b2fc0234
 Mihail
add migration Pre...
|
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
|
<?php
use yii\db\Schema;
use yii\db\Migration;
/**
* Class m151013_062829_deletePrefixFunction
* добавляем две функции - одна ищет префикс по поставщику и бренду,
* другая удаляет найденный префикс из переданного артикула
*/
class m151013_062829_deletePrefixFunction extends Migration
{
public function safeUp()
{
$find_prefix = <<< SQL
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
SQL;
$delete_prefix = <<< SQL
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
SQL;
$this->execute($find_prefix);
$this->execute($delete_prefix);
}
public function safedown()
{
$sql =
'drop FUNCTION IF EXIST FindPrefix;
drop FUNCTION IF EXIST DeletePrefix;';
$this->execute($sql);
}
}
|
