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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetUserSubscriptions
  @UserId Int, @PortalId Int
AS

SELECT {databaseOwner}{objectQualifier}DMX_Subscriptions.id, {databaseOwner}{objectQualifier}DMX_Subscriptions.LastAccess, {databaseOwner}{objectQualifier}DMX_Entries.*, {databaseOwner}{objectQualifier}DMX_Extensions.Name, {databaseOwner}{objectQualifier}DMX_Extensions.Icon16, Entries_1.Description AS CollectionName
FROM (({objectQualifier}DMX_Entries LEFT JOIN {databaseOwner}{objectQualifier}DMX_Entries AS Entries_1 ON {databaseOwner}{objectQualifier}DMX_Entries.CollectionId=Entries_1.EntryId) INNER JOIN {databaseOwner}{objectQualifier}DMX_Subscriptions ON {databaseOwner}{objectQualifier}DMX_Entries.EntryId={objectQualifier}DMX_Subscriptions.EntryId) LEFT JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON {databaseOwner}{objectQualifier}DMX_Entries.EntryType={objectQualifier}DMX_Extensions.ExtensionKey
WHERE ((({objectQualifier}DMX_Subscriptions.UserId)=@UserId)
 AND (({objectQualifier}DMX_Entries.PortalId)=@PortalId)
 AND (({objectQualifier}DMX_Extensions.PortalId)=@PortalId))

GO



/****** NEW EXTENSIONS COLUMN ******/

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Extensions 
ADD EntryTypes NVARCHAR (255)

GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensions
	@PortalId Int, 	@Addon NVarChar (50), 	@ExtensionKey NVarChar (50)
AS

SELECT
	[AccessRights],
	[Addon],
	[ControlToLoad],
	[Custom],
	[EntryTypes],
	[ExtensionKey],
	[Icon16],
	[Icon32],
	[IsPrivate],
	[MimeType],
	[Name],
	[PortalId],
	[ViewByDefault]
FROM
	{databaseOwner}{objectQualifier}DMX_Extensions
WHERE
	[PortalId] = @PortalId
	AND [Addon] = @Addon
	AND [ExtensionKey] = @ExtensionKey
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddExtensions
	@PortalId Int,
	@Addon NVarChar (50),
	@ExtensionKey NVarChar (50),
	@AccessRights NVarChar (255),
	@ControlToLoad NVarChar (255),
	@Custom NVarChar (50),
	@EntryTypes NVarChar (255),
	@Icon16 NVarChar (100),
	@Icon32 NVarChar (100),
	@IsPrivate Bit,
	@MimeType NVarChar (50),
	@Name NVarChar (50),
	@ViewByDefault Bit
AS

INSERT INTO {databaseOwner}{objectQualifier}DMX_Extensions (
	PortalId,
	Addon,
	ExtensionKey,
	AccessRights,
	ControlToLoad,
	Custom,
	EntryTypes,
	Icon16,
	Icon32,
	IsPrivate,
	MimeType,
	Name,
	ViewByDefault
) VALUES (
	@PortalId,
	@Addon,
	@ExtensionKey,
	@AccessRights,
	@ControlToLoad,
	@Custom,
	@EntryTypes,
	@Icon16,
	@Icon32,
	@IsPrivate,
	@MimeType,
	@Name,
	@ViewByDefault
)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateExtensions
	@PortalId Int,
	@Addon NVarChar (50),
	@ExtensionKey NVarChar (50),
	@AccessRights NVarChar (255),
	@ControlToLoad NVarChar (255),
	@Custom NVarChar (50),
	@EntryTypes NVarChar (255),
	@Icon16 NVarChar (100),
	@Icon32 NVarChar (100),
	@IsPrivate Bit,
	@MimeType NVarChar (50),
	@Name NVarChar (50),
	@ViewByDefault Bit
AS

UPDATE {databaseOwner}{objectQualifier}DMX_Extensions SET
	[AccessRights] = @AccessRights,
	[ControlToLoad] = @ControlToLoad,
	[Custom] = @Custom,
	[EntryTypes] = @EntryTypes,
	[Icon16] = @Icon16,
	[Icon32] = @Icon32,
	[IsPrivate] = @IsPrivate,
	[MimeType] = @MimeType,
	[Name] = @Name,
	[ViewByDefault] = @ViewByDefault
WHERE
	[PortalId] = @PortalId
	AND [Addon] = @Addon
	AND [ExtensionKey] = @ExtensionKey
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteExtensions
	@PortalId Int,
	@Addon NVarChar (50),
	@ExtensionKey NVarChar (50)
AS

DELETE FROM {databaseOwner}{objectQualifier}DMX_Extensions
WHERE
	[PortalId] = @PortalId
	AND [Addon] = @Addon
	AND [ExtensionKey] = @ExtensionKey
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindExtensionsByName
	@Name NVarChar (50),
        @PortalId int
AS

SELECT
	[AccessRights],
	[Addon],
	[ControlToLoad],
	[Custom],
	[EntryTypes],
	[ExtensionKey],
	[Icon16],
	[Icon32],
	[IsPrivate],
	[MimeType],
	[Name],
	[PortalId],
	[ViewByDefault]
FROM
	{databaseOwner}{objectQualifier}DMX_Extensions
WHERE
	[Name] = @Name
	AND [PortalId] = @PortalId
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensionsByAddons
	@Addon NVarChar (50)
AS

SELECT
	{databaseOwner}{objectQualifier}DMX_Extensions.AccessRights,
	{databaseOwner}{objectQualifier}DMX_Extensions.Addon,
	{databaseOwner}{objectQualifier}DMX_Extensions.ControlToLoad,
	{databaseOwner}{objectQualifier}DMX_Extensions.Custom,
	{databaseOwner}{objectQualifier}DMX_Extensions.EntryTypes,
	{databaseOwner}{objectQualifier}DMX_Extensions.ExtensionKey,
	{databaseOwner}{objectQualifier}DMX_Extensions.Icon16,
	{databaseOwner}{objectQualifier}DMX_Extensions.Icon32,
	{databaseOwner}{objectQualifier}DMX_Extensions.IsPrivate,
	{databaseOwner}{objectQualifier}DMX_Extensions.MimeType,
	{databaseOwner}{objectQualifier}DMX_Extensions.Name,
	{databaseOwner}{objectQualifier}DMX_Extensions.PortalId,
	{databaseOwner}{objectQualifier}DMX_Extensions.ViewByDefault, 
	{databaseOwner}{objectQualifier}DMX_Addons.Description AS AddonsDescription, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Addons INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Extensions.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Extensions.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Extensions.Addon = @Addon
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetExtensionsByPortals
	@PortalId Int
AS

SELECT
	{databaseOwner}{objectQualifier}DMX_Extensions.AccessRights,
	{databaseOwner}{objectQualifier}DMX_Extensions.Addon,
	{databaseOwner}{objectQualifier}DMX_Extensions.ControlToLoad,
	{databaseOwner}{objectQualifier}DMX_Extensions.Custom,
	{databaseOwner}{objectQualifier}DMX_Extensions.EntryTypes,
	{databaseOwner}{objectQualifier}DMX_Extensions.ExtensionKey,
	{databaseOwner}{objectQualifier}DMX_Extensions.Icon16,
	{databaseOwner}{objectQualifier}DMX_Extensions.Icon32,
	{databaseOwner}{objectQualifier}DMX_Extensions.IsPrivate,
	{databaseOwner}{objectQualifier}DMX_Extensions.MimeType,
	{databaseOwner}{objectQualifier}DMX_Extensions.Name,
	{databaseOwner}{objectQualifier}DMX_Extensions.PortalId,
	{databaseOwner}{objectQualifier}DMX_Extensions.ViewByDefault, 
	{databaseOwner}{objectQualifier}DMX_Addons.Description AS AddonsDescription, 
	{databaseOwner}{objectQualifier}Portals.Description AS PortalsDescription
FROM
	{databaseOwner}{objectQualifier}Portals INNER JOIN {databaseOwner}{objectQualifier}DMX_Addons INNER JOIN {databaseOwner}{objectQualifier}DMX_Extensions ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Extensions.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Extensions.PortalId
WHERE
	{databaseOwner}{objectQualifier}DMX_Extensions.PortalId = @PortalId
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

