B4A=true
Group=Default Group
ModulesStructureVersion=1
Type=Class
Version=9.85
@EndOfDesignText@
#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=\%PROJECT_NAME%.zip

'B4A ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=start&Args=..\..\B4A\%PROJECT_NAME%.b4a 
'B4i ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=start&Args=..\..\B4i\%PROJECT_NAME%.b4i 
'B4J ide://run?file=%WINDIR%\System32\cmd.exe&Args=/c&Args=start&Args=..\..\B4J\%PROJECT_NAME%.b4j


' SQLiteLight3  2022.07.03	version 1.1
' converted into a B4XPages project
' SQLiteLight3  2014.04.05	version 1.0
'	This a small evolution of the SQLiteLight1 project
' Some user interface changes :
' Added two buttons btnFist and btnLast and a show / hide routine for the navigation buttons
' Added a new Activity displaying the database in a table, using  DBUtils.
'
' Comments from SQLiteLight1
' This is a small project showing the basics of a SQLite database
' The database has 4 columns:
' FirstName			TEXT
' LastName			TEXT
' City					TEXT
'
' For the ID we use the rowid column which is automatically incremented by one when a new entry is added.
' When we delete an entry the ID is lost
'
' Following functions are supported:
' - Display in a table
' 	- Add  display the Edit activity to add a new entry
'		- Edit display the Edit activity to edit the selected entry
'   - Filer / UnFilter
'		- Set Filter	display the filter definition activity
' - Edit :
' 	- First		jumps to the first entry
' 	- Prev		shows the prvious entry
' 	- Next		shows the next entry
' 	- Last		jumps to the last entry
' 	- Add			adds a new entry
' 	- Delete	deletes the current entry
' 	- Update	updates the current entry
' - Filter :
'		- OR or AND boolean operator
'   - Filter
' The database name, the table name and the column names are variables
' The user interface is kept very simple
'
' Written by : klaus

Sub Class_Globals
	Private Root As B4XView
	Private xui As XUI
	
	Public Edit As PageEdit
	Public Filter As PageFilter

	Private lblToastMessage As B4XView
	Private ToastMessageTimer As Timer
	
	Private lblSelectedItem As Label
	Private btnFilter As B4XView
	Private tblData As B4XTable
	Public SQL1 As SQL
	Public SQLDataBasePath As String
	Public CurrentIndex = -1 As Int		' index of the current entry
	
	' with this program you can read any database with one Table
	' to show what you need to change
	' DBIndex = 0 loads the personsflc.db
	' DBIndex = 1 loads the citylist.db
	Public DBIndex = 1 As Int
	If DBIndex = 0 Then
		Public ColNumber = 3 As Int		' number of columns
		Public SQLDateBaseName = "personsflc.db" As String  'flc means FirstName, LastName, City
		Public SQLTabelName = "persons" As String
	Else
		Public ColNumber = 4 As Int		' number of columns
		Public SQLDateBaseName = "citylist.db" As String  'flc means FirstName, LastName, City
		Public SQLTabelName = "citylist" As String
	End If
	Public ColNames(ColNumber) As String
	Public ColAliasNames(ColNumber) As String
	Public ColDataTypes(ColNumber) As String
	If DBIndex = 0 Then
		ColNames(0) = "FirstName"
		ColNames(1) = "LastName"
		ColNames(2) = "City"
		
		ColAliasNames(0) = "First name"
		ColAliasNames(1) = "Last name"
		ColAliasNames(2) = "City"
		
		ColDataTypes(0) = "TEXT"
		ColDataTypes(1) = "TEXT"
		ColDataTypes(2) = "TEXT"
	Else
		ColNames(0) = "City"
		ColNames(1) = "Country"
		ColNames(2) = "Longitude"
		ColNames(3) = "Latitude"
	
		ColAliasNames(0) = "City"
		ColAliasNames(1) = "Country"
		ColAliasNames(2) = "Longitude"
		ColAliasNames(3) = "Latitude"
	
		ColDataTypes(0) = "TEXT"
		ColDataTypes(1) = "TEXT"
		ColDataTypes(2) = "REAL"
		ColDataTypes(3) = "REAL"
	End If
	
	Public RowIDList As List		' list containing the IDs of the database
	' we need it because the IDs can be different from the list indexes
	' if we delete an entry its ID is lost	
End Sub

Public Sub Initialize
'	B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
	Root = Root1
	Root.LoadLayout("MainPage")
	B4XPages.SetTitle(B4XPages.MainPage, "SQLiteLight3")
	
	Edit.Initialize
	B4XPages.AddPage("Edit", Edit)
	Filter.Initialize
	B4XPages.AddPage("Filter", Filter)

	ToastMessageTimer.Initialize("ToastMessageTimer", 3000)
	
'	MainForm.SetWindowSizeLimits(580, 500, 1000, 1000)
	
#If B4J
	xui.SetDataFolder("SQLiteLight3")
#End If
	SQLDataBasePath = xui.DefaultFolder
	
'	File.Delete(SQLDataBasePath, SQLDateBaseName) ' only for testing, removes the database
		
	'check if the database already exists
	If File.Exists(SQLDataBasePath, SQLDateBaseName) = False Then
		'copy the default DB
		File.Copy(File.DirAssets, SQLDateBaseName, SQLDataBasePath, SQLDateBaseName)
	End If
#If B4J
	SQL1.InitializeSQLite(SQLDataBasePath, SQLDateBaseName, True)
#Else
	SQL1.Initialize(SQLDataBasePath, SQLDateBaseName, True)
#End If
	
	InitTable
	
	Show
End Sub

Private Sub B4XPage_CloseRequest As ResumableSub
	Dim sf As Object = xui.Msgbox2Async("Do you want to quit ?", "Quit", "Yes", "Cancel", "No", Null)
	Wait For (sf) Msgbox_Result (Result As Int)
	If Result = xui.DialogResponse_Positive Then
		SQL1.Close
		Return True
	End If
	Return False
End Sub

Public Sub Show
	ShowTable
	SetbtnFilterText
	
	If CurrentIndex >= 0 Then
		UpdateSelectedEntryDisplay
	End If
End Sub

Private Sub UpdateSelectedEntryDisplay
	Private i As Int
	Private Query As String
	Private ResultSet1 As ResultSet
	
	Query = "SELECT " & ColNames(0)
	For i = 1 To ColNames.Length - 1
		Query = Query & ", " & ColNames(i)
	Next
	Query = Query & " FROM " & SQLTabelName & " WHERE rowid = " & RowIDList.Get(CurrentIndex)
	ResultSet1 = SQL1.ExecQuery(Query)
	ResultSet1.NextRow
	lblSelectedItem.Text = ResultSet1.GetString(ColNames(0)) & " " & ResultSet1.GetString(ColNames(1)) & "   " '& ResultSet1.GetString(ColNames(2))
	ResultSet1.Close
End Sub

'Shows the database in a table in a B4XTable
Public Sub ShowTable
	Private i As Int
	Private Query As String

	ReadDataBaseRowIDs
	
	Query = "SELECT " & ColNames(0)
	For i = 1 To ColNames.Length - 1
		Query = Query & ", " & ColNames(i)
	Next
	Query = Query & " FROM " & SQLTabelName
	'depending if the filter is active or not we add the filter query at the end of the query
	'the filter query is defined in the Filter Activity
	If Filter.flagFilterActive = False Then
		btnFilter.Text = "Filter"
	Else
		btnFilter.Text = "UnFilter"
		Query = Query & Filter.Query
	End If

	Dim Data As List
	Data.Initialize
	Dim rs As ResultSet = SQL1.ExecQuery(Query)
	Do While rs.NextRow
		Dim row(ColNames.Length) As Object
		For i = 0 To ColNames.Length - 1
			If ColDataTypes(i) = "TEXT" Then
				row(i) = rs.GetString2(i)
			Else
				row(i) = rs.GetDouble2(i)
			End If
		Next
		Data.Add(row)
	Loop
	rs.Close
	tblData.SetData(Data)
	
	UpdateSelectedEntryDisplay
End Sub

Private Sub InitTable
	For i = 0 To ColNames.Length - 1
		If ColDataTypes(i) = "TEXT" Then
			tblData.AddColumn(ColAliasNames(i), tblData.COLUMN_TYPE_TEXT)
		Else
			tblData.AddColumn(ColAliasNames(i), tblData.COLUMN_TYPE_NUMBERS)
		End If
	Next
	'Sets the column widths to 1/3 of the total width
	For i = 0 To 2
		Private col As B4XTableColumn
		col = tblData.Columns.Get(i)
		col.Width = tblData.mBase.Width / 3
	Next
End Sub

'Reads the database IDs in RowIDList
Private Sub ReadDataBaseRowIDs
	Private ResultSet1 As ResultSet
	Private i As Int
	ResultSet1 = SQL1.ExecQuery("SELECT * FROM " & SQLTabelName)
'	Private Cols(ColNames.Length)
	For i = 0 To ResultSet1.ColumnCount - 1
		ColNames(i) = ResultSet1.GetColumnName(i)
	Next
	'We read only the ID column and put them in the RowIDList
	If Filter.flagFilterActive = False Then
		ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM " & SQLTabelName)
	Else
		ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM " & SQLTabelName  & Filter.Query)
	End If
	
	RowIDList.Initialize								'initialize the ID list
	Do While ResultSet1.NextRow
		RowIDList.Add(ResultSet1.GetInt2(0))		'add the RowID's to the RowID list
	Loop
	If RowIDList.Size > 0 Then
		CurrentIndex = 0							'set the current index to 0
	Else
		CurrentIndex = -1							'set the current index to -1, no selected item
		ShowToastMessage("No items found", False)
	End If
	ResultSet1.Close												'close the cursor, we don't need it anymore
End Sub

Private Sub btnAdd_Click
	Edit.Mode = "Add"
	B4XPages.ShowPage("Edit")
End Sub

Private Sub btnEdit_Click
	Edit.Mode = "Edit"
	B4XPages.ShowPage("Edit")
End Sub

Private Sub btnFilter_Click
	Filter.flagFilterActive = Not(Filter.flagFilterActive)
	SetbtnFilterText
	ShowTable
End Sub

Private Sub btnSetFilter_Click
	B4XPages.ShowPage("Filter")
End Sub

Private Sub tblData_CellClicked (ColumnId As String, RowId As Long)
	CurrentIndex = RowId - 1
	UpdateSelectedEntryDisplay
End Sub

Private Sub SetbtnFilterText
	If Filter.flagFilterActive = True Then
		btnFilter.Text = "UnFilter"
	Else
		btnFilter.Text = "Filter"
	End If
End Sub

Private Sub ShowToastMessage(Message As String, LongDuration As Boolean)
	lblToastMessage.Text = Message
	
	If LongDuration Then
		ToastMessageTimer.Interval = 2000
	Else
		ToastMessageTimer.Interval = 1000
	End If
	ToastMessageTimer.Enabled = True
	lblToastMessage.Visible = True
End Sub

Private Sub ToastMessageTimer1_Tick
	lblToastMessage.Visible = False
	ToastMessageTimer.Enabled = False
End Sub
