03.01.12.SqlDataProvider

/* =====================================================================*/
/*****              Initialization Script  03.01.12        *****/
/* =====================================================================*/

/****** Object:  Table grmRepositoryObjects ******/
/****** increase Name column to 150 characters  ******/
ALTER TABLE {databaseOwner}{objectQualifier}grmRepositoryObjects ALTER COLUMN name nvarchar(150)
GO

/****** Object:  Table grmRepositoryCategories ******/
/****** add Parent column  ******/

IF (SELECT COLUMNPROPERTY( OBJECT_ID('{databaseOwner}{objectQualifier}grmRepositoryCategories'),'Parent','AllowsNull')) IS NULL 
ALTER TABLE {databaseOwner}{objectQualifier}grmRepositoryCategories ADD [Parent] [int] NULL
GO

UPDATE {databaseOwner}{objectQualifier}grmRepositoryCategories SET Parent = -1
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DF_grmRepositoryCategories_Parent]') and OBJECTPROPERTY(id, N'IsDefaultCnst') = 1)
ALTER TABLE {databaseOwner}[{objectQualifier}grmRepositoryCategories] WITH NOCHECK ADD CONSTRAINT [DF_grmRepositoryCategories_Parent] DEFAULT ((-1)) FOR [Parent]
GO

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}grmAddRepositoryObjectCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmAddRepositoryObjectCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}grmAddRepositoryObjectValues]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmAddRepositoryObjectValues]
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}grmChangeRepositoryModuleDefId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmChangeRepositoryModuleDefId]
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}grmDeleteRepositoryModuleDefId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmDeleteRepositoryModuleDefId]
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}grmDeleteRepositoryObjectCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmDeleteRepositoryObjectCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}grmDeleteRepositoryObjectValues]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmDeleteRepositoryObjectValues]
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}grmGetRepositoryObjectCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmGetRepositoryObjectCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}grmGetRepositoryObjectValues]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmGetRepositoryObjectValues]
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}grmGetSingleRepositoryObjectCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmGetSingleRepositoryObjectCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}grmGetSingleRepositoryObjectValues]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmGetSingleRepositoryObjectValues]
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}grmUpdateRepositoryObjectCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmUpdateRepositoryObjectCategories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}grmUpdateRepositoryObjectValues]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}grmUpdateRepositoryObjectValues]
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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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 ON 
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),
@Parent int,
@ViewOrder int

as

insert into {databaseOwner}{objectQualifier}grmRepositoryCategories (
  ModuleID,
  Category,
  Parent,
  ViewOrder
)
values (
  @ModuleID,
  @CategoryName,
  @Parent,
  @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(150),
@Description	ntext,
@Author	nvarchar(150),
@AuthorEMail	nvarchar(150),
@FileSize	nvarchar(12),
@PreviewImage	nvarchar(150),
@Image	nvarchar(150),
@FileName	nvarchar(150),
@Approved	int,
@ShowEMail	int,
@Summary	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,
  Summary
)
values (
  @UserName,
  getdate(),
  @UserName,
  getdate(),
  @ModuleID,
  @Name,
  @Description,
  @Author,
  @AuthorEMail,
  @FileSize,
   0,
  @PreviewImage,
  @Image,
  @FileName,
  0,
  0,
  0,
  0,
  @Approved,
  @ShowEMail,
  @Summary
)

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}grmAddRepositoryObjectCategories
(
	@ObjectID int = NULL,
	@CategoryID int = NULL
)
AS
BEGIN


	INSERT INTO	{databaseOwner}{objectQualifier}grmRepositoryObjectCategories
	(
		[ObjectID],
		[CategoryID]
	)
	VALUES
	(
		@ObjectID,
		@CategoryID
	)

	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 PROCEDURE {databaseOwner}{objectQualifier}grmAddRepositoryObjectValues
(
	@ObjectID int = NULL,
	@ValueID int = NULL
)
AS
BEGIN


	INSERT INTO	{databaseOwner}{objectQualifier}grmRepositoryObjectValues
	(
		[ObjectID],
		[ValueID]
	)
	VALUES
	(
		@ObjectID,
		@ValueID
	)

	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 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 ON 
GO


CREATE procedure {databaseOwner}{objectQualifier}grmChangeRepositoryModuleDefId
(
@ModuleId	int,
@OldValue	int,
@NewValue	int
)
as

UPDATE {databaseOwner}{objectQualifier}Modules
SET ModuleDefId = @NewValue
WHERE ModuleId=@ModuleId AND ModuleDefId=@OldValue

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}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 ON 
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}grmDeleteRepositoryModuleDefId

@ModuleDefId	int

as

BEGIN
	DELETE FROM {databaseOwner}{objectQualifier}ModuleDefinitions
	WHERE ModuleDefId = @ModuleDefId
	
	DELETE FROM {databaseOwner}{objectQualifier}ModuleControls
	WHERE ModuleDefId = @ModuleDefId

	DELETE FROM {databaseOwner}{objectQualifier}esktopModules
	WHERE FriendlyName = 'Gooddogs Repository' OR FriendlyName = 'Gooddogs Dashboard'
	
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}grmDeleteRepositoryObject

@ItemID int

as

BEGIN 

delete
from {databaseOwner}{objectQualifier}grmRepositoryComments
where ObjectID = @ItemID

delete
from   {databaseOwner}{objectQualifier}grmRepositoryObjects
where  ItemID = @ItemID

delete
from   {databaseOwner}{objectQualifier}grmRepositoryObjectCategories
where  ObjectID = @ItemID

delete
from   {databaseOwner}{objectQualifier}grmRepositoryObjectValues
where  ObjectID = @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}grmDeleteRepositoryObjectCategories
(
	@ObjectID int
)
AS
BEGIN

	DELETE FROM
		{databaseOwner}{objectQualifier}grmRepositoryObjectCategories
	WHERE
		[ObjectID] = @ObjectID
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}grmDeleteRepositoryObjectValues
(
	@ObjectID int
)
AS
BEGIN

	DELETE FROM
		{databaseOwner}{objectQualifier}grmRepositoryObjectValues
	WHERE
		[ObjectID] = @ObjectID
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}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 ON 
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 OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE procedure {databaseOwner}{objectQualifier}grmGetRepositoryCategories

@ModuleID int,
@RootID int

as

select 
    *, {databaseOwner}{objectQualifier}grmGetCategoryItemCount(ItemID) as Count

from 
    {databaseOwner}{objectQualifier}grmRepositoryCategories

where
    ModuleID = @ModuleID
    AND
    Parent = @RootID

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}grmGetRepositoryObjectCategories
(
	@ObjectID int
)
AS
BEGIN

	SELECT
		[ItemID],
		[ObjectID],
		[CategoryID]
	FROM 
		{databaseOwner}{objectQualifier}grmRepositoryObjectCategories
	WHERE
		[ObjectID] = @ObjectID
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}grmGetRepositoryObjectValues
(
	@ObjectID int
)
AS
BEGIN

	SELECT
		[ItemID],
		[ObjectID],
		[ValueID]
	FROM 
		{databaseOwner}{objectQualifier}grmRepositoryObjectValues
	WHERE
		[ObjectID] = @ObjectID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
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

DECLARE @sql NVARCHAR(4000)
DECLARE @SearchToken nvarchar(128)
DECLARE @SearchClause nvarchar(50)

BEGIN
SELECT  @sql = 'SELECT a.*, (SELECT COUNT({databaseOwner}{objectQualifier}grmRepositoryComments.ItemID) FROM {databaseOwner}{objectQualifier}grmRepositoryComments ' +
 		'WHERE {databaseOwner}{objectQualifier}grmRepositoryComments.ObjectID=a.ItemID) AS CommentCount ' 

IF @CategoryId = -1
BEGIN
	SET @sql = @sql + 'FROM {databaseOwner}{objectQualifier}grmRepositoryObjects a ' 
END
ELSE
BEGIN
	SET @sql = @sql + 'FROM {databaseOwner}{objectQualifier}grmRepositoryObjects a, {databaseOwner}{objectQualifier}grmRepositoryObjectCategories b ' 
END

SET @sql = @sql +
		'WHERE a.ModuleID = ' + CONVERT(VARCHAR, @ModuleID) + ' ' +
		'AND ( CONVERT(VARCHAR,a.Approved) = ' + CONVERT(VARCHAR, @Approved) + ') '  +
		'AND ( ''' + @Attributes + ''' = '''' OR {databaseOwner}{objectQualifier}grmCheckAllAttributes(''' + @Attributes + ''','';'', a.ItemID) = 1) ' 

IF @CategoryId <> -1
BEGIN
	SET @sql = @sql + 'AND (a.ItemID = b.ObjectID AND b.CategoryId = ' + CONVERT(VARCHAR, @CategoryId) + ') ' 
END

IF @sFilter <> ''
BEGIN
	SET @sql = @sql + 'AND ( ' 

	-- inject a set of search filters for each search word or phrase 
	DECLARE TCUR CURSOR FOR
	SELECT [Value] FROM {databaseOwner}{objectQualifier}grmParseDelimitedStrings(@sFilter, '|')
	SET @SearchClause = 'false'
	
	OPEN TCUR
	FETCH NEXT FROM TCUR INTO @SearchToken
	    WHILE @@FETCH_STATUS = 0
	    BEGIN
	   	IF @SearchClause <> 'true'
		BEGIN
		        SET @SearchClause = 'true'
		        SET @sql = @sql +
			'(( a.Description like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.Summary like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
			' OR ( a.Name like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.FileName like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
			' OR ( a.Author like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.AuthorEmail like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
			' ) ' 
		END
		ELSE
		BEGIN
		        SET @sql = @sql +
			' OR (( a.Description like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.Summary like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
			' OR ( a.Name like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.FileName like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
			' OR ( a.Author like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) OR ( a.AuthorEmail like ''%' + CONVERT(VARCHAR, @SearchToken) + '%'' ) ' +
			' ) ' 
		END
	        FETCH NEXT FROM TCUR INTO @SearchToken
	    END  
	CLOSE TCUR
	DEALLOCATE TCUR

	SET @sql = @sql + ' ) '

END

IF @sSort = 'Name'
BEGIN
	SET @sql = @sql + 'ORDER BY a.Name ASC '
END

IF @sSort = 'Author'
BEGIN
	SET @sql = @sql + 'ORDER BY a.Author ASC '
END

IF @sSort = 'Downloads'
BEGIN
	SET @sql = @sql + 'ORDER BY a.Downloads DESC '
END

IF @sSort = 'RatingAverage'
BEGIN
	SET @sql = @sql + 'ORDER BY a.RatingAverage DESC '
END

IF @sSort = 'UpdatedDate'
BEGIN
	SET @sql = @sql + 'ORDER BY a.UpdatedDate DESC '
END

EXEC(@sql)
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}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 ON 
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 
    *, {databaseOwner}{objectQualifier}grmGetCategoryItemCount(ItemID) as Count

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 ON 
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}grmGetSingleRepositoryObjectCategories
(
	@ObjectID int,
	@CategoryID int
)
AS
BEGIN

	SELECT
		[ItemID],
		[ObjectID],
		[CategoryID]
	FROM 
		{databaseOwner}{objectQualifier}grmRepositoryObjectCategories
	WHERE
		[ObjectID] = @ObjectID AND [CategoryId] = @CategoryId
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}grmGetSingleRepositoryObjectValues
(
	@ObjectID int,
	@ValueID int
)
AS
BEGIN

	SELECT
		[ItemID],
		[ObjectID],
		[ValueID]
	FROM 
		{databaseOwner}{objectQualifier}grmRepositoryObjectValues
	WHERE
		[ObjectID] = @ObjectID AND [ValueID] = @ValueID
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}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 ON 
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),
@Parent		int,
@ViewOrder		int
as

update {databaseOwner}{objectQualifier}grmRepositoryCategories
set
  Category = @CategoryName,
  Parent = @Parent,
  ViewOrder = @ViewOrder

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}grmUpdateRepositoryClicks

@ObjectId    int

as

declare @SQL nvarchar(200)

select @SQL = 'update {databaseOwner}{objectQualifier}grmRepositoryObjects set Downloads = Downloads + 1 where ItemID = ' + convert(varchar,@ObjectId)

EXEC sp_executesql @SQL




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}grmUpdateRepositoryComment

@ItemID		int,
@Username    nvarchar(100),
@Comment   ntext
as

update {databaseOwner}{objectQualifier}grmRepositoryComments
set
  CreatedDate = getdate(),
  CreatedByUser = @Username,
  Comment = @Comment

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}grmUpdateRepositoryObject

@ItemID		int,
@UserName	nvarchar(150),
@Name		nvarchar(150),
@Description	ntext,
@Author		nvarchar(150),
@AuthorEMail	nvarchar(150),
@FileSize		nvarchar(12),
@PreviewImage	nvarchar(150),
@Image		nvarchar(150),
@FileName	nvarchar(150),
@Approved	int,
@ShowEMail	int,
@Summary	ntext

as

update {databaseOwner}{objectQualifier}grmRepositoryObjects
set
  UpdatedByUser = @UserName,
  UpdatedDate = getdate(),
  [Name] = @Name,
  [Description] = @Description,
  Author = @Author,
  AuthorEMail = @AuthorEMail,
  FileSize = @FileSize,
  PreviewImage = @PreviewImage,
  [Image] = @Image,
  [FileName] = @FileName,
  Approved = @Approved,
  ShowEMail = @ShowEMail,
  Summary = @Summary

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}grmUpdateRepositoryObjectCategories
(
	@ItemID int,
	@ObjectID int,
	@CategoryID int
)
AS
BEGIN

	UPDATE {databaseOwner}{objectQualifier}grmRepositoryObjectCategories
	SET
		[ObjectID] = @ObjectID,
		[CategoryID] = @CategoryID
	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}grmUpdateRepositoryObjectValues
(
	@ItemID int,
	@ObjectID int,
	@ValueID int
)
AS
BEGIN

	UPDATE {databaseOwner}{objectQualifier}grmRepositoryObjectValues
	SET
		[ObjectID] = @ObjectID,
		[ValueID] = @ValueID
	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}grmUpdateRepositoryRating

@ObjectId	int,
@Rating	int

as

BEGIN

declare @SQL nvarchar(200)
select @SQL = 'update {databaseOwner}{objectQualifier}grmRepositoryObjects set RatingVotes = RatingVotes + 1 where ItemID = ' + convert(varchar,@ObjectId)
EXEC sp_executesql @SQL

select @SQL = 'update {databaseOwner}{objectQualifier}grmRepositoryObjects set RatingTotal = RatingTotal + ' + convert(varchar,@Rating) + ' where ItemID = ' + convert(varchar,@ObjectId)
EXEC sp_executesql @SQL

select @SQL = 'update {databaseOwner}{objectQualifier}grmRepositoryObjects set RatingAverage = RatingTotal / RatingVotes where ItemID = ' + convert(varchar,@ObjectId)
EXEC sp_executesql @SQL

END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/*****************************************************/
/*  USER DEFINED FUNCTIONS                           */
/*****************************************************/

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE Function {databaseOwner}{objectQualifier}grmParseDelimitedStrings
(
@String nvarchar(3500), 
@Delimiter char(1)
)
Returns @Values Table 
(
	RowId int Not Null Identity(1,1) Primary Key,
	Value nvarchar(255) Not Null
)
As
Begin
	Declare @startPos smallint,@endPos smallint
	If (Right(@String, 1) != @Delimiter)
		Set @String = @String + @Delimiter
		Set @startPos = 1
		Set @endPos = CharIndex(@Delimiter, @String)
		While @endPos > 0
		 Begin
			Insert @Values(Value)
			Select LTrim(RTrim(SubString(@String, @startPos, @endPos - @startPos)))
			-- remove the delimiter just used
			Set @String = Stuff(@String, @endPos, 1, '')
			-- move string pointer to next delimiter
			Set @startPos = @endPos
			Set @endPos = CharIndex(@Delimiter, @String)
		End
		Return
End

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO