Part 4 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Place/Store/Upload the file in the blob folder using X++)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about placing or uploading the file in  the blob folder using X++ in Dynamics D365 Finance and Operations. Basically it is covering the write operations to the blob storage.

Perquisite:

1. Storage account
2. Blob container
3. Create 2 folders in the blob container

Create a new class in the Dynamics 365 finance and operations and add following methods in it.

• connectToAzureBlob
• uploadFileToAzureBlob
• moveTheFileBetweenFolders
• deleteTheFileFromFolder

And we will be using following .NET libraries.

• using Microsoft.WindowsAzure.Storage;
• using Micorosft.WindowsAzure.Storage.Blob;
• using System.IO;
• using System.Text;
• using System.Text.ASCIIEncoding;

Output:

1) Uploaded or creating new file in azure blob

2) Moved file to another folder

3) Deleted the file from folder

Part 3 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Consume or Read file details or data from the blob folder using X++)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about Consuming or Reading file details or data from the blob folder using X++ in Dynamics D365 Finance and Operations.

Perquisite:

1. Storage account
2. Blob container
3. Create folder in the blob container
4. Add a file to the blob container for testing (Using the Upload option in the Azure portal)

Sample text file

Create a new class in the Dynamics 365 finance and operations and add following methods in it.

• connectToAzureBlob
• GetFileNameList
• readFileValueFromMemoryStream

And we will be using following .NET libraries.

• using Microsoft.WindowsAzure.Storage;
• using Micorosft.WindowsAzure.Storage.Blob;
• using System.IO;

Sample code for the classes:

Output :  

Part 2 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Establish connection between Azure BLOB and D365 F&O)

Today, I will share another part of connecting azure blob storage with Dynamics 365 for finance and operations series.

This part will be about establishing the connection between azure blob and D365 F&O.

Perquisite:

1. Storage account
2. Blob container

1- Storage account

The application will require to access the connection string at runtime in order to authorize requests made to Azure Storage. We will use the connection string in Dynamics 365 Finance and Operations environment variable. (That variable can be part of parameters on the form)

The format of the connection string is :

DefaultEndpointsProtocol = [http|https];AccountName=myAccountName;AccountKey=myAccountKey

• HTTP or HTTPS (Whether you want to connect to either of one protocol)
• myAccountName : Replace it with your storage account
• myAccountKey : Replace with your account access key

You can find out the connection key value on this path in the Azure portal.

- Go to Storage account - Access keys 

2- Blob Account

- Go to Storage account > Storage Explorer (preview) > BLOB CONTAINER




3- Store the BLOB file path in the Dynamics 365 Finance and Operations

Blob container has a folder type structure and files will be accessed from certain path.
We will store the file path and store it in the Dynamics 365 F&O variable to access the file at the particular location. With the help of file path we can iterate the blob container to particular folder and access the files.

Steps to create folder in blob container.

• Go to Storage account > Storage Explorer (preview) > BLOB CONTAINER
• Click New Folder and enter the name
• Click OK

4- Create a new class and a code in it.(It will establish connection between Azure blob and D365 F&O)

We will use two .NET libraries to use Azure blob.

• using Microsoft.WindowsAzure.Storage;
• using Micorosft.WindowsAzure.Storage.Blob;

Output:

Part 1 - Connect Azure BLOB storage with Dynamics 365 for Finance and Operations - (Create Storage account and container in it)

Today, I will share the list of blogs which give the understanding of how to connect azure blob storage with Dynamics 365 for finance and operations and how to perform different operations on it.

Consider one of the common scenario where to need to exchange files between different applications such file can be created by D365 for finance and operations and third party app. As we all know that D365 does not provide accessibility to local path for Dynamics Production URL so we need to rely on another source.

The best and convenient way will be to handle situation using Microsoft Azure BLOB storage as central repository to exchange files between external system.

Part 1 - Create Azure BLOB and establish connection with Dynamics 365 for finance and operations

Perquisite:

• Need Azure subscription to access Azure storage and can be used by creating a free account here .

• Create a free account on Azure portal.
• Sign into the Azure portal.

1) Go to Storage account by click home icon and click on it to open storage accounts page.

2) Create Create button.

3) Enter the information for storage account and click Review+Create button.

4) Click Create (once validation is passed) as it will deploy the storage account.

5) Go back to the storage accounts menu and check the newly created storage accounts. 

6) Click the Storage account and go the access keys. Click the show keys to view the details of keys.

Note : Key 1 and Key 2 are basically the connections strings which will be used to access this storage account.

7) Go to the container and click the Container icon to create new one.

8) Enter the name and click Create.

 

Output : Newly created container.

View the Extensible Data Security Policy by using SQL query

 Today, I will share out the query which can used to view the Extensible data security policy in order to get insight what is happening on the backend  whether policy is created correctly or not.

This query will list down the details of all policies available in the system.

Note : We will use AxDB for it in the SQL

SELECT PrimaryTableAotName, QueryObjectAOTName,

ConstrainedTable, ModeledQueryDebugInfo, ContextType,ContextString,IsModeled

FROM [ModelSecPolRuntimeEx]

Output:

SQL Query to get security details (such as Security roles, duties and privileges)

 Today, I will sharing out the list of queries which can used to get the security details.

It will cover following combinations of data.

• Security roles
• Security roles to duties
• Security roles with privileges
• Security role and duty combination along with privileges

Following list of tables are used in it.

• SECURITYROLES
• SECURITYOBJECTCHILDREREFERENCES
• SECURITYDUTY
• SECURITYPRIVILEGE

-- Get the list  of all security roles 

Select Name as SecurityRoleName FROM SecurityRole;

-- Get the list of all security roles to duties

SELECT T2.Name as SecurityRole, T3.NAME as Duty 

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 ;

-- Get the ;ist of all security roles with privileges 

SELECT T2.Name as SecurityRole, T3.NAME as Privileges

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYPRIVILEGE T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 2 ;

-- Get the list of all role-duty combination with privilege 

SELECT T2.Name as SecurityRole, T2.AOTNAME as RoleSystemName,  T3.NAME AS Duty, T3.IDENTIFIER as DutySystemName, T5.NAME as Privilege, T5.IDENTIFIER as PrivilegeSystemName

FROM SECURITYOBJECTCHILDREREFERENCES T1 

JOIN SECURITYROLE T2 ON T1.IDENTIFIER = T2.AOTNAME 

JOIN SECURITYDUTY T3 ON T1.CHILDIDENTIFIER = T3.IDENTIFIER

JOIN SECURITYOBJECTCHILDREREFERENCES T4 on T4.IDENTIFIER = T3.IDENTIFIER

JOIN SECURITYPRIVILEGE T5 on T4.CHILDIDENTIFIER = T5.IDENTIFIER

WHERE T1.OBJECTTYPE = 0 AND T1.CHILDOBJECTTYPE = 1 

AND T4.OBJECTTYPE = 1 AND T4.CHILDOBJECTTYPE = 2;

Read JSON file data in AX 2009 using batch processing

Today, I will share the code snippet which can be used to read a json
file in AX 2009.

We will be using .NET Newtonsoft.Json.dll file to proceed with this work.

Download the Newtonsoft.Json.dll  and place it on this path : D:\Program Files (x86)\Microsoft Dynamics AX\50\Client\Bin.

We will create a new class with name as 'JSONReaderWrapper' and add following methods in it.

class JSONReaderWrapper

{

   Newtonsoft.Json.Linq.JObject    jObject;

}

//Add these methods

public int getIntNode(str path)

{

    return this.traversePath(path);

}

public real getRealNode(str path)

{

    return this.traversePath(path);

}

public str getStringNode(str path)

{

    return System.Convert::ToString(this.traversePath(path));

}

public boolean isFound(str _path)

{

    return this.traversePath(_path) != null;

}

public void loadJson(str _json)

{

    ;

    jObject = Newtonsoft.Json.Linq.JObject::Parse(_json);

}

public static JSONReaderWrapper parseJson(str _json)

{

    JSONReaderWrapper reader = new JsonReader();

    ;

    reader.loadJson(_json);

    return reader;

}

private anytype traversePath(str                               path,

                             Newtonsoft.Json.Linq.JContainer   obj = jObject)

{

    List                            pathElements;

    ListEnumerator                  le;

    Newtonsoft.Json.Linq.JValue     value;

    Newtonsoft.Json.Linq.JToken     token;

    Newtonsoft.Json.Linq.JTokenType thisType,

                                    nestedType;

    Newtonsoft.Json.Linq.JObject    newObject;

    Newtonsoft.Json.Linq.JArray     newArray;

    str                             current,

                                    thisTypeString,

                                    nestedTypeString;

    #define.JObject("Newtonsoft.Json.Linq.JObject")

    #define.JArray ("Newtonsoft.Json.Linq.JArray")

    ;

    pathElements = strSplit(path, @".\/");

    le = pathElements.getEnumerator();

    if (le.moveNext())

    {

        current = le.current();

        thisType = obj.GetType();

        thisTypeString = thisType.ToString();

        switch (thisTypeString)

        {

            case #JObject:

                token = obj.get_Item(current);

                break;

            case #JArray:

                token = obj.get_Item(str2int(current) - 1);

                break;

            default:

                return null;

        }

        if (token)

        {

            nestedType = token.GetType();

            nestedTypeString = nestedType.ToString();

            if (nestedTypeString != #JObject && nestedTypeString != #JArray)

            {

                switch (thisTypeString)

                {

                    case #JArray:

                        return obj.get_Item(str2int(current) - 1);

                    case #JObject:

                        return obj.get_Item(current);

                    default:

                        return null;

                }

            }

            switch (nestedTypeString)

            {

                case #JObject:

                    newObject = Newtonsoft.Json.Linq.JObject::FromObject(token);

                    return this.traversePath(strDel(path, 1, strLen(current) + 1), newObject);

                case #JArray:

                    newArray = Newtonsoft.Json.Linq.JArray::FromObject(token);

                    return this.traversePath(strDel(path, 1, strLen(current) + 1), newArray);

                default:

                    return null;

            }

        }

        else

        {

            return null;

        }

    }

    else

    {

        return null;

    }

}

//Create another class which extends RunBaseBatch and add a method in it.

Json input string  = "{\"ItemDef\":[{\"ItemId\":\"Test1\",\"Price\":1},{\"ItemId\":\"Test2\",\"Price\":2},{\"ItemId\":\"Test3\",\"Price\":3}]}";

client server static  void readJsonFileData(str _jsonInput)

{

    str              jsonResult;

    System.Exception ex;

    str              itemId;

    str              quantity;

    str              conditionCode;

    real             intResult;

    container        jsonConResult;

    int              i;

    JSONReaderWrapper   reader;

;

    try

    {

        jsonResult = _jsonInput;

        new InteropPermission(InteropKind::ClrInterop).assert();

        reader = JSONReaderWrapper::parseJson(jsonResult);

        for (i = 1; reader.isFound(strfmt("ItemDef.%1.ItemId", i)); i++)

        {

            itemId = reader.getStringNode(strfmt("ItemDef.%1.ItemId", i));

             price = reader.getIntNode(strfmt("ItemDef.%1.Price", i));

             info(strfmt("%1 = %2", itemId, price));

        }

     }

     catch(Exception::CLRError)

     {

        ex = CLRInterop::getLastException().GetBaseException();

        error(ex.get_Message());

     }

}

Read files from folder in AX 2009 with batch processing

 Today, I will be discuss out how to read files from folder in AX 2009.

Below is the code snippet which can be used to read files from folder in ax 2009 using batch job.

public void readFilesFromFolder()

{

    #Evat_NL

    #File

    Filename                        baseFolder;

    Filename                        filename;

    Filename                        foundBaseFileNameVal;

    int                             itemCounter;

    System.IO.DirectoryInfo         directoryFolder;

    System.IO.FileInfo[]            files;

    System.IO.FileInfo              filelist;

    InteropPermission               permission;

    counter                         filesCount;

    counter                         i;

   ;

     filePath      = "D:\\MJ\\Test";

  

    permission  = new InteropPermission(InteropKind::ClrInterop);

    permission.assert();

    baseFolder = filePath;

    directoryFolder  = new System.IO.DirectoryInfo(baseFolder);

    files      = directoryFolder.GetFiles("*.json");  //Depending upon file type

    filesCount = files.get_Length();

    for (i = 0; i < filesCount; i++)

    {

        filelist               = files.GetValue(i);

        fileName        = filelist.get_FullName();

        foundBaseFileNameVal  = filelist.get_Name();

       if (System.IO.File::Exists(FileName))

       {

            //Read txt from file

       }

    }

       CodeAccessPermission::revertAssert();

}

Create JSON file in AX 2009

 Today, I will share the code snippet which can be used to create a json file in AX 2009.

We will be using .NET Newtonsoft.Json.dll file to proceed with this work.

Download the Newtonsoft.Json.dll  and place it on this path : D:\Program Files (x86)\Microsoft Dynamics AX\50\Client\Bin.

Below is the code snippet to create JSON file.

static void DataExtractToJSON(Args _args)

{

    Dialog                  dialog;

    FilePath                filepath;

    DialogField             dialogFilePath;

    InventSum               inventSum;

    InventDim               inventDim;

    WMSLocation             wmsLocation;

    InventTable             inventTable;

    InventDimParm           invDimParm;

    Qty                     availPhys;

    str                     lpnnumber, lpnconstant, filename;

    int                     lpnCounter, lpnLen;

    date                    consumptionPriorityDate,expirationDate,manufacturedDate;

    TextIO                  file;

    FileIOPermission        fileIOPermission;

    container               line,header,header2;

    Newtonsoft.Json.Linq.JTokenWriter       writer;

    Newtonsoft.Json.Linq.JObject            jObject;

    ClrObject                               clrObject;

    str                                     jsonStr;

    ;

    #File

    dialog = new dialog();

    dialog.caption("Select the folder to save JSON file");

    dialogFilePath = dialog.addField(typeId(FilePath));

    dialogFilePath.label('FilePath');

    dialog.run();//execute dialog

    filepath = dialogFilePath.value();//return path file value

    if (filepath)

    {

        writer = new Newtonsoft.Json.Linq.JTokenWriter();

        writer.WriteStartObject();

        writer.WritePropertyName("Items");

        writer.WriteStartArray();

        fileName = FilePath+'\\test.json';

        new FileIOPermission(Filename,'w').assert();

        file = new TextIO(Filename,#io_write,1250);

        line = connull();

        while select inventLocationId, wmsLocationId,inventLocationId,locationType from wmsLocation

           join inventSum

           join inventDim

           join inventTable

           where inventDim.inventDimId == inventSUm.InventDimId

            && inventSum.ItemId == inventTable.ItemId

            && inventDim.InventLocationId == wmsLocation.inventLocationId

            && inventDim.wMSLocationId == wmsLocation.wMSLocationId

            && inventSum.Closed == NoYes::No

            && wmsLocation.checkText == ''

            && wmsLocation.locationType == WMSLocationType::Pick 

        {

               line = connull();

               availPhys = InventSum.availPhysical();

               if ( availPhys != 0)

               {

                    writer.WriteStartObject();

                    writer.WritePropertyName('testLocation');

                    writer.WriteValue(wmsLocation.wmsLocationId);

                    writer.WritePropertyName('TestType');

                    writer.WriteValue('LOCATION');

                    writer.WritePropertyName('TestTransactionType');

                    writer.WriteValue('INVENTORY_ADJUSTMENT');

                    writer.WritePropertyName('TestItemId');

                    writer.WriteValue(inventTable.ItemId);

                    writer.WritePropertyName('TestQty');

                    writer.WriteValue(availPhys);

                    writer.WriteEndObject();

               }

        }

        writer.WriteEndArray();

        writer.WriteEndObject();

        clrObject   = writer.get_Token();

        jObject     = clrObject;

        jsonStr = jObject.ToString();

        file.write(jsonStr);

        info(jObject.ToString());

        if(dialog.closedOk())

        {

            info(strfmt("Please check the JSON file on this path %1", filename));

        }

    }

}

Output :

Send an email with attachment in AX 2009 with batch processing

 Today, I will be sharing the code snippet which can utilized to send email along with attachment in a batch job in AX 2009. 

We will be using standard mail mechanism of the system and will utilize  SysOutgoingEmailTable and SysOutgoingEmailData tables.

Following are perquisites for the this process: 

• Email parameters filled in : Administration > Administration Area > Setup > E-mail parameters.
  
  
  
  
  
• Email distributor batch must be running : Administration > Administration Area > Periodic > E-mail processing > Batch.
• 
  
  
• A class which extends RunBaseBatch and add a method in it which will have below code snippet.

void sendEmail()

{

    SysOutgoingEmailTable       outgoingEmailTable;

    SysEmailItemId              nextEmailItemId;

    Map                         map;

    str                         SenderName, SenderEmail, To, Subject, Body;

    SysOutgoingEmailData        outgoingEmailData;

    FileIOPermission            fileIOPermission;

    InteropPermission           interopPermission;

    BinData                     binData;

    FileName                    attachmentFileName;

    SysEmailParameters          emailParams;

    int                         maxAttachmentSize;

    str                         tmpPath;

    str                         filePath;

    str                         fileName;

    str                         fileExtension;

    container                   attachmentData;

;

    try

    {

        SenderName    = "Tester";

        SenderEmail   = "test@gmail.com";

        To            = "test@gmail.com";

        Subject       = "Subject  for test ";

        Body          = "test email";

        

        //Email parameters

        emailParams       = SysEmailParameters::find();

        maxAttachmentSize = emailParams.MaxEmailAttachmentSize;

        

        ttsbegin;

        nextEmailItemId                  = EventInbox::nextEventId();

        outgoingEmailTable.EmailItemId   = nextEmailItemId;

        outgoingEmailTable.IsSystemEmail = NoYes::No;

        outgoingEmailTable.Sender        = SenderEmail;

        outgoingEmailTable.SenderName    = SenderName;

        outgoingEmailTable.Recipient     = To;

        outgoingEmailTable.Subject       = SysEmailMessage::stringExpand(Subject, map);

        outgoingEmailTable.Priority      = eMailPriority::Normal ;

        outgoingEmailTable.WithRetries   = false;

        outgoingEmailTable.RetryNum      = 0;

        outgoingEmailTable.UserId        = curUserId();

        outgoingEmailTable.Status        = SysEmailStatus::Unsent;

        outgoingEmailTable.Message       = Body;

        outgoingEmailTable.LatestStatusChangeDateTime = DateTimeUtil::getSystemDateTime();

        outgoingEmailTable.insert();

        attachmentFileName = 'D:\\test.xlsx';

        fileIOPermission = new FileIOPermission(attachmentfileName,'r');

                                    fileIOPermission.assert();

        if(WinAPIServer::fileExists(attachmentfileName) && (WinAPIServer::fileSize(attachmentfileName) < (maxAttachmentSize * 1000000)))

        {

            binData = new BinData();

            binData.loadFile(attachmentfileName);

            attachmentData = binData.getData();

        }

         CodeAccessPermission::revertAssert();

         

         //Add attachment record

         outgoingEmailData.EmailItemId       = nextEmailItemId;

         outgoingEmailData.DataId            = 1;

         outgoingEmailData.EmailDataType     = SysEmailDataType::Attachment;

         outgoingEmailData.Data              = attachmentData;

         [filePath, filename, fileExtension] = Global::fileNameSplit(attachmentfileName);

         outgoingEmailData.FileName          = filename;

         outgoingEmailData.FileExtension     = fileExtension;

         outgoingEmailData.insert();

         ttscommit;

     }

     catch

     {

        throw error("Failed to send email");

     }

}

Output :

Check your batch job status in the Basic > Basic Area > Common Forms > Batch job list- User.

Check your email status : Administration > Administration Area > Periodic > E-mail processing > E-mail sending status. 

Send email along with attachment in AX 2009 without batch processing

 Today, I will be sharing the code snippet which can utilized to send email along with attachment in a job in AX 2009. 

We will use System.Net.Mail framework and configured email parameters in the AX 2009.

To fill in parameters we will use the form available on this path : Administration > Administration Area > Setup > E-mail parameters.

Code snippet.

static void TestEmai1(Args _args)

{

    System.Net.Mail.MailMessage             mailMessage;

    System.Net.Mail.Attachment              attachment;

    System.Net.Mail.AttachmentCollection    attachementCollection;

    System.Net.Mail.SmtpClient              smtpClient;

    System.Net.Mail.MailAddress             mailAddressFrom;

    System.Net.Mail.MailAddress             mailAddressTo;

    str                                     Body;

    str                                     Subject;

    str                                     SMTPServer;

    str                                     FileName;

    FileIOPermission                        perm;

    ;

    mailAddressFrom = new System.Net.Mail.MailAddress("sender email","");

    mailAddressTo = new System.Net.Mail.MailAddress("recipient email","");

    Body = "<B>Body of the email</B>";

    Subject = "Subject line for the email";

    SMTPServer = SysEmailParameters::find(false).SMTPRelayServerName;

    mailMessage = new System.Net.Mail.MailMessage(mailAddressFrom, mailAddressTo);

    mailmessage.set_Subject(Subject);

    mailmessage.set_Body(Body);

    attachementCollection = mailMessage.get_Attachments();

    // Add attachemnts! use double slashes ("\") in the filename path.

    FileName = "D:\\test.xlsx";

    perm = new FileIOPermission(FileName,'w');

    perm.assert();

    attachment = new System.Net.Mail.Attachment(FileName);

    attachementCollection.Add(attachment);

    smtpClient = new System.Net.Mail.SmtpClient(SMTPServer);

    smtpClient.Send(mailmessage);

    CodeAccessPermission::revertAssert();

}

Expected output: