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 Yii;
use yii\data\SqlDataProvider;
use yii\web\Controller;
class GoodsController extends Controller {
    public $layout = '/internal';
    public function actionIndex($name = '')
    {
     $query = $this->getQuery();
        $provider = new SqlDataProvider([
            'sql' => $query,
            'pagination' => false,
            'params' => [':article' => '0092S40090',
                ':brand' => 'BOSCH',
                ':margin_id' => '1',
                ':currency_id' => '1'],
            'sort' => [
                'attributes' => [
                    'box',
                    'delivery',
                    'price'
                ],
            ],
        ]);
        return $this->render('index',[
            'dataProvider' => $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_id
  left join `w_margins_importers` on `w_margins_importers`.`importer_id` = `w_details`.`IMPORT_ID` and
                                     `w_margins_importers`.`margin_id`=:margin_id
  left join `w_margins_groups` on `w_margins_groups`.`importer_id` = `w_details`.`IMPORT_ID` and
                                  `w_margins_groups`.`margin_id`  =:margin_id and
                                  `w_margins_groups`.`group` = `w_details`.`group`
  inner join `w_currency` on `w_currency`.`id` =:currency_id
  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` =:article and `w_details`.`BRAND` =:brand and (`w_details`.`BOX` > 0 or `w_details`.`ADD_BOX`)
MySQL;
        return $query;
}
}