B4J Tutorial [ABMaterial] Dashboard Creation with JustGage

Hi there

For this exercise I wanted to have a dashboard that will provide me with total Expenditure vs Budget as demonstrated in Figure 1 below. In this table, records are added that will have a budget and expenditure. The expenditure amounts are a sum up of records captured in another screen. This record copying and drill down (i.e master-detail) functionality was discussed in this article.

Figure 1

Dashboard.png

I had improved the JustGage custom component as discussed in more detail in this article and added this on top of my ABMTable at R2C2.

What this needed to do was refresh the dashboard each time a record is added, updated, cloned and deleted. To achieve this I had to ensure that the refresh method of the custom JustGage was linked to the ABMTable refresh method.

The expenditure and budget values in the dashboard justgage are based on the records captured thus I needed a way to read these details from the underlying database and then refresh the justgage.

My ConnectPage page created the grid, the dashboard, the search functionality and the table as defined here.

B4X:
Public Sub ConnectPage()
    'connect navigation bar
    ConnectNavigationBar
    Dim NowWH As String = ABM.GetBrowserWidthHeight(page)
    If NowWH <> "" And NowWH <> ";" Then
        Dim split() As String = Regex.Split(";", NowWH)
        Dim NewH As Int = split(1) - 350
        NewH = NewH / 50
        NewH = NewH / 5
        NewH = NewH * 5
        If NewH >= 10 Then
            MaxRows = NewH
        End If
    End If
    'add components for the page
    Dim txtSearchProcurementPlan As ABMInput
    txtSearchProcurementPlan.Initialize(page, "txtSearchProcurementPlan", ABM.INPUT_TEXT, "Search", False, "lightblue")
    page.Cell(3,1).AddComponent(txtSearchProcurementPlan)
    Dim btnSearchProcurementPlan As ABMButton
    btnSearchProcurementPlan.InitializeFloating(page, "btnSearchProcurementPlan", "mdi-action-search", "")
    page.Cell(3,2).AddComponent(btnSearchProcurementPlan)
    Dim ProcurementPlanPager As ABMPagination
    ProcurementPlanPager.Initialize(page, "ProcurementPlanPager", 10, True, True, "")
    ProcurementPlanPager.SetTotalNumberOfPages(10)
    page.Cell(4,1).AddComponent(ProcurementPlanPager)
    Dim lblProcurementPlandashboard As ABMLabel
    lblProcurementPlandashboard.Initialize(page, "lblProcurementPlandashboard", "{NBSP}", ABM.SIZE_PARAGRAPH, False, "")
    page.Cell(2,1).AddComponent(lblProcurementPlandashboard)
    jg2048.Initialize(page, "jg2048", 0,"Expenditure vs Budget","")
    jg2048.pointertoplength = -15
    jg2048.pointerbottomlength = 10
    jg2048.pointerbottomwidth = 12
    jg2048.pointercolor = fx.Colors.rgb(142,142,147)
    jg2048.pointerstroke = fx.colors.rgb(255,255,255)
    jg2048.pointerstrokewidth = 3
    jg2048.pointerstrokelinecap = jg2048.EnumPointerStrokeLineCap.isround
    jg2048.pointerstrokewidth = 3
    jg2048.levelcolor1 = fx.colors.rgb(161,136,127)
    jg2048.levelcolor2 = fx.colors.rgb(161,136,127)
    jg2048.levelcolor3 = fx.colors.rgb(161,136,127)
    jg2048.humanfriendlydecimal = 0
    jg2048.gaugewidthscale = 0.6
    jg2048.gaugecolor = fx.Colors.RGB(237,235,235)
    jg2048.labelfontcolor = fx.Colors.RGB(179,179,179)
    jg2048.shadowopacity = 0.2
    jg2048.shadowsize = 5
    jg2048.shadowverticaloffset = 3
    jg2048.startanimationtime = 700
    jg2048.startanimationtype = jg2048.EnumAnimationType.right
    jg2048.refreshanimationtime = 700
    jg2048.refreshanimationtype = jg2048.EnumAnimationType.right
    jg2048.donutstartangle = 90
    jg2048.valueminfontsize = 14
    jg2048.titleminfontsize = 10
    jg2048.labelminfontsize = 10
    jg2048.minlabelminfontsize = 10
    jg2048.maxlabelminfontsize = 10
    jg2048.hidevalue = False
    jg2048.hideminmax = False
    jg2048.titlefontcolor = fx.Colors.rgb(153,153,153)
    jg2048.hideinnershadow = False
    jg2048.humanfriendly = True
    jg2048.nogradient = False
    jg2048.donut = False
    jg2048.relativegaugesize = True
    jg2048.counter = True
    jg2048.decimals = 0
    jg2048.formatnumber = True
    jg2048.pointer = False
    jg2048.titleposition = jg2048.EnumTitlePosition.above
    jg2048.valuefontcolor = fx.Colors.black
    jg2048.minvalue = 0
    jg2048.maxvalue = 100
    jg2048.reverse = False
    RefreshOnLoad_jg2048
    page.Cell(2,2).AddComponent(jg2048.ABMComp)
    Dim tblProcPlan As ABMTable
    tblProcPlan.Initialize(page, "tblProcPlan", False, False, True, "tblTheme")
    tblProcPlan.IsBordered = True
    tblProcPlan.IsResponsive = True
    tblProcPlan.IgnoreFormattingCodes = False
    tblProcPlan.IsTextSelectable = True
    tblProcPlan.SetHeaders(Array As String("ID", "Seq Number", "Fin Year", "Programme", "Class", "Project", "Resource", "Status", "Budget", "Expenditure", "Advert Date", "Completion Date", "Comment", "Edit", "Payments", "Copy", "Delete"))
    tblProcPlan.SetHeaderThemes(Array As String("bg", "bg", "bg", "bg", "bg", "bg", "bg", "bg", "bgr", "bgr", "bg", "bg", "bg", "bgc", "bgc", "bgc", "bgc"))
    tblProcPlan.SetHeaderHeights(Array As Int(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 48, 48, 48, 48))
    tblProcPlan.SetColumnVisible(Array As Boolean(False, True, True, True, True, True, True, True, True, True, True, True, False, True, True, True, True))
    tblProcPlan.SetColumnSortable(Array As Boolean(False, True, True, True, True, True, True, True, True, True, True, False, False, False, False, False, False))
    tblProcPlan.SetColumnDataFields(Array As String("id", "SeqNumber", "FinYear", "Programme", "ClassOfGoods", "Description", "Resource", "Status", "Budget", "Expenditure", "AdvertDate", "CompletionDate", "Comment", "", "", "", ""))
    page.Cell(5,1).AddComponent(tblProcPlan)
    Dim frmProcPlanNewRecord As ABMActionButton
    frmProcPlanNewRecord.Initialize(page, "frmProcPlanNewRecord", "mdi-content-add", "", "bigblue")
    frmProcPlanNewRecord.MainButton.Size = ABM.BUTTONSIZE_LARGE
    page.AddActionButton(frmProcPlanNewRecord)
    AdminAccess
    page.Refresh ' IMPORTANT
    ' NEW, because we use ShowLoaderType=ABM.LOADER_TYPE_MANUAL
    page.FinishedLoading 'IMPORTANT
    page.RestoreNavigationBarPosition
    LoadProcurementPlan(1)
End Sub

As you might have noticed here I added a {NBSP} on R2C1 instead of cell offsets to ensure that the dashboard sits on the correct position. This is just my own safety net.

My ABMTable Refresh method call as defined below includes also the JustGage refresh method. This ensured that whatever table functionality like adding, updating, cloning and deleting the records was also linked to the dashboard refresh.
 

Mashiane

Expert
Licensed User
Longtime User
My ABMTable refresh method...

B4X:
Private Sub LoadProcurementPlan(fromPage As Int)
    'Lets get the component from the page.
    Dim tblProcPlan As ABMTable = page.Component("tblProcPlan")
    CurrentLabels.Initialize
    CurrentValues.Initialize
    'Define list to store the results of the query
    Dim results As List
    Dim resCnt As Int
    Dim resTot As Int
    Dim resMap As Map
    Dim sqlQry As String
    'Read arguments from LocalStorage (if any)
    Dim id As String = ABMShared.SessionStorageRead(page, "ProcurementPlanid")
    'Get the record linked to this record id
    Dim RecordJSON As String = ABMShared.SessionStorageRead(page, id)
    'Convert this record to a map from json
    Dim RecordMap As Map = ABMShared.Json2Map(RecordJSON)
    page.Pause
    'Let's define the qry string
    sqlQry = "select * from ProcurementPlan order by SeqNumber " & Filter & " " & LastSort & " LIMIT " & ((fromPage - 1) * 10) & ", 10"
    'Get connection from current pool if MySQL/MSSQL
    Dim SQL As SQL = ABMShared.SQLGet
    'Get the number of records
    Dim SQL_str As String
    SQL_str = "Select Count(id) As IDS FROM ProcurementPlan " & FilterCount
    Dim NumRecords As Int = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Null)
    'Get the records as a list of maps from the db
    results = ABMShared.SQLExecuteMaps(SQL, sqlQry, Null)
    'Close the connection to the database
    ABMShared.SQLClose(SQL)
    If results.Size = 0 And fromPage > 1 Then
        'we are on a page without any lines
        fromPage = fromPage - 1
        LoadProcurementPlan(fromPage)
        Return
    End If
    tblProcPlan.Clear
    'Loop throught each record read and process it
    resTot = results.size - 1
    For resCnt = 0 To resTot
        'Get the record map
        resMap = results.get(resCnt)
        'Update each table row
        Dim rCellValues As List
        Dim rCellThemes As List
        rCellValues.Initialize
        rCellThemes.Initialize
        Dim id As String = resMap.GetDefault("id", "")
        If id = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(id)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim SeqNumber As String = resMap.GetDefault("seqnumber", "1")
        If SeqNumber = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(SeqNumber)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim FinYear As String = resMap.GetDefault("finyear", "2016/17")
        If FinYear = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(FinYear)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim Programme As String = resMap.GetDefault("programme", "")
        ABMShared.SessionStorageSave(page, "programme", Programme)
        If Programme = "" Then
            rCellValues.Add("{NBSP}")
        Else
            Dim SQL_str As String
            SQL_str = "Select Number As Outcome FROM ProgrammesSet Where Id = ?"
            Programme = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(Programme))
            rCellValues.Add(Programme)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim ClassOfGoods As String = resMap.GetDefault("classofgoods", "")
        ABMShared.SessionStorageSave(page, "classofgoods", ClassOfGoods)
        If ClassOfGoods = "" Then
            rCellValues.Add("{NBSP}")
        Else
            Dim SQL_str As String
            SQL_str = "Select Description As Outcome FROM ClassOfGoods Where id = ?"
            ClassOfGoods = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(ClassOfGoods))
            rCellValues.Add(ClassOfGoods)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim Description As String = resMap.GetDefault("description", "")
        If Description = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(Description)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim Resource As String = resMap.GetDefault("resource", "")
        ABMShared.SessionStorageSave(page, "resource", Resource)
        If Resource = "" Then
            rCellValues.Add("{NBSP}")
        Else
            Dim SQL_str As String
            SQL_str = "Select ResourceName As Outcome FROM Resources Where id = ?"
            Resource = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(Resource))
            rCellValues.Add(Resource)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim Status As String = resMap.GetDefault("status", "")
        ABMShared.SessionStorageSave(page, "status", Status)
        If Status = "" Then
            rCellValues.Add("{NBSP}")
        Else
            Dim SQL_str As String
            SQL_str = "Select Description As Outcome FROM ProcurementStatus Where id = ?"
            Status = ABMShared.SQLSelectSingleResult(SQL, SQL_str, Array As String(Status))
            rCellValues.Add(Status)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim Budget As String = resMap.GetDefault("budget", "0.00")
        If Budget = "" Then
            rCellValues.Add("{NBSP}")
        Else
            '*** Start output format
            Budget = ABMShared.makemoney(Budget)
            '*** End output format
            rCellValues.Add(Budget)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolorr")
        Dim Expenditure As String = resMap.GetDefault("expenditure", "0.00")
        If Expenditure = "" Then
            rCellValues.Add("{NBSP}")
        Else
            '*** Start output format
            Expenditure = ABMShared.makemoney(Expenditure)
            '*** End output format
            rCellValues.Add(Expenditure)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolorr")
        Dim AdvertDate As String = resMap.GetDefault("advertdate", "DateTime.Now")
        If AdvertDate = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(AdvertDate)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim CompletionDate As String = resMap.GetDefault("completiondate", "DateTime.Now")
        If CompletionDate = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(CompletionDate)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim Comment As String = resMap.GetDefault("comment", "")
        If Comment = "" Then
            rCellValues.Add("{NBSP}")
        Else
            rCellValues.Add(Comment)
        End If
        'Add theme to the cell
        rCellThemes.Add("nocolor")
        Dim btnEditProcurementPlan As ABMButton
        btnEditProcurementPlan.InitializeFloating(page, "btnEditProcurementPlan", "mdi-action-visibility", "")
        rCellValues.Add(btnEditProcurementPlan)
        rCellThemes.Add("openedit")
        Dim btnDetailProcurementPlan As ABMButton
        btnDetailProcurementPlan.InitializeFloating(page, "btnDetailProcurementPlan", "mdi-action-list", "")
        rCellValues.Add(btnDetailProcurementPlan)
        rCellThemes.Add("openedit")
        Dim btnCopyProcurementPlan As ABMButton
        btnCopyProcurementPlan.InitializeFloating(page, "btnCopyProcurementPlan", "mdi-content-content-copy", "")
        rCellValues.Add(btnCopyProcurementPlan)
        rCellThemes.Add("openedit")
        Dim btnDeleteProcurementPlan As ABMButton
        btnDeleteProcurementPlan.InitializeFloating(page, "btnDeleteProcurementPlan", "mdi-action-delete", "")
        rCellValues.Add(btnDeleteProcurementPlan)
        rCellThemes.Add("openedit")
        'Add the row to the table
        tblProcPlan.AddRow("id" & resCnt, rCellValues)
        tblProcPlan.SetRowThemes(rCellThemes)
    Next
    'Update the paginating component
    Dim pager As ABMPagination = page.Component("ProcurementPlanPager")
    If (NumRecords Mod MaxRows > 0) Or (NumRecords = 0) Then
        NumRecords = NumRecords/MaxRows + 1
    Else
        NumRecords = NumRecords/MaxRows
    End If
    pager.SetTotalNumberOfPages(NumRecords)
    pager.SetActivePage(fromPage)
    pager.Refresh
    tblProcPlan.Refresh
    page.Resume
    RefreshOnLoad_jg2048
End Sub

The table is cleared, its records and related records are read from the respective tables and ABMTable updated. Now lets look at the RefreshOnLoad_jg2048 method.
 

Mashiane

Expert
Licensed User
Longtime User
Refreshing the dashboard justgage is only about updating the value and the max values. These values depend on the captured expenditure and captured budget per record in the ABMTable.

B4X:
Private Sub RefreshOnLoad_jg2048()
    'Read arguments from LocalStorage (if any)
    'Add a spinner to the page
    page.Pause
    'Get connection from current pool if MySQL/MSSQL or SQLite
    Dim jSQL As SQL = ABMShared.SQLGet
    'Get the value
    Dim jgValue As Double = ABMShared.SQLSelectSingleResult(jSQL, "select sum(expenditure) from ProcurementPlan", Null)
    'Get the total
    Dim jgMaxValue As Double = ABMShared.SQLSelectSingleResult(jSQL, "select sum(budget) from ProcurementPlan", Null)
    'Close the connection to the database
    ABMShared.SQLClose(jSQL)
    jg2048.Value = jgValue
    jg2048.MaxValue = jgMaxValue
    jg2048.ABMComp.Refresh
    page.resume
End Sub

So this method does basically that. The value and maxvalues read record from the records in the table and then the JustGage is refreshed. As any of the properties of the JustGage can be changed before a refresh, I had tweaked it a little bit to ensure that such happens.

This dashboard gage does not have a pointer and the values have K and M on them, this was achieved by turning humanfriendly = true.

Ta!

That's all folks...
 

incendio

Well-Known Member
Licensed User
Longtime User
Your UI is beautiful while mine is horrible. :(

How do you make line under the row in table?
 
Last edited:

incendio

Well-Known Member
Licensed User
Longtime User
This is a property of the table:

B4X:
Dim myTbl As ABMTable
myTbl.Initialize(page, "myTbl", False, False, True, "tblTheme")
myTbl.IsBordered = True
...
I have used that, but see no line, perhaps it was blend with my table rows color, with is also grey. Will check again, thanks for your help.
 
Top