--------------------------------------
-- 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;