{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "import datetime\n",
    "\n",
    "from dataclasses import dataclass\n",
    "\n",
    "from copy import copy\n",
    "\n",
    "from openpyxl import load_workbook, Workbook\n",
    "from openpyxl.styles import Color, PatternFill\n",
    "from openpyxl.worksheet.table import Table\n",
    "\n",
    "from spielbergerscripts.primitives.product import Product, Bundle\n",
    "from spielbergerscripts.connector import IBMConnector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define some colors\n",
    "\n",
    "yellow = Color(rgb='FFFF00')\n",
    "red = Color(rgb='E6B8B7')\n",
    "orange = Color(rgb='FCD5B4')\n",
    "purple = Color(rgb='CCC0DA')\n",
    "blue = Color(rgb='B8CCE4')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# classes Abpackung, DispoBedarf, Leistungsdaten, Umbau, Vorlage\n",
    "\n",
    "@dataclass\n",
    "class Abpackung:\n",
    "    artikelNummer:  str     # ArtNr\n",
    "    abpackMenge:    int     # in KG\n",
    "    bemerkung:      str\n",
    "    planPos:        int     # Wochenplannummer\n",
    "    bem_ek:         str     # Bemerkung Einkauf\n",
    "    roh1:           str     # Rohstoff 1\n",
    "    roh1_bes:       str     # Rohstoff 1 Bestand\n",
    "    roh2:           str     # Rohstoff 2\n",
    "    roh2_bes:       str     # Rohstoff 2 Bestand\n",
    "\n",
    "    def anzBeutel(self, bundles):\n",
    "        gg = bundles[self.artikelNummer]\n",
    "        return int((self.abpackMenge * 1000) / gg.bagWeight)\n",
    "\n",
    "    def anzGebinde(self, bundles):\n",
    "        gg = bundles[self.artikelNummer]\n",
    "        return int(self.anzBeutel(bundles) / gg.nbrBags)\n",
    "\n",
    "@dataclass\n",
    "class MischBedarf:\n",
    "    artikelNummer:  str     # ArtNr\n",
    "    abpackMenge:    int     # in KG\n",
    "    kw_reich:       float   # KW REICH\n",
    "    bem_ek:         str     # Bemerkung Einkauf\n",
    "    roh1:           str     # Rohstoff 1\n",
    "    roh1_bes:       str     # Rohstoff 1 Bestand\n",
    "    roh2:           str     # Rohstoff 2\n",
    "    roh2_bes:       str     # Rohstoff 2 Bestand\n",
    "\n",
    "@dataclass\n",
    "class DispoBedarf:\n",
    "    artikelNummer:  str     # ArtNr\n",
    "    palettenziel:   int     # Menge / Soll Palette\n",
    "    bestandL00:     int     # Cha BES00\n",
    "    bestandL01:     int     # Cha BES00\n",
    "    bestandL05:     int     # Cha BES05\n",
    "    auftragsBestand:int     # Auf Bes\n",
    "    bestellBestand: int     # Best Bes\n",
    "    durchschnitt:   int     # Durchschnitt Bestellung pro Woche\n",
    "    tuetenAnz:      int     # Anzahl Tüten Lager + HLB\n",
    "    bem_ek:         str     # Bemerkung Einkauf\n",
    "    roh1:           str     # Rohstoff 1\n",
    "    roh1_bes:       str     # Rohstoff 1 Bestand\n",
    "    roh2:           str     # Rohstoff 2\n",
    "    roh2_bes:       str     # Rohstoff 2 Bestand\n",
    "\n",
    "    def bestandFrei(self):\n",
    "        return self.bestandL00 + self.bestandL05 + self.bestellBestand - self.auftragsBestand\n",
    "\n",
    "    def reichweite(self, prodNeu = 0):\n",
    "        return (self.bestandFrei() + prodNeu) / self.durchschnitt\n",
    "\n",
    "    def reichweiteNeu(self, abpackDict, prodDict):\n",
    "        abp = abpackDict.get(self.artikelNummer, None)\n",
    "        if abp is None:\n",
    "            return self.reichweite()\n",
    "\n",
    "        return self.reichweite(abp.anzBeutel(prodDict))\n",
    "\n",
    "@dataclass\n",
    "class Leistungsdaten:\n",
    "    artikelNummer:  str\n",
    "    leistung:       dict    # Beutel / h pro Packerei (Pack1, Pack2, Pack3, Pack4)\n",
    "    modi:           dict    # Produktionsmodus pro Packerei\n",
    "    kurzBez:        str\n",
    "    gebindeGr:      Bundle\n",
    "    palettenMenge:  int\n",
    "    muehle:         bool\n",
    "    muehleMisch:    bool\n",
    "    rezept:         bool\n",
    "    gfProd:         bool\n",
    "    duezi:          bool\n",
    "\n",
    "\n",
    "@dataclass\n",
    "class Umbauzeiten:\n",
    "    pack:   str\n",
    "    modi:   set\n",
    "    umbau:  dict\n",
    "\n",
    "@dataclass\n",
    "class PackereiVorlage:\n",
    "    packerei:   str\n",
    "    beginn:     datetime.datetime\n",
    "    vorlage:    str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# read Vorlage, Leistugnsdaten, Umbauzeiten, Stammdaten, Leistungsdaten\n",
    "\n",
    "def packVorlage(sheet):\n",
    "    vorlage = dict()\n",
    "    vorlage[\"Pack1\"] = PackereiVorlage(\n",
    "        packerei = sheet['A3'].value,\n",
    "        beginn = sheet['B3'].value,\n",
    "        vorlage = sheet['C3'].value,\n",
    "    )\n",
    "    vorlage[\"Pack2\"] = PackereiVorlage (\n",
    "        packerei = sheet['A4'].value,\n",
    "        beginn = sheet['B4'].value,\n",
    "        vorlage = sheet['C4'].value,\n",
    "    )\n",
    "    vorlage[\"Pack3\"] = PackereiVorlage (\n",
    "        packerei = sheet['A5'].value,\n",
    "        beginn = sheet['B5'].value,\n",
    "        vorlage = sheet['C5'].value,\n",
    "    )\n",
    "    vorlage[\"Pack4\"] = PackereiVorlage (\n",
    "        packerei = sheet['A6'].value,\n",
    "        beginn = sheet['B6'].value,\n",
    "        vorlage = sheet['C6'].value,\n",
    "    )\n",
    "\n",
    "    return vorlage\n",
    "\n",
    "def getPackLeistung(artikelNummer):\n",
    "    return LEISTUNGS_DICT.get(artikelNummer, LEISTUNGS_DICT[\"000000\"])  \n",
    "\n",
    "def getWechsel(modus, new_modus, pack):\n",
    "    tWechsel = PROD_STAMM[pack].umbau.get((modus, new_modus), None)\n",
    "    if tWechsel is None:\n",
    "        return 20\n",
    "    else:\n",
    "        return tWechsel\n",
    "\n",
    "def shortName(artikelNummer):\n",
    "    return getPackLeistung(artikelNummer).kurzBez\n",
    "\n",
    "def beutelProH(artikelNummer, packerei):\n",
    "    return getPackLeistung(artikelNummer).leistung[packerei]\n",
    "\n",
    "def readProdStamm(sheet, pack, offset):\n",
    "    modi = []\n",
    "    count = 0\n",
    "    while True:\n",
    "        c = sheet.cell(row=offset + 2 + count, column = 2).value\n",
    "        if c is None:\n",
    "            break\n",
    "        modi.append(c)\n",
    "        count += 1\n",
    "    umbau = dict()\n",
    "    for idxI, modI in enumerate(modi):\n",
    "        for idxJ, modJ in enumerate(modi):\n",
    "            umbau[modI, modJ] = int(sheet.cell(row=offset + 2 + idxJ, column=6+idxI).value)\n",
    "    return Umbauzeiten(\n",
    "        pack,\n",
    "        modi,\n",
    "        umbau\n",
    "    )\n",
    "\n",
    "def produktionStammdaten(sheet):\n",
    "    return {\n",
    "        \"Pack1\": readProdStamm(sheet, \"Pack1\", 1),\n",
    "        \"Pack2\": readProdStamm(sheet, \"Pack2\", 11),\n",
    "        \"Pack3\": readProdStamm(sheet, \"Pack3\", 30),\n",
    "        \"Pack4\": readProdStamm(sheet, \"Pack4\", 36),\n",
    "    }\n",
    "\n",
    "def isSet(field):\n",
    "    if field is None:\n",
    "        return False\n",
    "    if field != 1:\n",
    "        return False\n",
    "    return True\n",
    "\n",
    "def packLeistung(sheet):\n",
    "    leistungs_dict = {}\n",
    "    for row in sheet.iter_rows(min_row=3, max_row=len(sheet['A']), max_col=18, values_only=True):\n",
    "        if row[0] is None:\n",
    "            continue\n",
    "        l = Leistungsdaten(\n",
    "            artikelNummer=row[0],\n",
    "            leistung={\n",
    "                \"Pack1\": int(row[1] if not (row[1] is None or row[1] == \"#N/A\") else 2000),\n",
    "                \"Pack2\": int(row[3] if not (row[3] is None or row[3] == \"#N/A\") else 1000),\n",
    "                \"Pack3\": int(row[5] if not (row[5] is None or row[5] == \"#N/A\") else 800),\n",
    "                \"Pack4\": int(row[7] if not (row[7] is None or row[7] == \"#N/A\") else 60)\n",
    "            },\n",
    "            modi = {\n",
    "                \"Pack1\": row[2],\n",
    "                \"Pack2\": row[4],\n",
    "                \"Pack3\": row[6],\n",
    "                \"Pack4\": row[8]\n",
    "            },\n",
    "            kurzBez=row[9],\n",
    "            gebindeGr=Bundle.fromString(row[10]),\n",
    "            palettenMenge=int(row[11]),\n",
    "            muehle = isSet(row[13]),\n",
    "            muehleMisch = isSet(row[14]),\n",
    "            rezept = isSet(row[15]),\n",
    "            gfProd = isSet(row[16]),\n",
    "            duezi = isSet(row[17])\n",
    "        )\n",
    "        leistungs_dict[l.artikelNummer] = l\n",
    "\n",
    "    return leistungs_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read Dispo\n",
    "\n",
    "def readDispo(sheet, prodStamm, prodDict):\n",
    "    DISPO_DICT = {}\n",
    "    ABPACK_DICT = {}\n",
    "    MISCH_DICT = {}\n",
    "    GEB_GROESSE = {}\n",
    "    HEADER = True\n",
    "    for row in sheet.rows:\n",
    "\n",
    "        if HEADER:                  # skip the header lines\n",
    "            if row[0].value == \"ARTNR\":\n",
    "                HEADER = False\n",
    "            continue\n",
    "        if row[0].value is None:\n",
    "            break\n",
    "\n",
    "        product = prodDict.get(f\"s{row[0].value}\")\n",
    "        if product is None:\n",
    "            product = prodDict.get(f\"g{row[0].value}\")\n",
    "            \n",
    "        if product is None:\n",
    "            print(f\"Product {row[0].value} could neither be found in the spielberger nor gehrsitz database. Skipping!\")\n",
    "            continue\n",
    "\n",
    "        d = DispoBedarf(\n",
    "            artikelNummer=product.number,\n",
    "            palettenziel=row[4].value,\n",
    "            bestandL00=row[5].value,\n",
    "            bestandL01=row[6].value,\n",
    "            bestandL05=row[7].value,\n",
    "            auftragsBestand=row[8].value,\n",
    "            bestellBestand=row[15].value,\n",
    "            durchschnitt=row[17].value,\n",
    "            tuetenAnz= 0 if row[24].value is None else int(float(row[24].value)*1000),\n",
    "            roh1 = row[18].value,\n",
    "            roh1_bes = row[20].value,\n",
    "            roh2 = row[21].value,\n",
    "            roh2_bes = row[22].value,\n",
    "            bem_ek = row[23].value\n",
    "        )\n",
    "\n",
    "        g = Bundle.fromString(row[2].value)\n",
    "\n",
    "        DISPO_DICT[d.artikelNummer] = d\n",
    "        GEB_GROESSE[d.artikelNummer] = g\n",
    "\n",
    "        prodBase = prodStamm.get(product.number, None)\n",
    "\n",
    "        if not (row[25].value is None):\n",
    "            if prodBase is None:\n",
    "                print(f\"Product {product.number} could not be found in the production parameters database. Default parameters would be used for the production planing\")\n",
    "            a = Abpackung(\n",
    "                artikelNummer=product.number,\n",
    "                abpackMenge=int(row[25].value),\n",
    "                bemerkung=\"\",\n",
    "                planPos= 999 if row[29].value is None else int(int(row[29].value)),\n",
    "                roh1 = row[18].value,\n",
    "                roh1_bes = row[20].value,\n",
    "                roh2 = row[21].value,\n",
    "                roh2_bes = row[22].value,\n",
    "                bem_ek = row[23].value\n",
    "            )\n",
    "\n",
    "            ABPACK_DICT[d.artikelNummer]=a\n",
    "\n",
    "        if prodBase is None:\n",
    "            print(f\"Product {product.number} could not be found in the production parameters database. Cannot determine mixing status\")\n",
    "        elif prodBase.muehleMisch == 1:\n",
    "            MISCH_DICT[d.artikelNummer] = MischBedarf(\n",
    "                artikelNummer = d.artikelNummer,\n",
    "                abpackMenge =   int(row[25].value) if not row[25].value is None else 0,\n",
    "                kw_reich =      float(row[16].value),\n",
    "                bem_ek =        row[23].value,\n",
    "                roh1 = row[18].value,\n",
    "                roh1_bes = row[20].value,\n",
    "                roh2 = row[21].value,\n",
    "                roh2_bes = row[22].value)\n",
    "\n",
    "    return DISPO_DICT, ABPACK_DICT, GEB_GROESSE, MISCH_DICT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write Schedule\n",
    "def writeMischDispo(vorlage, dP1, dP2, dP3, dP4, prodDict):\n",
    "    name = f\"MischDispo\"\n",
    "    sheet = vorlage.copy_worksheet(vorlage[\"MischDispo\"])\n",
    "    if name in vorlage.sheetnames:\n",
    "        vorlage.remove(vorlage[name])\n",
    "    vorlage.move_sheet(sheet, 1 - vorlage.index(sheet))\n",
    "    sheet.title = name\n",
    "\n",
    "    counter = 0\n",
    "    for pack, mischDispo in [(\"Pack1\", dP1), (\"Pack2\", dP2), (\"Pack3\", dP3), (\"Pack4\", dP4)]:\n",
    "        if mischDispo is None:\n",
    "            continue\n",
    "\n",
    "        mischDispo = mischDispo[3]\n",
    "\n",
    "        for d in mischDispo.values():\n",
    "            p = prodDict.get(d.artikelNummer)\n",
    "            if p is None:\n",
    "                print(f\"ERROR: Product {p.artikelNummer} not found\")\n",
    "            counter += 1\n",
    "            sheet.append([pack, d.artikelNummer, p.name, p.bundle, d.kw_reich, d.abpackMenge, d.bem_ek, d.roh1, d.roh1_bes, d.roh2, d.roh2_bes])\n",
    "\n",
    "    tab = Table(displayName=\"MischDispo\", ref=f\"A1:K{counter}\")\n",
    "    sheet.add_table(tab)\n",
    "\n",
    "\n",
    "def writeSchedule(vorlage, dispo, pack):\n",
    "    name = f\"Schedule {pack}\"\n",
    "\n",
    "    _dispoDict, abpackDict, gebGroesse, _mischDict = dispo\n",
    "\n",
    "    sheet = vorlage.copy_worksheet(vorlage[\"Schedule\"])\n",
    "    if name in vorlage.sheetnames:\n",
    "        vorlage.remove(vorlage[name])\n",
    "    vorlage.move_sheet(sheet, 1 - vorlage.index(sheet))\n",
    "    sheet.title = name\n",
    "\n",
    "    pas = list(abpackDict.values())\n",
    "    pas.sort(key=lambda pa: pa.planPos)\n",
    "    modus = None\n",
    "    time = 0\n",
    "    for pa in pas:\n",
    "        leistung = getPackLeistung(pa.artikelNummer)\n",
    "        if leistung.artikelNummer == \"000000\":\n",
    "            print(f\"Leistungsdaten für Produkt {pa.artikelNummer} nicht verfügbar!\")\n",
    "            \n",
    "        #print(f\"Trying to pack {pa.artikelNummer} with {beutelProH(pa.artikelNummer, pack)}\")\n",
    "        new_modus = leistung.modi[pack]\n",
    "        if time != 0:\n",
    "            if new_modus is None:\n",
    "                print(f\"Leistungsdaten für Produkt {pa.artikelNummer} auf Packerei {pack} nicht verfügbar!\")\n",
    "                time = writePAProdChange(sheet, time, 60, modus, f\"Unbekannt - Leistungsdaten für {pa.artikelNummer} auf {pack} setzen und Leistungsdaten aktualisieren!\")\n",
    "            else:\n",
    "                delta = getWechsel(modus, new_modus, pack)\n",
    "                time = writePAProdChange(sheet, time, delta, modus, new_modus)\n",
    "\n",
    "        if pa.abpackMenge is None or pa.abpackMenge == 0:\n",
    "            print(f\"Für Produkt {pa.artikelNummer} in Pack {pack} ist keine gültige Abpackmenge eingetragen\")\n",
    "            continue\n",
    "\n",
    "        time = writePASchedule(sheet, pa, leistung, gebGroesse, pack, time)\n",
    "        modus = new_modus\n",
    "    writeEnd(sheet)\n",
    "\n",
    "def writeHeader(sheet):\n",
    "    sheet.cell(row=1, column = 1).value = \"ART.NR.\"\n",
    "    sheet.cell(row=1, column = 2).value = \"ART.BEZ.\"\n",
    "    sheet.cell(row=1, column = 3).value = \"GEBINDE\"\n",
    "    sheet.cell(row=1, column = 4).value = \"MENGE\"\n",
    "    sheet.cell(row=1, column = 5).value = \"GEBINDE\"\n",
    "    sheet.cell(row=1, column = 6).value = \"BEUTEL\"\n",
    "    sheet.cell(row=1, column = 7).value = \"PLANPOS\"\n",
    "    sheet.cell(row=1, column = 8).value = \"BEUTEL/H\"\n",
    "    sheet.cell(row=1, column = 9).value = \"DAUER\"\n",
    "    sheet.cell(row=1, column = 10).value = \"ANMERKUNG\"\n",
    "\n",
    "def writeEnd(sheet):\n",
    "    row = sheet.max_row + 1\n",
    "    sheet.cell(row=row, column = 9).value = \"30\"\n",
    "    sheet.cell(row=row, column = 10).value = \"Schichtende\"\n",
    "\n",
    "def prodTime(nbrBags, performance, min_t = 30):\n",
    "        return max(int(nbrBags * 60 / performance), min_t)\n",
    "\n",
    "def writePASchedule(sheet, pa, l, gebGroeDict, pack, start):\n",
    "    row = sheet.max_row + 1\n",
    "    geb = gebGroeDict[pa.artikelNummer]\n",
    "    t = prodTime(pa.anzBeutel(gebGroeDict), beutelProH(pa.artikelNummer, pack))\n",
    "    sheet.cell(row=row, column = 1).value = pa.artikelNummer\n",
    "    sheet.cell(row=row, column = 2).value = shortName(pa.artikelNummer)\n",
    "    sheet.cell(row=row, column = 3).value = geb.szBundle\n",
    "    sheet.cell(row=row, column = 4).value = pa.abpackMenge\n",
    "    sheet.cell(row=row, column = 5).value = pa.anzGebinde(gebGroeDict)\n",
    "    sheet.cell(row=row, column = 6).value = pa.anzBeutel(gebGroeDict)\n",
    "    sheet.cell(row=row, column = 7).value = pa.planPos\n",
    "    sheet.cell(row=row, column = 8).value = beutelProH(pa.artikelNummer, pack)\n",
    "    sheet.cell(row=row, column = 9).value = t\n",
    "    sheet.cell(row=row, column = 10).value = pa.bemerkung\n",
    "    sheet.cell(row=row, column = 11).value = pa.bem_ek\n",
    "    sheet.cell(row=row, column = 12).value = pa.roh1\n",
    "    sheet.cell(row=row, column = 13).value = pa.roh1_bes\n",
    "    sheet.cell(row=row, column = 14).value = pa.roh2\n",
    "    sheet.cell(row=row, column = 15).value = pa.roh2_bes\n",
    "\n",
    "#    if l.muehleMisch:\n",
    "#        for i in range(1,10):\n",
    "#            sheet.cell(row=row, column=i).fill  = PatternFill(fgColor=purple, fill_type = 'solid')\n",
    "    if l.muehle:\n",
    "        for i in range(1,10):\n",
    "            sheet.cell(row=row, column=i).fill  = PatternFill(fgColor=blue, fill_type = 'solid')\n",
    "\n",
    "    return start + t\n",
    "\n",
    "def writePAProdChange(sheet, start, delta, modFrom, modTo):\n",
    "    row = sheet.max_row + 1\n",
    "    sheet.cell(row=row, column = 9).value = delta\n",
    "    if modFrom != modTo:\n",
    "        sheet.cell(row=row, column = 10).value = f\"Umbau auf {modTo}\"\n",
    "\n",
    "    return start + delta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Workday\n",
    "@dataclass\n",
    "class Workday:\n",
    "    begin:      int\n",
    "    end:        int\n",
    "    breaks:     list\n",
    "\n",
    "    def to_holliday():\n",
    "        return Workday(0, 0, [])\n",
    "\n",
    "    def to_1_schicht_WU(begin, end=None):\n",
    "        return Workday(begin, begin + 9 if end is None else end, [3 * 60, 6 * 60])\n",
    "\n",
    "    def to_1_schicht(begin, end=None):\n",
    "        return Workday(begin, begin + 9.5 if end is None else end, [2.5 * 60, 6 * 60])\n",
    "        \n",
    "    def to_2_schicht(begin, end=None):\n",
    "        return Workday(begin, begin + 18 if end is None else end, [3 * 60, 6.5 * 60, 12 * 60, 15.5 * 60])\n",
    "\n",
    "    def to_2_schicht_one_only(begin, end=None):\n",
    "        return Workday(begin, begin + 9 if end is None else end, [3 * 60, 6.5 * 60])\n",
    "\n",
    "    def to_3_schicht(begin, end=None):\n",
    "        return Workday(begin, begin + 24 if end is None else end, [3*60, 9 * 60, 12.5 * 60, 18 * 60, 21.5 * 60])\n",
    "\n",
    "@dataclass\n",
    "class WTemplate:\n",
    "    template:   str\n",
    "    packName:   str\n",
    "    daySlots:   int\n",
    "    dayStart:   int\n",
    "    days:       tuple\n",
    "\n",
    "    def to_1_schicht(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"1-Schicht-WU\",\n",
    "            packName,\n",
    "            18,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_1_schicht_WU(begin)),\n",
    "                (\"DI\", Workday.to_1_schicht_WU(begin)),\n",
    "                (\"MI\", Workday.to_1_schicht_WU(begin)),\n",
    "                (\"DO\", Workday.to_1_schicht_WU(begin)),\n",
    "                (\"FR\", Workday.to_1_schicht_WU(begin)),\n",
    "                (\"SA\", Workday.to_1_schicht_WU(begin))\n",
    "            ),\n",
    "        )\n",
    "\n",
    "    def to_1_schicht_holiday(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"1-Schicht-WU\",\n",
    "            packName,\n",
    "            18,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_holliday()),\n",
    "                (\"DI\", Workday.to_holliday()),\n",
    "                (\"MI\", Workday.to_holliday()),\n",
    "                (\"DO\", Workday.to_1_schicht_WU(begin)),\n",
    "                (\"FR\", Workday.to_1_schicht_WU(begin)),\n",
    "                (\"SA\", Workday.to_1_schicht_WU(begin))\n",
    "            ),\n",
    "        )\n",
    "        \n",
    "    def to_schicht_long(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"1-Schicht\",\n",
    "            packName,\n",
    "            19,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_1_schicht(begin)),\n",
    "                (\"DI\", Workday.to_1_schicht(begin)),\n",
    "                (\"MI\", Workday.to_1_schicht(begin)),\n",
    "                (\"DO\", Workday.to_1_schicht(begin)),\n",
    "                (\"FR\", Workday.to_1_schicht(begin)),\n",
    "                (\"SA\", Workday.to_1_schicht(begin))\n",
    "            ),\n",
    "        )\n",
    "        \n",
    "    def to_schicht_long_mischungen(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"1-Schicht+Mischungen\",\n",
    "            packName,\n",
    "            19,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_1_schicht(begin)),\n",
    "                (\"DI\", Workday.to_1_schicht(begin)),\n",
    "                (\"MI\", Workday.to_1_schicht(begin)),\n",
    "                (\"DO\", Workday.to_1_schicht(begin)),\n",
    "                (\"FR\", Workday.to_1_schicht(begin)),\n",
    "                (\"SA\", Workday.to_1_schicht(begin))\n",
    "            ),\n",
    "        )\n",
    "\n",
    "    def to_2_schicht(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"2-Schicht\",\n",
    "            packName,\n",
    "            36,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_2_schicht(begin)),\n",
    "                (\"DI\", Workday.to_2_schicht(begin)),\n",
    "                (\"MI\", Workday.to_2_schicht(begin)),\n",
    "                (\"DO\", Workday.to_2_schicht(begin)),\n",
    "                (\"FR\", Workday.to_2_schicht(begin)),\n",
    "                (\"SA\", Workday.to_2_schicht(begin)),\n",
    "            )\n",
    "        )\n",
    "        \n",
    "    def to_2_schicht_one_only(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"2-Schicht\",\n",
    "            packName,\n",
    "            36,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"DI\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"MI\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"DO\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"FR\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"SA\", Workday.to_2_schicht_one_only(begin)),\n",
    "            )\n",
    "        )\n",
    "        \n",
    "    def to_2_schicht_special(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"2-Schicht\",\n",
    "            packName,\n",
    "            36,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"DI\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"MI\", Workday.to_2_schicht_one_only(begin)),\n",
    "                (\"DO\", Workday.to_2_schicht(begin)),\n",
    "                (\"FR\", Workday.to_2_schicht(begin)),\n",
    "                (\"SA\", Workday.to_2_schicht(begin)),\n",
    "            )\n",
    "        )\n",
    "    \n",
    "    def to_3_schicht(begin, packName):\n",
    "        return WTemplate(\n",
    "            \"3-Schicht\",\n",
    "            packName,\n",
    "            48,\n",
    "            begin,\n",
    "            (\n",
    "                (\"MO\", Workday.to_3_schicht(begin)),\n",
    "                (\"DI\", Workday.to_3_schicht(begin)),\n",
    "                (\"MI\", Workday.to_3_schicht(begin)),\n",
    "                (\"DO\", Workday.to_3_schicht(begin)),\n",
    "                (\"FR\", Workday.to_3_schicht(begin)),\n",
    "                (\"SA\", Workday.to_3_schicht(begin)),\n",
    "            )\n",
    "        )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Produktdatenbank aufbauen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@dataclass\n",
    "class ProdDict:\n",
    "    gProdDict: dict()\n",
    "    sProdDict: dict()\n",
    "\n",
    "    def get(self, pnr):\n",
    "        if pnr[0] == \"s\" or len(pnr) == 6:\n",
    "            return self.sProdDict.get(pnr, None)\n",
    "        elif pnr[0] == \"g\":\n",
    "            return self.gProdDict.get(pnr, None)\n",
    "\n",
    "g_ibmconnect = IBMConnector(False)\n",
    "s_ibmconnect = IBMConnector(True)\n",
    "prodDict = ProdDict(\n",
    "    sProdDict= s_ibmconnect.getProductsTable(),\n",
    "    gProdDict= g_ibmconnect.getProductsTable()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Steuerungsdaten"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "KW = 6\n",
    "YEAR = 2022\n",
    "\n",
    "# ACHTUNG: Beim Lesen von der Dispo (FROM_DISPO = True) gehen alle bisherigen Planungen verloren!\n",
    "FROM_DISPO = False   # True oder False\n",
    "\n",
    "P1 = True\n",
    "P2 = True\n",
    "P3 = True\n",
    "P4 = True\n",
    "\n",
    "#DISPO = f\"G:/Allgemein GmbH/Lager und Logistik/Dispo/Dispo Abpackung 2020/Dispo KW {KW-1}.xlsx\"\n",
    "DISPO = f\"Dispo KW {KW-1}.xlsx\"\n",
    "PLANUNG_PATH = f\"Wochenplanung KW {KW:02d}_temp.xlsx\"\n",
    "FINAL_PATH = f\"Wochenplanung KW {KW:02d}.xlsx\"\n",
    "VORLAGE = \"G:/Wochentakt/Packereiplanung/Vorlage/Abpackplanung Vorlage.xlsx\"\n",
    "# G:/Allgemein GmbH/Packerei/Abpackplanung 2021/Abpackplanung Vorlage.xlsx\"\n",
    "VORDEF = VORLAGE\n",
    "\n",
    "WEEK_P1 = WTemplate.to_2_schicht(5, \"Pack1\")\n",
    "WEEK_P2 = WTemplate.to_2_schicht(5, \"Pack2\")\n",
    "WEEK_P3 = WTemplate.to_2_schicht(5, \"Pack3\")\n",
    "WEEK_P4 = WTemplate.to_1_schicht(7, \"Pack4\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Leistungsdaten lesen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "vorlage = load_workbook(filename = VORLAGE, data_only=True)\n",
    "\n",
    "print(\"Read Leistungsdaten\")\n",
    "sheet = vorlage[\"TLeistungsdaten\"]\n",
    "PACK_VORLAGE = packVorlage(sheet)\n",
    "LEISTUNGS_DICT = packLeistung(sheet)\n",
    "#\n",
    "print(\"Read Produktionsstammdaten\")\n",
    "sheet = vorlage[\"ProduktionStammdaten\"]\n",
    "PROD_STAMM = produktionStammdaten(sheet)\n",
    "\n",
    "vorlage.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dispo lesen & Schedule erstellen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# read Schedule from Dispo\n",
    "if FROM_DISPO:\n",
    "    dispo = load_workbook(filename = DISPO, data_only = True, read_only = True)\n",
    "    if P1:\n",
    "        print(\"Reading Dispo P1\") \n",
    "        dispoP1 = readDispo(dispo[\"Dispo Abpackplanung P1\"], LEISTUNGS_DICT, prodDict)\n",
    "        if dispoP1 is None:\n",
    "            print(\".. encountered an error. Skipping!\")\n",
    "    if P2: \n",
    "        print(\"Reading Dispo P2\") \n",
    "        dispoP2 = readDispo(dispo[\"Dispo Abpackplanung P2\"], LEISTUNGS_DICT, prodDict)\n",
    "        if dispoP2 is None:\n",
    "            print(\".. encountered an error. Skipping!\")\n",
    "    if P3: \n",
    "        print(\"Reading Dispo P3\") \n",
    "        dispoP3 = readDispo(dispo[\"Dispo Abpackplanung P3\"], LEISTUNGS_DICT, prodDict)\n",
    "        if dispoP3 is None:\n",
    "            print(\".. encountered an error. Skipping!\")\n",
    "    if P4: \n",
    "        print(\"Reading Dispo P4\") \n",
    "        dispoP4 = readDispo(dispo[\"Dispo Abpackplanung P4\"], LEISTUNGS_DICT, prodDict)\n",
    "        if dispoP4 is None:\n",
    "            print(\".. encountered an error. Skipping!\")\n",
    "    dispo.close()\n",
    "\n",
    "    vorlage = load_workbook(filename = VORDEF, data_only = False, read_only = False)\n",
    "    writeMischDispo(vorlage, dispoP1, dispoP2, dispoP3, dispoP4, prodDict)\n",
    "    if P4: writeSchedule(vorlage, dispoP4, \"Pack4\")\n",
    "    if P3: writeSchedule(vorlage, dispoP3, \"Pack3\")\n",
    "    if P2: writeSchedule(vorlage, dispoP2, \"Pack2\")\n",
    "    if P1: writeSchedule(vorlage, dispoP1, \"Pack1\")\n",
    "    vorlage.save(PLANUNG_PATH)\n",
    "    vorlage.close() \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read update and write Schedule\n",
    "\n",
    "def readSchedule(sheet):\n",
    "    pas = []\n",
    "    for idx, row in enumerate(sheet.rows):\n",
    "        if idx == 0:\n",
    "            continue\n",
    "\n",
    "        # switch between Produktionsauftrag und Umbau / Bemerkung\n",
    "        if row[0].value is None and row[1].value is None:\n",
    "            # Bemerkung / Umbau\n",
    "            pas.append(Bemerkung(\n",
    "                pDauer= int(row[8].value),\n",
    "                bemerkung= row[9].value,\n",
    "                marking = row[0].fill\n",
    "            ))\n",
    "        else: \n",
    "            prod_nr = row[0].value\n",
    "            l = LEISTUNGS_DICT[prod_nr]\n",
    "            pas.append(Produktionsauftrag(\n",
    "                artikelNummer= prod_nr,\n",
    "                artikelBezeichnung= row[1].value,\n",
    "                gebindeGroesse= row[2].value,\n",
    "                abpackMenge= int(row[3].value),\n",
    "                anzGebinde= int(row[4].value),\n",
    "                anzBeutel= int(row[5].value),\n",
    "                pNumber=int(row[6].value),\n",
    "                pLeistung= int(row[7].value),\n",
    "                pDauer= int(row[8].value),\n",
    "                bemerkung= row[9].value,\n",
    "                bem_ek=row[10].value,\n",
    "                roh1=row[11].value,\n",
    "                roh1_bes=row[12].value,\n",
    "                roh2=row[13].value,\n",
    "                roh2_bes=row[14].value,\n",
    "                marking = row[0].fill,\n",
    "                prodDZ = l.duezi,\n",
    "            ))\n",
    "    return pas\n",
    "\n",
    "def writePASchedule2(sheet, pa):\n",
    "    row = sheet.max_row + 1\n",
    "    \n",
    "    sheet.cell(row=row, column = 9).value = pa.pDauer\n",
    "    sheet.cell(row=row, column = 10).value = pa.bemerkung\n",
    "\n",
    "    if isinstance(pa, Produktionsauftrag):\n",
    "        l = getPackLeistung(pa.artikelNummer)\n",
    "        if l.artikelNummer == \"000000\":\n",
    "            print(f\"Leistungsdaten für Produkt {pa.artikelNummer} nicht verfügbar!\")\n",
    "        \n",
    "        sheet.cell(row=row, column = 1).value = pa.artikelNummer\n",
    "        sheet.cell(row=row, column = 2).value = shortName(pa.artikelNummer)\n",
    "        sheet.cell(row=row, column = 3).value = pa.gebindeGroesse\n",
    "        sheet.cell(row=row, column = 4).value = pa.abpackMenge\n",
    "        sheet.cell(row=row, column = 5).value = pa.anzGebinde\n",
    "        sheet.cell(row=row, column = 6).value = pa.anzBeutel\n",
    "        sheet.cell(row=row, column = 7).value = pa.pNumber\n",
    "        sheet.cell(row=row, column = 8).value = pa.pLeistung\n",
    "        sheet.cell(row=row, column = 11).value = pa.bem_ek\n",
    "        sheet.cell(row=row, column = 12).value = pa.roh1\n",
    "        sheet.cell(row=row, column = 13).value = pa.roh1_bes\n",
    "        sheet.cell(row=row, column = 14).value = pa.roh2\n",
    "        sheet.cell(row=row, column = 15).value = pa.roh2_bes\n",
    "        if pa.marking.fgColor.rgb != \"00000000\":\n",
    "            for i in range(1,10):\n",
    "                sheet.cell(row=row, column=i).fill = pa.marking\n",
    "        else:\n",
    "            if l.muehleMisch:\n",
    "                for i in range(2,10):\n",
    "                    sheet.cell(row=row, column=i).fill = PatternFill(fgColor=purple, fill_type = 'solid')\n",
    "            if l.muehle:\n",
    "                for i in range(1,10):\n",
    "                    sheet.cell(row=row, column=i).fill = PatternFill(fgColor=blue, fill_type = 'solid')\n",
    "#            if l.duezi:\n",
    "#                for i in range(1,10):\n",
    "#                    sheet.cell(row=row, column=i).fill = PatternFill(fgColor=orange, fill_type = 'solid')\n",
    "\n",
    "\n",
    "def writeSchedule2(vorlage, pas , pack):\n",
    "    name = f\"Schedule {pack}\"\n",
    "\n",
    "    sheet = vorlage.copy_worksheet(vorlage[\"Schedule\"])\n",
    "    if name in vorlage.sheetnames:\n",
    "        vorlage.remove(vorlage[name])\n",
    "    vorlage.move_sheet(sheet, -vorlage.index(sheet))\n",
    "    sheet.title = name\n",
    "\n",
    "    for pa in pas:\n",
    "        #print(f\"Trying to pack {pa.artikelNummer} with {beutelProH(pa.artikelNummer, pack)}\")\n",
    "        if isinstance(pa, Produktionsauftrag):\n",
    "            if pa.abpackMenge is None or pa.abpackMenge == 0:\n",
    "                print(\"This should not happen ...\")\n",
    "                continue\n",
    "\n",
    "        writePASchedule2(sheet, pa)\n",
    "\n",
    "def updateSchedule(sheet, pack):\n",
    "    pas = []\n",
    "    lastProd = 0\n",
    "\n",
    "    for idx, row in enumerate(sheet.rows):\n",
    "        if idx == 0:\n",
    "            continue\n",
    "\n",
    "        # switch between Produktionsauftrag und Umbau / Bemerkung\n",
    "        if row[0].value is None and row[1].value is None:\n",
    "            # Bemerkung / Umbau\n",
    "            pas.append(Bemerkung(\n",
    "                pDauer= int(row[8].value),\n",
    "                bemerkung= row[9].value,\n",
    "                marking = copy(row[0].fill)\n",
    "            ))\n",
    "        else:\n",
    "            prod = prodDict.get(row[0].value)\n",
    "            if prod is None:\n",
    "                print(f\"Could not find product data of {row[0].value}\")\n",
    "            bundle = Bundle.fromString(prod.bundle)\n",
    "            amount = int(row[3].value)\n",
    "            nbrBags = int(amount * 1000 / bundle.bagWeight) if row[5].value is None else int(row[5].value)\n",
    "            nbrBundles = int(nbrBags / bundle.nbrBags) if row[4].value is None else int(row[4].value)\n",
    "            performance = int(row[7].value)\n",
    "            if performance is None:\n",
    "                l = getPackLeistung(prod.number)\n",
    "                if l.artikelNummer == \"000000\":\n",
    "                    print(f\"Leistungsdaten für Produkt {prod.number} nicht verfügbar!\")\n",
    "                \n",
    "                performance = l.leistung.get(pack)\n",
    "                if performance is None:\n",
    "                    print(f\"Leistungsdaten für Produkt {prod.number} auf Packerei {pack} nicht verfügbar!\")\n",
    "\n",
    "            prodNr = lastProd + 1\n",
    "            if not row[6].value is None:\n",
    "                prodNr = int(row[6].value)\n",
    "            lastProdNr = prodNr\n",
    "        \n",
    "\n",
    "            pas.append(Produktionsauftrag(\n",
    "                artikelNummer= prod.number,\n",
    "                artikelBezeichnung= prod.name,\n",
    "                gebindeGroesse= bundle.szBundle,\n",
    "                abpackMenge= amount,\n",
    "                anzGebinde= nbrBundles,\n",
    "                anzBeutel= nbrBags,\n",
    "                pNumber = prodNr,\n",
    "                pLeistung= performance,\n",
    "                pDauer= prodTime(nbrBags, performance) if row[8].value is None else row[8].value,\n",
    "                bemerkung= row[9].value,\n",
    "                bem_ek = row[10].value,\n",
    "                roh1=row[11].value,\n",
    "                roh1_bes=row[12].value,\n",
    "                roh2=row[13].value,\n",
    "                roh2_bes=row[14].value,\n",
    "                marking = copy(row[0].fill),\n",
    "                prodDZ = prod.pickArea == \"33\"\n",
    "            ))\n",
    "    return pas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Produktionsplanung schreiben\n",
    "\n",
    "nimmt die Schedule und schreibt sie in die Abpackplanungsdatei"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# classes Produktionsauftrag & Bemerkung\n",
    "@dataclass\n",
    "class Produktionsauftrag:\n",
    "    artikelNummer:      str\n",
    "    artikelBezeichnung: str\n",
    "    gebindeGroesse:     str\n",
    "    abpackMenge:        int\n",
    "    anzGebinde:         int\n",
    "    anzBeutel:          int\n",
    "    pNumber:            int\n",
    "    pLeistung:          int\n",
    "    pDauer:             int\n",
    "    bemerkung:          str\n",
    "    bem_ek:             str\n",
    "    roh1:               str     # Rohstoff 1\n",
    "    roh1_bes:           str     # Rohstoff 1 Bestand\n",
    "    roh2:               str     # Rohstoff 2\n",
    "    roh2_bes:           str     # Rohstoff 2 Bestand\n",
    "    marking:            PatternFill\n",
    "    prodDZ:             bool\n",
    "\n",
    "@dataclass\n",
    "class Bemerkung:\n",
    "    pDauer:             int\n",
    "    bemerkung:          str\n",
    "    marking:            PatternFill\n",
    "\n",
    "    def isSimpleProdChange(self):\n",
    "        return self.bemerkung is None or self.bemerkung == \"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write Schedule to factory plan\n",
    "\n",
    "def toTimeString(minutes):\n",
    "    return f\"{int(minutes / 60):2}:{int(minutes % 60):02}\"\n",
    "\n",
    "def toTimeSlotString(minutes):\n",
    "    return f\"{toTimeString(minutes)} - {toTimeString(minutes + 30)}\"\n",
    "\n",
    "\n",
    "def fillWorkday(sheet, kw, year, wtemplate):\n",
    "    pack = wtemplate.packName\n",
    "    workday = wtemplate.days[0][1]\n",
    "    # header\n",
    "    sheet.cell(row=1, column=1).value = f\"Produktionsplanung {pack}\"\n",
    "    now = datetime.datetime.now().strftime(\"%d.%m.%Y: %H:%M\")\n",
    "    sheet.cell(row=wtemplate.daySlots + 14, column=1).value = f\"Stand {now}\"\n",
    "    sheet.cell(row=2, column=2).value = kw\n",
    "    sheet.cell(row=3, column=2).value = year\n",
    "    sheet.cell(row=3, column=1).value = pack\n",
    "\n",
    "    current = workday.begin * 60\n",
    "    row = 4\n",
    "    while current < workday.end * 60:\n",
    "        sheet.cell(row=row, column=2).value = toTimeSlotString(current)\n",
    "        current += 30\n",
    "        if current >= 24*60:\n",
    "            current = 0\n",
    "        row += 1\n",
    "\n",
    "def writeToPlan(sheet, pa, day, startSlot, timeEnd, prodDict, workday):\n",
    "    if isinstance(pa, Produktionsauftrag):\n",
    "        writePA(sheet, pa, day, startSlot, prodDict, workday)\n",
    "    else:\n",
    "        writeComment(sheet, pa, day, startSlot, timeEnd, prodDict, workday)\n",
    "        \n",
    "\n",
    "def writeComment(sheet, pa, day, startSlot, timeEnd, prodDict, workday):\n",
    "    assert isinstance(pa, Bemerkung), \"Trying to write PA as Break\"\n",
    "\n",
    "    if pa.pDauer < 30 or pa.bemerkung is None:\n",
    "        return\n",
    "\n",
    "    columns = { \"MO\": 3, \"DI\": 13, \"MI\":23, \"DO\":33, \"FR\":43, \"SA\":53 }\n",
    "    columnOffset=columns[day]\n",
    "    rowOffset = 4\n",
    "\n",
    "    lastSlot = slot(timeEnd)\n",
    "\n",
    "    sheet.cell(row=rowOffset + startSlot, column=columnOffset+4).value=pa.bemerkung\n",
    "    current = startSlot\n",
    "    while True:\n",
    "        if not slotIsBreak(current, workday):\n",
    "            sheet.cell(row=rowOffset+current, column=columnOffset+4).fill = PatternFill(fgColor=red, fill_type = 'solid')\n",
    "        current += 1\n",
    "        if current >= lastSlot:\n",
    "            break\n",
    "\n",
    "def writePA(sheet, pa, day, startSlot, prodDict, workday):\n",
    "    assert isinstance(pa, Produktionsauftrag), \"Trying to write Break as PA\"\n",
    "    \n",
    "    columns = { \"MO\": 3, \"DI\": 13, \"MI\":23, \"DO\":33, \"FR\":43, \"SA\":53 }\n",
    "    columnOffset=columns[day]\n",
    "    rowOffset = 4\n",
    "    \n",
    "    sheet.cell(row=rowOffset+startSlot, column=columnOffset+0).value = pa.anzBeutel\n",
    "    sheet.cell(row=rowOffset+startSlot, column=columnOffset+1).value = pa.anzGebinde\n",
    "    if pa.artikelNummer is None:\n",
    "        sheet.cell(row=rowOffset+startSlot, column=columnOffset+2).value = pa.gebindeGroesse\n",
    "        sheet.cell(row=rowOffset+startSlot, column=columnOffset+4).value = pa.artikelBezeichnung\n",
    "    else:\n",
    "        sheet.cell(row=rowOffset+startSlot, column=columnOffset+3).value = pa.artikelNummer\n",
    "    sheet.cell(row=rowOffset+startSlot, column=columnOffset+7).value = pa.abpackMenge\n",
    "    prod = prodDict.get(pa.artikelNummer)\n",
    "    sheet.cell(row=rowOffset+startSlot, column=columnOffset+6).value = math.ceil(pa.anzGebinde / prod.palSize) if not prod is None else None\n",
    "    if pa.bemerkung != \"\" and not pa.bemerkung is None and pa.pDauer >= 60:\n",
    "        sheet.cell(row=rowOffset + startSlot + 1, column=columnOffset+4).value=pa.bemerkung\n",
    "        sheet.cell(row=rowOffset + startSlot + 1, column=columnOffset+4).fill = PatternFill(fgColor=yellow, fill_type = 'solid')\n",
    "    if not pa.marking is None:\n",
    "        for i in range(8):\n",
    "            sheet.cell(row=rowOffset+startSlot, column=columnOffset+i).fill = pa.marking\n",
    "    if pa.prodDZ:\n",
    "            sheet.cell(row=rowOffset+startSlot, column=columnOffset+0).fill = PatternFill(fgColor=orange, fill_type = 'solid')\n",
    "            sheet.cell(row=rowOffset+startSlot, column=columnOffset+1).fill = PatternFill(fgColor=orange, fill_type = 'solid')\n",
    "            sheet.cell(row=rowOffset+startSlot, column=columnOffset+2).fill = PatternFill(fgColor=orange, fill_type = 'solid')\n",
    "\n",
    "def writeZeitVerschn(sheet, slotsPerDay, day, uebertragZ):\n",
    "    columns = { \"MO\": 3, \"DI\": 13, \"MI\":23, \"DO\":33, \"FR\":43, \"SA\":53 }\n",
    "    columnOffset=columns[day]\n",
    "    rowOffset = 4\n",
    "    sheet.cell(row=rowOffset+slotsPerDay, column=columnOffset+2).value = uebertragZ\n",
    "\n",
    "            \n",
    "def writeNegUebertrag(sheet, slotsPerDay, day, uebertrag):\n",
    "    columns = { \"MO\": 3, \"DI\": 13, \"MI\":23, \"DO\":33, \"FR\":43, \"SA\":53 }\n",
    "    columnOffset=columns[day]\n",
    "    rowOffset = 4\n",
    "    sheet.cell(row=rowOffset+slotsPerDay-1, column=columnOffset+0).value = -uebertrag\n",
    "    \n",
    "def writePosUebertrag(sheet, slotsPerDay, day, uebertrag):\n",
    "    columns = { \"MO\": 3, \"DI\": 13, \"MI\":23, \"DO\":33, \"FR\":43, \"SA\":53 }\n",
    "    columnOffset=columns[day]\n",
    "    rowOffset = 4\n",
    "    sheet.cell(row=rowOffset, column=columnOffset+0).value = uebertrag\n",
    "    sheet.cell(row=rowOffset, column=columnOffset+1).value = \"Rest\"\n",
    "    sheet.cell(row=rowOffset, column=columnOffset+6).value = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# write Schedule to factory plan 2\n",
    "def slot(time):\n",
    "    return max(0, math.floor(time / 30))\n",
    "\n",
    "def slotIsBreak(s, workday):\n",
    "    breakSlots = set(map(lambda b: slot(b), workday.breaks))\n",
    "\n",
    "    return s in breakSlots\n",
    "\n",
    "def timeAdd(current, add,  breaks):\n",
    "    newTime = current + add\n",
    "    # find the next break\n",
    "    nextBreak = 0\n",
    "    while nextBreak < len(breaks):\n",
    "        if breaks[nextBreak] > current:\n",
    "            break\n",
    "        nextBreak += 1\n",
    "\n",
    "    # next break settled\n",
    "    for b in breaks[nextBreak:-1]:\n",
    "        if b <= newTime:\n",
    "            newTime += 30\n",
    "\n",
    "    return (newTime, breaks[-1] - newTime)\n",
    "\n",
    "\n",
    "def writeAbpackplan(vorlage, wtemplate, schedule, prodDict, p2=False):\n",
    "    days = wtemplate.days\n",
    "    sheet = vorlage.copy_worksheet(vorlage[wtemplate.template])\n",
    "    if wtemplate.packName in vorlage.sheetnames:\n",
    "        vorlage.remove(vorlage[wtemplate.packName])\n",
    "    vorlage.move_sheet(sheet, -vorlage.index(sheet))\n",
    "    sheet.title = wtemplate.packName\n",
    "\n",
    "\n",
    "    time = 15\n",
    "    paIdx = 0\n",
    "    remainingT = 0\n",
    "    uebertrag = None\n",
    "    fillWorkday(sheet, KW, YEAR, wtemplate)\n",
    "    \n",
    "    for day, schicht in wtemplate.days:\n",
    "        if schicht.begin == schicht.end:\n",
    "            # holliday\n",
    "            continue\n",
    "        \n",
    "        dayLength = (schicht.end - schicht.begin) * 60\n",
    "        breaks = schicht.breaks + [(schicht.end - schicht.begin) * 60,]\n",
    "        slotsPerDay = wtemplate.daySlots\n",
    "        time, remainingT = timeAdd(schicht.begin - wtemplate.dayStart, remainingT, breaks)\n",
    "        currentSlot = slot(time)\n",
    "        if not uebertrag is None:\n",
    "            writePosUebertrag(sheet, slotsPerDay, day, uebertrag)\n",
    "            uebertrag = None\n",
    "            \n",
    "            if remainingT < 0:    # running into the next day\n",
    "                uebertragVerhältnis = -remainingT / currentPA.pDauer\n",
    "                uebertragMenge = int(uebertragVerhältnis * currentPA.anzBeutel)\n",
    "\n",
    "                writeNegUebertrag(sheet, slotsPerDay + 1, day, uebertragMenge)\n",
    "                uebertrag = uebertragMenge\n",
    "\n",
    "                remainingT = -remainingT\n",
    "                continue\n",
    "\n",
    "            remainingT = 15 + max(15, remainingT )\n",
    "            \n",
    "\n",
    "        while paIdx < len(schedule):\n",
    "            currentPA = schedule[paIdx]        \n",
    "            if remainingT <= 15 and currentPA.pDauer > 30:\n",
    "                time = 15\n",
    "                break\n",
    "\n",
    "            currentSlot = slot(time)\n",
    "            newtime, remainingT = timeAdd(time, currentPA.pDauer, breaks)\n",
    "\n",
    "            writeToPlan(sheet, currentPA, day, currentSlot, newtime, prodDict, schicht)\n",
    "            lastSlot = currentSlot\n",
    "            \n",
    "            if remainingT < 0:    # running into the next day\n",
    "                if remainingT < -15:\n",
    "                    if type(currentPA) == Produktionsauftrag:\n",
    "                        uebertragVerhältnis = -remainingT / currentPA.pDauer\n",
    "                        uebertragMenge = int(uebertragVerhältnis * currentPA.anzBeutel)\n",
    "\n",
    "                        writeNegUebertrag(sheet, slotsPerDay + 1, day, uebertragMenge)\n",
    "                        uebertrag = uebertragMenge\n",
    "                    remainingT = -remainingT\n",
    "                else:\n",
    "                    uebertrag = None\n",
    "                    remainingT = 0\n",
    "\n",
    "                paIdx += 1\n",
    "                break\n",
    "\n",
    "            paIdx += 1\n",
    "            time = newtime\n",
    "\n",
    "    if paIdx < len(schedule):\n",
    "            print(f\"Week is full!\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Update Schedule"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "vorlage = load_workbook(filename = PLANUNG_PATH, data_only = False, read_only = False)\n",
    "try:\n",
    "    if P4:\n",
    "        print(\"Updating P4 ...\")\n",
    "        scheduleP4 = updateSchedule(vorlage[\"Schedule Pack4\"], \"Pack4\")\n",
    "        writeSchedule2(vorlage, scheduleP4, \"Pack4\")\n",
    "        writeAbpackplan(vorlage, WEEK_P4, scheduleP4, prodDict)\n",
    "    if P3:\n",
    "        print(\"Updating P3 ...\")\n",
    "        scheduleP3 = updateSchedule(vorlage[\"Schedule Pack3\"], \"Pack3\")\n",
    "        writeSchedule2(vorlage, scheduleP3, \"Pack3\")\n",
    "        writeAbpackplan(vorlage, WEEK_P3, scheduleP3, prodDict)\n",
    "    if P2: \n",
    "        print(\"Updating P2 ...\")\n",
    "        scheduleP2 = updateSchedule(vorlage[\"Schedule Pack2\"], \"Pack2\")\n",
    "        writeSchedule2(vorlage, scheduleP2, \"Pack2\")\n",
    "        writeAbpackplan(vorlage, WEEK_P2, scheduleP2, prodDict)\n",
    "    if P1:\n",
    "        print(\"Updating P1 ...\")\n",
    "        scheduleP1 = updateSchedule(vorlage[\"Schedule Pack1\"], \"Pack1\")\n",
    "        writeSchedule2(vorlage, scheduleP1, \"Pack1\")\n",
    "        writeAbpackplan(vorlage, WEEK_P1, scheduleP1, prodDict)\n",
    "\n",
    "    sheet = vorlage[\"Mischungen\"]\n",
    "    sheet[\"B2\"] = KW\n",
    "    sheet[\"B3\"] = YEAR\n",
    "    \n",
    "    print(\"\\nWriting to file ...\")\n",
    "    vorlage.save(PLANUNG_PATH)\n",
    "    print(\"done!\")\n",
    "except PermissionError:\n",
    "    print(f\"Saving the Excel file failed. Is it still open?\")\n",
    "finally:\n",
    "    vorlage.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Produktionsplanung finalisieren"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def finalizeSheet(sheet):\n",
    "    for row in sheet.iter_rows():\n",
    "        for cell in row:\n",
    "            if cell.value == \"#N/A\":\n",
    "                cell.value = None \n",
    "\n",
    "def finalize(vorlage):\n",
    "    for pack in [\"Mischungen\", \"Pack4\", \"Pack3\", \"Pack2\", \"Pack1\"]:\n",
    "    #for pack in [\"Pack3\", \"Pack2\", \"Pack1\"]:\n",
    "        sheet = vorlage[pack]\n",
    "        vorlage.move_sheet(sheet, -vorlage.index(sheet))\n",
    "        finalizeSheet(vorlage[pack])\n",
    "    \n",
    "    sheet = vorlage[\"Mischungen\"]\n",
    "    sheet[\"B2\"] = KW\n",
    "    vorlage.move_sheet(sheet, -vorlage.index(sheet) + 4)\n",
    "    \n",
    "    removeUnused(vorlage)\n",
    "    \n",
    "def removeUnused(vorlage):\n",
    "    vorlage.remove(vorlage[\"Schedule\"])\n",
    "    vorlage.remove(vorlage[\"1-Schicht\"])\n",
    "    vorlage.remove(vorlage[\"1-Schicht-WU\"])\n",
    "    vorlage.remove(vorlage[\"1-Schicht+Mischungen\"])\n",
    "    vorlage.remove(vorlage[\"2-Schicht\"])\n",
    "    vorlage.remove(vorlage[\"3-Schicht\"])\n",
    "    vorlage.remove(vorlage[\"ProduktionStammdaten\"])\n",
    "    vorlage.remove(vorlage[\"TLeistungsdaten\"])\n",
    "    vorlage.remove(vorlage[\"Artikelstamm\"])\n",
    "\n",
    "try:\n",
    "    weekPlan = load_workbook(filename = PLANUNG_PATH, data_only = True, read_only = False)\n",
    "    \n",
    "    finalize(weekPlan)\n",
    "    weekPlan.save(FINAL_PATH)\n",
    "except :\n",
    "    print(f\"Saving the Excel file {FINAL_PATH} failed. Is it still opened?\")\n",
    "finally:\n",
    "    weekPlan.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create List of WG Brackenheim products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl.styles import Border, Side\n",
    "thin = Side(border_style=\"thin\", color=\"000000\")\n",
    "\n",
    "productset = [p for p in scheduleP1 if isinstance(p, Produktionsauftrag)]\n",
    "productset.extend([p for p in scheduleP2 if isinstance(p, Produktionsauftrag)])\n",
    "productset.extend([p for p in scheduleP4 if isinstance(p, Produktionsauftrag)])\n",
    "\n",
    "FINAL = f\"WG Brackenheim KW{KW}.xlsx\"\n",
    "VORLAGE = f\"Dürrenzimmernvorlage.xlsx\"\n",
    "vorlage = load_workbook(VORLAGE, data_only=False, read_only = False)\n",
    "sheet = vorlage.active\n",
    "try:\n",
    "    row = 3\n",
    "    for pa in productset:\n",
    "        prod = prodDict[pa.artikelNummer]\n",
    "        if prod.pickArea != \"33\":\n",
    "            continue\n",
    "        sheet.cell(row = row, column = 1).value = pa.artikelNummer\n",
    "        sheet.cell(row = row, column = 1).border = Border(bottom=thin, top=thin)\n",
    "        sheet.cell(row = row, column = 2).value = pa.artikelBezeichnung\n",
    "        sheet.cell(row = row, column = 2).border = Border(bottom=thin, top=thin)\n",
    "        sheet.cell(row = row, column = 3).value = prod.bundle\n",
    "        sheet.cell(row = row, column = 3).border = Border(bottom=thin, top=thin)\n",
    "        sheet.cell(row = row, column = 4).value = math.ceil(pa.anzGebinde / (prod.palSize * 2 if prod.palSize == 64 else prod.palSize))\n",
    "        sheet.cell(row = row, column = 4).border = Border(bottom=thin, top=thin)\n",
    "        row += 1\n",
    "    vorlage.save(FINAL)\n",
    "finally:\n",
    "    vorlage.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a checklist for bags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PLANUNG_PATH_OLD = f\"Wochenplanung KW {KW-1:02d}_temp.xlsx\"\n",
    "scheduleOld = load_workbook(filename = PLANUNG_PATH_OLD, data_only = False, read_only = False)\n",
    "try:\n",
    "    scheduleP4Old = readSchedule(scheduleOld[\"Schedule Pack4\"])\n",
    "    scheduleP2Old = readSchedule(scheduleOld[\"Schedule Pack2\"])\n",
    "    scheduleP1Old = readSchedule(scheduleOld[\"Schedule Pack1\"])\n",
    "except PermissionError:\n",
    "    print(f\"Reading the Excel file {PLANUNG_PATH_OLD} failed. Is it still open?\")\n",
    "finally:\n",
    "    scheduleOld.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "productset = [p.artikelNummer for p in scheduleP1Old if isinstance(p, Produktionsauftrag)]\n",
    "productset.extend([p.artikelNummer for p in scheduleP2Old if isinstance(p, Produktionsauftrag)])\n",
    "productset.extend([p.artikelNummer for p in scheduleP4Old if isinstance(p, Produktionsauftrag)])\n",
    "\n",
    "HLB_COUNT_PATH = f\"Zaehlliste KW{KW-1}.xlsx\"\n",
    "HLB_VORLAGE_PATH = \"Abruf HLB Vorlage - Lagerplätze.xlsx\"\n",
    "vorlage_hlb = load_workbook(HLB_VORLAGE_PATH, data_only=True, read_only = False)\n",
    "sheet = vorlage_hlb[\"Tüten HLB\"]\n",
    "header = [\"Artikelnr. Tüten\", \"Format\", \"Artikelnr. VP\", \"Bezeichnung\", \"Tütengröße\", \"Menge pro Karton\", \"\", \"Marke\", \"Lagerplatz Reihe\", \"Lagerplatz Position\", \"Bemerkungen\", \"Kartons\", \"Stück\", \"Bestellung Karton\", \"In Stück\"]\n",
    "to_count = set()\n",
    "try:\n",
    "    for row in sheet.iter_rows(min_row=2, max_row=len(sheet['A']), max_col=15, values_only=True):\n",
    "        artNr = row[2]\n",
    "        if artNr in productset:\n",
    "            to_count.add(row)\n",
    "finally:\n",
    "    vorlage_hlb.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hlb_count = Workbook()\n",
    "sheet = hlb_count.active\n",
    "sheet.title = \"HLB Tüten\"\n",
    "try:\n",
    "    sheet.append((header))\n",
    "    to_count_list = list()\n",
    "    to_count_list.extend(to_count)\n",
    "    to_count_list.sort(key = lambda i: i[2])\n",
    "    for row in to_count_list:\n",
    "        sheet.append(row)\n",
    "    \n",
    "    tab = Table(displayName=\"Counting\", ref=f\"A1:O{len(to_count_list)+2}\")\n",
    "\n",
    "    sheet.add_table(tab)\n",
    "    hlb_count.save(HLB_COUNT_PATH)\n",
    "finally:\n",
    "    hlb_count.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "2be5faf79681da6f2a61fdfdd5405d65d042280f7fba6178067603e3a2925119"
  },
  "kernelspec": {
   "display_name": "Python 3.10.0 64-bit",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.2"
  },
  "metadata": {
   "interpreter": {
    "hash": "032eb40c1682b1e6109824d577ff4427b0bbc2f8ebe1487b7b1be524c4843266"
   }
  },
  "orig_nbformat": 2
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
