mmstranssearchtotalserviceimpl.java

来自「移动彩信管理平台」· Java 代码 · 共 616 行 · 第 1/2 页

package com.my7g.zj.mobile.mms.business.service;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.jdbc.core.JdbcTemplate;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import com.my7g.zj.mobile.mms.bean.MmsSelectBean;
import com.my7g.zj.mobile.mms.business.IMMSTransSearchTotalService;
import com.my7g.zj.mobile.mms.mapping.TbCjdxSmsTier;
import com.my7g.zj.mobile.mms.sys.BasicDao;
import com.my7g.zj.mobile.mms.sys.DaoSupport;
import com.my7g.zj.mobile.mms.sys.DaoSupportHibernateImp;

public class MMSTransSearchTotalServiceImpl extends BasicDao implements
		IMMSTransSearchTotalService {
	private DaoSupport daoSupportCp;

	private final Logger log = Logger
			.getLogger(MMSTransSearchTotalServiceImpl.class);

	// 生成报表
	public void getReport(String cp, String mms_type, String time,
			OutputStream out) {
		createWorkbook(getTrans(cp, mms_type, time), out);
	}

	// 获取统计数据
	public List getTrans(String cp, String mms_type, String time) {
		System.out.println(cp + ":" + mms_type + ":" + time);
		List<HashMap> data = new ArrayList<HashMap>();
		// 根据时间取月份
		String month = "0";
		try {
			System.out.println(time);
			month = time.replaceAll("-", "").substring(0, 6);
		} catch (Exception e) {
			log.info("时间格式不是yyyy-mm-dd");
			return null;
		}
		// 根据时间取天数
		int days = 0;
		try {
			days = Integer.parseInt(time.split("-")[2]);
		} catch (Exception e) {
			log.info("时间格式不是yyyy-mm-dd");
			return null;
		}
		// 截止日期
		String deadLine = "";
		String[] date = time.split("-");
		deadLine = date[0] + "年" + date[1] + "月" + date[2] + "日";
		if ("all".equals(mms_type)) {
			/**
			 * 用户选所有栏目情况
			 */
			StringBuffer colappend = new StringBuffer();
			for (int i = 1; i <= days; i++) {
				if (i == 1) {
					colappend.append(" sum(transmit_amount"
							+ new Integer(i).toString() + ")");
				} else {
					colappend.append("+sum(transmit_amount"
							+ new Integer(i).toString() + ")");
				}
			}
			// 前几个月的首层转发量
			StringBuffer sql_firstNum_p = new StringBuffer(
					"select c.sort_name,sum(a.transmit_total) first_count");
			sql_firstNum_p.append(" from ");
			sql_firstNum_p.append(" tb_mms_type c left join tb_mms_seed b ");
			sql_firstNum_p.append(" on c.sort_index=b.type_index  ");
			sql_firstNum_p.append(" left join dbo.tb_cjdx_sms_tier a ");
			sql_firstNum_p.append(" on a.sms_id=b.mms_id and tier_flag=1 ");
			sql_firstNum_p.append(" group by c.sort_name ");
			sql_firstNum_p.append(" order by c.sort_name ");
			// 前几个月的第二层转发量
			StringBuffer sql_secondNum_p = new StringBuffer(
					"select c.sort_name,sum(a.transmit_total) second_count ");
			sql_secondNum_p.append(" from ");
			sql_secondNum_p.append(" tb_mms_type c left join tb_mms_seed b  ");
			sql_secondNum_p.append(" on c.sort_index=b.type_index  ");
			sql_secondNum_p.append(" left join dbo.tb_cjdx_sms_tier a ");
			sql_secondNum_p.append(" on a.sms_id=b.mms_id and tier_flag=2 ");
			sql_secondNum_p.append(" group by c.sort_name ");
			sql_secondNum_p.append(" order by c.sort_name ");
			// 前几个月的第三层转发量
			StringBuffer sql_thirdNum_p = new StringBuffer(
					"select c.sort_name,sum(a.transmit_total) third_count ");
			sql_thirdNum_p.append(" from ");
			sql_thirdNum_p.append(" tb_mms_type c left join tb_mms_seed b ");
			sql_thirdNum_p.append(" on c.sort_index=b.type_index ");
			sql_thirdNum_p.append(" left join dbo.tb_cjdx_sms_tier a ");
			sql_thirdNum_p.append(" on a.sms_id=b.mms_id and tier_flag=3 ");
			sql_thirdNum_p.append(" group by c.sort_name ");
			sql_thirdNum_p.append(" order by c.sort_name ");
			// 当月的首层转发量
			StringBuffer sql_firstNum = new StringBuffer("select c.sort_name,");
			sql_firstNum.append(colappend.toString());
			sql_firstNum.append(" first_count ");
			sql_firstNum.append(" from ");
			sql_firstNum.append(" tb_mms_type c left join tb_mms_seed b ");
			sql_firstNum.append(" on c.sort_index=b.type_index ");
			sql_firstNum.append(" left join dbo.tb_cjdx_sms_tier a ");
			sql_firstNum.append(" on a.sms_id=b.mms_id and tier_flag=1 ");
			sql_firstNum.append(" group by c.sort_name ");
			sql_firstNum.append(" order by c.sort_name ");
			// 当月的第二层转发量
			StringBuffer sql_secondNum = new StringBuffer("select c.sort_name,");
			sql_secondNum.append(colappend.toString());
			sql_secondNum.append(" second_count ");
			sql_secondNum.append(" from ");
			sql_secondNum.append(" tb_mms_type c left join tb_mms_seed b ");
			sql_secondNum.append(" on c.sort_index=b.type_index ");
			sql_secondNum.append(" left join dbo.tb_cjdx_sms_tier a ");
			sql_secondNum.append(" on a.sms_id=b.mms_id and tier_flag=2 ");
			sql_secondNum.append(" group by c.sort_name ");
			sql_secondNum.append(" order by c.sort_name ");
			// 当月的第三层转发量
			StringBuffer sql_thirdNum = new StringBuffer("select c.sort_name,");
			sql_thirdNum.append(colappend.toString());
			sql_thirdNum.append(" third_count ");
			sql_thirdNum.append(" from ");
			sql_thirdNum.append(" tb_mms_type c left join tb_mms_seed b ");
			sql_thirdNum.append(" on c.sort_index=b.type_index ");
			sql_thirdNum.append(" left join dbo.tb_cjdx_sms_tier a ");
			sql_thirdNum.append(" on a.sms_id=b.mms_id and tier_flag=3 ");
			sql_thirdNum.append(" group by c.sort_name ");
			sql_thirdNum.append(" order by c.sort_name ");
			// 访问数据库
			List firstNum_p = jdbcTemplate.queryForList(sql_firstNum_p
					.toString());
			List secondNum_p = jdbcTemplate.queryForList(sql_secondNum_p
					.toString());
			List thirdNum_p = jdbcTemplate.queryForList(sql_thirdNum_p
					.toString());
			// List totalNum_p = jdbcTemplate.queryForList(sql_totalNum_p
			// .toString());
			System.out.println(sql_firstNum.toString());
			List firstNum = jdbcTemplate.queryForList(sql_firstNum.toString());
			List secondNum = jdbcTemplate
					.queryForList(sql_secondNum.toString());
			List thirdNum = jdbcTemplate.queryForList(sql_thirdNum.toString());
			// List totalNum =
			// jdbcTemplate.queryForList(sql_totalNum.toString());

			// 数据组装
			ArrayList<TransTotalRow> dataList = new ArrayList<TransTotalRow>();

			// 前几个月的首层转发量
			for (int i = 0; i < firstNum_p.size(); i++) {
				TransTotalRow row = new TransTotalRow();
				String name = getStringFromListMap(firstNum_p, i, "sort_name",
						"");
				row.setLanmu(name);
				row.setFirstnum(Integer.parseInt(getStringFromListMap(
						firstNum_p, i, "first_count", "0")));
				dataList.add(i, row);
			}
			// 前几个月的第二层转发量
			for (int i = 0; i < secondNum_p.size(); i++) {
				TransTotalRow row = dataList.get(i);
				row.setSecondnum(Integer.parseInt(getStringFromListMap(
						secondNum_p, i, "second_count", "0")));
			}
			// 前几个月的第三层转发量
			for (int i = 0; i < thirdNum_p.size(); i++) {
				TransTotalRow row = dataList.get(i);
				row.setSecondnum(Integer.parseInt(getStringFromListMap(
						thirdNum_p, i, "third_count", "0")));
			}

			// 当月至截止日首层转发量
			for (int i = 0; i < firstNum.size(); i++) {
				TransTotalRow row = dataList.get(i);
				row.setSecondnum(Integer.parseInt(getStringFromListMap(
						firstNum, i, "first_count", "0")));
			}
			// 当月至截止日二层转发量
			for (int i = 0; i < secondNum.size(); i++) {
				TransTotalRow row = dataList.get(i);
				row.setSecondnum(Integer.parseInt(getStringFromListMap(
						secondNum, i, "second_count", "0")));
			}
			// 当月至截止日三层转发量
			for (int i = 0; i < thirdNum.size(); i++) {
				TransTotalRow row = dataList.get(i);
				row.setSecondnum(Integer.parseInt(getStringFromListMap(
						thirdNum, i, "third_count", "0")));
			}
			// 页面栏目行数据封装
			int totalCal = 0;// 总转发量
			int firstCal = 0;// 首层转发
			int secondCal = 0;// 第二层转发
			int thirdCal = 0;// 第三层转发
			for (int i = 0; i < dataList.size(); i++) {
				TransTotalRow row = dataList.get(i);
				String lanmu = row.getLanmu();
				int i_total = row.getTotalnum();
				int i_first = row.getFirstnum();
				int i_second = row.getSecondnum();
				int i_third = row.getThirdnum();
				totalCal += i_total;
				firstCal += i_first;
				secondCal += i_second;
				thirdCal += i_third;
				String s_total = row.getTotalnum().toString();
				String s_first = row.getFirstnum().toString();
				String s_second = row.getSecondnum().toString();
				String s_third = row.getThirdnum().toString();
				HashMap<String, String> dataMap = new HashMap<String, String>();
				dataMap.put("lanmu", lanmu);
				dataMap.put("deadLine", deadLine);
				dataMap.put("total", new Integer(s_total).toString());
				dataMap.put("first", new Integer(s_first).toString());
				dataMap.put("second", new Integer(s_second).toString());
				dataMap.put("third", new Integer(s_third).toString());
				data.add(dataMap);
			}
			// 总计行数据封装
			HashMap<String, String> dataMap = new HashMap<String, String>();
			dataMap.put("lanmu", "总数");
			dataMap.put("deadLine", "");
			dataMap.put("total", new Integer(totalCal).toString());
			dataMap.put("first", new Integer(firstCal).toString());
			dataMap.put("second", new Integer(secondCal).toString());
			dataMap.put("third", new Integer(thirdCal).toString());
			data.add(dataMap);
		} else {
			/**
			 * 用户选单个栏目情况
			 */
			Object[] objs = { mms_type };
			String mmsIdHql = "select t.id.mmsId from TbMmsSeed t where t.id.typeIndex = ?";
			// 前几个月转发量情况
			String hql_firstNum_p = "select t.transmitTotal from TbCjdxSmsTier t where t.currentMonth<"
					+ month
					+ " and t.tierFlag=1 and t.smsId in ("
					+ mmsIdHql
					+ ")";
			String hql_secondNum_p = "select t.transmitTotal from TbCjdxSmsTier t where t.currentMonth<"
					+ month
					+ " and t.tierFlag=2 and t.smsId in ("
					+ mmsIdHql
					+ ")";
			String hql_thirdNum_p = "select t.transmitTotal from TbCjdxSmsTier t where t.currentMonth<"
					+ month
					+ " and t.tierFlag=3 and t.smsId in ("
					+ mmsIdHql
					+ ")";

			List firstNumList_p = daoSupport.find(hql_firstNum_p, objs);
			List secondNumList_p = daoSupport.find(hql_secondNum_p, objs);
			List thirdNumList_p = daoSupport.find(hql_thirdNum_p, objs);

			int firstNum_p = 0;
			int secondNum_p = 0;
			int thirdNum_p = 0;
			if (firstNumList_p != null && firstNumList_p.size() > 0) {
				firstNum_p = Integer.parseInt(getStringFromListMap(
						firstNumList_p, 0, "transmitTotal", "0"));
			}
			if (secondNumList_p != null && secondNumList_p.size() > 0) {
				secondNum_p = Integer.parseInt(getStringFromListMap(
						secondNumList_p, 0, "transmitTotal", "0"));
			}
			if (thirdNumList_p != null && thirdNumList_p.size() > 0) {
				thirdNum_p = Integer.parseInt(getStringFromListMap(
						thirdNumList_p, 0, "transmitTotal", "0"));
			}

			// 当月到截止日为止转发量情况
			StringBuilder sb = new StringBuilder();
			String mmsIdSql = "select mms_id from tb_mms_seed where type_index = '"
					+ mms_type + "'";
			sb.append("select ");
			for (int i = 1; i <= days; i++) {
				if (i != days) {
					sb.append(" t.transmit_amount" + new Integer(i).toString()
							+ "+");
				} else {
					sb.append("t.transmit_amount" + new Integer(i).toString());
				}
			}
			sb.append(" transmit_amount");
			String sql_firstNum = sb.toString()
					+ " from tb_cjdx_sms_tier t where t.current_month=" + month
					+ " and t.tier_flag=1 and t.sms_id in (" + mmsIdSql + ")";
			String sql_secondNum = sb.toString()
					+ " from tb_cjdx_sms_tier t where t.current_month=" + month
					+ " and t.tier_flag=2 and t.sms_id in (" + mmsIdSql + ")";
			String sql_thirdNum = sb.toString()
					+ " from tb_cjdx_sms_tier t where t.current_month=" + month
					+ " and t.tier_flag=3 and t.sms_id in (" + mmsIdSql + ")";