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 2012 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;
14: if (!defined('DATATABLES')) exit();
15:
16: use
17: DataTables,
18: DataTables\Editor,
19: DataTables\Editor\Join,
20: DataTables\Editor\Field;
21:
22:
23: /**
24: * DataTables Editor base class for creating editable tables.
25: *
26: * Editor class instances are capable of servicing all of the requests that
27: * DataTables and Editor will make from the client-side - specifically:
28: * - Get data
29: * - Create new record
30: * - Edit existing record
31: * - Delete existing records
32: *
33: * The Editor instance is configured with information regarding the
34: * database table fields that you which to make editable, and other information
35: * needed to read and write to the database (table name for example!).
36: *
37: * This documentation is very much focused on describing the API presented
38: * by these DataTables Editor classes. For a more general overview of how
39: * the Editor class is used, and how to install Editor on your server, please
40: * refer to the {@link http://editor.datatables.net/tutorials Editor tutorials}.
41: *
42: * @example
43: * A very basic example of using Editor to create a table with four fields.
44: * This is all that is needed on the server-side to create a editable
45: * table - the {@link process} method determines what action DataTables /
46: * Editor is requesting from the server-side and will correctly action it.
47: * <code>
48: * Editor::inst( $db, 'browsers' )
49: * ->fields(
50: * Field::inst( 'first_name' )->validator( 'Validate::required' ),
51: * Field::inst( 'last_name' )->validator( 'Validate::required' ),
52: * Field::inst( 'country' ),
53: * Field::inst( 'details' )
54: * )
55: * ->process( $_POST )
56: * ->json();
57: * </code>
58: */
59: class Editor extends Ext {
60: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
61: * Constructor
62: */
63:
64: /**
65: * Constructor.
66: * @param Database $db An instance of the DataTables Database class that we can
67: * use for the DB connection. Can be given here or with the 'db' method.
68: * <code>
69: * 456
70: * </code>
71: * @param string|array $table The table name in the database to read and write
72: * information from and to. Can be given here or with the 'table' method.
73: * @param string $pkey Primary key column name in the table given in the $table
74: * parameter. Can be given here or with the 'pkey' method.
75: */
76: function __construct( $db=null, $table=null, $pkey=null )
77: {
78: // Set constructor parameters using the API - note that the get/set will
79: // ignore null values if they are used (i.e. not passed in)
80: $this->db( $db );
81: $this->table( $table );
82: $this->pkey( $pkey );
83: }
84:
85:
86:
87: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
88: * Private properties
89: */
90:
91: /** @var DataTables\Database */
92: private $_db = null;
93:
94: /** @var DataTables\Editor\Field[] */
95: private $_fields = array();
96:
97: /** @var array */
98: private $_formData;
99:
100: /** @var string */
101: private $_idPrefix = 'row_';
102:
103: /** @var DataTables\Editor\Join[] */
104: private $_join = array();
105:
106: /** @var string */
107: private $_pkey = 'id';
108:
109: /** @var string[] */
110: private $_table = array();
111:
112: /** @var array */
113: private $_where = array();
114:
115: /** @var boolean */
116: private $_whereSet = false;
117:
118: /** @var array */
119: private $_out = array(
120: "id" => -1,
121: "fieldErrors" => array(),
122: "sError" => "",
123: "aaData" => array()
124: );
125:
126:
127:
128: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
129: * Public methods
130: */
131:
132: /**
133: * Get the data constructed in this instance.
134: *
135: * This will get the PHP array of data that has been constructed for the
136: * command that has been processed by this instance. Therefore only useful after
137: * process has been called.
138: * @return array Processed data array.
139: */
140: public function data ()
141: {
142: return $this->_out;
143: }
144:
145:
146: /**
147: * Get / set the DB connection instance
148: * @param Database $_ DataTable's Database class instance to use for database
149: * connectivity. If not given, then used as a getter.
150: * @return Database|self The Database connection instance if no parameter
151: * is given, or self if used as a setter.
152: */
153: public function db ( $_=null )
154: {
155: return $this->_getSet( $this->_db, $_ );
156: }
157:
158:
159: /**
160: * Get / set field instances.
161: *
162: * The list of fields designates which columns in the table that Editor will work
163: * with (both get and set).
164: * @param Field $_... Instances of the {@link Field} class, given as a single
165: * instance of {@link Field}, an array of {@link Field} instances, or multiple
166: * {@link Field} instance parameters for the function.
167: * @return Field[]|self Array of fields, or self if used as a setter.
168: * @see {@link Field} for field documentation.
169: */
170: public function field ( $_=null )
171: {
172: if ( $_ !== null && !is_array($_) ) {
173: $_ = func_get_args();
174: }
175: return $this->_getSet( $this->_fields, $_, true );
176: }
177:
178:
179: /**
180: * Get / set field instances.
181: *
182: * An alias of {@link field}, for convenience.
183: * @param Field $_... Instances of the {@link Field} class, given as a single
184: * instance of {@link Field}, an array of {@link Field} instances, or multiple
185: * {@link Field} instance parameters for the function.
186: * @return Field[]|self Array of fields, or self if used as a setter.
187: * @see {@link Field} for field documentation.
188: */
189: public function fields ( $_=null )
190: {
191: if ( $_ !== null && !is_array($_) ) {
192: $_ = func_get_args();
193: }
194: return $this->_getSet( $this->_fields, $_, true );
195: }
196:
197:
198: /**
199: * Get / set the DOM prefix.
200: *
201: * Typically primary keys are numeric and this is not a valid ID value in an
202: * HTML document - is also increases the likelihood of an ID clash if multiple
203: * tables are used on a single page. As such, a prefix is assigned to the
204: * primary key value for each row, and this is used as the DOM ID, so Editor
205: * can track individual rows.
206: * @param string $_ Primary key's name. If not given, then used as a getter.
207: * @return string|self Primary key value if no parameter is given, or
208: * self if used as a setter.
209: */
210: public function idPrefix ( $_=null )
211: {
212: return $this->_getSet( $this->_idPrefix, $_ );
213: }
214:
215:
216: /**
217: * Get / set join instances.
218: *
219: * The list of Join instances that Editor will join the parent table to (i.e. the one
220: * that the {@link table} and {@link fields} methods refer to in this class instance).
221: * @param Join $_,... Instances of the {@link Join} class, given as a single instance
222: * of {@link Join}, an array of {@link Join} instances, or multiple {@link Join}
223: * instance parameters for the function.
224: * @return Join[]|self Array of joins, or self if used as a setter.
225: * @see {@link Join} for joining documentation.
226: */
227: public function join ( $_=null )
228: {
229: if ( $_ !== null && !is_array($_) ) {
230: $_ = func_get_args();
231: }
232: return $this->_getSet( $this->_join, $_, true );
233: }
234:
235:
236: /**
237: * Get the JSON for the data constructed in this instance.
238: *
239: * Basically the same as the {@link data} method, but in this case we echo, or
240: * return the JSON string of the data.
241: * @param boolean $print Echo the JSON string out (true, default) or return it
242: * (false).
243: * @return string|self self if printing the JSON, or JSON representation of
244: * the processed data if false is given as the first parameter.
245: */
246: public function json ( $print=true )
247: {
248: if ( $print ) {
249: echo json_encode( $this->_out );
250: return $this;
251: }
252: return json_encode( $this->_out );
253: }
254:
255:
256: /**
257: * Get / set the table name.
258: *
259: * The table name designated which DB table Editor will use as its data source for
260: * working with the database. Multiple names can be given in an array, which can be
261: * useful if applying a WHERE condition over multiple tables. Note that this is not
262: * required for working with the the 'join' method, which supplies its own table
263: * names.
264: * @param string|array $_,... Table names given as a single string, an array of
265: * strings or multiple string parameters for the function.
266: * @return string[]|self Array of tables names, or self if used as a setter.
267: */
268: public function table ( $_=null )
269: {
270: if ( $_ !== null && !is_array($_) ) {
271: $_ = func_get_args();
272: }
273: return $this->_getSet( $this->_table, $_, true );
274: }
275:
276:
277: /**
278: * Get / set the primary key.
279: *
280: * The primary key must be known to Editor so it will know which rows are being
281: * edited / deleted upon those actions. The default value is 'id'.
282: * @param string $_ Primary key's name. If not given, then used as a getter.
283: * @return string|self Primary key value if no parameter is given, or
284: * self if used as a setter.
285: */
286: public function pkey ( $_=null )
287: {
288: return $this->_getSet( $this->_pkey, $_ );
289: }
290:
291:
292: /**
293: * Process a request from the Editor client-side to get / set data.
294: * @param array $data Typically $_POST or $_GET as required by what is sent by Editor
295: * @return self
296: */
297: public function process ( $data )
298: {
299: $this->_formData = isset($data['data']) ? $data['data'] : null;
300:
301: $this->_db->transaction();
302:
303: try {
304: if ( !isset($data['action']) ) {
305: /* Get data */
306: $this->_out = array_merge( $this->_out, $this->_get( null, $data ) );
307: }
308: else if ( $data['action'] == "remove" ) {
309: /* Remove rows */
310: $this->_remove( $data );
311: }
312: else {
313: /* Create or edit row */
314:
315: // Individual field validation
316: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
317: $field = $this->_fields[$i];
318: $validation = $field->validate( $this->_formData );
319:
320: if ( $validation !== true ) {
321: $this->_out['fieldErrors'][] = array(
322: "name" => $field->name(),
323: "status" => $validation
324: );
325: }
326: }
327:
328: // Global validation
329: $this->_out['sError'] = "";
330:
331: // Global validation - if you want global validation - do it here
332: $this->_out['sError'] = "";
333:
334: if ( count($this->_out['fieldErrors']) === 0 ) {
335: if ( $data['action'] == "create" ) {
336: $this->_out['row'] = $this->_insert();
337: }
338: else {
339: $this->_out['row'] = $this->_update( $data['id'] );
340: }
341: }
342: }
343:
344: $this->_db->commit();
345: }
346: catch (\Exception $e) {
347: // Error feedback
348: $this->_out['sError'] = $e->getMessage();
349: $this->_db->rollback();
350: }
351:
352: return $this;
353: }
354:
355:
356: /**
357: * Where condition to add to the query used to get data from the database.
358: *
359: * Can be used in two different ways, as where( field, value ) or as an array of
360: * conditions to use: where( array('fieldName', ...), array('value', ...) );
361: *
362: * Please be very careful when using this method! If an edit made by a user using
363: * Editor removes the row from the where condition, the result is undefined (since
364: * Editor expects the row to still be available, but the condition removes it from
365: * the result set).
366: * @param string|string[] $key Single field name, or an array of field names.
367: * @param string|string[] $value Single field value, or an array of values.
368: * @param string $op Condition operator: <, >, = etc
369: * @return string[]|self Where condition array, or self if used as a setter.
370: */
371: public function where ( $key=null, $value=null, $op='=' )
372: {
373: if ( $key === null ) {
374: return $this->_where;
375: }
376:
377: $this->_where[] = array(
378: "key" => $key,
379: "value" => $value,
380: "op" => $op
381: );
382:
383: return $this;
384: }
385:
386:
387: /**
388: * Get / set if the WHERE conditions should be included in the create and
389: * edit actions.
390: *
391: * This means that the fields which have been used as part of the 'get'
392: * WHERE condition (using the `where()` method) will be set as the values
393: * given. *Note* The value given for the where operation is what will be
394: * set in the database, regardless of the condition operator given (the
395: * optional third parameter for the `where()` method).
396: *
397: * This is default false (i.e. they are not included).
398: *
399: * @param boolean $_ Include (`true`), or not (`false`)
400: * @return boolean Current value
401: */
402: public function whereSet ( $_=null )
403: {
404: return $this->_getSet( $this->_whereSet, $_ );
405: }
406:
407:
408:
409: /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
410: * Private methods
411: */
412:
413: /**
414: * Get an array of objects from the database to be given to DataTables as a
415: * result of an sAjaxSource request, such that DataTables can display the information
416: * from the DB in the table.
417: * @param integer $id Primary key value to get an individual row (after create or
418: * update operations). Gets the full set if not given.
419: * @param array $http HTTP parameters from GET or POST request (so we can service
420: * server-side processing requests from DataTables).
421: * @private
422: */
423: private function _get( $id=null, $http=null )
424: {
425: $ssp = array();
426: $isSSP = isset( $http['sEcho'] );
427:
428: $query = $this->_db
429: ->query('select')
430: ->table( $this->_table )
431: ->get( $this->_pkey )
432: ->get( $this->_fields('get') );
433:
434: $this->_get_where( $query );
435:
436: // Server-side processing specific handling
437: if ( $isSSP ) {
438: // Add the server-side processing conditions
439: $this->_ssp_limit( $query, $http );
440: $this->_ssp_sort( $query, $http );
441: $this->_ssp_filter( $query, $http );
442:
443: // Get the number of rows in the result set
444: $ssp_set_count = $this->_db
445: ->query('select')
446: ->table( $this->_table )
447: ->get( 'COUNT('.$this->_pkey.') as cnt' );
448: $this->_get_where( $ssp_set_count );
449: $this->_ssp_filter( $ssp_set_count, $http );
450: $ssp_set_count = $ssp_set_count->exec()->fetch();
451:
452: // Get the number of rows in the full set
453: $ssp_full_count = $this->_db
454: ->query('select')
455: ->table( $this->_table )
456: ->get( 'COUNT('.$this->_pkey.') as cnt' );
457: $this->_get_where( $ssp_full_count );
458: $ssp_full_count = $ssp_full_count->exec()->fetch();
459:
460: $ssp = array(
461: "sEcho" => intval( $http['sEcho'] ),
462: "iTotalRecords" => $ssp_full_count['cnt'],
463: "iTotalDisplayRecords" => $ssp_set_count['cnt']
464: );
465: }
466:
467: if ( $id !== null ) {
468: $query->where( $this->_pkey, $id );
469: }
470:
471: $res = $query->exec();
472: if ( ! $res ) {
473: throw new \Exception('Error executing SQL for data get');
474: }
475:
476: $out = array();
477: while ( $row=$res->fetch() ) {
478: $inner = array();
479: $inner['DT_RowId'] = $this->_idPrefix . $row[ $this->_pkey ];
480:
481: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
482: $field = $this->_fields[$i];
483: if ( $field->apply('get') ) {
484: $inner[ $field->name() ] = $field->val('get', $row);
485: }
486: }
487:
488: $out[] = $inner;
489: }
490:
491: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
492: $this->_join[$i]->data( $this->_db, $this->table(), $this->pkey(),
493: $this->idPrefix(), $out
494: );
495: }
496:
497: return array_merge( array('aaData'=>$out), $ssp );
498: }
499:
500:
501: /**
502: * Insert a new row in the database
503: * @private
504: */
505: private function _insert( )
506: {
507: $set = array();
508:
509: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
510: $field = $this->_fields[$i];
511:
512: if ( $field->apply( 'set', $this->_formData ) ) {
513: $set[ $field->dbField() ] = $field->val('set', $this->_formData);
514: }
515: }
516:
517: // Add WHERE conditions to the database if needed
518: if ( $this->_whereSet ) {
519: for ( $i=0, $ien=count($this->_where) ; $i<$ien ; $i++ ) {
520: $set[ $this->_where[$i]['key'] ] = $this->_where[$i]['value'];
521: }
522: }
523:
524: $res = $this->_db->insert( $this->_table, $set );
525: if ( ! $res ) {
526: return;
527: }
528:
529: $id = $res->insertId();
530:
531: // Join tables
532: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
533: $this->_join[$i]->create( $this->_db, $id, $this->_formData );
534: }
535:
536: $this->_out['id'] = $this->_idPrefix . $id;
537:
538: // Get the data for the row so we can feed it back to the client and redraw
539: // the whole row with the full data set from the server.
540: $row = $this->_get( $id );
541: return $row['aaData'][0];
542: }
543:
544:
545: /**
546: * Update a row in the database
547: * @param string $id The DOM ID for the row that is being edited.
548: * @private
549: */
550: private function _update( $id )
551: {
552: $set = array();
553: $id = str_replace( $this->_idPrefix, "", $id );
554:
555: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
556: $field = $this->_fields[$i];
557:
558: if ( $field->apply( 'set', $this->_formData ) ) {
559: $set[ $field->dbField() ] = $field->val('set', $this->_formData);
560: }
561: }
562:
563: // Add WHERE conditions to the database if needed
564: if ( $this->_whereSet ) {
565: for ( $i=0, $ien=count($this->_where) ; $i<$ien ; $i++ ) {
566: $set[ $this->_where[$i]['key'] ] = $this->_where[$i]['value'];
567: }
568: }
569:
570: $res = $this->_db->update( $this->_table, $set, array($this->_pkey => $id) );
571: if ( ! $res ) {
572: return;
573: }
574:
575: // And the join tables
576: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
577: $this->_join[$i]->update( $this->_db, $id, $this->_formData );
578: }
579:
580: $this->_out['id'] = $this->_idPrefix . $id;
581:
582: // Get the data for the row so we can feed it back to the client and redraw
583: // the whole row with the full data set from the server.
584: $row = $this->_get( $id );
585: return $row['aaData'][0];
586: }
587:
588:
589: /**
590: * Delete one or more rows from the database
591: * @private
592: */
593: private function _remove( )
594: {
595: $ids = array();
596:
597: // Strip the ID prefix that the client-side sends back
598: for ( $i=0 ; $i<count($this->_formData) ; $i++ ) {
599: $ids[] = str_replace( $this->_idPrefix, "", $this->_formData[$i] );
600: }
601:
602: // Remove rows from the master table
603: $stmt = $this->_db
604: ->query( 'delete' )
605: ->table( $this->_table )
606: ->or_where( $this->_pkey, $ids )
607: ->exec();
608:
609: // And from the join tables
610: for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
611: $this->_join[$i]->remove( $this->_db, $ids );
612: }
613: }
614:
615:
616: /**
617: * Create an array of the DB fields to use for a get / set operation.
618: * @param string $direction Direction: 'get' or 'set'.
619: * @return array List of fields
620: * @private
621: */
622: private function _fields ( $direction )
623: {
624: $fields = array();
625:
626: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
627: $field = $this->_fields[$i];
628:
629: if ( $field->apply( $direction, $this->_formData ) ) {
630: $fields[] = $field->dbField();
631: }
632: }
633:
634: return $fields;
635: }
636:
637:
638: /* * * * * * * * * * * * * * * * * * * * * * * * *
639: * Server-side processing methods
640: */
641:
642: /**
643: * Convert a column index to a database field name - used for server-side
644: * processing requests.
645: * @param array $http HTTP variables (i.e. GET or POST)
646: * @param int $index Index in the DataTables' submitted data
647: * @returns string DB field name
648: * @private
649: */
650: private function _ssp_field( $http, $index )
651: {
652: $dataProp = $http['mDataProp_'.$index];
653:
654: // For proper security spin through the fields, to make sure this is a known
655: // field (i.e. someone hasn't just changed the mDataProp_{} value on the
656: // submit)
657: for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
658: $field = $this->_fields[$i];
659:
660: if ( $field->name() == $dataProp ) {
661: return $field->dbField();
662: }
663: }
664:
665: throw new \Exception('Unknown field: '.$dataProp .' (index '.$index.')');
666: }
667:
668:
669: /**
670: * Sorting requirements to a server-side processing query.
671: * @param Query $query Query instance to apply sorting to
672: * @param array $http HTTP variables (i.e. GET or POST)
673: * @private
674: */
675: private function _ssp_sort ( $query, $http )
676: {
677: for ( $i=0 ; $i<$http['iSortingCols'] ; $i++ ) {
678: if ( $http['bSortable_'.$i] == 'true' ) {
679: $query->order(
680: $this->_ssp_field( $http, $http['iSortCol_'.$i] ) .' '.
681: ($http['sSortDir_'.$i]==='asc' ? 'asc' : 'desc')
682: );
683: }
684: }
685: }
686:
687:
688: /**
689: * Add DataTables' 'where' condition to a server-side processing query. This
690: * works for both global and individual column filtering.
691: * @param Query $query Query instance to apply the WHERE conditions to
692: * @param array $http HTTP variables (i.e. GET or POST)
693: * @private
694: */
695: private function _ssp_filter ( $query, $http )
696: {
697: // Global filter
698: $fields = $this->_fields;
699:
700: if ( $http['sSearch'] != "" ) {
701: $query->where( function ($q) use (&$fields, $http) {
702: for ( $i=0 ; $i<count($fields) ; $i++ ) {
703: $field = $fields[$i];
704:
705: $q->or_where( $field->dbField(), '%'.$http['sSearch'].'%', 'like' );
706: }
707: } );
708: }
709:
710: // Column filters
711: $i = 0;
712: while ( isset( $http['sSearch_'.$i] ) ) {
713: $search = $http['sSearch_'.$i];
714:
715: if( $search != "" && $http['bSearchable_'.$i] == 'true' ) {
716: $query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );
717: }
718:
719: $i++;
720: }
721: }
722:
723:
724: /**
725: * Add a limit / offset to a server-side processing query
726: * @param Query $query Query instance to apply the offset / limit to
727: * @param array $http HTTP variables (i.e. GET or POST)
728: * @private
729: */
730: private function _ssp_limit ( $query, $http )
731: {
732: if ( $http['iDisplayLength'] != -1 ) { // -1 is 'show all' in DataTables
733: $query
734: ->offset( $http['iDisplayStart'] )
735: ->limit( $http['iDisplayLength'] );
736: }
737: }
738:
739:
740: /**
741: * Add local WHERE condition to query
742: * @param Query $query Query instance to apply the WHERE conditions ti
743: * @private
744: */
745: private function _get_where ( $query )
746: {
747: for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
748: $query->where(
749: $this->_where[$i]['key'],
750: $this->_where[$i]['value'],
751: $this->_where[$i]['op']
752: );
753: }
754: }
755: }
756:
757:
758: