Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- class DCExportSMMOpportunityToExcel
- {
- smmOpportunityTable smmOpportunityTable;
- public static void main(Args _args)
- {
- DCExportSMMOpportunityToExcel esote = new DCExportSMMOpportunityToExcel();
- if(_args.record().TableId == tableNum(smmOpportunityTable))
- {
- esote.parmSmmOpportunityTable(_args.record());
- }
- esote.run();
- }
- public str real2str(real r)
- {
- return Num2Str(r,0,0,2,0);
- }
- public str real2str_mm(real r)
- {
- return Num2Str(r,0,2,DecimalSeparator::Dot,0);
- }
- public str priceReal2str(real r)
- {
- return Num2Str(r,10,2,1,0);
- }
- public void insertDuplicateRow(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument Excel,
- DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet,
- int headerLines,
- int i,
- container data
- )
- {
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("A%1",headerLines + i)),
- conPeek(data,1));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("B%1",headerLines + i)),
- conPeek(data,2));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("C%1",headerLines + i)),
- conPeek(data,3));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("E%1",headerLines + i)),
- conPeek(data,4));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("F%1",headerLines + i)),
- conPeek(data,5));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("G%1",headerLines + i)),
- conPeek(data,6));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("H%1",headerLines + i)),
- conPeek(data,7));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("I%1",headerLines + i)),
- conPeek(data,8));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("J%1",headerLines + i)),
- conPeek(data,9));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("K%1",headerLines + i)),
- conPeek(data,10));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("L%1",headerLines + i)),
- conPeek(data,11));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("M%1",headerLines + i)),
- conPeek(data,12));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("N%1",headerLines + i)),
- conPeek(data,13));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("O%1",headerLines + i)),
- conPeek(data,14));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("P%1",headerLines + i)),
- conPeek(data,15));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("Q%1",headerLines + i)),
- conPeek(data,16));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("R%1",headerLines + i)),
- conPeek(data,17));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("S%1",headerLines + i)),
- conPeek(data,18));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("T%1",headerLines + i)),
- conPeek(data,19));
- }
- public void run()
- {
- #File
- #characters
- #xppTexts
- DocumentFormat.OpenXml.Packaging.SpreadsheetDocument Excel;
- DocumentFormat.OpenXml.Packaging.WorkbookPart workbook;
- DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet;
- DocumentFormat.OpenXml.Spreadsheet.Sheet sheet;
- DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart;
- Filename templateFilename;
- FilenameSave filenameSave;
- FileIOPermission fileIoPermissionTemplate;
- FileIOPermission fileIoPermissionSave;
- Set permissionSet;
- System.IO.Stream fileStream;
- BinData binData;
- DictTable dictTable = new DictTable(tablenum(smmOpportunityTable));
- Common common = smmOpportunityTable;
- ExecutePermission perm = new ExecutePermission();
- str cellAddress;
- int i = 0;
- int headerLines = 5;
- smmOpportunityTable smmOppTable = common;
- LemSalesOpportunityLineRef lemSalesOppLineRef;
- InventTable inventTable;
- LemProductTable lemProductTable;
- InventDimCombination inventDimCombination;
- EcoResProductVariantColor ecoResProductVariantColor;
- EcoResProductVariantConfiguration ecoResProductVariantConfiguration;
- EcoResProductVariantStyle ecoResProductVariantStyle;
- EcoResConfiguration ecoResConfiguration;
- EcoResStyle ecoResStyle;
- InventModelGroupItem inventModelGroupItem;
- LemBasePriceImpregnation lemBasePriceImpregnation;
- CustTable custTable;
- LemItemPacks lemItemPacks;
- CustVendExternalItem custVendExternalItem;
- InventDim inventDim;
- EcoResColor ecoResColor;
- real colorPrice, finalPrice;
- UnitOfMeasureSymbol uom;
- WHSPhysDimUOM physDim;
- int lemItemPacksNum = 1;
- str productOrItemId;
- str productName;
- str externalItemText;
- ;
- DocuTable docuTable = DocuTable::find("Fail", smmOpportunityTable.TableId);
- if (docuTable)
- {
- templateFilename = docuTable.TemplateFilename;
- filenameSave = strRem(strFmt("%1_%2.xlsx",WinApiServer::getTempPath(),newGuid()),"{}");
- fileIoPermissionTemplate = new FileIOPermission(templateFilename, #io_read+#io_write);
- fileIoPermissionSave = new FileIOPermission(filenameSave, #io_read+#io_write);
- permissionSet = new Set(Types::Class);
- permissionSet.add(fileIoPermissionTemplate);
- permissionSet.add(fileIoPermissionSave);
- CodeAccessPermission::assertMultiple(permissionSet);
- WinAPIServer::copyFile(templateFilename, filenameSave);
- Excel = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument::Open(filenameSave,true);
- workbook = Excel.WorkbookPart;
- worksheet = DCUtils::getFirstWorkSheet(workbook.Workbook);
- if (worksheet == null)
- worksheet = DCUtils::getWorkbookFirstWorksheet(workbook);
- perm.assert();
- select DefaultDeliveryDate, RecId, CustItemGroupId from custTable where custTable.Party == smmOppTable.Party;
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,"B1"),
- DirPartyTable::getName(smmOppTable.Party));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,"B2"),
- date2Str(today(), 123, 2, DateSeparator::Dot, 2, DateSeparator::Dot, DateYear::Digits4));
- if (custTable.RecId)
- {
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,"B3"),
- int2Str(custTable.DefaultDeliveryDate));
- }
- // LEMHLP-280 -->
- //LemSalesOpportunityLineRef::correctOrderNums(smmOppTable.OpportunityId);
- while select EcoResProduct, ProductId, Qty,EcoResDistinctProductVariant from lemSalesOppLineRef order by OrderNum where lemSalesOppLineRef.SalesOpportunityId == smmOppTable.OpportunityId
- {
- select firstonly * from lemProductTable where lemProductTable.ProductId == lemSalesOppLineRef.ProductId;
- if (lemProductTable.RecId)
- {
- productName = lemProductTable.Name;
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("A%1",headerLines + i)),
- productName);
- productOrItemId = "";
- if (lemProductTable.ItemId)
- {
- productOrItemId = lemProductTable.ItemId;
- }
- else
- {
- productOrItemId = lemProductTable.ProductId;
- }
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("B%1",headerLines + i)),
- productOrItemId);
- inventDimCombination = InventDimCombination::findByDistinctProductVariant(lemSalesOppLineRef.EcoResDistinctProductVariant);
- inventDim = InventDim::find(inventDimCombination.InventDimId);
- custVendExternalItem.clear();
- select firstonly * from custVendExternalItem
- where custVendExternalItem.ItemId == lemProductTable.ItemId &&
- custVendExternalItem.InventDimId == inventDim.inventDimId &&
- custVendExternalItem.CustVendRelation == custTable.CustItemGroupId;
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("C%1",headerLines + i)),
- custVendExternalItem.ExternalItemTxt);
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("E%1",headerLines + i)),
- lemProductTable.WallThickness);
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("F%1",headerLines + i)),
- int2str(lemProductTable.ProductHeight));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("G%1",headerLines + i)),
- int2str(lemProductTable.ProductWidth));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("H%1",headerLines + i)),
- int2str(lemProductTable.ProductDepth));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("I%1",headerLines + i)),
- strFmt("%1",lemProductTable.InnerArea));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("J%1",headerLines + i)),
- strFmt("%1",lemProductTable.LemQtyInTm));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("K%1",headerLines + i)),
- this.real2str(lemSalesOppLineRef.Qty));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("L%1",headerLines + i)),
- int2str(lemProductTable.QtyOnTruck));
- select * from ecoResProductVariantColor where ecoResProductVariantColor.DistinctProductVariant ==
- lemSalesOppLineRef.EcoResDistinctProductVariant;
- select * from ecoResColor where ecoResColor.RecId == ecoResProductVariantColor.Color;
- select firstonly * from lemBasePriceImpregnation order by StartDate desc where lemBasePriceImpregnation.InventColorId == ecoResColor.Name
- && lemBasePriceImpregnation.StartDate <= DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());
- uom = "";
- colorPrice = 0;
- finalPrice = 0;
- if (lemBasePriceImpregnation.RecId)
- {
- uom = lemBasePriceImpregnation.UnitId;
- colorPrice = lemBasePriceImpregnation.Price;
- if (uom == "m2Act")
- {
- finalPrice = lemProductTable.Price + (lemProductTable.ColorArea * colorPrice);
- }
- else if (uom == "m3Act")
- {
- finalPrice = lemProductTable.Price + (lemProductTable.LemQtyInTm * colorPrice);
- }
- }
- else
- {
- finalPrice = lemProductTable.Price;
- }
- //inventDimCombination = InventDimCombination::findByDistinctProductVariant(lemSalesOppLineRef.EcoResDistinctProductVariant);
- //inventDim = InventDim::find(inventDimCombination.InventDimId);
- select * from ecoResProductVariantConfiguration
- where ecoResProductVariantConfiguration.DistinctProductVariant == lemSalesOppLineRef.EcoResDistinctProductVariant;
- select firstonly * from ecoResConfiguration where ecoResConfiguration.RecId == ecoResProductVariantConfiguration.Configuration;
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("M%1",headerLines + i)),
- strFmt("%1",ecoResConfiguration.Name));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("N%1",headerLines + i)),
- strFmt("%1",ecoResColor.Name));
- select * from ecoResProductVariantStyle
- where ecoResProductVariantStyle.DistinctProductVariant == lemSalesOppLineRef.EcoResDistinctProductVariant;
- select firstonly * from ecoResStyle where ecoResStyle.RecId == ecoResProductVariantStyle.Style;
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("O%1",headerLines + i)),
- strFmt("%1",ecoResStyle.Name));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("W%1",headerLines + i)),
- this.priceReal2str(finalPrice));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("V%1",headerLines + i)),
- lemProductTable.CustItemModelType);
- LemItemPacks itemPacks;
- itemPacks.clear();
- select firstonly * from itemPacks
- where itemPacks.ProductId == lemProductTable.ProductId
- && itemPacks.Color == ecoResColor.Name
- && itemPacks.Config == ecoResConfiguration.Name;
- if (itemPacks.RecId)
- {
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("P%1",headerLines + i)),
- this.real2str_mm(itemPacks.Weight));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("Q%1",headerLines + i)),
- this.real2str_mm(itemPacks.Width / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("R%1",headerLines + i)),
- this.real2str_mm(itemPacks.Length / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("S%1",headerLines + i)),
- this.real2str_mm(itemPacks.Height / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("T%1",headerLines + i)),
- num2Str((itemPacks.Height / 1000) * (itemPacks.Width / 1000) * (itemPacks.Length / 1000),0,2,DecimalSeparator::Dot,0));
- LemItemPacks itemPackDuplicates;
- while select * from itemPackDuplicates
- where itemPackDuplicates.ProductId == itemPacks.ProductId &&
- itemPackDuplicates.Color == itemPacks.Color &&
- itemPackDuplicates.Config == itemPacks.Config
- {
- if (itemPackDuplicates.RecId != itemPacks.RecId)
- {
- i++;
- container data = [productName, productOrItemId,custVendExternalItem.ExternalItemTxt, lemProductTable.WallThickness,
- int2str(lemProductTable.ProductHeight), int2str(lemProductTable.ProductWidth), int2str(lemProductTable.ProductDepth),
- lemProductTable.InnerArea, lemProductTable.LemQtyInTm, this.real2str(lemSalesOppLineRef.Qty),
- int2str(lemProductTable.QtyOnTruck), strFmt("%1",ecoResConfiguration.Name), strFmt("%1",ecoResColor.Name), strFmt("%1",ecoResStyle.Name),
- this.real2str_mm(itemPackDuplicates.Weight),this.real2str_mm(itemPackDuplicates.Width / 1000),this.real2str_mm(itemPackDuplicates.Length / 1000), this.real2str_mm(itemPackDuplicates.Height / 1000),
- num2Str((itemPackDuplicates.Height / 1000) * (itemPackDuplicates.Width / 1000) * (itemPackDuplicates.Length / 1000),0,2,DecimalSeparator::Dot,0)
- ];
- this.insertDuplicateRow(Excel,worksheet,headerLines,i,data);
- }
- }
- }
- else
- {
- LemItemPacks itemPacksConfig;
- select firstonly * from itemPacksConfig
- where itemPacksConfig.ProductId == lemProductTable.ProductId &&
- itemPacksConfig.Config == ecoResConfiguration.Name;
- if (itemPacksConfig.RecId)
- {
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("P%1",headerLines + i)),
- this.real2str_mm(itemPacksConfig.Weight));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("Q%1",headerLines + i)),
- this.real2str_mm(itemPacksConfig.Width / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("R%1",headerLines + i)),
- this.real2str_mm(itemPacksConfig.Length / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("S%1",headerLines + i)),
- this.real2str_mm(itemPacksConfig.Height / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("T%1",headerLines + i)),
- num2Str((itemPacksConfig.Height / 1000) * (itemPacksConfig.Width / 1000) * (itemPacksConfig.Length / 1000),0,2,DecimalSeparator::Dot,0));
- }
- else
- {
- LemItemPacks itemPacksColor;
- select firstonly * from itemPacksColor
- where itemPacksColor.ProductId == lemProductTable.ProductId &&
- itemPacksColor.Color == ecoResColor.Name;
- if (itemPacksColor.RecId)
- {
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("P%1",headerLines + i)),
- this.real2str_mm(itemPacksColor.Weight));
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("Q%1",headerLines + i)),
- this.real2str_mm(itemPacksColor.Width / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("R%1",headerLines + i)),
- this.real2str_mm(itemPacksColor.Length / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("S%1",headerLines + i)),
- this.real2str_mm(itemPacksColor.Height / 1000)); //LEMHLP-496 mm to m
- DCUtils::setCellValueNew(Excel,
- DCUtils::getCell(worksheet,strFmt("T%1",headerLines + i)),
- num2Str((itemPacksColor.Height / 1000) *
- (itemPacksColor.Width / 1000) *
- (itemPacksColor.Length / 1000),0,2,DecimalSeparator::Dot,0));
- }
- }
- }
- }
- i++;
- //<-- LEMHLP-280
- }
- CodeAccessPermission::revertAssert();
- worksheet.Save();
- Excel.Close();
- BinData = new BinData();
- BinData.loadFile(filenameSave);
- fileStream = Binary::constructFromContainer(BinData.getData()).getMemoryStream();
- File::SendFileToUser(fileStream,filenameSave);
- CodeAccessPermission::revertAssert();
- }
- }
- public void parmSmmOpportunityTable(smmOpportunityTable _smmOpportunityTable = smmOpportunityTable)
- {
- smmOpportunityTable = _smmOpportunityTable;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement