def check_for_null_leave():
""" returns true if there are null values in leave time or type. """
sql = "SELECT * FROM rings3 WHERE leave_type IS NULL"
results = inquire(sql)
if results:
return True
return False
def check_for_null():
""" returns true if there are null values in bt """
sql = "SELECT * FROM rings3 WHERE bt IS NULL"
results = inquire(sql)
if results:
return True
return False
def rpt_carrier_history(carrier):
""" generates a report showing all records from a specified carrier. """
sql = "SELECT effective_date, list_status, ns_day, route_s, station" \
" FROM carriers WHERE carrier_name = '%s' ORDER BY effective_date DESC" % carrier
results = inquire(sql)
stamp = datetime.now().strftime("%Y%m%d_%H%M%S")
filename = "report_carrier_history" + "_" + stamp + ".txt"
report = open(dir_path('report') + filename, "w")
report.write("\nCarrier Status Change History\n\n")
report.write(
' Showing all status changes in the klusterbox database for {}\n\n'
.format(carrier))
report.write('{:<16}{:<8}{:<10}{:<31}{:<25}\n'.format(
"Date Effective", "List", "N/S Day", "Route/s", "Station"))
report.write(
'----------------------------------------------------------------------------------\n'
)
i = 1
for line in results:
report.write('{:<16}{:<8}{:<10}{:<31}{:<25}\n'.format(
dt_converter(line[0]).strftime("%m/%d/%Y"), line[1], line[2],
line[3], line[4]))
if i % 3 == 0:
report.write(
'----------------------------------------------------------------------------------\n'
)
i += 1
report.close()
if sys.platform == "win32": # open the text document
os.startfile(dir_path('report') + filename)
if sys.platform == "linux":
subprocess.call(["xdg-open", 'kb_sub/report/' + filename])
if sys.platform == "darwin":
subprocess.call(["open", dir_path('report') + filename])
def mousewheel(self):
""" initialize mousewheel - mouse wheel scroll direction """
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: Mousewheel")
sql = "SELECT tolerance FROM tolerances WHERE category = '%s'" % "mousewheel"
results = inquire(sql)
projvar.mousewheel = int(results[0][0])
def get_carriers(self):
""" get a list of distinct names from the carriers table. """
for i in range(self.iterations):
sql = "SELECT DISTINCT {} from {}".format(self.name_convention[i],
self.tablelist[i])
results = inquire(sql)
if results:
for carrier in results:
if not carrier[0].islower():
self.name_array[i].append(carrier[0])
def informalc(self):
""" check the informalc table and make changes if needed"""
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: Tables - Informal C > update ")
# modify table for legacy version which did not have level column of informalc_grv table.
sql = 'PRAGMA table_info(informalc_grv)' # get table info. returns an array of columns.
result = inquire(sql)
if len(result) <= 8: # if there are not enough columns add the leave type and leave time columns
sql = 'ALTER table informalc_grv ADD COLUMN level varchar'
commit(sql)
def list_of_stations(self):
""" sets up the list of stations project variable. """
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: List of Stations")
sql = "SELECT * FROM stations ORDER BY station"
results = inquire(sql)
# define and populate list of stations variable
projvar.list_of_stations = []
for stat in results:
projvar.list_of_stations.append(stat[0])
def delete_null_names(self):
""" delete all the records where the name is null """
for i in range(self.iterations):
sql = "SELECT DISTINCT {} from {}".format(self.name_convention[i],
self.tablelist[i])
results = inquire(sql)
if results:
for carrier in results:
if carrier[0] is None:
sql = "DELETE FROM {} WHERE {} IS NULL".format(
self.tablelist[i], self.name_convention[i])
commit(sql)
def minimum_recs(self, station):
""" places 7 records dated year 1 in the DOV table so that the database always has a default record
for any day. """
self.station = station
for day in self.day:
# check if the minimum record is in the database/ there is one for each day
sql = "SELECT * FROM dov WHERE station = '%s' AND eff_date = '%s' AND day = '%s'" % \
(self.station, "0001-01-01 00:00:00", day)
result = inquire(sql)
if not result:
# if the minimum record is not in the database, then add it.
sql = "INSERT INTO dov (eff_date, station, day, dov_time, temp) " \
"VALUES('%s', '%s', '%s', '%s', '%s')" % \
("0001-01-01 00:00:00", self.station, day, self.defaulttime, False)
commit(sql)
def rings(self):
""" sets up the rings table """
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: Tables - Rings > leave time/type")
# modify table for legacy version which did not have leave type and leave time columns of
# table.
sql = 'PRAGMA table_info(rings3)' # get table info. returns an array of columns.
result = inquire(sql)
if len(result) < 7: # if there are not enough columns, add the leave type column
sql = 'ALTER table rings3 ADD COLUMN leave_type varchar'
commit(sql)
if len(result) < 8: # if there are not enough columns, add the leave time column
sql = 'ALTER table rings3 ADD COLUMN leave_time varchar'
commit(sql)
if len(result) < 9: # if there are not enough columns, add the refusals column
sql = 'ALTER table rings3 ADD COLUMN refusals varchar'
commit(sql)
if len(result) < 10: # if there are not enough columns, add the bt column
sql = 'ALTER table rings3 ADD COLUMN bt varchar'
commit(sql)
if len(result) < 11: # if there are not enough columns, add the et column
sql = 'ALTER table rings3 ADD COLUMN et varchar'
commit(sql)
def get(self):
""" get the number of the most resently done fix from the tolerances table."""
sql = "SELECT tolerance from tolerances WHERE category = '%s'" % "lastfix"
result = inquire(sql)
self.lastfix = float(result[0][0])
