From 0b9079793844f8bbb8067c851d9b62584a55ed6d Mon Sep 17 00:00:00 2001
From: fomichev <vladimir.fomichev@erp-flowers.ru>
Date: Tue, 3 Jun 2025 18:13:53 +0300
Subject: [PATCH] =?utf8?q?=D0=AD=D0=BA=D1=88=D0=BD=20=D0=B2=D1=8B=D0=B3?=
 =?utf8?q?=D1=80=D1=83=D0=B7=D0=BA=D0=B8?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 .../AutoPlannogrammaController.php            | 208 ++++++++++++++++++
 .../week-sales-species-excel.php              | 111 ++++++++++
 2 files changed, 319 insertions(+)
 create mode 100644 erp24/views/auto-plannogramma/week-sales-species-excel.php

diff --git a/erp24/controllers/AutoPlannogrammaController.php b/erp24/controllers/AutoPlannogrammaController.php
index dba2f6da..d6dad295 100644
--- a/erp24/controllers/AutoPlannogrammaController.php
+++ b/erp24/controllers/AutoPlannogrammaController.php
@@ -2,6 +2,8 @@
 
 namespace app\controllers;
 
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 use Yii;
 use yii\data\ArrayDataProvider;
 use yii\db\Expression;
@@ -1392,4 +1394,210 @@ class AutoPlannogrammaController extends BaseController
     }
 
 
+    public function actionWeekSalesSpeciesExcel()
+    {
+        $request = Yii::$app->request;
+
+        $filters = [
+            'category' => $request->get('category'),
+            'subcategory' => $request->get('subcategory') ?? null,
+            'species' => $request->get('species') ?? null,
+            'store_id' => $request->get('store_id') ?? [],
+            'year' => $request->get('year'),
+            'month' => $request->get('month'),
+            'type' => $request->get('type'),
+        ];
+
+        $dataProvider = new ArrayDataProvider([
+            'allModels' => [],
+            'pagination' => ['pageSize' => 100],
+        ]);
+
+
+        return $this->render('week-sales-species-excel', [
+            'dataProvider' => $dataProvider,
+            'filters' => $filters,
+        ]);
+    }
+
+
+    public function actionExportExcel()
+    {
+        $rows = [];
+       /* $rows[] = [
+        'week' => '1',
+        'type_pm' => 'max',
+        'shop' => 'Ванеева',
+       'category' => 'Срезка',
+        'subcategory' => 'Розы',
+        'species' => 'Роза',
+        'product_name' => 'Роза Эквадор',
+        'quantity'  => '3',
+        'value_type' => 'offline',
+        'group_name' => 'Оффлайн',
+        ];*/
+        $request = Yii::$app->request;
+
+        $filters = [
+            'category' => $request->get('category'),
+            'subcategory' => $request->get('subcategory') ?? null,
+            'species' => $request->get('species') ?? null,
+            'store_id' => $request->get('store_id') ?? [],
+            'year' => $request->get('year'),
+            'month' => $request->get('month'),
+            'type' => $request->get('type'),
+        ];
+
+
+
+        $bouquetSpeciesForecast = [];
+
+
+            $filters['plan_date'] = $filters['year'] . '-' . str_pad($filters['month'], 2, '0', STR_PAD_LEFT) . '-01';
+            // var_dump( $filters['plan_date']); die();
+            $service = new AutoPlannogrammaService();
+
+            $monthCategoryShare = $service->getMonthCategoryShareOrWriteOff($filters['plan_date'], $filters);
+            $monthCategoryGoal = $service->getMonthCategoryGoal($monthCategoryShare, $filters['plan_date']);
+            $monthSubcategoryShare = $service->getMonthSubcategoryShareOrWriteOff($filters['plan_date'], $filters);
+            $monthSubcategoryGoal = $service->getMonthSubcategoryGoal($monthSubcategoryShare, $monthCategoryGoal);
+            $monthSpeciesShare = $service->getMonthSpeciesShareOrWriteOff($filters['plan_date'], $filters);
+            $goals = $service->getMonthSpeciesGoalDirty($monthSpeciesShare, $monthSubcategoryGoal);
+
+            if ($filters['type'] == AutoPlannogrammaService::TYPE_WRITE_OFFS) {
+                $monthCategoryWriteOffsShare = $service->getMonthCategoryShareOrWriteOff($filters['plan_date'], $filters, $filters['type']);
+                $monthCategoryWriteOffsGoal = $service->getMonthCategoryGoal($monthCategoryWriteOffsShare, $filters['plan_date'], $filters['type']);
+                $monthSubcategoryWriteOffsShare = $service->getMonthSubcategoryShareOrWriteOff($filters['plan_date'], $filters, $filters['type']);
+                $monthSubcategoryWriteOffsGoals = $service->getMonthSubcategoryGoal($monthSubcategoryWriteOffsShare, $monthCategoryWriteOffsGoal, $filters['type']);
+                $monthSpeciesWriteOffShare = $service->getMonthSpeciesShareOrWriteOff($filters['plan_date'], $filters, $filters['type']);
+                $goals = $service->getMonthSpeciesGoalDirty($monthSpeciesWriteOffShare, $monthSubcategoryWriteOffsGoals, $filters['type'], $data);
+            }
+
+
+            $result = StorePlanService::calculateHistoricalShare(
+                $filters['store_id'],
+                $filters['month'],
+                $filters['year'],
+                $filters['category'],
+                $filters['subcategory'],
+                $filters['species']
+            );
+
+            $noHistoryProductData = $service->calculateSpeciesForecastForProductsWithoutHistory($filters['plan_date'], $filters);
+
+            $productSalesShare = StorePlanService::calculateProductSalesShareProductsWithHistory(
+                $filters['store_id'],
+                $filters['month'],
+                $result['with_history']
+            );
+
+            $productSalesForecast = $service->calculateProductForecastInPiecesProductsWithHistory(
+                $filters['store_id'],
+                $filters['month'],
+                $productSalesShare,
+                $goals,
+                $filters['subcategory'],
+                $filters['category'],
+                $filters['species']
+            );
+
+            $matrixForecast = MatrixBouquetForecast::find()
+                ->where(['year' => $filters['year'], 'month' => $filters['month']])
+                ->asArray()
+                ->all();
+            $matrixGroups = array_unique(ArrayHelper::getColumn($matrixForecast, 'group'));
+            $bouquetForecast = StorePlanService::getBouquetSpiecesMonthGoalFromForecast($filters['month'], $filters['year'], $filters['store_id'], $matrixGroups);
+            $speciesData = $bouquetForecast['final'];
+            foreach ($speciesData as $store_id  => $categoryData) {
+                foreach ($categoryData as $category => $subcategoryData) {
+                    foreach ($subcategoryData as $subcategory => $species) {
+                        foreach ($species as $speciesInd => $row) {
+                            $bouquetSpeciesForecast[] = [
+                                'category' => $category,
+                                'subcategory' => $subcategory,
+                                'store_id' => $store_id,
+                                'species' => $speciesInd,
+                                'goal' => $row
+                            ];
+                        }
+                    }
+                }
+
+            }
+            $cleanedSpeciesGoals = $service->subtractSpeciesGoals($goals, $bouquetSpeciesForecast, $noHistoryProductData);
+
+
+            $salesProductForecastShare = $service->calculateProductForecastShare($noHistoryProductData, $productSalesForecast);
+
+            $productForecastSpecies = $service->calculateProductSalesBySpecies($salesProductForecastShare, $cleanedSpeciesGoals);
+
+
+            $weeklySales = $service->getHistoricalSpeciesShareByWeek($filters['plan_date'], $filters);
+
+            $weeklySalesForecast = $service->calculateWeeklyProductForecastPieces($productForecastSpecies, $weeklySales);
+
+
+            foreach ($weeklySalesForecast as $fc ) {
+                $rows[] = [
+                    'week' => $fc['week'],
+                    'type_pm' => 'max',
+                    'shop' => $fc['store_id'],
+                    'category' => $fc['category'],
+                    'subcategory' => $fc['subcategory'],
+                    'species' => $fc['species'],
+                    'product_name' => $fc['product_id'],
+                    'quantity'  => $fc['forecast_week_pieces'],
+                    'value_type' => 'offline',
+                    'group_name' => 'Оффлайн',
+                ];
+            }
+
+
+
+            $spreadsheet = new Spreadsheet();
+        $sheet = $spreadsheet->getActiveSheet();
+        $sheet->setTitle('Отчёт');
+
+        $sheet->setCellValue('A1', 'Неделя');
+        $sheet->setCellValue('B1', 'Тип п-ма');
+        $sheet->setCellValue('C1', 'Магазин');
+        $sheet->setCellValue('D1', 'Категория');
+        $sheet->setCellValue('E1', 'Подкатегория');
+        $sheet->setCellValue('F1', 'Вид');
+        $sheet->setCellValue('G1', 'Товар');
+        $sheet->setCellValue('H1', 'Кол-во');
+        $sheet->setCellValue('I1', 'Тип значения');
+        $sheet->setCellValue('J1', 'Имя группы');
+
+        $rowNum = 2;
+
+        foreach ($rows as $row) {
+            $sheet->setCellValue('A' . $rowNum, $row['week']);
+            $sheet->setCellValue('B' . $rowNum, $row['type_pm']);
+            $sheet->setCellValue('C' . $rowNum, $row['shop']);
+            $sheet->setCellValue('D' . $rowNum, $row['category']);
+            $sheet->setCellValue('E' . $rowNum, $row['subcategory']);
+            $sheet->setCellValue('F' . $rowNum, $row['species']);
+            $sheet->setCellValue('G' . $rowNum, $row['product_name']);
+            $sheet->setCellValue('H' . $rowNum, $row['quantity']);
+            $sheet->setCellValue('I' . $rowNum, $row['value_type']);
+            $sheet->setCellValue('J' . $rowNum, $row['group_name']);
+            $rowNum++;
+        }
+
+        $filename = 'report_' . date('Ymd_His') . '.xlsx';
+
+        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
+        header('Content-Disposition: attachment;filename="' . $filename . '"');
+        header('Cache-Control: max-age=0');
+
+        header('Expires: 0');
+        header('Pragma: public');
+
+        $writer = new Xlsx($spreadsheet);
+        $writer->save('php://output');
+        Yii::$app->end();
+    }
+
+
 }
diff --git a/erp24/views/auto-plannogramma/week-sales-species-excel.php b/erp24/views/auto-plannogramma/week-sales-species-excel.php
new file mode 100644
index 00000000..2166caef
--- /dev/null
+++ b/erp24/views/auto-plannogramma/week-sales-species-excel.php
@@ -0,0 +1,111 @@
+<?php
+?>
+    <div class="filter-form" style="margin-bottom: 20px;">
+        <?php use kartik\date\DatePicker;
+        use kartik\grid\GridView;
+        use kartik\select2\Select2;
+        use yii\helpers\ArrayHelper;
+        use yii\helpers\Html;
+        use yii\widgets\ActiveForm;
+        use yii_app\records\CityStore;
+        use yii_app\records\Products1cNomenclature;
+        ?>
+        <h1 class="ms-3 mb-4"><?= Html::encode("Выгрузка а эксель автоматическая") ?></h1>
+        <p class="ms-3 mb-4"><?= Html::encode("Сначала применить фильтр") ?></p>
+        <?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(['species' => $filters['species'] ?? '']), 'species')->widget(Select2::class, [
+                    'data' => ArrayHelper::map(
+                        Products1cNomenclature::find()->select('species')->distinct()->asArray()->all(),
+                        'species',
+                        'species'
+                    ),
+                    'options' => ['placeholder' => 'Тип товара', 'name' => 'species'],
+                    '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(['month' => $filters['month'] ?? '']), 'month')->dropDownList(\yii_app\helpers\DateHelper::MONTH_NUMBER_NAMES, [
+                    'prompt' => 'Месяц',
+                    'name' => 'month',
+                ])->label('Плановый месяц') ?>
+            </div>
+
+            <div class="col-md">
+                <?= $form->field(new \yii\base\DynamicModel(['year' => $filters['year'] ?? '']), 'year')->dropDownList(['2025' => 2025, '2026' => 2026], [
+                    'prompt' => 'Год',
+                    'name' => 'year',
+                ])->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-plannogramma/8'], ['class' => 'btn btn-default']) ?>
+            </div>
+        </div>
+
+        <?php ActiveForm::end(); ?>
+    </div>
+
+
+
+<?php
+
+$params = Yii::$app->request->get();
+
+$route = ['auto-plannogramma/export-excel'];
+
+$urlWithParams = array_merge($route, $params);
+?>
+<div class="row p-3">
+
+    <div class="col-md">
+        <?= Html::a('Экспорт в Excel', $urlWithParams, ['class' => 'btn btn-success']) ?>
+    </div>
+</div>
-- 
2.39.5