1: <?php
2: /**
3: * DataTables PHP libraries.
4: *
5: * PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
6: *
7: * @author SpryMedia
8: * @copyright 2016 SpryMedia ( http://sprymedia.co.uk )
9: * @license http://editor.datatables.net/license DataTables Editor
10: * @link http://editor.datatables.net
11: */
12:
13: namespace DataTables\Editor;
14: if (!defined('DATATABLES')) exit();
15:
16: use DataTables;
17:
18: /**
19: * The Options class provides a convenient method of specifying where Editor
20: * should get the list of options for a `select`, `radio` or `checkbox` field.
21: * This is normally from a table that is _left joined_ to the main table being
22: * edited, and a list of the values available from the joined table is shown to
23: * the end user to let them select from.
24: *
25: * `Options` instances are used with the {@link Field::options} method.
26: *
27: * @example
28: * Get a list of options from the `sites` table
29: * <code>
30: * Field::inst( 'users.site' )
31: * ->options( Options::inst()
32: * ->table( 'sites' )
33: * ->value( 'id' )
34: * ->label( 'name' )
35: * )
36: * </code>
37: *
38: * @example
39: * Get a list of options with custom ordering
40: * <code>
41: * Field::inst( 'users.site' )
42: * ->options( Options::inst()
43: * ->table( 'sites' )
44: * ->value( 'id' )
45: * ->label( 'name' )
46: * ->order( 'name DESC' )
47: * )
48: * </code>
49: *
50: * @example
51: * Get a list of options showing the id and name in the label
52: * <code>
53: * Field::inst( 'users.site' )
54: * ->options( Options::inst()
55: * ->table( 'sites' )
56: * ->value( 'id' )
57: * ->label( [ 'name', 'id' ] )
58: * ->render( function ( $row ) {
59: * return $row['name'].' ('.$row['id'].')';
60: * } )
61: * )
62: * </code>
63: */
64: class SearchPaneOptions extends DataTables\Ext {
65: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
66: * Private parameters
67: */
68:
69: /** @var string Table to get the information from */
70: private $_table = null;
71:
72: /** @var string Column name containing the value */
73: private $_value = null;
74:
75: /** @var string[] Column names for the label(s) */
76: private $_label = array();
77:
78: /** @var string[] Column names for left join */
79: private $_leftJoin = array();
80:
81: /** @var callable Callback function to do rendering of labels */
82: private $_renderer = null;
83:
84: /** @var callback Callback function to add where conditions */
85: private $_where = null;
86:
87: /** @var string ORDER BY clause */
88: private $_order = null;
89:
90: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
91: * Public methods
92: */
93:
94: /**
95: * Get / set the column(s) to use as the label value of the options
96: *
97: * @param null|string|string[] $_ null to get the current value, string or
98: * array to get.
99: * @return Options|string[] Self if setting for chaining, array of values if
100: * getting.
101: */
102: public function label ( $_=null )
103: {
104: if ( $_ === null ) {
105: return $this;
106: }
107: else if ( is_string($_) ) {
108: $this->_label = array( $_ );
109: }
110: else {
111: $this->_label = $_;
112: }
113:
114: return $this;
115: }
116:
117: /**
118: * Get / set the ORDER BY clause to use in the SQL. If this option is not
119: * provided the ordering will be based on the rendered output, either
120: * numerically or alphabetically based on the data returned by the renderer.
121: *
122: * @param null|string $_ String to set, null to get current value
123: * @return Options|string Self if setting for chaining, string if getting.
124: */
125: public function order ( $_=null )
126: {
127: return $this->_getSet( $this->_order, $_ );
128: }
129:
130: /**
131: * Get / set the label renderer. The renderer can be used to combine
132: * multiple database columns into a single string that is shown as the label
133: * to the end user in the list of options.
134: *
135: * @param null|callable $_ Function to set, null to get current value
136: * @return Options|callable Self if setting for chaining, callable if
137: * getting.
138: */
139: public function render ( $_=null )
140: {
141: return $this->_getSet( $this->_renderer, $_ );
142: }
143:
144: /**
145: * Get / set the database table from which to gather the options for the
146: * list.
147: *
148: * @param null|string $_ String to set, null to get current value
149: * @return Options|string Self if setting for chaining, string if getting.
150: */
151: public function table ( $_=null )
152: {
153: return $this->_getSet( $this->_table, $_ );
154: }
155:
156: /**
157: * Get / set the column name to use for the value in the options list. This
158: * would normally be the primary key for the table.
159: *
160: * @param null|string $_ String to set, null to get current value
161: * @return Options|string Self if setting for chaining, string if getting.
162: */
163: public function value ( $_=null )
164: {
165: return $this->_getSet( $this->_value, $_ );
166: }
167:
168: /**
169: * Get / set the method to use for a WHERE condition if it is to be
170: * applied to the query to get the options.
171: *
172: * @param null|callable $_ Function to set, null to get current value
173: * @return Options|callable Self if setting for chaining, callable if
174: * getting.
175: */
176: public function where ( $_=null )
177: {
178: return $this->_getSet( $this->_where, $_ );
179: }
180:
181: /**
182: * Get / set the array values used for a leftJoin condition if it is to be
183: * applied to the query to get the options.
184: *
185: * @param string $table to get the information from
186: * @param string $field1 the first field to get the information from
187: * @param string $operator the operation to perform on the two fields
188: * @param string $field2 the second field to get the information from
189: * @return self
190: */
191: public function leftJoin ( $table, $field1, $operator, $field2 )
192: {
193: $this->_leftJoin[] = array(
194: "table" => $table,
195: "field1" => $field1,
196: "field2" => $field2,
197: "operator" => $operator
198: );
199:
200: return $this;
201: }
202:
203: /**
204: * Adds all of the where conditions to the desired query
205: *
206: * @param string $query the query being built
207: * @return self
208: */
209: private function _get_where ( $query )
210: {
211: for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
212: if ( is_callable( $this->_where[$i] ) ) {
213: $this->_where[$i]( $query );
214: }
215: else {
216: $query->where(
217: $this->_where[$i]['key'],
218: $this->_where[$i]['value'],
219: $this->_where[$i]['op']
220: );
221: }
222: }
223: return $this;
224: }
225:
226: /**
227: * Adds a join for all of the leftJoin conditions to the
228: * desired query, using the appropriate values.
229: *
230: * @param string $query the query being built
231: * @return self
232: */
233: private function _perform_left_join ( $query )
234: {
235: if ( count($this->_leftJoin) ) {
236: for ( $i=0, $ien=count($this->_leftJoin) ; $i<$ien ; $i++ ) {
237: $join = $this->_leftJoin[$i];
238: $query->join( $join['table'], $join['field1'].' '.$join['operator'].' '.$join['field2'], 'LEFT' );
239: }
240: }
241: return $this;
242: }
243:
244: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
245: * Internal methods
246: */
247:
248: /**
249: * Execute the options (i.e. get them)
250: *
251: * @param Database $db Database connection
252: * @return array List of options
253: * @internal
254: */
255: public function exec ( $field, $editor, $http, $fields, $leftJoinIn )
256: {
257: // If the value is not yet set then set the variable to be the field name
258: if ( $this->_value == null) {
259: $value = $field->dbField();
260: }
261: else {
262: $value = $this->_value;
263: }
264:
265: // If the table is not yet set then set the table variable to be the same as editor
266: if ( $this->_table == null) {
267: $table = $editor->table();
268: }
269: else {
270: $table = $this->_table;
271: }
272:
273: // If the label value has not yet been set then just set it to be the same as value
274: if ( $this->_label == null ) {
275: $label = $value;
276: }
277: else {
278: $label = $this->_label[0];
279: }
280:
281: // Set the database from editor
282: $db = $editor->db();
283:
284: $formatter = $this->_renderer;
285:
286: // We need a default formatter if one isn't provided
287: if ( ! $formatter ) {
288: $formatter = function ( $str ) {
289: return $str;
290: };
291: }
292:
293: // Set up the join variable so that it will fit nicely later
294: $leftJoin = gettype($this->_leftJoin) === 'array' ?
295: $this->_leftJoin :
296: [$this->_leftJoin];
297:
298: foreach($leftJoinIn as $lj) {
299: $found = false;
300: foreach($leftJoin as $lje) {
301: if($lj['table'] === $lje['table']) {
302: $found = true;
303: }
304: }
305: if(!$found) {
306: array_push($leftJoin, $lj);
307: }
308: }
309:
310: // Set the query to get the current counts for viewTotal
311: $query = $db
312: ->query('select')
313: ->table( $table );
314:
315: if ( $field->apply('get') && $field->getValue() === null ) {
316: $query->get( $value." as value", "COUNT(*) as count");
317: $query->group_by( $value);
318: }
319:
320: // If a join is required then we need to add the following to the query
321: // print_r($leftJoin);
322: if (count($leftJoin) > 0){
323: foreach($leftJoin as $lj) {
324: $query->join( $lj['table'], $lj['field1'].' '.$lj['operator'].' '.$lj['field2'], 'LEFT' );
325: }
326: }
327:
328: // Construct the where queries based upon the options selected by the user
329: if( isset($http['searchPanes']) ) {
330: foreach ($fields as $fieldOpt) {
331: if( isset($http['searchPanes'][$fieldOpt->name()])){
332: $query->where( function ($q) use ($fieldOpt, $http) {
333: for($j=0 ; $j<count($http['searchPanes'][$fieldOpt->name()]) ; $j++){
334: $q->or_where( $fieldOpt->dbField(), $http['searchPanes'][$fieldOpt->name()][$j], '=' );
335: }
336: });
337: }
338: }
339: }
340:
341: // print_r($query);
342:
343: $res = $query
344: ->exec()
345: ->fetchAll();
346:
347: // Get the data for the pane options
348: $q = $db
349: ->query('select')
350: ->table( $table )
351: ->get( $label." as label", $value." as value", "COUNT(*) as total" )
352: ->group_by( $value )
353: ->where( $this->_where );
354:
355: // If a join is required then we need to add the following to the query
356: if (count($leftJoin) > 0){
357: foreach($leftJoin as $lj) {
358: $q->join( $lj['table'], $lj['field1'].' '.$lj['operator'].' '.$lj['field2'], 'LEFT' );
359: }
360: }
361:
362: if ( $this->_order ) {
363: // For cases where we are ordering by a field which isn't included in the list
364: // of fields to display, we need to add the ordering field, due to the
365: // select distinct.
366: $orderFields = explode( ',', $this->_order );
367:
368: for ( $i=0, $ien=count($orderFields) ; $i<$ien ; $i++ ) {
369: $field = strtolower( $orderFields[$i] );
370: $field = str_replace( ' asc', '', $field );
371: $field = str_replace( ' desc', '', $field );
372: $field = trim( $field );
373:
374: if ( ! in_array( $field, $fields ) ) {
375: $q->get( $field );
376: }
377: }
378:
379: $q->order( $this->_order );
380: }
381:
382: // print_r($q);
383:
384: $rows = $q
385: ->exec()
386: ->fetchAll();
387:
388: // Create the output array
389: $out = array();
390:
391: for ( $i=0, $ien=count($rows) ; $i<$ien ; $i++ ) {
392: $set = false;
393: for( $j=0 ; $j<count($res) ; $j ++) {
394: if($res[$j]['value'] == $rows[$i]['value']){
395: $out[] = array(
396: "label" => $formatter($rows[$i]['label']),
397: "total" => $rows[$i]['total'],
398: "value" => $rows[$i]['value'],
399: "count" => $res[$j]['count']
400: );
401: $set = true;
402: }
403: }
404: if(!$set) {
405: $out[] = array(
406: "label" => $formatter($rows[$i]['label']),
407: "total" => $rows[$i]['total'],
408: "value" => $rows[$i]['value'],
409: "count" => 0
410: );
411: }
412:
413: }
414:
415: // Only sort if there was no SQL order field
416: if ( ! $this->_order ) {
417: usort( $out, function ( $a, $b ) {
418: return is_numeric($a['label']) && is_numeric($b['label']) ?
419: ($a['label']*1) - ($b['label']*1) :
420: strcmp( $a['label'], $b['label'] );
421: } );
422: }
423:
424: return $out;
425: }
426: }
427: