CoolCMS API

OrderStatisticsService
in package
uses StatisticsSeriesTrait

FinalYes

Aggregates the data for the 8 dashboard charts.

Standalone raw-SQL service (fetchAllAssociative(), no ORM hydration): aggregate queries need numbers for ChartJS, not entities.

IMPORTANT: always reads the order snapshot tables (cms_mod_eshop_order_items, cms_mod_eshop_order_shippers, cms_mod_eshop_order_payment_methods; name and price frozen at order time), never the live catalog; otherwise order prices and names would be recomputed retroactively. Shipper, payment and status names are read from the current code list (text tables) only as chart axis labels: renaming a tariff or status later relabels old orders in the chart, which is acceptable for a dashboard but not for prices.

Canceled orders (is_cancelled = 1) are excluded from revenue and count charts, since a canceled order is not a sale. Exception: orderCounts() has no such filter and shows the cancelled status as its own series.

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  : mixed = 1
Fallback language for status, shipper and payment names in chart labels.

Properties

$em  : EntityManagerInterface

Methods

__construct()  : mixed
complaintCounts()  : array{labels: list, datasets: list}>}
Chart 8: complaint count over time, split by status (same shape as `orderCounts()`).
orderCounts()  : array{labels: list, datasets: list}>}
Chart 1: order count over time, split by status.
orderRevenue()  : array{labels: list, datasets: list}>}
Chart 2: revenue (rounded total with VAT) over time, non-canceled orders only, one pair of series (revenue, average order value) per order currency.
paymentMethodCounts()  : array{labels: list, datasets: list}>}
Chart 6: order count by payment method over time.
paymentMethodRevenue()  : array{labels: list, datasets: list}>}
Chart 7: payment revenue by method over time.
shipperCounts()  : array{labels: list, datasets: list}>}
Chart 4: order count by shipping tariff over time.
shipperRevenue()  : array{labels: list, datasets: list}>}
Chart 5: shipping revenue by tariff over time.
topProducts()  : array{labels: list, datasets: list}>}
Chart 3: best-selling items. Uses the snapshot name from `order_items.name`, not the live catalog, so a deleted or renamed set does not distort the historical ranking. Non-canceled orders only, aggregated by the snapshot name (a set that no longer exists still counts under its snapshot name).
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).
buildRevenueSeries()  : array{labels: list, datasets: list}>}
Pivots revenue rows (`bucket`, `currency_id`, `revenue`, `cnt`) into a revenue and an average-order-value series per currency, in ascending currency id order. A currency without a label (missing from the code list) is labelled `#<id>`.
buildTopProductSeries()  : array{labels: list, datasets: list}>}
currencyLabel()  : string
dayEnd()  : string
dayStart()  : string
dimensionSeries()  : array{labels: list, datasets: list}>}
Shared skeleton for charts 4-7 (shipper or payment, count or revenue): the query shape is the same, only the table, join and aggregate expression differ. Revenue (`isSum`) is split per order currency ({@see splitRowsPerCurrency()}).
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).
splitRowsPerCurrency()  : array<int, array<string, mixed>>
Turns the dimension of money rows into dimension x currency: `dim_id` becomes `<dim_id>.<currencyId>` and the name gets the currency label (`DPD CZK`), so `pivot()` builds one series per tariff/method and currency.
textLanguageColumn()  : string
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 status, shipper and payment names in chart labels.

private mixed DEFAULT_LANGUAGE_ID = 1

Properties

Methods

__construct()

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

complaintCounts()

Chart 8: complaint count over time, split by status (same shape as `orderCounts()`).

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

orderCounts()

Chart 1: order count over time, split by status.

public orderCounts(DateTimeInterface $from, DateTimeInterface $to[, string $granularity = 'month' ][, array<string|int, int>|null $statusIds = null ][, int $languageId = self::DEFAULT_LANGUAGE_ID ]) : array{labels: list, datasets: list}>}
Parameters
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string = 'month'
$statusIds : array<string|int, int>|null = null

limit to these statuses; null = all

$languageId : int = self::DEFAULT_LANGUAGE_ID
Return values
array{labels: list, datasets: list}>}

orderRevenue()

Chart 2: revenue (rounded total with VAT) over time, non-canceled orders only, one pair of series (revenue, average order value) per order currency.

public orderRevenue(DateTimeInterface $from, DateTimeInterface $to[, string $granularity = 'month' ][, array<int, string> $currencyLabels = [] ]) : array{labels: list, datasets: list}>}

IMPORTANT: amounts in different currencies are never added up. Converting them would need the exchange rate valid on each order date, which the order snapshot does not store; today's exchange_rate would rewrite past revenue. A shop selling in one currency gets exactly two series as before, only the label carries the currency.

The currency labels come from the caller (System layer, CurrencyManager): this service reads only eshop tables.

Parameters
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string = 'month'
$currencyLabels : array<int, string> = []

currency id => label (CZK); an unknown id is shown as #<id>

Return values
array{labels: list, datasets: list}>}

paymentMethodCounts()

Chart 6: order count by payment method over time.

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

paymentMethodRevenue()

Chart 7: payment revenue by method over time.

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

One series per tariff/method and order currency (<id>.<currencyId>, label with the currency shortcut): amounts in different currencies are never added up.

Parameters
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string = 'month'
$languageId : int = self::DEFAULT_LANGUAGE_ID
$currencyLabels : array<int, string> = []

currency id => label (CZK); an unknown id is shown as #<id>

Return values
array{labels: list, datasets: list}>}

shipperCounts()

Chart 4: order count by shipping tariff over time.

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

shipperRevenue()

Chart 5: shipping revenue by tariff over time.

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

One series per tariff/method and order currency (<id>.<currencyId>, label with the currency shortcut): amounts in different currencies are never added up.

Parameters
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string = 'month'
$languageId : int = self::DEFAULT_LANGUAGE_ID
$currencyLabels : array<int, string> = []

currency id => label (CZK); an unknown id is shown as #<id>

Return values
array{labels: list, datasets: list}>}

topProducts()

Chart 3: best-selling items. Uses the snapshot name from `order_items.name`, not the live catalog, so a deleted or renamed set does not distort the historical ranking. Non-canceled orders only, aggregated by the snapshot name (a set that no longer exists still counts under its snapshot name).

public topProducts(DateTimeInterface $from, DateTimeInterface $to[, int $limit = 10 ][, array<int, string> $currencyLabels = [] ]) : array{labels: list, datasets: list}>}

The quantity is one series over all orders; the revenue is one series per order currency (revenue.<currencyId>, label with the currency shortcut), never a sum across currencies (order_items.price_with_vat is in the currency of its order). Same split as orderRevenue().

Parameters
$from : DateTimeInterface
$to : DateTimeInterface
$limit : int = 10
$currencyLabels : array<int, string> = []

currency id => label (CZK); an unknown id is shown as #<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>

buildRevenueSeries()

Pivots revenue rows (`bucket`, `currency_id`, `revenue`, `cnt`) into a revenue and an average-order-value series per currency, in ascending currency id order. A currency without a label (missing from the code list) is labelled `#<id>`.

private buildRevenueSeries(array<int, array<string, mixed>> $rows, array<int|string, string> $labels, array<int, string> $currencyLabels) : array{labels: list, datasets: list}>}
Parameters
$rows : array<int, array<string, mixed>>
$labels : array<int|string, string>

bucket key => axis label

$currencyLabels : array<int, string>
Return values
array{labels: list, datasets: list}>}

buildTopProductSeries()

private buildTopProductSeries(array<int, array<string, mixed>> $top, array<int, array<string, mixed>> $revenueRows, array<int, string> $currencyLabels) : array{labels: list, datasets: list}>}
Parameters
$top : array<int, array<string, mixed>>

rows name, qty in ranking order

$revenueRows : array<int, array<string, mixed>>

rows name, currency_id, revenue

$currencyLabels : array<int, string>
Return values
array{labels: list, datasets: list}>}

currencyLabel()

private currencyLabel(int $currencyId, array<int, string> $currencyLabels) : string
Parameters
$currencyId : int
$currencyLabels : array<int, string>
Return values
string

dayEnd()

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

dayStart()

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

dimensionSeries()

Shared skeleton for charts 4-7 (shipper or payment, count or revenue): the query shape is the same, only the table, join and aggregate expression differ. Revenue (`isSum`) is split per order currency ({@see splitRowsPerCurrency()}).

private dimensionSeries(string $dimIdColumn, string $dimNameColumn, string $fromTable, string $textJoinTable, string $textJoinIdColumn, string $aggExpr, DateTimeInterface $from, DateTimeInterface $to, string $granularity, int $languageId[, bool $isSum = false ][, array<int, string> $currencyLabels = [] ]) : array{labels: list, datasets: list}>}
Parameters
$dimIdColumn : string
$dimNameColumn : string
$fromTable : string
$textJoinTable : string
$textJoinIdColumn : string
$aggExpr : string
$from : DateTimeInterface
$to : DateTimeInterface
$granularity : string
$languageId : int
$isSum : bool = false
$currencyLabels : array<int, string> = []
Return values
array{labels: list, datasets: list}>}

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}>}

splitRowsPerCurrency()

Turns the dimension of money rows into dimension x currency: `dim_id` becomes `<dim_id>.<currencyId>` and the name gets the currency label (`DPD CZK`), so `pivot()` builds one series per tariff/method and currency.

private splitRowsPerCurrency(array<int, array<string, mixed>> $rows, array<int, string> $currencyLabels) : array<int, array<string, mixed>>
Parameters
$rows : array<int, array<string, mixed>>
$currencyLabels : array<int, string>
Return values
array<int, array<string, mixed>>

textLanguageColumn()

private textLanguageColumn(string $textJoinTable) : string
Parameters
$textJoinTable : string
Return values
string

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