# Documentation: Subscribers Reporting Automation

## Table of Contents
1. [System Overview](#1-system-overview)
2. [Folder Structure](#2-folder-structure)
3. [Main Components](#3-main-components)
4. [File Naming Conventions](#4-file-naming-conventions)
5. [Workflow](#5-workflow)
6. [Operator List](#6-operator-list)
7. [System Reports](#7-system-reports)

---

## 1. System Overview

**Subscribers Reporting Automation** is an Excel VBA macro-based system for automating the subscriber reporting process for Disney channel packages distributed by Polish cable and IPTV operators.

### Purpose
- Automatically process subscriber reports received from operators
- Generate and distribute standardised reports back to operators
- Support the Polish market (PL/POL) for Disney channel package distribution

### Package
Primary package covered by reporting: **DISNEY10** (Disney channel bundle).

---

## 2. Folder Structure

```
Subscribers Reporting Automation/
│
├── Admin/                          # Administration folder (system core)
│   ├── SR_Macro.xlsb               # Main Excel macro file
│   ├── Subupload with Bundled Indicator.xlsx  # AIS upload file
│   ├── icon.ico                    # Application icon
│   ├── folder create.bat           # Operator folder creation script
│   │
│   └── Export/
│       └── PL/                     # Export for the Polish market
│           ├── [Operator Name]/    # One folder per operator
│           │   ├── *.xls           # Generated Excel reports
│           │   └── *.msg           # Outlook email archives
│           │
│           └── Nasza Wizja/        # Sub-distribution platform
│               └── [Operator Name]/
│
├── Import/
│   └── POL/                        # Import for the Polish market
│       └── [Operator ID]/          # Folder named by numeric operator ID
│           ├── [Operator] - [timestamp]_Disney*.xlsx
│           └── [Operator] - [timestamp]_RAPORTY.msg
│
├── System Reports/                 # System-level reports
│   ├── Export/                     # Data exported from the system
│   │   └── Exported data_DD.MM.YY.xlsx
│   └── Import/                     # Data imported into the system
│       └── P[period]_FY[year]_Subs_report DD.MM.YYYY.xlsx
│
└── SR_Macro - Shortcut.lnk        # Shortcut to the main macro file
```

---

## 3. Main Components

### `Admin/SR_Macro.xlsb` — Main Macro
An Excel Binary Workbook (`.xlsb`) containing VBA macros that execute the entire automation process. This is the central component of the system.

**Functions:**
- Read operator reports from the `Import/POL/` folder
- Process and validate subscriber data
- Generate standardised reports (`.xls`)
- Create and archive email messages (`.msg`)
- Write results to `Admin/Export/PL/`

### `Admin/folder create.bat` — Folder Creation Script
A Windows batch script that creates numeric sub-folders inside `Import/POL/`. Each folder corresponds to a numeric operator ID in the system.

Folders created (example operator IDs): `12781`, `12784`, ..., `15258`

### `Admin/Subupload with Bundled Indicator.xlsx`
The file used to upload processed subscriber data — including bundled-package indicators — to the Disney AIS portal.

### `System Reports/`
System-level reporting files:
- **Export**: Data exported from the system — `Exported data_*.xlsx` files dated by export day
- **Import**: Subscriber reports imported from the central system — `P[period]_FY[year]_Subs_report *.xlsx` files (e.g. `P07_FY24` = Period 7 / Fiscal Year 2024)

---

## 4. File Naming Conventions

### Export reports (.xls)
```
YYYYMMDD_HHMMSS_DISNEY10thismonth[OperatorID][YYYYMM].xls
```
Example: `20240410_134855_DISNEY10thismonth88202311.xls`
- `20240410` — report generation date (10 April 2024)
- `134855` — generation time (13:48:55)
- `DISNEY10` — channel package
- `thismonth` — current-month report
- `88` — operator ID
- `202311` — reporting period (November 2023)

### Export email archives (.msg)
```
YYYYMMDD_HHMMSS_[email_subject].msg
```
The email subject encodes the date, operator name and reporting month, e.g.:
`12.10.23NaszaWizjaRaportabonenckiodfirmyORIONBaejHesszamiesicLISTOPAD`

### Import files (.xlsx)
```
[Operator Name] - YYYYMMDDHHMMSS_Disney[package].xlsx
```
Example: `CITY NET SP ZOO - 20240614111758_DisneyC.N..xlsx`

### Import email archives (.msg)
```
[ID][Operator Name] - YYYYMMDDHHMMSS_RAPORTY.msg
```

### System reports
- Export: `Exported data_DD.MM.YY.xlsx`
- Import: `P[MM]_FY[YY]_Subs_report DD.MM.YYYY.xlsx`

---

## 5. Workflow

```
[Operator] --sends report--> [Import/POL/ID/] --processed by SR_Macro--> [Export/PL/Operator/]
                                                         |
                                                         v
                                               [System Reports/Export/]
                                               (data aggregated for AIS upload)
```

### Step 1: Import operator reports
Cable/IPTV operators send monthly subscriber reports:
- `.xlsx` files are saved to `Import/POL/[operator_id]/`
- Associated email messages (`.msg`) are archived in the same folder

### Step 2: Processing by SR_Macro.xlsb
The Excel macro reads the imported files, validates the data, and generates reports in the format required by Disney.

### Step 3: Export reports to operators
Generated files are written to `Admin/Export/PL/[Operator Name]/`:
- `.xls` — subscriber report for the operator
- `.msg` — archived copy of the outbound email

### Step 4: System reporting
Aggregated data is exported to `System Reports/Export/` and imported into the central Disney system via `System Reports/Import/`.

---

## 6. Operator List

### Direct operators (Admin/Export/PL/)
| Operator | Notes |
|---|---|
| AP MEDIA | |
| ASTA-NET / ASTA GROUP | |
| AVIOS | Formerly KORBANK MEDIA C |
| Beskidzka Telewizja Kablowa Bestkabel | |
| BIALOGARDZKA SPOLDZIELNIA | |
| BYDGOSKA TK GAWEX / Gawex-Media | |
| CANAL+ POLSKA S.A. | |
| CASTOR ENTERPRISE | Formerly FERO AGNIESZKA BUDNER |
| CG Multimedia | |
| CHOPIN TELEWIZJA KABLOWA | Formerly TK CHOPIN |
| CITY NET / CITY SAT GORDZIELIK / CITYNET | |
| CYFROWY POLSAT S.A. | incl. IPLA and OTT |
| CZESTOCHOWSKA SM NASZA PRACA | |
| Debacom | |
| DOLSAT | |
| DOMEL MEDIA | |
| EAST & WEST | |
| ELART | Formerly Stanislaw Zakrzewski |
| ELEKTRONIX TELEKOGK Sp. k. | |
| ELPOS | |
| ELTRONIK | Formerly Eltronik Media Sp.k. |
| ELWICO SC | |
| EURONET / EURONET Norbert Saniewski Sp.J. | |
| G-NET T | Formerly WWNET Bozena Furgol |
| Global Media | |
| Gorzowska Telewizja Przewodowa | |
| GRABOWSKI MARIUSZ INTERARENA / InterArena | |
| HORYZONT MEDIA SA | |
| HRUBIESZOWSKA TELEWIZJA KABLOWA SC | |
| IDEALAN | Formerly IDEALAN SPOLKA JAWNA |
| IMPERIUM TELECOM CZESLAW CHLEWICKI | |
| INEA S.A. | DC, NG & FOX |
| INFO-ART | |
| INSANT S.C. | J.Jasinski, R.Mieszkowicz |
| Integra, Pawel Mroz | |
| INTERNET UNION SA | |
| ITV MEDIA | |
| JG Satel | |
| JMDI JACEK MALESZKO | |
| Karpacka Spoldzielnia Mieszkaniowa | |
| KOBA | |
| Konecka Spoldzielnia Mieszkaniowa | |
| Korbank Data Center | |
| Lnet | |
| MARINEX-AMPOL 2 TELEWIZJA KABLOWA | |
| MAZOWIECKIE SS (KONSTANCIN NET PL) | |
| Media Management Europe Sp. k. | |
| MEDIASAT S.C. | |
| MEGOGO.PL | |
| METROPORT | |
| Miedzyzakladowa SM Chemik | |
| MLODZIEZOWA SM / MSM METALOWIEC | |
| Multiplay Sp. K. | |
| Myszkowska Telewizja Kablowa | |
| **Nasza Wizja** | Distribution sub-platform (see below) |

### Operators under Nasza Wizja (Admin/Export/PL/Nasza Wizja/)
| Operator | Notes |
|---|---|
| Akasha | |
| ALFAFIBER | |
| AMBIT | |
| AMPLUS | |
| ANetConnect | |
| AP MEDIA | |
| AVIOS | |
| BESKID MEDIA | |
| DAMIAN MURZYNOWSKI NASZASIEC.NET | |
| EZBIT | |
| FIBERWAY | |
| FINEMEDIA | |
| Firma Handlowa Giga | |
| HAKER MATEUSZ DYMITRUK | |
| HYPERNET | |
| INET CENTRUM | |
| INSTALNET SZABAT | |
| INTB SP ZOO | |
| INTERQ | |
| INTERTELL | |
| KM-NET | |
| KOMPEX | |
| LANNET S.C. | |
| LIQUID SYSTEMS | |
| MATRONIX Marcin Moczulski | |
| Metrosan | |
| MTM-INFO | |
| MULTICOM | |
| NETSERVICE | |
| NOWOGROD.NET | |
| ORION | |
| Shentel | |
| TONETIC GROUP | |

---

## 7. System Reports

### System Reports/Import/
Reports downloaded from the central AIS system. Naming convention: `P[Period]_FY[Fiscal Year]_Subs_report [date].xlsx`

| File | Description |
|---|---|
| `P05_FY24_Subs_report 21.02.2024.xlsx` | Period 5 / FY2024 |
| `P07_FY24_Subs_report 24.04.2024.xlsx` | Period 7 / FY2024 |
| `P08_FY24_Subs_report 01.05.2024.xlsx` | Period 8 / FY2024 |

### System Reports/Export/
Data exported to the central system. Naming convention: `Exported data_[DD.MM.YY].xlsx`

| File | Export date |
|---|---|
| `Exported data_11.04.24.xlsx` | 11 April 2024 |
| `Exported data_16.04.24.xlsx` | 16 April 2024 |
| `Exported data_25.04.24.xlsx` | 25 April 2024 |
| `Exported data_13.05.24.xlsx` | 13 May 2024 |
| `Exported data_30.05.24.xlsx` | 30 May 2024 |

---

## Technical Notes

- The `.xlsb` macro file (Excel Binary Workbook) requires Microsoft Excel with VBA macro execution enabled
- `.msg` files are Outlook messages — they require Microsoft Outlook or a compatible reader
- `folder create.bat` must be run on Windows from inside the `Import/POL/` directory
- `SR_Macro - Shortcut.lnk` assumes a Windows installation — the target path may need updating if files are moved