From d7606b0bcd31940794dbed32a3a333f7fabcd66f Mon Sep 17 00:00:00 2001
From: marina <m.zozirova@gmail.com>
Date: Wed, 16 Apr 2025 14:03:22 +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 | 144 ++++--
 erp24/services/AutoPlannogrammaService.php    | 442 +++++++++---------
 erp24/views/auto-planogramma/sales.php        | 132 +++++-
 erp24/views/auto-planogramma/test-sales.php   |  98 +++-
 4 files changed, 525 insertions(+), 291 deletions(-)

diff --git a/erp24/controllers/AutoPlanogrammaController.php b/erp24/controllers/AutoPlanogrammaController.php
index 02e7ae9a..0f4e8697 100644
--- a/erp24/controllers/AutoPlanogrammaController.php
+++ b/erp24/controllers/AutoPlanogrammaController.php
@@ -71,64 +71,122 @@ class AutoPlanogrammaController extends BaseController
 
     public function actionTestSales()
     {
-        $datePlan = '2025-04-01'; //плановое
-        $dateFromForCategory = date('Y-m-d', strtotime($datePlan . ' -12 months')); // смотрим 12 месяца назад
-        $dateFrom = date('Y-m-d', strtotime($datePlan . ' -3 months')); // смотрим 3 месяца назад
+        $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'),
+        ];
 
-        $autoPlannogrammaService = new AutoPlannogrammaService();
+        $dataProvider = new ArrayDataProvider([
+            'allModels' => [],
+            'pagination' => [
+                'pageSize' => 100
+            ],
+        ]);
 
-        // Шаг 1: Получаем долю по категориям
-        $monthCategoryShare = $autoPlannogrammaService->getMonthCategoryShare($dateFromForCategory);
+        if (!empty($filters['plan_date'])) {
 
-        // Шаг 2: Получаем цели по категориям
-        $monthCategoryGoal = $autoPlannogrammaService->getMonthCategoryGoal($monthCategoryShare, $datePlan);
+            $datePlan = $filters['plan_date'];
+            $dateFromForCategory = date('Y-m-d', strtotime($datePlan . ' -12 months'));
+            $dateFrom = date('Y-m-d', strtotime($datePlan . ' -3 months'));
 
-        // Шаг 3: Получаем долю по подкатегориям
-        $monthSubcategoryShare = $autoPlannogrammaService->getMonthSubcategoryShare($dateFrom, $datePlan);
-//        echo '<pre>';var_dump($monthSubcategoryShare);
-//        echo '</pre>';die();
-        // Шаг 4: Получаем цели по подкатегориям
-        $monthSubcategoryGoal = $autoPlannogrammaService->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal);
+            $autoPlannogrammaService = new AutoPlannogrammaService();
 
-        // Шаг 5: Получаем долю по видам
-        $monthSpeciesShare = $autoPlannogrammaService->getMonthSpeciesShare($datePlan);
+            $monthCategoryShare = $autoPlannogrammaService->getMonthCategoryShare($dateFromForCategory);
+            $monthCategoryGoal = $autoPlannogrammaService->getMonthCategoryGoal($monthCategoryShare, $datePlan);
+            $monthSubcategoryShare = $autoPlannogrammaService->getMonthSubcategoryShare($dateFrom, $datePlan);
+            $monthSubcategoryGoal = $autoPlannogrammaService->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal);
+            $monthSpeciesShare = $autoPlannogrammaService->getMonthSpeciesShare($dateFrom, $datePlan);
+            $monthSpeciesGoalDirtry = $autoPlannogrammaService->getMonthSpeciesGoalDirtry($monthSpeciesShare, $monthSubcategoryGoal);
 
-        // Шаг 6: Получаем цели по видам (final result)
-        $monthSpeciesGoalDirtry = $autoPlannogrammaService->getMonthSpeciesGoalDirtry($monthSpeciesShare, $monthSubcategoryGoal);
 
-        $dataProvider = new ArrayDataProvider([
-            'allModels' => $monthSpeciesGoalDirtry,
-            'pagination' => [
-                'pageSize' => 100
-            ]
-        ]);
+            $filtered = array_filter($monthSpeciesGoalDirtry, function ($row) use ($filters) {
+                foreach ($filters as $key => $value) {
+                    if ($value !== null && $value !== '' && isset($row[$key]) && stripos($row[$key], $value) === false) {
+                        return false;
+                    }
+                }
+                return true;
+            });
+
+            $dataProvider = new ArrayDataProvider([
+                'allModels' => array_values($filtered),
+                'pagination' => [
+                    'pageSize' => 100
+                ],
+            ]);
+        }
 
         return $this->render('test-sales', [
             'dataProvider' => $dataProvider,
+            'filters' => $filters,
         ]);
     }
 
     public function actionSales()
     {
+        $filters = Yii::$app->request->get();
+
+        $params = [];
+
+        if (!empty($filters['product_name'])) {
+            $conditions[] = 'p1.name LIKE :product_name';
+            $params[':product_name'] = '%' . $filters['product_name'] . '%';
+        }
+
+        if (!empty($filters['category'])) {
+            $conditions[] = 'p1.category = :category';
+            $params[':category'] = $filters['category'];
+        }
+
+        if (!empty($filters['subcategory'])) {
+            $conditions[] = 'p1.subcategory = :subcategory';
+            $params[':subcategory'] = $filters['subcategory'];
+        }
+
+        if (!empty($filters['city_name'])) {
+            $conditions[] = 'c.name LIKE :city_name';
+            $params[':city_name'] = '%' . $filters['city_name'] . '%';
+        }
+
+        if (!empty($filters['plan_date'])) {
+            // Преобразуем дату в формат Y-m-d
+            $date = \DateTime::createFromFormat('d-m-Y', $filters['plan_date']);
+            if ($date) {
+                $conditions[] = 'DATE(s.date) = :plan_date';
+                $params[':plan_date'] = $date->format('Y-m-d');
+            }
+        }
+
+        if (!empty($filters['type'])) {
+            $conditions[] = 's.type = :type';
+            $params[':type'] = $filters['type'];
+        }
+
+        $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 s.date >= '2024-04-01' 
- and p1.name = '' 
-	group by p1.name, p1.category, p1.subcategory, 	c.name, c.id
- order by c.id desc, category, subcategory, p1.name
-")->queryAll();
+        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();
 
         $dataProvider = new ArrayDataProvider([
             'allModels' => $query,
@@ -136,8 +194,10 @@ class AutoPlanogrammaController extends BaseController
         ]);
 
         return $this->render('sales', [
-            'dataProvider' => $dataProvider
+            'dataProvider' => $dataProvider,
+            'filters' => $filters,
         ]);
+
     }
 
     public function actionTestWriteOffs()
diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php
index 9d0b2eb4..a57736f7 100644
--- a/erp24/services/AutoPlannogrammaService.php
+++ b/erp24/services/AutoPlannogrammaService.php
@@ -1,284 +1,276 @@
 <?php
 
-
 namespace yii_app\services;
 
 use yii\db\Expression;
 use yii\db\Query;
 use yii_app\records\CityStore;
-use yii_app\records\Sales;
 use yii_app\records\SalesWriteOffsPlan;
 
 class AutoPlannogrammaService
 {
-    // шаг 1
-    public function getMonthCategoryShare($dateFrom)
+    private function getVisibleStores(): array
+    {
+        return CityStore::findAll(['visible' => CityStore::IS_VISIBLE]);
+    }
+
+    private function getStoreSalesTotals(array $storeIds, $dateFrom, $productFilter = null): array
     {
-        $percentArray = [];
-
-        $stores = CityStore::findAll(['visible' => CityStore::IS_VISIBLE]);
-
-        foreach ($stores as $store) {
-            // Правильный подзапрос для totalSum с учётом связи с city_store
-            $totalSum = (new Query())
-                ->select(['SUM(sp2.summ)'])
-                //вычитать возвраты
-                ->from('sales s2')
-                ->leftJoin('sales_products sp2', 'sp2.check_id = s2.id')
-                ->leftJoin('export_import_table ex2', 'ex2.export_val = s2.store_id_1c')
-                ->leftJoin('city_store cs', 'cs.id = ex2.entity_id')
-                ->where(['>=', 's2.date', $dateFrom])
-                //границу 3 месяца
-                ->andWhere(['cs.id' => $store->id])
-                ->scalar();
-
-            $query = (new Query())
-                ->select([
-                    'p1c.category',
-                    'total_sum' => new Expression('SUM(sp.summ)'),
-                    'share_of_total' => new Expression('ROUND(SUM(sp.summ) / :total, 4)', [
-                        ':total' => $totalSum ?: 1,
-                    ]),
-                ])
-                ->from('sales s')
-                ->leftJoin('sales_products sp', 'sp.check_id = s.id')
-                ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = sp.product_id')
-                ->leftJoin('export_import_table ex', 'ex.export_val = s.store_id_1c')
-                ->leftJoin('city_store cs', 'cs.id = ex.entity_id')
-                ->where(['<>', 'p1c.category', ''])
-                ->andWhere(['cs.id' => $store->id])
-                ->groupBy(['p1c.category'])
-                ->addParams([':total' => $totalSum ?: 1]);
-
-            $percentArray[$store->id] = $query->all();
+        $query = (new Query())
+            ->select([
+                'store_id' => 'ex.entity_id',
+                'total_sum' => new Expression('SUM(sp.summ)')
+            ])
+            ->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])
+            ->groupBy('ex.entity_id');
+
+        if ($productFilter !== null) {
+            $query->andWhere(['sp.product_id' => $productFilter]);
+        }
+
+        $rows = $query->all();
+        $totals = [];
+        foreach ($rows as $row) {
+            $totals[$row['store_id']] = $row['total_sum'];
         }
 
-        return $percentArray;
+        return $totals;
     }
 
-    // шаг 2
-        public function getMonthCategoryGoal(array $monthCategoryShare, $datePlan)
+    public function getMonthCategoryShare($dateFrom, $storeFilter = null, $productFilter = null): array
     {
-        $calculating = [];
-        $timestamp = strtotime($datePlan);
+        $stores = $this->getVisibleStores();
+        $storeIds = array_map(fn($s) => $s->id, $stores);
+        if ($storeFilter !== null) {
+            $storeIds = array_intersect($storeIds, (array)$storeFilter);
+        }
+
+        $totals = $this->getStoreSalesTotals($storeIds, $dateFrom, $productFilter);
+        if (empty($totals)) return [];
 
-        $storesPlans = SalesWriteOffsPlan::find()
-            ->andWhere([
-                'year' => date('Y', $timestamp),
-                'month' => date('m', $timestamp)
+        $query = (new Query())
+            ->select([
+                'store_id' => 'ex.entity_id',
+                'category' => 'p1c.category',
+                'total_sum' => new Expression('SUM(sp.summ)')
             ])
+            ->from(['s' => 'sales'])
+            ->leftJoin('sales_products sp', 'sp.check_id = s.id')
+            ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = sp.product_id')
+            ->leftJoin('export_import_table ex', 'ex.export_val = s.store_id_1c')
+            ->where(['>=', 's.date', $dateFrom])
+            ->andWhere(['ex.entity_id' => $storeIds])
+            ->andWhere(['<>', 'p1c.category', ''])
+            ->groupBy(['ex.entity_id', 'p1c.category']);
+
+        if ($productFilter !== null) {
+            $query->andWhere(['sp.product_id' => $productFilter]);
+        }
+
+        $rows = $query->all();
+        $result = [];
+        foreach ($rows as $row) {
+            $storeId = $row['store_id'];
+            $total = $totals[$storeId] ?? 1;
+            $result[$storeId][] = [
+                'category' => $row['category'],
+                'total_sum' => $row['total_sum'],
+                'share_of_total' => round($row['total_sum'] / $total, 4),
+            ];
+        }
+
+        return $result;
+    }
+
+    public function getMonthCategoryGoal(array $categoryShare, $datePlan): array
+    {
+        $timestamp = strtotime($datePlan);
+        $year = date('Y', $timestamp);
+        $month = date('m', $timestamp);
+
+        $plans = SalesWriteOffsPlan::find()
+            ->where(['year' => $year, 'month' => $month])
             ->asArray()
+            ->indexBy('store_id')
             ->all();
 
-        foreach ($storesPlans as $storesPlan) {
-            $storeId = $storesPlan['store_id'];
+        $result = [];
 
-            // Проверяем, есть ли данные по категориям для этого магазина
-            if (!isset($monthCategoryShare[$storeId])) {
-                continue;
-            }
+        foreach ($plans as $storeId => $plan) {
+            if (!isset($categoryShare[$storeId])) continue;
 
-            foreach ($monthCategoryShare[$storeId] as $categoryData) {
-                $calculating[] = [
-                    'category' => $categoryData['category'],
+            foreach ($categoryShare[$storeId] as $item) {
+                $result[] = [
+                    'category' => $item['category'],
                     'store_id' => $storeId,
-                    'goal' => $categoryData['share_of_total'] * $storesPlan['total_sales_plan'],
+                    'goal' => round($item['share_of_total'] * $plan['total_sales_plan'], 2),
                 ];
             }
         }
 
-        return $calculating;
+        return $result;
     }
 
-
-    // шаг 3
-    public function getMonthSubcategoryShare($dateFrom, $datePlan)
+    public function getMonthSubcategoryShare($dateFrom, $datePlan, $storeFilter = null, $productFilter = null): array
     {
+        $month = (int)date('m', strtotime($datePlan));
+
+        $stores = $this->getVisibleStores();
+        $storeIds = array_map(fn($s) => $s->id, $stores);
+        if ($storeFilter !== null) {
+            $storeIds = array_intersect($storeIds, (array)$storeFilter);
+        }
+
+        $query = (new Query())
+            ->select([
+                'store_id' => 'ex.entity_id',
+                'category' => 'p1c.category',
+                'subcategory' => 'p1c.subcategory',
+                'month' => new Expression('EXTRACT(MONTH FROM s.date)'),
+                'total_sum' => new Expression('SUM(sp.summ)')
+            ])
+            ->from(['sp' => 'sales_products'])
+            ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = sp.product_id')
+            ->leftJoin('sales s', 's.id = sp.check_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', 'p1c.category', 'p1c.subcategory', new Expression('EXTRACT(MONTH FROM s.date)')]);
+
+        if ($productFilter !== null) {
+            $query->andWhere(['sp.product_id' => $productFilter]);
+        }
+
+        $rows = $query->all();
+        $grouped = [];
+
+        foreach ($rows as $row) {
+            if ((int)$row['month'] !== $month) continue;
+            $groupKey = "{$row['store_id']}_{$row['category']}_{$row['month']}";
+            $grouped[$groupKey][] = $row;
+        }
+
         $result = [];
-        $stores = CityStore::findAll(['visible' => CityStore::IS_VISIBLE]);
-        $month = date('m', strtotime($datePlan));
-
-        foreach ($stores as $store) {
-            $storeId = $store->id;
-
-            $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')
-                ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c')
-                ->where(['>=', 's.date', $dateFrom])
-                ->andWhere(['ex.entity_id' => $storeId])
-                ->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')
-                ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c')
-                ->where(['>=', 's.date', $dateFrom])
-                ->andWhere(['ex.entity_id' => $storeId])
-                ->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',
-                    'store_id' => new Expression($storeId),
-                    'percent_of_month' => new Expression('ROUND(
-                            SUM(ss.total_sum) OVER (PARTITION BY ss.month, ss.category, ss.subcategory)
-                            / NULLIF(SUM(ss.total_sum) OVER (PARTITION BY ss.month, ss.category), 0),
-                            4
-                        )'
-                    ),
-                ])
-                ->from(['ss' => $subcategorySales])
-                ->innerJoin(['ms' => $monthlySales], 'ss.month = ms.month AND ss.year = ms.year')
-                ->where(['ss.month' => $month]);
-
-            $result = array_merge($result, $query->all());
+        foreach ($grouped as $group) {
+            $total = array_sum(array_column($group, 'total_sum')) ?: 1;
+            foreach ($group as $item) {
+                $result[] = [
+                    'store_id' => $item['store_id'],
+                    'category' => $item['category'],
+                    'subcategory' => $item['subcategory'],
+                    'percent_of_month' => round($item['total_sum'] / $total, 4),
+                ];
+            }
         }
 
         return $result;
     }
 
-
-    // шаг 4
-    public function getMonthSubcategoryGoal(array $categoryShare, array $sales)
+    public function getMonthSubcategoryGoal(array $subcategoryShare, array $categoryGoals): array
     {
-        $result = [];
+        $indexedGoals = [];
+        foreach ($categoryGoals as $goal) {
+            $indexedGoals[$goal['store_id']][$goal['category']] = $goal['goal'];
+        }
 
-        foreach ($categoryShare as $shareItem) {
-            foreach ($sales as $saleItem) {
-                if (
-                    $shareItem['category'] === $saleItem['category'] &&
-                    $shareItem['store_id'] == $saleItem['store_id']
-                ) {
-                    $percent = floatval(strtr($shareItem['percent_of_month'], [',' => '.', '(' => '', ')' => '']));
-                    $goal = $percent * floatval($saleItem['goal']); // исправлено тут
-
-                    $result[] = [
-                        'category' => $shareItem['category'],
-                        'subcategory' => $shareItem['subcategory'],
-                        'store_id' => $saleItem['store_id'],
-                        'goal' => $goal,
-                    ];
-                }
+        $result = [];
+        foreach ($subcategoryShare as $sub) {
+            $goal = $indexedGoals[$sub['store_id']][$sub['category']] ?? null;
+            if ($goal !== null) {
+                $result[] = [
+                    'category' => $sub['category'],
+                    'subcategory' => $sub['subcategory'],
+                    'store_id' => $sub['store_id'],
+                    'goal' => round($sub['percent_of_month'] * $goal, 2),
+                ];
             }
         }
 
         return $result;
     }
 
-
-    // шаг 5
-    public function getMonthSpeciesShare($datePlan)
+    public function getMonthSpeciesShare($dateFrom, $datePlan, $storeFilter = null, $productFilter = null): array
     {
+        $month = (int)date('m', strtotime($datePlan));
+
+        $stores = $this->getVisibleStores();
+        $storeIds = array_map(fn($s) => $s->id, $stores);
+        if ($storeFilter !== null) {
+            $storeIds = array_intersect($storeIds, (array)$storeFilter);
+        }
+
+        $query = (new Query())
+            ->select([
+                'store_id' => 'ex.entity_id',
+                'category' => 'p1.category',
+                'subcategory' => 'p1.subcategory',
+                'species' => 'p1.name',
+                'month' => new Expression('EXTRACT(MONTH FROM s.date)'),
+                'total_sum' => new Expression('SUM(sp.summ)')
+            ])
+            ->from(['sp' => 'sales_products'])
+            ->leftJoin('products_1c_nomenclature p1', 'p1.id = sp.product_id')
+            ->leftJoin('sales s', 's.id = sp.check_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', 'p1.category', 'p1.subcategory', 'p1.name', new Expression('EXTRACT(MONTH FROM s.date)')]);
+
+        if ($productFilter !== null) {
+            $query->andWhere(['sp.product_id' => $productFilter]);
+        }
+
+        $rows = $query->all();
+        $grouped = [];
+
+        foreach ($rows as $row) {
+            if ((int)$row['month'] !== $month) continue;
+            $key = "{$row['store_id']}_{$row['category']}_{$row['subcategory']}";
+            $grouped[$key][] = $row;
+        }
+
         $result = [];
-        $stores = CityStore::findAll(['visible' => CityStore::IS_VISIBLE]);
-        $month = date('m', strtotime($datePlan));
-
-        foreach ($stores as $store) {
-            $storeId = $store->id;
-
-            $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')
-                ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c')
-                ->where(['ex.entity_id' => $storeId])
-                ->andWhere(['EXTRACT(MONTH FROM s.date)' => $month])
-                ->groupBy(new Expression('EXTRACT(MONTH FROM s.date)'));
-
-            $speciesSales = (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')
-                ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c')
-                ->where(['ex.entity_id' => $storeId])
-                ->andWhere(['EXTRACT(MONTH FROM s.date)' => $month])
-                ->groupBy(['p1.category', 'p1.name', 'p1.subcategory', new Expression('EXTRACT(MONTH FROM s.date)')]);
-
-            $query = (new Query())
-                ->select([
-                    'ss.category',
-                    'ss.name',
-                    'ss.subcategory',
-                    'ss.month',
-                    'ss.total_sum',
-                    'store_id' => new Expression($storeId),
-                    'percent_of_month' => new Expression(
-                        'ROUND(ss.total_sum / SUM(ss.total_sum) OVER (PARTITION BY ss.month), 6)'
-                    ),
-                ])
-                ->from(['ss' => $speciesSales])
-                ->innerJoin(['ms' => $monthlySales], 'ss.month = ms.month');
-
-            $result = array_merge($result, $query->all());
+        foreach ($grouped as $group) {
+            $total = array_sum(array_column($group, 'total_sum')) ?: 1;
+            foreach ($group as $item) {
+                $result[] = [
+                    'store_id' => $item['store_id'],
+                    'category' => $item['category'],
+                    'subcategory' => $item['subcategory'],
+                    'species' => $item['species'],
+                    'percent_of_month' => round($item['total_sum'] / $total, 6),
+                    'goal' => null,
+                ];
+            }
         }
 
         return $result;
     }
 
-//количество = делить сумму на розничную цену в зависимости от магазина где он находится
-    // шаг 6
-    public function getMonthSpeciesGoalDirtry(array $monthSpeciesShare, array $monthSubcategoryGoal)
+    public function getMonthSpeciesGoalDirtry(array $speciesShare, array $subcategoryGoals): array
     {
-        $calculating = [];
-
-        foreach ($monthSpeciesShare as $species) {
-            foreach ($monthSubcategoryGoal as $goalItem) {
-                if (
-                    $species['category'] === $goalItem['category'] &&
-                    $species['subcategory'] === $goalItem['subcategory'] &&
-                    $species['store_id'] == $goalItem['store_id']
-                ) {
-                    $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,
-                    ];
-                }
+        $indexedGoals = [];
+        foreach ($subcategoryGoals as $goal) {
+            $indexedGoals[$goal['store_id']][$goal['category']][$goal['subcategory']] = $goal['goal'];
+        }
+
+        $result = [];
+        foreach ($speciesShare as $species) {
+            $goal = $indexedGoals[$species['store_id']][$species['category']][$species['subcategory']] ?? null;
+            if ($goal !== null) {
+                $result[] = [
+                    'category' => $species['category'],
+                    'subcategory' => $species['subcategory'],
+                    'product_name' => $species['species'],
+                    'store_id' => $species['store_id'],
+                    'goal' => round($species['percent_of_month'] * $goal, 2),
+                ];
             }
         }
 
-        return $calculating;
+        return $result;
     }
 }
diff --git a/erp24/views/auto-planogramma/sales.php b/erp24/views/auto-planogramma/sales.php
index 3c504913..67e46474 100644
--- a/erp24/views/auto-planogramma/sales.php
+++ b/erp24/views/auto-planogramma/sales.php
@@ -1,26 +1,118 @@
-<?php
-use yii\grid\GridView;
+<div class="filter-form" style="margin-bottom: 20px;">
+    <?php use kartik\date\DatePicker;
+    use kartik\select2\Select2;
+    use yii\grid\GridView;
+    use yii\helpers\ArrayHelper;
+    use yii\helpers\Html;
+    use yii\widgets\ActiveForm;
+    use yii_app\records\CityStore;
+    use yii_app\records\Products1cNomenclature;
 
-echo GridView::widget([
+    $form = ActiveForm::begin(['method' => 'get']); ?>
+    <div class="row">
+        <div class="col-md">
+            <?= Select2::widget([
+                'name' => 'category',
+                'value' => $filters['category'] ?? '',
+                'data' => ArrayHelper::map(
+                    Products1cNomenclature::find()->select('category')->distinct()->orderBy('category')->asArray()->all(),
+                    'category',
+                    'category'
+                ),
+                'options' => ['placeholder' => 'Категория'],
+                'pluginOptions' => ['allowClear' => true],
+            ]) ?>
+        </div>
+
+        <div class="col-md">
+            <?= Select2::widget([
+                'name' => 'subcategory',
+                'value' => $filters['subcategory'] ?? '',
+                'data' => ArrayHelper::map(
+                    Products1cNomenclature::find()->select('subcategory')->distinct()->orderBy('subcategory')->asArray()->all(),
+                    'subcategory',
+                    'subcategory'
+                ),
+                'options' => ['placeholder' => 'Подкатегория'],
+                'pluginOptions' => ['allowClear' => true],
+            ]) ?>
+        </div>
+        <div class="col-md">
+            <?= Select2::widget([
+                'name' => 'product_name',
+                'value' => $filters['product_name'] ?? '',
+                'data' => ArrayHelper::map(
+                    Products1cNomenclature::find()->select('name')->distinct()->orderBy('name')->asArray()->all(),
+                    'name',
+                    'name'
+                ),
+                'options' => ['placeholder' => 'Товар'],
+                'pluginOptions' => ['allowClear' => true],
+            ]) ?>
+        </div>
+
+
+        <div class="col-md">
+            <?= Select2::widget([
+                'name' => 'city_name',
+                'value' => $filters['city_name'] ?? '',
+                'data' => ArrayHelper::map(
+                    CityStore::find()->select(['name'])->distinct()->orderBy('name')->asArray()->all(),
+                    'name',
+                    'name'
+                ),
+                'options' => ['placeholder' => 'Магазин'],
+                'pluginOptions' => ['allowClear' => true],
+            ]) ?>
+        </div>
+
+        <div class="col-md">
+            <?= DatePicker::widget([
+                'name' => 'plan_date',
+                'value' => $filters['plan_date'] ?? '',
+                'pluginOptions' => [
+                    'autoclose' => true,
+                    'format' => 'dd-mm-yyyy',
+                ],
+                'options' => [
+                    'class' => 'form-control',
+                    'placeholder' => 'Плановая дата',
+                ],
+            ]) ?>
+        </div>
+
+        <div class="col-md">
+            <?= Select2::widget([
+                'name' => 'type',
+                'value' => $filters['type'] ?? '',
+                'data' => [
+                    'writeOffs' => 'Списания',
+                    'sales' => 'Продажи'
+                ],
+                'options' => ['placeholder' => 'Тип'],
+                'pluginOptions' => ['allowClear' => true],
+            ]) ?>
+        </div>
+
+
+        <div class="col-md">
+            <?= Html::submitButton('Фильтровать', ['class' => 'btn btn-primary']) ?>
+        </div>
+        <div class="col-md">
+            <?= Html::a('Сбросить', ['auto-planogramma/sales'], ['class' => 'btn btn-default']) ?>
+        </div>
+    </div>
+    <?php ActiveForm::end(); ?>
+</div>
+
+<?= GridView::widget([
     'dataProvider' => $dataProvider,
     'columns' => [
-        [
-            'attribute' => 'city_name',
-            'label' => 'Магазин',
-        ],
-        [
-            'attribute' => 'product_name',
-            'label' => 'Товар',
-        ],
         'category',
         'subcategory',
-        [
-            'attribute' => 'count',
-            'label' => 'Кол-во',
-        ],
-        [
-            'attribute' => 'sum',
-            'label' => 'Сумма',
-        ],
+        ['attribute' => 'product_name', 'label' => 'Товар'],
+        ['attribute' => 'city_name', 'label' => 'Магазин'],
+        ['attribute' => 'count', 'label' => 'Кол-во'],
+        ['attribute' => 'sum', 'label' => 'Сумма'],
     ],
-]);
\ No newline at end of file
+]) ?>
diff --git a/erp24/views/auto-planogramma/test-sales.php b/erp24/views/auto-planogramma/test-sales.php
index 8111b6f9..4139963b 100644
--- a/erp24/views/auto-planogramma/test-sales.php
+++ b/erp24/views/auto-planogramma/test-sales.php
@@ -1,18 +1,108 @@
 <?php
 
+use kartik\date\DatePicker;
+use kartik\select2\Select2;
+use yii\helpers\ArrayHelper;
+use yii\helpers\Html;
+use yii\widgets\ActiveForm;
 use yii\grid\GridView;
+use yii_app\records\CityStore;
+use yii_app\records\Products1cNomenclature;
 
-echo GridView::widget([
+/** @var array $filters */
+?>
+
+<div class="filter-form" style="margin-bottom: 20px;">
+    <?php $form = ActiveForm::begin(['method' => 'get']); ?>
+    <div class="row p-3">
+        <div class="col-md">
+            <?= $form->field(new \yii\base\DynamicModel(['category' => $filters['category'] ?? '']), 'category')->widget(Select2::class, [
+                'data' => ArrayHelper::map(
+                    Products1cNomenclature::find()->select('category')->distinct()->asArray()->all(),
+                    'category',
+                    'category'
+                ),
+                'options' => ['placeholder' => 'Категория', 'name' => 'category'],
+                'pluginOptions' => ['allowClear' => true],
+            ])->label('Категория') ?>
+        </div>
+        <div class="col-md">
+            <?= $form->field(new \yii\base\DynamicModel(['subcategory' => $filters['subcategory'] ?? '']), 'subcategory')->widget(Select2::class, [
+                'data' => ArrayHelper::map(
+                    Products1cNomenclature::find()->select('subcategory')->distinct()->asArray()->all(),
+                    'subcategory',
+                    'subcategory'
+                ),
+                'options' => ['placeholder' => 'Подкатегория', 'name' => 'subcategory'],
+                'pluginOptions' => ['allowClear' => true],
+            ])->label('Подкатегория') ?>
+        </div>
+        <div class="col-md">
+            <?= $form->field(new \yii\base\DynamicModel(['product_name' => $filters['product_name'] ?? '']), 'product_name')->widget(Select2::class, [
+                'data' => ArrayHelper::map(
+                    Products1cNomenclature::find()->select('name')->distinct()->asArray()->all(),
+                    'name',
+                    'name'
+                ),
+                'options' => ['placeholder' => 'Название товара', 'name' => 'product_name'],
+                'pluginOptions' => ['allowClear' => true],
+            ])->label('Товар') ?>
+        </div>
+        <div class="col-md">
+            <?= $form->field(new \yii\base\DynamicModel(['store_id' => $filters['store_id'] ?? '']), 'store_id')->widget(Select2::class, [
+                'data' => ArrayHelper::map(
+                    CityStore::findAll(['visible' => CityStore::IS_VISIBLE]),
+                    'id',
+                    'name'
+                ),
+                'options' => ['placeholder' => 'Магазин', 'name' => 'store_id'],
+                'pluginOptions' => ['allowClear' => true],
+            ])->label('Магазин') ?>
+        </div>
+        <div class="col-md">
+            <?= $form->field(new \yii\base\DynamicModel(['plan_date' => $filters['plan_date'] ?? '']), 'plan_date')->widget(DatePicker::class, [
+                'pluginOptions' => [
+                    'autoclose' => true,
+                    'format' => 'dd-mm-yyyy',
+                ],
+                'options' => [
+                    'class' => 'form-control',
+                    'placeholder' => 'Плановая дата',
+                    'name' => 'plan_date',
+                ],
+            ])->label('Плановая дата') ?>
+        </div>
+        <div class="col-md">
+            <?= $form->field(new \yii\base\DynamicModel(['type' => $filters['type'] ?? '']), 'type')->widget(Select2::class, [
+                'data' => [
+                    'writeOffs' => 'Списания',
+                    'sales' => 'Продажи'
+                ],
+                'options' => ['placeholder' => 'Тип', 'name' => 'type'],
+                'pluginOptions' => ['allowClear' => true],
+            ])->label('По дефолту продажи!') ?>
+        </div>
+        <div class="col-md">
+            <?= Html::submitButton('Фильтровать', ['class' => 'btn btn-primary']) ?>
+        </div>
+        <div class="col-md">
+            <?= Html::a('Сбросить', ['auto-planogramma/test-sales'], ['class' => 'btn btn-default']) ?>
+        </div>
+    </div>
+
+    <?php ActiveForm::end(); ?>
+</div>
+
+<?= GridView::widget([
     'dataProvider' => $dataProvider,
     'columns' => [
         'category',
         'subcategory',
-        'species',
+        'product_name',
         'store_id',
         [
             'attribute' => 'goal',
-//            'format' => ['decimal', 4],
-            'label' => 'План закупок(RUB)',
+            'label' => 'План закупок (RUB)',
         ],
     ],
 ]); ?>
-- 
2.39.5