query.java

一个比较简单的JAVA查帐代码

/*
 * Query.java
 * Created on 2003-11-20
 * Mod by zyc 2005-05-26  把getUserDetail系列方法中的查询条件acct_month = v_acct_month
 *                        改为     v_acct_month +1 > acct_month > v_acct_month -1
 *                        以提高查询语句的效率
 */
package com.softvan.project.unicomvip.datareward;
import com.softvan.project.unicomvip.database.DBAction;
import java.util.Vector;
import java.text.NumberFormat;
/**
 * @author grumiaux
 * @version 1.0, 2003-11-20
 *
 */
public class Query {

	/**
	 * 已返利查询页面所需数据，根据传入的四个参数查询某返利政策
	 * 下某个代理商在某个时间段之内的返利总额
	 * @param policy_id  查询条件1，返利政策id
	 * @param agent_id   查询条件2，代理商id
	 * @param begin_date 查询条件3，起始日期
	 * @param end_date   查询条件4，终止日期
	 * @return  返回的结果集包含在Vector中
	 * @throws Exception
	 */

	public Vector getPayQuery(String policy_id,String agent_id,String begin_date,String end_date) throws Exception{
		String select = "select t1.agent_id,t3.agent_name,t1.acct_month,decode(t2.status,'50',0,1),to_char(t2.check_date,'yyyy/mm/dd'),t4.user_name,t2.rtn_type,sum(t1.factor_pay) " +
			"from tat_pay_total t1,tat_pay_control t2,tat001 t3,tat005 t4,tat_policy_factor t5 where " +
			"t4.user_id=t2.checkuser_id and t1.agent_id=t3.agent_id " +
			"and t1.factor_id = t5.factor_id and t1.policy_id=t5.policy_id and t5.is_base='0' and " +
			"t1.acct_month=t2.acct_month and t1.make_month=t2.make_month and " +
			"t1.policy_id=t2.policy_id and t1.agent_id=t2.agent_id and t1.policy_id='"+policy_id+"' and " +
			"to_char(t2.check_date,'yyyymmdd')>='"+begin_date+"' and to_char(t2.check_date,'yyyymmdd')<='"+end_date+"' ";
		if(!agent_id.equals("all")){
			select += "and t1.agent_id='"+agent_id+"' ";
		}
		select += "group by t1.agent_id,t3.agent_name,t1.acct_month,decode(t2.status,'50',0,1),to_char(t2.check_date,'yyyy/mm/dd'),t4.user_name,t2.rtn_type " +
			"order by t1.agent_id,t1.acct_month";
		Vector result = DBAction.select(select);
		return result;
	}


	/**
	 * 得到tal_operate_log的分类条件列表
	 * @return  结果集包装到Vector中
	 * @throws Exception
	 */
	public Vector getLogClassify() throws Exception{
		String select = "select distinct(operate_classify) from tal_operate_log";
		Vector result = DBAction.select(select);
		return result;
	}


	/**
	 * 得到操作员列表
	 * @return  结果集包装到Vector中
	 * @throws Exception
	 */
	public Vector getModUser() throws Exception{
		String select = "select distinct(t2.user_name) " +
			"from tal_operate_log t1,tat005 t2 " +
			"where t1.operate_people=trim(t2.user_id)";
		Vector result = DBAction.select(select);
		return result;
	}


	/**
	 * 根据分类条件，操作员得到某段时间内的日志查询的结果集
	 * @param operate_classify 分类条件
	 * @param operate_people   操作员
	 * @param begin_date   查询起始时间
	 * @param end_date   查询终止时间
	 * @return  返回的结果集包装到一个Vector中
	 * @throws Exception
	 */
	public Vector getLogData(String operate_classify,String operate_people,String begin_date,String end_date) throws Exception{
		String select = "select t2.user_name,t1.operate_date,t1.operate_classify,t1.operate_table,t1.operate_code,nvl(t1.operate_condition,' '),t1.operate_desc " +
			"from tal_operate_log t1,tat005 t2 " +
			"where t1.operate_classify='"+operate_classify+"' and t2.user_name='"+operate_people+"' and t1.operate_people=trim(t2.user_id) and " +
			"to_char(t1.operate_date,'yyyymm')>='"+begin_date+"' and to_char(t1.operate_date,'yyyymm')<='"+end_date+"'";

		Vector result = DBAction.select(select);
		return result;
	}


	/**
	 * 代理商酬金汇总查询，查找某个返利政策下某帐期某种状态的代理商列表
	 * @param policy_id  返利政策id
	 * @param agent_status  代理商状态(all为所有状态,0为有效状态,其他为其他状态)
	 * @param acct_month  帐期
	 * @return
	 * @throws Exception
	 */
	public Vector getAgentPay(String policy_id,String agent_status,String acct_month) throws Exception{
		String select = "select t1.agent_id, t2.agent_name,t3.factor_value,t1.factor_value,t4.r1,t5.status " +
			"from tat_pay_total t1, tat001 t2,tat_pay_total t3,tat_pay_control t5," +
			"(select acct_month,make_month,policy_id,agent_id,sum(factor_pay) r1 " +
			"from tat_pay_total group by acct_month,make_month,policy_id,agent_id) t4 " +
			"where t1.agent_id = t2.agent_id and t1.factor_id='shishou' and t3.factor_id='yingshou' " +
			"and t3.agent_id=t1.agent_id and t1.make_month =t3.make_month and t1.acct_month=t3.acct_month " +
			"and t1.policy_id=t3.policy_id and t1.agent_id=t4.agent_id and t1.policy_id=t4.policy_id " +
			"and t1.make_month=t4.make_month and t1.acct_month=t4.acct_month and t1.acct_month='"+
			acct_month+"' and t1.policy_id='"+policy_id+"' and t1.acct_month=t5.acct_month and " +
			"t1.make_month=t5.make_month and t1.agent_id=t5.agent_id and t1.policy_id=t5.policy_id ";
		if(!agent_status.equals("all")){
			if(!agent_status.equals("0")){
				select+=" and t5.status='"+agent_status+"'";
			}else if(agent_status.equals("0")){
				select+=" and t5.status in ('0','20','30','50')";
			}
		}
	    Vector result = DBAction.select(select);
	    return result;
	}


	/**
	 * 代理商返利政策查询（按返利政策排序）
	 * @param policy_id 返利政策id
	 * @return  查到的结果集包装到Vector中
	 * @throws Exception
	 */
	public Vector getAgentPolicyQueryByPolicy(String policy_id) throws Exception{
		String select = "select t3.policy_name,t1.agent_id,t2.agent_name,t4.r1 " +
			"from tat_policy_agent t1,tat001 t2,tat_policy t3," +
			"(select policy_id,count(agent_id) r1 from tat_policy_agent ";
		if(!policy_id.equals("all")){
            select += "where policy_id='" +policy_id+"' ";
		}
		select += "group by policy_id) t4 where t1.agent_id=t2.agent_id and t1.policy_id=t3.policy_id";
		if(!policy_id.equals("all")){
			select += " and t1.policy_id='"+policy_id+"'";
		}
		Vector result = DBAction.select(select);
		return result;
	}


	/**
	 * 代理商返利政策查询（按代理商id排序）
	 * @param agent_id 代理商id
	 * @return  结果包装到Vector中
	 * @throws Exception
	 */
	public Vector getAgentPolicyQueryByAgent(String agent_id) throws Exception{
		String select = "select t1.agent_id,t2.agent_name,t3.policy_name,t4.r1 " +
			"from tat_policy_agent t1,tat001 t2,tat_policy t3," +
			"(select agent_id,count(policy_id) r1 from tat_policy_agent ";
		if(!agent_id.equals("all")){
			select += "where agent_id='"+agent_id+"' ";
		}
		select += "group by agent_id) t4 where t1.agent_id=t2.agent_id and t1.policy_id=t3.policy_id and t1.agent_id=t4.agent_id";
		if(!agent_id.equals("all")){
			select += " and t1.agent_id='"+agent_id+"'";
		}
		Vector result = DBAction.select(select);
		return result;
	}

	/**
	 * 查找政策对应的tele_type
	 * @param policyid
	 * @return
	 * @throws Exception
	 */
	public String getTeleType(String policyid) throws Exception{
		String select = "select tele_type from tat_policy where policy_id='"+policyid+"'";
		Vector result = DBAction.select(select);
		String tele_type = ((Vector)result.get(0)).get(0).toString();
		return tele_type;

	}
	/**
	 * 用户缴费明细查询，按业务类型分类，
	 * 如果是193业务的话，调用get193UserDetail()方法
	 * 如果是voip业务的话，调用getIpUserDetail()方法
	 * @param policy_id  返利政策id
	 * @param agent_id   代理商id
	 * @param acct_month  帐期
	 * @param status  支付状态
	 * @return  结果集包装到Vector中
	 * @throws Exception
	 */
	public Vector getUserDetail(String policy_id,String agent_id,String acct_month,String status) throws Exception{

		Vector result = null;
		String tele_type = getTeleType(policy_id);
		if(tele_type.equals("VOIP")){

			result = getIp(agent_id,acct_month,status,policy_id);

		}else if(policy_id.equals("0009")){

			result = getG0(agent_id,acct_month,status);

		}else if(policy_id.equals("0010")){

			result = getG1(agent_id,acct_month,status);

		}else if(tele_type.equals("193")){

			result = get193(policy_id,agent_id,acct_month,status);

		}else if(tele_type.equals("CDMAX")){

			result = getCDMA1XUserDetail(agent_id,acct_month,status,policy_id);
		} else if(tele_type.equals("ZX")){

            result = getZxDetail(policy_id,agent_id,acct_month,status);
		}


		return result;
	}



    private Vector getZxDetail(String policy_id,String agentid,String rtn_date,String status) throws Exception{
        String m_st_where4status = "";
        String  m_sql = "";
        if (policy_id.equals("0051")) {
            if ( status.equalsIgnoreCase("all") )
                    {
                        m_st_where4status = "";
                    }else  if ( status.equalsIgnoreCase("payed") )
                    {
                        m_st_where4status = " Where pay - payed = 0 ";
                    }else  if (status.equalsIgnoreCase("none") )
                    {
                        m_st_where4status = " Where pay - payed > 0 ";
                    }
                    m_sql = "Select * from ( select tat_info_hire.device_number,tat038.customer_name,";
                    m_sql += "sum(tat030.fav_charge-tat030.derate_charge-tat030.adjust_charge-tat030.disct_charge-tat030.prom_charge)  pay ,";
                    m_sql += "sum(tat030.pay_charge)  payed ,tat044.pay_type_name  ";
                    m_sql += "from tat030,tat038,tat_info_hire,tat044 ";
                    m_sql += "where tat030.acct_month > to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), -1),'yyyymm')  and  "
                          +  " tat030.acct_month < to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), 1),'yyyymm')  and ";
                    m_sql += " tat030.charge_code = 'H999' and ";
                    m_sql += "tat_info_hire.acct_month> to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), -1),'yyyymm')  and  "
                          +  " tat_info_hire.acct_month < to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), 1),'yyyymm') and ";
                    m_sql += " tat_info_hire.dept_no = '"+agentid+"' and ";
                    m_sql += "tat_info_hire.user_dinner in (select user_dinner from tat_policy_dinner where policy_id='"+policy_id+"') and ";
                    m_sql += "tat030.user_no = tat_info_hire.user_no and ";
                    m_sql += "tat038.customer_no = tat_info_hire.customer_no and ";
                    m_sql += "tat044.pay_type = tat030.pay_type ";
                    m_sql += "group by tat_info_hire.device_number,tat044.pay_type_name,tat038.customer_name ";
                    m_sql += "order by tat_info_hire.device_number) "+m_st_where4status;


        } else {
            if ( status.equalsIgnoreCase("all") )
                    {
                        m_st_where4status = "";
                    }else  if ( status.equalsIgnoreCase("payed") )
                    {
                        m_st_where4status = " Where pay - payed = 0 ";