package com.engine.kq.cmd.shiftschedule;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
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 org.apache.commons.lang3.StringUtils;
import weaver.common.DateUtil;
import weaver.conn.BatchRecordSet;
import weaver.conn.DBUtil;
import weaver.conn.RecordSet;
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.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SaveShiftScheduleCmd extends AbstractCommonCommand<Map<String, Object>> {

  private SimpleBizLogger logger;

  // 添加日志功能
  private  List<List<Object>> operateLogParams = new ArrayList<>();
  // 操作类型  操作人  考勤组名称+id  被修改人  被修改班次日期  被修改班次+id  排班设置行id
  private List<Object> operateLogParam = null;
  //启动新功能的考勤组
  public String openKqGroupId = new BaseBean().getPropValue("qc3189074","openKqGroupId");


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

  @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 today = DateUtil.getCurrentDate();
    String sql = "";
    KQTimesArrayComInfo kqTimesArrayComInfo = new KQTimesArrayComInfo();

    try{
      //必要的权限判断
      if(!HrmUserVarify.checkUserRight("HrmKQGroup:Add",user)) {
        retmap.put("status", "-1");
        retmap.put("message", SystemEnv.getHtmlLabelName(2012, user.getLanguage()));
        return retmap;
      }
      String data = Util.null2String(params.get("data"));
      JSONObject jsonObj = JSON.parseObject(data);
      KQGroupComInfo kqGroupComInfo = new KQGroupComInfo();
      KQShiftManagementComInfo kqShiftScheduleComInfo = new KQShiftManagementComInfo();
      ResourceComInfo rci = new ResourceComInfo();
      String groupId = Util.null2String(jsonObj.get("groupId"));
      String kqMonth = Util.null2String(jsonObj.get("kqMonth"));
      String monthBeginDate = DateUtil.getFirstDayOfMonth(kqMonth+"-01");
      String monthEndDate = DateUtil.getLastDayOfMonth(kqMonth+"-01");
      KQShiftManagementComInfo kqShiftManagementComInfo = new KQShiftManagementComInfo();

      JSONArray datas = jsonObj.getJSONArray("datas");

      String isEdit = Util.null2String(jsonObj.get("isEdit"));  //是否来自编辑页面

      String newlogic = new BaseBean().getPropValue("qc3560103","newlogic");//新逻辑
      String oldlogic = new BaseBean().getPropValue("qc3560103","oldlogic");//旧逻辑
      String isline = new BaseBean().getPropValue("qc3560103","isline");//换行
      String warnMessage = "";
      CustomLeaveUtil customLeaveUtil = new CustomLeaveUtil();
      KqCustomFlowData kqCustomFlowData = new KqCustomFlowData();
      Map<String, String> validateFromData = new HashMap<>();
      KqMembersValidateUtil kqMembersValidateUtil = new KqMembersValidateUtil();

      //新逻辑需要的参数信息
      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)){
        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);
      }

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


      ResourceComInfo resourceComInfo = new ResourceComInfo();
      List<List<Object>> paramInsert = new ArrayList<List<Object>>();
      List<List<Object>> paramUpdate = new ArrayList<List<Object>>();
      List<Object> params = null;
      String delIds = "";
      List<List<Object>> lsFormatParams = new ArrayList<>();
      List<Object> formatParams = null;
      List<List<Object>> lsDeleteParams = new ArrayList<>();
      List<Object> deleteParams = null;
      List<List<Object>> lscheckSerialParams = new ArrayList<>();
      List<Object> checkSerialParams = null;

      JSONObject resourceDataObj = null;
      JSONArray arrKqDataObj = null;
      JSONObject kqDataObj = null;
      KqShiftscheduleOperateLog bean = null;
      for(int i=0;datas!=null&&i<datas.size();i++){
        resourceDataObj = (JSONObject)datas.get(i);
        String resourceid = Util.null2String(resourceDataObj.get("resourceid"));
        String lastname = resourceComInfo.getLastname(resourceid);
        int groupCount = queryGroupCountByMonth(resourceid,kqMonth);
        arrKqDataObj = resourceDataObj.getJSONArray("kqdata");
        for(int j=0;arrKqDataObj!=null&&j<arrKqDataObj.size();j++) {
          kqDataObj = (JSONObject) arrKqDataObj.get(j);
          // 如果id为空，则代表原先是空白单元格
          String id = Util.null2String(kqDataObj.get("id"));
          String date = Util.null2String(kqDataObj.get("date"));
          if(!SaveShiftScheduleCmd.isDate(date)) {
            continue;
          }

          // 原先排班的班次id
          String oldSerialid = Util.null2String(kqDataObj.get("oldValue"));
          // 新班次id    如果 serialid是-999， oldValue是班次id，那么就是原先是有班次的，后面把班次删除了
          String serialid = Util.null2String(kqDataObj.get("serialid"));
          if(serialid.equals("-999"))serialid="";
          boolean needChange = false;
          //if(weaver.common.DateUtil.timeInterval(date,today)>0)continue;//今天之前的无需处理
          if(id.length()>0){  // 更新
            if(serialid.length()==0 && oldSerialid.length() > 0){  //删除

              String isRest = kqShiftManagementComInfo.getIsRest(oldSerialid);

              if ("1".equals(newlogic) && user.getUID()!=1){
                //换成新逻辑吧，因为当天可能没有排班或者是休息班次，没有生成请假数据，所以不能通过标准生成的去判断
                if ("1".equals(isRest)){
                  //校验请假

                  new BaseBean().writeLog("删除-groupCount:"+groupCount);
                  if(groupCount == 0) {
                      if (customLeaveUtil.isLeaveNew(workFlowInfoNew, resourceid, date, workFlowInfoBackNew)) {
                        warnMessage += lastname + "," + date + ",已有请假。" + ("1".equals(isline) ? "\n" : "");
                      }
                      //校验出差
  //                  if (customLeaveUtil.isKqFlowNew(evectionFlowInfoNew,resourceid,date)){
  //                    warnMessage += lastname+","+date+",已有出差。"+("1".equals(isline)?"\n":"");
  //                  }
                      //校验公出
//                      if (customLeaveUtil.isKqFlowNew(outFlowInfoNew, resourceid, date)) {
//                        warnMessage += lastname + "," + date + ",已有外出。" + ("1".equals(isline) ? "\n" : "");
//                      }
                      //校验调班
                      if (customLeaveUtil.isKqFlowNew(shiftFlowInfoNew, resourceid, date)) {
                        warnMessage += lastname + "," + date + ",已有调班。" + ("1".equals(isline) ? "\n" : "");
                      }
                  }

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

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

              if(delIds.length()>0) {
                delIds+=",";
              }
              delIds+=id;
              bean = new KqShiftscheduleOperateLog(2,user.getLastname(),kqGroupComInfo.getGroupname(groupId)+"(id:"+groupId+")",rci.getLastname(resourceid),
                      date, ("-1".equals(oldSerialid) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftScheduleComInfo.getSerial(oldSerialid)+"(id:"+oldSerialid+")"), id, "", user.getLanguage());
              addLog(bean);
            }else {
              if(!oldSerialid.equals(serialid) && oldSerialid.length() > 0) {
                String isRest = kqShiftManagementComInfo.getIsRest(oldSerialid);

                if ("1".equals(newlogic) && user.getUID()!=1){
                  //换成新逻辑吧，因为当天可能没有排班或者是休息班次，没有生成请假数据，所以不能通过标准生成的去判断
                  if ("1".equals(isRest)){
                    new BaseBean().writeLog("更新-groupCount:"+groupCount);
                    if(groupCount == 0) {
                        //校验请假
                        if (customLeaveUtil.isLeaveNew(workFlowInfoNew,resourceid,date,workFlowInfoBackNew)){
                          warnMessage += lastname+","+date+",已有请假。"+("1".equals(isline)?"\n":"");
                        }
                        //校验出差
  //                    if (customLeaveUtil.isKqFlowNew(evectionFlowInfoNew,resourceid,date)){
  //                      warnMessage += lastname+","+date+",已有出差。"+("1".equals(isline)?"\n":"");
  //                    }
                        //校验公出
//                        if (customLeaveUtil.isKqFlowNew(outFlowInfoNew,resourceid,date)){
//                          warnMessage += lastname+","+date+",已有外出。"+("1".equals(isline)?"\n":"");
//                        }
                        //校验调班
                        if (customLeaveUtil.isKqFlowNew(shiftFlowInfoNew,resourceid,date)){
                          warnMessage += lastname+","+date+",已有调班。"+("1".equals(isline)?"\n":"");
                        }
                    }

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

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

                needChange = true;
                params = new ArrayList<Object>();
                params.add(serialid);
                params.add(id);
                params.add(date);
                params.add(resourceid);
                params.add(groupId);
                params.add(oldSerialid);

                paramUpdate.add(params);
                bean = new KqShiftscheduleOperateLog(1,user.getLastname(),kqGroupComInfo.getGroupname(groupId)+"(id:"+groupId+")",rci.getLastname(resourceid),
                        date, ("-1".equals(oldSerialid) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftScheduleComInfo.getSerial(oldSerialid)+"(id:"+oldSerialid+")"), id,  ("-1".equals(serialid) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftScheduleComInfo.getSerial(serialid)+"(id:"+serialid+")"), user.getLanguage());
                addLog(bean);
              }
            }
          }else{  //新建
            if(serialid.length()==0)continue;
            if(!oldSerialid.equals(serialid)) {
              if ("1".equals(newlogic) && user.getUID()!=1){
                String isRest = kqShiftManagementComInfo.getIsRest(serialid);
                //换成新逻辑吧，因为当天可能没有排班或者是休息班次，没有生成请假数据，所以不能通过标准生成的去判断
                if ("1".equals(isRest)){

                  new BaseBean().writeLog("新增-groupCount:"+groupCount);
                  if(groupCount == 0) {
                      //校验请假
                      if (customLeaveUtil.isLeaveNew(workFlowInfoNew,resourceid,date,workFlowInfoBackNew)){
                        warnMessage += lastname+","+date+",已有请假。"+("1".equals(isline)?"\n":"");
                      }
                      //校验出差
  //                  if (customLeaveUtil.isKqFlowNew(evectionFlowInfoNew,resourceid,date)){
  //                    warnMessage += lastname+","+date+",已有出差。"+("1".equals(isline)?"\n":"");
  //                  }
                      //校验公出
//                      if (customLeaveUtil.isKqFlowNew(outFlowInfoNew,resourceid,date)){
//                        warnMessage += lastname+","+date+",已有外出。"+("1".equals(isline)?"\n":"");
//                      }
                      //校验调班
                      if (customLeaveUtil.isKqFlowNew(shiftFlowInfoNew,resourceid,date)){
                        warnMessage += lastname+","+date+",已有调班。"+("1".equals(isline)?"\n":"");
                      }
                  }

                }else {
                  if (workFlowInfo.get(resourceid+"|"+date)!=null){
                    warnMessage =  customLeaveUtil.isLeave(workFlowInfo,resourceid,date,kqTimesArrayComInfo,lastname,warnMessage);
                  }
                  if (customLeaveUtil.isKqFlowNew(shiftFlowInfoNew,resourceid,date)){
                    warnMessage += lastname+","+date+",已有调班。"+("1".equals(isline)?"\n":"");
                  }
                }
              }
              //如果排班月份为当前月份或之前月份，那么不能排班
              if ("1".equals(oldlogic)){
                boolean isNonEdit = kqMembersValidateUtil.isHave(date, resourceid,isEdit, user,validateFromData);
                if (isNonEdit){
                  retmap.put("status", "-1");
                  retmap.put("message","无法修改当前或者之前的月份，如需修改请联系超管");
                  return retmap;
                }
              }

              needChange = true;
              params = new ArrayList<Object>();
              params.add(date);
              params.add(serialid);
              params.add(resourceid);
              params.add(groupId);
              paramInsert.add(params);
              bean = new KqShiftscheduleOperateLog(0,user.getLastname(),kqGroupComInfo.getGroupname(groupId)+"(id:"+groupId+")",rci.getLastname(resourceid),
                      date, "", id, ("-1".equals(serialid) ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : kqShiftScheduleComInfo.getSerial(serialid)+"(id:"+serialid+")"), user.getLanguage());
              addLog(bean);
            }
          }
          if(needChange) {
            deleteParams = new ArrayList<>();
            deleteParams.add(resourceid);
            deleteParams.add(date);
            lsDeleteParams.add(deleteParams);

            checkSerialParams = new ArrayList<>();
            checkSerialParams.add(resourceid);
            checkSerialParams.add(date);
            checkSerialParams.add(serialid);
            lscheckSerialParams.add(checkSerialParams);

            if(DateUtil.timeInterval(date,today)>=0) {
              formatParams = new ArrayList<>();
              formatParams.add(resourceid);
              formatParams.add(date);
              lsFormatParams.add(formatParams);
            }
          }
        }
      }
      if (!"".equals(warnMessage)){
        retmap.put("status", "-1");
        retmap.put("message",warnMessage);
        return retmap;
      }
      //zj
      CustomScheduleUtil customScheduleUtil = new CustomScheduleUtil();
      boolean isopenGroup = customScheduleUtil.isOpenGroup(groupId);
      String tableName = "";
      if ("1".equals(isEdit)){
        tableName = "kq_flow_shiftschedule";
      }else {
        tableName = "kq_shiftschedule";
      }


      if (isopenGroup && "1".equals(isEdit) && (user.getUID() != 1)){
        sql = " update "+tableName+" set serialid=?,wfidstatus=1,isDelete=0 where id = ? ";
      }else {
        sql = " update "+tableName+" set serialid=?,wfidstatus=3,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);
        }



      //zj
      if (isopenGroup && "1".equals(isEdit) && (user.getUID() != 1)){
        sql = "insert into "+tableName+" (kqdate,serialid,resourceid,groupid,isDelete,wfidstatus) values(?,?,?,?,0,1)";
      }else {
        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);
      }

      if(delIds.length()>0){
        List sqlParams = new ArrayList();
        Object[] objects= DBUtil.transListIn(delIds,sqlParams);
        //==zj 如果是新功能，删除得批准后才能生效
        if (isopenGroup && "1".equals(isEdit) && (user.getUID() != 1)){
          sql = "update "+tableName+" set isdelete =1,wfidstatus=1 where id in("+objects[0]+") ";
        }else {
          sql = "update kq_shiftschedule set isdelete =1,wfidstatus=3 where id in("+objects[0]+") ";
        }

        rs.executeUpdate(sql, sqlParams);
      }

      //删除之前的排的班次，一天只能有一个班次
      sql = " update "+tableName+" set isdelete =1,wfidstatus=3 where (isdelete is null or isdelete <> '1') and resourceid = ? and kqdate = ? and groupid != "+groupId;
      for (int i = 0; lsDeleteParams != null && i < lsDeleteParams.size(); i++) {
        List<Object> delete_params = lsDeleteParams.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 where (isdelete is null or isdelete <> '1') and 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);
      }

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

      new KQShiftScheduleComInfo().removeCache();

      //刷新报表数据
      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");
      retmap.put("message", SystemEnv.getHtmlLabelName(18758, user.getLanguage()));
    }catch (Exception e) {
      retmap.put("status", "-1");
      retmap.put("message",  SystemEnv.getHtmlLabelName(382661,user.getLanguage()));
      writeLog(e);
    }
    CustomScheduleUtil customScheduleUtil = new CustomScheduleUtil();
    return retmap;
  }

  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 static boolean isDate(String str) {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    sdf.setLenient(false);  // 严格匹配
    try {
      sdf.parse(str);
      return true;
    } catch (ParseException e) {
      return false;
    }
  }

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

  @Override
  public List<BizLogContext> getLogContexts() {
    return logger.getBizLogContexts();
  }



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