Example: Simple Tabular Report

This example will walk you through creating a simple tabular report in SL1.

The following steps are covered in this example:

Report Elements

There are three elements that must be created during report development:

Input Form. Defines the user interface for a report. This user interface allows the user to provide inputs to use in the report. Inputs may include specifying a timespan for the report, selecting specific elements to include in the report, and selecting the information to include in the report. The Input Form defines the GUI elements that make up the form itself, how the GUI elements interact with each other, and how the input should be passed to the Gluecode.
Gluecode. PHP code that examines the inputs provided in the Input Form and makes database queries to the ScienceLogic database to gather the required data. The Gluecode then formats the data so it can be populated into the Output Template.
Output Template. OpenOffice speadsheet that defines the layout and formatting styles of the report. An Output Template can include directives that define how data should be displayed depending on how the Gluecode outputs the data.

This example will focus on creating simple versions of each of these elements. The report we will create in this example will:

Show a simple table of all tickets, with the following columns:

Ticket ID
Organization
Severity
Description
Date Create
Created By
Category
Status
Source
Queue Name
Assigned To

Allow the list of tickets to be limited to specific organizations.

After we create the three elements (Input Form, Gluecode, Output Template), we will use them in a Report Template, which is used to generate the Report. This example includes two versions of the gluecode that use different methods for outputting data.

The examples in this chapter are available on the ScienceLogic Support Site in the Example Reports PowerPack.

Creating the Report Input Form

In our example, the Report Input Form will include an option that limits the results to only tickets for specific organizations.

First, create a new Report Input Template:

Go to Reports > Management > Report Input Forms. The Report Input Forms page is displayed.
Select the Create button. The Report Form Editor page is displayed.
Supply a value in each of the following fields:

Form Name. Name of the Report Input Form. This example is called "Simple Ticket List".
Form Type. Can be set to either Report or Component. If Report is selected, the Report Input Form can be used to generate reports. If Component is selected, the Report Input Form can be used as an element in other Report Input Forms. This example uses the default value, Report.
Version. The version number for the Report Input Form. SL1 uses this when reports are included in Power Packs. This example uses the default value, "1.0".
Author. The person who authored the Report Input Form. This example uses "ScienceLogic Documentation Team".

Click the Save button.

Now that we have created the Report Input Form, we can add the input elements. This example uses one input element, the Organization Selector, which is one of the default Custom Report Components. Custom Report Components allow common combinations of elements to be reused in many Report Input Forms. In this case, the Organization Selector includes the input elements that allow a user to select an organization from a list of all organizations in the system. To add the Organization Selector to the Report Input Form:

In the Available Components pane, double click on Organization Selector. The Organization Selector appears in the Form Structure pane.
Select the wrench icon for the Organization Selector. The Editing Organization Selector Component modal window is displayed.
Supply a value in each of the following fields:

Input Name. Name that identifies this instance of the Organization Selector and its input to the gluecode. In our example, the Organization Selector is named "orgs".
Width. Specifies the width in pixels of the Organization Selector when it is displayed on the Report Input Form. This example uses the default value of "200".
Height. Specifies the height in pixels of the Organization Selector when it is displayed on the Report Input Form. In our example, we leave this field blank, so SL1 automatically calculates the height based on the size of the list of organizations.

Click the Save button. The Editing Organization Selector Component modal window closes.
Click the Save button on the Report Form Editor window. The Report Input Form for this example is now complete.

Creating the Report Output Template

In this example, we will use the default global styles for the Report Output Template. To create the Report Output Template with the default styles, download one of the existing Report Output Templates:

Go to Reports > Management > Report Output Templates.
Click on the Template Name of event_detections.ods.

To download the Report Output Template with the global style information, you must click on the filename of the Report Output Template. Selecting the save icon () will download the Report Output Template without the global styles.

When the browser save dialog appears, save the file as "simple_ticket_list.ods"
Delete the "Debug" sheet.
Delete all the content and unmerge all cells in Sheet1.
Set the width of columns A - J to 1 inch.
Select Format > Styles and Formatting. The Styles and Formatting toolbar will be displayed, containing all the global styles.
Select cells A1 - J20, then, in the Styles and Formatting toolbar, double click on "Default". The Report Output Template will look like this:

Save the Report Output Template. The Report Output Template is now ready for the addition of content specific to this report.

In the next step, we add static text, output directives, and variable substitution to the template. Output directives specify where to insert data from the gluecode into the output template. To add the static text and output directives to the example template:

Insert a header image. Merge cells A1 - H5, then insert an image and anchor the image to the merged cell. In this example the ScienceLogic logo image is used:

Add static column headers. This example uses the column headers listed in the Overview section. Enter the column headers in cells A7 - J7:

Add [if:], [else:] and [endif:] directives. These directives are conditional statements that are used to evaluate inputs and then specify one of multiple outputs (based on the inputs). In this example, we will use them to check if the gluecode has passed any tickets as output. If the gluecode has passed tickets, the report will display a table with the ticket information. If the gluecode has not passed any tickets, , the report will display the message "No tickets to display". These directives use the value of the boolean tickets to determine if there are tickets to be listed. Enter "[if:tickets]" in cell A8, and "[else:tickets][endif:tickets]" in cell A9:

Add a [bindrow:] directive. When the [bindrow:] directive is used with an array, the report engine will duplicate the row the [bindrow:] appears on for each item in the array. In our example, the displayed table will contain a row for each ticket in the array tickets. Enter "[bindrow:tickets]" after "[if:tickets]" in cell A8:

Add variable substitutions. Variables are inserted into Report Output Templates using curly braces. As described in the gluecode section, suppose the gluecode returns a ticket with ticket id 0. The ticket data will be stored in the following array structure:

[tickets] => Array

(

[0] => Array

(

[company] => System

[descr] => Example Ticket

[date_create] => 2010/01/01 8:00 AM

[user_create] => em7admin

[ticket_class] => Abuse

[status] => Working

[source] => Internal

[assign] => Monitoring

[assigned_to] => em7admin

)

In cell A8, {##} is entered after the [bindrow:tickets] directive. The first pound-sign indicates that the value to be substituted comes from the array used by the previous bind directive, in this case the tickets array in [bindrow:tickets]:

The second pound-sign indicates that our example should use the array key for this row. In our example, the array keys are the ticketIDs, so the substituted value from the example array is 0.

For the other columns in our example, the substitution variable uses in the format {#.array_key}, where array_key is the key that points to the value for that column. For example, {#.company} would substitute the highlighted value from the example array into the column B8:

[company] => System

In row 8, enter substitution variables for the remaining array key names in cells B8 - J8:

Add a "Generated On" message at the end of the report. Enter "Generated On {date}" in cell A11. The gluecode passes the variable called date, which contains the date on which the report was generated:

Add header information to display the options used to generate the report. The default reports created by ScienceLogic use the upper right-hand two columns to display the options used to generate the report. In our example, the variable orgspec is passed by the gluecode. orgspec specifies whether the user selected the All Organizations checkbox in the Report Input Form. If the user selected the All Organizations checkbox, orgspec is set to "All Orgs". If the user did not select the All Organizations checkbox, orgspec is set to "Selected Orgs". orgspec can be substituted into the report in the same way date was in the previous step. Enter "Selected" in cell I2 (eye-two), and "{orgspec}" in cell J2:

The header image, table column, "Generated On" text and "Selected" text are not inside an output directive or variable substitution. These elements will be displayed in the report as they appear on the template.

Now that we have added the static text and output directives, we can apply styles to the template. To add styles to the example template:

Select a cell or collection of cells to apply the style to.
In the Styles and Formatting toolbar, double click the style. Our example uses the following styles:

EM7 Report Header/Footer. Applied to the header image, cells A1 - H5.
EM7 Column Header. Applied to the column headings, cells A7 - J7.
EM7 Data Text. Applied to the data rows and "No tickets to display" message, cells A8 - J9.
EM7 Data Date/Time. Overrides the EM7 Data Text style for the Create Date, cell D8.
EM7 Data Numeric. Overrides the EM7 Data Text style for the Ticket ID, cell A8.
EM7 Report Footer. Applied to the "Generated On" message, cell A11.
EM7 Report Header Label. Applied to the "Selected" text, cell I2.
EM7 Report Header Value. Applied to the orgspec variable, cell J2.

The completed template looks like this:

The Report Output Template can now be uploaded to the user interface. To do this:

Go to Reports > Management > Report Output Templates.
Select the Upload button.
Select the Browse button. In the upload dialog, select simple_ticket_list.ods.
Select the Upload button.
In the pop-up window that appears, select the OK button.

Creating the Gluecode

Report gluecode is written in PHP. For our example, the gluecode must do the following:

Extract the values from the $input array passed from the Report Input Form.
Process the inputs to determine whether the "All Organizations" checkbox was checked or unchecked and then build an appropriate WHERE clause for the SQL statement.
Query the database.
Process any datetime strings to use the correct format.
Pass the output to the output template. This example describes two versions of the gluecode: one version that populates the $output array and another that uses $em7_report to pass the output.

Extracting Input Values

Report Input Forms pass an array called $input to the gluecode. The name you define for each input element in the Report Input Form is used as a key in the $input array . Each key points to the input values for that element. In this example, the $input array contains one key, orgs. The orgs key points to the input values from the Organization Selector. The following line of code is used to extract the orgs array from the $input array:

extract($input, EXTR_PREFIX_ALL, 'in');

This extract statement will extract each entry in the $input array and store the entry in its own variable. Each variable will be named $in_, followed by the array key for the array entry. In our example, one new variable is created: $in_orgs.

Because the Organization Selector is a Custom Report Component, its input value is an array of values for each of its child elements. The keys for each child element map to the child elements' name. For an Organization Selector, the child elements are named "all_orgs" and "org". For more information on the names given to components in default Custom Report Components, see the Input Forms section.

Another extract statement is included in the gluecode to extract the values of the child elements into variables, prefixed by "selected_":

extract($in_orgs, EXTR_PREFIX_ALL, 'selected');

The Organization Selector has two constituent components, a checkbox (named "all_orgs") and a Multiple Select (named "orgs"). After the extract statements, the gluecode will be working with the following variables:

$selected_all_orgs. Contains the value passed by the "All Organizations" checkbox, either 0 (zero) or 1 (one).
$selected_orgs. Contains the value passed by the "Organizations" Multiple Select List, a linear array of organization IDs.

Processing Inputs and Building a WHERE Clause

You must write the gluecode so that it processes the input for the following three conditions:

The All Organizations checkbox was checked. In this case, the SQL query should be run with a WHERE clause that includes all organizations. The output variable orgspec should be set to "All".
The All Organizations checkbox was unchecked, and organizations were selected. In this case, the SQL query should be run with a WHERE clause that includes only the selected organizations. The output variable orgspec should be set to "Selected Orgs".
The All Organizations checkbox was unchecked, and no organizations were selected. In this case, the SQL query should be skipped and the output variable orgspec should be set to "Selected Orgs".

To handle these three situations, the gluecode will set the following variables prior to querying the database:

$where. Contains the WHERE clause for the query. $where is initially given a value that will return all organizations:

$where = "1";

If organizations were selected, the gluecode will later append $where with an AND statement.

$skip_query. A boolean that determines whether the database will be queried. $skip_query is initially given a value for the most likely outcome, that the database will be queried:

$skip_query = FALSE;

$output['orgspec']. The output variable orgspec, which will contain either "All" or "Selected Orgs". This is set in the "if statements" described below.

The gluecode must include an "if statement" that evaluates the variable $selected_all_orgs to determine whether the All Organizations checkbox was unchecked:

if ($selected_all_orgs) {

If $selected_all_orgs is TRUE, the initial values of $where (return all organizations) and $skip_query (FALSE, meaning the database should be queried) are still correct. orgspec should then be set as follows for the version of the gluecode that populates the $output array:

$output['orgspec'] = 'All';

} else {

Or as follows for the version of the gluecode that uses $em7_report to pass the output:

$em7_report->set_token_value('orgspec', 'All');

} else {

If $selected_all_orgs is FALSE, orgspec should be set as follows for the version of the gluecode that populates the $output array:

$output['orgspec'] = 'Selected Orgs';

Or as follows for the version of the gluecode that uses $em7_report to pass the output:

$em7_report->set_token_value('orgspec', 'Selected Orgs');

Remember that the orgspec variable specifies whether the user selected the All Organizations checkbox in the Report Input Form. If the user selected the All Organizations checkbox, orgspec is set to "All Orgs". If the user did not select the All Organizations checkbox, orgspec is set to "Selected Orgs".

The gluecode must include another "if statement" to evaluate the variable $selected_all_orgs and determine if the array of selected organizations is empty (meaning the user did not select any organizations):

if (empty($selected_orgs)) {

If the array $selected_all_orgs is empty, the database query should be skipped:

$skip_query = TRUE;

} else {

If the user selected one or more organizations and the array $selected_all_orgs is not empty, $where should be appended with an appropriate AND statement:

$where .= ' AND o.roa_id IN ('.implode(',',$selected_orgs).')';

}

This statement selects all organizations where the organization ID matches an entry in the $selected_all_orgs array.

Querying the Database

If the value of the variable $skip_query is TRUE, the gluecode will not query the database. The section of gluecode that queries the database is contained in an "if statement" that evaluates the variable $skip_query:

if(!$skip_query) {

Ticket information is stored in the table master_biz.ticketing. Nine fields from this table will be used in the query:

tid. The ticket ID.
roa_id. The organization ID.
descr. The ticket description.
date_create. The date of creation.
status. The ticket status, stored as an integer between 0 and 3.
user_create. The user ID of the user that created the ticket.
class. The ID number of the ticket category.
source. The ID number of the ticket source.
qid. The queue ID of the ticket queue.
assigned_to. The user ID of the user the ticket is assigned to.

Six of these values are stored as an integer. You must use a a LEFT JOIN statement on these values to obtain a human-readable value:

roa_id. Used to join to the "roa_id" field in the master_biz.organizations table. The "company" field in master_biz.organizations stores the name of the organization.
user_create. Used to join to the "uid" field in the master_access.accounts table. The "user" field in master_access.accounts stores the username of the user.
class. Used to join to the "id" field in the master.definitions_ticketing table. The "def" field in master.definitions_ticketing stores the category name if the t_type field is 0.
source. Used to join to the "id" field in the master.definitions_ticketing table. The "def" field in master.definitions_ticketing stores the source name if the t_type field is 2.
qid. Used to join to the "qid" field in the master_biz.ticket_queues table. The "name" field in master_biz.ticket_queues stores the name of the ticket queue.
assigned_to. Used to join to the "uid" field in the master_access.accounts table. The "user" field in master_access.accounts stores the username of the user.

Two values require additional processing to be included in the query:

date_create. Requires the UNIX_TIMESTAMP function to convert the timestamp into a UNIX timestamp. Later, the gluecode requires a UNIX timestamp when the timestamps are processed.

status. Requires a CASE statement to convert the integer into a human-readable value:

0 = Open
1 = Working
2 = Pending
3 = Resolved

The query is built and stored in the variable $sql, using the variable $where to add the WHERE clause:

$sql = "SELECT

t.tid, o.company, t.descr, UNIX_TIMESTAMP(t.date_create) date_create, CASE t.status WHEN 0 THEN 'Open' WHEN 1 THEN 'Working' WHEN 2 THEN 'Pending' ELSE 'Resolved' END status, acr.user user_create, dtcat.def ticket_class, dtsrc.def source, tq.name assign, aas.user assigned_to FROM master_biz.ticketing t

LEFT JOIN master_biz.organizations o ON (t.roa_id = o.roa_id)

LEFT JOIN master_access.accounts acr ON (t.user_create = acr.uid)

LEFT JOIN master.definitions_ticketing dtcat ON (dtcat.id = t.class AND dtcat.t_type = 0)

LEFT JOIN master.definitions_ticketing dtsrc ON (dtsrc.id = t.source AND dtsrc.t_type = 2)

LEFT JOIN master_biz.ticket_queues tq ON (tq.qid = t.qid)

LEFT JOIN master_access.accounts aas ON (t.assigned_to = aas.uid)

WHERE " . $where . "

ORDER BY t.date_create ";

The query uses an ORDER BY statement using the date the ticket was created. This is the order in which the tickets will appear in the report.

The variable that contains the query ($sql) is then used to query the database:

$tickets = $db->autofetch_all_assoc($sql);

$db is an instance of a class that is always passed to the gluecode. The $db class provides several functions that query the database and return the results as variables. The return values of the $db class functions are typically stored in arrays; different functions will return different array structures. For more information on the $db class functions, see the section on Database Methods.

Our example uses the autofetch_all_assoc function. This function returns the results of an SQL query as an associative array. Each key value in the returned array maps to the value of the first column of each row returned from the SQL statement. The key value for each row points to another associative array that contains the remaining values from the row. In this example, the keys of the returned array will map to the ticket ID . Each ticket ID key will point to an array containing the rest of the values returned for that row, with the keys as the field names. The following example shows the array structure if one ticket, with ticket id = 0, is returned:

[tickets] => Array

(

[0] => Array

(

[company] => System

[descr] => Example Ticket

[date_create] => 123456789

[user_create] => em7admin

[ticket_class] => Abuse

[status] => Working

[source] => Internal

[assign] => Monitoring

[assigned_to] => em7admin

)

Populating the $output Array

This section is applicable only to the version of the gluecode that populates the $output array.

The returned values for the date_create field are in UNIX timestamp format. The gluecode uses the od_datetime function to convert each of these UNIX timestamps into a format readable by the Report Output Template:

foreach ($tickets as &$ticket) {

$ticket['date_create'] = od_datetime($ticket['date_create']);

}

The gluecode has now correctly formatted the values in the $tickets array. The next step is to copy the $tickets array into the $output array, which will be passed to the Report Output Template:

$output['tickets'] = $tickets;

Using $em7_report to Output Results

This section is applicable only to the version of the gluecode that uses $em7_report to pass the output.

The output template includes the directive [bindrow:tickets] to repeat a row for each ticket in the output. The gluecode uses the get_token method to create a token object ($ticket_rows) that references this directive:

$ticket_rows = $em7_report->get_token('tickets');

A foreach loop is used to iterate through the list of tickets returned by the database query:

foreach ($tickets as $ticket_id=> &$ticket) {

$ticket['date_create'] = od_datetime($ticket['date_create']);

For each ticket, the $ticket_rows token object is used to create a new row. The ticket ID (the key value form the foreach loop) is supplied as the key. These keys will populate the {##} directive in the output:

$ticket_row = $ticket_rows->new_row($ticket_id);

Values are supplied to the row using the current value from the $tickets array and the row is closed:

$ticket_row->set_value($ticket);

$ticket_row->close_row();

The foreach loop is then closed:

}

Populating the Date

The "if statement" that contains the gluecode that queries the database is closed:

}

The Report Output Template requires the date variable to be included. orgspec should be set as follows for the version of the gluecode that populates the $output array:

$output['date'] = date("Y-m-d");

Or as follows for the version of the gluecode that uses $em7_report to pass the output:

$em7_report->set_token_value('date', date("Y-m-d"));

Creating the Report Template

To generate the example report, we must create a Report Template. A Report Template allows you to define all the parts of a report. A Report Template specifies identifying information about a report, and the Report Input Form, Report Output Template and gluecode that will be used to generate instances of a report.

To create the Report Template for this example:

On the Reports page (), expand the Management section and select Report Manager (Reports > Management > Report Manager). The Report Management page appears.
Click the Create button. The Report Template Editor page appears.
Complete the following fields:

Template Name. The name of the report, without spaces. This example is called "Simple_Ticket_List".
Version. The version number for the report. This example uses the default value, "1.0".
Author. The person who authored the report. This example uses "ScienceLogic Documentation Team".
Delivery Method. This option allows you to limit the options that are available when a user schedules this report. Select the methods by which scheduled instances of this report can be delivered. This example uses the default values.
Input Form. The Report Input Form that will be used to generate the Report. This example uses Simple Ticket List.
Output Template. The Report Output Template that will be used to generate the Report. This example uses simple_ticket_list.ods.
Description. A description of the report. This example leaves this field empty.
Category. Select one or more categories for the report. Categories are used to arrange the list of reports in the left NavBar and in drop-down lists that display reports. This example assigns the "Ticketing" category.
Key Words. A comma-separated list of key words that describe the report. This example leaves this field empty.
Report Count Query. This query populates the Row Count Estimate field for the report, and this query executes in the background with each input filter change made by the report user. The Row Count Estimate field informs users before they generate reports that are so large that SL1 cannot create them successfully. The query can include variables for the fields that a user selects in the report (Input Forms). The variables can be scalars or lists. As the user selects or de-selects Inputs, such as selecting Devices, then de-selecting Devices and selecting Assets instead, the query is re-run, and a new value appears in the Row Count Estimate field.
Name. If the report requires multiple possible queries based on the inputs that the user selects, type a name for each query, such as "devices" and "assets". Based on the input selected by the user, one of the two queries will be used to get the row count estimate. If the report requires only a single query, type "default" in this field. Click the Add Row button to add additional queries. Do not use double quotes (") or the back slash character (\) in this field.
Query. Add an SQL query that returns the total possible number of data rows in the report using the inputs selected buy the user. The query includes variables for fields that a user selects from the Input Form. The query can include variables for scalar values (single values) and variables for list values (multiple values). For more information about the SQL query for this field, see Creating Queries for the Row Count Estimate Field. Do not use double quotes (") or the back slash character (\) in this field.

The goal of the SQL query in the Query field is to provide an estimate of the returned rows, but the query should be as lightweight as possible. Do not re-use the SQL query that populates the report.
Add Row. Click this button to add another row containing the name and query code for an additional query for this template. You can add up to eight queries. Click the red cancel icon () to remove a query that you previously added.

To disable the Row Count Estimate feature, de-select the Report Size Estimation option on the Behavior Settings page (System > Settings > Behavior). This feature is enabled by default. If you disable this feature, SL1 retains the queries you created in the Query field, but SL1 will not run those queries when you create reports.

Query/Template Binding Code. The gluecode that will be used to generate the Report. This example uses the gluecode listed in the Full Code Listing for the $output Version section or the Full Code Listing for the $em7_report Version section.

Click the Save button. The report is now available under the Run Report > Ticketing section in the left NavBar, and you can include the report in a Report Job on the Report Jobs page.

Full Code Listing for the $output Version

extract($input, EXTR_PREFIX_ALL, 'in');

extract($in_orgs, EXTR_PREFIX_ALL, 'selected');

$where = "1";

$skip_query = FALSE;

if ($selected_all_orgs) {

$output['orgspec'] = 'All';

} else {

$output['orgspec'] = 'Selected Orgs';

if (empty($selected_orgs)) {

$skip_query = TRUE;

} else {

$where .= ' AND o.roa_id IN ('.implode(',',$selected_orgs).')';

}

if(!$skip_query) {

$sql = "SELECT t.tid, o.company, t.descr, UNIX_TIMESTAMP(t.date_create) date_create,

CASE t.status WHEN 0 THEN 'Open' WHEN 1 THEN 'Working' WHEN 2 THEN 'Pending' ELSE 'Resolved' END

status, acr.user user_create, dtcat.def ticket_class, dtsrc.def source, tq.name assign, aas.user assigned_to

FROM master_biz.ticketing t

LEFT JOIN master_biz.organizations o ON (t.roa_id = o.roa_id)

LEFT JOIN master_access.accounts acr ON (t.user_create = acr.uid)

LEFT JOIN master.definitions_ticketing dtcat ON (dtcat.id = t.class AND dtcat.t_type = 0)

LEFT JOIN master.definitions_ticketing dtsrc ON (dtsrc.id = t.source AND dtsrc.t_type = 2)

LEFT JOIN master_biz.ticket_queues tq ON (tq.qid = t.qid)

LEFT JOIN master_access.accounts aas ON (t.assigned_to = aas.uid)

WHERE " . $where . "

ORDER BY t.date_create ";

$tickets = $db->autofetch_all_assoc($sql);

foreach ($tickets as &$ticket) {

$ticket['date_create'] = od_datetime($ticket['date_create']);

}

$output['tickets'] = $tickets;

}

$em7_report->set_token_value('date', date("Y-m-d"));

Full Code Listing for the $em7_report Version

extract($input, EXTR_PREFIX_ALL, 'in');

extract($in_orgs, EXTR_PREFIX_ALL, 'selected');

$where = "1";

$skip_query = FALSE;

if ($selected_all_orgs) {

$em7_report->set_token_value('orgspec', 'All');

} else {

$em7_report->set_token_value('orgspec', 'Selected Orgs');

if (empty($selected_orgs)) {

$skip_query = TRUE;

} else {

$where .= ' AND o.roa_id IN ('.implode(',',$selected_orgs).')';

}

if(!$skip_query) {

$sql = "SELECT t.tid, o.company, t.descr, UNIX_TIMESTAMP(t.date_create) date_create,

CASE t.status WHEN 0 THEN 'Open' WHEN 1 THEN 'Working' WHEN 2 THEN 'Pending' ELSE 'Resolved' END

status, acr.user user_create, dtcat.def ticket_class, dtsrc.def source, tq.name assign, aas.user assigned_to

FROM master_biz.ticketing t

LEFT JOIN master_biz.organizations o ON (t.roa_id = o.roa_id)

LEFT JOIN master_access.accounts acr ON (t.user_create = acr.uid)

LEFT JOIN master.definitions_ticketing dtcat ON (dtcat.id = t.class AND dtcat.t_type = 0)

LEFT JOIN master.definitions_ticketing dtsrc ON (dtsrc.id = t.source AND dtsrc.t_type = 2)

LEFT JOIN master_biz.ticket_queues tq ON (tq.qid = t.qid)

LEFT JOIN master_access.accounts aas ON (t.assigned_to = aas.uid)

WHERE " . $where . "

ORDER BY t.date_create ";

$tickets = $db->autofetch_all_assoc($sql);

$ticket_rows = $em7_report->get_token('tickets');

foreach ($tickets as $ticket_id=>&$ticket) {

$ticket['date_create'] = od_datetime($ticket['date_create']);

$ticket_row = $ticket_rows->new_row($ticket_id);

$ticket_row->set_value($ticket);

$ticket_row->close_row();

}

$em7_report->set_token_value('date', date("Y-m-d"));