USE [RecipeHolderDatabase]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveRecipeById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[RetrieveRecipeById]
(
@accountId int,
@recipeId int OUTPUT
)
AS


BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
	
SET NOCOUNT ON

SELECT   
[Recipes].[RecipeID], [Recipes].[CourseTypeID], [Recipes].[CuisineTypeID], [Recipes].[Name], [Recipes].[PreparationTime], [Recipes].[Directions], [Recipes].[CookTime], [Recipes].[Photo], [Recipes].[Temperature], [Recipes].[AccountId]
FROM       
[Recipes]
WHERE     
(AccountId = @accountId)

SET @recipeId = SCOPE_IDENTITY()
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateRecipe]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[UpdateRecipe]
(
	@preparationTime varchar(50),
	@directions varchar(3000),
	@cookTime int,
	@photo varbinary(MAX),
	@temperature int,
	@recipeID int,
	@courseTypeID int,
	@cuisineTypeID int,
	@recipeName varchar(50),
	@accountId int
)
	
AS
	
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON


SET NOCOUNT ON

BEGIN

UPDATE 
[dbo].[Recipes] 
SET [Recipes].[Name] = @recipeName, 
[Recipes].[PreparationTime] = @preparationTime, 
[Recipes].[Directions] = @directions, 
[Recipes].[CookTime] = @cookTime, 
[Recipes].[Photo] = @photo, 
[Recipes].[Temperature] = @temperature,
[Recipes].[CourseTypeID]= @courseTypeID,
[Recipes].[CuisineTypeID] = @cuisineTypeID,
[Recipes].[AccountId] = @accountId
WHERE 
(([Recipes].[RecipeID] = @recipeID))


END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CourseTypes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CourseTypes](
	[CourseTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_CourseTypes] PRIMARY KEY CLUSTERED 
(
	[CourseTypeID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Accounts]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Accounts](
	[AccountID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[UserName] [varchar](50) NULL,
	[Password] [varbinary](32) NULL,
	[isProtected] [bit] NOT NULL,
	[EMail] [varchar](50) NULL,
	[DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED 
(
	[AccountID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UnitMeasurements]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UnitMeasurements](
	[UnitMeasurementID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_UnitMeasurements] PRIMARY KEY CLUSTERED 
(
	[UnitMeasurementID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CuisineTypes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CuisineTypes](
	[CuisineTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_CuisineTypes] PRIMARY KEY CLUSTERED 
(
	[CuisineTypeID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FoodTypes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FoodTypes](
	[FoodTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_FoodTypes] PRIMARY KEY CLUSTERED 
(
	[FoodTypeID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Recipes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Recipes](
	[RecipeID] [int] IDENTITY(1,1) NOT NULL,
	[CourseTypeID] [int] NOT NULL,
	[CuisineTypeID] [int] NOT NULL,
	[AccountId] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[PreparationTime] [int] NULL,
	[Directions] [varchar](3000) NOT NULL,
	[CookTime] [int] NOT NULL,
	[Photo] [varbinary](max) NULL,
	[Temperature] [int] NOT NULL,
 CONSTRAINT [PK_Recipes] PRIMARY KEY CLUSTERED 
(
	[RecipeID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecipeIngredient]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RecipeIngredient](
	[RecipeIngredientID] [int] IDENTITY(1,1) NOT NULL,
	[RecipeID] [int] NOT NULL,
	[IngredientID] [int] NOT NULL,
	[UnitMeasurementID] [int] NOT NULL,
	[IngredientAmount] [smallmoney] NOT NULL,
 CONSTRAINT [PK_RecipeIngredient] PRIMARY KEY CLUSTERED 
(
	[RecipeIngredientID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NutritionDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[NutritionDetails](
	[NutritionID] [int] IDENTITY(1,1) NOT NULL,
	[RecipeID] [int] NOT NULL,
	[Calories] [float] NULL,
	[CaloriesFromFat] [float] NULL,
	[TotalFat] [float] NULL,
	[SaturatedFat] [float] NULL,
	[TransFat] [float] NULL,
	[Cholesteral] [float] NULL,
	[Sodium] [float] NULL,
	[TotalCarbohydrates] [float] NULL,
	[DietaryFiber] [float] NULL,
	[Sugar] [float] NULL,
	[Protein] [float] NULL,
	[VitaminA] [int] NULL,
	[VitaminC] [int] NULL,
	[Calcium] [int] NULL,
	[Iron] [int] NULL,
 CONSTRAINT [PK_NutritionDetails] PRIMARY KEY CLUSTERED 
(
	[NutritionID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Ingredients]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Ingredients](
	[IngredientID] [int] IDENTITY(1,1) NOT NULL,
	[FoodTypeID] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Ingredients] PRIMARY KEY CLUSTERED 
(
	[IngredientID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveRecipeTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/3/2012>
-- Description:	
-- =============================================

CREATE PROCEDURE [dbo].[RetrieveRecipeTable]
(
@accountId int
)

AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT     
Recipes.AccountId,Recipes.RecipeID, Recipes.Name AS RecipeName, Recipes.PreparationTime, 
Recipes.Directions, Recipes.CookTime, Recipes.Temperature, CourseTypes.Name AS CourseTypeName, 
CuisineTypes.Name AS CuisineTypeName
FROM         
CourseTypes 
INNER JOIN 
Recipes 
ON CourseTypes.CourseTypeID = Recipes.CourseTypeID 
INNER JOIN 
CuisineTypes 
ON Recipes.CuisineTypeID = CuisineTypes.CuisineTypeID
WHERE     
(Recipes.AccountId = @accountId)

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateCourse]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Andy Churchill>
-- Create date: <12/2/12>
-- Description: <>
-- =============================================

CREATE PROCEDURE [dbo].[UpdateCourse]
(
	@Original_CourseTypeID int,
	@Original_Name varchar(50),
	@CourseTypesName varchar(50)
)
	
AS
	
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON


UPDATE    
[CourseTypes]
SET       
[CourseTypes].[Name] = @CourseTypesName
WHERE     
([CourseTypes].[CourseTypeID] = @Original_CourseTypeID) 
AND 
([CourseTypes].[Name] = @Original_Name)
	
	
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForCourseType]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[SearchForCourseType]
(
	@idToSearch int = NULL,
	@nameToSearch varchar(50) = NULL
)
	
AS
	
SET NOCOUNT ON 

BEGIN
	
SELECT  
[CourseTypeID],[CourseTypes].[Name]
FROM    
[CourseTypes]
WHERE   
coalesce([CourseTypeID], -1) = coalesce(@idToSearch, [CourseTypeID], -1)
AND     
[CourseTypes].[Name] = coalesce(@nameToSearch, [CourseTypes].[Name])

END
   
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveCourseById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveCourseById]
(
	@courseId int 
)
	
AS

SET NOCOUNT ON 
	
SELECT   [CourseTypeID],[Name]
FROM     [CourseTypes]
WHERE   [CourseTypeID] = @courseId

RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveAllCourses]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveAllCourses]
	
AS

SET NOCOUNT ON

BEGIN

SELECT	
CourseTypes.CourseTypeID, CourseTypes.Name
FROM	
CourseTypes
ORDER BY 
CourseTypes.Name

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCourse]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[InsertCourse]
(
	@courseTypesName varchar(50),
	@Identity int OUT
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

BEGIN

INSERT INTO [CourseTypes]
([CourseTypes].[Name])
VALUES     
(@courseTypesName)

SET @Identity = @@Identity

END

RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchRecipeTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
	-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/4/12>
-- Description:	<Description>
-- =============================================
CREATE PROCEDURE [dbo].[SearchRecipeTable]
(
	@recipeName varchar(50) = NULL,
	@courseTypeName varchar(50) = NULL,
	@cuisineTypeName varchar(50) = NULL,
	@prepMax int = NULL,
	@prepMin int = NULL
)
	
AS
	
	 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
	
BEGIN

SELECT     
Recipes.AccountId,Recipes.RecipeID, Recipes.Name AS RecipeName, Recipes.PreparationTime, 
Recipes.Directions, Recipes.CookTime, Recipes.Temperature, CourseTypes.Name AS CourseTypeName, 
CuisineTypes.Name AS CuisineTypeName
FROM         
CuisineTypes 
INNER JOIN 
CourseTypes 
INNER JOIN
Recipes  
ON CourseTypes.CourseTypeID = Recipes.CourseTypeID 
ON CuisineTypes.CuisineTypeID = Recipes.CuisineTypeID
WHERE     
(Recipes.Name = COALESCE (@recipeName, Recipes.Name)) 
AND (Recipes.PreparationTime >= COALESCE (@prepMin, Recipes.PreparationTime))
AND (Recipes.PreparationTime <= COALESCE (@prepMax, Recipes.PreparationTime)) 
AND (CourseTypes.Name = COALESCE (@courseTypeName, CourseTypes.Name))
AND (CuisineTypes.Name = COALESCE (@cuisineTypeName, CuisineTypes.Name))

   
END
                   	
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteCourse]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/12/12>
-- Description:	<First it checks to see if the row to be deleted is in another table. 
--If so update all those records with the (Does Not Exist) text 
--and also inserts it into the CourseType table if that doesnt exist.
--Finally Deletes the selected course from the datatbase>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteCourse]
(
	 @Original_CourseTypeID int,
	 @Original_Name varchar(50)
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON 

BEGIN

DECLARE @courseTypeRowCount int
DECLARE @noneRowCount int
DECLARE @noneID int


	IF NOT @Original_Name = ''None''
	BEGIN
--See if None exist in courseTypes
		SET @noneRowCount =
		(SELECT COUNT(*)
		FROM 
		[CourseTypes]
		WHERE 
		[CourseTypes].[Name] = ''None'')
	END 
	ELSE
	BEGIN
--Exit procedure if row to delete is ''None''
		RETURN -1
	END  
--If there are no rows with "None" in it...create it
	IF @noneRowCount < 1
	BEGIN

		INSERT INTO [CourseTypes]
		([CourseTypes].[Name])
		VALUES
		(''None'')

		SET @noneID = SCOPE_IDENTITY()
	END
--Else if there is a None row, Get none''s id
	ELSE
	BEGIN
 
		SET @noneID = (SELECT [CourseTypes].[CourseTypeID]
		FROM
		[CourseTypes]
		WHERE 
		[CourseTypes].[Name] = ''None'')
				
	END

 
	BEGIN

--Get how many rows in Recipes that have the soon to be deleted row
		SET @courseTypeRowCount = 
		(SELECT  COUNT(*)
		FROM
		[Recipes]
		WHERE
		[Recipes].[CourseTypeID] = @Original_CourseTypeID)

--If the deleted row exist in the recipes table 
	
	IF @courseTypeRowCount > 0 
	BEGIN
			
--then update the records that has the to be deleted course with the Does Not Exist
			UPDATE 
			[dbo].[Recipes] 
			SET 
			[Recipes].[CourseTypeID]= @noneID
			WHERE 
			[Recipes].[CourseTypeID] = @Original_CourseTypeID
		
	
	END

--Delete the record no matter what
		DELETE FROM 
		[CourseTypes]
		WHERE     
		([CourseTypes].[CourseTypeID] = @Original_CourseTypeID) 
		AND 
		([CourseTypes].[Name] = @Original_Name)
 END

END ' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForProfile]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Andy Churchill>
-- Create date: <11/26/12>
-- Description: <Searches for accounts by user''s criteria>
-- =============================================

CREATE PROCEDURE [dbo].[SearchForProfile]
(
@accountId int = NULL,
@firstName varchar(50)= NULL,
@lastName varchar(50)= NULL,
@eMail varchar(50)= NULL,
@dateCreated datetime = NULL,
@isProtected bit = NULL
)
	

AS 


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN


SELECT     
[Accounts].[AccountID], [Accounts].[FirstName], [Accounts].[LastName], [Accounts].[UserName], 
[Accounts].[Password], [Accounts].[isProtected], [Accounts].[EMail], [Accounts].[DateCreated]
FROM       
[Accounts]
WHERE   
coalesce([Accounts].[AccountID], -1) = coalesce(@accountId, [Accounts].[AccountID], -1)
AND  
[Accounts].[FirstName] = coalesce(@firstName, [Accounts].[FirstName])
AND
[Accounts].[LastName] = coalesce(@lastName, [Accounts].[LastName])
AND  
[Accounts].[EMail] = coalesce(@eMail, [Accounts].[EMail])
AND  
[Accounts].[DateCreated] = coalesce(@dateCreated, [Accounts].[DateCreated])
AND  
[Accounts].[isProtected] = coalesce(@isProtected, [Accounts].[isProtected])

END

RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteAccount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/26/12>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteAccount]
(
@accountIdToDelete int 
)
AS


BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


DELETE FROM 
[Accounts]
WHERE     
([Accounts].[AccountID] = @accountIdToDelete) 

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveAccountByProtection]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Andy Churchill>
-- Create date: <11/26/12>
-- Description: <Retrieves all the accounts by true, the accounts are protected or false, they are unprotected>
-- =============================================

CREATE PROCEDURE [dbo].[RetrieveAccountByProtection]
(
@isProtected bit 
)
	
 
AS 


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

BEGIN



SELECT     
[Accounts].[AccountID], [Accounts].[FirstName], [Accounts].[LastName], [Accounts].[UserName], 
[Accounts].[Password], [Accounts].[isProtected], [Accounts].[EMail], [Accounts].[DateCreated]
FROM       
[Accounts]
WHERE   
[Accounts].[isProtected] = @isProtected

END

RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VerifyUser]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[VerifyUser]
(
	@password varbinary(32) OUTPUT,
	@userName varchar(50),
	@AccountId int OUTPUT 
)
	
AS

--SET NOCOUNT ON 

SELECT @password = [Accounts].[Password] 
FROM [dbo].[Accounts]
WHERE [Accounts].[UserName] = @userName



 
	
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertAccount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[InsertAccount]

	(
	@FirstName varchar(50),				/*Used in a person''s first name*/
	@LastName varchar(50),				/*Used in a person''s Last name*/
	@UserName varchar(50) = NULL,		/*A user''s user name, can be null*/
	@Password varbinary(32) = NULL,		/*A user''s password, can be null*/
	@isProtected bit,					/*Tells the program if the user has a protected account or not*/
	@Email varchar(50) = NULL,			--E-Mail can be null
	@AccountId int OUTPUT				--The Account id that is created is outputed
	)
	
AS

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON


SET NOCOUNT ON
	 
	
BEGIN
	
INSERT INTO [dbo].[Accounts]
([Accounts].[FirstName], [Accounts].[LastName], [Accounts].[UserName], [Accounts].[Password], [Accounts].[isProtected],[Accounts].[EMail],[Accounts].[DateCreated])
VALUES     
(@FirstName,@LastName,@UserName,@Password,@isProtected,@Email,GetDate())

SET @AccountId = SCOPE_IDENTITY()

END

RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateAccount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,Name>
-- Create date: <Create Date>
-- Description: <Description>
-- =============================================

CREATE PROCEDURE [dbo].[UpdateAccount]
(
	@FirstName varchar(50),
	@LastName varchar(50),
	@UserName varchar(50),
	@Password varbinary(32),
	@isProtected bit,
	@accountIdToUpdate int,
	@email varchar(50) 
)
	
AS
	
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
--SET NOCOUNT ON

BEGIN

UPDATE [dbo].[Accounts] 
SET [Accounts].[FirstName] = @FirstName, 
[Accounts].[LastName] = @LastName, 
[Accounts].[UserName] = @UserName, 
[Accounts].[Password] = @Password, 
[Accounts].[isProtected] = @isProtected,
[Accounts].[EMail] = @email
WHERE 
(([Accounts].[AccountID] = @accountIdToUpdate))


END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteUnitMeasurement]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/12/12>
-- Description:	<First it checks to see if the row to be deleted is in another table. 
-- If so update all those records with the (Does Not Exist) text 
-- and also inserts it into the Unit Measurement table if that doesnt exist.
-- Finally Deletes the selected Unit Measurement from the datatbase>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteUnitMeasurement]
(
	@Original_UnitMeasurementID int,
	@Original_Name varchar(50)
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON  
	

BEGIN

DECLARE @unitTypeRowCount int
DECLARE @unitCountExist int
DECLARE @retrievedUnitID int

SET @unitTypeRowCount = 
(SELECT  COUNT(*)
FROM
[RecipeIngredient]
WHERE
[RecipeIngredient].[UnitMeasurementID] = @Original_UnitMeasurementID)

--If the deleted row exist in the recipesingredient table 

IF @unitTypeRowCount > 0 
BEGIN
--See if the (Does Not Exist) unit exist
    SET @unitCountExist =        
    (SELECT COUNT(*) 
	FROM
	[UnitMeasurements]
	WHERE
	[UnitMeasurements].[Name] = ''Does Not Exist'')
--If it doesn''t, Create (Does Not Exist)
	IF @unitCountExist < 1
		BEGIN
			INSERT INTO [UnitMeasurements]
			([UnitMeasurements].[Name])
			VALUES     
			(''Does Not Exist'')
			
--then update the records that has the to be deleted unit with the Does Not Exist
			UPDATE 
			[dbo].[RecipeIngredient] 
			SET 
			[RecipeIngredient].[UnitMeasurementID]= SCOPE_IDENTITY()
			WHERE 
			[RecipeIngredient].[UnitMeasurementID] = @Original_UnitMeasurementID
		END
	ELSE
		BEGIN
--Else find the unitId of the (Does Not Exist) record 
			SET @retrievedUnitID = (SELECT [UnitMeasurements].[UnitMeasurementID]
			FROM 
			[UnitMeasurements]
			WHERE 
			[UnitMeasurements].[Name] = ''Does Not Exist'')
--Update the to be deleted Unit row with the (Does Not Exist) row
			UPDATE 
			[dbo].[RecipeIngredient] 
			SET 
			[RecipeIngredient].[UnitMeasurementID]= @retrievedUnitID
			WHERE 
			[RecipeIngredient].[UnitMeasurementID] = @Original_UnitMeasurementID
		END
  END

--Delete the record no matter what
DELETE FROM 
	[UnitMeasurements]
	WHERE     
	([UnitMeasurements].[UnitMeasurementID] = @Original_UnitMeasurementID) 
	AND 
	([UnitMeasurements].[Name] = @Original_Name)
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateRecipeIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/25/2012>
-- Description:	<Updates a recipeIngredient table''s row with new data.>
-- =============================================

CREATE PROCEDURE [dbo].[UpdateRecipeIngredient]
(
@RecipeID int,				        --Update the recipe number
@IngredientID int,					--Update the Ingredient id
@UnitMeasurementID int,				--Update the unit measurement
@IngredientAmount decimal,			--Update the ingredient amount
@Original_RecipeIngredientId int	--The recipeIngredient Id to test for
)

AS


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


BEGIN


UPDATE    
[RecipeIngredient]
SET              
[RecipeIngredient].[RecipeID] = @RecipeID,
[RecipeIngredient].[IngredientID] = @IngredientID,
[RecipeIngredient].[UnitMeasurementID] = @UnitMeasurementID,
[RecipeIngredient].[IngredientAmount] = @IngredientAmount
WHERE     
([RecipeIngredient].[RecipeIngredientID] = @Original_RecipeIngredientId) 


END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteRecipeIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/25/2012>
-- Description:	<Deletes a recipeIngredient table''s row.>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteRecipeIngredient]
(
@recipeIngredientId int	                  --The recipeIngredient Id to test for
)

AS	
	
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


BEGIN

DELETE FROM 
[RecipeIngredient]
WHERE     
([RecipeIngredient].[RecipeIngredientID] = @recipeIngredientId) 

END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForRecipeIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/26/2012>
-- Description:	Searches for recipeIngredient table rows based on user''s criteria.>
-- =============================================

CREATE PROCEDURE [dbo].[SearchForRecipeIngredient]
(
    --Search Recipe allow searcher to choose what fields to search
	@recipeIngredientId int = NULL,
	@recipeID int = NULL,
	@ingredientID int = NULL,
	@unitMeasurementID int = NULL,
	@ingredientAmount decimal = NULL
)
 
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT     
[RecipeIngredient].[RecipeIngredientID], [RecipeIngredient].[RecipeID], [RecipeIngredient].[IngredientID], 
[RecipeIngredient].[UnitMeasurementID], [RecipeIngredient].[IngredientAmount]
FROM         
[RecipeIngredient]
WHERE  
coalesce([RecipeIngredient].[RecipeIngredientID], -1) = coalesce(@recipeIngredientId, [RecipeIngredient].[RecipeIngredientID], -1)
AND
coalesce([RecipeIngredient].[RecipeID], -1) = coalesce(@recipeID, [RecipeIngredient].[RecipeID], -1)
AND
coalesce([RecipeIngredient].[IngredientID], -1) = coalesce(@ingredientID, [RecipeIngredient].[IngredientID], -1)
AND
coalesce([RecipeIngredient].[UnitMeasurementID], -1) = coalesce(@unitMeasurementID, [RecipeIngredient].[UnitMeasurementID], -1)
AND
coalesce([RecipeIngredient].[IngredientAmount], -1) = coalesce(@ingredientAmount, [RecipeIngredient].[IngredientAmount], -1)

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveRecipeIngredientById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/26/2012>
-- Description:	Retrieves all the recipeIngredients by Id.>
-- =============================================

CREATE PROCEDURE [dbo].[RetrieveRecipeIngredientById]
(
	@recipeId int 			--Used to search by id
)
 
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT     
[RecipeIngredient].[RecipeIngredientID], [RecipeIngredient].[RecipeID], [RecipeIngredient].[IngredientID], 
[RecipeIngredient].[UnitMeasurementID], [RecipeIngredient].[IngredientAmount]
FROM         
[RecipeIngredient]

WHERE 

([RecipeIngredient].[RecipeID] = @recipeId)

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertRecipeIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/25/2012>
-- Description:	Inserts a new recipeIngredient table''s row.>
-- =============================================

CREATE PROCEDURE [dbo].[InsertRecipeIngredient]
	
(
	@RecipeID int,						--Inserts the recipe number
	@IngredientID int,					--Inserts the Ingredient id
	@UnitMeasurementID int ,			--Inserts the unit measurement
	@IngredientAmount decimal,			--Inserts the ingredient amount
	@Identity int OUT                   --Sends out the new id number for this row
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

INSERT INTO [dbo].[RecipeIngredient] 
            ([RecipeIngredient].[RecipeID], [RecipeIngredient].[IngredientID], 
            [RecipeIngredient].[UnitMeasurementID], [RecipeIngredient].[IngredientAmount]) 
VALUES      
(@RecipeID, @IngredientID, @UnitMeasurementID, @IngredientAmount)

--Return the current identity id of this new row
SET @Identity = @@Identity

END

RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchRecipeIngredientTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
	-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/3/2012>
-- Description:	
-- =============================================

CREATE PROCEDURE [dbo].[SearchRecipeIngredientTable]
(
@ingredientName varchar(50) = NULL
)

AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT     
RecipeIngredient.RecipeId,Ingredients.Name AS IngredientName, RecipeIngredient.IngredientAmount, 
UnitMeasurements.Name AS UnitMeasurementName, FoodTypes.Name AS FoodTypeName
FROM         
RecipeIngredient 
INNER JOIN Ingredients ON RecipeIngredient.IngredientID = Ingredients.IngredientID 
INNER JOIN FoodTypes ON Ingredients.FoodTypeID = FoodTypes.FoodTypeID 
INNER JOIN UnitMeasurements ON RecipeIngredient.UnitMeasurementID = UnitMeasurements.UnitMeasurementID
INNER JOIN Recipes ON RecipeIngredient.RecipeID = Recipes.RecipeID
WHERE
(Ingredients.Name = COALESCE (@ingredientName, Ingredients.Name))


END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveRecipeIngredientTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/3/2012>
-- Description:	
-- =============================================

CREATE PROCEDURE [dbo].[RetrieveRecipeIngredientTable]
--(
--@recipeId int
--)
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT     
RecipeIngredient.RecipeId,Ingredients.Name AS IngredientName, RecipeIngredient.IngredientAmount, 
UnitMeasurements.Name AS UnitMeasurementName, FoodTypes.Name AS FoodTypeName
FROM         
RecipeIngredient 
INNER JOIN Ingredients ON RecipeIngredient.IngredientID = Ingredients.IngredientID 
INNER JOIN FoodTypes ON Ingredients.FoodTypeID = FoodTypes.FoodTypeID 
INNER JOIN UnitMeasurements ON RecipeIngredient.UnitMeasurementID = UnitMeasurements.UnitMeasurementID


END

    
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/12/12>
-- Description:	<First it checks to see if the row to be deleted is in another table. 
-- If so update all those records with the (Does Not Exist) text 
-- and also inserts it into the Ingredient table if that doesnt exist.
-- Finally Deletes the selected Ingredient from the datatbase>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteIngredient]
(
	@Original_IngredientID int,
	@Original_FoodTypeID int,
	@Original_Name varchar(50)
)
	

AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON 

BEGIN

DECLARE @courseTypeRowCount int
DECLARE @noneRowCount int
DECLARE @noneID int


	IF NOT @Original_Name = ''None''
	BEGIN
--See if None exist in courseTypes
		SET @noneRowCount =
		(SELECT COUNT(*)
		FROM 
		[Ingredients]
		WHERE 
		[Ingredients].[Name] = ''None'')
	END 
	ELSE
	BEGIN
--Exit procedure if row to delete is ''None''
		RETURN -1
	END  
--If there are no rows with "None" in it...create it
	IF @noneRowCount < 1
	BEGIN

		INSERT INTO [Ingredients]
		([Ingredients].[Name],[Ingredients].[FoodTypeID])
		VALUES
		(''None'',@Original_FoodTypeID)

		SET @noneID = SCOPE_IDENTITY()
	END
--Else if there is a None row, Get none''s id
	ELSE
	BEGIN
 
		SET @noneID = (SELECT [Ingredients].[IngredientID]
		FROM
		[Ingredients]
		WHERE 
		[Ingredients].[Name] = ''None'')
				
	END

 
	BEGIN

--Get how many rows in Recipes that have the soon to be deleted row
		SET @courseTypeRowCount = 
		(SELECT  COUNT(*)
		FROM
		[RecipeIngredient]
		WHERE
		[RecipeIngredient].[IngredientID] = @Original_IngredientID)

--If the deleted row exist in the recipes table 
	
	IF @courseTypeRowCount > 0 
	BEGIN
			
--then update the records that has the to be deleted course with the Does Not Exist
			UPDATE 
			[dbo].[RecipeIngredient] 
			SET 
			[RecipeIngredient].[IngredientID]= @noneID
			WHERE 
			[RecipeIngredient].[IngredientID] = @Original_IngredientID
		
	
	END

--Delete the record no matter what
		DELETE FROM 
		[Ingredients]
		WHERE     
		([Ingredients].[IngredientID] = @Original_IngredientID) 
		AND 
		([Ingredients].[Name] = @Original_Name)
		AND
		([Ingredients].[FoodTypeID] = @Original_FoodTypeID)
 END

END ' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteCuisine]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/12/12>
-- Description:	<First it checks to see if the row to be deleted is in another table. 
-- If so update all those records with the (Does Not Exist) text 
-- and also inserts it into the [CuisineTypes] table if that doesnt exist.
-- Finally Deletes the selected [CuisineTypes] from the datatbase>
-- =============================================


CREATE PROCEDURE [dbo].[DeleteCuisine]
(
	@Original_CuisineTypeID int,
	@Original_Name varchar(50)
)
	
AS


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON 

BEGIN

DECLARE @cuisineTypeRowCount int
DECLARE @noneRowCount int
DECLARE @noneID int


	IF NOT @Original_Name = ''None''
	BEGIN
--See if None exist in courseTypes
		SET @noneRowCount =
		(SELECT COUNT(*)
		FROM 
		[CuisineTypes]
		WHERE 
		[CuisineTypes].[Name] = ''None'')
	END 
	ELSE
	BEGIN
--Exit procedure if row to delete is ''None''
		RETURN -1
	END  
--If there are no rows with "None" in it...create it
	IF @noneRowCount < 1
	BEGIN

		INSERT INTO [CuisineTypes]
		([CuisineTypes].[Name])
		VALUES
		(''None'')

		SET @noneID = SCOPE_IDENTITY()
	END
--Else if there is a None row, Get none''s id
	ELSE
	BEGIN
 
		SET @noneID = (SELECT [CuisineTypes].[CuisineTypeID]
		FROM
		[CuisineTypes]
		WHERE 
		[CuisineTypes].[Name] = ''None'')
				
	END

 
	BEGIN

--Get how many rows in Recipes that have the soon to be deleted row
		SET @cuisineTypeRowCount = 
		(SELECT  COUNT(*)
		FROM
		[Recipes]
		WHERE
		[Recipes].[CuisineTypeID] = @Original_CuisineTypeID)

--If the deleted row exist in the recipes table 
	
	IF @cuisineTypeRowCount > 0 
	BEGIN
			
--then update the records that has the to be deleted cuisine with the (None)
			UPDATE 
			[dbo].[Recipes] 
			SET 
			[Recipes].[CuisineTypeID]= @noneID
			WHERE 
			[Recipes].[CuisineTypeID] = @Original_CuisineTypeID
		
	
	END

--Delete the record no matter what
		DELETE FROM 
		[CuisineTypes]
		WHERE     
		([CuisineTypes].[CuisineTypeID] = @Original_CuisineTypeID) 
		AND 
		([CuisineTypes].[Name] = @Original_Name)
 END

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertRecipe]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[InsertRecipe]
(   
    @RecipeName varchar(50),
    @CourseTypeID int,      
	@CuisineTypeID int,
	@PreparationTime int,				--Optional
	@Directions varchar(3000),
	@CookTime int,
	@Photo varbinary(MAX) = NULL,		--Optional
	@Temperature int,
	@AccountId int,
	@RecipeId int output
	
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 



INSERT INTO [dbo].Recipes
(CourseTypeID, CuisineTypeID, Recipes.Name, PreparationTime, Directions, CookTime, Photo, Temperature, AccountId)
VALUES     
(@CourseTypeID,@CuisineTypeID,@RecipeName,@PreparationTime,@Directions,@CookTime,@Photo,@Temperature,@AccountId)


--Return the current id that was inserted
SET @RecipeId = SCOPE_IDENTITY()

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForRecipes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/23/2012>
-- Description:	Searches the recipes table on user''s criteria.>
-- =============================================

CREATE PROCEDURE [dbo].[SearchForRecipes]
(
    --Search Recipe allow searcher to choose what fields to search
	@recipeName varchar(50) = NULL,
	@preparationTimeMin int = NULL,
	@preparationTimeMax int = NULL,
	@courseTypeID int = NULL,
	@cuisineTypeID int = NULL,
	@accountId int
)
 
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT      [Recipes].[RecipeID], [Recipes].[CourseTypeID], [Recipes].[CuisineTypeID], [Recipes].[Name], 
			[Recipes].[PreparationTime], [Recipes].[Directions], [Recipes].[CookTime], 
            [Recipes].[Photo], [Recipes].[Temperature], [Recipes].[AccountId]
FROM         
Recipes 

WHERE     
(Recipes.AccountId = @accountId) 
AND (Recipes.Name = COALESCE (@recipeName, Recipes.Name)) 
AND (Recipes.PreparationTime >= COALESCE (@preparationTimeMin, Recipes.PreparationTime)) 
AND (Recipes.PreparationTime <= COALESCE (@preparationTimeMax, Recipes.PreparationTime)) 
AND (COALESCE (Recipes.CuisineTypeID, - 1) = COALESCE (@cuisineTypeID, Recipes.CuisineTypeID, - 1)) 
AND (COALESCE (Recipes.CourseTypeID, - 1) = COALESCE (@courseTypeID, Recipes.CourseTypeID, - 1))


 
END



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteRecipe]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteRecipe]
(
@recipeId int
)
AS


BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON



DELETE FROM 
[Recipes]
WHERE     
([Recipes].[RecipeID] = @recipeId) 


END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Andy Churchill>
-- Create date: <12/2/12>
-- Description: <>
-- =============================================

CREATE PROCEDURE [dbo].[InsertIngredient]
(
	@nameToInsert varchar(50),
	@foodTypeIdToInsert int
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
--SET NOCOUNT ON

BEGIN


INSERT INTO [Ingredients]
([Ingredients].[FoodTypeID], [Ingredients].[Name])
VALUES     
(@foodTypeIdToInsert,@nameToInsert)


END






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[UpdateIngredient]
(
	@Name varchar(50),
	@FoodTypeID int,
	@ingredientID int
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON


BEGIN


UPDATE    
[Ingredients]
SET              
[Ingredients].[FoodTypeID] = @FoodTypeID, [Ingredients].[Name] = @Name
WHERE     
([Ingredients].[IngredientID] = @ingredientID) 



END
	
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CheckIngredientExists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <11/29/12>
-- Description:	<Returns 1 if the ingredient exists, returns 0 if the ingredient does not exist>
-- =============================================

CREATE PROCEDURE [dbo].[CheckIngredientExists]
(				
	@Name varchar(50),
	@Exists BIT OUTPUT
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS(SELECT ''True'' FROM Ingredients WHERE Ingredients.Name = @Name)

BEGIN
--This means it exists, return 1
 SET  @Exists =  1
 RETURN
END

ELSE

BEGIN
--This means it does not exist, return 0
SET  @Exists = 0
RETURN
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ViewIngredientsFoodTypes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/3/2012>
-- Description:	
-- =============================================

CREATE PROCEDURE [dbo].[ViewIngredientsFoodTypes]

AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 
	
BEGIN
SELECT     Ingredients.Name AS IngredientName, FoodTypes.Name AS FoodTypeName, Ingredients.FoodTypeID, Ingredients.IngredientID
FROM         FoodTypes INNER JOIN
                      Ingredients ON FoodTypes.FoodTypeID = Ingredients.FoodTypeID

END


RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForIngredient]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[SearchForIngredient]
(
	@ingredientIdToSearch int = NULL,
	@nameToSearch varchar(50) = NULL,
	@foodTypeIdToSearch int = NULL
)
	
AS
	
SET NOCOUNT ON 

BEGIN
	
SELECT     
[Ingredients].[IngredientID], [Ingredients].[FoodTypeID], [Ingredients].[Name]
FROM        
[Ingredients]
WHERE   
coalesce([Ingredients].[IngredientID], -1) = coalesce(@ingredientIdToSearch, [Ingredients].[IngredientID], -1)
AND     
[Ingredients].[Name] = coalesce(@nameToSearch, [Ingredients].[Name])
AND
coalesce([Ingredients].[FoodTypeID], -1) = coalesce(@foodTypeIdToSearch, [Ingredients].[FoodTypeID], -1)

END
   
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveAllIngredients]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveAllIngredients]

AS
	
SET NOCOUNT ON 

BEGIN
	
SELECT     
[Ingredients].[IngredientID], [Ingredients].[FoodTypeID], [Ingredients].[Name]
FROM         
[Ingredients]
ORDER BY [Ingredients].[Name]
END
   
RETURN



' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveIngredientById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/


CREATE PROCEDURE [dbo].[RetrieveIngredientById]
(
	@ingredientIdToSearch int
)
	
AS

SET NOCOUNT ON 
 
BEGIN
 
SELECT     
[Ingredients].[IngredientID], [Ingredients].[FoodTypeID], [Ingredients].[Name]
FROM         
[Ingredients]
WHERE  
([Ingredients].[IngredientID]= @ingredientIdToSearch)

END
 
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteFoodType]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/12/12>
-- Description:	<First it checks to see if the row to be deleted is in another table. 
-- If so update all those records with the (Does Not Exist) text 
-- and also inserts it into the Food Types table if that doesnt exist.
-- Finally Deletes the selected Food Types from the datatbase>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteFoodType]
(
	@Original_FoodTypeID int,
	@Original_Name varchar(50)
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON 

BEGIN

DECLARE @foodTypeRowCount int
DECLARE @noneRowCount int
DECLARE @noneID int


	IF NOT @Original_Name = ''None''
	BEGIN
--See if None exist in courseTypes
		SET @noneRowCount =
		(SELECT COUNT(*)
		FROM 
		[FoodTypes]
		WHERE 
		[FoodTypes].[Name] = ''None'')
	END 
	ELSE
	BEGIN
--Exit procedure if row to delete is ''None''
		RETURN -1
	END  
--If there are no rows with "None" in it...create it
	IF @noneRowCount < 1
	BEGIN

		INSERT INTO [FoodTypes]
		([FoodTypes].[Name])
		VALUES
		(''None'')

		SET @noneID = SCOPE_IDENTITY()
	END
--Else if there is a None row, Get none''s id
	ELSE
	BEGIN
 
		SET @noneID = (SELECT [FoodTypes].[FoodTypeID]
		FROM
		[FoodTypes]
		WHERE 
		[FoodTypes].[Name] = ''None'')
				
	END

 
	BEGIN

--Get how many rows in Recipes that have the soon to be deleted row
		SET @foodTypeRowCount = 
		(SELECT  COUNT(*)
		FROM
		[Ingredients]
		WHERE
		[Ingredients].[FoodTypeID] = @Original_FoodTypeID)

--If the deleted row exist in the recipes table 
	
	IF @foodTypeRowCount > 0 
	BEGIN
			
--then update the records that has the to be deleted course with the Does Not Exist
			UPDATE 
			[dbo].[Ingredients] 
			SET 
			[Ingredients].[FoodTypeID]= @noneID
			WHERE 
			[Ingredients].[FoodTypeID] = @Original_FoodTypeID
		
	
	END

--Delete the record no matter what
		DELETE FROM 
		[FoodTypes]
		WHERE     
		([FoodTypes].[FoodTypeID] = @Original_FoodTypeID) 
		AND 
		([FoodTypes].[Name] = @Original_Name)
 END

END 






' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveIngredients]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveIngredients]
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT	Ingredients.IngredientID, Ingredients.Name
FROM	Ingredients 
ORDER BY Ingredients.Name
	

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FillNutritionDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[FillNutritionDetails]

AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN	
	
SELECT     
NutritionID, RecipeID, Calories, CaloriesFromFat, TotalFat, SaturatedFat, TransFat, Cholesteral, Sodium, 
TotalCarbohydrates, DietaryFiber, Sugar, Protein, VitaminA, VitaminC, Calcium, Iron
FROM         
NutritionDetails

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveNutritionDetailsById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Andy Churchill>
-- Create date: <12/1/2012>
-- Description:	Retrieves all the Nutrition Details by Id.>
-- =============================================

CREATE PROCEDURE [dbo].[RetrieveNutritionDetailsById]
(
@recipeId as int
)

AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

BEGIN	
	
SELECT     
[NutritionDetails].[NutritionID], [NutritionDetails].[RecipeID], [NutritionDetails].[Calories], 
[NutritionDetails].[CaloriesFromFat], [NutritionDetails].[TotalFat], [NutritionDetails].[SaturatedFat], [NutritionDetails].[TransFat], 
[NutritionDetails].[Cholesteral], [NutritionDetails].[Sodium], [NutritionDetails].[TotalCarbohydrates], [NutritionDetails].[DietaryFiber], 
[NutritionDetails].[Sugar], [NutritionDetails].[Protein], [NutritionDetails].[VitaminA], 
[NutritionDetails].[VitaminC], [NutritionDetails].[Calcium], [NutritionDetails].[Iron]
FROM         
[NutritionDetails]
WHERE
([NutritionDetails].[RecipeID]=@recipeId)

END


RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteNutritionDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[DeleteNutritionDetails]
(
@Original_NutritionID int
)

AS


BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
	
SET NOCOUNT ON


DELETE FROM 
[NutritionDetails]
WHERE     
([NutritionDetails].[NutritionID] = @Original_NutritionID) 


END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateNutritionDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[UpdateNutritionDetails]
(
@Original_NutritionID int,
@RecipeID int,
@Calories Float,
@CaloriesFromFat Float,
@TotalFat Float,
@SaturatedFat Float,
@TransFat Float,
@Cholesteral Float,
@Sodium Float,
@TotalCarbohydrates Float,
@DietaryFiber Float,
@Sugar Float,
@Protein Float,
@VitaminA int,
@VitaminC int,
@Calcium int,
@Iron int
)

AS


BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
	
SET NOCOUNT ON

UPDATE    
[NutritionDetails]
SET              
[NutritionDetails].[RecipeID] = @RecipeID, [NutritionDetails].[Calories] = @Calories, [NutritionDetails].[CaloriesFromFat] = @CaloriesFromFat, 
[NutritionDetails].[TotalFat] = @TotalFat, [NutritionDetails].[SaturatedFat] = @SaturatedFat, 
[NutritionDetails].[TransFat] = @TransFat, [NutritionDetails].[Cholesteral] = @Cholesteral, [NutritionDetails].[Sodium] = @Sodium, 
[NutritionDetails].[TotalCarbohydrates] = @TotalCarbohydrates, [NutritionDetails].[DietaryFiber] = @DietaryFiber, 
[NutritionDetails].[Sugar] = @Sugar, [NutritionDetails].[Protein] = @Protein, [NutritionDetails].[VitaminA] = @VitaminA, 
[NutritionDetails].[VitaminC] = @VitaminC, [NutritionDetails].[Calcium] = @Calcium, [NutritionDetails].[Iron] = @Iron
WHERE     
([NutritionDetails].[NutritionID] = @Original_NutritionID) 

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForNutritionDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[SearchForNutritionDetails]
(
@RecipeID int,
@Calories float,
@CaloriesFromFat float,
@TotalFat float,
@SaturatedFat float,
@TransFat float,
@Cholesteral float,
@Sodium float,
@TotalCarbohydrates float,
@DietaryFiber float,
@Sugar float,
@Protein float,
@VitaminA int,
@VitaminC int,
@Calcium int,
@Iron int

)
AS


BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
	
SET NOCOUNT ON

--Search for nulls
SELECT     
[NutritionDetails].[NutritionID], [NutritionDetails].[RecipeID], [NutritionDetails].[Calories], [NutritionDetails].CaloriesFromFat,
[NutritionDetails].[TotalFat],[NutritionDetails].[SaturatedFat],[NutritionDetails].[TransFat],[NutritionDetails].[Cholesteral],
[NutritionDetails].[Sodium],[NutritionDetails].[TotalCarbohydrates],[NutritionDetails].[DietaryFiber],[NutritionDetails].[Sugar],
[NutritionDetails].[Protein],[NutritionDetails].[VitaminA],[NutritionDetails].[VitaminC],[NutritionDetails].[Calcium],[NutritionDetails].[Iron]
FROM        
[NutritionDetails]
WHERE   
coalesce([NutritionDetails].[RecipeID], -1) = coalesce(@RecipeID, [NutritionDetails].[RecipeID], -1)
AND
coalesce([NutritionDetails].[Calories], -1) = coalesce(@Calories, [NutritionDetails].[Calories], -1)
AND     
coalesce([NutritionDetails].[CaloriesFromFat], -1) = coalesce(@CaloriesFromFat, [NutritionDetails].[CaloriesFromFat], -1)
AND
coalesce([NutritionDetails].[TotalFat], -1) = coalesce(@TotalFat, [NutritionDetails].[TotalFat], -1)
AND
coalesce([NutritionDetails].[SaturatedFat], -1) = coalesce(@SaturatedFat, [NutritionDetails].[SaturatedFat], -1)
AND
coalesce([NutritionDetails].[TransFat], -1) = coalesce(@TransFat, [NutritionDetails].[TransFat], -1)
AND
coalesce([NutritionDetails].[Cholesteral], -1) = coalesce(@Cholesteral, [NutritionDetails].[Cholesteral], -1)
AND
coalesce([NutritionDetails].[Sodium], -1) = coalesce(@Sodium, [NutritionDetails].[Sodium], -1)
AND
coalesce([NutritionDetails].[TotalCarbohydrates], -1) = coalesce(@TotalCarbohydrates, [NutritionDetails].[TotalCarbohydrates], -1)
AND
coalesce([NutritionDetails].[DietaryFiber], -1) = coalesce(@DietaryFiber, [NutritionDetails].[DietaryFiber], -1)
AND
coalesce([NutritionDetails].[Sugar], -1) = coalesce(@Sugar, [NutritionDetails].[Sugar], -1)
AND
coalesce([NutritionDetails].[Protein], -1) = coalesce(@Protein, [NutritionDetails].[Protein], -1)
AND
coalesce([NutritionDetails].[VitaminA], -1) = coalesce(@VitaminA, [NutritionDetails].[VitaminA], -1)
AND
coalesce([NutritionDetails].[VitaminC], -1) = coalesce(@VitaminC, [NutritionDetails].[VitaminC], -1)
AND
coalesce([NutritionDetails].[Calcium], -1) = coalesce(@Calcium, [NutritionDetails].[Calcium], -1)
AND
coalesce([NutritionDetails].[Iron], -1) = coalesce(@Iron, [NutritionDetails].[Iron], -1)


END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertNutritionDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================

CREATE PROCEDURE [dbo].[InsertNutritionDetails]
(
@RecipeID int,
@Calories float,
@CaloriesFromFat float,
@TotalFat float,
@SaturatedFat float,
@TransFat float,
@Cholesteral float,
@Sodium float,
@TotalCarbohydrates float,
@DietaryFiber float,
@Sugar float,
@Protein float,
@VitaminA int,
@VitaminC int,
@Calcium int,
@Iron int,
@newNutritionId int OUTPUT
)

AS


BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
	
SET NOCOUNT ON


INSERT INTO [NutritionDetails]
([NutritionDetails].[RecipeID],[NutritionDetails].[Calories], [NutritionDetails].[CaloriesFromFat], 
[NutritionDetails].[TotalFat], [NutritionDetails].[SaturatedFat],[NutritionDetails].[TransFat], [NutritionDetails].[Cholesteral],
[NutritionDetails].[Sodium],[NutritionDetails].[TotalCarbohydrates],[NutritionDetails].[DietaryFiber],[NutritionDetails].[Sugar],
[NutritionDetails].[Protein],[NutritionDetails].[VitaminA],[NutritionDetails].[VitaminC],[NutritionDetails].[Calcium],[NutritionDetails].[Iron])
VALUES     
(@RecipeID,@Calories,@CaloriesFromFat,@TotalFat,@SaturatedFat,@TransFat,@Cholesteral,@Sodium,@TotalCarbohydrates,@DietaryFiber,@Sugar,
@Protein,@VitaminA,@VitaminC,@Calcium,@Iron)


--Return the current id that was inserted
SET @newNutritionId = SCOPE_IDENTITY()

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForUnitMeasurement]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[SearchForUnitMeasurement]	
(
	@idToSearch int = NULL,
	@nameToSearch varchar(50) = NULL
)
	
AS

SET NOCOUNT ON 
	
	
BEGIN

SELECT     
[UnitMeasurementID], [UnitMeasurements].[Name]
FROM         
[UnitMeasurements]
WHERE   
coalesce([UnitMeasurementID], -1) = coalesce(@idToSearch, [UnitMeasurementID], -1)
AND     
[UnitMeasurements].[Name] = coalesce(@nameToSearch, [UnitMeasurements].[Name])

END

	
RETURN


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveAllUnitMeasurements]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveAllUnitMeasurements]

	
AS

SET NOCOUNT ON 

BEGIN

SELECT	
UnitMeasurements.UnitMeasurementID, UnitMeasurements.Name
FROM	
[UnitMeasurements]
ORDER BY 
UnitMeasurements.Name

END

RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveUnitMeasurementById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveUnitMeasurementById]	
(
	@unitMeasurementIdToSearch int 
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT	
[UnitMeasurements].[UnitMeasurementID], [UnitMeasurements].[Name]
FROM	
[UnitMeasurements]
WHERE     
([UnitMeasurements].[UnitMeasurementID] = @unitMeasurementIdToSearch)

END
	
RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertUnitMeasurement]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		<Author,Name>
-- Create date: <Create Date>
-- Description:	<Description>
-- =============================================


CREATE PROCEDURE [dbo].[InsertUnitMeasurement]
(
	@unitMeaNameToInsert varchar(50),
	@Identity int OUT
)
	
AS

SET NOCOUNT ON 

BEGIN

INSERT INTO [UnitMeasurements]
([UnitMeasurements].[Name])
VALUES     
(@unitMeaNameToInsert)

SET @Identity = @@Identity

END
	
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateUnitMeasurement]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[UpdateUnitMeasurement]
(
	@Original_UnitMeasurementID varchar(50),
	@Original_Name varchar(50),
	@newUnitMeasurementName varchar(50)
)
	
AS
	
SET NOCOUNT ON 

BEGIN

UPDATE    
[UnitMeasurements]
SET       
[UnitMeasurements].[Name] = @newUnitMeasurementName
WHERE     
([UnitMeasurementID] = @Original_UnitMeasurementID) AND ([UnitMeasurements].[Name] = @Original_Name)

END

RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForCuisine]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[SearchForCuisine]
(
	@idToSearch int = NULL,
	@nameToSearch varchar(50) = NULL
)
	
AS
	
SET NOCOUNT ON 

BEGIN
	
SELECT     
[CuisineTypeID], [CuisineTypes].[Name]
FROM         
[CuisineTypes]
WHERE   
coalesce([CuisineTypeID], -1) = coalesce(@idToSearch, [CuisineTypeID], -1)
AND     
[CuisineTypes].[Name] = coalesce(@nameToSearch, [CuisineTypes].[Name])

END
   
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveCuisineById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveCuisineById]
(
	@cuisineIdToSearch int
)
	
AS

SET NOCOUNT ON 
 
BEGIN
 
SELECT     
[CuisineTypeID], [CuisineTypes].[Name]
FROM         
[CuisineTypes]
WHERE  
([CuisineTypeID]= @cuisineIdToSearch)

END
 
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveAllCuisines]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveAllCuisines]

		
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT     
[CuisineTypeID], [CuisineTypes].[Name]
FROM         
[CuisineTypes]
ORDER BY 
[CuisineTypes].[Name]

END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateCuisine]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Andy Churchill>
-- Create date: <12/2/12>
-- Description: <>
-- =============================================

CREATE PROCEDURE [dbo].[UpdateCuisine]
(
	@updatedName varchar(50),
	@Original_CuisineTypeID int,
	@Original_Name varchar(50)
)
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

BEGIN

UPDATE    
[CuisineTypes]
SET              
[CuisineTypes].[Name] = @updatedName
WHERE     
([CuisineTypes].[CuisineTypeID] = @Original_CuisineTypeID) 
AND 
([CuisineTypes].[Name] = @Original_Name)

END
	
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCuisine]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[InsertCuisine]
(
	@cuisineTypeName varchar(50),
	@Identity int OUT
)
	
AS
 
 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

BEGIN
	
INSERT INTO [CuisineTypes]
([CuisineTypes].[Name])
VALUES     
(@cuisineTypeName)	

SET @Identity = @@Identity

END
	
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveFoodTypeById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/


CREATE PROCEDURE [dbo].[RetrieveFoodTypeById]
(
	@FoodTypeIdToSearch int
)
	
AS

SET NOCOUNT ON 
 
BEGIN
 
SELECT     
[FoodTypes].[FoodTypeID], [FoodTypes].[Name]
FROM         
[FoodTypes]
WHERE  
([FoodTypes].[FoodTypeID]= @FoodTypeIdToSearch)

END
 
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RetrieveAllFoodTypes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[RetrieveAllFoodTypes]
	
AS

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON 

BEGIN

SELECT     
[FoodTypes].[FoodTypeID], [FoodTypes].[Name]
FROM         
FoodTypes
ORDER BY 
[FoodTypes].[Name]

END

RETURN' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchForFoodType]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[SearchForFoodType]
(
	@idToSearch int = NULL,
	@nameToSearch varchar(50) = NULL
)
	
AS
	
SET NOCOUNT ON 

BEGIN
	
SELECT     
[FoodTypes].[FoodTypeID], [FoodTypes].[Name]
FROM         
[FoodTypes]
WHERE   
coalesce([FoodTypes].[FoodTypeID], -1) = coalesce(@idToSearch, [FoodTypes].[FoodTypeID], -1)
AND     
[FoodTypes].[Name] = coalesce(@nameToSearch, [FoodTypes].[Name])

END
   
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertFoodType]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

CREATE PROCEDURE [dbo].[InsertFoodType]
(
	@foodTypeToInsert varchar(50),
	@Identity int OUT
)
	
AS

SET NOCOUNT ON 
	
BEGIN

INSERT INTO [FoodTypes]  
([FoodTypes].[Name])
VALUES
(@foodTypeToInsert)       

SET @Identity = @@Identity

END	
	
	
RETURN
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateFoodType]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'/*  Description
   
    Input parameters
     
    Output select list
   
    Author
     
    Release date    
*/

CREATE PROCEDURE [dbo].[UpdateFoodType]
(
	@updatedName varchar(50),
	@Original_FoodTypesID int,
	@Original_Name varchar(50)
)
	
AS

SET NOCOUNT ON 

BEGIN

UPDATE    
[FoodTypes]
SET              
[FoodTypes].[Name] = @updatedName
WHERE     
([FoodTypes].[FoodTypeID] = @Original_FoodTypesID) AND ([FoodTypes].[Name] = @Original_Name)

END
	
RETURN' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Recipes_Accounts]') AND parent_object_id = OBJECT_ID(N'[dbo].[Recipes]'))
ALTER TABLE [dbo].[Recipes]  WITH CHECK ADD  CONSTRAINT [FK_Recipes_Accounts] FOREIGN KEY([AccountId])
REFERENCES [dbo].[Accounts] ([AccountID])
GO
ALTER TABLE [dbo].[Recipes] CHECK CONSTRAINT [FK_Recipes_Accounts]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Recipes_CourseTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[Recipes]'))
ALTER TABLE [dbo].[Recipes]  WITH CHECK ADD  CONSTRAINT [FK_Recipes_CourseTypes] FOREIGN KEY([CourseTypeID])
REFERENCES [dbo].[CourseTypes] ([CourseTypeID])
GO
ALTER TABLE [dbo].[Recipes] CHECK CONSTRAINT [FK_Recipes_CourseTypes]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Recipes_CuisineTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[Recipes]'))
ALTER TABLE [dbo].[Recipes]  WITH CHECK ADD  CONSTRAINT [FK_Recipes_CuisineTypes] FOREIGN KEY([CuisineTypeID])
REFERENCES [dbo].[CuisineTypes] ([CuisineTypeID])
GO
ALTER TABLE [dbo].[Recipes] CHECK CONSTRAINT [FK_Recipes_CuisineTypes]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RecipeIngredient_Ingredients]') AND parent_object_id = OBJECT_ID(N'[dbo].[RecipeIngredient]'))
ALTER TABLE [dbo].[RecipeIngredient]  WITH CHECK ADD  CONSTRAINT [FK_RecipeIngredient_Ingredients] FOREIGN KEY([IngredientID])
REFERENCES [dbo].[Ingredients] ([IngredientID])
GO
ALTER TABLE [dbo].[RecipeIngredient] CHECK CONSTRAINT [FK_RecipeIngredient_Ingredients]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RecipeIngredient_Recipes]') AND parent_object_id = OBJECT_ID(N'[dbo].[RecipeIngredient]'))
ALTER TABLE [dbo].[RecipeIngredient]  WITH CHECK ADD  CONSTRAINT [FK_RecipeIngredient_Recipes] FOREIGN KEY([RecipeID])
REFERENCES [dbo].[Recipes] ([RecipeID])
GO
ALTER TABLE [dbo].[RecipeIngredient] CHECK CONSTRAINT [FK_RecipeIngredient_Recipes]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RecipeIngredient_UnitMeasurements]') AND parent_object_id = OBJECT_ID(N'[dbo].[RecipeIngredient]'))
ALTER TABLE [dbo].[RecipeIngredient]  WITH CHECK ADD  CONSTRAINT [FK_RecipeIngredient_UnitMeasurements] FOREIGN KEY([UnitMeasurementID])
REFERENCES [dbo].[UnitMeasurements] ([UnitMeasurementID])
GO
ALTER TABLE [dbo].[RecipeIngredient] CHECK CONSTRAINT [FK_RecipeIngredient_UnitMeasurements]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_NutritionDetails_Recipes]') AND parent_object_id = OBJECT_ID(N'[dbo].[NutritionDetails]'))
ALTER TABLE [dbo].[NutritionDetails]  WITH CHECK ADD  CONSTRAINT [FK_NutritionDetails_Recipes] FOREIGN KEY([RecipeID])
REFERENCES [dbo].[Recipes] ([RecipeID])
GO
ALTER TABLE [dbo].[NutritionDetails] CHECK CONSTRAINT [FK_NutritionDetails_Recipes]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Ingredients_FoodTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[Ingredients]'))
ALTER TABLE [dbo].[Ingredients]  WITH CHECK ADD  CONSTRAINT [FK_Ingredients_FoodTypes] FOREIGN KEY([FoodTypeID])
REFERENCES [dbo].[FoodTypes] ([FoodTypeID])
GO
ALTER TABLE [dbo].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_FoodTypes]
