mrp_qry_materialmo.pas

一个MRPII系统源代码版本

unit Mrp_Qry_MaterialMo;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Entry_Head, Menus, Db, ActnList, AdODB, Grids, DBGridEh, StdCtrls,
  ExtCtrls, ComCtrls, ToolWin, ExtEdit;

Type
  TFrm_Mrp_Qry_MaterialMo = Class(TFrm_Base_Entry_Head)
    Label1: TLabel;
    AdoQry_Headmono: TStringField;
    AdoQry_HeadMoLineno: TIntegerField;
    AdoQry_HeadItemCode: TStringField;
    AdoQry_HeadMoLinestatus: TIntegerField;
    AdoQry_Headonhandqty: TFloatField;
    AdoQry_Headrequireqty: TFloatField;
    edtItemCode: TExtEdit;
    AdoQry_HeadItemflag: TStringField;
    AdoQry_Headonwayqty: TFloatField;
    AdoQry_Headbeenusedqty: TFloatField;
    AdoQry_Headlessqty: TFloatField;
    AdoQry_HeadmostArtworkdate: TDateTimeField;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    lbl_Preparelt: TLabel;
    Label10: TLabel;
    Label13: TLabel;
    lbl_runlt: TLabel;
    Label16: TLabel;
    lbl_Qclt: TLabel;
    procedure edtItemCodeExit(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_LookExecute(Sender: TObject);
    procedure edtItemCodeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
   ItemCode:string;
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
   procedure Bomopen(ItemCode:string;reqqty:real);
   function  existsfArItem(ItemCode:string):boolean;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_MaterialMo: TFrm_Mrp_Qry_MaterialMo;
  mindate,maxdate:string;
implementation

uses Mrp_Qry_MaterialMo_Po,Sys_Global;

{$R *.DFM}

{ TFrm_Mrp_Qry_MaterialMo }

procedure TFrm_Mrp_Qry_MaterialMo.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
var sqltext1,sqltext2,sqltext3:string;
begin
  inherited;
   ItemCode:=edtItemCode.Text;
if ItemCode='' then
    exit;
sqltext1:='select top 0 ite_ItemCode,Bomqty,BomScrAp_Percent '
          +'   into #tmpite_ItemCode                  '
          +'  from Bom                                '
          +' where ItemCode='+quotedstr(ItemCode);
Executesql(AdoQry_tmp,sqltext1,1);
Bomopen(ItemCode,1);
Executesql(AdoQry_tmp,'select * from #tmpite_ItemCode',0);
sqltext2:='select  distinct mostArtworkdate,     '
          +'       mono,                '
          +'       MoLineno,             '
          +'       MoLine.ItemCode,     '
          +'       Itemflag=MoLine.ItemCode+'' ''+t2.ItemName,'
          +'       MoLinestatus,         '
          +'       onhandqty=t1.CurrentonhandInv+t1.CurrentonCheckInv,'
          +'       onwayqty=convert(float,0),                 '
          +'       beenusedqty=convert(float,0),              '
          +'       requireqty=moqty*#tmpite_ItemCode.Bomqty*(1+#tmpite_ItemCode.BomScrAp_Percent/100.00), '
          +'       lessqty=convert(float,0)                      '
          +'   into #tmpResult  '
         +' from MoLine,#tmpite_ItemCode,Item  t1,Item t2       '
         +' where #tmpite_ItemCode.ite_ItemCode=MoLine.ItemCode     '
         +'  and  MoLinestatus<=6     '
         +'  and  MoLine.ItemCode=t2.ItemCode'
         +'  and  t1.ItemCode='+quotedstr(ItemCode)
         +' Order by mostArtworkdate         '+
        ' select top 0 * into #tmpres from #tmpResult';
sqltext3:='declAre @mindate datetime,      '
          +'         @nowqty  float,        '
          +'         @beenusedqty float,    '
          +'         @requireqty  float,    '
          +'         @lessqty    float,     '
          +'         @Count    int          '
         +'  set rowCount 1                 '
          +' insert into #tmpres            '
          +' select * from #tmpResult       '
         + ' update #tmpres                  '
         + ' set onwayqty=isnull((select sum(isnull(ponoFinishqty,0)) from poline where polinestatus=6 and ItemCode='+quotedstr(ItemCode)+' and polinedate<=#tmpres.mostArtworkdate),0) '
         + ' update #tmpres                                     '
         + '  set   lessqty=requireqty-onwayqty-onhandqty       '
         + '  select @mindate=mostArtworkdate from #tmpres      '
         + '  select @nowqty=onhandqty+onwayqty-requireqty from #tmpres  '
         + '  select @requireqty=requireqty from #tmpres          '
         + '  select @lessqty=lessqty from #tmpres                '
         + ' if @lessqty>0                                        '
         + '  select @beenusedqty=onhandqty from #tmpres          '
         + ' else select @beenusedqty=requireqty from #tmpres     '
         + '  delete from #tmpResult                              '
         + ' select @Count=Count(*) from #tmpResult               '
         + ' select @Count                                         '
         + ' while @Count>0                                       '
         + '  begin                                               '
         + '  if @nowqty>0                                        '
         + '   update #tmpResult                                  '
         + '  set onhandqty=@nowqty                               '
         + '  else update #tmpResult                              '
         + '   set onhandqty=0                                     '
         + '  update #tmpResult                                    '
         + '  set beenusedqty=@beenusedqty                         '
         + '  update #tmpResult                                    '
         + '  set onwayqty=isnull((select sum(isnull(ponoFinishqty,0))  '
         + '            from poline                                    '
         + '             where polinestatus=6                         '
         + '               and ItemCode='+quotedstr(ItemCode)                     
         + '               and polinedate<=#tmpResult.mostArtworkdate  '
         + '               and polinedate>@mindate),0)                 '
         + ' update #tmpResult                                         '
         + ' set    lessqty=requireqty-onwayqty-onhandqty              '
          + '   select @lessqty=lessqty from #tmpResult      '
         + '  select @mindate=mostArtworkdate from #tmpResult          '
         + '  select @nowqty=onhandqty+onwayqty-requireqty from #tmpResult '
         + '  select @requireqty=requireqty from #tmpResult                '
         + '   if @lessqty>0 '
         +'           select @beenusedqty=beenusedqty+onhandqty from #tmpResult '
         +'         else  select @beenusedqty=beenusedqty+requireqty from #tmpResult '
          + ' insert into #tmpres          '
          + '    select * from #tmpResult   '
          + '  delete from #tmpResult      '
          + ' select @Count=@Count-1      '
          + ' end                         '
         + ' set rowCount 99999999       ';

with  AdoQry_Head do
  begin
    Close;
    sql.clear;
    sql.Add(sqltext2);
    Prepared;
    try
    execsql;
    except
    end;

    Close;
    sql.clear;
    sql.Add(sqltext3);
    Prepared;
    try
    execsql;
    except
    end;
    Close;
    sql.clear;
    sql.Add('update #tmpres set lessqty=0.0 where lessqty<=0.0');
    Prepared;
    try
    execsql;
    except
    end;

    Close;
    sql.clear;
    sql.Add('select * from #tmpres');
    Prepared;
    try
    open;
    except
    end;

with AdoQry_tmp do
 begin
  Close;
  sql.clear;
  sql.Add('select Countrequireqty=sum(isnull(requireqty,0)),Countlessqty=sum(isnull(lessqty,0)) from #tmpres');
  try
  open;
  except
  end;
  label4.Caption:=floattostr(fieldbyname('Countrequireqty').asfloat);
  label5.Caption:=floattostr(fieldbyname('Countlessqty').asfloat);
 end;
with AdoQry_tmp do
 begin
  Close;
  sql.clear;
  sql.Add('select Uom.UomName,Item.Preparelt,Item.runlt,Item.Qclt from Item,Uom where Item.UomCode=Uom.UomCode and Item.ItemCode='+quotedstr(ItemCode));
  try
  open;
  except
  end;
  label8.Caption:=fieldbyname('UomName').asstring;
  lbl_Preparelt.Caption:=fieldbyname('Preparelt').asstring;
  lbl_runlt.Caption:=fieldbyname('runlt').asstring;
  lbl_Qclt.Caption:=fieldbyname('Qclt').asstring;
 end;

  end;
end;

procedure TFrm_Mrp_Qry_MaterialMo.edtItemCodeExit(Sender: TObject);
begin
  inherited;
with AdoQry_tmp do
 begin
  Close;
  sql.clear;
  sql.Add('select * from Item where ItemCode='+quotedstr(edtItemCode.text));
  try
  open;
  except
  end;
  if recordCount=0 then
    commonHint(edtItemCode,AdoQry_tmp,'ItemName','物料名称','ItemCode','物料代码','Item','PmCode in(1,2)')
 end;

  with AdoQry_tmp do
 begin
  Close;
  sql.clear;
  sql.Add('drop table #tmpite_ItemCode,#tmpres,#tmpResult');
  try
  execsql;
  except
  end;
 end;
InitForm(dbconnect,True);
end;

procedure TFrm_Mrp_Qry_MaterialMo.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_MaterialMo:=nil;
end;

procedure TFrm_Mrp_Qry_MaterialMo.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
with AdoQry_tmp do
 begin
  Close;
  sql.clear;
  sql.Add('drop table #tmpite_ItemCode,#tmpres,#tmpResult');
  try
  execsql;
  except
  end;
 end;
end;

procedure TFrm_Mrp_Qry_MaterialMo.Act_LookExecute(Sender: TObject);
begin
  inherited;
if ( not  (AdoQry_Head.fieldbyname('onwayqty').asfloat>0)) then
  exit;
if not (AdoQry_Head.RecNo=1) then
 begin
 maxdate:=AdoQry_Head.fieldbyname('mostArtworkdate').asstring;
 AdoQry_Head.Prior;
 mindate:=AdoQry_Head.fieldbyname('mostArtworkdate').asstring;
 AdoQry_Head.Next;
 end
else begin
     mindate:='1900.01.01';
     maxdate:=AdoQry_Head.fieldbyname('mostArtworkdate').asstring;
     end;
 if Frm_Mrp_Qry_MaterialMo_Po=nil then
    begin
    Frm_Mrp_Qry_MaterialMo_Po:=TFrm_Mrp_Qry_MaterialMo_Po.Create(self);
    Frm_Mrp_Qry_MaterialMo_Po.SetSysParam(userCode,ModuleCode,menuid,formatdatetime('yyyy.mm.dd',now));
    Frm_Mrp_Qry_MaterialMo_Po.ItemCode:=ItemCode;
    Frm_Mrp_Qry_MaterialMo_Po.InitForm(AdoQry_Head.Connection,True);
  end
  else Frm_Mrp_Qry_MaterialMo_Po.Show;

end;

procedure TFrm_Mrp_Qry_MaterialMo.edtItemCodeKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  if key=120 then
    commonHint(edtItemCode,AdoQry_tmp,'ItemName','物料名称','ItemCode','物料代码','Item','PmCode in(1,2) and ItemUsable=1')
end;

procedure TFrm_Mrp_Qry_MaterialMo.DataSourceDataChange(Sender: TObject;
  Field: TField);
begin
  inherited;
if TdataSource(sender).dataset.fieldbyname('onwayqty').asfloat>0 then
   tlbtn_look.Enabled:=True
else tlbtn_look.Enabled:=False;
end;

procedure TFrm_Mrp_Qry_MaterialMo.FormCreate(Sender: TObject);
begin
  inherited;
tlbtn_look.Enabled:=False;
end;

procedure TFrm_Mrp_Qry_MaterialMo.Bomopen(ItemCode: string;reqqty:real);
var AdoQry:TAdoQuery;
    sqltext:string;
begin
 AdoQry:=TAdoQuery.Create(Application);
 AdoQry.EnableBCD:=False;
 AdoQry.Connection:=dbconnect;
try
  sqltext:='select  ite_ItemCode as ItemCode,BomItemType,'
          +' Bomqty=Bomqty*'+floattostr(reqqty)+',BomScrAp_Percent '
          +'  from Bom                                '
          +' where ItemCode='+quotedstr(ItemCode)
          +'   and Bom.Bomstatus in (0,2)';
  Executesql(AdoQry,sqltext,0);
  with AdoQry do
    begin
      First;
        while not eof do
          begin
                sqltext:='insert into #tmpite_ItemCode '
                    +' Values('+quotedstr(fieldbyname('ItemCode').asstring)+','
                    + floattostr(fieldbyname('Bomqty').asfloat)+','
                    + floattostr(fieldbyname('BomScrAp_Percent').asfloat)+')';
                Executesql(AdoQry_tmp,sqltext,1);
            if existsfArItem(fieldbyname('ItemCode').asstring) then
                Bomopen(fieldbyname('ItemCode').asstring,fieldbyname('Bomqty').asfloat);
            next;
          end;
    end;
finally
 AdoQry.Free;
end;
end;


function TFrm_Mrp_Qry_MaterialMo.existsfArItem(ItemCode: string): boolean;
var AdoQry:TAdoQuery;
    sqltext:string;
begin
 Result:=False;
 AdoQry:=TAdoQuery.Create(Application);
 AdoQry.EnableBCD:=False;
 AdoQry.Connection:=dbconnect;
 sqltext:='select ite_ItemCode from Bom where ItemCode='+quotedstr(ItemCode)
          +' and BomItemType=3 ';
 //         +' and ItemCode not in (select ItemCode from Item where PmCode=1 or PmCode=2)';
  try
   Executesql(AdoQry,sqltext,0);
   if AdoQry.RecordCount>0 then
    Result:=True;
  finally
   AdoQry.Free;
  end;
end;

end.