{
    "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
}
