# 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
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)

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;"

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()
End Sub```

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