unit Trends;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  TeeProcs, TeEngine, Chart, ExtCtrls, DBChart, Series, IBDatabase, IBQuery,
  Buttons, StdCtrls, ComCtrls, pFIBDataSet, pFIBDatabase;

type
  TTrendRec = record
    TagName: string;
    Descr: string;
    TagIdStr: string;
    Table: string;
    Series: TLineSeries;
    QueryIdx: integer;
    ModifExpr: string;
    idx: integer
  end;

  TQueryRec = record
    Table: string;
    SQL: string;
    Query: TpFIBDataset;
    Trans: TpFIBTransaction;
  end;

  TSelectMethod = (smAll, smOptimized, smOptimizedEx);

  TTrendsFrame = class(TFrame)
    Panel1: TPanel;
    Chart: TDBChart;
    Panel2: TPanel;
    btOnLine: TSpeedButton;
    Panel3: TPanel;
    Panel4: TPanel;
    btRefresh: TSpeedButton;
    btMoveTo: TSpeedButton;
    btForward: TSpeedButton;
    btBack: TSpeedButton;
    btDelta: TSpeedButton;
    btZoomOut: TSpeedButton;
    btZoomIn: TSpeedButton;
    EmptyPanel: TPanel;
    DeltaPanel: TPanel;
    MoveToPanel: TPanel;
    DeltaTimePicker: TDateTimePicker;
    btSetDelta: TSpeedButton;
    MoveToDatePicker: TDateTimePicker;
    MoveToTimePicker: TDateTimePicker;
    btSetMoveTo: TSpeedButton;
    lbPeriod: TLabel;
    btPrint: TSpeedButton;
    btOnlyTime: TSpeedButton;
    PrinterSetupDialog: TPrinterSetupDialog;
    DeltaDayEdit: TEdit;
    DateDeltaUpDown: TUpDown;
    procedure btRefreshClick(Sender: TObject);
    procedure btOnLineClick(Sender: TObject);
    procedure btBackClick(Sender: TObject);
    procedure btForwardClick(Sender: TObject);
    procedure btZoomInClick(Sender: TObject);
    procedure btZoomOutClick(Sender: TObject);
    procedure btDeltaClick(Sender: TObject);
    procedure btSetDeltaClick(Sender: TObject);
    procedure btMoveToClick(Sender: TObject);
    procedure btSetMoveToClick(Sender: TObject);
//    procedure btDTPClick(Sender: TObject);
    procedure btOnlyTimeClick(Sender: TObject);
    procedure btPrintClick(Sender: TObject);
    procedure btLastDataClick(Sender: TObject);
  private
    OnLineMode: boolean;
    Qry: array of TQueryRec;
    procedure UpdateTrends;
    procedure SetDatetimepickers;
  public
    Srs: array of TTrendRec;
    DateBeg, DateEnd: TDateTime;
    DateDelta: TDateTime;
    SelectMethod: TSelectMethod;
    procedure Init(MaxY: double);
    procedure CreateTrends(tagnames: string);
    procedure OnLineUpdateTrends;
    procedure DeleteTrends;
  end;


implementation
uses
  connecting,
  RpVisualGlobal,
  DataMod;

var
  DateDeltaT: double;
  OnLineModeT: boolean;
  DateEndT: TDatetime;


{$R *.DFM}

{ TTrendsFrame }


procedure TTrendsFrame.Init(MaxY: double);
begin
  DateDelta := DateDeltaT;

  OnLineMode := OnLineModeT;
  DateEnd := DateEndT;

  Chart.LeftAxis.Maximum := MaxY;

  DeltaPanel.Align := alClient;
  MoveToPanel.Align := alClient;
  DeltaPanel.Visible := false;
  MoveToPanel.Visible := false;

//  SelectMethod := smAll;
//  if dm.pFIBDbArc.QueryValue(
//        'select count(*) from RDB$PROCEDURES where RDB$PROCEDURE_NAME=''GETTREND''', 0)>0
//  then begin
//    SelectMethod := smOptimized;
//    if dm.pFIBDbArc.QueryValue(
//          'select count(*) from RDB$PROCEDURES where RDB$PROCEDURE_NAME=''GETTRENDEXA''', 0)>0
//    then
//      SelectMethod := smOptimizedEx;
//  end;
end;


//============================================================================
// Создание трендов
// Формат tagnames:
//  <имя_тега>;<имя_тега[~<выражение>]>;<имя_тега~[вычисление]>;
//  где
//    выражение - арифметическое пребразование, напр.: TEST.TAG1~/10;
//    вычисление - создание вычисляемого поля, напр.: TEST.TAG1~CAST(^ AS FLOAT)/100;
//                 знак ^ используется для подстановки имени поля тега
//============================================================================
procedure TTrendsFrame.CreateTrends(tagnames: string);
var
  i,j,k: integer;
  s, ss: string;
  sql: string;
  tagid: string;
  descr: string;
  tbl: string;
  tagname: string;
  flag: boolean;
begin
  if not isTagloggingConnected then
    exit;

  // удаление предыдущих данных, если еще не удалены
  DeleteTrends;

  // настройка периода
  if OnLineMode then
    DateEnd := now;

  DateBeg := DateEnd - DateDelta;

  SetDatetimepickers;

  try
    dm.QueryArc.Transaction.StartTransaction;

    // извлекаем имена тегов из параметра
    i := 0;
    s := tagnames;
    tagnames := '';
    while length(s) > 0 do begin
      k := pos(';',s);
      if k=0 then begin
        ss := trim(s);
        s := '';
      end else begin
        ss := trim(copy(s,1,k-1));
        delete(s, 1, k);
      end;

      inc(i);
      SetLength(Srs, i);

      k := pos('~',ss);
      if k>0 then begin
        Srs[i-1].ModifExpr := copy(ss, k+1, length(ss)-k);
        delete(ss, k, length(ss)-k+1);
      end else
        Srs[i-1].ModifExpr := '';
      Srs[i-1].TagName := ss;
      Srs[i-1].TagIdStr := '';
      tagnames := tagnames + ',''' + ss + '''';
      Srs[i-1].idx := i-1;
    end;

    if tagnames <> '' then
      Delete(tagnames, 1, 1);

    // определение наличия в базе хр.процедуры для оптимизированной выборки


    // получаем id тегов из БД
    sql := 'select '+
          '  t.idtag, '+
          '  t.descr, '+
          '  t.tagname, '+
          '  a.arcname, '+
          '  p1.rdb$procedure_id pid1, '+
          '  p2.rdb$procedure_id pid2 '+
          'from taglist t '+
          'join arclist a on a.idarc = t.idarc '+
          'left join RDB$PROCEDURES p1 on p1.RDB$PROCEDURE_NAME=''GETTREND'' '+
          'left join RDB$PROCEDURES p2 on p2.RDB$PROCEDURE_NAME=''GETTRENDEXA'' '+
          'where t.tagname in (' + tagnames + ')';

    dm.QueryArc.SQL.Text := sql;
    dm.QueryArc.ExecQuery;
    if not dm.QueryArc.Eof then begin
      tagId := 'T' + IntToStr(dm.QueryArc.Fields[0].AsInteger);
      descr := trim(dm.QueryArc.Fields[1].AsString);
      tagname := trim(dm.QueryArc.Fields[2].AsString);
      tbl := trim(dm.QueryArc.Fields[3].AsString);
      if not dm.QueryArc.Fields[5].IsNull then
        SelectMethod := smOptimizedEx
      else
      if not dm.QueryArc.Fields[4].IsNull then
        SelectMethod := smOptimized;

      for i:=0 to Length(srs) - 1 do
        if Srs[i].TagName = tagname then begin
          Srs[i].TagIdStr := tagId;
          Srs[i].Descr := descr;
          Srs[i].Table := tbl;
          break;
        end;
    end;
    dm.QueryArc.Close;

    i:=0;
    while i<length(Srs) do begin
      if Srs[i].TagIdStr = '' then begin
        Srs[i] := Srs[length(Srs)-1];
        SetLength(Srs, length(Srs)-1);
        dec(i);
      end else begin
        // Создание серии
        Srs[i].Series := TLineSeries.Create(self);
        if Srs[i].Descr = '' then
          Srs[i].Series.Title := Srs[i].TagName
        else
          Srs[i].Series.Title := Srs[i].Descr;
        Srs[i].Series.Marks.Visible := false;
        Srs[i].Series.ParentChart := Chart;
        Srs[i].Series.LinePen.Width := 2;
        Srs[i].Series.Stairs := true;
      end;
      inc(i);
    end;



//    i:=0;
//    while i<length(Srs) do begin
//      dm.QueryArc.SQL.Text := 'select t.idtag, t.descr, a.arcname ' +
//          'from taglist t, arclist a where t.tagname = ' +
//          #39 + Srs[i].TagName + #39' and t.idarc = a.idarc';
//      dm.QueryArc.ExecQuery;
//      if dm.QueryArc.RecordCount = 0 then begin
//        Srs[i] := Srs[length(Srs)-1];
//        SetLength(Srs, length(Srs)-1);
//        dec(i);
//      end else begin
//        Srs[i].TagIdStr := 'T' + IntToStr(dm.QueryArc.Fields[0].AsInteger);
//        Srs[i].Descr := trim(dm.QueryArc.Fields[1].AsString);
//        Srs[i].Table := trim(dm.QueryArc.Fields[2].AsString);
//
//        // Создание серии
//        Srs[i].Series := TLineSeries.Create(self);
//        if Srs[i].Descr = '' then
//          Srs[i].Series.Title := Srs[i].TagName
//        else
//          Srs[i].Series.Title := Srs[i].Descr;
//        Srs[i].Series.Marks.Visible := false;
//        Srs[i].Series.ParentChart := Chart;
//        Srs[i].Series.LinePen.Width := 2;
//        Srs[i].Series.Stairs := true;
//
//      end;
//      dm.QueryArc.Close;
//      inc(i);
//    end;

    // создание массива транзаций и запросов
    for i:=0 to length(Srs)-1 do begin
      flag := false;

      if SelectMethod = smAll then begin
        flag := true;
        for j:=0 to length(Qry)-1 do
          if Srs[i].Table = Qry[j].Table then begin
            Srs[i].QueryIdx := j;
            flag := false;
          end;
      end;

      if SelectMethod in [smOptimized, smOptimizedEx] then begin
        flag := true;
      end;


      if flag then begin
        k := length(Qry);
        SetLength(Qry, k+1);
        Qry[k].Table := Srs[i].Table;
        Qry[k].Trans := TpFIBTransaction.Create(self);
        Qry[k].Trans.TRParams.Text := dm.trnReadArc.TRParams.Text;
        Qry[k].Trans.TPBMode := dm.trnReadArc.TPBMode;
        Qry[k].Trans.DefaultDatabase := dm.pFIBDbArc;
        Qry[k].Query := TpFIBDataSet.Create(self);
        Qry[k].Query.Database := dm.pFIBDbArc;
        Qry[k].Query.Transaction := Qry[k].Trans;
        Srs[i].QueryIdx := k;
      end;
    end;




    // Создание и открытие запроса
    for j:=0 to length(Qry)-1 do begin

      if SelectMethod = smAll then begin
        s := '';
        for i:=0 to length(Srs)-1 do
          if Srs[i].Table = Qry[j].Table then begin
            ss := Srs[i].ModifExpr;
            k := pos('^', ss);
            if k>0 then begin
              delete(ss, k, 1);
              insert(Srs[i].TagIdStr, ss, k);
              s := s + ss + ' AS ' + Srs[i].TagIdStr+',';
            end else
              s := s + Srs[i].TagIdStr + Srs[i].ModifExpr + ' AS ' + Srs[i].TagIdStr+',';
          end;
        delete(s, length(s), 1);

        Qry[j].Query.SelectSQL.Text := 'SELECT DT,' + s + ' FROM ' + Qry[j].Table + ' WHERE ' +
            '(DT>:DATEBEG) AND (DT<:DATEEND)';
      end;

      if SelectMethod in [smOptimized, smOptimizedEx] then begin
        s := '';
        for i:=0 to length(Srs)-1 do
          if Srs[i].QueryIdx =j then begin

            s := Srs[i].TagName;
            ss := Srs[i].ModifExpr;
            k := pos('^', ss);
            if k>0 then begin
              delete(ss, k, 1);
              insert('FY', ss, k);
              ss := ss + ' AS FY';
            end else
              ss := 'FY' + Srs[i].ModifExpr + ' AS FY';

            Srs[i].TagIdStr := 'FY';

            break;
          end;

        if SelectMethod = smOptimized then
          Qry[j].Query.SelectSQL.Text :=
              'select DT, '+ss+' from gettrend(:DATEBEG, :DATEEND, ''' + s + ''', '''')';

        if SelectMethod = smOptimizedEx then
          Qry[j].Query.SelectSQL.Text :=
              'select DT, '+ss+' from gettrendexa(:DATEBEG, :DATEEND, ''' + s + ''', '''', 400)';
      end;


      Qry[j].Query.Transaction.StartTransaction;
      Qry[j].Query.Prepare;
      Qry[j].Query.ParamByName('DATEBEG').AsDateTime := DateBeg;
      Qry[j].Query.ParamByName('DATEEND').AsDateTime := DateEnd;

      Qry[j].Query.Open;
    end;

    // Подключение выборок к сериям
    for i:=0 to length(Srs)-1 do begin
      Srs[i].Series.DataSource := Qry[Srs[i].QueryIdx].Query;
      Srs[i].Series.YValues.ValueSource := Srs[i].TagIdStr;
      Srs[i].Series.XValues.ValueSource := 'DT';
      Srs[i].Series.XValues.DateTime := true;
      Srs[i].Series.CheckDatasource;
    end;

    dm.QueryArc.Transaction.Commit;
  except
    dm.QueryArc.Transaction.Active := false;
  end;

  btOnLine.Down := OnLineMode;
  btOnLineClick(nil);
end;


//============================================================================
// Обновление трендов
//============================================================================
procedure TTrendsFrame.UpdateTrends;
var
  j: integer;
  ymax,ymin: double;
begin
  if not isTagloggingConnected then exit;

  with Chart do if (Zoomed)  then begin
    ymax := LeftAxis.Maximum;
    ymin := LeftAxis.Minimum;
    UndoZoom;
    LeftAxis.SetMinMax(ymin, ymax);
  end;

    DateBeg := DateEnd - DateDelta;

    SetDatetimepickers;

    for j:=0 to length(Qry)-1 do begin
      if Qry[j].Query.Transaction.Active then
        Qry[j].Query.Transaction.Commit;

      Qry[j].Query.ParamByName('DATEBEG').AsDateTime := DateBeg;
      Qry[j].Query.ParamByName('DATEEND').AsDateTime := DateEnd;

      try
        Qry[j].Query.Transaction.StartTransaction;
        Qry[j].Query.Open;
      except
      end;

    end;

end;

procedure TTrendsFrame.OnLineUpdateTrends;
begin
  if OnLineMode then begin
    DateEnd := now;
    DateEndT := DateEnd;
    UpdateTrends;
  end;  
end;


//============================================================================
// Удаление трендов
//============================================================================
procedure TTrendsFrame.DeleteTrends;
var
  i: integer;
begin
  while Chart.SeriesList.Count >0 do
    Chart.SeriesList.Series[0].Free;

  for i:=0 to length(Qry)-1 do begin
    Qry[i].Query.Active := false;
    Qry[i].Trans.Active := false;
    Qry[i].Query.Free;
    Qry[i].Trans.Free;
  end;
  Qry := nil;
end;


//----------------------------------------------------------------------------

//============================================================================
// OnLine и Refresh
//============================================================================
procedure TTrendsFrame.btRefreshClick(Sender: TObject);
begin
  UpdateTrends;
end;

procedure TTrendsFrame.btOnLineClick(Sender: TObject);
begin
  OnLineMode := btOnLine.Down;
  OnLineModeT := OnLineMode;
  btBack.Enabled := not OnLineMode;
  btForward.Enabled := not OnLineMode;
  btMoveTo.Enabled := not OnLineMode;
end;

//============================================================================
// Навигация
//============================================================================
procedure TTrendsFrame.btBackClick(Sender: TObject);
begin
  with Chart do if Zoomed then begin
    DateEnd := BottomAxis.Maximum;
  end;

  DateEnd := DateEnd - DateDelta;
  DateEndT := DateEnd;
  UpdateTrends;
end;

procedure TTrendsFrame.btForwardClick(Sender: TObject);
begin
  with Chart do if Zoomed then begin
    DateEnd := BottomAxis.Maximum;
  end;

  DateEnd := DateEnd + DateDelta;
  DateEndT := DateEnd;
  UpdateTrends;
end;

//============================================================================
// ZOOM
//============================================================================
procedure TTrendsFrame.btZoomInClick(Sender: TObject);
begin
  Chart.ZoomPercent(120.0);
end;

procedure TTrendsFrame.btZoomOutClick(Sender: TObject);
begin
  Chart.ZoomPercent(80.0);
end;


//============================================================================
// Установка DELTA
//============================================================================
procedure TTrendsFrame.btDeltaClick(Sender: TObject);
begin
  DateDeltaUpDown.Position := trunc(DateDelta);
  DeltaTimePicker.DateTime := DateDelta;
  DeltaPanel.Visible := true;
end;

procedure TTrendsFrame.btSetDeltaClick(Sender: TObject);
begin
  DateDelta := DateDeltaUpDown.Position;
  ReplaceTime(DateDelta, DeltaTimePicker.DateTime);
  DateDeltaT := DateDelta;
  DeltaPanel.Visible := false;
  UpdateTrends;
end;


//============================================================================
// Переход к моменту
//============================================================================
procedure TTrendsFrame.btMoveToClick(Sender: TObject);
begin
  MoveToDatePicker.DateTime := DateEnd - (DateDelta / 2);
  MoveToTimePicker.DateTime := DateEnd - (DateDelta / 2);
  MoveToPanel.Visible := true;
end;

procedure TTrendsFrame.btSetMoveToClick(Sender: TObject);
begin
  DateEnd := MoveToDatePicker.DateTime;
  ReplaceTime(DateEnd, MoveToTimePicker.DateTime);
  DateEnd := DateEnd + (DateDelta / 2);
  DateEndT := DateEnd;
  MoveToPanel.Visible := false;
  UpdateTrends;
end;

procedure TTrendsFrame.btOnlyTimeClick(Sender: TObject);
begin
  if btOnlyTime.Down then
    Chart.BottomAxis.DateTimeFormat := 'hh:mm:ss'
  else
    Chart.BottomAxis.DateTimeFormat := 'dd-mm-yy hh:mm:ss';
end;

procedure TTrendsFrame.btPrintClick(Sender: TObject);
begin
  PrinterSetupDialog.Execute;

  With Chart do try
    Title.Visible := true;
    PrintMargins.Left := 60 ;
    PrintMargins.Top  := 5 ;
    PrintMargins.Right:= 10 ;
    PrintMargins.Bottom:= 75 ;
    Print;
  finally
    Chart.Title.Visible := false;
  end;
end;


procedure TTrendsFrame.SetDatetimepickers;
begin
  lbPeriod.Caption := 'Период с ' + DateToStr(DateBeg) + ' по ' + DateToStr(DateEnd);
  lbPeriod.Visible := true;
//  dtBeg.DateTime := DateBeg;
//  tmBeg.DateTime := DateBeg;
//  dtEnd.DateTime := DateEnd;
//  tmEnd.DateTime := DateEnd;
end;

procedure TTrendsFrame.btLastDataClick(Sender: TObject);
var
  flag: boolean;
  dt: TDateTime;
  j: integer;
begin
  if not isTagloggingConnected then exit;

  flag := false;
  dt := 0;

    for j:=0 to length(Qry)-1 do begin
      dm.QueryArc.SQL.Text := //Qry[j].Query.SelectSQL.Text;
              'select first 1 DT from gettrend(''01.01.1980 00:00:00'', ''01.01.2020 00:00:00'', ''' +
              Srs[j].TagName + ''', '''')';
      try
        dm.QueryArc.Transaction.StartTransaction;
        dm.QueryArc.ExecQuery;
        if not dm.QueryArc.Eof then begin

          if dt < dm.QueryArc.Fields[0].AsDateTime then begin
            dt := dm.QueryArc.Fields[0].AsDateTime;
            flag := true;
          end;

        end;
        dm.QueryArc.Transaction.Commit;
      except
        dm.QueryArc.Transaction.Active := false;
      end;
    end;


  if flag then begin
    DateEnd := dt;
    DateEndT := dt;
    UpdateTrends;
  end;
end;

initialization
  DateEndT := now;
  OnLineModeT := true;
  DateDeltaT := StrToDateTime('01.01.2000 00:02:30') - StrToDateTime('01.01.2000 00:00:00')

end.
