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