def showEmployees(self, show_dialog = False):
'''
Show employees we have in the app
'''
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute("SELECT * FROM employee ORDER BY name")
records_info = dbRowsToDict(cursor)
rows = records_info['rows']
db.close()
drawTable(records_info['columns'], rows, 'List of Registered Employees', test_mode)
if rows:
self.employees_found = True
if show_dialog:
employee_id = get_input('To see employee income report, enter employee id: ')
if employee_id != 'q':
self.employeeIncomeReports(employee_id)
return rows
def showVehicleTypes(self, show_dialog = True):
'''
Show vehicles types we have in the app
@input show_dialog: boolean. If there are registered vehicle types, should user be allowed to select one for management?
'''
db = DBOperation()
cur = db.connection.cursor()
cur.execute("SELECT * FROM vehicletype")
records_info = dbRowsToDict(cur)
rows = records_info['rows']
drawTable(records_info['columns'], rows, 'List of Vehicle Types')
db.close()
if rows:
self.vehicles_found = True
if show_dialog and rows:
# we have vehicle types so we can attach the types to ferries here
id = get_input('To set ferry - vehicle setting, type in the id of the vehicle type: ')
if id =='q':
sys.exit()
else:
FerryVehicleSetting('vehicle_type', id)
def showFerries(self, show_dialog = True):
'''
Show ferries we have in the app
@input show_dialog : boolean. If there are ferries, do we need to show dialog to manage them?
'''
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute("SELECT * FROM ferry")
records_info = dbRowsToDict(cursor)
rows = records_info['rows']
if rows:
self.ferries_found = True
drawTable(records_info['columns'], rows, 'List of Registered Ferries', test_mode)
db.close()
if show_dialog and rows:
# we have ferries so we can attach the types to vehicletypes here
id = get_input('To set ferry - vehicle setting, type in the id of the ferry ')
if id =='q':
sys.exit()
else:
FerryVehicleSetting('ferry', id)
def addVehicle(self):
'''
Add a new vehicle
'''
print('Follow the questions to add a new vehicle. A vehicle can be of the following registered type only')
vehicle_type = VehicleType(False)
vehicle_type.showVehicleTypes(False)
if not vehicle_type.vehicles_found:
print('To add vehicles, you must add vehicle types first')
return []
sys.exit()
db = DBOperation()
plate_number = get_input('Add plate of the vehicle: ')
gas_capacity = get_input('Enter the gas/fuel capacity of the vehicle: ')
vehicletype_id = get_input('From the list above, select the vehicle type ID: ')
# if plate number exists, simply return its id in the database
# is it unique?
cur = db.connection.cursor()
cur.execute("SELECT * FROM vehicle WHERE platenumber=?", (plate_number,))
records_info = dbRowsToDict(cur)
if len(records_info['rows'])>0:
db.close()
return records_info['rows'][0]
# doesn't exist. so confirm data is basically fine
try:
gas_capacity = float(gas_capacity)
except:
gas_capacity = 0
if gas_capacity<=0:
raise Exception('Gas capacity of a vehicle must be greater than 0')
cur = db.connection.cursor() #rember dbRowsToDict closes curors
cur.execute('INSERT INTO vehicle (platenumber,vehicletype_id, gas_capacity ) VALUES (?, ?, ?) ', (plate_number, vehicletype_id, gas_capacity))
db.connection.commit()
new_id = cur.lastrowid
added_vehicle_info = []
if new_id >0:
# added successfully. return the object now.
added_vehicle_info = self.showVehicleInfo(new_id)
else:
print('There was an error adding the vehicle')
print('The vehichle was added successfully with id ', new_id, ' . Please continue servicing the vehicle now')
cur.close()
db.close()
return added_vehicle_info
def showVehicles(self, return_one = False):
'''
Show vehicles we have in the app
@input return_one: boolean. Do we need to return a vehicle? If we have one vehicle only
return it. Else, ask the user to select one
@output dictionary if return_one = True, else an array of dictionaries of records
'''
plate_number = get_input('Enter the plate number of the vehicle you want to search: ')
db = DBOperation()
cur = db.connection.cursor()
if not plate_number:
cur.execute("SELECT * FROM vehicle")
else:
cur.execute("SELECT * FROM vehicle WHERE platenumber=?", (plate_number,))
records_info = dbRowsToDict(cur) #diciontary data
rows = records_info['rows']
drawTable(records_info['columns'], rows, 'List of Vehicles')
found_vehicles = len(records_info['rows'])
db.close()
if found_vehicles>0:
self.vehicles_found = True
#if it is one vehicle return it
if return_one:
# until user selects one of the list vehicles or cancels
selected_vehicle = 0
while selected_vehicle==0:
value = get_input('Enter the ID of the vehicle you want to work with or q to quit: ')
if value == 'q':
return {}
else:
# it must exist
counter = 0
for row in rows:
if row['id'] == value:
selected_vehicle = row['id']
return rows[counter]
counter = counter + 1
else:
return rows
else:
if return_one:
return {} # we didnt find
return []
def employeeIncomeReports(self, employee_id):
'''
Print income an employee has made so far
'''
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute('SELECT amount,paid_on FROM employee_incomes WHERE employee_id=? ORDER BY id', (employee_id,))
records_info = dbRowsToDict(cursor)
drawTable(records_info['columns'], records_info['rows'], 'Employee Income Basic Report', test_mode)
db.close()
def routesCovered(self, service_id):
'''
Print out the destinations taken already for the vehicle
'''
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute('SELECT routetype.name AS route_name , routes.door_status AS door_status FROM routes,routetype WHERE routes.route_type_id=routetype.id AND routes.service_id=? ORDER BY routes.id', (service_id,))
records_fetcher = dbRowsToDict(cursor)
drawTable(records_fetcher['columns'], records_fetcher['rows'], 'Path Taken By Vehicle')
db.close()
def serviceInfo(self, service_id):
'''
Return information about the service
'''
db = DBOperation()
cur = db.connection.cursor()
cur.execute('SELECT * FROM service WHERE id=?', (service_id,))
records_info = dbRowsToDict(cur)
db.close()
if records_info['rows']:
return records_info['rows'][0]
return None
def showVehicleInfo(self, id):
'''
Return vehicle information of a specific vehicle
@input id: id of the vehicle whose information you want
'''
db = DBOperation()
cur = db.connection.cursor()
cur.execute('SELECT * FROM vehicle WHERE id=?', (id,))
records_info = dbRowsToDict(cur)
db.close()
if records_info['rows']:
return records_info['rows'][0]
return None
def __init__(self, source_type, record_id):
'''
@input source_type: string. ferry if calling from ferries i.e. record_id is from ferry model.
else vehicleid
@input record_id: id from ferry or vehicletype
'''
db = DBOperation()
cursor = db.connection.cursor()
#get current settings based on field type
if source_type == 'ferry':
cursor.execute('SELECT * FROM ferryvehicletype WHERE ferry_id=?', ( record_id, ) )
else:
cursor.execute('SELECT * FROM ferryvehicletype WHERE vehicletype_id=?', ( record_id, ) )
records_info = dbRowsToDict(cursor)
rows = records_info['rows']
drawTable(records_info['columns'], rows,'Ferries vs Vehicle Types Relationship')
db.close()
#incase user wants to create associations, display ferries or types
if source_type == 'ferry':
# we are viewing ferry here. So display vehicle types
source = VehicleType(False)
source.showVehicleTypes(False)
# do we have vehicle types to attach with the active ferry with?
if source.vehicles_found:
vehicle_id = get_input('Enter the vehicle type you want to relate with a ferry with or q to quit ')
if vehicle_id=='q':
sys.exit()
self.createRelationships(vehicle_id,record_id)
elif source_type =='vehicle_type':
# we are viewing vehicle type here. So display ferries to attach it with
source = Ferry(False)
source.showFerries(False)
if source.ferries_found:
ferry_id = get_input('Enter the ferry id you want to relate with a vehicle with or q to quit ')
if ferry_id == 'q':
sys.exit()
self.createRelationships(ferry_id,record_id)
def employeeLogin(self, emp_code, password):
'''
Login the employee to the system
@input emp_code: username/employee code
@input password
@output empty object if emp_code and password are wrong. Else, returns employee detail
'''
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute('SELECT * FROM employee WHERE password=? AND emp_code=?', (password, emp_code))
records_fetcher = dbRowsToDict(cursor)
db.close()
return records_fetcher['rows']
def maxOrder(self):
'''
Return the route with the higest order (i.e. visited last during the boarding)
'''
route_order = 0 # assume we have no route type and this will be first entry
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute('SELECT MAX(route_order) as max_order FROM routetype')
records_info = dbRowsToDict(cursor)
rows = records_info['rows']
if rows and rows[0]['max_order'] is not None:
route_order = rows[0]['max_order']
db.close()
return route_order
def showVehicleTypeInfo(self , id):
'''
Return information of a specific vechile
'''
db = DBOperation()
cur = db.connection.cursor()
cur.execute("SELECT * FROM vehicletype WHERE id=?", (id,))
records_info = dbRowsToDict(cur)
rows = records_info['rows']
db.close()
if rows:
return rows[0]
return None
def showBoardings(self):
'''
Show boardings of vehicles
'''
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute('SELECT service.id AS service_id, service.amount AS boarding_price,service.registered_on AS registered_on,service.gas_level AS gas_level, vehicle.gas_capacity AS gas_capacity, vehicle.platenumber AS plate_number,employee.name AS emp_name, employee.emp_code AS employee_code,employee.ticket_percentage, ((1.0 * employee.ticket_percentage)/100) * vehicletype.boarding_price AS employee_cut FROM service,vehicle,employee,vehicletype WHERE vehicletype.id= vehicle.vehicletype_id AND vehicle.id=service.vehicle_id AND service.employee_id=employee.id ORDER BY service.id DESC')
records = dbRowsToDict(cursor)
drawTable(records['columns'], records['rows'], 'List of Boarding Services')
db.close()
if records['rows']:
#give user the option to view routes taken by a specific vehicle
service_id = get_input('To view routes covered by a specific service, enter its id: ')
if service_id!='q':
self.routesCovered(service_id)
else:
sys.exit()
def showRoutes(self):
'''
Show routes we have in the app
'''
db = DBOperation()
cursor = db.connection.cursor()
cursor.execute("SELECT * FROM routetype ORDER BY route_order")
records_info = dbRowsToDict(cursor)
rows = records_info['rows']
if rows:
self.routes_found = True
drawTable(records_info['columns'], rows, 'List of Registered Routes', test_mode)
db.close()
return rows