/plugins/jojo_core/classes/Jojo/Table.php
PHP | 582 lines | 425 code | 69 blank | 88 comment | 110 complexity | f66ec02cfeca9300bc39242050e940ba MD5 | raw file
Possible License(s): LGPL-2.1, BSD-3-Clause, LGPL-2.0, CC-BY-SA-3.0, MIT
- <?php
- /**
- * Jojo CMS
- * ================
- *
- * Copyright 2007-2008 Harvey Kane <code@ragepank.com>
- * Copyright 2007-2008 Michael Holt <code@gardyneholt.co.nz>
- * Copyright 2007 Melanie Schulz <mel@gardyneholt.co.nz>
- *
- * See the enclosed file license.txt for license information (LGPL). If you
- * did not receive this file, see http://www.fsf.org/copyleft/lgpl.html.
- *
- * @author Harvey Kane <code@ragepank.com>
- * @author Michael Cochrane <mikec@jojocms.org>
- * @author Melanie Schulz <mel@gardyneholt.co.nz>
- * @license http://www.fsf.org/copyleft/lgpl.html GNU Lesser General Public License
- * @link http://www.jojocms.org JojoCMS
- * @package jojo_core
- */
- class Jojo_Table {
- /* The name of the database table */
- private $table;
- /* The id of the current record */
- private $currentrecord;
- /* The options for the table data for this table */
- private $tabledata;
- /* Object of type Jojo_Field representing each field in this table */
- private $fields;
- /* cached value of the first permissions field in table */
- private $permsfield;
- /* Constructor */
- function __construct($table, $id = -1, $index = '')
- {
- $this->table = $table;
- /* Get info about this table */
- $rows = Jojo::selectQuery("SELECT * FROM {tabledata} WHERE td_name = ?", array($table));
- if (isset($rows[0])) {
- foreach($rows[0] as $k => $v) {
- $k = str_replace('td_', '', $k);
- $this->setOption($k, $v);
- }
- }
- /* Ensure options have values */
- if (!$this->getOption('displayfield')) {
- $this->setOption('displayfield', $this->getOption('primarykey'));
- }
- if (!$this->getOption('displayname')) {
- $this->setOption('displayname', $this->getOption('name'));
- }
- /* Get details about the fields in this table */
- $fieldlist = Jojo::selectQuery("SELECT * FROM {fielddata} WHERE fd_table = ? ORDER BY fd_order, fielddataid", array($table));
- $this->fields = array();
- foreach ($fieldlist as $field) {
- $this->fields[$field['fd_field']] = Jojo_Field::factory($field['fd_type'], $field, $this);
- if (!isset($this->permsfield) && ($field['fd_type'] == 'permissions')) {
- $this->permsfield = $field['fd_field'];
- }
- }
- if ($id > -1) {
- $this->getRecord($id);
- }
- }
- /**
- * Retrieve specific record in the database
- */
- function getRecord($id)
- {
- /* Create the query */
- $this->currentrecord = $id;
- if ($this->getOption('displayfield')) {
- $query = sprintf("SELECT *, %s AS DISPLAYFIELDVALUE FROM {%s} WHERE `%s` = ? LIMIT 1", $this->getOption('displayfield'), $this->table, $this->getOption('primarykey'));
- } else {
- $query = sprintf("SELECT * FROM {%s} WHERE `%s` = ? LIMIT 1", $this->table, $this->getOption('primarykey'));
- }
- /* Fetch the record from the database */
- $fieldvalues = Jojo::selectRow($query, array($id));
- /* Set all the fields to their values */
- if ($fieldvalues) {
- foreach ($fieldvalues as $k => $v) {
- if ($k == 'DISPLAYFIELDVALUE') {
- $this->setOption('displayvalue', $v);
- } else {
- $this->fields[$k]->setValueFromDB($v);
- }
- }
- }
- }
- /**
- * Return the ID of the current record
- */
- function getRecordID()
- {
- return $this->currentrecord;
- }
- /* Return the name of this table */
- function getTableName()
- {
- return $this->table;
- }
- /* Return the name of the first permissions field */
- function getPermsField()
- {
- if (isset($this->permsfield)) return $this->permsfield;
- foreach ($this->fields as $fname => $field) {
- //if (strtolower(get_class($field)) == 'jojo_field_permissions') {
- if ($field->fd_type == 'permissions') {
- $this->permsfield = $fname;
- return $fname;
- }
- }
- return false;
- }
- /* Return the Default Permissions for this table */
- function getDefaultPermissions()
- {
- static $cache;
- if (!is_array($cache)) {
- preg_match_all("/([a-zA-Z]+)\.([a-zA-Z]+)[\s=]+([01])+/", $this->getOption('defaultpermissions'), $parts);
- $cache = array();
- foreach ($parts[0] as $k => $v) {
- $cache[$parts[1][$k]][$parts[2][$k]] = ($parts[3][$k] == 1);
- }
- }
- return $cache;
- }
- /* Return HTML view of the current record in this table */
- function getHTML($mode = 'view')
- {
- $html = '';
- $fieldHTML = array();
- foreach($this->fields as $fieldname => $field) {
- $fieldHTML[$fieldname] = array(
- 'name' => $field->getDisplayName(),
- 'tabname' => $field->getOption('tabname'),
- 'showlabel' => $field->getOption('showlabel'),
- 'html' => $field->getHTML($mode),
- 'js' => $field->getJS($mode),
- 'value' => $field->getValue(),
- 'error' => $field->getError(),
- 'type' => $field->getOption('type'),
- 'required' => $field->getOption('required'),
- 'flags' => $field->getOption('flags'),
- 'privacy' => $field->getPrivacy(),
- );
- }
- return $fieldHTML;
- }
- /* Return HTML view of the current record in this table, for a single field only */
- function getFieldHTML($fieldname, $mode = 'view')
- {
- $field = $this->fields[$fieldname];
- $fieldHTML = array(
- 'name' => $field->getDisplayName(),
- 'tabname' => $field->getOption('tabname'),
- 'showlabel' => $field->getOption('showlabel'),
- 'html' => $field->getHTML($mode),
- 'error' => $field->getError(),
- 'type' => $field->getOption('type'),
- 'required' => $field->getOption('required'),
- );
- return $fieldHTML;
- }
- /* Return an array of all the field names */
- function getFieldNames()
- {
- $names = array();
- foreach($this->fields as $fieldname => $field) {
- $names[] = $fieldname;
- }
- return $names;
- }
- /* Check all the fields and ensure there are no errors with any of them */
- function fieldErrors()
- {
- $errors = array();
- foreach($this->fields as $fieldname => $field) {
- $valid = $field->validate();
- if ($valid !== true) {
- $errors[$fieldname] = sprintf('%s: %s', $field->getDisplayName(), $valid);
- }
- }
- if (count($errors) > 0) {
- return $errors;
- }
- return false;
- }
- /* Save this record to the database */
- function saveRecord()
- {
- $result = '';
- /* For tables with varchar based primary keys */
- $newrecord = true;
- if ($this->currentrecord > 0) {
- $newrecord = false;
- } else {
- $sqltype = Jojo::getMySQLType($this->getTableName(), $this->getOption('primarykey'));
- if (strpos($sqltype,'varchar') !== false && $this->currentrecord != '') {
- $newrecord = false;
- }
- }
- /* Create SQL query */
- if (!$newrecord) {
- $query = "UPDATE {" . $this->table . "} SET ";
- } else {
- $query = "INSERT INTO {" . $this->table . "} SET ";
- }
- /* Add all the values */
- $values = array();
- foreach ($this->fields as $fieldname => $f) {
- $value = $this->fields[$fieldname]->getValue();
- switch ($this->fields[$fieldname]->getOption('type')) {
- case 'readonly':
- break;
- case 'permissions':
- /* permissions fields are stared as an array, but must be converted to text for the SQL */
- if (is_array($value)) {
- $strvalue = '';
- foreach ($value as $k => $v) {
- $strvalue .= "$k = $v\n";
- }
- } else {
- $strvalue = $value;
- }
- $query .= sprintf(' `%s` = ?,', $fieldname);
- $values[] = $strvalue;
- break;
- default:
- $query .= sprintf(' `%s` = ?,', $fieldname);
- $values[] = $value;
- break;
- }
- }
- $query = rtrim($query, ', ');
- if (!$newrecord) {
- /* Update record */
- $query .= " WHERE `" . $this->getOption('primarykey') . "`= ? LIMIT 1";
- $values[] = $this->currentrecord;
- $res = Jojo::updateQuery($query, $values);
- if ($res === false) {
- return "An error occured updating the record.";
- }
- } else {
- /* Insert new record */
- $this->currentrecord = Jojo::insertQuery($query, $values);
- }
- /* Output result to the user */
- if (!$newrecord) {
- $result = $this->getOption('displayname') . " updated.";
- } else {
- $result = "A new " . $this->getOption('displayname') . " has been added. ID#" . $this->currentrecord;
- }
- /* Run any post-save procedures */
- foreach ($this->fields as $k => $f) {
- $this->fields[$k]->afterSave();
- }
- return $result;
- }
- function deleteRecord()
- {
- global $_USERID;
- /* Create the query */
- $query = sprintf("DELETE FROM {%s} WHERE `%s`= ? LIMIT 1",
- $this->table,
- $this->getOption('primarykey')
- );
- $values = array($this->currentrecord);
- /* Delete the record */
- if (Jojo::deleteQuery($query, $values)) {
- /* Run any post-delete procedures */
- $res = true;
- foreach ($this->fields as $f) {
- $res = $res && $f->onDelete();
- }
- return $res;
- }
- return false;
- }
- /* Set the value of an option for this table */
- function setOption($option, $value)
- {
- $this->tabledata[$option] = $value;
- }
- /* Return the value of an option for this table from table data */
- function getOption($option)
- {
- return isset($this->tabledata[$option]) ? $this->tabledata[$option] : false;
- }
- /**
- * Return a field by name
- */
- function getField($field)
- {
- return (isset($this->fields[$field])) ? $this->fields[$field] : false;
- }
- /**
- * Return the first field of a matching type
- */
- function getFieldByType($type)
- {
- foreach ($this->fields as $f) {
- if ($f instanceof $type) {
- return $f;
- }
- }
- return false;
- }
- /* Set the value of a field */
- function setFieldValue($field, $value)
- {
- if (isset($this->fields[$field])) {
- $this->fields[$field]->setPostData($value);
- return $this->fields[$field]->setValue($value);
- }
- return false;
- }
- /* Get the value of a field */
- function getFieldValue($field)
- {
- if (isset($this->fields[$field])) {
- return $this->fields[$field]->getValue();
- }
- return false;
- }
- /* Return an instance of table */
- static function &singleton($table = null)
- {
- static $instances;
- if (!isset($instances)) {
- $instances = array();
- }
- if (empty($instances[$table])) {
- $instances[$table] = new Jojo_table($table);
- }
- return $instances[$table];
- }
- /* DTree with ajax needs to be treated carefully, because it returns script, not HTML */
- function createlist($menutype = "tree", $ajax = false, $prefix = 'edit', $selectednode=false)
- {
- if ($menutype == "auto" && ($this->getOption('parentfield') || $this->getOption('group1'))) {
- $menutype = "tree";
- } elseif ($menutype == "auto") {
- $menutype = 'list';
- }
- if ($menutype == 'tree') {
- Jojo::assignTemplateVar('draggable', ($this->getOption('group1') || $this->getOption('parentfield')) && $this->getFieldByType('Jojo_Field_Order'));
- Jojo::assignTemplateVar('table', $this->table);
- if (Jojo::fieldExists($this->table, 'language')) {
- Jojo::assignTemplateVar('table_languages', Jojo::selectAssoc('SELECT DISTINCT l.tag, l.description FROM {tag_language} l WHERE l.tag IN (SELECT DISTINCT language FROM {' . $this->table . '}) OR l.tag IN (SELECT language FROM {site_language}) ORDER BY l.description'));
- }
- Jojo::assignTemplateVar('displayname', $this->getOption('displayname'));
- return Jojo::fetchTemplate('admin/edit-ajaxtree.tpl');
- } elseif ($menutype == 'list' || $menutype == 'recursivePath' || $menutype == 'array') {
- global $_USERGROUPS;
- $idfield = $this->getOption('primarykey');
- $displayfield = Jojo::either($this->getOption('displayfield'), $this->getOption('primarykey'));
- $parentfield = Jojo::either($this->getOption('parentfield'), $this->getOption('group1'), "'0'");
- $categorytable = $this->getOption('categorytable');
- $categoryfield = Jojo::either($this->getOption('categoryfield'), $this->getOption('group1'), "'0'");
- $orderbyfield = $this->getOption('orderbyfields');
- $group1field = $this->getOption('group1');
- $group2field = $this->getOption('group2');
- $golivefield = $this->getOption('golivefield');
- $expiryfield = $this->getOption('expiryfield');
- $activefield = $this->getOption('activefield');
- //filter results
- $datafilter = Jojo::either($this->getOption('filterby'), '1');
- $groupownerfilter = '';
- if ($this->getOption('groupowner') != '') {
- $groupownerfilter = ' AND ( ';
- foreach ($_USERGROUPS as $g) {
- $groupownerfilter .= "(" . $this->getOption('groupowner') . "='" . $g . "') OR";
- }
- $groupownerfilter .= " " . $this->getOption('groupowner') . "='' )";
- }
- if (is_array($_USERGROUPS) && in_array('admin', $_USERGROUPS)) {
- $groupownerfilter = '';
- }
- $rolloverfield = Jojo::either($this->getOption('rolloverfield'), "''");
- //this will be used for output
- $html = "";
- // - this is used to pull down the main groupings - takes an extra query
- if ($this->getOption('group2') != "") {
- // Done in main loop below, no extra query needed.
- } elseif ($this->getOption('group1') != "") {
- //Layer1 represents table structure where the first level is the grouping, then individual records underneath
- $layer1 = Jojo::selectQuery("SELECT " . $this->getOption('group1') . " FROM {" . $this->table . "} GROUP BY " . $this->getOption('group1') . " ORDER BY " . $this->getOption('group1') . "");
- }
- //Main query
- $query = "SELECT $idfield AS id, $displayfield AS display, $parentfield AS parent, $categoryfield AS categoryfield, $rolloverfield AS rollover "
- . Jojo::onlyIf($group1field, ", " . $group1field . " AS group1")
- . Jojo::onlyIf($group2field, ", " . $group2field . " AS group2")
- . Jojo::onlyIf($golivefield, ", " . $golivefield . " AS golive")
- . Jojo::onlyIf($expiryfield, ", " . $expiryfield . " AS expiry")
- . Jojo::onlyIf($activefield, ", " . $activefield . " AS active")
- . " FROM {" . $this->table . "} WHERE $datafilter " . $groupownerfilter . " ORDER BY " . Jojo::onlyIf($group1field, " " . $group1field . ", ")
- . Jojo::onlyIf($orderbyfield, " " . $orderbyfield . ", ")
- . " display";
- $records = Jojo::selectQuery($query);
- //get the TABLEDATA options for the category
- $catidfield = '';
- $catoptions = Jojo::selectQuery("SELECT * FROM {tabledata} WHERE td_name = ? LIMIT 1", array($categorytable));
- if (isset($catoptions[0])) {
- $catidfield = $catoptions[0]['td_primarykey'];
- $catdisplayfield = Jojo::either($catoptions[0]['td_displayfield'], $catoptions[0]['td_primarykey']);
- $catparentfield = Jojo::either($catoptions[0]['td_parentfield'], $catoptions[0]['td_group1'], "'0'");
- $catorderbyfield = $catoptions[0]['td_orderbyfields'];
- $catrolloverfield = Jojo::either($catoptions[0]['td_rolloverfield'], "'0'");
- $catgroupownerfilter = '';
- if ($catoptions[0]['td_groupowner'] != '') {
- $catgroupownerfilter = ' AND ( ';
- foreach ($_USERGROUPS as $g) {
- $catgroupownerfilter .= "(" . $catoptions[0]['td_groupowner'] . "='" . $g . "') OR";
- }
- $catgroupownerfilter .= " " . $catoptions[0]['td_groupowner'] . "='' )";
- }
- if (is_array($_USERGROUPS) && in_array('admin', $_USERGROUPS)) {
- $catgroupownerfilter = '';
- }
- }
- /* Category query */
- if ($catidfield != '') {
- //TODO: Make this query a join so it hides empty categories (if this would be useful)
- $query = "SELECT $catidfield AS id, $catdisplayfield AS display, $catparentfield AS parent, $catrolloverfield AS rollover
- FROM {" . $categorytable . "}
- WHERE 1 " . $catgroupownerfilter . Jojo::onlyif($catorderbyfield, "ORDER BY " . $catorderbyfield . "");
- $catrecords = Jojo::selectQuery($query);
- }
- $tree = new hktree($this->table);
- /* Add group 1 categories to HKTree */
- $groups = $this->_transpose($records);
- if (isset($groups['group1']) && is_array($groups['group1'])) {
- $groups['group1'] = array_unique($groups['group1']);
- foreach ($groups['group1'] as $group1) {
- $status = 'folder';
- $tree->addnode('c' . $group1, 0, $group1, '', '', "setH1('Loading...');frajax('load', '" . $this->table . "',this.value); return false;", '', '', '', $status);
- }
- }
- /* Add categories to HKTree */
- if (isset($catrecords) && is_array($catrecords)) {
- for ($i = 0; $i < count($catrecords); $i++) {
- $status = 'folder';
- $tree->addnode('c' . $catrecords[$i]['id'], 0, $catrecords[$i]['display'], '', '', '', '', '', '', $status);
- }
- }
- /* Add items to HKTree */
- for ($i = 0; $i < count($records); $i++) {
- if (($records[$i]['categoryfield'] != "") and ($records[$i]['categoryfield'] != "0")) {
- $parent = "c" . $records[$i]['categoryfield'];
- } elseif (isset($records[$i]['group1']) && $records[$i]['group1'] != '') {
- $parent = "'" . strtolower('c' . $records[$i]['group1']) . "'";
- } elseif ($records[$i]['parent'] != "") {
- $parent = "'" . strtolower($records[$i]['parent']) . "'";
- } else {
- $parent = "0";
- }
- $link = $prefix . "/" . $this->table . "/" . $records[$i]['id'] . "/";
- if (!$golivefield && !$expiryfield) {
- $status = '';
- } elseif ($golivefield && $expiryfield) {
- $now = strtotime('now');
- if (($now < $records[$i]['golive']) || (($now > $records[$i]['expiry']) && ($records[$i]['expiry'] > 0))) {
- $status = 'expired';
- } else {
- $status = '';
- }
- } else {
- $status = '';
- }
- if (!empty($activefield) && (($records[$i]['active'] == 'no') || ($records[$i]['active'] == 'inactive') ||($records[$i]['active'] == '0') || ($records[$i]['active'] === 0) || ($records[$i]['active'] == ''))) {
- $status = 'expired';
- }
- /* check permissions */
- global $_USERGROUPS;
- $perms = new Jojo_Permissions();
- $perms->getPermissions($this->table, $records[$i]['id']);
- /*
- A quick hack so that the list type will show record in the list after a frajax save.
- Permissions are only checked on 'page' records, not article / faq / anything else records.
- */
- if (($this->table != 'page') || $perms->hasPerm($_USERGROUPS, 'view')) {
- /* add the node */
- $tree->addnode($records[$i]['id'], $parent, $records[$i]['display'], $link, '', "frajax('load', '" . $this->table . "', '" . $records[$i]['id'] . "'); return false;", '', $records[$i]['rollover'],'',$status);
- }
- }
- if ($selectednode !== false) $tree->selected = $selectednode;
- /* output the tree structure */
- if ($menutype == 'recursivePath') {
- return $tree->recursivePath();
- } elseif ($menutype == 'array') {
- return $tree->printout_array();
- } else {
- $html .= "<form id=\"jump\" name=\"jump\" method=\"post\" action=\"" . $prefix . "/" . $this->table . "/\">\n<select name=\"jumpid\" size=\"25\" style=\"width: 100%\" onchange=\"frajax('load', '" . $this->table . "',this.value); return false;\">\n";
- $html .= $tree->printout_select(10, $this->currentrecord);
- $html .= "</select>";
- $html .= "</form>\n";
- }
- return $html;
- } else {
- return "Unknown menu type selected, select a valid menu type in tabledata.";
- }
- }
- /* Converts the format of a 2D array from $arr[a][b] to $arr[b][a] - used for sorting the array*/
- function _transpose($arr)
- {
- $newarr = array();
- foreach ($arr as $keyx => $valx) {
- foreach ($valx as $keyy => $valy) {
- $newarr[$keyy][$keyx] = $valy;
- }
- }
- return $newarr;
- }
- }