# erodriguezrshasa/stock architecture

> Architecture diagram of the GitHub repository erodriguezrshasa/stock, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-04-06). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/erodriguezrshasa/stock
- Repository: https://github.com/erodriguezrshasa/stock

## Overview

1. **What this repository does**
   This is a Windows-oriented stock-analysis pipeline for business units (BU) such as ROPA and CALZADO. Its core job is to pull a stock distribution from ZIMA, enrich it with SQL Server data, compute stock KPIs/flags, and publish a formatted Excel report.

2. **Main execution paths**
   There are two top-level modes:
   - **Automated end-to-end run**: `correr_stock.py` opens ZIMA in a browser, downloads the latest `.xlsx`, then triggers the analysis.
   - **Analysis-only run**: `Analisis_Stock/stock.py` reads an already-downloaded ZIMA file and produces the report.
   The `.bat` and `.ps1` scripts are operational wrappers for one BU or multiple BUs in parallel.

3. **Data acquisition boundary**
   The pipeline has two external inputs:
   - **ZIMA web application** at `http://10.30.32.10:7923/{BU}/`, automated with **Playwright** to login, run the stock distribution, and download the result.
   - **Reference Excel files** on a network share, especially `N:/ZIMA/Picos_DistiMn.xlsx`, used for minimum distribution / peak reference data.
   This boundary is important because the process depends on both browser automation and shared-file access.

4. **Database integration**
   The analysis layer connects to **SQL Server** (`datalake`, database `Allocations`) through `Analisis_Stock/db.py`.
   `queries.py` centralizes all SQL statements, while `carga_datos.py` executes them and loads the resulting tables into the pipeline.
   The repo depends on multiple allocation/reporting tables such as `AllocationsReports`, `DistCedis`, `BufferBas`, `VentasInv10s`, `RMCHist`, `FrameworkBase`, and others listed in the README.

5. **Core analytical orchestration**
   `Analisis_Stock/stock.py` is the orchestrator. It sequences:
   - loading the ZIMA export,
   - pulling SQL data,
   - merging reference datasets,
   - running KPI and validation calculations,
   - exporting the final workbook.
   The design is linear: acquire → enrich → calculate → export.

6. **Business logic subsystem**
   `calculos.py` contains the domain rules. It computes:
   - WOS current and projected,
   - stock available and stock final,
   - CEDIS coverage,
   - store percentage coverage,
   - buffer percentages and `Flag_Buffer`,
   - scarcity/excess per store,
   - recategorization and “Check RES” logic,
   - age of last receipt and next receipt timing.
   It also derives operational flags like `WK en CEDIS`, `DistMinima`, `Planning`, `Status_VAN`, `Piquerio`, and `Van_Clon`.

7. **Automatic step assignment**
   A notable business rule is the **“Paso Automatico”** classification. It first filters eligible items using inventory, pending ODs, stock thresholds, and offer type, then assigns a prioritized step from 1 to 5 based on BU-specific thresholds.
   Those thresholds are parameterized per BU in `config.py`, which makes the same logic reusable across business units.

8. **Report generation and formatting**
   `archivo_excel.py` produces the final workbook with four sheets: **Importador**, **Distribucion**, **Stock**, and **Check_WOS**.
   Formatting is not just data export: the module applies colors, formulas, and conditional formatting, using **openpyxl** and **win32com** for Excel automation on Windows.

9. **Configuration and BU isolation**
   `Analisis_Stock/config.py` is the central control point for:
   - active BU selection (`STOCK_BU`),
   - output locations,
   - per-BU parameters for planning and automatic step rules.
   The repo is structured to support one BU or parallel execution of multiple BUs, with the BU passed through environment variables or launcher scripts.

10. **Automation, scheduling, and notifications**
   - `programar_stock.py` uses the `schedule` library to run the pipeline at a configured time.
   - `notificaciones.py` sends lifecycle alerts to **Microsoft Teams** through a webhook/Power Automate endpoint.
   This gives the process an operational layer: start, completion timing, and error reporting.

11. **Operational wrappers and logs**
   The `.bat` and PowerShell scripts are deployment wrappers for Windows users and scheduled jobs. They manage launching, parallelism, and logs.
   The `logs/` directory shows that runtime output is persisted per BU and timestamp, which is useful for auditability and troubleshooting.

12. **Overall architecture boundary**
   Architecturally, this is not a server application; it is a **desktop/worker-style ETL and reporting pipeline**. Its external dependencies are ZIMA, SQL Server, a network file share, and Teams notifications. The repo’s main boundary is between **data collection** (browser + DB + shared files) and **business reporting** (calculations + Excel output).

Main entry points: [`Analisis_Stock/stock.py`](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/stock.py) (Analysis flow), [`correr_stock.py`](https://github.com/erodriguezrshasa/stock/blob/main/correr_stock.py) (Run ZIMA), [`Analisis_Stock/calculos.py`](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/calculos.py) (Business rules), [`Analisis_Stock/carga_datos.py`](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/carga_datos.py) (Load data)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_g_entry["Entry & automation"]
  node_n_correr_stock["Run ZIMA<br/>entrypoint<br/>[correr_stock.py]"]
  node_n_root_stock["Stock runner<br/>entrypoint<br/>[stock.py]"]
  node_n_programar_stock["Scheduler<br/>[programar_stock.py]"]
  node_n_ps1["PowerShell ops<br/>wrapper<br/>[run_stock.ps1]"]
  node_n_bat["Batch ops<br/>wrapper<br/>[ejecutar_stock.bat]"]
end

subgraph group_g_analysis["Analysis pipeline"]
  node_n_stock_main["Analysis flow<br/>orchestrator<br/>[stock.py]"]
  node_n_carga_datos["Load data<br/>etl<br/>[carga_datos.py]"]
  node_n_queries["SQL catalog<br/>sql definitions<br/>[queries.py]"]
end

subgraph group_g_rules["Business rules"]
  node_n_calculos["Business rules<br/>domain logic<br/>[calculos.py]"]
  node_n_config["BU config<br/>settings<br/>[config.py]"]
end

subgraph group_g_integration["External systems"]
  node_n_db[("SQL Server<br/>database client<br/>[db.py]")]
  node_n_playwright(("ZIMA browser<br/>web automation"))
  node_n_share["Reference files<br/>shared storage"]
  node_n_notify["Teams alerts<br/>notifications<br/>[notificaciones.py]"]
end

subgraph group_g_output["Reporting output"]
  node_n_excel["Excel report<br/>reporting<br/>[archivo_excel.py]"]
end

node_n_correr_stock -->|"download ZIMA export"| node_n_playwright
node_n_correr_stock -->|"start analysis"| node_n_stock_main
node_n_root_stock -->|"run analysis"| node_n_stock_main
node_n_programar_stock -.->|"schedule run"| node_n_correr_stock
node_n_ps1 -->|"launch"| node_n_correr_stock
node_n_bat -->|"launch"| node_n_correr_stock
node_n_stock_main -->|"load sources"| node_n_carga_datos
node_n_stock_main -->|"compute KPIs"| node_n_calculos
node_n_stock_main -->|"export workbook"| node_n_excel
node_n_carga_datos -->|"use SQL"| node_n_queries
node_n_queries -->|"execute on SQL Server"| node_n_db
node_n_db -->|"return tables"| node_n_carga_datos
node_n_calculos -->|"BU thresholds"| node_n_config
node_n_calculos -->|"read references"| node_n_share
node_n_excel -.->|"completion alert"| node_n_notify
node_n_stock_main -.->|"error alert"| node_n_notify

click node_n_correr_stock "https://github.com/erodriguezrshasa/stock/blob/main/correr_stock.py"
click node_n_root_stock "https://github.com/erodriguezrshasa/stock/blob/main/stock.py"
click node_n_programar_stock "https://github.com/erodriguezrshasa/stock/blob/main/programar_stock.py"
click node_n_ps1 "https://github.com/erodriguezrshasa/stock/blob/main/scripts/run_stock.ps1"
click node_n_bat "https://github.com/erodriguezrshasa/stock/blob/main/ejecutar_stock.bat"
click node_n_stock_main "https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/stock.py"
click node_n_carga_datos "https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/carga_datos.py"
click node_n_queries "https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/queries.py"
click node_n_db "https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/db.py"
click node_n_calculos "https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/calculos.py"
click node_n_config "https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/config.py"
click node_n_excel "https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/archivo_excel.py"
click node_n_notify "https://github.com/erodriguezrshasa/stock/blob/main/notificaciones.py"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_n_correr_stock,node_n_root_stock,node_n_programar_stock,node_n_ps1,node_n_bat toneBlue
class node_n_stock_main,node_n_carga_datos,node_n_queries toneAmber
class node_n_calculos,node_n_config toneMint
class node_n_db,node_n_playwright,node_n_share,node_n_notify toneRose
class node_n_excel toneIndigo
```

## Components

### Entry & automation

Launchers, schedulers, and operational wrappers.

- [Run ZIMA](https://github.com/erodriguezrshasa/stock/blob/main/correr_stock.py): entrypoint · Automated end-to-end run that downloads ZIMA data and starts analysis. · `correr_stock.py`
- [Stock runner](https://github.com/erodriguezrshasa/stock/blob/main/stock.py): entrypoint · Top-level wrapper for stock execution in the repository root. · `stock.py`
- [Scheduler](https://github.com/erodriguezrshasa/stock/blob/main/programar_stock.py): scheduler · Schedules stock runs at configured times. · `programar_stock.py`
- [PowerShell ops](https://github.com/erodriguezrshasa/stock/blob/main/scripts/run_stock.ps1): wrapper · Windows scripts for launching one or multiple BU runs. · `scripts/run_stock.ps1`
- [Batch ops](https://github.com/erodriguezrshasa/stock/blob/main/ejecutar_stock.bat): wrapper · Windows batch launchers for individual or parallel BU execution. · `ejecutar_stock.bat`

### Analysis pipeline

Core stock analysis orchestration and data loading.

- [Analysis flow](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/stock.py): orchestrator · Main analysis orchestrator that sequences load, enrich, calculate, and export. · `Analisis_Stock/stock.py`
- [Load data](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/carga_datos.py): etl · Runs SQL queries and loads source tables into the pipeline. · `Analisis_Stock/carga_datos.py`
- [SQL catalog](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/queries.py): sql definitions · Centralized SQL statements for the analysis layer. · `Analisis_Stock/queries.py`

### Business rules

Domain calculations and BU-specific parameters.

- [Business rules](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/calculos.py): domain logic · Computes stock KPIs, flags, coverage, recategorization, and step assignment. · `Analisis_Stock/calculos.py`
- [BU config](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/config.py): settings · Central BU selection and per-BU thresholds and output settings. · `Analisis_Stock/config.py`

### External systems

Database, web source, shared files, and notifications.

- [SQL Server](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/db.py): database client · Connection layer for datalake / Allocations in SQL Server. · `Analisis_Stock/db.py`
- **ZIMA browser**: web automation · Logs into ZIMA, runs stock distribution, and downloads the export.
- **Reference files**: shared storage · Network-share Excel inputs such as minimum distribution and peak references.
- [Teams alerts](https://github.com/erodriguezrshasa/stock/blob/main/notificaciones.py): notifications · Sends run lifecycle and error notifications through webhook automation. · `notificaciones.py`

### Reporting output

Excel generation and workbook formatting.

- [Excel report](https://github.com/erodriguezrshasa/stock/blob/main/Analisis_Stock/archivo_excel.py): reporting · Builds the final workbook, sheets, formulas, and formatting. · `Analisis_Stock/archivo_excel.py`

## Connections

- Run ZIMA → ZIMA browser: download ZIMA export · Automated run first retrieves the latest stock distribution from ZIMA.
- Run ZIMA → Analysis flow: start analysis · Passes the downloaded file into the analysis pipeline.
- Stock runner → Analysis flow: run analysis · Root runner invokes the main stock workflow.
- Scheduler → Run ZIMA: schedule run · Scheduler triggers the automated end-to-end flow.
- PowerShell ops → Run ZIMA: launch · PowerShell wrapper starts the stock process.
- Batch ops → Run ZIMA: launch · Batch wrapper starts one or multiple BU runs.
- Analysis flow → Load data: load sources · Orchestrator fetches database data and supporting inputs.
- Analysis flow → Business rules: compute KPIs · Orchestrator sends merged inputs into domain calculations.
- Analysis flow → Excel report: export workbook · Final results are written to the formatted Excel report.
- Load data → SQL catalog: use SQL · Data loader relies on centralized query definitions.
- SQL catalog → SQL Server: execute on SQL Server · Queries are run through the database access layer.
- SQL Server → Load data: return tables · Loaded tables are returned to the analysis pipeline.
- Business rules → BU config: BU thresholds · Calculations use BU-specific parameters and step rules.
- Business rules → Reference files: read references · Business rules incorporate network-share reference files.
- Excel report → Teams alerts: completion alert · Successful runs can be reported through notifications.
- Analysis flow → Teams alerts: error alert · Operational alerts are emitted on run failure or completion.
