------------后台
@At("/dateGroupMonth")
@Ok("jsp:/NewFile.jsp")
public Object dateGroupMonth() {
// 创建客户对象集合
List listCustomerManage = new ArrayList();
System.out.println("111111111111");
List list = null;
Client client = Client.getInstance();
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' ) ) 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' ) AS time " + "FROM " + databaseName
+ " .SMS_SENT WHERE DATE_FORMAT( NOW( ), '%Y-%m' ) > DATE_FORMAT( SENTTIME, '%Y-%m' ) "
+ "GROUP BY DATE_FORMAT( SENTTIME, '%Y-%m' ) ORDER BY "
+ " DATE_FORMAT( SENTTIME, '%Y-%m' ) ";
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<List> statistical = new ArrayList<>();
while (rs.next()) {
List tempstatistical = new ArrayList<>();
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.add(count);
tempstatistical.add(succeed);
tempstatistical.add(Failure);
tempstatistical.add(wzh);
tempstatistical.add(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;
}
}
----------------前台
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
Insert title here
Insert Body content here!
