Public Sub BuildQuery (WithPageLimit As Boolean) As Object()
Dim c As Int
Dim n As Int
Dim SortColumn As String
Dim strFind As String
Dim sb As StringBuilder
sb.Initialize
sb.Append("SELECT rowid")
For Each col As B4XTableColumn In VisibleColumns
If col.ColumnType = COLUMN_TYPE_VOID Then Continue
sb.Append(",").Append(col.SQLID)
If col.InternalSortMode <> "" Then
SortColumn = " ORDER BY " & col.SQLID & " " & col.InternalSortMode
End If
Next
sb.Append(" FROM ").Append(SQLTableName).Append(" ")
Dim args As List
args.Initialize
Dim TotalCount As Int
If iSearchConditions = 0 Then
TotalCount = CountAll
mCurrentCount = CountAll
miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)
Else
For Each col As B4XTableColumn In VisibleColumns
If col.Searchable Then
For n = 0 To arrCheckConditions(c) - 1
strFind = arrConditions(c, n).strSearchText
Log("BuildQuery, strFind: " & strFind)
If args.Size = 0 Then
sb.Append(" WHERE ")
Else
If arrConditions(c, n).bSQL_OR Then
'this used to be all OR
sb.Append(" OR ")
Else
sb.Append(" AND ")
End If
End If
If col.ColumnType = COLUMN_TYPE_INTEGER Or col.ColumnType = COLUMN_TYPE_DOUBLE Then
If arrConditions(c, n).strOperator.Length = 0 Then
sb.Append(col.SQLID).Append(" = ? ")
Else
sb.Append(col.SQLID).Append(" ").Append(arrConditions(c, n).strOperator).Append(" ? ")
End If
args.Add(strFind)
Else
If arrConditions(c, n).strOperator.StartsWith("length") Then
'this doesn't work for some reason as it always produces zero for the length
Select Case arrConditions(c, n).strOperator
Case "length ="
sb.Append("length(").Append(col.SQLID).Append(") = ? ")
Case "length >"
sb.Append("length(").Append(col.SQLID).Append(") > ? ")
Case "length <"
sb.Append("length(").Append(col.SQLID).Append(") < ? ")
Case "length <>"
sb.Append("length(").Append(col.SQLID).Append(") <> ? ")
End Select
args.Add(strFind) 'adding the length to look at
Else
If arrConditions(c,n).bWildCard Then
If arrConditions(c,n).bCaseSensitive Then
sb.Append(col.SQLID).Append(" GlOB ? ")
If arrConditions(c,n).bPrefixSearch Then
args.Add(strFind & "*")
Else
args.Add("*" & strFind & "*")
End If
Else
sb.Append(col.SQLID).Append(" LIKE ? ")
If arrConditions(c,n).bPrefixSearch Then
args.Add(strFind & "%")
Else
args.Add("%" & strFind & "%")
End If
End If
Else
If arrConditions(c, n).bCaseSensitive Then
If arrConditions(c, n).strOperator.Length = 0 Then
sb.Append(col.SQLID).Append(" = ? ")
Else
sb.Append(col.SQLID).Append(" ").Append(arrConditions(c, n).strOperator).Append(" ? ")
End If
args.Add(strFind)
Else
If arrConditions(c, n).strOperator.Length = 0 Then
sb.Append("lower(").Append(col.SQLID).Append(") = ? ")
Else
sb.Append("lower(").Append(col.SQLID).Append(") ").Append(arrConditions(c, n).strOperator).Append(" ? ")
End If
args.Add(strFind.ToLowerCase)
End If
End If
End If
End If
Next
End If
c = c + 1
Next
TotalCount = -1
End If
If SortColumn <> "" Then sb.Append(SortColumn)
If WithPageLimit Then
Dim limit As Int = mRowsPerPage
If TotalCount < 0 Then limit = limit + 1
sb.Append($" LIMIT ${mFirstRowIndex}, ${limit}"$) 'LIMIT offset, row_count
End If
#if B4A
Dim aargs(args.Size) As String
For i = 0 To args.Size - 1
aargs(i) = args.Get(i)
Next
#else
Dim aargs As List = args
#End If
Return Array(sb.ToString, aargs, TotalCount)
End Sub
Private Sub ImplUpdateDataFromQuery (Query As String, Args As Object, TotalCount As Int, DontSleep As Boolean)
Dim i As Int
Dim iCol As Int
Dim arrArgs() As String = Args
SQLIndex = SQLIndex + 1
If mRowsPerPage = 0 Then Return
Dim MyIndex As Int = SQLIndex
If DontSleep = False Then
Sleep(40)
End If
If MyIndex <> SQLIndex Then
Return
End If
VisibleRowIds.Clear
Dim rs As ResultSet = sql1.ExecQuery2(Query, arrArgs)
Log("ImplUpdateDataFromQuery, rs.RowCount: " & rs.RowCount)
Dim success As Boolean = True
If success Then
Dim RowNumber As Int = 1
Do While RowNumber <= mRowsPerPage And rs.NextRow
VisibleRowIds.Add(rs.GetLong("rowid"))
iCol = 0
For Each c As B4XTableColumn In VisibleColumns
Dim lbl As B4XView = GetLabelFromColumn(c, RowNumber)
Select c.ColumnType
Case COLUMN_TYPE_TEXT
SetTextToCell(rs.GetString(c.SQLID), lbl, c.Searchable, iCol) 'SetTextToCell was edited to give the extra iCol argument
Case COLUMN_TYPE_INTEGER
c.Formatter.FormatLabel(rs.GetInt(c.SQLID), lbl)
Case COLUMN_TYPE_DOUBLE
c.Formatter.FormatLabel(rs.GetDouble(c.SQLID), lbl)
Case COLUMN_TYPE_DATE
lbl.Text = DateTime.Date(rs.GetLong(c.SQLID))
End Select
iCol = iCol + 1
Next
RowNumber = RowNumber + 1
Loop
rs.Close
For i = RowNumber To mRowsPerPage
iCol = 0
For Each c As B4XTableColumn In VisibleColumns
#if B4J
SetTextToCell("", GetLabelFromColumn(c, i), c.Searchable, iCol)
#else
GetLabelFromColumn(c, i).Text = ""
#End If
iCol = iCol + 1
Next
VisibleRowIds.Add(0)
Next
mLastRowIndex = mFirstRowIndex - 2 + RowNumber
If iSearchConditions > 0 Then
mCurrentCount = TotalCount
miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)
End If
mCurrentPage = Ceil(mFirstRowIndex / mRowsPerPage) + 1
Else
Log(LastException)
End If
'this is to get the total number of rows obtained in the search and pass this to the B4XPage
'-------------------------------------------------------------------------------------------
If Query.ToLowerCase.Contains(" order by ") = False Then
If bRunningSearch And Query.ToLowerCase.Contains(" where ") Then
Dim strWhereClause As String = GetWhereClauseFromQuery(Query)
Log("ImplUpdateDataFromQuery, SQL: " & "select count(*) from data " & strWhereClause)
Log("ImplUpdateDataFromQuery, arrArgs(0): " & arrArgs(0))
Dim SenderFilter As Object = sql1.ExecQueryAsync("SQL", "select count(*) from data " & strWhereClause, arrArgs)
Wait For (SenderFilter) SQL1_QueryComplete (success As Boolean, rs2 As ResultSet)
End If
End If
bRunningSearch = False
If xui.SubExists(mCallBack, mEventName & "_DataUpdated", 0) Then
CallSub(mCallBack, mEventName & "_DataUpdated")
End If
End Sub
