1485 lines
58 KiB
Plaintext
1485 lines
58 KiB
Plaintext
{
|
|
"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
|
|
}
|