Fixed search to handle multiple responses and correctly check parameter values.

1 files changed, 6 insertions, 35 deletions

diff --git a/provisioning-model.lua b/provisioning-model.lua
index 635c800..744d66d 100644
--- a/provisioning-model.lua
+++ b/provisioning-model.lua
@@ -69,36 +69,6 @@ local table_creation_scripts = {
 -- Need to define indices
 }
 
---[[
--- Get the values for a particular device for use
-1) select * from provisioning_devices where name = 'devicename'
-2) SELECT g.name AS group, p.name, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value 
-	FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING (class_id) JOIN provisioning_groups g USING(group_id)
-		JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id)) LEFT JOIN provisioning_values v USING(device_id, group_id, param_id)
-	WHERE d2t.device_id='yyyyy'
-
--- Get the values for a particular device for editing in ACF
-1) Same as above
-2) Same as above, but replace step 2 first line with:
-SELECT g.name AS group, p.name, p.type, p.label, p.descr, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value, CASE WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS default, g2p.seq, g.label AS grouplabel
-3) Get the options
-
-
-
-
--- Get the params for a class of device
-a = select * from provisioning_types where name = '%1'
-b = select * from provisioning_params c, a.params d where c.pid = d.pid order by d.order
-(loop through looking for type="group" or option ~= null)
-to get the options, it is:
-select * from e.option order by order
-
--- Creating new param_table or option_table is just a matter of copying the schema (and indexes) of param_table or option_table
---]]
-
-
-
-
 -- ################################################################################
 -- LOCAL FUNCTIONS
 local function escape_quotes(str)
@@ -457,7 +427,7 @@ get_class = function(class_id)
 			end
 		end
 		-- Finally, get the paramgroup options
-		sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY seq ASC"
+		sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY seq ASC, name ASC"
 		tmp = getselectresponse(sql)
 		for i,g in ipairs(tmp) do
 			if not retval.groups.value[g.name] then
@@ -1310,17 +1280,18 @@ search_device_values = function(parameter_id, parameter_value)
 				sql = sql..escape(parameter_id).."='"..escape(parameter_value).."'"
 			elseif parameter_id=="template" then
 				-- Search by template label
-				sql = sql.."template_id = (SELECT template_id FROM provisioning_templates WHERE label='"..escape(parameter_value).."')"
+				sql = sql.."template_id IN (SELECT template_id FROM provisioning_templates WHERE label='"..escape(parameter_value).."')"
 			else
 				local group, param = string.match(parameter_id or "", "([^%.]*)%.?(.*)")
-				sql = sql.."device_id = (SELECT d2t.device_id FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) "..
+				sql = sql.."device_id IN (SELECT d2t.device_id FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) "..
 					"JOIN provisioning_groups g USING(group_id) JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id)) "..
 					"LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) "
 				if group and group ~= "" then
-					sql = sql.."WHERE g.name='"..escape(group).."' AND p.name='"..escape(param).."' AND v.value='"..escape(parameter_value).."')"
+					sql = sql.."WHERE g.name='"..escape(group).."' AND"
 				else
-					sql = sql.."WHERE p.name='"..escape(param).."' AND v.value='"..escape(parameter_value).."')"
+					sql = sql.."WHERE"
 				end
+				sql = sql.." p.name='"..escape(param).."' AND CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END='"..escape(parameter_value).."')"
 			end
 			sql = sql.." ORDER BY name ASC, label ASC"
 			retval.result.value = getselectresponse(sql)