      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: