public int AddNewRoute(Route newRoute, out string errorMessage)
{
errorMessage = null;
using (var connection = new NpgsqlConnection(ConfigParser.ConnString))
{
try
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText =
"INSERT INTO route (name, start_datetime, end_datetime, geojson_doc_id, reason_id, priority_id, status_id, org_id, created_by_user_id, sent_to_user_id, approved_by_user_id, note, created, updated, trip_area, driver_name, phone_num, last_changed) VALUES (@name, @start_datetime, @end_datetime, @geojson_doc_id, @reason_id, @priority_id, @status_id, @org_id, @created_by_user_id, @sent_to_user_id, @approved_by_user_id, @note, @created, @updated, st_geomfromgeojson(@trip_area), @driver_name, @phone_num, @last_changed) RETURNING route_id";
newRouteCommandHelper(newRoute, command, true);
newRoute.RouteId = Convert.ToInt32(command.ExecuteScalar());
RouteHistoryModel routeHistoryModel = new RouteHistoryModel();
routeHistoryModel.AddNewRouteHistory(newRoute, out errorMessage);
return(newRoute.RouteId);
}
catch (Exception e)
{
Console.WriteLine(e);
errorMessage = e.ToString();
throw;
}
}
}
public int UpdateRouteId(Route route, out string errorMessage)
{
errorMessage = null;
if (route.RouteId == 0)
{
errorMessage = "Invalid route ID (0)";
return(-1);
}
using (var connection = new NpgsqlConnection(ConfigParser.ConnString))
{
try
{
connection.Open();
NpgsqlCommand command = new NpgsqlCommand();
command.Connection = connection;
command.CommandText =
"UPDATE route SET name = (@name), start_datetime = (@start_datetime), end_datetime = (@end_datetime), geojson_doc_id = (@geojson_doc_id), reason_id = (@reason_id), priority_id = (@priority_id), status_id = (@status_id), org_id = (@org_id), sent_to_user_id = (@sent_to_user_id), approved_by_user_id = (@approved_by_user_id), note = (@note), created = (@created), updated = (@updated), trip_area = st_geomfromgeojson(@trip_area), driver_name = (@driver_name), phone_num = (@phone_num), last_changed = (@last_changed) WHERE route_id = (@routeId) RETURNING route_id";
command.Parameters.AddWithValue("routeId", route.RouteId);
newRouteCommandHelper(route, command, false);
route.RouteId = Convert.ToInt32(command.ExecuteScalar());
RouteHistoryModel routeHistoryModel = new RouteHistoryModel();
routeHistoryModel.AddNewRouteHistory(route, out errorMessage);
return(route.RouteId);
}
catch (Exception e)
{
Console.WriteLine(e);
errorMessage = e.ToString();
throw;
}
}
}