consultationreport.java

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

/**
 * 咨询留言报表
 */
package com.NCL.excel;
import java.util.ArrayList;
import java.util.List;

import com.sinosoft.common.*;

public class ConsultationReport {

	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 CONSULTATIONMESSAGE where dealflag = 3"
				+" and dealdate >= to_date('"+year+"-01', 'yyyy-MM')" 
				+" and dealdate < to_date('"+yearAddOne+"-01', 'yyyy-MM')"
				+" )"
				+" /(select count(dealflag) from CONSULTATIONMESSAGE where dealflag = 3" 
				+" 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 CONSULTATIONMESSAGE t1" 
				+"                       where t1.MAKEDATE >= to_date('"+year+"' || m.nu, 'yyyy-MM')" 
				+"                       and t1.MAKEDATE <add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)),0) as CONSULNUM," 
				//		--已受理
				+"      nvl(" 
				+"           (" 
				+"           select Count(t1.DEALFLAG) " 
				+"                       from CONSULTATIONMESSAGE t1" 
				+"                       where t1.DEALFLAG != 0 " 
				+"                       and t1.CCACCESSDATE >= to_date('"+year+"' || m.nu, 'yyyy-MM')" 
				+"                       and t1.CCACCESSDATE <add_months(to_date('"+year+"' || m.nu, 'yyyy-MM'), 1)),0) as DEAL," 
				//		--已完成
				+"      nvl(" 
				+"           (" 
				+"           select Count(t1.DEALFLAG) " 
				+"                       from CONSULTATIONMESSAGE t1" 
				+"                       where t1.DEALFLAG = 3 " 
				+"                       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 FINISH," 
				
				//     --1天内处理
				+"     nvl(" 
				+"             (" 
				+"              select Count(t3.DEALFLAG) " 
				+"                     from CONSULTATIONMESSAGE t3" 
				+"                    where t3.DEALFLAG = 3 " 
				+"                     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 CONSULTATIONMESSAGE t4" 
				+"                      where t4.DEALFLAG = 3 " 
				+"                      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 CONSULTATIONMESSAGE t5" 
				+"                       where t5.DEALFLAG = 3 " 
				+"                      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 CONSULTATIONMESSAGE t6" 
				+"                     where t6.DEALFLAG = 3 " 
				+"                     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 CONSULTATIONMESSAGE where dealflag = 3"
				+" 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 CONSULTATIONMESSAGE where dealflag = 3" 
				+" 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());
	}
	public static void main(String args[]){
	}
}