Water Off formula

This formula is used to return a dataset containing service requests and work orders to be displayed in the WaterOff grid in Call Center. It is very flexible in that you create the dataset using standard SQL and the data is displayed in the WaterOff grid where the column names match the grid columns IDs. Use the Content Manager to change the WaterOff grid if required.

dim doRadiusSearch as boolean = False 
Dim sqlStr As String 
Dim res As Hansen.Core.Result = Result.Success 
Try 
' Check if we do a radius search 
if 
not oAddress is Nothing 
then if 
oAddress.GPSXCoordinate > 1 and oAddress.GPSYCoordinate > 1 
then 
doRadiusSearch = True 
end if 
end if 
' Cast myObject to DataSet 
Dim myDataSet As System.Data.DataSet = CType(myObject, System.Data.DataSet) 
if 
doRadiusSearch 
then 
' SQL Server 
sqlStr = "select CUSTPROB.SERVNO as ""SRNumber"", CUSTPROB.SERVNO as ""colViewer"", '1' as ""gcWorkOrderKey"", 
ADDRESS.STNO + ' ' + ADDRESS.STNAME + ' ' + STATE + ' ' + ZIP as ""Address"", CUSTPROB.PRI as ""gcPriority"", 
PROBDEFN.PROBCODE as ""ProblemType"", PROBDEFN.PROBDESC as ""gcRequestTypeDesc"", 
CUSTPROB.PROBDTTM as ""gcInitialDate"", '0' as ""gcDistance"" 
FROM CRM.CUSTPROB CUSTPROB, CRM.PROBDEFN PROBDEFN, PROPERTY.ADDRESS ADDRESS 
Where CUSTPROB.ADDRKEY = ADDRESS.ADDRKEY AND CUSTPROB.PROB = PROBDEFN.PROBKEY 
AND PROBDEFN.PROBCODE = 'WATEROFF' AND ADDRESS.GPSX > " 
sqlStr = sqlStr + Convert.ToString(oAddress.GPSXCoordinate - searchRadius) 
sqlStr = sqlStr + " AND ADDRESS.GPSX < " sqlStr = sqlStr + 
Convert.ToString(oAddress.GPSXCoordinate + searchRadius) sqlStr = sqlStr + " 
AND ADDRESS.GPSY > " sqlStr = sqlStr + 
Convert.ToString(oAddress.GPSYCoordinate - searchRadius)
sqlStr = sqlStr + " AND ADDRESS.GPSY < " sqlStr = sqlStr + 
Convert.ToString(oAddress.GPSYCoordinate + searchRadius) 
else ' Get all, ie. no radius 
' SQLServer 
sqlStr = "select CUSTPROB.SERVNO as ""SRNumber"", CUSTPROB.SERVNO as ""colViewer"", '1' as ""gcWorkOrderKey"", 
ADDRESS.STNO + ' ' + ADDRESS.STNAME + ' ' + STATE + ' ' + ZIP as ""Address"", CUSTPROB.PRI as ""gcPriority"", 
PROBDEFN.PROBCODE as ""ProblemType"", PROBDEFN.PROBDESC as ""gcRequestTypeDesc"", 
CUSTPROB.PROBDTTM as ""gcInitialDate"", '0' as ""gcDistance"" 
FROM CRM.CUSTPROB CUSTPROB, CRM.PROBDEFN PROBDEFN, PROPERTY.ADDRESS ADDRESS 
Where CUSTPROB.ADDRKEY = ADDRESS.ADDRKEY AND CUSTPROB.PROB = PROBDEFN.PROBKEY 
AND PROBDEFN.PROBCODE = 'WATEROFF' " 
end if 
dim dSet As new System.Data.DataSet 
res = DataServices.ExecSQL(oServiceRequest.UserInfo.GetProvider(), sqlStr, nothing, dSet) 
if 
dSet.Tables.Count = 1 
then 
myDataSet.Tables.Add(dSet.Tables(0).Copy()) 
end if 
Catch ex As Exception 
res = new Result(0,ResultSeverity.Error,ex.Message,ex) 
End Try 
return res