def __add_person(
self,
fname: str = None,
lname: str = None,
bdate: str = None,
bplace: str = None,
address: str = None,
phone: str = None,
):
text = "Enter the person's"
if fname is None:
fname = input_util.read_string(f"{text} first name: ")
if lname is None:
lname = input_util.read_string(f"{text} last name: ")
if bdate is None:
bdate = input_util.read_date(f"{text} birth date: ", optional=True)
if bplace is None:
bplace = input_util.read_string(f"{text} birth place: ",
optional=True)
if address is None:
address = input_util.read_string(f"{text} address: ",
optional=True)
if phone is None:
phone = input_util.read_string(f"{text} phone number: ",
optional=True)
self.cursor.execute(
"""
INSERT INTO persons
VALUES(?, ?, ?, ?, ?, ?);
""", (fname, lname, bdate, bplace, address, phone))
def register_marriage(self, username):
print()
print("Registering a marriage.")
p1_first_name = input_util.read_string(
"Please enter first partner's first name: ")
p1_last_name = input_util.read_string(
"Please enter first partner's last name: ")
p2_first_name = input_util.read_string(
"Please enter second partner's first name: ")
p2_last_name = input_util.read_string(
"Please enter second partner's last name: ")
partner_1 = self.__get_person(p1_first_name, p1_last_name)
if partner_1 is None:
print(
"First partner does not exist in database, please enter optional details."
)
self.__add_person(fname=p1_first_name, lname=p1_last_name)
else:
p1_first_name = partner_1[0]
p1_last_name = partner_1[1]
partner_2 = self.__get_person(p2_first_name, p2_last_name)
if partner_2 is None:
print(
"Second partner does not exist in database, please enter optional details."
)
self.__add_person(fname=p2_first_name, lname=p2_last_name)
else:
p2_first_name = partner_2[0]
p2_last_name = partner_2[1]
self.cursor.execute(
"""
SELECT city
FROM users
WHERE uid LIKE ?;
""", (username, ))
(city, ) = self.cursor.fetchone()
reg_date = date.today().strftime("%Y-%m-%d")
self.cursor.execute(
"""
INSERT INTO marriages
VALUES((SELECT MAX(regno) + 1 FROM marriages), ?, ?, ?, ?, ?, ?);
""", (reg_date, city, p1_first_name, p1_last_name, p2_first_name,
p2_last_name))
self.connection.commit()
print("Marriage registered.")
def __add_person(
self,
fname: str = "",
lname: str = "",
bdate: str = "",
bplace: str = "",
address: str = "",
phone: str = "",
):
text = "Enter the person's"
if fname == "":
fname = input_util.read_name(text + " first name: ")
if lname == "":
lname = input_util.read_name(text + " last name: ")
if bdate == "":
bdate = input_util.read_date(text + " birth date: ", optional=True)
if bplace == "":
bplace = input_util.read_string(text + " birth place: ",
optional=True)
if address == "":
address = input_util.read_string(text + " address: ",
optional=True)
phone_regex = re.compile(r"\d{3}-\d{3}-\d{4}$")
if phone == "":
phone = None
while phone is None:
phone = input_util.read_string(
text + " phone number (XXX-XXX-XXXX): ", optional=True)
if phone is None: # optional
break
if not phone_regex.match(phone):
phone = None
print(
"Phone number entered in wrong format, please try again, or leave blank"
)
self.cursor.execute(
"""
INSERT INTO persons
VALUES(?, ?, ?, ?, ?, ?);
""",
(fname, lname, bdate, bplace, address, phone),
)
def process_bill_of_sale(self):
print()
print("Processing a bill of sale.")
vin = input_util.read_string(
"Please enter vehicle identification number: ")
current_fname = input_util.read_string(
"Please enter current owner's first name: ")
current_lname = input_util.read_string(
"Please enter current owner's last name: ")
self.cursor.execute(
"""
SELECT regno, vin, fname, lname
FROM registrations r
WHERE r.vin LIKE ?
ORDER BY regdate DESC;
""", (vin, ))
current_registration = self.cursor.fetchone()
if current_registration is None:
print("The registration number does not exist.")
return
registered_fname = current_registration[2].lower()
registered_lname = current_registration[3].lower()
if registered_fname != current_fname.lower(
) or registered_lname != current_lname.lower():
print(
"The vehicle is owned by someone else, transfer cannot be done."
)
return
new_fname = input_util.read_string(
"Please enter new owner's first name: ")
new_lname = input_util.read_string(
"Please enter new owner's last name: ")
plate = input_util.read_string("Please enter plate number: ")
new_owner = self.__get_person(new_fname, new_lname)
if new_owner is None:
print(
"New owner does not exist in database, please enter optional details."
)
self.__add_person(fname=new_fname, lname=new_lname)
self.cursor.execute(
"""
UPDATE registrations
SET expiry = DATE('now')
WHERE regno = ?;
""", (current_registration[0], ))
self.cursor.execute(
"""
INSERT INTO registrations
VALUES ((SELECT MAX(regno) + 1 FROM registrations), DATE('now'), DATE('now', '+1 year'), ?, ?, ?, ?);
""", (plate, current_registration[1], new_fname, new_lname))
self.connection.commit()
print("Bill of sale processed.")
def issue_ticket(self):
print()
print("Issuing a ticket.")
registration_number = input_util.read_int(
"Please enter registration number: ")
self.cursor.execute(
"""
SELECT r.fname, r.lname, v.make, v.model, v.year, v.color
FROM registrations r, vehicles v
WHERE r.regno = ? AND r.vin = v.vin;
""",
(registration_number, ),
)
row = self.cursor.fetchone()
if row is None:
print("Record not found.")
return
print("|".join(str(elem) for elem in row))
violation_date = input_util.read_date("Please enter violation date: ",
optional=True)
violation_text = input_util.read_string(
"Please enter violation text: ")
while True:
fine_amount = input_util.read_int("Please enter fine amount: ")
if fine_amount <= 0:
print("Fine amount must be larger than 0.")
else:
break
self.cursor.execute(
"""
INSERT INTO tickets
VALUES ((SELECT max(tno) + 1 FROM tickets), ?, ?, ?, IFNULL(?, DATE('now')));
""",
(registration_number, fine_amount, violation_text, violation_date),
)
self.connection.commit()
print("Ticket Issued.")
def find_car_owner(self):
print()
print("Finding a car owner.")
make = "%"
model = "%"
year = "%"
color = "%"
plate = "%"
while True:
print("1. Enter make")
print("2. Enter model")
print("3. Enter year")
print("4. Enter color")
print("5. Enter plate")
print("6. Execute search")
choice = input("Please choose an option: ")
if choice == "1":
make = input_util.read_string("Please enter make: ")
elif choice == "2":
model = input_util.read_string("Please enter model: ")
elif choice == "3":
year = input_util.read_string("Please enter year: ")
elif choice == "4":
color = input_util.read_string("Please enter color: ")
elif choice == "5":
plate = input_util.read_string("Please enter plate: ")
elif choice == "6":
break
else:
print("Invalid choice.")
self.cursor.execute(
"""
SELECT vin, make, model, year, color, plate
FROM (
SELECT regno, vin, make, model, year, color, plate
FROM vehicles LEFT OUTER JOIN registrations USING (vin)
UNION
SELECT regno, vin, make, model, year, color, plate
FROM registrations LEFT OUTER JOIN vehicles USING (vin)
)
WHERE make LIKE ? AND
model LIKE ? AND
year LIKE ? AND
color LIKE ? AND
IFNULL(plate, '') LIKE ?;
""",
(make, model, year, color, plate),
)
rows = self.cursor.fetchall()
if len(rows) == 0:
print("No result.")
elif len(rows) >= 4:
for i in range(len(rows)):
print(
str(i) + ". " +
"|".join(self.__normalize_vehicle_summary(rows[i][1:])))
choice = input_util.read_int(
"Please choose a result to see full information: ")
if choice < 0 or choice >= len(rows):
print("Invalid choice.")
return
self.__print_row(rows[choice])
else:
for row in rows:
self.__print_row(row)
def register_birth(self, username):
print()
print("Registering a birth")
first_name = input_util.read_name("Please enter baby's first name: ")
last_name = input_util.read_name("Please enter baby's last name: ")
self.cursor.execute(
"""
SELECT *
FROM births b, persons p
WHERE (b.fname LIKE ? AND b.lname LIKE ?) OR
(p.fname LIKE ? AND p.lname LIKE ?);
""",
(first_name, last_name, first_name, last_name),
)
if self.cursor.fetchone() is not None:
print(
"\nPerson with same name already exists. Cancelling registration..."
)
return
gender = None
while True:
gender = input_util.read_string(
"Please enter baby's gender (M/F): ")
if (gender.casefold() == "m".casefold()
or gender.casefold() == "f".casefold()):
break
print("Gender must either be m or f, please try again")
while True:
birth_date = input_util.read_date(
"Please enter baby's birth date: ")
bday_date = datetime.strptime(birth_date, "%Y-%m-%d").date()
if bday_date > date.today():
print("Baby can't be born in the future.")
else:
break
birth_place = input_util.read_string(
"Please enter baby's birth place: ")
mother_fname = input_util.read_name(
"Please enter mother's first name: ")
mother_lname = input_util.read_name(
"Please enter mother's last name: ")
father_fname = input_util.read_name(
"Please enter father's first name: ")
father_lname = input_util.read_name(
"Please enter father's last name: ")
registration_date = date.today().strftime("%Y-%m-%d")
if (first_name.lower() == mother_fname.lower()
and last_name.lower() == mother_lname.lower()) or (
first_name.lower() == father_fname.lower()
and last_name.lower() == father_lname.lower()):
print("A person cannot give birth to him or herself.")
return
elif (mother_fname.lower() == father_fname.lower()
and mother_lname.lower() == father_lname.lower()):
print("A person cannot have the same mother and father.")
return
self.cursor.execute(
"""
SELECT city
FROM users
WHERE uid LIKE ?;
""",
(username, ),
)
(city, ) = self.cursor.fetchone()
mother = self.__get_person(mother_fname, mother_lname)
if mother is None:
print(
"Mother does not exist in database, please enter her details.")
self.__add_person(fname=mother_fname, lname=mother_lname)
mother = self.__get_person(mother_fname, mother_lname)
father = self.__get_person(father_fname, father_lname)
if father is None:
print(
"Father does not exist in database, please enter his details.")
self.__add_person(fname=father_fname, lname=father_lname)
father = self.__get_person(father_fname, father_lname)
address = mother[4]
phone = mother[5]
self.__add_person(first_name, last_name, birth_date, birth_place,
address, phone)
self.cursor.execute(
"""
INSERT INTO births
VALUES((SELECT MAX(regno) + 1 FROM births), ?, ?, ?, ?, ?, ?, ?, ?, ?);
""",
(
first_name,
last_name,
registration_date,
city,
gender,
father[0],
father[1],
mother[0],
mother[1],
),
)
self.connection.commit()
print("Birth registered.")
def register_birth(self, username):
print()
print("Registering a birth")
first_name = input_util.read_string("Please enter baby's first name: ")
last_name = input_util.read_string("Please enter baby's last name: ")
gender = input_util.read_string(
"Please enter baby's gender (M/F): ") # TODO: Validation
birth_date = input_util.read_date("Please enter baby's birth date: ")
birth_place = input_util.read_string(
"Please enter baby's birth place: ")
mother_fname = input_util.read_string(
"Please enter mother's first name: ")
mother_lname = input_util.read_string(
"Please enter mother's last name: ")
father_fname = input_util.read_string(
"Please enter father's first name: ")
father_lname = input_util.read_string(
"Please enter father's last name: ")
registration_date = date.today().strftime("%Y-%m-%d")
self.cursor.execute(
"""
SELECT city
FROM users
WHERE uid LIKE ?;
""", (username, ))
(city, ) = self.cursor.fetchone()
mother = self.__get_person(mother_fname, mother_lname)
if mother is None:
print(
"Mother does not exist in database, please enter her details.")
self.__add_person(fname=mother_fname, lname=mother_lname)
mother = self.__get_person(mother_fname, mother_lname)
father = self.__get_person(father_fname, father_lname)
if father is None:
print(
"Father does not exist in database, please enter his details.")
self.__add_person(fname=father_fname, lname=father_lname)
father = self.__get_person(father_fname, father_lname)
address = mother[4]
phone = mother[5]
if address is None:
address = "NULL"
if phone is None:
phone = "NULL"
self.__add_person(first_name, last_name, birth_date, birth_place,
address, phone)
self.cursor.execute(
"""
INSERT INTO births
VALUES((SELECT MAX(regno) + 1 FROM births), ?, ?, ?, ?, ?, ?, ?, ?, ?);
""", (first_name, last_name, registration_date, city, gender,
father[0], father[1], mother[0], mother[1]))
self.connection.commit()
print("Birth registered.")
def get_driver_abstract(self):
print()
print("Getting a driver abstract.")
first_name = input_util.read_string("Please enter first name: ")
last_name = input_util.read_string("Please enter last name: ")
data = {'fname': first_name, 'lname': last_name}
self.cursor.execute(
"""
SELECT ticket_lifetime.tickets_count,
demerit_lifetime.demerit_count, demerit_lifetime.demerit_sum,
ticket_two_years.tickets_count,
demerit_two_years.demerit_count, demerit_two_years.demerit_sum
FROM (
SELECT COUNT(t.tno) AS tickets_count
FROM registrations r, tickets t
WHERE r.fname LIKE :fname AND r.lname LIKE :lname AND
r.regno = t.regno
) as ticket_lifetime, (
SELECT COUNT(*) AS demerit_count, IFNULL(SUM(d.points), 0) AS demerit_sum
FROM demeritNotices d
WHERE d.fname LIKE :fname AND d.lname LIKE :lname
) as demerit_lifetime, (
SELECT COUNT(t.tno) AS tickets_count
FROM registrations r, tickets t
WHERE r.fname LIKE :fname AND r.lname LIKE :lname AND
r.regno = t.regno AND
t.vdate >= DATE('now', '-2 years')
) as ticket_two_years, (
SELECT COUNT(*) AS demerit_count, IFNULL(SUM(d.points), 0) AS demerit_sum
FROM demeritNotices d
WHERE d.fname LIKE :fname AND d.lname LIKE :lname AND
d.ddate >= DATE('now', '-2 years')
) as demerit_two_years;
""", data)
result = self.cursor.fetchone()
print(
f"Lifetime: {result[0]} tickets, {result[1]} demerit notices, {result[2]} demerit points"
)
print(
f"Last two years: {result[3]} tickets, {result[4]} demerit notices, {result[5]} demerit points"
)
if result[0] == 0:
return
self.cursor.execute(
"""
SELECT t.tno, t.vdate, t.violation, t.fine, t.regno, v.make, v.model
FROM tickets t, registrations r, vehicles v
WHERE r.fname LIKE :fname AND r.lname LIKE :lname AND
v.vin = r.vin AND r.regno = t.regno
ORDER BY t.vdate DESC;
""", data)
consumed = 0
while consumed < result[0]:
print("1. Show tickets")
print("2. Exit")
choice = input("Please choose an option: ")
if choice == "1":
printed = 0
while printed < 5 and consumed < result[0]:
print("|".join(
str(elem) for elem in self.cursor.fetchone()))
printed += 1
consumed += 1
if consumed < result[0]:
print(
"More tickets not shown, please select show tickets to show more."
)
elif choice == "2":
break
else:
print("Invalid choice, please try again.")