/**
* @param $hostUid
* @return array|null
*/
public function findVideoAmateurPictures($hostUid)
{
$data = ['hostUid' => (int) $hostUid, 'pictures' => []];
foreach (VideoAmateurPictureEnum::$all as $pictureType) {
$sql = '
SELECT
uma.USR_ID "hostUid",
uma.UMA_ID "umaId",
ump.UMP_ID "umpId",
ump.UMP_FLAGS "flags",
ump.UMP_HEIGHT "height",
ump.UMP_WIDTH "width",
ump.UMP_FSK "fsk"
FROM videkomaster.user_media uma
INNER JOIN videkomaster.user_media_picture ump
ON uma.uma_id = ump.uma_id
AND ump.ump_status = 1
AND BITAND (ump.UMP_FLAGS, 8) = 8
WHERE uma.usr_id = :HOST_UID
';
$sql .= VideoAmateurPictureEnum::getSqlWhere($pictureType);
$query = $this->oracle->query($sql)->bindInt('HOST_UID', (int) $hostUid);
$result = $query->fetchAll();
foreach ($result as $idx => $row) {
$data['pictures'][$pictureType][$idx]['pictureId'] = (int) $row['umaId'];
$data['pictures'][$pictureType][$idx]['pictureSubId'] = (int) $row['umpId'];
$data['pictures'][$pictureType][$idx]['flags'] = FlagsEnum::resolveFlagNames((int) $row['flags'] & FlagsEnum::getContentPartnerFlags());
$data['pictures'][$pictureType][$idx]['height'] = (int) $row['height'];
$data['pictures'][$pictureType][$idx]['width'] = (int) $row['width'];
$data['pictures'][$pictureType][$idx]['fskAge'] = (int) $row['fsk'];
$data['pictures'][$pictureType][$idx]['fskNames'] = PictureRatingAgeEnum::resolveNamesByAge($row['fsk']);
}
}
return $data;
}
/**
* @param $hostUid
* @param $md5sum
* @return array
*/
public function findVideoByHostUidMd5Sum($hostUid, $md5sum)
{
$sql = '
SELECT *
FROM VIDEKOMASTER.USER_MEDIA_VIDEO umv
LEFT JOIN VIDEKOMASTER.USER_MEDIA um ON UM.UMA_ID = UMV.UMA_ID
WHERE UM.USR_ID = :HOSTUID AND UMV.UMV_HASH = :MD5SUM
';
$query = $this->oracle->query($sql)->bindInt('HOSTUID', (int) $hostUid)->bindString('MD5SUM', $md5sum);
$result = $query->fetchAll();
return $result;
}
/**
* @param int $uid
* @param int $xrefType
*
* @return array
*/
protected function findUserIdsXrefById($uid, $xrefType = null)
{
$sql = '
SELECT USR_ID FROM (
SELECT
USER_ID_02 USR_ID,
XRF_STARTDATE
FROM
videkomaster.USER_MASTER_XREF xref
WHERE
xref.USER_ID_01 = :USERID
' . (!is_null($xrefType) ? 'AND xref.XRF_TYPE = :XREF_TYPE' : '') . '
)
ORDER BY XRF_STARTDATE DESC
';
$query = $this->oracle->query($sql);
$query->bindInt('USERID', (int) $uid);
if (!is_null($xrefType)) {
$query->bindInt('XREF_TYPE', $xrefType);
}
$result = $query->fetchColumn();
return $result;
}
