Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
97.16% |
137 / 141 |
|
73.33% |
11 / 15 |
CRAP | |
0.00% |
0 / 1 |
| PurchaseBillMapper | |
97.16% |
137 / 141 |
|
73.33% |
11 / 15 |
21 | |
0.00% |
0 / 1 |
| getPurchaseBeforePivot | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| getPurchaseAfterPivot | |
100.00% |
6 / 6 |
|
100.00% |
1 / 1 |
1 | |||
| getPurchaseByItemId | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
1 | |||
| getPurchaseBySupplierId | |
100.00% |
9 / 9 |
|
100.00% |
1 / 1 |
1 | |||
| getAvgPurchasePriceByItemId | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
3 | |||
| getLastOrderDateByItemId | |
100.00% |
11 / 11 |
|
100.00% |
1 / 1 |
2 | |||
| getLastOrderDateBySupplierId | |
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 | |||
| getNewestSupplierInvoices | |
85.71% |
6 / 7 |
|
0.00% |
0 / 1 |
2.01 | |||
| getItemTopSuppliers | |
100.00% |
15 / 15 |
|
100.00% |
1 / 1 |
1 | |||
| getItemCountryPurchase | |
100.00% |
17 / 17 |
|
100.00% |
1 / 1 |
1 | |||
| getItemMonthlyPurchaseCosts | |
100.00% |
15 / 15 |
|
100.00% |
1 / 1 |
1 | |||
| getSupplierMonthlyPurchaseCosts | |
100.00% |
13 / 13 |
|
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\SupplierManagement\Models\SupplierMapper; |
| 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 PurchaseBillMapper 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 getPurchaseBeforePivot( |
| 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('transferType', BillTransferType::PURCHASE) |
| 55 | ->limit($limit) |
| 56 | ->execute(); |
| 57 | } |
| 58 | |
| 59 | /** |
| 60 | * Placeholder |
| 61 | */ |
| 62 | public static function getPurchaseAfterPivot( |
| 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('transferType', BillTransferType::PURCHASE) |
| 74 | ->limit($limit) |
| 75 | ->execute(); |
| 76 | } |
| 77 | |
| 78 | /** |
| 79 | * Placeholder |
| 80 | */ |
| 81 | public static function getPurchaseByItemId(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_netpurchaseprice)') |
| 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 getPurchaseBySupplierId(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_netcosts)') |
| 108 | ->from(self::TABLE) |
| 109 | ->where(self::TABLE . '.billing_bill_supplier', '=', $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 getAvgPurchasePriceByItemId(int $id, \DateTime $start, \DateTime $end) : FloatInt |
| 122 | { |
| 123 | $query = new Builder(self::$db); |
| 124 | |
| 125 | /** @var false|array $result */ |
| 126 | $result = $query->select('SUM(billing_bill_element_single_netpurchaseprice)', 'COUNT(billing_bill_element_total_netpurchaseprice)') |
| 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() ?? false; |
| 135 | |
| 136 | return new FloatInt($result === false || $result[1] == 0 ? 0 : (int) (((int) $result[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 getLastOrderDateBySupplierId(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_supplier', '=', $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 getNewestSupplierInvoices(int $id, int $limit = 10) : array |
| 222 | { |
| 223 | $query = self::getQuery(); |
| 224 | $query->where(self::TABLE . '_d1.billing_bill_supplier', '=', $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 getItemTopSuppliers(int $id, \DateTime $start, \DateTime $end, int $limit = 10) : array |
| 241 | { |
| 242 | $query = SupplierMapper::getQuery(); |
| 243 | $query->selectAs('SUM(billing_bill_element_total_netpurchaseprice)', 'net_purchase') |
| 244 | ->leftJoin(self::TABLE, self::TABLE . '_d1') |
| 245 | ->on(SupplierMapper::TABLE . '_d1.suppliermgmt_supplier_id', '=', self::TABLE . '_d1.billing_bill_supplier') |
| 246 | ->leftJoin(BillElementMapper::TABLE, BillElementMapper::TABLE . '_d1') |
| 247 | ->on(self::TABLE . '_d1.billing_bill_id', '=', BillElementMapper::TABLE . '_d1.billing_bill_element_bill') |
| 248 | ->where(BillElementMapper::TABLE . '_d1.billing_bill_element_item', '=', $id) |
| 249 | ->andWhere(self::TABLE . '_d1.billing_bill_performance_date', '>=', $start) |
| 250 | ->andWhere(self::TABLE . '_d1.billing_bill_performance_date', '<=', $end) |
| 251 | ->orderBy('net_purchase', 'DESC') |
| 252 | ->limit($limit) |
| 253 | ->groupBy(SupplierMapper::TABLE . '_d1.suppliermgmt_supplier_id'); |
| 254 | |
| 255 | $suppliers = SupplierMapper::getAll()->execute($query); |
| 256 | $data = SupplierMapper::getRaw()->execute(); |
| 257 | |
| 258 | return [$suppliers, $data]; |
| 259 | } |
| 260 | |
| 261 | /** |
| 262 | * Placeholder |
| 263 | */ |
| 264 | public static function getItemCountryPurchase(int $id, \DateTime $start, \DateTime $end, int $limit = 10) : array |
| 265 | { |
| 266 | $query = new Builder(self::$db); |
| 267 | $result = $query->select(CountryMapper::TABLE . '.country_code2') |
| 268 | ->selectAs('SUM(billing_bill_element_total_netpurchaseprice)', 'net_purchase') |
| 269 | ->from(self::TABLE) |
| 270 | ->leftJoin(BillElementMapper::TABLE) |
| 271 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 272 | ->leftJoin(CountryMapper::TABLE) |
| 273 | ->on(self::TABLE . '.billing_bill_billCountry', '=', CountryMapper::TABLE . '.country_code2') |
| 274 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 275 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 276 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 277 | ->groupBy(CountryMapper::TABLE . '.country_code2') |
| 278 | ->orderBy('net_purchase', 'DESC') |
| 279 | ->limit($limit) |
| 280 | ->execute() |
| 281 | ?->fetchAll(\PDO::FETCH_KEY_PAIR); |
| 282 | |
| 283 | return $result ?? []; |
| 284 | } |
| 285 | |
| 286 | /** |
| 287 | * Placeholder |
| 288 | */ |
| 289 | public static function getItemMonthlyPurchaseCosts(int $id, \DateTime $start, \DateTime $end) : array |
| 290 | { |
| 291 | $query = new Builder(self::$db); |
| 292 | $result = $query->selectAs('SUM(billing_bill_element_total_netpurchaseprice)', 'net_purchase') |
| 293 | ->selectAs('YEAR(billing_bill_performance_date)', 'year') |
| 294 | ->selectAs('MONTH(billing_bill_performance_date)', 'month') |
| 295 | ->from(self::TABLE) |
| 296 | ->leftJoin(BillElementMapper::TABLE) |
| 297 | ->on(self::TABLE . '.billing_bill_id', '=', BillElementMapper::TABLE . '.billing_bill_element_bill') |
| 298 | ->where(BillElementMapper::TABLE . '.billing_bill_element_item', '=', $id) |
| 299 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 300 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 301 | ->groupBy('year', 'month') |
| 302 | ->orderBy(['year', 'month'], ['ASC', 'ASC']) |
| 303 | ->execute() |
| 304 | ?->fetchAll(); |
| 305 | |
| 306 | return $result ?? []; |
| 307 | } |
| 308 | |
| 309 | /** |
| 310 | * Placeholder |
| 311 | */ |
| 312 | public static function getSupplierMonthlyPurchaseCosts(int $id, \DateTime $start, \DateTime $end) : array |
| 313 | { |
| 314 | $query = new Builder(self::$db); |
| 315 | $result = $query->selectAs('SUM(billing_bill_netcosts)', 'net_purchase') |
| 316 | ->selectAs('YEAR(billing_bill_performance_date)', 'year') |
| 317 | ->selectAs('MONTH(billing_bill_performance_date)', 'month') |
| 318 | ->from(self::TABLE) |
| 319 | ->where(self::TABLE . '.billing_bill_supplier', '=', $id) |
| 320 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '>=', $start) |
| 321 | ->andWhere(self::TABLE . '.billing_bill_performance_date', '<=', $end) |
| 322 | ->groupBy('year', 'month') |
| 323 | ->orderBy(['year', 'month'], ['ASC', 'ASC']) |
| 324 | ->execute() |
| 325 | ?->fetchAll(); |
| 326 | |
| 327 | return $result ?? []; |
| 328 | } |
| 329 | } |