'계층형게시판'에 해당되는 글 1건

CREATE TABLE dbo.tbllb_DBoard (
    idx          INT NOT NULL, 
    lb_name      VARCHAR(10) NOT NULL, 
    lb_SCate     TINYINT NOT NULL CONSTRAINT DF_tbllb_DBoard_lb_SCate DEFAULT (0), 
    lb_news      BIT NOT NULL, 
    m_id         VARCHAR(20), 
    Umame        VARCHAR(20) NOT NULL, 
    eMail        VARCHAR(50), 
    Title        VARCHAR(100) NOT NULL, 
    TagFlag      BIT NOT NULL, 
    Content      TEXT NOT NULL, 
    pwd          VARCHAR(10), 
    readNum      INT NOT NULL CONSTRAINT DF_tbllb_DBoard_readNum DEFAULT (0), 
    rRef         SMALLINT, 
    rLevel       SMALLINT, 
    rStep        SMALLINT, 
    file1        VARCHAR(50), 
    file1_size   INT, 
    file1_width  INT, 
    file2        VARCHAR(50), 
    file3        VARCHAR(50), 
    writeday     DATETIME NOT NULL CONSTRAINT DF_tbllb_DBoard_writeday DEFAULT (getdate()), 
 CONSTRAINT idx_board 
   PRIMARY KEY NONCLUSTERED (idx ASC, lb_news DESC, rRef DESC, rStep ASC) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE dbo.tbllb_DBoard 
    WITH CHECK ADD CONSTRAINT FK_tbllb_DBoard_tbllb_config
      FOREIGN KEY ( lb_name )
        REFERENCES dbo.tbllb_config ( lb_name )
;






CREATE PROCEDURE [Proc_lb_DList] 
	@lb_name		VarChar(10),
	@rlb_SCate		VarChar(50),
	@SearchF		VarChar(30),
	@SearchS		VarChar(50),
	@npage		INT,
	@lb_CateName		VarChar(50) 	OUTPUT,
	@lb_Nickname		VarChar(50) 	OUTPUT,
	@lb_open		Char(1)	 	OUTPUT,
	@lb_design		Char(28) 	OUTPUT,
	@lb_record		INT		OUTPUT,
	@lb_width		VARCHAR(5)	OUTPUT,
	@szRecordCount	INT		OUTPUT,
	@szPageCount		INT		OUTPUT,
	@MsgFlag		SMALLINT	OUTPUT,
	@Msg			VARCHAR(50)	OUTPUT
AS
	DECLARE 	@sql 		varchar(8000)
	DECLARE 	@sql2 		varchar(1000)
	DECLARE 	@sql3 		nvarchar(4000)  ---NvarChar형식이여야 함.
	DECLARE 	@p 		nvarchar(100)
	DECLARE 	@sum_cnt 	INT -- output 파라메터를 위한 선언
	SET @sql=''
	SET @sql2=''
	SET @SearchS=rTrim(ltrim(@SearchS))
		IF @rlb_SCate<>99 BEGIN
			Set @sql2=@sql2 + ' and ((lb_news=0 and lb_SCate=' + Convert(Varchar(5),@rlb_SCate) + ') or lb_news=1)'
		END
	IF @SearchS<>'' BEGIN
		IF @SearchF='Title' BEGIN
			SET @sql2=@sql2 + ' and ((Title like ' +Char(39) + '%' + @SearchS + '%' + Char(39) + ') or (Umame like ' +Char(39) + '%' + @SearchS + '%' + Char(39) + '))'
		END
		ELSE	BEGIN
			SET @sql2=@sql2 + ' and Content like ' +Char(39) + '%' + @SearchS + '%' + Char(39) 	
		END
	END
	IF EXISTS(select lb_name from tbllb_config where lb_name=@lb_name)
	BEGIN
		Set @lb_CateName=(select lb_CateName from tbllb_Cate where lb_Cate=(select lb_Cate from tbllb_config where lb_name=@lb_name))
		Set @lb_Nickname=(select lb_Nickname from tbllb_config where lb_name=@lb_name)
		Set @lb_open=(select lb_open from tbllb_config where lb_name=@lb_name)
		Set @lb_design=(select lb_design from tbllb_config where lb_name=@lb_name)
		Set @lb_record=(select lb_record from tbllb_config where lb_name=@lb_name)
		Set @lb_width=(select lb_width from tbllb_config where lb_name=@lb_name)
		
		SET @sql = 'select top ' + convert(varchar(10),@lb_record) + ' idx, lb_news, m_id, Umame, eMail, lb_SCate, 
		(case lb_news when 1 then ' + Char(39) +Char(39) + ' else (case lb_SCate when 0 then (case when (select count(lb_SCate) from tbllb_SCate where lb_Name=tbllb_Dboard.lb_Name)>0 then ' + Char(39) + '[기타]' +Char(39) +' else  ' + Char(39) +Char(39) +' end) else (select '  + Char(39) +  '['  + Char(39) +  '+lb_SCateName+' + Char(39) + ']'  + Char(39) +  ' from tbllb_SCate where lb_Name=tbllb_Dboard.lb_Name and lb_SCate=tbllb_Dboard.lb_SCate) end) end ) as lb_SCateName,
		 Title,readNum, convert(varchar(10),writeday,121) as writeday, (case when convert(varchar(10),writeday,121)>=convert(varchar(10),getdate()-1,121) then 1 else 0 end) as date_flag, rLevel,(case when (file1_width>0 and file1<>' + char(39)+char(39)+ ') then 1 when file1<>' + char(39)+char(39)+ ' then 2 else 0 end) as file_flag, file1,(select count(num) from tbllb_reView where lb_name=' + char(39) + @lb_name + char(39) + ' and idx=tbllb_Dboard.idx) as reViweNO from tbllb_Dboard where lb_name=' + char(39) + @lb_name + char(39) + @sql2
		SET @sql = @sql + ' and idx Not in(select top ' +convert(varchar(10),@lb_record*(@npage-1))  + ' idx from tbllb_Dboard where lb_name=' + char(39) + @lb_name + char(39) + @sql2 + ' Order by lb_news Desc, rRef desc, rStep asc) Order by lb_news Desc, rRef desc, rStep asc'
		-- 주문 수량 카운트
		SET @sql3='select @sum_cnt_in=(count(idx)) from tbllb_DBoard where lb_name=' +char(39) + @lb_name + char(39) + @sql2
		set @p = '@sum_cnt_in INT OUTPUT'
		EXEC sp_executesql @sql3, @p, @sum_cnt_in=@sum_cnt OUTPUT
		SET @szRecordCount=@sum_cnt
		SET @szPageCount=(CONVERT(INT,((@szRecordCount - 1)/@lb_record) + 1))
	
		--select @sql
		exec(@sql)
		SET @MsgFlag=1 -- 1  성공
	END
	ELSE	BEGIN
			SET @MsgFlag=0 -- 1저장 실패
			SET @Msg='잘못 된 접근입니다.' 
	END

















CREATE PROCEDURE [Proc_lb_BregProc] 
	@lb_name		VarChar(10),
	@lb_SCate		tinyInt,
	@idx			INT,
	@rRef			SmallINT,
	@rLevel		SmallINT,
	@rStep			SmallINT,
	@flag			Char(1),
	
	@Umame		VarChar(10),
	@M_id			VarChar(20),
	@Title			VarChar(100),
	@eMail			VarChar(50),
	@Content		TEXT,
	@TegFlag		BIT,
	@lb_news		BIT,
	@Pwd			VarChar(10),
	@MsgFlag		SmallINT	OUTPUT,
	@Msg			VarChar(50)	OUTPUT,
	@NextIdx		INT		OUTPUT
AS
	set nocount on
	DECLARE  @regFlag  	CHAR(1)
	
	IF EXISTS(select lb_name from tbllb_config where lb_name=@lb_name)
	BEGIN
		SET @regFlag=(select lb_open from tbllb_config where lb_name=@lb_name)
		SET @NextIdx=(select isnull(MAX(idx),0)+1 from tbllb_Bboard)
	
		Set @M_id=rtrim(@M_id)
		Set @Pwd=rtrim(@Pwd)
	
		IF @flag='R' and @idx>0 BEGIN
			SET @lb_SCate=(select top 1 lb_SCate from dbo.tbllb_Bboard where rRef=@rRef Order by rLevel Asc)
			UPdate tbllb_Bboard Set rStep=rStep+1 Where lb_name=@lb_name and rRef=@rRef and rStep>@rStep
			SET @rStep=@rStep+1
			SET @rLevel=@rLevel+1
		END 
		ELSE BEGIN
			SET @rRef=@NextIdx
			SET @rStep=0
			SET @rLevel=0
		END
	
		IF @regFlag='W' or LEN(@M_id)>0 BEGIN
			INSERT INTO tbllb_Bboard
			(idx,lb_name,lb_SCate,lb_news, m_id,Umame,eMail,Title, TagFlag, Content, pwd, rRef, rLevel, rStep) 
			values 
			(@NextIdx,@lb_name,@lb_SCate,@lb_news, @M_id,@Umame,@eMail,@Title, @TegFlag, @Content, @Pwd, @rRef, @rLevel, @rStep)
	
			SET @MsgFlag=1 -- 1저장 성공
		END
		ELSE	BEGIN
			SET @MsgFlag=0 -- 1저장 성공
			SET @Msg='게시판 쓰기 권한이 존재하지 않습니다.' -- 1저장 성공
		END
	END
	ELSE	BEGIN
			SET @MsgFlag=0 -- 1저장 성공
			SET @Msg='잘못 된 접근입니다.' -- 1저장 성공
	END
	set nocount off