insurancewillreport.java

java swing源码 欢迎下载 有问题请联系 我一定负责到底

/**
 * 投保意向报表
 */
package com.NCL.excel;

import java.util.ArrayList;
import java.util.List;
import com.sinosoft.common.*;

public class InsuranceWillReport {

	public List strList = new ArrayList();
	/**
	 * 投保意向-年报
	 * @param year
	 * @return
	 */
	public List yearReportDo(String year){
		int yearAddOne = Integer.parseInt(year)+1;
		String SQL="";
		DBAccess d = new DBAccess();
		SQL = "Select nvl("
				+" (select Sum(to_date(to_char(dealdate,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(makedate,'YYYY-MM-DD'),'YYYY-MM-DD'))" 
				+" from INSURANCEWILL where dealflag = 1"
				+" and dealdate >= to_date('"+year+"-01', 'yyyy-MM')" 
				+" and dealdate < to_date('"+yearAddOne+"-01', 'yyyy-MM')"
				+" )"
				+" /(select count(dealflag) from INSURANCEWILL where dealflag = 1" 
				+" and dealdate >= to_date('"+year+"-01', 'yyyy-MM')"
				+" and dealdate < to_date('"+yearAddOne+"-01', 'yyyy-MM')"
				+" ),0)" 
				+" as Average from months where nu=1";
		strList = d.parseSQL(SQL, new ArrayList());
		if(Data.hasValue(year))
			SQL="select " 
				+" m.nu,"
				//       --已受理
				+"      nvl(" 
				+"           (" 
				+"           select Count(t1.DEALFLAG) " 
				+"                       from INSURANCEWILL t1" 
				+"                       where t1.DEALFLAG=1 " 
				+"                       and t1.DEALDATE >= to_date('"+year+"' || m.nu, 'yyyy-MM')" 
				+"                       and t1.DEALDATE <add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)),0) as DEAL," 
				//       --1天内处理
				+"     nvl(" 
				+"             (" 
				+"              select Count(t3.DEALFLAG) " 
				+"                     from INSURANCEWILL t3" 
				+"                    where t3.DEALFLAG=1 " 
				+"                     and t3.DEALDATE >= to_date('"+year+"' || m.nu, 'yyyy-MM')" 
				+"                     and t3.DEALDATE <add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)" 
				+"                      and (to_date(to_char(t3.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t3.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) = 0),0)as OneCount,"
				//--1－2日处理
				+"         nvl(" 
				+"              (" 
				+"               select Count(t4.DEALFLAG) " 
				+"                      from INSURANCEWILL t4" 
				+"                      where t4.DEALFLAG=1 " 
				+"                      and t4.DEALDATE >= to_date('"+year+"' || m.nu, 'yyyy-MM')" 
				+"                       and t4.DEALDATE <add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)" 
				+"                      and (to_date(to_char(t4.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t4.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) in (1,2)),0)as OneAndTwoCount,"
				//       --2日处理
				+"     nvl(" 
				+"              (select Count(t5.DEALFLAG) " 
				+"                       from INSURANCEWILL t5" 
				+"                       where t5.DEALFLAG=1 " 
				+"                      and t5.DEALDATE >= to_date('"+year+"' || m.nu, 'yyyy-MM')" 
				+"                     and t5.DEALDATE <add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)" 
				+"                      and (to_date(to_char(t5.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t5.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) in (0,1,2)),0)as TwoCount,"
				//      --2日以上处理
				+"      nvl(" 
				+"          (" 
				+"              select Count(t6.DEALFLAG) " 
				+"                     from INSURANCEWILL t6" 
				+"                     where t6.DEALFLAG=1 " 
				+"                     and t6.DEALDATE >= to_date('"+year+"' || m.nu, 'yyyy-MM')" 
				+"                     and t6.DEALDATE <add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1) "  
				+"                      and (to_date(to_char(t6.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t6.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))>2),0)as OverTwoCount,"
				//平均时效
				+" nvl("
				+" (select Sum(to_date(to_char(dealdate,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(makedate,'YYYY-MM-DD'),'YYYY-MM-DD'))" 
				+" from INSURANCEWILL where dealflag = 1"
				+" and dealdate >= to_date('"+year+"'||m.nu, 'yyyy-MM')" 
				+" and dealdate < add_months(to_date('"+year+"'||m.nu, 'yyyy-MM'), 1)"
				+" )"
				+" /(select count(dealflag) from INSURANCEWILL where dealflag = 1" 
				+" and dealdate >= to_date('"+year+"'||m.nu, 'yyyy-MM')"
				+" and dealdate < add_months(to_date('"+year+"'||m.nu, 'yyyy-MM'), 1)"
				+" ),0)" 
				+" as Average from months m where m.nu>=1 and m.nu<=12 order by m.nu";
		return d.mulparseSQL(SQL, new ArrayList());
	}
	/**
	 * 按机构查询
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List comReportDo(String startDate,String endDate){
		String SQL="";
		DBAccess d = new DBAccess();
		SQL = "Select nvl("
				+" (select Sum(to_date(to_char(dealdate,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(makedate,'YYYY-MM-DD'),'YYYY-MM-DD'))" 
				+" from INSURANCEWILL where dealflag = 1"
				+" and dealdate >= to_date('"+startDate+"', 'YYYY-MM-DD')" 
				+" and dealdate < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+" )"
				+" /(select count(dealflag) from INSURANCEWILL where dealflag = 1" 
				+" and dealdate >= to_date('"+startDate+"', 'YYYY-MM-DD')"
				+" and dealdate < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+" ),0)" 
				+" as Average from months where nu=1";
		strList = d.parseSQL(SQL, new ArrayList());
		//按投保机构
		//取年份 startdate:2007-08-01 enddate:2007-09-30
		if(Data.hasValue(startDate)&&Data.hasValue(endDate))
			SQL="select"
				+" l.name,"
				//已受理
				+" nvl((select Count(t1.DEALFLAG)"
				+"      from INSURANCEWILL t1"
				+"     where t1.DEALFLAG = 1"
				+"      and t1.DEALDATE >="
				+"          to_date('"+startDate+"', 'YYYY-MM-DD')"
				+"      and t1.DEALDATE <"
				+"          (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+"      and t1.SERVICEORG = l.comcode),0) as DEAL,"
				 //1天内处理
				+"nvl((select Count(t3.DEALFLAG)"
				+"     from INSURANCEWILL t3"
				+"    where t3.DEALFLAG = 1"
				+"      and t3.DEALDATE >="
				+"          to_date('"+startDate+"', 'YYYY-MM-DD')"
				+"      and t3.DEALDATE <"
				+"          (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+"      and t3.SERVICEORG = l.comcode"
				+"                      and (to_date(to_char(t3.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t3.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) = 0),0)as OneCount,"
				 //--1－2日处理
				+"nvl((select Count(t4.DEALFLAG)"
				+"     from INSURANCEWILL t4"
				+"    where t4.DEALFLAG = 1"
				+"      and t4.DEALDATE >= to_date('"+startDate+"', 'YYYY-MM-DD')"
				+"      and t4.DEALDATE < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+"      and t4.SERVICEORG = l.comcode"
				+"                      and (to_date(to_char(t4.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t4.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) in (1,2)),0)as OneAndTwoCount,"
				 //2日处理
				+"nvl((select Count(t5.DEALFLAG)"
				+"     from INSURANCEWILL t5"
				+"    where t5.DEALFLAG = 1"
				+"      and t5.DEALDATE >= to_date('"+startDate+"', 'YYYY-MM-DD')"
				+"      and t5.DEALDATE <  (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+"      and t5.SERVICEORG = l.comcode"
				+"                      and (to_date(to_char(t5.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t5.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) in (0,1,2)),0)as TwoCount,"
				 //2日以上处理
				+"nvl((select Count(t6.DEALFLAG)"
				+"      from INSURANCEWILL t6"
				+"    where t6.DEALFLAG = 1"
				+"      and t6.DEALDATE >="
				+"          to_date('"+startDate+"', 'YYYY-MM-DD')"
				+"      and t6.DEALDATE <"
				+"          (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+"      and t6.SERVICEORG = l.comcode"
				+"                      and (to_date(to_char(t6.DEALDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(t6.MAKEDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) >2),0)as OverTwoCount,"
				//平均时效
				+" nvl("
				+" (select Sum(to_date(to_char(dealdate,'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(makedate,'YYYY-MM-DD'),'YYYY-MM-DD'))" 
				+" from INSURANCEWILL where dealflag = 1"
				+" and SERVICEORG = l.comcode"
				+" and dealdate >= to_date('"+startDate+"', 'YYYY-MM-DD')" 
				+" and dealdate < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+" )"
				+" /(select count(dealflag) from INSURANCEWILL where dealflag = 1" 
				+" and SERVICEORG = l.comcode"
				+" and dealdate >= to_date('"+startDate+"', 'YYYY-MM-DD')"
				+" and dealdate < (to_date('"+endDate+"', 'YYYY-MM-DD') + 1)"
				+" ),0)" 
				+" as Average from LDCOM l where comgrade='1' or comgrade='2' order by comgrade";
		return d.mulparseSQL(SQL, new ArrayList());
	}
	
	public static void main(String args[]){
	}
}