Fossil SCM

Automatic messages requesting user renewal are now generated, about once every 7 days. This needs more testing before going live.

drh 2021-05-31 18:58 time-limited-subscriptions
Commit 01ee25cf58aa6076eb74b8a0a159c73b71287b65df2283654115a949b4028ced
Parent 11b7c7699f89b8a…
1 file changed +126 -3
~ src/alerts.c
M src/alerts.c
+126 -3
--- src/alerts.c
+++ src/alerts.c
@@ -1061,10 +1061,12 @@
10611061
 ** a cron-job to make sure alerts are sent
10621062
 ** in a timely manner.
10631063
 ** Options:
10641064
 **
10651065
 ** --digest Send digests
1066
+** --renewal Send subscription renewal
1067
+** notices
10661068
 ** --test Write to standard output
10671069
 **
10681070
 ** settings [NAME VALUE] With no arguments, list all email settings.
10691071
 ** Or change the value of a single email setting.
10701072
 **
@@ -1138,10 +1140,11 @@
11381140
 }
11391141
 }else
11401142
 if( strncmp(zCmd, "send", nCmd)==0 ){
11411143
 u32 eFlags = 0;
11421144
 if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
1145
+ if( find_option("renewal",0,0)!=0 ) eFlags |= SENDALERT_RENEWAL;
11431146
 if( find_option("test",0,0)!=0 ){
11441147
 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
11451148
 }
11461149
 verify_all_options();
11471150
 alert_send_alerts(eFlags);
@@ -1167,10 +1170,12 @@
11671170
 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
11681171
 print_setting(pSetting);
11691172
 }
11701173
 }else
11711174
 if( strncmp(zCmd, "status", nCmd)==0 ){
1175
+ Stmt q;
1176
+ int iCutoff;
11721177
 int nSetting, n;
11731178
 static const char *zFmt = "%-29s %d\n";
11741179
 const Setting *pSetting = setting_info(&nSetting);
11751180
 db_open_config(1, 0);
11761181
 verify_all_options();
@@ -1182,14 +1187,32 @@
11821187
 }
11831188
 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
11841189
 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
11851190
 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
11861191
 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
1192
+ db_prepare(&q,
1193
+ "SELECT"
1194
+ " name,"
1195
+ " value,"
1196
+ " now()/86400-value,"
1197
+ " date(value*86400,'unixepoch')"
1198
+ " FROM repository.config"
1199
+ " WHERE name in ('email-renew-warning','email-renew-cutoff');");
1200
+ while( db_step(&q)==SQLITE_ROW ){
1201
+ fossil_print("%-29s %-6d (%d days ago on %s)\n",
1202
+ db_column_text(&q, 0),
1203
+ db_column_int(&q, 1),
1204
+ db_column_int(&q, 2),
1205
+ db_column_text(&q, 3));
1206
+ }
1207
+ db_finalize(&q);
11871208
 n = db_int(0,"SELECT count(*) FROM subscriber");
11881209
 fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
1210
+ iCutoff = db_get_int("email-renew-cutoff", 0);
11891211
 n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
1190
- " AND NOT sdonotcall AND length(ssub)>1");
1212
+ " AND NOT sdonotcall AND length(ssub)>1"
1213
+ " AND lastContact>=%d", iCutoff);
11911214
 fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
11921215
 }else
11931216
 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
11941217
 Stmt q;
11951218
 verify_all_options();
@@ -2693,22 +2716,69 @@
26932716
 db_end_transaction(0);
26942717
 if( doAuto ){
26952718
 alert_backoffice(SENDALERT_TRACE|mFlags);
26962719
 }
26972720
 }
2721
+
2722
+/*
2723
+** Construct the header and body for an email message that will alert
2724
+** a subscriber that their subscriptions are about to expire.
2725
+*/
2726
+static void alert_renewal_msg(
2727
+ Blob *pHdr, /* Write email header here */
2728
+ Blob *pBody, /* Write email body here */
2729
+ const char *zCode, /* The subscriber code */
2730
+ int lastContact, /* Last contact (days since 1970) */
2731
+ const char *zEAddr, /* Subscriber email address. Send to this. */
2732
+ const char *zSub, /* Subscription codes */
2733
+ const char *zRepoName, /* Name of the sending Fossil repostory */
2734
+ const char *zUrl /* URL for the sending Fossil repostory */
2735
+){
2736
+ blob_appendf(pHdr,"To: <%s>\r\n", zEAddr);
2737
+ blob_appendf(pHdr,"Subject: %s Subscription to %s expires soon\r\n",
2738
+ zRepoName, zUrl);
2739
+ blob_appendf(pBody,
2740
+ "You are currently receiving email notification of the following kinds\n"
2741
+ "of changes to the %s Fossil repository at %s:\n\n",
2742
+ zRepoName, zUrl
2743
+ );
2744
+ if( strchr(zSub, 'a') ) blob_appendf(pBody, " * Announcements\n");
2745
+ if( strchr(zSub, 'c') ) blob_appendf(pBody, " * Check-ins\n");
2746
+ if( strchr(zSub, 'f') ) blob_appendf(pBody, " * Forum posts\n");
2747
+ if( strchr(zSub, 't') ) blob_appendf(pBody, " * Ticket changes\n");
2748
+ if( strchr(zSub, 'w') ) blob_appendf(pBody, " * Wiki changes\n");
2749
+ blob_appendf(pBody,
2750
+ "\nTo continue receiving email notifications, click the following link\n"
2751
+ "\n %s/renew/%s\n\n",
2752
+ zUrl, zCode
2753
+ );
2754
+ blob_appendf(pBody,
2755
+ "If you take no action, your subscription will expire and you will be\n"
2756
+ "unsubscribed in about a week. To make other changes or to unsubscribe\n"
2757
+ "immediately, visit the following webpage:\n\n"
2758
+ " %s/alerts/%s\n\n",
2759
+ zUrl, zCode
2760
+ );
2761
+}
26982762
26992763
 #if INTERFACE
27002764
 /*
27012765
 ** Flags for alert_send_alerts()
27022766
 */
27032767
 #define SENDALERT_DIGEST 0x0001 /* Send a digest */
27042768
 #define SENDALERT_PRESERVE 0x0002 /* Do not mark the task as done */
27052769
 #define SENDALERT_STDOUT 0x0004 /* Print emails instead of sending */
27062770
 #define SENDALERT_TRACE 0x0008 /* Trace operation for debugging */
2771
+#define SENDALERT_RENEWAL 0x0010 /* Send renewal notices */
27072772
27082773
 #endif /* INTERFACE */
27092774
2775
+/*
2776
+** Minimum number of days between renewal messages
2777
+*/
2778
+#define ALERT_RENEWAL_MSG_FREQUENCY 7 /* Do renewals at most once/week */
2779
+
27102780
 /*
27112781
 ** Send alert emails to subscribers.
27122782
 **
27132783
 ** This procedure is run by either the backoffice, or in response to the
27142784
 ** "fossil alerts send" command. Details of operation are controlled by
@@ -2752,10 +2822,11 @@
27522822
 const char *zRepoName;
27532823
 const char *zFrom;
27542824
 const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
27552825
 AlertSender *pSender = 0;
27562826
 u32 senderFlags = 0;
2827
+ int iInterval = 0; /* Subscription renewal interval */
27572828
27582829
 if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
27592830
 alert_schema(0);
27602831
 if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
27612832
 zUrl = db_get("email-url",0);
@@ -2801,11 +2872,11 @@
28012872
28022873
 /* Step 2: compute EmailEvent objects for every notification that
28032874
 ** needs sending.
28042875
 */
28052876
 pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
2806
- if( nEvent==0 ) goto send_alert_done;
2877
+ if( nEvent==0 ) goto send_alert_expiration_warnings;
28072878
28082879
 /* Step 4a: Update the pending_alerts table to designate the
28092880
 ** alerts as having all been sent. This is done *before* step (3)
28102881
 ** so that a crash will not cause alerts to be sent multiple times.
28112882
 ** Better a missed alert than being spammed with hundreds of alerts
@@ -2929,10 +3000,62 @@
29293000
29303001
 /* Step 4b: Update the pending_alerts table to remove all of the
29313002
 ** alerts that have been completely sent.
29323003
 */
29333004
 db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");
3005
+
3006
+ /* Send renewal messages to subscribers whose subscriptions are about
3007
+ ** to expire. Only do this if:
3008
+ **
3009
+ ** (1) email-renew-interval is 14 or greater (or in other words if
3010
+ ** subscription expiration is enabled).
3011
+ **
3012
+ ** (2) The SENDALERT_RENEWAL flag is set
3013
+ */
3014
+send_alert_expiration_warnings:
3015
+ if( (flags & SENDALERT_RENEWAL)!=0
3016
+ && (iInterval = db_get_int("email-renew-interval",0))>=14
3017
+ ){
3018
+ int iNow = (int)(time(0)/86400);
3019
+ int iOldWarn = db_get_int("email-renew-warning",0);
3020
+ int iNewWarn = iNow - iInterval + ALERT_RENEWAL_MSG_FREQUENCY;
3021
+ if( iNewWarn >= iOldWarn + ALERT_RENEWAL_MSG_FREQUENCY ){
3022
+ db_prepare(&q,
3023
+ "SELECT"
3024
+ " hex(subscriberCode)," /* 0 */
3025
+ " lastContact," /* 1 */
3026
+ " semail," /* 2 */
3027
+ " ssub" /* 3 */
3028
+ " FROM subscriber"
3029
+ " WHERE lastContact<=%d AND lastContact>%d"
3030
+ " AND NOT sdonotcall"
3031
+ " AND length(sdigest)>0",
3032
+ iNewWarn, iOldWarn
3033
+ );
3034
+ while( db_step(&q)==SQLITE_ROW ){
3035
+ Blob hdr, body;
3036
+ blob_init(&hdr, 0, 0);
3037
+ blob_init(&body, 0, 0);
3038
+ alert_renewal_msg(&hdr, &body,
3039
+ db_column_text(&q,0),
3040
+ db_column_int(&q,1),
3041
+ db_column_text(&q,2),
3042
+ db_column_text(&q,3),
3043
+ zRepoName, zUrl);
3044
+ alert_send(pSender,&hdr,&body,0);
3045
+ blob_reset(&hdr);
3046
+ blob_reset(&body);
3047
+ }
3048
+ db_finalize(&q);
3049
+ if( (flags & SENDALERT_PRESERVE)==0 ){
3050
+ if( iOldWarn>0 ){
3051
+ db_set_int("email-renew-cutoff", iOldWarn, 0);
3052
+ }
3053
+ db_set_int("email-renew-warning", iNewWarn, 0);
3054
+ }
3055
+ }
3056
+ }
29343057
29353058
 send_alert_done:
29363059
 alert_sender_free(pSender);
29373060
 if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
29383061
 return nSent;
@@ -2955,11 +3078,11 @@
29553078
 if( !alert_tables_exist() ) return 0;
29563079
 nSent = alert_send_alerts(mFlags);
29573080
 iJulianDay = db_int(0, "SELECT julianday('now')");
29583081
 if( iJulianDay>db_get_int("email-last-digest",0) ){
29593082
 db_set_int("email-last-digest",iJulianDay,0);
2960
- nSent += alert_send_alerts(SENDALERT_DIGEST|mFlags);
3083
+ nSent += alert_send_alerts(SENDALERT_DIGEST|SENDALERT_RENEWAL|mFlags);
29613084
 }
29623085
 return nSent;
29633086
 }
29643087
29653088
 /*
29663089
--- src/alerts.c
+++ src/alerts.c
@@ -1061,10 +1061,12 @@
1061 ** a cron-job to make sure alerts are sent
1062 ** in a timely manner.
1063 ** Options:
1064 **
1065 ** --digest Send digests
 
 
1066 ** --test Write to standard output
1067 **
1068 ** settings [NAME VALUE] With no arguments, list all email settings.
1069 ** Or change the value of a single email setting.
1070 **
@@ -1138,10 +1140,11 @@
1138 }
1139 }else
1140 if( strncmp(zCmd, "send", nCmd)==0 ){
1141 u32 eFlags = 0;
1142 if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
 
1143 if( find_option("test",0,0)!=0 ){
1144 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
1145 }
1146 verify_all_options();
1147 alert_send_alerts(eFlags);
@@ -1167,10 +1170,12 @@
1167 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
1168 print_setting(pSetting);
1169 }
1170 }else
1171 if( strncmp(zCmd, "status", nCmd)==0 ){
 
 
1172 int nSetting, n;
1173 static const char *zFmt = "%-29s %d\n";
1174 const Setting *pSetting = setting_info(&nSetting);
1175 db_open_config(1, 0);
1176 verify_all_options();
@@ -1182,14 +1187,32 @@
1182 }
1183 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
1184 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
1185 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
1186 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1187 n = db_int(0,"SELECT count(*) FROM subscriber");
1188 fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
 
1189 n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
1190 " AND NOT sdonotcall AND length(ssub)>1");
 
1191 fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
1192 }else
1193 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
1194 Stmt q;
1195 verify_all_options();
@@ -2693,22 +2716,69 @@
2693 db_end_transaction(0);
2694 if( doAuto ){
2695 alert_backoffice(SENDALERT_TRACE|mFlags);
2696 }
2697 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2698
2699 #if INTERFACE
2700 /*
2701 ** Flags for alert_send_alerts()
2702 */
2703 #define SENDALERT_DIGEST 0x0001 /* Send a digest */
2704 #define SENDALERT_PRESERVE 0x0002 /* Do not mark the task as done */
2705 #define SENDALERT_STDOUT 0x0004 /* Print emails instead of sending */
2706 #define SENDALERT_TRACE 0x0008 /* Trace operation for debugging */
 
2707
2708 #endif /* INTERFACE */
2709
 
 
 
 
 
2710 /*
2711 ** Send alert emails to subscribers.
2712 **
2713 ** This procedure is run by either the backoffice, or in response to the
2714 ** "fossil alerts send" command. Details of operation are controlled by
@@ -2752,10 +2822,11 @@
2752 const char *zRepoName;
2753 const char *zFrom;
2754 const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
2755 AlertSender *pSender = 0;
2756 u32 senderFlags = 0;
 
2757
2758 if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
2759 alert_schema(0);
2760 if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
2761 zUrl = db_get("email-url",0);
@@ -2801,11 +2872,11 @@
2801
2802 /* Step 2: compute EmailEvent objects for every notification that
2803 ** needs sending.
2804 */
2805 pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
2806 if( nEvent==0 ) goto send_alert_done;
2807
2808 /* Step 4a: Update the pending_alerts table to designate the
2809 ** alerts as having all been sent. This is done *before* step (3)
2810 ** so that a crash will not cause alerts to be sent multiple times.
2811 ** Better a missed alert than being spammed with hundreds of alerts
@@ -2929,10 +3000,62 @@
2929
2930 /* Step 4b: Update the pending_alerts table to remove all of the
2931 ** alerts that have been completely sent.
2932 */
2933 db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2934
2935 send_alert_done:
2936 alert_sender_free(pSender);
2937 if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
2938 return nSent;
@@ -2955,11 +3078,11 @@
2955 if( !alert_tables_exist() ) return 0;
2956 nSent = alert_send_alerts(mFlags);
2957 iJulianDay = db_int(0, "SELECT julianday('now')");
2958 if( iJulianDay>db_get_int("email-last-digest",0) ){
2959 db_set_int("email-last-digest",iJulianDay,0);
2960 nSent += alert_send_alerts(SENDALERT_DIGEST|mFlags);
2961 }
2962 return nSent;
2963 }
2964
2965 /*
2966
--- src/alerts.c
+++ src/alerts.c
@@ -1061,10 +1061,12 @@
1061 ** a cron-job to make sure alerts are sent
1062 ** in a timely manner.
1063 ** Options:
1064 **
1065 ** --digest Send digests
1066 ** --renewal Send subscription renewal
1067 ** notices
1068 ** --test Write to standard output
1069 **
1070 ** settings [NAME VALUE] With no arguments, list all email settings.
1071 ** Or change the value of a single email setting.
1072 **
@@ -1138,10 +1140,11 @@
1140 }
1141 }else
1142 if( strncmp(zCmd, "send", nCmd)==0 ){
1143 u32 eFlags = 0;
1144 if( find_option("digest",0,0)!=0 ) eFlags |= SENDALERT_DIGEST;
1145 if( find_option("renewal",0,0)!=0 ) eFlags |= SENDALERT_RENEWAL;
1146 if( find_option("test",0,0)!=0 ){
1147 eFlags |= SENDALERT_PRESERVE|SENDALERT_STDOUT;
1148 }
1149 verify_all_options();
1150 alert_send_alerts(eFlags);
@@ -1167,10 +1170,12 @@
1170 if( strncmp(pSetting->name,"email-",6)!=0 ) continue;
1171 print_setting(pSetting);
1172 }
1173 }else
1174 if( strncmp(zCmd, "status", nCmd)==0 ){
1175 Stmt q;
1176 int iCutoff;
1177 int nSetting, n;
1178 static const char *zFmt = "%-29s %d\n";
1179 const Setting *pSetting = setting_info(&nSetting);
1180 db_open_config(1, 0);
1181 verify_all_options();
@@ -1182,14 +1187,32 @@
1187 }
1188 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentSep");
1189 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-alerts", n);
1190 n = db_int(0,"SELECT count(*) FROM pending_alert WHERE NOT sentDigest");
1191 fossil_print(zFmt/*works-like:"%s%d"*/, "pending-digest-alerts", n);
1192 db_prepare(&q,
1193 "SELECT"
1194 " name,"
1195 " value,"
1196 " now()/86400-value,"
1197 " date(value*86400,'unixepoch')"
1198 " FROM repository.config"
1199 " WHERE name in ('email-renew-warning','email-renew-cutoff');");
1200 while( db_step(&q)==SQLITE_ROW ){
1201 fossil_print("%-29s %-6d (%d days ago on %s)\n",
1202 db_column_text(&q, 0),
1203 db_column_int(&q, 1),
1204 db_column_int(&q, 2),
1205 db_column_text(&q, 3));
1206 }
1207 db_finalize(&q);
1208 n = db_int(0,"SELECT count(*) FROM subscriber");
1209 fossil_print(zFmt/*works-like:"%s%d"*/, "total-subscribers", n);
1210 iCutoff = db_get_int("email-renew-cutoff", 0);
1211 n = db_int(0, "SELECT count(*) FROM subscriber WHERE sverified"
1212 " AND NOT sdonotcall AND length(ssub)>1"
1213 " AND lastContact>=%d", iCutoff);
1214 fossil_print(zFmt/*works-like:"%s%d"*/, "active-subscribers", n);
1215 }else
1216 if( strncmp(zCmd, "subscribers", nCmd)==0 ){
1217 Stmt q;
1218 verify_all_options();
@@ -2693,22 +2716,69 @@
2716 db_end_transaction(0);
2717 if( doAuto ){
2718 alert_backoffice(SENDALERT_TRACE|mFlags);
2719 }
2720 }
2721
2722 /*
2723 ** Construct the header and body for an email message that will alert
2724 ** a subscriber that their subscriptions are about to expire.
2725 */
2726 static void alert_renewal_msg(
2727 Blob *pHdr, /* Write email header here */
2728 Blob *pBody, /* Write email body here */
2729 const char *zCode, /* The subscriber code */
2730 int lastContact, /* Last contact (days since 1970) */
2731 const char *zEAddr, /* Subscriber email address. Send to this. */
2732 const char *zSub, /* Subscription codes */
2733 const char *zRepoName, /* Name of the sending Fossil repostory */
2734 const char *zUrl /* URL for the sending Fossil repostory */
2735 ){
2736 blob_appendf(pHdr,"To: <%s>\r\n", zEAddr);
2737 blob_appendf(pHdr,"Subject: %s Subscription to %s expires soon\r\n",
2738 zRepoName, zUrl);
2739 blob_appendf(pBody,
2740 "You are currently receiving email notification of the following kinds\n"
2741 "of changes to the %s Fossil repository at %s:\n\n",
2742 zRepoName, zUrl
2743 );
2744 if( strchr(zSub, 'a') ) blob_appendf(pBody, " * Announcements\n");
2745 if( strchr(zSub, 'c') ) blob_appendf(pBody, " * Check-ins\n");
2746 if( strchr(zSub, 'f') ) blob_appendf(pBody, " * Forum posts\n");
2747 if( strchr(zSub, 't') ) blob_appendf(pBody, " * Ticket changes\n");
2748 if( strchr(zSub, 'w') ) blob_appendf(pBody, " * Wiki changes\n");
2749 blob_appendf(pBody,
2750 "\nTo continue receiving email notifications, click the following link\n"
2751 "\n %s/renew/%s\n\n",
2752 zUrl, zCode
2753 );
2754 blob_appendf(pBody,
2755 "If you take no action, your subscription will expire and you will be\n"
2756 "unsubscribed in about a week. To make other changes or to unsubscribe\n"
2757 "immediately, visit the following webpage:\n\n"
2758 " %s/alerts/%s\n\n",
2759 zUrl, zCode
2760 );
2761 }
2762
2763 #if INTERFACE
2764 /*
2765 ** Flags for alert_send_alerts()
2766 */
2767 #define SENDALERT_DIGEST 0x0001 /* Send a digest */
2768 #define SENDALERT_PRESERVE 0x0002 /* Do not mark the task as done */
2769 #define SENDALERT_STDOUT 0x0004 /* Print emails instead of sending */
2770 #define SENDALERT_TRACE 0x0008 /* Trace operation for debugging */
2771 #define SENDALERT_RENEWAL 0x0010 /* Send renewal notices */
2772
2773 #endif /* INTERFACE */
2774
2775 /*
2776 ** Minimum number of days between renewal messages
2777 */
2778 #define ALERT_RENEWAL_MSG_FREQUENCY 7 /* Do renewals at most once/week */
2779
2780 /*
2781 ** Send alert emails to subscribers.
2782 **
2783 ** This procedure is run by either the backoffice, or in response to the
2784 ** "fossil alerts send" command. Details of operation are controlled by
@@ -2752,10 +2822,11 @@
2822 const char *zRepoName;
2823 const char *zFrom;
2824 const char *zDest = (flags & SENDALERT_STDOUT) ? "stdout" : 0;
2825 AlertSender *pSender = 0;
2826 u32 senderFlags = 0;
2827 int iInterval = 0; /* Subscription renewal interval */
2828
2829 if( g.fSqlTrace ) fossil_trace("-- BEGIN alert_send_alerts(%u)\n", flags);
2830 alert_schema(0);
2831 if( !alert_enabled() && (flags & SENDALERT_STDOUT)==0 ) goto send_alert_done;
2832 zUrl = db_get("email-url",0);
@@ -2801,11 +2872,11 @@
2872
2873 /* Step 2: compute EmailEvent objects for every notification that
2874 ** needs sending.
2875 */
2876 pEvents = alert_compute_event_text(&nEvent, (flags & SENDALERT_DIGEST)!=0);
2877 if( nEvent==0 ) goto send_alert_expiration_warnings;
2878
2879 /* Step 4a: Update the pending_alerts table to designate the
2880 ** alerts as having all been sent. This is done *before* step (3)
2881 ** so that a crash will not cause alerts to be sent multiple times.
2882 ** Better a missed alert than being spammed with hundreds of alerts
@@ -2929,10 +3000,62 @@
3000
3001 /* Step 4b: Update the pending_alerts table to remove all of the
3002 ** alerts that have been completely sent.
3003 */
3004 db_multi_exec("DELETE FROM pending_alert WHERE sentDigest AND sentSep;");
3005
3006 /* Send renewal messages to subscribers whose subscriptions are about
3007 ** to expire. Only do this if:
3008 **
3009 ** (1) email-renew-interval is 14 or greater (or in other words if
3010 ** subscription expiration is enabled).
3011 **
3012 ** (2) The SENDALERT_RENEWAL flag is set
3013 */
3014 send_alert_expiration_warnings:
3015 if( (flags & SENDALERT_RENEWAL)!=0
3016 && (iInterval = db_get_int("email-renew-interval",0))>=14
3017 ){
3018 int iNow = (int)(time(0)/86400);
3019 int iOldWarn = db_get_int("email-renew-warning",0);
3020 int iNewWarn = iNow - iInterval + ALERT_RENEWAL_MSG_FREQUENCY;
3021 if( iNewWarn >= iOldWarn + ALERT_RENEWAL_MSG_FREQUENCY ){
3022 db_prepare(&q,
3023 "SELECT"
3024 " hex(subscriberCode)," /* 0 */
3025 " lastContact," /* 1 */
3026 " semail," /* 2 */
3027 " ssub" /* 3 */
3028 " FROM subscriber"
3029 " WHERE lastContact<=%d AND lastContact>%d"
3030 " AND NOT sdonotcall"
3031 " AND length(sdigest)>0",
3032 iNewWarn, iOldWarn
3033 );
3034 while( db_step(&q)==SQLITE_ROW ){
3035 Blob hdr, body;
3036 blob_init(&hdr, 0, 0);
3037 blob_init(&body, 0, 0);
3038 alert_renewal_msg(&hdr, &body,
3039 db_column_text(&q,0),
3040 db_column_int(&q,1),
3041 db_column_text(&q,2),
3042 db_column_text(&q,3),
3043 zRepoName, zUrl);
3044 alert_send(pSender,&hdr,&body,0);
3045 blob_reset(&hdr);
3046 blob_reset(&body);
3047 }
3048 db_finalize(&q);
3049 if( (flags & SENDALERT_PRESERVE)==0 ){
3050 if( iOldWarn>0 ){
3051 db_set_int("email-renew-cutoff", iOldWarn, 0);
3052 }
3053 db_set_int("email-renew-warning", iNewWarn, 0);
3054 }
3055 }
3056 }
3057
3058 send_alert_done:
3059 alert_sender_free(pSender);
3060 if( g.fSqlTrace ) fossil_trace("-- END alert_send_alerts(%u)\n", flags);
3061 return nSent;
@@ -2955,11 +3078,11 @@
3078 if( !alert_tables_exist() ) return 0;
3079 nSent = alert_send_alerts(mFlags);
3080 iJulianDay = db_int(0, "SELECT julianday('now')");
3081 if( iJulianDay>db_get_int("email-last-digest",0) ){
3082 db_set_int("email-last-digest",iJulianDay,0);
3083 nSent += alert_send_alerts(SENDALERT_DIGEST|SENDALERT_RENEWAL|mFlags);
3084 }
3085 return nSent;
3086 }
3087
3088 /*
3089

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button