uquerymaker.pas

AbsDataBase5.16 最新版

            FieldS := FieldS + Format('%s(%s) AS "%s"',
              [SgColumns.Cells[I, 6], CurNum, SgColumns.Cells[I, 0]]) + ', '
        end
        else begin
          if Length(SgColumns.Cells[I, 6]) <> 0 then begin
            case longint(SgColumns.Objects[I, 6]) of
              1: begin
                SAgregate := 'avg'
              end;
              2: begin
                SAgregate := 'cnt'
              end;
              3: begin
                SAgregate := 'max'
              end;
              4: begin
                SAgregate := 'min'
              end;
              5: begin
                SAgregate := 'sum'
              end;
            end;
            FieldS := FieldS + Format('%s(%s."%s") AS "%s%2:s"',
              [SgColumns.Cells[I, 6], SgColumns.Cells[I, 11], SgColumns.Cells[I, 0], SAgregate]) + ', '
          end
          else begin
            FieldS := FieldS + Format('%s."%s"', [SgColumns.Cells[I, 11], SgColumns.Cells[I, 0]]) + ', '
          end;
        end;
        TableList.Add((Format('"%s" %s', [SgColumns.Cells[I, 1], SgColumns.Cells[I, 11]])));
      end;

      if SgColumns.Cells[I, 8] = SFShow[2] then begin
        if SgColumns.Cells[I, 7] = SCalcField then begin
          Ti     := (SgColumns.Objects[I, 1] as TTableImage);
          CurNum := (Ti.FColumns.Objects[longint(SgColumns.Objects[I, 7])] as TColumnsParams).FFormula;
          if Pos('CASE', UpperCase(CurNum)) <> 0 then begin
            FieldS := FieldS + Format(#13#10'  %s%s AS "%s"',
              [SgColumns.Cells[I, 6], CurNum, SgColumns.Cells[I, 0]]) + ', ';
          end else begin
            FieldS := FieldS + Format(#13#10'  %s(%s) AS "%s"',
              [SgColumns.Cells[I, 6], CurNum, SgColumns.Cells[I, 0]]) + ', ';
          end;
        end
        else begin
          if Length(SgColumns.Cells[I, 6]) <> 0 then begin
            case longint(SgColumns.Objects[I, 6]) of
              1: begin
                SAgregate := 'avg'
              end;
              2: begin
                SAgregate := 'cnt'
              end;
              3: begin
                SAgregate := 'max'
              end;
              4: begin
                SAgregate := 'min'
              end;
              5: begin
                SAgregate := 'sum'
              end;
            end;
            FieldS := FieldS + Format(#13#10'  %s(%s."%s") AS "%s%2:s"',
              [SgColumns.Cells[I, 6], SgColumns.Cells[I, 11], SgColumns.Cells[I, 0], SAgregate]) + ', '
          end
          else begin
            FieldS := FieldS + Format(#13#10'  %s."%s"', [SgColumns.Cells[I, 11], SgColumns.Cells[I, 0]]) + ', '
          end;
        end;
        TableList.Add((Format('"%s" %s', [SgColumns.Cells[I, 1], SgColumns.Cells[I, 11]])));
      end;

      if longint(SgColumns.Objects[I, 9]) > 0 then begin
        CurNum := Format('%.5d', [longint(SgColumns.Objects[I, 9])]);
        GroupList.AddObject(CurNum, TItem.Create(Format('%s."%s"', [SgColumns.Cells[I, 11], SgColumns.Cells[I, 0]])));
      end;
      if (Copy(SgColumns.Cells[I, 3], 1, 1) = 'A') then begin
        CurNum := Format('%.5d', [longint(SgColumns.Objects[I, 3])]);
        if SgColumns.Cells[I, 7] = SCalcField then begin
          OrderList.AddObject(CurNum, TItem.Create(Format('"%s" ASC', [SgColumns.Cells[I, 0]])))
        end
        else if Length(SgColumns.Cells[I, 6]) <> 0 then begin
          OrderList.AddObject(CurNum, TItem.Create(Format('"%s%s" ASC', [SAgregate, SgColumns.Cells[I, 0]])));
        end
        else begin
          OrderList.AddObject(CurNum, TItem.Create(Format('%s."%s" ASC', [SgColumns.Cells[I, 11], SgColumns.Cells[I, 0]])))
        end;
      end;
      if (Copy(SgColumns.Cells[I, 3], 1, 1) = 'D') then begin
        CurNum := Format('%.5d', [longint(SgColumns.Objects[I, 3])]);
        if SgColumns.Cells[I, 7] = SCalcField then begin
          OrderList.AddObject(CurNum, TItem.Create(Format('"%s" DESC', [SgColumns.Cells[I, 0]])))
        end
        else if Length(SgColumns.Cells[I, 6]) <> 0 then begin
          OrderList.AddObject(CurNum, TItem.Create(Format('"%s%s" DESC', [SAgregate, SgColumns.Cells[I, 0]])));
        end
        else begin
          OrderList.AddObject(CurNum, TItem.Create(Format('%s."%s" DESC', [SgColumns.Cells[I, 11], SgColumns.Cells[I, 0]])))
        end;
      end;
    end;
    if UpperCase(FieldS) = 'SELECT ' then begin
      FieldS := Trim(FieldS) + #13#10;
    end;
    GroupList.Sort;
    OrderList.Sort;
    HavingS := Trim(HavingS);
    if Length(HavingS) <> 0 then begin
      HavingS := ' HAVING (' + HavingS + ')'#13#10
    end;
    WhereS := Trim(WhereS);
    if Length(WhereS) <> 0 then begin
      WhereS := ' WHERE ' + WhereS + #13#10
    end;
    FieldS := TrimRight(FieldS);
    if Pos(',', FieldS) <> 0 then begin
      SetLength(FieldS, Length(FieldS) - 1)
    end;
    FieldS := FieldS + #13#10;
    TableS := '';
    if FJoinsList.Count > 0 then begin
      TableS := '  FROM ' + TJoinImage(FJoinsList[0]).FDescription + ','#13#10;
      for I := 1 to FJoinsList.Count - 1 do begin
        TableS := TableS + '      ' + TJoinImage(FJoinsList[I]).FDescription + ','#13#10;
      end;
      TableS := TrimRight(TableS);
      if Pos(',', TableS) <> 0 then begin
        SetLength(TableS, Length(TableS) - 1)
      end;
      TableS := TableS + #13#10;
    end
    else if TableList.Count > 0 then begin
      TableS := '  FROM ';
      for I := 0 to TableList.Count - 1 do begin
        TableS := TableS + TableList[I] + ', '
      end;
      TableS := TrimRight(TableS);
      if Pos(',', TableS) <> 0 then begin
        SetLength(TableS, Length(TableS) - 1)
      end;
      TableS := TableS + #13#10;
    end;
    GroupS := '';
    if GroupList.Count > 0 then begin
      GroupS := ' GROUP BY ';
      for I := 0 to GroupList.Count - 1 do begin
        GroupS := GroupS + (GroupList.Objects[I] as TItem).Item + ', '
      end;
      GroupS := TrimRight(GroupS);
      if Pos(',', GroupS) <> 0 then begin
        SetLength(GroupS, Length(GroupS) - 1)
      end;
      GroupS := GroupS + #13#10;
    end;
    OrderS := '';
    if OrderList.Count > 0 then begin
      OrderS := ' ORDER BY ';
      for I := 0 to OrderList.Count - 1 do begin
        OrderS := OrderS + (OrderList.Objects[I] as TItem).Item + ', '
      end;
      OrderS := TrimRight(OrderS);
      if Pos(',', OrderS) <> 0 then begin
        SetLength(OrderS, Length(OrderS) - 1)
      end;
      OrderS := OrderS + #13#10;
    end;
    if (UpperCase(FieldS) = 'SELECT'#13#10) or (UpperCase(FieldS) = 'SELECT DISTINCT'#13#10) then begin
      if CbDistinct.Checked then begin
        FieldS := 'SELECT DISTINCT *'#13#10
      end
      else begin
        FieldS := 'SELECT *'#13#10
      end;
      begin
        if FJoinsList.Count = 0 then begin
          TableS := '  FROM ';
          for I := 0 to FTablesList.Count - 1 do begin
            with TTableImage(FTablesList.Items[I]) do begin
              TableS := TableS + Format('"%s" %s', [CaptionTable, TableAlias]) + ', ';
            end
          end;
          TableS := TrimRight(TableS);
          if Pos(',', TableS) <> 0 then begin
            SetLength(TableS, Length(TableS) - 1)
          end;
          TableS := TableS + #13#10;
        end;
      end;
    end;
    if cbIntoTable.Checked then begin
      if cbMemory.Checked then begin
        Smem := Format('DROP TABLE IF EXISTS MEMORY %s;'#13#10, [sIntoTable]);
        S := Format('  INTO MEMORY %s'#13#10, [sIntoTable]);
      end else begin
        Smem := Format('DROP TABLE IF EXISTS %s;'#13#10, [sIntoTable]);
        S := Format('  INTO %s'#13#10, [sIntoTable]);
      end;
    end else begin
      Smem := '';
      S := '';
    end;
    reText := (Comment + Smem + FieldS + S + TableS + WhereS + GroupS + HavingS + OrderS);
  finally
    for I := 0 to GroupList.Count - 1 do begin
      Application.ProcessMessages;
      GroupList.Objects[I].Free
    end;
    for I := 0 to OrderList.Count - 1 do begin
      Application.ProcessMessages;
      OrderList.Objects[I].Free
    end;
    TableList.Free;
    GroupList.Free;
    OrderList.Free;
  end;
  end;

    if cbDoubleQuotes.Checked then
    for I := 1 to Length(reText) do begin
      Application.ProcessMessages;
      if reText[I] = '''' then reText[I] := '"'
    end else
    for I := 1 to Length(reText) do begin
      Application.ProcessMessages;
      if reText[I] = '"' then reText[I] := ''''
    end;

    re.Text := reText;
    Lexer := TABSLexer.Create(reText);
    try
    for i:=0 to Lexer.NumCommands-1 do
      for j:=0 to Lexer.Commands[i].NumTokens-1 do begin
        t := Lexer.Commands[i].Tokens[j];
        if t.TokenType = tktReservedWord then begin
          SelPos := t.ColumnNum;
          for K := 1 to t.LineNum - 1 do begin
            SelPos := SelPos + Length(re.Lines[K - 1]) + 2;
          end;
          Delete(reText, SelPos, Length(t.Text));
          if cbLowerCase.Checked then
            Insert(LowerCase(t.Text), reText, SelPos)
          else
            Insert(UpperCase(t.Text), reText, SelPos);
        end;
      end;
    finally
      Lexer.Free;
    end;
    re.Text := reText;
end;

procedure TfrmQueryMaker.TableImageDragDrop(Sender, Source: TObject; X, Y: integer);
var
  Td, Ta: TTableImage;
  Id, Ia: integer;
  S1:     string;
  S2:     string;
  JI:     TJoinImage;
  I:      integer;
begin
  if Source is TTreeView then begin
    Ta := Sender as TTableImage;
    tbDragDrop(Sender, Source, X + Ta.Left, Y + Ta.Top);
    Exit;
  end;
  Td := Source as TTableImage;
  Ta := Sender as TTableImage;
  Id := Td.SelectedItem;
  Ia := Ta.GetSelItem(Y);
  if (Id < 0) or (Ia < 0) or (Id > Td.Columns.Count - 1) or (Ia > Ta.Columns.Count - 1) then begin
    Exit
  end;
  if (Td.Columns.Objects[Id] as TColumnsParams).IsCalc or (Ta.Columns.Objects[Ia] as TColumnsParams).IsCalc then begin
    Exit;
  end;
  S1 := (Td.FColumns.Objects[Id] as TColumnsParams).FType;
  S2 := (Ta.FColumns.Objects[Ia] as TColumnsParams).FType;
  I  := Pos('(', S1);
  if I <> 0 then begin
    S1 := Copy(S1, 1, I - 1)
  end;
  I := Pos('(', S2);
  if I <> 0 then begin
    S2 := Copy(S2, 1, I - 1)
  end;
  if AreTypesCompatible(StrToAft(S1), StrToAft(S2)) then begin
    JI := TJoinImage.Create(Self);
    with JI do begin
      PopupMenu   := PmJoin;
      FromTableJoin := longint(Td);
      ToTableJoin := longint(Ta);
      FromFieldJoin := Id;
      ToFieldJoin := Ia;
      ABSJoinType := AjtInner;
      Parent      := FTablesBoard;
      OnMouseDown := tbMouseDown;
      OnDragOver  := tbDragOver;
      OnDragDrop  := tbDragDrop;
    end;
    FJoinsList.Add(JI);
    ExecJoin(1, FJoinsList.Count - 1);
    FTablesBoard.Refresh;
  end;
end;

procedure TfrmQueryMaker.TableImageDragOver(Sender, Source: TObject;
  X, Y: integer; State: TDragState; var Accept: boolean);
begin
  Accept := (Sender <> Source) and (Sender is TTableImage);
end;

procedure TfrmQueryMaker.TableImagePosChange(Sender: TObject; Top, Left: integer);
begin
  FTablesBoard.Refresh;
end;

procedure TfrmQueryMaker.ResetSort(Col: integer);
var
  I, J, K: integer;
begin
  K := longint(SgColumns.Objects[Col, 3]);
  if K = 0 then begin
    Exit
  end;
  SgColumns.Cells[Col, 3]   := '';
  SgColumns.Objects[Col, 3] := TObject(0);
  for I := 1 to SgColumns.Colcount - 1 do begin
    Application.ProcessMessages;
    J := longint(SgColumns.Objects[I, 3]);
    if (J > K) and (J > 0) then begin
      Dec(J);
      SgColumns.Objects[I, 3] := TObject(J);
      if Copy(SgColumns.Cells[I, 3], 1, 1) = 'A' then begin
        SgColumns.Cells[I, 3] := Format('Asc_%d', [J])
      end
      else begin
        SgColumns.Cells[I, 3] := Format('Desc_%d', [J])
      end
    end;
  end;
  J := longint(SgColumns.Objects[0, 3]);
  Dec(J);
  SgColumns.Objects[0, 3] := TObject(J);
end;

procedure TfrmQueryMaker.WriteToFile(const FileName: string);
var
  J:  integer;
  I:  integer;
  Fs: TFileStream;
  Cs: TCols;
begin
  Fs := TFileStream.Create(FileName, FmCreate or FmOpenWrite);
  Cs := TCols.Create(Self);
  try
    I := SgColumns.ColCount - 1;
    Fs.Write(I, 4);
    for I := 0 to SgColumns.Colcount - 1 do begin
      Cs.FSel.Clear;
      Cs.FObj.Clear;
      for J := 0 to SgColumns.Rowcount - 1 do begin
        Cs.FSel.Add(SgColumns.Cells[I, J]);
        Cs.FObj.Add(IntToStr(longint(SgColumns.Objects[I, J])));
      end;
      Fs.WriteComponent(Cs);
    end;
    I := 0;
    Fs.Write(I, 1);
    I := FJoinsList.Count - 1;
    Fs.Write(I, 4);
    for I := 0 to FJoinsList.Count - 1 do begin
      TJoinImage(FJoinsList[I]).WriteToFile(Fs);
    end;
    I := 0;
    Fs.Write(I, 1);
    I := FTablesList.Count - 1;
    Fs.Write(I, 4);
    for I := 0 to FTablesList.Count - 1 do begin
      TTableImage(FTablesList[I]).WriteToFile(Fs);
    end;
  finally
    Fs.Free;
    Cs.Free;
  end;
end;

procedure TfrmQueryMaker.ReadFromFile(const FileName: string);
var
  J:  integer;
  I:  integer;
  K:  integer;
  Fs: TFileStream;
  Ti: TTableImage;
  Ji: TJoinImage;
  Cs: TCols;
  Flag: integer;
  arS: array[0..11] of Integer;