

'''
8/15/2017
updated to output a file that matches what needs to be submitted to the global fund on a monthly basis... 133 standard
columns and 4 additional columns for Planned and actual FLT, and Planned and Actual Cost.

6/23/2017
Updated to include additonal functiosn for KPI calculation. these run in a much shorter time frame than previous
version. 15 sec down to under .1. need to continue field testing.

6/13/2017
changed output file structure to be a document with all of the associated rows, use slicers to subset to particular KPIs so we can look at rows indvidually

NOTES: Consider constructing R markdown or something similar for internal usage

6/9/2017
The purpose of this file is to generate the monthly KPIs

KPI 1 OTIF: based on the Order last delivery year month
KPI 2 PE Turnaround: PE Response Date is used to subset it and measured as the difference between PE actionable and PE sent date
KPI 3 PO Turnaround: subset on PO Sent to Vendor Date and PQ Actionable date to PO Sent to vendor date. A concern with this one
    is that the same date that is used to measure

KPI 4 FLT: measured against an external FLT matrix maintained by Ben Smith. It is a liability because lanes are added by hand and
    needs to be adjusted over time by Ben and downloaded from Knowledge Tree, same subset as OTIF (based on order last delivery year month)
    calculate actual lead time based on difference between Vendor Inco Fulfilment date and D1 date
KPI 5: same as before but just looking for those that were late... I think we placed a restraint on it looking at just ones outside of the bound for KPI 4

KPI 6 actual vs planned cost: period is set by Order last delivery month, planned costs are currently provided by Nikolai on the freight team.. they have lots of issues..
    Grant provides the actual costs from the finance team
KPI 7 within planned cost: looking for rows where the actual costs exceed the planned costs



To do as of 3/6/17
    Add the KPIs 6 and 7. but need the reporting format to be standardized. aim for week of 3/6 to do it

The purpose of this document is to generate the 7 KPIs for VPP reporting.
'''

import numpy as np
from workdays import networkdays
import time
from datetime import datetime
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
import math
import re

from collections import Counter


#set once
#KPI 1

'''
main function, used to generate KPI values in a dataset which can be used by the other functions to quickly
calculate KPI values
'''

def run_kpis(matrix_file, dat, reporting_yr_month,save_loc,save_name,save_yes_no= 'yes',TGF = 'Yes'):

    #use these for calculations, so just make sure there are no NAs,
    # probably suboptimal, but it goes fast so I guess it is fine for now
    dat['PE Actionable Date'] = dat['PE Actionable Date'].fillna('')
    dat['PE Sent Date'] = dat['PE Sent Date'].fillna('')
    dat['PE Response Date'] = dat['PE Response Date'].fillna('')
    dat['PQ Actionable Date'] = dat['PQ Actionable Date'].fillna('')
    dat['PO Sent to Vendor Date'] = dat['PO Sent to Vendor Date'].fillna('')
    dat['Vendor INCO Fulfillment Date'] = dat['Vendor INCO Fulfillment Date'].fillna('')
    dat['Shipment Delivered Date'] = dat['Shipment Delivered Date'].fillna('')
    dat['Order Last Delivery Recorded Year - Month']=dat['Order Last Delivery Recorded Year - Month'].fillna("")


    # next section is to help mark what periods orders fall into.
    reporting_order_month = reporting_yr_month
    month_kpi = reporting_order_month[-2:]
    year_kpi = reporting_order_month[:4]

    ### KPI 6 #2 month lag
    #2 month lag
    def get_kpi_6(month,year):
        switch_list = ['01', '02']

        if month in switch_list:
            #print 'hello world'
            if month == '01':
                month_6 = '11'
                year_6 = int(year) - 1
                year_6 = str(year_6)
            if month == '02':
                month_6 = '12'
                year_6 = int(year) - 1
                year_6 = str(year_6)
        else:
            month_6 = int(month)-2
            month_6 = '0'+str(month_6)
            year_6 = year

        kpi_6_month_year = year_6+'-'+month_6
        return  kpi_6_month_year

    reporting_order_month_kpi6 = get_kpi_6(month_kpi,year_kpi)


    #KPI 4 and 5
    '''
    this part is finicky because we are currently using a list of lanes to calculate the planned lead time
    based on a file which is generated by ben smith in LSU. the file is not always complete and needs to be synced
    for consistent results within PMU. I would say that most of the errors come from here as far as inconsistancies
    in reporting
    '''
    #matrix = pd.read_csv('C:/Users/585000/Desktop/PCFSM/FLT matrix calculations/flt_v8_3_mar_21_2017.csv')
    #new as of april 19
    #matrix = pd.read_csv('C:/Users/585000/Desktop/PCFSM/FLT matrix calculations/PPM Waiver and Transportation LT Matrix 2017Q2_4_19.csv')

    #new matrix for q1 and q2 of 2017
    #matrix_loc = 'C:/Users/585000/Desktop/PCFSM/FLT matrix calculations/quarter_matricies/matrix_tests/matrix_test_5.csv'
    #adjusted 6_12_17
    #matrix_loc = 'C:/Users/585000/Desktop/PCFSM/FLT matrix calculations/quarter_matricies/matrix_tests/matrix_test_7.csv'

    #adjusted 7_27
    matrix_loc = 'C:/Users/585000/Desktop/PCFSM/FLT matrix calculations/quarter_matricies/matrix_tests/matrix_test_7_27_07.csv'

    matrix = pd.read_csv(matrix_loc)
    ### CAN ADJUST THE BRIDGE TO INCLUDE MULTIPLE
    bridge = pd.read_csv('C:/Users/585000/Desktop/PCFSM/FLT matrix calculations/bridge.csv')
    matrix_var_name = 'Full Lead Time (not including production)'
    # name in olde
    # r        'Lead Time: ASN Creation > ATP Date (Days+2 Days)'

    '''
    Need to retrieve planned and actual costs from the googlesheet we are currently using to maintain them.
    New asns need to be added monthly.
    '''
    #### GET planned costs ####
    # referencing a google sheet which I maintain in my email drive, will need to have this coppied over for maintinance
    scope = ['https://spreadsheets.google.com/feeds']

    credentials = ServiceAccountCredentials.from_json_keyfile_name('C:/Users/585000/Desktop/Python Projects/PPM USAID/spreadsheets/client_secret_2.json', scope)

    gc = gspread.authorize(credentials)
    wks = gc.open_by_key("1de7aqLFFSDAdrPYfdCS2WEye_zyqeoYlOViVlN6k3JY").get_worksheet(0)


    asn = pd.DataFrame([sub.replace(",",'') for sub in wks.col_values(1)])
    asn.columns = asn.iloc[0]
    asn = asn.ix[1:]
    planned = pd.DataFrame([sub.replace(",",'')  for sub in wks.col_values(2)])
    planned.columns = planned.iloc[0]
    planned = planned.ix[1:]
    actual = pd.DataFrame([sub.replace(",",'')  for sub in wks.col_values(3)])
    actual.columns = actual.iloc[0]
    actual = actual.ix[1:]

    spreadsheet = pd.DataFrame()#[asn['Shipment#'],planned['Planned Cost'],actual['Total Freight Cost']],axis=1)
    spreadsheet['Shipment#'] = asn['Shipment#']
    spreadsheet['Planned Cost'] = planned['Planned Cost']
    spreadsheet['Total Freight Cost'] = actual['Total Freight Cost']
    spreadsheet['Total Freight Cost'] = spreadsheet['Total Freight Cost'].str.replace('#N/A', '')
    #spreadsheet['Planned Cost'] = spreadsheet['Planned Cost'].str.replace('#N/A', '')

    #spreadsheet['Total Freight Cost'] = spreadsheet['Total Freight Cost'].str.replace('', '0')

    spreadsheet['slicer'] = 0
    for index, row in spreadsheet.iterrows():
        if row['Shipment#'] =='' and row['Planned Cost'] =='' and row['Total Freight Cost'] =='' :
            spreadsheet.loc[index,'slicer'] = 1

    spreadsheet = spreadsheet[spreadsheet['slicer']== 0 ]
    spreadsheet.drop(['slicer'], axis=1, inplace=True)

    spreadsheet['Total Freight Cost'] = spreadsheet['Total Freight Cost'].apply(pd.to_numeric, args=('coerce',))
    spreadsheet['Planned Cost'] = spreadsheet['Planned Cost'].apply(pd.to_numeric, args=('coerce',))


    #############################
    #clean matrix and bridge
    for index, row in matrix.iterrows():
        matrix.loc[index,'lane_id'] = str(row['lane_id']).upper()

    start_time = time.time()

    '''
    general dataset scrubbing, removing non global fund data for data submissions to the global fund
    '''
    # part 1: Remove rows as required
    #remove the Ta0 items

    if TGF == 'Yes':
        dat['Ta0'] = 0
        for index, row in dat.iterrows():
            a = str(row['Project Code'])
            a = a[-3:]
            if a == 'TA0':
                dat.loc[index, 'Ta0'] = 1

        dat = dat[dat['Ta0'] == 0]

        ## NEED TO FIX
        dat = dat[dat['Client Type'] != 'NGF']

        dat = dat[dat['Managed By - Project']!= 'SCMS']
        dat = dat[dat['Order Short Closed'] != "Yes"]

    #########################################################################
    #need to further vett
    #if TGF == 'No':
    #    dat = dat[dat['Managed By'] != 'L1']
    #    dat = dat[dat['Managed By'] != 'VPP']
    #########################################################################

    ### merge in costs#####


    #########################

    '''
    mark the rows with what month they fall into the 2nd and 3rd KPIs.

    formatted as Year-month is YYYY-MM
    '''

    for index, row in dat.iterrows():
        d= str(row['PE Response Date'])
        d2= str(row['PO Sent to Vendor Date'])



        if d != '':
            d = datetime.strptime(d, "%m/%d/%Y")
            dat.loc[index, 'KPI 2 PE Turnaround month'] = d.strftime("%Y-%m")

        if d2 != '':
            d2 = datetime.strptime(d2, "%m/%d/%Y")
            dat.loc[index, 'KPI 3 PO Turnaround month'] = d2.strftime("%Y-%m")

    #4, 5, 6, and 7 use same month criteria as KPI 1


    # for KPI 4 and 5
    dat = pd.merge(dat,bridge,how='left', on='Order Pick Up Country Name')
    #Asia-Afghanistan-Air-CIP
    #CHECK THAT IT IS SHIP TO COUNTRY NAME
    for index, row in dat.iterrows():
        a = str(row['Origin Region'])+'-'+str(row['Ship To Country Name'])+'-'+str(row['Shipment Mode'])+'-'+str(row['Client INCO Term'])

        dat.loc[index, 'Origin+Dest+Mode+INCO'] = a.upper()
        #print a.upper()

    ######################################################
    #### This section needs updating as new lanes are added and matrixes are updated by Ben smith

    #attempt to automate some of the data cleaning of the matrix
    dat = pd.merge(dat,matrix,how='left', left_on='Origin+Dest+Mode+INCO',right_on='lane_id')
    #Order Created Date
    col_keeper = ['-01','-02','-03','-04']
    qtr_col_names = list(matrix.columns)
    toss_col = []
    for col in qtr_col_names:
        test = 0
        for keep in col_keeper:

            if keep in col:
                test = 1
        if test == 0:
            toss_col.append(col)
            #qtr_col_names.remove(col)
            #print qtr_col_names
    toss_col.append('2017-01 leadtimes') #use this as default
    qtr_col_names = [item for item in qtr_col_names if item not in toss_col]


    dat['PO Sent to Vendor Date'] = dat['PO Sent to Vendor Date'].fillna('')

    # add the correct planned lead time based on what period it falls into as determined by PO Sent to Vendor Date
    #
    for index, row in dat.iterrows():
        col_indicator = 0
        order_create_date = str(row['PO Sent to Vendor Date'])
        if order_create_date != '':
            order_create_date = order_create_date.split('/')
            if str(order_create_date[0]) == '4' or str(order_create_date[0]) == '5' or str(order_create_date[0]) == '6':
                #print 'hello'
                order_create_qtr = str(order_create_date[2])+'-02'
                #print order_create_qtr
            elif str(order_create_date[0]) == '7' or str(order_create_date[0]) == '8' or str(order_create_date[0]) == '9':
                order_create_qtr = str(order_create_date[2])+'-03'
            elif str(order_create_date[0]) == '10' or str(order_create_date[0]) == '11' or str(order_create_date[0]) == '12':
                order_create_qtr = str(order_create_date[2]) + '-04'
            else:
                order_create_qtr = str(order_create_date[2]) + '-01'

        if order_create_date == '':
            order_create_qtr = 'null_zz'

        for col_name in qtr_col_names:
            col_name = str(col_name)

            if col_indicator == 1:
                continue
            if order_create_qtr in col_name:

                #print order_create_qtr,col_name
                dat.loc[index, matrix_var_name] = row[col_name]
                col_indicator = 1

        if col_indicator == 0:
            #print order_create_qtr

            dat.loc[index,matrix_var_name] = row['2017-01 leadtimes']


    ###################################################### NEEED TO UPDATE!


    #me being lame and doing percents the long way
    # calculation using COTD category, which is a calculated field between D1 and client promised date


    # switched PE and PO to use days_between function rather than business days:
    def business_days_between(d1, d2):
        d1 = datetime.strptime(d1, "%m/%d/%Y")
        d2 = datetime.strptime(d2, "%m/%d/%Y")
        return networkdays(d1, d2)


    def days_between(d1, d2):

        d1 = datetime.strptime(d1, "%m/%d/%Y")
        d2 = datetime.strptime(d2, "%m/%d/%Y")
        return (d2 - d1).days


    pe_to_list = []
    po_to_list = []
    kpi_4_list = []
    nanz = 0
    counter = 0
    kpi_4_list_full = []


    #calculate the differences for PE and PO turnaround  and flt vs plt (KPI 4)


    for index, row in dat.iterrows():
        pe_actionable = str(row['PE Actionable Date'])
        pe_sent = str(row['PE Sent Date'])

        pq_actionable = str(row['PQ Actionable Date'])
        pq_sent = str(row['PO Sent to Vendor Date'])

        vendor_fil_date = str(row['Vendor INCO Fulfillment Date'])
        d1 = str(row['Shipment Delivered Date'])

        #for the ones which are ok, unflag them. Basically start with all that fit in KPI and remove those that are ok
            #calculate KPI 2, and mark those that fall as less than 3 days since those are within bounds
        if pe_actionable != '' and pe_sent != '':
            pe_turnaround_time = days_between(pe_actionable, pe_sent)
            pe_to_list.append(pe_turnaround_time)
            dat.loc[index, 'pe_turnaround'] = pe_turnaround_time



        # for KPI 3 the target is within 7 days
        if pq_actionable != '' and pq_sent != '':
            pq_turnaround_time = days_between(pq_actionable, pq_sent)
            po_to_list.append(pq_turnaround_time)
            dat.loc[index, 'po_turnaround'] = pq_turnaround_time



        # this one is a bit convoluted because the business teams didnt want to
        # have to look at all of the rows not within KPI... because life is hard?
        if vendor_fil_date != '' and d1 != '':
            flt = days_between(vendor_fil_date,d1)
            plt = str(row[matrix_var_name])
            kpi_4_list_full.append((flt-float(plt))/float(plt))

            #have had issues with nan rows. and it is fairly common to see new rows
            if plt == '' or flt =='':
                nanz +=1
                flt_vs_plt = 'missing'
                flt_minus_plt ='missing'
                dat.loc[index, 'flt_vs_plt'] = flt_vs_plt
                dat.loc[index, 'flt_-_plt'] = flt_minus_plt

                #dat.loc[index, 'flt']
            # if the variation is within 125 of 0 then dont have to report them
            else:
                counter += 1
                plt = float(plt)
                flt_vs_plt = str((flt - plt) / plt)
                # print(flt_vs_plt,flt_vs_plt == 0.0)
                flt_minus_plt = flt - plt
                if flt < 0:
                    dat.loc[index, 'flt_vs_plt'] = 'flt less than 0, check vendor inco and D1 dates'
                    dat.loc[index, 'flt_-_plt'] = ''
                    dat.loc[index, 'Actual Freight Leadtime'] = flt

                if flt >= 0:
                    dat.loc[index, 'flt_vs_plt'] = flt_vs_plt
                    dat.loc[index, 'flt_-_plt'] = flt_minus_plt
                    dat.loc[index, 'Actual Freight Leadtime'] = flt

    #this one may have to be taken out. effort to reduce number of rows that are put in for root cause analysis...
    #

    # KPI 5 is based on KPI 4 outputs so just check whether the calculations are under 0
    # this could be made more efficient... but with the current setup it only has to iterate over a few hundred rows so it is computationally light
    dat['flt_vs_plt'] = dat['flt_vs_plt'].fillna('')
    for index,row in dat.iterrows():

        variance = row['flt_vs_plt']
        if variance == '':
            dat.loc[index, 'KPI 5 FLT var>0'] = 'missing'
        if variance == 'flt less than 0, check vendor inco and D1 dates':
            continue
        if variance != '':
            if  float(variance) >0:
                dat.loc[index, 'KPI 5 FLT var>0'] = 1
            else:
                dat.loc[index, 'KPI 5 FLT var>0'] = 'within'

    #### KPI 6 for testing

    #print(dat['FB Demurrage'].isnull().sum())
    dat['FB Demurrage'] = dat['FB Demurrage'].fillna(0)
    dat['FB Mod Fda'] = dat['FB Mod Fda'].fillna(0)
    dat = pd.merge(dat,spreadsheet,how='left',on='Shipment#',indicator=True)

    i=0
    z =0

    #calculating kpi 6 and 7
    #
    for index,row in dat.iterrows():

        planned = row['Planned Cost']
        total_fc = row['Total Freight Cost']

        if  planned !=0 and total_fc != 0:
            i+=1
            if math.isnan(planned)!= True and  math.isnan(total_fc)!= True:
                z+=1
                #total freight costs are the freight costs minus the side costs and mod fda costs since we are not responsible for paying those
                # and they do not count against our totals for KPI purposes
                side_costs = row['FB Demurrage'] + row['FB Mod Fda']
                total_freight  = (float(total_fc) - float(side_costs))
                bvp = (total_freight - planned) / planned
                dat.loc[index,'book_actual_vs_planned'] = bvp
                dat.loc[index,'MOH/Dem Fees'] = side_costs
                dat.loc[index,'Actual Costs Excluding MOH/Dem'] = total_freight

                #if bvp <=.1 and bvp >=-.1:
                #    dat.loc[index, 'KPI 6 freight_costs'] = 0

    #print i,z
    #dat['KPI 7 fc var>0']
    # only marks ones which are outside of the
    if 'book_actual_vs_planned' in dat.columns:
        dat['book_actual_vs_planned'] = dat['book_actual_vs_planned'].fillna('')
        for index,row in dat.iterrows():

            if row['book_actual_vs_planned'] == '':
                dat.loc[index, 'KPI 7 fc var>0'] = 'missing'

            if row['book_actual_vs_planned'] != '':
                if row['book_actual_vs_planned'] <= 0:
                    dat.loc[index, 'KPI 7 fc var>0'] = 'within'
                else:
                    dat.loc[index, 'KPI 7 fc var>0'] = 1

    if 'book_actual_vs_planned' not in dat.columns:
        dat['KPI 7 fc var>0'] = 0

    #remove rows which we dont need


    dat.drop(['Origin Region', 'Origin'	,'Dest',	'Mode',	'Client Incoterm',
               '2017-01 leadtimes','2017-02 leadtimes','2017-03 leadtimes'], axis=1, inplace=True)

    if matrix_var_name == 'Lead Time: ASN Creation > ATP Date (Days+2 Days)':
        dat.rename(columns = {'Lead Time: ASN Creation > ATP Date (Days+2 Days)':'Planned Lead Time'}, inplace = True)

    if save_yes_no == 'yes':

        dat.to_csv(save_loc+save_name,index=False)
        dat_submission = dat.copy()
        dat_submission = dat_submission.rename(columns={'Planned Cost': 'Planned Costs',
                                  'Actual Costs Excluding MOH/Dem': 'Actual Costs Excluding MOH/Dem',
                                  'Actual Freight Leadtime': 'Actual FLT',
                                  'Full Lead Time (not including production)': 'Planned LT',})
        if TGF == 'Yes':

            dat_submission_cols = ['Waiver Required?',
                        'Grant#',
                        'Project Code',
                        'PE#',
                        'PQ#',
                        'Order#',
                        'Shipment#',
                        'Order Type',
                        'Contract Type',
                        'Client Type',
                        'Order Short Closed',
                        'Order Point of Contact',
                        'PQ Buyer',
                        'Sub Vendor Code',
                        'Sub Vendor Name',
                        'PQ Product Group',
                        'Managed By',
                        'Managed By - Project',
                        'Managed By - Group',
                        'DIRDRP/RDC',
                        'Fulfill Via',
                        'Vendor INCO Term',
                        'Vendor INCO Location',
                        'Client INCO Term',
                        'Client INCO Location',
                        'Shipment Mode',
                        'Freight Forwarder',
                        'Shipment Total Item Quantity',
                        'Shipment Total Item Weight',
                        'Shipment Total Item Volume',
                        'Order Pick Up Country Code',
                        'Order Pick Up Country Name',
                        'Order Pick Up Country Latitude',
                        'Order Pick Up Country Longitude',
                        'Ship To Country Code',
                        'Ship To Country Name',
                        'Ship To Country Latitude',
                        'Ship To Country Longitude',
                        'PR Received Date',
                        'PR Last Submitted Date',
                        'PE Create Date',
                        'PE Actionable Date',
                        'PE Expiry Date',
                        'PE Estimate Ready Date',
                        'PE Sent Date',
                        'PE Response Date',
                        'PE Proceed To PQ Date',
                        'PE Requested Delivery Date',
                        'PQ Create Date',
                        'PQ Actionable Date',
                        'PQ First Submitted Date',
                        'PQ First Approved Date',
                        'PQ First Sent to Client Date',
                        'PQ Last Sent Date',
                        'PQ First Response Date',
                        'PQ Last Client Response Date',
                        'PQ Proceed To PO/SO Date',
                        'Order Created Date',
                        'PO Sent to Vendor Date',
                        'PO Vendor Confirmed Date',
                        'Vendor Promised Date',
                        'Vendor INCO Fulfillment Date',
                        'ASN/DN Created Date',
                        'Shipment Last Approved Date',
                        'Import Waiver Requested Date',
                        'Import Waiver Received Date',
                        'Shipment Documents Sent to F&L Date',
                        'F&L Accepted Shipment Date',
                        'Shipment Picked Up Date',
                        'Shipment Shipped Date',
                        'Shipment Arrived at Port Date',
                        'Shipment Entered Customs Date',
                        'Shipment Cleared Customs Date',
                        'Current Shipment Milestone',
                        'Shipment Delivered Date',
                        'Current Planned Delivery Date',
                        'PQ Item Req Delivery Date - Latest',
                        'Delivery Recorded Date',
                        'Delivery Recorded Year - Month',
                        'Delivery Recorded Month',
                        'Delivery Recorded Qtr',
                        'Delivery Recorded Year',
                        'Client Promised Delivery Date',
                        'Order Last Delivery Recorded Date',
                        'Order Fully Delivered?',
                        'Order Last Delivery Recorded Year - Month',
                        'Order Last Delivery Recorded Month',
                        'Order Last Delivery Recorded Qtr',
                        'Order Last Delivery Recorded Year',
                        'VOTD Days Late',
                        'VOTD Category',
                        'COTD Days Late',
                        'COTD Category',
                        'Shipment Total AD Days',
                        'Shipment Total UD Days',
                        'Shipment Value',
                        'PQ Value',
                        'Order Value',
                        'Pharma',
                        'Emergency',
                        'Confirmation of Receipt Date',
                        'Complaints About Delivery',
                        'FB Weight',
                        'FB Allocated Freight',
                        'FB Freight Cost Per Kilo',
                        'FB Booked Value',
                        'FB Breakup Value',
                        'FB Org Charge',
                        'FB Pickup Charge',
                        'FB Port Carr. Fees',
                        'FB Org Cust. Clearance',
                        'FB Hand Doc Charge',
                        'FB Total Trans Charge',
                        'FB Air Ocean Trans',
                        'FB Inland Drayage',
                        'FB Fuel',
                        'FB Bunker Charge',
                        'FB Carr. Sec Fees',
                        'FB Total Dest. Charge',
                        'FB Excp Cust. Clearance',
                        'FB Hand Brk Blk Fee',
                        'FB Delivery Offload To Dest.',
                        'FB Air Term Fee',
                        'FB Total Demurrage Det Oth',
                        'FB Demurrage',
                        'FB Mod Fda',
                        'FB Spl Warfage',
                        'FB Strg Fees',
                        'FB Container Deposit',
                        'FB Cold Chain Hazmat',
                        'FB Plug In Fee',
                        'FB Other',
                        'FB Not Found',
                        'Planned Costs',
                        'Actual Costs Excluding MOH/Dem',
                        'Actual FLT',
                        'Planned LT']
            dat_submission = dat_submission[dat_submission_cols]

            col_list_dat = list(dat_submission.columns)

            #classic issues where lines where there are encoding issues.
            # coercing them into latin-1 works fine... but bears further testing
            for i in col_list_dat:
                if dat_submission[i].dtypes == np.object:
                    # print i
                    dat_submission[i] = dat_submission[i].fillna('')
                    dat_submission[i] = dat_submission[i].str.decode('latin-1')

            writer = pd.ExcelWriter(save_loc+'PAD_COR_GF_submssion_format_.xlsx')
            dat_submission.to_excel(writer, 'PAD COR', index=False)
            writer.save()

            del dat_submission
    print("--- %s seconds ---" % (time.time() - start_time))

    return dat



def generate_individual_kpi_numbers(dat, months= ['2017-05'],matrix_file = '',surpress = "yes"):
    #KPI 1
    start_time = time.time()

    otif_final = []
    ontime_number = []
    otif_total_list = []
    pe_time = []
    pe_num = []
    po_time = []
    po_num = []
    kpi_4_eval_total = []
    kpi4_num = []
    kpi_5_num = []
    kpi_5_within = []
    kpi_5_total_list = []
    kpi_6_median_list = []
    kpi_6_total_list = []
    kpi_7_pass_perc_list = []
    kpi_7_eval_pass_list = []

    value = []

    for month in months:
        dat2 = dat.copy()
        reporting_period = [month]
        pattern = '|'.join(reporting_period)
        dat2['Order Last Delivery Recorded Year - Month'] = dat2['Order Last Delivery Recorded Year - Month'].fillna('')
        dat2 = dat2[dat2['Order Last Delivery Recorded Year - Month'].str.contains(pattern)]

        val = dat2['Shipment Value'].sum()
        otif_dict = dat2['COTD Category'].value_counts().to_dict()
        otif_total = float(sum(otif_dict.values()))

        if '14 Days or Less' not in otif_dict:
            otif_dict['14 Days or Less'] = 0

        if otif_total == 0:
            ontime = 0
        if otif_total != 0:

            ontime = (otif_dict['14 Days or Less'] / otif_total ) * 100


        #KPI 4
        kpi_4_list = list(dat2['flt_vs_plt'])
        kpi_4_list = [x for x in kpi_4_list if str(x) != 'nan']
        kpi_4_list = [x for x in kpi_4_list if str(x) != 'flt less than 0, check vendor inco and D1 dates']

        kpi_4_list = [float(i) for i in kpi_4_list]
        kpi_4_eval = np.median(kpi_4_list)*100

        #KPI 5
        kpi_5_dict = dat2['KPI 5 FLT var>0'].value_counts().to_dict()
        kpi_5_totals = float(sum(kpi_5_dict.values()))
        #print kpi_5_dict
        if 'within' not in kpi_5_dict:
            kpi_5_dict['within'] = 0

        if kpi_5_totals != 0:
            kpi5_within = (kpi_5_dict['within'] / kpi_5_totals) * 100
        if kpi_5_totals == 0:
            kpi5_within = 0
        def get_kpi_6(year_month):

            month = year_month[-2:]
            year = year_month[:4]

            switch_list = ['01', '02']

            if month in switch_list:
                # print 'hello world'
                if month == '01':
                    month_6 = '11'
                    year_6 = int(year) - 1
                    year_6 = str(year_6)
                if month == '02':
                    month_6 = '12'
                    year_6 = int(year) - 1
                    year_6 = str(year_6)
            else:
                month_6 = int(month) - 2
                month_6 = '0' + str(month_6)
                year_6 = year

            kpi_6_month_year = year_6 + '-' + month_6
            return kpi_6_month_year
        #get kpi 6 months
        kpi_6_period = []
        for item in reporting_period:
            item  = get_kpi_6(item)
            kpi_6_period.append(item)

        del dat2
        dat2 = dat.copy()
        pattern = '|'.join(kpi_6_period)
        dat2['Order Last Delivery Recorded Year - Month'] = dat2['Order Last Delivery Recorded Year - Month'].fillna('')
        dat2 = dat2[dat2['Order Last Delivery Recorded Year - Month'].str.contains(pattern)]

        #KPI 6
        if 'book_actual_vs_planned' not in list(dat2.columns):
            dat2['book_actual_vs_planned'] = 'nan'

        kpi_6_list = list(dat2['book_actual_vs_planned'])
        kpi_6_list = [x for x in kpi_6_list if str(x) != 'nan']
        #kpi_6_list = []
        #print kpi_6_list
        kpi_6_list = list(filter(str, kpi_6_list)) # fastest
        kpi_6_median = np.median(kpi_6_list)
        kpi_6_list_len = len(kpi_6_list)
        #kpi 7
        if len(kpi_6_list) != 0:
            kpi_7_eval_pass_per =  (float(sum(i <= 0 for i in kpi_6_list)) / len(kpi_6_list))* 100 #how many within
            kpi_7_eval_pass =  int(sum(i <= 0 for i in kpi_6_list))

        if len(kpi_6_list) == 0:
            kpi_6_list_len = 0
            kpi_7_eval_pass_per = 0.0
            kpi_7_eval_pass = 0.0

        # kpi 2 and 3
        del dat2
        dat2 = dat.copy()


        pattern = '|'.join(reporting_period)
        dat2['KPI 2 PE Turnaround month'] = dat2['KPI 2 PE Turnaround month'].fillna('')
        dat2 = dat2[dat2['KPI 2 PE Turnaround month'].str.contains(pattern)]

        dat2 = dat2.drop_duplicates('PE#')

        #dat2['KPI 2 PE Turnaround month'] = dat2['KPI 2 PE Turnaround month'].fillna('')
        #dat2 = dat2[dat2['KPI 2 PE Turnaround month']!='']

        #print dat2['pe_turnaround']
        #print dat2['pe_turnaround'].isnull().sum()



        kpi_2_list = list(dat2['pe_turnaround'])
        kpi_2_list = [x for x in kpi_2_list if str(x) != 'nan']

        kpi_2_list = list(filter(str, kpi_2_list)) # fastest
        pe_turnaround =  np.median(kpi_2_list)
        pe_to_list = dat2.shape[0]

        del dat2
        dat2 = dat.copy()

        pattern = '|'.join(reporting_period)
        dat2['KPI 3 PO Turnaround month'] = dat2['KPI 3 PO Turnaround month'].fillna('')
        dat2 = dat2[dat2['KPI 3 PO Turnaround month'].str.contains(pattern)]

        dat2 = dat2.drop_duplicates('Order#')
        #print dat2.shape
        #for index, row in dat2.iterrows():
        #    print row['po_turnaround'], type(row['po_turnaround'])



        kpi_3_list = list(dat2['po_turnaround'])
        kpi_3_list = [x for x in kpi_3_list if str(x) != 'nan']

        kpi_3_list = list(filter(str, kpi_3_list)) # fastest
        po_turnaround =  np.median(kpi_3_list)
        po_to_list = dat2.shape[0]
        if surpress.lower() != "yes":
            print('############################## KPI OUTPUTS ##############################')
            print 'date: ' + str(time.strftime("%b %d, %Y"))
            print reporting_period
            print matrix_file
            print
            print("OTIF: %.4f%%" % (ontime) + " or " + str(otif_dict['14 Days or Less']) + ' out of ' + str(otif_total) + " target >= 85%")
            print
            print("PE Turnaround: " + str(pe_turnaround) + " target <= 3 days" + " N=" + str(pe_to_list))
            print
            print("PO Turnaround: " + str(po_turnaround) + " target <= 7 days" + " N=" + str(po_to_list))
            print
            print(
            "FLT: %.4f%%" % (kpi_4_eval) + " total of " + str(len(kpi_4_list)) + " target -12% <= x <= 12% ")

            print
            print("Within Lead Times: %.2f%%" % (kpi5_within) + ' or ' + str(kpi_5_dict['within']) + ' out of ' + str(
                kpi_5_totals) + " target >=75%")
            print
            print('Freight Costs: ' + str(kpi_6_median) + " target median +- 10% N= " + str(len(kpi_6_list)))
            print
            print("Within Freight Costs: %.2f%%" % (kpi_7_eval_pass_per) + ' or ' + str(kpi_7_eval_pass) + ' out of ' + str(kpi_6_list_len) + " target >=75%")

            print('############################## KPI OUTPUTS ##############################')

        otif_final.append(str(ontime)+'%')
        ontime_number.append(otif_dict['14 Days or Less'])
        otif_total_list.append(otif_total)
        pe_time.append(pe_turnaround)
        pe_num.append(pe_to_list)
        po_time.append(po_turnaround)
        po_num.append(po_to_list)
        kpi_4_eval_total.append(str(kpi_4_eval)+'%')
        kpi4_num.append(len(kpi_4_list))
        value.append(val)
        kpi_5_num.append(str(kpi5_within)+'%')
        kpi_5_within.append(kpi_5_dict['within'])
        kpi_5_total_list.append(kpi_5_totals)
        kpi_6_median_list.append(kpi_6_median)
        kpi_6_total_list.append(len(kpi_6_list))
        kpi_7_pass_perc_list.append(str(kpi_7_eval_pass_per )+'%')
        kpi_7_eval_pass_list.append(kpi_7_eval_pass)

    dat_kpi = pd.DataFrame({'Reporting period': months,'OTIF Percent':otif_final,'# ontime orders':ontime_number,
                           'total number of orders KPI 1':otif_total_list,'pe turnaround':pe_time,'number of PEs':pe_num,
                           'po turnaround':po_time,'number of POs': po_num,'kpi 4 flt vs plt':kpi_4_eval_total,
                            'kpi4 number of orders': kpi4_num,'kpi 5': kpi_5_num,'kpi5 in bounds': kpi_5_within,
                            'kpi 5 total number of orders':kpi_5_total_list,'KPI 6 median':kpi_6_median_list,
                            'kpi 6 total number of orders':kpi_6_total_list, 'KPI 7 pass':kpi_7_pass_perc_list,
                            'kpi 7 number of orders within bounds':kpi_7_eval_pass_list,'shipment value':value})

    cols = ['Reporting period','OTIF Percent','# ontime orders','total number of orders KPI 1','pe turnaround',
            'number of PEs','po turnaround','number of POs','kpi 4 flt vs plt','kpi4 number of orders','kpi 5',
            'kpi5 in bounds','kpi 5 total number of orders','KPI 6 median','kpi 6 total number of orders',
            'KPI 7 pass','kpi 7 number of orders within bounds','shipment value']

    dat_kpi = dat_kpi[cols]
    print("--- %s seconds ---" % (time.time() - start_time))

    return dat_kpi


def generate_total_kpi(dat, months= ['2017-01','2017-02','2017-03'],matrix_file=''):
    #KPI 1
    start_time = time.time()

    dat2 = dat.copy()
    reporting_period = months
    pattern = '|'.join(reporting_period)
    dat2['Order Last Delivery Recorded Year - Month'] = dat2['Order Last Delivery Recorded Year - Month'].fillna('')
    dat2 = dat2[dat2['Order Last Delivery Recorded Year - Month'].str.contains(pattern)]
    otif_dict = dat2['COTD Category'].value_counts().to_dict()
    otif_total = float(sum(otif_dict.values()))

    #adding in code to cover situations where dicts are missing keys that are used to calculate
    if '14 Days or Less' not in otif_dict:
        otif_dict['14 Days or Less'] = 0

    ontime = (otif_dict['14 Days or Less']/otif_total)* 100

    #KPI 4
    kpi_4_list = list(dat2['flt_vs_plt'])
    kpi_4_list = [x for x in kpi_4_list if str(x) != 'nan']
    kpi_4_list = [x for x in kpi_4_list if str(x) != 'flt less than 0, check vendor inco and D1 dates']
    #
    #print kpi_4_list
    kpi_4_list = [float(i) for i in kpi_4_list]
    kpi_4_eval = np.median(kpi_4_list)* 100

    #KPI 5
    kpi_5_dict = dat2['KPI 5 FLT var>0'].value_counts().to_dict()
    kpi_5_totals = float(sum(kpi_5_dict.values()))

    if 'within' not in kpi_5_dict:
        kpi_5_dict['within'] = 0

    kpi5_within = (kpi_5_dict['within']/kpi_5_totals)* 100

    def get_kpi_6(year_month):
        month = year_month[-2:]
        year = year_month[:4]
        switch_list = ['01', '02']
        if month in switch_list:
            # print 'hello world'
            if month == '01':
                month_6 = '11'
                year_6 = int(year) - 1
                year_6 = str(year_6)
            if month == '02':
                month_6 = '12'
                year_6 = int(year) - 1
                year_6 = str(year_6)
        else:
            month_6 = int(month) - 2
            month_6 = '0' + str(month_6)
            year_6 = year
        kpi_6_month_year = year_6 + '-' + month_6
        return kpi_6_month_year
    #get kpi 6 months
    kpi_6_period = []
    for item in reporting_period:
        item  = get_kpi_6(item)
        kpi_6_period.append(item)
    del dat2
    dat2 = dat.copy()
    pattern = '|'.join(kpi_6_period)
    dat2['Order Last Delivery Recorded Year - Month'] = dat2['Order Last Delivery Recorded Year - Month'].fillna('')
    dat2 = dat2[dat2['Order Last Delivery Recorded Year - Month'].str.contains(pattern)]
    #KPI 6
    kpi_6_list = []

    # KPI 6
    if 'book_actual_vs_planned' not in list(dat2.columns):
        dat2['book_actual_vs_planned'] = 'nan'

    kpi_6_list = list(dat2['book_actual_vs_planned'])
    kpi_6_list = [x for x in kpi_6_list if str(x) != 'nan']
    #print kpi_6_list
    kpi_6_list = list(filter(str, kpi_6_list)) # fastest
    kpi_6_median = np.median(kpi_6_list)
    #kpi 7
    if len(kpi_6_list) != 0:

        kpi_7_eval_pass_per =  float(sum(i <= 0 for i in kpi_6_list)) / len(kpi_6_list) * 100#how many within
        kpi_7_eval_pass =  int(sum(i <= 0 for i in kpi_6_list))

    if len(kpi_6_list) == 0:
        kpi_7_eval_pass_per = 0.0
        kpi_7_eval_pass = 0.0
    # kpi 2 and 3
    del dat2
    dat2 = dat.copy()
    pattern = '|'.join(reporting_period)
    dat2['KPI 2 PE Turnaround month'] = dat2['KPI 2 PE Turnaround month'].fillna('')
    dat2 = dat2[dat2['KPI 2 PE Turnaround month'].str.contains(pattern)]
    dat2 = dat2.drop_duplicates('PE#')
    #dat2['KPI 2 PE Turnaround month'] = dat2['KPI 2 PE Turnaround month'].fillna('')
    #dat2 = dat2[dat2['KPI 2 PE Turnaround month']!='']
    #print dat2['pe_turnaround']
    #print dat2['pe_turnaround'].isnull().sum()
    kpi_2_list = list(dat2['pe_turnaround'])
    kpi_2_list = [x for x in kpi_2_list if str(x) != 'nan']
    kpi_2_list = list(filter(str, kpi_2_list)) # fastest
    pe_turnaround =  np.median(kpi_2_list)
    pe_to_list = dat2.shape[0]
    del dat2
    dat2 = dat.copy()
    pattern = '|'.join(reporting_period)
    dat2['KPI 3 PO Turnaround month'] = dat2['KPI 3 PO Turnaround month'].fillna('')
    dat2 = dat2[dat2['KPI 3 PO Turnaround month'].str.contains(pattern)]
    dat2 = dat2.drop_duplicates('Order#')
    #print dat2.shape
    #for index, row in dat2.iterrows():
    #    print row['po_turnaround'], type(row['po_turnaround'])
    kpi_3_list = list(dat2['po_turnaround'])
    kpi_3_list = [x for x in kpi_3_list if str(x) != 'nan']
    kpi_3_list = list(filter(str, kpi_3_list)) # fastest
    po_turnaround =  np.median(kpi_3_list)
    po_to_list = dat2.shape[0]

    print('############################## KPI OUTPUTS ##############################')
    print 'date: ' + str(time.strftime("%b %d, %Y"))
    print reporting_period
    print matrix_file
    print
    print("OTIF: %.4f%%" % (ontime) + " or " + str(otif_dict['14 Days or Less']) + ' out of ' + str(otif_total) + " target >= 85%")
    print
    print("PE Turnaround: " + str(pe_turnaround) + " target <= 3 days" + " N=" + str(pe_to_list))
    print
    print("PO Turnaround: " + str(po_turnaround) + " target <= 7 days" + " N=" + str(po_to_list))
    print
    print(
    "FLT: %.4f%%" % (kpi_4_eval) + " total of " + str(len(kpi_4_list)) + " target -12% <= x <= 12% ")
    print
    print("Within Lead Times: %.2f%%" % (kpi5_within) + ' or ' + str(kpi_5_dict['within']) + ' out of ' + str(
        kpi_5_totals) + " target >=75%")
    print
    print('Freight Costs: ' + str(kpi_6_median) + " target median +- 10% N= " + str(len(kpi_6_list)))
    print
    print("Within Freight Costs: %.2f%%" % (kpi_7_eval_pass_per) + ' or ' + str(kpi_7_eval_pass) + ' out of ' + str(
        len(kpi_6_list)) + " target >=75%")
    print('############################## KPI OUTPUTS ##############################')
    print("--- %s seconds ---" % (time.time() - start_time))





def generate_just_kpi_period_dataset(dat, months=['2017-01', '2017-02', '2017-03']):
    # KPI 1
    start_time = time.time()
    reporting_period = months
    dat['KPI 1_4_5'] = 'No'
    dat['KPI 2'] = 'No'
    dat['KPI 3'] = 'No'
    dat['KPI 6_7'] = 'No'

    pattern = '|'.join(reporting_period)
    dat['Order Last Delivery Recorded Year - Month'] = dat['Order Last Delivery Recorded Year - Month'].fillna('')
    #dat = dat[dat['Order Last Delivery Recorded Year - Month'].str.contains(pattern)]

    dat['KPI 1_4_5'] = np.where(dat['Order Last Delivery Recorded Year - Month'].str.contains(pattern), 'Yes', 'No')

    def get_kpi_6(year_month):
        month = year_month[-2:]
        year = year_month[:4]
        switch_list = ['01', '02']
        if month in switch_list:
            # print 'hello world'
            if month == '01':
                month_6 = '11'
                year_6 = int(year) - 1
                year_6 = str(year_6)
            if month == '02':
                month_6 = '12'
                year_6 = int(year) - 1
                year_6 = str(year_6)
        else:
            month_6 = int(month) - 2
            month_6 = '0' + str(month_6)
            year_6 = year
        kpi_6_month_year = year_6 + '-' + month_6
        return kpi_6_month_year
    # get kpi 6 months

    kpi_6_period = []

    for item in reporting_period:
        item = get_kpi_6(item)
        kpi_6_period.append(item)

    pattern = '|'.join(kpi_6_period)
    #dat2 = dat2[dat2['Order Last Delivery Recorded Year - Month'].str.contains(pattern)]

    dat['KPI 6_7'] = np.where(dat['Order Last Delivery Recorded Year - Month'].str.contains(pattern), 'Yes', 'No')


    # kpi 2 and 3
    pattern = '|'.join(reporting_period)
    dat['KPI 2 PE Turnaround month'] = dat['KPI 2 PE Turnaround month'].fillna('')
    dat['KPI 2'] = np.where(dat['KPI 2 PE Turnaround month'].str.contains(pattern), 'Yes', 'No')

    #dat2 = dat2.drop_duplicates('PE#')

    pattern = '|'.join(reporting_period)
    dat['KPI 3 PO Turnaround month'] = dat['KPI 3 PO Turnaround month'].fillna('')
    dat['KPI 3'] = np.where(dat['KPI 3 PO Turnaround month'].str.contains(pattern), 'Yes', 'No')
    #dat2 = dat2.drop_duplicates('Order#')
    # print dat2.shape
    # for index, row in dat2.iterrows():
    #    print row['po_turnaround'], type(row['po_turnaround'])

    dat = dat[(dat['KPI 1_4_5']=="Yes")|(dat['KPI 2']=="Yes")|(dat['KPI 3']=="Yes")|(dat['KPI 6_7']=="Yes")]
    print("--- %s seconds ---" % (time.time() - start_time))

    return dat

