//------------------------------------------------------------
//      作者:曾慶順
//      模塊:通用排序窗口
//      時間：2001.07.08
//      功能介紹：P_Sort( Query:TQuery;DbGRid:TDBgrid);
//                Query 為對應的數據集，DbGRid對應的數據控件---標準控件
//-----------------------------------------------------------
unit Sort;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, ExtCtrls, Buttons, StdCtrls,dbtables,dbgrids,db;

type
  TTSortFrm = class(TForm)
    Panel1: TPanel;
    StringGrid1: TStringGrid;
    Panel2: TPanel;
    StringGrid2: TStringGrid;
    Panel3: TPanel;
    Panel4: TPanel;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    procedure FormCreate(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure StringGrid2SelectCell(Sender: TObject; ACol, ARow: Integer;
      var CanSelect: Boolean);
    procedure StringGrid1DblClick(Sender: TObject);
    procedure StringGrid2DblClick(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
  private
    { Private declarations }

  public
    { Public declarations }
    FQuery:TQuery;
    FDbGRid:TDBgrid;
    procedure DeleteRow(Row:integer;StringGrid: TStringGrid);
    Function GetFieldName(FindName:string):string;
    Function GetKeyFieldName(FindName:string):string;
  end;
  procedure P_Sort( Query:TQuery;DbGRid:TDBgrid);
var
  TSortFrm: TTSortFrm;

implementation

{$R *.DFM}
procedure P_Sort( Query:TQuery;DbGRid:TDBgrid);
var dlg:TTSortFrm;
begin
    if not Dbgrid.DataSource.DataSet.Active then exit;
    dlg:=TTSortFrm.Create(nil);
    dlg.FDbgrid:=DBGrid;
    dlg.FQuery:=Query;
    dlg.ShowModal;
end;

procedure TTSortFrm.FormCreate(Sender: TObject);
begin
    StringGrid1.Cells[0,0]:='   序號';
    StringGrid1.Cells[1,0]:='      字段名';
    StringGrid2.Cells[0,0]:='   序號';
    StringGrid2.Cells[1,0]:='      字段名';
    StringGrid2.Cells[2,0]:='排序條件';
end;

procedure TTSortFrm.FormShow(Sender: TObject);
var i:integer;
    RNum:integer;
    str:string;
begin
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
     //和奇怪的事情，如果傳入的grid為繼成的窗體的FDbGrid.Fields[0]出錯
     try
       str:=FDbGrid.Fields[i].KeyFields;
     except
       continue;
     end;
     if (FDbGrid.Fields[i].FieldKind = fkData) or (FDbGrid.Fields[i].FieldKind = fkLookup) then
     begin
        //判斷是否還是查找字段
        if (FDbGrid.Fields[i].FieldKind = fkLookup) then
        begin
           str:=FDbGrid.Fields[i].KeyFields;
           if FDbGrid.DataSource.DataSet.FieldByName(str).FieldKind = fkLookup then continue;
        end;

        if StringGrid1.Cells[0,1]='' then RNum:=1
        else
        begin
          RNum:=StringGrid1.RowCount;
          StringGrid1.RowCount:=StringGrid1.RowCount + 1;
        end;
        StringGrid1.Cells[0,RNum]:=inttostr(RNum);
        StringGrid1.Cells[1,RNum]:=trim(FDbGrid.Columns[i].Title.Caption);
     end;
   end;
end;

procedure TTSortFrm.DeleteRow(Row:integer;StringGrid: TStringGrid);
var i:integer;
begin
    //大于2時要刪除行
    if StringGrid.RowCount > 2 then
    begin
       for i:=Row to StringGrid.RowCount - 1 do
       begin
          //行移動
          StringGrid.Rows[i]:=StringGrid.Rows[i+1];
       end;
       StringGrid.RowCount:=StringGrid.RowCount - 1;
    end
    else StringGrid.Rows[row].Clear;

end;
procedure TTSortFrm.StringGrid2SelectCell(Sender: TObject; ACol,
  ARow: Integer; var CanSelect: Boolean);
var RNum:integer;
begin
   //當單擊第三列時改變升降序
   if ACol = 2 then
   begin
       if StringGrid2.Cells[0,ARow]='' then exit;

       if StringGrid2.Cells[2,ARow]='↑' then StringGrid2.Cells[2,ARow]:='↓'
       else StringGrid2.Cells[2,ARow]:='↑' ;
   end;
end;

procedure TTSortFrm.StringGrid1DblClick(Sender: TObject);
var RNum:integer;
begin
    if StringGrid1.Cells[0,1]='' then exit;
    //雙擊時移動行
    if StringGrid2.Cells[0,1]='' then RNum:=1
    else
    begin
       RNum:=StringGrid2.RowCount;
       StringGrid2.RowCount:=StringGrid2.RowCount + 1;
    end;
    StringGrid2.Cells[0,RNum]:=StringGrid1.Cells[0,StringGrid1.row];
    StringGrid2.Cells[1,RNum]:=StringGrid1.Cells[1,StringGrid1.row];
    StringGrid2.Cells[2,RNum]:='↑';
    DeleteRow(StringGrid1.row,StringGrid1);
end;

procedure TTSortFrm.StringGrid2DblClick(Sender: TObject);
var RNum:integer;
begin
   if StringGrid2.col = 2 then exit;
   //雙擊時移動行
   if StringGrid2.Cells[0,1]='' then exit;
   if StringGrid1.Cells[0,1]='' then RNum:=1
   else
   begin
      RNum:=StringGrid1.RowCount;
      StringGrid1.RowCount:=StringGrid1.RowCount + 1;
   end;
   StringGrid1.Cells[0,RNum]:=StringGrid2.Cells[0,StringGrid2.row];
   StringGrid1.Cells[1,RNum]:=StringGrid2.Cells[1,StringGrid2.row];
   DeleteRow(StringGrid2.row,StringGrid2);
end;

procedure TTSortFrm.SpeedButton2Click(Sender: TObject);
begin
  ModalResult:=mrCancel;
end;
Function TTSortFrm.GetFieldName(FindName:string):string;
var i:integer;
begin
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
     if trim(FDbGrid.Columns[i].Title.Caption) = FindName then
     begin
        result:= FDbGrid.Columns[i].FieldName;
        break;
     end;
   end;
end;
//得到查查找字段的主鍵字段  KeyFields
Function TTSortFrm.GetKeyFieldName(FindName:string):string;
var i:integer;
begin
   result:='';
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
     if trim(FDbGrid.Columns[i].Title.Caption) = FindName then
     begin
        result:= FDbGrid.Columns[i].Field.KeyFields;
        break;
     end;
   end;
end;
procedure TTSortFrm.SpeedButton1Click(Sender: TObject);
var FOrder:string;
    i:integer;
    str:string;
    p:integer;
    FieldName:string;
begin
    if StringGrid2.Cells[0,1] ='' then exit;
    FOrder:=' ORDER BY ';
    for i:=1 to StringGrid2.RowCount - 1 do
    begin
       FieldName:=GetFieldName(StringGrid2.Cells[1,i]);
       //判斷是否是查找字段
       if FDbGrid.DataSource.DataSet.FieldByName(FieldName).FieldKind = fkLookup then
         FOrder:=FORder + GetKeyFieldName(StringGrid2.Cells[1,i])
       else
         FOrder:=FORder + FieldName;

       if StringGrid2.Cells[2,i] = '↑' then FOrder:=FORder+' ASC,'
       else FOrder:=FORder+' DESC,';
    end;
    Forder:=Copy(Forder,0,length(Forder)-1);//去掉最后的','
    str:=StrUpper(pchar(FQuery.SQL.Text));
    str:=copy(str,0,length(str)-2);
    p:=pos('ORDER BY',str);
    if p>0 then str:=copy(str,0,p-1);
    FQuery.DisableControls;
    FQuery.close;
    FQuery.sql.Text:=str+FOrder;
    FQuery.open;
    FQuery.EnableControls;
    ModalResult:=mrOk;
end;

end.