高度难动态BOM成本计算

--========+++++++++++++++++++++++++++++++++++==========

--======= 每天都在进步，却依然追不上地球的自传=========

--======= By: zc_0101	At:2009-08-25 11:13:00=========

--========++++++++++++++++++++++++++++++++++++=========

--> 测试数据: [M_BOM]

if object_id('[M_BOM]') is not null drop table [M_BOM]

create table [M_BOM] (BILLID int,GOODSID varchar(4),QTY int,PRICE int)

insert into [M_BOM]

select 584,'A001',1,0 union all

select 588,'B001',1,0 union all

select 560,'F001',1,0

go

--> 测试数据: [M_BOMD]

if object_id('[M_BOMD]') is not null drop table [M_BOMD]

create table [M_BOMD] (BILLID int,GOODSID varchar(4),ITEMNO int,QTY int,PRICE int)

insert into [M_BOMD]

select 584,'B001',1,1,null union all

select 584,'C001',2,2,5 union all

select 584,'D001',3,1,5 union all

select 588,'E001',1,1,3 union all

select 588,'F001',2,2,null union all

select 560,'G001',1,1,2 union all

select 560,'J001',2,2,2



----------------查询------------



--函数一

--说明：输入GOODSID返回父子关系表

IF OBJECT_ID('F_GET') IS NOT NULL DROP FUNCTION F_GET

GO

CREATE FUNCTION F_GET(@GOODSID VARCHAR(8000))

	RETURNS @TABLE TABLE(

		ID INT IDENTITY,

		P_ID INT DEFAULT(0),

		GOODSID VARCHAR(8000),

		FLAG BIT

	)

AS

BEGIN

	DECLARE @TEMP_GOODSID VARCHAR(8000),@TEMP_BILLID INT

	SELECT @TEMP_BILLID=BILLID FROM [M_BOM] WHERE GOODSID=@GOODSID

	IF (SELECT COUNT(*) FROM @TABLE WHERE GOODSID=@GOODSID)=0

		BEGIN

			IF (SELECT COUNT(*) FROM [M_BOMD] WHERE GOODSID=@GOODSID)=0

				INSERT INTO @TABLE SELECT 0,@GOODSID,1

			ELSE

				INSERT INTO @TABLE SELECT BILLID,@GOODSID,1 FROM [M_BOMD] WHERE GOODSID=@GOODSID

		END

	DECLARE @TEMP TABLE(ID INT IDENTITY,GOODSID VARCHAR(8000))

	INSERT INTO @TEMP SELECT GOODSID FROM [M_BOMD] WHERE BILLID=@TEMP_BILLID

	DECLARE @ID INT,@STR VARCHAR(8000)

	SET @ID=1

	WHILE @ID<=(SELECT MAX(ID) FROM @TEMP)

		BEGIN

			IF (SELECT COUNT(*) FROM [M_BOM] WHERE GOODSID=(SELECT GOODSID FROM @TEMP WHERE ID=@ID))>0

				BEGIN

					SELECT @STR=GOODSID FROM @TEMP WHERE ID=@ID

					INSERT INTO @TABLE SELECT P_ID,GOODSID,FLAG  FROM DBO.F_GET(@STR)

				END

			ELSE

				INSERT INTO @TABLE SELECT @TEMP_BILLID,GOODSID,0 FROM @TEMP WHERE ID=@ID

			SET @ID=@ID+1

		END

	RETURN

END

--函数二

--说明：返回最终结果

IF OBJECT_ID('F_ZC') IS NOT NULL DROP FUNCTION F_ZC

GO

CREATE FUNCTION F_ZC(@GOODSID VARCHAR(20))

	RETURNS @TABLE TABLE(

		ID INT IDENTITY PRIMARY KEY,

		A_BILLID INT,

		A_GOOSID VARCHAR(8000),

		A_QTY INT,

		B_GOODSID varchar(8000),

		B_QTY int,

		B_PRICE int,

		TOTAL int

	)

AS

BEGIN

	INSERT INTO @TABLE SELECT A.P_ID,@GOODSID,(SELECT QTY FROM [M_BOM] WHERE GOODSID=@GOODSID),b.GOODSID,b.QTY,B.PRICE,B.QTY*B.PRICE FROM DBO.F_GET(@GOODSID) A 

		INNER JOIN [M_BOMD] B ON A.GOODSID=B.GOODSID AND A.FLAG=0 ORDER BY A.ID DESC

	DECLARE @TEMP TABLE(ID INT IDENTITY,P_ID INT DEFAULT(0),GOODSID VARCHAR(8000),FLAG BIT)

	INSERT INTO @TEMP SELECT P_ID,GOODSID,FLAG FROM DBO.F_GET(@GOODSID) WHERE FLAG=1

	DECLARE @I INT

	SELECT @I=MAX(ID) FROM @TEMP

	WHILE @I>=1

		BEGIN

			INSERT INTO @TABLE SELECT MAX(C.P_ID),@GOODSID,MAX(A.QTY),MAX(C.GOODSID),MAX(B.QTY),SUM(D.B_QTY*D.B_PRICE),MAX(B.QTY)*(SUM(D.B_QTY*D.B_PRICE)) FROM 

			[M_BOM] A,[M_BOMD] B,@TEMP C,@TABLE D WHERE A.GOODSID=C.GOODSID AND A.BILLID=D.A_BILLID AND C.ID=@I AND B.GOODSID=C.GOODSID GROUP BY D.A_BILLID

			SET @I=@I-1		

		END

	DELETE FROM @TABLE WHERE A_BILLID<>(SELECT BILLID FROM [M_BOM] WHERE GOODSID=@GOODSID)

	RETURN

END

GO



--------------正式查询-------------

SELECT * FROM DBO.F_ZC('A001') ORDER BY ID DESC

SELECT * FROM DBO.F_ZC('B001') ORDER BY ID DESC

 

----------------结果--------------

/*

ID	A_BILLID	A_GOOSID	A_QTY	B_GOODSID	B_QTY	B_PRICE	TOTAL

7	584	A001	1	B001	1	15	15

2	584	A001	1	C001	2	5	10

1	584	A001	1	D001	1	5	5

*/

 

/*

ID	A_BILLID	A_GOOSID	A_QTY	B_GOODSID	B_QTY	B_PRICE	TOTAL

4	588	B001	1	F001	2	6	12

3	588	B001	1	E001	1	3	3

*/