288 lines
9.3 KiB
Markdown
288 lines
9.3 KiB
Markdown
# 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
|