//Add Employee Clocking Information
public void AddEmpClockingInfo(int empTrackerId, EmployeeClockingDTO employeeClockingDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[EmployeeClocking]([EmployeeTrackerId],[ClockFunctionTypeId],[ClockingTime],[MinutesWorked],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getInteger(empTrackerId) + "," + SQLUtility.getInteger(employeeClockingDTO.ClockFunctionTypeId) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(employeeClockingDTO.ClockingTime) + "', "
+ " " + SQLUtility.getInteger(employeeClockingDTO.MinutesWorked) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "' )";
db.ExecuteUpdate(query);
}
// Employee Clocking Information of each business day for every night
public ArrayList GetEmployeClocking(int empId, DateTime businessDate, string connetionString)
{
ArrayList list = new ArrayList();
StoreRemoteDataBaseUitility remoteDB = new StoreRemoteDataBaseUitility(connetionString);
SqlConnection con = null;
try
{
string query = "select EmployeeId,ClockFunctionTypeId,ClockingTime,MinutesWorked from dbo.UserClocking where ClockingTime "
+ "between '" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' and '" + SQLUtility.FormateDateYYYYMMDD(businessDate.AddDays(1)) + "' and EmployeeId = " + SQLUtility.getInteger(empId) + " ";
con = remoteDB.OpenConnection();
SqlCommand comm = remoteDB.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
EmployeeClockingDTO employeeClockingDTO = null;
while (reader.Read())
{
employeeClockingDTO = new EmployeeClockingDTO();
employeeClockingDTO.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
employeeClockingDTO.ClockFunctionTypeId = ValidationUtility.ToInteger(reader[1].ToString());
employeeClockingDTO.ClockingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(2));
// employeeClockingDTO.ClockingTime = ValidationUtility.ToDate(reader[2].ToString());
employeeClockingDTO.MinutesWorked = ValidationUtility.ToInteger(reader[3].ToString());
employeeClockingDTO.BusinessDate = businessDate;
list.Add(employeeClockingDTO);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeClocking Method ", ex);
}
finally
{
remoteDB.CloseConnection(con);
}
return list;
}
//Get Employee Clocking Time
public ArrayList GetEmpClockingTime(EmployeeInfoDTO employeeInfoDTO, DateTime selectDate)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(employeeInfoDTO.ConnectionString);
DateTime weekStartDate = ValidationUtility.GetActualWeekStartDate(selectDate);
SqlConnection conn = null;
ArrayList list = new ArrayList();
try
{
conn = remoteDataBase.OpenConnection();
for (int i = 0; i < 7; i++)
{
string query = "select EmployeeId,ClockFunctionTypeId,ClockingTime,MinutesWorked from dbo.UserClocking where ClockingTime "
+ "between '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(i)) + "' and '" + SQLUtility.FormateDateYYYYMMDD(weekStartDate.AddDays(i + 1)) + "' and EmployeeId = " + SQLUtility.getInteger(employeeInfoDTO.EmpId) + " ";
SqlCommand comm = remoteDataBase.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
EmployeeClockingDTO employeeClockingDTO = null;
while (reader.Read())
{
employeeClockingDTO = new EmployeeClockingDTO();
employeeClockingDTO.EmployeeId = ValidationUtility.ToInteger(reader[0].ToString());
employeeClockingDTO.ClockFunctionTypeId = ValidationUtility.ToInteger(reader[1].ToString());
employeeClockingDTO.ClockingTime = ValidationUtility.ToDate(reader[2].ToString());
employeeClockingDTO.MinutesWorked = ValidationUtility.ToInteger(reader[3].ToString());
employeeClockingDTO.BusinessDate = weekStartDate.AddDays(i);
list.Add(employeeClockingDTO);
}
//if (EmployeeClockingDTO==null)
//{
// EmployeeClockingDTO = new EmployeeClockingDTO();
// EmployeeClockingDTO.BusinessDate = weekStartDate.AddDays(i);
// list.Add(EmployeeClockingDTO);
//}
reader.Close();
comm.Dispose();
}
}
catch (Exception ex)
{
log.Error("Exception in GetEmpClockingTime Method ", ex);
}
finally
{
remoteDataBase.CloseConnection(conn);
}
return list;
}
public List<EmployeeClockingDTO> GetClockingList(int trakId)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
List<EmployeeClockingDTO> empClokingList = new List<EmployeeClockingDTO>();
try
{
string query = "select id,ClockingTime,ClockFunctionTypeId,MinutesWorked from EmployeeClocking where EmployeeTrackerId=" + SQLUtility.getInteger(trakId) + " ";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
DateTime clokingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
// DateTime clokingTime = ValidationUtility.ToDate(reader["ClockingTime"].ToString());
int funtion = ValidationUtility.ToInteger(reader["ClockFunctionTypeId"].ToString());
int minutesWork = ValidationUtility.ToInteger(reader["MinutesWorked"].ToString());
EmployeeClockingDTO dto = new EmployeeClockingDTO { Id = id, ClockFunctionTypeId = funtion, ClockingTimeToString = clokingTime.ToString("hh:mm tt"), MinutesWorked = minutesWork };
empClokingList.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTrackingList Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return empClokingList;
}
public List<EmployeeClockingDTO> GetClockingListPerDay(int empInfoId,DateTime businessDate)
{
DataBaseUtility db = new DataBaseUtility();
SqlConnection conn = null;
List<EmployeeClockingDTO> empClokingList = new List<EmployeeClockingDTO>();
try
{
string query = " select ec.id,ec.ClockingTime,ec.ClockFunctionTypeId,ec.MinutesWorked from dbo.EmployeeClocking ec where "
+ " ec.EmployeeTrackerId = (select Id from dbo.EmployeeTracker et where et.BusinessDate='" + SQLUtility.FormateDateYYYYMMDD(businessDate) + "' and et.EmployeeInfoId = " + SQLUtility.getInteger(empInfoId) + ")";
conn = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
DateTime clokingTime = ValidationUtility.ToDateTime(reader.GetSqlDateTime(1));
// DateTime clokingTime = ValidationUtility.ToDate(reader["ClockingTime"].ToString());
int funtion = ValidationUtility.ToInteger(reader["ClockFunctionTypeId"].ToString());
int minutesWork = ValidationUtility.ToInteger(reader["MinutesWorked"].ToString());
EmployeeClockingDTO dto = new EmployeeClockingDTO { Id = id, ClockFunctionTypeId = funtion, ClockingTimeToString = clokingTime.ToString("hh:mm tt"), MinutesWorked = minutesWork };
empClokingList.Add(dto);
}
if (empClokingList.Count == 0 && businessDate.Date < DateTime.Now.Date)
{
EmployeeClockingDTO dto = new EmployeeClockingDTO { ClockingTimeToString ="N/A" };
empClokingList.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeTrackingList Method", ex);
}
finally
{
db.CloseConnection(conn);
}
return empClokingList;
}
