package com.engine.kq.cmd.shiftschedule;

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.KQHolidaySetBiz;
import com.engine.kq.biz.KQShiftManagementComInfo;
import com.engine.kq.cmd.shiftmanagement.toolkit.ShiftManagementToolKit;
import com.engine.kq.log.KQLog;
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 java.util.*;

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

	public GetShiftScheduleCmd(Map<String, Object> params, User user) {
		this.user = user;
		this.params = params;
	}

	@Override
	public Map<String, Object> execute(CommandContext commandContext) {
		Map<String, Object> retmap = new HashMap<String, Object>();
		Map<String, Object> shiftSchedule = new HashMap<String, Object>();
		List<Object> lsDatas = null;
		List<Object> lsKeyDatas = null;
		Map<String, Object> data = null;
		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;
		  }

			int count = 0;
			int pageCount = 0;
			int isHavePre = 0;
			int isHaveNext = 0;

			KQGroupComInfo kQGroupComInfo = new KQGroupComInfo();
			KQShiftManagementComInfo kQShiftManagementComInfo = new KQShiftManagementComInfo();
			ShiftManagementToolKit shiftManagementToolKit = new ShiftManagementToolKit();
			ResourceComInfo resourceComInfo = new ResourceComInfo();
			DepartmentComInfo departmentComInfo = new DepartmentComInfo();
		  String status = Util.null2String(params.get("status"));

		  String groupId = Util.null2String(params.get("groupId"));//考勤组id
			String kqMonth = Util.null2String(params.get("kqMonth"));//月份
			String viewscope = Util.null2String(params.get("viewscope"));//0：总部 1：分部 2：部门 3：人员 4：我的下属
			int pageIndex = Util.getIntValue(Util.null2String(params.get("pageIndex")), 1);
			int pageSize = Util.getIntValue(Util.null2String(params.get("pageSize")), 20);
			String year = kqMonth.substring(0,4);
			String month = kqMonth.substring(5,7);
		  String monthBeginDate = DateUtil.getFirstDayOfMonth(kqMonth+"-01");
			String monthEndDate = DateUtil.getLastDayOfMonth(kqMonth+"-01");

			String serialidStr = Util.null2String(kQGroupComInfo.getSerialids(groupId));
			List<String> serialids = new ArrayList<>();
			List<Object> lsSerialDatas = new ArrayList<>();
			if(serialidStr.length() > 0){
				serialids = Util.splitString2List(serialidStr,",");
        for(int i=0;i<serialids.size();i++){
          data = new HashMap<>();
          data.put("id",serialids.get(i));
          data.put("name",shiftManagementToolKit.getShiftOnOffWorkSections(serialids.get(i),user.getLanguage()));
          data.put("bgColor",kQShiftManagementComInfo.getColor(serialids.get(i)));
					lsSerialDatas.add(data);
        }
      }

			lsDatas = new ArrayList<>();
      sql = "select id,shiftcyclename,shiftcycleserialids from kq_group_shiftcycle where groupid = ? order by id asc ";
      rs.executeQuery(sql,groupId);
      while(rs.next()){
        data = new HashMap<>();
        data.put("name",Util.null2String(rs.getString("shiftcyclename")));//周期名称,
        data.put("serial",Util.splitString(Util.null2String(rs.getString("shiftcycleserialids")),","));//周期顺序，例7天一周期
        lsDatas.add(data);
      }
			retmap.put("serialInfo", lsDatas);
			lsDatas = KQHolidaySetBiz.getGroupHolidaySetList(groupId,year,month);
			retmap.put("holidays", lsDatas);

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

			String isNoAccount = Util.null2String(params.get("isNoAccount"));// 是否显示无账号人员
			if (!"1".equals(isNoAccount)) {
				sqlWhere += " and t.status in(0,1,2,3) and t.loginid is not null "+(rs.getDBType().equals("oracle")?"":" and t.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 + ")";
				}
			}


			String backFields = " t.resourceid, t.groupid, t.status, t.dsporder,t.lastname,t.subcompanyid1, t.departmentid, t.loginid ";
			String sqlFrom = " from (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 " +
												" 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 where t.groupid = "+groupId;
			String groupBy = " ";


			lsKeyDatas = new ArrayList<>();
			lsDatas = 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 " +
//						" 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";

			sql = " select count(*) as c from ( select 1 as c "+sqlFrom+sqlWhere+groupBy+") t";
			rs.execute(sql);
			if (rs.next()){
				count = rs.getInt("c");
			}

			if (count <= 0) {
				pageCount = 0;
			}

			pageCount = count / pageSize + ((count % pageSize > 0) ? 1 : 0);

			isHaveNext = (pageIndex + 1 <= pageCount) ? 1 : 0;

			isHavePre = (pageIndex - 1 >= 1) ? 1 : 0;

			String orderBy = " order by t.dsporder asc, t.lastname asc ";
			String descOrderBy = " order by t.dsporder desc, t.lastname desc ";

			sql = backFields + sqlFrom  + sqlWhere + groupBy;

			if (pageIndex > 0 && pageSize > 0) {
				if (rs.getDBType().equals("oracle")) {
					sql = " select * from (select " + sql+") t "+orderBy;
					sql = "select * from ( select row_.*, rownum rownum_ from ( " + sql + " ) row_ where rownum <= "
						+ (pageIndex * pageSize) + ") where rownum_ > " + ((pageIndex - 1) * pageSize);
				} else if (rs.getDBType().equals("mysql")) {
					sql = " select * from (select " + sql+") t "+orderBy;
					sql = "select t1.* from (" + sql + ") t1 limit " + ((pageIndex - 1) * pageSize) + "," + pageSize;
				}
				else if (rs.getDBType().equals("postgresql")) {
					sql = " select * from (select " + sql+") t "+orderBy;
					sql = "select t1.* from (" + sql + ") t1 limit " +pageSize  + " offset " + ((pageIndex - 1) * pageSize);
				}
				else {
					orderBy = " order by dsporder asc, lastname asc ";
					descOrderBy = " order by dsporder desc, lastname desc ";
					if (pageIndex > 1) {
						int topSize = pageSize;
						if (pageSize * pageIndex > count) {
							topSize = count - (pageSize * (pageIndex - 1));
						}
						sql = " select top " + topSize + " * from ( select top  " + topSize + " * from ( select top "
							+ (pageIndex * pageSize) + sql + orderBy+ " ) tbltemp1 " + descOrderBy + ") tbltemp2 " + orderBy;
					} else {
						sql = " select top " + pageSize + sql+orderBy;
					}
				}
			} else {
				sql = " select " + sql;
			}
      new KQLog().info("GetShiftScheduleCmd:"+sql);
			rs.executeQuery(sql);
			while(rs.next()){
				String resourceid = Util.null2String(rs.getString("resourceid"));
				data = new HashMap<>();
				data.put("resourceid",resourceid);
				data.put("name",resourceComInfo.getLastname(resourceid));
				data.put("department",departmentComInfo.getDepartmentname(resourceComInfo.getDepartmentID(resourceid)));
				List<Object> shiftScheduleDatas = this.getShiftSchedule(groupId, resourceid, monthBeginDate,monthEndDate);
				for(int i=0;shiftScheduleDatas!=null&&i<shiftScheduleDatas.size();i++){
					Map<String,Object> shiftScheduleData = (Map<String,Object>)shiftScheduleDatas.get(i);
					String id = Util.null2String(shiftScheduleData.get("id"));
					String kqdate = Util.null2String(shiftScheduleData.get("kqdate"));
					String serialid = Util.null2String(shiftScheduleData.get("serialid"));
					if(!serialids.contains(serialid) && !serialid.equals("-1")){
						serialids.add(serialid);
						Map<String,Object> serialdata = new HashMap<>();
						serialdata.put("id",serialid);
						serialdata.put("name",shiftManagementToolKit.getShiftOnOffWorkSections(serialid,user.getLanguage()));
						serialdata.put("bgColor",kQShiftManagementComInfo.getColor(serialid));
						serialdata.put("isDelete",1);
						lsSerialDatas.add(serialdata);
					}
					data.put(kqdate,serialid);
					lsKeyDatas.add(resourceid+"|"+kqdate+"|"+id);
				}
				lsDatas.add(data);
			}
			retmap.put("pagesize", pageSize);
			retmap.put("pageindex", pageIndex);
			retmap.put("count", count);
			retmap.put("pagecount", pageCount);
			retmap.put("ishavepre", isHavePre);
			retmap.put("ishavenext", isHaveNext);
			retmap.put("datas", lsDatas);
			retmap.put("keyDatas", lsKeyDatas);
			retmap.put("shiftList", lsSerialDatas);
			retmap.put("groupName", kQGroupComInfo.getGroupname(groupId));
			retmap.put("today", DateUtil.getCurrentDate());
			if(Util.null2String(kQGroupComInfo.getValidity(groupId)).equals("1")){//考勤组设置了有效期
				retmap.put("validityFromDate", kQGroupComInfo.getValidityfromdate(groupId));
				retmap.put("validityEndDate", kQGroupComInfo.getValidityenddate(groupId));
			}
			retmap.put("status", "1");
		}catch (Exception e) {
			retmap.put("status", "-1");
			retmap.put("message",  SystemEnv.getHtmlLabelName(382661,user.getLanguage()));
			writeLog(e);
		}
		return retmap;
	}

	private List<Object> getShiftSchedule(String groupid, String resourceid, String monthBeginDate,String monthEndDate){
		List<Object> datas = new ArrayList<>();
		Map<String,Object> data = null;
		String sql = "";
		RecordSet rs = new RecordSet();
		sql = "	select id,kqdate, serialid from kq_shiftschedule where groupid= '"+groupid+"' and resourceid="+resourceid+" " +
					"	and kqdate>='"+monthBeginDate+"' and kqdate<='"+monthEndDate+"' and (isdelete is null or  isdelete <> '1')";
		rs.executeQuery(sql);
		while(rs.next()){
			String id = Util.null2String(rs.getString("id"));
			String kqdate = Util.null2String(rs.getString("kqdate"));
			String serialid = Util.null2String(rs.getString("serialid"));

			data = new HashMap<>();
			data.put("id",id);
			data.put("kqdate",kqdate);
			data.put("serialid",serialid);
			datas.add(data);
		}
		return datas;
	}

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

}
