guangzhouyiselie/GDYSL/websrc/com/kingdee/shr/customer/gtiit/util/ContractRptUtil.java

package com.kingdee.shr.customer.gtiit.util;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Map;

public class ContractRptUtil {

	/**
	 * 全部合同报表查询sql
	 * 
	 * @param param
	 *            用工关系状态
	 * @return 查询用工关系状态为 params的合同报表信息
	 */
	public static String getContractSql(Map<String, Object> params) {

		Map<String, String> date = (Map<String, String>) params.get("queryTime");
		if (date == null) {
			// 获取当前日期
			LocalDate currentDate = LocalDate.now();
			// 定义日期格式
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
			// 将日期转换为指定格式的字符串
			String formattedDate = currentDate.format(formatter);
			date.put("date", formattedDate);
		}
		String queryTime = date.get("date").toString();

		StringBuffer sql = new StringBuffer();

		sql.append("/*dialect*/");
		sql.append(
				"  SELECT     p.fnumber PersonNumber, p.cfsurname LastName, p.cfgivenName FirstName , p.fname_l1 EnglishName, ");
		sql.append(" emp.FAdminOrgID,bu.FName_L2 department,");
		sql.append(" emp.fpositionid,post.cflongname_l1 positions,");
		sql.append(" post.FJobID ,job.FName_L2 jobname,");
		sql.append(" emp.CFWorkercategoryID, w.FName_L2 WorkerCategory,");
		sql.append(
				"  CASE    WHEN ify.fname_l2  IS NOT NULL THEN ify.fname_l2  ELSE ph.PersonType END AS PersonType  ,");
		sql.append(
				"  CASE    WHEN  fu.fname_l2  IS NOT NULL THEN  fu.fname_l2  ELSE ph.FullorPartTime END AS FullorPartTime  ,");
		sql.append(
				" ps.FJoinGroupDateCur as HireDate ,empl.FPlanFormalDate ProbationEndDate,pco.CFContractno ContractNo ,    ");
		sql.append(
				" CASE    WHEN   pco.CFTYPE = '1' THEN 'Contract'     WHEN   pco.CFTYPE = '2' THEN 'Agreement'    ELSE ''     END AS type,    ");
		sql.append(" pco.CFCommencingDate CommencingDate,     pco.CFExpiredDate  ExpiredDate,   ");
		sql.append(
				" CASE  WHEN  ROUND(EXTRACT(DAYS FROM (pco.CFExpiredDate - empl.fenterdate))) =  mp.MaxYearsOfService then    ROUND(mp.MaxYearsOfService / 365, 2) else 0.00   end   as YearsOfService");
		sql.append(" FROM      CT_MP_Pcontractinfo  pco  ");
		sql.append(" left join (");
		sql.append(
				"        SELECT  pco.fpersonid ,max( ROUND(EXTRACT(DAYS FROM (pco.CFExpiredDate - emph.fenterdate)))) as MaxYearsOfService ");
		sql.append(
				"    FROM   CT_MP_Pcontractinfo pco left join T_HR_EmpLaborRelationhis emph on pco.fpersonid=emph.fpersonid where emph.feffdt<='"
						+ queryTime + "' and emph.fleffdt>='" + queryTime
						+ "' group by  pco.fpersonid )  mp  on mp.fpersonid = pco.fpersonid  ");
		sql.append(
				" left join (SELECT * FROM  T_HR_EmpOrgRelation emp where  emp.fisLatestInAday ='1' and  emp.fassignType ='1' and  emp.feffdt <= '"
						+ queryTime + "' and  emp.fleffdt >='" + queryTime
						+ "' )   emp  on  pco.fpersonid = emp.fpersonid   ");
		sql.append(" left join T_BD_PERSON  p on p.fid = pco.fpersonid");
		sql.append(" left join   T_ORG_BaseUnit bu on bu.fid=emp.FAdminOrgID");
		sql.append(" left join T_ORG_Position post on emp.fpositionid = post.fid");
		sql.append(" left join T_ORG_Job job  on post.FJobID=job.fid ");
		sql.append(" left join   T_HR_HRJob  job2     on  job2.FJobID =job.fid ");
		sql.append(" left join CT_MP_WorkerCategory w on w.fid =emp.CFWorkercategoryID");
		sql.append(" left join T_HR_EmployeeClassify ify on ify.fid = p.cfpersontypeid");
		sql.append(" left join  CT_MP_Fullorpart fu on p.cfftorptid= fu.fid");
		sql.append(
				" LEFT JOIN   (SELECT e.FName_L2 PersonType , f.FName_L2 FullorPartTime ,phs.FHistoryRelateID FROM  T_BD_PersonHis  phs ");
		sql.append("	    left join T_HR_EmployeeClassify e   ON e.fid =phs.cfpersontypeid ");
		sql.append("             left join  CT_MP_Fullorpart f  on   f.fid = phs.cfftorptid");
		sql.append("	  where   phs.feffdt <= '" + queryTime + "' and   phs.fleffdt >='" + queryTime
				+ "') ph ON p.fid = ph.FHistoryRelateID");
		sql.append(
				" left join   (SELECT ps.FJoinGroupDateCur ,ps.FPERSONID FROM   T_HR_PersonPositionHis ps where   ps.feffdt <= '"
						+ queryTime + "' and   ps.fleffdt >='" + queryTime + "')   as ps    on ps.fpersonid = p.fid");
		sql.append(
				" left join   ( SELECT e.FPlanFormalDate,e.FPERSONID,e.FLABORRELATIONSTATEID ,e.feffdt ,e.fleffdt, e.fenterdate  FROM T_HR_EmpLaborRelationhis e  where   e.feffdt <= '"
						+ queryTime + "' and   e.fleffdt >='" + queryTime + "' ) as  empl   ");
		sql.append("on empl.FPERSONID  = p.fid");
		sql.append(" LEFT JOIN T_HR_BDEmployeeType dbe ON empl.FLABORRELATIONSTATEID = dbe.fid    ");
		sql.append(" where 1 =1  and empl.feffdt <= '" + queryTime + "' and   empl.fleffdt >='" + queryTime + "'");
		// 在职离职过滤
		String personStatus = params.get("personStatus").toString();
		if (personStatus.equals("1")) {
			// 在职
			sql.append(" and dbe.fnumber  not IN ('010','009','S09')    ");
		} else if (personStatus.equals("2")) {
			// 离职
			sql.append(" and dbe.fnumber   IN ('010','009','S09') ");
		}
		// 员工类别过滤
		String workerCategory = params.get("workerCategory").toString();
		if (!"".equals(workerCategory) && workerCategory != null) {
			sql.append(" and  w.fid  in ( " + workerCategory + ")");
		}
		// 职务过滤
		String job = params.get("job").toString();
		if (!"".equals(job) && job != null) {
			// 组织职务 前端显示缺少 最低和最高职等
			// sql.append(" and job.fid in ( " + job + ")");
			// HR职务
			sql.append(" and  job2.fid  in ( " + job + ")");
		}
		// 合同时间过滤
		Map<String, Object> contractDateMap = (Map<String, Object>) params.get("contractDate");
		Object startDate = contractDateMap.get("startDate");
		Object endDate = contractDateMap.get("endDate");
		if (!"".equals(startDate) && startDate != null) {
			sql.append(" 	and  pco.CFCommencingDate <= '" + endDate + "'");
		}
		if (!"".equals(endDate) && endDate != null) {
			sql.append(" and   pco.CFExpiredDate >=  '" + startDate + "'");
		}
		sql.append("  order by  expireddate desc ");

		String str = sql.toString();
		System.out.print("我的sql是" + sql.toString());

		str = str.replaceAll("(?i)fname_l2", "fname_l1");

		System.out.print("我的sql是" + str);
		return str;

	}

	/**
	 * 最新一条合同报表
	 * 
	 * @param params
	 * @return
	 */
	public static String getNewContractSql(Map<String, Object> params) {

		Map<String, String> date = (Map<String, String>) params.get("queryTime");
		if (date.get("date") == null) {
			// 获取当前日期
			LocalDate currentDate = LocalDate.now();
			// 定义日期格式
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
			// 将日期转换为指定格式的字符串
			String formattedDate = currentDate.format(formatter);
			date.put("date", formattedDate);
		}
		String queryTime = date.get("date").toString();
		StringBuffer sql = new StringBuffer();

		sql.append("/*dialect*/");
//		sql.append(
//				"SELECT p.fnumber PersonNumber, p.cfsurname LastName, p.cfgivenName FirstName , p.fname_l1 EnglishName ,  ");
//		sql.append(" emp.FAdminOrgID,bu.FName_L2 department,");
//		sql.append(" emp.fpositionid,post.cflongname_l1 positions, ");
//		sql.append(" post.FJobID ,job.FName_L2 jobname,      ");
//		sql.append(" emp.CFWorkercategoryID  , w.FName_L2 WorkerCategory, ");
//		sql.append(
//				" CASE    WHEN ify.fname_l2  IS NOT NULL THEN ify.fname_l2  ELSE ph.PersonType END AS PersonType  , ");
//		sql.append(
//				" CASE    WHEN  fu.fname_l2  IS NOT NULL THEN  fu.fname_l2  ELSE ph.FullorPartTime END AS FullorPartTime  , ");
//		sql.append(
//				" ps.FJoinGroupDateCur  as   HireDate ,empl.FPlanFormalDate ProbationEndDate , pco.fpersonid,pco.CFContractno ContractNo , ");
//		sql.append(
//				" CASE    WHEN   pco.CFTYPE = '1' THEN 'Contract'     WHEN   pco.CFTYPE = '2' THEN 'Agreement'    ELSE ''     END AS type,");
//		sql.append(" pco.CFCommencingDate CommencingDate,     pco.CFExpiredDate  ExpiredDate ,mxc.Yearsofservice  ");
//		sql.append(" FROM  CT_MP_Pcontractinfo  pco");
//		sql.append(" right join (  ");
//		sql.append(
//				"		SELECT  c.fpersonid , c.CFCommencingDate , c.CFExpiredDate , ROUND(EXTRACT(DAYS FROM (c.CFExpiredDate - emph.fenterdate))/ 365.25, 1) as Yearsofservice ");
//		sql.append(
//				"		 FROM  (SELECT max(cc.CFCommencingDate) CFCommencingDate ,cc.CFExpiredDate ,cc.fpersonid  FROM   CT_MP_Pcontractinfo cc ");
//		sql.append("			right join ( ");
//		sql.append("			SELECT fpersonid , max(CFExpiredDate) ExpiredDate FROM   CT_MP_Pcontractinfo c ");
//		sql.append("		 where  c.CFCommencingDate <= '" + queryTime + "' and  c.CFExpiredDate >='" + queryTime
//				+ "'   ");
//		sql.append("	       	group by c.fpersonid  ) c  ");
//		sql.append(
//				"         on  cc.fpersonid = c.fpersonid and c.ExpiredDate =cc.CFExpiredDate  group  by cc.CFExpiredDate ,cc.fpersonid ) as c left join T_HR_EmpLaborRelationhis emph on c.fpersonid=emph.fpersonid where emph.feffdt<='"
//						+ queryTime + "' and emph.fleffdt>='" + queryTime + "' ) mxc    ");
//		sql.append(
//				" on mxc.fpersonid = pco.fpersonid and mxc.CFCommencingDate = pco.CFCommencingDate  and mxc.CFExpiredDate= pco.CFExpiredDate ");
//		sql.append(" left join  t_bd_person  p  on p.fid = pco.fpersonid   ");
//		sql.append(" left join ");
//		sql.append(
//				" (SELECT emp.FAdminOrgID,emp.fpositionid,emp.CFWorkercategoryID,emp.fpersonid  FROM  T_HR_EmpOrgRelation emp where ");
//		sql.append(" emp.fisLatestInAday ='1' and  emp.fassignType ='1' and  emp.feffdt <= '" + queryTime
//				+ "' and  emp.fleffdt >='" + queryTime + "' ) emp   ");
//		sql.append(" on p.fid =emp.fpersonid ");
//		sql.append(" left join   T_ORG_BaseUnit bu on bu.fid=emp.FAdminOrgID ");
//		sql.append(" left join T_ORG_Position post on emp.fpositionid = post.fid  ");
//		sql.append(" left join T_ORG_Job job  on post.FJobID=job.fid   ");
//		sql.append(" left join   T_HR_HRJob  job2     on  job2.FJobID =job.fid ");
//		sql.append(" left join CT_MP_WorkerCategory w on w.fid =emp.CFWorkercategoryID ");
//		sql.append(" left join T_HR_EmployeeClassify ify on ify.fid = p.cfpersontypeid ");
//		sql.append(" left join  CT_MP_Fullorpart fu on p.cfftorptid= fu.fid ");
//		sql.append(
//				" LEFT JOIN   (SELECT e.FName_L2 PersonType , f.FName_L2 FullorPartTime ,phs.FHistoryRelateID FROM  T_BD_PersonHis  phs ");
//		sql.append("	    left join T_HR_EmployeeClassify e   ON e.fid =phs.cfpersontypeid ");
//		sql.append("             left join  CT_MP_Fullorpart f  on   f.fid = phs.cfftorptid ");
//		sql.append(" where   phs.feffdt <= '" + queryTime + "' and   phs.fleffdt >='" + queryTime
//				+ "') ph ON p.fid = ph.FHistoryRelateID ");
//		sql.append(" left join  (SELECT ps.FJoinGroupDateCur ,ps.FPERSONID FROM   T_HR_PersonPositionHis ps where ");
//		sql.append(" ps.feffdt <= '" + queryTime + "' and   ps.fleffdt >='" + queryTime + "')  as ps  ");
//		sql.append(" on ps.fpersonid = p.fid ");
//		sql.append(
//				" left join   ( SELECT e.FPlanFormalDate,e.FPERSONID,e.FLABORRELATIONSTATEID,e.fenterdate  FROM T_HR_EmpLaborRelationhis e ");
//		sql.append(" where   e.feffdt <= '" + queryTime + "' and   e.fleffdt >='" + queryTime + "' ) as  empl ");
//		sql.append(" on empl.FPERSONID  = p.fid ");
//		sql.append(" LEFT JOIN T_HR_BDEmployeeType dbe ON empl.FLABORRELATIONSTATEID = dbe.fid ");
//		sql.append(" where 1 =1 ");
//       sql.append(" and  dbe.finservice='1'");
		
		sql.append("/*dialect*/SELECT p.fnumber PersonNumber, p.cfsurname LastName, p.cfgivenName FirstName , p.fname_l1 EnglishName ,   \r\n" + 
				"emp.FAdminOrgID,bu.fname_l1 department, emp.fpositionid,post.cflongname_l1 positions,  post.FJobID ,job.fname_l1 jobname,      \r\n" + 
				" emp.CFWorkercategoryID  , w.fname_l1 WorkerCategory,  CASE    WHEN ify.fname_l1  IS NOT NULL THEN ify.fname_l1  ELSE ph.PersonType END AS PersonType  , \r\n" + 
				" CASE    WHEN  fu.fname_l1  IS NOT NULL THEN  fu.fname_l1  ELSE ph.FullorPartTime END AS FullorPartTime  ,  ps.FJoinGroupDateCur  as  \r\n" + 
				" HireDate ,empl.FPlanFormalDate ProbationEndDate , pco.fpersonid,pco.CFContractno ContractNo ,  CASE    WHEN   pco.CFTYPE = '1' THEN 'Contract'    \r\n" + 
				" WHEN   pco.CFTYPE = '2' THEN 'Agreement'    ELSE ''     END AS type, pco.CFCommencingDate CommencingDate,     \r\n" + 
				" pco.cfexpireddate  ExpiredDate ,mxc.Yearsofservice   FROM  CT_MP_Pcontractinfo  pco right join (  		\r\n" + 
				" SELECT  c.fpersonid , c.CFCommencingDate , c.CFExpiredDate , ROUND(EXTRACT(DAYS FROM (c.CFExpiredDate - emph.fenterdate))/ 365, 2) as Yearsofservice \r\n" + 
				" FROM  (SELECT max(cc.CFCommencingDate) CFCommencingDate ,cc.CFExpiredDate ,cc.fpersonid  FROM   CT_MP_Pcontractinfo cc 		\r\n" + 
				" right join ( 			SELECT fpersonid , max(CFCommencingDate) ExpiredDate FROM   CT_MP_Pcontractinfo c 		\r\n" + 
				" where  c.CFCommencingDate <= '2024-01-04' and  c.CFExpiredDate >='2024-01-04'   	       	group by c.fpersonid  ) c      \r\n" + 
				" on  cc.fpersonid = c.fpersonid and c.ExpiredDate =cc.CFCommencingDate  group  by cc.CFExpiredDate ,cc.fpersonid ) as c left join T_HR_EmpLaborRelationhis  \r\n" + 
				" emph on c.fpersonid=emph.fpersonid where emph.feffdt<='2024-01-04' and emph.fleffdt>='2024-01-04' ) mxc     \r\n" + 
				" on mxc.fpersonid = pco.fpersonid and mxc.CFCommencingDate = pco.CFCommencingDate  and mxc.CFCommencingDate= pco.CFCommencingDate  \r\n" + 
				" left join  t_bd_person  p  on p.fid = pco.fpersonid    left join  (SELECT emp.FAdminOrgID,emp.fpositionid,emp.CFWorkercategoryID,emp.fpersonid  \r\n" + 
				" FROM  T_HR_EmpOrgRelation emp where  emp.fisLatestInAday ='1' and  emp.fassignType ='1' and  emp.feffdt <= '2024-01-04' and  emp.fleffdt >='2024-01-04' ) emp    \r\n" + 
				" on p.fid =emp.fpersonid  left join   T_ORG_BaseUnit bu on bu.fid=emp.FAdminOrgID  left join T_ORG_Position post on emp.fpositionid = post.fid   left join T_ORG_Job job  on post.FJobID=job.fid    \r\n" + 
				" left join   T_HR_HRJob  job2     on  job2.FJobID =job.fid  left join CT_MP_WorkerCategory w on w.fid =emp.CFWorkercategoryID  left join T_HR_EmployeeClassify ify on ify.fid = p.cfpersontypeid  left join  \r\n" + 
				" CT_MP_Fullorpart fu on p.cfftorptid= fu.fid  LEFT JOIN   (SELECT e.fname_l1 PersonType , f.fname_l1 FullorPartTime ,phs.FHistoryRelateID FROM  T_BD_PersonHis  phs 	    left join T_HR_EmployeeClassify e   ON e.fid =phs.cfpersontypeid      \r\n" + 
				" left join  CT_MP_Fullorpart f  on   f.fid = phs.cfftorptid  where   phs.feffdt <= '2024-01-04' and   phs.fleffdt >='2024-01-04') ph ON p.fid = ph.FHistoryRelateID  left join  (SELECT ps.FJoinGroupDateCur ,ps.FPERSONID FROM   \r\n" + 
				" T_HR_PersonPositionHis ps where  ps.feffdt <= '2024-01-04' and   ps.fleffdt >='2024-01-04')  as ps   on ps.fpersonid = p.fid  left join   \r\n" + 
				" ( SELECT e.FPlanFormalDate,e.FPERSONID,e.FLABORRELATIONSTATEID,e.fenterdate  FROM T_HR_EmpLaborRelationhis e  where   e.feffdt <= '2024-01-04' and   e.fleffdt >='2024-01-04' ) as  empl  \r\n" + 
				" on empl.FPERSONID  = p.fid  LEFT JOIN T_HR_BDEmployeeType dbe ON empl.FLABORRELATIONSTATEID = dbe.fid  where 1 =1 and  dbe.finservice='1' ");
//		String srt=sql.toString();
//		String tostr = srt.replaceAll("(?i)2024-01-04", queryTime);
//		sql.append(tostr);
		// 员工类别过滤
		String workerCategory = params.get("workerCategory").toString();
		if (!"".equals(workerCategory) && workerCategory != null) {
			sql.append(" and  w.fid  in ( " + workerCategory + ")");
		}
		// 职务过滤
		String job = params.get("job").toString();
		if (!"".equals(job) && job != null) {
			// sql.append(" and job.fid in ( " + job + ")");
			// HR职务
			sql.append(" and  job2.fid  in ( " + job + ")");
		}
		// 合同时间过滤
		if (!"".equals(queryTime) && queryTime != null) {
			sql.append(" and  pco.CFCommencingDate  <= '" + queryTime + "'");
			sql.append(" and   pco.CFExpiredDate  >= '" + queryTime + "'");
		}

		sql.append("  order by  expireddate desc ");

		String str = sql.toString();
		System.out.print("我的sql是" + sql.toString());

		str = str.replaceAll("(?i)fname_l2", "fname_l1");
		str=str.replaceAll("(?i)2024-01-04", queryTime);
		System.out.print("我的sql是" + str);
		return str;

	}

}