--------------------------------------
-- Initial efforts only
--------------------------------------
insert into renewals.extractions (publication, promotional_code, description)
    values (204, 'R082', 'Spring 2008 Expires') returning id;

insert into renewals.extraction_details
    (extraction, subscriber, subscriber_order, expiration_date)
  select currval('renewals.extractions_id_seq'::regclass), 
     s.id, current_subscriber_order, 
     publications.get_expiration_issue_date(s.publication,
                s.issues_remaining, s.last_issue_received)
  from subscribers s, subscriber_orders o
  where s.publication = 204 and 
     publications.get_expiration_issue_date(s.publication,
             s.issues_remaining, s.last_issue_received) = '2008-04-01' and
       s.status in ('OK','EXP','HOLD') and 
       s.can_mail = 'Y' and 
       s.renewal_series = 'DEFAULT' and 
       o.id = s.current_subscriber_order and 
       o.renewal_closed is null and 
       not exists (select 1 from renewals.extraction_details 
           where subscriber_order = current_subscriber_order);


--------------------------------------
-- All efforts
--------------------------------------
insert into renewals.extraction_efforts
    (extraction, effort)
     select extraction, coalesce(max(effort),0) + 1
         from renewals.extraction_efforts
         where extraction = 108
         group by extraction
     union all
         select 108, 1 
         where not exists
            (select 1 from renewals.extraction_efforts
                where extraction = 108);



insert into renewals.subscriber_efforts (
        subscriber_order, extraction, 
        publication, promotional_code, expiration_date, 
        subscriber_status, extraction_effort)
    select d.subscriber_order, d.extraction, s.publication, 
        'RENEWAL', 
        publications.get_expiration_issue_date(s.publication,
                                               s.issues_remaining,
                                               s.last_issue_received),
        s.STATUS, 
        (select max(effort) from renewals.extraction_efforts
            where extraction = 108)
    from subscribers s, renewals.extraction_details d, subscriber_orders so
    where so.id = d.subscriber_order AND 
      s.id = so.subscriber AND 
      so.renewal_closed IS NULL AND 
      s.status not in ('CANC') AND 
      s.current_subscriber_order = so.id AND 
      d.extraction = 108;

update renewals.subscriber_efforts 
    set master_effort = id 
    where master_effort is null and 
      extraction = 108;
      
update renewals.subscriber_efforts
    set offer_set = (
        select max(id) from renewals.offer_sets
        where active and publication = subscriber_efforts.publication)
    where extraction = 108 and 
        offer_set is null;

-----------------------------------------------------
-- VQR/Y'all Gift-giver aggregation
-----------------------------------------------------

update renewals.subscriber_efforts set aggregate_method = 'B', 
  master_effort = 
     (select min(e.id) 
       from renewals.subscriber_efforts e,
            subscribers s, 
            subscriber_orders so, 
            addresses a
      where
         e.publication = 2 and 
         e.print_session is null and 
         e.extraction_effort in (1,3,4) and 
         so.id = e.subscriber_order and 
         s.id = so.subscriber and
         a.id = s.bill_address and 
         (a.address1, a.address2, a.city, 
          a.state, a.country, a.postal_code) = 
         (select a2.address1, a2.address2, a2.city, 
          a2.state, a2.country, a2.postal_code
          from 
             renewals.subscriber_efforts r2, 
             subscribers s2, 
             subscriber_orders so2, 
             addresses a2
          where
             r2.id = subscriber_efforts.id and 
             so2.id = r2.subscriber_order and
             s2.id = so2.subscriber and
             a2.id = s2.bill_address))
     where id in (
       select e3.id 
       from renewals.subscriber_efforts e3,
            subscriber_orders so3, 
            subscribers s3, 
            addresses a3
      where
         e3.publication = 2 and
         e3.print_session is null and
         e3.extraction_effort in (1,3,4) and
         so3.id = e3.subscriber_order and
         s3.id = so3.subscriber and
         a3.id = s3.bill_address and
         (a3.address1, a3.address2, a3.city, 
          a3.state, a3.country, a3.postal_code) in 
         (select a4.address1, a4.address2, a4.city, 
          a4.state, a4.country, a4.postal_code
               from renewals.subscriber_efforts e4,
                    subscriber_orders so4,
                    subscribers s4,
                    addresses a4
              where
                 e4.publication = 2 and 
                 e4.print_session is null and 
                 e4.extraction_effort in (1,3,4) and
                 so4.id = e4.subscriber_order and 
                 s4.id = so4.subscriber and
                 a4.id = s4.bill_address
              group by a4.address1, a4.address2, a4.city, 
                 a4.state, a4.country, a4.postal_code
              having count(*) > 1));



`
-----------------------------------------------------
-- Mailing -- standard
-----------------------------------------------------
insert into mailing.mail_sessions (mail_class, permit) values ('STDLTR',1) returning id;

insert into printing.print_sessions  (report, source, mail_session) values (5,
            'RENEW', currval('mailing.mail_session_seq')) returning id;

update renewals.subscriber_efforts
    set print_session = currval('printing.print_session_seq')
    where extraction = 108 and
        print_session is null;

commit;




-----------------------------------------------------
-- Mailing -- Missouri Review
-----------------------------------------------------
insert into mailing.mail_sessions (mail_class, permit) values ('STDLTR',3) returning id;

insert into printing.print_sessions  (report, source, mail_session) values (5,
            'RENEW', currval('mailing.mail_session_seq')) returning id;

update renewals.subscriber_efforts
    set print_session = currval('printing.print_session_seq')
    where publication = 197 and
        print_session is null;

commit;


-----------------------------------------------------
-- Mailing -- VQR
-----------------------------------------------------
insert into mailing.mail_sessions (mail_class, permit) values ('STDLTR',2) returning id;

insert into printing.print_sessions  (report, source, mail_session) values (5,
            'RENEW', currval('mailing.mail_session_seq')) returning id;

update renewals.subscriber_efforts
    set print_session = currval('printing.print_session_seq')
    where publication = 204 and
        print_session is null;

commit;