sfc_mnrequestoverplan_b.pas

一个MRPII系统源代码版本

unit Sfc_MnRequestOverPlan_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, jpeg;

Type
  TFrm_Sfc_MnRequestOverPlan_B = Class(TFrm_Base_Entry_Body)
    DBText1: TDBText;
    Label8: TLabel;
    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;
    CheckBox2: TCheckBox;
    cmbbx_WhEmployee: TComboBox;
    Label7: TLabel;
    cmbbx_Shift: TComboBox;
    Label13: TLabel;
    lbl_qty: TLabel;
    Label14: TLabel;
    lbl_Noqty: TLabel;
    Label17: TLabel;
    cmbx_WipWaster: TComboBox;
    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 cmbbx_WhCodeChange(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure cmbbx_WhCodeExit(Sender: TObject);
    procedure Act_NewExecute(Sender: TObject);
    procedure Act_InsertLineExecute(Sender: TObject);
    procedure Act_ModifyExecute(Sender: TObject);
    procedure Act_DeleteLineExecute(Sender: TObject);
    procedure AdoQry_BodyAfterPost(DataSet: TDataSet);
    procedure DateCheck(Sender: TObject);
    procedure cmbbx_WhPositionCodeExit(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure cmbbx_WhEmployeeExit(Sender: TObject);
    procedure cmbx_WipWasterChange(Sender: TObject);
  private
    IOType,oldmoRequestlineqty:real;
    BillTypeCode,oldWhPosition:string;
    Isinsert,IsAfterprint:boolean;
    WipWaster:integer;    
    procedure checkIOType;
    procedure checkcoCurrentqty;
    procedure InitCmbbx_WhPositionCode(whCode:string);
    procedure InitCmbbx_WhEmployee(whCode:string);
    procedure showdbgrid;
    procedure initprint;
    { Private declarations }
  public
    procedure SetStatus(CurrentStatus:String;var AnswerStatus,EnableControls:String); Override;//设置各种状态下那些控件Enable
    procedure InitControls; Override;//初始化Form上所有控件
    procedure SaveData; Override;

      { Public declarations }
  end;

var
  Frm_Sfc_MnRequestOverPlan_B: TFrm_Sfc_MnRequestOverPlan_B;

implementation

uses Sys_Global, Sfc_MnRequest,Sfc_MnRequestOverPlan_D,Inv_Global,
  Sfc_MnOut_NotSave_P;
{$R *.DFM}

procedure TFrm_Sfc_MnRequestOverPlan_B.Showdbgrid;
var
  sql_text,tmpfields,tmp_DeptCode,tmp_DeptCode1,tmp_WhEmployee:string;
begin
  tmpfields:=getInvfield(AdoQry_tmp,getCode(cmbbx_WhCode.text),getCode(cmbbx_WhPositionCode.text),'CurrentInv');

  if checkbox2.Checked=True then
  begin
     tmp_DeptCode:=' and  (m.DeptCode in ('''+getCode(lbl_Dept.Caption)+''','''+copy(getCode(lbl_Dept.Caption),1,length(getCode(lbl_Dept.Caption))-2)+''')) ';
     tmp_DeptCode1:=' and  (DeptCode in ('''+getCode(lbl_Dept.Caption)+''','''+copy(getCode(lbl_Dept.Caption),1,length(getCode(lbl_Dept.Caption))-2)+''')) '
  end
  else
  begin
     tmp_DeptCode:='';
     tmp_DeptCode1:='';
  end;

  if cmbbx_WhEmployee.text<>'' then
    tmp_WhEmployee:='   and i.wh_EmployeeCode='''+getCode(cmbbx_WhEmployee.text)+''''
  else
    tmp_WhEmployee:='';

{
   数据的取法：
   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,BilllineremArk varchAr(250) null,'+
                          ' status varchAr(10) null ,status1 varchAr(10) null,'+
                          'moCtrlqty float null,MoRealqty float null,'+
                          'moRequestqty float null,alterNative int,ItemListid int,'+
                          'Parentid int,Bomqty float null,BomScrAp_Percent float null,Batchno varchAr(16)) '+
      //插入满足条件的非替代件，所有条件为限额数量〈=实领数量
      ' 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,'+
           'convert(int,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 convert(int,m.moCtrlqty)<=m.MoRealqty '+
          tmp_DeptCode+
       //插入满足件的替代虚项
      ' 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,'+
           'convert(int,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 convert(int,m.moCtrlqty)>0 '+
         '   and convert(int,moCtrlqty)<=MoRealqty '+
          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 '+
      //把代换项实际项加入到临时表中
    ' insert #tmp1(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.Parentid in ( select ItemListid '+
                           ' from mnItemList '+
                           ' where mono='''+lbl_Mono.Caption+''' '+
                           '   and MoLineno='+lbl_MoLineno.Caption+
                           '   and alterNative=1 '+
                           '   and moCtrlqty>0  '+
                           '   and convert(int,moCtrlqty)<=MoRealqty '+
                           tmp_DeptCode1+')'+
      ' declAre @tmp_Status varchAr(10) '+
      ' declAre @tmp_Parentid int '+
      ' declAre @n int '+
     ' select @n=65 '+
     ' select distinct ite_ItemCode,Parentid '+
     '  into #tmp3 '+
     '  from #tmp1 '+
     ' where  ite_ItemCode<>'''' and alterNative=1 and moCtrlqty=0 '+
     //做一个CURSOR，对替代件子物料代码字段中父物料代码作status,status1字段标志
     ' declAre Item_Cursor cursor for select ite_ItemCode,Parentid from #tmp3 '+
     ' open Item_Cursor '+
     ' fetch Item_Cursor into @tmp_ItemCode,@tmp_Parentid '+
     ' while @@fetch_Status=0 '+
     ' begin '+
     '   select @tmp_Status=status from #tmp1 where ItemListid=@tmp_Parentid '+
     '   if @tmp_Status is null '+
     '      select @tmp_Status=chAr(@m) '+
     '   select @tmp_Status=@tmp_Status+chAr(@n) '+
     '   update #tmp1 set status=@tmp_Status where ItemListid=@tmp_Parentid '+
     '   select @tmp_Status=status from #tmp1 where ItemListid=@tmp_Parentid '+
     '   update #tmp1 set status=@tmp_Status+''1'' where Parentid=@tmp_Parentid '+
     '   select @n=@n+1 '+
     '   fetch Item_Cursor into @tmp_ItemCode,@tmp_Parentid '+
     ' end '+
     ' Close Item_Cursor '+
     ' deallocate Item_Cursor '+
      ' select * from '+
      ' (select t.mono,'+
           't.MoLineno,'+
           '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 and moCtrlqty>0) then ''-''+t.ItemCode '+
         ' else t.ItemCode end  tmpItemCode,'+
           'i.ItemName,'+
           'e.EmployeeName,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BilllineremArk,'+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.Bomqty,'+
           't.BomScrAp_Percent,'+
           't.status,'+
           't.alterNative,'+
           't.Parentid,'+
           't.ItemListid,'+
           't.Batchno,'+
           'c.'+tmpfields+' as onhandInv '+
       '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 '+
       ' where (i.whCode='''+getCode(cmbbx_WhCode.text)+''' or '+
       '       c.'+tmpfields+'>0) '+
       ' union '+
       ' select t.mono,'+
           't.MoLineno,'+
           '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 and moCtrlqty>0) then ''-''+t.ItemCode '+
         ' else t.ItemCode end  tmpItemCode,'+
           'i.ItemName,'+
           'null as EmployeeName,'+
           'i.ItemCode2,'+
           'u.UomName,'+
           't.BilllineremArk,'+
           't.moCtrlqty,'+
           't.MoRealqty,'+
           't.moRequestqty, '+
           't.Bomqty,'+
           't.BomScrAp_Percent,'+
           't.status,'+
           't.alterNative,'+
           't.Parentid,'+
           't.ItemListid,'+
           't.Batchno,'+
           'null as onhandInv '+
       'from #tmp1 t,Item i,Uom u '+
       ' where t.ItemCode=i.ItemCode '+
       '   and i.UomCode=u.UomCode '+
       '   and t.status1 is not null '+
       ' union '+