B4J Tutorial [ABMaterial] Flexible User Groups & Permissions

Discussion in 'B4J Tutorials' started by Mashiane, May 2, 2017.

  1. Mashiane

    Mashiane Expert Licensed User

    Well, well, well.

    It has been a long day. Due to the complexity of my app, I needed a few things.

    1. For the various modules available, some users must have read only access and others can create, update and delete records.
    2. Some users need to have access to User Administration to create and update other user records.
    3. There can be a variety of groups in the app and these should not be hard coded as there should be flexibility to create the groups and also delete the groups including their permissions.
    4. There can be 3 groups, Administrators, Basic and ReadOnly. Administrators should have full access, Basic - full access excluding user administration and ReadOnly: just that excluding user administration.
    5. Each adding, updating, deleting functionality should be linked.
    6. There are about 31 different pages in the app.

    PermissionsProcess.png

    So basically here I needed about 3 tables, these are:

    1. UserGroups
    2. GroupPermissions and
    3. Users

    • A user signs in (his/her details are stored in the users table including the user group they belong to)
    • As soon as the UserGroup for that user is identified, the permissions for that group are read and stored
    • Each time a user performs a CRUD operation on that 'specific' module, the app reads the stored permission and then opens/closes the operation depending on granted permission.
    Group Permissions Schema & Entry

    GroupPermissionsSchema.png

    Entry Screen

    GroupPermissions.png

    User Groups Schema and Entry Screen


    UserGroupsSchema.png

    User Groups Entry

    UserGroups.png

    Users Schema and Entry

    UsersSchema.png

    Users Listing & Entry Screen

    UsersListing.png

    Adding/Editing Users

    AddEditUser.png



     
    clarionero, joulongleu and Cableguy like this.
  2. Cableguy

    Cableguy Expert Licensed User

    You must have read my mind!!!
    I'm just about to get into that particular part of my app...
    BTW, "In Active" should be "Inactive" as non-active

    Do you by any chance have a "template" for DB user Registration/Authentication ?
     
    joulongleu and Mashiane like this.
  3. Mashiane

    Mashiane Expert Licensed User

    Selecting the Edit button allows one to change each module/page permissions for that group.

    ModulePermissions.png

    In my ABMShared I added a few things..

    Code:
    Public Permissions As Map
        
    Public PermissionsL As List
        
    Public HasAccessControl As String = 1
    And then in my Sign In screen, I built up the permissions based on the group the user is allocated to.

    Code:
    'Build the permissions
        ABMShared.PermissionsL.Initialize
        ABMShared.Permissions.Initialize
        ABMShared.PermissionsL = ABMShared.SQLExecuteMaps(jSQL,
    "select * from GroupPermissions where groupid = ?"Array As String(utype))
        
    For Each permission As Map In ABMShared.PermissionsL
            
    Dim spageName As String = permission.get("pagename")
            spageName = spageName.tolowercase
            ABMShared.Permissions.put(spageName,permission)
        
    Next
    The Permissions Map stores the page name and the canread, cancreate, canupdate and candelete map so that this can be read when a user does an operation.

    Also in ABMShared, I added a HasPermission method, on each page this gets passed the pagename and the operation the user wants to perform.

    Code:
    Sub HasPermission(pgCheck As String, permit As StringAs Boolean
        
    If HasAccessControl = 0 Then Return True
        pgCheck = pgCheck.tolowercase
        
    If Permissions.ContainsKey(pgCheck) = True Then
            
    Dim permitm As Map = Permissions.Get(pgCheck)
            
    Dim spermit As String = permitm.getdefault(permit.tolowercase,"0")
            
    If spermit = "1" Then
                
    Return True
            
    Else
                
    Return False
            
    End If
        
    Else
            
    Return False
        
    End If
    End Sub
    Then for the AddEditRecord method in my page, I check the permissions...

    Code:
    Public Sub AddEditRecord()
        
    'Show progress dialog...
        Dim strAction As String
        
    Dim IYMAnalysisSetId As String
        OldValue = 
    ""
        
    'Read action from localstorage...
        strAction = ABMShared.SessionStorageRead(page"action")
        
    'Read the ID from localstorage...
        IYMAnalysisSetId = ABMShared.SessionStorageRead(page"iymanalysissetid")
        
    Select Case strAction
        
    Case "new"
            
    If ABMShared.HasPermission("frmIYMUpdate","cancreate") = False Then Return
            
    'clear the contents of the page
            Clear
        
    Case "edit"
            
    If ABMShared.HasPermission("frmIYMUpdate","canupdate") = False Then Return
            
    'clear the controls of the page
            Clear
            
    'The record should be summed
            ABMShared.IYMAnalysisSet_Sum(page)
            
    'read the record from the database.
            Dim jSQL As SQL = ABMShared.SQLGet
            
    Dim m As Map
            m = ABMShared.SQLRecordRead(jSQL,
    "IYMAnalysisSet""Id", IYMAnalysisSetId)
            
    'Close the database connection...
            ABMShared.SQLClose(jSQL)
            
    If m.IsInitialized = True Then
                
    'The record has been found, update the page controls...
                SetContents(m)
            
    End If
        
    End Select
        
    'Hide the progress dialog...
    End Sub
    For my tables, I decided to disable the 'Edit' and 'Delete' buttons... if there are no permissions for them..

    Code:
    Private Sub LoadResources(fromPage As Int)
        
    'Lets get the component from the page.
        Dim tblResources As ABMTable = page.Component("tblResources")
        
    '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"resourcesid")
        
    '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)
        
    'Let's define the qry string
        sqlQry = "SELECT * FROM Resources " & Filter & " " & LastSort & " LIMIT " & ((fromPage - 1) * MaxRows) & ", " & MaxRows
        
    'Get connection from current pool if MySQL/MSSQL
        Dim jSQL As SQL = ABMShared.SQLGet
        
    'Get the number of records
        Dim SQL_str As String
        SQL_str = 
    "Select Count(id) As IDS FROM Resources " & Filter
        
    Dim NumRecords As Int = ABMShared.SQLSelectSingleResult(jSQL, SQL_str, Null)
        
    'Get the records as a list of maps from the db
        results = ABMShared.SQLExecuteMaps(jSQL, sqlQry, Null)
        
    'Close the connection to the database
        ABMShared.SQLClose(jSQL)
        
    If results.Size = 0 And fromPage > 1 Then
            
    'we are on a page without any lines
            fromPage = fromPage - 1
            LoadResources(fromPage)
            
    Return
        
    End If
        tblResources.Clear
        
    'Loop throught each record read and process it
        resTot = results.size - 1
        
    For resCnt = 0 To resTot
            
    'Get the record map
            resMap = results.get(resCnt)
            
    'Update each table row
            Dim rCellValues As List
            
    Dim rCellThemes As List
            rCellValues.Initialize
            rCellThemes.Initialize
            
    Dim primKey As String = resMap.get("id")
            
    Dim id As String = resMap.GetDefault("id""")
            
    If id = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(
    id)
            
    End If
            
    'Add theme to the cell
            rCellThemes.Add("nocolor")
            
    Dim ResourceName As String = resMap.GetDefault("resourcename""")
            
    If ResourceName = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(ResourceName)
            
    End If
            
    'Add theme to the cell
            rCellThemes.Add("nocolor")
            
    Dim Abbreviation As String = resMap.GetDefault("abbreviation""")
            
    If Abbreviation = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(Abbreviation)
            
    End If
            
    'Add theme to the cell
            rCellThemes.Add("nocolor")
            
    Dim ResourceTitle As String = resMap.GetDefault("resourcetitle""")
            
    If ResourceTitle = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(ResourceTitle)
            
    End If
            
    'Add theme to the cell
            rCellThemes.Add("nocolor")
            
    Dim EmailAddress As String = resMap.GetDefault("emailaddress""")
            
    If EmailAddress = "" Then
                rCellValues.Add(
    "{NBSP}")
            
    Else
                rCellValues.Add(EmailAddress)
            
    End If
            
    'Add theme to the cell
            rCellThemes.Add("nocolor")
            
    Dim btnEditResources As ABMButton
            btnEditResources.InitializeFloating(
    page"btnEditResources""mdi-action-visibility""")
            btnEditResources.Enabled = ABMShared.HasPermission(
    "frmResources","canupdate")
            rCellValues.Add(btnEditResources)
            rCellThemes.Add(
    "openedit")
            
    Dim btnDeleteResources As ABMButton
            btnDeleteResources.InitializeFloating(
    page"btnDeleteResources""mdi-action-delete""")
            btnDeleteResources.Enabled = ABMShared.HasPermission(
    "frmResources","candelete")
            rCellValues.Add(btnDeleteResources)
            rCellThemes.Add(
    "openedit")
            
    'Add the row to the table
            tblResources.AddRow("id" & resCnt, rCellValues)
            tblResources.SetRowThemes(rCellThemes)
        
    Next
        tblResources.Refresh
        
    'Update the paginating component
        Dim pager As ABMPagination = page.Component("ResourcesPager")
        
    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
    End Sub
    That's all folks, happy coding...
     
  4. Cableguy

    Cableguy Expert Licensed User

    Thank You Very, Very, Very Much!!!
     
    joulongleu and Mashiane like this.
  5. Mashiane

    Mashiane Expert Licensed User

    These modules are using SQLite for a connection...

    frmSignIn - sign in and verify user using email address and password.
    frmUsers - create, update, delete users based on group id.
    frmUserGroups - create, update, delete groups (still need to add permission deletion when a group is deleted)
    frmGroupPermissions - assign CRUD permissions to a group.

    Known Issues: There are some redundancies in my grid creation scripts (no shortcut methods used in some instances), so use your own way.
     

    Attached Files:

    joulongleu and Cableguy like this.
  6. Mashiane

    Mashiane Expert Licensed User

    To Do:

    • When each group is created, then add the permission links for that group set off
    • When each group is deleted, delete the permissions.
     
    joulongleu and Cableguy like this.
  7. Mashiane

    Mashiane Expert Licensed User

    I am also adding my sidebar items depending on the user permissions granted. There must be a better way of doing this... The dividers are individual components thus their own if statements. #mycodegeneratorthings

    Code:
    Sub ConnectNavigationBar()
        
    ' Clear the dummies we created in BuildNavigationBar
        page.NavigationBar.Clear
        
    'connect the items in the navigation bar
        Dim header As String = ABMShared.SessionStorageRead(page"header")
        
    If header.Length > 0 Then
            
    page.NavigationBar.Title = "Group Permissions: " & header
        
    End If
        
    page.NavigationBar.AddTopItemEx("NewRecord""""mdi-content-add"""True, ABM.COLOR_WHITE, ABM.INTENSITY_NORMAL, ABM.ICONALIGN_CENTER)
        
    page.NavigationBar.AddTopItem("GoBack""""mdi-image-navigate-before""../frmUserGroups/frmUserGroups.html"False)
        
    page.NavigationBar.AddTopItemEx("LogOff""""fa fa-sign-out""../frmSignIn/frmSignIn.html"True, ABM.COLOR_WHITE, ABM.INTENSITY_NORMAL, ABM.ICONALIGN_CENTER)
        
    If ABMShared.HasPermission("frmAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarItem("frmAdministration""Administration""mdi-action-work""../frmAdministration/frmAdministration.html")
        
    End If
        
    If ABMShared.HasPermission("frmAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarDivider
        
    End If
        
    If ABMShared.HasPermission("frmIYMUpdate","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmAdministration""frmIYMUpdate""IYM Update""mdi-action-bookmark""../frmIYMUpdate/frmIYMUpdate.html")
        
    End If
        
    If ABMShared.HasPermission("frmAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmAdministration")
        
    End If
        
    If ABMShared.HasPermission("frmProgrammes","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmAdministration""frmProgrammes""IYM vs Procurement Plan""mdi-action-bookmark""../frmProgrammes/frmProgrammes.html")
        
    End If
        
    If ABMShared.HasPermission("frmAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmAdministration")
        
    End If
        
    If ABMShared.HasPermission("frmProcPlan","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmAdministration""frmProcPlan""Procurement Plan""mdi-action-bookmark""../frmProcPlan/frmProcPlan.html")
        
    End If
        
    If ABMShared.HasPermission("frmAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmAdministration")
        
    End If
        
    If ABMShared.HasPermission("frmAddProgrammes","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmAdministration""frmAddProgrammes""Programmes""mdi-action-bookmark""../frmAddProgrammes/frmAddProgrammes.html")
        
    End If
        
    If ABMShared.HasPermission("frmAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmAdministration")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarItem("frmReporting""Reporting""mdi-av-my-library-books""../frmReporting/frmReporting.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarDivider
        
    End If
        
    If ABMShared.HasPermission("frmIYMTYD","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMTYD""Chart: YTD IYM""mdi-action-bookmark""../frmIYMTYD/frmIYMTYD.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMMonthly","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMMonthly""Chart: Monthly IYM""mdi-action-bookmark""../frmIYMMonthly/frmIYMMonthly.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMPercentageExpenditure","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMPercentageExpenditure""Chart: Percentage Expenditure""mdi-action-bookmark""../frmIYMPercentageExpenditure/frmIYMPercentageExpenditure.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMPercentageChange","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMPercentageChange""Chart: Percentage Change""mdi-action-bookmark""../frmIYMPercentageChange/frmIYMPercentageChange.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMProjectionsPerProgramme","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMProjectionsPerProgramme""Chart: Monthly Projections Analysis""mdi-action-bookmark""../frmIYMProjectionsPerProgramme/frmIYMProjectionsPerProgramme.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMExpenditurePerProgramme","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMExpenditurePerProgramme""Chart: Actual Monthly Expenditure Analysis""mdi-action-bookmark""../frmIYMExpenditurePerProgramme/frmIYMExpenditurePerProgramme.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMPercentageExpenditurePerProgramme","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMPercentageExpenditurePerProgramme""Chart: Monthly Percentage Expenditure Analysis""mdi-action-bookmark""../frmIYMPercentageExpenditurePerProgramme/frmIYMPercentageExpenditurePerProgramme.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMVariancesPerProgramme","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMVariancesPerProgramme""Chart: Monthly Variance Analysis""mdi-action-bookmark""../frmIYMVariancesPerProgramme/frmIYMVariancesPerProgramme.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMYTDExpenditurePerProgramme","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMYTDExpenditurePerProgramme""Chart: YTD Expenditure Analysis""mdi-action-bookmark""../frmIYMYTDExpenditurePerProgramme/frmIYMYTDExpenditurePerProgramme.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMYTDProjectionsPerProgramme","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMYTDProjectionsPerProgramme""Chart: YTD Projections Analysis""mdi-action-bookmark""../frmIYMYTDProjectionsPerProgramme/frmIYMYTDProjectionsPerProgramme.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmIYMYTDPercentageExpenditure","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmIYMYTDPercentageExpenditure""Chart: YTD Percentage Expenditure Analysis""mdi-action-bookmark""../frmIYMYTDPercentageExpenditure/frmIYMYTDPercentageExpenditure.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmProcPlanCalendar","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmProcPlanCalendar""Procurement Plan Calendar""mdi-action-bookmark""../frmProcPlanCalendar/frmProcPlanCalendar.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmProcPlanGantt","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReporting""frmProcPlanGantt""Procurement Project Plan""mdi-action-bookmark""../frmProcPlanGantt/frmProcPlanGantt.html")
        
    End If
        
    If ABMShared.HasPermission("frmReporting","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReporting")
        
    End If
        
    If ABMShared.HasPermission("frmReferences","canread") = True Then
            
    page.NavigationBar.AddSideBarItem("frmReferences""References""mdi-content-link""../frmReferences/frmReferences.html")
        
    End If
        
    If ABMShared.HasPermission("frmReferences","canread") = True Then
            
    page.NavigationBar.AddSideBarDivider
        
    End If
        
    If ABMShared.HasPermission("frmResources","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReferences""frmResources""Resources""mdi-action-bookmark""../frmResources/frmResources.html")
        
    End If
        
    If ABMShared.HasPermission("frmReferences","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReferences")
        
    End If
        
    If ABMShared.HasPermission("frmHolidays","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReferences""frmHolidays""Holidays""mdi-action-bookmark""../frmHolidays/frmHolidays.html")
        
    End If
        
    If ABMShared.HasPermission("frmReferences","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReferences")
        
    End If
        
    If ABMShared.HasPermission("frmClassOfGoods","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReferences""frmClassOfGoods""Class of Goods""mdi-action-bookmark""../frmClassOfGoods/frmClassOfGoods.html")
        
    End If
        
    If ABMShared.HasPermission("frmReferences","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReferences")
        
    End If
        
    If ABMShared.HasPermission("frmProcStatus","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmReferences""frmProcStatus""Procurement Status""mdi-action-bookmark""../frmProcStatus/frmProcStatus.html")
        
    End If
        
    If ABMShared.HasPermission("frmReferences","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmReferences")
        
    End If
        
    If ABMShared.HasPermission("frmUserAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarItem("frmUserAdministration""User Administration""mdi-action-account-circle""../frmUserAdministration/frmUserAdministration.html")
        
    End If
        
    If ABMShared.HasPermission("frmUserAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarDivider
        
    End If
        
    If ABMShared.HasPermission("frmUserGroups","canread") = True Then
            
    page.NavigationBar.AddSideBarSubItem("frmUserAdministration""frmUserGroups""User Groups""mdi-action-bookmark""../frmUserGroups/frmUserGroups.html")
        
    End If
        
    If ABMShared.HasPermission("frmUserAdministration","canread") = True Then
            
    page.NavigationBar.AddSideBarSubDivider("frmUserAdministration")
        
    End If
        
    'refresh the navigation bar
        page.NavigationBar.Refresh ' IMPORTANT
    End Sub
     
    joulongleu and Cableguy like this.
  8. Mashiane

    Mashiane Expert Licensed User

    Resetting & Sending User Passwords

    The next step was then to add functionality for administrators to Reset user passwords and then the app to send the new password to the user using their email address.

    To do this, I needed an EmailAccounts table in my database to store the respective email account details that the app will use. I didnt want to hard code these. I used the normal table for this. At anytime, there can be multiple accounts here but the app will just use 1 account that it first finds. One can use an 'Active' indicator though and just select that email account. This table also is only available for administrators ONLY as per permissions discussed above.

    EmailSettings.png

    This uses the SMTP functionality to send the emails...

    The schema of this EmailAccounts table is defined like this..

    EmailAccounts.png

    Then I updated my Users Table to have a password reset button...

    PasswordReset.png

    Now, I needed a way for my app to generate the passwords for the users. I added a new method to my ABMShared to do this... (based on some code I found here in the forum)

    Code:
    Sub RandomString(Length As Int, LowerCase As Boolean, UpperCase As Boolean, Numbers As Boolean, AdditionalChars As StringAs String
        
    Dim source As String
        
    If LowerCase = True Then
            source = source &
    "abcdefghijklmnopqrstuvwxyz"
        
    End If
        
    If UpperCase = True Then
            source = source &
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
        
    End If
        
    If Numbers = True Then
            source = source &
    "0123456789"
        
    End If
        
    If AdditionalChars.Length > 0 Then
            source = source&AdditionalChars
        
    End If
        
    Dim SB As StringBuilder
        SB.Initialize
        
    For i = 1 To Length
            
    Dim r As Int = Rnd(0,source.Length-1)
            SB.Append(source.SubString2(r,r+
    1))
        
    Next
        
    Return SB.ToString
    End Sub
    Sub GeneratePassword(IntNum As Int) As String
        
    Return RandomString(IntNum,True,True,True,"")
    End Sub
    And clicking the 'Reset' button does what?

    Code:
    ...
            
    Case 9
            
    If ABMShared.HasPermission("frmUsers","canupdate") = False Then Return
            
    'reset a user password
            'read the record from the database.
            Dim jSQL As SQL = ABMShared.SQLGet
            
    Dim m As Map
            m = ABMShared.SQLRecordRead(jSQL,
    "drpw_users""id", ActiveID)
            
    Dim email As String = m.getdefault("email","")
            
    Dim username As String = m.getdefault("username","")
            
    Dim np As String = ABMShared.GeneratePassword(8)
            m.put(
    "password", np)
            m.put(
    "useractive","1")
            
    If ABMShared.SQLRecordUpdate(jSQL,"drpw_users", m, "id", ActiveID) = True Then
                
    Dim sbody As String = "Good Day||Your Sign In credentials have been been changed.||Your username Is: " & username & "||Your Password Is: " & np & "||Development Team|"
                sbody = ABMShared.Replace(sbody,
    "|",CRLF)
                
    Dim subject As String = "DRPW: Sign In Credentials"
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastgreen""Password updated successfully."3000)
                
    'Refresh the table
                Filter = ""
                LastSort = 
    "ORDER BY fullname"
                
    Dim pager As ABMPagination = page.Component("drpw_usersPager")
                Loaddrpw_users(pager.GetActivePage())
                SendEmail(jSQL,
    smtp,email, subject, sbody)
            
    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(jSQL)...
    When the message is sent, tell the end user using a toast message, even if an error occurs.

    Code:
    Sub smtp_MessageSent(Success As Boolean)
        
    If Success = False Then
            myToastId = myToastId + 
    1
            
    page.ShowToast("toast" & myToastId, "toastred""Email could not be sent, please try again."3000)
        
    Else
            myToastId = myToastId + 
    1
            
    page.ShowToast("toast" & myToastId, "toastgreen""Email sent successfully."3000)
        
    End If
    End Sub
    Then the SendEmail method I used... This reads the email account from Email Accounts and uses that to send the email.

    Code:
    Sub SendEmail(jSQL As SQL, esmtp As SMTP, toEmail As String, subject As String, msg As String)
        
    'get first available account, there should be 1
        Dim id As String = ABMShared.SQLSelectSingleResult(jSQL, "select id from EmailAccounts Limit 1"Null)
        
    'read the email settings first
        Dim es As Map
        es = ABMShared.SQLRecordRead(jSQL,
    "EmailAccounts""id"id)
        
    Dim sserver As String = es.GetDefault("server","")
        
    Dim sport As String = es.GetDefault("port","25")
        
    Dim susername As String = es.GetDefault("username","")
        
    Dim spassword As String = es.GetDefault("password","")
        
    Dim sStartTLSMode As String = es.GetDefault("starttlsmode","0")
        
    Dim sUseSSL As String = es.GetDefault("usessl","0")
        
    Dim sadministrator As String = es.GetDefault("administrator","")
        
    'start sending the email
        esmtp.Initialize(sserver, sport, susername, spassword, "smtp")
        esmtp.To.Add(toEmail)
        esmtp.Body = msg
        esmtp.Subject = subject
        
    If sStartTLSMode = "1" Then
            esmtp.StartTLSMode = 
    True
        
    Else
            esmtp.StartTLSMode = 
    False
        
    End If
        
    If sUseSSL = "1" Then
            esmtp.UseSSL = 
    True
        
    Else
            esmtp.UseSSL = 
    False
        
    End If
        esmtp.Send
    End Sub
    After everything else, I received the email confirming the change..

    ConfirmEmail.png

    That's all then.

    PS: Password generator always generates a random password.
     
    inakigarm likes this.
  9. Mashiane

    Mashiane Expert Licensed User

    Testing Email Accounts

    I needed a way to test the email account created for use before I can use it. So I added a 'Test' button to my table as depicted below. There should be ONLY 1 email account recorded here for my app.

    EmailAccountsTable.png



    TestEmail.png

    And the code to send the 'Test' email...

    Code:
    Case 10
            
    If ABMShared.HasPermission("frmEmailAccounts","canread") = False Then Return
            
    'test email sending for email settings
            'read the record from the database.
            'Get connection from current pool if MySQL/MSSQL
            Dim jSQL As SQL = ABMShared.SQLGet
            
    'get first available account, there should be 1
            Dim id As String = ABMShared.SQLSelectSingleResult(jSQL, "select id from EmailAccounts Limit 1"Null)
            
    'read the email settings first
            Dim es As Map
            es = ABMShared.SQLRecordRead(jSQL,
    "EmailAccounts""id"id)
            
    Dim admin As String = es.GetDefault("administrator","")
            
    Dim sbody As String = "Hello||This email serves as a test email for the email account settings.||If you are reading this email, your app is ready to send emails."
            sbody = ABMShared.Replace(sbody,
    "|",CRLF)
            SendEmail(jSQL,
    smtp,admin, "DRPW: Test Email", sbody)
            
    'Close the connection to the database
            ABMShared.SQLClose(jSQL)
     
  10. Mashiane

    Mashiane Expert Licensed User

    When each group is deleted, the app also needs to delete the underlying permissions for the User Group, thus after a user confirms the Delete action, my YesNoProcess had to be updated to delete the permissions also..

    Code:
    Private Sub YesNoProcess(Tag As String)
        
    Select Case Tag
        
    Case "DeleteUserGroups"
            Filter = 
    ""
            LastSort = 
    ""
            
    'get the database connection
            Dim jSQL As SQL = ABMShared.SQLGet
            ABMShared.SQLRecordDelete(jSQL, 
    "GroupPermissions""groupid", ActiveID)
            
    Dim bDeleted As Boolean = ABMShared.SQLRecordDelete(jSQL, "UserGroups""id", ActiveID)
            ABMShared.SQLClose(jSQL)
            
    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("UserGroupsPager")
                LoadUserGroups(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
    Also when a new user group is added, the permissions needed to be created each time a new 'group' is added. As the permissions are per page, I needed a way to add each pages permissions and these initially should be set to off so that the administrator can give the respective permissions.

    Code:
    Private Sub AddGroupPermissions(jSQL As SQL, grpID As Int)
        
    Dim nrl As List
        nrl.Initialize
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMUpdate","IYM Update"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMTYD","Chart: YTD IYM"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMMonthly","Chart: Monthly IYM"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMMenu","In Year Monitoring"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMPercentageExpenditure","Chart: Percentage Expenditure"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMPercentageChange","Chart: Percentage Change"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMProjectionsPerProgramme","Chart: Monthly Projections Analysis"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMExpenditurePerProgramme","Chart: Actual Monthly Expenditure Analysis"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMPercentageExpenditurePerProgramme","Chart: Monthly Percentage Expenditure Analysis"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMVariancesPerProgramme","Chart: Monthly Variance Analysis"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMYTDExpenditurePerProgramme","Chart: YTD Expenditure Analysis"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMYTDProjectionsPerProgramme","Chart: YTD Projections Analysis"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYMYTDPercentageExpenditure","Chart: YTD Percentage Expenditure Analysis"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmResources","Resources"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmHolidays","Holidays"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmClassOfGoods","Class of Goods"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmProgrammes","IYM vs Procurement Plan"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmProcStatus","Procurement Status"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmProcPlan","Procurement Plan"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmPayments","Payments"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmProcPlanCalendar","Procurement Plan Calendar"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmProcPlanGantt","Procurement Project Plan"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmAddProgrammes","Programmes"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmIYM","IYM Programmes"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmAdministration","Administration"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmReporting","Reporting"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmReferences","References"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmUserAdministration","User Administration"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmUserGroups","User Groups"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmUsers","Users"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmGroupPermissions","Group Permissions"))
        nrl.add(ABMShared.PreparePermission(grpID,
    "frmEmailAccounts","Email Accounts"))
        ABMShared.SQLRecordInsertMaps(jSQL,
    "GroupPermissions",nrl)
    End Sub
    This method, only being called in my CreateUpdate method's 'new' action using the added group id.

    Code:
    Private Sub msUserGroupsCreateUpdate()
        Filter = 
    ""
        LastSort = 
    "ORDER BY groupname"
        
    'define a map to hold the form contents
        Dim m As Map
        
    'read the modal sheet contents to a map
        m = msUserGroupsGetContents
        
    'validate the form contents where required
        If msUserGroupsValidate(m) = False Then
            
    Return
        
    End If
        
    'the form contents are ok, continue with the save
        Dim strAction As String
        
    Dim UserGroupsid As String
        
    'determine the action we have been performing
        strAction = ABMShared.SessionStorageRead(page"action")
        UserGroupsid = ABMShared.SessionStorageRead(
    page"usergroupsid")
        
    Select Case strAction
        
    Case "new"
            
    If ABMShared.HasPermission("frmUserGroups","cancreate") = False Then Return
            
    Dim fk As String = ""
            
    If fk.Length > 0 Then
                fk = ABMShared.SessionStorageRead(
    page, fk.tolowercase)
                m.put(
    "", fk)
            
    End If
            
    'get the database connection
            Dim jSQL As SQL = ABMShared.SQLGet
            
    'insert the record
            UserGroupsid = ABMShared.SQLRecordInsert(jSQL, "UserGroups", m)
            
    If UserGroupsid > 0 Then
                ABMShared.SessionStorageSave(
    page"action""edit")
                ABMShared.SessionStorageSave(
    page"UserGroupsid", UserGroupsid)
                AddGroupPermissions(jSQL,UserGroupsid)
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastgreen""Record added successfully."3000)
                
    'Refresh the table
                Dim pager As ABMPagination = page.Component("UserGroupsPager")
                LoadUserGroups(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(jSQL)
        
    Case "edit"
            
    If ABMShared.HasPermission("frmUserGroups","canupdate") = False Then Return
            
    Dim fk As String = ""
            
    If fk.Length > 0 Then
                fk = ABMShared.SessionStorageRead(
    page, fk.tolowercase)
                m.put(
    "", fk)
            
    End If
            
    'get the database connection
            Dim jSQL As SQL = ABMShared.SQLGet
            
    If ABMShared.SQLRecordUpdate(jSQL,"UserGroups", m, "id", UserGroupsid) = True Then
                myToastId = myToastId + 
    1
                
    page.ShowToast("toast" & myToastId, "toastgreen""Record updated successfully."3000)
                
    'Refresh the table
                Dim pager As ABMPagination = page.Component("UserGroupsPager")
                LoadUserGroups(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(jSQL)
        
    End Select
    End Sub
    The code to prepare the permission, just creates a map to add to the list of records to add to the database.

    Code:
    Sub PreparePermission(sgrpID As String, spgName As String, spgTitle As StringAs Map
        
    Dim nr As Map
        nr.Initialize
        nr.Put(
    "groupid",sgrpID)
        nr.Put(
    "pagename", spgName)
        nr.Put(
    "pagetitle", spgTitle)
        nr.Put(
    "cancreate","0")
        nr.Put(
    "canread","0")
        nr.Put(
    "canupdate","0")
        nr.Put(
    "candelete","0")
        
    Return nr
    End Sub
    Attached are the latest files for Users, Sign In, Email Accounts

    Ta!
     

    Attached Files:

  11. Cableguy

    Cableguy Expert Licensed User

    Where do I find this module?
    esmtp.Initialize(sserver, sport, susername, spassword, "smtp")
     
  12. Mashiane

    Mashiane Expert Licensed User

    Reference the Net library which comes as part of the IDE. Ta!
     
    Cableguy and joulongleu like 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