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' => 'addItem($itemTodo)', 'log' => $itemTodo]);
		if (empty($itemTodo['kwota'])) throw new Exception("Nie podano kwoty");
		if (empty($itemTodo['workerLogin'])) throw new Exception("Nie podano pracownika");

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

		$sqlItem['ID_USERS2'] = $id;
		DB::getPDO()->insert('ZALICZKA_WNIOSEK_HIST', $sqlItem);
		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]);
		$sqlItemTodo['A_RECORD_UPDATE_AUTHOR'] = User::getLogin();
		$sqlItemTodo['A_RECORD_UPDATE_DATE'] = "NOW()";
		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); }
	}

}