/* =====================================================================*/

/***** Initialization Script 03.01.02 *****/

/* =====================================================================*/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmAddRepositoryAttributeValues') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmAddRepositoryAttributeValues

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmAddRepositoryAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmAddRepositoryAttributes

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmAddRepositoryCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmAddRepositoryCategory

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmAddRepositoryComment') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmAddRepositoryComment

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmAddRepositoryObject') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmAddRepositoryObject

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmApproveRepositoryObject') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmApproveRepositoryObject

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmDeleteRepositoryAttributeValues') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmDeleteRepositoryAttributeValues

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmDeleteRepositoryAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmDeleteRepositoryAttributes

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmDeleteRepositoryCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmDeleteRepositoryCategory

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmDeleteRepositoryComment') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmDeleteRepositoryComment

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmDeleteRepositoryObject') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmDeleteRepositoryObject

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetRepositoryAttributeValues') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetRepositoryAttributeValues

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetRepositoryAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetRepositoryAttributes

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetRepositoryCategories') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetRepositoryCategories

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetRepositoryComments') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetRepositoryComments

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetRepositoryModules') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetRepositoryModules

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetRepositoryObjects') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetRepositoryObjects

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetSingleRepositoryAttributeValues') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetSingleRepositoryAttributeValues

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetSingleRepositoryAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetSingleRepositoryAttributes

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetSingleRepositoryCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetSingleRepositoryCategory

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetSingleRepositoryComment') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetSingleRepositoryComment

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmGetSingleRepositoryObject') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmGetSingleRepositoryObject

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmUpdateRepositoryAttributeValues') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryAttributeValues

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmUpdateRepositoryAttributes') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryAttributes

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmUpdateRepositoryCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryCategory

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmUpdateRepositoryClicks') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryClicks

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmUpdateRepositoryComment') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryComment

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmUpdateRepositoryObject') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryObject

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmUpdateRepositoryRating') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryRating

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmCheckAllAttributes') and xtype in (N'FN', N'IF', N'TF'))

drop function {databaseOwner}{objectQualifier}grmCheckAllAttributes

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmRepositoryAttributeValues') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE {databaseOwner}{objectQualifier}grmRepositoryAttributeValues (

[ItemID] [int] IDENTITY (1, 1) NOT NULL ,

[AttributeID] [int] NULL ,

[ValueName] [nvarchar] (50) NULL

)

GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'PK_{objectQualifier}grmRepositoryAttributeValues')

CREATE UNIQUE CLUSTERED INDEX [PK_{objectQualifier}grmRepositoryAttributeValues] ON

{databaseOwner}{objectQualifier}grmRepositoryAttributeValues([ItemID])

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}grmRepositoryAttributes') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE {databaseOwner}{objectQualifier}grmRepositoryAttributes (

[ItemID] [int] IDENTITY (1, 1) NOT NULL ,

[ModuleID] [int] NOT NULL ,

[AttributeName] [nvarchar] (50) NULL

)

GO

IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 'PK_{objectQualifier}grmRepositoryAttributes')

CREATE UNIQUE CLUSTERED INDEX [PK_{objectQualifier}grmRepositoryAttributes] ON

{databaseOwner}{objectQualifier}grmRepositoryAttributes([ItemID])

GO

IF (SELECT COLUMNPROPERTY( OBJECT_ID('{databaseOwner}{objectQualifier}grmRepositoryObjects'),'Attributes','AllowsNull')) IS NULL

ALTER TABLE {databaseOwner}{objectQualifier}grmRepositoryObjects ADD [Attributes] nvarchar (150) NULL

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE FUNCTION {databaseOwner}{objectQualifier}grmCheckAllAttributes (

@tcList VARCHAR(150),

@tcDelimiter char(1),

@tcValues VARCHAR(150)

)

RETURNS INT

AS

BEGIN

DECLARE

@lcListWork varchar(150),

@lnCommaPos int,

@lcItem varchar(150),

@hasAttributes int

SET @lcListWork = @tcList

SET @hasAttributes = 1

WHILE LEN(@lcListWork) > 0

BEGIN

SET @lnCommaPos = CHARINDEX(@tcDelimiter, @lcListWork)

IF @lnCommaPos > 0

BEGIN

SET @lcItem = SUBSTRING(@lcListWork, 1, @lnCommaPos - 1)

SET @lcListWork = SUBSTRING(@lcListWork, @lnCommaPos + 1, LEN(@lcListWork) - @lnCommaPos)

END

ELSE

BEGIN

SET @lcItem = @lcListWork

SET @lcListWork = ''

END

IF @lcItem <> '' AND (CHARINDEX(';' + @lcItem + ';',@tcValues)=0)

SET @hasAttributes = 0

END

RETURN @hasAttributes

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmAddRepositoryAttributeValues

(

@AttributeID int = NULL,

@ValueName nvarchar(50) = NULL

)

AS

BEGIN

INSERT INTO {databaseOwner}{objectQualifier}grmRepositoryAttributeValues

(

[AttributeID],

[ValueName]

)

VALUES

(

@AttributeID,

@ValueName

)

SELECT CAST(SCOPE_IDENTITY() as INT)

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmAddRepositoryAttributes

(

@ModuleID int,

@AttributeName nvarchar(50) = NULL

)

AS

BEGIN

INSERT INTO {databaseOwner}{objectQualifier}grmRepositoryAttributes

(

[ModuleID],

[AttributeName]

)

VALUES

(

@ModuleID,

@AttributeName

)

SELECT CAST(SCOPE_IDENTITY() as INT)

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/* =====================================================================*/

/* Create the stored procedures */

/* =====================================================================*/

CREATE procedure {databaseOwner}{objectQualifier}grmAddRepositoryCategory

@ModuleID int,

@CategoryName nvarchar(100),

@ViewOrder int

as

insert into {databaseOwner}{objectQualifier}grmRepositoryCategories (

ModuleID,

Category,

ViewOrder

)

values (

@ModuleID,

@CategoryName,

@ViewOrder

)

select SCOPE_IDENTITY()

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmAddRepositoryComment

@ObjectID int,

@UserName nvarchar(100),

@Comment ntext

as

insert into {databaseOwner}{objectQualifier}grmRepositoryComments (

ObjectID,

CreatedByUser,

CreatedDate,

Comment

)

values (

@ObjectID,

@UserName,

getdate(),

@Comment

)

select SCOPE_IDENTITY()

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmAddRepositoryObject

@UserName nvarchar(150),

@ModuleID int,

@Name nvarchar(50),

@Description ntext,

@Author nvarchar(150),

@AuthorEMail nvarchar(150),

@FileSize nvarchar(12),

@PreviewImage nvarchar(150),

@Image nvarchar(150),

@FileName nvarchar(150),

@Approved int,

@ShowEMail int,

@CategoryId nvarchar(50),

@Summary ntext,

@Attributes ntext

as

insert into {databaseOwner}{objectQualifier}grmRepositoryObjects (

CreatedByUser,

CreatedDate,

UpdatedByUser,

UpdatedDate,

ModuleID,

[Name],

[Description],

Author,

AuthorEMail,

FileSize,

Downloads,

PreviewImage,

[Image],

[FileName],

Clicks,

RatingVotes,

RatingTotal,

RatingAverage,

Approved,

ShowEMail,

CategoryId,

Summary,

Attributes

)

values (

@UserName,

getdate(),

@UserName,

getdate(),

@ModuleID,

@Name,

@Description,

@Author,

@AuthorEMail,

@FileSize,

0,

@PreviewImage,

@Image,

@FileName,

0,

0,

0,

0,

@Approved,

@ShowEMail,

@CategoryId,

@Summary,

@Attributes

)

select SCOPE_IDENTITY()

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

create procedure {databaseOwner}{objectQualifier}grmApproveRepositoryObject

@ItemID int

as

update {databaseOwner}{objectQualifier}grmRepositoryObjects

set

Approved = 1

where

ItemID = @ItemID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmDeleteRepositoryAttributeValues

(

@ItemID int

)

AS

BEGIN

DELETE FROM

{databaseOwner}{objectQualifier}grmRepositoryAttributeValues

WHERE

[ItemID] = @ItemID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmDeleteRepositoryAttributes

(

@ItemID int

)

AS

BEGIN

DELETE FROM

{databaseOwner}{objectQualifier}grmRepositoryAttributes

WHERE

[ItemID] = @ItemID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmDeleteRepositoryCategory

@ItemID int

as

delete

from {databaseOwner}{objectQualifier}grmRepositoryCategories

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 {databaseOwner}{objectQualifier}grmDeleteRepositoryComment

@ItemID int

as

delete

from {databaseOwner}{objectQualifier}grmRepositoryComments

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 {databaseOwner}{objectQualifier}grmDeleteRepositoryObject

@ItemID int

as

BEGIN

delete

from {databaseOwner}{objectQualifier}grmRepositoryComments

where ObjectID = @ItemID

delete

from {databaseOwner}{objectQualifier}grmRepositoryObjects

where ItemID = @ItemID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmGetRepositoryAttributeValues

(

@AttributeID int

)

AS

BEGIN

SELECT

[ItemID],

[AttributeID],

[ValueName]

FROM

{databaseOwner}{objectQualifier}grmRepositoryAttributeValues

WHERE

[AttributeID] = @AttributeID

ORDER BY

[ValueName]

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmGetRepositoryAttributes

(

@ModuleID int

)

AS

BEGIN

SELECT

[ItemID],

[ModuleID],

[AttributeName]

FROM

{databaseOwner}{objectQualifier}grmRepositoryAttributes

WHERE

[ModuleID] = @ModuleID

ORDER BY

[AttributeName]

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmGetRepositoryCategories

@ModuleID int

as

select

*,

(select count(ItemId)

from {databaseOwner}{objectQualifier}grmRepositoryObjects

where CHARINDEX(CONVERT(varchar, {databaseOwner}{objectQualifier}grmRepositoryCategories.ItemID), {databaseOwner}{objectQualifier}grmRepositoryObjects.CategoryId)>0) as Count

from

{databaseOwner}{objectQualifier}grmRepositoryCategories

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 {databaseOwner}{objectQualifier}grmGetRepositoryComments

@ItemID int

as

select *

from {databaseOwner}{objectQualifier}grmRepositoryComments

where ObjectID = @ItemID

order by CreatedDate desc

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmGetRepositoryModules

@PortalId int

as

SELECT

{databaseOwner}{objectQualifier}TabModules.TabID AS TabID,

{databaseOwner}{objectQualifier}Modules.*,

{databaseOwner}{objectQualifier}Modules.PortalID AS PortalID

FROM

{databaseOwner}{objectQualifier}ModuleDefinitions INNER JOIN

{databaseOwner}{objectQualifier}Modules ON {databaseOwner}{objectQualifier}ModuleDefinitions.ModuleDefID = {databaseOwner}{objectQualifier}Modules.ModuleDefID INNER JOIN

{databaseOwner}{objectQualifier}TabModules ON {databaseOwner}{objectQualifier}Modules.ModuleID = {databaseOwner}{objectQualifier}TabModules.ModuleID

WHERE

({databaseOwner}{objectQualifier}ModuleDefinitions.FriendlyName = N'Repository') AND ({databaseOwner}{objectQualifier}Modules.PortalID = @PortalId)

and {databaseOwner}{objectQualifier}Modules.IsDeleted=0

order by

TabId

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmGetRepositoryObjects

(

@ModuleID int,

@sFilter nvarchar(256),

@sSort nvarchar(100),

@Approved int,

@CategoryId int,

@Attributes nvarchar(150),

@RowCount int

)

AS

BEGIN

IF @RowCount > 0

SET ROWCOUNT @RowCount

END

SELECT

*,

(SELECT COUNT({databaseOwner}{objectQualifier}grmRepositoryComments.ItemID) FROM {databaseOwner}{objectQualifier}grmRepositoryComments

WHERE {databaseOwner}{objectQualifier}grmRepositoryComments.ObjectID={databaseOwner}{objectQualifier}grmRepositoryObjects.ItemID) AS CommentCount

FROM {databaseOwner}{objectQualifier}grmRepositoryObjects

WHERE ModuleID = @ModuleID

AND (Approved = @Approved)

AND (@CategoryId = -1 OR CHARINDEX(';' + CONVERT(varchar, @CategoryId) + ';',CategoryId)>0)

AND (@Attributes = '' OR {databaseOwner}{objectQualifier}grmCheckAllAttributes(@Attributes,';',{databaseOwner}{objectQualifier}grmRepositoryObjects.Attributes) = 1)

AND

(

@sFilter = ''

OR UPPER(Name) LIKE UPPER('%' + @sFilter + '%')

OR UPPER(Author) LIKE UPPER('%' + @sFilter + '%')

OR UPPER(AuthorEMail) LIKE UPPER('%' + @sFilter + '%')

OR Description LIKE '%' + @sFilter + '%'

)

ORDER BY

CASE @sSort

WHEN 'Name' THEN Name

WHEN 'Author' THEN Author

END ASC,

CASE @sSort

WHEN 'Downloads' THEN Downloads

WHEN 'RatingAverage' THEN RatingAverage

END DESC,

CASE @sSort

WHEN 'UpdatedDate' THEN UpdatedDate

END DESC

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmGetSingleRepositoryAttributeValues

(

@ItemID int

)

AS

BEGIN

SELECT

[ItemID],

[AttributeID],

[ValueName]

FROM

{databaseOwner}{objectQualifier}grmRepositoryAttributeValues

WHERE

[ItemID] = @ItemID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmGetSingleRepositoryAttributes

(

@ItemID int

)

AS

BEGIN

SELECT

[ItemID],

[ModuleID],

[AttributeName]

FROM

{databaseOwner}{objectQualifier}grmRepositoryAttributes

WHERE

[ItemID] = @itemID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmGetSingleRepositoryCategory

@ItemID int

as

select *

from {databaseOwner}{objectQualifier}grmRepositoryCategories

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 {databaseOwner}{objectQualifier}grmGetSingleRepositoryComment

@ItemID int

as

select *

from {databaseOwner}{objectQualifier}grmRepositoryComments

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 {databaseOwner}{objectQualifier}grmGetSingleRepositoryObject

@ItemID int

as

select *

from {databaseOwner}{objectQualifier}grmRepositoryObjects

where ItemID = @ItemID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmUpdateRepositoryAttributeValues

(

@ItemID int,

@AttributeID int,

@ValueName nvarchar(50)

)

AS

BEGIN

UPDATE {databaseOwner}{objectQualifier}grmRepositoryAttributeValues

SET

[AttributeID] = @AttributeID,

[ValueName] = @ValueName

WHERE

[ItemID] = @ItemID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}grmUpdateRepositoryAttributes

(

@ItemID int,

@ModuleID int,

@AttributeName nvarchar(50)

)

AS

BEGIN

UPDATE {databaseOwner}{objectQualifier}grmRepositoryAttributes

SET

[ModuleID] = @ModuleID,

[AttributeName] = @AttributeName

WHERE

[ItemID] = @ItemID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE procedure {databaseOwner}{objectQualifier}grmUpdateRepositoryCategory

@ItemID int,

@CategoryName nvarchar(100),

@ViewOrder int

as

update {databaseOwner}{objectQualifier}grmRepositoryCategories

set

Category = @CategoryName,

ViewOrder = @ViewOrder

where ItemID = @ItemID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO