pm_qry_analyzervendorsupply.pas

一个MRPII系统源代码版本

unit Pm_Qry_AnalyzerVendorSupply;

Interface

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

Type
  TFrm_Pm_Qry_AnalyzerVendorSupply = Class(TFrm_Base_Qry)
    AdoQry_MaInvendorCode: TStringField;
    AdoQry_MaInvendorName: TStringField;
    AdoQry_MainCountdilivertimes: TIntegerField;
    AdoQry_Mainontimedilivertimes: TIntegerField;
    AdoQry_Maindiliverrate: TStringField;
    AdoQry_MaInvendorflag: TStringField;
    Label3: TLabel;
    dbtxtOrderno: TDBText;
    dbtxtVendorflag: TDBText;
    Label2: TLabel;
    AdoQry_MaInOrderno: TIntegerField;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Pm_Qry_AnalyzerVendorSupply: TFrm_Pm_Qry_AnalyzerVendorSupply;

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

{ TFrm_Pm_Qry_AnalyzerVendorSupply }

procedure TFrm_Pm_Qry_AnalyzerVendorSupply.InitForm(
  AdOConnection: TAdOConnection; ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4:string;
    sdate,edate:string;
begin
  inherited;
try
with TFrm_Pm_Qry_AnalyzerVendorSupply_C.Create(self) do
  begin
   showmodal;
    if modalResult<>mrok then exit;
    sdate:=medts.Text;
    edate:=medte.Text;
  end;
finally
   Frm_Pm_Qry_AnalyzerVendorSupply_C.Free;
end;
   sqltext2:='select t1.OverPlan,  '
             +'        t1.VendorCode,             '
             +'        t2.*                        '
             +'  into #tmpInvBill                  '
             +' from InvInBill t1,InvInBillline t2    '
             +' where t1.InvBillid=t2.InvBillid        '
             +'   and t1.realBillflag=1  '
   //             +'  and t1.InvBillStkchck=1               '
             +'  and exists(select * from #tmppoline       '
             +'                where t2.pono=#tmppoline.pono     '
             +'                 and t2.polineno=#tmppoline.polineno)    ';
   sqltext1:='select po.podate,          '+
             '         po.VendorCode,    '+
             '         poline.pono,      '+
             '         poline.polineno  '+
             ' into #tmppoline    '+
             ' from poline,po     '+
             ' where po.pono=poline.pono   '+
             '     and  polinestatus in(6,7,8)     '+
             '     and  po.podate between '''+sdate+''''+' and '''+edate+' 23:59:59'+''''+
             '  Order by po.VendorCode,poline.ItemCode,poline.polineno  ';
   sqltext3:=' select VendorCode,Countdilivertimes=Count(pono) '
             +' into #tmp1    '
            +' from #tmpInvBill         '
            +' group by VendorCode   '
            +'  select VendorCode,ontimedilivertimes=Count(pono) '
            +' into #tmp2           '
            +'  from #tmpInvBill    '
            +' where OverPlan<>1    '
           +' group by VendorCode                                 '
            +' select t1.VendorCode,t3.VendorName,Vendorflag=t1.VendorCode+'' ''+t3.VendorName,'
            +' t1.Countdilivertimes,ontimedilivertimes=isnull(t2.ontimedilivertimes,0),diliverrate=convert(decimal(12,2),convert(decimal(12,2),isnull(t2.ontimedilivertimes,0))*100/convert(decimal(12,2),t1.Countdilivertimes)),'
            +' Orderno=1 '
            +' into #tmpResult   '
            +' from #tmp1 t1,#tmp2 t2,Vendor t3(nolock)  '
            +' where t1.VendorCode*=t2.VendorCode  '
            +'  and  t1.VendorCode*=t3.VendorCode'
            +' Order by diliverrate DESC,t1.VendorCode '
            +' select top 0 * into #tmpres from #tmpResult';
 sqltext4:=' declAre @Count int,                              '
           +'          @tmprate float,                         '
           +'          @tmpOrder tinyint                       '
           +'   select @Count=(select Count(*) from #tmpResult) '
           +'  set rowCount 1                                '
           +'    select @tmprate=diliverrate from #tmpResult '
           +'    select @tmpOrder=Orderno from #tmpResult    '
           +'    insert into #tmpres                         '
           +'    select * from #tmpResult                    '
            +'   delete from #tmpResult'
           +' while @Count-1>0                               '
           +'  begin                                         '
           +'   if exists(select * from #tmpResult where diliverrate=@tmprate)'
           +'        update #tmpResult                     '
           +'          set Orderno=@tmpOrder              '
           +'   else update #tmpResult                    '
           +'           set Orderno=@tmpOrder+1            '
           +'    select @tmprate=diliverrate from #tmpResult  '
           +'    select @tmpOrder=Orderno from #tmpResult '
           +'   insert into #tmpres      '
           +'   select * from #tmpResult '
           +'   delete from #tmpResult  '
           +'   select @Count=@Count-1 '
           +' end                      '
           +' set rowCount 999999999   ';

  selectfromsql:='select VendorCode,VendorName,Vendorflag,Countdilivertimes,ontimedilivertimes,diliverrate=convert(varchAr,diliverrate)+''%'',Orderno from #tmpres ';
  with AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext1);
      Prepared;
      try
      execsql;
      except
      end;

      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(sqltext4);
      Prepared;
      try
      execsql;
      except
      end;

      Close;
      sql.clear;
      sql.Add('select VendorCode,VendorName,Vendorflag,Countdilivertimes,ontimedilivertimes,diliverrate=convert(varchAr,diliverrate)+''%'',Orderno  from #tmpres ');
      Prepared;
      try
      open;
      except
      end;

      if recordCount=0 then
        begin
         DispInfo('无该统计日期期间的供应商交货数据！',3);
         self.Close;
        end;
    end;

end;

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

end;

procedure TFrm_Pm_Qry_AnalyzerVendorSupply.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Pm_Qry_AnalyzerVendorSupply:=nil;
end;

end.