/*

exec dbo.CF_GetMainCafePostList 'ja', 0, N'', 'users', 1, 10

*/
CREATE PROC [dbo].[CF_GetMainCafePostList]
  @lang_cd varchar(20),
  @menu_id int,
  @keyword NVARCHAR(100),
	@orderby VARCHAR(20),
	@cpage INT,
	@pagesize INT
AS

DECLARE 
	@totcnt INT;

BEGIN

  SET @totcnt = 0

  -- total count
  if( @menu_id = 0 )
  begin

    SELECT 
      @totcnt = COUNT(*)
      FROM [dbo].[CF_POSTS] p
     INNER JOIN dbo.CF_CAFE_INFO c on c.cafe_id = p.cafe_id
     WHERE c.CAFE_LANG = @lang_cd
       AND c.IS_PUBLIC = 'Y'
       AND c.IS_HOUSE <> 'Y'
       AND c.STAT <> 9
       AND p.STAT <> 9
       AND p.POST_TITLE LIKE + '%' + @keyword + '%'

  end 

  if( @menu_id = 0 )
  begin

      if( @orderby = 'forward' )
      begin

        SELECT
          d.*,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email1)), d.mem_email1) as cafe_nick1,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email2)), d.mem_email2) as cafe_nick2,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email3)), d.mem_email3) as cafe_nick3
         FROM (
                SELECT
                  a.cafe_id, 
                  a.cafe_seq,
                  a.cafe_name,
                  a.lang_cd,
                  a.region_cd,
                  a.TIMEZONE,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_id1,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_title1,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_content1,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as writer1,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as mem_email1,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as write_dt1,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_id2,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_title2,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_content2,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as writer2,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as mem_email2,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as write_dt2,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_id3,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_title3,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_content3,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as writer3,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as mem_email3,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as write_dt3,
                  (select count(*) from dbo.CF_CAFE_MEMBERS where cafe_seq in (select seq from dbo.CF_CAFE_INFO where cafe_id = a.CAFE_ID)) as mem_cnt,
                  (select top 1 file_name from dbo.CF_CAFE_FILES where cafe_id = a.CAFE_ID and file_type = 1 order by dt desc) as cafe_icon_file,
                  a.cafe_dt
                  FROM (
                    SELECT 
                      p.*, c.DT as cafe_dt, c.seq as cafe_seq, c.timezone,
                      (select top 1 MENU_NAME from dbo.CF_CAFE_MENU where CAFE_ID = p.CAFE_ID and MENU_ID = p.MENU_ID) as menu_name,
                      (select top 1 CAFE_NAME from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as cafe_name,
                      (select top 1 CAFE_DESC from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as cafe_desc,
                      (select top 1 CAFE_LANG from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as lang_cd,
                      (select top 1 REGION_CD from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as region_cd
                      FROM [dbo].[CF_POSTS] p
                     INNER JOIN dbo.CF_CAFE_INFO c on c.cafe_id = p.cafe_id
                     WHERE c.CAFE_LANG = @lang_cd
                       AND c.IS_PUBLIC = 'Y'
                       AND c.IS_HOUSE <> 'Y'
                       AND c.STAT <> 9
                       AND p.STAT <> 9
                       AND p.POST_TITLE LIKE + '%' + @keyword + '%'
                       ) a
                 GROUP BY
                    a.cafe_id, a.cafe_seq, a.cafe_name, a.lang_cd, a.region_cd, a.TIMEZONE, a.cafe_dt
              ) d
         ORDER BY 
            d.cafe_dt ASC
         OPTION (fast 3)
        ;

      end
      else if ( @orderby = 'users' )
      begin

        SELECT
          d.*,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email1)), d.mem_email1) as cafe_nick1,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email2)), d.mem_email2) as cafe_nick2,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email3)), d.mem_email3) as cafe_nick3
         FROM (
                SELECT
                  a.cafe_id, 
                  a.cafe_seq,
                  a.cafe_name,
                  a.lang_cd,
                  a.region_cd,
                  a.TIMEZONE,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_id1,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_title1,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_content1,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as writer1,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as mem_email1,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as write_dt1,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_id2,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_title2,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_content2,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as writer2,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as mem_email2,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as write_dt2,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_id3,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_title3,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_content3,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as writer3,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as mem_email3,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as write_dt3,
                  (select count(*) from dbo.CF_CAFE_MEMBERS where cafe_seq in (select seq from dbo.CF_CAFE_INFO where cafe_id = a.CAFE_ID)) as mem_cnt,
                  (select top 1 file_name from dbo.CF_CAFE_FILES where cafe_id = a.CAFE_ID and file_type = 1 order by dt desc) as cafe_icon_file,
                  a.cafe_dt
                  FROM (
                    SELECT 
                      p.*, c.DT as cafe_dt, c.seq as cafe_seq, c.timezone,
                      (select top 1 MENU_NAME from dbo.CF_CAFE_MENU where CAFE_ID = p.CAFE_ID and MENU_ID = p.MENU_ID) as menu_name,
                      (select top 1 CAFE_NAME from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as cafe_name,
                      (select top 1 CAFE_DESC from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as cafe_desc,
                      (select top 1 CAFE_LANG from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as lang_cd,
                      (select top 1 REGION_CD from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as region_cd
                      FROM [dbo].[CF_POSTS] p
                     INNER JOIN dbo.CF_CAFE_INFO c on c.cafe_id = p.cafe_id
                     WHERE c.CAFE_LANG = @lang_cd
                       AND c.IS_PUBLIC = 'Y'
                       AND c.IS_HOUSE <> 'Y'
                       AND c.STAT <> 9
                       AND p.STAT <> 9
                       AND p.POST_TITLE LIKE + '%' + @keyword + '%'
                       ) a
                 GROUP BY
                    a.cafe_id, a.cafe_seq, a.cafe_name, a.lang_cd, a.region_cd, a.TIMEZONE, a.cafe_dt
              ) d
         ORDER BY 
            d.mem_cnt DESC
         OPTION (fast 3)
        ;

      end
      else
      begin

        SELECT
          d.*,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email1)), d.mem_email1) as cafe_nick1,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email2)), d.mem_email2) as cafe_nick2,
          isnull((select top 1 cafe_nick from dbo.CF_CAFE_MEMBERS where cafe_seq = d.cafe_seq and mem_no in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = d.mem_email3)), d.mem_email3) as cafe_nick3
         FROM (
                SELECT
                  a.cafe_id, 
                  a.cafe_seq,
                  a.cafe_name,
                  a.lang_cd,
                  a.region_cd,
                  a.TIMEZONE,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_id1,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_title1,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as post_content1,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as writer1,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as mem_email1,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 1 ), '') as write_dt1,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_id2,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_title2,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as post_content2,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as writer2,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as mem_email2,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 2 ), '') as write_dt2,
                  isnull(( select top 1 post_id from (select ROW_NUMBER() over(order by dt desc) as rownum, post_id from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_id3,
                  isnull(( select top 1 post_title from (select ROW_NUMBER() over(order by dt desc) as rownum, post_title from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_title3,
                  isnull(( select top 1 post_content from (select ROW_NUMBER() over(order by dt desc) as rownum, post_content from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as post_content3,
                  isnull(( select top 1 writer from (select ROW_NUMBER() over(order by dt desc) as rownum, writer from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as writer3,
                  isnull(( select top 1 mem_email from (select ROW_NUMBER() over(order by dt desc) as rownum, mem_email from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as mem_email3,
                  isnull(( select top 1 dt from (select ROW_NUMBER() over(order by dt desc) as rownum, dt from dbo.CF_POSTS where cafe_id = a.cafe_id and stat <> 9 and IS_PUBLIC = 'Y') t where rownum = 3 ), '') as write_dt3,
                  (select count(*) from dbo.CF_CAFE_MEMBERS where cafe_seq in (select seq from dbo.CF_CAFE_INFO where cafe_id = a.CAFE_ID)) as mem_cnt,
                  (select top 1 file_name from dbo.CF_CAFE_FILES where cafe_id = a.CAFE_ID and file_type = 1 order by dt desc) as cafe_icon_file,
                  a.cafe_dt
                  FROM (
                    SELECT 
                      p.*, c.DT as cafe_dt, c.seq as cafe_seq, c.timezone,
                      (select top 1 MENU_NAME from dbo.CF_CAFE_MENU where CAFE_ID = p.CAFE_ID and MENU_ID = p.MENU_ID) as menu_name,
                      (select top 1 CAFE_NAME from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as cafe_name,
                      (select top 1 CAFE_DESC from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as cafe_desc,
                      (select top 1 CAFE_LANG from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as lang_cd,
                      (select top 1 REGION_CD from dbo.CF_CAFE_INFO where CAFE_ID = p.cafe_id) as region_cd
                      FROM [dbo].[CF_POSTS] p
                      INNER JOIN dbo.CF_CAFE_INFO c on c.cafe_id = p.cafe_id
                      WHERE c.CAFE_LANG = @lang_cd
                        AND c.IS_PUBLIC = 'Y'
                        AND c.IS_HOUSE <> 'Y'
                        AND c.STAT <> 9
                        AND p.STAT <> 9
                        AND p.POST_TITLE LIKE + '%' + @keyword + '%'
                        ) a
                  GROUP BY
                    a.cafe_id, a.cafe_seq, a.cafe_name, a.lang_cd, a.region_cd, a.TIMEZONE, a.cafe_dt
              ) d
         ORDER BY 
            d.cafe_dt DESC
         OPTION (fast 3)
        ;

      end

  end

print @totcnt;
	RETURN @totcnt;

END


GO