📄 account_stat.java
字号:
year="";
month="";
}
}
// 按周
if(week==null||week.equals("")){week="";}
else{
if(week.equals("1")){
String s1="01";
String s2="07";
week=" and to_char(a.CreateTime,'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.CreateTime,'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.CreateTime,'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.CreateTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按日期
if(rq==null||rq.equals("")){rq="";}
else{rq=" and to_char(a.CreateTime,'yyyy-mm-dd')='"+rq+"'";}
//按时间段
if(start==null||start.equals("")){start="";}
else{start=" and to_char(a.CreateTime,'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 e.areaname, e.areacode,");
if(lx.equals("E")){
sql.append(" decode(a.cnt,null,0,a.cnt) ae_cnt, decode(b.cnt,null,0,b.cnt) ne_cnt ,decode(f.cnt, null, 0, f.cnt) fl_cnt");
sql.append(" from ( ");
}if(lx.equals("D")){
sql.append(" decode(c.cnt,null,0,c.cnt) ad_cnt, decode(d.cnt,null,0,d.cnt) nd_cnt ,decode(g.cnt, null, 0, g.cnt) gl_cnt");
sql.append(" from ( ");
}
if(lx.equals("E")){
sql.append("select areacode, count(distinct a.userid) cnt from tf_custinfoweb a ");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'E' and a.userstate = 'A'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) a,");
sql.append(" (select a.areacode, count( distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'E' and a.userstate = 'N'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) b,");
sql.append(" (select a.areacode, count( distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'E' and a.userstate = 'L'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) f");
sql.append(", td_area e ");
sql.append(" where a.areacode(+)=e.areacode and b.areacode(+) =e.areacode and f.areacode(+) =e.areacode ");
sql.append(" and e.STATE='E' and e.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(areacode);
}
if(lx.equals("D")){
sql.append(" select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'D' and a.userstate = 'A'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) c,");
sql.append("( select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'D' and a.userstate = 'N'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) d, ");
sql.append("( select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'D' and a.userstate = 'L'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) g");
sql.append(", td_area e ");
sql.append(" where c.areacode(+)=e.areacode and d.areacode(+)=e.areacode and g.areacode(+)=e.areacode");
sql.append(" and e.STATE='E' and e.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(areacode);
}
if(lx.equals("")){
sql.append(" decode(a.cnt,null,0,a.cnt) ae_cnt, decode(b.cnt,null,0,b.cnt) ne_cnt,decode(c.cnt,null,0,c.cnt) ad_cnt, decode(d.cnt,null,0,d.cnt) nd_cnt, ");
sql.append(" decode(f.cnt,null,0,f.cnt) fl_cnt,decode(g.cnt,null,0,g.cnt) gl_cnt");
sql.append(" from ( ");
sql.append("select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'E' and a.userstate = 'A' ");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) a,");
sql.append(" (select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'E' and a.userstate = 'N' ");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by areacode ) b,");
sql.append(" (select a.areacode, count( distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'E' and a.userstate = 'L'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) f,");
sql.append("(select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'D' and a.userstate = 'A'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by areacode ) c,");
sql.append("( select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'D' and a.userstate = 'L'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by a.areacode ) g,");
sql.append("( select a.areacode, count(distinct a.userid) cnt from tf_custinfoweb a");
if(realtion.equals("1")){
sql.append(" ,tf_andcustrela b");
}
sql.append(" where a.custtypeid = '2' and a.state = 'D' and a.userstate = 'N'");
if(realtion.equals("1")){
sql.append(" and a.userid=b.userid");
}
sql.append(year);
sql.append(month);
sql.append(quarter);
sql.append(xun);
sql.append(week);
sql.append(rq);
sql.append(start);
sql.append(end);
sql.append(" and a.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(" group by areacode ) d, td_area e ");
sql.append(" where a.areacode(+)=e.areacode and b.areacode(+)=e.areacode and c.areacode(+)=e.areacode and d.areacode(+)=e.areacode ");
sql.append(" and f.areacode(+)=e.areacode and g.areacode(+)=e.areacode ");
sql.append(" and e.STATE='E' and e.citycode='");
sql.append(citycode);
sql.append("'");
sql.append(areacode);
}
sql.append(" )");
DataTable dt = null;
//System.out.println("---------------------------------------------------------------------------") ;
//System.out.println("areasql==>"+sql.toString());
try {
dt = _dac.executeQuery(sql.toString());
}
catch (Exception ex) {
FileLogger.getLogger().debug(ex);
return null;
}
return dt;
}
public DataTable getCount(String year,String quarter,String month,String xun,String week,String rq,String start,String end,String lx,String citycode ,String realtion){
if(citycode.equals("")){citycode="";}
else{citycode=" and citycode='"+citycode+"'";}
//按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(a.CreateTime,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(a.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(a.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(a.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(a.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -