Low balance reminders

[hcoop/portal.git] / init.sml

structure Init :> INIT =
struct

open Util Sql Config
structure C = PgClient

exception Access of string
exception NeedTos

fun conn () = C.conn dbstring
val close = C.close

type user = {id : int, name : string, rname : string, bal : int, joined : C.timestamp,
	     app : int}

val db = ref (NONE : C.conn option)
val user = ref (NONE : user option)

fun fromSql v =
    if C.isNull v then
	"NULL"
    else
	C.stringFromSql v

fun rowError (tab, vs) = raise Fail ("Bad " ^ tab ^ "row: " ^ makeSet fromSql vs)

fun getDb () = valOf (!db)

fun mkUserRow [id, name, rname, bal, joined, app] =
    {id = C.intFromSql id, name = C.stringFromSql name, rname = C.stringFromSql rname,
     bal = C.intFromSql bal, joined = C.timestampFromSql joined,
     app = C.intFromSql app}
  | mkUserRow row = rowError ("user", row)

fun init () =
    let
	val _ = Util.init ()

	val c = conn ()
    in
	db := SOME c;
	C.dml c "BEGIN";
	case Web.getCgi "REMOTE_USER" of
	    NONE => raise Fail "Not logged in"
	  | SOME name =>
	    (case C.oneOrNoRows c ($`SELECT id, name, rname, bal, joined, app
  				     FROM WebUserActive
				     WHERE name=^(C.stringToSql name)`) of
		 NONE => raise Fail "User not found"
	       | SOME r =>
		 let
		     val r = mkUserRow r
		 in
		     user := SOME r;
		     case C.oneOrNoRows c ($`SELECT ipaddr
					        FROM MemberApp
					        WHERE id = ^(C.intToSql (#app r))
						   AND ipaddr IS NOT NULL`) of
			 NONE =>
			 if Web.getParam "agree" = "on" then
			     (case Web.getCgi "REMOTE_ADDR" of
				  NONE => raise Fail "REMOTE_ADDR not set"
				| SOME ra =>
				  ignore (C.dml c ($`UPDATE MemberApp
					                SET ipaddr = ^(C.stringToSql ra),
						            applied = CURRENT_TIMESTAMP
						        WHERE id = ^(C.intToSql (#app r))`)))
			 else
			     raise NeedTos
		       | _ => ()
		 end)
    end

fun done () =
    let
	val db = getDb ()
    in
	C.dml db "COMMIT";
	close db
    end

fun getUser () = valOf (!user)
fun getUserId () = #id (getUser ())
fun getUserName () = #name (getUser ())

fun lookupUser id =
    mkUserRow (C.oneRow (getDb ()) ($`SELECT id, name, rname, bal, joined, app
				      FROM WebUser
			     	      WHERE id = ^(C.intToSql id)`))

fun listUsers () =
    C.map (getDb ()) mkUserRow ($`SELECT id, name, rname, bal, joined, app
			          FROM WebUser
			          ORDER BY name`)

fun nextSeq (db, seq) =
    case C.oneRow db ($`SELECT nextval('^(seq)')`) of
	[id] => C.intFromSql id
      | _ => raise Fail "Bad next sequence val"

fun addUser (name, rname, bal, app) =
    let
	val db = getDb ()
	val id = nextSeq (db, "WebUserSeq")
    in
	C.dml db ($`INSERT INTO WebUser (id, name, rname, bal, joined, app)
		    VALUES (^(C.intToSql id), ^(C.stringToSql name), ^(C.stringToSql rname), ^(C.intToSql bal), CURRENT_TIMESTAMP, ^(C.intToSql app))`);
	id
    end

fun modUser (user : user) =
    let
	val db = getDb ()
    in
	ignore (C.dml db ($`UPDATE WebUser SET
			    name = ^(C.stringToSql (#name user)), rname = ^(C.stringToSql (#rname user)),
			       bal = ^(C.intToSql (#bal user)), app = ^(C.intToSql (#app user))
			    WHERE id = ^(C.intToSql (#id user))`))
    end

fun deleteUser id =
    C.dml (getDb ()) ($`DELETE FROM WebUser WHERE id = ^(C.intToSql id)`)

fun validUsername name =
    size name <= 10
    andalso CharVector.all Char.isAlpha name

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

fun dateString () =
    case C.oneRow (getDb ()) "SELECT CURRENT_DATE" of
	[d] => C.stringFromSql d
      | r => rowError ("dateString", r)

fun grandfatherUsers () =
    let
	val db = getDb ()

	fun mkApp [id, name, rname] =
	    let
		val id = C.intFromSql id
		val name = C.stringFromSql name
		val rname = C.stringFromSql rname

		val aid = nextSeq (db, "MemberAppSeq")
	    in
		ignore (C.dml db ($`INSERT INTO MemberApp (id, name, rname, gname, email, forward, uses, other,
							passwd, status, applied, confirmed, decided, msg)
			         VALUES (^(C.intToSql aid), ^(C.stringToSql name), ^(C.stringToSql rname),
					 NULL, '^name^(emailSuffix)', FALSE, 'GRANDFATHERED', 'GRANDFATHERED',
					 'GRANDFATHERED', 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,
					 CURRENT_TIMESTAMP, 'GRANDFATHERED')`));
		ignore (C.dml db ($`UPDATE WebUser SET app = ^(C.intToSql aid) WHERE id = ^(C.intToSql id)`))
	    end
    in
	C.app db mkApp "SELECT id, name, rname FROM WebUser WHERE app IS NULL"
    end
end