DataEngine.FinalizeSQLStatement

This script can be used to change the SQL statement used for every query. A typical use of this is to add a filter condition to each query so the user can only see records they are allowed to.

For example, suppose you have a sales processing system and don't want salespeople to see any sales records that aren't their own. Every table in the database has a SALESPERSONID field that matches the ID for the salesperson. Regardless of any filter the user creates for a report, you want an automatic filter on SALESPERSONID applied. The DataEngine.FinalizeSQLStatement can do that because it allows you to change the SQL statement.

Rather than adding a WHERE clause to the SQL statement, add conditions to the FilterConditions collection. If you manually add or update the WHERE clause, Stonefield Query treats this as a custom SQL statement, which is processed differently than one Stonefield Query creates.

Return a blank string from this script if you don't want Stonefield Query to continue running the report, such as if there's a problem.

Note that if you want to alter the SQL statement based on the current datasource (for example, adding a WHERE clause using something from current data source) when doing a multi-datasource query, use a DataEngine.BeforeSendSQLStatementToDatabase script instead.

Parameters
A reference to the Stonefield Query Application object and the current SQL statement used for the report.

Return Value
The updated SQL statement or a blank string to not continue.

Example
Here's code that implements the solution for the example discussed above. This assumes the ID for the salesperson is stored in a Registry setting for the application.

Visual FoxPro

lparameters toApplication as SQApplication, tcSelect
local lcSalespersonID, lcTable, lcSelect
lcSalespersonID = toApplication.GetRegistryValue('ID', '', ;
  'Software\MyCompany\SalesApplication')
lcTable = toApplication.DataEngine.GetTableFromSQLStatement(tcSelect)
lcSelect = toApplication.DataEngine.AddToWhere(tcSelect, ;
  lcTable + ".SalespersonID = '" + lcSalespersonID + "'")
return lcSelect

VBScript

function Main(Application, SelectStatement)
SalespersonID = Application.GetRegistryValue("ID", "", _
  "Software\MyCompany\SalesApplication")
Table = Application.DataEngine.GetTableFromSQLStatement(SelectStatement)
Main = Application.DataEngine.AddToWhere(SelectStatement, _
  Table + ".SalespersonID = " + chr(34) + SalespersonID + chr(34))
end function

JavaScript

function Main(Application, SelectStatement) {
var SalespersonID, Table, ReturnValue ;
SalespersonID = Application.GetRegistryValue('ID', ' ', 
  'Software\MyCompany\SalesApplication') ;
Table = Application.DataEngine.GetTableFromSQLStatement(SelectStatement) ;
ReturnValue = Application.DataEngine.AddToWhere(SelectStatement, 
  Table + '.SalespersonID = "' + SalespersonID + '"') ;
return ReturnValue ;
}

Please note that the method in this script must be named DataEngine_FinalizeSQLStatement.

public static object DataEngine_FinalizeSQLStatement(SFQApplication sfqApplication,
  string selectStatement)
{	
  string salespersonID, table, returnValue;

  salespersonID = sfqApplication.GetRegistryValue("ID", " ", 
    @"HKEY_CURRENT_USER\Software\MyCompany\SalesApplication");
    
  table = sfqApplication.DataEngine.GetTableFromSQLStatement(selectStatement);
  returnValue = sfqApplication.DataEngine.AddToWhere(selectStatement, table + 
    ".SalespersonID = '" + salespersonID + "'");

  return returnValue;
}

VB.NET

Please note that the method in this script must be named DataEngine_FinalizeSQLStatement.

public shared function DataEngine_FinalizeSQLStatement(sfqApplication as SFQApplication, _
  selectStatement as string) as object

  Dim salespersonID, table, returnValue As String

  salespersonID = sfqApplication.GetRegistryValue("ID", " ", _
    "HKEY_CURRENT_USER\Software\MyCompany\SalesApplication")
    
  table = sfqApplication.DataEngine.GetTableFromSQLStatement(selectStatement)
  returnValue = sfqApplication.DataEngine.AddToWhere(selectStatement, table + _
    ".SalespersonID = '" + salespersonID + "'")

  return returnValue

End Function

Example
Suppose you want to add a filter condition to all queries: if the user filters on the start date being greater than or equal to a certain date, you also want to filter on the end date being less than or equal to that date without forcing the user to create that condition manually.

Visual FoxPro

lparameters toApplication as SQApplication, tcSelect
local lnI, loCondition as FilterCondition, llHaveStart, ldStartDate, ;
  llHaveEnd, lcSelect
for lnI = 1 to toApplication.DataEngine.FilterConditions.Count
  loCondition = toApplication.DataEngine.FilterConditions.Item(lnI)
  if upper(loCondition.FieldName) = 'SERVICE.START_DATE'
    llHaveStart = .T.
    ldStartDate = loCondition.Values.Item(1)
  endif
  llHaveEnd = upper(loCondition.FieldName) = 'SERVICE.END_DATE'
next
lcSelect = tcSelect
if llHaveStart and not llHaveEnd

* Use this for VFP data

  lcSelect = toApplication.DataEngine.AddToWhere(lcSelect, ;
    'SERVICE.END_DATE >= {^' + ;
    transform(dtos(ldStartDate), '@R 9999-99-99') + '}')

* Use this for ODBC data

  lcSelect = toApplication.DataEngine.AddToWhere(lcSelect, ;
    "SERVICE.END_DATE >= {ts '" + ;
    transform(dtos(ldStartDate), '@R 9999-99-99') + " 00:00:00'}")
endif
return lcSelect

Please note that the method in this script must be named DataEngine_FinalizeSQLStatement.

public static string DataEngine_FinalizeSQLStatement(SFQApplication sfqApplication,
  string selectStatement)
{	
  bool haveStart = false;
  bool haveEnd = false;
  string newSelect = selectStatement;
  DateTime startDate = DateTime.Today;

  foreach(FilterCondition filter in sfqApplication.DataEngine.FilterConditions)
  {
    if(filter.FieldName.ToUpper() == "ORDERS.ORDERDATE")
    {
      haveStart = true;
      startDate = (DateTime)filter.Values.Item(0).Value;
    }
    haveEnd = (filter.FieldName.ToUpper() == "ORDERS.SHIPPEDDATE");
  }

  if(haveStart && !haveEnd)
  {
    string dateString = String.Format("{0}-{1}-{2}", startDate.Year.ToString(), 
      startDate.Month.ToString().PadLeft(2, '0'), startDate.Day.ToString().PadLeft(2, '0'));

    newSelect = sfqApplication.DataEngine.AddToWhere(selectStatement, 
      "ORDERS.SHIPPEDDATE >= {ts '" + dateString + " 00:00:00'}");
  }

  return newSelect;
}

VB.NET

Please note that the method in this script must be named DataEngine_FinalizeSQLStatement.

public shared function DataEngine_FinalizeSQLStatement(sfqApplication as SFQApplication, _
  selectStatement as string) as string

  Dim haveStart as Boolean = false
  Dim haveEnd as Boolean = false
  Dim newSelect As String = selectStatement
  Dim startDate As DateTime = DateTime.Today

  For Each filter As FilterCondition In sfqApplication.DataEngine.FilterConditions
    if filter.FieldName.ToUpper() = "ORDERS.ORDERDATE" Then
      haveStart = true
      startDate = filter.Values.Item(0).Value
    End If
    haveEnd = (filter.FieldName.ToUpper() = "ORDERS.SHIPPEDDATE")
  Next
    
  if haveStart And Not haveEnd Then
    Dim dateString As String = String.Format("{0}-{1}-{2}", _
      startDate.Year.ToString(), startDate.Month.ToString().PadLeft(2, "0"), _
      startDate.Day.ToString().PadLeft(2, "0"))
        
    newSelect = sfqApplication.DataEngine.AddToWhere(selectStatement, _
      "ORDERS.SHIPPEDDATE >= {ts '" + dateString + " 00:00:00'}")
  End If

  return newSelect
End Function