项目中用到列表的地方很多,二页面列表的显示必然要求分页,
所以分页和查询几乎密不可分,如果说你不会分页查询数据,
那你基本上还属于菜鸟。
分页的原理很简单,从sql上看就是从哪一条开始,往后差几条。
所以sql只需要传2个参数,这只是原理罢了,关键是实现。
而实现的方法就多了去了,架构师干这个是小菜一碟。
在我的项目中,关于分页架构师已经写好了一个管理分页的类,
这个类与sql耦合,控制分页只需哟啊控制这个类的对象的参数,
说白了这个类就是管理上面sql中需要的那2个参数,无论怎么写
实质是一样的。
在定义HibernateTemplete是定义分页的查询方法,例如:
- public List queryNativeSQL(final String sql, final PaginationSupport ps) {
- return (List) execute(new HibernateCallback() {
- public Object doInHibernate(Session session) throws HibernateException {
- try {
- SQLQuery query = session.createSQLQuery(sql);
- query.setFirstResult(ps.getStartIndex());
- query.setMaxResults(ps.getCountOnEachPage());
- return query.list();
- } catch (RuntimeException e) {
- log.error("query sql catch exception: " , e);
- throw e;
- }
- }
- });
- }
核心就是
query.setFirstResult(ps.getStartIndex());
query.setMaxResults(ps.getCountOnEachPage());
关于PaginationSupport ps就是管理这两个参数与分页其他参数的逻辑。
- package org.rd.framework.query.support;
- import java.io.Serializable;
- import org.rd.framework.query.sort.SortCriterion;
- public class PaginationSupport implements Serializable {
- private static final long serialVersionUID = 1L;
- private static int DEFAULT_COUNT_ON_EACH_PAGE = 20;
- private int totalCount;
- private int startIndex;
- private int countOnEachPage;
- // support field sort
- private boolean sortingEnabled = false; // default
- private SortCriterion sorter = null;
- public PaginationSupport() {
- this(DEFAULT_COUNT_ON_EACH_PAGE);
- }
- /**
- * @param sortingEnabled default false
- */
- public PaginationSupport(boolean sortingEnabled) {
- this(DEFAULT_COUNT_ON_EACH_PAGE);
- this.sortingEnabled = sortingEnabled;
- }
- public PaginationSupport(int countOnEachPage) {
- startIndex = 0;
- this.countOnEachPage = countOnEachPage < 1
- ? DEFAULT_COUNT_ON_EACH_PAGE
- : countOnEachPage;
- }
- public PaginationSupport(int startIndex, int totalCount, int countOnEachPage) {
- this.startIndex = startIndex;
- this.totalCount = totalCount;
- this.countOnEachPage = countOnEachPage;
- }
- public void setStartIndex(int startIndex) {
- this.startIndex = startIndex;
- }
- public void setCountOnEachPage(int countOnEachPage) {
- this.countOnEachPage = countOnEachPage;
- }
- public int getTotalCount() {
- return totalCount;
- }
- public int getEndIndex() {
- int endIndex = getStartIndex() + countOnEachPage;
- return endIndex > totalCount ? totalCount : endIndex;
- }
- public int getStartIndex() {
- if (startIndex > totalCount) {
- return totalCount;
- } else if (startIndex < 0) {
- return 0;
- } else {
- return startIndex;
- }
- }
- public int getNextIndex() {
- int[] nextStartIndexes = getNextStartIndexes();
- if (nextStartIndexes == null) {
- return getTotalCount();
- } else {
- return nextStartIndexes[0];
- }
- }
- public int getPreviousIndex() {
- int[] previousIndexes = getPreviousStartIndexes();
- if (previousIndexes == null) {
- return getStartIndex();
- } else {
- return previousIndexes[previousIndexes.length - 1];
- }
- }
- public int[] getNextStartIndexes() {
- int index = getEndIndex();
- if (index == totalCount) {
- return null;
- }
- int count = (totalCount - index) / countOnEachPage;
- if ((totalCount - index) % countOnEachPage > 0) {
- count++;
- }
- int result[] = new int[count];
- for (int i = 0; i < count; i++) {
- result[i] = index;
- index += countOnEachPage;
- }
- return result;
- }
- public int[] getPreviousStartIndexes() {
- int index = getStartIndex();
- if (index == 0) {
- return null;
- }
- int count = index / countOnEachPage;
- if (index % countOnEachPage > 0) {
- count++;
- }
- int result[] = new int[count];
- for (int i = count - 1; i > 0; i--) {
- index -= countOnEachPage;
- result[i] = index;
- }
- return result;
- }
- public int getCountOnEachPage() {
- return countOnEachPage;
- }
- public void setTotalCount(int totalCount) {
- this.totalCount = totalCount;
- validate();
- }
- private void validate() {
- if (startIndex >= totalCount) {
- int i = getTotalCount() % countOnEachPage;
- startIndex = totalCount - i;
- }
- if (startIndex < 0) {
- startIndex = 0;
- }
- }
- /**
- * Return the number of pages for the current query.
- */
- public int getPageCount() {
- int pages = getTotalCount() / countOnEachPage;
- int i = getTotalCount() % countOnEachPage;
- if (i > 0) {
- pages++;
- }
- if (getTotalCount() == 0) {
- pages = 1;
- }
- return pages;
- }
- /**
- * Return the current page number.
- * Page numbering starts with 1.
- */
- public int getPage() {
- int page = startIndex / countOnEachPage;
- return page + 1;
- }
- public void setPage(int page) {
- startIndex = page < 1 ? 0 : (page - 1) * countOnEachPage;
- }
- public boolean isSortingEnabled() {
- return sortingEnabled;
- }
- public void setSortingEnabled(boolean sortingEnabled) {
- this.sortingEnabled = sortingEnabled;
- }
- public SortCriterion getSorter() {
- return sorter;
- }
- public void setSorter(SortCriterion sorter) {
- this.sorter = sorter;
- }
- public String toString() {
- return "PaginationSupport["
- + "totalCount=" + totalCount
- + ", startIndex="+ startIndex
- + ", pageCount=" + getPageCount()
- + ", page=" + getPage()
- + ", sorter=" + sorter
- + "]";
- }
- }
底层的方法已经具备,剩下的就是要在业务层管理传参和在
显示层管理页面展示就可以了。
在页面的展示,
我的思路是对一个div,定义好id,根据这个id找到它,然后再里面
写html展示分页。
greenpage.js(使用到jquerty,在使用本js文件时需要同时引入jquery)
- function GreenPage(divid,pageHref,currPage,pageCount,validHref){
- this.divid=divid;
- this.pageHref=pageHref;
- this.currPage=currPage;
- this.pageCount=pageCount;
- this.validHref=validHref;
- };
- GreenPage.prototype.getCurrPageInfo=function ()
- {
- return "GreenPageInfo---"+this.divid+":"+this.pageHref+":"+this.currPage+":"+this.pageCount;
- };
- GreenPage.prototype.getDivInfo=function ()
- {
- return "getDivInfo---"+$("#"+this.divid).html();
- };
- GreenPage.prototype.currPageClick=function (currPage)
- {
- this.currPage=currPage;
- document.getElementById(divid).innerHTML="";
- this.makePage();
- };
- GreenPage.prototype.makePage=function ()
- {
- $("#"+this.divid).addClass("greenpage");
- if(this.currPage=="1"){
- }else{
- if(this.validHref=="0"){
- writeHtml(this.divid,"<a href=\"#\" οnclick=\"pageClickFunc('1')\">首页</a> ");
- }else{
- writeHtml(this.divid,"<a href=\""+this.pageHref+"?targetPageNum=1\" οnclick=\"pageClickFunc('1')\">首页</a> ");
- }
- }
- if(this.currPage=="1"){
- }else{
- var tempnum=(this.currPage-1>0)?(this.currPage-1):1;
- if(this.validHref=="0"){
- writeHtml(this.divid,"<a href=\"#\" οnclick=\"pageClickFunc('"+tempnum+"')\">上一页</a> ");
- }else{
- writeHtml(this.divid,"<a href=\""+this.pageHref+"?targetPageNum="+tempnum+"\" οnclick=\"pageClickFunc('"+tempnum+"')\">上一页</a> ");
- }
- }
- var start=0;
- var end=0;
- if(this.pageCount<=10){
- start=1;
- end=this.pageCount;
- }else{
- if(this.currPage<=5){
- start=1;
- end=10;
- }else if(this.currPage>=this.pageCount-5){
- start=this.currPage-(10-(this.pageCount-this.currPage))+1;
- end=this.pageCount;
- }else{
- start=this.currPage-4;
- end=(this.currPage+5<=this.pageCount)?(this.currPage+5):this.pageCount;
- }
- }
- for(var i=start;i<=end;i++){
- if(i==this.currPage){
- writeHtml(this.divid," <span class=\"current\" id=\""+this.divid+"Selected\">["+i+"] </span> ");
- }else{
- if(this.validHref=="0"){
- writeHtml(this.divid,"<a href=\"#\" οnclick=\"pageClickFunc('"+i+"')\">"+i+"</a> ");
- }else{
- writeHtml(this.divid,"<a href=\""+this.pageHref+"?targetPageNum="+i+"\" οnclick=\"pageClickFunc('"+i+"')\">"+i+"</a> ");
- }
- }
- }
- if(this.currPage==this.pageCount){
- }else{
- var pcurr=parseInt(this.currPage);
- var pcoun=parseInt(this.pageCount);
- var tempnum=((pcurr+1)<pcoun)?(pcurr+1):pcoun;
- if(this.validHref=="0"){
- writeHtml(this.divid,"<a href=\"#\" οnclick=\"pageClickFunc('"+tempnum+"')\">下一页</a> ");
- }else{
- writeHtml(this.divid,"<a href=\""+this.pageHref+"?targetPageNum="+tempnum+"\" οnclick=\"pageClickFunc('"+tempnum+"')\">下一页</a> ");
- }
- }
- if(this.currPage==this.pageCount){
- }else{
- if(this.validHref=="0"){
- writeHtml(this.divid,"<a href=\"#\" οnclick=\"pageClickFunc('"+this.pageCount+"')\">尾页</a> ");
- }else{
- writeHtml(this.divid,"<a href=\""+this.pageHref+"?targetPageNum="+this.pageCount+"\" οnclick=\"pageClickFunc('"+this.pageCount+"')\">尾页</a> ");
- }
- }
- };
- GreenPage.prototype.reMakePage=function (divid,pageHref,currPage,pageCount,validHref)
- {
- this.divid=divid;
- this.pageHref=pageHref;
- this.currPage=currPage;
- this.pageCount=pageCount;
- this.validHref=validHref;
- document.getElementById(divid).innerHTML="";
- this.makePage();
- };
- function writeHtml(divid,str){
- //document.getElementById(divid).innerHTML=document.getElementById(divid).innerHTML+str;
- $("#"+divid).html($("#"+divid).html()+str);
- }
逻辑很简单,可以说算是分页里面的最简单版本了,没有多余的其他控制逻辑,
值根据参数负责显示。
有一个reMakePage方法需要注意,这个是根据参数重新画分页,因为你没点一次
分页的链接,分页都需要变化(你点的那一页选中,有没有上一页,首页等),
所以每点一次必然重画分页,这个方法就是重画分页,在ajax回调方法里要调用这个
方法,当然要给正确的参数,才能保证正确显示。
greenpage.css(没有用到图片,所以样式很容易改,公用性比较强)
- DIV.greenpage {
- PADDING-RIGHT: 3px; PADDING-LEFT: 3px; PADDING-BOTTOM:10px; MARGIN: 3px; PADDING-TOP: 17px; TEXT-ALIGN: center; font-size:12px;
- }
- DIV.greenpage A {
- BORDER-RIGHT: #ddd 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #ddd 1px solid; PADDING-LEFT: 5px; PADDING-BOTTOM: 2px; BORDER-LEFT: #ddd 1px solid; COLOR: #88af3f; MARGIN-RIGHT: 2px; PADDING-TOP: 2px; BORDER-BOTTOM: #ddd 1px solid; TEXT-DECORATION: none
- }
- DIV.greenpage A:hover {
- BORDER-RIGHT: #85bd1e 1px solid; BORDER-TOP: #85bd1e 1px solid; BORDER-LEFT: #85bd1e 1px solid; COLOR: #638425; BORDER-BOTTOM: #85bd1e 1px solid; BACKGROUND-COLOR: #f1ffd6
- }
- DIV.greenpage A:active {
- BORDER-RIGHT: #85bd1e 1px solid; BORDER-TOP: #85bd1e 1px solid; BORDER-LEFT: #85bd1e 1px solid; COLOR: #638425; BORDER-BOTTOM: #85bd1e 1px solid; BACKGROUND-COLOR: #f1ffd6
- }
- DIV.greenpage SPAN.current {
- BORDER-RIGHT: #b2e05d 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #b2e05d 1px solid; PADDING-LEFT: 5px;
- FONT-WEIGHT: bold; PADDING-BOTTOM: 2px; BORDER-LEFT: #b2e05d 1px solid; COLOR: #fff; MARGIN-RIGHT: 2px; PADDING-TOP: 2px;
- BORDER-BOTTOM: #b2e05d 1px solid; BACKGROUND-COLOR: #b2e05d
- }
- DIV.greenpage SPAN.disabled {
- BORDER-RIGHT: #f3f3f3 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #f3f3f3 1px solid; PADDING-LEFT: 5px; PADDING-BOTTOM: 2px; BORDER-LEFT: #f3f3f3 1px solid;
- COLOR: #ccc; MARGIN-RIGHT: 2px; PADDING-TOP: 2px; BORDER-BOTTOM: #f3f3f3 1px solid
- }
项目中使用的一个实例:
insummary.jsp
- <%@ page language="java" contentType="text/html; charset=UTF-8"%>
- <%@ taglib uri="/struts-tags" prefix="s"%>
- <%@ taglib uri="/WEB-INF/component.tld" prefix="cx"%>
- <%@ taglib uri="/WEB-INF/greenpage.tld" prefix="page"%>
- <%@ page import="java.util.*" %>
- <%@ page import="org.hd.util.ReportUtil" %>
- <%@ page import="org.rd.framework.query.support.PaginationSupport" %>
- <%
- String path = request.getContextPath();
- %>
- <%
- String xmldata="";
- if(request.getAttribute("InSummaryData")!=null){
- xmldata=(String)request.getAttribute("InSummaryData");
- }
- String isqueryed="";
- if(request.getAttribute("isqueryed")!=null){
- isqueryed=(String)request.getAttribute("isqueryed");
- }
- List tablelist=null;
- if(request.getAttribute("InSummaryTableData")!=null){
- tablelist=(List)request.getAttribute("InSummaryTableData");
- }
- PaginationSupport pageset=null;
- if(request.getAttribute("pageset")!=null){
- pageset=(PaginationSupport)request.getAttribute("pageset");
- }else{
- pageset=new PaginationSupport(0,0,20);
- }
- String pathUrl=path+"/report/reportInSummary.action";
- %>
- <?xml version="1.0" encoding="UTF-8"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml">
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
- <title>Single Series Column 2D Chart</title>
- <script src="<%=path%>/script/jquery-1.7.1.js" type="text/javascript"></script>
- <link href="<%=path%>/resource/hd/css/style2.css" rel="stylesheet" type="text/css" />
- <link href="<%=path%>/script/plugin/FusionCharts_Evaluation/Gallery/assets/ui/css/style.css" rel="stylesheet" type="text/css" />
- <link href="<%=path%>/script/plugin/FusionCharts_Evaluation/Gallery/assets/prettify/prettify.css" rel="stylesheet" type="text/css" />
- <script type="text/javascript" src="<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/FusionCharts.js"></script>
- <script type="text/javascript" src="<%=path%>/script/plugin/FusionCharts_Evaluation/Gallery/assets/ui/js/json2.js"></script>
- <script type="text/javascript" src="<%=path%>/script/plugin/FusionCharts_Evaluation/Gallery/assets/prettify/prettify.js"></script>
- <script type="text/javascript" src="<%=path%>/script/plugin/FusionCharts_Evaluation/Gallery/assets/ui/js/lib.js" ></script>
- <script src="<%=path%>/script/plugin/My97DatePicker/WdatePicker.js" type="text/javascript"></script>
- <script src="<%=path%>/resource/tool/hometab/Style/menu.js" type="text/javascript"></script>
- <link rel="stylesheet" href="<%=path%>/resource/tool/hometab/Style/default.css" type="text/css" />
- <script src="<%=path%>/script/hd/greenpage.js" type="text/javascript"></script>
- <link rel="stylesheet" href="<%=path%>/script/hd/greenpage.css" type="text/css" />
- <script type="text/javascript" src="<%=path%>/script/hd/greenpage.js"></script>
- <link href="<%=path%>/script/hd/greenpage.css" rel="stylesheet" type="text/css" />
- </script>
- <script type="text/javascript">
- var divpage=null;
- $(document).ready(function(){
- divpage=new GreenPage('divpage','',1,1,'0');
- divpage.makePage();
- });
- </script>
- </head>
- <body style=" overflow-y:scroll;">
- <input type="hidden" id="path" value="<%=path%>" />
- <input type="hidden" id="xmldata" value='<%=xmldata%>' />
- <input type="hidden" id="isqueryed" value='<%=isqueryed%>' />
- <div style="width:100%;height:10px"></div>
- <h3 style="text-align: center;vertical-align:bottom;padding: 0px,0px,5px,0px; margin: 0px,0px,5px,0px; font-size:14px;color: #0c212b; ">
- 报表系统--呼入汇总</h3>
- <div style="width:100%;height:10px"></div>
- <table width="100%" border="0">
- <tr>
- <td style="width:100%;">
- <div >
- <table>
- <tr style=" margin: 8px,0px,5px,0px;">
- <td style="width:10%"></td>
- <td style="width:20%;align:center" id="fromdatetd"><p><font color="#401c44">开始日期: </font><input type="text" id="fromdate" class="Wdate" onClick="WdatePicker()"></p></td>
- <td style="width:20%;align:center" id="todatetd"><p><font color="#401c44">结束日期: </font><input type="text" id="todate" class="Wdate" onClick="WdatePicker()"></p></td>
- <td style="width:5%;align:center"></td>
- <td style="width:30%;">
- <select id="ritypebox" name="ritypebox" style="width: 100px; height: 25px;font-size:14px; color:#401c44;">
- <option value='ttian' selected>按日期统计</option>
- <option value='dnian' >按年份对比</option>
- <option value='djidu' >按季度对比</option>
- <option value='dyuefen'>按月份对比</option>
- <option value='dtian'>按日期对比</option>
- </select>
- </td>
- <td style="width:5%"><input type="button" id="chaxun" onclick="chaxun()" value="查询"></td>
- </tr>
- </table>
- </div>
- </td>
- </tr>
- <tr>
- <td style="width:100%;align:center">
- <div id="tablelist" >
- <table style="margin : 0px 0px 0px 0px;position: relative;left: 0px;top: 0px;
- width="100%" height="100%" border="0" cellspacing="0" cellpadding="0" id="box_mid">
- <tr>
- <td class="right"><!--列表-->
- <div class="divline2" >
- <table width="1100px" border="0" cellspacing="0" cellpadding="0" class="tables2">
- <tr >
- <th width="8%">日期</th>
- <th width="8%">坐席总数</th>
- <th width="8%">呼入总数</th>
- <th width="8%">总失败次数</th>
- <th width="8%">咨询次数</th>
- <th width="8%">转移次数 </th>
- <th width="8%">总通话时长</th>
- <th width="8%">平均通话时长</th>
- <th width="8%">平均振铃次数</th>
- <th width="8%">平均振铃时长</th>
- </tr>
- </table>
- </div>
- <div style="height:200px; overflow-y:none;width:100%;">
- <table width="100%" border="0" cellspacing="0" cellpadding="0" class="table4" id="list">
- <div id="listdata">
- <tr>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="8%"></td>
- <td width="9%"></td>
- </tr>
- </div>
- </table>
- <!--列表END--></td>
- </tr>
- </table>
- <div id="divpage" style="align:center"></div>
- </div>
- </div>
- </td>
- </tr>
- <tr>
- <td style="width:100%">
- </td>
- </tr>
- <tr>
- <td style="width:100%">
- </td>
- </tr>
- </table>
- <table style="width:100%;align:center">
- <tr>
- <td style="width:100%;">
- <div id="chartdiv11" style="align:center"></div>
- </td>
- </tr>
- <tr>
- <td style="width:100%;">
- <div id="chartdiv12" style="align:center"></div>
- </td>
- </tr>
- <tr>
- <td style="width:100%;">
- <div id="chartdiv13" style="align:center"></div>
- </td>
- </tr>
- </table>
- <table>
- <tr>
- <td style="width:50%">
- <div id="chartdiv21" style="display:none" align="center"></div>
- </td>
- <td style="width:50%">
- <div id="chartdiv22" style="display:none" align="center"></div>
- </td>
- </tr>
- <tr>
- <td style="width:50%">
- <div id="chartdiv23" style="display:none" align="center"></div>
- </td>
- <td style="width:50%">
- <div id="chartdiv24" style="display:none" align="center"></div>
- </td>
- </tr>
- </tr>
- <tr>
- <td style="width:50%">
- <div id="chartdiv25" style="display:none" align="center"></div>
- </td>
- <td style="width:50%">
- </td>
- </tr>
- </table>
- </body>
- </html>
- <script type="text/javascript" src="temp/insummary.js" ></script>
- <script type="text/javascript" src="temp/insummary2.js" ></script>
- <script type="text/javascript">
- var path=document.getElementById("path").value;
- var isqueryed=document.getElementById("isqueryed").value;
- var xmldata=document.getElementById("xmldata").value;
- if (GALLERY_RENDERER && GALLERY_RENDERER.search(/javascript|flash/i)==0) FusionCharts.setCurrentRenderer(GALLERY_RENDERER);
- var chart11 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/Column3D.swf", "ChartId11", "560", "400", "0", "0");
- chart11.setXMLData(dataString11);
- chart11.render("chartdiv11");
- var chart12 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/MSColumn3D.swf", "ChartId12", "560", "400", "0", "0");
- chart12.setXMLData(dataString12);
- chart12.render("chartdiv12");
- var chart13 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/Column3D.swf", "ChartId13", "560", "400", "0", "0");
- chart13.setXMLData( dataString13 );
- chart13.render("chartdiv13");
- //if (GALLERY_RENDERER && GALLERY_RENDERER.search(/javascript|flash/i)==0) FusionCharts.setCurrentRenderer(GALLERY_RENDERER);
- var chart21 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/MSLine.swf", "ChartId21", "560", "400", "0", "0");
- chart21.setXMLData( dataString21 );
- chart21.render("chartdiv21");
- var chart22 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/MSLine.swf", "ChartId22", "560", "400", "0", "0");
- chart22.setXMLData( dataString22 );
- chart22.render("chartdiv22");
- var chart23 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/MSLine.swf", "ChartId23", "560", "400", "0", "0");
- chart23.setXMLData( dataString23 );
- chart23.render("chartdiv23");
- var chart24 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/MSLine.swf", "ChartId24", "560", "400", "0", "0");
- chart24.setXMLData( dataString24 );
- chart24.render("chartdiv24");
- var chart25 = new FusionCharts("<%=path%>/script/plugin/FusionCharts_Evaluation/Charts/MSLine.swf", "ChartId25", "560", "400", "0", "0");
- chart25.setXMLData( dataString25 );
- chart25.render("chartdiv25");
- $("#ritypebox").change( function() {
- // 这里可以写些验证代码
- var tt=getRitypeValueBox();
- var vv=$("#ritypebox").val();
- if($.trim(vv)=="dnian"){
- reNewMy97DatePicker("yyyy");
- chooseone('d');
- }else if($.trim(vv)=="djidu"){
- var hh1="<p>年份: <input type=\"text\" id=\"fromdate\" class=\"Wdate\" onClick=\"WdatePicker({dateFmt:'yyyy'})\"></p>";
- var hh2="季度:<select id=\"todate\" name=\"todate\">"
- +"<option value='1' >第一季度</option><option value='2' >第二季度</option>"+
- "<option value='3'>第三季度</option>"+
- "<option value='4'>第四季度</option>"+
- "</select>";
- $("#fromdatetd").html(hh1);
- $("#todatetd").html(hh2);
- chooseone('d');
- }else if($.trim(vv)=="dyuefen"){
- reNewMy97DatePicker("yyyy-MM");
- chooseone('d');
- }else if($.trim(vv)=="dtian"){
- reNewMy97DatePicker("yyyy-MM-dd");
- chooseone('d');
- }else if($.trim(vv)=="ttian"){
- reNewMy97DatePicker("yyyy-MM-dd");
- chooseone('t');
- }
- });
- function reNewMy97DatePicker(fmt){
- var hh1="<p>开始日期: <input type=\"text\" id=\"fromdate\" class=\"Wdate\" onClick=\"WdatePicker({dateFmt:'"+fmt+"'})\"></p>";
- var hh2="<p>结束日期: <input type=\"text\" id=\"todate\" class=\"Wdate\" onClick=\"WdatePicker({dateFmt:'"+fmt+"'})\"></p>";
- $("#fromdatetd").html(hh1);
- $("#todatetd").html(hh2);
- }
- function getRitypeValueBox() {
- var checkText=$("#ritypebox").find("option:selected").text();
- return checkText;
- }
- function chooseone(t){
- if(t=="t"){
- $("#tablelist").show();
- $("#chartdiv11").show();
- $("#chartdiv12").show();
- $("#chartdiv13").show();
- $("#chartdiv21").hide();
- $("#chartdiv22").hide();
- $("#chartdiv23").hide();
- $("#chartdiv24").hide();
- $("#chartdiv25").hide();
- }else{
- $("#tablelist").hide();
- $("#chartdiv11").hide();
- $("#chartdiv12").hide();
- $("#chartdiv13").hide();
- $("#chartdiv21").show();
- $("#chartdiv22").show();
- $("#chartdiv23").show();
- $("#chartdiv24").show();
- $("#chartdiv25").show();
- }
- }
- //查询
- function chaxun(){
- var datetype=$.trim($("#ritypebox").val());
- var date1=$("#fromdate").val();
- var date2=$("#todate").val();
- if($.trim(date1)==""&&$.trim(date2)==""){
- alert("请选择查询条件!");
- }else{
- var urlpath=path+'/report/judgeIsTwoRightDate.action?fromdate='+date1+'&todate='+date2+'&datetype='+datetype;
- $.ajax({
- type: "POST",
- url: urlpath,
- success: validateBack
- });
- }
- }
- function validateBack(msg){
- var mm=$.trim(msg);
- if(mm=="NO"){
- alert("开始日期不能大于结束日期!");
- }else{
- var datetype=$.trim($("#ritypebox").val());
- var date1=$("#fromdate").val();
- var date2=$("#todate").val();
- //对比还是统计
- if(datetype=="ttian"){
- var urlpath=path+'/report/rptInSummaryAll.action?fromdate='+date1+'&todate='+date2+'&datetype='+datetype;
- $.ajax({
- type: "POST",
- url: urlpath,
- success: chaxunBackDealData,
- dataType:"json"
- });
- }else{
- var urlpath=path+'/report/rptInSummaryCompare.action?fromdate='+date1+'&todate='+date2+'&datetype='+datetype;
- $.ajax({
- type: "POST",
- url: urlpath,
- success: chaxunCompareBackDealData,
- dataType:"json"
- });
- }
- }
- }
- function chaxunBackDealData(data){
- //var str=eval('('+data+')');
- //alert(data.all.page.currPage);
- var page=data.all.page;
- var listdata=data.all.listdata;
- var dataString11=data.all.dataString11.data;
- var dataString12=data.all.dataString12.data;
- var dataString13=data.all.dataString13.data;
- var datetype=$.trim($("#ritypebox").val());
- var date1=$("#fromdate").val();
- var date2=$("#todate").val();
- var url=path+'/report/rptInSummaryPage.action?fromdate='+date1+'&todate='+date2+'&datetype='+datetype;
- divpage.reMakePage('divpage',url,page.currPage,page.pageCount,'0');
- if(dataString11=="nodata"){
- alert("结果集无数据,请选择其它条件!");
- }else{
- chart11.setXMLData(dataString11);
- chart12.setXMLData(dataString12);
- chart13.setXMLData(dataString13);
- }
- var tablehtml="";
- for(var i=0;i<listdata.length;i++){
- //alert(listdata[i].agent_count);
- var agent_count=listdata[i].agent_count;
- var avg_long_time=listdata[i].avg_long_time;
- var avg_ring_count=listdata[i].avg_ring_count;
- var avg_ring_time=listdata[i].avg_ring_time;
- var fail_count=listdata[i].fail_count;
- var sdf_date=listdata[i].sdf_date;
- var sum_consult_count=listdata[i].sum_consult_count;
- var sum_long_time=listdata[i].sum_long_time;
- var sum_transfer_count=listdata[i].sum_transfer_count;
- var voice_count=listdata[i].voice_count;
- var htmlstr="<tr>";
- htmlstr+="<td width=\"8%\">"+sdf_date+"</td>";
- htmlstr+="<td width=\"8%\">"+agent_count+"</td>";
- htmlstr+="<td width=\"8%\">"+voice_count+"</td>";
- htmlstr+="<td width=\"8%\">"+fail_count+"</td>";
- htmlstr+="<td width=\"8%\">"+sum_consult_count+"</td>";
- htmlstr+="<td width=\"8%\">"+sum_transfer_count+"</td>";
- htmlstr+="<td width=\"8%\">"+sum_long_time+"</td>";
- htmlstr+="<td width=\"8%\">"+avg_long_time+"</td>";
- htmlstr+="<td width=\"8%\">"+avg_ring_count+"</td>";
- htmlstr+="<td width=\"9%\">"+avg_ring_time+"</td>";
- htmlstr+="</tr>";
- tablehtml+=htmlstr;
- }
- $("#list").html(tablehtml);
- }
- function chaxunCompareBackDealData(data){
- var dataString21=data.all.dataString21.data;
- var dataString22=data.all.dataString22.data;
- var dataString23=data.all.dataString23.data;
- var dataString24=data.all.dataString24.data;
- var dataString25=data.all.dataString25.data;
- //alert(dataString21);
- if(dataString21=="nodata"){
- alert("结果集无数据,请选择其它条件!");
- }else{
- chart21.setXMLData(dataString21);
- chart22.setXMLData(dataString22);
- chart23.setXMLData(dataString23);
- chart24.setXMLData(dataString24);
- chart25.setXMLData(dataString25);
- }
- }
- function pageClickFunc(page){
- //alert(divpage.getDivInfo());
- var datetype=$.trim($("#ritypebox").val());
- var date1=$("#fromdate").val();
- var date2=$("#todate").val();
- var urlpath=path+'/report/rptInSummaryPage.action?fromdate='+date1+'&todate='+date2+'&datetype='+datetype+"&targetPageNum="+page;
- $.ajax({
- type: "POST",
- url: urlpath,
- success: chaxunPageBackDealData,
- dataType:"json"
- });
- }
- function chaxunPageBackDealData(data){
- var page=data.all.page;
- var listdata=data.all.listdata;
- var datetype=$.trim($("#ritypebox").val());
- var date1=$("#fromdate").val();
- var date2=$("#todate").val();
- var url=path+'/report/rptInSummaryPage.action?fromdate='+date1+'&todate='+date2+'&datetype='+datetype;
- divpage.reMakePage('divpage',url,page.currPage,page.pageCount,'0');
- //alert(page.currPage+"-----"+page.pageCount+"--"+listdata);
- var tablehtml="";
- for(var i=0;i<listdata.length;i++){
- //alert(listdata[i].agent_count);
- var agent_count=listdata[i].agent_count;
- var avg_long_time=listdata[i].avg_long_time;
- var avg_ring_count=listdata[i].avg_ring_count;
- var avg_ring_time=listdata[i].avg_ring_time;
- var fail_count=listdata[i].fail_count;
- var sdf_date=listdata[i].sdf_date;
- var sum_consult_count=listdata[i].sum_consult_count;
- var sum_long_time=listdata[i].sum_long_time;
- var sum_transfer_count=listdata[i].sum_transfer_count;
- var voice_count=listdata[i].voice_count;
- var htmlstr="<tr>";
- htmlstr+="<td width=\"8%\">"+sdf_date+"</td>";
- htmlstr+="<td width=\"8%\">"+agent_count+"</td>";
- htmlstr+="<td width=\"8%\">"+voice_count+"</td>";
- htmlstr+="<td width=\"8%\">"+fail_count+"</td>";
- htmlstr+="<td width=\"8%\">"+sum_consult_count+"</td>";
- htmlstr+="<td width=\"8%\">"+sum_transfer_count+"</td>";
- htmlstr+="<td width=\"8%\">"+sum_long_time+"</td>";
- htmlstr+="<td width=\"8%\">"+avg_long_time+"</td>";
- htmlstr+="<td width=\"8%\">"+avg_ring_count+"</td>";
- htmlstr+="<td width=\"9%\">"+avg_ring_time+"</td>";
- htmlstr+="</tr>";
- tablehtml+=htmlstr;
- }
- $("#list").html(tablehtml);
- }
- /*
- var xmlurl=path+'/business/changeComeUserInfo.action?jobNumber='+cusid2;
- $.ajax({
- type: "POST",
- url: urlpath,
- success: function(msg){
- var mm=$.trim(msg);
- if(mm=="NO"){
- //alert("NO");
- }else{
- changeUser(mm);
- }
- }
- });
- */
- </script>
关于分页的使用,有几个地方:
var divpage=null;
$(document).ready(function(){
divpage=new GreenPage('divpage','',1,1,'0');
divpage.makePage();
});
divpage要拿到方法外面,因为后面刷新数据,需要这个对象,
初始化方法如上。
<div id="divpage" style="align:center"></div>
对这个div话分页
在ajax回调方法里要有
var url=path+'/report/rptInSummaryPage.action?fromdate='+date1+'&todate='+date2+'&datetype='+datetype;
divpage.reMakePage('divpage',url,page.currPage,page.currPage,'0');
这个就是为了重新画分页currPage,currPage是我用json返回的参数,这个你在后台自己
拼好在这里能取到就可以。
整体的思路就是这样,我的完整实例如下:
(逻辑有些复杂,因为不是为了分页专门写的例子,是项目中的实例)
insummary.jsp如上,
RptInSummaryAction.java
- package org.hd.report.action;
- import java.io.PrintWriter;
- import java.util.List;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.struts2.ServletActionContext;
- import org.hd.report.model.GreenPageVo;
- import org.hd.report.service.ReportService;
- import org.hd.report.service.RptService;
- import org.rd.framework.common.container.ContainerManager;
- import org.rd.framework.query.support.PaginationSupport;
- import org.rd.framework.struts.action.CommonAction;
- import com.opensymphony.xwork2.ActionContext;
- //呼入汇总
- public class RptInSummaryAction extends CommonAction{
- private RptService rptService = (RptService)ContainerManager.getComponent(RptService.BEAN_ID);
- private int targetPageNum;//目标页
- private PaginationSupport pageset=new PaginationSupport();
- private GreenPageVo greenpage=new GreenPageVo();//分页的设置开关
- public String rptInSummaryAll() throws Exception{
- ActionContext ctx = ActionContext.getContext();
- HttpServletResponse response = (HttpServletResponse) ctx.get(ServletActionContext.HTTP_RESPONSE);
- HttpServletRequest request = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
- response.setCharacterEncoding("UTF-8");
- PrintWriter out = response.getWriter();
- String fromdate="";
- String todate="";
- String datetype="";//下拉框5个参数
- if(request.getParameter("fromdate")==null||request.getParameter("fromdate").trim().equals("")){
- }else{
- fromdate=request.getParameter("fromdate").trim();
- }
- if(request.getParameter("todate")==null||request.getParameter("todate").trim().equals("")){
- }else{
- todate=request.getParameter("todate").trim();
- }
- if(request.getParameter("datetype")==null||request.getParameter("datetype").trim().equals("")){
- }else{
- datetype=request.getParameter("datetype").trim();
- }
- getGreenpage().setEachItemsOfPage(5);
- String xmlData=rptService.getAllDataForInSummary(fromdate, todate, datetype,getGreenpage());
- out.println(xmlData);
- return NONE;
- }
- public String rptInSummaryPage() throws Exception{
- ActionContext ctx = ActionContext.getContext();
- HttpServletResponse response = (HttpServletResponse) ctx.get(ServletActionContext.HTTP_RESPONSE);
- HttpServletRequest request = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
- response.setCharacterEncoding("UTF-8");
- PrintWriter out = response.getWriter();
- String fromdate="";
- String todate="";
- String datetype="";//下拉框5个参数
- String targetPageNum="";//目标页
- if(request.getParameter("targetPageNum")==null||request.getParameter("targetPageNum").trim().equals("")){
- }else{
- targetPageNum=request.getParameter("targetPageNum").trim();
- getGreenpage().setCurrPage(Integer.valueOf(targetPageNum));
- }
- if(request.getParameter("fromdate")==null||request.getParameter("fromdate").trim().equals("")){
- }else{
- fromdate=request.getParameter("fromdate").trim();
- }
- if(request.getParameter("todate")==null||request.getParameter("todate").trim().equals("")){
- }else{
- todate=request.getParameter("todate").trim();
- }
- if(request.getParameter("datetype")==null||request.getParameter("datetype").trim().equals("")){
- }else{
- datetype=request.getParameter("datetype").trim();
- }
- //设置分页
- getGreenpage().setEachItemsOfPage(5);
- String xmlData=rptService.getPageJsonForInSummary(fromdate, todate, datetype,getGreenpage());
- out.println(xmlData);
- return NONE;
- }
- //对比查询
- public String rptInSummaryCompare() throws Exception{
- ActionContext ctx = ActionContext.getContext();
- HttpServletResponse response = (HttpServletResponse) ctx.get(ServletActionContext.HTTP_RESPONSE);
- HttpServletRequest request = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
- response.setCharacterEncoding("UTF-8");
- PrintWriter out = response.getWriter();
- String fromdate="";
- String todate="";
- String datetype="";//下拉框5个参数
- if(request.getParameter("fromdate")==null||request.getParameter("fromdate").trim().equals("")){
- }else{
- fromdate=request.getParameter("fromdate").trim();
- }
- if(request.getParameter("todate")==null||request.getParameter("todate").trim().equals("")){
- }else{
- todate=request.getParameter("todate").trim();
- }
- if(request.getParameter("datetype")==null||request.getParameter("datetype").trim().equals("")){
- }else{
- datetype=request.getParameter("datetype").trim();
- }
- String xmlData=rptService.getCompareDataForInSummary(fromdate, todate, datetype);
- out.println(xmlData);
- return NONE;
- }
- public String execute() throws Exception{
- return SUCCESS;
- }
- public int getTargetPageNum() {
- return targetPageNum;
- }
- public PaginationSupport getPageset() {
- return pageset;
- }
- public void setTargetPageNum(int targetPageNum) {
- this.targetPageNum = targetPageNum;
- }
- public void setPageset(PaginationSupport pageset) {
- this.pageset = pageset;
- }
- public GreenPageVo getGreenpage() {
- return greenpage;
- }
- public void setGreenpage(GreenPageVo greenpage) {
- this.greenpage = greenpage;
- }
- }
GreenPageVo.java
管理传参,但不控制分页逻辑。
真正管理分页参数传到sql的是PaginationSupport,
因为传参过程只是传递,不需要逻辑,有逻辑的反而会把参数
传丢(因为它判断参数之间关系,你穿过去的不对,它就给你改正,这样参数就传丢了)
- package org.hd.report.model;
- import org.rd.framework.query.support.PaginationSupport;
- public class GreenPageVo {
- private int currPage=0;
- private int pageCount=0;
- private int eachItemsOfPage=5;
- public int getCurrPage() {
- return currPage;
- }
- public int getPageCount() {
- return pageCount;
- }
- public void setCurrPage(int currPage) {
- this.currPage = currPage;
- }
- public void setPageCount(int pageCount) {
- this.pageCount = pageCount;
- }
- public int getEachItemsOfPage() {
- return eachItemsOfPage;
- }
- public void setEachItemsOfPage(int eachItemsOfPage) {
- this.eachItemsOfPage = eachItemsOfPage;
- }
- }
action中有两个请求,一个是查询总数据的数据,包括分页数据;
,另一个是只查询分页列表数据的请求。
实现的底层方法(包括数据查询,拼装,返回)
RptServiceImpl.java
- package org.hd.report.service.impl;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.hd.report.model.GreenPageVo;
- import org.hd.report.model.resultvo.InDetailResult;
- import org.hd.report.model.resultvo.InsummaryResult;
- import org.hd.report.service.RptService;
- import org.hd.util.ReportUtil;
- import org.rd.framework.dao.impl.CommonServiceImpl;
- import org.rd.framework.query.support.PaginationSupport;
- public class RptServiceImpl extends CommonServiceImpl implements RptService {
- // 点击查询,呼入汇总insummary.sql
- public String getAllDataForInSummary(String fromdate, String todate,
- String datetype,GreenPageVo page) {
- String jsonData = "";
- List list = new ArrayList();
- String sql = "select vo.* " + " ,con.sum_consult_count "
- + " ,con.sum_transfer_count " + " from " + " ( "
- + " select " + " sdf_date "
- + " ,count(distinct a.agent_id) agent_count "
- + " ,count(voice_id) voice_count"
- + " ,sum(decode(a.start_time,null,1,'',1,0)) fail_count"
- + " ,avg(ring_long_time)/60 avg_ring_time"
- + " ,avg(decode(voice_id,null,0,1))/60 avg_ring_count"
- + " ,sum(long_time)/60 sum_long_time "
- + " ,avg(long_time)/60 avg_long_time " + " from "
- + " ( " + " select "
- + " substr(ring_time,0,10) sdf_date "
- + " , t.* from hd_agent_voice_seq t " + " where 1=1 ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and t.ring_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and t.ring_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " and t.voice_direct_id='0' "
- + " ) a "
- + " group by sdf_date "
- + " ) vo, "
- + " ( "
- + " select "
- + " zc.sdf_consult_date "
- + " ,sum(decode(zc.consult_start_time,null,0,'',0,1)) sum_consult_count "
- + " ,sum(decode(zc.transfer_flag,null,0,'',0,0,0,1)) sum_transfer_count "
- + " ,sum(decode(zc.conference_start_time,null,0,'',0,1)) sum_conference_count "
- + " ,sum(consult_long_time)/60 sum_consult_time "
- + " ,sum(conference_long_time)/60 sum_consult_time "
- + " from "
- + " ( "
- + " select za.*,substr(consult_start_time,0,10) sdf_consult_date,zb.voice_id bvoice_id "
- + " from hd_consult_log za , hd_agent_voice_seq zb "
- + " where za.voice_id=zb.voice_id(+) ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and za.consult_start_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and za.consult_start_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " ) zc " + " group by sdf_consult_date " + " ) con "
- + " where vo.sdf_date=con.sdf_consult_date(+) "
- + " order by vo.sdf_date ";
- System.out.println("getAllListForInSummary--" + sql);
- list = this.queryNativeSQL(sql);
- jsonData = this.getAllJsonForInSummaryByList(list,page);
- return jsonData;
- }
- // 列表,分页,报表 的数据
- public String getAllJsonForInSummaryByList(List list,GreenPageVo page) {
- String res = "";
- // "page":{"currPage":"6","pageCount":"67"}
- String pageJson = "";
- // [{"agent_count":"12","avg_long_time":"345"},{}]
- String listJson = "\"listdata\":[";
- String dataString11 = "";
- String dataString12 = "";
- String dataString13 = "";
- String rptJson11 = "<chart caption='呼入总数 ' palette='2' xAxisName='' yAxisName='单位:个' "
- + "formatNumberScale='0' numberPrefix='' labeldisplay='ROTATE' slantLabels='1'"
- + " sNumberSuffix=' pcs.' showLabels='1' showValues='1' labelDisplay='STAGGER'>";
- String rptJson11Set = "";
- String rptJson12 = "<chart caption='平均值比对' XAxisName='' palette='2' animation='1' "
- + "formatNumberScale='0' numberPrefix='' showValues='1' numDivLines='4' "
- + "legendPosition='BOTTOM'>";
- String rptJson12categories = "<categories>";
- String rptJson12dataset1 = "<dataset seriesName='平均振铃时长'>";
- String rptJson12dataset2 = "<dataset seriesName='平均通话时长'>";
- String rptJson13 = "<chart caption='呼损率' palette='2' formatNumberScale='0' xAxisName=''"
- + " yAxisName='单位:百分比' numberSuffix='%' numberPrefix='' labeldisplay='ROTATE' "
- + "slantLabels='1' sNumberSuffix=' pcs.' showValues='1' labelDisplay='STAGGER'>";
- String rptJson13set = "";
- if (list != null) {
- // 分页显示数据
- pageJson = "\"page\":{\"currPage\":\"1\",\"pageCount\":\""
- + (ReportUtil.getPageCountByDivide(list.size(), page.getEachItemsOfPage())) + "\"}";
- int f = 1;
- boolean dataset = false;
- for (int i = 0; i < list.size(); i++) {
- Object[] o1 = (Object[]) list.get(i);
- if (o1 != null && o1.length >= 10) {
- String sdf_date = ReportUtil.objectToString(o1[0]);
- String agent_count = ReportUtil.objectToString(o1[1]);
- agent_count=ReportUtil.getNumberFormatForCount(agent_count);
- String voice_count = ReportUtil.objectToString(o1[2]);
- voice_count=ReportUtil.getNumberFormatForCount(voice_count);
- String fail_count = ReportUtil.objectToString(o1[3]);
- fail_count=ReportUtil.getNumberFormatForCount(fail_count);
- String avg_ring_time = ReportUtil.objectToString(o1[4]);
- String avg_ring_count = ReportUtil.objectToString(o1[5]);
- avg_ring_count=ReportUtil.getNumberFormatForCount(avg_ring_count);
- String sum_long_time = ReportUtil.objectToString(o1[6]);
- String avg_long_time = ReportUtil.objectToString(o1[7]);
- String sum_consult_count = ReportUtil.objectToString(o1[8]);
- sum_consult_count=ReportUtil.getNumberFormatForCount(sum_consult_count);
- String sum_transfer_count = ReportUtil
- .objectToString(o1[9]);
- sum_transfer_count=ReportUtil.getNumberFormatForCount(sum_transfer_count);
- // 列表数据
- InsummaryResult is = new InsummaryResult(sdf_date,
- agent_count, voice_count, fail_count,
- avg_ring_time, avg_ring_count, sum_long_time,
- avg_long_time, sum_consult_count,
- sum_transfer_count);
- // {"agent_count":"12","avg_long_time":"345","avg_ring_count":"","avg_ring_time":"","fail_count":"","sdf_date":"","sum_consult_count":"","sum_long_time":"","sum_transfer_count":"","voice_count":""}
- String isJson = ReportUtil.getJsonFromObject(is);
- if(i<page.getEachItemsOfPage()){
- if (f == 1) {
- listJson += isJson;
- } else {
- listJson += "," + isJson;
- }
- }
- f++;
- // 报表dataString11的数据
- rptJson11Set += "<set label='" + sdf_date + "' value='"
- + voice_count + "' />";
- // 报表dataString12的数据
- rptJson12categories += "<category label='" + sdf_date
- + "' />";
- rptJson12dataset1 += "<set value='" + avg_ring_time
- + "' />";
- rptJson12dataset2 += "<set value='" + avg_long_time
- + "' />";
- // 报表dataString13的数据
- rptJson13set += "<set label='"
- + sdf_date
- + "' value='"
- + ReportUtil.getTwoStrDivision(fail_count,
- voice_count) + "' />";
- }// end if
- dataset = true;
- }// end for
- listJson += "]";
- rptJson11 += rptJson11Set;
- rptJson11 += "<styles>"
- + "<definition><style type='font' name='CaptionFont' size='15' color='666666' />"
- + "<style type='font' name='SubCaptionFont' bold='0' />"
- + "</definition><application>"
- + "<apply toObject='caption' styles='CaptionFont' />"
- + "<apply toObject='SubCaption' styles='SubCaptionFont' />"
- + "</application>" + "</styles>" + "</chart>'";
- rptJson12categories += "</categories>";
- rptJson12dataset1 += "</dataset>";
- rptJson12dataset2 += "</dataset>";
- rptJson12 += rptJson12categories;
- rptJson12 += rptJson12dataset1;
- rptJson12 += rptJson12dataset2;
- rptJson12 += "<styles>"
- + "<definition>"
- + "<style type='font' name='CaptionFont' color='666666' size='15' />"
- + "<style type='font' name='SubCaptionFont' bold='0' />"
- + "</definition><application>"
- + "<apply toObject='caption' styles='CaptionFont' />"
- + "<apply toObject='SubCaption' styles='SubCaptionFont' />"
- + "</application>" + "</styles>" + "</chart>'";
- rptJson13 += rptJson13set;
- rptJson13 += "<styles>"
- + "<definition><style type='font' name='CaptionFont' size='15' color='666666' />"
- + "<style type='font' name='SubCaptionFont' bold='0' />"
- + "</definition><application>"
- + "<apply toObject='caption' styles='CaptionFont' />"
- + "<apply toObject='SubCaption' styles='SubCaptionFont' />"
- + "</application>" + "</styles></chart>'";
- // {"dataString11":"xxxxx"}
- if (dataset) {
- dataString11 += "\"dataString11\":{\"data\":\"" + rptJson11
- + "\"}";
- dataString12 += "\"dataString12\":{\"data\":\"" + rptJson12
- + "\"}";
- dataString13 += "\"dataString13\":{\"data\":\"" + rptJson13
- + "\"}";
- } else {
- dataString11 += "\"dataString11\":{\"data\":\"nodata\"}";
- dataString12 += "\"dataString12\":{\"data\":\"nodata\"}";
- dataString13 += "\"dataString13\":{\"data\":\"nodata\"}";
- }
- // [{"agent_count":"12","avg_long_time":"345"},{}]
- res += "{\"all\":{" + pageJson + "," + listJson + ","
- + dataString11 + "," + dataString12 + "," + dataString13
- + "}}";
- System.out.println("json结果:\n" + res);
- }// end if
- return res;
- }
- // 分页某一页的数据
- public String getPageJsonForInSummary(String fromdate, String todate,
- String datetype, GreenPageVo page) {
- String jsonData = "";
- List list = new ArrayList();
- String sql = "select vo.* " + " ,con.sum_consult_count "
- + " ,con.sum_transfer_count " + " from " + " ( "
- + " select " + " sdf_date "
- + " ,count(distinct a.agent_id) agent_count "
- + " ,count(voice_id) voice_count"
- + " ,sum(decode(a.start_time,null,1,'',1,0)) fail_count"
- + " ,avg(ring_long_time)/60 avg_ring_time"
- + " ,avg(decode(voice_id,null,0,1)) avg_ring_count"
- + " ,sum(long_time)/60 sum_long_time "
- + " ,avg(long_time)/60 avg_long_time " + " from "
- + " ( " + " select "
- + " substr(ring_time,0,10) sdf_date "
- + " , t.* from hd_agent_voice_seq t " + " where 1=1 ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and t.ring_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and t.ring_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " and t.voice_direct_id='0' "
- + " ) a "
- + " group by sdf_date "
- + " ) vo, "
- + " ( "
- + " select "
- + " zc.sdf_consult_date "
- + " ,sum(decode(zc.consult_start_time,null,0,'',0,1)) sum_consult_count "
- + " ,sum(decode(zc.transfer_flag,null,0,'',0,0,0,1)) sum_transfer_count "
- + " ,sum(decode(zc.conference_start_time,null,0,'',0,1)) sum_conference_count "
- + " ,sum(consult_long_time)/60 sum_consult_time "
- + " ,sum(conference_long_time)/60 sum_consult_time "
- + " from "
- + " ( "
- + " select za.*,substr(consult_start_time,0,10) sdf_consult_date,zb.voice_id bvoice_id "
- + " from hd_consult_log za , hd_agent_voice_seq zb "
- + " where za.voice_id=zb.voice_id(+) ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and za.consult_start_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and za.consult_start_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " ) zc " + " group by sdf_consult_date " + " ) con "
- + " where vo.sdf_date=con.sdf_consult_date(+) "
- + " order by vo.sdf_date ";
- System.out.println("getAllListForInSummary--" + sql);
- String countSql = "select count(sdf_date) countNumber " + " from ("
- + sql + " )";
- List listCount = new ArrayList();
- listCount = this.queryNativeSQL(countSql);
- PaginationSupport pageset=new PaginationSupport();
- pageset.setTotalCount(this.getCountNumberFromList(listCount));// 记录总数
- pageset.setCountOnEachPage(page.getEachItemsOfPage());
- pageset.setPage(page.getCurrPage());
- list = this.queryNativeSQL(sql, pageset);
- jsonData = this.getPageJsonForInSummaryByList(list, pageset);
- return jsonData;
- }
- public int getCountNumberFromList(List list) {
- int a = 0;
- if (list == null) {
- } else {
- Object o1 = (Object) list.get(0);
- String cc = ReportUtil.objectToString(o1);
- String cc2=ReportUtil.getNumberFormatForCount(cc);
- a = Integer.valueOf(cc2);
- System.out.println("getCount--" + a);
- }
- return a;
- }
- // 列表,分页,报表 的数据
- public String getPageJsonForInSummaryByList(List list,
- PaginationSupport pageset) {
- String res = "";
- // "page":{"currPage":"6","pageCount":"67"}
- String pageJson = "";
- // [{"agent_count":"12","avg_long_time":"345"},{}]
- String listJson = "\"listdata\":[";
- if (list != null) {
- // 分页显示数据
- pageJson = "\"page\":{\"currPage\":\"" + pageset.getPage()
- + "\",\"pageCount\":\"" + pageset.getPageCount() + "\"}";
- int f = 1;
- for (int i = 0; i < list.size(); i++) {
- Object[] o1 = (Object[]) list.get(i);
- if (o1 != null && o1.length >= 10) {
- String sdf_date = ReportUtil.objectToString(o1[0]);
- String agent_count = ReportUtil.objectToString(o1[1]);
- agent_count=ReportUtil.getNumberFormatForCount(agent_count);
- String voice_count = ReportUtil.objectToString(o1[2]);
- voice_count=ReportUtil.getNumberFormatForCount(voice_count);
- String fail_count = ReportUtil.objectToString(o1[3]);
- fail_count=ReportUtil.getNumberFormatForCount(fail_count);
- String avg_ring_time = ReportUtil.objectToString(o1[4]);
- String avg_ring_count = ReportUtil.objectToString(o1[5]);
- avg_ring_count=ReportUtil.getNumberFormatForCount(avg_ring_count);
- String sum_long_time = ReportUtil.objectToString(o1[6]);
- String avg_long_time = ReportUtil.objectToString(o1[7]);
- String sum_consult_count = ReportUtil.objectToString(o1[8]);
- sum_consult_count=ReportUtil.getNumberFormatForCount(sum_consult_count);
- String sum_transfer_count = ReportUtil
- .objectToString(o1[9]);
- sum_transfer_count=ReportUtil.getNumberFormatForCount(sum_transfer_count);
- // 列表数据
- InsummaryResult is = new InsummaryResult(sdf_date,
- agent_count, voice_count, fail_count,
- avg_ring_time, avg_ring_count, sum_long_time,
- avg_long_time, sum_consult_count,
- sum_transfer_count);
- // {"agent_count":"12","avg_long_time":"345","avg_ring_count":"","avg_ring_time":"","fail_count":"","sdf_date":"","sum_consult_count":"","sum_long_time":"","sum_transfer_count":"","voice_count":""}
- String isJson = ReportUtil.getJsonFromObject(is);
- if (f == 1) {
- listJson += isJson;
- } else {
- listJson += "," + isJson;
- }
- f++;
- }// end if
- }// end for
- listJson += "]";
- // [{"agent_count":"12","avg_long_time":"345"},{}]
- res += "{\"all\":{" + pageJson + "," + listJson + "}}";
- System.out.println("分页json结果:\n" + res);
- }// end if
- return res;
- }
- // 对比查询
- public String getCompareDataForInSummary(String fromdate, String todate,
- String datetype) {
- String jsonData = "";
- List list = new ArrayList();
- String sql = "select vo.* " + " ,con.sum_consult_count "
- + " ,con.sum_transfer_count " + " from " + " ( "
- + " select " + " sdf_date "
- + " ,count(distinct a.agent_id) agent_count "
- + " ,count(voice_id) voice_count"
- + " ,sum(decode(a.start_time,null,1,'',1,0)) fail_count"
- + " ,avg(ring_long_time)/60 avg_ring_time"
- + " ,avg(decode(voice_id,null,0,1)) avg_ring_count"
- + " ,sum(long_time)/60 sum_long_time "
- + " ,avg(long_time)/60 avg_long_time " + " from "
- + " ( " + " select ";
- sql += this.getSubStrDate(datetype);
- sql += " , t.* from hd_agent_voice_seq t " + " where 1=1 ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and t.ring_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and t.ring_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " and t.voice_direct_id='0' "
- + " ) a "
- + " group by sdf_date "
- + " ) vo, "
- + " ( "
- + " select "
- + " zc.sdf_consult_date "
- + " ,sum(decode(zc.consult_start_time,null,0,'',0,1)) sum_consult_count "
- + " ,sum(decode(zc.transfer_flag,null,0,'',0,0,0,1)) sum_transfer_count "
- + " ,sum(decode(zc.conference_start_time,null,0,'',0,1)) sum_conference_count "
- + " ,sum(consult_long_time)/60 sum_consult_time "
- + " ,sum(conference_long_time)/60 sum_consult_time "
- + " from "
- + " ( "
- + " select za.*,substr(consult_start_time,0,10) sdf_consult_date,zb.voice_id bvoice_id "
- + " from hd_consult_log za , hd_agent_voice_seq zb "
- + " where za.voice_id=zb.voice_id(+) ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and za.consult_start_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and za.consult_start_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " ) zc " + " group by sdf_consult_date " + " ) con "
- + " where vo.sdf_date=con.sdf_consult_date(+) "
- + " order by vo.sdf_date ";
- System.out.println("getAllListForInSummary--" + sql);
- list = this.queryNativeSQL(sql);
- jsonData = this.getCompareJsonForInSummaryByList(list);
- return jsonData;
- }
- public String getSubStrDate(String datetype) {
- String sub = " substr(ring_time,0,10) sdf_date ";
- // substr(ring_time,0,10) sdf_date --按天
- // --substr(ring_time,0,4) sdf_date --按年
- // -- substr(ring_time,0,7) sdf_date --按月份
- if (datetype == null) {
- } else if (datetype.trim().equals("")) {
- } else {
- String dd = datetype.trim();
- if (dd.equals("dtian")) {
- sub = " substr(ring_time,0,10) sdf_date ";
- } else if (dd.equals("djidu") || dd.equals("dyuefen")) {
- sub = " substr(ring_time,0,7) sdf_date ";
- } else if (dd.equals("dnian")) {
- sub = " substr(ring_time,0,4) sdf_date ";
- }
- }
- return sub;
- }
- // 折线的json
- public String getCompareJsonForInSummaryByList(List list) {
- String res = "";
- // 第一张折线图--呼叫总数
- String dataString21 = "";
- String rpt21 = "<chart canvasPadding='10' caption='呼叫总数' yAxisName='单位:个' formatNumberScale='0' "
- + " bgColor='F7F7F7, E9E9E9' numVDivLines='10' divLineAlpha='30' "
- + " labelPadding ='30' yAxisValuesPadding ='30' showValues='1' rotateValues='2' valuePosition='auto'>";
- String categories21 = "<categories>";
- String dataset211 = "<dataset seriesName='呼叫总数' color='F6BD0F'>";
- // 第二张折线图--总通话时长
- String dataString22 = "<chart canvasPadding='10' caption='总通话时长' yAxisName='单位:分钟'"
- + " formatNumberScale='0' bgColor='F7F7F7, E9E9E9'"
- + " numVDivLines='10' divLineAlpha='30' labelPadding ='30' yAxisValuesPadding ='30' "
- + "showValues='1' rotateValues='2' valuePosition='auto'>";
- String rpt22 = "";
- String categories22 = "<categories>";
- String dataset221 = "<dataset seriesName='总通话时长' color='F6BD0F'>";
- // 第三张折线图--平均振铃时长
- String dataString23 = "<chart canvasPadding='10' caption='平均振铃时长' yAxisName='单位:分钟'"
- + " formatNumberScale='0' bgColor='F7F7F7, E9E9E9'"
- + " numVDivLines='10' divLineAlpha='30' labelPadding ='30' yAxisValuesPadding ='30' "
- + "showValues='1' rotateValues='2' valuePosition='auto'>";
- String rpt23 = "";
- String categories23 = "<categories>";
- String dataset231 = "<dataset seriesName='平均振铃时长' color='F6BD0F'>";
- // 第四张折线图--平均通话时长
- String dataString24 = "<chart canvasPadding='10' caption='平均通话时长' yAxisName='单位:分钟'"
- + " formatNumberScale='0' bgColor='F7F7F7, E9E9E9'"
- + " numVDivLines='10' divLineAlpha='30' labelPadding ='30' yAxisValuesPadding ='30' "
- + "showValues='1' rotateValues='2' valuePosition='auto'>";
- String rpt24 = "";
- String categories24 = "<categories>";
- String dataset241 = "<dataset seriesName='平均通话时长' color='F6BD0F'>";
- // 第五张折线图--呼损率
- String dataString25 = "<chart canvasPadding='10' caption='呼损率' yAxisName='单位:%' "
- + " numberSuffix='%' formatNumberScale='0' bgColor='F7F7F7, E9E9E9' "
- + "numVDivLines='10' divLineAlpha='30' labelPadding ='30' yAxisValuesPadding ='30' "
- + "showValues='1' rotateValues='2' valuePosition='auto'>";
- String rpt25 = "";
- String categories25 = "<categories>";
- String dataset251 = "<dataset seriesName='呼损率' color='F6BD0F'>";
- if (list != null) {
- int f = 1;
- // 平均线的值
- double line_voice_count = 0;
- double line_sum_long_time = 0;
- double line_avg_ring_time = 0;
- double line_avg_long_time = 0;
- double line_husunlv = 0;
- boolean dataset = false;
- // boolean dataset_sum_long_time=false;
- // boolean dataset_avg_ring_time=false;
- // boolean dataset_avg_long_time=false;
- for (int i = 0; i < list.size(); i++) {
- Object[] o1 = (Object[]) list.get(i);
- if (o1 != null && o1.length >= 10) {
- String sdf_date = ReportUtil.objectToString(o1[0]);
- String agent_count = ReportUtil.objectToString(o1[1]);
- agent_count=ReportUtil.getNumberFormatForCount(agent_count);
- String voice_count = ReportUtil.objectToString(o1[2]);
- voice_count=ReportUtil.getNumberFormatForCount(voice_count);
- String fail_count = ReportUtil.objectToString(o1[3]);
- fail_count=ReportUtil.getNumberFormatForCount(fail_count);
- String avg_ring_time = ReportUtil.objectToString(o1[4]);
- String avg_ring_count = ReportUtil.objectToString(o1[5]);
- avg_ring_count=ReportUtil.getNumberFormatForCount(avg_ring_count);
- String sum_long_time = ReportUtil.objectToString(o1[6]);
- String avg_long_time = ReportUtil.objectToString(o1[7]);
- String sum_consult_count = ReportUtil.objectToString(o1[8]);
- sum_consult_count=ReportUtil.getNumberFormatForCount(sum_consult_count);
- String sum_transfer_count = ReportUtil
- .objectToString(o1[9]);
- sum_transfer_count=ReportUtil.getNumberFormatForCount(sum_transfer_count);
- categories21 += "<category label='" + sdf_date + "' />";
- dataset211 += "<set value='" + voice_count + "' />";
- categories22 += "<category label='" + sdf_date + "' />";
- dataset221 += "<set value='" + sum_long_time + "' />";
- categories23 += "<category label='" + sdf_date + "' />";
- dataset231 += "<set value='" + avg_ring_time + "' />";
- categories24 += "<category label='" + sdf_date + "' />";
- dataset241 += "<set value='" + avg_long_time + "' />";
- categories25 += "<category label='" + sdf_date + "' /> ";
- dataset251 += "<set value='"
- + ReportUtil.getTwoStrDivision(fail_count,
- voice_count) + "' /> ";
- line_voice_count = ReportUtil.getSumLeijiDouble(
- line_voice_count, voice_count);
- line_sum_long_time = ReportUtil.getSumLeijiDouble(
- line_sum_long_time, sum_long_time);
- line_avg_ring_time = ReportUtil.getSumLeijiDouble(
- line_avg_ring_time, avg_ring_time);
- line_avg_long_time = ReportUtil.getSumLeijiDouble(
- line_avg_long_time, avg_long_time);
- line_husunlv = ReportUtil.getSumLeijiDouble(line_husunlv,
- ReportUtil.getTwoStrDivision(fail_count,
- voice_count));
- }// end if
- f++;
- dataset = true;
- }// end for
- line_voice_count = ReportUtil.getAvgLeijiDouble(line_voice_count,
- (f - 1));
- line_sum_long_time = ReportUtil.getAvgLeijiDouble(
- line_sum_long_time, (f - 1));
- line_avg_ring_time = ReportUtil.getAvgLeijiDouble(
- line_avg_ring_time, (f - 1));
- line_avg_long_time = ReportUtil.getAvgLeijiDouble(
- line_avg_long_time, (f - 1));
- line_husunlv = ReportUtil.getAvgLeijiDouble(line_husunlv, (f - 1));
- categories21 += "</categories>";
- dataset211 += "</dataset>";
- rpt21 += categories21 + dataset211;
- rpt21 += "<trendlines><line startValue='"
- + line_voice_count
- + "' displayValue='Average:"
- + line_voice_count
- + "' color='009900' valueOnRight='1' /></trendlines></chart>";
- dataString21 += rpt21;
- if (dataset) {
- dataString21 = "\"dataString21\":{\"data\":\"" + dataString21
- + "\"}";
- } else {
- dataString21 = "\"dataString21\":{\"data\":\"nodata\"}";
- }
- // //
- categories22 += "</categories>";
- dataset221 += "</dataset>";
- rpt22 += categories22;
- rpt22 += dataset221;
- rpt22 += "<trendlines><line startValue='"
- + line_sum_long_time
- + "' displayValue='Average:"
- + line_sum_long_time
- + "' color='009900' valueOnRight='1' /></trendlines></chart>";
- dataString22 += rpt22;
- if (dataset) {
- dataString22 = "\"dataString22\":{\"data\":\"" + dataString22
- + "\"}";
- } else {
- dataString22 = "\"dataString22\":{\"data\":\"nodata\"}";
- }
- // ///
- categories23 += "</categories>";
- dataset231 += "</dataset>";
- rpt23 += categories23;
- rpt23 += dataset231;
- rpt23 += "<trendlines><line startValue='"
- + line_avg_ring_time
- + "' displayValue='Average:"
- + line_avg_ring_time
- + "' color='009900' valueOnRight='1' /></trendlines></chart>";
- dataString23 += rpt23;
- if (dataset) {
- dataString23 = "\"dataString23\":{\"data\":\"" + dataString23
- + "\"}";
- } else {
- dataString23 = "\"dataString23\":{\"data\":\"nodata\"}";
- }
- // ///
- categories24 += "</categories>";
- dataset241 += "</dataset>";
- rpt24 += categories24;
- rpt24 += dataset241;
- rpt24 += "<trendlines><line startValue='"
- + line_avg_long_time
- + "' displayValue='Average:"
- + line_avg_long_time
- + "' color='009900' valueOnRight='1' /></trendlines></chart>";
- dataString24 += rpt24;
- if (dataset) {
- dataString24 = "\"dataString24\":{\"data\":\"" + dataString24
- + "\"}";
- } else {
- dataString24 = "\"dataString24\":{\"data\":\"nodata\"}";
- }
- //
- categories25 += "</categories>";
- dataset251 += "</dataset>";
- rpt25 += categories25;
- rpt25 += dataset251;
- rpt25 += "<trendlines><line startValue='"
- + line_husunlv
- + "' displayValue='Average:"
- + line_husunlv
- + "' color='009900' valueOnRight='1' /></trendlines></chart>";
- dataString25 += rpt25;
- if (dataset) {
- dataString25 = "\"dataString25\":{\"data\":\"" + dataString25
- + "\"}";
- } else {
- dataString25 = "\"dataString25\":{\"data\":\"nodata\"}";
- }
- // [{"agent_count":"12","avg_long_time":"345"},{}]
- res += "{\"all\":{" + dataString21 + "," + dataString22 + ","
- + dataString23 + "," + dataString24 + "," + dataString25
- + "}}";
- System.out.println("折线json结果:\n" + res);
- }// end if
- return res;
- }
- // ///明细开始/
- // 点击查询,明细sql
- public String getAllDataForInDetail(String fromdate, String todate,
- String datetype,GreenPageVo page) {
- String jsonData = "";
- List list = new ArrayList();
- String sql = " select "
- + " agent_name "
- + " ,count(voice_id) voice_count "
- + " ,sum(decode(a.start_time,null,1,'',1,0)) fail_count "
- + " ,count(voice_id)-sum(decode(a.start_time,null,1,'',1,0)) success_count "
- + " ,sum(long_time)/60 sum_long_time "
- + " ,avg(long_time)/60 avg_long_time "
- + " ,avg(ring_long_time)/60 avg_ring_time "
- + " ,avg(decode(voice_id,null,0,1)) avg_ring_count "
- + " from " + " ( " + " select "
- + " t.* from hd_agent_voice_seq t " + " where 1=1 ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and t.ring_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and t.ring_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " and t.voice_direct_id='0' " + " ) a "
- + " group by agent_name ";
- System.out.println("getAllDataForInDetail--" + sql);
- list = this.queryNativeSQL(sql);
- jsonData = this.getAllJsonForInDetailByList(list,page);
- return jsonData;
- }
- public String getAllJsonForInDetailByList(List list,GreenPageVo page){
- String res="";
- //"page":{"currPage":"6","pageCount":"67"}
- String pageJson="";
- //[{"agent_count":"12","avg_long_time":"345"},{}]
- String listJson="\"listdata\":[";
- String dataString11="";
- String dataString12="";
- String dataString13="";
- String dataString14="";
- String dataString15="";
- String rptJson11="<chart palette='2' caption='呼入总数--通话次数--呼损次数' " +
- "showLabels='1' showvalues='0' numberPrefix='' showSum='1' " +
- "decimals='0' useRoundEdges='1' legendBorderAlpha='0' formatNumberScale='0'>";
- String rptJson11categories="<categories>";
- String rptJson11Set1="<dataset seriesName='呼入总数' color='AFD8F8' showValues='0'>";
- String rptJson11Set2="<dataset seriesName='通话次数 ' color='F6BD0F' showValues='0'>";
- String rptJson11Set3="<dataset seriesName='呼损次数' color='8BBA00' showValues='0'>";
- String rptJson12="<chart caption='平均通话时长 ' bgColor='E0EEEE,90B1DE' palette='2'" +
- " formatNumberScale='0' numberPrefix='' labeldisplay='ROTATE' " +
- "slantLabels='1' sNumberSuffix=' pcs.' showValues='1' labelDisplay='STAGGER'>";
- String rptJson12dataset1="";
- String rptJson13="<chart caption='平均振铃时长 ' bgColor='E0EEEE,90B1DE' palette='2'" +
- " formatNumberScale='0' numberPrefix='' labeldisplay='ROTATE' " +
- "slantLabels='1' sNumberSuffix=' pcs.' showValues='1' labelDisplay='STAGGER'>";
- String rptJson13dataset1="";
- String rptJson14="<chart caption='通话次数' showValues='1' baseFontColor='FFFFFF'" +
- " bgColor='2E4A89,90B1DE' bgAlpha='100,100' pieYScale='30' " +
- "pieSliceDepth='8' smartLineColor='FFFFFF'>";
- String rptJson14dataset1="";
- String rptJson15="<chart caption='通话时长' showValues='1' baseFontColor='FFFFFF'" +
- " bgColor='2E4A89,90B1DE' bgAlpha='100,100' pieYScale='30' " +
- "pieSliceDepth='8' smartLineColor='FFFFFF'>";
- String rptJson15dataset1="";
- if(list!=null){
- //分页显示数据
- pageJson="\"page\":{\"currPage\":\"1\",\"pageCount\":\""
- +(ReportUtil.getPageCountByDivide(list.size(), page.getEachItemsOfPage()))+"\"}";
- int f=1;
- boolean dataset=false;
- for(int i=0;i<list.size();i++){
- Object[] o1=(Object[])list.get(i);
- if(o1!=null&&o1.length>=8){
- String agent_name=ReportUtil.objectToString(o1[0]);
- String voice_count=ReportUtil.objectToString(o1[1]);
- voice_count=ReportUtil.getNumberFormatForCount(voice_count);
- String fail_count=ReportUtil.objectToString(o1[2]);
- fail_count=ReportUtil.getNumberFormatForCount(fail_count);
- String success_count=ReportUtil.objectToString(o1[3]);
- success_count=ReportUtil.getNumberFormatForCount(success_count);
- String sum_long_time=ReportUtil.objectToString(o1[4]);
- String avg_long_time=ReportUtil.objectToString(o1[5]);
- String avg_ring_time=ReportUtil.objectToString(o1[6]);
- String avg_ring_count=ReportUtil.objectToString(o1[7]);
- avg_ring_count=ReportUtil.getNumberFormatForCount(avg_ring_count);
- //列表数据
- InDetailResult id = new InDetailResult(agent_name, voice_count, fail_count, success_count, sum_long_time, avg_long_time, avg_ring_time, avg_ring_count);
- //{"agent_count":"12","avg_long_time":"345","avg_ring_count":"","avg_ring_time":"","fail_count":"","sdf_date":"","sum_consult_count":"","sum_long_time":"","sum_transfer_count":"","voice_count":""}
- String isJson=ReportUtil.getJsonFromObject(id);
- if(i<page.getEachItemsOfPage()){
- if(f==1){
- listJson+=isJson;
- }else{
- listJson+=","+isJson;
- }
- }
- f++;
- //报表dataString11的数据
- rptJson11categories+="<category label='"+agent_name+"' />";
- rptJson11Set1+="<set value='"+voice_count+"' />";
- rptJson11Set2+="<set value='"+success_count+"' />";
- rptJson11Set3+="<set value='"+fail_count+"' />";
- //报表dataString12的数据
- rptJson12dataset1+="<set value='"+avg_long_time+"' label='"+agent_name+"' color='"+ReportUtil.getColorByInt(i)+"'/>";
- //报表dataString13的数据
- rptJson13dataset1+="<set value='"+avg_ring_time+"' label='"+agent_name+"' color='"+ReportUtil.getColorByInt(i)+"'/>";
- //报表dataString14的数据
- rptJson14dataset1+="<set label='"+agent_name+"' value='"+success_count+"' />";
- //报表dataString15的数据
- rptJson15dataset1+="<set label='"+agent_name+"' value='"+sum_long_time+"' />";
- }//end if
- dataset=true;
- }//end for
- listJson+="]";
- rptJson11categories+="</categories>";
- rptJson11Set1+="</dataset>";
- rptJson11Set2+="</dataset>";
- rptJson11Set3+="</dataset>";
- rptJson11+=rptJson11categories;
- // rptJson11+=rptJson11Set1;
- rptJson11+=rptJson11Set2;
- rptJson11+=rptJson11Set3;
- rptJson11+="</chart>";
- rptJson12+=rptJson12dataset1;
- rptJson12+="<styles>" +
- "<definition><style type='font' name='CaptionFont' size='15' color='666666' />" +
- "<style type='font' name='SubCaptionFont' bold='0' />" +
- "</definition><application>" +
- "<apply toObject='caption' styles='CaptionFont' />" +
- "<apply toObject='SubCaption' styles='SubCaptionFont' />" +
- "</application>" +
- "</styles>" +
- "</chart>";
- rptJson13+=rptJson13dataset1;
- rptJson13+="<styles>" +
- "<definition><style type='font' name='CaptionFont' size='15' color='666666' />" +
- "<style type='font' name='SubCaptionFont' bold='0' />" +
- "</definition><application>" +
- "<apply toObject='caption' styles='CaptionFont' />" +
- "<apply toObject='SubCaption' styles='SubCaptionFont' />" +
- "</application>" +
- "</styles>" +
- "</chart>";
- rptJson14+=rptJson14dataset1;
- rptJson14+="<styles>" +
- "<definition>" +
- "<style name='CaptionFont' type='FONT' size='12' bold='1' />" +
- "<style name='LabelFont' type='FONT' color='2E4A89' bgColor='FFFFFF' bold='1' />" +
- "<style name='ToolTipFont' type='FONT' bgColor='2E4A89' borderColor='2E4A89' />" +
- "</definition>" +
- "<application>" +
- "<apply toObject='CAPTION' styles='CaptionFont' />" +
- "<apply toObject='DATALABELS' styles='LabelFont' />" +
- "<apply toObject='TOOLTIP' styles='ToolTIpFont' />" +
- "</application>" +
- "</styles>" +
- "</chart>";
- rptJson15+=rptJson15dataset1;
- rptJson15+="<styles>" +
- "<definition>" +
- "<style name='CaptionFont' type='FONT' size='12' bold='1' />" +
- "<style name='LabelFont' type='FONT' color='2E4A89' bgColor='FFFFFF' bold='1' />" +
- "<style name='ToolTipFont' type='FONT' bgColor='2E4A89' borderColor='2E4A89' />" +
- "</definition>" +
- "<application>" +
- "<apply toObject='CAPTION' styles='CaptionFont' />" +
- "<apply toObject='DATALABELS' styles='LabelFont' />" +
- "<apply toObject='TOOLTIP' styles='ToolTIpFont' />" +
- "</application>" +
- "</styles>" +
- "</chart>";
- //{"dataString11":"xxxxx"}
- if(dataset){
- dataString11+="\"dataString11\":{\"data\":\""+rptJson11+"\"}";
- dataString12+="\"dataString12\":{\"data\":\""+rptJson12+"\"}";
- dataString13+="\"dataString13\":{\"data\":\""+rptJson13+"\"}";
- dataString14+="\"dataString14\":{\"data\":\""+rptJson14+"\"}";
- dataString15+="\"dataString15\":{\"data\":\""+rptJson15+"\"}";
- }else{
- dataString11+="\"dataString11\":{\"data\":\"nodata\"}";
- dataString12+="\"dataString12\":{\"data\":\"nodata\"}";
- dataString13+="\"dataString13\":{\"data\":\"nodata\"}";
- dataString14+="\"dataString14\":{\"data\":\"nodata\"}";
- dataString15+="\"dataString15\":{\"data\":\"nodata\"}";
- }
- //[{"agent_count":"12","avg_long_time":"345"},{}]
- res+="{\"all\":{"+pageJson+","+listJson+","+dataString11+","+dataString12+","+dataString13+","+dataString14+","+dataString15+"}}";
- System.out.println("json结果:\n"+res);
- }//end if
- return res;
- }
- // 明细分页 的数据
- public String getPageJsonForInDetail(String fromdate,String todate,String datetype,GreenPageVo page){
- String jsonData = "";
- List list = new ArrayList();
- String sql = " select "
- + " agent_name "
- + " ,count(voice_id) voice_count "
- + " ,sum(decode(a.start_time,null,1,'',1,0)) fail_count "
- + " ,count(voice_id)-sum(decode(a.start_time,null,1,'',1,0)) success_count "
- + " ,sum(long_time)/60 sum_long_time "
- + " ,avg(long_time)/60 avg_long_time "
- + " ,avg(ring_long_time)/60 avg_ring_time "
- + " ,avg(decode(voice_id,null,0,1)) avg_ring_count "
- + " from " + " ( " + " select "
- + " t.* from hd_agent_voice_seq t " + " where 1=1 ";
- if (fromdate != null && !fromdate.trim().equals("")) {
- sql += " and t.ring_time>='"
- + ReportUtil.dealDateByDatetype(fromdate.trim(), datetype,
- true, todate) + "' ";
- }
- if (todate != null && !todate.trim().equals("")) {
- sql += " and t.ring_time<='"
- + ReportUtil.dealDateByDatetype(todate.trim(), datetype,
- false, todate) + "' ";
- }
- sql += " and t.voice_direct_id='0' " + " ) a "
- + " group by agent_name ";
- System.out.println("getAllListForInSummary--" + sql);
- String countSql = "select count(agent_name) countNumber " + " from ("
- + sql + " )";
- List listCount = new ArrayList();
- listCount = this.queryNativeSQL(countSql);
- PaginationSupport pageset=new PaginationSupport();
- pageset.setTotalCount(this.getCountNumberFromList(listCount));// 记录总数
- pageset.setCountOnEachPage(page.getEachItemsOfPage());
- pageset.setPage(page.getCurrPage());
- list = this.queryNativeSQL(sql, pageset);
- jsonData = this.getPageJsonForInDetailByList(list, pageset);
- return jsonData;
- }
- // 明细分页 的数据拼装
- public String getPageJsonForInDetailByList(List list,
- PaginationSupport pageset) {
- String res = "";
- // "page":{"currPage":"6","pageCount":"67"}
- String pageJson = "";
- // [{"agent_count":"12","avg_long_time":"345"},{}]
- String listJson = "\"listdata\":[";
- if (list != null) {
- // 分页显示数据
- pageJson = "\"page\":{\"currPage\":\"" + pageset.getPage()
- + "\",\"pageCount\":\"" + pageset.getPageCount() + "\"}";
- int f = 1;
- boolean dataset=false;
- for(int i=0;i<list.size();i++){
- Object[] o1=(Object[])list.get(i);
- if(o1!=null&&o1.length>=8){
- String agent_name=ReportUtil.objectToString(o1[0]);
- String voice_count=ReportUtil.objectToString(o1[1]);
- voice_count=ReportUtil.getNumberFormatForCount(voice_count);
- String fail_count=ReportUtil.objectToString(o1[2]);
- fail_count=ReportUtil.getNumberFormatForCount(fail_count);
- String success_count=ReportUtil.objectToString(o1[3]);
- success_count=ReportUtil.getNumberFormatForCount(success_count);
- String sum_long_time=ReportUtil.objectToString(o1[4]);
- String avg_long_time=ReportUtil.objectToString(o1[5]);
- String avg_ring_time=ReportUtil.objectToString(o1[6]);
- String avg_ring_count=ReportUtil.objectToString(o1[7]);
- avg_ring_count=ReportUtil.getNumberFormatForCount(avg_ring_count);
- //列表数据
- InDetailResult id = new InDetailResult(agent_name, voice_count, fail_count, success_count, sum_long_time, avg_long_time, avg_ring_time, avg_ring_count);
- //{"agent_count":"12","avg_long_time":"345","avg_ring_count":"","avg_ring_time":"","fail_count":"","sdf_date":"","sum_consult_count":"","sum_long_time":"","sum_transfer_count":"","voice_count":""}
- String isJson=ReportUtil.getJsonFromObject(id);
- if(f==1){
- listJson+=isJson;
- }else{
- listJson+=","+isJson;
- }
- f++;
- }//end if
- dataset=true;
- }//end for
- listJson += "]";
- // [{"agent_count":"12","avg_long_time":"345"},{}]
- res += "{\"all\":{" + pageJson + "," + listJson + "}}";
- System.out.println("分页json结果:\n" + res);
- }// end if
- return res;
- }
- }
内容很多,一般两个方法对应一个功能,一个查数据,
一个负责对查询的数据进行处理。
例如:
public String getPageJsonForInSummary(String fromdate, String todate,
String datetype, GreenPageVo page)
从前台接受参数,拼装sql查询数据。
其中jsonData = this.getPageJsonForInSummaryByList(list, pageset);
public String getPageJsonForInSummaryByList(List list,
PaginationSupport pageset)
负责对结果集处理数据,所有数据处理后,返回一个json对象,
把需要的数据全部返回到页面。
控制分页能正确显示的json部分:
pageJson = "\"page\":{\"currPage\":\"" + pageset.getPage()
+ "\",\"pageCount\":\"" + pageset.getPageCount() + "\"}";
struts配置
- <action name="rptInSummaryAll" class="org.hd.report.action.RptInSummaryAction" method="rptInSummaryAll">
- </action>
- <action name="rptInSummaryPage" class="org.hd.report.action.RptInSummaryAction" method="rptInSummaryPage">
- </action>
- <action name="rptInSummaryCompare" class="org.hd.report.action.RptInSummaryAction" method="rptInSummaryCompare">
- </action>
- <action name="judgeIsTwoRightDate" class="org.hd.report.action.RightDateJudgeAction" method="judgeIsTwoRightDate">
- </action>
- <action name="rptInDetailAll" class="org.hd.report.action.RptInDetailAction" method="rptInDetailAll">
- </action>
- <action name="rptInDetailPage" class="org.hd.report.action.RptInDetailAction" method="rptInDetailPage">
- </action>
效果: