umasterdetailsearch.pas

企业进销存管理系统

  for iCount := 0 to high(LookUpPurposeFields) do
    FLookUpPurposeFields[iCount] := LookUpPurposeFields[iCount];

   SetLength(FLookUpVisibleFields,high(LookUpVisibleFields)+1);   //将要LookUp的资料表显示栏位储存
  for iCount := 0 to high(LookUpVisibleFields) do
    FLookUpVisibleFields[iCount] := LookUpVisibleFields[iCount];

  SetLength(FLookUpVisibleName,high(LookUpVisibleName)+1);   //将要LookUp的来源资料表栏位名称储存
  for iCount := 0 to high(LookUpVisibleName) do
    FLookUpVisibleName[iCount] := LookUpVisibleName[iCount];


  SetLength(FLookUpSourceFields,high(LookUpSourceFields)+1);   //将要LookUp的来源资料表栏位储存
  for iCount := 0 to high(LookUpSourceFields) do
    FLookUpSourceFields[iCount] := LookUpSourceFields[iCount];
//===========================================================================================



  SetLength(FSearchField,high(MSearchField)+1);   //将要搜寻的主资料表栏位储存
  for iCount := 0 to high(MsearchField) do
    FsearchField[iCount] := MSearchField[iCount];

  SetLength(FDetailsearchField,high(DSearchField)+1);  //将要搜寻的detail资料表栏位储存
  for iCount := 0 to high(DsearchField) do
    FDetailSearchField[iCount] := dSearchField[iCount];

  if High(iMDisField_List)<>(-1) then begin
    SetLength(FMaster_DisPlay_Field,High(iMDisField_List)+1);
    for iCount := low(iMDisField_List) to high(iMDisField_List) do
      FMaster_DisPlay_Field[iCount] := iMDisField_List[iCount];
  end;

  if High(iDDisField_List)<>(-1) then begin
    SetLength(FDetail_DisPlay_Field,High(iDDisField_List)+1);
    for iCount := low(iDDisField_List) to high(iDDisField_List) do
      FDetail_DisPlay_Field[iCount] := iDDisField_List[iCount];  //将要查询的栏位和要显示的栏位  储存在属性中
  end;



  FMFieldCount := high(MSearchField)+1;
  FDFieldCount := high(DsearchField)+1;

  Query1.DatabaseName := MDataSet.DatabaseName;

   //设定搜寻栏位的资讯(型别)
  InitialSearchField;

  if (High(iDDisField_List)<>(-1)) or (High(iMDisField_List)<>(-1)) then
    GetSQLEx //如果有取定要显示出来的那个栏位
  else
    GetSQL; //取得ＳＱＬ语法
  SetSearchGrid; //设定要寻找的GRID环境
  showmodal;
end;

procedure TMasterDetailSearch.InitialSearchField;
var
  iCount: integer;
  xx : integer;
  xxVis,xxName : string;
  xRec : integer;
begin
  xx := 0;
//===================write by JEFF=====================
  for iCount := low(FLookUpData) to high(FLookUpData) do
      xx := xx +SubStrCnt(',',FLookupVisibleFields[iCount])+1;

//=====================================================



  SetLength(ffieldname,FMFieldCount+FDFieldCount+xx);
  SetLength(FFieldType,FMFieldCount+FDFieldCount+xx);
  SetLength(FFieldDisplayname,FMFieldCount+FDFieldCount+xx);

  for iCount := 0 to FMFieldCount-1 do begin
    FFieldName[iCount] :='M.'+ TTable(sDataSet).Fields[fsearchfield[iCount]].FieldName;
    FFieldDisplayName[iCount] := TTable(sDataSet).Fields[fsearchfield[iCount]].DisplayName;
    case TTable(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;

  for iCount := 0 to FDFieldCount-1 do begin
    FFieldName[iCount+FMFieldCount] :='D.'+ TTable(FDetailDataSet).Fields[fDetailsearchfield[iCount]].FieldName;
    FFieldDisplayName[iCount+FMFieldCount] := TTable(FDetailDataSet).Fields[fDetailsearchfield[iCount]].DisplayName;
    case TTable(FDetailDataSet).Fields[fDetailsearchfield[iCount]].DataType  of
      ftdatetime :          FFieldType[iCount+FMFieldCount] := 'D';
      ftString:         FFieldType[ICount+FMFieldCount] := 'S';
      ftInteger,ftFloat:FFieldType[ICount+FMFieldCount] := 'I';
      else
        FFieldType[iCount+FMFieldCount] := 'N';
    end;//end of case
  end;
//=======================write by JEFF ===========================
  xRec := FDFieldCount+FMFieldCount;
  for iCount := low(FLookUpData) to high(FLookUpData) do begin
    xxVis := FLookupVisibleFields[icount];
    xxName := FLookupVisibleName[icount];           //加入相应连接表的栏位，如基本资料的栏位
    while pos(',',xxVis) <>0 do begin

      FFieldName[xRec] :=FLookUpData[icount]+'.'+ copy(xxVis,1,pos(',',xxVis)-1);
      FFieldDisplayName[xRec] := copy(xxName,1,pos(',',xxName)-1);
      Delete(xxVis,1,pos(',',xxVis));
      Delete(xxName,1,pos(',',xxName));
      FFieldType[xRec] := 'S';
      xRec := xRec +1 ;
    end;

    FFieldName[xRec] :=FLookUpData[icount]+'.'+ xxVis;
    FFieldDisplayName[xRec] := xxName;
    FFieldType[xRec] := 'S';
    xRec := xRec +1 ;
  end;
//================================================================
end;

//取回 SQL语句中From的字串
function TMasterDetailSearch.Get_From_Sentence: string;   //JEFF： 得到SQL语句中的 From 段语句；
var
  iCount: integer;
  LM,LD: TList;
  sFrom: string;
  xxVis : string;
  xxName : string;

begin
  FKeyValue := TstringList.Create;//储存要将资料取回的栏位名称(主键)
  LM := TList.Create;
  LD := TList.Create;
  try
    TTable(FDetailDataSet).GetDetailLinkFields(lm,ld); // JEFF：得到主档，明细档关联栏位
//===============================write by JEFF===============================
    sFrom := ' FROM '+TTable(sDataSet).TableName+' M left join '+TTable(FDetailDataSet).tablename+' D on ';
    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;
    delete(sFrom,length(sFrom)-2,3);

    for iCount := low(FLookUpData) to high(FLookUpData) do begin
       sFrom := sFrom + ' left join ' + FLookUpData[iCount] +' on ' ;
       xxVis := FLookUpPurposeFields[iCount];
       xxName := FLookUpSourceFields[iCount];
       while pos(',',xxVis) <>0 do begin
         sFrom := sFrom +' ' + FLookUpData[iCount] +'.'+copy(xxVis,1,pos(',',xxVis)-1)+ '='+ copy(xxName,1,pos(',',xxName)-1)+' and';
         Delete(xxVis,1,pos(',',xxVis));
         Delete(xxName,1,pos(',',xxName));
       end;

       sFrom := sFrom+' ' + FLookUpData[iCount] +'.'+xxVis+ '='+xxName ;

    end;

//=============================================================================
   // 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 +' where '+FOtherWhere;//+' AND';

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

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

//当使用者有设定要显示出那些栏位时,
procedure TMasterDetailSearch.GetSQLEx;
var
  iCount: integer;
  sSelect: string;
  xxVis : string;
  xxName : 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+', ';
  //========================write by JEFF==============================

  for iCount := low(FLookUpData) to high(FLookUpData) do begin
      xxVis := FLookupVisibleFields[iCount];
      xxName := FLookupVisibleName[iCount];
      while pos(',',xxVis) <>0 do begin
         sSelect  :=sSelect+ FLookUpData[iCount] +'.'+copy(xxVis,1,pos(',',xxVis)-1) +' as '+ copy(xxName,1,pos(',',xxName)-1)+', ';
         Delete(xxVis,1,pos(',',xxVis));
         Delete(xxName,1,pos(',',xxName));
      end;
      sSelect :=sSelect+ FLookUpData[iCount] +'.'+xxVis +' as '+ xxName+', ';

  end;

//===================================================================
  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 TMasterDetailSearch.GetSQL;
var
  iCount: integer;
  iCountRepeat: integer;
  iRepeatTime: integer;
  sSelect: string;
  xxVis : string;
  xxName : 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;

  //========================write by JEFF==============================

  for iCount := low(FLookUpData) to high(FLookUpData) do begin
      xxVis := FLookupVisibleFields[iCount];
      xxName := FLookupVisibleName[iCount];
      while pos(',',xxVis) <>0 do begin
         sSelect  :=sSelect+ FLookUpData[iCount] +'.'+copy(xxVis,1,pos(',',xxVis)-1) +' as '+ copy(xxName,1,pos(',',xxName)-1)+', ';
         Delete(xxVis,1,pos(',',xxVis));
         Delete(xxName,1,pos(',',xxName));
      end;
      sSelect :=sSelect+ FLookUpData[iCount] +'.'+xxVis +' as '+ xxName+', ';

  end;

//===================================================================


  trim(sSelect);