库存存储过程

USE [wkim1102]
GO
/****** Object:  StoredProcedure [dbo].[WK_Financial_age]    Script Date: 2019/12/27 15:26:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[WK_Financial_age]
@Org varchar(50),
@Period varchar(30),
@Items varchar(50),
@wh varchar(50),
@xianmu varchar(100),
@BegDate varchar(30),
@EndDate datetime
as  
begin  
SET NOCOUNT ON
--库存表

--select project.Code project,ItemOwnOrg,StoreQty,ItemInfo_ItemID,ItemInfo_ItemName,ItemInfo_ItemCode,wh
--into #trans
-- from InvTrans_WhQoh  wh 
-- left join CBO_Project project on wh.Project=project.ID
-- left join Base_Organization org on wh.ItemOwnOrg=org.ID
-- left join CBO_Wh whs on wh.Wh=whs.ID
-- where StoreQty!=0 and 
-- org.Code=@Org
-- and 1=case when isnull(@Items,'')='' then 1 when ItemInfo_ItemCode=@Items then 1 else 0 end
--and  whs.ID in(case when (@xianmu='板材数量' or @xianmu='板材金额') then (select whs.ID  where whs.ID  IN('1001506025044619','1001506025044627','1001506025044635','1001506025044611'
--  ,'1001403062214690'
--  ,'1001403062214983','1001702170258093','1001502172634111'
--  ,'1001909081671392','1001909081692600'
--  ,'1001403062215027','1001507265439432','1001507265439484'
--  ,'1002108071009778'
--  ) and whs.Org=org.ID) 

-- when @xianmu='铜排件金额' then (select whs.ID  where whs.ID  IN('1001403062214746','1001403062214794'
--  ,'1001403062214698'
--  ,'1001403062214875','1001403062214859','1002005185180121'
--  ,'1001909081671335','1001909081671552'
--  ,'1001403062215044','1001403062215061','1001403062215035'
--  ,'1002108071009778'
--  ) and whs.Org=org.ID) 
--  when @xianmu='元器件金额' then (select whs.ID  where whs.ID  IN('1001403062214762','1001403062214770'
--  ,'1001403062214698'
--  ,'1001403062214843'
--  ,'1001909081671127'
--  ,'1001403062215019','1001506024983053'
--  ,'1002012058415993','1002012058410355'
--  ) and whs.Org=org.ID) 
--  when @xianmu='库存商品金额' then (select whs.ID  where whs.ID  IN('1001403062214835',
--  '1001403062214730',
--  '1001403062215009',
--  '1001909081686245'
--  ,'1001403062215119'
--  ,'1002012058428258','1002108071007708','1002012058428360'
--  ) and whs.Org=org.ID) 
--    when @xianmu='其它金额' then (select whs.ID  where whs.ID  not in('1001506025044619','1001506025044627','1001506025044635','1001506025044611'
--  ,'1001403062214690'
--  ,'1001403062214983','1001702170258093','1001502172634111'
--  ,'1001909081671392','1001909081692600'
--  ,'1001403062215027','1001507265439432','1001507265439484'
--  ,'1002108071009778'
--  ,'1001403062214746','1001403062214794'
--  ,'1001403062214698'
--  ,'1001403062214875','1001403062214859','1002005185180121'
--  ,'1001909081671335','1001909081671552'
--  ,'1001403062215044','1001403062215061','1001403062215035'
--  ,'1002108071009778'
--  ,'1001403062214762','1001403062214770'
--  ,'1001403062214698'
--  ,'1001403062214843'
--  ,'1001909081671127'
--  ,'1001403062215019','1001506024983053'
--  ,'1002012058415993','1002012058410355'
--  ,'1001403062214835',
--  '1001403062214730',
--  '1001403062215009',
--  '1001909081686245'
--  ,'1001403062215119'
--  ,'1002012058428258','1002108071007708','1002012058428360'
--  ) and whs.Org=org.ID) 
--  ELSE '' END)

select project.Code projectr,orgtr.Name OrgName,LEFT(Convert(varchar(10),period.FromDate,120),7) Period,wh.ID WhID,whtr.Name WhName,
 itemtype.Code itemtypeCode,itemtypetr.[Name] as itemtypeName,A1.SPECS,A5S.Name CU_Name,
 A1.[Code] as [ItemID_Code], A1.[Name] as [ItemID_Name], 
 sum(A.[CostSubBalQty]) as [INVTotal_CostSubBalQty], 
 sum( case  when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end ) as [MF_BalMoney],
  A6.[Code] as [Org_Code]
  into #project
  from  InvTrans_AccountPeriodLine as A  
  left join CBO_Project project on A.Project=project.ID
  left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])  
  left join [InvTrans_AccountPeriodLineCost] as A2 on (A.[ID] = A2.[AccountPeriodLine])  
  left join [CBO_CostType] as A3 on (A2.[CostType] = A3.[ID])  
  left join [Base_UOM] as A5 on (A.[CostUOM] = A5.[ID])  
  left join Base_UOM_Trl A5S on A5S.ID=A5.ID and A5S.SysMLFlag='zh-CN'
  left join [Base_Organization] as A6 on (A.[Org] = A6.[ID])  
  left join Base_Organization_Trl orgtr on A6.ID=orgtr.ID and orgtr.SysMLFlag='zh-CN'
  left join [Base_SetofBooks] as A7 on (A2.[SOB] = A7.[ID])  
  left join CBO_Wh wh on wh.ID=A.Wh
  left join CBO_Wh_Trl whtr on wh.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
  left join Base_AccountingPeriod period on A.AccountPeriod=period.ID
  left join [CBO_Category] as itemtype on (A1.[StockCategory] = itemtype.[ID])  
  left join [CBO_Category_Trl] as itemtypetr on (itemtypetr.SysMlFlag = 'zh-CN') and (itemtypetr.[ID] = itemtype.[ID]) 
  where  (((((A2.[IsMainSOB] = 1) and (A2.[IsAccCostType] = 1) and (A7.[Code] = @Org)) 
   and A6.[Code]=@Org) and A.[QtyPriceDealFlg] in (0, 2, 4)))  --and (A1.[Code] = N'15.5504.3206')
   --and A1.[Code] in(select ItemInfo_ItemCode from #trans)
   and LEFT(Convert(varchar(10),period.FromDate,120),7)=@Period
and  wh.ID in (case when (@xianmu='板材数量' or @xianmu='板材金额') then (select wh.ID  where wh.ID  IN(1001611020221442,1001611020221602,1001611020221612
 ,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
 ,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
 ,1001611020220535
 ,1001612200000271
  ) and wh.Org=A6.ID) 

 when @xianmu='铜排件金额' then (select wh.ID  where wh.ID  IN(1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
 ,1001611020221452,1001611020221462,1001611020221482
 ,1001610130496757,1001610130496758,1001611020221166,1001611020221226
 ,1001611020220546
 ,1001612200000271
  ) and wh.Org=A6.ID) 
  when @xianmu='元器件金额' then (select wh.ID  where wh.ID  IN(1001611020221432,1001611020221572,1001611020221582
 ,1001611011087574,1001611011087677
 ,1001610130496747,1001611020221186,1001610130496795,1001611020221196
 ,1001611020220546
 ,1001611020209909,1001611020209984
  ) and wh.Org=A6.ID) 
  when @xianmu='库存商品金额' then (select wh.ID  where wh.ID  IN(1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
 1001611020220003,1001611020220058,1001611020220069,1001611020220080,1001611011087884,1001702073047087
  ) and wh.Org=A6.ID) 
  
   when @xianmu='售后金额' then (select wh.ID  where wh.ID  IN(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371
  ) and wh.Org=A6.ID) 

    when @xianmu='所有库存' then (select wh.ID  where wh.ID  IN(1001611020221156,
1001611020221166,
1001611020221176,
1001611020221186,
1001611020221196,
1001611020221206,
1001611020221216,
1001611020221226,
1001611020221236,
1001611020221246,
1001611020221256,
1001611020221266,
1001611020221276,
1001611020221286,
1001611020221296,
1001611020221306,
1001611020221320,
1001611020221330,
1001611020221340,
1001611020221350,
1001611020221360,
1001611020221370,
1001611020221380,
1001611020221390,
1001611020221400,
1001611020221410,
1001701140216358,
1001712153504084,
1001611011087575,
1001611011087657,
1001611011087667,
1001611011087717,
1001611011087738,
1001611011087739,
1001611011087818,
1001611011087847,
1001611011087867,
1001611011087884,
1001612290244422,
1001612290244438,
1001701041682704,
1001701041682906,
1001701041682945,
1001701041683096,
1001701041683135,
1001701041683171,
1001702073047087,
1001611020221432,
1001611020221442,
1001611020221452,
1001611020221462,
1001611020221472,
1001611020221482,
1001611020221492,
1001611020221502,
1001611020221512,
1001611020221532,
1001611020221572,
1001611020221582,
1001611020221592,
1001611020221602,
1001611020221612,
1001611020221632,
1001611020221642,
1001611020221652,
1001611020221672,
1001611020221682,
1001702073046092,
1001709181534949,
1001801300744738,
1001806231486379,
1001904280561252,
1001905245084482,
1001905251806348,
1001905251808792
  ) and wh.Org=A6.ID) 

    when @xianmu='其它金额' then (select wh.ID  where wh.ID  not in(1001611020221442,1001611020221602,1001611020221612
 ,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
 ,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
 ,1001611020220535
 ,1001612200000271
 ,1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
 ,1001611020221452,1001611020221462,1001611020221482
 ,1001610130496757,1001610130496758,1001611020221166,1001611020221226
 ,1001611020220546
 ,1001612200000271
 ,1001611020221432,1001611020221572,1001611020221582
 ,1001611011087574,1001611011087677
 ,1001610130496747,1001611020221186,1001610130496795,1001611020221196
 ,1001611020220546
 ,1001611020209909,1001611020209984
 ,1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
 1001611020220003,1001611020220058,1001611020220069,1001611020220080
 ,001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371
  ) and wh.Org=A6.ID) 
  ELSE '' END)
   --and A1.[Code] in(select ItemInfo_ItemCode from #trans)
  group by project.Code,orgtr.Name, LEFT(Convert(varchar(10),period.FromDate,120),7) ,wh.ID,whtr.Name,itemtype.Code,
  itemtypetr.[Name],A1.[Code], A1.[Name], A1.SPECS,A5S.Name, A6.[Code] having sum(A.[CostSubBalQty])>0 and sum( case  when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end )>0
 
---- --剔除以下单据类型2016.3.16
-- select * from InvDoc_CostAdjustDocType_Trl where Name='存货成本调整[默认]'
 
-- --售后增加单据类型
-- --TransIn001 调入单(组间调拨)
-- --TransIn002 调入单(仓库转储)
----推算三个月内收数据,以入库来推算库龄;

 select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case  when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType')) 
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case  when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #TThree
from  InvTrans_TransLine as A  
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])  
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])  
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])  
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])  
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
 where  (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
  and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
  --and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
  --and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
  and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
  and A.DocType_EntityID not in(select A.DocType_EntityID where  A.DocType_EntityID in (case when A6.ID in(  1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where  A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
  and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
  and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
  and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11)   then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
  and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-2,@Period+'-01'),120),7)
  and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,0,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #Three from #TThree group by Org,Wh,ItemCode,IttemName

--推算三到六个月收数据;
 select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case  when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType')) 
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case  when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #SSix
from  InvTrans_TransLine as A  
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])  
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])  
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])  
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])  
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
 where  (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
  and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
  --and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
  --and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
  and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
  and A.DocType_EntityID not in(select A.DocType_EntityID where  A.DocType_EntityID in (case when A6.ID in(  1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where  A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
  and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
  and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
  and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11)   then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
  and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-5,@Period+'-01'),120),7)
  and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-3,@Period+'-01'),120),7) 
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #Six from #SSix group by Org,Wh,ItemCode,IttemName
  
  
--推算六到十二个月收数据,
 select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case  when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType')) 
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case  when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #tthreety
from  InvTrans_TransLine as A  
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])  
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])  
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])  
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])  
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
 where  (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
  and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
  --and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
  --and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
  and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
  and A.DocType_EntityID not in(select A.DocType_EntityID where  A.DocType_EntityID in (case when A6.ID in(  1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where  A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
  and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
  and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
  and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11)   then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
  and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-11,@Period+'-01'),120),7)
  and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-6,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #threety from #tthreety group by Org,Wh,ItemCode,IttemName
  --推算一年到二年收数据,
 select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case  when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType')) 
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case  when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #onetwoyear
from  InvTrans_TransLine as A  
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])  
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])  
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])  
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])  
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
 where  (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
  and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
  --and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
  --and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
  and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
  and A.DocType_EntityID not in(select A.DocType_EntityID where  A.DocType_EntityID in (case when A6.ID in(  1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where  A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
  and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
  and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
  and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11)   then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
  and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-23,@Period+'-01'),120),7)
  and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-12,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #onetwoyearresult from #onetwoyear group by Org,Wh,ItemCode,IttemName
    --推算二年到三年收数据,
 select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case  when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType')) 
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case  when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #twothreeyear
from  InvTrans_TransLine as A  
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])  
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])  
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])  
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])  
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
 where  (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
  and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
  --and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
  --and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
  and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
  and A.DocType_EntityID not in(select A.DocType_EntityID where  A.DocType_EntityID in (case when A6.ID in(  1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where  A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
  and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
  and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
  and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11)   then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
  and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),DATEADD(MONTH,-35,@Period+'-01'),120),7)
  and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-24,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #twothreeyearresult from #twothreeyear group by Org,Wh,ItemCode,IttemName
--推算十二月以上收数据,改为3年以上
  select A5.ID Org,A6.ID Wh, A.[ItemInfo_ItemCode] ItemCode,A.ItemInfo_ItemName IttemName,
(case  when ((A.[DisplayDirection] = 0) and (A.[DocType_EntityType] != 'UFIDA.U9.AP.APMatch.APMatchDocType')) 
then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A.[CostUOMQty]) when (A.[DisplayDirection] = 0) then 0 else convert(decimal(24,9),null) end) ArQyt,
( case  when (A.[DisplayDirection] = 0) then (Power((-(1)),(A.[Direction] + A.[DisplayDirection])) * A1.[CostAmt]) else convert(decimal(24,9),null) end) ARMoney
into #tthreetys
from  InvTrans_TransLine as A  
left join CBO_Project project on A.Project=project.ID
left join [InvTrans_TransLineCost] as A1 on (A.[ID] = A1.[TransLine])  
left join [CBO_ItemMaster] as A2 on (A.[ItemInfo_ItemID] = A2.[ID])  
left join [Base_Organization] as A5 on (A.[Org] = A5.[ID])  
left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])  
left join CBO_Wh_Trl whtr on A6.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
 where  (((((((A.[QtyPriceDealFlg] in (0, 2, 4) and (A.[IsInit] = 0))) and (A1.[IsMainSOB] = 1))
  and (A1.[IsAccCostType] = 1)) and (A5.[Code] = @Org)))) and A.[BackLineType]=0
  --and A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')
  --and A.DocType_EntityID not in(1001403062214916,1001411163157992,1001411163158219,1001411163160100,1001411163160919,1001704298698255,1001912170007419,1002011224145362,1001902134457069,1001902134463063,1001902134467221,1001902134468564,1001902134468825,1001912260407534,1002011224156576,1001403080600876,1001403084597428,1001410173048341,1001410176023799,1001410179419464,1001410182415129,1001410185410794,1001908188352127,1001909055023050,1002011048637031)
  and A.[Doc_EntityType] in(case when A6.ID in(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371) then (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt','UFIDA.U9.InvDoc.TransferIn.TransferIn')) else (select A.[Doc_EntityType] where A.[Doc_EntityType] in('UFIDA.U9.PM.Rcv.Receivement','UFIDA.U9.InvDoc.MiscRcv.MiscRcvTrans','UFIDA.U9.InvDoc.TransferForm.TransferForm','UFIDA.U9.InvDoc.CostAdjust.CostAdjust','UFIDA.U9.MO.Complete.CompleteRpt')) end)
  and A.DocType_EntityID not in(select A.DocType_EntityID where  A.DocType_EntityID in (case when A6.ID in(  1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) then (select A.DocType_EntityID where  A.DocType_EntityID in(1001610140113902,1001610140114316,1001610140111841,1001610170660163,1001610088250101,1001612130000756,1001610140111847,1001612220464169,1001610076730101,1001610120111576,1001610140114296,1001610140111885,1001610120111589,1001701050845947)) End))
  and A.[ItemInfo_ItemCode] in(select [ItemID_Code] from #project)
  and ISNULL(A6.ID,'') not IN ( SELECT A6.ID WHERE A6.ID IN (case when whtr.Name like '售后%' then (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name NOT like '售后%'))else (select A6.ID where A6.ID IN(select ID from CBO_Wh_Trl where Name like '售后%')) END))
  and ISNULL(project.Code,'') in (case when (whtr.ID in(1001610130496691,1001611020221276
  ,1001611020220611
  ,1001611011087576
  ,1001611020221552
  ,1001611020220058,1001611020220069,1001611020220080) AND A5.Code!=11)   then (select projectr from #project WHERE [ItemID_Code]=A.ItemInfo_ItemCode and projectr=project.Code AND Wh=A.Wh) else ISNULL(project.Code,'') end)
  and left(Convert(varchar(10),A.[DocDate],120),7)>=left(convert(varchar(10),'2010-01-01',120),7)
  and left(Convert(varchar(10),A.[DocDate],120),7)<=left(convert(varchar(10),DATEADD(MONTH,-36,@Period+'-01'),120),7)
select Org,Wh,ItemCode,IttemName,sum(ArQyt) ArQyt,sum(ARMoney) ARMoney into #threetys from #tthreetys group by Org,Wh,ItemCode,IttemName

  --————————————————————————————————————————————————————————————————————————————————————————————————————————————
  --以上往后退月得出的收金额与数量
  
 select orgtr.Name OrgName,LEFT(Convert(varchar(10),period.FromDate,120),7) Period,wh.ID WhID,whtr.Name WhName,
 itemtype.Code itemtypeCode,itemtypetr.[Name] as itemtypeName,A1.SPECS,A5S.Name CU_Name,
 A1.[Code] as [ItemID_Code], A1.[Name] as [ItemID_Name], 
 sum(A.[CostSubBalQty]) as [INVTotal_CostSubBalQty], 
 sum( case  when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end ) as [MF_BalMoney],
  A6.[Code] as [Org_Code]
  into #ItemMan
  from  InvTrans_AccountPeriodLine as A  
  left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])  
  left join [InvTrans_AccountPeriodLineCost] as A2 on (A.[ID] = A2.[AccountPeriodLine])  
  left join [CBO_CostType] as A3 on (A2.[CostType] = A3.[ID])  
  left join [Base_UOM] as A5 on (A.[CostUOM] = A5.[ID])  
  left join Base_UOM_Trl A5S on A5S.ID=A5.ID and A5S.SysMLFlag='zh-CN'
  left join [Base_Organization] as A6 on (A.[Org] = A6.[ID])  
  left join Base_Organization_Trl orgtr on A6.ID=orgtr.ID and orgtr.SysMLFlag='zh-CN'
  left join [Base_SetofBooks] as A7 on (A2.[SOB] = A7.[ID])  
  left join CBO_Wh wh on wh.ID=A.Wh
  left join CBO_Wh_Trl whtr on wh.ID=whtr.ID and whtr.SysMLFlag='zh-CN'
  left join Base_AccountingPeriod period on A.AccountPeriod=period.ID
  left join [CBO_Category] as itemtype on (A1.[StockCategory] = itemtype.[ID])  
  left join [CBO_Category_Trl] as itemtypetr on (itemtypetr.SysMlFlag = 'zh-CN') and (itemtypetr.[ID] = itemtype.[ID]) 
  where  (((((A2.[IsMainSOB] = 1) and (A2.[IsAccCostType] = 1) and (A7.[Code] = @Org)) 
   and A6.[Code]=@Org) and A.[QtyPriceDealFlg] in (0, 2, 4)))  --and (A1.[Code] = N'15.5504.3206')
   --and A1.[Code] in(select ItemInfo_ItemCode from #trans)
   and LEFT(Convert(varchar(10),period.FromDate,120),7)=@Period
   and  wh.ID in (case when (@xianmu='板材数量' or @xianmu='板材金额') then (select wh.ID  where wh.ID  IN(1001611020221442,1001611020221602,1001611020221612
 ,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
 ,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
 ,1001611020220535
 ,1001612200000271
  ) and wh.Org=A6.ID) 

 when @xianmu='铜排件金额' then (select wh.ID  where wh.ID  IN(1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
 ,1001611020221452,1001611020221462,1001611020221482
 ,1001610130496757,1001610130496758,1001611020221166,1001611020221226
 ,1001611020220546
 ,1001612200000271
  ) and wh.Org=A6.ID) 
  when @xianmu='元器件金额' then (select wh.ID  where wh.ID  IN(1001611020221432,1001611020221572,1001611020221582
 ,1001611011087574,1001611011087677
 ,1001610130496747,1001611020221186,1001610130496795,1001611020221196
 ,1001611020220546
 ,1001611020209909,1001611020209984
  ) and wh.Org=A6.ID) 
  when @xianmu='库存商品金额' then (select wh.ID  where wh.ID  IN(1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
 1001611020220003,1001611020220058,1001611020220069,1001611020220080,1001611011087884,1001702073047087
  ) and wh.Org=A6.ID) 
  
   when @xianmu='售后金额' then (select wh.ID  where wh.ID  IN(1001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371
  ) and wh.Org=A6.ID) 

     when @xianmu='所有库存' then (select wh.ID  where wh.ID  IN(1001611020221156,
1001611020221166,
1001611020221176,
1001611020221186,
1001611020221196,
1001611020221206,
1001611020221216,
1001611020221226,
1001611020221236,
1001611020221246,
1001611020221256,
1001611020221266,
1001611020221276,
1001611020221286,
1001611020221296,
1001611020221306,
1001611020221320,
1001611020221330,
1001611020221340,
1001611020221350,
1001611020221360,
1001611020221370,
1001611020221380,
1001611020221390,
1001611020221400,
1001611020221410,
1001701140216358,
1001712153504084,
1001611011087575,
1001611011087657,
1001611011087667,
1001611011087717,
1001611011087738,
1001611011087739,
1001611011087818,
1001611011087847,
1001611011087867,
1001611011087884,
1001612290244422,
1001612290244438,
1001701041682704,
1001701041682906,
1001701041682945,
1001701041683096,
1001701041683135,
1001701041683171,
1001702073047087,
1001611020221432,
1001611020221442,
1001611020221452,
1001611020221462,
1001611020221472,
1001611020221482,
1001611020221492,
1001611020221502,
1001611020221512,
1001611020221532,
1001611020221572,
1001611020221582,
1001611020221592,
1001611020221602,
1001611020221612,
1001611020221632,
1001611020221642,
1001611020221652,
1001611020221672,
1001611020221682,
1001702073046092,
1001709181534949,
1001801300744738,
1001806231486379,
1001904280561252,
1001905245084482,
1001905251806348,
1001905251808792
  ) and wh.Org=A6.ID) 

    when @xianmu='其它金额' then (select wh.ID  where wh.ID  not in(1001611020221442,1001611020221602,1001611020221612
 ,1001610130496966,1001610130496952,1001610130496880,1001610130496936,1001610130497111,1001610130497153
 ,1001611011087690,1001611011087797,1001611011087847,1001611011087580,1001611011087581
 ,1001611020220535
 ,1001612200000271
 ,1001611011087758,1001611011087825,1001611011087578,1001611011087647,1001611011087837,1001611011087699,1001611011087577
 ,1001611020221452,1001611020221462,1001611020221482
 ,1001610130496757,1001610130496758,1001611020221166,1001611020221226
 ,1001611020220546
 ,1001612200000271
 ,1001611020221432,1001611020221572,1001611020221582
 ,1001611011087574,1001611011087677
 ,1001610130496747,1001611020221186,1001610130496795,1001611020221196
 ,1001611020220546
 ,1001611020209909,1001611020209984
 ,1001611020221552,1001610130496691,1001611020221276,1001611011087576,1001611020220611,
 1001611020220003,1001611020220058,1001611020220069,1001611020220080
 ,001611011087777,1001611011087727,1001611011087727
  ,1001610130496922,1001611020221296,1001610130497043,1001611020221330
  ,1001611020221632
  ,1001612200000371
  ) and wh.Org=A6.ID)
  ELSE '' END)
   and A1.[Code] in(select [ItemID_Code] from #project)
  group by orgtr.Name, LEFT(Convert(varchar(10),period.FromDate,120),7) ,wh.ID,whtr.Name,itemtype.Code,
  itemtypetr.[Name],A1.[Code], A1.[Name], A1.SPECS,A5S.Name, A6.[Code] having sum(A.[CostSubBalQty])>0 and sum( case  when A3.[CostMethod] in (4, 5) then A2.[BalPCAAmt] else A2.[CostBalMny] end )>0
 
 --select *
 -- ,isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreeMoney,
 --isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) SixMoney,
 --isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetyMoney,
 --isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetysMoney,
 --isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreeQty,
 --isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) SixQty,
 --isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetyQty,
 --isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) ThreetysQty
 --into #TuiMain
 --from #ItemMan a
 -- select *
 -- ,isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code),0) ThreeMoney,
 --isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code),0) SixMoney,
 --isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code),0) ThreetyMoney,
 --isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code),0) ThreetysMoney,
 --isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code),0) ThreeQty,
 --isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code),0) SixQty,
 --isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code ),0) ThreetyQty,
 --isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code ),0) ThreetysQty
 --into #TuiMain
 --from #ItemMan a
   select *,
 (case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #Three b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) ThreeMoney,
 (case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #Six b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) SixMoney,
 (case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #threety b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) ThreetyMoney,

 (case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) onetwoyearresultMoney,
 (case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) twothreeyearresultMoney,

 (case when @xianmu='售后金额' then isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ARMoney) from #threetys b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) ThreetysMoney,


 (case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #Three b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)ThreeQty,
 (case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #Six b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)SixQty,
 (case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #threety b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)ThreetyQty,

 (case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #onetwoyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) onetwoyearresultQty,
 (case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #twothreeyearresult b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end) twothreeyearresultQty,

 (case when @xianmu='售后金额' then isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code and a.WhID=b.Wh),0) else isnull((select sum(ArQyt) from #threetys b where b.ItemCode=a.ItemID_Code and Wh not IN(select ID from CBO_Wh_Trl where Name like '售后%')),0) end)ThreetysQty
 into #TuiMain
 from #ItemMan a
 
 
 --推算三个月内与三至六个月数据


select OrgName,Period,WhID,WhName,itemtypeCode,itemtypeName,ItemID_Code,ItemID_Name,SPECS,CU_Name,INVTotal_CostSubBalQty,MF_BalMoney,
 Org_Code, ThreeMoney,SixMoney,ThreetyMoney,onetwoyearresultMoney,twothreeyearresultMoney,ThreetysMoney,ThreeQty,SixQty,ThreetyQty,onetwoyearresultQty,twothreeyearresultQty,ThreetysQty,MF_BalMoney BalMoney,
 --三个月内算法:如果结存数大于三个月内则为结存数,如果三个月内少于结存数的则为三个月内数据;when INVTotal_CostSubBalQty=ThreeQty then MF_BalMoney 当数量相等时金额等于当前金额
 (case when ThreeMoney>0 then
        (case when INVTotal_CostSubBalQty=ThreeQty then MF_BalMoney  when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end) 
        else 0 
  end) TMoney
  ,
  (case when ThreeQty>0 then 
 (case when INVTotal_CostSubBalQty>ThreeQty then ThreeQty when INVTotal_CostSubBalQty<=ThreeQty then INVTotal_CostSubBalQty else 0 end) else 0 end) TQyt
 into #STA
 from #TuiMain
 
  --三到六个月算法:如果结存数据减去三个月内数大于零,则为结存数据减去三个月内数之和(前提三到六个月数大于零:如果结存数减去三个月之和大于六个月数据则为六个月内数据,如果少于六个月数据则为结存减去三个月数据),
--(case when MF_BalMoney-(case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end)>0 then 
----当六个月数大于零
--(case when ThreeMoney>0 then 
----如果结存减去三个月数小于等于零怎则为零,如果结存减去三个月内数据大于零(如果结存减去三个月数据大于等于六个月数据则为六个月数据,如果结存加减去三个月数小于六个月数据则为结存减去三个月数
--(case when  MF_BalMoney- (case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end)>=SixMoney

-- then SixMoney when MF_BalMoney- (case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end)<SixMoney then MF_BalMoney-(case when MF_BalMoney>ThreeMoney then ThreeMoney when MF_BalMoney<=ThreeMoney then MF_BalMoney else 0 end) else 0 end)
-- else 0 end) else 0 end) as SMoney
 SELECT *,
 (case when SixQty>0 and 
 (case when SixMoney>0 then 
 (case when (MF_BalMoney-TMoney)<=SixMoney  
 then (MF_BalMoney-TMoney) when (MF_BalMoney-TMoney)>SixMoney  then SixMoney else 0 end) else 0 end)=0 then (MF_BalMoney-TMoney) else (case when SixMoney>0 then 
 (case when (INVTotal_CostSubBalQty-(case when SixQty>0 then 
 (case when (INVTotal_CostSubBalQty-TQyt)<=SixQty
 then (INVTotal_CostSubBalQty-TQyt) when (INVTotal_CostSubBalQty-TQyt)>SixQty then SixQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney)<=SixMoney  
 then (MF_BalMoney-TMoney) when (MF_BalMoney-TMoney)>SixMoney  then SixMoney else 0 end) else 0 end) end) SMoney
 
  ,(case when SixQty>0 then 
 (case when (INVTotal_CostSubBalQty-TQyt)<=SixQty
 then (INVTotal_CostSubBalQty-TQyt) when (INVTotal_CostSubBalQty-TQyt)>SixQty then SixQty else 0 end) else 0 end) SQyt
 into #ST
 FROM #STA
 
 --推算六个月到十二个月
 select *,
 --如果结存减去三个月内减去六个月内数小于等于零则为零;如果结存减去三个月减去六个月数据大于零则(如果六到十二个月数据大于结存减去三个月与六个月数则为结存减去三个月与六个月之和,反之则等于六到十二月数),
 (case when ThreetyQty>0 and
 (case when ThreetyMoney>0  then 
 (case  when (MF_BalMoney-TMoney-SMoney)<=ThreetyMoney 
  then (MF_BalMoney-TMoney-SMoney)
  when (MF_BalMoney-TMoney-SMoney)>ThreetyMoney then ThreetyMoney else 0 end) else 0 end)= 0 then (MF_BalMoney-TMoney-SMoney) else  (case when ThreetyMoney>0  then 
 (case when (INVTotal_CostSubBalQty-(case when ThreetyQty>0 then
  (case when (INVTotal_CostSubBalQty-TQyt-SQyt)<=ThreetyQty 
  then (INVTotal_CostSubBalQty-TQyt-SQyt) 
  when (INVTotal_CostSubBalQty-TQyt-SQyt)>ThreetyQty  then ThreetyQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney-SMoney)<=ThreetyMoney 
  then (MF_BalMoney-TMoney-SMoney)
  when (MF_BalMoney-TMoney-SMoney)>ThreetyMoney then ThreetyMoney else 0 end) else 0 end) end) ThMoney
    ,(case when ThreetyQty>0 then
  (case when (INVTotal_CostSubBalQty-TQyt-SQyt)<=ThreetyQty 
  then (INVTotal_CostSubBalQty-TQyt-SQyt) 
  when (INVTotal_CostSubBalQty-TQyt-SQyt)>ThreetyQty  then ThreetyQty else 0 end) else 0 end) ThQyt
  into #Ttys
  from #ST
 
 --这里要写 1到2年
 select *,
 --如果结存减去三个月内减去六个月内数小于等于零则为零;如果结存减去三个月减去六个月数据大于零则(如果六到十二个月数据大于结存减去三个月与六个月数则为结存减去三个月与六个月之和,反之则等于六到十二月数),
 (case when onetwoyearresultQty>0 and
 (case when onetwoyearresultMoney>0  then 
 (case  when (MF_BalMoney-TMoney-SMoney-ThMoney)<=onetwoyearresultMoney 
  then (MF_BalMoney-TMoney-SMoney-ThMoney)
  when (MF_BalMoney-TMoney-SMoney-ThMoney)>onetwoyearresultMoney then onetwoyearresultMoney else 0 end) else 0 end)= 0 then (MF_BalMoney-TMoney-SMoney-ThMoney) else  (case when onetwoyearresultMoney>0  then 
 (case when (INVTotal_CostSubBalQty-(case when onetwoyearresultQty>0 then
  (case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)<=onetwoyearresultQty 
  then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt) 
  when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)>onetwoyearresultQty  then onetwoyearresultQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney-SMoney-ThMoney)<=onetwoyearresultMoney 
  then (MF_BalMoney-TMoney-SMoney-ThMoney)
  when (MF_BalMoney-TMoney-SMoney-ThMoney)>onetwoyearresultMoney then onetwoyearresultMoney else 0 end) else 0 end) end) onetwoYsMoney
    ,(case when onetwoyearresultQty>0 then
  (case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)<=onetwoyearresultQty 
  then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt) 
  when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt)>onetwoyearresultQty  then onetwoyearresultQty else 0 end) else 0 end) onetwoYsQyt
  into #onetwoyearTyy
  from #Ttys

 --这里写2到3年
--twothreeyearresultMoney
--twothreeyearresultQty
 select *,
 --如果结存减去三个月内减去六个月内数小于等于零则为零;如果结存减去三个月减去六个月数据大于零则(如果六到十二个月数据大于结存减去三个月与六个月数则为结存减去三个月与六个月之和,反之则等于六到十二月数),
 (case when twothreeyearresultQty>0 and
 (case when twothreeyearresultMoney>0  then 
 (case  when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)<=twothreeyearresultMoney 
  then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)
  when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)>twothreeyearresultMoney then twothreeyearresultMoney else 0 end) else 0 end)= 0 then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney) else  (case when twothreeyearresultMoney>0  then 
 (case when (INVTotal_CostSubBalQty-(case when twothreeyearresultQty>0 then
  (case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)<=twothreeyearresultQty 
  then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt) 
  when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)>twothreeyearresultQty  then twothreeyearresultQty else 0 end) else 0 end))=0 then MF_BalMoney when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)<=twothreeyearresultMoney 
  then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)
  when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney)>twothreeyearresultMoney then twothreeyearresultMoney else 0 end) else 0 end) end) twothreeYsMoney
    ,(case when twothreeyearresultQty>0 then
  (case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)<=twothreeyearresultQty 
  then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt) 
  when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt)>twothreeyearresultQty  then twothreeyearresultQty else 0 end) else 0 end) twothreeYsQyt
  into #Ttys_cout
  from #onetwoyearTyy
 --推算十二个月以上数据 改为36个月以上
  select *,
 (case when ThreetysMoney>0 then 
 (case when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney-twothreeYsMoney)<=ThreetysMoney then (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney-twothreeYsMoney) when (MF_BalMoney-TMoney-SMoney-ThMoney-onetwoYsMoney-twothreeYsMoney)>ThreetysMoney then ThreetysMoney  else 0 end) else 0 end) ThMoneyss
  ,(case when ThreetysQty>0 then
  (case when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt-twothreeYsQyt)<=ThreetysQty then (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt-twothreeYsQyt) when (INVTotal_CostSubBalQty-TQyt-SQyt-ThQyt-onetwoYsQyt-twothreeYsQyt)>ThreetysQty then ThreetysQty else 0 end) else 0 end) ThQytss
  into #endsum
  from #Ttys_cout
  
  select *,(case when (case when (TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney+ThMoneyss)!=BalMoney then BalMoney-(TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney) else ThMoneyss end)>0 then (case when (TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney+ThMoneyss)!=BalMoney then BalMoney-(TMoney+SMoney+ThMoney+onetwoYsMoney+twothreeYsMoney) else ThMoneyss end) else 0 end) ThMoneys 
   ,(case when (TQyt+SQyt+ThQyt+onetwoYsQyt+twothreeYsQyt+ThQytss)!=INVTotal_CostSubBalQty then INVTotal_CostSubBalQty-(TQyt+SQyt+ThQyt+onetwoYsQyt+twothreeYsQyt) else ThQytss end)  ThQyts
  from #endsum
  
  
 
  drop table #project
  drop table #TThree
  drop table #Three
  drop table #Six
  drop table #SSix
  drop table #threety
  drop table #tthreety

  drop table #onetwoyear
  drop table #onetwoyearresult
  drop table #twothreeyear
  drop table #twothreeyearresult

  drop table #threetys
  drop table #tthreetys
  drop table #ItemMan
  drop table #TuiMain
  drop table #ST
  drop table #STA
  drop table #Ttys
  drop table #onetwoyearTyy
  drop table #Ttys_cout
  drop table #endsum
 
 end

 

posted @ 2019-12-27 15:27  咖啡无眠  阅读(533)  评论(0编辑  收藏  举报