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)