From fa7cc2c4fc27bbecf0d00a99362dffcb69b03dfe Mon Sep 17 00:00:00 2001 From: fomichev Date: Thu, 22 May 2025 17:07:32 +0300 Subject: [PATCH] =?utf8?q?=D0=BF=D0=B5=D1=80=D0=B5=D0=BD=D0=BE=D1=81=20?= =?utf8?q?=D0=BC=D0=B5=D1=82=D0=BE=D0=B4=D0=BE=D0=B2?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../AutoPlannogrammaController.php | 160 ++++- erp24/services/AutoPlannogrammaService.php | 596 ++++++++++++------ ...-species.phpld => control-species-old.php} | 0 .../auto-plannogramma/control-species.php | 141 +++++ 4 files changed, 694 insertions(+), 203 deletions(-) rename erp24/views/auto-plannogramma/{control-species.phpld => control-species-old.php} (100%) create mode 100644 erp24/views/auto-plannogramma/control-species.php diff --git a/erp24/controllers/AutoPlannogrammaController.php b/erp24/controllers/AutoPlannogrammaController.php index 3b8c3bf6..c623cd75 100644 --- a/erp24/controllers/AutoPlannogrammaController.php +++ b/erp24/controllers/AutoPlannogrammaController.php @@ -93,7 +93,7 @@ class AutoPlannogrammaController extends BaseController if (!empty($filters['plan_date'])) { $service = new AutoPlannogrammaService(); $dataProvider = new ArrayDataProvider([ - 'allModels' => $service->calculateFullGoalChainWeighted($filters), + 'allModels' => $service->calculateFullGoalChain($filters), 'pagination' => ['pageSize' => 100], ]); } @@ -426,6 +426,164 @@ class AutoPlannogrammaController extends BaseController } + public function actionControlSpecies() + { + $model = new DynamicModel([ + 'storeId', 'month', 'type', + + ]); + $model->addRule(['month', 'type'], 'required') + ->addRule('storeId', 'integer'); + + $storeList = CityStore::find() + ->select(['name','id']) + ->where(['visible' => CityStore::IS_VISIBLE]) + ->indexBy('id') + ->column(); + + $monthsList = []; + for ($i = 0; $i < 12; $i++) { + // получаем метку вида "03-2025" + $ts = strtotime("first day of -{$i} month"); + $key = date('m-Y', $ts); + $monthsList[$key] = $key; + } + + $monthResult = []; + $totals = []; + $weeksData = []; + $weeksShareResult = []; + $weeksGoalResult = []; + $monthCategoryShareResult = []; + $weeksProductForecast = []; + + if ($model->load(Yii::$app->request->post()) && $model->validate()) { + $filters = []; + + list($m, $y) = explode('-', $model->month); + $dateFrom = date("Y-m-d 00:00:00", strtotime(sprintf('%04d-%02d-01', $y, $m))); + $dateTo = date("Y-m-t 23:59:59", strtotime($dateFrom)); + + if ($model->storeId) { + $filters['store_id'] = $model->storeId; + $filters['type'] = $model->type; + $filters['plan_date'] = $dateFrom; + } + + $service = new AutoPlannogrammaService(); + + $monthSpeciesGoals = $service->calculateFullGoalChain($filters); + $monthSpeciesGoalsMap = []; + foreach ($monthSpeciesGoals as $monthSpeciesGoal) { + $monthSpeciesGoalsMap[$monthSpeciesGoal['store_id']] + [$monthSpeciesGoal['category']] + [$monthSpeciesGoal['subcategory']] + [$monthSpeciesGoal['species']] = $monthSpeciesGoal['goal']; + } + + + $weeksShareResult = $service->getHistoricalWeeklySpeciesShare($model->month, $filters, null, 'writeOffs'); + $weeksData = $service->calculateWeeklySpeciesGoals($weeksShareResult['weeksData'], $monthSpeciesGoals) ; + + $datePlan = $filters['plan_date']; + $monthCategoryShare = $service->getMonthCategoryShareOrWriteOff($datePlan, $filters, $filters['type']); + $monthCategoryGoal = $service->getMonthCategoryGoal($monthCategoryShare, $datePlan, $filters['type']); + + $monthCategorySalesShare = $service->getMonthCategoryShareOrWriteOff($datePlan, $filters); + $monthCategorySalesGoal = $service->getMonthCategoryGoal($monthCategorySalesShare, $datePlan); + + foreach ($monthCategoryShare as $sid => $cats) { + foreach($cats as $cat) { + $monthCategoryShareResult[$sid][$cat['category']]['total_sum'] = $cat['total_sum']; + $monthCategoryShareResult[$sid][$cat['category']]['percent'] = $cat['percent']; + } + + } + foreach ($monthCategoryGoal as $cats) { + $monthCategoryShareResult[$cats['store_id']][$cats['category']]['goal'] = $cats['goal']; + + } + $monthSubcategorySalesShare = $service->getMonthSubcategoryShareOrWriteOff($datePlan, $filters); + $monthSubcategorySalesGoal = $service->getMonthSubcategoryGoal($monthSubcategorySalesShare, $monthCategorySalesGoal); + + $monthSubcategoryShare = $service->getMonthSubcategoryShareOrWriteOff($datePlan, $filters, $filters['type']); + $monthSubcategoryGoal = $service->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal, $filters['type'], $monthSubcategorySalesGoal); + + + + // var_dump($monthSubcategoryShare); die(); + foreach ($monthSubcategoryShare as $subcat) { + $monthCategoryShareResult[$subcat['store_id']][$subcat['category']][$subcat['subcategory']]['total_sum'] = $subcat['total_sum']; + $monthCategoryShareResult[$subcat['store_id']][$subcat['category']][$subcat['subcategory']]['percent'] = $subcat['percent']; + } + foreach ($monthSubcategoryGoal as $cats) { + $monthCategoryShareResult[$cats['store_id']][$cats['category']][$cats['subcategory']]['goal'] = $cats['goal']; + + } + $monthSpeciesSalesShare = $service->getMonthSpeciesShareOrWriteOff($datePlan, $filters); + $monthSpeciesSalesGoal = $service->getMonthSpeciesGoalDirty($monthSpeciesSalesShare, $monthSubcategoryGoal); + + $monthSpeciesShare = $service->getMonthSpeciesShareOrWriteOff($datePlan, $filters, $filters['type']); + $monthSpeciesGoal = $service->getMonthSpeciesGoalDirty($monthSpeciesShare, $monthSubcategoryGoal, $filters['type'], $monthSpeciesSalesGoal); + + foreach ($monthSpeciesShare as $species) { + $monthCategoryShareResult[$species['store_id']][$species['category']][$species['subcategory']][$species['species']]['total_sum'] = $species['total_sum']; + $monthCategoryShareResult[$species['store_id']][$species['category']][$species['subcategory']][$species['species']]['percent'] = $species['percent']; + + } + foreach ($monthSpeciesGoal as $cats) { + $monthCategoryShareResult[$cats['store_id']][$cats['category']][$cats['subcategory']][$cats['species']]['goal'] = $cats['goal']; + + } + + foreach ($weeksShareResult['weeksData'] as $row) { + $monthCategoryShareResult[$row['store_id']][$row['category']][$row['subcategory']][$row['species']][$row['week']]['sumWeek'] = $row['sumWeek']; + + } + //var_dump($monthCategoryShareResult); die(); + + foreach ($weeksData as $r) { + $forecasts = $service->calculateWeekForecastSpeciesProducts($r['category'], $r['subcategory'], $r['species'], $r['store_id'], $r['weekly_goal']); + foreach ($forecasts as $forecast) { + $weeksProductForecast[] = [ + 'category' => $forecast['category'] ?? '', + 'subcategory' => $forecast['subcategory'] ?? '', + 'species' => $forecast['species'] ?? '', + 'product_id' => $forecast['product_id'] ?? '', + 'name' => $forecast['name'] ?? '', + 'price' => $forecast['price'] ?? '', + 'goal' => $forecast['goal'] ?? 0, + 'forecast' => $forecast['forecast'] ?? 0, + 'week' => $r['week'], + ]; + } + } + + usort($weeksProductForecast, function($a, $b) { + foreach (['category','subcategory','species','name','week'] as $key) { + $va = $a[$key]; + $vb = $b[$key]; + if ($va < $vb) return -1; + if ($va > $vb) return 1; + } + return 0; + }); + + } +//var_dump($weeksProductForecast); die(); + return $this->render('control-species', [ + 'model' => $model, + 'result' => $monthResult, + 'weeksData' => $weeksData, + 'monthCategoryShare' => $monthCategoryShareResult, + 'weeksProductForecast' => $weeksProductForecast, + 'totals' => $totals, + 'storeList' => $storeList, + 'monthsList' => $monthsList, + + ]); + } + } diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php index 73f129aa..1a941338 100644 --- a/erp24/services/AutoPlannogrammaService.php +++ b/erp24/services/AutoPlannogrammaService.php @@ -12,132 +12,169 @@ use yii_app\records\SalesWriteOffsPlan; class AutoPlannogrammaService { + public const TYPE_SALES = 'sales'; // Тип операции: продажи + public const TYPE_WRITE_OFFS = 'writeOffs'; // Тип операции: списания + private const CATEGORY_LOOKBACK_MONTHS = 3; // Период для анализа категорий (месяцы) + private const LOOKBACK_MONTHS = 2; // Отступаемый шаг от плановой даты перед расчетами + + /** + * Получение списка видимых магазинов + * @return CityStore[] Массив моделей магазинов + */ private function getVisibleStores(): array { return CityStore::findAll(['visible' => CityStore::IS_VISIBLE]); } - public function getStoreTotals(array $storeIds, string $dateFrom, ?array $productFilter = null, string $type = 'sales', ?string $dateTo = null): array + /** + * Корректировка цели списаний, если она превышает 10% от цели продаж. + * @param float|null $writeOffGoal Текущая цель списаний + * @param float|null $salesGoal Цель продаж для того же магазина, категории и подкатегории + * @return float Скорректированная цель списаний + */ + private function adjustWriteOffPercent(?float $writeOffGoal, ?float $salesGoal): float { - $query = (new Query()) - ->select([ - 'store_id' => 'ex.entity_id', - 'total_sum' => new Expression( - $type === 'writeOffs' - ? 'SUM(CAST(item ->> \'summ\' AS NUMERIC))' - : 'SUM(sp.summ)' - ) - ]) - ->from(['w' => $type === 'writeOffs' ? 'write_offs' : 'sales']); - - if ($type === 'writeOffs') { - $query->leftJoin('export_import_table ex', 'ex.export_val = w.store_id') - ->leftJoin( - 'LATERAL jsonb_array_elements(w.items::jsonb) AS item', - 'TRUE' - ); - } else { - $query->leftJoin('sales_products sp', 'sp.check_id = w.id') - ->leftJoin('export_import_table ex', 'ex.export_val = w.store_id_1c'); + if ($writeOffGoal === null || $salesGoal === null || $salesGoal <= 0) { + return $writeOffGoal ?? 0.0; } - $query->where(['>=', 'w.date', $dateFrom]) - ->andWhere(['ex.entity_id' => $storeIds]); - - if ($productFilter !== null) { - if ($type === 'writeOffs') { - $query->andWhere(['item ->> \'product_id\'' => $productFilter]); - } else { - $query->andWhere(['sp.product_id' => $productFilter]); - } - } - - if ($dateTo !== null) { - $query->andWhere(['<=', 'w.date', $dateTo]); - } - - $query->groupBy('ex.entity_id'); - - $rows = $query->all(); - - return ArrayHelper::map($rows, 'store_id', 'total_sum'); + return ($writeOffGoal / $salesGoal >= 0.1) ? $salesGoal * 0.1 : $writeOffGoal; } - public function getMonthCategoryShareOrWriteOff(string $dateFrom, string $dateTo, ?array $filters = null, ?array $productFilter = null, string $type = 'sales'): array + /** + * Получение доли категорий или списаний за месяц + * @param string $dateFrom Дата начала периода + * @param array|null $filters Дополнительные фильтры + * @param string $type Тип операции + * @return array Массив с долями категорий + */ + public function getMonthCategoryShareOrWriteOff(string $dateFrom, ?array $filters = null, string $type = self::TYPE_SALES): array { - $stores = $this->getVisibleStores(); - $storeIds = array_map(fn($s) => $s->id, $stores); + // Получение ID видимых магазинов + $storeIds = array_map(fn($store) => $store->id, $this->getVisibleStores()); - if (!empty($filters) && array_key_exists('store_id', $filters) && $filters['store_id'] !== '') { + // Применение фильтра по магазину, если указан + if (!empty($filters['store_id'])) { $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); } - $totals = $this->getStoreTotals($storeIds, $dateFrom, null, $type, $dateTo); -var_dump($totals); die(); - if (empty($totals)) return []; - - $query = (new Query()) - ->select([ - 'store_id' => 'ex.entity_id', - 'category' => 'p1c.category', - 'total_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') { - $query->join('LEFT JOIN', 'export_import_table ex', 'ex.export_val = w.store_id') - ->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']); + // Формирование компонентов запроса + $fromTable = $type === self::TYPE_WRITE_OFFS ? ['w' => 'write_offs'] : ['s' => 'sales']; + $productJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'wp.product_id' : 'sp.product_id'; + $storeJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'ex.export_val = w.store_id' : 'ex.export_val = s.store_id_1c'; + $productTableJoin = $type === self::TYPE_WRITE_OFFS ? ['wp' => 'write_offs_products'] : ['sp' => 'sales_products']; + $productTableJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'wp.write_offs_id = w.id' : 'sp.check_id = s.id'; + $alias = key($fromTable); + $productAlias = key($productTableJoin); + + // Подготовка весов по месяцам + $date = new \DateTime($dateFrom); + $month3 = $date->modify('-3 month')->format('Y-m');// ближний (вес 3) + $month2 = $date->modify('-1 month')->format('Y-m'); + $month1 = $date->modify('-1 month')->format('Y-m'); // дальний (вес 1) + + // Выражение с весами + $weightedSumExpression = new Expression("SUM(CASE + WHEN TO_CHAR($alias.date, 'YYYY-MM') = :month3 THEN $productAlias.summ * 3 + WHEN TO_CHAR($alias.date, 'YYYY-MM') = :month2 THEN $productAlias.summ * 2 + WHEN TO_CHAR($alias.date, 'YYYY-MM') = :month1 THEN $productAlias.summ * 1 + ELSE 0 END)", [ + ':month1' => $month1, + ':month2' => $month2, + ':month3' => $month3, + ]); - if ($productFilter !== null) { - $query->andWhere(['item ->> \'product_id\'' => $productFilter]); - } - } else { - $query->leftJoin('sales_products sp', 'sp.check_id = s.id') - ->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']); + if ($type == self::TYPE_SALES) { + $weightedSumExpression = new Expression("SUM(CASE + WHEN TO_CHAR($alias.date, 'YYYY-MM') = :month3 THEN + CASE WHEN $alias.operation = 'Возврат' THEN -$productAlias.summ * 3 ELSE $productAlias.summ * 3 END + WHEN TO_CHAR($alias.date, 'YYYY-MM') = :month2 THEN + CASE WHEN $alias.operation = 'Возврат' THEN -$productAlias.summ * 2 ELSE $productAlias.summ * 2 END + WHEN TO_CHAR($alias.date, 'YYYY-MM') = :month1 THEN + CASE WHEN $alias.operation = 'Возврат' THEN -$productAlias.summ * 1 ELSE $productAlias.summ * 1 END + ELSE 0 END)", [ + ':month1' => $month1, + ':month2' => $month2, + ':month3' => $month3, + ]); - if ($productFilter !== null) { - $query->andWhere(['sp.product_id' => $productFilter]); - } } + // Основной запрос с CTE + $query = (new Query()) + ->select([ + 'store_id' => 'main.ex_entity_id', + 'category' => 'main.category', + 'total_sum' => 'main.total_sum', + 'percent' => new Expression('ROUND(CAST(main.total_sum AS DECIMAL) / NULLIF(totals.total, 0), 4)'), + 'type' => new Expression(':type', ['type' => $type]), + ]) + ->from([ + 'main' => (new Query()) + ->select([ + 'ex_entity_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'total_sum' => $weightedSumExpression, + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>', 'p1c.category', '']) + ->andWhere(['>=', "$alias.date", (new \DateTime($month1 . '-01'))->format('Y-m-d')]) + ->andWhere(['<=', "$alias.date", (new \DateTime($month3 . '-01'))->modify('last day of this month')->format('Y-m-d')]) + ->groupBy(['ex.entity_id', 'p1c.category']), + ]) + ->innerJoin([ + 'totals' => (new Query()) + ->select([ + 'store_id' => 'ex.entity_id', + 'total' => $weightedSumExpression, + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>', 'p1c.category', '']) + ->andWhere(['>=', "$alias.date", (new \DateTime($month1 . '-01'))->format('Y-m-d')]) + ->andWhere(['<=', "$alias.date", (new \DateTime($month3 . '-01'))->modify('last day of this month')->format('Y-m-d')]) + ->groupBy(['ex.entity_id']), + ], 'main.ex_entity_id = totals.store_id') + ->orderBy('category'); + + // Выполнение запроса и форматирование $rows = $query->all(); $result = []; foreach ($rows as $row) { $storeId = $row['store_id']; - $total = $totals[$storeId] ?? 1; $result[$storeId][] = [ 'category' => $row['category'], 'total_sum' => $row['total_sum'], - 'share_of_total' => round($row['total_sum'] / $total, 4), + 'percent' => $row['percent'], + 'type' => $row['type'], ]; } return $result; } - - - public function getMonthCategoryGoal(array $categoryShare, $datePlan, $filters): array + /** + * Получение целей по категориям за месяц + * @param array $categoryShare Доли категорий + * @param string $datePlan Дата плана + * @param array $filters Фильтры + * @return array Массив с целями по категориям + */ + public function getMonthCategoryGoal(array $categoryShare, string $datePlan, string $type = self::TYPE_SALES): array { $timestamp = strtotime($datePlan); $year = date('Y', $timestamp); $month = date('m', $timestamp); + $plans = SalesWriteOffsPlan::find() ->where(['year' => $year, 'month' => $month]) ->asArray() @@ -147,13 +184,15 @@ var_dump($totals); die(); $result = []; foreach ($plans as $storeId => $plan) { - if (!isset($categoryShare[$storeId])) continue; + if (!isset($categoryShare[$storeId])) { + continue; + } foreach ($categoryShare[$storeId] as $item) { $result[] = [ 'category' => $item['category'], 'store_id' => $storeId, - 'goal' => round($item['share_of_total'] * ($filters['type'] == 'writeOffs' ? $plan['write_offs_plan'] : $plan['total_sales_plan']), 2), + 'goal' => round($item['percent'] * ($type === self::TYPE_WRITE_OFFS ? $plan['write_offs_plan'] : $plan['total_sales_plan']), 2), ]; } } @@ -161,77 +200,100 @@ var_dump($totals); die(); return $result; } - public function getMonthSubcategoryShareOrWriteOff(string $dateFrom, ?array $filters = null, ?array $productFilter = null, string $type = 'sales'): array + /** + * Получение доли подкатегорий или списаний за месяц + * @param string $dateFrom Дата начала периода + * @param array|null $filters Дополнительные фильтры + * @param string $type Тип операции + * @return array Массив с долями подкатегорий + */ + public function getMonthSubcategoryShareOrWriteOff(string $dateFrom, ?array $filters = null, string $type = self::TYPE_SALES): array { - $stores = $this->getVisibleStores(); - $storeIds = array_map(fn($s) => $s->id, $stores); - - if (!empty($filters) && array_key_exists('store_id', $filters) && $filters['store_id'] !== '') { + $storeIds = array_map(fn($store) => $store->id, $this->getVisibleStores()); + if (!empty($filters['store_id'])) { $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); } - $totals = $this->getStoreTotals($storeIds, $dateFrom, $productFilter, $type); - if (empty($totals)) return []; + $sumExpression = $type === self::TYPE_WRITE_OFFS ? 'SUM(wp.summ)' : "SUM(CASE WHEN s.operation = 'Возврат' THEN -sp.summ ELSE sp.summ END)"; + $fromTable = $type === self::TYPE_WRITE_OFFS ? ['w' => 'write_offs'] : ['s' => 'sales']; + $productJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'wp.product_id' : 'sp.product_id'; + $storeJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'ex.export_val = w.store_id' : 'ex.export_val = s.store_id_1c'; + $productTableJoin = $type === self::TYPE_WRITE_OFFS ? ['wp' => 'write_offs_products'] : ['sp' => 'sales_products']; + $productTableJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'wp.write_offs_id = w.id' : 'sp.check_id = s.id'; + + $months = [ + [ + 'between', + $type === self::TYPE_WRITE_OFFS ? 'w.date' : 's.date', + (new \DateTime($dateFrom))->modify('-1 year')->format('Y-m-01'), + (new \DateTime($dateFrom))->format('Y-m-t'), + ], + [ + 'between', + $type === self::TYPE_WRITE_OFFS ? 'w.date' : 's.date', + (new \DateTime($dateFrom))->modify('-1 year')->format('Y-m-01'), + (new \DateTime($dateFrom))->format('Y-m-t'), + ], + ]; $query = (new Query()) ->select([ - 'store_id' => 'ex.entity_id', - 'subcategory' => 'p1c.subcategory', - 'category' => 'p1c.category', - 'total_sum' => new Expression( - $type === 'writeOffs' ? 'SUM(CAST(item ->> \'summ\' AS NUMERIC))' : 'SUM(sp.summ)' - ), - ]); - - if ($type === 'writeOffs') { - $query->from(['w' => 'write_offs']) - ->join('LEFT JOIN', 'export_import_table ex', 'ex.export_val = w.store_id') - ->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(['ex.entity_id' => $storeIds]) - ->andWhere(['<>', 'p1c.subcategory', '']) - ->groupBy(['ex.entity_id', 'p1c.subcategory', 'p1c.category']); - - if ($productFilter !== null) { - $query->andWhere(['item ->> \'product_id\'' => $productFilter]); - } - } else { - $query->from(['s' => 'sales']) - ->leftJoin('sales_products sp', 'sp.check_id = s.id') - ->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(['ex.entity_id' => $storeIds]) - ->andWhere(['<>', 'p1c.subcategory', '']) - ->groupBy(['ex.entity_id', 'p1c.subcategory', 'p1c.category']); - - if ($productFilter !== null) { - $query->andWhere(['sp.product_id' => $productFilter]); - } - } - - $rows = $query->all(); - $result = []; - - foreach ($rows as $row) { - $storeId = $row['store_id']; - $total = $totals[$storeId] ?? 1; - $result[] = [ - 'store_id' => $storeId, - 'category' => $row['category'], - 'subcategory' => $row['subcategory'], - 'total_sum' => $row['total_sum'], - 'percent_of_month' => round($row['total_sum'] / $total, 4), - ]; - } + 'store_id' => 'main.ex_entity_id', + 'category' => 'main.category', + 'subcategory' => 'main.subcategory', + 'total_sum' => 'main.total_sum', + 'percent' => new Expression('ROUND(CAST(main.total_sum AS DECIMAL) / NULLIF(totals.total, 0), 4)'), + 'type' => new Expression(':type', ['type' => $type]), + ]) + ->from([ + 'main' => (new Query()) + ->select([ + 'ex_entity_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'subcategory' => 'p1c.subcategory', + 'total_sum' => new Expression($sumExpression), + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>', 'p1c.category', '']) + ->andWhere(['or', ...$months]) + ->groupBy(['ex.entity_id', 'p1c.category', 'p1c.subcategory']), + ]) + ->innerJoin( + ['totals' => (new Query()) + ->select([ + 'store_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'total' => new Expression($sumExpression), + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>', 'p1c.category', '']) + ->andWhere(['or', ...$months]) + ->groupBy(['ex.entity_id', 'p1c.category'])], + 'main.ex_entity_id = totals.store_id AND main.category = totals.category' + ) + ->orderBy('category, subcategory'); + + $result = $query->all(); return $result; } - - - public function getMonthSubcategoryGoal(array $subcategoryShare, array $categoryGoals): array + /** + * Получение целей по подкатегориям за месяц + * @param array $subcategoryShare Доли подкатегорий + * @param array $categoryGoals Цели по категориям + * @param string $type Тип операции + * @return array Массив с целями по подкатегориям + */ + public function getMonthSubcategoryGoal(array $subcategoryShare, array $categoryGoals, string $type = self::TYPE_SALES, array $salesGoals = []): array { $indexedGoals = []; foreach ($categoryGoals as $goal) { @@ -246,88 +308,158 @@ var_dump($totals); die(); 'category' => $sub['category'], 'subcategory' => $sub['subcategory'], 'store_id' => $sub['store_id'], - 'goal' => round($sub['percent_of_month'] * $goal, 2), + 'goal' => round($sub['percent'] * $goal, 2), ]; } } + if ($type == self::TYPE_WRITE_OFFS) { + foreach ($result as &$row) { + foreach ($salesGoals as $salesGoal) { + if ($row['category'] === $salesGoal['category'] + && $row['subcategory'] === $salesGoal['subcategory'] + && $row['store_id'] === $salesGoal['store_id']) { + $row['old_value'] = $row['goal']; + $row['sales_goal'] = $salesGoal['goal']; + $row['goal'] = $this->adjustWriteOffPercent($row['goal'], $salesGoal['goal']); + } + } + } + unset($row); + } + return $result; } - public function getMonthSpeciesShareOrWriteOff(string $dateFrom, ?array $filters = null, ?array $productFilter = null, string $type = 'sales'): array + /** + * Получение доли видов или списаний за месяц + * @param string $dateFrom Дата начала периода + * @param array|null $filters Дополнительные фильтры + * @param string $type Тип операции + * @return array Массив с долями видов + * @throws \InvalidArgumentException + */ + public function getMonthSpeciesShareOrWriteOff(string $dateFrom, ?array $filters = null, string $type = self::TYPE_SALES): array { - $stores = $this->getVisibleStores(); - $storeIds = array_map(fn($s) => $s->id, $stores); - - if (!empty($filters) && array_key_exists('store_id', $filters) && $filters['store_id'] !== '') { + $storeIds = array_map(fn($store) => $store->id, $this->getVisibleStores()); + if (!empty($filters['store_id'])) { $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); } - $totals = $this->getStoreTotals($storeIds, $dateFrom, $productFilter, $type); - if (empty($totals)) return []; + $sumExpression = $type === self::TYPE_WRITE_OFFS ? 'SUM(wp.summ)' : "SUM(CASE WHEN s.operation = 'Возврат' THEN -sp.summ ELSE sp.summ END)"; + $fromTable = $type === self::TYPE_WRITE_OFFS ? ['w' => 'write_offs'] : ['s' => 'sales']; + $productJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'wp.product_id' : 'sp.product_id'; + $storeJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'ex.export_val = w.store_id' : 'ex.export_val = s.store_id_1c'; + $productTableJoin = $type === self::TYPE_WRITE_OFFS ? ['wp' => 'write_offs_products'] : ['sp' => 'sales_products']; + $productTableJoinCondition = $type === self::TYPE_WRITE_OFFS ? 'wp.write_offs_id = w.id' : 'sp.check_id = s.id'; + + $months = [ + [ + 'between', + $type === self::TYPE_WRITE_OFFS ? 'w.date' : 's.date', + (new \DateTime($dateFrom))->modify('-1 year')->format('Y-m-01'), + (new \DateTime($dateFrom))->format('Y-m-t'), + ], + [ + 'between', + $type === self::TYPE_WRITE_OFFS ? 'w.date' : 's.date', + (new \DateTime($dateFrom))->modify('-1 year')->format('Y-m-01'), + (new \DateTime($dateFrom))->format('Y-m-t'), + ], + ]; $query = (new Query()) ->select([ - 'store_id' => 'ex.entity_id', - 'species' => 'p1c.name', - 'category' => 'p1c.category', - 'subcategory' => 'p1c.subcategory', - 'total_sum' => new Expression( - $type === 'writeOffs' ? 'SUM(CAST(item ->> \'summ\' AS NUMERIC))' : 'SUM(sp.summ)' - ), - ]); - - if ($type === 'writeOffs') { - $query->from(['w' => 'write_offs']) - ->join('LEFT JOIN', 'export_import_table ex', 'ex.export_val = w.store_id') - ->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(['ex.entity_id' => $storeIds]) - ->andWhere(['<>', 'p1c.name', '']) - ->groupBy(['ex.entity_id', 'p1c.name', 'p1c.category', 'p1c.subcategory']); + 'store_id' => 'main.ex_entity_id', + 'category' => 'main.category', + 'subcategory' => 'main.subcategory', + 'species' => 'main.species', + 'total_sum' => 'main.total_sum', + 'percent' => new Expression('ROUND(CAST(main.total_sum AS DECIMAL) / NULLIF(totals.total, 0), 4)'), + 'type' => new Expression(':type', ['type' => $type]), + ]) + ->from([ + 'main' => (new Query()) + ->select([ + 'ex_entity_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'subcategory' => 'p1c.subcategory', + 'species' => 'p1c.species', + 'total_sum' => new Expression($sumExpression), + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>', 'p1c.species', '']) + ->andWhere(['or', ...$months]) + ->groupBy(['ex.entity_id', 'p1c.category', 'p1c.subcategory', 'p1c.species']), + ]) + ->innerJoin( + ['totals' => (new Query()) + ->select([ + 'store_id' => 'ex.entity_id', + 'category' => 'p1c.category', + 'subcategory' => 'p1c.subcategory', + 'total' => new Expression($sumExpression), + ]) + ->from($fromTable) + ->leftJoin($productTableJoin, $productTableJoinCondition) + ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") + ->leftJoin('export_import_table ex', $storeJoinCondition) + ->andWhere(['ex.entity_id' => $storeIds]) + ->andWhere(['<>', 'p1c.species', '']) + ->andWhere(['or', ...$months]) + ->groupBy(['ex.entity_id', 'p1c.category', 'p1c.subcategory'])], + 'main.ex_entity_id = totals.store_id + AND main.category = totals.category + AND main.subcategory = totals.subcategory' + ) + ->orderBy('category, subcategory, species'); - if ($productFilter !== null) { - $query->andWhere(['item ->> \'product_id\'' => $productFilter]); - } - } else { - $query->from(['s' => 'sales']) - ->leftJoin('sales_products sp', 'sp.check_id = s.id') - ->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(['ex.entity_id' => $storeIds]) - ->andWhere(['<>', 'p1c.name', '']) - ->groupBy(['ex.entity_id', 'p1c.name', 'p1c.category', 'p1c.subcategory']); + $rows = $query->all(); + $result = []; + $salesPercents = []; - if ($productFilter !== null) { - $query->andWhere(['sp.product_id' => $productFilter]); + foreach ($rows as $row) { + $key = "{$row['store_id']}_{$row['category']}_{$row['subcategory']}_{$row['species']}"; + if ($row['type'] === self::TYPE_SALES) { + $salesPercents[$key] = $row['percent']; } } - $rows = $query->all(); - $result = []; - foreach ($rows as $row) { - $storeId = $row['store_id']; - $total = $totals[$storeId] ?? 1; + $key = "{$row['store_id']}_{$row['category']}_{$row['subcategory']}_{$row['species']}"; + $percent = $row['percent']; + + if ($row['type'] === self::TYPE_WRITE_OFFS) { + $percent = $this->adjustWriteOffPercent($percent, $salesPercents[$key] ?? 0); + } + $result[] = [ - 'store_id' => $storeId, + 'store_id' => $row['store_id'], 'category' => $row['category'], 'subcategory' => $row['subcategory'], 'species' => $row['species'], 'total_sum' => $row['total_sum'], - 'percent_of_month' => round($row['total_sum'] / $total, 4), + 'percent' => $percent, + 'type' => $row['type'], ]; } return $result; } - public function getMonthSpeciesGoalDirty(array $speciesShare, array $subcategoryGoals): array + /** + * Получение целей по видам за месяц + * @param array $speciesShare Доли видов + * @param array $subcategoryGoals Цели по подкатегориям + * @param string $type Тип операции + * @return array Массив с целями по видам + */ + public function getMonthSpeciesGoalDirty(array $speciesShare, array $subcategoryGoals, $type = self::TYPE_SALES, $salesGoals = []): array { - $storeNamesMap = ArrayHelper::map($this->getVisibleStores(), 'id', 'name'); - $indexedGoals = []; foreach ($subcategoryGoals as $goal) { $indexedGoals[$goal['store_id']][$goal['category']][$goal['subcategory']] = $goal['goal']; @@ -342,15 +474,75 @@ var_dump($totals); die(); 'subcategory' => $species['subcategory'], 'species' => $species['species'], 'store_id' => $species['store_id'], - 'store_name' => $storeNamesMap[$species['store_id']] ?? 'Неизвестный магазин', // Добавляем название магазина - 'goal' => round($species['percent_of_month'] * $goal, 2), + 'goal' => round($species['percent'] * $goal, 2), ]; } } + if ($type == self::TYPE_WRITE_OFFS) { + foreach ($result as &$row) { + foreach ($salesGoals as $salesGoal) { + if ($row['category'] === $salesGoal['category'] + && $row['subcategory'] === $salesGoal['subcategory'] + && $row['species'] === $salesGoal['species'] + && $row['store_id'] === $salesGoal['store_id']) { + $row['old_value'] = $row['goal']; + $row['sales_goal'] = $salesGoal['goal']; + $row['goal'] = $this->adjustWriteOffPercent($row['goal'], $salesGoal['goal']); + } + } + } + unset($row); + } + return $result; } + /** + * Расчет полной цепочки целей + * @param array $filters Фильтры + * @return array Отфильтрованный массив целей + */ + public function calculateFullGoalChain(array $filters): array + { + $datePlan = $filters['plan_date']; + $dateFromForCategory = (new \DateTime($datePlan))->modify('-' . (self::CATEGORY_LOOKBACK_MONTHS + self::LOOKBACK_MONTHS) . ' months')->format('Y-m-d'); + + $monthCategoryShare = $this->getMonthCategoryShareOrWriteOff($dateFromForCategory, $filters, $filters['type']); + $monthCategoryGoal = $this->getMonthCategoryGoal($monthCategoryShare, $datePlan, $filters['type']); + + $monthSubcategoryShare = $this->getMonthSubcategoryShareOrWriteOff($datePlan, $filters, $filters['type']); + $monthSubcategoryGoal = $this->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal, $filters['type']); + + $monthSpeciesShare = $this->getMonthSpeciesShareOrWriteOff($datePlan, $filters, $filters['type']); + $monthSpeciesGoal = $this->getMonthSpeciesGoalDirty($monthSpeciesShare, $monthSubcategoryGoal, $filters['type']); + + $filtered = array_filter($monthSpeciesGoal, function ($row) use ($filters) { + foreach ($filters as $key => $value) { + if ($value === null || $value === '') { + continue; + } + + if (!array_key_exists($key, $row)) { + continue; + } + + if (is_numeric($row[$key]) && is_numeric($value)) { + if ((float)$row[$key] !== (float)$value) { + return false; + } + } else { + if (stripos((string)$row[$key], (string)$value) === false) { + return false; + } + } + } + return true; + }); + + return array_values($filtered); + } + diff --git a/erp24/views/auto-plannogramma/control-species.phpld b/erp24/views/auto-plannogramma/control-species-old.php similarity index 100% rename from erp24/views/auto-plannogramma/control-species.phpld rename to erp24/views/auto-plannogramma/control-species-old.php diff --git a/erp24/views/auto-plannogramma/control-species.php b/erp24/views/auto-plannogramma/control-species.php new file mode 100644 index 00000000..f85073d0 --- /dev/null +++ b/erp24/views/auto-plannogramma/control-species.php @@ -0,0 +1,141 @@ + + +
+

Проверка списаний по видам

+ + 'post']); ?> + + field($model, 'storeId') + ->dropDownList($storeList, ['prompt' => 'Выберите магазин']) + ->label('Магазин') ?> + + field($model, 'month') + ->dropDownList($monthsList, ['prompt' => 'Выберите месяц']) + ->label('Месяц') ?> + + field($model, 'type') + ->radioList([ + //'sales' => 'Продажи', + 'writeOffs'=> 'Списания', + ]) + ->label('Тип данных') ?> + + +
+ 'btn btn-primary']) ?> +
+ + + + + +

Результаты расчетов списаний видов по неделям

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
КатегорияКатегория суммаКатегория доляКатегория цельПодкатегорияПодкатегория суммаПодкатегория доляПодкатегория цельВидВид суммаВид доляВид Цель месяцаНеделяСумма неделиДоля недели (%)Цель недели
storeId][$r['category']]['total_sum'] ?? 0 ?> + formatter->asPercent($monthCategoryShare[$model->storeId][$r['category']]['percent'] ?? 0, 2) ?>storeId][$r['category']]['goal'] ?? 0 ?> + storeId][$r['category']][$r['subcategory']]['total_sum'] ?? 0 ?> + formatter->asPercent($monthCategoryShare[$model->storeId][$r['category']][$r['subcategory']]['percent'] ?? 0, 2) ?>storeId][$r['category']][$r['subcategory']]['goal'] ?? 0 ?> + storeId][$r['category']][$r['subcategory']][$r['species']]['total_sum'] ?? 0 ?> + formatter->asPercent($monthCategoryShare[$model->storeId][$r['category']][$r['subcategory']][$r['species']]['percent'] ?? 0, 2) ?> неделя - начало storeId][$r['category']][$r['subcategory']][$r['species']][$r['week']]['sumWeek'] ?? 0 ?> + + — + + formatter->asPercent($r['percent'], 1) ?> + +
+ + +

Прогноз списаний товаров по неделям

+ + + + + + + + + + + + + + + + + + + + + + + + + + + +
КатегорияПодкатегорияВидТовар (ID)НеделяЦельЦенаПрогноз
+ +
() +
+ +
-- 2.39.5