/** * @param $uid * * @return array|null */ public function findRealPersonById($uid) { $sql = ' SELECT um.USR_ID, um.USR_LOGIN, um.USR_TYPE, LISTAGG (xref.USER_ID_02, \',\') WITHIN GROUP (ORDER BY xref.USER_ID_02) "hostUids" FROM videkomaster.USER_MASTER um LEFT JOIN videkomaster.USER_MASTER_XREF xref ON (xref.USER_ID_01 = um.USR_ID) AND (xref.XRF_TYPE = 1) LEFT OUTER JOIN videkomaster.SESSIONS ses ON (ses.USR_ID = um.USR_ID) AND (ses.SET_TP IN (1, 2, 3)) WHERE um.USR_ID = :USRID AND um.USR_TYPE = :USRTYPE GROUP BY um.USR_ID, um.USR_LOGIN, um.USR_TYPE, xref.USER_ID_01 '; $result = $this->oracle->query($sql)->bindInt('USRID', (int) $uid)->bindInt('USRTYPE', UserTypeEnum::getDbMapping(UserTypeEnum::REALPERSON))->fetchRow(); return $result; }
/** * @param array $uids * @return array */ public function findHostByLogin($login) { $sql = ' SELECT um.USR_ID, um.USR_LOGIN, um.USR_TYPE, um.USR_PWD, um.USR_MASTERPWD, ses.SET_TP ONLINE_STATE FROM videkomaster.USER_MASTER um LEFT OUTER JOIN videkomaster.SESSIONS ses on (ses.USR_ID = um.USR_ID) WHERE um.USR_LOGIN = :LOGIN AND USR_TYPE = :USRTYPE '; $userType = UserTypeEnum::getDbMapping(UserTypeEnum::HOST); $query = $this->oracle->query($sql)->bindString('LOGIN', $login)->bindInt('USRTYPE', $userType); $result = $query->fetchRow(); return $result; }
/** * @param $login * @param array|string $userTypeScope * @internal param array $uids * @return array */ protected function findUserByLogin($login, $userTypeScope = []) { if (!$userTypeScope) { $userTypeScope = $this->defaultUserTypeScope; } $sql = ' SELECT um.USR_ID, um.USR_LOGIN, um.USR_PWD, um.USR_MASTERPWD, um.USR_TYPE, ses.SET_TP ONLINE_STATE FROM videkomaster.USER_MASTER um LEFT OUTER JOIN videkomaster.SESSIONS ses on (ses.USR_ID = um.USR_ID) WHERE um.LOGIN = :LOGIN '; if ($userTypeScope != [UserTypeEnum::USER]) { $sql .= 'AND USR_TYPE IN ( SELECT COLUMN_VALUE FROM TABLE (:USRTYPE) )'; } $query = $this->oracle->query($sql)->bindString('LOGIN', $login); if ($userTypeScope != [UserTypeEnum::USER]) { $query->bindArrayInt('USRTYPE', UserTypeEnum::getDbMapping(UserTypeEnum::HOST)); } $result = $query->fetchAll(); return $result; }