-- Function: uaction.gf_oper_get_cafe_keys(integer, integer)

-- DROP FUNCTION uaction.gf_oper_get_cafe_keys(integer, integer);

CREATE OR REPLACE FUNCTION uaction.gf_oper_get_cafe_keys(IN igroup integer, IN idesk integer, OUT tt_o integer, OUT id_o integer, OUT typ_o integer, OUT title_o text, OUT id_list_o numeric(13), OUT img_o boolean, OUT num_modification_o numeric(10), OUT price_o numeric)
  RETURNS SETOF record AS
$BODY$
DECLARE
  rec RECORD;
  binminus boolean;
BEGIN
  ----Входные параметры 
  --1) igroup - Путь группы
  --2) idesk - Рабочее место
  ----Выходные данные
  --1) tt_o - тип строки - 0 - кнопка назад, 1 - остальные кнопки
  --2) id_o - идентификатор кнопки в cafe_keys
  --3) typ_o - тип кнопки. 0 - группа кнопок, 1 - элемент
  --4) title_o - наименование
  --5) id_list_o - артикул для элемента
  --6) img_o - наличие изображения у кнопки
  --7) num_modification_o - номер последнего изменения карточки
  --8) price_o - цена продажи

  binminus = (sf_get_one_local_conf(10) = '1');

  FOR rec IN 
    SELECT tt, id, typ, title, id_list, img, num_modification, 
           trim(replace(to_char(price[1],'9999990.99'),'.00','')) AS price
      FROM
        (SELECT 0 AS tt, 
                0 AS id, 
                0 AS typ, 
                'В начало' AS title, 
                null::numeric AS id_list, 
                0 AS path, 
                false AS img, 
                0 AS num_modification, 
                NULL::numeric[] AS price, 
                false AS service
           FROM gd_cafe_keys as s1
          WHERE s1.id=igroup AND s1.id_parent IS NOT NULL
         UNION ALL
         SELECT 0 AS tt, 
                gd_cafe_keys.id_parent AS id, 
                0 AS typ, 
                'Назад' AS title, 
                null::numeric AS id_list, 
                1 AS path, 
                false AS img, 
                0 AS num_modification, 
                NULL::numeric[] AS price, 
                false AS service
           FROM gd_cafe_keys WHERE id=igroup AND igroup IS NOT NULL
         UNION ALL
         SELECT 1 AS tt, 
                gd_cafe_keys.id, 
                gd_cafe_keys.typ, 
                gd_cafe_keys.title, 
                gd_cafe_keys.id_list, 
                path,
                CASE WHEN typ=0 AND gd_cafe_keys_img IS NOT NULL THEN true 
                     WHEN typ=1 AND gd_list_img IS NOT NULL AND gd_list.num_modification IS NOT NULL THEN TRUE
                  ELSE FALSE END AS img,
                CASE WHEN typ=0 THEN gd_cafe_keys.num_modification 
                     WHEN typ=1 THEN gd_list.num_modification 
                  ELSE NULL END AS num_modification,
                CASE WHEN gd_list.id IS NOT NULL THEN (SELECT ARRAY[m.price_o, m.count_o]::numeric[] FROM uaction.gf_oper_get_art_info(gd_list.id, idesk) as m)
                  ELSE ARRAY[NULL, NULL]::numeric[] END AS price, 
                COALESCE(gd_list.service, false) AS service 
          FROM gd_cafe_keys
                 LEFT JOIN gd_cafe_keys_img ON gd_cafe_keys.id=gd_cafe_keys_img.id 
                           AND gd_cafe_keys_img.full_del=false AND gd_cafe_keys.typ=0
                 LEFT JOIN gd_list_img ON gd_cafe_keys.id_list=gd_list_img.id_list 
                           AND gd_list_img.full_del=false AND gd_cafe_keys.typ=1
                 LEFT JOIN gd_list ON gd_cafe_keys.id_list=gd_list.id AND gd_cafe_keys.typ=1
         WHERE ((gd_cafe_keys.id_parent IS NULL AND igroup IS NULL) OR (gd_cafe_keys.id_parent=igroup))
               AND gd_cafe_keys.isenable=true AND gd_cafe_keys.full_del=false
      ) AS T 
     WHERE typ=0 OR 
           (typ=1 AND price[2] IS NOT NULL AND (price[2]>0 OR binminus) AND service=false AND price[1] IS NOT NULL) OR 
           (typ=1 AND service=true AND price[1] IS NOT NULL) 
     ORDER BY tt, path
    LOOP
    tt_o = rec.tt;
    id_o = rec.id;
    typ_o = rec.typ;
    title_o = rec.title;
    id_list_o = rec.id_list;
    img_o = rec.img;
    num_modification_o = rec.num_modification;
    price_o = rec.price;
    
    RETURN NEXT;
  END LOOP;

  RETURN;
END;
$BODY$
  LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION uaction.gf_oper_get_cafe_keys(integer, integer) TO public;
COMMENT ON FUNCTION uaction.gf_oper_get_cafe_keys(integer, integer) IS 'v6.5.0 Функция. Вывод списка кнопок кафе.';
 
