reportsql.java

这个是完整的wap项目的源码 开发语言 Java 系统架构 Struts + hibernate + spring 数据库 Mysql5.0 应用服务器Tomcat5.0 开发工具 MyEc

package com.longtime.wap.module.cp.common;

/**
 * 报表统计SQL
 * 
 * @author liuzb
 * @date Nov 15, 2007
 */
public class ReportSQL {

	/**
	 * 取得按单位统计的SQL
	 * 
	 * @param startDate
	 *            开始日期
	 * @param toDate
	 *            结束日期
	 * @return 统计语句
	 */
	public static String getReportSqlByCp(String startDate, String toDate) {
		String date1 = startDate;
		String date2 = toDate;
		if (date1 == null || date1.equals("")) {
			date1 = "1900-01-01";
		}
		if (date2 == null || date2.equals("")) {
			date2 = "2900-01-01";
		}

		StringBuffer sql = new StringBuffer();
		sql.append("select t1.cp_id,company_name,");
		sql.append("t1.count1,t2.count2,t3.count3,t4.count4 ");
		sql.append("from ( ");
		sql.append("	select cp.cp_id,company_name,");
		sql.append("count(business.business_id) as count1 ");
		sql.append("	from cp left join business ");
		sql.append("	on cp.cp_id=business.cp_id ");
		sql.append("	group by cp.cp_id,company_name ");
		sql.append(") t1, ");
		sql.append("(	select a.cp_id,");
		sql.append("count(information.information_id) as count2 ");
		sql.append("	from ( ");
		sql.append("		select cp.cp_id,business.business_id ");
		sql.append("		from cp left join business ");
		sql.append("		on cp.cp_id=business.cp_id ");
		sql.append("	) a left join information ");
		sql.append("	on a.business_id=information.business_id ");
		sql.append("	and information.reg_date between TO_DATE('");
		sql.append(date1).append("','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	and TO_DATE('").append(date2).append(
				"','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	group by a.cp_id ");
		sql.append(") t2, ");
		sql.append("(	select a.cp_id,");
		sql.append("count(information.information_id) as count3 ");
		sql.append("	from ( ");
		sql.append("		select cp.cp_id,business.business_id ");
		sql.append("		from cp left join business ");
		sql.append("		on cp.cp_id=business.cp_id ");
		sql.append("	) a left join information ");
		sql.append("	on a.business_id=information.business_id ");
		sql.append("	and information.is_pub=1 ");
		sql.append("	and information.reg_date between TO_DATE('");
		sql.append(date1).append("','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	and TO_DATE('").append(date2).append(
				"','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	group by a.cp_id ");
		sql.append(") t3, ");
		sql.append("(	select a.cp_id,");
		sql.append("count(information.information_id) as count4 ");
		sql.append("	from ( ");
		sql.append("		select cp.cp_id,business.business_id ");
		sql.append("		from cp left join business ");
		sql.append("		on cp.cp_id=business.cp_id ");
		sql.append("	) a left join information ");
		sql.append("	on a.business_id=information.business_id ");
		sql.append("	and information.is_pub=0 ");
		sql.append("	and information.reg_date between TO_DATE('");
		sql.append(date1).append("','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	and TO_DATE('").append(date2).append(
				"','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	group by a.cp_id ");
		sql.append(") t4 ");
		sql.append("where t1.cp_id=t2.cp_id ");
		sql.append("and t1.cp_id=t3.cp_id ");
		sql.append("and t1.cp_id=t4.cp_id ");
		return sql.toString();
	}

	/**
	 * 取得按业务统计的SQL
	 * 
	 * @param startDate
	 *            开始日期
	 * @param toDate
	 *            结束日期
	 * @return 统计语句
	 */
	public static String getReportSqlByService(String startDate, 
			String toDate) {
		String date1 = startDate;
		String date2 = toDate;
		if (date1 == null || date1.equals("")) {
			date1 = "1900-01-01";
		}
		if (date2 == null || date2.equals("")) {
			date2 = "2900-01-01";
		}

		StringBuffer sql = new StringBuffer();
		sql.append("select t1.category,t1.count1,t2.count2,t3.count3 ");
		sql.append("from ( ");
		sql.append("	select business.category,");
		sql.append("count(information.information_id) as count1 ");
		sql.append("	from business left join information ");
		sql.append("	on business.business_id=information.business_id ");
		sql.append("	and information.is_pub=1 ");
		sql.append("	and information.reg_date between TO_DATE('");
		sql.append(date1).append("','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	and TO_DATE('").append(date2).append(
				"','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	group by business.category ");
		sql.append(") t1, ");
		sql.append("(	select business.category,");
		sql.append("count(information.information_id) as count2 ");
		sql.append("	from business left join information ");
		sql.append("	on business.business_id=information.business_id ");
		sql.append("	and information.is_pub=0 ");
		sql.append("	and information.reg_date between TO_DATE('");
		sql.append(date1).append("','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	and TO_DATE('").append(date2).append(
				"','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	group by business.category ");
		sql.append(") t2, ");
		sql.append("(	select business.category,");
		sql.append("decode(sum(information.pay_count),null,0,");
		sql.append("sum(information.pay_count)) as count3 ");
		sql.append("	from business left join information ");
		sql.append("	on business.business_id=information.business_id ");
		sql.append("	and information.reg_date between TO_DATE('");
		sql.append(date1).append("','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	and TO_DATE('").append(date2).append(
				"','YYYY-MM-DD   HH24:MI:SS') ");
		sql.append("	group by business.category ");
		sql.append(") t3 ");
		sql.append("where t1.category=t2.category ");
		sql.append("and t1.category=t3.category ");
		sql.append("order by category ");

		return sql.toString();
	}

}