public HttpResponseMessage Put([FromBody] DeliveryOrderDetailBO schedulerData)
{
SchedulingDL dl = new SchedulingDL();
var orderdetailCollection = dl.UpdateScheduler(schedulerData);
return(Request.CreateResponse(HttpStatusCode.OK, orderdetailCollection, Configuration.Formatters.JsonFormatter));
}
public List <DeliveryOrderDetailBO> GetOrderstoDispatchDelivery()
{
try
{
string sql = "dbo.fn_get_orders_to_dispatch";
List <DeliveryOrderDetailBO> orderDetails = new List <DeliveryOrderDetailBO>();
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())
{
var orderDetail = new DeliveryOrderDetailBO();
orderDetail.OrderKey = Utils.CustomParse <Guid>(reader["orderkey"]);
orderDetail.DOHeader = new DeliveryOrderBO();
orderDetail.DOHeader.OrderDate = Utils.CustomParse <DateTime>(reader["orderdate"]);
orderDetail.DOHeader.BrokerRefNo = Utils.CustomParse <string>(reader["brokerrefno"]);
orderDetail.OrderDetailKey = Utils.CustomParse <Guid>(reader["orderdetailkey"]);
orderDetail.containerid = Utils.CustomParse <string>(reader["containerid"]);
orderDetail.ContainerNo = Utils.CustomParse <string>(reader["containerno"]);
orderDetail.ContainerSize = Utils.CustomParse <short>(reader["containersize"]);
orderDetail.ContainerSizeDesc = Utils.CustomParse <string>(reader["containersizeDesc"]);
orderDetail.Chassis = Utils.CustomParse <string>(reader["chassis"]);
orderDetail.SealNo = Utils.CustomParse <string>(reader["sealno"]);
orderDetail.Weight = Utils.CustomParse <string>(reader["weight"]);
orderDetail.AppDateFrom = Utils.CustomParse <DateTime>(reader["apptdatefrom"]);
orderDetail.AppDateTo = Utils.CustomParse <DateTime>(reader["apptdateto"]);
orderDetail.PickupDateTime = Utils.CustomParse <DateTime>(reader["scheduledarrival"]);
orderDetail.DropOffDateTime = Utils.CustomParse <DateTime>(reader["scheduleddeparture"]);
orderDetail.LastFreeDay = Utils.CustomParse <DateTime>(reader["LastFreeDay"]);
orderDetail.SchedulerNotes = Utils.CustomParse <string>(reader["schedulernotes"]);
orderDetails.Add(orderDetail);
}
}while (reader.NextResult());
reader.Close();
}
return(orderDetails);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public List <DeliveryOrderDetailBO> GetScheduledContainers()
{
try
{
var orderDetails = new List <DeliveryOrderDetailBO>();
string sql = "dbo.fn_get_scheduledlist";
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())
{
var orderDetail = new DeliveryOrderDetailBO();
orderDetail.OrderKey = Utils.CustomParse <Guid>(reader["orderkey"]);
orderDetail.OrderDetailKey = Utils.CustomParse <Guid>(reader["orderdetailkey"]);
orderDetail.containerid = Utils.CustomParse <string>(reader["containerid"]);
orderDetail.ContainerNo = Utils.CustomParse <string>(reader["containerno"]);
orderDetail.ContainerSize = Utils.CustomParse <short>(reader["containersize"]);
orderDetail.ContainerSizeDesc = Utils.CustomParse <string>(reader["containerDesc"]);
orderDetail.Comments = Utils.CustomParse <string>(reader["comment_notes"]);
orderDetail.SchedulerNotes = Utils.CustomParse <string>(reader["schedulernotes"]);
// orderDetail = Utils.CustomParse<string>(reader["routekey"]);
orderDetail.LastFreeDay = Utils.CustomParse <DateTime>(reader["lastfreeday"]);
orderDetail.PickupDateTime = Utils.CustomParse <DateTime>(reader["scheduledarrival"]);
orderDetail.DropOffDateTime = Utils.CustomParse <DateTime>(reader["scheduleddeparture"]);
orderDetail.StatusDesc = Utils.CustomParse <string>(reader["status"]);
orderDetail.nextaction = Utils.CustomParse <string>(reader["nextaction"]);
// orderDetail.dr = Utils.CustomParse<string>(reader["drivernotes"]);
orderDetails.Add(orderDetail);
}
} while (reader.NextResult());
reader.Close();
}
return(orderDetails);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public DeliveryOrderDetailBO GetOrderDetailsbykey(string orderdetailkey)
{
try
{
string sql = "dbo.fn_get_orderdetailbykey";
var orderDetail = new DeliveryOrderDetailBO();
conn = new NpgsqlConnection(connString);
conn.Open();
cmd = new NpgsqlCommand(sql, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, Guid.Parse(orderdetailkey));
var reader = cmd.ExecuteReader();
do
{
while (reader.Read())
{
orderDetail.OrderKey = Utils.CustomParse <Guid>(reader["orderkey"]);
orderDetail.OrderDetailKey = Utils.CustomParse <Guid>(reader["orderdetailkey"]);
orderDetail.containerid = Utils.CustomParse <string>(reader["containerid"]);
orderDetail.ContainerNo = Utils.CustomParse <string>(reader["containerno"]);
orderDetail.ContainerSize = Utils.CustomParse <short>(reader["containersize"]);
orderDetail.ContainerSizeDesc = Utils.CustomParse <string>(reader["containersizeDesc"]);
orderDetail.Chassis = Utils.CustomParse <string>(reader["chassis"]);
orderDetail.SealNo = Utils.CustomParse <string>(reader["sealno"]);
orderDetail.Weight = Utils.CustomParse <string>(reader["weight"]);
}
}while (reader.NextResult());
reader.Close();
return(orderDetail);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
public HttpResponseMessage UpdateDOdetailStatus([FromBody] DeliveryOrderDetailBO objList)
{
var orderdetailCollection = doObj.UpdateDeliveryOrderDetailsStatus(objList);
return(Request.CreateResponse(HttpStatusCode.OK, orderdetailCollection, Configuration.Formatters.JsonFormatter));
}
public bool UpdateScheduler(DeliveryOrderDetailBO detail)
{
try
{
string sql = "dbo.fn_update_order_details";
conn = new NpgsqlConnection(connString);
conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();
cmd = new NpgsqlCommand(sql, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, detail.OrderKey);
cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, detail.OrderDetailKey);
if (detail.AppDateFrom == null)
{
cmd.Parameters.AddWithValue("_apptdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp, null);
}
else
{
cmd.Parameters.AddWithValue("_apptdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.AppDateFrom);// DateTime.Parse(detail.AppDateFrom, System.Globalization.CultureInfo.InvariantCulture));
//if (detail.AppDateFrom.ToString().Length == 24)
//{
// cmd.Parameters.AddWithValue("_apptdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.AppDateFrom);// DateTime.Parse(detail.AppDateFrom, System.Globalization.CultureInfo.InvariantCulture));
//}
//else
//{
// var AppDateFrom = DateTime.ParseExact(detail.AppDateFrom.ToString().Substring(0, 24), "ddd MMM dd yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
// cmd.Parameters.AddWithValue("_apptdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp, AppDateFrom);
//}
}
if (detail.AppDateTo == null)
{
cmd.Parameters.AddWithValue("_apptdateto", NpgsqlTypes.NpgsqlDbType.Timestamp, null);
}
else
{
cmd.Parameters.AddWithValue("_apptdateto", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.AppDateTo);
//if (detail.AppDateTo.ToString().Length == 24)
//{
// //cmd.Parameters.AddWithValue("_apptdateto", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(detail.AppDateTo, System.Globalization.CultureInfo.InvariantCulture));
// cmd.Parameters.AddWithValue("_apptdateto", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.AppDateTo);
//}
//else
//{
// var AppDateTo = DateTime.ParseExact(detail.AppDateTo.ToString().Substring(0, 24), "ddd MMM dd yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
// cmd.Parameters.AddWithValue("_apptdateto", NpgsqlTypes.NpgsqlDbType.Timestamp, AppDateTo);
//}
}
//cmd.Parameters.AddWithValue("_apptdatefrom", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(detail.AppDateFrom, System.Globalization.CultureInfo.InvariantCulture));
//cmd.Parameters.AddWithValue("_apptdateto", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(detail.AppDateTo, System.Globalization.CultureInfo.InvariantCulture));
//cmd.Parameters.AddWithValue("_status", NpgsqlTypes.NpgsqlDbType.Smallint, detail.Status);
//cmd.Parameters.AddWithValue("_statusdate", NpgsqlTypes.NpgsqlDbType.Date, DateTime.Now);
// cmd.Parameters.AddWithValue("_pickupdatetime", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.PickupDateTime);
//cmd.Parameters.AddWithValue("_dropoffdatetime", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.DropOffDateTime);
if (detail.SchedulerNotes == null || String.IsNullOrEmpty(detail.SchedulerNotes) || String.IsNullOrWhiteSpace(detail.SchedulerNotes))
{
cmd.Parameters.AddWithValue("_schedulernotes", NpgsqlTypes.NpgsqlDbType.Varchar, string.Empty);
}
else
{
cmd.Parameters.AddWithValue("_schedulernotes", NpgsqlTypes.NpgsqlDbType.Varchar, detail.SchedulerNotes);
}
if (detail.LastFreeDay == null)
{
cmd.Parameters.AddWithValue("_lastfreeday", NpgsqlTypes.NpgsqlDbType.Timestamp, null);
}
else
{
cmd.Parameters.AddWithValue("_lastfreeday", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.LastFreeDay);
//if (detail.LastFreeDay.ToString().Length == 24)
//{
// cmd.Parameters.AddWithValue("_lastfreeday", NpgsqlTypes.NpgsqlDbType.Timestamp, detail.LastFreeDay);// DateTime.Parse(detail.LastFreeDay, System.Globalization.CultureInfo.InvariantCulture));
//}
//else
//{
// var LastFreeDay = DateTime.ParseExact(detail.LastFreeDay.ToString().Substring(0, 24), "ddd MMM dd yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture);
// cmd.Parameters.AddWithValue("_lastfreeday", NpgsqlTypes.NpgsqlDbType.Timestamp, LastFreeDay);
// // cmd.Parameters.AddWithValue("_lastfreeday", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(detail.LastFreeDay, System.Globalization.CultureInfo.InvariantCulture));
//}
}
cmd.ExecuteNonQuery();
tran.Commit();
return(true);
}
catch (Exception msg)
{
throw msg;
}
finally
{
conn.Close();
}
}
