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: if(count($this->_leftJoin) > 0){
295: $join = $this->_leftJoin[0];
296: }
297: else {
298: $join = $this->_leftJoin;
299: }
300:
301: // Set up the left join varaible so that it will fit nicely later
302: if(count($leftJoinIn) > 0) {
303: $leftJoin = $leftJoinIn[0];
304: }
305: else {
306: $leftJoin = $leftJoinIn;
307: }
308:
309: // Set the query to get the current counts for viewTotal
310: $query = $db
311: ->query('select')
312: ->table( $table );
313:
314: if ( $field->apply('get') && $field->getValue() === null ) {
315: $query->get( $value." as value", "COUNT(*) as count");
316: $query->group_by( $value);
317: }
318:
319: // If a join is required then we need to add the following to the query
320: if (count($leftJoin) > 0){
321: $query->join( $leftJoin['table'], $leftJoin['field1'].' '.$leftJoin['operator'].' '.$leftJoin['field2'], 'LEFT' );
322: }
323:
324: // Construct the where queries based upon the options selected by the user
325: if( isset($http['searchPanes']) ) {
326: foreach ($fields as $fieldOpt) {
327: if( isset($http['searchPanes'][$fieldOpt->name()])){
328: $query->where( function ($q) use ($fieldOpt, $http) {
329: for($j=0 ; $j<count($http['searchPanes'][$fieldOpt->name()]) ; $j++){
330: $q->or_where( $fieldOpt->dbField(), $http['searchPanes'][$fieldOpt->name()][$j], '=' );
331: }
332: });
333: }
334: }
335: }
336:
337: $res = $query
338: ->exec()
339: ->fetchAll();
340:
341: // Get the data for the pane options
342: $q = $db
343: ->query('select')
344: ->table( $table )
345: ->get( $label." as label", $value." as value", "COUNT(*) as total" )
346: ->group_by( $value )
347: ->where( $this->_where );
348:
349: // If a join is required then we need to add the following to the query
350: if (count($join) > 0){
351: $q->join( $join['table'], $join['field1'].' '.$join['operator'].' '.$join['field2'], 'LEFT' );
352: }
353:
354: if ( $this->_order ) {
355: // For cases where we are ordering by a field which isn't included in the list
356: // of fields to display, we need to add the ordering field, due to the
357: // select distinct.
358: $orderFields = explode( ',', $this->_order );
359:
360: for ( $i=0, $ien=count($orderFields) ; $i<$ien ; $i++ ) {
361: $field = strtolower( $orderFields[$i] );
362: $field = str_replace( ' asc', '', $field );
363: $field = str_replace( ' desc', '', $field );
364: $field = trim( $field );
365:
366: if ( ! in_array( $field, $fields ) ) {
367: $q->get( $field );
368: }
369: }
370:
371: $q->order( $this->_order );
372: }
373:
374: $rows = $q
375: ->exec()
376: ->fetchAll();
377:
378: // Create the output array
379: $out = array();
380:
381: for ( $i=0, $ien=count($rows) ; $i<$ien ; $i++ ) {
382: $set = false;
383: for( $j=0 ; $j<count($res) ; $j ++) {
384: if($res[$j]['value'] == $rows[$i]['value']){
385: $out[] = array(
386: "label" => $formatter($rows[$i]['label']),
387: "total" => $rows[$i]['total'],
388: "value" => $rows[$i]['value'],
389: "count" => $res[$j]['count']
390: );
391: $set = true;
392: }
393: }
394: if(!$set) {
395: $out[] = array(
396: "label" => $formatter($rows[$i]['label']),
397: "total" => $rows[$i]['total'],
398: "value" => $rows[$i]['value'],
399: "count" => 0
400: );
401: }
402:
403: }
404:
405: // Only sort if there was no SQL order field
406: if ( ! $this->_order ) {
407: usort( $out, function ( $a, $b ) {
408: return is_numeric($a['label']) && is_numeric($b['label']) ?
409: ($a['label']*1) - ($b['label']*1) :
410: strcmp( $a['label'], $b['label'] );
411: } );
412: }
413:
414: return $out;
415: }
416: }
417: