From 3deaf89e78a3730dea408aac39eace69644d845d Mon Sep 17 00:00:00 2001 From: marina Date: Fri, 4 Apr 2025 15:08:12 +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 | 144 +++++++++++++-- erp24/views/shipment/auto-planogramma.php | 169 ++++++------------ 2 files changed, 180 insertions(+), 133 deletions(-) diff --git a/erp24/actions/shipment/AutoPlanogrammaAction.php b/erp24/actions/shipment/AutoPlanogrammaAction.php index 7479fee6..4b8737da 100644 --- a/erp24/actions/shipment/AutoPlanogrammaAction.php +++ b/erp24/actions/shipment/AutoPlanogrammaAction.php @@ -6,6 +6,8 @@ use yii\base\Action; use yii\data\ArrayDataProvider; use yii\db\Expression; use yii\db\Query; +use yii\helpers\ArrayHelper; +use yii_app\records\CityStore; use yii_app\records\Products1cNomenclature; @@ -13,32 +15,136 @@ class AutoPlanogrammaAction extends Action { public function run() { - $categories = Products1cNomenclature::find() + $categories = (new Query()) ->select([ - 'category', - new Expression("jsonb_object_agg(subcategory, products) AS subcategories") + 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 ]) - ->from( - (new Query()) - ->select([ - 'category', - 'subcategory', - new Expression('array_agg(name) AS products') - ]) - ->from('products_1c_nomenclature') - ->andWhere(['ilike', 'name', 'роз']) - ->groupBy(['category', 'subcategory']) - ) - ->groupBy('category') - ->asArray() ->all(); - $dataProvider = new ArrayDataProvider([ + $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 + 'categories' => $categories, + 'stores' => $stores ]); } } diff --git a/erp24/views/shipment/auto-planogramma.php b/erp24/views/shipment/auto-planogramma.php index 481fae8a..d330c0bd 100644 --- a/erp24/views/shipment/auto-planogramma.php +++ b/erp24/views/shipment/auto-planogramma.php @@ -18,24 +18,6 @@ use yii\jui\Accordion; use yii\data\ArrayDataProvider; -$data = [ - ['Адрес' => '01 Ванеева 181', 'Значение' => 300], - ['Адрес' => '02 Плотникова 4', 'Значение' => 300], - ['Адрес' => '03 Гагарина 60', 'Значение' => 100], - ['Адрес' => '04 Московское шоссе 9а', 'Значение' => 50], - ['Адрес' => '05 Белинского 71', 'Значение' => 300], - ['Адрес' => '06 Коминтерна 168', 'Значение' => 160], - ['Адрес' => '07 Аэродромная', 'Значение' => 300], -]; -$addresses = array_column($data, 'Адрес'); -$values = array_column($data, 'Значение'); - -$row = array_combine($addresses, $values); -$dataProvider = new ArrayDataProvider([ - 'allModels' => [$row], - 'pagination' => false, -]); - $tooltipText = "Оффлайн: 50 Флаумак (off-line): 20 Флаумак (on-line): 10 Флаумак (market): 10 Изумительная (off-line): 30 Списания: 20"; ?>

@@ -167,7 +149,6 @@ $tooltipText = "Оффлайн: 50 Флаумак (off-line): 20 Фла
- - + - - - + - - - - + // Группируем данные по категориям и подкатегориям + foreach ($categories as $item) { + $groupedCategories[$item['category']][$item['subcategory']] = $item['products']; + } - - + - - - - - - - ]) ?> - - - - - - - - - - - - - - - - - - - - + $products): ?> + + + + + + + + + + + + + +
@@ -186,9 +167,9 @@ $tooltipText = "Оффлайн: 50 Флаумак (off-line): 20 Фла - 'writing-mode: sideways-lr; text-align: center; white-space: nowrap; font-weight: bold; transform-origin: left bottom; padding-right: 7%;' ]) ?> @@ -197,104 +178,64 @@ $tooltipText = "Оффлайн: 50 Флаумак (off-line): 20 Фла
- Срезка - ▲ -
- - Розы ▶ - -
- - Розы 40 Эк - - -
- 'btn btn-primary input', - 'data-bs-toggle' => 'tooltip', - 'data-bs-placement' => 'top', - 'title' => $tooltipText, - ]) ?> - -
+ foreach ($groupedCategories as $category => $subcategories): ?> +
+ + ▲ +
- - Розы 80 Эк - - -
- 'btn btn-primary input', - 'data-bs-toggle' => 'tooltip', - 'data-bs-placement' => 'top', - 'title' => $tooltipText, +
- - Экзотика ▶ - -
- - Зелень ▶ - -
- Горшечные растения ▲ -
- Сухоцветы ▲ -
- Остальные категории ▲ -
+ + ▶ + +
+ + + + +
+ 'btn btn-primary input', + 'data-bs-toggle' => 'tooltip', + 'data-bs-placement' => 'top', + 'title' => $tooltipText, + ]) ?> + +
+
-- 2.39.5