createdbobjects.sql

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

-- point to proper DB 
use [~~##PRTLStarterKitDB##~~]
GO

-- drop any existing stuff

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserRoles_Roles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Portal_UserRoles] DROP CONSTRAINT FK_UserRoles_Roles
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UserRoles_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Portal_UserRoles] DROP CONSTRAINT FK_UserRoles_Users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddAnnouncement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddAnnouncement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddContact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddEvent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddLink]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_AddUserRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_AddUserRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteAnnouncement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteAnnouncement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteContact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteDocument]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteDocument]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteEvent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteLink]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteModule]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteModule]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_DeleteUserRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_DeleteUserRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetAnnouncements]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetAnnouncements]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetAuthRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetAuthRoles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetContacts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetContacts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetDocumentContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetDocumentContent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetDocuments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetDocuments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetEvents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetEvents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetHtmlText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetHtmlText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetLinks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetLinks]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetNextMessageID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetNextMessageID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetPortalRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetPortalRoles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetPrevMessageID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetPrevMessageID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetRoleMembership]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetRoleMembership]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetRolesByUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetRolesByUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleAnnouncement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleAnnouncement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleContact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleDocument]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleDocument]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleEvent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleLink]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleMessage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleMessage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetSingleUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetSingleUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetThreadMessages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetThreadMessages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetTopLevelMessages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetTopLevelMessages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_GetUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_GetUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateAnnouncement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateAnnouncement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateContact]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateContact]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateDocument]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateDocument]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateEvent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateHtmlText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateHtmlText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateLink]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateLink]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UpdateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UpdateUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UserLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Portal_UserLogin]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Announcements]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Announcements]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Contacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Contacts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Discussion]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Discussion]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Documents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Documents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Events]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Events]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_HtmlText]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_HtmlText]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Links]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Links]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Roles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_UserRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_UserRoles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Portal_Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Portal_Users]
GO

CREATE TABLE [dbo].[Portal_Announcements] (
	[ItemID] [int] IDENTITY (0, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[CreatedByUser] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedDate] [datetime] NULL ,
	[Title] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MoreLink] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MobileMoreLink] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ExpireDate] [datetime] NULL ,
	[Description] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_Contacts] (
	[ItemID] [int] IDENTITY (0, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[CreatedByUser] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedDate] [datetime] NULL ,
	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Role] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Contact1] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Contact2] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_Discussion] (
	[ItemID] [int] IDENTITY (0, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[Title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedDate] [datetime] NULL ,
	[Body] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DisplayOrder] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedByUser] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_Documents] (
	[ItemID] [int] IDENTITY (0, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[CreatedByUser] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedDate] [datetime] NULL ,
	[FileNameUrl] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FileFriendlyName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Category] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Content] [image] NULL ,
	[ContentType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContentSize] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_Events] (
	[ItemID] [int] IDENTITY (0, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[CreatedByUser] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedDate] [datetime] NULL ,
	[Title] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WhereWhen] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Description] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ExpireDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_HtmlText] (
	[ModuleID] [int] NOT NULL ,
	[DesktopHtml] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MobileSummary] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[MobileDetails] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_Links] (
	[ItemID] [int] IDENTITY (0, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[CreatedByUser] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedDate] [datetime] NULL ,
	[Title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Url] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MobileUrl] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ViewOrder] [int] NULL ,
	[Description] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_Roles] (
	[RoleID] [int] IDENTITY (0, 1) NOT NULL ,
	[PortalID] [int] NOT NULL ,
	[RoleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_UserRoles] (
	[UserID] [int] NOT NULL ,
	[RoleID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Portal_Users] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Portal_Announcements] ADD 
	CONSTRAINT [PK_Announcements] PRIMARY KEY  NONCLUSTERED 
	(
		[ItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_Contacts] ADD 
	CONSTRAINT [PK_Contacts] PRIMARY KEY  NONCLUSTERED 
	(
		[ItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_Discussion] ADD 
	CONSTRAINT [PK_Discussion] PRIMARY KEY  NONCLUSTERED 
	(
		[ItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_Documents] ADD 
	CONSTRAINT [PK_Documents] PRIMARY KEY  NONCLUSTERED 
	(
		[ItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_Events] ADD 
	CONSTRAINT [PK_Events] PRIMARY KEY  NONCLUSTERED 
	(
		[ItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_HtmlText] ADD 
	CONSTRAINT [PK_HtmlText] PRIMARY KEY  NONCLUSTERED 
	(
		[ModuleID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_Links] ADD 
	CONSTRAINT [PK_Links] PRIMARY KEY  NONCLUSTERED 
	(
		[ItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_Roles] ADD 
	CONSTRAINT [PK_PortalRoles] PRIMARY KEY  NONCLUSTERED 
	(
		[RoleID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_Users] ADD 
	CONSTRAINT [PK_PortalUsers] PRIMARY KEY  NONCLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_PortalUsers] UNIQUE  NONCLUSTERED 
	(
		[Email]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Portal_UserRoles] ADD 
	CONSTRAINT [FK_UserRoles_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Portal_Roles] (
		[RoleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION ,
	CONSTRAINT [FK_UserRoles_Users] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[Portal_Users] (
		[UserID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



-- =============================================================
-- create the stored procs
-- =============================================================
CREATE PROCEDURE Portal_AddAnnouncement
(
    @ModuleID       int,
    @UserName       nvarchar(100),
    @Title          nvarchar(150),
    @MoreLink       nvarchar(150),
    @MobileMoreLink nvarchar(150),
    @ExpireDate     DateTime,
    @Description    nvarchar(2000),
    @ItemID         int OUTPUT
)
AS

INSERT INTO Portal_Announcements
(
    ModuleID,
    CreatedByUser,
    CreatedDate,
    Title,
    MoreLink,
    MobileMoreLink,
    ExpireDate,
    Description
)

VALUES
(
    @ModuleID,
    @UserName,
    GetDate(),
    @Title,
    @MoreLink,
    @MobileMoreLink,
    @ExpireDate,
    @Description
)

SELECT
    @ItemID = @@Identity



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_AddContact
(
    @ModuleID int,
    @UserName nvarchar(100),
    @Name     nvarchar(50),
    @Role     nvarchar(100),
    @Email    nvarchar(100),
    @Contact1 nvarchar(250),
    @Contact2 nvarchar(250),
    @ItemID   int OUTPUT
)
AS

INSERT INTO Portal_Contacts
(
    CreatedByUser,
    CreatedDate,
    ModuleID,
    Name,
    Role,
    Email,
    Contact1,
    Contact2
)

VALUES
(
    @UserName,
    GetDate(),
    @ModuleID,
    @Name,
    @Role,
    @Email,
    @Contact1,
    @Contact2
)

SELECT
    @ItemID = @@Identity



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_AddEvent
(
    @ModuleID    int,
    @UserName    nvarchar(100),
    @Title       nvarchar(100),
    @ExpireDate  DateTime,
    @Description nvarchar(2000),
    @WhereWhen   nvarchar(100),
    @ItemID      int OUTPUT
)
AS

INSERT INTO Portal_Events
(
    ModuleID,
    CreatedByUser,
    Title,
    CreatedDate,
    ExpireDate,
    Description,
    WhereWhen
)

VALUES
(
    @ModuleID,
    @UserName,
    @Title,
    GetDate(),
    @ExpireDate,
    @Description,
    @WhereWhen
)

SELECT
    @ItemID = @@Identity



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE Portal_AddLink
(
    @ModuleID    int,
    @UserName    nvarchar(100),
    @Title       nvarchar(100),
    @Url         nvarchar(250),
    @MobileUrl   nvarchar(250),
    @ViewOrder   int,
    @Description nvarchar(2000),
    @ItemID      int OUTPUT
)
AS

INSERT INTO Portal_Links
(
    ModuleID,
    CreatedByUser,
    CreatedDate,
    Title,
    Url,
    MobileUrl,
    ViewOrder,
    Description
)
VALUES
(
    @ModuleID,
    @UserName,
    GetDate(),
    @Title,
    @Url,
    @MobileUrl,
    @ViewOrder,
    @Description
)

SELECT
    @ItemID = @@Identity



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE Portal_AddMessage
(
    @ItemID int OUTPUT,
    @Title nvarchar(100),
    @Body nvarchar(3000),
    @ParentID int,
    @UserName nvarchar(100),
    @ModuleID int
)   

AS 

/* Find DisplayOrder of parent item */
DECLARE @ParentDisplayOrder as nvarchar(750)

SET @ParentDisplayOrder = ""

SELECT 
    @ParentDisplayOrder = DisplayOrder
FROM Portal_Discussion 
WHERE 
    ItemID = @ParentID

INSERT INTO Portal_Discussion
(
    Title,
    Body,
    DisplayOrder,
    CreatedDate, 
    CreatedByUser,
    ModuleID
)

VALUES
(
    @Title,
    @Body,
    @ParentDisplayOrder + CONVERT( nvarchar(24), GetDate(), 21 ),
    GetDate(),
    @UserName,
    @ModuleID
)

SELECT 
    @ItemID = @@Identity



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE Portal_AddRole
(
    @PortalID    int,
    @RoleName    nvarchar(50),
    @RoleID      int OUTPUT
)
AS

INSERT INTO Portal_Roles
(
    PortalID,
    RoleName
)

VALUES
(
    @PortalID,
    @RoleName
)

SELECT
    @RoleID = @@Identity



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE  PROCEDURE Portal_AddUser
(
    @Name     nvarchar(50),
    @Email    nvarchar(100),
    @Password nvarchar(50),
    @UserID   int OUTPUT
)
AS

INSERT INTO Portal_Users
(
    Name,
    Email,
    Password
)

VALUES
(
    @Name,
    @Email,
    @Password
)

SELECT
    @UserID = @@Identity





GO
SET QUOTED_IDENTIFIER OFF 
GO