vtorio/prisma/migrations/20250722041246_add_cintas_calculate_summary_procedure/migration.sql

-- Create the cintas_calculate_summary stored procedure
CREATE OR REPLACE PROCEDURE cintas_calculate_summary(IN provided_import_id bigint)
    LANGUAGE plpgsql
AS
$$
BEGIN
    CREATE TEMP TABLE weekSums
    (
        week       int          not null,
        amount     decimal      not null,
        amountType varchar(100) not null
    );

    -- current week
    insert into weekSums (week, amount, amountType)
    select aw.week,
           sum(aw.week_sum),
           'trr4wk'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date else null end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' then 99 + 22
                   else null end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 else null end,
               case when aw.week > 2 then aw.week + 2 else null end,
               case when aw.week > 3 then aw.week + 3 else null end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i.import_id = provided_import_id
      and rtrim(opportunity_status) <> ''
      and w.week is not null
    group by aw.week
    ;

    -- week - 1
    insert into weekSums (week, amount, amountType)
    select ow.week_minus_1,
           sum(w.paper_chem_wk2),
           'week-1'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date else null end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' then 99 + 22
                   else null end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 else null end,
               case when aw.week > 2 then aw.week + 2 else null end,
               case when aw.week > 3 then aw.week + 3 else null end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i.import_id = provided_import_id
      and rtrim(opportunity_status) <> ''
      and ow.week_minus_1 is not null
      and aw.week <> 99
    group by ow.week_minus_1
    ;

    -- week - 2
    insert into weekSums (week, amount, amountType)
    select ow.week_minus_2,
           sum(w.paper_chem_wk3),
           'week-2'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date else null end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' then 99 + 22
                   else null end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 else null end,
               case when aw.week > 2 then aw.week + 2 else null end,
               case when aw.week > 3 then aw.week + 3 else null end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i.import_id = provided_import_id
      and rtrim(opportunity_status) <> ''
      and ow.week_minus_2 is not null
      and aw.week <> 99
    group by ow.week_minus_2
    ;

    -- week - 3
    insert into weekSums (week, amount, amountType)
    select ow.week_minus_3,
           sum(w.paper_chem_wk4),
           'week-3'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date else null end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' then 99 + 22
                   else null end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 else null end,
               case when aw.week > 2 then aw.week + 2 else null end,
               case when aw.week > 3 then aw.week + 3 else null end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i.import_id = provided_import_id
      and rtrim(opportunity_status) <> ''
      and ow.week_minus_3 is not null
      and aw.week <> 99
    group by ow.week_minus_3
    ;

    -- power adds
    insert into weekSums (week, amount, amountType)
    select aw.week,
           sum(w.power_add),
           'poweradd'
    from cintas_install_calendar i
             inner join lateral (
        select case when i.install_date <> '' then i.install_date::date else null end,
               case
                   when i.install_date <> '' then date_part('week', i.install_date::date)
                   when i.opportunity_status = 'Pending - Install Not Scheduled' then 99 + 22
                   else null end,
               case when i.trr <> '' then i.trr::decimal else 0 end,
               case when i.paper_chem_wk1 <> '' then i.paper_chem_wk1::decimal else 0 end,
               case when i.paper_chem_wk2 <> '' then i.paper_chem_wk2::decimal else 0 end,
               case when i.paper_chem_wk3 <> '' then i.paper_chem_wk3::decimal else 0 end,
               case when i.paper_chem_wk4 <> '' then i.paper_chem_wk4::decimal else 0 end,
               case when i.power_add <> '' then i.power_add::decimal else 0 end
        ) w (install_date, week, trr, paper_chem_wk1, paper_chem_wk2, paper_chem_wk3, paper_chem_wk4, power_add) on true
             inner join lateral (
        select case when w.week is null then null when w.week >= 23 then w.week - 22 else w.week + 30 end,
               w.trr + w.paper_chem_wk1
        ) aw (week, week_sum) on true
             inner join lateral (
        select case when aw.week > 1 then aw.week + 1 else null end,
               case when aw.week > 2 then aw.week + 2 else null end,
               case when aw.week > 3 then aw.week + 3 else null end
        ) ow (week_minus_1, week_minus_2, week_minus_3) on true
    where i.import_id = provided_import_id
      and rtrim(opportunity_status) <> ''
      and aw.week is not null
      and aw.week <> 99
    group by aw.week
    ;

    insert into cintas_intall_calendar_summary (import_id, week, trr_total, fourwk_averages, trr_plus_4wk, power_adds, week_id)
    select distinct
        provided_import_id,
        case when t.week = 99 then 'Pending - Install Not Scheduled' else concat('Week ', t.week::varchar(100)) end "Week",
        cast(sum(case when t.amountType <> 'poweradd' and t.amountType = 'trr4wk' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "TRR Total",
        cast(sum(case when t.amountType <> 'poweradd' and t.amountType <> 'trr4wk' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "4WK Averages",
        cast(sum(case when t.amountType <> 'poweradd' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "TRR + 4WK",
        cast(sum(case when t.amountType = 'poweradd' then t.amount else 0 end) over (partition by t.week) as decimal(9,0)) "Power Adds",
        t.week "WeekId"
    from weekSums t
    order by t.week desc
    ;

    drop table weekSums;
END;
$$;