B4J Tutorial [Pen&Paper] Automating ABMTable CRUD Functionality

Discussion in 'B4J Tutorials' started by Mashiane, Mar 1, 2017.

  1. Mashiane

    Mashiane Expert Licensed User

    Hi there

    Well, this is not new. The ABMGenerator has been an inspiration to this, so I have just adjusted a few things for my liking. As you might know, MyMaterial.Show is a code generator for ABMaterial Apps with some limited components as per this post.

    I was faced with having to create my ModalSheets, Tables and CRUD functionality for a variety of my tables. I liked the ABMGenerator so much as it does this perfectly, however the code generated by that is done externally and has to be pasted to your app. No harm with that. As part of MyMaterial.Show, I wanted that to be done within the automated script engine to produce the output without much effort.

    These are my requirements:

    1. No search functionality for the table (Can be turned on/off)
    2. CRUD functionality (Create, Read, Update and Delete records)
    3. Ability to hide the table footer where "Number of rows is shown"
    4. Modal Sheets

    MyMaterial.Show has never fully explored this functionality before, so there are some tricks of the trade that needed to be learned.

    Figure 1: Output

    output.png

    Figure 2: AddEdit

    AddEdit.png

    Figure 3: Confirm

    Confirm.png

    Other credits: Thanks to the team behind ABMaterial and other ABMaterial users for very useful advise.
     
    Last edited: Mar 1, 2017
    joulongleu likes this.
  2. Mashiane

    Mashiane Expert Licensed User

    Creating the database table:

    1. As a backend, I am using SQLite for development and can just upscale to MySQL after I have finalized everything. So I fired up my SQLite Manager and created a table with a couple of columns, id, numeric primary key which is autoincrement, HolidayDate a date type, HolidayName i.e. the name of the holiday, a varchar and also the Background color entity, also another varchar.

    2. My database is linked to my Project on MyMaterial.Show project, so after creating the table, I did, Database > Test Connection and agreend to Document the database. I captured some few records first.

    Figure 4

    Holidays.png
     
    joulongleu likes this.
  3. Mashiane

    Mashiane Expert Licensed User

    Creating the frmHolidays Page inside MyMaterial.Show

    1. After opening up my project, I created a new page, using Page > New Page.
    2. I changed the Page Name to be frmHolidays and also checked Is Initial Page
    3. I also changed the Page Title to be the project name
    4. On the Navigation tab, I changed the NavBar Title to be Holidays
    5. On the CRUD tab, I checked Has Table Crud and also checked Save Records to Session. I selected the Holiday table for DB table and Id for the Primary Key. Then back to the ABMPage tab and clicked Apply, this saved the page, created a navigation bar component and a grid row component.

    Creating the ABMTable

    1. Selected the newly created page, frmHolidays
    2. On Designers > Components > ABMGenerator and set it up as shown below.
    3. I have not specified the Search Input Caption because I do not want to have search functionality on this page. If selected this created a search input box and a search button
    4. It's important for you to check Is ABMTable for this to work properly.

    Figure 5

    ABMGenerator.png

    Figure 6

    ABMGenerator1.png

    Figure 7

    ABMGenerator2.png
     
    Last edited: Mar 1, 2017
    joulongleu likes this.
  4. Mashiane

    Mashiane Expert Licensed User

    Adding Columns to the ABMTable

    The tables for the ABMTable are derived from the database table Holidays, thus we need to create the respective columns as shown in Figure 1 above. To do this we need to use the Fields tab in the ABMGenerator.

    The first field in the ABMGenerator is usually the primary key of the table with a GEN_NONE type. As there are 3 columns in the ABMTable, we need to create 3 extra columns for it. On the EditableListView custom control one uses the buttons to Add, Update, Delete, Clone each of the columns, the first column 01 is added by default. So we need to add column 02, 03 and 04. Why 02,03,04 and not anything else?

    The sequence when using this custom control is important, we want the columns to show in the order they are added numerically, so the entries will always sorted. Below is the definition of each of these columns to be in the table. The Seq Field Name property is only used to store and sort the column order for this control. Note the Data Field that is used to link the ABMTable to the backend table.

    Figure 8: Field 1

    field1.png

    Figure 9: Field 2

    field2.png

    Figure 10: Field 3

    field3.png

    Figure 11: Field 4

    field4.png

    Each time you add a column, the structure of the table is changed. After finishing adding the columns, you need to update the ABMGenerator by selecting the ABMGenerator tab and clicking Apply. This will create the respective components for the page and the tree structure for your page should look like this figure 12 below.

    Figure 12: Tree Structure

    treestructure.png
     
    Last edited: Mar 1, 2017
    joulongleu likes this.
  5. Mashiane

    Mashiane Expert Licensed User

    Closing Off

    As noted above, the components created have the table name used i.e. Holidays in them. This is to differentiate should you have more than 1 modal sheet, 1 table for example in the same page. When you select the page frmHolidays in the tree, all of your code is now generated and you can just compile your app.

    Code:
    Sub Class_Globals
        
    Private ws As WebSocket 'ignore
        ' will hold our page information
        Public page As ABMPage
        
    ' page theme
        Private theme As ABMTheme
        
    ' to access the constants
        Private ABM As ABMaterial 'ignore
        ' name of the page, must be the same as the class name (case sensitive!)
        Public Name As String = "frmHolidays"  '<-------------------------------------------------------- IMPORTANT
        Private ABMPageId As String = ""
        
    ' your own variables
        Dim Origin As String = "http://localhost:51045" '<--- IMPORTANT for youtube videos.  Set here your website address
        ' the download folder
        Public DownloadFolder As String = "/www/" & ABMShared.AppName & "/uploads/"
        
    Public DownloadMaxSize As String = 100*1024
        
    Private myToastId As Int
        
    Private dbAction As Map
        
    Private ActiveID As Int
        
    private IsNew As Boolean
        
    Private Filter As String
        
    Private LastSort As String
        
    Private FilterCount As String
        
    Private OldValue As String
        
    Dim CurrentValues As List
        
    Dim CurrentLabels As List
        
    Public MaxRows As Int = 10
    End Sub
    'Initializes the object. You can add parameters to this method if needed.
    Public Sub Initialize()
        CurrentLabels.Initialize
        CurrentValues.Initialize
        
    ' build the local structure IMPORTANT!
        BuildPage
    End Sub
    Private Sub WebSocket_Connected (WebSocket1 As WebSocket)
        
    Log("Connected")
        ws = WebSocket1
        ABMPageId = ABM.GetPageID(
    page, Name,ws)
        
    Dim session As HttpSession = ABM.GetSession(ws, ABMShared.SessionMaxInactiveIntervalSeconds)
        
    'page specific authorization
        If session.GetAttribute2("frmHolidays""false") = "true" Then
            
    If session.GetAttribute2("IsAuthorized""") = "" Then
                ABMShared.NavigateToPage(ws, ABMPageId, 
    "../")
                
    Return
            
    End If
        
    End If
        
    If ABMShared.NeedsAuthorization Then
            
    If session.GetAttribute2("IsAuthorized""") = "" Then
                ABMShared.NavigateToPage(ws, ABMPageId, 
    "../")
                
    Return
            
    End If
        
    End If
        ABM.UpdateFromCache(Me, ABMShared.CachedPages, ABMPageId, ws)
        
    If page.ComesFromPageCache Then
            
    ' refresh the page
            page.Refresh
            
    ' because we use ShowLoaderType=ABM.LOADER_TYPE_MANUAL
            page.FinishedLoading
        
    Else
            
    ' Prepare the page
            page.Prepare
            
    ' load the dynamic content
            ConnectPage
        
    End If
        
    Log(ABMPageId)
    End Sub
    Private Sub WebSocket_Disconnected()
        
    Log("Disconnected")
    End Sub
    Public Sub Page_ParseEvent(Params As Map)
        
    Dim eventName As String = Params.Get("eventname")
        
    Dim eventParams() As String = Regex.Split(",",Params.Get("eventparams"))
        
    If eventName = "beforeunload" Then
            
    Log("preparing for url refresh")
            ABM.RemoveMeFromCache(ABMShared.CachedPages, ABMPageId)
            
    Return
        
    End If
        
    If SubExists(Me, eventName) Then
            Params.Remove(
    "eventname")
            Params.Remove(
    "eventparams")
            
    Select Case Params.Size
            
    Case 0
                
    CallSub(Me, eventName)
            
    Case 1
                
    CallSub2(Me, eventName, Params.Get(eventParams(0)))
            
    Case 2
                
    If Params.get(eventParams(0)) = "abmistable" Then
                    
    Dim PassedTables As List = ABM.ProcessTablesFromTargetName(Params.get(eventParams(1)))
                    
    CallSub2(Me, eventName, PassedTables)
                
    Else
                    
    CallSub3(Me, eventName, Params.Get(eventParams(0)), Params.Get(eventParams(1)))
                
    End If
            
    Case Else
                
    ' cannot be called diretly, to many param
                CallSub2(Me, eventName, Params)
            
    End Select
        
    End If
    End Sub
    public Sub BuildTheme()
        
    ' start with the base theme defined in ABMShared
        theme.Initialize("pagetheme")
        theme.AddABMTheme(ABMShared.MyTheme)
        
    ' add additional themes specific for this page
        theme.Page.BackColor = ABM.COLOR_TRANSPARENT
        theme.Page.BackColorIntensity = ABM.INTENSITY_NORMAL
        theme.Page.ConnectedIndicatorColor = ABM.COLOR_GREEN
        theme.Page.ConnectedIndicatorColorIntensity = ABM.INTENSITY_NORMAL
        theme.Page.DisconnectedIndicatorColor = ABM.COLOR_RED
        theme.Page.DisconnectedIndicatorColorIntensity = ABM.INTENSITY_NORMAL
        theme.Page.PlaceHolderColor = ABM.COLOR_GREY
        theme.Page.PlaceHolderColorIntensity = ABM.INTENSITY_NORMAL
    End Sub
    public Sub BuildPage()
        
    ' initialize the theme
        BuildTheme
        
    ' initialize this page using our theme
        page.InitializeWithTheme(Name, "/ws/" & ABMShared.AppName & "/" & Name, False, ABMShared.SessionMaxInactiveIntervalSeconds, theme)
        
    page.ShowLoader=True
        
    page.ShowLoaderType=ABM.LOADER_TYPE_MANUAL
        
    page.PageHTMLName = "frmHolidays.html"
        
    page.PageTitle = "DRPW"
        
    page.PageDescription = ""
        
    page.PageKeywords = ""
        
    page.PageSiteMapPriority = "0.50"
        
    page.PageSiteMapFrequency = ABM.SITEMAP_FREQ_MONTHLY
        
    'page.UseFontAwesome = False
        page.DisableBackButton = False
        
    page.UseGoogleAnalytics(ABMShared.TrackingID, Null' IMPORTANT Change this to your own TrackingID !!!!!!!
        page.GoogleMapsAPIExtras = ABMShared.GoogleMapsAPIExtras
        
    page.IsFixedFooter = False
        
    page.DisablePageReloadOnSwipeDown = True
        
    page.ShowConnectedIndicator = True
        
    page.PageLanguage = "en"
        
    page.SetFontStack("arial,sans-serif")
        
    page.AlwaysShowVerticalScrollBar = False
        
    ' adding a navigation bar
        Dim sbtopimg As ABMImage
        sbtopimg.Initialize(
    page"sbtopimg""../images/drpwlogo.png"1)
        sbtopimg.IsCircular = 
    False
        sbtopimg.SetFixedSize(
    27979)
        
    page.NavigationBar.Initialize(page"navBar", ABM.SIDEBAR_MANUAL_ALWAYSHIDE, "Holidays"TrueTrue40049, sbtopimg, ABM.COLLAPSE_ACCORDION, "drpw")
        
    page.NavigationBar.SideBarLogoHeight = 79
        
    page.NavigationBar.TopBarDropDownConstrainWidth = True
        
    ' you must add at least ONE dummy item if you want to add items to the topbar in ConnectNaviagationBar
        page.NavigationBar.AddTopItem("DUMMY"" """""False)
        
    ' you must add at least ONE dummy item if you want to add items to the sidebar
        page.NavigationBar.AddSideBarItem("DUMMY"" """"")
        
    page.ShowGridInfo = False
        
    ' create the page grid
        page.AddRows(5True"").AddCells12(1"")
        
    page.BuildGrid 'IMPORTANT once you loaded the complete grid AND before you start adding components
        ' add a error box template if the name is not entered
        page.AddModalSheetTemplate(ABMShared.BuildWrongInputModalSheet(page))
        
    ' add a modal sheet template for a yes no msgbox
        page.AddModalSheetTemplate(ABMShared.BuildMsgBoxYesNo(page))
        
    ' add a modal sheet template for a  msgbox
        page.AddModalSheetTemplate(ABMShared.BuildMsgBox(page))
        
    page.AddModalSheetTemplate(BuildModalSheetmsHolidays)
    End Sub
    Private Sub BuildModalSheetmsHolidays() As ABMModalSheet
        
    Dim msHolidays As ABMModalSheet
        msHolidays.Initialize(
    page"msHolidays"True, ABM.MODALSHEET_TYPE_NORMAL, "")
        msHolidays.Size = ABM.MODALSHEET_SIZE_NORMAL
        msHolidays.IsDismissible = 
    False
        msHolidays.IsTextSelectable = 
    True
        msHolidays.Footer.AddRowsM(
    1True00"").AddCellsOS(1000121212"")
        msHolidays.Header.AddRowsM(
    1True00"").AddCellsOS(1000121212"")
        msHolidays.Content.AddRowsM(
    5True00"").AddCellsOS(1000121212"")
        msHolidays.Header.BuildGrid  
    'IMPORTANT once you loaded the complete grid AND before you start adding components
        msHolidays.Content.BuildGrid 'IMPORTANT once you loaded the complete grid AND before you start adding components
        msHolidays.Footer.BuildGrid  'IMPORTANT once you loaded the complete grid AND before you start adding components
        'Add components to ModalSheet
        Dim dpHolidaysHolidayDate As ABMDateTimePicker
        
    Dim dpHolidaysHolidayDateDate As Long = DateTime.Now
        dpHolidaysHolidayDate.Initialize(
    page"dpHolidaysHolidayDate", ABM.DATETIMEPICKER_TYPE_DATE, dpHolidaysHolidayDateDate, "Date""")
        dpHolidaysHolidayDate.WeekText = 
    "Week"
        dpHolidaysHolidayDate.TodayText = 
    "Today"
        dpHolidaysHolidayDate.ReturnDateFormat = 
    "YYYY-MM-DD"
        dpHolidaysHolidayDate.ReturnTimeFormat = 
    ""
        dpHolidaysHolidayDate.Language = 
    "en"
        dpHolidaysHolidayDate.FirstDayOfWeek = ABM.FIRSTDAYOFWEEK_SUNDAY
        dpHolidaysHolidayDate.PickText = 
    "OK"
        dpHolidaysHolidayDate.CancelText = 
    "Back"
        msHolidays.Content.Cell(
    2,1).AddComponent(dpHolidaysHolidayDate)
        
    Dim txtHolidaysHolidayName As ABMInput
        txtHolidaysHolidayName.Initialize(
    page"txtHolidaysHolidayName", ABM.INPUT_TEXT, "Name"False"")
        msHolidays.Content.Cell(
    2,1).AddComponent(txtHolidaysHolidayName)
        
    Dim txtHolidaysBackgroundColor As ABMInput
        txtHolidaysBackgroundColor.Initialize(
    page"txtHolidaysBackgroundColor", ABM.INPUT_TEXT, "Background Color"False"")
        txtHolidaysBackgroundColor.Text = 
    "lightblue"
        msHolidays.Content.Cell(
    2,1).AddComponent(txtHolidaysBackgroundColor)
        
    Dim lblHolidays As ABMLabel
        lblHolidays.Initialize(
    page"lblHolidays""{NBSP}Add / Edit Holidays", ABM.SIZE_H5, False"whitefc")
        msHolidays.Header.Cell(
    1,1).AddComponent(lblHolidays)
        
    Dim btnApplyHolidays As ABMButton
        btnApplyHolidays.InitializeFlat(
    page"btnApplyHolidays""""""Apply""transparent")
        btnApplyHolidays.Size = ABM.BUTTONSIZE_NORMAL
        msHolidays.Footer.Cell(
    1,1).AddComponent(btnApplyHolidays)
        
    Dim btnCancelHolidays As ABMButton
        btnCancelHolidays.InitializeFlat(
    page"btnCancelHolidays""""""Cancel""transparent")
        btnCancelHolidays.Size = ABM.BUTTONSIZE_NORMAL
        msHolidays.Footer.Cell(
    1,1).AddComponent(btnCancelHolidays)
        
    Return msHolidays
    End Sub
    Public Sub msHolidaysClear()
        
    Dim msHolidays As ABMModalSheet
        msHolidays = 
    page.ModalSheet("msHolidays")
        
    Dim dpHolidaysHolidayDate As ABMDateTimePicker = msHolidays.Content.Component("dpHolidaysHolidayDate")
        
    Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
        
    Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
        dpHolidaysHolidayDate.SetDate(
    DateTime.Now)
        dpHolidaysHolidayDate.Refresh
        txtHolidaysHolidayName.Text = 
    ""
        txtHolidaysHolidayName.Refresh
        txtHolidaysBackgroundColor.Text = 
    "lightblue"
        txtHolidaysBackgroundColor.Refresh
    End Sub
    Public Sub msgyes_Clicked(Target As String)
        
    Dim msg As ABMModalSheet = page.ModalSheet("confirm")
        
    Dim msgyes As ABMButton = msg.Footer.Component("msgyes")
        
    dim strTag As String
        strTag = msgyes.Tag
        YesNoProcess(strTag)
        
    page.CloseModalSheet("confirm")
    End Sub
    Public Sub msgok_Clicked(Target As String)
        
    page.CloseModalSheet("msgbox")
    End Sub
    Public Sub msgno_Clicked(Target As String)
        
    page.CloseModalSheet("confirm")
    End Sub
    ' clicked on the navigation bar
    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
        
    End Select
        ABMShared.NavigateToPage(ws, ABMPageId, Value)
    End Sub
    Public Sub Page_FileUploaded(FileName As String, success As Boolean)
        
    page.ws.Flush ' IMPORTANT
    End Sub
    Public Sub Page_ToastClicked(ToastId As String, Action As String)
        
    page.DismissToast(ToastId)
    End Sub
    Public Sub Page_ToastDismissed(ToastId As String)
    End Sub
    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 HolidaysPager As ABMPagination
        HolidaysPager.Initialize(
    page"HolidaysPager"10TrueTrue"")
        HolidaysPager.SetTotalNumberOfPages(
    10)
        
    page.Cell(3,1).AddComponent(HolidaysPager)
        
    Dim tblHolidays As ABMTable
        tblHolidays.Initialize(
    page"tblHolidays"TrueFalseTrue"tblTheme")
        tblHolidays.IsBordered = 
    True
        tblHolidays.IsResponsive = 
    True
        tblHolidays.IgnoreFormattingCodes = 
    True
        tblHolidays.IsTextSelectable = 
    True
        tblHolidays.SetFooter(
    "Number of records: ",12,"")
        tblHolidays.SetHeaders(
    Array As String("ID""Date""Name""Background Color""Open/Edit""Delete"))
        tblHolidays.SetHeaderThemes(
    Array As String("bg""bg""bg""bg""bgc""bgc"))
        tblHolidays.SetHeaderHeights(
    Array As Int(00004848))
        tblHolidays.SetColumnVisible(
    Array As Boolean(FalseTrueTrueTrueTrueTrue))
        tblHolidays.SetColumnSortable(
    Array As Boolean(FalseTrueTrueTrueFalseFalse))
        tblHolidays.SetColumnDataFields(
    Array As String("id""HolidayDate""HolidayName""BackgroundColor"""""))
        
    page.Cell(4,1).AddComponent(tblHolidays)
        
    Dim frmHolidaysAddToTable As ABMActionButton
        frmHolidaysAddToTable.Initialize(
    page"frmHolidaysAddToTable""mdi-content-add""""bigblue")
        frmHolidaysAddToTable.MainButton.Size = ABM.BUTTONSIZE_LARGE
        
    page.AddActionButton(frmHolidaysAddToTable)
        AdminAccess
        
    page.Refresh ' IMPORTANT
        ' NEW, because we use ShowLoaderType=ABM.LOADER_TYPE_MANUAL
        page.FinishedLoading 'IMPORTANT
        page.RestoreNavigationBarPosition
        LoadHolidays(
    1)
    End Sub
    Public Sub Page_SignedOffSocialNetwork(Network As String, Extra As String)
        
    page.ws.Session.SetAttribute("UserID""")
        
    page.ws.Session.SetAttribute("UserEmail""")
        
    page.ws.Session.SetAttribute("authType""")
        
    page.ws.Session.SetAttribute("authName""")
        
    page.ws.Session.SetAttribute("IsAuthorized""")
        ABMShared.NavigateToPage(ws,ABMPageId, 
    "../")
    End Sub
    'An ABMPagination event has been fired
    Public Sub HolidaysPager_PageChanged(OldPage As Int, NewPage As Int)
        LoadHolidays(NewPage)
    End Sub
    'An ABMActionButton has been clicked
    Public Sub frmHolidaysAddToTable_Clicked(Target As String, SubTarget As String)
        
    If SubTarget = "" Then
            
    Select Case Target.tolowercase
            
    End Select
            ABMShared.SessionStorageSave(
    page"action""new")
            ABMShared.SessionStorageSave(
    page"id""-1")
            msHolidaysAddEditRecord
            
    Return
        
    End If
    End Sub
    'An ABMButton has been clicked
    Public Sub btnApplyHolidays_Clicked(Target As String)
        msHolidaysCreateUpdate
        
    page.CloseModalSheet("msHolidays")
    End Sub
    'An ABMButton has been clicked
    Public Sub btnCancelHolidays_Clicked(Target As String)
        
    page.CloseModalSheet("msHolidays")
    End Sub
    Private Sub LoadHolidays(fromPage As Int)
        
    'Lets get the component from the page.
        Dim tblHolidays As ABMTable = page.Component("tblHolidays")
        
    '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"id")
        
    'Get the record linked to this record id
        Dim RecordJSON As String = ABMShared.SessionStorageRead(pageid)
        
    '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 Holidays " & 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 Holidays " & 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
            LoadHolidays(fromPage)
            
    Return
        
    End If
        
    'set the table records
        tblHolidays.SetFooter("Number of records: " & NumRecords, 12"")
        tblHolidays.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
            
    If resMap.GetDefault("id","") = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(resMap.GetDefault(
    "id",""))
            
    End If
            rCellThemes.Add(
    "nocolor")
            
    If resMap.GetDefault("holidaydate","") = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(resMap.GetDefault(
    "holidaydate",""))
            
    End If
            rCellThemes.Add(
    "nocolor")
            
    If resMap.GetDefault("holidayname","") = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(resMap.GetDefault(
    "holidayname",""))
            
    End If
            rCellThemes.Add(
    "nocolor")
            
    If resMap.GetDefault("backgroundcolor","") = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(resMap.GetDefault(
    "backgroundcolor",""))
            
    End If
            rCellThemes.Add(
    "nocolor")
            
    Dim btnEditHolidays As ABMButton
            btnEditHolidays.InitializeFloating(
    page"btnEditHolidays""mdi-action-visibility""")
            rCellValues.Add(btnEditHolidays)
            rCellThemes.Add(
    "openedit")
            
    Dim btnDeleteHolidays As ABMButton
            btnDeleteHolidays.InitializeFloating(
    page"btnDeleteHolidays""mdi-action-delete""")
            rCellValues.Add(btnDeleteHolidays)
            rCellThemes.Add(
    "openedit")
            
    'Add the row to the table
            tblHolidays.AddRow("id" & resCnt, rCellValues)
            tblHolidays.SetRowThemes(rCellThemes)
        
    Next
        
    'Update the paginating component
        Dim pager As ABMPagination = page.Component("HolidaysPager")
        
    If (NumRecords Mod MaxRows > 0Or (NumRecords = 0Then
            NumRecords = NumRecords/MaxRows + 
    1
        
    Else
            NumRecords = NumRecords/MaxRows
        
    End If
        pager.SetTotalNumberOfPages(NumRecords)
        pager.SetActivePage(fromPage)
        pager.Refresh
        tblHolidays.Refresh
        
    page.Resume
    End Sub
    'Code to handle the sorting of the table
    Public Sub tblHolidays_SortChanged(DataField As String, Order As String)
        
    Select Case DataField
        
    Case "HolidayDate","HolidayName","BackgroundColor"
            LastSort = 
    " ORDER BY " & DataField & " " & Order & ", id "
        
    Case Else
            LastSort = 
    " ORDER BY id "
        
    End Select
        
    Dim pager As ABMPagination = page.Component("HolidaysPager")
        LoadHolidays(pager.GetActivePage())
    End Sub
    'Code to handle the search on the data
    Public Sub DoSearchHolidays
        
    Dim txtSearch As ABMInput = page.Component("txtSearchHolidays")
        Filter = txtSearch.Text
        
    If Filter <> "" Then
            Filter = 
    " WHERE HolidayDate LIKE '%" & Filter & "%' OR HolidayName LIKE '%" & Filter & "%' OR BackgroundColor LIKE '%" & Filter & "%' "
        
    Else
            Filter = 
    ""
        
    End If
        
    'reload the table
        LoadHolidays(1)
    End Sub
    'Code to handle the search enter press event
    Public Sub txtSearchHolidays_EnterPressed(value As String)
        DoSearchHolidays
    End Sub
    'Code to handle button clicks on table for edit/delete
    Public Sub tblHolidays_Clicked(PassedRowsAndColumns As List)
        
    'Get the details of the cell being selected
        Dim tblCellInfo As ABMTableCell = PassedRowsAndColumns.Get(0)
        
    'Get the table being processed.
        Dim tblHolidays As ABMTable = page.Component(tblCellInfo.TableName)
        
    'Read the first column in the current row and assign value to ActiveID
        ActiveId = tblHolidays.GetString(tblCellInfo.Row, 0)
        
    Select Case tblCellInfo.Column
        
    Case 4
            
    'Edit a record
            ABMShared.SessionStorageSave(page"action""edit")
            ABMShared.SessionStorageSave(
    page"id", ActiveId)
            msHolidaysAddEditRecord
        
    Case 5
            
    'Delete a record
            ABMShared.ShowYesNo(page,"Are you sure that you want to delete this record? You will not be able to undo your action. Continue to delete?""DeleteHolidays""CancelDeleteRecord")
        
    End Select
    End Sub
    Public Sub msHolidaysAddEditRecord()
        
    'Show progress dialog...
        Dim strAction As String
        
    Dim strID As String
        OldValue = 
    ""
        
    'Read action from localstorage...
        strAction = ABMShared.SessionStorageRead(page"action")
        
    'Read the ID from localstorage...
        strID = ABMShared.SessionStorageRead(page"id")
        
    Select Case strAction
        
    Case "new"
            
    'clear the contents of the modal sheet component
            msHolidaysClear
            
    page.ShowModalSheet("msHolidays")
        
    Case "edit"
            
    'clear the controls of the page
            msHolidaysClear
            
    'read the record from the database.
            Dim SQL As SQL = ABMShared.SQLGet
            
    Dim Record As Map
            Record = ABMShared.SQLRecordRead(
    SQL,"Holidays""id", strID)
            
    'Close the database connection...
            ABMShared.SQLClose(SQL)
            
    If Record.IsInitialized = True Then
                
    'The record has been found, update the modal sheet controls...
                msHolidaysSetContents(Record)
                
    page.ShowModalSheet("msHolidays")
            
    End If
        
    End Select
    End Sub
    Private Sub msHolidaysCreateUpdate()
        
    page.pause
        
    'define a map to hold the form contents
        Dim m As Map
        
    'read the modal sheet contents to a map
        m = msHolidaysGetContents
        
    'validate the form contents where required
        If msHolidaysValidate(m) = False Then
            
    page.resume
            
    Return
        
    End If
        
    'the form contents are ok, continue with the save
        Dim strAction As String
        
    Dim strID As String
        
    'determine the action we have been performing
        strAction = ABMShared.SessionStorageRead(page"action")
        strID = ABMShared.SessionStorageRead(
    page"id")
        
    Select Case strAction
        
    Case "new"
            
    Dim SQL As SQL
            
    'get the database connection
            Dim SQL As SQL = ABMShared.SQLGet
            
    'insert the record
            strID = ABMShared.SQLRecordInsert(SQL"Holidays", m)
            
    If strID > 0 Then
                ABMShared.SessionStorageSave(
    page"action""edit")
                ABMShared.SessionStorageSave(
    page"id", strID)
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastgreen""Record added successfully."3000)
                
    'Refresh the table
                Dim pager As ABMPagination = page.Component("HolidaysPager")
                LoadHolidays(pager.GetActivePage())
            
    Else
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastred""Record could not be added, please try again."3000)
            
    End If
            
    'Close the connection to the database
            ABMShared.SQLClose(SQL)
        
    Case "edit"
            
    Dim SQL As SQL
            
    'get the database connection
            Dim SQL As SQL = ABMShared.SQLGet
            
    If ABMShared.SQLRecordUpdate(SQL,"Holidays", m, "id", strID) = True Then
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastgreen""Record updated successfully."3000)
                
    'Refresh the table
                Dim pager As ABMPagination = page.Component("HolidaysPager")
                LoadHolidays(pager.GetActivePage())
            
    Else
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastred""Record could not be updated, please try again."3000)
            
    End If
            
    'Close the connection to the database
            ABMShared.SQLClose(SQL)
        
    End Select
        
    page.resume
    End Sub
    'Get the contents of the modal sheet input components and save to a map
    Public Sub msHolidaysGetContents() As Map
        
    Dim pMap As Map
        pMap.Initialize
        
    Dim msHolidays As ABMModalSheet
        msHolidays = 
    page.ModalSheet("msHolidays")
        
    Dim dpHolidaysHolidayDate As ABMDateTimePicker = msHolidays.Content.Component("dpHolidaysHolidayDate")
        
    Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
        
    Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
        
    Dim dpHolidaysHolidayDateContents As String
        
    Dim dpHolidaysHolidayDateDate As Long
        dpHolidaysHolidayDateDate = dpHolidaysHolidayDate.GetDate
        dpHolidaysHolidayDateContents = ABM.Util.ConvertToDateTimeString(dpHolidaysHolidayDateDate, 
    "yyyy-MM-dd")
        pMap.put(
    "holidaydate", dpHolidaysHolidayDateContents)
        pMap.put(
    "holidayname", txtHolidaysHolidayName.Text)
        pMap.put(
    "backgroundcolor", txtHolidaysBackgroundColor.Text)
        
    Return pMap
    End Sub
    'Get the contents of the modal sheet from GetContents and check validity
    Public Sub msHolidaysValidate(gMap As MapAs Boolean
        
    Dim msHolidays As ABMModalSheet
        msHolidays = 
    page.ModalSheet("msHolidays")
        
    Dim stxtHolidaysHolidayName As String
        stxtHolidaysHolidayName = gMap.get(
    "holidayname")
        
    If stxtHolidaysHolidayName = "null" Then stxtHolidaysHolidayName = ""
        
    If stxtHolidaysHolidayName.Length = 0 Then
            ABMShared.ShowMsgBox(
    page,"Name cannot be blank. Please enter a value.")
            
    Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
            txtHolidaysHolidayName.SetFocus
            
    Return False
        
    End If
        
    Dim stxtHolidaysBackgroundColor As String
        stxtHolidaysBackgroundColor = gMap.get(
    "backgroundcolor")
        
    If stxtHolidaysBackgroundColor = "null" Then stxtHolidaysBackgroundColor = ""
        
    If stxtHolidaysBackgroundColor.Length = 0 Then
            ABMShared.ShowMsgBox(
    page,"Background Color cannot be blank. Please enter a value.")
            
    Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
            txtHolidaysBackgroundColor.SetFocus
            
    Return False
        
    End If
        
    Return True
    End Sub
    'Set the contents of the modal page input components from map
    Public Sub msHolidaysSetContents(pMap As Map)
        
    Dim msHolidays As ABMModalSheet
        msHolidays = 
    page.ModalSheet("msHolidays")
        
    'Get the page components to update
        Dim dpHolidaysHolidayDate As ABMDateTimePicker = msHolidays.Content.Component("dpHolidaysHolidayDate")
        
    Dim txtHolidaysHolidayName As ABMInput = msHolidays.Content.Component("txtHolidaysHolidayName")
        
    Dim txtHolidaysBackgroundColor As ABMInput = msHolidays.Content.Component("txtHolidaysBackgroundColor")
        
    'Update computations
        'Assign content to component values
        Dim dpHolidaysHolidayDateContents As String
        
    If pMap.GetDefault("holidaydate",null) = null Then
            dpHolidaysHolidayDateContents = 
    DateTime.Now
        
    Else
            dpHolidaysHolidayDateContents = pMap.get(
    "holidaydate")
            dpHolidaysHolidayDateContents = ABM.Util.ConvertFromDateTimeString(dpHolidaysHolidayDateContents, 
    "yyyy-MM-dd")
        
    End If
        dpHolidaysHolidayDate.SetDate(dpHolidaysHolidayDateContents)
        dpHolidaysHolidayDate.Refresh
        
    Dim txtHolidaysHolidayNameContents As String
        
    If pMap.GetDefault("holidayname",null) = null Then
            txtHolidaysHolidayName.Text = 
    ""
        
    Else
            txtHolidaysHolidayNameContents = pMap.get(
    "holidayname")
        
    End If
        txtHolidaysHolidayName.Text = txtHolidaysHolidayNameContents
        txtHolidaysHolidayName.Refresh
        
    Dim txtHolidaysBackgroundColorContents As String
        
    If pMap.GetDefault("backgroundcolor",null) = null Then
            txtHolidaysBackgroundColor.Text = 
    "lightblue"
        
    Else
            txtHolidaysBackgroundColorContents = pMap.get(
    "backgroundcolor")
        
    End If
        txtHolidaysBackgroundColor.Text = txtHolidaysBackgroundColorContents
        txtHolidaysBackgroundColor.Refresh
    End Sub
    Public Sub AdminAccess()
        
    'Get the usertype
        Dim UserType As String = ws.Session.GetAttribute2("UserType""0")
    End Sub
    Private Sub YesNoProcess(Tag As String)
        
    Select case Tag
        
    Case "DeleteHolidays"
            
    Dim SQL As SQL
            
    'get the database connection
            Dim SQL As SQL = ABMShared.SQLGet
            
    Dim bDeleted As Boolean = ABMShared.SQLRecordDelete(SQL"Holidays""id", ActiveID)
            ABMShared.SQLClose(
    SQL)
            
    If bDeleted = True Then
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastgreen""Record deleted successfully."3000)
                
    'Refresh the table
                Dim pager As ABMPagination = page.Component("HolidaysPager")
                LoadHolidays(pager.GetActivePage())
            
    Else
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastred""Record could not be deleted, please try again."3000)
            
    End If
        
    End Select
    End Sub
    Public Sub ConnectNavigationBar()
        
    ' Clear the dummies we created in BuildNavigationBar
        page.NavigationBar.Clear
        
    'connect the items in the navigation bar
        'refresh the navigation bar
        page.NavigationBar.Refresh ' IMPORTANT
    End Sub
    By default, the ModalSheet page created will follow this methodology of creation.

    Code:
    msHolidays.Footer.AddRowsM(1True00"").AddCellsOS(1000121212"")
        msHolidays.Header.AddRowsM(
    1True00"").AddCellsOS(1000121212"")
        msHolidays.Content.AddRowsM(
    5True00"").AddCellsOS(1000121212"")
    This is not cast in stone as you can simply change this by selecting the footerrowHolidays, headerrowHolidays and or contentrowHolidays to change the specs of your rows and columns

    footerrow.png

    So with just a simple few steps...

    1. Database table creation
    2. Documenting the database in MyMaterial.Show
    3. Creating a new page and linking it with the backend datasource
    4. Creating a ABMGenerator within the page
    5. Adding the columns for your table

    You have a fully functional ABMTable with crud functionality for Adding, Updating and Deleting your records.

    To Do:

    Add support for DropdownLists and other components and proper validation.

    That's all folks..

    PS: The latest version of MyMaterial.Show is available here
     
    Last edited: Mar 1, 2017
    joulongleu, Widget and Harris like this.
  6. Harris

    Harris Well-Known Member Licensed User

    Like I stated previously @Mashiane , you are a machine.

    Simple grids and modal sheets will benefit greatly from this.
    More complex pages will require tweeking - yet the grunt work of a foundation has been generated for you - by YOU.

    ilikeit.jpg
     
    Mashiane and joulongleu like this.
  7. joulongleu

    joulongleu Active Member

    Mashiane likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice