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); }