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

Sub Class_Globals
	Private Root As B4XView
	Private xui As XUI
	
	Public SQL1 As SQL
	
	Public DBFileName = "personsflca.db" As String
	Public DBFileDir As String
	Public DBTableName  = "persons" As String
	
	Public lstTable, lstRowID As List
	Public DisplayMode = 1 As Int
	Public SelectedRow, SelectedCol As Int
	Public Query As String
	
	Private WebView1 As WebView
	Private edtFirstName, edtLastName, edtAddress, edtCity As B4XView
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")
	
	'copies the database from DirAssets
#If B4J
	DBFileDir = DBUtils.CopyDBFromAssets(DBFileName, "DBUtilsDemo")
	SQL1.InitializeSQLite(DBFileDir, DBFileName, True)
#Else
	DBFileDir = DBUtils.CopyDBFromAssets(DBFileName)
	SQL1.Initialize(DBFileDir, DBFileName, True)
#End If
	Log(DBFileDir)
	
	
	ShowTableInWebView
	ReadDatabaseInTable
	UpdateSelectedData
End Sub

#If B4J
Private Sub WebView1_LocationChanged (Location As String)
	'parse the row and column numbers from the URL
	Private values() As String
	If Location.Contains("stub") Then
		values = Regex.Split("[.]", Location.SubString(7))
		SelectedCol = values(0)
		SelectedRow = values(1)
	
		UpdateSelectedData	'updates the selected entry
	End If
End Sub
#Else
Private Sub WebView1_OverrideUrl (Url As String) As Boolean
	'parse the row and column numbers from the URL
	Private values() As String
	values = Regex.Split("[.]", Url.SubString(7))
	SelectedCol = values(0)
	SelectedRow = values(1)
	
	UpdateSelectedData	'updates the selected entry
	
	Return True 'Don't try to navigate to this URL
End Sub
#End If

Private Sub btnAdd_Click
	'checks if the data already exist in the database
	Private ResultSet1 As ResultSet
	ResultSet1 = SQL1.ExecQuery2("SELECT * FROM " & DBTableName & " WHERE FirstName = ? AND LastName = ? AND Address = ? AND City = ?", Array As String(edtFirstName.Text, edtLastName.Text, edtAddress.Text, edtCity.Text))
	If ResultSet1.NextRow = True Then
		'if yes display a MessageBox
		xui.MsgboxAsync("Data already exists", "SQL")
	Else
		'adds the new data
		Private lstAdd As List
		Private mapRow As Map
		
		'defines the Map with the column names as key and the values
		mapRow.Initialize
		mapRow.Put("FirstName", edtFirstName.Text)
		mapRow.Put("LastName", edtLastName.Text)
		mapRow.Put("Address", edtAddress.Text)
		mapRow.Put("City", edtCity.Text)
		
		'defines the List with the row to add
		lstAdd.Initialize
		lstAdd.Add(mapRow)
		
		'inserts the new row
		DBUtils.InsertMaps(SQL1, DBTableName, lstAdd)
		
		ShowTableInWebView	'shows the updated table
		ReadDatabaseInTable	'rereads the data
	End If
End Sub

Private Sub btnUpdate_Click
	Private mapUpdate, mapWhere As Map
	
	'defines the Map with the column names as key and the values
	mapUpdate.Initialize
	mapUpdate.Put("FirstName", edtFirstName.Text)
	mapUpdate.Put("LastName", edtLastName.Text)
	mapUpdate.Put("Address", edtAddress.Text)
	mapUpdate.Put("City", edtCity.Text)
	
	'defines the Map with the WHERE conditions
	mapWhere.Initialize
	mapWhere.Put("rowid", lstRowID.Get(SelectedRow))
	
	'updates the the selected row
	DBUtils.UpdateRecord2(SQL1, DBTableName, mapUpdate, mapWhere)
	
	ShowTableInWebView	'shows the updated table
	ReadDatabaseInTable	'rereads the data
End Sub

Private Sub btnDelete_Click
	Dim sf As Object = xui.Msgbox2Async("Do you really want to delete the selected row?", "SQL Delete", "Yes", "", "No", Null)
	Wait For (sf) Msgbox_Result (Result As Int)
	If Result = xui.DialogResponse_Positive Then
		Private mapWhere As Map
		mapWhere.Initialize
		mapWhere.Put("rowid",  lstRowID.Get(SelectedRow))
		DBUtils.DeleteRecord(SQL1, DBTableName, mapWhere)
		Log("Deleted!!!")
		
		ShowTableInWebView	'shows the updated table
		ReadDatabaseInTable	'rereads the data
	End If
End Sub

Private Sub ShowTableInWebView
	If DisplayMode = 1 Then
		Query = "SELECT FirstName As [First name], LastName As Name, Address, City FROM " & DBTableName
	Else
		'[First Name] || ' ' || [Last Name] As Name =>
		'Creates a column named Name which is made of the First Name AND Last Name fields (with a space between them).
		Query = "SELECT [FirstName] || ' ' || [LastName] As Name, Address As Address, City As City FROM " & DBTableName
	End If

	WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL1, Query, Null, 0, True))
End Sub

Private Sub UpdateSelectedData
	Private ResultSet1 As ResultSet
	
	Query = "SELECT * FROM " & DBTableName & " WHERE rowid = " & lstRowID.Get(SelectedRow)
	ResultSet1 = SQL1.ExecQuery(Query)
	ResultSet1.NextRow
	
	edtFirstName.Text = ResultSet1.GetString("FirstName")
	edtLastName.Text = ResultSet1.GetString("LastName")
	edtAddress.Text = ResultSet1.GetString("Address")
	edtCity.Text = ResultSet1.GetString("City")
	ResultSet1.Close
End Sub

Private Sub ReadDatabaseInTable
	'loads the data in a table in memory
	lstTable = DBUtils.ExecuteMemoryTable(SQL1, Query, Null, 0)
	
	'reads the rowids in a List
	lstRowID = DBUtils.ExecuteList(SQL1, "SELECT rowid FROM " & DBTableName, Null, 0)
End Sub
