public StoreDeptModel GetStoreDept(string pDeptID)
{
StoreDeptModel data = new StoreDeptModel();
using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["EventReportConnectionString"].ToString()))
{
string Sql = @"SELECT * from StoreDept where 1 = 1 and DeptID = @DeptID";
SqlCommand cmd = new SqlCommand(Sql, conn);
cmd.Parameters.AddWithValue("@DeptID", pDeptID);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
List <StoreDeptModel> lst = DataTableAndListClass.DataTableToList <StoreDeptModel>(dt);
if (lst.Count == 0)
{
return(null);
}
else
{
foreach (var item in lst)
{
data = item;
}
}
}
catch (Exception ex)
{
string error = ex.Message;
return(null);
// ErrorClass.Write("sql=" + Sql + "-" + ex.Message.ToString());
}
finally
{
conn.Close();
cmd.Dispose();
da.Dispose();
}
}
return(data);
}
} //格式化後到職日
/// <summary>
/// 查員工資料
/// </summary>
/// <param name="eID">工號</param>
/// <param name="pPOSStore">店ID 0001</param>
/// <param name="pWorkStatusID">0在職 1離職 2留停</param>
/// <returns>集合</returns>
public List <EmployeeModel> GetEmpDataByStaffID(string eID, string pPOSStore, string pWorkStatusID)
{
List <EmployeeModel> data = new List <EmployeeModel>();
// using (SqlConnection conn = new SqlConnection(ConnClass.getEmployeesConn()))
using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString()))
{
string Sql = @"SELECT [eID],[eName],[DeptID],DeptName,ParentDeptName
,PosStore = s.StoreNo
,[Title],[TitleName]
,[EducationLevel],[Education],[EducationDepartment]
,[Experience1],[Experience2]
,[Experience3],[Experience4]
,[OnBoardDate],[OriginalOnBoardDate],[LeaveDate]
,[workstatusid]
,[EmpType]
,[Gender]
,[Birthday]
FROM
(SELECT [eID],[eName]
,emp.[DeptID],dept.DeptName
,StoreID =
CASE
WHEN Left(dept.DeptID, 1) < 4 THEN Left(dept.DeptID, 1)+'000'
ELSE dept.ParentDept
END
,ParentDeptName = Parentdept.DeptName
,emp.[Title],[TitleName]
,[EducationLevel],[Education],[EducationDepartment]
,[Experience1],[Experience2]
,[Experience3],[Experience4]
,[OnBoardDate],[OriginalOnBoardDate],[LeaveDate]
,[workstatusid]
,[EmpType]
,[Gender]
,[Birthday]
FROM [HumanWeb].[dbo].[Employee] emp
Left Join [HumanWeb].[dbo].[Title] t on emp.title = t.TitleID
Left Join [HumanWeb].[dbo].[vw_aDepartment] dept on emp.DeptID = dept.DeptId
Left Join [HumanWeb].[dbo].[vw_aDepartment] Parentdept on dept.ParentDept = Parentdept.DeptId
--Left Join [HumanWeb].[dbo].[StoreDisplay] s on dept.ParentDept = s.StoreID
) as Staff
Left Join [HumanWeb].[dbo].[StoreDisplay] s on Staff.StoreID = s.StoreID
where 1 = 1 " ;
if (!eID.Trim().Equals(""))
{
Sql += " and eID = @eID ";
}
if (!pPOSStore.Trim().Equals("0"))
{
Sql += " and s.StoreNo=@POSStore ";
}
if (!pWorkStatusID.Trim().Equals("0"))
{
Sql += " and WorkStatusID = @WorkStatusID ";
}
SqlCommand cmd = new SqlCommand(Sql, conn);
cmd.Parameters.AddWithValue("@eID", eID);
//cmd.Parameters.Add("@eID ", SqlDbType.VarChar).Value = "%" + eID + "%";
cmd.Parameters.AddWithValue("@POSStore", pPOSStore);
cmd.Parameters.AddWithValue("@WorkStatusID", pWorkStatusID);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
data = DataTableAndListClass.DataTableToList <EmployeeModel>(dt);
}
catch (Exception ex)
{
string error = ex.Message;
}
finally
{
conn.Close();
cmd.Dispose();
da.Dispose();
}
}
foreach (var item in data)
{
if (item.OriginalOnBoardDate != null)
{
item.OnBoard = item.OriginalOnBoardDate.ToString("yyyy-MM-dd");
}
if (item.Birthday != null)
{
item.OBirthday = item.Birthday.ToString("yyyy-MM-dd");
}
}
return(data);
}
