/* -------------------------------------------------------------------------
   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

-------------------------------------------------------------------------------
-- NOTE: It is possible that more than one skill is associated with a person.
-- Therefore, this procedure and the next are implemented to add and remove
-- skills associated with people.

CREATE PROCEDURE AssociatePersonSkill( @PersonId INT, @SkillId INT ) AS

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

	-- Check to make sure the person exists.
	DECLARE @TempPersonTable TABLE ( one INT )
	INSERT INTO @TempPersonTable
		SELECT TOP 1 1
		FROM PERSON
		WHERE Id = @PersonId;
	IF @@ROWCOUNT <> 1
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('Invalid person id.', 16, -1, @PersonId)
		RETURN 50000
	END

	-- Check to make sure the skill exists.
	DECLARE @TempSkillTable TABLE ( one INT )
	INSERT INTO @TempSkillTable
		SELECT TOP 1 1
		FROM SKILL
		WHERE Id = @SkillId;
	IF @@ROWCOUNT <> 1
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('Invalid skill id.', 16, -1, @PersonId)
		RETURN 50000
	END

	-- Check to make sure the skill isn't already associated.
	DECLARE @TempPersonSkillTable TABLE ( one INT )
	INSERT INTO @TempPersonSkillTable
		SELECT TOP 1 1
		FROM PERSON_SKILL
		WHERE SkillId = @SkillId AND PersonId = @PersonId;
	IF @@ROWCOUNT <> 0
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('The skill is already associated.', 16, -1, @SkillId)
		RETURN 50000
	END

	-- Finally, make the association.
	INSERT INTO PERSON_SKILL ( SkillId, PersonId )
	VALUES ( @SkillId, @PersonId );

	COMMIT TRANSACTION
GO

-------------------------------------------------------------------------------
CREATE PROCEDURE DisassociatePersonSkill( @PersonId INT, @SkillId INT ) AS

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

	-- Check to make sure the skill is associated.
	SELECT TOP 1 1 FROM PERSON_SKILL
	WHERE SkillId = @SkillId AND PersonId = @PersonId;
	IF @@ROWCOUNT <> 1
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('The skill is not associated.', 16, -1, @SkillId)
		RETURN 50000
	END

	-- Finally, disassocate the person and skill.
	DELETE FROM PERSON_SKILL
	WHERE SkillId = @SkillId AND PersonId = @PersonId;

	COMMIT TRANSACTION
GO

-------------------------------------------------------------------------------
-- NOTE: This procedure does not allow a change to the badge issuer since this
-- is something that happens only once when the employee is hired.  Similarly,
-- this procedure does not allow a change to the badge number.  If the person's
-- card is damaged, it is assumed that the new badge would have the same
-- number.

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

	-- Verify the employment status is valid.
	IF @EmploymentStatus NOT IN ('F', 'P')
	BEGIN
		ROLLBACK TRANSACTION
		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

	-- Check to make sure the person exists.
	SELECT TOP 1 1 FROM PERSON WHERE Id = @PersonId;
	IF @@ROWCOUNT <> 1
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('Invalid person id.', 16, -1, @PersonId)
		RETURN 50000
	END

	-- Get the previous employment status.
	DECLARE @OldStatus NCHAR(1)
	SELECT @OldStatus = EmploymentStatus FROM PERSON WHERE Id = @PersonId;

	-- Check if the employment status is changing to part time.
	IF @OldStatus = 'F' AND @EmploymentStatus = 'P'
	BEGIN
		-- Delete the old entry.
		DELETE FROM FULL_TIME WHERE Id = @PersonId;

		-- Add the new record into the other table.
		INSERT INTO PART_TIME ( Id, HourlySalary )
		VALUES ( @PersonId, @Money );
	END

	-- Check if the employment status is changing to full time.
	ELSE IF @OldStatus = 'P' AND @EmploymentStatus = 'F'
	BEGIN
		-- Delete the old entry.
		DELETE FROM PART_TIME WHERE Id = @PersonId;

		-- Add the new record into the other table.
		INSERT INTO FULL_TIME ( Id, AnnualWage )
		VALUES ( @PersonId, @Money );
	END

	-- Otherwise, update the full time table if they are full time.
	ELSE IF @EmploymentStatus = 'F'
	BEGIN
		UPDATE FULL_TIME SET AnnualWage = @Money WHERE Id = @PersonId
	END

	-- Otherwise, update the part time table if they are part time.
	ELSE IF @EmploymentStatus = 'P'
	BEGIN
		UPDATE PART_TIME SET HourlySalary = @Money WHERE Id = @PersonId
	END

	-- Update the person table.
	UPDATE PERSON SET
		FirstName        = @FirstName,
		LastName         = @LastName,
		MiddleName       = @MiddleName,
		DateOfBirth      = @DateOfBirth,
		EmploymentStatus = @EmploymentStatus
	WHERE Id = @PersonId;

	COMMIT TRANSACTION
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';

-- Change some information about the person.
EXECUTE UpdatePersonInstance
	@PersonId,
	'Cheng2', 'Yu2', NULL,
	'10/14/1984',
	'P',
	250

-- Display information about the person; join the full or part time tables.
-- This returns:
--
--   FirstName LastName MiddleName DateOfBirth EmpStatus Badge HourlySalary
-- - --------- -------- ---------- ----------- --------- ----- ------------
-- 1 Cheng2    Yu2      NULL       1984-10-14  P         4276  250.00
--
SELECT FirstName, LastName, MiddleName, DateOfBirth,
	EmploymentStatus, Badge, HourlySalary
	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 added.
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

*/
