customer_review/sql/schema.pg.sql

/*
walk
sales_walk_agg
sales_walk
sales_walk_seg_agg
sales_walk_seg
*/



CREATE TABLE IF NOT EXISTS rlarp.customer_review (
     bill_cust  text 
    ,ship_cust  text
    ,newords    numeric
    ,newuom     text
    ,PRIMARY KEY (bill_cust, ship_cust)
);

GRANT ALL ON TABLE rlarp.customer_review TO PUBLIC;
DROP VIEW IF EXISTS rlarp.cust_review_seg;
DROP VIEW IF EXISTS rlarp.cust_review;

------------------------------------------sales walk agg---------------------------------------------------
DROP MATERIALIZED VIEW IF EXISTS rlarp.cust_review_agg ;

CREATE MATERIALIZED VIEW rlarp.cust_review_agg AS
WITH 
act as (
SELECT 
	bill_dba  
	,ship_dba 
	,dsm 
	--,os.glec
	--,m.biggroup 
	--,os.priceg
	,COALESCE(sum(sales_usd) filter (WHERE sseas = 2023 AND version = 'Actual'),0)                                 "Actual 2023"
	,COALESCE(sum(sales_usd) filter (WHERE sseas = 2024 AND version = 'Actual'),0)                                 "Actual 2024"
 	,COALESCE(sum(sales_usd) filter (WHERE version = 'Actual' AND ostatus LIKE 'Open%' AND rseas <= 2024),0)       "Open Ord"
	--,COALESCE(sum(sales_usd) filter (WHERE version = 'Quotes'),0)						                            "Quotes"
FROM
	rlarp.osm_stack os
	LEFT OUTER JOIN rlarp.molds m ON
		m.stlc = substring(os.product ,1,8)
WHERE 
	(
		(
			sseas IN (2023,2024)
			AND version = 'Actual'
			AND ostatus = 'Shipped'
		)
		--OR (
		--	version = 'Quotes'
		--	AND odate >= '2023-05-01'::date
		--)
		OR (ostatus LIKE 'Open%')
	)
	AND calc_status <> 'CANCELED'
	AND substring(os.glec,1,1) <= '2'
    AND os.fs_line = '41010'
    AND COALESCE(ship_dba,'') <> ''
GROUP BY
	bill_dba  
	,ship_dba 
	,dsm
	--,os.glec
	--,m.biggroup 
	--,os.priceg
)
,bgt AS (
    SELECT 
         bill_dba  
        ,ship_dba 
        ,dsm 
        ,sum(sales_usd) sales
        ,sum(sales_usd) sales_usd
        --string_agg(distinct mold,', ') 
    FROM 
        rlarp.osm_stack
    WHERE   
            version = 'Budget'
        AND oseas = '2024'
    GROUP BY 
         bill_dba  
        ,ship_dba 
        ,dsm 
)
,agg AS (
    SELECT 
         COALESCE(act.bill_dba,TRIM(bgt.bill_dba))  bill_dba  
        ,COALESCE(act.ship_dba,TRIM(bgt.ship_dba))  ship_dba
        ,COALESCE(act.dsm     ,TRIM(bgt.dsm     ))  dsm       
        ,COALESCE(SUM(bgt.sales_usd       ),0)                     "Budget 2024"
        ,COALESCE(SUM(act."Actual 2023"),0)                     "Actual 2023" 
        ,COALESCE(SUM(act."Actual 2024"),0)                     "Actual 2024"
        ,COALESCE(SUM(act."Open Ord"   ),0)                     "Open Ord"
        --,COALESCE(SUM(act."Quotes"     ),0)                     "Quotes"
    FROM
        act
        FULL OUTER JOIN bgt ON
                TRIM(bgt.bill_dba)  =     act.bill_dba
            AND TRIM(bgt.ship_dba)  = act.ship_dba
            AND TRIM(bgt.dsm     )  = act.dsm 
    GROUP BY
         COALESCE(act.bill_dba,TRIM(bgt.bill_dba))
        ,COALESCE(act.ship_dba,TRIM(bgt.ship_dba))
        ,COALESCE(act.dsm     ,TRIM(bgt.dsm     ))
)
SELECT * FROM agg;

------------------------------------------sales walk-------------------------------------------------------
CREATE OR REPLACE VIEW rlarp.cust_review AS
SELECT 
	 agg.bill_dba  
	,agg.ship_dba 
	,agg.dsm 
    ,agg."Budget 2024" budget_2024
	,agg."Actual 2023" shipments_2023
	,agg."Actual 2024" shipments_2024
 	,agg."Open Ord" open_orders_2024
	--,"Quotes" quotes
    ,COALESCE(a.newords,0) newords
    ,COALESCE(a.newuom,'Units') newuom
FROM
	rlarp.cust_review_agg agg
	LEFT OUTER JOIN rlarp.customer_review a ON
		a.ship_cust = agg.ship_dba
		AND a.bill_cust = agg.bill_dba
WHERE
    "Budget 2024" <> 0
    OR "Actual 2023" <> 0 
    OR ("Actual 2024") <> 0
    OR ("Open Ord") <> 0
    --OR ("Quotes") <> 0
ORDER BY
    dsm
    --,"Budget 2024" + "Actual 2024" + "Actual 2023" + "Open Ord" + "Quotes" desc;
    ,"Budget 2024" + "Actual 2024" + "Actual 2023" + "Open Ord" desc;

GRANT SELECT ON rlarp.cust_review TO PUBLIC;
    --,biggroup