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
'Log("B4XTable, BuildQuery, mCurrentCount: " & mCurrentCount)
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
'Log("BuildQuery, strFind: " & strFind)
If lstCurrentArgs.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(" = ? ") 'this may not always work with double values
lstCurrentArgs.Add(strFind)
Else 'If arrConditions(c, n).strOperator.Length = 0
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 'If arrConditions(c, n).strOperator.Length = 0
Else 'If col.ColumnType = COLUMN_TYPE_INTEGER Or col.ColumnType = COLUMN_TYPE_DOUBLE
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 'If col.ColumnType = COLUMN_TYPE_INTEGER Or col.ColumnType = COLUMN_TYPE_DOUBLE
Next
End If
c = c + 1
Next
TotalCount = -1 'so TotalCount will be -1 if we are doing a search
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 'this will be the case if we are doing a search
sb.Append($" LIMIT ${mFirstRowIndex}, ${limit}"$) 'LIMIT offset, row_count
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)
'Log("B4XTable, 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)
Case COLUMN_TYPE_INTEGER
'c.Formatter.FormatLabel(rs.GetInt(c.SQLID), lbl)
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
'as the previous and next page views are not done in the custom view anymore we are just dealing with Boolean variables here
'---------------------------------------------------------------------------------------------------------------------------
bCanDoNextPage = mLastRowIndex < TotalCount - 1
mCurrentCount = TotalCount 'this used to be just past this If block, but that meant mCurrentCount could be -1 (seems to indicate that current row count is unknown)
Else
'TotalCount -1 means we ran a search and mCurrentCount will be set later by SetCurrentCountAfterSearch (run from the DataUpdated Event)
'--------------------------------------------------------------------------------------------------------------------------------------
bCanDoNextPage = bAreThereMoreResults
'this is to get the total number of rows obtained in the search (user initiated search) and pass this to the B4XPage
'-------------------------------------------------------------------------------------------------------------------
marrSearchArgs = arrArgs
End If
bCanDoPreviousPage = mFirstRowIndex > 0
'This used to be here, see above comment!
'----------------------------------------
'mCurrentCount = TotalCount
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
