def get_distances(self) -> dict: # Get distances matrix of mills and distribution centers.
with DBSession(self.systemname, self.dbfile) as session:
distance = dict()
data = session.execute("select a.CENTERID, b.MILLID, a.LATITUDE, a.LONGITUDE, b.LATITUDE, b.LONGITUDE from centers a cross join mills b;")
for center, mill, center_lat, center_lon, mill_lat, mill_lon in data:
distance[mill.replace(' ', ''), center.replace(' ', '')] = haversine_((center_lat, center_lon), (mill_lat, mill_lon))
return distance
def load_location_table(data_path, location_file, systemname, dbfile):
with DBSession(systemname, dbfile) as session, codecs.open(data_path + location_file, 'r',
encoding='ascii', errors='ignore') as f_handle:
reader = csv.DictReader(f_handle)
for item in reader:
session.add(Location(**item))
print("File_loaded...! {}".format(location_file))
def get_capacity(self) -> dict:
with DBSession(self.systemname, self.dbfile) as session:
capacity = session.query(Centers.CENTER_ID,
Centers.SUPPLY_CAPACITY)
return {
obj[0].replace(' ', '!'): int(obj[1].replace(',', ''))
for obj in capacity
}
def get_home_dict(self) -> dict:
with DBSession(self.systemname, self.dbfile) as session:
home_map = defaultdict(set)
homes = session.query(Opponents.HOME_TEAM, Opponents.AWAY_TEAM)
for item in homes:
home_map[item[0]].add(item[1])
home_teams = set(home_map.keys())
home_map['BYE'] = home_teams # Add bye home team for all teams.
return home_map
def load_opponents_table(data_path, _file, systemname, dbfile):
with DBSession(systemname, dbfile) as session, codecs.open(data_path + _file, 'r',
encoding='ascii', errors='ignore') as f_handle:
next(f_handle) # Skip headers of csv file.
reader = csv.DictReader(f_handle, fieldnames=Opponents.metadata.tables['opponents'].columns.keys())
for item in reader:
obj = Opponents(**item)
session.add(obj)
print(f"File_loaded...! {_file}")
def get_distances(self) -> dict:
with DBSession(self.systemname, self.dbfile) as session:
distance = dict()
data = session.execute(
"select a.CENTER_ID, b.STORE_NUMBER, a.LATITUDE, a.LONGITUDE, b.LATITUDE, b.LONGITUDE from centers a cross join goodstores b;"
)
for center, store, center_lat, center_lon, store_lat, store_lon in data:
distance[center.replace(' ', '!'), store] = haversine_(
(center_lat, center_lon), (store_lat, store_lon))
return distance
def get_away_dict(self) -> dict:
""" away matrix key:- team value :- list of teams"""
with DBSession(self.systemname, self.dbfile) as session:
away_map = defaultdict(set)
aways = session.query(Opponents.AWAY_TEAM, Opponents.HOME_TEAM)
for item in aways:
away_map[item[0]].add(item[1])
for key in away_map.keys(): # add bye opponent to all away teams.
away_map[key].add('BYE')
return away_map
def load_network_slot_week_table(data_path, _file, systemname, dbfile):
with DBSession(systemname, dbfile) as session, codecs.open(data_path + _file, 'r',
encoding='ascii', errors='ignore') as f_handle:
next(f_handle) # Skip headers of csv file.
reader = csv.DictReader(f_handle, fieldnames=NetworkSlots.metadata.tables['networkslots'].columns.keys())
row_id = count(1)
for item in reader:
item['ROW_ID'] = int(next(row_id))
session.add(NetworkSlots(**item))
print(f"File_loaded...! {_file}")
def get_milk_production(self) -> dict:
""" milk prodiction matrix (calving_month, demand_month)"""
with DBSession(self.systemname, self.dbfile) as session:
cost = session.query(
Production.CALVIN_MONTH, Production.M_1, Production.M_2,
Production.M_3, Production.M_4, Production.M_5, Production.M_6,
Production.M_7, Production.M_8, Production.M_9,
Production.M_10, Production.M_11, Production.M_12)
return {(int(obj[0]), idx): float(item)
for obj in cost
for idx, item in enumerate(obj[1:], start=1)}
def get_game_variables(self):
with DBSession(self.systemname, self.dbfile) as session:
vars = session.query(
GameVariables.AWAY_TEAM,
GameVariables.HOME_TEAM,
GameVariables.WEEK, # string
GameVariables.SLOT,
GameVariables.NETWORK,
GameVariables.QUAL_POINTS)
return [(item[0], item[1], item[2], item[3], item[4], item[5])
for item in vars]
def load_team_data_table(data_path, _file, systemname, dbfile):
with DBSession(systemname, dbfile) as session, codecs.open(data_path + _file, 'r',
encoding='ascii', errors='ignore') as f_handle:
next(f_handle) # Skip headers of csv file.
row_id = count(1)
reader = csv.DictReader(
f_handle, fieldnames=TeamData.metadata.tables['teamdata'].columns.keys())
for item in reader:
item['ROW_ID'] = int(next(row_id))
obj = TeamData(**item)
session.add(obj)
print(f"File_loaded...! {_file}")
def load_location_table(data_path, location_file, systemname, dbfile):
""" Loades Location table directoly from the zip as chunks into the sqlite database.
"""
with DBSession(systemname,
dbfile) as session, codecs.open(
data_path + location_file,
'r',
encoding='ascii',
errors='ignore') as f_handle:
reader = csv.DictReader(f_handle)
for item in reader:
session.add(Location(**item))
print("File_loaded...! {}".format(location_file))
def load_trip_table(data_path, zip_file, systemname, dbfile):
with zipfile.ZipFile(data_path + zip_file) as zipf:
row_id = count(1, 1)
for f_name in zipf.filelist:
with DBSession(systemname,
dbfile) as session, zipf.open(f_name,
'r') as f_handle:
header = next(f_handle).decode().strip('\r\n').split(',')
header.insert(0, 'ROW_ID')
for row in f_handle:
row = row.decode().strip('\r\n').split(',')
row.insert(0, next(row_id))
session.add(Trip(**dict(zip(header, row))))
print("File_loaded...! {}".format(f_name))
def load_production_table(data_path, _file, systemname, dbfile):
with DBSession(systemname,
dbfile) as session, codecs.open(
data_path + _file,
'r',
encoding='ascii',
errors='ignore') as f_handle:
next(f_handle) # Skip headers of csv file.
reader = csv.DictReader(
f_handle,
fieldnames=Production.metadata.tables['production'].columns.keys())
for item in reader:
item.pop(None)
session.add(Production(**item))
print(f"File_loaded...! {_file}")
def load_trip_table(data_path, zip_file, systemname, dbfile):
with zipfile.ZipFile(data_path + zip_file) as zipf:
row_id = count(1, 1)
file_count = 0
for f_name in zipf.filelist:
record_count = 0
with DBSession(systemname, dbfile) as session, zipf.open(f_name, 'r') as f_handle:
file_count = file_count + 1
header = next(f_handle).decode().strip('\r\n').split(',')
header.insert(0, 'ROW_ID')
for row in f_handle:
row = row.decode().strip('\r\n').split(',')
row.insert(0, next(row_id))
session.add(Trip(**dict(zip(header, row))))
record_count = record_count + 1
print("Records loaded: {} from File_{} ::: {}".format(record_count, file_count, f_name))
def load_cowfeed_table(data_path, _file, systemname, dbfile):
with DBSession(systemname,
dbfile) as session, codecs.open(
data_path + _file,
'r',
encoding='ascii',
errors='ignore') as f_handle:
next(f_handle) # Skip headers of csv file.
reader = csv.DictReader(
f_handle,
fieldnames=CowFeed.metadata.tables['cowfeed'].columns.keys())
for item in reader:
item['CALVIN_MONTH'] = int(item['CALVIN_MONTH'].strip())
item['FEED_COST'] = float(item['FEED_COST'].strip(' ').strip('$'))
obj = CowFeed(**item)
session.add(obj)
print(f"File_loaded...! {_file}")
def load_shopdemand_table(data_path, _file, systemname, dbfile):
with DBSession(systemname,
dbfile) as session, codecs.open(
data_path + _file,
'r',
encoding='ascii',
errors='ignore') as f_handle:
next(f_handle) # Skip headers of csv file.
reader = csv.DictReader(
f_handle,
fieldnames=ShopDemand.metadata.tables['shopdemand'].columns.keys())
row_count = count(1)
for item in reader:
obj = ShopDemand(**item)
obj.id_ = next(row_count)
session.add(obj)
print(f"File_loaded...! {_file}")
def load_milkdemand_table(data_path, _file, systemname, dbfile):
with DBSession(systemname,
dbfile) as session, codecs.open(
data_path + _file,
'r',
encoding='ascii',
errors='ignore') as f_handle:
next(f_handle) # Skip headers of csv file.
reader = csv.DictReader(
f_handle,
fieldnames=MilkDemand.metadata.tables['milkdemand'].columns.keys())
for item in reader:
item['MONTH'] = int(item['MONTH'])
item['DEMAND'] = float(item['DEMAND'])
item['PRICE'] = float(item['PRICE'].strip(' ').strip('$'))
session.add(MilkDemand(**item))
print(f"File_loaded...! {_file}")
def add_records(self, objects: list) -> None:
with DBSession(self.systemname, self.dbfile) as session:
session.add_all(objects)
def get_slots_list(self) -> list:
with DBSession(self.systemname, self.dbfile) as session:
slots = session.query(NetworkSlots.SLOT).distinct()
return [item[0] for item in slots]
def get_network_list(self) -> list:
with DBSession(self.systemname, self.dbfile) as session:
networks = session.query(NetworkSlots.NETWORK).distinct()
return [item[0] for item in networks]
def get_team_list(self) -> set:
with DBSession(self.systemname, self.dbfile) as session:
teams = session.query(TeamData.TEAM).distinct()
return {item[0] for item in teams}
def get_feed_cost(self) -> dict:
with DBSession(self.systemname, self.dbfile) as session:
""" Feed cost indexed by calving month ($/cow)"""
cost = session.query(CowFeed.CALVIN_MONTH, CowFeed.FEED_COST)
return {obj[0]: obj[1] for obj in cost}
def get_milk_demand(self) -> dict:
""" Milk demand indexed by demand month (gals)"""
with DBSession(self.systemname, self.dbfile) as session:
demand = session.query(MilkDemand.MONTH, MilkDemand.DEMAND)
return {obj[0]: obj[1] for obj in demand}
def get_milk_price(self) -> dict:
""" Milk market selling price indexed by demand month ($/gal)"""
with DBSession(self.systemname, self.dbfile) as session:
price = session.query(MilkDemand.MONTH, MilkDemand.PRICE)
return {obj[0]: obj[1] for obj in price}
def get_team_list(self) -> dict:
with DBSession(self.systemname, self.dbfile) as session:
teams = session.query(TeamData.TEAM, TeamData.CONF, TeamData.DIV,
TeamData.TIMEZONE)
return {item[0]: [item[1], item[2], item[3]] for item in teams}
def get_good_stores(self) -> list:
with DBSession(self.systemname, self.dbfile) as session:
return [obj[0] for obj in session.query(GoodStores.STORE_NUMBER)]
def get_demand(self) -> dict:
with DBSession(self.systemname, self.dbfile) as session:
demand = session.query(ShopDemand.STORE_NUMBER,
func.avg(ShopDemand.PIZZA_SALES)).group_by(
ShopDemand.STORE_NUMBER).all()
return {obj[0]: obj[1] * 7 for obj in demand}
def get_opponents(self):
with DBSession(self.systemname, self.dbfile) as session:
data = session.query(Opponents.AWAY_TEAM, Opponents.HOME_TEAM)
return [(item[0], item[1]) for item in data]
def get_cost(self) -> dict:
with DBSession(self.systemname, self.dbfile) as session:
cost = session.query(Centers.CENTER_ID, Centers.DIST_COST)
return {obj[0].replace(' ', '!'): obj[1] for obj in cost}