guangzhouyiselie/GDYSL/.svn/pristine/df/dfb88ac66ff8fe6e09e23f8e1b5d72d4916e9eae.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.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

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

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONObject;
import org.springframework.ui.ModelMap;

import com.cloudera.impala.jdbc4.internal.apache.log4j.Logger;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
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.ExportAllUtils;

/**
 * @author ISSUSER 兼职员工工时提报汇总表
 */
public class DeclarationListHandler extends ListHandler {
	Context ctx = SHRContext.getInstance().getContext();
	private static Logger logger1 = Logger.getLogger("com.kingdee.shr.customer.gtiit.rpt.DeclarationListHandler");
	private int totalCount;

	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 {
		int rows = Integer.parseInt(request.getParameter("rows"));
		int page = Integer.parseInt(request.getParameter("page"));
		// 获取过滤条件
		String fastFilterItems = request.getParameter("fastFilterItems");
		JSONObject tofastFilterItems = new JSONObject(fastFilterItems);
		// 获取时间
		// 入职时间段
		JSONObject periodEmploymentJson = tofastFilterItems.getJSONObject("periodEmployment");
		String pervalues = periodEmploymentJson.getString("values");
		String startDate = null;
		String endDate = null;
		if (pervalues != null && !pervalues.equals("")) {
			JSONObject periodEmploymentValues = periodEmploymentJson.getJSONObject("values");
			startDate = periodEmploymentValues.getString("startDate");
			endDate = periodEmploymentValues.getString("endDate");
		}
		// 获取状态
		JSONObject statusJson = tofastFilterItems.getJSONObject("status");
		String status = statusJson.getString("values");
		IRowSet rs = DbUtil.executeQuery(this.ctx, this.getSql(startDate, endDate, status));
		List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
		int num = 0;
		while (rs.next()) {
			++num;
			if (num >= rows * (page - 1) + 1 && num <= rows * page) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("Name", rs.getString("person"));
				map.put("FNumber", rs.getString("FNumber"));
				map.put("positionName", rs.getString("positionName"));
				map.put("CFSuperiorName", rs.getString("CFSuperiorName"));
				map.put("CFHourlyWage", rs.getBigDecimal("CFHourlyWage"));
				map.put("CFTotalWorkHours", rs.getBigDecimal("CFTotalWorkHours"));
				map.put("CFTotalAmount", rs.getBigDecimal("CFTotalAmount"));
				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("Person Number 职工号");
		list.add("Name 姓名");
		list.add("Position 职位");
		list.add("Line Manager 直属上级");
		list.add("Hourly Rate 时薪");
		list.add("Working Hours 工时数");
		list.add("Total Payment 总工资");
		return list;

	}

	public List<Map<String, Object>> getcolModelAction() {
		List<Map<String, Object>> modelColList = new ArrayList<Map<String, Object>>();
		modelColList.add(this.buildColModelMap("FNumber", "Person Number 职工号", 70));
		modelColList.add(this.buildColModelMap("Name", "Name 姓名", 70));
		modelColList.add(this.buildColModelMap("positionName", "Position 职位", 70));
		modelColList.add(this.buildColModelMap("CFSuperiorName", "Line Manager 直属上级", 70));
		modelColList.add(this.buildColModelMap("CFHourlyWage", "Hourly Rate 时薪", 70));
		modelColList.add(this.buildColModelMap("CFTotalWorkHours", "Working Hours 工时数", 70));
		modelColList.add(this.buildColModelMap("CFTotalAmount", "Total Payment 总工资", 70));
		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 getSql(String starTime, String endTime, String status) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct d.FName_L1 as person,d.FNumber,e.FName_L1 as positionName,c.CFSuperiorName,c.CFHourlyWage ,a.CFTotalWorkHours,a.CFTotalAmount FROM T_HR_SBatchSubmitShemeBill a  right join T_HR_SchemeBillEntry c on a.fid=c.FBillID left join t_bd_person d on c.FPersonID=d.fid left join T_ORG_Position e on c.FPositionID= e.fid where 1 =1  and a.fsubmitschemeid in(SELECT fid FROM T_HR_SCalSubmitScheme where fnumber in ('eply_GS','eply_LZGS'))\r\n" + 
				"");

		// 可能传多个状态过来
		String[] statusArry = new String[0];
		if (StringUtils.isNoneBlank(status)) {
			statusArry = status.split(",");
			sql.append(" and  a.FBillState in (");
			for (String statu : statusArry) {
				sql.append("'");
				sql.append(statu);
				sql.append("'");
				sql.append(",");
			}
			// 删除最后一个逗号
			sql.deleteCharAt(sql.length() - 1);
			sql.append(")");

			if (StringUtils.isNoneBlank(starTime) && StringUtils.isNoneBlank(endTime)) {
				sql.append(" and  c.FEffectDate >=");
				sql.append("'");
				sql.append(starTime);
				sql.append("' and  c.FEffectDate<= '");
				sql.append(endTime);
				sql.append("'");

			}
		} else if (StringUtils.isNoneBlank(starTime) && StringUtils.isNoneBlank(endTime)) {
			sql.append(" and  c.FEffectDate >=");
			sql.append("'");
			sql.append(starTime);
			sql.append("' and  c.FEffectDate<= '");
			sql.append(endTime);
			sql.append("'");

		}
		return sql.toString();
	}



	// 导出
	// 导出数据
	public String toExcelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException {
		// 获取过滤条件
		String fastFilterItems = request.getParameter("fastFilterItems");
		JSONObject tofastFilterItems = new JSONObject(fastFilterItems);
		// 获取时间
		// 入职时间段
		JSONObject periodEmploymentJson = tofastFilterItems.getJSONObject("periodEmployment");
		String pervalues = periodEmploymentJson.getString("values");
		String startDate = null;
		String endDate = null;
		if (pervalues != null && !pervalues.equals("")) {
			JSONObject periodEmploymentValues = periodEmploymentJson.getJSONObject("values");
			startDate = periodEmploymentValues.getString("startDate");
			endDate = periodEmploymentValues.getString("endDate");
		}
		// 获取状态
		JSONObject statusJson = tofastFilterItems.getJSONObject("status");
		String status = statusJson.getString("values");
		IRowSet rs = DbUtil.executeQuery(this.ctx, this.getSql(startDate, endDate, status));

		LinkedHashMap<String, String> selectFieldsTypeMap = new LinkedHashMap<String, String>();
		String stringType = "String_@";
		// String dateType = "Date_yyyy/MM/dd";
		// String numberType = "Number_0_%s";
		selectFieldsTypeMap.put("FNumber", stringType);
		selectFieldsTypeMap.put("person", stringType);
		selectFieldsTypeMap.put("positionName", stringType);
		selectFieldsTypeMap.put("CFSuperiorName", stringType);
		selectFieldsTypeMap.put("CFHourlyWage", stringType);
		selectFieldsTypeMap.put("CFTotalWorkHours", stringType);
		selectFieldsTypeMap.put("CFTotalAmount", stringType);

		ArrayList<String> list = this.getcolNamesAction();
		ArrayList<String> fieldlist = this.getcolNamesAction();
		fieldlist.add("CFHourlyWage");
		fieldlist.add("CFTotalWorkHours");
		fieldlist.add("CFTotalAmount");
		exportData(response, rs, "StaffTimekeepingList.xlsx", selectFieldsTypeMap, list,fieldlist);
		return null;
	}
	
	
	/**
	 * 
	 * @param response
	 *            response
	 * @param resource
	 *            需要导出的数据源
	 * @param fileName
	 *            导出文件名称
	 * @param selectFieldsTypeMap
	 *            存放查询字段对应导出类型和导出样式 key selectField 导出字段 value
	 *            fieldType_cellStyleMap key 导出查询字段 value 导出类型_导出样式 导出样式 key
	 *            DateType 日期格式 value "yyyy-mm-dd" key NumberType 数字 value '0.0_
	 *            '->保留一位小数 '0_ '->整数 key StringType 文本 value @
	 * @throws SQLException
	 */
	public static void exportData(HttpServletResponse response, Object resource, String fileName,
			LinkedHashMap<String, String> selectFieldsTypeMap,List<String>list,List<String>fiedlist) throws SQLException {
		// 创建新的Excel 工作簿
		// Excel2003版本（包含2003）以前使用HSSFWorkbook类，扩展名为.xls
		// Excel2007版本（包含2007）以后使用XSSFWorkbook类，扩展名为.xlsx
		XSSFWorkbook workBook = new XSSFWorkbook();

		// 在Excel工作簿中创建新的工作表，名为"Sheet1"
		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());
				//List<String> chineseList = fieldsTypeMap.keySet().stream().collect(Collectors.toList());
				// 创建字体样式
				XSSFFont font = workBook.createFont();
				font.setFontName("宋体"); // 设置字体为 Arial
				font.setFontHeightInPoints((short) 11); // 设置字体大小为 12 点
				font.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色为黑色
				font.setBold(true);
				// 创建一个新的单元格样式，并将字体样式应用于该样式
				CellStyle cellStyle = workBook.createCellStyle();
				cellStyle.setFont(font);
				if (i == 0) {
					for (int j = 0; j < numColumns; j++) {
						// 创建列
						XSSFCell cell = titleRow.createCell(j);
						//cell.setCellValue(keyList.get(j));
						//cell.setCellValue(chineseList.get(j));
						cell.setCellValue(list.get(j));	
						
						cell.setCellValue(list.get(j));
						// 调整列宽
						sheet.setColumnWidth(j, 6000);
						// 应用字体样式
						titleRow.getCell(j).setCellStyle(cellStyle);
					}
				}

				i++;
				// 创建数据
				while (rs.next()) {
					try {
						XSSFRow dateRow = sheet.createRow(i);
						for (int j = 0; j < numColumns; j++) {
							// 创建列
							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(fiedlist.contains(column)&&StringUtils.isNotBlank(rs.getString(column))) {
								String bigcolumn=rs.getBigDecimal(column).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
								cell.setCellValue(bigcolumn);
							}else {
								cell.setCellValue(rs.getString(column));

							}
//							if ("Date".equals(fieldType)) {
//								// 日期
//								// cell.setCellValue(rs.getString(column));
//								Date date = rs.getDate(column);
//								cell.setCellValue(date);
//								XSSFCellStyle stylee = workBook.createCellStyle();
//								stylee.setDataFormat(format.getFormat(cellStyleType));
//								cell.setCellStyle(stylee);
//								// cell.setCellValue(rs.getString(column));
//							} else if ("Number".equals(fieldType)) {
//								// 数值 (小数和整数)
//								String value = rs.getString(column);
//								cell.setCellValue(value);
//								if (value != null && !("").equals(value)) {
//									XSSFCellStyle style = workBook.createCellStyle();
//									style.setDataFormat(format.getFormat(String.format(cellStyleType, " ")));
//									cell.setCellStyle(style);
//									if (cellStyleType.contains(".")) {
//										// 小数
//										cell.setCellValue(Double.parseDouble(value));
//									} else {
//										cell.setCellValue(Integer.parseInt(value));
//									}
//								}
//							} else if ("String".equals(fieldType)) {
//								// 文本类型
//								if (rs.getString(column) != null && !("").equals(rs.getString(column))) {
//									// style.setDataFormat(format.getFormat(cellStyleType));
//									// cell.setCellStyle(style);
//									cell.setCellValue(rs.getString(column));
//								}
//							}
						}
						i++;
					} catch (Exception e) {
						logger1.info("Excel file was created Error!" + e.getMessage());
					}
				}

				try {

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

			}

		}

	}


	public static void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			try {
				fileName = URLEncoder.encode(fileName, "UTF-8");
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			response.setContentType("application/octet-stream;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
}