Electricity Bill Management

System

Done by: Sanjith T

itheesh K R
Mohammed Zeeshan Samseer
ER DIAGRAM
 Abstract
The Electricity Bill Management System is a comprehensive database management solution designed to streamline and enhance the
management of electricity billing processes. This system caters to the needs of utility companies, enabling efficient tracking of
customer information, meter readings, billing, and payment transactions. The project focuses on user authentication, customer data
management, meter reading, billing and invoice generation, payment tracking, and analytics.

The system provides a secure and user-friendly interface for both administrators and customers. Users can easily access and
manage their account information, view historical usage data, and receive timely notifications about upcoming bills and payment
reminders. Administrators have access to a robust analytics dashboard, enabling them to monitor overall system performance, track
outstanding payments, and analyze energy consumption trends.

Key features include a flexible billing module that calculates charges based on meter readings, automated invoice generation, and a
notification system to keep customers informed. The database ensures data integrity, security, and scalability, with regular backups
to prevent data loss.

The Electricity Bill Management System aims to improve the efficiency of electricity billing operations, enhance customer
experience, and contribute to better decision-making through insightful analytics. This project aligns with the growing demand for
advanced utility management systems, offering a reliable and scalable solution for the evolving needs of electricity providers.
 Objective

Develop an Electricity Bill Management System to streamline utility operations,

enhance user experience, and automate billing processes. The system aims to ensure
data accuracy, facilitate efficient payment tracking, provide analytics for decision-
making, and improve overall efficiency for utility providers. Through a user-friendly
interface and secure design, the project aims to optimize electricity billing operations
and contribute to a more informed and positive customer experience.
 Problem Statement
The current state of electricity billing systems faces several challenges:

Operational Inefficiency:

Manual handling of customer data and billing processes leads to inefficiencies, errors, and delays.

Limited Customer Interaction:

Inadequate communication channels leave customers uninformed about their usage, upcoming bills, and payments.

Billing Errors and Discrepancies:

Manual calculation of charges increases the risk of billing errors and discrepancies.

Insufficient Analytics:

Lack of analytics tools hinders utility providers' ability to derive insights from historical data.

Security Concerns:

Inadequate security measures expose customer data to potential breaches.

Scalability Challenges:

Growing customer bases pose challenges for existing systems in terms of scalability.

Addressing these issues requires the development of an Electricity Bill Management System, aiming to automate processes, enhance communication,
minimize errors, provide analytics for decision-making, and ensure security and scalability for efficient utility management.
 Scope of the project
This project will create a user-friendly Electricity Bill Management System, covering:
Customer Info:
Collect and manage customer details for accurate billing.
Automated Billing:
Set up automatic billing based on meter readings for precision.
Easy Payments:
Implement a straightforward payment system for customers and administrators.
Notifications:
Notify customers about bills, payments, and important updates.
Analytics for Admins:
Provide admins with an easy-to-use dashboard to track system performance and payment trends.
Security and Growth:
Ensure data security and design for future growth.
History Access:
Allow customers to check their usage history easily.
Documentation:
Provide simple documentation for easy system understanding and future improvements.
This simplified scope aims to create a hassle-free Electricity Bill Management System, making billing processes straightforward and accessible for users.
Database Schema
 Database Tables
1.CUSTOMER
2.ADMIN 3.ACCOUNT
4.BILLING
5.Elec_board 6.Tarrif
7.Invoice
 Queries

Query to Find Customers Who Haven't Paid Their Bills:

SELECT C.CustomerID, CONCAT(C.FirstName, ' ', C.LastName) AS CustomerName

FROM Customers C
LEFT JOIN CustomerPayments CP ON C.CustomerID = CP.CustomerID
WHERE CP.CustomerID IS NULL;
Query to Get Complaints with Customer Details:

SELECT C.*, CO.*

FROM Customers C
JOIN Complaints CO ON C.CustomerID = CO.CustomerID;
Query to Calculate Total Maintenance Cost for Each Meter:

SELECT M.MeterID, SUM(MM.Cost) AS TotalMaintenanceCost

FROM Meters M
JOIN MeterMaintenance MM ON M.MeterID = MM.MeterID
GROUP BY M.MeterID;
Query to Retrieve Average Usage per Month for Each Meter:

SELECT M.MeterID, AVG(UH.UnitsConsumed) AS AverageUsagePerMonth

FROM Meters M
JOIN UsageHistory UH ON M.MeterID = UH.MeterID
GROUP BY M.MeterID;
Query to Retrieve Customers with Their Total Payments Using GROUP BY:

SELECT CP.CustomerID, CONCAT(C.FirstName, ' ', C.LastName) AS CustomerName, SUM(P.AmountPaid) AS TotalPayments

FROM Customers C
LEFT JOIN CustomerPayments CP ON C.CustomerID = CP.CustomerID
LEFT JOIN Payments P ON CP.PaymentID = P.PaymentID
GROUP BY CP.CustomerID, C.FirstName, C.LastName;
Query to Find Customers Who Haven't Paid Their Bills Using GROUP BY:

SELECT C.CustomerID, CONCAT(C.FirstName, ' ', C.LastName) AS CustomerName

FROM Customers C
LEFT JOIN CustomerPayments CP ON C.CustomerID = CP.CustomerID
WHERE CP.CustomerID IS NULL
GROUP BY C.CustomerID, C.FirstName, C.LastName;
Trigger to Update Meter Status:

CREATE TRIGGER update_meter_status

AFTER INSERT ON Complaints
FOR EACH ROW
BEGIN
UPDATE Meters
SET IsActive = FALSE
WHERE MeterID = NEW.MeterID;
END;
Trigger to Update Customer Status:

DELIMITER //
CREATE TRIGGER update_customer_status
AFTER INSERT ON Payments
FOR EACH ROW
BEGIN
DECLARE last_payment_date DATE;
-- Get the last payment date
SELECT MAX(PaymentDate) INTO last_payment_date
FROM Payments
WHERE CustomerID = NEW.CustomerID;
-- Update customer status if last payment date is older than 60 days
IF last_payment_date < DATE_SUB(NOW(), INTERVAL 60 DAY) THEN
UPDATE Customers
SET Status = 'On Hold'
WHERE CustomerID = NEW.CustomerID;
END IF;
END;
//
DELIMITER ;
 DELIMITER //
CREATE PROCEDURE CalculateTotalPayments()
Cursor to Calculate Total Payments by Customers: BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE customer_id INT;
DECLARE total_payments DECIMAL(10, 2);
-- Declare cursor for selecting distinct customer IDs
DECLARE customer_cursor CURSOR FOR
SELECT DISTINCT CustomerID FROM CustomerPayments;
-- Declare continue handler to exit loop
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Open cursor
OPEN customer_cursor;
-- Loop through each customer ID
customer_loop: LOOP
-- Fetch next customer ID
FETCH customer_cursor INTO customer_id;
IF done THEN
LEAVE customer_loop;
END IF;
-- Calculate total payments for the customer
SELECT SUM(AmountPaid) INTO total_payments FROM Payments WHERE PaymentID IN (
SELECT PaymentID FROM CustomerPayments WHERE CustomerID = customer_id
);
-- Print the result
SELECT CONCAT('Customer ', customer_id, ' Total Payments: ', total_payments);
END LOOP;
-- Close cursor
CLOSE customer_cursor;
END;
//
DELIMITER ;
Query to Retrieve Customer Information with Meter Details:

SELECT
C.FirstName,
C.LastName,
C.Address,
M.MeterNumber,
M.InstallationDate,
M.IsActive
FROM
Customers C
JOIN
Meters M ON C.CustomerID = M.CustomerID;
Query to Retrieve Meter Readings with Customer Information:

SELECT
C.FirstName,
C.LastName,
CO.Description,
CO.Status,
CO.SubmissionDate,
CO.ResolutionDate
FROM
Customers C
JOIN
Complaints CO ON C.CustomerID = CO.CustomerID;