ALTER TABLE {databaseOwner}{objectQualifier}DMX_Entries
ADD [IsPrivate] BIT
GO

UPDATE {databaseOwner}{objectQualifier}DMX_Entries
SET IsPrivate = 0

GO

/****** PROCEDURES ******/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntries') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntries
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntries
	@EntryId Int,
        @PortalId int
AS

SELECT
	[Author],
	[Categories],
	[CollectionId],
	[DateSubmitted],
	[Deleted],
	[Description],
	[EditRoles],
	[Entry],
	[EntryId],
	[EntryType],
	[FileSize],
	[IsApproved],
	[IsLocked],
	[IsPrivate],
	[Keywords],
	[LastModified],
	[LastVersionId],
	[LockedBy],
	[OriginalFileName],
	[PortalId],
	[Remarks],
	[UserId],
	[Version],
	[ViewRoles]
FROM
	{databaseOwner}{objectQualifier}DMX_Entries
WHERE
	[EntryId] = @EntryId
	AND [PortalId] = @PortalId
GO


IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_AddEntries') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntries
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddEntries
	@Author NVarChar (50),
	@Categories NVarChar (255),
	@CollectionId Int,
	@DateSubmitted DateTime,
	@Deleted Bit,
	@Description NVarChar (150),
	@EditRoles NVarChar (255),
	@Entry NVarChar (255),
	@EntryType NVarChar (150),
	@FileSize Int,
	@IsApproved Bit,
	@IsLocked Bit,
	@IsPrivate Bit,
	@Keywords NVarChar (255),
	@LastModified DateTime,
	@LastVersionId Int,
	@LockedBy Int,
	@OriginalFileName NVarChar (255),
	@PortalId Int,
	@Remarks NText,
	@UserId Int,
	@Version Int,
	@ViewRoles NVarChar (255)
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Entries (
	[Author],
	[Categories],
	[CollectionId],
	[DateSubmitted],
	[Deleted],
	[Description],
	[EditRoles],
	[Entry],
	[EntryType],
	[FileSize],
	[IsApproved],
	[IsLocked],
	[IsPrivate],
	[Keywords],
	[LastModified],
	[LastVersionId],
	[LockedBy],
	[OriginalFileName],
	[PortalId],
	[Remarks],
	[UserId],
	[Version],
	[ViewRoles]
) VALUES (
	@Author,
	@Categories,
	@CollectionId,
	@DateSubmitted,
	@Deleted,
	@Description,
	@EditRoles,
	@Entry,
	@EntryType,
	@FileSize,
	@IsApproved,
	@IsLocked,
	@IsPrivate,
	@Keywords,
	@LastModified,
	@LastVersionId,
	@LockedBy,
	@OriginalFileName,
	@PortalId,
	@Remarks,
	@UserId,
	@Version,
	@ViewRoles
)

select SCOPE_IDENTITY()
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_UpdateEntries') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntries
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateEntries
	@EntryId Int,
	@Author NVarChar (50),
	@Categories NVarChar (255),
	@CollectionId Int,
	@DateSubmitted DateTime,
	@Deleted Bit,
	@Description NVarChar (150),
	@EditRoles NVarChar (255),
	@Entry NVarChar (255),
	@EntryType NVarChar (150),
	@FileSize Int,
	@IsApproved Bit,
	@IsLocked Bit,
	@IsPrivate Bit,
	@Keywords NVarChar (255),
	@LastModified DateTime,
	@LastVersionId Int,
	@LockedBy Int,
	@OriginalFileName NVarChar (255),
	@PortalId Int,
	@Remarks NText,
	@UserId Int,
	@Version Int,
	@ViewRoles NVarChar (255)
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Entries SET
	[Author] = @Author,
	[Categories] = @Categories,
	[CollectionId] = @CollectionId,
	[DateSubmitted] = @DateSubmitted,
	[Deleted] = @Deleted,
	[Description] = @Description,
	[EditRoles] = @EditRoles,
	[Entry] = @Entry,
	[EntryType] = @EntryType,
	[FileSize] = @FileSize,
	[IsApproved] = @IsApproved,
	[IsLocked] = @IsLocked,
	[IsPrivate] = @IsPrivate,
	[Keywords] = @Keywords,
	[LastModified] = @LastModified,
	[LastVersionId] = @LastVersionId,
	[LockedBy] = @LockedBy,
	[OriginalFileName] = @OriginalFileName,
	[PortalId] = @PortalId,
	[Remarks] = @Remarks,
	[UserId] = @UserId,
	[Version] = @Version,
	[ViewRoles] = @ViewRoles
WHERE
	[EntryId] = @EntryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_DeleteEntries') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntries
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteEntries
	@EntryId Int
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Entries
WHERE
	[EntryId] = @EntryId
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntriesByPortals') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortals
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByPortals
	@PortalId Int,
        @IncludeDeleted BIT
AS

SELECT
	{databaseOwner}{objectQualifier}DMX_Entries.Author,
	{databaseOwner}{objectQualifier}DMX_Entries.Categories,
	{databaseOwner}{objectQualifier}DMX_Entries.CollectionId,
	{databaseOwner}{objectQualifier}DMX_Entries.DateSubmitted,
	{databaseOwner}{objectQualifier}DMX_Entries.Deleted,
	{databaseOwner}{objectQualifier}DMX_Entries.Description,
	{databaseOwner}{objectQualifier}DMX_Entries.EditRoles,
	{databaseOwner}{objectQualifier}DMX_Entries.Entry,
	{databaseOwner}{objectQualifier}DMX_Entries.EntryId,
	{databaseOwner}{objectQualifier}DMX_Entries.EntryType,
	{databaseOwner}{objectQualifier}DMX_Entries.FileSize,
	{databaseOwner}{objectQualifier}DMX_Entries.IsApproved,
	{databaseOwner}{objectQualifier}DMX_Entries.IsLocked,
	{databaseOwner}{objectQualifier}DMX_Entries.IsPrivate,
	{databaseOwner}{objectQualifier}DMX_Entries.Keywords,
	{databaseOwner}{objectQualifier}DMX_Entries.LastModified,
	{databaseOwner}{objectQualifier}DMX_Entries.LastVersionId,
	{databaseOwner}{objectQualifier}DMX_Entries.LockedBy,
	{databaseOwner}{objectQualifier}DMX_Entries.OriginalFileName,
	{databaseOwner}{objectQualifier}DMX_Entries.PortalId,
	{databaseOwner}{objectQualifier}DMX_Entries.Remarks,
	{databaseOwner}{objectQualifier}DMX_Entries.UserId,
	{databaseOwner}{objectQualifier}DMX_Entries.Version,
	{databaseOwner}{objectQualifier}DMX_Entries.ViewRoles, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Entries.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Entries.PortalId = @PortalId
	AND	((NOT {databaseOwner}{objectQualifier}DMX_Entries.Deleted = 1) OR (@IncludeDeleted = 1))
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{objectQualifier}DMX_GetEntriesByUsers') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByUsers
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntriesByUsers
	@UserId Int,
        @IncludeDeleted BIT
AS

SELECT
	{databaseOwner}{objectQualifier}DMX_Entries.Author,
	{databaseOwner}{objectQualifier}DMX_Entries.Categories,
	{databaseOwner}{objectQualifier}DMX_Entries.CollectionId,
	{databaseOwner}{objectQualifier}DMX_Entries.DateSubmitted,
	{databaseOwner}{objectQualifier}DMX_Entries.Deleted,
	{databaseOwner}{objectQualifier}DMX_Entries.Description,
	{databaseOwner}{objectQualifier}DMX_Entries.EditRoles,
	{databaseOwner}{objectQualifier}DMX_Entries.Entry,
	{databaseOwner}{objectQualifier}DMX_Entries.EntryId,
	{databaseOwner}{objectQualifier}DMX_Entries.EntryType,
	{databaseOwner}{objectQualifier}DMX_Entries.FileSize,
	{databaseOwner}{objectQualifier}DMX_Entries.IsApproved,
	{databaseOwner}{objectQualifier}DMX_Entries.IsLocked,
	{databaseOwner}{objectQualifier}DMX_Entries.IsPrivate,
	{databaseOwner}{objectQualifier}DMX_Entries.Keywords,
	{databaseOwner}{objectQualifier}DMX_Entries.LastModified,
	{databaseOwner}{objectQualifier}DMX_Entries.LastVersionId,
	{databaseOwner}{objectQualifier}DMX_Entries.LockedBy,
	{databaseOwner}{objectQualifier}DMX_Entries.OriginalFileName,
	{databaseOwner}{objectQualifier}DMX_Entries.PortalId,
	{databaseOwner}{objectQualifier}DMX_Entries.Remarks,
	{databaseOwner}{objectQualifier}DMX_Entries.UserId,
	{databaseOwner}{objectQualifier}DMX_Entries.Version,
	{databaseOwner}{objectQualifier}DMX_Entries.ViewRoles, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Entries ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Entries.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Entries.UserId = @UserId
	AND	((NOT {databaseOwner}{objectQualifier}DMX_Entries.Deleted = 1) OR (@IncludeDeleted = 1))
GO




