Assignment

# import packages
import os
import warnings
import datetime
import sys
from copy import deepcopy
import pandas as pd
from openpyxl.reader.excel import load_workbook
from openpyxl.workbook import Workbook
from itertools import islice
import xlsxwriter
from math import floor
from tkinter import Tk
from tkinter.filedialog import askopenfilename
from collections import OrderedDict
"""
Loads Dictionary Index
----------------------
'Delivery',     key
'Source_ID',    0
'Plan_Date',    1
'Origin',       2
'Trans_Mode',   3 
'Destination',  4
'State',        5
'Pallets',      6 
'Gross_Wt',     7
'To_Fill',      8
'Prefill_%',    9
-----------------------
"""
"""
Orders Dictionary Index
-----------------------
'Order_ID',     Key
'Source_ID',    0
'Plan_WK',      1
'Origin',       2
'Trans_Mode',   3
'Destination',  4
'State',        5
'Base_UoM',     6
'MSKU',         7
'SO',           8
'SO_Line',      9
'Unit_Wt',      10
'U_Pallet',     11
'Pallets',      12
-----------------------
"""
# start timer
print(f"\n//////////////////////////////////////////////////////////////////////")
s_s_time = datetime.datetime.now()
print(f"Beginning phase 1...")
print(f"Unplanned demand assignment")
print(f"Starting script timer...")
print(f"Start time:                        {s_s_time}")
# set working directory
filepath = 'C:\\Users\\JSVAR\\OneDrive\\Desktop\\Light Loads Template\\LL Python'
os.chdir(filepath)
print(f"Directory set to {os.getcwd()}")
print(f"Defining script functions...")

"""Define script functions"""


def assign(target: int, p_wt: float, t_plt: float) -> list:
    """Calculates how many pallets of unplanned demand can be assigned to the delivery"""
    try:
        p_assigned = floor(target / p_wt)
    except ZeroDivisionError:
        p_assigned = 0
    if p_wt > target or p_assigned == 0:
        n_target = target
        return [0, 0, n_target]
    elif p_assigned > t_plt:
        filled = (t_plt * p_wt)
        n_target = target - filled
        return [t_plt, filled, n_target]
    else:
        filled = (p_assigned * p_wt)
        n_target = target - filled
        t_plt = p_assigned
        return [t_plt, filled, n_target]


def take(n, iterable):
    """Return first n items of the iterable as a list"""
    return dict(islice(iterable, n))


def import_lwvr(lwvr_file: str) -> dict:
    """Get source data and create loads Dictionary"""
    # start timer
    begin_time = datetime.datetime.now()
    print(f"Importing data to dictionary")
    print(f"Start time:                        {begin_time}")
    # turn off warnings
    warnings.simplefilter("ignore")
    # establish variables
    dunnage = 2000
    delength = 9
    # open source report
    wb1 = load_workbook(lwvr_file)
    ws1 = wb1["All"]
    # create destination workbook
    wb2 = Workbook()
    ws2 = wb2.active
    ws2.title = "Sheet1"
    # calculate total number of rows and
    # columns in source excel file
    mr = ws1.max_row
    mc = ws1.max_column
    # copying the cell values from source
    # excel file to destination excel file
    for i in range(1, mr + 1):
        for j in range(1, mc + 1):
            # reading cell value from source excel file
            c = ws1.cell(row=i, column=j)
            # writing the read value to destination excel file
            cell = ws2.cell(row=i, column=j)
            cell.value = c.value

    # format columns 1 and 2 as numbers
    for i in range(2, mr + 1):
        for j in range(1, 3):
            # ignore blank values
            try:
                c = ws2.cell(row=i, column=j)
                value = int(c.value)
                c.value = value
            except TypeError as e:
                continue

    # delete unwanted columns
    ws2 = wb2['Sheet1']
    ws2.delete_cols(7, 1)
    ws2.delete_cols(8, 3)
    ws2.delete_cols(10, 1)
    ws2.delete_cols(14, 1)
    # insert calculated fields
    ws2['S1'] = "To Fill"
    ws2['T1'] = "Include"
    # get new max rows and columns
    mr = ws2.max_row
    # calculate To Fill values
    for i in range(2, mr + 1):
        # ignore blank values
        try:
            c1 = ws2.cell(row=i, column=17)  # Total Weight
            c2 = ws2.cell(row=i, column=14)  # Resource Max Weight
            d = ws2.cell(row=i, column=19)  # To Fill
            # To Fill = Resource Max Weight - dunnage - Total Weight
            d.value = c2.value - dunnage - c1.value
        except TypeError as e:
            d = ws2.cell(row=i, column=19)  # To Fill
            d.value = -1
            continue

    """
    Conditions to NOT include Delivery
    1. Length Delivery < 9 (DeLength)
    2. Max Resource Weight Status = Tentative
    3. Multiple Deliveries on Shipment = X
    4. Distributed to EWM = X
    """
    for i in range(2, mr + 1):
        c1 = ws2.cell(row=i, column=2)  # Delivery
        c2 = ws2.cell(row=i, column=15)  # Resource Max Weight Status
        c3 = ws2.cell(row=i, column=12)  # Multiple Deliveries on the Shipment
        c4 = ws2.cell(row=i, column=11)  # Distributed to EWM
        c5 = ws2.cell(row=i, column=19)  # To Fill
        d = ws2.cell(row=i, column=20)  # Include
        if len(str(c1.value)) < delength or c2.value == 'Tentative' or c3.value == 'X' \
                or c4.value == 'X' or c5.value <= 0:
            d.value = 0
        else:
            d.value = 1

    # saving the destination excel file
    wb2.save('lwvr.xlsx')
    # convert xlsx to csv and remove xlsx
    read_file = pd.read_excel(r'lwvr.xlsx')
    read_file.to_csv(r"lwvr.csv", index=None, header=True)
    # read source csv file
    df = pd.read_csv('lwvr.csv')
    # remove rows where Include == 0
    df = df[df.Include != 0]
    # remove extra columns
    df = df.drop('CSA/Planner', axis=1)
    df = df.drop('Means of Transport Description', axis=1)
    df = df.drop('Distributed to EWM', axis=1)
    df = df.drop('Multiple Deliveries on the Shipment', axis=1)
    df = df.drop('Resource Max Weight', axis=1)
    df = df.drop('Resource Max Weight Status', axis=1)
    df = df.drop('Total Weight (Product Dependent Dunnage Calc)', axis=1)
    df = df.drop('Available Weight', axis=1)
    df = df.drop('Include', axis=1)
    # format the data frame
    df = df.rename(columns={"Freight Order Number": "Shipment", "Delivery Number": "Delivery",
                            "Planned Load End Wk Nbr": "Plan_WK",
                            "Planned Load End Date": "Plan_Date", "Origin Plant Desc": "Origin",
                            "Dest Location Number": "Destination", "Dest Region": "State",
                            "Transportation Mode Description": "Trans_Mode",
                            "Total Pallets on Freight Order": "Pallets",
                            "Gross Wt LBS (FH)": "Gross_Wt", "To Fill": "To_Fill"})
    df = df.drop('Shipment', axis=1)
    df = df.drop('Plan_WK', axis=1)
    # create list of sources
    sources = ["GOLDEN BREWERY", "MILWAUKEE BREWERY", "TRENTON BREWERY",
               "FORT WORTH BREWERY", "SHENANDOAH BREWERY", "ALBANY BREWERY",
               "IRWINDALE BREWERY", "GOLDEN DC", "PORTLAND DC", "ELIZABETH DC",
               "ALBANY DC", "FORT WORTH DC", "MILWAUKEE DC",
               "CHIP FALLS LEINENKUGEL BREWERY", "BACKUS Y JOHNSTON S.A.A.",
               "TYSKIE BROWARY", "BAVARIA S.A.", "GROLSCH BREWERY",
               "BIRRA PERONI  SPA", "CZECH REPUBLIC IMPORT",
               "CANADA,MOLSON, MONTREAL IMPORT", "CANADA, MOLSON, TORONTO IMPORT"]
    # replace all unavailable Origins with 0
    df.loc[~df["Origin"].isin(sources), "Origin"] = 0
    # create new data frame without Origin == 0
    df = df[df.Origin != 0]
    # replace column values with replacements
    df.loc[df.Origin == "GOLDEN BREWERY", "Origin"] = 1000
    df.loc[df.Origin == "MILWAUKEE BREWERY", "Origin"] = 1010
    df.loc[df.Origin == "TRENTON BREWERY", "Origin"] = 1020
    df.loc[df.Origin == "FORT WORTH BREWERY", "Origin"] = 1030
    df.loc[df.Origin == "SHENANDOAH BREWERY", "Origin"] = 1040
    df.loc[df.Origin == "ALBANY BREWERY", "Origin"] = 1060
    df.loc[df.Origin == "IRWINDALE BREWERY", "Origin"] = 1070
    df.loc[df.Origin == "GOLDEN DC", "Origin"] = 2000
    df.loc[df.Origin == "PORTLAND DC", "Origin"] = 2020
    df.loc[df.Origin == "ELIZABETH DC", "Origin"] = 2030
    df.loc[df.Origin == "ALBANY DC", "Origin"] = 2060
    df.loc[df.Origin == "FORT WORTH DC", "Origin"] = 2070
    df.loc[df.Origin == "MILWAUKEE DC", "Origin"] = 2080
    df.loc[df.Origin == "CHIP FALLS LEINENKUGEL BREWERY", "Origin"] = 5000
    df.loc[df.Origin == "BACKUS Y JOHNSTON S.A.A.", "Origin"] = 6800
    df.loc[df.Origin == "TYSKIE BROWARY", "Origin"] = 6810
    df.loc[df.Origin == "BAVARIA S.A.", "Origin"] = 6850
    df.loc[df.Origin == "GROLSCH BREWERY", "Origin"] = 6860
    df.loc[df.Origin == "BIRRA PERONI  SPA", "Origin"] = 6870
    df.loc[df.Origin == "CZECH REPUBLIC IMPORT", "Origin"] = 6880
    df.loc[df.Origin == "CANADA,MOLSON, MONTREAL IMPORT", "Origin"] = 6890
    df.loc[df.Origin == "CANADA, MOLSON, TORONTO IMPORT", "Origin"] = 6900
    df.loc[df.Trans_Mode == "Intermodal", "Trans_Mode"] = "Intm"
    # filling missing values
    df.fillna(0)
    # create new columns
    df['Source_ID'] = df.agg('{0[Origin]}-{0[Trans_Mode]}-{0[Destination]}'.format, axis=1)
    df['Prefill_%'] = df['Gross_Wt'] / (df['Gross_Wt'] + df['To_Fill'])
    # format data frame columns
    df = df.astype({"Delivery": int,  "Origin": int, "Pallets": int,
                    "Gross_Wt": float, "To_Fill": float, "Prefill_%": float})
    # reindex columns
    df = df[['Delivery', 'Source_ID', 'Plan_Date', 'Origin', 'Trans_Mode',
             'Destination', 'State', 'Pallets', 'Gross_Wt', 'To_Fill', 'Prefill_%']]
    # convert data frame to dictionary
    loads = df.set_index('Delivery').T.to_dict('list')
    # remove extra files
    os.remove('lwvr.xlsx')
    os.remove('lwvr.csv')
    # turn warnings back on
    warnings.simplefilter("default")
    # print execution time
    end_time = datetime.datetime.now()
    lwvr_time = end_time - begin_time
    print(f"End time:                          {end_time}")
    print(f"LWVR Time:                         {lwvr_time}")
    print(f"Dictionary created successfully")
    return loads


def import_updr(updr_file: str) -> dict:
    """Get source data and create orders dictionary"""
    # start timer
    begin_time = datetime.datetime.now()
    print(f"Importing data to dictionary")
    print(f"Start time:                        {begin_time}")
    # turn off warnings
    warnings.simplefilter("ignore")
    # convert xlsx to csv and remove xlsx
    read_file = pd.read_excel(updr_file, sheet_name='Unplanned Demand')
    read_file.to_csv(r"UPDR.csv", index=None, header=True)
    df = pd.read_csv('UPDR.csv', header=0)
    # delete unwanted columns
    df.drop(['Region', 'CSA Name', 'Source',
             'Ship to Dist Name', 'City', 'OSKU Description', 'ABV',
             'Run Frequency', 'Confirmed Order Units', 'Shipment Units',
             'Standard Units per Partial Pallet', 'Partial Pallets of Unplanned',
             'Line Item Weight of Unplanned', 'Total Weight of Unplanned',
             'Max Vehicle Weight', 'Total Load Percent by Line Item',
             'Total Load Percent by Source and Mode'], axis=1, inplace=True)
    # format the data frame
    df = df.rename(columns={"Plan Ship Yr-Wk": "Plan_WK", "Source Number": "Origin",
                            "Ship To Number": "Destination", "OSKU Number": "OSKU",
                            "MSKU Number": "MSKU", "Base UoM": "Base_UoM",
                            "Sales Order Number": "SO", "Sales Order Line Item Number": "SO_Line",
                            "Mode": "Trans_Mode", "Net Weight": "Unit_Wt",
                            "Standard Units per Pallet": "U_Pallet",
                            "Pallets of Unplanned": "Pallets"})
    # remove lines without Trans_Mode
    df.fillna(value=0, axis=1, inplace=True)                # fill missing values with 0
    # df = df[df.Trans_Mode != 0]                           # remove 0 values
    # replace column values with replacements
    df.loc[df.Trans_Mode == "Truck", "Trans_Mode"] = 'Road'
    df.loc[df.Trans_Mode == "Intermodal (Rail)", "Trans_Mode"] = 'Intm'
    # calculate new variables
    df['UPD'] = df['Unplanned Units'] * -1
    df['Pallet_Wt'] = (df['Unit_Wt'] * df['U_Pallet']) / df['Pallets']
    df['Source_ID'] = df.agg('{0[Origin]}-{0[Trans_Mode]}-{0[Destination]}'.format, axis=1)
    df['Order_ID'] = df.agg('{0[SO]}-{0[MSKU]}'.format, axis=1)
    # delete unwanted columns
    df.drop('Unplanned Units', axis=1, inplace=True)
    df.drop('OSKU', axis=1, inplace=True)
    # reindex columns
    df = df[['Order_ID', 'Source_ID', 'Plan_WK', 'Origin', 'Trans_Mode',
             'Destination', 'State', 'Base_UoM', 'MSKU', 'SO', 'SO_Line',
             'Unit_Wt', 'U_Pallet', 'Pallets']]
    # sort 1) oldest to newest sales order, 2) kegs then cases, 3) oldest to newest plan week
    df = df.sort_values(['SO', 'Base_UoM', 'Plan_WK'], ascending=[True, False, True])
    # convert data frame to dictionary
    orders = df.set_index('Order_ID').T.to_dict('list')
    # round down pallet count to remove partial pallets
    for demand, order in orders.items():
        order[12] = floor(order[12])

    # remove extra files
    os.remove('UPDR.csv')
    # turn warnings back on
    warnings.simplefilter("default")
    # print execution time
    end_time = datetime.datetime.now()
    updr_time = end_time - begin_time
    print(f"End time:                          {end_time}")
    print(f"UPDR Time:                         {updr_time}")
    print(f"Dictionary created successfully")
    return orders


"""Send startup messages and begin timers"""
print(f"Functions and methods defined successfully")
print(f"\n//////////////////////////////////////////////////////////////////////")
# select load weight variance source file
Tk().withdraw()                         # prevent root window
lwvrfilename = askopenfilename()        # open file explorer window to select file
# select unplanned demand source file
Tk().withdraw()                         # prevent root window
updrfilename = askopenfilename()        # open file explorer window to select file
# begin data importing
# start timer 1
btime1 = datetime.datetime.now()
loads = import_lwvr(lwvrfilename)
etime1 = datetime.datetime.now()
step1_time = etime1 - btime1
print(f"Load Weight Variance Report dictionary imported successfully")
print(f"\n//////////////////////////////////////////////////////////////////////")
# start timer 2
btime2 = datetime.datetime.now()
orders = import_updr(updrfilename)
etime2 = datetime.datetime.now()
step2_time = etime2 - btime2
print(f"Unplanned Demand Report dictionary imported successfully")

# deep copy orders to create a separate dictionary for assignment
n_orders = deepcopy(orders)
# deep copy orders to create a separate dictionary as loads to create
all_orders = deepcopy(orders)

"""Prompt user if they would like to build loads in phase 2"""
print(f"\n//////////////////////////////////////////////////////////////////////")
response1 = input("Would you like to create new loads?               : ")
response1 = response1.title()
if response1 == 'Yes' or response1 == 'Y':
    print(f"Maximum theoretical shipments:                      {len(all_orders.keys())}")
    response2 = input("How many shipments would you like to create?      : ")
    response2 = int(response2)
    response3 = input("What cutoff weight is successful?                 : ")

"""Delete key values pairs in orders dictionary that have no matches in the loads dictionary"""
print(f"\n//////////////////////////////////////////////////////////////////////")
# start timer
a_begin_time = datetime.datetime.now()
print(f"Removing unusable unplanned demand...")
print(f"Start time:                        {a_begin_time}")
# calculate current week + 2 weeks
today_date = datetime.datetime.today()  # return today's date
confirm_end_date = today_date + datetime.timedelta(days=14)  # calculate confirmation week (today +14 days)
confirm_2wk = confirm_end_date.strftime("%Y-%W")  # format confirmation week as 'YYYY-WK'
# loop into orders dictionary
for demand, order in orders.items():
    matches = 0
    usource_id = order[0]   # grab source_id for matching
    mode = order[3]         # grab trans_mode
    plan_wk = order[1]
    # count number of matches each order has in the loads dictionary
    for delivery, load in loads.items():
        lsource_id = load[0]  # grab source_id for matching
        if lsource_id == usource_id:
            matches = matches + 1
        else:
            continue
    # set pallets to 0 if order source ID has no matches in loads dictionary or if trans_mode == 0
    if matches == 0 or mode == 0 or plan_wk == confirm_2wk:
        order[12] = 0
    else:
        continue

print(f"Unmatched unplanned demand identified")
# List of keys to be deleted from dictionary
selectedKeys = list()
# iterate over the list and add keys where pallets == 0 to a list
for demand, order in orders.items():
    u_pallets = order[12]  # grab number of pallets
    # delete dictionary key if 0 pallets are available on the order
    if u_pallets == 0:
        selectedKeys.append(demand)
    else:
        continue

# iterate over the list and delete corresponding key from dictionary
for key in selectedKeys:
    if key in orders:
        del orders[key]

print(f"Unmatched unplanned demand deleted successfully")

"""Calculate starting totals for final results"""
# find total number of starting pallets in orders dictionary
all_pallets = 0
for demand, order in orders.items():
    u_pallets = order[12]
    all_pallets = all_pallets + u_pallets

print(f"Starting Pallets Available:        {all_pallets}")

"""Create winshuttle script excel file"""
# mm/dd/yyyy
date = datetime.datetime.today().strftime("%m-%d-%Y")
# create file name as a variable
filename = filepath + "\\Light Loads - FM36 - " + date + ".xlsx"
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet('Script')
# create formatting methods
comma_format = workbook.add_format({'num_format': '#,##0.00', 'align': 'right'})
merge_format = workbook.add_format({'align': 'center', 'italic': True, 'bold': True})
right_format = workbook.add_format({'align': 'right'})
center_format = workbook.add_format({'align': 'center'})
bold_format = workbook.add_format({'bold': True, 'align': 'center'})
# write workbook headers for script output
worksheet.write(0, 0, 'RUN LOG', bold_format)                       # cell A1
worksheet.write(0, 1, 'VALIDATE LOG', bold_format)                  # cell B1
worksheet.write(0, 2, 'Delivery', bold_format)                      # cell C1
worksheet.write(0, 3, 'Select Date', bold_format)                   # cell D1
worksheet.write(0, 4, 'Sales Order', bold_format)                   # cell E1
worksheet.write(0, 5, 'Line Item', bold_format)                     # cell F1
worksheet.write(0, 6, 'Line Item', bold_format)                     # cell G1
worksheet.write(0, 7, 'MSKU', bold_format)                          # cell H1
worksheet.write(0, 8, 'Units', bold_format)                         # cell I1
worksheet.write(0, 9, 'Plan Date', bold_format)                     # cell J1
worksheet.write(0, 10, 'Order ID', bold_format)                     # cell K1
worksheet.write(0, 11, 'Gross Wt', bold_format)                     # cell L1
worksheet.write(0, 12, 'Start ToFill', bold_format)                 # cell K1
worksheet.write(0, 13, 'Wt Assigned', bold_format)                  # cell L1
worksheet.write(0, 14, 'Plts Assigned', bold_format)                # cell M1
worksheet.write(0, 15, 'New ToFill', bold_format)                   # cell N1
# write phase 1 execution results
worksheet.merge_range('R1:S1', 'Phase 1: Light Load Filling', merge_format)
# write summary categories
worksheet.write(1, 17, 'Total Pallets Assigned')                    # cell R2
worksheet.write(2, 17, 'Total Weight Assigned')                     # cell R3
worksheet.write(3, 17, 'Full Truck Equivalent')                     # cell R4
worksheet.write(4, 17, 'Execution Time')                            # cell R5
# write summary results
worksheet.write(1, 18, '=SUM($O:$O)', comma_format)                 # cell S2
worksheet.write(2, 18, '=SUM($N:$N)', comma_format)                 # cell S3
worksheet.write(3, 18, '=$S$3/43000', comma_format)                 # cell S4
# set column widths by range
worksheet.set_column(0, 0, 40)                                      # A:A
worksheet.set_column(1, 1, 13)                                      # B:B
worksheet.set_column(2, 9, 11)                                      # C:J
worksheet.set_column(10, 10, 18)                                    # K:K
worksheet.set_column(11, 13, 11)                                    # L:N
worksheet.set_column(14, 14, 12)                                    # O:O
worksheet.set_column(15, 15, 11)                                    # P:P
worksheet.set_column(17, 17, 20.29)                                 # R:R
worksheet.set_column(18, 18, 13.14)                                 # S:s

"""Start the main assignment loop"""
# create variable to count how many pallets were assigned
p = 0
# set starting row and column for new worksheet
row = 1
col = 2
# loop through dictionary of loads to work on
print(f"\n//////////////////////////////////////////////////////////////////////")
print(f"Beginning unplanned demand assignment")
for delivery, load in loads.items():
    # grab values from dictionary list
    lsource_id = load[0]                                        # Source ID
    gross_wt = load[7]                                          # Gross Wt
    to_fill = load[8]                                           # To Fill
    # convert date object to correct date format as string
    plan_date = load[1]                                         # plan_date YYYY-MM-DD
    p_year = plan_date[0:4]                                     # year
    p_month = plan_date[5:7]                                    # month
    p_day = plan_date[8:10]                                     # day
    plan_date = p_month + '/' + p_day + '/' + p_year            # reconfigure to string
    # calculate selection date - Today + 1 year
    today_date = datetime.datetime.today()                      # return today's date
    n_year_today = today_date + datetime.timedelta(days=365)    # calculate next year today
    s_date = n_year_today.strftime("%m/%d/%Y")                  # format confirmation week as 'YYYY-WK
    # for each load, loop through dictionary of UPD available to find assignment matches
    for demand, order in orders.items():
        usource_id = order[0]                                   # order source_id
        msku = order[7]                                         # msku
        so = order[8]                                           # sales order
        line = order[9]                                         # sales order line item
        unit_wt = order[10]                                     # unit weight
        u_p_pal = order[11]                                     # units per pallet
        pallets = order[12]                                     # pallets
        pallet_wt = unit_wt * u_p_pal
        # make assignment if source_ids match
        if lsource_id == usource_id:
            # calculate how much UPD to assign using the assign() function
            assigned = assign(target=to_fill, p_wt=pallet_wt, t_plt=pallets)
            # [t_plt, filled, n_target] output list from assign() function
            tot_plt = assigned[0]                               # total pallets assigned
            a_filled = assigned[1]                              # weight assigned
            n_to_fill = assigned[2]                             # new to fill target
            units = tot_plt * u_p_pal                           # find how many units were assigned
            # if units were assigned
            if units > 0:
                # update orders dictionary with new pallets available
                order[12] = pallets - tot_plt                   # new pallets available
                # store to fill before assignment
                orig_to_fill = to_fill                          # to fill before assignment
                # update to fill amount with new target to fill amount
                to_fill = n_to_fill                             # lower to fill by more amount assigned
                # print results in next row of script file
                worksheet.write(row, 2, delivery)               # column C - Delivery
                worksheet.write(row, 3, s_date, center_format)  # column D - Select Date
                worksheet.write(row, 4, so)                     # column E - Sales Order
                worksheet.write(row, 5, line)                   # column F - Line Item
                worksheet.write(row, 6, line)                   # column G - Line Item
                worksheet.write(row, 7, msku)                   # column H - MSKU
                worksheet.write(row, 8, units, right_format)                  # column I - Units
                worksheet.write(row, 9, plan_date, center_format)              # column J - Plan Date
                worksheet.write(row, 10, usource_id, center_format)            # column K - Order ID
                worksheet.write(row, 11, gross_wt, comma_format)              # column L - Gross Wt
                worksheet.write(row, 12, orig_to_fill, comma_format)          # column M - Start ToFill
                worksheet.write(row, 13, a_filled, comma_format)              # column N - Wt Assigned
                worksheet.write(row, 14, tot_plt, right_format)               # column O - Plts Assigned
                worksheet.write(row, 15, to_fill, comma_format)               # column P - New ToFill
                worksheet.write(row, 20, lsource_id)                          # column U - Load Source ID
                # increase row index
                row += 1
            else:
                # if no units are assigned continue to next line
                continue
        else:
            # if the next line doesn't match continue to next line
            continue

"""Calculate totals for final results in phase 1"""
# find total number of remaining pallets in orders dictionary
print(f"Totaling unplanned demand assigned...")
count_pallets = 0
for demand, order in orders.items():
    u_pallets = order[12]
    count_pallets = count_pallets + u_pallets

print(f"Assignment completed")
# print execution time
a_end_time = datetime.datetime.now()
assign_time = a_end_time - a_begin_time
e_assign_time = str(assign_time)
print(f"Assign Time:                        {assign_time}")
# print total script execution and results time
s_e_time = datetime.datetime.now()
t_script_time = s_e_time - s_s_time
s_script_time = str(t_script_time)
# add in results fields
worksheet.write(4, 18, e_assign_time)         # cell S5
# close created workbook
workbook.close()
print(f"End time:                           {a_end_time}")
print(f"Ending phase 1...")
print(f"Phase 1 script time:                {t_script_time}")

"""Prompt user if they would like to build loads and enter phase 2"""
if response1 == 'Yes' or response1 == 'Y':
    cutoff = str(response3)
else:
    print(f"\n//////////////////////////////////////////////////////////////////////\n")
    print(f"Your light loads script has been created successfully!")
    print(f"Total UPD assignment script time")
    print(f"Import Loads data:      {step1_time}")
    print(f"Import Orders data:     {step2_time}")
    print(f"Assignment time:        {assign_time}")
    print(f"Phase 1 script time:                    {t_script_time}")
    print(f"\n//////////////////////////////////////////////////////////////////////\n")
    sys.exit(0)

print(f"\n//////////////////////////////////////////////////////////////////////")
print(f"Beginning phase 2...")
print(f"Building new loads...")
# start timer
s_s_time2 = datetime.datetime.now()
s_s_time3 = datetime.datetime.now()
"""
n_orders Dictionary Index
-----------------------
'Order_ID',     Key
'Source_ID',    0
'Plan_WK',      1
'Origin',       2
'Trans_Mode',   3
'Destination',  4
'State',        5
'Base_UoM',     6
'MSKU',         7
'SO',           8
'SO_Line',      9
'Unit_Wt',      10
'U_Pallet',     11
'Pallets',      12
'Target_Wt',    13
-----------------------
"""
# update all_orders and n_orders dictionaries with key value pairs from orders dictionary
n_orders.update(orders)
all_orders.update(orders)

# append new column with 0 for target_wt
for demand, all_order in all_orders.items():
    all_order.append(0)

"""Delete key values pairs in all_orders and n_orders dictionaries that have 0 pallets available"""
# List of keys to be deleted from all_orders dictionary
no_pallets1 = list()
# iterate over the list and add keys where pallets == 0 to a list
for demand, all_order in all_orders.items():
    u_pallets1 = all_order[12]  # grab number of pallets
    # delete dictionary key if 0 pallets are available on the order
    if u_pallets1 == 0:
        no_pallets1.append(demand)
    else:
        continue

# iterate over the list and delete corresponding key from dictionary
for key in no_pallets1:
    if key in all_orders:
        del all_orders[key]

# List of keys to be deleted from n_orders dictionary
no_pallets2 = list()
# iterate over the list and add keys where pallets == 0 to a list
for demand, n_order in n_orders.items():
    u_pallets2 = n_order[12]  # grab number of pallets
    # delete dictionary key if 0 pallets are available on the order
    if u_pallets2 == 0:
        no_pallets2.append(demand)
    else:
        continue

# iterate over the list and delete corresponding key from dictionary
for key in no_pallets2:
    if key in n_orders:
        del n_orders[key]

# sort n_orders dictionary descending by source_id
n_orders = OrderedDict(sorted(n_orders.items(), key=lambda x: x[0]))        # , reverse=True
# check timer
s_e_time3 = datetime.datetime.now()
recalc_t = s_e_time3 - s_s_time3
print(f"Recalculate UPD:                    {recalc_t}")

"""Find target full truck weight for each order in all_orders dictionary"""
# start timer
s_s_time4 = datetime.datetime.now()
# iterate through n_orders dictionary and look up lane weight from loads dictionary
for demand, all_order in all_orders.items():
    usource_id = all_order[0]  # order source_id
    # convert '1000-Road-22003' to '1000-Road'
    ulane = usource_id[0:9]
    for delivery, load in loads.items():
        lsource_id = load[0]    # source_id
        llane = lsource_id[0:9]
        gross_wt = load[7]      # gross_wt
        to_fill = load[8]       # to_fill
        target_wt = gross_wt + to_fill
        if ulane == llane:
            # update target_wt in n_orders if lanes match
            all_order[13] = target_wt
        else:
            # skip if lanes don't match
            continue

"""Delete key values pairs in all_orders dictionary that have 0 target_wt"""
# List of keys to be deleted from dictionary
no_target = list()
# iterate over the list and add keys where pallets == 0 to a list
for demand, all_order in all_orders.items():
    target_wt = all_order[13]  # target wt
    # delete dictionary key if target wt is 0
    if target_wt == 0:
        no_target.append(demand)
    else:
        continue

# iterate over the list and delete corresponding key from dictionary
for key in no_target:
    if key in all_orders:
        del all_orders[key]

# check timer
s_e_time4 = datetime.datetime.now()
targ_calc = s_e_time4 - s_s_time4
print(f"Calculate targets:                  {targ_calc}")

"""Create list of loads to build from the top of the all_orders dictionary"""
# start timer
s_s_time5 = datetime.datetime.now()
all_orders = take(response2, orders.items())        # replace dictionary with first x keys of itself

# find average target_wt in all_orders
avg_target = list()
# iterate over the list and add keys where pallets == 0 to a list
for demand, all_order in all_orders.items():
    target_wt = all_order[13]  # target wt
    # add target wt to list if wt > 0
    if target_wt > 0:
        avg_target.append(target_wt)
    else:
        continue

# calculate average lane weight
a_t_wt = sum(avg_target) / len(avg_target)
a_t_formula = '=$S$5*' + str(round(a_t_wt, 2))

"""Create new workbook to output results"""
# mm/dd/yyyy
date2 = datetime.datetime.today().strftime("%m-%d-%Y")
# create file name as a variable
filename2 = filepath + "\\New Loads - FM36 - " + date2 + ".xlsx"
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook(filename2)
worksheet = workbook.add_worksheet('Script')
# create formatting methods
comma_format = workbook.add_format({'num_format': '#,##0.00', 'align': 'right'})
merge_format = workbook.add_format({'align': 'center', 'italic': True, 'bold': True})
right_format = workbook.add_format({'align': 'right'})
left_format = workbook.add_format({'align': 'left'})
center_format = workbook.add_format({'align': 'center'})
bold_format = workbook.add_format({'bold': True, 'align': 'center'})
# write workbook headers for script output
worksheet.write(0, 0, 'RUN LOG', bold_format)                       # cell A1
worksheet.write(0, 1, 'VALIDATE LOG', bold_format)                  # cell B1
worksheet.write(0, 2, 'Delivery', bold_format)                      # cell C1
worksheet.write(1, 2, 1)                                            # cell C2
worksheet.write(0, 3, 'Select Date', bold_format)                   # cell D1
worksheet.write(0, 4, 'Sales Order', bold_format)                   # cell E1
worksheet.write(0, 5, 'Line Item', bold_format)                     # cell F1
worksheet.write(0, 6, 'Line Item', bold_format)                     # cell G1
worksheet.write(0, 7, 'MSKU', bold_format)                          # cell H1
worksheet.write(0, 8, 'Units', bold_format)                         # cell I1
worksheet.write(0, 9, 'Plan Date', bold_format)                     # cell J1
worksheet.write(0, 10, 'Order ID', bold_format)                     # cell K1
worksheet.write(0, 11, 'Target Wt', bold_format)                    # cell L1
worksheet.write(0, 12, 'Start ToFill', bold_format)                 # cell K1
worksheet.write(0, 13, 'Wt Assigned', bold_format)                  # cell L1
worksheet.write(0, 14, 'Plts Assigned', bold_format)                # cell M1
worksheet.write(0, 15, 'New ToFill', bold_format)                   # cell N1
worksheet.write(0, 16, 'Cutoff Met', bold_format)                   # cell Q1
# write phase 2 execution categories
worksheet.merge_range('R1:S1', 'Phase 2: New Load Planning', merge_format)
worksheet.write(1, 17, 'Total Pallets Assigned')                    # cell R2
worksheet.write(2, 17, 'Total Weight Assigned')                     # cell R3
worksheet.write(3, 17, 'Full Truck Wt Assigned')                    # cell R4
worksheet.write(4, 17, 'Full Trucks Created')                       # cell R5
worksheet.write(5, 17, 'Execution Time')                            # cell R6
# write summary results
worksheet.write(1, 18, '=SUM($O:$O)', comma_format)                 # cell S2
worksheet.write(2, 18, '=SUM($N:$N)', comma_format)                 # cell S3
worksheet.write_formula('S4', a_t_formula, comma_format)            # cell S4
worksheet.write(4, 18, '=SUM($Q:$Q)', comma_format)                 # cell S5
# write load status categories
worksheet.write(0, 19, 'Shipment', bold_format)                     # cell T1
worksheet.write(0, 20, 'Status', bold_format)                       # cell U1
worksheet.write(0, 21, 'Lines', bold_format)                        # cell V1
worksheet.write(0, 22, 'Wt Rem', bold_format)                       # cell W1
worksheet.write(0, 23, 'Plts Asgn Histogram', bold_format)          # cell X1
# set column widths by range
worksheet.set_column(0, 0, 40)                                      # A:A
worksheet.set_column(1, 1, 13)                                      # B:B
worksheet.set_column(2, 9, 11)                                      # C:J
worksheet.set_column(10, 10, 18)                                    # K:K
worksheet.set_column(11, 13, 11)                                    # L:N
worksheet.set_column(14, 14, 12)                                    # O:O
worksheet.set_column(15, 16, 11)                                    # P:q
worksheet.set_column(17, 17, 20.29)                                 # R:R
worksheet.set_column(18, 18, 13.14)                                 # S:S
worksheet.set_column(19, 22, 10)                                    # T:W
worksheet.set_column(23, 23, 18)                                    # X:X

"""Start the main building loop"""
# create variable to count how many pallets were assigned
p = 0
# create variable to designate which load is being worked on
l_num = 1
# set starting row and column for new worksheet
row = 1
# loop through dictionary of loads to work on
print(f"Beginning unplanned demand assignment")
for shipments, all_order in all_orders.items():
    allsource_id = all_order[0]                                     # load source_id
    target_wt = all_order[13]                                       # target wt
    s_target_wt = all_order[13]                                     # target wt - true start
    # calculate selection date - Today + 1 year
    today_date = datetime.datetime.today()                          # return today's date
    n_year_today = today_date + datetime.timedelta(days=365)        # calculate next year today
    s_date = n_year_today.strftime("%m/%d/%Y")                      # format confirmation week as 'YYYY-WK
    # calculate selection date - Today + 14 days
    today_14 = today_date + datetime.timedelta(days=14)             # calculate next year today
    twks_date = today_14.strftime("%m/%d/%Y")                       # format confirmation week as 'YYYY-WK
    # for each load, loop through dictionary of UPD available to find assignment matches
    for demand, n_order in n_orders.items():
        usource_id = n_order[0]                                     # order source_id
        msku = n_order[7]                                           # msku
        so = n_order[8]                                             # sales order
        line = n_order[9]                                           # sales order line item
        unit_wt = n_order[10]                                       # unit weight
        u_p_pal = n_order[11]                                       # units per pallet
        pallets = n_order[12]                                       # pallets
        pallet_wt = unit_wt * u_p_pal                               # pallet wt
        # make assignment if source_ids match
        if allsource_id == usource_id:
            # calculate how much UPD to assign using the assign() function
            assigned = assign(target=target_wt, p_wt=pallet_wt, t_plt=pallets)
            # [t_plt, filled, n_target] output list from assign() function
            tot_plt = assigned[0]           # total pallets assigned
            a_filled = assigned[1]          # weight assigned
            n_to_fill = assigned[2]         # new to fill target
            units = tot_plt * u_p_pal       # find how many units were assigned
            # if units were assigned
            if units > 0:
                # update orders dictionary with new pallets available
                n_order[12] = pallets - tot_plt
                # store to fill before assignment
                orig_to_fill = target_wt
                # update to fill amount with new target to fill amount
                target_wt = n_to_fill
                # print results in next row of script file
                worksheet.write(row, 3, s_date, center_format)          # column D - select date
                worksheet.write(row, 4, so)                             # column E - Sales Order
                worksheet.write(row, 5, line)                           # column F - Line Item
                worksheet.write(row, 6, line)                           # column G - Line Item
                worksheet.write(row, 7, msku)                           # column H - MSKU
                worksheet.write(row, 8, units)                          # column I - Units Added
                worksheet.write(row, 9, twks_date, center_format)       # column J - Source ID
                worksheet.write(row, 10, allsource_id, center_format)   # column K - Order ID
                worksheet.write(row, 11, s_target_wt, comma_format)     # column L - Target Wt
                worksheet.write(row, 12, orig_to_fill, comma_format)    # column M - Start ToFill
                worksheet.write(row, 13, a_filled, comma_format)        # column N - Wt Assigned
                worksheet.write(row, 14, tot_plt, right_format)         # column O - Plts Assigned
                worksheet.write(row, 15, target_wt, comma_format)       # column P - New ToFill
                # increase row index
                row += 1
            else:
                # if no units are assigned continue to next line
                continue
        else:
            # if the next line doesn't match continue to next line
            continue

# increase row index final time
row += 1
# distinguish variables to prevent continuity issues
row1 = row
row2 = row
row3 = row
row4 = row
row5 = row - 1
row6 = row
row7 = row
row8 = row
row9 = row

print(f"Load building completed")
print(f"Entering formulas...")
# input formula for column c starting in cell C3
for i in range(3, row1):
    # =IF(AND(P2>P3,K3=K2),C2,C2+1)
    inp_cell = 'C' + str(i)
    cell1 = 'P' + str(i - 1)
    cell2 = 'P' + str(i)
    cell3 = 'K' + str(i)
    cell4 = 'K' + str(i - 1)
    cell5 = 'C' + str(i - 1)
    formula = '=IF(AND(' + cell1 + '>' + cell2 + ',' + cell3 + '=' + cell4 + '),' + cell5 + ',' + cell5 + '+1)'
    # input string as formula
    worksheet.write_formula(inp_cell, formula)

# input formula for column n starting in cell N2
for i in range(2, row2):
    # =IF(OR(C2<C3,ISBLANK(C3)),IF(M2<{cutoff},1,0),0)
    inp_cell = 'Q' + str(i)
    cell1 = 'C' + str(i)
    cell2 = 'C' + str(i + 1)
    cell3 = 'P' + str(i)
    formula = '=IF(OR(' + cell1 + '<' + cell2 + ',ISBLANK(' + cell2 + ')),IF(' + cell3 + '<' + cutoff + ',1,0),0)'
    # input string as formula
    worksheet.write_formula(inp_cell, formula)

# input load completion count list starting in cell Q2
l_comp = 1
for i in range(1, row5):
    # inp_cell = 'T' + str(i)
    # worksheet.write_formula(inp_cell, l_comp1)
    worksheet.write(i, 19, l_comp, left_format)
    l_comp += 1

# input formula for column r starting in cell U2
for i in range(2, row6):
    # =IF(T2>{last cell in C},"-",IF(SUMIF($C:$C,T2,$Q:$Q)=1,"Filled", "Light"))
    inp_cell = 'U' + str(i)
    cell1 = 'T' + str(i)
    cell2 = '$C$' + str(row1 - 1)
    formula = '=IF(' + cell1 + '>' + cell2 + ',"-",IF(SUMIF($C:$C,' + cell1 + ',$Q:$Q)=1,"Filled", "Light"))'
    # input string as formula
    worksheet.write_formula(inp_cell, formula)

# input formula for column s starting in cell V2
for i in range(2, row7):
    # =IF($U2="-","-",COUNTIF($C:$C,$T2))
    inp_cell = 'V' + str(i)
    cell1 = 'U' + str(i)
    cell2 = 'T' + str(i)
    formula = '=IF($' + cell1 + '="-","-",COUNTIF($C:$C,$' + cell2 + '))'
    # input string as formula
    worksheet.write_formula(inp_cell, formula, right_format)

# input formula for column s starting in cell W2
for i in range(2, row8):
    # =IFERROR(INDEX($C:$P,MATCH($T2,$C:$C,0)+$V2-1,14),"-")
    inp_cell = 'W' + str(i)
    cell1 = 'T' + str(i)
    cell2 = 'V' + str(i)
    formula = '=IFERROR(INDEX($C:$P,MATCH($' + cell1 + ',$C:$C,0)+$' + cell2 + '-1,14),"-")'
    # input string as formula
    worksheet.write_formula(inp_cell, formula, comma_format)

# input formula for column s starting in cell X2
for i in range(2, row9):
    # =IF(COUNTIFS($O:$O,$T2)=0,"-",COUNTIFS($O:$O,$T2))
    inp_cell = 'X' + str(i)
    cell1 = 'T' + str(i)
    formula = '=IF(COUNTIFS($O:$O,$' + cell1 + ')=0,"-",COUNTIFS($O:$O,$' + cell1 + '))'
    # input string as formula
    worksheet.write_formula(inp_cell, formula, right_format)

# conditional format with data bars for formula range in column X
# turn off warnings
warnings.simplefilter("ignore")
x_range = 'X1:X' + str(row9 - 1)
worksheet.conditional_format(x_range, {'type': 'data_bar', 'bar_border_color': '091F3F', 'bar_color': 'D8E0E4'})
# turn warnings back on
warnings.simplefilter("default")

# check timer
s_e_time5 = datetime.datetime.now()
l_build_t = s_e_time5 - s_s_time5
print(f"Build time:                         {l_build_t}")

s_e_time2 = datetime.datetime.now()
t_script_time2 = s_e_time2 - s_s_time2
s_script_time2 = str(t_script_time2)
# add in results fields
worksheet.write(5, 18, s_script_time2)                  # cell S6
# close created workbook
workbook.close()

# print final results
final_time = t_script_time + t_script_time2
print(f"Load building completed")
print(f"Ending phase 2...")
print(f"Your light loads script has been created successfully!")
print(f"\n//////////////////////////////////////////////////////////////////////")
print(f"Assignment Processing Time Results")
print(f"\nImport Loads data:        {step1_time}")
print(f"Import Orders data:       {step2_time}")
print(f"Assignment time:          {assign_time}")
print(f"Phase 1 script time:                    {t_script_time}")
print(f"\nRecalculate UPD:          {recalc_t}")
print(f"Calculate targets:        {targ_calc}")
print(f"Build time:               {l_build_t}")
print(f"Phase 2 script time:                    {t_script_time2}")
print(f"\nTotal Time:                                           {final_time}")
print(f"\n//////////////////////////////////////////////////////////////////////\n")