/**
* @param array $uids
*
* @param array $ftrTypes
* @return array
*/
public function findFeatureTypesByUids(array $uids, array $ftrTypes)
{
$sql = '
SELECT
USR_ID "' . FeatureTypeModel::FIELD_UID . '",
FTR_TP "' . FeatureTypeModel::FIELD_TYPE . '",
UFT_TEXT "' . FeatureTypeModel::FIELD_TEXT . '",
UFT_NUMBER "' . FeatureTypeModel::FIELD_NUMBER . '",
UFT_START_DATE "' . FeatureTypeModel::FIELD_START_DATE . '",
UFT_END_DATE "' . FeatureTypeModel::FIELD_END_DATE . '",
UFT_TIMESTAMP "' . FeatureTypeModel::FIELD_TIMESTAMP . '"
FROM videkomaster.USER_FEATURES uf
WHERE uf.USR_ID IN (SELECT COLUMN_VALUE FROM TABLE (:USRIDS))
AND uf.FTR_TP IN (SELECT COLUMN_VALUE FROM TABLE (:FTRTPS))
';
$query = $this->oracle->query($sql)->bindArrayInt('USRIDS', $uids)->bindArrayInt('FTRTPS', $ftrTypes);
$result = $query->fetchTree(2);
$result = array_map(function ($item) {
return array_map(function ($item) {
unset($item['USR_ID']);
unset($item['FTR_TP']);
return $item;
}, $item);
}, $result);
return $result;
}
/**
* @param $login
* @param $password
*
* @param array $authScope
* @return array
*/
public function findUserByCredentials($login, $password)
{
$sql = '
SELECT DISTINCT
um.USR_ID,
cm.CPM_NAME,
cm.CPM_EMAIL_TECH,
cm.CPM_IP_RANGES,
cm.CPM_FLAGS,
um.USR_LANGUAGE_CODE,
um.LCK_TP_HOST
FROM videkomaster.USER_MASTER um
LEFT JOIN
videkomaster.CONTPARTNER_MASTER cm
ON cm.USR_ID = um.USR_ID
WHERE cm.CPM_NAME = :LOGIN
AND um.USR_TYPE = 10
AND cm.CPM_PASSWORD IS NOT NULL
AND ( (cm.CPM_PASSWORD = :PASSWORD) OR (cm.CPM_PASSWORD = lower(videkomaster.F_MD5 (:PASSWORD))) )
';
$query = $this->oracle->query($sql)->bindString('LOGIN', $login)->bindString('PASSWORD', $password);
$result = $query->fetchRow();
return $result;
}
/**
* @return bool
*/
public function getOracleIntegrationTest()
{
$sql = 'SELECT DISTINCT * FROM VIDEKOMASTER.USER_MASTER UMA WHERE UMA.USR_ID = :USR_ID ';
$query = $this->oracle->query($sql)->bindString('USR_ID', CampointUidsEnum::AK);
$result = $query->fetchRow();
return $result['USR_LOGIN'] === 'akentner-TX';
}
/**
* @param array $uids
*
* @return array
*/
public function findOptionsByIds(array $uids)
{
$sql = '
SELECT uo.USR_ID "uid", uo.OPT_TP "optTp", uo.UOT_STATUS "value"
FROM videkomaster.USER_OPTIONS uo
WHERE UO.USR_ID IN (SELECT COLUMN_VALUE FROM TABLE (:USRIDS))
AND UO.OPT_TP IN (SELECT COLUMN_VALUE FROM TABLE (:OPTTPS))
';
$query = $this->oracle->query($sql)->bindArrayInt('USRIDS', $uids)->bindArrayInt('OPTTPS', array_keys(OptionTypeEnum::$fieldMapping));
$result = $query->fetchTree(2);
return $result;
}
/**
* @param $login
* @param $isMasterAccount
* @param $remoteAddress
* @return null|Model\Session
*/
private function recoverSession($login, $isMasterAccount, $remoteAddress)
{
$sql = '
SELECT
ses.USR_ID as "uid",
ses.SES_MNUMBER as "sid",
ses.SES_FLAGS as "flags",
um.USR_LOGIN as "login"
FROM VIDEKOMASTER.SESSIONS ses
INNER JOIN VIDEKOMASTER.USER_MASTER um on (um.USR_ID = ses.USR_ID)
WHERE um.USR_LOGIN = :LOGIN
AND ses.SES_IP = :REMOTE_ADDRESS
AND ses.SET_TP = 200
AND ses.SES_FLAGS = :IS_MASTER_ACCOUNT
';
$query = $this->oracle->query($sql)->bindString('LOGIN', $login)->bindString('REMOTE_ADDRESS', $remoteAddress)->bindInt('IS_MASTER_ACCOUNT', (int) $isMasterAccount);
$result = $query->fetchRow();
if (!$result) {
return null;
}
$session = new Model\Session();
$result['uid'] = (int) $result['uid'];
$result['flags'] = (int) $result['flags'];
ObjectUtil::hydrateObject($session, $result);
return $session;
}
/**
* @param $login
* @param $password
*
* @param array $authScope
* @return array
*/
public function findUserByCredentials($login, $password, array $authScope = [])
{
if (!$authScope) {
$authScope = $this->defaultAuthScope;
}
$sql = '
SELECT DISTINCT
um.USR_ID,
um.USR_LOGIN,
um.USR_PWD,
um.USR_MASTERPWD,
ua.URA_EMAIL,
um.USR_LANGUAGE_CODE,
CASE
WHEN um.USR_MASTERPWD = :PASSWORD THEN 1
WHEN um.USR_MASTERPWD = videkomaster.F_MD5 (:PASSWORD) THEN 1
ELSE 0
END IS_MASTER,
CASE
WHEN um.USR_TYPE = 0 THEN um.LCK_TP_GUEST
WHEN um.USR_TYPE = 1 THEN um.LCK_TP_HOST
WHEN um.USR_TYPE = 13 THEN um.LCK_TP_GUEST
ELSE -1
END LOCK_TYPE,
um.USR_TYPE
FROM videkomaster.USER_MASTER um
LEFT JOIN
videkomaster.USER_ADDRESS ua
ON ua.ADD_TP = 1 AND ua.USR_ID = um.USR_ID
WHERE um.USR_LOGIN = :LOGIN
AND USR_PWD IS NOT NULL
AND ( (USR_PWD = :PASSWORD)
OR (USR_MASTERPWD = :PASSWORD)
OR (USR_PWD = videkomaster.F_MD5 (:PASSWORD))
OR (USR_MASTERPWD = videkomaster.F_MD5 (:PASSWORD)))
';
if ($authScope != [UserTypeEnum::USER]) {
$sql .= 'AND USR_TYPE IN ( SELECT COLUMN_VALUE FROM TABLE (:USRTYPE) )';
}
$query = $this->oracle->query($sql)->bindString('LOGIN', $login)->bindString('PASSWORD', $password);
if ($authScope) {
$authScopeInt = array_map('\\VXBase\\User\\Model\\UserTypeEnum::getDbMapping', $authScope);
$query->bindArrayInt('USRTYPE', $authScopeInt);
}
$result = $query->fetchRow();
return $result;
}
/**
* @param $remoteAddress
*
* @return null|array
*/
private function findSessionInOracleByRemoteAddress($remoteAddress)
{
$sql = '
SELECT
SES.USR_ID "userId",
SES.SES_MNUMBER "sid",
UM.USR_LOGIN "userScreenname",
SES.SES_END + ST.SET_EXPIRE_TIME/86400 "validUntil"
FROM videkomaster.SESSIONS ses
LEFT JOIN videkomaster.SESSION_TYPE ST ON ST.SET_TP = SES.SET_TP
LEFT JOIN videkomaster.USER_MASTER UM ON UM.USR_ID = SES.USR_ID
WHERE SES.SES_IP = :REMOTE_ADDRESS AND SES.SET_TP = :SID_SESSION_TYPE
';
$query = $this->oracle->query($sql)->bindString('REMOTE_ADDRESS', $remoteAddress)->bindInt('SID_SESSION_TYPE', OracleSessionTypeEnum::HOST);
$result = $query->fetchAll();
return $result;
}
/**
* @param $sid
* @return array|null
*/
public function findHistorySessionInOracle($sid)
{
$sql = '
SELECT DISTINCT
SES.SES_MNUMBER "sid",
SES.USR_ID "uid",
SES.SES_IP "remoteAddress",
SES.SES_START "validFrom",
SES.SES_END "validUntil",
SES.SES_UAGENT "userAgent"
FROM videkomaster.SESSIONS_VXTOOL ses
WHERE SES.SES_MNUMBER = :SID
';
$query = $this->oracle->query($sql)->bindString('SID', $sid);
$result = $query->fetchRow();
return $result;
}
/**
* @param $hostUid
* @return array|null
*/
public function findFtpUserByHostUid($hostUid)
{
$sql = '
SELECT um.USR_ID AS "hostUid",
UM.USR_LOGIN AS "login",
UF.UFT_TEXT AS "password",
UF.UFT_START_DATE AS "created",
UF.UFT_TIMESTAMP AS "modified"
FROM VIDEKOMASTER.USER_MASTER um
INNER JOIN
VIDEKOMASTER.USER_FEATURES uf
ON UF.USR_ID = UM.USR_ID
AND UF.FTR_TP = 405
WHERE UM.USR_ID = :USRID
';
$query = $this->oracle->query($sql)->bindString('USRID', (int) $hostUid);
$result = $query->fetchRow();
return $result;
}
