EXCELIA_DBARR/custom/multicompany/class/ssp.class.php

<?php
/* Copyright (C) 2017-2021	Regis Houssin	<regis.houssin@inodbox.com>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 */

/**
 *   \file			/multicompany/class/ssp.class.php
 *   \ingroup		multicompany
 *   \brief			File of class to manage DataTables server-side processing
 */

/**
 * 		\class      SSP
 *      \brief      DataTables server-side processing class
 */

class SSP
{
	/**
	 * Create the data output array for the DataTables rows
	 *
	 *  @param  array $columns Column information array
	 *  @param  array $data    Data from the SQL get
	 *  @return array          Formatted data in a row based format
	 */
	static function data_output($columns, $data)
	{
		$out = array();

		for ($i=0, $ien=count($data); $i<$ien; $i++) {
			$row = array();

			for ($j=0, $jen=count($columns); $j<$jen; $j++) {
				$column = $columns[$j];

				// Is there a formatter?
				if (isset( $column['formatter'])) {
					$row[$column['dt']] = $column['formatter']($data[$i][$column['db']], $data[$i]);
				} else {
					$row[$column['dt']] = $data[$i][$columns[$j]['db']];
				}
			}

			$out[] = $row;
		}

		return $out;
	}

	/**
	 * Paging
	 *
	 * Construct the LIMIT clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL limit clause
	 */
	static function limit($request, $columns, $db)
	{
		$limit = '';

		if (isset($request['start']) && $request['length'] != -1) {
			$limit = $db->plimit(intval($request['length']), intval($request['start']));
		}

		return $limit;
	}

	/**
	 * Ordering
	 *
	 * Construct the ORDER BY clause for server-side processing SQL query
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @return string SQL order by clause
	 */
	static function order($request, $columns, $db)
	{
		$order = '';
		$sortfield = array();
		$sortorder = array();

		if (isset($request['order']) && count($request['order'])) {
			$dtColumns = self::pluck( $columns, 'dt' );
			for ($i=0, $ien=count($request['order']); $i<$ien; $i++) {
				// Convert the column index into the column data property
				$columnIdx = intval($request['order'][$i]['column']);
				$requestColumn = $request['columns'][$columnIdx];

				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[ $columnIdx ];

				if ($requestColumn['orderable'] == 'true') {
					$dir = $request['order'][$i]['dir'] === 'asc' ? 'ASC' : 'DESC';
					$sortfield[] = $column['db'];
					$sortorder[] = $dir;
				}
			}

			$order = $db->order(implode(', ', $sortfield), implode(', ', $sortorder));
		}

		return $order;
	}

	/**
	 * Searching / Filtering
	 *
	 * Construct the WHERE clause for server-side processing SQL query.
	 *
	 * NOTE this does not match the built-in DataTables filtering which does it
	 * word by word on any field. It's possible to do here performance on large
	 * databases would be very poor
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  array $columns Column information array
	 *  @param  array $bindings Array of values for PDO bindings, used in the
	 *    sql_exec() function
	 *  @return string SQL where clause
	 */
	static function filter($request, $columns, $db)
	{
		$globalSearch = array();
		$columnSearch = array();
		$dtColumns = self::pluck( $columns, 'dt' );

		if (isset($request['search']) && $request['search']['value'] != '') {
			$str = $request['search']['value'];

			for ($i=0, $ien=count($request['columns']); $i<$ien; $i++) {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[ $columnIdx ];

				if ($requestColumn['searchable'] == 'true') {
					$globalSearch[] = $column['db']." LIKE '%" . $db->escape($str)."%'";
				}
			}
		}

		// Individual column filtering
		if (isset( $request['columns'])) {
			for ($i=0, $ien=count($request['columns']); $i<$ien; $i++) {
				$requestColumn = $request['columns'][$i];
				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[$columnIdx];

				$str = $requestColumn['search']['value'];

				if ($requestColumn['searchable'] == 'true' && $str != '') {
					$columnSearch[] = $column['db']." LIKE '%" . $db->escape($str)."%'";
				}
			}
		}

		// Combine the filters into a single string
		$where = '';

		if (count( $globalSearch)) {
			$where = '('.implode(' OR ', $globalSearch).')';
		}
		if (count( $columnSearch)) {
			$where = $where === '' ?
				implode(' AND ', $columnSearch) :
				$where .' AND '. implode(' AND ', $columnSearch);
		}
		if ($where !== '') {
			$where = 'WHERE '.$where;
		}

		return $where;
	}

	/**
	 * Perform the SQL queries needed for an server-side processing requested,
	 * utilising the helper functions of this class, limit(), order() and
	 * filter() among others. The returned array is ready to be encoded as JSON
	 * in response to an SSP request, or can be modified if needed before
	 * sending back to the client.
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  string $table SQL table to query
	 *  @param  string $primaryKey Primary key of the table
	 *  @param  array $columns Column information array
	 *  @return array          Server-side processing response array
	 */
	static function simple($request, $db, $table, $primaryKey, $columns)
	{
		// Build the SQL query string from the request
		$limit = self::limit( $request, $columns, $db );
		$order = self::order( $request, $columns, $db );
		$where = self::filter( $request, $columns, $db );

		// Main query to actually get the data
		$sql =
			"SELECT ".implode(", ", self::pluck($columns, 'db'))."
			 FROM $table
			 $where
			 $order
			 $limit";

		$data = self::sql_exec($db, $sql);
		//print_r($data);

		// Data set length after filtering
		$resFilterLength = self::sql_exec($db,
			"SELECT COUNT({$primaryKey})
			 FROM   $table
			 $where"
		);
		$recordsFiltered = $resFilterLength[0][0];

		// Total data set length
		$resTotalLength = self::sql_exec($db,
			"SELECT COUNT({$primaryKey})
			 FROM   $table"
		);
		$recordsTotal = $resTotalLength[0][0];

		/*
		 * Output
		 */
		return array(
			"draw"            => (isset ( $request['draw'] ) ? intval( $request['draw'] ) : 0),
			"recordsTotal"    => intval( $recordsTotal ),
			"recordsFiltered" => intval( $recordsFiltered ),
			"data"            => self::data_output( $columns, $data )
		);
	}

	/**
	 * The difference between this method and the `simple` one, is that you can
	 * apply additional `where` conditions to the SQL queries. These can be in
	 * one of two forms:
	 *
	 * * 'Result condition' - This is applied to the result set, but not the
	 *   overall paging information query - i.e. it will not effect the number
	 *   of records that a user sees they can have access to. This should be
	 *   used when you want apply a filtering condition that the user has sent.
	 * * 'All condition' - This is applied to all queries that are made and
	 *   reduces the number of records that the user can access. This should be
	 *   used in conditions where you don't want the user to ever have access to
	 *   particular records (for example, restricting by a login id).
	 *
	 *  @param  array $request Data sent to server by DataTables
	 *  @param  string $table SQL table to query
	 *  @param  string $primaryKey Primary key of the table
	 *  @param  array $columns Column information array
	 *  @param  string $whereResult WHERE condition to apply to the result set
	 *  @param  string $whereAll WHERE condition to apply to all queries
	 *  @return array          Server-side processing response array
	 */
	static function complex($request, $db, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null)
	{
		$whereAllSql = '';

		// Build the SQL query string from the request
		$limit = self::limit( $request, $columns, $db );
		$order = self::order( $request, $columns, $db );
		$where = self::filter( $request, $columns, $db );

		$whereResult = self::_flatten( $whereResult );
		$whereAll = self::_flatten( $whereAll );

		if (!empty($whereResult)) {
			$where = $where ? $where .' AND '.$whereResult : 'WHERE '.$whereResult;
		}

		if (!empty($whereAll)) {
			$where = $where ? $where .' AND '.$whereAll : 'WHERE '.$whereAll;
			$whereAllSql = 'WHERE '.$whereAll;
		}

		// Main query to actually get the data
		$data = self::sql_exec($db,
			"SELECT ".implode(", ", self::pluck($columns, 'db'))."
			 FROM $table $where $order $limit"
		);

		// Data set length after filtering
		$resFilterLength = self::sql_exec($db,
			"SELECT COUNT({$primaryKey})
			 FROM $table $where"
		);
		$recordsFiltered = $resFilterLength[0][0];

		// Total data set length
		$resTotalLength = self::sql_exec($db,
			"SELECT COUNT({$primaryKey})
			 FROM   $table ".$whereAllSql
		);
		$recordsTotal = $resTotalLength[0][0];

		/*
		 * Output
		 */
		return array(
			"draw"            => (isset($request['draw']) ? intval($request['draw']) : 0),
			"recordsTotal"    => intval($recordsTotal),
			"recordsFiltered" => intval($recordsFiltered),
			"data"            => self::data_output($columns, $data)
		);
	}

	/**
	 * Execute an SQL query on the database
	 *
	 * @param  string   $sql SQL query to execute.
	 * @return array         Result from the query (all rows)
	 */
	static function sql_exec($db, $sql)
	{
		$out=array();

		$resql = $db->query($sql);
		if (!empty($resql)) {
			$i = 0;
			$num_rows = $db->num_rows($resql);
			while ($i < $num_rows) {
				$array = $db->fetch_array($resql);
				array_push($out, $array);
				$i++;
			}
		}

		return $out;
	}

	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	 * Internal methods
	 */

	/**
	 * Pull a particular property from each assoc. array in a numeric array,
	 * returning and array of the property values from each item.
	 *
	 *  @param  array  $a    Array to get data from
	 *  @param  string $prop Property to read
	 *  @return array        Array of property values
	 */
	static function pluck($a, $prop)
	{
		$out = array();

		for ($i=0, $len=count($a); $i<$len; $i++) {
			$out[] = $a[$i][$prop];
		}

		return $out;
	}

	/**
	 * Return a string from an array or a string
	 *
	 * @param  array|string $a Array to join
	 * @param  string $join Glue for the concatenation
	 * @return string Joined string
	 */
	static function _flatten($a, $join = ' AND ')
	{
		if (empty($a)) {
			return '';
		} else if (!empty($a) && is_array($a)) {
			return implode( $join, $a );
		}
		return $a;
	}
}