# Import libraries
import numpy as np
import pandas as pd

from pulp import *

import plotly.graph_objects as go

# Settings
fn = 'Sample.xlsm'

# Project name
project = 'Sample'

# Force round off (2 means 2 decimal places, 
# -2 means round off to hundreds, set to None if no need to round off)
round_dec = 0

# Scenario name
scenario_name = 'Output'

# Historical flow? (Keeps flow from HistFlow columns and calculates costs)
histFlow = True
#histFlow = False

# Always apply fixed costs regardless of whether site is open?
#alwaysFixed = True
alwaysFixed = False

# Dictionary of sheet names (key is based on spreadsheet sheet names, 
# value is data role) <- breaking change from previous version
sheets = {'Prod': 'Prod',
          'Supply': 'Site',
          'psS': 'PSite',
          'DC': 'Site',
          'psDC': 'PSite',
          'Market': 'Site',
          'psM': 'PSite',
          'lSDC': 'Lane',
          'plSDC': 'PLane',
          'lDCM': 'Lane',
          'plDCM': 'PLane'}

# Site and product site connections (key is site, value is product site)
# and lane and product lane connections (key is lane, value is product lane)
prod_conn = {'Supply': 'psS',
             'DC': 'psDC',
             'Market': 'psM',
             'lSDC': 'plSDC',
             'lDCM': 'plDCM'}

# Columns of fixed costs in site sheet
# FixedCost renamed to Fixed Cost when computing totals
fix_cost_cols = ['FixedCost']

# NOT YET IMPLEMENTED
# Columns of product fixed costs in product site sheet
# FixedCost renamed to Fixed Cost when computing totals
fixp_cost_cols = []

# Columns of variable costs in site sheet
# CPU renamed to Total Cost when computing totals
var_cost_cols = []

# Columns of product variable costs in product site sheet
# CPU renamed to Total Cost when computing totals
varp_cost_cols = ['ReceivingCPU',
                  'StorageCPU',
                  'VASCPU',
                  'XDockCPU']

# NOT YET IMPLEMENTED
# Columns of fixed costs in lane sheet
# FixedCost renamed to Fixed Cost when computing totals
tfix_cost_cols = []

# NOT YET IMPLEMENTED
# Columns of product fixed costs in product lane sheet
# FixedCost renamed to Fixed Cost when computing totals
tfixp_cost_cols = []

# Columns of variable costs in lane sheet
# TransPrice renamed to Transport Cost when computing totals
tvar_cost_cols = []

# Columns of product variable costs in product lane sheet
# TransPrice renamed to Transport Cost when computing totals
tvarp_cost_cols = ['FreightCPU',
                   'DrayageCPU',
                   'TransportCPU']

data = {'Prod': pd.DataFrame(),
        'Site': pd.DataFrame(),
        'PSite': pd.DataFrame(),
        'Lane': pd.DataFrame(),
        'PLane': pd.DataFrame()}

# Rename cost variables when getting cost totals
def costRename(string):
    # Rename CPU to Cost
    string = string.replace('CPU', ' Cost')
    # Rename TransPrice to Transport Cost
    string = string.replace('TransPrice', 'Transport Cost')
    # Rename FixedCost to Fixed Cost
    string = string.replace('FixedCost', 'Fixed Cost')
    return string

# Map Settings

# Insert mapbox token
mapbox_access_token = ''

# Map dimensions
map_height = 500
map_width = 800

# Function to get zoom level
def getBoundsZoomLevel(bounds, mapDim):
    """source: https://stackoverflow.com/questions/6048975/google-maps-v3-how-to-calculate-the-zoom-level-for-a-given-bounds
    bounds: list of ne and sw lat/lon
    mapDim: dictionary with image size in pixels
    returns: zoom level to fit bounds in the visible area
    """
    ne_lat = bounds[0]
    ne_long = bounds[1]
    sw_lat = bounds[2]
    sw_long = bounds[3]

    scale = 2 # adjustment to reflect MapBox base tiles are 512x512 vs. Google's 256x256
    WORLD_DIM = {'height': 256 * scale, 'width': 256 * scale}
    ZOOM_MAX = 18

    def latRad(lat):
        sin = np.sin(lat * np.pi / 180)
        radX2 = np.log((1 + sin) / (1 - sin)) / 2
        return max(min(radX2, np.pi), -np.pi) / 2

    def zoom(mapPx, worldPx, fraction):
        return np.floor(np.log(mapPx / worldPx / fraction) / np.log(2))

    latFraction = (latRad(ne_lat) - latRad(sw_lat)) / np.pi

    lngDiff = ne_long - sw_long
    lngFraction = ((lngDiff + 360) if lngDiff < 0 else lngDiff) / 360

    latZoom = zoom(mapDim['height'], WORLD_DIM['height'], latFraction)
    lngZoom = zoom(mapDim['width'], WORLD_DIM['width'], lngFraction)

    return min(latZoom, lngZoom, ZOOM_MAX)

# Load data
xls = pd.ExcelFile(fn)

for sheet in sheets.keys():
    temp = pd.read_excel(xls, 
                         sheet,
                         skiprows=2)
    if sheets[sheet] == 'Lane':
        temp['ID'] = temp['ID'].apply(lambda x: prod_conn[sheet]+'_'+x)
    if sheets[sheet] == 'PLane':
        temp['LaneID'] = temp['LaneID'].apply(lambda x: sheet+'_'+x)
    data[sheets[sheet]] = data[sheets[sheet]].append(temp)
                                                     
for dataType in data.keys():
    data[dataType] = data[dataType].loc[:, ~data[dataType].columns.str.contains('^unnamed', 
                                                                                case=False)]

xls.close()

# Round off if necessary
if not round_dec is None:
    try:
        data['PSite']['Supply'] = data['PSite']['Supply'].round(round_dec)
    except:
        print('No column Supply in PSite')
    try:
        data['PSite']['Demand'] = data['PSite']['Demand'].round(round_dec)
    except:
        print('No column Demand in PSite')
    try:
        data['PLane']['HistFlow'] = data['PLane']['HistFlow'].round(round_dec)
    except:
        print('No column HistFlow in PLane')

# Get value of M (very large number)
M = data['PSite']['Supply'].sum()

# The list of products
products = list(data['Prod']['ID'])
#products

sites = data['Site']['ID']

# Get dataframe of suppliers
suppliers = data['PSite']#.loc[data['PSite']['Supply']>0]

# The dictionary of supply per product per site
supply = {s:{p:suppliers.loc[(suppliers['SiteID']==s)&(suppliers['ProductID']==p), 'Supply'].values[0] for p in suppliers.loc[(suppliers['SiteID']==s)&(suppliers['Supply']>0), 'ProductID'].values} for s in pd.unique(suppliers['SiteID'])}

# Get dataframe of markets
markets = data['PSite']#.loc[data['PSite']['Demand']>0]

# The dictionary of demand per product per market
demand = {m:{p:markets.loc[(markets['SiteID']==m)&(markets['ProductID']==p), 'Demand'].values[0] for p in markets.loc[(markets['SiteID']==m)&(markets['Demand']>0), 'ProductID'].values} for m in pd.unique(markets['SiteID'])}

# Join product-independent costs from DC Site sheet to DC Product Site sheet
data['PSite'] = data['PSite'].join(data['Site'].set_index('ID')[fix_cost_cols + 
                                                                var_cost_cols],
                                   on='SiteID')

# Get dataframe of DCs (defined as sites with costs greater than zero)
DC = data['PSite'].loc[data['PSite'][fix_cost_cols +
                                     fixp_cost_cols +
                                     var_cost_cols + 
                                     varp_cost_cols].sum(axis=1)>0]

# The dictionary of dictionaries of variable costs per DC
DCVar = {}
for cost in var_cost_cols + varp_cost_cols:
    DCVar[cost] = makeDict([pd.unique(DC['SiteID']),
                            pd.unique(DC['ProductID'])],
                           [list(DC.loc[DC['SiteID'] == dc, cost]) for dc in pd.unique(DC['SiteID'])],
                           0)

DCVar

# The dictionary of dictionaries of fixed costs per DC
DCFix = {}
for cost in fix_cost_cols:
    DCFix[cost] = dict(zip(DC['SiteID'], DC[cost]))

DCFix

# The dictionary of dictionaries of fixed product costs per DC
DCFixp = {}
for cost in fixp_cost_cols:
    DCFixp[cost] = dict(zip(DC['SiteID'], DC[cost]))

DCFixp

if not alwaysFixed:
    # The binary variables for DC fixed costs
    DC_bv = LpVariable.dicts('DCbin', DC['SiteID'], 0, cat='Binary')
    #DC_bv
    
    # The binary variables for DC product fixed costs
    # TBD

DC_bv

# Join in origin and destination data to product lane dataframe
if 'FromID' in data['PLane'].columns:
    pass
else:
    data['PLane'] = data['PLane'].join(data['Lane'].set_index('ID')[['FromID',
                                                                     'ToID']],
                                       on='LaneID')

# Fill NaNs in costs with zeroes
data['PLane'][tvarp_cost_cols] = data['PLane'][tvarp_cost_cols].fillna(0)

data['PLane']

# Remove later
data['PLane'].loc[data['PLane']['HistFlow']>0]#.to_clipboard(index=False)

# Remove later
pd.DataFrame({'OUT': data['PLane'].groupby(['FromID', 'ProductID'])['HistFlow'].sum(),
              'IN': data['PLane'].groupby(['ToID', 'ProductID'])['HistFlow'].sum()}).fillna(0).to_clipboard()

# Remove later
data['PLane'].loc[(data['PLane']['FromID']=='Kunshan')&(data['PLane']['HistFlow']>0)]#.to_clipboard(index=False)

# Remove later
data['PLane'].loc[(data['PLane']['ToID']=='Kunshan')&(data['PLane']['HistFlow']>0)]#.to_clipboard(index=False)

# Get dataframe of transport lanes (defined as lanes with transport costs greater than zero)
Lanes = data['PLane'].loc[data['PLane'][tfix_cost_cols +
                                        tfixp_cost_cols +
                                        tvar_cost_cols + 
                                        tvarp_cost_cols].sum(axis=1)>0]

Lanes

# The dictionary of dictionaries of costs for the transport routes per product from origin to destination
OtoD = {}
for cost in tvarp_cost_cols:
    if cost in OtoD:
        pass
    else:
        OtoD[cost] = {}
    for o in np.unique(Lanes['FromID']):
        if o in OtoD[cost]:
            pass
        else:
            OtoD[cost][o] = {}
        for p in np.unique(Lanes.loc[Lanes['FromID']==o, 
                                     'ProductID']):
            if p in OtoD[cost][o]:
                pass
            else:
                OtoD[cost][o][p] = {}
            for d in np.unique(Lanes.loc[(Lanes['FromID']==o) & 
                                         (Lanes['ProductID']==p), 
                                         'ToID']):
                OtoD[cost][o][p][d] = Lanes.loc[(Lanes['FromID']==o) & 
                                                (Lanes['ProductID']==p) & 
                                                (Lanes['ToID']==d), 
                                                cost].values[0]

OtoD

# The dictionary containing the referenced variables (the routes) per product for origin to destination
OtoD_v = {}
for o in np.unique(Lanes['FromID']):
    if o in OtoD_v:
        pass
    else:
        OtoD_v[o] = {}
    for p in np.unique(Lanes.loc[Lanes['FromID']==o, 
                                 'ProductID']):        
        if p in OtoD_v[o]:
            pass
        else:
            OtoD_v[o][p] = {}
        for d in np.unique(Lanes.loc[(Lanes['FromID']==o) & 
                                     (Lanes['ProductID']==p), 
                                     'ToID']):
            OtoD_v[o][p][d] = LpVariable('OtoD_'+str(o)+'_'+str(p)+'_'+str(d),
                                         0,
                                         None,
                                         LpInteger)

OtoD_v

# Creates a list of tuples containing all the possible routes per product from origin to destination
OtoD_l = list(Lanes[['FromID', 
                     'ProductID', 
                     'ToID']].itertuples(index=False,
                                                 name=None))
#OtoD_l

OtoD_l

# The variable containing the problem data
prob = LpProblem(project, LpMinimize)

# The objective function is added to 'prob' first

if alwaysFixed:
    prob += (
        lpSum(
            [OtoD_v[o][p][d] * OtoD[cost][o][p][d] for (o, p, d) in OtoD_l for cost in OtoD] + # Origin to destination transport cost
            [OtoD_v[o][p][d] * DCVar[cost][d][p] for (o, p, d) in OtoD_l for cost in DCVar] + # Site variable cost (based on inbound)
            #[OtoD_v[o][p][d] * DCVar[cost][o][p] for (o, p, d) in OtoD_l for cost in DCVar] + # Site variable cost (based on outbound)
            #[DC_bv[d] * DCFix[cost][d] for d in DC['SiteID'] for cost in DCFix] # Site fixed cost applied if there is flow
            [DCFix[cost][d] for d in np.unique(DC['SiteID']) for cost in DCFix] # DC fixed cost is always applied
             ),
        'Sum_of_Logistics_Costs',
    )
else:
    prob += (
        lpSum(
            [OtoD_v[o][p][d] * OtoD[cost][o][p][d] for (o, p, d) in OtoD_l for cost in OtoD] + # Origin to destination transport cost
            [OtoD_v[o][p][d] * DCVar[cost][d][p] for (o, p, d) in OtoD_l for cost in DCVar] + # Site variable cost (based on inbound)
            #[OtoD_v[o][p][d] * DCVar[cost][o][p] for (o, p, d) in OtoD_l for cost in DCVar] + # Site variable cost (based on outbound)
            [DC_bv[d] * DCFix[cost][d] for d in np.unique(DC['SiteID']) for cost in DCFix] # Site fixed cost applied if there is flow
            #[DCFix[cost][d] for d in DC['SiteID'] for cost in DCFix] # DC fixed cost is always applied
             ),
        'Sum_of_Logistics_Costs',
    )

# Print objective
prob.objective

if not alwaysFixed:
    # Get list of DCs with fixed costs
    temp_list = DC.loc[DC[fix_cost_cols].sum(axis=1)>0, 'SiteID']
    # DC fixed costs are applied if a DC is open (based on inbound)
    for d in np.unique(temp_list):
        prob += (
            lpSum([OtoD_v[s][p][d] for s in np.unique(Lanes.loc[Lanes['ToID']==d, 'FromID']) for p in np.unique(Lanes.loc[(Lanes['FromID']==s)&(Lanes['ToID']==d), 'ProductID'])]) <= DC_bv[d]*M,
            "%s_Hub_is_Open" % d,
        )

# Print DC integer constraints
for c in prob.constraints:
    if '_Hub_is_Open' in c:
        print(c, ':', prob.constraints[c], '\n')

# The supply maximum constraints are added to prob for each supply node (supplier)
for s in supply:
    for p in supply[s]:
        if not np.isnan(supply[s][p]):
            prob += (
                lpSum([OtoD_v[s][p][d] for d in OtoD_v[s][p].keys()]) <= supply[s][p],
                "Sum_of_Products_{}_out_of_Supplier_{}".format(p, s),
            )

# Print supply constraints
for c in prob.constraints:
    if '_out_of_Supplier_' in c:
        print(c, ':', prob.constraints[c], '\n')

# The DC can only ship out what was shipped in

for s in sites:
    for p in products:
        # Skip supply and demand sites:
        if (demand.get(s, {p:0}).get(p, 0)>0) or (supply.get(s, {p:0}).get(p, 0)>0):
            pass
        else:
            prob +=(
                lpSum([OtoD_v[s][p][d] for d in np.unique(Lanes.loc[(Lanes['FromID']==s)&(Lanes['ProductID']==p), 'ToID'])]) + # Total outbound to site
                #demand.get(s, {p:0}).get(p, 0) == # Total demand at site
                demand.get(s, {p:0}).get(p, 0) <= # Total demand at site # Test Demand <= Supply
                lpSum([OtoD_v[o][p][s] for o in np.unique(Lanes.loc[(Lanes['ToID']==s)&(Lanes['ProductID']==p), 'FromID'])]) + # Total inbound to site
                supply.get(s, {p:0}).get(p, 0), # Total supply at site
                "Sum_of_Products_{}_Through_Site_{}".format(p, s),
            )

# Print DC flow constraints
for c in prob.constraints:
    if '_Through_Site_' in c:
        print(c, ':', prob.constraints[c], '\n')

# The demand minimum constraints are added to prob for each demand node (market)

for m in demand:
    for p in demand[m]:
        if not np.isnan(demand[m][p]):
            prob += (
                lpSum([OtoD_v[d][p][m] for d in np.unique(Lanes.loc[(Lanes['ToID']==m)&(Lanes['ProductID']==p), 'FromID'])]) >= demand[m][p],
                "Sum_of_Products_{}_into_Market_{}".format(p, m),
            )

# Print demand constraints
for c in prob.constraints:
    if '_into_Market_' in c:
        print(c, ':', prob.constraints[c], '\n')

if histFlow:
    # Fix historical supplier to hub flow
    for s in OtoD_v:
        for p in OtoD_v[s]:
            for d in OtoD_v[s][p]:
                prob += (
                    OtoD_v[s][p][d] == data['PLane'].loc[(data['PLane']['ProductID']==p)&
                                                         (data['PLane']['FromID']==s)&
                                                         (data['PLane']['ToID']==d), 
                                                         'HistFlow'].values[0],
                    "Historical_Flow_of_{}_from_{}_to_{}".format(p, s, d)
                )

# Print optional baseline historical flow constraint
for c in prob.constraints:
    if 'Historical_Flow_of_' in c:
        print(c, ':', prob.constraints[c])

# Print LP
prob

# The problem data is written to an .lp file
prob.writeLP("TransportationProblem.lp")

# The problem is solved using PuLP's choice of Solver
prob.solve()
#prob.solve('PULP_CHOCO_CMD')

# The status of the solution is printed to the screen
print("Status:", LpStatus[prob.status])

# Each of the variables is printed with its resolved optimum value
for v in prob.variables():
    print(v.name, "=", v.varValue)

# The optimised objective function value is printed to the screen
print("Total Logistics Cost = ", value(prob.objective))

# Print violated constraints, if any
for c in prob.constraints.values():
    if not c.valid(0):
        print(c.name)
        print(c)
        print(c.value())

# Create a Pandas Excel writer using XlsxWriter as the engine.
#writer = pd.ExcelWriter(fn.replace('.xlsx', ' - '+str(scenario_name)+'.xlsx'),
#                        engine='xlsxwriter')
writer = pd.ExcelWriter(fn.replace('.xlsm', ' - '+str(scenario_name)+'.xlsx'),
                        engine='xlsxwriter')

# Cost components come from the objective function with the variables evaluated using the value() function
# Must use value() function twice since lpSum result is not recognized by Pandas

totals = {}

# Transport cost
for cost in OtoD:
    totals['Transport '+costRename(cost)] = lpSum([OtoD_v[o][p][d] * OtoD[cost][o][p][d] for (o, p, d) in OtoD_l]).value()

# DC variable costs (based on inbound)
for cost in DCVar:
    totals['Site Variable '+costRename(cost)] = lpSum([OtoD_v[o][p][d] * DCVar[cost][d][p] for (o, p, d) in OtoD_l]).value()

# DC fixed cost
if alwaysFixed:
    for cost in DCFix:
        totals['Site '+costRename(cost)] = lpSum([DCFix[cost][d] for d in np.unique(DC['SiteID'])]).value()
else:
    for cost in DCFix:
        totals['Site '+costRename(cost)] = lpSum([DC_bv[d] * DCFix[cost][d] for d in DC['SiteID']]).value()

totals = pd.DataFrame.from_dict(totals,
                                orient='index',
                                columns=['Cost'])

totals = totals.reset_index() 

totals = totals.rename(columns={'index': 'Cost Component'})

# Add totals
temp = totals.sum()
temp['Cost Component'] = 'Total'
temp = totals.append(temp, ignore_index=True)

# Save to Excel
temp.to_excel(writer, 
              sheet_name='Totals',
              index=False)

temp

# Total flow per site
dc_flow = {(s, p, d): OtoD_v[s][p][d].value() for (s, p, d) in OtoD_l}

dc_flow = pd.DataFrame.from_dict(dc_flow,
                                 orient='index',
                                 columns=['Flow'])

# Reset index and parse supplier, product, and DC from index
dc_flow = dc_flow.reset_index()
dc_flow = pd.DataFrame(dc_flow['index'].tolist(),
                       columns=['Supplier',
                                'Product',
                                'Site']).join(dc_flow.drop(['index'],
                                                         axis=1))

dc_flow = dc_flow.pivot_table(index=['Site',
                                     'Product'],
                              values=['Flow'],
                              aggfunc=sum)

dc_flow = dc_flow.reset_index()

# Add totals
temp = dc_flow.sum()
temp['Site'] = 'Total'
temp['Product'] = 'Total'
temp = dc_flow.append(temp,
                      ignore_index=True)

# Save to Excel
temp.to_excel(writer, 
              sheet_name='Site Flow',
              index=False)

temp

# Total flow per site
sdc_flow = {(s, p, d): OtoD_v[s][p][d].value() for (s, p, d) in OtoD_l}

sdc_flow = pd.DataFrame.from_dict(sdc_flow,
                                  orient='index',
                                  columns=['Flow'])

# Reset index and parse supplier, product, and DC from index
sdc_flow = sdc_flow.reset_index()
sdc_flow = pd.DataFrame(sdc_flow['index'].tolist(),
                        columns=['Origin',
                                 'Product',
                                 'Destination']).join(sdc_flow.drop(['index'],
                                                           axis=1))

# Add totals
temp = sdc_flow.sum()
temp['Origin'] = 'Total'
temp['Product'] = 'Total'
temp['Destination'] = 'Total'
temp = sdc_flow.append(temp,
                       ignore_index=True)

# Save to Excel
temp.to_excel(writer, 
              sheet_name='Origin to Destination Flow',
              index=False)

temp

# Table of DC variable costs by product and DC
dc_varcost = dc_flow.pivot_table(index=['Site', 
                                        'Product'],
                                 values=['Flow'],
                                 aggfunc=sum)

dc_varcost = dc_varcost.reset_index()

dc_varcost = dc_varcost.merge(data['PSite'],
                              left_on=['Site',
                                       'Product'],
                              right_on=['SiteID',
                                        'ProductID'])

for cost in DCVar:
    dc_varcost[costRename(cost)] = dc_varcost[cost] * dc_varcost['Flow']

# Drop unnecessary columns
colnames = ['Site', 'Product', 'Flow'] + \
           var_cost_cols + \
           varp_cost_cols + \
           [costRename(cost) for cost in DCVar]
dc_varcost = dc_varcost[colnames]

# Drop rows with zero flow
dc_varcost = dc_varcost.loc[dc_varcost['Flow']>0]

# Add totals
temp = dc_varcost.sum()
temp['Site'] = 'Total'
temp['Product'] = 'Total'
temp = dc_varcost.append(temp,
                       ignore_index=True)

# Fill NAs with zeros
temp = temp.fillna(0)

# Save to Excel
temp.to_excel(writer, 
              sheet_name='Variable Site Costs',
              index=False)

temp

# Table of DC fixed costs by DC
dc_fixcost = dc_flow.pivot_table(index=['Site'],
                                 values=['Flow'],
                                 aggfunc=sum)

dc_fixcost = dc_fixcost.reset_index()

dc_fixcost = dc_fixcost.join(data['Site'].set_index('ID'),
                             on='Site')

for cost in DCFix:
    dc_fixcost[costRename(cost)] = 0
    dc_fixcost.loc[dc_fixcost['Flow']>0, costRename(cost)] = dc_fixcost['FixedCost']

# Drop unnecessary columns
colnames = ['Site', 'Flow', 'X', 'Y'] + \
           fix_cost_cols + \
           [costRename(cost) for cost in fix_cost_cols]
dc_fixcost = dc_fixcost[colnames]

if not alwaysFixed:
    # Drop rows with zero flow
    dc_fixcost = dc_fixcost.loc[dc_fixcost['Flow']>0]

# Add totals
temp = dc_fixcost.sum()
temp['Site'] = 'Total'
temp = dc_fixcost.append(temp,
                         ignore_index=True)

temp = temp.fillna(0)

# Save to Excel
temp.drop(['X', 'Y'],
          axis=1).to_excel(writer, 
                           sheet_name='Site Fixed Costs',
                           index=False)

temp.drop(['X', 'Y'],
          axis=1)

# Total DC costs (fixed and variable) by DC
dc_totalcost = dc_varcost.pivot_table(index=['Site'],
                                      values=[costRename(cost) for cost in DCVar],
                                      aggfunc=sum)
dc_totalcost = dc_totalcost.join(dc_fixcost.set_index('Site'),
                                 how='outer')

dc_totalcost['Var Cost'] = dc_totalcost[[costRename(cost) for cost in DCVar]].sum(axis=1)

dc_totalcost['Site Cost'] = dc_totalcost['Var Cost'] + dc_totalcost['Fixed Cost']

dc_totalcost = dc_totalcost.reset_index()

# Drop unnecessary columns
colnames = ['Site', 'Flow', 'X', 'Y'] + \
           [costRename(cost) for cost in fix_cost_cols] + \
           [costRename(cost) for cost in var_cost_cols] + \
           [costRename(cost) for cost in varp_cost_cols] + \
           ['Var Cost', 'Site Cost']
dc_totalcost = dc_totalcost[colnames]

# Add totals
temp = dc_totalcost.sum()
temp['Site'] = 'Total'
temp = dc_totalcost.append(temp,
                           ignore_index=True)

temp = temp.fillna(0)

# Save to Excel
temp.drop(['X', 'Y'],
          axis=1).to_excel(writer, 
                           sheet_name='Site Total Costs',
                           index=False)

temp.drop(['X', 'Y'],
          axis=1)

# Total supplier to DC transport costs

sdc_cost = sdc_flow.merge(data['PLane'],
                          left_on=['Origin',
                                   'Product',
                                   'Destination'],
                          right_on=['FromID',
                                    'ProductID',
                                    'ToID'])

# Calculate total transport costs based on flow
for cost in tvarp_cost_cols:
    sdc_cost[costRename(cost)] = sdc_cost['Flow'] * sdc_cost[cost]
    
# Drop unnecessary columns
colnames = ['FromID', 'ToID', 'Product', 'Flow'] + \
           [cost for cost in tvarp_cost_cols] + \
           [costRename(cost) for cost in tvarp_cost_cols]
sdc_cost = sdc_cost[colnames]

# Drop rows with zero flow
sdc_cost = sdc_cost.loc[sdc_cost['Flow']>0]

# Join coordinates (and location name?)
sdc_cost = sdc_cost.join(data['Site'].set_index('ID')[['X',
                                                       'Y']],
                         on='FromID')
sdc_cost = sdc_cost.join(data['Site'].set_index('ID')[['X',
                                                       'Y']],
                         on='ToID',
                         lsuffix='_From',
                         rsuffix='_To')

# Add totals
temp = sdc_cost.sum()
temp['FromID'] = 'Total'
temp['ToID'] = 'Total'
temp['Product'] = 'Total'
temp = sdc_cost.append(temp,
                       ignore_index=True)

# Save to Excel
temp.drop(['X_From', 
           'Y_From',
           'X_To',
           'Y_To'],
          axis=1).to_excel(writer, 
                           sheet_name='Transport Costs',
                           index=False)

temp.drop(['X_From', 
           'Y_From',
           'X_To',
           'Y_To'],
          axis=1)

sdc_cost

# Create a map of routes

# Initialize figure sites
fig = go.Figure()

# Add routes from origin to destination
for index, row in sdc_cost.iterrows():
    fig.add_trace(go.Scattermapbox(
        name = str(row['FromID'])+' to '+str(row['ToID']),
        mode = 'lines',
        #hovertemplate = temp_data['Sequence'].apply(str) + ') ' + temp_data['Ship_To_Location'],
        lon = [row['X_From'], row['X_To']],
        lat = [row['Y_From'], row['Y_To']],
        #marker = {'size': 10},
        line = {'width': 10*row['Flow']/sdc_cost['Flow'].max(),
                'color': 'red'}))
# Add sites
temp = sdc_cost.pivot_table(index=['FromID',
                                   'Y_From',
                                   'X_From'],
                            values=['Flow'],
                            aggfunc=sum)
temp = temp.reset_index()
for index, row in temp.iterrows():
    fig.add_trace(go.Scattermapbox(
        name = str(row['FromID']),
        mode = 'markers',
        lon = [row['X_From']],
        lat = [row['Y_From']],
        marker = {'size': 50*row['Flow']/sdc_cost['Flow'].sum(),
                  'color': 'red'}
        ))

temp_X = sdc_cost['X_To'].append(sdc_cost['X_From'])
temp_Y = sdc_cost['Y_To'].append(sdc_cost['Y_From'])
    
fig.update_layout(
    margin ={'l':0,'t':0,'b':0,'r':0},
    mapbox = {
        'accesstoken': mapbox_access_token,
        'center': {'lon': temp_X.mean(),
                   'lat': temp_Y.mean()},
        'bearing': 0,
        'zoom': getBoundsZoomLevel([temp_Y.max(),
                                    temp_X.max(),
                                    temp_Y.min(),
                                    temp_X.min()],
                                   {'height': map_height,
                                    'width': map_width})})

fig

# Close the Pandas Excel writer and output the Excel file.
writer.save()
# Save and release handle
#writer.close()
#writer.handles = None

fig.write_image(fn.replace('.xlsm', ' - '+str(scenario_name)+'.jpg'),
                height=map_height,
                width=map_width)