se/SE/se-lib/FeatureAttrContextSelected.php

<?php

// TODO: cron task - clear not used tables

class FeatureAttrContextSelected {

	static function getIdContext($context) {
		try {
			$idContext = self::_fetchIdContext($context);
		} catch (Exception $e) {
			DBG::log($e);
			if ('42S02' == $e->getCode()) { // SQLSTATE[42S02]: Base table or view not found: 1146 Table 'SES_USERS2.CRM_@SELECTED_CONTEXT' doesn't exist
				self::_createContextTable();
				try {
					$idContext = self::_fetchIdContext($context);
				} catch (Exception $e) {
					DBG::log($e);
					if ('42S02' == $e->getCode()) { // SQLSTATE[42S02]: Base table or view not found: 1146 Table 'SES_USERS2.CRM_@SELECTED_CONTEXT' doesn't exist
						throw new Exception("Cannot create config table for @selected with context");
					}
				}
				DBG::log("DBG: Created config table for @selected with context");
			}
		}
		if (!$idContext) {
			try {
				$idContext = DB::getPDO()->insert('CRM__@SELECTED_CONTEXT', [ 'name' => $context, 'A_CREATE_TIME' => "NOW()", 'A_LAST_ACCESS_TIME' => "NOW()", 'A_LAST_UPDATE_TIME' => "NOW()" ]);
				DBG::log("DBG: created context ID:{$idContext} for: '{$context}'");
			} catch (Exception $e) {
				// TODO: if (cannot add new row - table full)
				throw $e;
			}
		} else {
			DBG::log("DBG: found context ID:{$idContext} for: '{$context}'");
			self::_updateLastAccessTime($idContext);
		}
		return $idContext;
	}
	static function _fetchIdContext($context) {
		return (int)DB::getPDO()->fetchValue("
			select ctx.id
			from `CRM__@SELECTED_CONTEXT` ctx
			where ctx.name = :name
		", [ ':name' => $context ]);
	}
	static function _createContextTable() {
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `CRM__@SELECTED_CONTEXT` (
				`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
				`name` varchar(32) NOT NULL,
				`A_CREATE_TIME` datetime,
				`A_LAST_ACCESS_TIME` datetime,
				`A_LAST_UPDATE_TIME` datetime,
				PRIMARY KEY (`ID`),
				UNIQUE KEY `name` (`name`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8;
		");
	}
	static function _updateLastAccessTime($idContext) {
		DB::getPDO()->update('CRM__@SELECTED_CONTEXT', 'ID', $idContext, [ 'A_LAST_ACCESS_TIME' => "NOW()" ]);
	}
	static function _updateLastUpdateTime($idContext) {
		DB::getPDO()->update('CRM__@SELECTED_CONTEXT', 'ID', $idContext, [ 'A_LAST_UPDATE_TIME' => "NOW()" ]);
	}

	static function getTotalSelected($idContext, $typeName) {
		$idUser = User::getID();
		self::prepareUserTable($idContext, $typeName, $idUser);
		self::_updateLastAccessTime($idContext);
		$tableName = self::getAttributeTableName($idContext, $typeName, $idUser);
		return (int)DB::getPDO()->fetchValue("
			select count(*) as total
			from `{$tableName}`
		");
	}

	static function getSelectState($idContext, $typeName, array $listPrimaryKeys) {
		$idUser = User::getID();
		self::prepareUserTable($idContext, $typeName, $idUser);
		self::_updateLastAccessTime($idContext);
		$tableName = self::getAttributeTableName($idContext, $typeName, $idUser);
		if (empty($listPrimaryKeys)) return [];
		$dbQuote = [ DB::getPDO(), 'quote' ];
		$sqlPks = implode(", ", array_map(function ($pk) use ($dbQuote) {
			return $dbQuote($pk);
		}, $listPrimaryKeys));
		$selected = DB::getPDO()->fetchValuesListByKey("
			select primaryKey
			from `{$tableName}`
			where primaryKey in ( {$sqlPks} )
		", $key = 'primaryKey');
		return array_combine($listPrimaryKeys, array_map(function ($pk) use ($selected) {
			return in_array($pk, $selected);
		}, $listPrimaryKeys));
	}

	static function selectAllByFilter($idContext, $typeName, array $filterQuery) {
		$idUser = User::getID();
		self::prepareUserTable($idContext, $typeName, $idUser);
		$tableName = self::getAttributeTableName($idContext, $typeName, $idUser);
		$acl = ACL::getAclByTypeName($typeName);

		$args = [];
		parse_str($filterQuery[0], $args);
		DBG::log(['$filterQuery' => $filterQuery, '$args' => $args], 'array', "DBG: selectAllByFilter");

		$params = [];
		$params['limit'] = 0;
		$params['cols'] = [ $acl->getPrimaryKeyField() ];
		foreach ($args as $k => $v) {
			if (strlen($k) > 3 && substr($k, 0, 2) == 'f_' && strlen($v) > 0) {// filter prefix
				$params[$k] = $v;
			}
			else if (strlen($k) > 4 && substr($k, 0, 3) == 'sf_' && strlen($v) > 0) {// special filter prefix
				$params[$k] = $v;
			}
		}
		$queryFeatures = $acl->buildQuery($params);
		DBG::log($queryFeatures, 'array', '$queryFeatures');
		try {
			$sql = $queryFeatures->getRawQueryPrimaryKeys();
			DBG::log($sql, 'string', 'sql getRawQueryPrimaryKeys');
			Lib::loadClass('FeatureAttrSelected');
			$userSelectedTableName = FeatureAttrSelected::getAttributeTableName($idContext, $typeName = $acl->getNamespace(), $idUser = User::getID());
			DB::getPDO()->execSql("
				insert ignore into `{$userSelectedTableName}` (`primaryKey`)
				select `selectedTable`.`@primaryKey`
				from ( {$sql} ) as `selectedTable`
			");
		} catch (Exception $e) {
			DBG::log($e);
			DBG::log('legacyMode - execute query for all primary keys');
			$listItems = $queryFeatures->getItems();
			$listPrimaryKeys = array_map(function ($item) {
				return $item['ID'];
			}, $listItems);
			foreach ($listPrimaryKeys as $pk) {
				DB::getPDO()->execSql(" insert ignore `{$tableName}` (`primaryKey`) values ( :pk ) ", [ ':pk' => $pk ]);
			}
		}
		self::_updateLastUpdateTime($idContext);
	}

	static function select($idContext, $typeName, array $listPrimaryKeys) {
		$idUser = User::getID();
		self::prepareUserTable($idContext, $typeName, $idUser);
		$tableName = self::getAttributeTableName($idContext, $typeName, $idUser);
		foreach ($listPrimaryKeys as $pk) {
			DB::getPDO()->execSql(" insert ignore `{$tableName}` (`primaryKey`) values ( :pk ) ", [ ':pk' => $pk ]);
		}
		self::_updateLastUpdateTime($idContext);
	}

	static function unselect($idContext, $typeName, array $listPrimaryKeys) {
		$idUser = User::getID();
		self::prepareUserTable($idContext, $typeName, $idUser);
		$tableName = self::getAttributeTableName($idContext, $typeName, $idUser);
		foreach ($listPrimaryKeys as $pk) {
			DB::getPDO()->execSql(" delete from `{$tableName}` where `primaryKey` = :pk ", [ ':pk' => $pk ]);
		}
		self::_updateLastUpdateTime($idContext);
	}

	static function unselectAll($idContext, $typeName) {
		$idUser = User::getID();
		self::prepareUserTable($idContext, $typeName, $idUser);
		$tableName = self::getAttributeTableName($idContext, $typeName, $idUser);
		DB::getPDO()->execSql(" truncate table `{$tableName}` ");
		self::_updateLastUpdateTime($idContext);
	}

	static function prepareUserTable($idContext, $typeName, $idUser) {
		static $_created = [];
		$key = "{$typeName}-{$idContext}-{$idUser}";
		if (!array_key_exists($key, $_created)) {
			self::_prepareUserTable($idContext, $typeName, $idUser);
			$_created[$key] = true;
		}
	}
	static function _prepareUserTable($idContext, $typeName, $idUser) {
		$tableName = self::getAttributeTableName($idContext, $typeName, $idUser);
		// TODO: primaryKey type from $acl
		DB::getPDO()->execSql("
			CREATE TABLE IF NOT EXISTS `{$tableName}` (
				`primaryKey` int(11) NOT NULL,
				UNIQUE KEY `primaryKey` (`primaryKey`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
		");
	}

	// @example FeatureAttrSelected::getAttributeTableName($typeName, $idUser);
	static function getAttributeTableName($idContext, $typeName, $idUser) {
		static $_created = [];
		$key = "{$typeName}-{$idUser}";
		if (!array_key_exists($key, $_created)) {
			$_created[$key] = self::_getAttributeTableName($idContext, $typeName, $idUser);
		}
		return $_created[$key];
	}
	static function _getAttributeTableName($idContext, $typeName, $idUser) {
		$acl = ACL::getAclByTypeName($typeName);
		$rootTableName = $acl->getRootTableName();
		return "{$rootTableName}__@sel_{$idContext}_{$idUser}";
	}

}