/**
* 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 array $sql_details SQL connection details - see sql_connect()
* @param string $table SQL table to query
* @param string $primaryKey Primary key of the table
* @param array $columns Column information array
* @param array $joinQuery Join query String
* @param string $extraWhere Where query String
*
* @return array Server-side processing response array
*
*/
static function simple($conn, $request, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '')
{
// static function simple ( $request, $sql_details, $table, $primaryKey, $columns,
// $joinQuery = NULL, $extraWhere = '', $groupBy = '') {
$bindings = array();
//$db = SSP::sql_connect( $sql_details );
// Build the SQL query string from the request
$limit = SSP::limit($request, $columns);
$order = SSP::order($request, $columns, $joinQuery);
$where = SSP::filter($request, $columns, $bindings, $joinQuery);
// IF Extra where set then set and prepare query
if ($extraWhere) {
$extraWhere = $where ? ' AND ' . $extraWhere : ' WHERE ' . $extraWhere;
}
$groupBy = $groupBy ? ' GROUP BY ' . $groupBy . ' ' : '';
// Main query to actually get the data
if ($joinQuery) {
$col = SSP::pluck($columns, 'db', $joinQuery);
$query = "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $col) . "\n {$joinQuery}\n {$where}\n {$extraWhere}\n {$groupBy}\n {$order}\n {$limit}";
} else {
$query = "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", SSP::pluck($columns, 'db')) . "\n FROM {$table}\n {$where}\n {$extraWhere}\n {$groupBy}\n {$order}\n {$limit}";
}
$conn->Execute("SET NAMES 'utf8'");
$data = SSP::sql_exec($conn, $bindings, $query);
// Data set length after filtering
$resFilterLength = SSP::sql_exec($conn, "SELECT FOUND_ROWS() as cnt");
$recordsFiltered = $resFilterLength[0][cnt];
// Total data set length
$resTotalLength = SSP::sql_exec($conn, "SELECT COUNT({$primaryKey}) as cnt FROM {$table}");
$recordsTotal = $resTotalLength[0][cnt];
//file_put_contents('d:\query', $query);
//file_put_contents('d:\bindings', $bindings);
//file_put_contents('d:\11', varDump($resFilterLength));
//file_put_contents('d:\12', $recordsFiltered);
//file_put_contents('d:\data', varDump($data));
/*
* Output
*/
return array("draw" => intval($request['draw']), "recordsTotal" => intval($recordsTotal), "recordsFiltered" => intval($recordsFiltered), "data" => SSP::data_output($columns, $data, $joinQuery));
}
/**
* 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
* @return string SQL where clause
*/
static function filter($request, $columns)
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = SSP::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') {
if (isset($column['coloumn_name'])) {
$globalSearch[] = $column['coloumn_name'] . " LIKE " . '"%' . $str . '%"';
} else {
$globalSearch[] = $column['db'] . " LIKE " . '"%' . $str . '%"';
}
}
}
}
// Individual column filtering
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 != '') {
if (isset($column['coloumn_name'])) {
$columnSearch[] = $column['coloumn_name'] . " LIKE " . '"%' . $str . '%"';
} else {
if (in_array($column['db'], array("ad.clad_category", "ad.clad_id", "u_id"))) {
$columnSearch[] = $column['db'] . " LIKE " . '"' . $str . '%"';
} else {
$columnSearch[] = $column['db'] . " LIKE " . '"%' . $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);
}
return $where;
}
/**
* 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
EDIT : added $mywhere functionality for passing initial filtering conditions
*/
static function filter($request, $columns, &$bindings, $myWhere)
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = SSP::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') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$globalSearch[] = "" . $column['db'] . " LIKE " . $binding;
}
}
}
// Individual column filtering
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 != '') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$columnSearch[] = "" . $column['db'] . " LIKE " . $binding;
}
}
// Combine the filters into a single string
$where = '';
if (count($globalSearch)) {
$where = '(' . implode(' OR ', $globalSearch) . ')';
}
if (count($columnSearch)) {
$where = $where === '' ? implode(' AND ', $globalSearch) : $where . ' AND ' . implode(' AND ', $globalSearch);
}
if ($where !== '') {
$where = 'WHERE ' . $where;
// add my clause
if ($myWhere !== '') {
$where .= ' AND ' . $myWhere;
}
}
if ($where == '' && $myWhere !== '') {
// add my clause
$where = 'WHERE ' . $myWhere;
}
return $where;
}
$whereAll .= $filter_sql;
function jp_bind($bindings)
{
$return_array = array();
if (is_array($bindings)) {
for ($i = 0, $ien = count($bindings); $i < $ien; $i++) {
//$binding = $bindings[$i];
// $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
$return_array[$bindings[$i]['key']] = $bindings[$i]['val'];
}
}
return $return_array;
}
$where .= !empty($where) ? " AND " . $whereAll : "WHERE " . $whereAll;
$bindings = jp_bind($bindings);
$complete_query = "SELECT SQL_CALC_FOUND_ROWS `" . implode("`, `", SSP::pluck($columns, 'db')) . "`\n FROM `qry_assets` {$where} {$order} {$limit}";
// echo $complete_query;
//var_dump($bindings);
// echo $complete_query;
// var_dump($bindings);
// die;
$data = $con->myQuery($complete_query, $bindings)->fetchAll();
$recordsFiltered = $con->myQuery("SELECT FOUND_ROWS();")->fetchColumn();
$recordsTotal = $con->myQuery("SELECT COUNT(id) FROM `qry_assets` {$where};", $bindings)->fetchColumn();
$json['draw'] = isset($request['draw']) ? intval($request['draw']) : 0;
$json['recordsTotal'] = $recordsFiltered;
$json['recordsFiltered'] = $recordsFiltered;
$json['data'] = SSP::data_output($columns, $data);
echo json_encode($json);
// $resTotalLength = SSP::sql_exec( $db, $bindings,
// "SELECT COUNT(`{$primaryKey}`)
/**
* 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 array $sql_details SQL connection details - see sql_connect()
* @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, $sql_details, $table, $primaryKey, $columns) {
$bindings = array();
$db = SSP::sql_connect($sql_details);
// Build the SQL query string from the request
$limit = SSP::limit($request, $columns);
$order = SSP::order($request, $columns);
$where = SSP::filter($request, $columns, $bindings);
// Main query to actually get the data
$data = SSP::sql_exec($db, $bindings, "SELECT SQL_CALC_FOUND_ROWS `" . implode("`, `", SSP::pluck($columns, 'db')) . "`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = SSP::sql_exec($db, "SELECT FOUND_ROWS()"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = SSP::sql_exec($db, "SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => intval($request['draw']),
"recordsTotal" => intval($recordsTotal),
"recordsFiltered" => intval($recordsFiltered),
"data" => SSP::data_output($columns, $data)
);
}
/**
* 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 array $sql_details SQL connection details - see sql_connect()
* @param string $table SQL table to query
* @param string $primaryKey Primary key of the table
* @param array $columns Column information array
* @param array $joinQuery Join query String
* @param string $extraWhere Where query String
*
* @return array Server-side processing response array
*
*/
static function simple($request, $sql_details, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '')
{
$bindings = array();
$db = SSP::sql_connect($sql_details);
// Build the SQL query string from the request
$limit = SSP::limit($request, $columns);
$order = SSP::order($request, $columns, $joinQuery);
$where = SSP::filter($request, $columns, $bindings, $joinQuery);
// IF Extra where set then set and prepare query
if ($extraWhere) {
$extraWhere = $where ? ' AND ' . $extraWhere : ' WHERE ' . $extraWhere;
}
$groupBy = $groupBy ? ' GROUP BY ' . $groupBy . ' ' : '';
// Main query to actually get the data
if ($joinQuery) {
$col = SSP::pluck($columns, 'db', $joinQuery);
$query = "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $col) . "\n\t\t\t {$joinQuery}\n\t\t\t {$where}\n\t\t\t {$extraWhere}\n {$groupBy}\n\t\t\t {$order}\n\t\t\t {$limit}";
} else {
$query = "SELECT SQL_CALC_FOUND_ROWS `" . implode("`, `", SSP::pluck($columns, 'db')) . "`\n\t\t\t FROM `{$table}`\n\t\t\t {$where}\n\t\t\t {$extraWhere}\n\t\t\t {$groupBy}\n {$order}\n\t\t\t {$limit}";
}
$data = SSP::sql_exec($db, $bindings, $query);
// Data set length after filtering
$resFilterLength = SSP::sql_exec($db, "SELECT FOUND_ROWS()");
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$count_request = "SELECT COUNT(`{$primaryKey}`)";
if ($joinQuery) {
$count_request .= $joinQuery;
} else {
$count_request .= "FROM `{$table}`";
}
$resTotalLength = SSP::sql_exec($db, $count_request);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array("draw" => intval($request['draw']), "recordsTotal" => intval($recordsTotal), "recordsFiltered" => intval($recordsFiltered), "data" => SSP::data_output($columns, $data, $joinQuery));
}
