<?php
namespace Diplix\KMGBundle\DataTables;
use Diplix\KMGBundle\DataTables\Expr\ExprStub;
use Doctrine\ORM\Query\Expr\Andx;
use Doctrine\ORM\Query\Expr\Orx;
use Doctrine\ORM\QueryBuilder;
use Symfony\Component\HttpFoundation\Request;
/**
* (c) C.Schmidhuber, Version Nov2018
* Class DataTablesHelper
* @package Diplix\KMGBundle\DataTables
*/
class DataTablesHelper
{
const T_RAW = "";
const T_BUTTONS = "action_buttons";
const T_SELECTOR = "selectorcheckbox";
const T_CSSICON = "cssicon";
const T_COMMENT = "comment";
// the default setup for a single column
public static $defaultSetup = array(
"fieldName"=>null, // the name of the database field
"caption"=>null, // the head title for the html table
"virtual"=>false, // set to true to exclude from default filter handling
"searchable"=>true, // allow searching
"searchWithLike"=>true, // use like for field-specific search
"sortable"=>true, // allow sorting for the column
"visible"=>true, // show/hide columns,
"footer"=>false, // add .nofooter class to footer cell
"type"=>self::T_RAW,// display type
"headStyle"=>"", // additional styles for the th-tag
);
/** @var array Our complete setup*/
protected $columnSetup = array(
"columns" => array(),
"ajaxUrl"=>null,
"ajaxData"=>null,
"ajaxType"=>null, // GET / POST
"deferLoading"=>null,
"defaultSorting"=>null,
"searching"=>true, // https://datatables.net/reference/option/searching
"autoCaptionPrefix"=>"th.",
"addTableClass" => "",
"addTableStyle" => "",
'stateSave' => false,
'stateContext' => null,
);
/**
* @var QueryBuilder
*/
protected $queryBuilder;
protected $additionalOrX = [];
protected $additionalAndX = [];
protected $additionalParams = [];
protected $additionalOrdering = [];
protected $primaryOrdering = [];
/**
* DataTablesHelper constructor.
* @param array $columSetup see self::$defaultSetup for parameters
* @throws \Exception
*/
public function __construct(array $columnSetup,$options=array())
{
// take column settings
foreach ($columnSetup as $idx=>$arr)
{
// check for unknown fields
if (count(array_diff_key($arr,self::$defaultSetup))>0)
{
throw new \Exception(sprintf("columnSetup[%d] includes unknown fields",$idx));
}
// diverging default values for special types
if (isset($arr["type"]))
{
if ($arr["type"]==self::T_BUTTONS) $arr = array_merge(array("sortable"=>false,"searchable"=>false),$arr);
if ($arr["type"]==self::T_SELECTOR) $arr = array_merge(array("sortable"=>false,"searchable"=>false),$arr);
if ($arr["type"]==self::T_COMMENT) $arr = array_merge(array("searchable"=>false),$arr);
}
// fill missing fields with default values
$this->columnSetup["columns"][$idx] = array_merge(self::$defaultSetup,$arr);
}
// take other settings
if (count($options)>0)
foreach ($options as $k=>$o)
{
if (array_key_exists($k,$this->columnSetup))
{
$this->columnSetup[$k] = $o;
}
else
{
throw new \Exception(sprintf("Unknown parameter: %s",$k));
}
}
}
public function setQueryBuilder(QueryBuilder $queryBuilder)
{
$this->queryBuilder = $queryBuilder;
}
public function processRequest(Request $request)
{
$req = array_merge($request->query->all() , $request->request->all());
$this->addRequestParametersToDbQuery($this->queryBuilder,$this->columnSetup["columns"],$req);
}
public function getColumnSetup()
{
return $this->columnSetup;
}
protected function makeCaptionFromFieldName($fieldName)
{
$caption = "";
// remove entity reference part before the dot
$dp = strrpos($fieldName,".");
if ($dp!==false) $fieldName = substr($fieldName,$dp+1);
// transform
$len = strlen($fieldName);
for ($i = 0; $i < $len; ++$i) {
if (ctype_upper($fieldName[$i])) {
$caption .= ' '.$fieldName[$i];
} else {
$caption .= strtolower($fieldName[$i]);
}
}
return ucfirst($caption);
}
public function addOr($expr,$params=[])
{
if (!is_array($expr)) $expr = [ $expr ];
foreach ($expr as $x)
{
$this->additionalOrX[]= $x;
}
$this->additionalParams = array_merge($this->additionalParams,$params);
}
public function addAnd($expr,$params=[])
{
if (!is_array($expr)) $expr = [ $expr ];
foreach ($expr as $x)
{
$this->additionalAndX[]= $x;
}
$this->additionalParams = array_merge($this->additionalParams,$params);
}
public function addOrderBy($expr,$order)
{
$this->additionalOrdering[$expr] = $order;
}
public function addPrimaryOrderBy($expr,$order)
{
$this->primaryOrdering[$expr] = $order;
}
/**
* @param \Doctrine\ORM\QueryBuilder $cb
* @param $knownColumns array
* @param $req array array containing the request parameters ($request->query->all())
*/
protected function addRequestParametersToDbQuery(\Doctrine\ORM\QueryBuilder $cb, $knownColumns, $req )
{
$dtColumns = (array)$req['columns']; // the columns known to datatables js
$params = array();
// filtering (global search)
$orLikes = array();
if ((isset($req['search']['value']))&&(strlen(trim($req['search']['value']))>0))
{
foreach ($dtColumns as $k=>$e)
{
if ($knownColumns[$k]["virtual"]) continue; // virtual fields have to processed manually
if (($e["searchable"]=="true")&&($knownColumns[$k]["searchable"]))
{
$orLikes[] = $cb->expr()->like($knownColumns[$k]["fieldName"], ":orlike".$k );
$params["orlike".$k] = "%".addcslashes($req['search']['value'], "%_")."%";
}
}
}
$orLikes2 = array_merge( $orLikes , $this->additionalOrX );
if (count($orLikes2)>0) $cb->andWhere(new Orx($orLikes2));
// filtering (field-wise search, not taking into account the actual field type)
$andLikes = array();
foreach($dtColumns as $k=>$e)
{
if ($knownColumns[$k]["virtual"]) continue; // virtual fields have to processed manually
if (($e['searchable']=="true") && ($knownColumns[$k]["searchable"]) && (trim($e['search']['value'])!=""))
{
if ($knownColumns[$k]["searchWithLike"])
{
$andLikes[] = $cb->expr()->like($knownColumns[$k]["fieldName"], ":andlike".$k );
$params["andlike".$k] = "%".addcslashes($e['search']['value'], "%_")."%";
}
else
{
$andLikes[]= $cb->expr()->eq($knownColumns[$k]["fieldName"], ":andlike".$k);
$params["andlike".$k] = $e['search']['value'];
}
}
}
$andLikes2 = array_merge($andLikes, $this->additionalAndX);
if (count($andLikes2)>0) $cb->andWhere(new Andx($andLikes2));
foreach ( array_merge($params,$this->additionalParams) as $k=>$e)
{
$cb->setParameter($k,$e);
}
// ordering
foreach ($this->primaryOrdering as $expr => $order)
{
$cb->addOrderBy($expr,$order);
}
if (isset($req['order']))
{
$order = (array)$req['order'];
if (count($order)>0)
foreach ($order as $o)
if ( (array_key_exists($o["column"],$knownColumns)) && ($knownColumns[$o["column"]]["sortable"]) )
{
if ($knownColumns[$o['column']]["virtual"]) continue; // virtual fields have to processed manually
$cb->addOrderBy( $knownColumns[$o['column']]["fieldName"] , $o['dir']);
}
}
foreach ($this->additionalOrdering as $expr => $order)
{
$cb->addOrderBy($expr,$order);
}
// paging
if (isset($req['start'])) $cb->setFirstResult($req['start']);
if ( (isset($req['length'])) && ($req['length']>0)) $cb->setMaxResults($req['length']);
}
public function getRawColumnOrder($req)
{
$raw = [];
$knownColumns = $this->columnSetup["columns"];
if (isset($req['order']))
{
$order = (array)$req['order'];
if (count($order) > 0)
foreach ($order as $o)
if ((array_key_exists($o["column"], $knownColumns)))
{
$raw[$o['column']] = $o["dir"];
}
}
return $raw;
}
protected function getUniqueName()
{
return "param".rand(1000,9999);
}
public function addSimpleFilter($colIndex,$req,$order,$orX=[],$orderExpr,$weak=false,$includedToGlobal=true)
{
if (count($orX)>0)
{
// column specific search means the search term has to be there (AND)
if ($colIndex>=0)
{
$column = $req["columns"][$colIndex];
$reqFilter = (isset($column["search"]["value"]) ? $column["search"]["value"] : "");
if ($reqFilter!="")
{
if ($weak) $reqFilter = "%".addcslashes($reqFilter, "%_")."%";
$u = $this->getUniqueName();
$parts = [];
/** @var ExprStub[] $orX */
for ($i=0;$i<count($orX);$i++) $parts[]= $orX[$i]->resurrect(":".$u);
$this->addAnd(new OrX($parts), [$u=>$reqFilter]);
}
}
if ($includedToGlobal)
{
// global search filter just looks for matches everywhere (OR)
$optFilter = $req["search"]["value"];
if ($optFilter!="")
{
if ($weak) $optFilter = "%".addcslashes($optFilter, "%_")."%";
$u = $this->getUniqueName();
$parts = [];
/** @var ExprStub[] $orX */
for ($i=0;$i<count($orX);$i++) $parts[] = $orX[$i]->resurrect(":".$u);
$this->addOr(new OrX($parts), [$u=>$optFilter]);
}
}
}
if (array_key_exists($colIndex,$order))
{
$this->addOrderBy($orderExpr,$order[$colIndex]);
}
}
}