#
#	ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:		dquermfile_addperson.txt
#	DESCRIPTION:	DQUERY definition for CCDB DQUERY "addperson"
#
@querytitle	Eine Person hinzufügen
@querydescription	
@group	GROUPS
@attributenames			titelvorne:string:{{titelvorneoptions}},vorname:string:{{vornameoptions}},familienname:string:{{familiennameoptions}},titelhinten:string:{{titelhintenoptions}},uskurzz:string:{{uskurzzoptions}},Telefonnummer:string:{{telefonnummeroptions}},Email:string:{{emailoptions}}
@titelvorneoptions		{"labeltext":"Titel vorangestellt","typedesc":"Titel, dem Namen vorangestellt","is_optional":"true"}
@vornameoptions			{"labeltext":"Vorname","typedesc":"Vorname der neuen Person"}
@familiennameoptions	{"labeltext":"Familienname","typedesc":"Familienname der neuen Person"}
@titelhintenoptions		{"labeltext":"Titel hinten","typedesc":"Titel, dem Namen nachgestellt","is_optional":"true"}
@telefonnummeroptions	{"labeltext":"Telefon","typedesc":"Vollständige internationale Telefonnummer in der Form +43xxxxxxxxx","is_optional":"true"}
@emailoptions			{"labeltext":"Email-Adresse","typedesc":"Vollständige internationale Email-Adresse in der Form vorname.nachname@domain.root","is_optional":"true"}
@uskurzzoptions			{"labeltext":"Kurzzeichen (optional)","typedesc":"Kurzzeichen für die neue Person, wenn nicht angegeben, wird eines berechnet, wenn bereits vorhanden, wird es eindeutig gemacht","is_optional":"true"}
@querytype				function
@function				seqtrans.seqtrans
@input_mode				QUERY
@inputqueryattributes	vorname,familienname,uskurzz,titelvorne,titelhinten,Telefonnummer,Email
@init.qexpression		this.nameutils = require("./nameutils.js")(logger,this.dataname,prefs);
#	was: @tsteps_mfileid	gentext_00000000999999033 , now: TSTEPS in this very file!
@title					Anlage der Person {{vorname}} {{familienname}}
@toptext				Person <a href="/dquery?dataname=personinfo&uskurzz=[[new_uskurzz]]">{{vorname}} {{familienname}}</a> angelegt.

#
# tsteps definition for DQUERY addperson
#
~query.tsteps

#
# 0: determine if user entered a USKURZZ proposal, if not create one 
#
qexpression
	if (!this.setup) {
		ppush(this,[phead(this,"PROCESSING_STARTED"),"","",""]);
		this.setup = true;						/* ensure this is executed exactly once */
	}
	this.cdata = this.inputresult.getRowObject(this.recnum,"value");
	this.nameobject = {vorname: this.cdata.vorname, familienname: this.cdata.familienname};
	if (this.cdata.uskurzz) {
		this.new_uskurzz_root = this.cdata.uskurzz;
	} else {
		this.new_uskurzz_root = this.nameutils.genUskurzzProposal(this.nameobject);
	}
	this.new_uskurzz = this.new_uskurzz_root;

#
# 1: read from user database if the person from the input dataset is already known
#
sql	select USKURZZ from CCUS us where us.USVORNAME='@@vorname@@' and us.USFAMILIENNAME='@@familienname@@'

#
# 2: analyze data read from CCUS:
#	if we found an entry, we already have a person with this VORNAME/FAMILIENNAME, log it with the USKURZZ in the protocol
#	if not, prepare for creating a new CCUS entry by calculating a new (hopefully unique) value for USKURZZ
#
qexpression
	if (!this.last_sql_result) {
		this.errcoll.collect(null,"No result from last SQL select present");
	} else if (this.last_sql_result.hasRows()) {
		this.ukc = this.last_sql_result.findColumn("USKURZZ");
		if (this.ukc<0) {
			this.errcoll.collect(null,"result has no USKURZZ",this.last_sql_result);
			throw new Error("result has no USKURZZ");
		}
		this.old_uskurzz = this.last_sql_result.getCellValue(0,this.ukc);
		if (this.cdata.uskurzz) {
			if (this.cdata.uskurzz!=this.old_uskurzz) {
				this.info5 = "IST_NICHT="+this.cdata.uskurzz+",ABGEBROCHEN!";
			} else {
				this.info5 = "IST_GLEICH="+this.cdata.uskurzz+",KEINE_ÄNDERUNG."
			}
		} else {
			this.info5 = "KEINE_ÄNDERUNG";
		}
		this.inputresult.setCellValue(this.recnum,
                                this.kurzzcolumn,
                                this.uskurzz);
		this.cdata.USKURZZ= this.uskurzz;
		ppush(this,[phead(this,"PERSON_SCHON_BEKANNT"),"VORNAME="+this.cdata.VORNAME,"FAMILIENNAME="+this.cdata.FAMILIENNAME,"MIT_USKURZZ="+this.uskurzz,this.info5]);
		this.new_person = false;
	} else {
		this.new_person = true;
		this.new_uskurzz_ucount = 0;
		this.new_uskurzz = this.new_uskurzz_root;
		this.cdata.NEW_USKURZZ = this.new_uskurzz;
		this.cdata.USKURZZ = this.new_uskurzz;
		this.protocol.push(["PERSON_NOCH_NICHT_BEKANNT_WIRD_ANGELEGT","VORNAME="+this.cdata.vorname,"FAMILIENNAME="+this.cdata.familienname,"TRY_NEW_USKURZZ="+this.new_uskurzz_root,""]);
	}

# 3: if not a new person, skip the rest
if	!this.new_person
goto	RECDONE

# 4: new person, check if calculated NEW_USKURZZ is already in use
label	TRYNEWUSKURZZ
sql		select USVORNAME,USFAMILIENNAME from CCUS where USKURZZ='@@NEW_USKURZZ@@'

# 5: if we did not find an entry in CCUS with our NEW_USKURZZ, we can use it to create a new entry for our new person
if	!this.last_sql_result.hasRows()
goto	CREANEWUS

# 6: we found an entry in CCUS for our NEW_USKURZZ, this means it is already in use by another user record, try a new one (append (next) count to KURZZ-root)
qexpression	
	this.new_uskurzz = this.new_uskurzz_root+(++this.new_uskurzz_ucount);
	ppush(this,[phead(this,"NEUE_PERSON"),"NEW_USKURZZ="+this.cdata.NEW_USKURZZ,"BEREITS_VERWENDET",
	                    "NAME="+this.last_sql_result.getCellValue(0,0)+" "+this.last_sql_result.getCellValue(0,1),
						".NÄCHSTER_VERSUCH_USKURZZ="+this.new_uskurzz"]);
	this.cdata.NEW_USKURZZ = this.new_uskurzz;
	this.cdata.USKURZZ = this.new_uskurzz;
then_goto	TRYNEWUSKURZZ

# 7: create new active user record with VORNAME,FAMILIENNAME and calculated USKURZZ
label	CREANEWUS
sql	insert into CCUS(USKURZZ,USVORNAME,USFAMILIENNAME,USSTATUSCCUSTAT) values('@@NEW_USKURZZ@@','@@vorname@@','@@familienname@@','ON')
post_qexpression
	this.set_new_uskurzz = this.cdata.NEW_USKURZZ;
	ppush(this,[phead(this,"NEUE_PERSON_ANGELEGT"),"USKURZZ="+this.cdata.NEW_USKURZZ,"VORNAME="+this.cdata.vorname,"FAMILIENNAME="+this.cdata.familienname,""]);

# 8: check if input dataset has TITELVORNE, if not skip to check for TITELHINTEN
if		!this.cdata.titelvorne
goto	CHKITH

# 9: create a new entry in CCATT for the TITELVORNE of the new person
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('PERSINFO','CCUS','@@USKURZZ@@','TITELVORNE','@@titelvorne@@')
post_qexpression
	ppush(this,[phead(this,"NEUE_PERSON_TITELVORNE_ANGELEGT"),"USKURZZ="+this.cdata.NEW_USKURZZ,"TITELVORNE="+this.cdata.titelvorne,"",""]);

#
# 10: if there is no TITELHINTEN for the new person in the input dataset, skip to check on FON
#
CHKITH:
if	!this.cdata.titelhinten
goto	CHKITEL

#
# 11: create a new entry in CCATT for the TITELHINTEN of the new person
#
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('PERSINFO','CCUS','@@USKURZZ@@','TITELHINTEN','@@titelhinten@@')
post_qexpression	this.protocol.push(["NEUE_PERSON_TITELHINTEN_ANGELEGT","USKURZZ="+this.cdata.NEW_USKURZZ,"TITELHINTEN="+this.cdata.titelhinten,"",""]);

#
# 12: if there is no FON for the new person in the input dataset, skip to check on EMAIL
#
CHKITEL:
if		!this.cdata.Telefonnummer
goto	CHKIMAIL

#
# 13: create a new entry in CCATT for the FON (phone number) of the new person
#
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('PERSINFO','CCUS','@@USKURZZ@@','FON','@@Telefonnummer@@')
post_qexpression	this.protocol.push(["NEUE_PERSON_PERSON_FON_ANGELEGT","USKURZZ="+this.cdata.NEW_USKURZZ,"FON="+this.cdata.Telefonnummer,"",""]);

#
# 14: if there is no e-mail address for the new person in the input dataset, skip to end of checks for new person
#
CHKIMAIL:
if		!this.cdata.Email
goto	RECDONE

#
# 15: create a new entry in CCATT for the EMAIL (e-mail address) of the new person
#
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('USEMAIL','CCUS','@@USKURZZ@@','EMAIL','@@Email@@')
post_qexpression	this.protocol.push(["CREATED_NEW_PERSON_EMAIL","USKURZZ="+this.cdata.NEW_USKURZZ,"EMAIL="+this.cdata.Email,"",""]);
		
#		
# 16: end of processing one record in the input dataset
#
RECDONE:
qexpression	
	ppush(this,[phead(this,"RECORD_COMPLETE"),"RECNUM="+this.recnum,"COMPLETE","",""]);
	this.result = protocolToResult(this.protocol);
	this.result.setResultAttribute("new_uskurzz",this.set_new_uskurzz);

# end of seqtrans TSTEPS for DQUERY addperson