def stations(self):
""" checks for columns in station table and creates out of station value if it doesn't exist. """
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: Tables - Station > add out of station")
sql = 'INSERT OR IGNORE INTO stations (station) VALUES ("out of station")'
commit(sql)
def update_null_leavetime_type():
""" this converts leave type to an empty string and leave time to an empty float. """
types = ""
times = float(0.0)
sql = "UPDATE rings3 SET leave_type='%s',leave_time='%s'" \
"WHERE leave_type IS NULL" \
% (types, times)
commit(sql)
def skippers(self):
""" put records in the skippers table """
skip_these = (("354", "stand by"), ("613", "stewards time"), ("743", "route maintenance"))
for rec in skip_these:
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: Tables - Skippers > {}".format(rec[0]))
sql = 'INSERT OR IGNORE INTO skippers(code, description) VALUES ("%s", "%s")' % (rec[0], rec[1])
commit(sql)
def fix_carriers(self):
""" check if the name is all lower, if not, update the record. """
for i in range(self.iterations):
for carrier in self.name_array[i]:
if not carrier.islower():
sql = "UPDATE {} SET {} = '%s' WHERE {} = '%s'"\
.format(self.tablelist[i], self.name_convention[i], self.name_convention[i]) \
% (carrier.lower(), carrier)
commit(sql)
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 tolerances(self):
""" checks the tolerances table and inputs values if they do not exist. """
tolerance_array = (
(0, "ot_own_rt", 0),
(1, "ot_tol", 0),
(2, "av_tol", 0),
(3, "min_ss_nl", 25),
(4, "min_ss_wal", 25),
(5, "min_ss_otdl", 25),
(6, "min_ss_aux", 25),
(7, "allow_zero_top", "False"), # obsolete
(8, "allow_zero_bottom", "True"), # obsolete
(9, "pdf_error_rpt", "off"),
(10, "pdf_raw_rpt", "off"),
(11, "pdf_text_reader", "off"),
(12, "ns_auto_pref", "rotation"),
(13, "mousewheel", -1),
(14, "min_ss_overmax", 30),
(15, "abc_breakdown", "False"),
(16, "min_spd_empid", 50),
(17, "min_spd_alpha", 50),
(18, "min_spd_abc", 10),
(19, "speedcell_ns_rotate_mode", "True"),
(20, "ot_rings_limiter", 0),
(21, "pb_nl_wal", "True"),
(22, "pb_wal_otdl", "True"),
(23, "pb_otdl_aux", "True"),
(24, "invran_mode", "simple"),
(25, "min_ot_equit", 19),
(26, "ot_calc_pref", "off_route"),
(27, "min_ot_dist", 25),
(28, "ot_calc_pref_dist", "off_route"),
(29, "tourrings", 0),
(30, "spreadsheet_pref", "Mandates"),
(31, "lastfix", "1.000"),
(32, "min4_ss_nl", 19),
(33, "min4_ss_wal", 19),
(34, "min4_ss_otdl", 19),
(35, "min4_ss_aux", 19),
(36, "pb4_nl_wal", "True"),
(37, "pb4_wal_aux", "True"),
(38, "pb4_aux_otdl", "True"),
(39, "man4_dis_limit", "show all"),
(40, "speedsheets_fullreport", "False"),
(41, "offbid_distinctpage", "True"),
(42, "offbid_maxpivot", 2.0)
)
for tol in tolerance_array:
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: Tables - Tolerances {}".format(tol[1]))
sql = 'INSERT OR IGNORE INTO tolerances (row_id, category, tolerance) ' \
'VALUES ("%s", "%s", "%s")' % (tol[0], tol[1], tol[2])
commit(sql)
def tables(self):
"""
Make sure the count of the tables_sql and tables_text match or you will get a list out of sequence error
"""
tables_sql = (
'CREATE table IF NOT EXISTS stations (station varchar primary key)',
'CREATE table IF NOT EXISTS carriers (effective_date date, carrier_name varchar, list_status varchar, '
'ns_day varchar, route_s varchar, station varchar)',
'CREATE table IF NOT EXISTS rings3 (rings_date date, carrier_name varchar, total varchar, rs varchar, '
'code varchar, moves varchar, leave_type varchar, leave_time varchar, refusals varchar, bt varchar, '
'et varchar)',
'CREATE table IF NOT EXISTS name_index (tacs_name varchar, kb_name varchar, emp_id varchar)',
'CREATE table IF NOT EXISTS station_index (tacs_station varchar, kb_station varchar, finance_num varchar)',
'CREATE table IF NOT EXISTS skippers (code varchar primary key, description varchar)',
'CREATE table IF NOT EXISTS ns_configuration (ns_name varchar primary key, fill_color varchar, '
'custom_name varchar)',
'CREATE table IF NOT EXISTS tolerances (row_id integer primary key, category varchar, tolerance varchar)',
'CREATE table IF NOT EXISTS otdl_preference (quarter varchar, carrier_name varchar, preference varchar, '
'station varchar, makeups varchar)',
'CREATE table IF NOT EXISTS refusals (refusal_date varchar, carrier_name varchar, refusal_type varchar, '
'refusal_time varchar)',
'CREATE table IF NOT EXISTS informalc_grv (grv_no varchar, indate_start varchar, indate_end varchar,'
'date_signed varchar, station varchar, gats_number varchar, docs varchar, description varchar, '
'level varchar)',
'CREATE table IF NOT EXISTS informalc_awards (grv_no varchar,carrier_name varchar, hours varchar, '
'rate varchar, amount varchar)',
'CREATE table IF NOT EXISTS informalc_payouts(year varchar, pp varchar, payday varchar, '
'carrier_name varchar, hours varchar,rate varchar,amount varchar)',
'CREATE table IF NOT EXISTS dov(eff_date date, station varchar, day varchar, dov_time varchar, '
'temp varchar)'
)
tables_text = (
"Setting up: Tables - Station",
"Setting up: Tables - Carriers",
"Setting up: Tables - Rings",
"Setting up: Tables - Name Indexes",
"Setting up: Tables - Station Indexes",
"Setting up: Tables - Skippers",
"Setting up: Tables - NS Configurations",
"Setting up: Tables - Tolerances...",
"Setting up: Tables - OTDL Preference",
"Setting up: Tables - Refusals",
"Setting up: Tables - Informal C",
"Setting up: Tables - Informal C Awards",
"Setting up: Tables - Informal C Payouts",
"Setting up: Tables - DOV"
)
for i in range(len(tables_sql)):
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text(tables_text[i])
commit(tables_sql[i])
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 ns_config(self):
""" sets rotating non scheduled days if not set """
ns_sql = (
("yellow", "gold", "yellow"),
("blue", "navy", "blue"),
("green", "forest green", "green"),
("brown", "saddle brown", "brown"),
("red", "red3", "red"),
("black", "gray10", "black")
)
for ns in ns_sql:
self.pbar_counter += 1
self.pbar.move_count(self.pbar_counter)
self.pbar.change_text("Setting up: Tables - NS Configurations {}".format(ns[0]))
sql = 'INSERT OR IGNORE INTO ns_configuration(ns_name,fill_color,custom_name)VALUES("%s", "%s", "%s")'\
% (ns[0], ns[1], ns[2])
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 update_lastfix(self):
""" update the lastfix value in the tolerances table. """
sql = "UPDATE tolerances SET tolerance = '%s' WHERE category = 'lastfix'" % self.version
commit(sql)
def update_null_to_emptystring():
""" change any null values in bt and et to empty strings in the rings3 table. """
sql = "UPDATE rings3 SET bt = '' WHERE bt IS NULL"
commit(sql)
sql = "UPDATE rings3 SET et = '' WHERE et IS NULL"
commit(sql)
