1、/*存储过程spwebAddLeaveReq*/
CREATE PROC DBO.spwebAddLeaveReq
@EmployeeID INT,
@StartTime DATETIME,
@SubmitTime datetime,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblLeave(
EmployeeID,
StartTime,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID ,
@StartTime ,
@SubmitTime,
@EndTime ,
@ApproverID ,
@Hours ,
@Reason )
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddLeaveReq TO BlueHillASPUser
2、/*创建存储过程spwebAddOTReq向表 tblOvertime 插入一条已提交的加班申请*/
CREATE PROC spwebAddOTReq
@EmployeeID INT,--
@Type TINYINT,
@SubmitTime DATETIME,
@EndTime DATETIME,
@ApproverID INT,
@Hours INT,
@Reason NCHAR(100)
AS
INSERT tblOvertime(
EmployeeID,
Type,
SubmitTime,
EndTime,
ApproverID,
Hours,
Reason)
VALUES(
@EmployeeID,
@Type,
@SubmitTime,
@EndTime,
@ApproverID,
@Hours,
@Reason)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebAddOTReq TO BlueHillASPUser
4、/*创建存储过程spwebCancelLeaveReq用来取消表 tblLeave 中的一条请假申请*/
CREATE PROC spwebCancelLeaveReq
@LeaveID INT
AS
DELETE tblLeave
WHERE LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelLeaveReq TO BlueHillASPUser
5、/*创建存储过程spwebCancelOTReq用来取消表tblOvertime中一条加班申请*/
CREATE PROC spwebCancelOTReq
@OvertimeID INT
AS
DELETE tblOvertime
WHERE OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebCancelOTReq TO BlueHillASPUser
6、/*创建存储过程spwebChangeLeaveStatus更新表tblLeave中的一条请假申请记录的状态，
并输入更新的理由。*/
CREATE PROC spwebChangeLeaveStatus
@LeaveID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblLeave
SET Status=@Status,DenyReason=@DenyReason
where LeaveID=@LeaveID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeLeaveStatus TO BlueHillASPUser
7、/*创建存储过程spwebChangeOTStatus更新表tblOvertime中的一条请假申请记录的状态，
并输入更新的理由。*/
CREATE PROC spwebChangeOTStatus
@OvertimeID INT,
@Status NCHAR(10),
@DenyReason NCHAR(100)
AS
UPDATE tblOvertime
SET Status=@Status,DenyReason=@DenyReason
where OvertimeID=@OvertimeID
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebChangeOTStatus TO BlueHillASPUser

8、/*创建存储过程spwebGetCEOInfo可以得到一个 CEOID从TBLEMPLOYEE表中。*/
CREATE PROC spwebGetCEOInfo
@CEOID INT
AS
RETURN @CEOID(SELECT EmployeeID FROM tblEmployee WHERE Title='CEO')
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetCEOInfo TO BlueHillASPUser

9、/*创建存储过程spwebGetCEOInfo可以按指定部门ID
和指定的时间段汇总本部门的员工考勤信息。*/
CREATE PROC spwebGetDeptAttendSummary
@DeptID? ? ? ? ? ? ? ? INT,? ? ? ? ? ? ? ?
@StartTime? ? ? ? DATETIME,? ? ? ?
@EndTime? ? ? ? DATETIME? ? ? ?
AS
SELECT * FROM viwwebAllAttend
WHERE DeptID=@DeptID OR (BreachTime>=@StartTime AND BreachTime<=@EndTime)
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptAttendSummary TO BlueHillASPUser
10、/*创建存储过程spwebGetDeptLvSummary可以按指定部门ID
和指定的时间段汇总本部门员工已批准的请假信息。*/
CREATE PROC spwebGetDeptLvSummary
@DeptID? ? ? ? ? ? ? ? INT,? ? ? ? ? ? ? ?
@StartTime? ? ? ? DATETIME,? ? ? ?
@EndTime? ? ? ? DATETIME? ? ? ?
AS
SELECT D.DeptID,L.StartTime,L.EndTime FROM tblLeave AS L
INNER JOIN tblEmployee AS E ON L.EmployeeID=E.EmployeeID
INNER JOIN tblDepartment AS D ON D.DeptID=E.DeptID
WHERE D.DeptID=@DeptID OR (L.StartTime<=@StartTime AND L.EndTime>=@EndTime) OR??L.Status='已批准'
/*BlueHillASPUser 具有 EXEC 权限*/
GRANT EXEC ON spwebGetDeptLvSummary TO BlueHillASPUser

11、/*创建存储过程spwebGetDeptOTSummary可以按指定部门ID、指定时间段
和指定加班类型汇总本部门员工已批准的加班信息*/
CREATE PROC spwebGetDeptOTSummary
@DeptID? ? ? ? ? ? ? ? INT,
@Type? ? ? ? ? ? ? ? TINYINT,
@StartTime? ? ? ? DATETIME,? ? ? ?
@EndTime? ? ? ? DATETIME? ? ? ?
AS
SELECT D.DeptID,O.