--增加短消息
IF OBJECT_ID('dnt_createpm','P') IS NOT NULL
DROP PROC dnt_createpm
GO 
CREATE PROCEDURE dnt_createpm
@pmid int,
@msgfrom nvarchar(20),
@msgto nvarchar(20),
@msgfromid int,
@msgtoid int,
@folder smallint=0,--草稿箱
@new int=0,
@subject nvarchar(60),
@postdatetime datetime,
@message ntext,
@savetosentbox smallint=1
AS
IF @folder<>0    --不为0时是保存至草稿箱
    BEGIN
        SET @msgfrom=@msgto   --发给自己的，因此发件人和收件人是一个
    END
ELSE             --若草稿箱为0时，是发给其他人的，更新，通知用户有新消息
    BEGIN
        UPDATE [dnt_users] 
        SET [newpmcount]=ABS(ISNULL([newpmcount],0)*1)+1,[newpm] = 1 
        WHERE [uid]=@msgtoid
    END
    
INSERT INTO [dnt_pms] 
    ([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
VALUES
    (@msgfrom,@msgfromid,@msgto,@msgtoid,@folder,@new,@subject,@postdatetime,@message)
    
SELECT SCOPE_IDENTITY() AS 'pmid'
IF @savetosentbox=1 AND @folder=0   --用户发送信息时勾选保存至发稿箱，
    BEGIN
        INSERT INTO [dnt_pms]
            ([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
        VALUES
            (@msgfrom,@msgfromid,@msgto,@msgtoid,1,@new,@subject,@postdatetime,@message)
    END
GO 
--查询短消息个数
IF OBJECT_ID('dnt_getpmcount','P') IS NOT NULL
DROP PROC dnt_getpmcount
GO 
CREATE PROCEDURE dnt_getpmcount
@userid int,
@folder int=0,
@state int=-1
AS
IF @folder=-1
    BEGIN
      SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE ([msgtoid]=@userid AND [folder]=0) OR ([msgfromid] = @userid AND [folder] = 1) OR ([msgfromid] = @userid AND [folder] = 2)
    END
ELSE
    BEGIN
        IF @folder=0
            BEGIN
                IF @state=-1
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid]=@userid AND [folder]=@folder
                    END
                ELSE IF @state=2
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid]=@userid AND [folder]=@folder AND [new]=1 AND GETDATE()-[postdatetime]<3
                    END
                ELSE
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgtoid]=@userid AND [folder]=@folder AND [new]=@state
                    END
            END
        ELSE
            BEGIN
                IF @state=-1
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid]=@userid AND [folder]=@folder
                    END
                ELSE IF @state=2
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid]=@userid AND [folder]=@folder AND [new]=1 AND GETDATE()-[postdatetime]<3
                    END
                ELSE
                    BEGIN
                        SELECT COUNT(pmid) AS [pmcount] FROM [dnt_pms] WHERE [msgfromid]=@userid AND [folder]=@folder AND [new]=@state
                    END
            END
    END
GO 
--查询
IF OBJECT_ID('dnt_getpmlist','P') IS NOT NULL
DROP PROC dnt_getpmlist
GO
CREATE PROCEDURE dnt_getpmlist
@userid int,
@folder int,
@pagesize int,
@pageindex int,
@inttype int
AS
DECLARE @strSQL varchar(5000)
DECLARE @msgformORtoID varchar(10)
SET @msgformortoid='msgtoid'
IF @folder=1 OR @folder=2
    BEGIN
        SET @msgformortoid='msgfromid'
    END
IF @pageindex = 1
    BEGIN
        IF (@inttype <> 1)
            SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder)  + ' ORDER BY [pmid] DESC'
        ELSE
            SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC'
        
    END
ELSE
    BEGIN
        SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [pmid] < (SELECT MIN([pmid]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [pmid] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC) AS tblTmp) AND [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' + STR(@folder) + ' ORDER BY [pmid] DESC'
        IF (@inttype <> 1)
            BEGIN
                SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [pmid] < (SELECT MIN([pmid]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [pmid] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC) AS tblTmp) AND [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) +  ' ORDER BY [pmid] DESC'
            END
    END
EXEC(@strSQL)
GO