Share My Creation Pen&Paper

Discussion in 'B4J Share Your Creations' started by Mashiane, Aug 22, 2016.

  1. Mashiane

    Mashiane Well-Known Member Licensed User

    @clarionero

    Which database are you using as a backend? I assume MySQL. I'm not sure if the "pragma table_info" works with MySQL, you might have to migrate your db to SQLite and then in the project properties choose SQLite and the database location, do Database > Test Connect and opt to document your database. Currently the way I am developing my projects is using SQLite first and then when I deploy, just change the database connection string to MySQL in my project properties, do a database, test connection and run the project.

    I will also ensure that I trap this error on my side, thanks, I wouldnt have found this out..

    Well I needed an easy way out, its an evolving project. ta!;)
     
    joulongleu likes this.
  2. Mashiane

    Mashiane Well-Known Member Licensed User

    What's new:

    Just fixed a database bug and the new updated version is available. See the ReadMeFirst file for the details.

    Ta!
     
    joulongleu and JakeBullet70 like this.
  3. clarionero

    clarionero Member Licensed User

    Hi. Yes, my database is MySQL. MyMaterial import the database tables information without issues. The error is only when i click a table mane.

    Rubén
     
    joulongleu likes this.
  4. Mashiane

    Mashiane Well-Known Member Licensed User

    What's New

    2017-01-27

    Dropbox Folder

    1. Revamped the ABMImage designer. Learned: To align images inside a cell use the CellTheme.
    2. Added _Computed function for ABMInput and ABMImages designers, your images are changed depending on variables you pass.
    3. Added _Compute function for the ABMPage, before a record is shown/saved this code runs. Computed components can be/ cannot be saved to a database depending on your preference.
    4. Revamped again the functionality of the ABMInput designer for computations etc.

    ABMImage Designer

    ABMImage.png

    ABMInput

    ABMInput.png

    Computations: These are executed after the record is read from the db and before it is also saved. Computed fields can be saved/not saved depending on whether you opt to Use on AddUpdate.

    Compute.png

    ImgCompute.png

    As an example above, the value of the AprVar field is computed using AprProj and AprAct. Then the value of AprImg is determined by passing the AprVar value previously saved. It's important that the TabOrder is fixed properly here so that whatever should be calculated first is done before its processed.

    As an output, here is an example of my screen. I'm not using a table but just ABMInputs and Images and Labels here. I have a side bar that have custom components, each time a record is selected, its read and displayed on the page and the computations happen in real time. I decided on this approach to have to avoid having to click on a button after the content is displayed. The nice part is that code is also broken down into small managable chunks.

    Example output..

    Output.png

    Variance = Projections Less Expenditure
    EOU (EqualOverUnder) are stored ../imaged/ that are returned from an ABMShared imaged I have added..

    Code:
    Public Sub UpDownSame(variance As StringAs String
        
    If variance < 0 Then Return "../images/up.png"
        
    If variance > 0 Then Return "../images/down.png"
        
    If variance = 0 Then Return "../images/same.png"
        
    Return ""
    End Sub
    Percentage Change is percentage change between projections and expenditure, also to add my own tolerance levels via a computation. This is becoming very interesting.

    By the way, is there an easier way to learn the ABMChart, I'm finding it very intimidating?
     
    clarionero and joulongleu like this.
  5. Mashiane

    Mashiane Well-Known Member Licensed User

    Just fixed the imagesliders designer, and also included a charting library... The charts are downloadable from the canvas.

    ImageSlider.png

    canvas.png
     
    Erel and joulongleu like this.
  6. joulongleu

    joulongleu Member

    Above two examples, would like to test this example, but no database, can provide, Thanks
     
    Mashiane likes this.
  7. Mashiane

    Mashiane Well-Known Member Licensed User

    Will post something soon...
     
  8. Mashiane

    Mashiane Well-Known Member Licensed User

    MyMaterial 2:

    Added functionality on the Project Definition to add code that will be appended to the ABMShared module. You can paste the code that you want to share across all modules here. Select the first tree item (your project name) and click the ABMShared code.

    ABMSharedCode.png

    As an example the KPIFinder is a method that I want to run whenever I read a record from the database to return an image that I need to return depending on the value of some field.

    I have defined ABMImages components like this...

    Code:
    Dim AugImg As ABMImage
        AugImg.Initialize(
    page"AugImg""../images/orange.png"1)
        AugImg.IsResponsive = 
    False
        AugImg.IsCircular = 
    False
        AugImg.IsClickable = 
    False
        AugImg.IsMaterialBoxed = 
    False
        
    page.cell(9,5).UseTheme("centercontent")
        
    page.Cell(9,5).AddComponent(AugImg)
    Code:
    Dim AugKPI As ABMImage
        AugKPI.Initialize(
    page"AugKPI""../images/orange.png"1)
        AugKPI.IsResponsive = 
    False
        AugKPI.IsCircular = 
    False
        AugKPI.IsClickable = 
    False
        AugKPI.IsMaterialBoxed = 
    False
        
    page.cell(9,7).UseTheme("centercontent")
        
    page.Cell(9,7).AddComponent(AugKPI)
    When both Images are initialized, they have an orange.png assigned to them. When I want to update them based on the computed images... I get them from the form...

    Code:
    Dim AugImg As ABMImage = page.Component("AugImg")
    Dim AugKPI As ABMImage = page.Component("AugKPI")
    The AugImg depends on the variance field, if the variance between projections and expenditure is positive, we have an under-expenditure, thus a green image, else we have a red arrow, if both are the same we have an arrow pointing to the right. So I need to read the variance field first. In my case as the variance i.e. projections less expenditure can be saved in a table, I just run a query to update the field and then read it..

    To refresh the Image, I run..

    Code:
    Dim augChange as string = pMap.get("augchange")
    Dim AugKPIContents As String = KpiChange(augChange)
    AugKPI.Source = AugKPIContents
    AugKPI.Refresh
    The pMap variable being a table record that stores the current record I'm using. The commands for all the records are..

    Code:
    sqlCommands.add("update iymanalysisset set augvar = augproj - augact")
    sqlCommands.add(
    "update iymanalysisset set augimg = '../images/up.png' where augvar < 0")
    sqlCommands.add(
    "update iymanalysisset set augimg = '../images/down.png' where augvar > 0")
    sqlCommands.add(
    "update iymanalysisset set augimg = '../images/same.png' where augvar = 0")
    As the KPIImage is rather tricky, I needed a method to return the appropriate image using Computed fields. The end result is based on the percentage change variable. So, these are the steps.

    1. Read the record, assign it to a map e.g. pMap
    2. Get the value of the change e.g. augchange
    3. Get the output image by passing augchange to KpiFinder e.g. Dim augKPI as String = KPIFinder(augchange)
    4. Assign the kpi to the image object.

    Code:
    Public Sub KpiFinder(value as stringAs String
    value = 
    round(value)
    value = 
    abs(value)
    if value <= 5 then return "../images/green.png"
    if (value > 5and (value <= 10then return "../images/amber.png"
    return "../images/red.png"
    End Sub
    That is how your computed field values work. You can also do the same for records to be saved to the database, by assigning them a result of a formular/method that you run before they get saved.
     
    joulongleu likes this.
  9. Mashiane

    Mashiane Well-Known Member Licensed User

    MyMaterial 2: Continued.

    Dynamic Loading ABMSideBar component items.

    I recently wrote a post here about how this was done. That post shared the code portion of this exercise. This is how I did with inside MyMaterial.Show.

    1. I had created my page... (Select the page)
    2. In the page I added a ABMSideBarItem (Select Designer, Components, ABMSideBarItem)


    SideBarItem.png

    3. I entered the properties as required. As this item uses a custom component, I checked that this component needs to be built.
    4. These contents are to be based on a table, I selected the table from the list
    5. I specified the query to use to return the results, in this case we want the year column to be returned.
    6. As each record in the table has to be referenced, the primary key field was assigned to the Year column for this choice.

    SideBarItem1.png

    For my custom side bar components, the title and description are required. In this case we only want the title to be sourced from the Year field, so only that is provided. This then generates this following code...

    Code:
    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),"../frmIYMExpenditurePerProgramme/frmIYMExpenditurePerProgramme.html")
            
    page.NavigationBar.AddSideBarDivider("")
        
    Next
        
    page.Resume
    End Sub
     
    joulongleu likes this.
  10. Mashiane

    Mashiane Well-Known Member Licensed User

    MyMaterial 2: Continued

    Dynamic Charts

    This version of MyMaterial.Show includes the SyncfusionChart charting components. My article here was the output of what I will detail here. To add a SyncfusionChart component to your page...

    1. Create the page
    2. Add the SyncfusionChart component (select page, goto Designers > Components > SyncfusionChart)

    Chart1.png

    Provide the relevant information for the chart... Each time the chart is drawn, I want each record to be recomputed using the ABMShared.IYMAnalysisSet_Compute method. This is part of the code I saved in my project ABMShared tab and this code is shared accross the app.

    Specify the axis, and other details... In my article I indicated how this information is sourced from a db table, the next portion of the screen is where you indicate the datasource.

    Chart2.png

    You provide the table name, the primary key. This primary key is the same as the primary key for the sidebaritem, by doing this we are creating a link between the sidebar item and the chart. We then specify the select query to use for the chart details and also an arguement to pass. Arguements passed with MyMaterial.Show are all read from session storage, after another methods writes them there initially.

    As these are custom components and need to be accessed from Global variables on the page, we need to tell the page that we are using these components. Then select your page component and then check Use SyncfusionChart.

    Chart3.png

    Now, specifiying the series for the chart. Before that is done, we need to ensure that our query is correct by running a test. Go back to the SyncfusionChart component and select the records Tab and select the Run > button.

    SQLViewer.png

    The "Records" section should show the column names. If not then your SQL query was incorrect. To make this work you should have done Database Connection and Documentation so that MyMaterial.Show knows the datasources to work with.

    The next step then is the series data as depicted below.

    Series.png

    You can have a multiple of series in your chart. Series 1 above here is based on multiple records from the database that will be based on the year, the series name is sourced from the ProgrammeName, it needs to be a line chart per programme, with a circle marker shape, the field names to plot and their Labels, i.e headers.

    The code generated by this will sit on ConnectPage....

    Code:
    'add components for the page
        chart1.Initialize(page"chart1")
        chart1.Title = 
    "In Year Monitoring"
        chart1.XAxisTitle = 
    "Month"
        chart1.YAxisTitle = 
    "Millions of Rands"
        chart1.YAxisLabelFormat = 
    "R{value}M"
        chart1.YAxisLabelFormatLocale = 
    True
        chart1.LegendTitle = 
    "YTD Expenditure Analysis"
        chart1.LegendVisible = 
    True
        chart1.LegendPosition = chart1.EnumLegendPosition.bottom
        chart1.EnableZooming = 
    False
        chart1.enableCanvasRendering = 
    False
        chart1.toolTipInitialize = 
    True
        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)
    and then the RefreshOnLoad_chart1(chart1) method, will be called..

    Code:
    'Refresh the contents of an SyncfusionChart in runtime
    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 AprAcc,MayAcc,JunAcc,JulAcc,AugAcc,SepAcc,OctAcc,NovAcc,DecAcc,JanAcc,FebAcc,MarAcc,[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 = ABMShared.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 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")
            
    Dim AugAcc As String = resMap.GetDefault("augacc""0")
            
    Dim SepAcc As String = resMap.GetDefault("sepacc""0")
            
    Dim OctAcc As String = resMap.GetDefault("octacc""0")
            
    Dim NovAcc As String = resMap.GetDefault("novacc""0")
            
    Dim DecAcc As String = resMap.GetDefault("decacc""0")
            
    Dim JanAcc As String = resMap.GetDefault("janacc""0")
            
    Dim FebAcc As String = resMap.GetDefault("febacc""0")
            
    Dim MarAcc As String = resMap.GetDefault("maracc""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.AddXY(ser01,
    "Aug",AugAcc)
            ochart1.AddXY(ser01,
    "Sep",SepAcc)
            ochart1.AddXY(ser01,
    "Oct",OctAcc)
            ochart1.AddXY(ser01,
    "Nov",NovAcc)
            ochart1.AddXY(ser01,
    "Dec",DecAcc)
            ochart1.AddXY(ser01,
    "Jan",JanAcc)
            ochart1.AddXY(ser01,
    "Feb",FebAcc)
            ochart1.AddXY(ser01,
    "Mar",MarAcc)
            ochart1.AddSeries(ser01)
        
    Next
        
    page.Resume
    End Sub
    From above, as long as the query string for your data is correct, you can draw any chart you want..

    PS: You might have noted that the field names in the designer and the output code are different. It's a mix of code for another page and designer input for another page. In my app, I used the same approach to draw 8 different charts using different sets of data.
     
    joulongleu likes this.
  11. Mashiane

    Mashiane Well-Known Member Licensed User

    MyMaterial 2: Continued.

    Excel Reporting

    My article about using Excel as a reporting engine bears reference. As you will note, the same approach here about the charts, sidebaritems will be followed. We create an ExcelReport component, specify the template file to use for the output, the table to source the information from and the fields to draw out on the excel workbook.

    1. As explained in the article, you will have to create your Excel Template first. You can also add charts to it based on your data.
    2. Select your page and add an ExcelReport component from the Designers.

    ExcelReport.png

    We give the report a title to differentiate it from the others, and select the template file to use. All templates selected are automatically added to the server folder to publish. Also here we want the records to be processed with IYMAnalysis_Set, you can leave this blank if you dont need it. Here we need to indicate the starting row to start writing records at and the starting column. We specify which table to source the records from and the query to run.

    Again, here the arguements is the Year, derived from the saved session variable that was saved from the SideBarItem selection. The next section then is definition of the fields to report on.

    The columns Tab of the ExcelReport component holds all the sequences of the fields.

    IMPORTANT: The order of the fields here follow the order of the columns in the template, thus the use of 01, 02 etc for the ids.

    Columns.png

    Also the column types are important here. The monetary columns should be numeric. In this case, this column has a formula at the bottom and if there is no value read from the table, use a zero (0). After all is done, this component design generates this code for the report.

    Code:
    Private Sub ExcelReport_ME()
    Dim sOriginalTitle As String = "Actual Monthly Expenditure Analysis"
        
    Dim sTitle As String = sOriginalTitle.Replace(" ","")
        
    page.Pause
        
    Dim startRow As Int = 3 - 1
        
    Dim startCell As Int = 1 - 1
        
    Dim endCell As Int = 0
        
    Dim hasFormula As Boolean = True
        
    Dim protectWB As Boolean = False
        
    Dim projectPWD As String = "drpw"
        
    Dim qry As String = "select ProgrammeName,AprAct,MayAct,JunAct,JulAct,AugAct,SepAct,OctAct,NovAct,DecAct,JanAct,FebAct,MarAct,Actual from IYMAnalysisSet join ProgrammesSet on IYMAnalysisSet.IYMAnalysis_Programmes = ProgrammesSet.id where IYMAnalysisSet.IsSum = 0 and IYMAnalysisSet.Year = ?"
        
    Dim resTot As Int
        
    Dim sTemplate As String = "iym_me.xlsx"
        
    Dim resCnt As Int
        
    Dim results As List
        
    Dim resMap As Map
    'Read arguments from LocalStorage (if any)
    Dim Year As String = ABMShared.SessionStorageRead(page"Year")

    Dim sProgrammeName As String
    Dim sAprAct As String
    Dim sMayAct As String
    Dim sJunAct As String
    Dim sJulAct As String
    Dim sAugAct As String
    Dim sSepAct As String
    Dim sOctAct As String
    Dim sNovAct As String
    Dim sDecAct As String
    Dim sJanAct As String
    Dim sFebAct As String
    Dim sMarAct As String
    Dim sActual As String
    'The number of columns to generate
    endCell = 13
    'start processing the workbook
        Dim wb As PoiWorkbook
        
    'initialize an existing workbook
        wb.InitializeExisting(File.DirApp,sTemplate,"")
        
    'get the firstsheet in the workbook
        Dim wSheet As PoiSheet = wb.GetSheet(0)
        
    Dim wsrow As PoiRow
        
    Dim wsCell As PoiCell
        
    Dim cellCnt As Int
    '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, qry, 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 = ABMShared.IYMAnalysisSet_Compute(resMap)
    sProgrammeName = resMap.GetDefault(
    "programmename","")
    sAprAct = resMap.GetDefault(
    "apract","0")
    sMayAct = resMap.GetDefault(
    "mayact","0")
    sJunAct = resMap.GetDefault(
    "junact","0")
    sJulAct = resMap.GetDefault(
    "julact","0")
    sAugAct = resMap.GetDefault(
    "augact","0")
    sSepAct = resMap.GetDefault(
    "sepact","0")
    sOctAct = resMap.GetDefault(
    "octact","0")
    sNovAct = resMap.GetDefault(
    "novact","0")
    sDecAct = resMap.GetDefault(
    "decact","0")
    sJanAct = resMap.GetDefault(
    "janact","0")
    sFebAct = resMap.GetDefault(
    "febact","0")
    sMarAct = resMap.GetDefault(
    "maract","0")
    sActual = resMap.GetDefault(
    "actual","0")
    Dim lstFlds As List
    lstFlds.Initialize
    lstFlds.Add(sProgrammeName)
    lstFlds.Add(sAprAct)
    lstFlds.Add(sMayAct)
    lstFlds.Add(sJunAct)
    lstFlds.Add(sJulAct)
    lstFlds.Add(sAugAct)
    lstFlds.Add(sSepAct)
    lstFlds.Add(sOctAct)
    lstFlds.Add(sNovAct)
    lstFlds.Add(sDecAct)
    lstFlds.Add(sJanAct)
    lstFlds.Add(sFebAct)
    lstFlds.Add(sMarAct)
    lstFlds.Add(sActual)
    'get the excel row
    wsrow = wSheet.GetRow(StartRow)
    'initialize it, there are existing rows in the file
    wsrow.IsInitialized'get the excel columns
    For cellCnt = startCell To endCell
    wsrow.GetCell(cellCnt).IsInitialized
    Select Case cellCnt
    Case 0
    wsrow.GetCell(cellCnt).ValueString = lstFlds.Get(cellCnt)
    Case 1
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 2
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 3
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 4
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 5
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 6
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 7
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 8
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 9
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 10
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 11
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 12
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    Case 13
    wsrow.GetCell(cellCnt).ValueNumeric = lstFlds.Get(cellCnt)
    End Select
    Next
    StartRow = StartRow + 
    1
    Next
    If hasFormula = True Then
            
    'we have formulars
            Dim lastRow As Int = wSheet.LastRowNumber - 1
            
    'get the last row
            wsrow = wSheet.GetRow(lastRow)
            wsrow.IsInitialized
            
    For cellCnt = startCell To endCell
                
    Select Case cellCnt
                
    Case 1,2,3,4,5,6,7,8,9,10,11,12,13
                    
    'get the cell in question
                    wsCell = wsrow.GetCell(cellCnt)
                    wsCell.IsInitialized
                    wsCell.ValueFormula = wsCell.ValueFormula
                
    End Select
            
    Next
        
    End If
    'protect the sheet
        If protectWB = True Then
            
    Dim stSecret As String = projectPWD
                
    Dim jo As JavaObject = ws
                jo.RunMethod(
    "protectSheet",Array As Object(stSecret))
        
    End If
        
    'Creating an unique date and time stamp as part of the filename.
        DateTime.DateFormat= "yyyy-MM-dd"
        
    DateTime.TimeFormat="HHmm"
        
    Dim stDateTime As String ="_" & DateTime.Date(DateTime.Now) & "_" & DateTime.Time(DateTime.now)
        
    Dim outFileName As String = sOriginalTitle & stDateTime & ".xlsx"
        
    'save the file in the reports folder
        Dim fPath As String = File.Combine(File.DirApp,"www/" & ABMShared.AppName.tolowercase & "/reports")
        wb.Save(fPath, outFileName)
        wb.Close
        
    page.resume
        
    ' we want to execute a download of the file, open the document in a new browser tab
        Dim pgLink As String = "../reports/" & outFileName
        
    If ws.Open Then
            
    'force a download of the document
            ws.Eval("window.open(arguments[0],'_blank');"Array As Object(pgLink))             
            ws.Flush
        
    End If
    End Sub
     
    Last edited: Feb 16, 2017
    joulongleu likes this.
  12. Mashiane

    Mashiane Well-Known Member Licensed User

    MyMaterial In Action - Part 1



    This video demonstrates the link between a sqlite database and mymaterial.show and how that is used to create table for data entry.

    This part just ends after the project is compiled but before it can be compiled and viewed on the browser, more videos are coming.
     
    Last edited: Mar 16, 2017
    joulongleu likes this.
  13. Mashiane

    Mashiane Well-Known Member Licensed User

    What's New: 24/03/2017

    Source

    1. The Database Documentor (SQLite ONLY) has been updated. This means the fields are now table specific and not selectable anymore in the treeview. This needed to be done for more control as this app just got too busy. So to avoid any confusion, when a table is selected from the treeview, the "Fields" and their properties are now on the right side of the screen in the second tab.

    2. This "Fields" tab enables one to add, update and delete fields from the underlying database table. ONLY SQLite is currently supported with this.

    3. Please ensure that when you use this version, you run the database documentor. On the menu...
    3.1 Select Database
    3.2 Select Test Connection
    3.3 Click Ok
    3.4. Confirm with Yes when asked to document your database.
    3.5 When the process is done, a notification, (bottom right of screen will indicate)

    Figure 1

    DatabaseDocumentation.png

    Figure 2

    FieldProperties.png

    NB: This does not change/create any primary key or set NOT NULL.
     
    Last edited: Mar 24, 2017
    joulongleu likes this.
  14. Mashiane

    Mashiane Well-Known Member Licensed User

    SideBarItems can be created per page so that each page can have different sidebaritems. I wanted a quicker way of adding multiple sidebar items per page and also these could be copied to other pages. So I have added a Side Navigation Tab on the page so that multiple side bar items can be configured per page and added. As soon as the page is added, the respective individual components are created.

    Figure 1

    LeftSideBar.png

    Figure 2: Example

    LeftNav.png
     
    joulongleu likes this.
  15. Mashiane

    Mashiane Well-Known Member Licensed User

    What's New: AMPaperGrid to view your 12x12 Large Grid

    As part of I helping myself develop ABMaterial webapps, I have included the AMPaperGrid as discussed in this article in the upcoming MyMaterial.Show version. With this I am able to see which controls are misplaced where some will actually sit. As my current apps are based on desktop apps, so the limitation here is the LARGE view also on a 12x12 grid and nothing else. This grid here is built around how MyMaterial works as its a helper inside MyMaterial.Show.

    The grid view is currently at Page and ModalSheet level and Container greed view will be added.

    PageGrid.png ModalSheetGrid.png
     
    joulongleu likes this.
  16. Mashiane

    Mashiane Well-Known Member Licensed User

    Updates to MyMaterial.Show

    2017-04-04

    1. Theme Documentation within this dropbox folder has been updated to include the latest themes.
    2. The Projects in this DropBox folder now includes the source code for the BibleShow WebApp. bibleshow2.db is the mymaterial project database whilst bibleshow.db is the underlying database for the bible.
    3. Fixed some minor bugs in MyMaterial.Show code generation.
    4. Open and Upgrade and Compile your projects to use this version.
    5. The AMPaperGrid to view your 12x12 grids has been included. This is just for viewing MyMaterial.Show grids and nothing else.
     
    joulongleu likes this.
  17. Mashiane

    Mashiane Well-Known Member Licensed User

    What's New: 08 April 2017

    Wow, time flies, it's my bday next saturday, 15th April, and now that I'm thinking about it, one of the best events of my life has been discovering ABMaterial.

    Anyway, MyMaterial.Show now includes functionality for those nice loaders that alwaysbusy created. Sooo sooo cool. You set these from the project properties, save your project and then from the Loader tab, set the respective properties as per Figure 2 below. Leaving the Loading Method blank will use the default spinning color coded circles.

    This version also comes up with the JustGauge Custom Component as discussed here, see Figure 3 for the definition...

    Figure 1

    LoadingMethod.png

    Figure 2

    Loader1.png

    Figure 3
    JustGaugeDef.png

    As always, this is drop boxed here...

    This hex to rgb site was found to be useful too...

    https://www.webpagefx.com/web-design/hex-to-rgb/
     
    Last edited: Apr 9, 2017
    joulongleu likes this.
  18. Mashiane

    Mashiane Well-Known Member Licensed User

    clarionero and joulongleu like this.
  19. Mashiane

    Mashiane Well-Known Member Licensed User

    What's New: 26-04-2017

    Get MyMaterial.Show

    A code snippetting feature has been added to MyMaterial.Show. This enables one to add some source code on their pages when they are creating apps inside mymaterial.show. Watch this video in HD mode...



    1. The Side Menu refresh code has been updated, this was buggy.
    2. The SideNavigation functionality at the Page level has been updated. This now ensures that any sidebars in the page are updated properly.
    3. ModalSheet generation via internal ABMGenerator: this has been fixed to ensure that modal sheet components are created properly.
    4. Fixed the location of the ABMShared<Page>_Sum functionality on the code, this was buggy.
    5. Fixed the code formatting. All blank lines are removed and case statement formatting working properly.
    6. The static top buttons coloured to white.
    7. Removed SessionCreator module inclusion for project template.
    8. There is a known grid generation statements redudancy.
    9. Pages that 'Need Authorization' and should display on SideBarMenu links, their SideBarMenu links only show when a user is Signed In.
    10. Changes to the grid for modal sheets persist the changes.

    Things I want to remember

    1. Remember to PadLeft 10 and TopMargin of 14 for checkboxes.
    2. Remember to copymewithjar.needs all the time on upload.
    3. Remember for SideBar, better to show this for desktop apps and hide for small devices
    4. Remember to simplify webapp navigation as possible for more user friendly experience.
     
    clarionero and joulongleu like this.
  20. Mashiane

    Mashiane Well-Known Member Licensed User

    What's Coming...

    1. The ABMSwitch and ABMUpload code designers have been revamped to follow the current structure of the designers.

    ABMUpload.png ABMSwitch.png

    2. A generic Excel Importer has been added to help creating code to import stuff from MS Excel, see this post. Inside MyMaterial, you define an ExcelImporter, the target tables, template and columns to import.

    ExcelImporter.png ExcelImporter1.png ExcelImporter2.png

    3. Access control functionality has been added to easily create permission based pages for your app.

    AccessControl.png permissions.png

    Pages that need authorization i.e will use CRUD permissions have a lock icon.
    In-activave pages are red texted, these are pages that will not form part of the final app. i.e. work in progress or other modules that will be included later in your app. This also generates the needed code for CRUD permissions per page and will add the respective permissions for your app CRUD based permissions. This was discussed in this post.

    4. Added Load On Demand for Page components. Page components are now loaded only when the page is selected on the tree.
    5. The built in ABMGenerator has an option to exclude the creation of the Modal Sheet for data entry. This is in case you just need a table without the data entry option.
    6. The built in ABMGenerator includes an ABMSwitch / ABMCheckBox component that you can link to your database table for data entry.
    7. ABMUpload code included, tested and working as part of the ExcelImporter.
    8. Managing your SQLite databases is easier as you can Create Tables, rename and delete fields and also see the table contents within MyMaterial.Show.

    DatabaseManager.png
     
    joulongleu likes this.
Loading...