/**
* Recursively generates tables based on grouping parameters. The method
* is called in a nested fashion hence the grouping can also be nested.
*
* @param Array $params
* @return Array
*/
protected function generateTable($report, $params)
{
$groupingField = array_search($params["grouping_fields"][$params["grouping_level"]], $params["fields"]);
$groupingLevel = $params["grouping_level"];
$accumulatedTotals = array();
if (count($this->reportData) == 0) {
return;
}
do {
if ($_REQUEST["grouping_" . ($params["grouping_level"] + 1) . "_newpage"] == "1") {
$report->addPage($_REQUEST["grouping_" . ($params["grouping_level"] + 1) . "_newpage"]);
}
$headingValue = $this->reportData[$this->reportDataIndex][$groupingField];
$this->drawHeading($report, $headingValue);
array_unshift($params["previous_headings"], array($headingValue, $groupingField));
$params["ignored_fields"][] = $groupingField;
if ($params["grouping_fields"][$groupingLevel + 1] == "") {
$data = array();
do {
$continue = true;
$row = $this->reportData[$this->reportDataIndex];
@($data[] = array_values($row));
$this->reportDataIndex++;
foreach ($params["previous_headings"] as $heading) {
if ($heading[0] != $this->reportData[$this->reportDataIndex][$heading[1]]) {
array_shift($params["previous_headings"]);
$continue = false;
break;
}
}
} while ($continue);
$tableDetails = array('headers' => $params['headers'], 'data' => $data, 'params' => $params);
$totals = $this->drawTable($report, $tableDetails);
$params = $tableDetails['params'];
array_pop($params["ignored_fields"]);
} else {
$params["grouping_level"]++;
$totals = $this->generateTable($report, $params);
array_shift($params["previous_headings"]);
$params["grouping_level"]--;
array_pop($params["ignored_fields"]);
}
if ($this->drawTotals && $totals != null) {
$totals[0] = 'Total';
$totalsBox = new TableContent($params["headers"], $totals);
$totalsBox->setAsTotalsBox(true);
$params['widths'] = $this->widths;
$totalsBox->setDataParams($params);
$report->add($totalsBox);
foreach ($totals as $i => $total) {
if ($total === null) {
$accumulatedTotals[$i] = null;
} else {
$accumulatedTotals[$i] += $total;
}
}
}
if ($params["previous_headings"][0][0] != $this->reportData[$this->reportDataIndex][$params["previous_headings"][0][1]]) {
break;
}
} while ($this->reportDataIndex < count($this->reportData));
return $accumulatedTotals;
}
private function generateTableSection($reader, $report)
{
$reader->moveToAttribute("name");
$name = $reader->value;
$tableConditionsArray = array();
if ($reader->moveToAttribute("conditions")) {
$tableConditionsArray[] = $reader->value;
}
if ($this->tableConditions != '') {
$tableConditionsArray[] = $this->tableConditions;
}
$tableConditions = implode(" AND ", $tableConditionsArray);
$fields = $this->xml->xpath("/rapi:report/rapi:table[@name='{$name}']/rapi:fields/rapi:field");
$headers = $this->xml->xpath("/rapi:report/rapi:table[@name='{$name}']/rapi:fields/rapi:field[@label!='']/@label");
$dontJoins = $this->xml->xpath("/rapi:report/rapi:table[@name='{$name}']/rapi:dont_join/rapi:pair");
$ignoredFields = array();
$dataParams["total"] = array();
$hardCodedSorting = array();
$reportGroupingFields = array();
$models = array();
$fieldInfos = array();
// Generate filter conditions
$filters = array();
$filterSummaries = array();
$keyOffset = 0;
foreach ($fields as $key => $field) {
// Load the model for this field if it hasn't been
// loaded already. I have a hunch that this check
// is really not necessary since the model loader
// sort of caches loaded models now.
$modelInfo = Model::resolvePath((string) $field);
if (array_search($modelInfo["model"], array_keys($models)) === false) {
$models[$modelInfo["model"]] = Model::load($modelInfo["model"]);
}
$model = $models[$modelInfo["model"]];
$fieldInfo = reset($model->getFields(array($modelInfo["field"])));
$fieldInfos[(string) $field] = $fieldInfo;
//Ignore fields which are not needed.
if (isset($_REQUEST[$name . "_" . $fieldInfo["name"] . "_ignore"])) {
$ignoredFields[] = $key;
}
if (isset($field["sort"])) {
$sortField = "{$model->database}.{$fieldInfo["name"]}";
$hardCodedSorting[] = array("field" => $sortField, "type" => $field["sort"]);
}
$tableHeaders[] = (string) $field["label"];
switch ($fieldInfo["type"]) {
case "integer":
$dataParams["type"][] = "number";
break;
case "double":
$dataParams["type"][] = "double";
break;
default:
$dataParams["type"][] = "";
}
$dataParams["total"][] = $field["total"] == "true" ? true : false;
$fields[$key] = (string) $field;
$value = $field["value"];
$field = (string) $field;
if (array_search($model->getKeyField(), $this->referencedFields) === false || $fieldInfo["type"] == "double" || $fieldInfo["type"] == "date") {
if ($value != null) {
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}='{$value}'";
continue;
}
switch ($fieldInfo["type"]) {
case "string":
case "text":
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] != "") {
switch ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_option"]) {
case "CONTAINS":
$filterSummaries[] = "{$headers[$key]} containing {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"]}";
$filters[] = $models[$modelInfo["model"]]->getSearch($models[$modelInfo["model"]]->escape($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"]), "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}");
break;
case "EXACTLY":
$filterSummaries[] = "{$headers[$key]} being exactly {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"]}";
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}='" . $models[$modelInfo["model"]]->escape($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"]) . "'";
break;
}
}
break;
case "integer":
case "double":
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"] != "") {
switch ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_option"]) {
case "EQUALS":
$filterSummaries[] = "{$headers[$key]} equals {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]}";
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}='" . $models[$modelInfo["model"]]->escape($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]) . "'";
break;
case "GREATER":
$filterSummaries[] = "{$headers[$key]} greater than {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]}";
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}>'" . $models[$modelInfo["model"]]->escape($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]) . "'";
break;
case "LESS":
$filterSummaries[] = "{$headers[$key]} less than {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]}";
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}<'" . $models[$modelInfo["model"]]->escape($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]) . "'";
break;
case "BETWEEN":
$filterSummaries[] = "{$headers[$key]} between {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]} and {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_end_value"]}";
$filters[] = "({$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}>='" . $models[$modelInfo["model"]]->escape($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_value"]) . "' AND {$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}<='" . $models[$modelInfo["model"]]->escape($_REQUEST[$name . "_" . $fieldInfo["name"] . "_end_value"]) . "')";
break;
}
}
break;
case "reference":
break;
case "datetime":
case "date":
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"] != "") {
switch ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_option"]) {
case "EQUALS":
$filterSummaries[] = "{$headers[$key]} on {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"]}";
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}='" . $models[$modelInfo["model"]]->escape(Utils::stringToDatabaseDate($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"])) . "'";
break;
case "GREATER":
$filterSummaries[] = "{$headers[$key]} after {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"]}";
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}>'" . $models[$modelInfo["model"]]->escape(Utils::stringToDatabaseDate($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"])) . "'";
break;
case "LESS":
$filterSummaries[] = "{$headers[$key]} before {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"]}";
$filters[] = "{$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}<'" . $models[$modelInfo["model"]]->escape(Utils::stringToDatabaseDate($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"])) . "'";
break;
case "BETWEEN":
$filterSummaries[] = "{$headers[$key]} from {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"]} to {$_REQUEST[$name . "_" . $fieldInfo["name"] . "_end_date"]}";
$filters[] = "({$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}>='" . $models[$modelInfo["model"]]->escape(Utils::stringToDatabaseDate($_REQUEST[$name . "_" . $fieldInfo["name"] . "_start_date"])) . "' AND {$models[$modelInfo["model"]]->getDatabase()}.{$fieldInfo["name"]}<='" . $models[$modelInfo["model"]]->escape(Utils::stringToDatabaseDate($_REQUEST[$name . "_" . $fieldInfo["name"] . "_end_date"])) . "')";
break;
}
}
break;
case "enum":
if (count($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"]) >= 1 && $_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"][0] != "") {
$m = $models[$modelInfo["model"]];
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_option"] == "INCLUDE") {
$summary = array();
foreach ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] as $value) {
$summary[] = $fieldInfo["options"][$value];
}
$filterSummaries[] = "{$headers[$key]} being " . implode(", ", $summary);
$condition = array();
foreach ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] as $value) {
if ($value != "") {
$condition[] = "{$m->getDatabase()}.{$fieldInfo["name"]}='" . $m->escape($value) . "'";
}
}
} else {
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_option"] == "EXCLUDE") {
$summary = array();
foreach ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] as $value) {
$summary[] = $fieldInfo["options"][$value];
}
$filterSummaries[] = "{$headers[$key]} excluding " . implode(", ", $summary);
$condition = array();
foreach ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] as $value) {
if ($value != "") {
$condition[] = "{$m->getDatabase()}.{$fieldInfo["name"]}<>'" . $m->escape($value) . "'";
}
}
}
}
if (count($condition) > 0) {
$filters[] = "(" . implode(" OR ", $condition) . ")";
}
}
break;
}
} else {
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] != "") {
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_option"] == "IS_ANY_OF") {
foreach ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] as $value) {
if ($value != "") {
$condition[] = "{$model->getDatabase()}.{$fieldInfo["name"]}='" . $model->escape($value) . "'";
}
}
} else {
if ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_option"] == "IS_NONE_OF") {
foreach ($_REQUEST[$name . "_" . $fieldInfo["name"] . "_value"] as $value) {
if ($value != "") {
$condition[] = "{$model->getDatabase()}.{$fieldInfo["name"]}<>'" . $model->escape($value) . "'";
}
}
}
}
if (count($condition) > 0) {
$filters[] = "(" . implode(" OR ", $condition) . ")";
}
}
}
}
// Generate the various tables taking into consideration grouping
if (count($filterSummaries) > 0) {
$report->filterSummary = new TextContent(str_replace("\\n", " ", implode("\n", $filterSummaries)), 'summary');
$report->add($report->filterSummary);
}
$params = array("fields" => $fields, "conditions" => implode(" AND ", $filters), "headers" => $tableHeaders, "dont_join" => array(), 'total' => $dataParams['total'], 'type' => $dataParams['type']);
foreach ($dontJoins as $pair) {
$params['dont_join'][] = (string) $pair;
}
if ($tableConditions != "") {
$params["conditions"] = $params['conditions'] . ($params['conditions'] != '' ? " AND " : '') . "({$tableConditions})";
}
if ($_REQUEST[$name . "_sorting"] != "") {
array_unshift($hardCodedSorting, array("field" => $_REQUEST[$name . "_sorting"], "type" => $_REQUEST[$name . "_sorting_direction"]));
}
if (is_array($_REQUEST[$name . "_grouping"])) {
foreach ($_REQUEST[$name . "_grouping"] as $postGrouping) {
if ($postGrouping != "") {
$groupingFields = explode(",", $postGrouping);
foreach ($groupingFields as $key => $groupingField) {
$modelInfo = Model::resolvePath($groupingField);
$model = Model::load($modelInfo["model"]);
$groupingFields[$key] = "{$model->database}.{$modelInfo["field"]}";
}
$reportGroupingFields[] = array("field" => $model->datastore->concatenate($groupingFields), "type" => "ASC");
}
}
$hardCodedSorting = array_merge($reportGroupingFields, $hardCodedSorting);
}
$params["sort_field"] = $hardCodedSorting;
if ($_REQUEST[$name . "_limit"] != '') {
$params['limit'] = $_REQUEST[$name . "_limit"];
}
$params["no_num_formatting"] = true;
$this->reportData = SQLDBDataStore::getMulti($params, SQLDatabaseModel::MODE_ARRAY);
unset($params["sort_field"]);
$wparams = $params;
$wparams["global_functions"] = array("LENGTH", "MAX");
$wparams["global_functions_set"] = true;
$this->widths = reset(SQLDBDataStore::getMulti($wparams, SQLDatabaseModel::MODE_ARRAY));
foreach ($tableHeaders as $i => $header) {
foreach (explode("\\n", $header) as $line) {
if (strlen($line) / 2 > $this->widths[$i]) {
$this->widths[$i] = strlen($line) / 2;
}
}
}
$params['ignored_fields'] = $ignoredFields;
if ($_REQUEST[$name . "_grouping"][0] == "") {
$tableDetails = array('headers' => $headers, 'data' => $this->reportData, 'params' => $params, 'totals' => true);
$this->drawTable($report, $tableDetails);
} else {
if ($_REQUEST[$name . "_grouping"][0] != "" && $_REQUEST["grouping_1_summary"] == '1') {
$params["grouping_fields"] = $_REQUEST[$name . "_grouping"];
$params["grouping_level"] = 0;
$params["previous_headings"] = array();
$params["ignored_fields"] = array();
$this->generateSummaryTable($params);
} else {
$params["grouping_fields"] = $_REQUEST[$name . "_grouping"];
$params["grouping_level"] = 0;
$params["previous_headings"] = array();
//$params["ignored_fields"] = array();
$total = $this->generateTable($report, $params);
if (is_array($total) && count($total) > 0) {
$total[0] = $total[0] == "" ? "Overall Total" : $total[0];
$dataParams["widths"] = $this->widths;
$totalTable = new TableContent($tableHeaders, $total);
$totalTable->setDataParams($dataParams);
$totalTable->setAsTotalsBox(true);
$report->add($totalTable);
}
}
}
}
