package com.engine.kq.cmd.shiftschedule;

import cn.hutool.core.exceptions.ExceptionUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.api.customization.kq.util.CustomLeaveUtil;
import com.api.customization.kq.util.KqCustomFlowData;
import com.api.customization.kq.util.KqMembersValidateUtil;
import com.api.customization.qc3189074.util.CustomScheduleUtil;
import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.biz.SimpleBizLogger;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import com.engine.kq.bean.KqShiftscheduleOperateLog;
import com.engine.kq.biz.*;
import com.engine.kq.log.KQLog;
import org.apache.commons.lang3.StringUtils;
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.BaseBean;
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.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
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 KQLog kqLog = new KQLog();


  // 添加日志功能
  private  List<List<Object>> operateLogParams = new ArrayList<>();
  // 操作类型  操作人  考勤组名称+id  被修改人  被修改班次日期  被修改班次+id  排班设置行id
  private List<Object> operateLogParam = null;

  public ImportExcelCmd(Map<String, Object> params, User user) {
    this.user = user;
    this.params = params;
    this.logger = new SimpleBizLogger();
  }

  @Override
  public Map<String, Object> execute(CommandContext commandContext) {
     String openKqGroupId = new BaseBean().getPropValue("qc3189074","openKqGroupId");
    CustomScheduleUtil customScheduleUtil = new CustomScheduleUtil();
    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"));
      List<String> managerRoleUserIdList = new KqShiftScheduleRoleBiz().getUfKqShiftScheduleRoleValue(""+user.getUID(), groupId);

      String filename = Util.null2String(params.get("filename"));
      String isEdit = Util.null2String(params.get("isEdit"));//是否来自编辑页面
      String kqMonth = Util.null2String(params.get("kqMonth"));//导入月份
      String monthBeginDate = weaver.common.DateUtil.getFirstDayOfMonth(kqMonth+"-01");
      String monthEndDate = weaver.common.DateUtil.getLastDayOfMonth(kqMonth+"-01");

      String newlogic = new BaseBean().getPropValue("qc3560103","newlogic");//新逻辑
      String oldlogic = new BaseBean().getPropValue("qc3560103","oldlogic");//旧逻辑
      String isline = new BaseBean().getPropValue("qc3560103","isline");//换行
      String warnMessage = "";

      Map<String, String> validateFromData = new HashMap<>();
      KqMembersValidateUtil kqMembersValidateUtil = new KqMembersValidateUtil();

      if ("1".equals(oldlogic)){
        //获取当前考勤组人员开始生效日期
         validateFromData = kqMembersValidateUtil.getValidateFromData(groupId, user);
      }

      KQTimesArrayComInfo kqTimesArrayComInfo = new KQTimesArrayComInfo();

      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>> delValidateParams = new ArrayList<>();
      List<Object> validateParams = null;

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

      //获取当前日期和时间和操作者
      LocalDateTime now = LocalDateTime.now();
      DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
      DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("HH:mm:ss");
      String operateDate = now.format(dateFormatter); // 日期字符串
      String operateTime = now.format(timeFormatter); // 时间字符串
      String operateUser = user.getUID()+""; // 操作人

      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);

      KqMembersValidateBiz kqMembersValidateBiz = new KqMembersValidateBiz();
      CustomLeaveUtil customLeaveUtil = new CustomLeaveUtil();
      KqCustomFlowData kqCustomFlowData = new KqCustomFlowData();

      Map<String, List<String>> validateResult = new KqMembersValidateBiz(params, user).getKqMembersValidateList();
      Map<String, Object> workFlowInfo = new HashMap<>();//userid|date--工作流程
      Map<String, Object> workFlowInfoNew = new HashMap<>();//userid|date--请假流程
      Map<String, Object> workFlowInfoBackNew = new HashMap<>();//userid|date--销假流程
      Map<String, Object> shiftFlowInfoNew = new HashMap<>();//userid|date--调班流程
      Map<String, Object> evectionFlowInfoNew = new HashMap<>();//出差流程
      Map<String, Object> outFlowInfoNew = new HashMap<>();//公出流程
      Map<String, Object> changeFlowInfoNew = new HashMap<>();//变更流程
      //换个逻辑，如果当天有请假，那么不允许操作
      if ("1".equals(newlogic)  && user.getUID()!=1){
        try {
          customLeaveUtil.getAllFlowData(workFlowInfo,monthBeginDate,monthEndDate,"1");
          customLeaveUtil.getLeaveDataNew(workFlowInfoNew,monthBeginDate,monthEndDate);
          customLeaveUtil.getLeaveBackDataNew(workFlowInfoBackNew,monthBeginDate,monthEndDate);
          //增加调班流程
          kqCustomFlowData.getShiftDataNew(shiftFlowInfoNew,monthBeginDate,monthEndDate);
          //增加出差流程
          kqCustomFlowData.getEvectionDataNew(evectionFlowInfoNew,monthBeginDate,monthEndDate);
          //增加外出流程
          kqCustomFlowData.getOutDataNew(outFlowInfoNew,monthBeginDate,monthEndDate);
        } catch (Exception e) {
          kqLog.writeLog("==ImportExcelCmdGetFlowError=="+e);
        }
      }
      kqLog.info("==impotSchedule==groupid"+groupId);
      kqLog.info("==workFlowInfo=="+JSON.toJSONString(workFlowInfo));
      kqLog.info("==workFlowInfoNew=="+JSON.toJSONString(workFlowInfoNew));
      kqLog.info("==workFlowInfoBackNew=="+JSON.toJSONString(workFlowInfoBackNew));
      kqLog.info("==evectionFlowInfoNew=="+JSON.toJSONString(evectionFlowInfoNew));
      kqLog.info("==outFlowInfoNew=="+JSON.toJSONString(outFlowInfoNew));
      List<String> excludeidList = new ArrayList<>();
      KqShiftscheduleOperateLog bean = null;
      boolean needChange = false;
      ResourceComInfo rci = new ResourceComInfo();
      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);
        }
      }

      String tableName = "";
      if ("1".equals(isEdit)){
        tableName = "kq_flow_shiftschedule";
      }else {
        tableName = "kq_shiftschedule";
      }
      sql = " select id, resourceid, kqdate, serialid from "+tableName+" where groupid=? and (isdelete is null or isdelete <> '1') ";
      rs.executeQuery(sql,groupId);
      while(rs.next()){
        mapShiftSchedule.put(rs.getString("kqdate")+"|"+rs.getString("resourceid"),Util.null2String(rs.getString("id"))+"#"+Util.null2String(rs.getString("serialid")));
      }

      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;
          if(!managerRoleUserIdList.isEmpty()) {
            if(!managerRoleUserIdList.contains(resourceId)) 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));
			kqdate = kqdate.trim().replace(String.valueOf((char)10), "#");
            kqdate = kqdate.split("#")[0];
            if(!SaveShiftScheduleCmd.isDate(kqdate)) {
              continue;
            }



            //if(weaver.common.DateUtil.timeInterval(kqdate,today)>0)continue;//今天之前的无需处理
            serialId =  Util.null2String(mapSerial.get(serialName));
            if(serialId.length()==0)continue;

            String shiftScheduleValue = Util.null2String(mapShiftSchedule.get(kqdate+"|"+resourceId));
            String id = "";
            String oldSerialid = "";
            if(!"".equals(shiftScheduleValue)) {
              id = shiftScheduleValue.split("#")[0];
              oldSerialid = shiftScheduleValue.split("#")[1];
            }

            // 如果超过了有效期，就不存储数据
            List<String> validateList = validateResult.get(resourceId);
            boolean isInRange = kqMembersValidateBiz.isInRange(kqdate, validateList);

            if (!isInRange){
              continue;
            }
//            if(!isInRange && oldSerialid.length() > 0) {
//              validateParams = new ArrayList<>();
//              validateParams.add(resourceId);
//              validateParams.add(kqdate);
//              delValidateParams.add(validateParams);
//              lsFormatParams.add(validateParams);
//              bean = new KqShiftscheduleOperateLog(2,user.getLastname(),kqGroupComInfo.getGroupname(groupId)+"(id:"+groupId+")",rci.getLastname(resourceId),
//                      kqdate, ("-1".equals(oldSerialid) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftManagementComInfo.getSerial(oldSerialid)+"(id:"+oldSerialid+")"), id, "",user.getLanguage());
//              addLog(bean);
//            }


            if(id.length()>0 && !oldSerialid.equals(serialId) && oldSerialid.length() > 0){
              String isRest = kqShiftManagementComInfo.getIsRest(oldSerialid);
              String lastname = resourceComInfo.getLastname(resourceId);
              kqLog.info("==isRest=="+isRest+ " | "+oldSerialid + " | "+kqdate + " | "+resourceId);
              //如果当天有请假，不允许修改
              if ("1".equals(newlogic) && user.getUID()!=1){
//                if (workFlowInfo.get(resourceId+"|"+kqdate)!=null && user.getUID()!=1
//                && customLeaveUtil.isLeave(workFlowInfo,resourceId,kqdate,kqTimesArrayComInfo)){
//                  String lastname = resourceComInfo.getLastname(resourceId);
//                  warnMessage += lastname+","+kqdate+",已有请假。"+("1".equals(isline)?"\n":"");
//                }
                //换成新逻辑吧，因为当天可能没有排班或者是休息班次，没有生成请假数据，所以不能通过标准生成的去判断
                if ("1".equals(isRest)){

                  int groupCount = queryGroupCountByMonth(resourceId,kqMonth);
                  if(groupCount == 0){
                    //校验请假
                    if (customLeaveUtil.isLeaveNew(workFlowInfoNew,resourceId,kqdate,workFlowInfoBackNew)){
                      warnMessage += lastname+","+kqdate+",已有请假。"+("1".equals(isline)?"\n":"");
                    }
                    //校验出差
//                  if (customLeaveUtil.isKqFlowNew(evectionFlowInfoNew,resourceId,kqdate)){
//                    warnMessage += lastname+","+kqdate+",已有出差。"+("1".equals(isline)?"\n":"");
//                  }
                    //校验公出
//                    if (customLeaveUtil.isKqFlowNew(outFlowInfoNew,resourceId,kqdate)){
//                      warnMessage += lastname+","+kqdate+",已有外出。"+("1".equals(isline)?"\n":"");
//                    }
                    //校验调班
                    if (customLeaveUtil.isKqFlowNew(shiftFlowInfoNew,resourceId,kqdate)){
                      warnMessage += lastname+","+kqdate+",已有调班。"+("1".equals(isline)?"\n":"");
                    }
                  }

                }else {
                  if (workFlowInfo.get(resourceId+"|"+kqdate)!=null){
                    warnMessage =  customLeaveUtil.isLeave(workFlowInfo,resourceId,kqdate,kqTimesArrayComInfo,lastname,warnMessage);
                  }
                  if (customLeaveUtil.isKqFlowNew(shiftFlowInfoNew,resourceId,kqdate)){
                    warnMessage += lastname+","+kqdate+",已有调班。"+("1".equals(isline)?"\n":"");
                  }
                }
              }


              //如果排班月份为当前月份或之前月份，那么不能排班
              if ("1".equals(oldlogic)){
                boolean isNonEdit = kqMembersValidateUtil.isHave(kqdate, resourceId, isEdit, user, validateFromData);
                if (isNonEdit){
                  retmap.put("status", "-1");
                  retmap.put("message","无法修改当前或者之前的月份，如需修改请联系超管");
                  return retmap;
                }
              }

              needChange = true;
              param = new ArrayList<Object>();
              param.add(serialId);
              param.add(operateDate);
              param.add(operateTime);
              param.add(operateUser);
              param.add("importExcel");

              param.add(id);

//              param.add(kqdate);
//              param.add(resourceId);
//              param.add(groupId);
//              param.add(oldSerialid);


              paramUpdate.add(param);
              bean = new KqShiftscheduleOperateLog(1, user.getLastname(), kqGroupComInfo.getGroupname(groupId) + "(id:" + groupId + ")", rci.getLastname(resourceId),
                      kqdate, ("-1".equals(oldSerialid) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftManagementComInfo.getSerial(oldSerialid) + "(id:" + oldSerialid + ")"), id, ("-1".equals(serialId) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftManagementComInfo.getSerial(serialId) + "(id:" + serialId + ")"),user.getLanguage());
              addLog(bean);
            }else{
              if(!oldSerialid.equals(serialId)) {

                //如果当天有请假，不允许修改

                if ("1".equals(newlogic) && user.getUID()!=1){
//                  if (workFlowInfo.get(resourceId+"|"+kqdate)!=null && user.getUID()!=1
//                  && customLeaveUtil.isLeave(workFlowInfo,resourceId,kqdate,kqTimesArrayComInfo)){
//                    String lastname = resourceComInfo.getLastname(resourceId);
//                    warnMessage += lastname+","+kqdate+",已有请假。"+("1".equals(isline)?"\n":"");
//                  }
                  //换成新逻辑吧，因为当天可能没有排班或者是休息班次，没有生成请假数据，所以不能通过标准生成的去判断
                  String isRest = kqShiftManagementComInfo.getIsRest(serialId);
                  if (true){
                    String lastname = resourceComInfo.getLastname(resourceId);
                    //插入的话还是用流程直接判断
                    if ( user.getUID()!=1){

                      int groupCount = queryGroupCountByMonth(resourceId,kqMonth);
                      if(groupCount == 0){

                        //校验请假
                        if (customLeaveUtil.isLeaveNew(workFlowInfoNew,resourceId,kqdate,workFlowInfoBackNew)){
                          warnMessage += lastname+","+kqdate+",已有请假。"+("1".equals(isline)?"\n":"");
                        }
                        //校验出差
//                      if (customLeaveUtil.isKqFlowNew(evectionFlowInfoNew,resourceId,kqdate)){
//                        warnMessage += lastname+","+kqdate+",已有出差。"+("1".equals(isline)?"\n":"");
//                      }
                        //校验公出
//                        if (customLeaveUtil.isKqFlowNew(outFlowInfoNew,resourceId,kqdate)){
//                          warnMessage += lastname+","+kqdate+",已有外出。"+("1".equals(isline)?"\n":"");
//                        }
                        //校验调班
                        if (customLeaveUtil.isKqFlowNew(shiftFlowInfoNew,resourceId,kqdate)){
                          warnMessage += lastname+","+kqdate+",已有调班。"+("1".equals(isline)?"\n":"");
                        }

                      }



                    }
                  }else if (workFlowInfo.get(resourceId+"|"+kqdate)!=null && user.getUID()!=1){
                    String lastname = resourceComInfo.getLastname(resourceId);
                      warnMessage =  customLeaveUtil.isLeave(workFlowInfo,resourceId,kqdate,kqTimesArrayComInfo,lastname,warnMessage);
                  }

                }
                //如果排班月份为当前月份或之前月份，那么不能排班
                if ("1".equals(oldlogic)){
                  boolean isNonEdit = kqMembersValidateUtil.isHave(kqdate, resourceId, isEdit, user, validateFromData);
                  if (isNonEdit){
                    retmap.put("status", "-1");
                    retmap.put("message","无法修改当前或者之前的月份，如需修改请联系超管");
                    return retmap;
                  }
                }

                needChange = true;
                param = new ArrayList<Object>();
                param.add(kqdate);
                param.add(serialId);
                param.add(resourceId);
                param.add(groupId);
                param.add(operateDate);
                param.add(operateTime);
                param.add(operateUser);
                param.add("importExcel");
                paramInsert.add(param);
                bean = new KqShiftscheduleOperateLog(0, user.getLastname(), kqGroupComInfo.getGroupname(groupId) + "(id:" + groupId + ")", rci.getLastname(resourceId),
                        kqdate, "", id, ("-1".equals(serialId) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftManagementComInfo.getSerial(serialId) + "(id:" + serialId + ")"), user.getLanguage());
                addLog(bean);
              }
            }
            if(needChange) {
              formatParams = new ArrayList<>();
              formatParams.add("importExcel");
              formatParams.add(resourceId);
              formatParams.add(kqdate);
              lsFormatParams.add(formatParams);

              checkSerialParams = new ArrayList<>();
              checkSerialParams.add("importExcel");
              checkSerialParams.add(resourceId);
              checkSerialParams.add(kqdate);
              checkSerialParams.add(serialId);
              lscheckSerialParams.add(checkSerialParams);
            }

          }
        }
        if (!"".equals(warnMessage)){
          retmap.put("status", "-1");
          retmap.put("message",warnMessage);
          return retmap;

        }

        //2025-03-27 增加对应操作人信息，以及操作时间
        //2025-06-06 增加创建来源
        sql = " update "+tableName+" set serialid=?, isDelete=0,wfidstatus=1,operateDate=?,operateTime=?,operateUser=?,updateFrom=? where id = ? ";
        boolean isok4 = bRs.executeBatchSqlNew(sql,paramUpdate);
        new BaseBean().writeLog(idx+">>>isok4="+isok4+",paramUpdate="+ JSONObject.toJSONString(paramUpdate));
//        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));
//          boolean isUpdate = rs.executeUpdate(sql, serialid, operateDate, operateTime, operateUser,id);
//          new BaseBean().writeLog("==isUpdate=="+isUpdate);
//        }

          sql = "insert into "+tableName+" (kqdate,serialid,resourceid,groupid,isDelete,wfidstatus,operateDate,operateTime,operateUser,updateFrom) values(?,?,?,?,0,1,?,?,?,?)";
        boolean isok3 = bRs.executeBatchSqlNew(sql,paramInsert);
        new BaseBean().writeLog(idx+">>>isok3="+isok3+",paramInsert="+JSONObject.toJSONString(paramInsert));
//        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,operateDate,operateTime,operateUser);
//        }
      }

      // 删除一些有效期之后的排班
//      sql = " update "+tableName+" set isdelete =1,wfidstatus=3 where resourceid = ? and kqdate = ? and groupid = "+groupId;
//      boolean isok1 = bRs.executeBatchSqlNew(sql,lsFormatParams);
//      new BaseBean().writeLog("isok1="+isok1+",lsFormatParams="+JSONObject.toJSONString(lsFormatParams));
      //      for (List<Object> delete_params : delValidateParams) {
//        String resourceid = Util.null2String(delete_params.get(0));
//        String kqdate = Util.null2String(delete_params.get(1));
//        rs.executeUpdate(sql, resourceid, kqdate);
//      }

      //删除之前的排的班次，一天只能有一个班次
      sql = " update "+tableName+" set isdelete =1,wfidstatus=3,deleteFrom=? where (isdelete is null or isdelete <> '1') and resourceid = ? and kqdate = ? and groupid != "+groupId;
      boolean isok1 = bRs.executeBatchSqlNew(sql,lsFormatParams);
      kqLog.info("isok1="+isok1+",lsFormatParams="+JSONObject.toJSONString(lsFormatParams));
      //      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 "+tableName+" set isdelete =1,wfidstatus=3,deleteFrom=? where (isdelete is null or isdelete <> '1') and resourceid = ? and kqdate = ? and serialid != ? ";
      boolean isok2 = bRs.executeBatchSqlNew(sql,lscheckSerialParams);
      kqLog.info("isok2="+isok2+",lscheckSerialParams="+JSONObject.toJSONString(lscheckSerialParams));
//      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);
//      }

      sql = "insert into kq_shiftscheduleoperate_log(operatetype,operator,kqgroupname,changeduser,changedshiftdate,changedshiftname,kqshiftid) values(?,?,?,?,?,?,?)";
      bRs.executeBatchSqlNew(sql, operateLogParams);

      new KQShiftScheduleComInfo().removeCache();

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

      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()));
      new BaseBean().writeLog("==importScheduleError== 错误信息：" + ExceptionUtil.getMessage(e));
      new BaseBean().writeLog("==importScheduleError== 堆栈信息：\n" + ExceptionUtil.stacktraceToString(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;
  }

  private void addLog(KqShiftscheduleOperateLog bean) {
    operateLogParam = new ArrayList<>();
    operateLogParam.add(bean.getOperatetype());
    operateLogParam.add(bean.getOperator());
    operateLogParam.add(bean.getKqgroupname());
    operateLogParam.add(bean.getChangeduser());
    operateLogParam.add(bean.getChangedshiftdate());
    operateLogParam.add(bean.getChangedshiftname());
    operateLogParam.add(bean.getKqshiftid());
    operateLogParams.add(operateLogParam);
  }


  public int queryGroupCountByMonth(String resourceid, String kqMonth) {
    int count = 0;
    BaseBean bb = new BaseBean();
    RecordSet rs = new RecordSet();
    try{
      if(StringUtils.isNotBlank(resourceid) && StringUtils.isNotBlank(kqMonth)){
        String sql =" SELECT distinct resourceid, groupid FROM ( " +
                " SELECT a.id AS resourceid, b.groupid, a.status,b.validateto FROM HrmResource a, kq_groupmember b " +
                " WHERE a.id=b.typevalue and b.type =1 and (b.isdelete is null or b.isdelete <> '1') " +
                " UNION ALL " +
                " SELECT a.id AS resourceid, b.groupid, a.status,b.validateto FROM HrmResource a, kq_groupmember b " +
                " WHERE a.subcompanyid1 = b.typevalue AND b.type=2 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto and (b.isdelete is null or b.isdelete <> '1') " +
                " UNION ALL " +
                " SELECT a.id AS resourceid, b.groupid, a.status,b.validateto FROM HrmResource a, kq_groupmember b " +
                " WHERE a.departmentid = b.typevalue AND b.type=3 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto and (b.isdelete is null or b.isdelete <> '1') " +
                " UNION ALL " +
                " SELECT a.id AS resourceid, b.groupid, a.status,b.validateto FROM HrmResource a, kq_groupmember b " +
                " WHERE a.jobtitle = b.typevalue AND b.type=5 " +
                " AND (b.jobtitlelevel=1 OR (b.jobtitlelevel=2 AND a.subcompanyid1 IN(b.jobtitlelevelvalue)) OR (b.jobtitlelevel=3 AND a.departmentid IN(b.jobtitlelevelvalue))) " +
                " and (b.isdelete is null or b.isdelete <> '1') " +
                " UNION ALL " +
                " SELECT a.id AS resourceid, b.groupid, a.status,b.validateto FROM HrmResource a, kq_groupmember b " +
                " WHERE b.type=6 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto and (b.isdelete is null or b.isdelete <> '1') ) t" +
                " where 1=1 "+
                " and t.status in (0,1,2,3) "+
                " and t.resourceid =  "+resourceid+
                " and left(t.validateto,7)='"+kqMonth+"'";
        bb.writeLog("sql:"+sql);
        rs.executeQuery(sql);
        if(rs.next()){
          count++;
        }
      }
    }catch (Exception e){
      bb.writeLog("e:"+e);
    }
    return count;

  }

}
