togetherbean.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.statana;

import java.util.Date;

import com.doone.data.DacClient;
import com.doone.data.DataTable;
import com.doone.util.FileLogger;
import com.doone.data.*;

public class TogetherBean {
	private DacClient _dac = null;

	private DataTable dt = null;

	private int pageSize = 20; // 默认每页20条

	private int pageCount = 0;

	public TogetherBean() {
		try {
			_dac = new DacClient();

		} catch (Exception ex) {
			FileLogger.getLogger().warn("数据库连接出错！", ex);
		}
	}
/**
 * 以业务类型来查询业务受理量
 * @param str
 * @param start
 * @param end
 * @return
 */
  public DataTable query_pro(){
	  dt=null;
	  String sql="select distinct PRODUCTNAME,PRODUCTID from td_product t where t.upproductid='0'";
	  try{
	  dt = _dac.executeQuery(sql);}catch(Exception ex){ex.printStackTrace();}
	  return dt;
  }	
	
	public DataTable queryItem(String str, String start, String end,String sllx,String ywlx) {

		dt = null;
		String realend = "";
		String sql = "";
		String _sql = "";
		if (str.equals("0590"))
			str = "";
		else
		str = "where City.Citycode=" + str;
		if(sllx.equals("")){sql="";}
		else{sql=" and b.OPERATIONTYPE='"+sllx+"'";}
		if(ywlx.equals("")){_sql="";}
		else{_sql=" and a.PRODUCTID="+ywlx;}
		if (start == "" && end == "") {
			start = "20040101";
			end = "30080101";
		}
		 realend = checkDay(end);

		try {
			//java.util.Date d =new Date();
			String sqlstr = "select City.Cityname,Area.Areaname,Rpt.type,Rpt.esp,Rpt.cnt,Rpt.BgnTime||'~'||Rpt.EndTime btime"
					+ " from Td_Area Area "
					+ " join Td_City City on Area.Citycode=City.Citycode"
					+ " join "
					+ " (Select c.areacode,"
					+ " a.Productname esp, b.OPERATIONTYPE type,"
					+ " to_date(?, 'YYYYMMDD') BgnTime,"
					+ " to_date(?,'YYYYMMDD') EndTime,"
					+ " Count(*) cnt"
					+ " From Td_Product a join "
					+ " Tf_Prodorder b on a.Productid = b.Productid "
					+ " join  tf_Address c on b.prodorderid = c.prodorderid"
					+ " where b.AcceptTime Between to_date(?, 'YYYYMMDD') And to_date(?, 'YYYYMMDD') "
					+ sql
					+_sql
					+ " Group By a.Productid, a.Productname, c.areacode, b.OPERATIONTYPE"
					+ " )"
					+ " Rpt   on Area.Areacode=Rpt.areacode  "
					+ str
					+ " order By City.Cityname";
			
			Object[] _object = new Object[4];
			_object[0] = start;
			_object[1] = end;
			_object[2] = start;
			_object[3] = realend;
			dt = _dac.executeQuery(sqlstr, _object);

		} catch (Exception ex) {
			FileLogger.getLogger().warn("数据库查询出错！", ex);

		}
		return dt;

	}
	
	public DataTable queryItem_sjd(String str, String start, String end,String sllx,String ywlx) {

		dt = null;
		String realend = "";
		String sql = "";
		String _sql = "";
		if (str.equals("0590"))
			str = "";
		else
		str = "where City.Citycode=" + str;
		if(sllx.equals("")){sql="";}
		else{sql=" and b.OPERATIONTYPE='"+sllx+"'";}
		if(ywlx.equals("")){_sql="";}
		else{_sql=" and a.PRODUCTID="+ywlx;}
		if (start == "" && end == "") {
			start = "20040101";
			end = "30080101";
		}
		 realend = checkDay(end);

		try {
			//java.util.Date d =new Date();
			String sqlstr = "select City.Cityname,Area.Areaname,Rpt.type,Rpt.esp,Rpt.cnt,Rpt.BgnTime||'~'||Rpt.EndTime btime"
					+ " from Td_Area Area "
					+ " join Td_City City on Area.Citycode=City.Citycode"
					+ " join "
					+ " (Select c.areacode,"
					+ " a.Productname esp, b.OPERATIONTYPE type,"
					+ " to_date(?, 'YYYYMMDDHH24mi') BgnTime,"
					+ " to_date(?,'YYYYMMDDHH24mi') EndTime,"
					+ " Count(*) cnt"
					+ " From Td_Product a join "
					+ " Tf_Prodorder b on a.Productid = b.Productid "
					+ " join  tf_Address c on b.prodorderid = c.prodorderid"
					+ " where b.AcceptTime Between to_date(?, 'YYYYMMDDHH24mi') And to_date(?, 'YYYYMMDDHH24mi') "
					+ sql
					+_sql
					+ " Group By a.Productid, a.Productname, c.areacode, b.OPERATIONTYPE"
					+ " )"
					+ " Rpt   on Area.Areacode=Rpt.areacode  "
					+ str
					+ " order By City.Cityname";
			
			Object[] _object = new Object[4];
			_object[0] = start;
			_object[1] = end;
			_object[2] = start;
			_object[3] = realend;
			dt = _dac.executeQuery(sqlstr, _object);

		} catch (Exception ex) {
			FileLogger.getLogger().warn("数据库查询出错！", ex);

		}
		return dt;

	}
	
/**
 * 以状态方式来查询业务受理量
 * @param str
 * @param start
 * @param end
 * @return
 */
	public DataTable queryState(String str, String start, String end) {
		dt = null;

		if (start == "" && end == "") {
			start = "20050101";
			end = "20080101";
		}
		String realend = checkDay(end);
		if (str.equals("0590"))
			str = "";
		else{str=" and b.citycode="+ str;}

		try {
				
				String sqlstr="select count(*) cnt,PROGRESSSTATE esp,b.citycode,c.areaname,d.cityname  from Tf_Prodorder a,tf_address b,td_area c ,Td_City  d where" +
				"  a.prodorderid=b.prodorderid and d.Citycode=c.Citycode and c.citycode=b.citycode and b.AREACODE=c.AREACODE " +str+
				"  and a.UPPRODORDERID='0' " +
				"  and a.AcceptTime Between to_date(?, 'YYYYMMDD') And to_date(?, 'YYYYMMDD')"+
				" group by PROGRESSSTATE,b.citycode,c.areaname,d.cityname";
			
		Object[] _object = new Object[2];
		_object[0] = start;
		_object[1] = end;
		dt = _dac.executeQuery(sqlstr, _object);
	} catch (Exception ex) {
		FileLogger.getLogger().warn("数据库查询出错！", ex);
	}
	return dt;


	}
	
	public DataTable queryState_sjd(String str, String start, String end) {
		dt = null;

		if (start == "" && end == "") {
			start = "20050101";
			end = "20080101";
		}
		String realend = checkDay(end);
		if (str.equals("0590"))
			str = "";
		else{str=" and b.citycode="+ str;}

		try {
				String sqlstr="select count(*) cnt,PROGRESSSTATE esp,b.citycode,c.areaname,d.cityname  from Tf_Prodorder a,tf_address b,td_area c ,Td_City  d where" +
				"  a.prodorderid=b.prodorderid and d.Citycode=c.Citycode and c.citycode=b.citycode and b.AREACODE=c.AREACODE " +str+
				"  and a.UPPRODORDERID='0' " +
				"  and a.AcceptTime Between to_date(?, 'YYYYMMDDHH24mi') And to_date(?, 'YYYYMMDDHH24mi')"+
				" group by PROGRESSSTATE,b.citycode,c.areaname,d.cityname";
			
		Object[] _object = new Object[2];
		_object[0] = start;
		_object[1] = end;
		dt = _dac.executeQuery(sqlstr, _object);
	} catch (Exception ex) {
		FileLogger.getLogger().warn("数据库查询出错！", ex);
	}
	return dt;


	}
/**
 * 用于以时间方式查询
 * @param str 用户注册的地址
 * @param start 开始时间
 * @param end  结束时间
 * @return 满足条件的记录表
 */
	public DataTable queryByTime(String str, String start, String end) {

		dt = null;
		if (start.equals("") && end.equals("")) {
			start = "20050101";
			end = "20080101";
		}

		try {
			//    String sqlstr="select
			// City.Cityname,Area.Areaname,Rpt.cnt,Rpt.BgnTime||'~'||Rpt.EndTime"+
			//         " from Td_Area Area,"+
			//         " Td_City City,"+
			//         " (select count(*) cnt,a.PROGRESSSTATE ,c.areacode,to_date(?,
			// 'YYYYMMDD') BgnTime,"+
			//         " to_date(?,'YYYYMMDD') EndTime"+
			//         " from tf_prodorder a,tf_funorder b,tf_address c "+
			//         " where a.accepttime=b.accepttime "+
			//         " and a.prodorderid=c.prodorderid "+
			//         " and c.citycode=b.citycode"+
			//         " And a.AcceptTime Between to_date(?, 'YYYYMMDD') And to_date(?,
			// 'YYYYMMDD') "+
			//         " And b.AcceptTime Between to_date(?, 'YYYYMMDD') And to_date(?,
			// 'YYYYMMDD') "+
			//         " group by a.PROGRESSSTATE,c.areacode) Rpt"+
			//         " where Area.Citycode=City.Citycode"+
			//         " and Area.Areacode=Rpt.areacode"+str+
			//         " order By City.Cityname";
			String sqlstr = "";
			if (end == "" && start != "" || end == null) {
				if (str.equals("0590"))
					str = "";
				else
					str = " And c.Citycode=" + str;
				sqlstr = "select count(*) cnt,Area.Areaname,d.CITYNAME,to_date(?, 'YYYYMMDD') btime"
						+ " from tf_prodorder a,tf_funorder b,tf_address c,TD_CITY d,Td_Area Area"
						+ " where  a.prodorderid=c.prodorderid And a.ACCEPTTIME Between to_date(?, 'YYYYMMDD') And to_date(?, 'YYYYMMDD')"
						+ " And b.ACCEPTTIME Between to_date(?, 'YYYYMMDD') And to_date(?, 'YYYYMMDD')"
						+ " And c.CITYCODE=d.CITYCODE"
						+ " And c.areacode=Area.Areacode"
						+ str
						+ "  Group By a.ACCEPTTIME,d.CITYNAME,c.AREACODE,Area.Areaname";
				end = start;
				String realend = checkDay(end);
				Object[] _object = new Object[5];
				_object[0] = start;
				_object[1] = start;
				_object[2] = realend;
				_object[3] = start;
				_object[4] = realend;
				dt = _dac.executeQuery(sqlstr, _object);
			} else {
				if (str.equals("0590"))
					str = "";
				else
					str = " And City.Citycode=" + str;
				String realend = checkDay(end);
				sqlstr = "select City.Cityname,Area.Areaname,Rpt.cnt,Rpt.BgnTime||'~'||Rpt.EndTime  btime"
						+ " from Td_Area  Area,"
						+ " Td_City City, "
						+ " (select count(*) cnt,a.PROGRESSSTATE ,c.areacode,to_date(?, 'YYYYMMDD') BgnTime,"
						+ " to_date(?,'YYYYMMDD') EndTime"
						+ " from tf_prodorder a,tf_funorder b,tf_address c "
						+ " where  a.prodorderid=c.prodorderid"
						+ " and c.citycode=b.citycode"
						+ " And a.AcceptTime Between to_date(?, 'YYYYMMDD') And to_date(?, 'YYYYMMDD') "
						+ " And b.AcceptTime Between to_date(?, 'YYYYMMDD') And to_date(?, 'YYYYMMDD')"