From baa7d82ccf576ba83baebe72a88e6d3eb95123f5 Mon Sep 17 00:00:00 2001 From: marina Date: Fri, 18 Apr 2025 14:36:10 +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 --- .../AutoPlannogrammaController.php | 167 ++++++++---------- 1 file changed, 77 insertions(+), 90 deletions(-) diff --git a/erp24/controllers/AutoPlannogrammaController.php b/erp24/controllers/AutoPlannogrammaController.php index d4c97463..3550bd6b 100644 --- a/erp24/controllers/AutoPlannogrammaController.php +++ b/erp24/controllers/AutoPlannogrammaController.php @@ -8,13 +8,10 @@ use yii\db\Expression; use yii\db\Query; use yii\helpers\ArrayHelper; use yii_app\records\CityStore; -use yii_app\records\Sales; use yii_app\services\AutoPlannogrammaService; class AutoPlannogrammaController extends BaseController { - - public function behaviors() { return [ @@ -30,42 +27,45 @@ class AutoPlannogrammaController extends BaseController ]; } - 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 - "), + 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') + 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 + 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'); + $stores = ArrayHelper::map( + CityStore::findAll(['visible' => CityStore::IS_VISIBLE]), + 'id', + 'name' + ); return $this->render('index', [ 'stores' => $stores, - 'categories' => $categories + 'categories' => $categories, ]); } @@ -74,45 +74,30 @@ class AutoPlannogrammaController extends BaseController $request = Yii::$app->request; $filters = [ - 'category' => $request->get('category'), - 'subcategory' => $request->get('subcategory'), - 'product_name' => $request->get('product_name'), - 'store_id' => $request->get('store_id'), - 'plan_date' => $request->get('plan_date'), - 'type' => $request->get('type'), + 'category' => $request->get('category'), + 'subcategory' => $request->get('subcategory'), + 'product_name' => $request->get('product_name'), + 'store_id' => $request->get('store_id'), + 'plan_date' => $request->get('plan_date'), + 'type' => $request->get('type'), ]; $dataProvider = new ArrayDataProvider([ 'allModels' => [], - 'pagination' => [ - 'pageSize' => 100 - ], + 'pagination' => ['pageSize' => 100], ]); if (!empty($filters['plan_date'])) { - $filters = [ - 'category' => $request->get('category'), - 'subcategory' => $request->get('subcategory'), - 'product_name' => $request->get('product_name'), - 'store_id' => $request->get('store_id'), - 'plan_date' => $request->get('plan_date'), - 'type' => $request->get('type'), - ]; - $service = new AutoPlannogrammaService(); - $dataProvider = new ArrayDataProvider([ 'allModels' => $service->calculateFullGoalChain($filters), - 'pagination' => [ - 'pageSize' => 100 - ], + 'pagination' => ['pageSize' => 100], ]); - } return $this->render('calculating', [ 'dataProvider' => $dataProvider, - 'filters' => $filters, + 'filters' => $filters, ]); } @@ -162,52 +147,54 @@ class AutoPlannogrammaController extends BaseController } $where = !empty($conditions) ? 'WHERE ' . implode(' AND ', $conditions) : ''; - if (isset($filters['type']) && $filters['type'] == 'writeOffs') { + + if (isset($filters['type']) && $filters['type'] === 'writeOffs') { $query = Yii::$app->db->createCommand(" - SELECT - c.name AS city_name, - p1n.category, - p1n.subcategory, - item ->> 'product_id' AS product_id, - p1n.name AS product_name, - SUM(CAST(item ->> 'quantity' AS NUMERIC)) AS count, - SUM(CAST(item ->> 'summ' AS NUMERIC)) AS sum - FROM write_offs w - JOIN export_import_table ex ON ex.export_val = w.store_id - JOIN city_store c ON c.id = ex.entity_id - JOIN LATERAL jsonb_array_elements(w.items::jsonb) AS item ON true - LEFT JOIN products_1c_nomenclature p1n ON p1n.id = item ->> 'product_id' - $where - GROUP BY w.store_id, c.name, p1n.category, p1n.subcategory, product_id, p1n.name - ORDER BY w.store_id, p1n.category, p1n.subcategory, p1n.name - ")->bindValues($params)->queryAll(); + SELECT + c.name AS city_name, + p1n.category, + p1n.subcategory, + item ->> 'product_id' AS product_id, + p1n.name AS product_name, + SUM(CAST(item ->> 'quantity' AS NUMERIC)) AS count, + SUM(CAST(item ->> 'summ' AS NUMERIC)) AS sum + FROM write_offs w + JOIN export_import_table ex ON ex.export_val = w.store_id + JOIN city_store c ON c.id = ex.entity_id + JOIN LATERAL jsonb_array_elements(w.items::jsonb) AS item ON true + LEFT JOIN products_1c_nomenclature p1n ON p1n.id = item ->> 'product_id' + $where + GROUP BY w.store_id, c.name, p1n.category, p1n.subcategory, product_id, p1n.name + ORDER BY w.store_id, p1n.category, p1n.subcategory, p1n.name + ")->bindValues($params)->queryAll(); } else { $query = Yii::$app->db->createCommand(" - SELECT - c.name AS city_name, - p1n.name AS product_name, - p1n.category, - p1n.subcategory, - SUM(CASE - WHEN s.operation = 'Продажа' THEN sp.quantity - WHEN s.operation = 'Возврат' THEN -sp.quantity - ELSE 0 - END) AS count, - SUM(CASE - WHEN s.operation = 'Продажа' THEN sp.summ - WHEN s.operation = 'Возврат' THEN -sp.summ - ELSE 0 - END) AS sum - FROM sales s - LEFT JOIN sales_products sp ON sp.check_id = s.id - LEFT JOIN products_1c_nomenclature p1n ON p1n.id = sp.product_id - LEFT JOIN export_import_table ex ON ex.export_val = store_id_1c - LEFT JOIN city_store c ON c.id = entity_id - $where - GROUP BY p1n.name, p1n.category, p1n.subcategory, c.name, c.id - ORDER BY c.id DESC, category, subcategory, p1n.name -")->bindValues($params)->queryAll(); + SELECT + c.name AS city_name, + p1n.name AS product_name, + p1n.category, + p1n.subcategory, + SUM(CASE + WHEN s.operation = 'Продажа' THEN sp.quantity + WHEN s.operation = 'Возврат' THEN -sp.quantity + ELSE 0 + END) AS count, + SUM(CASE + WHEN s.operation = 'Продажа' THEN sp.summ + WHEN s.operation = 'Возврат' THEN -sp.summ + ELSE 0 + END) AS sum + FROM sales s + LEFT JOIN sales_products sp ON sp.check_id = s.id + LEFT JOIN products_1c_nomenclature p1n ON p1n.id = sp.product_id + LEFT JOIN export_import_table ex ON ex.export_val = store_id_1c + LEFT JOIN city_store c ON c.id = entity_id + $where + GROUP BY p1n.name, p1n.category, p1n.subcategory, c.name, c.id + ORDER BY c.id DESC, category, subcategory, p1n.name + ")->bindValues($params)->queryAll(); } + $dataProvider = new ArrayDataProvider([ 'allModels' => $query, 'pagination' => ['pageSize' => 20], @@ -215,7 +202,7 @@ class AutoPlannogrammaController extends BaseController return $this->render('control', [ 'dataProvider' => $dataProvider, - 'filters' => $filters, + 'filters' => $filters, ]); } -} \ No newline at end of file +} -- 2.39.5