public IEnumerable<StopVO> GetStopsByCustomerRequest(CustomerRequestVO customerRequest)
{
Database.CreateQuery("SELECT * FROM REQ_ETY_OGN WHERE ETY_NM = 'CUS_REQ' AND ETY_KEY_I = @CUS_REQ_I"");
NewQuery.AddParameter(customerRequest, "@CUS_REQ_I");
dataTable = Database.RunSelect(NewQuery);
return StopHydrater.Hydrate(dataTable);
}
public IEnumerable<AppointmentVO> GetAppointmentsByCustomerRequest(CustomerRequestVO CustomerRequest)
{
Database.CreateQuery("SELECT * FROM REQ_ETY_SCH WHERE ETY_NM = 'CUS_REQ' AND ETY_KEY_I = @CUS_REQ_I");
NewQuery.AddParameter(CustomerRequest, "@CUS_REQ_I");
dataTable = Database.RunSelect(NewQuery);
return AppointmentHydrater.Hydrate(dataTable);
}
public IEnumerable<ReferenceNumberVO> GetReferenceNumbersByCustomerRequest(CustomerRequestVO customerRequest)
{
Database.CreateQuery("SELECT * FROM REQ_ETY_REF_NBR WHERE ETY_NM = 'CUS_REQ' AND ETY_KEY_I = @CUS_REQ_I");
NewQuery.AddParameter(customerRequest, "@CUS_REQ_I");
dataTable = Database.RunSelect(NewQuery);
return ReferenceNumberHydrater.Hydrate(dataTable);
}
private List<StopVO> GetStopsByCustomerRequest(CustomerRequestVO customerRequest)
{
const string selectQueryStatement = "SELECT * FROM REQ_ETY_OGN WHERE ETY_NM = 'CUS_REQ' AND ETY_KEY_I = @CUS_REQ_I";
using (SqlConnection defaultSqlConnection = new SqlConnection(DatabaseConnectionString))
{
defaultSqlConnection.Open();
DataTable queryResult = new DataTable();
using (SqlCommand queryCommand = new SqlCommand(selectQueryStatement, defaultSqlConnection))
{
queryCommand.Parameters.AddWithValue("@CUS_REQ_I", customerRequest);
var sqlReader = queryCommand.ExecuteReader();
queryResult.Load(sqlReader);
}
var StopsByCustomerRequest = BuildStops(queryResult);
return StopsByCustomerRequest;
}
}
private List<CustomerRequestVO> BuildCustomerRequests(DataTable dataTable)
{
var customerRequests = new List<CustomerRequestVO>();
foreach (DataRow currentRow in dataTable.Rows)
{
var customerRequest = new CustomerRequestVO
{
Identity = (int)currentRow["CUS_REQ_I"],
Status = currentRow["PRS_STT"].ToString(),
BusinessEntityKey = currentRow["BUS_UNT_ETY_NM"].ToString(),
TypeCode = currentRow["REQ_TYP_C"].ToString(),
ConsumerClassificationType = currentRow["CNSM_CLS"].ToString(),
CreatedDate = (DateTime?)currentRow["CRT_S"],
CreatedUserId = currentRow["CRT_UID"].ToString(),
CreatedProgramCode = currentRow["CRT_PGM_C"].ToString(),
LastUpdatedDate = (DateTime?)currentRow["LST_UPD_S"],
LastUpdatedUserId = currentRow["LST_UPD_UID"].ToString(),
LastUpdatedProgramCode = currentRow["LST_UPD_PGM_C"].ToString()
};
customerRequest.Appointments = GetAppointmentsByCustomerRequest(customerRequest);
customerRequest.Comments = GetCommentsByCustomerRequest(customerRequest);
customerRequest.ReferenceNumbers = GetReferenceNumbersByCustomerRequest(customerRequest);
customerRequest.Stops = GetStopsByCustomerRequest(customerRequest);
customerRequests.Add(customerRequest);
}
return customerRequests;
}