|
版本: |
BeX5V3.1 |
小版本号: |
|
|
|
数据库: |
Oracle |
服务器操作系统: |
Windows |
应用服务器: |
Tomcat |
客户端操作系统: |
Windows 7 |
浏览器: |
Firefox |
|
|
public static Table monthReprot(String beginDate,String endDate){
*
*/
//本月受理
//String SQL1 = "select count(M.TRANS_FLAG) from MA_HOT_LINE M where M.TRANS_FLAG>=1 and HOT_LINE_TIME between to_date('2015-03-01','yyyy-mm-dd') and to_date('2015-03-31','yyyy-mm-dd')";
String SQL1 = "select TRANS_FLAG from MA_HOT_LINE where TRANS_FLAG>=1 and HOT_LINE_TIME between to_date('2015-03-01','yyyy-mm-dd') and to_date('2015-03-31','yyyy-mm-dd')";
int monthTotalCount = HotLineReport.monthTotal(SQL1).getColumnCount();
//已办件
//String SQL2 = "select count(M.TRANS_FLAG) from MA_HOT_LINE M where M.TRANS_FLAG>=11 and HOT_LINE_TIME between to_date('2015-03-01','yyyy-mm-dd') and to_date('2015-03-31','yyyy-mm-dd')";
String SQL2 = "select TRANS_FLAG from MA_HOT_LINE where TRANS_FLAG>=11 and HOT_LINE_TIME between to_date('2015-03-01','yyyy-mm-dd') and to_date('2015-03-31','yyyy-mm-dd')";
int monthOverTotalCount = HotLineReport.monthOverTotal(SQL2).getColumnCount();
int HOT_LINE_NUMBER = monthTotalCount;
int HOT_LINE_TAKEN = monthOverTotalCount;
//HOT_LINE_SCALE 已办占比 =已办件/本月受理
int HOT_LINE_SCALE = monthOverTotalCount/monthTotalCount;
//HOT_LINE_DISPOSE 正在办理=本月受理-已办件
int HOT_LINE_DISPOSE = monthTotalCount - monthOverTotalCount;
//HOT_LINE_ZHENGBAN 正办占比=正在办理/本月受理
int HOT_LINE_ZHENGBAN = (monthTotalCount - monthOverTotalCount)/monthTotalCount;
//HOT_LINE_ALL 总累计数=本月受理总数
int HOT_LINE_ALL = monthTotalCount;
//HOT_LINE_YIBAN已办累计数
int HOT_LINE_YIBAN =monthOverTotalCount;
//HOT_LINE_YIBANZHANBI已办占比=已办累计数/总累计数
int HOT_LINE_YIBANZHANBI = HOT_LINE_YIBAN/HOT_LINE_ALL;
//HOT_LLINE_ZHENGBANLEIJISHU正在办理=总累计数-已办累计数
int HOT_LLINE_ZHENGBANLEIJISHU =HOT_LINE_ALL - HOT_LINE_YIBAN;
//HOT_LINE_ZHENGBANZHANBI正办占比=(总累计数-已办累计数)/总累计数
int HOT_LINE_ZHENGBANZHANBI = (HOT_LINE_ALL - HOT_LINE_YIBAN)/HOT_LINE_ALL;
//数据插入
String sql="insert into MA_HOT_MONTH(FID,YER,MONTH_S,HOT_LINE_NUMBER,HOT_LINE_TAKEN,HOT_LINE_SCALE," +
"HOT_LINE_DISPOSE,HOT_LINE_ZHENGBAN,HOT_LINE_ALL,HOT_LINE_YIBAN,HOT_LINE_YIBANZHANBI," +
"HOT_LLINE_ZHENGBANLEIJISHU,HOT_LINE_ZHENGBANZHANBI) " +
"values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
try{
Connection conn = ModelUtils.getConnectionInTransaction("/szrx/hotLineReport/data");
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,UUID.randomUUID().toString());
pstmt.setString(2, "YER");
pstmt.setString(3, "MONTH_S");
pstmt.setInt(4, HOT_LINE_NUMBER);
pstmt.setInt(5, HOT_LINE_TAKEN);
pstmt.setLong(6, HOT_LINE_SCALE);
pstmt.setInt(7, HOT_LINE_DISPOSE);
pstmt.setLong(8, HOT_LINE_ZHENGBAN);
pstmt.setInt(9, HOT_LINE_ALL);
pstmt.setInt(10, HOT_LINE_YIBAN);
pstmt.setLong(11, HOT_LINE_YIBANZHANBI);
pstmt.setInt(12, HOT_LLINE_ZHENGBANLEIJISHU);
pstmt.setLong(13, HOT_LINE_ZHENGBANZHANBI);
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}
//查询出逐月统计表的数据
String querysql = "select MA_HOT_MONTH.MONTH_S as MONTH_S," +
"MA_HOT_MONTH.HOT_LINE_NUMBER as NUMBER_S," +
"MA_HOT_MONTH.HOT_LINE_TAKEN as TAKEN_S," +
"MA_HOT_MONTH.HOT_LINE_SCALE as SCALE_S," +
"MA_HOT_MONTH.HOT_LINE_DISPOSE as DISPOSE_S," +
"MA_HOT_MONTH.HOT_LINE_ZHENGBAN as ZHENGBAN_S," +
"MA_HOT_MONTH.HOT_LINE_ALL as ALL_S," +
"MA_HOT_MONTH.HOT_LINE_YIBAN as YIBAN_S," +
"MA_HOT_MONTH.HOT_LINE_YIBANZHANBI as NUMBER_SS," +
"MA_HOT_MONTH.HOT_LLINE_ZHENGBANLEIJISHU as NUMBER_SSS," +
"MA_HOT_MONTH.HOT_LINE_ZHENGBANZHANBI as CODE_S from MA_HOT_MONTH ";
System.out.println("@@@@@@@@@@@@@@@@@@@");
HashMap<String,String> sqlMap = new HashMap<String,String>();
sqlMap.put(DatabaseProduct.ORACLE.name(),querysql);
return SQL.select(sqlMap, null, "/szrx/hotLineReport/data");
}
/**
* 统计本月受理总数
* @param SQL1
* @return
*/
private static Table monthTotal(String SQL1) {
HashMap<String,String> sqlMap = new HashMap<String,String>();
sqlMap.put(DatabaseProduct.ORACLE.name(),SQL1);
return SQL.select(sqlMap, null, "/szrx/hotLineReport/data");
}
/**
* 统计已办件数
* @param SQL2
* @return
*/
private static Table monthOverTotal(String SQL2){
HashMap<String,String> sqlMap = new HashMap<String,String>();
sqlMap.put(DatabaseProduct.ORACLE.name(),SQL2);
return SQL.select(sqlMap, null, "/szrx/hotLineReport/data");
}
|
|