This script can be used to change the SQL statement used for every query. A typical use of this is to add a WHERE clause 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.

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

Return Value
The updated SQL statement.

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 ;
}

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

See Also
Scripts