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.
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.
You can access the running report object through the global variable Report.
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 ;
}
C#
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
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
C#
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
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
See also
DataEngine.BeforeSendSQLStatementToDatabase | Scripts© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic