m151013_062829_deletePrefixFunction.php
1.62 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
<?php
use yii\db\Schema;
use yii\db\Migration;
/**
* Class m151013_062829_deletePrefixFunction
* добавляем две функции - одна ищет префикс по поставщику и бренду,
* другая удаляет найденный префикс из переданного артикула
*/
class m151013_062829_deletePrefixFunction extends Migration
{
public function safeUp()
{
$find_prefix = <<< MySQL
CREATE FUNCTION FindPrefix(p_importer_id int, p_brand VARCHAR(100)) RETURNS VARCHAR(50)
BEGIN
DECLARE _prefix varchar(10);
select prefix into _prefix From w_importers_prefix where importer_id = p_importer_id and brand = p_brand COLLATE utf8_general_ci;
RETURN (_prefix);
END
MySQL;
$delete_prefix = <<< MySQL
CREATE FUNCTION DeletePrefix(p_articul VARCHAR(150), p_importer_id int, p_brand VARCHAR(100)) RETURNS VARCHAR(150)
BEGIN
DECLARE _articul varchar(10);
select substring(p_articul, LENGTH( FindPrefix( p_importer_id, p_brand ) ) + 1 ) into _articul;
RETURN (_articul);
END
MySQL;
$this->execute($find_prefix);
$this->execute($delete_prefix);
}
public function safedown()
{
$find_prefix = <<< MySQL
drop FUNCTION FindPrefix;
MySQL;
$delete_prefix = <<< MySQL
drop FUNCTION DeletePrefix;
MySQL;
$this->execute($find_prefix);
$this->execute($delete_prefix);
}
}