//获取所有工资项
public static List <SalResultItem> GetList(string calRunId, string payGroup)
{
List <SalResultItem> list = new List <SalResultItem>();
CalRunInfo calRun = CalRunInfo.Get(calRunId);
if (calRun == null)
{
return(list);
}
OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString());
using (conn)
{
OleDbDataReader rs = null;
try
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
string condition = "";
if (string.IsNullOrEmpty(payGroup) == false)
{
condition = " AND gp_paygroup='" + payGroup + "'";
}
cmd.CommandText = String.Format("SELECT A.EMPLID, A.cal_run_id,a.gp_paygroup, cal_id, A.PIN_NUM, P.PIN_NM, P.DESCR, A.CALC_RSLT_VAL FROM SYSADM.PS_GP_RSLT_ERN_DED A LEFT JOIN SYSADM.PS_GP_PIN P ON A.PIN_NUM = P.PIN_NUM WHERE A.cal_run_id = '{0}' {1} ORDER BY A.EMPLID", calRunId, condition);
rs = cmd.ExecuteReader();
while (rs.Read())
{
SalResultItem item = new SalResultItem();
item.年度 = calRun.年度;
item.月份 = calRun.月份;
item.日历组 = (string)rs["cal_run_id"];
item.日历 = (string)rs["cal_id"];
item.薪资组 = ((string)rs["gp_paygroup"]).Trim();
item.员工编号 = (string)rs["EMPLID"];
item.元素编号 = Convert.ToDecimal(rs["PIN_NUM"]);
item.英文名称 = (string)rs["PIN_NM"];
item.中文名称 = (string)rs["DESCR"];
item.金额 = Convert.ToDecimal(rs["CALC_RSLT_VAL"]);
list.Add(item);
}
}
}
finally
{
if (rs != null)
{
rs.Close();
}
conn.Close();
}
}
return(list);
}
public static StringBuilder SychSalaryResultItem(string calRunId, string payGroup)
{
StringBuilder sb = new StringBuilder();
CalRunInfo calRun = CalRunInfo.Get(calRunId);
if (calRun == null)
{
return(sb);
}
if (DeleteAll(calRunId, payGroup))
{
List <SalResultItem> list = SalResultItem.GetList(calRunId, payGroup);
foreach (SalResultItem sri in list)
{
try
{
SalaryResultItem item = new SalaryResultItem();
item.年度 = calRun.年度;
item.月份 = calRun.月份;
item.日历组 = sri.日历组;
item.日历 = sri.日历;
item.薪资组 = sri.薪资组;
item.员工编号 = sri.员工编号;
item.元素编号 = sri.元素编号;
item.英文名称 = sri.英文名称;
item.中文名称 = sri.中文名称;
item.金额 = sri.金额;
item.类别 = item.GetCategory();
item.次同步时间 = DateTime.Now;
item.Save();
}
catch
{
sb.Append("同步工资明细失败:" + sri);
}
}
}
else
{
sb.Append("删除上次同步的数据失败");
}
return(sb);
}
//获取指定的日历组
public static CalRunInfo Get(string cal_run_id)
{
List <CalRunInfo> list = new List <CalRunInfo>();
OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString());
using (conn)
{
OleDbDataReader rs = null;
try
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
string sql = String.Format("SELECT DISTINCT A.CAL_RUN_ID,A.DESCR, RUN_FINALIZED_IND, B.CAL_PRD_ID, C.PRD_BGN_DT, C.PRD_END_DT FROM SYSADM.PS_GP_CAL_RUN A LEFT JOIN SYSADM.PS_GP_CAL_RUN_DTL B ON A.CAL_RUN_ID = B.CAL_RUN_ID LEFT JOIN SYSADM.PS_GP_CAL_PRD C ON C.CAL_PRD_ID = B.CAL_PRD_ID WHERE A.CAL_RUN_ID='{0}'", cal_run_id);
cmd.CommandText = sql;
rs = cmd.ExecuteReader();
if (rs.Read())
{
CalRunInfo item = new CalRunInfo();
item.日历组编号 = (string)rs["CAL_RUN_ID"];
item.日历组名称 = (string)rs["DESCR"];
item.期间 = (string)rs["CAL_PRD_ID"];
item.开始日期 = Convert.ToDateTime(rs["PRD_BGN_DT"]);
item.结束日期 = Convert.ToDateTime(rs["PRD_END_DT"]);
item.年度 = item.开始日期.Year;
item.月份 = item.开始日期.Month;
return(item);
}
}
}
finally
{
if (rs != null)
{
rs.Close();
}
conn.Close();
}
}
return(null);
}
//获取指定年份的日历组
public static List <CalRunInfo> GetList(DateTime start, DateTime end)
{
List <CalRunInfo> list = new List <CalRunInfo>();
OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString());
using (conn)
{
OleDbDataReader rs = null;
try
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
string sql = String.Format("SELECT DISTINCT A.CAL_RUN_ID,A.DESCR, RUN_FINALIZED_IND, B.CAL_PRD_ID, C.PRD_BGN_DT, C.PRD_END_DT FROM SYSADM.PS_GP_CAL_RUN A LEFT JOIN SYSADM.PS_GP_CAL_RUN_DTL B ON A.CAL_RUN_ID = B.CAL_RUN_ID LEFT JOIN SYSADM.PS_GP_CAL_PRD C ON C.CAL_PRD_ID = B.CAL_PRD_ID WHERE C.PRD_BGN_DT >= date'{0:yyyy-M-d}' AND C.PRD_END_DT < date'{1:yyyy-M-d}' order by C.PRD_BGN_DT desc", start, end);
cmd.CommandText = sql;
rs = cmd.ExecuteReader();
while (rs.Read())
{
CalRunInfo item = new CalRunInfo();
item.日历组编号 = (string)rs["CAL_RUN_ID"];
item.日历组名称 = (string)rs["DESCR"];
item.期间 = (string)rs["CAL_PRD_ID"];
item.开始日期 = Convert.ToDateTime(rs["PRD_BGN_DT"]);
item.结束日期 = Convert.ToDateTime(rs["PRD_END_DT"]);
item.年度 = item.开始日期.Year;
item.月份 = item.开始日期.Month;
list.Add(item);
}
}
}
finally
{
if (rs != null)
{
rs.Close();
}
conn.Close();
}
}
return(list);
}
public PersonalTax(SalaryResult salary)
{
表工资 = salary;
封闭工资 = PrivateSalary.GetPrivateSalary(salary.员工编号, salary.年度, salary.月份);
CalRunInfo cal = CalRunInfo.Get(salary.日历组);
年 = cal.年度;
月 = cal.月份;
期间_开始 = cal.开始日期;
期间_结束 = cal.结束日期;
期间 = String.Format("{0}年{1}", 年, 月) + "月";
发放单位 = salary.财务公司;
员工编号 = salary.员工编号;
姓名 = salary.姓名;
身份证号 = salary.身份证号;
养老保险个人缴纳金额 = salary.养老保险个人缴纳;
医疗保险个人缴纳金额 = salary.基本医疗个人缴纳 + salary.大病医疗个人缴纳金额;
失业保险个人缴纳金额 = salary.失业保险个人缴纳;
住房公积金个人缴纳金额 = salary.住房公积金个人缴纳;
应税工资额 = 表工资.应税工资额;
个人所得税 = 封闭工资 == null ? 表工资.个人所得税金额 : 封闭工资.个人所得税;
工资发放总额 = 表工资.表工资总额 + 表工资.未休年休假工资;
//如果有封闭工资
if (封闭工资 != null)
{
应税工资额 = 封闭工资.总应税工资;
工资发放总额 = 封闭工资.工资发放总额;
}
个税起征点 = PsHelper.GetPersonTaxPoint(期间_开始);
decimal taxIncome = 应税工资额 - 个税起征点;
if (taxIncome > 0)
{
TaxInfo tax = TaxInfo.Get(taxIncome);
税率 = tax.税率;
速算扣除数 = tax.速算扣除数;
}
}
public static SalaryAuditingResult AddSalaryAuditingResult(string payGroup, string calRunId)
{
SalaryAuditingResult result = GetSalaryAuditingResult(payGroup, calRunId);
if (result == null)
{
CalRunInfo cal = CalRunInfo.Get(calRunId);
result = new SalaryAuditingResult();
result.标识 = Guid.NewGuid();
result.薪资组 = payGroup;
result.日历组 = calRunId;
result.年 = cal.年度;
result.月 = cal.月份;
result.创建时间 = DateTime.Now;
result.Save();
}
return(result);
}
public static StringBuilder SychSalaryResult(string calRunId, string payGroup)
{
StringBuilder sb = new StringBuilder();
CalRunInfo calRun = CalRunInfo.Get(calRunId);
if (calRun == null)
{
sb.Append(String.Format("指定的日历组（{0}）不存在，无法同步。", calRunId));
return(sb);
}
if (CheckCalRunFinalized(calRunId) == false)
{
sb.Append(String.Format("日历组（{0}）的上表工资计算未完成，不能同步。", calRunId));
return(sb);
}
List <SalResult> list = SalResult.GetList(calRunId, payGroup);
//删除历史数据
SalaryAuditingResult.ClearAuditingResult(calRunId, payGroup);
#region 保存PS系统的上表工资数据
foreach (SalResult sr in list)
{
if (!SychSalaryResult(sr))
{
//删除历史数据
SalaryAuditingResult.ClearAuditingResult(calRunId, payGroup);
sb.Append("同步工资结果失败:" + sr.ToString <SalResult>());
break;
}
}
#endregion
return(sb);
}
//根据日历组获取所有工资项
public static List <SalResult> GetList(string calRunId, string payGroup)
{
List <SalResult> list = new List <SalResult>();
CalRunInfo calRun = CalRunInfo.Get(calRunId);
if (calRun == null)
{
return(list);
}
OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString());
using (conn)
{
OleDbDataReader rs = null;
try
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
string payGroupCondition = "";
if (string.IsNullOrEmpty(payGroup) == false)
{
payGroupCondition = String.Format(" AND GP_PAYGROUP = '{0}'", payGroup);
}
cmd.CommandText = String.Format("SELECT B.NAME,A.* FROM SYSADM.PS_C_WA_PAY_RSLT A LEFT JOIN PS_PERSONAL_DATA B ON A.EMPLID=B.EMPLID WHERE CAL_RUN_ID='{0}' {1} ORDER BY A.EMPLID", calRunId, payGroupCondition);
rs = cmd.ExecuteReader();
while (rs.Read())
{
SalResult item = new SalResult();
#region 日历信息
item.日历组 = calRunId;
item.期间 = calRun.期间;
item.年度 = calRun.年度;
item.月份 = calRun.月份;
#endregion
#region 员工信息
item.姓名 = MyHelper.RemoveNumber((string)rs["NAME"]);
item.员工编号 = (string)rs["EMPLID"];
item.员工类型 = (string)rs["C_CHR027"];
EmpInfo empInfo = EmpInfo.员工表.Find(a => a.员工编号 == item.员工编号);
if (empInfo != null)
{
item.身份证号 = empInfo.身份证号;
item.财务公司 = empInfo.财务公司;
item.财务部门 = empInfo.财务部门;
item.财务部门序号 = empInfo.财务部门序号;
item.员工序号 = empInfo.员工序号;
item.银行账号 = empInfo.银行账号;
item.帐户名称 = empInfo.帐户名称;
}
#endregion
#region 所属机构
item.公司编号 = (string)rs["COMPANY"];
item.机构编号 = (string)rs["DEPTID"];
CompanyInfo company = CompanyInfo.Get(item.公司编号);
if (company != null)
{
item.公司名称 = company.公司名称;
}
DeptInfo dept = DeptInfo.Get(item.机构编号);
if (dept != null && dept.所在部门 != null)
{
item.部门编号 = dept.所在部门.部门编号;
item.部门名称 = dept.所在部门.部门名称;
}
#endregion
#region 薪酬体系
item.薪资组 = (string)rs["GP_PAYGROUP"];
item.薪资集合 = (string)rs["SETID_SALARY"];
item.薪酬体系编号 = (string)rs["SAL_ADMIN_PLAN"];
item.薪等编号 = (string)rs["GRADE"];
item.薪级编号 = Convert.ToString(rs["STEP"]);
#endregion
#region 职务数据
item.职务代码 = (string)rs["JOBCODE"];
//2018-9-10 职务等级以15号为界
//item.职务等级 = (string)rs["SUPV_LVL_ID"];
item.职务等级 = GetSupvLvlId(item.员工编号, item.年度, item.月份);
item.职位编号 = (string)rs["POSITION_NBR"];
item.班别 = (string)rs["SHIFT"];
#endregion
#region 出勤情况
item.企业排班天数 = Convert.ToDecimal(rs["C_AMT202"]);
item.法定工作日天数 = Convert.ToDecimal(rs["C_AMT243"]);
item.实际出勤天数 = Convert.ToDecimal(rs["C_AMT198"]);
item.法定工作日出勤天数 = Convert.ToDecimal(rs["C_AMT199"]);
item.法定节假日出勤天数 = Convert.ToDecimal(rs["C_AMT201"]);
item.休息日出勤天数 = Convert.ToDecimal(rs["C_AMT203"]);
item.月综合出勤天数 = Convert.ToDecimal(rs["C_AMT204"]);
item.工作日延长出勤小时数 = Convert.ToDecimal(rs["C_AMT205"]);
#endregion
#region 出勤工资
item.法定工作日出勤工资 = Convert.ToDecimal(rs["C_AMT005"]);
item.法定节假日出勤工资 = Convert.ToDecimal(rs["C_AMT006"]);
item.休息日出勤工资 = Convert.ToDecimal(rs["C_AMT007"]);
item.月综合出勤工资 = Convert.ToDecimal(rs["C_AMT009"]);
item.工作日延长工作出勤工资 = Convert.ToDecimal(rs["C_AMT010"]);
#endregion
item.未休年休假工资 = Convert.ToDecimal(rs["C_AMT134"]);
#region 社保缴纳
item.养老保险个人缴纳金额 = Convert.ToDecimal(rs["C_AMT170"]);
item.医疗保险个人缴纳金额 = Convert.ToDecimal(rs["C_AMT171"]);
item.失业保险个人缴纳金额 = Convert.ToDecimal(rs["C_AMT172"]);
item.住房公积金个人缴纳金额 = Convert.ToDecimal(rs["C_AMT173"]);
item.大病医疗个人缴纳金额 = Convert.ToDecimal(rs["C_AMT292"]);
//社保合计
item.社保个人缴纳金额 = Convert.ToDecimal(rs["C_AMT258"]);
item.社保公司缴纳金额 = Convert.ToDecimal(rs["C_AMT259"]);
//补缴 2015.9.14
item.养老保险个人补缴金额 = Convert.ToDecimal(rs["C_AMT174"]);
item.医疗保险个人补缴金额 = Convert.ToDecimal(rs["C_AMT175"]);
item.失业保险个人补缴金额 = Convert.ToDecimal(rs["C_AMT176"]);
item.住房公积金个人补缴金额 = Convert.ToDecimal(rs["C_AMT177"]);
#endregion
item.个人所得税金额 = Convert.ToDecimal(rs["C_AMT178"]); //C_AMT265 总的个税（含封闭工资）
item.代垫费用 = Convert.ToDecimal(rs["C_AMT262"]);
item.挂钩效益工资 = Convert.ToDecimal(rs["C_AMT136"]);
item.其他所得 = Convert.ToDecimal(rs["C_AMT138"]);
item.其他扣款 = Convert.ToDecimal(rs["C_AMT196"]);
item.预留风险金 = Convert.ToDecimal(rs["C_AMT241"]);
item.出勤工资 = Convert.ToDecimal(rs["C_AMT252"]);
item.实得满勤奖 = Convert.ToDecimal(rs["C_AMT017"]);
item.应得满勤奖 = Convert.ToDecimal(rs["C_AMT232"]);
item.表工资 = Convert.ToDecimal(rs["C_AMT002"]);
item.设定工资 = Convert.ToDecimal(rs["C_AMT003"]);
item.基准工资 = Convert.ToDecimal(rs["C_AMT004"]);
item.特殊社保的基准工资 = Convert.ToDecimal(rs["C_AMT278"]);
item.基数等级与基准工资差额 = Convert.ToDecimal(rs["C_AMT011"]);
//item.工资降级 = Convert.ToDecimal(rs["C_AMT008"]) - item.上表工资; //2015.5.20 封闭上表工资 - 上表工资
//2017.7.10 直接读取工资降级数据
item.工资降级 = PsHelper.GetPayDegrade(item.员工编号, calRun.开始日期);
if (item.工资降级 < 0)
{
item.工资降级 = 0; //2015.9.24
}
#region 累加器
item.出勤工资 = Convert.ToDecimal(rs["C_AMT252"]);
item.津贴补助 = Convert.ToDecimal(rs["C_AMT253"]);
item.综合考核工资 = Convert.ToDecimal(rs["C_AMT254"]);
item.奖项 = Convert.ToDecimal(rs["C_AMT255"]);
item.扣项 = Convert.ToDecimal(rs["C_AMT256"]);
#endregion
item.交通餐饮补助 = Convert.ToDecimal(rs["C_AMT248"]);
item.应税工资额 = Convert.ToDecimal(rs["C_AMT260"]);
//含年休假工资（260是不含年休假的工资总额）
//即：合计应税工资额 = 应税工资额 + 年休假工资
item.合计应税工资额 = Convert.ToDecimal(rs["C_AMT261"]);
item.表工资总额 = Convert.ToDecimal(rs["C_AMT257"]);
item.实发工资总额 = Convert.ToDecimal(rs["C_AMT263"]);
list.Add(item);
}
}
}
finally
{
if (rs != null)
{
rs.Close();
}
conn.Close();
}
}
return(list);
}
/// <summary>
/// 同步上月工资明细
/// </summary>
/// <returns></returns>
public static StringBuilder SychSalaryResult()
{
List <CalRunInfo> cals = new List <CalRunInfo>();
StringBuilder sb = new StringBuilder();
DateTime prevMonth = DateTime.Now.AddMonths(-1);
DateTime prd_begin = new DateTime(prevMonth.Year, prevMonth.Month, 1);
OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString());
using (conn)
{
OleDbDataReader rs = null;
try
{
conn.Open();
using (OleDbCommand cmd = conn.CreateCommand())
{
string sql = String.Format("select distinct A.CAL_RUN_ID,A.DESCR, RUN_FINALIZED_IND, B.CAL_PRD_ID, C.PRD_BGN_DT, C.PRD_END_DT from SYSADM.ps_GP_CAL_RUN A left join SYSADM.ps_GP_CAL_RUN_DTL B ON A.cal_run_id = B.cal_run_id left join SYSADM.ps_GP_CAL_PRD C ON C.cal_prd_id = B.CAL_PRD_ID where C.PRD_BGN_DT = date'{0:yyyy-M-d}'", prd_begin);
cmd.CommandText = sql;
rs = cmd.ExecuteReader();
while (rs.Read())
{
bool finalized = (string)rs["RUN_FINALIZED_IND"] == "Y";
if (finalized)
{
string cal_run_id = (string)rs["CAL_RUN_ID"];
CalRunInfo cal = CalRunInfo.Get(cal_run_id);
if (cal != null)
{
cals.Add(cal);
WageLoan.AutoGenerateMonthlyWageLoanItems(cal.年度, cal.月份);
}
}
}
rs.Close();
foreach (CalRunInfo cal in cals)
{
string cal_run_id = cal.日历组编号;
foreach (string groupId in cal.薪资组列表)
{
#if (DEBUG)
StringBuilder msgBuilder = SychSalaryResult(cal_run_id, groupId);
sb.Append(msgBuilder.ToString());
StringBuilder msgBuilderItem = SalaryResultItem.SychSalaryResultItem(cal_run_id, groupId);
sb.Append(msgBuilderItem.ToString());
#else
SalaryAuditingResult sar = SalaryAuditingResult.AddSalaryAuditingResult(groupId, cal_run_id);
if (sar.表工资已审核)
{
continue;
}
if (sar.已审核 || sar.已冻结)
{
sb.Append(String.Format("薪资组{0}, 日历组（{0}）的工资已审核或者已冻结，不能重新同步。", groupId, cal_run_id));
return(sb);
}
else
{
StringBuilder msgBuilder = SychSalaryResult(cal_run_id, groupId);
sb.Append(msgBuilder.ToString());
StringBuilder msgBuilderItem = SalaryResultItem.SychSalaryResultItem(cal_run_id, groupId);
sb.Append(msgBuilderItem.ToString());
}
#endif
}
}
}
}
finally
{
if (rs != null)
{
rs.Close();
}
conn.Close();
}
}
return(sb);
}