B4J Tutorial [ABMaterial] Creating Dynamic Charts from Database Records At Runtime

Hi there

My previous article, Creating Dynamic ABMSideBarItems from Database Records at Runtime, started this real world situation.

My next challenge was, when each ABMSideBarItem is selected, get a record from the database and display the respective chart dynamically. The chart should be downloadable. Phew...

So, again, fired up Evolus Pencil and did a simple sketch of what I needed to do as per Figure 1

Figure 1

DynamicCharts0.png

For this task I decided on a CustomChart component. I had done CustomJQPlot, my first try at custom components, that went very well, but then, it got to be a turn off as I just could'nt get the legends to work properly for me. I could have used the built in ABMChart component, but my mind has not registered its functionality yet, for some reason my head says its complex, just like when I started with the ABMGrid. For a long while I avoided them because they seemed complex for my head, then boom, gave myself time, researched, analysed them and amazingly, easy peasy, as long as you first designed on paper. :mad: The ABMGridBuilder is thee thing now. :):):)

Anyway, like I said, I decided on a CustomChart. I had run into Syncfusion a couple of years back and never quiet got to use anything they have, so I thought, why not, their components look nice. So I started a quest to build the SyncfusionChart custom component for ABMaterial, herein attached.

At the end of the day, for each of my chart I needed something like this... all based on database records dynamically.

Figure 2

DynamicCharts.png


Figure 3

DynamicCharts1.png


Figure 4

DynamicCharts2.png


Figure 5


PercentageChange.png


The charts also dynamically resize themselves when the device orientation is changed and I was able to have dynamic labels based on db records (figure 4) and also static ones (figure 2 & 3).

There is a trick here that the ABMaterial creator indicated when it comes to Custom Components. Changing the internal runnings at runtime, define the component in your Class_Globals. So I defined my chart in the Class_Globals like this...

B4X:
Dim chart1 As SyncfusionChart

This nicely enables you to access the chart anywhere in your code as

the Dim chart1 as SyncFusionChart = page.Component("SyncfusionChart"), didnt work for me, when I tried to access the chart to change anything about it at runtime.

As per point 1 of my sketch, when each ABMSideBarItem is selected, I need a chart to be displayed, so...

B4X:
Public Sub Page_NavigationbarClicked(Action As String, Value As String)
    page.SaveNavigationBarPosition
    If Action = "LogOff" Then
        ABMShared.LogOff(page)
        Return
    End If
    Select Case Action.ToLowerCase
    Case Else
        ABMShared.SessionStorageSave(page, "action", "edit")
        ABMShared.SessionStorageSave(page, "id", Action)
        ABMShared.SessionStorageSave(page, "Year", Action)
        ABMShared.NavigateToPage(ws, ABMPageId, "../frmIYMPercentageExpenditurePerProgramme/frmIYMPercentageExpenditurePerProgramme.html")
        Return
    Case "downloadchart"
        ExecuteDownloadChart
        Return
    Case "goback"
        ExecuteGoBack
        Return
    End Select
    ABMShared.NavigateToPage(ws, ABMPageId, Value)
End Sub

This also uses the "Else" case as explained in my previous post. When the item is selected, save the relevant information and navigate to the same current page. Navigating to the current open page will re-run ConnectPage, so in my ConnectPage method, I added the magic to draw the chart at runtime.

B4X:
Public Sub ConnectPage()
    'connect navigation bar
    ConnectNavigationBar
    'add components for the page
    chart1.Initialize(page, "chart1")
    chart1.Title = "In Year Monitoring"
    chart1.XAxisTitle = "Month"
    chart1.YAxisTitle = "Percentage Expenditure"
    chart1.YAxisLabelFormat = "{value}%"
    chart1.YAxisLabelFormatLocale = False
    chart1.LegendTitle = "Percentage Expenditure Per Programme Per Month"
    chart1.LegendVisible = True
    chart1.LegendPosition = chart1.EnumLegendPosition.bottom
    chart1.EnableZooming = False
    chart1.enableCanvasRendering = False
    chart1.toolTipInitialize = False
    chart1.SubTitle = "Year"
    chart1.CommonSeriesOptions.markershape = "circle"
    chart1.CommonSeriesOptions.stype = chart1.EnumType.spline
    chart1.CommonSeriesOptions.FillColor = "lightblue"
    chart1.CommonSeriesOptions.EnableAnimation = True
    chart1.CommonSeriesOptions.markerdatalabelsvisible = False
    chart1.CommonSeriesOptions.MarkerVisible = True
    chart1.CommonSeriesOptions.ToolTipVisible = True
    chart1.CommonSeriesOptions.Width = 3
    RefreshOnLoad_chart1(chart1)
    page.Cell(2,1).AddComponent(chart1.ABMComp)
    AdminAccess
    page.Refresh ' IMPORTANT
    ' NEW, because we use ShowLoaderType=ABM.LOADER_TYPE_MANUAL
    page.FinishedLoading 'IMPORTANT
    page.RestoreNavigationBarPosition
End Sub

From what you see at first look above, there seems to be no code that refreshes the chart, well, there is. The crux of this piece of code is the RefreshOnLoad_chart1(chart1) call. I decided to pass the chart variable because you can have many chart components on your page, but then again, the RefreshOnLoad_chart1 already says which chart to be refreshed. Ok, let's continue, semantics.

The Refresh method is supposed to get the record to chart from the db and plot it. Let's take a deeper look into it.

B4X:
Private Sub RefreshOnLoad_chart1(ochart1 As SyncfusionChart)
    '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
    'Read arguments from LocalStorage (if any)
    Dim Year As String = ABMShared.SessionStorageRead(page, "Year")
    'The record has session storage, process them...
    'Get the current record id from the session
    Dim RecordID As String = ABMShared.SessionStorageRead(page, "id")
    'A different primary key has been specified.
    RecordID = ABMShared.SessionStorageRead(page, "Year")
    'Get the record linked to this record id
    Dim RecordJSON As String = ABMShared.SessionStorageRead(page, RecordID)
    'Convert this record to a map from json
    Dim RecordMap As Map = ABMShared.Json2Map(RecordJSON)
    Dim subTitle As StringBuilder
    subTitle.Initialize
    Dim Year As String = RecordMap.getDefault("year","")
    subTitle.Append(Year)
    subTitle.append(" ")
    ochart1.SubTitle = subTitle.ToString.Trim
    page.Pause
    'Get connection from current pool if MySQL/MSSQL
    Dim SQL As SQL = ABMShared.SQLGet
    'Get the records as a list of maps from the db
    results = ABMShared.SQLExecuteMaps(SQL,"select IYMAnalysisSet.*,[year],ProgrammeName from IYMAnalysisSet join ProgrammesSet on IYMAnalysisSet.IYMAnalysis_Programmes = ProgrammesSet.id where IYMAnalysisSet.Year = ?", Array As String(Year))
    'Close the connection to the database
    ABMShared.SQLClose(SQL)
    '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)
        resMap = IYMAnalysisSet_Compute(resMap)
        'create the series Actual
        Dim ser01 As SeriesObj
        ochart1.InitializeSeries(ser01)
        ser01.stype = ochart1.EnumType.line
        ser01.name = resMap.GetDefault("programmename","")
        ser01.tooltipvisible = True
        ser01.width = 3
        ser01.EnableAnimation = True
        ser01.markershape = "circle"
        ser01.MarkerVisible = True
        ser01.markerdatalabelsvisible = False
        'Read the fields to draw...
        Dim AprPerc As String = resMap.GetDefault("aprperc", "0")
        Dim MayPerc As String = resMap.GetDefault("mayperc", "0")
        Dim JunPerc As String = resMap.GetDefault("junperc", "0")
        Dim JulPerc As String = resMap.GetDefault("julperc", "0")
        Dim AugPerc As String = resMap.GetDefault("augperc", "0")
        Dim SepPerc As String = resMap.GetDefault("sepperc", "0")
        Dim OctPerc As String = resMap.GetDefault("octperc", "0")
        Dim NovPerc As String = resMap.GetDefault("novperc", "0")
        Dim DecPerc As String = resMap.GetDefault("decperc", "0")
        Dim JanPerc As String = resMap.GetDefault("janperc", "0")
        Dim FebPerc As String = resMap.GetDefault("febperc", "0")
        Dim MarPerc As String = resMap.GetDefault("marperc", "0")
        'Add data to the chart
        ochart1.AddXY(ser01,"Apr",AprPerc)
        ochart1.AddXY(ser01,"May",MayPerc)
        ochart1.AddXY(ser01,"Jun",JunPerc)
        ochart1.AddXY(ser01,"Jul",JulPerc)
        ochart1.AddXY(ser01,"Aug",AugPerc)
        ochart1.AddXY(ser01,"Sep",SepPerc)
        ochart1.AddXY(ser01,"Oct",OctPerc)
        ochart1.AddXY(ser01,"Nov",NovPerc)
        ochart1.AddXY(ser01,"Dec",DecPerc)
        ochart1.AddXY(ser01,"Jan",JanPerc)
        ochart1.AddXY(ser01,"Feb",FebPerc)
        ochart1.AddXY(ser01,"Mar",MarPerc)
        ochart1.AddSeries(ser01)
    Next
    page.Resume
End Sub

This produces something like this...

Figure 6

PercentageExpenditure.png


The JavaScript for this generated by the SyncfusionChart element is like this...

B4X:
$("#chart1").ejChart({
primaryYAxis:{
labelFormat: '{value}%',
title: { text: 'Percentage Expenditure'},
},
primaryXAxis:{
title: { text: 'Month'},
},
commonSeriesOptions: {
tooltip: {visible: true},
enableAnimation: true,
type: 'spline',
marker: {visible: true,
shape: 'circle',
size: { height: 10, Width: 10 },
},
},
title: { text: 'In Year Monitoring',subTitle: { text: '2015/16', textAlignment: 'center'},},
series: [{points: [{x: 'Apr', y: 0},{x: 'May', y: 109},{x: 'Jun', y: 125},{x: 'Jul', y: 100},{x: 'Aug', y: 133},{x: 'Sep', y: 113},{x: 'Oct', y: 123},{x: 'Nov', y: 89},{x: 'Dec', y: 101},{x: 'Jan', y: 83},{x: 'Feb', y: 109},{x: 'Mar', y: 0}],name: 'Administration',type: 'line',tooltip: {visible: true},enableAnimation: true,marker: {visible: true,shape: 'circle',size: { height: 10, Width: 10 },}},

{points: [{x: 'Apr', y: 0},{x: 'May', y: 100},{x: 'Jun', y: 83},{x: 'Jul', y: 115},{x: 'Aug', y: 60},{x: 'Sep', y: 100},{x: 'Oct', y: 86},{x: 'Nov', y: 117},{x: 'Dec', y: 126},{x: 'Jan', y: 60},{x: 'Feb', y: 66},{x: 'Mar', y: 0}],name: 'Public Works Infrastructure',type: 'line',tooltip: {visible: true},enableAnimation: true,marker: {visible: true,shape: 'circle',size: { height: 10, Width: 10 },}},

{points: [{x: 'Apr', y: 0},{x: 'May', y: 62},{x: 'Jun', y: 106},{x: 'Jul', y: 113},{x: 'Aug', y: 103},{x: 'Sep', y: 77},{x: 'Oct', y: 64},{x: 'Nov', y: 81},{x: 'Dec', y: 157},{x: 'Jan', y: 40},{x: 'Feb', y: 101},{x: 'Mar', y: 0}],name: 'Transport Infrastructure',type: 'line',tooltip: {visible: true},enableAnimation: true,marker: {visible: true,shape: 'circle',size: { height: 10, Width: 10 },}},

{points: [{x: 'Apr', y: 0},{x: 'May', y: 99},{x: 'Jun', y: 94},{x: 'Jul', y: 118},{x: 'Aug', y: 98},{x: 'Sep', y: 103},{x: 'Oct', y: 103},{x: 'Nov', y: 91},{x: 'Dec', y: 103},{x: 'Jan', y: 45},{x: 'Feb', y: 76},{x: 'Mar', y: 0}],name: 'Expanded Public Works Programme',type: 'line',tooltip: {visible: true},enableAnimation: true,marker: {visible: true,shape: 'circle',size: { height: 10, Width: 10 },}},

{points: [{x: 'Apr', y: 0},{x: 'May', y: 86},{x: 'Jun', y: 101},{x: 'Jul', y: 113},{x: 'Aug', y: 91},{x: 'Sep', y: 93},{x: 'Oct', y: 80},{x: 'Nov', y: 92},{x: 'Dec', y: 134},{x: 'Jan', y: 50},{x: 'Feb', y: 89},{x: 'Mar', y: 0}],name: 'Totals',type: 'line',tooltip: {visible: true},enableAnimation: true,marker: {visible: true,shape: 'circle',size: { height: 10, Width: 10 },}},
],
legend: {
visible: true,
position: 'bottom',
title: {
text: "Percentage Expenditure Per Programme Per Month",
},
},
isResponsive: true,
exporting: { type: 'png', mode: 'client', fileName: 'ChartSnapshot' }
});

Now for the last part, download the chart...

From the above code, I defined that the chart should be png format, must be run at client side and the file name will be chartsnapshot.

I added a download button to my navigation bar, that when clicked should download the chart.

B4X:
Public Sub ExecuteDownloadChart()
    chart1.Download(page)
End Sub

Inside the SyncfusionChart component, I added a Download method, lets look into that.

B4X:
Sub Download(InternalPage As ABMPage)
    Dim sb As String
    sb = $"var chart = $("#${iID}").ejChart("instance");
           chart.model.enableCanvasRendering = true;
           chart.redraw();
           var canvas = chart.export();
           var lnk = canvas.toDataURL("image/png");
           $("#${iID}").attr('href', lnk);
           $("#${iID}").attr('download', ${iID}.png);
           var a = document.createElement("a");
             a.download = "${iID}.png";
           a.href = lnk;
           a.click();
           delete a;
           chart.model.enableCanvasRendering = false;
           chart.redraw();"$
    InternalPage.ws.Eval(sb, Array As Object(ABMComp.ID))
End Sub

The trick to the download is enabling the canvas element of the chart to come alive. I switch this on and refresh the chart and turn it off again. So bit by bit.

1. Get the chart instance
2. Enable CanvasRendering
3. Redraw the chart
4. Export the chart
5. Get the toDataURL canvas element (this is what will be downloaded)
6. Create an anchor within the document
7. Set the href of the anchor to be link of the image
8. Execute a click method in the anchor
9. Delete the anchor (it was temporal afterall just for the download)
10. Turn off canvas rendering on the chart and
11. Redraw the chart.

The download has been working fine on Google Chrome, other browsers, dololo (i.e. nada)

Project Dependencies:

Add ej.theme.css to your custom css folder
Add ej.web.all.js contents to your custom js folder

PS: ABMSideBarItem Elements

For my case in this example, I just loaded years in my sidebaritems like this..

B4X:
Private Sub RefreshOnLoad_programme()
    'We will create each side bar needed on the load
    '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 sTitles As StringBuilder
    Dim sDescriptions As StringBuilder
    'We have titles, description and image
    'variable to hold the primary key
    Dim strYear As String
    'variables to title fields
    Dim strYear As String
    page.Pause
    'Get connection from current pool if MySQL/MSSQL
    Dim SQL As SQL = ABMShared.SQLGet
    'Get the records as a list of maps from the db
    results = ABMShared.SQLExecuteMaps(SQL,"select distinct Year from IYMAnalysisSet order by Year Desc", Null)
    'Close the connection to the database
    ABMShared.SQLClose(SQL)
    'Loop throught each record read and process it
    resTot = results.size - 1
    For resCnt = 0 To resTot
        'Initialize the titles and descriptions and define them
        sTitles.Initialize
        sDescriptions.Initialize
        'Get the record map
        resMap = results.get(resCnt)
        'process the primary key fields
        strYear = resMap.get("year")
        'Save record offline
        Dim resJSON As String = ABMShared.Map2Json(resMap)
        ABMShared.SessionStorageSave(page, strYear, resJSON)
        strYear = strYear.Replace(CRLF,"{BR}")
        'process the title fields
        strYear = resMap.get("year")
        strYear = strYear.Replace(CRLF,"{BR}")
        sTitles.Append(strYear)
        'add the item to the ABMSideBarItem
        page.NavigationBar.AddSideBarComponent(strYear, ABMShared.BuildSideBarComponent(page, strYear, "../images/trends.png", sTitles.ToString, sDescriptions.ToString),"../frmIYMPercentageExpenditurePerProgramme/frmIYMPercentageExpenditurePerProgramme.html")
        page.NavigationBar.AddSideBarDivider("")
    Next
    page.Resume
End Sub

That's all Folks
 

Attachments

  • SyncfusionChart.bas
    10.9 KB · Views: 320
  • ej.theme.min.zip
    33.3 KB · Views: 310

Mashiane

Expert
Licensed User
hi @Mashiane The JavaScript for this generated by the SyncfusionChart element is like this ,but chart no display
please help thank youView attachment 53830
Please use the latest MyMaterial.Show, it has this component built into it, you can also PM me your project database I will run it and check for you.
 

joulongleu

Active Member
Licensed User
chart is ouptput but XAsix is more 2-10 is error like this,use the latest MyMaterial.Show

upload_2017-3-17_15-9-16.png
 

Attachments

  • upload_2017-3-17_15-4-53.png
    upload_2017-3-17_15-4-53.png
    50.8 KB · Views: 236

joulongleu

Active Member
Licensed User
Hi @Mashiane zip project db and like this ,Thank You

Public Sub ConnectPage()
chart1.Initialize(page, "chart1")
chart1.XAxisTitle = "106年統計 x"
chart1.YAxisTitle = "106y"
chart1.YAxisLabelFormat = "{value}%"
chart1.YAxisLabelFormatLocale = True
chart1.LegendVisible = True
chart1.LegendPosition = chart1.EnumLegendPosition.top
chart1.EnableZooming = False
chart1.enableCanvasRendering = False
chart1.toolTipInitialize = False
chart1.CommonSeriesOptions.markershape = "circle"
chart1.CommonSeriesOptions.stype = chart1.EnumType.spline
chart1.CommonSeriesOptions.EnableAnimation = False
chart1.CommonSeriesOptions.markerdatalabelsvisible = False
chart1.CommonSeriesOptions.MarkerVisible = False
chart1.CommonSeriesOptions.ToolTipVisible = False
chart1.CommonSeriesOptions.Width = 3
RefreshOnLoad_chart1(chart1)
page.Cell(2,1).AddComponent(chart1.ABMComp)
AdminAccess
page.Refresh ' IMPORTANT
' NEW, because we use ShowLoaderType=ABM.LOADER_TYPE_MANUAL
page.FinishedLoading 'IMPORTANT
page.RestoreNavigationBarPosition
End Sub

'Refresh the contents of an SyncfusionChart in runtime
Private Sub RefreshOnLoad_chart1(ochart805 As SyncfusionChart)
'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
'Read arguments from LocalStorage (if any)
' Dim Year As String = ABMShared.SessionStorageRead(page, "Year")
' Dim Year As String ="106"
page.Pause
'Get connection from current pool if MySQL/MSSQL
Dim SQL As SQL = ABMShared.SQLGet
'Get the records as a list of maps from the db
results = ABMShared.SQLExecuteMaps(SQL,"select * from IYMAnalysisSet", Null)
'Close the connection to the database
ABMShared.SQLClose(SQL)
'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)
'create the series apract
Dim ser01apract As SeriesObj
ochart805.InitializeSeries(ser01apract)
ser01apract.stype = ochart805.EnumType.line
ser01apract.name = resMap.GetDefault("apract","")
ser01apract.tooltipvisible = False
ser01apract.width = 3
ser01apract.EnableAnimation = False
ser01apract.markershape = "circle"
ser01apract.MarkerVisible = True
ser01apract.markerdatalabelsvisible = True
'Read the fields to draw...
Dim apract As String = resMap.GetDefault("apract", "0")
'Add data to the chart
ochart805.AddXY(ser01apract,"apract",apract)
ochart805.AddSeries(ser01apract)
'create the series mayacc
Dim ser02mayacc As SeriesObj
ochart805.InitializeSeries(ser02mayacc)
ser02mayacc.stype = ochart805.EnumType.line
ser02mayacc.name = resMap.GetDefault("mayacc","")
ser02mayacc.tooltipvisible = False
ser02mayacc.width = 3
ser02mayacc.EnableAnimation = False
ser02mayacc.markershape = "circle"
ser02mayacc.MarkerVisible = True
ser02mayacc.markerdatalabelsvisible = False
'Read the fields to draw...
Dim mayacc As String = resMap.GetDefault("mayacc", "0")
'Add data to the chart
ochart805.AddXY(ser02mayacc,"mayacc",mayacc)
ochart805.AddSeries(ser02mayacc)
'create the series junacc
Dim ser03junacc As SeriesObj
ochart805.InitializeSeries(ser03junacc)
ser03junacc.stype = ochart805.EnumType.line
ser03junacc.name = resMap.GetDefault("junacc","")
ser03junacc.tooltipvisible = False
ser03junacc.width = 3
ser03junacc.EnableAnimation = False
ser03junacc.markershape = "circle"
ser03junacc.MarkerVisible = True
ser03junacc.markerdatalabelsvisible = True
'Read the fields to draw...
Dim junacc As String = resMap.GetDefault("junacc", "0")
'Add data to the chart
ochart805.AddXY(ser03junacc,"junacc",junacc)
ochart805.AddSeries(ser03junacc)
'create the series julacc
Dim ser04julacc As SeriesObj
ochart805.InitializeSeries(ser04julacc)
ser04julacc.stype = ochart805.EnumType.line
ser04julacc.name = resMap.GetDefault("julacc","")
ser04julacc.tooltipvisible = False
ser04julacc.width = 3
ser04julacc.EnableAnimation = False
ser04julacc.markershape = "circle"
ser04julacc.MarkerVisible = True
ser04julacc.markerdatalabelsvisible = True
'Read the fields to draw...
Dim julacc As String = resMap.GetDefault("julacc", "0")
'Add data to the chart
ochart805.AddXY(ser04julacc,"julacc",julacc)
ochart805.AddSeries(ser04julacc)
Next
page.Resume
End Sub
 

Attachments

  • TEST.zip
    14 KB · Views: 278

Mashiane

Expert
Licensed User
Hi @Mashiane XAsix must more than 10 series is ok ,Less than 10 will be automatically filled in 10 series
Please email me at anele@mbangas.com, your project database, this is in the "\Objects\Projects folder" where you are running MyMaterial.Show. That is what I need to check, the database should be your project name .db
 

Mashiane

Expert
Licensed User
Hi, can you zip and email me your project db, there is something wrong with your series definition.
Actually, can you post the complete code for your

Private Sub RefreshOnLoad_chart1(ochart1 As SyncfusionChart)

I think the problem is in that method, you need to ensure that the series being added only addresses the colukns in your database that need to be plotted.

It should be something like this...

B4X:
Private Sub RefreshOnLoad_chart1(ochart1 As SyncfusionChart)
  ......
        'Read the fields to draw...
        Dim AprAcc As String = resMap.GetDefault("apracc", "0")
        Dim MayAcc As String = resMap.GetDefault("mayacc", "0")
        Dim JunAcc As String = resMap.GetDefault("junacc", "0")
        Dim JulAcc As String = resMap.GetDefault("julacc", "0")
        'Add data to the chart
        ochart1.AddXY(ser01,"Apr",AprAcc)
        ochart1.AddXY(ser01,"May",MayAcc)
        ochart1.AddXY(ser01,"Jun",JunAcc)
        ochart1.AddXY(ser01,"Jul",JulAcc)
        ochart1.AddSeries(ser01)

.....
End Sub

 
Top