yii 分页案例

总结一下yii分页的小案例和小知识

数据库设计：

CREATE TABLE IF NOT EXISTS `statistics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL COMMENT "游戏编号",
  `type` smallint(1) NOT NULL COMMENT "登录或注册类型(0:登录;1:注册)",
  `content` varchar(500) NOT NULL COMMENT "数据内容",
  `tt` varchar(10) NOT NULL COMMENT "时间",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT="统计登录或注册数据" AUTO_INCREMENT=110 ;

INSERT INTO `statistics` (`id`, `game_id`, `type`, `content`, `tt`) VALUES
(1, 111, 1, "[{"type":2,"num":496},{"type":6,"num":8848},{"type":41,"num":3451},{"type":42,"num":46},{"type":43,"num":1733}]", "20160617");

 public function actionAdmin() //读取最近一周的记录
    {
        $startTime = date("Ymd", strtotime("-7 days")); //开始时间
        $endTime = date("Ymd", time()); //结束时间
        $redisKey = "statistic_login:login_type:1:tt:weekday_7_days:startTime:".$startTime."endTime".$endTime; 
        $info = json_decode(Yii::app()->redis->get($redisKey), true); //从缓存中读取
        if (empty($info)) { //缓存中不存在数据从数据库中读取
            $sql = "SELECT * FROM `statistics` WHERE `tt` > "" . $startTime . ""  AND `tt` < "" . $endTime . "" AND `type`=1  GROUP BY `tt`, `game_id`";
            $info = Yii::app()->db->createCommand($sql)->queryAll();
            Yii::app()->redis->set($redisKey, json_encode($info));
        }
        $newInfo = array();
        $loginTypeArray = array();
        if (count($info) > 0) {
            foreach ($info as $infokey => &$infovalue) {
                $newInfo[$infovalue["tt"]][] = $infovalue; //按时间分组
            }
        }
        $gameInfo = StatisticsHelper::gameInfo();
        $title = "近一周的注册统计";
        $this->render("admin", array("info" => $newInfo, "loginType" => $loginTypeArray, "title" => $title, "gameInfo" => $gameInfo));
    }

 private function page($pageSize = 15) //分页方法
    {
        $loginType = trim(Yii::app()->request->getParam("loginType"));
        $startTime = date("Ymd", strtotime(trim(Yii::app()->request->getParam("startTime"))));
        $endTime = date("Ymd", strtotime(trim(Yii::app()->request->getParam("endTime"))));
        $gameId = trim(Yii::app()->request->getParam("gameId"));
        $sql = $this->getSql($startTime, $endTime, $gameId, $loginType, 1);
        $info = Yii::app()->db->createCommand($sql)->queryAll();
        $pageNum = intval($_GET["pageNum"]);
        $total = $info[0]["num"];
        $totalPage = ceil($total / $pageSize);
        $startPage = $pageNum * $pageSize;
        $arr["total"] = $total;
        $arr["pageSize"] = $pageSize;
        $arr["totalPage"] = $totalPage;
        $loginTypeName = $loginType == 0 ? "登录" : "注册";
        $title = "title";
        $arr["title"] = $title;
        $query = $this->getSql($startTime, $endTime, $gameId, $loginType);
        $query .= " limit " . $startPage . "," . $pageSize;
        $results = Yii::app()->db->createCommand($query)->queryAll();
        $arr["result"] = $results;
        return $arr;
    }

public function actionStatisticsPage()
    {
        $array = $this->page();
        $result = $array["result"];
        $newInfo = array();
        $loginTypeArray = array();
        if (count($result) > 0) {
            foreach ($result as $infokey => &$infovalue) {
                $newInfo[$infovalue["tt"]][] = $infovalue;
            }
        }
        $array["info"] = $newInfo;
        $array["loginType"] = $loginTypeArray;
        echo json_encode($array);
    }

<div class="portlet">
    <div class="portlet-title">
        <div class="caption" id="title">
            <span><?php echo $title; ?>
        </div>
    </div>
    <div class="portlet-body">
        <div class="table-responsive" id="table">
            <span></span>
            <table class="table table-bordered table-hover">
                <thead>
                <tr>
                    <th>日期</th>
                    <th>游戏名称</th>
                    <th>总计</th>
                    <?php
                    foreach ($loginType as $typeKey => $typeValue) {
                        $typeValue = empty($typeKey) ? "empty" : $typeValue;
                        echo "<th>" . StatisticsHelper::loginOrRegType($typeValue) . "</th>";
                    }
                    ?>
                </tr>
                </thead>
                <tbody>
                <?php
                foreach ($info as $key => $value1) {
                    $count = count($value1);
                    echo "<tr><td rowspan="{$count}" valign="middle" align="center">$key</td>";
                    foreach ($value1 as $key2 => $value) {
                        $content = json_decode($value["content"], true);
                        $sum = 0;
                        foreach ($content as &$contentvalue) {
                            $sum += $contentvalue["num"];
                        }
                        echo "<td>" . $value["game_name"] . "</td><td>" . $sum . "</td>";

                        foreach ($loginType as $loginkey => $loginvalue) {
                            $tmpnum = 0;
                            foreach ($content as $childkey => $childvalue) {
                                if ($loginvalue == $childvalue["type"]) {
                                    $tmpnum = $childvalue["num"];
                                }
                            }
                            $percent = round(($tmpnum / $sum) * 100) . "%";
                            echo "<td>" . $tmpnum . "(" . $percent . ")" . "</td>";
                        }
                        echo "</tr>";
                    }
                }
                ?>
                </tbody>
            </table>
        </div>
        <div id="pageBar" style="width: auto;height:30px;">
            <div id="pagecount" style="font-size:110%;width: auto;float: right;margin-right: 50px;margin-top: 8px"><span><a></a></span> </div>
        </div>
    </div>
</div>

$(document).ready(function () {
    $("#statisticsSubmit").click(function () {
        var search_starttime = $.trim($("#startTime").val());
        var el = jQuery(this).closest(".portlet").children(".portlet-body");
        blockUI(el);
        doAjax(1, el);
    });
});
function checkEndTime() {
    var startTime = $("#startTime").val();
    var start = new Date(startTime.replace("-", "/").replace("-", "/"));
    var endTime = $("#endTime").val();
    var end = new Date(endTime.replace("-", "/").replace("-", "/"));
    if (end < start) {
        return false;
    }
    return true;
}
function getJsonLength(jsonData) {
    var jsonLength = 0;
    for (var item in jsonData) {
        jsonLength++;
    }
    return jsonLength;
}
function getPageBar() {
    var $pager = $("#pagecount");
    $("#pagecount span").remove();
    $("#pagecount a").remove();
    //页码大于最大页数
    if (curPage > totalPage) curPage = totalPage;
    //页码小于1
    if (curPage < 1) curPage = 1;
    $pager.append("<span style="margin-right: 3px">共" + total + "条</span><span style="margin-right: 3px">" + curPage + "/" + totalPage + "</span>");
    var divin = document.createElement("a");
    //如果是第一页
    if (curPage == 1) {
        $pager.append("<span style="margin-right: 5px">首页</span><span style="margin-right: 5px">上一页</span>");
    }
    else {
        var first = $("<span style="margin-right: 3px"><a href="javascript:void(0)">首页</a></span>").click(function () {
            var el = jQuery(this).closest(".portlet").children(".portlet-body");
            blockUI(el);
            doAjax(1, el);
            return false;
        });
        $pager.append(first);
        var first1 = $("<span style="margin-right: 3px"><a href="javascript:void(0)">上一页</a></span>").click(function () {
            var el = jQuery(this).closest(".portlet").children(".portlet-body");
            blockUI(el);
            doAjax(curPage - 1, el);
            return false;
        });
        $pager.append(first1);
    }

    var viewedIndex = 6;
    if (totalPage < viewedIndex) {
        viewedIndex = totalPage;
    }
    var firstIndex = (curPage <= parseInt(viewedIndex / 2) + 1 ? 1 : ( curPage - parseInt(viewedIndex / 2) ));
    var lastIndex = (firstIndex + viewedIndex - 1 >= totalPage ? totalPage : firstIndex + viewedIndex - 1);
    if (lastIndex >= totalPage)
        firstIndex = lastIndex - viewedIndex + 1;
    for (var j = firstIndex; j < lastIndex + 1; j++) {
        var a = $("<a style="margin-right: 5px" rel=" + j + " href="javascript:void(0)">" + j + "</a>").click(function () {
            var value = $(this).attr("rel");
            var el = jQuery(this).closest(".portlet").children(".portlet-body");
            blockUI(el);
            doAjax(value, el);
            return false;
        });
        $pager.append(a);
    }

    //如果是最后页
    if (curPage >= totalPage) {
        $pager.append("<span style="margin-right: 3px">下一页</span><span>尾页</span>");
    }
    else {
        var first = $("<span style="margin-right: 3px"><a href="javascript:void(0)">下一页</a></span>").click(function () {
            var el = jQuery(this).closest(".portlet").children(".portlet-body");
            blockUI(el);
            doAjax(curPage + 1, el);
            return false;
        });
        $pager.append(first);
        var first1 = $("<span style="margin-right: 3px"><a href="javascript:void(0)">尾页</a></span>").click(function () {
            var el = jQuery(this).closest(".portlet").children(".portlet-body");
            blockUI(el);
            doAjax(totalPage, el);
            return false;
        });
        $pager.append(first1);
    }
}
function doAjax(page, el) {
    var startTime = $("input[name="from"]").val();
    var endTime = $("input[name="to"]").val();
    var loginType1 = $("#loginType").val();
    var gameId = $("#selectedGameId").val();
    if (startTime == "" || endTime == "") {
        alert("起止时间不能为空");
        return;
    }
    if (!checkEndTime()) {
        alert("结束时间必须晚于开始时间！");
        return;
    }
    page = parseInt(page);
    $.ajax
    ({
        type: "GET",
        dataType: "json",
        data: {
            startTime: startTime,
            endTime: endTime,
            loginType: loginType1,
            gameId: gameId,
            pageNum:page-1
        },
        url: "/admin/statistics/StatisticsPage",
        dataType: "json",
        error: function (XMLHttpRequest, textStatus, errorThrown) {
            unblockUI(el);
            $("#table").find("span").html("失败！" + textStatus + ";" + errorThrown);
        },
        success: function (datas) {
            unblockUI(el);
            $("#table tbody").empty();
            //var myobj=eval(datas);
            total = datas.total; //总记录数
            pageSize = datas.pageSize; //每页显示条数
            curPage = page; //当前页
            totalPage = datas.totalPage; //总页数
            var li = "";
            var infoArray = datas.info;
            var loginType = datas.loginType;
            $("#title").find("span").html(datas.title);
            $("#table").find("span").html("");
            if (total <= 0) {
                unblockUI(el);
                $("#table").find("table").remove();
                $("#table").find("span").html("没有符合该条件的信息");
                return;
            }
            if (getJsonLength(infoArray) > 0) {
                $("#table").find("table").remove();
                var html = "";
                html += "<table class="table table-bordered table-hover"><tr><th>日期</th><th>游戏名称<th>总计</th></th>";
                $.each(loginType, function (typeKey, typeValue) {
                    html += "<th>" + loginOrRegType(typeValue) + "</th>";
                });
                html += "</tr></thead><tbody>";

                $.each(infoArray, function (infokey, infoValue) {
                    count = infoValue.length;
                    html += "<tr><td rowspan="" + count + "" valign="middle" align="center">" + infokey + "</td>";
                    $.each(infoValue, function (key, value) {
                        var sum = 0;
                        content = jQuery.parseJSON(value.content);
                        $.each(content, function (key2, value2) {
                            sum += value2["num"];
                        });
                        html += "<td>" + value.game_name + "</td><td>" + sum + "</td>";
                        $.each(loginType, function (loginkey, loginvalue) {
                            var tmpnum = 0;
                            $.each(content, function (childkey, childvalue) {
                                if (loginvalue == childvalue.type) {
                                    tmpnum = childvalue.num;
                                }
                            });
                            var percent = (Math.round((tmpnum / sum) * 100)) + "%";
                            var string = tmpnum + "(" + percent + ")";
                            html += "<td>" + string + "</td>";
                        });
                        html += "</tr>";
                    });
                });
                html += "</tbody></table>";
                $("#table").append(html);
            } else {
                $("#table").find("table").remove();
                $("#table").find("span").html("没有符合该条件的信息");
            }
            if (!datas.admin) {
                $(".repeat").css("display", "none");
                $(".repeat_admin").css("display", "none");
            }
        },
        complete: function () {
            //生成分页条
            if (total > 10) {
                getPageBar();
            }
            else {
                $("#pagecount span").remove();
                $("#pagecount a").remove();
            }
        }
    });
}
function getRootPath1() {
    var strFullPath = window.document.location.href;
    var strPath = window.document.location.pathname;
    var pos = strFullPath.indexOf(strPath);
    var prePath = strFullPath.substring(0, pos);
    return prePath;
}
function blockUI(el, centerY) {
    var el = jQuery(el);
    if (el.height() <= 400) {
        centerY = true;
    }
    el.block({
        message: "<img src= " + getRootPath1() + "/assets/img/ajax-loading.gif" + " align="">",
        centerY: centerY != undefined ? centerY : true,
        css: {
            top: "10%",
            border: "none",
            padding: "2px",
            backgroundColor: "none"
        },
        overlayCSS: {
            backgroundColor: "#000",
            opacity: 0.05,
            cursor: "wait"
        }
    });
}
function unblockUI(el) {
    jQuery(el).unblock({
        onUnblock: function () {
            jQuery(el).removeAttr("style");
        }
    });
}