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()) {
		$total = 0;
		$db = DB::getDB();
		$sql_where = UsersHelper::_parse_query($params);
		$sql = "select count(1) as cnt
			from `ADMIN_USERS` as a
			where {$sql_where}
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$total = $r->cnt;
		}
		return $total;
	}

	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'
		$db = DB::getDB();
		$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,
				 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}
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$ret[$r->ID] = $r;
		}
		return $ret;
	}

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

		$sql_where = "a.`ID`='{$id}'";
		$db = DB::getDB();
		$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
				, 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 {$sql_where}
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$ret = $r;
		}
		return $ret;
	}

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

		$db = DB::getDB();
		$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`  -- adres email
				, a.`EMPLOYEE_TYPE`  -- EMPLOYEE_TYPE (ldap:employeeType)
				, a.`A_SYNC_LDAP_DATE`
			from `ADMIN_USERS` as a
			where a.`EMAIL`='{$email}'
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$usr = $r;
		}
		return $usr;
	}

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

		$db = DB::getDB();
		$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`  -- adres email
				, a.`EMPLOYEE_TYPE`  -- EMPLOYEE_TYPE (ldap:employeeType)
				, a.`A_SYNC_LDAP_DATE`
			from `ADMIN_USERS` as a
			where a.`ADM_ACCOUNT`='{$userName}'
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$user = $r;
		}
		return $user;
	}

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

		$db = DB::getDB();
		$sql = "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`='{$zasobId}'
				and up.`REMOTE_TABLE`='ADMIN_USERS'
				and up.`A_STATUS` in('WAITING', 'NORMAL')
				and u.`A_STATUS` in('WAITING', 'NORMAL')
			group by u.`ID`
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$users[$r->ID] = $r;
		}
		return $users;
	}

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

		$db = DB::getDB();
		$sqlIds = " and up.`ID_ZASOB` in(" . implode(",", $zasobyIds) . ")";
		$sqlIgnore = "";
		if (!empty($ignoreUsrIds)) {
			$sqlIgnore = " and u.`ID` not in(" . implode(",", $ignoreUsrIds) . ")";
		}
		$sql = "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`
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$users[$r->ID] = $r;
		}
		return $users;
	}

	public static function get_group_list() {
		static $_groups;
		if (!$_groups) {
			$_groups = array();
			$db = DB::getDB();
			$sql = "select z.`ID`, z.`DESC`, z.`OPIS`
				from `CRM_LISTA_ZASOBOW` as z
				where
					z.`TYPE` in('STANOWISKO','PODMIOT')
					order by z.`DESC`
			";
			$res = $db->query($sql);
			while ($r = $db->fetch($res)) {
				$_groups[$r->ID] = $r;
			}
		}
		return $_groups;
	}

	public static function get_localisation_list() {
		static $_groups;
		if (!$_groups) {
			$_groups = array();
			$db = DB::getDB();
			$sql = "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`
			";
			$res = $db->query($sql);
			while ($r = $db->fetch($res)) {
				$_groups[$r->ID] = $r;
			}
		}
		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 = array();
			$user_list_id[] = "'{$v_item->ID}'";
		}
		if (empty($user_list_id)) return;
		$db = DB::getDB();
		$sql = "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')
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$items[$r->REMOTE_ID]->groups[$r->ID] = $r->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()) {
		$db = DB::getDB();
		$userID = 0;
		$sql = "select u.`ID`
			from `ADMIN_USERS` as u
			where u.`ADM_ACCOUNT`='{$userName}'
		";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$userID = $r->ID;
		}
		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 ($row) {
			return (object)$row;
		}, 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() {
		$allGroups = array();

		$db = DB::getDB();
		$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')
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$allGroups[$r->ID] = $r;
		}
		return $allGroups;
	}

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

		$db = DB::getDB();
		$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) . ")
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$groups[$r->ID] = $r;
		}
		return $groups;
	}

	public static function getUsersAll() {
		$allGroups = array();

		$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);

		$db = DB::getDB();
		$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}
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$allGroups[$r->ID] = $r;
		}
		return $allGroups;
	}

	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;
		$db = DB::getDB();
		$sqlToday = date('Y-m-d-H:i');
		$sqlUser = User::getName();
		$sql = "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`, '{$user_id}' as user_id, 'ADMIN_USERS' as REMOTE_TABLE
				, '{$sqlToday}' as A_RECORD_CREATE_DATE
				, '{$sqlUser}' as A_RECORD_CREATE_AUTHOR , '{$add_localisation}' as T_TELBOX_NEIGHBOUR_IN_ID
			from `CRM_LISTA_ZASOBOW`
			where `ID`='{$group_id}' and `TYPE` in('STANOWISKO','PODMIOT')
		";
		$db->query($sql);
		$ret_id = $db->insert_id();
		return $ret_id;
	}

	public static function remove_group($user_id, $group_id) {
		if ($group_id <= 0) return -1;
		if ($user_id <= 0) return -1;
		$db = DB::getDB();
		$sql = "select `ID` from `CRM_AUTH_PROFILE` where `ID_ZASOB`='{$group_id}' and `REMOTE_ID`='{$user_id}' and `REMOTE_TABLE`='ADMIN_USERS' ";
		$res = $db->query($sql);
		if ($r = $db->fetch($res)) {
			$table = 'CRM_AUTH_PROFILE';
			$sql_obj = new stdClass();
			$sql_obj->ID = $r->ID;
			$sql_obj->A_STATUS = 'DELETED';
			$ret = $db->UPDATE_OBJ($table, $sql_obj);
			if ($ret > 0) {
				$sql = "delete from `CRM_AUTH_PROFILE` where `ID`='{$r->ID}' limit 1;";
				$db->query($sql);
			}
			return $ret;
		} else {
			return -1;
		}
	}

	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() {
		static $dataAll;
		if (is_array($dataAll)) {
			return $dataAll;
		}

		$dataAll = array();
		$filterTreeTypes = array('STANOWISKO','DZIAL','PODMIOT');
		// tree flat
		$db = DB::getDB();
		$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')
		";
		$res = $db->query($sql);
		$dataAll['items'] = array();
		$treeZasoby = array();
		while ($r = $db->fetch($res)) {
			$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;
	}

}