//==============================================================================
//点击DBGrid标题栏对查询结果排序************************************************
//==============================================================================
function DBGridTitleOrder(Column: TColumn): integer;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//DBGrid.DataSource.DataSet is TTable*****************************************
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
procedure TableOrder;
begin end;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//DBGrid.DataSource.DataSet is TQuery*****************************************
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
procedure QueryOrder;
var
SQLStr, OrderFieldName, TempStr: string;
i, OrderPos: integer;
SavedParams: TParams;
begin
if not (TQuery(TDBGrid(Column.Grid).DataSource.DataSet).State in [dsBrowse]) then Exit;
SavedParams := nil;
for i:=0 to Column.Grid.FieldCount-1 do
begin
{TDBGrid(Column.Grid).Columns[i].Font.Color := clBlack;}
TDBGrid(Column.Grid).Columns[i].Title.Caption := TDBGrid(Column.Grid).Columns[i].Field.DisplayName;
end;
if not (Column.Field.FieldKind in [fkData,fkLookup]) then Exit;
if Column.Field.FieldKind=fkData
then OrderFieldName := LowerCase(Column.Field.FieldName)
else OrderFieldName := LowerCase(Column.Field.KeyFields);
while Pos(OrderFieldName,';')<>0 do OrderFieldName := copy(OrderFieldName,1,Pos(OrderFieldName,';')-1)+','+copy(OrderFieldName,Pos(OrderFieldName,';')+1,100);
with TQuery(TDBGrid(Column.Grid).DataSource.DataSet) do
begin
SQLStr := LowerCase(SQL.Text);
if ParamCount>0 then
begin
SavedParams := TParams.Create;
SavedParams.Assign(Params);
end;
OrderPos := pos('order',SQLStr);
if (OrderPos=0) or (pos(OrderFieldName,copy(SQLStr,OrderPos,100))=0) then
begin
Result := 1;
TempStr := ' order by ' + OrderFieldName + ' asc';
{Column.Title.Caption := Column.Title.Caption + '(▲)';}
{Column.font.Color := clRed;}
end else if pos('asc',SQLStr)=0 then
begin
Result := 1;
TempStr := ' order by ' + OrderFieldName + ' asc';
{Column.Title.Caption := Column.Title.Caption + '(▲)';}
{Column.font.Color := clRed;}
end else
begin
Result := 2;
TempStr := ' order by ' + OrderFieldName + ' desc';
{Column.Title.Caption := Column.Title.Caption + '(▼)';}
{Column.font.Color := clGreen;}
end;
if OrderPos<>0 then SQLStr := Copy(SQLStr,1,OrderPos-1);
SQLStr := SQLStr + TempStr;
Active := False;
SQL.Clear;
SQL.Text := SQLStr;
if ParamCount>0 then
begin
Params.AssignValues(SavedParams);
SavedParams.Free;
end;
Open;
end;
end;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//DBGrid.DataSource.DataSet is TClientDataSet*********************************
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
procedure ClientDataSetOrder;
begin
TDBGrid(Column.Grid).Tag := TDBGrid(Column.Grid).Tag xor Round(Exp(Column.Index * Ln(2)));
with (TDBGrid(Column.Grid).DataSource.DataSet as TClientDataSet) do
begin
if Column.Field.DataType<ftAutoInc then
begin
if (TDBGrid(Column.Grid).Tag and Round(Exp(Ln(2) * Column.Index)))=0 then
begin
AddIndex(Column.FieldName + 'InxDES', Column.FieldName, [ixDescending]);
IndexName := Column.FieldName + 'InxDES';
Result := 2;
end else
begin
AddIndex(Column.FieldName + 'InxASC', Column.FieldName, []);
IndexName := Column.FieldName + 'InxASC';
Result := 1;
end;
end;
end;
end;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin
Result := 0;
if (TDBGrid(Column.Grid).DataSource.DataSet is TTable) then TableOrder
else if (TDBGrid(Column.Grid).DataSource.DataSet is TQuery) then QueryOrder
else if (TDBGrid(Column.Grid).DataSource.DataSet is TClientDataSet) then ClientDataSetOrder;
end;
//点击DBGrid标题栏对查询结果排序************************************************
//==============================================================================
function DBGridTitleOrder(Column: TColumn): integer;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//DBGrid.DataSource.DataSet is TTable*****************************************
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
procedure TableOrder;
begin end;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//DBGrid.DataSource.DataSet is TQuery*****************************************
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
procedure QueryOrder;
var
SQLStr, OrderFieldName, TempStr: string;
i, OrderPos: integer;
SavedParams: TParams;
begin
if not (TQuery(TDBGrid(Column.Grid).DataSource.DataSet).State in [dsBrowse]) then Exit;
SavedParams := nil;
for i:=0 to Column.Grid.FieldCount-1 do
begin
{TDBGrid(Column.Grid).Columns[i].Font.Color := clBlack;}
TDBGrid(Column.Grid).Columns[i].Title.Caption := TDBGrid(Column.Grid).Columns[i].Field.DisplayName;
end;
if not (Column.Field.FieldKind in [fkData,fkLookup]) then Exit;
if Column.Field.FieldKind=fkData
then OrderFieldName := LowerCase(Column.Field.FieldName)
else OrderFieldName := LowerCase(Column.Field.KeyFields);
while Pos(OrderFieldName,';')<>0 do OrderFieldName := copy(OrderFieldName,1,Pos(OrderFieldName,';')-1)+','+copy(OrderFieldName,Pos(OrderFieldName,';')+1,100);
with TQuery(TDBGrid(Column.Grid).DataSource.DataSet) do
begin
SQLStr := LowerCase(SQL.Text);
if ParamCount>0 then
begin
SavedParams := TParams.Create;
SavedParams.Assign(Params);
end;
OrderPos := pos('order',SQLStr);
if (OrderPos=0) or (pos(OrderFieldName,copy(SQLStr,OrderPos,100))=0) then
begin
Result := 1;
TempStr := ' order by ' + OrderFieldName + ' asc';
{Column.Title.Caption := Column.Title.Caption + '(▲)';}
{Column.font.Color := clRed;}
end else if pos('asc',SQLStr)=0 then
begin
Result := 1;
TempStr := ' order by ' + OrderFieldName + ' asc';
{Column.Title.Caption := Column.Title.Caption + '(▲)';}
{Column.font.Color := clRed;}
end else
begin
Result := 2;
TempStr := ' order by ' + OrderFieldName + ' desc';
{Column.Title.Caption := Column.Title.Caption + '(▼)';}
{Column.font.Color := clGreen;}
end;
if OrderPos<>0 then SQLStr := Copy(SQLStr,1,OrderPos-1);
SQLStr := SQLStr + TempStr;
Active := False;
SQL.Clear;
SQL.Text := SQLStr;
if ParamCount>0 then
begin
Params.AssignValues(SavedParams);
SavedParams.Free;
end;
Open;
end;
end;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//DBGrid.DataSource.DataSet is TClientDataSet*********************************
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
procedure ClientDataSetOrder;
begin
TDBGrid(Column.Grid).Tag := TDBGrid(Column.Grid).Tag xor Round(Exp(Column.Index * Ln(2)));
with (TDBGrid(Column.Grid).DataSource.DataSet as TClientDataSet) do
begin
if Column.Field.DataType<ftAutoInc then
begin
if (TDBGrid(Column.Grid).Tag and Round(Exp(Ln(2) * Column.Index)))=0 then
begin
AddIndex(Column.FieldName + 'InxDES', Column.FieldName, [ixDescending]);
IndexName := Column.FieldName + 'InxDES';
Result := 2;
end else
begin
AddIndex(Column.FieldName + 'InxASC', Column.FieldName, []);
IndexName := Column.FieldName + 'InxASC';
Result := 1;
end;
end;
end;
end;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin
Result := 0;
if (TDBGrid(Column.Grid).DataSource.DataSet is TTable) then TableOrder
else if (TDBGrid(Column.Grid).DataSource.DataSet is TQuery) then QueryOrder
else if (TDBGrid(Column.Grid).DataSource.DataSet is TClientDataSet) then ClientDataSetOrder;
end;
//DBGrid.单击标题事件***********************************************************
//==============================================================================
procedure TexDockForm.DBGridTitleClick(Column: TColumn);
var Ressource: TResourceStream;
Order: integer;
Rect: TRect;
Bmp: TBitmap;
begin
try
if TClientDataSet(TDBGrid(Column.Grid).DataSource.DataSet).MasterSource<>nil then Exit;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Order := DBGridTitleOrder(Column);
Rect := TStringGrid(Column.Grid).CellRect(Column.Index+1, 0);
Column.Grid.Repaint;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if Order=1
then Ressource := TResourceStream.Create(hInstance, 'OrderUp', PChar('BMP'))
else Ressource := TResourceStream.Create(hInstance, 'OrderDown', PChar('BMP'));
Ressource.Position := 0;
Bmp := TBitmap.Create;
Bmp.LoadFromStream(Ressource);
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TDBGrid(Column.Grid).Canvas.Draw(Rect.Right-14, Rect.Top+2, Bmp);
Ressource.Free;
Bmp.Free;
except
end;
end;