Tag: Links

Posted on

Gathering permission for users and groups

I have a solution that stores scanned and linked documents in an external database.  A URL to the document is stored in the Record Link table in NAV and attached to the relevant record.  The URL points to a web page that delivers the attached document given that the user asking for the document has permission to read the table the document belongs to.

In my latest installation I faced a new challenge; we are now using Active Directory Groups for access control.  Still I need to store the permissions in my external database for each user.  Of course it would be possible to change the permission handling in the external database and modify the ASPX web page that delivers the document, but I am a NAV programmer and if the solution is possible in NAV it will be written in NAV.

The task was to find all users that belong to a group.  The method I used is through dotnet and through the service tier.  If the service tier is not running on a domain account the dotnet execution needs to be on the client side.

The first step is to create the connection string to Active Directory.  I use “System.Net.NetworkInformation.IPGlobalProperties” dotnet object to the the current dns domain name and change the domain name from example.com to the connection string “DC=example,DC=com”.

Next step is to load all the group information from Active Directory into a temporary table.  I use table 367 as ADBuffer and load all combinations of group and user into that table.

For every group used in the access control I filter the buffer table and read all the users within that group.

This is a part of the codeunit I use in my solution and should give you a hint on how to do this in your own solution.

[code] PROCEDURE CreatePermissionBuf@1200050012(VAR PermissionBuf@1200050001 : Record 2000000005;VAR TempObject@1100408001 : Record 2000000001) : Boolean;
VAR
PermissonSet@1100408000 : Record 2000000004;
User@1005 : Record 2000000120;
AccessControl@1006 : Record 2000000053;
Permission@1008 : Record 2000000005;
TempUser@50000 : TEMPORARY Record 91;
HasPermission@1003 : Boolean;
BEGIN
PermissionBuf.DELETEALL;

WITH User DO
IF FINDSET THEN REPEAT
IF CreateTempUserIDs(User,TempUser) THEN BEGIN
PermissonSet.SETFILTER("Role ID",’SUPER’ + ‘*’);
PermissonSet.FINDSET;
REPEAT
HasPermission := AccessControl.GET("User Security ID",PermissonSet."Role ID",COMPANYNAME);
IF NOT HasPermission THEN
HasPermission := AccessControl.GET("User Security ID",PermissonSet."Role ID",”);
UNTIL (PermissonSet.NEXT = 0) OR HasPermission;

IF HasPermission THEN BEGIN
IF TempUser.FIND(‘-‘) THEN
REPEAT
TempObject.FIND(‘-‘);
REPEAT
PermissionBuf."Role ID" := TempUser."User ID";
PermissionBuf."Object Type" := PermissionBuf."Object Type"::"Table Data";
PermissionBuf."Object ID" := TempObject.ID;
IF PermissionBuf.INSERT THEN;
UNTIL TempObject.NEXT = 0;
UNTIL TempUser.NEXT = 0;
END ELSE BEGIN
TempObject.FIND(‘-‘);
REPEAT
Permission.SETRANGE("Object Type",Permission."Object Type"::"Table Data");
Permission.SETRANGE("Object ID",TempObject.ID);
Permission.SETRANGE("Read Permission",Permission."Read Permission"::Yes);
IF Permission.FINDSET THEN
REPEAT
HasPermission := AccessControl.GET("User Security ID",Permission."Role ID",COMPANYNAME);
IF NOT HasPermission THEN
HasPermission := AccessControl.GET("User Security ID",Permission."Role ID",”);
UNTIL HasPermission OR (Permission.NEXT = 0);
IF HasPermission THEN BEGIN
IF TempUser.FIND(‘-‘) THEN
REPEAT
PermissionBuf."Role ID" := TempUser."User ID";
PermissionBuf."Object Type" := PermissionBuf."Object Type"::"Table Data";
PermissionBuf."Object ID" := TempObject.ID;
IF PermissionBuf.INSERT THEN;
UNTIL TempUser.NEXT = 0;
END;
UNTIL TempObject.NEXT = 0;
END;
END;
UNTIL NEXT = 0;

TempObject.FIND(‘-‘);
REPEAT
PermissionBuf."Role ID" := SigningSetup."Database Login Name";
PermissionBuf."Object Type" := PermissionBuf."Object Type"::"Table Data";
PermissionBuf."Object ID" := TempObject.ID;
IF PermissionBuf.INSERT THEN;
UNTIL TempObject.NEXT = 0;
END;

LOCAL PROCEDURE CreateTempUserIDs@50000(FromUser@50001 : Record 2000000120;VAR TempUser@50000 : Record 91) : Boolean;
VAR
DomainName@50003 : Text;
GroupName@50004 : Text;
BEGIN
TempUser.DELETEALL;

WITH FromUser DO BEGIN
IF "Windows Security ID" = ” THEN EXIT(FALSE);
CASE "License Type" OF
"License Type"::"Windows Group":
BEGIN
DomainName := SigningTools.GetDomainName("User Name");
GroupName := SigningTools.RemoveDomainName("User Name");
GetListOfAdUsersByGroup(DomainName,GroupName,TempUser);
END;
ELSE BEGIN
TempUser.INIT;
TempUser."User ID" := SigningTools.RemoveDomainName(UPPERCASE("User Name"));
TempUser.INSERT;
END;
END;
EXIT(TRUE);
END;
END;

LOCAL PROCEDURE GetListOfAdUsersByGroup@50033(DomainName@50001 : Text;GroupName@50002 : Text;VAR TempUser@50000 : Record 91);
VAR
IPProperties@50003 : DotNet "’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.NetworkInformation.IPGlobalProperties";
TextMgt@50005 : Codeunit 10000204;
ConnectionString@50014 : Text;
IPDomainName@50013 : Text;
BEGIN
ADBuffer.RESET;
IF ADBuffer.ISEMPTY THEN BEGIN
IPDomainName := IPProperties.GetIPGlobalProperties.DomainName;
WHILE STRPOS(IPDomainName,’.’) > 0 DO BEGIN
IF ConnectionString = ” THEN
ConnectionString := ‘DC=’ + TextMgt.ExtractFirstPartFromString(IPDomainName,’.’,”)
ELSE
ConnectionString += ‘,DC=’ + TextMgt.ExtractFirstPartFromString(IPDomainName,’.’,”);
END;

IF ConnectionString = ” THEN
ConnectionString := ‘DC=’ + IPDomainName
ELSE
ConnectionString += ‘,DC=’ + IPDomainName;
LoadADBuffer(ConnectionString);
END;

ADBuffer.SETRANGE(Totaling,GroupName);
IF ADBuffer.FIND(‘-‘) THEN REPEAT
TempUser.INIT;
TempUser."User ID" := ADBuffer.Name;
TempUser.INSERT;
UNTIL ADBuffer.NEXT = 0;
END;

LOCAL PROCEDURE LoadADBuffer@50003(ConnectionString@50015 : Text);
VAR
DirectoryEntry@50004 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.DirectoryEntry";
DirectorySearcher@50003 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.DirectorySearcher";
SearchResultCollection@50006 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.SearchResultCollection";
SearchResult@50009 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.SearchResult";
ResultPropertyCollection@50010 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.ResultPropertyCollection";
ResultPropertyValueCollection@50007 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.ResultPropertyValueCollection";
Query@50005 : Text;
KeyValue@50000 : Code[10];
SearchLoop@50008 : Integer;
PropertyLoop@50001 : Integer;
BEGIN
KeyValue := ‘00000001’;
DirectoryEntry := DirectoryEntry.DirectoryEntry(‘LDAP://’ + ConnectionString);
DirectorySearcher := DirectorySearcher.DirectorySearcher(DirectoryEntry);
Query := ‘(&(objectCategory=person)(objectClass=user)(memberOf=*))’;
DirectorySearcher.Filter := Query;
DirectorySearcher.PropertiesToLoad.Add(‘sAMAccountName’);
DirectorySearcher.PropertiesToLoad.Add(‘memberOf’);
SearchResultCollection := DirectorySearcher.FindAll;
FOR SearchLoop := 1 TO SearchResultCollection.Count DO BEGIN
SearchResult := SearchResultCollection.Item(SearchLoop – 1);
ResultPropertyCollection := SearchResult.Properties;
ADBuffer.INIT;
ADBuffer.Name := UPPERCASE(ResultPropertyCollection.Item(‘sAMAccountName’).Item(0));
FOR PropertyLoop := 1 TO ResultPropertyCollection.Item(‘memberOf’).Count DO BEGIN
ADBuffer.Code := KeyValue;
ADBuffer.Totaling := UPPERCASE(COPYSTR(SELECTSTR(1,ResultPropertyCollection.Item(‘memberOf’).Item(PropertyLoop – 1)),4));
ADBuffer.INSERT;
KeyValue := INCSTR(KeyValue);
END;
END;
END;
[/code]

Posted on

Access your SQL server through dotnet interop

As the Icelandic localization for NAV 2013 is supposed to land here in April it is now time to start moving the solutions to the new platform.  One of the things that I have used are the automation variables for SQL server connection.  In NAV 2013 I plan to replace all automation variables with dotnet variables.  Here is a code that could help you replacing some of your automation variables.[code]OBJECT Codeunit 50000 SQL Mgt.
{
OBJECT-PROPERTIES
{
Date=07.03.13;
Time=14:58:29;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
BEGIN
END;

}
CODE
{
VAR
ClientFile@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ServerFile@1100408008 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
SQLConnection@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlConnection";
SigningSetup@1200050000 : Record 10017128;
HostSetup@1200050027 : Record 10001061;
FileMgt@1200050026 : Codeunit 419;
SigningTools@1200050006 : Codeunit 10017129;
NoSeriesMgt@1200050011 : Codeunit 396;
HasGotSigningSetup@1200050001 : Boolean;
Text001@1200050004 : TextConst ‘ENU=Table %1 does not exist;ISL=Tafla %1 er ekki fyrir hendi’;

PROCEDURE GetSigningSetup@1200050001();
BEGIN
IF NOT HasGotSigningSetup THEN BEGIN
SigningSetup.GET;
IF SigningSetup."Default Storage" = SigningSetup."Default Storage"::"Web Site" THEN BEGIN
SigningSetup.TESTFIELD("Database Server Name");
SigningSetup.TESTFIELD("Database Name");
SigningSetup.TESTFIELD("Database Table Name");
SigningSetup.TESTFIELD("Database Permission Table Name");
SigningSetup.TESTFIELD("Database Login Name");
SigningSetup.TESTFIELD("Database User Password");
SigningSetup.TESTFIELD("Link Reference Nos.");
END;
SigningTools.TryFindHostSetup(HostSetup);
HasGotSigningSetup := TRUE;
END;
END;

PROCEDURE OpenLinkDB@1200050000();
VAR
ConnectionString@1200050000 : Text[1024];
BEGIN
GetSigningSetup;

IF ISNULL(SQLConnection) THEN BEGIN
ConnectionString :=
STRSUBSTNO(‘Data Source=%1;Initial Catalog=%2;User Id=%3;Password=%4′,
SigningSetup."Database Server Name",
SigningSetup."Database Name",
SigningSetup."Database Login Name",
SigningSetup."Database User Password");

SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open;
END;
END;

PROCEDURE CloseLinkDB@1200050004();
BEGIN
IF ISNULL(SQLConnection) THEN
EXIT;

IF SQLConnection.State = 1 THEN
SQLConnection.Close;
END;

PROCEDURE TableExists@1200050006(TableName@1200050000 : Text[50];ShowError@1200050001 : Boolean) : Boolean;
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLReader@1000000001 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Data.SqlClient.SqlDataReader" WITHEVENTS;
Query@1200050002 : Text[1024];
BEGIN
OpenLinkDB;

Query :=
STRSUBSTNO(‘SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ”%1” AND TABLE_TYPE = ”BASE TABLE”’,
TableName);

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLReader := SQLCommand.ExecuteReader;
IF SQLReader.HasRows THEN
EXIT(TRUE)
ELSE
IF ShowError THEN
ERROR(Text001,TableName)
ELSE
EXIT(FALSE);
END;

PROCEDURE CreateLinkTable@1200050020();
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
Guid@1200050000 : Text[40];
Query@1000000001 : Text[1024];
BEGIN
OpenLinkDB;

Guid := CREATEGUID;

Query :=
STRSUBSTNO(‘CREATE TABLE [%1](‘,SigningSetup."Database Table Name") +
‘[Link ID] [varchar](20) NOT NULL,’ +
‘[Table ID] [int] NOT NULL,’ +
‘[Period] [varchar](6) NOT NULL,’ +
‘[User ID] [varchar](20) NOT NULL,’ +
‘[Computer Name] [varchar](50) NOT NULL,’ +
‘[File Name] [varchar](1024) NOT NULL,’ +
‘[Content Type] [varchar](50) NOT NULL,’ +
‘[Object] [image] NULL,’ +
STRSUBSTNO(‘ CONSTRAINT [PK_%1_ID] PRIMARY KEY CLUSTERED’,Guid) +
‘([Link ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,’ +
‘IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]’ +
‘) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];’;

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinately for the command to finish.
SQLCommand.ExecuteNonQuery;
END;

LOCAL PROCEDURE UploadFileToSQL@1200050011(FileName@1200050000 : Text[1024];TableID@1200050007 : Integer) URL : Text[1024];
VAR
SQLCommand@1000000005 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLdbType@1000000003 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlDbType";
TempFileName@1100408001 : Text[1024];
Query@1000000006 : Text[1024];
ComputerName@1200050002 : Code[50];
LinkID@1200050006 : Code[20];
InsertCount@1000000007 : Integer;
BEGIN
TempFileName := FileMgt.UploadFileSilent(FileName);
OpenLinkDB;

SigningSetup.TESTFIELD("Link Web URL");
ComputerName := SigningTools.GetComputerName;

LinkID := NoSeriesMgt.GetNextNo(SigningSetup."Link Reference Nos.",TODAY,TRUE);
URL := SigningSetup."Link Web URL" + STRSUBSTNO(‘&ID=%1’,LinkID);

Query := STRSUBSTNO(
‘INSERT INTO [%1] ‘ +
‘ ([Link ID] ‘ +
‘ ,[Table ID] ‘ +
‘ ,[Period] ‘ +
‘ ,[User ID] ‘ +
‘ ,[Computer Name] ‘ +
‘ ,[File Name] ‘ +
‘ ,[Content Type] ‘ +
‘ ,[Object]) ‘ +
‘ VALUES ‘ +
‘ (@LinkID ‘ + // varchar(20)
‘ ,@TableID ‘ + // int
‘ ,@Period ‘ + // varchar(6)
‘ ,@UserID ‘ + // varchar(20)
‘ ,@ComputerName ‘ + // varchar(50)
‘ ,@FileName ‘ + // varchar(1024)
‘ ,@ContentType ‘ + // varchar(50)
‘ ,@Object) ‘ + // image
”,SigningSetup."Database Table Name");

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLCommand.Parameters.Add(‘@LinkID’,SQLdbType.VarChar,20).Value := LinkID;
SQLCommand.Parameters.Add(‘@TableID’,SQLdbType.Int).Value := TableID;
SQLCommand.Parameters.Add(‘@Period’,SQLdbType.VarChar,6).Value := FORMAT(TODAY,0,'<Year4><Month,2>’);
SQLCommand.Parameters.Add(‘@UserID’,SQLdbType.VarChar,20).Value := USERID;
SQLCommand.Parameters.Add(‘@ComputerName’,SQLdbType.VarChar,50).Value := ComputerName;
SQLCommand.Parameters.Add(‘@FileName’,SQLdbType.VarChar,1024).Value := FileMgt.GetFileName(FileName);
SQLCommand.Parameters.Add(‘@ContentType’,SQLdbType.VarChar,50).Value := SigningTools.ContentType(FileName);
SQLCommand.Parameters.Add(‘@Object’,SQLdbType.Image).Value := ServerFile.ReadAllBytes(TempFileName);
InsertCount := SQLCommand.ExecuteNonQuery;
ServerFile.Delete(TempFileName);
END;

LOCAL PROCEDURE DownloadFileFromWeb@1100408027(URL@1200050000 : Text[1024]) FileName : Text[1024];
VAR
HttpWebRequest@1100408002 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.HttpWebRequest";
HttpWebResponse@1100408001 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebResponse";
HttpWebHeaderCollection@1100408003 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebHeaderCollection";
Credential@1100408008 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.NetworkCredential";
MemoryStream@1100408004 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.MemoryStream";
ServerFileStream@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.FileStream";
TempBlob@1100408009 : TEMPORARY Record 99008535;
Document@1100408011 : BigText;
OutStr@1100408007 : OutStream;
InStr@1100408010 : InStream;
TempFile@1100408006 : File;
FilePath@1200050001 : Text[1024];
TempPath@1100408000 : Text[1024];
BEGIN
GetSigningSetup;
Credential := Credential.NetworkCredential;
Credential.UserName := SigningSetup."Database Login Name";
Credential.Password := SigningSetup."Database User Password";
Credential.Domain := ‘.’;
HttpWebRequest := HttpWebRequest.Create(URL);
HttpWebRequest.Timeout := 30000;
HttpWebRequest.Credentials := Credential;
HttpWebRequest.Method := ‘GET’;
HttpWebResponse := HttpWebRequest.GetResponse;
HttpWebHeaderCollection := HttpWebResponse.Headers;

FileName := HttpWebHeaderCollection.Get(‘Content-Disposition’);
IF STRPOS(FileName,’filename=’) = 0 THEN
FileName := ”
ELSE BEGIN
FileName := COPYSTR(FileName,STRPOS(FileName,’filename=’) + 9);
TempPath :=
FileMgt.GetDirectoryName(FileMgt.ServerTempFileName(”)) +
FileName;
TempFile.CREATE(TempPath);
TempFile.CREATEOUTSTREAM(OutStr);
COPYSTREAM(OutStr,HttpWebResponse.GetResponseStream);
TempFile.CLOSE;
FileName := FileMgt.DownloadTempFile(TempPath);
END;
END;

PROCEDURE OpenLinksForRecRef@1200050005(VAR RecRef@1200050000 : RecordRef);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
OpenRecordLinks(RecordLinks);
END;
END;

PROCEDURE MailLinksForRecRef@1100408004(VAR RecRef@1200050000 : RecordRef;SuggestedEMailAddr@1100408000 : Text[1024];Body@1100408001 : Text[1024]);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
MailRecordLinks(RecordLinks,SuggestedEMailAddr,Body);
END;
END;

PROCEDURE OpenRecordLinks@1100408000(VAR RecordLinks@1100408000 : Record 2000000068);
VAR
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN BEGIN
HYPERLINK(FileName);
END ELSE
HYPERLINK(URL);
UNTIL NEXT = 0;
END;
END;

PROCEDURE MailRecordLinks@1100408003(VAR RecordLinks@1100408000 : Record 2000000068;SuggestedEMailAddr@1100408011 : Text[1024];Body@1100408012 : Text[1024]);
VAR
Mail@1000000000 : Codeunit 397;
URL@1100408002 : Text[1024];
FileName@1100408001 : Text[1024];
BEGIN
Mail.NewMessage(SuggestedEMailAddr,”,Text031,Body,”,TRUE);

WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN
Mail.AttachFile(FileName)
ELSE
Mail.AttachFile(URL);
UNTIL NEXT = 0;
END;

Mail.Send;
END;

PROCEDURE MailFile@1100408002(VAR FileName@1100408011 : Text[1024];VAR Description@1100408000 : Text[250]);
VAR
Mail@1000000000 : Codeunit 397;
BEGIN
Mail.NewMessage(”,”,Description,”,FileName,TRUE);
Mail.Send;
END;

EVENT SQLConnection@1000000000::InfoMessage@48(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlInfoMessageEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::StateChange@49(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.StateChangeEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::Disposed@50(sender@1000000001 : Variant;e@1000000000 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.EventArgs");
BEGIN
END;

BEGIN
{
You experience bad performance when you scroll any list in the classic client in Microsoft Dynamics NAV 2009

To resolve this problem, use one of the following methods:
You can create a required index directly in the SQL database for the Record Links table by using Microsoft SQL Server Management Stu
You can execute the following TSQL statement in Microsoft NAV 2009 database:

CREATE INDEX Sign_Index ON [Record Link] ([Record ID], [Type], Company)
}
END.
}
}

[/code]

Posted on

UPLOAD and DOWNLOAD size limit

Always something new.  I have bin using the Record Links to store pointers to scanned files, created pdf files all other files that the user would like to link to a record and store centrally.  I have the option to import the file to a BLOB field and also to a separate tables in a different database.

What I do next is to install a single aspx page to an internal web server and the URL in the Record Links table points me to this page with parameters that define where to get the centrally stored file.  This aspx page can fetch the file from the database or through web services from NAV BLOB field.

My first version of this was to have everything executed on the client side.  I did not see that working for NAV 2013 where we can have users that authenticate outside the windows domain.  The same is for NAV 2009 R2 where the Role Tailored Client is connecting to the service tier via WAN.

So I moved the functionality to the service tier and wanted to use UPLOAD and DOWNLOAD to transfer the files to and from the client.  Here I hit a wall.  There is a size limit to the files that I can upload and download.

I therefore looked at the code I had created earlier and modified it a little bit.

[code]PROCEDURE UploadClientFile@1100408005(FileName@1100408000 : Text[1024]) ServerFileName : Text[1024];
VAR
Document@1100408011 : BigText;
ServerConvert@1100408010 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert";
ServerBase64File@1100408009 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
ServerDocumentFile@1100408008 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
ClientConvert@1100408007 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert" RUNONCLIENT;
ClientFile@1100408006 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ServerFileStream@1100408005 : File;
ServerBase64FileName@1100408004 : Text[1024];
OutStr@1100408002 : OutStream;
BEGIN
Document.ADDTEXT(ClientConvert.ToBase64String(ClientFile.ReadAllBytes(FileName)));
ServerBase64FileName := ThreeTier.ServerTempFileName(”,”);
ServerFileStream.WRITEMODE(TRUE);
ServerFileStream.CREATE(ServerBase64FileName);
ServerFileStream.CREATEOUTSTREAM(OutStr);
Document.WRITE(OutStr);
ServerFileStream.CLOSE;

ServerFileName := ThreeTier.ServerTempFileName(”,”);

ServerDocumentFile.WriteAllBytes(
ServerFileName,
ServerConvert.FromBase64String(
ServerBase64File.ReadAllText(ServerBase64FileName)));

ServerBase64File.Delete(ServerBase64FileName);
END;

PROCEDURE DownloadServerFile@1100408017(FileName@1100408000 : Text[1024]) ClientFileName : Text[1024];
VAR
Document@1100408002 : BigText;
ServerFile@1100408003 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
ServerConvert@1100408001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert";
ClientBase64File@1100408006 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ClientStreamWriter@1100408007 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.StreamWriter" RUNONCLIENT;
ClientConvert@1100408010 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert" RUNONCLIENT;
ClientFile@1100408009 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ClientPath@1100408011 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.Path" RUNONCLIENT;
ClientBase64FileName@1100408008 : Text[1024];
SubString@1100408004 : Text[1024];
Pos@1100408005 : Integer;
BEGIN
Document.ADDTEXT(ServerConvert.ToBase64String(ServerFile.ReadAllBytes(FileName)));
ClientBase64FileName := ThreeTier.ClientTempFileName(”,’b64′);
ClientStreamWriter := ClientBase64File.CreateText(ClientBase64FileName);
Pos := 1;
WHILE Pos < Document.LENGTH DO BEGIN
Pos := Pos + Document.GETSUBTEXT(SubString,Pos,MAXSTRLEN(SubString));
ClientStreamWriter.Write(SubString);
END;
ClientStreamWriter.Close;

ClientFileName :=
ClientPath.GetTempPath +
SigningTools.RemovePath(FileName);

ClientFile.WriteAllBytes(
ClientFileName,
ClientConvert.FromBase64String(
ClientBase64File.ReadAllText(ClientBase64FileName)));
END;
[/code]

The problem is that this method is not fast enough. Perhaps someone reading this has a faster solution.  I convert the file to base64 and use BigText variable to move it from and to the service tier.  This will support large files but it is slow.

Posted on

Copy Record Link between databases

In my current upgrade process I needed to copy the Record Link table from one database to another.  I can use a copy-paste method for small number of records but in this case I needed something else.

A Dataport or a XML Port was the way I choose to go.  There is one problem though.  the Record Link table has a field for the Record ID and this field is not a field type that can be used in dataports or xml ports.

I created two new global variables, one text variable for the record position and one integer variable for the table number.  And to help me with the conversion I needed to have two more global variables; RecID  and RecRef.

RecordLinkVariables

Now for the code.  To convert my Record ID field to a record position and a table number

RecordLinksBeforeExport

and to convert from the table number and position to a Record ID

RecordLinksAfterImport

Posted on

Record Links in NAV 2009 R2

I made a function that collects all the record links from the documents related to a customer ledger entry or a vendor ledger entry.  I then display the list or record links and offer the user the change to open the links or email them.

This all worked just fine in my Classic Client but in the Role Tailored Client the page gives me a metadata error.  I asked Microsoft and got an answer from Lars Lohndorf-Larsen.

I have been in contact with development and they say that this is unfortunately a restriction in NAV2009. It is not possible to use “Record Link” as Source Table on a page.
“Record Link” is a special table which gets (re)built dynamically. A page expects a normal table and will implicitly compile the underlying table. But this particular table does not have usual metadata that the page expects, so it fails.
In NAV2013 it works because this table was redesigned.

This means that I will have to use the Record Link table as a global variable instead of using it as a source table.  That is what I will do.

Posted on

Import a text to RTC Note in the Record Link Table

In the process of importing data from MSSQL into NAV I needed to import a comment text. I wanted to import the text into the Note BLOB field in the Record Link table. It was not obvious how to do this but after some work I successfully imported all my comments.

I first start to create the record link with the following code
[code htmlscript=”false”]IF Note <> ” THEN BEGIN
LinkID := DestTbl.ADDLINK(”,DestTbl.TABLECAPTION);
RecordLink.GET(LinkID);
RecordLink.Type := RecordLink.Type::Note;
RecordLink.Note.CREATEOUTSTREAM(OutStr);
OutStr.WRITE(Text2NoteText(Note));
RecordLink.MODIFY;
END;[/code]
After my first round I found out that I needed to include the length of the Note as a Char in the beginning of the note. Then I noticed that my Icelandic characters where not correctly imported and after some study found out that the content of the BLOB needs to be UFT-8 encoded. This led me to create the Text2NoteText function.
[code htmlscript=”false”]Text2NoteText(NoteToImport : Text[1024]) NAVNode : Text[1024]
NAVNode := UTF8Encode(NoteToImport);
NoteLength := STRLEN(NAVNode);
IF NoteLength <= 255 THEN BEGIN
Char1 := NoteLength;
NAVNode := FORMAT(Char1) + NAVNode;
Char2 := 1;
END ELSE BEGIN
Char1 := 128 + (NoteLength – 256) MOD 128;
Char2 := 2 + (NoteLength – 256) DIV 128;
NAVNode := FORMAT(Char1) + FORMAT(Char2) + NAVNode;
END;[/code]
And the UFT8Encode function
[code htmlscript=”false”]UTF8Encode(String2Encode : Text[1024]) EncodedString : Text[1024]
MakeVars;
String2Encode := CONVERTSTR(String2Encode,NavStr,AsciiStr);
FOR Index := 1 TO STRLEN(String2Encode) DO BEGIN
Char2 := String2Encode[Index];
IF Char2 <= 127 THEN
EncodedString := EncodedString + FORMAT(Char2)
ELSE IF Char2 <= 192 THEN BEGIN
Char1 := 194;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END ELSE BEGIN
Char1 := 195;
Char2 := Char2 – 64;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END;
END;[/code]
The MakeVars function creates the NavStr and AsciiStr that I need for the conversion. This is similar to function InitCharTables in Codeunit 424 except I am not using Excel to convert the strings.

Attached is a codeunit with these functions.

RTCNoteTool

 

Posted on

Web Service that deliveres BLOB data

I wanted to be able to use the Record Link table in NAV to link to my attachments. Since that part of NAV is not customizable every attachment that I link to NAV needs to be accessible via URL.

So, when I import or scan a file into NAV it can be stored in a BLOB or I can store it in a customized external database. After storing the file I create a URL link to that file and insert that as a new link to any record in NAV. To complete this task I created a NAV Web Service that delivers the file as a base64 string. Here is the part of the code that encodes the BLOB stream to a BigText variable.
[code htmlscript=”false”]TempFile.CREATETEMPFILE;
TempFileName := TempFile.NAME;
TempFile.CLOSE;
TempFile.TEXTMODE(FALSE);
TempFile.CREATE(TempFileName);
TempFile.CREATEOUTSTREAM(OutStr);
DocumentStore.Blob.CREATEINSTREAM(InStr);
COPYSTREAM(OutStr,InStr);
TempFile.CLOSE;

CREATE(XMLDoc);
CREATE(ADOStream);
XMLNode := XMLDoc.createNode(‘element’, ‘ImageFile’, ‘SKYRR Signing’);
XMLNode.dataType := ‘bin.base64’;

ADOStream.Type := 1;
ADOStream.Open();
ADOStream.LoadFromFile(TempFileName);
XMLNode.nodeTypedValue := ADOStream.Read();
ADOStream.Close();

Document.ADDTEXT(XMLNode.text);

CLEAR(ADOStream);
CLEAR(XMLNode);
CLEAR(XMLDoc);
ERASE(TempFileName);[/code]
Then I created a aps.net website that can get the document both from this NAV Web Service and also from the customized database all based on the parameters passed with the URL.

Attached is a part of the NAV code and the website required to deliver the attachment.

WebSite

Attachment NAV Source Code

 

Posted on

Scanning in Remote Desktop

When running Dynamics NAV as a Remote Application or in Remote Desktop you are missing connection to your locally connected scanner.  I have created a solution that uses a standalone application running on the client computer.  That application uses the TwainControlX from Ciansoft and connects to a Microsoft SQL database.  The database needs to be on a computer that both the client computer and the remote desktop computer have access to.

On that database server a database is created with only one table.  That table can be truncated every night in a maintenance job.  This is the table creation script.

CREATE TABLE [dbo].[ScanRequests](
	[ComputerName] [varchar](20) NOT NULL,
	[ClientName] [varchar](20) NOT NULL,
	[SelectedDeviceID] [varchar](250) NULL,
	[Image] [image] NULL,
	[RequestDateTime] [datetime] NULL,
	[FormatID] [varchar](50) NULL,
	[AnswerDateTime] [datetime] NULL,
	[Answered] [int] NOT NULL,
	[AnswerText] [varchar](250) NULL,
	[PageNo] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The program on the client then connects to the database and to the scanner.

A code in Dynamics NAV creates the request for a scan by inserting a entry into the ScanRequest table and the application on the client computer automatically scans the images and updates that scan request entry.  The updated entry then containes the image file that is imported into Dynamics NAV or attached as a link.

The following ZIP files are encrypted.  View the Product page for more information.

Twain Scanner (ISL)
NAV Myndlestur (TwainOCX) Source Code (ISL)

 

Posted on

Record Links and Attachments

The standard Record Links table in Dynamics NAV has the ability to store a URL and link it to any record in the Dynamics NAV database.  However, in order to fully use this each user must have access to the give URL.

As a part of the Purchase Invoice signing solution I built a process to select or scan a file, upload it to a SQL database and create a URL to that file in the Record Link table.  I create a  SQL link table that stores the file and a permission table that holds information on users permission by table id.

I used TwainControlX to scan and create files to import and link.  Then I add a “Link File” button to the Purchase Order.

When Link File is selected the following dialogue appears.

After selecting or scanning a file a new link is created and the file is uploaded to the SQL server.

The file is now available to all users that have the required permission through the following link.

The can be downloaded directly with the Open button or NAV can download and open the file with code.  I created a .ASP web page to download files from the Link database.