Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION get_pwo_id(integer, character varying, character varying, character varying, character varying)
- RETURNS SETOF json AS
- $BODY$
- DECLARE
- product_ids integer = $1;
- site_names character varying = $2;
- guest_token character varying = $3;
- access_token character varying = $4;
- image_size character varying = $5;
- level_global INTEGER;
- json_result json;
- BEGIN
- --Определяем количество уровней
- SELECT INTO level_global max(product_type_to_product_options.level) as level
- FROM product_type_to_product_options, products
- WHERE product_type_to_product_options.product_type_name = products.product_type_name AND products.id = product_ids;
- IF (level_global = 1) THEN
- with
- --Получаем продукты и их количество в корзине
- basket_pwo as (
- SELECT * from get_basket(guest_token, access_token)
- ),
- --Получаем PWO и цены
- pwo_v as (
- SELECT sp.products_with_options_id as products_with_options_id, sp.price, COALESCE(check_price(access_token), False) as is_visible_price
- FROM products_with_options as pwo, sale_price as sp
- WHERE pwo.id=sp.products_with_options_id AND pwo.product_id = product_ids AND sp.site_name= site_names
- ),
- full_pwo as (
- SELECT pwo_v.*, COALESCE(basket_pwo.quantity, 0) as quantity
- FROM pwo_v
- LEFT JOIN basket_pwo
- ON pwo_v.products_with_options_id = basket_pwo.pwo_id
- ),
- --Находим связи со значениями характеристик
- pwo_to_po_v as (
- SELECT pwo_to_pov.product_options_value_id, full_pwo.products_with_options_id,
- full_pwo.price, full_pwo.quantity, full_pwo.is_visible_price,
- COALESCE(get_image(product_ids, pwo_to_pov.product_options_value_id, image_size, site_names)) as image
- FROM products_with_options_to_product_options_value as pwo_to_pov, full_pwo
- WHERE pwo_to_pov.products_with_options_id=full_pwo.products_with_options_id
- ),
- --Получаем PWO_id, product_options_id, value_id, value и сортируем по полю value
- pov_v as (
- SELECT pwo_to_po_v.products_with_options_id, pov.product_options_id, pov.id as id_value,
- value, pwo_to_po_v.price, pwo_to_po_v.quantity, pwo_to_po_v.is_visible_price, pwo_to_po_v.image
- FROM product_options_value as pov, pwo_to_po_v
- WHERE pov.id=pwo_to_po_v.product_options_value_id
- ORDER BY value
- ),
- --Получаем название характеристики
- po_v as (
- SELECT po.name as level1
- FROM pov_v, product_options as po
- WHERE pov_v.product_options_id=po.id
- LIMIT 1
- ),
- --Пихаем в JSON структуру level1
- json_level1_virtual as (
- SELECT json_agg(
- row_to_json (((
- SELECT t FROM (SELECT products_with_options_id, id_value, value, price, quantity, is_visible_price, image)
- as t( products_with_options_id, id_value, value, price, quantity, is_visible_price, image))))
- ) as level1
- FROM pov_v
- ),
- --Пихаем в JSON структуру name
- json_name_virtual as (
- SELECT row_to_json(po_v) as names
- FROM po_v
- ),
- --Связыем level1 и name в одну таблицу
- level1_name_virtual as (
- SELECT json_name_virtual.names, json_level1_virtual.level1
- FROM json_name_virtual, json_level1_virtual
- ),
- --Пихаем в JSON структуру products_with_options
- json_pwo_virtual as (
- SELECT row_to_json(level1_name_virtual) as products_with_options
- FROM level1_name_virtual
- )
- SELECT INTO json_result products_with_options FROM json_pwo_virtual;
- RETURN NEXT json_result;
- ELSEIF (level_global = 2) THEN
- WITH
- --Получаем продукты и их количество в корзине
- basket_pwo as (
- SELECT * from get_basket(guest_token, access_token)
- ),
- -- Получаем уровни для значений
- po_values AS (
- SELECT product_type_to_product_options.product_options_id, product_type_to_product_options.level AS level
- FROM product_type_to_product_options, products
- WHERE product_type_to_product_options.product_type_name = products.product_type_name AND products.id = product_ids AND NOT level = 0
- ),
- -- Получаем PWO и цены
- pwo_v AS (
- SELECT sp.products_with_options_id AS products_with_options_id, sp.price, COALESCE(check_price(access_token), False) as is_visible_price
- FROM products_with_options AS pwo, sale_price AS sp
- WHERE pwo.id=sp.products_with_options_id AND pwo.product_id = product_ids AND sp.site_name= site_names
- ),
- -- получаем продукты и их количество в корзине
- full_pwo as (
- SELECT pwo_v.*, COALESCE(basket_pwo.quantity, 0) as quantity
- FROM pwo_v
- LEFT JOIN basket_pwo
- ON pwo_v.products_with_options_id = basket_pwo.pwo_id
- ),
- -- Находим связи со значениями характеристик
- pwo_to_po_v AS (
- SELECT pwo_to_pov.product_options_value_id, full_pwo.products_with_options_id, full_pwo.price, full_pwo.quantity, full_pwo.is_visible_price,
- COALESCE(get_image(product_ids, pwo_to_pov.product_options_value_id, image_size, site_names)) as image
- FROM products_with_options_to_product_options_value AS pwo_to_pov, full_pwo
- WHERE pwo_to_pov.products_with_options_id=full_pwo.products_with_options_id
- ),
- -- Получаем PWO_id, product_options_id, value_id, value и сортируем по полю value и добавляем к ним level
- pov_v AS (
- SELECT pwo_to_po_v.products_with_options_id, pov.product_options_id, pov.id AS id_value,
- value, pwo_to_po_v.price, pwo_to_po_v.quantity, pwo_to_po_v.is_visible_price, pwo_to_po_v.image
- FROM product_options_value AS pov, pwo_to_po_v
- WHERE pov.id=pwo_to_po_v.product_options_value_id
- ),
- pov_values AS (
- SELECT pov_v.*, po_values.level
- FROM pov_v
- FULL JOIN po_values
- ON pov_v.product_options_id = po_values.product_options_id
- ),
- -- Объединение в пары двух уровней
- join_pov AS (
- SELECT p1.products_with_options_id, p1.value as val, p1.id_value as id_v,
- p2.value as value, p2.price, p2.quantity, p2.id_value, p2.is_visible_price, p1.image as image1, p2.image as image2
- from pov_values as p1
- JOIN pov_values as p2
- ON p1.products_with_options_id = p2.products_with_options_id
- WHERE p1.level = 1 AND p2.level = 2
- ORDER BY NULLIF(regexp_replace(p2.value, E'\\^[0-9+\.]', '', 'g'), '')::float
- ),
- -- Разбивка на уровни характеристик
- pov_json AS (
- SELECT val as value, id_v as id_value, image1 as image, json_agg (
- row_to_json (
- (SELECT t FROM (SELECT products_with_options_id, id_value, value, price, quantity, is_visible_price, image2)
- AS t( products_with_options_id, id_value, value, price, quantity, is_visible_price, image2))
- )
- ) as level2
- FROM join_pov
- GROUP BY val, id_v, image
- ),
- -- Пихаем в JSON структуру level1
- pov_level1_json as (
- SELECT json_agg(pov_json) AS level1
- FROM pov_json
- ),
- -- Получаем название характеристики
- po_v_1 AS (
- SELECT 1 as indx, po.name AS level1
- FROM pov_values, product_options AS po
- WHERE pov_values.product_options_id=po.id AND level=1
- LIMIT 1
- ),
- po_v_2 AS (
- SELECT 1 as indx, po.name AS level2
- FROM pov_values, product_options AS po
- WHERE pov_values.product_options_id=po.id AND level=2
- LIMIT 1
- ),
- po_v as (
- select po_v_1.level1, po_v_2.level2
- from po_v_1
- join po_v_2
- on po_v_1.indx = po_v_2.indx
- ),
- -- Пихаем в JSON структуру name
- json_name_v AS (
- SELECT row_to_json(po_v) AS names
- FROM po_v
- ),
- -- Связыем level1 и name в одну таблицу
- level1_name_virtual AS (
- SELECT json_name_v.names, pov_level1_json.level1
- FROM json_name_v, pov_level1_json
- ),
- -- Пихаем в JSON структуру products_with_options
- json_pwo_virtual AS (
- SELECT row_to_json(level1_name_virtual) AS products_with_options
- FROM level1_name_virtual
- )
- SELECT INTO json_result products_with_options FROM json_pwo_virtual;
- RETURN NEXT json_result;
- ELSE
- with
- --Получаем продукты и их количество в корзине
- basket_pwo as (
- SELECT * from get_basket(guest_token, access_token)
- ),
- --Находим PWO_id и price
- pwo_v as (
- SELECT sp.products_with_options_id as products_with_options_id, sp.price
- FROM products_with_options as pwo, sale_price as sp
- WHERE pwo.id=sp.products_with_options_id AND pwo.product_id = product_ids AND sp.site_name= site_names
- ),
- full_pwo as (
- SELECT pwo_v.*, COALESCE(basket_pwo.quantity, 0) as quantity, COALESCE(check_price(access_token), False) as is_visible_price
- FROM pwo_v
- LEFT JOIN basket_pwo
- ON pwo_v.products_with_options_id = basket_pwo.pwo_id
- ),
- -- Пихаем в JSON
- json_pwo as (
- SELECT json_agg(full_pwo) as products_with_options
- FROM full_pwo
- )
- SELECT INTO json_result products_with_options FROM json_pwo;
- RETURN NEXT json_result;
- END IF;
- END
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- ALTER FUNCTION get_pwo_id(integer, character varying, character varying, character varying, character varying)
- OWNER TO "ros-zdravnica";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement