sfc_mnrequest_b.pas

一个MRPII系统源代码版本

           '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.BomremArk,'+
           't.BillLineRemArk,'+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.status,'+
           't.Batchno,'+
           'null as onhandInv, '+
           'null as onCheckInv '+
       'from #tmp1 t,Item i,Uom u '+
       ' where t.ItemCode=i.ItemCode '+
       '   and i.UomCode=u.UomCode '+
       '   and t.status1 is not null  '+
       ' union '+
       //选出替代虚项
       ' select t.mono,'+
           't.MoLineno,'+
           't.alterNative,'+
           't.ItemListid,'+
           't.Parentid, '+
           't.ItemCode,'+
           '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.BomremArk,'+
           'T.BillLineRemArk,'+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.status,'+
           't.Batchno,'+
           'null as onhandInv,'+
           'null as onCheckInv '+
       'from #tmp1 t,Item i,Uom u '+
       ' where t.ItemCode=i.ItemCode '+
       '   and i.UomCode=u.UomCode '+
       '   and t.alterNative=1 '+
       '   and t.moCtrlqty>0 '+
       ' ) a '+
       ' Order by a.status,a.ItemCode '+
       ' drop table #tmp2 '+
       ' drop table #tmp1 ';
  end
  else
  begin
    sql_text:=
      'set noCount on '+
      ' create table #tmp1(mono varchAr(16),MoLineno int ,ItemCode varchAr(16) ,'+
                          'ite_ItemCode varchAr(16) null,BomremArk varchAr(250) 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)) '+
      ' insert #tmp1(mono,MoLineno,ItemCode,ite_ItemCode,BomremArk,BillLineRemArk,moCtrlqty,MoRealqty,moRequestqty,alterNative,ItemListid,Parentid) '+
      ' select m.mono,'+
           'm.MoLineno,'+
           'm.ItemCode,'+
           'm.ite_ItemCode,'+
           'm.BomremArk,'+
           '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=0 '+
         '   and m.moCtrlqty>0  '+
             tmp_DeptCode+
        //把代换项加入到临时表中
      ' insert #tmp1(mono,MoLineno,ItemCode,ite_ItemCode,BomremArk,BillLineRemArk,moCtrlqty,MoRealqty,moRequestqty,alterNative,ItemListid,Parentid) '+
      ' select m.mono,'+
           'm.MoLineno,'+
           'm.ItemCode,'+
           'm.ite_ItemCode,'+
           'm.BomremArk,'+
           '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 * from ( '+
      ' select t.mono,'+
           't.MoLineno,'+
           't.ItemListid,'+
           't.Parentid,'+
           't.ItemCode,'+
           '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.BomremArk,'+
           't.BillLineRemArk,'+
           'round(t.moCtrlqty,0) as moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.alterNative,'+
           'isnull(c.'+tmpfields+',0) as onhandInv,'+
           'isnull(c1.onCheckInv,0) 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 '+
       ' 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+''''+
       ' left join Employee e on i.wh_EmployeeCode=e.EmployeeCode '+
       ' where 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.mono,'+
           't.MoLineno,'+
           't.ItemListid,'+
           't.Parentid,'+
           't.ItemCode,'+
           '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.BomremArk,'+
           't.BillLineRemArk,'+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.alterNative,'+
           'isnull(c.'+tmpfields+',0) as onhandInv,'+
           'isnull(c1.onCheckInv,0) 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 '+
       ' 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 t.status1 is not null or alterNative=1'+
       ') a'+
       ' Order by a.status '+
       ' drop table #tmp2 '+
       ' drop table #tmp1 ';
  end;

  try
    with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('drop table #tmp1');
      //execsql;
    end;
  except
  end;

  try
    with AdoQry_Body do
    begin
      Close;
      sql.clear;
      sql.Add(sql_text);
      open;
    end;
  except
    begin
      DispInfo('连接失败，请重试！',3);
    end
  end;

//下面取所有是替代件的
  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,BomremArk varchAr(250) 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,BomremArk,BillLineremArk,moCtrlqty,'+
                       'MoRealqty,moRequestqty,alterNative,ItemListid,Parentid,'+
                       'Bomqty,BomScrAp_Percent) '+
        ' select m.mono,'+
             'm.MoLineno,'+
             'm.ItemCode,'+
             'm.ite_ItemCode,'+
             'm.BomremArk,'+
             '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.BomremArk,'+
           '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_B.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;
    end
    else
      pnl_Hint.Visible :=False;
  end;
  //控制保存后届面不变SHOWFLAG是基类的参数
  if (showflag=True) and (status<>'Add') then
     exit;
  checkbox1.Checked :=False;