se/SE/se-lib/AclQueryFeatures.php

<?php

Lib::loadClass('ACL');
Lib::loadClass('SqlQueryWhereBuilder');

// usage: (Acl class)::buildQuery($params): return new AclQueryFeatures($this, $params);
//        (view): $queryFeatures = $acl->buildQuery($params);
//        (view): $total = $queryFeatures->getTotal();
//        (view): $items = $queryFeatures->getItems();
// example: @see TableAcl, TableAjax
class AclQueryFeatures {

  public $_params;
  public $_acl;
  public $_query;
  public $_total;
  public $_legacyMode;

  public function __construct($acl, $params) {
    $this->_acl = $acl;
    $this->_params = $params;
    $this->_query = null;
    $this->_total = null;
    $this->_legacyMode = false;
    // TODO: _legacyMode = ($from instanceof simple schema or another programmed objects)
  }

  public function parseQueryValue($fieldName, $searchQuery, $fieldType = 'xsd:string') {
    if ('!NULL' === $searchQuery) return ['is not null', null];
    if ('IS NOT NULL' === $searchQuery) return ['is not null', null];
    if ('NULL' === $searchQuery) return ['is null', null];
    if ('IS NULL' === $searchQuery) return ['is null', null];
    switch ($fieldType) {
      case 'gml:PolygonPropertyType':
      case 'gml:PointPropertyType':
      case 'gml:LineStringPropertyType':
      case 'gml:GeometryPropertyType': return $this->_parseGeomQuery($searchQuery);
      //   $sqlFilter = $this->_sqlValueForGeomField($fldName, $v, 't');
      // if ('_CSV_NUM' == substr($fldName, -8)) { // if ($this->isCsvNumericField($fldName)) { // TODO: xsd type - p5:csv_num
      //   $sqlFilter = $this->_sqlValueForCsvNumericField($fldName, $v, 't');
      //   if ($sqlFilter) $sql_where_and[] = $sqlFilter;
      //   continue;
      // }
    }

    switch (substr($searchQuery, 0, 1)) {
      case '=': return ['=', substr($searchQuery, 1)];
      case '>':
        switch (substr($searchQuery, 1, 1)) {
          case '=': return ['>=', substr($searchQuery, 2)];
          default:  return ['>', substr($searchQuery, 1)];
        }
      case '<':
        switch (substr($searchQuery, 1, 1)) {
          case '=': return ['<=', substr($searchQuery, 2)];
          case '>': return ['!=', substr($searchQuery, 2)];
          default:  return ['<', substr($searchQuery, 1)];
        }
      case '!':
        switch (substr($searchQuery, 1, 1)) {
          case '=': return ['!=', substr($searchQuery, 2)];
          default:  return ['not like', substr($searchQuery, 1)];
        }
      default: {
        switch ($fieldType) {
          case 'xsd:number':
          case 'xsd:integer': return ['=', $searchQuery];
          default: {
            if (false !== strpos($searchQuery, '%')) return ['like', $searchQuery];
            $queryWhereBuilder = new SqlQueryWhereBuilder();
            return ['and'
              , array_map(function ($word) use ($fieldName) {
                  return [$fieldName, 'like', "%{$word}%"];
                }, $queryWhereBuilder->splitQueryToWords($searchQuery)
              )
            ];
          }
        }
        return ['=', $searchQuery];
      }
    }
  }
  public function _parseGeomQuery($searchQuery) { // _sqlValueForGeomField($fldName, $fltrValue, $tblPrefix = 't')
    // example: BBOX:54.40993961633866,18.583889010112824,54.337945760687454,18.397121431987586
    DBG::log($searchQuery, 'string', "\$searchQuery");
    if ('BBOX:' == substr($searchQuery, 0, 5)) {
      $valParts = explode(',', substr($searchQuery, 5));
      if (4 !== count($valParts)) throw new Exception("Wrong BBOX query");
      $valParts = array_filter($valParts, 'is_numeric');
      if (4 !== count($valParts)) throw new Exception("Wrong BBOX query - expected 4 numeric values");
      $bounds = "POLYGON((
        {$valParts[3]} {$valParts[2]},
        {$valParts[3]} {$valParts[0]},
        {$valParts[1]} {$valParts[0]},
        {$valParts[1]} {$valParts[2]},
        {$valParts[3]} {$valParts[2]}
      ))";
      // for mysql 5.6 use ST_Contains() @see http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions.html
      return [ 'Intersects', $bounds ];
    }
    else if ('GeometryType=' == substr($fltrValue, 0, 13)) {
      return [ 'GeometryType', substr($fltrValue, 13) ];
    }
    throw new Exception("Not implemented geometry query string"); // TODO:? return null;
  }

  public function _sqlValueForCsvNumericField($fldName, $fltrValue, $tblPrefix = 't') {
    $sqlFilter = false;
    if (is_numeric($fltrValue)) {
      $sqlFilter = "FIND_IN_SET('{$fltrValue}', `{$fldName}`)>0";
    } else if (false !== strpos($fltrValue, ' ')) {
      $sqlGlue = " or ";
      $fltrValues = $fltrValue;
      if ('&' == substr($fltrValues, 0, 1)) {
        $fltrValues = substr($fltrValues, 1);
        $sqlGlue = " and ";
      }
      $fltrValues = explode(' ', $fltrValues);
      $sqlNumericValues = array();
      foreach ($fltrValues as $fltrVal) {
        if (is_numeric($fltrVal)) {
          $sqlNumericValues[] = "FIND_IN_SET('{$fltrVal}', `{$fldName}`)>0";
        }
      }
      if (!empty($sqlNumericValues)) {
        $sqlFilter = "(" . implode($sqlGlue, $sqlNumericValues) . ")";
      }
    }
    return $sqlFilter;
  }

  public function getQuery() {
    if ($this->_query) return $this->_query;
    // $ds = $this->_acl->getDataSource(); // TODO: only for TableAcl // TODO: move _parseSqlWhere to this class
    $filtrIsInstance = [];
    $filtrIsNotInstance = [];
    $this->_query = ACL::query($this->_acl)
      ->isInstance($filtrIsInstance)
      ->isNotInstance($filtrIsNotInstance);
    // ->join($instanceTable, 'i', [ 'rawJoin' => "i.pk = t.{$sqlPrimaryKey} and i.idInstance = {$idInstance}" ])
    // $this->_query->where([
    //   'rawWhere' => $ds->_parseSqlWhere($params)
    // ]);
    foreach ($this->_params as $k => $v) {
      if ('f_' === substr($k, 0, 2) && strlen($k) > 3) {
        $fieldName = substr($k, 2);
        $fieldType = $this->_acl->getXsdFieldType($fieldName);
        list($comparisonSign, $value) = $this->parseQueryValue($fieldName, $v, $fieldType);
        DBG::log([ $fieldName, $fieldType, $comparisonSign, $value ], 'array', "parseQueryValue");
        $this->_query->where($fieldName, $comparisonSign, $value);
      } else if ('sf_' === substr($k, 0, 3) && strlen($k) > 4) {
      } else if ('ogc:Filter' == $k) {
      } else if ('primaryKey' == $k) {
      }
    }
    return $this->_query;
  }

  public function getTotal() {
    if ($this->_legacyMode) return $this->_acl->getTotal($this->_params);
    if (null !== $this->_total) return $this->_total;
    $this->_total = $this->getQuery()
      ->select([ 'rawSelect' => "count(1) as cnt" ])
      ->fetchValue();
    return $this->_total;
  }

  public function hasParam($key) { return !empty($this->_params[$key]); }
  public function getParam($key) { return V::get($key, '', $this->_params); }

  public function getItems() {
    if ($this->_legacyMode) return $this->_acl->getTotal($this->_params);
    // 'limit' => 10,
    //  'limitstart' => 0,
    //  'order_by' => 'ID',
    //  'order_dir' => 'desc',
    // TODO: sortBy from wfs query
    $sortBy = $this->hasParam('order_by')
      ? ( $this->hasParam('order_dir')
          ? $this->getParam('order_by') . " " . $this->getParam('order_dir')
          : $this->getParam('order_by')
        )
      : '';
    $limit = V::get('limit', 10, $this->_params, 'int');
    $offset = V::get('limitstart', 0, $this->_params, 'int');
    $ds = $this->_acl->getDataSource(); // TODO: only for TableAcl // TODO: move _parseSqlWhere to this class

    DBG::log(['params' => $this->_params, 'sortBy' => $sortBy, 'limit' => $limit, 'offset' => $offset], 'array', '$this->_params');
    return $this->getQuery()
      ->select([
        'rawSelect' =>   $ds->_getSqlCols()
      ]) // TODO: fields
      ->select(!empty($this->_params['@instances']) ? '@instances' : null)
      ->limit($limit)
      ->offset($offset)
      ->orderBy($sortBy)
      ->fetchAll();
  }

  public function getItem($primaryKey) { throw new HttpException("Acl function " . __FUNCTION__ . " Not implemented", 501); }

}