拆分汇总各类材料劳务机械SQL

将数据源表中包含的各类定额材料、组合材料、基本材料、劳务、机械拆分并汇总成基础的材料劳务机械数据

--获取临时表
WITH T_A AS
(SELECT CostAnalysisId,ProjectId,ProjectNo FROM [ECM_CostAnalysis] WHERE ProjectId='{0}' 
AND DeleteMark=0 AND SubdivisionWorkCode<>'000000000000'),
T_D AS --查询所有初始工料机条目
(SELECT T_A.ProjectId,T_A.ProjectNo,D.Category,D.Type,D.Name,D.Code,D.Unit,D.UnitPrice,
SUM(D.Amount) Amount,SUM(D.Money) Money,D.Model,MAX(D.Remark) Remark
FROM ECM_CostDetail D INNER JOIN T_A ON T_A.CostAnalysisId=D.CostAnalysisId AND D.DeleteMark=0
GROUP BY T_A.ProjectId,T_A.ProjectNo,D.Category,D.Type,D.Name,D.Code,D.Unit,D.UnitPrice,D.Model),
T_C AS --查询出所有组合材料
(SELECT T_D.ProjectId,T_D.ProjectNo,Category,T_D.Type,
T_D.Name,T_D.Code,T_D.Unit,T_D.UnitPrice,T_D.Amount,T_D.Model,T_D.Remark	
FROM T_D WHERE Category='组合材料'
UNION ALL
SELECT T_D.ProjectId,T_D.ProjectNo,'组合材料' Category,QQ.Type,
QQ.Name,QQ.EnCode,QQ.Unit,0 UnitPrice,QQ.Num*T_D.Amount,QQ.Model,QQ.Description  
FROM ECM_QuotaMaterial Q 
INNER JOIN T_D ON T_D.Code=Q.EnCode AND Q.DeleteMark=0
INNER JOIN ECM_QuotaMaterial QQ ON QQ.ParentId=Q.QuotaMaterialId AND QQ.DeleteMark=0 AND QQ.Category=4
WHERE QQ.Category=4),
T_B AS --查询出所有基本材料
(SELECT T_D.ProjectId,T_D.ProjectNo,Category,T_D.Type,
T_D.Name,T_D.Code,T_D.Unit,T_D.UnitPrice,T_D.Amount,T_D.Model,T_D.Remark 
FROM T_D WHERE Category='基本材料' AND Type NOT LIKE '自定义%'
UNION ALL
SELECT T_C.ProjectId,T_C.ProjectNo,'基本材料' Category,CC.Type,
CC.Name,CC.EnCode,CC.Unit,0 UnitPrice,CC.Num*T_C.Amount,CC.Model,CC.Description FROM ECM_CompositeMaterial C
INNER JOIN T_C ON T_C.Code=C.EnCode AND C.DeleteMark=0
INNER JOIN ECM_CompositeMaterial CC ON CC.ParentId=C.CompositeMaterialId AND CC.DeleteMark=0
UNION ALL
SELECT T_D.ProjectId,T_D.ProjectNo,'基本材料' Category,QQ.Type,
QQ.Name,QQ.EnCode,QQ.Unit,0 UnitPrice,QQ.Num*T_D.Amount,QQ.Model,QQ.Description FROM ECM_QuotaMaterial Q
INNER JOIN T_D ON T_D.Code=Q.EnCode AND Q.DeleteMark=0
INNER JOIN ECM_QuotaMaterial QQ ON QQ.ParentId=Q.QuotaMaterialId AND QQ.DeleteMark=0 AND QQ.Category=5),
T_T AS --查询出所有基本材料汇总
(SELECT ProjectId,ProjectNo,Category,Type,Name,Code,Unit,MAX(UnitPrice) UnitPrice,SUM(Amount) TotalAmount,MAX(Model) Model,MAX(Remark) Remark 
FROM T_B GROUP BY ProjectId,ProjectNo,Category,Type,Name,Code,Unit)
--数据插入临时表
INSERT INTO ECM_ConsumptionSummaryTemp(ConsumptionSummaryId,ProjectId,ProjectNo,Category,Type,Name,Code,Unit,UnitPrice,ReferencePrice,ModifyPrice,TotalAmount,Model,CalculationPrinciple,DeleteMark,TotalMoney)
SELECT NEWID(),ProjectId,ProjectNo,Category,Type,Name,Code,Unit,UnitPrice,0,0,TotalAmount,Model,Remark,0,UnitPrice*TotalAmount FROM T_T
UNION ALL
SELECT NEWID(),ProjectId,ProjectNo,Category,Type,Name,Code,Unit,UnitPrice,UnitPrice,0,Amount,Model,Remark,0,UnitPrice*Amount
FROM T_D WHERE Category <> '基本材料'AND Type NOT LIKE '自定义%'
UNION ALL
SELECT NEWID(),ProjectId,ProjectNo,Category,Type,Name,Code,Unit,UnitPrice,UnitPrice,0,Amount,Model,Remark,0,
CASE Type WHEN '自定义(占比)' THEN Money ELSE UnitPrice*Amount END
FROM T_D WHERE Type LIKE '自定义%'
--根据主表更新临时表的价格和调价用户
UPDATE [ECM_ConsumptionSummaryTemp] SET ReferencePrice=C.UnitPrice-C.ModifyPrice,
ModifyPrice=C.ModifyPrice,UnitPrice=C.UnitPrice,
TotalMoney=CASE T.Type WHEN '自定义(占比)' THEN T.TotalMoney ELSE C.UnitPrice*T.TotalAmount END,
ModifyDate=C.ModifyDate,ModifyUserName=C.ModifyUserName,Source=C.Source
FROM ECM_ConsumptionSummary C,ECM_ConsumptionSummaryTemp T
WHERE T.ProjectNo=C.ProjectNo AND T.Code=C.Code AND T.Unit=C.Unit
--删除主表
DELETE FROM [ECM_ConsumptionSummary] WHERE ProjectId='{0}';
--备份新表至主表
INSERT INTO [ECM_ConsumptionSummary] SELECT * FROM [ECM_ConsumptionSummaryTemp] WHERE ProjectId='{0}'
--删除临时表
DELETE FROM [ECM_ConsumptionSummaryTemp] WHERE ProjectId='{0}'