· 5 years ago · Apr 30, 2020, 02:50 AM
1import sqlite3 # import sqlite3 module
2import math
3
4class Customers(): # create a class Customers
5
6 def __init__(self):
7 print("WELCOME TO ASPIRE ELECTRICITY DISTRIBUTION COMPANY")
8 self.conn = sqlite3.connect('AspireDb.db') # initialize connection to Aspiredb.db
9 self.cursor = self.conn.cursor() # initialize cursor
10 self.create_table() # call method to create table
11 self.user_input() # call method to obtain input from user
12
13 def user_input(self): # create method to obtain user input
14 print("ENTER (1) TO SIGN IN") # request user to move to sign in section
15 print("ENTER (2) TO SIGN UP") # request user to move to sign up section
16 print('> ')
17 self.response = int(input()) # variable to store user response
18 if self.response == 2: # condition to check if user response is 2
19 self.insert() # call insert method
20 elif self.response == 1: # condition to check if user response is 1
21 self.signin() # call signin method
22 elif self.response not in [1,2]: # if user input is neither 1 or 2, instantiate the customer object and start afresh
23 customer = Customers()
24
25 def signin(self): # create the signin method
26 with self.conn: # establish connection to Aspire.db
27 c_email = input("Enter Email: ") # request user to enter email
28 c_password = input("Enter password: ") # request user to ennter password
29 self.cursor.execute("SELECT email, password FROM customerdb WHERE email = ?", (c_email,)) # sqlite3 statement to select email and password from the table customers where email is c_mail
30 records = self.cursor.fetchone() # assign the email and password to records if it's in tables
31 if records is not None: # check if c_mail in table
32 db_email, db_password = records # assign email and password to db_email and db_password respectively
33 if c_email == db_email and c_password == db_password: # check email and password are correct
34 self.purchase() # call the purchase method
35 else:
36 print("incorrect password") # print if password is incorrect
37 customer = Customers() # return to main section
38 else:
39 print("incorrect username") # print if email isn't correct
40 customer = Customers() # return to main section
41
42 def commit(self): # commit method
43 self.conn.commit() # this enables us to commit changes to databases
44
45 def close(self): # close method
46 self.conn.close() # this enables us to close database after committing chages
47
48 def create_table(self): # create table method to create method
49 with self.conn: # connect to Aspire database
50 self.cursor.execute("""CREATE TABLE IF NOT EXISTS customerdb(
51 name text,
52 email text,
53 address text,
54 mobile integer,
55 password text,
56 meter_no integer,
57 units real,
58 total_units real)""") # sqlite3 statement to create a table customerdb
59
60
61 def insert(self): # insert method
62 with self.conn:
63 c_name = input("Enter Name: ")
64 c_email = input("Enter email: ")
65 c_address = input("Enter address: ")
66 c_mobile = input("Enter telephone number: ")
67 c_password = input("Enter password: ")
68 c_meter_no = input("Enter meter number: ")
69 c_units = ''
70 c_total_units = 0
71
72 if '@' in c_email: # check if email exists
73 self.cursor.execute("INSERT INTO customerdb VALUES(:name, :email, :address, :mobile, :password, :meter_no, :units, :total_units)",
74 {'name': c_name,
75 'email': c_email,
76 'address': c_address,
77 'mobile': c_mobile,
78 'password': c_password,
79 'meter_no': c_meter_no,
80 'units': c_units,
81 'total_units': c_total_units }) # sqlite3 statement to insert user input to the table customerdb
82 else:
83 print("incorrect email")
84 customer = Customers()
85
86 def purchase(self): # purchase method
87 print("WELCOME TO THE PURCHASE SECTION")
88 c_meter_no = input("Enter meter number: ") # request user meter's number
89 with self.conn: # connect to Aspiredb
90 self.cursor.execute("SELECT meter_no, name, units, total_units from customerdb WHERE meter_no = ?",(c_meter_no,)) # aqlite3 statement to select meter numbber and name where meter number is c_meter_no
91 records = self.cursor.fetchone() # assign values to records
92 if records is not None: # check if c_meter_no in customerdb table
93 db_meter_no, db_name, db_units, db_total_units = records # assign meter number name, units and total units to db_meter_no, db_name, db_units and db_total_units respectively
94 # if c_meter_no == db_meter_no:
95 c_acct_no = input("Enter account number: ")
96 c_bank = input("Enter bank name: ")
97 c_amount = int(input("Enter amount: "))
98 c_units = round(c_amount/21.83, 2)
99 c_total_units = db_total_units + c_units # add units bought to existing total units in database
100 with self.conn:
101
102 self.cursor.execute("UPDATE customerdb SET units=? WHERE meter_no=?", (c_units, db_meter_no)) # update units to the current units bought
103 self.cursor.execute("UPDATE customerdb SET total_units=? WHERE meter_no=?", (c_total_units, db_meter_no)) # add units bought to units in database
104
105 print(f"Name: {db_name}")
106 print(f"Meter number: {db_meter_no}")
107 print(f"Amount paid: #{c_amount}")
108 print(f"units bought: {c_units}")
109 print("PURCHASE SUCCESSFUL!")
110 print()
111 input("press any key to ENTER to main menu")
112 customer = Customers()
113 else:
114 print("INCORRECT METER NUMBER")
115 customer = Customers()
116
117
118
119customer = Customers()