#
# tsteps definition for DQUERY kfoanspproclist
#
# ATTENTION: when modifying this definition check with the rest of the configuration in CCRW 
#

# 
# the record from the input dataset contains a "Name" field
# this field is parsed to determine the different name parts
# afterwards, the name is classified it it can be used to identify a person
#
qexpression
	this.pname = this.inputresult.getCellValue(this.recnum,this.namecolumn);
	this.nameobject = this.nameutils.parseName(this.pname);
	this.cdata = this.inputresult.getRowObject(this.recnum,"value");
	if (this.nameobject.vorname) {
		if (this.nameobject.familienname) {
			this.persontype = "NATURAL";
		} else {
			this.nameobject.familienname = this.nameobject.vorname;
			this.nameobject.vorname = "";
			this.persontype = "NATURAL";
		}
	} else {
		if (this.nameobject.familienname) {
			this.nameobject.vorname = "";
			this.persontype = 'NATURAL';
		} else {
			this.nameobject.vorname = "";
			this.nameobject.familienname = "";
			this.persontype = "NONE";
			if (this.nameobject.extern) {
				this.nameobject.familienname = this.nameobject.extern.replace(/[()]/g,"");
				this.persontype = "EXTERN";
			}
		}
	}
	this.inputresult.setCellValue(this.recnum,this.irtvcolumn,this.nameobject.titelvorne);
	this.inputresult.setCellValue(this.recnum,this.irvcolumn,this.nameobject.vorname);
	this.inputresult.setCellValue(this.recnum,this.irfcolumn,this.nameobject.familienname);
	this.inputresult.setCellValue(this.recnum,this.irthcolumn,this.nameobject.titelhinten);
	this.inputresult.setCellValue(this.recnum,this.irancolumn,this.nameobject.anmerkung);
	this.inputresult.setCellValue(this.recnum,this.irexcolumn,this.nameobject.extern);
	this.inputresult.setCellValue(this.recnum,this.iroecolumn,this.nameobject.OEname);
	this.inputresult.setCellValue(this.recnum,this.irnocolumn,aux.objTxt(this.nameobject));
	this.cdata = this.inputresult.getRowObject(this.recnum,"value");

# if the record from the input dataset is not useable to identify a person, skip it
if (this.persontype=="NONE")
goto RECDONE

# read from userdatabae if the person frmo the input dataset is already known
sql	select USKURZZ from CCUS us where us.USVORNAME='@@VORNAME@@' and us.USFAMILIENNAME='@@FAMILIENNAME@@'

# analyze data read from CCUS:
#	if we found an entry, we already have a person with this VORNAME/FAMILIENNAME, memorize their USKURZZ
#	if not, prepare for creating a new CCUS entry by calculating a new (hopefully unique) value for USKURZZ
qexpression
	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.uskurzz = this.last_sql_result.getCellValue(0,this.ukc);
		this.inputresult.setCellValue(this.recnum,
                                this.kurzzcolumn,
                                this.uskurzz);
		this.cdata.USKURZZ= this.uskurzz;
		this.protocol.push(["PERSON_KNOWN","VORNAME="+this.cdata.VORNAME,"FAMILIENNAME="+this.cdata.FAMILIENNAME,"HAS_USKURZZ="+this.uskurzz,""]);
		this.new_person = false;
	} else {
		this.new_person = true;
		this.new_uskurzz_root = this.nameutils.genUskurzzProposal(this.nameobject);
		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_NOT_YET_KNOWN_WILL_BE_CREATED","VORNAME="+this.cdata.VORNAME,"FAMILIENNAME="+this.cdata.FAMILIENNAME,"TRY_NEW_USKURZZ="+this.new_uskurzz_root,""]);
	}

# if not a new person, skip to checking for updates
if	!this.new_person
goto	CHKCTV

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

# 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

# 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.ttext = new Array();
	this.ttext.push("NEW_PERSON");
	this.ttext.push("NEW_USKURZZ="+this.cdata.NEW_USKURZZ);
	this.ttext.push("ALREADY_IN_USE");
	this.new_uskurzz = this.new_uskurzz_root+(++this.new_uskurzz_ucount);
	this.ttext.push(".TRY_USKURZZ="+this.new_uskurzz");
	this.ttext.push("");
	this.protocol.push(this.ttext);
	this.cdata.NEW_USKURZZ = this.new_uskurzz;
	this.cdata.USKURZZ = this.new_uskurzz;
then_goto	TRYNEWUSKURZZ

# 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.protocol.push(["CREATED_NEW_PERSON","USKURZZ="+this.cdata.NEW_USKURZZ,"VORNAME="+this.cdata.VORNAME,"FAMILIENNAME="+this.cdata.FAMILIENNAME,""]);

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

# 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
	this.protocol.push(["CREATED_NEW_PERSON_TITELVORNE","USKURZZ="+this.cdata.NEW_USKURZZ,"TITELVORNE="+this.cdata.TITELVORNE,"",""]);

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

# 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(["CREATED_NEW_PERSON_TITELHINTEN","USKURZZ="+this.cdata.NEW_USKURZZ,"TITELHINTEN="+this.cdata.TITELHINTEN,"",""]);

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

# 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(["CREATED_NEW_PERSON_FON","USKURZZ="+this.cdata.NEW_USKURZZ,"FON="+this.cdata.Telefonnummer,"",""]);

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

# 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,"",""]);
				   
# all person attributes created for new person, proceed to checking group membership(s)				   
label	ENDCHKI
goto	CHKGRP

# here we come when we have the person already found, now check for new or changed user attributes

# if the input dataset record has no TITELVORNE, skip to checking TITELHINTEN
label	CHKCTV
if	!this.cdata.TITELVORNE
goto	CHKCTH

# read CCATT to check if we already have TITELVORNE for this already existing person
sql	select ATTVALUE from CCATT where ATTARCHTYP='PERSINFO' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='TITELVORNE'

# if we do not already have a TITELVORNE for this already existing person, proceed to create a new entry
if	!this.last_sql_result.hasRows()
goto	INSXTV

# if the already present value for TITELVORNE is the same as in the input dataset record, skip any updating
if	(this.cdata.TITELVORNE==this.last_sql_result.getCellValue(0,0))
goto	CHKCTH

# update the already present CCATT entry for TITELVORNE of the already existing person
pre_qexpression this.old_titelvorne = this.last_sql_result.getCellValue(0,0)
sql	update CCATT set ATTVALUE='@@TITELVORNE@@' where ATTARCHTYP='PERSINFO' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='TITELVORNE'
post_qexpression	this.protocol.push(["UPDATED_TITELVORNE","TITELVORNE="+this.cdata.TITELVORNE,"FOR_USKURZZ="+this.cdata.USKURZZ,"OLD_TITELVORNE="+this.old_titelvorne,""]);
then_goto	CHKCTH

# create a new TITELVORNE entry in CCATT for the already existing person
label	INSXTV
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('PERSINFO','CCUS','@@USKURZZ@@','TITELVORNE','@@TITELVORNE@@')
post_qexpression	this.protocol.push(["ADDED_TITELVORNE","TITELVORNE="+this.cdata.TITELVORNE,"FOR_USKURZZ="+this.cdata.USKURZZ,"",""]);

# if the input dataset record has no TITELHINTEN, skip to checking FON				   
label	CHKCTH
if	!this.cdata.TITELHINTEN
goto	CHKCTEL

# read CCATT to check if we already have TITELHINTEN for this already existing person
sql select ATTVALUE from CCATT where ATTARCHTYP='PERSINFO' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='TITELHINTEN'

# if we do not already have a TITELVORNE for this already existing person, proceed to create a new entry
if	!this.last_sql_result.hasRows()
goto	INSXTH

# if the already present value for TITELHINTEN is the same as in the input dataset record, skip any updating
if	(this.cdata.TITELHINTEN==this.last_sql_result.getCellValue(0,0))
goto	CHKCTEL

# update the already present CCATT entry for TITELHINTEN of the already existing person
pre_qexpression this.old_titelhinten = this.last_sql_result.getCellValue(0,0)
sql	update CCATT set ATTVALUE='@@TITELHINTEN@@' where ATTARCHTYP='PERSINFO' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='TITELHINTEN'
post_qexpression this.protocol.push(["UPDATED_TITELHINTEN","TITELHINTEN="+this.cdata.TITELHINTEN,"FOR_USKURZZ="+this.cdata.USKURZZ,"OLD_TITELHINTEN="+this.old_titelhinten,""]);
then_goto	CHKCTEL

# create a new TITELHINTEN entry in CCATT for the already existing person
label	INSXTH
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('PERSINFO','CCUS','@@USKURZZ@@','TITELHINTEN','@@TITELHINTEN@@')
post_qexpression	this.protocol.push(["ADDED_TITELHINTEN","TITELHINTEN="+this.cdata.TITELHINTEN,"FOR_USKURZZ="+this.cdata.USKURZZ,"",""]);
				   
# if the input dataset record has no FON, skip to checking EMAIL
label	CHKCTEL
if	!this.cdata.Telefonnummer
goto	CHKCEMAIL

# read CCATT to check if we already have FON (telephone number) for this already existing person
sql	select ATTVALUE from CCATT where ATTARCHTYP='PERSINFO' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='FON'
 
# if we do not already have a FON for this already existing person, proceed to create a new entry
if	!this.last_sql_result.hasRows()
goto	INSXTELE

# if the already present value for FON is the same as in the input dataset record, skip any updating
if	(this.cdata.Telefonnummer==this.last_sql_result.getCellValue(0,0))
goto	CHKCEMAIL

#  update the already present CCATT entry for FON of the already existing person
label	UPDTELE
pre_qexpression this.old_fon = this.last_sql_result.getCellValue(0,0)
sql	update CCATT set ATTVALUE='@@Telefonnummer@@' where ATTARCHTYP='PERSINFO' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='FON'
post_qexpression	this.protocol.push(["UPDATED_FON","FON="+this.cdata.Telefonnummer,"FOR_USKURZZ="+this.cdata.USKURZZ,"OLD_FON="+this.old_fon,""]);
then_goto	CHKCEMAIL

# create a new FON entry in CCATT for the already existing person
label	INSXTELE
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('PERSINFO','CCUS','@@USKURZZ@@','FON','@@Telefonnummer@@')
post_qexpression
	this.protocol.push(["ADDED_FON","FON="+this.cdata.Telefonnummer,"FOR_USKURZZ="+this.cdata.USKURZZ,"",""]);
				   
# if the input dataset record has no EMAIL, skip to checking group membership
label	CHKCEMAIL
if	!this.cdata.Email
goto	CHKGRP

#  read CCATT to check if we already have EMAIL for this already existing person
sql	select ATTVALUE from CCATT where ATTARCHTYP='USEMAIL' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='EMAIL'
 
# if we do not already have a EMAIL for this already existing person, proceed to create a new entry
if	!this.last_sql_result.hasRows()
goto	INSXEMAIL

# if the already present value for EMAIL is the same as in the input dataset record, skip any updating
if	(this.cdata.Email==this.last_sql_result.getCellValue(0,0))
goto	CHKGRP

#  update the already present CCATT entry for EMAIL of the already existing person
label	UPDEMAIL
pre_qexpression	this.old_email = this.last_sql_result.getCellValue(0,0);
sql	update CCATT set ATTVALUE='@@Email@@' where ATTARCHTYP='USEMAIL' and ATTPARTYPE='CCUS' and ATTPARKURZ='@@USKURZZ@@' and ATTNAME='EMAIL'
post_qexpression	this.protocol.push(["UPDATED_EMAIL","EMAIL="+this.cdata.Email,"FOR_USKURZZ="+this.cdata.USKURZZ,"OLD_EMAIL="+this.old_email,""]);
then_goto	CHKGRP

# create a new EMAIL entry in CCATT for the already existing person
label	INSXEMAIL
sql	insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('USEMAIL','CCUS','@@USKURZZ@@','EMAIL','@@Email@@')
post_qexpression this.protocol.push(["ADDED_EMAIL","EMAIL="+this.cdata.Email,"FOR_USKURZZ="+this.cdata.USKURZZ,"",""]);
	
# now check already existing group membership for the new or already present person

# at first, determine the ROLE for the group membership from ths input dataset record
label	CHKGRP
qexpression
	if (this.cdata.Bereich.startsWith('Leist')) this.cdata.ROLE = 'LEIST';
	if (this.cdata.Bereich.startsWith('Med')) this.cdata.ROLE = 'MED';
	if (this.cdata.Bereich.startsWith('Vert')) this.cdata.ROLE = 'VERT';
	if (this.cdata.Bereich.startsWith('Abre')) this.cdata.ROLE = 'VPAB';
	if (this.cdata.Bereich.startsWith('EDV')) this.cdata.ROLE = 'EDV';
	if (this.cdata.Bereich.startsWith('Stat')) this.cdata.ROLE = 'STAT';
	if (this.cdata.Bereich.startsWith('QS')) this.cdata.ROLE = 'QS';
	if (!this.cdata.ROLE) {
		throw new Error('undefined Bereich '+this.cdata.Bereich);
	}

# read group assignment (CCUSAS) for the person if they're already known with this ROLE for this OE in the group KFOANSP
sql	SELECT * from CCUSAS where USKURZZ='@@USKURZZ@@' and GRP='KFOANSP' and OE='@@KVTraeger@@' and ROLE='@@ROLE@@'

# if there already is a group membership, skip to end of processing
if	this.last_sql_result.hasRows()
goto	RECDONE

# create a new group membership in CCUSAS
sql	insert into CCUSAS(USKURZZ,GRP,OE,ROLE,VONJAHR,VONMONAT,BISJAHR,BISMONAT,ASSFTE) values('@@USKURZZ@@','KFOANSP','@@KVTraeger@@','@@ROLE@@',null,null,null,null,1)
post_qexpression	this.protocol.push(["ADDED_USKURZZ_TO_GRP","USKURZZ="+this.cdata.USKURZZ,"TO_GRP=KFOANSP","FOR_OE="+this.cdata.KVTraeger,"AS_ROLE="+this.cdata.ROLE]);

# end of processing one record in the input dataset
label	RECDONE
qexpression	
	this.protocol.push(["RECORD_COMPLETE","RECNUM="+this.recnum,"COMPLETE","",""]);

# end of tsteps for kfoanspproclist