diff options
Diffstat (limited to 'provisioning-model.lua')
| -rw-r--r-- | provisioning-model.lua | 31 |
1 files changed, 18 insertions, 13 deletions
diff --git a/provisioning-model.lua b/provisioning-model.lua index e517907..1c063fd 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -1292,27 +1292,32 @@ search_device_values = function(parameter_id, parameter_value, comparison) -- Get the devices from the DB if parameter_id and modelfunctions.validateselect(retval.id) and modelfunctions.validateselect(retval.comparison) then parameter_value = parameter_value or "" - sql = "SELECT * FROM provisioning_devices WHERE " - if parameter_id == "name" or parameter_id=="label" then - sql = sql..escape(parameter_id)..retval.comparison.value.."'"..escape(parameter_value).."'" - elseif parameter_id=="template_id" then - sql = sql.."CAST(template_id AS text)"..retval.comparison.value.."'"..escape(parameter_value).."'" - elseif parameter_id=="template" then - -- Search by template label - sql = sql.."template_id IN (SELECT template_id FROM provisioning_templates WHERE label"..retval.comparison.value.."'"..escape(parameter_value).."')" + sql = "SELECT d.device_id, d.name, d.label, d.template_id, " + local group, param = string.match(parameter_id or "", "([^%.]*)%.(.*)") + if not group then + sql = sql.."'"..escape(parameter_id).."' AS param, " + if parameter_id == "name" or parameter_id=="label" then + sql = sql.."d."..escape(parameter_id).." AS value FROM provisioning_devices d WHERE d."..escape(parameter_id) + elseif parameter_id=="template_id" then + sql = sql.."d.template_id AS value FROM provisioning_devices d WHERE CAST(d.template_id AS text)" + elseif parameter_id=="template" then + -- Search by template label + sql = sql.."t.label AS value FROM provisioning_devices d JOIN provisioning_templates t USING(template_id) WHERE t.label" + end + sql = sql..retval.comparison.value.."'"..escape(parameter_value).."'" else - local group, param = string.match(parameter_id or "", "([^%.]*)%.?(.*)") - 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)) ".. + sql = sql.."g.name as group, p.name as param, 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 ".. + "provisioning_devices d JOIN devices_to_classes d2t USING(device_id) 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" else 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"..retval.comparison.value.."'"..escape(parameter_value).."')" + 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"..retval.comparison.value.."'"..escape(parameter_value).."'" end - sql = sql.." ORDER BY name ASC, label ASC" + sql = sql.." ORDER BY d.name ASC, d.label ASC" retval.result.value = getselectresponse(sql) end if connected then databasedisconnect() end |