gq_uquerymain.pas

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

    Close;
    Sql.Clear;
    Sql.Add('Select * ');
    Sql.Add('From ' + tempstring);
    Open;
    while not EOF do
    begin
      tempPos := fldnames2.IndexOf(trim(fieldbyname('FieldNameE').Asstring));
      if tempPos <> -1 then
        FldCNames2[tempPos] := trim(fieldbyname('FieldNameC').Asstring);
      next;
    end;
  end;
  ComboBox6.Items.Assign(FldCnames2);
  CheckListBox2.Items.Assign(FldCnames2);
  speedbutton2click(sender);
end;

procedure TGQ_FQueryMain.ComboBox2Exit(Sender: TObject);
//var
  //  tempcount:integer;
begin

  {if trim(Combobox2.Text)=tablename2 then exit;
  if trim(Combobox2.Text)=tablename then
     begin
        showmessage('对不起，不能对同一表进行连接');
        Combobox2.Text:=tablename2;
        exit;
     end;
  if Combobox2.Items.IndexOf( trim(combobox2.text))=-1 then
     begin
        combobox2.Text:=tablename2;
        exit;
     end;
   with GQ_FDataModule.ADOQuery1 do
      begin
        Close;
        Sql.Clear;
        Sql.Add('Select * ');
        Sql.Add('From '+trim(ComboBox2.text));
        Open;
        fldnames2.Clear;
        for tempcount:=0 to fieldcount-1 do fldnames2.Add(fields[tempcount].FieldName);
        fldcnames2.Assign(fldnames2);
        combobox6.Items.Assign(fldcnames);
      end;
    tablename2:=comboBox2.text;
    }
end;

procedure TGQ_FQueryMain.ComboBox6Click(Sender: TObject);
begin
  if ComboBox6.itemIndex = -1 then
    exit;
  CheckListBox2.itemIndex := CheckListBox2.Items.IndexOf(combobox6.Text);
  CheckListBox2.Checked[CheckListBox2.itemIndex] := true;
end;

procedure TGQ_FQueryMain.ComboBox5Click(Sender: TObject);
begin
  if Combobox5.ItemIndex = -1 then
    exit;
  CheckListBox1.itemIndex := CheckListBox1.Items.IndexOf(combobox5.Text);
  CheckListBox1.Checked[CheckListBox1.itemIndex] := true;
end;

procedure TGQ_FQueryMain.BitBtn1Click(Sender: TObject);
var
  ExampleForm: TExampleForm;
begin
  Application.CreateForm(TExampleForm, ExampleForm);
  ExampleForm.Showmodal;
  ExampleForm.Free;
end;

function TGQ_FQueryMain.CreateMyView: boolean;
var
  FieldDataType: TFieldType;
  i, MTFCount, ETfCount: integer;
begin
  MTFCount := 0;
  ETFCount := 0;
  for i := 0 to 5 do
    viewSqlstring[i] := '';
  result := false;
  //以下处理为用户自己选择连接条件
  if not checkbox1.checked then
  begin
    if trim(MyViewName) = '' then
    begin
      Showmessage('视图名称未定义！');
      Exit;
    end;
    //以下为选择了从表时的情况
    if (trim(tablename2) <> '') and (trim(tablename2) <> '空') then
    begin
      if trim(combobox1.text) = trim(tablename2) then
      begin
        messagedlg('对不起，主从表不能重复！', mtInformation, [mbOK], 0);
        exit;
      end;
      if trim(Combobox3.Text) = '' then
      begin
        messagedlg('请选择运算符如果你准备进行单表操作,' + #10#13 +
          '请保持从表为空', mtInformation, [mbOK], 0);
        exit;
      end; //作为比较的字段类型要相符，运算符不能为空
      if trim(Combobox6.Text) = '' then
      begin
        messagedlg('从表字段不能为空', mtInformation, [mbOK], 0);
        exit;
      end;
      if trim(Combobox5.text) = '' then
      begin
        messagedlg('主表字段不能为空', mtInformation, [mbOK], 0);
        exit;
      end;
      if trim(combobox4.text) = '' then
      begin
        messagedlg('连接类型不能为空', mtInformation, [mbOK], 0);
        exit;
      end;
      //判断用户选择的连接字段是否类型匹配
      TableName := ComboBox1.Text;
      with GQ_FDataModule.ADOQuery1 do
      begin
        Close;
        Sql.Clear;
        sql.Add('Select * From ' + GetTableEName(TableName));
        Open;
        FldNames.Clear;
        for i := 0 to FieldCount - 1 do
          FldNames.Add(Fields[i].FieldName);
        FieldDataType := FieldByName(combobox5.text).DataType;
        Close;
        Sql.Clear;
        Sql.Add('Select ' +
          Trim(FldNames2[Combobox6.Items.Indexof(trim(combobox6.text))]));
        Sql.Add('From ' + Trim(GetTableEName(TableName2)));
        Open;
        if FieldDataType <> Fields[0].DataType then
        begin
          Messagedlg('对不起，你所用作连接条件的两字段数据类型不同，无法比较!',
            mtInformation, [mbOK], 0);
          Exit;
        end;
      end;
      //生成产生联合视图的语句
      //生成查询语句的投影部分
      ViewSqlString[0] := 'Create view ' + MyViewName + '(';
      viewsqlString[1] := 'AS Select ';
      ViewFieldNames.Clear;
      for i := 0 to ChecklistBox1.Items.Count - 1 do
        if CheckListBox1.Checked[i] then
        begin
          ViewFieldNames.Add(Trim(CheckListBox1.Items[i]));
          ViewSqlString[0] := ViewSqlString[0] + CheckListBox1.Items[i] + ',';
          viewSqlstring[1] := ViewSqlString[1] + GetTableEName(TableName) + '.'
            + FldNames[FldCnames.indexOf(trim(CheckListBox1.Items[i]))] + ',';
          MTFCount := MTFCount + 1;
        end;
      for i := 0 to ChecklistBox2.Items.Count - 1 do
        if CheckListBox2.Checked[i] then
        begin
          if viewfieldNames.IndexOf(Trim(CheckListBox2.Items[i])) <> -1 then
          begin
            ViewFieldNames.Add(GetTableEName(Trim(tablename2)) +
              Trim(CheckListBox2.Items[i]));
            ViewSqlString[0] := ViewSqlString[0] +
              GetTableEName(trim(tablename2)) + CheckListBox2.Items[i] + ',';
            viewSqlString[1] := viewSqlString[1] +
              GetTableEName(Trim(tablename2)) + '.' +
              FldNames2[FldCnames2.indexOf(trim(CheckListBox2.Items[i]))] + ',';
          end
          else
          begin
            ViewFieldNames.Add(Trim(CheckListBox2.Items[i]));
            ViewSqlString[0] := ViewSqlString[0] + CheckListBox2.Items[i] + ',';
            viewSqlstring[1] := ViewSqlString[1] +
              GetTableEName(Trim(tablename2)) + '.' +
              FldNames2[FldCnames2.indexOf(trim(CheckListBox2.Items[i]))] + ',';
          end;
          ETFCount := ETFCount + 1;
        end;
      ViewSqlString[0][length(viewsqlstring[0])] := ')';
      ViewSqlString[1][length(viewsqlstring[1])] := ' ';
      //生成查询语句的From语句
      viewsqlString[1] := viewSqlString[1] + 'From ' +
        GetTableEName(Trim(TableName)) + ' ';
      if trim(combobox4.Text) = '左连接' then
        viewSqlString[2] := 'LEFT JOIN ';
      if trim(combobox4.Text) = '右连接' then
        viewSqlString[2] := 'RIGHT JOIN ';
      if trim(combobox4.Text) = '内连接' then
        viewSqlString[2] := 'INNER JOIN ';
      if trim(combobox4.Text) = '全连接' then
        viewSqlString[2] := 'FULL JOIN ';
      viewSqlString[2] := viewSqlString[2] + GetTableEName(Trim(TableName2)) +
        ' ON ' + GetTableEName(trim(TableName)) + '.' +
        trim(FldNames[FldCNames.indexof(trim(combobox5.text))]);
      if trim(combobox3.Text) = '等于' then
        viewSqlstring[2] := viewSqlstring[2] + ' = ' +
          GetTableEName(trim(tablename2)) + '.' +
          trim(FldNames2[FldCNames2.indexof(trim(combobox6.text))]);
      if trim(combobox3.Text) = '不等于' then
        viewSqlstring[2] := viewSqlstring[2] + ' <> ' +
          GetTableEName(trim(tablename2)) + '.' +
          trim(FldNames2[FldCNames2.indexof(trim(combobox6.text))]);
      if trim(combobox3.Text) = '小于' then
        viewSqlstring[2] := viewSqlstring[2] + ' < ' +
          GetTableEName(trim(tablename2)) + '.' +
          trim(FldNames2[FldCNames2.indexof(trim(combobox6.text))]);
      if trim(combobox3.Text) = '大于' then
        viewSqlstring[2] := viewSqlstring[2] + ' > ' +
          GetTableEName(trim(tablename2)) + '.' +
          trim(FldNames2[FldCNames2.indexof(trim(combobox6.text))]);
      if trim(combobox3.Text) = '小于等于' then
        viewSqlstring[2] := viewSqlstring[2] + ' <= ' +
          GetTableEName(trim(tablename2)) + '.' +
          trim(FldNames2[FldCNames2.indexof(trim(combobox6.text))]);
      if trim(combobox3.Text) = '大于等于' then
        viewSqlstring[2] := viewSqlstring[2] + ' >= ' +
          GetTableEName(trim(tablename2)) + '.' +
          trim(FldNames2[FldCNames2.indexof(trim(combobox6.text))]);
    end;
  end;
  //以下处理的是连接条件由软件自动给出
  if checkbox1.checked then
  begin
    viewSqlstring[0] := 'CREATE VIEW ' + myViewName + '( ';
    viewSqlstring[1] := 'AS SELECT ';
    viewFieldNames.clear;
    if FldCnames.count > 1 then
      for i := 0 to FldCnames.Count - 2 do
      begin
        ViewFieldNames.add(trim(FldCnames[i]));
        ViewSqlString[0] := ViewSqlString[0] + trim(FldCnames[i]) + ',';
        viewSqlstring[1] := ViewSqlString[1] + tablename + '.' +
          trim(FldNames[i]) + ',';
        MTFCount := MTFCount + 1;
      end;
    ViewFieldNames.add(FldCnames[FldCnames.Count - 1]);
    ViewFieldNames.add('交换号');
    ViewSqlString[0] := ViewSqlString[0] + trim(FldCnames[FldCnames.Count - 1])
      +
      ',交换号' + ') ';
    viewSqlstring[1] := ViewSqlString[1] + tablename + '.' +
      trim(FldNames[FldCnames.Count - 1]) + ',Tyhjbxx.Jhh';
    ViewSqlstring[2] := ' From ' + TableName;
    viewSqlstring[3] := 'where ';
    //在这里填充软件自动给出的连接条件
  end;
  with GQ_FDataModule.ADOQuery1 do
  begin
    Close;
    Sql.Clear;
    for i := 0 to 5 do
      Sql.Add(viewsqlstring[i]);
    try
      execsql;
    except on E: exception do
        showmessage('sql 错了');
    end;
  end;
  TableName := myViewName;
  result := true;
  Fldnamebox.Items.Assign(viewFieldNames);
  selectList.Items.Assign(viewFieldNames);
  selectBox.Items.assign(viewFieldNames);
  OrderList.Items.Assign(viewFieldNames);
  GroupList.Items.Assign(viewFieldNames);
  groupbox.items.Clear;
  disablegrouping;
end;

procedure TGQ_FQueryMain.OperateBoxClick(Sender: TObject);
var
  InputDialog: TInputDialog;
  OrinputDialog: TOrinputDialog;
begin
  if OperateBox.ItemIndex = -1 then
    exit;
  FldValueBox.Enabled := true;
  FldValueBox.text := '';
  if trim(OperateBox.Text) = '从...到...之间' then
  begin
    InputDialog := TInputDialog.Create(Application);
    InputDialog.init(stringType);
    if InputDialog.ShowModal = mrcancel then
    begin
      OperateBox.ItemIndex := -1;
      InputDialog.Free;
      Exit;
    end;
    if stringType = date then
      FldValueBox.Text := ' between ' + '''' + trim(InputDialog.Edit1.Text) +
        '''' + ' and ' + '''' + trim(InputDialog.Edit2.Text) + ''''
    else
      FldValueBox.Text := ' between ' + trim(InputDialog.Edit1.Text) + ' and ' +
        trim(InputDialog.Edit2.Text);
    FldValueBox.Enabled := false;
    InputDialog.Free;
  end;
  if trim(OperateBox.text) = '在...中' then
  begin
    OrinputDialog := TOrinputDialog.Create(Application);
    OrinputDialog.init(StringType);
    if OrinputDialog.showmodal = mrcancel then
    begin
      OperateBox.ItemIndex := -1;
      OrinputDialog.Free;
      Exit;
    end;
    FldValueBox.Text := trim(OrinputDialog.edit1.Text);
    FldvalueBox.text := ' (' + copy(FldvalueBox.text, 1, length(FldvalueBox.text)
      - 1) + ') ';
    FldValueBox.Enabled := false;
    OrinputDialog.Free;
  end;
end;

procedure TGQ_FQueryMain.SpeedButton1Click(Sender: TObject);
var
  tempcount: integer;
begin
  if checklistbox1.Items.count <> 0 then
    for tempcount := 0 to Checklistbox1.Items.Count - 1 do
      checklistbox1.Checked[tempcount] := true;
end;

procedure TGQ_FQueryMain.SpeedButton2Click(Sender: TObject);
var
  tempcount: integer;
begin
  if checklistbox2.Items.count <> 0 then
    for tempcount := 0 to Checklistbox2.Items.Count - 1 do
      checklistbox2.Checked[tempcount] := true;
end;

procedure TGQ_FQueryMain.SpeedButton3Click(Sender: TObject);
var
  i: 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);
    try
      Open;
    except
      ShowMessage('当前查询的文件受到破旧，系统无法正常工作！');
      Exit;
    end;
    Application.CreateForm(Thavingform, havingform);
    HavingForm.CanGroupbox.Items.Clear;
    TempStringList := TStringList.Create;
    for i := 0 to FieldCount - 1 do
    begin
      case Fields[i].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[i].FieldName);
    end;
  end;
  HavingForm.InitHavingForm(True, Date);
  HavingForm.ss.Assign(TempStringList);
  if HavingForm.ShowModal = mrOK then
    GroupFieldBox.Items.Add('(' + HavingForm.ListBox3.Items[0] + ')');
  TempStringList.Free;
  havingform.Free;
end;

procedure TGQ_FQueryMain.SpeedButton4Click(Sender: TObject);
begin
  if groupfieldbox.ItemIndex = -1 then
  begin
    messagedlg('请你先选中你要删除的条件，好吗？', mtInformation, [mbOK], 0);
    exit
  end;
  if groupfieldbox.ItemIndex > 2 then
  begin
    messagedlg('请你每次删除一行，好吗？', mtInformation, [mbOK], 0);
    exit
  end;

  groupfieldbox.Items.Delete(groupfieldbox.itemIndex);
end;

procedure TGQ_FQueryMain.BitBtn2Click(Sender: TObject);
var
  thefirst: boolean;
  tempcount: integer;
  nowpos: integer;
begin
  thefirst := true;
  if Groupfieldbox.SelCount + fieldchoosebox.SelCount <> 2 then
  begin
    messagedlg('请在列表框中选择你需要的两行进行该操作' + #10#13 +
      '     好吗？', mtInformation, [mbOK], 0);
    exit;
  end;
  if groupfieldbox.SelCount = 2 then
    for tempcount := 0 to groupfieldbox.Items.Count - 1 do
      if groupfieldbox.Selected[tempcount] then
        if thefi