///
/// Creates load based on Excel file
///
///
/// - Mod 018 Load creation import
///
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
class ANSARILoadCreationService
{
///
/// Creates load based on Excel file
///
/// Location of Excel file
/// Site to create load for
/// Location to create load for
/// Template for load
///
/// Jan 30, 2019 - BLue Horseshoe Solutions, Inc.
/// - Search for item based on ItemId or NameAlias
///
public void process(
str _fileUrl,
InventSiteId _siteId,
InventLocationId _locationId,
WHSLoadTemplateId _loadTemplateId,
ANSARIWHSASNLicensePlateId _licensePlateId) //03/14/2024 - - BUG 255602 - CR 303 - Add license plate field for manual load creation import
{
container con, errorList;
int i, curLine, purchLineNum;
boolean hasError = false;
Set purchRecIdSet = new Set(Types::AnyType);
PurchId purchId, nextPurchId;
ItemId itemId;
WHSLoadTable whsLoadTable;
WHSLoadLine whsLoadLine;
PurchLine purchLine, validatePurchLine;
PurchTable purchTable;
WHSShipmentTable whsShipmentTable;
WHSParameters whsParameters = WHSParameters::find();
WHSUOMStructure whsUOMStructure; //03/14/2024 - - BUG 255602 - CR 303 - Add license plate field for manual load creation import
ttsbegin;
// Build Header
whsLoadTable.LoadId = NumberSeq::newGetNum(NumberSeqReference::findReference(extendedTypeNum(WHSLoadId))).num();
whsLoadTable.InventSiteId = _siteId;
whsLoadTable.InventLocationId = _locationId;
whsLoadTable.initFromloadTemplateId(_loadTemplateId);
whsLoadTable.LoadDirection = WHSLoadDirection::Inbound;
whsLoadTable.LoadStatus = WHSLoadStatus::Open;
whsLoadTable.loadTemplateId = _loadTemplateId;
whsLoadTable.CarrierCode = whsParameters.ANSARILoadCreationShippingCarrier;
whsLoadTable.CarrierServiceCode = whsParameters.ANSARILoadCreationCarrierService;
whsLoadTable.ModeCode = whsParameters.ANSARILoadCreationMode;
whsLoadTable.ANSARIEDIASNLicensePlateId = _licensePlateId; //04/12/2024 - - BUG 255602 - CR 303 - Add license plate field for manual load creation import
whsLoadTable.insert();
// Read excel file
System.Byte[] byteArray;
System.IO.Stream stream;
stream = File::UseFileFromURL(_fileUrl);
con = this.readExcelData(stream);
curLine = 1;
purchLineNum = 1;
for (i=1; i <= conLen(con); i++)
{
purchId = conPeek(conPeek(con, curLine), 1);
nextPurchId = conPeek(conPeek(con, curLine - 1), 1);
itemId = conPeek(conPeek(con, curLine), 2);
purchTable = PurchTable::find(purchId);
if (PurchTable::exist(purchId))
{
if (purchId != nextPurchId)
{
// Create ShipmentId
whsShipmentTable.clear();
whsShipmentTable.ShipmentId = whsShipmentTable.getShipmentId();
whsShipmentTable.LoadId = whsLoadTable.LoadId;
whsShipmentTable.WorkTransType = WHSWorkTransType::Purch;
whsShipmentTable.OrderNum = purchTable.PurchId;
whsShipmentTable.AccountNum = purchTable.OrderAccount;
whsShipmentTable.DeliveryName = purchTable.DeliveryName;
whsShipmentTable.DeliveryPostalAddress = purchTable.DeliveryPostalAddress;
whsShipmentTable.CountryRegionISOCode = purchTable.CountyOrigDest;
whsShipmentTable.DlvTermId = purchTable.DlvTerm;
whsShipmentTable.InventSiteId = _siteId;
whsShipmentTable.InventLocationId = _locationId;
whsShipmentTable.CarrierCode = whsLoadTable.CarrierCode;
whsShipmentTable.CarrierServiceCode = whsLoadTable.CarrierServiceCode;
whsShipmentTable.CarrierGroupCode = whsLoadTable.CarrierGroupCode;
whsShipmentTable.LoadDirection = WHSLoadDirection::Inbound;
whsShipmentTable.CustomerRef = purchTable.VendorRef;
whsShipmentTable.insert();
//03/14/2024 - - BUG 255602 - CR 303 - Add license plate field for manual load creation import - START
whsUOMStructure.clear();
whsUOMStructure.LoadId = whsShipmentTable.LoadId;
whsUOMStructure.ShipmentId = whsShipmentTable.ShipmentId;
whsUOMStructure.Module = WHSModule::Purch;
whsUOMStructure.LicensePlateId = _licensePlateId;
whsUOMStructure.insert();
//03/14/2024 - - BUG 255602 - CR 303 - Add license plate field for manual load creation import - END
}
InventTable inventTable;
// Search for item based on ItemId or Search Name
select firstonly ItemId from inventTable
where inventTable.ItemId == itemId
exists join validatePurchLine
where validatePurchLine.PurchId == purchTable.PurchId
&& validatePurchLine.ItemId == inventTable.ItemId;
if (!inventTable.ItemId)
{
select firstonly ItemId from inventTable
where inventTable.NameAlias == itemId
exists join validatePurchLine
where validatePurchLine.PurchId == purchTable.PurchId
&& validatePurchLine.ItemId == inventTable.ItemId;
if (inventTable.ItemId)
{
itemId = inventTable.ItemId;
}
else
{
errorList += strFmt("@ANSARI:LoadPOError" + '\n', purchId, itemId);
curLine++;
hasError = true;
continue;
}
}
select firstonly purchLine
where purchLine.PurchId == purchId
&& purchLine.ItemId == itemId;
if (!purchRecIdSet.in(purchLine.RecId))
{
// Build load lines
whsLoadLine.clear();
whsLoadLine.initFromPurchLine(purchLine);
whsLoadLine.LoadId = whsLoadTable.LoadId;
whsLoadLine.InventTransType = InventTransType::Purch;
whsLoadLine.LoadDirection = WHSLoadDirection::Inbound;
whsLoadLine.OrderNum = purchLine.PurchId;
whsLoadLine.ItemId = purchLine.ItemId;
whsLoadLine.Qty = conPeek(conPeek(con, curLine), 3);
whsLoadLine.UOM = conPeek(conPeek(con, curLine), 4);
whsLoadLine.PackingQty = WHSInventTable::getDefaultPackingQty(whsLoadLine.ItemId, purchLine.InventDimId);
whsLoadLine.ShipmentId = whsShipmentTable.ShipmentId;
whsLoadLine.insert();
}
if (curLine == i)
{
curLine++;
}
purchRecIdSet.add(purchLine.RecId);
}
else
{
throw error(strFmt("@ANSARI:LoadPOError", purchId, itemId));
}
}
if (hasError == true)
{
container shipments = whsLoadTable.getShipmentIds();
if (shipments != conNull())
{
info(con2Str(errorList, '\n'));
}
else
{
throw error(con2Str(errorList, '\n'));
}
}
info(strFmt("@ANSARI:LoadCreated", whsLoadTable.LoadId));
ttscommit;
}
///
/// Reads data from Excel sheet
///
/// Stream of Excel file
/// Two dimensional container hold Excel data
///
/// Jan 30, 2019 - BLue Horseshoe Solutions, Inc. - Mod 018 Load creation import
///
public container readExcelData(System.IO.Stream _stream)
{
OfficeOpenXml.ExcelWorksheet worksheet;
OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(_stream);
int iRowCount,iCellCount;
anytype anyData;
container conRow,ret;
WHSParameters whsParameters = WHSParameters::find();
try
{
if (package)
{
worksheet = package.get_Workbook().get_Worksheets().Copy(whsParameters.ANSARILoadExcelSheet, whsParameters.ANSARILoadExcelJournal);
var cells = worksheet.get_Cells();
iRowCount = worksheet.get_Dimension().get_End().get_Row();
iCellCount = worksheet.get_Dimension().get_End().get_Column();
// Start at row 2 to allow for headers in Excel
for (int i=2;i<=iRowCount;i++)
{
conRow = conNull();
for (int j=1;j<=iCellCount;j++)
{
anyData= cells.get_Item(i, j).get_Value();
if (!anyData && j ==1)
{
break;
}
if (anyData)
{
conRow += anyData;
}
else
{
conRow += "";
}
}
if (conRow)
{
conRow += iRowCount;
ret = conIns(ret,i,conRow);
}
}
}
}
catch (Exception::CLRError)
{
throw error("@SYS135884");
}
return ret;
}
}
<![CDATA[<span class="pln">
</span>]]>
<![CDATA[<span class="pln">
</span><span class="kwd">class</span><span class="pln"> </span><span class="typ">ANSARIInventTransRegisterFormEventHandler</span><span class="pln">
</span><span class="pun">{</span><span class="pln">
</span><span class="com">/// <summary></span><span class="pln">
</span><span class="com">///</span><span class="pln">
</span><span class="com">/// </summary></span><span class="pln">
</span><span class="com">/// <param name="args"></param></span><span class="pln">
</span><span class="pun">[</span><span class="typ">PostHandlerFor</span><span class="pun">(</span><span class="pln">formStr</span><span class="pun">(</span><span class="typ">InventTransRegister</span><span class="pun">),</span><span class="pln"> formMethodStr</span><span class="pun">(</span><span class="typ">InventTransRegister</span><span class="pun">,</span><span class="pln"> autoTmpCreate</span><span class="pun">))]</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> </span><span class="kwd">void</span><span class="pln"> </span><span class="typ">InventTransRegister_Post_autoTmpCreate</span><span class="pun">(</span><span class="typ">XppPrePostArgs</span><span class="pln"> args</span><span class="pun">)</span><span class="pln">
</span><span class="pun">{</span><span class="pln">
</span><span class="typ">FormRun</span><span class="pln"> form </span><span class="pun">=</span><span class="pln"> args</span><span class="pun">.</span><span class="pln">getThis</span><span class="pun">();</span><span class="pln">
</span><span class="typ">FormDataSource</span><span class="pln"> tmpInventDim_DS </span><span class="pun">=</span><span class="pln"> form</span><span class="pun">.</span><span class="pln">dataSource</span><span class="pun">(</span><span class="pln">formDataSourceStr</span><span class="pun">(</span><span class="typ">InventTransRegister</span><span class="pun">,</span><span class="pln"> </span><span class="typ">TmpInventDim</span><span class="pun">))</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">FormDataSource</span><span class="pun">;</span><span class="pln">
</span><span class="typ">FormDataSource</span><span class="pln"> inventTransOriginMovement_DS </span><span class="pun">=</span><span class="pln"> form</span><span class="pun">.</span><span class="pln">dataSource</span><span class="pun">(</span><span class="pln">formDataSourceStr</span><span class="pun">(</span><span class="typ">InventTransRegister</span><span class="pun">,</span><span class="pln"> </span><span class="typ">InventTransOriginMovement</span><span class="pun">))</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">FormDataSource</span><span class="pun">;</span><span class="pln">
</span><span class="typ">InventDim</span><span class="pln"> inventDim </span><span class="pun">=</span><span class="pln"> tmpInventDim_DS</span><span class="pun">.</span><span class="pln">cursor</span><span class="pun">();</span><span class="pln">
</span><span class="typ">InventTransOrigin</span><span class="pln"> inventTransOrigin </span><span class="pun">=</span><span class="pln"> inventTransOriginMovement_DS</span><span class="pun">.</span><span class="pln">cursor</span><span class="pun">();</span><span class="pln">
</span><span class="typ">SalesTable</span><span class="pln"> salesTable</span><span class="pun">;</span><span class="pln">
</span><span class="typ">SalesLine</span><span class="pln"> salesLine</span><span class="pun">;</span><span class="pln">
</span><span class="typ">ReturnReasonCode</span><span class="pln"> returnReasonCode</span><span class="pun">;</span><span class="pln">
</span><span class="typ">ReturnReasonCodeGroup</span><span class="pln"> returnReasonCodeGroup</span><span class="pun">;</span><span class="pln">
str licensePlateId</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> firstonly salesTable </span><span class="kwd">where</span><span class="pln"> salesTable</span><span class="pun">.</span><span class="typ">SalesId</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> inventTransOrigin</span><span class="pun">.</span><span class="typ">ReferenceId</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> firstonly returnReasonCode </span><span class="kwd">where</span><span class="pln"> returnReasonCode</span><span class="pun">.</span><span class="typ">ReasonCodeId</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> salesTable</span><span class="pun">.</span><span class="typ">ReturnReasonCodeId</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> firstonly returnReasonCodeGroup </span><span class="kwd">where</span><span class="pln"> returnReasonCodeGroup</span><span class="pun">.</span><span class="typ">ReasonCodeGroupId</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> returnReasonCode</span><span class="pun">.</span><span class="typ">ReasonCodeGroupId</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> firstonly </span><span class="typ">LineNum</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> salesLine </span><span class="kwd">where</span><span class="pln"> salesLine</span><span class="pun">.</span><span class="typ">SalesId</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> inventTransOrigin</span><span class="pun">.</span><span class="typ">ReferenceId</span><span class="pln"> </span><span class="pun">&&</span><span class="pln"> salesLine</span><span class="pun">.</span><span class="typ">InventTransId</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> inventTransOrigin</span><span class="pun">.</span><span class="typ">InventTransId</span><span class="pln"> </span><span class="pun">;</span><span class="pln">
</span><span class="typ">LineNum</span><span class="pln"> lineNum </span><span class="pun">=</span><span class="pln"> salesLine</span><span class="pun">.</span><span class="typ">LineNum</span><span class="pun">;</span><span class="pln">
</span><span class="kwd">if</span><span class="pun">(</span><span class="pln">returnReasonCodeGroup</span><span class="pun">.</span><span class="typ">ANSARIAllowAutoGenLicensePlate</span><span class="pln"> </span><span class="pun">==</span><span class="pln"> </span><span class="typ">NoYes</span><span class="pun">::</span><span class="typ">Yes</span><span class="pun">){</span><span class="pln">
</span><span class="kwd">if</span><span class="pun">(</span><span class="pln">salesTable</span><span class="pun">.</span><span class="typ">ANSARIClaimNumber</span><span class="pln"> </span><span class="pun">!=</span><span class="pln"> </span><span class="str">""</span><span class="pun">)</span><span class="pln">
</span><span class="pun">{</span><span class="pln">
licensePlateId </span><span class="pun">=</span><span class="pln"> salesTable</span><span class="pun">.</span><span class="typ">ReturnItemNum</span><span class="pun">+</span><span class="str">"_"</span><span class="pun">+</span><span class="pln">salesTable</span><span class="pun">.</span><span class="typ">ANSARIClaimNumber</span><span class="pun">+</span><span class="str">"_"</span><span class="pun">+</span><span class="pln">int2Str</span><span class="pun">(</span><span class="pln">real2int</span><span class="pun">(</span><span class="pln">lineNum</span><span class="pun">));</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="kwd">else</span><span class="pln">
</span><span class="pun">{</span><span class="pln">
licensePlateId </span><span class="pun">=</span><span class="pln"> salesTable</span><span class="pun">.</span><span class="typ">ReturnItemNum</span><span class="pun">+</span><span class="str">"_"</span><span class="pun">+</span><span class="pln">strDel</span><span class="pun">(</span><span class="pln">salesTable</span><span class="pun">.</span><span class="typ">ReturnItemNum</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">3</span><span class="pun">)+</span><span class="str">"_"</span><span class="pun">+</span><span class="pln">int2Str</span><span class="pun">(</span><span class="pln">real2int</span><span class="pun">(</span><span class="pln">lineNum</span><span class="pun">));</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
inventDim</span><span class="pun">.</span><span class="typ">LicensePlateId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> licensePlateId</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
</span><span class="pun">}</span>]]>
