se/SE/se-lib/Schema/PrzypomnijStorageAcl.php

<?php

Lib::loadClass('Core_AclSimpleSchemaBase');
Lib::loadClass('ParseOgcFilter');
Lib::loadClass('Router');
Lib::loadClass('SchemaVersionUpgrade');

class Schema_PrzypomnijStorageAcl extends Core_AclSimpleSchemaBase {

	public $_simpleSchema = [
		'root' => [
			'@namespace' => 'default_objects/Przypomnij',
			'@primaryKey' => 'feature_id',
			// col: Typ rekordu / ID
			'feature_id' => [ '@type' => 'xsd:string' ], // eg.: "PROBLEMS.123"
			'namespace' => [ '@type' => 'xsd:string' ], // eg.: "default_db/PROBLEMS"
			'primaryKey' => [ '@type' => 'xsd:integer' ], // eg.: 123
			'A_STATUS' => [ '@type' => 'xsd:string' ],
			'featureType' => [ '@type' => 'xsd:string' ], // eg.: Zasob.TYPE, Projekt.M_DIST_TYPE, etc.
			'featureDesc' => [ '@type' => 'xsd:string' ], // eg.: Zasob.DESC, Projekt.M_DIST_DESC, etc.

			'L_APPOITMENT_USER' => [ '@type' => 'xsd:string' ], // Osoba odpowiedzialna
			'A_ADM_COMPANY' =>  [ '@type' => 'xsd:string' ],
			'A_CLASSIFIED' =>  [ '@type' => 'xsd:string' ],

			// col: Termin wykonania / Opis działań do wykonania
			'L_APPOITMENT_DATE' => [ '@type' => 'xsd:dateTime' ], // Termin wykonania
			'L_APPOITMENT_INFO' => [ '@type' => 'xsd:string' ], // Opis działań do wykonania
			// Firma powiąz. / adres / opis-temat
			// Lokalizacja

			'PROJECT__ID' => [ '@type' => "xsd:int" ],
			'PROJECT__L_APPOITMENT_USER' => [ '@type' => "xsd:string" ],
			'USER__IS_ACTIVE' => [ '@type' => "xsd:int" ],

			// 'idZasob' => [ '@type' => 'xsd:integer' ],
			// 'idDatabase' => [ '@type' => 'xsd:integer' ],
			// '_rootTableName' => [ '@type' => 'xsd:string' ],
			// '_type' => [ '@type' => 'xsd:string' ],
			// 'hasStruct' => [ '@type' => 'xsd:integer' ], // 0 - removed, old, 1 - has config, structure
			// 'isStructInstalled' => [ '@type' => 'xsd:integer' ], // installed
			// 'isObjectActive' => [ '@type' => 'xsd:integer' ], // (0,1) - admin settings with restrictions: (hasStruct, isStructInstalled, all fields installed and with idZasob)
			// 'description' => [ '@type' => 'xsd:string' ],
			// 'name' => [ '@type' => 'p5:string' ],
			// 'typeName' => [ '@type' => 'p5:string' ],
			// 'nsPrefix' => [ '@type' => 'p5:string' ],
			// 'reinstallLink' => [ '@type' => 'p5:www_link' ],
			// 'instanceTableSource' => [ '@type' => 'xsd:string' ], // enum('table', 'view') default 'view'
			// 'A_RECORD_CREATE_AUTHOR' => [ '@type' => 'xsd:string' , '@label' => 'autor' ],
			// 'A_RECORD_CREATE_DATE' => [ '@type' => 'xsd:date' , '@label' => 'utworzono' ],
			// 'A_RECORD_UPDATE_AUTHOR' => [ '@type' => 'xsd:string' , '@label' => 'zaktualizował' ],
			// 'A_RECORD_UPDATE_DATE' => [ '@type' => 'xsd:date', '@label' => 'zaktualizowano' ],
		]
	];
	// public $_rootTableName = 'CRM_LISTA_ZASOBOW';
	public $_rootTableName = '_PRZYPOMNIJ_ITEMS';
	static function _createItemsTable() {
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `_PRZYPOMNIJ_ITEMS` (
				`L_APPOITMENT_USER` varchar(255) NOT NULL default '',
				`A_ADM_COMPANY` varchar(255) NOT NULL default '',
				`A_CLASSIFIED` varchar(255) NOT NULL default '',
				`feature_id` varchar(255) NOT NULL,
				`namespace` varchar(255) NOT NULL default '',
				`primaryKey` varchar(255) NOT NULL default '',
				`A_STATUS` varchar(16) NOT NULL default '',
				`featureType` varchar(32) NOT NULL default '',
				`featureDesc` varchar(255) NOT NULL default '',
				`L_APPOITMENT_DATE` dateTime DEFAULT NULL,
				`L_APPOITMENT_INFO` varchar(255) NOT NULL default '',
				`PROJECT__ID` int(11) NOT NULL DEFAULT 0,
				`PROJECT__L_APPOITMENT_USER` varchar(255) NOT NULL default '',
				`USER__IS_ACTIVE` tinyint(1) NOT NULL DEFAULT 0,

				PRIMARY KEY (`feature_id`),
				KEY `namespace` (`namespace`),
				KEY `L_APPOITMENT_USER` (`L_APPOITMENT_USER`),
				KEY `A_ADM_COMPANY` (`A_ADM_COMPANY`),
				KEY `A_CLASSIFIED` (`A_CLASSIFIED`)

				-- `ID_PROJECT` int(11) NOT NULL,
				-- `_l_app_user` varchar(40) NOT NULL DEFAULT '',
				-- `P_ID` varchar(20) NOT NULL DEFAULT '0',
				-- `A_RECORD_CREATE_DATE` datetime NOT NULL,
				-- `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL DEFAULT '',
				-- `A_RECORD_UPDATE_DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				-- `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL DEFAULT '',
				-- `L_APPOITMENT_DATE` varchar(30) NOT NULL DEFAULT '',
				-- `L_APPOITMENT_USER` varchar(40) NOT NULL DEFAULT '',
				-- `L_APPOITMENT_INFO` varchar(200) NOT NULL DEFAULT '',
				-- `L_CALENDAR` enum('NO','YES') NOT NULL DEFAULT 'NO',
				-- `A_STATUS` enum('WAITING','NORMAL','MONITOR','WARNING','OFF_SOFT','OFF_HARD','DELETED') NOT NULL DEFAULT 'WAITING',
				-- `M_DIST_DATE` date NOT NULL DEFAULT '0000-00-00',
				-- `M_DIST_TYPE` varchar(64) NOT NULL DEFAULT 'INNE',
				-- `M_DIST_DESC` varchar(255) NOT NULL DEFAULT '',
				-- `M_DISTRIBUTOR` varchar(200) NOT NULL DEFAULT '',
				-- KEY `ID_PROJECT` (`ID_PROJECT`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
		");
	}

	function __construct() {
		parent::__construct($this->_simpleSchema);
		$this->idUser = User::getID(); // default - current user
	}

	function onBeforeFetchData() {
		self::updateCacheIfNeeded();
	}

	static function updateCacheIfNeeded() {
		static $_checked = false;
		if ($_checked) return;

		$confKeyLastUpdateDate = 'Schema_PrzypomnijStorageAcl::lastUpdateDate';
		$lastUpdateDate = DB::getPDO()->fetchValue(" select CONF_VAL from CRM_CONFIG where CONF_KEY = :conf_key ", [ ':conf_key'  => $confKeyLastUpdateDate ]);
		$tablesList = [];
		$tablesList[] = '_PRZYPOMNIJ_ITEMS'; // to check if tables exists
		$tablesList[] = '_PRZYPOMNIJ_PROJECT_TREE'; // to check if tables exists
		$tablesList[] = '_PRZYPOMNIJ_ACTIVE_USERS'; // to check if tables exists
		$tablesList[] = 'IN7_MK_BAZA_DYSTRYBUCJI';
		$tablesList[] = 'IN7_DZIENNIK_KORESP';
		$tablesList[] = 'CRM_PROCES';
		$tablesList[] = 'PROBLEMS';
		$tablesList[] = 'CRM_LISTA_ZASOBOW';
		$tablesList[] = 'MK_Rewiry';
		// $tablesList[] = 'BUILDINGS';
		// $tablesList[] = 'QUALITY_NOTICES';
		// $tablesList[] = 'BADANIA_W_TERENIE';
		$sqlTableList = implode(", ", array_map(function ($table) { return "'{$table}'"; }, $tablesList));
		$tablesUpdateDates = DB::getPDO()->fetchValuesListByKey("
			select IF( UPDATE_TIME > :last_update_date, 1, 0 ) as TO_UPDATE
				, TABLE_NAME
				, UPDATE_TIME
			from information_schema.tables
			where TABLE_SCHEMA = :db_name
				and TABLE_NAME in({$sqlTableList})
		", $key = 'TABLE_NAME', [
			':db_name' => DB::getPDO()->getDatabaseName(),
			':last_update_date' => $lastUpdateDate,
		]);
		DBG::log($tablesUpdateDates, 'array', "DBG::Przypomnij: \$tablesUpdateDates 1");
		if (!array_key_exists('_PRZYPOMNIJ_ITEMS', $tablesUpdateDates)) {
			self::_createItemsTable();
			$tablesUpdateDates = array_map(function ($value) { return 1; }, $tablesUpdateDates);
		}
		if (!array_key_exists('_PRZYPOMNIJ_PROJECT_TREE', $tablesUpdateDates)
			|| $tablesUpdateDates['IN7_MK_BAZA_DYSTRYBUCJI']
		) {
			if (!array_key_exists('_PRZYPOMNIJ_PROJECT_TREE', $tablesUpdateDates)) {
				self::_createProjectsTreeTable();
			}
			if (self::_isProjectsOwnerChanged()) {
				self::_updateProjectsTreeTable();
			}
			// $tablesUpdateDates = array_map(function ($value) { return 1; }, $tablesUpdateDates); // moved to mass update
		}
		if (!array_key_exists('_PRZYPOMNIJ_ACTIVE_USERS', $tablesUpdateDates)
			|| $tablesUpdateDates['ADMIN_USERS']
		) {
			if (!array_key_exists('_PRZYPOMNIJ_ACTIVE_USERS', $tablesUpdateDates)) {
				self::_createUsersTable();
			}
			if (self::_isUsersChanged()) {
				self::_updateUsersStatusTable();
			}
			// $tablesUpdateDates = array_map(function ($value) { return 1; }, $tablesUpdateDates); // moved to mass update
		}
		DBG::log($tablesUpdateDates, 'array', "DBG::Przypomnij: \$tablesUpdateDates 2");

		$tablesToUpdate = array_keys(
			array_filter($tablesUpdateDates, function ($value) { return $value; })
		);
		$tablesToUpdate = array_filter($tablesToUpdate, function ($tableName) { return ( '_PRZYPOMNIJ_' !== substr($tableName, 0, strlen('_PRZYPOMNIJ_')) ); });
		DBG::log($tablesToUpdate, 'array', "DBG::Przypomnij: \$tablesToUpdate");
		foreach ($tablesToUpdate as $tableName) self::_updateForTable($tableName);

		if (!array_key_exists('_PRZYPOMNIJ_PROJECT_TREE', $tablesUpdateDates)
			|| $tablesUpdateDates['IN7_MK_BAZA_DYSTRYBUCJI']
		) {
			self::_updateLAppUserByProjectsTree();
		}
		if (!array_key_exists('_PRZYPOMNIJ_ACTIVE_USERS', $tablesUpdateDates)
			|| $tablesUpdateDates['ADMIN_USERS']
			|| !empty($tablesToUpdate) // update if anything updates
		) {
			self::_updateUserStatus();
		}

		DB::getPDO()->insertOrUpdate('CRM_CONFIG', [
			'CONF_KEY' => $confKeyLastUpdateDate,
			'@insert' => [
				'CONF_VAL' => "NOW()",
			],
			'@update' => [
				'CONF_VAL' => "NOW()",
			]
		]);
		$_checked = true;
	}

	static function _updateForTable($tableName) {
		$namespace = "default_db/{$tableName}";
		if ('CRM_LISTA_ZASOBOW' === $tableName) { // TODO: getUpdateConfigForTable($tableName);
			DB::getPDO()->execSql(" DELETE from `_PRZYPOMNIJ_ITEMS` where `namespace` = :namespace ", [ ':namespace' => $namespace ]);
			DB::getPDO()->execSql("
				insert into `_PRZYPOMNIJ_ITEMS` (
					`feature_id`, `namespace`, `primaryKey`
					, `A_STATUS`, `featureType`, `featureDesc`
					, `L_APPOITMENT_USER`, `A_ADM_COMPANY`, `A_CLASSIFIED`
					, `L_APPOITMENT_DATE`, `L_APPOITMENT_INFO`
				)
				select concat( :table_name , '.', t.ID) as `feature_id`, :namespace as `namespace`, t.ID as `primaryKey`
					, t.`A_STATUS`
					, t.`TYPE` as `featureType`
					, t.`DESC` as `featureDesc`
					, t.`L_APPOITMENT_USER`, t.`A_ADM_COMPANY`, t.`A_CLASSIFIED`
					, t.`L_APPOITMENT_DATE`, t.`L_APPOITMENT_INFO`
				from `CRM_LISTA_ZASOBOW` as t
				where t.`A_STATUS` in ('NORMAL', 'WAITING')
					and t.`L_APPOITMENT_DATE` != ''
					and t.`L_APPOITMENT_USER` != ''
			", [
				':namespace' => $namespace,
				':table_name' => $tableName,
			]);
		}
		else if ('CRM_PROCES' === $tableName)  { // TODO: getUpdateConfigForTable($tableName);
			DB::getPDO()->execSql(" DELETE from `_PRZYPOMNIJ_ITEMS` where `namespace` = :namespace ", [ ':namespace' => $namespace ]);
			DB::getPDO()->execSql("
				insert into `_PRZYPOMNIJ_ITEMS` (
					`feature_id`, `namespace`, `primaryKey`
					, `A_STATUS`, `featureType`, `featureDesc`
					, `L_APPOITMENT_USER`, `A_ADM_COMPANY`, `A_CLASSIFIED`
					, `L_APPOITMENT_DATE`, `L_APPOITMENT_INFO`
				)
				select concat( :table_name , '.', t.ID) as `feature_id`, :namespace as `namespace`, t.ID as `primaryKey`
					, t.`A_STATUS`
					, t.`TYPE` as `featureType`
					, t.`DESC` as `featureDesc`
					, t.`L_APPOITMENT_USER`, t.`A_ADM_COMPANY`, t.`A_CLASSIFIED`
					, t.`L_APPOITMENT_DATE`, t.`L_APPOITMENT_INFO`
				from `CRM_PROCES` as t
				where t.`A_STATUS` in ('NORMAL', 'WAITING')
					and t.`TYPE` = 'PROCES_INIT'
					and t.`L_APPOITMENT_DATE` != ''
					and t.`L_APPOITMENT_USER` != ''
			", [
				':namespace' => $namespace,
				':table_name' => $tableName,
			]);
		}
		else if ('IN7_DZIENNIK_KORESP' === $tableName)  { // TODO: getUpdateConfigForTable($tableName);
			DB::getPDO()->execSql(" DELETE from `_PRZYPOMNIJ_ITEMS` where `namespace` = :namespace ", [ ':namespace' => $namespace ]);
			DB::getPDO()->execSql("
				insert into `_PRZYPOMNIJ_ITEMS` (
					`feature_id`, `namespace`, `primaryKey`
					, `A_STATUS`, `featureType`, `featureDesc`
					, `L_APPOITMENT_USER`, `A_ADM_COMPANY`, `A_CLASSIFIED`
					, `L_APPOITMENT_DATE`, `L_APPOITMENT_INFO`
					, `PROJECT__ID`
				)
				select concat( :table_name , '.', t.ID) as `feature_id`, :namespace as `namespace`, t.ID as `primaryKey`
					, t.`A_STATUS`
					, concat(t.`K_TYP_KORESP`, '-', t.`K_TYP_RODZAJ`) as `featureType`
					, concat('<strong>', t.`K_OD_KOGO`, '</strong><br><em>', t.`OD_KOGO_ADRES`, '</em><br>', t.`K_ZAWARTOS`) as `featureDesc`
					, t.`L_APPOITMENT_USER`, t.`A_ADM_COMPANY`, t.`A_CLASSIFIED`
					, t.`L_APPOITMENT_DATE`, t.`L_APPOITMENT_INFO`
					, t.`ID_PROJECT` as PROJECT__ID
				from `IN7_DZIENNIK_KORESP` as t
				where t.`A_STATUS` not in ('OFF_HARD', 'DELETED')
			", [
				':namespace' => $namespace,
				':table_name' => $tableName,
			]);
		}
		else if ('IN7_MK_BAZA_DYSTRYBUCJI' === $tableName)  { // TODO: getUpdateConfigForTable($tableName);
			// TODO: 'M_DIST_DESC' => htmlspecialchars($row['M_DIST_DESC']), // TODO: fix bug in html a href inside M_DIST_DES
			DB::getPDO()->execSql(" DELETE from `_PRZYPOMNIJ_ITEMS` where `namespace` = :namespace ", [ ':namespace' => $namespace ]);
			DB::getPDO()->execSql("
				insert into `_PRZYPOMNIJ_ITEMS` (
					`feature_id`, `namespace`, `primaryKey`
					, `A_STATUS`, `featureType`, `featureDesc`
					, `L_APPOITMENT_USER`, `A_ADM_COMPANY`, `A_CLASSIFIED`
					, `L_APPOITMENT_DATE`, `L_APPOITMENT_INFO`
					, `PROJECT__ID`
				)
				select concat( :table_name , '.', t.ID) as `feature_id`, :namespace as `namespace`, t.ID as `primaryKey`
					, t.`A_STATUS`
					, t.`M_DIST_TYPE` as `featureType`
					, t.`M_DIST_DESC` as `featureDesc`
					, t.`L_APPOITMENT_USER`, t.`A_ADM_COMPANY`, t.`A_CLASSIFIED`
					, t.`L_APPOITMENT_DATE`, t.`L_APPOITMENT_INFO`
					, t.`P_ID` as PROJECT__ID
				from `IN7_MK_BAZA_DYSTRYBUCJI` as t
				where t.`A_STATUS` not in ('OFF_HARD', 'DELETED')
			", [
				':namespace' => $namespace,
				':table_name' => $tableName,
			]);
		}
		else if ('PROBLEMS' === $tableName)  { // TODO: getUpdateConfigForTable($tableName);
			// TODO: 'A_PROBLEM_DESC' => htmlspecialchars($row['A_PROBLEM_DESC']),
			DB::getPDO()->execSql(" DELETE from `_PRZYPOMNIJ_ITEMS` where `namespace` = :namespace ", [ ':namespace' => $namespace ]);
			DB::getPDO()->execSql("
				insert into `_PRZYPOMNIJ_ITEMS` (
					`feature_id`, `namespace`, `primaryKey`
					, `A_STATUS`, `featureType`, `featureDesc`
					, `L_APPOITMENT_USER`, `A_ADM_COMPANY`, `A_CLASSIFIED`
					, `L_APPOITMENT_DATE`, `L_APPOITMENT_INFO`
					, `PROJECT__ID`
				)
				select concat( :table_name , '.', t.ID) as `feature_id`, :namespace as `namespace`, t.ID as `primaryKey`
					, t.`A_STATUS`
					, '' as `featureType`
					, t.`A_PROBLEM_DESC` as `featureDesc`
					, t.`L_APPOITMENT_USER`, t.`A_ADM_COMPANY`, t.`A_CLASSIFIED`
					, t.`L_APPOITMENT_DATE`, t.`L_APPOITMENT_INFO`
					, t.`ID_PROJECT` as PROJECT__ID
				from `PROBLEMS` as t
				where t.`A_STATUS` not in ('OFF_HARD', 'DELETED')
					and t.`L_APPOITMENT_DATE` != ''
					and t.`L_APPOITMENT_USER` != ''
			", [
				':namespace' => $namespace,
				':table_name' => $tableName,
			]);
		}
		else if ('MK_Rewiry' === $tableName)  { // TODO: getUpdateConfigForTable($tableName);
			// TODO: 'A_PROBLEM_DESC' => htmlspecialchars($row['A_PROBLEM_DESC']),
			DB::getPDO()->execSql(" DELETE from `_PRZYPOMNIJ_ITEMS` where `namespace` = :namespace ", [ ':namespace' => $namespace ]);
			DB::getPDO()->execSql("
				insert into `_PRZYPOMNIJ_ITEMS` (
					`feature_id`, `namespace`, `primaryKey`
					, `A_STATUS`, `featureType`, `featureDesc`
					, `L_APPOITMENT_USER`, `A_ADM_COMPANY`, `A_CLASSIFIED`
					, `L_APPOITMENT_DATE`, `L_APPOITMENT_INFO`
					, `PROJECT__ID`
				)
				select concat( :table_name , '.', t.ID) as `feature_id`, :namespace as `namespace`, t.ID as `primaryKey`
					, t.`A_STATUS`
					, '' as `featureType`
					, t.`L_APPOITMENT_INFO` as `featureDesc`
					, t.`L_APPOITMENT_USER`, t.`A_ADM_COMPANY`, t.`A_CLASSIFIED`
					, t.`L_APPOITMENT_DATE`, t.`L_APPOITMENT_INFO`
					, t.`ID_PROJECT` as PROJECT__ID
				from `MK_Rewiry` as t
				where t.`A_STATUS` not in ('OFF_HARD', 'DELETED')
					and t.`L_APPOITMENT_DATE` != ''
					and t.`L_APPOITMENT_USER` != ''
			", [
				':namespace' => $namespace,
				':table_name' => $tableName,
			]);
		}
		else if ('_PRZYPOMNIJ_ITEMS' === $tableName) {}
		else if ('_PRZYPOMNIJ_PROJECT_TREE' === $tableName) {}
		else {
			throw new Exception("TODO: (default_objects/Przypomnij)::_updateForTable({$tableName})");
		}
	}

	static function _createUsersTable() {
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `_PRZYPOMNIJ_ACTIVE_USERS` (
				`ADM_ACCOUNT` varchar(20) NOT NULL,
				UNIQUE KEY `ADM_ACCOUNT` (`ADM_ACCOUNT`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
		");
	}
	static function _isUsersChanged() {
		return ( DB::getPDO()->fetchValue("
			select count(1) as total
				-- u.ID as u__ID, u.ADM_ACCOUNT as u__ADM_ACCOUNT, t.*
			from ADMIN_USERS u
				left join _PRZYPOMNIJ_ACTIVE_USERS t on ( t.ADM_ACCOUNT = u.ADM_ACCOUNT )
			where t.ADM_ACCOUNT is null
		") > 0);
	}
	static function _updateUsersStatusTable() {
		DB::getPDO()->execSql(" truncate table `_PRZYPOMNIJ_ACTIVE_USERS` ");
		DB::getPDO()->execSql("
			insert into `_PRZYPOMNIJ_ACTIVE_USERS` (`ADM_ACCOUNT`)
			select `ADM_ACCOUNT`
			from `ADMIN_USERS`
			where A_STATUS = 'NORMAL'
		");
	}
	static function _updateUserStatus() {
		DB::getPDO()->execSql("
			update `_PRZYPOMNIJ_ITEMS` as i
				left join `_PRZYPOMNIJ_ACTIVE_USERS` as u on ( u.ADM_ACCOUNT = i.L_APPOITMENT_USER )
			set i.USER__IS_ACTIVE = IF(u.ADM_ACCOUNT is null, 0, 1)
			where i.L_APPOITMENT_USER != ''
		");
	}

	static function _createProjectsTreeTable() {
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `_PRZYPOMNIJ_PROJECT_TREE` (
				`ID_PROJECT` int(11) NOT NULL,
				`P_ID` int(11) NOT NULL DEFAULT 0,
				`L_APPOITMENT_USER` varchar(40) NOT NULL DEFAULT '',
				`_l_app_user` varchar(40) NOT NULL DEFAULT '',
				UNIQUE KEY `ID_PROJECT` (`ID_PROJECT`),
				KEY `P_ID` (`P_ID`)
			) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
		");
	}
	static function _isProjectsOwnerChanged() {
		return ( DB::getPDO()->fetchValue("
			select count(1) as total
				-- p.ID as p__ID, p.L_APPOITMENT_USER as p__L_APPOITMENT_USER, t.*
			from IN7_MK_BAZA_DYSTRYBUCJI p
				left join _PRZYPOMNIJ_PROJECT_TREE t on ( t.ID_PROJECT = p.ID )
			where (
				t.L_APPOITMENT_USER is null
				or p.L_APPOITMENT_USER != t.L_APPOITMENT_USER
			)
		") > 0);
	}
	static function _updateProjectsTreeTable() {
		DB::getPDO()->execSql(" truncate table `_PRZYPOMNIJ_PROJECT_TREE` ");
		DB::getPDO()->execSql("
			insert into `_PRZYPOMNIJ_PROJECT_TREE` (`ID_PROJECT`,`P_ID`,`_l_app_user`,`L_APPOITMENT_USER`)
			select `ID`,`P_ID`,`L_APPOITMENT_USER`,`L_APPOITMENT_USER`
			from `IN7_MK_BAZA_DYSTRYBUCJI`
				where 1=1
		");

		// test recurse update l_app
		$sql = "
			select c._l_app_user
				, p.L_APPOITMENT_USER
				, p1.L_APPOITMENT_USER
				, p2.L_APPOITMENT_USER
				, p3.L_APPOITMENT_USER
				, p4.L_APPOITMENT_USER
				, p5.L_APPOITMENT_USER
				, p.ID
				, p1.ID
				, p2.ID
				, p3.ID
				, p4.ID
				, p5.ID
			from `_PRZYPOMNIJ_PROJECT_TREE` as c
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p on (p.ID=c.ID_PROJECT)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p1 on (p1.ID=p.P_ID)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p2 on (p2.ID=p1.P_ID)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p3 on (p3.ID=p2.P_ID)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p4 on (p4.ID=p3.P_ID)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p5 on (p5.ID=p4.P_ID)
			where c.`_l_app_user`=''
		";

		// for i to recurse limit
		$sql = "
			update `_PRZYPOMNIJ_PROJECT_TREE` as c
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p on (p.ID=c.ID_PROJECT)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p1 on (p1.ID=p.P_ID)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p2 on (p2.ID=p1.P_ID)
				left join `IN7_MK_BAZA_DYSTRYBUCJI` as p3 on (p3.ID=p2.P_ID)
			--	left join `IN7_MK_BAZA_DYSTRYBUCJI` as p4 on (p4.ID=p3.P_ID)
			--	left join `IN7_MK_BAZA_DYSTRYBUCJI` as p5 on (p5.ID=p4.P_ID)
			set
				c.`_l_app_user`=IF(p.`L_APPOITMENT_USER`!='', p.`L_APPOITMENT_USER`
					, IF(p1.`L_APPOITMENT_USER`!='', p1.`L_APPOITMENT_USER`
						, IF(p2.`L_APPOITMENT_USER`!='', p2.`L_APPOITMENT_USER`
							, IF(p3.`L_APPOITMENT_USER`!='', p3.`L_APPOITMENT_USER`
			--					, IF(p4.`L_APPOITMENT_USER`!='', p4.`L_APPOITMENT_USER`
			--						, IF(p5.`L_APPOITMENT_USER`!='', p5.`L_APPOITMENT_USER`
										, ''
			--						)
			--					)
							)
						)
					)
				)
			where c.`_l_app_user`=''
		";
		DB::getPDO()->execSql($sql);
	}
	static function _updateLAppUserByProjectsTree() {
		DB::getPDO()->execSql("
			update `_PRZYPOMNIJ_ITEMS` as i
				join `_PRZYPOMNIJ_PROJECT_TREE` as t on ( t.ID_PROJECT = i.PROJECT__ID )
			set i.PROJECT__L_APPOITMENT_USER = t._l_app_user
			where i.PROJECT__ID > 0
		");
	}

	// function getTotal($params = []) {
	// 	self::updateCacheIfNeeded();
	// 	$sqlWhere = $this->_parseWhere($params);
	// 	return DB::getPDO()->fetchValue("
	// 		select count(1) as cnt
	// 		from `{$this->_rootTableName}` t
	// 		{$sqlWhere}
	// 	");
	// }
	// function getItem($pk, $params = []) {
	// 	self::updateCacheIfNeeded();
	// 	if (!$pk) throw new Exception("Missing primary key '{$this->_namespace}'");
	// 	$pkField = $this->getSqlPrimaryKeyField();
	// 	if (!$pkField) throw new Exception("Missing primary key field defined in '{$this->_namespace}'");
	// 	$item = DB::getPDO()->fetchFirst("
	// 		select t.*
	// 		from `{$this->_rootTableName}` t
	// 		where t.`{$pkField}` = :pk
	// 	", [ ':pk' => $pk ]);
	// 	if (!$item) throw new Exception("Item '{$pk}' not exists - type '{$this->_namespace}'");
	// 	return $this->buildFeatureFromSqlRow($item, $params);
	// }
	//
	// function getItems($params = []) {
	// 	self::updateCacheIfNeeded();
	// 	$sqlWhere = $this->_parseWhere($params);
	//
	// 	$currSortCol = V::get('order_by', 'feature_id', $params);
	// 	$currSortFlip = strtolower(V::get('order_dir', 'desc', $params));
	// 	// TODO: validate $currSortCol is in field list
	// 	// TODO: validate $currSortFlip ('asc' or 'desc')
	// 	$xsdFields = $this->getXsdTypes();
	// 	if (!array_key_exists($currSortCol, $xsdFields)) throw new Exception("Field '{$currSortCol}' not found in '{$this->_namespace}'");
	// 	if (!in_array($currSortFlip, ['asc', 'desc'])) throw new Exception("Sort dir not allowed");
	// 	$sqlOrderBy = "order by t.`{$currSortCol}` {$currSortFlip}";
	//
	// 	$limit = V::get('limit', 0, $params, 'int');
	// 	$limit = ($limit < 0) ? 0 : $limit;
	// 	$offset = V::get('limitstart', 0, $params, 'int');
	// 	$offset = ($offset < 0) ? 0 : $offset;
	// 	$sqlLimit = ($limit > 0)
	// 	? "limit {$limit} offset {$offset}"
	// 	: '';
	//
	// 	Lib::loadClass('AclQueryItems');
	// 	$query = new AclQueryItems($this);
	// 	$query->setParams($params);
	// 	$query->setSource('default_db');
	// 	$query->setRawSql("
	// 		select t.*
	// 		from `{$this->_rootTableName}` t
	// 		{$sqlWhere}
	// 		{$sqlOrderBy}
	// 		{$sqlLimit}
	// 	");
	// 	return array_map(function ($item) use ($params) {
	// 		return $this->buildFeatureFromSqlRow($item, $params);
	// 	}, $query->fetchAll());
	// }

	function buildQuery($params = array()) {
		Lib::loadClass('AclQueryFeatures');
		return new AclQueryFeatures($this, $params, $legacyMode = false);
	}

	function _parseWhere($params = []) {
		$sqlWhere = [];
		DBG::log($params, 'array', "SystemObject::_parseWhere");
		// if (!empty($params['#refFrom'])) {
		// 	// '#refFrom' => [
		// 	//	 'namespace' => 'default_objects/SystemSource',
		// 	//	 'primaryKey' => $sourceItem['idZasob']
		// 	// ]
		// 	if (empty($params['#refFrom']['namespace'])) throw new Exception("Missing refFrom/namespace");
		// 	if (empty($params['#refFrom']['primaryKey'])) throw new Exception("Missing refFrom/primaryKey");
		//
		// 	if ('default_objects/SystemSource' != $params['#refFrom']['namespace']) throw new Exception("Unsupported refFrom/namespace '{$params['#refFrom']['namespace']}'");
		// 	$sqlWhere[] = "t.idDatabase = " . DB::getPDO()->quote($params['#refFrom']['primaryKey'], PDO::PARAM_INT);
		// }
		{
			$filterParams = [];
			$xsdFields = $this->getXsdTypes();
			foreach ($params as $k => $v) {
				if ('f_' != substr($k, 0, 2)) continue;
				$fieldName = substr($k, 2);
				if (!array_key_exists($fieldName, $xsdFields)) {
					// TODO: check query by xpath or use different param prefix
					throw new Exception("Field '{$fieldName}' not found in '{$this->_namespace}'");
				}
				if ('p5:www_link' == $xsdFields[$fieldName]) {
					continue;
				}
				$filterParams[$fieldName] = $v;
			}
		}
		if (!empty($filterParams)) {
			DBG::log($filterParams, 'array', "SystemObject::_parseWhere TODO \$filterParams");
			foreach ($filterParams as $fieldName => $value) {
				if (is_array($value)) {
					DBG::log($value, 'array', "TODO SystemObject::_parseWhere array value for \$filterParams[{$fieldName}]");
				} else if (is_scalar($value)) {
					if ('=' == substr($value, 0, 1)) {
						$sqlWhere[] = "t.{$fieldName} = " . DB::getPDO()->quote(substr($value, 1), PDO::PARAM_STR);
					} else {
						$sqlWhere[] = "t.{$fieldName} like " . DB::getPDO()->quote("%{$value}%", PDO::PARAM_STR);
					}
				} else {
					DBG::log($value, 'array', "BUG SystemObject::_parseWhere unknown type for \$filterParams[{$fieldName}]");
				}
			}
		}
		{ // acl fields
			$sqlWhere[] = "t.{$fieldName} like " . DB::getPDO()->quote("%{$value}%", PDO::PARAM_STR);
		}
		return (!empty($sqlWhere)) ? "where " . implode(" and ", $sqlWhere) : '';
	}

	function buildFeatureFromSqlRow($item, $params = []) {
		// DBG::log($params, 'array', "buildFeatureFromSqlRow... '{$item['namespace']}'");
		// $exNs = explode('/', $item['namespace']);
		// $item['name'] = array_pop($exNs);
		// $item['nsPrefix'] = implode('__x3A__', $exNs);
		// $item['typeName'] = implode('__x3A__', $exNs) . ':' . $item['name'];
		// $item['reinstallLink'] = Router::getRoute('Storage_AclReinstall')->getLink('', [ 'namespace' => $item['namespace'] ]);
		// if (!empty($params['propertyName'])) {
		// 	if (is_string($params['propertyName'])) $params['propertyName'] = explode(',', $params['propertyName']);
		// 	if (!is_array($params['propertyName'])) throw new Exception("Wrong param propertyName - expected array or string");
		// 	foreach ($params['propertyName'] as $fetchField) {
		// 		if ('*' == $fetchField) continue;
		// 		if ('field' == $fetchField) {
		// 			$item['field'] = SchemaFactory::loadDefaultObject('SystemObjectField')->getItems([
		// 				'__backRef' => [
		// 					'namespace' => 'default_objects/SystemObject',
		// 					'primaryKey' => $item['namespace']
		// 				],
		// 				'order_by' => 'sortPrio',
		// 				'order_dir' => 'asc',
		// 			]);
		// 		}
		// 	}
		// }
		return $item;
	}

	// function updateItem($itemPatch) { // @required [ 'namespace' => ... ] (primaryKey)
	// 	$pkField = $this->getPrimaryKeyField();
	// 	$pk = V::get($pkField, null, $itemPatch);
	// 	if (null === $pk) throw new Exception("BUG missing primary key field for '{$this->_namespace}' updateItem");
	// 	$this->clearGetItemCache($pk);
	// 	DBG::log(['updateItem $itemPatch', $itemPatch]);
	// 	unset($itemPatch[$pkField]);
	// 	if (empty($itemPatch)) return 0;
	// 	foreach ($itemPatch as $fieldName => $value) {
	// 		if ('isStructInstalled' == $fieldName) continue;
	// 		if ('isObjectActive' == $fieldName) continue;
	// 		if ('primaryKey' == $fieldName) continue;
	// 		if ('appInfo' == $fieldName) continue;
	// 		throw new Exception("Update field '{$fieldName}' not allowed for '{$this->_namespace}'");
	// 	}
	// 	return DB::getPDO()->update($this->_rootTableName, $pkField, $pk, $itemPatch);
	// }

	// TODO: to use in AclQueryFeatures
	function getDB() { return DB::getPDO()->getZasobId(); }
	function getLocalFieldList() {
		return array_filter(array_keys($this->_simpleSchema['root']), function ($fieldName) {
			if ( '@' == substr($fieldName, 0, 1)) return false;
			return true;
		});
	}
	function hasWriteGroupField() { return $this->_hasWriteGroupField; }
	function hasReadGroupField() { return $this->_hasReadGroupField; }
	function hasOwnerField() { return $this->_hasOwnerField; }
	function isLocalField($fieldName) {
		return in_array($fieldName, array_keys($this->_simpleSchema['root']));
	}

}