From ae5624a1564713f70c65d4e09cc2e3701b2d79bb Mon Sep 17 00:00:00 2001
From: marina <m.zozirova@gmail.com>
Date: Thu, 17 Apr 2025 09:17:51 +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

---
 .../controllers/AutoPlanogrammaController.php | 40 +++++++++++--------
 erp24/services/AutoPlannogrammaService.php    | 14 +++++--
 2 files changed, 35 insertions(+), 19 deletions(-)

diff --git a/erp24/controllers/AutoPlanogrammaController.php b/erp24/controllers/AutoPlanogrammaController.php
index 45687c16..4af47320 100644
--- a/erp24/controllers/AutoPlanogrammaController.php
+++ b/erp24/controllers/AutoPlanogrammaController.php
@@ -177,22 +177,30 @@ class AutoPlanogrammaController extends BaseController
         $where = implode(' AND ', $conditions);
 
         $query = Yii::$app->db->createCommand("
-        SELECT 
-            c.name AS city_name,
-            p1.name AS product_name,
-            p1.category, 
-            p1.subcategory,
-            SUM(sp.quantity) AS count, 
-            SUM(sp.summ) AS sum
-        FROM sales s
-        LEFT JOIN sales_products sp ON sp.check_id = s.id
-        LEFT JOIN products_1c_nomenclature p1 ON p1.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 $where
-        GROUP BY p1.name, p1.category, p1.subcategory, c.name, c.id
-        ORDER BY c.id DESC, category, subcategory, p1.name
-    ")->bindValues($params)->queryAll();
+    SELECT 
+        c.name AS city_name,
+        p1.name AS product_name,
+        p1.category, 
+        p1.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 p1 ON p1.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 $where
+    GROUP BY p1.name, p1.category, p1.subcategory, c.name, c.id
+    ORDER BY c.id DESC, category, subcategory, p1.name
+")->bindValues($params)->queryAll();
 
         $dataProvider = new ArrayDataProvider([
             'allModels' => $query,
diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php
index a57736f7..6fd32f13 100644
--- a/erp24/services/AutoPlannogrammaService.php
+++ b/erp24/services/AutoPlannogrammaService.php
@@ -14,17 +14,24 @@ class AutoPlannogrammaService
         return CityStore::findAll(['visible' => CityStore::IS_VISIBLE]);
     }
 
-    private function getStoreSalesTotals(array $storeIds, $dateFrom, $productFilter = null): array
+    private function getStoreSalesTotals(array $storeIds, string $dateFrom, $productFilter = null): array
     {
         $query = (new Query())
             ->select([
                 'store_id' => 'ex.entity_id',
-                'total_sum' => new Expression('SUM(sp.summ)')
+                'total_sum' => new Expression("
+                SUM(CASE 
+                        WHEN s.operation = 'Продажа' THEN sp.summ
+                        WHEN s.operation = 'Возврат' THEN -sp.summ
+                        ELSE 0
+                    END)
+            ")
             ])
             ->from(['s' => 'sales'])
             ->leftJoin('sales_products sp', 'sp.check_id = s.id')
             ->leftJoin('export_import_table ex', 'ex.export_val = s.store_id_1c')
             ->where(['>=', 's.date', $dateFrom])
+            ->andWhere(['ex.entity_id' => $storeIds])
             ->groupBy('ex.entity_id');
 
         if ($productFilter !== null) {
@@ -33,8 +40,9 @@ class AutoPlannogrammaService
 
         $rows = $query->all();
         $totals = [];
+
         foreach ($rows as $row) {
-            $totals[$row['store_id']] = $row['total_sum'];
+            $totals[(int)$row['store_id']] = (float)$row['total_sum'];
         }
 
         return $totals;
-- 
2.39.5