Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import os
- import datetime as dt
- from datetime import timedelta
- import json
- from openpyxl import Workbook, load_workbook
- import openpyxl.styles
- from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
- from openpyxl.styles import NamedStyle
- with open('doc3.json') as file:
- data = json.load(file)
- def readableTime(time, action):
- origin = dt.datetime(1970, 1, 1, 0, 0) + timedelta(hours=action)
- pickupTime = origin + timedelta(milliseconds=time)
- if dt.datetime.today() < dt.datetime(2020, 3, 29, 0, 0):
- pickupTime += timedelta(hours=-1)
- return dt.datetime.strftime(pickupTime, "%H:%M")
- def loggingType(delivery):
- deli, ret, rej = 0, 0, 0
- for item in delivery['packagesList']:
- if item['status'] == "DELIVERED":
- deli += 1
- if item['status'] == "RETURNED":
- ret += 1
- if item['status'] == "REJECTED":
- rej += 1
- if ret == 0 and rej == 0:
- return "Late Delivery"
- elif rej > deli and rej > ret:
- return "Undelivered- Rejected"
- elif ret > deli and ret > rej:
- return "Undelivered- Attempted"
- elif 0 not in (deli, rej, ret):
- return "Partial Deliveries"
- def packages(i):# creating packages list with their numbers, status' and reason codes - extracted from loaded json file
- packagessList = []
- for pkg, sendung in enumerate(data[i]['destinationStop']['packages']):
- nr = pkg
- package = {}
- package['number'] = data[i]['destinationStop']['packages'][pkg]['packageInfo']['scannableId']
- package['status'] = data[i]['destinationStop']['packages'][pkg]['packageInfo']['packageStatus']
- package['statusReason'] = data[i]['destinationStop']['packages'][pkg]['packageInfo']['statusReasonCode']
- packagessList.append(package)
- return packagessList
- def packageCounter(i):# counting packages in the record
- count = 0
- for pak in data[i]['destinationStop']['packages']:
- count += 1
- count - 1
- return count
- def reasoning(i):
- if len(data[i]['destinationStop']['packages']) == 1:
- data[i]['destinationStop']['packages'][0]['packageInfo']['statusReasonCode']
- else:
- print("more than one package - check delivery.packages() function")
- '''
- class Delivery():
- def __init__(self, tw, pickedup, completed, risk, packages, packageCount, reason, idNum, driver):
- self.tw = tw
- self.pickedup = pickedup
- self.completed = completed
- self.risk = risk
- self.packages = packages
- self.packageCount = packageCount
- self.reason = reason
- self.idNum = idNum
- self.driver = driver
- '''
- misses = []
- for i, delivries in enumerate(data):
- delivery = {}
- delivery['tw'] = readableTime(data[i]['packageGroup']['deliveryWindow']['startDate'], 2) + " - " + readableTime(data[i]['packageGroup']['deliveryWindow']['endDate'], 2)
- delivery['pickedup'] = readableTime(data[i]['pickupStop']['stopCompletedTime'], 2)
- delivery['completed'] = readableTime(data[i]['destinationStop']['stopCompletedTime'], 2)
- delivery['risk'] = data[i]['riskStatus']
- delivery['packagesList'] = packages(i)
- delivery['packageCount'] = packageCounter(i)
- delivery['reason'] = data[i]['destinationStop']['packages'][0]['packageInfo']['statusReasonCode']
- delivery['idNum'] = data[i]['destinationStop']['packages'][0]['packageInfo']['clientOrderId']
- delivery['driver'] = data[i]['transporter']['transporterName']
- misses.append(delivery)
- for delivery in misses:
- for (k, v) in zip(delivery.keys(), delivery.values()):
- print(k, v)
- #font = Font(name='Calibri', size=22, bold=False, italic=False, vertAlign='baseline', underline='none', strike=False, color='9c6500')
- # Defining misses field style - background color, alignment, etc
- thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),bottom=Side(style='thin'))
- misses_field_style = NamedStyle(name='missesfield', fill= PatternFill(fgColor="ffeb9c", fill_type ="solid"), font=Font(name='Calibri', size=11), alignment= Alignment(horizontal='center', vertical='center', wrap_text=True), border=thin_border)
- print(loggingType(misses[0]))
- wb = load_workbook("eos_template.xlsm", keep_vba=True)
- ws = wb.active
- for cell, delivery in enumerate(misses, start=1):
- ws['A' + str(18+cell)] = delivery['tw']
- ws['A' + str(18+cell)].style = misses_field_style
- ws['D' + str(18+cell)] = delivery['idNum']
- ws['D' + str(18+cell)].style = misses_field_style
- ws['G' + str(18+cell)] = loggingType(misses[cell-1])
- ws['G' + str(18+cell)].style = misses_field_style
- ws['H' + str(18+cell)] = "SyLo"
- ws['H' + str(18+cell)].style = misses_field_style
- ws['M' + str(18+cell)] = str(delivery['completed']) + " " + str(delivery['reason'])
- ws['M' + str(18+cell)].style = misses_field_style
- thicc_green_border = Border(top=Side(style='thin', color='00b050'), bottom = Side(style='thick', color='00b050'))
- # title style group
- title_data_cell_alignment = Alignment(horizontal='center', vertical='center')
- title_font = Font(name="Calibri", size=22, color="9c6500")
- title_fill = PatternFill(fgColor ="FFeb9c", fill_type='solid')
- date_title_cell_a1 = NamedStyle(name="datea1", font=title_font, fill=title_fill, alignment=title_data_cell_alignment, border=thicc_green_border)
- # blackout cell style
- blackout = NamedStyle(name="blackout", fill=PatternFill(fgColor='000000', fill_type='solid'))
- # percent cell format
- #procent = NamedStyle(name='procent', number_format='00.00%')
- ws["A1"] = dt.datetime.strftime(dt.datetime.today(), "%a %d %b")
- ws['a1'].style = date_title_cell_a1 #= PatternFill(bgColor="ffeb9c", fill_type="solid")
- ws['c1'].style = date_title_cell_a1 # = PatternFill(bgColor="ffeb9c", fill_type="solid")
- ws['C6'].number_format = "0.00%"
- ws["J6"].number_format = "0.00%"
- ws["J8"].number_format = "0.00%"
- ws['B4'].fill = PatternFill(bgColor="000000", fill_type="solid")
- patternFill = PatternFill(bgColor="000000", fill_type="solid")
- for row in ws['h4:h7']:
- for cell in row:
- ws['H' + str(cell.row)].style = blackout
- ws['B6'].number_format = "0.00%"
- #ws['H'].fill = PatternFill(bgColor="000000", fill_type="solid")
- wb.save("EoS.xlsm")
Advertisement
Add Comment
Please, Sign In to add comment