public static DataTable ReadFromWarehouse(TotMastrActions totMastrAction, string a1ShipRequestStatus)
{
using (DBConnection warehouseDBConnection = WarehouseDatabaseConnectionAdapter.GetDatabaseConnection())
return(warehouseDBConnection.ExecuteDataTableQuery(@"
select
*
from
A1ShipRequest
where
[action] = {0} and
[status] = '{1}' and
[threadName] = '{2}'
order by [createdDate] asc", (short)totMastrAction, a1ShipRequestStatus, Thread.CurrentThread.Name));
}
public static bool IsRecordInStatus(TotMastrActions totMastrAction, string a1ShipRequestStatus, string uniqueIdentifier)
{
using (DBConnection warehouseDBConnection = WarehouseDatabaseConnectionAdapter.GetDatabaseConnection())
{
int?recordCount = warehouseDBConnection.ExecuteScalar(@"
select
count(id)
from
A1ShipRequest
where
[action] = {0} and
[uniqueIdentifier] = '{1}' and
[status] = '{2}'" , (short)totMastrAction, uniqueIdentifier, a1ShipRequestStatus) as int?;
return(recordCount.HasValue && recordCount > 0);
}
}
public static DataTable InsertToWarehouse(TotMastrActions totMastrAction, string uniqueIdentifier, string status)
{
DataTable warehouseA1ShipRequestRecords = ReadSchemaFromWarehouse();
warehouseA1ShipRequestRecords.Rows.Add(warehouseA1ShipRequestRecords.NewRow());
warehouseA1ShipRequestRecords.Rows[0]["id"] = Guid.NewGuid();
warehouseA1ShipRequestRecords.Rows[0]["action"] = (short)totMastrAction;
warehouseA1ShipRequestRecords.Rows[0]["uniqueIdentifier"] = uniqueIdentifier;
warehouseA1ShipRequestRecords.Rows[0]["status"] = status;
warehouseA1ShipRequestRecords.Rows[0]["threadName"] = Thread.CurrentThread.Name;
warehouseA1ShipRequestRecords.Rows[0]["createdDate"] = DateTime.UtcNow;
lock (databaseSyncObject)
using (DBConnection warehouseDBConnection = WarehouseDatabaseConnectionAdapter.GetDatabaseConnection())
using (DBAdapter a1ShipRequestDBAdapter = new DBAdapter(A1ShipRequestTableSchemaQuery, warehouseDBConnection.SqlConnection))
a1ShipRequestDBAdapter.Update(warehouseA1ShipRequestRecords);
return(warehouseA1ShipRequestRecords);
}
public static IList <TOTMASTR> ReadFromWarehouse(string uniqueIdentifier, TotMastrActions totMastrAction, UniqueIdentifierType uniqueIdentifierType, bool isForWarehouseWriteBack)
{
IList <TOTMASTR> totMastrs = null;
string uniqueIdentifierFieldName = string.Empty;
WarehouseSyncClient warehouseSyncClient = WarehouseConnectionAdapter.GetNewWarehouseSyncClient();
try
{
switch (uniqueIdentifierType)
{
case UniqueIdentifierType.CartonASN:
uniqueIdentifierFieldName = "CARTON_ASN";
break;
case UniqueIdentifierType.OrderNumber:
uniqueIdentifierFieldName = "PACKSLIP";
break;
case UniqueIdentifierType.ShipmentNumber:
uniqueIdentifierFieldName = "SHP_CNSLDT";
break;
default:
throw new Exception(string.Format("No records could be loaded from TOTMASTR because the unique identifier type {0} is invalid!", uniqueIdentifierType.ToString("G")));
}
#region Handle Manual Shipment By Carton ASN
if (totMastrAction == TotMastrActions.ShipmentDataSync && !isForWarehouseWriteBack)
{
IList <TOTMASTR> tempTotMastrs = warehouseSyncClient.FindAllTOTMASTRPaged(TOTMASTRHydration.None,
string.Format("TOTMASTR.Where(item => item.{0} == \"{1}\" && item.ACTION == {2}).OrderBy(param => param.CARTON_ASN).Take(1)",
uniqueIdentifierFieldName, uniqueIdentifier, totMastrAction.ToString("D")));
if (tempTotMastrs == null || tempTotMastrs.Count == 0)
{
return(null);
}
uniqueIdentifierFieldName = "SHP_CNSLDT";
uniqueIdentifier = tempTotMastrs[0].SHP_CNSLDT;
}
#endregion
string queryString = null;
if (totMastrAction == TotMastrActions.ShipmentDataSync)
{
queryString = string.Format("TOTMASTR.Where(item => item.{0} == \"{1}\" && item.ACTION == {2}).OrderBy(param => param.CARTON_ASN).Take({3})",
uniqueIdentifierFieldName, uniqueIdentifier, totMastrAction.ToString("D"), WarehouseIntegrationConfig.Instance.PageSize);
}
else
{
queryString = string.Format("TOTMASTR.Where(item => item.{0} == \"{1}\" && item.ACTION == {2} && item.SHIP_SYS == \"A\").OrderBy(param => param.CARTON_ASN).Take({3})",
uniqueIdentifierFieldName, uniqueIdentifier, totMastrAction.ToString("D"), WarehouseIntegrationConfig.Instance.PageSize);
}
totMastrs = warehouseSyncClient.FindAllTOTMASTRPaged(TOTMASTRHydration.None, queryString);
int i = 0;
while (totMastrs != null && totMastrs.Count > 0)
{
i++;
IList <TOTMASTR> remainingTotMastrs = warehouseSyncClient.FindAllTOTMASTRPaged(TOTMASTRHydration.None,
string.Format("{0}.Skip({1})", queryString, (i * WarehouseIntegrationConfig.Instance.PageSize)));
if (remainingTotMastrs == null || remainingTotMastrs.Count == 0)
{
break;
}
foreach (TOTMASTR remainingTotMastr in remainingTotMastrs)
{
totMastrs.Add(remainingTotMastr);
}
}
#region Check All Picked
if (totMastrAction == TotMastrActions.ShipmentDataSync && !isForWarehouseWriteBack)
{
bool isAllPicked = false;
List <string> packslipNumbers = totMastrs.Select(t => t.PACKSLIP).ToList();
foreach (string packslipNumber in packslipNumbers)
{
IList <PickHead> tempPickHeads = warehouseSyncClient.FindAllPickHeadPaged(PickHeadHydration.None,
string.Format("PickHead.Where(item => item.PACKSLIP == \"{0}\" && item.ALL_PICKED == \"1\").Take(1)", packslipNumber));
isAllPicked = tempPickHeads != null && tempPickHeads.Count > 0;
if (!isAllPicked)
{
return(null);
}
}
}
#endregion
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (warehouseSyncClient != null)
{
warehouseSyncClient.Close();
warehouseSyncClient = null;
}
}
return(totMastrs);
}
public static TOTMASTR ConvertToWarehouseDTO(Container container, TOTMASTR totMastr, TotMastrActions newTotMastrAction)
{
#region Label
if (container.Labels != null && container.Labels.Count > 0)
{
IEnumerable <byte> labelImage = null;
foreach (Label label in container.Labels)
{
labelImage = labelImage == null?Convert.FromBase64String(label.LabelBuffer) : labelImage.Concat(Convert.FromBase64String(label.LabelBuffer));
}
totMastr.LABEL_IMAGE = ShipIntegrationConfig.Instance.UseBase64Labels ?
Convert.ToBase64String(labelImage.ToArray()) : Encoding.UTF8.GetString(labelImage.ToArray());
}
#endregion
totMastr.ACTION = (short)newTotMastrAction;
#region Shipment Costs
if (container.Shipment.ShipmentCostDetails != null && container.Shipment.ShipmentCostDetails.Count > 0)
{
ShipmentCostDetail overriddenFreightCost = container.Shipment.ShipmentCostDetails.FirstOrDefault(c => c.Type == (int)CostDetailType.OverriddenFreightCost);
ShipmentCostDetail listedFreightCost = container.Shipment.ShipmentCostDetails.FirstOrDefault(c => c.Type == (int)CostDetailType.ListedFreightCost);
ShipmentCostDetail freightCost = null;
if (overriddenFreightCost == null) // Standard logic
{
freightCost = container.Shipment.ShipmentCostDetails.FirstOrDefault(c => c.Type == (int)CostDetailType.TotalFreightCost);
if (freightCost != null && listedFreightCost != null)
{
totMastr.CHRG_SHIP = ShipIntegrationConfig.Instance.PassOnFreightDiscount ? freightCost.Value : listedFreightCost.Value;
}
}
else // Override logic
{
freightCost = overriddenFreightCost;
totMastr.CHRG_SHIP = overriddenFreightCost.Value;
}
if (freightCost != null)
{
totMastr.BAS_CHARGE = freightCost.Value;
totMastr.TOTAL_CHG = freightCost.Value;
totMastr.EST_CHARGE = freightCost.Value;
}
}
#endregion
#region Carrier/Service
if (container.Shipment.CarrierServiceCode.Contains(";"))
{
totMastr.CARRIER = container.Shipment.CarrierServiceCode.Substring(0, container.Shipment.CarrierServiceCode.IndexOf(";"));
totMastr.CARR_SRVC = StringUtils.Left(container.Shipment.CarrierServiceCode.Substring(container.Shipment.CarrierServiceCode.IndexOf(";") + 1), 25);
}
else
{
totMastr.CARRIER = container.Shipment.CarrierServiceCode;
totMastr.CARR_SRVC = string.Empty;
}
totMastr.CARRIER = StringUtils.Left(totMastr.CARRIER, 10);
#endregion
totMastr.DATE_SHIP = container.Shipment.ShipDate;
totMastr.ERR_CODE = string.Empty;
if (container.Shipment.ChildShipmentXRefChilds != null && container.Shipment.ChildShipmentXRefChilds.Count > 0)
{
totMastr.MASTER_BOL = StringUtils.Left(container.Shipment.ChildShipmentXRefChilds[0].Shipment.BOLNumber, 20);
}
totMastr.MAN_NUMBER = StringUtils.Left(container.Shipment.BOLNumber, 20);
totMastr.SHIPFIELD1 = StringUtils.Left(container.ModifiedBy, 30);
#region Tracking and Pro Number Handling
totMastr.SHIP_NUM = StringUtils.Left(string.IsNullOrWhiteSpace(container.Shipment.ProNumber) ? container.Shipment.ShipmentNumber : container.Shipment.ProNumber, 22);
if (newTotMastrAction == TotMastrActions.PreRateComplete)
{
totMastr.SHIP_NUM = string.Empty;
}
else if (newTotMastrAction == TotMastrActions.VoidComplete)
{
if (totMastr.SHIP_NUM == container.Shipment.ShipmentNumber)
{
totMastr.SHIP_NUM = string.Empty;
}
totMastr.SHIPMENT = string.Empty;
totMastr.TRACKTRACE = container.Shipment.VoidId;
}
else
{
totMastr.SHIPMENT = StringUtils.Left(container.Shipment.ShipmentNumber, 22);
if (string.IsNullOrWhiteSpace(container.TrackingNumber))
{
totMastr.TRACKTRACE = container.Shipment.ProNumber;
}
else if (ShipIntegrationConfig.Instance.UseLastMileTrackingNumber && !string.IsNullOrWhiteSpace(container.LastMileTrackingNumber))
{
totMastr.CAPTUREDBC = container.TrackingNumber;
totMastr.TRACKTRACE = container.LastMileTrackingNumber;
}
else
{
totMastr.CAPTUREDBC = string.IsNullOrWhiteSpace(container.ReturnTrackingNumber) ? container.LastMileTrackingNumber : container.ReturnTrackingNumber;
totMastr.TRACKTRACE = container.TrackingNumber;
}
}
totMastr.CAPTUREDBC = StringUtils.Left(totMastr.CAPTUREDBC, 30);
totMastr.TRACKTRACE = StringUtils.Left(totMastr.TRACKTRACE, 34);
#endregion
if (newTotMastrAction != TotMastrActions.PrePrintComplete)
{
if (WarehouseIntegrationConfig.Instance.UseContainerWeights)
{
totMastr.WEIGHT_PC = "1";
totMastr.WEIGHT_SHP = container.Weight;
}
else
{
totMastr.WEIGHT_SHP = container.Shipment.Containers.Sum(c => c.Weight ?? 0m);
}
}
return(totMastr);
}
public static TOTMASTR ConvertToWarehouseDTO(Container container, TOTMASTR totMastr, TotMastrActions currentTotMastrAction, TotMastrActions newTotMastrAction)
{
if (totMastr.ACTION != (short)currentTotMastrAction)
{
return(totMastr);
}
return(ConvertToWarehouseDTO(container, totMastr, newTotMastrAction));
}
public static void UpdateWarehouse(string setA1ShipRequestStatus, string currentA1ShipRequestStatus, TotMastrActions totMastrAction)
{
lock (databaseSyncObject)
using (DBConnection warehouseDBConnection = WarehouseDatabaseConnectionAdapter.GetDatabaseConnection())
warehouseDBConnection.ExecuteNonQuery(@"
update
A1ShipRequest
set
[status] = '{0}',
[threadName] = '{1}'
where
[status] = '{2}' and
[action] = {3} and
([threadName] = '{1}' or [threadName] = '' or [threadName] is null)" , setA1ShipRequestStatus, Thread.CurrentThread.Name, currentA1ShipRequestStatus, (short)totMastrAction);
}
