/* -------------------------------------------------------------------------
   Jade Cheng
   ICS 321 Assignment 3
   December 12, 2008

   PART 2.2
   Create a stored procedure that will create an instance of a person. This
   stored procedure must also be able to create an instance of a
   PERSON_SKILL, BADGE assignment, Full Time data or Part Time data
   depending on the employee status. Name this procedure
   CreatePersonInstance. Generate your own user defined error message for
   error conditions.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-------------------------------------------------------------------------------
-- These are optional tables to return a skill id or badge issuer for a name.
-- These potentially simplify use of the CreatePersonInstance procedure
-- defined below.

CREATE FUNCTION GetSkillId(@Title NVARCHAR(50)) RETURNS INT AS
BEGIN
	DECLARE @Result INT
	SELECT TOP 1 @Result = Id FROM SKILL WHERE Title = @Title
	RETURN @Result
END
GO

-------------------------------------------------------------------------------
CREATE FUNCTION GetBadgeIssuerId(@Name NVARCHAR(50)) RETURNS INT AS
BEGIN
	DECLARE @Result INT
	DECLARE @Pattern NVARCHAR(52)

	SET @Pattern = '%' + @Name + '%';

	SELECT TOP 1 @Result = Id
		FROM PERSON_PROFILE
		JOIN PERSON ON PERSON_PROFILE.PersonId = PERSON.Id
		WHERE ProfileId = 'Scty' AND dbo.PersonName(PERSON.Id) LIKE @Pattern

	RETURN @Result
END
GO

-------------------------------------------------------------------------------
-- NOTE: The assignment instructions seem to suggest that each person is
-- always assigned one skill.  This is not necessarily the case.  The
-- PERSON_SKILL table allows zero or more associations between people and
-- skills.  Regardless, this stored procedure will always create a person
-- with an initially-assigned skill.

CREATE PROCEDURE CreatePersonInstance(
	@BadgeIssuerId    INT,
	@FirstName        NVARCHAR(20),
	@LastName         NVARCHAR(20),
	@MiddleName       NCHAR(1),
	@DateOfBirth      DATETIME,
	@EmploymentStatus NCHAR(1),
	@Money            MONEY,
	@SkillId          INT)
	AS

	-- Verify the employment status is valid.
	IF @EmploymentStatus NOT IN ('F', 'P')
	BEGIN
		RAISERROR('EmploymentStatus is invalid (F/P).', 16, -1, @EmploymentStatus)
		RETURN 50000
	END

	-- Lock the database in here.  Use the most restrictive lock because speed
	-- is not an issue.
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	-- Verify the skill id is valid.
	DECLARE @TempSkillTable TABLE ( one INT )
	INSERT INTO @TempSkillTable SELECT TOP 1 1 FROM SKILL WHERE Id = @SkillId
	IF @@ROWCOUNT <> 1
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('Illegal skill id.', 16, -1, @SkillId)
		RETURN 50000
	END

	-- Verify the badge issuer is valid.
	DECLARE @TempBadgeTable TABLE ( one INT )
	INSERT INTO @TempBadgeTable SELECT TOP 1 1 FROM PERSON_PROFILE
		WHERE ProfileId='Scty' AND PersonId=@BadgeIssuerId;
	IF @@ROWCOUNT <> 1
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('Illegal badge issuer id.', 16, -1, @BadgeIssuerId)
		RETURN 50000
	END

	-- Determine the next available badge id.
	DECLARE @BadgeId INT
	SELECT @BadgeId = MAX(Id) FROM BADGE;
	SET @BadgeId = @BadgeId + 1;

	-- Create the new badge record with this id.
	INSERT INTO BADGE (Id, IssuingPerson, IssueDate)
	VALUES (@BadgeId, @BadgeIssuerId, GETDATE());

	-- Determine the next available person id.
	DECLARE @PersonId INT
	SELECT @PersonId = MAX(Id) FROM PERSON;
	SET @PersonId = @PersonId + 1;

	-- Create the new person record with this badge id.
	INSERT INTO PERSON
	(
		Id,
		FirstName,
		LastName,
		MiddleName,
		DateOfBirth,
		EmploymentStatus,
		Badge
	)
	VALUES
	(
		@PersonId,
		@FirstName,
		@LastName,
		@MiddleName,
		@DateOfBirth,
		@EmploymentStatus,
		@BadgeId
	);

	-- Create a record in the FULL_TIME or PART_TIME table.
	IF @EmploymentStatus = 'F'
		INSERT INTO FULL_TIME ( Id, AnnualWage )
		VALUES ( @PersonId, @Money );
	ELSE
		INSERT INTO PART_TIME ( Id, HourlySalary )
		VALUES ( @PersonId, @Money );

	-- Create a record in the PERSON_SKILL table that associates the new person
	-- with an initial skill.  The assessment and review dates are initially
	-- null.
	INSERT INTO PERSON_SKILL ( SkillId, PersonId )
	VALUES ( @SkillId, @PersonId );

	COMMIT TRANSACTION

	-- Return the person id.
	SELECT Id FROM PERSON WHERE Id = @PersonId
GO

-------------------------------------------------------------------------------
-- The following commented section was used to test the procedures above.

/*

-- Find a skill called 'Bilingual'.
DECLARE @SkillId INT
SET @SkillId = dbo.GetSkillId('Bilingual')

-- Find a badge issuer with the name 'Chen'.
DECLARE @BadgeIssuerId INT
SET @BadgeIssuerId = dbo.GetBadgeIssuerId('Chen')

-- Create a new person.
EXECUTE CreatePersonInstance
	@BadgeIssuerId,
	'Cheng', 'Yu', NULL,
	'10/13/1984',
	'F',
	15000,
	@SkillId;

-- Check that the id exists for the person just created.
DECLARE @PersonId INT
SELECT @PersonId = Id
	FROM PERSON
	WHERE FirstName = 'Cheng' AND LastName = 'Yu';

-- Display information about the person; join the full or part time tables.
-- This returns:
--
--   FirstName LastName MiddleName DateOfBirth EmpStatus Badge AnnualWage
-- - --------- -------- ---------- ----------- --------- ----- ----------
-- 1 Cheng     Yu       NULL       1984-10-13  F         4276  15000.00
--
SELECT FirstName, LastName, MiddleName, DateOfBirth,
	EmploymentStatus, Badge, AnnualWage
	FROM PERSON
	LEFT JOIN FULL_TIME ON PERSON.Id = FULL_TIME.Id
	LEFT JOIN PART_TIME ON PERSON.Id = PART_TIME.Id
	WHERE PERSON.Id = @PersonId;

-- Determine the badge id for the person just created.
DECLARE @BadgeId INT
SELECT @BadgeId = Badge FROM PERSON WHERE Id = @PersonId;

-- Display the name of the person that issued the badge.
-- This returns:
--
--   (No column name)
-- - ----------------
-- 1 Chen, Rue P
--
SELECT dbo.PersonName(IssuingPerson)
	FROM BADGE
	WHERE Id = @BadgeId;

-- Display the skill names associated with this person.
-- This returns:
--
--      Title       (No column name)
-- ---- ----------- ----------------
-- 1    Bilingual   Yu, Cheng
--
SELECT Title, dbo.PersonName(PersonId)
	FROM SKILL JOIN PERSON_SKILL
	ON SKILL.Id = PERSON_SKILL.SkillId
	WHERE PERSON_SKILL.PersonId = @PersonId;

-- Remove the person records.
EXECUTE DeletePersonInstance @PersonId
GO

*/
