se/SE/se-lib/AclQueryBuilder.php

<?php

Lib::loadClass('P5');
Lib::loadClass('Core_AclBase');
Lib::loadClass('AntAclBase');
Lib::loadClass('ACL');

class AclQueryBuilder {

  public $select;
  public $from;
  public $where;
  public $orderBy;
  public $groupBy;
  public $limit;
  public $offset;
  public $_fromPrefix;
  public $_joinPrefix;
  public $_joinParams;
  public $isInstances;
  public $isNotInstances;

  public function __construct() {
    $this->select = [];
    $this->from = null; // (ACL | tableName)
    $this->where = [];
    $this->orderBy = null;
    $this->groupBy = null;
    $this->limit = null;
    $this->offset = null;
    $this->_fromPrefix = 't'; // prefix for this->from - default 't'
    $this->_joinPrefix = []; // prefix => from (Acl | tableName)
    $this->_joinParams = []; // prefx => params
    $this->isInstances = [];
    $this->isNotInstances = [];
  }

  public function select($propertyName) { // TODO: ogc:propertyName, *, xpath, @instances, etc...
    if (!empty($propertyName) && !in_array($propertyName, $this->select)) $this->select[] = $propertyName;
    return $this;
  }

  public function from($from, $prefix = 't') {
    DBG::log([
      'from instanceof Core_AclBase' => ($from instanceof Core_AclBase),
      'from instanceof AntAclBase' => ($from instanceof AntAclBase),
    ], 'array', "\$from class(".get_class($from).")");
    if ($this->from) throw new Exception("Duplicate FROM");
    $this->from = $from;
    $this->_fromPrefix = $prefix;
    return $this;
  }

  public function where($params) {
    if (!empty($params['rawWhere'])) {
      $this->where[] = $params['rawWhere'];
    } else {
      DBG::log($params, 'array', "Where not supported");
      throw new Exception("Where not supported");
    }
  }

  public function join($join, $prefix, $params) {
    if (array_key_exists($prefix, $this->_joinPrefix)) throw new Exception("Prefix already used!");
    $this->_joinPrefix[$prefix] = $join;
    $this->_joinParams[$prefix] = $params;
    return $this;
  }

  public function orderBy($orderBy) { // TODO: ogc: order by ...

    return $this;
  }

  public function limit($limit) {
    $this->limit = (int)$limit;
    return $this;
  }

  public function offset($offset) {
    $this->offset = (int)$offset;
    return $this;
  }

  public function isInstance($instances) {
    $this->isInstances = (is_array($instances)) ? $instances : [ $instances ];
    return $this;
  }

  public function isNotInstance($instances) {
    $this->isNotInstances = (is_array($instances)) ? $instances : [ $instances ];
    return $this;
  }

  public function _parseJoinParams($params) {
    if (array_key_exists('rawJoin', $params)) return $params['rawJoin'];
    throw new Exception("Not implemented JOIN params '".json_encode($params)."'");
  }

  public function _getTableName($source) {
    if (is_scalar($source)) return $source;
    if ($source instanceof Core_AclBase) return $source->getRootTableName();
    throw new Exception("Not implemented FROM type '".get_class($source)."'");
  }

  public function execute() {
    $sql = $this->generateSql();
    DBG::log((array)$this, 'array', "AclQueryBuilder");
    return DB::getPDO()->fetchAll($sql);
  }

  public function generateSql() {
    if (!$this->from) throw new Exception("Missing FROM");
    $tableName = $this->_getTableName($this->from);
    if (!$tableName) throw new Exception("Missing FROM table name");

    $sqlPk = 'ID';
    if ($this->from instanceof Core_AclBase) $sqlPk = $this->from->getSqlPrimaryKeyField();

    $sqlJoin = [];
    foreach ($this->isInstances as $k => $ns) {
      $idInstance = ACL::getInstanceId($ns);
      $instanceTable = ACL::getInstanceTable($ns);
      // ->join($instanceTable, 'i', [ 'rawJoin' => "i.pk = t.{$sqlPk} and i.idInstance = {$idInstance}" ])
      $prefix = "is_inst_{$k}";
      // $sqlJoin[] = " inner join `{$joinName}` {$prefix} on (
      //   {$prefix}.pk = {$this->_fromPrefix}.{$sqlPk}
      //   and {$prefix}.idInstance = {$idInstance}
      // )";
      $this->where[] = "{$this->_fromPrefix}.{$sqlPk} in (
        select {$prefix}.pk
        from `{$instanceTable}` {$prefix}
        where {$prefix}.idInstance = {$idInstance}
      )";
      DBG::log("{$this->_fromPrefix}.{$sqlPk} in (
        select {$prefix}.pk
        from `{$instanceTable}` {$prefix}
        where {$prefix}.idInstance = {$idInstance}
      )", 'string', "\$this->where[] =");
    }
    foreach ($this->isNotInstances as $k => $ns) {
      $idInstance = ACL::getInstanceId($ns);
      $instanceTable = ACL::getInstanceTable($ns);
      // ->join($instanceTable, 'i', [ 'rawJoin' => "i.pk = t.{$sqlPk} and i.idInstance = {$idInstance}" ])
      $prefix = "is_inst_{$k}";
      // $sqlJoin[] = " inner join `{$joinName}` {$prefix} on (
      //   {$prefix}.pk = {$this->_fromPrefix}.{$sqlPk}
      //   and {$prefix}.idInstance = {$idInstance}
      // )";
      $this->where[] = "{$this->_fromPrefix}.{$sqlPk} not in (
        select {$prefix}.pk
        from `{$instanceTable}` {$prefix}
        where {$prefix}.idInstance = {$idInstance}
      )";
    }
    // join `{$instanceTable}` i on(i.pk = t.{$sqlPk} and i.idInstance = {$idInstance})
    foreach ($this->_joinPrefix as $prefix => $join) {
      $joinName = $this->_getTableName($join);
      $sqlJoin[] = " join `{$joinName}` {$prefix} on(" . $this->_parseJoinParams($this->_joinParams[$prefix]) . ")";
    }

    $sqlJoin = (!empty($sqlJoin)) ? implode("\n\t", $sqlJoin) : "";

    $sqlWhere = [];
    foreach ($this->where as $where) {
      $sqlWhere[] = $where;
    }
    $sqlWhere = (!empty($sqlWhere)) ? "where " . implode("\n\t and ", $sqlWhere) : '';

    $limit = ($this->limit < 0) ? 0 : $this->limit;
    $offset = ($this->offset < 0) ? 0 : $this->offset;
    $sqlLimit = ($limit > 0) ? "limit {$limit} offset {$offset}" : '';

    $sqlSelect = [];
    $sqlSelect[] = "{$this->_fromPrefix}.{$sqlPk}";
    if (in_array('*', $this->select)) $sqlSelect[] = "{$this->_fromPrefix}.*";
    if (in_array('@instances', $this->select)) {
      if (!($this->from instanceof Core_AclBase)) throw new Exception("select @instances allowed only for Acl object");
      $instanceTable = ACL::getInstanceTable($this->from->getNamespace());
      $sqlSelect[] = "
        (
          select GROUP_CONCAT(inst_conf.namespace)
          from `{$instanceTable}` inst_tbl
            join `CRM_INSTANCE_CONFIG` inst_conf on (inst_conf.id = inst_tbl.idInstance)
          where inst_tbl.pk = {$this->_fromPrefix}.{$sqlPk}
        ) as `@instances`
      ";
    }
    $sqlSelect = (!empty($sqlSelect)) ? implode(", ", $sqlSelect) : "{$this->_fromPrefix}.*";
    return "
      select {$sqlSelect}
      from `{$tableName}` {$this->_fromPrefix}
        {$sqlJoin}
      {$sqlWhere}
      {$sqlLimit}
    ";
  }

}