BOM核算及树型排序

  

        我相信好多程序员朋友都遇到过核算公司BOM，求一个成品的单价的问题。在CSDN上，也有许多朋友发贴问这样的问题。我为我们公司开发了许多报表，涉及到各个部门，当然也包括采购部门（采购经理要看），那核算BOM成品单价也是理所当然的事了。其实核算一个BOM成品的算法不是很难,但要以树型的方式来显示就有点难度了。在我们公司，我写了两个报表一个是单层的（老板要看）,另一个是多层的也就是树型结构（工程部，采购部要看）。老板看单层，是因为他只关心结果，只要看一个成品下面第一层每个半成品共要多少钱就可以了。而工程部可不同了，他们可要看到一个层次结构。下面我以这个BOM为例，详细叙述如何核算BOM及层次显示。Follow Me!

 

                             FG001

                               |

 ---------------------------------------------------------------------

 |                                  |                                |

SFG001                           SFG002                           SFG003

 |                                   |                                  

-------------------               ---------------------                 

|                 |               |            |      |

WIP001         WIP002           WIP003       WIP004 WIP005       WIP006

|                 |               |

RAW001 RAW002  RAW003      RAW004,RAW005  ............

|                             |

KKK001                         KKK003

|

WWW005

 

一:先创建BOM表

 

create table t
(parent varchar(10),
 child varchar(10),qty numeric(9,2)
)

insert into t
select 'FG001',  'SFG001',     1  union all
select 'FG001' , 'SFG002',     1 union all
select 'FG001'  ,'SFG003',     1 union all
select 'SFG001', 'WIP001',     2 union all
select 'SFG001' ,'WIP002',     2 union all
select 'SFG002' ,'WIP003',     3 union all
select 'SFG002' ,'WIP004',     3 union all
select 'SFG002' ,'WIP005',     2 union all
select 'SFG003' ,'WIP006',     3 union all
select 'WIP001' ,'RAW001',     2.66 union all
select 'WIP001' ,'RAW002'  ,   2.33 union all
select 'WIP002' ,'RAW003'  ,   3.21 union all
select 'WIP003' ,'RAW004'  ,   1.89 union all
select 'WIP003' ,'RAW005'  ,   1.86 union all
select 'RAW001','KKK001',      3.25 union all
select 'RAW004','KKK003',      4.26 union all
select 'KKK001','WWW005',      5.23

 

二:创建函数（a:树型结构显示）

 

 

create function test(@parent VARCHAR(10))
returns @t table(parent Nvarchar(10),child Nvarchar(10),qty numeric(9,2),
                level int,sort Nvarchar(1000)collate Latin1_General_BIN  ) 
as
begin
     declare @level int
     set @level=1
     insert into @t
     select parent,child,qty,@level,parent+child 
     from t
     where parent=@parent collate Latin1_General_BIN 
     while @@rowcount>0
     begin
         set @level=@level+1
         insert @t
         select a.parent,a.child,a.qty*b.qty,@level,b.sort+a.child
         from   t a ,@t b
         where  a.parent=b.child collate Latin1_General_BIN
                and b.level=@level-1   
    end
return
end

--调用函数
select
    level,
    space(level*2)+'|--' + child as 'product',
    qty
from 
    dbo.test('FG001')
order by 
    sort 


--结果
/**//*
level       product                         qty         
1             |--SFG001                     1.00
2               |--WIP001                   2.00
3                 |--RAW001                 5.32
4                   |--KKK001               17.29
5                     |--WWW005             90.43
3                 |--RAW002                 4.66
2               |--WIP002                   2.00
3                 |--RAW003                 6.42
1             |--SFG002                     1.00
2               |--WIP003                   3.00
3                 |--RAW004                 5.67
4                   |--KKK003               24.15
3                 |--RAW005                 5.58
2               |--WIP004                   3.00
2               |--WIP005                   2.00
1             |--SFG003                     1.00
2               |--WIP006                   3.00

(17 row(s) affected)
*/

 

三:创建函数（b:单层结构显示）

 

 

create function test(@parent VARCHAR(10))
returns @t table(parent Nvarchar(10),child Nvarchar(10),qty numeric(9,2),
                level int,sort Nvarchar(1000)collate Latin1_General_BIN  ) 
as
begin
     declare @level int
     set @level=1
     insert into @t
     select parent,child,qty,@level,parent
     from t
     where parent=@parent collate Latin1_General_BIN 
     while @@rowcount>0
     begin
         set @level=@level+1
         if @level=2 
            begin
                 insert @t
                 select a.parent,a.child,a.qty*b.qty,@level,a.parent
                 from   t a ,@t b
                 where  a.parent=b.child collate Latin1_General_BIN
                        and b.level=@level-1   
            end
         else
            begin
                 insert @t
                 select a.parent,a.child,a.qty*b.qty,@level,b.sort
                 from   t a ,@t b
                 where  a.parent=b.child collate Latin1_General_BIN
                        and b.level=@level-1 
            end
    end
return
end

--函数调用

select *  from dbo.test('FG001')

 

--水晶报表结果: