Barangay Management System Development Part 7 – Sidebar and Statistics

Barangay Management System Development Part 7 – Sidebar and Statistics

Good day everyone, we are half way on our journey to design and develop an information system for barangay. This part of the tutorial will focus on the sidebar feature of the system where we will put the basic statistics of the system such as the population report per purok and the population grouped according to their gender.

Requirements:

You need to download first the resources from the previous tutorial which is the creation of main form and menu structure of our system.

Let us now proceed to the steps on how to establish our statistics feature of the project.

Step 1 – Add a picture box component in our main form.

Step 2 – Set the alignment into Align Left and appearance into Flat.

Step 3 –  You are free to give colors and design to your sidebar, in our case we will keep it as simple as possible.  The image below is the final design of our sidebar and statistics section.

Based on the image above, our sidebar will include the basic statistics such as the population of the barangay grouped by gender and the population of the barangay grouped by purok.

Step 4 – Next is to add a class module that will handle and store the codes on our statistics. Name and save our class module as Cls_Statistics.

Step 5 –  We will create a function that will count the number of male residents.

Function CountTotalMale() As Integer
If rs.State = adStateOpen Then rs.Close

    msql = " SELECT Count(tbl_residentinfo.gender) AS countMale" & _
    " From tbl_residentinfo" & _
    " Where tbl_residentinfo.gender=0"
    rs.Open msql, conn

If IsNull(rs(0).Value) Then
    CountTotalMale = 0
Else
    CountTotalMale = rs(0).Value
End If
End Function

Step 6 – Go back to the main form and we will create an object of the class statistics, after that we will use the object to call the function that we have created.

Dim clsData As New Cls_Statistics
Private Sub MDIForm_Load()
    lblmale.Caption = clsData.CountTotalMale
End Sub

Step 7 – we will now create the function that will count the number of female. Go back to the Cls_Statistics and paste the code.

Function CountTotalFemale() As Integer

If rs.State = adStateOpen Then rs.Close

    msql = " SELECT Count(tbl_residentinfo.gender) AS countFemale" & _
    " From tbl_residentinfo" & _
    " Where tbl_residentinfo.gender=1"
    rs.Open msql, conn

If IsNull(rs(0).Value) Then
    CountTotalFemale = 0
Else
    CountTotalFemale = rs(0).Value
End If
End Function

Step 8 – call the function

Private Sub MDIForm_Load()
    lblmale.Caption = clsData.CountTotalMale
    lblfemale.Caption = clsData.CountTotalFemale
End Sub

Step 10 –  We will add the value of male and female to get the total population. Paste the code in the load event of the form

lbltotal.Caption = CInt(lblmale.Caption) + CInt(lblfemale.Caption)

Step 10 – We will now create a function that will count the population by purok.

Public Sub DisplayPopulationByPurok(lstDay As ListView)

    Set mrs = New ADODB.Recordset
    mrs.CursorLocation = adUseServer

Dim lstItem As ListItem, a As Integer
If mrs.State = adStateOpen Then mrs.Close
msql = "SELECT tbl_purok.purok_name, Count(tbl_residentinfo.resident_id) AS CountOfID" & _
" FROM tbl_purok INNER JOIN tbl_residentinfo ON tbl_purok.purok_id = tbl_residentinfo.purok_id" & _
" GROUP BY tbl_purok.purok_name;"

 mrs.Open msql, conn, adOpenStatic, adLockOptimistic, adCmdText
   lstDay.ListItems.Clear
   Do While Not mrs.EOF
   a = a + 1

        Set lstItem = lstDay.ListItems.Add(, , a, 1, 1)
            lstItem.SubItems(1) = IIf(IsNull(mrs(0).Value), "", mrs(0).Value)
            lstItem.SubItems(2) = IIf(IsNull(mrs(1).Value), "", mrs(1).Value)
            mrs.MoveNext
            Loop
End Sub

Step 11 – we will create a code that will sum up the columns of population in the list view. Copy the code below in the main form

Public Sub ComputeTotal()
Dim a As Integer, b As Integer, stotal As Currency
a = lstcount.ListItems.Count
stotal = 0
For b = 1 To a
    stotal = Val(stotal) + CCur(lstcount.ListItems(b).SubItems(2))
Next
lblcount.Caption = Format(stotal, "#")
End Sub

Call the function in the load event of the form.

Private Sub MDIForm_Load()
    lblmale.Caption = clsData.CountTotalMale
    lblfemale.Caption = clsData.CountTotalFemale
    lbltotal.Caption = CInt(lblmale.Caption) + CInt(lblfemale.Caption)
    clsData.DisplayPopulationByPurok lstcount
    ComputeTotal
End Sub

Step 12 – the code below is the code for our refresh button. This will call all the functions that were executed in the load event of the form.

Private Sub cmdOk_Click()
    MDIForm_Load
End Sub

Congratulations you have successfully completed the part 7 of our tutorial. The full video tutorial will be uploaded in our youtube channel.

Download Source code

You may also visit the following articles related to the barangay information system.

Barangay Records Management Features and User Interface

City Wide Barangay Management System in PHP and MySQL

see you on the part 7 of this free tutorial.

iNetTutor.com