se/SE/se-lib/AclQueryFeatures.php

<?php

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

// 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

// Special Filter Access - btns visible only if user don't have super access perms. If has, then will always see all rows.

class AclQueryFeatures {

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

  public function __construct($acl, $params, $legacyMode = false) {
    $this->_acl = $acl;
    $this->_params = $params;
    $this->_query = null;
    $this->_total = null;
    $this->_legacyMode = $legacyMode;
    // 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': {
            if (false !== strpos($searchQuery, '%')) return ['like', $searchQuery];
            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 parseSpecialFilterMsgs($type) {
    $rootTableName = $this->_acl->getRootTableName();
    DBG::log($rootTableName, 'string', "parse SpecialFilter Msgs({$type}), \$rootTableName");
    $sqlHasFltrMsgs = "
      select 1
      from `CRM_UI_MSGS` m
      where m.`uiTargetName`=CONCAT('{$rootTableName}.', t.`ID`)
        and m.`uiTargetType`='default_db_table_record'
        and m.`A_STATUS` not in('DELETED')
      limit 1
    ";
    switch ($type) {
      case 'HAS_MSGS': return " ({$sqlHasFltrMsgs})=1 ";
      case 'NO_MSGS':  return " ({$sqlHasFltrMsgs}) is null ";
      case 'NEW_MSGS': {
        $sqlNewFltrMsgs = "
          select 1
          from `CRM_UI_MSGS` m
          where m.`uiTargetName`=CONCAT('{$rootTableName}.', t.`ID`)
            and m.`uiTargetType`='default_db_table_record'
            and m.`A_STATUS` in('WAITING')
          limit 1
        ";
        return " ({$sqlNewFltrMsgs})=1 ";
      }
    }
    return null;
  }

  public function parseSpecialFilterProblemy($type) {
    DBG::log($type, 'string', "parse SpecialFilter Problemy");
    switch ($type) {
      case 'PROBLEM': return ['A_PROBLEM', '!=', ''];
      case 'WARNING': return ['A_PROBLEM', '=', 'WARNING'];
      case 'NORMAL':  return ['A_PROBLEM', '=', 'NORMAL'];
    }
    return null;
  }

  public function parseSpecialFilterStatus($type) {
    DBG::log($type, 'string', "parse SpecialFilter Status");
    switch ($type) {
      case 'WAITING': return ['A_STATUS', '=', 'WAITING'];
      case 'AKTYWNI': return ['A_STATUS', 'or', [ // `A_STATUS` in('NORMAL', 'WARNING') ";
        ['A_STATUS', '=', 'NORMAL'],
        ['A_STATUS', '=', 'WARNING'],
      ] ];
    }
    return null;
  }

  public function parseSpecialFilterSpotkania($type) {
    DBG::log($type, 'string', "parse SpecialFilter Spotkania");
    switch ($type) {
      case 'OLD': return ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN_NOW'];
      //           COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < UNIX_TIMESTAMP()
      //           and t.`L_APPOITMENT_DATE` != ''
      //           and t.`L_APPOITMENT_DATE` != '0000-00-00 00:00:00'
      case 'NOW': return ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_NOW_3600'];
      //           COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < UNIX_TIMESTAMP()+3600
      //           and COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) > UNIX_TIMESTAMP()-3600
      case 'TODAY': return ['L_APPOITMENT_DATE', 'and', [
        ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_GREATER_THAN', mktime(0,0,0, date("m"), date("d"),     date("Y"))],
        ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN',    mktime(0,0,0, date("m"), date("d") + 1, date("Y"))],
      ] ];
      //         $start = mktime(0,0,0, date("m"), date("d"), date("Y"));
      //         $end = mktime(0,0,0, date("m"), date("d") + 1, date("Y"));
      //         $sqlFltr = "
      //           COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) > '{$start}'
      //           and COALESCE(UNIX_TIMESTAMP(t.`L_APPOITMENT_DATE`), 0) < '{$end}'
      //         ";
      case 'TOMORROW': return ['L_APPOITMENT_DATE', 'and', [
        ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_GREATER_THAN', mktime(0,0,0, date("m"), date("d") + 1, date("Y"))],
        ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN',    mktime(0,0,0, date("m"), date("d") + 2, date("Y"))],
      ] ];
      case 'YESTERDAY': return ['L_APPOITMENT_DATE', 'and', [
        ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_GREATER_THAN', mktime(0,0,0, date("m"), date("d") - 2, date("Y"))],
        ['L_APPOITMENT_DATE', 'UNIX_TIMESTAMP_LESS_THAN',    mktime(0,0,0, date("m"), date("d") - 1, date("Y"))],
      ] ];
      case 'BRAK': return ['L_APPOITMENT_DATE', 'or', [
        ['L_APPOITMENT_DATE', '=', ''],
        ['L_APPOITMENT_DATE', '=', '0000-00-00 00:00:00'],
      ] ];
    }
    return null;
  }

  public function parseSpecialFilterAccess() {
    $userLogin = User::getLogin();
    $usrAclGroups = User::getLdapGroupsNames();
    DBG::log(['login'=>$userLogin, 'groups'=>$usrAclGroups, 'acl'=>$this->_acl], 'array', "parse SpecialFilter Access");
    $orWhere = [];
    if ($this->_acl->hasField('A_ADM_COMPANY')) {
      $orWhere[] = ['A_ADM_COMPANY', '=', ''];// TODO: allow empty for everyone?
      foreach ($usrAclGroups as $group) $orWhere[] = ['A_ADM_COMPANY', '=', $group];
    }
    if ($this->_acl->hasField('A_CLASSIFIED')) {
      $orWhere[] = ['A_CLASSIFIED', '=', ''];// TODO: allow empty for everyone?
      foreach ($usrAclGroups as $group) $orWhere[] = ['A_CLASSIFIED', '=', $group];
    }
    if (!empty($orWhere) && $this->_acl->hasField('L_APPOITMENT_USER')) {
      $orWhere[] = ['L_APPOITMENT_USER', '=', $userLogin];
    }
    return (!empty($orWhere)) ? [null, 'or', $orWhere] : null;
  }

  public function parseOgcFilter($ogcFilter) {
    $parser = new ParseOgcFilter();
    $parser->loadOgcFilter($ogcFilter);
    $queryWhereBuilder = $parser->convertToSqlQueryWhereBuilder();
    return $queryWhereBuilder->getQueryWhere('t'); // TODO: $this->_fromPrefix
  }

  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($ds->_parseSqlWhere($params))
    DBG::log($this->_params, 'array', "AclQueryFeatures::getQuery \$this->_params");
    foreach ($this->_params as $k => $v) {
      // DBG::log(['v'=>$v, 'is_numeric' => is_numeric($k), 'is_int' => is_int($k), 'is_array' => is_array($v)], 'array', "AclQueryFeatures::getQuery \$this->_params[{$k}]");
      if (is_int($k) && is_array($v)) {
        $this->_query->where($v); // TODO: check format [$fieldName, $comparisonSign, $value]
      } else if (is_int($k) && null === $v) { // skip NULL
      } else if ('f_' === substr($k, 0, 2) && is_string($v) && strlen($k) > 3) {
        $fieldName = substr($k, 2);
        $fieldType = $this->_acl->getXsdFieldType($fieldName);
        list($comparisonSign, $value) = $this->parseQueryValue($fieldName, $v, $fieldType);
        DBG::log([ $fieldName, $comparisonSign, $value, $fieldType ], 'array', "parseQueryValue");
        $this->_query->where([$fieldName, $comparisonSign, $value]);
      } else if ('sf_' === substr($k, 0, 3) && is_string($v) && strlen($k) > 4) {
        switch (substr($k, 3)) {
          case 'Msgs': $this->_query->where($this->parseSpecialFilterMsgs($v)); break;
          case 'Problemy': $this->_query->where($this->parseSpecialFilterProblemy($v)); break;
          case 'Status': $this->_query->where($this->parseSpecialFilterStatus($v)); break;
          case 'Spotkania': $this->_query->where($this->parseSpecialFilterSpotkania($v)); break;
          case 'Access': break; // SKIP - used below
          default: throw new Exception("Not Implemented special filter '".substr($k, 3)."'");
        }
      } else if ('ogc:Filter' === $k) {
        $this->_query->where($this->parseOgcFilter($v));
      } else if ('primaryKey' === $k) {
        $fieldName = $this->_acl->getPrimaryKeyField();
        $fieldType = $this->_acl->getXsdFieldType($fieldName);
        list($comparisonSign, $value) = $this->parseQueryValue($fieldName, $v, $fieldType);
        DBG::log([ $fieldName, $comparisonSign, $value, $fieldType ], 'array', "parseQueryValue");
        $this->_query->where([$fieldName, $comparisonSign, $value]);
      } else if ('limit' === $k) {
      } else if ('limitstart' === $k) {
      } else if ('order_by' === $k) {
      } else if ('order_dir' === $k) {
      } else {
        throw new Exception("Not Implemented param '{$k}' = '{$v}'");
      }
    }

    // sf_Access: if 'SHOW' then show all rows, but data with ***
    if ('SHOW' !== V::get('sf_Access', '', $this->_params)) $this->_query->where($this->parseSpecialFilterAccess());

    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" ]) // TODO: fetchTotal() ? a// TODO: add AclQueryBuilder::fetchTotal()
      ->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->getItems($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');

    DBG::log(['params' => $this->_params, 'sortBy' => $sortBy, 'limit' => $limit, 'offset' => $offset], 'array', '$this->_params');
    return $this->getQuery()
      ->select([
        'rawSelect' => ($this->_acl instanceof TableAcl)
          ? $this->_acl->getDataSource()->_getSqlCols()
          : '*'
      ])
      ->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); }

}