EXEC sp_addlogin 'isc559', '559', 'Northwind'
use Northwind

--EXEC sp_dropuser 'login'
--EXEC sp_changedbowner 'login'
EXEC sp_addUser 'isc559'
--EXEC sp_dropuser 'login'
--EXEC sp_droplogin 'login'

REVOKE SELECT ON Employees TO isc559

SELECT * FROM Customers
SELECT * FROM Employees

ALTER TABLE	Customers 
ADD Email NVARCHAR(50) NULL

ALTER TABLE	Customers 
ADD Password NVARCHAR(50) NULL

ALTER TABLE	Employees
ADD Email NVARCHAR(50) NULL

ALTER TABLE	Employees 
ADD Password NVARCHAR(50) NULL

SELECT CustomerID + '@Northwind.com', *
FROM Customers


UPDATE Customers SET Email = CustomerID + '@Northwind.com'
UPDATE Customers SET Password = CustomerID

SELECT SUBSTRING(FirstName, 1,1) + LastName + '@Northwind.com', *
FROM Employees

UPDATE Employees SET Email = SUBSTRING(FirstName, 1,1) + LastName + '@Northwind.com'
UPDATE Employees SET Password = EmployeeID


--How to put these together?
SELECT * FROM Customers
SELECT * FROM Employees

SELECT Email, Password FROM Customers
UNION
SELECT Email, Password FROM Employees

--Create a view
CREATE VIEW vwCustomersEmployeesUNION
AS
  SELECT Email, Password FROM Customers
   UNION
  SELECT Email, Password FROM Employees

SELECT * FROM vwCustomersEmployeesUNION

DROP PROC ValidateUser
CREATE PROC ValidateUser
 @Login NVARCHAR(50),
 @Password NVARCHAR(50),
 @IsFound BIT OUTPUT
AS
BEGIN
IF EXISTS(SELECT * 
          FROM vwCustomersEmployeesUNION AS tblUser
          WHERE Email = @Login AND
                Password = @Password)
   SET @IsFound = 1
ELSE
   SET @IsFound = 0
END;
go;

DECLARE  @Login NVARCHAR(50), @Password NVARCHAR(50), @IsFound BIT
SET  @Login = 'ADodsworth@Northwind.co'
SET  @Password = '9'

EXEC ValidateUser @Login, @Password, @IsFound OUTPUT

SELECT @IsFound AS IsFound

GRANT EXECUTE ON ValidateUser TO ISC559


/*
We decided to use a supertype subtype for customers and employees
so we are creating a supertype called tblUser
*/

CREATE TABLE tblUser
(
UserID INT IDENTITY(1,1) PRIMARY KEY,
Login NVARCHAR(50) NOT NULL,
Password NVARCHAR(50) NOT NULL,
UserType CHAR(1) NOT NULL
)

--SELECT UserID, LOGIN, password, ISNULL(Customer.Login, 'E', 'C') AS UserType
--FROM tblUser, Customers
--WHERE tblUser.UserID=Customers.UserID

SELECT *
FROM tblUser

--Block insert. Add subtype data to supertype and create a discriminator attribute
INSERT INTO tblUser (Login, Password, UserType) 
  SELECT Email, Password, 'C' AS UserType FROM Customers
   UNION
  SELECT Email, Password, 'E' AS UserType FROM Employees

--Add a FK to each subtype

ALTER TABLE Customers
ADD UserID INT NULL

ALTER TABLE Employees
ADD UserID INT NULL

SELECT *
FROM tblUser, Employees
WHERE tblUser.Login=Employees.Email

--Link the subtype to the supertype.

UPDATE Employees SET Employees.UserID=tbluser.UserID
FROM tblUser, Employees
WHERE tblUser.Login=Employees.Email

SELECT *
FROM Employees

SELECT *
FROM tblUser, Customers
WHERE tblUser.Login=Customers.Email

UPDATE customers SET Customers.UserID=tbluser.UserID
FROM tblUser, Customers
WHERE tblUser.Login=Customers.Email

SELECT *
FROM Customers

/*
Remove attributes from subtype tables that have been moved to the supertype.
*/

ALTER TABLE Customers
DROP COLUMN UserType

ALTER TABLE Customers
DROP COLUMN Email

ALTER TABLE Customers
DROP COLUMN Password

ALTER TABLE Employees
DROP COLUMN UserType

ALTER TABLE Employees
DROP COLUMN Email

ALTER TABLE Employees
DROP COLUMN Password

SELECT * FROM tblUser

/*
We were trying to deterime the supertype's participation in the 
subtype by writing a query rather than hard coding a discriminator attribute.
*/

SELECT tbluser.UserID, LOGIN, tblUser.Password, ISNULL(Customers.UserID, 'E') AS UserType
FROM tblUser, 
     (SELECT *, 'C' AS UserType
      FROM Customers) Customers, Employees
WHERE tblUser.UserID=Customers.UserID AND
      tblUser.UserID=Employees.UserID


select tblUser.UserID, LOGIN, tblUser.Password
from tblUser right outer join customers on tbluser.userid = customers.userid
where customers.UserId is null


SELECT tbluser.UserID, LOGIN, tblUser.Password, UserType
FROM tblUser

/*
Alter the Customers table to create the 1:1 relationship for the 
supertype subtype relationship.
*/

ALTER TABLE Customers
ADD CONSTRAINT UserID_Unique UNIQUE(UserID) -- makes this an alternate key

ALTER TABLE Customers
ADD CONSTRAINT UserID_fk FOREIGN KEY (UserID)
REFERENCES tblUser (UserID)

/*
The previous primary key now is an identifier and needs to be constrained
to be unique
*/
ALTER TABLE Customers
ADD CONSTRAINT CustomerID_Unique UNIQUE (CustomerID)


/*
Alter the Employees table to create the 1:1 relationship for the 
supertype subtype relationship.
*/

ALTER TABLE Employees
ADD CONSTRAINT UserID_emp_Unique UNIQUE(UserID) -- makes this an alternate key

ALTER TABLE Employees
ADD CONSTRAINT UserID_emp_fk FOREIGN KEY (UserID)
REFERENCES tblUser (UserID)

/*
The previous primary key now is an identifier and needs to be constrained
to be unique
*/

ALTER TABLE Employees
ADD CONSTRAINT EmployeeID_Unique UNIQUE (EmployeeID)

--Modify our sproc to reflect the new table tblUser and test...

--DROP PROC ValidateUser
ALTER PROC ValidateUser
 @Login NVARCHAR(50),
 @Password NVARCHAR(50),
 @IsFound BIT OUTPUT
AS
BEGIN
IF EXISTS(SELECT * 
          FROM tblUser --vwCustomersEmployeesUNION AS tblUser
          WHERE Login = @Login AND --Email = @Login AND
                Password = @Password)
   SET @IsFound = 1
ELSE
   SET @IsFound = 0
END;

--Highlight BEGIN to END and run as a block
BEGIN
 DECLARE  @Login NVARCHAR(50), @Password NVARCHAR(50), @IsFound BIT
 SET  @Login = 'ADodsworth@Northwind.com'
 SET  @Password = '9'

 EXEC ValidateUser @Login, @Password, @IsFound OUTPUT

 SELECT @IsFound AS IsFound
END

/*
Create the tables and entries for tblRole and tblProfile.
First create the 1 table, then the Many table
*/

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

SELECT * FROM tblRole

INSERT INTO tblRole (RoleName, RoleDescription) 
   VALUES('GUser', 'General User')
INSERT INTO tblRole (RoleName, RoleDescription) 
   VALUES('Admin', 'Performs system administrative functions')
INSERT INTO tblRole (RoleName, RoleDescription) 
   VALUES('DbProg', 'Database programmer')
INSERT INTO tblRole (RoleName, RoleDescription) 
   VALUES('AppProg', 'Application programmer')
INSERT INTO tblRole (RoleName, RoleDescription) 
   VALUES('GDesign', 'Graphic Designer')  
  
SELECT * FROM tblRole

--DROP TABLE tblProfile

CREATE TABLE tblProfile
(
ProfileID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
RoleID INT REFERENCES tblRole (RoleID) NOT NULL,
UserID INT REFERENCES tblUser (UserID) NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT GETDATE()
)

SELECT * FROM tblProfile
SELECT * FROM tblUser

--Put everyone in as a user
--Highlight BEGIN to END and run as a block
BEGIN
 DECLARE @RoleID INT, @UserID INT
 SET @RoleID = (SELECT RoleID FROM tblRole WHERE RoleName = 'GUser')

 INSERT INTO tblProfile (RoleID, UserID) 
    SELECT @RoleID AS RoleID, UserID
    FROM tblUser
END

SELECT * FROM tblUser
SELECT * FROM tblRole

--Add an admin
BEGIN
 DECLARE @RoleID INT, @UserID INT
 SET @UserID = (SELECT UserID FROM tblUser WHERE Login = 'ADodsworth@Northwind.com')
 SET @RoleID = (SELECT RoleID FROM tblRole WHERE RoleName = 'Admin')
 INSERT INTO tblProfile (RoleID, UserID) 
   VALUES (@RoleID,@UserID)
END

--Add a couple of application developers
BEGIN
 DECLARE @RoleID INT, @UserID INT
 SET @RoleID = (SELECT RoleID FROM tblRole WHERE RoleName = 'AppProg')
 SET @UserID = (SELECT UserID FROM tblUser WHERE Login = 'AFuller@Northwind.com')
 INSERT INTO tblProfile (RoleID, UserID) 
   VALUES (@RoleID,@UserID)
 SET @UserID = (SELECT UserID FROM tblUser WHERE Login = 'JLeverling@Northwind.com')
 INSERT INTO tblProfile (RoleID, UserID) 
   VALUES (@RoleID,@UserID)
END

--Add three of database programmers
BEGIN
 DECLARE @RoleID INT, @UserID INT
 SET @RoleID = (SELECT RoleID FROM tblRole WHERE RoleName = 'DbProg')
 SET @UserID = (SELECT UserID FROM tblUser WHERE Login = 'AFuller@Northwind.com')
 INSERT INTO tblProfile (RoleID, UserID) 
   VALUES (@RoleID,@UserID)
 SET @UserID = (SELECT UserID FROM tblUser WHERE Login = 'LCallahan@Northwind.com')
 INSERT INTO tblProfile (RoleID, UserID) 
   VALUES (@RoleID,@UserID)
 SET @UserID = (SELECT UserID FROM tblUser WHERE Login = 'NDavolio@Northwind.com')
 INSERT INTO tblProfile (RoleID, UserID) 
   VALUES (@RoleID,@UserID)
END

--Test cases
SELECT * 
FROM tblRole, tblProfile, tblUser
WHERE tblRole.RoleID=tblProfile.RoleID AND
      tblUser.UserID=tblProfile.UserID AND
      tblUser.Login = 'ADodsworth@Northwind.com'
      
SELECT * 
FROM tblRole, tblProfile, tblUser
WHERE tblRole.RoleID=tblProfile.RoleID AND
      tblUser.UserID=tblProfile.UserID AND
      tblUser.Login = 'AFuller@Northwind.com'
      
/*
But what we really want is to create a single row per user that contains
they roles as a collection of booleans. That is, we want to pivot row-wise
data for each user into column-wise data.

AFuller@Northwind.com, profile:

User Admin DbProg AppProg GDesign
 1    0     1      1       0

We will then use this list to enable/disable hide/show controls.

For example:

btnSaveAs.Visible = dtUserProfile.Rows(0)("Admin")

Or 

If (dtUserProfile.Rows(0)("Admin"))
{
  // do something
}
else
{
  //do something else
}

We will use SQL Server's PIVOT operator.
*/

BEGIN
 DECLARE @UserID INT
 SET @UserID = (SELECT Userid FROM tblUser WHERE Login = 'AFuller@Northwind.com')

 SELECT UserID, 
       [1] AS GUser, 
       [2] AS Admin, 
       [3] AS DbProg,
       [4] AS AppProg,
       [5] AS GDesign 
 FROM (SELECT UserID, RoleID 
       FROM tblProfile
       WHERE UserID=@UserID) AS PivotTable
 PIVOT
 (COUNT(RoleID)
  FOR RoleID IN ([1], [2], [3], [4], [5])) AS PVT
 END

--Create a sproc to use this operator
--We can add additional attributes to the profile later as needed.

--DROP PROC GetUserProfile
CREATE PROC GetUserProfile
 @UserID INT
AS
BEGIN
 SELECT UserID, 
       [1] AS GUser, 
       [2] AS Admin, 
       [3] AS DbProg,
       [4] AS AppProg,
       [5] AS GDesign 
 FROM (SELECT UserID, RoleID 
       FROM tblProfile
       WHERE UserID=@UserID) AS PivotTable
 PIVOT
 (COUNT(RoleID)
  FOR RoleID IN ([1], [2], [3], [4], [5])) AS PVT
END

sp_help tbluser

GRANT EXECUTE ON GetUserProfile TO ISC559

/*
Note: we need to explicitly CAST as BIT in our sproc
because C# will not take an implicitly cast 0/1 value
from SQL Server.
*/

ALTER PROC GetUserProfile
 @UserID INT = NULL,
 @Login NVARCHAR(50) = NULL 
AS
BEGIN

IF @Login IS NULL
 BEGIN
   SELECT UserID, 
          CAST([1] AS BIT) AS GUser, 
          CAST([2] AS BIT) AS Admin, 
          CAST([3] AS BIT) AS DbProg,
          CAST([4] AS BIT) AS AppProg,
          CAST([5] AS BIT) AS GDesign 
   FROM (SELECT UserID, RoleID 
         FROM tblProfile
         WHERE UserID=@UserID) AS PivotTable
   PIVOT
        (COUNT(RoleID)
         FOR RoleID IN ([1], [2], [3], [4], [5])) AS PVT
END
ELSE
  BEGIN
   SELECT UserID, 
         CAST([1] AS BIT) AS GUser, 
         CAST([2] AS BIT) AS Admin, 
         CAST([3] AS BIT) AS DbProg,
         CAST([4] AS BIT) AS AppProg,
         CAST([5] AS BIT) AS GDesign 
   FROM (SELECT tblUser.UserID, RoleID 
         FROM tblProfile, tblUser
         WHERE tblProfile.UserID = tblUser.UserID AND 
               tblUser.Login=@Login) AS PivotTable
   PIVOT
        (COUNT(RoleID)
        FOR RoleID IN ([1], [2], [3], [4], [5])) AS PVT
END
END

ALTER PROC GetUserProfile
 @UserID INT = NULL,
 @Login NVARCHAR(50) = NULL 
AS
BEGIN

IF @Login IS NULL
 BEGIN
   SELECT UserID, 
          [1] AS GUser, 
          [2] AS Admin, 
          [3] AS DbProg,
          [4] AS AppProg,
          [5] AS GDesign 
   FROM (SELECT UserID, RoleID 
         FROM tblProfile
         WHERE UserID=@UserID) AS PivotTable
   PIVOT
        (COUNT(RoleID)
         FOR RoleID IN ([1], [2], [3], [4], [5])) AS PVT
END
ELSE
  BEGIN
   SELECT UserID, 
         [1] AS GUser, 
         [2] AS Admin, 
         [3] AS DbProg,
         [4] AS AppProg,
         [5] AS GDesign 
   FROM (SELECT tblUser.UserID, RoleID 
         FROM tblProfile, tblUser
         WHERE tblProfile.UserID = tblUser.UserID AND 
               tblUser.Login=@Login) AS PivotTable
   PIVOT
        (COUNT(RoleID)
        FOR RoleID IN ([1], [2], [3], [4], [5])) AS PVT
END
END


--Test cases
DECLARE @UserID INT, @Login NVARCHAR(50)
SET @UserID = (SELECT Userid FROM tblUser WHERE Login = 'AFuller@Northwind.com')
SET @Login = 'AFuller@Northwind.com'

SET @UserID = NULL
--SET @Login = NULL
EXEC GetUserProfile @UserID

EXEC GetUserProfile @UserID, @Login

EXEC GetUserProfile NULL, 'AFuller@Northwind.com'

DECLARE @SQLstring NVARCHAR(2000)
SET @SQLstring = 'SELECT * FROM tblUser WHERE Login=''' + @UserID + ''';'

EXEC sp_executesql @SQLstring


--I'm changing the login for ADodsworth to something simple for ease of login...

UPDATE tblUser SET Login = '9' WHERE UserID = 1

SELECT * FROM tblUser

/*
Sprocs for Customer Info

GetRoleList for Customer
  This sproc is similar GetUserProfile, but there is no need to 
  pivot the table. That is, in this case, we want row-wise data 
  to bind to a gridview containing checkboxes
*/
--GetCustomerRoleList
SELECT * FROM tblRole
SELECT * FROM tblProfile


--Get the roles for a given customer
/*
SELECT list of roles for customer with binary that indicates HasRole
UNION
SELECT list of roles customer does not have with a binary that indicates does not have role

*/
SELECT tblRole.RoleID, tblRole.RoleName, 1 AS HasRole--, Customers.CustomerID, --, * 
FROM tblUser, Customers, tblProfile, tblRole
WHERE tblUser.UserID=Customers.UserID AND
      tblUser.UserID=tblProfile.UserID AND
      tblProfile.RoleID=tblRole.RoleID AND
      Customers.CustomerID = 'ALFKI'
UNION
SELECT tblRole.RoleID, tblRole.RoleName, 0 AS HasRole
FROM tblRole
WHERE tblRole.RoleID NOT IN (SELECT tblProfile.RoleID
                             FROM tblProfile, Customers
                             WHERE tblProfile.UserID=Customers.UserID AND
                                   Customers.CustomerID = 'ALFKI')

--Paramertize and put behind a sproc

ALTER PROC GetCustomerRoleList
@CustomerID NVARCHAR(10)
AS
BEGIN
 SELECT tblRole.RoleID, tblRole.RoleName, 1 AS HasRole--, Customers.CustomerID, --, * 
 FROM tblUser, Customers, tblProfile, tblRole
 WHERE tblUser.UserID=Customers.UserID AND
       tblUser.UserID=tblProfile.UserID AND
       tblProfile.RoleID=tblRole.RoleID AND
       Customers.CustomerID = @CustomerID
 UNION
 SELECT tblRole.RoleID, tblRole.RoleName, 0 AS HasRole
 FROM tblRole
 WHERE tblRole.RoleID NOT IN (SELECT tblProfile.RoleID
                              FROM tblProfile, Customers
                              WHERE tblProfile.UserID=Customers.UserID AND
                                    Customers.CustomerID = @CustomerID)

END

GRANT EXECUTE ON GetCustomerRoleList TO ISC559

DECLARE @CustomerID AS NvarChar(10)
SET @CustomerID = 'ANTON'--'ALFKI'

EXEC GetCustomerRoleList @CustomerID

SELECT * FROM Customers

SELECT * FROM tblProfile

ALTER PROC InsertRole
  @RoleID INT,
  @CustomerID NVARCHAR(10)
AS
BEGIN
  DECLARE @UserID INT
  SET @UserID = (SELECT UserID FROM Customers WHERE CustomerID = @CustomerID)
  
  INSERT INTO tblProfile (RoleID, UserID, CreateDate)
     VALUES (@RoleID, @UserID, GETDATE())

END

GRANT EXECUTE ON InsertRole TO ISC559

EXEC InsertRole 2, 'ANTON'

DECLARE @RoleID INT, @CustomerID NVARCHAR(10)

SELECT * FROM customers 

SELECT * FROM tblRole
select * from tblProfile

ALTER PROC DeleteRole
  @RoleID INT,
  @CustomerID NVARCHAR(10)
AS
BEGIN
  DECLARE @UserID INT
  SET @UserID = (SELECT UserID FROM Customers WHERE CustomerID = @CustomerID)

  DELETE tblProfile
  WHERE UserID = @UserID AND
        RoleID = @RoleID
END

GRANT EXECUTE ON DeleteRole TO ISC559


BEGIN TRY
  BEGIN TRANSACTION
   --Some SQL statements
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: [message here].'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO
GRANT EXECUTE ON UpdateCustomer TO ISC559

--ACID
--DROP PROC InsertCustomer
ALTER PROC InsertCustomer
@CustomerID nvarchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
AS
BEGIN
BEGIN TRY
  BEGIN TRANSACTION

INSERT INTO tblUser
(Login,
 Password,
 UserType)
 
 VALUES 
 
 (@ContactName + '@Northwind.com',
 '9',
 'C')

DECLARE @UserID int
SET @UserID = @@IDENTITY

INSERT INTO Customers
           ([CustomerID],
            [CompanyName],
            [ContactName],
            [ContactTitle],
            [Address],
            [City],
            [Region],
            [PostalCode],
            [Country],
            [Phone],
            [Fax],
            [UserID])
     VALUES
           (@CustomerID,
            @CompanyName, 
            @ContactName, 
            @ContactTitle,
            @Address,
            @City,
            @Region,
            @PostalCode,
            @Country,
            @Phone,
            @Fax,
            @UserID)

  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: [message here].'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH

END
GO