Administrator / test_1

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

      }
  
  
  }