/// <summary> /// Retrieve a single office shift entry from list of office shift details /// </summary> /// <param name="id">Id of that particular entry</param> /// <param name="CompanyId">comapanyid of that particular entry</param> /// <returns>single entry of office shift details</returns> public static OfficeShift GetDetails(int id, int CompanyId) { DBManager db = new DBManager(); try { db.Open(); string query = @"select top 1 s.Office_Shift_Id,s.Shift_Name,s.Monday_In_Time,s.Monday_Out_Time, s.Tuesday_In_Time,s.Tuesday_Out_Time,s.Wednesday_In_Time, s.Wednesday_Out_Time,s.Thursday_In_Time,s.Thursday_Out_Time, s.Friday_In_Time,s.Friday_Out_Time,s.Saturday_In_Time, s.Saturday_Out_Time,s.Sunday_In_Time,s.Sunday_Out_time, l.Name[Company] from TBL_OFFICE_SHIFT_MST s left join TBL_COMPANY_MST l on l.Company_Id = s.Company_Id where l.Company_Id=@Company_Id and Office_Shift_Id=@id"; db.CreateParameters(2); db.AddParameters(0, "@Company_Id", CompanyId); db.AddParameters(1, "@id", id); DataTable dt = db.ExecuteQuery(CommandType.Text, query); if (dt != null) { OfficeShift off = new OfficeShift(); DataRow item = dt.Rows[0]; off.ID = item["Office_Shift_Id"] != DBNull.Value ? Convert.ToInt32(item["Office_Shift_Id"]) : 0; off.Name = Convert.ToString(item["Shift_Name"]).ToString(); off.MondayShift = item["Monday_In_Time"] != DBNull.Value ? Convert.ToDateTime(item["Monday_In_Time"]).ToString("HH:mm:ss") : string.Empty; off.MondayOutShift = item["Monday_Out_Time"] != DBNull.Value ? Convert.ToDateTime(item["Monday_Out_Time"]).ToString("HH:mm:ss") : string.Empty; off.TuesdayShift = item["Tuesday_In_Time"] != DBNull.Value ? Convert.ToDateTime(item["Tuesday_In_Time"]).ToString("HH:mm:ss") : string.Empty; off.TuesdayOutShift = item["Tuesday_Out_Time"] != DBNull.Value ? Convert.ToDateTime(item["Tuesday_Out_Time"]).ToString("HH:mm:ss") : string.Empty; off.WednesdayShift = item["Wednesday_In_Time"] != DBNull.Value ? Convert.ToDateTime(item["Wednesday_In_Time"]).ToString("HH:mm:ss") : string.Empty; off.WednesdayOutShift = item["Wednesday_Out_Time"] != DBNull.Value ? Convert.ToDateTime(item["Wednesday_Out_Time"]).ToString("HH:mm:ss") : string.Empty; off.ThursdayShift = item["Thursday_In_Time"] != DBNull.Value ? Convert.ToDateTime(item["Thursday_In_Time"]).ToString("HH:mm:ss") : string.Empty; off.ThursdayOutShift = item["Thursday_Out_Time"] != DBNull.Value ? Convert.ToDateTime(item["Thursday_Out_Time"]).ToString("HH:mm:ss") : string.Empty; off.FridayShift = item["Friday_In_Time"] != DBNull.Value ? Convert.ToDateTime(item["Friday_In_Time"]).ToString("HH:mm:ss") : string.Empty; off.FridayOutShift = item["Friday_Out_Time"] != DBNull.Value ? Convert.ToDateTime(item["Friday_Out_Time"]).ToString("HH:mm:ss") : string.Empty; off.SaturdayShift = item["Saturday_In_Time"] != DBNull.Value ? Convert.ToDateTime(item["Saturday_In_Time"]).ToString("HH:mm:ss") : string.Empty; off.SaturdayOutShift = item["Saturday_Out_Time"] != DBNull.Value ? Convert.ToDateTime(item["Saturday_Out_Time"]).ToString("HH:mm:ss") : string.Empty; off.SundayShift = item["Sunday_In_Time"] != DBNull.Value ? Convert.ToDateTime(item["Sunday_In_Time"]).ToString("HH:mm:ss") : string.Empty; off.SundayOutShift = item["Sunday_Out_time"] != DBNull.Value ? Convert.ToDateTime(item["Sunday_Out_time"]).ToString("HH:mm:ss") : string.Empty; return(off); } else { return(null); } } catch (Exception ex) { Application.Helper.LogException(ex, "OfficeShift | GetDetails(int id, int CompanyId)"); return(null); } finally { db.Close(); } }
/// <summary> /// Retrieve all officeshift details from payroll master /// </summary> /// <param name="CompanyId">company id of that list</param> /// <returns>list of office shift details</returns> public static List <OfficeShift> GetDetails(int CompanyId) { DBManager db = new DBManager(); try { db.Open(); string query = @"select s.Office_Shift_Id,s.Shift_Name,s.Monday_In_Time,s.Monday_Out_Time, s.Tuesday_In_Time,s.Tuesday_Out_Time,s.Wednesday_In_Time, s.Wednesday_Out_Time,s.Thursday_In_Time,s.Thursday_Out_Time, s.Friday_In_Time,s.Friday_Out_Time,s.Saturday_In_Time, s.Saturday_Out_Time,s.Sunday_In_Time,s.Sunday_Out_time, l.Name[Company] from TBL_OFFICE_SHIFT_MST s left join TBL_COMPANY_MST l on l.Company_Id = s.Company_Id where l.Company_Id=@Company_Id order by s.Created_Date desc"; db.CreateParameters(1); db.AddParameters(0, "@Company_Id", CompanyId); DataTable dt = db.ExecuteQuery(CommandType.Text, query); List <OfficeShift> result = new List <OfficeShift>(); if (dt != null) { foreach (DataRow item in dt.Rows) { OfficeShift off = new OfficeShift(); off.ID = item["Office_Shift_Id"] != DBNull.Value ? Convert.ToInt32(item["Office_Shift_Id"]) : 0; off.Name = Convert.ToString(item["Shift_Name"]); if (item["Monday_In_Time"] != DBNull.Value && item["Monday_Out_Time"] != DBNull.Value) { off.MondayShift = Convert.ToDateTime(item["Monday_In_Time"]).ToString("H:mm") + " - " + Convert.ToDateTime(item["Monday_Out_Time"]).ToString("H:mm"); } if (item["Tuesday_In_Time"] != DBNull.Value && item["Tuesday_Out_Time"] != DBNull.Value) { off.TuesdayShift = Convert.ToDateTime(item["Tuesday_In_Time"]).ToString("H:mm") + " - " + Convert.ToDateTime(item["Tuesday_Out_Time"]).ToString("H:mm"); } if (item["Wednesday_In_Time"] != DBNull.Value && item["Wednesday_Out_Time"] != DBNull.Value) { off.WednesdayShift = Convert.ToDateTime(item["Wednesday_In_Time"]).ToString("H:mm") + " - " + Convert.ToDateTime(item["Wednesday_Out_Time"]).ToString("H:mm"); } if (item["Thursday_In_Time"] != DBNull.Value && item["Thursday_Out_Time"] != DBNull.Value) { off.ThursdayShift = Convert.ToDateTime(item["Thursday_In_Time"]).ToString("H:mm") + " - " + Convert.ToDateTime(item["Thursday_Out_Time"]).ToString("H:mm"); } if (item["Friday_In_Time"] != DBNull.Value && item["Friday_Out_Time"] != DBNull.Value) { off.FridayShift = Convert.ToDateTime(item["Friday_In_Time"]).ToString("H:mm") + " - " + Convert.ToDateTime(item["Friday_Out_Time"]).ToString("H:mm"); } if (item["Saturday_In_Time"] != DBNull.Value && item["Saturday_Out_Time"] != DBNull.Value) { off.SaturdayShift = Convert.ToDateTime(item["Saturday_In_Time"]).ToString("H:mm") + " - " + Convert.ToDateTime(item["Saturday_Out_Time"]).ToString("H:mm"); } if (item["Sunday_In_Time"] != DBNull.Value && item["Sunday_Out_time"] != DBNull.Value) { off.SundayShift = Convert.ToDateTime(item["Sunday_In_Time"]).ToString("H:mm") + " - " + Convert.ToDateTime(item["Sunday_Out_time"]).ToString("H:mm"); } result.Add(off); } return(result); } else { return(null); } } catch (Exception ex) { Application.Helper.LogException(ex, "OfficeShift | GetDetails(int CompanyId)"); return(null); } finally { db.Close(); } }