$_pdo
A connection to your database. This is used to
execute SQLs, to create and populate temporary tables and to perform
other preparatory work like filtering, reformatting etc. Can also be
used to perform database maintenance operations such as deletes and updates.
This is a PDO type connection and so can be used as documented in the PHP PDO documentation at http://php.net/manual/en/book.pdo.php.
For example, this value creates a temporary table containing month numbers
and names. This could then be joined in with the main report query
to generate reports with month names based on a month number from
another table.
$sql ="CREATE TEMPORARY TABLE t_month ( monthno INT, monthname CHAR(12) ) ";
$stat = $_pdo->query($sql);
if ( !$stat )
{
trigger_error("Query Failed<BR>".$sql."<br>" .
"Error ".$_pdo->errorCode(). " - ".
$_pdo->errorInfo(), E_USER_ERROR);
return;
}
$sql = "INSERT INTO t_month VALUES (1, 'January')";
$stat = $_pdo->query($sql);
$sql = "INSERT INTO t_month VALUES (1, 'February')";
$stat = $_pdo->query($sql);
$sql = "INSERT INTO t_month VALUES (1, 'March')";
$stat = $_pdo->query($sql);
/*.............. And so on for each month ..........*/
// Also cursors may be used for example :-
foreach($_pdo->query('SELECT * from t_month') as $row)
{
print_r($row);
}
$_criteria
This array variable contains an entry for each criteria item
on the prepare screen and gives access to the relevant selected
criteria values. This allows query statements to be executed in the
custom code that incorporate the user's selected criteria. This is
advantageous in several ways. For example, reports that use queries
selecting from many large tables will be faster if some of the data
meeting the entered criteria is first selected into temporary tables
which can then be linked into the main query at the end. Also data can be
selected into temporary tables and further filtered or modified
before the final report query runs.
Accessing criteria values as selected on the prepare screen
involves calling the get_criteria_clause() function for the relevant
criteria item passing the type of value you wish returned from the
criteria which depends on whether the criteria item is a list,
lookup or date criteria type. The valid passed argument can be one
of "VALUE", "RANGE1" or "RANGE2".
For LOOKUP criteria types, the parameter of "VALUE" should be
passed to get the user's selection. For example, with a LOOKUP
criteria that is named country that
presents the user with a set of countries selected from the
database, you may fetch the selected countries with something like
this:-
$countries =
$_criteria["country"]->get_criteria_value("VALUE");
This returns a comma delimited string containing the selected
country values that is suitable for use in an SQL IN clause to help
you use it in further queries within the custom code. The values
returned relate to the criteria's Return Column attribute.
For List criteria items, again the parameter of "VALUE" should
be passed to get the user's selection as a comma-separated
list.
For Text Field criteria, pass the "VALUE" parameter to get what
the user has typed in to the criteria text field.
For Date type criteria, pass the "VALUE" parameter to fetch the chosen
date. This will be returned in the format as defined in the
configuration parameter SW_DB_DATEFORMAT. This should be suitable
for including in SQL data comparison clauses.
For Date Range Criteria, pass the "RANGE1" parameter to get the lower
date range value and pass "RANGE2" to get the upper date range
parameter.
The get_criteria_value() function by default returns criteria values which are surrounded by single quotes. This is so that they can be incorporated easily into SQL text e.g. 'January','February','March'. You can return the values without the single quotes by passing false as a second parameter.
