server-zx/src/main/java/cn/hmsoft/mr/control/std/StdExportControl.java

package cn.hmsoft.mr.control.std;

import java.io.ByteArrayOutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import cn.hmsoft.frame.constants.FrameStatus;
import cn.hmsoft.frame.control.FrameControl;
import cn.hmsoft.frame.util.FrameDictUtil;
import cn.hmsoft.helper.excel.Excel2007Writer;
import cn.hmsoft.mr.data.dao.std.StdApplyDetailDao;
import cn.hmsoft.mr.data.dao.std.StdEnrolDao;
import cn.hmsoft.mr.data.model.std.StdApplyDetail;

@RestController
public class StdExportControl extends FrameControl {

	@Autowired
	private StdEnrolDao daoEnrol;
	@Autowired
	private StdApplyDetailDao daoDetail;
	
	@RequestMapping("std/enrol/video/export")
	public void export() {
		List<List<Object>> values = new ArrayList<List<Object>>();
    	List<Object> headerList = new ArrayList<Object>();
    	ServletOutputStream outStream = null;
    	headerList.add("考生姓名");
    	headerList.add("证件号码");
    	headerList.add("考生性别");
    	headerList.add("报考专业");
    	headerList.add("考生类型");
    	headerList.add("是否完成上传");
    	values.add(headerList);
    	List<Map<String,Object>> enrolList = daoEnrol.listStdEnrolPay(FrameStatus.Active.toString());
    	for (Map<String,Object> enrol : enrolList) {
			List<Object> rowList = new ArrayList<>();
			rowList.add(getStdName(enrol));
			rowList.add(enrol.get("cert_id"));
			rowList.add(enrol.get("std_sex"));
			rowList.add(enrol.get("aspect_name"));
			rowList.add(FrameDictUtil.getFrameDictText("StdType", String.valueOf(enrol.get("std_type"))));
			rowList.add(getHaveUpload(Integer.parseInt(String.valueOf(enrol.get("std_id"))),String.valueOf(enrol.get("aspect_name"))));
			values.add(rowList);
		}
    	
    	try {
			String fileName =  "考生视频上传情况" + ".xlsx";
			fileName = URLEncoder.encode(fileName, "UTF-8");
			fileName = new String(fileName.getBytes(), "ISO-8859-1");
	    	XSSFWorkbook wb = Excel2007Writer.createExcel(values);
	    	ByteArrayOutputStream os = new ByteArrayOutputStream();
			wb.write(os);
			this.getResponse().reset();
			this.getResponse().setContentType("application/x-msdownload");
			this.getResponse().setHeader("Content-Disposition", "attachment; filename=" + fileName);
			outStream = this.getResponse().getOutputStream();
			outStream.write(os.toByteArray());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(outStream != null) {
				try {
					outStream.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}

	private String getHaveUpload(Integer std_id, String aspect_name) {
		List<StdApplyDetail> detailList = daoDetail.listStdApplyDeail(std_id, aspect_name);
		if(detailList != null && detailList.size() > 0) return "已上传";
		return "未上传";
	}

	private Object getStdName(Map<String, Object> enrol) {
		String std_type = String.valueOf(enrol.get("std_type"));
		if(std_type.equals("Overseas") || std_type.equals("HMT")) {
			return enrol.get("std_name");
		} else {
			return enrol.get("passport_last_name")+ " " +enrol.get("passport_name");
		}
	}
	
	@RequestMapping("std/enrol/video/totol/export")
	public void totalExport() {
		List<List<Object>> values = new ArrayList<List<Object>>();
    	List<Object> headerList = new ArrayList<Object>();
    	ServletOutputStream outStream = null;
    	headerList.add("专业名称");
    	headerList.add("报考总人数");
    	headerList.add("已缴费人数");
    	headerList.add("未缴费人数");
    	headerList.add("完成上传人数");
    	headerList.add("未上传人数");
    	values.add(headerList);
    	List<Map<String,Object>> list = daoEnrol.totalStd();
    	for (Map<String, Object> map : list) {
			List<Object> rowList = new ArrayList<>();
			rowList.add(map.get("aspect_name"));
			rowList.add(map.get("nums"));
			rowList.add(map.get("have_pay_num"));
			rowList.add(map.get("no_pay_num"));
			Integer haveUploadNum = daoDetail.countStdByAspect(String.valueOf(map.get("aspect_name")));
			rowList.add(haveUploadNum);
			rowList.add(Integer.parseInt(String.valueOf(map.get("have_pay_num")))-haveUploadNum);
			values.add(rowList);
		}
    	try {
			String fileName =  "专业视频上传统计" + ".xlsx";
			fileName = URLEncoder.encode(fileName, "UTF-8");
			fileName = new String(fileName.getBytes(), "ISO-8859-1");
	    	XSSFWorkbook wb = Excel2007Writer.createExcel(values);
	    	ByteArrayOutputStream os = new ByteArrayOutputStream();
			wb.write(os);
			this.getResponse().reset();
			this.getResponse().setContentType("application/x-msdownload");
			this.getResponse().setHeader("Content-Disposition", "attachment; filename=" + fileName);
			outStream = this.getResponse().getOutputStream();
			outStream.write(os.toByteArray());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(outStream != null) {
				try {
					outStream.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}