excelout.java

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

/**
 * 统计量报表
 */
package com.NCL.excel;

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

public class ExcelOut {

	private List strList = new ArrayList();
	/**
	 * 构造函数
	 *
	 */
	public ExcelOut(){		
	}
	/**
	 * 年报参数设定
	 * @param ls
	 * @param j
	 */
	public void setList(String ls, int j){
		for(int i=0; i<j ;i++){
			this.strList.add(ls);
		}	
	}
	/**
	 * 月报参数设定
	 * @param month
	 */
	public void setMonthList(String month){
		this.strList.add(month);		
	}	
	/**
	 * 按照时间段进行查询
	 * @param STime
	 * @param ETime
	 * @param j
	 */
	public void setTimeList(String STime,String ETime, int j){
		for(int i=0;i<j;i++){
			this.strList.add(STime);
			this.strList.add(ETime);
		}		
	}	
	/**
	 * 业务报表查询-月报
	 * @return
	 */
	public List dayFind(){		
		StringBuffer sql = new StringBuffer("select months.nu month, ");
		sql.append("(select count(*) from Members where makedate < to_date(?||'-' || months.nu, 'yyyy-MM-dd') + 1 and makedate >= to_date(?||'-' || months.nu, 'yyyy-MM-dd')) a, ");
		sql.append("(select count(*) from UserLog where OperationType = '查询' and makedate >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and makedate < to_date(?||'-' || months.nu, 'yyyy-MM-dd') + 1) b, ");
		sql.append("(select count(*) from PolicyCard where POLICYDATE >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and POLICYDATE < to_date(?||'-' || months.nu, 'yyyy-MM-dd') + 1) c, ");
		sql.append("(select count(*) from tmpfCardPolicy where makedate >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and makedate < to_date(?||'-' || months.nu, 'yyyy-MM-dd') + 1) d, ");
		sql.append("(select count(*) from UserLog where OperationType = '保全' and makedate >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and makedate < to_date(?||'-' || months.nu, 'yyyy-MM-dd') + 1) e, ");
		sql.append("(select count(*) from InsuranceWill where makedate >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and makedate < to_date(?||'-' || months.nu, 'yyyy-MM-dd')+1) f, ");
		sql.append("(select count(*) from ComplaintMessage where MESSAGETIME >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and MESSAGETIME <to_date(?||'-' || months.nu, 'yyyy-MM-dd')+1) g, ");
		sql.append("(select count(*) from ConsultationMessage where makedate >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and makedate <to_date(?||'-' || months.nu, 'yyyy-MM-dd')+1) h, ");
		sql.append("(select count(*) from UserLog where OperationType = '代理人查询' and makedate >= to_date(?||'-' || months.nu, 'yyyy-MM-dd') and makedate <to_date(?||'-' || months.nu, 'yyyy-MM-dd')+1) i  ");
		sql.append("from (select nu from months where cast(nu as int) <= cast(? as int)) months");			
		DBAccess d = new DBAccess();
		List RESULT = new ArrayList();
		RESULT = d.mulparseSQL(sql.toString(),this.strList);
		strList.clear();
		return RESULT;		
	}
	/**
	 * 业务报表查询-年报
	 * @return
	 */
	public List monthFind(){	
		StringBuffer sql = new StringBuffer("select months.nu month,");	
		sql.append("(select count(*) from Members ");
		sql.append("where makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1) and makedate >= to_date(? || '-' || months.nu,'yyyy-MM') ) a, ");
		sql.append("(select count(*) from UserLog ");
		sql.append("where OperationType = '查询' and makedate >= to_date( ? || '-' || months.nu,'yyyy-MM') and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) b, ");
		sql.append("(select count(*) from PolicyCard ");
		sql.append("where POLICYDATE >= to_date( ? || '-' || months.nu,'yyyy-MM') and POLICYDATE < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) c, ");
		sql.append("(select count(*) from tmpfCardPolicy ");
		sql.append("where makedate >= to_date( ? || '-' || months.nu,'yyyy-MM') and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) d,");
		sql.append("(select count(*) from UserLog ");
		sql.append("where OperationType = '保全' and makedate >= to_date( ? || '-' || months.nu,'yyyy-MM') and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) e,");
		sql.append("(select count(*) from InsuranceWill ");
		sql.append("where makedate >= to_date( ? || '-' || months.nu,'yyyy-MM') and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) f,");
		sql.append("(select count(*) from ComplaintMessage ");
		sql.append("where MESSAGETIME >= to_date( ? || '-' || months.nu,'yyyy-MM') and MESSAGETIME < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) g,");
		sql.append("(select count(*) from ConsultationMessage ");
		sql.append("where makedate >= to_date( ? || '-' || months.nu,'yyyy-MM') and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) h ,");
		sql.append("(select count(*) from UserLog ");
		sql.append("where OperationType = '代理人查询' and makedate >= to_date( ? || '-' || months.nu,'yyyy-MM') and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1)) i ");
		sql.append("from (select nu from months where cast(nu as int) <13) months");
		DBAccess SQL = new DBAccess();
		List result = new ArrayList();
		result = SQL.mulparseSQL(sql.toString(),this.strList);
		strList.clear();
		return result;		
	}
	/**
	 * 咨询业务分析报表查询-年报
	 * @return
	 */
	public List consultationMonthFind(){	
		StringBuffer sql = new StringBuffer("select months.nu month, ");
		sql.append("(select count(*) from Consultationmessage where  type = '购买前产品咨询' and makedate < add_months(to_date(? || '-' || months.nu, 'yyyy-MM'), 1) and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM')) a, ");
		sql.append("(select count(*) from Consultationmessage where type = '售后咨询' and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM') and makedate < add_months(to_date(? || '-' || months.nu, 'yyyy-MM'), 1)) b, ");
		sql.append("(select count(*) from Consultationmessage where type = '意见建议' and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM') and makedate < add_months(to_date(? || '-' || months.nu, 'yyyy-MM'), 1)) c, ");
		sql.append("(select count(*) from Consultationmessage where type = '网站功能咨询' and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM') and makedate < add_months(to_date(? || '-' || months.nu, 'yyyy-MM'), 1)) d ");
		sql.append("from (select nu from months where cast(nu as int) < 13) months ");		
		DBAccess SQL = new DBAccess();
		List result = new ArrayList();
		result = SQL.mulparseSQL(sql.toString(),this.strList);
		strList.clear();
		return result;		
	}
	/**
	 * 咨询业务分析报表查询-月报
	 * @return
	 */
	public List consultationDayFind(){	
		StringBuffer sql = new StringBuffer("select months.nu month,");
		sql.append("(select count(*) from Consultationmessage where  type = '购买前产品咨询' and makedate <to_date(? || '-' || months.nu, 'yyyy-MM-dd')+1 and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM-dd')) a, ");
		sql.append("(select count(*) from Consultationmessage where type = '售后咨询' and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM-dd') and makedate <to_date(? || '-' || months.nu, 'yyyy-MM-dd')+1) b, ");
		sql.append("(select count(*) from Consultationmessage where type = '意见建议' and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM-dd') and makedate < to_date(? || '-' || months.nu, 'yyyy-MM-dd')+1) c, ");
		sql.append("(select count(*) from Consultationmessage where type = '网站功能咨询' and makedate >= to_date(? || '-' || months.nu, 'yyyy-MM-dd') and makedate < to_date(? || '-' || months.nu, 'yyyy-MM-dd')+1) d ");
		sql.append("from (select nu from months where cast(nu as int) <= cast(? as int)) months ");
		DBAccess SQL = new DBAccess();
		List result = new ArrayList();
		result = SQL.mulparseSQL(sql.toString(),this.strList);
		strList.clear();
		return result;		
	}
	/**
	 * 投保意向查询-年报
	 * @return
	 */
	public List insuranceWillMonthFind(){	
		StringBuffer sql = new StringBuffer("select months.nu month, ");
		sql.append("(select count(*) from (select producttype, makedate from insurancewill ");
        sql.append(" where insurancewill.productchannel = '84')  where producttype = '90' and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1) and makedate >= to_date(? || '-' || months.nu,'yyyy-MM') ) a,");
        sql.append(" (select count(*) from (select producttype, makedate from insurancewill");
		sql.append(" where insurancewill.productchannel = '84')  where producttype = '91'and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1) and makedate >= to_date(? || '-' || months.nu,'yyyy-MM') ) b,");
		sql.append(" (select count(*) from (select producttype, makedate from insurancewill");
		sql.append(" where insurancewill.productchannel = '84')  where producttype = '92'and makedate < add_months(to_date( ? || '-' || months.nu,'yyyy-MM') ,1) and makedate >= to_date(? || '-' || months.nu,'yyyy-MM') ) c,");