uquerymaker.pas

AbsDataBase5.16 最新版

  Application.ProcessMessages;
  for I := 0 to SgColumns.Rowcount - 1 do begin
    SgColumns.Cells[Colno, I]   := '';
    SgColumns.Objects[Colno, I] := nil;
  end;
  if Colno < SgColumns.Colcount - 1 then begin
    for I := Colno to SgColumns.Colcount - 2 do begin
      for Ii := 0 to SgColumns.Rowcount - 1 do begin
        SgColumns.Cells[I, Ii]   := SgColumns.Cells[I + 1, Ii];
        SgColumns.Objects[I, Ii] := SgColumns.Objects[I + 1, Ii];
      end
    end;
  end;
  if SgColumns.Colcount < 3 then begin
    GenerateSQL(FrmMain.ReSQL);
    Exit;
  end;
  SgColumns.Colcount := SgColumns.Colcount - 1;
  if ColNo < SgColumns.Colcount then begin
    SelectColumn(ColNo)
  end
  else begin
    SelectColumn(SgColumns.Colcount - 1)
  end;
end;

procedure TfrmQueryMaker.AgregateClick(Sender: TObject);
begin
  SgColumns.Objects[SgColumns.Col, 6] := TObject((Sender as TmenuItem).Tag);
  case (Sender as TmenuItem).Tag of
    0: begin
      SgColumns.Cells[SgColumns.Col, 6]  := '';
      SgColumns.Cells[SgColumns.Col, 10] := '';
    end;
    1: begin
      SgColumns.Cells[SgColumns.Col, 6] := 'AVG'
    end;
    2: begin
      SgColumns.Cells[SgColumns.Col, 6] := 'COUNT'
    end;
    3: begin
      SgColumns.Cells[SgColumns.Col, 6] := 'MAX'
    end;
    4: begin
      SgColumns.Cells[SgColumns.Col, 6] := 'MIN'
    end;
    5: begin
      SgColumns.Cells[SgColumns.Col, 6] := 'SUM'
    end;
  end;
  GenerateSQL(FrmMain.ReSQL);
end;

procedure TfrmQueryMaker.PmNoSortClick(Sender: TObject);
begin
  ResetSort(SgColumns.Col);
  GenerateSQL(FrmMain.ReSQL);
end;

procedure TfrmQueryMaker.PmASCClick(Sender: TObject);
var
  J: integer;
begin
  if Length(Trim(SgColumns.Cells[SgColumns.Col, 3])) = 0 then begin
    J := longint(SgColumns.Objects[0, 3]);
    Inc(J);
    SgColumns.Objects[0, 3] := TObject(J);
    SgColumns.Objects[SgColumns.Col, 3] := TObject(J);
  end
  else begin
    J := longint(SgColumns.Objects[SgColumns.Col, 3])
  end;
  SgColumns.Cells[SgColumns.Col, 3] := Format('Asc_%d', [J]);
  GenerateSQL(FrmMain.ReSQL);
end;

procedure TfrmQueryMaker.PmDESCClick(Sender: TObject);
var
  J: integer;
begin
  if Length(Trim(SgColumns.Cells[SgColumns.Col, 3])) = 0 then begin
    J := longint(SgColumns.Objects[0, 3]);
    Inc(J);
    SgColumns.Objects[0, 3] := TObject(J);
    SgColumns.Objects[SgColumns.Col, 3] := TObject(J);
  end
  else begin
    J := longint(SgColumns.Objects[SgColumns.Col, 3])
  end;
  SgColumns.Cells[SgColumns.Col, 3] := Format('Desc_%d', [J]);
  GenerateSQL(FrmMain.ReSQL);
end;

procedure TfrmQueryMaker.SgColumnsDrawCell(Sender: TObject;
  ACol, ARow: integer; Rect: TRect; State: TGridDrawState);
begin
  if (ACol > 0) then begin
    with SgColumns.Canvas do begin
      Font.Color := ClFieldType;
      if (ARow in [3..6, 8..10]) and (GdSelected in State) then begin
        Brush.Color := ClIndexField;
        Font.Color  := ClWindowText;
        TextRect(Rect, Rect.Left + 2, Rect.Top + 2, SgColumns.Cells[ACol, ARow]);
      end;
      if ARow in [0..2, 7, 11] then begin
        Brush.Color := ClBtnFace;
        Font.Style  := [FsBold];
        TextRect(Rect, Rect.Left + 2, Rect.Top + 2, SgColumns.Cells[ACol, ARow]);
      end;
    end
  end;
end;

procedure TfrmQueryMaker.AddCalcFldClick(Sender: TObject);
var
  Ti:    TTableImage;
  S, NameField: string;
  PosTable: TPoint;
  AWidth: integer;
begin
  Ti := TTableImage(PmTable.PopupComponent.Tag);
      S := '';
      NameField := 'Enter Calc Field Name Here';
      if ExecuteCondition(FTablesList, S, NameField, CoCalc) then begin
        if (Length(S) <> 0) and (Length(NameField) <> 0) then begin
          NameField := Trim(NameField);
          Ti.FColumns.AddObject(NameField, TColumnsParams.Create(Ti));
          with(Ti.FColumns.Objects[Ti.FColumns.Count - 1] as TColumnsParams) do begin
            FType      := SCalcField;
            FFormula   := S;
            FIsCalc    := True;
            FNameWidth := Canvas.TextWidth(NameField);
            FTypeWidth := Canvas.TextWidth(FType);
            FNameColor := ClRed;
            FTypeColor := ClFieldType;
          end;
          AjustPosWidthTables(PosTable, AWidth);
        end
    else begin
      if Length(S) = 0 then
        MessageDlg(Format(SAddEditCalcField, [NameField]), mtInformation, [mbOK], 0)
      else
        MessageDlg(SAddCalcField, mtInformation, [mbOK], 0)
    end
  end;
end;

procedure TfrmQueryMaker.RemoveTableClick(Sender: TObject);
var
  I, J: integer;
  Ti:   TTableImage;
  PosTable: TPoint;
  AWidth: integer;

  procedure ReNumberTables(CurAliasTable: integer);
  var
    I, J: integer;
  begin
    for I := 0 to FTablesList.Count - 1 do begin
      Application.ProcessMessages;
      J := TTableImage(FTablesList[I]).FTableNumber;
      if J > CurAliasTable then begin
        Dec(J);
        TTableImage(FTablesList[I]).FTableNumber := J;
      end;
    end;
    for I := 1 to SgColumns.Colcount - 1 do begin
      Application.ProcessMessages;
      J := longint(SgColumns.Objects[I, 11]);
      if J > CurAliasTable then begin
        Dec(J);
        SgColumns.Objects[I, 11] := TObject(J);
        SgColumns.Cells[I, 11] := (SgColumns.Objects[I, 1] as TTableImage).TableAlias;
      end;
    end;
    Dec(FTableCounter);
  end;

begin
  Ti := TTableImage(PmTable.PopupComponent.Tag);
  for I := SgColumns.ColCount - 1 downto 1 do begin
    Application.ProcessMessages;
    if Ti = (SgColumns.Objects[I, 1] as TTableImage) then begin
      DeleteColumn(I);
    end;
  end;
  for I := FJoinsList.Count - 1 downto 0 do begin
    Application.ProcessMessages;
    if (longint(Ti) = TJoinImage(FJoinsList[I]).FFromTableJoin) or
      (longint(Ti) = TJoinImage(FJoinsList[I]).FToTableJoin) then begin
      ExecJoin(5, I);
    end;
  end;
  for I := FTablesList.Count - 1 downto 0 do begin
    Application.ProcessMessages;
    if Ti = TTableImage(FTablesList[I]) then begin
      J := TTableImage(FTablesList[I]).FTableNumber;
      TTableImage(FTablesList[I]).Free;
      FTablesList.Delete(I);
      ReNumberTables(J);
    end;
  end;
  for I := 0 to FJoinsList.Count - 1 do begin
    Application.ProcessMessages;
    ExecJoin(1, I);
  end;
  if FTablesList.Count = 0 then begin
    frmMain.Settings.LastSQMFile := '';
    frmQueryMaker.Caption := Format(sMakerCaption, [sNewProject]);
    CbDistinct.Checked := False;
    SgColumns.Objects[0, 0] := TObject(0);
    cbLowerCase.Checked := True;
    SgColumns.Objects[0, 1] := TObject(1);
    cbDoubleQuotes.Checked := True;
    SgColumns.Objects[0, 2] := TObject(1);
    cbIntoTable.Checked := False;
    SgColumns.Objects[0, 4] := TObject(0);
    cbMemory.Checked := False;
    cbMemory.Enabled := False;
    SgColumns.Objects[0, 6] := TObject(0);
    cbSaveComments.Checked := True;
    SgColumns.Objects[0, 5] := TObject(1);
    Label1.Visible := True;
  end;
  AjustPosWidthTables(PosTable, AWidth);
  GenerateSQL(FrmMain.ReSQL);
end;

procedure TColumnsParams.ReadFromFile(Fs: TFileStream);
begin
  Fs.ReadComponent(Self);
end;

procedure TColumnsParams.WriteToFile(Fs: TFileStream);
begin
  Fs.WriteComponent(Self);
end;

constructor TItem.Create(Value: string);
begin
  FItem := Value;
end;

procedure TfrmQueryMaker.tbMouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: integer);

  function FindJoin(X, Y: integer): integer;
  var
    I:    integer;
    Join: TJoinImage;
    AMin, AMinNumber: integer;
    Distance: longint;
  begin
    Result := -1;
    AMin   := MaxInt;
    AMinNumber := -1;
    for I := 0 to FJoinsList.Count - 1 do begin
      Join := TJoinImage(FJoinsList[I]);
      with
        Join do begin
        Distance := Abs((X - FromPointX) * (ToPointY - FromPointY) - (Y - FromPointY) * (ToPointX - FromPointX));
        if Distance <= AMin then begin
          AMin := Distance;
          AMinNumber := I;
        end;
      end;
      if AMin < 500 then begin
        Result := AMinNumber
      end;
    end;
  end;

begin
  if (Button = MbRight) then begin
    FSelectedJoin := FindJoin(X, Y);
    if FSelectedJoin < 0 then begin
      PmJoin.AutoPopup := False;
      Exit;
    end;
    PmJoin.AutoPopup := True;
  end;
end;

procedure TfrmQueryMaker.tbDragOver(Sender, Source: TObject; X, Y: integer; State: TDragState; var Accept: boolean);
begin
  if Source is TTreeView then begin
    Accept := True;
    if State = DsDragLeave then begin
      (Source as TTreeView).DragCursor := CrDrag
    end;
    if (State = DsDragEnter) and ((Source as TTreeView).SelectionCount > 1) then begin
      (Source as TTreeView).DragCursor := CrMultiDrag
    end;
  end
end;

procedure TfrmQueryMaker.tbDragDrop(Sender, Source: TObject; X, Y: integer);
var
  I: integer;
  TmpList: TStringList;
  TmpTable: TABSTable;
begin
  if Source is TTreeView then begin
    FTablesBoard.Hide;
    TmpList  := TStringList.Create;
    TmpTable := TABSTable.Create(Application);
    TmpTable.DatabaseName := FrmMain.TCurTable.DatabaseName;
    try
      for I := 0 to FrmMain.TvDatabase.Items.Count - 1 do begin
        if FrmMain.TvDatabase.Items[I].Selected then begin
          TmpList.Add(FrmMain.TvDatabase.Items[I].Text)
        end
      end;
      FrmMain.TvDatabase.ClearSelection(True);
      for I := 0 to TmpList.Count - 1 do begin
        TmpTable.TableName := TmpList[I];
        TmpTable.Open;
        try
          if TmpList.Count = 1 then begin
            AddTable(TmpTable, Point(X, Y))
          end
          else begin
            AddTable(TmpTable, Point(0, 0))
          end;
        finally
          TmpTable.Close;
        end;
      end;
    finally
      FTablesBoard.Show;
      TmpList.Free;
      TmpTable.Free;
    end;
  end;
end;

constructor TJoinImage.Create(AOwner: TComponent);
begin
  inherited;
  Align := AlClient;
end;

procedure TJoinImage.Paint;
var
  X1, Y1, X2, Y2, D1, D2: longint;
  A1: longint;
begin
  A1 := Canvas.TextHeight('Wj') div 2;
  D1 := 1;
  D2 := 1;
  if (TTableImage(FFromTableJoin) <> nil) and (TTableImage(FToTableJoin) <> nil) then begin
    with(TTableImage(FFromTableJoin).FColumns.Objects[FFromFieldJoin] as TColumnsParams) do begin
      Y1 := FCoord.Y + TTableImage(FFromTableJoin).Top + A1;
      Y2 := (TTableImage(FToTableJoin).FColumns.Objects[FToFieldJoin] as TColumnsParams).FCoord.Y + TTableImage(FToTableJoin).Top + A1;
      X1 := 0;
      X2 := Maxint;
      if Abs(TTableImage(FFromTableJoin).Left - TTableImage(FToTableJoin).Left) < Abs(X1 - X2) then begin
        X1 := TTableImage(FFromTableJoin).Left;
        X2 := TTableImage(FToTableJoin).Left;
        D1 := -1;
        D2 := -1;
      end;
      if Abs(TTableImage(FFromTableJoin).Left - (TTableImage(FToTableJoin).Left + TTableImage(FToTableJoin).Width - 1)) < Abs(X1 - X2) then begin
        X1 := TTableImage(FFromTableJoin).Left;
        X2 := TTableImage(FToTableJoin).Left + TTableImage(FToTableJoin).Width - 1;
        D1 := -1;
        D2 := 1;
      end;
      if Abs((TTableImage(FFromTableJoin).Left + TTableImage(FFromTableJoin).Width - 1) - TTableImage(FToTableJoin).Left) < Abs(X1 - X2) then begin
        X1 := TTableImage(FFromTableJoin).Left + TTableImage(FFromTableJoin).Width - 1;
        X2 := TTableImage(FToTableJoin).Left;
        D1 := 1;
        D2 := -1;
      end;
      if Abs((TTableImage(FFromTableJoin).Left + TTableImage(FFromTableJoin).Width - 1) - (TTableImage(FToTableJoin).Left + TTableImage(FToTableJoin).Width - 1)) < Abs(X1 - X2) then begin
        X1 := TTableImage(FFromTableJoin).Left + TTableImage(FFromTableJoin).Width - 1;
        X2 := TTableImage(FToTableJoin).Left + TTableImage(FToTableJoin).Width - 1;
        D1 := 1;
        D2 := 1;
      end;
      inc(Y1);
      inc(Y2);
      FromPointX := X1;
      FromPointY := Y1;
      ToPointX   := X2;
      ToPointY   := Y2;
      if (Y1 <= TTableImage(FFromTableJoin).Top + TTableImage(FFromTableJoin).Height - 1) and
        (Y2 <= TTableImage(FToTableJoin).Top + TTableImage(FToTableJoin).Height - 1) then begin
        with Canvas do begin
          Pen.Mode  := PmCopy;
          Pen.Style := Pssolid;
          Pen.Color := TJoinColors[FABSJoinType];
          Moveto(X1 + TForm(Parent).HorzScrollBar.Position, Y1 + TForm(Parent).VertScrollBar.Position);
          Lineto(X1 + (D1 * 8) + TForm(Parent).HorzScrollBar.Position, Y1 + TForm(Parent).VertScrollBar.Position);
          Lineto(X2 + (D2 * 8) + TForm(Parent).HorzScrollBar.Position, Y2 + TForm(Parent).VertScrollBar.Position);
          Lineto(X2 + TForm(Parent).HorzScrollBar.Position, Y2 + TForm(Parent).VertScrollBar.Position);
          Moveto(X2 + (D2 * 4) + TForm(Parent).HorzScrollBar.Position, Y2 - 4 + TForm(Parent).VertScrollBar.Position);
          Lineto(X2 + TForm(Parent).HorzScrollBar.Position, Y2 + TForm(Parent).VertScrollBar.Position);
          Moveto(X2 + (D2 * 4) + TForm(Parent).HorzScrollBar.Position, Y2 + 4 + TForm(Pa