sfc_mnlistrequest_b.pas

一个MRPII系统源代码版本

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

Type
  TFrm_Sfc_MnListRequest_B = Class(TFrm_Base_Entry_Body)
    Label3: TLabel;
    Label5: TLabel;
    Label9: TLabel;
    Label11: TLabel;
    lbl_Dept: TLabel;
    lbl_Mono: TLabel;
    lbl_Item: TLabel;
    cmbbx_Shift: TComboBox;
    Label1: TLabel;
    DBText1: TDBText;
    Label2: TLabel;
    DBText2: TDBText;
    ComboBox1: TComboBox;
    Label4: TLabel;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    Label6: TLabel;
    lbl_qty: TLabel;
    Label8: TLabel;
    lbl_Overdate: TLabel;
    lbl_MoLineno: TLabel;
    lbl_Mono1: TLabel;
    Label7: TLabel;
    cmbx_Employee: TComboBox;
    procedure FormCreate(Sender: TObject);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender:TObject);
    procedure medt_DateChange(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure DBGridEh1GetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure Act_ModifyExecute(Sender: TObject);
    procedure Act_InsertLineExecute(Sender: TObject);
    procedure DBGridEhDblClick(Sender: TObject);
    procedure cmbbx_ShiftChange(Sender: TObject);
    procedure Act_DeleteLineExecute(Sender: TObject);
    procedure Act_QuitExecute(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure cmbx_EmployeeChange(Sender: TObject);
  private
    IOType,oldmoRequestlineqty,oldswApmoRequestqty,oldbodymoRequestqty:real;
    swApflag,MoLinestatus:integer;
    bodyItemListid,BillTypeCode,oldwhCode,oldWhPosition,oldEmployee:string;
    IsAfterprint:boolean;
    procedure  showdbgrid1(s:string;whCode:string;WhPosition:string);
    procedure showdbgrid(s:string); ///没有用
    { Private declarations }
  public
    tmp_Status:string;
    ClassType1:string;
    procedure SetStatus(CurrentStatus:String;var AnswerStatus,EnableControls:String); Override;//设置各种状态下那些控件Enable
    procedure InitControls; Override;//初始化Form上所有控件
    procedure SaveData; Override;

      { Public declarations }
  end;

var
  Frm_Sfc_MnListRequest_B: TFrm_Sfc_MnListRequest_B;

implementation

uses Sys_Global, Sfc_MnListRequest,Inv_Global, Sfc_MnListOut_P;//,Sfc_MnListRequest_D;

{$R *.DFM}

procedure  TFrm_Sfc_MnListRequest_B.Showdbgrid(s:string);
var
  sql_text,tmp_WhEmployee,tmp_DeptCode,tmpfields:string;
begin
  try
  //确保获取数据的临时表的删除工作
    with AdoQry_tmp do
    begin
      Close;
      sql.text:='drop table #tmPmoRequest,#TmPmORequest2';
      execsql;
    end;
  except
  end;
  //初始化Qry＿BODY数据
  sql_text:=
    'set noCount on '+
    'select m.ItemCode,m.moCtrlqty,m.MoRealqty,m.mono,m.MoLineno,m.Bomqty,m.BomScrAp_Percent,'+
         ' m.alterNative,m.Parentid,m.ItemListid,m.Parentid Parentid2 '+
       'into #TmPmORequest '+
       'from mnItemList m '+
       'where m.mono='''+lbl_Mono.Caption+''' '+
         '   and m.MoLineno='+lbl_MoLineno.Caption+' '+
    //使父项与子项放在一起
    'update #TmPmORequest '+
       'set Parentid=ItemListid '+
       'where ItemListid in (select Parentid from #TmPmORequest) '+
    //产生显示的结果集
    'select t.ItemCode+'' ''+i.ItemName ItemDESC,t.ItemCode,u.UomName,i.ItemCode2,t.Parentid,t.ItemListid,t.alterNative,'+
       '  t.moCtrlqty,t.MoRealqty,t.Parentid2,m.ShiftType,t.mono,t.MoLineno,t.Bomqty,t.BomScrAp_Percent, '+
       '  m.whCode1,m.WhPositionCode1,m.Requestqty1,m.realoutqty1,Batchno1,m.Requesttime1, '+
       '  m.whCode2,m.WhPositionCode2,m.Requestqty2,m.realoutqty2,Batchno2,m.Requesttime2, '+
       '  m.whCode3,m.WhPositionCode3,m.Requestqty3,m.realoutqty3,Batchno3,m.Requesttime3, '+
       '  m.whCode4,m.WhPositionCode4,m.realoutqty4,Batchno4,m.Requesttime4, '+
       '  m.whCode5,m.WhPositionCode5,m.realoutqty5,Batchno5,m.Requesttime5 '+
       'into #TmPmORequest2 '+
       'from #TmPmORequest t '+
       '  left join '+
       '    (select ItemListid,ShiftType, '+
       '         m.whCode1+'' ''+w1.whName whCode1,rTrim(m.WhPositionCode1)+'' ''+wp1.WhPositionName WhPositionCode1,m.Requestqty1,m.realoutqty1,Batchno1,m.Requesttime1, '+
       '         m.whCode2+'' ''+w2.whName whCode2,rTrim(m.WhPositionCode2)+'' ''+wp2.WhPositionName WhPositionCode2,m.Requestqty2,m.realoutqty2,Batchno2,m.Requesttime2, '+
       '         m.whCode3+'' ''+w3.whName whCode3,rTrim(m.WhPositionCode3)+'' ''+wp3.WhPositionName WhPositionCode3,m.Requestqty3,m.realoutqty3,Batchno3,m.Requesttime3, '+
       '         m.whCode4+'' ''+w4.whName whCode4,rTrim(m.WhPositionCode4)+'' ''+wp4.WhPositionName WhPositionCode4,m.realoutqty4,Batchno4,m.Requesttime4, '+
       '         m.whCode5+'' ''+w5.whName whCode5,rTrim(m.WhPositionCode5)+'' ''+wp5.WhPositionName WhPositionCode5,m.realoutqty5,Batchno5,m.Requesttime5 '+
       '       from MNListRequest m '+
       '         left join Warehouse w1 '+
       '           on w1.whCode=m.whCode1 '+
       '         left join WhPosition wp1 '+
       '           on wp1.whCode=m.whCode1 and '+
       '              wp1.WhPositionCode=m.WhPositionCode1 '+
       '         left join Warehouse w2 '+
       '           on w2.whCode=m.whCode2 '+
       '         left join WhPosition wp2 '+
       '           on wp2.whCode=m.whCode2 and '+
       '              wp2.WhPositionCode=m.WhPositionCode2 '+
       '         left join Warehouse w4 '+
       '           on w4.whCode=m.whCode4 '+
       '         left join WhPosition wp4 '+
       '           on wp4.whCode=m.whCode4 and '+
       '              wp4.WhPositionCode=m.WhPositionCode4 '+
       '         left join Warehouse w5 '+
       '           on w5.whCode=m.whCode5 '+
       '         left join WhPosition wp5 '+
       '           on wp5.whCode=m.whCode5 and '+
       '              wp5.WhPositionCode=m.WhPositionCode5 '+
       '         left join Warehouse w3 '+
       '           on w3.whCode=m.whCode3 '+
       '         left join WhPosition wp3 '+
       '           on wp3.whCode=m.whCode3 and '+
       '              wp3.WhPositionCode=m.WhPositionCode3 where m.shiftType='''+s+''' ) m'+
       '    on m.ItemListid=t.ItemListid, '+
       '  Item i,Uom u '+
       'where t.ItemCode=i.ItemCode and '+
       '  i.UomCode=u.UomCode '+
       'Order by t.Parentid,t.ItemListid '+
    //处理虚项的子项缩进，注意可能有虚项嵌套的情况
    //只考虑两两层嵌套的情况
       'update #TmPmORequest2 '+
         'set ItemDESC=''  ''+ItemDESC '+
         'where Parentid2 in '+
         '  (select ItemListid from #TmPmORequest) '+
         ' alter table #TmPmORequest2 Add tmp01 varchAr(10) '+
         ' alter table #TmPmORequest2 Add tmp02 varchAr(10) '+
         ' alter table #TmPmORequest2 Add tmp03 varchAr(10) '+
       'update #TmPmORequest2 '+
         'set ItemDESC=''  ''+ItemDESC '+
         'where Parentid2 in '+
         '  (select ItemListid from #TmPmORequest where Parentid2<>0) '+
    'select * from #TmPmoRequest2 ';
  try
    with AdoQry_Body do
    begin
      Close;
      sql.clear;
      sql.Add(sql_text);
      open;

    end;
  ClassType1:=AdoQry_Body.fieldbyname('shiftType').asstring;
  finally
  //获取数据后删除临时表
    with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('drop table #TmPmORequest,#TmPmoRequest2');
      execsql;
    end;
  end;
//
  with dbgrideh do
  begin
    columns[4].Width:=64;
    columns[5].Width:=64;
    columns[8].Width:=64;
    columns[9].Width:=64;
    columns[12].Width:=64;
    columns[13].Width:=64;
    columns[16].Width:=64;
    columns[17].Width:=64;
    columns[20].Width:=64;
    columns[21].Width:=64;
  end;
end;

procedure TFrm_Sfc_MnListRequest_B.InitControls;
begin//初始化Form上的控件
  inherited;

  BillTypeCode:=Param2;
  lbl_Mono.Caption:=AdoQry_Head.fieldbyname('mono').asstring;
  lbl_Mono1.Caption:=AdoQry_Head.fieldbyname('mono').asstring+'-'+
                    AdoQry_Head.fieldbyname('MoLineno').asstring;
  lbl_MoLineno.Caption:=AdoQry_Head.fieldbyname('MoLineno').asstring;
  lbl_Item.Caption:=AdoQry_Head.fieldbyname('ItemCode').asstring+' '+Frm_Sfc_MnListRequest.AdoQry_Head.fieldbyname('ItemName').asstring;
  lbl_Dept.Caption:=Frm_Sfc_MnListRequest.tmp_DeptCode;
  lbl_qty.Caption:=AdoQry_Head.fieldbyname('moqty').asstring;
  lbl_Overdate.Caption:=AdoQry_Head.fieldbyname('MoLinedate').asstring;
   MoLinestatus:=AdoQry_Head.fieldbyname('MoLinestatus').asinteger;

  IOType:=0;
  combobox1.Enabled:=True;
  checkbox1.Enabled:=True;
  cmbx_Employee.Enabled:=True;
  checkbox2.Enabled:=True;
  cmbbx_Shift.Enabled:=True;

  Act_insertline.visible:=True;
  act_Modify.visible:=True;
  act_Deleteline.visible:=True;
  act_SetColumn.Enabled:=True;


  if Frm_Sfc_MnListRequest.AdoQry_Head.fieldbyname('MoLinestatus').asstring='准备' then
  begin
    Act_insertline.Enabled :=False;
    act_Modify.Enabled:=False;
    act_Deleteline.Enabled:=False;
  end
  else
  begin
    Act_insertline.Enabled :=True;
    act_Modify.Enabled:=True;
    act_Deleteline.Enabled:=True;
  end;
  if status='ReadOnly' then
  begin
    Act_insertline.Enabled :=False;
    act_Modify.Enabled:=False;
    act_Deleteline.Enabled:=False;
  end;
  InitShiftCmBx(AdoQry_tmp,cmbbx_Shift,True);
  InitCmBxText(cmbbx_Shift,Frm_Sfc_MnListRequest.tmp_MoRequestshift);
  showdbgrid1('',getCode(combobox1.text),'');
//  showdbgrid(Frm_Sfc_MnListRequest.tmp_MoRequestshift);
end;

procedure TFrm_Sfc_MnListRequest_B.SetStatus(CurrentStatus:String;var AnswerStatus,
  EnableControls:String);
begin//指明当前状态,定义Enable=True的控件
  inherited;
 if CurrentStatus='Edit' then
 begin
   EnableControls:='cmbbx_Shift,combobox1,checkbox1,cmbx_Employee,';
 end
 else
   EnableControls:='cmbbx_Shift,combobox1,checkbox1,cmbx_Employee,';
end;

procedure TFrm_Sfc_MnListRequest_B.FormCreate(Sender: TObject);
begin
  inherited;
  IsAfterprint:=False;
//  toolbutton2.Action:=act_SetColumn;
//  toolbutton2.Visible:=True;
//  Frm_Entry_Detail:=TFrm_Sfc_MnListRequest_D.Create(Self);
//  SetFocus_Control:=cmbbx_Shift;
end;

procedure  TFrm_Sfc_MnListRequest_B.Showdbgrid1(s:string;whCode:string;WhPosition:string);
var
  sql_text,con_Sql,con_Sql2,tmp_WhEmployee,tmp_DeptCode,tmpfields,tmp_WhCode,tmp_WhPosition:string;
  cursor:tcursor;
begin
  cursor:=Screen.Cursor ;
  Screen.Cursor :=crHourGlass;
  tmp_WhCode:=whCode;
  tmp_WhPosition:=WhPosition;
  if  whCode<>'' then
  begin
    with AdoQry_tmp do
    begin
      Close;
      sql.text:='select WhPositionCode from WhPosition where WhPositionType=0 and whCode='+quotedstr(whCode);
      open;
      if not eof then
      tmp_WhPosition:=fieldbyname('WhPositionCode').AsString;
    end;
  end;
  try
    with AdoQry_tmp do
    begin
      Close;
      sql.text:='drop table #tmPmoRequest,#TmPmORequest2';
      execsql;
    end;
  except
  end;
  if (checkbox1.Checked) or (whCode='') then
     con_Sql2:=' and 1=1 '
  else
    if cmbx_Employee.Text='' then
     con_Sql2:=' and (i.whCode='''+tmp_WhCode+''' or (m.alterNative=1 and m.Parentid=0) '+
     ' or m.alterNative=2 ) '
    else
     con_Sql2:=' and (i.whCode='+quotedstr(tmp_WhCode)+' or (m.alterNative=1 and m.Parentid=0) '+
     ' or m.alterNative=2 ) and (i.WH_EmployeeCode='+
     QuotedStr(GetCode(cmbx_Employee.Text))+' or (m.alterNative=1 and m.Parentid=0) or m.alterNative=2)';

  if s='' then
     con_Sql:=' (m.shiftType is null or m.shiftType='''') and '
  else
     con_Sql:=' m.shiftType='''+s+''' and ';
  if tmp_WhCode='' then
     con_Sql:=con_Sql+' 1=1 and '
  else
     con_Sql:=con_Sql+' 1=1 and ';
//     con_Sql:=con_Sql+'(m.whCode1='''+tmp_WhCode+''' or m.whCode2='''+tmp_WhCode+''' or m.whCode3='''+tmp_WhCode+''' or m.whCode4='''+tmp_WhCode+''' or m.whCode5='''+tmp_WhCode+''') and '+
//              '(m.WhPositionCode1='''+tmp_WhPosition+''' or m.WhPositionCode2='''+tmp_WhPosition+''' or m.WhPositionCode3='''+tmp_WhPosition+''' or m.WhPositionCode4='''+tmp_WhPosition+''' or m.WhPositionCode5='''+tmp_WhPosition+''') and ';
  sql_text:=
    'set noCount on '+
    'select c.whCode,c.onhandInv,m.ItemCode,m.ite_ItemCode,m.BilllineremArk,M.BomRemArk,'+
       'm.moCtrlqty,m.MoRealqty,m.mono,m.MoLineno,m.Bomqty,m.BomScrAp_Percent,'+
       'm.alterNative,m.Parentid,m.ItemListid,m.Parentid Parentid2 '+
       'into #TmPmORequest '+
       'from mnItemList m '+
       '   left join CurrentInv C on m.ItemCode=C.ItemCode and c.whCode='+QuotedStr(tmp_WhCode)+' and c.WhPositionCode='+quotedstr(tmp_WhPosition)+
       '   left join Item i on m.ItemCode=i.ItemCode '+
       'where m.mono='''+lbl_Mono.Caption+''' '+
         con_Sql2+
         '   and m.MoLineno='+lbl_MoLineno.Caption+' '+
    //使父项与子项放在一起
    'update #TmPmORequest '+
       'set Parentid=ItemListid '+
       'where ItemListid in (select Parentid from #TmPmORequest) '+
    //产生显示的结果集
    'select t.ItemCode+'' ''+i.ItemName ItemDESC,t.ItemCode,t.ite_ItemCode,t.whCode,t.onhandInv,'+
       ' t.BilllineremArk,u.UomName,i.ItemCode2,t.Parentid,t.ItemListid,t.alterNative,'+
       '  t.moCtrlqty,t.MoRealqty,t.Parentid2,m.ShiftType,t.mono,t.MoLineno,t.Bomqty,t.BomScrAp_Percent, '+
       '  m.whCode1,m.WhPositionCode1,m.Requestqty1,m.realoutqty1,Batchno1,m.Requesttime1, '+
       '  m.whCode2,m.WhPositionCode2,m.Requestqty2,m.realoutqty2,Batchno2,m.Requesttime2,m.tmpflag, '+
       '  m.whCode3,m.WhPositionCode3,m.realoutqty3,Batchno3,m.Requesttime3, '+
       '  m.whCode4,m.WhPositionCode4,m.realoutqty4,Batchno4,m.Requesttime4, '+
       '  m.whCode5,m.WhPositionCode5,m.realoutqty5,Batchno5,m.Requesttime5 '+
       'into #TmPmORequest2 '+
       'from #TmPmORequest t '+
       '  left join '+
       '    (select ItemListid,ShiftType,m.tmpflag, '+
       '         m.whCode1+'' ''+w1.whName whCode1,rTrim(m.WhPositionCode1)+'' ''+wp1.WhPositionName WhPositionCode1,m.Requestqty1,m.realoutqty1,Batchno1,m.Requesttime1, '+
       '         m.whCode2+'' ''+w2.whName whCode2,rTrim(m.WhPositionCode2)+'' ''+wp2.WhPositionName WhPositionCode2,m.Requestqty2,m.realoutqty2,Batchno2,m.Requesttime2, '+
       '         m.whCode3+'' ''+w3.whName whCode3,rTrim(m.WhPositionCode3)+'' ''+wp3.WhPositionName WhPositionCode3,m.Requestqty3,m.realoutqty3,Batchno3,m.Requesttime3, '+
       '         m.whCode4+'' ''+w4.whName whCode4,rTrim(m.WhPositionCode4)+'' ''+wp4.WhPositionName WhPositionCode4,m.realoutqty4,Batchno4,m.Requesttime4, '+
       '         m.whCode5+'' ''+w5.whName whCode5,rTrim(m.WhPositionCode5)+'' ''+wp5.WhPositionName WhPositionCode5,m.realoutqty5,Batchno5,m.Requesttime5 '+
       '       from MNListRequest m '+
       '         left join Warehouse w1 '+
       '           on w1.whCode=m.whCode1 '+
       '         left join WhPosition wp1 '+
       '           on wp1.whCode=m.whCode1 and '+
       '              wp1.WhPositionCode=m.WhPositionCode1 '+
       '         left join Warehouse w2 '+
       '           on w2.whCode=m.whCode2 '+
       '         left join WhPosition wp2 '+
       '           on wp2.whCode=m.whCode2 and '+
       '              wp2.WhPositionCode=m.WhPositionCode2 '+
       '         left join Warehouse w4 '+
       '           on w4.whCode=m.whCode4 '+
       '         left join WhPosition wp4 '+
       '           on wp4.whCode=m.whCode4 and '+
       '              wp4.WhPositionCode=m.WhPositionCode4 '+
       '         left join Warehouse w5 '+
       '           on w5.whCode=m.whCode5 '+
       '         left join WhPosition wp5 '+
       '           on wp5.whCode=m.whCode5 and '+
       '              wp5.WhPositionCode=m.WhPositionCode5 '+
       '         left join Warehouse w3 '+
       '           on w3.whCode=m.whCode3 '+
       '         left join WhPosition wp3 '+
       '           on wp3.whCode=m.whCode3 and '+
       '              wp3.WhPositionCode=m.WhPositionCode3 ) m'+
       '    on m.ItemListid=t.ItemListid, '+
       '  Item i,Uom u '+
       'where t.ItemCode=i.ItemCode and '+
       '  i.UomCode=u.UomCode and '+
          con_Sql+
//       '(m.Requestqty1<>0 or m.Requestqty2<>0 or m.Requestqty3<>0) and '+
       '(m.realoutqty1 is null or m.realoutqty1=0 or m.realoutqty2 is null or m.realoutqty2=0 or m.realoutqty3 is null or m.realoutqty3=0'+
       ' or m.realoutqty4 is null or m.realoutqty4=0 or m.realoutqty5 is null or m.realoutqty5=0 ) '+
       'Order by t.Parentid,t.ItemListid '+
    //处理虚项的子项缩进，注意可能有虚项嵌套的情况
    //只考虑两两层嵌套的情况
       'update #TmPmORequest2 '+
         'set ItemDESC='' ''+ItemDESC '+
         'where Parentid2 in '+
         '  (select ItemListid from #TmPmORequest) '+
       'update #TmPmORequest2 '+
         'set ItemDESC='' ''+ItemDESC '+
         'where Parentid2 in '+
         '  (select ItemListid from #TmPmORequest where Parentid2<>0) '+
         ' alter table #TmPmoRequest2 Add tmp01 varchAr(10) '+
         ' alter table #TmPmoRequest2 Add tmp02 varchAr(10) '+
         ' alter table #TmPmoRequest2 Add tmp03 varchAr(10) '+
    'select * from #TmPmoRequest2 ';
  try