mmstranssearchserviceimpl.java

来自「移动彩信管理平台」· Java 代码 · 共 245 行

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

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowCallbackHandler;

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.IMMSTransSearchService;
import com.my7g.zj.mobile.mms.sys.BasicDao;
import com.my7g.zj.mobile.mms.sys.DaoSupport;

public class MMSTransSearchServiceImpl extends BasicDao implements
		IMMSTransSearchService {
	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) {
		List<Map> data;
		// 根据时间取月份
		String month = "0";
		try {
			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;
		}
		StringBuilder sb = new StringBuilder();
		for (int i = 1; i <= days; i++) {
			if (i != days) {
				sb.append(" transmit_amount" + new Integer(days).toString()
						+ "+");
			} else {
				sb.append("transmit_amount" + new Integer(days).toString());
			}
		}

		// 如果选择了拦目，附加的sql
		String where = " and m.sort_id = '"+mms_type+"'";

		// 查询转发量前100的彩信记录
		StringBuilder sql_top100 = new StringBuilder();
		sql_top100
				.append(" select top 100 mms_id,[user_name],sort_name,mms_title,sum(total) total ");
		sql_top100.append(" from( ");
		sql_top100
				.append(" select s.mms_id,c.[user_name],m.sort_name,s.mms_title,sum(transmit_total) total ");
		sql_top100.append("  from tb_mms_seed s ");
		sql_top100
		.append("  inner join tb_mms_type m on s.type_index=m.sort_index ");
		if (!"all".equals(mms_type))
			sql_top100.append(where);
		sql_top100
				.append("  left join tb_cjdx_sms_transmit t on s.mms_id = t.sms_id and current_month<"+month+" ");
		sql_top100
				.append("  left join supermms_cp..tb_mms_cp_provider c on c.type_index = s.type_index ");
		sql_top100
				.append("  group by s.mms_id,c.[user_name],m.sort_name,s.mms_title  ");
		sql_top100.append(" union ");
		sql_top100
				.append(" select s.mms_id,c.[user_name],m.sort_name,s.mms_title,sum(");
		sql_top100.append(sb.toString());
		sql_top100.append(" ) total ");
		sql_top100.append("  from tb_mms_seed s  ");
		sql_top100
		.append("  inner join tb_mms_type m on s.type_index=m.sort_index  ");
		if (!"all".equals(mms_type))
			sql_top100.append(where);
		sql_top100
				.append("  left join tb_cjdx_sms_transmit t on s.mms_id = t.sms_id and current_month="+month+" ");
		sql_top100
				.append("  left join supermms_cp..tb_mms_cp_provider c on c.type_index = s.type_index ");
		sql_top100
				.append("  group by s.mms_id,c.[user_name],m.sort_name,s.mms_title  ");
		sql_top100.append(" ) ");
		sql_top100.append(" kkk ");
		sql_top100.append(" group by mms_id,[user_name],sort_name,mms_title ");
		sql_top100.append(" order by total desc,mms_id asc ");

		data = this.jdbcTemplate.queryForList(sql_top100.toString());
		
		// 数据加上排名列
		for (int i = 0; i < data.size(); i++) {
			Map transRow = (Map) data.get(i);
			transRow.put("place", new Integer(i + 1));
			//total
			Object total = transRow.get("total");
			if (total==null) {
				transRow.put("total","0");
			}
		}
		return data;
	}

	public List getTypeList(String userid, String user_group) {
		List<MmsSelectBean> typeList = new ArrayList<MmsSelectBean>();
		List list;
		// 登陆用户为局方
		
		if (!"4".equals(user_group)) {
			// 取栏目对应的id,名称列表
			list = this.daoSupport
					.find("select t.sortId,t.sortName from TbMmsType t where t.sortDeep = '3' order by t.sortOrder");
		} else {
			// 登陆用户为cp
			Object[] objs = { userid };
			// 取这个cp对应的栏目id
			List typeIdList = this.daoSupportCp
					.find(
							"select t.typeIndex from TbMmsCpProvider t where t.userId = ?",
							objs);
			StringBuffer sb = new StringBuffer();
			for (int i = 0; i < typeIdList.size(); i++) {
				sb.append(typeIdList.get(i).toString());
				if (i != typeIdList.size() - 1)
					sb.append(",");
			}
			// 取栏目对应的名称
			list = this.daoSupport
					.find("select t.sortId,t.sortName from TbMmsType t where t.sortIndex in ("
							+ sb.toString() + ")");

		}
		if ("4".equals(user_group)) {
			MmsSelectBean selectBean = new MmsSelectBean("all", "所有栏目");
			typeList.add(selectBean);
		}
		if (list != null) {
			for (int i = 0; i < list.size(); i++) {
				Object[] objs = (Object[]) list.get(i);
				String code = objs[0] != null ? objs[0].toString() : "";
				String name = objs[1] != null ? objs[1].toString() : "";
				MmsSelectBean selectBean = new MmsSelectBean(code, name);
				typeList.add(selectBean);
			}
		}
		return typeList;
	}

	// 生成报表
	private void createWorkbook(List list, OutputStream out) {
		WritableWorkbook wwb = null;
		try {
			wwb = Workbook.createWorkbook(out);
			WritableSheet ws = wwb.createSheet("转发排行榜", 0);

			// 字段名
			Label lb_1 = new Label(0, 0, "排名");
			ws.addCell(lb_1);
			Label lb_2 = new Label(1, 0, "ID");
			ws.addCell(lb_2);
			Label lb_3 = new Label(2, 0, "所属CP");
			ws.addCell(lb_3);
			Label lb_4 = new Label(3, 0, "所属栏目");
			ws.addCell(lb_4);
			Label lb_5 = new Label(4, 0, "标题");
			ws.addCell(lb_5);
			Label lb_6 = new Label(5, 0, "转发量");
			ws.addCell(lb_6);

			// 明细数据
			for (int i = 0; i < list.size(); i++) {
				Map objs = (Map) list.get(i);
				if (objs == null)
					continue;
				// 排名
				String place = objs.get("place")!=null?objs.get("place").toString():"0";
				Label lb_place = new Label(0, i + 1, place);
				ws.addCell(lb_place);
				// ID
				String mms_id = objs.get("mms_id")!=null?objs.get("mms_id").toString():"0";
				Label lb_mms_id = new Label(1, i + 1, mms_id);
				ws.addCell(lb_mms_id);
				// 所属CP
				String user_name = objs.get("user_name")!=null?objs.get("user_name").toString():"";
				Label lb_user_name = new Label(2, i + 1, user_name);
				ws.addCell(lb_user_name);				
				// 所属栏目
				String lanmu = objs.get("lanmu")!=null?objs.get("lanmu").toString():"";
				Label lb_lanmu = new Label(3, i + 1, lanmu);
				ws.addCell(lb_lanmu);
				// 标题
				String mms_title = objs.get("mms_title")!=null?objs.get("mms_title").toString():"";
				Label lb_mms_title = new Label(4, i + 1, mms_title);
				ws.addCell(lb_mms_title);
				// 转发量
				String total = objs.get("total")!=null?objs.get("total").toString():"0";
				Label lb_total = new Label(5, i + 1, total);
				ws.addCell(lb_total);
			}
			// 保存
			wwb.write();
		} catch (Exception e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} finally {
			if (wwb != null) {
				try {
					// 关闭
					wwb.close();
				} catch (WriteException e) {
					// TODO 自动生成 catch 块
					e.printStackTrace();
				} catch (IOException e) {
					// TODO 自动生成 catch 块
					e.printStackTrace();
				}
			}
		}
	}

	public void setDaoSupportCp(DaoSupport daoSupportCp) {
		this.daoSupportCp = daoSupportCp;
	}
}