销售管理.sql

毕业设计作品--超市管理系统

use DB_SMS
go

-----------------------------------------------------
--1. proc_getSaleInfo
--功能：查询销售信息
--输入：销售单编号，商品编号，商品名称或开始和结束日期	
--输出：商品销售信息(TB_SALE_SHEET, TB_SALE_GOODS, TB_GOODS, TB_EMPLOYEE)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_getSaleInfo')
	drop procedure proc_getSaleInfo
go
create procedure proc_getSaleInfo
	@sasId varchar(17) = '',		--销售单编号
	@gsId varchar(17) = '',			--商品编号
	@gsName varchar(30) = '',		--商品名称
	@startDate datetime = '',		--开始日期
	@endDate datetime = ''			--结束日期
as
	if @sasId != ''
		--根据销售单编号查询销售情况
		select gsName, gsPrice, sagCount, empName, sasTime
		from TB_GOODS as gs, TB_SALE_GOODS as sag, TB_SALE_SHEET as sas, TB_EMPLOYEE as emp
		where gs.gsId = sag.gsId and sag.sasId = sas.sasId and sas.empId = emp.empId and 
			sas.sasId like '%'+@sasId+'%'
	else if @gsId != ''
		--根据商品编号查询销售情况
		select gsName, gsPrice, sagCount, empName, sasTime
		from TB_GOODS as gs, TB_SALE_GOODS as sag, TB_SALE_SHEET as sas, TB_EMPLOYEE as emp
		where gs.gsId = sag.gsId and sag.sasId = sas.sasId and sas.empId = emp.empId and 
			sag.gsId like '%'+@gsId+'%'
	else if @gsName != ''
		--根据商品查询商品的销售情况
		select gsName, gsPrice, sagCount, empName, sasTime
		from TB_GOODS as gs, TB_SALE_GOODS as sag, TB_SALE_SHEET as sas, TB_EMPLOYEE as emp
		where gs.gsId = sag.gsId and sag.sasId = sas.sasId and sas.empId = emp.empId and 
			gsName like '%'+@gsName+'%'
	else if @startDate != '' and @endDate != ''
		--查询某段日期的销售信息
		select gsName, gsPrice, sagCount, empName, sasTime
		from TB_GOODS as gs, TB_SALE_GOODS as sag, TB_SALE_SHEET as sas, TB_EMPLOYEE as emp
		where gs.gsId = sag.gsId and sag.sasId = sas.sasId and sas.empId = emp.empId and 
			datediff(day, @startDate, sasTime) >= 0 and datediff(day, sasTime, @endDate) >= 0
	
go

------------------------------------------------------------------------------------------
--2. proc_addSaleSheetInfo
--功能：存放销售单记录
--输入：销售单编号，会员编号，员工编号，销售总额
--输出：成功或失败信息
------------------------------------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_addSaleSheetInfo')
	drop procedure proc_addSaleSheetInfo
go
create procedure proc_addSaleSheetInfo
	@sasId varchar(17),				--销售单编号
	@memId varchar(17),				--会员编号
	@empId varchar(17),				--员工编号
	@sasTotalMoney decimal(18,0),	--销售总额
	@state int output
as
	--开始事务
	begin transaction
	--添加销售单
	if @memId <> ''
		insert into TB_SALE_SHEET values(@sasId, @memId, @empId, @sasTotalMoney, getDate())
	else 
		insert into TB_SALE_SHEET(sasId, empId, sasTotalMoney, sasTime) 
			values(@sasId, @empId, @sasTotalMoney, getDate())
	if (@@error = 0)
	begin
		--提交事务
		print '提交'
		commit transaction
		set @state = 0
	end
	else 
	begin
		--回滚事务
		print '回滚'
		rollback transaction
		set @state = 1
	end
go

------------------------------------------------------------------------------------------
--3. proc_addSaleGoodsInfo
--功能：存放销售商品记录
--输入：销售单编号，商品编号，商品销售数量
--输出：成功或失败信息
------------------------------------------------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_addSaleGoodsInfo')
	drop procedure proc_addSaleGoodsInfo
go
create procedure proc_addSaleGoodsInfo
	@sagId varchar(17),		--销售商品编号
	@sasId varchar(17),		--销售单编号
	@gsId varchar(17),		--商品编号
	@sagCount int,			--销售数量
	@state int output
as
	--开始事务
	begin transaction
	--添加销售商品
	insert into TB_SALE_GOODS values(@sagId, @gsId, @sagCount, @sasId)
	if (@@error = 0)
	begin
		--提交事务
		print '提交'
		commit transaction
		set @state = 0
	end
	else 
	begin
		--回滚事务
		print '回滚'
		rollback transaction
		set @state = 1
	end
go

select * from tb_sale_sheet
select * from tb_sale_goods
select * from tb_goods