Added type field to definition

Possible values are stored in 'type' table and defaulted to Home or Work.

1 files changed, 35 insertions, 12 deletions

diff --git a/did-model.lua b/did-model.lua
index 0a31665..2fa6bf9 100644
--- a/did-model.lua
+++ b/did-model.lua
@@ -29,9 +29,13 @@ local didlist
 local database_creation_script = {
 	"CREATE AGGREGATE array_accum(anyelement) (SFUNC = array_append, STYPE = anyarray, INITCOND = '{}')",
 	"CREATE TABLE dbhistlog (logdatetime timestamp(3) without time zone NOT NULL, msgtext text, userid text)",
-	"CREATE TABLE definition (did character varying(40) NOT NULL, identification character varying(7), department character varying(40), description character varying(255), lastchange timestamp(0) without time zone, available boolean)",
+	"CREATE TABLE definition (did character varying(40) NOT NULL, identification character varying(7), department character varying(40), description character varying(255), lastchange timestamp(0) without time zone, available boolean, type character varying(40))",
 	"CREATE TABLE pubdid (did character varying(40), extension character varying(40), starttime time without time zone, endtime time without time zone, stale boolean)",
 	"CREATE TABLE rule (did character varying(40) NOT NULL, extension character varying(40) NOT NULL, starttime time without time zone, endtime time without time zone, startdate date, enddate date, dayofweek bit(7))",
+	"CREATE TABLE type (name character varying(40))",
+	"INSERT INTO type values('')",
+	"INSERT INTO type values('Home')",
+	"INSERT INTO type values('Work')",
 	"ALTER TABLE ONLY definition ADD CONSTRAINT definition_pkey PRIMARY KEY (did)",
 	"CREATE INDEX ruledididx ON rule USING btree (did)",
 	"CREATE INDEX ruledidextensionidx ON rule USING btree (did, extension)",
@@ -40,6 +44,7 @@ local database_creation_script = {
 	"GRANT SELECT, INSERT ON dbhistlog TO "..DatabaseUser,
 	"GRANT SELECT, UPDATE ON definition TO "..DatabaseUser,
 	"GRANT SELECT, UPDATE, INSERT, DELETE ON rule TO "..DatabaseUser,
+	"GRANT SELECT, UPDATE, INSERT, DELETE ON type TO "..DatabaseUser,
 }
 
 -- ################################################################################
@@ -233,7 +238,7 @@ local getdefinitionentries = function(sql)
 	cur = assert (con:execute(sql))
 	row = cur:fetch ({}, "a")
 	while row do
-		entries[#entries+1] = {did=row.did, identification=row.identification, department=row.department, description=row.description, extension=row.extension, available=row.available, lastchange=row.lastchange}
+		entries[#entries+1] = {did=row.did, identification=row.identification, department=row.department, description=row.description, extension=row.extension, available=row.available, lastchange=row.lastchange, type=row.type}
 		entries[#entries].available = (entries[#entries].available == 't')
 		row = cur:fetch (row, "a")
 	end
@@ -349,9 +354,9 @@ local findunuseddefinition = function(exchange)
 end
 
 local updatedefinitionentry = function(definition)
-	local sql = string.format("UPDATE definition SET identification='%s', department='%s', description='%s', available=%s, lastchange='now' WHERE did='%s'",
+	local sql = string.format("UPDATE definition SET identification='%s', department='%s', description='%s', available=%s, type='%s', lastchange='now' WHERE did='%s'",
 		escape(definition.identification), escape(definition.department), escape(definition.description),
-		tostring(definition.available or false), escape(definition.did))
+		tostring(definition.available or false), escape(definition.type), escape(definition.did))
 	local res = assert (con:execute(sql))
 --	logme("Updated DID "..definition.did)
 	return res
@@ -373,6 +378,20 @@ local deletedefinitionentry = function(did)
 	return res
 end
 
+local listtypes = function()
+	local entries = {}
+	local sql = "SELECT * from type"
+	cur = assert (con:execute(sql))
+	row = cur:fetch ({}, "a")
+	while row do
+		entries[#entries+1] = row.name
+		row = cur:fetch (row, "a")
+	end
+	-- close everything
+	cur:close()
+	return entries
+end
+
 local listrules = function(did, date, dayofweek)
 	local entries = {}
 	-- retrieve a cursor
@@ -681,6 +700,7 @@ local validatedefinition = function(defin)
 		defin.value.identification.errtxt = "Invalid identification number"
 		success = false
 	end
+	success = modelfunctions.validateselect(defin.value.type) and success
 	-- defin.value.department
 	-- defin.value.description
 	
@@ -715,7 +735,7 @@ local describechange = function(olddef, newdef)
 	local changes = {}
 	olddef = olddef or {}
 	for name,val in pairs(newdef) do
-		if name ~= "did" and name ~= "rules" and tostring(val) ~= (tostring(olddef[name]) or "") then
+		if name ~= "lastchange" and name ~= "did" and name ~= "rules" and tostring(val) ~= (tostring(olddef[name]) or "") then
 			changes[#changes+1] = name.." from '"..(tostring(olddef[name]) or "").."' to '"..tostring(val).."'"
 		end
 	end
@@ -904,10 +924,13 @@ function getdefinition(self, userid, did)
 	group.description = cfe({ label="Description" })
 	group.rules = cfe({ type="longtext", label="Rules", descr="One entry (extension, starttime, endtime, startdate, enddate, dayofweek) per line"})
 	group.available = cfe({ type="boolean", label="Available", descr="Available for request"})
-	if did then
-		group.did.errtxt = "DID does not exist"
-		local res, err = pcall(function()
-			local connected = databaseconnect(DatabaseUser)
+	group.type = cfe({ type="select", label="Type", option={}})
+	local res, err = pcall(function()
+		local connected = databaseconnect(DatabaseUser)
+		group.type.option = listtypes()
+		table.sort(group.type.option)
+		if did then
+			group.did.errtxt = "DID does not exist"
 			local definition = listdefinitions(stripdash(did), nil, nil, nil, nil, allowedlist)
 			local rules = listrules(stripdash(did))
 			if connected then databasedisconnect() end
@@ -922,10 +945,10 @@ function getdefinition(self, userid, did)
 			if #rules > 0 then
 				group.rules.value = formatrules(rules)
 			end
-		end)
-		if not res then
-			errtxt = err
 		end
+	end)
+	if not res then
+		errtxt = err
 	end
 	group.did.value = adddash(group.did.value)