/// <summary>
///
/// </summary>
/// <param name="whereClause"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public List <DepartmentDTO> GetDepartments(Dictionary <string, string> whereClause, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var departments = new List <DepartmentDTO>();
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, "GetDepartments", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
DepartmentDTO department = new DepartmentDTO
{
Company = new CompanyDTO {
CompanyId = Convert.ToInt32(row["COMPANY_ID"])
},
DepartmentDescription = row["DEPARTMENT_DESCRIPTION"].ToString(),
DepartmentId = Convert.ToInt32(row["DEPARTMENT_ID"]),
DepartmentStatus = row["DEPARTMENT_STATUS"].ToString(),
SpendingCenter = new SpendingCenterDTO {
SpendingCenterId = Convert.ToInt32(row["SPENDING_CENTER_ID"]),
SpendingCenterDescription = row["SPENDING_CENTER_DESCRIPTION"].ToString()
}
};
departments.Add(department);
}
}
else
{
departments = null;
}
}
catch (Exception exception)
{
departments = null;
}
finally
{
connector.Dispose();
}
return(departments);
}
/// <summary>
///
/// </summary>
/// <param name="whereClause"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public List <ReaderDTO> GetReadersByEmployee(Dictionary <string, string> whereClause, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var readers = new List <ReaderDTO>();
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, employeeId, scheduleId, pageSize, pageNumber, sortColumn, sortOrder };
connector.FillDataSet(dataSet, CommandType.StoredProcedure, "GetReadersByEmployee", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
ReaderDTO reader = new ReaderDTO
{
Company = new CompanyDTO {
CompanyId = Convert.ToInt32(row["COMPANY_ID"])
},
ReaderId = Convert.ToInt32(row["READER_ID"]),
ReaderName = row["READER_NAME"].ToString(),
ReaderSerialNumber = row["READER_SERIAL_NUMBER"].ToString(),
ReaderStatus = row["READER_STATUS"].ToString(),
ReaderStatusComm = row["READER_STATUS_COMM"].ToString(),
ReaderType = row["READER_TYPE"].ToString()
};
readers.Add(reader);
}
}
else
{
readers = null;
}
}
catch (Exception exception)
{
readers = null;
}
finally
{
connector.Dispose();
}
return(readers);
}
/// <summary>
///
/// </summary>
/// <param name="whereClause"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public List <WorkdayDTO> GetWorkdays(Dictionary <string, string> whereClause, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var workdays = new List <WorkdayDTO>();
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, "GetWorkdays", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
WorkdayDTO workday = new WorkdayDTO
{
Company = new CompanyDTO {
CompanyId = Convert.ToInt32(row["COMPANY_ID"])
},
WorkdayDescription = row["WORKDAY_DESCRIPTION"].ToString(),
WorkdayId = Convert.ToInt32(row["WORKDAY_ID"]),
WorkdayShortName = row["WORKDAY_SHORT_NAME"].ToString(),
WorkdayStatus = row["WORKDAY_STATUS"].ToString()
};
workdays.Add(workday);
}
}
else
{
workdays = null;
}
}
catch (Exception exception)
{
workdays = null;
}
finally
{
connector.Dispose();
}
return(workdays);
}
/// <summary>
///
/// </summary>
/// <param name="whereClause"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public List <PermissionTypeDTO> GetPermissionTypes(Dictionary <string, string> whereClause, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var permissionTypes = new List <PermissionTypeDTO>();
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, "GetPermissionTypes", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
PermissionTypeDTO permissionType = new PermissionTypeDTO
{
Company = new CompanyDTO {
CompanyId = Convert.ToInt32(row["COMPANY_ID"])
},
PermissionTypeDescription = row["PERMISSION_TYPE_DESCRIPTION"].ToString(),
PermissionTypeId = Convert.ToInt32(row["PERMISSION_TYPE_ID"]),
PermissionTypeStatus = row["PERMISSION_TYPE_STATUS"].ToString(),
PermissionTypeType = row["PERMISSION_TYPE_TYPE"].ToString()
};
permissionTypes.Add(permissionType);
}
}
else
{
permissionTypes = null;
}
}
catch (Exception exception)
{
permissionTypes = null;
}
finally
{
connector.Dispose();
}
return(permissionTypes);
}
/// <summary>
///
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageNumber"></param>
/// <param name="sortColumn"></param>
/// <param name="sortOrder"></param>
/// <returns></returns>
public List <PersonDTO> GetPersons(int pageSize, int pageNumber, string sortColumn, string sortOrder, ref DataTable dataTable)
{
DataSet dataSet = new DataSet();
IUniversalConnector connector = new UniversalConnectorImpl(ServerEnumType.SqlServer, connectionString);
var persons = new List <PersonDTO>();
try
{
//Todos los registros
object[] parameters = new object[] { pageSize, pageNumber, sortColumn, sortOrder };
connector.FillDataSet(dataSet, CommandType.StoredProcedure, "GetPersons", parameters);
if (dataSet.Tables[1].Rows.Count > 0)
{
dataTable = dataSet.Tables[0];
foreach (DataRow row in dataSet.Tables[1].Rows)
{
PersonDTO person = new PersonDTO
{
PersonBirthDate = Convert.ToDateTime(row["PERSON_BIRTH_DATE"]),
PersonId = Convert.ToInt32(row["PERSON_ID"]),
PersonName = row["PERSON_NAME"].ToString(),
PersonStatus = row["PERSON_STATUS"].ToString(),
PersonWeight = Convert.ToDecimal(row["PERSON_WEIGHT"].ToString()),
IdentificationNumber = row["PERSON_INUMBER"].ToString()
};
persons.Add(person);
}
}
else
{
persons = null;
}
}
catch (Exception exception)
{
persons = null;
}
finally
{
connector.Dispose();
}
return(persons);
}
//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);
}
/// <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);
}