/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

/** Change CreatedByUser column to an int for performance **/

ALTER TABLE {databaseOwner}{objectQualifier}Surveys ADD
	CreatedByUserID int NULL
GO

update {databaseOwner}{objectQualifier}Surveys
set    CreatedByUserID = convert(int,CreatedByUser)
GO

ALTER TABLE {databaseOwner}{objectQualifier}Surveys
	DROP COLUMN CreatedByUser
GO

ALTER TABLE {databaseOwner}{objectQualifier}Surveys ADD
	CreatedByUser int NOT NULL CONSTRAINT DF_{objectQualifier}Surveys_CreatedByUser DEFAULT 0
GO

update {databaseOwner}{objectQualifier}Surveys
set    CreatedByUser = CreatedByUserID
GO

ALTER TABLE {databaseOwner}{objectQualifier}Surveys
	DROP COLUMN CreatedByUserID
GO

ALTER TABLE {databaseOwner}{objectQualifier}Surveys
	DROP CONSTRAINT DF_{objectQualifier}Surveys_CreatedByUser
GO

/** Drop Existing Stored Procedures **/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}AddSurvey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}AddSurvey
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetSurvey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}GetSurvey
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UpdateSurvey]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}UpdateSurvey
GO

/** Create Optimized Stored Procedures **/

create procedure {databaseOwner}{objectQualifier}AddSurvey

@ModuleID      int,
@Question      nvarchar(500),
@ViewOrder     int,
@OptionType    char(1),
@UserID        int

as

insert into {objectQualifier}Surveys ( 
  ModuleID,
  Question,
  ViewOrder,
  OptionType,
  CreatedByUser,
  CreatedDate
)
values (
  @ModuleID,
  @Question,
  @ViewOrder,
  @OptionType,
  @UserID,
  getdate()
)

select SCOPE_IDENTITY()

GO

create procedure {databaseOwner}{objectQualifier}GetSurvey

@SurveyID int,
@ModuleID int

as

select SurveyID,
       ModuleID,
       Question,
       ViewOrder,
       OptionType,
       CreatedByUser,
       CreatedDate,
       'Votes' = ( select sum(Votes) from {objectQualifier}SurveyOptions where {objectQualifier}SurveyOptions.SurveyID = {objectQualifier}Surveys.SurveyID )
from   {objectQualifier}Surveys
where  SurveyID = @SurveyID
and    ModuleID = @ModuleID

GO

create procedure {databaseOwner}{objectQualifier}UpdateSurvey

@SurveyID       int,
@Question       nvarchar(500),
@ViewOrder      int,
@OptionType     char(1),
@UserID         int

as

update {objectQualifier}Surveys
set    Question = @Question,
       ViewOrder = @ViewOrder,
       OptionType = @OptionType,
       CreatedByUser = @UserID,
       CreatedDate = getdate()
where  SurveyID = @SurveyID

GO

ALTER TABLE {databaseOwner}{objectQualifier}SurveyOptions ADD
	IsCorrect bit NOT NULL CONSTRAINT DF_{objectQualifier}SurveyOptions_IsCorrect DEFAULT 0
GO

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

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

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

create procedure {databaseOwner}{objectQualifier}GetSurveyOptions

@SurveyID int

as

select SurveyOptionID,
       ViewOrder,
       OptionName,
       Votes,
       IsCorrect
from   {objectQualifier}SurveyOptions
where  SurveyID = @SurveyID
order by ViewOrder

GO

create procedure {databaseOwner}{objectQualifier}AddSurveyOption

@SurveyID    int,
@OptionName  nvarchar(500),
@ViewOrder   int,
@IsCorrect   bit

as

insert into {objectQualifier}SurveyOptions ( 
  SurveyID,
  OptionName,
  ViewOrder,
  Votes,
  IsCorrect
)
values (
  @SurveyID,
  @OptionName,
  @ViewOrder,
  0,
  @IsCorrect
)

select SCOPE_IDENTITY()

GO

create procedure {databaseOwner}{objectQualifier}UpdateSurveyOption

@SurveyOptionID int,
@OptionName     nvarchar(500),
@ViewOrder      int,
@IsCorrect      bit

as

update {objectQualifier}SurveyOptions
set    OptionName = @OptionName,
       ViewOrder = @ViewOrder,
       IsCorrect = @IsCorrect
where  SurveyOptionID = @SurveyOptionID

GO

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

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}SurveyResults') and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}SurveyResults (
	[SurveyResultID] [int] IDENTITY (1, 1) NOT NULL ,
	[SurveyOptionID] [int] NOT NULL ,
	[UserID] [int] NOT NULL
) ON [PRIMARY]

GO


if not exists (select * from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}SurveyResults') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}SurveyResults ADD CONSTRAINT
	PK_{objectQualifier}SurveyResults PRIMARY KEY CLUSTERED 
	(
	SurveyResultID
	) ON [PRIMARY]

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'FK_{objectQualifier}SurveyResults_{objectQualifier}SurveyOptions') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}SurveyResults ADD CONSTRAINT
	FK_{objectQualifier}SurveyResults_{objectQualifier}SurveyOptions FOREIGN KEY
	(
	SurveyOptionID
	) REFERENCES {databaseOwner}{objectQualifier}SurveyOptions
	(
	SurveyOptionID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

create procedure {databaseOwner}{objectQualifier}AddSurveyResult

@SurveyOptionID  int,
@UserID          int

as

update {objectQualifier}SurveyOptions
set    Votes = Votes + 1
where  SurveyOptionID = @SurveyOptionID

insert into {objectQualifier}SurveyResults ( SurveyOptionID, UserID )
values ( @SurveyOptionID, @UserID )

GO
 

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/