package com.kingdee.eas.custom.esign.service;


import com.kingdee.bos.util.BOSObjectType;
import com.kingdee.bos.util.BOSUuid;


/**
 * 调薪薪酬取数
 * @author coyle
 * 202603
 */
public class SalaryAdjustmentDataService extends OtherESignConfigDataService {
	public String getSql(String id) {
    	StringBuilder sql = new StringBuilder();
    	
    	
    	// ===================== 薪酬调薪前后对比查询 =====================
    	// 查询内容：员工基础信息 + 固定工资/绩效奖金/福利 调薪前后对比及差额
    	sql.append("SELECT \n");
    	
    	// 1. 员工基础信息
    	// 人员姓名
    	sql.append("    person.FNAME_L2 AS person_name, \n");
    	// 入职日期
    	sql.append("    elation.FEnterDate AS entry_date, \n");
    	// 调薪日期（新记录生效日）
    	sql.append("    new_fas.FEffectDay AS salary_adjust_date, \n");
    	sql.append("    firstParty.FName_l2  as  firstPartyName , \n");
    	// 2. 固定工资对比（税前：TD01+T012+T013+TD02）
    	// 调薪前固定工资
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER IN ('TD01', 'T012', 'T013', 'TD02') THEN old_fas.FMoney ELSE 0 END), 0) AS before_fixed_salary, \n");
    	// 调薪后固定工资
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER IN ('TD01', 'T012', 'T013', 'TD02') THEN new_fas.FMoney ELSE 0 END), 0) AS after_fixed_salary, \n");
    	// 固定工资调薪差额
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER IN ('TD01', 'T012', 'T013', 'TD02') THEN new_fas.FMoney - old_fas.FMoney ELSE 0 END), 0) AS fixed_salary_diff, \n");
    	
    	// 3. 绩效奖金对比（税前：TD04=标准绩效，TD74=试用期绩效）
    	// 3.1 标准绩效奖金
    	// 调薪前标准绩效奖金
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD04' THEN old_fas.FMoney ELSE 0 END), 0) AS before_perf_bonus, \n");
    	// 调薪后标准绩效奖金
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD04' THEN new_fas.FMoney ELSE 0 END), 0) AS after_perf_bonus, \n");
    	// 标准绩效奖金差额
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD04' THEN new_fas.FMoney - old_fas.FMoney ELSE 0 END), 0) AS perf_bonus_diff, \n");
    	
    	// 3.2 试用期绩效奖金
    	// 调薪前试用期绩效奖金
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD74' THEN old_fas.FMoney ELSE 0 END), 0) AS before_prob_perf_bonus, \n");
    	// 调薪后试用期绩效奖金
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD74' THEN new_fas.FMoney ELSE 0 END), 0) AS after_prob_perf_bonus, \n");
    	// 试用期绩效奖金差额
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD74' THEN new_fas.FMoney - old_fas.FMoney ELSE 0 END), 0) AS prob_perf_bonus_diff, \n");
    	
    	// 4. 福利对比（餐补+艰苦补贴+社保公积金补贴）
    	// 4.1 国内餐补（T014）
    	// 调薪前餐补
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'T014' THEN old_fas.FMoney ELSE 0 END), 0) AS before_meal_subsidy, \n");
    	// 调薪后餐补
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'T014' THEN new_fas.FMoney ELSE 0 END), 0) AS after_meal_subsidy, \n");
    	
    	// 4.2 艰苦补贴（TD05）
    	// 调薪前艰苦补贴
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD05' THEN old_fas.FMoney ELSE 0 END), 0) AS before_hardship_subsidy, \n");
    	// 调薪后艰苦补贴
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD05' THEN new_fas.FMoney ELSE 0 END), 0) AS after_hardship_subsidy, \n");
    	
    	// 4.3 社保公积金补贴（TD101）
    	// 调薪前社保公积金补贴
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD101' THEN old_fas.FMoney ELSE 0 END), 0) AS before_social_fund, \n");
    	// 调薪后社保公积金补贴
    	sql.append("    ISNULL(SUM(CASE WHEN sitem_new.FNUMBER = 'TD101' THEN new_fas.FMoney ELSE 0 END), 0) AS after_social_fund \n");
    	
    	// ===================== 表关联 =====================
    	// 主表：人员基础信息
    	sql.append("FROM \n");
    	sql.append("    T_bd_Person person \n");
    	
    	// 关联：入职日期
    	sql.append("LEFT JOIN \n");
    	sql.append("    T_HR_EmpLaborRelation elation ON person.fid = elation.FPersonID \n");
    	
    	// 主表：调薪后记录（当前生效）
    	sql.append("LEFT JOIN \n");
    	sql.append("    T_HR_SFixAdjustSalary new_fas ON person.FID = new_fas.FPersonID \n");
    	
    	// 关联：调薪前记录
    	sql.append("LEFT JOIN \n");
    	sql.append("    T_HR_SFixAdjustSalary old_fas ON new_fas.FOldFixAdjustSalaryID = old_fas.FID \n");
    	
    	// 关联：薪酬项目字典
    	sql.append("LEFT JOIN \n");
    	sql.append("    T_HR_SCmpItem sitem_new ON new_fas.FCmpItemID = sitem_new.FID \n");
    	sql.append("   LEFT JOIN 	\n");
    	sql.append("   T_HR_EmployeeContract  econtract  on  econtract.FEmployeeID = person.FID  \n");
    	sql.append("   left join T_HR_LabContractFirstParty  firstParty   on econtract.FContFirstPartyID = firstParty.FID  \n");
    	
    	// ===================== 查询条件 =====================
    	sql.append("WHERE \n");
    	// 仅保留调薪相关记录
    	sql.append("    new_fas.FAdjustSalaryCauseID IS NOT NULL \n");
    	// 仅保留模板所需薪酬项目
    	sql.append("    AND sitem_new.FNUMBER IN ('TD01', 'T012', 'T013', 'TD02', 'TD04', 'TD74', 'T014', 'TD05', 'TD101') \n");
    	// 调薪后记录为永久有效
    	sql.append("    AND new_fas.FLeffectDay = '2199-12-31 00:00:00' \n");
    	sql.append("    AND  econtract. FState = '1' AND  econtract.FNewState  = '1' AND  econtract.FIsNewestContract = '1'   \n");
        BOSUuid read = BOSUuid.read(id);
        BOSObjectType type = read.getType();
    	 if (type.toString().equals("7BD37592")){//id类型为合同
         	sql.append("and  person.fid  in (select FEmployeeID from T_HR_EmployeeContract where fid = '"+id+"')" ) ;
         }else  if (type.toString().equals("80EF7DED")){//id类型为员工felse
         	sql.append("and person.fid = '"+id+"'");
         }

 	    // ===================== 分组 & 排序 =====================
 	    // 按员工维度分组，一人一条记录
 	    sql.append("GROUP BY \n");
 	    sql.append("    person.FID, person.FNAME_L2, elation.FEnterDate, new_fas.FEffectDay , firstParty.FName_l2  \n");

    	// -------------------------- ORDER BY子句 --------------------------
    	// 排序规则：按人员姓名升序排列
    	sql.append("ORDER BY \n");
    	sql.append("    person.FNAME_L2");
    	
    	System.out.print("osfsql"+sql.toString());
    	return sql.toString();
    }
 
     
}