public function getDisplayValue()
{
$value = $this->getValue();
if ($value == "") {
return;
}
$data = reset(SQLDBDataStore::getMulti(array('fields' => array($this->mainModelField, $this->subModelField), 'conditions' => "{$this->name} = '{$value}'")));
return array_shift($data) . " // " . array_shift($data);
}
public function get_multi()
{
$params = json_decode($_REQUEST["params"], true);
$data = SQLDBDataStore::getMulti($params);
if (count($data) > 0) {
http_response_code(200);
$this->output($data);
} elseif (count($data) == 0) {
http_response_code(404);
$this->error('Empty set returned');
}
}
public function setParams($params)
{
$params["limit"] = $this->itemsPerPage;
$params["offset"] = $this->itemsPerPage * $params["page"];
$params["url"] = Application::$prefix . "/system/api/table";
$params["id"] = $this->name;
$params["operations"] = $this->operations;
$params["moreInfo"] = true;
$this->params = $params;
if ($this->useAjax) {
$params['moreInfoOnly'] = true;
}
$this->tableData = SQLDBDataStore::getMulti($params);
}
public function manage()
{
if (isset($_POST['is_form_sent'])) {
$this->postNewNote();
return;
}
$notes = SQLDBDataStore::getMulti(array('fields' => array('system.notes.note_id', 'system.notes.note', 'system.notes.note_time', 'system.users.first_name', 'system.users.last_name'), 'filter' => 'item_type = ? and item_id = ?', 'bind' => [$this->model->package, $params[0]]));
foreach ($notes as $i => $note) {
$attachments = $noteAttachments->getWithField2('note_id', $note['note_id']);
foreach ($attachments as $j => $attachment) {
$attachments[$j]['path'] = PgFileStore::getFilePath($attachment['object_id'], $attachment['description']);
}
$notes[$i]['attachments'] = $attachments;
}
$form = Element::create('Form')->add(Element::create('TextArea', 'Note', 'note'), Element::create('FieldSet', 'Add Attachments')->add(Element::create('UploadField', 'Attachment', 'attachment_1'), Element::create('UploadField', 'Attachment', 'attachment_2'), Element::create('UploadField', 'Attachment', 'attachment_3'), Element::create('UploadField', 'Attachment', 'attachment_4'))->setId('attachments')->setCollapsible(true))->setRenderer('default');
return $this->arbitraryTemplate('lib/controllers/notes.tpl', array('form' => $form->render(), 'notes' => $notes, 'route' => $this->path, 'id' => $params[0]));
}
public function get_multi()
{
$params = json_decode($_REQUEST["params"], true);
$data = SQLDBDataStore::getMulti($params);
return $this->format(array("success" => true, "data" => $data, "status" => 100));
}
public static function getCachedMulti($params, $mode = SQLDatabaseModel::MODE_ASSOC)
{
$key = md5(serialize($params));
if (!isset(SQLDBDataStore::$multiQueryCache[$key])) {
SQLDBDataStore::$multiQueryCache[$key] = SQLDBDataStore::getMulti($params, $mode);
}
return SQLDBDataStore(SQLDBDataStore::$multiQueryCache[$key]);
}
/**
* Default controller action. This is the default action which is executed
* when no action is specified for a given call.
* @see lib/controllers/Controller::getContents()
*/
public function getContents()
{
if (count($this->listFields) > 0) {
$fieldNames = $this->listFields;
} else {
if ($this->app != null) {
$fieldNames = $this->app->xpath("/app:app/app:list/app:field");
$concatenatedLabels = $this->app->xpath("/app:app/app:list/app:field/@label");
} else {
$fieldNames = array();
$keyField = $this->model->getKeyField();
$fieldNames[$keyField] = "{$this->model->package}.{$keyField}";
$fields = $this->model->getFields();
foreach ($fields as $i => $field) {
if ($field["reference"] == "") {
$fieldNames[$i] = $this->model->package . "." . $field["name"];
} else {
$modelInfo = Model::resolvePath($field["reference"]);
$fieldNames[$i] = $modelInfo["model"] . "." . $field["referenceValue"];
}
}
}
}
foreach ($fieldNames as $i => $fieldName) {
$fieldNames[$i] = substr((string) $fieldName, 0, 1) == "." ? $this->redirectedPackage . (string) $fieldName : (string) $fieldName;
}
if (count($this->fieldNames) > 0) {
$fieldNames = $this->fieldNames;
}
if ($this->apiMode === false) {
$this->setupList();
$params["fields"] = $fieldNames;
$params["page"] = 0;
$params["sort_field"] = array(array("field" => $this->model->database . "." . $this->model->getKeyField(), "type" => "DESC"));
$this->table->setParams($params);
$return = '<div id="table-wrapper">' . $this->toolbar->render() . $this->table->render() . '</div>';
} else {
$params["fields"] = $fieldNames;
$params["page"] = 0;
$params["sort_field"] = array(array("field" => $this->model->database . "." . $this->model->getKeyField(), "type" => "DESC"));
$return = json_encode(SQLDBDataStore::getMulti($params));
}
return $return;
}
/**
* A utility function around the SQLDataStore::getMulti() method. This function
* allows for dynamic fields. These dynamic fields are fields which are read
* from a database table.
*
* @param array $params
* @param int $mode
* @return array
* @see SQLDataStore::getMulti()
*/
public static function getReportData($params, $mode = SQLDatabaseModel::MODE_ASSOC)
{
$dynamicFields = array();
if (is_array($params["dynamicHeaders"])) {
foreach ($params["dynamicHeaders"] as $key => $dynamicHeader) {
$info = Model::resolvePath($dynamicHeader);
$headers = Model::load($info["model"]);
$data = $headers->get(array("fields" => array($headers->getKeyField(), $info["field"])), SQLDataBaseModel::MODE_ARRAY);
foreach ($data as $dat) {
$replacement[$dat[1]] = null;
}
$dynamicFields[$key]["replacement"] = $replacement;
$dynamicFields[$key]["headers"] = $data;
$dynamicFields[$key]["field"] = $params["dynamicFields"][$key];
$dynamicFields[$key]["keyField"] = count($params["fields"]);
//array_search($headers->getKeyField(), $params["fields"]);
$dynamicFields[$key]["replaceIndex"] = array_search($params["dynamicFields"][$key], $params["fields"]);
$dynamicFields[$key]["numFields"] = count($data);
$params["fields"][] = $headers->package . "." . $headers->getKeyField();
}
}
$data = SQLDBDataStore::getMulti($params, $mode);
$numData = count($data);
if ($numData == 0) {
return $data;
} elseif ($numData == 1) {
foreach ($dynamicFields as $dynamicField) {
array_splice($data[0], $dynamicField["replaceIndex"], 1, array_pad(array(), $dynamicField["numFields"], $data[0][$dynamicField["replaceIndex"]]));
}
} else {
if (count($dynamicFields) == 0) {
return $data;
}
$keys = array_keys($data[0]);
$numReturnData = 0;
for ($i = 0; $i < $numData;) {
foreach ($dynamicFields as $dynamicField) {
$base = $i;
$returnData[] = $data[$i];
array_splic($returnData[$numReturnData], $dynamicField["replaceIndex"], 1, $dynamicField["replacement"]);
foreach ($dynamicField["headers"] as $header) {
for ($j = 0; $j < $dynamicField["numFields"]; $j++) {
if ($data[$base + $j][$dynamicField["keyField"]] == $header[0]) {
$returnData[$numReturnData][$header[1]] = $data[$base + $j][$keys[$dynamicField["replaceIndex"]]];
break;
}
}
$i++;
}
unset($returnData[$numReturnData][$dynamicField["keyField"]]);
$numReturnData++;
}
}
$data = $returnData;
}
return $data;
}
public function notes($params)
{
$noteAttachments = Model::load('system.note_attachments');
if ($params[1] == 'delete') {
$model = Model::load('system.notes');
$model->delete('note_id', $params[2]);
Application::redirect("{$this->path}/notes/{$params[0]}");
}
if (isset($_POST['is_form_sent'])) {
$model = Model::load('system.notes');
$model->datastore->beginTransaction();
$data = array('note' => $_POST['note'], 'note_time' => time(), 'item_id' => $params[0], 'user_id' => $_SESSION['user_id'], 'item_type' => $this->model->package);
$model->setData($data);
$id = $model->save();
for ($i = 1; $i < 5; $i++) {
$file = $_FILES["attachment_{$i}"];
if ($file['error'] == 0) {
$noteAttachments->setData(array('note_id' => $id, 'description' => $file['name'], 'object_id' => PgFileStore::addFile($file['tmp_name'])));
$noteAttachments->save();
}
}
$model->datastore->endTransaction();
Application::redirect("{$this->urlPath}/notes/{$params[0]}");
}
$notes = SQLDBDataStore::getMulti(array('fields' => array('system.notes.note_id', 'system.notes.note', 'system.notes.note_time', 'system.users.first_name', 'system.users.last_name'), 'conditions' => Model::condition(array('item_type' => $this->model->package, 'item_id' => $params[0]))));
foreach ($notes as $i => $note) {
$attachments = $noteAttachments->getWithField2('note_id', $note['note_id']);
foreach ($attachments as $j => $attachment) {
$attachments[$j]['path'] = PgFileStore::getFilePath($attachment['object_id'], $attachment['description']);
}
$notes[$i]['attachments'] = $attachments;
}
$this->label = "Notes on item";
$form = Element::create('Form')->add(Element::create('TextArea', 'Note', 'note'), Element::create('FieldSet', 'Add Attachments')->add(Element::create('UploadField', 'Attachment', 'attachment_1'), Element::create('UploadField', 'Attachment', 'attachment_2'), Element::create('UploadField', 'Attachment', 'attachment_3'), Element::create('UploadField', 'Attachment', 'attachment_4'))->setId('attachments')->setCollapsible(true))->setRenderer('default');
return $this->arbitraryTemplate(Application::getWyfHome('controllers/notes.tpl'), array('form' => $form->render(), 'notes' => $notes, 'route' => $this->path, 'id' => $params[0]));
}
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);
}
}
}
}