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 Query As String
	Public Active = False As Boolean
	
	' Boolean operator used in the filter
	' can be OR or AND
	Public BooleanOperator = "AND " As String
	
	' Variables used to hold the selected indexes of the Spinners
	Public SelectedProduct As Int
	Public SelectedSupplier As Int
	Public SelectedDateBegin As Int
	Public SelectedDateEnd As Int
	
	Private cbxProducts, cbxSuppliers, cbxDateBegin, cbxDateEnd As B4XComboBox
	Private lstProducts, lstSuppliers, lstDate As List
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("FilterStock")
	'load the layout to Root
	B4XPages.SetTitle(B4XPages.GetPage("Filter"), "SQLiteLight4  Stock")
	
	InitComboBoexs
	
End Sub

Public Sub Show
'	InitComboBoexs
	Log("Filter Show")
End Sub

Sub InitComboBoexs
	Private Query As String
	Private ResultSet1 As ResultSet
	
	Query = "SELECT DISTINCT Name FROM Products"
	ResultSet1 = B4XPages.MainPage.SQL1.ExecQuery(Query)
	
	lstProducts.Initialize
	lstProducts.Add("no filter")
	Do While ResultSet1.NextRow
		lstProducts.Add(ResultSet1.GetString2(0))
	Loop
	cbxProducts.SetItems(lstProducts)
	cbxProducts.SelectedIndex = SelectedProduct
	
	Query = "SELECT DISTINCT Name FROM Suppliers"
	ResultSet1 = B4XPages.MainPage.SQL1.ExecQuery(Query)
	
	lstSuppliers.Initialize
	lstSuppliers.Add("no filter")
	Do While ResultSet1.NextRow
		lstSuppliers.Add(ResultSet1.GetString2(0))
	Loop
	cbxSuppliers.SetItems(lstSuppliers)
	cbxSuppliers.SelectedIndex = SelectedSupplier
	
	Query = "SELECT DISTINCT date(Stock.Date / 1000, 'unixepoch') AS Date FROM Stock ORDER BY Date ASC"
	ResultSet1 = B4XPages.MainPage.SQL1.ExecQuery(Query)
	
	lstDate.Initialize
	lstDate.Add("no filter")
	Do While ResultSet1.NextRow
		lstDate.Add(ResultSet1.GetString2(0))
	Loop
	cbxDateBegin.SetItems(lstDate)
	cbxDateBegin.SelectedIndex = SelectedDateBegin
	cbxDateEnd.SetItems(lstDate)
	cbxDateEnd.SelectedIndex = SelectedDateEnd
End Sub

Private Sub cbxProducts_SelectedIndexChanged (Index As Int)
	SelectedProduct = Index
End Sub

Private Sub cbxSuppliers_SelectedIndexChanged (Index As Int)
	SelectedSupplier = Index
End Sub

Private Sub cbxDateBegin_SelectedIndexChanged (Index As Int)
	SelectedDateBegin = Index
End Sub

Private Sub cbxDateEnd_SelectedIndexChanged (Index As Int)
	SelectedDateEnd = Index
End Sub

Private Sub btnFilter_Click
	Query = BuildQuery
	B4XPages.MainPage.FilterActive = True
	B4XPages.ClosePage(Me)
	B4XPages.MainPage.Show
End Sub

Private Sub btnReset_Click
	cbxProducts.SelectedIndex = 0
	cbxSuppliers.SelectedIndex = 0
	cbxDateBegin.SelectedIndex = 0
	cbxDateEnd.SelectedIndex = 0
End Sub

Private Sub BuildQuery As String
	Private Query As String
	
	Query = ""
	
	If SelectedProduct > 0 Then
		Query = " AND Products.Name = '" & lstProducts.Get(cbxProducts.SelectedIndex) & "'"
	End If

	If SelectedSupplier > 0 Then
		Query = Query & " AND Suppliers.Name = '" & lstSuppliers.Get(cbxSuppliers.SelectedIndex) & "'"
	End If

	If SelectedDateBegin > 0 Or SelectedDateEnd > 0 Then
		If SelectedDateBegin = SelectedDateEnd Then
			Query = Query & " AND date(Stock.Date / 1000, 'unixepoch') = '" & lstDate.Get(cbxDateBegin.SelectedIndex) & "' ORDER BY date(Stock.Date / 1000, 'unixepoch') ASC"
		Else If SelectedDateBegin > 0 And SelectedDateEnd > 0 Then
			If SelectedDateBegin <= SelectedDateEnd Then
				Query = Query & " AND date(Stock.Date / 1000, 'unixepoch') BETWEEN '" & lstDate.Get(cbxDateBegin.SelectedIndex) & "' AND '" & lstDate.Get(cbxDateEnd.SelectedIndex) & "' ORDER BY date(Stock.Date / 1000, 'unixepoch') ASC"
			Else
				Query = Query & " AND date(Stock.Date / 1000, 'unixepoch') BETWEEN '" & lstDate.Get(cbxDateEnd.SelectedIndex) & "' AND '" & lstDate.Get(cbxDateBegin.SelectedIndex) & "' ORDER BY date(Stock.Date / 1000, 'unixepoch') ASC"
			End If
		Else If SelectedDateBegin > 0 Then
			Query = Query & " AND date(Stock.Date / 1000, 'unixepoch') >= '" & lstDate.Get(cbxDateBegin.SelectedIndex) & "' ORDER BY date(Stock.Date / 1000, 'unixepoch') ASC"
		Else If SelectedDateEnd > 0 Then
			Query = Query & " AND date(Stock.Date / 1000, 'unixepoch') <= '" & lstDate.Get(cbxDateEnd.SelectedIndex) & "' ORDER BY date(Stock.Date / 1000, 'unixepoch') ASC"
		End If
	End If
	
	Return Query
End Sub