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
Dim lstCurrentArgs As List
Dim lstCurrentArgsIsText As List
Dim TotalCount As Int
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(" ")
lstCurrentArgs.Initialize
lstCurrentArgsIsText.Initialize
Log("B4XTable, BuildQuery, miSearchConditions: " & miSearchConditions)
If miSearchConditions = 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
lstCurrentArgsIsText.Add(arrConditions(c, n).bIsTextArgument)
strFind = arrConditions(c, n).strSearchText
If lstCurrentArgs.Size = 0 Then
sb.Append(" WHERE ")
Else
If arrConditions(c, n).bSQL_OR Then
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(" = ? ")
lstCurrentArgs.Add(strFind)
Else
If arrConditions(c, n).strOperator.StartsWith("between") Then
Dim arrFind() As String = Regex.Split(",", strFind)
If col.ColumnType = COLUMN_TYPE_DOUBLE Then
Dim arrFindDouble(2) As Double
arrFindDouble(0) = arrFind(0)
arrFindDouble(1) = arrFind(1)
sb.Append(col.SQLID).Append(" between cast(? as real) and cast(? as real)")
lstCurrentArgs.Add(arrFindDouble(0))
lstCurrentArgs.Add(arrFindDouble(1))
Else
Dim arrFindInt(2) As Int
arrFindInt(0) = arrFind(0)
arrFindInt(1) = arrFind(1)
sb.Append(col.SQLID).Append(" between cast(? as integer) and cast(? as integer)")
lstCurrentArgs.Add(arrFindInt(0))
lstCurrentArgs.Add(arrFindInt(1))
End If
Else
If arrConditions(c, n).strOperator.StartsWith("length") Then
Select Case arrConditions(c, n).strOperator
Case "length ="
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) = cast(? as integer) ")
Case "length >"
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) > cast(? as integer) ")
Case "length <"
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) < cast(? as integer) ")
Case "length <>"
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) <> cast(? as integer) ")
End Select
Else
sb.Append(col.SQLID).Append(" ").Append(arrConditions(c, n).strOperator).Append(" ? ")
End If
lstCurrentArgs.Add(strFind)
End If
End If
Else
If arrConditions(c, n).strOperator.StartsWith("between") Then
Dim arrFind() As String = Regex.Split(",", strFind)
sb.Append(col.SQLID).Append(" between ? and ?")
lstCurrentArgs.Add(arrFind(0))
lstCurrentArgs.Add(arrFind(1))
Else
If arrConditions(c, n).strOperator.StartsWith("length") Then
Select Case arrConditions(c, n).strOperator
Case "length ="
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) = cast(? as integer) ")
Case "length >"
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) > cast(? as integer) ")
Case "length <"
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) < cast(? as integer) ")
Case "length <>"
sb.Append("length(coalesce(").Append(col.SQLID).Append(", '')) <> cast(? as integer) ")
End Select
lstCurrentArgs.Add(strFind)
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
lstCurrentArgs.Add(strFind & "*")
Else
lstCurrentArgs.Add("*" & strFind & "*")
End If
Else
sb.Append(col.SQLID).Append(" LIKE ? ")
If arrConditions(c,n).bPrefixSearch Then
lstCurrentArgs.Add(strFind & "%")
Else
lstCurrentArgs.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
lstCurrentArgs.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
lstCurrentArgs.Add(strFind.ToLowerCase)
End If
End If
End If
End If
End If
Next
End If
c = c + 1
Next
TotalCount = -1
End If
Log("B4XTable, BuildQuery, SortColumn.Length: " & SortColumn.Length)
If SortColumn <> "" Then
sb.Append(SortColumn)
End If
If sb.ToString.ToLowerCase.Contains(" where ") Then
strCurrentWhereClause = GetWhereClauseFromQuery(sb.ToString)
Log("BuildQuery, strCurrentWhereClause: |" & strCurrentWhereClause & "|")
End If
If WithPageLimit Then
Dim limit As Int = mRowsPerPage
If TotalCount < 0 Then limit = limit + 1
sb.Append($" LIMIT ${mFirstRowIndex}, ${limit}"$)
End If
Log("B4XTable, BuildQuery, sb.ToString: " & sb.ToString)
#if B4A
Dim aargs(lstCurrentArgs.Size) As String
For i = 0 To lstCurrentArgs.Size - 1
aargs(i) = lstCurrentArgs.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)
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)
Case COLUMN_TYPE_INTEGER
lbl.Text = rs.GetInt(c.SQLID)
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
Dim bAreThereMoreResults As Boolean = RowNumber = mRowsPerPage + 1 And rs.NextRow
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 TotalCount > -1 Then
bCanDoNextPage = mLastRowIndex < TotalCount - 1
mCurrentCount = TotalCount
Else
bCanDoNextPage = bAreThereMoreResults
marrSearchArgs = arrArgs
End If
bCanDoPreviousPage = mFirstRowIndex > 0
Log("B4XTable, ImplUpdateDataFromQuery, mCurrentCount: " & mCurrentCount)
miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)
mCurrentPage = Ceil(mFirstRowIndex / mRowsPerPage) + 1
rs.Close
Else
Log(LastException)
End If
If xui.SubExists(mCallBack, mEventName & "_DataUpdated", 0) Then
CallSub(mCallBack, mEventName & "_DataUpdated")
End If
End Sub