///
/// Creates load based on Excel file
///
///
/// Jan 30, 2019 - Blue Horseshoe Solutions, Inc. - 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 - TCS - extsuchakraborty - 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 - TCS - extsuchakraborty - 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 - TCS - extsuchakraborty - 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 - TCS - extsuchakraborty - 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 - TCS - extsuchakraborty - 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[
]]>
<![CDATA[
class ANSARIInventTransRegisterFormEventHandler
{
/// <summary>
///
/// </summary>
/// <param name="args"></param>
[PostHandlerFor(formStr(InventTransRegister), formMethodStr(InventTransRegister, autoTmpCreate))]
public static void InventTransRegister_Post_autoTmpCreate(XppPrePostArgs args)
{
FormRun form = args.getThis();
FormDataSource tmpInventDim_DS = form.dataSource(formDataSourceStr(InventTransRegister, TmpInventDim)) as FormDataSource;
FormDataSource inventTransOriginMovement_DS = form.dataSource(formDataSourceStr(InventTransRegister, InventTransOriginMovement)) as FormDataSource;
InventDim inventDim = tmpInventDim_DS.cursor();
InventTransOrigin inventTransOrigin = inventTransOriginMovement_DS.cursor();
SalesTable salesTable;
SalesLine salesLine;
ReturnReasonCode returnReasonCode;
ReturnReasonCodeGroup returnReasonCodeGroup;
str licensePlateId;
select firstonly salesTable where salesTable.SalesId == inventTransOrigin.ReferenceId;
select firstonly returnReasonCode where returnReasonCode.ReasonCodeId == salesTable.ReturnReasonCodeId;
select firstonly returnReasonCodeGroup where returnReasonCodeGroup.ReasonCodeGroupId == returnReasonCode.ReasonCodeGroupId;
select firstonly LineNum from salesLine where salesLine.SalesId == inventTransOrigin.ReferenceId && salesLine.InventTransId == inventTransOrigin.InventTransId ;
LineNum lineNum = salesLine.LineNum;
if(returnReasonCodeGroup.ANSARIAllowAutoGenLicensePlate == NoYes::Yes){
if(salesTable.ANSARIClaimNumber != "")
{
licensePlateId = salesTable.ReturnItemNum+"_"+salesTable.ANSARIClaimNumber+"_"+int2Str(real2int(lineNum));
}
else
{
licensePlateId = salesTable.ReturnItemNum+"_"+strDel(salesTable.ReturnItemNum, 1, 3)+"_"+int2Str(real2int(lineNum));
}
}
inventDim.LicensePlateId = licensePlateId;
}
}
]]>
