|
1
|
|
|
2
|
|
|
3
|
|
|
4
|
|
|
5
|
|
|
6
|
|
|
7
|
CREATE PROC [dbo].[CF_GetMainCafeListPosts]
|
|
8
|
@lang_cd varchar(20),
|
|
9
|
@menu_id int,
|
|
10
|
@keyword NVARCHAR(100),
|
|
11
|
@orderby VARCHAR(20),
|
|
12
|
@cpage INT,
|
|
13
|
@pagesize INT
|
|
14
|
AS
|
|
15
|
|
|
16
|
DECLARE
|
|
17
|
@totcnt INT;
|
|
18
|
|
|
19
|
BEGIN
|
|
20
|
|
|
21
|
SET @totcnt = 0;
|
|
22
|
|
|
23
|
with wa
|
|
24
|
as (
|
|
25
|
select
|
|
26
|
pvt.CAFE_ID, isnull(pvt.[1], 0) POSTRANK1, isnull(pvt.[2], 0) POSTRANK2, isnull(pvt.[3], 0) POSTRANK3
|
|
27
|
from (
|
|
28
|
select
|
|
29
|
pd.CAFE_ID, pd.prank, pd.POST_ID
|
|
30
|
from (
|
|
31
|
select
|
|
32
|
ROW_NUMBER() over(partition by p.cafe_id order by p.dt desc) prank,
|
|
33
|
p.cafe_id, p.post_id
|
|
34
|
from dbo.CF_POSTS p
|
|
35
|
where 1=1
|
|
36
|
and p.STAT <> 9
|
|
37
|
) pd
|
|
38
|
left outer join dbo.CF_POSTS pt on pt.CAFE_ID = pd.CAFE_ID and pt.POST_ID = pd.POST_ID
|
|
39
|
where 1=1
|
|
40
|
and pd.CAFE_ID <> ''
|
|
41
|
and pd.CAFE_ID is not null
|
|
42
|
and pd.prank in (1, 2, 3)
|
|
43
|
group by
|
|
44
|
pd.CAFE_ID, pd.prank, pd.POST_ID
|
|
45
|
) tp
|
|
46
|
pivot ( max(tp.post_id) for tp.prank in ([1], [2], [3]) ) as pvt
|
|
47
|
)
|
|
48
|
select
|
|
49
|
@totcnt = COUNT(*)
|
|
50
|
from wa
|
|
51
|
left outer join dbo.CF_CAFE_INFO cc on cc.cafe_id = wa.CAFE_ID
|
|
52
|
left outer join dbo.CF_POSTS p1 on p1.CAFE_ID = wa.CAFE_ID and p1.POST_ID = wa.POSTRANK1
|
|
53
|
left outer join dbo.CF_POSTS p2 on p2.CAFE_ID = wa.CAFE_ID and p2.POST_ID = wa.POSTRANK2
|
|
54
|
left outer join dbo.CF_POSTS p3 on p3.CAFE_ID = wa.CAFE_ID and p3.POST_ID = wa.POSTRANK3
|
|
55
|
left outer join dbo.CF_CAFE_MEMBERS c1 on c1.CAFE_SEQ = cc.SEQ
|
|
56
|
and c1.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p1.MEM_EMAIL)
|
|
57
|
left outer join dbo.CF_CAFE_MEMBERS c2 on c2.CAFE_SEQ = cc.SEQ
|
|
58
|
and c2.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p2.MEM_EMAIL)
|
|
59
|
left outer join dbo.CF_CAFE_MEMBERS c3 on c3.CAFE_SEQ = cc.SEQ
|
|
60
|
and c3.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p3.MEM_EMAIL)
|
|
61
|
where cc.STAT <> 9
|
|
62
|
and cc.IS_PUBLIC = 'Y'
|
|
63
|
and cc.CAFE_LANG = 'ko'
|
|
64
|
;
|
|
65
|
|
|
66
|
|
|
67
|
with wa
|
|
68
|
as (
|
|
69
|
select
|
|
70
|
pvt.CAFE_ID, isnull(pvt.[1], 0) POSTRANK1, isnull(pvt.[2], 0) POSTRANK2, isnull(pvt.[3], 0) POSTRANK3
|
|
71
|
from (
|
|
72
|
select
|
|
73
|
pd.CAFE_ID, pd.prank, pd.POST_ID
|
|
74
|
from (
|
|
75
|
select
|
|
76
|
ROW_NUMBER() over(partition by p.cafe_id order by p.dt desc) prank,
|
|
77
|
p.cafe_id, p.post_id
|
|
78
|
from dbo.CF_POSTS p
|
|
79
|
where 1=1
|
|
80
|
and p.STAT <> 9
|
|
81
|
) pd
|
|
82
|
left outer join dbo.CF_POSTS pt on pt.CAFE_ID = pd.CAFE_ID and pt.POST_ID = pd.POST_ID
|
|
83
|
where 1=1
|
|
84
|
and pd.CAFE_ID <> ''
|
|
85
|
and pd.CAFE_ID is not null
|
|
86
|
and pd.prank in (1, 2, 3)
|
|
87
|
group by
|
|
88
|
pd.CAFE_ID, pd.prank, pd.POST_ID
|
|
89
|
) tp
|
|
90
|
pivot ( max(tp.post_id) for tp.prank in ([1], [2], [3]) ) as pvt
|
|
91
|
)
|
|
92
|
select
|
|
93
|
wa.CAFE_ID, wa.POSTRANK1, wa.POSTRANK2, wa.POSTRANK3,
|
|
94
|
(select count(*) from dbo.CF_CAFE_MEMBERS where CAFE_SEQ = cc.SEQ) MEM_CNT,
|
|
95
|
(select wp.DT from dbo.CF_POSTS wp where wp.CAFE_ID = wa.CAFE_ID and wp.POST_ID = wa.POSTRANK1) as POSTRANK1_DT,
|
|
96
|
|
|
97
|
p1.POST_ID POST_ID1, isnull(p1.POST_TITLE, '') POST_TITLE1, p1.DT WRITE_DT1, isnull(c1.CAFE_NICK, '') CAFE_NICK1,
|
|
98
|
p2.POST_ID POST_ID2, isnull(p2.POST_TITLE, '') POST_TITLE2, p2.DT WRITE_DT2, isnull(c2.CAFE_NICK, '') CAFE_NICK2,
|
|
99
|
p3.POST_ID POST_ID3, isnull(p3.POST_TITLE, '') POST_TITLE3, p3.DT WRITE_DT3, isnull(c3.CAFE_NICK, '') CAFE_NICK3,
|
|
100
|
cc.CAFE_NAME, cc.CAFE_LANG LANG_CD, cc.REGION_CD, cc.TIMEZONE,
|
|
101
|
(select top 1 file_name from dbo.CF_CAFE_FILES where cafe_id = cc.CAFE_ID and file_type = 1 order by dt desc) as cafe_icon_file
|
|
102
|
from wa
|
|
103
|
left outer join dbo.CF_CAFE_INFO cc on cc.cafe_id = wa.CAFE_ID
|
|
104
|
left outer join dbo.CF_POSTS p1 on p1.CAFE_ID = wa.CAFE_ID and p1.POST_ID = wa.POSTRANK1
|
|
105
|
left outer join dbo.CF_POSTS p2 on p2.CAFE_ID = wa.CAFE_ID and p2.POST_ID = wa.POSTRANK2
|
|
106
|
left outer join dbo.CF_POSTS p3 on p3.CAFE_ID = wa.CAFE_ID and p3.POST_ID = wa.POSTRANK3
|
|
107
|
left outer join dbo.CF_CAFE_MEMBERS c1 on c1.CAFE_SEQ = cc.SEQ
|
|
108
|
and c1.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p1.MEM_EMAIL)
|
|
109
|
left outer join dbo.CF_CAFE_MEMBERS c2 on c2.CAFE_SEQ = cc.SEQ
|
|
110
|
and c2.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p2.MEM_EMAIL)
|
|
111
|
left outer join dbo.CF_CAFE_MEMBERS c3 on c3.CAFE_SEQ = cc.SEQ
|
|
112
|
and c3.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p3.MEM_EMAIL)
|
|
113
|
where cc.STAT <> 9
|
|
114
|
and cc.IS_PUBLIC = 'Y'
|
|
115
|
and cc.CAFE_LANG = @lang_cd
|
|
116
|
order by POSTRANK1_DT desc
|
|
117
|
|
|
118
|
|
|
119
|
print @totcnt;
|
|
120
|
RETURN @totcnt;
|
|
121
|
|
|
122
|
END
|
|
123
|
|
|
124
|
|
|
125
|
GO
|
|
126
|
|
|
127
|
|
