OAoffice 系统源码

create database OAoffice GO USE OAoffice --职员信息表 drop table Staff create table Staff ( s_id int identity(20071001,1), --员工ID号 s_password varchar(16), --密码 s_name varchar(50), --姓名 s_sex varchar(10), --性别 s_nation varchar(20), --民族 s_birthday datetime, --出生日期 s_collage varchar(100), --毕业学校 s_edu varchar(50), --学历 s_phone varchar(15), --固定电话 s_mobile varchar(15), --移动电话 s_email varchar(50), --邮箱 s_department varchar(50), --部门 s_departmentid int, --部门ID号 s_position varchar(50), --职位 s_positionid int, --职位ID号 s_status int default(1), --职员状态，1代表在职，0代表离职 s_registerDate datetime, --入职时间 r_name varchar(50) default('0') --角色的名称,0代表普通员工，1代表管理员 ) --查询员工信息（通信录 等） drop proc sel_staff create proc sel_staff as select * from staff order by s_name --添加员工信息 drop proc add_staff create proc add_staff @s_name varchar(50), --姓名 @s_password varchar(16), --密码 @s_sex varchar(10), --性别 @s_nation varchar(20), --民族 @s_birthday datetime, --出生日期 @s_collage varchar(100), --毕业学校 @s_edu varchar(50), --学历 @s_phone varchar(15), --固定电话 @s_mobile varchar(15), --移动电话 @s_email varchar(50), --邮箱 @s_department varchar(50), --部门 @s_departmentid int, --部门ID号 @s_position varchar(50), --职位 @s_positionid int, --职位ID @s_registerDate datetime --入职时间 as insert into staff (s_password,s_name, s_sex, s_nation, s_birthday, s_collage, s_edu, s_phone, s_mobile, s_email, s_department, s_departmentid, s_position, s_positionid, s_registerDate) values (@s_password,@s_name, @s_sex, @s_nation, @s_birthday, @s_collage, @s_edu, @s_phone, @s_mobile, @s_email, @s_department, @s_departmentid, @s_position, @s_positionid, @s_registerDate) select scope_identity() --更新员工信息 drop proc update_staff create proc update_staff @s_id int, @s_name varchar(50), --姓名 @s_sex varchar(10), --性别 @s_nation varchar(20), --民族 @s_birthday datetime, --出生日期 @s_collage varchar(100), --毕业学校 @s_edu varchar(50), --学历 @s_phone varchar(15), --固定电话 @s_mobile varchar(15), --移动电话 @s_email varchar(50), --邮箱 @s_department varchar(50), --部门 @s_departmentid int, --部门ID号 @s_position varchar(50), --职位 @s_positionid int, --职位ID号 @s_status int, --职员状态，1代表在职，0代表离职 @r_name varchar(50) --角色的名称,0代表普通员工，1代表管理员 as update staff set s_name=@s_name , --姓名 s_sex=@s_sex , --性别 s_nation=@s_nation, --民族 s_birthday=@s_birthday, --出生日期 s_collage=@s_collage, --毕业学校 s_edu=@s_edu, --学历 s_phone=@s_phone, --固定电话 s_mobile=@s_mobile, --移动电话 s_email=@s_email, --邮箱 s_department=@s_department, --部门 s_departmentid=@s_departmentid, --部门ID号 s_position=@s_position, --职位 s_positionid=@s_positionid, --职位ID号 s_status=@s_status, --职员状态，1代表在职，0代表离职 r_name=@r_name where s_id=@s_id --删除用户信息 drop proc del_staff create proc del_staff @s_id int as delete from staff where s_id=@s_id delete from Send_messages where s_Sendid=@s_id delete from Attendance where s_id=@s_id delete from vacation where s_id=@s_id ----判断旧密码是否输入正确 --create proc select_oldpassword --@s_password varchar(16) --as --select --重设密码 create proc set_PassWord @s_id int, @s_password varchar(16) as update staff set s_password=@s_password where s_id=@s_id --修改密码 create proc update_password @s_id int, @s_password varchar(16) as update staff set s_password=@s_password where s_id=@s_id --根据用户选择的条件查询用户信息 --(1)员工编号 drop proc Sel_staff_id create proc Sel_staff_id @s_id varchar(50) as select * from staff where Cast(s_id as varchar(50)) like '%'+@s_id+'%' go --(2)员工姓名 drop proc Sel_Staff_Name create proc Sel_Staff_Name @s_name varchar(50) as select * from Staff where s_name like '%'+@s_name+'%' go --(3)部门的名称 drop proc Sel_Staff_Department create proc Sel_Staff_Department @s_department varchar(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