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 EditProduct As EditProductPage
	Public EditStock As EditStockPage
	Public EditSupplier As EditSupplierPage
	Public Products As ProductsPage
	Public Suppliers As SuppliersPage
	Public Filter As FilterPage

	Public SQLDataBasePath As String
	Public SQLDateBaseName As String
	
	Public SQLTabelName As String
	
	Public HtmlCSS As String
	
	Public StockSelectedRowID As Long
	Public StockSelectedItem As Long
	Public StockRowIDList As List
	Public StockEditMode, ProductEditMode, SupplierEditMode As String
	Public FilterActive As Boolean
	
	Private tblData As B4XTable
	Private lblSelectedItem As Label
	Private btnFilter As Button
End Sub

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

Private Sub B4XPage_Created (Root1 As B4XView)
	Root = Root1
	Root.LoadLayout("MainPage")
	
	B4XPages.SetTitle(Me, "SQLiteLight4  Stock")
	
	EditProduct.Initialize
	B4XPages.AddPage("EditProduct", EditProduct)
	EditStock.Initialize
	B4XPages.AddPage("EditStock", EditStock)
	EditSupplier.Initialize
	B4XPages.AddPage("EditSupplier", EditSupplier)
	Products.Initialize
	B4XPages.AddPage("Products", Products)
	Suppliers.Initialize
	B4XPages.AddPage("Suppliers", Suppliers)
	Filter.Initialize
	B4XPages.AddPage("Filter", Filter)
	
	'initialize the variables
#If B4J
	xui.SetDataFolder("SQLiteLight4")
#End If	
	SQLDataBasePath = xui.DefaultFolder
	SQLDateBaseName = "Stock.db"
	
	File.Delete(SQLDataBasePath, SQLDateBaseName) ' only for testing, removes the database
	If File.Exists(SQLDataBasePath, SQLDateBaseName) = True Then
#If B4J
		SQL1.InitializeSQLite(SQLDataBasePath, SQLDateBaseName, True)
#Else
		SQL1.Initialize(SQLDataBasePath, SQLDateBaseName, True)
#End If
	Else
#If B4J
		SQL1.InitializeSQLite(SQLDataBasePath, SQLDateBaseName, True)
#Else
		SQL1.Initialize(SQLDataBasePath, SQLDateBaseName, True)
#End If
		SQLInit
	End If
	
	InitTable
	ShowTableStock
	
	StockSelectedRowID = StockRowIDList.Get(StockSelectedItem)
	UpdateSelectedItem
End Sub

Public Sub Show
	If FilterActive = False Then
		btnFilter.Text = "Filter"
	Else
		btnFilter.Text = "UnFilter"
	End If
	ShowTableStock
End Sub

Sub btnProducts_Click
	B4XPages.ShowPage("Products")
End Sub

Sub btnSuppliers_Click
	B4XPages.ShowPage("Suppliers")
End Sub

Sub btnAdd_Click
	StockEditMode = "Add"
	B4XPages.ShowPage("EditStock")
End Sub

Sub btnEdit_Click
	StockEditMode = "Edit"
	B4XPages.ShowPage("EditStock")
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
		SQL1.ExecNonQuery("DELETE FROM Stock WHERE rowid = " & StockSelectedRowID)
		ShowTableStock
		StockSelectedRowID = -1
		lblSelectedItem.Text = ""
	End If
End Sub

Sub btnFilter_Click
	If FilterActive = True Then
		btnFilter.Text = "Filter"
		FilterActive = False
	Else
		btnFilter.Text = "UnFilter"
		FilterActive = True
	End If
	ShowTableStock
End Sub

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

Private Sub tblData_CellClicked (ColumnId As String, RowId As Long)
	StockSelectedItem = RowId - 1
	StockSelectedRowID = StockRowIDList.Get(StockSelectedItem)
	
	UpdateSelectedItem
End Sub

'Shows the Stock table in a WebView
Public Sub ShowTableStock
	Private Query As String
	
	Query = "SELECT Stock.Number, Products.Name AS Product, Suppliers.Name AS Supplier, Products.Price AS Price, Stock.Number * Products.Price AS Value, Stock.Date AS Date"
	Query = Query & " FROM Stock, Products, Suppliers"
	Query = Query & " WHERE Products.rowid = Stock.ProductID AND Suppliers.rowid = Products.SupplierID"
	
	If FilterActive = True Then
		Query = Query & Filter.Query
	End If
	
	Dim Data As List
	Data.Initialize
	Dim rs As ResultSet = B4XPages.MainPage.SQL1.ExecQuery(Query)
	Do While rs.NextRow
		Dim row(6) As Object
		row(0) = rs.GetInt2(0)
		row(1) = rs.GetString2(1)
		row(2) = rs.GetString2(2)
		row(3) = rs.GetDouble2(3)
		row(4) = rs.GetInt2(0) * rs.GetDouble2(3)
		row(5) = rs.GetLong2(5)
		Data.Add(row)
	Loop
	rs.Close
	tblData.SetData(Data)
	
	ReadDataBaseIDs
End Sub

Private Sub InitTable
	tblData.AddColumn("Number", tblData.COLUMN_TYPE_NUMBERS)
	tblData.AddColumn("Product", tblData.COLUMN_TYPE_TEXT)
	tblData.AddColumn("Suppliers", tblData.COLUMN_TYPE_TEXT)
	tblData.AddColumn("Price", tblData.COLUMN_TYPE_NUMBERS)
	tblData.AddColumn("Value", tblData.COLUMN_TYPE_NUMBERS)
	tblData.AddColumn("Date", tblData.COLUMN_TYPE_DATE)
End Sub

'Reads the table rowids in the IDList for each table
Sub ReadDataBaseIDs
	Private ResultSet1 As ResultSet
	
	StockRowIDList.Initialize		'initialize the ID list
	'We read only the ID column and put them in a List
	ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM Stock")
	
	StockRowIDList.Initialize								'initialize the ID list
	Do While ResultSet1.NextRow
		StockRowIDList.Add(ResultSet1.GetLong2(0))	'add the ID's to the ID list
	Loop

	Products.RowIDList.Initialize		'initialize the ID list
	ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM Products")
	Products.RowIDList.Initialize							'initialize the ID list
	Do While ResultSet1.NextRow
		Products.RowIDList.Add(ResultSet1.GetLong2(0))		'add the ID's to the ID list
	Loop
	
	Suppliers.RowIDList.Initialize		'initialize the ID list
	ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM Suppliers")
	Suppliers.RowIDList.Initialize						'initialize the ID list
	Do While ResultSet1.NextRow
		Suppliers.RowIDList.Add(ResultSet1.GetLong2(0))		'add the ID's to the ID list
	Loop
	
	ResultSet1.Close											'close the ResultSet, we don't need it anymore
End Sub

'Updates the text of the selected item in the lblSelectedItem Label
Sub UpdateSelectedItem
	Private Query As String
	Private ResultSet1 As ResultSet
	
	Query = "SELECT Stock.Number, Products.Name, Suppliers.Name AS Supplier, date(Stock.Date / 1000, 'unixepoch')"
	Query = Query & " FROM Stock, Products, Suppliers"
	Query = Query & " WHERE Stock.rowid = " & StockSelectedRowID & " AND Products.rowid = Stock.ProductID AND Suppliers.rowid = Products.SupplierID"

	ResultSet1 = SQL1.ExecQuery(Query)
	ResultSet1.NextRow
	lblSelectedItem.Text = ResultSet1.GetString2(0) & " : " & ResultSet1.GetString2(1) & " - " & ResultSet1.GetString2(2) & "  " & ResultSet1.GetString2(3)

	ResultSet1.Close
End Sub

'create the stocdatabase
Sub SQLInit
	Private query As String
	
	query = "CREATE TABLE Stock (Number INTEGER, ProductID INTEGER, Date INTEGER)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (5, 1, 1092941466000)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (10, 2, 1265583600000)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (15, 4, 1328655600000)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (2, 5, 1297119600000)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (20, 3, 1391814000000)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (15, 7, " & DateTime.Now & ")"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (8, 8, 1391814000000)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (8, 9, 1391814000000)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (13, 10, " & DateTime.Now & ")"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (4, 11, " & DateTime.Now & ")"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (2, 12, " & DateTime.Now & ")"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (1, 6, " & DateTime.Now & ")"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Stock VALUES (3, 13, " & DateTime.Now & ")"
	SQL1.ExecNonQuery(query)

	query = "CREATE TABLE Products (Name TEXT, Price REAL, SupplierID INTEGER)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Brake', 350.50, 1)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Brake', 175.65, 3)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Brake', 225, 4)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Brake', 270, 5)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Brake', 250, 6)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Brake', 300, 7)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Exhaust pipe', 150, 2)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Exhaust pipe', 250, 1)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Exhaust pipe', 175, 4)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Exhaust pipe', 175, 3)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Exhaust pipe', 175, 5)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Exhaust pipe', 175, 6)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Products VALUES ('Exhaust pipe', 300, 7)"
	SQL1.ExecNonQuery(query)
		
	query = "CREATE TABLE Suppliers (Name TEXT, Address TEXT, City TEXT)"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Suppliers VALUES ('Renault','Rue de la Gare 30', 'Paris')"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Suppliers VALUES ('Ford', 'Main street 11', 'Detroit')"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Suppliers VALUES ('VW', 'Hauptstrasse 3', 'Wolfsburg')"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Suppliers VALUES ('Opel', 'Bahnhofsplatz 1', 'Rüsselsheim')"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Suppliers VALUES ('PSA', 'Avenue de la Grande Armée 75', 'Paris')"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Suppliers VALUES ('Fiat', 'Corso Giulio Cesare 360', 'Torino')"
	SQL1.ExecNonQuery(query)
	query = "INSERT INTO Suppliers VALUES ('Tesla', 'Main street 1000', 'Chicago')"
	SQL1.ExecNonQuery(query)
End Sub
