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 params) { Map date = (Map) 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 contractDateMap = (Map) 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 params) { Map date = (Map) 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; } }