def generate_edges_from_routes_summary(timestamp_directory, the_scenario, logger): logger.info("start: generate_edges_from_routes_summary") report_file_name = 'optimal_routes_' + TIMESTAMP.strftime( "%Y_%m_%d_%H-%M-%S") + ".csv" report_file_name = clean_file_name(report_file_name) report_file = os.path.join(timestamp_directory, report_file_name) with sqlite3.connect(the_scenario.main_db) as main_db_con: db_cur = main_db_con.cursor() summary_route_data = main_db_con.execute( """select rr.route_id, f1.facility_name as from_facility, f2.facility_name as to_facility, rr.phase_of_matter, rr.dollar_cost, rr.cost, rr.miles FROM route_reference rr join facilities f1 on rr.from_facility_id = f1.facility_id join facilities f2 on rr.to_facility_id = f2.facility_id; """ ) # Print route data to file in debug folder with open(report_file, 'w', newline='') as f: writer = csv.writer(f) writer.writerow([ 'route_id', 'from_facility', 'to_facility', 'phase', 'dollar_cost', 'routing_cost', 'miles' ]) writer.writerows(summary_route_data)
def generate_detailed_emissions_summary(timestamp_directory, the_scenario, logger): logger.info("start: generate_detailed_emissions_summary") report_file_name = 'detailed_emissions_' + TIMESTAMP.strftime( "%Y_%m_%d_%H-%M-%S") + ".csv" report_file_name = clean_file_name(report_file_name) report_file = os.path.join(timestamp_directory, report_file_name) # query the emissions tables and report out the results with sqlite3.connect(the_scenario.main_db) as main_db_con: db_cur = main_db_con.cursor() emissions_data = main_db_con.execute( "select * from detailed_emissions;") emissions_data = emissions_data.fetchall() # print emissions data to new report file with open(report_file, 'w', newline='') as f: writer = csv.writer(f) writer.writerow( ['commodity', 'mode', 'pollutant', 'value', 'units']) writer.writerows(emissions_data) logger.debug("finish: generate_detailed_emissions_summary")
def generate_reports(the_scenario, logger): logger.info("start: parse log operation for reports") report_directory = os.path.join(the_scenario.scenario_run_directory, "Reports") if not os.path.exists(report_directory): os.makedirs(report_directory) filetype_list = ['s_', 'f_', 'f2', 'c_', 'c2', 'g_', 'g2', 'o', 'o1', 'o2', 'oc', 'oc1', 'oc2', 'oc3', 'os', 'p_'] # init the dictionary to hold them by type. for the moment ignoring other types. log_file_dict = {} for x in filetype_list: log_file_dict[x] = [] # get all of the log files matching the pattern log_files = glob.glob(os.path.join(the_scenario.scenario_run_directory, "logs", "*_log_*_*_*_*-*-*.log")) # add log file name and date to dictionary. each entry in the array # will be a tuple of (log_file_name, datetime object) for log_file in log_files: path_to, the_file_name = ntpath.split(log_file) the_type = the_file_name[:2] # one letter switch 's_log_', 'd_log_', etc. if the_file_name[5] == "_": the_date = datetime.datetime.strptime(the_file_name[5:25], "_%Y_%m_%d_%H-%M-%S") # two letter switch 'c2_log_', 'g2_log_', etc. elif the_file_name[6] == "_": the_date = datetime.datetime.strptime(the_file_name[6:26], "_%Y_%m_%d_%H-%M-%S") else: logger.warning("The filename: {} is not supported in the logging".format(the_file_name)) if the_type in log_file_dict: log_file_dict[the_type].append((the_file_name, the_date)) # sort each log type list by datetime so the most recent is first. for x in filetype_list: log_file_dict[x] = sorted(log_file_dict[x], key=lambda tup: tup[1], reverse=True) # create a list of log files to include in report by grabbing the latest version. # these will be in order by log type (i.e. s, f, c, g, etc) most_recent_log_file_set = [] if len(log_file_dict['s_']) > 0: most_recent_log_file_set.append(log_file_dict['s_'][0]) if len(log_file_dict['f_']) > 0: most_recent_log_file_set.append(log_file_dict['f_'][0]) if len(log_file_dict['c_']) > 0: most_recent_log_file_set.append(log_file_dict['c_'][0]) if len(log_file_dict['g_']) > 0: most_recent_log_file_set.append(log_file_dict['g_'][0]) if len(log_file_dict['oc']) == 0: if len(log_file_dict['o']) > 0: most_recent_log_file_set.append(log_file_dict['o'][0]) if len(log_file_dict['o1']) > 0: most_recent_log_file_set.append(log_file_dict['o1'][0]) if len(log_file_dict['o2']) > 0: most_recent_log_file_set.append(log_file_dict['o2'][0]) if len(log_file_dict['os']) > 0: most_recent_log_file_set.append(log_file_dict['os'][0]) if len(log_file_dict['oc']) > 0: most_recent_log_file_set.append(log_file_dict['oc'][0]) if len(log_file_dict['f2']) > 0: most_recent_log_file_set.append(log_file_dict['f2'][0]) if len(log_file_dict['c2']) > 0: most_recent_log_file_set.append(log_file_dict['c2'][0]) if len(log_file_dict['g2']) > 0: most_recent_log_file_set.append(log_file_dict['g2'][0]) if len(log_file_dict['o']) > 0: most_recent_log_file_set.append(log_file_dict['o'][0]) if len(log_file_dict['o1']) > 0: most_recent_log_file_set.append(log_file_dict['o1'][0]) if len(log_file_dict['o2']) > 0: most_recent_log_file_set.append(log_file_dict['o2'][0]) if len(log_file_dict['p_']) > 0: most_recent_log_file_set.append(log_file_dict['p_'][0]) # figure out the last index of most_recent_log_file_set to include # by looking at dates. if a subsequent step is seen to have an older # log than a preceding step, no subsequent logs will be used. # -------------------------------------------------------------------- last_index_to_include = 0 for i in range(1, len(most_recent_log_file_set)): # print most_recent_log_file_set[i] if i == 1: last_index_to_include += 1 elif i > 1: if most_recent_log_file_set[i][1] > most_recent_log_file_set[i - 1][1]: last_index_to_include += 1 else: break # print last_index_to_include # -------------------------------------------------------- message_dict = { 'RESULT': [], 'CONFIG': [], 'ERROR': [], 'WARNING': [], 'RUNTIME': [] } for i in range(0, last_index_to_include + 1): in_file = os.path.join(the_scenario.scenario_run_directory, "logs", most_recent_log_file_set[i][0]) # s, p, b, r record_src = most_recent_log_file_set[i][0][:2].upper() with open(in_file, 'r') as rf: for line in rf: recs = line.strip()[19:].split(' ', 1) if recs[0] in message_dict: if len(recs) > 1: # RE: Issue #182 - exceptions at the end of the log will cause this to fail. message_dict[recs[0]].append((record_src, recs[1].strip())) # dump to file # --------------- report_file_name = 'report_' + datetime.datetime.now().strftime("%Y_%m_%d_%H-%M-%S") + ".txt" report_file = os.path.join(report_directory, report_file_name) with open(report_file, 'w') as wf: wf.write('\nTOTAL RUNTIME\n') wf.write('---------------------------------------------------------------------\n') for x in message_dict['RUNTIME']: wf.write('{}\t:\t{}\n'.format(x[0], x[1])) wf.write('\nRESULTS\n') wf.write('---------------------------------------------------------------------\n') for x in message_dict['RESULT']: wf.write('{}\t:\t{}\n'.format(x[0], x[1])) wf.write('\nCONFIG\n') wf.write('---------------------------------------------------------------------\n') for x in message_dict['CONFIG']: wf.write('{}\t:\t{}\n'.format(x[0], x[1])) if len(message_dict['ERROR']) > 0: wf.write('\nERROR\n') wf.write('---------------------------------------------------------------------\n') for x in message_dict['ERROR']: wf.write('{}\t:\t\t{}\n'.format(x[0], x[1])) if len(message_dict['WARNING']) > 0: wf.write('\nWARNING\n') wf.write('---------------------------------------------------------------------\n') for x in message_dict['WARNING']: wf.write('{}\t:\t\t{}\n'.format(x[0], x[1])) logger.info("Done Parse Log Operation") logger.info("Report file location: {}".format(report_file)) # ------------------------------------------------------------- logger.info("start: Tableau results report") report_file_name = 'tableau_report_' + datetime.datetime.now().strftime("%Y_%m_%d_%H-%M-%S") + "_" + str( the_scenario.scenario_name).replace(" ", "_") + ".csv" report_file_name = clean_file_name(report_file_name) report_file = os.path.join(report_directory, report_file_name) with open(report_file, 'w') as wf: wf.write('scenario_name, table_name, commodity, facility_name, measure, mode, value, units, notes\n') import sqlite3 with sqlite3.connect(the_scenario.main_db) as db_con: # query the optimal scenario results table and report out the results # ------------------------------------------------------------------------- sql = "select * from optimal_scenario_results order by table_name, commodity, measure, mode;" db_cur = db_con.execute(sql) data = db_cur.fetchall() for row in data: wf.write("{}, {}, {}, {}, {}, {}, {}, {}, {}\n".format(the_scenario.scenario_name, row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7])) # Scenario Total Supply and Demand, and Available Processing Capacity # note: processor input and outputs are based on facility size and reflect a processing capacity, # not a conversion of the scenario feedstock supply # ------------------------------------------------------------------------- sql = """ select c.commodity_name, fti.facility_type, io, sum(fc.quantity), fc.units from facility_commodities fc join commodities c on fc.commodity_id = c.commodity_id join facilities f on f.facility_id = fc.facility_id join facility_type_id fti on fti.facility_type_id = f.facility_type_id group by c.commodity_name, fc.io, fti.facility_type, fc.units order by commodity_name, io desc;""" db_cur = db_con.execute(sql) db_data = db_cur.fetchall() for row in db_data: facility_type = row[1] io = row[2] if facility_type == "raw_material_producer": measure = "supply" if facility_type == "processor" and io == 'o': measure = "processing output capacity" if facility_type == "processor" and io == 'i': measure = "processing input capacity" if facility_type == "ultimate_destination": measure = "demand" wf.write("{}, {}, {}, {}, {}, {}, {}, {}, {}\n".format(the_scenario.scenario_name, "total_supply_demand_proc", row[0], "all_{}".format(row[1]), measure, io, row[3], row[4], None)) # Scenario Stranded Supply, Demand, and Processing Capacity # note: stranded supply refers to facilities that are ignored from the analysis.") # ------------------------------------------------------------------------- sql = """ select c.commodity_name, fti.facility_type, io, sum(fc.quantity), fc.units, f.ignore_facility from facility_commodities fc join commodities c on fc.commodity_id = c.commodity_id join facilities f on f.facility_id = fc.facility_id join facility_type_id fti on fti.facility_type_id = f.facility_type_id where f.ignore_facility != 'false' group by c.commodity_name, fc.io, fti.facility_type, fc.units, f.ignore_facility order by commodity_name, io asc;""" db_cur = db_con.execute(sql) db_data = db_cur.fetchall() for row in db_data: facility_type = row[1] io = row[2] if facility_type == "raw_material_producer": measure = "stranded supply" if facility_type == "processor" and io == 'o': measure = "stranded processing output capacity" if facility_type == "processor" and io == 'i': measure = "stranded processing input capacity" if facility_type == "ultimate_destination": measure = "stranded demand" wf.write("{}, {}, {}, {}, {}, {}, {}, {}, {}\n".format(the_scenario.scenario_name, "stranded_supply_demand_proc", row[0], "stranded_{}".format(row[1]), measure, io, row[3], row[4], row[5])) # ignore_facility note # report out net quantities with ignored facilities removed from the query # note: net supply, demand, and processing capacity ignores facilities not connected to the network # ------------------------------------------------------------------------- sql = """ select c.commodity_name, fti.facility_type, io, sum(fc.quantity), fc.units from facility_commodities fc join commodities c on fc.commodity_id = c.commodity_id join facilities f on f.facility_id = fc.facility_id join facility_type_id fti on fti.facility_type_id = f.facility_type_id where f.ignore_facility == 'false' group by c.commodity_name, fc.io, fti.facility_type, fc.units order by commodity_name, io desc;""" db_cur = db_con.execute(sql) db_data = db_cur.fetchall() for row in db_data: facility_type = row[1] io = row[2] if facility_type == "raw_material_producer": measure = "net supply" if facility_type == "processor" and io == 'o': measure = "net processing output capacity" if facility_type == "processor" and io == 'i': measure = "net processing input capacity" if facility_type == "ultimate_destination": measure = "net demand" wf.write("{}, {}, {}, {}, {}, {}, {}, {}, {}\n".format(the_scenario.scenario_name, "net_supply_demand_proc", row[0], "net_{}".format(row[1]), measure, io, row[3], row[4], None)) # REPORT OUT CONFIG FOR O2 STEP AND RUNTIMES FOR ALL STEPS # Loop through the list of configurations records in the message_dict['config'] and ['runtime']. # Note that this list is of the format: ['step', "xml_record, value"], and additional parsing is required. step_to_export = 'O2' # the step we want to export logger.info("output the configuration for step: {}".format(step_to_export)) for config_record in message_dict['CONFIG']: step = config_record[0] if step != step_to_export: continue else: # parse the xml record and value from the xml_record_and_value = config_record[1] xml_record = xml_record_and_value.split(":", 1)[0].replace("xml_", "") value = xml_record_and_value.split(":", 1)[1].strip() # only split on the first colon to prevent paths from being split wf.write("{}, {}, {}, {}, {}, {}, {}, {}, {}\n".format(the_scenario.scenario_name, "config", '', '', xml_record, '', '', '', value)) for x in message_dict['RUNTIME']: # message_dict['RUNTIME'][0] # ('S_', 's Step - Total Runtime (HMS): \t00:00:21') step, runtime = x[1].split('\t') wf.write("{}, {}, {}, {}, {}, {}, {}, {}, {}\n".format(the_scenario.scenario_name, "runtime", '', '', step, '', '', '', runtime)) logger.debug("finish: Tableau results report operation") latest_report_dir = prepare_tableau_assets(report_file, the_scenario, logger) logger.result("Tableau Report file location: {}".format(report_file))
def generate_artificial_link_summary(timestamp_directory, the_scenario, logger): logger.info("start: generate_artificial_link_summary") report_file_name = 'artificial_links_' + TIMESTAMP.strftime( "%Y_%m_%d_%H-%M-%S") + ".csv" report_file_name = clean_file_name(report_file_name) report_file = os.path.join(timestamp_directory, report_file_name) # query the facility and network tables for artificial links and report out the results with sqlite3.connect(the_scenario.main_db) as db_con: sql = """select fac.facility_name, fti.facility_type, ne.mode_source, round(ne.miles, 3) as miles from facilities fac left join facility_type_id fti on fac.facility_type_id = fti.facility_type_id left join networkx_nodes nn on fac.location_id = nn.location_id left join networkx_edges ne on nn.node_id = ne.from_node_id where nn.location_1 like '%OUT%' and ne.artificial = 1 ;""" db_cur = db_con.execute(sql) db_data = db_cur.fetchall() artificial_links = {} for row in db_data: facility_name = row[0] facility_type = row[1] mode_source = row[2] link_length = row[3] if facility_name not in artificial_links: # add new facility to dictionary and start list of artificial links by mode artificial_links[facility_name] = { 'fac_type': facility_type, 'link_lengths': {} } if mode_source not in artificial_links[facility_name][ 'link_lengths']: artificial_links[facility_name]['link_lengths'][ mode_source] = link_length else: # there should only be one artificial link for a facility for each mode error = "Multiple artificial links should not be found for a single facility for a particular mode." logger.error(error) raise Exception(error) # create structure for artificial link csv table output_table = {'facility_name': [], 'facility_type': []} for permitted_mode in the_scenario.permittedModes: output_table[permitted_mode] = [] # iterate through every facility, add a row to csv table for k in artificial_links: output_table['facility_name'].append(k) output_table['facility_type'].append(artificial_links[k]['fac_type']) for permitted_mode in the_scenario.permittedModes: if permitted_mode in artificial_links[k]['link_lengths']: output_table[permitted_mode].append( artificial_links[k]['link_lengths'][permitted_mode]) else: output_table[permitted_mode].append('NA') # print artificial link data for each facility to file in debug folder with open(report_file, 'w', newline='') as f: writer = csv.writer(f) output_fields = ['facility_name', 'facility_type' ] + the_scenario.permittedModes writer.writerow(output_fields) writer.writerows(zip(*[output_table[key] for key in output_fields])) logger.debug("finish: generate_artificial_link_summary")