CoolCMS API

ProductStatisticsService
in package
uses StatisticsSeriesTrait

FinalYes

Statistics of a single product, broken down by its sales sets (admin action `:Admin:Eshop:Product:statistics`).

Standalone raw-SQL service like OrderStatisticsService (aggregation needs numbers, not entities); the shared axis mechanics (buckets, labels, pivot()) live in StatisticsSeriesTrait.

A product set here is a sales card with type = PRODUCT(1) attached to a variant of this product (ProductSet::$variant, 1:1), the same definition the variant list in product edit uses (ProductPresenter::findSellableSet()). Bundles that contain the variant via set_item_relations are deliberately not counted: order_items carry the bundle's product_set_id, so splitting a bundle sale into components would be a guess.

IMPORTANT: soft-deleted sets and variants (deleted = 1) stay in the output. Their historical orders are real revenue of the product, and hiding the row would silently understate the numbers. The row carries a deleted flag so the table shows why it is gone from the variants.

Sales are read from the order_items snapshot (frozen price and quantity), views from product_set_statistics_v2 (upserted by the front detail); these are two independent axes that together give the conversion in one table row.

Table of Contents

Constants

BUCKETS  : array<string|int, mixed> = ['day' => '%Y-%m-%d', 'week' => '%x-%v', 'month...
Allowed granularities mapped to MySQL `DATE_FORMAT` masks.
DEFAULT_LANGUAGE_ID  : int = 1
Fallback language for set names in chart and table labels.

Properties

$em  : EntityManagerInterface
$setsCache  : array<string, array<int, array{setId: int, code: string, name: string, activated: bool, deleted: bool}>>
Memoizes {@see productSets()} per instance: every series asks for the set axis twice (for `IN (:setIds)` and for labels) and `summary()` a third time, so without the cache a page with three charts would run 7 identical queries.

Methods

__construct()  : mixed
productSets()  : array<int, array{setId: int, code: string, name: string, activated: bool, deleted: bool}>
Returns the sales sets of the product (`type = 1` via the variant), the axis of all other queries. The label is the set name in `$languageId`, falling back to the set code, then to `#id`.
revenue()  : array{labels: list, datasets: list}>}
Chart 3: revenue (with VAT) over time, one series per sales set and order currency.
soldQuantity()  : array{labels: list, datasets: list}>}
Chart 2: sold quantity over time, one series per sales set (`order_items` snapshot, non-canceled orders).
summary()  : array{rows: list, conversion: ?float}>, totals: array{views: int, orders: int, quantity: int, revenue: array, conversion: ?float}}
Summary table: one row per sales set for the whole period: views, order count (`DISTINCT`: two items of one order are one order), sold quantity, revenue and conversion (orders / views).
views()  : array{labels: list, datasets: list}>}
Chart 1: detail views over time, one series per sales set (`statistics_v2`, type `views`). A set with no view is omitted from the chart (an empty series only clutters the legend) and has zero in the summary table.
bucketExpr()  : string
Returns the MySQL `DATE_FORMAT` expression for the granularity; it must produce the same key as {@see bucketLabels()}.
bucketLabels()  : array<int|string, string>
Builds the `bucketKey => label` map for the whole `from..to` range, including days and months without a single order (the x axis must have no gaps).
conversion()  : float|null
Conversion in percent (orders / views); `null` = no views (division by zero).
dayEnd()  : string
dayStart()  : string
pivot()  : array{labels: list, datasets: list}>}
Pivots flat SQL rows (bucket x dimension) into the ChartJS structure: one dataset per dimension value (status/shipper/payment/product set), padded with zero buckets for days without data (otherwise the chart would have gaps).
pivotBySet()  : array{labels: list, datasets: list}>}
Adds the set label to the rows and hands them to the shared {@see StatisticsSeriesTrait::pivot()}. The label comes from the catalog (live set name), not from the order snapshot, since it is only a chart legend, not an accounting value.
salesSeries()  : array{labels: list, datasets: list}>}
Shared body of both sales series ({@see soldQuantity()}, {@see revenue()}); they differ only in the aggregate expression and in whether the value is monetary.
setIds()  : array<int, int>
Returns the ids of the product's sales sets, the input of `IN (:setIds)`. An empty result means "the product has no sales card" and must be handled before the query: `IN ()` is a syntax error in MySQL.
toFloat()  : float
toInt()  : int
toStr()  : string

Constants

BUCKETS

Allowed granularities mapped to MySQL `DATE_FORMAT` masks.

private array<string|int, mixed> BUCKETS = ['day' => '%Y-%m-%d', 'week' => '%x-%v', 'month' => '%Y-%m', 'year' => '%Y']

DEFAULT_LANGUAGE_ID

Fallback language for set names in chart and table labels.

private int DEFAULT_LANGUAGE_ID = 1

Properties

$setsCache

Memoizes {@see productSets()} per instance: every series asks for the set axis twice (for `IN (:setIds)` and for labels) and `summary()` a third time, so without the cache a page with three charts would run 7 identical queries.

private array<string, array<int, array{setId: int, code: string, name: string, activated: bool, deleted: bool}>> $setsCache = []

Methods

__construct()

public __construct(EntityManagerInterface $em) : mixed
Parameters
$em : EntityManagerInterface

productSets()

Returns the sales sets of the product (`type = 1` via the variant), the axis of all other queries. The label is the set name in `$languageId`, falling back to the set code, then to `#id`.

public productSets(int $productId[, int $languageId = self::DEFAULT_LANGUAGE_ID ]) : array<int, array{setId: int, code: string, name: string, activated: bool, deleted: bool}>
Parameters
$productId : int
$languageId : int = self::DEFAULT_LANGUAGE_ID
Return values
array<int, array{setId: int, code: string, name: string, activated: bool, deleted: bool}>

revenue()

Chart 3: revenue (with VAT) over time, one series per sales set and order currency.

public revenue(int $productId, DateTimeInterface $from, DateTimeInterface $to[, string $granularity = 'month' ][, int $languageId = self::DEFAULT_LANGUAGE_ID ]) : array{labels: list, datasets: list}>}

IMPORTANT: the currency is part of the dimension on purpose. order_items.price_with_vat is in the currency of its order, so a plain SUM() over orders in two currencies yields a meaningless number. A single-currency shop sees one series per set (with a currency code suffix); a multi-currency shop gets correct per-currency series.

Parameters
$productId : int
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string = 'month'
$languageId : int = self::DEFAULT_LANGUAGE_ID
Return values
array{labels: list, datasets: list}>}

soldQuantity()

Chart 2: sold quantity over time, one series per sales set (`order_items` snapshot, non-canceled orders).

public soldQuantity(int $productId, DateTimeInterface $from, DateTimeInterface $to[, string $granularity = 'month' ][, int $languageId = self::DEFAULT_LANGUAGE_ID ]) : array{labels: list, datasets: list}>}
Parameters
$productId : int
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string = 'month'
$languageId : int = self::DEFAULT_LANGUAGE_ID
Return values
array{labels: list, datasets: list}>}

summary()

Summary table: one row per sales set for the whole period: views, order count (`DISTINCT`: two items of one order are one order), sold quantity, revenue and conversion (orders / views).

public summary(int $productId, DateTimeInterface $from, DateTimeInterface $to[, int $languageId = self::DEFAULT_LANGUAGE_ID ]) : array{rows: list, conversion: ?float}>, totals: array{views: int, orders: int, quantity: int, revenue: array, conversion: ?float}}

The three aggregations (views, sales, sets) run as three queries and are joined in PHP: one SQL with two LEFT JOINs on two 1:N relations of different grain would multiply the values (fan-out) and the table would not show it.

IMPORTANT: revenue is a map currency code => amount, never a single number; adding orders of two currencies into one value is meaningless (see revenue()).

Parameters
$productId : int
$from : DateTimeInterface
$to : DateTimeInterface
$languageId : int = self::DEFAULT_LANGUAGE_ID
Return values
array{rows: list, conversion: ?float}>, totals: array{views: int, orders: int, quantity: int, revenue: array, conversion: ?float}}

views()

Chart 1: detail views over time, one series per sales set (`statistics_v2`, type `views`). A set with no view is omitted from the chart (an empty series only clutters the legend) and has zero in the summary table.

public views(int $productId, DateTimeInterface $from, DateTimeInterface $to[, string $granularity = 'month' ][, int $languageId = self::DEFAULT_LANGUAGE_ID ]) : array{labels: list, datasets: list}>}
Parameters
$productId : int
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string = 'month'
$languageId : int = self::DEFAULT_LANGUAGE_ID
Return values
array{labels: list, datasets: list}>}

bucketExpr()

Returns the MySQL `DATE_FORMAT` expression for the granularity; it must produce the same key as {@see bucketLabels()}.

private bucketExpr(string $column, string $granularity) : string
Parameters
$column : string
$granularity : string
Return values
string

bucketLabels()

Builds the `bucketKey => label` map for the whole `from..to` range, including days and months without a single order (the x axis must have no gaps).

private bucketLabels(DateTimeInterface $from, DateTimeInterface $to, string $granularity) : array<int|string, string>

The key is always a string (DATE_FORMAT returns a string), but PHP casts purely numeric string keys (such as year "2025") to int, hence array<int|string, string>.

Parameters
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string
Return values
array<int|string, string>

conversion()

Conversion in percent (orders / views); `null` = no views (division by zero).

private conversion(int $orders, int $views) : float|null
Parameters
$orders : int
$views : int
Return values
float|null

dayEnd()

private dayEnd(DateTimeInterface $date) : string
Parameters
$date : DateTimeInterface
Return values
string

dayStart()

private dayStart(DateTimeInterface $date) : string
Parameters
$date : DateTimeInterface
Return values
string

pivot()

Pivots flat SQL rows (bucket x dimension) into the ChartJS structure: one dataset per dimension value (status/shipper/payment/product set), padded with zero buckets for days without data (otherwise the chart would have gaps).

private pivot(array<int, array<string, mixed>> $rows, DateTimeInterface $from, DateTimeInterface $to, string $granularity, string $idField, string $keyField, string $nameField, string $valueField[, bool $isSum = false ]) : array{labels: list, datasets: list}>}
Parameters
$rows : array<int, array<string, mixed>>
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string
$idField : string
$keyField : string
$nameField : string
$valueField : string
$isSum : bool = false
Return values
array{labels: list, datasets: list}>}

pivotBySet()

Adds the set label to the rows and hands them to the shared {@see StatisticsSeriesTrait::pivot()}. The label comes from the catalog (live set name), not from the order snapshot, since it is only a chart legend, not an accounting value.

private pivotBySet(array<int, array<string, mixed>> $rows, int $productId, DateTimeInterface $from, DateTimeInterface $to, string $granularity, int $languageId, bool $isSum) : array{labels: list, datasets: list}>}
Parameters
$rows : array<int, array<string, mixed>>
$productId : int
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string
$languageId : int
$isSum : bool
Return values
array{labels: list, datasets: list}>}

salesSeries()

Shared body of both sales series ({@see soldQuantity()}, {@see revenue()}); they differ only in the aggregate expression and in whether the value is monetary.

private salesSeries(int $productId, DateTimeInterface $from, DateTimeInterface $to, string $granularity, int $languageId, string $aggregateExpr, bool $isSum, bool $perCurrency) : array{labels: list, datasets: list}>}
Parameters
$productId : int
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string
$languageId : int
$aggregateExpr : string
$isSum : bool
$perCurrency : bool
Return values
array{labels: list, datasets: list}>}

setIds()

Returns the ids of the product's sales sets, the input of `IN (:setIds)`. An empty result means "the product has no sales card" and must be handled before the query: `IN ()` is a syntax error in MySQL.

private setIds(int $productId, int $languageId) : array<int, int>
Parameters
$productId : int
$languageId : int
Return values
array<int, int>

toFloat()

private toFloat(mixed $value) : float
Parameters
$value : mixed
Return values
float

toStr()

private toStr(mixed $value[, string $default = '' ]) : string
Parameters
$value : mixed
$default : string = ''
Return values
string
On this page

Search results