authen.public

Parameters

Name	Type	Mode
prop_id	integer	IN
batch_data	jsonb	IN

Definition

DECLARE
    batch record;
    item record;
    _batch_id int;
    _item_id int;
    active_items int[] = '{}';
BEGIN 
      
    SELECT * FROM jsonb_to_record(batch_data) 
    AS (id int, batch_name TEXT, batch_type TEXT, output_type TEXT, output_target TEXT, enabled bool) INTO batch;

    IF (batch.id IS NULL OR batch.id < 1) THEN 
        INSERT INTO batch_report(prop_id, batch_name, batch_type, output_type, output_target, enabled)
        VALUES ($1, batch.batch_name, batch.batch_type, batch.output_type, batch.output_target, batch.enabled)
        RETURNING id INTO _batch_id;
    ELSE 
        UPDATE batch_report b 
        SET batch_name    = coalesce(batch.batch_name, b.batch_name), 
            batch_type    = coalesce(batch.batch_type, b.batch_type),
            output_type   = coalesce(batch.output_type, b.output_type), 
            output_target = coalesce(batch.output_target, b.output_target), 
            enabled       = coalesce(batch.enabled, b.enabled)
        WHERE b.id = batch.id;
        _batch_id := batch.id;
    END IF;

    --DELETE FROM batch_report_items WHERE batch_id = batch.id;
    
    FOR item IN 
        with items as (
            SELECT * FROM jsonb_to_recordset(batch_data->'financial_reports') 
            AS i(report_id int, report_name text, item_id int, option_values jsonb, enabled bool, duplicated bool) --WHERE i.enabled 
            UNION ALL 
            SELECT * FROM jsonb_to_recordset(batch_data->'reservation_reports')
            AS i(report_id int, report_name text, item_id int, option_values jsonb, enabled bool, duplicated bool) --WHERE i.enabled   
            UNION ALL 
            SELECT * FROM jsonb_to_recordset(batch_data->'registration_reports')
            AS i(report_id int, report_name text, item_id int, option_values jsonb, enabled bool, duplicated bool) --WHERE i.enabled  
            UNION ALL 
            SELECT * FROM jsonb_to_recordset(batch_data->'marketing_reports') 
            AS i(report_id int, report_name text, item_id int, option_values jsonb, enabled bool, duplicated bool) --WHERE i.enabled 
        )
        select i.*, 
            case when (r.report_name <> i.report_name) then i.report_name  else null end as report_alias
        from items i left join reports r on r.id = i.report_id
    LOOP
        IF (item.item_id IS NULL OR item.item_id < 1 
            OR NOT EXISTS(SELECT 1 FROM batch_report_items WHERE batch_id = _batch_id AND item_id = item.item_id)) 
        THEN 
            IF item.enabled THEN
                INSERT INTO batch_report_items (batch_id, report_id, report_alias, option_values, duplicated)
                VALUES(_batch_id, item.report_id, item.report_alias, item.option_values, COALESCE(item.duplicated,FALSE))
                RETURNING item_id INTO _item_id; 
            END IF;
        ELSE 
            UPDATE batch_report_items bi
            SET report_id     = item.report_id, 
                report_alias  = COALESCE(item.report_alias, bi.report_alias),
                option_values = COALESCE(item.option_values, bi.option_values), 
                duplicated    = COALESCE(item.duplicated,FALSE),
                enabled       = COALESCE(item.enabled, bi.enabled)
            WHERE item_id = item.item_id;
            _item_id := item.item_id;
        END IF;
        active_items := active_items || _item_id;
    END LOOP;   

    --DELETE FROM batch_report_items WHERE batch_id = batch.id AND item_id <> ALL(active_items);

    RETURN fn_result_success();
END

sp_save_batch_report

Parameters

Definition