B4J Tutorial [BANanoVueMaterial] Creating Expenses.Show - a CRUD expense tracker with MySQL backend: Part 2

Ola

UPDATE 2020-05-19: Please use this library instead

Download

You can get Part 1 here.

In Part 1 of this tutorial we looked at how we could create, read, update and delete MySQL table records for our expenses, expense categories and expense types.

Part 2 will look at the first part of reporting: The Dashboad.

The Dashboard here provides one with a quick outline to see your expenditure performance and also functionality to add an expense.



We have the following chart information:

1. Budget (current month) - the total budget per category & expenses per category for the current month. This uses the current computer date to get the current year and current month.
2. Budget by Categories (Current Month) - this is an explosion of budget chart however now looking at each category differently.
3. Expenditure by Month (Current Year) - this is a sum of expenditure per month for the current year.
4. Expenditure by Categories (Current Year) - this explodes the expenditure for the year per category.

NB: As soon as you add a new expense in this screen, the charts are automatically updated using a v-model approach of the KickChart control.

 

[Mashiane]

Creating the Charts (exploring one of the charts)

To be able to create the charts, they need to be defined in Process Globals, for example:

Public expByMonth As VMChartKick

We then create a container to host the chart and we add this container to our grid RC location.

Sub AddExpenditureByMonthCurrentYear As VMContainer
    Dim bcont As VMContainer = vm.CreateContainer("budgetmonthcurryearcont", Me)
    bcont.SetElevation(2)
    bcont.SetPaddingAll("20px")
    bcont.AddRows(2).AddColumns12
    bcont.SetMargins("20px","20px",0,0)
    
    Dim lblx As VMLabel = vm.CreateLabel("lblx").SetH1.SetText("Expenditure by Month (Current Year)")
    bcont.AddComponent(1, 1, lblx.tostring)
    '
    expByMonth = vm.CreateChartKick("expByMonth", Me).SetColumnChart.SetStyleSingle("height", "345px")
    bcont.AddComponent(2,1, expByMonth.tostring)
    
    Return bcont
End Sub

Now we want to refresh our chart at runtime. We need to clear the chart up reseting its v-modal. This is done by:

expByMonth.Reset
    expByMonth.overwriteoptions = False
    expByMonth.Refresh

We want this chart to display all the months for the year from January - December, irrespective of whether each month has expenses or not.

Dim expensesm As Map = CreateMap()
    expensesm.Put("Jan", 0)
    expensesm.Put("Feb", 0)
    expensesm.Put("Mar", 0)
    expensesm.Put("Apr", 0)
    expensesm.Put("May", 0)
    expensesm.Put("Jun", 0)
    expensesm.Put("Jul", 0)
    expensesm.Put("Aug", 0)
    expensesm.Put("Sep", 0)
    expensesm.Put("Oct", 0)
    expensesm.Put("Nov", 0)
    expensesm.Put("Dec", 0)
    
    Dim expqry2 As String =  $"select sum(expense_amount) as amount, month(expense_date) as period from expenses where year(expense_date) = '${cYear}' group by month(expense_date)"$
    Dim cats3 As BANanoMySQL
    cats3.Initialize(Main.dbase, "expenses", "id")
    cats3.Execute(expqry2)
    cats3.json = BANano.CallInlinePHPWait(cats3.methodname, cats3.Build)
    cats3.FromJSON
    If cats3.OK Then
        For Each rec As Map In cats3.result
            Dim stext As String = rec.get("period")
            Dim sbudget As String = rec.get("amount")
            sbudget = BANano.parseFloat(sbudget)
            Select Case stext
            Case "1"
                expensesm.Put("Jan", sbudget)
            Case "2"
                expensesm.Put("Feb", sbudget)
            Case "3"
                expensesm.Put("Mar", sbudget)
            Case "4"
                expensesm.Put("Apr", sbudget)
            Case "5"
                expensesm.Put("May", sbudget)
            Case "6"
                expensesm.Put("Jun", sbudget)
            Case "7"
                expensesm.Put("Jul", sbudget)
            Case "8"
                expensesm.Put("Aug", sbudget)
            Case "9"
                expensesm.Put("Sep", sbudget)
            Case "10"
                expensesm.Put("Oct", sbudget)
            Case "11"
                expensesm.Put("Nov", sbudget)
            Case "12"
                expensesm.Put("Dec", sbudget)
            End Select
        Next
        For Each k As String In expensesm.Keys
            Dim v As String = expensesm.Get(k)
            v = BANano.parsefloat(v)
            expByMonth.AddXY(k,v)
        Next
        expByMonth.refresh
    Else
        Log("modDashboard.Refresh: Error - " & cats.error)
    End If

We then create a map to hold each month and then update this map and use .AddXY to update the chart and the later .Refresh it.

Instead of Mon(?) on the querystring we could also have used MonthName(?), its just a matter of preference. MonthName returns the full month name.

This produces the above chart based on avaible expenditure information. You can explore the rest of the code (and even tweak it to meet your needs)

Ta!

 

[Mashiane]

One of the interesting charts here is the one that shows the budget vs expenditure per category for the current year.

1. When capturing categories, the budget is indicated as a monthly budget.
2. When capturing expenses, one uses the date for the expense. Using SQL commands we are able to find the applicable year and month an expenditure is for.

In this case, we have sorted the categories starting with the one with the most budget, in this case, Shopping.

3. We first determine the budget series we need to add to the chart.

'get categories and budget for the month
    Dim cats As BANanoMySQL
    Dim catsm As Map = CreateMap()
    Dim expsm As Map = CreateMap()
    cats.Initialize(Main.dbase, "expensecategories", "id")
    cats.Execute("select * from expensecategories order by budget desc")
    cats.json = BANano.CallInlinePHPWait(cats.methodname, cats.Build)
    cats.FromJSON
    If cats.OK Then
        For Each rec As Map In cats.result
            Dim stext As String = rec.get("text")
            Dim sbudget As String = rec.get("budget")
            sbudget = BANano.parseFloat(sbudget)
            '
            catsm.put(stext, sbudget)
        Next
    Else
        Log("modDashboard.Refresh: Error - " & cats.error)
    End If

4. We then find the expenses for this year and this month and then group them by category and then add this series too. However a category might have/ might not have expenditure and we also want to show this picture.

'expenses for this month
    Dim expqry As String = $"select sum(expenses.expense_amount) as amount, expensecategories.text as expense_category from expenses, expensecategories where expenses.expense_category = expensecategories.id and month(expense_date) = '${cMonth}' and year(expense_date) = '${cYear}' group by expensecategories.text"$
    Dim cats1 As BANanoMySQL
    cats1.Initialize(Main.dbase, "expenses", "id")
    cats1.Execute(expqry)
    cats1.json = BANano.CallInlinePHPWait(cats1.methodname, cats1.Build)
    cats1.FromJSON
    If cats1.OK Then
        For Each rec As Map In cats1.result
            Dim stext As String = rec.get("expense_category")
            Dim sbudget As String = rec.get("amount")
            sbudget = BANano.parseFloat(sbudget)
            '
            expsm.put(stext, sbudget)
        Next
    Else
        Log("modDashboard.Refresh: Error - " & cats.error)
    End If
    'consolidate the two
    Dim ser1 As Map = CreateMap()
    For Each k As String In catsm.Keys
        Dim v As String = catsm.Get(k)
        v = BANano.parseFloat(v)
        ser1.Put(k, v)
    Next
    '
    Dim ser2 As Map = CreateMap()
    For Each k As String In catsm.Keys
        If expsm.ContainsKey(k) Then
            Dim v As String = expsm.Get(k)
            v = BANano.parsefloat(v)
            ser2.put(k, v)
        Else
            ser2.Put(k,0)
        End If
    Next
    budgetByCat.AddSeries("Budget","",False,ser1)
    budgetByCat.AddSeries("Expenses","",False,ser2)
    budgetByCat.Refresh

So we super-impose the expenditure per category for the year & month against the budget per category and get the chart.

Next we will look at the Stats reporting, soon!

 

[Mashiane]

There is one dashboard that we can add to be more informative about our expenditure. This uses a plugin called InfoBox.

Each time we refresh the dashboard, calculations are done and the values of the info boxes updated.

1. We add an info box to a grid location

allspent = vm.CreateInfoBox("allspent", Me).SetIcon("attach_money")
    allspent.SetFrom("0")
    allspent.SetTo("0")
    allspent.SetText("Overall Spent")
    allspent.SetIconBackgroundColor(vm.vue.Colors.green)
    allspent.SetHoverExpandEffect(True)
    cont.AddComponent(1,1,allspent.tostring)

We run a query from the db and then update the value:

Dim dball As BANanoMySQL
    dball.Initialize(Main.dbase, "expenses", "id")
    dball.Execute("select sum(expense_amount) as amount from expenses")
    dball.json = BANano.CallInlinePHPWait(dball.methodname, dball.Build)
    dball.FromJSON
    If dball.OK Then
        Dim rec As Map = dball.result.Get(0)
        Dim samount As String = rec.Get("amount")
        allspent.SetTo(samount)
        allspent.refresh
    End If

 

[Mashiane]

Ive received a request to add filters for the expenses so that one can filter by date and category. I decided to all add the expense type for an overall experience.

NB: Watch in HD



1. Step 1: Create the filter container and add it just above the table.

Sub ExpenseFilter As VMContainer
    Dim ec As VMContainer = vm.CreateContainer("ec", Me)
    '
    Dim dpstartdate As VMDateTimePicker = vm.NewDatePicker(Me, True, "dpstartdate", "startdate", "Start Date", True, "", "", "", 0).SetClearable(True)
    ec.AddControl(dpstartdate.DateTimePicker, dpstartdate.tostring, 1, 1, 0, 0, 0, 0, 12, 2, 2, 2)

    Dim dpfinishdate As VMDateTimePicker = vm.NewDatePicker(Me, True, "dpfinishdate", "finishdate", "Finish Date", True, "", "", "", 0).SetClearable(True)
    ec.AddControl(dpfinishdate.DateTimePicker, dpfinishdate.tostring, 1, 2, 0, 0, 0, 0, 12, 2, 2, 2)

    Dim cbocategory As VMSelect = vm.NewComboDataSource(Me, True, "cbocategory", "category", "Category", True, True, "", "categories", "id", "text", True, "", "", 0)
    cbocategory.SetSmallChips(True).SetClearable(True).SetDeletablechips(True)
    ec.AddControl(cbocategory.Combo, cbocategory.tostring, 1, 3, 0, 0, 0, 0, 12, 2, 2, 2)

    Dim cboexpensetype As VMSelect = vm.NewComboDataSource(Me, True, "cboexpensetype", "expensetype", "Type", True, True, "", "types", "id", "text", True, "", "", 0)
    cboexpensetype.setsmallchips(True).SetClearable(True).SetDeletablechips(True)
    ec.AddControl(cboexpensetype.Combo, cboexpensetype.tostring, 1, 4, 0, 0, 0, 0, 12, 2, 2, 2)

    Dim btnbtnApplyFilter As VMButton = vm.NewButton(Me, True, "btnApplyFilter", "Apply", True, False, False, True)
    btnbtnApplyFilter.SetTooltip("Apply filter")
    btnbtnApplyFilter.SetColorIntensity("green", "darken-1")
    ec.AddControl(btnbtnApplyFilter.Button, btnbtnApplyFilter.tostring, 1, 5, 0, 0, 0, 0, 12, 2, 2, 2)

    Dim btnbtnResetFilter As VMButton = vm.NewButton(Me, True, "btnResetFilter", "Reset", True, False, False, True)
    btnbtnResetFilter.SetTooltip("Reset filter")
    btnbtnResetFilter.SetColorIntensity("red", "darken-1")
    ec.AddControl(btnbtnResetFilter.Button, btnbtnResetFilter.tostring, 1, 6, 0, 0, 0, 0, 12, 2, 2, 2)

    Return ec
End Sub

2. When Apply is clicked, get the selected details and apply the filter.

'apply a filter to the records
Sub btnApplyFilter_click(e As BANanoEvent)
    Dim sstartdate As String = vm.getdata("startdate")
    Dim sfinishdate As String = vm.getdata("finishdate")
    Dim lcategory As List = vm.getdata("category")
    Dim lexpensetype As List = vm.getdata("expensetype")
    '
    If sstartdate = "" Then
        vm.ShowSnackBar("Start date should be specified!")
        Return
    End If
    '
    If sfinishdate = "" Then
        vm.ShowSnackBar("Finish date should be specified!")
        Return
    End If
    '
    If lcategory.size = 0 Then
        vm.ShowSnackBar("Category should be specified!")
        Return
    End If
    '
    If lexpensetype.size = 0 Then
        vm.ShowSnackBar("Expense type should be specified!")
        Return
    End If
    '
    Dim kc As List
    kc.initialize
    For Each rec As Map In lcategory
        Dim v As String = rec.Get("id")
        kc.add(v)
    Next
    'extract the keys
    Dim kt As List
    kt.initialize
    For Each rec As Map In lexpensetype
        Dim v As String = rec.Get("id")
        kt.add(v)
    Next
  
    ' join for query
    Dim scat As String = vm.Join(",", kc)
    Dim styp As String = vm.join(",", kt)
    '
    vm.pagepause
    Dim qry As String = "select expenses.id, expenses.expense_date, expenses.expense_description, expenses.expense_amount, expensecategories.text as expense_category,"
    qry = qry & "expensetypes.text As expense_type from expenses, expensecategories, expensetypes where expenses.expense_category = expensecategories.id and expenses.expense_type = "
    qry = qry & $"expensetypes.id and expenses.expense_date >= '${sstartdate}' and expenses.expense_date <= '${sfinishdate}' and expensecategories.id in (${scat}) and expensetypes.id in (${styp}) order by expenses.expense_date desc"$
    Log(qry)
    Dim dbsql As BANanoMySQL
    dbsql.Initialize(Main.dbase, "expenses", "id")
    dbsql.Execute(qry)
    dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
    dbsql.FromJSON
    If dbsql.OK Then
        expenses.SetDataSource(dbsql.result)
    Else
        Log("modExpenses.btnApplyFilter: Error - " & dbsql.error)
    End If
    vm.pageresume
End Sub

3. When reset is clicked, revert to the original list..

'reset the filter
Sub btnResetFilter_click(e As BANanoEvent)
    vm.pagepause
    Dim mp As Map = CreateMap()
    mp.put("startdate", Null)
    mp.put("finishdate", Null)
    mp.put("category", Array())
    mp.put("expensetype", Array())
    vm.setstate(mp)
    Refresh
    vm.pageresume
End Sub