public HttpResponseMessage Post([FromBody] RoutesBO objList)
{
DispatchAssignmentDL dl = new DispatchAssignmentDL();
var list = routes.AddRoutes(objList);
return(Request.CreateResponse(HttpStatusCode.OK, list, Configuration.Formatters.JsonFormatter));
}
public IList <Guid> InsertRouteData(RoutesBO obj)
{
try
{
//routekey, orderdetailkey, orderkey, legno, legtype, sourceaddrkey, destinationaddrkey,
//estimateddistanceinmiles, estimatedtraveltime, status, driverkey,
//scheduledarrival, scheduleddeparture, odometeratsource, actualarrival,
//actualdeparture, odometeratdestination
var RouteDetailCollection = new List <Guid>();
string sql = "dbo.fn_insert_routes_details";
conn = new NpgsqlConnection(connString);
conn.Open();
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_orderkey",
NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderKey);
cmd.Parameters.AddWithValue("_orderdetailkey",
NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderDetailKey);
cmd.Parameters.AddWithValue("_driverkey",
NpgsqlTypes.NpgsqlDbType.Uuid, obj.driverkey);
cmd.Parameters.AddWithValue("_drivernotes",
NpgsqlTypes.NpgsqlDbType.Varchar, obj.drivernotes);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
var RouteDetailID = Guid.Parse(reader[i].ToString());
RouteDetailCollection.Add(RouteDetailID);
}
}
reader.Close();
}
return(RouteDetailCollection);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public bool UpdateRouteDataforDispatchAssignment(RoutesBO obj)
{
try
{
string sql = "update dbo.tms_routes set drivernotes=@drivernotes, driverkey = @driverkey" +
" where orderkey = @orderkey and orderdetailkey = @orderdetailkey";
//SELECT routekey, orderdetailkey, orderkey, legno, legtype, sourceaddrkey, destinationaddrkey, estimateddistanceinmiles,
// estimatedtraveltime, status, driverkey, scheduledarrival, scheduleddeparture, odometeratsource, actualarrival,
// actualdeparture, odometeratdestination, drivernotes FROM dbo.tms_routes;
string query = "dbo.fn_update_route_for_dispatch_assignment";
conn = new NpgsqlConnection(connString);
conn.Open();
using (var cmd = new NpgsqlCommand(query, conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderKey);
cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderDetailKey);
cmd.Parameters.AddWithValue("_drivernotes", NpgsqlTypes.NpgsqlDbType.Varchar, obj.drivernotes);
cmd.Parameters.AddWithValue("_driverkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.driverkey);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var result = bool.Parse(reader[0].ToString());
return(result);
}
reader.Close();
}
return(false);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public RoutesBO getRoutesData(string orderDetailKey)
{
try
{
string sql = "dbo.fn_getRoutesData";
RoutesBO routeData = new RoutesBO();
conn = new NpgsqlConnection(connString);
conn.Open();
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
var reader = cmd.ExecuteReader();
do
{
while (reader.Read())
{
routeData.Routekey = Utils.CustomParse <Guid>(reader["routekey"]);
routeData.scheduledarrival = Utils.CustomParse <DateTime>(reader["scheduledarrival"]);
routeData.scheduledarrival = Utils.CustomParse <DateTime>(reader["scheduleddeparture"]);
routeData.legtype = Utils.CustomParse <int>(reader["legtype"]);
routeData.legno = Utils.CustomParse <int>(reader["legno"]);
routeData.drivernotes = Utils.CustomParse <string>(reader["drivernotes"]);
}
}while (reader.NextResult());
reader.Close();
}
return(routeData);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public bool UpdatePortwaitingtime(RoutesBO route)
{
try
{
string sql = "update dbo.tms_routes set drivernotes =" +
"@drivernotes where orderdetailkey = @orderdetailkey";
conn = new NpgsqlConnection(connString);
conn.Open();
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.AddWithValue("@orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, route.OrderDetailKey);
cmd.Parameters.AddWithValue("@drivernotes", NpgsqlTypes.NpgsqlDbType.Smallint, route.driverkey);
int returnvalue = cmd.ExecuteNonQuery();
if (returnvalue < 0)
{
return(false);
}
else
{
return(true);
}
}
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public HttpResponseMessage Post([FromBody] RoutesBO routesBO)
{
var routesdata = routes.UpdateRouteDataforDispatchAssignment(routesBO);
return(Request.CreateResponse(HttpStatusCode.OK, routesdata, Configuration.Formatters.JsonFormatter));
}
public IList <Guid> AddRoutes(RoutesBO obj)
{
try
{
//routekey, orderdetailkey, orderkey, legno, legtype, sourceaddrkey, destinationaddrkey,
//estimateddistanceinmiles, estimatedtraveltime, status, driverkey,
//scheduledarrival, scheduleddeparture, odometeratsource, actualarrival,
//actualdeparture, odometeratdestination
var RouteDetailCollection = new List <Guid>();
string sql = "dbo.fn_insert_tms_route_details";
conn = new NpgsqlConnection(connString);
conn.Open();
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderKey);
cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderDetailKey);
cmd.Parameters.AddWithValue("_legno", NpgsqlTypes.NpgsqlDbType.Smallint, obj.legno);
cmd.Parameters.AddWithValue("_legtype", NpgsqlTypes.NpgsqlDbType.Smallint, obj.legtype);
if (String.IsNullOrEmpty(obj.drivernotes) || String.IsNullOrWhiteSpace(obj.drivernotes))
{
cmd.Parameters.AddWithValue("_drivernotes", NpgsqlTypes.NpgsqlDbType.Varchar, string.Empty);
}
else
{
cmd.Parameters.AddWithValue("_drivernotes", NpgsqlTypes.NpgsqlDbType.Varchar, obj.drivernotes);
}
cmd.Parameters.AddWithValue("_scheduledarrival", NpgsqlTypes.NpgsqlDbType.Timestamp, obj.scheduledarrival);
cmd.Parameters.AddWithValue("_scheduleddeparture", NpgsqlTypes.NpgsqlDbType.Timestamp, obj.scheduleddeparture);
//cmd.Parameters.AddWithValue("_scheduledarrival", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(obj.scheduleddeparture, System.Globalization.CultureInfo.InvariantCulture));
//cmd.Parameters.AddWithValue("_scheduleddeparture", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(obj.scheduleddeparture, System.Globalization.CultureInfo.InvariantCulture));
var reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
var RouteDetailID = Guid.Parse(reader[i].ToString());
RouteDetailCollection.Add(RouteDetailID);
}
}
reader.Close();
}
return(RouteDetailCollection);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}