+

注释：用VB开发的进销存系统源码

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO


/****** Object:  Stored Procedure dbo.P_INStoreBill    Script Date: 2002-01-14 21:13:24 ******/
/*重算库存*/
/*增加库存*/
CREATE PROCEDURE P_INChianStoreBill @DER varchar(15),@PBillType varchar(15),@PBillNo  varchar(15)
AS

DECLARE @CChain varchar(15) 
DECLARE @CCode varchar(15) 
DECLARE @CName varchar(40)
DECLARE @CUnit varchar(10)
DECLARE @CColor varchar(15)
DECLARE @CSize varchar(15)
DECLARE @CQty NUMERIC(6,2)
DECLARE @CPrc NUMERIC(6,2)

if @PBillType='PSD' 
begin
	DECLARE C_Bill CURSOR 
	FORWARD_ONLY STATIC FOR 
		SELECT 分店编码,商品编码,品名,单位,颜色,尺寸,配送数量 FROM PSD WHERE 表单号 = @PBillNO
end
/*打开光标*/
OPEN C_Bill
/*读数据*/
FETCH NEXT FROM C_Bill INTO @CChain,@CCode,@CName,@CUnit,@CColor,@CSize,@CQty

if @DER='审核'
	set @CQty=@CQty
else
	set @CQty=-@CQty

while (@@FETCH_STATUS<>-1) 
BEGIN
	EXEC P_InChainStoreGoods @CChain,@CCode,@CName,@CUnit,@CColor,@CSize,@CQty
	FETCH NEXT FROM C_Bill INTO @CChain,@CCode,@CName,@CUnit,@CColor,@CSize,@CQty
	if @DER='审核'
		set @CQty=@CQty
	else
		set @CQty=-@CQty
END

CLOSE C_Bill
DEALLOCATE C_Bill










GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO


/*重算库存*/
/*增加库存*/
CREATE PROCEDURE P_INChainStoreGoods @PChain varchar(15),@PCode varchar(15),@PName varchar(40),@PUnit varchar(10),
	@PColor varchar(15),@PSize varchar(15),@PQty Numeric(8,2)
AS

DECLARE @CQty NUMERIC(6,2)


/*定义库存光标*/
DECLARE C_Store CURSOR 
FORWARD_ONLY STATIC FOR 
	SELECT 数量 FROM 分店库存 WHERE 分店编码=@PChain and 商品编码 = @PCode and 颜色=@PColor and 尺寸=@PSize
/*打开光标*/
OPEN C_Store
/*读数据*/
FETCH NEXT FROM C_Store INTO @CQty
--print @@FETCH_STATUS
IF @@FETCH_STATUS =0 
BEGIN
	UPDATE 分店库存 SET 数量 = 数量 + @PQty
		WHERE 分店编码=@PChain and 商品编码 = @PCode and 颜色=@PColor and 尺寸=@PSize
	--PRINT '更新库存数据！'
/*	FETCH NEXT FROM C_Store INTO @CQty,@CSum */
END
ELSE
BEGIN
	INSERT INTO 分店库存(分店编码,商品编码,品名,单位,颜色,尺寸,数量) 
	VALUES(@PChain,@PCode,@PName,@PUnit,@PCOlor,@PSize,@Pqty)
	--PRINT '增加库存数据！'
END
CLOSE C_Store
DEALLOCATE C_Store









GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE XP_计算分店库存 @结束时间 datetime AS
delete difchainstore
insert into difchainstore(分店编码,商品编码,颜色,尺寸,数量) 
	select 分店编码,商品编码,颜色,尺寸,sum(数量) as 数量 from V_计算分店库存 
		where 日期 between '2002-03-20' and @结束时间 group by 分店编码,商品编码,颜色,尺寸 
update V_UpChainstore set K数量=数量

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

create View V_DifChainStore as
select * from (select a.分店编码,a.商品编码,a.颜色,a.尺寸,a.数量 as 计算库存数量,b.数量 as 实际库存数量 from 
v_计算分店库存 as a inner join 分店库存 as b on a.分店编码=b.分店编码 and a.商品编码=b.商品编码 and a.颜色=b.颜色 and a.尺寸=b.尺寸)  as aaa where 计算库存数量<>实际库存数量






GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

create view V_UpChainStore as 
select a.分店编码,a.商品编码,a.颜色,a.尺寸,a.数量,b.数量 as K数量 from V_计算分店库存 as a inner join 分店库存 as b on a.分店编码=b.分店编码 and a.商品编码=b.商品编码 AND A.颜色=B.颜色 AND A.尺寸=B.尺寸



GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO

CREATE view V_计算分店库存 as
select 配送日期 as 日期,分店编码,商品编码,颜色,尺寸,配送数量 as 数量 from psd where 确认状态=1
union all 
select 配送日期 as 日期,分店编码,商品编码,颜色,尺寸,配送数量 as 数量 from lschainpdd where 确认状态=1
union all
select 销售时间 as 日期,分店编码,商品编码,颜色,尺寸,-数量 from 分店销售信息




GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE TABLE [DIFChainStore] (
	[分店编码] [varchar] (10) NOT NULL ,
	[商品编码] [varchar] (20) NOT NULL ,
	[颜色] [varchar] (20) NOT NULL ,
	[尺寸] [varchar] (20) NOT NULL ,
	[数量] [numeric](18, 0) NOT NULL CONSTRAINT [DF_DIFChainStore_数量] DEFAULT (0),
	CONSTRAINT [PK_DIFChainStore] PRIMARY KEY  NONCLUSTERED 
	(
		[分店编码],
		[商品编码],
		[颜色],
		[尺寸]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO