-------------------后台代码
@At("/dateGroupDay")
@Ok("json:full")
public Object dateGroupDay() {
// 创建客户对象集合
List<CustomerManage> listCustomerManage = new ArrayList<CustomerManage>();
System.out.println("111111111111");
List<Object> list = null;
try {
//
String path = MachineTableConfigAction.class.getResource("/conf.ini").getFile();
File file = new File(path);
String text = FileOPeration.readFile(file);
String[] userName = text.split(";");
if (userName.length > 1) {
String userNameStr = userName[1].substring(userName[1].indexOf("=") + 1, userName[1].length());
String nodeId = userName[0].substring(userName[0].indexOf("=") + 1, userName[0].length());
String sqlstr = "select cm.id,user_name,user_pass,user_account,cm.node_id,priority,gatenum_id,database_id,login_url,"
+ "cm.app_id,cm.description,database_name,gatenum,gatenum_outside,sign_code "
+ "from node_manage nm,customer_manage cm,database_cust_manage dcm,gatenum_manage gm "
+ "where cm.node_id=nm.id and cm.database_id=dcm.id and cm.gatenum_id=gm.id and cm.node_id="
+ nodeId + "" + " and cm.user_name='" + userNameStr + "'";
Sql sql = Sqls.create(sqlstr);
sql.setCallback(Sqls.callback.entities());
Entity<CustomerManage> entity = daoTest.getEntity(CustomerManage.class);
sql.setEntity(entity);
daoTest.execute(sql).getResult();
listCustomerManage = sql.getList(CustomerManage.class);
}
System.err.println(listCustomerManage.size());
// 判断用户是否存在
if (listCustomerManage.size() > 0) {
for (int i = 0; i < listCustomerManage.size(); i++) {
String databaseName = listCustomerManage.get(i).getDatabaseName();
String sqlSent = "SELECT COUNT( DATE_FORMAT( SENTTIME, '%Y-%m-%d' ) ) AS count,"
+ "COUNT( CASE WHEN SMSSTATUS = 'DELIVRD' THEN 1 ELSE NULL END ) AS succeed,"
+ "COUNT( CASE WHEN SMSSTATUS != 'DELIVRD' AND SMSSTATUS IS NOT NULL THEN 1 ELSE NULL END ) AS Failure,"
+ "COUNT( CASE WHEN SMSSTATUS IS NULL THEN 1 ELSE NULL END ) AS wzh,"
+ "DATE_FORMAT( SENTTIME, '%Y-%m-%d' ) AS time " + "FROM " + databaseName
+ " .SMS_SENT WHERE DATE_FORMAT( NOW( ), '%Y-%m-%d' ) > DATE_FORMAT( SENTTIME, '%Y-%m-%d' ) "
+ "GROUP BY DATE_FORMAT( SENTTIME,'%Y-%m-%d' ) ORDER BY "
+ " DATE_FORMAT( SENTTIME, '%Y-%m-%d') ";
System.err.println("*-----------------------------" + sqlSent);
Sql create = Sqls.create(sqlSent);
create.setCallback(new SqlCallback() {
@Override
public ArrayList invoke(Connection con, ResultSet rs, Sql sql) throws SQLException {
ArrayList<Map> statistical = new ArrayList<>();
while (rs.next()) {
Map tempstatistical = new HashMap<>();
int count = rs.getInt("count");
int succeed = rs.getInt("succeed");
int Failure = rs.getInt("Failure");
int wzh = rs.getInt("wzh");
String time = rs.getString("time");
tempstatistical.put("count",count);
tempstatistical.put("succeed",succeed);
tempstatistical.put("Failure",Failure);
tempstatistical.put("wzh",wzh);
tempstatistical.put("time",time);
statistical.add(tempstatistical);
}
return statistical;
}
});
list = daoTest.execute(create).getList(Object.class);
}
}
System.err.println(list);
return new NutMap().setv("unitList", list);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
-------------前台
var SteptypeGrid;
$(function() {
SteptypeGrid = $('#SteptypeGrid')
.datagrid(
{
url : '${pageContext.request.contextPath}/dateGroupAction/dateGroupDay.miao',
fitColumns : true,
border : false,
striped : true,
rownumbers : true,
pagination : true,
singleSelect : true,
idField : 'unitList',
columns : [ [ {
field : 'count',
title : '总数',
width : '300'
}, {
field : 'succeed',
title : '成功',
width : '100',
hidden : false
}, {
field : 'Failure',
title : '失败',
width : '200',
}, {
field : 'wzh',
title : '无回执',
width : '200',
}, {
field : 'time',
title : '日期',
width : '200',
} ] ]
});
alert(SteptypeGrid);
});
${obj.unitList }
开始日期 结束日期
<div data-options="region:'center',fit:true,border:false">
<table id="SteptypeGrid"></table>
</div>
