se/SE/se-lib/UserStorageDB.php

<?php

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

class UserStorageDB extends UserStorageBase {

	private $_db;

	public function __construct($db) {
		$this->_db = $db;
	}

	/**
	 * @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				1, 0 or null if not set
	 */
	public function getUser($usrLogin) {
		if (!$this->_db) return false;
		if (empty($usrLogin)) return false;

		$user = null;
		$sql = "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`='{$usrLogin}'
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$user = $this->_buildUserFromRow($r);
		}
		return $user;
	}

	private function _buildUserFromRow($r) {
		$user = new ObjectUserDB($this);
		$user->primaryKey = $r->primaryKey;
		$user->login = $r->login;
		$user->password = $r->password;
		$user->name = $r->name;
		$user->email = $r->email;
		$user->phone = $r->phone;
		$user->homeEmail = $r->homeEmail;
		$user->homePhone = $r->homePhone;
		$user->employeeType = $r->employeeType;
		$user->isDisabled = (int)$r->isDisabled;
		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($groupID) {
		if (!$this->_db) return false;
		if ($groupID <= 0) return false;

		$group = null;
		$sql = "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`='{$groupID}'
				and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$group = $this->_buildGroupFromRow($r, $fetchNested = true);
		}
		return $group;
	}

	public function getGroupWithoutNested($groupID) {
		if (!$this->_db) return false;
		if ($groupID <= 0) return false;

		$group = null;
		$sql = "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`='{$groupID}'
				and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$group = $this->_buildGroupFromRow($r, $fetchNested = false);
		}
		return $group;
	}

	public function fetchNestedGroups($groupID) {
		if (!$this->_db) return null;
		if ($groupID <= 0) return null;
		$groups = array();
		$sql = "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`='{$groupID}'
				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')
		";
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			$groups[$r->groupID] = $this->_buildGroupFromRow($r, $fetchNested = false);
		}

		return $groups;
	}

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

	public function fetchParentGroups($groupID) {
		if (!$this->_db) return null;
		if ($groupID <= 0) return null;
		$groups = array();
		$sql = "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`='{$groupID}'
				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')
		";
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			$groups[$r->groupID] = $this->_buildGroupFromRow($r, $fetchNested = false);
		}

		return $groups;
	}

	private function _getGroupConnection($parentGroupID, $groupID) {
		if (!$this->_db) return null;
		if ($parentGroupID <= 0) return null;
		if ($groupID <= 0) return null;
		$sql = "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`='{$parentGroupID}'
				and l.`TABLE_2_ID`='{$groupID}'
				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')
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			return $r;
		}
		return null;
	}

	private function _setGroupConnection($parentGroupID, $groupID) {
		if (!$this->_db) return null;
		if ($groupID <= 0 || $parentGroupID <= 0) return null;

		$tblName = 'CRM_LISTA_ZASOBOW';
		Lib::loadClass('ProcesHelper');
		$tblZasobyID = ProcesHelper::getZasobTableID($tblName);
		if (!$tblZasobyID) return false;

		$connObj = $this->_getGroupConnection($parentGroupID, $groupID);
		if ($connObj) {
			$connObj->A_STATUS = 'NORMAL';
			$affected = $this->_db->UPDATE_OBJ('ITEM_LINKS', $connObj);
			if ($affected > 0) {
				return true;
			}
		}
		else {
			$sqlObj = new stdClass();
			$sqlObj->TABLE_1_ID = $parentGroupID;
			$sqlObj->TABLE_2_ID = $groupID;
			$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';

			$rowID = $this->_db->ADD_NEW_OBJ('ITEM_LINKS', $sqlObj);
			if ($rowID > 0) {
				return true;
			}
		}
		return false;
	}

	public function addNestedGroup($groupID, $nestedGroupID) {
		if (!$this->_db) return null;
		if ($groupID <= 0) return null;
		if ($nestedGroupID) return null;
		return $this->_setGroupConnection($groupID, $nestedGroupID);
	}

	public function addParentGroup($groupID, $parentGroupID) {
		if (!$this->_db) return null;
		if ($groupID <= 0) return null;
		if ($parentGroupID <= 0) return null;
		return $this->_setGroupConnection($parentGroupID, $groupID);
	}

	public function removeNestedGroup($groupID, $nestedGroupID) {
		if (!$this->_db) return null;
		if ($groupID <= 0) return null;
		if ($nestedGroupID <= 0) return null;

		$connObj = $this->_getGroupConnection($groupID, $nestedGroupID);
		if ($connObj) {
			$connObj->A_STATUS = 'DELETED';
			$affected = $this->_db->UPDATE_OBJ('ITEM_LINKS', $connObj);
			if ($affected > 0) {
				return true;
			}
		}
		return false;
	}

	public function removeParentGroup($groupID, $parentGroupID) {
		if (!$this->_db) return null;
		if ($groupID <= 0) return null;
		if ($parentGroupID <= 0) return null;

		$connObj = $this->_getGroupConnection($parentGroupID, $groupID);
		if ($connObj) {
			$connObj->A_STATUS = 'DELETED';
			$affected = $this->_db->UPDATE_OBJ('ITEM_LINKS', $connObj);
			if ($affected > 0) {
				return true;
			}
		}
		return false;
	}

	/**
	 * @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;
	}

	private function _getUserGroupsAll($usrLogin) {
		$groups = 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 = implode(', ', $sql_select);
		$sql = "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','PODMIOT')
		";
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			$groups[$r->ID] = $this->_buildGroupFromRow($r);
		}

		return $groups;
	}

	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($this);
		$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');
		}

		$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) return false;
		$usrDB = $this->getUser($usrLogin);
		if (!$usrDB) return false;

		$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;
		$rowID = $this->_db->ADD_NEW_OBJ('CRM_AUTH_PROFILE', $sqlObj);
		if ($rowID > 0) {
			return true;
		}
		return false;
	}

	/**
	 * 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;
		$profile = null;

		$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);
		$sql = "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','PODMIOT')
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$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);
		$sql = "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','PODMIOT')
		";
		$res = $this->_db->query($sql);
		while ($r = $this->_db->fetch($res)) {
			$profile = new stdClass();
			$profile->profileId = $r->profileId;
			$profile->localisationId = $r->localisationId;
			$profile->usrId = $r->usrId;
			$profile->usrLogin = $r->usrLogin;
			$profile->group = $this->_buildGroupFromRow($r);
			$profiles[] = $profile;
		}

		return $profiles;
	}

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

		$sql = "delete from `CRM_AUTH_PROFILE`
			where
				`ID_ZASOB`='{$group->zasobID}'
				and `REMOTE_ID`='{$usrDB->primaryKey}'
				and `REMOTE_TABLE`='ADMIN_USERS'
				and `ID`='{$profileID}'
		";
		$res = $this->_db->query($sql);
		return true;
	}

	public function setSyncUserDate($usrLogin) {
		if (!$this->_db) return false;
		$sql = "update `ADMIN_USERS` set `A_SYNC_LDAP_DATE`=NOW() where `ADM_ACCOUNT`='{$usrLogin}' ";
		$res = $this->_db->query($sql);
	}

	public function setSyncGroupDate($groupID) {
		if (!$this->_db) return false;
		if ($groupID <= 0) return false;
		$sql = "update `CRM_LISTA_ZASOBOW` set `A_SYNC_LDAP_DATE`=NOW() where `ID`='{$groupID}' ";
		$res = $this->_db->query($sql);
	}

	public function isPasswordChanged($usrLogin) {
		$cnt = 0;
		$sql = "
		  SELECT
		--         h.`ADM_PASSWD`,
		         count(1) as cnt
		    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`
		";
		$res = $this->_db->query($sql);
		if ($r = $this->_db->fetch($res)) {
			$cnt = $r->cnt;
		}
		return ($cnt > 0);
	}

}