GM-X ERP for Blockchain - The ALERT subsystem

By Tony Marston

29th October 2019
Amended: 6th March 2023

Introduction
1. Database tables
ALERT table
ALERT_DETAIL table
2. Relationships with other tables
3. File Attachments
3.1 getAlertData() method
3.2 showAlertPaperClip() method
3.3 Remove unwanted navigation buttons
3.4 Run getAlertData()
3.5 Passing key details to ALERT screens
3.6 Adding an automatic attachment
4. Signed Approvals
4.1 Authorisation Workflow
4.2 Activating the starting task
4.3 Run starting task
4.4 Change document status
Amendment History

Introduction

This subsystem is used provide a standard mechanism for dealing with document approvals and/or attachments which can be used with any document in any GM-X subsystem. Each attachment may be a file of any type. Note that an alert may be raised for attachments only, for approval only, or both attachments and approval.

1. Database tables

Figure 1 - Alerts

ALERT table

This holds the details of every alert.

Field	Type	Description
alertId	number	Number which is generated by the system.
message	string	Describes the purpose of the alert.
isReceived	boolean	A YES/NO switch to indicate to indicate if the document has been received. The default value is NO.
isSigned	boolean	A YES/NO switch which indicates if the document associated with this alert has been signed/approved. The default value is NO.
user_password	string	Optional. The password of the user who signed/authorised this document.
rdcaccount_id	number	This identifies the account which owns this entry. The value '1' represents the shared account while other values are for private accounts.
effectiveDate	datetime	The date and time on which this alert starts to be effective.
discontinueDate	datetime	The date and time on which this alert ceases to be effective.
case_id	number	Optional. Links to an entry on the WF_CASE (Workflow Case) table.
workitem_id	number	Optional. Links to an entry on the WF_WORKITEM (Workflow WorkItem) table.
context	string	Optional. This value is available to be passed to the Workflow engine.
object_id	string	Optional. This identifies the GM-X object (table class) for the document which is associated with this alert. It is in the format '`<subsystem>/<table>`' where `<subsystem>` identifies the subsystem and `<table>` identifies the table within that subsystem.
object_where	string	Optional. This identifies the primary key of object_id in the format of an sql WHERE clause, in the format `pkey='value'`.
isActive	Boolean	A YES/NO switch which indicates if this alert is active or not. The default value is NO.
rdcversion	number	A number that starts at 1 and which is incremented by 1 during every update.

ALERT_DETAIL table

This holds an entry for each file which needs to be associated with a document.

Field	Type	Description
alertId	number	Links to an entry on the ALERT table.
attachmentSeqId	number	A sequence number which is generated by the system. It starts at 1 and is incremented by 1 for each attachment.
language_id	string	Identifies the language for the attachment.
attachmentURL	string	The filename of the attached document in the file system. This will be stored in the alert/documents folder with a random file name which will be generated by the system as a GUID.
attachmentDescription	string	A description of the attached file.
attachmentFileName	string	The original name of the file before it was uploaded. This is the name that will be displayed to the user.
attachmentFileType	string	Identifies the file type, such as 'pdf', 'doc', 'txt' or 'jpg'.
party_id	number	Optional. Links to an entry on the PARTY table.
contact_mech_id_blockchain	number	Optional. Links to an entry on the CONTACT_MECHANISM table which identifies a node address to be used by the BLOCKCHAIN subsystem..
effectiveDate	datetime	Optional. The date and time on which this alert starts to be effective.
discontinueDate	datetime	Optional. The date and time on which this alert ceases to be effective.
case_id	number	Optional. Links to an entry on the WF_CASE (Workflow Case) table.
workitem_id	number	Optional. Links to an entry on the WF_WORKITEM (Workflow WorkItem) table.
context	string	Optional. This value is available to be passed to the Workflow engine.
object_id	string	Optional. This identifies the GM-X object (table class) for the document which is associated with this alert. It is in the format '`<subsystem>/<table>`' where `<subsystem>` identifies the subsystem and `<table>` identifies the table within that subsystem.
object_where	string	Optional. This identifies the primary key of object_id in the format of an sql WHERE clause, in the format `pkey='value'`.
isActive	Boolean	A YES/NO switch which indicates if this alert is active or not. The default value is NO.
rdcversion	number	A number that starts at 1 and which is incremented by 1 during every update.

2. Relationships with other tables

In a relational database when there are links between two tables it is normal practice for the child table in any relationship to contain one or more columns which hold the primary key of a record in the parent table. This column (or columns in the case of a compound key) is known as a foreign key. This relationship is recorded in the $child_relations array of the parent table and the $parent_relations array of its children. Each of these arrays is contained with the <table>.dict.inc file which is exported from the Data Dictionary. An alert may be raised for any document within the application either to record attachments, to allow a document to be approved, or both. Instead of forcing each child table (which stores the document details) to contain a column called alertId to act as a foreign key to the parent ALERT table a different mechanism has been devised. This is for several reasons:

Before a table could allow either attachments or approvals it would have to be amended to contain the alertId column. The data Dictionary would then have to be amended to record this relationship, and the <table>.dict.inc file for the ALERT table re-exported so that the amended data is made available to the table object.
While there may be child relations in any number of the GM-X subsystems, a particular installation of GM-X may not contain all of those subsystems, so there could be pointers to child tables which did not actually exist.
It is a requirement of the approval process that before the approval screen for a document can be displayed that the document be checked to see if it can actually be approved. This could mean that the ALERT object, being the parent in the relationship, would have to interrogate each and every child table before it found the single record with that alertId as its foreign key.

Instead of each child record having a foreign key which identifies its parent, the ALERT table contains two columns which instead allows the parent record to know the identity of the one (and only one) child table as well as the primary key for a record on that table. These two columns are:

object_id - This is in the format <subsystem>/<table> which can be passed as-is to the singleton::getinstance() function in order to create an instance of that table object.
object_where - This is in the format of a WHERE clause which can be used to read the relevant record from the object created using object_id.

This also means that any object within the GM-X application can perform a lookup on the ALERT table simply by passing the correct values in object_id and object_where. This avoids the need to have alertId as a foreign key in the object's database table.

3. File Attachments

Any number of files can be attached to an object in the GM-X database. This requires creating an entry on the ALERT table to provide a unique alertId, and an entry on the ALERT_DETAIL table for each attached file. There are several ways in which this can be done:

Run task alert_alert(add1) from a navigation button. This will allow the user to create an ALERT entry on its own without the option to upload any files. This may be subsequently followed by running task alert_alert_detail(multi4).
Run task alert_alert_detail(add7) from a navigation button. This will allow the user to create an ALERT entry with the option to upload as many files as necessary.
Run task alert_alert_detail(multi4) to show the current ALERT data and attachments for the specified object_id and object_where, and allow the data to be amended and attachments to be added or deleted.
Run task alert_alert_detail(list2) to list all attachments for the specified object_id and object_where.

All of these tasks must be run from a navigation button and will require that the values from running the getAlertData() method be available.

3.1 getAlertData() method

This method is defined within std.table.class.inc, so will be available in each object which uses the ALERT subsystem. Its purpose is to construct the object_id and object_where strings and to find out if an entry with these values exists on the ALERT table.

function getAlertData ($fieldarray, $subsys_dir=null, $object_id=null)
// get the details of any ALERT associated with this document.
{
    // if alternatives are not supplied then use the defaults
    if (empty($subsys_dir)) {
        $subsys_dir = basename(dirname($this->dirname));
    } // if
    if (empty($object_id)) {
        $object_id = $this->getClassName();
    } // if
		
    $dbobject1 = RDCsingleton::getInstance('alert/alert');
		
    $where_array['object_id']    = $subsys_dir.'/'.$object_id;
    $where_array['object_where'] = array2where_missingIsNull($fieldarray, $this->getPkeyNames());
    $where = array2where($where_array);
    $data = $dbobject1->getData($where);
    if (!empty($data)) {
        $alert = $data[0];
        $dbobject2 = RDCsingleton::getInstance('alert/alert_detail');
        $fieldarray['attachment_count'] = $dbobject2->getCount("alertid='{$alert['alertid']}'");
        $fieldarray['alertid']          = $alert['alertid'];
        $fieldarray['case_id']          = $alert['case_id'];
        $fieldarray['issigned']         = $alert['issigned'];
    } else {
        $fieldarray['attachment_count'] = 0;
        $fieldarray['alertid']          = null;
        $fieldarray['case_id']          = null;
        $fieldarray['issigned']         = null;
    } // if

    $fieldarray['object_id']    = $where_array['object_id'];
    $fieldarray['object_where'] = $where_array['object_where'];

    if ($this->initiated_from_controller AND $this->rows_per_page == 1) {
        // remove navigation buttons which do not apply
        if (empty($fieldarray['alertid'])) {
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert_detail(list2)';
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert_detail(multi4)';
        } else {
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert_detail(add7)';
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert(add1)';
        } // if
    } // if
		
    return $fieldarray;

} // getAlertData

Note here that values for object_id, object_where and alertid are always supplied, but alertid will be empty if there is no record on the ALERT table. Note also that attachment_count will indicate how many records exist on the associated ALERT_DETAIL table.

3.2 showAlertPaperClip() method

This method is defined within std.table.class.inc, so will be available in each object which uses the ALERT subsystem. It will examine each row and show a paperclip if there are any attachments. It should be run in the _cm_post_getData() method as shown in Run getAlertData().

function showAlertPaperClip ($rows)
// examine each row and show a paperclip if there are any attachments.
{
    $this->fieldspec['paperclip']['type']        = 'string';
    $this->fieldspec['paperclip']['subtype']     = 'image';
    $this->fieldspec['paperclip']['control']     = 'image';
    $this->fieldspec['paperclip']['imagewidth']  = 18;
    $this->fieldspec['paperclip']['imageheight'] = 18;
    foreach ($rows as $row => &$rowdata) {
        $rowdata = $this->getAlertData($rowdata);
        if (!empty($rowdata['alertid']) AND (!empty($rowdata['attachment_count']) AND $rowdata['attachment_count'] > 0)) {
            // attachments exist, so display icon
            $rowdata['paperclip'] = '../images/paperclip.gif';
        } else {
            $rowdata['paperclip'] = '';
        } // if
    } // foreach
    unset($rowdata);

    return $rows;

} // showAlertPaperClip

3.3 Remove unwanted navigation buttons

While there are four possible tasks for the ALERT subsystem which may be defined as navigation buttons not all of them will be applicable at the same time. This means that some of them should be removed depending on the contents of alertid. This can be done using the code shown below which can be inserted into either the _cm_formatData() method or the getAlertData() method.

    if ($this->initiated_from_controller AND $this->rows_per_page == 1) {
        // remove navigation buttons which do not apply
        if (empty($fieldarray['alertid'])) {
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert_detail(list2)';
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert_detail(multi4)';
        } else {
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert_detail(add7)';
            $GLOBALS['nav_buttons_omit'][] = 'alert_alert(add1)';
        } // if
    } // if

3.4 run getAlertData()

In order to obtain the values for object_id, object_where and alertid so that they are available after pressing a navigation button for one of the alert tasks the getAlertData() method should be run using code similar to the following in the _cm_post_getData() method:

function _cm_post_getData ($rows, &$where)
// perform custom processing after database record(s) are retrieved.
{
    $pattern_id = getPatternId();
		
    if ($this->initiated_from_controller) {
        if (preg_match('/^(LIST)/i', $pattern_id)) {
            $rows = $this->showAlertPaperClip($rows);
        } elseif (preg_match('/^(UPD|DEL|ENQ)/i', $pattern_id)) {
            if (count($rows) == 1) {
                $rows[0] = $this->getAlertData($rows[0]);
            } // if
        } // if
    } // if

    return $rows;
		
} // _cm_post_getData

This will ensure that the data is available in multi-row LIST screens as well as single-row DETAIL screens.

3.5 Passing key details to ALERT screens

By default whenever a navigation button is pressed to activate a child task the primary key of the selected occurrence(s) in the current task will be extracted and passed to that child task. However, this primary key data does not include the information that is required by the ALERT subsystem, so it will have to be made available using code such as that show below:

The _cm_getPkeyNames method is used in tasks which have multiple occurrences.

function _cm_getPkeyNames ($pkey_array, $task_id, $pattern_id)
// return the list of primary key fields in this table before the selection string
// is constructed and passed to another form.
// $pkey_array contains the current list of primary key fields.
// $task_id identifies the task to which the primary key(s) will be passed.
// $pattern_id identifies the task's pattern.
{
    // these are needed for navigation buttons for the ALERT subsystem
    if (preg_match('/alert_detail/i', $task_id)) {
        $pkey_array[] = 'alertid';
        $pkey_array[] = 'object_id';
        $pkey_array[] = 'object_where';
    } // if

    return $pkey_array;

} // _cm_getPkeyNames

The _cm_getWhere method is used in tasks which have a single occurrence.

function _cm_getWhere ($where, $task_id, $pattern_id)
// allow WHERE string to be customised before being passed to next task.
// $task_id = identity of next task.
// $pattern_id = pattern of next task.
{
    if ($this->rows_per_page == 1 AND preg_match('/^alert_/i', $task_id)) {
        $fieldarray =& $this->fieldarray;
        $where_array = array_reduce_to_named_list($fieldarray, array('alertid','object_id','object_where'));
        $where = array2where($where_array);
    } // if

    return $where;

} // _cm_getWhere

3.6 Adding an automatic attachment

It may be that you have a task which creates a file, such as a PDF or CSV, which needs to be added as an attachment on the ALERT_DETAIL table automatically, without the user having to do it manually. This can be achieved using code similar to the following:

function _cm_post_output ($filebody, $filename)
// perform any processing required after the output operation
{
    if (!empty($filebody)) {
        $fieldarray =& $this->fieldarray;
        $fieldarray = $this->getAlertData($fieldarray);
        if (empty($fieldarray['alertid'])) {
            // entry on ALERT table does not exist, so create it now
            $dbobject1 = RDCsingleton::getInstance('alert/alert');
            $alert['message'] = 'insert message text here';
            $alert = $dbobject1->insertRecord($alert);
            if (!empty($dbobject1->errors)) {
                $this->errors[$dbobject1->getClassName()] = $dbobject1->errors;
                return $filebody;
            } // if
            $fieldarray['alertid'] = $alert['alertid'];
        } // if
        // create a record on the ALERT_DETAIL table for this PDF document
        $dbobject2 = RDCsingleton::getInstance('alert/alert_detail');
        $data['alertid']               = $this->alertid;  // from alert created previously
        $data['attachmentdescription'] = 'annex.pdf';
        $data['attachmentfilename']    = 'annex';
        $data['attachmentfiletype']    = 'pdf';
        $data['file_body']             = '<- put file body here ->';
        $data = $dbobject2->insertRecord($data);
        if ($dbobject2->errors) {
            $this->errors[$dbobject2->getClassName()] = $dbobject2->errors;
            return $string;
        } // if
    } // if

    return $filebody;

} // _cm_post_output

4. Signed Approvals

If a document requires to be approved and signed by a designated person before it can be processed further it will require additional steps, including the use of the WORKFLOW subsystem, which are described below. For this to function properly the document should have status values similar to the following:

PEND - Pending
REL - Released for Approval
APPR - Approved

Each subsystem should have a Control Record which identifies if a particular document within that subsystem requires signed authorisation before it can be approved for further processing.

4.1 Authorisation Workflow

The workflow should be constructed as shown in Figure 2 below:

Figure 2 - Authorisation Workflow

This will need the following tasks in the MENU database:

Starting Task
This is the task which, when run, will start a new workflow case for the designated document. This will create (or update) an entry on the ALERT table and set the relevant values in the object_id and object_where fields so that this record can be associated with a particular document in the application.

Note that if an ALERT record has already been created to hold attachments for the same document then this will be updated.

See the following tasks for examples:
- fap_invoice_header(add4)alert
- fap_payment_batch(add4)alert
- fap_payment_header(add4)alert
When this task completes with a database COMMIT the framework will detect that the task_id has been identified as the start_task_id in a workflow, and this will cause the framework to generate a new workflow case.
Update Alert
This should be set to the pre-written task alert_case_workitem_alert(upd4). This will copy the identity of the newly created workflow case into the case_id and workitem_id fields on the ALERT table. This task has no dialog with the user, so will be activated automatically. When completed it will enable the next task in the workflow.
Approve Document
This should be set to the pre-written task alert_alert_detail(multi4)b. When run this task will first instantiate the object identified in object_id and call the _cm_validateAlert() method using object_where as its argument in order to verify that the document is in a valid state for it to be approved. If the object returns any errors the approval will be abandoned.

You may need to set the ROLE to one which has approval capabilities.

When this task is enabled in the WORKFLOW subsystem it will require some dialog with the user so it will not be activated automatically. Instead a hyperlink will appear on the Home Page in the MENU subsystem, and the task will not be activated until this hyperlink is clicked. Upon completion the hyperlink will disappear from the Home Page.

To be approved the designated person must enter their current GM-X password and press the SUBMIT button. This will then set the isSigned field to TRUE. The Workflow system will then activate the next task. In order for the system to chose arc P2 (approved) instead of P3 (rejected) you must set the following condition on the outward arc from P2:
```
Condition Field:       issigned
Condition Operator:    === (equal)
Condition Value:       1
```
Change Status to Approved
This task will have to be created specifically to change the document status to "Approved". It should use the Update 4 pattern as there is no dialog with the user. When enabled it will be run automatically, and upon completion the workflow case will be closed.

See the following tasks for examples:
- fap_invoice_header(upd4)apri
- fap_payment_batch(upd4)appr
- fap_payment_header(upd4)appr
- wrk_leave_request_notification(upd4)
Change Status to Rejected
This task will have to be created specifically to change the document status to "Rejected". It should use the Update 4 pattern as there is no dialog with the user. When enabled it will be run automatically, and upon completion the workflow case will be closed.

See the following tasks for examples:
- fap_invoice_header(upd4)rej
- fap_payment_header(upd4)rej

4.2 Activating the starting task

Whenever the document status is changed from 'Pending' to 'Released for Approval' code similar to the following will be required in order to ask for a signature before the status can be changed from 'Released for Approval' to 'Approved'.

function _cm_post_updateRecord ($fieldarray, $old_data)
// perform custom processing after database record is updated.
{
    if (array_key_exists('???_status_type_id', $this->dbchanges)) {
        if ($old_data['???_batch_status_type_id'] == 'PEND') {
            if ($fieldarray['???_batch_status_type_id'] == 'REL') {
                $fieldarray = $this->_is_alert_required($fieldarray);
            } // if
        } // if
    } // if
		
    return $fieldarray;
		
} // _cm_post_updateRecord

Note that the _is_alert_required() method will only be called when the document status is being changed from 'Pending' to 'Released for Approval'.

function _is_alert_required ($fieldarray)
// check to see if an ALERT record needs to be created.
// if it does then start another task to do so.
{
    if (!isset($this->control_data['is_signature_required']) OR !is_True($this->control_data['is_signature_required'])) {
        return $fieldarray;  // no signature required
    } // if
		
    $next['task_id'] = '<-- enter name of starting task -->';
    $next['where']   = array2where($fieldarray, $this->getPkeyNames());
    append2ScriptSequence($next);
		
    return $fieldarray;
		
} // _is_alert_required

4.3 Run the Starting Task

This task will require code similar to the following:

function _cm_pre_insertRecord ($fieldarray)
// perform custom processing before database record is inserted.
// if anything is placed in $this->errors the insert will be terminated.
{
    $pkey_array = array_reduce_to_named_list($fieldarray, $this->getPkeyNames());
		
    $dbobject = RDCsingleton::getInstance('alert/alert');
		
    $subsys_dir = basename(dirname($this->dirname));
    $alert['object_id']    = $subsys_dir.'/'.$this->getClassName();
    $alert['object_where'] = array2where($pkey_array);
		
    $alert['message'] = '<-- enter message here -->';
		
    $alert = $dbobject->insertRecord($alert);
    if ($dbobject->errors) {
        $this->errors[$dbobject->getClassName()] = $dbobject->errors;
        return $fieldarray;
    } // if
    $this->messages = array_merge($this->messages, $dbobject->messages);
		
    $fieldarray = array_reduce_to_named_list($alert, array('alertid', 'object_id', 'object_where'));
    $fieldarray = array_merge($fieldarray, $pkey_array);
    $this->save_fieldarray = $fieldarray;  // to be used in _cm_post_insertRecord() method

    return array();  // nothing to be inserted into this table
		
} // _cm_pre_insertRecord

Note that this actually inserts a record into the ALERT table and not the application table. This is achieved returning an empty array from the _cm_pre_insertRecord(). It is reinstated in the _cm_post_insertRecord() so that the relevant data can be passed to the WORKFLOW subsystem for processing.

function _cm_post_insertRecord ($fieldarray)
// perform custom processing after database record has been inserted.
{
    if (!empty($this->save_fieldarray)) {
        $fieldarray = $this->save_fieldarray;
    } // if

    return $fieldarray;

} // _cm_post_insertRecord

After this task has completed the next task in the workflow, Update ALERT, will be activated.

4.4 Change document status

This task will require code similar to the following in order to verify that the change in status is allowed:

    if ( < -- status has changed from 'REL' to 'APPR' --> )) {
        if (isset($this->control_data['is_signature_required']) AND is_True($this->control_data['is_signature_required'])) {
            $fieldarray = $this->getAlertData($fieldarray);
            if (!is_True($fieldarray['issigned'])) {
                // "Document cannot be approved without a signature"
                $error = $this->getLanguageText('e1136');
                throw new Exception($error, 1136);
            } // if
        } // if
    } // if

Note here that if the control data does not specify that a signature is required then the update will be allowed automatically, otherwise it will check that the issigned field on the ALERT record has been set to TRUE.

Date created: 29th October 2019

Amendment history:

16 May 2023	Amended Authorisation Workflow to include a choice of tasks to change the document status to either 'Approved" or 'Rejected'.
01 Nov 2022	Added the showAlertPaperClip() method.
18 May 2022	Modified the ALERT table to rename rowversionseqid to rdcversion. Modified the ALERT_DETAIL table to rename rowversionseqid to rdcversion. Modified the ALERT_DETAIL table to rename culturecode to language_id. Modified the ALERT_DETAIL table by dropping column rdcaccount_id. Revised the contents of File Attachments and Signed Approvals.