Add all-balance summing and listing of retired balances

[hcoop/zz_old/portal.git] / balance.sml

structure Balance :> BALANCE =
struct

open Util Sql Init


(* Managing balances *)

type balance = {id :int, name : string, amount : real}

fun mkBalanceRow [id, name, amount] =
    {id = C.intFromSql id, name = C.stringFromSql name, amount = C.realFromSql amount}
  | mkBalanceRow row = Init.rowError ("balance", row)

fun addBalance name =
    let
	val db = getDb ()
	val id = nextSeq (db, "BalanceSeq")
    in
	C.dml db ($`INSERT INTO Balance (id, name, amount)
                    VALUES (^(C.intToSql id), ^(C.stringToSql name), 0.0)`);
	id
    end

fun lookupBalance id =
    let
	val c = getDb ()
    in
	(case C.oneOrNoRows c ($`SELECT id, name, amount FROM Balance WHERE id = ^(C.intToSql id)`) of
	     NONE => raise Fail "Balance not found"
	   | SOME r => mkBalanceRow r)
    end

fun modBalance (balance : balance) =
    let
	val db = getDb ()
    in
	ignore (C.dml db ($`UPDATE Balance
			    SET name = ^(C.stringToSql (#name balance))
			    WHERE id = ^(C.intToSql (#id balance))`))
    end

fun deleteBalance id =
    ignore (C.dml (getDb ()) ($`DELETE FROM Balance WHERE id = ^(C.intToSql id)`))

fun listBalances () =
    C.map (getDb ()) mkBalanceRow ($`SELECT id, name, amount FROM Balance
				     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
				     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
				     WHERE WebUser.id IS NULL
				     ORDER BY Balance.name`)

fun validBalanceName name =
    size name <= 20
    andalso CharVector.all (fn ch => Char.isAlpha ch orelse ch = #"+") name

fun balanceNameToId name =
    case C.oneOrNoRows (getDb ()) ($`SELECT id FROM Balance WHERE name = ^(C.stringToSql name)`) of
	SOME [id] => SOME (C.intFromSql id)
      | _ => NONE

fun listBalanceUsers bal =
    C.map (getDb ()) mkUserRow ($`SELECT id, name, rname, bal, joined, app, shares
			          FROM WebUser
				  WHERE bal = ^(C.intToSql bal)
			          ORDER BY name`)

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"

end