[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4   * Joomla! Content Management System
   5   *
   6   * @copyright  (C) 2010 Open Source Matters, Inc. <https://www.joomla.org>
   7   * @license    GNU General Public License version 2 or later; see LICENSE.txt
   8   */
   9  
  10  namespace Joomla\CMS\Form\Field;
  11  
  12  use Joomla\CMS\Factory;
  13  use Joomla\CMS\HTML\HTMLHelper;
  14  use Joomla\CMS\Language\Text;
  15  use Joomla\Database\DatabaseQuery;
  16  use Joomla\Database\Exception\ExecutionFailureException;
  17  
  18  // phpcs:disable PSR1.Files.SideEffects
  19  \defined('JPATH_PLATFORM') or die;
  20  // phpcs:enable PSR1.Files.SideEffects
  21  
  22  /**
  23   * Supports a custom SQL select list
  24   *
  25   * @since  1.7.0
  26   */
  27  class SqlField extends ListField
  28  {
  29      /**
  30       * The form field type.
  31       *
  32       * @var    string
  33       * @since  1.7.0
  34       */
  35      public $type = 'SQL';
  36  
  37      /**
  38       * The keyField.
  39       *
  40       * @var    string
  41       * @since  3.2
  42       */
  43      protected $keyField;
  44  
  45      /**
  46       * The valueField.
  47       *
  48       * @var    string
  49       * @since  3.2
  50       */
  51      protected $valueField;
  52  
  53      /**
  54       * The translate.
  55       *
  56       * @var    boolean
  57       * @since  3.2
  58       */
  59      protected $translate = false;
  60  
  61      /**
  62       * The query.
  63       *
  64       * @var    string
  65       * @since  3.2
  66       */
  67      protected $query;
  68  
  69      /**
  70       * Method to get certain otherwise inaccessible properties from the form field object.
  71       *
  72       * @param   string  $name  The property name for which to get the value.
  73       *
  74       * @return  mixed  The property value or null.
  75       *
  76       * @since   3.2
  77       */
  78      public function __get($name)
  79      {
  80          switch ($name) {
  81              case 'keyField':
  82              case 'valueField':
  83              case 'translate':
  84              case 'query':
  85                  return $this->$name;
  86          }
  87  
  88          return parent::__get($name);
  89      }
  90  
  91      /**
  92       * Method to set certain otherwise inaccessible properties of the form field object.
  93       *
  94       * @param   string  $name   The property name for which to set the value.
  95       * @param   mixed   $value  The value of the property.
  96       *
  97       * @return  void
  98       *
  99       * @since   3.2
 100       */
 101      public function __set($name, $value)
 102      {
 103          switch ($name) {
 104              case 'keyField':
 105              case 'valueField':
 106              case 'translate':
 107              case 'query':
 108                  $this->$name = (string) $value;
 109                  break;
 110  
 111              default:
 112                  parent::__set($name, $value);
 113          }
 114      }
 115  
 116      /**
 117       * Method to attach a Form object to the field.
 118       *
 119       * @param   \SimpleXMLElement  $element  The SimpleXMLElement object representing the `<field>` tag for the form field object.
 120       * @param   mixed              $value    The form field value to validate.
 121       * @param   string             $group    The field name group control value. This acts as an array container for the field.
 122       *                                       For example if the field has name="foo" and the group value is set to "bar" then the
 123       *                                       full field name would end up being "bar[foo]".
 124       *
 125       * @return  boolean  True on success.
 126       *
 127       * @see     FormField::setup()
 128       * @since   3.2
 129       */
 130      public function setup(\SimpleXMLElement $element, $value, $group = null)
 131      {
 132          $return = parent::setup($element, $value, $group);
 133  
 134          if ($return) {
 135              // Check if its using the old way
 136              $this->query = (string) $this->element['query'];
 137  
 138              if (empty($this->query)) {
 139                  // Get the query from the form
 140                  $query    = array();
 141                  $defaults = array();
 142  
 143                  $sql_select = (string) $this->element['sql_select'];
 144                  $sql_from   = (string) $this->element['sql_from'];
 145  
 146                  if ($sql_select && $sql_from) {
 147                      $query['select'] = $sql_select;
 148                      $query['from']   = $sql_from;
 149                      $query['join']   = (string) $this->element['sql_join'];
 150                      $query['where']  = (string) $this->element['sql_where'];
 151                      $query['group']  = (string) $this->element['sql_group'];
 152                      $query['order']  = (string) $this->element['sql_order'];
 153  
 154                      // Get the filters
 155                      $filters = isset($this->element['sql_filter']) ? explode(',', $this->element['sql_filter']) : '';
 156  
 157                      // Get the default value for query if empty
 158                      if (\is_array($filters)) {
 159                          foreach ($filters as $filter) {
 160                              $name   = "sql_default_{$filter}";
 161                              $attrib = (string) $this->element[$name];
 162  
 163                              if (!empty($attrib)) {
 164                                  $defaults[$filter] = $attrib;
 165                              }
 166                          }
 167                      }
 168  
 169                      // Process the query
 170                      $this->query = $this->processQuery($query, $filters, $defaults);
 171                  }
 172              }
 173  
 174              $this->keyField   = (string) $this->element['key_field'] ?: 'value';
 175              $this->valueField = (string) $this->element['value_field'] ?: (string) $this->element['name'];
 176              $this->translate  = (string) $this->element['translate'] ?: false;
 177              $this->header     = (string) $this->element['header'] ?: false;
 178          }
 179  
 180          return $return;
 181      }
 182  
 183      /**
 184       * Method to process the query from form.
 185       *
 186       * @param   array   $conditions  The conditions from the form.
 187       * @param   string  $filters     The columns to filter.
 188       * @param   array   $defaults    The defaults value to set if condition is empty.
 189       *
 190       * @return  DatabaseQuery  The query object.
 191       *
 192       * @since   3.5
 193       */
 194      protected function processQuery($conditions, $filters, $defaults)
 195      {
 196          // Get the database object.
 197          $db = $this->getDatabase();
 198  
 199          // Get the query object
 200          $query = $db->getQuery(true);
 201  
 202          // Select fields
 203          $query->select($conditions['select']);
 204  
 205          // From selected table
 206          $query->from($conditions['from']);
 207  
 208          // Join over the groups
 209          if (!empty($conditions['join'])) {
 210              $query->join('LEFT', $conditions['join']);
 211          }
 212  
 213          // Where condition
 214          if (!empty($conditions['where'])) {
 215              $query->where($conditions['where']);
 216          }
 217  
 218          // Group by
 219          if (!empty($conditions['group'])) {
 220              $query->group($conditions['group']);
 221          }
 222  
 223          // Process the filters
 224          if (\is_array($filters)) {
 225              $html_filters = Factory::getApplication()->getUserStateFromRequest($this->context . '.filter', 'filter', array(), 'array');
 226  
 227              foreach ($filters as $k => $value) {
 228                  if (!empty($html_filters[$value])) {
 229                      $escape = $db->quote($db->escape($html_filters[$value]), false);
 230  
 231                      $query->where("{$value} = {$escape}");
 232                  } elseif (!empty($defaults[$value])) {
 233                      $escape = $db->quote($db->escape($defaults[$value]), false);
 234  
 235                      $query->where("{$value} = {$escape}");
 236                  }
 237              }
 238          }
 239  
 240          // Add order to query
 241          if (!empty($conditions['order'])) {
 242              $query->order($conditions['order']);
 243          }
 244  
 245          return $query;
 246      }
 247  
 248      /**
 249       * Method to get the custom field options.
 250       * Use the query attribute to supply a query to generate the list.
 251       *
 252       * @return  array  The field option objects.
 253       *
 254       * @since   1.7.0
 255       */
 256      protected function getOptions()
 257      {
 258          $options = array();
 259  
 260          // Initialize some field attributes.
 261          $key   = $this->keyField;
 262          $value = $this->valueField;
 263          $header = $this->header;
 264  
 265          if ($this->query) {
 266              // Get the database object.
 267              $db = $this->getDatabase();
 268  
 269              // Set the query and get the result list.
 270              $db->setQuery($this->query);
 271  
 272              try {
 273                  $items = $db->loadObjectList();
 274              } catch (ExecutionFailureException $e) {
 275                  Factory::getApplication()->enqueueMessage(Text::_('JERROR_AN_ERROR_HAS_OCCURRED'), 'error');
 276              }
 277          }
 278  
 279          // Add header.
 280          if (!empty($header)) {
 281              $header_title = Text::_($header);
 282              $options[] = HTMLHelper::_('select.option', '', $header_title);
 283          }
 284  
 285          // Build the field options.
 286          if (!empty($items)) {
 287              foreach ($items as $item) {
 288                  if ($this->translate == true) {
 289                      $options[] = HTMLHelper::_('select.option', $item->$key, Text::_($item->$value));
 290                  } else {
 291                      $options[] = HTMLHelper::_('select.option', $item->$key, $item->$value);
 292                  }
 293              }
 294          }
 295  
 296          // Merge any additional options in the XML definition.
 297          $options = array_merge(parent::getOptions(), $options);
 298  
 299          return $options;
 300      }
 301  }