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