uform_searchdata.pas

企业进销存管理系统

    while sOldSQL[iPOS] <>' ' do
      inc(iPos);
    inc(iPos);

    if i2Pos=0 then
      sFrom := 'FROM '+copy(sOldSQL,iPos,length(sOldSQL)-iPos+1)
    else
      sFrom := 'FROM '+copy(sOldSQL,iPos,i2Pos-iPos);

    //产生query的Select语法
    for iCount := 0 to FFieldCount-1 do begin
      sSelect := sSelect + TQuery(sDataSet).Fields[fsearchfield[iCount]].Origin +' as '+TQuery(sDataSet).Fields[fsearchfield[iCount]].DisplayName+', ';
      FFieldName[iCount] := TQuery(sDataSet).Fields[fsearchfield[iCount]].Origin;
      FFieldDisplayName[iCount] := TQuery(sDataSet).Fields[fsearchfield[iCount]].DisplayName;
      case TQuery(sDataSet).Fields[fsearchfield[iCount]].DataType  of
        ftdatetime :          FFieldType[iCount] := 'D';
        ftString:         FFieldType[ICount] := 'S';
        ftInteger,ftFloat:FFieldType[ICount] := 'I';
        else
          FFieldType[iCount] := 'N';
      end;//end of case
    end;
  end;//end of if ... else

  trim(sSelect);
  Delete(sSelect,length(sSelect)-1,1);

  FSql := sSelect + sFrom;

  {AssignFile(output, 'f:\aaa.sql');
  rewrite(output);
  writeln(output,fsql);
  closefile(output);}
end;

procedure TForm_SearchData.SetSearchGrid;
var
  iCount: integer;
begin
  //设定tsGrid的环境
  with tsGrid1 do begin
    tsGrid1.Rows := FFieldCount;

    for iCount := 0 to FFieldCount-1 do begin
     // tsGrid1.Cell[iISPrint_Cell,iCount+1] := False;
      tsGrid1.Cell[iBool_Cell,iCount+1] := '并且';
      tsGrid1.Cell[isearchKind,iCount+1] := '=';
      tsGrid1.Cell[isearchField,iCount+1] := FFieldDisplayName[iCount]; //指定搜寻栏位的名称
    end;
    tsGrid1.cell[1,1] := '';//第一个搜寻条件值不需要有搜寻条件
   // tsGrid1.cell[2,1] := '';//第一个搜寻条件值不需要有搜寻条件
    tsGrid1.Col[isearchField].ReadOnly := true;
    tsGrid1.CurrentCell.MoveTo(isearchValue,1);//将游标移至第第一个要输入搜寻条件值的地方
  end;//end of with
end;

procedure TForm_SearchData.tsGrid1ButtonClick(Sender: TObject; DataCol,
  DataRow: Integer);
begin
  //当输入栏位值是日期型别时,该使用者可以用选择的方式
  if DataCol <> isearchvalue then
    exit;

  if FFieldType[DataRow-1]='D' then
   with TCalendarDlg.Create(nil) do
      try
        if ShowModal=mrok then
          tsGrid1.Cell[DataCol,DataRow] := FormatDateTime('yyyy/mm/dd',Calendar1.CalendarDate);//copy(inttostr(Calendar1.Month+101),2,2)+'/' +copy(inttostr(Calendar1.Day+101),2,2)+'/'+inttostr(Calendar1.Year);
      finally
        free;
      end;
end;

procedure TForm_SearchData.StartSearch(Sender: TObject);
var
  sWhere: string;

  function GetEValue(s: string):string;//取得判断式
  begin
    if s='并且' then
      result := 'AND'
    else if s= '或' then
      Result := 'OR'
    else
      Result := '';
    if sWhere = '' then
      Result := '';
  end;

  function GetSearchKind(i: integer): string;//取得要搜寻的条件值
  var
    sKind: string;
    sSearchValue: string;
  begin
    sKind := tsGrid1.Cell[isearchKind,i+1];
    sSearchValue := tsGrid1.Cell[iSearchValue,i+1];
    case FFieldType[i] of
       'S':
          if sKind = 'LIKE%' then
            Result := ' '+copy(sKind,1,4) + ' "'+sSearchValue+'%") '
          else if sKind = '%LIKE' then
            Result := ' '+copy(sKind,2,4) + ' "%'+sSearchValue+'") '
          else if sKind = '%LIKE%' then
            Result := ' '+copy(sKind,2,4) + ' "%'+sSearchValue+'%") '
          else
            Result := ' '+sKind + ' "'+sSearchValue+'") ';
      'I': Result :=  ' '+sKind + sSearchValue+') ';
      'D','N':
          Result := ' '+sKind + ' "'+sSearchValue+'") ';
    end;
  end;

  procedure GetSWhere;//取得要搜寻的条件值
  var
    i: integer;
  begin
    for i := 0 to FFieldCount-1 do
      if vartostr(tsGrid1.Cell[iSearchValue,i+1])<>'' then
        sWhere := sWhere+GetEvalue(tsGrid1.Cell[iBool_Cell,i+1])+' ( '+ FFieldname[i] +GetSearchKind(i);
  end;

begin
  //开始搜寻资料
  Query1.Close;
  Query1.SQL.Clear;
  Animate1.Active := True;
  try
    GetSWhere;//取得要搜寻的条件值
    if sWhere = '' then begin
      MessageBoxEx(application.handle,'请输入查询条件','错误',MB_OK,SUBLANG_CHINESE_TRADITIONAL);
      exit;
    end;
    if pos('WHERE',UpperCase(FSql)) <> 0 then
      Query1.SQL.Add(FSql + ' AND ( '+sWhere+' )')
    else
      Query1.SQL.Add(FSql + ' WHERE '+sWhere);
    {for Test}
    {AssignFile(output, 'f:\aaa.sql');
    rewrite(output);
    writeln(output,Query1.SQL.Text);
    closefile(output); }
    {end of Test}
    Query1.open;
    if Query1.IsEmpty then begin
      MessageBoxEx(application.handle,'找不到您要的资料,请重新输入查询条件','错误',MB_OK,SUBLANG_CHINESE_TRADITIONAL);
      tsGrid1.SetFocus;
      tsGrid1.CurrentCell.MoveTo(iSearchvalue,1);
      StatusBar1.SimpleText := '共找到 0 个资料';
    end else
      StatusBar1.SimpleText := '共找到 '+inttostr(query1.recordcount)+' 个资料';

  finally
    Animate1.Stop;
  end;
end;

procedure TForm_SearchData.tsGrid1ComboGetValue(Sender: TObject;
  Combo: TtsComboGrid; GridDataCol, GridDataRow, ComboDataRow: Integer;
  var Value: Variant);
begin
  //当使用者选择了第一个搜寻条件值的搜寻条件时,不能让使用者改变
  if (GridDataCol= ibool_Cell) and (GridDataRow=1) then begin
    MessageBoxEx(application.handle,'您不能设定这个条件值','错误',MB_OK,SUBLANG_CHINESE_TRADITIONAL);
    value := '';
  end;
end;

procedure TForm_SearchData.tsGrid1ComboDropDown(Sender: TObject;
  Combo: TtsComboGrid; DataCol, DataRow: Integer);
begin
  //指定搜寻的条件
  if tsgrid1.CurrentDataCol <> iSearchKind then
    exit;
  case FFieldType[tsgrid1.CurrentDataRow-1] of
    'S':begin
         Combo.DropDownRows := 4;
          Combo.Rows := 4;
          Combo.Cell[1,1] := '=';
          Combo.Cell[1,2] := 'LIKE%';
          Combo.Cell[1,3] := '%LIKE';
          Combo.Cell[1,4] := '%LIKE%';
        end;
    else
      begin
        Combo.DropDownRows := 6;
        combo.Rows := 6;
        Combo.Cell[1,1] := '=';
        Combo.Cell[1,2] := '>';
        Combo.Cell[1,3] := '>=';
        Combo.Cell[1,4] := '<';
        Combo.Cell[1,5] := '<=';
        Combo.Cell[1,6] := '<>';
      end;//
  end;//end of case

end;

procedure TForm_SearchData.BitBtn3Click(Sender: TObject);
begin
  Close;
end;

procedure TForm_SearchData.BitBtn2Click(Sender: TObject);
var
  sParimayKey: string;
  icount: Integer;
  vFieldArray: variant;
begin
  {执行取回的动作}
  if query1.IsEmpty then begin
    if sender = nil then
      exit;
    MessageBoxEx(application.handle,'无资料可供取回','错误',MB_OK,SUBLANG_CHINESE_TRADITIONAL);
    exit;
  end;

  sParimayKey := '';
  if fkeyField > 1 then
    VFieldArray := VarArrayCreate([0,FKeyField-1],varolestr);


  for iCount := 0 to FKeyField-1 do begin //取得要搜寻的键值
    if sDataSet is TTable then
      sParimayKey := sParimaykey + FFieldName[iCount]+';'
    else
      sParimayKey := sParimaykey + sDataSet.Fields[FSearchField[iCount]].FieldName+';';
    if fkeyField>1 then
      vFieldArray[iCount] := Query1.Fields[icount].Value
    else
      vFieldArray := Query1.Fields[icount].Value;
  end;

  Delete(sParimayKey,length(sParimayKey),1);

  sDataSet.Locate(sParimayKey,vFieldArray,[]);
  close;
end;

procedure TForm_SearchData.tsDBGrid1DblClick(Sender: TObject);
begin
  BitBtn2Click(nil);
end;

procedure TForm_SearchData.Query1AfterOpen(DataSet: TDataSet);
begin
  BitBtn2.Enabled := not query1.IsEmpty;
  (* add code*)
  BitBtn4.Enabled := not query1.IsEmpty;
  BitBtn5.Enabled := not query1.IsEmpty;
  (**)
end;

procedure TForm_SearchData.tsGrid1CellEdit(Sender: TObject; DataCol,
  DataRow: Integer; ByUser: Boolean);
begin
  BitBtn2.Enabled := False;
  (**)
  BitBtn4.Enabled := False;
  BitBtn5.Enabled := False;
  (**)
  if Query1.Active then
    Query1.Close;
end;

procedure TForm_SearchData.BitBtn4Click(Sender: TObject);
begin
  GetSelRang;
  SearchRepExc(Query1,FSelR,FSelRang);
end;

procedure TForm_SearchData.BitBtn5Click(Sender: TObject);
begin
  GetSelRang;
  ExcelPrint(Query1,'Excel.Application','资料表',FSelR,FSelRang);
end;

end.