ITrack/Enterprise/EDocuments Extension
This ITrack Enterprise module has no view associated with it, but it generates output based on ASCII reports from the database and ITrack settings.
Currently, this module is used:
- To create output for HD Exchange and TransNet electronic purchase orders on the PO screen
- To create quote emails from the sales orders screen
Database Structure
EDocuments require an entry in the table edocument. If such an entry does not exist, no document generation is possible. If the user ever receives an error along the lines of 'cannot generate document due to missing template' this is likely the cause, and an ISoft employee must be contacted.
- edocumentid: unique identifier
- type: the specific type/category for the document. All edocuments with the same type will be considered interchangeable in a particular context (example: 'Purchase Order'). All documents with the same type will receive the same data sources when being generated.
- outputfilename: [optional] the file to which the output data will be written. If this does not exist, no output file is written. This string may contain constant text, functions, and variables (see syntax below).
- description: [optional] a simple description of the edocument and its use.
- content: the body of the document. This is what will be parsed by the EDocument manager and output to the output file.
- postgenerationscriptname: [optional] the name of a particular post-generation script. This represents a type of script to be run after document generation. This will also be the heading of a value in the [scripts] section of the host.ini. This allows each computer to have custom post-generation scripts (different locations/command line arguments).
- postgenerationscriptdefault: [optional] a default value for the post generation script defined above that will be used if no entry is found for the specified script in the user's host.ini.
- postgenerationscriptparameters: [optional] a string that will be passed on the command line to the post-generation script. This string may contain variables and functions as specified by the syntax section.
Top-level Report
The top-level report refers to the entirety of the document. This usually refers to the entire body of the document after all subreports have been expanded.
Subreports
Subreports represent dynamic sections of the top-level report that are repeated once for each data source registered in the subreport. A report can have unlimited subreports (they can be nested). Subreports are initiated with the special string '#BEGIN DETAIL#' (without quotation marks) and terminated by '#END DETAIL#' (without quotation marks).
Each subreport is given a 0-based index. A datasource is created for each subreport with the name subreportN, where N is the index of the subreport (example: subreport0 is the data source that represents metadata for the first subreport in the document).
The sub-report declaration has an optional area for passing in commands to the subreport generator. They follow the following syntax:
- BEGIN DETAIL[(subreport_name, row_offset, row_count, row_separator)]#
Here are all the optional arguments explained. Not all of the parameters have to be specified, but a parameter must have all previous parameters specified.
- subreport_name: This is the name of the subreport being invoked. If this is absent, the document assumes that this is the nth subreport. So, if the report needs 2 sections that use the same subreport, the second occurance of the subreport will need to specify that subreport0 was being used (or whatever that subreport is named).
- row_offset: This is a number of rows to skip in the subreport. This is similar to the first argument to a LIMIT clause in MySQL.
- row_count: This is the number of rows to output in the subreport. This is similar to the second argument to a LIMIT clause in MySQL.
- row_separator: This string represents the character sequence to automatically insert between all occurrences of the subreport. A common option for this section is "\n"
Variables
Variables represent chunks of information that can be retrieved from data sources. Data sources are parameter-value maps. Usually, data sources are column name-value maps from rows in a specific table. However, they may be any object in code that can respond to several virtual functions (see ITDataSource). Generally, this is performed by making an object inherit from ITDataSource (which contains an ITAttributeMap), and responding to the GetUniqueAttributes (which represent the defining/unique values of a datasource) and GetData (get all attributes) functions.
Along with variables that come from data sources, there are also variables that come from 'virtual data sources'. This include the subreport data source mentioned earlier. Currently, the following variables are available from subreport datasources:
- count: this value represents the cardinality of the subreport (# of records/rows).
There are also variables that represent top-level metadata. Currently, the following toplevel metadata data sources exist:
- {?outfiletitle}: [only available to post-generation script] the name of the final output file (after being parsed/processed).
- {?outfilepath}: [only available to post-generation script] the path of the final output file (after being parsed/processed).
- {?outfilename}: [only available to post-generation script] the full filename (path and name) of the final output file (after being parsed/processed).
Functions
Functions are data interpreting clauses that mutate parts of the content of an edocument. They are usually string modification, formatting, or math functions. Their format is a variable name (case-insensitive, but all caps is expected) surrounded by the appropriate function symbols (see syntax below), followed by parenthesis containing an N-tuple of comma separated parameters (example: PAD(test, 10). Some of these functions have optional parameters. All non-optional parameters are required for document generation (an error will be thrown if less than the minimum # of parameters is passed to a particular function).
Below is a list of all functions currently processed by the EDocument manager (optional parameters are enclosed by brackets):
- PAD/PADLEFT(str, count, [padChar]). This function pads a string out to a particular minimum length with a specified substring. If the string is already long enough, the string will not be modified.
- str: the string to be padded.
- count: the minimum length of the output string (if the string is already this long, the function will return the original string).
- padChar: the substring that will be added to the left of the string repeatedly until the string is at or longer than count. If this string is omitted, it will be assumed to be a single space character.
- PADRIGHT(str, count, [padChar]). This function is the same as above, but the substring will be added to the right side of the string until the desired length is reached.
- ESCAPE(str): This function will do standard SQL escaping on the string str. In other words, the characters \ and ' will be escaped with the character \.
- CURRENCY(str, [prechar = $], [precision = 2], [delimit_thousands = false]): this string formats a numerical (decimal) string to be a currency formatted string.
- str: the string to be formatted.
- prechar: the character to be put at the start of the string (if not already present).
- precision: the number of decimal characters to show in the string (default 2).
- delimit_thousands: if this is true (case insensitive), commas will be inserted every 10^3 (thousands separator).
- ADD(val1, val2): does a numerical addition on the two specified values. This substring will be replaced by the sum of the values. These values can be integers, decimals or floats. val1 and val2 represent the items to be added.
- SUBTRACT(val1, val2): compliment of above.
- MULTIPLY(val1, val2): multiplies the values.
- DIVIDE(val1, val2): compliment of above.
- ROUND(val, precision): rounds a numerical value (val) at a specific precision. Currently, this function only truncates to the specified precision, instead of rounding.
- val: the numerical value to be rounded. This can be integral or floating point.
- precision: the precision at which to round. 0 means to make the value integral. Negative precisions reduce the number of significant figures represented in the answer (-1 means truncate 1's place).
- TRIMZEROES(str): this function greedily strips 0's from the right of the string str. If this eliminates the entire decimal portion of a string, the decimal point is removed also.
- DATETIME(fmt): this function formats the current timestamp into a particular output format.
- fmt: a string that represents the output format. If this format is surrounded by " characters, they will be removed/ignored. For formatting flags, see MSDN CTime::Format formatting flags [1].
- TIMESTAMP(): this simply puts the current unix timestamp directly into the text.
- SETTING(scope, location, name, [default = ""], [cascade = false]): this function retrieves the specified ITrack setting.
- scope: can be one of the following (GLOBAL, STORE, GROUP, USER, COMPUTER/INI), and they represent global settings, store settings, group settings, user settings, and host.ini settings.
- location: the location of the setting. This is category the setting is located under. (See settings for more information)
- name: the name of the setting in that location. (See settings for more information)
- default: the default value for the setting that will be used (and written to that setting) if it didn't previously exist (default is "").
- cascade: whether the setting should cascade to user settings (non-cascading functions shouldn't be defined at lower scopes). (See settings for more info).
Pre-processing
Currently, the system makes 2 passes when replacing variables. In the first one, variables of the syntax {??variable_name} are replaced. Then, a second pass is run for variables of the structure {?variable_name}. This allows the user to nest information somewhat.
Sub-reports are always processed in order, and functions are always processed from the inside out, so pre-processing doesn't apply to them.
Syntax
Nearly all text in the body of an edocument is considered to be static text and will not be parsed. The only things that will be parsed and processed by the system are functions and data (see below). In order for a function or data clause to be well-formed, they must be encased by the appropriate delimiters.
Data items must be started by the begin data symbol. By default, this is: '{?' (without the quotation marks). They must be terminated by the end data symbol. By default this is '}' (without quotation marks). Data items are self-contained, so they will not be parsed internally, so do not attempt to nest data within data. Within these delimiters, data items are defined as: 'DATA_SOURCE'.
Example
The following is a sample EDocument that generates a quote EMail on the sales order screen, and an example output.
Customer ({?salesorder.customerid}): {?salesorder.billingcompany}
Billing Address:
{?salesorder.billingstreet}
{?salesorder.billingcity}, {?salesorder.billingstate} {?salesorder.billingzip}
Items:#BEGIN DETAIL#
{#PADRIGHT({?salesorderline.lookup}, 20)}{#PADRIGHT({#TRIMZEROES({?salesorderline.quantity})}, 20)} @ {#PADRIGHT({#CURRENCY({?salesorderline.price},$,2,true)}, 20)} {#PADRIGHT({#CURRENCY({#MULTIPLY({?salesorderline.price}, {?salesorderline.quantity})},$,2,true)}, 20)} {?salesorderline.description}#END DETAIL#
Customer (1): Test company
Billing Address:
1010 testing street
anywhere, NE 68003
Items:
test1_____1__________@ $1,000.00 $1,000.00__This is a test item that I created to demonstrate this process
test2_____20_________@ $500.00___$10,000.00 This is another item
