Effector 6.3 developer manual

XML reference 6.3

Effector Studio 6.3 summary

Effector Studio 6.3 manual

Effector 6.2 developer manual

XML reference 6.0

Effector Studio 3.2 summary

Effector Studio 3.2 manual

Extra functions

ProcessResultSet (PRS)

What does PRS stand for?

The abbreviation stands for the ProcessResultSet function. In practice, it means arbitrary operations carried out using the data displayed in the Grid, the input and output filters of the component, the values of the ControlPanel Controls and UserSession data. These operations can be defined in SQL stored procedures and triggered by controls with appropriate PRS settings.

The process of the PRS function:

  • In line with the configuration, it generates an XML structure from a predefined content of the Grid, which then will be saved to a database table. The filters, ControlPanel Control and UserSession values will also be stored here. Once the record has been inserted, it stores the unique ID of the table for further use (ProcessResultSetID).
  • Using the saved ProcessResultSetID, it calls the stored procedure defined at the configuration.
  • Based on the parameters passed on, the stored procedure has the possibility to perform operations on the data previously saved into the XML structure (such as modification, generation of lists, performing batch tasks, creation of campaigns, preparation of data for mail merges, etc.)
  • As an option, the stored procedure, when successfully executed, will return an arbitrary value (using a SELECT clause), which value will then be displayed. (For example: "42 new items have been successfully added to the list.")

The following example illustrates a possible operation of the PRS. Of course, we can depart from this in functionality and in operational levels allowed for by the framework. This is a uniquely defined task intended to give the reader an insight, so this is not necessarily the only way to follow – considering the fact that this function is one of the most versatile functions in Effector.

Example:

Given is a Grid displaying the list of invitees associated with the object (event) on the left-hand side. The Grid contains a ControlPanel Control (with SimpleLinkButton value of Type, so this is a button) with the following configuration:

<Control>
    <Name>AddToList</Name>
    <Type>SimpleLinkButton</Type>
    <ReferencedMinorMenu>MMPEP</ReferencedMinorMenu>
    <Screen>ScreenMarcomEvents</Screen>
    <Caption>Adding person</Caption>
    <OnButtonClick>
        <RefreshAfterAction>Component</RefreshAfterAction>
    </OnButtonClick>
</Control>

This in itself is not a PRS action yet, however, it is strictly related to the current example.

What can we see in the example?

  • The value of the Type is SimpleLinkButton, resulting in the Screen specified in the Screen being displayed when clicking on it.
  • The value of the OnButtonClick/RefreshAfterAction node is Component. This means, that once the PRS defined on the displayed screen has been executed, the grid from which the SimpleLink was called will be refreshed. Obviously, this setting is needed because the PRS action will modify the data table of this grid and we want to see the modification immediately (e.g. invitees added to the list).

Once clicking on this ControlPanel Control, the user will see the Screen displaying a Grid with a ControlPanel Control (also a button) above it, whose configuration is the following:

<Control>
    <Name>PRS_1</Name>
    <Type>ActionButton</Type>
    <Caption>Add to list</Caption>
    <OnButtonClick>
        <ActionType>ProcessResultSet</ActionType>
        <StoredProcedure>osp_prs_sp1</StoredProcedure>
        <CloseScreenAfterAction>true</CloseScreenAfterAction>
        <RefreshAfterAction>Component</RefreshAfterAction>
        <OnlySelectedRows>true</OnlySelectedRows>
    </OnButtonClick>
</Control>

As seen above, the value of the ActionType node is ProcessResultSet, and in the OnButtonClick node, all the currently available options are used:

  • StoredProcedure: The name of the SQL stored procedure to be called.
  • CloseScreenAfterAction: It regulates whether the current Screen be closed once the whole PRS process has been executed. As the current Screen has been opened via a SimpleLink call, it makes sense to close it.
  • RefreshAfterAction: The name is telling; it refers to the table being refreshed after the execution of the PRS process. In this example, this is actually an unnecessary step, as the Screen will be closed owing to the previous attribute.
  • OnlySelectedRows: It determines whether the selected data or the entire content of the current Grid be processed by the PRS. In this example, only the selected ones are needed, therefore, the value is true.

When the user clicks on the ActionButton with the „PRS_1” ID, the following will happen:

  • Generation and storing of the XML schema: Once the button is clicked, a new record is made in the system table called ProcessResultSet. This table has a Data column of XML type, where the generated XML will be stored. The content of the ProcessResultSetID field (which in this case acts as a unique ID) will be retained. Let’s look at the content of the Data field of this new record!

           <Grid>
               <Rows>
                   <OneRow>
                       <CompanyName>Company_1899</CompanyName>
                       <PeopleName>People_1932</PeopleName>
                       <Street>Street_1932</Street>
                       <Zip>1134</Zip>
                       <City>Budapest</City>
                       <Email>Email_1932</Email>
                       <Jobtitle>Chief Financial Officer</Jobtitle>
                   </OneRow>
               </Rows>
               <ornFilters>
                   <ornInFilter>
                       <ornAlias>Event</ornAlias>
                       <ornType>Parameter</ornType>
                       <ornValue>24310</ornValue>
                   </ornInFilter>
                   <ornOutFilter>
                       <ornAlias>Company_ID</ornAlias>
                       <ornType>Param</ornType>
                       <ornValue>1899</ornValue>
                   </ornOutFilter>
               </ornFilters>
               <ornPSearches>
                   <ornPSearch>
                       <ornID>CompanyText</ornID>
                       <ornValue />
                   </ornPSearch>
               </ornPSearches>
               <UserSession>
                   <UserID>11</UserID>
                   <UserName>rbasa</UserName>
               </UserSession>
           </Grid>
    

The schema is quite telling, we can notice that a single piece of record has been selected, in addition, there is one input and one output filter, a ControlPanel Control whose name indicates that it is tied to a company name but contains no value, and the basic UserSession data.

  • Using the retained ProcessResultSetID, the stored procedure specified at the configuration is being called. In the procedure, we can obtain the stored XML data with this input parameter. It makes sense to convert the XML structure to a table thus making further data handling easier. Please note that this process may take a while in case of massive amounts of data, so it is practical to use this method with records in quantities of around 10 pieces. For more robust operations, it is worthwhile to create a unique XML processing function, for which T-SQL offers numerous options.

    A draft of the stored procedure to be processed, including comments:

       SET ANSI_NULLS ON
       GO
       SET QUOTED_IDENTIFIER ON
       GO
       CREATE PROCEDURE [dbo].[osp_prs_sp1]
           @id INT
    
       AS BEGIN
           SET NOCOUNT ON
           SET ANSI_WARNINGS OFF
    
           DECLARE 
               @xml XML
    
           -- the content of the XML is assigned to a variable 
           SET @xml = (SELECT TOP 1 data FROM ProcessResultSet WITH(NOLOCK) WHERE ProcessResultSetID = @id)
    
           --the temporary table with the same schema as the return schema of the XML processing function is generated
           CREATE TABLE #tmp_xml (
               -- FIELD DEFINITIONS--
           )
    
           -- the XML data gets inserted into the generated temporary table
           -- only one out of the four stored here is needed to be run. 
           -- - according to their names each returns the particular ControlPanel Control, filter or grid, or if ending in „all” 
           -- all types at once
           INSERT INTO #tmp_xml 
           execute osp_getPrsDataFromXml_Psearch @id
           execute osp_getPrsDataFromXml_Filters @id
           execute osp_getPrsDataFromXml_Rows @id
           execute osp_getPrsDataFromXml_All @id
           -- if there is usable data, the necessary operations will be performed on them, after which an arbitrary message will be returned
           -- otherwise we will display a message also selected arbitrarily.
           IF((SELECT Count(1) FROM #tmp_xml) <> 0)
           BEGIN
               -- PERFORMING THE OPERATION --
               SELECT `The result of the operation ....`
           END
           ELSE
               SELECT `No rows are selected!`
    
           DROP TABLE #tmp_psearch
       END
    

Trick:

PRS has another useful function. There may be use cases where we must specify “head data” for a PRS operation.

For example: We want to use PRS to create a list of our customers, grouped by their company, to whom we want to send a Christmas present. In such a case, it might be necessary for the lists to contain, in addition to their content, extra data such as: company name, who and when made the list, etc.

We can do this, if we define a Screen in the ControlPanel Control from which the PRS stored procedure is called. This Screen should point to an EditForm that generates a PRS type (even with a subtype). In this case, the BusinessObject uses the ProcessResultSet table! What happens here is that the BusinessObject will do the insertion into the ProcessResultSet table once the EditForm is saved, and from this point on the system will continue working with the ID received here. All further steps are the same as above.

Example for configuration of PRS in Link

<ControlPanel>
    <Controls>
        <Control>
            <Name>save</Name>
            <Type>ActionButton</Type>
            <Caption>Save</Caption>
            <OnButtonClick>
                <ActionType>ProcessResultSet</ActionType>
                <StoredProcedure>osp_wrk_ShowAdvocacyQuestionAnswers</StoredProcedure>
                <CloseScreenAfterAction>false</CloseScreenAfterAction>
                <RefreshAfterAction>Screen</RefreshAfterAction>
            </OnButtonClick>
            <Visible type="Constant" return="boolean" default="false">false</Visible>
        </Control>
    </Controls>
</ControlPanel>

<Links>
    <Link>
        <Type>ProcessResultSet</Type>
        <ColumnName>Checked</ColumnName>
        <ProcessResultSetControl>save</ProcessResultSetControl>
    </Link>
</Links>

We use the name specified in the ProcessResultSetPSearch as the Psearch ID.

DocumentTemplateHandler

The DocumentTemplateHandler or DTH is the document template filler module built in the system. Its task is to replace various references in .doc or .docx files with specified values.

For the configuration of the function, the tables DocumentTemplate, DocumentTemplateField and DocumentTemplateFieldTemplate, and for filling the document template with data the DocumentTemplateValues table should be used.

  • In the DocumentTemplate table, the data of the template must be defined:
    • Path: The qualified path of the template file in the file system (indicating in which directory and under what name it is stored on the IIS server).
    • Name: The name of the template (it does not directly take part in the DTH operation).
    • Filename: The name of the template file without its path.
  • In the DocumentTemplateField table we must specify the references used in the template (which will be replaced by the system):
    • FieldName: The reference used in the template file. It is frequently formulated as <%field%>.
  • In the DocumentTemplateFieldTemplate table, the document template has to be associated with the references, and the method of replacing the references must also be specified:
    • TemplateID: The unique ID of the row in the DocumentTemplate table.
    • FieldID: The unique ID of the row in the DocumentTemplateField table.
    • Method: It specifies the method of replacement. Its value can be SimpleReplace, TableRow or SpecialRow. With the SimpleReplace method all instances of the reference will be replaced. The TableRow method can be used to fill a table with data. In such a case, if there are more than one instances of the reference indicated in the FieldID column of the DocumentTemplateValues table, the number of rows generated in the table in which this reference is located will be consistent with the number of instances. The SpecialRow method is rarely used. Theoretically, if there is more than one value associated with the given reference in the DocumentTemplateValues table, it creates a string separating the values by commas and the thus created string will be used by the system to replace the reference in the template. In such cases, the reference in the template must follow the <<%65 <%field%>, %>> format, whereas the DocumentTemplateField table should contain references of <%field%> format only.

The program code fulfilling the client requirements is responsible for filling the DocumentTemplateValues table with values.

The fields of the DocumentTemplateValues table:

  • TemplateFieldID: The unique ID of the reference stored in the DocumentTemplateField table (this table contains what should be replaced).
  • Value: The value itself.
  • PeopleID: Who inserted the row in the table. As to the operation of the function, it is irrelevant.
  • Time: When the insertion to the table was made. As to the operation of the function, it is irrelevant.
  • ProcessID: An important field whose value binds the data needed to fill the document. Its content is an arbitrarily generated value. If a row is already existing in the DocumentTemplateValues table for the selected value, the DTH inserts those at the beginning of the execution.

GenerateDocument

It is a document generation function constructed from the PRS and DTH functions that can be configured in the system. The data are obtained by a PRS stored procedure (that inserts data in the DocumentTemplateValues table, then at the end of the stored procedure, it returns data needed for the DTH operation). The maximum number of documents is consistent with the number of rows returned by the stored procedure.

Example for stored procedure:

CREATE PROCEDURE [dbo].[osp_prs_DTH1_grid]
    @id INT
AS 
BEGIN
    DECLARE @PrsRange INT,
            @UserID INT

    SET @PrsRange = 100000000

    DECLARE @UserSession TABLE (
        [UserID] INT,
        [UserName] VARCHAR(250),
        [Language] VARCHAR(10)
    )

    --We obtain the data needed for the generation of the document from the table. Each row will mean one document.
    DECLARE @ids TABLE (
        [TaskID] INT,
        [ShortDescription] VARCHAR(1000), 
        [Responsible] VARCHAR(1000)
    )
    INSERT INTO @ids (TaskID,ShortDescription,Responsible)
    EXECUTE [dbo].[osp_getPrsDataFromXml_Rows] @id, 'TaskID,ShortDescription,Responsible'

    --We obtain the value of the UserID of the user to insert it to the `DocumentTemplateValues` table.
    INSERT INTO @UserSession
    EXECUTE [dbo].[osp_getPrsDataFromXml_UserSession] @id

    SELECT @UserID = (SELECT TOP 1 [UserID] FROM @UserSession)

    --We delete all previous data. (ProcessID!)
    DELETE FROM DocumentTemplateValues 
    WHERE ProcessID IN (SELECT @PrsRange + (@id * 1000) + ROW_NUMBER() OVER (ORDER BY TaskID) FROM @ids) 

    --Here we insert all data in the `DocumentTemplateValues` table, the ID of the DocumentTemplate is 1.
    INSERT INTO DocumentTemplateValues (TemplateFieldID, Value, PeopleID, [Time], ProcessID)
    SELECT dtf.TemplateFieldID, Value, @UserID, GETDATE(), @PrsRange + (@id * 1000) + RowNo
    FROM
    (
        SELECT ROW_NUMBER() OVER (ORDER BY i.TaskID) AS RowNo,  CAST(i.TaskID AS VARCHAR(1000)) AS TaskID, CAST(i.ShortDescription AS VARCHAR(1000)) AS ShortDescription, CAST(i.Responsible AS VARCHAR(1000)) AS Responsible
        FROM @ids i
    )
    AS Result
    UNPIVOT
    (
        Value FOR FieldName IN (TaskID, ShortDescription, Responsible)
    )
    AS UnPvt
    INNER JOIN DocumentTemplateField dtf on dtf.FieldName = '<%'+ UnPvt.FieldName + '%>'
    INNER JOIN [dbo].[DocumentTemplateFieldTemplate] dtft on dtft.FieldID = dtf.TemplateFieldID
    WHERE dtft.TemplateID = 1

    -- We hand the generation over to the GenetateDocument function
    SELECT CAST(1 AS BIT) AS Generate, 
        1 AS DocumentTemplateID, 
        @PrsRange + (@id * 1000) + ROW_NUMBER() OVER (ORDER BY TaskID)  AS ProcessID, 
        'D:\DTH1\' + CAST(@PrsRange + (@id * 1000) + ROW_NUMBER() OVER (ORDER BY TaskID)  AS VARCHAR(1000)) + '.docx' AS OutputFileName, 
        'A(z) #' + CAST(TaskID AS VARCHAR(20)) + ' generate document for the task... {0}' AS Message,
        CAST(1 AS bit) AS CreateDocumentObject, 
        'Link' AS CreateDocumentObject_ObjectType, 
        0 AS AttachDocument_BoID, 
        '' AS AttachDocument_BoType,
        'MMDokumentumtar' AS ReferencedMinorMenu, 
        'ScreenDocumentEdit' AS Screen, 
        '[{ "Alias": "Document_ID", "Type": "Out", "Value" : [##DocumentID##]}]'  AS FilterValues,

        'FileInterface?p=[##FileInterfaceID##]&a=download' AS RedirectURL, 
        '_blank' AS RedirectTarget,
        'Test' AS SubDirectory

    FROM @ids
END

The stored procedure must return with the following columns:

  • DocumentTemplateID: the unique ID of the DocumentTemplate table, that is, where the data of the tamplete are obtained from.
  • ProcessID: the ProcessID of the given task in the DocumentTemplateValues (this combines the field values of the given document generation task)
  • OutputFileName: the name of the generated file and its location in the system. The file extension defines the format of the result. Currently the following formats are supported: .pdf, .doc, .docx, .rtf.
  • Generate: This is a BIT column, and if its value is 1, the given row will be generated by the system.

The system will also consider the following:

  • For the creation of Document rows (in such cases, the system considers the values of TargetPathTemplate and TargetFileNameTemplate specified in the DocumentStoreSettings.xml file relevant to the given document type):
    • CreateDocumentObject: A BIT type field. If its value is 1, it generates the row in the Document table (and moves the file to its final location).
    • CreateDocumentObject_ObjectType: The type of the Document BusinessObject.
    • AttachDocument_BoID: What the document will be attached to (can be empty).
    • AttachDocument_BoType: What type of objects the document will be attached to (Event, Project, etc.). Its value can be empty.
  • Using the following columns, we can specify which Screen with what filter values be opened by Effector following a successful generation:
    • ReferencedMinorMenu: The name of the MinorMenu to be opened.
    • Screen: The name of the Screen to be opened.
    • FilterValues: The infilters needed for the window opened in JSON format. All instances of the [##DocumentID##] reference will be replaced with the Documents table ID of the generated row.
  • Message: The message returned at the end of the generation. In case of a successful generation, the system replaces the reference {0} with the term “Ok”.
  • Using the following columns, we can specify the address to which Effector is to navigate after a successful generation:
    • RedirectURL: The URL itself, in which the reference [##DocumentID##] – if any – will be replaced with the Documents table ID of the generated row.
    • RedirectTarget: Its value can be _blank (open in new window) or top (open in place of Effector).
  • FailedMessage: A message displayed when the document generation fails; the system replaces the reference {0} with the reference number of the error.
  • BodyHtml: In this case, the bases of the document will be this HTML text.
  • SkipDocumentCleanUp: Retain placeholders for values which was not found during document filling. The value can be true orfalse, the default value is false.
  • SourceDocumentID: The document fill will not start from a template file but from a document line. Its value is the DocumentID field of the Document table.

The values of all other columns can be used as values of the reference specified in the DocumentStoreSettings setting relevant to the given document type. More on this is the Document management section.

Example for configuration:

<Control>
    <Name>DocGen</Name>
    <Type>ActionButton</Type>
    <Caption>Add to list</Caption>
    <OnButtonClick>
        <ActionType>GenerateDocument</ActionType>
        <StoredProcedure>osp_prs_DTH1_grid</StoredProcedure>
        <CloseScreenAfterAction>false</CloseScreenAfterAction>
        <RefreshAfterAction>Component</RefreshAfterAction>
        <OnlySelectedRows>true</OnlySelectedRows>
    </OnButtonClick>
</Control>

Getting rid of viruses

The system can use ClamAV. This function can be turned on and off and configured in the web.config file. For its configuration, the following appSettings keys can be used:

  • If the clamav setting is true, the function is turned on.
  • The clamav_server and clamav_port settings define the server details.

Example

<add key="clamav" value="false" />
<add key="clamav_server" value="localhost" />
<add key="clamav_port" value="3310" />

  • Last update: 2 weeks 5 days ago
  • Effector