From fd2dfa11ed6bc227ef6b5823ae5d5b2d26d6273c Mon Sep 17 00:00:00 2001
From: Alexander Smirnov <fredeom@mail.ru>
Date: Tue, 21 Jan 2025 14:09:12 +0300
Subject: [PATCH] =?utf8?q?[ERP-283]=20=D0=9A=D0=B0=D1=82=D0=B5=D0=B3=D0=BE?=
 =?utf8?q?=D1=80=D0=B8=20=D0=BF=D0=BB=D0=B0=D0=BD?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 erp24/controllers/CategoryPlanController.php  | 129 ++++++++++++++++++
 ...0117_130954_create_table_category_plan.php |  40 ++++++
 erp24/views/category-plan/index.php           |  92 +++++++++++++
 erp24/web/js/category-plan/index.js           |  14 ++
 4 files changed, 275 insertions(+)
 create mode 100644 erp24/controllers/CategoryPlanController.php
 create mode 100755 erp24/migrations/m250117_130954_create_table_category_plan.php
 create mode 100644 erp24/views/category-plan/index.php
 create mode 100644 erp24/web/js/category-plan/index.js

diff --git a/erp24/controllers/CategoryPlanController.php b/erp24/controllers/CategoryPlanController.php
new file mode 100644
index 00000000..80fdfaaf
--- /dev/null
+++ b/erp24/controllers/CategoryPlanController.php
@@ -0,0 +1,129 @@
+<?php
+
+namespace app\controllers;
+
+use Yii;
+use yii\base\DynamicModel;
+use yii\helpers\ArrayHelper;
+use yii\web\Controller;
+use yii_app\records\CityStore;
+use yii_app\records\ExportImportTable;
+use yii_app\records\Sales;
+use yii_app\records\WriteOffs;
+use yii_app\records\WriteOffsErp;
+
+class CategoryPlanController extends Controller {
+    public function actionIndex()
+    {
+        $model = DynamicModel::validateData([
+            'year' => date('Y'),
+            'month' => date('m'),
+            'store_id' => null,
+        ], [
+            [['year', 'month', 'store_id'], 'safe']
+        ]);
+
+        $model->load(Yii::$app->request->get());
+
+        /////////////////////////// CHAT GPT STUFF ///////////////////////////////////
+
+        $currentDate = new \DateTime();
+
+        $startDate = (clone $currentDate)->modify('-4 months')->modify('first day of this month');
+
+        $endDate = (clone $startDate)->modify('+2 months')->modify('last day of this month');
+
+        $date_start = $startDate->format('Y-m-d');
+        $date_end = $endDate->format('Y-m-d');
+
+        $sales = Sales::find()->alias('s')->select([
+            "COUNT(*) as cnt",
+            "SUM(CASE WHEN operation='Продажа' THEN s.summ ELSE (CASE WHEN operation='Возврат' THEN -s.summ ELSE 0 END) END) as total",
+            "s.store_id",
+            "p1c.type as type",
+            "TO_CHAR(s.date, 'YYYY-MM') as month"
+        ])
+            ->leftJoin('sales_products sp', 's.id = sp.check_id')
+            ->leftJoin('products_1c p1c', 'p1c.id = sp.product_id')
+            ->where(['between', 's.date', $date_start, $date_end])
+            ->andWhere(['order_id' => ['', '0']])
+            ->andWhere(['s.store_id' => $model->store_id])
+            ->groupBy(['s.store_id', "TO_CHAR(s.date, 'YYYY-MM')", "type"])
+            ->orderBy(['month' => SORT_ASC, 'type' => SORT_ASC])
+            ->asArray()
+            ->all();
+
+        $types = [];
+        $mnths = [];
+        foreach ($sales as $sale) {
+            $mnths[$sale['month']] = 1;
+            $types[$sale['type']] = 1;
+        }
+        $mnths = array_keys($mnths);
+        sort($mnths);
+        $weights = [];
+        foreach ($mnths as $ind => $month) {
+            $weights[$month] = $ind + 1;
+        }
+        $table = [];
+        foreach ($sales as $sale) {
+            $table[$sale['store_id']][$sale['type']] = ($table[$sale['store_id']][$sale['type']] ?? 0) + $weights[$sale['month']] * $sale['total'] / 6;
+        }
+        /////////////////////////////////////////////////////////////////////////////////////
+        $salesOnline = Sales::find()->alias('s')->select([
+            "COUNT(*) as cnt",
+            "SUM(CASE WHEN operation='Продажа' THEN s.summ ELSE (CASE WHEN operation='Возврат' THEN -s.summ ELSE 0 END) END) as total",
+            "s.store_id",
+            "p1c.type as type",
+            "TO_CHAR(s.date, 'YYYY-MM') as month"
+        ])
+            ->leftJoin('sales_products sp', 's.id = sp.check_id')
+            ->leftJoin('products_1c p1c', 'p1c.id = sp.product_id')
+            ->where(['between', 's.date', $date_start, $date_end])
+            ->andWhere(['not in', 'order_id', ['', '0']])
+            ->andWhere(['s.store_id' => $model->store_id])
+            ->groupBy(['s.store_id', "TO_CHAR(s.date, 'YYYY-MM')", "type"])
+            ->orderBy(['month' => SORT_ASC, 'type' => SORT_ASC])
+            ->asArray()
+            ->all();
+
+        $tableOnline = [];
+        foreach ($salesOnline as $sale) {
+            $types[$sale['type']] = 1;
+            $tableOnline[$sale['store_id']][$sale['type']] = ($tableOnline[$sale['store_id']][$sale['type']] ?? 0) + $weights[$sale['month']] * $sale['total'] / 6;
+        }
+        /////////////////////////////////////////////////////////////////////////////////////
+        $eit = ExportImportTable::find()->where(['entity' => 'city_store', 'export_id' => 1, 'entity_id' => $model->store_id])->one();
+        $store_id = $eit->export_val;
+        $writeOffs = WriteOffs::find()->alias('wo')->select([
+            'sum(wo.summ) as total',
+            "p1c.type as p1ctype",
+            "TO_CHAR(wo.date, 'YYYY-MM') as month"
+        ])
+            ->leftJoin('write_offs_products wop', 'wop.write_offs_id = wo.id')
+            ->leftJoin('products_1c p1c', 'p1c.id = wop.product_id')
+            ->where(['between', 'wo.date', $date_start, $date_end])
+            ->andWhere(['wo.type' => WriteOffsErp::WRITE_OFFS_TYPE_BRAK])
+            ->andWhere(['wo.store_id' => $store_id])
+            ->groupBy(["month", 'p1ctype'])
+            ->asArray()->all();
+
+        $tableWriteOffs = [];
+        foreach ($writeOffs as $writeoff) {
+            $types[$writeoff['p1ctype']] = 1;
+            $tableWriteOffs[$writeoff['p1ctype']] = ($tableWriteOffs[$writeoff['p1ctype']] ?? 0) + $weights[$writeoff['month']] * $writeoff['total'] / 6;
+        }
+        $types = array_keys($types);
+        /////////////////////////////////////////////////////////////////////////////////////
+
+        $years = [];
+        for ($i = 3; $i >= 0; $i--) {
+            $year = date("Y") - $i;
+            $years [$year] = $year;
+        }
+        $stores = ArrayHelper::map(CityStore::find()->andWhere(['visible' => '1'])->all(), 'id', 'name');
+
+        return $this->render('index', compact('model', 'years', 'stores', 'table', 'tableOnline',
+            'tableWriteOffs', 'types'));
+    }
+}
diff --git a/erp24/migrations/m250117_130954_create_table_category_plan.php b/erp24/migrations/m250117_130954_create_table_category_plan.php
new file mode 100755
index 00000000..7dc96ef9
--- /dev/null
+++ b/erp24/migrations/m250117_130954_create_table_category_plan.php
@@ -0,0 +1,40 @@
+<?php
+
+use yii\db\Migration;
+
+/**
+ * Class m250117_130954_create_table_category_plan
+ */
+class m250117_130954_create_table_category_plan extends Migration
+{
+    const TABLE_NAME = 'erp24.category_plan';
+    /**
+     * {@inheritdoc}
+     */
+    public function safeUp()
+    {
+        $this->createTable(self::TABLE_NAME, [
+            'id' => $this->primaryKey(),
+            'year' => $this->integer()->notNull()->comment('Год создания отчёта'),
+            'month' => $this->integer()->notNull()->comment('Месяц создания отчёта'),
+            'store_id' => $this->integer()->notNull()->comment('id магазина в ERP'),
+            'category' => $this->string(100)->notNull()->comment('Название категории: срезка, горшечные, сопутствующие товары'),
+            'offline' => $this->float()->null()->comment('Оффлайн план процент'),
+            'internet_shop' => $this->float()->null()->comment('Интернет-магазин план процент'),
+            'marketplace' => $this->float()->null()->comment('Маркетплейс план процент'),
+            'write_offs' => $this->float()->null()->comment('Списания план процент'),
+            'created_at' => $this->dateTime()->notNull()->comment('Дата создания'),
+            'updated_at' => $this->dateTime()->notNull()->comment('Дата обновления'),
+            'created_by' => $this->integer()->notNull()->comment('ID создателя записи'),
+            'updated_by' => $this->integer()->notNull()->comment('ID обновителя записи'),
+        ]);
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function safeDown()
+    {
+        $this->dropTable(self::TABLE_NAME);
+    }
+}
diff --git a/erp24/views/category-plan/index.php b/erp24/views/category-plan/index.php
new file mode 100644
index 00000000..3df10e13
--- /dev/null
+++ b/erp24/views/category-plan/index.php
@@ -0,0 +1,92 @@
+<?php
+
+use yii\base\DynamicModel;
+use yii\widgets\ActiveForm;
+
+use kartik\select2\Select2;
+use yii_app\helpers\HtmlHelper;
+
+
+/* @var $model DynamicModel */
+/* @var $years array */
+/* @var $stores array */
+/* @var $table array */
+/* @var $tableOnline array */
+/* @var $types array */
+/* @var $tableWriteOffs array */
+
+$this->registerJsFile('/js/category-plan/index.js', ['position' => \yii\web\View::POS_END]);
+
+?>
+
+<div class="categoryPlanIndex m-5">
+
+    <?php $form = ActiveForm::begin([
+        'id' => 'filter-form',
+        'method' => 'GET',
+        'action' => '/category-plan'
+    ]) ?>
+
+    <div class="row">
+        <div class="col-2">
+            <?= $form->field($model, 'year')->dropDownList($years, ['onchange' => 'this.form.submit();'])->label(false) ?>
+        </div>
+    </div>
+    <div class="row">
+        <div class="col-2">
+            <?= $form->field($model, 'month')->dropDownList(HtmlHelper::getMonthNames(), ['onchange' => 'this.form.submit();'])->label(false) ?>
+        </div>
+    </div>
+
+    <div class="row">
+        <div class="col-2">
+            <?= $form->field($model, 'store_id')->widget(Select2::class, [
+                'data' => $stores,
+                'language' => 'ru',
+                'options' => ['placeholder' => 'Магазин...'],
+                'pluginOptions' => [
+                    'allowClear' => true
+                ],
+                'pluginEvents' => [
+                    'change' => 'function(e) {
+                        $("#filter-form").get(0).submit();
+                    }'
+                ]
+            ])->label(false) ?>
+        </div>
+    </div>
+
+    <?php ActiveForm::end() ?>
+
+    <?php if (isset($model->store_id)): ?>
+        <div class="table-responsive" style="max-width: 1285px;">
+            <table id="categoryPlan">
+                <thead>
+                    <tr><th rowspan="4" class="text-center align-middle border">Категории</th><th colspan="6" class="text-center border">План продаж</th><th colspan="2" class="text-center border">Списания</th></tr>
+                    <tr><th colspan="2" class="text-center border">Оффлайн</th><th colspan="2" class="text-center border">Интернет-Магазин</th><th colspan="2" class="text-center border">Маркетплейс</th><th colspan="2" class="text-center border">Списания</th></tr>
+                    <tr><th colspan="2" class="text-center border">1 100 000</th><th colspan="2" class="text-center border">800 000</th><th colspan="2" class="text-center border">0</th><th colspan="2" class="text-center border">300 000</th></tr>
+                    <tr><th class="text-center border">%</th><th class="text-center border">Сумма</th><th class="text-center border">%</th><th class="text-center border">Cумма</th><th class="text-center border">%</th><th class="text-center border">Cумма</th><th class="text-center border">%</th><th class="text-center border">Cумма</th></tr>
+                </thead>
+                <tbody>
+                    <?php foreach ($types as $type): ?>
+                        <?php $data = $table[$model->store_id][$type] ?? 0; ?>
+                        <tr>
+                            <th><?= $type ?></th>
+                            <td><?= number_format($data / 110000 * 100, 0, '.', ' ') ?>%</td>
+                            <td><?= number_format($data, 0, '.', ' ') ?></td>
+                            <?php $data2 = $tableOnline[$model->store_id][$type] ?? 0; ?>
+                            <td><?= number_format($data2 / 800000 * 100, 0, '.', ' ') ?>%</td>
+                            <td><?= number_format($data2, 0, '.', ' ') ?></td>
+                            <td></td>
+                            <td></td>
+                            <?php $data4 = $tableWriteOffs[$type] ?? 0; ?>
+                            <td><?= number_format($data4 / 300000 * 100, 0, '.', ' ') ?>%</td>
+                            <td><?= number_format($data4, 0, '.', ' ') ?></td>
+                        </tr>
+                    <?php endforeach; ?>
+                </tbody>
+            </table>
+        </div>
+    <?php endif; ?>
+
+</div>
diff --git a/erp24/web/js/category-plan/index.js b/erp24/web/js/category-plan/index.js
new file mode 100644
index 00000000..6b5a4858
--- /dev/null
+++ b/erp24/web/js/category-plan/index.js
@@ -0,0 +1,14 @@
+/* jshint esversion: 6 */
+
+const param26 = $('meta[name=csrf-param]').attr('content');
+const token26 = $('meta[name=csrf-token]').attr('content');
+
+$(document).ready(() => {
+    $('#categoryPlan').DataTable({
+        sorting: false,
+        info: false,
+        paging: false,
+        searching: true,
+        language: data_table_language
+    });
+});
-- 
2.39.5