unit CubeDefForm_SRC;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, db,zCube_TLB, DBTables, StdCtrls, ExtCtrls;

type

  TDimDef = class
  public
   DataType : TFieldType;
   isBranch : boolean;
   FieldName : string;
   TableName : string;
   KeyField : string;
   LookupField : string;
   MasterTable : string;
   AliasName : string;
//   Fields : TStringList;
   constructor Create;
   destructor Destroy; override;
  end;

  TMeasureDef = class
  public
   DataType : TFieldType;
   FieldName : string;
   AliasName : string;
   MeasureType : TMeasureType;
  end;

  TCubeDef = class
  public
   Name : string;
   AliasName : string;
   FactTable : string;
   SQL : string;
   Dimensions : TStringList;
   Measures : TStringList;
   Fields : TStringList;
   constructor Create;
   destructor Destroy; override;
   procedure Clear;
  end;


  TCubeDefForm = class(TForm)
    Pages: TPageControl;
    DSSheet: TTabSheet;
    DimSheet: TTabSheet;
    GroupBox1: TGroupBox;
    NameEdit: TEdit;
    Panel1: TPanel;
    GroupBox2: TGroupBox;
    AliasCB: TComboBox;
    GroupBox3: TGroupBox;
    FactTableCB: TComboBox;
    GroupBox4: TGroupBox;
    FieldsLB: TListBox;
    DimensionsLB: TListBox;
    MeasuresLB: TListBox;
    AddDimBtn: TButton;
    DelDimBtn: TButton;
    AddMeasureBtn: TButton;
    DelMeasureBtn: TButton;
    GroupBox5: TGroupBox;
    Dimensions2LB: TListBox;
    Panel2: TPanel;
    Label1: TLabel;
    DimAliasEdit: TEdit;
    Label2: TLabel;
    DimTableCB: TComboBox;
    Label3: TLabel;
    DimKeyCB: TComboBox;
    Label4: TLabel;
    DimLookupCB: TComboBox;
    Label5: TLabel;
    DimMasterCB: TComboBox;
    GroupBox6: TGroupBox;
    Panel3: TPanel;
    Button5: TButton;
    Button6: TButton;
    Measures2LB: TListBox;
    Panel4: TPanel;
    GroupBox7: TGroupBox;
    MeasureAliasEdit: TEdit;
    GroupBox8: TGroupBox;
    mtSummaCB: TCheckBox;
    mtCountCB: TCheckBox;
    mtAverageCB: TCheckBox;
    Label6: TLabel;
    Label7: TLabel;
    DB: TDatabase;
    Query: TQuery;
    Label8: TLabel;
    DimBaseCB: TComboBox;
    procedure DSSheetShow(Sender: TObject);
    procedure FactTableCBClick(Sender: TObject);
    procedure AliasCBClick(Sender: TObject);
    procedure FieldsLBData(Control: TWinControl; Index: Integer;
      var Data: String);
    procedure DimensionsLBData(Control: TWinControl; Index: Integer;
      var Data: String);
    procedure MeasuresLBData(Control: TWinControl; Index: Integer;
      var Data: String);
    procedure FormCreate(Sender: TObject);
    procedure AddDimBtnClick(Sender: TObject);
    procedure AddMeasureBtnClick(Sender: TObject);
    procedure DelDimBtnClick(Sender: TObject);
    procedure DelMeasureBtnClick(Sender: TObject);
    procedure PagesChanging(Sender: TObject;
      var AllowChange: Boolean);
    procedure DimSheetShow(Sender: TObject);
    procedure Dimensions2LBData(Control: TWinControl; Index: Integer;
      var Data: String);
    procedure Measures2LBData(Control: TWinControl; Index: Integer;
      var Data: String);
    procedure Dimensions2LBDrawItem(Control: TWinControl; Index: Integer;
      Rect: TRect; State: TOwnerDrawState);
    procedure Measures2LBDrawItem(Control: TWinControl; Index: Integer;
      Rect: TRect; State: TOwnerDrawState);
    procedure DimTableCBClick(Sender: TObject);
    procedure DimKeyCBClick(Sender: TObject);
    procedure DimLookupCBClick(Sender: TObject);
    procedure DimMasterCBClick(Sender: TObject);
    procedure mtSummaCBClick(Sender: TObject);
    procedure mtCountCBClick(Sender: TObject);
    procedure mtAverageCBClick(Sender: TObject);
    procedure MeasureAliasEditClick(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure DimAliasEditChange(Sender: TObject);
    procedure DimMasterCBChange(Sender: TObject);
    procedure DimBaseCBChange(Sender: TObject);
  private
    { Private declarations }
    procedure CheckBefore;
  public
    { Public declarations }
    Cubedef : TCubeDef;
  end;

var
  CubeDefForm: TCubeDefForm;

implementation

uses ZCubeExplr_SRC, SQLEditForm_SRC;

{$R *.dfm}

constructor TDimDef.Create;
begin
//Fields := TStringList.Create;
end;

destructor TDimDef.Destroy;
var
   i : integer;
begin
//  Fields.Free;
  inherited;
end;


procedure TCubeDef.Clear;
var
   i : integer;
begin
while Dimensions.Count > 0 do
      begin
      TDimDef(Dimensions.Objects[Dimensions.Count-1]).Free;
      Dimensions.Delete(Dimensions.Count-1);
      end;
while Measures.Count > 0 do
      begin
      TMeasureDef(Measures.Objects[Measures.Count-1]).Free;
      Measures.Delete(Measures.Count-1);
      end;
AliasName := '';
FactTable := '';
SQL := '';
end;

constructor TCubeDef.Create;
begin
Dimensions := TStringList.Create;
Measures := TStringList.Create;
Fields := TStringList.Create;
AliasName := '';
FactTable := '';
SQL := '';
end;


destructor TCubeDef.Destroy;
begin
Dimensions.Free;
Measures.Free;
Fields.Free;
inherited;
end;


procedure TCubeDefForm.DSSheetShow(Sender: TObject);
var
   i : integer;
begin
if Cubedef = nil then
   CubeDef := TCubeDef.Create;
NameEdit.Text := CubeDef.Name;
AliasCB.Text := CubeDef.AliasName;
AliasCBClick(nil);
if CubeDef.SQL = '' then
   FactTableCB.ItemIndex := FactTableCB.Items.IndexOf(CubeDef.FactTable)
else
   FactTableCB.ItemIndex := 0;

FieldsLB.Count := CubeDef.Fields.Count;
DimensionsLB.Count := CUbedef.Dimensions.Count;
MeasuresLB.Count := CubeDef.Measures.Count;
end;

procedure TCubeDefForm.FactTableCBClick(Sender: TObject);
var
   i : integer;
begin
   CubeDef.Dimensions.Clear;
   CubeDef.Measures.Clear;
   CubeDef.Fields.Clear;
   if FactTableCB.ItemIndex > 0 then
      begin
      CubeDef.FactTable := FactTableCB.Text;
      Session.GetFieldNames(Cubedef.AliasName,Cubedef.FactTable,CubeDef.Fields);
      end
   else
      begin
      SQLEditForm := TSQLEditForm.Create(Self);
      if Cubedef.SQL <> '' then
         SQLEditForm.Memo1.Text := Cubedef.SQL;
      if SQLEditForm.ShowModal = mrOk then
         begin
         Cubedef.SQL := SQLEditForm.Memo1.Text;
         Cubedef.FactTable := zExplorerForm.ProcessSQL(Cubedef.AliasName,Cubedef.SQL);
         Query.SQL.Clear;
         Query.SQL.Add('select * from ''' + cubedef.FactTable + '''');
         Query.OPen;
         for i := 0 to Query.FieldCount - 1 do
             CubeDef.Fields.Add(Query.Fields[i].FieldName);
         Query.Close;
         end;
      end;
   FieldsLB.Count := CubeDef.Fields.Count;
end;

procedure TCubeDefForm.AliasCBClick(Sender: TObject);
begin
if Sender <> nil then
   begin
   CubeDef.Dimensions.Clear;
   CubeDef.Measures.Clear;
   CubeDef.Fields.Clear;
   CubeDef.AliasName := AliasCB.Text;
   CubeDef.SQL := '';
   CubeDef.FactTable := '';
   end;
DB.AliasName := AliasCB.Text;
FactTableCb.Clear;
Session.GetTableNames(AliasCB.Text,'',False,False,FactTableCb.Items);
FactTableCB.Items.Insert(0,'Use SQL expression');
end;

procedure TCubeDefForm.FieldsLBData(Control: TWinControl; Index: Integer;
  var Data: String);
begin
Data := CubeDef.Fields.Strings[Index];
end;

procedure TCubeDefForm.DimensionsLBData(Control: TWinControl;
  Index: Integer; var Data: String);
begin
Data := CubeDef.Dimensions.Strings[Index];
end;

procedure TCubeDefForm.MeasuresLBData(Control: TWinControl; Index: Integer;
  var Data: String);
begin
Data := CubeDef.Measures.Strings[Index];
end;

procedure TCubeDefForm.FormCreate(Sender: TObject);
begin
Pages.ActivePage := DSSheet;
Session.GetAliasNames(AliasCb.Items);
CubeDef := nil;
end;

procedure TCubeDefForm.AddDimBtnClick(Sender: TObject);
var
   s : string;
   d : TDimDef;
begin
if FieldsLB.ItemIndex >= 0 then
   begin
   s := FieldsLB.Items[FieldsLB.ItemIndex];
   d := TDimDef.Create;
   d.FieldName := s;
   CubeDef.Dimensions.AddObject(s,d);
   CubeDef.Fields.Delete(CubeDef.Fields.IndexOf(s));
   FieldsLB.Count := CubeDef.Fields.Count;
   DimensionsLB.Count := CubeDef.Dimensions.Count;
   end;
end;

procedure TCubeDefForm.AddMeasureBtnClick(Sender: TObject);
var
   s : string;
   m : TMeasureDef;
begin
if FieldsLB.ItemIndex >= 0 then
   begin
   s := FieldsLB.Items[FieldsLB.ItemIndex];
   m := TMeasureDef.Create;
   m.FieldName := s;
   CubeDef.Measures.AddObject(s,m);
   CubeDef.Fields.Delete(CubeDef.Fields.IndexOf(s));
   FieldsLB.Count := CubeDef.Fields.Count;
   MeasuresLB.Count := CubeDef.Measures.Count;
   end;
end;

procedure TCubeDefForm.DelDimBtnClick(Sender: TObject);
var
   s : string;
begin
if DimensionsLB.ItemIndex >= 0 then
   begin
   s := DimensionsLB.Items[DimensionsLB.ItemIndex];
   CubeDef.Fields.Add(s);
   TDimDef(CubeDef.Dimensions.Objects[CubeDef.Dimensions.IndexOf(s)]).Free;
   CubeDef.Dimensions.Delete(CubeDef.Dimensions.IndexOf(s));
   FieldsLB.Count := CubeDef.Fields.Count;
   DimensionsLB.Count := CubeDef.Dimensions.Count;
   end;

end;

procedure TCubeDefForm.DelMeasureBtnClick(Sender: TObject);
var
   s : string;
begin
if MeasuresLB.ItemIndex >= 0 then
   begin
   s := MeasuresLB.Items[MeasuresLB.ItemIndex];
   CubeDef.Fields.Add(s);
   TMeasureDef(CubeDef.Measures.Objects[CubeDef.Measures.IndexOf(s)]).Free;
   CubeDef.Measures.Delete(CubeDef.Measures.IndexOf(s));
   FieldsLB.Count := CubeDef.Fields.Count;
   MeasuresLB.Count := CubeDef.Measures.Count;
   end;
end;

procedure TCubeDefForm.PagesChanging(Sender: TObject;
  var AllowChange: Boolean);
begin
if (CubeDef.Dimensions.Count = 0) or
   (CubeDef.Measures.Count = 0) and
   (Pages.TabIndex = 0) then
   begin
   ShowMessage('Could not continue. Please check you data!');
   AllowChange := False;
   end;
end;

procedure TCubeDefForm.DimSheetShow(Sender: TObject);
begin
Dimensions2LB.Count := CubeDef.Dimensions.Count;
Measures2LB.Count := CubeDef.Measures.Count;
DimTableCB.Clear;
DimMasterCB.Clear;
Session.GetTableNames(CubeDef.AliasName,'',False,False,DimTableCB.Items);
Session.GetTableNames(CubeDef.AliasName,'',False,False,DimMasterCB.Items);
end;

procedure TCubeDefForm.Dimensions2LBData(Control: TWinControl;
  Index: Integer; var Data: String);
begin
Data := CubeDef.Dimensions.Strings[Index];
end;

procedure TCubeDefForm.Measures2LBData(Control: TWinControl;
  Index: Integer; var Data: String);
begin
Data := CubeDef.Measures.Strings[Index];
end;

procedure TCubeDefForm.Dimensions2LBDrawItem(Control: TWinControl;
  Index: Integer; Rect: TRect; State: TOwnerDrawState);
begin
if Dimensions2LB.ItemIndex = -1 then
   Dimensions2LB.ItemIndex := 0;
if Dimensions2LB.ItemIndex = Index then
   begin
   DimAliasEdit.Text := TDimDef(CubeDef.Dimensions.Objects[Index]).AliasName;
   if DimAliasEdit.Text = '' then
      begin
      DimAliasEdit.Text := TDimDef(CubeDef.Dimensions.Objects[Index]).Fieldname;
      DimAliasEditChange(nil);
      end;
   DimTableCB.ItemIndex := DimTableCB.Items.IndexOf(TDimDef(CubeDef.Dimensions.Objects[Index]).TableName);
   if DimTableCB.ItemIndex < 0 then
      begin
      DimTableCB.ItemIndex := DimTableCB.Items.IndexOf(TDimDef(CubeDef.Dimensions.Objects[Index]).FieldName);
      if DimTableCB.ItemIndex < 0 then
         DimTableCB.ItemIndex := DimTableCB.Items.IndexOf(CubeDef.FactTable);
      end;
   DimTableCBClick(nil);
   DimKeyCB.ItemIndex := DimKeyCB.Items.IndexOf(TDimDef(CubeDef.Dimensions.Objects[Index]).KeyField);
   if DimKeyCB.ItemIndex < 0 then
      DimKeyCB.ItemIndex := DimKeyCB.Items.IndexOf('ID');
   DimKeyCBCLick(nil);
   DimLookupCB.ItemIndex := DimLookupCB.Items.IndexOf(TDimDef(CubeDef.Dimensions.Objects[Index]).LookupField);
   if DimLookupCB.ItemIndex < 0 then
      DimLookupCB.ItemIndex := DimKeyCB.Items.IndexOf('NAME');
   DimLookupCBClick(nil);
   DimMasterCB.Text := TDimDef(CubeDef.Dimensions.Objects[Index]).MasterTable;
   DimMasterCBClick(nil);
   end;
Dimensions2LB.Canvas.TextRect(Rect,Rect.Left + 1,rect.Top+1,CubeDef.Dimensions.Strings[Index]);

end;

procedure TCubeDefForm.Measures2LBDrawItem(Control: TWinControl;
  Index: Integer; Rect: TRect; State: TOwnerDrawState);
begin
if Measures2LB.ItemIndex = -1 then
   Measures2LB.ItemIndex := 0;
if Measures2LB.ItemIndex = Index then
   begin
   MeasureAliasEdit.Text := TMeasuredef(CubeDef.Measures.Objects[Index]).AliasName;
   if MeasureAliasEdit.Text = '' then
      begin
      MeasureAliasEdit.Text := TMeasuredef(CubeDef.Measures.Objects[Index]).FieldName;
      MeasureAliasEditClick(nil);
      end;
   if TMeasuredef(CubeDef.Measures.Objects[Index]).MeasureType = 0 then
      TMeasuredef(CubeDef.Measures.Objects[Index]).MeasureType := mtSumma;
   if TMeasuredef(CubeDef.Measures.Objects[Index]).MeasureType and mtSumma <> 0 then
      mtSummaCB.Checked := True
   else
      mtSummaCB.Checked := False;
   if TMeasuredef(CubeDef.Measures.Objects[Index]).MeasureType and mtCount <> 0 then
      mtCountCB.Checked := True
   else
      mtCountCB.Checked := False;
   if TMeasuredef(CubeDef.Measures.Objects[Index]).MeasureType and mtAverage <> 0 then
      mtAverageCB.Checked := True
   else
      mtAverageCB.Checked := False;
   end;
Measures2LB.Canvas.TextRect(Rect,Rect.Left + 1,rect.Top+1,CubeDef.Measures.Strings[Index]);
end;

procedure TCubeDefForm.DimTableCBClick(Sender: TObject);
begin
TDimDef(CubeDef.Dimensions.Objects[Dimensions2LB.ItemIndex]).TableName := DimTableCB.Text;
if DimTableCB.Text <> '' then
   begin
   DimKeyCB.Items.Clear;
   DimLookupCB.Items.Clear;
   Session.GetFieldNames(CubeDef.AliasName,DimTableCB.Text,DimKeyCB.Items);
   Session.GetFieldNames(CubeDef.AliasName,DimTableCB.Text,DimLookupCB.Items);
   end;
end;

procedure TCubeDefForm.DimKeyCBClick(Sender: TObject);
begin
TDimDef(CubeDef.Dimensions.Objects[Dimensions2LB.ItemIndex]).KeyField := DimKeyCB.Text;
end;

procedure TCubeDefForm.DimLookupCBClick(Sender: TObject);
begin
TDimDef(CubeDef.Dimensions.Objects[Dimensions2LB.ItemIndex]).LookupField := DimLookupCB.Text;
end;

procedure TCubeDefForm.DimMasterCBClick(Sender: TObject);
begin
TDimDef(CubeDef.Dimensions.Objects[Dimensions2LB.ItemIndex]).MasterTable := DimMasterCB.Text;
if DimMasterCB.Text <> '' then
   TDimDef(CubeDef.Dimensions.Objects[Dimensions2LB.ItemIndex]).isBranch := True;
end;

procedure TCubeDefForm.mtSummaCBClick(Sender: TObject);
begin
   if mtSummaCB.Checked then
      TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType :=
      TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType or mtSumma
   else
      begin
      if (TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType and mtSumma) > 0 then
         TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType :=
         TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType xor mtSumma;
      end;
end;

procedure TCubeDefForm.mtCountCBClick(Sender: TObject);
begin
   if mtCountCB.Checked then
      TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType :=
      TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType or mtCount
   else
      begin
      if (TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType and mtCount) > 0  then
         TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType :=
         TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType xor mtCount
      end;
end;

procedure TCubeDefForm.mtAverageCBClick(Sender: TObject);
begin
   if mtAverageCB.Checked then
      TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType :=
      TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType or mtAverage
   else
      begin
      if (TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType and mtAverage) > 0  then
         TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType :=
         TMeasureDef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).MeasureType xor mtAverage
      end;
end;

procedure TCubeDefForm.MeasureAliasEditClick(Sender: TObject);
begin
TMeasuredef(CubeDef.Measures.Objects[Measures2LB.ItemIndex]).AliasName := MeasureAliasEdit.Text;
end;

procedure TCubeDefForm.Button5Click(Sender: TObject);
begin
try
   CheckBefore;
   zExplorerForm.CubeDef := CubeDef;
   zExplorerForm.SaveClick(nil);
   except
   Application.HandleException(Self);
   ModalResult := mrNone;
   end;
end;

procedure TCubeDefForm.Button6Click(Sender: TObject);
begin
try
   CheckBefore;
   zExplorerForm.CubeDef := CubeDef;
   zExplorerForm.BuildCube;
   except
   Application.HandleException(Self);
   ModalResult := mrNone;
   end;
end;

procedure TCubeDefForm.CheckBefore;
var
   i : integer;
   d : TDimDef;
   m : TMeasureDef;
begin
Query.SQL.Clear;
Query.SQL.Add('select * from ''' + CubeDef.FactTable + '''');
Query.Open;
for i := 0 to Cubedef.Dimensions.Count - 1 do
    begin
    d := TDimDef(Cubedef.Dimensions.Objects[i]);
    d.DataType := Query.FieldByName(d.FieldName).DataType;
    end;
for i := 0 to Cubedef.Measures.Count - 1 do
    begin
    m := TMeasureDef(Cubedef.Measures.Objects[i]);
    m.DataType := Query.FieldByName(m.FieldName).DataType;
    end;
   for i := 0 to Cubedef.Dimensions.Count - 1 do
       begin
       d := TDimDef(Cubedef.Dimensions.Objects[i]);
       if d.TableName = '' then
          begin
          if Cubedef.SQL <> '' then
             d.TableName := Cubedef.FactTable
          else
             raise EStringListError.Create('Wrond table for dimension : ' + d.FieldName);
          end;
       if d.KeyField = '' then
          begin
          if Cubedef.SQL <> '' then
             d.KeyField := d.FieldName
          else
             raise EStringListError.Create('Wrond key field for dimension : ' + d.FieldName);
          end;
       if (d.LookupField = '') and (d.TableName <> CubeDef.FactTable) then
          begin
          if Cubedef.SQL = '' then
             raise EStringListError.Create('Wrond lookup field for dimension : ' + d.FieldName);
          end;
       end;
for i := 0 to Cubedef.Measures.Count - 1 do
    begin

    m := TMeasureDef(cubedef.Measures.Objects[i]);
    if (m.DataType <> ftInteger) and (m.DataType <> ftFloat) and
       (m.DataType <> ftCurrency) and (m.DataType <> ftBCD) and
       (m.DataType <> ftSmallInt) and (m.DataType <> ftFMTBCD) then
       begin
       if (m.MeasureType <> mtCount) then
          raise EStringListError.Create('Wrond measure type for field : ' + m.FieldName);
       end;

    end;
end;

procedure TCubeDefForm.DimAliasEditChange(Sender: TObject);
begin
TDimDef(CubeDef.Dimensions.Objects[Dimensions2LB.ItemIndex]).AliasName := DimAliasEdit.Text;
end;

procedure TCubeDefForm.DimMasterCBChange(Sender: TObject);
begin
ShowMessage('Sorry, not implemented yet!');
end;

procedure TCubeDefForm.DimBaseCBChange(Sender: TObject);
begin
ShowMessage('Sorry, not implemented yet!');
end;

end.
