Spaces:
Runtime error
Runtime error
File size: 7,243 Bytes
a24c4bf decc247 a24c4bf b95b7f1 a24c4bf |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 |
from datetime import datetime, timedelta
from openpyxl import load_workbook
import openpyxl as pyxl
class Brw():
def __init__(self,bNo,brnd,fltr,frm,t):
self.brewNo=bNo
self.brand=brnd
# self.mixer
self.fltr=fltr
self.cookStart=t[0]
self.mixStart=t[1]
self.filtStart=t[2]
self.ktlStart=t[3]
self.ktlFull=t[4]
self.wrtclStart=t[5]
self.wrtclEnd=t[6]
def Gantiffy(brwSchd):
wb = load_workbook(filename = brwSchd,data_only=True)
#Generate objects
ws=wb['Schedule']
brews={}
i=6
while ws['E'+str(i)].value!=None:
t=[] #to grab times
bNo=ws['C'+str(i)].value
brnd=ws['E'+str(i)].value
fltr=ws['F'+str(i)].value
frm=ws['G'+str(i)].value
for col in ('P','Q','R','S','T','U','V'):
t.append(ws[col+str(i)].value)
brews[bNo]=Brw(bNo,brnd,fltr,frm,t)
i+=1 #increment so no inf loop
#-------------------
#Column widths
#Generate a big list of all column names AAA-ZZZ in excel and set the column width to 1 pt for each
master=[]
alph=[chr(65+i) for i in range(26)]
master.extend(alph)
alph1=[[chr(65+i)+l for l in alph] for i in range(26)]
ls=[]
for l in alph1:
ls.extend(l)
master.extend(l)
alph2=[[chr(65+i)+l for l in ls] for i in range(26)]
alph2
ls=[]
for l in alph2:
ls.extend(l)
master.extend(l)
for c in master[1:1500]:
ws.column_dimensions[c].width =1.5
#------------------
#Set time step, in minutes, and print timeline
ts=10
i=0 #Pull first brew with below statement to get the first time needs to be on schedule
st=min(brews[list(brews.keys())[i]].cookStart,brews[list(brews.keys())[i]].mixStart)
# sheet["A3"].alignment = Alignment(text_rotation=90)
#Print starting value, starting from top of the hour
#-------------------
#Generate dictionary containing column id (number or letters?) keyed by datetime for that column at same time as printout
timeCol={}
for i in range(1420): #720 10 min chunks in 5 days
tVal=datetime(st.year,st.month,st.day,st.hour)+timedelta(minutes=10*i)
timeCol[tVal]=i+2 #Column Number 2 for iteration 0, etc
#Print the half hours and hours
if tVal.minute in (20,30):ws.cell(column=i+2,row=2).value="-" #Print '--' on the middle of the hour
elif tVal.minute==0:ws.cell(column=i+2,row=2).value="'"+str(tVal.hour) #Print the hour
if tVal.hour==0 and tVal.minute==0: #Print the weekday name
v=tVal.weekday()
if v==0: ws.cell(column=i+2,row=1).value="Monday"
elif v==1: ws.cell(column=i+2,row=1).value="Tuesday"
elif v==2: ws.cell(column=i+2,row=1).value="Wednesday"
elif v==3: ws.cell(column=i+2,row=1).value="Thursday"
elif v==4: ws.cell(column=i+2,row=1).value="Friday"
elif v==5: ws.cell(column=i+2,row=1).value="Saturday"
elif v==6: ws.cell(column=i+2,row=1).value="Sunday"
def styleCell(cl,val):
if val==0:
cl.font=pyxl.styles.Font(bold=True,size=14)
cl.alignment=pyxl.styles.Alignment(horizontal='center')
cl.fill=pyxl.styles.PatternFill(fill_type="solid",start_color='0092D050',end_color='0092D050')
cl.border=pyxl.styles.Border(left=pyxl.styles.Side(border_style='thin'),
right=pyxl.styles.Side(border_style='thin'),
top=pyxl.styles.Side(border_style='thin'),
bottom=pyxl.styles.Side(border_style='thin'))
else:
cl.fill=pyxl.styles.PatternFill(fill_type="solid",start_color='00B0F0',end_color='00B0F0')
cl.font=pyxl.styles.Font(bold=True,size=14,color="00FFFFFF")
cl.alignment=pyxl.styles.Alignment(horizontal='center')
cl.border=pyxl.styles.Border(left=pyxl.styles.Side(border_style='thin'),
right=pyxl.styles.Side(border_style='thin'),
top=pyxl.styles.Side(border_style='thin'),
bottom=pyxl.styles.Side(border_style='thin'))
#-------------------
#Print brew info to the cell on each row where it starts,
#and merge that cell appropriately to the right.
#Round the previous process down to nearest 10 min and next process up to ensure no conflcit when the schedule makes them very tight
def d_key(tm):#d for round DOWN
#Given time from a brew object, returns that time rounded DOWN to the nearest 10 minute,
#corresponding to key for finding which column to print to
tm = tm - timedelta(minutes=tm.minute % 10,seconds=tm.second,microseconds=tm.microsecond)
return tm
def u_key(tm):#u for round UP
#returns key for slot rounded up.
discard = timedelta(minutes=tm.minute % 10,seconds=tm.second,microseconds=tm.microsecond)
tm -= discard
# if discard >= timedelta(minutes=5): #Include this statement and indent next to make it classic rounding
tm += timedelta(minutes=10)
return tm
class mm1():
def __init__(self):
self.log=[]
def sched(self,st,en):
self.log.append((st,en))
def busy(self,st,en):
for r in self.log:
if r[0]<st<r[1]:
return True
if r[0]<en<r[1]:
return True
if st<r[0] and r[1]<en:
return True
return False
mm=mm1()
for brN in brews.keys():
b=brews[brN]
#Cereal Cooker - assume only used if cook time is diff from mix time
r=3
if d_key(b.cookStart)!=d_key(b.mixStart):
ws.cell(column=timeCol[u_key(b.cookStart)],row=r).value=str(b.brewNo) +b.brand[:5]
ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.cookStart)], end_row=r, end_column=timeCol[d_key(b.mixStart)])
styleCell(ws.cell(column=timeCol[u_key(b.cookStart)],row=r),brN%2)
#Mixer
if mm.busy(b.mixStart,b.filtStart):r=5
else:
r=4
mm.sched(b.mixStart,b.filtStart)
ws.cell(column=timeCol[u_key(b.mixStart)],row=r).value=str(b.brewNo) +b.brand[:5]
ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.mixStart)], end_row=r, end_column=timeCol[d_key(b.filtStart)])
styleCell(ws.cell(column=timeCol[u_key(b.mixStart)],row=r),brN%2)
#Filter - choose row based on
if fltr=='1-MF':r=6
elif fltr=='2-MF':r=7
else:r=8
ws.cell(column=timeCol[u_key(b.filtStart)],row=r).value=str(b.brewNo) +b.brand[:5]
ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.filtStart)], end_row=r, end_column=timeCol[d_key(b.ktlFull)])
styleCell(ws.cell(column=timeCol[u_key(b.filtStart)],row=r),brN%2)
#Kettle
r=9
ws.cell(column=timeCol[u_key(b.ktlStart)],row=r).value=str(b.brewNo) +b.brand[:5]
ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.ktlStart)], end_row=r, end_column=timeCol[d_key(b.wrtclStart)])
styleCell(ws.cell(column=timeCol[u_key(b.ktlStart)],row=r),brN%2)
#Cool
r=10
ws.cell(column=timeCol[u_key(b.wrtclStart)],row=r).value=str(b.brewNo) +b.brand[:5]
ws.merge_cells(start_row=r, start_column=timeCol[u_key(b.wrtclStart)], end_row=r, end_column=timeCol[d_key(b.wrtclEnd)])
styleCell(ws.cell(column=timeCol[u_key(b.wrtclStart)],row=r),brN%2)
wb.save(filename = 'gantt.xlsx') |