-- Download AdventureWorksLT from 
-- http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx
-- Script to create Credit schema, CreditHold table, views and stored procedures.

USE [AdventureWorksLT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------
-------------------------------------------------------------------

-- Delete dependent objects first
IF EXISTS(SELECT name FROM
  sys.procedures WHERE name='InsertCreditHold')  
    DROP PROCEDURE Credit.InsertCreditHold;
GO

IF EXISTS(SELECT name FROM
  sys.procedures WHERE name='RemoveCreditHold')  
    DROP PROCEDURE Credit.RemoveCreditHold;
GO

IF EXISTS(SELECT name FROM
  sys.tables WHERE name='CreditHold')  
    DROP TABLE Credit.CreditHold;
GO

IF EXISTS(SELECT name FROM
  sys.database_principals WHERE name='Accounting')  
    DROP ROLE Accounting;
GO

IF EXISTS(SELECT name FROM
  sys.schemas WHERE name='Credit')  
    DROP SCHEMA Credit;
GO

-------------------------------------------------------------------
-------------------------------------------------------------------
-- Create the Accounting role
CREATE ROLE Accounting;
GO

-- Create the Credit schema, granting SELECT and EXECUTE to  public.
-- Users will be able to select data from tables and views, but they
-- won't be able to modify it. Granting EXECUTE permissions allows
-- for data modifications through stored procedures.
CREATE SCHEMA [Credit] AUTHORIZATION [dbo]
GO
GRANT SELECT ON SCHEMA::Credit TO public;
GRANT EXECUTE ON SCHEMA :: Credit TO public;

-- Members of the Accounting role inherit the permissions
-- granted to public. This statement grants them additional 
-- permissions on objects contained in the Credit schema.
GRANT DELETE, INSERT, UPDATE ON SCHEMA::Credit TO Accounting;
GO
-------------------------------------------------------------------
-------------------------------------------------------------------

-- Create CreditHold table
CREATE TABLE Credit.CreditHold
(
	CreditHoldID int IDENTITY(1000,1) NOT NULL,
	CustomerID int NOT NULL,
	DatePlaced smalldatetime NOT NULL,
	DateReleased smalldatetime NULL,
	SalesPersonEmail nvarchar(50) NULL
) ON [PRIMARY]
GO

ALTER TABLE Credit.CreditHold
	ADD CONSTRAINT CreditHoldID_PK PRIMARY KEY (CreditHoldID);
GO

-------------------------------------------------------------------
-------------------------------------------------------------------

CREATE PROCEDURE Credit.InsertCreditHold
	@CustomerID int = NULL,
	@SalesPersonEmail nvarchar(50) = NULL OUTPUT,
	@CreditHoldID int = NULL OUTPUT,
	@ReturnCode int = NULL OUTPUT,
	@ReturnMessage nvarchar(255) = NULL OUTPUT 
AS
SET NOCOUNT ON

BEGIN TRY
	-- Test if CustomerID is null. Raising an error here will
	-- branch to the code in BEGIN CATCH block.
	IF @CustomerID IS NULL
		RAISERROR ('A valid CustomerID is required.', 16, 1);

	-- Test if Customer already exists and retrieve SalesPersonEmail.
	SET @SalesPersonEmail = (
		SELECT SalesPerson FROM SalesLT.Customer WHERE CustomerID=@CustomerID)

	-- If there isn't a SalesPerson, then there isn't a CustomerID.
	IF @SalesPersonEmail IS NULL
		RAISERROR ('CustomerID does not exist.', 16, 1);

	-- Retrieve the current date, setting the time portion to zero.
	-- Setting the time to zero makes it easier to compare two different
	-- date values for equality. GETDATE() retrieves today's date.
	DECLARE @DatePlaced smalldatetime;
	SET @DatePlaced = (
		SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))))

		-- Raise an error if the INSERT would create a duplicate credit hold 
		-- in this timeframe.
		-- Begin the transaction. 
		BEGIN TRANSACTION
			SELECT @CreditHoldID = CreditHoldID FROM Credit.CreditHold WITH (REPEATABLEREAD)
			WHERE CustomerID=@CustomerID AND DateReleased IS NULL;
			
			IF @CreditHoldID IS NOT NULL
				RAISERROR ('A credit hold already exists for this customer.', 16, 1);
				INSERT INTO 
					Credit.CreditHold (CustomerID, DatePlaced, SalesPersonEmail)
				VALUES (@CustomerID, @DatePlaced, @SalesPersonEmail);

				-- Check to make sure the update succeeded and commit the transaction.
				IF @@ROWCOUNT > 0 AND @@ERROR = 0
					BEGIN
						COMMIT
						-- Retrieve the new CreditHoldID value and return it in the
						-- output parameter along with the return message.
						SET @CreditHoldID=SCOPE_IDENTITY();
						SELECT @ReturnCode=0, 
							@ReturnMessage='Credit Hold Number: ' + 
								CAST(@CreditHoldID AS nvarchar(10));
					END
				ELSE
					-- If the update failed for any reason (@@ROWCOUNT=0 will not throw
					-- to the catch block), then notify the caller that the insert failed.
					RAISERROR ('Unknown error; credit hold not inserted.', 16, 1); 
END TRY

BEGIN CATCH
	-- Test to see if there is an open transaction before rolling back.
	IF @@TRANCOUNT > 0
		ROLLBACK

	-- Return error information to client.
	SET @ReturnCode = ERROR_NUMBER();
	SET @ReturnMessage = ERROR_MESSAGE();
END CATCH
GO

-------------------------------------------------------------------
-------------------------------------------------------------------
--CREATE PROCEDURE Credit.RemoveCreditHold
ALTER PROCEDURE Credit.RemoveCreditHold
	@CreditHoldID int = NULL
AS
SET NOCOUNT ON

DECLARE @ReturnMessage nvarchar(255)

-- Assume success by initializing the return message.
SELECT @ReturnMessage='Credit hold released: ' + 
	CAST(@CreditHoldID AS nvarchar(10));

BEGIN TRY
	-- Validate the input parameter.
	IF @CreditHoldID IS NULL
		RAISERROR ('A valid CreditHoldID is required.', 16, 1);

	-- Set the DateReleased to today's date.
	DECLARE @DateReleased smalldatetime;
	SET @DateReleased = (
		SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))))

	-- Perform the update.
	UPDATE Credit.CreditHold
		SET DateReleased = @DateReleased
		WHERE CreditHoldID = @CreditHoldID AND DateReleased IS NULL;

	-- Check to see if the row was updated.
	IF @@ROWCOUNT = 0 
		RAISERROR ('CreditHold already released or does not exist.', 16, 1);
END TRY

BEGIN CATCH
	-- Process RAISERROR messages.
	SELECT @ReturnMessage = 'Error ' + CAST(ERROR_NUMBER() AS nvarchar(10)) + 
		': ' + ERROR_MESSAGE();
END CATCH

-- Return result set to client.
SELECT @ReturnMessage AS ReturnMessage
GO

-------------------------------------------------------------------
-------------------------------------------------------------------
----UNCOMMENT THE FOLLOWING CODE TO TEST THE STORED PROCEDURES
-------------------------------------------------------------------

--USE [AdventureWorksLT]
--GO
------ InsertCreditHold
------ Execute the Stored Procedure
--DECLARE @CustomerID int
--DECLARE @SalesPersonEmail nvarchar(50)
--DECLARE @CreditHoldID int
--DECLARE @ReturnCode int
--DECLARE @ReturnMessage nvarchar(255)
--
--SET @CustomerID = 9
--
--EXECUTE Credit.InsertCreditHold 
--	@CustomerID, 
--	@SalesPersonEmail OUTPUT, 
--	@CreditHoldID OUTPUT,
--	@ReturnCode OUTPUT, 
--	@ReturnMessage OUTPUT;
--
--SELECT @CustomerID AS CustomerID,
--	@SalesPersonEmail AS SalesPerson, 
--	@CreditHoldID AS CreditHoldID,
--	@ReturnCode AS ReturnCode, 
--	@ReturnMessage AS ReturnMessage;
--GO
--	SELECT * FROM Credit.CreditHold
--
--
---- RemoveCreditHold
--DECLARE @CreditHoldID int
--SET @CreditHoldID = null
--
--EXECUTE Credit.RemoveCreditHold 
--	@CreditHoldID;
--GO
--	SELECT * FROM Credit.CreditHold


