mrp_qry_analyzermrpmo_h.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 443 行 · 第 1/2 页

unit Mrp_Qry_AnalyzerMrpMo_H;

Interface

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

Type
  TFrm_Mrp_Qry_AnalyzerMrpMo_H = Class(TFrm_Base_Entry_Head)
    AdoQry_HeadDeptCode: TStringField;
    AdoQry_HeadDeptName: TStringField;
    AdoQry_HeadMoNo: TStringField;
    AdoQry_HeadMoLineNo: TIntegerField;
    AdoQry_HeadItemCode: TStringField;
    AdoQry_HeadMOStArtWorkDate: TDateTimeField;
    AdoQry_HeadItemName: TStringField;
    AdoQry_HeadMoLineStatus: TIntegerField;
    AdoQry_HeadMoLineDate: TDateTimeField;
    AdoQry_HeadMoQty: TFloatField;
    AdoQry_HeadMoNoFinishQty: TFloatField;
    AdoQry_HeadDeptflag: TStringField;
    AdoQry_HeadItemflag: TStringField;
    Label2: TLabel;
    dbtxtDeptName: TDBText;
    Label1: TLabel;
    dbtxtItemflag: TDBText;
    AdoQry_Headcheckflag: TIntegerField;
    procedure FormDestroy(Sender: TObject);
    procedure Act_LookExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGridEhTitleClick(Column: TColumnEh);
    procedure AdoQry_HeadAfterPost(DataSet: TDataSet);
  private
    Flag : boolean;
    SelectedCount : integer;
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
   procedure openBom(ItemCode:string;Mpsqty:real;requiredate:string);
   function  existssonItem(ItemCode:string):boolean;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_AnalyzerMrpMo_H: TFrm_Mrp_Qry_AnalyzerMrpMo_H;
  ItemCode,requiredate:string;
  requireqty:real;
implementation

uses Mrp_Qry_AnalyzerMrpMo,Sys_Global;

{$R *.DFM}

{ TFrm_Mrp_Qry_AnalyzerMrpMo_H }

procedure TFrm_Mrp_Qry_AnalyzerMrpMo_H.InitForm(
  AdOConnection: TAdOConnection; ReadOnly: Boolean);
var sqltext:string;
begin
  inherited;
     sqltext:=' Select checkflag=0,Mo.DeptCode,Dept.DeptName,Mo.MoNo,MoLine.MoLineNo,      '+
             '         Deptflag=mo.DeptCode+'' ''+Dept.DeptName,              '+
             '         MoLine.ItemCode,MoLine.MOStArtWorkDate,                '+
             '         Item.ItemName,MoLine.MoLineStatus,MoLine.MoLineDate,   '+
             '         Itemflag=MoLine.ItemCode+'' ''+Item.ItemName,          '+
             '         MoLine.MoQty,MoLine.MoNoFinishQty                      '+
             '  into #tmPMoLinenoFinish                                       '+
             '  From MoLine                                                   '+
             '  Join Mo On MoLine.MoNo=Mo.MoNo                                '+
             '  left outer  Join Item On MoLine.ItemCode=Item.ItemCode        '+
             '  left outer  Join Dept On Mo.DeptCode=Dept.DeptCode            '+
             '  Where MoLineStatus<=6  And MoLine.MoQty>0  '+
             '   and MoLine.ItemCode not in (select ItemCode from Item where ItemUsable=0)'+
             '  Order By Mo.DeptCode,Mo.MoNo,MoLine.MoLineNo,                 '+
             '  MoLine.ItemCode,MoLine.MOStArtWorkDate                        ';

  selectfromsql:='select * from #tmPMoLinenoFinish';
    with AdoQry_Head do
      begin
        Close;
        sql.clear;
        sql.Add(sqltext);
        Prepared;
        try
        execsql;
        except
        end;

        Close;
        sql.clear;
        sql.Add('select * from #tmPMoLinenoFinish');
        Prepared;
        try
        open;
        except
        end;
      end;
  Flag := False;
  SelectedCount := 0;
end;

procedure TFrm_Mrp_Qry_AnalyzerMrpMo_H.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Mrp_Qry_AnalyzerMrpMo_H:=nil;
end;

procedure TFrm_Mrp_Qry_AnalyzerMrpMo_H.Act_LookExecute(Sender: TObject);
var BookmArk:string;
    tMpsqltext:string;
    Ordernoandlineno:string;
    AdoQry:TAdoQuery;
    tmpItemCode:string;
    tmprequireqty:double;
    tmpPlanqty:double;
    mindate:string;
    txt:string;
    tmpponoFinishqty:double;
    tmPMoNoFinishqty:double;
begin
  inherited;
  if AdoQry_Head.RecordCount=0 then exit;
  if AdoQry_Head.State in [dsedit] then AdoQry_Head.Post;
  if SelectedCount=0 then
  begin
    DispInfo('请选择记录！',3);
    abort;
  end;
  AdoQry:=TAdoQuery.Create(self);
  AdoQry.Connection:=dbconnect;
  tmpPlanqty:=0;
  mindate:='1900.01.01';
  BookmArk:=AdoQry_Head.BookmArk;
  Ordernoandlineno:=quotedstr('yy');
    try
     Executesql(AdoQry_tmp,'drop table #tmpBom',1);
    except
    end;
      tMpsqltext:='Select top 0 Bom.ItemCode,mono=''fgfdggfgfdfgfgfggf'',MoLineno=0,Bom.BomItemType,Item.PmCode,  '+#13+
                  '        dateAdd(dd,(Item.MnLdTime)*(-1),'''+requiredate+''''+') As RequireDate, '+#13+
                          floattostr(requireqty)+'*Bom.BomQty*(1+ BomScrAp_Percent/100) as requireqty,'
                  +' Item.ItemName,Item.CurrentonhandInv,Item.CurrentonCheckInv, '+#13+
                  '       Itemflag=Bom.ItemCode+'' ''+Item.ItemName'+#13+
                  ' into #tmpBom  '+#13+
                  ' From Bom   '+#13+
                  ' Join Item On Bom.ItemCode=Item.ItemCode  '+#13+
                  ' where Bom.ite_ItemCode='''+ItemCode+'''';
      Executesql(AdoQry_tmp,tMpsqltext,1);
     { tMpsqltext:=' select  top 0 #tmpBom.*,lessqty=convert(float,0),wipqty=convert(float,0),Assignqty=convert(float,0) '
                 +' into #tmpBomResult  '
                 +' from #tmpBom ';
      Executesql(AdoQry_tmp,tMpsqltext,1)                 ;}
            

  AdoQry_Head.First;
   while not AdoQry_Head.Eof do
    begin
      if AdoQry_Head.fieldbyname('checkflag').asinteger=1 then
       begin
          Ordernoandlineno:=Ordernoandlineno+','+quotedstr(AdoQry_Head.fieldbyname('mono').asstring+inttostr(AdoQry_Head.fieldbyname('MoLineno').asinteger));
          ItemCode:=AdoQry_Head.fieldbyname('ItemCode').asstring;
          requiredate:=AdoQry_Head.fieldbyname('MoLinedate').asstring;
          requireqty:=AdoQry_Head.fieldbyname('moqty').asfloat;
          openBom(ItemCode,requireqty,requiredate);
       end;
      AdoQry_Head.Next;
    end;
    //-------------------------------------------------------------
  {tMpsqltext:=' select InvOutBillline.*,MoLine.MoNoFinishqty/moqty as Finishrate            '
              +' into #tmpInvOutBill                '
              +'      from InvOutBillline          '
              +'   join InvOutBill on InvOutBill.InvBillid=InvOutBillline.InvBillid '
              +'         and  InvOutBill.mono+convert(varchAr,InvOutBill.MoLineno) in ('+Ordernoandlineno+')'
              +'         and InvOutBill.BillTypeCode in (''0201'',''0202'')    '
              +'   join MoLine on InvOutBill.mono=MoLine.mono '
              +'              and InvOutBill.MoLineno=MoLine.MoLineno '}
    tMpsqltext:=' select #tmpBom.ItemCode,#tmpBom.mono,#tmpBom.MoLineno,convert(float,0) as wipqty,sum(isnull(mnItemList.MoRealqty,0)) as Assignqty '
              +' into #tmpBom1                                             '
              +'  from #tmpBom                                              '
           +' left outer join mnItemList on  #tmpBom.ItemCode=mnItemList.ItemCode  '
           +'                             and #tmpBom.mono=mnItemList.mono'
           +'                             and #tmpBom.MoLineno=mnItemList.MoLineno '
      {     +' left outer join MoLine on #tmpBom.ItemCode=MoLine.ItemCode  '
           +'         and     MoLine.MoLinedate<=#tmpBom.requiredate     '
           +'         and     MoLine.MoLinestatus=6                     '
           +' left outer join poline on #tmpBom.ItemCode=poline.ItemCode '
           +'         and     poline.polinedate<=#tmpBom.requiredate   '
           +'         and     poline.polinestatus=6     '       }
           +'   group by #tmpBom.ItemCode,#tmpBom.mono,#tmpBom.MoLineno   ';

   Executesql(AdoQry_tmp,tMpsqltext,1);

  tMpsqltext:=' select #tmpBom.*,lessqty=convert(decimal(14,2),#tmpBom.requireqty-#tmpBom.CurrentonhandInv-isnull(#tmpBom1.Assignqty,0)),netqty=case when requireqty-#tmpBom1.Assignqty>0 then requireqty-#tmpBom1.Assignqty else 0 end,'
             +' Planqty=convert(float,0),wipqty=isnull(#tmpBom1.wipqty,0),Assignqty=isnull(#tmpBom1.Assignqty,0),minflag=0 '
            +' into #tmpBomResult'+
            ' from #tmpBom'+#13+
            ' left join #tmpBom1 on  #tmpBom.ItemCode=#tmpBom1.ItemCode'+
            '                     and #tmpBom.mono=#tmpBom1.mono '
            +'                    and #tmpBom.MoLineno=#tmpBom1.MoLineno '
            +' Order by #tmpBom.ItemCode,#tmpBom.requiredate';
   Executesql(AdoQry_tmp,tMpsqltext,1);

   Executesql(AdoQry,'select * from #tmpBomResult Order by ItemCode,requiredate',0);
    AdoQry.First;
    tmpItemCode:=AdoQry.fieldbyname('ItemCode').asstring;
   while not AdoQry.Eof do 
    begin
     if AdoQry.fieldbyname('ItemCode').asstring<>tmpItemCode then 
       begin
         mindate:='1900.01.01';
       end;
      txt:='select sum(isnull(case when ponoFinishqty<0 then 0  else ponoFinishqty end,0)) as ponoFinishqty from poline'
          +' where    poline.polinestatus=6  '
             +'  and   poline.ItemCode='+quotedstr(AdoQry.fieldbyname('ItemCode').asstring)
             +'  and   poline.polinedate > '+quotedstr(mindate)
             +' and poline.polinedate<='+quotedstr(AdoQry.fieldbyname('requiredate').asstring);
      Executesql(AdoQry_tmp,txt,0)             ;
      tmpponoFinishqty:=AdoQry_tmp.fieldbyname('ponoFinishqty').asfloat;
      txt:='select sum(isnull(case when MoNoFinishqty<0 then 0 else MoNoFinishqty end,0)) as MoNoFinishqty from MoLine'
          +' where    MoLine.MoLinestatus=6  '