mrp_qry_analyzermo7.pas

一个MRPII系统源代码版本

unit Mrp_Qry_AnalyzerMo7;

Interface

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

Type
  TFrm_Mrp_Qry_AnalyzerMo7 = Class(TFrm_Base_Qry)
    AdoQry_MaInOrderno: TStringField;
    AdoQry_MaInOrderlineno: TIntegerField;
    AdoQry_Mainite_Itemflag: TStringField;
    AdoQry_Mainite_ItemCode: TStringField;
    AdoQry_Mainite_ItemName: TStringField;
    AdoQry_Mainite_Moqty: TFloatField;
    AdoQry_Mainite_Finishqty: TFloatField;
    AdoQry_MainsonItemflag: TStringField;
    AdoQry_MainsonItemCode: TStringField;
    AdoQry_MainsonItemName: TStringField;
    AdoQry_MainsonmoCtrlqty: TFloatField;
    AdoQry_MainInvBillqty: TFloatField;
    AdoQry_Mainite_NoFinishqty: TFloatField;
    AdoQry_Mainson_NoFinishqty: TFloatField;
    AdOCommand: TAdOCommand;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_AnalyzerMo7: TFrm_Mrp_Qry_AnalyzerMo7;

implementation
uses Mrp_Qry_AnalyzerMo7_C,Sys_Global;
{$R *.DFM}

{ TFrm_Mrp_Qry_AnalyzerMo7 }

procedure TFrm_Mrp_Qry_AnalyzerMo7.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
var sItemCode:string;
    eItemCode:string;
    linestatus:integer;
    sqltext:string;
begin
Application.ProcessMessages;
linestatus:=999;
  inherited;
try
 with TFrm_Mrp_Qry_AnalyzerMo7_C.Create(self) do
   begin
     AdoQry_tmp.Connection:=dbconnect;
     showmodal;
     if modalResult=mrok then
      begin
      sItemCode:=edtItemCodes.Text;
      eItemCode:=edtItemCodee.Text;
      case cmbstatus.ItemIndex of
       0: linestatus:=100;
       1: linestatus:=6;
       2: linestatus:=7;
      end;
     end;
 end;
finally
  Frm_Mrp_Qry_AnalyzerMo7_C.Free;
end;
if linestatus=999 then
   abort;
Adocommand.Connection:=dbconnect;
sqltext:='select mnItemList.mono,mnItemList.MoLineno,mnItemList.ItemCode,sum(moCtrlqty) as moCtrlqty '
         +'   into #mnItemList                                       '
         +'   from mnItemList                                        '
         +' where exists(select * from MoLine,mo        '
         +'                where mnItemList.mono=MoLine.mono      '
         +'                  and mnItemList.MoLineno=MoLine.MoLineno  '
         +'                  and (MoLine.MoLinestatus='+inttostr(linestatus)+' or '+inttostr(linestatus)+'=100 )  '
         +'                  and MoLine.ItemCode between '+quotedstr(sItemCode)+' and '+quotedstr(eItemCode)
         +'                  and MoLine.mono=mo.mono )  '
//         +'                  and MoLine.MoLinedate between '+quotedstr(sdate)+' and '+quotedstr(edate)+')'
         +'  group by mono,MoLineno,ItemCode                         '
         +' Order by mono,MoLineno,ItemCode';
//showmessage(sqltext);
Executesql(AdoQry_tmp,sqltext,1);

sqltext:='select opItemList.pono,opItemList.polineno,opItemList.ItemCode,sum(poCtrlqty) as poCtrlqty  '
         +'   into #opItemList                                        '
         +'   from opItemList                                         '
         +' where exists(select * from poline,po        '
         +'                where opItemList.pono=poline.pono      '
         +'                  and opItemList.polineno=poline.polineno  '
         +'                  and (poline.polinestatus='+inttostr(linestatus)+' or '+inttostr(linestatus)+'=100 )  '
         +'                  and poline.ItemCode between '+quotedstr(sItemCode)+' and '+quotedstr(eItemCode)
         +'                  and poline.pono=po.pono   '
         +'                  and po.poType=1 ) '
//         +'                  and poline.polinedate between '+quotedstr(sdate)+' and '+quotedstr(edate)+')'
         +'  group by pono,polineno,ItemCode                          '
         +'  Order by pono,polineno,ItemCode';
Executesql(AdoQry_tmp,sqltext,1);
Application.ProcessMessages;
sqltext:='select #mnItemList.mono as Orderno,  '
         +'       #mnItemList.MoLineno as Orderlineno,                       '
         +'       #mnItemList.ItemCode as sonItemCode,                       '
         +'        #mnItemList.moCtrlqty as sonmoCtrlqty,                    '
         +'        MoLine.ItemCode as ite_ItemCode,                          '
         +'        MoLine.moqty as ite_Moqty,                                '
         +'        MoLine.moqty-MoLine.MoNoFinishqty as ite_Finishqty,        '
         +'        MoLine.MoNoFinishqty as ite_NoFinishqty,                  '
         +'        sum(isnull(InvOutBillline.InvBillqty,0)) as InvBillqty     '
         +'  into #tmpResult                                                  '
         +'  from #mnItemList                                                 '
         +'  join MoLine on #mnItemList.mono=MoLine.mono                      '
         +'         and #mnItemList.MoLineno=MoLine.MoLineno                  '
         +'  left outer join InvOutBill on #mnItemList.mono=InvOutBill.mono   '
         +'         and #mnItemList.MoLineno=InvOutBill.MoLineno              '
         +'         and InvOutBill.BillTypeCode in(''0201'',''0202'')         '
         +'  left outer join InvOutBillline on InvOutBill.InvBillid=InvOutBillline.InvBillid  '
         +'            and #mnItemList.ItemCode=InvOutBillline.ItemCode            '
         +'  group by #mnItemList.mono,#mnItemList.MoLineno,#mnItemList.ItemCode, '
         +'           #mnItemList.moCtrlqty,                                  '
         +'           MoLine.ItemCode,MoLine.moqty,MoLine.MoNoFinishqty       '
      +'  union                                                               '
      +'      select #opItemList.pono as Orderno,                             '
      +'             #opItemList.polineno as Orderlineno,                     '
      +'             #opItemList.ItemCode as sonItemCode,                     '
      +'             #opItemList.poCtrlqty as sonmoCtrlqty,                   '
      +'             poline.ItemCode as ite_ItemCode,                         '
       +'            poline.poqty as ite_Moqty,                               '
      +'             poline.poqty-poline.ponoFinishqty as ite_Finishqty,      '
      +'             poline.ponoFinishqty as ite_NoFinishqty,                 '
      +'             sum(isnull(InvOutBillline.InvBillqty,0)) as InvBillqty   '
      +'        from #opItemList                                              '
      +'        join poline on #opItemList.pono=poline.pono                   '
      +'                and #opItemList.polineno=poline.polineno              '
      +'       left outer join InvOutBill on #opItemList.pono=InvOutBill.pono '
      +'                and #opItemList.polineno=InvOutBill.polineno          '
         +'         and InvOutBill.BillTypeCode in(''0201'',''0202'')         '      +'        left outer join InvOutBillline on InvOutBill.InvBillid=InvOutBillline.InvBillid '
      +'               and #opItemList.ItemCode=InvOutBillline.ItemCode             '
      +'       group by #opItemList.pono,#opItemList.polineno,#opItemList.ItemCode, '
      +'                 #opItemList.poCtrlqty,                          '
      +'             poline.ItemCode,poline.poqty,poline.ponoFinishqty   ' ;
//Executesql(AdoQry_tmp,sqltext,1);
 Adocommand.CommandText:=sqltext;
 Adocommand.Execute;

sqltext:=' select Orderno,    '
         +'        Orderlineno,                               '
         +'        ite_ItemCode+'' ''+t1.ItemName as ite_Itemflag,  '
         +'        ite_ItemCode,                        '
         +'        t1.ItemName as ite_ItemName,'
         +'        ite_Moqty,  '
         +'        ite_Finishqty,                              '
         +'        ite_NoFinishqty,                          '
         +'        sonItemCode+'' ''+t2.ItemName as sonItemflag,     '
         +'        sonItemCode,                                '
         +'        t2.ItemName as sonItemName,                 '
         +'        sonmoCtrlqty,                               '
         +'        InvBillqty,                                  '
         +'        isnull(sonmoCtrlqty,0)-isnull(InvBillqty,0) as son_NoFinishqty '
         +'  into #resu                                        '
         +'  from #tmpResult                                    '
         +'  join Item t1 on #tmpResult.ite_ItemCode=t1.ItemCode '
         +'  join Item t2 on #tmpResult.sonItemCode=t2.ItemCode ';
Executesql(AdoQry_tmp,sqltext,1);

selectfromsql:='select * from #resu';
Executesql(AdoQry_Main,'select * from #resu Order by Orderno,Orderlineno,sonItemCode',0);


end;

procedure TFrm_Mrp_Qry_AnalyzerMo7.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
try
Executesql(AdoQry_tmp,'drop table #mnItemList,#opItemList,#tmpResult,#resu',1);
except
end;

end;

procedure TFrm_Mrp_Qry_AnalyzerMo7.FormDestroy(Sender: TObject);
begin
  inherited;
Frm_Mrp_Qry_AnalyzerMo7:=nil;
end;

end.