postgresql7.1
insert into acs_mail_lite_bounce_notif (user_id, notification_count, notification_time)
select party_id, 0 as notification_count,
date_trunc('day', current_timestamp - to_interval(1 + :notification_interval, 'days'))
as notification_time
from acs_mail_lite_bounce
where bounce_count >= :max_bounce_count
select u.user_id, u.email, u.first_names || ' ' || u.last_name as name
from cc_users u, acs_mail_lite_bounce_notif n
where u.user_id = n.user_id
and u.email_bouncing_p = 't'
and n.notification_time < current_timestamp - to_interval(:notification_interval, 'days')
and n.notification_count < :max_notification_count
update acs_mail_lite_bounce_notif
set notification_time = date_trunc('day',current_timestamp),
notification_count = notification_count + 1
where user_id = :user_id
delete from acs_mail_lite_bounce
where party_id in (select party_id
from acs_mail_lite_mail_log
where last_mail_date < current_timestamp - to_interval(:max_days_to_bounce, 'days'))
insert into acs_mail_lite_queue
(message_id, to_addr, from_addr, subject, body, extra_headers, bcc, package_id, valid_email_p)
values
(nextval('acs_mail_lite_id_seq'), :to_addr, :from_addr, :subject, :body, :eh_list, :bcc, :package_id,
(case when :valid_email_p = '1' then TRUE else FALSE end))
update acs_mail_lite_mail_log
set last_mail_date = current_timestamp
where party_id = :user_id
insert into acs_mail_lite_mail_log (party_id, last_mail_date)
values (:user_id, current_timestamp)
select message_id,
to_addr,
from_addr,
subject,
body,
extra_headers,
bcc,
package_id,
(case when valid_email_p = TRUE then 1
else 0
end) as valid_email_p
from acs_mail_lite_queue
insert into acs_mail_lite_queue
(message_id, to_addr, from_addr, subject, body, extra_headers, bcc,
package_id, valid_email_p)
values
(nextval('acs_mail_lite_id_seq'), :to_addr, :from_addr, :subject, :body,
:extraheaders, :bcc, :package_id,
(case when :valid_email_p = '1' then TRUE
else FALSE end))