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')