package com.kingdee.eas.custom.facade;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.eas.base.param.IParamControl;
import com.kingdee.eas.base.param.ParamControlFactory;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.custom.utils.SendUtils;
import com.kingdee.eas.mobile.BOSMsgTypeEnum;
import com.kingdee.eas.mobile.MimeTypeEnum;
import com.kingdee.eas.mobile.PriorityEnum;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;

/**
 * 后台任务发送预警消息
 * @author xiaoxin
 *
 */
public class EarlyWarningFacadeControllerBean extends AbstractEarlyWarningFacadeControllerBean
{
    private static Logger logger =
        Logger.getLogger("com.kingdee.eas.custom.facade.EarlyWarningFacadeControllerBean");
    
    /**
     * 发送提交加班费用申请通知
     */
    @Override
    protected void _overExpenseApply(Context ctx) throws BOSException, EASBizException {
    	try {
    		String sql = "SELECT u.fid,emp.jobgradename FROM T_PM_USER u left join T_BD_Person p on u.FPERSONID = p.fid " +
    				"left join (select gra.fname_l2 jobgradename,e.fpersonid from (SELECT FPERSONID,max(fleffdt) as maxDate FROM T_HR_EmpPostExperienceHis group by  FPERSONID) as h " + 
    				"left join T_HR_EmpPostExperienceHis e on e.fpersonid= h.FPERSONID and fleffdt = h.maxDate " + 
    				"left join (SELECT * FROM T_HR_EmpPostRank where fislatest='1') r on r.fpersonid = e.fpersonid " + 
    				"left join T_HR_JobGrade gra on gra.fid=r.fjobgradeid) emp on p.fid=emp.fpersonid "
    				+ "left join T_HR_EmpLaborRelationHis l on l.fpersonid=p.fid "
    				+ "where l.fstartdatetime <= now() and l.fenddatetime >= now()";
    		logger.error("加班费用申请SQL："+sql);
    		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
    		Set<String> userIdSet = new HashSet<String>();
			while(rowSet.next()) {
				String jobGradeName = rowSet.getString("jobgradename");
				if(StringUtils.isNotBlank(jobGradeName)) {
					jobGradeName = jobGradeName.replace("L", "");
					int gradeLevel = Integer.parseInt(jobGradeName);
					//获取职等为1-10的用户ID
					if(gradeLevel>=1 && gradeLevel<=10) {
						userIdSet.add(rowSet.getString("fid"));
					}
				}
			}
			logger.error("用户ID集合："+userIdSet.toString());
			
			LocalDate currentDate = LocalDate.now();
			// 格式化为英文月份
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MMMM", Locale.ENGLISH);
			//获取这个月的英文月份
			String thisMonthName = currentDate.format(formatter);
			//获取上个月的英文月份
			LocalDate previousMonthDate = currentDate.minusMonths(1);
			String LastMonthName = previousMonthDate.format(formatter);
			
			IParamControl ipc = ParamControlFactory.getLocalInstance(ctx);
			String shrEnvironmentIP = ipc.getParamValue(null, "shrEnvironmentIP");
			
			String day = "";
			//自助加班时间设置
			String baseSql = "select fname_l1 from T_HR_ATS_OverTimeReason where FNUMBER = 'zbjbsj001'";
			IRowSet baseRow = DbUtil.executeQuery(ctx, baseSql);
			while(baseRow.next()) {
				day = baseRow.getString("fname_l1");
			}
			
			this.sendEmail(ctx, userIdSet, "reminder of OT application submission", "This is a kind reminder that your "+LastMonthName+" OT application should be submitted via the following link before "+thisMonthName+" "+day+"th. Thanks for your cooperation.\r\n" + 
					shrEnvironmentIP + "/home.do\r\n" + 
					"Those who have no OT request please disregard this reminder.\r\n" + 
					"\r\n" + 
					"Best regards,\r\n" + 
					"Human Resources Department");
		} catch (Exception e) {
			logger.error("发送提交加班费用申请通知失败："+e.getMessage());
		}
    }
    
    /**
     * 转正审批提醒通知
     */
    @Override
    protected void _becomeWorkApply(Context ctx) throws BOSException, EASBizException {
//    	String sql = "select s.FPERSONUSERID,r.fsenderid from T_BAS_AssignRead r "
//    			+ "left join T_WFR_Assign s on r.FASSIGNID = s.FASSIGNID "
//    			+ "where r.FBOSTYPE = 'B65CCEF1' and s.FBIZOBJID in "
//    			+ "(SELECT a.fid FROM T_HR_EMPHIREBIZBILL a "
//    			+ "left join T_HR_EMPHIREBIZBILLEntry b on a.fid=b.fbillid "
//    			+ "where now()>= DATEADD(day, -7,b.fbizdate) "
//    			+ "and now()<= b.fbizdate and a.FBILLSTATE = '2')";
    	//获取转正前一周的审批人和提交人信息
    	String sql = "select s.FPERSONUSERID,r.fsenderid,u.FNAME_L1 readUserName,s.assignUserName,p.fname_l1 personName,p.fnumber personNumber from T_BAS_AssignRead r \r\n" + 
    			"left join T_PM_User u on r.FSENDERID = u.FID \r\n" + 
    			"left join (SELECT a.*,b.FNAME_L1 assignUserName FROM T_WFR_Assign a left join T_PM_User b on a.FPERSONUSERID = b.FID ) s on r.FASSIGNID = s.FASSIGNID \r\n" + 
    			"left join (SELECT a.fid,b.FPERSONID FROM T_HR_EMPHIREBIZBILL a left join T_HR_EMPHIREBIZBILLEntry b on a.fid=b.fbillid ) emp on emp.fid = s.FBIZOBJID \r\n" + 
    			"left join t_bd_person p on p.fid = emp.fpersonid \r\n" + 
    			"where r.FBOSTYPE = 'B65CCEF1' and s.FBIZOBJID in (SELECT a.fid FROM T_HR_EMPHIREBIZBILL a \r\n" + 
    			"    left join T_HR_EMPHIREBIZBILLEntry b on a.fid=b.fbillid \r\n" + 
    			"    where now()>= DATEADD(day, -7,b.fbizdate) and now()<= b.fbizdate and a.FBILLSTATE = '2')";
    	logger.error("获取转正单审批人SQL："+sql);
    	try {
	    	IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
	    	
	    	IParamControl ipc = ParamControlFactory.getLocalInstance(ctx);
			String shrEnvironmentIP = ipc.getParamValue(null, "shrEnvironmentIP");
	    	
	    	Set<String> senderSet = new HashSet<String>();
			while(rowSet.next()) {
				StringBuffer approvalBuffer = new StringBuffer();
				approvalBuffer.append("Dear "+rowSet.getString("assignUserName")+",");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("This is a kind reminder for approving "+rowSet.getString("personName")+"'s probation workflow in Kingdee system. Could you please login to the system by using below link?");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("\r\n");
				approvalBuffer.append(shrEnvironmentIP + "/home.do");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("Please kindly check To-Be-Processed in My Tasks and approve/reject at your earliest convenience. Feel free to let us know if any problem. Many thanks!");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("Best regards,");
				approvalBuffer.append("\r\n");
				approvalBuffer.append("Human Resources Department");
				this.sendEmail(ctx, rowSet.getString("FPERSONUSERID"), "Reminder to probation approval", approvalBuffer.toString());
				
				String senders = rowSet.getString("fsenderid")+rowSet.getString("personNumber");
				if(!senderSet.contains(senders)) {
					StringBuffer senderBuffer = new StringBuffer();
					senderBuffer.append("Dear "+rowSet.getString("readUserName")+",");
					senderBuffer.append("\r\n");
					senderBuffer.append("\r\n");
					senderBuffer.append("This is a kind reminder for approving "+rowSet.getString("personName")+"'s probation workflow in Kingdee system. Could you please login to the system by using below link?");
					senderBuffer.append("\r\n");
					senderBuffer.append("\r\n");
					senderBuffer.append(shrEnvironmentIP + "/home.do");
					senderBuffer.append("\r\n");
					senderBuffer.append("\r\n");
					senderBuffer.append("Please kindly check To-Be-Processed in My Tasks and approve/reject at your earliest convenience. Feel free to let us know if any problem. Many thanks!");
					senderBuffer.append("\r\n");
					senderBuffer.append("\r\n");
					senderBuffer.append("\r\n");
					senderBuffer.append("Best regards,");
					senderBuffer.append("\r\n");
					senderBuffer.append("Human Resources Department");
					this.sendEmail(ctx, rowSet.getString("fsenderid"), "Reminder to probation approval", senderBuffer.toString());
					
				}
				senderSet.add(senders);
			}
			
    	} catch (Exception e) {
    		logger.error("转正审批提醒通知失败："+e.getMessage());
    	}
	}
    
    @Override
    protected void _overAuditApply(Context ctx) throws BOSException, EASBizException {
    	
    }
    
    /**
     * 考勤异常预警通知
     */
    @Override
    protected void _attendanceAbnormalApply(Context ctx) throws BOSException, EASBizException {
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	Calendar calendar = Calendar.getInstance();
    	String nowDate = sdf.format(calendar.getTime());
    	calendar.add(Calendar.DAY_OF_MONTH, -1);
    	String yesterday = sdf.format(calendar.getTime());
    	//获取当天考勤异常总数
    	String sql = "SELECT count(1) count FROM (select FPROPOSER from T_HR_ATS_AbnormalAttendance where FAttendanceDate >= '"+yesterday+"' and FAttendanceDate < '"+nowDate+"' and fstatus='1' and foperationstatus='1' group by FPROPOSER)";
    	logger.error("当天考勤异常人数SQL："+sql);
		try {
			IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
			int count = 0;
			while(rowSet.next()) {
				count = rowSet.getInt("count");
			} 
			IParamControl ipc = ParamControlFactory.getLocalInstance(ctx);
			String attendThreshold = ipc.getParamValue(null, "attendThreshold");
			logger.error("考勤异常阈值："+attendThreshold);
			int threshold = Integer.valueOf(attendThreshold);
			if(count <= threshold) {
				return;
			}
			String roleSql = "select c.fid from T_WFR_WFROLE a left join T_WFR_PERSONROLE b on a.fid=b.froleid left join T_PM_USER c on b.FPSERSONID = c.FPERSONID where a.FNUMBER in ('101','102') and c.fid is not null";
			logger.error("获取工作流角色对应用户SQL："+roleSql);
			IRowSet roleRow = DbUtil.executeQuery(ctx, roleSql);
			Set<String> userIdSet = new HashSet<String>();
			while(roleRow.next()) {
				userIdSet.add(roleRow.getString("fid"));
			}
			String shrEnvironmentIP = ipc.getParamValue(null, "shrEnvironmentIP");
			logger.error("用户ID集合："+userIdSet.toString());
			String title = "Unusual Attendance Exceptions Reminder "+yesterday;
			String content = "Dear HR and IT administrator,\r\n" + 
					" \r\n" + 
					"There are "+count+" staff had attendance exception record on "+yesterday+", please log in the system to check the details and handle them as soon as possible if necessary.\r\n" + 
					" \r\n" + 
					shrEnvironmentIP+"\r\n" + 
					" \r\n" + 
					"GTIIT HR System";
			this.sendEmail(ctx, userIdSet, title, content);
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("考勤异常预警失败："+e.getMessage());
		}
    }
    
    /**
     * 个人异常考勤（已废弃）
     */
    @Override
    protected void _thatAttendAbnormal(Context ctx) throws BOSException, EASBizException {
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	Calendar calendar = Calendar.getInstance();
    	String nowDate = sdf.format(calendar.getTime());
    	calendar.add(Calendar.DAY_OF_MONTH, -1);
    	String yesterday = sdf.format(calendar.getTime());
    	
    	IParamControl ipc = ParamControlFactory.getLocalInstance(ctx);
		String shrEnvironmentIP = ipc.getParamValue(null, "shrEnvironmentIP");
    	
    	String sql = "select b.fid personId,f.fid,a.FATTENDANCEVALUE,j.fname_l1 attendanceName from T_HR_ATS_AbnormalAttendance a "
    			+ "left join T_BD_Person b on a.FPROPOSER = b.fid "
    			+ "left join (select c.fpersonid,d.CFWorkercategoryID from (SELECT FPERSONID,max(FlEFFDT) as maxDate FROM T_HR_EmpOrgRelation where fassignType = '1' group by FPERSONID) c "
    			+ "left join T_HR_EmpOrgRelation d on c.fpersonid = d.fpersonid and c.maxdate=d.FlEFFDT where d.fassignType = '1') e on e.fpersonid=b.fid "
    			+ "left join t_pm_user f on f.FPERSONID = b.fid "
    			+ "left join CT_MP_Fullorpart g on g.fid=b.cfftorptid "
    			+ "left join CT_MP_WorkerCategory h on h.fid=e.CFWorkercategoryID "
    			+ "left join T_HR_ATS_AttendanceProject j on j.fid=a.FATTENDANCEPROJECT "
    			+ "where g.fnumber='FULL' and h.fnumber in ('GTIIT_GAS','GTIIT_PSS','GTIIT_SAS') "
    			+ " and a.FAttendanceDate>='"+yesterday+"' and a.FAttendanceDate<='"+nowDate+"' and a.fstatus='1' and a.foperationstatus='1'";
    	
    	logger.error("个人考勤异常SQL："+sql);
    	IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
    	Set<String> personIdSet = new HashSet<String>();
    	Map<String, List<Map<String, String>>> map = new HashMap<>();
		try {
			while(rowSet.next()) {
				personIdSet.add(rowSet.getString("personId"));
				String userId = rowSet.getString("fid");
				if(StringUtils.isEmpty(userId)) {
					continue;
				}
				if(map.containsKey(userId)) {
					List<Map<String, String>> list = map.get(userId);
					Map<String, String> attendMap = new HashMap<String, String>();
					attendMap.put("attendanceName", rowSet.getString("attendanceName"));
					attendMap.put("attendanceCount", rowSet.getString("FATTENDANCEVALUE"));
					list.add(attendMap);
					map.put(userId, list);
				}else {
					List<Map<String, String>> list = new ArrayList<Map<String, String>>();
					Map<String, String> attendMap = new HashMap<String, String>();
					attendMap.put("attendanceName", rowSet.getString("attendanceName"));
					attendMap.put("attendanceCount", rowSet.getString("FATTENDANCEVALUE"));
					list.add(attendMap);
					map.put(userId, list);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		logger.error("人员ID集合："+personIdSet.toString());
		if(personIdSet.size()<=60) {
			for(String key:map.keySet()) {
				List<Map<String, String>> list = map.get(key)==null?new ArrayList<Map<String, String>>():map.get(key);
				StringBuffer strBuffer = new StringBuffer();
				strBuffer.append("You have attendance exception record on "+yesterday+" ");
				StringBuffer attendBuffer = new StringBuffer();
				for(int i=0;i<list.size();i++) {
					Map<String, String> attendMap = list.get(i);
					attendBuffer.append(attendMap.get("attendanceName")+": "+attendMap.get("attendanceCount")+",");
				}
				if(attendBuffer.length()>0) {
					strBuffer.append("(");
					strBuffer.append(attendBuffer.deleteCharAt(attendBuffer.length()-1));
					strBuffer.append("), ");
				}
				strBuffer.append("please log in the system to check and correct it as soon as possible.");
				strBuffer.append("\r\n");
				strBuffer.append(shrEnvironmentIP + "/home.do");
				this.sendEmail(ctx, key, "Attendance Exception Notification", strBuffer.toString());
			}
		}else {
			Set<String> userIdSet = new HashSet<String>();
			String roleSql = "select c.fid from T_WFR_WFROLE a left join T_WFR_PERSONROLE b on a.fid=b.froleid left join T_PM_USER c on b.FPSERSONID = c.FPERSONID where a.FNUMBER in ('103','104') and c.fid is not null";
			logger.error("获取工作流角色对应用户SQL："+roleSql);
			IRowSet roleRow = DbUtil.executeQuery(ctx, roleSql);
			try {
				while(roleRow.next()) {
					userIdSet.add(roleRow.getString("fid"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			logger.error("用户ID集合："+userIdSet.toString());
			this.sendEmail(ctx, userIdSet, "Attendance Exception Notification", "Today, more than 60% of administrative staff have abnormal attendance. There may be a problem with the system interface. Please contact itsupport@gtiit.edu.cn or the system administrator to check.");
		}
    }
    
    
    /**
     * 个人异常考勤预警（按月）
     */
    @Override
    protected void _monthAttendAbnormal(Context ctx) throws BOSException, EASBizException {
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	Calendar calendar = Calendar.getInstance();
    	calendar.set(Calendar.DAY_OF_MONTH, 1);
    	String thisMonthDate = sdf.format(calendar.getTime());
    	calendar.add(Calendar.MONTH, -1);
    	int thisYear = calendar.get(Calendar.YEAR);
    	String lastMonthDate = sdf.format(calendar.getTime());
    	
    	LocalDate currentDate = LocalDate.now();
		// 格式化为英文月份
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MMM", Locale.ENGLISH);
		//获取这个月的英文月份简写
		String thisMonthName = currentDate.format(formatter);
		//获取上个月的英文月份简写
		LocalDate previousMonthDate = currentDate.minusMonths(1);
		String LastMonthName = previousMonthDate.format(formatter);
		
		IParamControl ipc = ParamControlFactory.getLocalInstance(ctx);
		String shrEnvironmentIP = ipc.getParamValue(null, "shrEnvironmentIP");
    	
		//获取员工上月的异常考勤次数
    	String sql = "select b.fid personId,f.fid,sum(a.FATTENDANCEVALUE) FATTENDANCEVALUE from T_HR_ATS_AbnormalAttendance a "
    			+ "left join T_BD_Person b on a.FPROPOSER = b.fid "
    			+ "left join (select c.fpersonid,d.CFWorkercategoryID from (SELECT FPERSONID,max(FlEFFDT) as maxDate FROM T_HR_EmpOrgRelation where fassignType = '1' group by FPERSONID) c "
    			+ "left join T_HR_EmpOrgRelation d on c.fpersonid = d.fpersonid and c.maxdate=d.FlEFFDT where d.fassignType = '1') e on e.fpersonid=b.fid "
    			+ "left join t_pm_user f on f.FPERSONID = b.fid "
    			+ "left join CT_MP_Fullorpart g on g.fid=b.cfftorptid "
    			+ "left join CT_MP_WorkerCategory h on h.fid=e.CFWorkercategoryID "
    			+ "left join T_HR_ATS_AttendanceProject j on j.fid=a.FATTENDANCEPROJECT "
    			+ "where g.fnumber='FULL' and h.fnumber in ('GTIIT_GAS','GTIIT_PSS','GTIIT_SAS') "
    			+ " and a.FAttendanceDate>='"+lastMonthDate+"' and a.FAttendanceDate<'"+thisMonthDate+"' and a.fstatus='1' and a.foperationstatus='1' group by b.fid,f.fid";
    	
    	logger.error("个人考勤异常SQL："+sql);
    	IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
    	Set<String> userIdSet = new HashSet<String>();
		try {
			while(rowSet.next()) {
				String personId = rowSet.getString("personId");
				String userId = rowSet.getString("fid");
				String attendanceValue = rowSet.getString("FATTENDANCEVALUE");
				if(StringUtils.isEmpty(userId) || StringUtils.isEmpty(personId) || StringUtils.isEmpty(attendanceValue)) {
					continue;
				}
				
				StringBuffer mesBuffer = new StringBuffer();
				mesBuffer.append("You have "+attendanceValue+" attendance exception(s) in "+LastMonthName+", "+thisYear+", please log in the system via the following link to check and correct it(them) as soon as possible.");
				mesBuffer.append("\r\n");
				mesBuffer.append("Line manger's approval of your correction shall be done by "+thisMonthName+" 5th.");
				mesBuffer.append("\r\n");
				mesBuffer.append("\r\n");
				mesBuffer.append(shrEnvironmentIP + "/home.do");
				mesBuffer.append("\r\n");
				mesBuffer.append("\r\n");
				mesBuffer.append("GTIIT HR System");
				this.sendEmail(ctx, userId, "Attendance Exception Notification", mesBuffer.toString());
				userIdSet.add(userId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		logger.error("用户ID集合："+userIdSet.toString());
    }
    
    /**
     * 已废弃
     */
    @Override
    protected void _departAttendAbnormal(Context ctx) throws BOSException, EASBizException {
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	Calendar calendar = Calendar.getInstance();
    	calendar.set(Calendar.DAY_OF_MONTH, 1);
    	String thisMonthDate = sdf.format(calendar.getTime());
    	calendar.add(Calendar.MONTH, -1);
    	int thisYear = calendar.get(Calendar.YEAR);
    	String lastMonthDate = sdf.format(calendar.getTime());
    	
    	LocalDate currentDate = LocalDate.now();
		// 格式化为英文月份
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MMM", Locale.ENGLISH);
		//获取这个月的英文月份简写
		String thisMonthName = currentDate.format(formatter);
		//获取上个月的英文月份简写
		LocalDate previousMonthDate = currentDate.minusMonths(1);
		String LastMonthName = previousMonthDate.format(formatter);
		
		IParamControl ipc = ParamControlFactory.getLocalInstance(ctx);
		String shrEnvironmentIP = ipc.getParamValue(null, "shrEnvironmentIP");
    	
    	String sql = "select b.fid personId,f.fid,sum(a.FATTENDANCEVALUE) FATTENDANCEVALUE from T_HR_ATS_AbnormalAttendance a "
    			+ "left join T_BD_Person b on a.FPROPOSER = b.fid "
    			+ "left join (select c.fpersonid,d.CFWorkercategoryID from (SELECT FPERSONID,max(FlEFFDT) as maxDate FROM T_HR_EmpOrgRelation where fassignType = '1' group by FPERSONID) c "
    			+ "left join T_HR_EmpOrgRelation d on c.fpersonid = d.fpersonid and c.maxdate=d.FlEFFDT where d.fassignType = '1') e on e.fpersonid=b.fid "
    			+ "left join t_pm_user f on f.FPERSONID = b.fid "
    			+ "left join CT_MP_Fullorpart g on g.fid=b.cfftorptid "
    			+ "left join CT_MP_WorkerCategory h on h.fid=e.CFWorkercategoryID "
    			+ "left join T_HR_ATS_AttendanceProject j on j.fid=a.FATTENDANCEPROJECT "
    			+ "where g.fnumber='FULL' and h.fnumber in ('GTIIT_GAS','GTIIT_PSS','GTIIT_SAS') "
    			+ " and a.FAttendanceDate>='"+lastMonthDate+"' and a.FAttendanceDate<'"+thisMonthDate+"' and a.fstatus='1' and a.foperationstatus='1' group by b.fid,f.fid";
    	
    	logger.error("个人考勤异常SQL："+sql);
    	IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
    	Set<String> userIdSet = new HashSet<String>();
    	String title = "Attendance Exception Notification";
		try {
			while(rowSet.next()) {
				String personId = rowSet.getString("personId");
				String userId = rowSet.getString("fid");
				String attendanceValue = rowSet.getString("FATTENDANCEVALUE");
				if(StringUtils.isEmpty(userId) || StringUtils.isEmpty(personId) || StringUtils.isEmpty(attendanceValue)) {
					continue;
				}
				
//				StringBuffer mesBuffer = new StringBuffer();
//				mesBuffer.append("<html><head></head><body>");
//				mesBuffer.append("<span>You have "+attendanceValue+" attendance exception(s) in "+LastMonthName+", "+thisYear+", please log in the system via the following link to check and correct it(them) as soon as possible.</span><br>");
//				mesBuffer.append("<span>Line manger's approval of your correction shall be done by "+thisMonthName+" 5th.</span><br>");
//				mesBuffer.append("<br>");
//				mesBuffer.append("<span><a href=\"https://gtiit.kdeascloud.com/shr/home.do\">https://gtiit.kdeascloud.com/shr/home.do</a></span><br>");
//				mesBuffer.append("<br>");
//				mesBuffer.append("<span>GTIIT HR System</span><br>");
//				mesBuffer.append("</body></html>");
				
				StringBuffer mesBuffer = new StringBuffer();
				mesBuffer.append("You have "+attendanceValue+" attendance exception(s) in "+LastMonthName+", "+thisYear+", please log in the system via the following link to check and correct it(them) as soon as possible.");
				mesBuffer.append("\r\n");
				mesBuffer.append("Line manger's approval of your correction shall be done by "+thisMonthName+" 5th.");
				mesBuffer.append("\r\n");
				mesBuffer.append("\r\n");
				mesBuffer.append(shrEnvironmentIP + "/home.do");
				mesBuffer.append("\r\n");
				mesBuffer.append("\r\n");
				mesBuffer.append("GTIIT HR System");
				
				logger.error("邮件内容："+mesBuffer.toString());
				logger.error("人员ID："+personId);
				SendUtils.msgSend(ctx, title, PriorityEnum.HIGHT_VALUE, false, mesBuffer.toString(), personId,
						BOSMsgTypeEnum.V_TYPE_EMAIL, null, null, MimeTypeEnum.HTML);
				userIdSet.add(userId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
		logger.error("用户ID集合："+userIdSet.toString());
    }
    
    public void sendEmail(Context ctx, Set<String> userIdSet, String title, String body) throws BOSException, EASBizException {
    	if(userIdSet.size() == 0) {
    		return;
    	}
    	List<String> list = userIdSet.stream().collect(Collectors.toList());
    	SendUtils.msgGroupSend(ctx, title, PriorityEnum.HIGHT_VALUE, false, body, list, BOSMsgTypeEnum.V_TYPE_EMAIL, null);
    }
    
    public void sendEmail(Context ctx, String userId, String title, String body) throws BOSException, EASBizException {
    	List<String> list = new ArrayList<String>();
    	list.add(userId);
    	SendUtils.msgGroupSend(ctx, title, PriorityEnum.HIGHT_VALUE, false, body, list, BOSMsgTypeEnum.V_TYPE_EMAIL, null);
    }
}