public IEnumerable<AppointmentVO> GetAppointmentsByStop(StopVO Stop)
{
Database.CreateQuery("SELECT * FROM REQ_ETY_SCH WHERE ETY_NM = 'REQ_ETY_OGN' AND ETY_KEY_I = @REQ_ETY_OGN_I");
NewQuery.AddParameter(Stop, "@REQ_ETY_OGN_I");
dataTable = Database.RunSelect(NewQuery);
return AppointmentHydrater.Hydrate(dataTable);
}
private List<CommentVO> GetCommentsByStop(StopVO stop)
{
const string selectQueryStatement = "SELECT * FROM REQ_ETY_CMM WHERE ETY_NM = 'REQ_ETY_OGN' AND ETY_KEY_I = @REQ_ETY_OGN_I";
using (SqlConnection defaultSqlConnection = new SqlConnection(DatabaseConnectionString))
{
defaultSqlConnection.Open();
DataTable queryResult = new DataTable();
using (SqlCommand queryCommand = new SqlCommand(selectQueryStatement, defaultSqlConnection))
{
queryCommand.Parameters.AddWithValue("@REQ_ETY_OGN_I", stop);
var sqlReader = queryCommand.ExecuteReader();
queryResult.Load(sqlReader);
}
var CommentsByStop = BuildComments(queryResult);
return CommentsByStop;
}
}
private List<StopVO> BuildStops(DataTable dataTable)
{
var Stops = new List<StopVO>();
foreach (DataRow currentRow in dataTable.Rows)
{
var Stop = new StopVO
{
Identity = (int)currentRow["REQ_ETY_OGN_I"],
EntityName = currentRow["ETY_NM"].ToString(),
EntityID = (int)currentRow["ETY_KEY_I"],
RoleType = currentRow["SLU_PTR_RL_TYP_C"].ToString(),
StopNumber = currentRow["STP_NBR"].ToString(),
CustomerAlias = currentRow["CUS_SIT_ALS"].ToString(),
OrganizationName = currentRow["OGN_NM"].ToString(),
AddressLine1 = currentRow["ADR_LNE_1"].ToString(),
AddressLine2 = currentRow["ADR_LNE_2"].ToString(),
CityName = currentRow["ADR_CTY_NM"].ToString(),
StateCode = currentRow["ADR_ST_PROV_C"].ToString(),
CountryCode = currentRow["ADR_CRY_C"].ToString(),
PostalCode = currentRow["ADR_PST_C_SRG"].ToString(),
CreatedDate = (DateTime)currentRow["CRT_S"],
CreatedUID = currentRow["CRT_UID"].ToString(),
CreatedCode = currentRow["CRT_PGM_C"].ToString(),
LastUpdatedDate = (DateTime)currentRow["LST_UPD_S"],
LastUpdatedUID = currentRow["LST_UPD_UID"].ToString(),
LastUpdatedCode = currentRow["LST_UPD_PGM_C"].ToString()
};
Stop.Appointments = GetAppointmentsByStop(Stop);
Stop.Comments = GetCommentsByStop(Stop);
}
return Stops;
}