sfc_mnrequest_b.pas

一个MRPII系统源代码版本

unit Sfc_MnRequest_B;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Entry_Body, Db, DBCtrls, StdCtrls, ActnList, AdODB, Grids, DBGridEh,
  ExtCtrls, ComCtrls, ToolWin, ExtPrintReport, ExtEdit, Mask,dbgrids, jpeg,variants;

Type
  TFrm_Sfc_MnRequest_B = Class(TFrm_Base_Entry_Body)
    Extpr: TExtPrintReport;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    cmbbx_WhCode: TComboBox;
    cmbbx_WhPositionCode: TComboBox;
    lbl_Dept: TLabel;
    medt_Date: TMaskEdit;
    lbl_Mono: TLabel;
    lbl_MoLineno: TLabel;
    lbl_Item: TLabel;
    Extedt_Billno: TExtEdit;
    Extedt_memo: TExtEdit;
    CheckBox1: TCheckBox;
    cmbbx_WhEmployee: TComboBox;
    Label7: TLabel;
    CheckBox2: TCheckBox;
    cmbbx_Shift: TComboBox;
    Panel1: TPanel;
    Label13: TLabel;
    DBText2: TDBText;
    DBGridEh1: TDBGridEh;
    AdoQry_SwAp: TAdoQuery;
    DataSource1: TDataSource;
    Panel2: TPanel;
    DBText1: TDBText;
    Label8: TLabel;
    Label14: TLabel;
    lbl_qty: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    lbl_Noqty: TLabel;
    CheckBox3: TCheckBox;
    Label17: TLabel;
    cmbx_WipWaster: TComboBox;
    procedure DBGridEhKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure AdoQry_BodyBeforePost(DataSet: TDataSet);
    procedure DBGridEhColExit(Sender: TObject);
    procedure Act_autoExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender:TObject);
    procedure Act_ExcelExecute(Sender: TObject);
    procedure AdoQry_BodyAfterPost(DataSet: TDataSet);
    procedure cmbbx_WhPositionCodeExit(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure cmbbx_WhCodeExit(Sender: TObject);
    procedure Act_NewExecute(Sender: TObject);
    procedure DBGridEhExit(Sender: TObject);
    procedure Extedt_BillnoChange(Sender: TObject);
    procedure medt_DateChange(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure cmbbx_WhCodeChange(Sender: TObject);
    procedure cmbbx_WhPositionCodeChange(Sender: TObject);
    procedure Act_SaveExecute(Sender: TObject);
    procedure DateCheck(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure cmbbx_WhEmployeeExit(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure AdoQry_BodyAfterInsert(DataSet: TDataSet);
    procedure AdoQry_SwApBeforePost(DataSet: TDataSet);
    procedure AdoQry_SwApAfterInsert(DataSet: TDataSet);
    procedure AdoQry_SwApAfterPost(DataSet: TDataSet);
    procedure AdoQry_SwApBeforeEdit(DataSet: TDataSet);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure DBGridEh1GetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure DBGridEh1Exit(Sender: TObject);
    procedure AdoQry_BodyBeforeEdit(DataSet: TDataSet);
    procedure DBGridEh1ColExit(Sender: TObject);
    procedure DBGridEh1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure CheckBox3Click(Sender: TObject);
    procedure CheckBox3Exit(Sender: TObject);
    procedure cmbx_WipWasterChange(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
    IOType,oldmoRequestlineqty,oldswApmoRequestqty,oldbodymoRequestqty:real;
    swApflag,MoLinestatus:integer;
    bodyItemListid,BillTypeCode,oldwhCode,oldWhPosition,oldEmployee:string;
    IsAfterprint:boolean;
    WipWaster:integer;
    procedure checkbodyIOType;
    procedure checkswApIOType;
    procedure checkcoCurrentqty;
    procedure updatebodymoRequestqty;
    procedure checkswAp;
    procedure InitCmbbx_WhPositionCode(whCode:string);
    procedure InitCmbbx_WhEmployee(whCode:string);
    procedure showdbgrid;
    procedure initprint;
    procedure updateswApmoRequestqty;
    { Private declarations }
  public
    tmp_Status:string;
    procedure SetStatus(CurrentStatus:String;var AnswerStatus,EnableControls:String); Override;//设置各种状态下那些控件Enable
    procedure InitControls; Override;//初始化Form上所有控件
    procedure SaveData; Override;
      { Public declarations }
  end;

var
  Frm_Sfc_MnRequest_B: TFrm_Sfc_MnRequest_B;

implementation

uses Sys_Global, Sfc_MnRequest,Inv_Global,Sfc_MnOut_NotSave_P, Sfc_MnOut_P,
  Sfc_MnOut_NotSave_P1, Sfc_MnOut_P1;
{$R *.DFM}

procedure TFrm_Sfc_MnRequest_B.InitCmbbx_WhPositionCode(whCode:string);
begin
  //初始化货位，根据当前传入的仓库号码，选出不是待检货位及不是拉式货位的货位
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select WhPositionCode,WhPositionName '+
              ' from WhPosition  '+
              ' where whCode='''+whCode+''''+
              ' and WhPositionType<>1 '+
              ' and BackFlushWhP=0 '+
              ' Order by WhPositionCode ');
    open;
    cmbbx_WhPositionCode.clear;
    if not Eof then
    begin
      First;
      while not Eof do
      begin
        cmbbx_WhPositionCode.Items.Add(fieldbyname('WhPositionCode').asstring+' '+fieldbyname('WhPositionName').asstring);
        Next;
      end;
    end;
  end;
  cmbbx_WhPositionCode.Itemindex:=0;
end;

procedure TFrm_Sfc_MnRequest_B.InitCmbbx_WhEmployee(whCode:string);
begin
  //初始化仓管员，从物料主文件中选出当前仓库的缺省仓管员，可以为空
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select distinct i.wh_EmployeeCode,e.EmployeeName '+
            '  from Item i,Employee e '+
            '  where whCode='''+whCode+''''+
            '    and i.wh_EmployeeCode is not null '+
            '    and i.wh_EmployeeCode=e.EmployeeCode '+
            ' Order by i.wh_EmployeeCode');
    open;
    cmbbx_WhEmployee.clear;
    cmbbx_WhEmployee.Items.Add('');
    if not Eof then
    begin
      First;
      while not Eof do
      begin
        cmbbx_WhEmployee.Items.Add(fieldbyname('wh_EmployeeCode').asstring+' '+fieldbyname('EmployeeName').asstring);
        Next;
      end;
      cmbbx_WhEmployee.Itemindex:=0;
    end;
  end;
end;



procedure  TFrm_Sfc_MnRequest_B.Showdbgrid;
var
  sql_text,tmp_WhEmployee,tmp_DeptCode,tmpfields,oncheckpositionCode:string;
begin
   //取待检货位的代码
   with AdoQry_tmp do
   begin
     Close;
     sql.text:='select WhPositionCode from WhPosition where WhPositionType=1 '+
               'and whCode='+QuotedStr(getCode(cmbbx_WhCode.text));
     open;
     oncheckpositionCode:=fieldbyname('WhPositionCode').asstring;
   end;
  //取数据显示于DBGRID

  //根据仓库代码，货位代码决定选出数据表中的英文字段名，用于下面的SQL语名组合
  tmpfields:=getInvfield(AdoQry_tmp,getCode(cmbbx_WhCode.text),getCode(cmbbx_WhPositionCode.text),'CurrentInv');
  //如果仓库管理员为空或不为空情况下的SQL语名中仓管员条件组合
  if cmbbx_WhEmployee.text<>'' then
    tmp_WhEmployee:='   and i.wh_EmployeeCode='''+getCode(cmbbx_WhEmployee.text)+''''
  else
    tmp_WhEmployee:='';
   //按部门过滤条件的组合
  if checkbox2.Checked then
     tmp_DeptCode:=' and  (m.DeptCode in ('''+getCode(lbl_Dept.Caption)+''','''+copy(getCode(lbl_Dept.Caption),1,length(getCode(lbl_Dept.Caption))-2)+''')) '
  else
     tmp_DeptCode:='';

 {
   数据的取法：
   1。创建一个存放数据的临时表
   2。从生产计划中的MNItemList中取出满足条件的记录，存放在临时表中
      先取不是替代件的记录，后取替代虚项，而实际的替代件放在第二DBGRID中显示。
   3。由于取出来的数据要根据父，子代码在DBGRID中显示结构虚项及替代虚项，因此用CUOSOR
      对数据进行构造，只要是对两个状态标志字段STATUS，STATUS1进行付值。
        STATUS   STATUS1
         A          A
         A1         A
         A1         A
         B          B
         B1         B
 这样出来后，Order BY 后就得到显示的结果，用红色字体显示结构虚项
 蓝色字体显示替代虚项
 }

  //显示库存不为零的数据
  if checkbox1.checked =True then
  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,'+
      ' 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),BillLineRemArk varchAr(250)) '+
      ' 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 ite_ItemCode<>'''+getCode(lbl_Item.Caption)+''''+
      '    and alterNative=0 '+
      //做一个CURSOR，对子物料代码字段中父物料代码作status,status1字段标志
      '  select distinct ite_ItemCode into #tmp2 '+
      '   from #tmp1 '+
      '   where  ite_ItemCode<>'''''+
      '    and ite_ItemCode<>'''+getCode(lbl_Item.Caption)+''''+
      '    and alterNative=0 '+
      '  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.alterNative,'+
           '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,'+
           'e.EmployeeName as wh_EmployeeCode,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BomremArk,'+
           't.BillLineRemArk,'+
           'round(t.moCtrlqty,0) as moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.status,'+
           't.Batchno,'+
           'c.'+tmpfields+' as onhandInv, '+
           'c1.onCheckInv as onCheckInv '+
       'from #tmp1 t '+
       ' join Item i on t.ItemCode=i.ItemCode '+
       tmp_WhEmployee+
       ' join Uom u on i.UomCode=u.UomCode '+
       ' left join Employee e on i.wh_EmployeeCode=e.EmployeeCode '+
       ' join CurrentInv c on t.ItemCode=c.ItemCode '+
       '      and c.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '      and c.WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
       '   and c.'+tmpfields+'>0 '+
       ' Left join CurrentInv c1 on t.ItemCode=c1.ItemCode '+
       '      and c1.whCode='''+getCode(cmbbx_WhCode.text)+''''+
       '      and c1.WhPositionCode='''+oncheckpositionCode+''''+
       ' where t.alterNative<>1 '+
       '   and (i.whCode='''+getCode(cmbbx_WhCode.text)+''' or '+
       '       c.'+tmpfields+'>0) '+
       ' union '+
       // 结构虚项
       ' select t.mono,'+
           't.MoLineno,'+
           't.alterNative,'+
           't.ItemListid,'+
           't.Parentid, '+
           't.ItemCode,'+
           't.ite_ItemCode,'+