Security Procedures

Create Schema ManagerSchema
Go
Create proc ManagerSchema.usp_UpdateEmployee
@EmployeeKey int, 
@EmployeeLastName nvarchar(255), 
@EmployeeFirstName nvarchar(255), 
@EmployeeAddress nvarchar(255), 
@EmployeeCity nvarchar(255), 
@EmployeeZipCode nchar(5), 
@EmployeePhone nchar(10), 
@EmployeeEmail nvarchar(255), 
@PositionName nvarchar(255),
@EmployeeHourlyPayRate decimal(5,2),
@OrignalDatePositionAssigned Date,
@EmployeePositionDateAssigned date
As
Declare @PositionKey int
Select @Positionkey = PositionKey from Position
Where PositionName = @PositionName
if @positionKey is null
Begin
print 'Invalid position'
return -1
End
Begin tran
Begin try
Update Employee
Set [EmployeeLastName] = @EmployeeLastName,
[EmployeeFirstName] = @EmployeeFirstName,
[EmployeeAddress]=@EmployeeAddress,
[EmployeeCity]=@EmployeeCity,
[EmployeeZipCode]=@EmployeeZipCode,
[EmployeePhone]=@EmployeePhone,
[EmployeeEmail]=@EmployeeEmail
Where EmployeeKey = @EmployeeKey

Update EmployeePosition
Set [PositionKey]=@PositionKey,
[EmployeeHourlyPayRate]=@EmployeeHourlyPayRate,
[EmployeePositionDateAssigned]=@EmployeePositionDateAssigned
Where EmployeeKey = @EmployeeKey
and EmployeePositionDateAssigned = @OrignalDatePositionAssigned

Commit Tran
End try
Begin Catch
Rollback tran
Print error_Message()
return -2
End Catch


Select * from Employee

exec ManagerSchema.usp_UpdateEmployee
@EmployeeKey = 1, 
@EmployeeLastName ='Summers', 
@EmployeeFirstName='Susanne', 
@EmployeeAddress='28 Elm Ave.', 
@EmployeeCity='Seattle', 
@EmployeeZipCode='98100', 
@EmployeePhone='2065554312', 
@EmployeeEmail='Elizabeth.Adams16@metroalt.com', 
@PositionName='Lawyer', 
@EmployeeHourlyPayRate=100.00, 
@OrignalDatePositionAssigned='4/14/1998', 
@EmployeePositionDateAssigned='7/28/2016'

Select * from EmployeePosition

create Unique index ix_UniqueEmail on Employee (EmployeeEmail)

--add a driver to a route for a day
go
Create proc managerSchema.usp_AssignShift
 @BusDriverShiftKey int, 
 @EmployeeKey int, 
 @BusRouteKey int, 
 @BusScheduleAssignmentDate date, 
 @BusKey int
 As
 Begin tran
 Begin try
 Insert into BusScheduleAssignment (
BusDriverShiftKey, 
EmployeeKey, 
BusRouteKey, 
BusScheduleAssignmentDate, 
BusKey)
Values(
@BusDriverShiftKey, 
@EmployeeKey, 
@BusRouteKey, 
@BusScheduleAssignmentDate, 
@BusKey)

Commit Tran
End Try
Begin Catch
Rollback tran
print Error_Message()
return -1
End Catch



Exec [ManagerSchema].[usp_AssignShift]
@BusDriverShiftKey=2, 
@EmployeeKey=6, 
@BusRouteKey=127, 
@BusScheduleAssignmentDate='7/29/2016', 
@BusKey=43

Select * from [dbo].[BusScheduleAssignment]
Select * from Busroute

Select * from EmployeePosition where EmployeeKey=6
go
Create Proc managerSchema.usp_TotalHoursDateRange
@EmployeeKey int,
@startDate Date,
@EndDate Date
AS

Select EmployeeKey, sum(dateDiff(hour,[BusDriverShiftStartTime],[BusDriverShiftStopTime])) [Total Hours]
From BusDriverShift bs
Inner join BusScheduleAssignment bsa
on bs.BusDriverShiftKey=bsa.BusDriverShiftKey
Where EmployeeKey=@EmployeeKey
And [BusScheduleAssignmentDate] between @startDate and @EndDate
Group by EmployeeKey
go
exec [ManagerSchema].[usp_TotalHoursDateRange]
@EmployeeKey= 6, 
@startDate ='6/1/2014', 
@EndDate='6/8/2014'