📄 account_stat.java
字号:
package com.doone.fj1w.fjmgr.monitor;
import com.doone.data.*;
import com.doone.util.*;
/**
* <p>Title:注册帐号统计 </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2005</p>
* <p>Company:新东网 </p>
* @author 吴章银
*/
public class Account_Stat {
DacClient _dac=new DacClient();
int pagecount =15;//分页是每一页显示的记录个数
public Account_Stat() {
}
//地市详细列表
public DataTable getlist(String state,String _state,String citycode,String pageNo,String year,String quarter,String month,String xun, String week,String rq,String start,String end,String flag,String relationproduct)
{
if(state.equals("")){state="";}
else{state=" and Tf_CustInfoWeb.STATE='"+state+"'";}
if(_state.equals("")){_state="";}
else{_state=" and Tf_CustInfoWeb.USERSTATE='"+_state+"'";}
if(citycode.equals("")){citycode="";}
else{citycode=" and Tf_CustInfoWeb.Citycode='"+citycode+"'";}
//按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(tf_custinfoweb.CreateTime,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
else{
if(xun.equals("")&&week.equals("")){month=" and to_char(tf_custinfoweb.CreateTime,'yyyymm')='"+year+month+"'";year="";}
}
//按旬
if(xun.equals("")){xun="";}
else{
if(xun.equals("1")){
String s1="01";
String s2="10";
xun=" and to_char(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.CreateTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按日期
if(rq==null||rq.equals("")){rq="";}
else{rq=" and to_char(tf_custinfoweb.CreateTime,'yyyy-mm-dd')='"+rq+"'";}
//按时间段
if(start==null||start.equals("")){start="";}
else{start=" and to_char(tf_custinfoweb.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 (select r.*,rownum num from(select distinct t.* from (");
sql.append("select td_city.cityname,Tf_CustInfoWeb.ACCOUNTID, Tf_CustInfoWeb.CREATETIME,Tf_CustInfoWeb.STATE,Tf_CustInfoWeb.NAME,Tf_CustInfoWeb.EMAIL,Tf_CustInfoWeb.PWDQUESTION,Tf_CustInfoWeb.PWDANSWER from Tf_CustInfoWeb,");
if(relationproduct.equals("1")){
sql.append("tf_andcustrela ,");
}
sql.append("td_city");
sql.append(" where Tf_CustInfoWeb.CUSTTYPEID='2'");
if(relationproduct.equals("1")){
sql.append(" and tf_custinfoweb.userid=tf_andcustrela.userid");
}
sql.append(state);
sql.append(_state);
sql.append(citycode);
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 Tf_CustInfoWeb.Citycode=td_city.citycode");
sql.append(" group by Tf_CustInfoWeb.CityCode,td_city.cityname,Tf_CustInfoWeb.ACCOUNTID,Tf_CustInfoWeb.CREATETIME,Tf_CustInfoWeb.STATE,Tf_CustInfoWeb.NAME,Tf_CustInfoWeb.EMAIL,Tf_CustInfoWeb.PWDQUESTION,Tf_CustInfoWeb.PWDANSWER,rownum");
sql.append(" order by Tf_CustInfoWeb.CREATETIME desc)t)r)f");
if(flag.equals("0")){
sql.append(" where f.num>");
sql.append(pagecount * (Integer.parseInt(pageNo) - 1));
sql.append(" and f.num<=");
sql.append(pagecount * Integer.parseInt(pageNo));
}
DataTable dt = null;
// System.out.println("--------------------------------------------------------------------------------");
try {
dt = _dac.executeQuery(sql.toString());
}
catch (Exception ex) {
FileLogger.getLogger().debug(ex);
return null;
}
return dt;
}
//地区详细列表
public DataTable getarealist(String state,String _state,String areacode,String pageNo,String year,String quarter,String month,String xun,String week, String rq,String start,String end,String flag,String relationproduct)
{
if(state.equals("")){state="";}
else{state=" and Tf_CustInfoWeb.STATE='"+state+"'";}
if(_state.equals("")){_state="";}
else{_state=" and Tf_CustInfoWeb.USERSTATE='"+_state+"'";}
if(areacode.equals("")){areacode="";}
else{areacode=" and Tf_CustInfoWeb.areacode='"+areacode+"'";}
//按年
if(year.equals("")){year=" ";}
else{
if(quarter.equals("")&&month.equals("")&&xun.equals("")&&start.equals("")&&end.equals("")){year=" and to_char(tf_custinfoweb.CreateTime,'yyyy')='"+year+"'";}
}
//按季度
if(quarter.equals("")){quarter=" ";}
else{
if(quarter.equals("1")){
String s1=year+"01";
String s2=year+"03";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("2")){
String s1=year+"04";
String s2=year+"06";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("3")){
String s1=year+"07";
String s2=year+"09";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
if(quarter.equals("4")){
String s1=year+"10";
String s2=year+"12";
quarter=" and to_char(tf_custinfoweb.CreateTime,'yyyymm') between '" +s1+"' and '"+s2+"'";
year="";}
}
//按月份
if(month.equals("")){month="";}
else{
if(xun.equals("")&&week.equals("")){month=" and to_char(tf_custinfoweb.CreateTime,'yyyymm')='"+year+month+"'";year="";}
}
//按旬
if(xun.equals("")){xun="";}
else{
if(xun.equals("1")){
String s1="01";
String s2="10";
xun=" and to_char(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.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(tf_custinfoweb.CreateTime,'yyyymmdd') between '"+year+month+s1+"' and '"+year+month+s2+"'";
year="";
month="";
}
}
//按日期
if(rq==null||rq.equals("")){rq="";}
else{rq=" and to_char(tf_custinfoweb.CreateTime,'yyyy-mm-dd')='"+rq+"'";}
//按时间段
if(start==null||start.equals("")){start="";}
else{start=" and to_char(tf_custinfoweb.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 (select r.*,rownum num from(select distinct t.* from (");
sql.append("select td_area.areaname,Tf_CustInfoWeb.ACCOUNTID, Tf_CustInfoWeb.CREATETIME,Tf_CustInfoWeb.STATE,Tf_CustInfoWeb.NAME,Tf_CustInfoWeb.EMAIL,Tf_CustInfoWeb.PWDQUESTION,Tf_CustInfoWeb.PWDANSWER from Tf_CustInfoWeb ,");
if(relationproduct.equals("1")){
sql.append(" tf_andcustrela ,");
}
sql.append(" td_area ");
sql.append(" where tf_custinfoweb.CUSTTYPEID='2'");
if(relationproduct.equals("1")){
sql.append(" and tf_custinfoweb.userid=tf_andcustrela.userid");
}
sql.append(state);
sql.append(_state);
sql.append(areacode);
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 tf_custinfoweb.areacode=td_area.areacode");
sql.append(" group by tf_custinfoweb.areacode,td_area.areaname,tf_custinfoweb.ACCOUNTID,tf_custinfoweb.CREATETIME,tf_custinfoweb.STATE,tf_custinfoweb.NAME,tf_custinfoweb.EMAIL,tf_custinfoweb.PWDQUESTION,tf_custinfoweb.PWDANSWER");
sql.append(" order by Tf_CustInfoWeb.CREATETIME)t)r)f");
if(flag.equals("0")){
sql.append(" where f.num>");
sql.append(pagecount * (Integer.parseInt(pageNo) - 1));
sql.append(" and f.num<=");
sql.append(pagecount * Integer.parseInt(pageNo));
}
DataTable dt = null;
// System.out.println("--------------------------------------------------------------------------------");
// System.out.println("列表====================》"+sql.toString());
try {
dt = _dac.executeQuery(sql.toString());
}
catch (Exception ex) {
FileLogger.getLogger().debug(ex);
return null;
}
return dt;
}
//分页
public String buildPageOptions(int pageCount, int pageNo) {
String optionHTML = "0";
for (int i = 1; i <= pageCount; i++) {
if (pageNo == i || pageNo == 0)
optionHTML += "<option value='" + i + "' selected/>" + i;
else
optionHTML += "<option value='" + i + "'/>" + i;
}
return optionHTML;
}
/**
* 查询总有几页
* @param state 状态
* @param _state 帐号状态
* @param citycode 城市代码
* @return 返回页数
*/
public String getareaPageNo(String state,String _state,String areacode,String year,String quarter,String month,String xun,String week,String rq,String start,String end,String relationproduct) {
if(state.equals("")){state="";}
else{state=" and Tf_CustInfoWeb.STATE='"+state+"'";}
if(_state.equals("")){_state="";}
else{_state=" and Tf_CustInfoWeb.USERSTATE='"+_state+"'";}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -