-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 WebUser.name FROM WebUser JOIN Balance ON Balance.name = WebUser.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, "It would be great if you could bring your balance above that amount soon.\n");
- Mail.mwrite (m, "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
+
+val basePerMonth = 7.0
+val deposit = basePerMonth * 3.0
+
+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 perMonth = basePerMonth * real shares
+ val headsUp = deposit + perMonth * 2.0
+ in
+ if amount >= headsUp then
+ ()
+ else
+ let
+ val m = Mail.mopen ()
+ fun write msg = Mail.mwrite (m, msg)
+ in
+ if amount < deposit 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 < deposit then
+ (write "Your HCoop balance is negative. This means that you've paid less than you've\n";
+ write "been charged to date, excluding your required deposit. If your account hasn't\n";
+ write "been frozen yet, expect that to happen very soon. Our bylaws allow our board\n";
+ write "of directors to vote you out of the co-op, without any obligation to contact\n";
+ write "you first, when your balance stays negative for three months. Please make a\n";
+ write "payment as soon as possible, so that we don't need to do this!\n\n")
+ else
+ (write "With our current dues projections, you have less than two months left until\n";
+ write "your HCoop balance becomes negative, based on your sliding scale pledge amount.\n";
+ write "Please make a payment as soon as you can. We will freeze your account if your\n";
+ write "balance does become negative, and the board of directors will probably vote you\n";
+ write "out of the cooperative shortly thereafter if you don't make a payment.\n\n");
+
+ write "Your balance: US$";
+ write (printReal (amount - deposit));
+ write "\nTotal number of members linked to your balance: ";
+ write (Int.toString members);
+ write "\nTotal pledge amount: ";
+ write (Int.toString shares);
+ write "\nDeposit: US$";
+ write (printReal deposit);
+ write "\nMinimum amount to pay to not see this message again for two months: US$";
+ write (printReal (headsUp - amount));
+
+ write "\n\nThe deposit requirement was calculated as three months of dues at $7/mo..\n\n";
+
+ write "To make a payment, visit:\n";
+ write " https://members.hcoop.net/\n";
+ write "and use the PayPal or Google Checkout link.\n";
+
+ write "\nIf for whatever reason you don't plan to pay the amount suggested in this e-mail,\n";
+ write "_please_ don't stay silent. Reply to this message explaining your circumstances.\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 < " ^ C.realToSql deposit ^ " + " ^ C.realToSql (basePerMonth * 2.0) ^ " * SUM(WebUserPaying.shares)");
+ C.close db;
+ OS.Process.success
+ end handle C.Sql s => (print ("SQL failure: " ^ s ^ "\n");
+ OS.Process.failure)
+
+end