package com.engine.kq.cmd.shiftschedule;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import com.engine.kq.biz.KQGroupComInfo;
import com.engine.kq.biz.KQShiftManagementComInfo;
import com.engine.kq.cmd.shiftmanagement.toolkit.ShiftManagementToolKit;
import com.engine.kq.log.KQLog;
import com.engine.kq.util.ExcelUtil;
import weaver.common.DateUtil;
import weaver.conn.RecordSet;
import weaver.general.Util;
import weaver.hrm.HrmUserVarify;
import weaver.hrm.User;
import weaver.hrm.company.DepartmentComInfo;
import weaver.hrm.resource.ResourceComInfo;
import weaver.systeminfo.SystemEnv;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;

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

  private HttpServletRequest request;
  private HttpServletResponse response;

  public ExportExcelCmd(Map<String, Object> params, HttpServletRequest request, HttpServletResponse response, User user) {
    this.user = user;
    this.params = params;
    this.request = request;
    this.response = response;
  }
  @Override
  public Map<String, Object> execute(CommandContext commandContext) {
    Map<String, Object> retmap = new HashMap<String, Object>();
    RecordSet rs = new RecordSet();
    String sql = "";
    try {
      if (!HrmUserVarify.checkUserRight("HrmKQGroup:Add", user)) {
        retmap.put("status", "-1");
        retmap.put("message", SystemEnv.getHtmlLabelName(2012, user.getLanguage()));
        return retmap;
      }
      JSONObject jsonObj = JSON.parseObject(Util.null2String(params.get("data")));
      String status = Util.null2String(jsonObj.get("status"));
      String groupId = Util.null2String(jsonObj.get("groupId"));//考勤组id
      String kqMonth = Util.null2String(jsonObj.get("kqMonth"));//年月
      String viewscope = Util.null2String(jsonObj.get("viewscope"));
      String startDate = DateUtil.getFirstDayOfMonth(kqMonth+"-01");
      String endDate = DateUtil.getLastDayOfMonth(kqMonth+"-01");

      DepartmentComInfo departmentComInfo = new DepartmentComInfo();
      ResourceComInfo resourceComInfo = new ResourceComInfo();
      KQGroupComInfo kqGroupComInfo = new KQGroupComInfo();
      KQShiftManagementComInfo kqShiftManagementComInfo = new KQShiftManagementComInfo();
      LinkedHashMap<String, Object> workbook = new LinkedHashMap<>();
      List<Object> lsSheet = new ArrayList<>();
      Map<String, Object> sheet = null;
      List<Object> titleList = null;
      Map<String,Object> title = null;
      List<List<Object>> dataList = null;
      List<Object> data = null;
      List<Map<String,Object>> constraintList = null;
      Map<String,Object> constraint = null;

      sheet = new HashMap<>();
      sheet.put("sheetName", SystemEnv.getHtmlLabelName(389516, user.getLanguage()));
      sheet.put("sheetTitle", Util.formatMultiLang(kqGroupComInfo.getGroupname(groupId), user.getLanguage() + "")+SystemEnv.getHtmlLabelName(389516, user.getLanguage()));
      boolean isEnd = false;
      Calendar cal = DateUtil.getCalendar();

      //部门	工号	姓名
      titleList = new ArrayList<>();
      title = new HashMap<>();
      title.put("title",SystemEnv.getHtmlLabelName(124, user.getLanguage()));
      title.put("width",30*256);
      titleList.add(title);

      title = new HashMap<>();
      title.put("title",SystemEnv.getHtmlLabelName(714, user.getLanguage()));
      title.put("width",15*256);
      titleList.add(title);

      title = new HashMap<>();
      title.put("title",SystemEnv.getHtmlLabelName(413, user.getLanguage()));
      title.put("width",15*256);
      titleList.add(title);

      int firstRow = 6;
      int endRow = 0;
      int firstCol = 3;
      int endCol = 0;

      for(String date=startDate; !isEnd;) {
        if (date.equals(endDate)) isEnd = true;
        title = new HashMap<>();
        title.put("title",date);
        title.put("width",16*256);
        titleList.add(title);
        cal.setTime(DateUtil.parseToDate(date));
        date = DateUtil.getDate(cal.getTime(), 1);
      }
      endCol = titleList.size()+firstCol;
      sheet.put("titleList", titleList);

      Map<String,Object> mapShiftSchedule = new HashMap<>();
      String tmpSerialids = "";
      sql = " select serialid, resourceid, kqdate from kq_shiftschedule where (isdelete is null or  isdelete <> '1') and groupid=? ";
      rs.executeQuery(sql,groupId);
      while(rs.next()){
        mapShiftSchedule.put(rs.getString("kqdate")+"|"+rs.getString("resourceid"),rs.getString("serialid"));
        if((","+tmpSerialids+",").indexOf(","+rs.getString("serialid")+",")==-1 && rs.getInt("serialid")>0) {
          if (tmpSerialids.length() > 0) tmpSerialids += ",";
          tmpSerialids += rs.getString("serialid");
        }
      }
      List<Object> sheetMemo = new ArrayList<>();
      ShiftManagementToolKit shiftManagementToolKit = new ShiftManagementToolKit();
      sheetMemo.add(SystemEnv.getHtmlLabelName(390620, user.getLanguage()));
	  List<String> tmpserialids = Util.splitString2List( Util.null2String(kqGroupComInfo.getSerialids(groupId)),",");
      tmpSerialids = "" ;
      if(tmpSerialids != null){
        for(String ts : tmpserialids){
          if(tmpSerialids.length() > 0) tmpSerialids += "," ;
          tmpSerialids += ts ;
        }
      }
      sheetMemo.add( Util.formatMultiLang(shiftManagementToolKit.getShiftOnOffWorkSections(tmpSerialids,user.getLanguage()), user.getLanguage()+"")+" "+SystemEnv.getHtmlLabelName(390605, user.getLanguage()));
      sheet.put("sheetMemo",sheetMemo);


      String sqlWhere  = " ";
      if(viewscope.length()>0){
        if(viewscope.equals("1")){//分部
          String subCompanyId = Util.null2String(jsonObj.get("subCompanyId"));
          sqlWhere += " and t.subcompanyid1 in ("+subCompanyId+") ";
        }else if(viewscope.equals("2")){//部门
          String departmentId = Util.null2String(jsonObj.get("departmentId"));
          sqlWhere += " and t.departmentid in ("+departmentId+") ";
        }else if(viewscope.equals("3")){//人员
          String resourceId = Util.null2String(jsonObj.get("resourceId"));
          sqlWhere += " and t.resourceid in ("+resourceId+") ";
        }
      }

      String isNoAccount = Util.null2String(jsonObj.get("isNoAccount"));// 是否显示无账号人员
      if (!"1".equals(isNoAccount)) {
        sqlWhere += " and status in(0,1,2,3) and loginid is not null "+(rs.getDBType().equals("oracle")?"":" and loginid<>'' ");
      }
      if(status.length()>0){
        if (!status.equals("8") && !status.equals("9")) {
          sqlWhere += " and t.status = "+status+ "";
        }else if (status.equals("8")) {
          sqlWhere += " and (t.status = 0 or t.status = 1 or t.status = 2 or t.status = 3) ";
        }
      }

      if(!Util.null2String(kqGroupComInfo.getExcludecount(groupId)).equals("1")) {
        //剔除无需排班人员
        String excludeid = Util.null2String(kqGroupComInfo.getExcludeid(groupId));
        if (excludeid.length() > 0) {
          sqlWhere += " and t.resourceid not in (" + excludeid + ")";
        }
      }

      dataList = new ArrayList<>();

      sql = " select b.id, b.kqdate, b.serialid, t.resourceid, t.subcompanyid1, t.departmentid, t.loginid from (\n" +
              " SELECT distinct t.resourceid, t.groupid, t.status, t.dsporder,t.lastname,t.subcompanyid1, t.departmentid, t.loginid FROM (  \n" +
              " SELECT a.id AS resourceid, b.groupid, a.status,a.dsporder,a.lastname,a.subcompanyid1, a.departmentid, a.loginid FROM HrmResource a, kq_groupmember b  \n" +
              " WHERE (a.id=b.typevalue and b.type =1) and (b.isdelete is null or  b.isdelete <> '1') \n" +
              " UNION ALL  \n" +
              " SELECT a.id AS resourceid, b.groupid, a.status,a.dsporder,a.lastname,a.subcompanyid1, a.departmentid, a.loginid FROM HrmResource a, kq_groupmember b  \n" +
              " WHERE (a.subcompanyid1 = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=2) and (b.isdelete is null or  b.isdelete <> '1') \n" +
              " UNION ALL  \n" +
              " SELECT a.id AS resourceid, b.groupid, a.status,a.dsporder,a.lastname,a.subcompanyid1, a.departmentid, a.loginid FROM HrmResource a, kq_groupmember b  \n" +
              " WHERE (a.departmentid = b.typevalue AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto AND b.type=3) and (b.isdelete is null or  b.isdelete <> '1') \n" +
              " UNION ALL  \n" +
              " SELECT a.id AS resourceid, b.groupid, a.status,a.dsporder,a.lastname,a.subcompanyid1, a.departmentid, a.loginid FROM HrmResource a, kq_groupmember b  \n" +
              " WHERE  (a.jobtitle = b.typevalue AND b.type=5 and (b.isdelete is null or  b.isdelete <> '1') AND (b.jobtitlelevel=1 OR (b.jobtitlelevel=2 AND a.subcompanyid1 IN(b.jobtitlelevelvalue)) OR (b.jobtitlelevel=3 AND a.departmentid IN(b.jobtitlelevelvalue)))) \n" +
              " UNION ALL  \n" +
              " SELECT a.id AS resourceid, b.groupid, a.status,a.dsporder,a.lastname,a.subcompanyid1, a.departmentid, a.loginid FROM HrmResource a, kq_groupmember b \n" +
              " WHERE b.type=6 AND a.seclevel>=b.seclevel AND a.seclevel<=b.seclevelto and (b.isdelete is null or b.isdelete <> '1'))t) t left join kq_shiftschedule b on t.resourceid=b.resourceid " +
              " and t.groupid=b.groupid and (b.isdelete is null or  b.isdelete <> '1') and b.kqdate>=? and b.kqdate<=? where t.groupid=? \n " +
              sqlWhere +
              " order by t.dsporder,t.lastname";
      new KQLog().info("ExportExcelCmd:"+sql);
      rs.executeQuery(sql,startDate,startDate,groupId);
      while(rs.next()){
        data = new ArrayList<>();
        String resourceid = rs.getString("resourceid");
        data.add(Util.formatMultiLang(departmentComInfo.getDepartmentName(resourceComInfo.getDepartmentID(resourceid)),""+user.getLanguage()));
        data.add(Util.formatMultiLang(resourceComInfo.getWorkcode(resourceid),""+user.getLanguage()));
        data.add(Util.formatMultiLang(resourceComInfo.getLastname(resourceid),""+user.getLanguage()));
        isEnd = false;
        for(String date=startDate; !isEnd;) {
          if (date.equals(endDate)) isEnd = true;
          String serialId = Util.null2String(mapShiftSchedule.get(date+"|"+resourceid));
          String serialName = "";
          if(serialId.equals("-1")){
            serialName =  SystemEnv.getHtmlLabelName(26593, user.getLanguage());
          }else{
            serialName = Util.formatMultiLang(kqShiftManagementComInfo.getSerial(serialId),""+user.getLanguage());
          }
          data.add(serialName);
          cal.setTime(DateUtil.parseToDate(date));
          date = DateUtil.getDate(cal.getTime(), 1);
        }
        dataList.add(data);
      }
      endRow = dataList.size()+firstRow;
      sheet.put("dataList", dataList);

      //增加数据有效性校验
      constraintList = new ArrayList<>();
      constraint = new HashMap<>();
      constraint.put("firstRow",firstRow);
      constraint.put("endRow",endRow);
      constraint.put("firstCol",firstCol);
      constraint.put("endCol",endCol);
      List<String> serialids = Util.splitString2List( Util.null2String(kqGroupComInfo.getSerialids(groupId)),",");
      serialids.add("-1");
      String[] serialnames = new String[serialids.size()];
      for(int i=0;i<serialids.size();i++){
        if(serialids.get(i).equals("-1")){
          serialnames[i] =  SystemEnv.getHtmlLabelName(26593, user.getLanguage());
        }else{
          serialnames[i] = Util.formatMultiLang(kqShiftManagementComInfo.getSerial(serialids.get(i)),""+user.getLanguage());
        }
      }
      constraint.put("constraintDatas", serialnames);
      constraintList.add(constraint);

      sheet.put("constraintList",constraintList);
      sheet.put("createFile", "1");
      lsSheet.add(sheet);

      workbook.put("sheet", lsSheet);
      String fileName = "";//SystemEnv.getHtmlLabelName(389978, user.getLanguage()).replace("$groupName$",kqGroupComInfo.getGroupname(groupId));
      fileName = fileName.replace("$date$",startDate.replace("-","")+"-"+endDate.replace("-",""));
      workbook.put("fileName", fileName);
      ExcelUtil ExcelUtil = new ExcelUtil();
      Map<String, Object> exportMap = ExcelUtil.export(workbook, request, response);
      retmap.putAll(exportMap);
      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;
  }
}
