How erodriguezrshasa/stock works
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: stock.py Analysis flow · correr_stock.py Run ZIMA · calculos.py Business rules · carga_datos.py Load data
Components 15
Entry & automation
Launchers, schedulers, and operational wrappers.
- Run ZIMA · Automated end-to-end run that downloads ZIMA data and starts analysis.
correr_stock.py - Stock runner · Top-level wrapper for stock execution in the repository root.
stock.py - Scheduler · Schedules stock runs at configured times.
programar_stock.py - PowerShell ops · Windows scripts for launching one or multiple BU runs.
scripts/run_stock.ps1 - Batch ops · Windows batch launchers for individual or parallel BU execution.
ejecutar_stock.bat
Analysis pipeline
Core stock analysis orchestration and data loading.
- Analysis flow · Main analysis orchestrator that sequences load, enrich, calculate, and export.
Analisis_Stock/stock.py - Load data · Runs SQL queries and loads source tables into the pipeline.
Analisis_Stock/carga_datos.py - SQL catalog · Centralized SQL statements for the analysis layer.
Analisis_Stock/queries.py
Business rules
Domain calculations and BU-specific parameters.
- Business rules · Computes stock KPIs, flags, coverage, recategorization, and step assignment.
Analisis_Stock/calculos.py - BU config · 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 · Connection layer for datalake / Allocations in SQL Server.
Analisis_Stock/db.py - ZIMA browser · Logs into ZIMA, runs stock distribution, and downloads the export.
- Reference files · Network-share Excel inputs such as minimum distribution and peak references.
- Teams alerts · Sends run lifecycle and error notifications through webhook automation.
notificaciones.py
Reporting output
Excel generation and workbook formatting.
- Excel report · Builds the final workbook, sheets, formulas, and formatting.
Analisis_Stock/archivo_excel.py
Connections 16
- 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.