gistfile1.txt

create or replace function subs_renewals(date) returns setof renewals_member as $$
DECLARE
    r RECORD;
    now boolean;
    mem renewals_member;
    renewed bool;
    churned bool;
    mon date;
    canceled date;
    renewal_date date;
    dunning boolean;
    waiting boolean;
BEGIN
    for r in select c.user_id,
                    u.first_name,
                    u.last_name,
                    u.payment_plan_id,
                    u.deleted,
                    u.cohort,
                    u.cohort_date,
                    s.current_period_start,
                    s.current_period_end,
                    s.canceled_at,
                    s.state,
                    u.date_joined,
                    u.date_canceled,
                    u.cancel_end_of_period
                    from fnstripe_subscription s left join
                         fnstripe_customer c on c.id = s.customer_id left join
                         fnsite_userprofile u on u.id = c.user_id
                    where
                          (cohort_date = $1 - interval '1 year' or
                          cohort_date = $1 - interval '2 year' or
                          cohort_date = $1 - interval '3 year' or
                          cohort_date = $1 - interval '4 year' or
                          cohort_date = $1 - interval '5 year' or
                          cohort_date = $1 - interval '6 year' or
                          cohort_date = $1 - interval '7 year' or
                          cohort_date = $1 - interval '8 year' or
                          cohort_date = $1 - interval '9 year')
                          and u.payment_plan_id in (1,2,3,4,5,6)
    loop


        if r.date_joined > $1 - interval '30 days' and r.date_joined < $1 then
            continue;
        end if;

        if r.state = 'canceled' and not r.deleted then
            -- there will be more subscriptions
            continue;
        end if;

        if $1 > r.canceled_at + interval '11 months' then
            continue;
        end if;

        now := false;
        if $1 > r.current_period_start - interval '15 days'
              and $1 < r.current_period_start + interval '15 days' then
           now := true;
        end if;

        
        renewed := false;
        churned := true;
        dunning  := false;
        waiting := false;
        mon := $1;

        canceled := r.date_canceled;
        if canceled is null then
           canceled := r.canceled_at;
        end if;
        

        if r.state = 'active' or r.state = 'trialing' then
            renewed := true;
            churned := false;
            -- check upgraded to annual!
            if r.payment_plan_id in (2,4,6) then
                if not now then
                   if r.current_period_end <= mon + interval '1 month' then
                      mon := replace_year(date(date_trunc('month',r.current_period_start)), mon);
                   end if;
                end if;
            end if;
        end if;
        if r.state = 'past_due' then
           if now then
               renewed := false;
               churned := false;
           else
               renewed := true;
               churned := false;
           end if;
        end if;
        
        if r.state = 'canceled' then
             if (r.canceled_at > $1  - interval '11 months') and r.canceled_at < $1  + interval '1 month' then
               renewed := false;
               churned := true;
               mon := date_trunc('month',canceled);--r.canceled_at
            else
               renewed := true;
               churned := false;
            end if;
        end if;

        renewal_date := replace_year(date(r.current_period_start), mon);
        if r.payment_plan_id in (1,3,5) then
           renewal_date := replace_month(renewal_date, mon);
        end if;

        if not renewed and not churned then
           if r.state = 'past_due' then
              dunning := true;
           else
              waiting := true;
           end if;
        end if;

        select r.user_id,
               r.first_name || ' ' || r.last_name,
               mon,
               renewal_date,
               canceled,
               0,
               0,
               0,
               0,
               0,
               r.payment_plan_id,
               renewed,
               churned,
               waiting,
               dunning,
               r.state,
               'stripe',
               r.cohort,
               r.cancel_end_of_period
        into mem;
        mem := fill_renewals_member_data($1, mem);

        return next mem;

    end loop;


    for r in select u.id,
                    u.first_name,
                    u.last_name,
                    u.payment_plan_id,
                    u.deleted,
                    u.cohort,
                    u.cohort_date,                    
                    s.current_period_started_at,
                    s.current_period_ends_at,
                    s.canceled_at,
                    s.state,
                    u.date_joined,
                    u.date_canceled,
                    u.cancel_end_of_period
                    from chargify_subscription s left join
                         chargify_customer c on c.id = s.customer_id left join
                         chargify_product cp on cp.id = s.product_id left join
                         fnsite_userprofile u on u.user_id = c.user_id
                    where (cohort_date = $1 - interval '1 year' or
                               cohort_date = $1 - interval '2 year' or
                               cohort_date = $1 - interval '3 year' or
                               cohort_date = $1 - interval '4 year' or
                               cohort_date = $1 - interval '5 year' or
                               cohort_date = $1 - interval '6 year' or
                               cohort_date = $1 - interval '7 year' or
                               cohort_date = $1 - interval '8 year' or
                               cohort_date = $1 - interval '9 year')
                          and not
                              --exists (select 1 from fnstripe_customer where user_id=u.id)
                              exists(select 1 from fnstripe_subscription fns
                                            left join fnstripe_customer fnc on fns.customer_id=fnc.id
                                                 where fnc.user_id=u.id)
                          and (lower(cp.handle) like '%month%' or lower(cp.handle) like '%annual%'
                              or lower(cp.handle) like '%passive%')
                          and u.payment_plan_id in (1,2,3,4,5,6)
    loop

        if r.date_joined > $1 - interval '30 days' and r.date_joined < $1 then
            continue;
        end if;



        if $1 > r.canceled_at + interval '11 months' then
            continue;
        end if;

        if $1 > r.date_canceled + interval '11 months' then
            continue;
        end if;

        now := false;
        if $1 > r.current_period_started_at - interval '15 days'
              and $1 < r.current_period_started_at + interval '15 days' then
           now := true;
        end if;

        

        renewed := false;
        churned := true;
        waiting := false;
        dunning  := false;
        mon := $1;
        
        canceled := r.canceled_at;
        if canceled is null then
           canceled := r.date_canceled;
        end if;
        
        if r.state = 'active' or r.state = 'trialing' then
            renewed := true;
            churned := false;
            -- check upgraded to annual!
            if r.payment_plan_id in (2,4,6) then
                if not now then
                   mon := replace_year(date(date_trunc('month',r.current_period_started_at)), mon);
                end if;
            end if;
        end if;
        if r.state = 'past_due' then
            if now  then
               renewed := false;
               churned := false;
            else
               renewed := true;
               churned := false;
            end if;
        end if;
        if r.state = 'canceled' then            
            if (r.canceled_at > $1  - interval '11 months') and (r.canceled_at < $1  + interval '1 month') then
            -- if r.canceled_at < $1  + interval '1 year' then
              renewed := false;
              churned := true;
              mon := date_trunc('month',r.canceled_at);
           else
               renewed := true;
               churned := false;
            end if;
        end if;
        if r.state = 'expired' or r.state = 'unpaid' then            
             if (r.canceled_at > $1  - interval '11 months') and (r.canceled_at < $1  + interval '1 month') then
            -- if r.canceled_at < $1  + interval '1 year' then           
              renewed := false;
              churned := true;
              mon := date_trunc('month',r.date_canceled);
           else
               renewed := true;
               churned := false;
            end if;
            
        end if;


        renewal_date := replace_year(date(r.current_period_started_at), mon);
        if r.payment_plan_id in (1,3,5) then
           renewal_date := replace_month(renewal_date, mon);
        end if;


       if not renewed and not churned then
           if r.state = 'past_due' then
              dunning := true;
           else
              waiting := true;
           end if;
        end if;

        select r.id,
               r.first_name || ' ' || r.last_name,
               mon,
               renewal_date,
               canceled,    
               0,
               0,
               0,
               0,
               0,
               r.payment_plan_id,
               renewed,
               churned,
               waiting,
               dunning,
               r.state,
               'chargify',
               r.cohort,
               r.cancel_end_of_period
        into mem;
        mem := fill_renewals_member_data($1, mem);

        return next mem;

    end loop;

END;
$$ LANGUAGE PlPgSQL;