pivot_forecast/setup_sql/04_gen_baseline.sql

DO
$func$
DECLARE
    _clist          text;
    _clist_sum      text;
    _clist_group    text;
    _clist_inc      text;
    _version_col    text;
    _iter_col       text;
    _logid_col      text;
    _order_date     text;
    _ship_date      text;
    _order_status   text;
    _units_col      text;
    _value_col      text;
    _ytdbody        text;
    _actpy          text;
    _sql            text;
    _baseline       text;
    _date_funcs     jsonb;
    _perd_joins     text;
    _interval       interval;
    _target_table   text;

/*----------------parameters listing--------------
app_baseline_from_date
app_baseline_to_date
app_first_forecast_date
app_openorder_cutoff
app_openstatus_code
app_plug_fromdate
app_plug_todate
------------------------------------------------*/

BEGIN

-----------------populate application variables--------------------------------------------
SELECT cname INTO _order_date   FROM fc.target_meta WHERE appcol = 'order_date'      ;
SELECT cname INTO _ship_date    FROM fc.target_meta WHERE appcol = 'ship_date'       ;
SELECT cname INTO _order_status FROM fc.target_meta WHERE appcol = 'order_status'    ;
SELECT cname INTO _units_col    FROM fc.target_meta WHERE appcol = 'units'           ;
SELECT cname INTO _value_col    FROM fc.target_meta WHERE appcol = 'value'           ;
SELECT cname INTO _version_col  FROM fc.target_meta WHERE appcol = 'version'         ;
SELECT cname INTO _iter_col     FROM fc.target_meta WHERE appcol = 'iter'            ;
SELECT cname INTO _logid_col    FROM fc.target_meta WHERE appcol = 'logid'           ;
SELECT format('%I',max(schema))||'.'||format('%I',max(tname))   INTO _target_table FROM fc.target_meta;
SELECT interval '1 year' INTO _interval;

----------------------setup listing of date joins required-----------------------------------------------------
SELECT jsonb_agg(DISTINCT func) INTO _date_funcs FROM fc.target_meta WHERE dtype = 'date' AND fkey is NOT null;
raise notice E'date list  -----> \n    %',_date_funcs;

-----------------------create the date based table joins--------------------------------------------------------
--create table join for each date based func in target_meta joining to fc.perd static table
--the join, though, should be based on the target date, which is needs an interval added to get to the target
SELECT
    string_agg(
        'LEFT OUTER JOIN fc.perd '||func||' ON'||
        $$
        $$||'(o.'||format('%I',cname)||' + interval '||format('%L',_interval) ||' )::date <@ '||func||'.drange'
    ,E'\n    ')
INTO
    _perd_joins
FROM 
    fc.target_meta 
WHERE 
    dtype = 'date' 
    AND func IS NOT NULL;

raise notice E'period table joins   -----> \n    %',_perd_joins;

-------------------------------build a column for sums-----------------------------------------
SELECT 
    --string_agg('o.'||format('%I',cname),E'\n    ,' ORDER BY opos ASC)
    string_agg(
        --create the column reference
        CASE appcol 
            ----aggregation columns-----------------------------------------------------------
            WHEN 'units'    THEN 'sum(o.'||format('%I',cname)||') AS '||format('%I',cname) 
            WHEN 'value'    THEN 'sum(o.'||format('%I',cname)||') AS '||format('%I',cname) 
            WHEN 'cost'     THEN 'sum(o.'||format('%I',cname)||') AS '||format('%I',cname) 
            ----create singular values for fingle forecast step-------------------------------
            WHEN 'version'  THEN '''app_forecast_name'' AS '||format('%I',_version_col)
            WHEN 'iter'     THEN '''baseline'' AS '||format('%I',_iter_col)
            WHEN 'logid'    THEN '(SELECT id FROM ilog) AS '||format('%I',_logid_col)
            ELSE 
                ---if there is no function, make the column null or the default value---
                CASE WHEN t.func IS NULL 
                    THEN 'null::'||t.dtype||' AS '||format('%I',cname)
                    ELSE 'o.'||format('%I',cname)
                END
        END
        --delimiter
        ,E'\n    ,' 
        --sort column ordinal
        ORDER BY opos ASC
    )
INTO
    _clist_sum
FROM 
    fc.target_meta t;

raise notice E'column list for sums -----> \n    %',_clist_sum;
-------------------------------build a column for groupby-----------------------------------------
SELECT 
    --string_agg('o.'||format('%I',cname),E'\n    ,' ORDER BY opos ASC)
    string_agg(
        --create the column reference
        'o.'||format('%I',cname)
        --delimiter
        ,E'\n    ,' 
        --sort column ordinal
        ORDER BY opos ASC
    )
INTO
    _clist_group
FROM 
    fc.target_meta t
WHERE
    COALESCE(appcol,'') NOT IN ('units','value','cost','version','iter','logid')
    AND func IS NOT NULL;

raise notice E'column list for group by-----> \n    %',_clist_group;
---------------------------build column to increment dates---------------------------------

SELECT 
    string_agg(
        CASE
            --if you're dealing with a date function...
            WHEN _date_funcs ? func THEN
                CASE 
                    --...but it's not the date itself...
                    WHEN fkey IS NULL THEN 
                        --...pull the associated date field from perd table
                        'perd.'||m.fcol
                    --...and it's the primary key date...
                    ELSE 
                        --use the date key but increment by the target interval
                        --this assumes that the primary key for the func is a date, but it has to be or it wont join anyways
                        'o.'||format('%I',cname)||' + interval '||format('%L',_interval) ||' AS '||format('%I',cname)
                END
            ELSE
                CASE appcol 
                    ----aggregation columns-----------------------------------------------------------
                    WHEN 'units'    THEN 'sum(o.'||format('%I',cname)||') AS '||format('%I',cname) 
                    WHEN 'value'    THEN 'sum(o.'||format('%I',cname)||') AS '||format('%I',cname) 
                    WHEN 'cost'     THEN 'sum(o.'||format('%I',cname)||') AS '||format('%I',cname) 
                    ----create singular values for fingle forecast step-------------------------------
                    WHEN 'version'  THEN '''app_forecast_name'' AS '||format('%I',_version_col)
                    WHEN 'iter'     THEN '''baseline'' AS '||format('%I',_iter_col)
                    WHEN 'logid'    THEN '(SELECT id FROM ilog) AS '||format('%I',_logid_col)
                    ELSE 
                        ---if there is no function, make the column null or the default value---
                        CASE WHEN m.func IS NULL 
                            THEN 'null::'||m.dtype||' AS '||format('%I',cname)
                            ELSE 'o.'||format('%I',cname)
                        END
                END
        END
        ,E'\n    ,' ORDER BY opos ASC
    )
INTO
    _clist_inc
FROM 
    fc.target_meta m;
--WHERE 
--    COALESCE(appcol,'') NOT IN ('version','iter','logid');

RAISE NOTICE E'DATES INCREMENTED: ------> \n    %',_clist_inc;

--------------------------------------clone the actual baseline-----------------------------------------------

SELECT 
$$SELECT
    $$||
    _clist_sum||
    $$
FROM
    $$||_target_table||$$ o
WHERE
    (
        --base period orders booked....
        $$||format('%I',_order_date)||$$ BETWEEN 'app_baseline_from_date'::date AND 'app_baseline_to_date'::date
        --...or any open orders currently booked before cutoff....
        OR ($$||format('%I',_order_status)||$$ IN (app_openstatus_code) and $$||format('%I',_order_date)||$$ <= 'app_openorder_cutoff'::date)
        --...or anything that shipped in that period
        OR ($$||format('%I',_ship_date)||$$ BETWEEN 'app_baseline_from_date'::date AND 'app_baseline_to_date'::date)
    )
    --be sure to pre-exclude unwanted items, like canceled orders, non-gross sales, and short-ships
GROUP BY
    $$||
    _clist_group
INTO
    _ytdbody;

RAISE NOTICE E'_ytdbody \n%', _ytdbody;

------------------------------------pull a plug from actuals to create a full year baseline------------------

SELECT
$$SELECT
    $$||_clist_inc||
$$
FROM
    $$||_target_table||' o'||E'\n    '||_perd_joins||$$
WHERE
    $$||format('%I',_order_date)||$$ BETWEEN 'app_plug_fromdate'::date AND 'app_plug_todate'::date
    --be sure to pre-exclude unwanted items, like canceled orders, non-gross sales, and short-ships
GROUP BY
    $$||_clist_group
INTO
    _actpy;

RAISE NOTICE E'plug body --------> \n%',_actpy;
------------------------------copy a full year and increment by 1 year for the baseline-------------------------

SELECT
--$$INSERT INTO 
--    fc.live
$$,incr AS (
SELECT
    $$||_clist_inc||$$
FROM
    baseline o$$||E'\n'||_perd_joins||$$
GROUP BY
    $$||_clist_group||$$
)
,ins AS (
INSERT INTO
    $$||_target_table||$$
SELECT
    *
FROM
    incr i
WHERE
    i.$$||format('%I',_order_date)||$$ >= 'app_first_forecast_date'::date$$||$$
    OR i.$$||format('%I',_ship_date)||$$ >= 'app_first_forecast_date'::date$$||$$
RETURNING *
)
SELECT COUNT(*) num_rows  FROM ins$$
    --any orders in the forecast period, or any sales in the forecast period (from open orders)
INTO
    _baseline;
    

RAISE NOTICE E'_baseline -------> \n%',_baseline;
------------------------------stack the sql into the final format------------------------------------------------

SELECT
$$DELETE FROM $$||_target_table||$$ WHERE $$||_version_col||$$ = 'app_forecast_name';
WITH
req AS  (SELECT $$||'$$app_req$$::jsonb j)'||$$
,ilog AS (
    INSERT INTO
        fc.log (doc)
    SELECT
        req.j 
    FROM
        req
    RETURNING *
)
,baseline AS (
$$||_ytdbody||$$
UNION ALL
$$||_actpy
||$$)
$$||_baseline
INTO
    _sql;

RAISE NOTICE E'_sql ---------> \n%',_sql;

INSERT INTO fc.sql SELECT _target_table, 'baseline', _sql ON CONFLICT ON CONSTRAINT sql_pkey  DO UPDATE SET t = EXCLUDED.t;

END
$func$;

---SELECT * FROM fc.sql;