jjimenez/safekat
1
0
Fork 0
mirror of https://git.imnavajas.es/jjimenez/safekat.git synced 2025-07-25 22:52:08 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
safekat/ci4/app/ThirdParty/DatatablesEditor/Editor.php

2601 lines
72 KiB
PHP
Executable File
Raw Permalink Blame History

<?php
/**
* DataTables PHP libraries.
*
* PHP libraries for DataTables and DataTables Editor, utilising PHP 5.3+.
*
* @author SpryMedia
* @version __VERSION__
* @copyright 2012 SpryMedia ( http://sprymedia.co.uk )
* @license http://editor.datatables.net/license DataTables Editor
* @link http://editor.datatables.net
*/
namespace DataTables;
if (!defined('DATATABLES')) exit();
use
DataTables,
DataTables\Editor\Join,
DataTables\Editor\Field;
/**
* DataTables Editor base class for creating editable tables.
*
* Editor class instances are capable of servicing all of the requests that
* DataTables and Editor will make from the client-side - specifically:
*
* * Get data
* * Create new record
* * Edit existing record
* * Delete existing records
*
* The Editor instance is configured with information regarding the
* database table fields that you wish to make editable, and other information
* needed to read and write to the database (table name for example!).
*
* This documentation is very much focused on describing the API presented
* by these DataTables Editor classes. For a more general overview of how
* the Editor class is used, and how to install Editor on your server, please
* refer to the {@link https://editor.datatables.net/manual Editor manual}.
*
* @example
* A very basic example of using Editor to create a table with four fields.
* This is all that is needed on the server-side to create a editable
* table - the {@see Editor->process()} method determines what action DataTables /
* Editor is requesting from the server-side and will correctly action it.
*
* ```php
* Editor::inst( $db, 'browsers' )
* ->fields(
* Field::inst( 'first_name' )->validator( Validate::required() ),
* Field::inst( 'last_name' )->validator( Validate::required() ),
* Field::inst( 'country' ),
* Field::inst( 'details' )
* )
* ->process( $_POST )
* ->json();
* ```
*/
class Editor extends Ext {
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Statics
*/
/** Request type - read */
const ACTION_READ = 'read';
/** Request type - create */
const ACTION_CREATE = 'create';
/** Request type - edit */
const ACTION_EDIT = 'edit';
/** Request type - delete */
const ACTION_DELETE = 'remove';
/** Request type - upload */
const ACTION_UPLOAD = 'upload';
/**
* Determine the request type from an HTTP request.
*
* @param array $http Typically $_POST, but can be any array used to carry
* an Editor payload
* @param string $name The parameter name that the action should be read from.
* @return string `Editor::ACTION_READ`, `Editor::ACTION_CREATE`,
* `Editor::ACTION_EDIT` or `Editor::ACTION_DELETE` indicating the request
* type.
*/
static public function action ( $http, $name='action' )
{
if ( ! isset( $http[$name] ) ) {
return self::ACTION_READ;
}
switch ( $http[$name] ) {
case 'create':
return self::ACTION_CREATE;
case 'edit':
return self::ACTION_EDIT;
case 'remove':
return self::ACTION_DELETE;
case 'upload':
return self::ACTION_UPLOAD;
default:
throw new \Exception("Unknown Editor action: ".$http['action']);
}
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Constructor
*/
/**
* Constructor.
* @param Database $db An instance of the DataTables Database class that we can
* use for the DB connection. Can be given here or with the 'db' method.
* @param string|array $table The table name in the database to read and write
* information from and to. Can be given here or with the 'table' method.
* @param string|array $pkey Primary key column name in the table given in
* the $table parameter. Can be given here or with the 'pkey' method.
*/
function __construct( $db=null, $table=null, $pkey=null )
{
// Set constructor parameters using the API - note that the get/set will
// ignore null values if they are used (i.e. not passed in)
$this->db( $db );
$this->table( $table );
$this->pkey( $pkey );
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Public properties
*/
/** @var string */
public $version = '2.1.3';
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Private properties
*/
/** @var DataTables\Database */
private $_db = null;
/** @var DataTables\Editor\Field[] */
private $_fields = array();
/** @var array */
private $_formData;
/** @var array */
private $_processData;
/** @var string */
private $_idPrefix = 'row_';
/** @var DataTables\Editor\Join[] */
private $_join = array();
/** @var array */
private $_pkey = array('id');
/** @var string[] */
private $_table = array();
/** @var string[] */
private $_readTableNames = array();
/** @var boolean */
private $_transaction = true;
/** @var array */
private $_where = array();
/** @var boolean */
private $_write = true;
/** @var array */
private $_leftJoin = array();
/** @var boolean - deprecated */
private $_whereSet = false;
/** @var array */
private $_out = array();
/** @var array */
private $_events = array();
/** @var boolean */
private $_debug = false;
/** @var array */
private $_debugInfo = array();
/** @var string Log output path */
private $_debugLog = '';
/** @var callback */
private $_validator = array();
/** @var boolean Enable true / catch when processing */
private $_tryCatch = true;
/** @var boolean Enable / disable delete on left joined tables */
private $_leftJoinRemove = false;
/** @var string Action name allowing for configuration */
private $_actionName = 'action';
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Public methods
*/
/**
* Get / set the action name to read in HTTP parameters. This can be useful
* to set if you are using a framework that uses the default name of `action`
* for something else (e.g. WordPress).
* @param string Value to set. If not given, then used as a getter.
* @return string|self Value, or self if used as a setter.
*/
public function actionName ( $_=null )
{
return $this->_getSet( $this->_actionName, $_ );
}
/**
* Get the data constructed in this instance.
*
* This will get the PHP array of data that has been constructed for the
* command that has been processed by this instance. Therefore only useful after
* process has been called.
* @return array Processed data array.
*/
public function data ()
{
return $this->_out;
}
/**
* Get / set the DB connection instance
* @param Database $_ DataTable's Database class instance to use for database
* connectivity. If not given, then used as a getter.
* @return Database|self The Database connection instance if no parameter
* is given, or self if used as a setter.
*/
public function db ( $_=null )
{
return $this->_getSet( $this->_db, $_ );
}
/**
* Get / set debug mode and set a debug message.
*
* It can be useful to see the SQL statements that Editor is using. This
* method enables that ability. Information about the queries used is
* automatically added to the output data array / JSON under the property
* name `debugSql`.
*
* This method can also be called with a string parameter, which will be
* added to the debug information sent back to the client-side. This can
* be useful when debugging event listeners, etc.
*
* @param boolean|mixed $_ Debug mode state. If not given, then used as a
* getter. If given as anything other than a boolean, it will be added
* to the debug information sent back to the client.
* @param string [$path=null] Set an output path to log debug information
* @return boolean|self Debug mode state if no parameter is given, or
* self if used as a setter or when adding a debug message.
*/
public function debug ( $_=null, $path=null )
{
if ( ! is_bool( $_ ) ) {
$this->_debugInfo[] = $_;
return $this;
}
if ( $path ) {
$this->_debugLog = $path;
}
return $this->_getSet( $this->_debug, $_ );
}
/**
* Get / set field instance.
*
* The list of fields designates which columns in the table that Editor will work
* with (both get and set).
* @param Field|string $_... This parameter effects the return value of the
* function:
*
* * `null` - Get an array of all fields assigned to the instance
* * `string` - Get a specific field instance whose 'name' matches the
* field passed in
* * {@see Field} - Add a field to the instance's list of fields. This
* can be as many fields as required (i.e. multiple arguments)
* * `array` - An array of {@see Field} instances to add to the list
* of fields.
* @return Field|Field[]|Editor The selected field, an array of fields, or
* the Editor instance for chaining, depending on the input parameter.
* @throws \Exception Unkown field error
* @see {@see Field} for field documentation.
*/
public function field ( $_=null )
{
$args = func_get_args();
if ( is_string( $_ ) ) {
for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
if ( $this->_fields[$i]->name() === $_ ) {
return $this->_fields[$i];
}
}
throw new \Exception('Unknown field: '.$_);
}
if ( $_ !== null && !is_array($_) ) {
$_ = $args;
}
return $this->_getSet( $this->_fields, $_, true );
}
/**
* Get / set field instances.
*
* An alias of {@see field}, for convenience.
* @param Field $_... Instances of the {@see Field} class, given as a single
* instance of {@see Field}, an array of {@see Field} instances, or multiple
* {@see Field} instance parameters for the function.
* @return Field[]|self Array of fields, or self if used as a setter.
* @see {@see Field} for field documentation.
*/
public function fields ( $_=null )
{
$args = func_get_args();
if ( $_ !== null && !is_array($_) ) {
$_ = $args;
}
return $this->_getSet( $this->_fields, $_, true );
}
/**
* Get / set the DOM prefix.
*
* Typically primary keys are numeric and this is not a valid ID value in an
* HTML document - is also increases the likelihood of an ID clash if multiple
* tables are used on a single page. As such, a prefix is assigned to the
* primary key value for each row, and this is used as the DOM ID, so Editor
* can track individual rows.
* @param string $_ Primary key's name. If not given, then used as a getter.
* @return string|self Primary key value if no parameter is given, or
* self if used as a setter.
*/
public function idPrefix ( $_=null )
{
return $this->_getSet( $this->_idPrefix, $_ );
}
/**
* Get the data that is being processed by the Editor instance. This is only
* useful once the `process()` method has been called, and is available for
* use in validation and formatter methods.
*
* @return array Data given to `process()`.
*/
public function inData ()
{
return $this->_processData;
}
/**
* Get / set join instances. Note that for the majority of use cases you
* will want to use the `leftJoin()` method. It is significantly easier
* to use if you are just doing a simple left join!
*
* The list of Join instances that Editor will join the parent table to
* (i.e. the one that the {@see Editor->table()} and {@see Editor->fields}
* methods refer to in this class instance).
*
* @param Join $_,... Instances of the {@see Join} class, given as a
* single instance of {@see Join}, an array of {@see Join} instances,
* or multiple {@see Join} instance parameters for the function.
* @return Join[]|self Array of joins, or self if used as a setter.
*/
public function join ( $_=null )
{
$args = func_get_args();
if ( $_ !== null && !is_array($_) ) {
$_ = $args;
}
return $this->_getSet( $this->_join, $_, true );
}
/**
* Get the JSON for the data constructed in this instance.
*
* Basically the same as the {@see Editor->data()} method, but in this case we echo, or
* return the JSON string of the data.
* @param boolean $print Echo the JSON string out (true, default) or return it
* (false).
* @param int JSON encode option https://www.php.net/manual/en/json.constants.php
* @return string|self self if printing the JSON, or JSON representation of
* the processed data if false is given as the first parameter.
*/
public function json ( $print=true, $options=0 )
{
if ( $print ) {
$json = json_encode( $this->_out, $options );
if ( $json !== false ) {
echo $json;
}
else {
echo json_encode( array(
"error" => "JSON encoding error: ".json_last_error_msg()
) );
}
return $this;
}
return json_encode( $this->_out );
}
/**
* Echo out JSONP for the data constructed and processed in this instance.
* This is basically the same as {@see Editor->json()} but wraps the return in a
* JSONP callback.
*
* @param string $callback The callback function name to use. If not given
* or `null`, then `$_GET['callback']` is used (the jQuery default).
* @return self Self for chaining.
* @throws \Exception JSONP function name validation
*/
public function jsonp ( $callback=null )
{
if ( ! $callback ) {
$callback = $_GET['callback'];
}
if ( preg_match('/[^a-zA-Z0-9_]/', $callback) ) {
throw new \Exception("Invalid JSONP callback function name");
}
echo $callback.'('.json_encode( $this->_out ).');';
return $this;
}
/**
* Add a left join condition to the Editor instance, allowing it to operate
* over multiple tables. Multiple `leftJoin()` calls can be made for a
* single Editor instance to join multiple tables.
*
* A left join is the most common type of join that is used with Editor
* so this method is provided to make its use very easy to configure. Its
* parameters are basically the same as writing an SQL left join statement,
* but in this case Editor will handle the create, update and remove
* requirements of the join for you:
*
* * Create - On create Editor will insert the data into the primary table
* and then into the joined tables - selecting the required data for each
* table.
* * Edit - On edit Editor will update the main table, and then either
* update the existing rows in the joined table that match the join and
* edit conditions, or insert a new row into the joined table if required.
* * Remove - On delete Editor will remove the main row and then loop over
* each of the joined tables and remove the joined data matching the join
* link from the main table.
*
* Please note that when using join tables, Editor requires that you fully
* qualify each field with the field's table name. SQL can result table
* names for ambiguous field names, but for Editor to provide its full CRUD
* options, the table name must also be given. For example the field
* `first_name` in the table `users` would be given as `users.first_name`.
*
* @param string $table Table name to do a join onto
* @param string $field1 Field from the parent table to use as the join link
* @param string $operator Join condition (`=`, '<`, etc)
* @param string $field2 Field from the child table to use as the join link
* @return self Self for chaining.
*
* @example
* Simple join:
*
* ```php
* ->field(
* Field::inst( 'users.first_name as myField' ),
* Field::inst( 'users.last_name' ),
* Field::inst( 'users.dept_id' ),
* Field::inst( 'dept.name' )
* )
* ->leftJoin( 'dept', 'users.dept_id', '=', 'dept.id' )
* ->process($_POST)
* ->json();
* ```</code>```
*
* This is basically the same as the following SQL statement:
*
* ```sql
* SELECT users.first_name, users.last_name, user.dept_id, dept.name
* FROM users
* LEFT JOIN dept ON users.dept_id = dept.id
* ```
*/
public function leftJoin ( $table, $field1, $operator = null, $field2 = null )
{
$this->_leftJoin[] = array(
"table" => $table,
"field1" => $field1,
"field2" => $field2,
"operator" => $operator
);
return $this;
}
/**
* Indicate if a remove should be performed on left joined tables when deleting
* from the parent row. Note that this is disabled by default and will be
* removed completely in v2. Use `ON DELETE CASCADE` in your database instead.
*
* @deprecated
* @param boolean $_ Value to set. If not given, then used as a getter.
* @return boolean|self Value if no parameter is given, or
* self if used as a setter.
*/
public function leftJoinRemove ( $_=null )
{
return $this->_getSet( $this->_leftJoinRemove, $_ );
}
/**
* Add an event listener. The `Editor` class will trigger an number of
* events that some action can be taken on.
*
* @param string $name Event name
* @param callable $callback Callback function to execute when the event
* occurs
* @return self Self for chaining.
*/
public function on ( $name, $callback )
{
if ( ! isset( $this->_events[ $name ] ) ) {
$this->_events[ $name ] = array();
}
$this->_events[ $name ][] = $callback;
return $this;
}
/**
* Get / set the primary key.
*
* The primary key must be known to Editor so it will know which rows are being
* edited / deleted upon those actions. The default value is ['id'].
*
* @param string|array $_ Primary key's name. If not given, then used as a
* getter. An array of column names can be given to allow composite keys to
* be used.
* @return string|self Primary key value if no parameter is given, or
* self if used as a setter.
*/
public function pkey ( $_=null )
{
if ( is_string( $_ ) ) {
$this->_pkey = array( $_ );
return $this;
}
return $this->_getSet( $this->_pkey, $_ );
}
/**
* Convert a primary key array of field values to a combined value.
*
* @param string $row The row of data that the primary key value should
* be extracted from.
* @param boolean $flat Flag to indicate if the given array is flat
* (useful for `where` conditions) or nested for join tables.
* @return string The created primary key value.
* @throws \Exception If one of the values that the primary key is made up
* of cannot be found in the data set given, an Exception will be thrown.
*/
public function pkeyToValue ( $row, $flat=false )
{
$pkey = $this->_pkey;
$id = array();
for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
$column = $pkey[ $i ];
if ( $flat ) {
if ( isset( $row[ $column ] ) ) {
if ( $row[ $column ] === null ) {
throw new \Exception("Primary key value is null.", 1);
}
$val = $row[ $column ];
}
else {
$val = null;
}
}
else {
$val = $this->_readProp( $column, $row );
}
if ( $val === null ) {
throw new \Exception("Primary key element is not available in data set.", 1);
}
$id[] = $val;
}
return implode( $this->_pkey_separator(), $id );
}
/**
* Convert a primary key combined value to an array of field values.
*
* @param string $value The id that should be split apart
* @param boolean $flat Flag to indicate if the returned array should be
* flat (useful for `where` conditions) or nested for join tables.
* @param string[] $pkey The primary key name - will use the instance value
* if not given
* @return array Array of field values that the id was made up of.
* @throws \Exception If the primary key value does not match the expected
* length based on the primary key configuration, an exception will be
* thrown.
*/
public function pkeyToArray ( $value, $flat=false, $pkey=null )
{
$arr = array();
$value = str_replace( $this->idPrefix(), '', $value );
$idParts = explode( $this->_pkey_separator(), $value );
if ( $pkey === null ) {
$pkey = $this->_pkey;
}
if ( count($pkey) !== count($idParts) ) {
throw new \Exception("Primary key data doesn't match submitted data", 1);
}
for ( $i=0, $ien=count($idParts) ; $i<$ien ; $i++ ) {
if ( $flat ) {
$arr[ $pkey[$i] ] = $idParts[$i];
}
else {
$this->_writeProp( $arr, $pkey[$i], $idParts[$i] );
}
}
return $arr;
}
/**
* Process a request from the Editor client-side to get / set data.
*
* @param array $data Typically $_POST or $_GET as required by what is sent
* by Editor
* @return self
*/
public function process ( $data )
{
if ( $this->_debug ) {
$debugInfo = &$this->_debugInfo;
$debugInfo[] = 'Editor PHP libraries - version '. $this->version;
$debugVal = $this->_db->debug( function ( $mess ) use ( &$debugInfo ) {
$debugInfo[] = $mess;
} );
}
if ( $this->_tryCatch ) {
try {
$this->_process( $data );
}
catch (\Exception $e) {
// Error feedback
$this->_out['error'] = $e->getMessage();
if ( $this->_transaction ) {
$this->_db->rollback();
}
}
}
else {
$this->_process( $data );
}
if ( $this->_debug ) {
$this->_out['debug'] = $this->_debugInfo;
// Save to a log file
if ( $this->_debugLog ) {
file_put_contents( $this->_debugLog, json_encode( $this->_debugInfo )."\n", FILE_APPEND );
}
$this->_db->debug( false );
}
return $this;
}
/**
* The CRUD read table name. If this method is used, Editor will create from the
* table name(s) given rather than those given by `Editor->table()`. This can be
* a useful distinction to allow a read from a VIEW (which could make use of a
* complex SELECT) while writing to a different table.
*
* @param string|array $_,... Read table names given as a single string, an array
* of strings or multiple string parameters for the function.
* @return string[]|self Array of read tables names, or self if used as a setter.
*/
public function readTable ( $_=null )
{
$args = func_get_args();
if ( $_ !== null && !is_array($_) ) {
$_ = $args;
}
return $this->_getSet( $this->_readTableNames, $_, true );
}
/**
* Get / set the table name.
*
* The table name designated which DB table Editor will use as its data
* source for working with the database. Table names can be given with an
* alias, which can be used to simplify larger table names. The field
* names would also need to reflect the alias, just like an SQL query. For
* example: `users as a`.
*
* @param string|array $_,... Table names given as a single string, an array of
* strings or multiple string parameters for the function.
* @return string[]|self Array of tables names, or self if used as a setter.
*/
public function table ( $_=null )
{
$args = func_get_args();
if ( $_ !== null && !is_array($_) ) {
$_ = $args;
}
return $this->_getSet( $this->_table, $_, true );
}
/**
* Get / set transaction support.
*
* When enabled (which it is by default) Editor will use an SQL transaction
* to ensure data integrity while it is performing operations on the table.
* This can be optionally disabled using this method, if required by your
* database configuration.
*
* @param boolean $_ Enable (`true`) or disabled (`false`) transactions.
* If not given, then used as a getter.
* @return boolean|self Transactions enabled flag, or self if used as a
* setter.
*/
public function transaction ( $_=null )
{
return $this->_getSet( $this->_transaction, $_ );
}
/**
* Enable / try catch when `process()` is called. Disabling this can be
* useful for debugging, but is not recommended for production.
*
* @param boolean $_ `true` to enable (default), otherwise false to disable
* @return boolean|Editor Value if used as a getter, otherwise `$this` when
* used as a setter.
*/
public function tryCatch ( $_=null )
{
return $this->_getSet( $this->_tryCatch, $_ );
}
/**
* Perform validation on a data set.
*
* Note that validation is performed on data only when the action is
* `create` or `edit`. Additionally, validation is performed on the _wire
* data_ - i.e. that which is submitted from the client, without formatting.
* Any formatting required by `setFormatter` is performed after the data
* from the client has been validated.
*
* @param array $errors Output array to which field error information will
* be written. Each element in the array represents a field in an error
* condition. These elements are themselves arrays with two properties
* set; `name` and `status`.
* @param array $data The format data to check
* @return boolean `true` if the data is valid, `false` if not.
*/
public function validate ( &$errors, $data )
{
// Validation is only performed on create and edit
if ( $data[$this->_actionName] != "create" && $data[$this->_actionName] != "edit" ) {
return true;
}
foreach( $data['data'] as $id => $values ) {
for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
$field = $this->_fields[$i];
$validation = $field->validate( $values, $this,
str_replace( $this->idPrefix(), '', $id )
);
if ( $validation !== true ) {
$errors[] = array(
"name" => $field->name(),
"status" => $validation
);
}
}
// MJoin validation
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
$this->_join[$i]->validate( $errors, $this, $values, $data[$this->_actionName] );
}
}
return count( $errors ) > 0 ? false : true;
}
/**
* Get / set a global validator that will be triggered for the create, edit
* and remove actions performed from the client-side. Multiple validators
* can be added.
*
* @param callable $_ Function to execute when validating the input data.
* It is passed three parameters: 1. The editor instance, 2. The action
* and 3. The values.
* @return Editor|callback Editor instance if called as a setter, or the
* validator function if not.
*/
public function validator ( $_=null )
{
return $this->_getSet( $this->_validator, $_, true );
}
/**
* Where condition to add to the query used to get data from the database.
*
* Can be used in two different ways:
*
* * Simple case: `where( field, value, operator )`
* * Complex: `where( fn )`
*
* The simple case is fairly self explanatory, a condition is applied to the
* data that looks like `field operator value` (e.g. `name = 'Allan'`). The
* complex case allows full control over the query conditions by providing a
* closure function that has access to the database Query that Editor is
* using, so you can use the `where()`, `or_where()`, `and_where()` and
* `where_group()` methods as you require.
*
* Please be very careful when using this method! If an edit made by a user
* using Editor removes the row from the where condition, the result is
* undefined (since Editor expects the row to still be available, but the
* condition removes it from the result set).
*
* @param string|callable $key Single field name or a closure function
* @param string $value Single field value.
* @param string $op Condition operator: <, >, = etc
* @return string[]|self Where condition array, or self if used as a setter.
*/
public function where ( $key=null, $value=null, $op='=' )
{
if ( $key === null ) {
return $this->_where;
}
if ( is_callable($key) && is_object($key) ) {
$this->_where[] = $key;
}
else {
$this->_where[] = array(
"key" => $key,
"value" => $value,
"op" => $op
);
}
return $this;
}
/**
* Get / set if the WHERE conditions should be included in the create and
* edit actions.
*
* @param boolean $_ Include (`true`), or not (`false`)
* @return boolean Current value
* @deprecated Note that `whereSet` is now deprecated and replaced with the
* ability to set values for columns on create and edit. The C# libraries
* do not support this option at all.
*/
public function whereSet ( $_=null )
{
return $this->_getSet( $this->_whereSet, $_ );
}
public function write ($_writeVal=null){
return $this->_getSet($this->_write, $_writeVal);
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Private methods
*/
/**
* Process a request from the Editor client-side to get / set data.
*
* @param array $data Data to process
* @private
*/
private function _process( $data )
{
$this->_out = array(
"fieldErrors" => array(),
"error" => "",
"data" => array(),
"ipOpts" => array(),
"cancelled" => array()
);
$action = Editor::action($data);
$this->_processData = $data;
$this->_formData = isset($data['data']) ? $data['data'] : null;
$validators = $this->_validator;
// Sanity check that data isn't getting truncated as that can lead to data corruption
if ( $data && count($data, COUNT_RECURSIVE) >= ini_get('max_input_vars') ) {
$this->_out['error'] = 'Too many rows edited at the same time (tech info: max_input_vars exceeded).';
}
if ( ! $this->_out['error'] ) {
if ( $this->_transaction ) {
$this->_db->transaction();
}
$this->_prepJoin();
if ( $validators ) {
for ( $i=0 ; $i<count($validators) ; $i++ ) {
$validator = $validators[$i];
$ret = $validator( $this, $action, $data );
if ( is_string($ret) ) {
$this->_out['error'] = $ret;
break;
}
}
}
}
if ( ! $this->_out['error'] ) {
if ( $action === Editor::ACTION_READ ) {
/* Get data */
$this->_out = array_merge( $this->_out, $this->_get( null, $data ) );
}
else if ( $action === Editor::ACTION_UPLOAD && $this->_write === true ) {
/* File upload */
$this->_upload( $data );
}
else if ( $action === Editor::ACTION_DELETE && $this->_write === true) {
/* Remove rows */
$this->_remove( $data );
$this->_fileClean();
}
else if (($action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) && $this->_write === true ) {
/* Create or edit row */
// Pre events so they can occur before the validation
foreach ($data['data'] as $idSrc => &$values) {
$cancel = null;
if ( $action === Editor::ACTION_CREATE ) {
$cancel = $this->_trigger( 'preCreate', $values );
}
else {
$id = str_replace( $this->_idPrefix, '', $idSrc );
$cancel = $this->_trigger( 'preEdit', $id, $values );
}
// One of the event handlers returned false - don't continue
if ( $cancel === false ) {
// Remove the data from the data set so it won't be processed
unset( $data['data'][$idSrc] );
// Tell the client-side we aren't updating this row
$this->_out['cancelled'][] = $idSrc;
}
}
// Validation
$valid = $this->validate( $this->_out['fieldErrors'], $data );
if ( $valid ) {
foreach ($data['data'] as $id => &$values) {
$d = $action === Editor::ACTION_CREATE ?
$this->_insert( $values ) :
$this->_update( $id, $values );
if ( $d !== null ) {
$this->_out['data'][] = $d;
}
}
$this->_fileClean();
}
}
}
if ( $this->_transaction ) {
$this->_db->commit();
}
// Tidy up the reply
if ( count( $this->_out['fieldErrors'] ) === 0 ) {
unset( $this->_out['fieldErrors'] );
}
if ( $this->_out['error'] === '' ) {
unset( $this->_out['error'] );
}
if ( count( $this->_out['ipOpts'] ) === 0 ) {
unset( $this->_out['ipOpts'] );
}
if ( count( $this->_out['cancelled'] ) === 0 ) {
unset( $this->_out['cancelled'] );
}
}
/**
* Get an array of objects from the database to be given to DataTables as a
* result of an sAjaxSource request, such that DataTables can display the information
* from the DB in the table.
*
* @param integer|string $id Primary key value to get an individual row
* (after create or update operations). Gets the full set if not given.
* If a compound key is being used, this should be the string
* representation of it (i.e. joined together) rather than an array form.
* @param array $http HTTP parameters from GET or POST request (so we can service
* server-side processing requests from DataTables).
* @return array DataTables get information
* @throws \Exception Error on SQL execution
* @private
*/
private function _get( $id=null, $http=null )
{
$cancel = $this->_trigger( 'preGet', $id );
if ( $cancel === false ) {
return array();
}
// print_r($id);
// print_r($http);
$query = $this->_db
->query('select')
->table( $this->_read_table() )
->get( $this->_pkey );
// Add all fields that we need to get from the database
foreach ($this->_fields as $field) {
// Don't reselect a pkey column if it was already added
if ( in_array( $field->dbField(), $this->_pkey ) ) {
continue;
}
if ( $field->apply('get') && $field->getValue() === null ) {
$query->get( $field->dbField() );
}
}
$this->_get_where( $query );
$query->left_join($this->_leftJoin);
$ssp = $this->_ssp_query( $query, $http );
if ( $id !== null ) {
$query->where( $this->pkeyToArray( $id, true ) );
}
$res = $query->exec();
if ( ! $res ) {
throw new \Exception('Error executing SQL for data get. Enable SQL debug using `->debug(true)`');
}
$out = array();
while ( $row=$res->fetch() ) {
$inner = array();
$inner['DT_RowId'] = $this->_idPrefix . $this->pkeyToValue( $row, true );
foreach ($this->_fields as $field) {
if ( $field->apply('get') ) {
$field->write( $inner, $row );
}
}
$out[] = $inner;
}
// Field options
$options = array();
$spOptions = array();
$sbOptions = array();
$searchPanes = array();
if ( $id === null ) {
foreach ($this->_fields as $field) {
$opts = $field->optionsExec( $this->_db );
if ( $opts !== false ) {
$options[ $field->name() ] = $opts;
}
// SearchPanes options
$spOpts = $field->searchPaneOptionsExec( $field, $this, $http, $this->_fields, $this->_leftJoin);
if ( $spOpts !== false ) {
$spOptions[ $field->name() ] = $spOpts;
}
$sbOpts = $field->searchBuilderOptionsExec($field, $this, $http, $this->_fields, $this->_leftJoin);
if ( $sbOpts !== false) {
$sbOptions[ $field->name() ] = $sbOpts;
}
}
}
$searchPanes[ 'options' ] = $spOptions;
$searchBuilder[ 'options' ] = $sbOptions;
// Row based "joins"
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
$this->_join[$i]->data( $this, $out, $options );
}
$this->_trigger( 'postGet', $out, $id );
if (count($searchPanes['options']) > 0 && count($searchBuilder['options']) > 0) {
return array_merge(
array(
'data' => $out,
'options' => $options,
'files' => $this->_fileData( null, null, $out ),
'searchBuilder' => $searchBuilder,
'searchPanes' => $searchPanes
),
$ssp
);
}
else if (count($searchBuilder['options']) > 0) {
return array_merge(
array(
'data' => $out,
'options' => $options,
'files' => $this->_fileData( null, null, $out ),
'searchBuilder' => $searchBuilder
),
$ssp
);
}
else if (count($searchPanes['options']) > 0) {
return array_merge(
array(
'data' => $out,
'options' => $options,
'files' => $this->_fileData( null, null, $out ),
'searchPanes' => $searchPanes
),
$ssp
);
}
else {
return array_merge(
array(
'data' => $out,
'options' => $options,
'files' => $this->_fileData( null, null, $out )
),
$ssp
);
}
}
/**
* Insert a new row in the database
* @private
*/
private function _insert( $values )
{
// Get values to generate the id, including from setValue, not just the
// submitted values
$all = array();
foreach ($this->_fields as $field) {
if ($field->apply('set', $values)) {
$this->_writeProp($all, $field->name(), $field->val( 'set', $values ));
}
}
// Only allow a composite insert if the values for the key are
// submitted. This is required because there is no reliable way in MySQL
// to return the newly inserted row, so we can't know any newly
// generated values.
$this->_pkey_validate_insert( $all );
$this->_trigger( 'validatedCreate', $values );
// Insert the new row
$id = $this->_insert_or_update( null, $values );
if ( $id === null ) {
return null;
}
// Was the primary key altered as part of the edit, if so use the
// submitted values
$id = count( $this->_pkey ) > 1 ?
$this->pkeyToValue( $all ) :
$this->_pkey_submit_merge( $id, $all );
// Join tables
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
$this->_join[$i]->create( $this, $id, $values );
}
$this->_trigger( 'writeCreate', $id, $values );
// Full data set for the created row
$row = $this->_get( $id );
$row = count( $row['data'] ) > 0 ?
$row['data'][0] :
null;
$this->_trigger( 'postCreate', $id, $values, $row );
return $row;
}
/**
* Update a row in the database
* @param string $id The DOM ID for the row that is being edited.
* @return array Row's data
* @private
*/
private function _update( $id, $values )
{
$id = str_replace( $this->_idPrefix, '', $id );
$this->_trigger( 'validatedEdit', $id, $values );
// Update or insert the rows for the parent table and the left joined
// tables
$this->_insert_or_update( $id, $values );
// And the join tables
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
$this->_join[$i]->update( $this, $id, $values );
}
// Was the primary key altered as part of the edit, if so use the
// submitted values
$getId = $this->_pkey_submit_merge( $id, $values );
$this->_trigger( 'writeEdit', $id, $values );
// Full data set for the modified row
$row = $this->_get( $getId );
$row = count( $row['data'] ) > 0 ?
$row['data'][0] :
null;
$this->_trigger( 'postEdit', $id, $values, $row );
return $row;
}
/**
* Delete one or more rows from the database
* @private
*/
private function _remove( $data )
{
$ids = array();
// Get the ids to delete from the data source
foreach ($data['data'] as $idSrc => $rowData) {
// Strip the ID prefix that the client-side sends back
$id = str_replace( $this->_idPrefix, "", $idSrc );
$res = $this->_trigger( 'preRemove', $id, $rowData );
// Allow the event to be cancelled and inform the client-side
if ( $res === false ) {
$this->_out['cancelled'][] = $idSrc;
}
else {
$ids[] = $id;
}
}
if ( count( $ids ) === 0 ) {
return;
}
// Row based joins - remove first as the host row will be removed which
// is a dependency
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
$this->_join[$i]->remove( $this, $ids );
}
// Remove from the left join tables
if ( $this->_leftJoinRemove ) {
for ( $i=0, $ien=count($this->_leftJoin) ; $i<$ien ; $i++ ) {
$join = $this->_leftJoin[$i];
$table = $this->_alias( $join['table'], 'orig' );
// which side of the join refers to the parent table?
if ( strpos( $join['field1'], $join['table'] ) === 0 ) {
$parentLink = $join['field2'];
$childLink = $join['field1'];
}
else {
$parentLink = $join['field1'];
$childLink = $join['field2'];
}
// Only delete on the primary key, since that is what the ids refer
// to - otherwise we'd be deleting random data! Note that this
// won't work with compound keys since the parent link would be
// over multiple fields.
if ( $parentLink === $this->_pkey[0] && count($this->_pkey) === 1 ) {
$this->_remove_table( $join['table'], $ids, array($childLink) );
}
}
}
// Remove from the primary tables
for ( $i=0, $ien=count($this->_table) ; $i<$ien ; $i++ ) {
$this->_remove_table( $this->_table[$i], $ids );
}
foreach ($data['data'] as $idSrc => $rowData) {
$id = str_replace( $this->_idPrefix, "", $idSrc );
$this->_trigger( 'postRemove', $id, $rowData );
}
}
/**
* File upload
* @param array $data Upload data
* @throws \Exception File upload name error
* @private
*/
private function _upload( $data )
{
// Search for upload field in local fields
$field = $this->_find_field( $data['uploadField'], 'name' );
$fieldName = '';
if ( ! $field ) {
// Perhaps it is in a join instance
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
$join = $this->_join[$i];
$fields = $join->fields();
for ( $j=0, $jen=count($fields) ; $j<$jen ; $j++ ) {
$joinField = $fields[ $j ];
$name = $join->name().'[].'.$joinField->name();
if ( $name === $data['uploadField'] ) {
$field = $joinField;
$fieldName = $name;
}
}
}
}
else {
$fieldName = $field->name();
}
if ( ! $field ) {
throw new \Exception("Unknown upload field name submitted");
}
$res = $this->_trigger( 'preUpload', $data );
// Allow the event to be cancelled and inform the client-side
if ( $res === false ) {
return;
}
$upload = $field->upload();
if ( ! $upload ) {
throw new \Exception("File uploaded to a field that does not have upload options configured");
}
$res = $upload->exec( $this );
if ( $res === false ) {
$this->_out['fieldErrors'][] = array(
"name" => $fieldName, // field name can be just the field's
"status" => $upload->error() // name or a join combination
);
}
else {
$files = $this->_fileData( $upload->table(), array($res) );
$this->_out['files'] = $files;
$this->_out['upload']['id'] = $res;
$this->_trigger( 'postUpload', $res, $files, $data );
}
}
/**
* Get information about the files that are detailed in the database for
* the fields which have an upload method defined on them.
*
* @param string [$limitTable=null] Limit the data gathering to a single
* table only
* @param number[] [$id=null] Limit to a specific set of ids
* @return array File information
* @private
*/
private function _fileData ( $limitTable=null, $ids=null, $data=null )
{
$files = array();
// The fields in this instance
$this->_fileDataFields( $files, $this->_fields, $limitTable, $ids, $data );
// From joined tables
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
$joinData = null;
// If we have data from the get, it is nested from the join, so we need to
// un-nest it (i.e. get the array of joined data for each row)
if ( $data ) {
$joinData = array();
for ( $j=0, $jen=count($data) ; $j<$jen ; $j++ ) {
$joinData = array_merge( $joinData, $data[$j][$this->_join[$i]->name()] );
}
}
$this->_fileDataFields( $files, $this->_join[$i]->fields(), $limitTable, $ids, $joinData );
}
return $files;
}
/**
* Common file get method for any array of fields
* @param array &$files File output array
* @param Field[] $fields Fields to get file information about
* @param string[] $limitTable Limit the data gathering to a single table
* only
* @private
*/
private function _fileDataFields ( &$files, $fields, $limitTable, $idsIn=null, $data=null )
{
foreach ($fields as $field) {
$upload = $field->upload();
if ( $upload ) {
$table = $upload->table();
if ( ! $table ) {
continue;
}
if ( $limitTable !== null && $table !== $limitTable ) {
continue;
}
// Make a collection of the ids used in this data set to get a limited data set
// in return (security and performance)
$ids = $idsIn;
if ( $ids === null ) {
$ids = array();
}
if ( $data !== null ) {
for ( $i=0, $ien=count($data); $i<$ien ; $i++ ) {
$val = $field->val( 'set', $data[$i] );
if ( $val && ! in_array($val, $ids) ) {
$ids[] = $val;
}
}
if ( count($ids) === 0 ) {
// If no data to fetch for this field, so don't bother
continue;
}
else if ( count($ids) > 1000 ) {
// Don't use `where_in` for really large data sets
$ids = array();
}
}
$fileData = $upload->data( $this->_db, $ids );
if ( $fileData !== null ) {
if ( isset($files[$table]) ) {
$files[$table] = $files[$table] + $fileData;
}
else {
$files[$table] = $fileData;
}
}
}
}
}
/**
* Run the file clean up
*
* @private
*/
private function _fileClean ()
{
foreach ( $this->_fields as $field ) {
$upload = $field->upload();
if ( $upload ) {
$upload->dbCleanExec( $this, $field );
}
}
for ( $i=0 ; $i<count($this->_join) ; $i++ ) {
foreach ( $this->_join[$i]->fields() as $field ) {
$upload = $field->upload();
if ( $upload ) {
$upload->dbCleanExec( $this, $field );
}
}
}
}
/* * * * * * * * * * * * * * * * * * * * * * * * *
* Server-side processing methods
*/
/**
* When server-side processing is being used, modify the query with // the
* required extra conditions
*
* @param \DataTables\Database\Query $query Query instance to apply the SSP commands to
* @param array $http Parameters from HTTP request
* @return array Server-side processing information array
* @private
*/
private function _ssp_query ( $query, $http )
{
if ( ! isset( $http['draw'] ) ) {
return array();
}
// Add the server-side processing conditions
$this->_ssp_limit( $query, $http );
$this->_ssp_sort( $query, $http );
$this->_ssp_filter( $query, $http );
// Get the number of rows in the result set
$ssp_set_count = $this->_db
->query('count')
->table( $this->_read_table() )
->get( $this->_pkey[0] );
$this->_get_where( $ssp_set_count );
$this->_ssp_filter( $ssp_set_count, $http );
$ssp_set_count->left_join($this->_leftJoin);
$ssp_set_count = $ssp_set_count->exec()->fetch();
// Get the number of rows in the full set
$ssp_full_count = $this->_db
->query('count')
->table( $this->_read_table() )
->get( $this->_pkey[0] );
$this->_get_where( $ssp_full_count );
if ( count( $this->_where ) ) { // only needed if there is a where condition
$ssp_full_count->left_join($this->_leftJoin);
}
$ssp_full_count = $ssp_full_count->exec()->fetch();
return array(
"draw" => intval( $http['draw'] ),
"recordsTotal" => $ssp_full_count['cnt'],
"recordsFiltered" => $ssp_set_count['cnt']
);
}
/**
* Convert a column index to a database field name - used for server-side
* processing requests.
* @param array $http HTTP variables (i.e. GET or POST)
* @param int $index Index in the DataTables' submitted data
* @returns string DB field name
* @throws \Exception Unknown fields
* @private Note that it is actually public for PHP 5.3 - thread 39810
*/
public function _ssp_field( $http, $index )
{
$name = $http['columns'][$index]['data'];
$field = $this->_find_field( $name, 'name' );
if ( ! $field ) {
// Is it the primary key?
if ( $name === 'DT_RowId' ) {
return $this->_pkey[0];
}
throw new \Exception('Unknown field: '.$name .' (index '.$index.')');
}
return $field->dbField();
}
/**
* Sorting requirements to a server-side processing query.
* @param \DataTables\Database\Query $query Query instance to apply sorting to
* @param array $http HTTP variables (i.e. GET or POST)
* @private
*/
private function _ssp_sort ( $query, $http )
{
if ( isset( $http['order'] ) ) {
for ( $i=0 ; $i<count($http['order']) ; $i++ ) {
$order = $http['order'][$i];
$query->order(
$this->_ssp_field( $http, $order['column'] ) .' '.
($order['dir']==='asc' ? 'asc' : 'desc')
);
}
}
}
private function _constructSearchBuilderConditions($query, $data) {
$first = true;
if(!isset($data['criteria'])) {
return;
}
// Iterate over every group or criteria in the current group
foreach($data['criteria'] as $crit) {
// If criteria is defined then this must be a group
if(isset($crit['criteria'])) {
// Check if this is the first, or if it is and logic
if($data['logic'] === 'AND' || $first) {
// Call the function for the next group
$query->where_group(function($q) use ($crit) {
$this->_constructSearchBuilderConditions($q, $crit);
});
// Set first to false so that in future only the logic is checked
$first = false;
}
else {
$query->where_group(function ($q) use ($crit) {
$this->_constructSearchBuilderConditions($q, $crit);
}, 'OR');
}
}
else if (isset($crit['condition']) && (isset($crit['value1']) || $crit['condition'] === 'null' || $crit['condition'] === '!null')) {
// Sometimes the structure of the object that is passed across is named in a strange way.
// This conditional assignment solves that issue
$val1 = isset($crit['value1']) ? $crit['value1'] : '';
$val2 = isset($crit['value2']) ? $crit['value2'] : '';
if(strlen($val1) === 0 && $crit['condition'] !== 'null' && $crit['condition'] !== '!null') {
continue;
}
if(strlen($val2) === 0 && ($crit['condition'] === 'between' || $crit['condition'] === '!between')) {
continue;
}
// Switch on the condition that has been passed in
switch($crit['condition']) {
case '=':
// Check if this is the first, or if it is and logic
if($data['logic'] === 'AND' || $first) {
// Call the where function for this condition
$query->where($crit['origData'], $val1, '=');
// Set first to false so that in future only the logic is checked
$first = false;
}
else {
// Call the or_where function - has to be or logic in this block
$query->or_where($crit['origData'], $val1, '=');
}
break;
case '!=':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], $val1, '<>');
$first = false;
}
else {
$query->or_where($crit['origData'], $val1, '<>');
}
break;
case 'contains':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], '%'.$val1.'%', 'LIKE');
$first = false;
}
else {
$query->or_where($crit['origData'], '%'.$val1.'%', 'LIKE');
}
break;
case '!contains':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], '%'.$val1.'%', 'NOT LIKE');
$first = false;
}
else {
$query->or_where($crit['origData'], '%'.$val1.'%', 'NOT LIKE');
}
break;
case 'starts':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], $val1.'%', 'LIKE');
$first = false;
}
else {
$query->or_where($crit['origData'], $val1.'%', 'LIKE');
}
break;
case '!starts':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], $val1.'%', 'NOT LIKE');
$first = false;
}
else {
$query->or_where($crit['origData'], $val1.'%', 'NOT LIKE');
}
break;
case 'ends':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], '%'.$val1, 'LIKE');
$first = false;
}
else {
$query->or_where($crit['origData'], '%'.$val1, 'LIKE');
}
break;
case '!ends':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], '%'.$val1, 'NOT LIKE');
$first = false;
}
else {
$query->or_where($crit['origData'], '%'.$val1, 'NOT LIKE');
}
break;
case '<':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], $val1, '<');
$first = false;
}
else {
$query->or_where($crit['origData'], $val1, '<');
}
break;
case '<=':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], $val1, '<=');
$first = false;
}
else {
$query->or_where($crit['origData'], $val1, '<=');
}
break;
case '>=':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], $val1, '>=');
$first = false;
}
else {
$query->or_where($crit['origData'], $val1, '>=');
}
break;
case '>':
if($data['logic'] === 'AND' || $first) {
$query->where($crit['origData'], $val1, '>');
$first = false;
}
else {
$query->or_where($crit['origData'], $val1, '>');
}
break;
case 'between':
if($data['logic'] === 'AND' || $first) {
$query->where_group(function($q) use ($crit, $val1, $val2) {
$q->where($crit['origData'], is_numeric($val1) ? intval($val1) : $val1, '>')->where($crit['origData'], is_numeric($val2) ? intval($val2) : $val2, '<');
});
$first = false;
}
else {
$query->or_where($crit['origData'], is_numeric($val1) ? intval($val1) : $val1, '>')->where($crit['origData'], is_numeric($val2) ? intval($val2) : $val2, '<');
}
break;
case '!between':
if($data['logic'] === 'AND' || $first) {
$query->where_group(function($q) use ($crit, $val1, $val2) {
$q->where($crit['origData'], is_numeric($val1) ? intval($val1) : $val1, '<')->or_where($crit['origData'], is_numeric($val2) ? intval($val2) : $val2, '>');
});
$first = false;
}
else {
$query->or_where($crit['origData'], is_numeric($val1) ? intval($val1) : $val1, '<')->or_where($crit['origData'], is_numeric($val2) ? intval($val2) : $val2, '>');
}
break;
case 'null':
if($data['logic'] === 'AND' || $first) {
$query->where_group(function ($q) use ($crit) {
$q->where($crit['origData'], null, "=");
if (strpos($crit['type'], 'date') === false && strpos($crit['type'], 'moment') === false && strpos($crit['type'], 'luxon') === false) {
$q->or_where($crit['origData'], "", "=");
}
});
$first = false;
}
else {
$query->where_group(function ($q) use ($crit) {
$q->where($crit['origData'], null, "=");
if (strpos($crit['type'], 'date') === false && strpos($crit['type'], 'moment') === false && strpos($crit['type'], 'luxon') === false) {
$q->or_where($crit['origData'], "", "=");
}
}, 'OR');
}
break;
case '!null':
if($data['logic'] === 'AND' || $first) {
$query->where_group(function ($q) use ($crit) {
$q->where($crit['origData'], null, "!=");
if (strpos($crit['type'], 'date') === false && strpos($crit['type'], 'moment') === false && strpos($crit['type'], 'luxon') === false) {
$q->where($crit['origData'], "", "!=");
}
});
$first = false;
}
else {
$query->where_group(function ($q) use ($crit) {
$q->where($crit['origData'], null, "!=");
if (strpos($crit['type'], 'date') === false && strpos($crit['type'], 'moment') === false && strpos($crit['type'], 'luxon') === false) {
$q->where($crit['origData'], "", "!=");
}
}, 'OR');
}
break;
default:
break;
}
}
}
return $query;
}
/**
* Add DataTables' 'where' condition to a server-side processing query. This
* works for both global and individual column filtering.
* @param \DataTables\Database\Query $query Query instance to apply the WHERE conditions to
* @param array $http HTTP variables (i.e. GET or POST)
* @private
*/
private function _ssp_filter ( $query, $http )
{
$that = $this;
// Global filter
$fields = $this->_fields;
// Global search, add a ( ... or ... ) set of filters for each column
// in the table (not the fields, just the columns submitted)
if ( $http['search']['value'] ) {
$query->where( function ($q) use (&$that, &$fields, $http) {
for ( $i=0 ; $i<count($http['columns']) ; $i++ ) {
if ( $http['columns'][$i]['searchable'] == 'true' ) {
$fieldName = $that->_ssp_field( $http, $i );
if ( $fieldName ) {
$q->or_where( $fieldName, '%'.$http['search']['value'].'%', 'like' );
}
}
}
} );
}
/*
foreach ($this->_fields as $field) {
// Don't reselect a pkey column if it was already added
if ( in_array( $field->dbField(), $this->_pkey ) ) {
continue;
}
if ( $field->apply('get') && $field->getValue() === null ) {
$query->get( $field->dbField() );
}
}
*/
if( isset($http['searchPanes']) ) {
// Set the database from editor
$db = $this->_db;
// For every selection in every column
foreach ($this->_fields as $field) {
if( isset($http['searchPanes'][$field->name()])){
for($i = 0; $i < count($http['searchPanes'][$field->name()]); $i++) {
// Check the number of rows...
$q = $db
->query('select')
->table($this->_table)
->get('COUNT(*) as cnt');
$q->left_join($this->_leftJoin);
// ... where the selected option is present...
$r = $q
->where($field->dbField(), $http['searchPanes'][$field->name()][$i], '=')
->exec()
->fetchAll();
// ... If there are none then don't bother with this selection
if($r[0]['cnt'] == 0) {
array_splice($http['searchPanes'][$field->name()], $i, 1);
$i--;
}
}
$query->where( function ($q) use ($field, $http) {
for($j=0 ; $j<count($http['searchPanes'][$field->name()]) ; $j++){
$q->or_where(
$field->dbField(),
isset($http['searchPanes_null'][$field->name()][$j])
? null
: $http['searchPanes'][$field->name()][$j],
'='
);
}
});
}
}
}
if(isset($http['searchBuilder']) && $http['searchBuilder'] !== 'false') {
$query->where_group(function($q) use ($http) {
$this->_constructSearchBuilderConditions($q, $http['searchBuilder']);
});
}
// if ( $http['search']['value'] ) {
// $words = explode(" ", $http['search']['value']);
// $query->where( function ($q) use (&$that, &$fields, $http, $words) {
// for ( $j=0, $jen=count($words) ; $j<$jen ; $j++ ) {
// if ( $words[$j] ) {
// $q->where_group( true );
// for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
// if ( $http['columns'][$i]['searchable'] == 'true' ) {
// $field = $that->_ssp_field( $http, $i );
// $q->or_where( $field, $words[$j].'%', 'like' );
// $q->or_where( $field, '% '.$words[$j].'%', 'like' );
// }
// }
// $q->where_group( false );
// }
// }
// } );
// }
// Column filters
for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
$column = $http['columns'][$i];
$search = $column['search']['value'];
if ( $search !== '' && $column['searchable'] == 'true' ) {
$query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );
}
}
}
/**
* Add a limit / offset to a server-side processing query
* @param \DataTables\Database\Query $query Query instance to apply the offset / limit to
* @param array $http HTTP variables (i.e. GET or POST)
* @private
*/
private function _ssp_limit ( $query, $http )
{
if ( $http['length'] != -1 ) { // -1 is 'show all' in DataTables
$query
->offset( $http['start'] )
->limit( $http['length'] );
}
}
/* * * * * * * * * * * * * * * * * * * * * * * * *
* Internal helper methods
*/
/**
* Add local WHERE condition to query
* @param \DataTables\Database\Query $query Query instance to apply the WHERE conditions to
* @private
*/
private function _get_where ( $query )
{
for ( $i=0 ; $i<count($this->_where) ; $i++ ) {
if ( is_callable( $this->_where[$i] ) ) {
$this->_where[$i]( $query );
}
else {
$query->where(
$this->_where[$i]['key'],
$this->_where[$i]['value'],
$this->_where[$i]['op']
);
}
}
}
/**
* Get a field instance from a known field name
*
* @param string $name Field name
* @param string $type Matching name type
* @return Field Field instance
* @private
*/
private function _find_field ( $name, $type )
{
for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
$field = $this->_fields[ $i ];
if ( $type === 'name' && $field->name() === $name ) {
return $field;
}
else if ( $type === 'db' && $field->dbField() === $name ) {
return $field;
}
}
return null;
}
/**
* Insert or update a row for all main tables and left joined tables.
*
* @param int|string $id ID to use to condition the update. If null is
* given, the first query performed is an insert and the inserted id
* used as the value should there be any subsequent tables to operate
* on. Mote that for compound keys, this should be the "joined" value
* (i.e. a single string rather than an array).
* @return \DataTables\Database\Result Result from the query or null if no
* query performed.
* @private
*/
private function _insert_or_update ( $id, $values )
{
// Loop over all tables in _table, doing the insert or update as needed
for ( $i=0, $ien=count( $this->_table ) ; $i<$ien ; $i++ ) {
$res = $this->_insert_or_update_table(
$this->_table[$i],
$values,
$id !== null ?
$this->pkeyToArray( $id, true ) :
null
);
// If we don't have an id yet, then the first insert will return
// the id we want
if ( $res !== null && $id === null ) {
$id = $res->insertId();
}
}
// And for the left join tables as well
for ( $i=0, $ien=count( $this->_leftJoin ) ; $i<$ien ; $i++ ) {
$join = $this->_leftJoin[$i];
// which side of the join refers to the parent table?
$joinTable = $this->_alias( $join['table'], 'alias' );
$tablePart = $this->_part( $join['field1'] );
if ( $this->_part( $join['field1'], 'db' ) ) {
$tablePart = $this->_part( $join['field1'], 'db' ).'.'.$tablePart;
}
if ( $tablePart === $joinTable ) {
$parentLink = $join['field2'];
$childLink = $join['field1'];
}
else {
$parentLink = $join['field1'];
$childLink = $join['field2'];
}
if ( $parentLink === $this->_pkey[0] && count($this->_pkey) === 1 ) {
$whereVal = $id;
}
else {
// We need submitted information about the joined data to be
// submitted as well as the new value. We first check if the
// host field was submitted
$field = $this->_find_field( $parentLink, 'db' );
if ( ! $field || ! $field->apply( 'set', $values ) ) {
// If not, then check if the child id was submitted
$field = $this->_find_field( $childLink, 'db' );
// No data available, so we can't do anything
if ( ! $field || ! $field->apply( 'set', $values ) ) {
continue;
}
}
$whereVal = $field->val('set', $values);
}
$whereName = $this->_part( $childLink, 'field' );
$this->_insert_or_update_table(
$join['table'],
$values,
array( $whereName => $whereVal )
);
}
return $id;
}
/**
* Insert or update a row in a single database table, based on the data
* given and the fields configured for the instance.
*
* The function will find the fields which are required for this specific
* table, based on the names of fields and use only the appropriate data for
* this table. Therefore the full submitted data set can be passed in.
*
* @param string $table Database table name to use (can include an alias)
* @param array $where Update condition
* @return \DataTables\Database\Result Result from the query or null if no query
* performed.
* @throws \Exception Where set error
* @private
*/
private function _insert_or_update_table ( $table, $values, $where=null )
{
$set = array();
$action = ($where === null) ? 'create' : 'edit';
$tableAlias = $this->_alias( $table, 'alias' );
for ( $i=0 ; $i<count($this->_fields) ; $i++ ) {
$field = $this->_fields[$i];
$tablePart = $this->_part( $field->dbField() );
if ( $this->_part( $field->dbField(), 'db' ) ) {
$tablePart = $this->_part( $field->dbField(), 'db' ).'.'.$tablePart;
}
// Does this field apply to this table (only check when a join is
// being used)
if ( count($this->_leftJoin) && $tablePart !== $tableAlias ) {
continue;
}
// Check if this field should be set, based on options and
// submitted data
if ( ! $field->apply( $action, $values ) ) {
continue;
}
// Some db's (specifically postgres) don't like having the table
// name prefixing the column name. Todo: it might be nicer to have
// the db layer abstract this out?
$fieldPart = $this->_part( $field->dbField(), 'field' );
$set[ $fieldPart ] = $field->val( 'set', $values );
}
// Add where fields if setting where values and required for this
// table
// Note that `whereSet` is now deprecated
if ( $this->_whereSet ) {
for ( $j=0, $jen=count($this->_where) ; $j<$jen ; $j++ ) {
$cond = $this->_where[$j];
if ( ! is_callable( $cond ) ) {
// Make sure the value wasn't in the submitted data set,
// otherwise we would be overwriting it
if ( ! isset( $set[ $cond['key'] ] ) )
{
$whereTablePart = $this->_part( $cond['key'], 'table' );
// No table part on the where condition to match against
// or table operating on matches table part from cond.
if ( ! $whereTablePart || $tableAlias == $whereTablePart ) {
$set[ $cond['key'] ] = $cond['value'];
}
}
else {
throw new \Exception( 'Where condition used as a setter, '.
'but value submitted for field: '.$cond['key']
);
}
}
}
}
// If nothing to do, then do nothing!
if ( ! count( $set ) ) {
return null;
}
// Use pkey only for the host table
$pkey = in_array( $table, $this->_table ) !== false ?
$this->_pkey :
'';
// Insert or update
if ( $action === 'create' ) {
return $this->_db->insert( $table, $set, $pkey );
}
else {
return $this->_db->push( $table, $set, $where, $pkey );
}
}
/**
* Delete one or more rows from the database for an individual table
*
* @param string $table Database table name to use
* @param array $ids Array of ids to remove
* @param string $pkey Database column name to match the ids on for the
* delete condition. If not given the instance's base primary key is
* used.
* @private
*/
private function _remove_table ( $table, $ids, $pkey=null )
{
if ( $pkey === null ) {
$pkey = $this->_pkey;
}
$tableAlias = $this->_alias($table, 'alias');
$tableOrig = $this->_alias($table, 'orig');
// If using an alias we need to replace the alias'ed table name in our pkey
// with the real table name
for ($i=0 ; $i<count($pkey) ; $i++) {
$a = explode('.', $pkey[$i]);
if (count($a) > 1 && $a[0] === $tableAlias) {
$a[0] = $tableOrig;
$pkey[$i] = implode('.', $a);
}
}
// Check there is a field which has a set option for this table
$count = 0;
foreach ($this->_fields as $field) {
$fieldName = $field->dbField();
$fieldDots = substr_count( $fieldName, '.' );
if ( $fieldDots === 0 ) {
$count++;
}
else if ( $fieldDots === 1 ) {
if (
$field->set() !== Field::SET_NONE &&
$this->_part( $fieldName, 'table' ) === $tableAlias
) {
$count++;
}
}
else {
// db link
// note that if the table name for the constructor uses a db part, we need to also have
// the fields using the db name as Editor doesn't do any conflict resolution.
$dbTable = $this->_part( $fieldName, 'db' ) .'.'. $this->_part( $fieldName, 'table' );
if ( $field->set() !== Field::SET_NONE && $dbTable === $table ) {
$count++;
}
}
}
if ( $count > 0 ) {
$q = $this->_db
->query( 'delete' )
->table( $tableOrig );
for ( $i=0, $ien=count($ids) ; $i<$ien ; $i++ ) {
$cond = $this->pkeyToArray( $ids[$i], true, $pkey );
$q->or_where( function ($q2) use ($cond) {
$q2->where( $cond );
} );
}
$q->exec();
}
}
/**
* Check the validity of the set options if we are doing a join, since
* there are some conditions for this state. Will throw an error if not
* valid.
*
* @private
*/
private function _prepJoin ()
{
if ( count( $this->_leftJoin ) === 0 ) {
return;
}
// Check if the primary key has a table identifier - if not - add one
for ( $i=0, $ien=count($this->_pkey) ; $i<$ien ; $i++ ) {
$val = $this->_pkey[$i];
if ( strpos( $val, '.' ) === false ) {
$this->_pkey[$i] = $this->_alias( $this->_table[0], 'alias' ).'.'.$val;
}
}
// Check that all fields have a table selector, otherwise, we'd need to
// know the structure of the tables, to know which fields belong in
// which. This extra requirement on the fields removes that
for ( $i=0, $ien=count($this->_fields) ; $i<$ien ; $i++ ) {
$field = $this->_fields[$i];
$name = $field->dbField();
if ( strpos( $name, '.' ) === false ) {
throw new \Exception( 'Table part of the field "'.$name.'" was not found. '.
'In Editor instances that use a join, all fields must have the '.
'database table set explicitly.'
);
}
}
}
/**
* Get one side or the other of an aliased SQL field name.
*
* @param string $name SQL field
* @param string $type Which part to get: `alias` (default) or `orig`.
* @returns string Alias
* @private
*/
private function _alias ( $name, $type='alias' )
{
if ( stripos( $name, ' as ' ) !== false ) {
$a = preg_split( '/ as /i', $name );
return $type === 'alias' ?
$a[1] :
$a[0];
}
if ( stripos( $name, ' ' ) !== false ) {
$a = preg_split( '/ /i', $name );
return $type === 'alias' ?
$a[1] :
$a[0];
}
return $name;
}
/**
* Get part of an SQL field definition regardless of how deeply defined it
* is
*
* @param string $name SQL field
* @param string $type Which part to get: `table` (default) or `db` or
* `column`
* @return string Part name
* @private
*/
private function _part ( $name, $type='table' )
{
$db = null;
$table = null;
$column = null;
if ( strpos( $name, '.' ) !== false ) {
$a = explode( '.', $name );
if ( count($a) === 3 ) {
$db = $a[0];
$table = $a[1];
$column = $a[2];
}
else if ( count($a) === 2 ) {
$table = $a[0];
$column = $a[1];
}
}
else {
$column = $name;
}
if ( $type === 'db' ) {
return $db;
}
else if ( $type === 'table' ) {
return $table;
}
return $column;
}
/**
* Trigger an event
*
* @private
*/
private function _trigger ( $eventName, &$arg1=null, &$arg2=null, &$arg3=null, &$arg4=null, &$arg5=null )
{
$out = null;
$argc = func_num_args();
$args = array( $this );
// Hack to enable pass by reference with a "variable" number of parameters
for ( $i=1 ; $i<$argc ; $i++ ) {
$name = 'arg'.$i;
$args[] = &$$name;
}
if ( ! isset( $this->_events[ $eventName ] ) ) {
return;
}
$events = $this->_events[ $eventName ];
for ( $i=0, $ien=count($events) ; $i<$ien ; $i++ ) {
$res = call_user_func_array( $events[$i], $args );
if ( $res !== null ) {
$out = $res;
}
}
return $out;
}
/**
* Merge a primary key value with an updated data source.
*
* @param string $pkeyVal Old primary key value to merge into
* @param array $row Data source for update
* @return string Merged value
*/
private function _pkey_submit_merge ( $pkeyVal, $row )
{
$pkey = $this->_pkey;
$arr = $this->pkeyToArray( $pkeyVal, true );
for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
$column = $pkey[ $i ];
$field = $this->_find_field( $column, 'db' );
if ( $field && $field->apply( 'edit', $row ) ) {
$arr[ $column ] = $field->val( 'set', $row );
}
}
return $this->pkeyToValue( $arr, true );
}
/**
* Validate that all primary key fields have values for create.
*
* @param array $row Row's data
* @return boolean `true` if valid, `false` otherwise
*/
private function _pkey_validate_insert ( $row )
{
$pkey = $this->_pkey;
if ( count( $pkey ) === 1 ) {
return true;
}
for ( $i=0, $ien=count($pkey) ; $i<$ien ; $i++ ) {
$column = $pkey[ $i ];
$field = $this->_find_field( $column, 'db' );
if ( ! $field || ! $field->apply("create", $row) ) {
throw new \Exception( "When inserting into a compound key table, ".
"all fields that are part of the compound key must be ".
"submitted with a specific value.", 1
);
}
}
return true;
}
/**
* Create the separator value for a compound primary key.
*
* @return string Calculated separator
*/
private function _pkey_separator ()
{
$str = implode(',', $this->_pkey);
return hash( 'crc32', $str );
}
private function _read_table ()
{
return count($this->_readTableNames) ?
$this->_readTableNames :
$this->_table;
}
}
Reference in New Issue View Git Blame Copy Permalink