public HttpResponseMessage PostSchedule(ScheduleOrderBO orderBO)
{
SchedulingDL dl = new SchedulingDL();
var routeKey = dl.InsertSchedule(orderBO);
return(Request.CreateResponse(HttpStatusCode.OK, routeKey, Configuration.Formatters.JsonFormatter));
}
public List <ScheduleOrderBO> GetSchedulingDetails(Guid OrderKey)
{
try
{
List <ScheduleOrderBO> DOlist = new List <ScheduleOrderBO>();
List <string> list = new List <string>();
string sql = "dbo.fn_get_all_route_forDO";
conn = new NpgsqlConnection(connString);
conn.Open();
cmd = new NpgsqlCommand(sql, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, OrderKey);
var reader = cmd.ExecuteReader();
do
{
while (reader.Read())
{
var BO = new ScheduleOrderBO();
BO.OrderDetailKey = Utils.CustomParse <Guid>(reader["orderdetailkey"]);
BO.LegNo = Utils.CustomParse <short>(reader["legno"]);
BO.LegType = Utils.CustomParse <short>(reader["legtype"]);
BO.SourceAddressKey = Utils.CustomParse <Guid>(reader["sourceaddrkey"]);
BO.DestinationAddressKey = Utils.CustomParse <Guid>(reader["destinationaddrkey"]);
BO.DistanceInMiles = Utils.CustomParse <double>(reader["estimateddistanceinmiles"]);
BO.TravelTime = Utils.CustomParse <double>(reader["estimatedtraveltime"]);
BO.Status = Utils.CustomParse <short>(reader["status"]);
BO.DriverKey = Utils.CustomParse <Guid>(reader["driverkey"]);
BO.ScheduleArrival = TimeSpan.Parse(reader["scheduledarrival"].ToString());
BO.ScheduleDeparture = TimeSpan.Parse(reader["scheduleddeparture"].ToString());
BO.Odometer = Utils.CustomParse <short>(reader["odometeratsource"]);
BO.OdometerAtDestination = Utils.CustomParse <short>(reader["odometeratdestination"]);
BO.ActualArrival = TimeSpan.Parse(reader["actualarrival"].ToString());
BO.ActualDeparture = TimeSpan.Parse(reader["actualdeparture"].ToString());
DOlist.Add(BO);
}
}while (reader.NextResult());
reader.Close();
return(DOlist);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public Guid InsertSchedule(ScheduleOrderBO schedule)
{
try
{
string sql = "dbo.fn_insert_tms_route";
conn = new NpgsqlConnection(connString);
conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();
cmd = new NpgsqlCommand(sql, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, schedule.OrderDetailKey);
cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, schedule.OrderKey);
cmd.Parameters.AddWithValue("_legno", NpgsqlTypes.NpgsqlDbType.Smallint, schedule.LegNo);
cmd.Parameters.AddWithValue("_legtype", NpgsqlTypes.NpgsqlDbType.Smallint, schedule.LegType);
cmd.Parameters.AddWithValue("_sourceaddrkey", NpgsqlTypes.NpgsqlDbType.Uuid, schedule.SourceAddressKey);
cmd.Parameters.AddWithValue("_destinationaddrkey", NpgsqlTypes.NpgsqlDbType.Uuid, schedule.DestinationAddressKey);
cmd.Parameters.AddWithValue("_estimateddistanceinmiles", NpgsqlTypes.NpgsqlDbType.Double, schedule.DistanceInMiles);
cmd.Parameters.AddWithValue("_estimatedtraveltime", NpgsqlTypes.NpgsqlDbType.Double, schedule.TravelTime);
cmd.Parameters.AddWithValue("_status", NpgsqlTypes.NpgsqlDbType.Smallint, schedule.Status);
cmd.Parameters.AddWithValue("_driverkey", NpgsqlTypes.NpgsqlDbType.Smallint, schedule.DriverKey);
cmd.Parameters.AddWithValue("_scheduledarrival", NpgsqlTypes.NpgsqlDbType.Timestamp, schedule.ScheduleArrival);
cmd.Parameters.AddWithValue("_scheduleddeparture", NpgsqlTypes.NpgsqlDbType.Timestamp, schedule.ScheduleDeparture);
cmd.Parameters.AddWithValue("_odometeratsource", NpgsqlTypes.NpgsqlDbType.Smallint, schedule.Odometer);
cmd.Parameters.AddWithValue("_actualarrival", NpgsqlTypes.NpgsqlDbType.Timestamp, schedule.ActualArrival);
cmd.Parameters.AddWithValue("_actualdeparture", NpgsqlTypes.NpgsqlDbType.Timestamp, schedule.ActualDeparture);
cmd.Parameters.AddWithValue("_odometeratdestination", NpgsqlTypes.NpgsqlDbType.Smallint, schedule.OdometerAtDestination);
var RouteKey = cmd.ExecuteScalar();
tran.Commit();
return(Guid.Parse(RouteKey.ToString()));
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}