mrp_qry_analyzermoin.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 297 行

unit Mrp_Qry_AnalyzerMoIn;

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_Mrp_Qry_AnalyzerMoIn = Class(TFrm_Base_Qry)
    AdoQry_MainCountdilivertimes: TIntegerField;
    AdoQry_Mainontimedilivertimes: TIntegerField;
    AdoQry_Maindiliverrate: TStringField;
    AdoQry_MaInOrderno: TIntegerField;
    Label2: TLabel;
    dbtxtmo: TDBText;
    Label3: TLabel;
    AdoQry_MainDeptflag: TStringField;
    dbtxtOrderno: TDBText;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_AnalyzerMoIn: TFrm_Mrp_Qry_AnalyzerMoIn;

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

{ TFrm_Mrp_Qry_AnalyzerMoIn }

procedure TFrm_Mrp_Qry_AnalyzerMoIn.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
var sqltext1,sqltext2,sqltext3,sqltext4:string;
    sdate,edate:string;
begin
  inherited;
try
with TFrm_Mrp_Qry_AnalyzerMoIn_C.Create(self) do
  begin
   showmodal;
    if modalResult<>mrok then exit;
    sdate:=medts.Text;
    edate:=medte.Text;
  end;
finally
   Frm_Mrp_Qry_AnalyzerMoIn_C.Free;
end;
   sqltext2:='select t1.OverPlan,  '
             +'        t1.DeptCode,             '
             +'        t2.*                        '
             +'  into #tmpInvBill                  '
             +' from InvInBill t1,InvInBillline t2    '
            +' where t1.InvBillid=t2.InvBillid        '
             +'  and (t1.BillTypeCode=''0104'' or t1.BillTypeCode=''0105'') '
            // +'  and t1.InvBillStkchck=1               '
             +'  and exists(select * from #tmPMoLine       '
             +'                where t2.mono=#tmPMoLine.mono     '
             +'                 and t2.MoLineno=#tmPMoLine.MoLineno)    ';
   sqltext1:='select mo.modate,          '+
             '         mo.DeptCode,    '+
             '         MoLine.mono,      '+
             '         MoLine.MoLineno  '+
             ' into #tmPMoLine    '+
             ' from MoLine,mo     '+
             ' where mo.mono=MoLine.mono   '+
             '     and  MoLinestatus in(7,8)     '+
             '     and  mo.modate between '''+sdate+''''+' and '''+edate+' 23:59:59'+''''+
             '  Order by mo.DeptCode,MoLine.ItemCode,MoLine.MoLineno  ';

   sqltext3:=' select DeptCode,Countdilivertimes=Count(mono) '
             +' into #tmp1    '
            +' from #tmpInvBill         '
            +' group by DeptCode   '
            +'  select DeptCode,ontimedilivertimes=Count(mono) '
            +' into #tmp2           '
            +'  from #tmpInvBill    '
            +' where OverPlan<>1    '
           +' group by DeptCode                                 '
            +' select t1.DeptCode,t3.DeptName,Deptflag=t1.DeptCode+'' ''+t3.DeptName,'
            +' 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,Dept t3(nolock)  '
            +' where t1.DeptCode*=t2.DeptCode  '
            +'  and  t1.DeptCode*=t3.DeptCode'
            +' Order by diliverrate DESC,t1.DeptCode '
            +' 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 0   ';

  selectfromsql:='select DeptCode,DeptName,Deptflag,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 DeptCode,DeptName,Deptflag,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;

  { sqltext1:='select t1.OverPlan,Deptflag=t1.DeptCode+'' ''+t3.DeptName,t2.*  '+
             '  into #tmpInvBill       '+
            ' from InvInBill t1,InvInBillline t2,Dept t3  '+
            ' where t1.InvBillid=t2.InvBillid     '+
            '   and t1.DeptCode*=t3.DeptCode'+
            '   and exists(select * from #tmPMoLine where t2.mono=#tmPMoLine.mono and t2.MoLineno=#tmPMoLine.MoLineno)';
   sqltext2:='select mo.modate,          '+
             '         MoLine.mono,      '+
             '         MoLine.MoLineno,  '+
             '         mo.DeptCode,      '+
             '         Dept.DeptName,    '+
             '         Deptflag=mo.DeptCode+'' ''+Dept.DeptName'+
//             '         isontime=isnull((select distinct mono from #tmpInvBill where OverPlan<>1 and  mono=MoLine.mono and MoLineno=MoLine.MoLineno),''//aaassss\\'') '+
             ' into #tmPMoLine    '+
             ' from MoLine,mo,Dept     '+
             ' where mo.mono=MoLine.mono   '+
             '     and mo.DeptCode*=Dept.DeptCode'+
             '     and  MoLinestatus=7     '+
             '     and  mo.modate between '''+sdate+''''+' and '''+edate+' 23:59:59'+''''+
             '  Order by mo.DeptCode,MoLine.mono,MoLine.MoLineno  ';
   sqltext3:=' select t1.Deptflag,Countdilivertimes=Count(t1.MoLineno)  '
             +' into #tmp1                                        '
            +' from #tmpInvBill t1,#tmPMoLine t2                                   '
            +' where t2.mono is not null'
            +'   and t2.MoLineno is not null'
            +'   and t1.mono=t2.mono '
            +'   and t1.MoLineno=t2.MoLineno'
            +'   and t1.Deptflag=t2.Deptflag'
            +' group by t1.Deptflag                                '
            +' select t1.Deptflag,ontimedilivertimes=Count(t1.mono)'
            +' into #tmp2                                         '
            +'  from #tmpInvBill   t1,#tmPMoLine t2                               '
            +' where t1.OverPlan<>1                       '
            +' and  t1.mono is not null'
            +'   and t1.MoLineno is not null'
            +'   and t1.mono=t2.mono '
            +'   and t1.MoLineno=t2.MoLineno'
            +' group by t1.Deptflag                                 '
            +' select t1.Deptflag,'
            +' t1.Countdilivertimes,ontimedilivertimes=isnull(t2.ontimedilivertimes,0),diliverrate=convert(decimal(14,0),convert(decimal(14,0),isnull(t2.ontimedilivertimes,0))*100/convert(decimal(14,0),t1.Countdilivertimes))'
            +' into #tmPMoInResult   '
            +' from #tmp1 t1,#tmp2 t2  '
            +' where t1.Deptflag*=t2.Deptflag  '
            +' Order by diliverrate DESC,t1.Deptflag '
            +' select #tmPMoInResult.*,identity(int,1,1) as Orderno '
            +' into #tmpResult '
            +' from #tmPMoInResult';
  selectfromsql:='select Deptflag,Countdilivertimes,ontimedilivertimes,diliverrate=convert(varchAr,diliverrate)+''%'',Orderno '
                 +' from #tmpResult';
  with AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext2);
      Prepared;
      try
      execsql;
      except
      end;

      Close;
      sql.clear;
      sql.Add(sqltext1);
      Prepared;
      try
      execsql;
      except
      end;

      Close;
      sql.clear;
      sql.Add(sqltext3);
      Prepared;
      try
      execsql;
      except
      end;

      Close;
      sql.clear;
      sql.Add('select Deptflag,Countdilivertimes,ontimedilivertimes,diliverrate=convert(varchAr,diliverrate)+''%'',Orderno from #tmpResult ');
      Prepared;
      try
      open;
      except
      end;
       if recordCount=0 then
        begin
         DispInfo('无相关数据！',3);
         self.Close;
        end;
    end; }

end;

procedure TFrm_Mrp_Qry_AnalyzerMoIn.FormDestroy(Sender: TObject);
begin
  inherited;
   Frm_Mrp_Qry_AnalyzerMoIn:=nil;
end;

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

  inherited;

end;

end.