USE [IST278EagleCorp13-1]

GO

DROP PROC sp_AddPotentialCustomers

GO 

CREATE PROC sp_AddPotentialCustomers
--Declare variables.
@CustomerID		varchar(10) = NULL,
@CustFirstName	varchar(15) = NULL,
@CustLastName	varchar(20) = NULL,
@Phone			varchar(12) = NULL,
@CreditScore	char(1) = NULL

AS
DECLARE @EmployeeID		varchar(10)
--@Customer ID is blank.
IF @CustomerID IS NULL
	BEGIN
		PRINT 'You must enter a Customer ID for '+ @CustFirstName + ' ' + @CustLastName
		PRINT 'Customer not inserted.'
		PRINT ' '
		RETURN
	END
--@Customer ID not 8 characters or with I or C in the substring.
IF LEN(@CustomerID) != 8 OR SUBSTRING(@CustomerID, 1, 1) NOT IN ('I','C')
	BEGIN
		PRINT 'Invalid ID ' + @CustomerID + ' Must be 8 characters'
		PRINT 'and must begin with either a "C" or "I".'
		PRINT 'Customer not inserted.'
		PRINT ' '
		RETURN
	END
--@Phone is blank.
IF @Phone IS NULL
	BEGIN
		PRINT 'Phone Number is required for "Potential Customer" '+ @CustomerID+ '.'
		PRINT 'Customer not inserted.'
		PRINT ' '
		RETURN
	END
--@Credit Score is blank or not in the CreditRating table.
IF @CreditScore IS NULL OR @CreditScore NOT IN(SELECT CreditScore FROM CreditRating)
	BEGIN
		PRINT 'Credit Score must be entered and must exists in the CreditRating table.'
		PRINT 'Customer not inserted.'
		PRINT ' '
		RETURN
	END
--@Customer ID exists in the PotentialCustomer table.
IF EXISTS (SELECT * FROM PotentialCustomer WHERE CustomerID = @CustomerID)
	BEGIN
		PRINT 'Customer ID '+ @CustomerID +' already exists in the PotentialCustomer table.'
		PRINT 'Customer not inserted.'
		PRINT ' '
		RETURN
	END

--Begin inserting values into the PotentialCustomer table (Transaction).
BEGIN TRANSACTION
INSERT INTO PotentialCustomer
	(CustomerID, CustFirstName, CustLastName, Phone, CreditScore)
VALUES (@CustomerID, @CustFirstName, @CustLastName, @Phone, @CreditScore)

--Validating that @EmployeeID is in the Employee and ContactAssignment table.
SELECT @EmployeeID = 
(		SELECT TOP 1 e.EmployeeID FROM Employee e
		LEFT OUTER JOIN ContactAssignment ca
		ON e.EmployeeID = ca.EmployeeID
		WHERE e.JobTitle = 'Sales'
		GROUP BY e.employeeID
		ORDER BY COUNT(*) ASC
)

--Begin inserting values into the ContactAssignment table.
INSERT INTO ContactAssignment
	(EmployeeID, CustomerID, DateAssigned, ContactAttempted, Status)
VALUES (@EmployeeID, @CustFirstName, CONVERT(smalldatetime, CONVERT(varchar, GETDATE(), 101)), 'N', 'P')

--@CustomerID exists in the Customer table.
IF EXISTS (SELECT CustomerID FROM Customer WHERE CustomerID = @CustomerID)
	BEGIN
		PRINT 'Customer ID '+ @CustomerID +' already exists in the Customer table.'
		PRINT 'Customer not inserted.'
		PRINT ' '
		ROLLBACK
	END
ELSE
--Safe to add the new transaction.
	COMMIT TRANSACTION
	BEGIN
		PRINT 'Customer '+ @CustomerID +' added to the PotentialCustomer table'
		PRINT 'and assigned to Employee '+ @EmployeeID +'.'
		PRINT ' '
    END
--End of Stored Procedure.

GO

--Inserting values into Potential Customer Table.

--Error in the CustFirstName (NULL allowed, Transaction accepted).
EXEC sp_AddPotentialCustomers
	@CustomerID = 'I-100375',
	@CustFirstName = '',
	@CustLastName = 'Schmolick',
	@Phone = '864-551-8624', 
	@CreditScore = 'A'

--Error in the CreditScore.
EXEC sp_AddPotentialCustomers
	@CustomerID = 'C-100106',
	@CustFirstName = 'Rick',
	@CustLastName = 'Flynn',
	@Phone = '561-304-7900',
	@CreditScore = 'H'

--Error in the CustomerID.
EXEC sp_AddPotentialCustomers
	@CustomerID	= 'C-00112',
	@CustFirstName = 'Rick',
	@CustLastName = 'Twoey',
	@Phone = '505-664-2107',
	@CreditScore = 'C'

--Error in the CustLastName (NULL allowed, Transaction accepted).
EXEC sp_AddPotentialCustomers
	@CustomerID	= 'C-100984',
	@CustFirstName = 'Kate',
	@CustLastName = '',
	@Phone = '505-971-8897',
	@CreditScore = 'B'

--Error in the Phone Number.
EXEC sp_AddPotentialCustomers
	@CustomerID	= 'C-101204',
	@CustFirstName = 'Ben',
	@CustLastName = 'Heydrich',
	@Phone = '', 
	@CreditScore = 'A'

--Error in CustomerID (blank).
EXEC sp_AddPotentialCustomers
	@CustomerID	= '',
	@CustFirstName = 'Chris',
	@CustLastName = 'Heydrich',
	@Phone = '561-522-9641', 
	@CreditScore = 'E'

--Error: CustomerID is in the Customer Table.
EXEC sp_AddPotentialCustomers
	@CustomerID	= 'C-300007',
	@CustFirstName = 'Ben',
	@CustLastName = 'Heydrich',
	@Phone = '561-522-9461', 
	@CreditScore = 'A'

--End of executing inserted values.

GO