<?php

use yii\db\Schema;
use yii\db\Migration;

class m151219_103804_goods_view extends Migration
{
    public function safeUp()
    {
        $view = <<< MySQL
        create view w_goods_view as
      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`,
                     `t`.`rate` as rate,
                     `t`.`id` as currency_id,
  `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('/storage/images/tecdoc/big/',`w_details_description`.`tecdoc_image`)
                         ),
                         concat('/storage/images/goods/big/',`w_details_description`.`image`)
                     ) as `image`,
  `w_details_description`.`tecdoc_id`,

						`w_details`.`PRICE` * rate as `price`,
                     `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_currency` as `t` on `t`.`id` = `w_importers`.`currency_id`;
MySQL;

        $this->execute($view);

    }

    public function safeDown()
    {
        // вернем все как было
        $drop_view = 'drop view if exists w_goods_view';

        $this->execute($drop_view);

    }
}