Month: August 2012

Posted on

Service to import from Serial Port

Similar to the last post I have also been asked to listen to a serial port and import the data into NAV.  I even used the same VB.NET service.  On the NAV side I added two functions to my web service.

Where RMSerial is a table with the following code.

Here is the VB.NET code for the service.

[code lang=”vb”]Imports System
Imports System.Timers
Imports System.Net
Imports System.IO
Imports System.IO.Ports

Public Class FileImportService
Dim Salvor1 As Salvor.SalvorWebService
Dim Timer2 As System.Timers.Timer
Dim User As New System.Net.NetworkCredential
Dim Serial1 As New System.IO.Ports.SerialPort

Protected Overrides Sub OnStart(ByVal args() As String)
‘ Add code here to start your service. This method should set things
‘ in motion so your service can do its work.
Salvor1 = New Salvor.SalvorWebService
User.Domain = "<Domain>"
User.UserName = "<User>"
User.Password = "<Password>"
Salvor1.Credentials = User

Timer2 = New System.Timers.Timer(30000)
AddHandler Timer2.Elapsed, AddressOf ProcessSerialData
Timer2.Interval = 30000
Timer2.Enabled = True
Timer2.Stop()

If My.Settings.COMPort <> "" Then
If Serial1.IsOpen Then
Serial1.Close()
End If
WriteDebug("COM Port Selected: " & My.Settings.COMPort)
Serial1.PortName = My.Settings.COMPort
Serial1.BaudRate = 9600
Serial1.Parity = Parity.Even
Serial1.DataBits = 8
Serial1.StopBits = 1
Serial1.Open()
If Serial1.IsOpen Then
WriteDebug("Serial Port Opened")
Else
WriteDebug("Failed to open Serial Port")
End If
AddHandler Serial1.DataReceived, AddressOf Serial1_DataReceived
End If
End Sub

Protected Overrides Sub OnStop()
‘ Add code here to perform any tear-down necessary to stop your service.
If My.Settings.COMPort <> "" Then
If Serial1.IsOpen Then
Serial1.Close()
End If
End If
End Sub

Protected Sub Serial1_DataReceived()
Timer2.Stop()
WriteDebug("Serial Event Started, buffer size: " & Serial1.ReadBufferSize)
Dim LineRead As String
Dim LineResponse As String = ""
Dim WaitLoop As Integer = 0
LineRead = Serial1.ReadExisting
If LineRead = "!" Then
LineResponse = "$"
ElseIf LineRead = "*" Then
LineResponse = "&"
ElseIf Left(LineRead, 1) = "[" Then
While Right(LineRead, 1) <> "]" And WaitLoop < 10
Threading.Thread.Sleep(100)
LineRead = LineRead & Serial1.ReadExisting
WaitLoop = WaitLoop + 1
End While
If Right(LineRead, 1) = "]" Then
Try
If Salvor1.AboutCompany = "SAM" Then
LineResponse = Salvor1.COMPort(LineRead)
End If
Catch ex As Exception
LineResponse = "%"
WriteToEventLog("Web Service unavailable:" & ex.ToString, EventLogEntryType.Error)
End Try
Else
LineResponse = "%"
End If
End If
Serial1.Write(LineResponse)
WriteDebug("Serial read: " & LineRead)
WriteDebug("Serial response: " & LineResponse)
Timer2.Interval = 30000
Timer2.Enabled = True
Timer2.Start()
End Sub

Protected Sub ProcessSerialData()
Timer2.Enabled = False
Timer2.Stop()
Dim Success As Boolean
Try
Success = Salvor1.ProcessSerialData
If Not Success Then
WriteToEventLog("Failed to process serial data", EventLogEntryType.Error)
End If
Catch ex As Exception
WriteToEventLog("Web Service unavailable:" & ex.ToString, EventLogEntryType.Error)
End Try
End Sub

Protected Sub WriteToEventLog(ByVal Message As String, ByVal EntryType As EventLogEntryType)
Dim MyLog As New EventLog()
‘ Check if the the Event Log Exists
If Not Diagnostics.EventLog.SourceExists(Me.ServiceName) Then
Diagnostics.EventLog.CreateEventSource(Me.ServiceName, Me.ServiceName & " Log")
‘ Create Log
End If
MyLog.Source = Me.ServiceName
‘ Write to the Log
Diagnostics.EventLog.WriteEntry(MyLog.Source, Message, EntryType)
End Sub

Protected Sub WriteDebug(ByVal Message As String)
If My.Settings.Debug Then
WriteToEventLog(Message, EventLogEntryType.Information)
End If
End Sub
End Class
[/code]

Posted on

Service to import files

The task is to import every file that is dropped into a specific folder on my local drive into NAV.  The solution is a windows service programmed in Visual Studio 2008 VB.NET.

The first step is to create a web service in Dynamics NAV that accepts a text line and a file name.  Another function to remove the file if the import fails and the third to process the file after it has been imported.

The vb.net code from Visual Studio

[code lang=”vb”]Imports System
Imports System.Timers
Imports System.Net
Imports System.IO

Public Class FileImportService
Dim Salvor1 As Salvor.SalvorWebService
Dim Timer1 As System.Timers.Timer
Dim User As New System.Net.NetworkCredential

Protected Overrides Sub OnStart(ByVal args() As String)
‘ Add code here to start your service. This method should set things
‘ in motion so your service can do its work.
Salvor1 = New Salvor.SalvorWebService
User.Domain = "<Domain>"
User.UserName = "<User>"
User.Password = "<Password>"
Salvor1.Credentials = User

Timer1 = New System.Timers.Timer(30000)
AddHandler Timer1.Elapsed, AddressOf OnTimedEvent

Timer1.Interval = 30000
Timer1.Enabled = True
Timer1.Start()
WriteDebug("Timer 1 Started")

‘ If the timer is declared in a long-running method, use
‘ KeepAlive to prevent garbage collection from occurring
‘ before the method ends.
GC.KeepAlive(Timer1)

End Sub

Protected Overrides Sub OnStop()
‘ Add code here to perform any tear-down necessary to stop your service.
End Sub

Protected Sub OnTimedEvent(ByVal source As Object, ByVal e As ElapsedEventArgs)
Timer1.Enabled = False
Timer1.Stop()
WriteDebug("File Event Started")
Try
If Salvor1.AboutCompany = "SAM" Then
ReadFolder()
End If
Catch ex As Exception
WriteToEventLog("Web Service unavailable:" & ex.ToString, EventLogEntryType.Error)
End Try
Timer1.Enabled = True
Timer1.Start()
End Sub

Protected Sub ReadFolder()
Dim dirInfo As New DirectoryInfo(My.Settings.ImportFolder)
Dim FileArray As FileInfo() = dirInfo.GetFiles()

For Each TextFile In FileArray
If ReadFile(TextFile) Then
WriteDebug("Check File: " & TextFile.Name)
If Salvor1.ProcessFile(TextFile.Name) Then
DeleteFile(TextFile)
Else
WriteDebug("Rollback File: " & TextFile.Name)
Salvor1.RemoveFile(TextFile.Name)
End If

Else
Salvor1.RemoveFile(TextFile.Name)
End If
Next

End Sub

Protected Function ReadFile(ByVal TextFile As FileInfo) As Boolean
Dim Success As Boolean
Try
If File.Exists(TextFile.FullName) Then
Dim ioFile As New StreamReader(TextFile.FullName)
Dim ioLine As String
Success = True

While Not ioFile.EndOfStream
ioLine = ioFile.ReadLine
Success = Success And Salvor1.InsertLine(TextFile.Name, ioLine)
End While
ioFile.Close()
End If
Catch ex As Exception
Success = False
WriteToEventLog("Import of file " & TextFile.FullName & " failed:" & ex.ToString, EventLogEntryType.Error)
End Try
Return Success
End Function

Protected Sub DeleteFile(ByVal TextFile As FileInfo)
Try
WriteDebug("Delete File: " & TextFile.Name)
TextFile.Delete()
Catch ex As Exception
WriteToEventLog("Failed to delete file " & TextFile.FullName & ":" & ex.ToString, EventLogEntryType.Error)
End Try
End Sub

Protected Sub WriteToEventLog(ByVal Message As String, ByVal EntryType As EventLogEntryType)
Dim MyLog As New EventLog()
‘ Check if the the Event Log Exists
If Not Diagnostics.EventLog.SourceExists(Me.ServiceName) Then
Diagnostics.EventLog.CreateEventSource(Me.ServiceName, Me.ServiceName & " Log")
‘ Create Log
End If
MyLog.Source = Me.ServiceName
‘ Write to the Log
Diagnostics.EventLog.WriteEntry(MyLog.Source, Message, EntryType)
End Sub

Protected Sub WriteDebug(ByVal Message As String)
If My.Settings.Debug Then
WriteToEventLog(Message, EventLogEntryType.Information)
End If
End Sub
End Class[/code]

Posted on

A DotNet Interop Soap Web Request

I am currently working on a solution that requires a Dynamics NAV client to communicate with Dynamics NAV web service.  This I have done before with the classic client and have used automation objects for the job.  Now I wanted to do this with dotnet only objects in the Role Tailored Client.  Took some time to put all things together but here it is.  This version is running the request from the client.

OBJECT Codeunit 50027 IC Addon Inbox WebService
{
  OBJECT-PROPERTIES
  {
    Date=09.04.14;
    Time=17:28:02;
    Modified=Yes;
    Version List=IC7.10;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;

  }
  CODE
  {

    PROCEDURE LoadTransaction@1100408001(FromPartnerCode@1100408004 : Code[20];FromRespCenterCode@1100408005 : Code[10];ToPartnerCode@1100408006 : Code[20];ToRespCenterCode@1100408007 : Code[10];Transaction@1100408000 : BigText;PDFInvoice@1100408001 : BigText;PDFDetails@1100408002 : BigText;XMLInvoice@1100408003 : BigText;VAR ResponseMessage@1100408009 : Text) Success : Boolean;
    VAR
      Loader@1000000000 : Codeunit 50019;
      TransactionStream@1000000014 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      PDFInvoiceStream@1000000016 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      PDFDetailsStream@1000000015 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      XMLInvoiceStream@1000000013 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
    BEGIN
      TransactionStream := TransactionStream.MemoryStream;
      PDFInvoiceStream := PDFInvoiceStream.MemoryStream;
      PDFDetailsStream := PDFDetailsStream.MemoryStream;
      XMLInvoiceStream := XMLInvoiceStream.MemoryStream;
      IF Transaction.LENGTH > 0 THEN
        Transaction.WRITE(TransactionStream);
      IF PDFInvoice.LENGTH > 0 THEN
        PDFInvoice.WRITE(PDFInvoiceStream);
      IF PDFDetails.LENGTH > 0 THEN
        PDFDetails.WRITE(PDFDetailsStream);
      IF XMLInvoice.LENGTH > 0 THEN
        XMLInvoice.WRITE(XMLInvoiceStream);

      Loader.SetProperties(
        FromPartnerCode,
        FromRespCenterCode,
        ToPartnerCode,
        ToRespCenterCode,
        TransactionStream,
        PDFInvoiceStream,
        PDFDetailsStream,
        XMLInvoiceStream,
        Transaction.LENGTH > 0,
        PDFInvoice.LENGTH > 0,
        PDFDetails.LENGTH > 0,
        XMLInvoice.LENGTH > 0);

      IF Loader.RUN THEN
        EXIT(TRUE)
      ELSE BEGIN
        ResponseMessage := GETLASTERRORTEXT;
        EXIT(FALSE);
      END;
    END;

    BEGIN
    END.
  }
}

OBJECT Codeunit 50028 IC Addon Web Service Client
{
  OBJECT-PROPERTIES
  {
    Date=08.03.15;
    Time=16:01:05;
    Modified=Yes;
    Version List=IC7.10.0432;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;

  }
  CODE
  {
    VAR
      Text001@1000000019 : TextConst 'ENU=Succesfully delivered;ISL=Sending hepnaÐist';
      Text003@1100408001 : TextConst 'ENU=Error: %1\%2;ISL=St”Ðuvilla: %1\%2';
      Credential@1000000015 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.NetworkCredential";
      HttpWebRequest@1000000014 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.HttpWebRequest";
      HttpWebResponse@1000000013 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebResponse";
      HttpWebException@1000000017 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebException";
      MemoryStream@1000000012 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      XMLRequestDoc@1000000011 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
      XMLResponseDoc@1000000010 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
      XMLProsInstr@1000000009 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlProcessingInstruction";
      XMLElement1@1000000008 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
      XMLElement2@1000000007 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
      XMLElement3@1000000006 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
      XMLNode4@1000000005 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
      XMLNsMgr@1000000004 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNamespaceManager";
      Bytes@1000000003 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      String@1000000002 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";
      Convert@1000000001 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
      ServerFile@1000000000 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File";
      NAVWebRequest@1000000018 : DotNet "'NAVWebRequest, Version=1.0.0.0, Culture=neutral, PublicKeyToken=f53f0925d26e1382'.NAVWebRequest.NAVWebRequest";
      RespCenter@1100408000 : Record 5714;
      CompanyInfo@1100408002 : Record 79;
      Log@1000000016 : Record 50009;
      FileMgt@1100408003 : Codeunit 419;
      WebServiceName@1100408008 : Text[1024];
      InStr@1100408005 : InStream;
      Text006@1100408007 : TextConst 'ENU=Export;ISL=Flytja £t';
      Text009@1100408006 : TextConst 'ENU=All Files (*.*)|*.*;ISL=Allar skr r (*.*)|*.*';

    PROCEDURE SendToPartner@1100408000(ICOutboxTrans@1100408000 : Record 414;ICPartner@1100408001 : Record 413;FileName@1100408002 : Text[250]);
    BEGIN
      ICPartner.TESTFIELD("Inbox Details");
      WebServiceName := FindWebServiceName(ICPartner."Inbox Details");

      WITH ICOutboxTrans DO BEGIN
        CALCFIELDS("PDF Document","XML Document","Details Document");

        IF "Responsibility Center" <> '' THEN BEGIN
          RespCenter.GET("Responsibility Center");
          RespCenter.TESTFIELD("IC Partner Code");
          CompanyInfo."IC Partner Code" := RespCenter."IC Partner Code";
        END ELSE BEGIN
          CompanyInfo.GET;
          CompanyInfo.TESTFIELD("IC Partner Code");
        END;

      END;

      SendTransactionToPartnerDotNet(ICOutboxTrans,ICPartner,FileName)
    END;

    LOCAL PROCEDURE FindWebServiceName@1100408002(URL@1100408000 : Text[1024]) WebServiceName : Text[1024];
    VAR
      i@1100408001 : Integer;
    BEGIN
      FOR i := 1 TO STRLEN(URL) DO
        IF COPYSTR(URL,i,1) = '/' THEN
          WebServiceName := COPYSTR(URL,i + 1);
    END;

    LOCAL PROCEDURE SendTransactionToPartnerDotNet@1100408003(ICOutboxTrans@1100408000 : Record 414;ICPartner@1100408001 : Record 413;FileName@1100408002 : Text[250]);
    VAR
      TempFile@1000000001 : File;
      TempFileName@1000000000 : Text[250];
      WebServiceUserID@1000000003 : Text[1024];
      OutStr@1000000002 : OutStream;
    BEGIN
      WITH ICOutboxTrans DO BEGIN

        Log.GET("Transaction No.");
        Log."Delivered Date and Time" := CURRENTDATETIME;
        Log."Delivered by User ID" := USERID;

        XMLRequestDoc := XMLResponseDoc.XmlDocument;
        XMLProsInstr := XMLRequestDoc.CreateProcessingInstruction('xml','version="1.0" encoding="utf-8"');
        XMLRequestDoc.AppendChild(XMLProsInstr);

        XMLElement1 := XMLRequestDoc.CreateElement('soap','Envelope','http://schemas.xmlsoap.org/soap/envelope/');
        XMLElement1.SetAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
        XMLElement1.SetAttribute('xmlns:xsd','http://www.w3.org/2001/XMLSchema');

        XMLElement2 := XMLRequestDoc.CreateElement('soap','Body', 'http://schemas.xmlsoap.org/soap/envelope/');
        XMLElement3 := XMLRequestDoc.CreateElement('LoadTransaction');
        XMLElement3.SetAttribute('xmlns',STRSUBSTNO('urn:microsoft-dynamics-schemas/codeunit/%1',WebServiceName));

        XMLNode4 := XMLRequestDoc.CreateElement('fromPartnerCode');
        XMLNode4.InnerText := CompanyInfo."IC Partner Code";
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('fromRespCenterCode');
        IF ICPartner."Send Resp. Center Code" THEN
          XMLNode4.InnerText := "Responsibility Center";
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('toPartnerCode');
        XMLNode4.InnerText := "IC Partner Code";
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('toRespCenterCode');
        XMLNode4.InnerText := "IC Partner Resp. Center";

        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('transaction');
        XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(FileName));
        XMLElement3.AppendChild(XMLNode4);
        TempFile.OPEN(FileName);
        TempFile.CREATEINSTREAM(InStr);
        Log.Transaction.CREATEOUTSTREAM(OutStr);
        COPYSTREAM(OutStr,InStr);
        TempFile.CLOSE;
        ServerFile.Delete(FileName);

        XMLNode4 := XMLRequestDoc.CreateElement('pDFInvoice');
        IF "PDF Document".HASVALUE THEN BEGIN
          "PDF Document".CREATEINSTREAM(InStr);
          TempFileName := FileMgt.ServerTempFileName('pdf');
          TempFile.CREATE(TempFileName);
          TempFile.CREATEOUTSTREAM(OutStr);
          COPYSTREAM(OutStr,InStr);
          TempFile.CLOSE;
          XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(TempFileName));
          ServerFile.Delete(TempFileName);
        END;
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('pDFDetails');
        IF "Details Document".HASVALUE THEN BEGIN
          "Details Document".CREATEINSTREAM(InStr);
          TempFileName := FileMgt.ServerTempFileName('pdf');
          TempFile.CREATE(TempFileName);
          TempFile.CREATEOUTSTREAM(OutStr);
          COPYSTREAM(OutStr,InStr);
          TempFile.CLOSE;
          XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(TempFileName));
          ServerFile.Delete(TempFileName);
        END;
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('xMLInvoice');
        IF "XML Document".HASVALUE THEN BEGIN
          "XML Document".CREATEINSTREAM(InStr);
          TempFileName := FileMgt.ServerTempFileName('xml');
          TempFile.CREATE(TempFileName);
          TempFile.CREATEOUTSTREAM(OutStr);
          COPYSTREAM(OutStr,InStr);
          TempFile.CLOSE;
          XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(TempFileName));
          ServerFile.Delete(TempFileName);
        END;
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('responseMessage');
        XMLElement3.AppendChild(XMLNode4);
        XMLElement2.AppendChild(XMLElement3);
        XMLElement1.AppendChild(XMLElement2);
        XMLRequestDoc.AppendChild(XMLElement1);

        HttpWebRequest := HttpWebRequest.Create(ICPartner."Inbox Details");
        HttpWebRequest.Timeout := 30000;
        WebServiceUserID := ICPartner.GetUserID;
        IF WebServiceUserID = '' THEN
          HttpWebRequest.UseDefaultCredentials(TRUE)
        ELSE BEGIN
          HttpWebRequest.UseDefaultCredentials(FALSE);
          Credential := Credential.NetworkCredential;
          Credential.UserName := WebServiceUserID;
          Credential.Password := ICPartner.GetPassword;
          Credential.Domain := ICPartner.GetDomain;
          HttpWebRequest.Credentials := Credential;
        END;
        HttpWebRequest.Method := 'POST';
        HttpWebRequest.ContentType := 'text/xml; charset=utf-8';
        HttpWebRequest.Accept := 'text/xml';
        HttpWebRequest.Headers.Add('SOAPAction','LoadTransaction');
        MemoryStream := HttpWebRequest.GetRequestStream;
        XMLRequestDoc.Save(MemoryStream);
        MemoryStream.Flush;
        MemoryStream.Close;

        NAVWebRequest := NAVWebRequest.NAVWebRequest;
        IF NOT NAVWebRequest.doRequest(HttpWebRequest,HttpWebException,HttpWebResponse) THEN BEGIN
          Log.Delivered := FALSE;
          Log.SetMessage(HttpWebException.Message);
          Log.MODIFY;
          COMMIT;
          ERROR(Text003,HttpWebException.Status.ToString,HttpWebException.Message);
        END;

        MemoryStream := HttpWebResponse.GetResponseStream;
        XMLResponseDoc := XMLResponseDoc.XmlDocument;
        XMLResponseDoc.Load(MemoryStream);
        MemoryStream.Flush;
        MemoryStream.Close;

        XMLNsMgr := XMLNsMgr.XmlNamespaceManager(XMLResponseDoc.NameTable);
        XMLNsMgr.AddNamespace('urn',STRSUBSTNO('urn:microsoft-dynamics-schemas/codeunit/%1',WebServiceName));
        XMLNode4 := XMLResponseDoc.SelectSingleNode('//urn:return_value',XMLNsMgr);

        IF UPPERCASE(XMLNode4.InnerText) = 'FALSE' THEN BEGIN
          XMLNode4 :=  XMLResponseDoc.SelectSingleNode('//urn:responseMessage',XMLNsMgr);
          Log.Delivered := FALSE;
          Log.SetMessage(XMLNode4.InnerText);
          Log.MODIFY;
          COMMIT;
          ERROR(XMLNode4.InnerText);
        END;

        Log.Delivered := TRUE;
        Log.SetMessage(Text001);
        Log.MODIFY;
        COMMIT;

      END;
    END;

    EVENT XMLResponseDoc@1000000010::NodeInserting@93(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeInserted@94(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeRemoving@95(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeRemoved@96(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeChanging@97(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeChanged@98(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeInserting@93(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeInserted@94(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeRemoving@95(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeRemoved@96(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeChanging@97(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeChanged@98(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    BEGIN
    END.
  }
}


OBJECT Codeunit 50019 IC Addon Load Transaction
{
  OBJECT-PROPERTIES
  {
    Date=02.05.14;
    Time=10:12:10;
    Modified=Yes;
    Version List=IC7.10;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            LoadTransaction;
          END;

  }
  CODE
  {
    VAR
      Text001@1100408000 : TextConst 'ENU=IC Partner Code %1 not found;ISL=Mf. f‚lagak¢ti %1 finnst ekki';
      Text002@1100408001 : TextConst 'ENU=Responsibility Center Code mismatch, %1 <> %2;ISL=µbyrgÐast”Ðvark¢ti stemmir ekki, %1 <> %2';
      Convert@1100408004 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
      DocumentFile@1100408003 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File";
      Bytes@1100408010 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      MemoryStream@1100408008 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      FileMgt@1100408009 : Codeunit 419;
      TempFile@1100408007 : File;
      InStr@1000000011 : InStream;
      OutStr@1000000010 : OutStream;
      TempFileName@1100408006 : Text[1024];
      Text003@1100408011 : TextConst 'ENU=No data received;ISL=Engin g”gn m¢ttekin';
      Text004@1100408012 : TextConst 'ENU=Transaction no. %1 is already imported;ISL=F‘rsla nr. %1 er çegar innflutt';
      Text005@1000000000 : TextConst 'ENU=From Partner Code Error in Transaction, %1 <> %2;ISL=Fr  mf. f‚lagak¢ta villa ¡ f‘rslu, %1 <> %2';
      Text006@1000000001 : TextConst 'ENU=To Partner Code Error in Transaction, %1 <> %2;ISL=Til mf. f‚lagak¢ta villa ¡ f‘rslu, %1 <> %2';
      FromPartnerCode@1000000009 : Code[20];
      FromRespCenterCode@1000000008 : Code[10];
      ToPartnerCode@1000000007 : Code[20];
      ToRespCenterCode@1000000006 : Code[10];
      Transaction@1000000005 : BigText;
      PDFInvoice@1000000004 : BigText;
      PDFDetails@1000000003 : BigText;
      XMLInvoice@1000000002 : BigText;

    LOCAL PROCEDURE LoadTransaction@1100408001();
    VAR
      ICPartner@1100408010 : Record 413;
      TempBlob@1100408024 : TEMPORARY Record 99008535;
      TempICOutboxTrans@1100408020 : TEMPORARY Record 414;
      TempICOutBoxJnlLine@1100408019 : TEMPORARY Record 415;
      TempICIOBoxJnlDim@1100408018 : TEMPORARY Record 423;
      TempICOutBoxSalesHdr@1100408017 : TEMPORARY Record 426;
      TempICOutBoxSalesLine@1100408016 : TEMPORARY Record 427;
      TempICOutBoxPurchHdr@1100408015 : TEMPORARY Record 428;
      TempICOutBoxPurchLine@1100408014 : TEMPORARY Record 429;
      TempICDocDim@1100408013 : TEMPORARY Record 442;
      ICInboxTransaction@1100408022 : Record 418;
      ICInboxTransaction2@1100408032 : Record 418;
      ICInboxJnlLine@1100408030 : Record 419;
      ICInboxSalesHdr@1100408029 : Record 434;
      ICInboxSalesLine@1100408028 : Record 435;
      ICInboxPurchHdr@1100408027 : Record 436;
      ICInboxPurchLine@1100408026 : Record 437;
      ICInboxJnlLineDim@1100408025 : Record 423;
      ICInboxDocDim@1100408023 : Record 442;
      HandledICInboxTransaction@1000000000 : Record 420;
      ICInboxOutboxMgt@1100408021 : Codeunit 427;
      FromICPartnerCode@1100408012 : Code[20];
      ToICPartnerCode@1100408011 : Code[20];
      ICOutboxExportXML@1100408008 : XMLport 12;
      NewTableID@1100408031 : Integer;
    BEGIN
      IF NOT ICPartner.GET(FromPartnerCode) THEN
        ERROR(Text001,FromPartnerCode);

      IF ICPartner."Responsibility Center" <> FromRespCenterCode THEN
        ERROR(Text002,ICPartner."Responsibility Center",FromRespCenterCode);

      IF NOT ICPartner.GET(ToPartnerCode) THEN
        ERROR(Text001,ToPartnerCode);

      IF ICPartner."Responsibility Center" <> ToRespCenterCode THEN
        ERROR(Text002,ICPartner."Responsibility Center",ToRespCenterCode);

      IF Transaction.LENGTH > 0 THEN BEGIN
        Bytes := Convert.FromBase64String(Transaction);
        MemoryStream := MemoryStream.MemoryStream(Bytes);
        TempBlob.Blob.CREATEOUTSTREAM(OutStr);
        MemoryStream.WriteTo(OutStr);
        TempBlob.Blob.CREATEINSTREAM(InStr);

        ICOutboxExportXML.SETSOURCE(InStr);
        ICOutboxExportXML.IMPORT;
        ICOutboxExportXML.GetICOutboxTrans(TempICOutboxTrans);
        ICOutboxExportXML.GetICOutBoxJnlLine(TempICOutBoxJnlLine);
        ICOutboxExportXML.GetICIOBoxJnlDim(TempICIOBoxJnlDim);
        ICOutboxExportXML.GetICOutBoxSalesHdr(TempICOutBoxSalesHdr);
        ICOutboxExportXML.GetICOutBoxSalesLine(TempICOutBoxSalesLine);
        ICOutboxExportXML.GetICOutBoxPurchHdr(TempICOutBoxPurchHdr);
        ICOutboxExportXML.GetICOutBoxPurchLine(TempICOutBoxPurchLine);
        ICOutboxExportXML.GetICSalesDocDim(TempICDocDim);
        ICOutboxExportXML.GetICSalesDocLineDim(TempICDocDim);
        ICOutboxExportXML.GetICPurchDocDim(TempICDocDim);
        ICOutboxExportXML.GetICPurchDocLineDim(TempICDocDim);
        FromICPartnerCode := ICOutboxExportXML.GetFromICPartnerCode;
        ToICPartnerCode := ICOutboxExportXML.GetToICPartnerCode;

        TempICOutBoxSalesHdr.MODIFYALL("Responsibility Center",FromRespCenterCode);
        TempICOutBoxSalesHdr.MODIFYALL("IC Partner Resp. Center",ToRespCenterCode);
        TempICOutBoxPurchHdr.MODIFYALL("Responsibility Center",FromRespCenterCode);
        TempICOutBoxPurchHdr.MODIFYALL("IC Partner Resp. Center",ToRespCenterCode);

        IF FromICPartnerCode <> FromPartnerCode THEN
          ERROR(Text005,FromICPartnerCode,FromPartnerCode);

        IF ToICPartnerCode <> ToPartnerCode THEN
          ERROR(Text006,ToICPartnerCode,ToPartnerCode);

        ICInboxTransaction2.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
        ICInboxTransaction2.SETRANGE("IC Partner Code",FromICPartnerCode);
        ICInboxTransaction2.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
        IF ICInboxTransaction2.FINDFIRST THEN
          ERROR(Text004,TempICOutboxTrans."Transaction No.");

        HandledICInboxTransaction.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
        HandledICInboxTransaction.SETRANGE("IC Partner Code",FromICPartnerCode);
        HandledICInboxTransaction.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
        IF HandledICInboxTransaction.FINDFIRST THEN
          ERROR(Text004,TempICOutboxTrans."Transaction No.");

        IF TempICOutboxTrans.FIND('-') THEN BEGIN
          ICInboxOutboxMgt.OutboxTransToInbox(TempICOutboxTrans,ICInboxTransaction,FromICPartnerCode);

          TempICOutBoxJnlLine.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxJnlLine.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxJnlLine.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxJnlLine.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxJnlLineToInbox(ICInboxTransaction,TempICOutBoxJnlLine,ICInboxJnlLine);
              TempICIOBoxJnlDim.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
              TempICIOBoxJnlDim.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
              TempICIOBoxJnlDim.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
              TempICIOBoxJnlDim.SETRANGE("Line No.",ICInboxJnlLine."Line No.");
              IF TempICIOBoxJnlDim.FIND('-') THEN
                REPEAT
                  ICInboxOutboxMgt.OutboxJnlLineDimToInbox(
                    ICInboxJnlLine,TempICIOBoxJnlDim,ICInboxJnlLineDim,DATABASE::"IC Inbox Jnl. Line");
                UNTIL TempICIOBoxJnlDim.NEXT = 0;
            UNTIL TempICOutBoxJnlLine.NEXT = 0;

          TempICOutBoxSalesHdr.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxSalesHdr.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxSalesHdr.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxSalesHdr.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxSalesHdrToInbox(ICInboxTransaction,TempICOutBoxSalesHdr,ICInboxPurchHdr);
            UNTIL TempICOutBoxSalesHdr.NEXT = 0;

          TempICOutBoxSalesLine.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxSalesLine.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxSalesLine.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxSalesLine.FIND('-') THEN BEGIN
            REPEAT
              ICInboxOutboxMgt.OutboxSalesLineToInbox(ICInboxTransaction,TempICOutBoxSalesLine,ICInboxPurchLine);
            UNTIL TempICOutBoxSalesLine.NEXT = 0;
            ICInboxPurchLine.SETRANGE("IC Transaction No.",ICInboxPurchHdr."IC Transaction No.");
            ICInboxPurchLine.SETRANGE("IC Partner Code",ICInboxPurchHdr."IC Partner Code");
            ICInboxPurchLine.SETRANGE("Transaction Source",ICInboxPurchHdr."Transaction Source");
            ICInboxPurchLine.SETRANGE("VAT Base Amount",-0.5,0.5);
            ICInboxPurchLine.CALCSUMS("Amount Including VAT");
            ICInboxPurchHdr."Payable Rounding Amount" := ICInboxPurchLine."Amount Including VAT";
            ICInboxPurchLine.SETRANGE("VAT Base Amount");
            ICInboxPurchLine.CALCSUMS("Amount Including VAT","VAT Base Amount");
            ICInboxPurchHdr."Payable Amount" := ICInboxPurchLine."Amount Including VAT";
            ICInboxPurchHdr."Line Extension Amount" := ICInboxPurchLine."VAT Base Amount" - ICInboxPurchHdr."Payable Rounding Amount";
            ICInboxPurchHdr."Tax Exclusive Amount" := ICInboxPurchHdr."Line Extension Amount";
            ICInboxPurchHdr."Tax Inclusive Amount" := ICInboxPurchLine."Amount Including VAT" - ICInboxPurchHdr."Payable Rounding Amount";
            ICInboxPurchHdr."Tax Amount" :=  ICInboxPurchHdr."Tax Inclusive Amount" - ICInboxPurchHdr."Tax Exclusive Amount";
            ICInboxPurchHdr.MODIFY;
          END;

          TempICOutBoxPurchHdr.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxPurchHdr.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxPurchHdr.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxPurchHdr.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxPurchHdrToInbox(ICInboxTransaction,TempICOutBoxPurchHdr,ICInboxSalesHdr);
            UNTIL TempICOutBoxPurchHdr.NEXT = 0;

          TempICOutBoxPurchLine.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxPurchLine.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxPurchLine.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxPurchLine.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxPurchLineToInbox(ICInboxTransaction,TempICOutBoxPurchLine,ICInboxSalesLine);
            UNTIL TempICOutBoxPurchLine.NEXT = 0;

          TempICDocDim.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICDocDim.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICDocDim.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICDocDim.FIND('-') THEN
            REPEAT
              CASE TempICDocDim."Table ID" OF
                DATABASE::"IC Outbox Sales Header": NewTableID := DATABASE::"IC Inbox Purchase Header";
                DATABASE::"IC Outbox Sales Line": NewTableID := DATABASE::"IC Inbox Purchase Line";
                DATABASE::"IC Outbox Purchase Header": NewTableID := DATABASE::"IC Inbox Sales Header";
                DATABASE::"IC Outbox Purchase Line": NewTableID := DATABASE::"IC Inbox Sales Line";
              END;
              ICInboxOutboxMgt.OutboxDocDimToInbox(
                TempICDocDim,ICInboxDocDim,NewTableID,FromICPartnerCode,ICInboxTransaction."Transaction Source");
            UNTIL TempICDocDim.NEXT = 0;
        END;

        ICInboxTransaction."Responsibility Center" := ToRespCenterCode;
        ICInboxTransaction."IC Partner Resp. Center" := FromRespCenterCode;

        IF XMLInvoice.LENGTH > 0 THEN BEGIN
          Bytes := Convert.FromBase64String(XMLInvoice);
          MemoryStream := MemoryStream.MemoryStream(Bytes);
          ICInboxTransaction."XML Document".CREATEOUTSTREAM(OutStr);
          MemoryStream.WriteTo(OutStr);
        END;

        IF PDFInvoice.LENGTH > 0 THEN BEGIN
          Bytes := Convert.FromBase64String(PDFInvoice);
          MemoryStream := MemoryStream.MemoryStream(Bytes);
          ICInboxTransaction."PDF Document".CREATEOUTSTREAM(OutStr);
          MemoryStream.WriteTo(OutStr);
        END;

        IF PDFDetails.LENGTH > 0 THEN BEGIN
          Bytes := Convert.FromBase64String(PDFDetails);
          MemoryStream := MemoryStream.MemoryStream(Bytes);
          ICInboxTransaction."Details Document".CREATEOUTSTREAM(OutStr);
          MemoryStream.WriteTo(OutStr);
        END;

        ICInboxTransaction.MODIFY;
      END ELSE
        ERROR(Text003);
    END;

    PROCEDURE SetProperties@1000000000(VAR SetFromPartnerCode@1000000007 : Code[20];VAR SetFromRespCenterCode@1000000006 : Code[10];VAR SetToPartnerCode@1000000005 : Code[20];VAR SetToRespCenterCode@1000000004 : Code[10];VAR SetTransaction@1000000003 : InStream;VAR SetPDFInvoice@1000000002 : InStream;VAR SetPDFDetails@1000000001 : InStream;VAR SetXMLInvoice@1000000000 : InStream;TransactionHasValue@1000000011 : Boolean;PDFInvoiceHasValue@1000000010 : Boolean;PDFDetailsHasValue@1000000009 : Boolean;XMLInvoiceHasValue@1000000008 : Boolean);
    BEGIN
      FromPartnerCode := SetFromPartnerCode;
      FromRespCenterCode := SetFromRespCenterCode;
      ToPartnerCode := SetToPartnerCode;
      ToRespCenterCode := SetToRespCenterCode;
      IF TransactionHasValue THEN
        Transaction.READ(SetTransaction);
      IF PDFInvoiceHasValue THEN
        PDFInvoice.READ(SetPDFInvoice);
      IF PDFDetailsHasValue THEN
        PDFDetails.READ(SetPDFDetails);
      IF XMLInvoiceHasValue THEN
        XMLInvoice.READ(SetXMLInvoice);
    END;

    BEGIN
    END.
  }
}

 

Posted on

Prepare for Report Transformation

On of the bigger tasks when upgrading to the Role Tailored Client is the report transformation.  In big database there are houndreds of reports.  Some of them might just be idle reports that are never used.  This blog from ArcherPoint that is based on a original post from Mark Brummel got me thinking.  In 2009 Microsoft added to the client the possibility to execute a function with ID 120 in codeunit 1 that only works for the Classic Client.

I mixed these together by adding the code to codeunit 1 in the same way as Mark Brummel but creating a report log table instead of a report print count table.

Here the Entry No. field automatically increments the numbers in the database.  I also changed the property DataPerCompany for the table to No.

The single instance codeunit is also a simple one.

The next step might just be to create a report for Excel Pivot table like I did with ledger tables.

Report Printing Log

Posted on

Send multiple base 64 encoded files to a single Web Service

Sometimes a little more flexibility is needed than a single XML Port in a web service function.  Then it is possible to send multiple files to a single web service.  I am working on an enhancement for the inter-company posting feature in Dynamics NAV.

To add to the standard functionality I want to be able to use web services to deliver an invoice from one company to another.  I send two XML files and two PDF files to web service.

In the Classic Client I use then ‘CG Request Client’.Base64 to encode the files into a XML node.

[code]
XMLElement3.appendChild(XMLNode);
XMLNode := XMLRequestDoc.createElement(‘pDFInvoice’);
IF "PDF Document".HASVALUE THEN BEGIN
PDFInvoiceFileName := FileMgt.ClientTempFileName(”,’pdf’);
ICOutboxTrans."PDF Document".EXPORT(PDFInvoiceFileName,FALSE);
Base64.Encode(PDFInvoiceFileName,XMLNode);
ERASE(PDFInvoiceFileName);
END;
XMLElement3.appendChild(XMLNode);[/code]

in the Role Tailored Client I use dotnet interop

[code]
XMLNode4 := XMLRequestDoc.CreateElement(‘pDFInvoice’);
IF "PDF Document".HASVALUE THEN BEGIN
"PDF Document".CREATEINSTREAM(InStr);
DOWNLOADFROMSTREAM(InStr,Text006,Path,Text009,PDFInvoiceFileName);
XMLNode4.InnerText := Convert.ToBase64String(ClientFile.ReadAllBytes(PDFInvoiceFileName));
ClientFile.Delete(PDFInvoiceFileName);
END;
XMLElement3.AppendChild(XMLNode4);[/code]

On the service part it is possible to write the file to a BLOB without using a temporary file.

[code]
IF PDFInvoice.LENGTH > 0 THEN BEGIN
Bytes := Convert.FromBase64String(PDFInvoice);
MemoryStream := MemoryStream.MemoryStream(Bytes);
ICInboxTransaction."PDF Document".CREATEOUTSTREAM(OutStr);
MemoryStream.WriteTo(OutStr);
END;[/code]

Posted on

A message from Dan Brown, General Manager Dynamics NAV

This is a message to you all from Dan Brown General Manager, Dynamics NAV:

Hi, everyone.

Over the past several months the NAV team has worked hard getting ready to release Microsoft Dynamics NAV 2013. We’ve run hundreds of thousands of performance-, stress-, unit- and regression-tests daily. We’ve monitored the comments you’ve made on our first-ever public Beta of NAV and incorporated the feedback as much as possible. And, we’ve worked with partners bringing several customers live on NAV 2013. All of this has been to ensure that the product is of the highest possible quality before we ship. We’re looking forward to getting the RTM version in your hands as soon as possible!

Microsoft Dynamics NAV 2013 is probably the biggest launch of the product ever. It concludes the transition from the classic client/server 2-tier proprietary architecture developed in the 1990’s to a state-of-the-art, 4-tier Microsoft architecture capable of rendering multiple clients and facilitating multiple modes of integration. It also means that Microsoft Dynamics NAV now is a full-blown member of the Microsoft server family and adheres to all Microsoft standards in terms of security, reliability and scalability. Finally, it signals the beginning of a new era of “NAV in the cloud,” opening up an array of new opportunities using NAV and integrating it with Microsoft and non-Microsoft products.

With Microsoft Dynamics NAV 2013 coming out this fall, the NAV TechDays conference is a great opportunity for everybody in the NAV developer community to learn more about and get ready for the release. All the sessions at NAV TechDays are technical and long enough to allow the speakers to go into enough detail for the developers in the audience to understand what the features in the product are about and how to use them. Since NAV TechDays is a conference for developers by developers with deep technical content, we are sending some of our best developers who designed and wrote the code to attend and speak at the conference. If you have questions about your favorite feature, you will have an opportunity to give your feedback on Microsoft Dynamics NAV 2013 and provide input on what you would like to see in future releases.

I hope to see you all at NAV TechDays in Antwerp, Belgium on September 27th.

-Dan

____________________________________
Daniel C. Brown
General Manager, Dynamics NAV

Posted on

Accessing your non english data from the MS SQL server

If you are like me, located in a non-English speaking country and would like users to be able to use other tools then the Dynamics NAV clients to access the company data you will find that all the meta data in the database is in English.  This means that you will have to translate the fields and sometimes the data to your language.

The problems are:

  • Field captions are unavailable in the MS SQL database
  • Option values are shown as number
  • Boolean is shown as number
  • Global dimension do not have the correct caption
  • Time is shown as DateTime
  • Not easy to see the difference between normal date and closing date

The solution that I am using is to create a separate database on the same database server and create localized views in that database.  What you get with the solution is:

  • A date table that can be used to show all properties of a given date
  • A import of the option value captions
  • A selection of tables to make accessible
  • A batch job to create the localized SQL view for each table

Here is a list of the fields in the date table

Field No. Field Name Data Type Length
1 Date Date
2 Date Name Text 30
3 Year Integer
4 Week Integer
5 Month Integer
6 Month Name Text 20
7 Day of Week Integer
8 Day Name Text 20
9 Day of Month Integer
10 Closing Date Boolean
11 SQL Month Name Text 20
12 SQL Day Name Text 20
13 Quarter Integer
14 Year Month Integer
15 Year Month Name Text 20
16 Month Year  Name Text 20
17 Quarter Name Text 20
18 VAT Period Integer
19 VAT Period Name Text 20
20 Sorting Date Integer
21 HRMS Integer Start Integer
22 HRMS Integer End Integer
23 Day of Year Integer
24 Day of Half Year Integer
25 Day of Quarter Integer
26 Day of Accounting Integer
27 Half Years Integer
28 Half Year of Year Integer
29 Is Holiday Boolean
30 Is Working Day Boolean
31 Month of Half Year Integer
32 Month of Quarter Integer
33 Month of Year Integer
34 Quarters of Half Year Integer
35 Quarters of Year Integer
36 Week of Year Integer
37 Is Week Day Boolean
41 Half Year Name Text 20
42 Week Name Text 20
102 Fiscal Day Date
103 Fiscal Year Integer
104 Fiscal Week Integer
105 Fiscal Month Integer
107 Fiscal Day of Week Integer
109 Fiscal Day of Month Integer
113 Fiscal Quarter Integer
123 Fiscal Day of Year Integer
124 Fiscal Day of Half Year Integer
125 Fiscal Day of Quarter Integer
127 Fiscal Half Years Integer
128 Fiscal Half Year of Year Integer
131 Fiscal Month of Half Year Integer
132 Fiscal Month of Quarter Integer
133 Fiscal Month of Year Integer
134 Fiscal Quarters of Half Year Integer
135 Fiscal Quarters of Year Integer
136 Fiscal Week of Half Year Integer
137 Fiscal Week of Month Integer
138 Fiscal Week of Quarter Integer
139 Fiscal Week of Year Integer
140 Fiscal Quarter Name Text 20
141 Fiscal Half Year Name Text 20
142 Fiscal Week Name Text 20
143 Fiscal Month Name Text 20

Lets take the G/L Entry table as an example. In the MS SQL the field names are in English and the data not readable for the normal user.

SELECT [Entry No_],[G_L Account No_],[Posting Date],[Document Type]
      ,[Document No_],[Description],[Bal_ Account No_],[Amount]
      ,[Global Dimension 1 Code],[Global Dimension 2 Code],[User ID] 
... 
      ,[FA Entry Type],[FA Entry No_] 
FROM [Dynamics NAV Demo Database (IS 2009 R2)].[dbo].[CRONUS Ísland hf_$G_L Entry]

and for example the [Document Type] will show as numbers.  Here is an example of the output of my tool to create a localized view for this table.

IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.ISL$CRONUS Ísland hf_$Fjárhagsfærsla'))
DROP VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
GO
CREATE VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
AS
SELECT
[Entry No_] As [Færslunr_]
,[G_L Account No_] As [Fjárhagsreikn_nr_]
,[Posting Date] As [Bókunardags_]
,CASE [Document Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Greiðsla'
WHEN 2 THEN 'Reikningur'
WHEN 3 THEN 'Kreditreikningur'
WHEN 4 THEN 'Vaxtareikningur'
WHEN 5 THEN 'Innheimtubréf'
WHEN 6 THEN 'Endurgreiðsla'
END As [Tegund fylgiskjals]
,[Document No_] As [Númer fylgiskjals]
,[Description] As [Lýsing]
,[Bal_ Account No_] As [Mótreikningur nr_]
,[Amount] As [Upphæð]
,[Global Dimension 1 Code] As [Deild Kóti]
,[Global Dimension 2 Code] As [Verkefni Kóti]
,[User ID] As [Kenni notanda]
,CASE [System-Created Entry]
WHEN 1 THEN 'Já'
WHEN 0 THEN 'Nei'
END As [Kerfisfærsla]
...
,CASE [FA Entry Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Eignir'
WHEN 2 THEN 'Viðhald'
END As [Eignafærslutegund]
,[FA Entry No_] As [Eignafærslunr_]
FROM [CRONUS Ísland hf_$G_L Entry]
GO

Executing this will give me a view in my database that I can use to fetch localized data from the G/L Entry table with all the above problems solved.

By using the additional date table as dimension in OLAP or as join in a SQL query I can easily find all aspects of the “Posting Date” in the G/L Entry table and group entries accordingly.