-structure Remind :> REMIND =
-struct
-
-open Config
-
-structure C = PgClient
-
-fun main _ =
- let
- val db = C.conn dbstring
-
- fun getEmail [name] = C.stringFromSql name ^ emailSuffix
- | getEmail row = raise Fail "remind getName"
-
- val names = C.map db getEmail "SELECT WebUserActive.name FROM WebUserActive JOIN Balance ON Balance.name = WebUserActive.name AND bal = Balance.id WHERE amount < 10"
-
- val m = Mail.mopen ()
- in
- Mail.mwrite (m, "Subject: Reminder of low HCoop balance\n");
- Mail.mwrite (m, "From: HCoop Portal <payment");
- Mail.mwrite (m, emailSuffix);
- Mail.mwrite (m, ">\n");
- Mail.mwrite (m, "Bcc: ");
- Mail.mwrite (m, String.concatWith "," names);
- Mail.mwrite (m, "\n\n");
- Mail.mwrite (m, "This is a friendly reminder that your monetary balance at HCoop has dropped below\n");
- Mail.mwrite (m, "the US$10 \"deposit\" amount. You can check your balance at:\n");
- Mail.mwrite (m, "\t");
- Mail.mwrite (m, urlPrefix);
- Mail.mwrite (m, "money\n\n");
- Mail.mwrite (m, "You'll also see on that page a link you can follow to pay via PayPal.\n\n");
- Mail.mwrite (m, "It would be great if you could bring your balance above that amount soon.\n");
- Mail.mwrite (m, "More information on how to pay can be found at:\n");
- Mail.mwrite (m, "\thttp://wiki.hcoop.net/wiki/MemberDues\n");
- C.close db;
- OS.Process.success
- end
-
-end
+structure Remind :> REMIND =
+struct
+
+open Config
+
+structure C = PgClient
+
+fun printReal n =
+ if n < 0.0 then
+ "-" ^ Real.fmt (StringCvt.FIX (SOME 2)) (~n)
+ else
+ Real.fmt (StringCvt.FIX (SOME 2)) n
+
+fun main _ =
+ let
+ val db = C.conn dbstring
+
+ val totalShares = case C.oneRow db "SELECT SUM(shares) FROM WebUserPaying" of
+ [total] => C.intFromSql total
+ | _ => raise Fail "Bad SUM(shares) row"
+
+ fun doOne [user, members, shares, amount] =
+ let
+ val user = C.stringFromSql user
+ val members = C.intFromSql members
+ val shares = C.intFromSql shares
+ val amount = C.realFromSql amount
+
+ val minimum = 900.0 * real shares / real totalShares * 2.0
+ in
+ if amount >= minimum then
+ ()
+ else
+ let
+ val m = Mail.mopen ()
+ fun write msg = Mail.mwrite (m, msg)
+ in
+ if amount < 0.0 then
+ write "Subject: Your NEGATIVE HCoop balance\n"
+ else
+ write "Subject: Your low HCoop balance\n";
+ write "From: HCoop Payment Reminder Robot <payment";
+ write emailSuffix;
+ write ">\n";
+ write "To: ";
+ write user;
+ write emailSuffix;
+ write "\nCc: payment@hcoop.net";
+ write "\n\n";
+
+ if amount < 0.0 then
+ (write "Your HCoop balance is negative. This means that you've paid less than you've\n";
+ write "been charged to date. Our bylaws allow our board of directors to vote you out\n";
+ write "of the co-op, without any obligation to contact you first, when your balance\n";
+ write "stays negative for three months. Please make a payment as soon as possible, so\n";
+ write "that we don't need to do this!\n\n")
+ else
+ (write "Your HCoop balance has dropped below your requested minimum, based on your\n";
+ write "sliding scale pledge amount. Please make a payment as soon as you can.\n\n");
+
+ write "Your balance: US$";
+ write (printReal amount);
+ write "\nTotal number of members linked to your balance: ";
+ write (Int.toString members);
+ write "\nTotal pledge amount: ";
+ write (Int.toString shares);
+ write "\nRequested minimum: US$";
+ write (printReal (900.0 * real shares / real totalShares * 2.0));
+
+ write "\n\nYour minimum was calculated by dividing our total monthly expenses ($900) by the\n";
+ write "sum of all members' pledge amounts, multiplying by your pledge amount, and then\n";
+ write "multiplying by 2. That is, the amount covers your share of two months' expenses.\n\n";
+
+ write "To make a payment, visit:\n";
+ write " https://members2.hcoop.net/\n";
+ write "and use the PayPal or Google Checkout link.\n";
+ ignore (Mail.mclose m)
+ end
+ end
+ | doOne _ = raise Fail "Bad SQL row"
+ in
+ C.app db doOne "SELECT Balance.name, COUNT(*), SUM(WebUserPaying.shares) AS shrs, Balance.amount FROM WebUserPaying JOIN Balance ON WebUserPaying.bal = Balance.id GROUP BY Balance.name, Balance.amount HAVING amount < 10";
+ C.close db;
+ OS.Process.success
+ end handle C.Sql s => (print ("SQL failure: " ^ s ^ "\n");
+ OS.Process.failure)
+
+end
HCoop / hcoop / zz_old / portal.git / commitdiff