Others How To Topics

From Pengower
Jump to: navigation, search

Can't find what you are looking for in the topics above? You might find it here.

How to check for NULLs

You often need to check if a parameter was passed into a routine or if a field was populated in a type. However, checking for NULL is unreliable as empty values may or may not be NULL.


Dates


Dates In Action Script

Empty dates in the system are actually dates with a value of 1 Jan 00 or thereabouts

Set datevalue = 0 + parameter or element  //This will return a DateTime if the element/parameter is a datetime
If (datevalue.Year < 1910)
    //datevalue is empty
EndIf

Please note: If the date is empty, date.Year will return an empty or NULL value so it does not work to say date.Year > 1910. You'd have to do something like this to simulate that behaviour:

Set datevalue = 0 + parameter or element  //This will return a DateTime if the element/parameter is a datetime
If (datevalue.Year < 1910)
    //Do nothing
Else
    //Do the thing you want to do if there is a date
EndIf

You must also apply this principle when comparing two dates as a newly created empty date field displays the following comparison behaviour

virginBlankDate < TODAY (nope)
virginBlankDate > TODAY (nope)
virginBlankDate = TODAY (nope)
virginBlankDate.Year < 1910 (yep)

Dates In Matisse SQL''

Dates are never NULL in Matisse and it seems to work to just check for dates less than 1910-01-01 like this:

Output = "SQL Absence Where First Day Back < DATE '1901-01-01'"

Strings

Set value = parameter or element
If ("" + value = "")
    //value is empty
EndIf

Numbers

There is an additional subtlety with numbers in elements as they behave differently depending on whether a value has previously been specified and then deleted or whether there has never been a value. If no value has ever been entered into a number element on a type, you will get a null or empty, so can do the usual "" + value = "". However, if you specify a number in an element, save the type and then delete the number. Save the type again and then the number element will actually have the value -1.79769313486232E+308 which is Double.MinValue from C#. So, to be safe when checking for empty numbers, you need to do this:

Set value = parameter or element
If (("" + value = "") OR (value < (0-100000)))  //Remember you can't specify negative numbers directly. Subtract as large a number as you need to make sure it is out of range of valid numbers.
    //Value is empty
EndIf

If you are sure the number you are checking for is not coming from a Date element on a type, you should be able to skip the check for the very small number.


Types

With types you rely on having an element you always know is populated, for example an ID and then it's much the same as checking for strings. Remember that the system will "fail silently" if you ask for a non-existing element or even if you ask for an element from a NULL. I.e.:

Set type = parameter
If ("" + type["ID"]) = "")
    //The type is NULL - or doesn't have an ID element or the ID element is empty
EndIf


How to create an array of string literals

You can easily create an array of string literals like this:

Set fields = {'Product ID', 'Master Product ID', 'Name', 'Country', 'Language'}

However, please note that the following will not work: You will get the right number of elements, but they will all be empty:

Set fields = {"Product ID", "Master Product ID", "Name", "Country", "Language"}

To make it completely clear, the key is to use single quotes rather than double quotes.

N.B. Avoid using Brackets when creating the array as the parser will try to evaluate the brackets. To get around this add the required field using Add x To y


How to add a timer on a page to perform a refresh

1. The creation of the Arguments class


This method of handling Command Line parameters is based of the method suggested in The Code Project.

The key to Command Line parameter processing is the Arguments class which is a versatile command line parser allowing parameters to be provided in any order and in a number of different formats.

Therefore, before anything else, this class was created.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using System.Text.RegularExpressions;

namespace ePayslips_Uploader
{
    class Arguments
    {

    private StringDictionary Parameters;

        // Constructor

        public Arguments(string[] Args)
        {
            Parameters = new StringDictionary();
            Regex Spliter = new Regex(@"^-{1,2}|^/|=|:",
                RegexOptions.IgnoreCase|RegexOptions.Compiled);

            Regex Remover = new Regex(@"^['""]?(.*?)['""]?$",
                RegexOptions.IgnoreCase|RegexOptions.Compiled);

            string Parameter = null;
            string[] Parts;

            // Valid parameters forms:

            // {-,/,--}param{ ,=,:}((",')value(",'))

            // Examples: 

            // -param1 value1 --param2 /param3:"Test-:-work" 

            //   /param4=happy -param5 '--=nice=--'

            foreach(string Txt in Args)
            {
                // Look for new parameters (-,/ or --) and a

                // possible enclosed value (=,:)

                Parts = Spliter.Split(Txt,3);

                switch(Parts.Length){
                // Found a value (for the last parameter 

                // found (space separator))

                case 1:
                    if(Parameter != null)
                    {
                        if(!Parameters.ContainsKey(Parameter)) 
                        {
                            Parts[0] = 
                                Remover.Replace(Parts[0], "$1");

                            Parameters.Add(Parameter, Parts[0]);
                        }
                        Parameter=null;
                    }
                    // else Error: no parameter waiting for a value (skipped)

                    break;

                // Found just a parameter

                case 2:
                    // The last parameter is still waiting. 

                    // With no value, set it to true.

                    if(Parameter!=null)
                    {
                        if(!Parameters.ContainsKey(Parameter)) 
                            Parameters.Add(Parameter, "true");
                    }
                    Parameter=Parts[1];
                    break;

                // Parameter with enclosed value

                case 3:
                    // The last parameter is still waiting. 

                    // With no value, set it to true.

                    if(Parameter != null)
                    {
                        if(!Parameters.ContainsKey(Parameter)) 
                            Parameters.Add(Parameter, "true");
                    }

                    Parameter = Parts[1];

                    // Remove possible enclosing characters (",')

                    if(!Parameters.ContainsKey(Parameter))
                    {
                        Parts[2] = Remover.Replace(Parts[2], "$1");
                        Parameters.Add(Parameter, Parts[2]);
                    }

                    Parameter=null;
                    break;
                }
            }
            // In case a parameter is still waiting

            if(Parameter != null)
            {
                if(!Parameters.ContainsKey(Parameter)) 
                    Parameters.Add(Parameter, "true");
            }
        }

        // Retrieve a parameter value if it exists 

        // (overriding C# indexer property)

        public string this [string Param]
        {
            get
            {
                return(Parameters[Param]);
            }
        }
    }
}


2. Determine the list of parameters that the program needs to be able to accept


After examining the existing form controls, a list command line input parameters was decided upon.

The main program was amended to accept an argument list (args). Checking the length of this argument list allows us to determine whether the program is being run from the command line (with parameters) or not.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;

namespace ePayslips_Uploader
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]

        static void Main(string[] args)
        {

            // This program can be run as a windows form or as a console application.
            // Therefore check for arguments to see whether it is running as a 
            // console application otherwise assume it is a windows  
            //
            //  Acceptable arguments are as follows:
            //  -import (P for payslips or E for employees)                                 : Required for both imports
            //  -folder (Payslips Folder)                                                   : Required for payslips import
            //  -length (Payroll Number Length)                                             : Required for both imports
            //  -prefix (Payroll Number Prefix)                                             : Required for both imports
            //  -suffix (Payroll Number Suffix)                                             : Required for both imports
            //  -document (Document Type)                                                   : Required for payslips import
            //  -overwrite (Overwrite If Exists)                                            : Required for payslips import
            //  -send (Do Not Send Emails Before; in the format DD-MM-YYYY-HH-MM-SS)        : Required for employees import
            //  -letters (Apply To Letters)                                                 : Required for employees import
            //  -file (Import File)                                                         : Required for employees import
            //
            if (args.Length > 0)    
                {
                    CommandLineImporter consoleApp = new CommandLineImporter(args);
                }
	        else
	            {
                 Application.EnableVisualStyles();
                 Application.SetCompatibleTextRenderingDefault(false);
                 Application.Run(new Importer());
	            }
        }
    }
}

If the program is being run from the command line, instead of the form being rendered, the parameters are processed using a new class, CommandLineImporter.


3. Creation of the CommandLineImporter Class


The CommandLineImporter class is where the Command Line parameters are processed.

Note how a class of arguments is created with the name of CommandLine. This allows parameters to be referenced using

CommandLine["parameter name"]

Once we have the parameters, we can proceed as though we had received the parameters from the form itself.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Globalization;

namespace ePayslips_Uploader
{
    class CommandLineImporter
    {

        Arguments CommandLine;
        private DateTime sendEmails;

        public CommandLineImporter(string[] args)
        {
            Boolean importPayslips = false;
            Boolean importEmployees = false;

            string auditFilename = string.Format("importer_logfile_{0:yyyyMMdd_hhmmsstt}.txt", DateTime.Now);
            this.CommandLine = new Arguments(args);

            //
            //  Let's work out what we're importing; payslips or employees
            //
            if (this.CommandLine["import"] != null)
            {
                if (this.CommandLine["import"].ToLower() == "p")
                {
                    auditFilename = string.Format("ePayslips_logfile_{0:yyyyMMdd_hhmmsstt}.txt", DateTime.Now);
                    importPayslips = true;
                }
                if (this.CommandLine["import"].ToLower() == "e")
                {
                    auditFilename = string.Format("employees_logfile_{0:yyyyMMdd_hhmmsstt}.txt", DateTime.Now);
                    importEmployees = true;
                }
            }
     
           
            using (var logger = new AuditLogger(auditFilename))
            {
                //
                // Timestamp beginning of processing...
                //
                logger.Write(String.Format("{0:d/M/yyyy HH:mm:ss}", DateTime.Now));     
                logger.Write("Start Processing");

                //////////////////////////////////////////////////////////
                // Process ePayslips
                //////////////////////////////////////////////////////////
                if (importPayslips)
                {
                    // 
                    // If parameters pass initial validation; perform the import
                    if (validatePayParameters(logger))
                    {
                        string resultStatus = this.ImportPayslips(logger);
                        logger.Write(resultStatus);
                        Console.WriteLine(resultStatus);
                    }
                    else
                        Console.WriteLine("Errors detected; see log file for more information");
                }


                //////////////////////////////////////////////////////////
                // Process Employees
                //////////////////////////////////////////////////////////
                if (importEmployees)
                {
                    // 
                    // If parameters pass initial validation; perform the import
                    if (validateEmpParameters(logger))
                    {
                        string resultStatus = this.ImportEmployees(logger);
                        logger.Write(resultStatus);
                        Console.WriteLine(resultStatus);
                    }
                    else
                        Console.WriteLine("Errors detected; see log file for more information");
                }


                //
                // Timestamp end of processing...
                //
                logger.Write(String.Format("{0:d/M/yyyy HH:mm:ss}", DateTime.Now));     
                logger.Write("End Processing");
            }       
        }


        //////////////////////////////////////////////////////////
        // Import Payslips
        //////////////////////////////////////////////////////////
        public string ImportPayslips(ILogger logger)
        {
            Boolean checkOverwrite = false;
            String statusText = "";
            int errCnt = 0; 

            if (this.CommandLine["overwrite"] != null)
            {
                if (this.CommandLine["overwrite"].ToLower() == "y")
                    checkOverwrite = true;
            }


            PayslipsImporter importer = new PayslipsImporter(this.CommandLine["folder"],
                System.Convert.ToInt32(this.CommandLine["length"]),
                this.CommandLine["prefix"],
                this.CommandLine["suffix"],
                this.CommandLine["document"],
                checkOverwrite);

            importer.LoadPayslips();
            importer.ImportPayslips();

            foreach (Payslip p in importer.Payslips)
            {
                if (p.Status == PayslipStatus.Error)
                {
                    logger.Write(p.ErrorMessage);
                    errCnt++;
                }
            }


            if (importer.Cancelled)
                statusText = String.Format("Import cancelled ({0} errors)", errCnt);
            else
               statusText = String.Format("Import finished with {0} errors", errCnt);

            return statusText;
        }



        //////////////////////////////////////////////////////////
        // Import Employees
        //////////////////////////////////////////////////////////
        public string ImportEmployees(ILogger logger)
        {
            Boolean checkLetters = false;

            if (this.CommandLine["letters"] != null)
            {
                if (this.CommandLine["letters"].ToLower() == "y")
                    checkLetters = true;
            }

           EmployeesImporter empImporter = new EmployeesImporter(this.CommandLine["file"],
                                                       System.Convert.ToInt32(this.CommandLine["length"]),
                                                       this.CommandLine["prefix"],
                                                       this.CommandLine["suffix"],
                                                       this.sendEmails,
                                                       this.sendEmails,
                                                       checkLetters);

           return "Import file sent to server";
        }

        //////////////////////////////////////////////////////
        //  Validation of ePayslips Parameters
        //////////////////////////////////////////////////////
        private bool validatePayParameters(ILogger logger)
        {
            Boolean validParameters = true;

            //
            //  -folder Validation
            //
            if (this.CommandLine["folder"] == null)
            {
                logger.Write("Parameter -folder not defined!");
                validParameters = false;
            }
            else
            {
                Regex r = new Regex(@"^(([a-zA-Z]\:)|(\\))(\\{1}|((\\{1})[^\\]([^/:*?<>""|]*))+)$");
                if (!(r.IsMatch(this.CommandLine["folder"])))
                {
                    logger.Write("Payslips folder name contains invalid characters");
                    validParameters = false;
                }
                else
                    if (!(System.IO.Directory.Exists(this.CommandLine["folder"])))
                    {
                        logger.Write("Provided payslips folder is not a valid directory");
                        logger.Write(this.CommandLine["folder"]);
                        validParameters = false;
                    }
            }

            if (this.CommandLine["length"] == null)
            {
                logger.Write("Parameter -length not defined!");
                validParameters = false;
            }
            else
            {
                double Num;
                bool isNum = double.TryParse(this.CommandLine["length"], out Num);
                if (!(isNum))
                {
                    logger.Write("Provided payroll number length is not a number");
                    validParameters = false;
                }
            }

            //
            //  documentType Validation
            //
            bool isFound = false;

            if (this.CommandLine["document"] == null)
            {
                logger.Write("Parameter -document not defined!");
                validParameters = false;
            }
            else
            {
                foreach (DocumentTypes value in Enum.GetValues(typeof(DocumentTypes)))
                {
                    string enumValue = Functions.GetEnumDescription(value);
                    if (enumValue.ToLower() == this.CommandLine["document"].ToLower())
                        isFound = true;
                }

                if (!(isFound))
                {
                    logger.Write("Provided document type is not a valid value");
                    validParameters = false;
                }
            }

            //
            //  overwriteIfExits Validation
            //
            if (this.CommandLine["overwrite"] != null)
            {
                if (!(this.CommandLine["overwrite"].ToLower() == "y") & !(this.CommandLine["overwrite"].ToLower() == "n"))
                {
                    logger.Write("Provided Overwrite If Exists parameter needs to be either Y or N or empty");
                    validParameters = false;
                }
            }

            return validParameters;
        }

        //////////////////////////////////////////////////////
        //  Validation of Employee Parameters
        //////////////////////////////////////////////////////
        private bool validateEmpParameters(ILogger logger)
        {
            Boolean validParameters = true;

            //
            //  -file Validation
            //
            if (this.CommandLine["file"] == null)
            {
                logger.Write("Parameter -file not defined!");
                validParameters = false;
            }
            else
            {
                if (!(File.Exists(this.CommandLine["file"])))
                {
                        logger.Write("Provided employee import file does not exist");
                        logger.Write(this.CommandLine["file"]);
                        validParameters = false;
                    }
            }

            
            //
            //  -length Validation
            //
            if (this.CommandLine["length"] == null)
            {
                logger.Write("Parameter -length not defined!");
                validParameters = false;
            }
            else
            {
                double Num;
                bool isNum = double.TryParse(this.CommandLine["length"], out Num);
                if (!(isNum))
                {
                    logger.Write("Provided payroll number length is not a number");
                    validParameters = false;
                }
            }

            //
            //  -send Validation
            //
            if (this.CommandLine["send"] == null)
            {
                this.sendEmails = DateTime.Now.AddHours(1);
            }
            else
            {
                string  dateFormat = "dd-MM-yyyy-HH-mm-ss";
                string dateTest = this.CommandLine["send"];

           if (!(DateTime.TryParseExact(dateTest, dateFormat, 
                              new System.Globalization.CultureInfo("en-GB"), 
                              System.Globalization.DateTimeStyles.None,
                              out this.sendEmails)))
                {
                    logger.Write("Provided send parameter is not a valid datetime");
                    validParameters = false;
                }
            }

            //
            //  -letters Validation
            //
            if (this.CommandLine["letters"] != null)
            {
                if (!(this.CommandLine["letters"].ToLower() == "y") & !(this.CommandLine["letters"].ToLower() == "n"))
                {
                    logger.Write("letters parameter needs to be either Y or N or empty");
                    validParameters = false;
                }
            }

            return validParameters;
        }
    
    }
}


How to facilitate command line inputs for a Windows form application

Learn different application customizations.

How to create a Dynamic HTML button

A dynamic button is a button element that contains a dynamic text. It has the ability to change its state when hovered over.

Insert sample image here

We are going to use HTML and Jquery to create the button.

HTML Markup

<style type="text/css">
       .button div
       {
           float:left;
           height:30px;  /* Set the btnMiddle.line-height to the same value to vertically center the text */
           padding:0px;
           margin:0px;
       }
       .btnMiddle
       {
           float:left;
           width:50px;
           font-size:12pt;
           font-weight:bold;
           color:White;
           text-align:center;
           line-height:30px;
       }
</style>
 <div class="button">
 <div class="btnLeft"><img src="Images/ImgBtnLeftNormal.png" /></div>
 <div class="btnMiddle" style="background-image:url(Images/ImgBtnMiddleNormal.png);">Button text</div>
 <div class="btnRight"><img class="btnRight" src="Images/ImgBtnRightNormal.png"/></div>
 </div>

Note that the "wrapper" around the button does not have to be a div - it could be an li for example - but it must have the class button.

We then need the following script (you need to include the jQuery library first):

<script type="text/javascript">
   $(function() {
       $(".button").hover(buttonOver, buttonOut);
   });
   function buttonOver(eventObj) {
       $(this).children(".btnLeft").children("img").attr("src", "Images/ImgBtnLeftHot.png");
       $(this).children(".btnRight").children("img").attr("src", "Images/ImgBtnRightHot.png");
       $(this).children(".btnMiddle").css("background-image", "url(Images/ImgBtnMiddleHot.png)");
   }
   function buttonOut(eventObj) {
       $(this).children(".btnLeft").children("img").attr("src", "Images/ImgBtnLeftNormal.png");
       $(this).children(".btnRight").children("img").attr("src", "Images/ImgBtnRightNormal.png");
       $(this).children(".btnMiddle").css("background-image", "url(Images/ImgBtnMiddleNormal.png)");
   }
</script>


Note: Images - This technique requires three images:

a.) one for the left end of the button

b.) one for the right end of the button

c.) background image for the middle

They all need to be the same height (in the example above they are all 30px high). The background-image for the middle bit should be 1px in width and will be repeated horizontally to expand the space. You will of course need two versions of each image - a "normal" and a "hot" version.


How to build a custom Drop Down

Sometimes you need to build a custom drop-down to restrict the items a user can see in a list. These tree bits of action script combined with a Content field will do the job.

This method uses a utility function to render the dropdown and shows you how to call it and how to retrieve the result.


Example of calling the render function

Set frm = Do Open Custom Form Params Custom Form = "Referral"
 
Set custID  = ""
Set tmpArr  = referral["Client Organisation"]
If (tmpArr.Count > 0)
    Set tmpObj  = tmpArr[1]
    Set custID  = tmpObj["ID"]
EndIf
 
Set ArrayToSort = referrer["Customers"]
Set SortByElement = "Name"
Set args = {}
Add ArrayToSort To args
Add SortByElement To args
Set rtn = Do Run Script Params Script Name = "fnLib Sort", Args = args
Set custArr = rtn[3]
 
Set OptionArr = {}
For custNo = 1 To custArr.Count
    Set cust = custArr[custNo]
 
    If ("Yes" = cust["Active?"])
        Set tmpArr  = {}
        Set tmpStr  = cust["ID"]
        Add tmpStr To tmpArr
        Set tmpStr  = cust["Name"]
        Add tmpStr To tmpArr
        If (cust["ID"] = custID)
            Add 1 To tmpArr
        Else
            Add 0 To tmpArr
        EndIf
 
        Add tmpArr To OptionArr
    EndIf
Next
Set ControlID = "customerid"
 
Set args = {}
Add OptionArr To args
Add ControlID To args
Set rtn = Do Run Script Params Script Name = "fn Render Dropdown", Args = args
Set h = rtn[3]
 
Set frm["Client Organisation"]  = h


Example of retrieving the result

Set customerArr = {}
Set tmpStr  = Do Request Field Params Name = "customerid"
Set customerID  = "" + tmpStr
If ("#NONE#" != customerID)
    Set tmpArr  = referrer["Customers"]
    If tmpArr.Count > 0
        Set i = 1
        Loop
            Set tmpObj  = tmpArr[i]
            If (tmpObj["ID"] = customerID)
                Add tmpObj To customerArr
            EndIf
            Set i = i + 1
        Until (customerArr.Count > 0) OR (i > tmpArr.Count)  
    EndIf
EndIf
Set referral["Client Organisation"] = customerArr


fn Render Dropdown

This is a utility function that will return the HTML to render a dropdown

// fn Render Dropdown
 
// arguments OptionArr array ID, Description, selected (bool) eg [ID-1, First Entry, 0]
//  ControlID string  the desired value of the id & name attributes of the control
//  Width integer the desired width of the control in pixies
 
 
Set optionArr  = OptionArr
Set controlid = "" + ControlID
If ("" = controlid)
    Set controlid   = "selectedid"
EndIf
Set width = 0 + Width
If (0 = width)
    Set widthStr = ""
Else
    Set widthStr = "style='width: " + width + "px;'"
EndIf
 
 
Set selected = 0
 
Set h = ""
Set h = h + "<table cellspacing='0' cellpadding='0' border='0' class='workText'>"
Set h = h + "<tbody>"
Set h = h + "<tr>"
Set h = h + "<td style='vertical-align: middle; padding-bottom: 1px;'>"
Set h = h + "<select class='dropDown' id='dropdown' name='dropdown' onchange='set" + controlid + "(this)' " + widthStr + ">"
For optionNo = 1 To optionArr.Count
    Set tmpArr  = optionArr[optionNo]
    If (1 = tmpArr[3])
        Set selected = 1
        Set selectedID = tmpArr[1]
        Set h = h + "<option value='" + tmpArr[1] + "' selected='selected'>" + tmpArr[2] + "</option>"
    Else
        Set h = h + "<option value='" + tmpArr[1] + "'>" + tmpArr[2] + "</option>"
    EndIf
Next
If (0 = selected)
    Set selectedID = "#NONE#"
    Set h = h + "<option value='#NONE#' selected='selected'><none></option>"
Else
    Set h = h + "<option value='#NONE#'><none></option>"
EndIf
Set h = h + "</select>"
Set h = h + "</td>"
Set h = h + "<td style='vertical-align: middle; padding-left: 2px; padding-bottom: 1px;'> </td>"
Set h = h + "</tr>"
Set h = h + "</tbody>"
Set h = h + "</table>"
 
Set h = h + "<input name='" + controlid + "' id='" + controlid + "' value='" + selectedID + "' type='hidden'>"
Set h = h + "<script type='text/javascript'>"
Set h = h + "   function set" + controlid + "(obj)"
Set h = h + "       {"
Set h = h + "       document.getElementById('" + controlid + "').value=obj.value;"
Set h = h + "       }"
Set h = h + "</script>"
 
 
Output = h
Return_Ok

How to process a Word Document

MS Word Docx Processing

The Pengower Platform allows developers to read data from docx files, which is the new MS Word format document. This is done using Content Controls, which are placeholders for certain types of data. The module allows you to read from these content controls, using a shared tag name between the content control and your script. The component can also be used to write data into the document using replacements.

Reading from a .docx document

Before trying to include content controls in your document, please enable the Developer ribbon in MS Word 2007 or above. After doing this, you can include content controls in your document. These content controls need to be given an identifier so that your script can look for the content control you are trying to access. To do that, the properties of the content control can be accessed and can be given a tag name. When referring to this tag name, your script will know what content control to read from and will pull out the inner data of it. This name has to be unique, with the only exception of checkboxes, you can give the same name to several checkbox content controls in order to create a group of checkboxes. When reading from this content control, the output will be something along the lines of: true#true#false Where true means that the checkbox is activated and false means it's not. So in this particular example the two first checkboxes are marked, but not the last one. You can have groups with as many checkboxes as you need.

The following script shows how to call the module from the Pengower Platform:

Set object = Current_Item
Set files = object["File"]
Set file = files[1]

Set docName = file.FullName
Set tagName = "date"

Set args = {}
Add docName To args
Add tagName To args

Set text = Do Run Module Params Name = "WordManager_Read", Args = args

Output = text
Return_Ok

The previous code is achieving the following:
1. Gets a file from an object, it can also be done from a custom form as long as the file is stored on the server.
2. Gets the full path of this file, so that the module knows where to look to.
3. Compiles 2 parameters for the module: 1) "docName" it's the full name on the server for this particular file. 2) "tagName" it's the unique identifier for this content control (or several content controls in the case of checkboxes)
4. Calls the module "WordManager_Read", which returns the content inside this content control.
5. Returns this content as a text.


Writing into a .docx document

This feature can be used in order to customize templates. The idea is that the developer can place hashtags or keywords inside a document, which can be later on replaced in script with real data. This module is a powerful tool to customize documents this way. These hashtags or replacement strings, as you like to call them, can be included inside or outside of content controls, so these data inside a content control, can be also for instance pre-populated for the final user, and then be read afterwards using the technique described in the above paragraph (Reading from a .docx document).

The following script shows how to call the module from the Pengower Platform:

Set object = Current_Item
Set files = object["File"]
Set file = files[1]

Set docName = file.FullName
Set replacement = "##TODAY|28/02/2013|##SW_NAME|Paul Johnes|##CONTENT|this is the replaced content"
Set newPathName = "https:/" + "/www.penapplications.net/MyAppAttachFolder/52709/social worker templates/Replaced_Template.docx"

Set args = {}
Add docName To args
Add replacement To args
Add newPathName To args

Set text = Do Run Module Params Name = "WordManager_Replace", Args = args

Output = text
Return_Ok

The previous code is achieving the following:
1. Gets a file from an object, it can also be done from a custom form as long as the file is stored on the server.
2. Gets the full path of this file, so that the module knows where the initial template is.
3. Compiles 3 parameters for the module: 1) "docName" it's the full name on the server for the original file. 2) "replacement" it's a string of pairs in the format "hashtag|replacement string" so that when the hashtag is found on the document, it gets replaced with the replacement string. Please make sure that all the hashtags are properly paired with their replacement strings. 3) "newPathName" it's the full path of the new generated file (name included). This file is a copy of the original template with all its hashtags replaced as specified in the "replacement" string parameter.
4. Calls the module "WordManager_Replace", which creates the new file.

In most scenarios, a cleaner way to send replacement parameters to the module would be using a 2-dimension array, where a main array hosts a number of 2-position arrays, where the first position is the string to be replaced and the second position contains the replacement string. The name of the array parameter to be sent to the module in this case is: "replacementArray". The following example illustrates how to do it:

//Build a couple of arrays with replacements
Set arrToday = {}
Set today = "<<TODAY>>"
Set todayRepl = "27/03/2013"
Add today To arrToday
Add todayRepl To arrToday

Set arrName = {}
Set name = "<<Name>>"
Set nameRepl = "Joe Bloggs"
Add name To arrName
Add nameRepl To arrName

//Add these arrays to the replacementArray parameter
Set replacementArray = {}
Add arrToday To replacementArray
Add arrName To replacementArray

//Finally, sent the replacementArray parameter to the module through the args array variable
Add replacementArray To args
...

Extracting keywords from a .docx document

Another option available within this module is to be able to extract certain keywords from a docx document. You can specify keywords in the document that start and finish with a certain string or character. The developer can then send the document path along with the starting and ending string, in order to retrieve an array of matching keywords as a result. The following example explains how to call the module:

//Build the parameters
Set docName = "C:\My_Document.docx"
Set start = "<<"
Set end = ">>"
Set args = {}
Add docName To args
Add start To args
Add end To args

Set array = Do Run Module Params Name = "WordManager_Extract_Keywords", Args = args
...


Including images in a .docx document

The module is also able to include pictures in picture content controls, the developer only needs to send the module the document path and a number of replacements. A replacement is a tuple: content control tag name and image path. So multiple images can be attached in one single call to the module. If everything goes well the module returns a message containing "ok". The following example explains how to call the module:

Set docName = "C:\My_Document.docx"
Set tagName = "logo"
Set imagePath = "C:\Logo.jpg"
Set array = {}
Add tagName To array
Add imagePath To array
Set tagName2 = "graph"
Set imagePath2 = "C:\graphic.png"
Set arrayGraph = {}
Add tagName2 To arrayGraph
Add imagePath2 To arrayGraph
Set replacementArray = {}
Add array To replacementArray
Add arrayGraph To replacementArray

Set args = {}
Add docName To args
Add replacementArray To args

Set text = Do Run Module Params Name = "WordManager_Replace_Picture", Args = args
...

Inserting tables in a .docx document

Using this module the developer can also include tables on a docx document. The basic idea is that the developer includes a tag or keyword in a paragraph inside the document, then the module will find this keyword and replace it with the specified table. The user needs to send to the module a number of parameters: the "tagName" or keyword, the document path, the heading for the table (array of strings), and the content of the table. This content can be depicted as a matrix, the bidimensional matrix is an array containing a number of rows, where each row contains the text for every cell in the document. Optionally, the cell width for the table can also be included as a parameter, however if not included it will default to a predefined value in the platform. Another optional parameter is the font attribute, it will give the text the specified font. The font size can also be specified using the optional parameter size. If everything goes well the module returns a message: "Ok".
The following example explains how to call the module:

//Build variables
Set docName = "C:\MyDocument.docx"
Set tagName = "Table1"
Set heading = {'First Name', 'Surname', 'City'}
Set row1 = {'Rafael', 'Diaz', 'Malaga'}
Set row2 = {'Joe', 'Blogs', 'Manchester'}
Set data = {}
Add row1 To data
Add row2 To data
Set font = "Arial"
Set cellWidth = "3500"
Set size = "40"
Set cellAligns = {'right', 'left','center'}
Set cellWidths = {'5000', '1440','1440'}

//Add parameters
Set args = {}
Add docName To args
Add tagName To args
Add heading To args
Add data To args
Add font To args
Add cellWidth To args
Add size To args
Add cellAligns To args
Add cellWidths To args
//Call module
Set text = Do Run Module Params Name = "WordManager_InsertTable", Args = args
...

Reading from tables in a .docx document

Another option related to tables is to read data from them. The table must be given a unique description in the document, this description will be used for the module in order to look for the table and return its content. This unique description goes in the "tagName" parameter. If the routine runs successfully, a 2-dimension array is returned, this contains the table rows, including the heading. Every row is an array itself, containig the text for each of its cells. If an error occurs, then the module will return an error message.
The following example shows how to call the module:

//Build variables
Set docName = "C:\Test.docx"
Set tagName = "Table 1"

//Add parameters
Set args = {}
Add docName To args
Add tagName To args
//Call module
Set array = Do Run Module Params Name = "WordManager_ReadTable", Args = args
...

Inserting Multi-line text

This module call comes in handy when the text needs to be formatted as a multi-line text. This is especially useful when inserting multi-line addresses, where the first line can be the street name, the second line the city, etc. This call will insert a carriage-return separated text into a content control.
The following example shows how to call the module:

//Build variables
Set docName = "C:\Test.docx"
//In this example 'address1' and 'address2' are content controls in the 'Test.docx' document
Set replacement = "address1|Quayside Tower#Birmingham|address2|20-22 Harborne Road#Birmingham#UK"
Set args = {}
Add docName To args
Add replacement To args
Set text = Do Run Module Params Name = "WordManager_ReplaceWithNewLines", Args = args
...

How to use Action Script Language Highlighting for Notepad++

Zenburn

Matches the Zenburn colourscheme included with Notepad++

<NotepadPlus>
    <UserLang name="PenActionScript" ext="pen" udlVersion="2.0">
        <Settings>
            <Global caseIgnored="no" allowFoldOfComments="no" forceLineCommentsAtBOL="no" foldCompact="no" />
            <Prefix Keywords1="no" Keywords2="no" Keywords3="no" Keywords4="no" Keywords5="no" Keywords6="no" Keywords7="no" Keywords8="no" />
        </Settings>
        <KeywordLists>
            <Keywords name="Comments" id="0">03 04 04 00// 01 02</Keywords>
            <Keywords name="Numbers, additional" id="1"></Keywords>
            <Keywords name="Numbers, prefixes" id="2"></Keywords>
            <Keywords name="Numbers, extras with prefixes" id="3"></Keywords>
            <Keywords name="Numbers, suffixes" id="4"></Keywords>
            <Keywords name="Operators1" id="5">- ! & * , . | + < = ></Keywords>
            <Keywords name="Operators2" id="6">And Or</Keywords>
            <Keywords name="Folders in code1, open" id="7"></Keywords>
            <Keywords name="Folders in code1, middle" id="8"></Keywords>
            <Keywords name="Folders in code1, close" id="9"></Keywords>
            <Keywords name="Folders in code2, open" id="10">If For Loop</Keywords>
            <Keywords name="Folders in code2, middle" id="11">Else</Keywords>
            <Keywords name="Folders in code2, close" id="12">EndIf Next Until</Keywords>
            <Keywords name="Folders in comment, open" id="13"></Keywords>
            <Keywords name="Folders in comment, middle" id="14"></Keywords>
            <Keywords name="Folders in comment, close" id="15"></Keywords>
            <Keywords name="Keywords1" id="16">'Add File' 'Application ID' 'Barcode Image' 'Convert File' 'Create Date' 'Create File' 'CSV Interpret' 'Current Section' 'Decrypt String' 'Delete Objects' 'Email' 'Encrypt String' 'HTML Encode' 'Lock Item' 'New Object' 'Query String Element' 'Read Line' 'Regular Expression' 'Store Item' 'Write Line'</Keywords>
            <Keywords name="Keywords2" id="17">/</Keywords>
            <Keywords name="Keywords3" id="18">Set Return_Ok Output Report Progress To Add Cache Clear Key Print CR NL DBLQTE ToLower Count</Keywords>
            <Keywords name="Keywords4" id="19">Do Params Args Force AUTOGEN NOW NULL TODAY Get Where</Keywords>
            <Keywords name="Keywords5" id="20">Current_Form Current_Item Current_User</Keywords>
            <Keywords name="Keywords6" id="21"></Keywords>
            <Keywords name="Keywords7" id="22"></Keywords>
            <Keywords name="Keywords8" id="23"></Keywords>
            <Keywords name="Delimiters" id="24">00" 01 02" 03' 04 05' 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23</Keywords>
        </KeywordLists>
        <Styles>
            <WordsStyle name="DEFAULT" styleID="0" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="COMMENTS" styleID="1" fgColor="7F9F7F" bgColor="3F3F3F" fontName="" fontStyle="2" nesting="0" />
            <WordsStyle name="LINE COMMENTS" styleID="2" fgColor="7F9F7F" bgColor="3F3F3F" fontName="" fontStyle="2" nesting="0" />
            <WordsStyle name="NUMBERS" styleID="3" fgColor="8CD0D3" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="KEYWORDS1" styleID="4" fgColor="C0BED1" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="KEYWORDS2" styleID="5" fgColor="CC0066" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="KEYWORDS3" styleID="6" fgColor="CEDF99" bgColor="3F3F3F" fontName="" fontStyle="1" nesting="0" />
            <WordsStyle name="KEYWORDS4" styleID="7" fgColor="CEDF99" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="KEYWORDS5" styleID="8" fgColor="DCA3A3" bgColor="3F3F3F" fontName="" fontStyle="1" nesting="0" />
            <WordsStyle name="KEYWORDS6" styleID="9" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="KEYWORDS7" styleID="10" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="KEYWORDS8" styleID="11" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="OPERATORS" styleID="12" fgColor="9F9D6D" bgColor="3F3F3F" fontName="" fontStyle="1" nesting="0" />
            <WordsStyle name="FOLDER IN CODE1" styleID="13" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="1" nesting="0" />
            <WordsStyle name="FOLDER IN CODE2" styleID="14" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="FOLDER IN COMMENT" styleID="15" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="DELIMITERS1" styleID="16" fgColor="CC9393" bgColor="3F3F3F" fontName="" fontStyle="1" nesting="0" />
            <WordsStyle name="DELIMITERS2" styleID="17" fgColor="0000FF" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="DELIMITERS3" styleID="18" fgColor="9F9D6D" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="DELIMITERS4" styleID="19" fgColor="9F9D6D" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="DELIMITERS5" styleID="20" fgColor="9F9D6D" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="DELIMITERS6" styleID="21" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="DELIMITERS7" styleID="22" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
            <WordsStyle name="DELIMITERS8" styleID="23" fgColor="DCDCCC" bgColor="3F3F3F" fontName="" fontStyle="0" nesting="0" />
        </Styles>
    </UserLang>
</NotepadPlus>

How to view source for Notepad++ AutoSuggestion File

<?xml version="1.0" encoding="Windows-1252" ?>
<NotepadPlus>
	<AutoComplete>
		<KeyWord name="AUTOGEN" />
		<KeyWord name="Cache Add" />
		<KeyWord name="Cache Clear" />
		<KeyWord name="Cache Get" />
		<KeyWord name="Cache Purge" />
		<KeyWord name="Current_Item" />
		<KeyWord name="Current_Form" />
		<KeyWord name="Current_Selection" />
		<KeyWord name="Current_User" />
		<KeyWord name="Do Add Attribute Params" />
		<KeyWord name="Do Add File Params Object = , Element = , File = " />
		<KeyWord name="Do API Token Params" />
		<KeyWord name="Do Application ID" />
		<KeyWord name="Do Barcode Image Params Code = , Symbology = , Height = , Rotation = " />
		<KeyWord name="Do Barcode String" />
		<KeyWord name="Do Barcode Tag" />
		<KeyWord name="Do Convert File Params File = , Type = " />
		<KeyWord name="Do Create Date Params Params Year = , Month = , Day = " />
		<KeyWord name="Do Create File Params Params Name = , Type = " />
		<KeyWord name="Do Create User Params" />
		<KeyWord name="Do CSV Interpret Params String = " />
		<KeyWord name="Do Current Culture" />
		<KeyWord name="Do Current Section" />
		<KeyWord name="Do Current User" />
		<KeyWord name="Do Current User Type" />
		<KeyWord name="Do Decrypt String Params String = " />
		<KeyWord name="Do Delete Objects Params Objects = , Force = " />
		<KeyWord name="Do Edit Object" />
		<KeyWord name="Do Edit User Item" />
		<KeyWord name="Do Email Params From = , To = , Subject = , Body = , CC = , BCC = , Attachment = , Format = " />
		<KeyWord name="Do Encrypt String Params String = " />
		<KeyWord name="Do Export Type" />
		<KeyWord name="Do File Url Params" />
		<KeyWord name="Do Format Number Params" />
		<KeyWord name="Do Get Attribute Params" />
		<KeyWord name="Do Get Emails Params" />
		<KeyWord name="Do Get Item Params" />
		<KeyWord name="Do Help" />
		<KeyWord name="Do HTML Encode Params String = " />
		<KeyWord name="Do Import Data" />
		<KeyWord name="Do List Attribute" />
		<KeyWord name="Do Lock Item Params Item = " />
		<KeyWord name="Do Log Event Params" />
		<KeyWord name="Do Logout" />
		<KeyWord name="Do Manage Users" />
		<KeyWord name="Do New Object Params Type = " />
		<KeyWord name="Do Open" />
		<KeyWord name="Do Open Custom Form Params" />
		<KeyWord name="Do Open Element" />
		<KeyWord name="Do Open Email Account Params" />
		<KeyWord name="Do Open Group" />
		<KeyWord name="Do Open HTML Page" />
		<KeyWord name="Do Populate File" />
		<KeyWord name="Do Query String Element Params Key = " />
		<KeyWord name="Do Read Params File = " />
		<KeyWord name="Do Read Line Params File = " />
		<KeyWord name="Do Regular Expression Params String = , Find = " />
		<KeyWord name="Do Remove Attribute Params" />
		<KeyWord name="Do Remove File Params" />
		<KeyWord name="Do Replace String Params" />
		<KeyWord name="Do Request Field Params" />
		<KeyWord name="Do Round Params" />
		<KeyWord name="Do Run Module Params" />
		<KeyWord name="Do Run Script Params" />
		<KeyWord name="Do Send SMS Message Params" />
		<KeyWord name="Do Set Section Params" />
		<KeyWord name="Do Sub String Params" />
		<KeyWord name="Do Split String Params" />
		<KeyWord name="Do Store Item Params" />
		<KeyWord name="Do Update Element Params" />
		<KeyWord name="Do Update User Params" />
		<KeyWord name="Do View Login Params" />
		<KeyWord name="Do View URL Params" />
		<KeyWord name="Do Write Params" />
		<KeyWord name="Do Write Line Params" />
		<KeyWord name="Return_Error" />
		<KeyWord name="Return_Ok" />
		<KeyWord name="NOW" />
		<KeyWord name="NULL" />
		<KeyWord name="TODAY" />
	</AutoComplete>
</NotepadPlus>

How to view source for Automatically Filtering Relationship Elements

The following code added to an information tab will add the Filter Text to the Filter on the Form/Item and then simulate a Return Key Press. This will then reload the form with the filter applied the same as if it were entered manually. Removing the condition can lead to an infinite loop as the Return Key Press event reloads the pages, calling the onload of the image to be called again.

It also has a side effect of replacing any user entered filter with the filter defined in the function so should only be used to force a filter on the user. Code to apply filter:

<script type='text/javascript'>
function filterBy(){
	var filter = document.getElementById('*IndexOfElementOnForm/Type*RFIL');
	//need this if or causes infinite loop
	if (filter.value != '*Filter text to be applied*'){
		filter.value = '*Filter text to be applied*';
		var event = filter.ownerDocument.createEvent("KeyEvents");
		event.initKeyEvent("keypress",true,true,null,false,false,false,false, KeyEvent.DOM_VK_RETURN, KeyEvent.DOM_VK_RETURN);
		filter.dispatchEvent(event);
		}
}
</script>
<img src='sticons/spacer.gif' onload='filterBy()'>

Be sure to replace *IndexOfElementOnForm/Type* with the index e.g.

var filter = document.getElementById('112314RFIL');


And *Filter text to be applied* with the filter to be used e.g.

if (filter.value != 'Account'){
		filter.value = 'Account';

How to assign variables to a relationship

Some relationships can only have one item in it. For example, a Contact may have a relationship to an Organisation and there will only ever be one Organisation in that relationship.

When you create a new Contact, you would typically do something like;

Set org = Current_Item
Set contact = Do New Object Params Type = "Contact"
Set contact["Organisation"] = org
<pre>

This works fine most of time. However, if you tried to pass the contact to another bit of script <i>before</i> you returned it to the user and that other piece of script tried to read the Organisation from the employee then it would fail:

<pre>
Set org = Current_Item
Set contact = Do New Object Params Type = "Contact"
Set contact["Organisation"] = org
Set testArr = contact["Organisation"]
Output = testArr.Count

This will output 0

The reason is that while you can just assign a variable to a relationship, this is not strictly kosher - you should assign an array as such:

<pre> Set org = Current_Item Set orgArr = {} Add org To orgArr Set contact = Do New Object Params Type = "Contact" Set contact["Organisation"] = orgArr Set testArr = contact["Organisation"] Output = testArr.Count <pre>

This will output 1 Please note: The majority of the time you won't have to worry about it - just be aware of this when you start writing more complicated code where you are passing objects around.


Backend Databases

Matisse SQL

DateTime functions

Caution:In Matisse SQL you can use CURRENT_DATE and CURRENT_TIMESTAMP. However, they work in UTC time so will ignore British Summertime. In other words, in summer this will return the time an hour out from what you would actually expected.

MatElement

The valType on the MatElement record is from the following list:

TEXT = 1,
NUMBER = 2,
TRUE_FALSE = 3,
SELECTABLE = 4,
RELATIONSHIP = 5,
DATE_TIME = 6,
TIME_PERIOD = 7,
EMBEDDED_TYPE = 8,
ATTACHMENT = 9,
CONTENT = 10,
CALCULATED = 11,
MULTI_SELECT = 12,
ACTION_BUTTON = 13,
INFORMATION = 14,
AUTO_GENERATE = 15,
LINKED_DATA = 16,
GROUP = 17,
LINKED_COPY = 18


RTF Document Replacement

This module/script allows insertion of RTF code in a word document. Provided that you have a text in the document "##RTF##" you can now do the following:

Set docName = "C:\Users\Username\Desktop\QUO-363_Test.docx" Set replacement = "##RTF##" Set rtf = "{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang2057{\fonttbl{\f0\fswiss\fprq2\fcharset0 Calibri;}{\f1\froman\fprq2\fcharset2 Symbol;}{\f2\fnil\fcharset0 Arial;}} {\colortbl ;\red255\green0\blue0;\red0\green0\blue255;} \viewkind4\uc1\pard\ltrpar\li720\sa200\sl276\slmult1\b\f0\fs22 Supply and fit a Warrior High Security Interlock and Shop Front as attached drawings comprising:\b0\par \pard\ltrpar\fi-360\li720\sa200\sl276\slmult1\f1\'b7\tab\f0 All glass to be LPS 1270 1-2-3 (20mm).\par \pard\ltrpar\fi-360\li720\sa200\sl276\slmult1\f1\'b7\tab\f0 Solid infills to be 6mm stainless steel. Powder Coated in any available standard RAL colour. Security fixings throughout.\par \pard\ltrpar\fi-360\li720\sa200\sl276\slmult1\f1\'b7\tab\f0 Outer door with high security three point lock, cylinder, cylinder guard, heavy duty door closer and full height stainless steel security hinge.\par \f1\'b7\tab\f0 Interlock side screen.\par \f1\'b7\tab\f0 Inner door with high security three point lock, cylinder, cylinder guard, heavy duty door closer and full height stainless steel security hinge.\par \pard\ltrpar\li720\sa200\sl276\slmult1\cf1\b Repairs to plaster work to be done by others\par \pard\ltrpar\cf2\b0\f2\fs20\par }"

Set args = {} Add docName To args Add replacement To args Add rtf To args Set text = Do Run Module Params Name = "WordManager_Replace_RTF", Args = args Output = text Return_Ok