fun mkBalanceRow [id, name, amount] =
{id = C.intFromSql id, name = C.stringFromSql name, amount = C.realFromSql amount}
- | mkBalanceRow row = raise Fail ("Bad balance row : " ^ makeSet id row)
+ | mkBalanceRow row = Init.rowError ("balance", row)
fun addBalance name =
let
val id = nextSeq (db, "BalanceSeq")
in
C.dml db ($`INSERT INTO Balance (id, name, amount)
- VALUES (^id, ^(C.stringToSql name), 0.0)`);
- C.intFromSql id
+ VALUES (^(C.intToSql id), ^(C.stringToSql name), 0.0)`);
+ id
end
fun lookupBalance id =
fun listBalances () =
C.map (getDb ()) mkBalanceRow ($`SELECT id, name, amount FROM Balance
- ORDER BY name`)
+ ORDER BY name`)
+
+fun listOwnedBalances () =
+ C.map (getDb ()) mkBalanceRow ($`SELECT Balance.id, Balance.name, amount
+ FROM Balance JOIN WebUser
+ ON Balance.name = WebUser.name
+ AND WebUser.bal = Balance.id
+ JOIN Membership
+ ON Membership.grp = 1
+ AND Membership.usr = WebUser.id
+ ORDER BY Balance.name`)
+
+fun listUnownedBalances () =
+ C.map (getDb ()) mkBalanceRow ($`SELECT Balance.id, Balance.name, amount
+ FROM Balance LEFT OUTER JOIN WebUser
+ ON Balance.name = WebUser.name
+ AND WebUser.bal = Balance.id
+ LEFT OUTER JOIN Membership
+ ON Membership.grp = 1
+ AND Membership.usr = WebUser.id
+ WHERE WebUser.id IS NULL
+ OR Membership.grp IS NULL
+ ORDER BY Balance.name`)
+
+fun listNegativeOwnedBalances () =
+ C.map (getDb ()) mkBalanceRow ($`SELECT Balance.id, Balance.name, amount
+ FROM Balance JOIN WebUser
+ ON Balance.name = WebUser.name
+ AND WebUser.bal = Balance.id
+ JOIN Membership
+ ON Membership.grp = 1
+ AND Membership.usr = WebUser.id
+ WHERE amount < 0
+ ORDER BY Balance.name`)
fun validBalanceName name =
size name <= 20
| _ => NONE
fun listBalanceUsers bal =
- C.map (getDb ()) mkUserRow ($`SELECT id, name, rname, bal, joined
+ C.map (getDb ()) mkUserRow ($`SELECT id, name, rname, bal, joined, app, shares, paypal, checkout
FROM WebUser
WHERE bal = ^(C.intToSql bal)
ORDER BY name`)
-end
\ No newline at end of file
+fun sumOwnedBalances () =
+ case C.oneRow (getDb ()) ($`SELECT SUM(amount)
+ FROM Balance JOIN WebUser
+ ON Balance.name = WebUser.name
+ AND WebUser.bal = Balance.id`) of
+ [amt] => C.realFromSql amt
+ | _ => raise Fail "sumOwnedBalance: no rows"
+
+fun isNegative (bal : balance) = #amount bal < 0.0
+
+end