larsmrx

AutoEoS

Nov 5th, 2019
177
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 6.31 KB | None | 0 0
  1. import os
  2. import datetime as dt
  3. from datetime import timedelta
  4. import json
  5. from openpyxl import Workbook, load_workbook
  6. import openpyxl.styles
  7. from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
  8. from openpyxl.styles import NamedStyle
  9.  
  10. with open('doc3.json') as file:
  11.     data = json.load(file)
  12.  
  13.  
  14. def readableTime(time, action):
  15.     origin = dt.datetime(1970, 1, 1, 0, 0) + timedelta(hours=action)
  16.     pickupTime = origin + timedelta(milliseconds=time)
  17.     if dt.datetime.today() < dt.datetime(2020, 3, 29, 0, 0):
  18.         pickupTime += timedelta(hours=-1)
  19.     return dt.datetime.strftime(pickupTime, "%H:%M")
  20.  
  21.  
  22. def loggingType(delivery):
  23.     deli, ret, rej = 0, 0, 0
  24.     for item in delivery['packagesList']:
  25.         if item['status'] == "DELIVERED":
  26.             deli += 1
  27.         if item['status'] == "RETURNED":
  28.             ret += 1
  29.         if item['status'] == "REJECTED":
  30.             rej += 1
  31.     if ret == 0 and rej == 0:
  32.         return "Late Delivery"
  33.     elif rej > deli and rej > ret:
  34.         return "Undelivered- Rejected"
  35.     elif ret > deli and ret > rej:
  36.         return "Undelivered- Attempted"
  37.     elif 0 not in (deli, rej, ret):
  38.         return "Partial Deliveries"
  39.            
  40.  
  41.  
  42.  
  43. def packages(i):#       creating packages list with their numbers, status' and reason codes - extracted from loaded json file
  44.         packagessList = []
  45.         for pkg, sendung in enumerate(data[i]['destinationStop']['packages']):
  46.             nr = pkg
  47.             package = {}
  48.             package['number'] = data[i]['destinationStop']['packages'][pkg]['packageInfo']['scannableId']
  49.             package['status'] = data[i]['destinationStop']['packages'][pkg]['packageInfo']['packageStatus']
  50.             package['statusReason'] = data[i]['destinationStop']['packages'][pkg]['packageInfo']['statusReasonCode']
  51.             packagessList.append(package)
  52.         return packagessList
  53.  
  54. def packageCounter(i):# counting packages in the record
  55.     count = 0
  56.     for pak in data[i]['destinationStop']['packages']:
  57.         count += 1
  58.     count - 1
  59.     return count
  60.  
  61. def reasoning(i):
  62.     if len(data[i]['destinationStop']['packages']) == 1:
  63.                 data[i]['destinationStop']['packages'][0]['packageInfo']['statusReasonCode']
  64.     else:
  65.         print("more than one package - check delivery.packages() function")
  66.  
  67. '''    
  68.  
  69. class Delivery():
  70.    def __init__(self, tw, pickedup, completed, risk, packages, packageCount, reason, idNum, driver):
  71.        self.tw = tw
  72.        self.pickedup = pickedup
  73.        self.completed = completed
  74.        self.risk = risk
  75.        self.packages = packages
  76.        self.packageCount = packageCount
  77.        self.reason = reason
  78.        self.idNum = idNum
  79.        self.driver = driver
  80. '''
  81.  
  82.  
  83. misses = []
  84. for i, delivries in enumerate(data):
  85.     delivery = {}
  86.     delivery['tw'] = readableTime(data[i]['packageGroup']['deliveryWindow']['startDate'], 2) + " - " + readableTime(data[i]['packageGroup']['deliveryWindow']['endDate'], 2)
  87.     delivery['pickedup'] = readableTime(data[i]['pickupStop']['stopCompletedTime'], 2)
  88.     delivery['completed'] = readableTime(data[i]['destinationStop']['stopCompletedTime'], 2)
  89.     delivery['risk'] = data[i]['riskStatus']
  90.     delivery['packagesList'] = packages(i)
  91.     delivery['packageCount'] = packageCounter(i)
  92.     delivery['reason'] = data[i]['destinationStop']['packages'][0]['packageInfo']['statusReasonCode']
  93.     delivery['idNum'] = data[i]['destinationStop']['packages'][0]['packageInfo']['clientOrderId']
  94.     delivery['driver'] = data[i]['transporter']['transporterName']
  95.     misses.append(delivery)
  96.  
  97.  
  98. for delivery in misses:
  99.     for (k, v) in zip(delivery.keys(), delivery.values()):
  100.         print(k, v)
  101. #font = Font(name='Calibri', size=22, bold=False, italic=False, vertAlign='baseline', underline='none', strike=False, color='9c6500')
  102.  
  103. # Defining misses field style - background color, alignment, etc
  104. thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),bottom=Side(style='thin'))
  105. 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)
  106.  
  107. print(loggingType(misses[0]))
  108. wb = load_workbook("eos_template.xlsm", keep_vba=True)
  109. ws = wb.active
  110. for cell, delivery in enumerate(misses, start=1):
  111.     ws['A' + str(18+cell)] = delivery['tw']
  112.     ws['A' + str(18+cell)].style = misses_field_style
  113.     ws['D' + str(18+cell)] = delivery['idNum']
  114.     ws['D' + str(18+cell)].style = misses_field_style
  115.     ws['G' + str(18+cell)] = loggingType(misses[cell-1])
  116.     ws['G' + str(18+cell)].style = misses_field_style
  117.     ws['H' + str(18+cell)] = "SyLo"
  118.     ws['H' + str(18+cell)].style = misses_field_style
  119.     ws['M' + str(18+cell)] = str(delivery['completed']) + " " + str(delivery['reason'])
  120.     ws['M' + str(18+cell)].style = misses_field_style
  121.  
  122. thicc_green_border = Border(top=Side(style='thin', color='00b050'), bottom = Side(style='thick', color='00b050'))
  123. # title style group
  124. title_data_cell_alignment = Alignment(horizontal='center', vertical='center')
  125. title_font = Font(name="Calibri", size=22, color="9c6500")
  126. title_fill = PatternFill(fgColor ="FFeb9c", fill_type='solid')
  127. date_title_cell_a1 = NamedStyle(name="datea1", font=title_font, fill=title_fill, alignment=title_data_cell_alignment, border=thicc_green_border)
  128.  
  129.  
  130. # blackout cell style
  131. blackout = NamedStyle(name="blackout", fill=PatternFill(fgColor='000000', fill_type='solid'))
  132. # percent cell format
  133.  
  134. #procent = NamedStyle(name='procent', number_format='00.00%')
  135.  
  136.  
  137. ws["A1"] = dt.datetime.strftime(dt.datetime.today(), "%a %d %b")
  138. ws['a1'].style = date_title_cell_a1 #= PatternFill(bgColor="ffeb9c", fill_type="solid")
  139. ws['c1'].style = date_title_cell_a1 # = PatternFill(bgColor="ffeb9c", fill_type="solid")
  140. ws['C6'].number_format = "0.00%"
  141. ws["J6"].number_format = "0.00%"
  142. ws["J8"].number_format = "0.00%"
  143. ws['B4'].fill = PatternFill(bgColor="000000", fill_type="solid")
  144. patternFill  = PatternFill(bgColor="000000", fill_type="solid")
  145. for row in ws['h4:h7']:
  146.     for cell in row:
  147.         ws['H' + str(cell.row)].style = blackout
  148. ws['B6'].number_format = "0.00%"
  149. #ws['H'].fill = PatternFill(bgColor="000000", fill_type="solid")
  150. wb.save("EoS.xlsm")
Advertisement
Add Comment
Please, Sign In to add comment