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

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
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.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.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 PassportsListHandler extends ListHandler {
	Context ctx = SHRContext.getInstance().getContext();
	private static Logger logger1 = Logger.getLogger("com.kingdee.shr.customer.gtiit.rpt.PassportsListHandler");
	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 personJson = tofastFilterItems.getJSONObject("person");
		String personid = personJson.getString("values");
		JSONObject statusJson = tofastFilterItems.getJSONObject("status");
		String status = statusJson.getString("values");
		IRowSet rs = DbUtil.executeQuery(this.ctx, this.getSql(personid, 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("FName_L1", rs.getString("FName_L1"));
				map.put("FCredentialsTypeNO", rs.getString("FCredentialsTypeNO"));
				map.put("FEmail", rs.getString("FEmail"));
				map.put("CFPrivateemail", rs.getString("CFPrivateemail"));
				map.put("Person Number", rs.getString("FNumber"));
				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("Display Name");
		list.add("Passport Number");
		list.add("Work Email");
		list.add("Private Email");
		list.add("Person Number");
		return list;

	}

	public List<Map<String, Object>> getcolModelAction() {
		List<Map<String, Object>> modelColList = new ArrayList<Map<String, Object>>();
		modelColList.add(this.buildColModelMap("FName_L1", "Display Name", 70));
		modelColList.add(this.buildColModelMap("FCredentialsTypeNO", "Passport Number", 70));
		modelColList.add(this.buildColModelMap("FEmail", "Work Email", 70));
		modelColList.add(this.buildColModelMap("CFPrivateemail", "Private Email", 70));
		modelColList.add(this.buildColModelMap("Person Number", "Person Number", 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 peronid, String status) {
		StringBuffer sql = new StringBuffer();
		sql.append("\r\n" + 
				"\r\n" + 
				"\r\n" + 
				"select a.FCredentialsTypeNO, c.FName_L1,d.FEmail,d.CFPrivateemail, c.FNumber from T_HR_PersonCredentialsType a left join T_HR_CredentialsType b on a.FCredentialsTypeID=b.fid left join T_BD_Person c \r\n" + 
				" on a.FPERSONID =c.fid \r\n" + 
				" left join T_HR_PersonContactMethod d on d.FPERSONID =c.fid   left join (select * from T_HR_EmpLaborRelationHis where FIsLatestInAday = 1  ) k on  c.fid = k.FPERSONID\r\n" + 
				"left join T_HR_BDEmployeeType p on k.FLABORRELATIONSTATEID=p.fid where   k.FEndDateTime>='2199-01-01'  and  b.FNumber='OT001' ");
		// 传了多个人员需要拆开拼接sql
		StringBuffer personsql = new StringBuffer();
		String[] personidArry = new String[0];
		if (!peronid.isEmpty()) {

			personidArry = peronid.split(",");
		}
		// 状态的sql
		StringBuffer statussql = new StringBuffer();
		statussql.append(" and p.FINSERVICE = ");
		statussql.append("'");
		statussql.append(status);
		statussql.append("'");
		if (personidArry.length > 0) {
			personsql.append(" and c.fid in ");
			personsql.append("(");
			for (String pid : personidArry) {
				personsql.append("'");
				personsql.append(pid);
				personsql.append("'");
				personsql.append(",");
			}
			// 删除最后一个逗号
			personsql.deleteCharAt(personsql.length() - 1);
			personsql.append(")");
			sql.append(personsql);
			// 和状态拼接
			if (status != null && !status.equals("")) {
				sql.append(statussql);
			}
		} else if (status != null && !status.equals("")) {
			sql.append(statussql);
		} else {
			sql.append(" and p.FINSERVICE in ('1','2') ");
		}
		System.out.println("这个sql是：" + sql.toString());
		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 personJson = tofastFilterItems.getJSONObject("person");
		String personid = personJson.getString("values");
		String newperonid = personid.replace(" ", "+"); // 传过来id+可能是空格需要转
		JSONObject statusJson = tofastFilterItems.getJSONObject("status");
		String status = statusJson.getString("values");
		IRowSet rs = DbUtil.executeQuery(this.ctx, this.getSql(newperonid, 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("FName_L1", stringType);
		selectFieldsTypeMap.put("FCredentialsTypeNO", stringType);
		selectFieldsTypeMap.put("FEmail", stringType);
		selectFieldsTypeMap.put("CFPrivateemail", stringType);
		selectFieldsTypeMap.put("FNumber", stringType);
		//ArrayList<String> list = this.getcolNamesAction();
//		LinkedHashMap<String,String> fieldsTypeMap = new LinkedHashMap<String, String>();
//		fieldsTypeMap.put("Display Name", stringType);
//		fieldsTypeMap.put("Passport Number", stringType);
//		fieldsTypeMap.put("Work Email", stringType);
//		fieldsTypeMap.put("Private Email", stringType);
		ArrayList<String> list = this.getcolNamesAction();
		ExportAllUtils.exportData(response, rs, "PassportsNumberList.xlsx", selectFieldsTypeMap,list);
		return null;

	}

	
}