unit_superquery.pas

万能查询主要代码, 可按配置无限选择查询条件,自动生成SQL语句.

   end else
   begin
      t := (StrToInt(tI) - 1) * xHeight;
   end;
   //
   CreateControl(cName, CompType, Label3.Left + Label3.Width, Label3.Top + Label3.Height + t + 2, 155);
   //
   if FindComponent(cName) <> nil then
   begin
      case CompType of
         cText :
         begin
            (FindComponent(cName) as TEdit).Text := '';
            (FindComponent(cName) as TEdit).SetFocus;
         end;
         cDate :
         begin
            (FindComponent(cName) as TDateTimePicker).DateTime := StrToDate(FormatDateTime('yyyy-MM-dd', now()));
            SetFocus;
         end;
         cNumber :
         begin
            with (FindComponent(cName) as TEdit) do
            begin
               Text := '0';
               SetFocus;
               OnKeyPress := NumberCompValueKeyPress;
               OnExit := NumberCompValueExit;
            end;
         end;
         cTree :
         begin
            with (FindComponent(cName) as TdxTreeViewEdit) do
            begin
               for i := low(ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue) to high(ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue) do
               begin
                  ParentNode := getParentNode(Items,ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue[i].parent_id);
                  NewNode := Items.AddChild(ParentNode,ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue[i].name);
                  NewNode.Data := Pointer(StrToInt(ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue[i].id));
               end;
               SetFocus;
            end;
         end;
         cList :
         begin
            with (FindComponent(cName) as TComboBox) do
            begin
               Items.Clear; 
               for i := low(ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue) to high(ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue) do
               begin
                  Items.AddObject(ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue[i].name, TObject(ConfigSQLData[(Sender as TComboBox).ItemIndex].ArrayValue[i].id));
               end;
               ItemIndex := 0;
               SetFocus;
            end;
         end;
      end;
   end;
   //
end;

procedure TForm_SuperQuery.CompBoxChange(Sender: TObject);
begin
   //
end;

procedure TForm_SuperQuery.RelaBoxChange(Sender: TObject);
var
   k :Integer;
   cName,value :String;
begin
   cName := (Sender as TComboBox).Name;
   value := (Sender as TComboBox).Text;
   k := StrToInt(Copy(cName, getNumberIndex(cName), Length(cName)));

   if (k <> g_row_Index) and (trim(value) <> '') then Exit;

   if trim(value) <> '' then
   begin
      //创建查询控件
      g_row_Index := g_row_Index + 1;
      CreateControls(g_row_Index);
   end else
   begin
      //删除查询控件
      DeleteControls(k);
   end;
end;

procedure TForm_SuperQuery.DeleteControls(rowIndex :Integer);
var
   i :Integer;
begin
   while rowIndex <> g_row_Index do
   begin
      for i := 0 to ControlNamesMax - 1 do
      begin
         try
            FindComponent(ControlNames[i] + IntToStr(g_row_Index)).Free;
         except
         end;
      end;
      g_row_Index := g_row_Index - 1;
      Self.Height := Self.Height - SpeedButton2.Height;
   end;
end;

procedure TForm_SuperQuery.CreateControl(ControlName :String; cType :TSupperQueryType; left :Integer; top :Integer; width :Integer);
var
   Edit :TEdit;
   Date :TDateTimePicker;
   Tree : TdxTreeViewEdit;
   ComboBox :TComboBox;
begin
   if FindComponent(ControlName) <> nil then FindComponent(ControlName).Free;
   //[cText cDate cNumber cTree cList]
   case cType of
      cText, cNumber :
      begin
         Edit := TEdit.Create(self);
         Edit.Name := ControlName;
         Edit.Left := left;
         Edit.Top := top;
         Edit.Width := width;
         Edit.Parent := Self;
      end;
      cDate :
      begin
         Date := TDateTimePicker.Create(self);
         Date.Name := ControlName;
         Date.Left := left;
         Date.Top := top;
         Date.Width := width;
         Date.Parent := Self;
         Date.Format := 'yyyy-MM-dd HH:mm:ss';
         Date.Kind := dtkTime;
      end;
      cTree :
      begin
         Tree := TdxTreeViewEdit.Create(self);
         Tree.Name := ControlName;
         Tree.Left := left;
         Tree.Top := top;
         Tree.Width := width;
         Tree.Parent := Self;
         Tree.DropDownRows := 12;
         Tree.TreeViewFont.Charset := ANSI_CHARSET;
         Tree.TreeViewFont.Name := '宋体';
         Tree.TreeViewFont.Size := 11;
         Tree.TreeViewFont.Height := -14;
         Tree.TreeViewReadOnly := true;
      end;
      cList :
      begin
         ComboBox := TComboBox.Create(self);
         ComboBox.Name := ControlName;
         ComboBox.Left := left;
         ComboBox.Top := top;
         ComboBox.Width := width;
         ComboBox.Parent := Self;
         ComboBox.Style := csDropDownList;
      end;
   end;
end;

procedure TForm_SuperQuery.CreateControls(rowIndex :Integer);
var
   i,t :Integer;
   cName :String;
begin
   if rowIndex < 2 then
   begin
      t := 0;
   end else
   begin
      t := (rowIndex - 1) * xHeight;
   end;

   //创建比较运算符下拉列表
   cName := ControlNames[1] + IntToStr(rowIndex);
   CreateControl(cName, cList, Label2.Left + Label2.Width, Label2.Top + Label2.Height + t + 2, 80);

   //创建比较名称下拉列表
   cName := ControlNames[0] + IntToStr(rowIndex);
   CreateControl(cName, cList, Label1.Left + Label1.Width, Label1.Top + Label1.Height + t + 2, 150);
   with (FindComponent(cName) as TComboBox) do
   begin
      Clear;
      for i := 0 to Length(ConfigSQLData) - 1 do
      begin
         Items.AddObject(ConfigSQLData[i].name, TObject(ConfigSQLData[i].SQL));
      end;
      ItemIndex := 0;
      OnChange := FieldBoxChange;
   end;
   FieldBoxChange((FindComponent(cName) as TComboBox));
   
   //创建关系运算符下拉列表
   cName := ControlNames[3] + IntToStr(rowIndex);
   CreateControl(cName, cList, Label4.Left + Label4.Width, Label4.Top + Label4.Height + t + 2, 50);
   with (FindComponent(cName) as TComboBox) do
   begin
      Clear;
      for i := 0 to RelaOperatorMax - 1 do
      begin
         Items.AddObject(RelaOperator[1][i], TObject(RelaOperator[0][i]));
      end;
      ItemIndex := 0;
      OnChange := RelaBoxChange;
   end;
   //
   if rowIndex <> 1 then
   begin
      Self.Height := Self.Height + SpeedButton1.Height;
   end;
end;

procedure TForm_SuperQuery.FormShow(Sender: TObject);
begin
   //
   if Length(ConfigSQLData) < 1 then Exit;
   g_row_Index := 1;
   CreateControls(g_row_Index);
end;

procedure TForm_SuperQuery.SpeedButton2Click(Sender: TObject);
begin
   ModalResult := mrCancel;
end;

procedure TForm_SuperQuery.SpeedButton1Click(Sender: TObject);
   procedure checkValue(rowIndex :Integer);
   var
      i :Integer;
      component :TComponent;
   begin
      for i := 1 to rowIndex do
      begin
         component := FindComponent(ControlNames[2] + IntToStr(i));
         if component = nil then Continue;
         //
         if component.ClassType = TdxTreeViewEdit then
         begin
            if trim((component as TdxTreeViewEdit).Text) = '' then
            begin
               MessageBox(GetActiveWindow, '请选择比较值!', '提示', MB_ICONWARNING);
               (component as TdxTreeViewEdit).SetFocus;
               Abort;
            end;
         end;
         //
         if (component.ClassType = TEdit) and (ConfigSQLData[(FindComponent(ControlNames[0] + IntToStr(i)) as TComboBox).ItemIndex].cType = cNumber) then
         begin
            if trim((component as TEdit).Text) = '' then
            begin
               (component as TEdit).Text := '0';
            end;
         end;
         //
         if component.ClassType = TComboBox then
         begin
            if (component as TComboBox).ItemIndex < 0 then
            begin
               MessageBox(GetActiveWindow, '请选择比较值!', '提示', MB_ICONWARNING);
               (component as TComboBox).SetFocus;
               Abort;
            end;
         end;
      end;
   end;
begin
   if g_row_Index < 1 then Exit;
   checkValue(g_row_Index);
   //
   resultSQL := createSQL(g_row_Index);
   //
   showmessage(  resultSQL );
   ModalResult := mrOk;
end;

end.