unit AggregateAndCalc;

interface

uses
{$IFDEF MSWINDOWS}
  Windows, Messages,
{$ENDIF}
  SysUtils, Variants, Classes,
  Graphics, Controls, Forms, Dialogs, DemoFrame, StdCtrls,
  DB, Grids, DBGrids, ExtCtrls, DBCtrls, Buttons,
  Mask, MemDS, DBAccess, MyAccess;

type
  TAggregateFrame = class(TDemoFrame)
    pnTop: TPanel;
    pnLeft: TPanel;
    pnMain: TPanel;
    DBNavigator1: TDBNavigator;
    DBGrid1: TDBGrid;
    sbOpen: TSpeedButton;
    sbClose: TSpeedButton;
    rgFields: TRadioGroup;
    pnBottom: TPanel;
    lbSum: TLabel;
    edFilter: TEdit;
    cbFilter: TCheckBox;
    DataSource: TDataSource;
    Label1: TLabel;
    dbeAgg01: TDBEdit;
    edAgg02: TEdit;
    dbeAgg00: TDBEdit;
    Label2: TLabel;
    qNone: TMyQuery;
    qCalculated: TMyQuery;
    qInternalCalc: TMyQuery;
    qAggregate: TMyQuery;
    qAggregateAgg: TAggregateField;
    procedure sbOpenClick(Sender: TObject);
    procedure cbFilterClick(Sender: TObject);
    procedure rgFieldsClick(Sender: TObject);
    procedure qCalculatedCalcFields(DataSet: TDataSet);
    procedure sbCloseClick(Sender: TObject);
    procedure qInternalCalcCalcFields(DataSet: TDataSet);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure Initialize; override;
    procedure SetDebug(Value: boolean); override;
  end;

implementation

{$R *.dfm}

uses MyDacDemoForm;

const 
  GroupLevel = 0;

procedure TAggregateFrame.cbFilterClick(Sender: TObject);
begin
  DataSource.DataSet.Filtered := cbFilter.Checked;
  DataSource.DataSet.Filter := edFilter.Text;
end;

procedure TAggregateFrame.qCalculatedCalcFields(DataSet: TDataSet);
begin
  qCalculated.FieldByName('Calc01').AsFloat := qCalculated.FieldByName('sal').AsFloat * 0.2;
end;

procedure TAggregateFrame.qInternalCalcCalcFields(DataSet: TDataSet);
begin
  qInternalCalc.FieldByName('ICalcOnCalculated').AsFloat := qInternalCalc.FieldByName('sal').AsFloat * 0.2;
end;

procedure TAggregateFrame.rgFieldsClick(Sender: TObject);
var
  oField: TField;
  List: TStrings;
  i, n: integer;
begin
  sbCloseClick(nil);  
  case rgFields.ItemIndex of
    0: DataSource.DataSet := qNone;
    1: begin
      DataSource.DataSet := qCalculated;
      List := TStringList.Create;
      try
        qCalculated.GetFieldNames(List);
        //Calculated example
        if List.IndexOf('Calc01') < 0 then begin
          qCalculated.FieldDefs.Updated := False;
          qCalculated.FieldDefs.Update;
          for i := 0 to qCalculated.FieldDefs.Count - 1 do
            qCalculated.FieldDefs[i].CreateField(Self);

          oField := TFloatField.Create(qCalculated);
          oField.FieldName := 'Calc01';
          oField.FieldKind := fkCalculated; // or fkInternalCalc
          oField.DataSet := qCalculated;
        end;
      finally
        List.Free;
      end; 
    end;
    2: begin    
      DataSource.DataSet := qInternalCalc;
      
      List := TStringList.Create;
      try
        qInternalCalc.GetFieldNames(List);
        //InternalCalc example 1
        if List.IndexOf('ICalcOnCalculated') < 0 then begin
          qInternalCalc.FieldDefs.Updated := False;
          qInternalCalc.FieldDefs.Update;
          for i := 0 to qInternalCalc.FieldDefs.Count - 1 do
            qInternalCalc.FieldDefs[i].CreateField(Self);
          oField := TFloatField.Create(qInternalCalc);
          oField.FieldName := 'ICalcOnCalculated';
          oField.FieldKind := fkInternalCalc; // or fkCalculated
          oField.DataSet := qInternalCalc;
        end;
     
        //InternalCalc example 2
        qInternalCalc.GetFieldNames(List);
        if List.IndexOf('ICalcExpression') < 0 then begin
          oField := TFloatField.Create(qInternalCalc);
          oField.FieldName := 'ICalcExpression';
          oField.FieldKind := fkInternalCalc;
          oField.DefaultExpression := 'sal * ' + FloatToStr(0.1);
          oField.DataSet := qInternalCalc;
        end;
      finally
        List.Free;
      end;
    end;
    3: begin
      DataSource.DataSet := qAggregate;

      //aggregate example1
      n := qAggregate.Aggregates.IndexOf('Agg01');
      if n < 0 then begin
        qAggregate.FieldDefs.Updated := False;
        qAggregate.FieldDefs.Update;
        for i := 0 to qAggregate.FieldDefs.Count - 1 do
          qAggregate.FieldDefs[i].CreateField(Self);

        oField := TAggregateField.Create(qAggregate);
        oField.FieldName := 'Agg01';
        TAggregateField(oField).Expression := 'SUM(Sal * ' + FloatToStr(1.1) + ')';
        TAggregateField(oField).Active := True;
        oField.DataSet := qAggregate;       
      end;        
      dbeAgg01.DataField := 'Agg01';
      
     //aggregate example2
      n := qAggregate.Aggregates.IndexOf('Agg02');
      if n < 0 then begin
        with qAggregate.Aggregates.Add('Agg02') do begin
          Expression := 'SUM(Sal * ' + FloatToStr(1.2) + ')';
          Active := True;
        end;
      end;

      qAggregate.AggregatesActive := True;
      sbOpenClick(nil);
      pnBottom.Visible := True;
    end;
  end;
  sbOpenClick(nil);
  cbFilterClick(nil);
end;

procedure TAggregateFrame.sbCloseClick(Sender: TObject);
begin
  if pnBottom.Visible then begin
    pnBottom.Visible := False;
    dbeAgg00.DataField := '';
    dbeAgg01.DataField := '';
  end;
  DataSource.DataSet.Close;
end;

procedure TAggregateFrame.sbOpenClick(Sender: TObject);
begin
  if DataSource.DataSet.Active then
    Exit;
  cbFilterClick(nil);
  DataSource.DataSet.Open;
  if rgFields.ItemIndex = 3 then begin  
    pnBottom.Visible := True; 
    dbeAgg00.DataField := 'Agg00';
    if qAggregate.Aggregates.IndexOf('Agg01') > 0 then
      dbeAgg01.DataField := 'Agg01';
    DataSourceDataChange(nil, nil);
  end;
end;

procedure TAggregateFrame.DataSourceDataChange(Sender: TObject; Field: TField);
var
  n: integer;
begin
  if pnBottom.Visible then begin
    n := qAggregate.Aggregates.IndexOf('Agg02');
    edAgg02.Text := qAggregate.Aggregates[n].FieldValue;
  end;
end;

procedure TAggregateFrame.Initialize;
begin
  inherited;
  qNone.Connection := Connection as TMyConnection;
  qCalculated.Connection := Connection as TMyConnection;
  qInternalCalc.Connection := Connection as TMyConnection;
  qAggregate.Connection := Connection as TMyConnection;
end;

procedure TAggregateFrame.SetDebug(Value: boolean);
begin
  inherited;
  qNone.Debug := Value;
  qCalculated.Debug := Value;
  qInternalCalc.Debug := Value;
  qAggregate.Debug := Value;
end;

end.
