WIP报表查询

最新推荐文章于 2024-05-21 22:13:42 发布

lvchunhu_

最新推荐文章于 2024-05-21 22:13:42 发布

阅读量887

点赞数 21

文章标签： java sql

本文链接：https://blog.csdn.net/lvchunhu_1998/article/details/135959856

版权

什么是WIP?

ERP中WIP的意思即为Work In Process，也就是在制品的意思。在制品指的是正在加工，尚未完成的产品。广义的包括正在加工的产品和准备进一步加工的半成品;狭义的仅指正在加工的产品，合理地控制在制品、半成品的储备量，做好保管工作，使它们不受损坏，可以保证产品质量，节约流动资金，缩短生产周期，减少和避免积压。

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

年前暂时性鸡血症记录改造wip报表查询

查询列表中主要展示字段（仅提供部分字段）的包括：

客户代码

客户订单号

配料单号

投产类型

客户型号

本厂编号

交货尺寸

下单日期

回复交期

订单数

已交货

欠货数

投产数

在线数

报废数

在制数

入仓数

客户物料号

产品类型

应用领域

工序

或部门

库存

查询需要按照明细和汇总两个维度查询

查询条件包括：

汇总模式为工序 or 部门
统计单位个数面积等
订单类型
客户代码
产品类型
产品组别
工序或部门名称
订单备注
客户型号
客户订单号
本厂编号
配料单号 ........

原程序开发语言为Delphi 数据库为SQLsever 改造为 java + mysql

原程序在实现查询中调用了存储过程，这里省略Delphi 代码只提供部分存储过程

明细查询：

CREATE procedure [dbo].[WIP_Report](  
@RptType tinyint,  
@CalcUnit tinyint,  
@SubSql varchar(500),  
@prod_status varchar(20),  
@IncludeBorder tinyint)  
--@CalcUnit:统计单位 
AS  
  
set nocount on  
      if object_ID('tempdb..#table_WIP_Report') is not null   
        drop table #table_WIP_Report   
declare   
  @Dept_Code varchar(20),  
  @Dept_Code_Out varchar(20),  
  @Qty float,  
  @Qty_Out float,  
  @Sqlstr varchar(8000),   
  @Wo_ptr int,  
  @status int,  --状态标志  
  @Manu_Part_Number varchar(30),  
  @Cut_No varchar(20),  
  @Panel_A_B tinyint  
  
  
--  @i int  
-- declare @Loc Table(i int)  
  
 declare @T Table(i int)  
 declare @s varchar(200)  
  
/*  
set @i=charindex('d56.loc_ptr=',@SubSql)  
if @i>0   
  insert into @Loc values (substring(@SubSql,@i+12,2))  
else  
  insert into @Loc select rkey from data0015  
*/  
--select * from @loc  
--return  
  
  
if @prod_status<>''  
begin  
 --print('ff')  
  set @s=replace(@prod_status,',',' union select ')  
  insert into @T exec(' select '+@s)    
end  
  
select @status=0  
  
begin tran  
   Create table #table_WIP_Report (Manu_Part_Number varchar(30),Cut_No varchar(20),Panel_A_B tinyint )  
   Create table #Flow_list_t(Dept_Code varchar(20),Dept_Code_Out varchar(25))   
--2017-6-17增加外发部门  
--1.从56表汇总，每个工序，再把所有的部门编号作为字段名称，动态创建到临时表#table_WIP_Report  
--2.查询所有作业单的在线数量，然后对临时表操作，把相应的部门数量更新到相应的字段（通过部门编号）  
  
--给临时表加上工序字段，（字段是按照工序排序）  
if @RptType = 1 ---按照工序统计  
 begin  
    select @sqlstr= 'insert into #Flow_list_t SELECT  ''[''+rtrim(D34.DEPT_CODE)+'']'' ,''[''+rtrim(D34.DEPT_CODE)+''_out''+'']''  
 FROM dbo.Data0006 D06 INNER JOIN  
    dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY   
    inner join dbo.data0008 d08 on d08.rkey = d25.PROD_CODE_PTR  
    inner join dbo.data0007 d07 on d07.rkey = d08.PR_GRP_POINTER  
    INNER JOIN  
          dbo.data0010 D10 ON D25.CUSTOMER_PTR = D10.rKey INNER JOIN  
    dbo.Data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN  
    dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY inner join   
          dbo.Data0492 D492 on D06.Cut_no=D492.Cut_no left Outer join  
          data0060 d60 on d492.so_no=d60.sales_order left join 
		  data0010 D010 on d60.ORIG_CUSTOMER=d010.ABBR_NAME
		  '+@SubSql+  
    ' group by D34.seq_nr,D34.DEPT_CODE   
  order by D34.seq_nr'  
    
    exec(@sqlstr)  
  --print @sqlstr     --EXEC WIP_Report 1,0,'where 1=1 and d06.PROD_STATUS in (2,3,4,5,6)','3,4',0
 declare Flow_list cursor for SELECT * from #Flow_list_t  
 end  
else  ---按照部门统计  
 begin  
   insert into #Flow_list_t  
 SELECT '['+rtrim(DEPT_CODE)+']' as Dept_Code , '['+rtrim(DEPT_CODE)+'_out'+']' as Dept_Code_Out  
       From data0034   
    WHERE TTYPE=5  and   
         Seq_Nr>0 and  
     rkey in (select big_dept_ptr from data0034 where TTYPE=1 group by big_dept_ptr  )  
    order by SEQ_NR  
      
    declare Flow_list cursor for SELECT * from #Flow_list_t  
end  
  
select @Sqlstr=''  
open Flow_list   
fetch next from Flow_list into @Dept_Code ,@Dept_Code_Out   
while @@FETCH_STATUS = 0  
begin  
 select @Sqlstr= @Sqlstr+' ALTER TABLE [#table_WIP_Report] ADD '+rtrim(@Dept_Code)+' [Float] Default 0 '  +char(13)+char(10)  
 --+',ADD '+rtrim(@Dept_Code_Out)+' [Float] Default 0 '+char(13)+char(10) --2017-6-12增加外发部门  
 select @Sqlstr= @Sqlstr+' ALTER TABLE [#table_WIP_Report] ADD '+rtrim(@Dept_Code_Out)+' [Float] Default 0 '+char(13)+char(10)  
   
 exec(@Sqlstr)  
 select @Sqlstr=''  
 fetch next from Flow_list into @Dept_Code ,@Dept_Code_Out    
end  
   
  
  
if @@error <> 0   
 begin   
   select @status=999  
   goto error_handle  
 end  
  
--插入需统计的记录到临时表  
select @sqlstr=  
'insert into #table_WIP_Report(Cut_No,Manu_Part_Number,Panel_A_B)   
  
SELECT TOP 100 PERCENT D492.CUT_NO, D25.MANU_PART_NUMBER, D06.PANEL_A_B  
FROM dbo.Data0006 D06 INNER JOIN  
      dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN  
      dbo.data0492 D492 ON D06.CUT_NO = D492.CUT_NO   
    inner join dbo.data0008 d08 on d08.rkey = d25.PROD_CODE_PTR  
    inner join dbo.data0007 d07 on d07.rkey = d08.PR_GRP_POINTER        
      INNER JOIN  
      dbo.data0010 D10 ON D25.CUSTOMER_PTR = D10.rKey  INNER JOIN   
      dbo.Data0056 D56 ON D06.RKEY = D56.WO_PTR inner join   
      dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY left Outer JOIN   
      dbo.data0060 D60 ON D492.So_NO = D60.sales_order left join 
		  data0010 D010 on d60.ORIG_CUSTOMER=d010.ABBR_NAME '+@SubSql+  
' GROUP BY D492.CUT_NO, D25.MANU_PART_NUMBER, D06.PANEL_A_B  
order by D492.CUT_NO, D25.MANU_PART_NUMBER, D06.PANEL_A_B'  
Exec(@Sqlstr)  
if @@error <> 0   
 begin   
  select @status=999  
  goto error_handle  
 end  
  
---为每张作业单更新相应工序的数量  
declare Data_tmp cursor for   
select Cut_No,MANU_PART_NUMBER,PANEL_A_B from #table_WIP_Report  
open Data_tmp  
fetch next from Data_tmp into @Cut_No,@MANU_PART_NUMBER,@PANEL_A_B    
while @@FETCH_STATUS = 0  
begin  
--print '更新一个产品开始: '+convert(varchar,getdate(),113)  
if @RptType = 1 ---按照工序统计  
begin  
  if @prod_status = ''  --全部状态  
  begin   
    declare Dept_Qty cursor for  
 SELECT '['+rtrim(D34.DEPT_CODE)+']' as Dept_Code,   
           case when @CalcUnit =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))   
                when @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((D56.PANELS))   
                when @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))  
                when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)    
    when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))    
                when @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) end as qty  
          , '['+rtrim(D34.DEPT_CODE)+'_out'+']' as Dept_Code_Out,  --2017-6-12 增加外发部门数量  
          case when  @CalcUnit =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG +D56.TO_BE_STOCKED  ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((case when d06.prod_status = 4 then D56.PANELS ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))  
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*D25.unit_sq)    
      when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))    
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))   
               else 0 end as qty_out  
 FROM dbo.Data0006 D06 INNER JOIN  
       dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN  
       dbo.data0492 D492 ON D06.CUT_NO = D492.CUT_NO INNER JOIN  
       dbo.data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN  
       dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY   
 Where D34.Seq_Nr > 0 and   
          D34.ttype = 1 and   
        --  d34.barcode_entry_flag = 'Y' and   
          D492.Cut_No = @Cut_No and   
          D25.MANU_PART_NUMBER = @MANU_PART_NUMBER and   
          D06.PANEL_A_B = @PANEL_A_B   
        -- and D56.loc_ptr in (select i from @Loc)  
 GROUP BY D492.CUT_NO, D25.MANU_PART_NUMBER, D06.PANEL_A_B, D34.DEPT_CODE,D34.PCS_AS_UNIT  
  end  
  else begin  --状态不为0(0为全部)  
    declare Dept_Qty cursor for  
 SELECT '['+rtrim(D34.DEPT_CODE)+']' as Dept_Code,   
           case when @CalcUnit =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))   
                when @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((D56.PANELS))   
                when @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))  
                when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)   
    when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))     
                when @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) end as qty  
          , '['+rtrim(D34.DEPT_CODE)+'_out'+']' as Dept_Code_Out,  --2017-6-12 增加外发部门数量  
          case when  @CalcUnit =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG +D56.TO_BE_STOCKED  ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((case when d06.prod_status = 4 then D56.PANELS ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))  
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*D25.unit_sq)    
      when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))    
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))   
               else 0 end as qty_out       
 FROM dbo.Data0006 D06 INNER JOIN  
       dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN  
       dbo.data0492 D492 ON D06.CUT_NO = D492.CUT_NO INNER JOIN  
       dbo.data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN  
       dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY   
 Where D34.Seq_Nr > 0 and   
          D34.ttype = 1 and   
        --  d34.barcode_entry_flag = 'Y' and   
          D492.Cut_No = @Cut_No and   
          D25.MANU_PART_NUMBER = @MANU_PART_NUMBER and   
          D06.PANEL_A_B = @PANEL_A_B and d06.prod_status in (select i from @T)  
       --   and D56.loc_ptr in (select i from @Loc)  
 GROUP BY D492.CUT_NO, D25.MANU_PART_NUMBER, D06.PANEL_A_B, D34.DEPT_CODE,D34.PCS_AS_UNIT  
   end  
 end  
else begin --按部门统计  
 if @prod_status = ''  
 begin  
  declare Dept_Qty cursor for  
  select '['+rtrim(D34_Dpt.DEPT_CODE)+']' as Dept_Code,   
         case when @CalcUnit =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))   
              when @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((D56.PANELS))   
              when @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))  
              when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)    
     when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))     
              when @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) end as qty  
          , '['+rtrim(D34_Dpt.DEPT_CODE)+'_out'+']' as Dept_Code_Out,  --2017-6-12 增加外发部门数量  
          case when  @CalcUnit =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG +D56.TO_BE_STOCKED  ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((case when d06.prod_status = 4 then D56.PANELS ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))  
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*D25.unit_sq)    
      when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))    
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))   
               else 0 end as qty_out                
  FROM dbo.Data0006 D06 INNER JOIN  
       dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN  
       dbo.data0492 D492 ON D06.CUT_NO = D492.CUT_NO INNER JOIN  
       dbo.Data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN  
       dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY inner join  
    dbo.Data0034 D34_Dpt ON D34.Big_DEPT_PTR = D34_Dpt.RKEY   
--and D56.loc_ptr in (select i from @Loc)  
  where D34_Dpt.ttype = 5 and   
       D34_Dpt.Seq_NR > 0 and  
       D492.Cut_No = @Cut_No and  
       D25.MANU_PART_NUMBER = @MANU_PART_NUMBER and  
       D06.PANEL_A_B = @PANEL_A_B   
  GROUP BY D492.CUT_NO, D25.MANU_PART_NUMBER, D06.PANEL_A_B, D34_Dpt.DEPT_CODE,D34.PCS_AS_UNIT  
 end  
 else begin  
  declare Dept_Qty cursor for  
  select '['+rtrim(D34_Dpt.DEPT_CODE)+']' as Dept_Code,   
         case when @CalcUnit =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))   
              when @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((D56.PANELS))   
              when @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED))  
              when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)    
     when @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))     
              when @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) end as qty  
          , '['+rtrim(D34_Dpt.DEPT_CODE)+'_out'+']' as Dept_Code_Out,  --2017-6-12 增加外发部门数量  
          case when  @CalcUnit =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG +D56.TO_BE_STOCKED  ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=0  then sum((case when d06.prod_status = 4 then D56.PANELS ELSE 0 END))   
               when  @CalcUnit =1 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))  
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =0 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*D25.unit_sq)    
      when  @CalcUnit =2 and D34.PCS_AS_UNIT=0 and @IncludeBorder =1 then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))    
               when  @CalcUnit =2 and D34.PCS_AS_UNIT=1  then sum((case when d06.prod_status = 4 then D56.QTY_BACKLOG+D56.TO_BE_STOCKED ELSE 0 END))   
               else 0 end as qty_out       
  FROM dbo.Data0006 D06 INNER JOIN  
       dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN  
       dbo.data0492 D492 ON D06.CUT_NO = D492.CUT_NO INNER JOIN  
       dbo.Data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN  
       dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY inner join  
    dbo.Data0034 D34_Dpt ON D34.Big_DEPT_PTR = D34_Dpt.RKEY   
  where D34_Dpt.ttype = 5 and   
       D34_Dpt.Seq_NR > 0 and  
       D492.Cut_No = @Cut_No and  
       D25.MANU_PART_NUMBER = @MANU_PART_NUMBER and  
       D06.PANEL_A_B = @PANEL_A_B and D06.PROD_STATUS in (select i from @T)  
--and D56.loc_ptr in (select i from @Loc)  
  GROUP BY D492.CUT_NO, D25.MANU_PART_NUMBER, D06.PANEL_A_B, D34_Dpt.DEPT_CODE,D34.PCS_AS_UNIT  
 end  
end  
  
  open Dept_Qty   
  fetch next from Dept_Qty into @Dept_Code,@Qty,@Dept_Code_Out,@Qty_Out  
  while @@FETCH_STATUS = 0  
  begin  
     if exists(select Dept_Code from #Flow_list_t where Dept_Code=@Dept_Code)  
     begin  
  select @Sqlstr=' update #table_WIP_Report set '+ rtrim(@Dept_Code)+' = '+convert(varchar,@Qty)+  
                 ','+ rtrim(@Dept_Code_Out)+' = '+convert(varchar,@Qty_Out)+  --2017-6-12更新外发数量  
        ' where MANU_PART_NUMBER='''+@MANU_PART_NUMBER+ ''' and '+  
        ' Cut_No='''+@Cut_NO+''' and '+  
        ' PANEL_A_B='+convert(varchar,@PANEL_A_B)  
        
  exec(@Sqlstr)  
  if @@error <> 0   
  begin   
     select @status=999  
     goto error_handle  
     end  
     end  
    --print '更新一个工序完成: '+convert(varchar,getdate(),113)  
    fetch next from Dept_Qty into @Dept_Code,@Qty,@Dept_Code_Out,@Qty_Out   
  end  
  close Dept_Qty  
  deallocate Dept_Qty  
  
if @@error <> 0   
 begin   
   select @status=999  
   goto error_handle  
 end  
  
fetch next from Data_tmp into @Cut_No,@MANU_PART_NUMBER,@PANEL_A_B    
end  
  
drop table #Flow_list_t  
  
---最后返回统计结果  
  
select distinct D10.Cust_Code,D60.sales_order,D60.reference_number,K.*,d60.sch_date,D08.Prod_Code, D08.Product_Name,  
D60.PARTS_ORDERED,D97.PO_NUmber as FOB,D283.PRIORITY_name,D60.ORIG_CUSTOMER,
D60.ENT_DATE,d07.PR_GRP_CODE,d07.PRODUCT_GROUP_NAME,d25.CPJS, d60.ANALYSIS_CODE_1 as AC1,
(D60.PARTS_SHIPPED+D60.RETURNED_SHIP) PARTS_SHIPPED ,   
(D60.PARTS_ORDERED+D60.PARTS_RETURNED-D60.PARTS_SHIPPED-D60.RETURNED_SHIP) ownQty,  
T.*, d25.Source_Part_Number,D010.CUSTOMER_GROUP, d60.PART_PRICE,

D25.MANU_PART_NUMBER AS PARTNUMBER,KuCun.TotalQuantity,CASE WHEN d25.PARENT_PTR IS NULL AND (ISNULL(KuCun.TotalQuantity,0) + ISNULL(k.Qty_OnLine,0) - ISNULL(d60.PARTS_ORDERED,0) <0) THEN ISNULL(KuCun.TotalQuantity,0) + ISNULL(k.Qty_OnLine,0) - ISNULL(d60.PARTS_ORDERED,0) ELSE 0 END AS NEEDS, 
  K.panel_ln,k.panel_wd,

CASE WHEN DATEDIFF(day, D60.sch_date, getdate())   
      < 0 THEN '未延期' ELSE '已延期' END AS Status,   
D492.ISSUE_DATE,D492.TType,D492.SCH_COMPL_DATE,D492.Issued_Qty,D492.cite_qryonhand, --2017-6-28增加引用库存pcs数 
D492.QTY_REJECT-(D492.ISSUED_QTY-D492.ORD_REQ_QTY)*0.5 AS QTYREJECT,  
'A:'+case  when D492.PANEL_1_QTY>0 then convert(varchar,D492.PANEL_1_QTY)+'X'+  
convert(varchar,D492.UPANEL1) else '' end+   
case  when D492.PANEL_2_QTY>0 then '; B:'+convert(varchar,D492.PANEL_2_QTY)+'X'+  
convert(varchar,D492.UPANEL2) else ''end as PANEL_Desc,  
D25.QTY_ON_HAND AS Qty_OnHand,D492.ORD_REQ_QTY,d25.ANALYSIS_CODE_1,  
case D492.TType when 1 then rtrim(K.Manu_Part_Number)+'(*)'   
                        else K.Manu_Part_Number end as esp_ProdName  
 from (  
SELECT D25.MANU_PART_NUMBER, D25.MANU_PART_DESC, D492.CUT_NO, D492.So_NO,D25.ANALYSIS_CODE_3,D25.ANALYSIS_CODE_2,D25.ANALYSIS_CODE_5,--2017-6-22增加客户物料号
      D06.PANEL_A_B,sum(D56.PANELS) as Qty_PNL ,  
      D06.PARTS_PER_PANEL, SUM(D06.QUAN_PROD) AS Qty_Finished,   
      sum(D06.Quan_rej) as Qty_Rej,  
      sum(D56.QTY_BACKLOG+D56.TO_BE_STOCKED) as Qty_OnLine,  
      D25.CUSTOMER_PTR,D25.PROD_CODE_PTR,  
 case when @IncludeBorder = 0 then D25.unit_sq  
   when @IncludeBorder = 1 then (d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL)  
 end as unit_sq,  D25.SAMPLE_NR
 
    ,D06.panel_ln ,D06.panel_wd		--2019-10-19增加PNL长和宽

FROM dbo.Data0006 D06 INNER JOIN  
      dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN  
      dbo.data0492 D492 ON D06.CUT_NO = D492.CUT_NO INNER JOIN  
      dbo.Data0056 D56 ON D06.RKEY = D56.WO_PTR  
--and D56.loc_ptr in (select i from @Loc)  
GROUP BY d25.PARENT_PTR,D25.MANU_PART_NUMBER, D25.MANU_PART_DESC, D492.CUT_NO,   
  D492.So_NO,D06.PANEL_A_B, D06.PARTS_PER_PANEL,D25.CUSTOMER_PTR,D25.PROD_CODE_PTR,D25.unit_sq,D25.SAMPLE_NR, D25.ANALYSIS_CODE_2,--2017-6-22增加客户物料号 
 D06.panel_ln,D06.panel_wd,D25.ANALYSIS_CODE_3,D25.ANALYSIS_CODE_5  
) K  
inner join #table_WIP_Report T on   
 T.Manu_Part_Number = K.MANU_PART_NUMBER and   
 T.Cut_No = K.Cut_No and   
 T.Panel_A_B = K.Panel_A_B  
left Outer join data0060 D60 on K.So_NO = D60.sales_order  
Inner join data0010 D10 on D10.rKey=K.CUSTOMER_PTR  
inner join data0008 D08 on D08.rKey=K.PROD_CODE_PTR  
INNER join data0007 d07 on d07.rkey = d08.PR_GRP_POINTER  
inner join data0492 D492 on D492.CUT_NO=K.CUT_NO  
left Outer join data0097 D97 on D97.rKey=D60.PURCHASE_ORDER_PTR  
inner join data0025 D25 on D25.MANU_PART_NUMBER=K.MANU_PART_NUMBER 
left Outer join data0283 D283 on D492.bom_update=D283.PRIORITY_CODE 
left join data0010 D010 on d60.ORIG_CUSTOMER=d010.ABBR_NAME
OUTER APPLY (
	SELECT SUM(ISNULL(QUAN_PROD,0)) AS TotalQuantity
	FROM data0006	K06
		INNER JOIN data0492 K492 ON K492.CUT_NO=K06.CUT_NO
	WHERE K492.CUT_NO = k.CUT_NO
) KuCun
--where D492.CUT_NO='B0117277-0'
  
if @@error <> 0   
 begin   
   select @status=999  
   goto error_handle  
 end  
  
rollback tran  
  
 error_handle:  
  if @status <> 0   
   rollback tran  
  
set nocount off  
  
 close Flow_list  
 deallocate Flow_list  
  
close Data_tmp  
deallocate Data_tmp  
  
return @status


go

汇总查询：

CREATE procedure [dbo].[WIP_Sum_Report](
@RptType tinyint,
@CalcUnit tinyint,
@SubSql varchar(500),
@prod_status varchar(20),
@IncludeBorder tinyint)
--@RptType:按工序还是班组
--@CalcUnit:统计单位： 
--@SubSql: 查询条件
--prod_status:生产状态
AS

set nocount on
if object_ID('tempdb..#table_WIP_SUM_Report') is not null 
	    drop table #table_WIP_SUM_Report 
declare @Dept_Code varchar(20),@Sqlstr varchar(8000), 
		@rKey25 int,@Qty float,@status int,
		@Manu_Part_Number varchar(30),
		@Cut_No varchar(20),@Panel_A_B tinyint
--@Dept_Code 工序代码
--@Sqlstr 查询语句
--@rKey25 
--@Qty
--@status
--@Manu_Part_Number
--@Cut_No
--@Panel_A_B

declare @T Table(i int)
declare @s varchar(200)

if @prod_status<>''
begin
  set @s=replace(@prod_status,',',' union select ')
--select a union select b
  insert into @T exec(' select '+@s)  
end

select @status=0

begin tran
   Create table #table_WIP_SUM_Report (rKey25 int PRIMARY KEY )
   Create table #Flow_list_t(Dept_Code varchar(15)) 
--1.从56表汇总，每个工序，再把所有的部门编号作为字段名称，动态创建到临时表#table_WIP_Report
--2.查询所有作业单的在线数量，然后对临时表操作，把相应的部门数量更新到相应的字段（通过部门编号）

--给临时表加上工序字段，（字段是按照工序排序）

if @RptType = 1 ---按照工序统计
begin
    select @sqlstr=	'insert into #Flow_list_t SELECT  ''[''+rtrim(D34.DEPT_CODE)+'']'' 
	FROM dbo.Data0006 D06 INNER JOIN
		  dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN
          dbo.data0010 D10 ON D25.CUSTOMER_PTR = D10.rKey 
		  inner join dbo.data0008 d08 on d08.rkey = d25.PROD_CODE_PTR
		  inner join dbo.data0007 d07 on d07.rkey = d08.PR_GRP_POINTER          
          INNER JOIN
		  dbo.Data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN
		  dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY inner join
          dbo.Data0492 D492 on D06.Cut_no=D492.Cut_no left Outer join
          data0060 d60 on d492.so_no=d60.sales_order '+@SubSql+
    ' group by D34.seq_nr,D34.DEPT_CODE
	 order by D34.seq_nr'
    exec(@sqlstr)
	declare Flow_list cursor for SELECT * from #Flow_list_t
end
else  ---按照部门统计
begin

	select @sqlstr=	'insert into #Flow_list_t SELECT ''[''+rtrim(DEPT_CODE)+'']'' as Dept_Code
       From data0034 
WHERE TTYPE=5  and 
      Seq_Nr>0 and 
rkey in (select big_dept_ptr from data0034 where TTYPE=1 group by big_dept_ptr )
    order by SEQ_NR'
    exec(@sqlstr)
	declare Flow_list cursor for SELECT * from #Flow_list_t
end

select @Sqlstr=''
open Flow_list 
fetch next from Flow_list into @Dept_Code	
while @@FETCH_STATUS = 0
begin
    select @Sqlstr= @Sqlstr+' ALTER TABLE [#table_WIP_SUM_Report] ADD '+rtrim(@Dept_Code)+' [Float] Default 0 '+char(13)+char(10)
    fetch next from Flow_list into @Dept_Code	   
end

exec(@Sqlstr)
if @@error <> 0 
 begin 
   select @status=999
   goto error_handle
 end

---print '部门字段增加完成:'+ convert(varchar,getdate(),113)

--插入需统计的记录到临时表
select @sqlstr=
'insert into #table_WIP_SUM_Report(rKey25) 

SELECT D25.rKey
FROM dbo.Data0006 D06 INNER JOIN
      dbo.Data0025 D25 ON D06.BOM_PTR = D25.RKEY INNER JOIN
      dbo.data0492 D492 ON D06.CUT_NO = D492.CUT_NO 
		  inner join dbo.data0008 d08 on d08.rkey = d25.PROD_CODE_PTR
		  inner join dbo.data0007 d07 on d07.rkey = d08.PR_GRP_POINTER      
      INNER JOIN
      dbo.data0010 D10 ON D25.CUSTOMER_PTR=D10.rKey  INNER JOIN       
      dbo.Data0056 D56 ON D06.RKEY = D56.WO_PTR inner join 
      dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY  left Outer JOIN 
      dbo.data0060 D60 ON D492.So_NO=D60.sales_order '+@SubSql+
' GROUP BY D25.rkey'

Exec(@Sqlstr)
print @@error

if @@error <> 0 
 begin 
   select @status=999
   goto error_handle
 end

--print '插入产品记录完成:'+ convert(varchar,getdate(),113)
---为每个产品更新相应工序的数量

declare Data_tmp cursor for 
select rkey25 from #table_WIP_SUM_Report
open Data_tmp
fetch next from Data_tmp into @rkey25	
while @@FETCH_STATUS = 0
begin

--print '更新一个产品开始: '+convert(varchar,getdate(),113)
if @RptType = 1 ---按照工序统计
begin
  if @prod_status = ''  --生产状态为全部(0)
  begin
    declare Dept_Qty cursor for
	SELECT '['+rtrim(D34.DEPT_CODE)+']' as Dept_Code, 
	       case  when @CalcUnit=0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) 
           when @CalcUnit=1 then sum((D56.PANELS)) 
           when @CalcUnit=2 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)
		   when @CalcUnit=2 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))  end as qty
	FROM dbo.Data0006 D06 INNER JOIN
	     dbo.data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN
	     dbo.data0025 D25 ON D06.BOM_Ptr = D25.rKey INNER JOIN
	     dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY
	Where D34.Seq_Nr>0 and D34.ttype=1 
	      and D06.BOM_PTR=@rkey25
--and D56.loc_ptr in (select i from @Loc)
	GROUP BY D34.DEPT_CODE
  end
  else begin  --生产状态不为全部
    declare Dept_Qty cursor for
	SELECT '['+rtrim(D34.DEPT_CODE)+']' as Dept_Code, 
	       case when @CalcUnit=0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) 
           when @CalcUnit=1 then sum((D56.PANELS)) 
		   when @CalcUnit=2 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)
		   when @CalcUnit=2 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))  end as qty
	FROM dbo.Data0006 D06 INNER JOIN
	     dbo.data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN
	     dbo.data0025 D25 ON D06.BOM_Ptr = D25.rKey INNER JOIN
	     dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY
	Where D34.Seq_Nr>0 and D34.ttype=1 
	      and D06.BOM_PTR=@rkey25 and D06.prod_status in (select i from @T)

	GROUP BY D34.DEPT_CODE
  end
end
else begin--按部门统计
 if @prod_status = ''
 begin
  declare Dept_Qty cursor for
  select '['+rtrim(D34_Dpt.DEPT_CODE)+']' as Dept_Code, 
         case when @CalcUnit=0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) 
         when @CalcUnit=1 then sum((D56.PANELS)) 
		 when @CalcUnit=2 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)
		 when @CalcUnit=2 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))  end as qty
  FROM dbo.Data0006 D06 INNER JOIN
       dbo.data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN
       dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY inner join
       dbo.data0025 D25 ON D06.BOM_Ptr = D25.rKey INNER JOIN
       dbo.Data0034 D34_Dpt ON D34.Big_DEPT_PTR = D34_Dpt.RKEY 
  where D34_Dpt.ttype=5 and D34_Dpt.Seq_NR>0 and D06.BOM_PTR=@rkey25
  GROUP BY D34_Dpt.DEPT_CODE
 end
 else begin
  declare Dept_Qty cursor for
  select '['+rtrim(D34_Dpt.DEPT_CODE)+']' as Dept_Code, 
         case when @CalcUnit=0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)) 
         when @CalcUnit=1 then sum((D56.PANELS)) 
		 when @CalcUnit=2 and @IncludeBorder =0 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*D25.unit_sq)
		 when @CalcUnit=2 and @IncludeBorder =1 then sum((D56.QTY_BACKLOG+D56.TO_BE_STOCKED)*(d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL))  end as qty
  FROM dbo.Data0006 D06 INNER JOIN
       dbo.data0056 D56 ON D06.RKEY = D56.WO_PTR INNER JOIN
       dbo.Data0034 D34 ON D56.DEPT_PTR = D34.RKEY inner join
       dbo.data0025 D25 ON D06.BOM_Ptr = D25.rKey INNER JOIN
       dbo.Data0034 D34_Dpt ON D34.Big_DEPT_PTR = D34_Dpt.RKEY 
  where D34_Dpt.ttype=5 and D34_Dpt.Seq_NR>0 and D06.BOM_PTR=@rkey25 and D06.PROD_STATUS in (select i from @T)
  GROUP BY D34_Dpt.DEPT_CODE
 end
end
		
 open Dept_Qty 
 fetch next from Dept_Qty into @Dept_Code,@Qty
 while @@FETCH_STATUS = 0
 begin
   if exists(select Dept_Code from #Flow_list_t where Dept_Code=@Dept_Code)
   begin
   select @Sqlstr=' update #table_WIP_SUM_Report set '+ rtrim(@Dept_Code)+' ='+convert(varchar,@Qty)+
                  ' where rKey25='+convert(varchar,@rKey25)
   exec(@Sqlstr)
   if @@error <> 0 
   begin 
     close Dept_Qty
     deallocate Dept_Qty
     select @status=999
     goto error_handle
   end
   end
   fetch next from Dept_Qty into @Dept_Code,@Qty
--	print '更新一个工序完成: '+convert(varchar,getdate(),113)
end
close Dept_Qty
deallocate Dept_Qty

	if @@error <> 0 
	 begin 
	   select @status=999
	   goto error_handle
	 end
    fetch next from Data_tmp into @rkey25	
--    print '更新一个产品完成: '+convert(varchar,getdate(),113)
end
--print '更新所有部门数量完成: '+convert(varchar,getdate(),113)

drop table #Flow_list_t

select D10.Cust_Code,D08.Prod_Code, D08.Product_Name,d07.PR_GRP_CODE,d07.PRODUCT_GROUP_NAME,d25.CPJS,K.*,T.* from (

SELECT     D25.RKEY, D25.MANU_PART_NUMBER,D25.MANU_PART_DESC, D25.CUSTOMER_PTR, D25.PROD_CODE_PTR,D25.SAMPLE_NR,D25.ANALYSIS_CODE_3, 
D25.QTY_ON_HAND AS Qty_OnHand, SUM(D492.ORD_REQ_QTY) AS PARTS_ORDERED,D25.ANALYSIS_CODE_1, 
SUM(D492.ISSUED_QTY) AS Issued_qty, SUM(D492.FG_QTY) AS Qty_Finished, 
SUM(D56.QTY_BACKLOG) AS Qty_OnLine, SUM(D492.QTY_REJECT) AS Qty_Rej,
SUM(D492.waitfor_release) AS PARTS_SHIPPED, 
case when @IncludeBorder = 0 then SUM(D25.unit_sq * D492.WIP_QTY)
	 when @IncludeBorder = 1 then SUM((d06.panel_ln*panel_wd*0.000001/PARTS_PER_PANEL) * D492.WIP_QTY)
end
 AS Qty_SQM,
sum(D56.PANELS) as Qty_PNL, --online.Qty_PNL,
sum(D492.QTY_REJECT-(D492.ISSUED_QTY-D492.ORD_REQ_QTY)*0.5) AS QTYREJECT
FROM     
  data0492 D492
  INNER JOIN data0006 D06 on D492.cut_no=D06.cut_no  inner join data0056 D56
 on D06.rkey=D56.wo_ptr  inner join data0025 D25 on D25.rkey=D06.BOM_ptr

GROUP BY D25.RKEY, D25.MANU_PART_NUMBER,D25.MANU_PART_DESC, D25.CUSTOMER_PTR, D25.PROD_CODE_PTR, D25.QTY_ON_HAND, D25.SAMPLE_NR,D25.ANALYSIS_CODE_3--,online.Qty_PNL
		,ANALYSIS_CODE_1	

) K
inner join #table_WIP_SUM_Report T on T.rKey25=K.rKey 
inner join data0025 d25 on t.rkey25 = d25.rkey
Inner join data0010 D10 on D10.rKey=K.CUSTOMER_PTR
inner join data0008 D08 on D08.rKey=K.PROD_CODE_PTR
inner join data0007 d07 on d07.rkey = d08.PR_GRP_POINTER

--print '返回查询结果完成: '+convert(varchar,getdate(),113 )

rollback tran

error_handle:
      if @status <> 0 
      begin
        print 'A'
        rollback tran
   end

set nocount off

close Flow_list
deallocate Flow_list

close Data_tmp
deallocate Data_tmp

return @status


go

注释：解读上述存储过程主要为实现功能

1.将查询条件以及部门 or 工序数量等声明为变量，并创建了两个临时表 #Flow_list_t 临时表①中存储工序或部门名称另一个主要的临时表② #table_WIP_Report 中存储业务中的唯一标识产品的本厂编号或者客户订单号将在线表中符合要求的工序部门insert进临时表

2. 打开游标，从游标中获取下一行数据，并将部门代码和部门代码输出分别存储到@Dept_Code变量中。接下来，使用while循环遍历游标中的所有数据并将其添加到@Sqlstr变量中

3.使用ALTER TABLE语句向临时表#table_WIP_Report中添加新的列，

4. 将需要统计对应的本厂编号 insert 进临时表 sql中的@SubSql 变量为拼接的查询条件

5.根据前面的本厂编号或客户订单号逐一查出对应的需要汇总的在线工序部门数量在使用update 更新临时表 table_WIP_Report匹配的工序部门名称对应的数量（ Qty）

6.在最后主查询SQL中 INNER JOIN #table_WIP_Report 临时表

解析：这里的光两个存储过程大概就达到了700行，使用存储过程的优点很明显（预编译SQL，提升了执行效率），但是缺点也是显著的（随着业务的发展，SQL的行数不断增加，维护的复杂度呈线性提升，调试起来也比较困难。且在不清楚表结构表表与表之间的关系与命名字段的含义时和业务逻辑时候，迭代时将耗费大量时间。

这一点在新系统mysql建表时得到了很好的规范表名字段名使用单词组成 + comment 注释

字段注释对于理解和维护数据库结构非常重要

改造计划：

在初步改造时，由于对业务的理解还不够透彻，加上理解前人留下的代码花费了大量的时间，时间紧任务重，为了不改变现有功能的业务逻辑，套用了原程序的模式但是摒弃了存储过程在java的业务层优化了一部分

减少了一个临时表并且条件判断采用 mybatis if-test 判断拼接查询条件减少了一大部分代码量，到这里可以说暂时告一段落。好像没什么问题但是也没有解决什么问题代码的可读性维护性简介性效率性并没有实质性的提升，原程序的模式当工序数量或者在制品的数量量大时 alter， insert， update 频繁时势必会影响到查询效率另外有这里取到的工序部门名称并不是直接展现的且字段不固定还需要改造为动态列返回给前端还需要统计总计数，库存数当出现新的需求时还是需要不断的往上堆代码。代码的可读性以及性能优化都是不能得到保障。

进一步改造：

完整改造：

1.根据需求创建明细加汇总查询导出Excel 共计四个接口

2.创建接口实现类抽取公共方法customSql 用于编写自定义的sql 便于下一步填充

3. mybatis 中编写SQL语句这里使用了group_concat函数分别将工序或部门产生的同一个分组中的值连接起来，只返回一个字符串结果

返回示例结果格式如下：

查询出工序id的 AS 别名为工序部门名称同时解决了查询列表需要展示工序部门名称的问题

4.mybatis 为我们提供了两种支持动态 sql 的语法：#{} 以及 ${}。将生成动态SQL的字符串填充进主查询的占位符中，这里推荐在能保证数据安全的情况下使用 ${ }

功能实现重点：

1.使用 SELECT IFNULL 从在线表中选择work_id和process_id列，并计算qty列的值。其中，如果D56.process_id为空，则将其替换为'total' 作为子查询内容 SELECT D56.work_id, IFNULL(process_id, 'total') AS process_id, #sum(pcs_qty ，to_be_stocked_qty) AS qty, 查询出各工单对应的工序部门数量如下图

2. 当主查询中使用 SUM(IF(process_id = 'total', qty, 0)) AS 库存

即可配合计算出（工单）库存总数量。

3. 使用with rollup 函数优化数据汇总分析进行分组求和配合 SELECT IFNULL(work_id, '总计') AS work_id, 计算出（各工序部门的）总计数量。
 提供部分演示代码
<select id="detail" resultType="java.util.Map">

       SELECT IFNULL(work_id, '总计') AS 作业单id,
       ${customSql},  
--------------------------------------------------------------------
//     ${customSql}的内容如下：即根据工序数量增加减少 实现动态SQL                 
//     SUM(IF(process_id = 133, qty, 0))     AS `包装`,
       SUM(IF(process_id = 156, qty, 0))     AS `质检`,
       SUM(IF(process_id = 1973, qty, 0))    AS `其他`,
       SUM(IF(process_id = 817, qty, 0))     AS `入库`,
--------------------------------------------------------------------
       SUM(IF(process_id = 'total', qty, 0)) AS 库存,
       A.*

        FROM ( SELECT D56.work_id,
        IFNULL(D56.process_id, 'total') AS process_id,
        sum((D56.pcs_qty + D56.to_be_stocked_qty)) AS qty,

        D06.work_order_no,
        D06.product_id,
        D492.work_no,
        D25.customer_code,
        D60.original_customer_code,
        D60.bill_no,
        D60.remark,
        D25.customer_number,
        D25.manu_part_number,
        D25.test_remark,
        D25.unit_sq,
        D06.issued_date,
        D60.create_time,
        D492.wait_plan_qty,
        D492.issued_qty,
        D56.panel_qty,
        D492.scrap_qty,
        D25.qty_on_hand,
        D492.fg_qty,
        apply.name as apply_name,
        D08.name as type_name,
        D07.name as group_name,
        D25.customer_materiel_no,
        D25.customer_short_name,
        D25.out_material_no,D06.pnl_size
        FROM prod_work_online D56
        INNER JOIN eng_prod_process D34 ON D34.id = D56.process_id
        INNER JOIN plan_work_detail D06 ON D06.id = work_id
        INNER JOIN base_product D25 on D06.product_id = D25.id
        INNER JOIN base_product_type D08 ON D08.id = D25.product_type_id
        INNER JOIN base_product_group D07 ON D07.id = D08.group_id
        LEFT JOIN base_product_applyarea apply on D25.applyarea_id = apply.id
        INNER JOIN plan_burden_sheet D492 on D06.burden_id = D492.id
        LEFT JOIN sell_bill D60 on D60.id = D492.sell_order_id
        group by D56.work_id, D56.process_id
        with rollup
        having D56.work_id is not null) AS A
        group by work_id
        with rollup;

在IDEA中，springboot + mybatis 配置了日志打印下面看一下启动后台调用接口打印日志中的SQL

为了更直观的展示效果，把执行的SQL语句复制一下使用DataGrip执行一下 DataGrip支持将查询的数据结果导出各种格式文件：

为使效果更直观展示出部分Excel数据（下图：

lvchunhu_

关注

21
点赞
踩
15

收藏

觉得还不错? 一键收藏
1
评论
WIP报表查询

ERP中WIP的意思即为Work In Process，也就是在制品的意思。在制品指的是正在加工，尚未完成的产品。广义的包括正在加工的产品和准备进一步加工的半成品;狭义的仅指正在加工的产品，合理地控制在制品、半成品的储备量，做好保管工作，使它们不受损坏，可以保证产品质量，节约流动资金，缩短生产周期，减少和避免积压。年前暂时性鸡血症记录改造wip报表查询客户代码客户订单号配料单号投产类型客户型号本厂编号交货尺寸下单日期回复交期订单数已交货欠货数投产数在线数报废数在制数。
复制链接

扫一扫