📄 ywsl_count.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) 2005</p>
* <p>Company:新东网 </p>
* @author 吴章银
*/
public class ywsl_count {
private DacClient _dac = null;
private DataTable dt = null;
int pagecount =15;//分页是每一页显示的记录个数
public ywsl_count() {
try {
_dac = new DacClient();
} catch (Exception ex) {
FileLogger.getLogger().warn("数据库连接出错！", ex);
}
}
//按地区类型统计
public DataTable getareaCount(String citycode,String areacode,String ywlx,String sllx,String slzt,String year,String quarter,String month,String xun,String week,String rq,String start,String end,String pageNo,String falg){
if(citycode.equals("")||citycode==null){citycode="";}
else{citycode=" and c.citycode='"+citycode+"'";}
if(areacode.equals("")||areacode==null){areacode="";}
else{areacode=" and c.areacode='"+areacode+"'";}
if(ywlx.equals("")||ywlx==null){ywlx="";}
else{ywlx=" and a.PRODUCTID='"+ywlx+"'";}
if(sllx.equals("")||sllx==null){sllx="";}
else{sllx=" and b.OPERATIONTYPE='"+sllx+"'";}
if(slzt.equals("")||slzt==null){slzt="";}
else{slzt=" and b.BOSOMPROGRESSSTATE='"+slzt+"'";}
//按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(b.AcceptTime,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
else{
if(xun.equals("")&&week.equals("")){month=" and to_char(b.AcceptTime,'yyyymm')='"+year+month+"'";year="";}
}
//按旬
if(xun.equals("")){xun="";}
else{
if(xun.equals("1")){
String s1="01";
String s2="10";
xun=" and to_char(b.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(b.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(b.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(b.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(b.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(b.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(b.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按日期
if(rq==null||rq.equals("")){rq="";}
else{rq=" and to_char(b.AcceptTime,'yyyy-mm-dd')='"+rq+"'";}
//按时间段
if(start==null||start.equals("")){start="";}
else{start=" and to_char(b.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 count(*) cnt,a.Productname esp,a.PRODUCTID productid,b.OPERATIONTYPE type,");
sql.append(" b.BOSOMPROGRESSSTATE pro,decode(b.BOSOMPROGRESSSTATE,'1','待处理', '2','转派','3','挂起','8','作废','9','完成','无') state,");
sql.append(" c.citycode,c.areacode,d.cityname, f.areaname");
sql.append(" from Tf_Prodorder b, Td_Product a, tf_address c,td_city d,");
sql.append(" (select e.areacode, e.areaname from td_area e where e.state = 'E') f");
sql.append(" where b.Productid = a.Productid and b.prodorderid = c.prodorderid and c.citycode = d.citycode");
sql.append(" and c.areacode = f.areacode(+)");
sql.append(citycode);
sql.append(areacode);
sql.append(ywlx);
sql.append(sllx);
sql.append(slzt);
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 a.Productname,a.PRODUCTID,b.OPERATIONTYPE,b.BOSOMPROGRESSSTATE,c.citycode,c.areacode, d.cityname,f.areaname");
sql.append(" order by c.areacode) t )r");
if(falg.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("ywslcount==>"+sql.toString()) ;
try {
dt = _dac.executeQuery(sql.toString());
} catch (Exception ex) {
FileLogger.getLogger().warn("数据库查询出错！", ex);
}
return dt;
}
public DataTable getarealist(String citycode,String areacode,String ywlx,String sllx,String slzt,String year,String quarter,String month,String xun, String week,String rq,String start,String end,String pageNo,String falg){
if(citycode.equals("")||citycode==null){citycode="";}
else{citycode=" and c.citycode='"+citycode+"'";}
if(areacode.equals("")||areacode==null){areacode=" and c.areacode is null";}
else{areacode=" and c.areacode='"+areacode+"'";}
if(ywlx.equals("")||ywlx==null){ywlx="";}
else{ywlx=" and a.PRODUCTID='"+ywlx+"'";}
if(sllx.equals("")||sllx==null){sllx="";}
else{sllx=" and b.OPERATIONTYPE='"+sllx+"'";}
if(slzt.equals("")||slzt==null){slzt="";}
else{slzt=" and b.BOSOMPROGRESSSTATE='"+slzt+"'";}
//按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(b.AcceptTime,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
else{
if(xun.equals("")&&week.equals("")){month=" and to_char(b.AcceptTime,'yyyymm')='"+year+month+"'";year="";}
}
//按旬
if(xun.equals("")){xun="";}
else{
if(xun.equals("1")){
String s1="01";
String s2="10";
xun=" and to_char(b.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(b.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(b.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(b.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(b.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(b.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(b.AcceptTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按日期
if(rq==null||rq.equals("")){rq="";}
else{rq=" and to_char(b.AcceptTime,'yyyy-mm-dd')='"+rq+"'";}
//按时间段
if(start==null||start.equals("")){start="";}
else{start=" and to_char(b.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 b.USERNAME,b.ABOUTTEL,b.POSTALCODE,b.LINKMAN,b.EMAIL,b.RELATIONTEL,b.ACCEPTTIME,b.HOPEUSETIME");
sql.append(" from Tf_Prodorder b, Td_Product a,tf_address c,td_city d,");
sql.append(" (select e.areacode, e.areaname from td_area e where e.state = 'E') f");
sql.append(" where b.Productid = a.Productid and b.prodorderid = c.prodorderid");
sql.append(" and c.citycode = d.citycode and c.areacode = f.areacode(+)");
sql.append(citycode);
sql.append(areacode);
sql.append(ywlx);
sql.append(sllx);
sql.append(slzt);
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(") t )r");
if(falg.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("111"+sql.toString()) ;
try {
dt = _dac.executeQuery(sql.toString());
} catch (Exception ex) {
FileLogger.getLogger().warn("数据库查询出错！", ex);
}
return dt;
}
public String getarealistPageNo(String citycode,String areacode,String ywlx,String sllx,String slzt,String year,String quarter,String month,String xun, String week,String rq,String start,String end){
if(citycode.equals("")||citycode==null){citycode="";}
else{citycode=" and c.citycode='"+citycode+"'";}
if(areacode.equals("")||areacode==null){areacode=" and c.areacode is null";}
else{areacode=" and c.areacode='"+areacode+"'";}
if(ywlx.equals("")||ywlx==null){ywlx="";}
else{ywlx=" and a.PRODUCTID='"+ywlx+"'";}
if(sllx.equals("")||sllx==null){sllx="";}
else{sllx=" and b.OPERATIONTYPE='"+sllx+"'";}
if(slzt.equals("")||slzt==null){slzt="";}
else{slzt=" and b.BOSOMPROGRESSSTATE='"+slzt+"'";}
//按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(b.AcceptTime,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(b.AcceptTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
else{
if(xun.equals("")&&week.equals("")){month=" and to_char(b.AcceptTime,'yyyymm')='"+year+month+"'";year="";}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -