//string connectionString = ConfigurationManager.ConnectionStrings["ConnectionTA"].ToString();
#endregion
#region Charge
/// <summary>
///
/// </summary>
/// <param name="company"></param>
/// <param name="operation"></param>
/// <returns></returns>
public MessageDTO SaveCompany(CompanyDTO company, string operation)
{
DataTable dataTable = new DataTable();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
MessageDTO message = new MessageDTO();
try
{
connector.BeginTransaction();
//Todos los registros
object[] parameters = new object[] { company.CompanyId, company.User.UserId, company.CompanyName,
company.CompanyShortName, company.CompanyStatus, operation };
//connector.ExecuteNonQuery(CommandType.StoredProcedure, "SaveCompany", parameters);
dataTable = connector.ExecuteDataTable(CommandType.StoredProcedure, "SaveCompany", parameters);
foreach (DataRow row in dataTable.Rows)
{
message.ErrorCode = row["ERROR_NUMBER"].ToString();
message.ErrorMessage = row["ERROR_DESCRIPTION"].ToString();
}
if (message.ErrorCode == "0")
{
connector.Commit();
}
else
{
connector.RollBack();
}
}
catch (Exception exception)
{
message.ErrorCode = "-1";
message.ErrorMessage = exception.ToString();
connector.RollBack();
}
finally
{
connector.Dispose();
}
return(message);
}
//string connectionString = ConfigurationManager.ConnectionStrings["ConnectionTA"].ToString();
#endregion
#region Employee
/// <summary>
///
/// </summary>
/// <param name="employee"></param>
/// <param name="operation"></param>
public void SavePerson(PersonDTO person, string operation)
{
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
try
{
//Todos los registros
object[] parameters = new object[] { 1, person.PersonId, person.IdentificationNumber, person.PersonName,
person.PersonBirthDate, person.PersonWeight, person.PersonStatus,
operation };
connector.ExecuteNonQuery(CommandType.StoredProcedure, "SavePerson", parameters);
}
catch (Exception exception)
{
}
finally
{
connector.Dispose();
}
}
//string connectionString = ConfigurationManager.ConnectionStrings["ConnectionTA"].ToString();
#endregion
#region DropDownList
/// <summary>
///
/// </summary>
/// <param name="companyId"></param>
/// <param name="tableName"></param>
/// <param name="valueMember"></param>
/// <param name="displayMember"></param>
/// <returns></returns>
public List <DropDownDTO> GetDropDownList(int companyId, string tableName, string valueMember, string displayMember)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var dropDownList = new List <DropDownDTO>();
try
{
//Todos los registros
object[] parameters = new object[] { companyId, tableName, valueMember, displayMember };
connector.FillDataSet(dataSet, CommandType.StoredProcedure, "GetDropDownList", parameters);
if (dataSet.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataSet.Tables[0].Rows)
{
DropDownDTO dropDown = new DropDownDTO
{
DisplayMember = row["DISPLAY"].ToString(),
ValueMember = row["VALUE"].ToString()
};
dropDownList.Add(dropDown);
}
}
else
{
dropDownList = null;
}
}
catch (Exception exception)
{
dropDownList = null;
}
finally
{
connector.Dispose();
}
return(dropDownList);
}
/// <summary>
///
/// </summary>
/// <param name="whereClause"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public List <ScheduleDTO> GetSchedules(Dictionary <string, string> whereClause, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var schedules = new List <ScheduleDTO>();
int companyId = Convert.ToInt32(whereClause["companyId"]);
int pageSize = Convert.ToInt32(whereClause["pageSize"]);
int pageNumber = Convert.ToInt32(whereClause["pageNumber"]);
string sortColumn = whereClause["sortColumn"].ToString();
string sortOrder = whereClause["sortOrder"].ToString();
try
{
//Todos los registros
object[] parameters = new object[] { companyId, pageSize, pageNumber, sortColumn, sortOrder };
connector.FillDataSet(dataSet, CommandType.StoredProcedure, "GetSchedules", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
ScheduleDTO schedule = new ScheduleDTO
{
Company = new CompanyDTO {
CompanyId = Convert.ToInt32(row["COMPANY_ID"])
},
EndHour = row["END_HOUR"].ToString(),
LunchHour = row["LUNCH_HOUR"].ToString(),
ScheduleAccess = row["SCHEDULE_ACCESS"].ToString(),
ScheduleDescription = row["SCHEDULE_DESCRIPTION"].ToString(),
ScheduleEndHour = row["SCHEDULE_END_HOUR"].ToString(),
ScheduleEntryDelay = Convert.ToInt32(row["SCHEDULE_ENTRY_DELAY"]),
ScheduleId = Convert.ToInt32(row["SCHEDULE_ID"]),
ScheduleInnerZone = Convert.ToInt32(row["SCHEDULE_INNER_ZONE"]),
ScheduleIsNight = row["SCHEDULE_IS_NIGHT"].ToString(),
ScheduleLunchHour = row["SCHEDULE_LUNCH_HOUR"].ToString(),
ScheduleLunchTime = Convert.ToInt32(row["SCHEDULE_LUNCH_TIME"]),
ScheduleOuterZone = Convert.ToInt32(row["SCHEDULE_OUTER_ZONE"]),
ScheduleOutputDelay = Convert.ToInt32(row["SCHEDULE_OUTPUT_DELAY"]),
ScheduleShortName = row["SCHEDULE_SHORT_NAME"].ToString(),
ScheduleStartHour = row["SCHEDULE_START_HOUR"].ToString(),
ScheduleStatus = row["SCHEDULE_STATUS"].ToString(),
StartHour = row["START_HOUR"].ToString(),
Workday = new WorkdayDTO {
WorkdayId = Convert.ToInt32(row["WORKDAY_ID"])
}
};
schedules.Add(schedule);
}
}
else
{
schedules = null;
}
}
catch (Exception exception)
{
schedules = null;
}
finally
{
connector.Dispose();
}
return(schedules);
}
//string connectionString = ConfigurationManager.ConnectionStrings["ConnectionTA"].ToString();
#endregion
/// <summary>
/// Valida si el usuario que ingresa al sistema es válido
/// </summary>
/// <param name="userLogin"></param>
/// <param name="userPassword"></param>
/// <returns></returns>
public MessageDTO GetValidUser(string userLogin, string userPassword)
{
DataTable dataTable = new DataTable();
MessageDTO message = new MessageDTO();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
List <object> users = new List <object>();
try
{
//Todos los registros
object[] parameters = new object[] { userLogin, userPassword };
connector.FillDataTable(dataTable, CommandType.StoredProcedure, "GetValidUser", parameters);
if (dataTable.Rows.Count > 0)
{
foreach (DataRow row in dataTable.Rows)
{
UserDTO user = new UserDTO
{
Company = new CompanyDTO
{
CompanyId = Convert.ToInt32(row["COMPANY_ID"]),
CompanyName = row["COMPANY_NAME"].ToString(),
CompanyShortName = row["COMPANY_SHORT_NAME"].ToString()
},
UserId = Convert.ToInt32(row["USR_ID"]),
Employee = new EmployeeDTO
{
EmployeeId = Convert.ToInt32(row["EMPLOYEE_ID"]),
IdentificationNumber = row["EMPLOYEE_IDNUMBER"].ToString(),
EmployeeName = row["EMPLOYEE_NAME"].ToString()
},
Role = new RoleDTO {
RoleId = Convert.ToInt32(row["ROLE_ID"])
},
UserLogin = row["USR_LOGIN"].ToString(),
UserPassword = row["USR_PASSWORD"].ToString(),
UserStatus = row["USR_STATUS"].ToString(),
UserSupervisor = row["USR_SUPERVISOR"].ToString()
};
users.Add(user);
}
message.ErrorCode = "OK";
message.ErrorMessage = "";
message.ListObject = users;
}
else
{
message.ErrorCode = "-1";
message.ErrorMessage = "Usuario y/o contraseña incorrecta";
message.ListObject = null;
}
}
catch (Exception exception)
{
message.ErrorCode = "-1";
message.ErrorMessage = exception.Message.ToString();
users = null;
message.ListObject = null;
}
finally
{
connector.Dispose();
}
return(message);
}
/// <summary>
///
/// </summary>
/// <param name="whereClause"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public List <ScheduleEmployeeReaderDTO> GetEmployeeReaders(Dictionary <string, string> whereClause, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var employeeReaders = new List <ScheduleEmployeeReaderDTO>();
int companyId = Convert.ToInt32(whereClause["companyId"]);
int scheduleId = Convert.ToInt32(whereClause["scheduleId"]);
int employeeId = Convert.ToInt32(whereClause["employeeId"]);
int pageSize = Convert.ToInt32(whereClause["pageSize"]);
int pageNumber = Convert.ToInt32(whereClause["pageNumber"]);
string sortColumn = whereClause["sortColumn"].ToString();
string sortOrder = whereClause["sortOrder"].ToString();
try
{
//Todos los registros
object[] parameters = new object[] { companyId, scheduleId, employeeId, pageSize, pageNumber, sortColumn, sortOrder };
connector.FillDataSet(dataSet, CommandType.StoredProcedure, "GetEmployeeReaders", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
ScheduleEmployeeReaderDTO employeeReader = new ScheduleEmployeeReaderDTO
{
Company = new CompanyDTO {
CompanyId = Convert.ToInt32(row["COMPANY_ID"])
},
Employee = new EmployeeDTO {
EmployeeId = Convert.ToInt32(row["EMPLOYEE_ID"])
},
Reader = new ReaderDTO
{
ReaderId = Convert.ToInt32(row["READER_ID"]),
ReaderName = row["READER_NAME"].ToString(),
ReaderSerialNumber = row["READER_SERIAL_NUMBER"].ToString(),
ReaderType = row["READER_TYPE"].ToString()
},
Schedule = new ScheduleDTO {
ScheduleId = Convert.ToInt32(row["SCHEDULE_ID"])
},
ScheduleAccess = row["SCHEDULE_ACCESS"].ToString(),
ScheduleDataFrame = row["SCHEDULE_DATA_FRAME"].ToString(),
ScheduleEndTime = row["SCHEDULE_END_TIME"].ToString(),
ScheduleEndTimeIncome = row["SCHEDULE_END_TIME_INCOME"].ToString(),
ScheduleEndTimeLunch = row["SCHEDULE_END_TIME_LUNCH"].ToString(),
ScheduleEndTimeOutput = row["SCHEDULE_END_TIME_OUTPUT"].ToString(),
ScheduleLunchTime = row["SCHEDULE_LUNCH_TIME"].ToString(),
ScheduleStartTime = row["SCHEDULE_START_TIME"].ToString(),
ScheduleStartTimeIncome = row["SCHEDULE_START_TIME_INCOME"].ToString(),
ScheduleStartTimeLunch = row["SCHEDULE_START_TIME_LUNCH"].ToString(),
ScheduleStartTimeOutput = row["SCHEDULE_START_TIME_OUTPUT"].ToString(),
ZoneNumber = Convert.ToInt32(row["ZONE_NUMBER"])
};
employeeReaders.Add(employeeReader);
}
}
else
{
employeeReaders = null;
}
}
catch (Exception exception)
{
employeeReaders = null;
}
finally
{
connector.Dispose();
}
return(employeeReaders);
}
/// <summary>
///
/// </summary>
/// <param name="whereClause"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public List <EmployeeDTO> GetEmployees(Dictionary <string, string> whereClause, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var employees = new List <EmployeeDTO>();
int companyId = Convert.ToInt32(whereClause["companyId"]);
int pageSize = Convert.ToInt32(whereClause["pageSize"]);
int pageNumber = Convert.ToInt32(whereClause["pageNumber"]);
string sortColumn = whereClause["sortColumn"].ToString();
string sortOrder = whereClause["sortOrder"].ToString();
try
{
//Todos los registros
object[] parameters = new object[] { companyId, pageSize, pageNumber, sortColumn, sortOrder };
connector.FillDataSet(dataSet, CommandType.StoredProcedure, "GetEmployees", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
EmployeeDTO employee = new EmployeeDTO
{
Charge = new ChargeDTO {
ChargeId = Convert.ToInt32(row["CHARGE_ID"])
},
Company = new CompanyDTO {
CompanyId = Convert.ToInt32(row["COMPANY_ID"])
},
ContractType = new ContractTypeDTO {
ContractTypeId = Convert.ToInt32(row["CONTRACT_TYPE_ID"])
},
Department = new DepartmentDTO {
DepartmentId = Convert.ToInt32(row["DEPARTMENT_ID"]), DepartmentDescription = row["DEPARTMENT_DESCRIPTION"].ToString()
},
EmployeeBirthDate = Convert.ToDateTime(row["EMPLOYEE_BIRTH_DATE"]),
EmployeeCardNumber = row["EMPLOYEE_CARD_NUMBER"].ToString(),
EmployeeEntryDate = Convert.ToDateTime(row["EMPLOYEE_ENTRY_DATE"]),
EmployeeFingerPrint = row["EMPLOYEE_FINGERPRINT"].ToString(),
EmployeeFullName = row["EMPLOYEE_LAST_NAME"].ToString() + " " + row["EMPLOYEE_NAME"].ToString(),
EmployeeId = Convert.ToInt32(row["EMPLOYEE_ID"]),
EmployeeLastName = row["EMPLOYEE_LAST_NAME"].ToString(),
EmployeeModificationDate = Convert.ToDateTime(row["EMPLOYEE_MODIFICATION_DATE"]),
EmployeeName = row["EMPLOYEE_NAME"].ToString(),
EmployeeSalary = Convert.ToDecimal(row["EMPLOYEE_SALARY"].ToString()),
EmployeeStatus = row["EMPLOYEE_STATUS"].ToString(),
EmployeeStreetAddress = row["EMPLOYEE_STREET_ADDRESS"] == DBNull.Value ? "" : row["EMPLOYEE_STREET_ADDRESS"].ToString(),
EmployeeTruancy = row["EMPLOYEE_TRUANCY"].ToString(),
EmployeeUsedCard = row["EMPLOYEE_USED_CARD"].ToString(),
EmployeeValidityEndDate = Convert.ToDateTime(row["EMPLOYEE_VALIDITY_END_DATE"]),
EmployeeValidityStartDate = Convert.ToDateTime(row["EMPLOYEE_VALIDITY_START_DATE"]),
IdentificationNumber = row["EMPLOYEE_IDNUMBER"].ToString(),
IdentificationType = new IdentificationTypeDTO {
IdentificationTypeId = Convert.ToInt32(row["IDENTIFICATION_TYPE_ID"])
},
Schedule = new ScheduleDTO {
ScheduleId = Convert.ToInt32(row["SCHEDULE_ID"])
},
ScheduleType = row["SCHEDULE_TYPE"].ToString(),
SectorId = row["SECTOR_ID"] == DBNull.Value ? -1 : Convert.ToInt32(row["SECTOR_ID"]),
Sex = new SexDTO {
SexId = Convert.ToInt32(row["SEX_ID"])
}
};
employees.Add(employee);
}
}
else
{
employees = null;
}
}
catch (Exception exception)
{
employees = null;
}
finally
{
connector.Dispose();
}
return(employees);
}
