public void AddEmpInfo(int storeId, EmployeeInfoDTO employeeInfoDTO)
{
DataBaseUtility db = new DataBaseUtility();
string query = "INSERT INTO [dbo].[EmployeeInfo] ([EmpId],[StoreId],[EmpFirstName],[EmpMiddleName],[EmpLastName],[CreatedDateTime],[LastUpdateDateTime]) "
+ " VALUES(" + SQLUtility.getInteger(employeeInfoDTO.EmpId) + "," + SQLUtility.getInteger(storeId) + "," + SQLUtility.getString(employeeInfoDTO.EmpFirstName) + "," + SQLUtility.getString(employeeInfoDTO.EmpMiddleName) + ", "
+ " " + SQLUtility.getString(employeeInfoDTO.EmpLastName) + ",'" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "','" + SQLUtility.FormateDateYYYYMMDDWtithTime(DateTime.Now) + "')";
db.ExecuteUpdate(query);
}
// This method is use for check employee is exsist in our local db or it new employee
public bool IsEmployeExsist(EmployeeInfoDTO employeeInfoDTO, int storeId)
{
bool isEmployeeExsist = false;
SqlConnection con = null;
DataBaseUtility db = new DataBaseUtility();
try
{
string query = "select * from dbo.EmployeeInfo where EmpId = " + SQLUtility.getInteger(employeeInfoDTO.EmpId) + " and storeId = " + SQLUtility.getInteger(storeId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
isEmployeeExsist = true;
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsEmployeExsist Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return isEmployeeExsist;
}
// This method is use for get employee list from local database
public ArrayList GetEmployeeList(int storeId)
{
ArrayList list = new ArrayList();
SqlConnection con = null;
DataBaseUtility db = new DataBaseUtility();
try
{
//string query = "select * from dbo.EmployeeInfo where storeId = " + SQLUtility.getInteger(storeId) + " ";
string query = "select ei.*,s.StoreNumber from dbo.EmployeeInfo ei,dbo.Store s where ei.StoreId=s.Id and ei.StoreId =" + SQLUtility.getInteger(storeId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
int id = ValidationUtility.ToInteger(reader["Id"].ToString());
int empId = ValidationUtility.ToInteger(reader["EmpId"].ToString());
string firstName = reader["EmpFirstName"].ToString();
int sId = ValidationUtility.ToInteger(reader["StoreId"].ToString());
int storeNumber = ValidationUtility.ToInteger(reader["StoreNumber"].ToString());
EmployeeInfoDTO dto = new EmployeeInfoDTO { Id = id, StoreId = sId, StoreNumber=storeNumber, EmpId = empId, EmpFirstName = firstName };
list.Add(dto);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsEmployeExsist Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return list;
}
// This method is use for get Imployee info from server
public ArrayList GetEmployeeInfo(string connectionString)
{
StoreRemoteDataBaseUitility remoteDataBase = new StoreRemoteDataBaseUitility(connectionString);
SqlConnection conn = null;
ArrayList list = new ArrayList();
try
{
//string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where ere.Employee_Id = e.EmployeeId and ere.EmployeeRole_Id in(5,6)";
//string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where ere.Employee_Id = e.EmployeeId and ere.EmployeeRole_Id in(1,2)";
string query = "select e.EmployeeId ,e.FirstName,e.MiddleName,e.LastName from dbo.Employees e, dbo.EmployeeRoleEmployees ere where ere.Employee_Id = e.EmployeeId and ere.EmployeeRole_Id in(select EmployeeRoleId from dbo.EmployeeRoles)";
conn = remoteDataBase.OpenConnection();
SqlCommand comm = remoteDataBase.getSQLCommand(query, conn);
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
EmployeeInfoDTO employeeInfoDTO = new EmployeeInfoDTO();
employeeInfoDTO.EmpId = ValidationUtility.ToInteger(reader[0].ToString());
employeeInfoDTO.EmpFirstName = reader[1].ToString();
employeeInfoDTO.EmpMiddleName = reader[2].ToString();
employeeInfoDTO.EmpLastName = reader[3].ToString();
list.Add(employeeInfoDTO);
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in GetEmployeeInfo Method ", ex);
}
finally
{
remoteDataBase.CloseConnection(conn);
}
return list;
}
// This method is use for check employee is exsist in our local db or it new employee
public EmployeeInfoDTO GetEmployeeInfo(int storeId, int empId)
{
EmployeeInfoDTO dto = null;
SqlConnection con = null;
DataBaseUtility db = new DataBaseUtility();
try
{
string query = "select * from dbo.EmployeeInfo where EmpId = " + SQLUtility.getInteger(empId) + " and storeId = " + SQLUtility.getInteger(storeId) + " ";
con = db.OpenConnection();
SqlCommand comm = db.getSQLCommand(query, con);
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
dto = new EmployeeInfoDTO();
dto.Id = ValidationUtility.ToInteger(reader["Id"].ToString());
}
reader.Close();
comm.Dispose();
}
catch (Exception ex)
{
log.Error("Exception in IsEmployeExsist Method ", ex);
}
finally
{
db.CloseConnection(con);
}
return dto;
}
//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;
}