public static List <TraineeInfo> GetAll()
{
List <TraineeInfo> list = new List <TraineeInfo>();
OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString());
using (conn)
{
OleDbDataReader rs = null;
try
{
conn.Open();
//获取管培生配置
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT EMPLID FROM PS_C_EMP_APPLYJOB WHERE C_CAMPUSRECRUIT_YN='Y'";
rs = cmd.ExecuteReader();
while (rs.Read())
{
string emplid = (string)rs["EMPLID"];
TraineeInfo t = Get(emplid);
list.Add(t);
}
rs.Close();
}
}
finally
{
if (rs != null)
{
rs.Close();
}
conn.Close();
}
}
return(list);
}
public static TraineeInfo Get(string emplid)
{
TraineeInfo item = null;
OleDbConnection conn = new OleDbConnection(MyHelper.GetPsConnectionString());
using (conn)
{
OleDbDataReader rs = null;
try
{
conn.Open();
//获取管培生配置
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT EMPLID, C_CAMPUSRECRUIT_L FROM PS_C_EMP_APPLYJOB WHERE C_CAMPUSRECRUIT_YN='Y' AND EMPLID='" + emplid + "'";
rs = cmd.ExecuteReader();
if (rs.Read())
{
string grade = (string)rs["C_CAMPUSRECRUIT_L"];
if (grade == "A")
{
grade = "一级";
}
if (grade == "B")
{
grade = "二级";
}
if (grade == "C")
{
grade = "三级";
}
item = new TraineeInfo();
item.员工编号 = emplid;
item.岗位级别 = grade;
}
rs.Close();
}
//获取学历信息
if (item != null)
{
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT emplid,c.descrshort, descr,f.effdt,school_descr, major_descr FROM sysadm.PS_JPM_PROFILE A, sysadm.PS_JPM_JP_ITEMS F LEFT JOIN SYSADM.PS_JPM_CAT_ITEMS C ON F.JPM_CAT_ITEM_ID = C.JPM_CAT_ITEM_ID WHERE A.JPM_PROFILE_ID = F.JPM_PROFILE_ID AND F.JPM_CAT_TYPE = 'EDLVLACHV' AND F.EFFDT = (SELECT MAX(BF.EFFDT) FROM sysadm.PS_JPM_PROFILE BE, sysadm.PS_JPM_JP_ITEMS BF WHERE BE.JPM_PROFILE_ID = BF.JPM_PROFILE_ID AND BE.EMPLID = A.EMPLID AND BF.JPM_CAT_TYPE = 'EDLVLACHV' AND BF.EFFDT <= SYSDATE AND BF.JPM_YN_1 = 'Y') AND F.JPM_YN_1 = 'Y' AND a.emplid='" + emplid + "'";
rs = cmd.ExecuteReader();
if (rs.Read())
{
item.姓名 = (string)rs["descr"];
item.毕业时间 = Convert.ToDateTime(rs["effdt"]);
item.毕业学校 = (string)rs["school_descr"];
item.学习专业 = (string)rs["major_descr"];
item.学历 = (string)rs["descrshort"];
item.届别 = item.毕业时间.Year.ToString();
}
rs.Close();
}
//获取入职时间
using (OleDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select hire_dt from ps_job where emplid='" + emplid + "' and rownum=1 order by last_hire_dt desc";
rs = cmd.ExecuteReader();
if (rs.Read())
{
item.入职时间 = Convert.ToDateTime(rs["hire_dt"]);
}
rs.Close();
}
}
return(item);
}
finally
{
if (rs != null)
{
rs.Close();
}
conn.Close();
}
}
}
