function createFromTemplate($sTitle = "NO DEFINED TITLE", $sFile = null, $oParms = null, $oDocument = null, $oGroups = null)
{
$sPath = getPHPReportsFilePath();
$oError = new PHPReportsErrorTr();
$sIf = $this->getDatabaseInterface();
$sFile = $sFile ? $sFile : realpath($sPath . "/template.xml");
// if the file does not exist
if (!file_exists($sFile)) {
$oError->showMsg("NOTEMPLATE", $sFile);
}
// get the template contents
$sFileContents = file_get_contents($sFile);
// check if there are some parameters here ...
if ($oParms) {
$sParms = null;
if (is_object($oParms)) {
$sParms = $oParms->write();
}
if (is_string($oParms)) {
$sParms = $oParms;
}
if ($sParms) {
$sFileContents = str_replace("<REPLACE_WITH_PARAMETERS/>", $sParms, $sFileContents);
}
}
// check if there is some document info
if ($oDocument) {
$sDocument = null;
if (is_object($oDocument)) {
$sDocument = $oDocument->write();
}
if (is_string($oDocument)) {
$sDocument = $oDocument;
}
if ($sDocument) {
$sFileContents = str_replace("<REPLACE_WITH_DOCUMENT_INFO/>", $sDocument, $sFileContents);
}
}
// if no groups info was specified, follow the default behaviour: all the fields from the query,
// with no group break
if (!$oGroups) {
// include the database interface and try to open the connection and execute the query
$sIfFile = realpath($sPath . "/database/db_" . $sIf . ".php");
if (!file_exists($sIfFile)) {
$oError->showMsg("NOIF", $sIf);
}
include_once $sIfFile;
// if the database connection is null, open it
if (is_null($this->_oCon)) {
$this->_oCon = @PHPReportsDBI::db_connect(array($this->sUser, $this->sPass, $this->sCon, $this->sDatabase)) or $oError->showMsg("REFUSEDCON");
}
// if there are some input filters ...
if ($this->_oInput) {
foreach ($this->_oInput as $oFilter) {
$oFilter->setConnection($this->_oCon);
$oFilter->setSQL(trim($this->sSQL));
$this->sSQL = trim($oFilter->run());
}
// there is no need to run the filters again
$this->_oInput = null;
}
// run the query
$this->_oQuery = @PHPReportsDBI::db_query($this->_oCon, trim($this->sSQL)) or $oError->showMsg("QUERYERROR");
// insert the column names
$sNames = "";
$sReplacedNames = "";
$iColNum = PHPREportsDBI::db_colnum($this->_oQuery);
for ($i = 1; $i <= $iColNum; $i++) {
$sName = PHPReportsDBI::db_columnName($this->_oQuery, $i);
$sExtra = isNumericType(PHPReportsDBI::db_columnType($this->_oQuery, $i)) ? " ALIGN=\"RIGHT\"" : "";
$sReplacedNames .= "<COL CELLCLASS=\"bold\"{$sExtra}>" . ucfirst(strtolower(str_replace("_", " ", $sName))) . "</COL>";
$sNames .= "<COL TYPE=\"FIELD\"{$sExtra}>{$sName}</COL>";
}
// build the group info
$sGroup = "<GROUP REPRINT_HEADER_ON_PAGEBREAK='TRUE'><HEADER><ROW><REPLACE_WITH_REPLACED_COLUMN_NAMES/></ROW></HEADER><FIELDS><ROW><REPLACE_WITH_COLUMN_NAMES/></ROW></FIELDS></GROUP>";
$sGroup = str_replace("<REPLACE_WITH_REPLACED_COLUMN_NAMES/>", $sReplacedNames, $sGroup);
$sGroup = str_replace("<REPLACE_WITH_COLUMN_NAMES/>", $sNames, $sGroup);
$sFileContents = str_replace("<REPLACE_WITH_GROUP_INFO/>", $sGroup, $sFileContents);
} else {
$sGroups = null;
if (is_object($oGroups)) {
$sGroups = $oGroups->write();
}
if (is_string($oGroups)) {
$sGroups = $oGroups;
}
if ($sGroups) {
$sFileContents = str_replace("<REPLACE_WITH_GROUP_INFO/>", $sGroups, $sFileContents);
}
}
// replace the report title
$sFileContents = str_replace("<REPLACE_WITH_TITLE/>", $sTitle, $sFileContents);
// print htmlspecialchars($sFileContents);
// create the temporary XML file
$sTemp = tempnam($this->_sTmp, "tempphprpt");
// this is just for PHP4 compability
$fHand = fopen($sTemp, "w");
fwrite($fHand, $sFileContents);
fclose($fHand);
$this->_bDeleteXML = true;
// flag to delete the temporary file
$this->sXML = $sTemp;
// the XML layout file is the temporary file now
}
function run()
{
// default values for aggregation functions
$this->_default["SUM"] = 0;
$this->_default["COUNT"] = "null";
$this->_default["MIN"] = "null";
$this->_default["MAX"] = "null";
// first step - find what columns we have, let's make a query that returns nothing but the columns
$stmt = PHPReportsDBI::db_query($this->_con, "select * from (" . $this->_sql . ") crosstab_table where 1=2");
$cols = array();
for ($i = 1; $i <= PHPReportsDBI::db_colnum($stmt); $i++) {
array_push($cols, PHPReportsDBI::db_columnName($stmt, $i));
}
PHPReportsDBI::db_free($stmt);
// find a delimiter
$deli = isNumericType($cols[$this->_group_key]) ? "" : "'";
// now we find the colums to work with the aggregated values - those are the values we'll create the columns
$cagr = array_diff(array_values($cols), array_merge($this->_group_desc, array($this->_group_key)));
// ok, now we know that columns to work with, we need to know the values of the group key
$stmt = PHPReportsDBI::db_query($this->_con, "select distinct " . $this->_group_key . " from (" . $this->_sql . ") crosstab_table order by " . $this->_group_key);
$keys = array();
while ($row = PHPReportsDBI::db_fetch($stmt)) {
array_push($keys, $row[$this->_group_key]);
}
PHPReportsDBI::db_free($stmt);
// create the sql query
// check if there is another default operation other than SUM
$oper = "SUM";
if ($this->_options["DEFAULT_OPERATION"]) {
$oper = strtoupper($this->_options["DEFAULT_OPERATION"]);
}
// check if there is an order
$order = "";
if ($this->_options["ORDER"]) {
$order = "order by " . $this->_options["ORDER"];
}
// first the description columns
$sql = "";
foreach ($this->_group_desc as $col) {
$sql .= $col . ",";
}
$group = substr($sql, 0, strlen($str) - 1);
$sql = "select " . $sql;
$coln = array();
// store the used column names
$apcn = $this->_options["APPEND_COLUMNS_NAMES"];
// then the aggregated values
foreach ($keys as $key) {
// here the key to compare
foreach ($cagr as $col) {
// here the column to manipulate
$op = $oper;
// default operation
// if there is a customized function for this column ...
if ($this->_options["COLUMNS_FUNCTIONS"][$col]) {
$op = $this->_options["COLUMNS_FUNCTIONS"][$col];
}
// check the default value of the aggregation function - convert to uppercase because they're uppercase there
if (!array_key_exists(strtoupper($op), $this->_default)) {
print "THERE IS NO DEFAULT VALUE FOR {$op}!";
return;
}
$defv = $this->_default[strtoupper($op)];
// check if there is some alias to the function - some translation, for example
$alias = $this->_options["FUNCTIONS_ALIASES"][$op] ? $this->_options["FUNCTIONS_ALIASES"][$op] : $op;
// create the column name
$name = strtoupper($alias) . "_" . strtoupper($key) . ($apcn ? "_{$col}" : "");
// check if there is already a column name like this, if so create a new
// one based on how many times it was repeated.
if ($coln[$name]) {
$coln[$name] = $coln[$name] + 1;
$name = strtoupper($alias) . "_" . $coln[$name] . "_" . strtoupper($key) . ($apcn ? "_{$col}" : "");
} else {
$coln[$name] = 1;
}
$sql .= "{$op}(case when " . $this->_group_key . "={$deli}{$key}{$deli} then {$col} else {$defv} end) as {$name},";
}
}
$sql = substr($sql, 0, strlen($sql) - 1) . " from (" . $this->_sql . ") crosstab_table group by {$group} {$order}";
if ($this->_options["SHOW_SQL"]) {
print $sql;
}
return $sql;
}
