📄 funcount.java
字号:
package com.doone.fj1w.fjmgr.statana;
import com.doone.data.DacClient;
import com.doone.data.DataTable;
import com.doone.util.FileLogger;
/**
* <p>Title:程控受理量统计 </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2006-01-06</p>
* <p>Company:新东网 </p>
* @author 吴章银
*/
public class Funcount {
private DacClient _dac = null;
private DataTable dt = null;
int pagecount =15;//分页是每一页显示的记录个数
public Funcount() {
try {
_dac = new DacClient();
} catch (Exception ex) {
FileLogger.getLogger().warn("数据库连接出错！", ex);
}
}
public DataTable funcount(String citycode,String cplb,String ywlb,String funid,String transact,String year,String quarter,String month,String xun, String week,String rq,String start,String end,String pageNo,String flag){
if(citycode==null||citycode.equals("")){citycode="";}
else{citycode=" and a.citycode='"+citycode+"'";}
if(cplb==null||cplb.equals("")){cplb="";}
else{cplb=" and d.PRODUCTID='"+cplb+"'";}
if(ywlb==null||ywlb.equals("")){ywlb="";}
else{ywlb=" and b.FUNTYPE='"+ywlb+"'";}
if(funid==null||funid.equals("")){funid="";}
else{funid=" and a.PRODFUNID='"+funid+"'";}
if(transact==null||transact.equals("")){transact="";}
else{transact=" and a.PROGRESSSTATE='"+transact+"'";}
//按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(a.ACCEPTTIME,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
else{
if(xun.equals("")&&week.equals("")){month=" and to_char(a.AcceptTime,'yyyymm')='"+year+month+"'";year="";}
}
//按旬
if(xun.equals("")){xun="";}
else{
if(xun.equals("1")){
String s1="01";
String s2="10";
xun=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(xun.equals("2")){
String s1="11";
String s2="20";
xun=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(xun.equals("3")){
String s1="21";
String s2="31";
xun=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按周
if(week==null||week.equals("")){week="";}
else{
if(week.equals("1")){
String s1="01";
String s2="07";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(week.equals("2")){
String s1="08";
String s2="15";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(week.equals("3")){
String s1="16";
String s2="23";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(week.equals("4")){
String s1="24";
String s2="31";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按日期
if(rq==null||rq.equals("")){rq="";}
else{rq=" and to_char(a.AcceptTime,'yyyy-mm-dd')='"+rq+"'";}
//按时间段
if(start==null||start.equals("")){start="";}
else{start=" and to_char(a.AcceptTime,'yyyy-mm-dd HH24:mi') between '"+start+"'";year="";}
if(end==null||end.equals("")){end="";}
else{end=" and '"+end+"'";year="";}
StringBuffer sql=new StringBuffer();
sql.append("select * from ");
sql.append("(select t.*, rownum num from");
sql.append("(select d.productname,b.funname,a.citycode,count(*) cnt, to_char(a.ACCEPTTIME,'yyyy-mm-dd') ACCEPTTIME,decode(a.PROGRESSSTATE,'1','等待处理','2','处理中','3','处理结束','处理失败') PROGRESSSTATE ,c.cityname,d.productid,b.prodfunid from tf_funorder a, TD_PRODFUN b,td_product d,td_city c");
sql.append(" where a.PRODFUNID = b.prodfunid");
sql.append(" and a.citycode=c.citycode and b.OUTPUT = '1' AND b.FUNSTATE = 'E' and b.productid = d.productid");
sql.append(citycode);
sql.append(cplb);
sql.append(ywlb);
sql.append(funid);
sql.append(transact);
sql.append(year);
sql.append(quarter);
sql.append(month);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" group by b.funname,a.citycode,to_char(a.ACCEPTTIME,'yyyy-mm-dd'),a.PROGRESSSTATE,c.cityname, d.productname,d.productid,b.prodfunid ");
sql.append(" order by d.productid,b.prodfunid,to_char(a.ACCEPTTIME,'yyyy-mm-dd'))t)r");
if(flag.equals("0")){
sql.append(" where r.num>");
sql.append(pagecount * (Integer.parseInt(pageNo) - 1));
sql.append(" and r.num<=");
sql.append(pagecount * Integer.parseInt(pageNo));
}
//System.out.println("sql==========>"+sql.toString());
try {
dt = _dac.executeQuery(sql.toString());
} catch (Exception ex) {
FileLogger.getLogger().warn("数据库查询出错！", ex);
}
return dt;
}
// 得到城市列表
public DataTable getCity()
{
String sql="select cityname,citycode from td_city where citycode<>'0590' and STATE='E' order by citycode";
DataTable dt=null;
try {
dt=_dac.executeQuery(sql);
}
catch (Exception ex) {
return null;
}
return dt;
}
// 得到区域列表
public DataTable getarea(String citycode)
{
StringBuffer sql=new StringBuffer();
sql.append("select t.AREACODE,t.AREANAME from td_area t ");
sql.append(" where t.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" and t.STATE='E'");
sql.append(" order by t.areacode");
//System.out.println(sql.toString());
DataTable dt=null;
try {
dt=_dac.executeQuery(sql.toString());
}
catch (Exception ex) {
return null;
}
return dt;
}
public String getPageNo(String citycode,String cplb,String ywlb,String funid,String transact,String year,String quarter,String month,String xun, String week,String rq,String start,String end){
if(citycode==null||citycode.equals("")){citycode="";}
else{citycode=" and a.citycode='"+citycode+"'";}
if(cplb==null||cplb.equals("")){cplb="";}
else{cplb=" and d.PRODUCTID='"+cplb+"'";}
if(ywlb==null||ywlb.equals("")){ywlb="";}
else{ywlb=" and b.FUNTYPE='"+ywlb+"'";}
if(funid==null||funid.equals("")){funid="";}
else{funid=" and a.PRODFUNID='"+funid+"'";}
if(transact==null||transact.equals("")){transact="";}
else{transact=" and a.PROGRESSSTATE='"+transact+"'";}
// 按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(a.ACCEPTTIME,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(a.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
else{
if(xun.equals("")&&week.equals("")){month=" and to_char(a.AcceptTime,'yyyymm')='"+year+month+"'";year="";}
}
//按旬
if(xun.equals("")){xun="";}
else{
if(xun.equals("1")){
String s1="01";
String s2="10";
xun=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(xun.equals("2")){
String s1="11";
String s2="20";
xun=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(xun.equals("3")){
String s1="21";
String s2="31";
xun=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按周
if(week==null||week.equals("")){week="";}
else{
if(week.equals("1")){
String s1="01";
String s2="07";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(week.equals("2")){
String s1="08";
String s2="15";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(week.equals("3")){
String s1="16";
String s2="23";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
if(week.equals("4")){
String s1="24";
String s2="31";
week=" and to_char(a.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按日期
if(rq==null||rq.equals("")){rq="";}
else{rq=" and to_char(a.AcceptTime,'yyyy-mm-dd')='"+rq+"'";}
//按时间段
if(start==null||start.equals("")){start="";}
else{start=" and to_char(a.AcceptTime,'yyyy-mm-dd HH24:mi') between '"+start+"'";year="";}
if(end==null||end.equals("")){end="";}
else{end=" and '"+end+"'";year="";}
StringBuffer sql=new StringBuffer();
sql.append("select count(*) count from ");
sql.append("(select t.*, rownum num from");
sql.append("(select b.funname,a.citycode,count(*) cnt,to_char(a.ACCEPTTIME,'yyyy-mm-dd') ACCEPTTIME,a.PROGRESSSTATE,c.cityname from tf_funorder a, TD_PRODFUN b,td_product d,td_city c");
sql.append(" where a.PRODFUNID = b.prodfunid and b.productid = d.productid");
sql.append(" and a.citycode=c.citycode and b.OUTPUT = '1' AND b.FUNSTATE = 'E'");
sql.append(citycode);
sql.append(cplb);
sql.append(ywlb);
sql.append(funid);
sql.append(transact);
sql.append(year);
sql.append(quarter);
sql.append(month);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" group by b.funname,a.citycode,to_char(a.ACCEPTTIME,'yyyy-mm-dd'),a.PROGRESSSTATE,c.cityname)t)r");
int count = 0;
System.out.println("得到总页数"+sql.toString());
try {
DataTable dt = _dac.executeQuery(sql.toString());
count = Integer.parseInt(dt.getRow(0).getString(0));
if (count % pagecount == 0)
count = count / pagecount;
else
count = count / pagecount + 1;
} catch (Exception ex) {
count=0;
}
return String.valueOf(count);
}
//得到程控名字
public DataTable GetFunname(String citycode,String cplb,String ywlb){
if(citycode==null||citycode.equals("")){citycode="";}
else{citycode=" and a.citycode='"+citycode+"'";}
if(cplb==null||cplb.equals("")){cplb="";}
else{cplb=" and a.PRODUCTID='"+cplb+"'";}
if(ywlb==null||ywlb.equals("")){ywlb="";}
else{ywlb=" and a.FUNTYPE='"+ywlb+"'";}
StringBuffer sql=new StringBuffer();
sql.append("select a.PRODFUNID,a.FUNNAME,b.productname from TD_PRODFUN a,td_product b");
sql.append(" where a.OUTPUT = '1' AND a.FUNSTATE = 'E' " );
sql.append(" and a.productid = b.productid");
sql.append(citycode);
sql.append(cplb);
sql.append(ywlb);
sql.append(" order by b.productid,a.PRODFUNID");
//System.out.println("程控===》"+sql.toString());
DataTable dt=null;
try {
dt=_dac.executeQuery(sql.toString());
}
catch (Exception ex) {
return null;
}
return dt;
}
//得到受理状态
public DataTable GetProcestate(){
StringBuffer sql=new StringBuffer();
sql.append("select PROGRESSSTATE,decode(PROGRESSSTATE,'1','等待处理','2','处理中','3','处理结束','处理失败')PROGRESSSTATENAME from tf_funorder ");
DataTable dt=null;
try {
dt=_dac.executeQuery(sql.toString());
}
catch (Exception ex) {
return null;
}
return dt;
}
public static void main(String[] args) {
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -