PROCEDURE h20.menu(IN a_id int, IN a_level int, IN ntree_ready int, IN login_ varchar(20))
  DETERMINISTIC
BEGIN
  DECLARE done int DEFAULT 0;
  DECLARE a_role,
          a_parent_id,
          a_order,
          mn,
          a_action int;
  DECLARE a_name varchar(255);
  DECLARE a_action_string varchar(50);


  DECLARE cur1 CURSOR FOR
  SELECT DISTINCT
    menu.id,
    menu.parent,
    menu.name,
    menu.role,
    menu.`order`,
    menu.action,
    menu.action_string
  FROM menu
    INNER JOIN role_pages
      ON FIND_IN_SET(menu.action_string, (SELECT
          GROUP_CONCAT(role_pages.pages) AS expr1
        FROM role_pages
          INNER JOIN users
            ON role_pages.id_role = users.role
        WHERE users.login = login_))
      OR menu.action_string = ''
    INNER JOIN users
      ON users.role = role_pages.id_role
      AND menu.parent = a_id
      AND menu.action = 0
  ORDER BY menu.`order`;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  IF ntree_ready = 1
  THEN
    DROP TABLE IF EXISTS menu_tree;

    CREATE TABLE menu_tree (
      id int(11) NOT NULL AUTO_INCREMENT,
      id_in int(11) NOT NULL,
      parent_id int(11) NOT NULL,
      name varchar(50) NOT NULL,
      role int(11) NOT NULL,
      nlevel int(11) NOT NULL,
      `order` int(11) DEFAULT NULL,
      action int(11),
      action_string varchar(50),
      PRIMARY KEY (id)
    )
    ENGINE = MEMORY
    AUTO_INCREMENT = 1
    AVG_ROW_LENGTH = 176
    CHARACTER SET utf8
    COLLATE utf8_general_ci;

  END IF;

  OPEN cur1;

  SET @@max_sp_recursion_depth = 10;


  REPEAT

    FETCH cur1 INTO a_id, a_parent_id, a_name, a_role, a_order, a_action, a_action_string;

    IF NOT done
    THEN

      INSERT INTO menu_tree (id_in, parent_id, `name`, role, `order`, nlevel, `action`, action_string)
        VALUES (a_id, a_parent_id, a_name, menu_tree.role, a_order, a_level, a_action, a_action_string);

      CALL menu(a_id, a_level + 1, 0, login_);


    END IF;

  UNTIL done

  END REPEAT;

  CLOSE cur1;

  SET @a = 0;

  IF ntree_ready = 1
  THEN
    SELECT
      d.id,
      d.id_in,
      d.parent_id,
      d.`name`,
      d.role,
      nlevel,
      IFNULL((SELECT
          nlevel
        FROM menu_tree
        WHERE id = d.id + 1), 0) AS b_level,
      d.`order`,
      d.`action`,
      d.action_string
    FROM menu_tree AS d
    ORDER BY d.id;
  END IF;

END

PROCEDURE xtest._menu(IN CharId_ int)
BEGIN
  SET @a = 1;
  SET @@group_concat_max_len = 42949672;


  SELECT
    users.role INTO @role
  FROM users
  WHERE users.ChatId = CharId_;


  SELECT
    CONCAT('<ul class="treeCSS"  id="divmenu">', GROUP_CONCAT(d SEPARATOR ''), REPEAT('</ul>', -1 + SUBSTRING_INDEX (GROUP_CONCAT(l SEPARATOR ','), ",", -1)), '<li data-action="page0.jsp"><span>выход</span></ul>') dd
  FROM (WITH RECURSIVE Rec (
          lev,
          id,
          name,
          id_parent,
          order_,
          ord,
          action
        )
      AS
      ((SELECT
              1 AS lev,
              _menu.id,
              spr_pages.name_menu AS name,
              IFNULL(_menu.id_parent, 0),
              CAST(LPAD(_menu.`order`, 3, 0) AS char(200)),
              _menu.`order`,
              spr_pages.name_file AS action
            FROM _menu
              INNER JOIN spr_pages
                ON _menu.action_file = spr_pages.id
            WHERE _menu.id_parent = 0
            AND _menu.role = @role
            ORDER BY _menu.`order`)
        UNION ALL
        SELECT
          Rec.lev + 1,
          _menu.id,
          spr_pages.name_menu AS name,
          _menu.id_parent,
          CONCAT(Rec.order_, ',', LPAD(_menu.order, 3, 0)) AS order_,
          _menu.order AS org,
          spr_pages.name_file AS action
        FROM Rec
          JOIN _menu
            ON Rec.id = _menu.id_parent
          INNER JOIN spr_pages
            ON _menu.action_file = spr_pages.id

        WHERE _menu.role = @role)
    SELECT

      CASE
        WHEN @a < lev THEN CONCAT('<ul><li data-lev="', lev, '" data-order="', Rec.ord, '" data-p_id="', Rec.id_parent, '" data-id="', Rec.id, '" data-action="', Rec.action, '" ><span>', Rec.name, '</span>')
        WHEN @a > lev THEN CONCAT(REPEAT('</ul>', @a - lev), '<li data-lev="', lev, '" data-order="', Rec.ord, '" data-p_id="', Rec.id_parent, '" data-id="', Rec.id, '" data-action="', Rec.action, '"><span>', Rec.name, '</span>')
        WHEN @a = lev THEN CONCAT('<li data-lev="', lev, '" data-order="', Rec.ord, '" data-p_id="', Rec.id_parent, '" data-id="', Rec.id, '" data-action="', Rec.action, '"><span>', Rec.name, '</span>')
      END AS d,
      @a := lev AS l

    FROM Rec
    ORDER BY Rec.order_, Rec.ord) AS ff
  ;

END

PROCEDURE tan.page5_xxx50_listBox(IN param varchar(400))
  DETERMINISTIC
BEGIN

  SET @param = REPLACE(param, 'name', 'name_reagent');

  SET @sql = CONCAT('SELECT id, name_reagent as name FROM l_spr_name_reagent where', @param);

  PREPARE sqll FROM @sql;

  EXECUTE sqll;
  DEALLOCATE PREPARE sqll;


END