sfc_mnrequest_b1.pas

一个MRPII系统源代码版本

       '      on t.ItemCode=c.ItemCode '+
       '     and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '     and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
       ' join CurrentInv c1 on t.ItemCode=c1.ItemCode '+
       '      and c1.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '      and c1.WhPositionCode='''+oncheckpositionCode+''''+
       ' left join Employee e on i.wh_EmployeeCode=e.EmployeeCode '+
       ' where alterNative=0'+//t.status1 is null and alterNative=0 '+
       '  and ((i.onlydefaultwh=1 and i.whCode='''+getCode(cmbbx_WhCode.text)+''') or '+
       ' (i.onlydefaultwh=0)) '+
 //      '   and (i.whCode='''+getCode(cmbbx_WhCode.text)+''' or '+
       '   and    c.'+tmpfields+'>0 '+
       ' union '+
       //虚项不受条件限制
      ' select t.Bomlevel,t.mono,'+
           't.MoLineno,'+
           't.ItemListid,'+
           't.Parentid,'+
           't.ItemCode,'+
           'space(t.Bomlevel*2)+t.ItemCode as tmpItemCode,'+
           't.ite_ItemCode,'+
{           'case '+
            ' when (status1 is not null and alterNative is null) then ''+''+t.ItemCode '+
           '  when (status1 is null and alterNative=1) then ''-''+t.ItemCode '+
           ' else t.ItemCode end  tmpItemCode,'+}
           'i.ItemName,'+
           'null as wh_EmployeeCode,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BilllineremArk,'+
           't.Bomqty, '+
           't.BomScrAp_Percent, '+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.alterNative,'+
           'isnull(c.'+tmpfields+',0.00) as onhandInv,'+
           'isnull(c1.onCheckInv,0.00) as onCheckInv, '+
           't.Batchno '+
//           't.status '+
       'from #tmp1 t'+
       ' join Item i '+
       '      on  t.ItemCode=i.ItemCode '+
       ' left join Uom u '+
       '      on i.UomCode=u.UomCode '+
       '  join CurrentInv c '+
       '      on t.ItemCode=c.ItemCode '+
       '     and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '     and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
       '  join CurrentInv c1 on t.ItemCode=c1.ItemCode '+
       '      and c1.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '      and c1.WhPositionCode='''+oncheckpositionCode+''''+
       ' where  alterNative in (1,2) '+
       ') a'+
       ' Order by a.ItemListid '+
       ' drop table #tmp1 ';
  end
  else
  begin
    sql_text:=
      'set noCount on '+
      ' create table #tmp1(Bomlevel int,mono varchAr(16),MoLineno int ,ItemCode varchAr(16) ,'+
                          'ite_ItemCode varchAr(16) null,BilllineremArk varchAr(250) null,'+
                          'Bomqty float null,BomScrAp_Percent float null,moCtrlqty float null,'+
                          'MoRealqty float null,moRequestqty float null,alterNative int,'+
                          'ItemListid int,Parentid int,Batchno varchAr(16)) '+
      ' insert #tmp1(Bomlevel,mono,MoLineno,ItemCode,ite_ItemCode,BilllineremArk,Bomqty,BomScrAp_Percent,moCtrlqty,MoRealqty,moRequestqty,alterNative,ItemListid,Parentid) '+
      ' select case when m.Parentid=0 then m.Parentid else -1 end ,m.mono,'+
           'm.MoLineno,'+
           'm.ItemCode,'+
           'm.ite_ItemCode,'+
           'm.BilllineremArk,'+
           'b.Bomqty, '+
           'b.BomScrAp_Percent,'+
           'm.moCtrlqty,'+
           'm.MoRealqty,'+
           'm.moRequestqty,'+
           'm.alterNative,'+
           'm.ItemListid,'+
           'm.Parentid '+
      ' from mnItemList m '+
      '  join Bom b on m.ite_ItemCode=b.ite_ItemCode and m.ItemCode=b.ItemCode '+
      ' where m.mono='''+lbl_Mono.Caption+''' '+
         '   and m.MoLineno='+lbl_MoLineno.Caption+
        //'   and m.alterNative=0 '+
//       '   and m.moCtrlqty>0  '+
             tmp_DeptCode+
      ' while exists(select Bomlevel from #tmp1 where Bomlevel<0) '+
      '   begin  ' +
      '      update #tmp1  '   +
      '      set Bomlevel=tt.Bomlevel+1   '  +
      '      from #tmp1,#tmp1 tt          '  +
      '      where #tmp1.ite_ItemCode=tt.ItemCode '  +
      '                      and tt.Bomlevel>=0    '  +
      '   end  '    +
      
        //把代换项加入到临时表中
{      ' insert #tmp1(mono,MoLineno,ItemCode,ite_ItemCode,BilllineremArk,moCtrlqty,MoRealqty,moRequestqty,alterNative,ItemListid,Parentid) '+
      ' select m.mono,'+
           'm.MoLineno,'+
           'm.ItemCode,'+
           'm.ite_ItemCode,'+
           'm.BilllineremArk,'+
           'm.moCtrlqty,'+
           'm.MoRealqty,'+
           'm.moRequestqty,'+
           'm.alterNative,'+
           'm.ItemListid,'+
           'm.Parentid'+
      ' from mnItemList m '+
      ' where m.mono='''+lbl_Mono.Caption+''' '+
         '   and m.MoLineno='+lbl_MoLineno.Caption+
         '   and m.alterNative=1 '+
         '   and m.moCtrlqty>0  '+
             tmp_DeptCode+
      ' insert #tmp1(mono,MoLineno,ItemCode) '+
      '  select distinct mono,MoLineno,ite_ItemCode from #tmp1 '+
      '  where ite_ItemCode<>'''''+
      '    and alterNative=0 '+
      '    and ite_ItemCode<>'''+getCode(lbl_Item.Caption)+''''+
      '  select distinct ite_ItemCode into #tmp2 '+
      '    from #tmp1 '+
      '    where ite_ItemCode<>'''''+
      '      and alterNative=0 '+
      '      and ite_ItemCode<>'''+getCode(lbl_Item.Caption)+''''+
      '  declAre Item_Cursor cursor for select ite_ItemCode from #tmp2 '+
      ' open Item_Cursor '+
      ' declAre @tmp_ItemCode varchAr(16) '+
      ' declAre @m int '+
      ' set @m=65 '+
      '  fetch Item_Cursor into @tmp_ItemCode '+
      ' while @@fetch_Status=0 '+
      ' begin '+
      '   update #tmp1 set status=chAr(@m),status1=chAr(@m) where ItemCode=@tmp_ItemCode '+
      '   update #tmp1 set status=chAr(@m)+''1'' where ite_ItemCode=@tmp_ItemCode '+
      '   set @m=@m+1 '+
      '   fetch Item_Cursor into @tmp_ItemCode '+
      ' end '+
      ' Close Item_Cursor '+
      ' deallocate Item_Cursor '+}
      ' select * into #Sfcmo from ( '+
      ' select t.Bomlevel,t.mono,'+
           't.MoLineno,'+
           't.ItemListid,'+
           't.Parentid,'+
           't.ItemCode,'+
           'space(t.Bomlevel*2)+t.ItemCode as tmpItemCode,'+
           't.ite_ItemCode,'+
{           'case '+
           '  when (status1 is not null and alterNative=0) then ''+''+t.ItemCode '+
           '  when (status1 is null and alterNative=1) then ''-''+t.ItemCode '+
           ' else t.ItemCode end  tmpItemCode,'+}
           'i.ItemName,'+
           'i.wh_EmployeeCode+'+''' '''+'+e.EmployeeName as wh_EmployeeCode,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BilllineremArk,'+
           't.Bomqty, '+
           't.BomScrAp_Percent, '+
           'round(t.moCtrlqty,0) as moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.alterNative,'+
           'isnull(c.'+tmpfields+',0.00) as onhandInv,'+
           'isnull(c1.onCheckInv,0.00) as onCheckInv,'+
           't.Batchno '+
//           't.status '+
       'from #tmp1 t'+
       ' join Item i '+
       '      on  t.ItemCode=i.ItemCode '+
     //  '   and i.whCode='''+getCode(cmbbx_WhCode.text)+''' '+
       tmp_WhEmployee+
       ' left join Uom u '+
       '      on i.UomCode=u.UomCode '+
       '  join CurrentInv c '+
       '      on t.ItemCode=c.ItemCode '+
       '     and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '     and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
       '  join CurrentInv c1 on t.ItemCode=c1.ItemCode '+
       '      and c1.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '      and c1.WhPositionCode='''+oncheckpositionCode+''''+
       ' left join Employee e on i.wh_EmployeeCode=e.EmployeeCode '+
       ' where alterNative=0'+//t.status1 is null and alterNative=0 '+
       '  and ((i.onlydefaultwh=1 and i.whCode='''+getCode(cmbbx_WhCode.text)+''') or '+
       ' (i.onlydefaultwh=0)) '+
 //      '   and (i.whCode='''+getCode(cmbbx_WhCode.text)+''' or '+
 //      '       c.'+tmpfields+'>0) '+
       ' union '+
       //虚项不受条件限制
      ' select t.Bomlevel,t.mono,'+
           't.MoLineno,'+
           't.ItemListid,'+
           't.Parentid,'+
           't.ItemCode,'+
           'space(t.Bomlevel*2)+t.ItemCode as tmpItemCode,'+
           't.ite_ItemCode,'+
{           'case '+
            ' when (status1 is not null and alterNative is null) then ''+''+t.ItemCode '+
           '  when (status1 is null and alterNative=1) then ''-''+t.ItemCode '+
           ' else t.ItemCode end  tmpItemCode,'+}
           'i.ItemName,'+
           'null as wh_EmployeeCode,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BilllineremArk,'+
           't.Bomqty, '+
           't.BomScrAp_Percent, '+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.alterNative,'+
           'isnull(c.'+tmpfields+',0.00) as onhandInv,'+
           'isnull(c1.onCheckInv,0.00) as onCheckInv, '+
           't.Batchno '+
//           't.status '+
       'from #tmp1 t'+
       ' join Item i '+
       '      on  t.ItemCode=i.ItemCode '+
       ' left join Uom u '+
       '      on i.UomCode=u.UomCode '+
       '  join CurrentInv c '+
       '      on t.ItemCode=c.ItemCode '+
       '     and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '     and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
       '  join CurrentInv c1 on t.ItemCode=c1.ItemCode '+
       '      and c1.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '      and c1.WhPositionCode='''+oncheckpositionCode+''''+
       ' where  alterNative in (1,2)'+
       ') a'+
       ' Order by a.ItemListid '+
       ' drop table #tmp1 ';
  end;

  try
    Executesql(AdoQry_tmp,'drop table #tmp1',1);
  except
  end;

  try
    Executesql(AdoQry_Body,sql_text,1);
  except
    begin
      DispInfo('连接失败，请重试！',3);
    end
  end;

  Executesql(AdoQry_Body,'select * from #Sfcmo',0);

//下面取所有是替代件的
{  with AdoQry_SwAp do
  begin
    Close;
    sql.clear;
      sql.text:=
        'set noCount on '+
        ' create table #tmp3(mono varchAr(16),MoLineno int ,ItemCode varchAr(16) ,'+
                            'ite_ItemCode varchAr(16) null,BilllineremArk varchAr(250) null,'+
                            'status varchAr(2) null ,status1 varchAr(1) null,moCtrlqty float null,'+
                            'MoRealqty float null,moRequestqty float null,alterNative int,'+
                            'ItemListid int,Parentid int,Batchno varchAr(16),Bomqty float,BomScrAp_Percent float) '+
        ' insert #tmp3(mono,MoLineno,ItemCode,ite_ItemCode,BilllineremArk,moCtrlqty,'+
                       'MoRealqty,moRequestqty,alterNative,ItemListid,Parentid,'+
                       'Bomqty,BomScrAp_Percent) '+
        ' select m.mono,'+
             'm.MoLineno,'+
             'm.ItemCode,'+
             'm.ite_ItemCode,'+
             'm.BilllineremArk,'+
             'm.moCtrlqty,'+
             'm.MoRealqty,'+
             'm.moRequestqty,'+
             'm.alterNative,'+
             'm.ItemListid,'+
             'm.Parentid,'+
             'm.Bomqty,'+
             'm.BomScrAp_Percent '+
        ' from mnItemList m '+
        ' where m.mono='''+lbl_Mono.Caption+''' '+
        '   and m.MoLineno='+lbl_MoLineno.Caption+
        '   and m.moCtrlqty=0  '+
        '   and m.alterNative=1 '+
        tmp_DeptCode+
        ' select t.mono,'+
           't.MoLineno,'+
           't.ItemListid,'+
           't.Parentid,'+
           't.alterNative,'+
           't.ItemCode,'+
           't.ite_ItemCode,'+
           't.ItemCode as tmpItemCode,'+
           'i.ItemName,'+
           'i.wh_EmployeeCode+'+''' '''+'+e.EmployeeName as wh_EmployeeCode,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BilllineremArk,'+
           'round(t.moCtrlqty,0) as moCtrlqty,'+
           't.MoRealqty,'+
           't.Bomqty,'+
           't.BomScrAp_Percent,'+
           't.moRequestqty,'+
           't.Batchno,'+
           'isnull(c.'+tmpfields+',0) as onhandInv, '+
           'isnull(c1.onCheckInv,0) as onCheckInv '+
       'from #tmp3 t'+
       '   join Item i '+
       '      on t.ItemCode=i.ItemCode '+
       tmp_WhEmployee+
       ' left join Uom u '+
       '      on i.UomCode=u.UomCode '+
       ' left join Employee e on i.wh_EmployeeCode=e.EmployeeCode '+
       ' left join CurrentInv c '+
       '      on t.ItemCode=c.ItemCode '+
       '     and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '     and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
       ' left join CurrentInv c1 on t.ItemCode=c1.ItemCode '+
       '      and c1.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '      and c1.WhPositionCode='''+oncheckpositionCode+''''+
       ' where (i.whCode='''+getCode(cmbbx_WhCode.text)+''' or '+
       '       c.'+tmpfields+'>0) '+
       ' Order by t.ItemCode '+
       ' drop table #tmp3 ';
    open;
  end;  }
end;

procedure TFrm_Sfc_MnRequest_B1.InitControls;
begin//初始化Form上的控件
  inherited;
  //说明：用tmp_Status变量控制这个窗体, =notChange 为窗体刚进入
  //是 =Change cmbbx_WhPositionCode 改变的状态，要根据货位的改变而刷新GRID
  AdoQry_SwAp.Connection:=AdoQry_Body.Connection;
  with DBGridEh do
  begin
    options:=options+[dgEditing]-[dgRowselect];
  end;
{  with DBGridEh1 do
  begin
    options:=options+[dgEditing]-[dgRowselect];
  end; }
  cmbx_WipWaster.ItemIndex :=0;
  //判断是否存在替代项，如果有，显示pnl_Hint，用于显示替代件
{  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.text:=
      'select Count(*) as record  from mnItemList'+
      ' where mono='''+AdoQry_Head.fieldbyname('mono').asstring+''''+
      '   and MoLineno='+AdoQry_Head.fieldbyname('MoLineno').asstring+
      '   and alterNative=1 ';
    open;
    swApflag:=fieldbyname('record').asinteger;
    if swApflag>0 then
    begin
      pnl_Hint.Visible :=True;