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

Ola

DEPRECATED: This project is based on a very old version of BANanoVueMaterial and is NO LONGER MAINTAINED!!!

HOWEVER, YOU CAN FOLLOW THIS THREAD ON CREATING AN EXPENSE TRACKER USING THE NEW VERSION,


PS: A lot has happened eversince this tutorial was written and a lot of changes to libraries took place. Please just start a new thread should you have any questions about it.

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


Download

ExpensesShow.png


The purpose of this tutorial is showing how easy it is to create a CRUD app using BANanoVueMaterial. In more detail, we will look at the following:

1. Creating Modal forms for data entry.
2. Data validation.
3. Inserting data to a table
4. Reading data from a table for edits
5. Deleting data from a table (with a confirm dialog box)

  1. Part 1 of this tutorial will deal specifically with data entry.
  2. Part 2 will deal with reporting, e.g. charts, exporting to excel etx.

Preparations

1. From the attachments here, create a mysql database named expensesshow and execute the scripts in the expensesshow.zip (sql) file.
2. Update the config.php file in the Files tab with your own MySQL db connection settings

B4X:
<?php
const DB_HOST = '127.0.0.1';
const DB_NAME = 'expensesshow';
const DB_USER = 'root';
const DB_PASS = '';
?>

3. Also in Main.AppStart, update the ServerIP with your own IP address.

B4X:
ServerIP = "127.0.0.1"

Some silly assumptions: You have downloaded the BANanoVueMaterial library from Github and copied it to your external libraries folder.
You have done some reading about BANanoVueMaterial from these posts.

1. Creating Records.

The most important screen for our Expense Tracker is the modExpenses module where one is able to capture expenses. This module however is dependent on 2 other modules, i.e. the expense type and the expense category.

CreatingET.gif


The expense types screen works exactly like the expense category screens. One defines the two records that they will use in capturing expensed.

When adding a new record, a dialog box with the expense type details is provided. One captures the expense type/category and then saves. Each id of the record here is auto-incremented. The app though does not check for duplicated expense types but one can add that when needed.

The code to execute the adding of records is sitting in pgIndex.

B4X:
Sub btnAddExpenseType_click(e As BANanoEvent)
    modExpenseTypes.Add
End Sub

This calls the .Add method in modExpenseTypes

B4X:
'a button to add a new record is clicked
Sub Add
    Mode = "A"
    mdlExpenseType.Container.SetDefaults
    mdlExpenseType.SetTitle("New Expense Type")
    vm.ShowDialog("mdlExpenseType")
End Sub

This sets the mode for the record, sets the default values for the container input elements,update the title of the dialog and then shows the dialog.

You will recall, that when the .Code method was called for modExpenseTypes, the code to built what is needed was executed.

1. A dialog for the input controls was created so that we can capture the expense types.

B4X:
'create a modal to add an expense type
    mdlExpenseType = vm.CreateDialog("mdlExpenseType",Me)
    mdlExpenseType.settitle("New Expense Type")
    mdlExpenseType.AddCancel("btnCancelExpenseType", "Cancel")
    mdlExpenseType.AddOK("btnSaveExpenseType", "Save")
    '
    Dim etID As VMInputControl = mdlExpenseType.Container.NewText("id","#","",False,"",0,"","",0)
    etID.SetVisible(False).SetInt
    Dim etText As VMInputControl = mdlExpenseType.Container.NewText("text","Name","",True,"",20,"","The expense type is required!",0)
    Dim etDescription As VMInputControl = mdlExpenseType.Container.NewTextArea("description","Description","",False,False,"",100,"","",0)


    mdlExpenseType.Container.AddControlS(etID, 1, 1, 12, 12, 12, 12)
    mdlExpenseType.Container.AddControlS(etText, 2, 1, 12, 12, 12, 12)
    mdlExpenseType.Container.AddControlS(etDescription, 3, 1, 12, 12, 12, 12)
    '
    mdlExpenseType.SetClickOutsideToClose(False)
    mdlExpenseType.SetCloseOnEsc(False)
    mdlExpenseType.SetWidth("500px")
    vm.AddDialog(mdlExpenseType)

and just before that, we added a table to list all our expense types.

2.
B4X:
'create a container to hold all contents
    Dim cont As VMContainer = vm.CreateContainer(name,Me)
    'hide this container
    cont.Hide
    'create 1 columns each spanning 12 columns
    cont.AddRows(1).AddColumns12
    '
    expenseType = vm.CreateGijgoTable("expensetype", "id", Me)
    expenseType.SetTitle("Expense Types")
    expenseType.AddColumn("text","Name")   ' 20
    expenseType.AddColumn("description","Description")   '100
    expenseType.AddEditTrash
    expenseType.autoLoad = True
    expenseType.SetDataSource(Array())
    cont.AddComponent(1,1, expenseType.tostring)
    'add container to the page content
    vm.AddContainer(cont)

As the table configuration is new, we define a new gijgo table and then add columns to it. We only add the columns we want to show on the grid. We also add action buttons for Add and Delete.

Each time we want to show the contents of the database table expensetypes, we call the .Refresh method.

B4X:
'load all existing expense types
Sub Refresh
    vm.pagepause
    Dim dbsql As BANanoMySQL
    dbsql.Initialize(Main.dbase, "expensetypes", "id")
    dbsql.SelectAll(Array("*"), Array("text"))
    dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
    dbsql.FromJSON
    If dbsql.OK Then
        expenseType.SetDataSource(dbsql.result)
        expenseType.refresh
    Else
        Log("modExpenseTypes.Refresh: Error - " & dbsql.error)
    End If
    vm.pageresume
End Sub

We open the MySQL database, select all records and sort them by id and then load the records to the grid. This also happens when we edit and update a record. The grid is refreshed.
 

Attachments

  • expensesshow.zip
    1 KB · Views: 427
Last edited:

Mashiane

Expert
Licensed User
Longtime User
Saving Records

Depending on the mode of the transaction. A record is either Created / Updated in the database table. Also the schema that is defined when we were creating the dialog box for data entry is crucial.

B4X:
Sub btnSaveExpenseType_click(e As BANanoEvent)
    'get the category
    Dim rec As Map = mdlExpenseType.Container.GetData
    'validate
    Dim bValid As Boolean = vm.validate(rec, mdlExpenseType.Container.Required)
    If bValid = False Then Return
            
    Dim dbsql As BANanoMySQL
    dbsql.Initialize(Main.dbase, "expensetypes", "id")
    dbsql.SchemaFromDesign(mdlExpenseType.Container)
    Select Case Mode
        Case "A"
            dbsql.RecordFromMap(rec)
            dbsql.Insert
            dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
            dbsql.FromJSON
            If dbsql.OK Then
                vm.ShowSnackBar("Expense Type added successfully!")
                vm.HideDialog("mdlExpenseType")
                Refresh
            Else
                Log("modExpenseTypes.btnSaveExpenseType_click: Error - " & dbsql.error)
                vm.ShowSnackBar(dbsql.error)
            End If
        Case "E"
            Dim sid As String = rec.Get("id")
            dbsql.RecordFromMap(rec)
            dbsql.Update(sid)
            dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
            dbsql.FromJSON
            If dbsql.OK Then
                vm.ShowSnackBar("Expense Type updated successfully!")
                vm.HideDialog("mdlExpenseType")
                Refresh
            Else
                Log("modExpenseTypes.btnSaveExpenseType_click: Error - " & dbsql.error)
                vm.ShowSnackBar(dbsql.error)
            End If
    End Select
End Sub

  1. We get the data from the container (depends on the input controls we added)
  2. We then validate the data. This picks us everything that we indicated is "required"
  3. We then open the underlying database to save the record.
 

Mashiane

Expert
Licensed User
Longtime User
Updating Existing Records

EditET.gif


To edit an existing record, one clicks the Edit button from the table actions. This reads the corresponding record and then displays the content in the dialog. The person then updates the record as needed and then saves it.

When we initialized our table, we created it with..

B4X:
expenseType = vm.CreateGijgoTable("expensetype", "id", Me)

So when this happens, 2 extra events are also created when we run .AddEditTrash. These are expensetype_edit and expensetype_delete.
expensetype_edit -
gets fired each time we click the edit button per row and
expensetype_delete - gets fired each time we click the delete button per row

So when we edit the record, we need to read the selected record from the event.

B4X:
'an edit button is clicked on the table
Sub expensetype_edit(e As BANanoEvent)
    'get the record corresponding to the row
    Dim rec As Map = expenseType.GetRecordFromEvent(e)
    Dim sid As String = rec.GetDefault("id","")
    If sid = "" Then Return
    'turn the mode to edit
    Mode = "E"
    'update the title of the modal dialog
    mdlExpenseType.SetTitle("Edit Expense Type")
    'set the default values for the modal
    mdlExpenseType.Container.setdefaults
    'update the states for the modeal
    vm.SetState(rec)
    'show the modal with new states
    vm.showdialog("mdlExpenseType")
End Sub

After reading the record from the event, we change the mode to "E", update the title of the dialog box and then update the state of the form input components. The update of the state automatically updates the v-model contents for each field in the map that is passed. We then show the modal.
 

Mashiane

Expert
Licensed User
Longtime User
Deleting existing records

We had indicated that the click event per row is expensetype_delete.

DeleteET.gif


When the delete button is clicked, we want a confirmation to appear asking the end user to confirm the delete. One of the things to note here is that the code in the modules available in the app is all added to the pgIndex code module. Thus the callback for the confirm dialog will be fired in pgIndex and NOT IN modExpenseTypes.

So, let's run the code for our delete.

B4X:
'table delete record is clicked
Sub expensetype_delete(e As BANanoEvent)
    'get the record for the row
    Dim rec As Map = expenseType.GetRecordFromEvent(e)
    Dim sid As String = rec.GetDefault("id","")
    Dim stext As String = rec.getdefault("text","")
    If sid = "" Then Return
    'save the category id to delete
    vm.SetStateSingle("expensetypeid", sid)
    'indicate confirm dialog
    vm.ShowConfirm("delete_expensetype", $"Confirm Delete: ${stext}"$, _
    "Are you sure that you want to delete this expense type. You will not be able to undo your actions. Continue?","Ok","Cancel")
End Sub

We get the record from the event. For our confirm dialog to function properly we do 1 thing. We save the id of the record to delete in a state. *THIS IS IMPORTANT.

For this case, we have used "expensetypeid". For our confirm dialog, we also give it a process name, e.g. "delete_expensetype". This is because we want to know which process fired the confirm dialog.

As indicated, the callback for the confirm dialog, being confirm_ok, will be in pgIndex. So when the user confirms by clicking OK on the confirm dialog, pgIndex.confirm_ok will be fired.

B4X:
Sub confirm_ok
    Dim sconfirm As String = vm.GetConfirm
    Select Case sconfirm
    Case "delete_expensetype"
        'get the expense type to be deleted
        Dim sid As String = vm.getstate("expensetypeid", "")
        If sid = "" Then Return
        'connect to the database
        Dim dbsql As BANanoMySQL
        dbsql.Initialize(Main.dbase, "expensetypes", "id")
        dbsql.Delete(sid)
        dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
        dbsql.FromJSON
        If dbsql.OK Then
            vm.ShowSnackBar("Expense Type deleted successfully!")
            'refresh the table listing
            modExpenseTypes.Refresh
        Else
            Log("phIndex.confirm_ok.delete_expensetype: Error - " & dbsql.error)
            vm.ShowSnackBar(dbsql.error)
        End If

When that happens, we run .GetConfirm so that we get the confirm dialog process name. Now we know that the process is "delete_expensetype".
We then read the expense type we want to delete and then ensure that this is deleted from the database. We also ensure we run modExpenseTypes.Refresh to ensure that the table is updated with the latest available records.

This in essence covers how CRUD is done with BANanoVueMaterial. Here as a backend we have used MySQL. A similar approach can be done with SQLite, BANanoSQL and also MSSQL.

This also coveres how one can use the Gijgo Table plugin for BANanoVueMaterial.

Hopefully you have enjoyed this!

Ta!
 

Mashiane

Expert
Licensed User
Longtime User
Capturing Expenses

ExpensesShow.png


When one captures expenses, they might also want to clone an existing record and then update that clone. For that we have added functionality to clone a record.

Thus when a record is selected in the table and clone is selected, the record is read from the db, the dialog is shown and then a user can update and save the expense as a new record.

NB: You will note here that a query with joins has been used to display the category and type.

B4X:
Sub expensetable_clone(e As BANanoEvent)
    'get the record corresponding to the row
    Dim rec As Map = expenses.GetRecordFromEvent(e)
    Dim sid As String = rec.GetDefault("id","")
    If sid = "" Then Return
    'turn the mode to edit
    Mode = "A"
    Dim dbsql As BANanoMySQL
    dbsql.Initialize(Main.dbase, "expenses", "id")
    dbsql.Read(sid)
    dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
    dbsql.FromJSON
    If dbsql.OK Then
        Dim rec As Map = dbsql.result.get(0)
        rec.put("id", Null)
        vm.CallMethod("LoadTypes")
        vm.CallMethod("LoadCategories")
        mdlExpenses.Container.SetDefaults
        mdlExpenses.SetTitle("New Expense")
        vm.SetState(rec)
        vm.ShowDialog("mdlExpenses")
    Else
        Log("modExpenses.expensetable_clone: Error - " & dbsql.error)
    End If
End Sub

NB: When adding / editing records, we load the Types and Categories by calling LoadTypes and LoadCategories. You will recall that when we defined the selects for the expense type and expense category, we defined them like this:

B4X:
Dim expense_category As VMInputControl = mdlExpenses.Container.NewSelect("expense_category","Category",True,False,"","categories","id","text","","The category should be specified!",0).SetInt
    Dim expense_type As VMInputControl = mdlExpenses.Container.NewSelect("expense_type","Type",True,False,"","types","id","text","","The type should be specified!",0).SetInt

  • For the expense category, the select should use the "categories" state, get the "id" and "text" fields to generate the select.
  • For the expense type, the select should use the "types" state, get the "id" and the "text" fields to generate the select.

What loadtypes does is to read all existing expense types, saves this as the "types" state so that it can be used for our dropbox:

B4X:
Sub LoadTypes
    vm.SetStateSingle("types", Array())
    Dim dbsql As BANanoMySQL
    dbsql.Initialize(Main.dbase, "expensetypes", "id")
    dbsql.SelectAll(Array("id","text"), Array("text"))
    dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
    dbsql.FromJSON
    If dbsql.OK Then
        vm.SetStateSingle("types", dbsql.result)
    End If
End Sub

Thats the same with LoadCategories. Its updates the "categories" state with a list of existing categories.

B4X:
Sub LoadCategories
    vm.SetStateSingle("categories", Array())
    Dim dbsql As BANanoMySQL
    dbsql.Initialize(Main.dbase, "expensecategories", "id")
    dbsql.SelectAll(Array("id","text"), Array("text"))
    dbsql.json = BANano.CallInlinePHPWait(dbsql.methodname, dbsql.Build)
    dbsql.FromJSON
    If dbsql.OK Then
        vm.SetStateSingle("categories", dbsql.result)
    End If   
End Sub

We need to ensure that we get the latest database records captured, thus the need to reload these from time to time.

With that said, you have seen just how a select / combo is loaded from a database.
 

Mashiane

Expert
Licensed User
Longtime User
We have added excel report functionality to report on the expenses.

Check this out...

 

serro efe

Member
Licensed User
Longtime User
Hi,
How safe is it to use the BANanoMySQL library.
Is it open to external threats? As far as I have seen, database names, table names, column names are added to app.js. Yes config.php provides password security, but it may not be good to reveal some things.
What do you think about this?
Thanks
Edit;
Or I'm new to web programming. Is this the way these things are generally done?
 
Last edited:

Mashiane

Expert
Licensed User
Longtime User
Hi,
How safe is it to use the BANanoMySQL library.
Is it open to external threats? As far as I have seen, database names, table names, column names are added to app.js. Yes config.php provides password security, but it may not be good to reveal some things.
What do you think about this?
Thanks
Edit;
Or I'm new to web programming. Is this the way these things are generally done?
Please next time start a new thread for your question. It helps with a lot of things.

BananoMySql uses php to speak to the database. There are ways to secure apps that use php and you need to implement those security measures specific to php.

You can even create stored procedures and then call them via php if you want your content to be hidden from app.js

As a bonus, banano comes with an obfuscated code base when you run in release mode. So as long as you secure your php code base for bananomysql, you are safe to that extent.

Apply best practice on your side.
 
Top