From 8551d6d43a9a11a3eccf9c9430b362e5614d515e Mon Sep 17 00:00:00 2001 From: fomichev Date: Wed, 7 May 2025 13:18:53 +0300 Subject: [PATCH] =?utf8?q?=D0=9C=D0=B5=D1=82=D0=BE=D0=B4=20=D0=B2=D0=B7?= =?utf8?q?=D0=B2=D0=B5=D1=88=D0=B5=D0=BD=D0=BD=D0=BE=D0=B9=20=D0=B4=D0=BE?= =?utf8?q?=D0=BB=D0=B8=20=D0=BA=D0=B0=D1=82=D0=B5=D0=B3=D0=BE=D1=80=D0=B8?= =?utf8?q?=D0=B8?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- erp24/services/AutoPlannogrammaService.php | 132 ++++++++++++++++++++- 1 file changed, 128 insertions(+), 4 deletions(-) diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php index 04c22ab2..14623385 100644 --- a/erp24/services/AutoPlannogrammaService.php +++ b/erp24/services/AutoPlannogrammaService.php @@ -61,7 +61,7 @@ class AutoPlannogrammaService return ArrayHelper::map($rows, 'store_id', 'total_sum'); } - public function getMonthCategoryShareOrWriteOff(string $dateFrom, ?array $filters = null, ?array $productFilter = null, string $type = 'sales'): array + public function getMonthCategoryShareOrWriteOff(string $dateFrom, string $dateTo, ?array $filters = null, ?array $productFilter = null, string $type = 'sales'): array { $stores = $this->getVisibleStores(); $storeIds = array_map(fn($s) => $s->id, $stores); @@ -70,8 +70,8 @@ class AutoPlannogrammaService $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); } - $totals = $this->getStoreTotals($storeIds, $dateFrom, null, $type); - + $totals = $this->getStoreTotals($storeIds, $dateFrom, null, $type, $dateTo); +var_dump($totals); die(); if (empty($totals)) return []; $query = (new Query()) @@ -89,6 +89,7 @@ class AutoPlannogrammaService ->join('JOIN', new Expression('LATERAL jsonb_array_elements(w.items::jsonb) AS item'), 'true') ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = item ->> \'product_id\'') ->where(['>=', 'w.date', $dateFrom]) + ->andWhere(['<=','w.date',$dateTo]) ->andWhere(['ex.entity_id' => $storeIds]) ->andWhere(['<>', 'p1c.category', '']) ->groupBy(['ex.entity_id', 'p1c.category']); @@ -101,6 +102,7 @@ class AutoPlannogrammaService ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = sp.product_id') ->leftJoin('export_import_table ex', 'ex.export_val = s.store_id_1c') ->where(['>=', 's.date', $dateFrom]) + ->andWhere(['<=','w.date',$dateTo]) ->andWhere(['ex.entity_id' => $storeIds]) ->andWhere(['<>', 'p1c.category', '']) ->groupBy(['ex.entity_id', 'p1c.category']); @@ -126,6 +128,127 @@ class AutoPlannogrammaService return $result; } + /** + * Считает взвешенную долю категорий за целевой месяц: + * берёт три предыдущих к «месяцу-цели» месяца (пропуская сразу предыдущий), + * присваивает им веса 3, 2 и 1 (самый старый месяц — 3, самый свежий — 1), + * суммирует взвешенные итоги по каждой категории и выдаёт их доли + * от общего взвешенного итога. + * + * @param string $month Целевой месяц в формате 'YYYY-MM' + * @param array|null $filters ['store_id'=>…, …] + * @param array|null $productFilter Опционально: [product_id, …] + * @param string $type 'sales' или 'writeOffs' + * @return array [ + * => [ + * ['category'=>string, 'total_sum'=>float, 'share_of_total'=>float], + * … + * ], + * … + * ] + */ + public function getMonthCategoryShareOrWriteOffWeighted( + string $month, + ?array $filters = null, + ?array $productFilter = null, + string $type = 'sales' + ): array + { + $stores = $this->getVisibleStores(); + $storeIds = array_map(fn($s) => $s->id, $stores); + if (!empty($filters['store_id'])) { + $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); + } + if (empty($storeIds)) { + return []; + } + + $baseMonth = strtotime("{$month}-01"); + + $monthOffsets = [2, 3, 4]; + $monthWeights = [3, 2, 1]; + + + $weightedSums = []; + $monthStoreTotalsWeighted = []; + + foreach ($monthOffsets as $idx => $offsetMonths) { + $w = $monthWeights[$idx]; + $start = date('Y-m-01 00:00:00', strtotime("-{$offsetMonths} months", $baseMonth)); + $end = date('Y-m-t 23:59:59', strtotime($start)); + + $monthStoreTotals = $this->getStoreTotals( + $storeIds, $start, $productFilter, $type, $end + ); + + $q = (new Query()) + ->select([ + 'store_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'month_sum' => new Expression( + $type === 'writeOffs' + ? "SUM(CAST(item->>'summ' AS NUMERIC))" + : 'SUM(sp.summ)' + ) + ]) + ->from($type === 'writeOffs' ? ['w'=>'write_offs'] : ['s'=>'sales']); + + if ($type === 'writeOffs') { + $q->leftJoin('export_import_table ex', 'ex.export_val = w.store_id') + ->leftJoin('LATERAL jsonb_array_elements(w.items::jsonb) AS item', 'TRUE') + ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = item->>\'product_id\'') + ->andWhere(['>=','w.date',$start]) + ->andWhere(['<=','w.date',$end]); + if ($productFilter !== null) { + $q->andWhere(["item->>'product_id'" => $productFilter]); + } + } else { + $q->leftJoin('sales_products sp', 'sp.check_id = s.id') + ->leftJoin('export_import_table ex', 'ex.export_val = s.store_id_1c') + ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = sp.product_id') + ->andWhere(['>=','s.date',$start]) + ->andWhere(['<=','s.date',$end]); + if ($productFilter !== null) { + $q->andWhere(['sp.product_id' => $productFilter]); + } + } + $q->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>','p1c.category','']) + ->groupBy(['ex.entity_id','p1c.category']); + + $rows = $q->all(); + + foreach ($rows as $r) { + $sid = $r['store_id']; + $cat = $r['category']; + $sum = (float)$r['month_sum'] * $w; + $weightedSums[$sid][$cat] = ($weightedSums[$sid][$cat] ?? 0) + $sum; + } + foreach ($monthStoreTotals as $sid => $monthStoreTotal) { + $monthStoreTotalsWeighted[$sid] = ($monthStoreTotalsWeighted[$sid] ?? 0) + ($monthStoreTotal * $w); + + } + + } + + $result = []; + foreach ($weightedSums as $storeId => $cats) { + $grand = array_sum($cats) ?: 1; + foreach ($cats as $category => $weightedSum) { + $result[$storeId][] = [ + 'category' => $category, + 'total_sum_cat' => $weightedSum, + 'total_sum_store' => $monthStoreTotalsWeighted[$storeId], + 'query_total_sum_store' => $grand, + 'share_of_total' => round($weightedSum / $grand, 4), + 'share_of_total_control' => round($weightedSum / $monthStoreTotalsWeighted[$storeId], 4), + ]; + } + } + + return $result; + } + public function getMonthCategoryGoal(array $categoryShare, $datePlan, $filters): array { $timestamp = strtotime($datePlan); @@ -349,7 +472,8 @@ class AutoPlannogrammaService $dateFromForCategory = (new \DateTime($datePlan))->modify('-12 months')->format('Y-m-d'); $dateFrom = (new \DateTime($datePlan))->modify('-3 months')->format('Y-m-d'); - $monthCategoryShare = $this->getMonthCategoryShareOrWriteOff($dateFromForCategory, $filters); + //$monthCategoryShare = $this->getMonthCategoryShareOrWriteOff($dateFromForCategory, $filters); + $monthCategoryShare = $this->getMonthCategoryShareOrWriteOffWeighted($datePlan, $filters, null, $filters['type']); $monthCategoryGoal = $this->getMonthCategoryGoal($monthCategoryShare, $datePlan, $filters); var_dump($monthCategoryShare); die(); $monthSubcategoryShare = $this->getMonthSubcategoryShareOrWriteOff($dateFrom, $filters); -- 2.39.5