public static void SychEmployeeInfo() { foreach (EmpInfo emp in EmpInfo.GetAll()) { EmployeeInfo empInfo = AddEmployeeInfo(emp.员工编号); empInfo.姓名 = emp.姓名; empInfo.出生日期 = emp.出生日期; empInfo.性别 = emp.性别; empInfo.身份证号 = emp.身份证号; empInfo.公司 = emp.公司; empInfo.部门 = emp.部门; empInfo.状态 = emp.状态; empInfo.职位代码 = emp.职位代码; empInfo.职务代码 = emp.职务代码; empInfo.职务等级 = emp.职务等级; empInfo.集合 = emp.集合; empInfo.薪资体系 = emp.薪资体系; empInfo.薪等 = emp.薪等; empInfo.薪级 = emp.薪级; empInfo.薪资组 = emp.薪资组; empInfo.个月薪资组 = emp.个月薪资组; empInfo.银行账号 = emp.银行账号; empInfo.帐户名称 = emp.帐户名称; empInfo.财务公司 = emp.财务公司; empInfo.财务部门 = emp.财务部门; empInfo.财务部门序号 = emp.财务部门序号; empInfo.员工序号 = emp.员工序号; empInfo.职等 = GetGrade(empInfo); empInfo.是管培生 = PsHelper.CheckIsGuanPeiSheng(emp.员工编号); empInfo.离职时间 = emp.离职时间; empInfo.次同步时间 = DateTime.Now; empInfo.Save(); } }
//根据日历组获取所有工资项 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); }
//获取值列表 public static List <EmpInfo> GetAll() { List <EmpInfo> list = new List <EmpInfo>(); OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString()); using (conn) { OleDbDataReader rs = null; try { conn.Open(); using (OleDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT B.HIGHEST_EDUC_LVL, B.BIRTHPLACE, ACTION, EFFDT, B.EMPLID AS EMPLID, B.NAME, B.BIRTHDATE, CASE WHEN B.SEX = 'M' THEN '男' ELSE '女' END AS SEX, NATIONAL_ID AS NID,DEPTID,HR_STATUS,COMPANY,A.POSITION_NBR,A.JOBCODE,A.SUPV_LVL_ID FROM PS_C_EMP_JOB_VW A LEFT JOIN PS_C_EMP_BASE_VW B ON B.EMPLID = A.EMPLID LEFT JOIN PS_PERS_WRKLIF_CHN C ON C.EMPLID = A.EMPLID WHERE EMPL_RCD = 0"; rs = cmd.ExecuteReader(); while (rs.Read()) { EmpInfo emp = new EmpInfo(); emp.员工编号 = (string)rs["EMPLID"]; emp.姓名 = (string)rs["NAME"]; if (rs["BIRTHDATE"] != DBNull.Value) { emp.出生日期 = Convert.ToDateTime(rs["BIRTHDATE"]); } emp.性别 = (string)rs["SEX"]; emp.身份证号 = (string)rs["NID"]; emp.公司 = (string)rs["COMPANY"]; emp.部门 = (string)rs["DEPTID"]; emp.状态 = (string)rs["HR_STATUS"]; emp.职位代码 = (string)rs["POSITION_NBR"]; emp.职务代码 = (string)rs["JOBCODE"]; emp.职务等级 = (string)rs["SUPV_LVL_ID"]; emp.学历 = (string)rs["HIGHEST_EDUC_LVL"]; emp.籍贯 = (string)rs["BIRTHPLACE"]; if ((string)rs["ACTION"] == "TER") { emp.离职时间 = Convert.ToDateTime(rs["EFFDT"]); } list.Add(emp); } rs.Close(); } using (OleDbCommand cmd = conn.CreateCommand()) { foreach (EmpInfo emp in list) { cmd.CommandText = String.Format("SELECT A.SAL_ADMIN_PLAN, A.GRADE, A.STEP, A.GP_PAYGROUP,A.SETID_SALARY FROM PS_JOB A WHERE EMPLID='{0}' AND A.EMPL_RCD=0 AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= SYSDATE) order by A.EFFDT desc", emp.员工编号); rs = cmd.ExecuteReader(); if (rs.Read()) { emp.集合 = (string)rs["SETID_SALARY"]; emp.薪资体系 = (string)rs["SAL_ADMIN_PLAN"]; emp.薪等 = (string)rs["GRADE"]; emp.薪级 = Convert.ToInt32(rs["STEP"]); emp.薪资组 = (string)(rs["GP_PAYGROUP"]); } rs.Close(); cmd.CommandText = String.Format("SELECT A.GP_PAYGROUP FROM PS_JOB A WHERE A.EMPL_RCD=0 AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE EMPLID='{0}' and A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT < date'{1}') order by A.EFFDT desc", emp.员工编号, MyHelper.GetPrevMonth1Day().ToString("yyyy-M-d")); rs = cmd.ExecuteReader(); if (rs.Read()) { emp.个月薪资组 = (string)(rs["GP_PAYGROUP"]); } rs.Close(); cmd.CommandText = String.Format("SELECT ACCOUNT_EC_ID, ACCOUNT_NAME FROM SYSADM.PS_PYE_BANKACCT WHERE EMPLID='{0}' AND ACCOUNT_TYPE_PYE='A'", emp.员工编号); rs = cmd.ExecuteReader(); if (rs.Read()) { emp.银行账号 = (string)rs["ACCOUNT_EC_ID"]; emp.帐户名称 = (string)rs["ACCOUNT_NAME"]; } rs.Close(); cmd.CommandText = String.Format("SELECT B.SEQ_NBR AS DEPT_SEQ, A.COMPANY, A.C_GP_DEPT, A.SEQ_NBR AS EMP_SEQ FROM SYSADM.PS_C_GP_DEPT_EMP_C A, SYSADM.PS_C_GP_DEPT B where A.Company = B.Company AND A.c_gp_dept = B.c_gp_dept and EMPLID='{0}' and rownum=1 order by a.effdt desc", emp.员工编号); rs = cmd.ExecuteReader(); if (rs.Read()) { CompanyInfo company = CompanyInfo.Get((string)rs["COMPANY"]); if (company != null) { emp.财务公司 = company.公司名称; } emp.财务部门 = (string)rs["C_GP_DEPT"]; emp.财务部门序号 = Convert.ToInt32(rs["DEPT_SEQ"]); emp.员工序号 = Convert.ToInt32(rs["EMP_SEQ"]); } rs.Close(); } } } finally { if (rs != null) { rs.Close(); } conn.Close(); } } empList = list; return(list); }