📄 query.java
字号:
}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;
}
Vector result = DBAction.select(m_sql);
return result;
}
/**
*
* @param agentid
* @param rtn_date
* @param status
* @return
* @throws Exception
*/
private Vector get193(String policy_id,String agentid,String rtn_date,String status) throws Exception{
String m_st_where4status = "";
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 ";
}
String m_sql = "Select * from ( select tat040.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,tat040,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 += "tat040.acct_month > to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), -1),'yyyymm') and "
+ "tat040.acct_month < to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), 1),'yyyymm') and ";
m_sql += "tat040.dept_no = '"+agentid+"' and ";
m_sql += "tat040.user_dinner in (select user_dinner from tat_policy_dinner where policy_id='"+policy_id+"') and ";
m_sql += "tat030.user_no = tat040.user_no and ";
m_sql += "tat038.customer_no = tat040.customer_no and ";
m_sql += "tat044.pay_type = tat030.pay_type ";
m_sql += "group by tat040.device_number,tat044.pay_type_name,tat038.customer_name ";
m_sql += "order by tat040.device_number) "+m_st_where4status;
Vector result = DBAction.select(m_sql);
return result;
}
/**
*
* @param agent_id
* @param acct_month
* @param status
* @return
*/
private Vector getCDMA1XUserDetail(String agent_id,String acct_month,String status,String policy_id) throws Exception{
String m_st_where4status = "";
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 ";
}
String m_sql = "select t001.device_number,t003.customer_name,t001.pay," +
"t001.payed,t004.pay_type_name,t002.pay,t002.payed from " +
"(select t01.device_number,t01.user_no,t01.acct_month,t01.customer_no," +
"t01.pay_type,t01.pay,t01.payed from ( select t3.device_number,t3.user_no," +
"t3.acct_month,t3.customer_no,t1.pay_type,sum(t1.fav_charge-t1.derate_charge-t1.adjust_charge-t1.disct_charge-t1.prom_charge) pay ," +
"sum(t1.pay_charge) payed from tat030 t1,tat036 t3 " +
"where t1.acct_month > to_char(add_months(to_date('"+acct_month+"', 'yyyymm'), -1),'yyyymm') " +
" and t1.acct_month < to_char(add_months(to_date('"+acct_month+"', 'yyyymm'), 1),'yyyymm') " +
"and t3.dept_no = '"+agent_id+"' and " +
" t3.user_dinner in (select user_dinner from tat_policy_dinner where policy_id='"+policy_id+"') and t1.user_no = t3.user_no and t1.acct_month=t3.acct_month " +
"group by t3.device_number,t3.user_no,t3.acct_month,t3.customer_no,t1.pay_type ) t01 "+m_st_where4status+") t001," +
"(select t01.device_number,t01.user_no,t01.acct_month,t01.customer_no,t01.pay_type,t01.pay,t01.payed " +
"from ( select t3.device_number,t3.user_no,t3.acct_month,t3.customer_no," +
"t1.pay_type,sum(t1.fav_charge-t1.derate_charge-t1.adjust_charge-t1.disct_charge-t1.prom_charge) pay ,sum(t1.pay_charge) payed " +
"from tat030 t1,tat036 t3 where " +
" t1.acct_month > to_char(add_months(to_date('"+acct_month+"', 'yyyymm'), -1),'yyyymm') and " +
" t1.acct_month < to_char(add_months(to_date('"+acct_month+"', 'yyyymm'), 1),'yyyymm') and " +
"t3.dept_no = '"+agent_id+"' and t1.user_no = t3.user_no and t1.charge_code in('C003','C1XS') and t3.user_dinner in (select user_dinner from tat_policy_dinner where policy_id='"+policy_id+"') and t1.acct_month=t3.acct_month " +
"group by t3.device_number,t3.user_no,t3.acct_month,t3.customer_no,t1.pay_type ) " +
"t01 "+m_st_where4status+") t002,tat038 t003,tat044 t004 " +
"where t001.device_number=t002.device_number and t001.user_no=t002.user_no and " +
"t001.customer_no=t002.customer_no and t001.customer_no=t003.customer_no and " +
"t001.pay_type=t004.pay_type";
Vector result = DBAction.select(m_sql);
return result;
}
/**
*
* @param agentid
* @param rtn_date
* @param status
* @return
* @throws Exception
*/
private Vector getIp(String agentid,String rtn_date,String status,String policy_id) throws Exception{
String str_SQL1 = "";
if(status.compareTo("all") == 0)
str_SQL1 = "";
else
if(status.compareTo("payed") == 0)
str_SQL1 = " Where pay - payed = 0 ";
else
if(status.compareTo("none") == 0)
str_SQL1 = " Where pay - payed > 0 ";
String m_sql = "Select * from ( select tat039.device_number,tat038.customer_name," +
"sum(tat030.fav_charge-tat030.derate_charge-tat030.adjust_charge-tat030.disct_charge-tat030.prom_charge) pay " +
",sum(tat030.pay_charge) payed ,tat044.pay_type_name " +
"from tat030,tat038,tat039,tat044 " +
"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 " +
"tat039.acct_month > to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), -1),'yyyymm') and " +
"tat039.acct_month < to_char(add_months(to_date('"+rtn_date+"', 'yyyymm'), 1),'yyyymm') and "+
"tat039.dept_no = '" + agentid +"' and " +
"tat039.user_dinner in (select user_dinner from tat_policy_dinner where policy_id='"+policy_id+"') and " +
"tat030.user_no = tat039.user_no and " +
"tat038.customer_no = tat039.customer_no and " +
"tat044.pay_type = tat030.pay_type " +
"group by tat039.device_number,tat044.pay_type_name,tat038.customer_name " +
"order by tat039.device_number) " +str_SQL1;
Vector result = DBAction.select(m_sql);
return result;
}
private Vector getG0(String agentid,String permonth,String status) throws Exception
{
String m_sql = "";
String str_Agent = "";
String str_Acct = null;
if(agentid!=null && agentid.trim().compareTo("all")==0) {
str_Agent = "";
} else {
str_Agent = "tat035.dept_no = '"+agentid+"' and ";
}
String m_st_where4status = "";
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 ";
}
//Added by WangCong 2003-08-20
if (Integer.parseInt(permonth)<200307)
str_Acct="200307";
else
str_Acct=permonth;
//////////////////////////
m_sql = "select * from (select tat030.device_number,tat038.customer_name,"
+"sum(tat030.fav_charge - tat030.derate_charge - tat030.adjust_charge - tat030.disct_charge - tat030.prom_charge) pay,sum(tat030.pay_charge) payed,tat044.pay_type_name "
+"from tat001,tat030,tat038,tat035,tat044 "
+"where tat035.acct_month > to_char(add_months(to_date('"+str_Acct+"', 'yyyymm'), -1),'yyyymm') and "
+" tat035.acct_month < to_char(add_months(to_date('"+str_Acct+"', 'yyyymm'), 1),'yyyymm') and "
+"tat035.user_dinner in (select user_dinner from tat_policy_dinner where policy_id='0009') and "
+"tat030.acct_month > to_char(add_months(to_date('"+permonth+"', 'yyyymm'), -1),'yyyymm') and "
+"tat030.acct_month < to_char(add_months(to_date('"+permonth+"', 'yyyymm'), 1),'yyyymm') and "
+str_Agent
+"tat030.user_no = tat035.user_no and "
+"tat038.customer_no = tat035.customer_no and "
+"tat001.agent_id = tat035.dept_no and "
+"tat044.pay_type = tat030.pay_type "
+"group by tat001.agent_id,tat001.agent_name,tat030.device_number,tat035.user_no,tat038.customer_name,tat044.pay_type_name "
+"order by agent_id,device_number) " +m_st_where4status;
Vector result = DBAction.select(m_sql);
return result;
}
private Vector getG1(String agentid,String permonth,String status) throws Exception
{
String m_sql = "";
String str_Agent = "";
String str_Acct = null;
if(agentid!=null && agentid.trim().compareTo("all")==0) {
str_Agent = "";
} else {
str_Agent = "tat035.dept_no = '"+agentid+"' and ";
}
String m_st_where4status = "";
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 ";
}
//Added by WangCong 2003-08-20
if (Integer.parseInt(permonth)<200307)
str_Acct="200307";
else
str_Acct=permonth;
//////////////////////////
m_sql = "select * from ( select tat030.device_number,tat038.customer_name,"
+"sum(tat030.fav_charge - tat030.derate_charge - tat030.adjust_charge - tat030.disct_charge - tat030.prom_charge) pay,sum(tat030.pay_charge) payed,tat044.pay_type_name "
+"from tat001,tat030,tat038,tat035,tat044 "
+"where tat035.acct_month > to_char(add_months(to_date('"+str_Acct+"', 'yyyymm'), -1),'yyyymm') and "
+" tat035.acct_month < to_char(add_months(to_date('"+str_Acct+"', 'yyyymm'), 1),'yyyymm') and "
+"tat035.user_dinner in (select user_dinner from tat_policy_dinner where policy_id='0010') and "
+"tat030.acct_month > to_char(add_months(to_date('"+permonth+"', 'yyyymm'), -1),'yyyymm') and "
+"tat030.acct_month < to_char(add_months(to_date('"+permonth+"', 'yyyymm'), 1),'yyyymm') and "
+str_Agent
+"tat030.user_no = tat035.user_no and "
+"tat038.customer_no = tat035.customer_no and "
+"tat001.agent_id = tat035.dept_no and "
+"tat044.pay_type = tat030.pay_type "
+"group by tat001.agent_id,tat001.agent_name,tat030.device_number,tat035.user_no,tat038.customer_name,tat044.pay_type_name "
+"order by agent_id,device_number) "+m_st_where4status;
Vector result = DBAction.select(m_sql);
return result;
}
public String formatValue(double value){
NumberFormat nf = NumberFormat.getInstance();
nf.setMaximumFractionDigits(2);
return nf.format(value);
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -