//// Get Store List public List<EmployeeTrackerDTO> GetTrackingList(int empInfoId, DateTime weekStartDate) { DataBaseUtility db = new DataBaseUtility(); SqlConnection conn = null; List<EmployeeTrackerDTO> empTrakList = new List<EmployeeTrackerDTO>(); try { string query = " select et.Id,et.ScheduleIn,et.ScheduleOut, et.BusinessDate,et.TotalTimeWorked from dbo.EmployeeTracker et where " + " et.BusinessDate between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(6)) + "' and et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + " "; conn = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, conn); SqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { int isSchedulDay = 0; int id = ValidationUtility.ToInteger(reader["Id"].ToString()); DateTime scheduleIn = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1)); DateTime scheduleOut = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2)); DateTime businessDate = ValidationUtility.ToDate(reader["BusinessDate"].ToString()); TimeSpan totalTime = reader.GetTimeSpan(4); // var v = (scheduleOut - scheduleOut).Subtract; TimeSpan sheduleTimeDifference = scheduleOut.Subtract(scheduleIn); TimeSpan difference = sheduleTimeDifference - totalTime; if (scheduleIn.Date.Equals(ValidationUtility.GetDefaultDate().Date)) { isSchedulDay = 1; } if (scheduleIn.Date.Equals(ValidationUtility.GetDefaultDate().Date) && businessDate.Date < DateTime.Now.Date) { isSchedulDay = 2; } EmployeeTrackerDTO trakerDTO = new EmployeeTrackerDTO { Id = id, ScheduleInToString = scheduleIn.ToString("hh:mm tt"), ScheduleOutToString = scheduleOut.ToString("hh:mm tt"), IsDayscheduled = isSchedulDay, BusinessDateToString = businessDate.ToString("MM/dd/yyyy"), TotalTimeWorked = totalTime.ToString(), WorkDifference = difference.ToString(), BusinessDate = businessDate, ScheduleTimeDifference = sheduleTimeDifference.ToString() }; empTrakList.Add(trakerDTO); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error("Exception in GetEmployeeTrackingList Method", ex); } finally { db.CloseConnection(conn); } return empTrakList; }
public void AddNewEmployeeInTracking(int empInfoId, DateTime date) { DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(date); DataBaseUtility db = new DataBaseUtility(); for (int i = 0; i < 7; i++) { DateTime businessDate = weekStartDate.AddDays(i); EmployeeTrackerDTO employeeTrackerDTO = new EmployeeTrackerDTO(); string query = "INSERT INTO [dbo].[EmployeeTracker]([BusinessDate],[EmployeeInfoId],[ScheduleIn],[ScheduleOut],[TotalTimeWorked],[CreatedDateTime],[LastUpdateDateTime]) " + " VALUES('" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "'," + SQLUtility.getInteger(empInfoId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeTrackerDTO.ScheduleIn) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeTrackerDTO.ScheduleOut) + "', " + " '00:00:00','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )"; db.ExecuteUpdate(query); } }
// This method is use for get employee id from EmployeeTracker table public EmployeeTrackerDTO GetEmployeeTracking(int empId, int storeId, DateTime businessDate) { EmployeeTrackerDTO dto = null; DataBaseUtility db = new DataBaseUtility(); SqlConnection con = null; try { string query = " select * from dbo.EmployeeTracker et where EmployeeInfoId = (select Id from dbo.EmployeeInfo where EmpId=" + SQLUtility.getInteger(empId) + " and StoreId=" + SQLUtility.getInteger(storeId) + " ) " + " and BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' "; con = db.OpenConnection(); SqlCommand comm = db.getSQLCommand(query, con); SqlDataReader reader = comm.ExecuteReader(); if (reader.Read()) { dto = new EmployeeTrackerDTO(); dto.Id = ValidationUtility.ToInteger(reader["Id"].ToString()); } reader.Close(); comm.Dispose(); } catch (Exception ex) { log.Error("Exception in GetEmployeeTracking Method ", ex); } finally { db.CloseConnection(con); } return dto; }
public void UpdateTotalTimeWorked(EmployeeTrackerDTO dto, ArrayList list) { int totalMinutesWorked = 0; if (!ValidationUtility.IsNull(list) && list.Count > 0) { foreach (EmployeeClockingDTO clockingDTO in list) { if (clockingDTO.ClockFunctionTypeId == 2) { totalMinutesWorked = clockingDTO.MinutesWorked + totalMinutesWorked; } } TimeSpan time = TimeSpan.FromMinutes(totalMinutesWorked); DataBaseUtility db = new DataBaseUtility(); string query = "update dbo.EmployeeTracker set TotalTimeWorked = '" + time + "', LastUpdateDateTime = '" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' where Id=" + SQLUtility.getInteger(dto.Id) + " "; db.ExecuteUpdate(query); } }