Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
93.06% |
161 / 173 |
|
58.82% |
10 / 17 |
CRAP | |
0.00% |
0 / 1 |
| SalesBillMapper | |
93.06% |
161 / 173 |
|
58.82% |
10 / 17 |
26.23 | |
0.00% |
0 / 1 |
| getSalesBeforePivot | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| getSalesAfterPivot | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| getSalesByItemId | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
1 | |||
| getSalesByClientId | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| getAvgSalesPriceByItemId | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
2 | |||
| getLastOrderDateByItemId | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
2 | |||
| getLastOrderDateByClientId | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
2 | |||
| getItemRetentionRate | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| getItemLivetimeValue | |
0.00% |
0 / 1 |
|
0.00% |
0 / 1 |
2 | |||
| getNewestItemInvoices | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
2.01 | |||
| getNewestClientInvoices | |
85.71% |
6 / 7 |
|
0.00% |
0 / 1 |
2.01 | |||
| getItemTopClients | |
77.78% |
21 / 27 |
|
0.00% |
0 / 1 |
3.10 | |||
| getItemBills | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
2.01 | |||
| getClientItem | |
88.89% |
8 / 9 |
|
0.00% |
0 / 1 |
2.01 | |||
| getItemCountrySales | |
100.00% |
17 / 17 |
|
100.00% |
1 / 1 |
1 | |||
| getItemMonthlySalesCosts | |
100.00% |
16 / 16 |
|
100.00% |
1 / 1 |
1 | |||
| getClientMonthlySalesCosts | |
100.00% |
14 / 14 |
|
100.00% |
1 / 1 |
1 | |||
| 1 | <?php |
| 2 | /** |
| 3 | * Jingga |
| 4 | * |
| 5 | * PHP Version 8.1 |
| 6 | * |
| 7 | * @package Modules\Billing\Models |
| 8 | * @copyright Dennis Eichhorn |
| 9 | * @license OMS License 2.0 |
| 10 | * @version 1.0.0 |
| 11 | * @link https://jingga.app |
| 12 | */ |
| 13 | declare(strict_types=1); |
| 14 | |
| 15 | namespace Modules\Billing\Models; |
| 16 | |
| 17 | use Modules\ClientManagement\Models\ClientMapper; |
| 18 | use phpOMS\DataStorage\Database\Query\Builder; |
| 19 | use phpOMS\Localization\Defaults\CountryMapper; |
| 20 | use phpOMS\Stdlib\Base\FloatInt; |
| 21 | |
| 22 | /** |
| 23 | * Mapper class. |
| 24 | * |
| 25 | * @package Modules\Billing\Models |
| 26 | * @license OMS License 2.0 |
| 27 | * @link https://jingga.app |
| 28 | * @since 1.0.0 |
| 29 | */ |
| 30 | final class SalesBillMapper extends BillMapper |
| 31 | { |
| 32 | /** |
| 33 | * Model to use by the mapper. |
| 34 | * |
| 35 | * @var class-string<T> |
| 36 | * @since 1.0.0 |
| 37 | */ |
| 38 | public const MODEL = Bill::class; |
| 39 | |
| 40 | /** |
| 41 | * Placeholder |
| 42 | */ |
| 43 | public static function getSalesBeforePivot( |
| 44 | mixed $pivot, |
| 45 | string $column = null, |
| 46 | int $limit = 50, |
| 47 | int $depth = 3, |
| 48 | Builder $query = null |
| 49 | ) : array |
| 50 | { |
| 51 | return self::getAll() |
| 52 | ->with('type') |
| 53 | ->where('id', $pivot, '<') |
| 54 | ->where('type/transferType', BillTransferType::SALES) |
| 55 | ->limit($limit) |
| 56 | ->execute(); |
| 57 | } |
| 58 | |
| 59 | /** |
| 60 | * Placeholder |
| 61 | */ |
| 62 | public static function getSalesAfterPivot( |
| 63 | mixed $pivot, |
| 64 | string $column = null, |
| 65 | int $limit = 50, |
| 66 | int $depth = 3, |
| 67 | Builder $query = null |
| 68 | ) : array |
| 69 | { |
| 70 | return self::getAll() |
| 71 | ->with('type') |
| 72 | ->where('id', $pivot, '>') |
| 73 | ->where('type/transferType', BillTransferType::SALES) |
| 74 | ->limit($limit) |
| 75 | ->execute(); |
| 76 | } |
| 77 | |
| 78 | /** |
| 79 | * Placeholder |
| 80 | */ |
| 81 | public static function getSalesByItemId(int $id, \DateTime $start, \DateTime $end) : FloatInt |
| 82 | { |
| 83 | $query = new Builder(self::$db); |
| 84 | |
| 85 | /** @var array $result */ |
| 86 | $result = $query->select('SUM(billing_bill_element_total_netsalesprice)') |
| 87 | ->from(self::TABLE) |
| 88 | ->leftJoin(BillElementMapper::TABLE) |
| 89 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 90 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 91 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 92 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 93 | ->execute() |
| 94 | ?->fetch() ?? []; |
| 95 | |
| 96 | return new FloatInt((int) ($result[0] ?? 0)); |
| 97 | } |
| 98 | |
| 99 | /** |
| 100 | * Placeholder |
| 101 | */ |
| 102 | public static function getSalesByClientId(int $id, \DateTime $start, \DateTime $end) : FloatInt |
| 103 | { |
| 104 | $query = new Builder(self::$db); |
| 105 | |
| 106 | /** @var array $result */ |
| 107 | $result = $query->select('SUM(billing_bill_netsales)') |
| 108 | ->from(self::TABLE) |
| 109 | ->where(self::TABLE . '.billing_bill_client', '=', $id) |
| 110 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 111 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 112 | ->execute() |
| 113 | ?->fetch() ?? []; |
| 114 | |
| 115 | return new FloatInt((int) ($result[0] ?? 0)); |
| 116 | } |
| 117 | |
| 118 | /** |
| 119 | * Placeholder |
| 120 | */ |
| 121 | public static function getAvgSalesPriceByItemId(int $id, \DateTime $start, \DateTime $end) : FloatInt |
| 122 | { |
| 123 | $query = new Builder(self::$db); |
| 124 | |
| 125 | /** @var array $result */ |
| 126 | $result = $query->select('SUM(billing_bill_element_single_netsalesprice)', 'COUNT(billing_bill_element_total_netsalesprice)') |
| 127 | ->from(self::TABLE) |
| 128 | ->leftJoin(BillElementMapper::TABLE) |
| 129 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 130 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 131 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 132 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 133 | ->execute() |
| 134 | ?->fetch() ?? []; |
| 135 | |
| 136 | return new FloatInt(((int) ($result[1] ?? 0)) === 0 ? 0 : (int) (((int) ($result[0] ?? 0)) / ((int) $result[1]))); |
| 137 | } |
| 138 | |
| 139 | /** |
| 140 | * Placeholder |
| 141 | */ |
| 142 | public static function getLastOrderDateByItemId(int $id) : ?\DateTimeImmutable |
| 143 | { |
| 144 | // @todo: only delivers/invoice/production (no offers ...) |
| 145 | $query = new Builder(self::$db); |
| 146 | |
| 147 | /** @var false|array $result */ |
| 148 | $result = $query->select('billing_bill_performance_date') |
| 149 | ->from(self::TABLE) |
| 150 | ->leftJoin(BillElementMapper::TABLE) |
| 151 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 152 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 153 | ->orderBy('billing_bill_id', 'DESC') |
| 154 | ->limit(1) |
| 155 | ->execute() |
| 156 | ?->fetch() ?? false; |
| 157 | |
| 158 | return $result === false ? null : new \DateTimeImmutable($result[0]); |
| 159 | } |
| 160 | |
| 161 | /** |
| 162 | * Placeholder |
| 163 | */ |
| 164 | public static function getLastOrderDateByClientId(int $id) : ?\DateTimeImmutable |
| 165 | { |
| 166 | // @todo: only delivers/invoice/production (no offers ...) |
| 167 | $query = new Builder(self::$db); |
| 168 | |
| 169 | /** @var false|array $result */ |
| 170 | $result = $query->select('billing_bill_performance_date') |
| 171 | ->from(self::TABLE) |
| 172 | ->where(self::TABLE . '.billing_bill_client', '=', $id) |
| 173 | ->orderBy('billing_bill_id', 'DESC') |
| 174 | ->limit(1) |
| 175 | ->execute() |
| 176 | ?->fetch() ?? false; |
| 177 | |
| 178 | return $result === false ? null : new \DateTimeImmutable($result[0]); |
| 179 | } |
| 180 | |
| 181 | /** |
| 182 | * Placeholder |
| 183 | */ |
| 184 | public static function getItemRetentionRate(int $id, \DateTime $start, \DateTime $end) : float |
| 185 | { |
| 186 | return 0.0; |
| 187 | } |
| 188 | |
| 189 | /** |
| 190 | * Placeholder |
| 191 | */ |
| 192 | public static function getItemLivetimeValue(int $id, \DateTime $start, \DateTime $end) : FloatInt |
| 193 | { |
| 194 | return new FloatInt(); |
| 195 | } |
| 196 | |
| 197 | /** |
| 198 | * Placeholder |
| 199 | */ |
| 200 | public static function getNewestItemInvoices(int $id, int $limit = 10) : array |
| 201 | { |
| 202 | $query = self::getQuery(); |
| 203 | $query->leftJoin(BillElementMapper::TABLE, BillElementMapper::TABLE . '_d1') |
| 204 | ->on(self::TABLE . '_d1.billing_bill_id', '=', BillElementMapper::TABLE . '_d1.billing_bill_element_bill') |
| 205 | ->where(BillElementMapper::TABLE . '_d1.billing_bill_element_item', '=', $id) |
| 206 | ->limit($limit); |
| 207 | |
| 208 | /** @phpstan-ignore-next-line */ |
| 209 | if (!empty(self::CREATED_AT)) { |
| 210 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::CREATED_AT]['name'], 'DESC'); |
| 211 | } else { |
| 212 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::PRIMARYFIELD]['name'], 'DESC'); |
| 213 | } |
| 214 | |
| 215 | return self::getAll()->execute($query); |
| 216 | } |
| 217 | |
| 218 | /** |
| 219 | * Placeholder |
| 220 | */ |
| 221 | public static function getNewestClientInvoices(int $id, int $limit = 10) : array |
| 222 | { |
| 223 | $query = self::getQuery(); |
| 224 | $query->where(self::TABLE . '_d1.billing_bill_client', '=', $id) |
| 225 | ->limit($limit); |
| 226 | |
| 227 | /** @phpstan-ignore-next-line */ |
| 228 | if (!empty(self::CREATED_AT)) { |
| 229 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::CREATED_AT]['name'], 'DESC'); |
| 230 | } else { |
| 231 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::PRIMARYFIELD]['name'], 'DESC'); |
| 232 | } |
| 233 | |
| 234 | return self::getAll()->execute($query); |
| 235 | } |
| 236 | |
| 237 | /** |
| 238 | * Placeholder |
| 239 | */ |
| 240 | public static function getItemTopClients(int $id, \DateTime $start, \DateTime $end, int $limit = 10) : array |
| 241 | { |
| 242 | $query = new Builder(self::$db); |
| 243 | $query->selectAs(ClientMapper::TABLE . '.clientmgmt_client_id', 'client') |
| 244 | ->selectAs('SUM(' . BillElementMapper::TABLE . '.billing_bill_element_total_netsalesprice)', 'net_sales') |
| 245 | ->from(ClientMapper::TABLE) |
| 246 | ->leftJoin(self::TABLE) |
| 247 | ->on(ClientMapper::TABLE . '.clientmgmt_client_id', '=', self::TABLE . '.billing_bill_client') |
| 248 | ->leftJoin(BillElementMapper::TABLE) |
| 249 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 250 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 251 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 252 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 253 | ->orderBy('net_sales', 'DESC') |
| 254 | ->limit($limit) |
| 255 | ->groupBy('client'); |
| 256 | |
| 257 | $stmt = $query->execute(); |
| 258 | $data = $stmt?->fetchAll() ?? []; |
| 259 | |
| 260 | $clientIds = []; |
| 261 | foreach ($data as $client) { |
| 262 | $clientIds[] = $client['client']; |
| 263 | } |
| 264 | |
| 265 | $clients = []; |
| 266 | if (!empty($clientIds)) { |
| 267 | $clients = ClientMapper::getAll() |
| 268 | ->with('account') |
| 269 | ->with('mainAddress') |
| 270 | ->where('id', $clientIds, 'IN') |
| 271 | ->execute(); |
| 272 | } |
| 273 | |
| 274 | return [$clients, $data]; |
| 275 | } |
| 276 | |
| 277 | /** |
| 278 | * Placeholder |
| 279 | */ |
| 280 | public static function getItemBills(int $id, \DateTime $start, \DateTime $end) : array |
| 281 | { |
| 282 | $query = self::getQuery(); |
| 283 | $query->leftJoin(BillElementMapper::TABLE, BillElementMapper::TABLE . '_d1') |
| 284 | ->on(self::TABLE . '_d1.billing_bill_id', '=', BillElementMapper::TABLE . '_d1.billing_bill_element_bill') |
| 285 | ->where(BillElementMapper::TABLE . '_d1.billing_bill_element_item', '=', $id) |
| 286 | ->limit($limit = 10); |
| 287 | |
| 288 | /** @phpstan-ignore-next-line */ |
| 289 | if (!empty(self::CREATED_AT)) { |
| 290 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::CREATED_AT]['name'], 'DESC'); |
| 291 | } else { |
| 292 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::PRIMARYFIELD]['name'], 'DESC'); |
| 293 | } |
| 294 | |
| 295 | return self::getAll()->execute($query); |
| 296 | } |
| 297 | |
| 298 | /** |
| 299 | * Placeholder |
| 300 | */ |
| 301 | public static function getClientItem(int $client, \DateTime $start, \DateTime $end) : array |
| 302 | { |
| 303 | $query = BillElementMapper::getQuery(); |
| 304 | $query->leftJoin(self::TABLE, self::TABLE . '_d1') |
| 305 | ->on(BillElementMapper::TABLE . '_d1.billing_bill_element_bill', '=', self::TABLE . '_d1.billing_bill_id') |
| 306 | ->where(self::TABLE . '_d1.billing_bill_client', '=', $client) |
| 307 | ->limit($limit = 10); |
| 308 | |
| 309 | /** @phpstan-ignore-next-line */ |
| 310 | if (!empty(self::CREATED_AT)) { |
| 311 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::CREATED_AT]['name'], 'DESC'); |
| 312 | } else { |
| 313 | $query->orderBy(self::TABLE . '_d1.' . self::COLUMNS[self::PRIMARYFIELD]['name'], 'DESC'); |
| 314 | } |
| 315 | |
| 316 | return BillElementMapper::getAll()->execute($query); |
| 317 | } |
| 318 | |
| 319 | /** |
| 320 | * Placeholder |
| 321 | */ |
| 322 | public static function getItemCountrySales(int $id, \DateTime $start, \DateTime $end, int $limit = 10) : array |
| 323 | { |
| 324 | $query = new Builder(self::$db); |
| 325 | $result = $query->select(CountryMapper::TABLE . '.country_code2') |
| 326 | ->selectAs('SUM(billing_bill_element_total_netsalesprice)', 'net_sales') |
| 327 | ->from(self::TABLE) |
| 328 | ->leftJoin(BillElementMapper::TABLE) |
| 329 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 330 | ->leftJoin(CountryMapper::TABLE) |
| 331 | ->on(self::TABLE . '.billing_bill_billCountry', '=', CountryMapper::TABLE . '.country_code2') |
| 332 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 333 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 334 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 335 | ->groupBy(CountryMapper::TABLE . '.country_code2') |
| 336 | ->orderBy('net_sales', 'DESC') |
| 337 | ->limit($limit) |
| 338 | ->execute() |
| 339 | ?->fetchAll(\PDO::FETCH_KEY_PAIR); |
| 340 | |
| 341 | return $result ?? []; |
| 342 | } |
| 343 | |
| 344 | /** |
| 345 | * Placeholder |
| 346 | */ |
| 347 | public static function getItemMonthlySalesCosts(int $id, \DateTime $start, \DateTime $end) : array |
| 348 | { |
| 349 | $query = new Builder(self::$db); |
| 350 | $result = $query->selectAs('SUM(billing_bill_element_total_netsalesprice)', 'net_sales') |
| 351 | ->selectAs('SUM(billing_bill_element_total_netpurchaseprice)', 'net_costs') |
| 352 | ->selectAs('YEAR(billing_bill_performance_date)', 'year') |
| 353 | ->selectAs('MONTH(billing_bill_performance_date)', 'month') |
| 354 | ->from(self::TABLE) |
| 355 | ->leftJoin(BillElementMapper::TABLE) |
| 356 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 357 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 358 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 359 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 360 | ->groupBy('year', 'month') |
| 361 | ->orderBy(['year', 'month'], ['ASC', 'ASC']) |
| 362 | ->execute() |
| 363 | ?->fetchAll(); |
| 364 | |
| 365 | return $result ?? []; |
| 366 | } |
| 367 | |
| 368 | /** |
| 369 | * Placeholder |
| 370 | */ |
| 371 | public static function getClientMonthlySalesCosts(int $id, \DateTime $start, \DateTime $end) : array |
| 372 | { |
| 373 | $query = new Builder(self::$db); |
| 374 | $result = $query->selectAs('SUM(billing_bill_netsales)', 'net_sales') |
| 375 | ->selectAs('SUM(billing_bill_netcosts)', 'net_costs') |
| 376 | ->selectAs('YEAR(billing_bill_performance_date)', 'year') |
| 377 | ->selectAs('MONTH(billing_bill_performance_date)', 'month') |
| 378 | ->from(self::TABLE) |
| 379 | ->where(self::TABLE . '.billing_bill_client', '=', $id) |
| 380 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 381 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 382 | ->groupBy('year', 'month') |
| 383 | ->orderBy(['year', 'month'], ['ASC', 'ASC']) |
| 384 | ->execute() |
| 385 | ?->fetchAll(); |
| 386 | |
| 387 | return $result ?? []; |
| 388 | } |
| 389 | } |