gq_uquerymain.pas

亚惠快餐管理信息系统 包括亚惠快餐管理的各项功能

function TGQ_FQueryMain.OpenSource: Boolean;
var
  i, j: integer;
  tempstring: string;
begin
  Result := False;
  SqlString[0] := 'Select * ';
  SqlString[1] := 'From ' + TableName;
  if not GQ_FDataModule.OpenSql(SqlString[0] + SqlString[1]) then
    Exit;
  FldNames.Clear;
  for i := 0 to GQ_FDataModule.ADOQuery1.FieldCount - 1 do
  begin
    FldNames.Add(GQ_FDataModule.ADOQuery1.Fields[i].FieldName);
    if GQ_FDataModule.ADOQuery1.Fields[i].DataType in [ftInteger, ftFloat] then
      GroupingFlds.Add(GQ_FDataModule.ADOQuery1.Fields[i].FieldName);
  end;
  SetLength(OrderFlag, GQ_FDataModule.ADOQuery1.FieldCount);
  for i := 0 to GQ_FDataModule.ADOQuery1.FieldCount - 1 do
    OrderFlag[i] := False;
  FldCNames.Assign(FldNames);
  ViewFieldNames.Assign(FldCNames);
  Result := True;
  SqlString[0] := 'Select * ';
  GQ_FDataModule.ADOConnection1.GetTableNames(LinkFld, false);

  if UpperCase(Copy(TableName, 1, 1)) <> 'T' then
    Exit;
  tempstring := 'D' + copy(TableName, 2, strlen(pchar(TableName)));

  if LinkFld.IndexOf(tempstring) = -1 then
  begin
    Exit;
  end;
  SqlString[1] := 'From D' + Copy(TableName, 2, 1000);
  if not GQ_FDataModule.OpenSql(SqlString[0] + SqlString[1]) then
    Exit;
  with GQ_FDataModule.ADOQuery1 do
    while not Eof do
    begin
      j := FldNames.Indexof(trim(FieldByName('FieldNameE').Asstring));
      if j <> -1 then
        FldCNames[j] := trim(FieldByName('FieldNameC').AsString);
      Next;
    end;
  //将中文字段值付给GroupingFlds
end;

procedure TGQ_FQueryMain.QueryByCondition;
begin
  {  str:='';
    for i:=0 to 4 do
      str:=str+SqlString[i];
    if str<>'' then  begin
      try
        FDataModule.OpenSql(str);
      except
        ShowMessage('Error');
        Exit;
      end;

      setlength(n,FDataModule.ADOQuery1.FieldCount);

      for i:=0 to FDataModule.ADOQuery1.FieldCount-1 do begin
        n[i]:=TSTringList.Create;
        FDataModule.ADOQuery1.First;
        stringGrid1.Cells[i,0]:=FDataModule.ADOQuery1.Fields[i].DisplayName;
        while not FDataModule.ADOQuery1.Eof do begin
          n[i].Add(FDataModule.ADOQuery1.Fields[i].AsString);
          FDataModule.ADOQuery1.Next;
        end;
      end;

      DisplayResult(n);

      for i:=0 to FDataModule.ADOQuery1.FieldCount-1 do n[i].Free;

    end; //of first if}
end;

procedure TGQ_FQueryMain.CancelStepClick(Sender: TObject);
var
  tempcount: integer;
begin
  for tempcount := 0 to 5 do
    viewsqlstring[tempcount] := '';
  OperateBox.ItemIndex := -1;
  FldNameBox.ItemIndex := -1;
  OperateBox.ItemIndex := -1;
  FldValueBox.ItemIndex := -1;
  FldValueBox.Text := '';
  combobox2.text := '';
  combobox3.text := '';
  combobox4.text := '';
  combobox5.text := '';
  combobox6.text := '';
  FldValueBox.Text := '';
  WhereBox.items.Clear;
  SelectBox.Items.Assign(selectlist.items);
  OrderBox.items.Clear;
  GroupBox.items.Clear;
  ListBox1.Items.Clear;
  WhereBox.Items.Clear;
  GroupingBox.items.Clear;
  NoteBook1.PageIndex := 0;
  previewstep.Enabled := false;
  laststep.Enabled := false;
  nextstep.Enabled := true;
  Shape7.Brush.Color := clLime;
  Shape2.Brush.Color := clSilver;
  Shape3.Brush.Color := clSilver;
  Shape4.Brush.Color := clSilver;
  Shape5.Brush.Color := clSilver;
  Shape6.Brush.Color := clSilver;
end;

procedure TGQ_FQueryMain.GroupingFldSelClick(Sender: TObject);
var
  tempcount: integer;
  tempstringlist: Tstrings;
  HavingForm: THavingForm;
begin
  if GroupBox.Items.Count = 0 then
  begin
    messagedlg('分组字段不要为空,好吗？', mtInformation, [mbOK], 0);
    exit;
  end;
  with GQ_FDataModule.ADOQuery1 do
  begin
    Close;
    Sql.Clear;
    Sql.Add('Select *');
    Sql.Add('From ' + TableName);
    Open;
    Application.CreateForm(THavingForm, HavingForm);
    HavingForm.CanGroupbox.items.clear;
    tempstringlist := TstringList.Create;
    for tempcount := 0 to FieldCount - 1 do
    begin
      case Fields[tempcount].DataType of
        ftString:
          begin
            StringType := Strings;
            tempstringlist.Add('Strings');
          end;
        ftBoolean:
          begin
            StringType := Logical;
            tempstringlist.Add('Logical');
          end;
        ftDate, ftDateTime, ftTime:
          begin
            StringType := Date;
            tempstringlist.Add('Date');
          end;
      else
        begin
          StringType := Number;
          tempstringlist.Add('Data');
        end;
      end;
      HavingForm.CanGroupbox.items.add(fields[tempcount].FieldName);
    end;
  end;
  HavingForm.inithavingForm(false, Date);
  HavingForm.ss.Assign(tempstringlist);
  if HavingForm.ShowModal = mrOK then
    groupingbox.Items.Add('(' + havingForm.ListBox3.Items[0] + ')');
  tempstringlist.free;
  HavingForm.Free;
end;

procedure TGQ_FQueryMain.PreviewStepClick(Sender: TObject);
var
  i: integer;
begin
  for i := 0 to 6 do
    sqlstring[i] := '';
  if not FormSqlString then
  begin
    MessageDlg('未能成功地生成查询语句，请检查数据库环境或您设置的条件是否完备！', mtInformation, [mbOk], 0);
    Exit;
  end;
  Query;
end;

{--------------------------------------------------------------------}

function TGQ_FQueryMain.FormSqlString: Boolean;
begin
  Result := False;
  if ((ServerName = '') or (DataBaseName = '') or (TableName = '')) then
    Exit;
  if not FormSelectSql then
    Exit;
  if not FormFromSql then
    Exit;
  if not FormWhereSql then
    Exit;
  if not FormOrderSql then
    Exit;
  if not FormGroupSql then
    Exit;
  if not FormGroupingSql then
    Exit;
  Result := True;
end;

function TGQ_FQueryMain.FormSelectSql: Boolean;
var
  i: integer;
begin
  Result := False;
  if SelectBox.Items.Count = 0 then
    SelectBox.Items.Assign(SelectList.Items);
  SqlString[0] := 'Select ';
  if selectbox.items.count > 1 then
    for i := 0 to SelectBox.Items.Count - 2 do
      SqlString[0] := SqlString[0] + trim(SelectBox.Items[i]) + ',';
  SqlString[0] := SqlString[0] + trim(SelectBox.Items[SelectBox.Items.Count - 1])
    + ' ';
  Result := True;
end;

function TGQ_FQueryMain.FormFromSql: Boolean;
begin
  Result := False;
  SqlString[1] := 'From ' + TableName;
  Result := True;
end;

function TGQ_FQueryMain.FormWhereSql: Boolean;
begin
  Result := False;
  if listBox1.Items.Count = 0 then
    SqlString[2] := ' '
  else
  begin
    SqlString[2] := ' Where ';
    Sqlstring[2] := Sqlstring[2] + listbox1.items[0];
  end;
  Result := True;
end;

function TGQ_FQueryMain.FormOrderSql: Boolean;
var
  i: integer;
begin
  Result := False;
  if OrderBox.Items.Count = 0 then
    SqlString[5] := ''
  else
  begin
    SqlString[5] := ' Order By ';
    if orderbox.items.count > 1 then
      for i := 0 to OrderBox.Items.Count - 2 do
        SqlString[5] := SqlString[5] + OrderBox.Items[i] + ',';
    SqlString[5] := SqlString[5] + OrderBox.Items[OrderBox.Items.Count - 1];
  end;
  Result := True;
end;

function TGQ_FQueryMain.FormGroupSql: Boolean;
var
  i: integer;
begin
  Result := False;
  if GroupBox.Items.Count = 0 then
    SqlString[3] := ''
  else
  begin
    SqlString[0] := 'Select ';
    SqlString[3] := 'Group By ';
    if GroupBox.Items.Count > 1 then
    begin
      for i := 0 to GroupBox.Items.Count - 2 do
      begin
        SqlString[0] := SqlString[0] + GroupBox.Items[i] + ',';
        SqlString[3] := SqlString[3] + GroupBox.Items[i] + ',';
      end;
      SqlString[0] := SqlString[0] + GroupBox.Items[GroupBox.Items.Count - 1];
      SqlString[3] := SqlString[3] + GroupBox.Items[GroupBox.Items.Count - 1];
    end
    else
    begin
      SqlString[0] := SqlString[0] + GroupBox.Items[GroupBox.Items.Count - 1];
      SqlString[3] := SqlString[3] + GroupBox.Items[GroupBox.Items.Count - 1];
    end;
    sqlstring[5] := ''
      {  begin
sqlstring[5]:=' order by ';
for tempcount:=0 to GroupsortForm.sortlist.Items.Count-2 do
sqlstring[5]:=sqlstring[5]+ GroupsortForm.sortlist.Items[tempcount]+',';
sqlstring[5]:=sqlstring[5]+ GroupsortForm.sortlist.Items[tempcount];
end;
}
  end;
  Result := True;
end;

function TGQ_FQueryMain.FormGroupingSql: Boolean;
var
  i: integer;
  groupinglabel: string;
  temptype: TReplaceFlags;
begin
  result := false;
  if groupbox.Items.Count = 0 then
  begin
    sqlstring[4] := '';
    sqlstring[6] := '';
    {if listbox2.Items.Count<>0 then
     begin
     sqlstring[6]:=' compute ';
       if listbox2.items.count>1 then
       for i:=0 to listbox2.items.Count-2 do  sqlstring[6]:= sqlstring[6]+listbox2.items[i]+',';
         sqlstring[6]:=sqlstring[6]+ listbox2.items[listbox2.items.count-1];
     end;
     }
  end
  else
  begin
    if fieldchoosebox.Items.Count <> 0 then
      sqlstring[4] := ' having ' + fieldchoosebox.Items[0];
    if groupingbox.Items.Count <> 0 then
      for i := 0 to groupingbox.Items.Count - 1 do
      begin
        temptype := [rfReplaceAll, rfIgnoreCase];
        sqlstring[0] := sqlstring[0] + ',' + groupingbox.Items[i];
        groupinglabel := groupingbox.items[i];
        groupinglabel := stringreplace(groupinglabel, 'max', '最大值',
          temptype);
        groupinglabel := stringreplace(groupinglabel, 'min', '最小值',
          temptype);
        groupinglabel := stringreplace(groupinglabel, 'sum', '求和', temptype);
        groupinglabel := stringreplace(groupinglabel, 'count', '列数',
          temptype);
        groupinglabel := stringreplace(groupinglabel, 'avg', '平均值',
          temptype);
        sqlstring[0] := sqlstring[0] + '''' + groupinglabel + '''';
      end;

    if listbox2.Items.Count <> 0 then
    begin
      // sqlstring[6]:=' compute ';
      sqlstring[3] := sqlstring[3] + ' with rollup ';
      if listbox2.items.count <> 0 then
        for i := 0 to listbox2.items.Count - 1 do
          sqlstring[0] := sqlstring[0] + ', grouping(' + listbox2.items[i] + ')'
            + '''' + '按' + listbox2.items[i] + '小计' + '''';
      {for i:=0 to listbox2.items.Count-2 do  sqlstring[6]:= sqlstring[6]+listbox2.items[i]+',';
        sqlstring[6]:=sqlstring[6]+ listbox2.items[listbox2.items.count-1];}

//    if radiobutton2.Checked=false then sqlstring[6]:=sqlstring[6]+' by '+groupsortform.sortbox.Items[0];
    end;

  end;
  //if (trim(sqlstring[6])<>'')and(trim(sqlstring[5])<>'') then sqlstring[6]:=sqlstring[6]+copy(sqlstring[5],7,length(sqlstring[5]))+' ';
  result := true;
end;

procedure TGQ_FQueryMain.ComboBox1Click(Sender: TObject);
var
  tempcount: integer;
begin
  if ComboBox1.ItemIndex = -1 then
    exit;
  ComboBox5.Items.Clear;
  with GQ_FDataModule.ADOQuery1 do
  begin
    Close;
    Sql.Clear;
    Sql.Add('Select *');
    Sql.Add('From ' + ComboBox1.text);
    Open;
    if combobox1.text = TableName then
      combobox5.Items.assign(FldCNames)
    else
      for tempcount := 0 to fieldcount - 1 do
        ComboBox5.Items.Add(fields[tempcount].FieldName);
  end;
end;

procedure TGQ_FQueryMain.ComboBox1Exit(Sender: TObject);
begin
  //if ComboBox1.Items.IndexOf(ComboBox1.Text)<>-1 then
end;

procedure TGQ_FQueryMain.ComboBox2Click(Sender: TObject);
var
  TempCount: integer;
  TempString: string;
  TempPos: integer;
begin
  TableName2 := '';
  if ComboBox2.ItemIndex = -1 then
    Exit;
  if Trim(ComboBox2.Text) = '' then
    Exit;
  if Trim(ComboBox2.Text) = '空' then
  begin
    ComboBox6.Items.Clear;
    CheckListBox2.Items.Clear;
    Exit;
  end;
  FldNames2.Clear;
  FldCNames2.Clear;
  ComboBox6.Items.Clear;
  if pos(' (', combobox2.text) = 0 then
    tablename2 := GetTableEName(trim(combobox2.text))
  else
    tablename2 := GetTableEName(trim(copy(comboBox2.text, 1, pos(' (',
      combobox2.text) - 1)));

  with GQ_FDataModule.ADOQuery1 do
  begin
    Close;
    Sql.Clear;
    Sql.Add('Select * ');
    Sql.Add('From ' + trim(tablename2));
    Open;
    for tempcount := 0 to fieldcount - 1 do
      FldNames2.Add(fields[tempcount].FieldName);
    FldCNames2.Assign(FldNames2);
    CheckListBox2.Items.Assign(FldCNames2);
  end;
  Combobox6.Items.Assign(FldNames2);

  speedbutton2click(sender);
  if uppercase(tablename2[1]) <> 'T' then
    exit;
  tempstring := trim(tablename2);
  tempstring[1] := 'D';
  if LinkFld.IndexOf(tempstring) = -1 then
    exit;
  with GQ_FDataModule.ADOQuery1 do
  begin