LOGIN CODE

Imports MySql.Data.MySqlClient

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 String.IsNullOrWhiteSpace(txtUsername.Text) Then
MsgBox("Please enter your username.", MsgBoxStyle.Exclamation, "Input Error")
txtUsername.Focus() ' Set focus to the username field
Return
End If

If String.IsNullOrWhiteSpace(txtPassword.Text) Then
MsgBox("Please enter your password.", MsgBoxStyle.Exclamation, "Input Error")
txtPassword.Focus() ' Set focus to the password field
Return
End If

Try
' Open the connection
If connection.State = ConnectionState.Closed Then
connection.Open()
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)
command.Parameters.Add("@username", MySqlDbType.VarChar).Value =
txtUsername.Text
command.Parameters.Add("@password", MySqlDbType.VarChar).Value =
txtPassword.Text

' 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
adapter.Fill(table)

' Check if any rows were returned

If table.Rows.Count = 0 Then
MsgBox("Invalid Username or Password", MsgBoxStyle.Critical, "Login Failed")
txtUsername.Text = "" 'Clear Previous input
txtPassword.Text = "" 'Clear Previous input
txtUsername.Focus() ' Set focus to the username field

Else
' Open the dashboard form if the login is successful
frmDashboard.Show()
txtUsername.Text = ""
txtPassword.Text = ""
txtUsername.Focus()
Me.Hide() ' Optionally hide the login form
End If

Catch ex As Exception
MsgBox("An error occurred: " & ex.Message, MsgBoxStyle.Critical, "Error")
Finally
' Ensure the connection is closed
If connection.State = ConnectionState.Open Then
connection.Close()
End If
End Try
End Sub

' Event handler for the Login button click

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
PerformLogin()
End Sub

' TextBox KeyPress Event to detect Enter key press

Private Sub txtUsername_KeyPress(sender As Object, e As KeyPressEventArgs) Handles
txtUsername.KeyPress, txtPassword.KeyPress
' Check if the Enter key is pressed
If e.KeyChar = ChrW(Keys.Enter) Then
e.Handled = 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 MyBase.Load
' Set focus to the username field on form load
txtUsername.Focus()

' Set TabIndex order for controls

txtUsername.TabIndex = 0
txtPassword.TabIndex = 1
btnLogin.TabIndex = 2
End Sub
End Class

DASHBOARD CODE
Imports MySql.Data.MySqlClient
Imports System.Drawing.Printing

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 MyBase.Load

' Initial settings on form load
txtSearchResident.Focus()

' Set default values

cmbFilterBy.SelectedIndex = 0 ' Set to "All" (assuming "All" is the first item)
cmbFilterValue.SelectedIndex = -1 ' Set to "None" or unselected

' Hide the filter value ComboBox initially

 cmbFilterValue.Visible = False

' Set tab order for controls

txtSearchResident.TabIndex = 0
btnAddResident.TabIndex = 1
btnUpdateResident.TabIndex = 2
btnPrint.TabIndex = 3
btnFind.TabIndex = 4
btnReset.TabIndex = 5
btnArchiveResident.TabIndex = 6
btnReports.TabIndex = 7
btnLogOut.TabIndex = 8

' Load residents data

LoadResidents()
End Sub
' Load residents data from the database
Private Sub LoadResidents(Optional searchQuery As String = "")
Try
connection.Open()

' 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 cmbFilterBy.SelectedItem IsNot Nothing Then
Select Case cmbFilterBy.SelectedItem.ToString()
Case "Sector"
filterColumn = "sector"
If cmbFilterValue.SelectedItem IsNot Nothing Then
filterValue = cmbFilterValue.SelectedItem.ToString()
End If
Case "Employment Status"
filterColumn = "employment_status"
If cmbFilterValue.SelectedItem IsNot Nothing Then
filterValue = cmbFilterValue.SelectedItem.ToString()
End If
Case "Type of Assistance"
filterColumn = "type_of_assistance"
If cmbFilterValue.SelectedItem IsNot Nothing Then
filterValue = cmbFilterValue.SelectedItem.ToString()
 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 String.IsNullOrEmpty(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 String.IsNullOrEmpty(filterColumn) AndAlso Not String.IsNullOrEmpty(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 String.IsNullOrEmpty(searchQuery) Then
cmd.Parameters.AddWithValue("@search", "%" & searchQuery & "%")
End If

If Not String.IsNullOrEmpty(filterValue) Then

cmd.Parameters.AddWithValue("@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()
adapter.Fill(table)
dataToPrint = table ' Store data for printing

' Bind the DataTable to the DataGridView

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

Catch ex As Exception
MessageBox.Show("Error loading data: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
connection.Close()
End Try
End Sub
' Search button click event
Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles btnFind.Click
LoadResidents(txtSearchResident.Text.Trim())
End Sub

' KeyPress event for pressing Enter in txtSearchResident

Private Sub txtSearchResident_KeyPress(sender As Object, e As KeyPressEventArgs)
Handles txtSearchResident.KeyPress
If e.KeyChar = ChrW(Keys.Enter) Then
LoadResidents(txtSearchResident.Text.Trim())
e.Handled = True
End If
End Sub
Private Sub btnPrint_Click(sender As Object, e As EventArgs) Handles btnPrint.Click
' Set the page to landscape
PrintDocument1.DefaultPageSettings.Landscape = True
' Maximize the PrintPreviewDialog window
PrintDialog1.WindowState = FormWindowState.Maximized

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

Dim shortBondSize As New Printing.PaperSize("Short Bond", 850, 1300) ' 850 and 1300
are in hundredths of an inch (8.5 x 13 inches)
PrintDocument1.DefaultPageSettings.PaperSize = shortBondSize

' Show the print dialog

PrintDialog1.Document = PrintDocument1
 PrintDialog1.ShowDialog()
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 PrintDocument1.PrintPage
' Define fonts for different sections
Dim font As New Font("Arial", 8) ' For data rows
Dim fontHeader As New Font("Arial", 9, FontStyle.Bold) ' For column headers
Dim fontTitle As New Font("Arial", 16, FontStyle.Bold) ' Larger font for the title

' Define printing area without margins

Dim availableWidth As Integer = e.PageBounds.Width
Dim availableHeight As Integer = e.PageBounds.Height
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 = TextRenderer.MeasureText(reportTitle, fontTitle).Width
Dim titleHeight As Integer = TextRenderer.MeasureText(reportTitle, fontTitle).Height
Dim centerX As Integer = (availableWidth - titleWidth) / 2
Dim titleYPosition As Integer = topMargin ' Set Y position with top margin
e.Graphics.DrawString(reportTitle, fontTitle, Brushes.Black, 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 dgvResidents.Columns.Count - 1
Dim maxColumnWidth As Integer =
TextRenderer.MeasureText(dgvResidents.Columns(i).HeaderText, fontHeader).Width
For Each row As DataGridViewRow In dgvResidents.Rows
If Not row.IsNewRow Then
 Dim cellText As String = row.Cells(i).FormattedValue.ToString()
maxColumnWidth = Math.Max(maxColumnWidth,
TextRenderer.MeasureText(cellText, font).Width)
End If
Next
columnWidths.Add(maxColumnWidth)
Next

' Scale column widths to fit the available print width

Dim totalWidth As Integer = columnWidths.Sum()
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 dgvResidents.Columns.Count - 1
Dim scaledColumnWidth As Integer = CInt(columnWidths(i) * scaleFactor)
Dim headerText As String = dgvResidents.Columns(i).HeaderText
Dim headerTextWidth As Integer = TextRenderer.MeasureText(headerText,
fontHeader).Width
Dim headerTextX As Integer = currentX + (scaledColumnWidth - headerTextWidth) / 2
e.Graphics.DrawString(headerText, fontHeader, Brushes.Black, headerTextX, startY +
offset)
e.Graphics.DrawRectangle(Pens.Black, 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 dgvResidents.Rows.Count - 1
If dgvResidents.Rows(i).IsNewRow Then Continue For

' Limit the number of rows per page

If rowsPrinted >= maxRowsPerPage Then
e.HasMorePages = True
currentPageIndex += 1
Return
End If

' Print each column in the current row

 For j As Integer = 0 To dgvResidents.Columns.Count - 1
Dim scaledColumnWidth As Integer = CInt(columnWidths(j) * scaleFactor)
Dim cellText As String = dgvResidents.Rows(i).Cells(j).FormattedValue.ToString()
Dim cellTextWidth As Integer = TextRenderer.MeasureText(cellText, font).Width
Dim cellTextX As Integer = currentX + (scaledColumnWidth - cellTextWidth) / 2
e.Graphics.DrawString(cellText, font, Brushes.Black, cellTextX, startY + offset)
e.Graphics.DrawRectangle(Pens.Black, currentX, startY + offset, scaledColumnWidth,
CInt(dgvResidents.RowTemplate.Height))
currentX += scaledColumnWidth
Next

offset += CInt(dgvResidents.RowTemplate.Height)
currentX = startX
rowsPrinted += 1
totalRowsPrinted += 1

' Check if we reached the end of the printable area

If offset >= availableHeight Then
e.HasMorePages = True
currentPageIndex += 1
Return
End If
Next

' Reset page control variables after printing is complete

e.HasMorePages = False
currentPageIndex = 0
totalRowsPrinted = 0
End Sub
' Add Resident button click event
Private Sub btnAddResident_Click(sender As Object, e As EventArgs) Handles
btnAddResident.Click
frmAddResident.Show()
Me.Close()
End Sub

' Update Resident button click event

Private Sub btnUpdateResident_Click(sender As Object, e As EventArgs) Handles
btnUpdateResident.Click
frmUpdateResident.Show()
Me.Close()
End Sub

' Archive Resident button click event

 Private Sub btnArchiveResident_Click(sender As Object, e As EventArgs) Handles
btnArchiveResident.Click
frmArchiveResident.Show()
Me.Close()
End Sub

' Reports button click event

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles btnReports.Click
frmReports.Show()
Me.Close()
End Sub

' Reset button click event

Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles btnReset.Click
' Clear the search text box
txtSearchResident.Clear()

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

cmbFilterBy.SelectedIndex = 0 ' Set to "All" (assuming "All" is the first item)

' Reset the filter value ComboBox (cmbFilterValue) to its default state
cmbFilterValue.Items.Clear() ' Remove any values in cmbFilterValue
cmbFilterValue.SelectedIndex = -1 ' Set to "None" or unselected

' Hide the cmbFilterValue as no filter is selected

cmbFilterValue.Visible = False

' Reload all the residents without any filters

LoadResidents()
txtSearchResident.Focus()
End Sub
' Log Out button click event
Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles btnLogOut.Click
frmLogin.Show()
Me.Close()
End Sub
' ComboBox for FilterBy selected index change
Private Sub cmbFilterBy_SelectedIndexChanged(sender As Object, e As EventArgs) Handles
cmbFilterBy.SelectedIndexChanged
' Clear previous items in the second ComboBox
cmbFilterValue.Items.Clear()

' Ensure cmbFilterBy has a selected item before processing

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

' Show the cmbFilterValue and enable it

cmbFilterValue.Visible = True
If cmbFilterValue.Items.Count > 0 Then
cmbFilterValue.SelectedIndex = 0
End If
Else
' If no selection in cmbFilterBy, hide the cmbFilterValue
cmbFilterValue.Visible = False
End If
End Sub
Private Sub ResetFilters()
' Clear the search input field
txtSearchResident.Clear()

' Reset the ComboBox filter selection (clear it, set to default)
cmbFilterBy.SelectedIndex = -1 ' No selection

' Clear the filter values in cmbFilterValue

cmbFilterValue.Items.Clear()
cmbFilterValue.SelectedIndex = -1 ' Set to null by clearing the selection
cmbFilterValue.Enabled = False ' Disable the filter value ComboBox
 ' Optionally, reset the DataGridView if needed
dgvResidents.DataSource = Nothing
dgvResidents.Rows.Clear()

' 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
cmbFilterValue.KeyPress
' Check if Enter key is pressed
If e.KeyChar = ChrW(Keys.Enter) Then
' Trigger search based on the selected filter and value
If cmbFilterBy.SelectedItem IsNot Nothing AndAlso cmbFilterValue.SelectedItem IsNot
Nothing Then
LoadResidents(txtSearchResident.Text.Trim()) ' Call search with current text
Else
MessageBox.Show("Please select a valid filter and value.", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
e.Handled = True ' Mark event as handled so no further processing happens
End If
End Sub
End Class

ADD RESIDENT CODE

Imports MySql.Data.MySqlClient
Imports System.Text.RegularExpressions

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

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

' Display the resident number in the txtResidentNumber textbox

txtResidentNumber.Text = residentNumber.ToString()
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try
End Sub
' Function to reset form controls
Private Sub ResetControls()
For Each ctrl As Control In Me.Controls
' 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 = DateTime.Now
End If
cmbTypeOfAssistance.Enabled = False
Next
End Sub
Private Sub CalculateTheAge()
' Get the selected date from DateTimePicker
Dim birthDate As Date = dtpBirthDate.Value
' Get today's date
Dim today As Date = Date.Today
 ' Calculate age
Dim age As Integer = today.Year - birthDate.Year

' Adjust if the birth date has not occurred yet this year
If (birthDate > today.AddYears(-age)) Then
age -= 1
End If

' Display the age in the TextBox

txtAge.Text = age.ToString()
End Sub
' Method to enable or disable occupation-related fields
Private Sub UpdateOccupationFields(isEnabled As Boolean)
cmbOccupation.Enabled = isEnabled
cmbTypeOfEmployment.Enabled = isEnabled
cmbMonthlyIncome.Enabled = isEnabled

' Optionally clear selections when disabled

If Not isEnabled Then
cmbOccupation.SelectedIndex = -1
cmbTypeOfEmployment.SelectedIndex = -1
cmbMonthlyIncome.SelectedIndex = -1
End If
End Sub
' Method to enable or disable occupation-related fields
Private Sub UpdateEmploymentFields(isEnabled As Boolean)
cmbOccupation.Enabled = isEnabled
cmbTypeOfEmployment.Enabled = isEnabled
cmbMonthlyIncome.Enabled = isEnabled

' Optionally clear selections when disabled

If Not isEnabled Then
cmbOccupation.SelectedIndex = -1
cmbTypeOfEmployment.SelectedIndex = -1
cmbMonthlyIncome.SelectedIndex = -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)

cmd.Parameters.AddWithValue("@resident_number",
Integer.Parse(txtResidentNumber.Text))
cmd.Parameters.AddWithValue("@first_name", txtFirstName.Text)
cmd.Parameters.AddWithValue("@middle_name", txtMiddleName.Text)
cmd.Parameters.AddWithValue("@last_name", txtLastName.Text)
cmd.Parameters.AddWithValue("@sex", cmbSex.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@birthdate", dtpBirthDate.Value.Date)
cmd.Parameters.AddWithValue("@age", Integer.Parse(txtAge.Text))
cmd.Parameters.AddWithValue("@contact_number", txtContactNumber.Text)
cmd.Parameters.AddWithValue("@marital_status",
cmbMaritalStatus.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@sector", cmbSector.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@employment_status",
cmbEmploymentStatus.SelectedItem.ToString())

' Check if an occupation is selected

If cmbOccupation.SelectedItem IsNot Nothing Then
cmd.Parameters.AddWithValue("@occupation",
cmbOccupation.SelectedItem.ToString())
Else
cmd.Parameters.AddWithValue("@occupation", DBNull.Value)
End If

' Check if a type of employment is selected

If cmbTypeOfEmployment.SelectedItem IsNot Nothing Then
cmd.Parameters.AddWithValue("@type_of_employment",
cmbTypeOfEmployment.SelectedItem.ToString())
Else
cmd.Parameters.AddWithValue("@type_of_employment", DBNull.Value)
End If

' Check if a monthly income is selected

If cmbMonthlyIncome.SelectedItem IsNot Nothing Then
cmd.Parameters.AddWithValue("@monthly_income",
cmbMonthlyIncome.SelectedItem.ToString())
Else
cmd.Parameters.AddWithValue("@monthly_income", DBNull.Value)
End If

' Check if a type of assistance is selected

 If cmbTypeOfAssistance.SelectedItem IsNot Nothing Then
cmd.Parameters.AddWithValue("@type_of_assistance",
cmbTypeOfAssistance.SelectedItem.ToString())
Else
cmd.Parameters.AddWithValue("@type_of_assistance", DBNull.Value)
End If

cmd.Parameters.AddWithValue("@receiving_government_assistance",
If(rbYes.Checked, "Yes", "No"))

connection.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
connection.Close()

If rowsAffected > 0 Then

MessageBox.Show("Resident data saved successfully!", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Failed to save resident data.", "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
End If
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try
End Sub
Private Sub CalculateAge()
' Get the selected date from DateTimePicker
Dim birthDate As Date = dtpBirthDate.Value
' Get today's date
Dim today As Date = Date.Today
' Calculate age
Dim age As Integer = today.Year - birthDate.Year

' Adjust if the birth date has not occurred yet this year
If birthDate > today.AddYears(-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
txtAge.Text = age.ToString()
End Sub
Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles btnLogOut.Click
frmLogin.Show()
Me.Close()

End Sub

Private Sub btnSubmit_Click_1(sender As Object, e As EventArgs) Handles btnSubmit.Click

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

' Validate First Name

If String.IsNullOrEmpty(txtFirstName.Text) Then
MessageBox.Show("Please enter a value for First Name.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtFirstName.Focus()
Return
ElseIf Not Regex.IsMatch(txtFirstName.Text, namePattern) Then
MessageBox.Show("First Name should contain letters only.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtFirstName.Focus()
Return
End If

' Validate Middle Name

If String.IsNullOrEmpty(txtMiddleName.Text) Then
MessageBox.Show("Please enter a value for Middle Name.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtMiddleName.Focus()
Return
ElseIf Not Regex.IsMatch(txtMiddleName.Text, namePattern) Then
MessageBox.Show("Middle Name should contain letters only.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtMiddleName.Focus()
Return
End If

' Validate Last Name

If String.IsNullOrEmpty(txtLastName.Text) Then
MessageBox.Show("Please enter a value for Last Name.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
 txtLastName.Focus()
Return
ElseIf Not Regex.IsMatch(txtLastName.Text, namePattern) Then
MessageBox.Show("Last Name should contain letters only.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtLastName.Focus()
Return
End If

' Validate Contact Number

If String.IsNullOrWhiteSpace(txtContactNumber.Text) Then
MessageBox.Show("Please enter a value for Contact Number.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtContactNumber.Focus()
Return
Else
Dim contactNumber As Long
If Not Long.TryParse(txtContactNumber.Text, contactNumber) Then
MessageBox.Show("Please enter a valid Contact Number.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtContactNumber.Focus()
Return
End If
End If

' Validate Sex

If cmbSex.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Sex.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbSex.Focus()
Return
End If

' Validate Marital Status

If cmbMaritalStatus.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Marital Status.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbMaritalStatus.Focus()
Return
End If

' Validate Sector

If cmbSector.SelectedIndex = -1 Then
 MessageBox.Show("Please select a value for Sector.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbSector.Focus()
Return
End If

' Validate Employment Status

If cmbEmploymentStatus.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Employment Status.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbEmploymentStatus.Focus()
Return
ElseIf cmbEmploymentStatus.SelectedItem.ToString() = "Employed" OrElse
cmbEmploymentStatus.SelectedItem.ToString() = "Self-Employed" Then
' Validate Occupation
If cmbOccupation.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Occupation.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbOccupation.Focus()
Return
End If

' Validate Type of Employment

If cmbTypeOfEmployment.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Type of Employment.", "Validation
Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbTypeOfEmployment.Focus()
Return
End If

' Validate Monthly Income

If cmbMonthlyIncome.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Monthly Income.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbMonthlyIncome.Focus()
Return
End If
End If

' Validate Age

Dim age As Integer
If Not Integer.TryParse(txtAge.Text, age) OrElse age < 18 Then
MessageBox.Show("Please enter a valid birthdate resulting in age not less than 18.",
"Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
 dtpBirthDate.Focus()
Return
End If

' Validate Government Assistance

If Not rbYes.Checked AndAlso Not rbNo.Checked Then
MessageBox.Show("Please select if receiving government assistance.", "Validation
Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
rbYes.Focus()
Return
ElseIf rbYes.Checked AndAlso cmbTypeOfAssistance.SelectedIndex = -1 Then
MessageBox.Show("Please select a type of assistance.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbTypeOfAssistance.Focus()
Return
End If

' If validation passes, save resident details

SaveResident()
ResetControls()
GenerateResidentNumber()
frmDashboard.Show()
Me.Hide()
End Sub

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

dtpBirthDate.ValueChanged
CalculateAge()
End Sub

Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click

frmDashboard.Show()
Me.Close()
ResetControls() ' Call ResetControls here to reset the form
End Sub

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

btnResidents.Click
frmDashboard.Show()
Me.Close()
End Sub
Private Sub rbNo_CheckedChanged_1(sender As Object, e As EventArgs) Handles
rbNo.CheckedChanged
 ' Disable cmbTypeOfAssistance and clear selection when "No" is selected
If rbNo.Checked Then
cmbTypeOfAssistance.Enabled = False
cmbTypeOfAssistance.SelectedIndex = -1 ' Clear selection
End If
End Sub

Private Sub cmbEmploymentStatus_SelectedIndexChanged(sender As Object, e As

EventArgs) Handles cmbEmploymentStatus.SelectedIndexChanged
' Check if an item is selected to avoid NullReferenceException
If cmbEmploymentStatus.SelectedItem Is Nothing Then
UpdateOccupationFields(False)
Return
End If

' Enable or disable fields based on employment status

Dim status As String = cmbEmploymentStatus.SelectedItem.ToString()

' 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

rbYes.CheckedChanged
' Enable cmbTypeOfAssistance when "Yes" is selected
If rbYes.Checked Then
cmbTypeOfAssistance.Enabled = True
End If
End Sub
End Class

UPDATE RESIDENT CODE

Imports MySql.Data.MySqlClient
Imports System.Text.RegularExpressions
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
MyBase.Load
' Disable all textboxes and comboboxes except the search function
DisableTextAndComboBoxes()
txtSearchResident.Enabled = True

' Hide and disable cmbResidents on form load

cmbResidents.Visible = False
cmbResidents.Enabled = False
End Sub

' Method to disable all textboxes and comboboxes

Private Sub DisableTextAndComboBoxes()
For Each ctrl As Control In Me.Controls
If TypeOf ctrl Is TextBox OrElse TypeOf ctrl Is ComboBox Then
ctrl.Enabled = False
End If
Next
End Sub

Private Sub EnableAllFields()

For Each ctrl As Control In Me.Controls
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

cmbTypeOfAssistance.Enabled = rbYes.Checked
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)
cmd.Parameters.AddWithValue("@search", "%" & txtSearchResident.Text & "%")

connection.Open()
Using reader As MySqlDataReader = cmd.ExecuteReader()
Dim residents As New List(Of String)
While reader.Read()
residents.Add(reader("resident_number").ToString() & " - " &
reader("first_name").ToString() & " " & reader("last_name").ToString())
End While

' Reset DataSource before populating

cmbResidents.DataSource = Nothing
If residents.Count > 1 Then
cmbResidents.DataSource = residents
cmbResidents.Visible = True
cmbResidents.Enabled = True
ElseIf residents.Count = 1 Then
reader.Close()
cmd.Parameters.Clear()
cmd.CommandText = "SELECT * FROM ResidentForm WHERE
resident_number = @resident_number"
cmd.Parameters.AddWithValue("@resident_number", residents(0).Split(" - ")(0))
Using reader2 As MySqlDataReader = cmd.ExecuteReader()
If reader2.Read() Then
FillForm(reader2)
EnableAllFields()
End If
End Using
cmbResidents.Visible = False
Else
MessageBox.Show("No residents found.", "Search Result",
MessageBoxButtons.OK, MessageBoxIcon.Information)
cmbResidents.Visible = False
cmbResidents.Enabled = False
End If
End Using
End Using
connection.Close()
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
 connection.Close()
End Try
End Sub
' Fill the form fields with resident data
Private Sub FillForm(reader As MySqlDataReader)
txtResidentNumber.Text = reader("resident_number").ToString()
txtFirstName.Text = reader("first_name").ToString()
txtMiddleName.Text = reader("middle_name").ToString()
txtLastName.Text = reader("last_name").ToString()

' Safely select items

cmbSex.SelectedIndex = cmbSex.FindStringExact(reader("sex").ToString())
dtpBirthDate.Value = Convert.ToDateTime(reader("birthdate"))
txtAge.Text = reader("age").ToString()
txtContactNumber.Text = reader("contact_number").ToString()
cmbMaritalStatus.SelectedIndex =
cmbMaritalStatus.FindStringExact(reader("marital_status").ToString())
cmbSector.SelectedIndex = cmbSector.FindStringExact(reader("sector").ToString())
cmbEmploymentStatus.SelectedIndex =
cmbEmploymentStatus.FindStringExact(reader("employment_status").ToString())

' Handle occupation

If cmbEmploymentStatus.SelectedItem.ToString() = "Employed" Or
cmbEmploymentStatus.SelectedItem.ToString() = "Self-Employed" Then
cmbOccupation.SelectedIndex =
cmbOccupation.FindStringExact(reader("occupation").ToString())
cmbTypeOfEmployment.SelectedIndex =
cmbTypeOfEmployment.FindStringExact(reader("type_of_employment").ToString())
cmbMonthlyIncome.SelectedIndex =
cmbMonthlyIncome.FindStringExact(reader("monthly_income").ToString())
Else
UpdateOccupationFields(False)
End If

' Assistance
If reader("receiving_government_assistance").ToString() = "Yes" Then
rbYes.Checked = True
cmbTypeOfAssistance.SelectedIndex =
cmbTypeOfAssistance.FindStringExact(reader("type_of_assistance").ToString())
cmbTypeOfAssistance.Enabled = True
Else
rbNo.Checked = True
cmbTypeOfAssistance.Enabled = False
End If
 End Sub

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

Handles cmbResidents.SelectedIndexChanged
Dim selectedResident As String = cmbResidents.SelectedItem.ToString()
Dim residentNumber As String = selectedResident.Split(" - ")(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)
cmd.Parameters.AddWithValue("@resident_number", residentNumber)

connection.Open()
Using reader As MySqlDataReader = cmd.ExecuteReader()
If reader.Read() Then
FillForm(reader)
EnableAllFields() ' Enable all fields for editing
End If
End Using
End Using
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
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

cmd.Parameters.AddWithValue("@resident_number", txtResidentNumber.Text)
cmd.Parameters.AddWithValue("@first_name", txtFirstName.Text)
cmd.Parameters.AddWithValue("@middle_name", txtMiddleName.Text)
cmd.Parameters.AddWithValue("@last_name", txtLastName.Text)
cmd.Parameters.AddWithValue("@sex", cmbSex.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@birthdate", dtpBirthDate.Value.Date)
cmd.Parameters.AddWithValue("@age", Integer.Parse(txtAge.Text))
cmd.Parameters.AddWithValue("@contact_number", txtContactNumber.Text)
cmd.Parameters.AddWithValue("@marital_status",
cmbMaritalStatus.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@sector", cmbSector.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@employment_status",
cmbEmploymentStatus.SelectedItem.ToString())

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

Dim employmentStatus As String = cmbEmploymentStatus.SelectedItem.ToString()
If employmentStatus = "Employed" Or employmentStatus = "Self-Employed" Then
cmd.Parameters.AddWithValue("@occupation", If(cmbOccupation.SelectedIndex <>
-1, cmbOccupation.SelectedItem.ToString(), DBNull.Value))
cmd.Parameters.AddWithValue("@type_of_employment",
If(cmbTypeOfEmployment.SelectedIndex <> -1,
cmbTypeOfEmployment.SelectedItem.ToString(), DBNull.Value))
cmd.Parameters.AddWithValue("@monthly_income",
If(cmbMonthlyIncome.SelectedIndex <> -1, cmbMonthlyIncome.SelectedItem.ToString(),
DBNull.Value))
Else
' Set occupation-related fields to NULL if status is not employed
cmd.Parameters.AddWithValue("@occupation", DBNull.Value)
cmd.Parameters.AddWithValue("@type_of_employment", DBNull.Value)
cmd.Parameters.AddWithValue("@monthly_income", DBNull.Value)
End If

' Handling assistance status

If rbYes.Checked Then
 cmd.Parameters.AddWithValue("@receiving_government_assistance", "Yes")
cmd.Parameters.AddWithValue("@type_of_assistance",
If(cmbTypeOfAssistance.SelectedIndex <> -1, cmbTypeOfAssistance.SelectedItem.ToString(),
DBNull.Value))
Else
cmd.Parameters.AddWithValue("@receiving_government_assistance", "No")
cmd.Parameters.AddWithValue("@type_of_assistance", DBNull.Value)
End If

connection.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
connection.Close()

If rowsAffected > 0 Then

MessageBox.Show("Resident data updated successfully!", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Failed to update resident data.", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If

Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try
End Sub

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

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

' Validate Search Field

If String.IsNullOrEmpty(txtSearchResident.Text) Then
MessageBox.Show("Please enter a value to search.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtSearchResident.Focus()
Return
End If

' Validate First Name

If String.IsNullOrEmpty(txtFirstName.Text) Then
MessageBox.Show("Please enter a value for First Name.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
 txtFirstName.Focus()
Return
ElseIf Not Regex.IsMatch(txtFirstName.Text, namePattern) Then
MessageBox.Show("First Name should contain letters only.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtFirstName.Focus()
Return
End If

' Validate Middle Name

If String.IsNullOrEmpty(txtMiddleName.Text) Then
MessageBox.Show("Please enter a value for Middle Name.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtMiddleName.Focus()
Return
ElseIf Not Regex.IsMatch(txtMiddleName.Text, namePattern) Then
MessageBox.Show("Middle Name should contain letters only.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtMiddleName.Focus()
Return
End If

' Validate Last Name

If String.IsNullOrEmpty(txtLastName.Text) Then
MessageBox.Show("Please enter a value for Last Name.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtLastName.Focus()
Return
ElseIf Not Regex.IsMatch(txtLastName.Text, namePattern) Then
MessageBox.Show("Last Name should contain letters only.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtLastName.Focus()
Return
End If

' Validate Contact Number

If String.IsNullOrEmpty(txtContactNumber.Text) Then
MessageBox.Show("Please enter a value for Contact Number.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtContactNumber.Focus()
Return
Else
Dim contactNumber As Long
If Not Long.TryParse(txtContactNumber.Text, contactNumber) Then
 MessageBox.Show("Please enter a valid Contact Number.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtContactNumber.Focus()
Return
End If
End If

' Validate Sex

If cmbSex.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Sex.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbSex.Focus()
Return
End If

' Validate Marital Status

If cmbMaritalStatus.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Marital Status.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbMaritalStatus.Focus()
Return
End If

' Validate Sector

If cmbSector.SelectedIndex = -1 Then
MessageBox.Show("Please select a value for Sector.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
cmbSector.Focus()
Return
End If

' Validate Birthdate and Calculate Age

Dim age As Integer
If dtpBirthDate.Value > DateTime.Now Then
MessageBox.Show("Birthdate cannot be in the future.", "Validation Error",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
dtpBirthDate.Focus()
Return
Else
age = DateTime.Now.Year - dtpBirthDate.Value.Year
If dtpBirthDate.Value.Date > DateTime.Now.AddYears(-age) Then
age -= 1
End If
End If
 ' Validate Government Assistance
Dim receivingAssistance As String = If(rbYes.Checked, "Yes", "No")
Dim typeOfAssistance As Object = If(cmbTypeOfAssistance.Enabled AndAlso
cmbTypeOfAssistance.SelectedIndex <> -1, cmbTypeOfAssistance.SelectedItem.ToString(),
DBNull.Value)

' Handle Employment Data

Dim employmentStatus As String = If(cmbEmploymentStatus.SelectedIndex <> -1,
cmbEmploymentStatus.SelectedItem.ToString(), String.Empty)
Dim occupation As String = If(cmbOccupation.SelectedIndex <> -1,
cmbOccupation.SelectedItem.ToString(), String.Empty)
Dim typeOfEmployment As String = If(cmbTypeOfEmployment.SelectedIndex <> -1,
cmbTypeOfEmployment.SelectedItem.ToString(), String.Empty)
Dim monthlyIncome As String = If(cmbMonthlyIncome.SelectedIndex <> -1,
cmbMonthlyIncome.SelectedItem.ToString(), String.Empty)

' 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

cmd.Parameters.AddWithValue("@first_name", txtFirstName.Text)
cmd.Parameters.AddWithValue("@middle_name", txtMiddleName.Text)
cmd.Parameters.AddWithValue("@last_name", txtLastName.Text)
cmd.Parameters.AddWithValue("@contact_number", txtContactNumber.Text)
cmd.Parameters.AddWithValue("@sex", cmbSex.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@age", age)
cmd.Parameters.AddWithValue("@marital_status",
cmbMaritalStatus.SelectedItem.ToString())
 cmd.Parameters.AddWithValue("@sector", cmbSector.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@receiving_assistance", receivingAssistance)
cmd.Parameters.AddWithValue("@type_of_assistance", typeOfAssistance)
cmd.Parameters.AddWithValue("@employment_status",
If(String.IsNullOrWhiteSpace(employmentStatus), DBNull.Value, employmentStatus))
cmd.Parameters.AddWithValue("@occupation", If(String.IsNullOrWhiteSpace(occupation),
DBNull.Value, occupation))
cmd.Parameters.AddWithValue("@type_of_employment",
If(String.IsNullOrWhiteSpace(typeOfEmployment), DBNull.Value, typeOfEmployment))
cmd.Parameters.AddWithValue("@monthly_income",
If(String.IsNullOrWhiteSpace(monthlyIncome), DBNull.Value, monthlyIncome))
cmd.Parameters.AddWithValue("@resident_number",
Integer.Parse(txtResidentNumber.Text))

' Execute the command

Try
connection.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
connection.Close()

If rowsAffected > 0 Then

MessageBox.Show("Resident data updated successfully!", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Failed to update resident data.", "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try

' Close the form and show the dashboard

frmDashboard.Show()
Me.Close()
End Sub

' Search button click event

Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles btnFind.Click
SearchResident()
End Sub
 ' Search on Enter key press
Private Sub txtSearchResident_KeyPress(sender As Object, e As KeyPressEventArgs)
Handles txtSearchResident.KeyPress
If e.KeyChar = ChrW(Keys.Enter) Then
SearchResident()
End If
End Sub

Private Sub cmbEmploymentStatus_SelectedIndexChanged(sender As Object, e As

EventArgs) Handles cmbEmploymentStatus.SelectedIndexChanged
Dim selectedStatus As String = cmbEmploymentStatus.SelectedItem.ToString()

' 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)
cmbOccupation.SelectedIndex = -1
cmbTypeOfEmployment.SelectedIndex = -1
cmbMonthlyIncome.SelectedIndex = -1
End If
End Sub

' Update occupation-related fields visibility

Private Sub UpdateOccupationFields(visible As Boolean)
cmbOccupation.Enabled = visible
cmbTypeOfEmployment.Enabled = visible
cmbMonthlyIncome.Enabled = 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
rbYes.Checked = True
rbNo.Checked = False
cmbTypeOfAssistance.Enabled = True
 cmbTypeOfAssistance.SelectedItem = typeOfAssistance ' Populate
cmbTypeOfAssistance
Else
rbYes.Checked = False
rbNo.Checked = True
cmbTypeOfAssistance.Enabled = False
cmbTypeOfAssistance.SelectedIndex = -1 ' Clear the selection
End If
End Sub
Private Sub rbYes_CheckedChanged(sender As Object, e As EventArgs) Handles
rbYes.CheckedChanged
If rbYes.Checked Then
cmbTypeOfAssistance.Enabled = True
End If
End Sub

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

rbNo.CheckedChanged
If rbNo.Checked Then
cmbTypeOfAssistance.Enabled = False
cmbTypeOfAssistance.SelectedIndex = -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 = dtpBirthDate.Value
' Get today's date
Dim today As Date = Date.Today
' Calculate age
Dim age As Integer = today.Year - birthDate.Year

' Adjust if the birth date has not occurred yet this year
If (birthDate > today.AddYears(-age)) Then
age -= 1
End If

' Display the age in the TextBox

txtAge.Text = age.ToString()
End Sub
Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
frmDashboard.Show()
Me.Close()
ResetControls() ' Call ResetControls here to reset the form
End Sub
 Private Sub btnResidents_Click(sender As Object, e As EventArgs) Handles
btnResidents.Click
frmDashboard.Show()
Me.Close()
End Sub
' Function to reset form controls
Private Sub ResetControls()
For Each ctrl As Control In Me.Controls
' 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 = DateTime.Now
End If
cmbTypeOfAssistance.Enabled = False
Next
End Sub

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles btnReports.Click

frmReports.Show()
Me.Close()
End Sub

Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles btnLogOut.Click

frmLogin.Show()
Me.Close()
End Sub

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

dtpBirthDate.ValueChanged
CalculateTheAge()
End Sub
End Class
ARCHIVE RESIDENT CODE
Imports MySql.Data.MySqlClient

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
MyBase.Load
LoadResidents()
End Sub

' Method to load all residents into DataGridView

Private Sub LoadResidents()
Try
connection.Open()

' 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()
adapter.Fill(table)

' Bind the DataTable to the DataGridView

dgvResidents.DataSource = table

' Set column headers

dgvResidents.Columns("resident_number").HeaderText = "Resident Number"
dgvResidents.Columns("first_name").HeaderText = "First Name"
dgvResidents.Columns("middle_name").HeaderText = "Middle Name"
dgvResidents.Columns("last_name").HeaderText = "Last Name"
dgvResidents.Columns("is_archived").HeaderText = "Archived Status"

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

dgvResidents.Columns("is_archived").Visible = False

' Add the Archive and Retrieve Button columns if they do not exist
If Not dgvResidents.Columns.Contains("btnArchive") Then
Dim btnArchive As New DataGridViewButtonColumn()
 btnArchive.Name = "btnArchive"
btnArchive.HeaderText = "Action"
btnArchive.Text = "Archive"
btnArchive.UseColumnTextForButtonValue = True
dgvResidents.Columns.Add(btnArchive)
End If

If Not dgvResidents.Columns.Contains("btnRetrieve") Then

Dim btnRetrieve As New DataGridViewButtonColumn()
btnRetrieve.Name = "btnRetrieve"
btnRetrieve.HeaderText = "Action"
btnRetrieve.Text = "Retrieve"
btnRetrieve.UseColumnTextForButtonValue = True
dgvResidents.Columns.Add(btnRetrieve)
End If

' Adjust column widths for better UI

dgvResidents.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

connection.Close()
Catch ex As Exception
MessageBox.Show("Error loading data: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try
End Sub
' Method to search residents by name or resident number
Private Sub SearchResidents(searchValue As String)
Try
connection.Open()

' 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)
cmd.Parameters.AddWithValue("@searchValue", "%" & searchValue & "%")

Dim adapter As New MySqlDataAdapter(cmd)

Dim table As New DataTable()
adapter.Fill(table)

' Bind the search result to the DataGridView

 dgvResidents.DataSource = table

connection.Close()
Catch ex As Exception
MessageBox.Show("Error searching data: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try
End Sub

' Archive a resident

Private Sub ArchiveResident(residentNumber As Integer)
Try
connection.Open()
Dim query As String = "UPDATE ResidentForm SET is_archived = 1 WHERE
resident_number = @residentNumber"
Dim cmd As New MySqlCommand(query, connection)
cmd.Parameters.AddWithValue("@residentNumber", residentNumber)
Dim result = cmd.ExecuteNonQuery()
connection.Close()

If result > 0 Then

MessageBox.Show("Resident archived successfully.", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information)
LoadResidents() ' Refresh DataGridView
Else
MessageBox.Show("Failed to archive resident.", "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
End If
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try
End Sub

' Retrieve a resident

Private Sub RetrieveResident(residentNumber As Integer)
Try
connection.Open()
Dim query As String = "UPDATE ResidentForm SET is_archived = 0 WHERE
resident_number = @residentNumber"
Dim cmd As New MySqlCommand(query, connection)
cmd.Parameters.AddWithValue("@residentNumber", residentNumber)
 Dim result = cmd.ExecuteNonQuery()
connection.Close()

If result > 0 Then

MessageBox.Show("Resident retrieved successfully.", "Success",
MessageBoxButtons.OK, MessageBoxIcon.Information)
LoadResidents() ' Refresh DataGridView
Else
MessageBox.Show("Failed to retrieve resident.", "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
End If
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
connection.Close()
End Try
End Sub
Private Sub btnResidents_Click(sender As Object, e As EventArgs)
frmDashboard.Show()
Me.Hide()
End Sub

Private Sub dgvResidents_CellContentClick_1(sender As Object, e As

DataGridViewCellEventArgs) Handles dgvResidents.CellContentClick
' Ensure the click is on a valid cell
If e.RowIndex >= 0 Then
' Fetch the Resident Number of the clicked row
Dim residentNumber As Integer =
Convert.ToInt32(dgvResidents.Rows(e.RowIndex).Cells("resident_number").Value)
Dim isArchived As Boolean =
Convert.ToBoolean(dgvResidents.Rows(e.RowIndex).Cells("is_archived").Value)
Dim columnName As String = dgvResidents.Columns(e.ColumnIndex).Name

' Archive Button Clicked

If columnName = "btnArchive" AndAlso Not isArchived Then
' Show confirmation dialog for archiving
Dim result As DialogResult = MessageBox.Show(
"Are you sure you want to archive this Resident?",
"Confirm Archive",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question
)

' Proceed only if user confirms

 If result = DialogResult.Yes 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 = MessageBox.Show(
"Are you sure you want to retrieve this Resident?",
"Confirm Retrieve",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question
)

' Proceed only if user confirms

If result = DialogResult.Yes Then
RetrieveResident(residentNumber)
End If
End If
End If
End Sub

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

btnRefresh.Click
LoadResidents()
txtSearchResident.Focus()
txtSearchResident.Text = ""
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 dgvResidents.CellFormatting
' Check if the column exists in the DataGridView to prevent errors
If dgvResidents.Columns("is_archived") IsNot Nothing AndAlso e.RowIndex >= 0 Then
Dim isArchived As Boolean =
Convert.ToBoolean(dgvResidents.Rows(e.RowIndex).Cells("is_archived").Value)

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

If isArchived Then
dgvResidents.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.Red
dgvResidents.Rows(e.RowIndex).DefaultCellStyle.ForeColor = Color.White ' 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

btnResidents.Click
frmDashboard.Show()
Me.Close()
End Sub

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles btnReports.Click

frmReports.Show()
Me.Close()
End Sub

Private Sub btnLogOut_Click(sender As Object, e As EventArgs) Handles btnLogOut.Click

frmLogin.Show()
Me.Close()
End Sub
' Click event for btnFind to perform the search
Private Sub btnFind_Click(sender As Object, e As EventArgs) Handles btnFind.Click
SearchResidents(txtSearchResident.Text.Trim())
End Sub
' KeyPress event for txtSearchResident to search on Enter key press
Private Sub txtSearchResident_KeyPress(sender As Object, e As KeyPressEventArgs)
Handles txtSearchResident.KeyPress
If e.KeyChar = ChrW(Keys.Enter) Then
SearchResidents(txtSearchResident.Text.Trim())
End If
End Sub
End Class

REPORTS CODE
Imports MySql.Data.MySqlClient
Imports System.Windows.Forms.DataVisualization.Charting

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 MyBase.Load

' Focus on btnResidents
btnResidents.Focus()
btnResidents.TabIndex = 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)
connection.Open()
Dim command As New MySqlCommand(query, connection)
totalResidents = Convert.ToInt32(command.ExecuteScalar())
lblTotalResidents.Text = totalResidents.ToString()
End Using
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
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)
connection.Open()
Dim command As New MySqlCommand(query, connection)
totalEmployed = Convert.ToInt32(command.ExecuteScalar())
lblTotalEmployed.Text = totalEmployed.ToString()
End Using
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
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)
connection.Open()
Dim command As New MySqlCommand(query, connection)
totalStudent = Convert.ToInt32(command.ExecuteScalar())
lblTotalStudent.Text = totalStudent.ToString()
End Using
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
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)
connection.Open()
Dim command As New MySqlCommand(query, connection)
totalUnemployed = Convert.ToInt32(command.ExecuteScalar())
lblTotalUnemployed.Text = totalUnemployed.ToString()
End Using
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
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)
connection.Open()
Dim command As New MySqlCommand(query, connection)
totalRetired = Convert.ToInt32(command.ExecuteScalar())
lblTotalRetired.Text = totalRetired.ToString()
End Using
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
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
chartReport.Series.Clear()

' 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
MessageBox.Show("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")
series.ChartType = SeriesChartType.Pie

' Add data points with manually calculated percentages

 series.Points.AddXY("Employed/Self-Employed", employedPercentage)
series.Points.AddXY("Students", studentPercentage)
series.Points.AddXY("Unemployed", unemployedPercentage)
series.Points.AddXY("Retired", retiredPercentage)

' Add the series to the chart

chartReport.Series.Add(series)

' Set chart title

chartReport.Titles.Clear()
Dim chartTitle As New Title("Occupation Status Distribution")
chartTitle.Font = New Font("Host Grotesk", 16, FontStyle.Bold)
chartReport.Titles.Add(chartTitle)

' Customize the appearance of data labels

series.IsValueShownAsLabel = True
series.LabelFormat = "{0:0.##}%"
series.Font = New Font("Host Grotesk", 12, FontStyle.Regular)
series.LabelForeColor = Color.Black ' Optional: Change label text color

' Customize legend appearance

If chartReport.Legends.Count > 0 Then
chartReport.Legends(0).Font = New Font("Host Grotesk", 10, FontStyle.Regular)
End If

' Set colors for better visualization

series.Points(0).Color = Color.LightBlue
series.Points(1).Color = Color.LightGreen
series.Points(2).Color = Color.LightSalmon
series.Points(3).Color = Color.Gray

' Hide axis labels and grid lines for a clean look
chartReport.ChartAreas(0).AxisX.LabelStyle.Enabled = False
chartReport.ChartAreas(0).AxisY.LabelStyle.Enabled = False
chartReport.ChartAreas(0).AxisX.MajorGrid.Enabled = False
chartReport.ChartAreas(0).AxisY.MajorGrid.Enabled = False
End Sub

' Button click handlers

Private Sub btnResidents_Click(sender As Object, e As EventArgs) Handles
btnResidents.Click
frmDashboard.Show()
 Me.Close()
End Sub

Private Sub btnLogOut_Click_1(sender As Object, e As EventArgs) Handles btnLogOut.Click

frmLogin.Show()
Me.Close()
End Sub

Private Sub btnReports_Click(sender As Object, e As EventArgs) Handles btnReports.Click

Me.Show()
End Sub
End Class