RSC SQL Automation Engine

RSC Sync Engine / SQL Automation Engine rev 2.231

RSC SQL Watch Engine 1.14

Table Of Contents

Overview

The RSC SQL Automation Engine is a standalone executable that performs companion tasks for ARCHIBUS. The program runs as a service and uses a 32bit ODBC connection to talk to the Archibus database. It also can support a secondary (External) database that does not need to be Archibus. There are three main sections within the SQL Automation Engine

The SQL Automation Engine executes user defined SQL statements on a schedule. A task can be one or many statements

The Sync Engine performs tasks from as set of built-in functions. A common use for the Sync Engine is to perform complex data imports and exports such as an employee synchronization. The Sync Engine also runs from a scheduler.

The Email Engine allows for complex data driven email events. These can be a single email per records or a single email that includes multiple records.

The Engine runs as a Windows service. Multiple instances of the SQL Automation can be added to service other Archibus databases. The Engine connects to the Archibus project database through a 32 bit System ODBC entry.

The engine writes all errors to a log file (RSC_SQL_AUTO_LOG.TXT). Errors can also be automatically emailed to a support person.

Installation and Configuration

Program Installation

Unzip RSCSQLAUTO.zip to create the application folder e.g. D:\APPS\RSCSQLAUTO
Create a 32 bit System ODBC entry named RSCSQLAUTO. The ODBC name can be different but it must match the “DSN=” value in the \RSCSQLAUTO\RSCSQLAUTO.INI file.
Open and alter (with Notepad) the “UID=” and “PWD=” values in the \RSCSQLAUTO\RSCSQLAUTO.INI file if the username and password are not default.
Copy RSCSQLAUTO\system32\mswinsck.ocx and vbsendmail.dll to the c:\windows\syswow64 folder.
Open a command prompt as Administrator and run Register_Dlls.bat.

Archibus Schema Installation

The SQL Automation Engine stores sync and process scripts as data in custom tables. These must be added to the Archibus project database. The following tasks require the Archibus Client Server program.

Open the Archibus project database. Use File – Data Transfer Import.
Import \Schema_Install\schema.xml.
Run the Update Schema Wizard to update the tables listed in tab_list_update.txt
Run File – Data Transfer Import. Import \Sample_Data\sample_data.xml.
Open ARCHIBUS/FM and the project database.

Archibus Client Server Views

The SQL Automation includes and interface for SmartClient, Web Central and Client Server. Perform the following to use the Client Server interface.

Copy the contents of the folder under CS_Views to a folder under the Client Server Schema filder e.g. \SCHEMA\RSCSQLAUTO

Starting of the SQL Automation Engine for the First Time

Initially the SQL Automation Engine is run in the foreground. On the first run it will automatically set parameters in the RSCSQLAUTO.INI file and will create two services. PLEASE NOTE that the 32 bit System ODBC entry must be present.

From File Exporer browse to the application folder e.g. \APPS\RSCSQLAUTO, Right mouse click on RSCSQLAuto.exe and choose Run as administrator. After a few seconds the application should present a message about the services being installed and then terminate. If nothing happens then review the log file \APPS\RSCSQLAUTO\RSCSQLAUTO_LOG.txt.
From the Windows Services application start the services named:
- RSC_Service_Manager (RSCSQLAUTO)
- RSC_Service_Manager_Watch (RSCSQLAUTO)
Check RSCSQLAUTO_LOG.txt RSCSQLWATCH_LOG.txt for any errors
The SQL Automation Engine requires access to an ougoing SMTP email server in order to support automated emails. The email parameters are set the the RSCSQLAUTO.INI file (in the application folder). The required parameters are:
- SMTP_HOST=
- SMTP_PORT=
- SENDER_EMAIL=
- ERROR_EMAIL=

The following is a list of all supported configuration parameters:

[RSCSQLAUTO] ;Name of the executable
SERVICE_NAME=RSC_SERVICE_MANAGER ; Required for SQLWatch service
PATH=D:\CAFM|APPS\RSCSQLAUTO ;Path where the application is installed
LOG_FILE_DIR= D:\CAFM|APPS\RSCSQLAUTO ;Path where the log file resides
TIMERINTERVAL=1 ;interval for processing (minutes)
DEBUG=0 ;default debug parameter
;DEBUG=1 ;verbose debug that echos all SQL statements
LOG_NAME=N ;Log heartbeat and errors to the log file
;LOG_NAME=Y ;Log heartbeat and errors to the process log table (rsc_proc_log)
CYCLE_CHECK=60 ;Elapsed time of inactivity before the SQL Watch Engine emails a warning
DSN=RSCSQLAUTO ;Name of the 32 bit System ODBC entry pointing to the Archibus database
UID=afm ;Username to attach to the database
PWD=afm ;Password to attach to the database
PWD_ENCRYPT= ;Place password in this field. It will be encypted on Service Restart
TEST_SQL=SELECT table_name FROM afm_tbls WHERE table_name = ‘bl’ ;Start test statement
;EXTERNAL_DSN=RSC_Access ;32 bit System ODBC entry pointing to the external database
;EXTERNAL_UID=uid ;External database username
;EXTERNAL_PWD=pwd ;External database password
;EXTERNAL_TEST_SQL=SELECT GroupID FROM Share ;Start test statement
;EXTERNAL_IS_AFM_DB=0 ;0 if external database is not Archibus, 1 if it is Archibus
;The following parameters are required only for email notification of processing errors
;SMTP_HOST=127.0.0.1 ;SMTP server for processing emails
;SMTP_PORT=25 ;SMTP port
;AUTHENTICATION=1 ;If the SMTP server requires authentication
;POP_AUTHENTICATION=1 ;If the SMTP server requires ‘POP’ first authentication ;POP_USERNAME=username
;Username for required SMTP authentication
;POP_PASSWORD=password ;Password for required SMTP authentication
;SENDER_EMAIL=user@company.mail ;From email address for sending emails
;ERROR_EMAIL=tech@company.mail ;Email address to echo processing errors to [optional]
;ERROR_EMAIL2=tech2@company.mail ;Second email address to echo processing errors to
;ERROR_EMAIL3=tech3@company.mail ;Third email address to echo processing errors to
;ERROR_EMAIL_SUBJECT=MicroView FM Automated Work Processor Error
HTML_EMAIL=1 ;HTML Emails enabled for custom email notifications
EMAIL_ATTACHMENTS=D:\CAFM\AFMDATA\EMAIL_ATTACHMENTS; Folder to store email body convertedto an attachment (if too large)
;DEMO_EMAIL=jsmith@rsc2lc.com ; If enabled all emails are routed to single address

Uninstalling the SQL Automation Services

The service can be uninstalled if required:

Open a command prompt in the application folder
Type the following line then press Enter:
- instsrv.exe “RSC_Service_Manager (RSCSQLAUTO)” remove
- instsrv.exe “RSC_Service_Manager_Watch (RSCSQLAUTO)” remove

Installation of The Smart Client and Web Central Interface

Optionally you can install the SmartClient and Web Central interface. By default these are assigned to the user AFM.

The source files are located under Web_Central_Install:

Copy contents of the ab-products folder under \archibus\schema\ab-products
Copy the contents of the lib folder to \archibus\WEB-INF\lib
Copy ab-icon-rsc.png from the graphics folder to \schema\ab-core\graphics\icons\nav\activities and \schema\ab-core\graphics\icons\nav\domains
In Archibus Client Server use File – Data Transfer Import. Import \Web_Central_Install\web_central.xml.
Restart Web Central.
Login To Web Central or SmartClient as User AFM (Activities are assigned to user AFM by default)

Configuration and Testing

In Archibus Client Server and Web Central the SQL Automation interface is under the RSC Domain.

In Archibus Client Server the SQL Automation interface is under System – Application – Processes & Roles.

SQL Tasks

The RSC SQL Automation Engine allows SQL statements to be run on a scheduled basis. If a single statement fails to execute (after 3 attempts) then an email is sent to the ERROR_EMAIL and ERROR_EMAIL2 addresses listed in the RSCSQLAUTO.INI file. On failure the routine moves to the next SQL statement and documents the failure in the Error Log table.

Configure SQL Tasks and Statements

SQL Statements are linked to and scheduled by a SQL Task. First create a SQL Task:

The Date To Run and Time To Run fields define when the event will run.
Is Enabled must be set to Yes for the event to run.
If the events are scheduled to run at the same time then they are queued up via the Task Order.
Once an event runs the Last Run field displays the compellation date and time.
The Run Selected Task View Action (Client Server only) will run the event via an ABS file. This is handy for debugging SQL Errors.

TIP: Blanking out the Last Run field will trigger an event again (assuming that it is past the scheduled time to run for that day.

One or more SQL Statements can belong to SQL Task

Add the SQL statement and give it a unique Task Step number.

Running Events

Once enabled, an event will run when the next cycle fires. The default cycle time is 1 minute and is set via the TIMERINTERVAL value in the RSCSQLAUTO.INI file. The shortest cycle time is 30 seconds (.5 minutes). The Last Run field will display the timestamp when the event completed.

Error Log

Recoverable and non-recoverable errors are logged to the RSC_SQL_ERROR table. These errors are also emailed. The Error Log table can be displayed under Administrative Functions in Archibus Client Server or Web Central.

Debugging Errors

The Error Log displays a record for each failure running a SQL statement. The error and the original SQL statement are displayed. An error email is also sent to the ERROR_EMAIL and ERROR_EMAIL2 addresses listed in the RSCSQLAUTO.INI file (C:\WINDOWS).

If the SQL Engine does not appear to be functioning then:

Check the RSC_SQL_AUTO_LOG.TXT file in the application install folder. This will indicate any errors or the last time the engine cycled.
If nothing appears to be happening then restart the SQL Automation Engine service. The SQL Automation Engine can be stopped and restarted in the Service Status view by setting the current command. Commands are run once per minute. Refresh the view to see the Current Status.

Sync Tasks

The RSC Sync Engine allows scheduled and automated multi-step sync processes to be executed. All of the workflow is user definable in Archibus tables. If a single statement fails to execute then it will retry forever (as the most common error is database connection issues. On failure an email is sent to the ERROR_EMAIL and ERROR_EMAIL2 addresses listed in the RSCSQLAUTO.INI file (C:\WINDOWS). On recovery another email is generated. In the Error events are also documented in the RSC_SQL_AUTO_LOG.TXT file.

The RSC interface contains links to the configuration tables and functions.

Master Functions

Master Functions item is the top-level definition for sync events. All Functions belong to a Master Functions.

The Date To Run and Time To Run fields define when the event will run.
Is Enabled must be set to Yes for the event to run.
Once an event runs the Last Run field displays the compellation date and time.
Once an event runs the Sync Log displays the results from each Function.
Email Address 1 and Email Address 2 allow users to be emailed with the results of a successful event.

TIP: Blanking out the Last Run field will trigger an event again (assuming that it is past the scheduled time to run for that day.

Functions

The Functions table defines tasks that belong to the Master Function.

Function must have a unique name and belong to a Master Function.

Is Enabled must be set to Yes for the function to be included when the Master Function event runs.

Function Type choices are presented in a list. Default if Fields. Special Functions require a specific Function Name.

Import Excel – Import Data from an existing Excel file to the “Table Name To” table.

Requires an “Import File Name” value, “Row Start”, “Column Start”, and “Number of Columns” value.
Uses “Excel Sheet” or the Active Sheet if NULL.
Normally Excel files are read into the RSC_EMSOURCE table

NOTE: If the Import fails with a cannot find XXX file and the server is Windows 2008 or later then:

Create a folder on the server c:\windows\syswow64\config\systemprofile\desktop
Import ASCII – Import Data from an existing ASCIl file to the “Table Name To” table. Requires:
- Import File Name e.g. D:\Transfer\Syncs\EMSOURCE.CSV
- Field Delimiter e.g. “,”Row Start e.g. 2

Column Start e.g. 1

– Normally ASCII files are read into the RSC_EMSOURCE table

or any table where the field names are field1, field2, field3,field4 etc.

– Field Delimiter converts to the ASCII character if set as an integer. Eg. 9 = Tab.

– Special Function Variable 1: Record Delimiter

If null then defaults to LF. Other options are CR, LF, CRLF

– Special Function Variable 2: Fields encased in double quotes Y or N

If null then defaults to Yes.

Import XML – Import Data (eg. XML or JSON) from an existing XML file to the “Table Name To”

Requires:

Import File Name

Field Delimiter

Special Function Variable1,

Special Function Variable 2

-Field Delimiter: If “L” then it assumes a LineFeed character between records.

If null then it assume LineFeed – CarriageReturn.

– Special Function Variable 1: record start parameter e.g. <wd:Report_Entry>

– Special Function Variable 2: record end parameter e.g. </wd:Report_Entry>

– Special Function Variable 3: field value start character. Defaults to “<“

– Special Function Variable 4: field value end character. Defaults to “>”

– Normally data is read into the RSC_EMSOURCE table

– Record start and end supports special characters (var1, var2)

{CRLF} {CR} {LF} {TAB}

Import File Name supports wildcards. It can import from multiple files

e.g. D:\CAFM\DOWNLOADS\PHOTOS\*.XML

Export ASCII – Export Data from “Table Name From” to an ASCII (delimited file.

– Function Type = “Export ASCII”

– Function View Required Values:

– Function Name (The same function name is referenced in the Sync Fields view).

– Table Name From – Table to export data from.

– Import / Export File Name – Directory and file name to export to.

– Field Delimiter – Delimiter character between fields of data. Default is a comma.

Converts to the ASCII character if set as an integer. Eg. 9 = Tab.

– Special Function Variable 1 – Restriction to the source table. E.g. “bl_id IS NOT NULL”

– Special Function Variable 2 – Character to surround character fields. E.g. double quoted strings

– Special Function Variable 3 – Header line.

Set to “Y” single character is you want the first record to list the fields

– Sync Fields Required Values (Master Function Name and Function Name match the

Function View: Table Name From

Field Name From

Field Order

Export XML – Export Data from “Table Name From” to an XML formatted file.

– Function Type = “Export XML”

– Function View Required Values:

Function Name (The same function name is referenced in the Sync Fields view).

Table Name From – Table to export data from.

From Table Restrict – Restriction in source table (optional)

Skip If Table Empty – Name of source table to skip if empty

File Prefix Text – XML parameters prior to record data.

Can use character formatting (see below)

File Suffix Text – XML parameters after record data.

Can use character formatting (see below)

– Sync Fields Required Values (Master Function Name and Function Name match the

Function View: Table Name From

Field Name From

Field Order

Field Prefix Text – XML parameters prior to field value.

Can use character formatting (see below)

Field Suffix Text – XML parameters after field value.

Can use character formatting (see below)

– Special character support for File and Field text (prefix / suffix)

{CR} = Chr$(13) Carriage Return

{LF} = Chr$(10) Line Feed

{TAB} = Chr$(9) Tab

Export Crystal – Run Crystal Report Report (RPT) files. Export to formats including PDF.

– Requires CREXPORT.EXE program in the RSCSQLAUTO folder

– Function Type = “Export CR”

– Will email the attachment (Requires variable 4 and 5)

– Function View Required Values:

Special Function Variable 1 – Full path and name of source RPT file.

e.g. D:\CAFM\TEMP\GENERIC_01_BLDGS_SUM.rpt

Special Function Variable 2 – Full path and name of output file.

Supports embedded date and time variable

e.g. D:\CAFM\TEMP\<yyyy_mmm>\GENERIC_01_BLDGS_SUM_<dd>.pdf

Will automatically create one level of directory.

Special Function Variable 3 – Output type. Values include: “pdf” ,”doc”,”xls”,”xlsdata”

Special Function Variable 4 – Prototype Email Type.

Must exist in Prototype Emails table

Defines the Email Subject and Email Body.

Special Function Variable 5 – Email To – Email Address to send to.

Special Function Variable 6 – Email CC List – Email Address to CC

Semi-colon separated list.

NOTE: .Net Runtime 4.0 and the Crystal Runtime for .Net

(CRforVS_redist_install_64bit_13_0_5.zip) is required.

NOTE: .If you use the 64 bit Crystal Runtime then it will use 64 bit ODBC.

A 64 bit ODBC entry named RSCSQLAUTO will be required.

The 64 bit ODBC MUST be the same name as the 32 bit ODBC.

Export JSON – Export Data from “Table Name From” to an JSON formatted file (UTF-8 format).

– Function Type = “Export JSON”

– Function View Required Values:

Function Name (The same function name is referenced in the Sync Fields view).

Table Name From – Table to export data from.

From Table Restrict – Restriction in source table (optional)

Skip If Table Empty – Name of source table to skip if empty

File Prefix Text – JSON parameters prior to record data.

Can use character formatting (see below)

File Suffix Text – JSON parameters after record data.

Can use character formatting (see below)

– Import / Export File Name

– Sync Fields Required Values (Master Function Name and Function

Name match the Function View:

Table Name From

Field Name From

Field Order

Field Prefix Text – JSON parameters prior to field value.

Can use character formatting (see below)

Field Suffix Text – JSON parameters after field value.

Can use character formatting (see below)

– Special character support for File and Field text (prefix / suffix)

{CR} = Chr$(13) Carriage Return

{LF} = Chr$(10) Line Feed

{TAB} = Chr$(9) Tab

– “Calculated Field Value”

<CURRENTDATE> Returns the current date.

<TIMESTAMP> Current date and time.

<RTRIM> Validates against the Field Value Translations table (see below)

<UCASE> Returns the result converted to uppercase

<LCASE> Returns the result converted to uppercase

<TRANS> Translate from the rsc_translation table

Fields – Copy data between “Table Name From” and “Table Name To” tables

– Assumes that the tables have only a single primary key field

– “Primary Key Field From 1” and “Primary Key Field To 1” must be defined.

– Supports 1 or 2 Primary key fields to the “To Table”

– Special Function Variable 1 can hold an option SQL statement to define the read from

the From Table(s)

eg. SELECT em.em_id,em.em_std,emstd.description FROM em,emstd

WHERE em.em_std = emstd.em_std

– NOTE: If the SQL pull is defined by SQL Function Variable 1 then the SQL Fields

Calculated Field value syntax is different.

e.g. SELECT pi_split_id FROM rsc_pi_split WHERE pi_em_id = <EMPLOYEE>

Variables are evaluated from the source recordset e.g. <EMPLOYEE>.

– The Sync Fields table defines which fields will be transferred and other business rules.

Archive – Copies all fields between “Table Name From” and “Table Name To” tables.

– If fields are missing from the “Table Name To” table then they will be skipped.

– Records can be filtered via the “From Table Restrict” value.

– Functions run in numeric order defined by “Processing Order”.

– “Data Source From / To” Supports two separate database sources either being

Primary or Secondary.

– If using an External datasource then the following parameters are required in the RSCSQLAUTO.INI

EXTERNAL_DSN=dsn_name

EXTERNAL_UID=username

EXTERNAL_PWD=password

EXTERNAL_TEST_SQL=

SELECT table_name FROM afm_tbls WHERE table_name = ‘bl’

EXTERNAL_IS_AFM_DB=1 (0 if it is not an Archibus database

– “Table Name From” is the table from which data will be copied from.

This is not required form “Function Type” = Import.

– “Primary Key Field From 1” is the first primary key field in the from table

(currently only a single Primary key field is supported).

This is required only for “Function Type” = Fields.

– “Table Name To” is the table from which data will be copied to.

This is required for all “Function Types”.

– “Primary Key Field To 1” is the first primary key field in the to table

(currently only a single Primary key field is supported).

This is required only for “Function Type” = Fields.

– The “From Table Restrict” restricts records chosen from the “From Table”.

Does not apply to “Function Type” = Import Excel.

For Fields Functions this supports embedded SQL evaluated to form the restriction,

[] surrounding with square bracket evaluates unquoted.(number)

{} surrounding with braces evaluates quotes (string)

Example :ID > [SELECT variable_value_int FROM rsc_variable_store

WHERE variable_name = ‘var1’]

– “Update Non Mod Records” is a flag that enables or disables updating records that have not changed.

If set to No then at least one field in the Sync Fields view must have “Check for Modification” = Yes.

If set to No then the “Update When Non Mod” field must contain a field name and value

(valid SQL format) to set when a record is not modified.

This ends up being the only change to the record.

Normally this is used to set the UPDATE_TYPE field.

– Skip If Table Empty. Skips running the function if the entered table name contains no records. This prevents A function from running if a previous function did not run. If the table name is prefixed with NOT_EMPTY e.g. “NOT_EMPTY rsc_emsource” then the function is skipped

if the table is not empty.

– “Validation Notes Field” is the field in the “Table Name To” where validation errors are written

– “Import File Name” is the full path and file name for a source Excel file.

This is required only for “Function Type” = Import Excel.

– “Excel Sheet name” is the Excel file sheet name.

This is required only for “Function Type” = Import Excel.

– “Excel Row Start” is the starting row where the data is read from.

This is required only for “Function Type” = Import Excel.

– “Excel Column Start” is the starting column where the data is read from.

This is required only for “Function Type” = Import Excel.

– “Excel Number of Columns” is the number of columns of data to read.

This is required only for “Function Type” = Import Excel.

Archive By Record

– Same as the Archive function but it copies one record at a time to support multiple data sources.

– Copies all fields between “Table Name From” and “Table Name To” tables.

– If fields are missing from the “Table Name To” table then they will be skipped.

– Records can be filtered via the “From Table Restrict” value.

– Same parameters as the Archive function

While Loop – Routine to select a data set and then perform up to 5 SQL statement for each record selected.

– Function Name – Specified name for this function

– Function Type – While Loop

– Function Variable1 – holds the SELECT Statement

e.g. SELECT wr_id, description FROM activity_log WHERE rsc_email_status = 0

– Function Variable 2 through 6 holds the UPDATE or INSERT statement(s) to process

for each record selected

– <name> returms value literalized

UPDATE wr SET comments = <comments> WHERE wr_id = 13

evaluates as:

UPDATE wr SET comments = ‘This is a comment’ WHERE wr_id = 13

– {name} returns value NOT literalized

e.g. INSERT INTO wr (wr_id, description, rsc_email_status) VALUES ({wr_id},<description>,1)

evaluates as:

INSERT INTO wr (wr_id, description, rsc_email_status) VALUES (13,’Help Me’,1)

– [<name>] Replace NULL with IS NULL or = value in a WHERE clause

e.g. WHERE eq_category [<eq_cat>]

evaluates as:

WHERE eq_category = ‘DOOR’ or WHERE eq_category IS NULL

– .<> characters not allowed in UPDATE or INSERT statement(s). Replace with:

.GT. >

.LT. <

.GE. >=

.LE. <=

.NE. <>

e.g. SELECT eq_id FROM eq where date_installed .GE. ‘2018/01/01’

evaluates as:

SELECT eq_id FROM eq where date_installed >= ‘2018/01/01’

– Datasource From and Datasource To can be different

Hatch Assign – Apply hatch patterns (hpattern_acad field)

This routine utilizes a patter and color palette defined in source tables

RSC_COLORS – 255 color palette. Enabled and disable as required

RSC_HATCHES – Hatch pattern palette to use. Enable and disable as required.

– Edit swatch to change pattern scale.

– Function Name – Specified name for this Function

– Function Type – Hatch Assign

Function Variable1 – Table containing hpattern_acad to update e.g. dp

Function Variable2 – Table referencing the hpattern_acad field e.g. rm

Function Variable3 -Type of update to perform

– overwrite – Remove and add pattern values for all referenced patterns

– append – Add pattern values for all referenced patterns that are currently blank.

– Current patterns assigned are not altered.

– backfill – Add patterns values for all unreferenced patterns that are currently blank.

– Normally run after running with update_type = overwrite

Function Variable4 – Pattern_type – Source patterns as either solid colors or hatch patterns.

Patterns and colors can be enabled or disabled from the rsc_colors and rsc_hatches table.

– solid – solid colors only

– hatch – hatches only

– both – Both solid colors and hatches. Solid colors are used first.

Function Variable5 – Clear Ins Use. Flag to clear rsc_patterns_in_use flag

– yes – sets rsc_patterns.in_use to 0 (Yes) Do this for the first pass for the first reference_table

– no – does not alter rsc_patterns.in_use Do this for subsequent runs for other referencing tables

e.g. rm (yes) then rmpct (no) then em (no)

Sample Use

One Time

HatchAssign (“dp”, “rm”, “overwrite”, “both”, “yes”)

Every Day

HatchAssign (“dp”, “rm”, “append”, “both”, “yes”)

HatchAssign (“dp”, “rmpct”, “append”, “both”, “no”)

HatchAssign (“dp”, “em”, “append”, “both”, “no”)

Run PHP – Run External PHP function (or any executable).

Waits for a log file to be written and returns the log file contents to the Sync Log.

Optionally it can email if the string “Error” is found in the log.

– Function Name – Specified name for this function

– Function Type – Run PHP

– Number of Columns = timeout waiting for log file (minutes) = 1

– Function Variable 1 = executable_dir = D:\CAFM\inetpub\wwwroot\spaceview_dev\client\workday

– Function Variable 2 = executable = c:\php\php.exe sendToWorkDay.php em all

– Function Variable 3 = log_file = wd_push.txt

– Function Variable 4 (optional) = email_type = WORKDAY_PUSH_ERROR

– Function Variable5 (optional) = email_to = gdryer@webnotes.ca

– Function Variable 6 = email_cc = user1@webnotes.ca;user1@webnotes.ca

– Number of Columns = timeout = Timeout for running PHP code (minutes)

Special Functions

Special Functions are a Function Type that calls specialized routines to perform particular tasks

Special Function utilize calling variables defined in the fields labeled Special Function Variable (1 to 4)

DELETE_REF_VALUE

DELETE_REF_VALUE performs cascading deletion of records

This is required when the referencing field is part of the primary key of the table. To select the tables to applied “Special Function Variable 4”, apply the following restriction to the Archibus fields table (afm_flds)

Validating Table = table name e.g. Em

AND Primary Key > 0

AND Validate Data? = Yes

Required Variables:

Function Name – DELETE_REF_VALUE

Function Type – Special Function

Special Function Variable 1 – Restriction that defines the records to remove.

This SQL statement must retrieve a single field value.

Special Function Variable 2 – Parent table name.

Special Function Variable 3 – Parent table field name. This must be the primary key of the parent table.

Special Function Variable 4 – A list of child tables to exclude. Format ‘table1′,’table2′,’table3’.

Special Function Variable 4 – % Threshhold for deletion of records.

Prevents deletion of records is the records in restriction

exceeds the total number of records. Set to 100 to allow all records to be deleted.

This is required when the referencing field is part of the primary key of the table, To search for tables apply the following restriction to the Archibus fields table (afm_flds)

Validating Table = table name e.g. Em

AND Primary Key > 0

AND Validate Data? = Yes

DELETE_REF_VALUE_2KEY – Performs cascading deletion of records from a source table with a 2 part primary key.

Validating Table = table name e.g. Em

AND Primary Key > 0

AND Validate Data? = Yes

Required Variables:

Function Name – DELETE_REF_VALUE_2KEY

Function Type – Special Function

Special Function Variable 1 – Restriction that defines the records to remove.

This SQL statement must retrieve both parts of the primary key.

Special Function Variable 2 – Parent table name.

Special Function Variable 3 – Parent table primary key 1 field name.

Special Function Variable 4 – Parent table primary key 2 field name.

Special Function Variable 5 – A list of child tables to exclude. Format ‘table1′,’table2′,’table3’.

Special Function Variable 6 – % Threshhold for deletion of records.

Prevents deletion of records is the records in restriction

exceeds the total number of records. Set to 100 to allow all records to be deleted.

UPDATE_REF_VALUE

UPDATE_REF_VALUE performs cascading updates of records

An example of the application of this function would be to rename employee (em table) records when an employee name changes. This function can compare records by the employee number (EMNUMBER) and then rename all references to the em record before renaming the parent record.

The function will mark the record as RENAMED. The Archive function should be called after UPDATE_REF_VALUE to create a record of the change.

The original parent record is marked as REN_DELETE

Required Variables:

Function Name – UPDATE_REF_VALUE

Function Type – Special Function

Datasource From – Can be different than Datasource To.

Table Name From – Table name where new list of records are sourced from

Primary Key Field From 1 – Primary key field in the source table

Datasource From – Can be different than Datasource To.

Table Name To – Destination table name where records are written to (parent table)

Primary Key Field To 1 – Primary key field in the destination table

From Table Restrict – Restriction in gathering records from the source table

Update Non-Mod Records – Always set to Yes

Validation Notes Field – Field name in the destination table where notes regarding

the rename are stored.

Special Function Variable 1 – Match field in the source table. This is compared to the match field

in the destination table to determine if a record was renamed.

Special Function Variable 2 – Match field in the destination table.

Special Function Variable 3 – Field in the source table that represents the primary key field

in the parent table.

Special Function Variable 4 – A list of child tables to exclude. Format ‘table1′,’table2′,’table3’.

Special Function Variable 5 – Duplicated Suffix.

Removes Duplicate suffix values eg. “_DUP” from the value.

Must be set to NA if no used.

This function is required when the referencing field is part of the primary key of the table. To search for tables apply the following restriction to the Archibus fields table (afm_flds)

Validating Table = table name eg. Em

AND Primary Key > 0

AND Validate Data? = Yes

CASCADE_RENAME_2KEY

CASCADE_RENAME_2KEY is similar to UPDATE_REF_VALUE but is suports tables with 2-part primary keys. It performs cascading renaming of referenced records.

An example of the application of this function would be to rename Department (dp table) records when a Division or Department Code changes. From and to values are required for both ot the primary key values.

The function will mark the record as RENAMED. The Archive function should be called after UPDATE_REF_VALUE to create a record of the change.

The original parent record is marked as REN_DELETE

Required Variables:

Function Name – CASCADE_RENAME_2KEY

Function Type – Special Function

Datasource From – Can also be External table

Table Name From – Table of record to be compared from e.g. rsc_emsource

From Table Restrict – Restriction applied to gathering of records from the From Table

Primary Key From 1 – Old first primary key value e.g. field1 (dv_id)

Primary Key From 2 – Old second primary key value e.g. field2 (dp_id)

Primary Key From 3 – New first primary key value e.g. field3 (dv_id)

Primary Key From 4 – New second primary key value e.g. field4 (dp_id)

Special Function Variable 1 – Parent table name e.g. dv

Special Function Variable 2 – Child table name e.g. dp

Special Function Variable 3 – Parent table field name e.g. dv_id

Special Function Variable 4 – Child table field name e.g. dp_id

Special Function Valiable 5 – Tables_to_skip – list of child tables to skip during cascading update

(e.g. ‘mo’,’em’) Set to None is no tables

The old parent value records are removed after the cascading rename is completed.

IMPORT_WEB_FILE

IMPORT_WEB_FILE – Performs download of file from Web URL.

An example of the application of this function would be to download a list of employees from an outsourced HR company.

The download file is overwritten.

Required Variables:

Special Function Variable 1 – Source URL e.g. microviewfm.com

Special Function Variable 2 – Source file name eg. “/download/MVHVEL.PDF

Special Function Variable 3 -.Download file name e.g. C:\TEMP\MVHVEL.PDF

Special Function Variable 4 – Port e.g. 80 for HTTP, 443 for HTTPS

Special Function Variable 5 – Username for secure connect

Special Function Variable 6 – Password for secure connection

INI File variables (RSCSQLAUTO.INI):

PROXY=192.10.10.10 – name or IP of the proxy server

IMPORT_WEB_FILE_REPEAT

IMPORT_WEB_FILE_REPEAT – Performs repeated download of files from Web URL using a variable parameter read from a table.

An example of the application of this function would be to download employee photos from an outsourced HR company.

The download file(s) are overwritten.

Required Variables:

Table Name From – Table name where <field_value> is sourced from

Primary Key Field From 1 – Field that maps to <field_value>

From Table Restrict – Restriction in gathering records from the source table

Special Function Variable 1 – Source URL eg. microviewfm.com

Special Function Variable 2 – Source file name

e.g. “/api//employees/<field_value>/photo/small” (field value is evaluated)

Special Function Variable 3 -.Download file name

e.g. “d:\cafm\pic\<field_value>.jpg” (field value is evaluated)

Special Function Variable 4 – Port e.g. 80 for HTTP, 443 for HTTPS

Special Function Variable 5 – Username for secure connect

Special Function Variable 6 – Password for secure connection

INI File variables (RSCSQLAUTO.INI):

PROXY=192.10.10.10 – name or IP of the proxy server

IMPORT_FTP_FILE

IMPORT_FTP_FILE – Performs download of a file from an FTP site

– FTP only. Use IMPORT_SFTP for secure FTP

The download file is overwritten.

Required Variables:

Special Function Variable 1 – Source URL e.g. ftp.microviewfm.com

Special Function Variable 2 – Source file name e.g. “/download/MVHVEL.PDF

Special Function Variable 3 -.Download file name e.g. C:\TEMP\MVHVEL.PDF

Special Function Variable 4 – Port e.g. 21

Special Function Variable 5 – Username for secure connect

Special Function Variable 6 – Password for secure connection

EXPORT_FTP_FILE

EXPORT_FTP_FILE – Performs upload of a file to an FTP site

– FTP only. Use EXPORT_SFTP for secure FTP

The upload file is overwritten.

Required Variables:

Special Function Variable 1 – Remote URL e.g. ftp.microviewfm.com

Special Function Variable 2 – Remote file name e.g. “/download/MVHVEL.PDF

Support embedded date parameters.eg. _<mm_dd_yy hh:nn>.

Special Function Variable 3 -.Source file name e.g. C:\TEMP\MVHVEL.PDF.

Special Function Variable 4 – Port e.g. 21

Special Function Variable 5 – Username for secure connect

Special Function Variable 6 – Password for secure connection

IMPORT_SFTP_FILE

IMPORT_SFTP_FILE – Performs download of a file from an SFTP site

– SFTP only. Use IMPORT_FTP for non-secure connections

– Requires registering WINSCP software. See Below

The download file is overwritten.

Required Variables:

Special Function Variable 1 – Source URL eg. ftp.microviewfm.com

Special Function Variable 2 – Source directory and file name on SFTP server

e.g. “/download/MVHVEL.PDF

Special Function Variable 3 -.Download directory and file name on local server

e..g. C:\TEMP\MVHVEL.PDF

Special Function Variable 4 – Port eg. 22

Special Function Variable 5 – Username for secure connect

Special Function Variable 6 – Password for secure connection

Import / Export File Name – HostKey. See WINSCP Installation Instruction below.

File Suffix Text – If set to “DELETE SOURCE” then the source file is deleted after transfer/

EXPORT_SFTP_FILE

EXPORT_SFTP_FILE – Performs upload of a file to an SFTP site

– SFTP only. Use IMPORT_FTP for non-secure connections

– Requires registering WINSCP software. See Below

The upload file is overwritten.

Required Variables:

Special Function Variable 1 – Source URL eg. ftp.microviewfm.com

Special Function Variable 2 – Source directory and file name on local server

eg. C:\TEMP\MVHVEL.PDF

Special Function Variable 3 -.Remote directory and and file name on SFTP server

e.g. \download\MVHVEL.PDF

Support embedded date parameters.eg. _<mm_dd_yy hh:nn>.

Special Function Variable 4 – Port eg. 23

Special Function Variable 5 – Username for secure connect

Special Function Variable 6 – Password for secure connection or private key file

For Private key use: KEY_FILE:D:/CAFM/STORE/pkey_name.ppk

Import / Export File Name – HostKey. See WINSCP Installation Instruction below.

Registering the WINSCP Software

WINSCP is a shareware Secure FTP client software. It is written in DotNet and requires registering the DLL before use

1) Unzip \RSCSQLAUTO\WINSCP.ZIP. This should create the WINSCP subfolder

2) Run \RSCSQLAUTO\WINSCP\REGWINSCP.BAT. Verify the the DotNet library registers correctly.

2) Run \RSCSQLAUTO\WINSCP\WINSCP.EXE. Open a test connection the destination SFTP server.

Copy the HostKey when prompted.

3) In the EXPORT_SFTP_FILE or IMPORT_SFTP_FILE functions add the HostKey to the

Import / Export File Name field.

NOTE: After the initial setup you can find the HostKey by connecting to the SFTP session and then going to Commands – Server/Protocol Information.

EXPORT_IMAGES

EXPORT_IMAGES – Extract images stored in a table field. Repeats for each record

– Normally uses the rsc_emsource.image_field. Use the sql_addimagefld.abs to add the field.

– The files are overwritten

Required Variables:

Table Name From – Source table holding image data e.g. rsc_emsource

Primary Key Field From 1 – Source field holding image data e.g. image_field

From Table Restric – restriction on source data field (not required) e.g. image_field IS NOT NULL

Special Function Variable 1 – field that holds the name of the file. (<name>.jpg) e.g. field1

Special Function Variable 2 – extraction folder (requires trailing backslach) e.g. d:\cafm\temp\

Special Function Variable 3 -.file extension e.g .jpg.

Special Function Variable 4 – Perform Base 64 conversion e.g. Y

PROC_FLOOR_COVERING

PROC_FLOOR_COVERING – RSC Floor Coverings customization (PGE),

– Keeps the rsc_rm_flooring table in sync. Corrects percentages.

– Normally scheduled to run once per day.

Required Variables:

None.

DELETE_UNREF_VALUE

DELETE_UNREF_VALUE performs deletion of records in tables from record values gathered in another table.

This is used to remove references to a record that are part of the primary key in a referenced table.

Required Variables:

Function Name – DELETE_UNREF_VALUE

Function Type – Special Function

Special Function Variable 1 – Restriction that defines the records to remove.

This SQL statement must retrieve a single field value.

e.g. “SELECT em_id FROM em WHERE update_type = ‘DELETE’”

Special Function Variable 2 – table(s) and field(s) to delete

e.g. “em_trial.em_id”. Records are line separated (ctrl-lf)

Special Function Variable 3 – % Threshhold for deletion of records.

Prevents deletion of records is the records in restriction

exceeds the total number of records. Set to 100 to allow all records to be deleted.

FILE_RENAME_DATE

FILE_RENAME_DATE renames a file to a new name. Can include the current date and time.

Required Variables:

Function Name – FILE_RENAME_DATE

Function Type – Special Function

Special Function Variable 1 – File name to rename that includes full path.

e.g “c:\import\em_import.txt”

Note: You can also use the * wildcard character. to rename multiple files.

Special Function Variable 2 – File name to rename to that includes full path.

e.g “c:\import\em_import_<mm_dd_yy hh:nn>.txt”

Can include embedded timestamp format surrounded by <> characters.

Note: You can also use the * wildcard character. to rename multiple files.

FILE_RENAME Identical functionality as FILE_RENAME_DATE.

This exists in order support two rename operations within the same function.

UPDATE_STATISTICS

UPDATE_STATISTICS writes records to the rsc_sql_statistics table based on counts for Update Types

Variables:

Function Name (required) – UPDATE_STATISTICS

Function Type (required) – Special Function

Table Name From Type (required) – The name of the table to read statistics from.

e.g. em

Primary Key Field From 1 Type (required) – The field to extract counts from (count for each unique value).

e.g. update_type

Special Function Variable 1 (required) – Function name to display in the statistics table.

e.g. Employee Update.

Special Function Variable 2 (required) – types exclude. This is comma delimited list in single quotes.

e.g. ‘NO_CHANGE’,’NO_UPDATE’

Special Function Variable 3 (optional) – email prototype name

e.g. STATISTICS_ERROR

Special Function Variable 4 (optional) – Exclusion list of update_type for average check (email)

e.g. ‘REN_DELETE’,’DELETED’,’DELETE1′

Special Function Variable 5 – email_to

e.g. gdryer@rsc2lc.com

Special Function Variable 6 (optional) – email_cc list

e.g. user1@test.com;user2@test.com

Number of Columns (optional) – % threshold for email notification (also regures email_to to be set)

Emails a warning when the quantity for the the update type (e.g. DELETED) is greater than the threshold.

For example if the threshold is set to 200% then 21 deletions would triggers an email if the average number of deletions is 10.

ARRAY_2D_INSERT

ARRAY_2D_INSERT Insert an array of records based on 2 lists of records based on 2 select statements. These can be from separate tables. If there are 10 records returned from the first select and 5 returned from the second then a total of 50 records will be created.

Required Variables:

Function Name – ARRAY_2D_INSERT

Function Type – Special Function

Table Name To – The name of the table to write records to.

Primary Key Field To 1 – Field to write field value from first select.

Primary Key Field To 2 – Field to write field value from second select.

Special Function Variable 1: First select statement

e.g. SELECT project_id FROM project

Special Function Variable 2: Second select statement

e.g. SELECT field2 FROM rsc_emsource

COMPLETE_MP

COMPLETE_MP – Auto-complete Move Projects for SpaceView

Required Variables:

Function Name – COMPLETE_MP

Function Type – Special Function

Table Name To – The name of the table to write records to.

Special Function Variable 1: Restriction on the MP table

e.g. move_status = ‘Approved’ AND DateDiff(d, date_start, GETDATE()) = 0

CLOSE_WR

CLOSE_WR – Auto-close Completed Work Requests

NOTE: This function sets the activity_log.external_req = 5 (close). It requires SVMSLA.java version 1.6 or later (RSC-SVMoblie-workflow.JAR). This is a scheduled Workflow in Archibus.

Required Variables:

Function Name – CLOSE_WR

Function Type – Special Function

Special Function Variable 1: Restriction on the WR table

e.g. DATEDIFF(day, date_completed, GETDATE()) > 30 AND status = ‘Com’

FILE_TIMESTAMP

FILE_TIMESTAMP – Update timestamps of files in the afm_dwgs (or any other) table

Required Variables:

Function Name – FILE_TIMESTAMP

Function Type – Special Function

Table Name From – source table for records and table where date and time fields reside.

Each record in this table represents a single file – REQUIRED.

E.g. afm_dwgs

Primary Key Field From 1 – 1st primary key of the from table.

Necessary as it may be an external DB – REQUIRED

E.g. dwg_name

Primary Key Field From 2 – 2st primary key of the from table.

Necessary as it may be an external DB

From Table Restrict: – restriction applied to the source table records

Special Function Variable 1 – date_field – field to hold date and time – REQUIRED

e.g. rsc_date_map

Special Function Variable 2 – time_field – field to hold date and time – REQUIRED

e.g. rsc_time_map

Special Function Variable 3 – source_dir – directory start search point – REQUIRED

Can contain embedded variables.

<bl_id and <fl_id> are special as they are extracted from the Space Hierarchy field

e.g. D:\CAFM\APPS\CADNOW\<bl_id>-<fl_id>\

Special Function Variable 4 – file_name – file name to search for – REQUIRED

Can contain embedded variables.

<bl_id and <fl_id> are special as they are extracted from the Space Hierarchy field

e.g. roomlist.txt

WRITE_MAPS

WRITE_MAPS – Publishes drawing via CADNow executable.

Drawings are published where rsc_date_dwg > rsc_date_map (afm_dwgs table)

Required Variables:

Function Name – WRITE_MAPS

Function Type – Special Function

Table Name From – source table for records and table where date and time fields reside.

Each record in this table represents a single file – REQUIRED.

e.g. afm_dwgs

Primary Key Field From 1 – 1st primary key of the from table.

Necessary as it may be an external DB – REQUIRED

e.g. dwg_name

Special Function Variable 1 – Path and name of CADNOW executable

e.g. d:\cafm\apps\cadnow\CADNOW.EXE

Special Function Variable 2 – Root drawing folder. Root folder where it searches for drawings from.

e.g. d:\cafm\afmdata\dwgs\

ZIP_FOLDER

ZIP_FOLDER – Compress a folder of files (and subfolders into a compressed (ZIP) file.

Required Variables:

Function Name – ZIP_FOLDER

Function Type – Special Function

Special Function Variable 1 – Path for top level folder.

e.g. d:\cafm\afmdata\maps\

Special Function Variable 2 – Path and filename for zip file..

e.g. d:\cafm\afmdata\mapzip\maps.zip

Special Function Variable 3 – Maximum minutes to wait for completion.

Note – It will continue to zip in the background after this timeout period

e.g. 10

ZIP_OPERATION

ZIP_OPERATION – Designed for z-zip command line but could be used to run any executable that can return an ERRORLEVEL.

Required Variables:

Function Name – ZIP_OPERTION

Function Type – Special Function

Special Function Variable 1 – Path and file name for executable..

e.g. D:\CAFM\RSCSQLAUTO\7zip.bat

Special Function Variable 2 – Path and filename for log file.

e.g. D:\CAFM\RSCSQLAUTO\7zip_log.txt

Special Function Variable 3 – Maximum minutes to wait for completion.

Note – It will continue to zip in the background after this timeout period

e.g. 10

Example batch file for z-zip command line compression:

d:\cafm\rscsqlauto\7za a -bd -aoa d:\cafm\afmdata\maps.zip d:\cafm\afmdata\maps\* > nul

If %ERRORLEVEL% == 0 (Echo Zip Successful > D:\CAFM\rscsqlauto\7zip_log.txt)

ELSE (Echo Error – Zip Not Successful > D:\CAFM\rscsqlauto\7zip_log.txt)

RUN_SHELL

RUN_SHELL – Shell out and run a command (from the command prompt).

Required Variables:

Function Name – RUN_SHELL

Function Type – Special Function

Special Function Variable 1 – Command line parameters..

e.g. del c:\temp\*.bak

IMPORT_EMAILS

IMPORT_EMAILS – Import emails and place the emails in the rsc_email_import table.

Rsc_email_import table

Email_from – address from

Email_text_subject – email subject

Email_text_body – email body

Processed – default 0. Used as a flag for post processing of email records.

Email_type – Prototype Email Name. Used as a flag for post processing.

Field1 to field5 – Used to store intermediate data.

Requires installing the EAGetMail client on the server:

Run EAGetMail.exe and install under \RSCSQLAUTO\EAGetMail folder.

Required Variables:

Function Name – IMPORT_EMAILS

Function Type – Special Function

Import/Export File Name – Prototype Email Name eg. ACT_LOG_IMPORT

Special Function Variable 1 – Server Type

IMAP – Exchange

DAV

EWS

POP3

Special Function Variable 2 – Use SSL

TRUE – value for exchange

FLASE

Special Function Variable 3 – Server URL / name

Eg. imap10.exchangecentral.net

Special Function Variable 4 – Server Port

Eg. 993

Special Function Variable 5 – User account name

Eg. scheduleview@rsc2lc.com

Special Function Variable 6 – User account password.

WRITE_PDFS

WRITE_PDFS – Publishes drawing via PDF Files.

Requires installation of AnyDWGToPDF software on server.

Drawings are published where the drawings date is later than the PDF date

Required Variables:

Function Name – WRITE_PDFS

Function Type – Special Function

Function Variable 1 – Full path and executable name of the AnyDWG to PDF software [D:\CAFM\RSCSQLAUTO\AnyDWGtoPDF\dp.exe]

Function Variable 2 – Full path to the source DWG folder [D:\CAFM\AFMDATA\DWGs]

Function Variable 3 – Full path to the output PDF folder [D:\CAFM\AFMDATA\PDFs]

Function Variable 4 – Full path to the backup PDF folder [D:\CAFM\AFMDATA\PDFs\Old]

All folders must exist.

If the PDF is being updated then it is backed up to the backup PDF folder

WR_PARTS

WR_PARTS – Adds a part to work requests by parsing the part_id quantity, and parts warehouse from a field in the WR table (normally the description). This was written to be used with the submit request functionality is SpaceView.

Required Variables:

Function Name – WR_PARTS

Function Type – Special Function

Table Name From – Table to pull records from ‘wr’

Primary Key Field From 1 – ‘wr_id’

Primary Key Field From 2 – Not required

From Table Restriction – Restriction to pull records.

e.g. wr.prob_type = ‘WAREHOUSE’ AND

(SELECT COUNT(part_id) FROM wrpt WHERE wrpt.wr_id = wr.wr_id) = 0

Special Function Variable 1 – Field to parse part data from

e.g. description

Special Function Variable 2 – Start of String before the part code <LF> ends the line

e.g. Item:

Special Function Variable 3 – Start of String before the part quantity <LF> ends the line

e.g. Quantity:

Special Function Variable 4 – Start of String before the warehouse <LF> ends the line

e.g. Warehouse:

Parts are removed to the PT table inventory.

WS_DP_PCT_USE

WS_DP_PCT_USE – Populates the RMPCT table with records that represent shared use of rooms by department

Required Variables:

Function Name – WS_DP_PERCENT_USE

Function Type – Special Function

Table Name From – Table to pull records from

e.g. rsc_emdown

Primary Key Field From 1 – Primary key of source data table

e.g. emdown_id

From Table Restriction – Restriction to pull records.

e.g. comments IS NULL AND division IS NOT NULL AND department IS NOT NULL

AND mailstopdown <> ‘0’

Special Function Variable 1 – Field for bl_id

e.g. buildingdown

Special Function Variable 2 – Field for fl_id

e.g. floordown

Special Function Variable 3 – Field for rm_id

e.g. rooomdown

Special Function Variable 4 – Field for dv_id

e.g. division

Special Function Variable 5 – Field for dp_id

e.g. department

Special Function Variable 6 – Field for % of space used

e.g. mailstopdown

The RMPCT table is updated using standard Archibus rules for Workplace Transactions. NOTE: Apply a custom routine for Reconcile otherwise it will likely overwrite the data.

RMPCT_INCLUDE_CALCS

RMPCT_INCLUDE_CALCS – Update rmpct.rsc_include_calcs for SpaceView Reports Default value is Yes Review the code for the algorithm

Required Variables: None

DP_CHARGEBACK

DB_CHARRGEBACK – Update Room Division and Department values based on business logic defined in the RSC_DP_CHGBK_RULES table

– Logic defined by one or more records in the Department Chargeback Rules table.

– Restriction requires Room Category and Room Type value.

– Restriction requires additional custom restriction on any field in the BL, FL or RM table.

Required Variables:

None.

WRITE_FILES

WRITE_FILES – Create multiple ASCII files based on a prototype with embedded variables

– This can be used in conjunction with an email event to deliver a custom attachment with an email e.g. an ICS calendar event for Outlook.

– The prototype file can contain embedded variable. e.g.

End Day: <date_end>

Part Of Day: <SELECT trans_to FROM rsc_translation WHERE trans_from = {day_part}>

– The prototype file will recognize simple HTML tags e.g. <br>

Required Variables:

Table Name From – Table to pull records from e.g. rmpct

Primary Key Field From 1 – 1st Primary key for table e.g. pct_id

Primary Key Field From 2 – 2nd Primary key for table (not required)

From Table Restrict – From Table Restriction

e.g. rsc_email_status = 0 AND em_id IS NOT NULL AND activity_log_id IS NOT NULL AND

(SELECT activity_type FROM activity_log WHERE activity_log.activity_log_id = rmpct.activity_log_id)

= ‘SERVICE DESK – HOTELING’

Special Function Variable 1 – Prototype path and file name

e.g. D:\CAFM\AFMDATA\EXCHANGE\PROTOTYPE\room_res.ics

Special Function Variable 2 – Export path (include trailing back slash

e.g. D:\CAFM\AFMDATA\EXCHANGE\

Special Function Variable 3 – Export file name

e.g. room_res_<pct_id>.ics

Special Function Variable 4 – Post SQL statement

e.g. UPDATE rmpct SET rsc_email_status = 1 WHERE pct_id = <pct_id>

UTF8TOANSI

UTF8TOANSI converts a text file from UTF-8 to ANSI encoding.

Required Variables:

Function Name – UTF8TOANSI

Function Type – Special Function

Special Function Variable 1 – UTF-8 source file directory and name e.g. c:\temp\utf.csv

Special Function Variable 2 – ANSI destination file directory and name e.g. c:\temp\ansi.csv

PRE_POST_ONLY

PRE_POST_ONLY executes a series of SQL statements from the Pre / Post SQL Table

Required Variables:

Function Name – PRE_POST_ONLY

Function Type – Special Function

The Pre / Post SQL table must be populated with SQL statements for the Master Function and the Function named PRE_POST_ONLY

– This can be used when more than one Pre/Post event is required per Master Function.

NOTE: The SQL statement repeat until completion without error. This is different than a Pre-Post statement assigned to a function that will try only 3 times.

Pre / Post SQL

The Pre / Post SQL table holds SQL statements to run before and after the Functions that perform field processing. This is useful for clearing tables prior to processing.

– “Function Name” is required

– “Pre / Post To Field Pro” is either Pre (before) or Post (after)

– Sql statements run in numeric order defined by “Processing Order”.

– The SQL statement do not support embedded field names.

NOTE: On error a SQL statement will repeat a total of 3 times before continuing.

Sync Fields

Sync Fields table values are required when the Function Type is “Fields”. Fields are copied from “Table Name From” to “Table Name To”

NOTE: The primary key field (in the To Table) should be first in the processing order for fields.

– “Table Name From” and “Field Name From” are omitted for calculated fields.

– “Table Name From” and “Table Name To” must match with the parent Master Function definition.

– “Field Order” is required when processing multi-level validated field (e.g. dv_id, dp_id).

– “Table Name To” and “Field Name To” are always required.

– “Field Types” choices are Char, Numeric, Date or Time

– “Apply To” indicates if the field value is updated for an existing record (Update),

new record (Insert), or both (Both).

– “Check For Modification is used to define which fields constitute a change to a record.

If the fields are the same then the record is not updated,

“Update Non Mod Records” in the Functions view must be set to No for this to apply.

“Update When Non Mod” in the Functions view must be set for this to apply.

– “Exclude If Match” allow record updates in inserts to be skipped based on a field matches

the current value

Current Record checks only the single matching record in the “Table Name To” (by the primary key).

Any Record looks for the field value in any record in the “Table Name To” .

– “Calculated Field Value” describes text, field names, or SQL statements that define the value

of the field.

Field values are from the “Table Name From”

Pure text (not surrounded by { } or < > is returned unevaluated.

Example: MODIFIED returns that string to the field value.

Field names are enclosed greater than and less than signs. They can also include other text.

Example: <lastname>, <firstname>

Field names can also be embedded in a SQL Statement.

Example: <SELECT trans_to FROM rsc_translation WHERE trans_from = {field10}>

<CURRENTDATE> Returns the current date.

<TIMESTAMP> Current date and time.

<RTRIM> Validates against the Field Value Translations table (see below)

<UCASE> Returns the result converted to uppercase

<LCASE> Returns the result converted to uppercase

Handling of < or > characters in >= or <= requires the following translation:

> – .GT.

< – .LT.

>= – .GE.

<= – .LE.

<> – .NE.

– “Valid Check SQL” describes a select statement to evaluate a foreign key.

If the statement returns null then it returns null to the field value.

Validation errors are returned to the “Validation Notes field”

The SQL statement supports embedded field values from the “Table Name From”

encoded as <name>.

e.g. SELECT dv_id FROM dv WHERE dv_id = <division>

– “Valid Table Insert SQL” is the SQL select statement to insert a new validating record in a

foreign key table.

If not null then foreign key values are inserted automatically

The SQL statement supports embedded field values from the “Table Name From”

encoded as <name>.

e.g. INSERT INTO dv (dv_id) VALUES(<division>)

Field Value Translations

The Field Value Translation table holds string translations and is used in conjunction with a Calculated Field value

– If the translation is not found then the field returns the original value from “Field Name From”.

– The “Calculated Field” value in the Sync Fields table requires a value.

Example: <SELECT trans_to FROM rsc_translation WHERE trans_from = {field10}>

Running Events

Sync Log

The Sync Log displays a history of all Master Functions (events).

Email Tasks

The RSC Email Engine performs email notifications either driven from email events (a SQL restriction) or directly queued emails populating the RSC_EMAIL_QUEUE table from an external application.

There are three tables that provide the email functionality:

1) Prototype Emails (RSC_EMAIl_DEF) This defines email types and their associated Email Subject and Email Body (overridden if defined in the email event.

2) Email Events (RSC_EMAIL_EVENTS) Links a prototype email to a scheduled function.

a) If the Email Subject and the Email Body Record are both NULL then an email is sent for all records matching the restriction. The Email Subject and Email Body must be set in the Prototype Email. The Post Email SQL should alter the parent record to prevent the email from being sent again. This supports parameterized variables e.g. “UPDATE wr SET option1 = ’email req’ WHERE wr_id = <wr_id>”

b) If the Email Subject and the Email Body Record are both set then a single email is sent for all records matching the restriction although each record can be reflected in the email body. See below for further details.

c) If the Email Body starts with <NO_HTML> then the email will be sent as Plain Text (if HTML emails are enabled).

3) Email Queue (RSC_EMAIL_QUEUE) Email Events create queue records which are processed from this table. Records remain as a history. An external application can add records to this table directly. See below for further details.

Email Queue

The Email Queue table (RSC_EMAIL_QUEUE) is the table for the email events. This table can be populated by an external application.

These fields are automatically generated:

auto_id – Auto-increment Primary Key
auto_date – Date record was generated (auto generated)
auto_time – Time record was generated (auto generated)
processed – Processed (indicates that email has been sent 1 = sent)
error_message – Error returned by email engine on failure

These fields are supplied values when the record is created (external application):

email_type – Email Definition (points to RSC_EMAIL_DEF table) CHAR 24 (uppercase)
email_to – Email Address (single entry only max 100 addresses including cc and bcc) VARCHAR 50
email_cc – CC List (one or more recipients delimited with a semicolon) VARCHAR 2000
email_bcc – BCC List (one or more recipients delimited with a semicolon) VARCHAR 2000
email_text_subject – Email Subject (If not null overrides value from RSC_EMAIL_DEF table) VARCHAR 200
email_text_body – Email Body (If not null overrides value from RSC_EMAIL_DEF table) VARCHAR 4000
attachment URL to attachment (this is a URL that will be added to the end of the email_text_body) VARCHAR 150
update_sql_success – SQL statement to issue after email sent (prevents repeat) VARCHAR 250
update_sql_failure – SQL statement to issue on failure to send email (prevents repeat) VARCHAR 250

source_table_name – Name of table of source record. See RSC_EMAIL_DEF table below.

source_primary_key_value – Primary key value for the source record. See RSC_EMAIL_DEF table below.

Prototype Emails

The RSC_EMAIL_DEF table provides prototype email definitions for the email events and single externally driven (batch emails.

Email Events

– The Master Function and Function point to the scheduled event (Pre and Post SQL are also supported)

– The Function Name points to the Email Type In te Prototype Emails table.

– The Email Subject, Email Body are defined by the Prototype Email if they are null.

– The Email Record Restriction sources records to process from the Email Data Table

The minimum required is a restriction against the Email Data Table

em.update_type = ‘NEW’

The following syntax is also supported. This allows the emails to be sorted in a grouped email (an email with multple records)

SELECT name_last, name_first FROM EM WHERE em.update_type = ‘NEW’

ORDER BY name_last, name_first

DO NOT include the primary key in the field list after SELECT as they will be automatically added. Only add fields after the SELECT necessary for the ORDER BY. To sort by em_id (primary key) use the following syntax for the Email Record Restriction:

em.update_type = ‘NEW’ ORDER BY em_id

– Post Email SQL On Success is a statement that is run if the email is sent. Email variables eg <name> are supported.

– Post Email SQL On Failure is a statement that is run if the email fails. Email variables eg <name> are supported.

– The Email Data Table and Email Table Primary Key point to the record in which the following fields derive their data from:

Email To Field – Field where email to is derived from. It assumes that the source table has a field named EMAIL.

Email To Field 2 – Field where second email to is derived from. Generates a separate email without cc or bcc.

Email To Field 3 – Field where third email to is derived from. Generates a separate email without cc or bcc.

Email To Field 4 – Field where fourth email to is derived from. Generates a separate email without cc or bcc.

Email To Fixed – Fixed email value. Generates a separate email without cc or bcc.

Email CC Field – Field where CC email list is derived from. This is sent with the first email.

Email BCC Field – Field where BCC email list is derived from. This is sent with the first email.

Email BCC List (;) Delimited is a fixed BCC list

Email CC List (;) Delimited is a fixed CC list or

if it begins with SELECT then it is a SQL statement that supports email variables eg. <name>.

e.g SELECT em_id FROM cf WHERE is_supervisor = 1

AND work_team_id = <work_team_id>

AND em_id IS NOT NULL

AND work_team_id IS NOT NULL

The first value returned becomes the Email To and the rest of the values form a CC list.

It is assumed that the SQL returns em_id values from which the email values are sourced.

Attachment File – Fixed file to attach to the email.

Supports date variables surrounded by <>

e.g. D:\CAFM\AFMDATA\MIR3\MIR3_DATA_encrypted_<yyyymmdd>.csv

Supports field values from the record being processed (Email Data Table)

surrounded by {}

D:\CAFM\AFMDATA\Badge_Request\{wr_id}.jpg

Attachment File = yes

Always convert the Email Body to an attachment file (HTML) regardless of the size.

Externally Driven (Batch) Emails

– The Email Queue record must reference the RSC_EMAIL_DEF record via the Email Type field.

– The Email Subject can contain embedded SQL (see below). This requires that the Email Queue record has values for the Source Table and Source PKEY variable.

– The Email Body can contain embedded SQL (see below). This requires that the Email Queue record has values for the Source Table and Source PKEY variable.

– Email CC and Email BCC fields override values if the are NULL in the email queue record.

Embedded SQL in the Email Subject and Email Body fields

– Pure text (not surrounded by { } or < > is returned unevaluated.

– Field names are enclosed greater than and less than signs. They can also include other text.

Example: <lastname>, <firstname>

– Field names can also be embedded in a SQL Statement.

Example: <SELECT bl_id FROM em WHERE em_id = {em_id}>

Debugging Errors

If there is a processing error (fatal) then an error email is sent to the ERROR_EMAIL and ERROR_EMAIL2 addresses listed in the RSCSQLAUTO.INI.

If the SQL Engine does not appear to be functioning then:

1) Check the RSC_SQL_AUTO_LOG.TXT file (C:\PROGRAM FILES\RSCSQLAUTO folder). This will indicate any errors or the last time the engine cycled.

2) If nothing appears to be happening then restart the SQL Automation Engine service. In Services it will be listed under RSC Service Manager.

Configuring Email Events

The Email Events table configures emails to be fired and queued via the scheduler.

1) Under Sync Engine – Administrative Functions – Master Functions, create a Master Function and apply it to a schedule

2) Under Sync Engine – Administrative Functions – Functions, create one or more Functions of Function Type “Mail Event”

3) Under Email Engine – Administrative Function – Email Events, create 1 or more email events.

– Email Data Table is the table from which records will be processed

– Email Table Primary Key is the primary key field of the table (Only tables with a single primary key field can be chosen).

– Email To Field is is a field from which the email address will be extracted.

If this is a a foreign key to the EM table then it looks up the email.

If it is not foreign keyed to EM then it evaluates the field (field value.

A single email is sent for all records grouped by this field.

– Email To Field 2 is is a field from which the email address will be extracted. This creates a separate email

If this is a a foreign key to the EM table then it looks up the email.

If it is not foreign keyed to EM then it evaluates the field (field value).

A single email is sent for all records grouped by this field.

– Email To Field 3 is is a field from which the email address will be extracted. This creates a separate email

If this is a a foreign key to the EM table then it looks up the email.

If it is not foreign keyed to EM then it evaluates the field (field value).

A single email is sent for all records grouped by this field.

– Email To Field 4 is is a field from which the email address will be extracted. This creates a separate email

If this is a a foreign key to the EM table then it looks up the email.

If it is not foreign keyed to EM then it evaluates the field (field value).

A single email is sent for all records grouped by this field.

– Email To Fixed is is a field from which the email address will be extracted. This creates a separate email

The field represents the value, not the field name.

A single email is sent for all records grouped by this field.

– Email CC Field is the field name that stores a list of CC addresses.

This is added only to an email going to “Email To Field”.

The field parses ctrl, ctrl – linefeed or commas and converts then to semi-colons.

– Email BCC Field is the field name that stores a list of CC addresses.

This is added only to an email going to “Email To Field”.

The field parses ctrl, ctrl – linefeed or commas and converts then to semi-colons.

– Email Record Restriction is the SQL restriction that chooses the records to be processed.

e.g. “mo_type = ‘New Hire’ AND date_completed IS NULL AND move_cancelled = 0”

– Email Subject is the subject line of the email. If blank then it uses the value in the Prototype emails table.

– Email Body Header is the start of the body of the email

If the Email Body Header starts with <NO_HTML> then the email will be sent as Plain Text

(HTML email enabled).

– Email Body Record is processed for each record. This is grouped by the “Email To” Field.

The text is parsed for record values that are defined inside <value> field

e.g. <bl_id> finds the current record value for the bl_id field.

NOTE: If “Email To” is not set then it creates a single grouped email. The email is sent to “Email To Fixed”.

– Email Body Footer is the end of the body of the email

– SQL On Success. SQL statement to issue after email sent This needs to alter the source record so the email will not be queued again.

– SQL On Failure. SQL statement to issue if an email is not successfully sent Optionally this needs to alter the source record so the email will not be queued

update_sql_failure SQL statement to issue on failure to send email (prevents repeat) VARCHAR 250

After the event runs the results by function are displayed in Master Function table, Sync Log field.

Records to be emailed are placed in the Email Queue for processing.

Demo Email Mode

Email Events and more precisely any email processed from the email queue can be configured to be run in a demo mode in which all emails are directed to a single email address. To enable Demo Mode add the following parameter to the RSCSQLAUTO.INI file (normally in C:\WINDOWS).

DEMO_EMAIL=gdryer@rsc2l.com

With DEMO_EMAIL set all emails will be directed to the set address and all CC and BCC addresses will be excluded.

HTML Formatted Emails

Email Subject, Email Body Headers, Email Body Record and Email Body Footer within Email Events can also support HTML tags. This requires setting a parameter in the RSCSQLAUTO.INI file (normally in C:\WINDOWS).

HTML_EMAIL = 1

NOTE: If configured for HTML email then it is assumed that all email events will use HTML tags

NOTE: Any tags are supported but substituting {} for <> in the definition. These will be converted at runtime

Also as <> the following tags are supported:

NOTE: With HTML Email enabled if you add <NO_HTML> to the beginning of the Email Body field of the Email Prototype or the Email Body Header field of the Email Event (Grouped Emails) It will send the email out as Plain Text rather than HTML email.

For html emails carriage returns (CRTL + LF) in the email body are normally removed. To convert carriage returns to hard breaks <br> then start the email body with <CONVERT_CTRL_LF>.

<br>

Example:

Email Body Header:

<tr><td>Employee</td><td>Start Date</td> <td>Manager</td><td>Division</td><td>Department</td></tr>

Email Body Record:

Email Body Footer:

</table></body></html>

Email Body Converted to an Attachment

If the Body of the email is larger than the field rsc_email_queue.email_text_body. Then it converts the body of the email to an attachment and send the file as an attachment. The folder where the attachments are stored is read from the EMAIL_ATTACHMENTS from the RSCSQLAUTO.INI file.

The email Body text is then pulled from the Email Body value of the Prototype Email.

Alternate Email Addresses

Email Events can support alternate email addresses. This is supported if the field em.email_alt (employee table) is populated. This is useful when the admin person should receive the email instead of the manager or if emails should be temporarily be redirected.

The alternate email is used when emails are looked up from the employee table. Fixed email addressed are NOT redirected.

RSC SQL Watch Engine

The additional Watch server (RSCSQLWatch) is an optional service that monitors and allows the main SQL Automation service to be remotely started and stopped via the Archibus view labeled Service Status. RSCSQLWatch also monitors the HEARTBEAT of the SQL Automation Engine and reports when the heartbeat has not been updated (rsc_variable_store table) for the last 60 minutes or the value set for CYCLE_CHECK in the INI file. Heartbeat error are sent to ERROR_EMAIL1, ERROR_EMAIL2 and ERROR_EMAIL3 as defined in the RSCSQLAUTO.INI file.

Remote Access to the RSC SQL Automation Service

When direct access to the server running the SQL Automation Service is not possible, the SQL Watch Engine allows remote access the the SQL Automation Service. The view under Sync Engine – Administrative Functions – Service Status shows the current status of the service. and allows the user to choose a command (Current Command). Commands Are:

Stop – Stop the SQL Automation Engine Service

Start – Start the SQL Automation Engine Service

Verbose Debug – Show all SQL in log file or log table

Debug Errors Only – Show only errors in the log file or log table

Log To Table – redirect log output to the log table (rsc_proc_log)

Log File Only – Log SQL and error output only to the log file (RSC_SQL_AUTO_LOG.txt)

After setting the command the SQL Watch service will perform the command in a maximum of 30 seconds. The Current Command will display None when the command has been completed.

The Current Status and Last Check fields indicate the status of the SQL Automation Engine as of the last test (every 30 seconds).

Using the GNU Decryption With the SQL Automation Engine

The RUN_SHELL special function can used to call the GNU GPG command line to encrypt or decrypt files.

NOTE: Make sure that the RUN_SHELL function Special Function Variable 1 value DOES NOT contain a return character at the end of the line

Install GNU for Windows

1) Download and install GPGforWin from https://www.gnupg.org/download/ (Windows GpG4Win)

2) Run setup with default options other than the install folder. Include GPA in the install option.

3) Run GPA application

– Pick Generate key now.

– Give it a name, email and passphrase

– After generation click on the key and choose Export

– Export to a folder D:\CAFM\AFMDATA\GnuPg (Send backup to this folder)

– Save the passphrase to a file in D:\CAFM\AFMDATA\GnuPg

4) Send the public key to the host. e.g. Workday.

Using GNU To Decrypt a File

Below is an example command line to decrypt a file:

d:\cafm\gnupg\pub\gpg.exe –homedir “d:\cafm\afmdata\gnupg” –batch –passphrase Zynga_Workday_Secret01 –output “d:\cafm\afmdata\download\emsync\current_workers.xml” –decrypt “//10.0.0.51\FileManager\zynga\upload\WD_ARCHIBUS_Current_workers.xml”

gpg.exe needs a path to where the executable was installed

–homedir points to the GNU configuration, passphrase and keys. You need to create this folder and copy the files from the user’s home folder in order to run as the system user under the SQL Automation Engine. The default user’s home folder will be c:\users\xxxx\AppData\Roamin\gnupg

–passphrase is the passphrase text chosen when generating the keys.

–output is the file to decrypt to

–decrypt is the file to decrypt

Using GNU To Encrypt a File

d::cafm\gnupg\pub\gpg.exe –homedir “d:\cafm\afmdata\gnupg” –recipient “Client_Name Test Key <gdryer@rsc2lc.com>” –output “C:\temp\Archibus full_encrypted.csv” –encrypt “C:\temp\Archibus full.csv”

gpg.exe needs a path to where the executable was installed

–recipient is the name of the key as displayed in User Name column from the Key Manager program

–output is the file to encrypt to

–encrypt is the file to encrypt from

Importing Public Keys

– To receive encrypted files and then decrypt them, you need to send the third party your Public key.

– To send encrypted files to a third party you need to encrypt then with a Public key that they provide.

1) Save the public key file (.ASC) into the Homedir eg D:\CAFM\AFMDATA\GnuPg

2) Open the KeyToool (GPA) program and go to Key Manager

3) Go to Import and import the Private Key from the provided .ASC file

4) Right mouse click on the Key and choose Set Owner Trust.

5) Set the Owner Trust to Ultimate.

6) Note the that User Name will be the name required in the –recipient value for the encryption command line.

7) Exit GPA

8) Copy all files from C:\Users\name\Application Data\GnuPg to the home folder D:\CAFM\AFMDATA\GnuPg.

RSC Automation Engine Documentation

RSC SQL Automation Engine

Overview

Installation and Configuration

Program Installation

Archibus Schema Installation

Archibus Client Server Views

Starting of the SQL Automation Engine for the First Time

Uninstalling the SQL Automation Services

Installation of The Smart Client and Web Central Interface

Configuration and Testing

SQL Tasks

Configure SQL Tasks and Statements

Running Events

Error Log

Debugging Errors

Sync Tasks

Master Functions

Functions

Special Functions

WRITE_MAPS

ZIP_FOLDER

ZIP_OPERATION

RUN_SHELL

IMPORT_EMAILS

WRITE_PDFS

WR_PARTS

Sync Fields

Field Value Translations

Running Events

Sync Log

Email Tasks

Email Queue

Prototype Emails

Email Events

Debugging Errors

Configuring Email Events

Demo Email Mode

HTML Formatted Emails

Email Body Converted to an Attachment

Alternate Email Addresses

RSC SQL Watch Engine

Remote Access to the RSC SQL Automation Service

Using the GNU Decryption With the SQL Automation Engine