guangzhouyiselie/GDYSL/websrc/com/kingdee/shr/compensation/service/CustomerFunctionSalaryService.java

package com.kingdee.shr.compensation.service;


import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

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.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.compensation.CalSchemeInfo;
import com.kingdee.shr.compensation.app.formula.data.Tools;
import com.kingdee.shr.customer.gtiit.util.DateTimeUtils;

/**
 * 薪酬函数
 * @author issuser
 *
 */
public class CustomerFunctionSalaryService {

	private static Logger logger =
	        Logger.getLogger("com.kingdee.shr.compensation.service.CustomerFunctionSalaryService");
	
	/**
	 * 获取员工工资包当月金额
	 * @param personId
	 * @param cmpItemId
	 * @param months
	 * @return
	 * @throws BOSException 
	 * @throws SQLException 
	 */
	public double getSalayBag(Map<String, Object> paramMap, HashMap<String, Object> tempMap, CalSchemeInfo calSchemeInfo ,String cmpItemId,String months, String mainTableRowFilter) throws BOSException, SQLException {
		Context ctx = Tools.getInstance().getCtx();
		String personId = (String)paramMap.get("T_HR_SCMPCALTABLE_FPERSONID");
		String sql = "SELECT top 1 b.* FROM CT_SAL_SalaryBag a " + 
				" left join CT_SAL_SalaryBagEntry b on a.fid = b.fbillId " + 
				" where a.FBillState = 3 and CFPersonID = '" + personId + "' " + 
				" and a.CFCmpItemID = (select fid from T_HR_SCmpItem where FNumber = '" + cmpItemId + "') and b.CFMonths = '" + months + "'";
		System.out.println("工资包查询sql：" + sql);
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if(rowSet.next()) {
			if(rowSet.getObject("CFAmountPayable")==null || rowSet.getDouble("CFAmountPayable")==0) {
				return rowSet.getDouble("CFMoneys");
			}else {
				return rowSet.getDouble("CFAmountPayable");
			}
		} 
		return 0.0D;
	}
	
	
	/**
	 * 获取工资追溯信息
	 * @param personId
	 * @param cmpItemId
	 * @param months
	 * @return
	 * @throws BOSException 
	 * @throws SQLException 
	 */
	public double getSalayTraceability(Map<String, Object> paramMap, HashMap<String, Object> tempMap, CalSchemeInfo calSchemeInfo ,String projectNumber,String date, String mainTableRowFilter) throws BOSException, SQLException {
		Context ctx = Tools.getInstance().getCtx();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		int year = 0;
		int month = 0;
		try {
			Date parse = format.parse(date);
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(parse);
			year = calendar.get(Calendar.YEAR);
			month = calendar.get(Calendar.MONTH)+1;
		} catch (ParseException e) {
			e.printStackTrace();
		}
		String personId = (String)paramMap.get("T_HR_SCMPCALTABLE_FPERSONID");
		String sql = "SELECT sum(cfreissuemonthpay) reissuemonthpay,sum(cfreissueoverpay) reissueoverpay,sum(cfreissuerestoverpay) reissuerestoverpay,"
				+ "sum(cfreissueholidaysoverpay) reissueholidaysoverpay,sum(cfreissueunleavededuction) reissueunleavededuction "
				+ "FROM CT_CUS_PersonPayForm where year(cfformmonth)="+year+" and month(cfformmonth)="+month+" and cfpersonid='"+personId+"'";
		System.out.println("工资追溯查询sql：" + sql);
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if(rowSet.next()) {
			if(projectNumber.equals("0")) {
				return rowSet.getDouble("reissuemonthpay");
			}else if(projectNumber.equals("1")) {
				return rowSet.getDouble("reissueoverpay");
			}else if(projectNumber.equals("2")) {
				return rowSet.getDouble("reissuerestoverpay");
			}else if(projectNumber.equals("3")) {
				return rowSet.getDouble("reissueholidaysoverpay");
			}else if(projectNumber.equals("4")) {
				return rowSet.getDouble("reissueunleavededuction");
			}
		} 
		return 0.0D;
	}
	
	
	/**
	 * 获取工资提报信息
	 * @param personId
	 * @param cmpItemId
	 * @param months
	 * @return
	 * @throws BOSException 
	 * @throws SQLException
	 * 取工资提报,对于AssignType来说, 0表示兼职,1表示主要任职,3表示 无
	 * 参数takeType 0表示主职,1表示兼职
 	 */
	public double getPutForward(Map<String, Object> paramMap, HashMap<String, Object> tempMap, CalSchemeInfo calSchemeInfo ,String projectNumber,String date, String takeType, String isHour, String mainTableRowFilter) throws BOSException, SQLException {
		Context ctx = Tools.getInstance().getCtx();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String personId = (String)paramMap.get("T_HR_SCMPCALTABLE_FPERSONID");
		int year = 0;
		int month = 0;
		try {
			Date parse = format.parse(date);
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(parse);
			year = calendar.get(Calendar.YEAR);
			month = calendar.get(Calendar.MONTH)+1;
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		String assignType = "";
		if(takeType.equals("0")) {
			assignType = "1";
		}else {
			assignType = "0";
		}
		//查询全职、兼职总工时数
		if(StringUtils.isNotBlank(isHour)) {
			String hourSql = "SELECT sum(b.CFTOTALWORKHOURS) sumHour FROM T_HR_SBatchSubmitShemeBill a "
					+ "left join T_HR_SchemeBillEntry b on a.fid=b.fbillid "
					+ "left join T_HR_SCmpEmpORelation c on b.FCMPEMPORELATIONID=c.fid "
					+ "left join T_HR_EmpPostExperienceHis d on d.fid=c.femppostexphisid "  
					+ "where year(b.FEFFECTDATE)="+year+" and month(b.FEFFECTDATE)="+month+" "
					+ "and d.FASSIGNTYPE='"+assignType+"' and a.FBILLSTATE = '3' and b.fpersonid='"+personId+"'";
			logger.error("查询员工全职总工时SQL："+hourSql);
			IRowSet schemeRow = DbUtil.executeQuery(ctx, hourSql);
			while(schemeRow.next()) {
				return schemeRow.getDouble("sumHour");
			}
		}
		
		//全职
		if(takeType.equals("0")) {
			if(StringUtils.isBlank(projectNumber)) {
				String amountSql = "SELECT sum(b.CFTOTALAMOUNT) sumAmount FROM T_HR_SBatchSubmitShemeBill a "
						+ "left join T_HR_SchemeBillEntry b on a.fid=b.fbillid "
						+ "left join T_HR_SCmpEmpORelation c on b.FCMPEMPORELATIONID=c.fid "
						+ "left join T_HR_EmpPostExperienceHis d on d.fid=c.femppostexphisid "  
						+ "where year(b.FEFFECTDATE)="+year+" and month(b.FEFFECTDATE)="+month+" "
						+ "and d.FASSIGNTYPE='1' and a.FBILLSTATE = '3' and b.fpersonid='"+personId+"'";
				logger.error("查询员工全职总金额SQL："+amountSql);
				IRowSet schemeRow = DbUtil.executeQuery(ctx, amountSql);
				while(schemeRow.next()) {
					return schemeRow.getDouble("sumAmount");
				}
			}
			
			/*String projectFieldSql = "SELECT fieldsn FROM T_HR_SCalSubmitItem where FNUMBER = '"+projectNumber+"'";
			IRowSet fieldRow = DbUtil.executeQuery(ctx, projectFieldSql);
			String fieldNo = "";
			while(fieldRow.next()) {
				fieldNo = "S"+fieldRow.getString("fieldsn");
			}
			if(StringUtils.isEmpty(fieldNo)) {
				logger.error("根据提报项目编码未查到提报项目");
				return 0.0D;
			} 
			
			String tableSql = "SELECT c.FBILLENTRYTABLENAME FROM T_HR_SCalSubmitItem a "
					+ "left join T_HR_SCalSubmitSchemeItem b on a.fid=b.fcalsubmititemid "
					+ "left join T_HR_SCalShemeTableRelation c on b.FCALSUBMITSCHEMEID = c.fsubmitschemeid "
					+ "where a.FNUMBER = '"+projectNumber+"'";
			logger.error("查询提报项目对应表名SQL："+tableSql);
			IRowSet tableRow = DbUtil.executeQuery(ctx, tableSql);
			String tableName = "";
			while(tableRow.next()) {
				tableName = tableRow.getString("FBILLENTRYTABLENAME");
			}
			
			String schemeSql = "SELECT sum(k."+fieldNo+") sumHours FROM T_HR_SBatchSubmitShemeBill a "
					+ "left join T_HR_SchemeBillEntry b on a.fid=b.fbillid "
					+ "left join T_HR_SCmpEmpORelation c on b.FCMPEMPORELATIONID=c.fid "
					+ "left join T_HR_EmpPostExperienceHis d on d.fid=c.femppostexphisid "
					+ "left join T_HR_SCalSubmitScheme e on e.fid=a.fsubmitschemeid  "
					+ "left join T_HR_SCalShemeTableRelation g on g.fsubmitschemeid=e.fid  "
					+ "left join T_HR_SCalSubmitSchemeItem s on s.FCALSUBMITSCHEMEID = e.fid "
					+ "left join T_HR_SCalSubmitItem h on h.fid=s.fcalsubmititemid "
					+ "left join "+tableName+" k on k.fid = b.fid "  
					+ "where year(b.FEFFECTDATE)="+year+" and month(b.FEFFECTDATE)="+month+" and d.FASSIGNTYPE='1' "
					+ "and h.fnumber='"+projectNumber+"' and a.FBILLSTATE = '3' and b.fpersonid='"+personId+"'";
			logger.error("查询项目对应工时SQL："+schemeSql);
			IRowSet schemeRow = DbUtil.executeQuery(ctx, schemeSql);
			while(schemeRow.next()) {
				return schemeRow.getDouble("sumHours");
			}*/
		}else {
			String amountSql = "SELECT sum(b.CFTOTALAMOUNT) sumAmount FROM T_HR_SBatchSubmitShemeBill a "
					+ "left join T_HR_SchemeBillEntry b on a.fid=b.fbillid "
					+ "left join T_HR_SCmpEmpORelation c on b.FCMPEMPORELATIONID=c.fid "
					+ "left join T_HR_EmpPostExperienceHis d on d.fid=c.femppostexphisid "  
					+ "where year(b.FEFFECTDATE)="+year+" and month(b.FEFFECTDATE)="+month+" "
					+ "and d.FASSIGNTYPE='0' and a.FBILLSTATE = '3' and b.fpersonid='"+personId+"'";
			logger.error("查询员工兼职总金额SQL："+amountSql);
			IRowSet schemeRow = DbUtil.executeQuery(ctx, amountSql);
			while(schemeRow.next()) {
				return schemeRow.getDouble("sumAmount");
			}
		}
		return 0.0D;
	}
	
	// 0 兼职 1 全职
	/**
	 * 获取月份最新的离职补偿金
	 * @param months
	 * @return
	 * @throws BOSException 
	 * @throws SQLException 
	 */
	public double getSeparationAllowance(String months ) throws BOSException, SQLException {
		Context ctx = Tools.getInstance().getCtx();
		String sql = "select top 1 * from CT_COM_Compensationthreshold where CFEffectivedate <= '" + months + "' order by CFEffectivedate desc";
		System.out.println("获取月份最新的离职补偿金sql：" + sql);
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if(rowSet.next()) {
			return rowSet.getDouble("FName_l2");
		}
		return 0.0;
	}
	
	public double getMianPosHour(Map<String, Object> paramMap, HashMap<String, Object> tempMap, CalSchemeInfo calSchemeInfo ,String date, String mainTableRowFilter) throws BOSException, SQLException {
		logger.error("获取主要任职时薪日期参数："+date);
		Context ctx = Tools.getInstance().getCtx();
		String personId = (String)paramMap.get("T_HR_SCMPCALTABLE_FPERSONID");
		String sql = "SELECT cfhourlywage FROM T_HR_EmpOrgRelation where fpersonid = '"+personId+"' and fassignType = '1' and fislatestinaday = '1' and feffdt <= '"+date+"' and fleffdt >= '"+date+"'";
		logger.error("获取主要任职时薪SQL："+sql);
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		int count = 0;
		Double hour = 0.0D;
		if(rowSet.next()) {
			hour = rowSet.getDouble("cfhourlywage");
			count++;
		}
		logger.error("时薪："+hour+", 次数："+count);
		if(count>1) {
			return 0.0D;
		}
		return hour;
	}
	
	/**
	 * 根据日期段获取人员离职日期
	 * @param paramMap
	 * @param tempMap
	 * @param calSchemeInfo
	 * @param date
	 * @param mainTableRowFilter
	 * @return
	 * @throws BOSException
	 * @throws SQLException
	 */
	public String getDepartDate(Map<String, Object> paramMap, HashMap<String, Object> tempMap, CalSchemeInfo calSchemeInfo ,String beginDate, String endDate, String mainTableRowFilter) throws BOSException, SQLException {
		logger.error("获取离职日期参数：开始日期："+beginDate+",结束日期："+endDate);
		Context ctx = Tools.getInstance().getCtx();
		String personId = (String)paramMap.get("T_HR_SCMPCALTABLE_FPERSONID");
		String departDate = "";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String sql = "SELECT top 1 b.FBIZDATE FROM T_HR_ResignBizBill a left join T_HR_ResignBizBillEntry b on a.FID = b.FBILLID where b.FBIZDATE >= '"+beginDate+"' and b.FBIZDATE <= '"+endDate+"' and a.FBILLSTATE = '3' and b.FPERSONID = '"+personId+"'";
		logger.error("获取离职日期SQL："+sql);
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if(rowSet.next()) {
			Date date = rowSet.getDate("FBIZDATE");
			departDate = sdf.format(date);
		}
		if(StringUtils.isNotBlank(departDate)) {
			logger.error("人员ID："+personId+",离职日期："+departDate);
		}
		return departDate;
	}
}