📄 complaintreport.java
字号:
/**
* 投诉报表
*/
package com.NCL.excel;
import java.util.ArrayList;
import java.util.List;
import com.sinosoft.common.*;
public class ComplaintReport {
public List strList = new ArrayList();
/**
* 投诉报表-年报
* @param year
* @return
*/
public List yearReportDo(String year){
int yearAddOne = Integer.parseInt(year)+1;
String SQL="";
DBAccess d = new DBAccess();
SQL = "Select nvl("
+" (select Sum(to_date(to_char(dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD'))"
+" from COMPLAINTMESSAGE where dealflag = 1"
+" and dealtime >= to_date('"+year+"-01', 'yyyy-MM')"
+" and dealtime < to_date('"+yearAddOne+"-01', 'yyyy-MM')"
+" )"
+" /(select count(dealflag) from COMPLAINTMESSAGE where dealflag = 1"
+" and dealtime >= to_date('"+year+"-01', 'yyyy-MM')"
+" and dealtime < to_date('"+yearAddOne+"-01', 'yyyy-MM')"
+" ),0)"
+" as Average from months where nu=1";
strList = d.parseSQL(SQL, new ArrayList());
if(Data.hasValue(year))
SQL="select"
//--已受理
+" m.nu,"
+" nvl((select Count(t1.ACCEPTFLAG) "
+" from COMPLAINTMESSAGE t1 "
+" where t1.ACCEPTFLAG = 1 "
+" and t1.ACCEPTTIME >= "
+" to_date('"+year+"' || m.nu, 'yyyy-MM') "
+" and t1.ACCEPTTIME < "
+" add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)),0) as ACCEPT,"
// --已结案
+" nvl((select Count(t2.DEALFLAG) "
+" from COMPLAINTMESSAGE t2 "
+" where t2.DEALFLAG = 1 "
+" and t2.DEALTIME >= "
+" to_date('"+year+"' || m.nu, 'yyyy-MM') "
+" and t2.DEALTIME < "
+" add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)),0) as DEAL,"
//--1天内处理
+" nvl((select Count(t3.DEALFLAG) "
+" from COMPLAINTMESSAGE t3 "
+" where t3.DEALFLAG = 1 "
+" and t3.DEALTIME >= "
+" to_date('"+year+"' || m.nu, 'yyyy-MM') "
+" and t3.DEALTIME < "
+" add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1) "
+" and (to_date(to_char(t3.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t3.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) = 0),0)as OneCount,"
//--1－2日处理
+"nvl((select Count(t4.DEALFLAG) "
+" from COMPLAINTMESSAGE t4 "
+" where t4.DEALFLAG = 1 "
+" and t4.DEALTIME >= "
+" to_date('"+year+"' || m.nu, 'yyyy-MM') "
+" and t4.DEALTIME < "
+" add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1) "
+" and (to_date(to_char(t4.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t4.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) in (1,2)),0)as OneAndTwoCount,"
//--2日处理
+"nvl((select Count(t5.DEALFLAG) "
+" from COMPLAINTMESSAGE t5 "
+" where t5.DEALFLAG = 1 "
+" and t5.DEALTIME > "
+" to_date('"+year+"' || m.nu, 'yyyy-MM') "
+" and t5.DEALTIME < "
+" add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1) "
+" and (to_date(to_char(t5.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t5.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) in (0,1,2)),0)as TwoCount,"
// --2日以上处理
+" nvl((select Count(t6.DEALFLAG) "
+" from COMPLAINTMESSAGE t6 "
+" where t6.DEALFLAG = 1 "
+" and t6.DEALTIME >= "
+" to_date('"+year+"' || m.nu, 'yyyy-MM') "
+" and t6.DEALTIME < "
+" add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1) "
+" and (to_date(to_char(t6.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t6.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) >2),0)as OverTwoCount,"
//平均时效
+" nvl("
+" (select Sum(to_date(to_char(dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD'))"
+" from COMPLAINTMESSAGE where dealflag = 1"
+" and dealtime >= to_date('"+year+"'||m.nu, 'yyyy-MM')"
+" and dealtime < add_months(to_date('"+year+"'||m.nu, 'yyyy-MM'), 1)"
+" )"
+" /(select count(dealflag) from COMPLAINTMESSAGE where dealflag = 1"
+" and dealtime >= to_date('"+year+"'||m.nu, 'yyyy-MM')"
+" and dealtime < add_months(to_date('"+year+"'||m.nu, 'yyyy-MM'), 1)"
+" ),0)"
+" as Average from months m where m.nu>=1 and m.nu<=12 order by m.nu";
return d.mulparseSQL(SQL, new ArrayList());
}
/**
* 投诉报表-机构
* @param startDate
* @param endDate
* @return
*/
public List comReportDo(String startDate,String endDate){
String SQL="";
DBAccess d = new DBAccess();
SQL = "Select nvl("
+" (select Sum(to_date(to_char(DEALTIME,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD'))"
+" from COMPLAINTMESSAGE where dealflag = 1"
+" and DEALTIME >= to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and DEALTIME < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
+" )"
+" /(select count(dealflag) from COMPLAINTMESSAGE where dealflag = 1"
+" and DEALTIME >= to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and DEALTIME < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
+" ),0)"
+" as Average from months where nu=1";
strList = d.parseSQL(SQL, new ArrayList());
//按投诉机构
//取年份 startdate:"+year+"08-01 enddate:"+year+"09-30
if(Data.hasValue(startDate)&&Data.hasValue(endDate))
SQL="select"
+" l.name,"
//--已受理
+" nvl((select Count(t1.ACCEPTFLAG)"
+" from COMPLAINTMESSAGE t1"
+" where t1.ACCEPTFLAG = 1"
+" and t1.ACCEPTTIME >="
+" to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and t1.ACCEPTTIME <"
+" (to_date('"+endDate+"', 'YYYY-MM-DD')+1)"
+" and t1.COMPLAINTORG = l.comcode),0) as ACCEPT,"
//--已结案
+"nvl((select Count(t2.DEALFLAG)"
+" from COMPLAINTMESSAGE t2"
+" where t2.DEALFLAG = 1"
+" and t2.DEALTIME >="
+" to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and t2.DEALTIME <"
+" (to_date('"+endDate+"', 'YYYY-MM-DD')+1)"
+" and t2.COMPLAINTORG = l.comcode),0) as DEAL,"
//--1天内处理(当天)"
+"nvl((select Count(t3.DEALFLAG)"
+" from COMPLAINTMESSAGE t3"
+" where t3.DEALFLAG = 1"
+" and t3.DEALTIME >="
+" to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and t3.DEALTIME <"
+" (to_date('"+endDate+"', 'YYYY-MM-DD')+1)"
+" and t3.COMPLAINTORG = l.comcode"
+" and (to_date(to_char(t3.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t3.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) = 0),0)as OneCount,"
//--1－2日处理(第二天)
+"nvl((select Count(t4.DEALFLAG)"
+" from COMPLAINTMESSAGE t4"
+" where t4.DEALFLAG = 1"
+" and t4.DEALTIME >="
+" to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and t4.DEALTIME <"
+" (to_date('"+endDate+"', 'YYYY-MM-DD')+1)"
+" and t4.COMPLAINTORG = l.comcode"
+" and (to_date(to_char(t4.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t4.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) in (1,2)),0)as OneAndTwoCount,"
//--2日处理(当天或者第二天)
+"nvl((select Count(t5.DEALFLAG)"
+" from COMPLAINTMESSAGE t5"
+" where t5.DEALFLAG = 1"
+" and t5.DEALTIME >="
+" to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and t5.DEALTIME <"
+" (to_date('"+endDate+"', 'YYYY-MM-DD')+1)"
+" and t5.COMPLAINTORG = l.comcode"
+" and (to_date(to_char(t5.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t5.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) in (0,1,2)),0)as TwoCount,"
//--2日以上处理(第三天或者以上)
+"nvl((select Count(t6.DEALFLAG)"
+" from COMPLAINTMESSAGE t6"
+" where t6.DEALFLAG = 1"
+" and t6.DEALTIME >="
+" to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and t6.DEALTIME <"
+" (to_date('"+endDate+"', 'YYYY-MM-DD')+1)"
+" and t6.COMPLAINTORG = l.comcode"
+" and (to_date(to_char(t6.dealtime,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t6.MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD')) >2),0)as OverTwoCount,"
//--平均处理时效 average
+" nvl("
+" (select Sum(to_date(to_char(DEALTIME,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(MESSAGETIME,'YYYY-MM-DD'),'YYYY-MM-DD'))"
+" from COMPLAINTMESSAGE where dealflag = 1"
+" and COMPLAINTORG = l.comcode"
+" and DEALTIME >= to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and DEALTIME < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
+" )"
+" /(select count(dealflag) from COMPLAINTMESSAGE where dealflag = 1"
+" and COMPLAINTORG = l.comcode"
+" and DEALTIME >= to_date('"+startDate+"', 'YYYY-MM-DD')"
+" and DEALTIME < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
+" ),0)"
+" as Average from LDCOM l where comgrade='1' or comgrade='2' order by comgrade";
return d.mulparseSQL(SQL, new ArrayList());
}
public static void main(String args[]){
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -