se/SE/se-lib/Schema/DefaultDb/zaliczka_wniosek/ZaliczkaWniosekStorageAcl.php

<?php

Lib::loadClass('Core_AclSimpleSchemaBase');// extends Core_AclBase
Lib::loadClass('Core_AclHelper');
Lib::loadClass('DBG');

class Schema_DefaultDb_zaliczka_wniosek_ZaliczkaWniosekStorageAcl extends Core_AclSimpleSchemaBase {

  public $_simpleSchema = [
    'root' => [
      '@namespace' => 'default_db/ZALICZKA_WNIOSEK/ZaliczkaWniosek',// Api_WfsNs::getBaseWfsUri() . '/default_db/Zaliczka'
      '@primaryKey' => 'id',
      'id' => [ '@type' => 'xsd:integer', '@alias' => 'ID' ],
      'created' => [ '@type' => 'xsd:date', '@alias' => 'A_RECORD_CREATE_DATE' ],
      'kwota' => [ '@type' => 'xsd:decimal', '@totalDigits' => 16, '@fractionDigits' => 2, '@alias' => 'KWOTA' ],
      'uwagi' => [ '@type' => 'xsd:string', '@alias' => 'UWAGI' ],
      'status' => [ '@type' => 'p5:enum', '@alias' => 'A_STATUS', '@aliasMap' => [
        'WAITING' => "Oczekuje zatwierdzenia",
        'NORMAL' => "Zatwierdzony",
        'OFF_HARD' => "Odrzucony",
        'DELETED' => "Anulowany",
      ], '@default' => 'WAITING' ],
      'workerLogin' => [ '@type' => 'xsd:string', '@alias' => 'L_APPOITMENT_USER' ],
      'approvedBy' => [ '@type' => 'xsd:string', '@alias' => 'APPROVED_BY' ],
    ],
  ];

  public function getField($idField) {
    $idField = (int)$idField;
    $fieldName = DB::getPDO()->fetchValue("
      select z.`DESC`
      from CRM_LISTA_ZASOBOW z
      where z.ID = $idField
    ");
    if ('id' == $fieldName) {
      return [
        'name' => 'id',
        'type' => 'int(11)',
        'perms' => 'R'
      ];
    }
  }

  public function addItem($itemTodo) {
    DBG::log(['msg' => '$itemTodo', 'log' => $itemTodo]);
    if (empty($itemTodo['kwota'])) throw new Exception("Nie podano kwoty");
    if (empty($itemTodo['workerLogin'])) throw new Exception("Nie podano pracownika");

    $id = DB::getPDO()->insert('ZALICZKA_WNIOSEK', [
      'A_RECORD_CREATE_AUTHOR' => User::getLogin(),
      'A_RECORD_CREATE_DATE' => 'NOW()',
      'L_APPOITMENT_USER' => $itemTodo['workerLogin'],
      'KWOTA' => $itemTodo['kwota'],
      'UWAGI' => $itemTodo['uwagi']
    ]);
    DBG::log(['msg' => '$id', 'log' => $id]);
    if (!$id) throw new Exception("Wystąpiły błędy podczas dodawania Zaliczki do bazy danych");

    DB::getPDO()->insert('ZALICZKA_WNIOSEK_HIST', [
      'ID_USERS2' => $id,
      'A_RECORD_CREATE_AUTHOR' => User::getLogin(),
      'A_RECORD_CREATE_DATE' => 'NOW()',
      'L_APPOITMENT_USER' => $itemTodo['workerLogin'],
      'KWOTA' => $itemTodo['kwota'],
      'UWAGI' => $itemTodo['uwagi']
    ]);
    return $id;
  }

  public function updateItem($itemPatch) {
    DBG::log(['msg' => '$itemPatch', $itemPatch]);
    $pkField = $this->getPrimaryKeyField();
    if (!array_key_exists($pkField, $itemPatch)) throw new Exception("Missing primary key");
    $pk = (int)$itemPatch[$pkField];
    if ($pk <= 0) throw new Exception("Wrong primary key format");

    $sqlItemTodo = [];
    if (array_key_exists('status', $itemPatch)) {
      $sqlItemTodo['A_STATUS'] = $this->enumValueToSql('status', $itemPatch['status']);
    }
    if (array_key_exists('approvedBy', $itemPatch)) {
      $sqlItemTodo['APPROVED_BY'] = $itemPatch['approvedBy'];
    }
    if (empty($sqlItemTodo)) throw new Exception("Nothing to change");

    unset($sqlItemTodo[$pkField]);
    DB::getPDO()->update($this->getRootTableName(), $pkField, $pk, $sqlItemTodo);
    $sqlItemTodo['ID_USERS2'] = $pk;
    DB::getPDO()->insert($this->getRootTableName() . "_HIST", $sqlItemTodo);
  }

  public function getItem($primaryKey, $params = []) {
    $sqlWhere = $this->_parseSqlWhere([
      '@primaryKey' => $primaryKey
    ]);
    $row = DB::getPDO()->fetchFirst("
      select t.*
        , (select u.ID from ADMIN_USERS u where u.ADM_ACCOUNT = t.L_APPOITMENT_USER limit 1) as ID_USER
      from ZALICZKA_WNIOSEK t
      {$sqlWhere}
    ");
    return [
      'id' => $row['ID'],
      'created' => $row['A_RECORD_CREATE_DATE'],
      'status' => $this->enumValueFromSql('status', $row['A_STATUS']),
      'kwota' => $row['KWOTA'],
      'uwagi' => $row['UWAGI'],
      'workerLogin' => $row['L_APPOITMENT_USER'],
      'workerID' => $row['ID_USER'],
    ];
  }

  public function getTotal($params = []) {
    $sqlWhere = $this->_parseSqlWhere($params);
    return DB::getPDO()->fetchValue("
      select count(*) as cnt
      from ZALICZKA_WNIOSEK t
      {$sqlWhere}
    ");
  }

  public function getItems($params = []) {
    DBG::log(['msg'=>'getItems $params', '$params' => $params]);
    $sqlWhere = $this->_parseSqlWhere($params);
    $sqlOrderBy = "";
    if (!empty($params['sortBy'])) {
      $sqlOrderBy = $this->parseSqlSortBy($params['sortBy'], 't');
    } else if (array_key_exists('order_by', $params) && array_key_exists('order_dir', $params)) {
      $sqlOrderBy = $this->parseSqlSortBy("{$params['order_by']} {$params['order_dir']}", 't');
    }
    return array_map(function ($row) {
      return [
        'id' => $row['ID'],
        'created' => $row['A_RECORD_CREATE_DATE'],
        'status' => $this->enumValueFromSql('status', $row['A_STATUS']),
        'kwota' => $row['KWOTA'],
        'uwagi' => $row['UWAGI'],
        'workerLogin' => $row['L_APPOITMENT_USER'],
        'approvedBy' => $row['APPROVED_BY'],
      ];
    }, DB::getPDO()->fetchAll("
      select t.*
      from ZALICZKA_WNIOSEK t
      {$sqlWhere}
      {$sqlOrderBy}
    "));
  }

  public function _parseSqlWhere($params = []) {
    DBG::log(['msg'=>'_parseSqlWhere $params', '$params' => $params]);
    $sqlWhere = [];
    if (!empty($params['f_workerLogin'])) {
      $sqlUserLogin = DB::getPDO()->quote($params['f_workerLogin'], PDO::PARAM_STR);
      $sqlWhere[] = "t.`L_APPOITMENT_USER` = {$sqlUserLogin}";
    }
    if (!empty($params['f_id'])) {
      $sqlWhere[] = "t.`ID` = " . DB::getPDO()->quote($params['f_id'], PDO::PARAM_STR);
    }
    if (!empty($params['@primaryKey'])) {
      $sqlWhere[] = "t.`ID` = " . DB::getPDO()->quote($params['@primaryKey'], PDO::PARAM_STR);
    }
    return (!empty($sqlWhere)) ? " where " . implode("\n and ", $sqlWhere) : "";
  }

  public function enumValueFromSql($fieldName, $sqlValue) {
    DBG::log(['msg'=>'enumValueFromSql $type', '$type' => $this->getXsdFieldType($fieldName)]);
    if ('p5:enum' == $this->getXsdFieldType($fieldName)) {
      $aliasMap = V::get('@aliasMap', '', $this->_simpleSchema['root'][$fieldName]);
      if (!empty($aliasMap) && is_array($aliasMap)) {
        if (array_key_exists($sqlValue, $aliasMap)) {
          return $aliasMap[$sqlValue];
        }
      }
    }
    return $sqlValue;
  }
  public function enumValueToSql($fieldName, $value) {
    DBG::log(['msg'=>'enumValueToSql $type', '$type' => $this->getXsdFieldType($fieldName)]);
    if ('p5:enum' == $this->getXsdFieldType($fieldName)) {
      $aliasMap = V::get('@aliasMap', '', $this->_simpleSchema['root'][$fieldName]);
      if (empty($aliasMap) || !is_array($aliasMap)) throw new Exception("Schema error - missing @aliasMap for field '{$fieldName}'");
      $aliasMap = array_flip($aliasMap);
      if (!array_key_exists($value, $aliasMap)) throw new Exception("Schema error - value not exists in alias map");
      return $aliasMap[$value];
    }
    throw new Exception("Schema error - field is not enum '{$fieldName}'");
  }

  /**
  * @param sortBy ID A,COL_X D,COL_Y A,...
  * @return sql string "order by ..."
  */
  public function parseSqlSortBy($sortBy, $sqlTablePrefix = 't') {
    $list = $this->parseSortBy($sortBy);
    DBG::log(['msg'=>'parseSqlSortBy $list', '$list' => $list]);
    if (empty($list)) return "";
    return "order by " . implode(", ", array_map(function ($orderItem) use ($sqlTablePrefix) {
      list($fieldName, $order) = $orderItem;
      $sqlFieldName = $this->getSqlFieldName($fieldName);
      return "{$sqlTablePrefix}.`{$sqlFieldName}` {$order}";
    }, $list));
  }

  /**
  * @param sortBy ID A,COL_X D,COL_Y A,...
  * @return array with field names and sort order: [ fieldName => order ( ASC | DESC ) ]
  * TODO: require convert to sql names / validate if field exists or is allowed to sort
  */
  public function parseSortBy($sortBy) {
    if (!$sortBy) return [];
    return array_filter(
      array_map(
        function ($sortPart) {
          DBG::log(['msg'=>'parseSortBy $sortPart', '$sortPart' => $sortPart]);
          if (!$sortPart) return null;
          list($fieldName, $order, $error) = explode(' ', trim($sortPart));
          DBG::log("parseSortBy \$fieldName='{$fieldName}', \$order='{$order}', \$error='{$error}'");
          if ($error) throw new Exception("sortBy parse error '{$sortPart}'");
          if (!array_key_exists($fieldName, $this->_simpleSchema['root'])) throw new Exception("Parse sort by error - field not exists! '{$fieldName}'");
          $order = strtoupper($order);
          if ('A' == $order || 'ASC' == $order) {
            $order = 'ASC';
  				} else if ('D' == $order || 'DESC' == $order) {
  					$order = 'DESC';
  				} else throw new Exception("sortBy parse error - unknown sort order '{$order}' #" . __LINE__);
          return [ $fieldName, $order ];
        }, explode(',', $sortBy)
      )
      , function ($orderItem) {
        return !empty($orderItem);
      }
    );
  }

  public function reinstall() {
    // TODO: mv to Core_AclSimpleSchemaBase and reainstall by _simpleSchema - read current data from `information_schema`
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `ZALICZKA_WNIOSEK` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`A_RECORD_CREATE_DATE` datetime  DEFAULT NULL,
				`A_RECORD_CREATE_AUTHOR` varchar(20)  DEFAULT NULL,
				`A_RECORD_UPDATE_DATE` datetime  DEFAULT NULL,
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT '',
				`A_STATUS` enum('WAITING','NORMAL','MONITOR','WARNING','OFF_SOFT','OFF_HARD','DELETED') DEFAULT 'WAITING',
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT '',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT '',
				`L_APPOITMENT_USER` varchar(100) NOT NULL DEFAULT '',
				`KWOTA` decimal(16,2) NOT NULL DEFAULT 0,
				`UWAGI` varchar(255) NOT NULL DEFAULT '',
				`APPROVED_BY` varchar(255) NOT NULL DEFAULT '',
				PRIMARY KEY (`ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		");
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `ZALICZKA_WNIOSEK_HIST` (
				`ID` int(11) NOT NULL AUTO_INCREMENT,
				`ID_USERS2` int(11) NOT NULL,
				`A_RECORD_CREATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_CREATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_DATE` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_RECORD_UPDATE_AUTHOR` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_STATUS` varchar(20) NOT NULL DEFAULT 'N/S;',
				`A_ADM_COMPANY` varchar(100) NOT NULL DEFAULT 'N/S;',
				`A_CLASSIFIED` varchar(100) NOT NULL DEFAULT 'N/S;',
				`L_APPOITMENT_USER` varchar(100) NOT NULL DEFAULT 'N/S;',
				`KWOTA` varchar(16) NOT NULL DEFAULT 'N/S;',
        `UWAGI` varchar(255) NOT NULL DEFAULT 'N/S;',
        `APPROVED_BY` varchar(255) NOT NULL DEFAULT 'N/S;',
				PRIMARY KEY (`ID`),
			  KEY `ID_USERS2` (`ID_USERS2`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
		");
    try { DB::getPDO()->execSql(" ALTER TABLE `ZALICZKA_WNIOSEK` ADD `APPROVED_BY` varchar(255) NOT NULL DEFAULT '' ");
    } catch (Exception $e) { DBG::log($e); }
    try { DB::getPDO()->execSql(" ALTER TABLE `ZALICZKA_WNIOSEK_HIST` ADD `APPROVED_BY` varchar(255) NOT NULL DEFAULT 'N/S;' ");
    } catch (Exception $e) { DBG::log($e); }
  }

}