<?php
    namespace backend\controllers;
    
    use yii\web\Controller;
    use PHPExcel_IOFactory;
    use yii\web\Response;
    
    /**
     * Class PriceImportController
     *
     * @package backend\controllers
     */
    class PriceImportController extends Controller
    {
        public function actionIndex()
        {
            return $this->render('index');
        }
        
        public function actionUpload()
        {
            \Yii::$app->response->format = Response::FORMAT_JSON;
            
            $error = false;
            $files = [];
            
            $uploaddir = \Yii::getAlias('@storage/');
            foreach ($_FILES as $file) {
                if (move_uploaded_file($file[ 'tmp_name' ], $uploaddir . 'import_prices.xlsx')) {
                    $files[] = $uploaddir . $file[ 'name' ];
                } else {
                    $error = true;
                }
            }
            
            $data = ( $error ) ? [ 'error' => 'There was an error uploading your files' ] : [ 'files' => $files ];
            
            $this->importPrices();
            
            return $data;
        }
        
        protected function importPrices()
        {
            $xlsx = PHPExcel_IOFactory::load(\Yii::getAlias('@storage/import_prices.xlsx'));
            $xlsx->setActiveSheetIndex(0);
            $sheet = $xlsx->getActiveSheet();
            $rowIterator = $sheet->getRowIterator();
            $j = 0;
            $insert = [];
            foreach ($rowIterator as $row) {
                $j++;
                $cellIterator = $row->getCellIterator();
                $row = [];
                $i = 0;
                foreach ($cellIterator as $cell) {
                    /**
                     * @var \PHPExcel_Cell $cell
                     */
                    $i++;
                    $row[ $i ] = $cell->getValue();
                    if ($i > 4) {
                        break;
                    }
                }
                $insert[] = [
                    $row[ 1 ],
                    $row[ 2 ],
                    $row[ 3 ],
                    $row[ 4 ] ?? null,
                ];
                if (empty($row[ 1 ])) {
                    break;
                }
            }
            
            \Yii::$app->db->createCommand()
                          ->truncateTable('price_upload')
                          ->execute();
            \Yii::$app->db->createCommand()
                          ->batchInsert(
                              'price_upload',
                              [
                                  'sku',
                                  'stock',
                                  'price',
                                  'price_old',
                              ],
                              $insert
                          )
                          ->execute();
            
            $transaction = \Yii::$app->db->beginTransaction();
            try {
                \Yii::$app->db->createCommand(
                    'UPDATE variant SET price=pu.price
                                            , price_old=pu.price_old , stock=pu.stock
                                             FROM (
                                                SELECT * FROM price_upload
                                             ) pu
                                             WHERE variant.sku = pu.sku'
                )
                              ->execute();
                $transaction->commit();
                return true;
            } catch (\Exception $e) {
                $transaction->rollBack();
                throw $e;
                
            } catch (\Throwable $e) {
                $transaction->rollBack();
                return false;
            }
        }
    }