From 33b7d5757f9a4fd2d393476f3313366eb63b34e5 Mon Sep 17 00:00:00 2001
From: fomichev <vladimir.fomichev@erp-flowers.ru>
Date: Wed, 30 Apr 2025 18:50:01 +0300
Subject: [PATCH] =?utf8?q?=D0=9F=D1=80=D0=BE=D0=B2=D0=B5=D1=80=D0=BA=D0=B0?=
 =?utf8?q?=20=D1=81=D0=BF=D0=B8=D1=81=D0=B0=D0=BD=D0=B8=D0=B9?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 .../AutoPlannogrammaController.php            | 142 ++++++++++++++++++
 erp24/services/AutoPlannogrammaService.php    | 117 ++++++++++++++-
 .../auto-plannogramma/control-species.php     |  98 ++++++++++++
 3 files changed, 356 insertions(+), 1 deletion(-)
 create mode 100644 erp24/views/auto-plannogramma/control-species.php

diff --git a/erp24/controllers/AutoPlannogrammaController.php b/erp24/controllers/AutoPlannogrammaController.php
index 3550bd6b..3dc54ee8 100644
--- a/erp24/controllers/AutoPlannogrammaController.php
+++ b/erp24/controllers/AutoPlannogrammaController.php
@@ -3,11 +3,14 @@
 namespace app\controllers;
 
 use Yii;
+use yii\base\DynamicModel;
 use yii\data\ArrayDataProvider;
 use yii\db\Expression;
 use yii\db\Query;
 use yii\helpers\ArrayHelper;
+use yii\web\Response;
 use yii_app\records\CityStore;
+use yii_app\records\Products1cNomenclature;
 use yii_app\services\AutoPlannogrammaService;
 
 class AutoPlannogrammaController extends BaseController
@@ -205,4 +208,143 @@ class AutoPlannogrammaController extends BaseController
             'filters'      => $filters,
         ]);
     }
+
+
+    public function actionControlSpecies()
+    {
+
+        $model = new DynamicModel([
+            'storeId','monthFrom','monthTo','type',
+            'category','subcategory','species'
+        ]);
+        $model->addRule(['monthFrom','monthTo','type'], 'required')
+            ->addRule('storeId', 'integer')
+            ->addRule(['category','subcategory','species'], 'string');
+
+        $storeList = CityStore::find()
+            ->select(['name','id'])
+            ->indexBy('id')
+            ->column();
+
+        $monthsList = [];
+        for ($i = 0; $i < 12; $i++) {
+            $m = (new \DateTime("first day of -{$i} month"))->format('Y-m');
+            $monthsList[$m] = (new \DateTime("first day of -{$i} month"))
+                ->format('F Y');
+        }
+
+
+        $categoryList = Products1cNomenclature::find()
+            ->select('category')
+            ->distinct()
+            ->indexBy('category')
+            ->column();
+
+        $subcategoryList = [];
+        if ($model->category) {
+            $subcategoryList = Products1cNomenclature::find()
+                ->where(['category' => $model->category])
+                ->select('subcategory')
+                ->distinct()
+                ->indexBy('subcategory')
+                ->column();
+        }
+
+        $speciesList = [];
+        if ($model->category && $model->subcategory) {
+            $speciesList = Products1cNomenclature::find()
+                ->where([
+                    'category'    => $model->category,
+                    'subcategory' => $model->subcategory
+                ])
+                ->select('species')
+                ->distinct()
+                ->indexBy('species')
+                ->column();
+        }
+
+        $result = [];
+
+        if ($model->load(Yii::$app->request->post()) && $model->validate()) {
+            // формируем фильтры
+            $filters = [];
+            if ($model->storeId)    $filters['store_id']   = $model->storeId;
+            if ($model->category)   $filters['category']   = $model->category;
+            if ($model->subcategory)$filters['subcategory']= $model->subcategory;
+            if ($model->species)    $filters['species']    = $model->species;
+            //var_dump($model->type); die();
+            $dateFrom = $model->monthFrom . '-01';
+            $dateTo   = date('Y-m-t', strtotime($dateFrom));
+            $service =  new AutoPlannogrammaService();
+            $totals =  $service->getStoreTotals([1,2,4], '2025-03-01', null, 'writeOffs');
+           // var_dump($totals);die();
+            $result = $service
+                ->getMonthSpeciesShareOrWriteOffDate(
+                    '2025-03-01',
+                    '2025-03-31',
+                    null,
+                    null,               // productFilter
+                    'writeOffs'
+                );
+        }
+
+        return $this->render('control-species', [
+            'model'           => $model,
+            'result'          => $result,
+            'storeList'       => $storeList,
+            'monthsList'      => $monthsList,
+            'categoryList'    => $categoryList,
+            'subcategoryList' => $subcategoryList,
+            'speciesList'     => $speciesList,
+        ]);
+    }
+    /**
+     * Возвращает список подкатегорий для выбранной категории.
+     * @param string $category
+     * @return array JSON-ответ со списком подкатегорий
+     */
+    public function actionGetSubcategories($category)
+    {
+        Yii::$app->response->format = Response::FORMAT_JSON;
+        $subcategories = Products1cNomenclature::find()
+            ->select('subcategory')
+            ->distinct()
+            ->where(['category' => $category])
+            ->orderBy('subcategory')
+            ->asArray()
+            ->all();
+
+        $out = [];
+        foreach ($subcategories as $item) {
+            if (!empty($item['subcategory'])) {
+                $out[] = ['id' => $item['subcategory'], 'name' => $item['subcategory']];
+            }
+        }
+        return $out;
+    }
+
+    /**
+     * Возвращает список видов для выбранной подкатегории.
+     * @param string $subcategory
+     * @return array JSON-ответ со списком видов
+     */
+    public function actionGetSpecies($subcategory)
+    {
+        \Yii::$app->response->format = Response::FORMAT_JSON;
+        $species = Products1cNomenclature::find()
+            ->select('species')
+            ->distinct()
+            ->where(['subcategory' => $subcategory])
+            ->orderBy('species')
+            ->asArray()
+            ->all();
+
+        $out = [];
+        foreach ($species as $item) {
+            if (!empty($item['species'])) {
+                $out[] = ['id' => $item['species'], 'name' => $item['species']];
+            }
+        }
+        return $out;
+    }
 }
diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php
index 87b29e6c..4f89144a 100644
--- a/erp24/services/AutoPlannogrammaService.php
+++ b/erp24/services/AutoPlannogrammaService.php
@@ -30,7 +30,10 @@ class AutoPlannogrammaService
 
         if ($type === 'writeOffs') {
             $query->leftJoin('export_import_table ex', 'ex.export_val = w.store_id')
-                ->join('JOIN', new Expression('LATERAL jsonb_array_elements(w.items::jsonb) AS item'), 'true');
+                ->leftJoin(
+                    'LATERAL jsonb_array_elements(w.items::jsonb) AS item',
+                    'TRUE'
+                );
         } else {
             $query->leftJoin('sales_products sp', 'sp.check_id = w.id')
                 ->leftJoin('export_import_table ex', 'ex.export_val = w.store_id_1c');
@@ -380,4 +383,116 @@ class AutoPlannogrammaService
 
         return array_values($filtered);
     }
+
+    /**
+     * Считает для каждого магазина месячную сумму и долю
+     * продаж или списаний по видам (species).
+     *
+     * @param string      $dateFrom      Дата начала периода (Y-m-d)
+     * @param array|null  $filters       Фильтры (например ['store_id'=>...])
+     * @param array|null  $productFilter Опциональный фильтр по product_id
+     * @param string      $type          'sales' или 'writeOffs'
+     * @return array       [
+     *   [
+     *     'store_id'        => (int),
+     *     'category'        => (string),
+     *     'subcategory'     => (string),
+     *     'species'         => (string),  // теперь — именно вид
+     *     'total_sum'       => (float),
+     *     'percent_of_month'=> (float),  // доля от общего объёма магазина
+     *   ], …
+     * ]
+     */
+    public function getMonthSpeciesShareOrWriteOffDate(
+        string $dateFrom,
+        string $dateTo,
+        ?array $filters = null,
+        ?array $productFilter = null,
+        string $type = 'sales'
+    ): array
+    {
+        $stores  = $this->getVisibleStores();
+        $storeIds = array_map(fn($s) => $s->id, $stores);
+        if (!empty($filters['store_id'])) {
+            $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]);
+        }
+
+        $totals = $this->getStoreTotals($storeIds, '2025-03-01', null, 'writeOffs', '2025-03-31');
+        if (empty($totals)) {
+            return [];
+        }
+
+        $query = (new Query())->select([
+            'store_id'    => 'ex.entity_id',
+            'category'    => 'p1c.category',
+            'subcategory' => 'p1c.subcategory',
+            // Суммируем сразу по виду из колонки p1c.species
+            'species'     => 'p1c.species',
+            'total_sum'   => new Expression(
+                $type === 'writeOffs'
+                    ? "SUM(CAST(item->>'summ' AS NUMERIC))"
+                    : 'SUM(sp.summ)'
+            ),
+        ]);
+
+        if ($type === 'writeOffs') {
+            $query->from(['w' => 'write_offs'])
+                ->leftJoin('export_import_table ex', 'ex.export_val = w.store_id')
+                ->leftJoin(
+                    'LATERAL jsonb_array_elements(w.items::jsonb) AS item',
+                    'TRUE'
+                )
+                ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = item->>\'product_id\'')
+                ->where(['>=', 'w.date', $dateFrom])
+                ->andWhere(['<=', 'w.date', $dateTo])
+                ->andWhere(['ex.entity_id' => $storeIds])
+                ->andWhere(['<>', 'p1c.species', ''])
+                ->groupBy([
+                    'ex.entity_id',
+                    'p1c.category',
+                    'p1c.subcategory',
+                    'p1c.species',
+                ]);
+            if ($productFilter !== null) {
+                $query->andWhere(['item->>\'product_id\'' => $productFilter]);
+            }
+        } else {
+            $query->from(['s' => 'sales'])
+                ->leftJoin('sales_products sp', 'sp.check_id = s.id')
+                ->leftJoin('export_import_table ex', 'ex.export_val = s.store_id_1c')
+                ->leftJoin('products_1c_nomenclature p1c', 'p1c.id = sp.product_id')
+                ->where(['>=', 's.date', $dateFrom])
+                ->andWhere(['<=', 's.date', $dateTo])
+                ->andWhere(['ex.entity_id' => $storeIds])
+                ->andWhere(['<>', 'p1c.species', ''])
+                ->groupBy([
+                    'ex.entity_id',
+                    'p1c.category',
+                    'p1c.subcategory',
+                    'p1c.species',
+                ]);
+            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[] = [
+                'store_id'        => $storeId,
+                'category'        => $row['category'],
+                'subcategory'     => $row['subcategory'],
+                'species'         => $row['species'],
+                'total_sum'       => (float)$row['total_sum'],
+                'percent_of_month'=> round((float)$row['total_sum'] / $total, 4),
+            ];
+        }
+
+        return $result;
+    }
+
 }
diff --git a/erp24/views/auto-plannogramma/control-species.php b/erp24/views/auto-plannogramma/control-species.php
new file mode 100644
index 00000000..f331340b
--- /dev/null
+++ b/erp24/views/auto-plannogramma/control-species.php
@@ -0,0 +1,98 @@
+<?php
+
+use yii\helpers\Html;
+use yii\widgets\ActiveForm;
+use yii\base\DynamicModel;
+use yii_app\records\CityStore;
+use yii_app\records\PricesDynamic;
+use yii_app\records\Products1c;
+
+/* @var $this yii\web\View */
+/* @var $result array|null */
+/* @var $model DynamicModel */
+/* @var $storeList array */
+/* @var $monthsList array */
+/* @var $categoryList array */
+/* @var $subcategoryList array */
+/* @var $speciesList array */
+
+?>
+
+<div class="show-history-data p-4">
+    <h1>Проверка данных по видам</h1>
+
+    <?php $form = ActiveForm::begin([
+    'method' => 'post',
+    ]); ?>
+
+    <?= $form->field($model, 'storeId')
+    ->dropDownList($storeList, ['prompt' => 'Выберите магазин'])
+    ->label('Магазин') ?>
+
+    <?= $form->field($model, 'monthFrom')
+    ->dropDownList($monthsList, ['prompt' => 'Начало периода'])
+    ->label('Дата с') ?>
+
+    <?= $form->field($model, 'monthTo')
+    ->dropDownList($monthsList, ['prompt' => 'Конец периода'])
+    ->label('Дата по') ?>
+
+    <?= $form->field($model, 'type')
+    ->radioList([
+    'sales'    => 'Продажи',
+    'writeOffs'=> 'Списания',
+    ])
+    ->label('Тип данных') ?>
+
+    <?= $form->field($model, 'category')
+    ->dropDownList($categoryList, ['prompt' => 'Выберите категорию'])
+    ->label('Категория') ?>
+
+    <?= $form->field($model, 'subcategory')
+    ->dropDownList($subcategoryList, ['prompt' => 'Выберите подкатегорию'])
+    ->label('Подкатегория') ?>
+
+    <?= $form->field($model, 'species')
+    ->dropDownList($speciesList, ['prompt' => 'Выберите вид товара'])
+    ->label('Вид товара') ?>
+
+    <div class="form-group">
+        <?= Html::submitButton('Отправить', ['class' => 'btn btn-primary']) ?>
+    </div>
+
+    <?php ActiveForm::end(); ?>
+
+    <?php if (!empty($result)): ?>
+    <h2>Результаты</h2>
+    <table class="table table-striped table-bordered">
+        <thead>
+        <tr>
+            <th>Магазин</th>
+            <th>Категория</th>
+            <th>Подкатегория</th>
+            <th>Вид товара</th>
+            <th>Сумма</th>
+            <th>Доля</th>
+        </tr>
+        </thead>
+        <tbody>
+        <?php foreach ($result as $row): ?>
+        <tr>
+            <td><?= Html::encode($storeList[$row['store_id']] ?? $row['store_id']) ?></td>
+            <td><?= Html::encode($row['category']) ?></td>
+            <td><?= Html::encode($row['subcategory']) ?></td>
+            <td><?= Html::encode($row['species']) ?></td>
+            <td><?= Yii::$app->formatter->asDecimal($row['total_sum'], 2) ?></td>
+            <td><?= Yii::$app->formatter->asPercent($row['percent_of_month'], 2) ?></td>
+        </tr>
+        <?php endforeach; ?>
+        </tbody>
+    </table>
+    <?php endif; ?>
+
+    <?php
+    $this->registerJsFile('/js/category-plan/show-history-data.js', [
+    'position' => \yii\web\View::POS_END
+    ]);
+    ?>
+</div>
-- 
2.39.5