#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_execSqlSeqFromText.txt
#	DESCRIPTION:	DQUERY definition for CCDB DQUERY "execSqlSeqFromText"
#
@querytitle	SQL-Sequenz aus Text in KFOQSDB ausführen
@querydescription	
@group	ADMINTEXTFRAG
@attributenames	textid:textid:{{textidoptions}},targetcfgname:kfodbconfigname:{{targetcfgnameoptions}},targetcfgversion:kfoqsdbversion,opmode:string:{{opmodeoptions}}
@textidoptions			{	labeltext:	"SQL-Text-ID",		typedesc:	"Text-ID, die das auszuführende SQL enthält"}
@targetcfgnameoptions	{	labeltext:	"Ziel-Datenbank",	typedesc:	"Name der Datenbank-Zugriffs-Konfiguration, in der das SQL ausgeführt werden soll"	}
@opmodeoptions			{	labeltext:	"Modus",			typedesc:	"SQLEXEC: alle SQLs ausführen, SQLDOC: SQLs und Kommentare zur DOkumentation ausgeben", is_optional:	true }
@querytype	function
@function	seqtrans.seqtrans
@sql_select	select distinct ident from CCTEXTFRAG where ident=::textid:: 
@title	SQL-Sequenz [[textid]] in Datenbank [[targetcfgname]] ausgeführt

~query.tsteps

#
# this sequence of steps will be executed for each record in the input dataset
# (for documentation see http://zpvwiki.sozvers.at/wiki/CCDB_Abfragen_der_Type_function#seqtrans.seqtrans)
#
# specifically, for execSqlSeqFromText, each input row is assumed to contain one text ID
# each text ID is assumed to be a text containing one SQL statement to be executed
#

#
# 0: initialize once (REM: this could have been done in [init.qexpression] but is more convenient here
#
qexpression
  proc: {
	if (!this.is_setup) {							/* initialize only once (also if more than one record in input dataset) */ 
		this.execid = aux.uniqueNowID();
		this.dname = this.dataname;
		ppush(this,[phead(this,"PROCESSING_STARTED"),"time="+aux.noweutime()]);
		this.is_setup = true;
	}
	this.textid = this.cdata.ident;
	if (!this.textid) {
		this.errcoll.collect(null,"textid is not given in query",this.query);
	}
	this.configname = this.query.targetcfgname;
	if (!this.configname) {
		this.errcoll.collect(null,"targetcfgname is not given in query",this.query);
	}
	this.targetcfgversion = this.query.targetcfgversion;
	if (!this.targetcfgversion) {
		this.errcoll.collect(null,"targetcfgversion is not given in query",this.query);
	}
	if (this.query.opmode) {
		if (this.query.opmode=="SQLEXEC") {
			this.opmode = "SQLEXEC";
		} else if (this.query.opmode=="SQLDOC") {
			this.opmode = "SQLDOC";
		} else {
			this.errcoll.collect(null,"illegal opmode "+this.query.opmode+", valid are: SQLEXEC,SQLDOC", { query: this.query });
		}
	} else {
		this.opmode = "SQLEXEC";
	}
	if (this.opmode=="SQLDOC") {
		this.doctext = "-- SQL-Dokumentation von CCD_Text-Ident \"" + this.textid + "\"\n";
	}
	if (this.errcoll.hasErrors()) {
		logger.error("Error in execSqlSeqFromText: "+aux.objTxt(this.errcoll.getErrorResult()));
		break proc;
	}
	if (this.debug) logger.debug(this.dname+".START.EXECID="+this.execid+".TSTEP="+this.tstepnum+"/0.textid="+this.textid+".configname="+this.configname);
	ppush(this,["WILL_EXECUTE_SQLSEQ","time="+aux.noweutime(),"ident="+this.textid,"configname="+this.configname,""]);
  }

#
# 1: execute by calling subroutine
#
gosub			process_text
params.textid	@@textid@@
params.tcname	@@configname@@
	
#
# 2: end the processing
#
pre_qexpression
	ppush(this,[phead(this,"PROCESSING_ENDED"),"time="+aux.noweutime(),"",""]);
	if (this.opmode=="SQLDOC") {
		this.ppush(["SQLDOC-Text:",this.doctext]);
	}
end

#
# ==============================================================================
#		SUBROUTINE:		process_text
#		INPUT:			textid		-	ID of text in CCTEXTFRAG to be processed
#						tcname		-	DB config name of database to execute index
#		DESCRIPTION:	executes all SQLSEQ-Texts of <textid> as SQL-statements
#						in database <tcname>
#

#
# 3: subroutine for iterative processing of one text
#
# expects textid,tcname in params
#
process_text:
qexpression
	if (this.debug) logger.debug(this.dataname+".process_text. parameters: "+aux.objTxt(this.local.params));
	if (this.pcallback) this.pcallback({ fname: this.fname, dataname: this.dataname, master_text_id: this.textid, current_textid: this.local.params.textid});
	
#
# 4: get all sub-texts
#
pre_qexpression
	if (this.debug) logger.debug(this.fname+"("+this.dataname+").TSTEP="+this.tstepnum+".getSubTexts.textid="+this.local.params.textid);
sql select distinct ident,child_index from CCTEXTFRAG fs where fs.parent=::textid:: order by child_index asc
sqlparams textid
post_qexpression
	this.local.subtextresult = new aux.Result(this.last_sql_result);
	if (this.debug) logger.debug(this.fname+"("+this.dataname+").TSTEP="+this.tstepnum+".SUBTEXTCOUNT="+this.local.subtextresult.getRowCount()+".RESULT: "+aux.objTxt(this.local.subtextresult));

#
# 5: process sub-texts before our own text
#
if (this.local.subtextresult.getRowCount()<1)
goto do_own

#
# 6: setup loop for processing sub-elements
#
qexpression
	this.local.subtexttoproc = this.local.subtextresult.getRowCount();
	this.local.subtextindex = 0;

#
# 7: all sub-texts done?
#
do_sub:
if (this.local.subtexttoproc<1)
goto do_own

#
# 8: prepare current sub-element
#
qexpression	
	this.cdata.subident = this.local.subtextresult.getCellValue(this.local.subtextindex,0);
	if (this.debug) logger.debug(this.dname+".EXECID="+this.execid+".Subtext.index="+this.local.subtextindex+".ident="+this.cdata.subident);
	ppush(this,[phead(this,"WILL_EXECUTE_SUBTEXT"),"ident="+this.cdata.subident,"on_cfgname="+this.local.params.tcname,""]);

#
# 9: process sub-element by recursively invoking myself
#
pre_qexpression
	if (this.debug) logger.debug(this.dname+".RECURSIVE_CALL.SUBIDENT="+this.cdata.subident)
gosub			process_text
params.textid	@@subident@@
params.tcname	@@tcname@@

#
# 10: move to next sub-element
#
qexpression
	this.local.subtextindex++;
	this.local.subtexttoproc--;
	if (this.debug) logger.debug(this.dname+".Subtext.Next.index="+this.local.subtextindex+".to_go="+this.local.subtexttoproc);
	
#
# 11: rinse and repeat ...
#
goto do_sub
	
#
# 12: process own elements that are NOT SQLSEQ
#
do_own:
pre_qexpression
	if (this.debug) logger.debug(this.dname+".getOwnElements.TSTEP="+this.tstepnum+".TEXTID="+aux.getn([this.cdata,this.local.params,this.local,this],"textid"));
sql select type,ident,child_index,frag_index,data from CCTEXTFRAG fg where fg.type<>'SQLSEQ' and fg.ident=::textid:: order by child_index,frag_index asc
sqlparams textid
post_qexpression
	this.local.cmdresult = new aux.Result(this.last_sql_result);
	this.local.cmdstoprocess = this.local.cmdresult.getRowCount();
	this.local.cmdprocindex = 0;
	
#
# 13: more commands to be processed?
#
do_cmd:
if (this.local.cmdstoprocess<1)
goto do_own_sqlseq

#
# 14: prepare current commands
#
qexpression
	this.local.cmdtoken = this.local.cmdresult.getCellValue(this.local.cmdprocindex,0);
	this.local.cmddata  = this.local.cmdresult.getCellValue(this.local.cmdprocindex,4);
	switch (this.local.cmdtoken) {
		case 'SQLMAC':
		case 'SQLDESC':
		case 'REPDEF':
		case 'REPHEAD':
		case 'TITLE':
			this.local.cmdsubname = 'CMDSUB_'+this.local.cmdtoken;
			break;
		default:
			this.errcoll.collect(null,"illegal SQL sequence command \""+this.local.cmdtoken+"\"",this.local);
	}
	if (!this[this.local.cmdsubname]) {
		this.errcoll.collect(null,"seqtrans-command-subroutine label \""+this.local.cmdsubname+"\" not defined");
	}
	
#
# <<TSTEPNUM:15>>: dispatch to command type
#
pre_qexpression
	logger.debug(this.dname+".TSTEP_UDBG.DISPATCH_CMDSUB.CMDSUBNAME="+this.local.cmdsubname+".CMDDATA="+this.local.cmddata);
gosub			@@cmdsubname@@
params.cmddata	@@cmddata@@
params.tcname	@@tcname@@

#
# <<TSTEPNUM:16>>: move to next command
#
qexpression
	this.local.cmdstoprocess--;
	this.local.cmdprocindex++;

#
# <<TSTEPNUM:17>>: rinse and repeat ..
#
goto do_cmd

#
# <<TSTEPNUM:18>>: get all SQLSEQ text fragments for our own text
#
# ATTENTION: we must get each fragment as one row (and concatenate them in post_qexpression) 
#            as group_concat() truncates the resulting string to 1024 chars
#
do_own_sqlseq:
pre_qexpression
	if (this.debug) logger.debug(this.dname+".getOwnTextFragments.TSTEP="+this.tstepnum+".TEXTID="+aux.getn([this.cdata,this.local.params,this.local,this],"textid"));
sql select type,ident,child_index,frag_index,data from CCTEXTFRAG fg where fg.type='SQLSEQ' and fg.ident=::textid:: order by child_index,frag_index asc
sqlparams textid
post_qexpression
	this.local.textresult = new aux.Result(this.last_sql_result);
	if (this.local.textresult.getRowCount()<1) {
		this.protocol.push(["NO_SQLSEQ_TEXTDATA","textid="+this.local.params.textid,"on_cfgname="+this.local.params.tcname,""]);
		this.local.sqldata = "";
	} else {
		let ri;
		let notfirst = false;
		this.local.sqldata = "";
		for (ri=0; ri<this.local.textresult.getRowCount(); ri++) {
			if (notfirst) this.local.sqldata += " ";
			notfirst = true;
			this.local.sqldata += this.local.textresult.getCellValue(ri,4);
		}
		this.protocol.push(["SQL_TO_EXEC","textid="+this.local.params.textid,"on_cfgname="+this.local.params.tcname,this.local.sqldata]);
	}

#
# <<TSTEPNUM:19>>: skip SQL execution if no data
#
if		(!this.local.sqldata)
goto	sql_done
return

#
# <<TSTEPNUM:20>>: execute the concatenated SQL in dbconfig <configname>
#
sql		@@sqldata@@
cfgname	@@tcname@@
post_qexpression
	this.local.sqlresult = new aux.Result(this.last_sql_result);
	this.protocol.push(["SQL_EXECUTED","","",aux.objTxt(this.local.sqlresult)]);

#
# <<TSTEPNUM:21>>: all done for this text
#
sql_done:
qexpression
	ppush(this,[phead(this,"EXECSQL_DONE"),"ident="+this.local.params.textid,"on_cfgname="+this.local.params.tcname,""]);
	
#
# <<TSTEPNUM:22>>: return to caller
#
return

#
# --------------------------------------------
#

#
# 23: subroutine CMDSUB_SQLMAC - process SQL macro
#
CMDSUB_SQLMAC:
qexpression
	stp: {
		try {
			if (this.local.params.cmddata.startsWith("{")) {
				this.local.params.cmddata = "this.temp = "+this.local.params.cmddata;
			}
			this.local.macparams = eval(this.local.params.cmddata);
			if (!this.local.macparams.macro) {
				this.errcoll.collect(null,"macro not defined in SQLMAC setup expression: \""+this.local.params.cmddata+"\"");
				break stp;
			}
			this.local.macro = this.local.macparams.macro;
			this.local.macsub = 'SQLMACSUB_'+this.local.macro;
			if (!this.labels[this.local.macsub]) {
				this.errcoll.collect(null,"subroutine label for SQLMAC "+this.local.macro+" does not exist");
				break stp;
			}
		} catch (e) {
			this.errcoll.collect(e,"error processing SQLMAC setup expression: \""+this.local.params.cmddata+"\"");
			break stp;
		}
	}
	
#
# <<TSTEPNUM:24>>: execute the SQLMAC MACRO
#
gosub			@@macsub@@
params.cmddata	@@cmddata@@
params.tcname	@@tcname@@

#
# <<TSTEPNUM:25>>: SQLMAC finished
#
return

#
# --------------------------------------------
#

#
# <<TSTEPNUM:26>>: subroutine SQLMACSUB_registerKFOQSDBreport
#
SQLMACSUB_registerKFOQSDBreport:
if		(this.opmode=="SQLDOC")
goto	SRKR_create_doc

#
# <<TSTEPNUM:27>>: 
#
qexpression	
	this.local.macname = 'registerKFOQSDBreport';
	stp: {
		if (this.targetcfgversion!='1.5' && this.targetcfgversion!='1.6') {
			this.errcoll.collect(null,"Unrecognized target KFOQSDB version \""+this.targetcfgversion+"\" in "+this.local.macname,this.local);
			break stp;
		}
		this.local.isKFO16 = (this.targetcfgversion=='1.6')?true:false;
		try {
			if (this.local.params.cmddata.startsWith("{")) {
				this.local.params.cmddata = "this.temp = "+this.local.params.cmddata;
			}
			this.local.macdata = eval(this.local.params.cmddata);	
		} catch (e) {
			this.errcoll.collect(e,"Error evaluating cmddata in "+this.local.macname,this.local.params.cmddata);
			break stp;
		}
		if (!this.local.macdata.name) {
			this.errcoll.collect(null,this.local.macname+": <name> not given",this.local.macdata);
			break stp;
		}
		if (!this.local.macdata.sql) {
			this.errcoll.collect(null,this.local.macname+": <sql> not given",this.local.macdata);
			break stp;
		}
		if (!this.local.macdata.role) {
			this.errcoll.collect(null,this.local.macname+": <role> not given",this.local.macdata);
			break stp;
		}
		if (this.local.isKFO16) {
			if (!this.local.macdata.hasOwnProperty("personenbezug")) {
				this.errcoll.collect(null,this.local.macname+": <personenbezug> not given",this.local.macdata);
				break stp;
			}
		}
		if (!this.local.params.tcname) {
			this.errcoll.collect(null,this.local.macname+": parameter <tcname> not given",this.local.params);
			break stp;
		}
		this.local.repname = this.local.macdata.name;
		this.local.repsql  = this.local.macdata.sql;
		this.local.reprole = this.local.macdata.role;
		this.local.reppers = this.local.macdata.personenbezug?true:false;
	}

#
# <<TSTEPNUM:28>>: check if the report is already registered
#
sql			select "ID","name" from "reporttype" where "name"=::repname::
cfgname		@@tcname@@
sqlparams 	repname
post_qexpression
	this.local.chkpresresult = new aux.Result(this.last_sql_result);
	this.local.repprescount = this.local.chkpresresult.getRowCount();
	if (this.local.repprescount>1) {
		this.errcoll.collect(null,"there are too many entries in reporttype for report name \""+this.local.repname+"\"",this.local.chkpresresult);
	}
	if (this.local.repprescount>0) {
		this.local.reppresid = this.local.chkpresresult.getCellValue(0,0);
		this.local.reppresname = this.local.chkpresresult.getCellValue(0,1);
	}

#
# <<TSTEPNUM:29>>: if no report entry present, go directly to creation of new reporttype entry
#
if (this.local.repprescount<1)
goto SRKR_create

#
# <<TSTEPNUM:30>>: check if we're 1.5, so we can skip the test for referencing report instances
#
if		(!this.local.isKFO16)
goto 	SRKR_delete_old

#
# <<TSTEPNUM:31>>: check if there is an generated "Auswertung" referring to our reporttype
#
sql 		select "ID" from "Auswertung" where "reportid"=::reppresid::
sqlparams	reppresid
cfgname		@@tcname@@
post_qexpression
	this.local.repisinuse = (this.last_sql_result.getRowCount()>0);

#
# <<TSTEPNUM:32>>: if referenced, have to go to hiding
#
if		(this.local.repisinuse)
goto	SRKR_hide

#
# <<TSTEPNUM:33>>: not referenced, simply delete old registration record
#
SRKR_delete_old:
sql			delete from "reporttype" where "ID"=::reppresid::
sqlparams	reppresid
cfgname		@@tcname@@
post_qexpression
	this.protocol.push(["OLDREPDELETED","ID="+this.local.reppresid,"",""]);

#
# <<TSTEPNUM:34>>: proceed with creating new record
#
goto	SRKR_create

#
# <<TSTEPNUM:35>>: hide old report registration
#
SRKR_hide:
pre_qexpression
	this.local.hidden_repname = "HIDDEN_"+aux.nowstring()+"_"+this.local.reppresname;
	this.local.hidden_id = this.local.reppresid;
sql			update "reporttype" set "name"=::hidden_repname::,"role"='INVISIBLE' where "ID"=::hidden_id::
sqlparams	hidden_repname,hidden_id
cfgname		@@tcname@@
post_qexpression
	this.protocol.push(["OLDREPHIDDEN","ID="+this.local.hidden_id,"HIDDEN_NAME="+this.local.hidden_repname,""]);

#
# <<TSTEPNUM:36>>: create new reporttype entry, decide 015 or 016
#
SRKR_create:
if (this.local.isKFO16)
goto	SRKR_create_016

#
# <<TSTEPNUM:37>>: create 1.5 report entry
#
SRKR_create_015:
sql			insert into "reporttype"("sql",      "version", "Erstelldatum", "role",      "type", "name") 
			                  values(::repsql::, 1,         NOW(),          ::reprole::, 'USER', ::repname::)
			returning "ID"
sqlparams	repname,repsql,reprole
cfgname		@@tcname@@
post_qexpression
	this.local.created_id = this.last_sql_result.getCellValue(0,0);
	this.protocol.push(["NEWREPCREATED","ID="+this.local.created_id,"NAME="+this.local.repname,""])
then_goto	SRKR_create_done

#
# <<TSTEPNUM:38>>: create 1.6 report entry
#
SRKR_create_016:
sql			insert into "reporttype"("sql",      "version", "Erstelldatum", "role",      "type", "name",      "personenbezug") 
			                  values(::repsql::, 1,         NOW(),          ::reprole::, 'USER', ::repname::, ::reppers::)
			returning "ID"
sqlparams	repname,repsql,reprole,reppers
cfgname		@@tcname@@
post_qexpression
	this.local.created_id = this.last_sql_result.getCellValue(0,0);
	this.protocol.push(["NEWREPCREATED","ID="+this.local.created_id,"NAME="+this.local.repname,""])

#
# <<TSTEPNUM:39>>: report registration done
#
SRKR_create_done:
return

#
# <<TSTEPNUM:40>>: report creation documentation
#
SRKR_create_doc:
qexpression
	this.doctext += "\n" +
					"insert into \"reporttype\"(\"sql\",\"version\",\"Erstelldatum\",\"role\",\"role\",\"type\",\"name\",\"personenbezug\")"+
					"values(\""+this.repsql+"\",\"1\",\""+aux.nowsvtime()+"\",\""+this.reprole+"','USER','"+this.repname+"','"+this.reppers+"')";
then_goto	SRKR_create_done

#
# --------------------------------------------
#

#
# <<TSTEPNUM:41>>: subroutine CMDSUB_SQLDESC - process SQL description
#
CMDSUB_SQLDESC:
qexpression
	if (this.debug) logger.debug(this.dname+".SQLDESC=\""+this.local.params.cmddata+"\"");
	ppush(this,[phead(this,"SQLDESCRIPTION"),"target_cfgname="+this.local.params.tcname,"",this.local.params.cmddata]);
	if (this.opmode=="SQLDOC") this.doctext += "\n" + this.local.params.cmddata;

#
# <<TSTEPNUM:42>>: SQLDESC complete
#
return

#
# --------------------------------------------
#

#
# <<TSTEPNUM:42>>: subroutine CMDSUB_REPDEF - process report definition header
#
CMDSUB_REPDEF:
qexpression
	if (this.debug) logger.debug(this.dname+".REPDEF=\""+this.local.params.cmddata+"\"");
	ppush(this,[phead(this,"REPDEF"),"target_cfgname="+this.local.params.tcname,"",this.local.params.cmddata]);
	if (this.opmode=="SQLDOC") this.doctext += "\n" + this.local.params.cmddata;

#
# <<TSTEPNUM:43>>: REPDEF complete
#
return

#
# --------------------------------------------
#

#
# <<TSTEPNUM:44>>: subroutine CMDSUB_REPHEAD - process report definition group header
#
CMDSUB_REPHEAD:
qexpression
	if (this.debug) logger.debug(this.dname+".REPHEAD=\""+this.local.params.cmddata+"\"");
	ppush(this,[phead(this,"REPHEAD"),"target_cfgname="+this.local.params.tcname,"",this.local.params.cmddata]);
	if (this.opmode=="SQLDOC") this.doctext += "\n" + this.local.params.cmddata;
#
# <<TSTEPNUM:45>>: REPHEAD complete
#
return

#
# --------------------------------------------
#

#
# <<TSTEPNUM:46>>: subroutine CMDSUB_TITLE - process report definition description title
#
CMDSUB_TITLE:
qexpression
	if (this.debug) logger.debug(this.dname+".TITLE=\""+this.local.params.cmddata+"\"");
	ppush(this,[phead(this,"TITLE"),"target_cfgname="+this.local.params.tcname,"",this.local.params.cmddata]);
	this.outputmfiledescription = this.local.params.cmddata+" "+this.outputmfiledescription;
	if (this.opmode=="SQLDOC") this.doctext += "\n" + this.local.params.cmddata;

#
# <<TSTEPNUM:47>>: TITLE complete
#
return