ar(50)
as
select * from staff where s_department like '%'+@s_department+'%'
--用户登录
create proc login_staff
@s_id int,
@s_password varchar(16)
as
select s_id,s_password,s_status,r_name from staff where s_id=@s_id and s_password=@s_password
--查询某个员工的详细信息
create proc sel_personInfo
@s_id int
as
select * from staff where s_id=@s_id
--角色表
drop table Roles
create table Roles
(
r_id int identity(3100,1), --角色的ID号
r_name varchar(50), --角色的名称
r_value int --角色的值
)
insert into roles values('普通员工',0)
insert into roles values('超级管理员',1)
--
查询角色信息
create proc sel_allrole
as
select * from Roles
--更改角色名称
create proc update_rolename
@r_value int,
@r_name varchar(50)
as
update roles set r_name=@r_name where r_value=@r_value
--根据部门名称查询员工信息
create proc select_departmentstaff
@s_department varchar(50)
as
select s_id,s_name from staff where s_department=@s_department
--为员工分配权限
create proc shareroles
@s_id int,
@r_name varchar(50)
as
update staff set r_name=@r_name where s_id=@s_id
--用户角色表
--create table StaffRoles
--(
-- s_id int, --用户ID号
-- r_name varchar(50) --用户角色
--)
--用户考勤
drop table Attendance
create table Attendance
(
Attendanceid int identity(209000000,1),--考勤ID号
s_id int, --用户ID号
ondutyStatus int, --上班状态
offdutyStatus int, --下班状态
ondudydate varchar(20), --上班时间
offdudydate varchar(20), --下班时间
workDate datetime, --工作的日期
Later_message text, --迟到信息
leaver_message text --早退信息
)
--上午上班
create proc onwork
@s_id int, --用户ID号
@ondutyStatus int, --上班状态
@ondudydate varchar(20), --上班时间
@workDate datetime, --
工作的日期
@Later_message text --迟到信息
as
insert into Attendance (s_id, ondutyStatus, ondudydate, workDate, Later_message)
values(@s_id, @ondutyStatus, @ondudydate, @workDate, @Later_message)
--下班
drop proc offwork
create proc offwork
@s_id int, --用户ID号
@offdutyStatus int, --下班状态
@offdudydate varchar(20), --下班时间
@workDate datetime, --工作的日期
@leaver_message text --早退信息
as
update Attendance set offdutyStatus=@offdutyStatus,offdudydate=@offdudydate,leaver_message=@leaver_message where s_id=@s_id and workDate=@workDate
create proc offwork_insert
@s_id int, --用户ID号
@offdutyStatus int, --下班状态
@offdudydate varchar(20), --下班时间
@workDate datetime, --工作的日期
@leaver_message text --早退信息
as
insert into Attendance (s_id, offdutyStatus, offdudydate, workDate, leaver_message)
values(@s_id, @offdutyStatus, @offdudydate, @workDate, @leaver_message)
--判断是否下班
create proc checkoffwork
@s_id int,
@workDate datetime
as
select offdutyStatus,offdudydate from Attendance where s_id=@s_id and workDate=@workDate
--判断上午是否重复上班
drop proc Checkagainwork
create proc Checkagainwork
@s_id int, --员工号
@workdate datetime--日期
as
select Count(*) as num from Attendance where s_id=@s_id and workDate=@workdate
--查询个人本月的上班信息
drop proc Select_Attendance
create proc Select_Attendance
@s_id int,
@workDate varchar(10)
as
select * from Attendance where s_id=@s_id and month(workDate)=@workDate
select * from Attendance where s_id=20071004 and month(getdate())=11
--统计迟到、早退、矿工等信息
drop proc totalMessage
create proc totalMessage
@s_id int,
@workDate varchar(10)
as
select isnull(sum(case when ondutyStatus=0 then 1 else 0 end),0) as later, isnull(sum(case when (ondutyStatus is null) then 1 else 0 end),0) as nowork1 ,isnull(sum(case when offdutyStatus=0 then 1 else 0 end),0) as early,isnull(sum(case when (offdutyStatus is null) then 1 else 0 end),0) as nowork2 from Attendance where s_id=@s_id and month(workdate)=@workDate
----------------------------------
--查询所有的考勤信息
create proc allAttendance
as
select * from Attendance
--SQL强类型转换
select month(Convert(DateTime,'2007-9-2'))
--上班时间设置表
drop table AttendanceSetting
create table AttendanceSetting
(
--AttendanceSettingID varchar(20) default('TimeSettings'), --ID号
onStateTime varchar(20), --执行时间
onEndTime varchar(20), --结束时间
ondutytime varchar(20) , --上班时间
offdutytime varchar(20) --下班时间
)
drop proc setwork
create proc setwork
@onStateTime varchar(20), --执行时间
@onEndTime varchar(20), --结束时间
@ondutyti