<?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);
    }


}