OpenACS

jeffd
committed
oacs-5-2:jeffd:20050708130916
on 08 Jul 05
doh forgot to init err1
/tcl/acs-mail-lite-procs-postgresql.xql (+6 -3)
1 1 <?xml version="1.0"?>
2 2
3 3 <queryset>
4 4     <rdbms><type>postgresql</type><version>7.1</version></rdbms>
5 5
6 6    <fullquery name="acs_mail_lite::check_bounces.send_notification_to_bouncing_email">
7 7      <querytext>
8 8
9 9        insert into acs_mail_lite_bounce_notif (user_id, notification_count, notification_time)
10 10        select user_id, 0 as notification_count,
11              date_trunc('day', current_timestamp - to_interval(1 + :notification_interval, 'days'))
  11            date_trunc('day', current_timestamp - ( (1 +
  12        :notification_interval || 'days') :: interval ) )
12 13            as notification_time
13 14         from acs_mail_lite_bounce
14 15         where bounce_count >= :max_bounce_count
15 16
16 17      </querytext>
17 18    </fullquery>
18 19
19 20    <fullquery name="acs_mail_lite::check_bounces.get_recent_bouncing_users">
20 21      <querytext>
21 22
22 23        select u.user_id, u.email, u.first_names || ' ' || u.last_name as name
23 24        from cc_users u, acs_mail_lite_bounce_notif n
24 25        where u.user_id = n.user_id
25 26        and u.email_bouncing_p = 't'
26          and n.notification_time < current_timestamp - to_interval(:notification_interval, 'days')
  27        and n.notification_time < current_timestamp -
  28        ((:notification_interval || 'days') :: interval)
27 29        and n.notification_count < :max_notification_count
28 30
29 31      </querytext>
30 32    </fullquery>
31 33
32 34    <fullquery name="acs_mail_lite::check_bounces.log_notication_sending">
33 35      <querytext>
34 36
35 37        update acs_mail_lite_bounce_notif
36 38        set notification_time = date_trunc('day',current_timestamp),
37 39            notification_count = notification_count + 1
38 40        where user_id = :user_id
39 41
40 42      </querytext>
41 43    </fullquery>
42 44
43 45    <fullquery name="acs_mail_lite::check_bounces.delete_log_if_no_recent_bounce">
44 46      <querytext>
45 47
46 48        delete from acs_mail_lite_bounce
47 49        where user_id in (select user_id
48 50                          from acs_mail_lite_mail_log
49                            where last_mail_date < current_timestamp - to_interval(:max_days_to_bounce, 'days'))
  51                          where last_mail_date < current_timestamp -
  52        ((:max_days_to_bounce || 'days') :: interval))
50 53
51 54      </querytext>
52 55    </fullquery>
53 56
54 57
55 58     <fullquery name="acs_mail_lite::send.create_queue_entry">
56 59         <querytext>
57 60             insert into acs_mail_lite_queue
58 61             (message_id, to_addr, from_addr, subject, body, extra_headers, bcc, package_id, valid_email_p)
59 62             values
60 63             (nextval('acs_mail_lite_id_seq'), :to_addr, :from_addr, :subject, :body, :eh_list, :bcc, :package_id,
61 64             (case when :valid_email_p = '1' then TRUE else FALSE end))
62 65         </querytext>
63 66     </fullquery>
64 67
65 68
66 69    <fullquery name="acs_mail_lite::log_mail_sending.record_mail_sent">
67 70      <querytext>
68 71
69 72        update acs_mail_lite_mail_log