通用分組統計

{*******************************************************}
{                                                       }
{       分組統計                                        }
{                                                       }
{       著作權 (C) 2008 詠南工作室(陳新光)            }
{                                                       }
{*******************************************************}

unit uGroup;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, CheckLst, DBGridEh, db,ADOBatchMove,ComObj,
  ADODB,uDisplay,uCommFunc;

type
  TColParams = record
    FieldName: string;
    Title: string;
  end;

  TFormGroup = class(TForm)
    grp1: TGroupBox;
    pnl1: TPanel;
    grp3: TGroupBox;
    btn1: TButton;
    btn2: TButton;
    chklst1: TCheckListBox;
    chklst2: TCheckListBox;
    btn3: TButton;
    btn4: TButton;
    procedure btn2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure btn1Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btn3Click(Sender: TObject);
    procedure btn4Click(Sender: TObject);
  private
    { Private declarations }
    FDataSet:TDataSet          //起橋聯作用的變數
    qry88:TADOQuery;
    ColArray,ColArray2: array of TColParams;
    procedure LoadData;
    procedure Group;
    procedure CreateTmpDb;
    procedure BatMove;
    procedure Ok;
  public
    { Public declarations }
  end;

var
  FormGroup: TFormGroup;

const
  FConnStr='Provider=Microsoft.Jet.OLEDB.4.0;Data Source= %s';

//==============================================================================
// 顯示分組統計設定視窗，介面函數
//==============================================================================

procedure ShowGroup(ADataSet:TDataSet);

implementation

{$R *.dfm}

//==============================================================================
// grid是待被分組統計的GRID
// 用GRID關聯資料集grid.datasource.dataset
//==============================================================================

procedure ShowGroup(ADataSet:TDataSet);
begin
  if (not Assigned(ADataSet)) or (not ADataSet.Active) or
    (ADataSet.IsEmpty) then exit;
  FormGroup:=TFormGroup.Create(nil);
  try
    FormGroup.FDataSet:=ADataSet;
    FormGroup.ShowModal;
  finally
    FreeAndNil(FormGroup);
  end;
end;

//==============================================================================
// batCopy 先刪除已存在的表，再建立新表，再往表中增加資料
// batAppend 往已存在的表中追加資料
// dsQuery 來源資料集控制項是TADOQUERY
// dsTable 來源資料集控制項是TADOTABLE
// 批移dbgrideh的資料至access暫存資料表grp中
//==============================================================================

procedure tFormGroup.BatMove;
var
  Table:TADOTable;
  batchmove:TADOBatchMove;
begin
  Table:=TADOTable.Create(nil);
  BatchMove:=TADOBatchMove.Create(nil);
  try
    BatchMove.Mode:=batCopy;
    BatchMove.SourceMode:=dsQuery;
    Table.ConnectionString:=Format(FConnStr,[GetMDB]);
    Table.TableName:='grp';
    Batchmove.SourceQuery:=TADOQuery(FDataSet);
    Batchmove.DestTable:=Table;
    BatchMove.Execute;
  finally
    FreeAndNil(Table);
    FreeAndNil(batchmove);
  end;
end;

procedure TFormGroup.btn2Click(Sender: TObject);
begin
  close;
end;

//==============================================================================
// 將TNumericField和非TNumericField的欄位名分別放入不同的Tchecklistbox顯示
//==============================================================================

procedure TFormGroup.LoadData;
var
  i: Integer;
begin
  chklst1.Clear;
  chklst2.Clear;
  SetLength(ColArray,FDataSet.FieldCount);
  SetLength(ColArray2,FDataSet.FieldCount);
  for i := 0 to FDataSet.FieldCount - 1 do
  begin
    if not (FDataSet.Fields[i] is TNumericField)
      or (FDataSet.Fields[i] is TIntegerField) then
    begin
      ColArray[i].FieldName := FDataSet.Fields[i].FieldName;
      ColArray[i].Title := FDataSet.Fields[i].DisplayLabel;
      chklst1.Items.Add(ColArray[i].Title);
    end else
    begin
      ColArray2[i].FieldName := FDataSet.Fields[i].FieldName;
      ColArray2[i].Title := FDataSet.Fields[i].DisplayLabel;
      chklst2.Items.Add(ColArray2[i].Title);
    end; 
  end;
end;

procedure TFormGroup.FormShow(Sender: TObject);
begin
  qry88:=TADOQuery.Create(self);
  LoadData;
end;

procedure TFormGroup.btn1Click(Sender: TObject);
begin
  ok;
end;

//==============================================================================
// 對ACCESS暫存資料表GRP中的資料進行分組統計
//==============================================================================

procedure TFormGroup.Group;
var
  i,x:Integer;
begin
  with qry88 do begin
    ConnectionString:=Format(FConnStr,[GetMDB]);
    SQL.Clear;
    SQL.Add(' select ');
    SQL.Add(' from grp ');
    SQL.Add(' group by ');
    for i:=Low(colarray) to High(colarray) do begin
      for x:=0 to chklst1.Count-1 do begin
        if (ColArray[i].Title=chklst1.Items[x]) and (chklst1.Checked[x]) then
        begin
          SQL[0]:=SQL[0]+colarray[i].FieldName+' as '+colarray[i].Title+',';
          SQL[2]:=SQL[2]+colarray[i].FieldName+',';
        end;
      end;
    end;
    for i:=Low(colarray2) to High(colarray2) do begin
      for x:=0 to chklst2.Count-1 do begin
        if (ColArray2[i].Title=chklst2.Items[x]) and (chklst2.Checked[x]) then
        begin
          SQL[0]:=SQL[0]+'sum('+colarray2[i].FieldName+ ') as '+
            colarray2[i].Title+',';
        end;
      end;
    end;
    SQL[0]:=copy(sql[0],1,length(sql[0])-1);
    sql[2]:=copy(sql[2],1,length(sql[2])-1);
  end;
end;

//==============================================================================
// 建立ACCESS資料庫
//==============================================================================

procedure TFormGroup.CreateTmpDb;
var  
  CreateAccess:OleVariant;
begin
  CreateAccess:=CreateOleObject('ADOX.Catalog');
  CreateAccess.create(Format(FConnStr,[GetMDB]));
end;

procedure TFormGroup.FormDestroy(Sender: TObject);
begin
  FreeAndNil(qry88);
end;

//==============================================================================
// 確定
//==============================================================================

procedure TFormGroup.Ok;
var
  i,t,n:Integer;
begin
  t:=0;
  for i:=0 to chklst1.Count-1 do        //沒有選擇任何分類選擇
    if chklst1.Checked[i] then Inc(t);
  if t=0 then exit;

  n:=0;
  for i:=0 to chklst2.Count-1 do       //沒有選擇任何匯總選擇
    if chklst2.Checked[i] then Inc(n);
  if n=0 then exit;
  if not FileExists(GetMDB) then CreateTmpDb;
  BatMove;                   //批移
  group;                     //分組統計
  ShowDisplay(qry88);        //顯示分組後結果
  Close;
end;

procedure TFormGroup.btn3Click(Sender: TObject);
var
  i:Integer;
begin
  for i:=0 to chklst1.Count-1 do chklst1.Checked[i]:=True;
  for i:=0 to chklst2.Count-1 do chklst2.Checked[i]:=True;
end;

procedure TFormGroup.btn4Click(Sender: TObject);
var
  i:Integer;
begin
  for i:=0 to chklst1.Count-1 do chklst1.Checked[i]:=False;
  for i:=0 to chklst2.Count-1 do chklst2.Checked[i]:=False;
end;

end.