进货管理.sql

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

use DB_SMS
go

-----------------------------------------------------
--1. proc_getStockInfo
--功能：查询供货信息(商品名称, 入库数量, 采购员, 所属分类, 入库时间)
--输入：供货商名称，商品分类或开始和结束日期	
--输出：商品入库信息(TB_GOODSTYPE, TB_GOODS, TB_STOCK_GOODS, TB_STOCK_SHEET, TB_STORAGE, TB_EMPLOYEE, TB_PROVIDER)
-----------------------------------------------------
if exists (select 1 from sysobjects where name = 'proc_getStockInfo')
	drop procedure proc_getStockInfo
go
create procedure proc_getStockInfo
	@proName varchar(30) = '',		--供货商名称
	@gtName varchar(20) = '',		--商品类型名
	@startDate datetime = '',		--开始日期
	@endDate datetime = ''			--结束日期
as
	if @proName != ''
		--根据供货商名称查询商品的供货信息
		select gsName, stoCount, empName, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STOCK_GOODS as stg, 
			TB_STOCK_SHEET as sts, TB_STORAGE as sto, TB_EMPLOYEE as emp, TB_PROVIDER as pro
		where gt.gtId = gs.gtId and gs.gsId = stg.gsId and gs.gsId = sto.gsId
			and sts.stsId = stg.stsId and sts.empId = emp.empId and sts.proId = pro.proId 
			and proName like '%'+@proName+'%'
	else if @gtName != ''
		--根据商品类型查询某类商品的供货信息
		select gsName, stoCount, empName, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STOCK_GOODS as stg, 
			TB_STOCK_SHEET as sts, TB_STORAGE as sto, TB_EMPLOYEE as emp
		where gt.gtId = gs.gtId and gs.gsId = stg.gsId and gs.gsId = sto.gsId
			and sts.stsId = stg.stsId and sts.empId = emp.empId and gtName like '%'+@gtName+'%'
	else if @startDate != '' and @endDate != ''
		--查询某段日期的进货信息
		select gsName, stoCount, empName, gtName, stoTime
		from TB_GOODS_TYPE as gt, TB_GOODS as gs, TB_STOCK_GOODS as stg, 
			TB_STOCK_SHEET as sts, TB_STORAGE as sto, TB_EMPLOYEE as emp
		where gt.gtId = gs.gtId and gs.gsId = stg.gsId and gs.gsId = sto.gsId
			and sts.stsId = stg.stsId and sts.empId = emp.empId and 
			datediff(day, @startDate, stsTime) >= 0 and datediff(day, stsTime, @endDate) >= 0
	
go

------------------------------------------------------------------------------------------
--2. proc_storageGoods
--功能：商品入库
--输入：商品编号，供应商名称，所属分类，商品名称，商品单价，入库数量，采购员，入库时间	
--输出：成功或失败信息
------------------------------------------------------------------------------------------
--1. 添加商品
--2. 添加进货单
--3. 添加进货商品
--4. 添加库存信息
if exists (select 1 from sysobjects where name = 'proc_storageGoods')
	drop procedure proc_storageGoods
go
create procedure proc_storageGoods
	@gsId varchar(17),			--商品编号
	@proName varchar(30),		--供销商名称
	@gtName varchar(20),		--商品类型名称
	@gsName varchar(30),		--商品名称
	@gsPrice int,				--商品单价
	@stoCount int,				--入库数量
	@empName varchar(10),		--采购员
	@state int output
as
	declare @errorSum int		--错误累计
	set @errorSum = 0
	--开始事务
	begin transaction
	declare @gtId varchar(17)
	declare @proId varchar(17)
	declare @empId varchar(17)
	--根据商品类型名查询商品类型编号
	select @gtId = gtId from TB_GOODS_TYPE where gtName = @gtName
	--根据供货商名称查询供货商编号
	select @proId = proId from TB_PROVIDER where proName = @proName
	--根据采购员名称查询采购员编号
	select @empId = empId from TB_EMPLOYEE where empName = @empName
	--添加商品
	insert into TB_GOODS values(@gsId, @gtId, @gsName, @gsPrice)
	set @errorSum = @errorSum + @@error

	--添加进货单
	declare @stsId varchar(17)
	exec proc_createID 'STS', @stsId output		--生成进货单编号
	insert into TB_STOCK_SHEET values(@stsId, @empId, @proId, @gsPrice * @stoCount, getDate())
	set @errorSum = @errorSum + @@error

	--添加进货商品
	declare @stgId varchar(17)
	exec proc_createID 'SGS', @stgId output		--生成进货商品编号
	insert into TB_STOCK_GOODS values(@stgId, @stsId, @gsId, @stoCount)
	set @errorSum = @errorSum + @@error

	--添加库存信息
	declare @stoId varchar(17)
	exec proc_createID 'STO', @stoId output		--生成库存编号
	insert into TB_STORAGE values(@stoId, @gsId, @stoCount, getDate())
	set @errorSum = @errorSum + @@error

	if (@errorSum = 0)
	begin
		--提交事务
		print '提交'
		commit transaction
		set @state = 1
	end
	else 
	begin
		--回滚事务
		print '回滚'
		rollback transaction
		set @state = 0
	end
go
	
select * from TB_GOODS
select * from tb_storage