frontend/controllers/GoodsController.php

<?php
/**
 * Created by PhpStorm.
 * User: vitaliy
 * Date: 08.11.15
 * Time: 22:06
 */
namespace frontend\controllers;
use common\components\CustomVarDamp;
use common\models\DetailsCrosses;
use common\models\GoodsViewSearch;
use Yii;
use yii\web\Controller;
class GoodsController extends Controller {
    public $layout = '/internal';
    public function actionIndex($name = '')
    {
        $arr = ['GoodsViewSearch' => ['name' => '0092S40090',
                'brand' => 'BOSCH']];
        Yii::$app->session->setFlash('price_currency_id', 1);
        Yii::$app->session->setFlash('price_margin_id', 1);
        $searchModel = new GoodsViewSearch();
        $goods_provider = $searchModel->search($arr);
//$cross_arr = DetailsCrosses::find()->select('ARTICLE, BRAND')->where(['CROSS_ARTICLE' => '0092S40090', 'CROSS_BRAND' => 'BOSCH'])->asArray()->all();
//
//       $article_condition =  "'" . implode("','", array_column($cross_arr,'ARTICLE')) . "'";
//       $brand_condition =  "'" . implode("','", array_column($cross_arr,'BRAND')) . "'";
//
//        $crosses_provider = new SqlDataProvider([
//            'sql' => $query,
//            'key' => 'article',
//            'pagination' => false,
//            'params' => [':article' => $article_condition,
//                ':brand' => $brand_condition],
//            'sort' => [
//                'attributes' => [
//                    'box',
//                    'delivery',
//                    'price'
//                ],
//            ],
//        ]);
        return $this->render('index',[
            'goods_data_provider' => $goods_provider,
            'crosses_data_provider' => $goods_provider,
        ]);
    }
    private function getQuery(){
        $query = <<< MySQL
            select straight_join `w_details`.`ARTICLE` as `name`,
                     `w_details`.`BRAND` as `brand`,
                     `w_details`.`BOX` as `box`,
                     `w_details`.`ADD_BOX` as `add_box`,
                     `w_details`.`IMPORT_ID` as `importer_id`,
                     `w_importers`.`name` as `importer_name`,
  `w_importers`.`delivery`,
                     if (`w_details_description`.`description` = '',
                         if (`w_details_description`.`tecdoc_description` = '',
                             `w_details_description`.`supplier_description`,
                             `w_details_description`.`tecdoc_description`
                         ),
                         `w_details_description`.`description`
                     ) as `description`,
                     if (`w_details_description`.`tecdoc_article` = '',
                         if (`w_details_description`.`article` = '',
                             if (`w_details`.`FULL_ARTICLE` = '',
                                 `w_details`.`ARTICLE`,
                                 `w_details`.`FULL_ARTICLE`
                             ),
                             `w_details_description`.`article`
                         ),
                         `w_details_description`.`tecdoc_article`
                     ) as `article`,
  `w_details`.`ID`,
                     if (`w_details_description`.`image` = '',
                         if (`w_details_description`.`tecdoc_image` = '',
                             '',
                             concat('ital_origin/images/tecdoc/big/',`w_details_description`.`tecdoc_image`)
                         ),
                         concat('ital_origin/images/goods/big/',`w_details_description`.`image`)
                     ) as `image`,
  `w_details_description`.`tecdoc_id`,
                     round(if(`w_margins_groups`.`koef` is not null,
                              `w_details`.`PRICE`*`w_margins_groups`.`koef`,
                              if (`w_margins_importers`.`koef` is not null,
                                  `w_details`.`PRICE`*`w_margins_importers`.`koef`,
                                  `w_details`.`PRICE`*`w_margins`.`koef`
                              )
                           )*`t`.`rate`/`w_currency`.`rate`,2) as `price`,
                     round(if(`input_groups`.`koef` is not null,
                              `w_details`.`PRICE`*`input_groups`.`koef`,
                              `w_details`.`PRICE`
                           )*`t`.`rate`/`w_currency`.`rate`,2) as `input_price`,
                     round(if(`input_groups`.`koef` is not null,
                              `w_details`.`PRICE`*`input_groups`.`koef`,
                              `w_details`.`PRICE`
                           )*`t`.`rate`/`default`.`rate`,2) as `input_price_default`,
                     round(if(`w_margins_groups`.`koef` is not null,
                              `w_details`.`PRICE`*`w_margins_groups`.`koef`,
                              if (`w_margins_importers`.`koef` is not null,
                                  `w_details`.`PRICE`*`w_margins_importers`.`koef`,
                                  `w_details`.`PRICE`*`w_margins`.`koef`
                              )
                           )*`t`.`rate`/`default`.`rate`,2) as `price_default`,
                     `w_brands`.`ID` as `brand_id`
from `w_details`
  inner join `w_brands` on `w_brands`.`BRAND` = `w_details`.`BRAND`
  left join `w_details_description` on `w_details_description`.`name` = `w_details`.`ARTICLE` and
                                       `w_details_description`.`brand` = `w_details`.`BRAND`
  inner join `w_importers` on 	`w_importers`.`id` = `w_details`.`IMPORT_ID` and
                               `w_importers`.`active` = 1
  inner join `w_margins` on `w_margins`.`id` = @margin
  left join `w_margins_importers` on `w_margins_importers`.`importer_id` = `w_details`.`IMPORT_ID` and
                                     `w_margins_importers`.`margin_id`= @margin
  left join `w_margins_groups` on `w_margins_groups`.`importer_id` = `w_details`.`IMPORT_ID` and
                                  `w_margins_groups`.`margin_id`  = @margin and
                                  `w_margins_groups`.`group` = `w_details`.`group`
  inner join `w_currency` on `w_currency`.`id` = @currency
  inner join `w_currency` as `t` on `t`.`id` = `w_importers`.`currency_id`
  inner join `w_currency` as `default` on `default`.`is_default` = 1
  left join `w_margins_groups` as `input_groups` on `input_groups`.`importer_id` = `w_details`.`IMPORT_ID` and
                                                    `input_groups`.`margin_id` = 8 and
                                                    `input_groups`.`group` = `w_details`.`group`
where `w_details`.`ARTICLE` In(:article) and `w_details`.`BRAND` In(:brand) and (`w_details`.`BOX` > 0 or `w_details`.`ADD_BOX`  > 0)
MySQL;
        return $query;
}
}