办公oa.sql

oa办公系统

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 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),				--结束时间
	@ondutytime varchar(20)	,			--上班时间
	@offdutytime varchar(20)
as
update AttendanceSetting set onStateTime=@onStateTime,onEndTime=@onEndTime,ondutytime=@ondutytime,offdutytime=@offdutytime

--查询上班和下班的时间等
create proc select_worktime
as
select * from AttendanceSetting

--站内短信
create table Send_messages
(
	s_id int identity(861000000,1),--短信ID号
	s_Sendid int,					--发送人ID号	
	s_receive varchar(50),		   --接收人姓名
	s_subject text,				   --邮件主题
	s_Content text,				   --短信内容
	s_time datetime				   --发送时间
)
--插入发件箱
create proc SendMessage
	@s_Sendid int,					--发送人ID号	
	@s_receive varchar(50),		   --接收人姓名
	@s_subject text,				   --邮件主题
	@s_Content text				   --短信内容
as
insert into Send_messages values(@s_Sendid, @s_receive, @s_subject, @s_Content, getdate())

--获取发件箱中的邮件信息
drop proc sel_sendmessageBox
Create proc sel_sendmessageBox
@s_Sendid int
as
select * from Send_messages where s_Sendid=@s_Sendid

--删除发件箱中的信息
create proc del_sendbox
@s_id int
as
delete from Send_messages where s_id=@s_id





--收件箱
drop table Receive_messages
create table Receive_messages
(
	r_id int identity(861000000,1),--短信ID号
	r_Send varchar(50),			   --发送人姓名	
	r_receive varchar(50),		   --接收人姓名
	r_receiveid int,				--接收人的ID号
	r_subject text,				   --邮件主题
	r_Content text,				   --短信内容
	r_important varchar(20),		--重要性
	r_states varchar(20) default('未读'),  --是否阅读
	r_time datetime				   --发送时间
)

--插入收件箱
drop proc receivemessage
create proc receivemessage
	@r_Send varchar(50),			   --发送人姓名	
	@r_receive varchar(50),		   --接收人姓名
	@r_receiveid int,				--接收人的ID号
	@r_subject text,				   --邮件主题
	@r_Content text,				   --短信内容
	@r_important varchar(20)		--重要性
as
insert into Receive_messages(r_Send, r_receive, r_receiveid, r_subject, r_Content, r_important, r_time)
values(@r_Send, @r_receive, @r_receiveid, @r_subject, @r_Content, @r_important,getdate())

--获取收件箱中的信息
create proc sel_receivebox
@r_receiveid int
as
select * from Receive_messages where r_receiveid=@r_receiveid


--删除收件箱中的信息
create proc del_receivebox
@r_id int
as
delete from Receive_messages where r_id=@r_id