#
#	ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:		dquermfile_groupmembers.txt
#	DESCRIPTION:	DQUERY definition for CCDB DQUERY "groupmembers"
#
@querytitle			aktuelle Mitglieder einer oder mehrerer Gruppen anzeigen
@querydescription	
@group				GROUPS
@paramnames			groupid:groupname:{{groupidoptions}},roleid:role:{{roleidoptions}},oeid:oeid:{{oeidoptions}},addwhereclause:string:{{addwhereclauseoptions}}
@groupidoptions		{"multiple":"true"}
@oeidoptions		{"is_optional":"true"}
@roleidoptions		{"is_optional":"true"}
@addwhereclauseoptions	{"label":"Erweiterte WHERE-Klausel","typetext":"Erweiterung der SQL WHERE-Klausel um Auswahl zu verändern","is_optional":"true"}
@querytype	dbselect
@title	Mitglieder der [[group_qualifier]][[oeid_qualifier]][[role_qualifier]][[awc_qualifier]] per [[nowdate]]
@sqlexpr	that.query.sql = that.query.sql_s0 + "\n ";
		if (Array.isArray(that.query.groupid)) {
		  that.query.group_qualifier = "Gruppen \""+that.query.groupid.join(",")+"\"";
		  that.query.sql += that.query.sql_gwcm;
		} else {
		  that.query.group_qualifier = "Gruppe \""+that.query.groupid+"\"";
		  that.query.sql += that.query.sql_gwcs;
		}
		if (that.query.oeid) {
		  that.query.sql += that.query.sql_rwco;
		  that.query.oeid_qualifier = " von OE "+that.query.oeid;
		} else {
		  that.query.oeid_qualifier = "";
		}
		if (that.query.roleid) {
		  that.query.sql += that.query.sql_rwcr;
		  that.query.role_qualifier = ' mit Rolle '+that.query.roleid;
		} else {
		  that.query.role_qualifier = "";
		}
		if (that.query.addwhereclause) {
		  that.query.sql += " "+that.query.addwhereclause;
		  that.query.awc_qualifier = " "+that.query.addwhereclause;
		} else {
		  that.query.awc_qualifier = "";
		}
		(that.query.sql)
@sql_s0	select "VON_OE","Rolle","Name","Email","Telefon","Mobil","DevOpsUser","VONDATNUM","BISDATNUM",
		       '' as "Aktionen",
		       USKURZZ,ROLEID,Gruppe,Gruppenname
		  from CCVGRPMMB
@sql_gwcs	where Gruppe=::groupid::
@sql_gwcm	where Gruppe in (::groupid::)
@sql_rwcr	and ROLEID=::roleid::
@sql_rwco	and VON_OE=::oeid::
@post_query
	that.nd=new Date(); 
	that.result.setResultAttribute('nowdate',that.nd.getFullYear()+"-"+aux.DEC(that.nd.getMonth()+1,2));
	that.gcn = that.result.findColumn("Gruppe");
	that.gncn = that.result.findColumn("Gruppenname");
	that.groupids = {};
	that.groupidlist = new Array();
	that.groupnamelist = new Array();
	that.statinfo = "Einträge: "+that.result.getRowCount()+", ";
	that.result.setResultAttribute("groupname",that.result.getCellValue(0,that.gncn));
	that.ecn = that.result.findColumn("Email");
	that.acn = that.result.findColumn("Aktionen");
	that.eml = new Array();
	for (that.i=0; that.i<that.result.getRowCount(); that.i++) {
	  if (that.ecn>=0) {
	    that.ea = that.result.getCellValue(that.i,that.ecn);
	    if (that.ea) that.eml.push(that.ea);
	  }
	  that.gid = that.result.getCellValue(that.i,that.gcn);
	  that.gnm = that.result.getCellValue(that.i,that.gncn);
	  if (!that.groupids[that.gid]) {
	    that.groupids[that.gid] = { name: that.gnm };
	    that.groupidlist.push(that.gid);
	    that.groupnamelist.push(that.gnm);
	  }
	  if (that.acn>=0) {
	    that.trow = that.result.getRowObject(that.i,"value");
	    that.result.setCellValue(that.i,that.acn,"rawhtml",
	      "<a href=\"/dquery?dataname=delpersongroup"+
	              "&groupid="+that.trow.Gruppe+"&uskurzz="+that.trow.USKURZZ+
	              "&oeid="+that.trow.VON_OE+"&role="+that.trow.ROLEID+"\""+
	        " title=\"Person "+that.trow.Name+" (Kurzzeichen:"+that.trow.USKURZZ+") aus Gruppe "+
	        that.trow.Gruppenname+" (ID:"+that.trow.Gruppe+") entfernen\">"+
	        "<span class=\"fas fa-trash-alt\"></span></a>");
	  }
	}
	if (that.ecn>=0) {
	  if (that.eml.length>0) that.emls = that.eml.join(";");
	  that.statinfo += "Email-Adressen: "+that.eml.length;
	}
	if (that.emls) {
	  that.result.setResultAttribute("email_item",
	                                 "<a href=\"mailto:"+that.emls+"\">E-Mail an alle Mitglieder</a>");
	} else {
	  that.result.setResultAttribute("email_item","Keine E-Mail-Adressen gefunden");
	}
	that.result.setResultAttribute("stat_item",that.statinfo);
	aux.modify_result(that.result,{mods: [{modact: 'delcol', col: that.gncn},
	                                      {modact: 'delcol', col: that.gcn}]});
	if (that.groupnamelist.length>1) {
	  that.result.setResultAttribute('group_qualifier',"Gruppen \""+that.groupnamelist.join(",")+"\"");
	} else if (that.groupnamelist.length==1) {
	  that.result.setResultAttribute('group_qualifier',"Gruppe \""+that.groupnamelist[0]+"\"");
	} else {
	  that.result.setResultAttribute('group_identifier',"Gruppe mit der ID "+that.result.getResultAttribute('groupid'));
	}
	let escaped_whereclause;
	if (that.query.whereclause) {
		escaped_whereclause = that.query.whereclause.replace(/\"/g,'&#x22');
	} else {
		escaped_whereclause = "";
	}
	let refqs = "dataname=groupmembers";
	if (that.query.oeid) 			refqs += "&oeid="+that.query.oeid;
	if (that.query.groupid) 		refqs += "&groupid="+that.query.groupid;
	if (that.query.roleid) 			refqs += "&roleid="+that.query.roleid;
	if (that.query.addwhereclause)	refqs += "&addwhereclause="+that.query.addwhereclause;
	that.result.toptext = 
			"<p>"+that.result.stat_item+"</p>\r\n"+
			"<ul>"+
			"  <li>Die Abfrage hat "+that.result.getRowCount()+" Einträge gefunden</li>\r\n"+
			"</ul>\r\n"+
			"<h3>Weitere Aktionen</h3>\r\n"+
			"<ul>"+
			" <li><a href=\"/dquery?"+refqs+"\">Aktualisieren</a></li>\r\n"+
			" <li><table style=\"border-style: solid; border-width:thin;\">"+
			"  <tr><form action=\"/dquery\" method=\"GET\">"+
			"      <input type=\"hidden\" name=\"dataname\" value=\"groupmembers\"></input>"+
			"      <td valign=\"center\"><label for_input=\"addwhereclause\">Neue Bedingung</label></td>"+
			"      <td valign=\"center\"><input type=\"text\" name=\"addwhereclause\" size=\"132\" value=\""+escaped_whereclause+"\"></input></td>"+
			"      <td valign=\"center\"><input type=\"submit\" name=\"submit\" value=\"mit dieser Bedingung neu abfragen\"></input></td>"+
			"      </form></tr>"+
			" </table></li>\r\n"+	
			" <li>"+that.result.email_item+"</li>\r\n"+
			" <li><a href=\"/dquery?dataname=addpersongroup&groupid="+that.query.groupid+"\">eine Person zu dieser Gruppe hinzufügen</a></li>\r\n"+
			" <li><a href=\"/dquery?dataname=listgroups\">alle Gruppen</a></li>\r\n"+
			" <li><a href=\"/dquery?dataname=groupmembers&groupid={{groupid}}&addwhereclause=+AND+%22Email%22+is+null\">Zeige alle Mitglieder der Gruppe ohne Email-Adressen</a></li>\r\n"+
			"</ul>\r\n";
@tableclass	excelFilterTable
@decoration.decorate_colcell.0.hyperlink.href	/dquery?dataname=oeinfo&oeid=@@row.VON_OE@@
@decoration.decorate_colcell.0.hyperlink.title	Informationen über Organisationseinheit @@row.VON_OE@@ anzeigen
@decoration.decorate_colcell.1.hyperlink.href	/dquery?dataname=grpasinfo&uskurzz=@@row.USKURZZ@@&usgroup=@@groupid@@&role=@@row.ROLEID@@&oe=@@row.VON_OE@@
@decoration.decorate_colcell.1.hyperlink.title	Details der Zuordnung von @@row.Name@@ mit Kurzzeichen @@row.USKURZZ@@ als @@row.Rolle@@ von @@row.VON_OE@@ zu Gruppe @@groupid@@ anzeigen
@decoration.decorate_colcell.2.hyperlink.href	/dquery?dataname=personinfo&uskurzz=@@row.USKURZZ@@
@decoration.decorate_colcell.2.hyperlink.title	Details über Person @@row.Name@@ anzeigen
@decoration.decorate_colcell.3	$MAILTO
@decoration.decorate_colcell.4	$TEL
@decoration.decorate_colcell.5	$TEL