From 5a49bba5a1446369d0c0d8fb63b4f38ae80af65c Mon Sep 17 00:00:00 2001 From: marina Date: Tue, 13 May 2025 16:02:57 +0300 Subject: [PATCH] =?utf8?q?ERP-413=20=D0=9E=D0=B1=D1=80=D0=B0=D0=B1=D0=BE?= =?utf8?q?=D1=82=D0=BA=D0=B0=20=D0=9E=D0=A1=20=D0=9F=D0=BB=D0=B0=D0=BD?= =?utf8?q?=D0=BE=D0=B3=D1=80=D0=B0=D0=BC=D0=BC=D0=B0=20(=D0=BC=D0=B5=D1=81?= =?utf8?q?=D1=8F=D1=86)?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../AutoPlannogrammaController.php | 46 +--- erp24/services/AutoPlannogrammaService.php | 206 +++++++++--------- 2 files changed, 109 insertions(+), 143 deletions(-) diff --git a/erp24/controllers/AutoPlannogrammaController.php b/erp24/controllers/AutoPlannogrammaController.php index bc6a3c3d..577ec8fe 100644 --- a/erp24/controllers/AutoPlannogrammaController.php +++ b/erp24/controllers/AutoPlannogrammaController.php @@ -383,28 +383,14 @@ class AutoPlannogrammaController extends BaseController 'pagination' => ['pageSize' => 100], ]); - - // Обработка даты на год и месяц if (!empty($filters['year']) && !empty($filters['month'])) { $filters['plan_date'] = $filters['year'] . '-' . str_pad($filters['month'], 2, '0', STR_PAD_LEFT) . '-01'; - $planDateParts = explode('-', $filters['plan_date']); - if (count($planDateParts) === 2) { - [$month, $year] = $planDateParts; - $filters['plan_date'] = "$year-$month-01"; - } $service = new AutoPlannogrammaService(); - $monthCategoryShare = $service->getMonthCategoryShareOrWriteOff( - date('Y-m-d', strtotime($filters['plan_date'] . ' -12 months')), - $filters - ); + $monthCategoryShare = $service->getMonthCategoryShareOrWriteOff($filters['plan_date'], $filters, $filters['type']); $monthCategoryGoal = $service->getMonthCategoryGoal($monthCategoryShare, $filters['plan_date'], $filters); - - $monthSubcategoryShare = $service->getMonthSubcategoryShareOrWriteOff( - date('Y-m-d', strtotime($filters['plan_date'] . ' -3 months')), - $filters - ); + $monthSubcategoryShare = $service->getMonthSubcategoryShareOrWriteOff($filters['plan_date'], $filters, $filters['type']); $data = $service->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal); $flatData = array_filter($data, function ($row) use ($filters) { @@ -449,17 +435,12 @@ class AutoPlannogrammaController extends BaseController 'pagination' => ['pageSize' => 100], ]); - // Обработка даты на год и месяц if (!empty($filters['year']) && !empty($filters['month'])) { $filters['plan_date'] = $filters['year'] . '-' . str_pad($filters['month'], 2, '0', STR_PAD_LEFT) . '-01'; - $service = new AutoPlannogrammaService(); - $data = $service->getMonthSpeciesShareOrWriteOff( - date('Y-m-d', strtotime($filters['plan_date'] . ' -3 months')), - $filters, - $filters['type'] - ); + $service = new AutoPlannogrammaService(); + $data = $service->getMonthSpeciesShareOrWriteOff($filters['plan_date'], $filters, $filters['type']); $flatData = array_filter($data, function ($row) use ($filters) { foreach ($filters as $key => $value) { @@ -509,23 +490,12 @@ class AutoPlannogrammaController extends BaseController if (!empty($filters['year']) && !empty($filters['month'])) { $filters['plan_date'] = $filters['year'] . '-' . str_pad($filters['month'], 2, '0', STR_PAD_LEFT) . '-01'; $service = new AutoPlannogrammaService(); - - $monthCategoryShare = $service->getMonthCategoryShareOrWriteOff( - date('Y-m-d', strtotime($filters['plan_date'] . ' -12 months')), - $filters - ); - $monthCategoryGoal = $service->getMonthCategoryGoal($monthCategoryShare, $filters['plan_date'], $filters); - $monthSubcategoryShare = $service->getMonthSubcategoryShareOrWriteOff( - date('Y-m-d', strtotime($filters['plan_date'] . ' -3 months')), - $filters - ); + $monthCategoryShare = $service->getMonthCategoryShareOrWriteOff($filters['plan_date'], $filters, $filters['type']); + $monthCategoryGoal = $service->getMonthCategoryGoal($monthCategoryShare, $filters['plan_date'], $filters); + $monthSubcategoryShare = $service->getMonthSubcategoryShareOrWriteOff($filters['plan_date'], $filters, $filters['type']); $monthSubcategoryGoal = $service->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal); - - $monthSpeciesShare = $service->getMonthSpeciesShareOrWriteOff( - date('Y-m-d', strtotime($filters['plan_date'] . ' -3 months')), - $filters - ); + $monthSpeciesShare = $service->getMonthSpeciesShareOrWriteOff($filters['plan_date'], $filters, $filters['type']); $data = $service->getMonthSpeciesGoalDirty($monthSpeciesShare, $monthSubcategoryGoal); $flatData = array_filter($data, function ($row) use ($filters) { diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php index b4a07680..5a331451 100644 --- a/erp24/services/AutoPlannogrammaService.php +++ b/erp24/services/AutoPlannogrammaService.php @@ -27,91 +27,28 @@ class AutoPlannogrammaService return CityStore::findAll(['visible' => CityStore::IS_VISIBLE]); } - /** - * Создание базового запроса для подсчета итогов - * @param string $type Тип операции (продажи или списания) - * @return Query Объект запроса + /** Корректировка процента списаний, если он превышает 10% от процента продаж. + * @param float $currentPercent Текущий процент списаний. + * @param float $salesPercent Процент продаж для того же магазина, категории и подкатегории. + * @return float Скорректированный процент списаний. */ - private function buildBaseTotalsQuery(string $type): Query - { - // Определяем SQL-выражение для SUM - $sumExpression = $type === self::TYPE_WRITE_OFFS - ? 'SUM(wp.summ)' - : 'SUM(CASE WHEN s.operation = :operation THEN sp.summ ELSE 0 END)'; - - // Определяем таблицу и псевдоним - $fromTable = $type === self::TYPE_WRITE_OFFS - ? ['w' => 'write_offs'] - : ['s' => 'sales']; - - return (new Query()) - ->select([ - 'store_id' => 'ex.entity_id', - 'total_sum' => new Expression($sumExpression, ['operation' => self::OPERATION_SALE]) - ]) - ->from($fromTable); - } - - /** - * Применение соединений таблиц в зависимости от типа операции - * @param Query $query Объект запроса - * @param string $type Тип операции - * @return Query Обновленный запрос - */ - private function applyJoins(Query $query, string $type): Query - { - if ($type === self::TYPE_WRITE_OFFS) { - $query->leftJoin('export_import_table ex', 'ex.export_val = w.store_id') - ->leftJoin('write_offs_products wp', 'wp.write_offs_id = w.id'); - } else { - $query->leftJoin('sales_products sp', 'sp.check_id = s.id') - ->leftJoin('export_import_table ex', 'ex.export_val = s.store_id_1c'); + private function adjustWriteOffPercent( + float $currentPercent, + float $salesPercent + ): float { + if ($currentPercent > ($salesPercent * 0.1)) { + return $currentPercent * 0.1; } - return $query; + return $currentPercent; } - /** - * Получение итогов по магазинам - * @param array $storeIds Список ID магазинов - * @param string $dateFrom Дата начала периода - * @param array|null $productFilter Фильтр по продуктам - * @param string $type Тип операции - * @param string|null $dateTo Дата окончания периода - * @return array Ассоциативный массив с итогами - * @throws \InvalidArgumentException - */ - public function getStoreTotals(array $storeIds, string $dateFrom, ?array $productFilter = null, string $type = self::TYPE_SALES, ?string $dateTo = null): array - { - if (empty($storeIds)) { - return []; - } - - $query = $this->buildBaseTotalsQuery($type); - $query = $this->applyJoins($query, $type); - - $query->where(['>=', 'date', $dateFrom]) - ->andWhere(['ex.entity_id' => $storeIds]) - ->groupBy('ex.entity_id'); - - if ($productFilter !== null) { - $query->andWhere([$type === self::TYPE_WRITE_OFFS ? 'wp.product_id' : 'sp.product_id' => $productFilter]); - } - - if ($dateTo !== null) { - $query->andWhere(['<=', 'date', $dateTo]); - } - - return ArrayHelper::map($query->all(), 'store_id', 'total_sum'); - } /** * Получение доли категорий или списаний за месяц * @param string $dateFrom Дата начала периода * @param array|null $filters Дополнительные фильтры - * @param array|null $productFilter Фильтр по продуктам * @param string $type Тип операции * @return array Массив с долями категорий - * @throws \InvalidArgumentException */ public function getMonthCategoryShareOrWriteOff(string $dateFrom, ?array $filters = null, string $type = self::TYPE_SALES): array { @@ -124,14 +61,32 @@ class AutoPlannogrammaService } // Формирование компонентов запроса - $sumExpression = $type === self::TYPE_WRITE_OFFS ? 'SUM(wp.summ)' : 'SUM(sp.summ)'; $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); - // Создание запроса с CTE для итоговых сумм + // Подготовка весов по месяцам + $date = new \DateTime($dateFrom); + $month3 = $date->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, + ]); + + // Основной запрос с CTE $query = (new Query()) ->select([ 'store_id' => 'main.ex_entity_id', @@ -145,38 +100,36 @@ class AutoPlannogrammaService ->select([ 'ex_entity_id' => 'ex.entity_id', 'category' => 'p1c.category', - 'total_sum' => new Expression($sumExpression), + 'total_sum' => $weightedSumExpression, ]) ->from($fromTable) ->leftJoin($productTableJoin, $productTableJoinCondition) ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") ->leftJoin('export_import_table ex', $storeJoinCondition) - ->andWhere(['<=', key($fromTable) . '.date',(new \DateTime($dateFrom))->modify('-' . (self::LOOKBACK_MONTHS) . ' months')->format('Y-m-d')]) - ->andWhere(['>=', key($fromTable) . '.date',(new \DateTime($dateFrom))->modify('-' . (self::LOOKBACK_MONTHS + self::CATEGORY_LOOKBACK_MONTHS) . ' months')->format('Y-m-d')]) ->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()) + ->innerJoin([ + 'totals' => (new Query()) ->select([ 'store_id' => 'ex.entity_id', - 'total' => new Expression($sumExpression), + 'total' => $weightedSumExpression, ]) ->from($fromTable) ->leftJoin($productTableJoin, $productTableJoinCondition) ->leftJoin('products_1c_nomenclature p1c', "p1c.id = $productJoinCondition") ->leftJoin('export_import_table ex', $storeJoinCondition) - ->where(['<=', key($fromTable) . '.date', $dateFrom]) - ->andWhere(['<=', key($fromTable) . '.date',(new \DateTime($dateFrom))->modify('-' . (self::LOOKBACK_MONTHS) . ' months')->format('Y-m-d')]) - ->andWhere(['>=', key($fromTable) . '.date',(new \DateTime($dateFrom))->modify('-' . (self::LOOKBACK_MONTHS + self::CATEGORY_LOOKBACK_MONTHS) . ' months')->format('Y-m-d')]) ->andWhere(['ex.entity_id' => $storeIds]) ->andWhere(['<>', 'p1c.category', '']) - ->groupBy(['ex.entity_id'])], - 'main.ex_entity_id = totals.store_id' - ); + ->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'); - // Выполнение запроса и обработка результатов + // Выполнение запроса и форматирование $rows = $query->all(); $result = []; @@ -237,7 +190,6 @@ class AutoPlannogrammaService * @param array|null $filters Дополнительные фильтры * @param string $type Тип операции * @return array Массив с долями подкатегорий - * @throws \InvalidArgumentException */ public function getMonthSubcategoryShareOrWriteOff(string $dateFrom, ?array $filters = null, string $type = self::TYPE_SALES): array { @@ -253,10 +205,19 @@ class AutoPlannogrammaService $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'; - $date = new \DateTime($dateFrom); $months = [ - ['between', 'date', $date->modify('-1 year')->format('Y-m-01'), $date->format('Y-m-t')], - ['between', 'date', $date->modify('-1 year')->format('Y-m-01'), $date->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'), + ], + [ + '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()) @@ -304,14 +265,30 @@ class AutoPlannogrammaService $rows = $query->all(); $result = []; + $salesPercents = []; + + // Сначала вычисляем проценты для продаж (они понадобятся для сравнения) + foreach ($rows as $row) { + $key = "{$row['store_id']}_{$row['category']}_{$row['subcategory']}"; + if ($row['type'] === self::TYPE_SALES) { + $salesPercents[$key] = $row['percent']; + } + } + + foreach ($rows as $row) { + $key = "{$row['store_id']}_{$row['category']}_{$row['subcategory']}"; + $percent = $row['percent']; + + if ($row['type'] === self::TYPE_WRITE_OFFS) { + $percent = $this->adjustWriteOffPercent($percent, $salesPercents[$key] ?? 0); + } - foreach ($rows as $row) { $result[] = [ 'store_id' => $row['store_id'], 'category' => $row['category'], 'subcategory' => $row['subcategory'], 'total_sum' => $row['total_sum'], - 'percent' => $row['percent'], + 'percent' => $percent, 'type' => $row['type'], ]; } @@ -327,10 +304,6 @@ class AutoPlannogrammaService */ public function getMonthSubcategoryGoal(array $subcategoryShare, array $categoryGoals): array { - echo '
';
-        var_dump($subcategoryShare);
-        echo '
'; - die(); $indexedGoals = []; foreach ($categoryGoals as $goal) { $indexedGoals[$goal['store_id']][$goal['category']] = $goal['goal']; @@ -348,7 +321,6 @@ class AutoPlannogrammaService ]; } } - return $result; } @@ -374,10 +346,19 @@ class AutoPlannogrammaService $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'; - $date = new \DateTime($dateFrom); $months = [ - ['between', 'date', $date->modify('-1 year')->format('Y-m-01'), $date->format('Y-m-t')], - ['between', 'date', $date->modify('-1 year')->format('Y-m-01'), $date->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'), + ], + [ + '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()) @@ -405,6 +386,7 @@ class AutoPlannogrammaService ->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( @@ -426,15 +408,30 @@ class AutoPlannogrammaService $rows = $query->all(); $result = []; + $salesPercents = []; + + foreach ($rows as $row) { + $key = "{$row['store_id']}_{$row['category']}_{$row['subcategory']}_{$row['species']}"; + if ($row['type'] === self::TYPE_SALES) { + $salesPercents[$key] = $row['percent']; + } + } foreach ($rows as $row) { + $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' => $row['store_id'], 'category' => $row['category'], 'subcategory' => $row['subcategory'], 'species' => $row['species'], 'total_sum' => $row['total_sum'], - 'percent' => $row['percent'], + 'percent' => $percent, 'type' => $row['type'], ]; } @@ -481,7 +478,6 @@ class AutoPlannogrammaService { $datePlan = $filters['plan_date']; $dateFromForCategory = (new \DateTime($datePlan))->modify('-' . (self::CATEGORY_LOOKBACK_MONTHS + self::LOOKBACK_MONTHS) . ' months')->format(self::DATE_FORMAT); - $dateFrom = (new \DateTime($datePlan))->modify('-' . (self::SUBCATEGORY_LOOKBACK_MONTHS + self::LOOKBACK_MONTHS). ' months')->format(self::DATE_FORMAT); $monthCategoryShare = $this->getMonthCategoryShareOrWriteOff($dateFromForCategory, $filters); $monthCategoryGoal = $this->getMonthCategoryGoal($monthCategoryShare, $datePlan, $filters); -- 2.39.5