پیمایش درخت WBS در پریماورا با SQL-Server

از پاپیروس
پرش به ناوبریپرش به جستجو


این مطلب نیازمند گسترش است.


برای پیمایش ساختارهای درختی در SQL از مفهومی به نام CTE یا (Common Table Expressions) استفاده می‌شود. یکی از این ساختارهای درختی، WBS یا (Work Breakdown Structure) است. در نرم‌افزار Primavera اطلاعات WBS در جدولی به نام PROJWBS نگهداری می‌شود. فیلدهایی از این جدول که در کد زیر از آن استفاده شده‌است به این شرح است:

  • wbs_id آی‌دی WBS
  • proj_id آی‌دی پروژه
  • proj_node_flag مشخص می‌کند آیا این ردیف، نقطه آغاز WBS مربوط به یک پروژه است (Y) یا خیر(N).
  • parent_wbs_id سطح بالاتر (parent) سطح فعلی را مشخص می‌کند.
  • wbs_short_name نام کوتاه WBS را برمی‌گرداند. (مترادف با WBS Code در محیط Primavera ست.)
  • seq_num ترتیب قرارگیری اعضای هم سطح را در سطح بالاتر (parent_wbs_id) مشخص می‌کند.

از پروژه‌های نمونه‌ی Primavera پروژه MFG00772 با آی‌دی ۴۰۴۹ (ممکن است در نسخه‌های مختلف عدد دیگری باشد) برای این مثال انتخاب شده‌است.
جدول شماره ۱ 

select wbs_id, proj_id, proj_node_flag, parent_wbs_id, wbs_short_name, seq_num from privuser.PROJWBS where proj_id=4049

--Results:
wbs_id      proj_id     proj_node_flag parent_wbs_id wbs_short_name                           seq_num
----------- ----------- -------------- ------------- ---------------------------------------- -----------
24042       4049        Y              18133         MFG00772                                 100
24043       4049        N              24042         Auto                                     13590
24044       4049        N              24043         SoftEng                                  13560
24045       4049        N              24043         DesEng                                   13510
24046       4049        N              24043         SysEng                                   13520
24047       4049        N              24046         RobotCtl                                 13540
24048       4049        N              24046         TempCtl                                  13530
24049       4049        N              24046         SysCtl                                   13550
24050       4049        N              24043         Train                                    13570
24051       4049        N              24050         Manual                                   13580
24052       4049        N              24042         Conv                                     13592
24053       4049        N              24052         SysDesEng                                13600
24054       4049        N              24052         Train                                    13640
24055       4049        N              24054         OpsManual                                13660
24056       4049        N              24052         FieldOps                                 13610
24057       4049        N              24056         Install                                  13620
24058       4049        N              24056         OnsiteTest                               13630
24059       4049        N              24042         Bldg                                     13591
24060       4049        N              24059         ExtFinish                                13750
24061       4049        N              24060         Roof                                     13770
24062       4049        N              24060         Brick                                    13760
24063       4049        N              24060         DoorWin                                  13780
24064       4049        N              24059         Found                                    13690
24065       4049        N              24059         DesEng                                   13680
24066       4049        N              24059         IntFinish                                13790
24067       4049        N              24066         Carp                                     13820
24068       4049        N              24066         Floor                                    13810
24069       4049        N              24066         Plumb                                    13800
24070       4049        N              24066         Paint                                    13830
24071       4049        N              24059         Struct                                   13700
24072       4049        N              24059         MechSys                                  13710
24073       4049        N              24072         Plumb                                    13740
24074       4049        N              24072         HVAC                                     13730
24075       4049        N              24072         Elev                                     13720


در نرم‌افزار Primavera ساختار WBS بالا به این صورت نمایش داده می‌شود:
جدول شماره ۲ 

WBS Code
------------------------------------------------
MFG00772
     MFG00772.Auto
          MFG00772.Auto.DesEng
          MFG00772.Auto.SysEng
               MFG00772.Auto.SysEng.TempCtl
               MFG00772.Auto.SysEng.RobotCtl
               MFG00772.Auto.SysEng.SysCtl
          MFG00772.Auto.SoftEng
          MFG00772.Auto.Train
               MFG00772.Auto.Train.Manual
     MFG00772.Bldg
          MFG00772.Bldg.DesEng
          MFG00772.Bldg.Found
          MFG00772.Bldg.Struct
          MFG00772.Bldg.MechSys
               MFG00772.Bldg.MechSys.Elev
               MFG00772.Bldg.MechSys.HVAC
               MFG00772.Bldg.MechSys.Plumb
          MFG00772.Bldg.ExtFinish
               MFG00772.Bldg.ExtFinish.Brick
               MFG00772.Bldg.ExtFinish.Roof
               MFG00772.Bldg.ExtFinish.DoorWin
          MFG00772.Bldg.IntFinish
               MFG00772.Bldg.IntFinish.Plumb
               MFG00772.Bldg.IntFinish.Floor
               MFG00772.Bldg.IntFinish.Carp
               MFG00772.Bldg.IntFinish.Paint
     MFG00772.Conv
          MFG00772.Conv.SysDesEng
          MFG00772.Conv.FieldOps
               MFG00772.Conv.FieldOps.Install
               MFG00772.Conv.FieldOps.OnsiteTest
          MFG00772.Conv.Train
               MFG00772.Conv.Train.OpsManual


با استفاده از کد زیر می‌توان خروجی مشابه با نمای فوق داشت.
جدول شماره ۳ 

declare @iproj int
declare @sepr_tx varchar(MAX)
set @iproj = 4049
set @sepr_tx = '.'

;WITH WBS_TREE(wbs_id, wbs_short_name, parent_wbs_id, levleOfWBS, WBSCode, SO) AS 
(
 SELECT wbs_id, wbs_short_name, parent_wbs_id, 
 0 AS initwbslvl, 
 CAST(INIT_POINT.wbs_short_name AS VARCHAR(MAX)) AS initWBSCode,
 CAST(INIT_POINT.seq_num + 1 AS VARBINARY(MAX)) AS initSO

 FROM privuser.PROJWBS AS INIT_POINT
 WHERE (INIT_POINT.proj_id=@iproj and proj_node_flag = 'Y')
 UNION ALL

 SELECT top 100 percent LOOP_Q.wbs_id, LOOP_Q.wbs_short_name, LOOP_Q.parent_wbs_id, 
 FINAL_Q.levleOfWBS + 1 AS loopwbslvl, 
 CAST(FINAL_Q.WBSCode + @sepr_tx + LOOP_Q.wbs_short_name AS VARCHAR(MAX)) AS loopWBSCode,
 CAST(FINAL_Q.SO + CAST(rank() over (partition by LOOP_Q.parent_wbs_id order by LOOP_Q.parent_wbs_id, LOOP_Q.seq_num, LOOP_Q.wbs_id) +1 AS BINARY(4)) AS VARBINARY(MAX)) AS loopSO

 FROM privuser.PROJWBS AS LOOP_Q INNER JOIN WBS_TREE AS FINAL_Q ON LOOP_Q.parent_wbs_id = FINAL_Q.wbs_id
	order by parent_wbs_id, seq_num, wbs_id
)

 SELECT LAST_Q.wbs_id, LAST_Q.wbs_short_name, LAST_Q.parent_wbs_id, LAST_Q.levleOfWBS, LAST_Q.WBSCode, LAST_Q.SO
 FROM WBS_TREE AS LAST_Q
order by SO

--Results:
wbs_id      wbs_short_name         parent_wbs_id     levleOfWBS  WBSCode                                 SO
----------- ---------------------- ----------------- ----------- --------------------------------------- -----------------------------------
24042       MFG00772               18133             0           MFG00772                                0x00000065
24043       Auto                   24042             1           MFG00772.Auto                           0x0000006500000002
24045       DesEng                 24043             2           MFG00772.Auto.DesEng                    0x000000650000000200000002
24046       SysEng                 24043             2           MFG00772.Auto.SysEng                    0x000000650000000200000003
24048       TempCtl                24046             3           MFG00772.Auto.SysEng.TempCtl            0x00000065000000020000000300000002
24047       RobotCtl               24046             3           MFG00772.Auto.SysEng.RobotCtl           0x00000065000000020000000300000003
24049       SysCtl                 24046             3           MFG00772.Auto.SysEng.SysCtl             0x00000065000000020000000300000004
24044       SoftEng                24043             2           MFG00772.Auto.SoftEng                   0x000000650000000200000004
24050       Train                  24043             2           MFG00772.Auto.Train                     0x000000650000000200000005
24051       Manual                 24050             3           MFG00772.Auto.Train.Manual              0x00000065000000020000000500000002
24059       Bldg                   24042             1           MFG00772.Bldg                           0x0000006500000003
24065       DesEng                 24059             2           MFG00772.Bldg.DesEng                    0x000000650000000300000002
24064       Found                  24059             2           MFG00772.Bldg.Found                     0x000000650000000300000003
24071       Struct                 24059             2           MFG00772.Bldg.Struct                    0x000000650000000300000004
24072       MechSys                24059             2           MFG00772.Bldg.MechSys                   0x000000650000000300000005
24075       Elev                   24072             3           MFG00772.Bldg.MechSys.Elev              0x00000065000000030000000500000002
24074       HVAC                   24072             3           MFG00772.Bldg.MechSys.HVAC              0x00000065000000030000000500000003
24073       Plumb                  24072             3           MFG00772.Bldg.MechSys.Plumb             0x00000065000000030000000500000004
24060       ExtFinish              24059             2           MFG00772.Bldg.ExtFinish                 0x000000650000000300000006
24062       Brick                  24060             3           MFG00772.Bldg.ExtFinish.Brick           0x00000065000000030000000600000002
24061       Roof                   24060             3           MFG00772.Bldg.ExtFinish.Roof            0x00000065000000030000000600000003
24063       DoorWin                24060             3           MFG00772.Bldg.ExtFinish.DoorWin         0x00000065000000030000000600000004
24066       IntFinish              24059             2           MFG00772.Bldg.IntFinish                 0x000000650000000300000007
24069       Plumb                  24066             3           MFG00772.Bldg.IntFinish.Plumb           0x00000065000000030000000700000002
24068       Floor                  24066             3           MFG00772.Bldg.IntFinish.Floor           0x00000065000000030000000700000003
24067       Carp                   24066             3           MFG00772.Bldg.IntFinish.Carp            0x00000065000000030000000700000004
24070       Paint                  24066             3           MFG00772.Bldg.IntFinish.Paint           0x00000065000000030000000700000005
24052       Conv                   24042             1           MFG00772.Conv                           0x0000006500000004
24053       SysDesEng              24052             2           MFG00772.Conv.SysDesEng                 0x000000650000000400000002
24056       FieldOps               24052             2           MFG00772.Conv.FieldOps                  0x000000650000000400000003
24057       Install                24056             3           MFG00772.Conv.FieldOps.Install          0x00000065000000040000000300000002
24058       OnsiteTest             24056             3           MFG00772.Conv.FieldOps.OnsiteTest       0x00000065000000040000000300000003
24054       Train                  24052             2           MFG00772.Conv.Train                     0x000000650000000400000004
24055       OpsManual              24054             3           MFG00772.Conv.Train.OpsManual           0x00000065000000040000000400000002
در صورتی که نتیجه را به صورت Indent شده (شبیه جدول شماره ۲) می‌خواهید، به انتهای خط مشخص شده در جدول شماره ۳ عبارت

‎, SPACE(5*LAST_Q.levleOfWBS)+LAST_Q.WBSCode indented_WBSCode

را بیافزایید. در این صورت به نتیجه جدول فوق این ستون اضافه می‌شود: (برای مشاهده رو عبارت نمایش کلیک کنید)

جدول شماره ۴ 

indented_WBSCode
------------------------------------------------
MFG00772
     MFG00772.Auto
          MFG00772.Auto.DesEng
          MFG00772.Auto.SysEng
               MFG00772.Auto.SysEng.TempCtl
               MFG00772.Auto.SysEng.RobotCtl
               MFG00772.Auto.SysEng.SysCtl
          MFG00772.Auto.SoftEng
          MFG00772.Auto.Train
               MFG00772.Auto.Train.Manual
     MFG00772.Bldg
          MFG00772.Bldg.DesEng
          MFG00772.Bldg.Found
          MFG00772.Bldg.Struct
          MFG00772.Bldg.MechSys
               MFG00772.Bldg.MechSys.Elev
               MFG00772.Bldg.MechSys.HVAC
               MFG00772.Bldg.MechSys.Plumb
          MFG00772.Bldg.ExtFinish
               MFG00772.Bldg.ExtFinish.Brick
               MFG00772.Bldg.ExtFinish.Roof
               MFG00772.Bldg.ExtFinish.DoorWin
          MFG00772.Bldg.IntFinish
               MFG00772.Bldg.IntFinish.Plumb
               MFG00772.Bldg.IntFinish.Floor
               MFG00772.Bldg.IntFinish.Carp
               MFG00772.Bldg.IntFinish.Paint
     MFG00772.Conv
          MFG00772.Conv.SysDesEng
          MFG00772.Conv.FieldOps
               MFG00772.Conv.FieldOps.Install
               MFG00772.Conv.FieldOps.OnsiteTest
          MFG00772.Conv.Train
               MFG00772.Conv.Train.OpsManual
برگرفته از «http://papyrus.nevisar.net/index.php?title=پیمایش_درخت_WBS_در_پریماورا_با_SQL-Server&oldid=900»