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