createdbobjects.sql

Portal Starter Kit提供了在线管理工具

SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_AddUserRole
(
    @UserID int,
    @RoleID int
)
AS

SELECT 
    *
FROM Portal_UserRoles

WHERE
    UserID=@UserID
    AND
    RoleID=@RoleID

/* only insert if the record doesn't yet exist */
IF @@Rowcount < 1

    INSERT INTO Portal_UserRoles
    (
        UserID,
        RoleID
    )

    VALUES
    (
        @UserID,
        @RoleID
    )



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE Portal_DeleteAnnouncement
(
    @ItemID int
)
AS

DELETE FROM Portal_Announcements

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_DeleteContact
(
    @ItemID int
)
AS

DELETE FROM Portal_Contacts

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_DeleteDocument
(
    @ItemID int
)
AS

DELETE FROM Portal_Documents

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_DeleteEvent
(
    @ItemID int
)
AS

DELETE FROM Portal_Events

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_DeleteLink
(
    @ItemID int
)
AS

DELETE FROM Portal_Links

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  PROCEDURE Portal_DeleteModule
(
    @ModuleID       int
)
AS
      DELETE FROM Portal_Announcements
      WHERE ModuleID = @ModuleID

      DELETE FROM Portal_Contacts
      WHERE ModuleID = @ModuleID

      DELETE FROM Portal_Discussion
      WHERE ModuleID = @ModuleID

      DELETE FROM Portal_Documents
      WHERE ModuleID = @ModuleID

      DELETE FROM Portal_Events
      WHERE ModuleID = @ModuleID

      DELETE FROM Portal_HtmlText
      WHERE ModuleID = @ModuleID

      DELETE FROM Portal_Links
      WHERE ModuleID = @ModuleID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_DeleteRole
(
    @RoleID int
)
AS

DELETE FROM Portal_Roles

WHERE
    RoleID = @RoleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_DeleteUser
(
    @UserID int
)
AS

DELETE FROM Portal_Users

WHERE
    UserID=@UserID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_DeleteUserRole
(
    @UserID int,
    @RoleID int
)
AS

DELETE FROM Portal_UserRoles

WHERE
    UserID=@UserID
    AND
    RoleID=@RoleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetAnnouncements
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    CreatedByUser,
    CreatedDate,
    Title,
    MoreLink,
    MobileMoreLink,
    ExpireDate,
    Description

FROM Portal_Announcements

WHERE
    ModuleID = @ModuleID
  AND
    ExpireDate > GetDate()



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetAuthRoles
(
    @PortalID    int,
    @ModuleID    int,
    @AccessRoles nvarchar (256) OUTPUT,
    @EditRoles   nvarchar (256) OUTPUT
)
AS

SELECT  
    @AccessRoles = Portal_Tabs.AuthorizedRoles,
    @EditRoles   = Portal_Modules.AuthorizedEditRoles
    
FROM Portal_Modules
  INNER JOIN
    Portal_Tabs ON Portal_Modules.TabID = Portal_Tabs.TabID
    
WHERE   
    Portal_Modules.ModuleID = @ModuleID
  AND
    Portal_Tabs.PortalID = @PortalID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetContacts
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    CreatedDate,
    CreatedByUser,
    Name,
    Role,
    Email,
    Contact1,
    Contact2

FROM Portal_Contacts

WHERE
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetDocumentContent
(
    @ItemID int
)
AS

SELECT
    Content,
    ContentType,
    ContentSize,
    FileFriendlyName

FROM Portal_Documents

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetDocuments
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    FileFriendlyName,
    FileNameUrl,
    CreatedByUser,
    CreatedDate,
    Category,
    ContentSize
    
FROM Portal_Documents

WHERE
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetEvents
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    Title,
    CreatedByUser,
    WhereWhen,
    CreatedDate,
    Title,
    ExpireDate,
    Description

FROM Portal_Events

WHERE
    ModuleID = @ModuleID
  AND
    ExpireDate > GetDate()



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetHtmlText
(
    @ModuleID int
)
AS

SELECT
    *

FROM Portal_HtmlText

WHERE
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetLinks
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    CreatedByUser,
    CreatedDate,
    Title,
    Url,
    ViewOrder,
    Description

FROM Portal_Links

WHERE
    ModuleID = @ModuleID

ORDER BY
    ViewOrder



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetNextMessageID
(
    @ItemID int,
    @NextID int OUTPUT
)
AS

DECLARE @CurrentDisplayOrder as nvarchar(750)
DECLARE @CurrentModule as int

/* Find DisplayOrder of current item */
SELECT
    @CurrentDisplayOrder = DisplayOrder,
    @CurrentModule = ModuleID
FROM Portal_Discussion
WHERE
    ItemID = @ItemID

/* Get the next message in the same module */
SELECT Top 1
    @NextID = ItemID

FROM Portal_Discussion

WHERE
    DisplayOrder > @CurrentDisplayOrder
    AND
    ModuleID = @CurrentModule

ORDER BY
    DisplayOrder ASC

/* end of this thread? */
IF @@Rowcount < 1
    SET @NextID = null



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




/* returns all roles for the specified portal */
CREATE PROCEDURE Portal_GetPortalRoles
(
    @PortalID  int
)
AS

SELECT  
    RoleName,
    RoleID

FROM Portal_Roles

WHERE   
    PortalID = @PortalID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_GetPrevMessageID
(
    @ItemID int,
    @PrevID int OUTPUT
)
AS

DECLARE @CurrentDisplayOrder as nvarchar(750)
DECLARE @CurrentModule as int

/* Find DisplayOrder of current item */
SELECT
    @CurrentDisplayOrder = DisplayOrder,
    @CurrentModule = ModuleID
FROM Portal_Discussion
WHERE
    ItemID = @ItemID

/* Get the previous message in the same module */
SELECT Top 1
    @PrevID = ItemID

FROM Portal_Discussion

WHERE
    DisplayOrder < @CurrentDisplayOrder
    AND
    ModuleID = @CurrentModule

ORDER BY
    DisplayOrder DESC

/* already at the beginning of this module? */
IF @@Rowcount < 1
    SET @PrevID = null



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




/* returns all members for the specified role */
CREATE PROCEDURE Portal_GetRoleMembership
(
    @RoleID  int
)
AS

SELECT  
    Portal_UserRoles.UserID,
    Name,
    Email

FROM Portal_UserRoles
    
INNER JOIN 
    Portal_Users On Portal_Users.UserID = Portal_UserRoles.UserID

WHERE   
    Portal_UserRoles.RoleID = @RoleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




/* returns all roles for the specified user */
CREATE PROCEDURE Portal_GetRolesByUser
(
    @Email         nvarchar(100)
)
AS

SELECT  
    Portal_Roles.RoleName,
    Portal_Roles.RoleID

FROM Portal_UserRoles
  INNER JOIN 
    Portal_Users ON Portal_UserRoles.UserID = Portal_Users.UserID
  INNER JOIN 
    Portal_Roles ON Portal_UserRoles.RoleID = Portal_Roles.RoleID

WHERE   
    Portal_Users.Email = @Email



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROCEDURE Portal_GetSingleAnnouncement
(
    @ItemID int
)
AS

SELECT
    CreatedByUser,
    CreatedDate,