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 Options 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 integer Row limit */
79: private $_limit = null;
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: private $_manualAdd = array();
91:
92:
93: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
94: * Public methods
95: */
96:
97: /**
98: * Add extra options to the list, in addition to any obtained from the database
99: *
100: * @param string $label The label to use for the option
101: * @param string|null $value Value for the option. If not given, the label will be used
102: * @return Options Self for chaining
103: */
104: public function add ( $label, $value=null )
105: {
106: if ( $value === null ) {
107: $value = $label;
108: }
109:
110: $this->_manualAdd[] = array(
111: 'label' => $label,
112: 'value' => $value
113: );
114:
115: return $this;
116: }
117:
118: /**
119: * Get / set the column(s) to use as the label value of the options
120: *
121: * @param null|string|string[] $_ null to get the current value, string or
122: * array to get.
123: * @return Options|string[] Self if setting for chaining, array of values if
124: * getting.
125: */
126: public function label ( $_=null )
127: {
128: if ( $_ === null ) {
129: return $this;
130: }
131: else if ( is_string($_) ) {
132: $this->_label = array( $_ );
133: }
134: else {
135: $this->_label = $_;
136: }
137:
138: return $this;
139: }
140:
141: /**
142: * Get / set the LIMIT clause to limit the number of records returned.
143: *
144: * @param null|number $_ Number of rows to limit the result to
145: * @return Options|string[] Self if setting for chaining, limit if getting.
146: */
147: public function limit ( $_=null )
148: {
149: return $this->_getSet( $this->_limit, $_ );
150: }
151:
152: /**
153: * Get / set the ORDER BY clause to use in the SQL. If this option is not
154: * provided the ordering will be based on the rendered output, either
155: * numerically or alphabetically based on the data returned by the renderer.
156: *
157: * @param null|string $_ String to set, null to get current value
158: * @return Options|string Self if setting for chaining, string if getting.
159: */
160: public function order ( $_=null )
161: {
162: return $this->_getSet( $this->_order, $_ );
163: }
164:
165: /**
166: * Get / set the label renderer. The renderer can be used to combine
167: * multiple database columns into a single string that is shown as the label
168: * to the end user in the list of options.
169: *
170: * @param null|callable $_ Function to set, null to get current value
171: * @return Options|callable Self if setting for chaining, callable if
172: * getting.
173: */
174: public function render ( $_=null )
175: {
176: return $this->_getSet( $this->_renderer, $_ );
177: }
178:
179: /**
180: * Get / set the database table from which to gather the options for the
181: * list.
182: *
183: * @param null|string $_ String to set, null to get current value
184: * @return Options|string Self if setting for chaining, string if getting.
185: */
186: public function table ( $_=null )
187: {
188: return $this->_getSet( $this->_table, $_ );
189: }
190:
191: /**
192: * Get / set the column name to use for the value in the options list. This
193: * would normally be the primary key for the table.
194: *
195: * @param null|string $_ String to set, null to get current value
196: * @return Options|string Self if setting for chaining, string if getting.
197: */
198: public function value ( $_=null )
199: {
200: return $this->_getSet( $this->_value, $_ );
201: }
202:
203: /**
204: * Get / set the method to use for a WHERE condition if it is to be
205: * applied to the query to get the options.
206: *
207: * @param null|callable $_ Function to set, null to get current value
208: * @return Options|callable Self if setting for chaining, callable if
209: * getting.
210: */
211: public function where ( $_=null )
212: {
213: return $this->_getSet( $this->_where, $_ );
214: }
215:
216:
217:
218: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
219: * Internal methods
220: */
221:
222: /**
223: * Execute the options (i.e. get them)
224: *
225: * @param Database $db Database connection
226: * @return array List of options
227: * @internal
228: */
229: public function exec ( $db )
230: {
231: $label = $this->_label;
232: $value = $this->_value;
233: $formatter = $this->_renderer;
234:
235: // Create a list of the fields that we need to get from the db
236: $fields = array();
237: $fields[] = $value;
238: $fields = array_merge( $fields, $label );
239:
240: // We need a default formatter if one isn't provided
241: if ( ! $formatter ) {
242: $formatter = function ( $row ) use ( $label ) {
243: $a = array();
244:
245: for ( $i=0, $ien=count($label) ; $i<$ien ; $i++ ) {
246: $a[] = $row[ $label[$i] ];
247: }
248:
249: return implode(' ', $a);
250: };
251: }
252:
253: // Get the data
254: $q = $db
255: ->query('select')
256: ->table( $this->_table )
257: ->distinct( true )
258: ->get( $fields )
259: ->where( $this->_where );
260:
261: if ( $this->_order ) {
262: // For cases where we are ordering by a field which isn't included in the list
263: // of fields to display, we need to add the ordering field, due to the
264: // select distinct.
265: $orderFields = explode( ',', $this->_order );
266:
267: for ( $i=0, $ien=count($orderFields) ; $i<$ien ; $i++ ) {
268: $field = strtolower( $orderFields[$i] );
269: $field = str_replace( ' asc', '', $field );
270: $field = str_replace( ' desc', '', $field );
271: $field = trim( $field );
272:
273: if ( ! in_array( $field, $fields ) ) {
274: $q->get( $field );
275: }
276: }
277:
278: $q->order( $this->_order );
279: }
280:
281: if ( $this->_limit !== null ) {
282: $q->limit( $this->_limit );
283: }
284:
285: $rows = $q
286: ->exec()
287: ->fetchAll();
288:
289: // Create the output array
290: $out = array();
291:
292: for ( $i=0, $ien=count($rows) ; $i<$ien ; $i++ ) {
293: $out[] = array(
294: "label" => $formatter( $rows[$i] ),
295: "value" => $rows[$i][$value]
296: );
297: }
298:
299: // Stick on any extra manually added options
300: if ( count( $this->_manualAdd ) ) {
301: $out = array_merge( $out, $this->_manualAdd );
302: }
303:
304: // Only sort if there was no SQL order field
305: if ( ! $this->_order ) {
306: usort( $out, function ( $a, $b ) {
307: return is_numeric($a['label']) && is_numeric($b['label']) ?
308: ($a['label']*1) - ($b['label']*1) :
309: strcmp( $a['label'], $b['label'] );
310: } );
311: }
312:
313: return $out;
314: }
315: }
316: