Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DB::select($sql)
- select pd_report_orders_sum_by_categories( p_category_list := ARRAY[102,17]::integer[], p_last_operation_date_from := '2012-09-20', p_last_operation_date_till := '2017-09-29', p_status := 'O', p_sort_categories := 'sum', p_limit := null )
- и функция определена как:
- CREATE OR REPLACE FUNCTION public.pd_report_orders_sum_by_categories(p_category_list integer[], p_last_operation_date_from timestamp without time zone, p_last_operation_date_till timestamp without time zone, p_status type_order_status, p_sort_categories character varying DEFAULT 'by_sum_asc'::character varying, p_limit integer DEFAULT NULL::integer)
- RETURNS TABLE(category_name character varying, category_slug character varying, category_id smallint, sold_sum numeric, qty_sum bigint)
- LANGUAGE sql
- AS $function$
- SELECT c.name as category_name,
- c.slug as category_slug,
- pc.category_id,
- sum( oi.qty*oi.price ) AS sold_sum,
- sum( oi.qty ) AS qty_sum
- from pd_order_item as oi join
- pd_product_category as pc on pc.product_id = oi.product_id join
- pd_order as o on o.id = oi.order_id join
- pd_category as c on c.id = pc.category_id
- WHERE ( CASE when p_category_list IS NOT NULL THEN pc.category_id = ANY (p_category_list) else true END ) AND
- ( o.last_operation_date BETWEEN coalesce(p_last_operation_date_from,pd_f_min_timestamp()) AND coalesce(p_last_operation_date_till,pd_f_max_timestamp()) ) AND
- ( CASE when p_status IS NOT NULL THEN o.status = p_status else true END )
- group by pc.category_id, category_name, category_slug
- ORDER BY
- CASE WHEN p_sort_categories = 'by_sum_asc' THEN
- sum( oi.qty*oi.price )
- end ASC,
- CASE WHEN p_sort_categories = 'by_sum_desc' THEN
- sum( oi.qty*oi.price )
- end DESC,
- CASE WHEN p_sort_categories = 'by_sum_category_name' THEN
- c.name
- end ASC
- LIMIT p_limit ;
- $function$
- Array
- (
- [0] => stdClass Object
- (
- [pd_report_orders_sum_by_categories] => (Music,music,4,2.43,1)
- )
- [1] => stdClass Object
- (
- [pd_report_orders_sum_by_categories] => ("Computer Accessories",computer-accessories,1,25.75,1)
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement