boradcastserviceimpl.java

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

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

import java.io.IOException;
import java.io.OutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

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

import org.hibernate.Query;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlRowSetResultSetExtractor;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.my7g.zj.mobile.mms.bean.BoradInfomation;
import com.my7g.zj.mobile.mms.bean.BoradVolumeBean;
import com.my7g.zj.mobile.mms.business.IBoradCastService;
import com.my7g.zj.mobile.mms.mapping.TbMmsCpProvider;
import com.my7g.zj.mobile.mms.mapping.TbMmsType;
import com.my7g.zj.mobile.mms.sys.BasicDao;
import com.my7g.zj.mobile.mms.sys.DaoSupport;
import com.my7g.zj.mobile.mms.util.DividePageBean;
import com.my7g.zj.mobile.mms.util.PageBean;

public class BoradCastServiceImpl extends BasicDao implements IBoradCastService {
	public DividePageBean platDividePageBean;
	private DaoSupport daoSupportcp;
	private JdbcTemplate jdbcTemplatecp;

	public void setPlatDividePageBean(DividePageBean platDividePageBean) {
		this.platDividePageBean = platDividePageBean;
	}

	public void setDaoSupportcp(DaoSupport daoSupportcp) {
		this.daoSupportcp = daoSupportcp;
	}

	public void setJdbcTemplatecp(JdbcTemplate jdbcTemplatecp) {
		this.jdbcTemplatecp = jdbcTemplatecp;
	}

	public TbMmsType findMessageType(String sortIndex) {
		String hql = "from TbMmsType t where t.sortIndex=:id and t.sortDeep=3";
		Query query = this.getSession().createQuery(hql);
		query.setString("id", sortIndex);
		return (TbMmsType) query.uniqueResult();	
	}

	public List findMessageType() {
		String hql = "from TbMmsType t where t.sortDeep=3";
		return this.find(hql);
	}

	public List<BoradVolumeBean> findAllVolume(String sortIndex, String month) {
		List<BoradVolumeBean> allBoradvolume = new ArrayList<BoradVolumeBean>();
		if (sortIndex.equals("all")) {
			List allType = findMessageType();// 获取所有类型的列表
			for (int i = 0; i < allType.size(); i++) {
				TbMmsType type = (TbMmsType) allType.get(i);
				BoradVolumeBean boardBean = findTotalMessage(type
						.getSortIndex(), month);
				allBoradvolume.add(boardBean);
			}
			return allBoradvolume;
		} else
			allBoradvolume.add(findTotalMessage(sortIndex, month));
		return allBoradvolume;
	}

	public BoradVolumeBean findTotalMessage(String sortIndex, String month) {
		// 包含栏目信息的bean
		BoradVolumeBean borad = new BoradVolumeBean();
		// 开始日期
		String beginMonth = month.replaceAll("-", "").substring(0, 4) + "01";
		// 结束日期
		String endMonth = month.replaceAll("-", "").substring(0, 6);
		String sql, sqll;
		sql = "select sum(appoint_total) as total from tb_cjdx_sms_appoint where  current_month between '"
				+ beginMonth
				+ "' and '"
				+ endMonth
				+ "'"
				+ " and sms_id in "
				+ "(select mms_id from tb_mms_seed where type_index='"
				+ sortIndex + "')";
		sqll = "select * from tb_cjdx_sms_appoint where current_month='"
				+ endMonth + "'" + " and sms_id in "
				+ "(select mms_id from tb_mms_seed where type_index='"
				+ sortIndex + "')";
		int monthNumber = 0, dateNumber = 0;
		String userName = null;
		// 存放当前日期的记录
		SqlRowSet rsdate = this.getJdbcTemplate().queryForRowSet(sqll);
		// 存放到当前日前以前的总记录
		SqlRowSet rsmonth = this.getJdbcTemplate().queryForRowSet(sql);
		while (rsmonth.next())
			monthNumber = rsmonth.getInt(1);// 月点播量总和
		while (rsdate.next()) {
			if (rsdate.getInt(34) > 0)
				continue;
			for (int i = 3; i <= 33; i++)
				dateNumber = +(Integer) rsdate.getInt(i);
		}

		borad.setTotal(monthNumber + dateNumber);
		borad.setSortName(findMessageType(sortIndex).getSortName());
		borad.setMonth(month);
		return borad;
		// List boradList = new ArrayList();
		/*
		 * if (sortIndex == null || sortIndex.equals("")) { sql = "select
		 * sum(appoint_total) as total,b.sort_name name from tb_cjdx_sms_appoint
		 * as a, tb_mms_type as b," + "tb_mms_seed as t" + " where " +
		 * "b.sort_index=t.type_index and " + "a.sms_id=t.mms_id and
		 * a.current_month between '" + beginMonth + "' and '" + endMonth + "'"
		 * +"group by b.sort_name"; sqll = "select * from tb_cjdx_sms_appoint as
		 * a, tb_mms_seed as t" + " where a.sms_id=t.mms_id and
		 * a.current_month='" + endMonth + "'"; } else {
		 */
	}

	public TbMmsType findCpType(int id) {
		List list = null;
		String hql = "from TbMmsCpProvider t where t.userId=:_id";
		Query query = daoSupportcp.getaSession().createQuery(hql);
		query.setInteger("_id", id);
		TbMmsCpProvider provider = (TbMmsCpProvider) query.uniqueResult();
		if (provider != null)
			return this.findMessageType(provider.getTypeIndex());
		else
			return null;
	}

	public List<Integer> findAllid(String sortIndex) {
		String sql;
		List<Integer> list = new ArrayList<Integer>();
		if (sortIndex == null || sortIndex.equals("")) {
			sql = "select mms_id from tb_mms_seed where type_index in "
					+ "(select sort_index from tb_mms_type where sort_deep=3)";
		} else {
			sql = "select mms_id from tb_mms_seed where type_index='"
					+ sortIndex + "'";
		}

		SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql);
		while (rs.next()) {
			list.add(rs.getInt(1));
		}
		return list;
	}

	public List findAssociationContent(int id) {
		List list = new ArrayList();
		String sql = "select mms_id,mms_title,user_name from tb_mms_seed s inner join "
				+ "supermms_cp"
				+ "."
				+ "dbo"
				+ "."
				+ "tb_mms_cp_provider "
				+ "p on s.provide_id=p.user_id " + "and s.mms_id=" + id;
		SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
		if (rs.next()) {
			list.add(rs.getInt(1));
			list.add(rs.getString(2));
			list.add(rs.getString(3));
		}
		return list;
	}

	public String findSortName(int id) {
		String sortName = "";
		String sql = "select sort_name from tb_mms_type t inner join tb_mms_seed s"
				+ " on t.sort_index=s.type_index"
				+ " and s.mms_id='"
				+ id
				+ "'";
		SqlRowSet rs = this.getJdbcTemplate().queryForRowSet(sql);
		while (rs.next()) {
			sortName = rs.getString("sort_name");
		}
		return sortName;
	}

	public int findTotalNumber(String month, int id) {

		// 开始日期
		String beginMonth = month.replaceAll("-", "").substring(0, 4) + "01";
		// 结束日期
		String endMonth = month.replaceAll("-", "").substring(0, 6);
		String sql = "select sum(appoint_total) as total from tb_cjdx_sms_appoint where  current_month between '"
				+ beginMonth
				+ "' and '"
				+ endMonth
				+ "'"
				+ " and sms_id='"
				+ id + "'";
		String sqll = "select * from tb_cjdx_sms_appoint where  current_month between '"
				+ beginMonth
				+ "' and '"
				+ endMonth
				+ "'"
				+ " and sms_id='"
				+ id + "'";
		int monthNumber = 0, dateNumber = 0;
		SqlRowSet rsmonth = this.getJdbcTemplate().queryForRowSet(sql);
		SqlRowSet rsdate = this.getJdbcTemplate().queryForRowSet(sqll);
		if (rsmonth.next()) {
			monthNumber = rsmonth.getInt(1);// 月点播量总和,即使没有对应的记录，也会返回零
		}

		while (rsdate.next()) {
			if (rsdate.getInt(34) > 0)
				continue;
			for (int i = 3; i <= 33; i++)
				dateNumber = +(Integer) rsdate.getInt(i);
		}
		if (monthNumber == 0)
			return -1;
		else
			return monthNumber + dateNumber;
	}

	public Set findallInfomation(String sortName, String month) {
		TreeSet set = new TreeSet(new Comparator() {
			public int compare(Object o1, Object o2) {
				BoradInfomation inform1 = (BoradInfomation) o1;
				BoradInfomation inform2 = (BoradInfomation) o2;
				if (inform1.getTotal() < inform2.getTotal()) {
					return 1;
				}

				if (inform1.getTotal() == inform2.getTotal()) {
					return 0;
				}
				if (inform1.getTotal() > inform2.getTotal()) {
					return -1;
				}
				return 0;
			}

		});

		List<Integer> ids = findAllid(sortName);
		for (int i = 0; i < ids.size(); i++) {
			BoradInfomation inform = new BoradInfomation();
			inform.setSortName(findSortName(ids.get(i)));
			int total = findTotalNumber(month, ids.get(i));
			if (total == -1)
				continue;
			inform.setTotal(total);
			System.out.println(ids.get(i) + "*******");
			List content = findAssociationContent(ids.get(i));
			if (content.size() != 0) {
				System.out.println(content.get(0));
				inform.setId((Integer) content.get(0));
				inform.setCpName((String) content.get(1));
				inform.setTitle((String) content.get(2));
				inform.setOrdeyId(i + 1);
			}
			set.add(inform);
		}
		return set;
	}

	public PageBean findPageList(Integer page, int rowPerPage, String href,
			List list) {
		List pageList = new ArrayList();
		PageBean bean = null;
		platDividePageBean.setwaitPage(String.valueOf(page), rowPerPage);
		platDividePageBean.setPageInfo(list.size());
		String pageHref = platDividePageBean.gethrefPage(href, "pagesel",
				"page");

		int currentNumber = (page - 1) * rowPerPage;
		int lastNumber = currentNumber + rowPerPage;
		for (int i = currentNumber; i < lastNumber; i++) {
			if (i < list.size()) {
				pageList.add(list.get(i));
			}
		}
		bean = new PageBean(pageHref, pageList);
		return bean;
	}
    public void getInformationReport(String sortIndex, String month, OutputStream out){
    	Set set = findallInfomation(sortIndex,month);
    	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);
			Label lb_7 = new Label(6, 0, "短信点播");
			ws.addCell(lb_7);
			Label lb_8 = new Label(7, 0, "网站点播");
			ws.addCell(lb_8);
			// 明细数据
			Iterator it = set.iterator();
			int i=0;
			while(it.hasNext()) {
				BoradInfomation inform = (BoradInfomation)it.next();
				// 排名
				if (inform == null){
					continue;
				}
					
				int orderId = inform.getOrdeyId();
				Label pid = new Label(0, i + 1, String.valueOf(orderId));
				ws.addCell(pid);
				// 短信ID
				int id = inform.getId();
				Label monthLabel = new Label(1, i + 1, String.valueOf(id));
				ws.addCell(monthLabel);
				// 所属CP
				String cpName = inform.getCpName();
				Label cp = new Label(2, i + 1, cpName);
				ws.addCell(cp);
				//所属栏目
				String sortName = inform.getSortName();
				Label sort = new Label(3,i+1,sortName);
				ws.addCell(sort);
				//标题
				String title = inform.getTitle();
				Label titlecontent = new Label(4,i+1,title);
				ws.addCell(titlecontent);
				//总点播量
				int totalNumber = inform.getTotal();
				Label total = new Label(5, i + 1, String
						.valueOf(totalNumber));
				ws.addCell(total);
				//短信点播量
				int informaNumber = inform.getTotal();
				Label information = new Label(6,i+1,String.valueOf(informaNumber));
				ws.addCell(information);
				// 网站点播量
				String netTotal = "0";
				Label lb_total = new Label(7, i + 1, netTotal);
				ws.addCell(lb_total);
				i++;
			}
			// 保存
			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 getBoradReport(String sortIndex, String month, OutputStream out) {
		List list = findAllVolume(sortIndex, month);
		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, "截止日期");
			ws.addCell(lb_2);
			Label lb_3 = new Label(2, 0, "总点播量");
			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);

			// 明细数据
			for (int i = 0; i < list.size(); i++) {
				BoradVolumeBean inform = (BoradVolumeBean) list.get(i);
				// 栏目
				if (inform == null)
					continue;
				String sortName = inform.getSortName();
				Label sort = new Label(0, i + 1, sortName);
				ws.addCell(sort);
				// 截止日期
				String mon = inform.getMonth();
				Label monthLabel = new Label(1, i + 1, mon);
				ws.addCell(monthLabel);
				// 总点播量
				Integer total = inform.getTotal();
				Label totalNumber = new Label(2, i + 1, String.valueOf(total));
				ws.addCell(totalNumber);
				// 短信点播量
				int infoNumber = inform.getTotal();
				Label infomation = new Label(3, i + 1, String
						.valueOf(infoNumber));
				ws.addCell(infomation);
				// 网站点播量
				String netTotal = "0";
				Label lb_total = new Label(4, i + 1, netTotal);
				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();
				}
			}
		}
	}
}