package com.engine.payroll.process;

import com.engine.common.constant.BizLogSmallType4Hrm;
import com.engine.common.util.ParamUtil;
import com.engine.common.util.ServiceUtil;
import com.engine.hrm.service.ImportLogService;
import com.engine.hrm.service.impl.ImportLogServiceImpl;
import com.engine.payroll.util.PayrollUtil;
import net.sf.json.JSONArray;
import org.apache.poi.ss.usermodel.*;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.file.AESCoder;
import weaver.file.FileManage;
import weaver.file.FileUploadToPath;
import weaver.file.ImageFileManager;
import weaver.general.BaseBean;
import weaver.general.Util;
import weaver.hrm.User;
import weaver.hrm.common.database.dialect.DialectUtil;
import weaver.hrm.resource.ResourceComInfo;
import weaver.systeminfo.SystemEnv;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ImportProcess extends BaseBean {
    public ImportProcess(User user, String targetId) {
        this.user = user;
        this.targetId = targetId;
    }

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private User user;
    private String targetId;
    private String title;
    private String templateId;
    private String keyField;
    private int keyFieldIndex = 0;
    private String excelfile;
    private Sheet sheet;
    private Map<String,Object> error = null;
    private List<Map<String, Object>> errorInfo = new ArrayList<>();

    private Map<String, String> keys = new HashMap<>();

    //标准模板字段名称
    private List<String> tempFieldLabel = new ArrayList<>(Arrays.asList(""+weaver.systeminfo.SystemEnv.getHtmlLabelName(16126,weaver.general.ThreadVarLanguage.getLang())+"", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(413,weaver.general.ThreadVarLanguage.getLang())+"", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(714,weaver.general.ThreadVarLanguage.getLang())+"", ""+weaver.systeminfo.SystemEnv.getHtmlLabelName(10003253,weaver.general.ThreadVarLanguage.getLang())+"id"));
    private List<String> tempField = new ArrayList<>(Arrays.asList("loginid", "lastname", "workcode"));
    //列序号
//    private List<Integer> fieldIndex = new ArrayList<>();
    private Map<Integer, String> columns = new LinkedHashMap<>();

    //需要导入的数据
    private Map<String, List<List<String>>> resourceData = new LinkedHashMap<>();

    int processId = 0;

    /**
     * map集合创建类
     *
     * @param fu 上传参数
     * @return List
     */
    public List creatImportMap(FileUploadToPath fu) {
        try {
            // 初始化数据
            initDataSource(fu);

            // 模板验证
            valExcelTemp();

            // 读取数据并验证
            readExcel();

        } catch (NegativeArraySizeException ex) {
            deleteFile();
            errorInfo.add(generateErrMsg(SystemEnv.getHtmlLabelName(83615, user.getLanguage())));//目前尚不清楚，这种异常的产生原因
            writeLog(ex);
        } catch (Exception e) {
            deleteFile();
            e.printStackTrace();
            errorInfo.add(generateErrMsg(SystemEnv.getHtmlLabelName(512692, user.getLanguage())));//Excel导入错误，请阅读注意事项并检查模板文件
            writeLog(e);
        }

        return errorInfo;
    }

    private Map<String, Object> generateErrMsg(String msg){
        error = new HashMap<String,Object>();
        error.put("message",msg);
        return error;
    }

    /**
     * 删除文件
     */
    public void deleteFile() {
        try {
            FileManage.DeleteFile(excelfile);
        } catch (Exception ex) {
            writeLog(ex);
        }
    }

    /**
     * 获取上传文件，初始化参数
     *
     * @param fu
     * @return
     */
    private void initDataSource(FileUploadToPath fu) throws Exception {
        title = fu.getParameter("title");
        templateId = fu.getParameter("templateId");
        keyField = fu.getParameter("keyField");
        keyFieldIndex = tempField.indexOf(keyField);
        excelfile = fu.getParameter("excelFile");

        try {
            ImageFileManager manager = new ImageFileManager();
            manager.getImageFileInfoById(Util.getIntValue(excelfile));
//            File file = new File("/Users/eugene/Downloads/payroll.xlsx");
            Workbook workbook = WorkbookFactory.create(manager.getInputStream());
//            Workbook workbook = WorkbookFactory.create(file);
            sheet = workbook.getSheetAt(1);
        } catch (FileNotFoundException e) {
        } catch (IOException e) {
            errorInfo.add(generateErrMsg(SystemEnv.getHtmlLabelName(83618, user.getLanguage())));    //上传文件不是excel文件
            writeLog(e);
            throw e;
        } catch (IndexOutOfBoundsException e) {
            errorInfo.add(generateErrMsg(SystemEnv.getHtmlLabelName(512693, user.getLanguage())));
            writeLog(e);
            throw e;
        }
    }

    /**
     * 验证模板格式
     *
     * @return
     */
    private void valExcelTemp() throws Exception {
    }

    /**
     * 读取excel数据
     *
     * @return
     */
    private void readExcel() throws Exception {
        Row row;
        Cell cell;
        String cellValue;
        int wechatIdIdx = -1;

        try {
            row = sheet.getRow(0);
            List<String> header = new ArrayList<>();
            for(int i = 0; i < row.getLastCellNum(); i++){
                cell = row.getCell(i);
                cellValue = getCellValue(cell);
                if(!tempFieldLabel.contains(cellValue)){
                    columns.put(i, cellValue);
                    header.add(cellValue);
                }
                if(cellValue.equals(tempFieldLabel.get(3)))
                    wechatIdIdx = i;
            }

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);

                if (row == null)
                    continue;

                String wechatId = "";
                if(wechatIdIdx > -1){
                    cell = row.getCell(wechatIdIdx);
                    wechatId = getCellValue(cell);
                }

                cell = row.getCell(keyFieldIndex);
                if(cell == null && wechatId.equals("")){
                    errorInfo.add(generateErrMsg(getCellPosition(keyFieldIndex, i + 1) + SystemEnv.getHtmlLabelName(512694, user.getLanguage())));
                    continue;
                }

                cellValue = getCellValue(cell);
                List<String> resourceIds = !"".equals(cellValue) ? checkKeyField(cellValue) : null;
                if((resourceIds == null || resourceIds.size() == 0) && wechatId.equals("")){
                    errorInfo.add(generateErrMsg(getCellPosition(keyFieldIndex, i + 1) + SystemEnv.getHtmlLabelName(512694, user.getLanguage())));
                    continue;
                }
                if(resourceIds != null && resourceIds.size() > 1){
                    errorInfo.add(generateErrMsg(getCellPosition(keyFieldIndex, i + 1) + SystemEnv.getHtmlLabelName(131555, user.getLanguage())));
                    continue;
                }

                String rid = "";
                if(resourceIds != null && resourceIds.size() == 1) {
                    rid = resourceIds.get(0);
                }else
                    rid = wechatId +  ":wechatid";

                if(!resourceData.containsKey(rid)){
                    resourceData.put(rid, new ArrayList<>());
                }
                List<List<String>> datas = resourceData.get(rid);
                if(datas.size() == 0)
                    datas.add(header);

                List<String> data = new ArrayList<>();
                Iterator< Map.Entry<Integer, String>> iterator = columns.entrySet().iterator();
                while (iterator.hasNext()){
                    Map.Entry<Integer, String> entry = iterator.next();
                    Integer dataIndex = entry.getKey();

                    cell = row.getCell(dataIndex);
                    if(cell == null){
                        data.add("");
                    }else{
                        cellValue = getCellValue(cell);
                        data.add(cellValue);
                    }
                }
                datas.add(data);
            }
        } catch (Exception ex) {
            writeLog(ex);
            throw ex;
        }
    }

    private List<String> checkKeyField(String checkValue){
        List<String> resourceId = new ArrayList<>();
        try{
            RecordSet rs = new RecordSet();
            boolean isOralce = (rs.getDBType().toLowerCase()).equals("oracle");
            List<String> list = new ArrayList<>(Arrays.asList("loginid", "lastname", "workcode"));

            String sql = " select id, loginid, lastname from hrmresource where 1 = 1 and status in(0,1,2,3) ";
            if(list.contains(keyField)) {
                if(Util.isEnableMultiLang() && keyField.equals("lastname")){
                    if(isOralce || DialectUtil.isMySql()){
                        sql += " and convToMultiLang(lastname," + user.getLanguage() + ") = ?";
                    }else{
                        sql += " and dbo.convToMultiLang(lastname," + user.getLanguage() + ") = ?";
                    }
                }else{
                    sql += " and " + keyField + " = ?";
                }

                rs.executeQuery(sql, checkValue);
                while(rs.next()){
                    String loginid = Util.null2String(rs.getString("loginid"));
                    String id = rs.getString("id");
                    if(!"".equals(loginid))
                        resourceId.add(id);
                    else
                        resourceId.add(id +  ":userId");
                }
            }
        }catch (Exception ex){
            writeLog(ex);
        }
        return resourceId;
    }

    /**
     * 导入数据到数据库
     *
     * @return
     */
    public Set<String> processMap() {
        Set<String> userList = new HashSet<>();
        keys = new HashMap<>();

        ImportLogService importLogService = (ImportLogService) ServiceUtil.getService(ImportLogServiceImpl.class, this.user);
        try {
            String modifyDate = sdf.format(new Date());

            RecordSetTrans rst = new RecordSetTrans();
            rst.setAutoCommit(false);
            String sql = "insert into hrm_payroll_log(id, title, creator, targetId, sendDate) values(?, ?, ?, ?, ?)";
            rst.executeUpdate(sql, PayrollUtil.getInstance().getUnquieID(), title, user.getUID(), targetId, modifyDate);
            boolean flag = rst.commit();

            ResourceComInfo rci = new ResourceComInfo();

            if(flag) {
                Iterator<Map.Entry<String, List<List<String>>>> iterator = resourceData.entrySet().iterator();
                int rownum = 0;
                while (iterator.hasNext()) {
                    rownum++;

                    rst = new RecordSetTrans();
                    rst.setAutoCommit(false);

                    Map.Entry<String, List<List<String>>> entry = iterator.next();

                    String resourceId = entry.getKey();
                    userList.add("" + resourceId);

                    String key = PayrollUtil.getInstance().getUnquieID();
                    keys.put(resourceId, key);

                    String lastname = "";
                    if(resourceId.indexOf(":wechatid") > -1)
                        lastname = resourceId;
                    else if(resourceId.indexOf(":userId") > -1) {
                        String tmpid = resourceId.replace(":userId", "");
                        lastname = rci.getLastname(tmpid);
                    }else
                        lastname = rci.getLastname("" + resourceId);

                    List<List<String>> horizontalDatas = entry.getValue();
                    List<List<String>> verticalDatas = transfer(horizontalDatas);
                    String horizontalDataString = JSONArray.fromObject(horizontalDatas).toString();
                    horizontalDataString = AESCoder.encrypt(horizontalDataString, key);
                    String verticalDatasString = JSONArray.fromObject(verticalDatas).toString();
                    verticalDatasString = AESCoder.encrypt(verticalDatasString, key);

                    sql = "insert into hrm_payroll_logdetail(id, title, templateId, resourceId, horizontalData, verticalData, targetId, status, readed, sendDate, dsporder) values(?,?,?,?,?,?,?,?,?,?,?)";
                    rst.executeUpdate(sql, PayrollUtil.getInstance().getUnquieID(), title, templateId, resourceId, horizontalDataString, verticalDatasString, targetId, "1", "0", modifyDate, rownum);
                    flag = rst.commit();

                    if(!flag)
                        errorInfo.add(generateErrMsg(lastname + SystemEnv.getHtmlLabelName(512695, user.getLanguage())));

                    Map<String, Object> params = new HashMap<String, Object>();
                    params.put("pId", processId);
                    params.put("lineNum", rownum);
                    params.put("relatedName", lastname);
                    if(flag) {
                        params.put("status", "1");
                        params.put("msg", lastname);
                    }else {
                        params.put("status", "0");
                        params.put("msg", lastname);
                    }
                    params.put("logSmallType", BizLogSmallType4Hrm.HRM_ENGINE_SEND_PAYROLL.getCode());
                    importLogService.saveImportDeitalLog(params, user);
                }
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            writeLog(e);
        } finally {
            return userList;
        }
    }

    /**
     * 数据行列转换
     * @param data
     */
    private List<List<String>> transfer(List<List<String>> data){
        int size = data.get(0).size();
        List<List<String>> newList = new ArrayList<>();
        List<String> row = null;

        for(int i = 0; i < size; i++){
            row = new ArrayList<>();
            for(List<String> list : data){
                row.add(list.get(i));
            }
            newList.add(row);
        }
        return newList;
    }
    /**
     * 获取excel单元格值
     *
     * @param cell 要读取的单元格对象
     * @return
     */
    private String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null)
            return "";
        switch (cell.getCellType()) {
            case BOOLEAN://得到Boolean对象的方法
                cellValue = Util.null2String(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {//先看是否是日期格式
                    SimpleDateFormat sft = new SimpleDateFormat("yyyy-MM");
                    cellValue = sft.format(cell.getDateCellValue());   //读取日期格式
                } else {
                    cellValue = Util.null2String(new Double(cell.getNumericCellValue())); //读取数字
                    if (cellValue.endsWith(".0"))
                        cellValue = cellValue.substring(0, cellValue.indexOf("."));
                    cellValue = Util.round(cellValue, 2);
                }
                break;
            case FORMULA: // 读取公式
                cellValue = Util.null2String(cell.getNumericCellValue());
                cellValue = Util.round(cellValue, 2);
                break;
            case STRING: // 读取String
                cellValue = cell.getStringCellValue();
                break;
        }
        cellValue = Util.toHtmlForHrm(cellValue);
        return cellValue;
    }

    /**
     * excel单元格位置转换
     *
     * @param cellIndex 列号
     * @param rowNum    行号
     * @return
     */
    private String getCellPosition(int cellIndex, int rowNum) {

        int count = cellIndex / 26;
        String cellChar = String.valueOf((char) (cellIndex % 26 + 65));
        String cellPosition = "";

        if (count != 0)
            cellPosition = String.valueOf((char) ((count - 1) + 65)) + cellChar;
        else
            cellPosition = cellChar;
        cellPosition += rowNum;
        return cellPosition;
    }

    /**
     * 验证单元格内容是否为日期
     *
     * @param str
     * @return
     */
    private Date validDate(String str) {
        Date d = null;
        SimpleDateFormat fmt1 = new SimpleDateFormat("yyyy-MM-dd");
        fmt1.setLenient(false);
        SimpleDateFormat fmt2 = new SimpleDateFormat("yyyy/MM/dd");
        fmt2.setLenient(false);
        try {
            d = fmt1.parse(str);
        } catch (Exception e) {
        }
        try {
            d = fmt2.parse(str);
        } catch (Exception e) {

        }
        return d;
    }

    /**
     * 设置session属性，匹配监控进程
     * @param request
     * @return
     */
    public Map<String, Object> init(HttpServletRequest request){
        Map<String, Object> retmap = new HashMap<String, Object>();
        try {
            ImportLogService importLogService = (ImportLogService) ServiceUtil.getService(ImportLogServiceImpl.class, user);
            Map<String, Object> params = ParamUtil.request2Map(request);
            retmap = importLogService.saveImportLog(params, request, user);
            if(Util.null2String(retmap.get("status")).equals("1")){
                String importType = "payroll";
                processId = Util.getIntValue(Util.null2String(retmap.get("pId")));
                String processName = "hrm"+importType+"PId";
                request.getSession(true).setAttribute(processName, processId);
            }
        } catch(Exception e) {
            writeLog(e) ;
        }
        return retmap;
    }

    /**
     * 导入结束后更新日志状态，写入over状态，前端就不再轮询日志
     * @param request
     */
    public void over(HttpServletRequest request){
        try {
            ImportLogService importLogService = (ImportLogServiceImpl) ServiceUtil.getService(ImportLogServiceImpl.class, user);
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("pId", processId);
            params.put("status", "over");
            importLogService.saveImportLogStatus(params, user);

            deleteFile();
        } catch(Exception e) {
            writeLog(e) ;
        }
    }

    private boolean executeSql(String sql, Object[] params){
        boolean flag = false;
        try{
            flag = new RecordSet().executeUpdate(sql, params);
        }catch (Exception ex){
            writeLog(ex);
        }
        return flag;
    }

    public Map<String, String> getKeys() {
        return keys;
    }

    public void setKeys(Map<String, String> keys) {
        this.keys = keys;
    }
}
