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("Please enter violation text: ")
fine_amount = input_util.read_int("Please enter fine amount: ")
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 process_payment(self):
print()
print("Processing a payment.")
while True:
ticket_number = input_util.read_int("Please enter ticket number: ")
self.cursor.execute(
"""
SELECT fine
FROM tickets
WHERE tno = ?;
""", (ticket_number, ))
row = self.cursor.fetchone()
if row is None:
print("Invalid ticket number, please try again.")
else:
(fine, ) = row
self.cursor.execute(
"""
SELECT sum(amount)
FROM payments
WHERE tno = ?;
""", (ticket_number, ))
(payment_sum, ) = self.cursor.fetchone()
if payment_sum is None:
payment_sum = 0
if payment_sum >= fine:
print("Ticket is already payed, please try again.")
else:
break
print("Fine: " + str(fine))
print("Amount paid: " + str(payment_sum))
while True:
amount = input_util.read_int("Please enter the payment amount: ")
if amount <= 0:
print("Amount should be greater than 0, please try again.")
elif amount + payment_sum > fine:
print("Payment amount exceeds fine, please try again.")
else:
break
pay_date = date.today().strftime("%Y-%m-%d")
self.cursor.execute(
"""
INSERT INTO payments
VALUES(?, ?, ?);
""", (ticket_number, pay_date, amount))
self.connection.commit()
print("Ticket payment processed.")
def renew_vehicle_registration(self):
print()
print("Renewing a vehicle registration.")
registration_number = input_util.read_int(
"Please enter the vehicle registration number: ")
self.cursor.execute(
"""
SELECT expiry
FROM registrations
WHERE regno = ?;
""",
(registration_number, ),
)
row = self.cursor.fetchone()
if row is None:
print("Record not found.")
return
expiry_date = datetime.strptime(row[0], "%Y-%m-%d").date()
if expiry_date <= date.today():
expiry_date = date.today()
try:
expiry_date = expiry_date.replace(year=expiry_date.year + 1)
except ValueError: # in case of a leap year
expiry_date + (date(expiry_date.year + 1, 1, 1) -
date(expiry_date.year, 1, 1))
self.cursor.execute(
"""
UPDATE registrations
SET expiry = ?
WHERE regno = ?;
""",
(expiry_date, registration_number),
)
self.connection.commit()
print("Vehicle registration renewed.")
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 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("Please enter make: ")
elif choice == "2":
model = input("Please enter model: ")
elif choice == "3":
year = input("Please enter year: ")
elif choice == "4":
color = input("Please enter color: ")
elif choice == "5":
plate = input("Please enter plate: ")
elif choice == "6":
break
else:
print("Invalid choice.")
self.cursor.execute(
"""
SELECT v.vin, v.make, v.model, v.year, v.color, r.plate
FROM vehicles v, registrations r
WHERE v.vin = r.vin AND
v.make LIKE ? AND
v.model LIKE ? AND
v.year LIKE ? AND
v.color LIKE ? AND
r.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(str(elem) for elem in rows[i][1:]))
choice = input_util.read_int("Please choose an option: ")
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 process_payment(self):
print()
print("Processing a payment.")
while True:
ticket_number = input_util.read_int("Please enter ticket number: ")
self.cursor.execute(
"""
SELECT fine
FROM tickets
WHERE tno = ?;
""",
(ticket_number, ),
)
row = self.cursor.fetchone()
if row is None:
print("Ticket doesn't exist, please try again.")
else:
(fine, ) = row
self.cursor.execute(
"""
SELECT pdate
FROM payments
WHERE tno = ?
ORDER BY pdate DESC
LIMIT 1;
""",
(ticket_number, ),
)
latest_payment = self.cursor.fetchone()
if latest_payment is not None:
(latest_payment, ) = latest_payment
if latest_payment == date.today().strftime("%Y-%m-%d"):
print(
"A payment was already made today for this ticket, cancelling..."
)
return
self.cursor.execute(
"""
SELECT sum(amount)
FROM payments
WHERE tno = ?;
""",
(ticket_number, ),
)
(payment_sum, ) = self.cursor.fetchone()
if payment_sum is None:
payment_sum = 0
if payment_sum >= fine:
print("Ticket is already payed, please try again.")
else:
break
print("Fine: " + str(fine))
print("Amount paid: " + str(payment_sum))
while True:
amount = input_util.read_int("Please enter the payment amount: ")
if amount <= 0:
print("Amount should be greater than 0, please try again.")
elif amount + payment_sum > fine:
print("Payment amount exceeds fine, please try again.")
else:
break
pay_date = date.today().strftime("%Y-%m-%d")
self.cursor.execute(
"""
INSERT INTO payments
VALUES(?, ?, ?);
""",
(ticket_number, pay_date, amount),
)
self.connection.commit()
print("Ticket payment processed.")