Overview
Comment:Fix [9afefc9f2b6b1f4b] : permettre de chercher les champs nuls / non-nuls
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA1: fd314357774d0607f0d4bc925e1252ae846a6606
User & Date: bohwaz on 2018-10-25 10:56:13
Other Links: branch diff | manifest | tags
References
2018-10-25
10:57 Fixed ticket [9afefc9f2b]: Problèmes avec la recherche avancée plus 5 other changes artifact: 762f4a0d5a user: bohwaz
Context
2018-10-25
11:19
Recherche avancée : meilleure traduction de "ANY" (fix [026bac40f8e17f7668f40484abd773fec43dec61]) check-in: 2e72b91d09 user: bohwaz tags: dev
10:56
Fix [9afefc9f2b6b1f4b] : permettre de chercher les champs nuls / non-nuls check-in: fd31435777 user: bohwaz tags: dev
10:38
Fix [9afefc9f2b6b1f4b7d07949e92c66e1604b19d37] Champs de type sélection unique : recherche non fonctionnelle check-in: 222a7d057f user: bohwaz tags: dev
Changes

Modified src/include/lib/Garradin/Recherche.php from [d3b4fbf0f1] to [a738c3a98e].

   180    180   			foreach ($champs->getList() as $champ => $config)
   181    181   			{
   182    182   				$column = (object) [
   183    183   					'realType' => $config->type,
   184    184   					'textMatch'=> $champs->isText($champ),
   185    185   					'label'    => $config->title,
   186    186   					'type'     => 'text',
   187         -					'null'     => true,
          187  +					'null'     => $config->mandatory ? false : true,
   188    188   				];
   189    189   
   190    190   				if ($config->type == 'checkbox')
   191    191   				{
   192    192   					$column->type = 'boolean';
   193    193   				}
   194    194   				elseif ($config->type == 'select')
................................................................................
   241    241   
   242    242   		$db = DB::getInstance();
   243    243   		$target_columns = $this->getColumns($target);
   244    244   		$query_columns = [];
   245    245   
   246    246   		$query_groups = [];
   247    247   
          248  +		static $no_transform_operators = ['IS NULL', 'IS NOT NULL', '= 0', '= 1', '&'];
          249  +
   248    250   		foreach ($groups as $group)
   249    251   		{
   250    252   			if (!isset($group['conditions'], $group['operator'])
   251    253   				|| !is_array($group['conditions'])
   252    254   				|| ($group['operator'] != 'AND' && $group['operator'] != 'OR'))
   253    255   			{
   254    256   				// Ignorer les groupes de conditions invalides
................................................................................
   273    275   					// après avoir modifié les fiches de membres
   274    276   					throw new UserException('Cette recherche fait référence à un champ qui n\'existe plus dans les fiches de membres.');
   275    277   				}
   276    278   
   277    279   				$query_columns[] = $condition['column'];
   278    280   				$column = $target_columns[$condition['column']];
   279    281   
   280         -				if ($column->textMatch == 'text')
          282  +				if ($column->textMatch == 'text' && !in_array($condition['operator'], $no_transform_operators))
   281    283   				{
   282    284   					$query = sprintf('transliterate_to_ascii(%s) COLLATE NOCASE %s', $db->quoteIdentifier($condition['column']), $condition['operator']);
   283    285   				}
   284    286   				else
   285    287   				{
   286    288   					$query = sprintf('%s %s', $db->quoteIdentifier($condition['column']), $condition['operator']);
   287    289   				}

Modified src/www/admin/static/scripts/query_builder.min.js from [0e11953bac] to [6daac3a8d1].

     1         -!function(){var e=function(e){this.columns=e};(window.SQLQueryBuilder=e).prototype.loadDefaultOperators=function(){for(var e in this.operators={"= ?":this.__("is equal to"),"!= ?":this.__("is not equal to"),"IN (??)":this.__("is equal to one of"),"NOT IN (??)":this.__("is not equal to one of"),"> ?":this.__("is greater than"),">= ?":this.__("is greater than or equal to"),"< ?":this.__("is less than"),"<= ?":this.__("is less than or equal to"),"BETWEEN ? AND ?":this.__("is between"),"NOT BETWEEN ? AND ?":this.__("is not between"),"IS NULL":this.__("is null"),"IS NOT NULL":this.__("is not null"),"LIKE ?%":this.__("begins with"),"NOT LIKE ?%":this.__("doesn't begin with"),"LIKE %?":this.__("ends with"),"NOT LIKE %?":this.__("doesn't end with"),"LIKE %?%":this.__("contains"),"NOT LIKE %?%":this.__("doesn't contain"),"&":this.__("matches one of"),"= 1":this.__("is true"),"= 0":this.__("is false")},this.types_operators={integer:["= ?","!= ?","IN (??)","NOT IN (??)","> ?",">= ?","< ?","<= ?","BETWEEN ? AND ?","NOT BETWEEN ? AND ?"],enum:["= ?","!= ?","IN (??)","NOT IN (??)"],boolean:["= 1","= 0"],text:["= ?","!= ?","IN (??)","NOT IN (??)","LIKE ?%","NOT LIKE ?%","LIKE %?","NOT LIKE %?","LIKE %?%","NOT LIKE %?%"],bitwise:["&"]},this.types_operators){var t={};for(var i in this.types_operators[e]){var o=this.types_operators[e][i];t[o]=this.operators[o]}this.types_operators[e]=t}this.types_operators.date=JSON.parse(JSON.stringify(this.types_operators.integer)),delete this.types_operators.date["<= ?"],delete this.types_operators.date[">= ?"],this.types_operators.date["< ?"]=this.__("before"),this.types_operators.date["> ?"]=this.__("after"),this.types_operators.datetime=this.types_operators.date},e.prototype.__=function(e){return e},e.prototype.init=function(e){this.parent=e;var t={"":"---"};for(column in this.columns)t[column]=this.columns[column].label;this.columnSelect=this.buildSelect(t)},e.prototype.addGroup=function(t,e){var i=document.createElement("fieldset"),o=document.createElement("legend"),n=this.buildSelect({AND:this.__("Matches ALL of the following conditions:"),OR:this.__("Matches ANY of the following conditions:"),ADD:this.__("Add a new set of conditions below this one"),DEL:this.__("Remove this set of conditions")});n.onfocus=function(){this.oldValue=this.value},n.value=e;var r=this;n.onchange=function(){if("DEL"==this.value){if(1==t.childNodes.length)return void(this.value=this.oldValue);t.removeChild(i)}else if("ADD"==this.value){var e=r.addGroup(t,"AND");r.addRow(e),this.value=this.oldValue}},o.appendChild(n),i.appendChild(o);var s=document.createElement("table");return i.appendChild(s),t.appendChild(i),i},e.prototype.addRow=function(e,t){var i=e.getElementsByTagName("table")[0],o=document.createElement("tr");(s=document.createElement("td")).className="buttons";var n,r=this;(n=this.buildInput("button","+")).onclick=function(){r.addRow(function(e,t){for(;(e=e.parentElement)&&!(e.matches||e.matchesSelector).call(e,t););return e}(this,"fieldset"),this.parentNode.parentNode)},s.appendChild(n),(n=this.buildInput("button","-")).onclick=function(){r.deleteRow(this.parentNode.parentNode)},s.appendChild(n),o.appendChild(s),(s=document.createElement("td")).className="column";var s,a=this.columnSelect.cloneNode(!0);return a.onchange=function(){return r.switchColumn(this)},s.appendChild(a),o.appendChild(s),(s=document.createElement("td")).className="operator",o.appendChild(s),(s=document.createElement("td")).className="values",o.appendChild(s),void 0===t?i.appendChild(o):i.insertBefore(o,t.nextSibling),o},e.prototype.deleteRow=function(e){e.parentNode.childNodes.length<=1||e.parentNode.removeChild(e)},e.prototype.switchColumn=function(e){var t=e.parentNode.parentNode;t.childNodes[2].innerHTML="",t.childNodes[3].innerHTML="",this.addOperator(t,this.columns[e.value])},e.prototype.addOperator=function(e,t){var i=this.types_operators[t.type],o={"":"---"};for(var n in i)o[n]=i[n];var r=this.buildSelect(o),s=this;return r.onchange=function(){return s.switchOperator(this)},e.childNodes[2].appendChild(r),r},e.prototype.switchOperator=function(e,t){var i=e.parentNode.parentNode;i.childNodes[3].innerHTML="";var o=i.childNodes[3],n=i.childNodes[1].firstChild,r=e.value,s=this.columns[n.value];if(r){var a=1,l=!1,d=null,h=r.match(/\?/g);if(h){r.match(/\?\?/)?(a=t?t.length:3,l=!0):1<h.length&&(a=h.length);for(var p=0;p<a;p++)d=this.addMatchField(o,d,s,r),t&&(d.value=t[p]);if(l){(u=this.buildInput("button","-")).onclick=function(){this.parentNode.childNodes.length<=3||(this.parentNode.removeChild(this.previousSibling),this.parentNode.removeChild(this.previousSibling))},o.appendChild(u);var u=this.buildInput("button","+"),c=this;u.onclick=function(){c.addMatchField(o,this.previousSibling.previousSibling,s,r)},o.appendChild(u)}}}},e.prototype.addMatchField=function(e,t,i,o){if("enum"==i.type)var n=this.buildSelect(i.values);else if("bitwise"==i.type){n=document.createElement("span");for(var r in i.values){var s=this.buildInput("checkbox",r),a=document.createElement("label");a.appendChild(s),a.appendChild(document.createTextNode(" "+i.values[r])),n.appendChild(a.cloneNode(!0))}}else n=this.buildInput(i.type,"",i);return n=e.insertBefore(n,t?t.nextSibling:null),t&&e.insertBefore(document.createElement("br"),n),n},e.prototype.buildInput=function(e,t,i){var o=document.createElement("input");return o.type="integer"==e?"number":e,o.value=t,o},e.prototype.buildSelect=function(e){var t=document.createElement("select");for(var i in e){var o=document.createElement("option");o.value=i,o.innerHTML=e[i],t.appendChild(o)}return t},e.prototype.import=function(e){for(var t in e)if(0!=e[t].conditions.length){var i=this.addGroup(this.parent,e[t].operator);for(var o in e[t].conditions){var n=e[t].conditions[o],r=this.addRow(i);r.childNodes[1].firstChild.value=n.column;var s=this.addOperator(r,this.columns[n.column]);s.value=n.operator,this.switchOperator(s,n.values)}}},e.prototype.export=function(){var e=this.parent.querySelectorAll("table"),t=[];for(var i in e)if(e.hasOwnProperty(i)){for(var o=(i=e[i]).rows,n=[],r=0;r<o.length;r++){var s=o[r];if(s.getElementsByTagName("select")[0].value){var a=Array.prototype.slice.call(s.cells[3].querySelectorAll("input, select")).map(function(e){if("button"!=e.type)return e.value}),l={column:s.cells[1].firstChild.value,operator:s.cells[2].firstChild.value,values:a};l.operator&&(l.operator.match(/\?\?/)&&(l.values=l.values.slice(0,-2)),n.push(l))}}t.push({operator:i.parentNode.firstChild.firstChild.value,conditions:n})}return t}}();
            1  +!function(){var e=function(e){this.columns=e};(window.SQLQueryBuilder=e).prototype.loadDefaultOperators=function(){for(var e in this.operators={"= ?":this.__("is equal to"),"!= ?":this.__("is not equal to"),"IN (??)":this.__("is equal to one of"),"NOT IN (??)":this.__("is not equal to one of"),"> ?":this.__("is greater than"),">= ?":this.__("is greater than or equal to"),"< ?":this.__("is less than"),"<= ?":this.__("is less than or equal to"),"BETWEEN ? AND ?":this.__("is between"),"NOT BETWEEN ? AND ?":this.__("is not between"),"IS NULL":this.__("is null"),"IS NOT NULL":this.__("is not null"),"LIKE ?%":this.__("begins with"),"NOT LIKE ?%":this.__("doesn't begin with"),"LIKE %?":this.__("ends with"),"NOT LIKE %?":this.__("doesn't end with"),"LIKE %?%":this.__("contains"),"NOT LIKE %?%":this.__("doesn't contain"),"&":this.__("matches one of"),"= 1":this.__("is true"),"= 0":this.__("is false")},this.types_operators={integer:["= ?","!= ?","IN (??)","NOT IN (??)","> ?",">= ?","< ?","<= ?","BETWEEN ? AND ?","NOT BETWEEN ? AND ?"],enum:["= ?","!= ?","IN (??)","NOT IN (??)"],boolean:["= 1","= 0"],text:["= ?","!= ?","IN (??)","NOT IN (??)","LIKE ?%","NOT LIKE ?%","LIKE %?","NOT LIKE %?","LIKE %?%","NOT LIKE %?%"],bitwise:["&"]},this.types_operators){var t={};for(var i in this.types_operators[e]){var o=this.types_operators[e][i];t[o]=this.operators[o]}this.types_operators[e]=t}this.types_operators.date=JSON.parse(JSON.stringify(this.types_operators.integer)),delete this.types_operators.date["<= ?"],delete this.types_operators.date[">= ?"],this.types_operators.date["< ?"]=this.__("before"),this.types_operators.date["> ?"]=this.__("after"),this.types_operators.datetime=this.types_operators.date},e.prototype.__=function(e){return e},e.prototype.init=function(e){this.parent=e;var t={"":"---"};for(column in this.columns)t[column]=this.columns[column].label;this.columnSelect=this.buildSelect(t)},e.prototype.addGroup=function(t,e){var i=document.createElement("fieldset"),o=document.createElement("legend"),n=this.buildSelect({AND:this.__("Matches ALL of the following conditions:"),OR:this.__("Matches ANY of the following conditions:"),ADD:this.__("Add a new set of conditions below this one"),DEL:this.__("Remove this set of conditions")});n.onfocus=function(){this.oldValue=this.value},n.value=e;var r=this;n.onchange=function(){if("DEL"==this.value){if(1==t.childNodes.length)return void(this.value=this.oldValue);t.removeChild(i)}else if("ADD"==this.value){var e=r.addGroup(t,"AND");r.addRow(e),this.value=this.oldValue}},o.appendChild(n),i.appendChild(o);var s=document.createElement("table");return i.appendChild(s),t.appendChild(i),i},e.prototype.addRow=function(e,t){var i=e.getElementsByTagName("table")[0],o=document.createElement("tr");(s=document.createElement("td")).className="buttons";var n,r=this;(n=this.buildInput("button","+")).onclick=function(){r.addRow(function(e,t){for(;(e=e.parentElement)&&!(e.matches||e.matchesSelector).call(e,t););return e}(this,"fieldset"),this.parentNode.parentNode)},s.appendChild(n),(n=this.buildInput("button","-")).onclick=function(){r.deleteRow(this.parentNode.parentNode)},s.appendChild(n),o.appendChild(s),(s=document.createElement("td")).className="column";var s,a=this.columnSelect.cloneNode(!0);return a.onchange=function(){return r.switchColumn(this)},s.appendChild(a),o.appendChild(s),(s=document.createElement("td")).className="operator",o.appendChild(s),(s=document.createElement("td")).className="values",o.appendChild(s),void 0===t?i.appendChild(o):i.insertBefore(o,t.nextSibling),o},e.prototype.deleteRow=function(e){e.parentNode.childNodes.length<=1||e.parentNode.removeChild(e)},e.prototype.switchColumn=function(e){var t=e.parentNode.parentNode;t.childNodes[2].innerHTML="",t.childNodes[3].innerHTML="",this.addOperator(t,this.columns[e.value])},e.prototype.addOperator=function(e,t){var i=this.types_operators[t.type],o={"":"---"};for(var n in t.null&&(i["IS NULL"]=this.operators["IS NULL"],i["IS NOT NULL"]=this.operators["IS NOT NULL"]),i)o[n]=i[n];var r=this.buildSelect(o),s=this;return r.onchange=function(){return s.switchOperator(this)},e.childNodes[2].appendChild(r),r},e.prototype.switchOperator=function(e,t){var i=e.parentNode.parentNode;i.childNodes[3].innerHTML="";var o=i.childNodes[3],n=i.childNodes[1].firstChild,r=e.value,s=this.columns[n.value];if(r){var a=1,l=!1,d=null,h=r.match(/\?/g);if(h){r.match(/\?\?/)?(a=t?t.length:3,l=!0):1<h.length&&(a=h.length);for(var p=0;p<a;p++)d=this.addMatchField(o,d,s,r),t&&(d.value=t[p]);if(l){(u=this.buildInput("button","-")).onclick=function(){this.parentNode.childNodes.length<=3||(this.parentNode.removeChild(this.previousSibling),this.parentNode.removeChild(this.previousSibling))},o.appendChild(u);var u=this.buildInput("button","+"),c=this;u.onclick=function(){c.addMatchField(o,this.previousSibling.previousSibling,s,r)},o.appendChild(u)}}}},e.prototype.addMatchField=function(e,t,i,o){if("enum"==i.type)var n=this.buildSelect(i.values);else if("bitwise"==i.type){n=document.createElement("span");for(var r in i.values){var s=this.buildInput("checkbox",r),a=document.createElement("label");a.appendChild(s),a.appendChild(document.createTextNode(" "+i.values[r])),n.appendChild(a.cloneNode(!0))}}else n=this.buildInput(i.type,"",i);return n=e.insertBefore(n,t?t.nextSibling:null),t&&e.insertBefore(document.createElement("br"),n),n},e.prototype.buildInput=function(e,t,i){var o=document.createElement("input");return o.type="integer"==e?"number":e,o.value=t,o},e.prototype.buildSelect=function(e){var t=document.createElement("select");for(var i in e){var o=document.createElement("option");o.value=i,o.innerHTML=e[i],t.appendChild(o)}return t},e.prototype.import=function(e){for(var t in e)if(0!=e[t].conditions.length){var i=this.addGroup(this.parent,e[t].operator);for(var o in e[t].conditions){var n=e[t].conditions[o],r=this.addRow(i);r.childNodes[1].firstChild.value=n.column;var s=this.addOperator(r,this.columns[n.column]);s.value=n.operator,this.switchOperator(s,n.values)}}},e.prototype.export=function(){var e=this.parent.querySelectorAll("table"),t=[];for(var i in e)if(e.hasOwnProperty(i)){for(var o=(i=e[i]).rows,n=[],r=0;r<o.length;r++){var s=o[r];if(s.getElementsByTagName("select")[0].value){var a=Array.prototype.slice.call(s.cells[3].querySelectorAll("input, select")).map(function(e){if("button"!=e.type)return e.value}),l={column:s.cells[1].firstChild.value,operator:s.cells[2].firstChild.value,values:a};l.operator&&(l.operator.match(/\?\?/)&&(l.values=l.values.slice(0,-2)),n.push(l))}}t.push({operator:i.parentNode.firstChild.firstChild.value,conditions:n})}return t}}();