public static IList <TOTMASTR> ReadUnusedFromWarehouse(string shpCnsldtNumber)
{
IList <TOTMASTR> totMastrs = null;
WarehouseSyncClient warehouseSyncClient = WarehouseConnectionAdapter.GetNewWarehouseSyncClient();
try
{
totMastrs = warehouseSyncClient.FindAllTOTMASTRPaged(TOTMASTRHydration.None,
string.Format("TOTMASTR.Where(item => item.SHP_CNSLDT == \"{0}\" && item.ACTION == {1} && (item.ACCUM_QTY == null || item.ACCUM_QTY == 0)).OrderBy(param => param.ROWID).Take({2})",
shpCnsldtNumber, TotMastrActions.ShipmentDataSync.ToString("D"), WarehouseIntegrationConfig.Instance.PageSize));
int i = 0;
while (totMastrs != null && totMastrs.Count > 0)
{
i++;
IList <TOTMASTR> remainingTotMastrs = warehouseSyncClient.FindAllTOTMASTRPaged(TOTMASTRHydration.None,
string.Format("TOTMASTR.Where(item => item.SHP_CNSLDT == \"{0}\" && item.ACTION == {1} && (item.ACCUM_QTY == null || item.ACCUM_QTY == 0)).OrderBy(param => param.ROWID).Take({2}).Skip({3})",
shpCnsldtNumber, TotMastrActions.ShipmentDataSync.ToString("D"), WarehouseIntegrationConfig.Instance.PageSize, (i * WarehouseIntegrationConfig.Instance.PageSize)));
if (remainingTotMastrs == null || remainingTotMastrs.Count == 0)
{
break;
}
foreach (TOTMASTR remainingTotMastr in remainingTotMastrs)
{
totMastrs.Add(remainingTotMastr);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (warehouseSyncClient != null)
{
warehouseSyncClient.Close();
warehouseSyncClient = null;
}
}
return(totMastrs);
}
public static Container ConvertToShipDTO(TOTMASTR totMastr)
{
if (!string.IsNullOrWhiteSpace(totMastr.PALLET_ASN)) // Ship only needs to know about the pallet records if the given carton is on a pallet
{
return(null);
}
if ((totMastr.ACCUM_QTY == null || totMastr.ACCUM_QTY == 0) &&
(totMastr.ACTION != (short)TotMastrActions.PrePrintCartonLabel && totMastr.ACTION != (short)TotMastrActions.PreRateOrder)) // Don't send Ship records with no quantity associated, unless it is a pre-print or pre-rate
{
return(null);
}
Container container = new Container();
string ltlPackageType = null;
WarehouseSyncClient warehouseSyncClient = WarehouseConnectionAdapter.GetNewWarehouseSyncClient();
try
{
#region Load External Data
IList <TOTEDETL> totDetls = null;
List <string> toteLabelsOnPallet = new List <string>()
{
totMastr.TOTLABEL
};
if (!string.IsNullOrWhiteSpace(totMastr.IS_PALLET) && totMastr.IS_PALLET == "1")
{
IList <TOTMASTR> totMastrsOnPallet = warehouseSyncClient.FindAllTOTMASTRPaged(TOTMASTRHydration.None,
string.Format("TOTMASTR.Where(item => item.PALLET_ASN == \"{0}\" && item.ACCUM_QTY > 0 && item.SHP_CNSLDT == \"{1}\")", totMastr.CARTON_ASN, totMastr.SHP_CNSLDT));
if (totMastrsOnPallet != null && totMastrsOnPallet.Count > 0)
{
toteLabelsOnPallet.AddRange(totMastrsOnPallet.Select(tmp => tmp.TOTLABEL).Distinct());
}
}
string totDetlQuery = "TOTEDETL.Where(item => ";
foreach (string toteLabelOnPallet in toteLabelsOnPallet)
{
totDetlQuery += string.Format("item.TOTLABEL == \"{0}\" || ", toteLabelOnPallet);
}
totDetlQuery = totDetlQuery.Substring(0, totDetlQuery.Length - 4) + ")";
totDetls = warehouseSyncClient.FindAllTOTEDETLPaged(TOTEDETLHydration.None,
string.Format("{0}.Take({1})", totDetlQuery, WarehouseIntegrationConfig.Instance.PageSize));
int i = 0;
while (totDetls != null && totDetls.Count > 0)
{
i++;
IList <TOTEDETL> remainingTotdetls = warehouseSyncClient.FindAllTOTEDETLPaged(TOTEDETLHydration.None,
string.Format("{0}.Take({1}).Skip({2})", totDetlQuery, WarehouseIntegrationConfig.Instance.PageSize, (i * WarehouseIntegrationConfig.Instance.PageSize)));
if (remainingTotdetls == null || remainingTotdetls.Count == 0)
{
break;
}
foreach (TOTEDETL remainingTotdetl in remainingTotdetls)
{
totDetls.Add(remainingTotdetl);
}
}
#endregion
#region ContainerOption
container.ContainerOption = new ContainerOption();
container.ContainerOption.AdditionalHandling = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("H")) ? true : false;
container.ContainerOption.AdultSignatureRequired = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("A")) ? true : false;
container.ContainerOption.AppointmentDelivery = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("D")) ? true : false;
container.ContainerOption.DoNotSafeDrop = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("1")) ? true : false;
container.ContainerOption.ProofRequireSignatureAdult19 = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("2")) ? true : false;
container.ContainerOption.LeaveAtDoor = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("3")) ? true : false;
if (!string.IsNullOrWhiteSpace(totMastr.COD) && totMastr.COD == "C")
{
container.CODContainer = true;
container.ContainerOption.CODAmount = totMastr.SHIP_COD;
if (!string.IsNullOrWhiteSpace(totMastr.COD_PAY))
{
container.ContainerOption.CODPaymentType = ShipXMLMapsAdapter.GetShipCODPaymentTypeCode(totMastr.COD_PAY);
}
}
container.ContainerOption.DeliveryConfirmation = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("C")) ? true : false;
container.ContainerOption.FedExDirectSignatureRequired = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("T")) ? true : false;
if (!string.IsNullOrWhiteSpace(totMastr.IS_PALLET) && totMastr.IS_PALLET == "1")
{
container.ContainerOption.InnerPackageCount = warehouseSyncClient.FindAllTOTMASTRCount(
string.Format("TOTMASTR.Where(item => item.PALLET_ASN == \"{0}\" && item.ACCUM_QTY > 0 && item.SHP_CNSLDT == \"{1}\")", totMastr.CARTON_ASN, totMastr.SHP_CNSLDT));
ltlPackageType = "Pallet";
}
else
{
container.ContainerOption.InnerPackageCount = 1;
ltlPackageType = "Carton";
}
if (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("I"))
{
container.ContainerOption.InsuranceType = 1;
container.TransmitDeclaredValue = true;
}
container.ContainerOption.SignatureRequired = (!string.IsNullOrWhiteSpace(totMastr.SHP_OPTION) && totMastr.SHP_OPTION.Contains("S")) ? true : false;
#endregion
#region ContainerReferenceFields
container.ContainerReferenceFields = new ContainerReferenceFields()
{
ReferenceField1 = StringUtils.Left(totMastr.MESSAGE1, 50),
ReferenceField2 = StringUtils.Left(totMastr.MESSAGE2, 50),
ReferenceField3 = StringUtils.Left(totMastr.MESSAGE3, 50),
ReferenceField4 = StringUtils.Left(totMastr.MESSAGE4, 50),
ReferenceField5 = StringUtils.Left(totMastr.MESSAGE5, 50)
};
#endregion
container.DeclaredValueAmount = totMastr.SHIP_DV;
container.DocumentNumber = StringUtils.Left(totMastr.PACKSLIP, 30);
container.Height = totMastr.SHIP_HEIGHT;
container.IdentityField = StringUtils.Left(totMastr.CARTON_ASN, 50);
container.Length = totMastr.SHIP_LENGTH;
#region PackageType
if (totMastr.SHP_OPTION.Contains("F"))
{
container.PackageType = "LETTER";
}
if (totMastr.SHP_OPTION.Contains("G"))
{
container.PackageType = "SBOX";
}
if (totMastr.SHP_OPTION.Contains("K"))
{
container.PackageType = "MBOX";
}
if (totMastr.SHP_OPTION.Contains("J"))
{
container.PackageType = "LBOX";
}
if (totMastr.SHP_OPTION.Contains("X"))
{
container.PackageType = "BOX";
}
if (totMastr.SHP_OPTION.Contains("N"))
{
container.PackageType = "USPSRRBA";
}
if (totMastr.SHP_OPTION.Contains("O"))
{
container.PackageType = "USPSRRBB";
}
if (totMastr.SHP_OPTION.Contains("P"))
{
container.PackageType = "USPSRRBC";
}
if (totMastr.SHP_OPTION.Contains("U"))
{
container.PackageType = "PAK";
}
if (totMastr.SHP_OPTION.Contains("V"))
{
container.PackageType = "MDEXBX";
}
if (totMastr.SHP_OPTION.Contains("Y"))
{
container.PackageType = "LGEXBX";
}
if (totMastr.SHP_OPTION.Contains("?"))
{
container.PackageType = "TUBE";
}
if (totMastr.SHP_OPTION.Contains("!"))
{
container.PackageType = "FRE";
}
if (totMastr.SHP_OPTION.Contains("Q"))
{
container.PackageType = "EXLGEXBX";
}
if (totMastr.SHP_OPTION.Contains("$"))
{
container.PackageType = "SMEXBX";
}
if (string.IsNullOrWhiteSpace(container.PackageType))
{
container.PackageType = "CUSTOM";
}
#endregion
container.PONumber = StringUtils.Left(totMastr.CUST_ORDER, 50);
container.Shippable = true; // Currently hard-coded since this is a "ship" integration as of today, not a "pack" integration
if (totMastr.ACTION == (short)TotMastrActions.RateManifestShipment || totMastr.ACTION == (short)TotMastrActions.DeleteCarton)
{
if (ShipIntegrationConfig.Instance.UseLastMileTrackingNumber)
{
container.TrackingNumber = string.IsNullOrWhiteSpace(totMastr.CAPTUREDBC) ? StringUtils.Left(totMastr.TRACKTRACE, 50) : StringUtils.Left(totMastr.CAPTUREDBC, 50);
}
else
{
container.TrackingNumber = StringUtils.Left(totMastr.TRACKTRACE, 50);
}
}
container.UOM = new UOM()
{
Name = StringUtils.Left(ShipIntegrationConfig.Instance.WeightUnitOfMeasure, 50)
};
container.Weight = totMastr.WEIGHT == null || totMastr.WEIGHT == 0 ? ShipIntegrationConfig.Instance.DefaultContainerWeight : totMastr.WEIGHT;
container.Width = totMastr.SHIP_WIDTH;
#region AmazonItems, ContainerCIs, and LTLData
if ((totDetls == null || totDetls.Count == 0) && totMastr.ACTION == (short)TotMastrActions.PreRateOrder)
{
LTLData ltlData = new LTLData()
{
CommodityClass = StringUtils.Left(ShipIntegrationConfig.Instance.ForcedPreRateCommodityClass, 50)
};
container.LTLDatas.Add(ltlData);
}
foreach (TOTEDETL totDetl in totDetls)
{
IList <Config> configs = null;
IList <ProdMstr> ciLTLProdMstrs = null;
IList <CUBISCAN> cubiscans = null;
ContainerCI containerCI = new ContainerCI();
LTLData ltlData = new LTLData();
#region Load External Data
configs = warehouseSyncClient.FindAllConfigPaged(ConfigHydration.None, "Config.Where(item => item.ConfigKey.StartsWith(\"PACKSIZE_\"))");
ciLTLProdMstrs = warehouseSyncClient.FindProdMstrsBySearchCriteriaPaged(new ProdMstrsBySearchCriteria()
{
Extended = totDetl.EXTENDED
},
ProdMstrHydration.None, 0, 1);
cubiscans = warehouseSyncClient.FindAllCUBISCANPaged(CUBISCANHydration.None,
string.Format("CUBISCAN.Where(item => item.PRODUCT == \"{0}\" && item.PACKSIZE == {1}).Take(1)", totDetl.PRODUCT, totDetl.PACKSIZE));
#endregion
#region AmazonItems
if (IsAmazonOrder(totMastr.SHP_OPTION))
{
AmazonItem amazonItem = new AmazonItem();
amazonItem.Container = container;
var pickDetls = warehouseSyncClient.FindAllPickDetlPaged(PickDetlHydration.None, string.Format("PickDet.Where(item => item.PackSlip == \"{0}\" && item.LineNumber == \"{1}\")",
totDetl.PACKSLIP, totDetl.ORDER_SEQ));
if (pickDetls == null || pickDetls.Count == 0)
{
throw new Exception("One or more items on the shipment did not provide an Amazon item identifier!");
}
if (ShipIntegrationConfig.Instance.AmazonAssumeBoxPerLine && pickDetls[0].IS_CMPN == "E") // This is a kit component, thus the kit parent needs looking up for the Amazon item ID
{
string[] splitKitLineNum = totDetl.ORDER_SEQ.Split('.'); // Kit component line numbers look like this: 1.001
string parentLineNum = splitKitLineNum[0]; // Putting this into a variable for code readability
var parentPickDetls = warehouseSyncClient.FindAllPickDetlPaged(PickDetlHydration.None,
string.Format("PickDet.Where(item => item.PackSlip == \"{0}\" && item.LineNumber == \"{1}\")", totDetl.PACKSLIP, parentLineNum));
if (parentPickDetls == null || parentPickDetls.Count == 0 || string.IsNullOrWhiteSpace(parentPickDetls[0].CustomerCrossReference))
{
throw new Exception("One or more items on the shipment did not provide an Amazon item identifier!");
}
amazonItem.ItemId = StringUtils.Left(parentPickDetls[0].CustomerCrossReference, 128);
amazonItem.Quantity = parentPickDetls[0].QuantityPicked;
}
else // This is a regular item, not a kit
{
if (string.IsNullOrWhiteSpace(pickDetls[0].CustomerCrossReference))
{
throw new Exception("One or more items on the shipment did not provide an Amazon item identifier!");
}
amazonItem.ItemId = StringUtils.Left(pickDetls[0].CustomerCrossReference, 128);
amazonItem.Quantity = totDetl.QTY;
}
container.AmazonItems.Add(amazonItem);
}
#endregion
#region ContainerCI
containerCI.Container = container;
containerCI.CountryOfManufacture = StringUtils.Left(totDetl.CTRY_ORIG, 2);
containerCI.HarmonizedTariffCode = StringUtils.Left(totDetl.TariffClass, 10);
containerCI.Quantity = totDetl.QTY;
containerCI.UnitValue = totDetl.SELL_PRICE;
if (!string.IsNullOrWhiteSpace(totMastr.SHIP_VIA) && !string.IsNullOrWhiteSpace(totMastr.SHIP_SERVC) &&
(totMastr.SHIP_VIA.StartsWith("DHLGM") || totMastr.SHIP_VIA.StartsWith("DHLAMP") || totMastr.SHIP_VIA.StartsWith("GLOBEG")))
{
containerCI.CIDescription = StringUtils.Left(string.Format("{0} - {1}", StringUtils.Left(totDetl.PRODUCT, 20), totDetl.DESCRIPT), 250);
}
else
{
containerCI.CIDescription = StringUtils.Left(totDetl.CommodityDescription, 250);
}
if (ciLTLProdMstrs != null && ciLTLProdMstrs.Count > 0)
{
containerCI.UnitWeight = ciLTLProdMstrs[0].Weight;
if (totDetl.PACKSIZE.HasValue && configs != null && configs.Count > 0)
{
if (ciLTLProdMstrs[0].Size1 == totDetl.PACKSIZE && configs.Any(item => item.ConfigKey == "PACKSIZE_1"))
{
containerCI.QuantityUOM = StringUtils.Left(configs.Where(item => item.ConfigKey == "PACKSIZE_1").First().CValue, 10);
}
else if (ciLTLProdMstrs[0].Size2 == totDetl.PACKSIZE && configs.Any(item => item.ConfigKey == "PACKSIZE_2"))
{
containerCI.QuantityUOM = StringUtils.Left(configs.Where(item => item.ConfigKey == "PACKSIZE_2").First().CValue, 10);
}
else if (ciLTLProdMstrs[0].Size3 == totDetl.PACKSIZE && configs.Any(item => item.ConfigKey == "PACKSIZE_3"))
{
containerCI.QuantityUOM = StringUtils.Left(configs.Where(item => item.ConfigKey == "PACKSIZE_3").First().CValue, 10);
}
else if (ciLTLProdMstrs[0].Size4 == totDetl.PACKSIZE && configs.Any(item => item.ConfigKey == "PACKSIZE_4"))
{
containerCI.QuantityUOM = StringUtils.Left(configs.Where(item => item.ConfigKey == "PACKSIZE_4").First().CValue, 10);
}
else if (ciLTLProdMstrs[0].Size5 == totDetl.PACKSIZE && configs.Any(item => item.ConfigKey == "PACKSIZE_5"))
{
containerCI.QuantityUOM = StringUtils.Left(configs.Where(item => item.ConfigKey == "PACKSIZE_5").First().CValue, 10);
}
}
}
if (cubiscans != null && cubiscans.Count > 0)
{
containerCI.UnitWeight = cubiscans[0].WEIGHT;
}
container.ContainerCIs.Add(containerCI);
#endregion
#region LTLDatas
ltlData.CommodityClassDescription = StringUtils.Left(totDetl.CommodityDescription, 100);
ltlData.Container = container;
ltlData.InnerPackageCount = 1;
ltlData.PackageType = ltlPackageType;
if (totMastr.ACTION == (short)TotMastrActions.PreRateOrder && !string.IsNullOrWhiteSpace(ShipIntegrationConfig.Instance.ForcedPreRateCommodityClass))
{
ltlData.CommodityClass = StringUtils.Left(ShipIntegrationConfig.Instance.ForcedPreRateCommodityClass, 50);
}
else
{
ltlData.CommodityClass = StringUtils.Left(!string.IsNullOrWhiteSpace(totDetl.FreightClass) ? totDetl.FreightClass : ShipIntegrationConfig.Instance.DefaultCommodityClass, 50);
}
if (!string.IsNullOrWhiteSpace(totDetl.NMFCNumber))
{
if (totDetl.NMFCNumber.Contains("-"))
{
string[] splitNMFC = totDetl.NMFCNumber.Split('-');
ltlData.NMFCCode = StringUtils.Left(splitNMFC[0], 50);
ltlData.NMFCItemSub = StringUtils.Left(splitNMFC[1], 10);
}
else
{
ltlData.NMFCCode = StringUtils.Left(totDetl.NMFCNumber, 50);
}
}
if (ciLTLProdMstrs != null && ciLTLProdMstrs.Count > 0)
{
//if (totDetls.Count > 1)
// ltlData.Weight = ciLTLProdMstrs[0].Weight;
//else
// ltlData.Weight = container.Weight;
if (ciLTLProdMstrs[0].Weight != null && totDetl.QTY != null)
{
ltlData.Weight = ciLTLProdMstrs[0].Weight * totDetl.QTY;
}
}
container.LTLDatas.Add(ltlData);
#endregion
}
#endregion
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (warehouseSyncClient != null)
{
warehouseSyncClient.Close();
warehouseSyncClient = null;
}
}
return(container);
}
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);
}
