金蝶云星空物料需求存储过程

ALTER PROCEDURE [dbo].[PXJT_INV_MtrlRequire]
  @projName nvarchar(30)=null,
  --@orgFilter varchar(60)=null,
  @isRpt bit=0,
  @rptTmpTableName varchar(60)=null
AS
BEGIN
  SET NOCOUNT ON;
    if OBJECT_ID(N'tempdb..#proj',N'U') is not null
    drop table #proj
    if OBJECT_ID(N'tempdb..#moList',N'U') is not null --未完工单
    drop table #moList
    if OBJECT_ID(N'tempdb..#poList',N'U') is not null --未完采购
    drop table #poList
    if OBJECT_ID(N'tempdb..#rqList',N'U') is not null --未完请购
    drop table #rqList
    if OBJECT_ID(N'tempdb..#itemList',N'U') is not null --主料列表
    drop table #itemList
    if OBJECT_ID(N'tempdb..#itemList_sub',N'U') is not null --辅料列表
    drop table #itemList_sub
  if OBJECT_ID(N'#tempdb..#stock',N'U') is not null  --库存统计
    drop table #stock
  --if isnull(@orgFilter,'')=''
  --  select @orgFilter=stuff((select CONCAT(',',FORGID) from T_ORG_ORGANIZATIONS where FORGFUNCTIONS like '%104%' for xml path('')),1,1,'')
  select p.FID proj_id,p.FNUMBER proj_no,pl.FNAME proj_name
  into #proj
  from PXJT_t_ProJect p
  left join PXJT_t_ProJect_L pl on p.FID= pl.FID
    if ISNULL(@projName,'')<>''
    delete #proj where proj_name not like '%'+@projName+'%'
  declare @currWeekend date
  select @currWeekend=dateadd(wk,datediff(wk,0,GETDATE()),6)


  begin --1、统计数据
    ;/*未完工单*/
  ;with moList as(
    select 'MO' billType,mo.FBILLNO,moEntry.FMATERIALID product_id,pbom.FMATERIALID mtrl_id,m.FNUMBER mtrl_no
    ,moEntry.F_PXJT_PROJECTID proj_id,pbom_q.FNOPICKEDQTY qty_noPickUp,moq.FNOSTOCKINQTY qty_noStockIn,cast((pbom.FNUMERATOR/pbom.FDENOMINATOR) as float) unitUsage,pbom.FNEEDDATE FDATE
    ,'F'+convert(varchar(12), case when FNEEDDATE<dateadd(D,1,@currWeekend) then @currWeekend else dateadd(wk,datediff(wk,0,FNEEDDATE),6) end,112) wkDate
      from T_PRD_MO mo with(nolock)
    join T_PRD_MOENTRY moEntry with(nolock) on mo.FID=moEntry.FID
    join T_PRD_MOENTRY_A moa with(nolock) on mo.FID=moa.FID and moa.FENTRYID=moEntry.FENTRYID
    join #proj p on p.proj_id=moEntry.F_PXJT_PROJECTID
    join T_PRD_PPBOMENTRY pbom with(nolock) on moEntry.FID=pbom.FMOID and moEntry.FENTRYID=pbom.FMOENTRYID
    join T_PRD_PPBOMENTRY_Q pbom_q with(nolock) on pbom.FID=pbom_q.FID and pbom.FENTRYID=pbom_q.FENTRYID --用料清单数量控制
    join T_BD_MATERIAL m on m.FMATERIALID=pbom.FMATERIALID
    left join T_PRD_MOENTRY_Q moq with(nolock) on moEntry.FID=moq.FID and moEntry.FENTRYID=moq.FENTRYID and moq.FNOSTOCKINQTY>0
    where mo.FDOCUMENTSTATUS ='C' and moa.FSTATUS in ('1','2','3','4') and pbom_q.FNOPICKEDQTY>0-- and mo.FPRDORGID in(@orgFilter)
  )
  select *,0 mtrl_mark 
  into #moList
  from moList where mtrl_no not like '127-%'
  union all
  select *,1 from moList where mtrl_no like '127-%'


  /*已审核未完采购单*/
  ;with poList as(
    select 'PO' billType,po.FBILLNO,po_entry.FMATERIALID mtrl_id,m.FNUMBER mtrl_no,po_entry.F_PXJT_PROJECTID proj_id,po_r.FREMAINRECEIVEQTY qty,FDATE
    ,'F'+convert(varchar(12), case when FDATE<dateadd(D,1,@currWeekend) then @currWeekend else dateadd(wk,datediff(wk,0,FDATE),6) end,112)  wkDate
    from T_PUR_POORDER po with(nolock)
    join T_PUR_POORDERENTRY po_entry with(nolock) on po.FID=po_entry.FID
    join T_PUR_POORDERENTRY_R po_r with(nolock) on po_entry.FID=po_r.FID and po_entry.FENTRYID=po_r.FENTRYID
    join T_BD_MATERIAL m  with(nolock) on po_entry.FMATERIALID= m.FMATERIALID
    where po.FDOCUMENTSTATUS='C' and FMRPCLOSESTATUS='A' and FMRPTerminateStatus='A' and po.FCLOSESTATUS='A' and FMRPFreezeStatus='A' and po_r.FREMAINRECEIVEQTY>0
  )
  select p.*,0 mtrl_mark 
  into #poList
  from poList p join #proj pj on p.proj_id=pj.proj_id 
  where mtrl_no not like '127-%'
  union all
  select poList.*,1
  from poList
  join (select distinct mtrl_id from #moList where mtrl_mark=1)t on poList.mtrl_id=t.mtrl_id


  /*请购单未下推数量*/
  ;with rqList as(
    select 'RQ' billType,req.FBILLNO,reqEntry.FMATERIALID mtrl_id,m.FNUMBER mtrl_no,reqEntry.F_PXJT_PROJECT proj_id,reqR.FREMAINQTY qty,reqEntry.FSUGGESTPURDATE FDATE
    ,'F'+convert(varchar(12), case when FSUGGESTPURDATE<dateadd(D,1,@currWeekend) then @currWeekend else dateadd(wk,datediff(wk,0,FSUGGESTPURDATE),6) end,112)  wkDate
    from T_PUR_REQUISITION req with(nolock)
    join T_PUR_REQENTRY reqEntry with(nolock) on req.FID=reqEntry.FID
    join T_PUR_REQENTRY_R reqR with(nolock) on req.FID=reqR.FID and reqEntry.FENTRYID=reqR.FENTRYID
    join T_BD_MATERIAL m  with(nolock) on reqEntry.FMATERIALID= m.FMATERIALID
    where req.FDOCUMENTSTATUS in('C') and req.FCLOSESTATUS='A' and req.FCancelStatus='A' and reqEntry.FMRPTerminateStatus='A' and reqEntry.FMRPCLOSESTATUS='A' and reqR.FREMAINQTY>0
  )
  select r.*,0 mtrl_mark
  into #rqList
  from rqList r
  join #proj pj on r.proj_id=pj.proj_id where mtrl_no not like '127-%'
  union all 
  select r.*,1 
  from rqList r join (select distinct mtrl_id from #moList where mtrl_mark=1)t on r.mtrl_id=t.mtrl_id


  /*主料列表*/
  select t.*,0 mtrl_mark,m.FNUMBER mtrl_no,ml.FNAME mtrl_name,ml.FDESCRIPTION mtrl_desc,m.FMASTERID mtrl_mid,mp.FFIXLEADTIME mtrl_LT,mp.FMINPOQTY mtrl_moq
  into #itemList
  from(
    select  distinct mtrl_id,proj_id from #poList where mtrl_mark=0
    union
    select distinct mtrl_id,proj_id from #moList where mtrl_mark=0
    union
    select distinct mtrl_id,proj_id from #rqList where mtrl_mark=0
  )t
  join T_BD_MATERIAL m with(nolock) on t.mtrl_id=m.FMATERIALID
  left join T_BD_MATERIAL_L ml with(nolock) on ml.FMATERIALID=t.mtrl_id and ml.FLOCALEID=2052
  left join t_BD_MaterialPlan mp with(nolock) on mp.FMATERIALID=t.mtrl_id
  declare @_sql varchar(500)
  select @_sql='create unique index '+ '_'+replace(NEWID(),'-','') +' on #itemList(mtrl_id,proj_id)'
  execute(@_sql)
  /*辅料列表*/
  select t.*,1 mtrl_mark,m.FNUMBER mtrl_no,ml.FNAME mtrl_name,ml.FDESCRIPTION mtrl_desc,m.FMASTERID mtrl_mid,mp.FFIXLEADTIME mtrl_LT,mp.FMINPOQTY mtrl_moq 
  into #itemList_sub
  from(
    select  distinct mtrl_id from #poList where mtrl_mark=1
    union
    select distinct mtrl_id from #moList where mtrl_mark=1
    union
    select distinct mtrl_id from #rqList where mtrl_mark=1
  )t
  join T_BD_MATERIAL m with(nolock) on t.mtrl_id=m.FMATERIALID
  left join T_BD_MATERIAL_L ml with(nolock) on ml.FMATERIALID=t.mtrl_id and ml.FLOCALEID=2052
  left join t_BD_MaterialPlan mp with(nolock) on mp.FMATERIALID=t.mtrl_id
  select @_sql='create unique index '+ '_'+replace(NEWID(),'-','') +' on #itemList_sub(mtrl_id)'
  execute(@_sql)


  /*即时库存统计*/
  select mtrl_id,proj_id,sum(stk.FBASEQTY) qty_mtrlStock  --主料
  into #stock
  from  #itemList itm 
  join T_STK_INVENTORY stk with(nolock) on (stk.FMATERIALID=mtrl_mid or stk.FMATERIALID=mtrl_id)
  join T_BD_STOCK wh with(nolock) on stk.FSTOCKID=wh.FSTOCKID     
  group by mtrl_id,proj_id
  union all
  select mtrl_id,0,sum(stk.FBASEQTY) qty_mtrlStock --辅料
  from  #itemList_sub itm join T_STK_INVENTORY stk with(nolock) on (stk.FMATERIALID=mtrl_mid or stk.FMATERIALID=mtrl_id)
  join T_BD_STOCK wh with(nolock) on stk.FSTOCKID=wh.FSTOCKID     
  group by mtrl_id
  end
  /*创建临时表，及横向日期列*/
  declare @sql1 varchar(1000)
  declare @cols varchar(500)  --日期列
    ,@cols_sum varchar(500) --按日期汇总列
    ,@fieldStr varchar(100) --模板
    ,@cols_up varchar(500)  --更新列
  select distinct wkDate wkDate into #temp from #moList
  union
  select distinct wkDate from #poList
  union
  select distinct wkDate from #rqList  
  set @fieldStr='[$1]'
  select @cols= SUBSTRING((select ','+replace(@fieldStr,'$1', wkDate) from #temp for xml path('')),2,1000)


  set @fieldStr='alter table #rpt add [$1] numeric(20,3);'
  select @sql1=(select replace(@fieldStr,'$1',wkDate) from #temp for xml path(''))


  set @fieldStr='sum(isnull([$1],0)) [$1]'
  select @cols_sum= (select ','+REPLACE(@fieldStr,'$1',wkDate) from #temp for xml path(''))
  set @cols_sum=SUBSTRING(@cols_sum,2,1000)


  set @fieldStr='0-[$1] [$1]'
  select @cols_up=(select ','+REPLACE(@fieldStr,'$1',wkDate) from #temp for xml path(''))
  set @cols_up=SUBSTRING(@cols_up,2,1000)


  /*创建报表结构#rpt*/
  select *,cast('' as nvarchar(max)) infor,cast(0 as decimal(20,3)) qty_noStockIn
  ,cast(0 as decimal(10,6)) unitUsage,cast(0 as decimal(20,3)) qty_stock,cast(0 as decimal(20,3)) qty_receiveStay,cast('' as char(2)) billType,GETDATE() as FDATE,99 as groupId,99 sortId
  into #rpt
  from #itemList where 1=2
  execute(@sql1)


  begin --2、将数据插入报表临时表 #rpt*/
      ;/*将未完工单插入报表*/
    declare @sql2 nvarchar(max)
    set @sql2='
    insert into #rpt 
    select * from(
      select mo.mtrl_id,mo.proj_id,mo.mtrl_mark,mo.mtrl_no
      ,mtrl_name
      ,mtrl_desc,itm.mtrl_mid
      ,mtrl_LT
      ,mtrl_moq
      ,FBILLNO+''[''+m.FNAME+'']''+''[''+m.FDESCRIPTION+'']'' infor
      ,qty_noStockIn,unitUsage,qty_mtrlStock,qty_noPickUp,0 as qty_receiceStay
      ,billType
      ,mo.FDATE,1 groupId,ROW_NUMBER() over(partition by mtrl_mid,mo.proj_id order by FDATE) sortId
      ,mo.wkDate
      from #moList mo
      join T_BD_MATERIAL_L m with(nolock) on m.FMATERIALID=product_id  
      join #itemList itm on itm.mtrl_id=mo.mtrl_id 
      left join #stock s on s.mtrl_id=mo.mtrl_id and s.proj_id=mo.proj_id and s.proj_id>0
      where mo.mtrl_mark=0
    )p pivot(sum(qty_noPickUp) for wkDate in('+@cols+')) as pvt'
    execute(@sql2)
    set @sql2='
    insert into #rpt 
    select * from(
      select mo.mtrl_id,mo.proj_id,mo.mtrl_mark,mo.mtrl_no
      ,mtrl_name
      ,mtrl_desc,itm.mtrl_mid
      ,mtrl_LT
      ,mtrl_moq
      ,FBILLNO+''[''+m.FNAME+'']''+''[''+m.FDESCRIPTION+'']'' infor
      ,qty_noStockIn,unitUsage,qty_mtrlStock,qty_noPickUp,0 as qty_receiceStay
      ,billType
      ,mo.FDATE,1 groupId,ROW_NUMBER() over(partition by mtrl_mid order by FDATE) sortId
      ,mo.wkDate
      from #moList mo
      join T_BD_MATERIAL_L m with(nolock) on m.FMATERIALID=product_id  
      join #itemList_sub itm on itm.mtrl_id=mo.mtrl_id 
      left join #stock s on s.mtrl_id=mo.mtrl_id and s.proj_id=0
      where mo.mtrl_mark=1
    )p pivot(sum(qty_noPickUp) for wkDate in('+@cols+')) as pvt'
    execute(@sql2)
    /*将未完采购单插入报表*/
    set @sql2='
    insert into #rpt(mtrl_mid,mtrl_id,proj_id,mtrl_mark,mtrl_no,mtrl_name,mtrl_desc,mtrl_LT,mtrl_moq,infor,qty_stock,billType,FDATE,groupId,sortId,'
    +
      @cols
    +
    ') select * from(
      select itm.mtrl_mid,po.mtrl_id,po.proj_id,po.mtrl_mark,po.mtrl_no
        ,mtrl_name,mtrl_desc,mtrl_LT,mtrl_moq,FBILLNO,s.qty_mtrlStock     
      ,billType,po.FDATE,2 groupId,ROW_NUMBER() over(partition by mtrl_mid,po.proj_id order by FDATE) sortId,po.wkDate,po.qty
      from #poList po
      join #itemList itm on itm.mtrl_id=po.mtrl_id and itm.proj_id=po.proj_id
      left join #stock s on s.mtrl_id=po.mtrl_id and s.proj_id=s.proj_id 
      where po.mtrl_mark=0
    )p pivot(sum(qty) for wkDate in('+@cols+')) as pvt'
    execute(@sql2)
    set @sql2='
    insert into #rpt(mtrl_mid,mtrl_id,proj_id,mtrl_mark,mtrl_no,mtrl_name,mtrl_desc,mtrl_LT,mtrl_moq,infor,qty_stock,billType,FDATE,groupId,sortId,'
    +
      @cols
    +
    ') select * from(
      select itm.mtrl_mid,po.mtrl_id,po.proj_id,po.mtrl_mark,po.mtrl_no
        ,mtrl_name,mtrl_desc,mtrl_LT,mtrl_moq,FBILLNO,s.qty_mtrlStock     
      ,billType,po.FDATE,2 groupId,ROW_NUMBER() over(partition by mtrl_mid order by FDATE) sortId ,po.wkDate,po.qty
      from #poList po
      join #itemList_sub itm on itm.mtrl_id=po.mtrl_id
      left join #stock s on s.mtrl_id=po.mtrl_id and s.proj_id=0
      where po.mtrl_mark=1
    )p pivot(sum(qty) for wkDate in('+@cols+')) as pvt'
    execute(@sql2)
    /*请购单未下推数量，插入报表*/
    set @sql2='insert into #rpt(mtrl_mid,mtrl_id,proj_id,mtrl_mark,mtrl_no,mtrl_name,mtrl_desc,mtrl_LT,mtrl_moq,infor,qty_stock,billType,FDATE,groupId,sortId,'
    +
      @cols
    +
    ') select * from (
      select itm.mtrl_mid,rq.mtrl_id,rq.proj_id,rq.mtrl_mark,rq.mtrl_no,itm.mtrl_name,itm.mtrl_desc,itm.mtrl_LT,itm.mtrl_moq,FBILLNO,s.qty_mtrlStock,billType,rq.FDATE,3 groupId
      ,ROW_NUMBER() over(partition by mtrl_mid,rq.proj_id order by FDATE) sortId,rq.wkDate,rq.qty
      from #rqList rq
      join #itemList itm on rq.mtrl_id=itm.mtrl_id and rq.proj_id=itm.proj_id
      left join #stock s on s.mtrl_id=rq.mtrl_id and s.proj_id=rq.proj_id
      where rq.mtrl_mark=0
      )p pivot(sum(qty) for wkDate in('+@cols+')) as pvt'
    execute(@sql2)
    set @sql2='insert into #rpt(mtrl_mid,mtrl_id,proj_id,mtrl_mark,mtrl_no,mtrl_name,mtrl_desc,mtrl_LT,mtrl_moq,infor,qty_stock,billType,FDATE,groupId,sortId,'
    +
      @cols
    +
    ') select * from (
      select itm.mtrl_mid,rq.mtrl_id,rq.proj_id,rq.mtrl_mark,rq.mtrl_no,itm.mtrl_name,itm.mtrl_desc,itm.mtrl_LT,itm.mtrl_moq,FBILLNO,s.qty_mtrlStock,billType,rq.FDATE,3 groupId
      ,ROW_NUMBER() over(partition by mtrl_mid order by FDATE) sortId,rq.wkDate,rq.qty
      from #rqList rq
      join #itemList_sub itm on rq.mtrl_id=itm.mtrl_id
      left join #stock s on s.mtrl_id=rq.mtrl_id and s.proj_id=0
      where rq.mtrl_mark=1
      )p pivot(sum(qty) for wkDate in('+@cols+')) as pvt'
    execute(@sql2)


    /*插入汇总行*/
    set @sql2=';with temp as(select mtrl_mid,mtrl_mark,proj_id,mtrl_no,groupId,999 sortId,min(qty_stock) qty_stock,'+
    'case groupId when 1 then ''需求汇总'' when 2 then ''PO汇总'' when 3 then ''RQ汇总'' end infor,''1979-01-01 00:00:00'' FDATE,'+
    @cols_sum+
    ' from #rpt
    group by mtrl_mid,mtrl_mark,proj_id,mtrl_no,groupId)
    insert into #rpt(mtrl_mid,mtrl_mark,proj_id,mtrl_no,groupId,sortId,qty_stock,infor,FDATE,'
    +
      @cols
    +
    ') select * from temp'
    execute(@sql2)
  end
  begin --3、插入余量行
    alter table #rpt drop column FDATE
    declare @index varchar(30)
    declare @field table(
      FID varchar(20),
      id int
    )
    /*为报表添加索引*/
    select @_sql='create NONCLUSTERED index $1 on #rpt([mtrl_mid],[sortId],[groupId])'
    select @index='_'+REPLACE(newid(),'-','')
    set @_sql=REPLACE(@_sql,'$1',@index)
    execute(@_sql)


    insert into @field select *,ROW_NUMBER() over(order by FID) id from dbo.fn_StrSplit(@cols,',')  /*将【周】(2020/01/01,2020/01/08)字符串用逗号分隔到表函数*/
    declare @mtrl_mid int,@proj_id int,@mtrl_mark bit,@mtrl_no varchar(30),@qty_stock numeric(20,3),@qty numeric(20,3),@qty_last numeric(20,3),@fieldCount int,@seed tinyint
    declare @currentWeek varchar(20),@sql_temp varchar(1000),@val varchar(1000),@sql_insert varchar(2000),@sql_temp1 nvarchar(2000),@qty_temp numeric(20,3)
    declare @rowCount int,@seed_out int=1
    select @fieldCount=count(*) from @field
  
    /*将汇总行抽出插入临时表*/
    select * into #rpt_summary from #rpt where sortId=999
    select @_sql='create NONCLUSTERED index $1 on #rpt_summary([mtrl_mid],[sortId],[groupId])'
    set @index='_'+REPLACE(newid(),'-','')
    set @_sql=REPLACE(@_sql,'$1',@index)
    execute(@_sql)


    set @sql_temp='insert into #rpt(mtrl_mid,mtrl_mark,proj_id,mtrl_no,groupId,sortId,infor,'+@cols+') select '
    select *,ROW_NUMBER() over(order by mtrl_mid,proj_id) rid into #temp1 
    from(
      select distinct mtrl_mid,mtrl_mark,proj_id,mtrl_no from #rpt where sortId<999
    )t
    select @rowCount=count(*) from #temp1
    while @seed_out<=@rowCount
    begin
      select @mtrl_mid=mtrl_mid,@mtrl_mark=mtrl_mark,@proj_id=proj_id,@mtrl_no=mtrl_no from #temp1 where rid=@seed_out
      set @seed=1
      set @qty_last=0
      set @val=''
      select top 1 @qty_stock=isnull(qty_stock,0) from #rpt where sortId=999 and mtrl_mid=@mtrl_mid and proj_id=@proj_id
      while @seed<=@fieldCount
      begin
        set @qty=0
        set @qty_temp=0
        if @seed=1
          set @qty=@qty+@qty_stock
        set @qty=@qty+@qty_last
        select @currentWeek=FID from @field WHERE id=@seed
        set @qty_temp=0
        set @sql_temp1='select @qty='+@currentWeek+' from #rpt_summary with(index='+@index+') where mtrl_mid=@mtrl_mid and groupId=1 and sortId=999'
        exec sp_executesql @sql_temp1,N'@mtrl_mid int,@qty numeric(20,3) output',@mtrl_mid,@qty_temp output
        set @qty=@qty-isnull(@qty_temp,0)
        set @qty_temp=0
        set @sql_temp1='select @qty=sum('+@currentWeek+') from #rpt_summary with(index='+@index+') where mtrl_mid=@mtrl_mid and groupId>1 and sortId=999 group by mtrl_mid,proj_id'
        exec sp_executesql @sql_temp1,N'@mtrl_mid int,@qty numeric(20,3) output',@mtrl_mid,@qty_temp output  
        set @qty=@qty+isnull(@qty_temp,0)
        set @val=CONCAT(@val,',',@qty)
        set @qty_last=@qty
        set @seed+=1
      end
      if @val<>''
      begin
        set @sql_insert=@sql_temp +CONCAT( @mtrl_mid,',',@mtrl_mark,',',@proj_id,',''',@mtrl_no,''',',999,',',1,',''余量''',@val)
        --select @sql_insert
        execute(@sql_insert)
      end    
      set @seed_out=@seed_out+1
    end
  end 


  if @isRpt=1 and isnull(@rptTmpTableName,'')<>''
  begin
    execute('select mtrl_no 物料,p.proj_name 项目,mtrl_name 物料名称,mtrl_desc 物料规格
    ,mtrl_LT LT,mtrl_moq MOQ,infor 机型单号,qty_stock 即时库存,qty_noStockIn 待生产量,unitUsage 单位用量,qty_receiveStay 待入库,billType,'
    +
     @cols
    +
    ',mtrl_mid,mtrl_id,groupId,sortId,r.proj_id,mtrl_mark,ROW_NUMBER() OVER(ORDER BY r.mtrl_mid,r.proj_id,groupId,sortId) FIDENTITYID into '
    +@rptTmpTableName
    +' from #rpt r left join #proj p on r.proj_id=p.proj_id and r.proj_id>0 and r.mtrl_mark=0 ORDER BY mtrl_mid,r.proj_id,groupId,sortId'
    )
  end
  else
    select r.*,p.proj_name from #rpt r left join #proj p on r.proj_id=p.proj_id and r.proj_id>0 and r.mtrl_mark=0 ORDER BY mtrl_mid,proj_id,groupId,sortId
END