1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
| $aBind[":SITE_ID"]=$this->params[0];
$aBind[":LANGUE_ID"]=$this->params[1];
$sVersion=($this->params[2]?$this->params[2]:"CURRENT");
$aBind[":CONTENT_HIERARCHIE_ID"]=$this->params[3];
$aBind[":DAY"] = date("d/m/Y");
$aBind[":CONTENT_TYPE_ID"]=$_CONST["CNT_TYPE_NC_RENDEZ_VOUS"];
$sSQL="
SELECT
cv.CONTENT_ID AS id,
DECODE(cv.CONTENT_TITLE_BO,NULL,cv.CONTENT_TITLE,cv.CONTENT_TITLE_BO) AS lib,
CONTENT_TEXT AS TITRE_LONG,
CONTENT_START_DATE,
CONTENT_END_DATE,
cv.CONTENT_HIERARCHIE_ID,
CONTENT_CODE_OPERATION,
DECODE(TO_DATE(CONTENT_DIFFUSION_DATE,'DD/MM/YY') - TRUNC(SYSDATE), -1, 'Hier', 0, 'Aujourd''hui', 1, 'Demain', CONTENT_DIFFUSION_DATE) as DIFF_DATE,
CONTENT_SUBTITLE as LIEU,
CONTENT_URL as URL,
CONTENT_SUBTITLE_2 as DIFF_HEURE,
--TO_DATE(CONTENT_DIFFUSION_DATE||' '||CONTENT_SHORT_TEXT, 'DD/MM/YYYY HH24:MI') as TRI,
mcv.MEDIA_ID AS MEDIA_ID,
MEDIA_PATH
FROM
CNL_CONTENT c
INNER JOIN CNL_CONTENT_VERSION cv
ON (
c.CONTENT_ID = cv.CONTENT_ID
AND cv.CONTENT_VERSION = c.CONTENT_".$sVersion."_VERSION
AND cv.LANGUE_ID= :LANGUE_ID
AND CONTENT_START_DATE <= TRUNC(SYSDATE)
AND CONTENT_END_DATE >= TRUNC(SYSDATE)
AND TO_DATE(cv.CONTENT_DIFFUSION_DATE || cv.CONTENT_SUBTITLE_2, 'DD/MM/YYHH24:MI') >= TRUNC(SYSDATE-(1/24), 'HH24')
)
LEFT JOIN CNL_MEDIA_CONTENT_VERSION mcv
ON (mcv.CONTENT_ID=cv.CONTENT_ID
AND mcv.CONTENT_VERSION=cv.CONTENT_VERSION)
LEFT JOIN CNL_MEDIA m
ON (m.MEDIA_ID=mcv.MEDIA_ID)
JOIN CNL_CONTENT_HIERARCHIE ch
ON ( ch.CONTENT_HIERARCHIE_ID = cv.CONTENT_HIERARCHIE_ID )
WHERE
c.SITE_ID= :SITE_ID
AND c.CONTENT_TYPE_ID = :CONTENT_TYPE_ID
";
if($aBind[":CONTENT_HIERARCHIE_ID"]){
$sSQL.="
AND (
cv.CONTENT_HIERARCHIE_ID in(".$aBind[":CONTENT_HIERARCHIE_ID"].")
OR
ch.CONTENT_PARENT_HIERARCHIE_ID in(".$aBind[":CONTENT_HIERARCHIE_ID"].")
)
";
}
$sSQL.="ORDER BY CONTENT_DIFFUSION_DATE, CONTENT_SUBTITLE_2
"; |
Partager