#
#	ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_SVCL57CLASSIFY.txt
# 	DESCRIPTION:	dquery definition for SVCL57CLASSIFY
#
# 
@group			SVCL57
@querytitle 	Alle (noch) nicht klassifizierten SVCLEAR-57 bounce-emails klassifizieren
@description	klassifiziert alle Einträge in SVCL57NRCBMA , die noch BOUNCE_CLASSIFICATION="UNCLASSIFIED" haben
@attributenames	sql_select:string:{{sql_selectoptions}}
@sql_select		SELECT * FROM SVCL57NRCBMA where BOUNCE_CLASSIFICATION='UNCLASSIFIED'
@sql_selectoptions	{"labeltext":"Auswahl-SQL-Statement","typedesc":"SQL-Statement für Abfrage in SVCL57NRCBMA um die zu klassifizierenden Einträge auszuwählen"}
@querytype		function
@function		seqtrans.seqtrans

~query.tsteps

#
# <<TSTEPNUM:0>>: 
#
if	(this.init_done)
goto	record_proper

#
# <<TSTEPNUM:1>>: initialize iterators
#
qexpression
	proc: {
		if (!this.init_done) {
			this.startstime = aux.nowstring();
			this.startmillis = Date.now();
			this.recsprocessed = 0;
			this.recstoprocess = this.inputresult.getRowCount();
			this.protocol = new Array();
			this.fresults = new Array();
			this.ppush([this.phead("started"),"TIME="+aux.nowsvtime(),"RECORDS_TO_PROCESS="+this.recstoprocess,""]);
		}
	}

#
# <<TSTEPNUM:2>>: matchString()
#
qexpression
	proc: {
			this.matchString = function(target,regex) {
				let r = false;
				let match;
				if (target) {
					let re = new RegExp(regex);
					match = re.exec(target);
					if (match) return true;
				}
				return r;
			}
	}

#
# <<TSTEPNUM:3>>: captureString()
#
qexpression
	proc: {
		this.captureString = function(target,regex) {
			let r = null;
			let match;
			if (target) {
				let re = new RegExp(regex);
				match = re.exec(target);
				if (match && match.length>0) {
					r = match[1];
				}
			}
			return r;
		}
	}

#
# <<TSTEPNUM:4>>: lookupRegexList(target,lookup) - find a match from a list of possible regexes
#
qexpression
	proc: {
		this.SVTlookup = {
			"11":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sWien\\s*$",
			"12":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sNieder.*sterreich\\s*$",
			"13":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sBurgenland\\s*$",
			"14":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sOber.*sterreich\\s*$",
			"15":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sSteiermark\\s*$",
			"16":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sK.*rnten\\s*$",
			"17":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sSalzburg\\s*$",
			"18":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sTirol\\s*$",
			"19":		"^\\s*.sterreichische\\sGesundheitsk.+se\\sVorarlberg\\s*$",
			"05":		"^\\s*BVAEB.*Eisenbahn.*Bergbau\\s*$"
		}
		this.lookupRegexList = function(target,lookup) {
			for (name in lookup) {
				if (this.matchString(target,lookup[name])) return name;
			}
			return null;
		}
	}
	
#
# <<TSTEPNUM:5>>: matchText(msg,regex)
#
qexpression
	proc: {
			this.matchText = function(msg,regex) {
				let r = false;
				if (msg.text) {
					if (this.matchString(msg.text,regex)) return true;
				}
				if (!msg.texts) return false;
				for (let i = 0; i<msg.texts.length; i++) {
					if (this.matchString(msg.texts[i],regex)) return true;
				}
				return false;
			}
	}

#
# <<TSTEPNUM:6>>:	captureText(msg,regex)
#
qexpression
	proc: {
			this.captureText = function(msg,regex) {
				let r;
				if ((r = this.captureString(msg.text,regex))) return true;
				if (!msg.texts) return null;
				for (let i = 0; i<msg.texts.length; i++) {
					if ((r = this.captureString(msg.texts[i],regex))) return r;
				}
				return null;
			}
		}

#
# <<TSTEPNUM:7>>:	matchSubject(msg,regex)
#
qexpression
	proc: {
		this.matchSubject = function(msg,regex) {
			if (!msg) {
				this.errcoll.collect(null,"cannot matchSubject, no msg",{ msg: msg });
				return false;
			}
			if (!msg.subject) {
				this.errcoll.collect(null,"cannot matchSubject, no subject in msg",{ msg: msg });
				return false;
			}			
			return this.matchString(msg.subject,regex);
		}
	}
	
#
# <<TSTEPNUM:8>>:	captureSubject()
#
qexpression
	proc: {
		this.captureSubject = function(msg,regex) {
			if (!msg) {
				this.errcoll.collect(null,"cannot captureSubject, no msg",{ msg: msg });
				return false;
			}
			if (!msg.subject) {
				this.errcoll.collect(null,"cannot captureSubject, no subject in msg",{ msg: msg });
				return false;
			}
			return this.captureString(msg.subject,regex);
		}
	}

#
# <<TSTEPNUM:9>>: identifyCFnotification()
#
qexpression
	proc: {
		this.identifyCFnotification = function(msg, holder, prefix) {
			let bknrpart,bknr,svtnamepart,notificationreceiver;
			let bknrs = null, svtnames = null, svts = null;
			let i;
			if (!holder) holder = this;
			let neueCF = this.matchSubject(msg,"^Neue Clearingf.*lle eingelangt.*$");
			let urgenzCF = this.matchSubject(msg,"^Urgenz Clearingf.*lle.*$");
			if (neueCF || urgenzCF) {
				if ((msg.CLS_TO_01) && (msg.CLS_TO_01.length>0)) {
					notificationreceiver = msg.CLS_TO_01;
				} else {
					notificationreceiver = null;
				}
				bknrpart = this.captureText(msg,"^.*Betroffene Beitragskontonummer.+?([0-9]{4,11}.*)$");
				if (bknrpart) {
					bknrpart = bknrpart.replace("\r\n","\n").replace("\n\r","\n").replace("\r","\n").replace("\n"," ");
					bknrs = bknrpart.split(" ");
					for (i=0; i<bknrs.length; i++) {
						bknrs[i] = captureString(bknrs[i],"^\\s*([0-9]{4,11}).*$");
					}
				}
				svtnamepart = this.captureText(msg,"^.*Bei fachlichen Fragen wenden Sie sich bitte an die\\s(.+?)\\..*$");
				if (svtnamepart) {
					svtnames = svtnamepart.split(",");
					svts = new Array();
					for (i=0; i<svtnames.length; i++) {
						svts[i] = this.lookupRegexList(svtnames[i],this.SVTnameLookup);
					}
				}
				if ((bknrpart) && (svtnamepart)) {
					if (!holder) holder = {};
					if (!holder.attributes) holder.attribbutes = {};
					holder.attributes.CF_NOTIF_TYP = (neueCF?"CF_EINGELANGT":(urgenzCF?"CF_URGENZ":"UNBEKANNT"));
					if (svts) {
						for (i=0; i<svts.length; i++) {
							holder.attributes["CF_ZU_SVT_"+aux.DEC(i,2,2)] = svts[i];
						}
					}
					if (bknrs) {
						for (i=0; i<bknrs.length; i++) {
							holder.attributes["CF_ZU_BKNR_",aux.DEC(i,2,2)] = bknrs[i];
						}
					}
					if (svtnames) {
						for (i=0; i<svtnames.length; i++) {
							holder.attributes["CF_ZU_SVTNAME_"+aux.DEC(i,2,2)] = svtnames[i];
						}
					}
					holder.attributes.CF_EMPF = notificationreceiver;
					holder.attributes.CF_GESENDET = aux.serialDateTime(holder.txdate);
					holder.attributes.CF_EMPFANGEN = aux.serialDateTime(holder.rxdate);
					return true;
				}
			}
			if (!msg.parts) return false;
			for (i = 0; i < msg.parts.length; i++) {
				if (identifyCFnotification(msg.parts[i],holder,prefix+"%s.SUB"+DEC(i,2,2))) return true;
			}
			return false;
		}
	}

	

#
# <<TSTEPNUM:10>>:	identifyCFreceiverUnreachable(msg,holder,prefix)
#
qexpression
	proc: {
		this.identifyCFreceiverUnreachable = function(msg,holder,prefix) {
			let deliveryStatus = this.matchSubject(msg,"^Delivery Status Notification.*$");
			let failedEmailAddress = "";
			let unreachable = false;
			let unrcause = "";
			if (!holder) holder = msg;
			if (!holder.attributes) holder.attributes = {};
			if ((failedEmailAddress = this.captureText(msg,"^.*The following message to\\s(.*?)\\swas undeliverable.*$"))) {
					holder.attributes.BOUNCE_CLASSIFICATION = "UNREACHABLE";
					holder.attributes.UNREACH_EMAIL = failedEmailAddress;
					holder.attributes.UNREACH_CAUSE = "StatusNotificationUndeliverable";
					holder.attributes.CHANGED = true;
					unreachable = true;
			}
			if (unreachable) return true;
			unrcause = "no known reason for unreachability";
			if (!unreachable) {
				unreachable = this.matchText(msg,"^.*RecipientNotFound.*$");
				if (unreachable) unrcause = "RecipientNotFound";
			}
			if (!unreachable) {
				unreachable = this.matchText(msg,"^.*could\\snot\\sbe\\sdelivered.*$");
				if (unreachable) unrcause = "could not be delivered";
			}
			if (!unreachable) {
				unreachable = this.matchText(msg,"^.*essage.*undeliverable.*$");
				if (unreachable) unrcause = "message undeliverable";
			}
			if (!unreachable) {
				unreachable = this.matchText(msg,"^.*Delivery\\shas\\sfailed.*$");
				if (unreachable) unrcause = "Delivery has failed";
			}
			if (unreachable) {
				if (!(failedEmailAddress = this.captureText(msg,"^.*Original\\-Recipient\\:\\srfc822\\;(.*?)\\s.*$"))) {
					failedEmailAddress = "**unknown**";
				}
				holder.attributes.BOUNCE_CLASSIFICATION = "UNREACHABLE";
				holder.attributes.UNREACH_EMAIL = failedEmailAddress;
				holder.attributes.UNREACH_CAUSE = unrcause;
				holder.attributes.CHANGED = true;
				unreachable = true;
			}
			if (unreachable) return true;
			if (!msg.parts) return false;
			if (!Array.isArray(msg.parts)) return false;
			for (let i = 0; i < msg.parts.length; i++) {
				if (!msg.parts[i].attributes) msg.parts[i].attributes = {};
				if (this.identifyCFreceiverUnreachable(msg.parts[i],holder,prefix+"SUB"+aux.DEC(i,2))) return true;
			}
			return false;
		}
	}

#
# <<TSTEPNUM:11>>: identifyCFreceiverAbsent(msg,holder,prefix)
#
qexpression
	proc: {
		this.identifyCFreceiverAbsent = function(msg,holder,prefix) {
			if (!holder) holder = msg;
			if (!holder.attributes) holder.attributes = {};
			let absent = this.matchSubject(msg,"^.*Abwesend:.*$");
			if (!absent) absent = this.matchSubject(msg,"^.*ch\\*bin.*nicht\\serreichbar.*$");
			if (absent) {
				holder.attributes.BOUNCE_CLASSIFICATION = "ABSENT";
				holder.attributes.CHANGED = true;
				return true;
			}
			if (!msg.parts) return false;
			for (let i = 0; i < msg.parts.length; i++) {
				if (this.identifyCFreceiverAbsent(msg,holder,prefix + ".SUB" + aux.DEC(i,2,2) )) return true;
			}
			return false;
		}
	}

#
# <<TSTEPNUM:12>>: identifyCFreceiverMailboxFull(msg,holder,prefix)
#
qexpression
	proc: {
		this.identifyCFreceiverMailboxFull = function(msg,holder,prefix) {
			if (!holder) holder = msg;
			if (!holder.attributes) holder.attributes = {};
			let boxfull = this.matchSubject(msg,"^.*Das\\sPostfach\\sdes\\sEmpf.+ngers\\sist\\svoll.*$");
			if (boxfull) {
				holder.attributes.BOUNCE_CLASSIFICATION = "RXBOXFULL";
				holder.attributes.CHANGED = true;
				return true;
			}
			if (!msg.parts) return false;
			for (let i = 0; i < msg.parts.length; i++) {
				if (this.identifyCFreceiverMailboxFull(msg.parts[i], holder, prefix + ".SUB" + aux.DEC(i,2,2) )) return true;
			}
			return false;
		}
	}

#
# <<TSTEPNUM:13>>: classify(msg,prefix)
#
qexpression
	proc: {
		this.classify = function(msg,prefix) {
			if (!msg.attributes) msg.attributes = {};
			msg.attributes.CHANGED = false;
			let anam;
			for (let i = 0; i < 10; i++) {
				anam = "CLS_FROM_"+aux.DEC(i,2);
				msg.attributes[anam] = msg[anam];
			}
			for (let i = 0; i < 10; i++) {
				anam = "CLS_TO_"+aux.DEC(i,2);
				msg.attributes[anam] = msg[anam];
			}
			this.identifyCFnotification(msg,msg,prefix);
			let unr = this.identifyCFreceiverUnreachable(msg,msg,prefix);		if (unr) return;
			let absnt = this.identifyCFreceiverAbsent(msg,msg,prefix);			if (absnt) return;
			let mbfull = this.identifyCFreceiverMailboxFull(msg,msg,prefix);	if (mbfull) return;
		}
	}

#
# <<TSTEPNUM:14>>: init done
#
qexpression
	this.init_done = true;

#
# <<TSTEPNUM:15>>: process one record - first classify
#
record_proper:
pre_qexpression
	this.updlog = "-";
qexpression
	proc: {
		try {
			this.msg = JSON.parse(this.cdata.MSG_JSON);
			if (!this.msg) {
				this.errcoll.collect(null,"No message data to be classified",{ msg: this.msg, cdata: this.cdata });
				break proc;
			}
			if (!this.msg.attributes) this.msg.attributes = {};
			if (!this.msg.subject) {
				this.errcoll.collect(null,"no subject in message", {msg: this.msg, cdata: this.cdata });
				break proc;
			}
			this.classify(this.msg,"MSG_"+this.cdata.MSG_ID+"_CLASSIFY");
		} catch (e) {
			this.errcoll.collect(e,"Error processing MSG_JSON",{ cdata: this.cdata, errstack: aux.deepCopy(e.stack) });
			break proc;
		}
	}
post_qexpression
	this.classifystepresult = this.stepresult;
	
#
# <<TSTEPNUM:16>>: if the attributes have changed, update the database record
#
if		(!this.msg.attributes.CHANGED)
goto	after_update

#
# <<TSTEPNUM:17>>: update the database
#
pre_qexpression
	for (maname in this.msg) {
		if (this.cdata.hasOwnProperty(maname)) {
			this.cdata[maname] = this.msg[maname];
		}
	}
	this.cdata.MSG_JSON = JSON.encode(this.msg);
sql				update SVCL57NRCBMA set MSG_RECEIVED=::MSG_RECEIVED::,BOUNCE_CLASSIFICATION::BOUNCE_CLASSIFICATION::,
					UNREACH_EMAIL=::UNREACH_EMAIL::,
					CLS_FROM_00=::CLS_FROM_00::,CLS_FROM_01=::CLS_FROM_01::,CLS_FROM_02=::CLS_FROM_02::,CLS_FROM_03=::CLS_FROM_03::,CLS_FROM_04=::CLS_FROM_04::,
					CLS_TO_00=::CLS_TO_00::,CLS_TO_01=::CLS_TO_01::,CLS_TO_02=::CLS_TO_02::,CLS_TO_03=::CLS_TO_03::,CLS_TO_04=::CLS_TO_04::,
					CF_NOTIF_TYP=::CF_NOTIF_TYP,
					CF_ZU_SVT_00=::CF_ZU_SVT_00::,CF_ZU_SVT_01=::CF_ZU_SVT_01::,CF_ZU_SVT_02=::CF_ZU_SVT_02::,
					CF_ZU_SVT_03=::CF_ZU_SVT_03::,CF_ZU_SVT_04=::CF_ZU_SVT_04::,
					CF_ZU_BKNR_00=::CF_ZU_BKNR_00::,CF_ZU_BKNR_01=::CF_ZU_BKNR_01::,CF_ZU_BKNR_02=::CF_ZU_BKNR_02::,
					CF_ZU_BKNR_03=::CF_ZU_BKNR_03::,CF_ZU_BKNR_04=::CF_ZU_BKNR_04::,
					CF_ZU_SVTNAME_00=::CF_ZU_SVTNAME_00::,CF_ZU_SVTNAME_01=::CF_ZU_SVTNAME_01::,CF_ZU_SVTNAME_02=::CF_ZU_SVTNAME_02::,
					CF_ZU_SVTNAME_03=::CF_ZU_SVTNAME_03::,CF_ZU_SVTNAME_04=::CF_ZU_SVTNAME_04::,
					CF_EMPF=::CF_EMPF::,CF_GESENDET=::CF_GESENDET::,CF_EMPFANGEN=::CF_EMPFANGEN::,MSG_JSON=::MSG_JSON::
named_params	MSG_RECEIVED,BOUNCE_CLASSIFICATION,UNREACH_EMAIL,
				CLS_FROM_00,CLS_FROM_01,CLS_FROM_02,CLS_FROM_03,CLS_FROM_04,
				CLS_TO_00,CLS_TO_01,CLS_TO_02,CLS_TO_03,CLS_TO_04,CF_NOTIF_TYP,
				CF_ZU_SVT_00,CF_ZU_SVT_01,CF_ZU_SVT_02,CF_ZU_SVT_03,CF_ZU_SVT_04,CF_ZU_BKNR_00,CF_ZU_BKNR_01,CF_ZU_BKNR_02,CF_ZU_BKNR_03,CF_ZU_BKNR_04,
				CF_ZU_SVTNAME_00,CF_ZU_SVTNAME_01,CF_ZU_SVTNAME_02,CF_ZU_SVTNAME_03,CF_ZU_SVTNAME_04,CF_EMPF,CF_GESENDET,CF_EMPFANGEN,MSG_JSON
result_varname	ures
post_qexpression
	proc: {
		if (this.ures.resultobject.affectedRows!=1) {
			this.errcoll.collect(null,"UPDATE after classification affects more than one row",{ ures: this.ures, cdata: this.cdata });
			break proc;
		}
		this.updlog = "RECORDS_UPDATED="+this.ures.resultobject.affectedRows;
	}

#
# 16: one record done, write protocol entry for it 
#
after_update:
qexpression
	this.recsprocessed++;
	this.ppush([this.phead("record_processed"),"RECORD_PROCESSED="+this.recsprocessed,"MSG_ID="+this.cdata.MSG_ID,this.stepresult]);

#
# <<TSTEPNUM:18>>: check if this was the last record and skip final actions if not
#
if (!this.is_last_record)
goto record_done

#
# <<TSTEPNUM:19>>: finalize protocol, seqtrans will create a visible result from the protocol
#
qexpression
	this.finishstime = aux.nowstring().substring(0,14);
	this.endmillis = Date.now();
	this.elapsedmillis = this.endmillis - this.startmillis;
	this.elapsedseconds = this.elapsedmillis/1000;
	let totalrecs = 0;
	let totalmillis = 0;
	let cfr;
	if (this.fresults.length>0) {
		this.ppush(["Verarbeitungs-Statistik:","","",""]);
		this.ppush(["A,"B","C","D"]);
	}
	for (let fi=0; fi<this.fresults.length; fi++) {
		cfr = this.fresults[fi];
		this.ppush([cfr.A,cfr.B,cfr.C,cfr.D]);
	}
	this.overallrecspersecond = totalrecs/this.elapsedseconds;
	this.ppush(["AA","BB","CC","DD"]);
	this.query.toptext = 	"<table><tr><td>Vorgang begonnen:       </td><td>"+this.startstime+"</td></tr>"+
							"       <tr><td>Vorgang beendet:        </td><td>"+this.finishstime+"</td></tr>"+
							"	    <tr><td>Durchlaufzeit(Sekunden):</td><td>"+this.elapsedseconds+"</td></tr>"+
							"       <tr><td>Datensätze/Sekunde:     </td><td>"+this.overallrecspersecond+"</td></tr>"+
							"</table>";
	this.query.result_filename = "LOADRESULT_"+this.finishstime;
	this.ppush([this.phead("finished"),
				{ rawhtml:	"<p>TIME=" + aux.nowsvtime() + "<br/>ELAPSED_SECONDS="+(this.elapsedmillis/1000)+"</br>" },
				"RECORDS_PROCESSED="+this.recsprocessed,
				""]);
	this.query.title = " "+this.filesprocessed+" Dateien mit Auswertung_MeldungenClearingfaelle geladen";

#
# <<TSTEPNUM:20>>: end of record processing
#
record_done:
end