Send a filter string?

Comments

• rbuser

  February 2017 edited February 2017

  On 09/02/2017 05:52, Ian Branch wrote:
  
  Our app has rich end user reporting interface, including a report setup
  that allows for specifying a primary search criterion. When the report
  is run we check for this and then any form that supports printing passes
  the selection data to the report that is relevant, e.g. THIS invoice ID
  or these invoice IDs. It's all managed via a centralized reporting
  class. Here the key method:
  
  function TrbReporting.SetReportSQLFilter(aReport: TppReport;
  aReportDetails: TReportDetails; aFilterList: TStrings; aFilterInfo:
  TStrings = nil): TSearchCriteriaMode;
  var
  lSQLBuilder: TdaSQLBuilder;
  lCriterionIndex: Integer;
  ldaCriteria: TdaCriteria;
  lppParameter: TppParameter;
  begin
  
  Result := scmNone;
  
  // if this report is not linked to a database then exit
  if aReport.DataPipeline = nil then
  exit;
  
  lSQLBuilder := TdaSQLBuilder.Create(aReport.DataPipeline);
  
  lCriterionIndex :=
  lSQLBuilder.SearchCriteria.IndexOf(aReportDetails.FilterTable,
  aReportDetails.FilterField);
  
  try
  
  // if user has manually edited the SQL then we need to get out of here.
  if lSQLBuilder.SQL.EditSQLAsText then
  begin
  // user TppParameterListAccess cracker class to access
  GetParameterForName as the aReport.Parameters.Items[] property always
  returns a TppParameter, even a dummy one!
  lppParameter :=
  TppParameterListAccess(aReport.Parameters).GetParameterForName(aReportDetails.FilterField);
  
  if (assigned(lppParameter)) and (aFilterList.count > 0) then
  begin
  lppParameter.Value := aFilterList[0];
  aReport.ShowAutoSearchDialog := False;
  
  Result := scmParamater;
  end;
  
  exit;
  
  end;
  
  if lCriterionIndex > -1 then
  begin
  
  if
  lSQLBuilder.SearchCriteria.Items[lCriterionIndex]..operator<>dacoInList then
  raise EReportBuilderSearchError.Create('Primary search criterion
  should have an "IN LIST" operator');
  
  if aFilterList.count > 0 then
  lSQLBuilder.SearchCriteria.Items[lCriterionIndex].Value :=
  aFilterList.CommaText
  else
  lSQLBuilder.SearchCriteria.Items[lCriterionIndex].ShowAllValues
  := True;
  
  lSQLBuilder.ApplyUpdates;
  
  Result := scmDaCriteria;
  
  end
  else
  begin
  ldaCriteria :=
  lSQLBuilder.SearchCriteria.Add(aReportDetails.FilterTable,
  aReportDetails.FilterField, 'IN', aFilterList.CommaText);
  
  if assigned(ldaCriteria) then
  begin
  if aFilterList.count > 0 then
  ldaCriteria.Value := aFilterList.CommaText
  else
  ldaCriteria.ShowAllValues := True;
  
  Result := scmDaCriteria;
  end
  else
  begin
  MessageDlg('It was not possible to create search criterion for
  this report according to its Setup detail. Please check the Filter
  values.', mtWarning, [mbOK], 0);
  exit;
  end;
  
  lSQLBuilder.ApplyUpdates;
  end;
  
  finally
  if
  ApplicationDynamicSettings.CommandLineSwitchExists(ShowReportingSQLCode)
  then
  showmemoform(lSQLBuilder.SQL.SQLText.Text);
  
  lSQLBuilder.Free;
  end;
  
  if assigned(aFilterInfo) then
  begin
  if aReport.Parameters.InList(FiltersInfoParam) then
  aReport.Parameters.Items[FiltersInfoParam].AsString :=
  aFilterInfo.Text;
  
  end;
  
  end;