{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "from spielbergerscripts.connector import IBMConnector\n", "from spielbergerscripts.primitives.batch import Batch\n", "from spielbergerscripts.primitives.nve import NVE\n", "from spielbergerscripts.primitives.product import Product, Bundle\n", "from spielbergerscripts.helpers.functions import spiel_prefix\n", "\n", "from dataclasses import dataclass" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "ibmconnect = IBMConnector(True)\n", "nveDict = ibmconnect.getNVETable()\n", "prodDict = ibmconnect.getProductsTable()\n", "batchDict = ibmconnect.getBatchData()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "@dataclass\n", "class ProductArea:\n", " prod_nr: str\n", " area: str\n", " amount: int\n", " batches: list()\n", "\n", " def from_batch(batch):\n", " return ProductArea(\n", " batch.art_nr, batch.area, 0, list()\n", " )\n", "\n", " def add_batch(self, batch):\n", " assert(batch.area == batch.area)\n", "\n", " self.amount += batch.amount\n", " self.batches.append(batch)\n", "\n", "\n", "@dataclass\n", "class ProductAreas:\n", " prod_nr: str\n", " areas: dict()\n", "\n", " def addBatch(self, batch):\n", " parea = self.areas.get(batch.area, ProductArea.from_batch(batch))\n", " parea.add_batch(batch)\n", "\n", " self.areas[batch.area] = parea\n", "\n", "prod_areas = dict()\n", "for prod_nr, batches in batchDict.items():\n", " prod = prodDict.get(prod_nr, None)\n", " if prod is None:\n", " # print(f\"Error on batch {batch}\")\n", " continue\n", "\n", " pa = ProductAreas(prod_nr, dict())\n", " for batch in batches:\n", " if batch.amount > 0 and (int(batch.vq) < 30 or int(batch.vq) == 39):\n", " pa.addBatch(batch)\n", "\n", " prod_areas[pa.prod_nr] = pa" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "ART_EXT = dict()\n", "\n", "\n", "for prodarea in prod_areas.values():\n", " prod = prodDict[prodarea.prod_nr]\n", " if prod.bundle.strip() == \"25 KG\" or prod.bundle.strip() == \"20 KG\" or prod.bundle.strip() == \"BIG-BAG\" or prod.bundle.strip() == \"1000 ST\" or prod.bundle.strip() == \"LOSE\":\n", " continue\n", "\n", " ext_res = []\n", " for aid, area in prodarea.areas.items():\n", " if aid == prod.pickArea or (aid != \"00\" and aid != \"33\" and aid != \"36\"):\n", " continue\n", " if area.amount > 0:\n", " ext_res.append((aid, area))\n", " print(f\"Lager {aid}\\t{area.amount}x {prod.name.strip()} (#{prodarea.prod_nr}) {prod.bundle} auf Vorrat\")\n", "\n", " if ext_res:\n", " ART_EXT[prodarea.prod_nr] = ext_res\n", "\n", "L_ART_EXT = list(ART_EXT.keys())\n", "L_ART_EXT.sort()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# IBM 11/09 - Negative Verfügbarkeit ausführen!" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "\n", "from openpyxl import load_workbook\n", "from openpyxl.styles import Color, PatternFill\n", "\n", "from dataclasses import dataclass\n", "from datetime import date, datetime, timedelta\n", "\n", "from spielbergerscripts.helpers.functions import spiel_prefix\n", "\n", "@dataclass\n", "class Request:\n", " ordernr: str\n", " cust_name: str\n", " delivdate: date\n", " artnr: str\n", " artname: str\n", " bundle: str\n", " stock: int\n", " ordered: int\n", " ord_rem: int\n", " available: int\n", " avail_wo_36:int\n", " upcoming: int\n", " upc_date: str\n", " upc_area: str\n", " comm_area: str\n", " stock00: int\n", " stock01: int\n", " stock33: int\n", " stock36: int\n", "\n", " def from_tuple(t):\n", " d = str(t[2])\n", " szd = f\"20{d[-2:]}-{d[-4:-2]}-{int(d[:-4]):02d}\"\n", " return Request(\n", " ordernr=str(t[0]),\n", " cust_name=str(t[1]).strip(),\n", " delivdate=date.fromisoformat(szd),\n", " artnr=spiel_prefix(str(t[3]), True),\n", " artname=str(t[4]),\n", " bundle=str(t[5]),\n", " stock=int(t[6]),\n", " ordered=int(t[7]),\n", " ord_rem=int(t[23]),\n", " available=int(t[9]),\n", " avail_wo_36=int(t[28]),\n", " upcoming=int(t[10]),\n", " upc_date=date.fromisoformat(f\"20{str(t[11])[-2:]}-{str(t[11])[-4:-2]}-{int(str(t[11])[:-4]):02d}\") if int(t[10]) > 0 else None,\n", " upc_area=str(t[12]),\n", " comm_area=t[18],\n", " stock00=int(t[24]),\n", " stock01=int(t[25]),\n", " stock33=int(t[26]),\n", " stock36=int(t[27]),\n", " )\n", "\n", "\n", "RED = Color(rgb='E6B8B7')\n", "\n", "FROM = datetime.now().date()\n", "TO = FROM + timedelta(days=14)\n", "\n", "if FROM.month == TO.month and FROM.year == TO.year:\n", " aufbes = list(map(lambda t: Request.from_tuple(t), ibmconnect.getNegAvail(FROM.day, TO.day, FROM.month, FROM.year)))\n", "else:\n", " temp_to = FROM + timedelta(days = 31 if FROM.day >= 15 else 20)\n", " temp_to = temp_to.replace(day = 1)\n", " print(f\"Temp to = {temp_to}\")\n", " temp_to = temp_to - timedelta(days=1)\n", " print(f\"Temp to = {temp_to}\")\n", " aufbes = list(map(lambda t: Request.from_tuple(t), ibmconnect.getNegAvail(FROM.day, temp_to.day, FROM.month, FROM.year)))\n", " temp_from = temp_to + timedelta(days=1)\n", " print(f\"Temp from = {temp_from}\")\n", " while temp_from.month < TO.month:\n", " temp_to = FROM + timedelta(days = 31 if FROM.day >= 15 else 20)\n", " temp_to = temp_to.replace(day=1)\n", " temp_to = temp_to - timedelta(days=1)\n", " aufbes.extend(map(lambda t: Request.from_tuple(t), ibmconnect.getNegAvail(temp_from.day, temp_to.day, temp_from.month, temp_from.year)))\n", " temp_from = temp_to + timedelta(days=1)\n", " \n", " # last month. temp_from is the current TO month\n", " aufbes.extend(map(lambda t: Request.from_tuple(t), ibmconnect.getNegAvail(temp_from.day, TO.day, temp_from.month, temp_from.year)))\n", " \n", "\n", "\n", "aufbes.sort(key = lambda a: a.delivdate)\n", "\n", "vorlage = load_workbook(\"Umfuhrplanung_Vorlage.xlsx\")\n", "\n", "for prodNr in L_ART_EXT:\n", " aufbes_filt = [t for t in aufbes if t.artnr == prodNr]\n", "\n", " if len(aufbes_filt) > 0:\n", " prod = prodDict.get(prodNr)\n", " pareas = prod_areas.get(prodNr, None)\n", " if pareas is None:\n", " print(f\"Produktareas nicht gefunden: {prodNr}\")\n", "\n", " sheet = vorlage.copy_worksheet(vorlage[\"Vorlage\"])\n", " sheet.title = prodNr\n", " sheet['A1'] = f\"{prod.name.strip()} (#{prodNr})\"\n", " sheet['F1'] = f\"Kommiort {prod.pickArea}\"\n", " sheet['N1'] = f\"vom {FROM} bis inklussive {TO}\\n\\n\"\n", "\n", " first = aufbes_filt[0]\n", " verbleibend = {\n", " \"00\": pareas.areas.get(\"00\").amount if \"00\" in pareas.areas else 0,\n", " \"01\": pareas.areas.get(\"01\").amount if \"01\" in pareas.areas else 0,\n", " \"05\": pareas.areas.get(\"05\").amount if \"05\" in pareas.areas else 0,\n", " \"33\": pareas.areas.get(\"33\").amount if \"33\" in pareas.areas else 0,\n", " \"36\": pareas.areas.get(\"36\").amount if \"36\" in pareas.areas else 0,\n", " }\n", " \n", "\n", " we = first.upcoming\n", " we_dat = first.upc_date\n", " we_area = first.upc_area\n", " we_done = False\n", "\n", " sheet['C2'] = verbleibend[\"00\"]\n", " sheet['E2'] = verbleibend[\"33\"]\n", " sheet['G2'] = verbleibend[\"36\"]\n", " sheet['B3'] = we\n", " sheet['D3'] = we_dat\n", "\n", " verbleibendL = verbleibend\n", " bedarf_am = \"\"\n", " bedarf_durch = \"\"\n", " bedarf_auftrag = \"\"\n", " umfuhrmenge = 0\n", " umfuhr_an = \"\"\n", " auftrag = 0\n", " auftr_dennree = 0\n", " for t in aufbes_filt:\n", " if we > 0 and t.delivdate > we_dat:\n", " if not we_area in {\"00\", \"05\", \"33\", \"36\"}:\n", " print(f\"Unable to handle we of {prod_nr} in area {we_area}\")\n", " if we_area != \"05\":\n", " verbleibendL[we_area] += we\n", " else:\n", " verbleibendL[prod.pickArea] += we\n", " \n", " sheet.append([\"\", \"Produktion\", we_dat, t.artnr, t.artname, t.bundle, \"\", f\"+{we}\", verbleibendL[\"00\"], verbleibendL[\"01\"], verbleibendL[\"33\"], verbleibendL[\"36\"], t.upcoming, t.upc_date, t.upc_area, t.stock00, t.stock01, t.stock33, t.stock36])\n", " we = 0\n", " if t.cust_name.strip() == \"dennree GmbH\":\n", " sheet.append([t.ordernr, t.cust_name, t.delivdate, t.artnr, t.artname, t.bundle, t.ordered, t.ord_rem, verbleibendL[\"00\"], verbleibendL[\"01\"], verbleibendL[\"33\"], verbleibendL[\"36\"], t.upcoming, t.upc_date, t.upc_area, t.stock00, t.stock01, t.stock33, t.stock36])\n", " for row in sheet.iter_cols(min_col=1, max_col=16, min_row=sheet.max_row, max_row=sheet.max_row):\n", " for cell in row:\n", " cell.fill = PatternFill(fgColor=RED, fill_type = 'solid')\n", " elif t.cust_name.strip() == \"Interne Umlagerung\":\n", " #verbleibend += t.ordered\n", " verbleibendL[\"00\"] += t.ordered\n", " sheet.append([t.ordernr, t.cust_name, t.delivdate, t.artnr, t.artname, t.bundle, t.ordered, f\"+{t.ordered}\", verbleibendL[\"00\"], verbleibendL[\"01\"], verbleibendL[\"33\"], verbleibendL[\"36\"], t.upcoming, t.upc_date, t.upc_area, t.stock00, t.stock01, t.stock33, t.stock36])\n", " else:\n", " #verbleibend -= t.ord_rem\n", " verbleibendL[t.comm_area] -= t.ord_rem\n", " sheet.append([t.ordernr, t.cust_name, t.delivdate, t.artnr, t.artname, t.bundle, t.ordered, t.ord_rem, verbleibendL[\"00\"], verbleibendL[\"01\"], verbleibendL[\"33\"], verbleibendL[\"36\"], t.upcoming, t.upc_date, t.upc_area, t.stock00, t.stock01, t.stock33, t.stock36])\n", " \n", " if we > 0:\n", " if not we_area in {\"00\", \"05\", \"33\", \"36\"}:\n", " print(f\"Unable to handle we of {prod_nr} in area {we_area}\")\n", " if we_area != \"05\":\n", " verbleibendL[we_area] += we\n", " else:\n", " verbleibendL[prod.pickArea] += we\n", " \n", " sheet.append([\"\", \"Produktion\", we_dat, t.artnr, t.artname, t.bundle, \"\", f\"+{we}\", *verbleibendL.values(), t.upcoming, t.upc_date, t.upc_area, t.stock00, t.stock01, t.stock33, t.stock36])\n", "\n", "vorlage.save(f\"Umfuhrplanung_{FROM}.xlsx\")" ] }, { "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.1" }, "orig_nbformat": 4 }, "nbformat": 4, "nbformat_minor": 2 }