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
"; |
