se/SE/se-lib/UserStorageDB.php

<?php

Lib::loadClass('UserStorageBase');
Lib::loadClass('ObjectUserDB');
Lib::loadClass('ObjectGroupDB');

class UserStorageDB extends UserStorageBase {

	public function __construct() {
	}

	/**
	 * @return object $usr
	 *   $usr->primaryKey
	 *   $usr->login
	 *   $usr->password					optional (required in createUser)
	 *   $usr->name
	 *   $usr->email
	 *   $usr->phone
	 *   $usr->homeEmail
	 *   $usr->homePhone
	 *   $usr->employeeType			'Pracownik','Kandydat','Partner'
	 *   $usr->isDisabled				true, false or null if not set
	 */
	public function getUser($usrLogin) {
		if (empty($usrLogin)) return false;

		$user = DB::getPDO()->fetchFirstAsObject("
			SELECT a.`ID` as primaryKey
				, a.`ADM_ACCOUNT` as login
				, a.`ADM_PASSWD` as password
				, a.`ADM_NAME` as name
				, a.`EMAIL_LOCAL_ACCOUNT_ADDRESS` as email
				, a.`ADM_PHONE` as phone
				, a.`EMAIL` as homeEmail
				, '' as homePhone
				, a.`EMPLOYEE_TYPE` as employeeType
				, IF(a.`A_STATUS`='NORMAL', 0, 1) as isDisabled

		--		, a.`ADM_ADMIN_LEVEL`
		--		, a.`ADM_ADMIN_DESC`  -- stanowisko
		--		, a.`ADM_NIP`  -- NIP
		--		, a.`ADM_PESEL`  -- nr. PESEL
			from `ADMIN_USERS` as a
			where a.`ADM_ACCOUNT` = :login
		", [
			':login' => $usrLogin,
		]);

		return ($user) ? $this->_buildUserFromRow($user) : null;
	}

	private function _buildUserFromRow($r) {
		$user = new ObjectUserDB($this);
		$user->primaryKey = $r->primaryKey;
		$user->login = $r->login;
		$user->password = $r->password;
		$user->name = trim($r->name);
		$user->email = trim($r->email);

		$user->aliasesList = array();
		{
			$aliasesEx = $r->email;
			$aliasesEx = explode(' ', trim($aliasesEx));
			foreach ($aliasesEx as $emailAlias) {
				$emailAlias = trim($emailAlias);
				if (!empty($emailAlias) && filter_var($emailAlias, FILTER_VALIDATE_EMAIL)) {
					$user->aliasesList[] = $emailAlias;
				}
			}
		}

		$user->phone = trim($r->phone);
		$user->homeEmail = trim($r->homeEmail);
		$user->homePhone = trim($r->homePhone);
		$user->employeeType = $r->employeeType;
		$user->isDisabled = ((int)$r->isDisabled)? true : false;

		$exName = explode(' ', trim($r->name));
		$user->lastName = array_pop($exName);
		$user->firstName = (empty($exName)) ? $user->lastName : implode(' ', $exName);
		return $user;
	}

	/**
	 * Build group realName from zasob.
	 *
	 * @param object $zasob {ID, DESC, TYPE}
	 * @return string realName
	 */
	protected function _buildRealNameFromZasob($zasob) {
		$realName = "{$zasob->DESC}";
		if ($zasob->TYPE != 'STANOWISKO') $realName = "{$zasob->TYPE} {$realName}";
		$realName = "[{$zasob->ID}] {$realName}";
		return $realName;
	}

	/**
	 * Group.
	 *
	 * @return object $group
	 *   $group->primaryKey
	 *   $group->realName
	 *   $group->nestedGroups
	 *   $group->type			'STANOWISKO','PODMIOT','DZIAL','local'
	 *   $group->zasobID
	 *   (optional) $group->zasobDESC
	 */
	public function getGroup($idGroup) {
		if ($idGroup <= 0) return false;

		$group = DB::getPDO()->fetchFirstAsObject("
			SELECT z.`ID`
				, z.`DESC`
				, z.`TYPE`
		--		, IF(a.`A_STATUS`='NORMAL', 0, 1) as isDisabled
			from `CRM_LISTA_ZASOBOW` as z
			where z.`ID` = '{$idGroup}'
				and z.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
		");

		return ($group) ? $this->_buildGroupFromRow($group, $fetchNested = true) : null;
	}

	public function getGroupWithoutNested($idGroup) {
		if ($idGroup <= 0) return false;

		$group = DB::getPDO()->fetchFirstAsObject("
			SELECT z.`ID`
				, z.`DESC`
				, z.`TYPE`
		--		, IF(a.`A_STATUS` = 'NORMAL', 0, 1) as isDisabled
			from `CRM_LISTA_ZASOBOW` as z
			where z.`ID` = '{$idGroup}'
				and z.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
		");

		return ($group) ? $this->_buildGroupFromRow($group, $fetchNested = false) : null;
	}

	public function fetchNestedGroups($idGroup) {
		if ($idGroup <= 0) return null;

		return array_map(function ($item) {
			return $this->_buildGroupFromRow((object)$item, $fetchNested = false);
		}, DB::getPDO()->fetchAllByKey("
			SELECT l.`TABLE_2_ID` as groupID
				, z2.`ID`
				, z2.`DESC`
				, z2.`TYPE`
			from `ITEM_LINKS` as l
				join `CRM_LISTA_ZASOBOW` as z1 on ( z1.`ID` = l.`TABLE_1_ID` )
				join `CRM_LISTA_ZASOBOW` as z2 on ( z2.`ID` = l.`TABLE_2_ID` )
			where l.`TABLE_1_ID` = '{$idGroup}'
				and l.`TABLE_2_ID` > 0
				and l.`LINKS_TYPE_ID` = 5
				and l.`TABLE_1_NAME` = 'CRM_LISTA_ZASOBOW'
				and l.`A_STATUS` in ( 'NORMAL' )
				and l.`TABLE_2_NAME` = 'CRM_LISTA_ZASOBOW'
				and z1.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
				and z2.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
		", 'groupID'));
	}

	public function getParentGroups(ObjectGroup $group) {
		return $this->fetchParentGroups($group->primaryKey);
	}

	public function fetchParentGroups($idGroup) {
		if ($idGroup <= 0) return null;

		return array_map(function ($item) {
			return $this->_buildGroupFromRow((object)$item, $fetchNested = false);
		}, DB::getPDO()->fetchAllByKey("
			SELECT l.`TABLE_1_ID` as groupID
				, z1.`ID`
				, z1.`DESC`
				, z1.`TYPE`
			from `ITEM_LINKS` as l
				join `CRM_LISTA_ZASOBOW` as z1 on ( z1.`ID` = l.`TABLE_1_ID` )
				join `CRM_LISTA_ZASOBOW` as z2 on ( z2.`ID` = l.`TABLE_2_ID` )
			where l.`TABLE_1_ID` > 0
				and l.`TABLE_2_ID` = '{$idGroup}'
				and l.`LINKS_TYPE_ID` = 5
				and l.`TABLE_1_NAME` = 'CRM_LISTA_ZASOBOW'
				and l.`A_STATUS` in ( 'NORMAL' )
				and l.`TABLE_2_NAME` = 'CRM_LISTA_ZASOBOW'
				and z1.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
				and z2.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
		", 'groupID'));
	}

	private function _getGroupConnection($idParentGroup, $idGroup) {
		if ($idParentGroup <= 0) return null;
		if ($idGroup <= 0) return null;

		$listItems = array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAll("
			SELECT l.*
			from `ITEM_LINKS` as l
				join `CRM_LISTA_ZASOBOW` as z1 on ( z1.`ID` = l.`TABLE_1_ID` )
				join `CRM_LISTA_ZASOBOW` as z2 on ( z2.`ID` = l.`TABLE_2_ID` )
			where l.`TABLE_1_ID` = '{$idParentGroup}'
				and l.`TABLE_2_ID` = '{$idGroup}'
				and l.`LINKS_TYPE_ID` = 5
				and l.`TABLE_1_NAME` = 'CRM_LISTA_ZASOBOW'
				and l.`TABLE_2_NAME` = 'CRM_LISTA_ZASOBOW'
				and z1.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
				and z2.`TYPE` in ( 'STANOWISKO', 'PODMIOT', 'DZIAL' )
		"));

		return (!empty($listItems)) ? $listItems : null;
	}

	private function _setGroupConnection($idParentGroup, $idGroup, $errorMsg = null) {
		if ($idGroup <= 0) throw new Exception("Wrong param id group!");
		if ($idParentGroup <= 0) throw new Exception("Wrong param id parent group!");
		if (!$errorMsg) $errorMsg = "Nie udało się utworzyć połączenia grup [{$idGroup}] i [{$idParentGroup}]";

		$tblName = 'CRM_LISTA_ZASOBOW';
		Lib::loadClass('ProcesHelper');
		$tblZasobyID = ProcesHelper::getZasobTableID($tblName);
		if (!$tblZasobyID) throw new Exception("Cannot find zasob id for table Zasoby!");

		$connObj = $this->_getGroupConnection($idParentGroup, $idGroup);
		if ($connObj) {
			$connObj->A_STATUS = 'NORMAL';
			$affected = DB::getDB()->UPDATE_OBJ('ITEM_LINKS', $connObj);
			if ($affected <= 0) throw new Exception($errorMsg);
		}
		else {
			$sqlObj = new stdClass();
			$sqlObj->TABLE_1_ID = $idParentGroup;
			$sqlObj->TABLE_2_ID = $idGroup;
			$sqlObj->TABLE_1_NAME = $tblName;
			$sqlObj->TABLE_2_NAME = $tblName;
			$sqlObj->TABLE_1_ZASOB_ID = $tblZasobyID;
			$sqlObj->TABLE_2_ZASOB_ID = $tblZasobyID;
			$sqlObj->LINKS_TYPE_ID = 5;// NestedGroups
			$sqlObj->A_STATUS = 'NORMAL';

			$idCreatedRow = DB::getDB()->ADD_NEW_OBJ('ITEM_LINKS', $sqlObj);
			if ($idCreatedRow <= 0) throw new Exception($errorMsg);
		}
	}

	public function addNestedGroup($idGroup, $idNestedGroup) {
		if ($idGroup <= 0) throw new Exception("Wrong param id group!");
		if ($idNestedGroup <= 0) throw new Exception("Wrong param id nested group!");
		$errorMsg = "Nie udało się dodać grupy zagnieżdżonej [{$idNestedGroup}] do grupy [{$idGroup}]";
		return $this->_setGroupConnection($idGroup, $idNestedGroup, $errorMsg);
	}

	public function addParentGroup($idGroup, $idParentGroup) {
		if ($idGroup <= 0) throw new Exception("Wrong param id group!");
		if ($idParentGroup <= 0) throw new Exception("Wrong param id parent group!");
		$errorMsg = "Nie udało się dodać grupy nadrzędnej [{$idParentGroup}] do grupy [{$idGroup}]";
		return $this->_setGroupConnection($idParentGroup, $idGroup, $errorMsg);
	}

	public function removeNestedGroup($idGroup, $idNestedGroup) {
		if ($idGroup <= 0) throw new Exception("Wrong param id group!");
		if ($idNestedGroup <= 0) throw new Exception("Wrong param id nested group!");

		$connObj = $this->_getGroupConnection($idGroup, $idNestedGroup);
		if ($connObj) {
			$connObj->A_STATUS = 'DELETED';
			$affected = DB::getDB()->UPDATE_OBJ('ITEM_LINKS', $connObj);
			if ($affected <= 0) {
				throw new Exception("Nie udało się usunąć grupy zagnieżdżonej '{$kGroupID}' do grupy '{$groupID}' w bazie danych");
			}
		}
	}

	public function removeParentGroup($idGroup, $idParentGroup) {
		if ($idGroup <= 0) throw new Exception("Wrong param id group!");
		if ($idParentGroup <= 0) throw new Exception("Wrong param id parent group!");

		$connObj = $this->_getGroupConnection($idParentGroup, $idGroup);
		if ($connObj) {
			$connObj->A_STATUS = 'DELETED';
			$affected = DB::getDB()->UPDATE_OBJ('ITEM_LINKS', $connObj);
			if ($affected <= 0) {
				throw new Exception("Nie udało się usunąć grupy nadrzędnej [{$idParentGroupToRemove}] do grupy [{$idGroup}]");
			}
		}
	}

	/**
	 * @return bool
	 */
	public function isDisabled($usr) {
		if (null == $usr->isDisabled) {
			// TODO: sql IF(a.`A_STATUS`='NORMAL', 1, 0) as isDisabled
		}
		return $usr->isDisabled;
	}

	/**
	 * @return bool
	 */
	public function setDisabled($usrLogin, $isDisabled) {
		if (empty($usrLogin) || null == $isDisabled) {
			return false;
		}
		$sqlStatus = '';
		if ($isDisabled) {
			$sqlStatus = 'OFF_HARD';
		} else {
			$sqlStatus = 'NORMAL';
		}
		$sql = "
			update `ADMIN_USERS`
			set `A_STATUS`='{$sqlStatus}'
			where
				`ADM_ACCOUNT`='{$usrLogin}'
		";
		return false;
	}

	public function createUser($userData) {
		// TODO: insert into `ADMIN_USERS` ...
		return false;
	}

	public function updateUser($usrLogin, $updateData) {
		// TODO: update `ADMIN_USERS` set ...
		return false;
	}

	public function _getUserGroupsAll($usrLogin) {
		$sql_select = array();
		$sql_select[] = "z.`ID`";
		$sql_select[] = "z.`DESC`";
		$sql_select[] = "z.`TYPE`";
		//$sql_select[] = "z.`OPIS`";
		$sql_select[] = "z.`A_LDAP_GID`";
		$sql_select = implode(', ', $sql_select);

		return array_map(function ($item) {
			return $this->_buildGroupFromRow((object)$item);
		}, DB::getPDO()->fetchAllByKey("
			select {$sql_select}
			from `CRM_AUTH_PROFILE` as up
				join `ADMIN_USERS` as a on ( a.`ID` = up.`REMOTE_ID` and up.`REMOTE_TABLE` = 'ADMIN_USERS' )
				join `CRM_LISTA_ZASOBOW` as z on ( z.`ID` = up.`ID_ZASOB` )
			where
				a.`ADM_ACCOUNT` = '{$usrLogin}'
				and up.`A_STATUS` in ( 'WAITING', 'NORMAL' )
				and z.`TYPE` in ( 'STANOWISKO', 'DZIAL', 'PODMIOT' )
		", 'ID'));
	}

	private function _getUserGroupsBelow($groups) {// TODO: fetch groups below
		if (empty($groups)) return null;
		$groupsBelow = array();
		$sqlGroupIds = array_keys($groups);
		$sql = "

		";

		return $groupsBelow;
	}

	private function _getUserGroupsAbove($groups) {// TODO: fetch groups below
		if (empty($groups)) return null;
		$groupsAbove = array();
		$sqlGroupIds = array_keys($groups);
		$sql = "

		";

		return $groupsAbove;
	}

	/**
	 * Build network group object.
	 *
	 * @param object $groupDB {ID, DESC, TYPE} @see _getUserGroupsAll
	 * @return object $group @see getGroup
	 *
	 * Example: _buildGroupFromRow($r) => {@see group}
	 */
	private function _buildGroupFromRow($groupDB, $fetchNested = false) {
		$group = new ObjectGroupDB('DB');
		$group->primaryKey = $groupDB->ID;
		$group->type = $groupDB->TYPE;
		$group->realName = $this->_buildRealNameFromZasob($groupDB);
		$group->zasobID = $groupDB->ID;
		$group->zasobDESC = $groupDB->DESC;
		if ($fetchNested) $group->nestedGroups = $this->fetchNestedGroups($groupDB->ID);
		return $group;
	}

	/**
	 * User group list by id.
	 *
	 * @param bool $fetchNested - contain all groups below connected groups and group PODMIOT from above.
	 *
	 * @return array with group objects @see getGroup
	 */
	public function getUserGroups($usrLogin, $fetchNested = false) {
		$usrDB = $this->getUser($usrLogin);
		if (!$usrDB) return false;
		$groups = array();

		if ($usrDB->employeeType == 'Pracownik') {
			$groups['workgroup'] = $this->_buildGroupNetwork('workgroup');
			$groups['com.apple.access_mail'] = $this->_buildGroupLocal('com.apple.access_mail');
			$groups['com.apple.access_addressbook'] = $this->_buildGroupLocal('com.apple.access_addressbook');
			$groups['com.apple.access_calendar'] = $this->_buildGroupLocal('com.apple.access_calendar');
			$groups['com.apple.access_smb'] = $this->_buildGroupLocal('com.apple.access_smb');
			$groups['com.apple.access_afp'] = $this->_buildGroupLocal('com.apple.access_afp');
			$groups['com.apple.access_vpn'] = $this->_buildGroupLocal('com.apple.access_vpn');
			$groups['com.apple.access_chat'] = $this->_buildGroupLocal('com.apple.access_chat');
		} else if ($usrDB->employeeType == 'Partner') {
			$groups['com.apple.access_smb'] = $this->_buildGroupLocal('com.apple.access_smb');
			$groups['com.apple.access_afp'] = $this->_buildGroupLocal('com.apple.access_afp');
		}

		$groupsAll = $this->_getUserGroupsAll($usrLogin);
		if (is_array($groupsAll) && !empty($groupsAll)) {
			foreach ($groupsAll as $kId => $vGroup) {
				$groups[$vGroup->zasobID] = $vGroup;
			}

			if (false) {// TODO: $fetchNested) {
				$groupsBelow = $this->_getUserGroupsBelow($groupsAll);
				if (is_array($groupsBelow) && !empty($groupsBelow)) {
					foreach ($groupsBelow as $kId => $vGroup) {
						if (!isset($groups[$vGroup->zasobID])) {
							$groups[$vGroup->zasobID] = $vGroup;
						}
					}
				}

				$groupsAbove = $this->_getUserGroupsAbove($groupsAll);
				if (is_array($groupsAbove) && !empty($groupsAbove)) {
					foreach ($groupsAbove as $kId => $vGroup) {
						if (!isset($groups[$vGroup->zasobID])) {
							$groups[$vGroup->zasobID] = $vGroup;
						}
					}
				}
			}
		}

		return $groups;
	}

	/**
	 * Add group member.
	 *
	 * @param string $usrLogin - user login
	 * @param object $group - @see getGroup
	 * @param optional int $telboxID
	 * @return bool
	 */
	public function addUserGroup($usrLogin, $group, $telboxID = 0) {
		if (!$group->zasobID) throw new Exception("Group has no id zasob!");
		$usrDB = $this->getUser($usrLogin);
		if (!$usrDB) throw new Exception("User '{$usrLogin}' not found");

		$sqlObj = new stdClass();
		$sqlObj->ID_ZASOB = $group->zasobID;
		$sqlObj->REMOTE_TABLE = 'ADMIN_USERS';
		$sqlObj->REMOTE_ID = $usrDB->primaryKey;
		$sqlObj->T_TELBOX_NEIGHBOUR_IN_ID = $telboxID;
		$sqlObj->A_STATUS = 'NORMAL';

		// uniq key: (ID_ZASOB, REMOTE_ID, REMOTE_TABLE, T_TELBOX_NEIGHBOUR_IN_ID)
		$id = DB::getPDO()->fetchValue("
			select `ID`
			from `CRM_AUTH_PROFILE`
			where `ID_ZASOB`='{$sqlObj->ID_ZASOB}'
				and `REMOTE_TABLE`='{$sqlObj->REMOTE_TABLE}'
				and `REMOTE_ID`='{$sqlObj->REMOTE_ID}'
				and `T_TELBOX_NEIGHBOUR_IN_ID`='{$sqlObj->T_TELBOX_NEIGHBOUR_IN_ID}'
		");
		if ($id > 0) {
			$affected = DB::getPDO()->update('CRM_AUTH_PROFILE', 'ID', $id, [
				'ID_ZASOB' => $group->zasobID,
				'REMOTE_TABLE' => 'ADMIN_USERS',
				'REMOTE_ID' => $usrDB->primaryKey,
				'T_TELBOX_NEIGHBOUR_IN_ID' => $telboxID,
				'A_STATUS' => 'NORMAL',
				'A_RECORD_UPDATE_AUTHOR' => User::getLogin(),
				'A_RECORD_UPDATE_DATE' => 'NOW()',
			]);
			if ($affected <= 0) throw new Exception("Nie udało się przypisać grupy (błąd podczas aktualizacji rekordu)");
			DB::getPDO()->insert('CRM_AUTH_PROFILE_HIST', [
				'ID_SUERS2' => $id,
				'ID_ZASOB' => $group->zasobID,
				'REMOTE_TABLE' => 'ADMIN_USERS',
				'REMOTE_ID' => $usrDB->primaryKey,
				'T_TELBOX_NEIGHBOUR_IN_ID' => $telboxID,
				'A_STATUS' => 'NORMAL',
				'A_RECORD_UPDATE_AUTHOR' => User::getLogin(),
				'A_RECORD_UPDATE_DATE' => 'NOW()',
			]);
		} else {
			$rowID = DB::getPDO()->insert('CRM_AUTH_PROFILE', [
				'ID_ZASOB' => $group->zasobID,
				'REMOTE_TABLE' => 'ADMIN_USERS',
				'REMOTE_ID' => $usrDB->primaryKey,
				'T_TELBOX_NEIGHBOUR_IN_ID' => $telboxID,
				'A_STATUS' => 'NORMAL',
				'A_RECORD_CREATE_AUTHOR' => User::getLogin(),
				'A_RECORD_CREATE_DATE' => 'NOW()',
			]);
			if ($rowID <= 0) throw new Exception("Nie udało się przypisać grupy (błąd podczas tworzenia rekordu)");
		}
	}

	/**
	 * Get user and group info by profile ID (CRM_AUTH_PROFILE.ID)
	 * Only in UserStorageDB
	 *
	 * @return profile {}
	 *   ID
	 *   usrId
	 *   usrLogin
	 *   group - @see getGroup
	 */
	public function getProfileById($profileID) {
		if (!$profileID) return false;

		$sql_select = array();
		$sql_select[] = "z.`ID`";
		$sql_select[] = "z.`DESC`";
		$sql_select[] = "z.`TYPE`";
		//$sql_select[] = "z.`OPIS`";
		$sql_select[] = "z.`A_LDAP_GID`";
		$sql_select[] = "up.`ID` as profileId";
		$sql_select[] = "up.`T_TELBOX_NEIGHBOUR_IN_ID` as localisationId";
		$sql_select[] = "a.`ADM_ACCOUNT` as usrLogin";
		$sql_select[] = "a.`ID` as usrId";

		$sql_select = implode(', ', $sql_select);

		$r = DB::getPDO()->fetchFirstAsObject("
			select {$sql_select}
			from `CRM_AUTH_PROFILE` as up
				join `ADMIN_USERS` as a on ( a.`ID` = up.`REMOTE_ID` and up.`REMOTE_TABLE` = 'ADMIN_USERS' )
				join `CRM_LISTA_ZASOBOW` as z on ( z.`ID` = up.`ID_ZASOB` )
			where
				up.`ID` = '{$profileID}'
				and up.`A_STATUS` in ( 'WAITING', 'NORMAL' )
				and z.`TYPE` in ( 'STANOWISKO', 'DZIAL', 'PODMIOT' )
		");

		if (!$r) return null;

		$profile = new stdClass();
		$profile->profileId = $r->profileId;
		$profile->localisationId = $r->localisationId;
		$profile->usrId = $r->usrId;
		$profile->usrLogin = $r->usrLogin;
		$profile->group = $this->_buildGroupFromRow($r);

		return $profile;
	}

	/**
	 * Get user and group info by profile ID (CRM_AUTH_PROFILE.ID)
	 * Only in UserStorageDB
	 *
	 * @return array of profile {}
	 *   ID
	 *   usrId
	 *   usrLogin
	 *   group - @see getGroup
	 */
	public function getUserProfiles($usrLogin) {
		if (!$usrLogin) return false;
		$profiles = array();

		$sql_select = array();
		$sql_select[] = "z.`ID`";
		$sql_select[] = "z.`DESC`";
		$sql_select[] = "z.`TYPE`";
		//$sql_select[] = "z.`OPIS`";
		$sql_select[] = "z.`A_LDAP_GID`";
		$sql_select[] = "up.`ID` as profileId";
		$sql_select[] = "up.`T_TELBOX_NEIGHBOUR_IN_ID` as localisationId";
		$sql_select[] = "a.`ADM_ACCOUNT` as usrLogin";
		$sql_select[] = "a.`ID` as usrId";

		$sql_select = implode(', ', $sql_select);
		return array_map(function ($item) {
			$r = (object)$item;
			$profile = new stdClass();
			$profile->profileId = $r->profileId;
			$profile->localisationId = $r->localisationId;
			$profile->usrId = $r->usrId;
			$profile->usrLogin = $r->usrLogin;
			$profile->group = $this->_buildGroupFromRow($r);

			return $profile;
		}, DB::getPDO()->fetchAll("
			select {$sql_select}
			from `CRM_AUTH_PROFILE` as up
				join `ADMIN_USERS` as a on ( a.`ID` = up.`REMOTE_ID` and up.`REMOTE_TABLE` = 'ADMIN_USERS' )
				join `CRM_LISTA_ZASOBOW` as z on ( z.`ID` = up.`ID_ZASOB` )
			where
				a.`ADM_ACCOUNT` = '{$usrLogin}'
				and up.`A_STATUS` in ( 'WAITING', 'NORMAL' )
				and z.`TYPE` in ( 'STANOWISKO', 'DZIAL', 'PODMIOT' )
		"));
	}

	/**
	 * Remove user group by profile ID (CRM_AUTH_PROFILE.ID)
	 * Only in UserStorageDB
	 */
	public function removeUserGroupByProfileId($usrLogin, $group, $profileID) {
		if (!$usrLogin || !$profileID || !$group || !$group->zasobID) return false;
		$usrDB = $this->getUser($usrLogin);
		if (!$usrDB) return false;

		DB::getPDO()->execSql("
			delete from `CRM_AUTH_PROFILE`
			where
				`ID_ZASOB` = '{$group->zasobID}'
				and `REMOTE_ID` = '{$usrDB->primaryKey}'
				and `REMOTE_TABLE` = 'ADMIN_USERS'
				and `ID` = '{$profileID}'
		");
		return true;
	}

	public function setSyncUserDate($usrLogin) {
		DB::getPDO()->execSql("
			update `ADMIN_USERS`
			set `A_SYNC_LDAP_DATE` = NOW()
			where `ADM_ACCOUNT` = '{$usrLogin}'
		");
	}

	public function setSyncGroupDate($idGroup) {
		if ($idGroup <= 0) return false;

		DB::getPDO()->execSql("
			update `CRM_LISTA_ZASOBOW`
			set `A_SYNC_LDAP_DATE` = NOW()
			where `ID` = '{$idGroup}'
		");
	}

	public function isPasswordChanged($usrLogin) {
		$cnt = DB::getPDO()->fetchValue("
			SELECT count(1) as cnt
				, h.`ADM_PASSWD`
			FROM `ADMIN_USERS` as u
				JOIN `ADMIN_USERS_HIST` as h on ( h.`ID_USERS2` = u.`ID` )
			WHERE u.`ADM_ACCOUNT` = '{$usrLogin}'
				AND h.`A_RECORD_CREATE_DATE` > u.`A_SYNC_LDAP_DATE`
				AND h.`ADM_PASSWD` != 'N/S;'
				AND h.`ADM_PASSWD` != ''
			-- GROUP BY h.`ADM_PASSWD`
		");

		return ($cnt > 0);
	}

}