LOGIN CODE

Imports [Link]

Public Class frmLogin

Dim connection As New MySqlConnection("server=localhost; Database=osrms;

username=root; password=;")

' Function to handle the login process

Private Sub PerformLogin()
' Validate if both username and password fields are filled
If [Link]([Link]) Then
MsgBox("Please enter your username.", [Link], "Input Error")
[Link]() ' Set focus to the username field
Return
End If

If [Link]([Link]) Then
MsgBox("Please enter your password.", [Link], "Input Error")
[Link]() ' Set focus to the password field
Return
End If

Try
' Open the connection
If [Link] = [Link] Then
[Link]()
End If

' Create a new command with the SQL query

Dim command As New MySqlCommand("SELECT * FROM `tblusers` WHERE
`username` = @username AND `password` = @password", connection)
[Link]("@username", [Link]).Value =
[Link]
[Link]("@password", [Link]).Value =
[Link]

' Create a data adapter and set the select command

Dim adapter As New MySqlDataAdapter(command)
Dim table As New DataTable()
 ' Fill the DataTable using the data adapter
[Link](table)

' Check if any rows were returned

If [Link] = 0 Then
MsgBox("Invalid Username or Password", [Link], "Login Failed")
[Link] = "" 'Clear Previous input
[Link] = "" 'Clear Previous input
[Link]() ' Set focus to the username field

Else
' Open the dashboard form if the login is successful
[Link]()
[Link] = ""
[Link] = ""
[Link]()
[Link]() ' Optionally hide the login form
End If

Catch ex As Exception
MsgBox("An error occurred: " & [Link], [Link], "Error")
Finally
' Ensure the connection is closed
If [Link] = [Link] Then
[Link]()
End If
End Try
End Sub

' Event handler for the Login button click

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles [Link]
PerformLogin()
End Sub

' TextBox KeyPress Event to detect Enter key press

Private Sub txtUsername_KeyPress(sender As Object, e As KeyPressEventArgs) Handles
[Link], [Link]
' Check if the Enter key is pressed
If [Link] = ChrW([Link]) Then
[Link] = True ' Prevent the default Enter key behavior
PerformLogin() ' Call the login function
End If
End Sub
 ' Form Load Event to set initial focus to txtUsername and adjust TabIndex
Private Sub frmLogin_Load(sender As Object, e As EventArgs) Handles [Link]
' Set focus to the username field on form load
[Link]()

' Set TabIndex order for controls

[Link] = 0
[Link] = 1
[Link] = 2
End Sub
End Class

DASHBOARD CODE
Imports [Link]
Imports [Link]

Public Class frmDashboard

' Define the MySQL connection string

Dim connectionString As String = "server=localhost; Database=osrms; username=root;
password=;"
Dim connection As New MySqlConnection(connectionString)
Dim filterColumn As String = ""
Dim filterValue As String = ""

' Create PrintDocument and PrintPreviewDialog components

Private PrintDialog1 As New PrintPreviewDialog
Private dataToPrint As DataTable

Private Sub frmDashboard_Load(sender As Object, e As EventArgs) Handles [Link]

' Initial settings on form load
[Link]()

' Set default values

[Link] = 0 ' Set to "All" (assuming "All" is the first item)
[Link] = -1 ' Set to "None" or unselected

' Hide the filter value ComboBox initially

 [Link] = False

' Set tab order for controls

[Link] = 0
[Link] = 1
[Link] = 2
[Link] = 3
[Link] = 4
[Link] = 5
[Link] = 6
[Link] = 7
[Link] = 8

' Load residents data

LoadResidents()
End Sub
' Load residents data from the database
Private Sub LoadResidents(Optional searchQuery As String = "")
Try
[Link]()

' Define the base query

Dim query As String = "SELECT resident_number, first_name, middle_name, last_name,
age, contact_number, sector, employment_status, occupation, type_of_employment,
monthly_income, receiving_government_assistance, type_of_assistance FROM ResidentForm
WHERE is_archived = 0"

' Determine filter based on ComboBox selection

If [Link] IsNot Nothing Then
Select Case [Link]()
Case "Sector"
filterColumn = "sector"
If [Link] IsNot Nothing Then
filterValue = [Link]()
End If
Case "Employment Status"
filterColumn = "employment_status"
If [Link] IsNot Nothing Then
filterValue = [Link]()
End If
Case "Type of Assistance"
filterColumn = "type_of_assistance"
If [Link] IsNot Nothing Then
filterValue = [Link]()
 End If
Case "All"
' No filter logic needed when "All" is selected
filterColumn = ""
End Select
End If

' Modify the query if there is a search input and filter option selected
If Not [Link](searchQuery) Then
query &= " AND (first_name LIKE @search OR last_name LIKE @search OR
resident_number LIKE @search)"
End If

' Apply additional filter by the selected value if needed

If Not [Link](filterColumn) AndAlso Not [Link](filterValue)
Then
query &= " AND " & filterColumn & " = @filterValue"
End If

' Create the command with the query and parameters

Dim cmd As New MySqlCommand(query, connection)

' Add parameters for search and filter value

If Not [Link](searchQuery) Then
[Link]("@search", "%" & searchQuery & "%")
End If

If Not [Link](filterValue) Then

[Link]("@filterValue", filterValue)
End If

' Execute the query and fill the data into the DataGridView
Dim adapter As New MySqlDataAdapter(cmd)
Dim table As New DataTable()
[Link](table)
dataToPrint = table ' Store data for printing

' Bind the DataTable to the DataGridView

[Link] = table
' Set column headers
[Link]("resident_number").HeaderText = "Resident Number"
[Link]("first_name").HeaderText = "First Name"
[Link]("middle_name").HeaderText = "Middle Name"
[Link]("last_name").HeaderText = "Last Name"
 [Link]("age").HeaderText = "Age"
[Link]("contact_number").HeaderText = "Contact Number"
[Link]("sector").HeaderText = "Sector"
[Link]("employment_status").HeaderText = "Employment Status"
[Link]("occupation").HeaderText = "Occupation"
[Link]("type_of_employment").HeaderText = "Type of Employment"
[Link]("monthly_income").HeaderText = "Monthly Income"
[Link]("receiving_government_assistance").HeaderText = "Receiving
Assistance"
[Link]("type_of_assistance").HeaderText = "Type of Assistance"

Catch ex As Exception
[Link]("Error loading data: " & [Link], "Error",
[Link], [Link])
Finally
[Link]()
End Try
End Sub
' Search button click event
Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles [Link]
LoadResidents([Link]())
End Sub

' KeyPress event for pressing Enter in txtSearchResident

Private Sub txtSearchResident_KeyPress(sender As Object, e As KeyPressEventArgs)
Handles [Link]
If [Link] = ChrW([Link]) Then
LoadResidents([Link]())
[Link] = True
End If
End Sub
Private Sub btnPrint_Click(sender As Object, e As EventArgs) Handles [Link]
' Set the page to landscape
[Link] = True
' Maximize the PrintPreviewDialog window
[Link] = [Link]

' Set custom paper size to Short Bond (8.5 x 13 inches)

Dim shortBondSize As New [Link]("Short Bond", 850, 1300) ' 850 and 1300
are in hundredths of an inch (8.5 x 13 inches)
[Link] = shortBondSize

' Show the print dialog

[Link] = PrintDocument1
 [Link]()
End Sub

' Variables to keep track of the printing state

Private currentPageIndex As Integer = 0
Private totalRowsPrinted As Integer = 0

Private Sub PrintDocument1_PrintPage(sender As Object, e As PrintPageEventArgs)

Handles [Link]
' Define fonts for different sections
Dim font As New Font("Arial", 8) ' For data rows
Dim fontHeader As New Font("Arial", 9, [Link]) ' For column headers
Dim fontTitle As New Font("Arial", 16, [Link]) ' Larger font for the title

' Define printing area without margins

Dim availableWidth As Integer = [Link]
Dim availableHeight As Integer = [Link]
Dim startX As Integer = 0
Dim startY As Integer = 0
Dim offset As Integer = 40

' Define the report title

Dim reportTitle As String = "Residents Data"

' Define the margin from the top of the page

Dim topMargin As Integer = 40 ' Adjust this value for more or less space from the top

' Center the title horizontally and apply the top margin
Dim titleWidth As Integer = [Link](reportTitle, fontTitle).Width
Dim titleHeight As Integer = [Link](reportTitle, fontTitle).Height
Dim centerX As Integer = (availableWidth - titleWidth) / 2
Dim titleYPosition As Integer = topMargin ' Set Y position with top margin
[Link](reportTitle, fontTitle, [Link], centerX, titleYPosition)

' Adjust the start position for data to be below the title with additional spacing
startY = titleYPosition + titleHeight + 5

' Calculate maximum width for each column based on data and headers
Dim columnWidths As New List(Of Integer)()
For i As Integer = 0 To [Link] - 1
Dim maxColumnWidth As Integer =
[Link]([Link](i).HeaderText, fontHeader).Width
For Each row As DataGridViewRow In [Link]
If Not [Link] Then
 Dim cellText As String = [Link](i).[Link]()
maxColumnWidth = [Link](maxColumnWidth,
[Link](cellText, font).Width)
End If
Next
[Link](maxColumnWidth)
Next

' Scale column widths to fit the available print width

Dim totalWidth As Integer = [Link]()
Dim scaleFactor As Double = availableWidth / totalWidth

' Print column headers with scaling, borders, and center alignment
Dim currentX As Integer = startX
For i As Integer = 0 To [Link] - 1
Dim scaledColumnWidth As Integer = CInt(columnWidths(i) * scaleFactor)
Dim headerText As String = [Link](i).HeaderText
Dim headerTextWidth As Integer = [Link](headerText,
fontHeader).Width
Dim headerTextX As Integer = currentX + (scaledColumnWidth - headerTextWidth) / 2
[Link](headerText, fontHeader, [Link], headerTextX, startY +
offset)
[Link]([Link], currentX, startY + offset, scaledColumnWidth,
20)
currentX += scaledColumnWidth
Next

offset += 20
currentX = startX

' Print data rows with scaling, borders, and center alignment
Dim rowsPrinted As Integer = 0
Dim maxRowsPerPage As Integer = 30
For i As Integer = totalRowsPrinted To [Link] - 1
If [Link](i).IsNewRow Then Continue For

' Limit the number of rows per page

If rowsPrinted >= maxRowsPerPage Then
[Link] = True
currentPageIndex += 1
Return
End If

' Print each column in the current row

 For j As Integer = 0 To [Link] - 1
Dim scaledColumnWidth As Integer = CInt(columnWidths(j) * scaleFactor)
Dim cellText As String = [Link](i).Cells(j).[Link]()
Dim cellTextWidth As Integer = [Link](cellText, font).Width
Dim cellTextX As Integer = currentX + (scaledColumnWidth - cellTextWidth) / 2
[Link](cellText, font, [Link], cellTextX, startY + offset)
[Link]([Link], currentX, startY + offset, scaledColumnWidth,
CInt([Link]))
currentX += scaledColumnWidth
Next

offset += CInt([Link])
currentX = startX
rowsPrinted += 1
totalRowsPrinted += 1

' Check if we reached the end of the printable area

If offset >= availableHeight Then
[Link] = True
currentPageIndex += 1
Return
End If
Next

' Reset page control variables after printing is complete

[Link] = False
currentPageIndex = 0
totalRowsPrinted = 0
End Sub
' Add Resident button click event
Private Sub btnAddResident_Click(sender As Object, e As EventArgs) Handles
[Link]
[Link]()
[Link]()
End Sub

' Update Resident button click event

Private Sub btnUpdateResident_Click(sender As Object, e As EventArgs) Handles
[Link]
[Link]()
[Link]()
End Sub

' Archive Resident button click event

 Private Sub btnArchiveResident_Click(sender As Object, e As EventArgs) Handles
[Link]
[Link]()
[Link]()
End Sub

' Reports button click event

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles [Link]
[Link]()
[Link]()
End Sub

' Reset button click event

Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles [Link]
' Clear the search text box
[Link]()

' Reset the filter ComboBox (cmbFilterBy) to its default state

[Link] = 0 ' Set to "All" (assuming "All" is the first item)

' Reset the filter value ComboBox (cmbFilterValue) to its default state
[Link]() ' Remove any values in cmbFilterValue
[Link] = -1 ' Set to "None" or unselected

' Hide the cmbFilterValue as no filter is selected

[Link] = False

' Reload all the residents without any filters

LoadResidents()
[Link]()
End Sub
' Log Out button click event
Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles [Link]
[Link]()
[Link]()
End Sub
' ComboBox for FilterBy selected index change
Private Sub cmbFilterBy_SelectedIndexChanged(sender As Object, e As EventArgs) Handles
[Link]
' Clear previous items in the second ComboBox
[Link]()

' Ensure cmbFilterBy has a selected item before processing

If [Link] IsNot Nothing Then
 Select Case [Link]()
Case "Sector"
' Update sector options to values from 1 to 7
For i As Integer = 1 To 7
[Link]([Link]())
Next
Case "Employment Status"
[Link]("Employed")
[Link]("Unemployed")
[Link]("Self-Employed")
[Link]("Retired")
[Link]("Student")
' Add other employment status options
Case "Type of Assistance"
[Link]("4P's")
[Link]("Pension")
' Add other assistance types
Case "All" ' If "All" is selected, hide the filter value ComboBox
[Link] = False
Exit Sub
End Select

' Show the cmbFilterValue and enable it

[Link] = True
If [Link] > 0 Then
[Link] = 0
End If
Else
' If no selection in cmbFilterBy, hide the cmbFilterValue
[Link] = False
End If
End Sub
Private Sub ResetFilters()
' Clear the search input field
[Link]()

' Reset the ComboBox filter selection (clear it, set to default)
[Link] = -1 ' No selection

' Clear the filter values in cmbFilterValue

[Link]()
[Link] = -1 ' Set to null by clearing the selection
[Link] = False ' Disable the filter value ComboBox
 ' Optionally, reset the DataGridView if needed
[Link] = Nothing
[Link]()

' Reload the data (optional) to reset the grid view without any filter
LoadResidents()
End Sub
Private Sub cmbFilterValue_KeyPress(sender As Object, e As KeyPressEventArgs) Handles
[Link]
' Check if Enter key is pressed
If [Link] = ChrW([Link]) Then
' Trigger search based on the selected filter and value
If [Link] IsNot Nothing AndAlso [Link] IsNot
Nothing Then
LoadResidents([Link]()) ' Call search with current text
Else
[Link]("Please select a valid filter and value.", "Error",
[Link], [Link])
End If
[Link] = True ' Mark event as handled so no further processing happens
End If
End Sub
End Class

ADD RESIDENT CODE

Imports [Link]
Imports [Link]

Public Class frmAddResident

Dim connection As New MySqlConnection("server=localhost; Database=osrms;
username=root; password=;")

Private Sub frmAddResident_Load(sender As Object, e As EventArgs) Handles

[Link]
[Link] = False
End Sub
Private Sub frmAddResident_Shown(sender As Object, e As EventArgs) Handles [Link]
' Generate resident number when the form is shown
GenerateResidentNumber()
' Initially disable the occupation-related fields
 UpdateOccupationFields(False)
[Link] = False
End Sub
Private Sub GenerateResidentNumber()
Try
[Link]()
Dim lastIdCmd As New MySqlCommand("SELECT IFNULL(MAX(resident_number), 0) +
1 FROM ResidentForm", connection)
Dim residentNumber As Integer = Convert.ToInt32([Link]())
[Link]()

' Display the resident number in the txtResidentNumber textbox

[Link] = [Link]()
Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try
End Sub
' Function to reset form controls
Private Sub ResetControls()
For Each ctrl As Control In [Link]
' Exclude txtResidentNumber from being reset
If ctrl Is txtResidentNumber Then
Continue For
End If

If TypeOf ctrl Is TextBox Then

CType(ctrl, TextBox).Text = ""
ElseIf TypeOf ctrl Is ComboBox Then
CType(ctrl, ComboBox).SelectedIndex = -1
ElseIf TypeOf ctrl Is RadioButton Then
CType(ctrl, RadioButton).Checked = False
ElseIf TypeOf ctrl Is DateTimePicker Then
CType(ctrl, DateTimePicker).Value = [Link]
End If
[Link] = False
Next
End Sub
Private Sub CalculateTheAge()
' Get the selected date from DateTimePicker
Dim birthDate As Date = [Link]
' Get today's date
Dim today As Date = [Link]
 ' Calculate age
Dim age As Integer = [Link] - [Link]

' Adjust if the birth date has not occurred yet this year
If (birthDate > [Link](-age)) Then
age -= 1
End If

' Display the age in the TextBox

[Link] = [Link]()
End Sub
' Method to enable or disable occupation-related fields
Private Sub UpdateOccupationFields(isEnabled As Boolean)
[Link] = isEnabled
[Link] = isEnabled
[Link] = isEnabled

' Optionally clear selections when disabled

If Not isEnabled Then
[Link] = -1
[Link] = -1
[Link] = -1
End If
End Sub
' Method to enable or disable occupation-related fields
Private Sub UpdateEmploymentFields(isEnabled As Boolean)
[Link] = isEnabled
[Link] = isEnabled
[Link] = isEnabled

' Optionally clear selections when disabled

If Not isEnabled Then
[Link] = -1
[Link] = -1
[Link] = -1
End If
End Sub
Private Sub SaveResident()

Try
Dim cmd As New MySqlCommand("INSERT INTO ResidentForm (resident_number,
first_name, middle_name, last_name, sex, birthdate, age, contact_number, marital_status,
sector, employment_status, occupation, type_of_employment, monthly_income,
receiving_government_assistance, type_of_assistance) VALUES (@resident_number,
@first_name, @middle_name, @last_name, @sex, @birthdate, @age, @contact_number,
@marital_status, @sector, @employment_status, @occupation, @type_of_employment,
@monthly_income, @receiving_government_assistance, @type_of_assistance)", connection)

[Link]("@resident_number",
[Link]([Link]))
[Link]("@first_name", [Link])
[Link]("@middle_name", [Link])
[Link]("@last_name", [Link])
[Link]("@sex", [Link]())
[Link]("@birthdate", [Link])
[Link]("@age", [Link]([Link]))
[Link]("@contact_number", [Link])
[Link]("@marital_status",
[Link]())
[Link]("@sector", [Link]())
[Link]("@employment_status",
[Link]())

' Check if an occupation is selected

If [Link] IsNot Nothing Then
[Link]("@occupation",
[Link]())
Else
[Link]("@occupation", [Link])
End If

' Check if a type of employment is selected

If [Link] IsNot Nothing Then
[Link]("@type_of_employment",
[Link]())
Else
[Link]("@type_of_employment", [Link])
End If

' Check if a monthly income is selected

If [Link] IsNot Nothing Then
[Link]("@monthly_income",
[Link]())
Else
[Link]("@monthly_income", [Link])
End If

' Check if a type of assistance is selected

 If [Link] IsNot Nothing Then
[Link]("@type_of_assistance",
[Link]())
Else
[Link]("@type_of_assistance", [Link])
End If

[Link]("@receiving_government_assistance",
If([Link], "Yes", "No"))

[Link]()
Dim rowsAffected As Integer = [Link]()
[Link]()

If rowsAffected > 0 Then

[Link]("Resident data saved successfully!", "Success",
[Link], [Link])
Else
[Link]("Failed to save resident data.", "Error", [Link],
[Link])
End If
Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try
End Sub
Private Sub CalculateAge()
' Get the selected date from DateTimePicker
Dim birthDate As Date = [Link]
' Get today's date
Dim today As Date = [Link]
' Calculate age
Dim age As Integer = [Link] - [Link]

' Adjust if the birth date has not occurred yet this year
If birthDate > [Link](-age) Then
age -= 1
End If

' Ensure age is not less than 0

If age < 18 Then
age = age
End If
 ' Display the age in the TextBox
[Link] = [Link]()
End Sub
Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles [Link]
[Link]()
[Link]()

End Sub

Private Sub btnSubmit_Click_1(sender As Object, e As EventArgs) Handles [Link]

Dim namePattern As String = "^[a-zA-Z ]+$"

' Validate First Name

If [Link]([Link]) Then
[Link]("Please enter a value for First Name.", "Validation Error",
[Link], [Link])
[Link]()
Return
ElseIf Not [Link]([Link], namePattern) Then
[Link]("First Name should contain letters only.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Middle Name

If [Link]([Link]) Then
[Link]("Please enter a value for Middle Name.", "Validation Error",
[Link], [Link])
[Link]()
Return
ElseIf Not [Link]([Link], namePattern) Then
[Link]("Middle Name should contain letters only.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Last Name

If [Link]([Link]) Then
[Link]("Please enter a value for Last Name.", "Validation Error",
[Link], [Link])
 [Link]()
Return
ElseIf Not [Link]([Link], namePattern) Then
[Link]("Last Name should contain letters only.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Contact Number

If [Link]([Link]) Then
[Link]("Please enter a value for Contact Number.", "Validation Error",
[Link], [Link])
[Link]()
Return
Else
Dim contactNumber As Long
If Not [Link]([Link], contactNumber) Then
[Link]("Please enter a valid Contact Number.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If
End If

' Validate Sex

If [Link] = -1 Then
[Link]("Please select a value for Sex.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Marital Status

If [Link] = -1 Then
[Link]("Please select a value for Marital Status.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Sector

If [Link] = -1 Then
 [Link]("Please select a value for Sector.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Employment Status

If [Link] = -1 Then
[Link]("Please select a value for Employment Status.", "Validation Error",
[Link], [Link])
[Link]()
Return
ElseIf [Link]() = "Employed" OrElse
[Link]() = "Self-Employed" Then
' Validate Occupation
If [Link] = -1 Then
[Link]("Please select a value for Occupation.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Type of Employment

If [Link] = -1 Then
[Link]("Please select a value for Type of Employment.", "Validation
Error", [Link], [Link])
[Link]()
Return
End If

' Validate Monthly Income

If [Link] = -1 Then
[Link]("Please select a value for Monthly Income.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If
End If

' Validate Age

Dim age As Integer
If Not [Link]([Link], age) OrElse age < 18 Then
[Link]("Please enter a valid birthdate resulting in age not less than 18.",
"Validation Error", [Link], [Link])
 [Link]()
Return
End If

' Validate Government Assistance

If Not [Link] AndAlso Not [Link] Then
[Link]("Please select if receiving government assistance.", "Validation
Error", [Link], [Link])
[Link]()
Return
ElseIf [Link] AndAlso [Link] = -1 Then
[Link]("Please select a type of assistance.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' If validation passes, save resident details

SaveResident()
ResetControls()
GenerateResidentNumber()
[Link]()
[Link]()
End Sub

Private Sub dtpBirthDate_ValueChanged_1(sender As Object, e As EventArgs) Handles

[Link]
CalculateAge()
End Sub

Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles [Link]

[Link]()
[Link]()
ResetControls() ' Call ResetControls here to reset the form
End Sub

Private Sub btnResidents_Click(sender As Object, e As EventArgs) Handles

[Link]
[Link]()
[Link]()
End Sub
Private Sub rbNo_CheckedChanged_1(sender As Object, e As EventArgs) Handles
[Link]
 ' Disable cmbTypeOfAssistance and clear selection when "No" is selected
If [Link] Then
[Link] = False
[Link] = -1 ' Clear selection
End If
End Sub

Private Sub cmbEmploymentStatus_SelectedIndexChanged(sender As Object, e As

EventArgs) Handles [Link]
' Check if an item is selected to avoid NullReferenceException
If [Link] Is Nothing Then
UpdateOccupationFields(False)
Return
End If

' Enable or disable fields based on employment status

Dim status As String = [Link]()

' Enable if "Employed" or "Self-Employed" is selected, otherwise disable

If status = "Employed" Or status = "Self-Employed" Then
UpdateOccupationFields(True)
Else
UpdateOccupationFields(False)
End If
End Sub

Private Sub rbYes_CheckedChanged(sender As Object, e As EventArgs) Handles

[Link]
' Enable cmbTypeOfAssistance when "Yes" is selected
If [Link] Then
[Link] = True
End If
End Sub
End Class

UPDATE RESIDENT CODE

Imports [Link]
Imports [Link]
Public Class frmUpdateResident
 Dim connection As New MySqlConnection("server=localhost; Database=osrms;
username=root; password=;")

' Form Load Event

Private Sub frmUpdateResident_Load(sender As Object, e As EventArgs) Handles
[Link]
' Disable all textboxes and comboboxes except the search function
DisableTextAndComboBoxes()
[Link] = True

' Hide and disable cmbResidents on form load

[Link] = False
[Link] = False
End Sub

' Method to disable all textboxes and comboboxes

Private Sub DisableTextAndComboBoxes()
For Each ctrl As Control In [Link]
If TypeOf ctrl Is TextBox OrElse TypeOf ctrl Is ComboBox Then
[Link] = False
End If
Next
End Sub

Private Sub EnableAllFields()

For Each ctrl As Control In [Link]
If TypeOf ctrl Is TextBox Then
CType(ctrl, TextBox).Enabled = True
ElseIf TypeOf ctrl Is ComboBox Then
CType(ctrl, ComboBox).Enabled = True
ElseIf TypeOf ctrl Is RadioButton Then
CType(ctrl, RadioButton).Enabled = True
ElseIf TypeOf ctrl Is DateTimePicker Then
CType(ctrl, DateTimePicker).Enabled = True
End If
Next

' Additional logic: Disable specific controls if necessary

[Link] = [Link]
End Sub
' Function to fetch resident data based on search
Private Sub SearchResident()
Try
 Using cmd As New MySqlCommand("SELECT * FROM ResidentForm WHERE
first_name LIKE @search OR middle_name LIKE @search OR last_name LIKE @search",
connection)
[Link]("@search", "%" & [Link] & "%")

[Link]()
Using reader As MySqlDataReader = [Link]()
Dim residents As New List(Of String)
While [Link]()
[Link](reader("resident_number").ToString() & " - " &
reader("first_name").ToString() & " " & reader("last_name").ToString())
End While

' Reset DataSource before populating

[Link] = Nothing
If [Link] > 1 Then
[Link] = residents
[Link] = True
[Link] = True
ElseIf [Link] = 1 Then
[Link]()
[Link]()
[Link] = "SELECT * FROM ResidentForm WHERE
resident_number = @resident_number"
[Link]("@resident_number", residents(0).Split(" - ")(0))
Using reader2 As MySqlDataReader = [Link]()
If [Link]() Then
FillForm(reader2)
EnableAllFields()
End If
End Using
[Link] = False
Else
[Link]("No residents found.", "Search Result",
[Link], [Link])
[Link] = False
[Link] = False
End If
End Using
End Using
[Link]()
Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
 [Link]()
End Try
End Sub
' Fill the form fields with resident data
Private Sub FillForm(reader As MySqlDataReader)
[Link] = reader("resident_number").ToString()
[Link] = reader("first_name").ToString()
[Link] = reader("middle_name").ToString()
[Link] = reader("last_name").ToString()

' Safely select items

[Link] = [Link](reader("sex").ToString())
[Link] = [Link](reader("birthdate"))
[Link] = reader("age").ToString()
[Link] = reader("contact_number").ToString()
[Link] =
[Link](reader("marital_status").ToString())
[Link] = [Link](reader("sector").ToString())
[Link] =
[Link](reader("employment_status").ToString())

' Handle occupation

If [Link]() = "Employed" Or
[Link]() = "Self-Employed" Then
[Link] =
[Link](reader("occupation").ToString())
[Link] =
[Link](reader("type_of_employment").ToString())
[Link] =
[Link](reader("monthly_income").ToString())
Else
UpdateOccupationFields(False)
End If

' Assistance
If reader("receiving_government_assistance").ToString() = "Yes" Then
[Link] = True
[Link] =
[Link](reader("type_of_assistance").ToString())
[Link] = True
Else
[Link] = True
[Link] = False
End If
 End Sub

Private Sub cmbResidents_SelectedIndexChanged(sender As Object, e As EventArgs)

Handles [Link]
Dim selectedResident As String = [Link]()
Dim residentNumber As String = [Link](" - ")(0)

Try
Using connection As New MySqlConnection("server=localhost; Database=osrms;
username=root; password=;")
Dim cmd As New MySqlCommand("SELECT * FROM ResidentForm WHERE
resident_number = @resident_number", connection)
[Link]("@resident_number", residentNumber)

[Link]()
Using reader As MySqlDataReader = [Link]()
If [Link]() Then
FillForm(reader)
EnableAllFields() ' Enable all fields for editing
End If
End Using
End Using
Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
End Try
End Sub

' Save the updated resident data to the database

Private Sub UpdateResident()
Try
Dim cmd As New MySqlCommand("UPDATE ResidentForm SET " &
"first_name = @first_name, " &
"middle_name = @middle_name, " &
"last_name = @last_name, " &
"sex = @sex, " &
"birthdate = @birthdate, " &
"age = @age, " &
"contact_number = @contact_number, " &
"marital_status = @marital_status, " &
"sector = @sector, " &
 "employment_status = @employment_status, " &
"occupation = @occupation, " &
"type_of_employment = @type_of_employment, " &
"monthly_income = @monthly_income, " &
"receiving_government_assistance =
@receiving_government_assistance, " &
"type_of_assistance = @type_of_assistance " &
"WHERE resident_number = @resident_number", connection)

' Adding general resident details

[Link]("@resident_number", [Link])
[Link]("@first_name", [Link])
[Link]("@middle_name", [Link])
[Link]("@last_name", [Link])
[Link]("@sex", [Link]())
[Link]("@birthdate", [Link])
[Link]("@age", [Link]([Link]))
[Link]("@contact_number", [Link])
[Link]("@marital_status",
[Link]())
[Link]("@sector", [Link]())
[Link]("@employment_status",
[Link]())

' Determine if occupation-related fields should be set to NULL

Dim employmentStatus As String = [Link]()
If employmentStatus = "Employed" Or employmentStatus = "Self-Employed" Then
[Link]("@occupation", If([Link] <>
-1, [Link](), [Link]))
[Link]("@type_of_employment",
If([Link] <> -1,
[Link](), [Link]))
[Link]("@monthly_income",
If([Link] <> -1, [Link](),
[Link]))
Else
' Set occupation-related fields to NULL if status is not employed
[Link]("@occupation", [Link])
[Link]("@type_of_employment", [Link])
[Link]("@monthly_income", [Link])
End If

' Handling assistance status

If [Link] Then
 [Link]("@receiving_government_assistance", "Yes")
[Link]("@type_of_assistance",
If([Link] <> -1, [Link](),
[Link]))
Else
[Link]("@receiving_government_assistance", "No")
[Link]("@type_of_assistance", [Link])
End If

[Link]()
Dim rowsAffected As Integer = [Link]()
[Link]()

If rowsAffected > 0 Then

[Link]("Resident data updated successfully!", "Success",
[Link], [Link])
Else
[Link]("Failed to update resident data.", "Error",
[Link], [Link])
End If

Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try
End Sub

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles [Link]

Dim namePattern As String = "^[a-zA-Z ]+$"

' Validate Search Field

If [Link]([Link]) Then
[Link]("Please enter a value to search.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate First Name

If [Link]([Link]) Then
[Link]("Please enter a value for First Name.", "Validation Error",
[Link], [Link])
 [Link]()
Return
ElseIf Not [Link]([Link], namePattern) Then
[Link]("First Name should contain letters only.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Middle Name

If [Link]([Link]) Then
[Link]("Please enter a value for Middle Name.", "Validation Error",
[Link], [Link])
[Link]()
Return
ElseIf Not [Link]([Link], namePattern) Then
[Link]("Middle Name should contain letters only.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Last Name

If [Link]([Link]) Then
[Link]("Please enter a value for Last Name.", "Validation Error",
[Link], [Link])
[Link]()
Return
ElseIf Not [Link]([Link], namePattern) Then
[Link]("Last Name should contain letters only.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Contact Number

If [Link]([Link]) Then
[Link]("Please enter a value for Contact Number.", "Validation Error",
[Link], [Link])
[Link]()
Return
Else
Dim contactNumber As Long
If Not [Link]([Link], contactNumber) Then
 [Link]("Please enter a valid Contact Number.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If
End If

' Validate Sex

If [Link] = -1 Then
[Link]("Please select a value for Sex.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Marital Status

If [Link] = -1 Then
[Link]("Please select a value for Marital Status.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Sector

If [Link] = -1 Then
[Link]("Please select a value for Sector.", "Validation Error",
[Link], [Link])
[Link]()
Return
End If

' Validate Birthdate and Calculate Age

Dim age As Integer
If [Link] > [Link] Then
[Link]("Birthdate cannot be in the future.", "Validation Error",
[Link], [Link])
[Link]()
Return
Else
age = [Link] - [Link]
If [Link] > [Link](-age) Then
age -= 1
End If
End If
 ' Validate Government Assistance
Dim receivingAssistance As String = If([Link], "Yes", "No")
Dim typeOfAssistance As Object = If([Link] AndAlso
[Link] <> -1, [Link](),
[Link])

' Handle Employment Data

Dim employmentStatus As String = If([Link] <> -1,
[Link](), [Link])
Dim occupation As String = If([Link] <> -1,
[Link](), [Link])
Dim typeOfEmployment As String = If([Link] <> -1,
[Link](), [Link])
Dim monthlyIncome As String = If([Link] <> -1,
[Link](), [Link])

' Update the database

Dim cmd As New MySqlCommand("UPDATE ResidentForm SET " &
"first_name = @first_name, " &
"middle_name = @middle_name, " &
"last_name = @last_name, " &
"contact_number = @contact_number, " &
"sex = @sex, " &
"age = @age, " &
"marital_status = @marital_status, " &
"sector = @sector, " &
"receiving_government_assistance = @receiving_assistance, " &
"type_of_assistance = @type_of_assistance, " &
"employment_status = @employment_status, " &
"occupation = @occupation, " &
"type_of_employment = @type_of_employment, " &
"monthly_income = @monthly_income " &
"WHERE resident_number = @resident_number", connection)

' Add parameters to the command

[Link]("@first_name", [Link])
[Link]("@middle_name", [Link])
[Link]("@last_name", [Link])
[Link]("@contact_number", [Link])
[Link]("@sex", [Link]())
[Link]("@age", age)
[Link]("@marital_status",
[Link]())
 [Link]("@sector", [Link]())
[Link]("@receiving_assistance", receivingAssistance)
[Link]("@type_of_assistance", typeOfAssistance)
[Link]("@employment_status",
If([Link](employmentStatus), [Link], employmentStatus))
[Link]("@occupation", If([Link](occupation),
[Link], occupation))
[Link]("@type_of_employment",
If([Link](typeOfEmployment), [Link], typeOfEmployment))
[Link]("@monthly_income",
If([Link](monthlyIncome), [Link], monthlyIncome))
[Link]("@resident_number",
[Link]([Link]))

' Execute the command

Try
[Link]()
Dim rowsAffected As Integer = [Link]()
[Link]()

If rowsAffected > 0 Then

[Link]("Resident data updated successfully!", "Success",
[Link], [Link])
Else
[Link]("Failed to update resident data.", "Error",
[Link], [Link])
End If
Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try

' Close the form and show the dashboard

[Link]()
[Link]()
End Sub

' Search button click event

Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles [Link]
SearchResident()
End Sub
 ' Search on Enter key press
Private Sub txtSearchResident_KeyPress(sender As Object, e As KeyPressEventArgs)
Handles [Link]
If [Link] = ChrW([Link]) Then
SearchResident()
End If
End Sub

Private Sub cmbEmploymentStatus_SelectedIndexChanged(sender As Object, e As

EventArgs) Handles [Link]
Dim selectedStatus As String = [Link]()

' Check employment status

If selectedStatus = "Employed" Or selectedStatus = "Self-Employed" Then
' Enable occupation-related fields
UpdateOccupationFields(True)
Else
' Disable occupation-related fields and clear their values
UpdateOccupationFields(False)
[Link] = -1
[Link] = -1
[Link] = -1
End If
End Sub

' Update occupation-related fields visibility

Private Sub UpdateOccupationFields(visible As Boolean)
[Link] = visible
[Link] = visible
[Link] = visible
End Sub
Private Sub FillResidentForm()
' Assuming resident data is already fetched from the database
' Example: For demonstration, assume resident's info is available as variables
Dim receivingAssistance As String = "Yes" ' or "No"
Dim typeOfAssistance As String = "Financial Aid" ' example value

' If receiving government assistance (Yes), enable cmbTypeOfAssistance

If receivingAssistance = "Yes" Then
[Link] = True
[Link] = False
[Link] = True
 [Link] = typeOfAssistance ' Populate
cmbTypeOfAssistance
Else
[Link] = False
[Link] = True
[Link] = False
[Link] = -1 ' Clear the selection
End If
End Sub
Private Sub rbYes_CheckedChanged(sender As Object, e As EventArgs) Handles
[Link]
If [Link] Then
[Link] = True
End If
End Sub

Private Sub rbNo_CheckedChanged(sender As Object, e As EventArgs) Handles

[Link]
If [Link] Then
[Link] = False
[Link] = -1 ' Clear the selection when "No" is selected
End If
End Sub
Private Sub CalculateTheAge()
' Get the selected date from DateTimePicker
Dim birthDate As Date = [Link]
' Get today's date
Dim today As Date = [Link]
' Calculate age
Dim age As Integer = [Link] - [Link]

' Adjust if the birth date has not occurred yet this year
If (birthDate > [Link](-age)) Then
age -= 1
End If

' Display the age in the TextBox

[Link] = [Link]()
End Sub
Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles [Link]
[Link]()
[Link]()
ResetControls() ' Call ResetControls here to reset the form
End Sub
 Private Sub btnResidents_Click(sender As Object, e As EventArgs) Handles
[Link]
[Link]()
[Link]()
End Sub
' Function to reset form controls
Private Sub ResetControls()
For Each ctrl As Control In [Link]
' Exclude txtResidentNumber from being reset
If ctrl Is txtResidentNumber Then
Continue For
End If

If TypeOf ctrl Is TextBox Then

CType(ctrl, TextBox).Text = ""
ElseIf TypeOf ctrl Is ComboBox Then
CType(ctrl, ComboBox).SelectedIndex = -1
ElseIf TypeOf ctrl Is RadioButton Then
CType(ctrl, RadioButton).Checked = False
ElseIf TypeOf ctrl Is DateTimePicker Then
CType(ctrl, DateTimePicker).Value = [Link]
End If
[Link] = False
Next
End Sub

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles [Link]

[Link]()
[Link]()
End Sub

Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles [Link]

[Link]()
[Link]()
End Sub

Private Sub dtpBirthDate_ValueChanged(sender As Object, e As EventArgs) Handles

[Link]
CalculateTheAge()
End Sub
End Class
ARCHIVE RESIDENT CODE
Imports [Link]

Public Class frmArchiveResident

Dim connection As New MySqlConnection("server=localhost; Database=osrms;
username=root; password=;")

' Method to load residents data into DataGridView

Private Sub frmArchiveResident_Load(sender As Object, e As EventArgs) Handles
[Link]
LoadResidents()
End Sub

' Method to load all residents into DataGridView

Private Sub LoadResidents()
Try
[Link]()

' Fetch data from the database

Dim query As String = "SELECT resident_number, first_name, middle_name, last_name,
is_archived FROM ResidentForm"
Dim cmd As New MySqlCommand(query, connection)
Dim adapter As New MySqlDataAdapter(cmd)
Dim table As New DataTable()
[Link](table)

' Bind the DataTable to the DataGridView

[Link] = table

' Set column headers

[Link]("resident_number").HeaderText = "Resident Number"
[Link]("first_name").HeaderText = "First Name"
[Link]("middle_name").HeaderText = "Middle Name"
[Link]("last_name").HeaderText = "Last Name"
[Link]("is_archived").HeaderText = "Archived Status"

' Hide the "is_archived" column if not needed in view

[Link]("is_archived").Visible = False

' Add the Archive and Retrieve Button columns if they do not exist
If Not [Link]("btnArchive") Then
Dim btnArchive As New DataGridViewButtonColumn()
 [Link] = "btnArchive"
[Link] = "Action"
[Link] = "Archive"
[Link] = True
[Link](btnArchive)
End If

If Not [Link]("btnRetrieve") Then

Dim btnRetrieve As New DataGridViewButtonColumn()
[Link] = "btnRetrieve"
[Link] = "Action"
[Link] = "Retrieve"
[Link] = True
[Link](btnRetrieve)
End If

' Adjust column widths for better UI

[Link] = [Link]

[Link]()
Catch ex As Exception
[Link]("Error loading data: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try
End Sub
' Method to search residents by name or resident number
Private Sub SearchResidents(searchValue As String)
Try
[Link]()

' SQL query to search by resident number or name (first, middle, last)
Dim query As String = "SELECT resident_number, first_name, middle_name, last_name,
is_archived FROM ResidentForm WHERE resident_number LIKE @searchValue OR
first_name LIKE @searchValue OR middle_name LIKE @searchValue OR last_name LIKE
@searchValue"
Dim cmd As New MySqlCommand(query, connection)
[Link]("@searchValue", "%" & searchValue & "%")

Dim adapter As New MySqlDataAdapter(cmd)

Dim table As New DataTable()
[Link](table)

' Bind the search result to the DataGridView

 [Link] = table

[Link]()
Catch ex As Exception
[Link]("Error searching data: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try
End Sub

' Archive a resident

Private Sub ArchiveResident(residentNumber As Integer)
Try
[Link]()
Dim query As String = "UPDATE ResidentForm SET is_archived = 1 WHERE
resident_number = @residentNumber"
Dim cmd As New MySqlCommand(query, connection)
[Link]("@residentNumber", residentNumber)
Dim result = [Link]()
[Link]()

If result > 0 Then

[Link]("Resident archived successfully.", "Success",
[Link], [Link])
LoadResidents() ' Refresh DataGridView
Else
[Link]("Failed to archive resident.", "Error", [Link],
[Link])
End If
Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try
End Sub

' Retrieve a resident

Private Sub RetrieveResident(residentNumber As Integer)
Try
[Link]()
Dim query As String = "UPDATE ResidentForm SET is_archived = 0 WHERE
resident_number = @residentNumber"
Dim cmd As New MySqlCommand(query, connection)
[Link]("@residentNumber", residentNumber)
 Dim result = [Link]()
[Link]()

If result > 0 Then

[Link]("Resident retrieved successfully.", "Success",
[Link], [Link])
LoadResidents() ' Refresh DataGridView
Else
[Link]("Failed to retrieve resident.", "Error", [Link],
[Link])
End If
Catch ex As Exception
[Link]("An error occurred: " & [Link], "Error",
[Link], [Link])
[Link]()
End Try
End Sub
Private Sub btnResidents_Click(sender As Object, e As EventArgs)
[Link]()
[Link]()
End Sub

Private Sub dgvResidents_CellContentClick_1(sender As Object, e As

DataGridViewCellEventArgs) Handles [Link]
' Ensure the click is on a valid cell
If [Link] >= 0 Then
' Fetch the Resident Number of the clicked row
Dim residentNumber As Integer =
Convert.ToInt32([Link]([Link]).Cells("resident_number").Value)
Dim isArchived As Boolean =
[Link]([Link]([Link]).Cells("is_archived").Value)
Dim columnName As String = [Link]([Link]).Name

' Archive Button Clicked

If columnName = "btnArchive" AndAlso Not isArchived Then
' Show confirmation dialog for archiving
Dim result As DialogResult = [Link](
"Are you sure you want to archive this Resident?",
"Confirm Archive",
[Link],
[Link]
)

' Proceed only if user confirms

 If result = [Link] Then
ArchiveResident(residentNumber)
End If
End If

' Retrieve Button Clicked

If columnName = "btnRetrieve" AndAlso isArchived Then
' Show confirmation dialog for retrieval
Dim result As DialogResult = [Link](
"Are you sure you want to retrieve this Resident?",
"Confirm Retrieve",
[Link],
[Link]
)

' Proceed only if user confirms

If result = [Link] Then
RetrieveResident(residentNumber)
End If
End If
End If
End Sub

Private Sub btnRefresh_Click_1(sender As Object, e As EventArgs) Handles

[Link]
LoadResidents()
[Link]()
[Link] = ""
End Sub
' Method to load data into DataGridView with color formatting for archived data
' Event to format the DataGridView cells based on the archived status
Private Sub dgvResidents_CellFormatting1(sender As Object, e As
DataGridViewCellFormattingEventArgs) Handles [Link]
' Check if the column exists in the DataGridView to prevent errors
If [Link]("is_archived") IsNot Nothing AndAlso [Link] >= 0 Then
Dim isArchived As Boolean =
[Link]([Link]([Link]).Cells("is_archived").Value)

' If the row is archived, change its background color to red

If isArchived Then
[Link]([Link]).[Link] = [Link]
[Link]([Link]).[Link] = [Link] ' Optional:
change text color for better readability
 End If
End If
End Sub

Private Sub btnResidents_Click_1(sender As Object, e As EventArgs) Handles

[Link]
[Link]()
[Link]()
End Sub

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles [Link]

[Link]()
[Link]()
End Sub

Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles [Link]

[Link]()
[Link]()
End Sub
' Click event for btnFind to perform the search
Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles [Link]
SearchResidents([Link]())
End Sub
' KeyPress event for txtSearchResident to search on Enter key press
Private Sub txtSearchResident_KeyPress(sender As Object, e As KeyPressEventArgs)
Handles [Link]
If [Link] = ChrW([Link]) Then
SearchResidents([Link]())
End If
End Sub
End Class

REPORTS CODE
Imports [Link]
Imports [Link]

Public Class frmReports

' Define the MySQL connection string

 Dim connectionString As String = "server=localhost; Database=osrms; username=root;
password=;"

' Variables to hold the counts

Dim totalResidents As Integer
Dim totalEmployed As Integer
Dim totalStudent As Integer
Dim totalUnemployed As Integer
Dim totalRetired As Integer

Private Sub frmReports_Load(sender As Object, e As EventArgs) Handles [Link]

' Focus on btnResidents
[Link]()
[Link] = 0

' Fetch data

FetchResidentCount()
FetchEmployedCount()
FetchStudentCount()
FetchUnemployedCount()
FetchRetiredCount()

' Display data in a pie chart

DisplayPieChart()
End Sub

' Method to fetch the total number of non-archived residents

Private Sub FetchResidentCount()
Dim query As String = "SELECT COUNT(*) FROM ResidentForm WHERE is_archived = 0"
Try
Using connection As New MySqlConnection(connectionString)
[Link]()
Dim command As New MySqlCommand(query, connection)
totalResidents = Convert.ToInt32([Link]())
[Link] = [Link]()
End Using
Catch ex As Exception
[Link]("Error: " & [Link])
End Try
End Sub

' Method to fetch the total number of employed and self-employed residents
Private Sub FetchEmployedCount()
 Dim query As String = "SELECT COUNT(*) FROM ResidentForm WHERE
employment_status IN ('Employed', 'Self-Employed') AND is_archived = 0"
Try
Using connection As New MySqlConnection(connectionString)
[Link]()
Dim command As New MySqlCommand(query, connection)
totalEmployed = Convert.ToInt32([Link]())
[Link] = [Link]()
End Using
Catch ex As Exception
[Link]("Error: " & [Link])
End Try
End Sub

' Method to fetch the total number of students

Private Sub FetchStudentCount()
Dim query As String = "SELECT COUNT(*) FROM ResidentForm WHERE
employment_status = 'Student' AND is_archived = 0"
Try
Using connection As New MySqlConnection(connectionString)
[Link]()
Dim command As New MySqlCommand(query, connection)
totalStudent = Convert.ToInt32([Link]())
[Link] = [Link]()
End Using
Catch ex As Exception
[Link]("Error: " & [Link])
End Try
End Sub

' Method to fetch the total number of unemployed residents

Private Sub FetchUnemployedCount()
Dim query As String = "SELECT COUNT(*) FROM ResidentForm WHERE
employment_status = 'Unemployed' AND is_archived = 0"
Try
Using connection As New MySqlConnection(connectionString)
[Link]()
Dim command As New MySqlCommand(query, connection)
totalUnemployed = Convert.ToInt32([Link]())
[Link] = [Link]()
End Using
Catch ex As Exception
[Link]("Error: " & [Link])
End Try
 End Sub

' Method to fetch the total number of retired residents

Private Sub FetchRetiredCount()
Dim query As String = "SELECT COUNT(*) FROM ResidentForm WHERE
employment_status = 'Retired' AND is_archived = 0"
Try
Using connection As New MySqlConnection(connectionString)
[Link]()
Dim command As New MySqlCommand(query, connection)
totalRetired = Convert.ToInt32([Link]())
[Link] = [Link]()
End Using
Catch ex As Exception
[Link]("Error: " & [Link])
End Try
End Sub

' Method to display data in a pie chart with percentage totaling 100%
Private Sub DisplayPieChart()
' Clear existing series and data points
[Link]()

' Calculate the sum of all status counts

Dim totalForChart As Integer = totalEmployed + totalStudent + totalUnemployed +
totalRetired

' Avoid division by zero if no data is available

If totalForChart = 0 Or totalResidents = 0 Then
[Link]("No data available to display in the chart.")
Return
End If

' Calculate the percentage for each category

Dim employedPercentage As Double = (totalEmployed / totalResidents) * 100
Dim studentPercentage As Double = (totalStudent / totalResidents) * 100
Dim unemployedPercentage As Double = (totalUnemployed / totalResidents) * 100
Dim retiredPercentage As Double = (totalRetired / totalResidents) * 100

' Create a new series for the pie chart

Dim series As New Series("Residents")
[Link] = [Link]

' Add data points with manually calculated percentages

 [Link]("Employed/Self-Employed", employedPercentage)
[Link]("Students", studentPercentage)
[Link]("Unemployed", unemployedPercentage)
[Link]("Retired", retiredPercentage)

' Add the series to the chart

[Link](series)

' Set chart title

[Link]()
Dim chartTitle As New Title("Occupation Status Distribution")
[Link] = New Font("Host Grotesk", 16, [Link])
[Link](chartTitle)

' Customize the appearance of data labels

[Link] = True
[Link] = "{0:0.##}%"
[Link] = New Font("Host Grotesk", 12, [Link])
[Link] = [Link] ' Optional: Change label text color

' Customize legend appearance

If [Link] > 0 Then
[Link](0).Font = New Font("Host Grotesk", 10, [Link])
End If

' Set colors for better visualization

[Link](0).Color = [Link]
[Link](1).Color = [Link]
[Link](2).Color = [Link]
[Link](3).Color = [Link]

' Hide axis labels and grid lines for a clean look
[Link](0).[Link] = False
[Link](0).[Link] = False
[Link](0).[Link] = False
[Link](0).[Link] = False
End Sub

' Button click handlers

Private Sub btnResidents_Click(sender As Object, e As EventArgs) Handles
[Link]
[Link]()
 [Link]()
End Sub

Private Sub btnLogOut_Click_1(sender As Object, e As EventArgs) Handles [Link]

[Link]()
[Link]()
End Sub

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles [Link]

[Link]()
End Sub
End Class