B4J=true
Group=Default Group
ModulesStructureVersion=1
Type=Class
Version=9.8
@EndOfDesignText@
Sub Class_Globals
	Private Root As B4XView 'ignore
	Private xui As XUI 'ignore
	
	Public SelectedRowID As Long
	Public RowIDList As List
	
	Private tblData As B4XTable
	Private lblSelectedItem As B4XView
	Private btnEdit, btnDelete As B4XView
End Sub

'You can add more parameters here.
Public Sub Initialize As Object
	Return Me
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
	Root = Root1
	Root.LoadLayout("ShowTable")
	
	B4XPages.SetTitle(Me, "SLQiteLight4   Products")
	
	SelectedRowID = 1
	UpdateSelectedItem

	InitTable
	ShowTableProducts
End Sub

Public Sub Show
	ShowTableProducts
End Sub

Sub btnAdd_Click
	B4XPages.MainPage.ProductEditMode = "Add"
	B4XPages.ShowPage("EditProduct")
End Sub

Sub btnEdit_Click
	B4XPages.MainPage.ProductEditMode = "Edit"
	B4XPages.ShowPage("EditProduct")
End Sub

Sub btnDelete_Click
	Private sf As Object = xui.Msgbox2Async("Do you really want to delete the selected entry ?", "D E L E T E", "Yes", "", "No", Null)
	Wait For (sf) Msgbox_Result (Result As Int)

	If Result = xui.DialogResponse_Positive Then
		Private ProductNumber As Int
		ProductNumber = B4XPages.MainPage.SQL1.ExecQuerySingleResult2("SELECT count(*) FROM Stock WHERE ProductID = ?", Array As String(SelectedRowID))
		If ProductNumber = 0 Then
			B4XPages.MainPage.SQL1.ExecNonQuery("DELETE FROM Products WHERE rowid = " & SelectedRowID)
			ShowTableProducts
		Else
			Private sf As Object = xui.Msgbox2Async("There are " & ProductNumber & " products in stock. Do you really want to delete all this ?", "D E L E T E", "Yes", "", "No", Null)
			Wait For (sf) Msgbox_Result (Result1 As Int)
			If Result1 = xui.DialogResponse_Positive Then
				B4XPages.MainPage.SQL1.ExecNonQuery("DELETE FROM Stock WHERE ProductID = " & SelectedRowID)
				B4XPages.MainPage.SQL1.ExecNonQuery("DELETE FROM Products WHERE rowid = " & SelectedRowID)
				ShowTableProducts
				B4XPages.MainPage.Show
			End If
		End If
	End If
End Sub

Sub ShowTableProducts
	Private Query As String
	
	Query = "SELECT Products.Name, Products.Price, Suppliers.Name AS Supplier "
	Query = Query & "FROM Products, Suppliers "
	Query = Query & "WHERE Suppliers.rowid = Products.SupplierID"
	
	Dim Data As List
	Data.Initialize
	Dim rs As ResultSet = B4XPages.MainPage.SQL1.ExecQuery(Query)
	Do While rs.NextRow
		Dim row(3) As Object
		For i = 0 To 2
			row(i) = rs.GetString2(i)
		Next
		Data.Add(row)
	Loop
	rs.Close
	If tblData.IsInitialized Then
		tblData.SetData(Data)
	End If
	
	ReadDataBaseIDs
End Sub

Private Sub InitTable
	tblData.AddColumn("Name", tblData.COLUMN_TYPE_TEXT)
	tblData.AddColumn("Price", tblData.COLUMN_TYPE_TEXT)
	tblData.AddColumn("Supplier", tblData.COLUMN_TYPE_TEXT)
End Sub

'Reads the database IDs in IDList
Sub ReadDataBaseIDs
	Private ResultSet1 As ResultSet
	
	RowIDList.Initialize		'initialize the ID list

	'We read only the ID column and put them in a List
	ResultSet1 = B4XPages.MainPage.SQL1.ExecQuery("SELECT rowid FROM Products")
	
'	If ResultSet1.RowCount > 0 Then		'check if entries exist
	RowIDList.Initialize						'initialize the ID list
		
	Do While ResultSet1.NextRow
		RowIDList.Add(ResultSet1.GetInt2(0))		'add the ID's to the ID list
	Loop
'	Else
'		ToastMessageShow1("No items found", False)
'	End If
	ResultSet1.Close		'close the ResultSet, we don't need it anymore
End Sub

Sub UpdateSelectedItem
	Private Query As String
	Private ResultSet1 As ResultSet
	
	Query = "SELECT Products.Name, Suppliers.Name"
	Query = Query & " FROM Products, Suppliers"
	Query = Query & " WHERE Products.rowid = " & SelectedRowID & " AND Suppliers.rowID = Products.SupplierID"

	ResultSet1 = B4XPages.MainPage.SQL1.ExecQuery(Query)
	ResultSet1.NextRow
	lblSelectedItem.Text = ResultSet1.GetString2(0) & " / " & ResultSet1.GetString2(1)

	ResultSet1.Close
End Sub

Private Sub tblData_CellClicked (ColumnId As String, RowId As Long)
	SelectedRowID = RowIDList.Get(RowId - 1)
	
	UpdateSelectedItem
	btnDelete.Visible = True
	btnEdit.Visible = True
End Sub

