m151219_103804_goods_view.php
2.89 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
<?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('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`,
`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);
}
}