//自动建树控件,除第一层手动调用外,其他层次均在展开结点时,建下一层.
//适用范围: 编码分段定义的层次关系的代码表.
//引用方法: 对用户定义属性(5个)赋值,并在需要建树的时间调用 BuildFirst即可.其中,condition可以不赋值是Oracle的语法，需要改成自己的数据库的语法
unit DBTreeView;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ComCtrls,dbtables, db;
type
TDBTreeView = class(TTreeView)
private
{ Private declarations }
FDBName:String;
FTableName:String;
FDataField:String;
FLabelField:String;
Fcondition:String;
FSuprDataField:String;
FDataFieldLen:integer;
FBuildType:integer;
FFirstConditon:String;
protected
{ Protected declarations }
function CanExpand(Node: TTreeNode): Boolean;override;
public
{ Public declarations }
constructor Create(AOwner: TComponent); override;
procedure BuildFirst;
procedure BuildItem(Node:TTreeNode); //建子节点
published
{ Published declarations }
property UD_BuildType:integer read FBuildType write FBuildType;
property UD_DBName: String read FDBName write FDBName; //对应数据库名称
property UD_TableName: String read FTableName write FTableName; //对应表名或视图名
property UD_DataField: String read FDataField write FDataField; //对应数据字段名,保存在结点数据中
property UD_LabelField: String read FLabelField write FLabelField; //对应显示字段名,以结点标签显示
property UD_condition: String read Fcondition write Fcondition; //对每一层均有效的条件
property UD_SuprDataField:String read FSuprDataField Write FSuprDataField; //父层代码字段
property UD_FirstConditon:String read FFirstConditon Write FFirstConditon; //父层代码时，
end;
procedure Register;
implementation
constructor TDBTreeView.Create(AOwner: TComponent);
begin
inherited Create(AOwner);
//OnExpanding := ExpandEvt;
end;
procedure Register;
begin
RegisterComponents('DBEnhance', [TDBTreeView]);
end;
procedure TDBTreeView.BuildFirst; //BuildFistLevel
Var
qry,qry1:TQuery;
CurData
String;
CurNode:TTreeNode;
i_MinLen:integer;
begin
i_MinLen:=0;
Items.Clear;
ReadOnly:=True;
HideSelection:=False;
qry:=TQuery.Create(nil);
qry.DatabaseName:=FDBName;
qry1:=TQuery.Create(nil);
qry1.DatabaseName:=FDBName;
With qry do
begin
Close;
SQL.Clear;
if FBuildType = 1 then //按编码分层
begin
//find minlength
SQL.Add('select Min(length('+FDataField+')) as MinLen from '+FTableName);
if FCondition <> '' then SQL.Add('where '+ FCondition);
Open;
i_MinLen:= FieldByName('Minlen').AsInteger;
if i_MinLen> 0 then
begin
//find maxlength
Close;
SQL.Clear;
SQL.Add('select Max(length('+FDataField+')) as MaxLen from '+FTableName);
if FCondition <> '' then SQL.Add('where '+ FCondition);
Open;
FDataFieldLen := FieldByName('MaxLen').AsInteger;
//find fist level
Close;
SQL.Clear;
SQL.Add('select '+FDataField+','+FLabelField+' as S_Label from '+FTableName);
SQL.Add('where length('+FDataField+')='+IntToStr(i_MinLen));
end;
end;
if FBuildType=2 then //建父层代码建树
begin
SQL.Add('select '+FDataField+','+FLabelField+' as S_Label from '+FTableName);
SQL.Add('where 1=1');
if FFirstConditon ='' then
SQL.Add('and ('+FSuprDataField+' is null or trim('+ FSuprDataField+') is null)')
else
SQL.Add('and '+ FFirstConditon)
end;
if (FFirstConditon = '') and (FCondition <>'') then
SQL.Add('and '+ FCondition);
SQL.Add('order by '+FDataField);
Open;
while not Eof do
begin
CurNode:=items.Add(nil,FieldByName('S_Label').AsString);
New(CurData);
CurData^:=FieldByName(FDataField).AsString;
CurNode.Data:=CurData;
//Judge if has child
qry1.Close;
qry1.SQL.CLear;
if FBuildType=1 then qry1.SQL.Add('Select count(*) as Cou from '+FTableName+' where length('+FDataField+')>'+IntToStr(i_MinLen)+ ' and '+FDataField+' like '''+FieldByName(FDataField).AsString+'%''');
if FBuildType=2 then qry1.SQL.Add('Select count(*) as Cou from '+FTableName+' where '+FSuprDataField+ '= '''+ FieldByName(FDataField).AsString+'''');
if FCondition <> '' then qry1.SQL.Add('and '+ FCondition);
qry1.Open;
if qry1.FieldByName('cou').AsInteger > 0 then
CurNode.HasChildRen:=True;
qry.Next;
end;
end;
qry.Free;
qry1.Free;
end;
function TDBTreeView.CanExpand(Node: TTreeNode): Boolean;
Var
PNode:TTreeNode;
PData
String;
i,Second_len:integer;
qry,qry1:TQuery;
begin
Inherited CanExpand(Node);
Result:=True;
Second_len:=0;
BuildItem(Node);
end;
procedure TDBTreeView.BuildItem(Node:TTreeNode);
Var
PNode:TTreeNode;
PData
String;
i,Second_len:integer;
qry,qry1:TQuery;
begin
if Node=nil then
System.Exit;
Second_len:=0;
if Node.GetFirstChild <> nil then
System.Exit;
if Node.HasChildren then
begin
qry:=TQuery.Create(nil);
qry.DatabaseName:=FDBName;
qry1:=TQuery.Create(nil);
qry1.DatabaseName:=FDBName;
if FBuildType=1 then //按层次码建树
begin
//Find next level:length is longer then currentlength and minus DataFieldLen
for i:=length(trim(String(Node.Data^)))+1 to FDataFieldLen do
begin
With qry do
begin
Close;
SQL.Clear;
SQL.Add('select count(*) as int_cou from '+FTableName+' where '+FDataField+' like '''+String(Node.Data^)+'%''');
SQL.Add('and length(ltrim(rtrim('+FDataField+')))='+IntToStr(i));
if FCondition <> '' then SQL.Add('and '+ FCondition);
Open;
if FieldByName('int_cou').AsInteger>0 then
begin
Second_len:=i;
Break;
end;
end;
end;
//buildnext ,and length=i
with qry do
begin
Close;
SQL.Clear;
SQL.Add('select '+FDataField+','+FLabelField+' as S_Label from '+FTableName+' where length(ltrim(rtrim('+FDataField+')))='+IntToStr(Second_len));
SQL.Add('and '+FDataField+' like '''+string(Node.Data^)+'%''');
if FCondition <> '' then SQL.Add('and '+ FCondition);
SQL.Add('order by '+ FDataField) ;
Open;
end;
end;
if FBuildType=2 then //按父层代码建树
begin
qry.Close;
qry.SQL.Clear;
qry.SQL.Add('select '+FDataField+','+FLabelField+' as S_Label from '+FTableName+' where '+FSuprDataField+ '= '''+ string(Node.Data^)+'''');
if FCondition <> '' then qry.SQL.Add('and '+ FCondition);
qry.SQL.Add('order by '+ FDataField);
qry.Open;
end;
while not qry.Eof do
begin
PNode:=items.AddChild(Node,qry.FieldByName('S_Label').AsString);
New(PData);
PData^:=qry.FieldByName(FDataField).AsString;
PNode.Data:=PData;
//judge if has next level:length is longer and minus FDataFieldLen
with qry1 do
begin
CLose;
SQL.Clear;
if FBuildType=1 then
begin
SQL.Add('select count(*) as int_cou from '+FTableName+' where length(ltrim(rtrim('+FDataField+')))>'+IntToStr(Second_len));
SQL.Add('and '+FDataField+' like '''+ qry.FieldByName(FDataField).AsString+'%''');
end;
if FBuildType=2 then //按父层编码建树
begin
SQL.Add('select count(*) as int_cou from '+FTableName+' where '+FSuprDataField+ '= '''+ qry.FieldByName(FDataField).AsString +'''');
end;
if FCondition <> '' then SQL.Add('and '+ FCondition);
Open;
end;
if qry1.FieldByName('int_cou').AsInteger>0 then
PNode.HasChildren:=True
else
PNode.HasChildren:=False;
qry.Next;
end;
qry.Free;
qry1.Free;
end;
end;
end.
