//获取值列表
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);
}