From e2621ab39f004d91e098a436d861f6b5f3580ade Mon Sep 17 00:00:00 2001 From: marina Date: Fri, 11 Apr 2025 09:47:45 +0300 Subject: [PATCH] =?utf8?q?ERP-359=20=D0=A1=D0=BE=D0=B7=D0=B4=D0=B0=D1=82?= =?utf8?q?=D1=8C=20=D0=BB=D0=BE=D0=B3=D0=B8=D0=BA=D1=83=20=D1=80=D0=B0?= =?utf8?q?=D1=81=D1=87=D0=B5=D1=82=D0=B0=20=D0=BD=D0=B0=20=D0=BC=D0=B5?= =?utf8?q?=D1=81=D1=8F=D1=86=20-=20=D1=81=D1=83=D0=BC=D0=BC=D0=B0=20=D0=BF?= =?utf8?q?=D1=80=D0=BE=D0=B4=D0=B0=D0=B6=20=D0=B2=D0=B8=D0=B4=D0=B0=20?= =?utf8?q?=D0=B4=D0=BB=D1=8F=20=D0=B0=D0=B2=D1=82=D0=BE=D0=BF=D0=BC?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../shipment/AutoPlanogrammaAction.php | 150 ----------- .../controllers/AutoPlanogrammaController.php | 111 ++++++++ erp24/controllers/ShipmentController.php | 1 - erp24/services/AutoPlannogrammaService.php | 253 ++++++++++++++++++ .../index.php} | 0 erp24/views/auto-planogramma/test-sales.php | 92 +++++++ 6 files changed, 456 insertions(+), 151 deletions(-) delete mode 100644 erp24/actions/shipment/AutoPlanogrammaAction.php create mode 100644 erp24/controllers/AutoPlanogrammaController.php create mode 100644 erp24/services/AutoPlannogrammaService.php rename erp24/views/{shipment/auto-planogramma.php => auto-planogramma/index.php} (100%) create mode 100644 erp24/views/auto-planogramma/test-sales.php diff --git a/erp24/actions/shipment/AutoPlanogrammaAction.php b/erp24/actions/shipment/AutoPlanogrammaAction.php deleted file mode 100644 index 4b8737da..00000000 --- a/erp24/actions/shipment/AutoPlanogrammaAction.php +++ /dev/null @@ -1,150 +0,0 @@ -select([ - new Expression(" - CASE - WHEN category ILIKE 'срезка' THEN 'Срезка' - WHEN category ILIKE 'горшечные растения' THEN 'Горшечные растения' - WHEN category ILIKE 'сухоцветы' THEN 'Сухоцветы' - ELSE 'Остальные категории' - END AS category - "), - 'subcategory', - new Expression('json_agg(name) AS products') - ]) - ->from('products_1c_nomenclature') - ->groupBy(['category', 'subcategory']) - ->orderBy([ - new Expression(" - CASE - WHEN category ILIKE 'срезка' THEN 1 - WHEN category ILIKE 'горшечные растения' THEN 2 - WHEN category ILIKE 'сухоцветы' THEN 3 - ELSE 4 - END - "), - 'subcategory' => SORT_ASC - ]) - ->all(); - - $stores = ArrayHelper::map(CityStore::findAll(['visible' => CityStore::IS_VISIBLE]), 'id', 'name'); - //1 month_category_share - //-- select * from sales where date >= '2025-03-01' - //-- select * from sales_products limit 6 - - //1 =1.88 - //weight./category - не поняла зачем в формуле -// SELECT -// category, -// SUM(sp.summ) AS total_sum, -// ROUND((SUM(sp.summ) * 100) / -// (SELECT SUM(sp2.summ) -// FROM sales -// LEFT JOIN sales_products sp2 ON sp2.check_id = sales.id -// WHERE date >= '2025-04-01'), 2) AS share_of_total -// FROM sales -// LEFT JOIN sales_products sp ON sp.check_id = sales.id -// LEFT JOIN products_1c_nomenclature p1c ON p1c.id = sp.product_id -// WHERE date >= '2025-04-01' and category = 'Горшечные_растения' -// GROUP BY category; - //откуда берем 4200000? - //2 1.88 * 4200000 = 78960 - // - //3 -// WITH monthly_sales AS ( -// SELECT -// EXTRACT(MONTH FROM s.date) AS month, -// EXTRACT(YEAR FROM s.date) AS year, -// SUM(sp.summ) AS total_sales -// FROM sales_products sp -// LEFT JOIN sales s ON s.id = sp.check_id -// WHERE sp.summ IS NOT NULL -// GROUP BY EXTRACT(YEAR FROM s.date), EXTRACT(MONTH FROM s.date) -//), -//subcategory_sales AS ( -// SELECT -// EXTRACT(MONTH FROM s.date) AS month, -// EXTRACT(YEAR FROM s.date) AS year, -// p1c.subcategory, -// SUM(sp.summ) AS total_sum -// FROM sales_products sp -// LEFT JOIN products_1c_nomenclature p1c ON p1c.id = sp.product_id -// LEFT JOIN sales s ON s.id = sp.check_id -// WHERE p1c.category = 'Горшечные_растения' -// GROUP BY EXTRACT(YEAR FROM s.date), EXTRACT(MONTH FROM s.date), p1c.subcategory -//) -//SELECT -// ss.subcategory, -// ss.year, -// ss.month, -// ss.total_sum, -// ROUND( -// (SUM(ss.total_sum) OVER (PARTITION BY ss.month, ss.subcategory) / -// SUM(ss.total_sum) OVER (PARTITION BY ss.month)) * 100, -// 2 -// ) AS percent_of_month -//FROM subcategory_sales ss -//JOIN monthly_sales ms -// ON ss.month = ms.month AND ss.year = ms.year -// -- WHERE ss.month = 1 -- Фильтрация только для января -//ORDER BY ss.month, ss.subcategory, ss.year; - //4 = 40.59 * 78960 = 32049 -// 5WITH monthly_sales AS ( -// SELECT -// EXTRACT(MONTH FROM s.date) AS month, -// SUM(sp.summ) AS total_sales -// FROM erp24.sales_products sp -// LEFT JOIN erp24.sales s ON s.id = sp.check_id -// WHERE sp.summ IS NOT NULL -// GROUP BY EXTRACT(MONTH FROM s.date) -// ), -// subcategory_sales AS ( -// SELECT -// p1.name, -// p1.subcategory, -// EXTRACT(MONTH FROM s.date) AS month, -// SUM(sp.summ) AS total_sum -// FROM erp24.sales_products sp -// LEFT JOIN erp24.products_1c_nomenclature p1 ON p1.id = sp.product_id -// LEFT JOIN erp24.sales s ON s.id = sp.check_id -// WHERE p1.subcategory ILIKE '%Лиственные%' -// GROUP BY p1.name, p1.subcategory, EXTRACT(MONTH FROM s.date) -// ) -// SELECT -// ss.name, -// ss.subcategory, -// ss.month, -// ss.total_sum, -// ROUND( -// (ss.total_sum / SUM(ss.total_sum) OVER (PARTITION BY ss.month)) * 100, -// 2 -// ) AS percent_of_month -// FROM subcategory_sales ss -// JOIN monthly_sales ms -// ON ss.month = ms.month -// ORDER BY ss.month, ss.subcategory, ss.name; - - - - return $this->controller->render('auto-planogramma', [ - 'categories' => $categories, - 'stores' => $stores - ]); - } -} diff --git a/erp24/controllers/AutoPlanogrammaController.php b/erp24/controllers/AutoPlanogrammaController.php new file mode 100644 index 00000000..f1eacae6 --- /dev/null +++ b/erp24/controllers/AutoPlanogrammaController.php @@ -0,0 +1,111 @@ + [ + 'class' => \yii\filters\AccessControl::class, + 'rules' => [ + [ + 'allow' => true, + 'roles' => ['@'], + ], + ], + ], + ]; + } + + + public function actionIndex() { + $categories = (new Query()) + ->select([ + new Expression(" + CASE + WHEN category ILIKE 'срезка' THEN 'Срезка' + WHEN category ILIKE 'горшечные растения' THEN 'Горшечные растения' + WHEN category ILIKE 'сухоцветы' THEN 'Сухоцветы' + ELSE 'Остальные категории' + END AS category + "), + 'subcategory', + new Expression('json_agg(name) AS products') + ]) + ->from('products_1c_nomenclature') + ->groupBy(['category', 'subcategory']) + ->orderBy([ + new Expression(" + CASE + WHEN category ILIKE 'срезка' THEN 1 + WHEN category ILIKE 'горшечные растения' THEN 2 + WHEN category ILIKE 'сухоцветы' THEN 3 + ELSE 4 + END + "), + 'subcategory' => SORT_ASC + ]) + ->all(); + + $stores = ArrayHelper::map(CityStore::findAll(['visible' => CityStore::IS_VISIBLE]), 'id', 'name'); + + return $this->render('index', [ + 'stores' => $stores, + 'categories' => $categories + ]); + } + + public function actionTestSales() + { + $dateFrom = '2024-03-01'; + $datePlan = '2025-03-01'; + // Создаём экземпляр сервиса + $autoPlannogrammaService = new AutoPlannogrammaService(); + + // Шаг 1: Получаем долю по категориям + $monthCategoryShare = $autoPlannogrammaService->getMonthCategoryShare($dateFrom); + + // Шаг 2: Получаем цели по категориям + $monthCategoryGoal = $autoPlannogrammaService->getMonthCategoryGoal($monthCategoryShare, $datePlan); + + // Шаг 3: Получаем долю по подкатегориям + $monthSubcategoryShare = $autoPlannogrammaService->getMonthSubcategoryShare($dateFrom, $datePlan); + + // Шаг 4: Получаем цели по подкатегориям + $monthSubcategoryGoal = $autoPlannogrammaService->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal); + + // Шаг 5: Получаем долю по видам + $monthSpeciesShare = $autoPlannogrammaService->getMonthSpeciesShare($datePlan); + + // Шаг 6: Получаем цели по видам (final result) + $monthSpeciesGoalDirtry = $autoPlannogrammaService->getMonthSpeciesGoalDirtry($monthSpeciesShare, $monthSubcategoryGoal); + + $dataProvider = new ArrayDataProvider([ + 'allModels' => $monthSpeciesGoalDirtry, + 'pagination' => [ + 'pageSize' => 100 + ] + ]); + + return $this->render('test-sales', [ + 'dataProvider' => $dataProvider, + ]); + } + + public function actionTestWriteOffs() + { + return $this->render('test-write-offs'); + } + +} \ No newline at end of file diff --git a/erp24/controllers/ShipmentController.php b/erp24/controllers/ShipmentController.php index 02f7e87b..ba40173d 100755 --- a/erp24/controllers/ShipmentController.php +++ b/erp24/controllers/ShipmentController.php @@ -11,7 +11,6 @@ class ShipmentController extends Controller public function actions() { return [ 'add' => \yii_app\actions\shipment\AddAction::class, - 'auto-planogramma' => \yii_app\actions\shipment\AutoPlanogrammaAction::class, ]; } diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php new file mode 100644 index 00000000..7fecad0f --- /dev/null +++ b/erp24/services/AutoPlannogrammaService.php @@ -0,0 +1,253 @@ +from('sales') + ->leftJoin('sales_products sp2', 'sp2.check_id = sales.id') + ->where(['>=', 'date', $dateFrom]); + +// if ($isSales !== null) { +// $subQuery->andWhere(['sales.is_sale' => $isSales]); +// } + + $total = $subQuery->sum('sp2.summ'); + + $query = (new Query()) + ->select([ + 'category', + 'total_sum' => 'SUM(sp.summ)', + 'share_of_total' => new Expression( + 'ROUND(SUM(sp.summ) / :total, 4)', + [':total' => $total ?: 1] + ), + ]) + ->from('sales') + ->leftJoin('sales_products sp', 'sp.check_id = sales.id') + ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = sp.product_id') + ->andWhere(['>=', 'date', $dateFrom]) + ->andWhere(['<>', 'category', '']); + +// if ($isSales !== null) { +// $query->andWhere(['sales.is_sale' => $isSales]); +// } + + $query->groupBy('category'); + + return $query->all(); + } + + // шаг 2 + public function getMonthCategoryGoal(array $monthCategoryShare, $datePlan) + { + $calculating = []; + $timestamp = strtotime($datePlan); + $storesPlans = SalesWriteOffsPlan::find() + ->andWhere(['year' => date('Y', $timestamp), 'month' => date('m', $timestamp)]) + ->asArray() + ->all(); + foreach ($storesPlans as $storesPlan) { + foreach ($monthCategoryShare as $category) { + $calculating[] = [ + 'category' => $category['category'], + 'store_id' => $storesPlan['store_id'], + 'goal' => $category['share_of_total'] * $storesPlan['total_sales_plan'], + ]; + } + } + + return $calculating; + } + + // шаг 3 + public function getMonthSubcategoryShare($dateFrom, $datePlan) + { +// $dateFrom = $dateFrom ?? date('Y-m-d', strtotime('-1 year')); + + $monthlySales = (new Query()) + ->select([ + 'month' => new Expression('EXTRACT(MONTH FROM s.date)'), + 'year' => new Expression('EXTRACT(YEAR FROM s.date)'), + 'total_sales' => new Expression('SUM(sp.summ)'), + ]) + ->from(['sp' => 'sales_products']) + ->leftJoin(['s' => 'sales'], 's.id = sp.check_id') + ->where(['IS NOT', 'sp.summ', null]) + ->andWhere(['>=', 's.date', $dateFrom]) + ->groupBy([ + new Expression('EXTRACT(YEAR FROM s.date)'), + new Expression('EXTRACT(MONTH FROM s.date)'), + ]); + + $subcategorySales = (new Query()) + ->select([ + 'month' => new Expression('EXTRACT(MONTH FROM s.date)'), + 'year' => new Expression('EXTRACT(YEAR FROM s.date)'), + 'category' => 'p1c.category', + 'subcategory' => 'p1c.subcategory', + 'total_sum' => new Expression('SUM(sp.summ)'), + ]) + ->from(['sp' => 'sales_products']) + ->leftJoin(['p1c' => 'products_1c_nomenclature'], 'p1c.id = sp.product_id') + ->leftJoin(['s' => 'sales'], 's.id = sp.check_id') + ->andWhere(['>=', 's.date', $dateFrom]) + ->groupBy([ + new Expression('EXTRACT(YEAR FROM s.date)'), + new Expression('EXTRACT(MONTH FROM s.date)'), + 'p1c.category', + 'p1c.subcategory', + ]); + + $query = (new Query()) + ->select([ + 'ss.category', + 'ss.subcategory', + 'ss.year', + 'ss.month', + 'ss.total_sum', + 'percent_of_month' => new Expression( + '(ROUND(SUM(ss.total_sum) OVER (PARTITION BY ss.month, ss.category, ss.subcategory) / SUM(ss.total_sum) OVER (PARTITION BY ss.month, ss.category)), 4)' + ), + ]) + ->from(['ss' => $subcategorySales]) + ->innerJoin(['ms' => $monthlySales], 'ss.month = ms.month AND ss.year = ms.year') + ->where(['ss.month' => date('m', strtotime($datePlan))]) + ->orderBy([ + 'ss.category' => SORT_ASC, + 'ss.subcategory' => SORT_ASC, + 'ss.month' => SORT_ASC, + 'ss.year' => SORT_ASC, + ]); + + return $query->all(); + } + + // шаг 4 + public function getMonthSubcategoryGoal(array $monthSubcategoryShare, array $monthCategoryGoal) + { + $calculating = []; + + $goalIndex = []; + foreach ($monthCategoryGoal as $goal) { + $goalIndex[$goal['category']][] = $goal; + } +// echo '
';
+//        var_dump($monthSubcategoryShare);
+//        echo '
'; +// die; + + foreach ($monthSubcategoryShare as $shareData) { + if (isset($shareData['category'], $shareData['subcategory'], $shareData['percent_of_month'])) { + if (isset($goalIndex[$shareData['category']])) { + foreach ($goalIndex[$shareData['category']] as $goal) { + if (isset($goal['store_id'], $goal['goal'])) { + $calculating[] = [ + 'category' => $shareData['category'], + 'subcategory'=> $shareData['subcategory'], + 'store_id' => $goal['store_id'], + 'goal' => $goal['goal'] * floatval(strtr($shareData['percent_of_month'], [',' => '.', '(' => '', ')' => ''])), + ]; + } + } + } + } + } + + return $calculating; + } + + // шаг 5 + public function getMonthSpeciesShare($datePlan) + { + $monthlySales = (new Query()) + ->select([ + 'month' => new Expression('EXTRACT(MONTH FROM s.date)'), + 'total_sales' => new Expression('SUM(sp.summ)'), + ]) + ->from(['sp' => 'sales_products']) + ->leftJoin(['s' => 'sales'], 's.id = sp.check_id') + ->where(['IS NOT', 'sp.summ', null]) + ->groupBy(new Expression('EXTRACT(MONTH FROM s.date)')); + + $subcategorySales = (new Query()) + ->select([ + 'category' => 'p1.category', + 'name' => 'p1.name', + 'subcategory' => 'p1.subcategory', + 'month' => new Expression('EXTRACT(MONTH FROM s.date)'), + 'total_sum' => new Expression('SUM(sp.summ)'), + ]) + ->from(['sp' => 'sales_products']) + ->leftJoin(['p1' => 'products_1c_nomenclature'], 'p1.id = sp.product_id') + ->leftJoin(['s' => 'sales'], 's.id = sp.check_id') + ->groupBy(['p1.category', 'p1.name', 'p1.subcategory', new Expression('EXTRACT(MONTH FROM s.date)')]); + + if ($datePlan !== null) { + $month = date('m', strtotime($datePlan)); + $monthlySales->andWhere(['EXTRACT(MONTH FROM s.date)' => $month]); + $subcategorySales->andWhere(['EXTRACT(MONTH FROM s.date)' => $month]); + } + + $query = (new Query()) + ->select([ + 'ss.category', + 'ss.name', + 'ss.subcategory', + 'ss.month', + 'ss.total_sum', + 'percent_of_month' => new Expression( + 'ROUND(ss.total_sum / SUM(ss.total_sum) OVER (PARTITION BY ss.month), 6)' + ), + ]) + ->from(['ss' => $subcategorySales]) + ->innerJoin(['ms' => $monthlySales], 'ss.month = ms.month') + ->orderBy([ + 'ss.month' => SORT_ASC, + 'ss.category' => SORT_ASC, + 'ss.subcategory' => SORT_ASC, + 'ss.name' => SORT_ASC, + ]); + + return $query->all(); + } + + // шаг 6 + public function getMonthSpeciesGoalDirtry(array $monthSpeciesShare, array $monthSubcategoryGoal) + { + $calculating = []; + + foreach ($monthSpeciesShare as $species) { + foreach ($monthSubcategoryGoal as $goalItem) { + if ( + $species['category'] === $goalItem['category'] && + $species['subcategory'] === $goalItem['subcategory'] + ) { + $percent = floatval(strtr($species['percent_of_month'], [',' => '.', '(' => '', ')' => ''])); + $goalValue = floatval($goalItem['goal']); + $result = $percent * $goalValue; + + $calculating[] = [ + 'category' => $species['category'], + 'subcategory' => $species['subcategory'], + 'species' => $species['name'], + 'store_id' => $goalItem['store_id'], + 'goal' => $result, + ]; + } + } + } + + return $calculating; + } +} diff --git a/erp24/views/shipment/auto-planogramma.php b/erp24/views/auto-planogramma/index.php similarity index 100% rename from erp24/views/shipment/auto-planogramma.php rename to erp24/views/auto-planogramma/index.php diff --git a/erp24/views/auto-planogramma/test-sales.php b/erp24/views/auto-planogramma/test-sales.php new file mode 100644 index 00000000..609827b2 --- /dev/null +++ b/erp24/views/auto-planogramma/test-sales.php @@ -0,0 +1,92 @@ +select('category')->distinct()->all(), 'category', 'category'); +////$subcategories = $categories = ArrayHelper::map(Products1cNomenclature::find()->select('category')->distinct()->all(), 'category', 'subcategory'); +////$speciesList = $categories = ArrayHelper::map(Products1cNomenclature::find()->select('category')->distinct()->all(), 'category', 'species'); +////$stores = $categories = ArrayHelper::map(Products1cNomenclature::find()->select('category')->distinct()->all(), 'category', 'category'); +// +////$filter = Yii::$app->request->get('filter', []); +//// +////$filteredData = array_filter($monthSpeciesGoalDirtry, function ($item) use ($filter) { +//// foreach ($filter as $key => $value) { +//// if ($value !== '' && $item[$key] != $value) { +//// return false; +//// } +//// } +//// return true; +////}); +// +//$dataProvider = new ArrayDataProvider([ +// 'allModels' => $filteredData, +// 'pagination' => [ +// 'pageSize' => 20, +// ], +//]); +// +//?> + + 'get']); ?> + + + 'filter[category]', +// 'value' => $filter['category'] ?? '', +// 'data' => ArrayHelper::map($categories, fn($v) => $v, fn($v) => $v), +// 'options' => ['placeholder' => 'Категория'], +// 'pluginOptions' => ['allowClear' => true], +// ]) ?> + + 'filter[subcategory]', +// 'value' => $filter['subcategory'] ?? '', +// 'data' => ArrayHelper::map($subcategories, fn($v) => $v, fn($v) => $v), +// 'options' => ['placeholder' => 'Подкатегория'], +// 'pluginOptions' => ['allowClear' => true], +// ]) ?> + + 'filter[species]', +// 'value' => $filter['species'] ?? '', +// 'data' => ArrayHelper::map($speciesList, fn($v) => $v, fn($v) => $v), +// 'options' => ['placeholder' => 'Вид'], +// 'pluginOptions' => ['allowClear' => true], +// ]) ?> + + 'filter[store_id]', +// 'value' => $filter['store_id'] ?? '', +// 'data' => ArrayHelper::map($stores, fn($v) => $v, fn($v) => 'Магазин ' . $v), +// 'options' => ['placeholder' => 'Магазин'], +// 'pluginOptions' => ['allowClear' => true], +// ]) ?> + + 'btn btn-primary']) ?> + 'btn btn-default']) ?> + + + + + $dataProvider, + 'columns' => [ + 'category', + 'subcategory', + 'species', + 'store_id', + [ + 'attribute' => 'goal', + 'format' => ['decimal', 4], + 'label' => 'План закупок(RUB)', + ], + ], +]); ?> -- 2.39.5