guangzhouyiselie/GDYSL/.svn/pristine/4b/4bc4099e6bbec2c1d5cdd0924d820fc80759ec3f.svn-base

package com.kingdee.shr.customer.gtiit.rpt;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.ui.ModelMap;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.eas.hr.base.HRBillStateEnum;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.base.syssetting.context.SHRContext;
import com.kingdee.shr.base.syssetting.exception.SHRWebException;
import com.kingdee.shr.base.syssetting.web.handler.ListHandler;
import com.kingdee.shr.base.syssetting.web.json.JSONUtils;
import com.kingdee.shr.customer.gtiit.util.DateTimeUtils;
import com.kingdee.shr.customer.gtiit.util.ExportUtils;
import com.kingdee.shr.customer.gtiit.util.RptUtils;

/**
 * 教师缺勤报告
 * @author xiaoxin
 *
 */
public class FacultyAbsenceReportListHandler extends ListHandler {
	private static Logger logger =
	        Logger.getLogger("com.kingdee.shr.customer.gtiit.rpt.FacultyAbsenceReportListHandler");
	Context ctx = SHRContext.getInstance().getContext();
	private int totalCount;

	public FacultyAbsenceReportListHandler() {
	}

	public String getGridColModelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws SHRWebException, BOSException, SQLException {
		List<String> colNames = this.getcolNamesAction();
		List<Map<String, Object>> colModel = this.getcolModelAction();
		LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
		map.put("colNames", colNames);
		map.put("colModel", colModel);
		JSONUtils.writeJson(response, map);
		return null;
	}

	public void getGridDataAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException, ParseException {
		int rows = Integer.parseInt(request.getParameter("rows"));
		int page = Integer.parseInt(request.getParameter("page"));
		List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
		int num = 0;
		// 获取报表查询过滤条件
		List<String> qfNames = new ArrayList<>();
		qfNames.add("queryTime");
		String parameter = request.getParameter("fastFilterItems");
		JSONObject paramJson = JSON.parseObject(parameter);
		Map<String, Object> rptSQLFilterate = RptUtils.getRptSQLFilterate(paramJson, qfNames);
		
		Map<String, Object> contractDateMap = (Map<String, Object>) rptSQLFilterate.get("queryTime");
		Date startDate = null;
		Date endDate = null;
		if(ObjectUtils.isNotEmpty(contractDateMap.get("startDate")) && ObjectUtils.isNotEmpty(contractDateMap.get("endDate"))) {
			startDate = DateTimeUtils.parseDate((String)contractDateMap.get("startDate")+" 08:30", "yyyy-MM-dd HH:mm");
			endDate = DateTimeUtils.parseDate((String)contractDateMap.get("endDate")+" 17:30", "yyyy-MM-dd HH:mm");
		}
		
		// 获取报表数据源
		String sql =getSql(rptSQLFilterate);
		
		String searchFilterItems = request.getParameter("searchFilterItems");
		if (StringUtils.isNoneBlank(searchFilterItems)) {
			String filterItmes = getSearchFilterItems(searchFilterItems);
			sql = sql + " and " + filterItmes;
		}
		
		IRowSet rs = DbUtil.executeQuery(this.ctx, sql);

		while (rs.next()) {
			++num;
			if (num >= rows * (page - 1) + 1 && num <= rows * page) {
				Map<String, Object> map = new HashMap<String, Object>();	
				map.put("StaffID", rs.getString("StaffID"));
				map.put("Name", rs.getString("Name"));
				map.put("PhysicsProgram", rs.getString("PhysicsProgram"));
				map.put("Title", rs.getString("Title"));
				map.put("Document No.", rs.getString("Document No."));
				map.put("Type", rs.getString("Type"));
				map.put("Purpose", rs.getString("Purpose"));
				map.put("Location", rs.getString("Location"));
				map.put("Course", rs.getString("Course"));
				map.put("ReplacerNameForCourse", rs.getString("ReplacerNameForCourse"));
				map.put("Status", rs.getString("Status"));
				
				Date leaveStartDate = rs.getDate("BusinessTripStartDate");
				Date leaveEndDate = rs.getDate("BusinessTripEndDate");
				if(startDate != null && endDate != null) {
					if(startDate.compareTo(leaveStartDate) > 0) {
						leaveStartDate = startDate;
					}
					if(endDate.compareTo(leaveEndDate) < 0) {
						leaveEndDate = endDate;
					}
				}
				map.put("BusinessTripStartDate", leaveStartDate);
				map.put("BusinessTripEndDate", leaveEndDate);
				maplist.add(map);
			}
		}
		this.totalCount = num;
		Map<String, Object> gridDataMap = new LinkedHashMap<String, Object>();
		if (this.totalCount % rows == 0) {
			gridDataMap.put("total", this.totalCount / rows);
		} else {
			gridDataMap.put("total", this.totalCount / rows + 1);
		}
		gridDataMap.put("page", page);
		gridDataMap.put("records", this.totalCount);
		gridDataMap.put("rows", maplist);
		JSONUtils.writeJson(response, gridDataMap);
	}

	public ArrayList<String> getcolNamesAction() throws SHRWebException {
		ArrayList<String> list = new ArrayList<String>();
		list.add("StaffID");
		list.add("Name");
		list.add("PhysicsProgram");
		list.add("Title");
		list.add("Document No.");
		list.add("Type");
		list.add("BusinessTripStartDate");
		list.add("BusinessTripEndDate");
		list.add("Purpose");
		list.add("Location");
		list.add("Course");
		list.add("ReplacerNameForCourse");
		list.add("Status");
		return list;
	}

	public List<Map<String, Object>> getcolModelAction() {
		List<Map<String, Object>> modelColList = new ArrayList<Map<String, Object>>();
		modelColList.add(this.buildColModelMap("StaffID", "工号", 70));
		modelColList.add(this.buildColModelMap("Name", "姓", 120));
		modelColList.add(this.buildColModelMap("PhysicsProgram", "部门", 170));
		modelColList.add(this.buildColModelMap("Title", "岗位", 160));
		modelColList.add(this.buildColModelMap("Document No.", "请假单号", 120));
		modelColList.add(this.buildColModelMap("Type", "假勤类型", 130));
		modelColList.add(this.buildColModelMap("BusinessTripStartDate", "开始时间", 160));
		modelColList.add(this.buildColModelMap("BusinessTripEndDate", "结束时间", 160));
		modelColList.add(this.buildColModelMap("Purpose", "目的", 120));
		modelColList.add(this.buildColModelMap("Location", "位置", 120));
		modelColList.add(this.buildColModelMap("Course", "课程", 160));
		modelColList.add(this.buildColModelMap("ReplacerNameForCourse", "课程的替代者名称", 120));
		modelColList.add(this.buildColModelMap("Status", "单据状态", 90));
		return modelColList;
	}

	private Map<String, Object> buildColModelMap(String index, String label, int width) {
		return this.buildColModelMap(index, label, width, false, false, false);
	}

	private Map<String, Object> buildColModelMap(String index, String label, int width, boolean rowspan, boolean isKey,
			boolean isHedden) {
		Map<String, Object> gridIdMap = new LinkedHashMap<String, Object>();
		gridIdMap.put("index", index);
		gridIdMap.put("name", index);
		gridIdMap.put("label", label);
		gridIdMap.put("width", width);
		if (rowspan) {
			gridIdMap.put("rowspan", rowspan);
		}
		if (isKey) {
			gridIdMap.put("key", isKey);
		}
		if (isHedden) {
			gridIdMap.put("hidden", isHedden);
		}
		return gridIdMap;
	}

	public String toExcelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException {

		List<String> qfNames = new ArrayList<>();
		qfNames.add("queryTime");
		String parameter = request.getParameter("fastFilterItems");
		JSONObject paramJson = JSON.parseObject(parameter);
		Map<String, Object> rptSQLFilterate = RptUtils.getRptSQLFilterate(paramJson, qfNames);
		// 获取报表数据源
		String sql = getSql(rptSQLFilterate);
		
		String searchFilterItems = request.getParameter("searchFilterItems");
		if (StringUtils.isNoneBlank(searchFilterItems)) {
			String filterItmes = getSearchFilterItems(searchFilterItems);
			sql = sql + " and " + filterItmes;
		}
		
		IRowSet rs = DbUtil.executeQuery(this.ctx, sql);

		LinkedHashMap<String, String> selectFieldsTypeMap = new LinkedHashMap<String, String>();

		String stringType = "String_@";
		String dateType = "Date_yyyy/MM/dd HH:mm";
		selectFieldsTypeMap.put("StaffID", "Number_0_%s");
		selectFieldsTypeMap.put("Name", stringType);
		selectFieldsTypeMap.put("PhysicsProgram", stringType);
		selectFieldsTypeMap.put("Title", stringType);
		selectFieldsTypeMap.put("Document No.", stringType);
		selectFieldsTypeMap.put("Type", stringType);
		selectFieldsTypeMap.put("BusinessTripStartDate", dateType);
		selectFieldsTypeMap.put("BusinessTripEndDate", dateType);
		selectFieldsTypeMap.put("Purpose", stringType);
		selectFieldsTypeMap.put("Location", stringType);
		selectFieldsTypeMap.put("Course", stringType);
		selectFieldsTypeMap.put("ReplacerNameForCourse", stringType);
		selectFieldsTypeMap.put("Status", stringType);

		exportData(response, rs, "FacultyAbsenceReport.xlsx", selectFieldsTypeMap, rptSQLFilterate);
		return null;
	}
	
	public static void exportData(HttpServletResponse response, Object resource, String fileName,
			LinkedHashMap<String, String> selectFieldsTypeMap, Map<String, Object> rptSQLFilterate) throws SQLException {
		
		Map<String, Object> contractDateMap = (Map<String, Object>) rptSQLFilterate.get("queryTime");
		Date startDate = null;
		Date endDate = null;
		if(ObjectUtils.isNotEmpty(contractDateMap.get("startDate")) && ObjectUtils.isNotEmpty(contractDateMap.get("endDate"))) {
			startDate = DateTimeUtils.parseDate((String)contractDateMap.get("startDate")+" 08:30", "yyyy-MM-dd HH:mm");
			endDate = DateTimeUtils.parseDate((String)contractDateMap.get("endDate")+" 17:30", "yyyy-MM-dd HH:mm");
		}
		// 创建新的Excel 工作簿
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet sheet = workBook.createSheet("Sheet1");
		DataFormat format =workBook.createDataFormat();
		// 设置数字格式为整数，不保留小数 (_ )空格是必须要切勿格式
		if (resource != null) {
			if (resource instanceof IRowSet) {
				IRowSet rs = (IRowSet) resource;
				// 列数
				int numColumns = selectFieldsTypeMap.entrySet().size();
				// 创建多行多列的数据
				int i = 0;
				// 创建标题
				XSSFRow titleRow = sheet.createRow(i);
				List<String> keyList = selectFieldsTypeMap.keySet().stream().collect(Collectors.toList());

				if (i == 0) {
					for (int j = 0; j < numColumns; j++) {
						// 创建列
						XSSFCell cell = titleRow.createCell(j);
						cell.setCellValue(keyList.get(j));
					}
				}
				i++;
				// 创建数据
				while (rs.next()) {
					try {
						XSSFRow dateRow = sheet.createRow(i);
						for (int j = 0; j < numColumns; j++) {
							XSSFCellStyle style = workBook.createCellStyle();
							// 创建列
							XSSFCell cell = dateRow.createCell(j);
							// 由于开发与测试环境的数据库类型与不一致 导致无法从数据库表读取相关字段类型判断 且有相关计算字段
							// 所以采用参数判断
							String column = keyList.get(j);
							String fieldTypeAndcellStyleType = selectFieldsTypeMap.get(column);
							int indexOf = fieldTypeAndcellStyleType.indexOf("_");
							// 字段属性类型
							String fieldType = fieldTypeAndcellStyleType.substring(0, indexOf);
							// 样式类型
							String cellStyleType = fieldTypeAndcellStyleType.substring(indexOf + 1, fieldTypeAndcellStyleType.length());
							if ("Date".equals(fieldType)) {
								// 日期
								 
								Date date = rs.getDate(column);
								if(startDate != null && endDate != null) {
									date = column.equals("BusinessTripStartDate") && startDate.compareTo(date)>0?startDate:date;
									date = column.equals("BusinessTripEndDate") && endDate.compareTo(date)<0?endDate:date;
								}
								style.setDataFormat(format.getFormat(cellStyleType));
								cell.setCellStyle(style);
								cell.setCellValue(date);
							} else if ("Number".equals(fieldType)) {
								// 数值 (小数和整数)
								style.setDataFormat(format.getFormat(String.format(cellStyleType, " ")));
								String value = rs.getString(column);
								cell.setCellStyle(style);
								if (cellStyleType.contains(".")) {
									// 小数
									cell.setCellValue(Double.parseDouble(value));
								} else { 
									cell.setCellValue(Integer.parseInt(value));
								}
							} else if ("String".equals(fieldType)) {
								String value = rs.getString(column);
								cell.setCellValue(value);
							}
						}
						i++;
					} catch (Exception e) {
						System.out.println();
						logger.info("Excel file was created Error!" + e.getMessage());
					}
				}

				try {
					ExportUtils.setResponseHeader(response, fileName);
					OutputStream os = response.getOutputStream();
					workBook.write(os);
					workBook.close();
					os.flush();
					os.close();
					logger.info("Excel file was created successfully!");
				} catch (IOException e) {
					logger.info("Error while writing to file " + e.getMessage());
					e.printStackTrace();
				}
			}
		}
	}

	private String getSql(Map<String, Object> params) {
		Map<String, Object> contractDateMap = (Map<String, Object>) params.get("queryTime");
		Object startDate = contractDateMap.get("startDate");
		Object endDate = contractDateMap.get("endDate");
		
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.FNUMBER \"Document No.\",d.fnumber StaffID,d.fname_l1 Name,f.fname_l1 PhysicsProgram,g.fname_l1 Title,c.fname_l1 Type,");
		sql.append("b.FREALBEGINTIME BusinessTripStartDate,b.FREALENDTIME BusinessTripEndDate,b.CFPURPOSE Purpose,b.CFCITY Location,");
		sql.append("b.CFAlternativeTeacher as ReplacerNameforCourse,b.CFTeachingTask as Course,case when a.FBILLSTATE = 3 then 'Approved' else 'Pending Approval' end Status ");
		sql.append(" FROM T_HR_ATS_LeaveBill a ");
		sql.append("left join T_HR_ATS_LeaveBillEntry b on a.fid = b.fbillid ");
		sql.append("left join  T_HR_ATS_HolidayPolicy c on c.fid=b.fpolicyid ");
		sql.append("left join t_bd_person d on d.fid = b.FPERSONID ");
		sql.append("left join (SELECT emh.fpersonid,emh.fadminorgid,emh.fpositionid,emh.CFWorkercategoryID FROM ");
		sql.append("  (select fpersonid,max(fleffdt) maxdate from T_HR_EmpOrgRelation ");
		sql.append("  where fassigntype = 1 and fislatestinaday = 1 group by fpersonid) emp ");
		sql.append("  left join T_HR_EmpOrgRelation emh on emp.fpersonid = emh.fpersonid and emp.maxdate = emh.fleffdt ");
		sql.append("  where emh.fassigntype = 1 and emh.fislatestinaday = 1) e on e.fpersonid = b.FPERSONID ");
		sql.append("left join T_ORG_BaseUnit f on f.fid = e.FAdminOrgID ");
		sql.append("left join T_ORG_Position g on g.fid = e.fpositionid ");
		sql.append("left join CT_MP_WorkerCategory h on h.fid = e.CFWorkercategoryID ");
		sql.append("where a.FBILLSTATE in ('1','2','3') and h.fnumber = 'GTIIT_FACULTY' ");
		if(ObjectUtils.isNotEmpty(startDate) && ObjectUtils.isNotEmpty(endDate)) {
			startDate = startDate + " 08:30";
			endDate = endDate + " 17:30";
			sql.append(" and b.FREALBEGINTIME <= '"+endDate+"' and b.FREALENDTIME >= '"+startDate+"' ");
		}
		logger.error("教师缺勤报告SQL："+sql.toString());
		return sql.toString();
	}
	
	/**
	 * 获取serch的过虑条件返回拼接sql
	 * @param searchFilterItems
	 * @return
	 */
	public String getSearchFilterItems(String searchFilterItems) {
		// 获取serch的过虑条件
		String filter = null;
		if (StringUtils.isNotBlank(searchFilterItems)) {
			// 姓名
			if (searchFilterItems.indexOf("name") != -1) {
				filter = searchFilterItems.replaceAll("name", "d.fname_l1");
			}
			// 编码
			if (searchFilterItems.indexOf("number") != -1) {
				filter = searchFilterItems.replaceAll("number", "d.fnumber");
			}
		}
		if (filter != null) {
			return filter.replace("(", "").replace(")", "");
		}
		return filter;

	}

}