use DB_SMS
go

-----------------------------------------------------
--1. proc_getEmployeeInfo
--功能：查詢員工信息(員工編號, 員工姓名, 員工權(quán)限)
--輸入：員工編號, 員工姓名, 權(quán)限	
--輸出：員工信息(TB_EMPLOYEE, TB_EMPLOYEE_TYPE)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_getEmployeeInfo')
	drop procedure proc_getEmployeeInfo
go
create procedure proc_getEmployeeInfo
	@empId varchar(17) = '',	--員工編號
	@empName varchar(10) = '',	--員工姓名
	@emtName varchar(10) = ''	--員工權(quán)限
as
	if @empId != ''
		--根據(jù)員工編號查詢
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and empId like '%'+@empId+'%' and empState = 0
	else if @empName != ''
		--根據(jù)員工姓名查詢
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and empName like '%'+@empName+'%' and empState = 0
	else if @emtName != ''
		--根據(jù)員工權(quán)限查詢
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and emtName like '%'+@emtName+'%' and empState = 0
	else 
		--獲取所有員工信息
		select empId, empName, emtName
		from TB_EMPLOYEE as emp, TB_EMPLOYEE_TYPE as emt
		where emp.emtId = emt.emtId and empState = 0
go

-----------------------------------------------------
--2. proc_addEmployee
--功能：添加員工
--輸入：員工編號, 員工姓名, 初始密碼, 權(quán)限
--輸出：成功或失敗信息(TB_EMPLOYEE, TB_EMPLOYEE_TYPE)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_addEmployee')
	drop procedure proc_addEmployee
go
create procedure proc_addEmployee
	@empId varchar(17),		--員工編號
	@empName varchar(10),	--員工姓名
	@empPass varchar(8),	--員工密碼
	@emtName varchar(10),	--員工權(quán)限
	@state int output		--輸出狀態(tài)
as
	begin transaction 
	declare @errorSum int	--錯(cuò)誤累計(jì)
	set @errorSum = 0
	--獲取員工類型編號
	declare @emtId varchar(17)	--員工類型編號
	select @emtId = emtId from TB_EMPLOYEE_TYPE where emtName = @emtName
	--添加員工信息
	insert into TB_EMPLOYEE values(@empId, @emtId, @empName, @empPass, 0)
	set @errorSum = @errorSum + @@error

	if @errorSum <> 0
	begin
		--回滾事務(wù)
		rollback transaction
		set @state = 0
	end
	else 
	begin
		--提交事務(wù)
		commit transaction
		set @state = 1
	end
go

-----------------------------------------------------
--3. proc_GetAllEmployeeInfo
--功能：獲取所有的員工信息
--輸入：
--輸出：所有的員工信息
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_GetAllEmployeeInfo')
	drop procedure proc_GetAllEmployeeInfo
go
create procedure proc_GetAllEmployeeInfo
as
	select * from TB_EMPLOYEE
go

-----------------------------------------------------
--4. proc_updateEmployee
--功能：修改員工
--輸入：員工編號, 員工姓名, 初始密碼, 權(quán)限
--輸出：成功或失敗信息(TB_EMPLOYEE, TB_EMPLOYEE_TYPE)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_updateEmployee')
	drop procedure proc_updateEmployee
go
create procedure proc_updateEmployee
	@empId varchar(17),		--員工編號
	@empName varchar(10),	--員工姓名
	@empPass varchar(8),	--員工密碼
	@emtName varchar(10),	--員工權(quán)限
	@state int output		--輸出狀態(tài)
as
	begin transaction 
	declare @errorSum int	--錯(cuò)誤累計(jì)
	set @errorSum = 0
	--獲取員工類型編號
	declare @emtId varchar(17)	--員工類型編號
	select @emtId = emtId from TB_EMPLOYEE_TYPE where emtName = @emtName
	--添加員工信息
	update TB_EMPLOYEE set emtId = @emtId, empName = @empName, empPass = @empPass
		where empId = @empId
	set @errorSum = @errorSum + @@error

	if @errorSum <> 0
	begin
		--回滾事務(wù)
		rollback transaction
		set @state = 0
	end
	else 
	begin
		--提交事務(wù)
		commit transaction
		set @state = 1
	end
go

-----------------------------------------------------
--5. proc_deleteEmployee
--功能：刪除員工信息
--輸入：員工編號
--輸出：成功或失敗信息
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_deleteEmployee')
	drop procedure proc_deleteEmployee
go
create procedure proc_deleteEmployee
	@empId varchar(17),		--員工編號
	@state int output		--狀態(tài)信息
as
	begin transaction
	--更改員工狀態(tài)為1(不可用)
	update TB_EMPLOYEE set empState = 1 where empId = @empId
	if @@error <> 0
	begin
		--回滾事務(wù)
		rollback transaction
		set @state = 0
	end
	else 
	begin
		--提交事務(wù)
		commit transaction
		set @state = 1
	end
go

select * from tb_employee