se/SE/se-lib/UsersHelper.php

<?php

Lib::loadClass('UsersLdapHelper');

class UsersHelper {

	public static function _parse_query(&$params) {
		$sql_where = '';
		//if (!empty($params)) {
			$sql_where_and_arr = array();
			$sql_where_and_arr[] = "a.`A_STATUS` in('WAITING','NORMAL','MONITOR','WARNING')";
			$sql_where_and_arr[] = "a.`ADM_ADMIN_DESC`!='Kandydat'";
		//} else {
		//	$sql_where = "1=1";
		//}
		if (isset($params['ADM_ADMIN_LEVEL'])) {
			$adm_lvl = V::get('ADM_ADMIN_LEVEL', 0, $params, 'int');
			$sql_where_and_arr[] = "a.`ADM_ADMIN_LEVEL`='{$adm_lvl}'";
		}
		if (!empty($params['group'])) {
			$sql_where_and_arr[] = "(select up.`ID`
					from `CRM_AUTH_PROFILE` as up
					where
						up.`REMOTE_TABLE`='ADMIN_USERS'
						and up.`A_STATUS` in('WAITING', 'NORMAL')
						and up.`REMOTE_ID`=a.`ID`
						and up.`ID_ZASOB`='{$params['group']}'
					limit 1
				)>0";
		}
		$sql_where = implode(" and ", $sql_where_and_arr);
		return $sql_where;
	}

	public static function get_users_total($params = array()) {
		$sql_where = UsersHelper::_parse_query($params);
		return DB::getPDO()->fetchValue("
			select count(1) as cnt
			from `ADMIN_USERS` as a
			where {$sql_where}
		");
	}

	public static function &get_users_list($params = array(), $limit = 10, $limit_start = 0, $order_by = '', $order_dir = '') {
		$ret = array();

		$sql_where = UsersHelper::_parse_query($params);
		$sql_limit = "";
		if ($limit > 0) {
			$sql_limit = "limit {$limit}";
			if ($limit_start > 0) $sql_limit .= " offset {$limit_start}";
		}
		$allowed_order_by = array();
		$allowed_order_by[] = 'ID';
		$allowed_order_by[] = 'ADM_NAME';
		$sql_order_by = "";
		if (in_array($order_by, $allowed_order_by)) {
			$sql_order_by .= " order by {$order_by}";
			if (in_array($order_dir, array('DESC','ASC'))) {
				$sql_order_by .= " {$order_dir}";
			}
		}

		// 'WAITING','NORMAL','MONITOR','WARNING','OFF_SOFT','OFF_HARD','DELETED'
		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey("
			SELECT a.`ID`
				, a.`A_STATUS`
				, a.`ADM_ACCOUNT`
				, a.`ADM_ADMIN_LEVEL`
				, a.`ADM_ADMIN_DESC`  -- stanowisko
				, a.`ADM_NAME`  -- imie i nazwisko
				, a.`ADM_NIP`  -- NIP
				, a.`ADM_PESEL`  -- nr. PESEL
				, a.`ADM_PHONE`  -- nr. telefonu
				, a.`EMAIL` as EMAIL  -- adres email
				, a.`ADM_OTHER_INFO`
				, group_concat(', ',tx.T_TELBOX_NAME) as T_TELBOX_NEIGHBOUR_IN_ID_NAME,
				 a.L_APPOITMENT_USER , a.A_ADM_COMPANY, a.A_CLASSIFIED
			from `ADMIN_USERS` as a
			   left join CRM_AUTH_PROFILE as cp on (cp.REMOTE_TABLE='ADMIN_USERS' and cp.REMOTE_ID=a.ID)
			   left join TELBOXES as tx on (tx.ID=cp.T_TELBOX_NEIGHBOUR_IN_ID)
			where {$sql_where}
			group by a.ID
			{$sql_order_by}
			{$sql_limit}
		", 'ID'));
	}

	public static function get_user_by_id( $id ) {
		if ($id <= 0) return null;

		return DB::getPDO()->fetchFirstAsObject("
			SELECT a.`ID`
				, a.`A_STATUS`
				, a.`ADM_ACCOUNT`
				, a.`ADM_ADMIN_LEVEL`
				, a.`ADM_ADMIN_DESC`  -- stanowisko
				, a.`ADM_NAME`  -- imie i nazwisko
				, a.`ADM_NIP`  -- NIP
				, a.`ADM_PESEL`  -- nr. PESEL
				, a.`ADM_PHONE`  -- nr. telefonu
				, a.`EMAIL` as EMAIL  -- adres email
				, tx.T_TELBOX_NAME
			from `ADMIN_USERS` as a
				left join CRM_AUTH_PROFILE as cp on (cp.REMOTE_TABLE='ADMIN_USERS' and cp.REMOTE_ID=a.ID)
				left join TELBOXES as tx on (tx.ID=cp.T_TELBOX_NEIGHBOUR_IN_ID)
			where a.`ID` = :id
		", [
			':id' => $id,
		]);
	}

	public static function getUserByEmail($email) {
		if (empty($email)) return null;

		return DB::getPDO()->fetchFirstAsObject("
			SELECT a.`ID`
				, a.`A_STATUS`
				, a.`ADM_ACCOUNT`
				, a.`ADM_ADMIN_LEVEL`
				, a.`ADM_ADMIN_DESC`  -- stanowisko
				, a.`ADM_NAME`  -- imie i nazwisko
				, a.`ADM_NIP`  -- NIP
				, a.`ADM_PESEL`  -- nr. PESEL
				, a.`ADM_PHONE`  -- nr. telefonu
				, a.`EMAIL`  -- adres email
				, a.`EMPLOYEE_TYPE`  -- EMPLOYEE_TYPE (ldap:employeeType)
				, a.`A_SYNC_LDAP_DATE`
			from `ADMIN_USERS` as a
			where a.`EMAIL` = :email
		", [
			':email' => $email,
		]);
	}

	public static function getUserByName($userName) {
		if (empty($userName)) return null;

		return DB::getPDO()->fetchFirstAsObject("
			SELECT a.`ID`
				, a.`A_STATUS`
				, a.`ADM_ACCOUNT`
				, a.`ADM_ADMIN_LEVEL`
				, a.`ADM_ADMIN_DESC`  -- stanowisko
				, a.`ADM_NAME`  -- imie i nazwisko
				, a.`ADM_NIP`  -- NIP
				, a.`ADM_PESEL`  -- nr. PESEL
				, a.`ADM_PHONE`  -- nr. telefonu
				, a.`EMAIL`  -- adres email
				, a.`EMPLOYEE_TYPE`  -- EMPLOYEE_TYPE (ldap:employeeType)
				, a.`A_SYNC_LDAP_DATE`
			from `ADMIN_USERS` as a
			where a.`ADM_ACCOUNT` = :name
		", [
			':name' => $userName,
		]);
	}

	public static function getUsersByGroupId($zasobId) {
		if (empty($zasobId)) return null;

		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey("
			select u.`ID`
				, u.`A_STATUS`
				, u.`ADM_ACCOUNT`
				, u.`ADM_ADMIN_LEVEL`
				, u.`ADM_ADMIN_DESC`  -- stanowisko
				, u.`ADM_NAME`  -- imie i nazwisko
				, u.`ADM_NIP`  -- NIP
				, u.`ADM_PESEL`  -- nr. PESEL
				, u.`ADM_PHONE`  -- nr. telefonu
				, u.`EMAIL`  -- adres email
				, u.`EMPLOYEE_TYPE`  -- EMPLOYEE_TYPE (ldap:employeeType)
				, u.`A_SYNC_LDAP_DATE`
			from `CRM_AUTH_PROFILE` as up
				left join `ADMIN_USERS` as u on (u.`ID`=up.`REMOTE_ID`)
			where
				up.`ID_ZASOB` = :id
				and up.`REMOTE_TABLE`='ADMIN_USERS'
				and up.`A_STATUS` in('WAITING', 'NORMAL')
				and u.`A_STATUS` in('WAITING', 'NORMAL')
			group by u.`ID`
		", 'ID', [
			':id' => $zasobId,
		]));
	}

	public static function getUsersByGroupsIds($zasobyIds, $ignoreUsrIds = array()) {
		if (empty($zasobyIds)) return null;
		$users = array();

		$sqlIds = " and up.`ID_ZASOB` in(" . implode(",", $zasobyIds) . ")";
		$sqlIgnore = "";
		if (!empty($ignoreUsrIds)) {
			$sqlIgnore = " and u.`ID` not in(" . implode(",", $ignoreUsrIds) . ")";
		}

		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey("
			select u.`ID`
				, u.`A_STATUS`
				, u.`ADM_ACCOUNT`
				, u.`ADM_ADMIN_LEVEL`
				, u.`ADM_ADMIN_DESC`  -- stanowisko
				, u.`ADM_NAME`  -- imie i nazwisko
				, u.`ADM_NIP`  -- NIP
				, u.`ADM_PESEL`  -- nr. PESEL
				, u.`ADM_PHONE`  -- nr. telefonu
				, u.`EMAIL`  -- adres email
				, u.`EMPLOYEE_TYPE`  -- EMPLOYEE_TYPE (ldap:employeeType)
				, u.`A_SYNC_LDAP_DATE`
			from `CRM_AUTH_PROFILE` as up
				left join `ADMIN_USERS` as u on (u.`ID`=up.`REMOTE_ID`)
			where up.`REMOTE_TABLE`='ADMIN_USERS'
				and up.`A_STATUS` in('WAITING', 'NORMAL')
				and u.`A_STATUS` in('WAITING', 'NORMAL')
				{$sqlIds}
				{$sqlIgnore}
			group by u.`ID`
		", 'ID'));
	}

	public static function get_group_list() {
		static $_groups;
		if (!$_groups) {
			$_groups = array_map(function ($item) {
				return (object)$item;
			}, DB::getPDO()->fetchAllByKey("
				select z.`ID`, z.`DESC`, z.`OPIS`
				from `CRM_LISTA_ZASOBOW` as z
				where
					z.`TYPE` in ( 'STANOWISKO', 'PODMIOT' )
				order by z.`DESC`
			", 'ID'));
		}
		return $_groups;
	}

	public static function get_localisation_list() {
		static $_groups;
		if (!$_groups) {
			$_groups = array_map(function ($item) {
				return (object)$item;
			}, DB::getPDO()->fetchAllByKey("
				select tx.`ID`, tx.`T_TELBOX_NAME`, tx.`T_TELBOX_TYPE`
				from `TELBOXES` as tx
				where
					tx.`A_STATUS` != 'DELETED'
				order by tx.`T_TELBOX_NAME`
			", 'ID'));
		}
		return $_groups;
	}

	public static function add_groups_to_user_list(&$items) {
		if (empty($items)) return;
		$user_list_id = array();
		foreach ($items as $k_ind => $v_item) {
			$items[$k_ind]->groups = [];
			$user_list_id[] = "'{$v_item->ID}'";
		}
		if (empty($user_list_id)) return;

		$listGroups = DB::getPDO()->fetchAll("
			select z.`ID`, z.`DESC`, z.`OPIS`
				, up.`REMOTE_ID`
			from `CRM_AUTH_PROFILE` as up
				left join `CRM_LISTA_ZASOBOW` as z on ( z.`ID` = up.`ID_ZASOB` )
			where
				up.`REMOTE_ID` in (" . implode(",", $user_list_id) . ")
				and up.`A_STATUS` in ( 'WAITING', 'NORMAL' )
				and up.`REMOTE_TABLE` = 'ADMIN_USERS'
				and z.`ID` is not null
				and z.`TYPE` in ( 'STANOWISKO', 'PODMIOT' )
		");
		foreach ($listGroups as $group) {
			$items[$group['REMOTE_ID']]->groups[$group['ID']] = $group['DESC'];//array('DESC'=>$r->DESC , 'OPIS'=>$r->OPIS);
		}
	}

	public static function get_group_by_user($userID, $params = array()) {
		return self::getGroupByUser($userID, $params);
	}

	public static function getGroupByUserName($userName, $params = array()) {
		$userID = DB::getPDO()->fetchValue("
			select u.`ID`
			from `ADMIN_USERS` as u
			where u.`ADM_ACCOUNT` = '{$userName}'
		");
		if ($userID > 0) {
			return self::getGroupByUser($userID, $params);
		}
		return null;
	}

	public static function getGroupByUser($userID, $params = array()) {
		$sql_select = array();
		$sql_left_join = "";

		$sql_select[] = "z.`ID`";
		$sql_select[] = "z.`DESC`";
		$sql_select[] = "z.`OPIS`";
		$sql_select[] = "z.`A_LDAP_GID`";

		$telbox = V::get('T_TELBOX_NAME', 0, $params, 'int');
		$SHOW_IN_PERIOD_MARK = V::get('SHOW_IN_PERIOD_MARK', 0, $params, 'string');

		if ($telbox > 0) {
			$sql_left_join = "left join `TELBOXES` as tx on(tx.`ID`=up.`T_TELBOX_NEIGHBOUR_IN_ID`)";
			$sql_select[] = "tx.`T_TELBOX_NAME`";
		}
		$sql_select_where_and = "";
		if (!empty($SHOW_IN_PERIOD_MARK)) {
			$sql_select_where_and .= " and up.`SHOW_IN_PERIOD_MARK`='{$SHOW_IN_PERIOD_MARK}' ";
		}

		$sql_select = implode(', ', $sql_select);
		$sql = "
			select {$sql_select}
			from `CRM_AUTH_PROFILE` as up
				left join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=up.`ID_ZASOB`)
				{$sql_left_join}
			where
				up.`REMOTE_ID`='{$userID}'
				and up.`A_STATUS` in('WAITING', 'NORMAL')
				and up.`REMOTE_TABLE`='ADMIN_USERS'
				and z.`ID` is not null
				and z.`TYPE` in('STANOWISKO','PODMIOT')
				{$sql_select_where_and}
		";
		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey($sql, 'ID'));
	}

	public static function getLDAPGroupByUserName($userName) {
		return UsersLdapHelper::getUserGroups($userName);
	}

	public static function getLDAPGroupsAll() {
		return UsersLdapHelper::getGroupsAll();
	}

	public static function getGroupsAll() {
		$sql = "
			select z.`ID`, z.`TYPE`, z.`DESC`, z.`A_LDAP_GID`
			from `CRM_LISTA_ZASOBOW` as z
			where
				z.`A_STATUS` in('WAITING', 'NORMAL')
				and z.`TYPE` in('STANOWISKO','DZIAL','PODMIOT')
		";
		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey($sql, 'ID'));
	}

	public static function getGroupByID($id) {
		$sql = "
			select z.`ID`, z.`TYPE`, z.`DESC`, z.`A_LDAP_GID`
			from `CRM_LISTA_ZASOBOW` as z
			where
				z.`A_STATUS` in('WAITING', 'NORMAL')
				and z.`TYPE` in('STANOWISKO','DZIAL','PODMIOT')
				and z.ID = :id
		";
		return DB::getPDO()->fetchFirstAsObject($sql, [ ':id' => $id ]);
	}

	/**
	 * @params $ids array of integer
	 */
	public static function getGroupsByLdapGids($ldapGids) {
		if (empty($ldapGids)) {
			return false;
		}

		$sql = "
			select z.`ID`, z.`TYPE`, z.`DESC`, z.`A_LDAP_GID`
			from `CRM_LISTA_ZASOBOW` as z
			where
				z.`A_STATUS` in('WAITING', 'NORMAL')
				and z.`TYPE` in('STANOWISKO','DZIAL','PODMIOT')
				and z.`A_LDAP_GID` in(" . implode(",", $ldapGids) . ")
		";
		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey($sql, 'ID'));
	}

	public static function getUsersAll() {
		$sql_order_by = '';
		$sql_limit = '';

		$sql_where = '';
		//if (!empty($params)) {
			$sql_where_and_arr = array();
			$sql_where_and_arr[] = "a.`A_STATUS` in('WAITING','NORMAL','MONITOR','WARNING')";
			$sql_where_and_arr[] = "a.`ADM_ADMIN_DESC`!='Kandydat'";
		//} else {
		//	$sql_where = "1=1";
		//}
		if (isset($params['ADM_ADMIN_LEVEL'])) {
			$adm_lvl = V::get('ADM_ADMIN_LEVEL', 0, $params, 'int');
			$sql_where_and_arr[] = "a.`ADM_ADMIN_LEVEL`='{$adm_lvl}'";
		}
		$sql_where = implode(" and ", $sql_where_and_arr);

		$sql = "
			SELECT a.`ID`
				, a.`A_STATUS`
				, a.`ADM_ACCOUNT`
				, a.`ADM_ADMIN_LEVEL`
				, a.`ADM_ADMIN_DESC`  -- stanowisko
				, a.`ADM_NAME`  -- imie i nazwisko
				, a.`ADM_NIP`  -- NIP
				, a.`ADM_PESEL`  -- nr. PESEL
				, a.`ADM_PHONE`  -- nr. telefonu
				, a.`EMAIL` as EMAIL  -- adres email
				, a.`ADM_OTHER_INFO`
				, group_concat(', ',tx.T_TELBOX_NAME) as T_TELBOX_NEIGHBOUR_IN_ID_NAME
			from `ADMIN_USERS` as a
			   left join CRM_AUTH_PROFILE as cp on (cp.REMOTE_TABLE='ADMIN_USERS' and cp.REMOTE_ID=a.ID)
			   left join TELBOXES as tx on (tx.ID=cp.T_TELBOX_NEIGHBOUR_IN_ID)
			where {$sql_where}
			group by a.ID
			-- {$sql_order_by}
			-- {$sql_limit}
		";
		return array_map(function ($item) {
			return (object)$item;
		}, DB::getPDO()->fetchAllByKey($sql, 'ID'));
	}

	public static function getLDAPUsersAll() {
		return UsersLdapHelper::getUsersAll();
	}

	public static function getLDAPUserByName($userName, $allAttrs = false) {
		return UsersLdapHelper::getUser($userName, $allAttrs);
	}

	public static function getUserAcl($user_id) {
		static $_acl;
		if (!$_acl || !is_array($_acl)) {
			$_acl = array();
		}
		if ($user_id <= 0) {
			return false;
		}
		if (!array_key_exists($user_id, $_acl)) {
			Lib::loadClass('UserAcl');
			$_acl[$user_id] = new UserAcl($user_id);
		}
		return $_acl[$user_id];
	}

	public static function add_group($user_id, $group_id, $add_localisation) {
		if ($group_id <= 0) return -1;
		if ($user_id <= 0) return -1;
		$sqlToday = date('Y-m-d-H:i');
		$sqlUser = User::getName();
		DB::getPDO()->execSql("
			insert into `CRM_AUTH_PROFILE` (`ID_ZASOB`, `REMOTE_ID`, `REMOTE_TABLE`, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`,`T_TELBOX_NEIGHBOUR_IN_ID`)
			select `ID`, :id_user as user_id, 'ADMIN_USERS' as REMOTE_TABLE
				, :today as A_RECORD_CREATE_DATE
				, :name as A_RECORD_CREATE_AUTHOR , :add_localisation as T_TELBOX_NEIGHBOUR_IN_ID
			from `CRM_LISTA_ZASOBOW`
			where `ID` = :id_group and `TYPE` in('STANOWISKO','PODMIOT')
		", [
			':id_user' => $user_id,
			':id_group' => $group_id,
			':today' => $sqlToday,
			':name' => $sqlUser,
			':add_localisation' => $add_localisation,
		]);

		return DB::getPDO()->lastInsertId();
	}

	public static function remove_group($user_id, $group_id) {
		if ($group_id <= 0) return -1;
		if ($user_id <= 0) return -1;

		$record = DB::getPDO()->fetchFirst("
			select `ID`
			from `CRM_AUTH_PROFILE`
			where `ID_ZASOB` = :id_group
				and `REMOTE_ID` = :id_user
				and `REMOTE_TABLE` = 'ADMIN_USERS'
		", [
			':id_group' => $group_id,
			':id_user' => $user_id,
		]);
		if (!$record) return -1;

		$tableName = 'CRM_AUTH_PROFILE';

		$toUpdate = [
			'A_STATUS' => 'DELETED',
			'A_RECORD_UPDATE_AUTHOR' => User::getLogin(),
			'A_RECORD_UPDATE_DATE' => 'NOW()',
		];
		$affectedRows = DB::getPDO($dbID)->update($tableName, 'ID', $record['ID'], $toUpdate);
		DB::getPDO($dbID)->insert("{$tableName}_HIST", array_merge($toUpdate, [
			'ID_USERS2' => $record['ID'],
		]));

		if ($affectedRows > 0) {
			DB::getPDO()->execSql(" delete from `CRM_AUTH_PROFILE` where `ID` = :id limit 1; ", [ ':id' => $record['ID'] ]);
		}
		return $affectedRows;
	}

	public static function getGroupsTree() {
		self::_fetchGroupsTreeAll();
	}

	public function getGroupsTreeRoot() {
		$dataAll = self::_fetchGroupsTreeAll();
		return $dataAll['treeRoot'];
	}

	public function getGroupsTreeItems() {
		$dataAll = self::_fetchGroupsTreeAll();
		return $dataAll['items'];
	}

	public static function _fetchGroupsTreeAll() { // TODO: fix performance
		static $dataAll;
		if (is_array($dataAll)) {
			return $dataAll;
		}

		$dataAll = array();
		$filterTreeTypes = array('STANOWISKO','DZIAL','PODMIOT');
		// tree flat
		$dataAll['items'] = array();
		$treeZasoby = array();
		$sql = "
			select z.`ID`, z.`PARENT_ID`, z.`TYPE`, z.`DESC`, z.`A_LDAP_GID`
			from `CRM_LISTA_ZASOBOW` as z
			where
				z.`A_STATUS` in('WAITING', 'NORMAL')
		";
		$listItems = DB::getPDO()->fetchAll($sql);
		foreach ($listItems as $item) {
			$r = (object)$item;
			$treeZasoby[$r->ID] = $r->PARENT_ID;
			if (in_array($r->TYPE, $filterTreeTypes)) {
				$r->sub = array();
				$dataAll['items'][$r->ID] = $r;
			}
		}
		// find parent rec
		$dataAll['treeRoot'] = array();
		foreach ($dataAll['items'] as $kID => $vGroup) {
			$pID = $vGroup->PARENT_ID;
			$isSub = false;
			for ($i = 0, $limit = 100; $i < $limit; $i++) {
				if (array_key_exists($pID, $dataAll['items'])) {
					$dataAll['items'][$pID]->sub[] = $kID;
					//unset($dataAll['items'][$kID]);
					$isSub = true;
					break;
				}
				$pID = $treeZasoby[$pID];
			}
			// not found = root
			if (!$isSub) {
				$dataAll['treeRoot'][] = $kID;
			}
		}
		return $dataAll;
	}

}