From ca020f2ba4334da394f09f62ef9eaf1371b6aaa1 Mon Sep 17 00:00:00 2001 From: fomichev Date: Thu, 8 May 2025 13:40:02 +0300 Subject: [PATCH] =?utf8?q?=D0=A0=D0=B0=D1=81=D1=87=D0=B5=D1=82=20=D0=B4?= =?utf8?q?=D0=BE=D0=BB=D0=B5=D0=B9=20=D0=BD=D0=B5=D0=B4=D0=B5=D0=BB=D0=B8?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../AutoPlannogrammaController.php | 2 +- erp24/services/AutoPlannogrammaService.php | 215 +++++++++++++++++- 2 files changed, 215 insertions(+), 2 deletions(-) diff --git a/erp24/controllers/AutoPlannogrammaController.php b/erp24/controllers/AutoPlannogrammaController.php index eb10586d..0533a458 100644 --- a/erp24/controllers/AutoPlannogrammaController.php +++ b/erp24/controllers/AutoPlannogrammaController.php @@ -344,7 +344,7 @@ class AutoPlannogrammaController extends BaseController } - $weeksShareResult = $service->getWeeksSpeciesShare($dateFrom, $dateTo, $filters, null, 'writeOffs'); + $weeksShareResult = $service->getWeeksSpeciesShareOrWriteOff($dateFrom, $dateTo, $filters, null, 'writeOffs'); $weeksData = $weeksShareResult['weeksData']; $weeksShareResult = $weeksShareResult['weeksShare']; diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php index a9f93d6b..cab82f6d 100644 --- a/erp24/services/AutoPlannogrammaService.php +++ b/erp24/services/AutoPlannogrammaService.php @@ -839,7 +839,7 @@ var_dump($totals); die(); * weeksShare: array // доля недели от месячного итога * } */ - public function getWeeksSpeciesShare( + public function getWeeksSpeciesShareOrWriteOff( string $dateFrom, string $dateTo, array $filters, @@ -906,4 +906,217 @@ var_dump($totals); die(); ]; } + /** + * Получает суммы продаж или списаний по видам (species) для каждой недели указанного месяца. + * + * @param string $monthYear месяц-год в формате MM-YYYY, например '03-2025' + * @param array|null $filters опциональные фильтры ['store_id'=>...] + * @param array|null $productFilter опциональный фильтр по product_id + * @param string $type 'sales' или 'writeOffs' + * @return array массив строк: [ + * ['week'=>1,'store_id'=>...,'category'=>...,'subcategory'=>...,'species'=>...,'sum'=>...], + * ... + * ] + */ + public function getWeeklySpeciesDataForMonth( + string $monthYear, + ?array $filters = null, + ?array $productFilter = null, + string $type = 'sales' + ): array { + [$monthStr, $yearStr] = explode('-', $monthYear); + $month = (int)$monthStr; + $year = (int)$yearStr; + + $dateFrom = sprintf('%04d-%02d-01 00:00:00', $year, $month); + $dateTo = date('Y-m-d H:i:s', strtotime($dateFrom . ' +1 month -1 second')); + + $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 []; + } + + $result = []; + + foreach (range(1, 5) as $ind) { + $weekStart = date("Y-m-d 00:00:00", strtotime("+" . (($ind - 1) * 7) . ' days', strtotime($dateFrom))); + $weekEnd = date("Y-m-d 23:59:59", strtotime("+" . ($ind * 7 - 1) . ' days', strtotime($dateFrom))); + if ($weekEnd > $dateTo) { + $weekEnd = $dateTo; + } + if ($weekStart > $dateTo) { + continue; + } + + $query = (new Query())->select([ + 'week' => new Expression('$weekIndex'), + 'store_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'subcategory' => 'p1c.subcategory', + 'species' => 'p1c.species', + 'total_sum' => new Expression( + $type === 'writeOffs' + ? 'SUM(CAST(wop.summ AS NUMERIC))' + : 'SUM(sp.summ)' + ), + ]); + + if ($type === 'writeOffs') { + $query->from(['w' => 'write_offs']) + ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = w.store_id') + ->leftJoin(['wop'=> 'write_offs_products'], 'wop.write_offs_id = w.id') + ->leftJoin(['p1c'=> 'products_1c_nomenclature'], 'p1c.id = wop.product_id') + ->andWhere(['>=', 'w.date', $weekStart]) + ->andWhere(['<=', 'w.date', $weekEnd]); + if ($productFilter !== null) { + $query->andWhere(['wop.product_id' => $productFilter]); + } + } else { + $query->from(['s' => 'sales']) + ->leftJoin(['sp' => 'sales_products'], 'sp.check_id = s.id') + ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c') + ->leftJoin(['p1c'=> 'products_1c_nomenclature'], 'p1c.id = sp.product_id') + ->andWhere(['>=', 's.date', $weekStart]) + ->andWhere(['<=', 's.date', $weekEnd]); + if ($productFilter !== null) { + $query->andWhere(['sp.product_id' => $productFilter]); + } + } + + $query->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>', 'p1c.species', '']) + ->groupBy(['week','ex.entity_id','p1c.category','p1c.subcategory','p1c.species']); + + $rows = $query->all(); + foreach ($rows as $row) { + $result[] = [ + 'week' => $ind, + 'store_id' => $row['store_id'], + 'category' => $row['category'], + 'subcategory' => $row['subcategory'], + 'species' => $row['species'], + 'sum' => (float)$row['total_sum'], + ]; + } + } + + return $result; + } + + /** + * Строит исторический недельный отчёт за аналогичные месяцы предыдущих лет (2020–year-1) + * и вычисляет долю каждой недели от исторического месячного итога по видам. + * + * @param string $monthYear месяц-год в формате MM-YYYY, например '03-2025' + * @param array|null $filters опциональные фильтры ['store_id'=>...] + * @param array|null $productFilter опциональный фильтр по product_id + * @param string $type 'sales' или 'writeOffs' + * @return array{ + * 'historicalWeekly' => array, // суммарные суммы за каждый week + * 'weeklyShare' => array // доли недель относительно исторического месяца + * } + */ + public function getHistoricalWeeklySpeciesShare( + string $monthYear, + ?array $filters = null, + ?array $productFilter = null, + string $type = 'sales' + ): array { + [$monthStr, $yearStr] = explode('-', $monthYear); + $month = (int)$monthStr; + $year = (int)$yearStr; + + $historical = []; + for ($yr = 2020; $yr < $year; $yr++) { + $mYear = sprintf('%02d-%d', $month, $yr); + $weekData = $this->getWeeklySpeciesDataForMonth($mYear, $filters, $productFilter, $type); + foreach ($weekData as $row) { + $idx = $row['week']; + $sid = $row['store_id']; + $cat = $row['category']; + $sub = $row['subcategory']; + $spec = $row['species']; + $historical[$idx][$sid][$cat][$sub][$spec] = + ($historical[$idx][$sid][$cat][$sub][$spec] ?? 0) + + $row['sum']; + } + } + + $dateFrom = sprintf('%04d-%02d-01 00:00:00', $year, $month); + $dateTo = date('Y-m-d H:i:s', strtotime($dateFrom . ' +1 month -1 second')); + $monthWeighted = $this->getMonthSpeciesShareOrWriteOffWeighted( + $dateFrom, $dateTo, $filters, $productFilter, $type + ); + $monthMap = []; + foreach ($monthWeighted as $m) { + $monthMap[ + $m['store_id'] + ][ + $m['category'] + ][ + $m['subcategory'] + ][ + $m['species'] + ] = $m['total_sum']; + } + + $flat = $this->flattenHistorical($historical); + + $weeksShare = []; + foreach ($flat as $row) { + $w = $row['week']; + $sid = $row['store_id']; + $cat = $row['category']; + $sub = $row['subcategory']; + $spec = $row['species']; + $sum = $row['sum']; + + $monthSum = $monthMap[$sid][$cat][$sub][$spec] ?? 0; + if ($monthSum > 0) { + $weeksShare[$w][$sid][$cat][$sub][$spec] = round($sum / $monthSum, 4); + } + } + + return [ + 'historicalWeekly' => $historical, + 'weeklyShare' => $weeksShare, + ]; + } + + + /** + * Разворачивает вложенный многомерный массив исторических данных + * в плоский список записей. + * + * @param array $historical + * @return array [ ['week'=>..., 'store_id'=>..., 'category'=>..., 'subcategory'=>..., 'species'=>..., 'sum'=>...], ... ] + */ + private function flattenHistorical(array $historical): array + { + $flat = []; + foreach ($historical as $week => $byStore) { + foreach ($byStore as $storeId => $byCat) { + foreach ($byCat as $category => $bySub) { + foreach ($bySub as $subcategory => $bySpec) { + foreach ($bySpec as $species => $sum) { + $flat[] = [ + 'week' => $week, + 'store_id' => $storeId, + 'category' => $category, + 'subcategory' => $subcategory, + 'species' => $species, + 'sum' => $sum, + ]; + } + } + } + } + } + return $flat; + } + } -- 2.39.5