package com.engine.kq.util;

import com.engine.common.util.ServiceUtil;
import com.engine.kq.bean.KQBalanceDetail;
import com.engine.kq.bean.KQBalanceOfLeave;
import com.engine.kq.bean.KQHolidaySet;
import com.engine.kq.bean.KQScheduleCode;
import com.engine.kq.biz.KQBalanceOfLeaveBiz;
import com.engine.kq.biz.KQLeaveRulesBiz;
import com.engine.kq.biz.KQLeaveRulesComInfo;
import com.engine.kq.biz.KQLeaveRulesDetailComInfo;
import com.engine.kq.service.ImportLogService;
import com.engine.kq.service.impl.ImportLogServiceImpl;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import weaver.conn.RecordSet;
import weaver.file.ImageFileManager;
import weaver.general.BaseBean;
import weaver.general.GCONST;
import weaver.general.ThreadVarLanguage;
import weaver.general.TimeUtil;
import weaver.general.Util;
import weaver.hrm.User;
import weaver.hrm.moduledetach.ManageDetachComInfo;
import weaver.systeminfo.SystemEnv;
import weaver.hrm.common.Tools;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 节假日设置--Excel导入
 */
public class ImportProcessUtil extends BaseBean {

    /**
     * 导入类型
     */
    private String importtype;

    /**
     * 文件名称
     */
    private String filename;
    /**
     * excel页签
     */
    private HSSFSheet sheet;
    /**
     * 存储Excel的数据
     */
    private List<Object> lsImportSourceMap = new ArrayList<Object>();
    /**
     * 存储报错信息
     */
    private List<String> errorInfo = new ArrayList<String>();
    /**
     * 存储参数类型名称
     */
    private Map<String, Method> parameterTypes = new HashMap<String, Method>();
    /**
     * 字段类型
     */
    private Map<String, String> fieldTypes = new HashMap<String, String>();
    /**
     * 类名
     */
    private Map<String, String[]> mapImportSource = new HashMap<String, String[]>();
    /**
     * 导入类型名称
     */
    private Map<String, String> mapImportSourceName = new HashMap<String, String>();
    /**
     * excel文件中对应的字段别名
     */
    private Map<String, String[]> mapImportSourceField = new HashMap<String, String[]>();
    /**
     * excel文件中对应的字段名称
     */
    private Map<String, String[]> mapImportSourceFieldName = new HashMap<String, String[]>();
    /**
     * 多语言--中文
     */
    private int userlanguage = 7;
    /**
     * 字段名
     */
    private String voFields[];
    /**
     * 类
     */
    private Class<?> importSourceClass;
    /**
     * 字段标识
     */
    private String keyField = "";
    /**
     * 操作人
     */
    private String creater = "";

    private User user;
    /**
     * 导入记录的标识
     */
    private int pId = 0;

    /**
     * 仅用于假期余额导入
     */
    private String leaveRulesId = "";//假期规则的ID

    /**
     * 仅用于假期余额导入
     */
    private String belongYear = "";//所属年份

    public ImportProcessUtil() {
        mapImportSource.put("holidaySet", new String[]{KQHolidaySet.class.getName()});
        mapImportSource.put("kqScheduleCode", new String[]{KQScheduleCode.class.getName()});
        mapImportSourceName.put("holidaySet", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(388727,weaver.general.ThreadVarLanguage.getLang())+"");
        mapImportSourceName.put("kqScheduleCode", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(511419,weaver.general.ThreadVarLanguage.getLang())+"");
        mapImportSourceField.put("holidaySet", new String[]{"groupName,holidayDate,changeTypeName,holidayDesc,relatedDayName"});
        mapImportSourceField.put("kqScheduleCode", new String[]{"lastname,workcode,devicecode,schedulecode"});
        mapImportSourceFieldName.put("holidaySet", new String[]{""+weaver.systeminfo.SystemEnv.getHtmlLabelName(388700,weaver.general.ThreadVarLanguage.getLang())+"["+weaver.systeminfo.SystemEnv.getHtmlLabelName(18019,weaver.general.ThreadVarLanguage.getLang())+"],"+weaver.systeminfo.SystemEnv.getHtmlLabelName(31131,weaver.general.ThreadVarLanguage.getLang())+"["+weaver.systeminfo.SystemEnv.getHtmlLabelName(18019,weaver.general.ThreadVarLanguage.getLang())+"],"+weaver.systeminfo.SystemEnv.getHtmlLabelName(22256,weaver.general.ThreadVarLanguage.getLang())+"["+weaver.systeminfo.SystemEnv.getHtmlLabelName(18019,weaver.general.ThreadVarLanguage.getLang())+"],"+weaver.systeminfo.SystemEnv.getHtmlLabelName(85,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(16754,weaver.general.ThreadVarLanguage.getLang())+""});
        mapImportSourceFieldName.put("kqScheduleCode", new String[]{""+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005347,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005348,weaver.general.ThreadVarLanguage.getLang())+"["+weaver.systeminfo.SystemEnv.getHtmlLabelName(18019,weaver.general.ThreadVarLanguage.getLang())+"]"});
    }

    public List<String> importXls(Map<String, Object> params, User user, HttpServletRequest request) {
        ImportLogService importLogService = (ImportLogServiceImpl) ServiceUtil.getService(ImportLogServiceImpl.class, user);
        try {
            this.user = user;
            this.filename = (String) params.get("excelfile");
            this.importtype = Util.null2String(params.get("importType"));
            this.keyField = Util.null2String(params.get("keyField"));
            this.creater = "" + user.getUID();
            this.userlanguage = user.getLanguage();

            /*根据假期规则的ID加载相关模板数据*/
            if (this.importtype.equals("balanceOfLeave")) {
                String ruleId = Util.null2String(params.get("ruleId"));//假期规则的ID
                String searchYearSelect = Util.null2String(params.get("searchYearSelect"));//日期控件的选择值
                String searchYear = Util.null2String(params.get("searchYear"));//年份
                if (searchYearSelect.equals("5") || searchYearSelect.equals("8")) {
                    searchYear = TimeUtil.getDateByOption(searchYearSelect, "0").substring(0, 4);
                }
                this.leaveRulesId = ruleId;
                this.belongYear = searchYear;
                KQLeaveRulesComInfo rulesComInfo = new KQLeaveRulesComInfo();
                String leaveName = Util.formatMultiLang(rulesComInfo.getLeaveName(this.leaveRulesId), "" + this.userlanguage);//假期名称
                String UnitName = rulesComInfo.getUnitName(this.leaveRulesId, user.getLanguage());//最小请假单位的单位名称，天/小时

                /*判断该假期类型是否属于 法定年假+福利年假 的类型(暂不支持一个请假类型下既存在"按入职时长+工龄自动发放"的余额发放方式，又存在其他发放方式)*/
                boolean isMixMode = KQLeaveRulesBiz.isMixMode(this.leaveRulesId);
                //判断该假期类型是否属于 调休
                boolean isTiaoXiu = KQLeaveRulesBiz.isTiaoXiu(this.leaveRulesId);

                String[] mapImportSourceFieldNameArr = null;
                String[] mapImportSourceFieldArr = null;
                if (isMixMode) {
                    mapImportSourceFieldNameArr = new String[]{"ID("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(500228,weaver.general.ThreadVarLanguage.getLang())+"" + UnitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389745,weaver.general.ThreadVarLanguage.getLang())+"" + UnitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389743,weaver.general.ThreadVarLanguage.getLang())+"" + UnitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(500229,weaver.general.ThreadVarLanguage.getLang())+"" + UnitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389746,weaver.general.ThreadVarLanguage.getLang())+"" + UnitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389744,weaver.general.ThreadVarLanguage.getLang())+"" + UnitName};
                    mapImportSourceFieldArr = new String[]{"resourceId,workcode,lastName,baseAmount,extraAmount,usedAmount,baseAmount2,extraAmount2,usedAmount2"};
                } else if (isTiaoXiu) {
                    mapImportSourceFieldNameArr = new String[]{"ID("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(15933,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(887,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(130286,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(26642,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(508169,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(19547,weaver.general.ThreadVarLanguage.getLang())+""};
                    mapImportSourceFieldArr = new String[]{"resourceId,workcode,lastName,belongYear,belongMonth,totalAmount,usedAmount,effectiveDate,expirationDate"};
                } else {
                    mapImportSourceFieldNameArr = new String[]{"ID("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+")," + leaveName + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(17638,weaver.general.ThreadVarLanguage.getLang())+"" + UnitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(130286,weaver.general.ThreadVarLanguage.getLang())+"" + leaveName + UnitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(26642,weaver.general.ThreadVarLanguage.getLang())+"" + leaveName + UnitName};
                    mapImportSourceFieldArr = new String[]{"resourceId,workcode,lastName,baseAmount,extraAmount,usedAmount"};
                }
                if (isTiaoXiu) {
                    mapImportSource.put("balanceOfLeave", new String[]{KQBalanceDetail.class.getName()});
                    mapImportSourceName.put("balanceOfLeave", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005350,weaver.general.ThreadVarLanguage.getLang())+"");
                    mapImportSourceField.put("balanceOfLeave", mapImportSourceFieldArr);
                    mapImportSourceFieldName.put("balanceOfLeave", mapImportSourceFieldNameArr);
                } else {
                    mapImportSource.put("balanceOfLeave", new String[]{KQBalanceOfLeave.class.getName()});
                    mapImportSourceName.put("balanceOfLeave", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(389989,weaver.general.ThreadVarLanguage.getLang())+"");
                    mapImportSourceField.put("balanceOfLeave", mapImportSourceFieldArr);
                    mapImportSourceFieldName.put("balanceOfLeave", mapImportSourceFieldNameArr);
                }
            }

            ImageFileManager manager = new ImageFileManager();
            manager.getImageFileInfoById(Util.getIntValue(this.filename));
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(manager.getInputStream()));

            params.put("operateType", "add");
            Map<String, Object> retmap = importLogService.saveImportLog(params, request, user);
            if (Util.null2String(retmap.get("status")).equals("1")) {
                this.pId = Util.getIntValue(Util.null2String(retmap.get("pId")));
                String pIdName = "hrm" + this.importtype + "PId";
                request.getSession(true).setAttribute(pIdName, this.pId);
            }

            String[] tmpMapImportSource = mapImportSource.get(this.importtype);
            int idx = 0;
            for (String tmpImportSource : tmpMapImportSource) {
                idx++;
                this.sheet = workbook.getSheetAt(idx);
                importSourceClass = Class.forName(tmpImportSource);
                voFields = mapImportSourceField.get(this.importtype)[idx - 1].split(",");
                initReflectParam();
                /*校验模板有效性*/
                if (idx == 1) {
                    String[] importSourceFieldName = mapImportSourceFieldName.get(this.importtype);
                    String valExcelTempInfo = valExcelTemp(importSourceFieldName[idx - 1]);
                    if (valExcelTempInfo.length() > 0) {
                        this.errorInfo.add(valExcelTempInfo);
                        break;
                    }
                }

                readexcel();
                String importSourceName = "";
                importSourceName = mapImportSourceName.get(this.importtype);

                Object obj = null;
                Method method = null;
                String flag = "";
                String valErrorInfo = "";
                if (lsImportSourceMap == null || lsImportSourceMap.size() == 0) {
                    this.errorInfo.add("【" + importSourceName + "】" + SystemEnv.getHtmlLabelName(34195, userlanguage) + "！");
                }
                for (int i = 0; lsImportSourceMap != null && i < lsImportSourceMap.size(); i++) {
                    obj = lsImportSourceMap.get(i);

                    /*设置关联的假期规则ID和所属年份*/
                    if (this.importtype.equals("balanceOfLeave")) {
                        method = importSourceClass.getMethod("setLeaveRulesId", String.class);
                        method.invoke(obj, this.leaveRulesId);

                        method = importSourceClass.getMethod("setBelongYear", String.class);
                        method.invoke(obj, this.belongYear);

                        method = importSourceClass.getMethod("setOperator", String.class);
                        method.invoke(obj, "" + user.getUID());
                    } else if (this.importtype.equals("kqScheduleCode")) {
                        method = importSourceClass.getMethod("setKeyField", String.class);
                        method.invoke(obj, "" + keyField);
                    }

                    method = importSourceClass.getMethod("setUserLanguage", String.class);
                    method.invoke(obj, "" + userlanguage);

                    method = importSourceClass.getMethod("valExcelData");
                    try {
                        valErrorInfo = (String) method.invoke(obj);
                    } catch (Exception e) {
                        //验证出错返回错误信息
                        params = new HashMap<String, Object>();
                        params.put("pId", this.pId);
                        params.put("lineNum", (i + 1));
                        params.put("relatedName", this.getRelatedName(obj));
                        params.put("msg", importSourceName + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(15323,weaver.general.ThreadVarLanguage.getLang())+"" + (i + 1) + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005351,weaver.general.ThreadVarLanguage.getLang())+"");
                        params.put("status", "0");
                        importLogService.saveImportDeitalLog(params, user);
                        continue;
                    }
                    if (valErrorInfo.length() == 0) {
                        method = importSourceClass.getMethod("save");
                        flag = (String) method.invoke(obj);
                        params = new HashMap<String, Object>();
                        params.put("pId", this.pId);
                        params.put("lineNum", (i + 1));
                        params.put("relatedName", this.getRelatedName(obj));
                        String msg = importSourceName + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(15323,weaver.general.ThreadVarLanguage.getLang())+"" + (i + 1) + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005352,weaver.general.ThreadVarLanguage.getLang())+"" + this.getRelatedName(obj);
                        params.put("msg", flag.equals("true") ? msg + SystemEnv.getHtmlLabelName(25750, ThreadVarLanguage.getLang()) : msg + SystemEnv.getHtmlLabelName(24945, ThreadVarLanguage.getLang()));
                        params.put("status", flag.equals("true") ? "1" : "0");
                        importLogService.saveImportDeitalLog(params, user);
                    } else {
                        //验证出错返回错误信息
                        params = new HashMap<String, Object>();
                        params.put("pId", this.pId);
                        params.put("lineNum", (i + 1));
                        params.put("relatedName", this.getRelatedName(obj));
                        params.put("msg", importSourceName + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(15323,weaver.general.ThreadVarLanguage.getLang())+"" + (i + 1) + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005352,weaver.general.ThreadVarLanguage.getLang())+"" + valErrorInfo);
                        params.put("status", "0");
                        importLogService.saveImportDeitalLog(params, user);
                    }
                }
                if (this.importtype.equals("holidaySet")) {
                    //加载缓存
                    method = importSourceClass.getMethod("removeCache");
                    if (obj != null) method.invoke(obj);
                }
            }
            params = new HashMap<String, Object>();
            params.put("pId", this.pId);
            params.put("status", "over");
            importLogService.saveImportLogStatus(params, user);
        } catch (FileNotFoundException e) {
            params = new HashMap<String, Object>();
            params.put("pId", this.pId);
            params.put("status", "error");
            importLogService.saveImportLogStatus(params, user);
        } catch (IOException e) {
            this.errorInfo.add(SystemEnv.getHtmlLabelName(83618, userlanguage));
            writeLog(e);
            params = new HashMap<String, Object>();
            params.put("pId", this.pId);
            params.put("status", "error");
            importLogService.saveImportLogStatus(params, user);
        } catch (IndexOutOfBoundsException e) {
            this.errorInfo.add(SystemEnv.getHtmlLabelName(125871, userlanguage));
            writeLog(e);
            params = new HashMap<String, Object>();
            params.put("pId", this.pId);
            params.put("status", "error");
            importLogService.saveImportLogStatus(params, user);
        } catch (Exception e) {
            writeLog(e);
            this.errorInfo.add(SystemEnv.getHtmlLabelName(125871, userlanguage));
            params = new HashMap<String, Object>();
            params.put("pId", this.pId);
            params.put("status", "error");
            importLogService.saveImportLogStatus(params, user);
        }
        return this.errorInfo;
    }

    /**
     * 校验模板有效性
     *
     * @param importSourceFieldName 导入文件名称
     * @return
     */
    public String valExcelTemp(String importSourceFieldName) {
        String valExcelTempInfo = "";
        HSSFRow row = null;
        HSSFCell cell = null;
        String cellValue = "";
        try {
            String[] fieldname = importSourceFieldName.split(",");
            row = sheet.getRow(0);
            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                cell = row.getCell((short) cellIndex);
                if (cell == null) continue;
                cellValue = getCellValue(cell).trim();
                if (!fieldname[cellIndex].trim().equals(cellValue)) {
                    valExcelTempInfo = SystemEnv.getHtmlLabelName(387156, user.getLanguage());
                    break;
                }
            }
        } catch (Exception e) {
            writeLog(e);
            valExcelTempInfo = SystemEnv.getHtmlLabelName(387156, user.getLanguage()) + "1";
        }

        return valExcelTempInfo;
    }

    /**
     * 获取excel单元格值
     *
     * @param cell 要读取的单元格对象
     * @return
     */
    public String getCellValue(HSSFCell cell) {
        String cellValue = "";
        if (cell == null)
            return "";
        switch (cell.getCellType()) {
            case BOOLEAN: // 得到Boolean对象的方法
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 先看是否是日期格式
                    SimpleDateFormat sft = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = String.valueOf(sft.format(cell.getDateCellValue())); // 读取日期格式
                } else {
                    cellValue = String.valueOf(new Double(cell.getNumericCellValue())); // 读取数字
                    if (cellValue.endsWith(".0"))
                        cellValue = cellValue.substring(0, cellValue.indexOf("."));
                }
                break;
            case FORMULA: // 读取公式
                cellValue = cell.getCellFormula();
                break;
            case STRING: // 读取String
                cellValue = cell.getStringCellValue();
                break;
        }

        return cellValue;
    }

    /**
     * 读excel文件内容
     */
    public void readexcel() {
        try {
            HSSFRow row = null;
            HSSFCell cell = null;
            String cellValue = null;
            int rowNum = 0; // 行号
            int cellNum = 0; // 列号
            int firstRow = 1;
            lsImportSourceMap.clear();
            int lastRow = sheet.getLastRowNum();
            for (int i = firstRow; i <= lastRow; i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    errorInfo.add(SystemEnv.getHtmlLabelName(15323, userlanguage) + " " + (i + 1) + " " + SystemEnv.getHtmlLabelName(83622, userlanguage));
                    continue;
                }
                rowNum = row.getRowNum();

                Object obj = importSourceClass.newInstance();
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    cell = row.getCell((short) cellIndex);
                    if (cell == null) continue;
                    cellValue = getCellValue(cell).trim();
                    setImportSourceValue(cellIndex, cellValue, obj, null);
                }
                lsImportSourceMap.add(obj);
            }
        } catch (Exception e) {
            writeLog(e);
        }
    }

    /**
     * 通过反射为对象赋值
     *
     * @param cellNum
     * @param cellValue
     * @param importSourceObj
     * @param field
     */
    public void setImportSourceValue(int cellNum, String cellValue, Object importSourceObj, String field) {
        if (field == null) {
            String excelField = voFields[cellNum];
            String methodName = "set" + excelField.substring(0, 1).toUpperCase() + excelField.substring(1);
            Method method = (Method) parameterTypes.get(methodName);
            try {
                String fieldType = (String) fieldTypes.get(excelField);
                if (fieldType.equals("java.lang.String"))
                    method.invoke(importSourceObj, new Object[]{cellValue});
                else if (fieldType.equals("java.lang.Integer") && !cellValue.equals("")) {
                    method.invoke(importSourceObj, new Object[]{new Integer(Integer.parseInt(cellValue))});
                } else if (fieldType.equals("java.lang.Float") && !cellValue.equals(""))
                    method.invoke(importSourceObj, new Object[]{new Float(Float.parseFloat(cellValue))});
                else if (fieldType.equals("java.lang.Short") && !cellValue.equals("")) {
                    method.invoke(importSourceObj, new Object[]{new Short(Short.parseShort(cellValue))});
                } else if (fieldType.equals("double") && !cellValue.equals("")) {
                    method.invoke(importSourceObj, new Object[]{new Double(Double.parseDouble(cellValue))});
                }
            } catch (Exception e) {
                writeLog(e);
            }
        } else {
            String methodName = "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
            Method method = (Method) parameterTypes.get(methodName);
            try {
                method.invoke(importSourceObj, new Object[]{cellValue});
            } catch (Exception e) {
                writeLog(e);
            }
        }
    }

    /**
     * 初始化反射所需要的方法和字段Map，parameterTypes，fieldTypes
     */
    public void initReflectParam() {
        try {
            parameterTypes.clear();
            fieldTypes.clear();
            Method hrmResourceMethods[] = importSourceClass.getDeclaredMethods();
            for (int i = 0; i < hrmResourceMethods.length; i++) {
                parameterTypes.put(hrmResourceMethods[i].getName(), hrmResourceMethods[i]);
            }
            Field importSourceFields[] = importSourceClass.getDeclaredFields();
            for (int i = 0; i < importSourceFields.length; i++) {
                Class<?> fieldTypeClass = importSourceFields[i].getType();
                if (fieldTypeClass == Integer.TYPE) {
                    fieldTypeClass = Integer.class;
                }
                fieldTypes.put(importSourceFields[i].getName(), fieldTypeClass.getName());
            }
        } catch (Exception e) {
            writeLog(e);
        }
    }

    /**
     * 获取导入记录中的RelatedName
     *
     * @param obj
     * @return
     */
    private String getRelatedName(Object obj) {
        String relatedName = "";
        try {
            if (this.importtype.equals("holidaySet")) {
                Method method = importSourceClass.getMethod("getHolidayDate");
                relatedName = Util.null2String(method.invoke(obj));
            } else if (this.importtype.equals("balanceOfLeave")) {
                Method method = importSourceClass.getMethod("getLastName");
                relatedName = Util.null2String(method.invoke(obj));
            } else if (this.importtype.equals("kqScheduleCode")) {
                if (this.keyField.equals("lastname")) {
                    Method method = importSourceClass.getMethod("getLastname");
                    relatedName = Util.null2String(method.invoke(obj));
                } else if (this.keyField.equals("workcode")) {
                    Method method = importSourceClass.getMethod("getWorkcode");
                    relatedName = Util.null2String(method.invoke(obj));
                }
                Method method = importSourceClass.getMethod("getSchedulecode");
                relatedName += "|" + Util.null2String(method.invoke(obj));
            }

        } catch (Exception e) {
            writeLog(e);
        }
        return relatedName;
    }


    /**
     * 生成指定机构的指定的假期类型的指定年份下的员工假期余额的导入模板
     *
     * @param leaveRulesId     指定的假期类型ID(对应于数据库kq_leaveRules表的主键ID)
     * @param belongYear       指定的所属年份
     * @param organizationType 机构类型：0--总部、1--分部、2--部门
     * @param organizationIds  指定机构的ID(例如当机构类型为分部时，此变量代表分部ID)
     */
    public void createTempletOfLeave(String leaveRulesId, String belongYear, String organizationType, String organizationIds) {
        HSSFSheet sheet = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        String cellValue = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            sheet = workbook.createSheet();//获取页
            workbook.setSheetName(0, ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005353,weaver.general.ThreadVarLanguage.getLang())+"");

            LinkedHashMap<String, String> sheeetTitle = new LinkedHashMap<String, String>();
            sheeetTitle.put(""+weaver.systeminfo.SystemEnv.getHtmlLabelName(130664,weaver.general.ThreadVarLanguage.getLang())+"", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(33803,weaver.general.ThreadVarLanguage.getLang())+"");
            sheeetTitle.put("1", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(389749,weaver.general.ThreadVarLanguage.getLang())+"");
            sheeetTitle.put("2", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005354,weaver.general.ThreadVarLanguage.getLang())+"ID"+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005355,weaver.general.ThreadVarLanguage.getLang())+"");
            sheeetTitle.put("3", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005356,weaver.general.ThreadVarLanguage.getLang())+"0");
            sheeetTitle.put("4", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(509971,weaver.general.ThreadVarLanguage.getLang())+"");
            sheeetTitle.put("5", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005357,weaver.general.ThreadVarLanguage.getLang())+"yyyy，"+weaver.systeminfo.SystemEnv.getHtmlLabelName(18541,weaver.general.ThreadVarLanguage.getLang())+"2019");
            sheeetTitle.put("6", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005358,weaver.general.ThreadVarLanguage.getLang())+"MM，"+weaver.systeminfo.SystemEnv.getHtmlLabelName(18541,weaver.general.ThreadVarLanguage.getLang())+"08");
            sheeetTitle.put("7", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005359,weaver.general.ThreadVarLanguage.getLang())+"yyyy-MM-dd，"+weaver.systeminfo.SystemEnv.getHtmlLabelName(18541,weaver.general.ThreadVarLanguage.getLang())+"2019-08-01");

            int rowIndex = 0;
            Iterator<Map.Entry<String, String>> iterator = sheeetTitle.entrySet().iterator();
            while (iterator.hasNext()) {
                Map.Entry<String, String> entry = iterator.next();
                String key = entry.getKey();
                String value = Util.null2String(entry.getValue());
                row = sheet.createRow(rowIndex++);//获取行
                row.setHeight((short) 400);

                cell = row.createCell((short) 0);//获取单元格
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                HSSFFont font = workbook.createFont();
                font.setFontName("宋体");//设置字体
                font.setFontHeightInPoints((short) 12);//设置字体大小
                cellStyle.setFont(font);
                HSSFDataFormat format = workbook.createDataFormat();
                cellStyle.setDataFormat(format.getFormat("@"));//@为文本占位符
                cell.setCellStyle(cellStyle);//设置单元格样式
                cell.setCellType(CellType.STRING);//设置单元格类型
                cell.setCellValue(Util.formatMultiLang(key, "7"));//设置单元格的值

                cell = row.createCell((short) 1);//获取该行第二列的单元格
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(Util.formatMultiLang(value, "7"));
            }
            sheet.setColumnWidth((short) 0, (short) (2 * 1000));
            sheet.setColumnWidth((short) 1, (short) (40 * 1000));

            /**********************************************************************************************************/

            /*假期类型的相关设置*/
            KQLeaveRulesComInfo rulesComInfo = new KQLeaveRulesComInfo();

            /*假期类型的名称*/
            String leaveName = Util.formatMultiLang(rulesComInfo.getLeaveName(leaveRulesId), "" +weaver.general.ThreadVarLanguage.getLang());

            /*最小请假单位：1-按天请假、2-按半天请假、3-按小时请假、4-按整天请假*/
            int minimumUnit = Util.getIntValue(rulesComInfo.getMinimumUnit(leaveRulesId), 1);

            /*假期类型的最小请假单位名称*/
            String unitName = rulesComInfo.getUnitName(leaveRulesId, weaver.general.ThreadVarLanguage.getLang());//单位名称，天/小时

            /**********************************************************************************************************/

            /*假期规则(每个假期类型下可能存在多个假期规则)的相关设置*/
            KQLeaveRulesDetailComInfo detailComInfo = new KQLeaveRulesDetailComInfo();

            /*判断该假期类型是否属于 法定年假+福利年假 的类型(暂不支持一个请假类型下既存在"按入职时长+工龄自动发放"的余额发放方式，又存在其他发放方式)*/
            boolean isMixMode = KQLeaveRulesBiz.isMixMode(leaveRulesId);
            //判断该假期类型是否属于 调休
            boolean isTiaoXiu = KQLeaveRulesBiz.isTiaoXiu(leaveRulesId);

            /**********************************************************************************************************/

            sheet = workbook.createSheet();
            workbook.setSheetName(1, ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005360,weaver.general.ThreadVarLanguage.getLang())+"");
            row = sheet.createRow(0);
            row.setHeightInPoints((float) 15);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 12);//设置字体大小
            font.setBold(true);
            cellStyle.setFont(font);
            HSSFDataFormat format = workbook.createDataFormat();
            cellStyle.setDataFormat(format.getFormat("@"));//@为文本占位符
            cellStyle.setFillForegroundColor((short) 52);// 设置背景色
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setBorderBottom(BorderStyle.THIN);//下边框

            if (isMixMode) {
                String fieldNameStr = "ID("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(500228,weaver.general.ThreadVarLanguage.getLang())+"" + unitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389745,weaver.general.ThreadVarLanguage.getLang())+"" + unitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389743,weaver.general.ThreadVarLanguage.getLang())+"" + unitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(500229,weaver.general.ThreadVarLanguage.getLang())+"" + unitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389746,weaver.general.ThreadVarLanguage.getLang())+"" + unitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(389744,weaver.general.ThreadVarLanguage.getLang())+"" + unitName;
                String[] fieldNameArr = fieldNameStr.split(",");
                for (int i = 0; i < fieldNameArr.length; i++) {
                    cell = row.createCell((short) i);
                    cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(fieldNameArr[i]);

                    sheet.setColumnWidth((short) i, (short) (6 * 1000));
                }
            } else if (isTiaoXiu) {
                String fieldNameStr = "ID("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(15933,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(887,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(130286,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(26642,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(508169,weaver.general.ThreadVarLanguage.getLang())+","+weaver.systeminfo.SystemEnv.getHtmlLabelName(19547,weaver.general.ThreadVarLanguage.getLang())+"";
                String[] fieldNameArr = fieldNameStr.split(",");
                for (int i = 0; i < fieldNameArr.length; i++) {
                    cell = row.createCell((short) i);
                    cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(fieldNameArr[i]);

                    sheet.setColumnWidth((short) i, (short) (6 * 1000));
                }
            } else {
                String fieldNameStr = "ID("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+"),"+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+"("+weaver.systeminfo.SystemEnv.getHtmlLabelName(10005349,weaver.general.ThreadVarLanguage.getLang())+")," + leaveName + ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(17638,weaver.general.ThreadVarLanguage.getLang())+"" + unitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(130286,weaver.general.ThreadVarLanguage.getLang())+"" + leaveName + unitName + ","+weaver.systeminfo.SystemEnv.getHtmlLabelName(26642,weaver.general.ThreadVarLanguage.getLang())+"" + leaveName + unitName;
                String[] fieldNameArr = fieldNameStr.split(",");
                for (int i = 0; i < fieldNameArr.length; i++) {
                    cell = row.createCell((short) i);
                    cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(fieldNameArr[i]);

                    sheet.setColumnWidth((short) i, (short) (6 * 1000));
                }
            }

            /*插入模板数据*/
            RecordSet recordSet = new RecordSet();
            String sql = "select a.id,a.workCode,a.lastName,b.baseAmount,b.extraAmount,b.usedAmount,b.baseAmount2,b.extraAmount2,b.usedAmount2 " +
                    "from HrmResource a left join kq_balanceOfLeave b on a.id=b.resourceId " +
                    "and b.belongYear='" + belongYear + "' and b.leaveRulesId=" + leaveRulesId + " where a.status in (0,1,2,3) ";
            if (isTiaoXiu) {
                sql = "select a.id,a.workCode,a.lastName,'' as baseAmount,'' as extraAmount,'' as usedAmount,'' as baseAmount2,'' as extraAmount2,'' as usedAmount2 " +
                        " from HrmResource a where a.status in (0,1,2,3) ";
            }
            if (organizationType.equals("1")) {
                sql += " and a.subCompanyId1 in (" + organizationIds + ") ";
            } else if (organizationType.equals("2")) {
                sql += " and a.departmentId in (" + organizationIds + ") ";
            } else if (organizationType.equals("3")) {
                sql += " and ("+ Tools.getOracleSQLIn(organizationIds,"a.id")+")";
            }
            sql += " order by dspOrder,lastName ";
            recordSet.executeQuery(sql);

            cellStyle = workbook.createCellStyle();
            font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);//设置字体大小
            font.setColor((short) 55);
            cellStyle.setFont(font);
            format = workbook.createDataFormat();
            cellStyle.setDataFormat(format.getFormat("@"));//@为文本占位符

            HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
            font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);//设置字体大小
            hssfCellStyle.setFont(font);
            format = workbook.createDataFormat();
            hssfCellStyle.setDataFormat(format.getFormat("@"));//@为文本占位符

            rowIndex = 1;
            while (recordSet.next()) {
                int cellIndex = 0;
                String id = recordSet.getString("id");
                String lastName = recordSet.getString("lastName");
                String workcode = recordSet.getString("workcode");

                row = sheet.createRow(rowIndex++);
                row.setHeightInPoints(13);
                cell = row.createCell((short) cellIndex++);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(id);

                cell = row.createCell((short) cellIndex++);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(Util.formatMultiLang(workcode, "7"));

                cell = row.createCell((short) cellIndex++);
                cell.setCellStyle(cellStyle);
                cell.setCellType(CellType.STRING);
                cell.setCellValue(Util.formatMultiLang(lastName, "7"));

                if (isMixMode) {
                    String baseAmount = recordSet.getString("baseAmount");
                    String extraAmount = recordSet.getString("extraAmount");
                    String usedAmount = recordSet.getString("usedAmount");
                    String baseAmount2 = recordSet.getString("baseAmount2");
                    String extraAmount2 = recordSet.getString("extraAmount2");
                    String usedAmount2 = recordSet.getString("usedAmount2");

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(baseAmount);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(extraAmount);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(usedAmount);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(baseAmount2);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(extraAmount2);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(usedAmount2);
                } else if (isTiaoXiu) {
                    //调休的导入和其他假期类型的不太一样，调休的导入均为新增。
                    String belongMonth = "";
                    String totalAmount = "";
                    String usedAmount = "";
                    String effectiveDate = "";
                    String expirationDate = "";

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellStyle(hssfCellStyle);
                    cell.setCellValue(belongYear);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellStyle(hssfCellStyle);
                    cell.setCellValue(belongMonth);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellStyle(hssfCellStyle);
                    cell.setCellValue(totalAmount);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellStyle(hssfCellStyle);
                    cell.setCellValue(usedAmount);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellStyle(hssfCellStyle);
                    cell.setCellValue(effectiveDate);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellStyle(hssfCellStyle);
                    cell.setCellValue(expirationDate);
                } else {
                    String baseAmount = recordSet.getString("baseAmount");
                    String extraAmount = recordSet.getString("extraAmount");
                    String usedAmount = recordSet.getString("usedAmount");

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(baseAmount);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(extraAmount);

                    cell = row.createCell((short) cellIndex++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(usedAmount);
                }
            }

            String filePath = GCONST.getRootPath() + "hrm" + File.separator + "import" + File.separator + "templet" + File.separator + "KQBalanceOfLeave.xls";
            FileOutputStream fileOut = new FileOutputStream(filePath);
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
