SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Forums_Pmsg](
    [PmsgID] [int] IDENTITY(1,1) NOT NULL,
    [AddedDate] [datetime] NOT NULL CONSTRAINT [DF_Forums_Pmsg_AddedDate]  DEFAULT (getdate()),
    [AddedBy] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL,
    [IsRead] [bit] NOT NULL CONSTRAINT [DF_Forums_Pmsg_IsRead]  DEFAULT ((0)),
    [DeletedBySender] [bit] NOT NULL CONSTRAINT [DF_Forums_Pmsg_DeleteBySender]  DEFAULT ((0)),
    [DeletedByOwner] [bit] NOT NULL CONSTRAINT [DF_Forums_Pmsg_DeleteByOwner]  DEFAULT ((0)),
    [ToMember] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL,
    [Title] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL,
    [Body] [ntext] COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_Forums_Pmsg] PRIMARY KEY CLUSTERED 
(
    [PmsgID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE PROCEDURE [dbo].[Forums_DeletePmsg]
(
    @PmsgID int,
    @IsSender bit,
    @UserName nvarchar(256)
)
AS

DECLARE @Sender nvarchar(256)
DECLARE @Owner nvarchar(256)
DECLARE @DeletedBySender bit
DECLARE @DeletedByOwner bit

Select    @Sender = AddedBy, 
        @Owner = ToMember, 
        @DeletedBySender = DeletedBySender, 
        @DeletedByOwner = DeletedByOwner 
   FROM Forums_Pmsg WHERE PmsgID = @PmsgID

IF @IsSender = 1
  BEGIN
   --Security: Verify if really is the sender
   IF LOWER(@UserName) = LOWER(@Sender)
   BEGIN
     --Check if the owner already delete it previously
       IF @DeletedByOwner = 1
       BEGIN
          -- Then erase from DB
          DELETE FROM Forums_Pmsg WHERE PmsgID = @PmsgID
       END ELSE -- The owner didn't want to delete yet 
       BEGIN
          Update Forums_Pmsg SET DeletedBySender = 1 WHERE PmsgID = @PmsgID
       END
    END
   END ELSE -- If @IsSender = 0 then the currentUser is the owner
   BEGIN
   --Security: Verify if really is the owner
   IF LOWER(@UserName) = LOWER(@Owner)
   BEGIN
     --Check if the sender already delete it previously
       IF @DeletedBySender = 1
       BEGIN
          -- Then erase from DB
          DELETE FROM Forums_Pmsg WHERE PmsgID = @PmsgID
       END ELSE -- The sender didn't deleted yet 
       BEGIN
          Update Forums_Pmsg SET DeletedByOwner = 1 WHERE PmsgID = @PmsgID
       END
END
   END

        Public Overrides Function DeletePmsg(ByVal pmsgID As Integer, ByVal isSender As Boolean, ByVal userName As String) As Boolean
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("Forums_DeletePmsg", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@PmsgID", SqlDbType.Int).Value = pmsgID
                cmd.Parameters.Add("@IsSender", SqlDbType.Bit).Value = CInt(isSender)
                cmd.Parameters.Add("@Username", SqlDbType.Bit).Value = userName
                cn.Open()
                Dim ret As Integer = ExecuteNonQuery(cmd)
                Return (ret = 1)
            End Using
        End Function