umasterdetailsearchnolook.pas

企业进销存管理系统

  try
    TTable(FDetailDataSet).GetDetailLinkFields(lm,ld); // JEFF：得到主档，明细档关联栏位

    sFrom := ' FROM '+TTable(sDataSet).TableName+' M, '+TTable(FDetailDataSet).tablename+' D WHERE ';
    for iCount:= 0 to LM.Count-1 do begin         //JEFF ：用SQL将两个资料表关联上，关联的栏位用TTable(FDetailDataSet).GetDetailLinkFields(lm,ld)得到；
      sFrom := sFrom +' M.'+TField(Lm[iCount]).FieldName+' = D.'+TField(LD[iCount]).FieldName+' AND';
      FKeyValue.Add(TField(Lm[iCount]).FieldName);
    end;
    if FOtherWhere <>'' then
      sFrom := sFrom +' '+FOtherWhere+'AND';

    delete(sFrom,length(sFrom)-2,3);

  finally
    Lm.Free;
    LD.Free;
  end;
  result := sFrom;
end;

//当使用者有设定要显示出那些栏位时,
procedure TMasterDetailSearchNoLook.GetSQLEx;
var
  iCount: integer;
  sSelect: string;
begin
  sSelect := 'SELECT ';

  //产生 select 的语法
  for iCount := Low(FMaster_DisPlay_Field) to high(FMaster_DisPlay_Field) do
    sSelect := sSelect+' M.'+TTable(sDataSet).Fields[FMaster_DisPlay_Field[iCount]].FieldName+' as '+TTable(sDataSet).Fields[FMaster_DisPlay_Field[iCount]].DisplayName+', ';

  for iCount := low(FDetail_DisPlay_Field) to high(FDetail_DisPlay_Field) do
      sSelect := sSelect+' D.'+TTable(FDetailDataSet).Fields[FDetail_DisPlay_Field[iCount]].FieldName+' as '+TTable(FDetailDataSet).Fields[FDetail_DisPlay_Field[iCount]].DisplayName+', ';

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

  FSql := sSelect + Get_From_Sentence;

  {$ifdef TESt}
  {AssignFile(output, testSQLoutput+'aaa1.sql');
  rewrite(output);
  writeln(output,fsql);
  closefile(output);}
  {$Endif} 
end;

//当使用者无指定要显示的是那些栏位,则以搜寻的栏位为显示的栏位
procedure TMasterDetailSearchNoLook.GetSQL;
var
  iCount: integer;
  iCountRepeat: integer;
  iRepeatTime: integer;
  sSelect: string;
begin
  sSelect := 'SELECT ';

  //产生 select 的语法
  iRepeatTime := 0;
  for iCount := 0 to FMFieldCount-1 do begin
    for iCountRepeat := 0 to iCount-1 do
      if fsearchfield[iCount] = fsearchfield[iCountRepeat] then //JEFF：如选择销货单日期范围
        inc(iRepeatTime);
    if iRepeatTime = 0 then//无选择过,因有时候我们会选择二个相同的栏位来做比较,如日期栏位,但我们选择出来的栏位只需要显示一个就好了
      sSelect := sSelect+' M.'+TTable(sDataSet).Fields[fsearchfield[iCount]].FieldName+' as '+TTable(sDataSet).Fields[fsearchfield[iCount]].DisplayName+', ';
    iRepeatTime := 0;
  end;

  iRepeatTime := 0;
  for iCount := 0 to FDFieldCount-1 do begin
    for iCountRepeat := 0 to iCount-1 do
      if fDetailsearchfield[iCount] = fDetailsearchfield[iCountRepeat] then
        inc(iRepeatTime);
    if iRepeatTime = 0 then//无选择过,因有时候我们会选择二个相同的栏位来做比较,如日期栏位,但我们选择出来的栏位只需要显示一个就好了
      sSelect := sSelect+' D.'+TTable(FDetailDataSet).Fields[fDetailsearchfield[iCount]].FieldName+' as '+TTable(FDetailDataSet).Fields[fDetailsearchfield[iCount]].DisplayName+', ';
    iRepeatTime := 0;
  end;

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

  FSql := sSelect + Get_From_Sentence;
  {$ifdef TEST}
  {AssignFile(output, TestSQLOutput+'aaa2.sql');
  rewrite(output);
  writeln(output,fsql);
  closefile(output); }
  {$endif}
end;

procedure TMasterDetailSearchNoLook.SetSearchGrid;
var
  iCount: integer;
  iRows: integer;
begin
  //设定tsGrid的环境
  iRows := FMFieldCount+FDFieldCount;
  with tsGrid1 do begin
    tsGrid1.Rows := iRows;

    for iCount := 0 to iRows-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 TMasterDetailSearchNoLook.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 TMasterDetailSearchNoLook.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 (FMFieldCount+FMFieldCount)-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+' )')
    // Query1.SQL.Add(FSql + ' AND  '+sWhere+' ')
    else
      Query1.SQL.Add(FSql + ' WHERE '+sWhere);
    {$ifdef TEST}
    {AssignFile(output,  TestSQLOutput+'SQL.sql');
    rewrite(output);
    writeln(output,Query1.SQL.Text);
    closefile(output);}
    {$endif}
    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 TMasterDetailSearchNoLook.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 TMasterDetailSearchNoLook.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 TMasterDetailSearchNoLook.BitBtn3Click(Sender: TObject);
begin
  Close;
end;

procedure TMasterDetailSearchNoLook.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;

  if FKeyValue.Count > 1 then begin  //JEFF注:FKeyValue 是储存主档索引栏位
    VFieldArray := VarArrayCreate([0,FKeyValue.Count-1],varolestr); // 将VFieldArray 建成变量数组，varolestr是一种类型；
    for iCount := 0 to FKeyValue.Count-1 do begin    //给vFieldArray 中的每个原素赋值
      vFieldArray[iCount] := Query1.Fieldbyname(sDataSet.Fieldbyname(FKeyValue[iCount]).DisplayName).value;
      sParimayKey := sParimaykey + FKeyValue[iCount]+';'
    end;
    Delete(sParimayKey,length(sParimayKey),1);
    sDataSet.Locate(sParimayKey,vFieldArray,[]);
  end
  else
  begin
    sParimayKey := FKeyValue[0];      //JEFF注: 通过主档键值进行查找 
    vFieldArray := Query1.Fieldbyname(sDataSet.Fieldbyname(FKeyValue[0]).DisplayName).value;
    sDataSet.Locate(sParimayKey,vFieldArray,[]);
  end;
  close;
end;

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

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

procedure TMasterDetailSearchNoLook.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 TMasterDetailSearchNoLook.BitBtn4Click(Sender: TObject);
begin
  GetSelRang;
  SearchRepExc(Query1,FSelR,FSelRang);
end;

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

procedure TMasterDetailSearchNoLook.FormDestroy(Sender: TObject);
begin
  FKeyValue.Free;
end;

procedure TMasterDetailSearchNoLook.FormCreate(Sender: TObject);
begin
  FormResizer1.InitializeForm;
end;

procedure TMasterDetailSearchNoLook.FormResize(Sender: TObject);
begin
  FormResizer1.ResizeAll;
end;

end.