account_stat.java

电信的网厅的整站代码

	 else{
		 if(xun.equals("")&&week.equals("")){month=" and to_char(a.CreateTime,'yyyymm')='"+year+month+"'";year="";}
		 }
	 
	 //按旬
	 if(xun.equals("")){xun="";}
	 else{ 
		   if(xun.equals("1")){
			   String s1="01";
			   String s2="10";
			   xun=" and to_char(a.CreateTime,'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.CreateTime,'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.CreateTime,'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.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.cityname, e.citycode,");
	 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 a.citycode, 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(" group by a.citycode ) a,");
	 sql.append("(select a.citycode, 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(" group by a.citycode ) f,");
	 sql.append(" (select a.citycode, 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("  group by a.citycode ) b");
	 sql.append(", td_city e ");
	 sql.append("  where a.citycode(+)=e.citycode and  b.citycode(+)=e.citycode and f.citycode(+)=e.citycode");
	
	 }
	 if(lx.equals("D")){
	 sql.append(" select a.citycode, 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(" group by a.citycode ) c,");
	 sql.append(" (select a.citycode, 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(" group by a.citycode ) g,");
	 sql.append("( select a.citycode, 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(" group by a.citycode ) d ");
	 sql.append(", td_city e ");
	 sql.append("  where  c.citycode(+)=e.citycode and d.citycode(+)=e.citycode and g.citycode(+)=e.citycode");
	 }
	 
	 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.citycode, 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(" group by a.citycode ) a,");
		 sql.append(" (select a.citycode, 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(" group by a.citycode ) b,");
		 sql.append(" (select a.citycode, 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(" group by a.citycode ) f,");
		 sql.append("(select a.citycode, 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(rq);
		 sql.append(year);
		 sql.append(month);
		 sql.append(quarter);
		 sql.append(xun);
		 sql.append(week);
		 sql.append(start);
		 sql.append(end);
		 sql.append(" group by a.citycode ) c,");
		 sql.append("(select a.citycode, 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(rq);
		 sql.append(year);
		 sql.append(month);
		 sql.append(quarter);
		 sql.append(xun);
		 sql.append(week);
		 sql.append(start);
		 sql.append(end);
		 sql.append(" group by a.citycode ) g,");
		 sql.append("( select a.citycode, 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("  group by a.citycode ) d, td_city e ");
		 sql.append("  where a.citycode(+)=e.citycode and b.citycode(+)=e.citycode  and c.citycode(+)=e.citycode  and d.citycode(+)=e.citycode");
		 sql.append("  and f.citycode(+)=e.citycode and g.citycode(+)=e.citycode");
		 sql.append(" ");
		 
}
	 
	 sql.append(" )where citycode<>'0590'");
	 sql.append(citycode);
 		 
	 
	 
     DataTable dt = null;
   // System.out.println("---------------------------------------------------------------------------") ;
   //  System.out.println("sql==>"+sql.toString());
     
     try {
    	dt = _dac.executeQuery(sql.toString());
     }
     catch (Exception ex) {
       FileLogger.getLogger().debug(ex);
       return null;
     }
     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");
  DataTable dt=null;
  try {
    dt=_dac.executeQuery(sql.toString());
  }
  catch (Exception ex) {
	  return null;
  }
   return dt;
 }
 public static void  main(String args[]){
	 //Account_Stat a=new Account_Stat();
	
 }
 }