D365FFO - Dialog per caricare e leggere un file excel

In questo post vediamo come creare una semplice dialog che consente di caricare e leggere un file excel in locale. Ho preso spunto da questo post per creare la classe: 

https://www.linkedin.com/pulse/excel-import-dynamics-ax-365-ax7-using-dialog-shashi-kant-yadav

Questa è la classe che effettua la lettura del file:

 class LILFileExcelDataReader  
 {  
   str     fileURL;  
   str     sheetName;  
   container  dataContainer;  
   
   public static LILFileExcelDataReader construct(str _fileUrl, str _sheetName)  
   {  
     LILFileExcelDataReader instance = new LILFileExcelDataReader();  
   
     instance.parmFileURL(_fileUrl);  
   
     instance.parmSheetName(_sheetName);  
   
     return instance;  
   }  
   
   public str parmFileURL(str _fileUrl = fileURL)  
   {  
     fileURL = _fileUrl;  
   
     return fileURL;  
   }  
   
   private str getTMPSheetName()  
   {  
     return strFmt("TMP_%1_%2",date2str(  
                   today(),  
                   321,  
                   DateDay::Digits2,  
                   DateSeparator::None,  
                   DateMonth::Digits2,  
                   DateSeparator::None,  
                   DateYear::Digits4  
                   ),timeNow());  
   }  
   
   public str parmSheetName(str _sheetName = sheetName)  
   {  
     sheetName = _sheetName;  
   
     return sheetName;  
   }  
   
   public container getData()  
   {  
     return dataContainer;  
   }  
   
   private boolean validate()  
   {  
     boolean ret = true;  
   
     if(!this.parmFileURL())  
     {  
       return checkFailed("@SYS91305");  
     }  
   
     if(!this.parmSheetName())  
     {  
       return checkFailed("@GEE33476");  
     }  
   
     return ret;  
   }  
   
   public void read()  
   {  
     System.Byte[] byteArray;  
     System.IO.Stream   stream;  
   
     try  
     {  
       if(!this.validate())  
       {  
         throw error("@SYS93835");  
       }  
   
       stream = File::UseFileFromURL(this.parmFileURL());  
       //this.fillSheetList(stream);  
       dataContainer = this.readExcelData(stream);  
     }  
     catch(Exception::Error)  
     {  
       info(strFmt("%1 %2",Exception::Error,fileUrl));  
     }  
   }  
   
   public static List fillSheetList(System.IO.Stream   _stream)  
   {  
     OfficeOpenXml.ExcelWorksheets  workSheets;  
     OfficeOpenXml.ExcelWorksheet  sheet;  
     List    sheetList = new List(Types::String);  
     OfficeOpenXml.ExcelPackage   package = new OfficeOpenXml.ExcelPackage(_stream);  
   
     workSheets = package.get_Workbook().get_Worksheets();  
   
     var enumerator = workSheets.GetEnumerator();  
   
     while (enumerator.MoveNext())  
     {  
       sheet = enumerator.Current as OfficeOpenXml.ExcelWorksheet;  
   
       sheetList.addEnd(sheet.Name);  
     }  
   
     return sheetList;  
   }  
   
   private container readExcelData(System.IO.Stream   _stream)  
   {  
     OfficeOpenXml.ExcelWorksheet  _worksheet;  
       
     OfficeOpenXml.ExcelPackage   package = new OfficeOpenXml.ExcelPackage(_stream);  
     int               iRowCount,iCellCount;  
     anytype             anyData;  
     container            conRow,  
                     conData;  
     try  
     {  
       if(package)  
       {  
         //copy data to temporany sheet  
         _worksheet = package.get_Workbook().get_Worksheets().Copy(this.parmSheetName(),this.getTMPSheetName());  
         var cells = _worksheet.get_Cells();  
         iRowCount = _worksheet.get_Dimension().get_End().get_Row();  
         iCellCount = _worksheet.get_Dimension().get_End().get_Column();  
   
         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;  
             conData = conIns(conData,i,conRow);  
           }  
         }  
       }  
     }  
     catch (Exception::CLRError)  
     {  
       throw error("@SYS135884");  
     }  
   
     return conData;  
   }  
   
 }  

Questa classe RunBase mostra come utilizzarla:

 class LILExcelReaderDialog extends RunBase  
 {  
   str       fileUrl;  
   str       sheetName;  
   
   DialogRunbase  dialog;  
   
   FormBuildStringControl sheetNameCtrl;  
   
   DialogField   dlgSheetName;  
   
   container    dataContainer;  
   
   #define.CurrentVersion(1)  
   #define.Version1(1)  
   #localmacro.CurrentList  
     sheetName  
   #endmacro  
   
   public container pack()  
   {  
     return [#CurrentVersion,#CurrentList];  
   }  
   
   public boolean unpack(container _packedClass)  
   {  
     Integer   version   = RunBase::getVersion(_packedClass);  
   
     switch (version)  
     {  
       case #CurrentVersion  :  
         [version,#CurrentList] = _packedClass;  
         break;  
       default :  
         return false;  
     }  
   
     return true;  
   }  
   
   protected boolean canRunInNewSession()  
   {  
     return false;  
   }  
   
   private str getSessionID()  
   {  
     return strFmt("%1_%2",date2str(  
                   today(),  
                   321,  
                   DateDay::Digits2,  
                   DateSeparator::None,  
                   DateMonth::Digits2,  
                   DateSeparator::None,  
                   DateYear::Digits4  
                   ),timeNow());  
   }  
   
   public void sheetName_lookup(FormStringControl _executionControl)  
   {  
     if(!fileUrl)  
     {  
       warning("Please upload a file first!");  
   
       return;  
     }  
   
     List  valueList = LILFileExcelDataReader::fillSheetList(File::UseFileFromURL(fileUrl));  
   
     FormRun lookupForm = classFactory.createSysLookupPicklist(_executionControl);  
     lookupForm.init();  
       
     sysPickList pickList = new sysPickList(lookupForm);  
     lookupForm.choices(list2Con(valueList));  
     _executionControl.performFormLookup(lookupForm);  
   }  
   
   public Object dialog()  
   {  
     dialog = super();  
     FormBuildButtonControl buttonControl;  
     DialogGroup       dlgGroup;  
     FormBuildGroupControl  buttonGroup;  
     #resAppl  
     ;  
   
     dlgGroup    = dialog.addGroup("@SYS7764");  
     dlgGroup.columns(1);  
   
     buttonGroup  = dialog.formBuildDesign().control(dlgGroup.formBuildGroup().id());  
     buttonControl = buttonGroup.addControl(FormControlType::Button, "@ElectronicReporting:Upload");  
     buttonControl.text("@DMF1951");  
     buttonControl.registerOverrideMethod(methodStr(FormButtonControl, clicked),  
                       methodStr(LILExcelReaderDialog, uploadClickedEvent),  
                       this);  
   
     dlgSheetName = dialog.addFieldValue(extendedTypeStr(Name),   sheetName, "Sheet name");  
     sheetNameCtrl = dlgSheetName.fieldControl();  
     sheetNameCtrl.extendedDataType(extendedtypenum(Name));  
     sheetNameCtrl.registerOverrideMethod(methodStr(FormStringControl, lookup),  
                       methodStr(LILExcelReaderDialog, sheetName_lookup),  
                       this);  
   
       
     return dialog;  
   }  
   
   private void uploadClickedEvent(FormButtonControl _formButtonControl)  
   {  
     FileUploadTemporaryStorageResult result = File::GetFileFromUser() as FileUploadTemporaryStorageResult;  
   
     if (result && result.getUploadStatus())  
     {  
       result.getFileContentType();  
       fileUrl = result.getDownloadUrl();  
   
       info("@DMF1952");  
     }  
   }  
   
   Public void  readExcel()  
   {  
     LILFileExcelDataReader fileExcelDataReader = LILFileExcelDataReader::construct(fileUrl,sheetName);  
     fileExcelDataReader.read();  
   
     dataContainer = fileExcelDataReader.getData();  
   }  
   
   public boolean validate(Object calledFrom = null)  
   {  
     boolean ret;  
     
     ret = super(calledFrom);  
   
     if(ret && !fileUrl)  
     {  
       ret = checkFailed(strFmt("@MCR11498",fileUrl));  
     }  
   
     if(ret && !sheetName)  
     {  
       ret = checkFailed(strFmt("@SYS26332","@ElectronicReportingForAx:ExcelSheet"));  
     }  
    
     return ret;  
   }  
   
   protected void update()  
   {  
     //Metti quì le logiche...
     //La variabile dataContainer è un container di container che contiene tutti i valori
   }  
   
   public void run()  
   {  
     Voucher   voucher;  
     int     i;  
     container  line;  
     str         s;  
     #OCCRetryCount  
           
   
     if (!this.validate())  
     {  
       throw error("@SYS18447");  
     }  
   
     this.readExcel();  
   
     this.update();  
   }  
   
   public static LILExcelReaderDialog construct()  
   {  
     return new LILExcelReaderDialog();  
   }  
   
   public boolean getFromDialog()  
   {  
     sheetName   = dlgSheetName.value();  
   
     return super();  
   }  
   
   public boolean runsImpersonated()  
   {  
     return true;  
   }  
   
   public boolean init()  
   {  
     return true;  
   }  
   
   public static void main(Args _args)  
   {  
     LILExcelReaderDialog excelReaderDialog = LILExcelReaderDialog::construct();  
   
     if(excelReaderDialog.prompt())  
     {  
       excelReaderDialog.runOperation();  
     }  
   }  
   
 }  

La dialog si presenta così:

AX2012 - How to extract TMSCarrier addresses and addresses roles

 If you need to extract from an AX2012 R3 the full list of addresses and their roles you can use the following job.

 static void A_GiaImportCarrierAddresses(Args _args)  
 {  
      TMSCarrier             TMSCarrier;  
      LogisticsPostalAddress       LogisticsPostalAddress;  
      Tmscarrierlogisticslocation     TMScarrierlogisticslocation;  
      TMSCarrierLogisticsLocationRole   TMSCarrierLogisticsLocationRole;  
      LogisticsLocation          LogisticsLocation;  
      LogisticsLocationRole        logisticslocationrole;  
   
      while select TMScarrier  
           join TMScarrierlogisticslocation  
           where TMSCarrier.recid == TMScarrierlogisticslocation.Carrier  
           join LogisticsPostalAddress  
           where LogisticsPostalAddress.Location == TMScarrierlogisticslocation.Location  
           join LogisticsLocation  
           where LogisticsLocation.recid == LogisticsPostalAddress.Location  
           join TMSCarrierLogisticsLocationRole  
           where TMSCarrierLogisticsLocationRole.LocationLogisticsLocation == TMScarrierlogisticslocation.recid  
           join LogisticsLocationRole  
           where logisticslocationrole.RecId == TMSCarrierLogisticsLocationRole.LocationRole  
   {  
     info( strFmt("%1; %2; %3; %4; %5; %6",LogisticsPostalAddress.street,  
                                                         LogisticsPostalAddress.ZipCode,  
                                                         LogisticsPostalAddress.City,  
                                                         LogisticsPostalAddress.CountryRegionId,   
                                                         LogisticsLocation.Description,  
                                                         logisticslocationrole.Name));  
   }  
 }  

The result will be the following one.

AX 2012 - Esplosione albero BOM in x++

In questo post vediamo come esplodere la BOM di un articolo. Ho preso spunto da questi due post e li ho rielaborati:

• http://axforum.info/forums/showthread.php?t=21707
• https://daxbeginners.wordpress.com/2013/12/23/how-to-explode-bom-trough-x/

Il primo è interessante perchè mostra come usare la classe BOMSearch, però non è utilizzabile in quanto in AX 2012 l'enumerato InventTable.ItemType == ItemType::BOM è deprecato, il secondo è a mio avviso incompleto perchè espande la BOM più del necessario. Il fatto che un articolo abbia una BOM valida è condizione necessaria ma NON sufficiente per poterlo esplodere,  occorre che siano vere queste condizioni:

1) InventTable.PmfProductType  = BOM

2) In default order settings e anche in copertura articoli, abbiamo il default order type che ci dice se è Production o Purchase. Il campo è InventItemSetupSupplyType.DefaultOrderType

Il metodo BOMDesignerCtrl::itemCanHaveBOM(), usato nel "BOM designer" standard effettua questi due controlli.

Ecco la classe che effettua l'esplosione:

 class LIL_BomExplosionBOMSearch  
 {  
   BOMVersion   bomVersion;  
 }  

 public static LIL_BomExplosionBOMSearch construct(BOMVersion _bomVersion)  
 {  
   LIL_BomExplosionBOMSearch instance = new LIL_BomExplosionBOMSearch();  
   instance.parmBomVersion(_bomVersion);  
   return instance;  
 }  

 public void run()  
 {  
   BOMSearch search = BOMSearch::newBOMTree(this.parmBomVersion().BOMId,  
                        today(),  
                        this.parmBomVersion().ItemId,  
                        this.parmBomVersion().inventDim().configId);  
   this.BomExplode(search,0);  
 }  

 private boolean hasValidBomVersion(ItemId _itemId)  
 {  
   BOMVersion bomVersionLocal;  
   ;  
   select firstonly RecId from bomVersionLocal  
       where bomVersionLocal.ItemId == _itemid  
       && bomVersionLocal.Active  
       && bomVersionLocal.Approved  
       && bomVersionLocal.FromDate <= systemdateget ()  
       && (!bomVersionLocal.ToDate || bomVersionLocal.ToDate >= systemdateget ());  
   if (bomVersionLocal.RecId)  
   {  
     return true;  
   }  
   return false;  
 }  

 void BomExplode(BOMSearch _search, BOMLevel _level = 0)  
 {  
   BOM     bom;  
   BOMSearch  BomSearchLocal;  
   InventTable inventTable;  
   BOMId    bomId;  
   ;  
   //max tree deep level reached, trow error  
   if(_level > BOMParameters::find().BOMMaxLevel)  
   {  
     throw error("@SYS26729");  
   }  
   //root Item  
   if (_level == 0)  
   {  
     info(strFmt("%1 - %2",_level,this.parmBomVersion().ItemId));  
   }  
   while (_search.next())  
   {  
     bom = _search.BOM();  
     inventTable = bom.inventTable();  
           info(strFmt("%1 - %2",_level,bom.ItemId));  
     //verify if item can be exploded  
     if (BOMDesignerCtrl::itemCanHaveBOM(bom.inventTable()) &&  
       this.hasValidBomVersion(bom.ItemId))   
     {  
       //find active bom  
       bomId = inventTable.bomId(today(),bom.BOMQty,bom.inventDim());  
       BomSearchLocal = BOMSearch::newBOMTree(bomId,  
                     today(),  
                     bom.ItemId,  
                     bom.inventDim().configId);  
       this.BomExplode(BomSearchLocal, _level+1);  
     }  
   }  
 }  

La chiamata alla classe:

 LIL_BomExplosionBOMSearch     LIL_BomExplosionBOMSearch = LIL_BomExplosionBOMSearch::construct(bomVersion);  
 LIL_BomExplosionBOMSearch.run();  

Come nota tecnico / teorico, secondo la teoria degli alberi, questa ricerca sull'albero BOM è una ricerca di tipo DFS (Depth-first search) perchè non appena trova un nodo a livello (n) passa subito al suo primo figlio a livello (n+1):