/****** TABLES AND PRIMARY KEYS ******/

ALTER TABLE {databaseOwner}{objectQualifier}DMX_Attributes

ADD [ViewOrder] INT

GO



UPDATE {databaseOwner}{objectQualifier}DMX_Attributes

SET ViewOrder = 1



GO



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



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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributes

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributes

	@id Int,

        @PortalId int

AS



SELECT

	[Addon],

	[EntryTypes],

	[Explanation],

	[id],

	[IsPrivate],

	[Name],

	[PortalId],

	[ValueType],

	[ViewOrder]

FROM

	{databaseOwner}{objectQualifier}DMX_Attributes

WHERE

	[id] = @id

	AND [PortalId] = @PortalId

GO





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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttributes

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_AddAttributes

	@Addon NVarChar (50),

	@EntryTypes NVarChar (255),

	@Explanation NVarChar (255),

	@IsPrivate Bit,

	@Name NVarChar (50),

	@PortalId Int,

	@ValueType NVarChar (30),

	@ViewOrder Int

AS



INSERT INTO {databaseOwner}{objectQualifier}DMX_Attributes (

	[Addon],

	[EntryTypes],

	[Explanation],

	[IsPrivate],

	[Name],

	[PortalId],

	[ValueType],

	[ViewOrder]

) VALUES (

	@Addon,

	@EntryTypes,

	@Explanation,

	@IsPrivate,

	@Name,

	@PortalId,

	@ValueType,

	@ViewOrder

)



select SCOPE_IDENTITY()

GO





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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttributes

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_UpdateAttributes

	@id Int,

	@Addon NVarChar (50),

	@EntryTypes NVarChar (255),

	@Explanation NVarChar (255),

	@IsPrivate Bit,

	@Name NVarChar (50),

	@PortalId Int,

	@ValueType NVarChar (30),

	@ViewOrder Int

AS



UPDATE {databaseOwner}{objectQualifier}DMX_Attributes SET

	[Addon] = @Addon,

	[EntryTypes] = @EntryTypes,

	[Explanation] = @Explanation,

	[IsPrivate] = @IsPrivate,

	[Name] = @Name,

	[PortalId] = @PortalId,

	[ValueType] = @ValueType,

	[ViewOrder] = @ViewOrder

WHERE

	[id] = @id

GO





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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributes

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_DeleteAttributes

	@id Int

AS



DELETE FROM {databaseOwner}{objectQualifier}DMX_Attributes

WHERE

	[id] = @id

GO





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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributesByName

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_FindAttributesByName

	@Name NVarChar (50),

        @PortalId int

AS



SELECT

	[Addon],

	[EntryTypes],

	[Explanation],

	[id],

	[IsPrivate],

	[Name],

	[PortalId],

	[ValueType],

	[ViewOrder]

FROM

	{databaseOwner}{objectQualifier}DMX_Attributes

WHERE

	[Name] = @Name

	AND [PortalId] = @PortalId

GO





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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddons

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByAddons

	@Addon NVarChar (50)

AS



SELECT

	{databaseOwner}{objectQualifier}DMX_Attributes.Addon,

	{databaseOwner}{objectQualifier}DMX_Attributes.EntryTypes,

	{databaseOwner}{objectQualifier}DMX_Attributes.Explanation,

	{databaseOwner}{objectQualifier}DMX_Attributes.id,

	{databaseOwner}{objectQualifier}DMX_Attributes.IsPrivate,

	{databaseOwner}{objectQualifier}DMX_Attributes.Name,

	{databaseOwner}{objectQualifier}DMX_Attributes.PortalId,

	{databaseOwner}{objectQualifier}DMX_Attributes.ValueType,

	{databaseOwner}{objectQualifier}DMX_Attributes.ViewOrder, 

	{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_Attributes ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Attributes.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Attributes.PortalId

WHERE

	{databaseOwner}{objectQualifier}DMX_Attributes.Addon = @Addon

GO





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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortals

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetAttributesByPortals

	@PortalId Int

AS



SELECT

	{databaseOwner}{objectQualifier}DMX_Attributes.Addon,

	{databaseOwner}{objectQualifier}DMX_Attributes.EntryTypes,

	{databaseOwner}{objectQualifier}DMX_Attributes.Explanation,

	{databaseOwner}{objectQualifier}DMX_Attributes.id,

	{databaseOwner}{objectQualifier}DMX_Attributes.IsPrivate,

	{databaseOwner}{objectQualifier}DMX_Attributes.Name,

	{databaseOwner}{objectQualifier}DMX_Attributes.PortalId,

	{databaseOwner}{objectQualifier}DMX_Attributes.ValueType,

	{databaseOwner}{objectQualifier}DMX_Attributes.ViewOrder, 

	{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_Attributes ON {databaseOwner}{objectQualifier}DMX_Addons.AddonKey = {databaseOwner}{objectQualifier}DMX_Attributes.Addon ON {databaseOwner}{objectQualifier}Portals.PortalID = {databaseOwner}{objectQualifier}DMX_Attributes.PortalId

WHERE

	{databaseOwner}{objectQualifier}DMX_Attributes.PortalId = @PortalId

ORDER BY

	{databaseOwner}{objectQualifier}DMX_Attributes.ViewOrder



GO





/****** OTHER PROCEDURES ******/

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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeList

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetEntryAttributeList

  @PortalId Int, @EntryId Int

AS



SELECT {objectQualifier}DMX_Attributes.*, {objectQualifier}DMX_EntryAttributes.id AS EntryAttributeId, {objectQualifier}DMX_EntryAttributes.Value

FROM {objectQualifier}DMX_Attributes LEFT OUTER JOIN {objectQualifier}DMX_EntryAttributes ON {objectQualifier}DMX_Attributes.id={objectQualifier}DMX_EntryAttributes.Attribute

WHERE ({objectQualifier}DMX_Attributes.PortalId=@PortalId)

 AND ({objectQualifier}DMX_EntryAttributes.EntryId=@EntryId)

ORDER BY {objectQualifier}DMX_Attributes.ViewOrder



GO





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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNrOfAttributes

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetNrOfAttributes

  @PortalId Int

AS



SELECT Max({objectQualifier}DMX_Attributes.ViewOrder) AS MaxOfViewOrder

FROM {objectQualifier}DMX_Attributes

GROUP BY {objectQualifier}DMX_Attributes.IsPrivate, {objectQualifier}DMX_Attributes.PortalId

HAVING (({objectQualifier}DMX_Attributes.IsPrivate=0) AND ({objectQualifier}DMX_Attributes.PortalId=@PortalId))



GO









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

DROP PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCompletePortalEntryList

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DMX_GetCompletePortalEntryList

  @PortalId Int, @AllVersions Bit

AS



SELECT {objectQualifier}DMX_Entries.*, Entries_1.Description AS CollectionName, {objectQualifier}DMX_Extensions.Icon16

FROM ({objectQualifier}DMX_Entries LEFT JOIN {objectQualifier}DMX_Entries AS Entries_1 ON {objectQualifier}DMX_Entries.CollectionId = Entries_1.EntryId) INNER JOIN {objectQualifier}DMX_Extensions ON ({objectQualifier}DMX_Entries.EntryType = {objectQualifier}DMX_Extensions.ExtensionKey) AND ({objectQualifier}DMX_Entries.PortalId = {objectQualifier}DMX_Extensions.PortalId)

WHERE ({objectQualifier}DMX_Entries.PortalId = @PortalId)

AND (({objectQualifier}DMX_Entries.EntryId = {objectQualifier}DMX_Entries.LastVersionId) OR (@AllVersions = 1))





GO





