excel/Formatter.py

import os
import re
import json
from datetime import datetime 
from babel.dates import format_date
from babel import Locale
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.worksheet.datavalidation import DataValidation


class Item:
    _format_id_spacing = " " * (10 * 10 * 5)
    _unassigned_id = -1

    @staticmethod
    def parse_id(text: str) -> int | None:
        match = re.search(r"\[([+-]?\d+)\]$", text.strip())
        if not match:
            raise Exception("Id not found. Must be in format '[int]' at the end of the string")    
        
        res = int(match.group(1))
        
        return None if res == Item._unassigned_id else res


    def __init__(self, id: int):
        self._id = id
    
    def format(self) -> str:
        return f"[{self.id}]"
    
    def get_id(self) -> int:
        return self._id


class Branch(Item):
    def __init__(self, customer_name: str, customer_id: str, branch_name: str, branch_id: int):
        self._customer_name = customer_name
        self._branch_name = branch_name
        self._id = branch_id
        self._customer_id = customer_id

    def format(self) -> str:
        return f"{self._customer_name} - {self._branch_name}" + Branch._format_id_spacing + f"[{self._customer_id}][{self._id}]"

    @staticmethod
    def parse(text: str) -> tuple[int, int]:
        """
        Returns a tuple with (customer_id, branch_id)
        """
        res = re.search(r"\[(\d+)\]\[(\d+)\]$", text.strip())
        return (int(res.group(1)), int(res.group(2)))

class Driver(Item):
    def __init__(self, id: int, name: str):
        self._id = id
        self._name = name 

    def format(self) -> str:
        return f"{self._name}" + Driver._format_id_spacing + f"[{self._id}]"


class Vehicle(Item):
    def __init__(self, id: int, name: str):
        self._id = id
        self._name = name 

    def format(self) -> str:
        return f"{self._name}" + Vehicle._format_id_spacing + f"[{self._id}]"


class Route(Item):
    def __init__(self, id: int, name: str):
        self._id = id
        self._name = name
        
    def format(self) -> str:
        return f"{self._name}" + Route._format_id_spacing + f"[{self._id}]"



class RoutePlan:
    def __init__(self, route: Route, driver: Driver, vehicle: Vehicle, collection_points: list[Branch]):
        self.route = route
        self.collection_points = collection_points
        self.driver = driver
        self.vehicle = vehicle
    
    def append_collection(self, branch: Branch):
        self.collection_points.append(branch)
        return self

class ParsedRoutePlan:
    """
    collection_points is in format [(customer_id, branch_id)]
    """

    def __init__(self, route_id: int, driver_id: int | None, vehicle_id: int | None, collection_points: list[tuple[int, int]]):
        self.route_id = route_id
        self.driver_id = driver_id
        self.vehicle_id = vehicle_id
        self.collection_points = collection_points

    
    def to_dict(self) -> dict:
        """
        Returns a dictionary with the following format:
        {
            "route_id"  : int,
            "driver_id" : int,
            "vehicle_id": int,
            "collection_points": { "customer_id": int, "branch_id": int }
        }
        """  
        return {
            "route_id"  : self.route_id,
            "driver_id" : self.driver_id,
            "vehicle_id": self.vehicle_id,
            "collection_points": [{"customer_id": customer_id, "branch_id": branch_id} for (customer_id, branch_id) in self.collection_points]
        }
    
    def to_json(self) -> str:
        """
        Returns a JSON string with the following format:
        {
            "route_id"  : int,
            "driver_id" : int,
            "vehicle_id": int,
            "collection_points": { "customer_id": int, "branch_id": int }
        }
        """

        return json.dumps(self.to_dict())

    def __str__(self) -> str:
        return f"<ParsedRoutePlan {self.to_json()}>"

class Formatter:
    _locale = Locale("es")
    
    _non_spill_alignment = Alignment(horizontal="fill", vertical="top")
    _branch_column_width = 26
    _max_row             = 3000

    _route_start_column_i = 1 # A
    _route_start_row      = 2 # A2

    _metadata_sheet_name                  = "__metadata__"
    _metadata_sheet_branch_list_column    = 1 # A
    _metadata_sheet_driver_list_column    = 2 # B
    _metadata_sheet_vehicle_list_column   = 3 # C
    _metadata_sheet_route_metadata_cell   = "D1" # { "start_row": 3,  "start_column": 4, "end_column": 9 }

    _COLLECTION_POINT_PROMPT= \
"""Opciones:
- Arrastra un punto de recolección desde otra celda
- Selecciona un punto de recolección de esta lista desplegable
"""

    @staticmethod
    def _format_route_metadata(start_row: int, start_column_i: int, end_column_i: int):
        return json.dumps({ "start_row": start_row, "start_column": start_column_i, "end_column": end_column_i })

    def _parse_route_metadata(string: str) -> (int, int, int):
        data = json.loads(string)
        start_row = data.get("start_row", None)
        start_column_i = data.get("start_column", None)
        end_column_i = data.get("end_column", None)

        if start_row is None or start_column_i is None or end_column_i is None:
            raise Exception("Failed to parse route medata")

        return (start_row, start_column_i, end_column_i)
        

    @staticmethod
    def _format_title(dt: datetime) -> str:
        return f'Rutas {format_date(dt, format="full", locale=Formatter._locale)}'
    
    @staticmethod
    def _set_outer_border(sheet: Worksheet, cell_range_str: str, border_style: str ="medium", border_color: str="000000", omit_top: bool = False):
        cell_range = cell_range_str.split(":")
        start_cell, end_cell = cell_range
    
        start_col = ord(start_cell[0].upper()) - ord('A') + 1
        start_row = int(start_cell[1:])
    
        end_col = ord(end_cell[0].upper()) - ord('A') + 1
        end_row = int(end_cell[1:])
        
        side = Side(border_style=border_style, color=border_color)

        for row in range(start_row, end_row + 1):
            for col in range(start_col, end_col + 1):
                cell = sheet.cell(row=row, column=col)
                
                # Esquina superior izquierda
                if row == start_row and col == start_col:
                    if omit_top:
                        cell.border = Border(left=side)
                    else:
                        cell.border = Border(top=side, left=side)

                # Esquina superior derecha
                elif row == start_row and col == end_col and omit_top == False:
                    if omit_top:
                        cell.border = Border(right=side)
                    else:
                        cell.border = Border(top=side, right=side)

                # Esquina inferior izquierda
                elif row == end_row and col == start_col:
                    cell.border = Border(bottom=side, left=side)

                # Esquina inferior derecha
                elif row == end_row and col == end_col:
                    cell.border = Border(bottom=side, right=side)

                # Lado superior
                elif row == start_row and omit_top == False:
                    cell.border = Border(top=side)

                # Lado inferior
                elif row == end_row:
                    cell.border = Border(bottom=side)

                # Lado izquierdo
                elif col == start_col:
                    cell.border = Border(left=side)
                
                # Lado derecho
                elif col == end_col:
                    cell.border = Border(right=side)
    
    @staticmethod
    def format_workbook(
        date: datetime,
        routes: list[RoutePlan],
        branches: list[Branch],
        vehicles: list[Vehicle],
        drivers: list[Driver],
    ) -> Workbook:
        
        title = Formatter._format_title(date)
        
        workbook = Workbook()
        sheet = workbook.active
        if sheet is None:
            raise Exception("Failed to get active sheet")

        # Ponerle nombre a la hoja
        sheet.title = f"{date.day}-{date.month}-{date.year}"

        # Crear hoja escondida de metadatos
        metadata_sheet = workbook.create_sheet(Formatter._metadata_sheet_name)
        metadata_sheet.sheet_state = "hidden"
        
        ##########################################################################################
        # Validación de choferes
        ##########################################################################################

        # Crear lista escondida de validación de choferes
        validation_list_row_i_start = 1
        validation_list_row_i = validation_list_row_i_start
        for d in drivers:
            cell = metadata_sheet.cell(row=validation_list_row_i, column=Formatter._metadata_sheet_driver_list_column, value=d.format())
            cell.alignment = Formatter._non_spill_alignment
            validation_list_row_i += 1

        # Crear validación de choferes
        driver_validation_list_column_letter = get_column_letter(Formatter._metadata_sheet_driver_list_column)
        driver_validation = DataValidation(
            type="list", 
            formula1=f"{Formatter._metadata_sheet_name}!${driver_validation_list_column_letter}${validation_list_row_i_start}:${driver_validation_list_column_letter}${validation_list_row_i}",
            allowBlank=True,

            promptTitle="Asignar chofer",
            prompt="Selecciona un chofer de esta lista desplegable",

            errorTitle="El chofer no está en la lista",
            error="Agrégalo primero en la pestaña de choferes.",

            showDropDown=False, # Está al revéz la opción, por alguna razón
            showInputMessage=True,
            showErrorMessage=True
        )
        sheet.add_data_validation(driver_validation)

        ##########################################################################################
        # Validación de vehículos
        ##########################################################################################

        # Crear lista escondida de validación de vehículos
        validation_list_row_i = validation_list_row_i_start
        for v in vehicles:
            cell = metadata_sheet.cell(row=validation_list_row_i, column=Formatter._metadata_sheet_vehicle_list_column, value=v.format())
            cell.alignment = Formatter._non_spill_alignment
            validation_list_row_i += 1

        # Crear validación de vehículos
        vehicle_validation_list_column_letter = get_column_letter(Formatter._metadata_sheet_vehicle_list_column)
        vehicle_validation = DataValidation(
            type="list", 
            formula1=f"{Formatter._metadata_sheet_name}!${vehicle_validation_list_column_letter}${validation_list_row_i_start}:${vehicle_validation_list_column_letter}${validation_list_row_i}",
            allowBlank=True,

            promptTitle="Asignar vehículo",
            prompt="Selecciona un vehículo esta lista desplegable",

            errorTitle="El vehículo no está en la lista",
            error="Agrégalo primero en la pestaña de vehículos.",

            showDropDown=False, # Está al revéz la opción, por alguna razón
            showInputMessage=True,
            showErrorMessage=True
        )
        sheet.add_data_validation(vehicle_validation)
        
        ##########################################################################################
        # Validación de sucursales
        ##########################################################################################
            
        # Crear lista escondida de validación de puntos de recolección
        validation_list_row_i = validation_list_row_i_start
        for b in branches:
            cell = metadata_sheet.cell(row=validation_list_row_i, column=Formatter._metadata_sheet_branch_list_column, value=b.format())
            cell.alignment = Formatter._non_spill_alignment
            validation_list_row_i += 1
        # Crear validación de puntos de recolección
        collection_point_validation_list_column_letter = get_column_letter(Formatter._metadata_sheet_branch_list_column)
        collection_point_validation = DataValidation(
            type="list", 
            formula1=f"{Formatter._metadata_sheet_name}!${collection_point_validation_list_column_letter}${validation_list_row_i_start}:${collection_point_validation_list_column_letter}${validation_list_row_i}",
            allowBlank=True,

            promptTitle="Punto de recolección",
            prompt=Formatter._COLLECTION_POINT_PROMPT,

            errorTitle="Punto de recolección desconocido",
            error="Agrégalo primero en la pestaña de servicios.",

            showDropDown=False, # Está al revéz la opción, por alguna razón
            showInputMessage=True,
            showErrorMessage=True
        )
        sheet.add_data_validation(collection_point_validation)


        ##########################################################################################
        # Metadatos de rutas
        ##########################################################################################
        metadata_sheet[Formatter._metadata_sheet_route_metadata_cell].value = Formatter._format_route_metadata(
            start_row=Formatter._route_start_row, 
            start_column_i=Formatter._route_start_column_i,
            end_column_i=Formatter._route_start_column_i + len(routes) - 1
        )

        # Título superior de la página
        title_cell = sheet.cell(row=1, column=1, value=title)
        sheet.merge_cells(f"A1:{get_column_letter(len(routes))}1")
        title_cell.font = Font(size=14, bold=True)
        title_cell.alignment = Alignment(horizontal="center")

        # Poner todas las celdas con fondo blanco
        for c in range(1, len(routes) + 1):
            for r in range(1, Formatter._max_row + 1):
                cell = sheet.cell(row=r, column=c)
                cell.fill = PatternFill(patternType="solid", fgColor="FFFFFF")

        # Poner bordes a los encabezados
        Formatter._set_outer_border(sheet=sheet, cell_range_str=f"A1:{get_column_letter(len(routes))}4", border_color="000000", border_style="medium")

        # Poner bordes a las rutas
        Formatter._set_outer_border(sheet=sheet, cell_range_str=f"A5:{get_column_letter(len(routes))}{Formatter._max_row}", border_color="000000", border_style="medium", omit_top=True)
        
        # Inmovilizar primeras tres filas
        sheet.freeze_panes = "A5"

        # Rutas
        route_col_i = 1 # Empieza en columna A
        route_row_i = 2 # Empieza en fila 2
        driver_row_i = route_row_i + 1 # El chofer va a bajo de la ruta
        vehicle_row_i = driver_row_i + 1  # El vehículo va abajo del chofer
        for r in routes:
            column_letter = get_column_letter(route_col_i)

            # Poner ancho a la columna
            sheet.column_dimensions[column_letter].width = Formatter._branch_column_width

            # Escribir nombre de la ruta
            route_cell = sheet.cell(row=route_row_i, column=route_col_i, value=r.route.format())
            route_cell.alignment = Formatter._non_spill_alignment
            route_cell.font = Font(bold=True)

            # Escribir nombre de chofer
            driver_cell = sheet.cell(row=driver_row_i, column=route_col_i, value=r.driver.format())
            driver_cell.alignment = Formatter._non_spill_alignment
            driver_validation.add(driver_cell)

            # Escribir nombre de vehículo
            vehicle_cell = sheet.cell(row=vehicle_row_i, column=route_col_i, value=r.vehicle.format())
            vehicle_cell.alignment = Formatter._non_spill_alignment
            vehicle_validation.add(vehicle_cell)

            # Escribir puntos de recolección
            collection_point_row_i_start = route_row_i + 3
            collection_point_row_i = collection_point_row_i_start
            for cp in r.collection_points:
                collection_point_cell = sheet.cell(row=collection_point_row_i, column=route_col_i, value=cp.format())
                collection_point_cell.alignment = Formatter._non_spill_alignment
                collection_point_row_i += 1

            # Mover a siguiente columna
            route_col_i += 1

            # Agregar validación de datos a toda la columna de puntos de recolección de la ruta a partir del inicio de la lista
            collection_point_validation.add(f"${column_letter}${collection_point_row_i_start}:${column_letter}${Formatter._max_row}")
            
            # Agregar formato a todas las celdas
            for row_i in range(collection_point_row_i_start, Formatter._max_row + 1):
                cell = sheet[f"{column_letter}{row_i}"]
                cell.alignment = Formatter._non_spill_alignment

        return workbook

    @staticmethod
    def parse_workbook_from_path(path: str) -> list[ParsedRoutePlan]:
        wb = load_workbook(path)
        return Formatter.parse_workbook(wb)

    @staticmethod
    def parse_workbook(wb: Workbook) -> list[ParsedRoutePlan]:
        sheet = wb.worksheets[0]
        metadata_sheet = wb[Formatter._metadata_sheet_name]
        route_start_row_i, route_start_column_i, route_end_column_i = Formatter._parse_route_metadata(metadata_sheet[Formatter._metadata_sheet_route_metadata_cell].value)
    
        parsed_route_plans: list[ParsedRoutePlan] = []
        for route_col_i in range(route_start_column_i, route_end_column_i + 1):
            route_id   = Item.parse_id(sheet.cell(row=route_start_row_i,     column=route_col_i).value)
            driver_id  = Item.parse_id(sheet.cell(row=route_start_row_i + 1, column=route_col_i).value)
            vehicle_id = Item.parse_id(sheet.cell(row=route_start_row_i + 2, column=route_col_i).value)
            collection_points: list[tuple[int, int]] = []
            
            for collection_row_i in range(route_start_row_i + 3, Formatter._max_row + 1):
                value = sheet.cell(row=collection_row_i, column=route_col_i).value
                if value is not None and len(value) > 0:
                    id_pair = Branch.parse(value)
                    if id_pair is not None:
                        collection_points.append(id_pair)

            parsed_route_plans.append(ParsedRoutePlan(
                route_id=route_id,
                driver_id=driver_id,
                vehicle_id=vehicle_id,
                collection_points=collection_points
            ))
        return parsed_route_plans




if __name__ == "__main__":

    vehicle_1 = Vehicle(id=1, name="Kenworth")
    vehicle_2 = Vehicle(id=2, name="Camioneta Nissan")
    vehicle_3 = Vehicle(id=3, name="Honda 500")
    vehicle_4 = Vehicle(id=4, name="Freightliner")
    vehicle_5 = Vehicle(id=5, name="Camioneta Toyota")          
    
    driver_1 = Driver(id=1, name="Isaac")
    driver_2 = Driver(id=2, name="Gustavo")
    driver_3 = Driver(id=3, name="Raúl")
    driver_4 = Driver(id=4, name="Rita")
    driver_5 = Driver(id=5, name="Pedro")

    route_1 = Route(id=1, name="Ruta 1")
    route_2 = Route(id=2, name="Ruta 2")
    route_3 = Route(id=3, name="Ruta 3")
    route_4 = Route(id=4, name="Ruta 4")
    route_5 = Route(id=5, name="Ruta 5")
    route_6 = Route(id=6, name="Ruta 6")

    branches = [
        Branch(branch_id=1,  branch_name="A", customer_id=1, customer_name="Walmart"),
        Branch(branch_id=2,  branch_name="B", customer_id=1, customer_name="Walmart"),
        Branch(branch_id=3,  branch_name="C", customer_id=1, customer_name="Walmart"),
        Branch(branch_id=4,  branch_name="D", customer_id=1, customer_name="Walmart"),
        Branch(branch_id=5,  branch_name="E", customer_id=1, customer_name="Walmart"),
        Branch(branch_id=6,  branch_name="F", customer_id=1, customer_name="Walmart"),
        Branch(branch_id=7,  branch_name="A", customer_id=2, customer_name="Autozone"),
        Branch(branch_id=8,  branch_name="B", customer_id=2, customer_name="Autozone"),
        Branch(branch_id=9,  branch_name="C", customer_id=2, customer_name="Autozone"),
        Branch(branch_id=10, branch_name="D", customer_id=2, customer_name="Autozone"),
        Branch(branch_id=11, branch_name="E", customer_id=2, customer_name="Autozone"),
        Branch(branch_id=12, branch_name="F", customer_id=2, customer_name="Autozone"),
        Branch(branch_id=13, branch_name="A", customer_id=3, customer_name="Los Tarascos"),
        Branch(branch_id=14, branch_name="B", customer_id=3, customer_name="Los Tarascos"),
        Branch(branch_id=15, branch_name="C", customer_id=3, customer_name="Los Tarascos"),
        Branch(branch_id=16, branch_name="D", customer_id=3, customer_name="Los Tarascos"),
        Branch(branch_id=17, branch_name="E", customer_id=3, customer_name="Los Tarascos"),
        Branch(branch_id=18, branch_name="F", customer_id=3, customer_name="Los Tarascos"),
        Branch(branch_id=31, branch_name="A", customer_id=4, customer_name="Constructora Gutiérrez"),
        Branch(branch_id=32, branch_name="B", customer_id=4, customer_name="Constructora Gutiérrez"),
        Branch(branch_id=33, branch_name="C", customer_id=4, customer_name="Constructora Gutiérrez"),
        Branch(branch_id=45, branch_name="D", customer_id=4, customer_name="Constructora Gutiérrez"),
        Branch(branch_id=63, branch_name="E", customer_id=4, customer_name="Constructora Gutiérrez"),
        Branch(branch_id=23, branch_name="F", customer_id=4, customer_name="Constructora Gutiérrez")
    ]

    print("Formatting Excel")
    wb = Formatter.format_workbook(
        date=datetime(2023, 8, 25),
        branches=branches,
        vehicles=[
            vehicle_1,
            vehicle_2,
            vehicle_3,
            vehicle_4,
            vehicle_5
        ],
        drivers=[
            driver_1,
            driver_2,
            driver_3,
            driver_4,
            driver_5
        ],

        routes=[
            RoutePlan(route=route_1, driver=driver_1, vehicle=vehicle_1, collection_points=branches[0:6]),
            RoutePlan(route=route_2, driver=driver_2, vehicle=vehicle_2, collection_points=branches[6:12]),
            RoutePlan(route=route_3, driver=driver_3, vehicle=vehicle_3, collection_points=branches[12:18]),
            RoutePlan(route=route_4, driver=driver_4, vehicle=vehicle_4, collection_points=branches[18:24]),
        ],
    )

    cwd = os.getcwdb().decode('utf-8')
    filename = "test.xlsx"
    path = os.path.join(cwd, filename)
    print(f"Saving Excel at {path}")

    wb.save(path)
    print("Parsing Excel")

    parsed_routes = Formatter.parse_workbook_from_path(path)
    for r in parsed_routes:
        print(r)
    
    print("Done!")