import random as rd
import tabulate as tb
import datetime as dt
import mysql.connector as my
print("✦"*45)
print('\t ELECTRICITY BILL MANAGEMENT')
print("✦"*45)
#Connecting python with mysql interface
try:
con=my.connect(host='localhost',user='root',password='Praham@123',database='electr
icity_bill')
except my.Error as err:
print(f"Error: Could not connect to Database {err}")
exit() # Exit the Program if connection fails
#Function for logging in
def login():
while True:
a=input('Are you an Employee or a Customer - ').strip().lower()
if a not in ['employee','customer']:
print(" Invalid Input ")
continue
if a=='employee':
loginusernameandpass()
p='select role from logins where username=%s and passw=%s and role in
("admin","user")'
c=con.cursor()
c.execute(p,(un,pw))
rs=c.fetchone()
if rs:
role=rs[0]
if role=='admin':
admin_menu()
elif role=='user':
continue
else:
print('Invalid Employee Credentials')
elif a=='customer':
r=input('Do you have an account - ').strip().lower()
if r in ('yes','y'):
loginusernameandpass()
p='select role from logins where username=%s and passw=%s and
role="user"'
c=con.cursor()
c.execute(p,(un,pw))
rs=c.fetchone()
if rs:
user_menu()
else:
print('Invalid Customer Credentials')
elif r in ('no','n'):
register_customer()
else:
print(" Invalid Input ")
break
def logcus(message):
try:
with open('log_customer.txt','a') as lgf:
t=dt.datetime.now().strftime("%d-%m-%y %H:%M:%S")
lgf.write(f"[{t}] {message}\n")
except IOError as e:
print(f"Error writing to log file: {e}")
def loginusernameandpass():
while True:
print('\t LOGIN')
un = input('Enter UserName : ').strip()
if not validate_username(un):
print("Invalid username. Please try again.")
continue # Go back to asking for the username and password
pw = input('Enter Password : ').strip()
if not validate_password(pw):
print("Invalid password. Please try again.")
continue # Go back to asking for the username and password
# If both username and password are valid, break out of the loop
print("Login successful!")
break # Exit the loop if login is successful
def logbill(message):
try:
with open('log_bills.txt','a') as lf:
t=dt.datetime.now().strftime("%d-%m-%y %H:%M:%S")
lf.write(f"[{t}] {message}\n")
except IOError as e:
print(f"Error writing to log file: {e}")
def logcomp(message):
try:
with open('log_complaints.txt','a') as fi:
t=dt.datetime.now().strftime("%d-%m-%y %H:%M:%S")
fi.write(f"[{t}] {message}\n")
except IOError as e:
print(f"Error writing to log file: {e}")
def show_tables(head,data):
table=tb.tabulate(data, headers=head, tablefmt='github',colalign=
('left',),stralign=('left',))
print(table)
def validate_password(password):
if len(password)<8:
print('Password must atleast be 8-characters long')
return False
return True
def validate_username(username):
if len(username)<3:
print('Username must be at least 3-characters long')
return False
if not username.isalnum():
print("Username can only contain letters and numbers")
return False
return True
def validate_phone(phone_number):
if phone_number.isdigit() and len(phone_number)==10:
return True
print("Invalid Phone Number -> It should be 10 Digits")
return False
def showadmins():
c1=con.cursor()
c1.execute('Select username,passw from logins where role="admin"')
res=c1.fetchall()
print(' ','-'*48)
print(' List of Admins')
print(' ','-'*48)
head=['Usernames','Passwords']
show_tables(head,res)
def register_customer():
print("\t REGISTER NEW CUSTOMER ")
try:
while True:
un = input('Enter your Username: ').strip()
if not validate_username(un):
continue # Ask for username again if invalid
# Check if the username already exists in the logins table
c = con.cursor()
c.execute("SELECT username FROM logins WHERE username = %s", (un,))
existing_user = c.fetchone()
if existing_user:
print("Username has already been taken. Please choose a different
one.")
continue # Ask for username again
else:
break # If username is available, proceed
pw = input('Enter your Password: ').strip()
if not validate_password(pw):
return
# Insert new customer into the logins table
p = "INSERT INTO logins (username, passw) VALUES (%s, %s)"
val = (un, pw)
c.execute(p, val)
con.commit()
print('\t CUSTOMER ACCOUNT SUCCESSFULLY CREATED ')
user_menu()
except my.Error as err:
print(f"Error: Could Not create customer account - {err}")
def admin_menu():
while True:
print("-"*50)
print("\t CHOOSE AN OPERATION")
print("-"*50)
print("Press 1 - Add a New Customer")
print("Press 2 - Deleting an Existing Customer")
print("Press 3 - Show all Customers")
print("Press 4 - Generate the Bill")
print("Press 5 - Mark The Bill As Paid")
print("Press 6 - Show All Unpaid Bills")
print("Press 7 - Show Complaints")
print("Press 8 - Mark As Rectified")
print("Press 9 - Show Admins")
print("Press 10 - Quit")
try:
ch=int(input("Enter Your Choice : "))
except ValueError:
print("Invalid Output - Please enter a Valid Choice Number")
continue
if ch==1:
addcustomer()
elif ch==2:
delcustomer()
elif ch==3:
showcustomers()
elif ch==4:
generatebill()
elif ch==5:
paybill()
elif ch==6:
showunpaid()
elif ch==7:
scomplaints()
elif ch==8:
markrect()
elif ch==9:
showadmins()
elif ch==10:
break
else:
print('Invalid Choice')
def user_menu():
while True:
print("-"*50)
print("\t CHOOSE AN OPERATION")
print("-"*50)
print("Press 1 - Pay the Bill")
print("Press 2 - Generate the Bill")
print("Press 3 - Raise Complaints")
print("Press 4 - Quit")
try:
ch=int(input("Enter Your Choice : "))
except ValueError:
print('Invalid Choice - Enter a Valid Choice Number')
continue
if ch==1:
paybill()
elif ch==2:
generatebill()
elif ch==3:
rscomplaints()
elif ch==4:
break
else:
print('Invalid Choice')
def delcustomer():
print("*"*50)
print('\t DELETING A CUSTOMER')
print("*"*50)
try:
cd=input('Enter Customer Id : ')
c3=con.cursor()
q="delete from customers where customer_id=%s"
c3.execute(q,(cd,))
con.commit()
print(' CUSTOMER SUCCESSFULLY DELETED ')
except my.Error as err:
print(f"Error: Could not delete the customer {err}")
def addcustomer():
print("*"*50)
print('\t WELCOME TO ELECTRICITY MANAGEMENT ')
print("*"*50)
try:
q='select customer_id from customers'
c=con.cursor()
c.execute(q)
x=c.fetchall()
def custid():
return rd.randint(10000,99999)
cd=custid()
while (cd,) in x:
cd=custid()
cname=input('Enter Customer Name : ')
addr=input('Enter Customer Address : ')
pho=input('Enter Phone Number : ')
if not validate_phone(pho):
return
email=input('Enter Email : ')
mtr=input('Enter Meter Number: ')
print("Your Customer ID : ",cd)
print()
c4=con.cursor()
q='Insert into customers values (%s,%s,%s,%s,%s,%s)'
val=(cd,cname,addr,pho,email,mtr)
c4.execute(q,val)
con.commit()
print(' CUSTOMER SUCCESSFULLY ADDED ')
logcus(f"NEW CUSTOMER - Customer ID: {cd},Name: {cname}, Address: {addr},
Phone: {pho}, Email: {email}, Meter No: {mtr}")
user_menu()
except my.Error as err:
print(f"Error: Could not add the customer {err}")
logcus(f"Failed to add customer - Name : {cname} Error: {err}")
except ValueError:
print('Invalid Input')
def showcustomers():
c5=con.cursor()
c5.execute('Select customer_id,customer_name,phone_number,meter_no,email from
customers')
res=c5.fetchall()
print(" ","-"*48)
print(" CUSTOMER DETAILS " )
print(" ","-"*48)
headers=["ID","NAME","PHONE","METER NO","EMAIL"]
show_tables(headers,res)
def generatebill():
try:
name=input('Enter Name: ')
bid=input('Enter the Bill_ID : ')
mtr=input('Enter Meter Number : ')
dt=input("Enter the date of Bill Generation : ")
cunits=int(input('Enter Current Units on Meter : '))
punits=int(input('Enter Previous Units on Meter : '))
consumed=cunits-punits
if consumed<200:
bill=4*consumed
elif consumed<400:
bill=6*consumed
else:
bill=8*consumed
print("Total Units Consumed : ",consumed)
print("Total Amount to be paid : ",bill)
duedt=input('Enter the Due Date of Payment : ')
q="Insert into bills values(%s,%s,%s,%s,%s,%s,%s,%s,'No')"
val=(bid,mtr,dt,cunits,punits,consumed,bill,duedt)
c6=con.cursor()
c6.execute(q,val)
print(" BILL GENERATED SUCCESSFULLY !! ")
con.commit()
logbill(f"Generated bill for {name} - BILL ID: {bid}, METER NO: {mtr},
UNIT CONSUMED : {consumed}, AMOUNT: {bill}, DUE DATE: {duedt}")
except my.Error as err:
print(f"Error: Could not generate bill {err}")
logbill(f"Failed to generate bill for Meter No: {mtr} Error: {err}")
except ValueError:
print('Invalid Input')
except TypeError:
print("Invalid Input")
def showunpaid():
c7=con.cursor()
c7.execute('Select meter_no,bill_date,total_amount,due_date from bills where
paid="No"')
res=c7.fetchall()
if res==[]:
print('------------NO UNPAID BILLS------------')
else:
print(" ","-"*40)
print(" LIST OF UNPAID BILLS ")
print(" ","-"*40)
headers=["METER NO","BILL DATE","TOTAL AMOUNT","DUE DATE"]
show_tables(headers,res)
def paybill():
try:
c8=con.cursor()
mtr=input('Enter the Meter Number : ')
bdate=input('Enter the bill date for the bill to be paid : ')
mp=input('Please Select The Mode of Payment (Cash/Cheque/Card) : ')
if mp.lower()=='cash':
mo=float(input('Enter the amount : '))
print('₹ ',mo,' received')
print(" -------------------------TRANSACTION COMPLETE---------------------
---- ")
q="update bills set paid='Yes' where bill_date<%s and %s<due_date and
meter_no=%s"
val=(bdate,bdate,mtr)
c8.execute(q,val)
con.commit()
except my.Error as err:
print(f"Error: Could not mark the bill as paid {err}")
except ValueError:
print('Invalid Input')
except TypeError:
print('Invalid Input')
def rscomplaints():
try:
a=input("Enter Customer ID : ")
b=input("Enter your Complaint : ")
c=con.cursor()
q='insert into complaints (Customer_ID,complaints) values (%s,%s)'
val=(a,b)
c.execute(q,val)
con.commit()
print(' COMPLAINT SUCCESSFULLY GIVEN ')
logcomp(f"Complaint raised by Customer ID: {a}. Complaint : {b}")
except my.Error as err:
print(f"Error: Could not register Complaint {err}")
logcomp(f"Error: Failed to register complaint for Customer ID: {a}. Error:
{err}")
except ValueError:
print('Invalid Input')
def scomplaints():
c=con.cursor()
c.execute("Select customer_id,complaints from complaints where
rectified='No'")
res=c.fetchall()
if res==[]:
print('------------NO UNRECTIFIED COMPLAINTS------------')
else:
print(" ","-"*40)
print(" LIST OF UNRECTIFIED
COMPLAINTS ")
print(" ","-"*40)
headers=["CUSTOMER_ID","COMPLAINTS"]
show_tables(headers,res)
def markrect():
c=con.cursor()
q="update complaints set rectified='Yes'"
c.execute(q)
print(" -------------------------COMPLAINT RECTIFIED-------------------------
")
con.commit()
try:
login()
except Exception as err:
print(f"Unexpected Error Occured: {err}")
