From 192f01dbaf5251e1f20b289b48b40eae2d0156b6 Mon Sep 17 00:00:00 2001 From: fomichev Date: Mon, 2 Jun 2025 15:28:06 +0300 Subject: [PATCH] =?utf8?q?=D0=94=D0=BE=D0=BB=D1=8F=20=D0=BF=D1=80=D0=BE?= =?utf8?q?=D0=B4=D0=B0=D0=B6=20=D0=BD=D0=B5=D0=B4=D0=B5=D0=BB=D1=8C=D0=B8?= =?utf8?q?=20=D0=B4=D0=BB=D1=8F=20=D0=B2=D0=B8=D0=B4=D0=B0?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../AutoPlannogrammaController.php | 16 +- erp24/services/AutoPlannogrammaService.php | 261 +++++++++++++++++- .../month-products-species-forecast.php | 11 +- .../month-products-species-share.php | 8 +- .../week-sales-species-share.php | 6 +- 5 files changed, 270 insertions(+), 32 deletions(-) diff --git a/erp24/controllers/AutoPlannogrammaController.php b/erp24/controllers/AutoPlannogrammaController.php index a1d024d1..24215970 100644 --- a/erp24/controllers/AutoPlannogrammaController.php +++ b/erp24/controllers/AutoPlannogrammaController.php @@ -943,7 +943,7 @@ class AutoPlannogrammaController extends BaseController // $productForecastSpecies = $service->calculateProductSalesBySpecies($salesProductForecastShare, $cleanedSpeciesGoals); - var_dump($productSalesForecast); die(); + // var_dump($salesProductForecastShare); die(); @@ -1074,7 +1074,9 @@ class AutoPlannogrammaController extends BaseController $productForecastSpecies = $service->calculateProductSalesBySpecies($salesProductForecastShare, $cleanedSpeciesGoals); - //var_dump($salesProductForecastShare); die(); + + $weeklySales = $service->getHistoricalSpeciesShareByWeek($filters['plan_date'], $filters); + var_dump($weeklySales); die(); @@ -1225,16 +1227,16 @@ class AutoPlannogrammaController extends BaseController // var_dump( $filters['plan_date']); die(); $service = new AutoPlannogrammaService(); - // $weeklySales = $service->getWeeklySpeciesDataForMonth($filters['plan_date'], $filters); - $weeksShareResult = $service->getHistoricalWeeklySpeciesShare($filters['plan_date'], $filters); - // $weeksData = $service->calculateWeeklySpeciesGoals($weeksShareResult['weeksData'], $monthSpeciesGoals) ; - //var_dump($weeksShareResult); die(); + $weeklySales = $service->getHistoricalSpeciesShareByWeek($filters['plan_date'], $filters); + + + - $flatData = array_filter($weeksShareResult, function ($row) use ($filters) { + $flatData = array_filter($weeklySales, function ($row) use ($filters) { foreach ($filters as $key => $value) { if (empty($value)) continue; if (!isset($row[$key])) continue; diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php index c27a7a6c..c94aedb3 100644 --- a/erp24/services/AutoPlannogrammaService.php +++ b/erp24/services/AutoPlannogrammaService.php @@ -2,6 +2,7 @@ namespace yii_app\services; +use DateTime; use Yii; use yii\db\Expression; use yii\db\mssql\PDO; @@ -926,32 +927,248 @@ class AutoPlannogrammaService return $result; } - protected function getWeekRangesForMonth(int $year, int $month): array + /** + * Возвращает диапазоны недель (index, start, end) для указанного года и месяца, + * взятые по правилу «неделя считается, если в неё входит ≥4 дня из этого месяца». + * + * @param int $year Год (например, 2025) + * @param int $month Месяц (1–12) + * @return array + */ + public function getWeekRangesForMonth(int $year, int $month): array { $dateFrom = strtotime(sprintf('%04d-%02d-01 00:00:00', $year, $month)); $dateTo = strtotime('+1 month -1 second', $dateFrom); + $dayOfWeek = (int)date('N', $dateFrom); $firstMonday = $dayOfWeek === 1 ? $dateFrom : strtotime('next monday', $dateFrom); $ranges = []; - for ($wkStart = $firstMonday; $wkStart <= $dateTo; $wkStart += 7*86400) { - $wkEnd = min($dateTo, $wkStart + 6*86400); - $daysInMonth = floor(($wkEnd - max($wkStart, $dateFrom)) / 86400) + 1; - if ($daysInMonth < 4) { - continue; + for ($wkStart = $firstMonday; $wkStart <= $dateTo; $wkStart += 7 * 86400) { + $wkEnd = $wkStart + 6 * 86400; + + $periodStart = max($wkStart, $dateFrom); + $periodEnd = min($wkEnd, $dateTo); + $daysInMonth = floor(($periodEnd - $periodStart) / 86400) + 1; + + if ($daysInMonth >= 4) { + $ranges[] = [ + 'index' => (int)date('W', $wkStart), // ISO-неделя от $wkStart + 'start' => date('Y-m-d H:i:s', $wkStart), // “год-месяц-день 00:00:00” + 'end' => date('Y-m-d 23:59:59', $wkEnd), // “год-месяц-день 23:59:59” + ]; } - $ranges[] = [ - 'index' => (int)date('W', $wkStart), - 'start' => date('Y-m-d H:i:s', $wkStart), - 'end' => date('Y-m-d 23:59:59', $wkEnd), - ]; } return $ranges; } + public function getHistoricalSpeciesShareByWeek( + string $monthYear, + ?array $filters = null, + string $type = self::TYPE_SALES + ): array { + [$yearStr, $monthStr, $_] = explode('-', $monthYear); + $year = (int)$yearStr; + $month = (int)$monthStr; + + $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 []; + } + + $sumExpression = $type === self::TYPE_WRITE_OFFS + ? 'SUM(wp.quantity)' + : 'SUM(sp.quantity)'; + + // Таблицы и условия join + $fromTable = $type === self::TYPE_WRITE_OFFS ? ['w' => 'write_offs'] : ['s' => 'sales']; + $alias = key($fromTable); + + $productTableJoin = $type === self::TYPE_WRITE_OFFS ? ['wp' => 'write_offs_products'] : ['sp' => 'sales_products']; + $productAlias = key($productTableJoin); // 'wp' или 'sp' + $productTableJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'wp.write_offs_id = w.id' : 'sp.check_id = s.id'; + $storeJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'ex.export_val = w.store_id' : 'ex.export_val = s.store_id_1c'; + + + $monthQtyBySpecies = []; + $weekQtyByPos = []; + + foreach ([$year - 2, $year - 1] as $histYear) { + + $histMonthStart = sprintf('%04d-%02d-01 00:00:00', $histYear, $month); + $histMonthEnd = date('Y-m-d 23:59:59', strtotime("$histMonthStart +1 month -1 second")); + + $monthQuery = (new Query()) + ->select([ + 'store_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'subcategory' => 'p1c.subcategory', + 'species' => 'p1c.species', + 'month_sum' => new Expression($sumExpression), + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = {$productAlias}.product_id") + ->leftJoin('products_1c p1', "p1.id = {$productAlias}.product_id") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['p1.components' => '']) + ->andWhere(['not in', 'p1c.category', ['', 'букет', 'сборка', 'сервис']]) + ->andWhere(['>=', "{$alias}.date", $histMonthStart]) + ->andWhere(['<=', "{$alias}.date", $histMonthEnd]) + ->groupBy(['ex.entity_id', 'p1c.category', 'p1c.subcategory', 'p1c.species']); + + $monthRows = $monthQuery->all(); + foreach ($monthRows as $row) { + $sid = $row['store_id']; + $cat = $row['category']; + $sub = $row['subcategory']; + $spec = $row['species']; + $qty = (float)$row['month_sum']; + + $monthQtyBySpecies[$sid][$cat][$sub][$spec] = + ($monthQtyBySpecies[$sid][$cat][$sub][$spec] ?? 0.0) + $qty; + } + + $histRanges = $this->getWeekRangesForMonth($histYear, $month); + + $weekPos = 0; + foreach ($histRanges as $range) { + $weekPos++; + + $startDt = new DateTime($range['start']); + $startDt->setDate( + $histYear, + (int)date('n', strtotime($range['start'])), + (int)date('j', strtotime($range['start'])) + ); + $endDt = new DateTime($range['end']); + $endDt->setDate( + $histYear, + (int)date('n', strtotime($range['end'])), + (int)date('j', strtotime($range['end'])) + ); + + $weekQuery = (new Query()) + ->select([ + 'store_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'subcategory' => 'p1c.subcategory', + 'species' => 'p1c.species', + 'week_sum' => new Expression($sumExpression), + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = {$productAlias}.product_id") + ->leftJoin('products_1c p1', "p1.id = {$productAlias}.product_id") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['p1.components' => '']) + ->andWhere(['not in', 'p1c.category', ['', 'букет', 'сборка', 'сервис']]) + ->andWhere(new Expression( + "{$alias}.date BETWEEN :wstart AND :wend", + [ + ':wstart' => $startDt->format('Y-m-d H:i:s'), + ':wend' => $endDt->format('Y-m-d H:i:s'), + ] + )) + ->groupBy(['ex.entity_id', 'p1c.category', 'p1c.subcategory', 'p1c.species']); + + $weekRows = $weekQuery->all(); + foreach ($weekRows as $row) { + $sid = $row['store_id']; + $cat = $row['category']; + $sub = $row['subcategory']; + $spec = $row['species']; + $qty = (float)$row['week_sum']; + + if (!isset($weekQtyByPos[$weekPos])) { + $weekQtyByPos[$weekPos] = []; + } + $weekQtyByPos[$weekPos][$sid][$cat][$sub][$spec] = + ($weekQtyByPos[$weekPos][$sid][$cat][$sub][$spec] ?? 0.0) + $qty; + } + } + } + + $shareByPos = []; + foreach ($weekQtyByPos as $weekPos => $storesMap) { + foreach ($storesMap as $sid => $byCat) { + foreach ($byCat as $cat => $bySub) { + foreach ($bySub as $sub => $bySpec) { + foreach ($bySpec as $spec => $weekQty) { + $monthQty = $monthQtyBySpecies[$sid][$cat][$sub][$spec] ?? 0.0; + if ($monthQty <= 0.0) { + continue; + } + $shareByPos[$weekPos][$sid][$cat][$sub][$spec] = + round($weekQty / $monthQty, 4); + } + } + } + } + } + + + $targetRanges = $this->getWeekRangesForMonth($year, $month); + + $result = []; + foreach ($targetRanges as $posIndex => $range) { + $weekPos = $posIndex + 1; + $isoWeekNumber = $range['index']; + + if (!isset($shareByPos[$weekPos])) { + continue; + } + foreach ($shareByPos[$weekPos] as $sid => $byCat) { + foreach ($byCat as $cat => $bySub) { + foreach ($bySub as $sub => $bySpec) { + foreach ($bySpec as $spec => $share) { + $result[] = [ + 'store_id' => $sid, + 'category' => $cat, + 'subcategory' => $sub, + 'species' => $spec, + 'week' => $isoWeekNumber, + 'share' => $share, + 'sumMonth' => $monthQtyBySpecies[$sid][$cat][$sub][$spec] ?? 0.0, + 'sumWeek' => $weekQtyByPos[$weekPos][$sid][$cat][$sub][$spec] ?? 0.0 + ]; + } + } + } + } + } + + $grouped = []; + foreach ($result as $idx => $row) { + $key = "{$row['store_id']}|{$row['category']}|{$row['subcategory']}|{$row['species']}"; + $grouped[$key][] = $idx; + } + foreach ($grouped as $key => $indices) { + $sumPercent = 0.0; + foreach ($indices as $i) { + $sumPercent += $result[$i]['share']; + } + if ($sumPercent < 1.0) { + $diff = 1.0 - round($sumPercent, 4); + $count = count($indices); + $add = round($diff / $count, 4); + foreach ($indices as $i) { + $result[$i]['share'] = round($result[$i]['share'] + $add, 6); + } + } + } + + return $result; + } /** * Исторический недельный отчёт и доли по видам с учётом store_id. * @@ -1000,7 +1217,7 @@ class AutoPlannogrammaService ($historical[$week][$sid][$cat][$sub][$spec] ?? 0) + $sumWeek; } } -var_dump($yearData); die(); + $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->getMonthSpeciesShareOrWriteOff( @@ -1517,7 +1734,25 @@ var_dump($yearData); die(); if (!$priceRecord || $priceRecord->price <= 0) { continue; } - $goal = $goalsMap[$data['store_id']][$data['category']][$data['subcategory']][$data['species']]; + + $storeId = $data['store_id']; + $cat = $data['category']; + $sub = $data['subcategory']; + $spec = $data['species']; + if ( + ! isset( + $goalsMap[$storeId], + $goalsMap[$storeId][$cat], + $goalsMap[$storeId][$cat][$sub], + $goalsMap[$storeId][$cat][$sub][$spec] + ) + ) { + + continue; + } + + $goal = $goalsMap[$storeId][$cat][$sub][$spec]; + $forecastSum = $goal * $share; $forecastCount = $forecastSum / $priceRecord->price; diff --git a/erp24/views/auto-plannogramma/month-products-species-forecast.php b/erp24/views/auto-plannogramma/month-products-species-forecast.php index ae4969b7..7385b452 100644 --- a/erp24/views/auto-plannogramma/month-products-species-forecast.php +++ b/erp24/views/auto-plannogramma/month-products-species-forecast.php @@ -108,12 +108,10 @@ $columns = [ return \yii_app\records\Products1c::findOne($data['product_id'])->name ?? null; }, ], - ['attribute' => 'forecast_pieces', 'label' => 'Прогноз в шт', 'format' => ['decimal', 2]], - ['attribute' => 'share', 'label' => 'Доля', - - 'format' => ['percent', 2]], - ['attribute' => 'cleanGoal', 'label' => 'Цель вида очищенная', 'format' => ['decimal', 2]], - ['attribute' => 'product_sales', 'label' => 'Прогноз в стоимости внутри вида', 'format' => ['decimal', 2]], + ['attribute' => 'forecast_pieces', 'label' => 'Прогноз в шт', 'pageSummary' => true, 'format' => ['decimal', 2]], + ['attribute' => 'share', 'label' => 'Доля', 'pageSummary' => true, 'format' => ['percent', 2]], + ['attribute' => 'cleanGoal', 'label' => 'Цель вида очищенная', 'pageSummary' => true, 'format' => ['decimal', 2]], + ['attribute' => 'product_sales', 'label' => 'Прогноз в стоимости внутри вида', 'pageSummary' => true, 'format' => ['decimal', 2]], ['attribute' => 'history_status', 'label' => 'Статус товара', ], ]; @@ -121,6 +119,7 @@ $columns = [ ?> $dataProvider, + 'showPageSummary' => true, 'columns' => $columns, ]); ?> name ?? null; }, ], - ['attribute' => 'forecast_pieces', 'label' => 'Прогноз в шт', 'format' => ['decimal', 2]], - ['attribute' => 'share', 'label' => 'Доля', - - 'format' => ['percent', 2]], - + ['attribute' => 'forecast_pieces', 'label' => 'Прогноз в шт', 'pageSummary' => true, 'format' => ['decimal', 2]], + ['attribute' => 'share', 'label' => 'Доля', 'format' => ['percent', 2], 'pageSummary' => true,], ['attribute' => 'history_status', 'label' => 'Статус товара', ], ]; @@ -120,6 +117,7 @@ $columns = [ ?> $dataProvider, + 'showPageSummary' => true, 'columns' => $columns, ]); ?> 'sumMonth', 'label' => 'Сумма за месяц', 'format' => ['decimal', 0], + 'pageSummary' => true, ], [ 'attribute' => 'sumWeek', 'label' => 'Сумма за неделю', 'format' => ['decimal', 0], + 'pageSummary' => true, ], [ - 'attribute' => 'percent', + 'attribute' => 'share', 'label' => 'Доля', 'format' => ['percent', 2], + 'pageSummary' => true, ], ]; echo GridView::widget([ 'dataProvider' => $dataProvider, + 'showPageSummary' => true, 'columns' => $columns, ]); -- 2.39.5