/// <summary>
/// 根据ID获取到超级管理员信息
/// remark: johnsing ho 2018-08-01 现在人员来源复杂了，使用GetTableWithIDL代替
/// </summary>
/// <param name="intAdminID"></param>
/// <returns></returns>
public DataTable GetTable(string seCardID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
//string strSQL = @"select ProcessTab.IDCardNumber,ProcessTab.EmployeeID
// ,ProcessTab.Phone,ProcessTab.SegmentName,IDCardTab.Sex,IDCardTab.ChineseName
// from [dbo].[Process] as ProcessTab
// left join
// [dbo].[IDCard] as IDCardTab
// on ProcessTab.IDCardNumber = IDCardTab.IDCardNumber
// where ProcessTab.processstatus='6000' and ProcessTab.IDCardNumber='{0}'
// order by ProcessTab.ID desc";
//string strSQL = @"select EmployeeTab.IDCard as IDCardNumber,EmployeeTab.EmployeeID,EmployeeTab.Mobile as Phone,SegmentTab.Name as SegmentName,SegmentTab.ID as SegmentID
// ,EmployeeTab.Sex,EmployeeTab.ChineseName
// from [dbo].[Employee2] as EmployeeTab
// left join [dbo].[Segment] as SegmentTab
// on EmployeeTab.SegmentID = SegmentTab.ID where EmployeeTab.IDCard='{0}'
// order by EmployeeTab.ID desc";
string strSQL = @"select top 1 EmployeeTab.IDCardNumber ,EmployeeTab.EmployeeID,EmployeeTab.Phone,SegmentTab.SegmentName+' '+Building.BuildingName SegmentName,SegmentTab.ID as SegmentID
,A.Sex,A.ChineseName,B.EmployeeTypeName,EmployeeTab.DormAndOneCarAccessOperateDatetime Hire_Date
from Process as EmployeeTab
LEFT JOIN IDCard as A ON A.IDCardNumber=EmployeeTab.IDCardNumber
LEFT JOIN EmployeeType as B ON B.ID=EmployeeTab.EmployeeTypeID
left join [dbo].[Segment] as SegmentTab
on EmployeeTab.SegmentID = SegmentTab.ID
left join [dbo].[Building] as Building on Building.ID=EmployeeTab.BuildingID
where EmployeeTab.processstatus>=5000 and EmployeeTab.IDCardNumber='{0}' ORDER BY EmployeeTab.ID DESC ";
strSQL = string.Format(strSQL, seCardID);
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstanceStaffing();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
//strBuilder.AppendLine(" AND IDCardNumber = @ID");
//db.AddInParameter(dbCommandWrapper, "@ID", DbType.String, seCardID);
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
//1) 尝试从EHR取人员信息，但这些信息有可能是旧的，尤其是那些多次进宫的人
private DataTable GetEmpInfo_EHR(string sWorkdayNo, string seCardID)
{
DataTable dt = null;
DbCommand dbCommandWrapper = null;
try
{
string strSQL = @"
select top 1
EmployeeTab.IDCardNumber ,EmployeeTab.EmployeeID,EmployeeTab.Phone,SegmentTab.SegmentName+' '+Building.BuildingName SegmentName,SegmentTab.ID as SegmentID
,A.Sex,A.ChineseName,B.EmployeeTypeName,EmployeeTab.DormAndOneCarAccessOperateDatetime Hire_Date
from Process as EmployeeTab
LEFT JOIN IDCard as A ON A.IDCardNumber=EmployeeTab.IDCardNumber
LEFT JOIN EmployeeType as B ON B.ID=EmployeeTab.EmployeeTypeID
left join [dbo].[Segment] as SegmentTab
on EmployeeTab.SegmentID = SegmentTab.ID
left join [dbo].[Building] as Building on Building.ID=EmployeeTab.BuildingID
where EmployeeTab.processstatus>=5000
and (EmployeeTab.IDCardNumber='{0}' or EmployeeTab.EmployeeID='{1}')
ORDER BY EmployeeTab.ID DESC ";
strSQL = string.Format(strSQL, seCardID, sWorkdayNo);
StringBuilder strBuilder = new StringBuilder(strSQL);
Database db = DBO.GetInstanceStaffing();
dbCommandWrapper = db.DbProviderFactory.CreateCommand();
dbCommandWrapper.CommandType = CommandType.Text;
dbCommandWrapper.CommandText = strBuilder.ToString();
dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dbCommandWrapper != null)
{
dbCommandWrapper = null;
}
}
}
