package com.engine.kq.cmd.shiftschedule;

import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.biz.SimpleBizLogger;
import com.engine.common.constant.BizLogSmallType4Hrm;
import com.engine.common.constant.BizLogType;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import com.engine.kq.biz.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import weaver.conn.BatchRecordSet;
import weaver.conn.RecordSet;
import weaver.file.ImageFileManager;
import weaver.general.Util;
import weaver.hrm.HrmUserVarify;
import weaver.hrm.Reminder.KQAutoCardTask;
import weaver.hrm.User;
import weaver.hrm.resource.ResourceComInfo;
import weaver.systeminfo.SystemEnv;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ImportExcelCmd extends AbstractCommonCommand<Map<String, Object>> {
  private String today = weaver.common.DateUtil.getCurrentDate();
  private SimpleBizLogger logger;
  public ImportExcelCmd(Map<String, Object> params, User user) {
    this.user = user;
    this.params = params;
    this.logger = new SimpleBizLogger();

    String groupId = Util.null2String(params.get("groupId"));
    BizLogContext bizLogContext = new BizLogContext();
    bizLogContext.setLogType(BizLogType.HRM_ENGINE);//模块类型
    bizLogContext.setBelongType(BizLogSmallType4Hrm.HRM_ENGINE_Schedule_Set);//所属大类型
    bizLogContext.setLogSmallType(BizLogSmallType4Hrm.HRM_ENGINE_Schedule_Set);//当前小类型
    bizLogContext.setParams(params);//当前request请求参数
    logger.setUser(user);//当前操作人
    String mainSql = "select * from kq_group where id = " + groupId + "  and (isdelete is null or isdelete <> '1') ";
    logger.setMainSql(mainSql, "id");//主表sql
    logger.setMainPrimarykey("id");//主日志表唯一key
    logger.setMainTargetNameColumn("groupname");
    SimpleBizLogger.SubLogInfo subLogInfo1 = logger.getNewSubLogInfo();
    String subSql1 =  "select * from kq_shiftschedule where groupid = " + groupId + "  and (isdelete is null or isdelete <> '1') ";
    subLogInfo1.setSubSql(subSql1,"id");
    logger.addSubLogInfo(subLogInfo1);
    logger.before(bizLogContext);
  }

  @Override
  public Map<String, Object> execute(CommandContext commandContext) {
    Map<String, Object> retmap = new HashMap<String, Object>();
    RecordSet rs = new RecordSet();
    BatchRecordSet bRs = new BatchRecordSet();
    String sql = "";
    try {
      //必要的权限判断
      if (!HrmUserVarify.checkUserRight("HrmKQGroup:Add", user)) {
        retmap.put("status", "-1");
        retmap.put("message", SystemEnv.getHtmlLabelName(2012, user.getLanguage()));
        return retmap;
      }
      ResourceComInfo resourceComInfo = new ResourceComInfo();
      KQGroupComInfo kqGroupComInfo = new KQGroupComInfo();
      KQGroupBiz kqGroupBiz = new KQGroupBiz();
      KQShiftManagementComInfo kqShiftManagementComInfo = new KQShiftManagementComInfo();

      String groupId = Util.null2String(params.get("groupId"));
      String filename = Util.null2String(params.get("filename"));

      List lsColumn = null;
      List<List<Object>> paramInsert = new ArrayList<List<Object>>();
      List<List<Object>> paramUpdate = new ArrayList<List<Object>>();
      List<Object> param = null;
      List<List<Object>> lsFormatParams = new ArrayList<>();
      List<Object> formatParams = null;

      List<List<Object>> lscheckSerialParams = new ArrayList<>();
      List<Object> checkSerialParams = null;

      Map<String,Object> mapShiftSchedule = new HashMap<>();
      Map<String,String> mapSerial = new HashMap<>();
      Map<String,String> mapGroupMembersWorkCode = new HashMap<>();
      Map<String,String> mapGroupMembersLastName = new HashMap<>();
      List<String> serialids=Util.splitString2List(kqGroupComInfo.getSerialids(groupId),",");
      if(!serialids.contains("-1"))serialids.add("-1");
      for(String serialid : serialids){
        if(serialid.equals("-1")){
          mapSerial.put(SystemEnv.getHtmlLabelName(26593, user.getLanguage()),serialid);
        }else{
          mapSerial.put(Util.formatMultiLang(kqShiftManagementComInfo.getSerial(serialid),""+user.getLanguage()),serialid);
        }
      }

      Map<String,Object> params = new HashMap<>();
      params.put("groupId",groupId);
      params.put("isNoAccount","1");
      List<String> kqGroupMembers = kqGroupBiz.getGroupMembers(params);
	  List<String> excludeidList = new ArrayList<>();
      if(!Util.null2String(kqGroupComInfo.getExcludecount(groupId)).equals("1")) {
        String excludeid = Util.null2String(kqGroupComInfo.getExcludeid(groupId));
        if (excludeid.length() > 0) {
          excludeidList = Util.splitString2List(excludeid,",");
        }
      }
      for(String resourceid : kqGroupMembers){
		  // 排除无需考勤人员
        if(excludeidList != null && excludeidList.contains(resourceid)) {
          continue;
        }
        String workCode = Util.formatMultiLang(Util.null2String(resourceComInfo.getWorkcode(resourceid)),""+user.getLanguage());
        String lastName = Util.formatMultiLang(Util.null2String(resourceComInfo.getLastname(resourceid)),""+user.getLanguage());
        if(workCode.length()>0){
          mapGroupMembersWorkCode.put(workCode,resourceid);
        }else if(lastName.length()>0){
          mapGroupMembersLastName.put(lastName,resourceid);
        }
      }

      sql = " select id, resourceid, kqdate from kq_shiftschedule where groupid=? ";
      rs.executeQuery(sql,groupId);
      while(rs.next()){
        mapShiftSchedule.put(rs.getString("kqdate")+"|"+rs.getString("resourceid"),rs.getString("id"));
      }

      int firstRow = 6;
      int firstCol = 3;
      ImageFileManager manager = new ImageFileManager();
      manager.getImageFileInfoById(Util.getIntValue(filename));
      //Workbook workbook = WorkbookFactory.create(manager.getInputStream());
      Workbook workbook = new XSSFWorkbook(manager.getInputStream());
      for (int idx = 0; idx < workbook.getNumberOfSheets(); idx++) {
        Sheet sheet = workbook.getSheetAt(idx);
        if(sheet.getSheetName().indexOf("constraintDataSheet")>-1)continue;
        Row row = null;
        Cell cell = null;

        row = sheet.getRow(firstRow);
        lsColumn = new ArrayList();
        for (int cellIndex = firstCol; cellIndex < row.getLastCellNum(); cellIndex++) {
          cell = row.getCell((short) cellIndex);
          lsColumn.add(Util.null2String(getCellValue(cell)).trim());
        }
        firstRow++;

        for (int i = firstRow; firstRow<=sheet.getLastRowNum() && i <= sheet.getLastRowNum(); i++) {
          row = sheet.getRow(i);
          if (row == null) {
            continue;
          }
          String workCode = Util.null2String(row.getCell(1));//编号
          String lastName = Util.null2String(row.getCell(2));//姓名
          if(workCode.length()==0 && lastName.length()==0){
            continue;
          }
          String resourceId = Util.null2String(mapGroupMembersWorkCode.get(workCode));//人员id
          if(resourceId.length()==0){
            resourceId = Util.null2String(mapGroupMembersLastName.get(lastName));//人员id
          }
          if(resourceId.length()==0)continue;
          for (int cellIndex = firstCol; cellIndex < row.getLastCellNum(); cellIndex++) {
            cell = row.getCell((short) cellIndex);
            if (cell == null) continue;
            String serialId = "";
            String serialName = Util.null2String(getCellValue(cell)).trim();
            if(serialName.length()==0)continue;
            String kqdate = Util.null2String(lsColumn.get(cellIndex-firstCol));
            //if(weaver.common.DateUtil.timeInterval(kqdate,today)>0)continue;//今天之前的无需处理
            serialId =  Util.null2String(mapSerial.get(serialName));
            if(serialId.length()==0)continue;

            String id =  Util.null2String(mapShiftSchedule.get(kqdate+"|"+resourceId));
            if(id.length()>0){
              param = new ArrayList<Object>();
              param.add(serialId);
              param.add(id);
              paramUpdate.add(param);
            }else{
              param = new ArrayList<Object>();
              param.add(kqdate);
              param.add(serialId);
              param.add(resourceId);
              param.add(groupId);
              paramInsert.add(param);
            }

            formatParams = new ArrayList<>();
            formatParams.add(resourceId);
            formatParams.add(kqdate);
            lsFormatParams.add(formatParams);

            checkSerialParams = new ArrayList<>();
            checkSerialParams.add(resourceId);
            checkSerialParams.add(kqdate);
            checkSerialParams.add(serialId);
            lscheckSerialParams.add(checkSerialParams);
          }
        }
        sql = " update kq_shiftschedule set serialid=?, isDelete=0 where id = ? ";
        for (int i = 0; paramUpdate != null && i < paramUpdate.size(); i++) {
          List<Object> update_params = paramUpdate.get(i);
          String serialid = Util.null2String(update_params.get(0));
          String id = Util.null2String(update_params.get(1));
          rs.executeUpdate(sql, serialid,id);
        }

        sql = "insert into kq_shiftschedule (kqdate,serialid,resourceid,groupid,isDelete) values(?,?,?,?,0)";
        for (int i = 0; paramInsert != null && i < paramInsert.size(); i++) {
          List<Object> insert_params = paramInsert.get(i);
          String kqdate = Util.null2String(insert_params.get(0));
          String serialid = Util.null2String(insert_params.get(1));
          String resourceid = Util.null2String(insert_params.get(2));
          String groupid = Util.null2String(insert_params.get(3));
          rs.executeUpdate(sql, kqdate,serialid,resourceid,groupid);
        }
      }

      //删除之前的排的班次，一天只能有一个班次
      sql = " update kq_shiftschedule set isdelete =1 where resourceid = ? and kqdate = ? and groupid != "+groupId;
      for (int i = 0; lsFormatParams != null && i < lsFormatParams.size(); i++) {
        List<Object> delete_params = lsFormatParams.get(i);
        String resourceid = Util.null2String(delete_params.get(0));
        String kqdate = Util.null2String(delete_params.get(1));
        rs.executeUpdate(sql, resourceid,kqdate);
      }

      sql = " update kq_shiftschedule set isdelete =1 where resourceid = ? and kqdate = ? and serialid != ? ";
      for (int i = 0; lscheckSerialParams != null && i < lscheckSerialParams.size(); i++) {
        List<Object> check_params = lscheckSerialParams.get(i);
        String resourceid = Util.null2String(check_params.get(0));
        String kqdate = Util.null2String(check_params.get(1));
        String serialid = Util.null2String(check_params.get(2));
        rs.executeUpdate(sql, resourceid,kqdate,serialid);
      }

      new KQShiftScheduleComInfo().removeCache();

      //刷新报表数据
      sql = " insert into kq_format_pool (resourceid, kqdate) values (?,?)";
      bRs.executeBatchSql(sql, lsFormatParams);

      KQGroupComInfo kQGroupComInfo = new KQGroupComInfo();
      String auto_checkout = kQGroupComInfo.getAuto_checkout(groupId);
      String auto_checkin = kQGroupComInfo.getAuto_checkin(groupId);
      if("1".equalsIgnoreCase(auto_checkin) || "1".equalsIgnoreCase(auto_checkout)){
        //如果开启了自动打卡，保存考勤组成员之后需要格式化下缓存
        KQAutoCardTask kqAutoCardTask = new KQAutoCardTask();
        kqAutoCardTask.initAutoCardTask(groupId);
      }

      retmap.put("status", "1");
    } catch (Exception e) {
      retmap.put("status", "-1");
      retmap.put("message", SystemEnv.getHtmlLabelName(382661, user.getLanguage()));
      writeLog(e);
    }
    return retmap;
  }

  @Override
  public BizLogContext getLogContext() {
    return null;
  }

  @Override
  public List<BizLogContext> getLogContexts() {
    return logger.getBizLogContexts();
  }
  /**
   * /**
   * 获取excel单元格值
   *
   * @param cell 要读取的单元格对象
   * @return
   */
  public String getCellValue(Cell cell) {
    String cellValue = "";
    if (cell == null)
      return "";
    switch (cell.getCellType()) {
      case BOOLEAN: // 得到Boolean对象的方法
        cellValue = String.valueOf(cell.getBooleanCellValue());
        break;
      case NUMERIC:
        if (DateUtil.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;
  }
}
