/*
Data tier SQL scripts for ISC 361 - Fall 2008
*/
/*
I plan to use tblGuest to authenticate users.
We need to add a column for Password.
We'll use email as the login
*/

/*
ALTER TABLE tblGuest
DROP COLUMN Password
*/
ALTER TABLE tblGuest
ADD Password VARCHAR(50) NULL

UPDATE tblGuest SET Password = 'mypass'

SELECT * FROM tblGuest

/*
Before we start though, we need to set priviledges
for the SQL User ISC361. We do not want this user
account to have direct access to tables. We want to
restrict acccess strictly to stored procedures (sprocs).
*/


--DROP PROC ValidateGuest
CREATE PROC ValidateGuest
  @Email AS NVARCHAR(50),
  @Password AS NVARCHAR(35)
AS
/*
Authored by Dr. Pardue
Last changed: 09/17/2008
Reason for change: created sproc
This sproc validates guest's login and password
and returns a boolean @IsFound 0=false, 1=true
*/
DECLARE @IsFound BIT

BEGIN
   IF EXISTS (SELECT *
              FROM tblGuest
              WHERE Email=@Email AND Password = @Password)
     SET @IsFound = 1
   ELSE
     SET @IsFound = 0

SELECT @IsFound AS IsFound

END

--Grant EXECUTE priviledges to the SQL Server user account
GRANT EXECUTE ON ValidateGuest TO ISC361


--Test cases
DECLARE @Email AS NVARCHAR(50), @Password AS NVARCHAR(35), @IsFound AS BIT

--Valid guest
SET @Email = 'patrick5468@yahoo.com'
SET @Password = 'mypass'

/*
--Invalid guest (email not in database)
SET @Email = 'myemail@usouthal.edu'
SET @Password = 'mypass'
*/
/*
--Invalid guest (password does not match)
SET @Email = 'patrick5468@yahoo.com'
SET @Password = 'mypass111'
*/

EXEC ValidateGuest @Email, @Password

/*
Create tblRole and the intersection table tblProfile.
table1 >----< table2
table1 ---< tblProfile  >--- table3
*/

CREATE TABLE tblRole
(
RoleID INT IDENTITY(1,1) PRIMARY KEY,
RoleName VARCHAR(15) NOT NULL,
RoleDescription VARCHAR(500) NULL
)

CREATE TABLE tblProfile
(
ProfileID INT IDENTITY(1,1) PRIMARY KEY,
RoleID INT REFERENCES tblRole(RoleID) NOT NULL,
GuestID INT REFERENCES tblGuest(GuestID) NOT NULL,
TimeRoleGranted DATETIME DEFAULT GETDATE() NOT NULL
)

INSERT INTO tblRole (RoleName, RoleDescription) VALUES
   ('Admin', 'Used for administrative fucntions...')
INSERT INTO tblRole (RoleName, RoleDescription) VALUES
   ('User', 'Used for general access...')
INSERT INTO tblRole (RoleName, RoleDescription) VALUES
   ('Developer', 'Used by developers for debugging and ...')

SELECT * FROM tblRole
SELECT * FROM tblGuest


INSERT INTO tblProfile (RoleID, GuestID) VALUES (1, 4)
SELECT * FROM tblProfile

INSERT INTO tblProfile (RoleID, GuestID)
SELECT 2 AS RoleID, GuestID
FROM tblGuest 

SELECT * FROM tblProfile WHERE GuestID = 4

SELECT GuestID, [1] AS Admin, 
       [2] AS UserR, [3] AS Developer
FROM (SELECT GuestID, RoleID
      FROM tblProfile 
      WHERE GuestID = 4) AS PivotTable
PIVOT
(COUNT(RoleID)
 FOR RoleID IN ([1], [2], [3])) AS PVT
ORDER BY GuestID

SELECT GuestID AS UserID, Email,  
      (SELECT ISNULL((SELECT 1 -- 1=true  0=false
                      FROM tblProfile
                      WHERE RoleID = 1 AND
                            tblProfile.GuestID = tblGuest.GuestID),0)) AS AdminRole, 
      (SELECT ISNULL((SELECT 1 -- 1=true  0=false
                      FROM tblProfile
                      WHERE RoleID = 2 AND
                            tblProfile.GuestID = tblGuest.GuestID),0)) AS UserRole, 
      (SELECT ISNULL((SELECT 1 -- 1=true  0=false
                      FROM tblProfile
                      WHERE RoleID = 3 AND
                            tblProfile.GuestID = tblGuest.GuestID),0)) AS DeveloperRole 
FROM tblGuest
WHERE GuestID = 4
ORDER BY GuestID


sp_help tblGuest

ALTER PROC GetUserProfileInfo
@Email VARCHAR(100)
AS
BEGIN
SELECT GuestID AS UserID, Email,  
      (SELECT ISNULL((SELECT 1 -- 1=true  0=false
                      FROM tblProfile
                      WHERE RoleID = 1 AND
                            tblProfile.GuestID = tblGuest.GuestID),0)) AS AdminRole, 
      (SELECT ISNULL((SELECT 1 -- 1=true  0=false
                      FROM tblProfile
                      WHERE RoleID = 2 AND
                            tblProfile.GuestID = tblGuest.GuestID),0)) AS UserRole, 
      (SELECT ISNULL((SELECT 1 -- 1=true  0=false
                      FROM tblProfile
                      WHERE RoleID = 3 AND
                            tblProfile.GuestID = tblGuest.GuestID),0)) AS DeveloperRole 
FROM tblGuest
WHERE Email = @Email
ORDER BY GuestID
END

EXEC GetUserProfileInfo 'patrick5468@yahoo.com'

GRANT EXECUTE ON GetUserProfileInfo TO ISC361

SELECT * FROM tblGuest
SELECT * FROM tblReservation

GRANT SELECT ON tblGuest TO ISC361
GRANT SELECT ON tblReservation TO ISC361

GRANT UPDATE ON tblReservation TO ISC361
GRANT DELETE ON tblReservation TO ISC361


CREATE TABLE tblCreditCard
(
CreditCardID INT IDENTITY(1,1) PRIMARY KEY,
CardName VARCHAR(50) NOT NULL,
CardStatus VARCHAR(15) DEFAULT 'Active'
 CHECK (CardStatus = 'Active' OR CardStatus = 'Delete')
)

INSERT INTO tblCreditCard (CardName) VALUES ('Visa')
INSERT INTO tblCreditCard (CardName) VALUES ('MasterCard')
INSERT INTO tblCreditCard (CardName) VALUES ('Discover')
INSERT INTO tblCreditCard (CardName) VALUES ('American Express')

SELECT * FROM tblCreditCard

INSERT INTO tblCreditCard (CardName, CardStatus) 
   VALUES ('American Express', 'Nothing')

SELECT CreditCardID, CardName 
FROM tblCreditCard
WHERE CardStatus = 'Active'

CREATE PROC GetCreditCardList
AS
BEGIN
  SELECT CreditCardID, CardName 
  FROM tblCreditCard
  WHERE CardStatus = 'Active'
END

EXEC GetCreditCardList

GRANT EXECUTE ON GetCreditCardList TO ISC361

UPDATE tblCreditCard 
SET CardStatus = 'Delete' 
WHERE CreditCardID=3


--Get a list of users by lastname
--Consider adding a concatenated attribute Address
SELECT *
FROM tblGuest

SELECT GuestID, LastName, FirstName,
      FirstName + ' ' + LastName AS FullName
      ,Street ,City
      ,State
      ,Zip
      ,Street + ' ' + 
      City  + ' ' +
      State  + ' ' +
      Zip AS FullAddress
FROM Cottages.dbo.tblGuest
WHERE LastName = 'Peter'

SELECT GuestID, LastName, FirstName,
      FirstName + ' ' + LastName AS FullName
      ,Street ,City
      ,State
      ,Zip
      ,Street + ' ' + 
      City  + ' ' +
      State  + ' ' +
      Zip AS FullAddress
FROM Cottages.dbo.tblGuest
WHERE LastName LIKE 'P%'

--Create the parameter
DECLARE @LastName VARCHAR(50)
SET @LastName = 'P'
SELECT GuestID, LastName, FirstName,
      FirstName + ' ' + LastName AS FullName
      ,Street ,City
      ,State
      ,Zip
      ,Street + ' ' + 
      City  + ' ' +
      State  + ' ' +
      Zip AS FullAddress
FROM Cottages.dbo.tblGuest
WHERE LastName LIKE @LastName + '%'

--What happens in this case?
DECLARE @LastName VARCHAR(50)
SET @LastName = NULL
SELECT GuestID, LastName, FirstName,
      FirstName + ' ' + LastName AS FullName
      ,Street ,City
      ,State
      ,Zip
      ,Street + ' ' + 
      City  + ' ' +
      State  + ' ' +
      Zip AS FullAddress
FROM Cottages.dbo.tblGuest
WHERE LastName LIKE @LastName + '%'

--What happens in the case of empty string?
DECLARE @LastName VARCHAR(50)
SET @LastName = ''
SELECT GuestID, LastName, FirstName,
      FirstName + ' ' + LastName AS FullName
      ,Street ,City
      ,State
      ,Zip
      ,Street + ' ' + 
      City  + ' ' +
      State  + ' ' +
      Zip AS FullAddress
FROM Cottages.dbo.tblGuest
WHERE LastName LIKE @LastName + '%'

--let's make @LastName an optional parameter
--Two strategies:
--Dynamic SQL, that is concatenation of the entire string
--sp_execSQL(@SQLString)
--If Then structure

--Using the IF THEN
DECLARE @LastName VARCHAR(50)
SET @LastName = 'P'
IF @Lastname IS NOT NULL
  SELECT GuestID, LastName, FirstName,
        FirstName + ' ' + LastName AS FullName
        ,Street ,City
        ,State
        ,Zip
        ,Street + ' ' + 
         City  + ' ' +
         State  + ' ' +
         Zip AS FullAddress
  FROM Cottages.dbo.tblGuest
  WHERE LastName LIKE @LastName + '%'
ELSE
  SELECT GuestID, LastName, FirstName,
        FirstName + ' ' + LastName AS FullName
        ,Street ,City
        ,State
        ,Zip
        ,Street + ' ' + 
         City  + ' ' +
         State  + ' ' +
         Zip AS FullAddress
  FROM Cottages.dbo.tblGuest


--Using the ISNULL
DECLARE @LastName VARCHAR(50)
SET @LastName = NULL
  SELECT GuestID, LastName, FirstName,
        FirstName + ' ' + LastName AS FullName
        ,Street ,City
        ,State
        ,Zip
        ,Street + ' ' + 
         City  + ' ' +
         State  + ' ' +
         Zip AS FullAddress
  FROM Cottages.dbo.tblGuest
  WHERE LastName LIKE ISNULL(@LastName,'') + '%'

--Create sproc
--Using the IF THEN
CREATE PROC GetGuestListForSearchResult
@LastName VARCHAR(50) = NULL --Defines this as an optional parameter
/*
Created by hp on 10/07/2008
LastChange: created
Purpose: This sproc returns a guest list
It contains two concatenated aggregate attributes: FullName and Full Address
@LastName is an optional parameter. If not specified, the entire
result is returned.
*/
AS
BEGIN
IF @Lastname IS NOT NULL
  SELECT GuestID, LastName, FirstName,
        FirstName + ' ' + LastName AS FullName
        ,Street ,City
        ,State
        ,Zip
        ,Street + ' ' + 
         City  + ' ' +
         State  + ' ' +
         Zip AS FullAddress
  FROM Cottages.dbo.tblGuest
  WHERE LastName LIKE @LastName + '%'
ELSE
  SELECT GuestID, LastName, FirstName,
        FirstName + ' ' + LastName AS FullName
        ,Street ,City
        ,State
        ,Zip
        ,Street + ' ' + 
         City  + ' ' +
         State  + ' ' +
         Zip AS FullAddress
  FROM Cottages.dbo.tblGuest
END

GRANT EXECUTE ON GetGuestListForSearchResult TO ISC361

DECLARE @LastName VARCHAR(50)
SET @LastName = NULL

EXEC GetGuestListForSearchResult @LastName

/*
We will want to create an overloaded method for this 
sproc, one with the @Lastname parameter, one without.
*/

ALTER PROC GetRoomList
AS
/*
Created by hp on 10/09/2008
LastChange: created
Purpose: This sproc returns a room list

*/
BEGIN
SELECT [RoomID]
      ,[Room]
      ,[BedCode]
      ,[Jacuzzi]
      ,[PrivateAccess]
      ,[FirePlace]
  FROM [Cottages].[dbo].[tblRoom]
--EXEC GetRoomList
END

GRANT EXECUTE ON GetRoomList TO ISC361

EXEC GetRoomList

/*
We decided not to write this one but rather create
an sproc similar to the sproc for tblGuest.
*/
CREATE PROC GetReservationListAll
AS
BEGIN
SELECT [ReservationID]
      ,[GuestID]
      ,[RoomID]
      ,[Arrival]
      ,[Departure]
      ,[CreditCardNumber]
      ,[Card]
  FROM [Cottages].[dbo].[tblReservation]
END

GRANT EXECUTE ON GetReservationListAll TO ISC361

/*
Which table should we use for GuestID and RoomID?
Does it matter?
It can make a difference in the WHERE clause. You should
always try to filter on the small table.
*/

SELECT ReservationID
      ,tblReservation.GuestID
      ,LastName
      ,tblReservation.RoomID
      ,Room
      ,Arrival
      ,Departure
      ,CreditCardNumber
      ,Card--, tblGuest.*, tblRoom.*
      --Leave this there in case I want to add attributes later
FROM tblGuest, tblRoom, tblReservation
WHERE tblGuest.GuestID=tblReservation.GuestID AND
      tblRoom.RoomID=tblReservation.RoomID


DECLARE @GuestID INTEGER
SET @GuestID = NULL
IF @GuestID IS NOT NULL
  SELECT ReservationID
        ,tblReservation.GuestID
        ,LastName
        ,tblReservation.RoomID
        ,Room
        ,Arrival
        ,Departure
        ,CreditCardNumber
        ,Card--, tblGuest.*, tblRoom.*
        --Leave this there in case I want to add attributes later
  FROM tblGuest, tblRoom, tblReservation
  WHERE tblGuest.GuestID=tblReservation.GuestID AND
        tblRoom.RoomID=tblReservation.RoomID AND
        tblGuest.GuestID=@GuestID
ELSE
  SELECT ReservationID
        ,tblReservation.GuestID
        ,LastName
        ,tblReservation.RoomID
        ,Room
        ,Arrival
        ,Departure
        ,CreditCardNumber
        ,Card--, tblGuest.*, tblRoom.*
        --Leave this there in case I want to add attributes later
  FROM tblGuest, tblRoom, tblReservation
  WHERE tblGuest.GuestID=tblReservation.GuestID AND
        tblRoom.RoomID=tblReservation.RoomID

CREATE PROC GetReservationList
@GuestID AS INTEGER = NULL --Optional parameter
AS
BEGIN
IF @GuestID IS NOT NULL
  SELECT ReservationID
        ,tblReservation.GuestID
        ,LastName
        ,tblReservation.RoomID
        ,Room
        ,Arrival
        ,Departure
        ,CreditCardNumber
        ,Card--, tblGuest.*, tblRoom.*
        --Leave this there in case I want to add attributes later
  FROM tblGuest, tblRoom, tblReservation
  WHERE tblGuest.GuestID=tblReservation.GuestID AND
        tblRoom.RoomID=tblReservation.RoomID AND
        tblGuest.GuestID=@GuestID
ELSE
  SELECT ReservationID
        ,tblReservation.GuestID
        ,LastName
        ,tblReservation.RoomID
        ,Room
        ,Arrival
        ,Departure
        ,CreditCardNumber
        ,Card--, tblGuest.*, tblRoom.*
        --Leave this there in case I want to add attributes later
  FROM tblGuest, tblRoom, tblReservation
  WHERE tblGuest.GuestID=tblReservation.GuestID AND
        tblRoom.RoomID=tblReservation.RoomID
END

GRANT EXECUTE ON GetReservationList TO ISC361

DECLARE @GuestID INTEGER
SET @GuestID = 8

EXEC GetReservationList @GuestID
/*
Create using the scripting wizard
Then parameterize VALUES statement
*/
INSERT INTO [Cottages].[dbo].[tblReservation]
           ([GuestID]
           ,[RoomID]
           ,[Arrival]
           ,[Departure]
           ,[CreditCardNumber]
           ,[Card])
     VALUES
           (<GuestID, int,>
           ,<RoomID, int,>
           ,<Arrival, datetime,>
           ,<Departure, datetime,>
           ,<CreditCardNumber, varchar(20),>
           ,<Card, varchar(20),>)

--Parameterize
INSERT INTO [Cottages].[dbo].[tblReservation]
           ([GuestID]
           ,[RoomID]
           ,[Arrival]
           ,[Departure]
           ,[CreditCardNumber]
           ,[Card])
     VALUES
           (<@GuestID, int,>
           ,<@RoomID, int,>
           ,<@Arrival, datetime,>
           ,<@Departure, datetime,>
           ,<@CreditCardNumber, varchar(20),>
           ,<@Card, varchar(20),>)

--Create input parameters
CREATE PROC InsertReservation
 @GuestID int,
 @RoomID int,
 @Arrival datetime,
 @Departure datetime,
 @CreditCardNumber varchar(20),
 @Card varchar(20)
AS
BEGIN
INSERT INTO [Cottages].[dbo].[tblReservation]
           ([GuestID]
           ,[RoomID]
           ,[Arrival]
           ,[Departure]
           ,[CreditCardNumber]
           ,[Card])
     VALUES
           (@GuestID, @RoomID,@Arrival, @Departure ,@CreditCardNumber ,@Card)
END

GRANT EXECUTE ON InsertReservation TO ISC361

EXEC InsertReservation 8, 3, GETDATE(), DATEADD(DAY,1, GETDATE()), '2548789653', 'Master Card'

EXEC InsertReservation 8, 3, '2008-10-09 00:00:00.000', '2008-10-10 00:00:00.000', '2548789653', 'Master Card'

SELECT * FROM tblReservation

SELECT DATEADD(DAY,1, GETDATE())
SELECT GETDATE()

/*
With which attribute are we updating? That is, are we updating 
one row or many?
*/
CREATE PROC UpdateReservationInfo
 @ReservationID int,
 @GuestID int,
 @RoomID int,
 @Arrival datetime,
 @Departure datetime,
 @CreditCardNumber varchar(20),
 @Card varchar(20)
AS
BEGIN
UPDATE [Cottages].[dbo].[tblReservation]
   SET [GuestID] = @GuestID
      ,[RoomID] = @RoomID
      ,[Arrival] = @Arrival
      ,[Departure] = @Departure
      ,[CreditCardNumber] = @CreditCardNumber
      ,[Card] = @Card
 WHERE ReservationID=@ReservationID
END

GRANT EXECUTE ON UpdateReservationInfo TO ISC361

/*
How would you modify this to allow optional parameters?
That is, if the user does not supply all the attribute values,
replace the attribute value with the original value.

*/

ALTER PROC UpdateReservationInfo
 @ReservationID int,
 @GuestID int = NULL, --optional parameter
 @RoomID int = NULL, --optional parameter
 @Arrival datetime = NULL, --optional parameter
 @Departure datetime = NULL, --optional parameter
 @CreditCardNumber varchar(20) = NULL, --optional parameter
 @Card varchar(20) = NULL --optional parameter
AS
BEGIN
UPDATE [Cottages].[dbo].[tblReservation]
   SET [GuestID] = ISNULL(@GuestID, GuestID)
      ,[RoomID] = ISNULL(@RoomID, RoomID)
      ,[Arrival] = ISNULL(@Arrival, Arrival)
      ,[Departure] = ISNULL(@Departure, Departure)
      ,[CreditCardNumber] = ISNULL(@CreditCardNumber, CreditCardNumber)
      ,[Card] = ISNULL(@Card, Card)
 WHERE ReservationID=@ReservationID
END

EXEC UpdateReservationInfo 8, NULL, NULL, NULL, NULL, NULL, NULL
EXEC UpdateReservationInfo 8, NULL, NULL, NULL, NULL, NULL, 'Visa'

SELECT * FROM tblReservation WHERE ReservationID = 8

/*
What is your position on DELETE?
I recommend we do lazy delete. That is we mark for deletion.
Do we have a status attribute? And Do we want a Status table?

*/

CREATE TABLE tblStatus
(
StatusID INT IDENTITY(1,1) PRIMARY KEY,
StatusName VARCHAR(50) NOT NULL,
StatusDescription VARCHAR(250) NOT NULL
)

SELECT * FROM tblStatus

INSERT INTO tblStatus(StatusName, StatusDescription) VALUES('Active','Row is valid and current')
INSERT INTO tblStatus(StatusName, StatusDescription) VALUES('Deleted','Row has been physically deleted rows')
INSERT INTO tblStatus(StatusName, StatusDescription) VALUES('Pending','Requires review and approval')

SELECT * FROM tblReservation

--Add a new column for the foreign key
ALTER TABLE tblReservation
ADD StatusID INT REFERENCES tblStatus(StatusID)

--Update statusID
UPDATE tblReservation 
SET StatusID = (SELECT StatusID 
                FROM tblStatus 
                WHERE StatusName = 'active')

--We have to change our sprocs to filter by status
ALTER PROC GetReservationListAll
AS
BEGIN
SELECT [ReservationID]
      ,[GuestID]
      ,[RoomID]
      ,[Arrival]
      ,[Departure]
      ,[CreditCardNumber]
      ,[Card]
  FROM [Cottages].[dbo].[tblReservation]
  WHERE StatusID =(SELECT StatusID 
                   FROM tblStatus 
                   WHERE StatusName = 'active')
END

EXEC GetReservationListAll

ALTER PROC GetReservationList
@GuestID AS INTEGER = NULL --Optional parameter
AS
BEGIN
IF @GuestID IS NOT NULL
  SELECT ReservationID
        ,tblReservation.GuestID
        ,LastName
        ,tblReservation.RoomID
        ,Room
        ,Arrival
        ,Departure
        ,CreditCardNumber
        ,Card--, tblGuest.*, tblRoom.*
        --Leave this there in case I want to add attributes later
  FROM tblGuest, tblRoom, tblReservation
  WHERE tblGuest.GuestID=tblReservation.GuestID AND
        tblRoom.RoomID=tblReservation.RoomID AND
        tblGuest.GuestID=@GuestID AND
        StatusID =(SELECT StatusID 
                   FROM tblStatus 
                   WHERE StatusName = 'active')
ELSE
  SELECT ReservationID
        ,tblReservation.GuestID
        ,LastName
        ,tblReservation.RoomID
        ,Room
        ,Arrival
        ,Departure
        ,CreditCardNumber
        ,Card--, tblGuest.*, tblRoom.*
        --Leave this there in case I want to add attributes later
  FROM tblGuest, tblRoom, tblReservation
  WHERE tblGuest.GuestID=tblReservation.GuestID AND
        tblRoom.RoomID=tblReservation.RoomID AND
        StatusID =(SELECT StatusID 
                   FROM tblStatus 
                   WHERE StatusName = 'active')
END

/*
We changed our selects, but what about the insert?
We didn't set a default value for the table, so do we
insert the value in the insert or add a default constraint?
The advantage of a default constraint is that it will always
be enforced independent of the sproc or programming logic. A
disadvantage is that it is less flexible.
Another disadvantage is we can't use our uncorrelated subquery
to insert the value. we will have to hard code it.
*/

--This won't work
ALTER TABLE tblReservation
ADD CONSTRAINT constr_StatusID_Active 
DEFAULT (SELECT StatusID 
         FROM tblStatus 
         WHERE StatusName = 'active') FOR StatusID

--We have to hard code a literal
ALTER TABLE tblReservation
ADD CONSTRAINT constr_StatusID_Active 
DEFAULT 1 FOR StatusID

--This means we don't have to modify our insert sproc

/*
What about our update sprocs?
What does delete mean? It means it isn't availiable
So, developers shouldn't be able to update a row that isn't 
available.
But... what about undelete?
We can create an undelete sproc...
*/
ALTER PROC UpdateReservationInfo
 @ReservationID int,
 @GuestID int = NULL, --optional parameter
 @RoomID int = NULL, --optional parameter
 @Arrival datetime = NULL, --optional parameter
 @Departure datetime = NULL, --optional parameter
 @CreditCardNumber varchar(20) = NULL, --optional parameter
 @Card varchar(20) = NULL --optional parameter
AS
BEGIN
UPDATE [Cottages].[dbo].[tblReservation]
   SET [GuestID] = ISNULL(@GuestID, GuestID)
      ,[RoomID] = ISNULL(@RoomID, RoomID)
      ,[Arrival] = ISNULL(@Arrival, Arrival)
      ,[Departure] = ISNULL(@Departure, Departure)
      ,[CreditCardNumber] = ISNULL(@CreditCardNumber, CreditCardNumber)
      ,[Card] = ISNULL(@Card, Card)
 WHERE ReservationID=@ReservationID AND
       StatusID = (SELECT StatusID 
                   FROM tblStatus 
                   WHERE StatusName = 'active')
END


EXEC UpdateReservationInfo 8, NULL, NULL, NULL, NULL, NULL, 'Master Card'
EXEC UpdateReservationInfo 7, NULL, NULL, NULL, NULL, NULL, 'Master Card'

SELECT * FROM tblReservation

UPDATE tblReservation SET StatusID = 2 WHERE ReservationID = 8

--Let's write our delete and undelete sprocs
--We need the pk only

--delete
UPDATE tblReservation 
SET StatusID = (SELECT StatusID 
                FROM tblStatus 
                WHERE StatusName = 'deleted') 
WHERE ReservationID = 7

--undelete
UPDATE tblReservation 
SET StatusID = (SELECT StatusID 
                FROM tblStatus 
                WHERE StatusName = 'active') 
WHERE ReservationID = 7

CREATE PROC DeleteReservation
@ReservationID INT
AS 
BEGIN
  UPDATE tblReservation 
  SET StatusID = (SELECT StatusID 
                  FROM tblStatus 
                  WHERE StatusName = 'deleted') 
  WHERE ReservationID = @ReservationID
END

GRANT EXECUTE ON DeleteReservation TO ISC361

--undelete
CREATE PROC UnDeleteReservation
@ReservationID INT
AS 
BEGIN
  UPDATE tblReservation 
  SET StatusID = (SELECT StatusID 
                  FROM tblStatus 
                  WHERE StatusName = 'active') 
  WHERE ReservationID = @ReservationID
END

GRANT EXECUTE ON UnDeleteReservation TO ISC361

UnDeleteReservation 8
 
SELECT * FROM tblReservation


--DROP TABLE tblDemoTrigger
CREATE TABLE tblDemoTrigger
(
SurrogateKey INT IDENTITY PRIMARY KEY,
Attribute1 INT,
Attribute2 NVARCHAR(500),
Attribute3 DATETIME,
)

--Single row CRUD operation
INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3)
   VALUES (10, 'string1', '2006-10-31')

SELECT * FROM tblDemoTrigger

--Create an INSERT trigger
--DROP TRIGGER trigInsertDemo
CREATE TRIGGER trigInsertDemo ON tblDemoTrigger
FOR INSERT
AS
BEGIN
  /*
   Echo the contents of the "inserted" table
   The "inserted" table contains the values of the newly inserted row
   in tblDemoTrigger or the "after-image".
  */
  SELECT 'inserted' AS Buffer, inserted.* FROM inserted
  --Action...

END

INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3)
   VALUES (20, 'string2', '2006-10-31')

SELECT * FROM tblDemoTrigger

UPDATE tblDemoTrigger SET Attribute1 = 200 WHERE SurrogateKey = 1

--Create an UPDATE trigger
--DROP TRIGGER trigUpdateDemo
CREATE TRIGGER trigUpdateDemo ON tblDemoTrigger
FOR UPDATE
AS
BEGIN
  /*
   Echo the contents of the "inserted" and "deleted" tables
   The "inserted" table contains the after-image of the updated row.
   The "deleted" table contains the before-image of the updated row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  SELECT 'inserted' AS Buffer, inserted.*, 'after' AS RowImage FROM inserted
  --Action...
END

UPDATE tblDemoTrigger SET Attribute1 = 30 WHERE SurrogateKey = 2

SELECT * FROM tblDemoTrigger

--Create a DELETE trigger
--DROP TRIGGER trigDeleteDemo
CREATE TRIGGER trigDeleteDemo ON tblDemoTrigger
FOR DELETE
AS
BEGIN
  /*
   Echo the contents of the "deleted" table
   The "deleted" table contains the values of the deleted row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  --Action...
END

DELETE tblDemoTrigger WHERE SurrogateKey = 2

SELECT * FROM tblDemoTrigger

--Multi-row CRUD operation
--Insert values from Orders in the Northwind database as an example
INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3)
   SELECT EmployeeID, CustomerID, OrderDate 
   FROM Orders 
   WHERE OrderID < 10256

SELECT * FROM tblDemoTrigger

--Update ALL rows that contain the word "software" in the keywords
--Set the Attribute1 to 888

SELECT * FROM tblDemoTrigger WHERE Attribute2 LIKE '%SU%'

UPDATE tblDemoTrigger SET Attribute1 = 888 WHERE Attribute2 LIKE '%SU%'

/*
Using a trigger to implement a Row-Level Audit table.
See http://www.ddj.com/showArticle.jhtml;jsessionid=33ZCMV5XELMXYQSNDLOSKH0CJUNN2JVN?articleID=184406340

Perhaps the biggest issue for database is that the need for "data quality, 
data integrity, and accuracy of IT systems" has been legislated.



*/

SELECT * FROM tblDemoTrigger

--Create row-level audit table for tblDemoTrigger
--DROP TABLE RowLevelAuditTblDemoTrigger
CREATE TABLE RowLevelAuditTblDemoTrigger
(
AuditLogID INT IDENTITY(1,1) PRIMARY KEY,
SurrogateKey INT,
Attribute1 INT,
Attribute2 NVARCHAR(500),
Attribute3 DATETIME,
ChangedBy INT,                         --PK of person making change
ChangeTime DATETIME DEFAULT GETDATE(), --Time change occurred
ChangeType NVARCHAR(10),               --INSERT, UPDATE, DELETE
ImageType NVARCHAR(10)                 --BEFORE, AFTER     
)
SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger

/*
Where will the ChangedBy attribute come from?
Todd Schraml in the ddj article suggests we include LastChangedBy
in the base table tblDemoTrigger. Every time a row is udated,
the LastChangedBy is updated.
*/

SELECT * FROM tblDemoTrigger

ALTER TABLE tblDemoTrigger
ADD LastChangedBy INT

--There are no "users" in our demo table so I'll just make up a PK value
UPDATE tblDemoTrigger SET LastChangedBy = 23

INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3, LastChangedBy)
   VALUES (50, 'string5', '2006-10-31', 45)

SELECT * FROM tblDemoTrigger

--Alter the existing TRIGGER to include an INSERT statement into the row-level audit table
--DROP TRIGGER trigInsertDemo
ALTER TRIGGER trigInsertDemo ON tblDemoTrigger
FOR INSERT
AS
BEGIN
  /*
   Echo the contents of the "inserted" table
   The "inserted" table contains the values of the newly inserted row
   in tblDemoTrigger or the "after-image".
  */
  SELECT 'inserted' AS Buffer, inserted.* FROM inserted
  --Action...
  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'INSERT' AS ChangeType, 'AFTER' AS ImageType
    FROM inserted

END

INSERT INTO tblDemoTrigger (Attribute1, Attribute2, Attribute3, LastChangedBy)
   VALUES (60, 'string6', '2006-10-31', 45)

SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger


--Alter the existing TRIGGER to include an INSERT statement into the row-level audit table
--DROP TRIGGER trigUpdateDemo
ALTER TRIGGER trigUpdateDemo ON tblDemoTrigger
FOR UPDATE
AS
BEGIN
  /*
   Echo the contents of the "inserted" and "deleted" tables
   The "inserted" table contains the after-image of the updated row.
   The "deleted" table contains the before-image of the updated row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  SELECT 'inserted' AS Buffer, inserted.*, 'after' AS RowImage FROM inserted
  --Action...
  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'BEFORE' AS ImageType
    FROM deleted

  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'AFTER' AS ImageType
    FROM inserted

END

UPDATE tblDemoTrigger 
SET Attribute1 = 30,
    LastChangedBy = 23 
WHERE SurrogateKey = 11

SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger

--Alter the existing TRIGGER to include an INSERT statement into the row-level audit table
--DROP TRIGGER trigDeleteDemo
ALTER TRIGGER trigDeleteDemo ON tblDemoTrigger
FOR DELETE
AS
BEGIN
  /*
   Echo the contents of the "deleted" table
   The "deleted" table contains the values of the deleted row.
  */
  SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  --Action...

  INSERT INTO RowLevelAuditTblDemoTrigger (SurrogateKey, Attribute1, Attribute2, Attribute3, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT SurrogateKey, Attribute1, Attribute2, Attribute3, LastChangedBy, GETDATE() AS ChangeTime, 'DELETE' AS ChangeType, 'BEFORE' AS ImageType
    FROM deleted

END

DELETE tblDemoTrigger WHERE SurrogateKey = 11

SELECT * FROM tblDemoTrigger
SELECT * FROM RowLevelAuditTblDemoTrigger WHERE SurrogateKey = 11
--Essentially, this provides a record of the entire life cycle or life span of a record.

SELECT * FROM tblGuest
SELECT * FROM tblReservation

SELECT * FROM tblProfile ORDER BY ProfileID DESC
DELETE tblProfile WHERE ProfileID = 19

INSERT INTO tblProfile (RoleID, GuestID) VALUES (2, 7)

ALTER TABLE tblProfile
ADD CONSTRAINT contr_RoleID_GuestID_Must_Be_Unique UNIQUE(RoleID, GuestID)

ALTER TABLE tblReservation
ADD CONSTRAINT contr_Unique_Transactions UNIQUE(GuestID, RoomID, Arrival)


select * from tblprofile
where guestid=7
exec getuserprofileinfo 'jpeter@gte.com'
select * from tblguest

SELECT * FROM tblGuest

SELECT * FROM tblReservation

sp_help tblReservation

--Create row-level audit table for tblReservation
--DROP TABLE logReservation
/*
For now, we will allow ChangedBy to be NULL in order to simply
the demonstration. But we should require a ChangedBy value.
*/
CREATE TABLE logReservation
(
AuditLogID INT IDENTITY(1,1) PRIMARY KEY,
ReservationID INT,
GuestID INT,
RoomID INT,
Arrival DATETIME,
Departure DATETIME,
CreditCardNumber VARCHAR(20),
Card VARCHAR(20),
StatusID INT,
ChangedBy INT,                         --PK of person making change
ChangeTime DATETIME DEFAULT GETDATE(), --Time change occurred
ChangeType NVARCHAR(10),               --INSERT, UPDATE, DELETE
ImageType NVARCHAR(10)                 --BEFORE, AFTER     
)

SELECT * FROM tblReservation
SELECT * FROM logReservation

--Add the attribute for ChangedBy as LastChangedBy
ALTER TABLE tblReservation
ADD LastChangedBy INT

--DROP TRIGGER trigInsertReservation
CREATE TRIGGER trigInsertReservation ON tblReservation
FOR INSERT
AS
BEGIN
  /*
   Echo the contents of the "inserted" table
   The "inserted" table contains the values of the newly inserted row
   in tblDemoTrigger or the "after-image".
  */
  --SELECT 'inserted' AS Buffer, inserted.* FROM inserted
  --Action...
  INSERT INTO logReservation (ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, LastChangedBy, GETDATE() AS ChangeTime, 'INSERT' AS ChangeType, 'AFTER' AS ImageType
    FROM inserted

END

--Test case
INSERT INTO tblReservation (GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, LastChangedBy)
   VALUES (8, 4, GETDATE(), DATEADD(DAY,1,GETDATE()), '2548789653', 'Master Card', 1, 6)

SELECT * FROM tblReservation
SELECT * FROM logReservation


--DROP TRIGGER trigUpdateReservation
ALTER TRIGGER trigUpdateReservation ON tblReservation
FOR UPDATE
AS
BEGIN
  /*
   Echo the contents of the "inserted" and "deleted" tables
   The "inserted" table contains the after-image of the updated row.
   The "deleted" table contains the before-image of the updated row.
  */
  --SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  --SELECT 'inserted' AS Buffer, inserted.*, 'after' AS RowImage FROM inserted
  --Action...
  INSERT INTO logReservation (ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'BEFORE' AS ImageType
    FROM deleted

  INSERT INTO logReservation (ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'AFTER' AS ImageType
    FROM inserted
END

--Test case
--Extend the stay by one day
UPDATE tblReservation
SET Departure = DATEADD(DAY,2,GETDATE()),
    LastChangedBy = 6 
WHERE ReservationID = 10

SELECT * FROM tblReservation
SELECT * FROM logReservation

--Alter the existing TRIGGER to include an INSERT statement into the row-level audit table
--DROP TRIGGER trigDeleteReservation
CREATE TRIGGER trigDeleteReservation ON tblReservation
FOR DELETE
AS
BEGIN
  /*
   Echo the contents of the "deleted" table
   The "deleted" table contains the values of the deleted row.
  */
  --SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
  --Action...

  INSERT INTO logReservation (ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, ChangedBy, ChangeTime, ChangeType, ImageType)
    SELECT ReservationID, GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, LastChangedBy, GETDATE() AS ChangeTime, 'DELETE' AS ChangeType, 'BEFORE' AS ImageType
    FROM deleted
END

select * from tblGuest

--Test case
--Delete transaction 
DELETE tblReservation WHERE ReservationID = 10
SELECT * FROM tblReservation

SELECT * 
FROM logReservation 
WHERE ReservationID = 11
ORDER BY ChangeTime


/*
If this deletion had been in error, we could put the record back
by inserting from the log table
*/

INSERT INTO tblReservation (GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, LastChangedBy)
 SELECT GuestID, RoomID, Arrival, Departure, CreditCardNumber, Card, StatusID, 4 AS LastChangedBy 
 FROM logReservation
 WHERE AuditLogID = 5