#
# jobtype importPersonGroup definition file
#
# this job type assigns one person per input dataset row with a group
# group name is taken from parameter "grpid"
# starting year of assignment is taken from parameter "vonjahr"
# starting month of assignment is taken from parameter "vonmonat"
# ending year and month from parameters "bisjahr" and "bismonat" 
# this job type assumes an input dataset with person attributes, enhanced by 
# "rolle" - role the person shall take in the group, must exist in CCROLE
# "oe"    - OE the person represents in the group, must exist in CCOE
# "vorname","familienname","email" - person attributes.
#			if the person already exists it must have exactly these attributes
#
#
@init.qexpression
	proc: {
		if (!this.query.parameters) {
			this.errcoll.collect(null,"Error in Job description: no parameters",this.query);
			break proc;
		}
		if (!this.query.parameters.grpid) {
			this.errcoll.collect(null,"Error in Job description: no grpid parameter",this.query.parameters);
			break proc;
		}
		aux.default_param(this.query.parameters,"vonjahr",new Date().getFullYear());
		aux.default_param(this.query.parameters,"vonmonat",new Date().getMonth()+1);
		aux.default_param(this.query.parameters,"bisjahr",0);
		aux.default_param(this.query.parameters,"bismonat",0);
		this.needed_irec_attributes = ['vorname','familienname','email','rolle','oe'];
		this.nameutils = require("./nameutils.js")(logger,this.dataname,prefs);
		this.protocol = new Array();
		this.protocol.push([this.fname, this.dataname,"PROCESSING_STARTED","",""]);
		this.result = new aux.Result('dbresult');
		this.result.setResultAttribute("resultname","PROTOCOL");
		this.result.addMetaDataName("Activity");
		this.result.addMetaDataName("Param_1");
		this.result.addMetaDataName("Param_2");
		this.result.addMetaDataName("Param_3");
		this.result.addMetaDataName("Param_4");
		this.result.rows = this.protocol;
		for (let pn in this.query.parameters) {
			this.protocol.push(["","BATCH","PARAMETERS",pn,this.query.parameters[pn]]);
		}
	}
	
@setup.qexpression
	proc: {
		this.protocol.push(["","","SETUP_DONE","",""]);
	}
	
#
# now the processing steps executed for each record
#

#
# 0: check if all required data fields are there and prepare CCUS checking
#
qexpression
	proc: {
		for (let ni=0; ni<this.needed_irec_attributes.length; ni++) {
			if (!this.cdata.hasOwnProperty(this.needed_irec_attributes[ni])) this.errcoll.collect(null,"missing "+this.needed_irec_attributes[ni],this.cdata);
		}
		if (this.errcoll.hasErrors()) break proc;	
		this.nameobject = {vorname: this.cdata.vorname, familienname: this.cdata.familienname};
		if (this.cdata.uskurzz) {
			this.new_uskurzz_root = this.cdata.uskurzz;
			logger.debug("importPersonGroup.GOT_CDATA_USKURZZ.new_uskurzz_root=\""+this.new_uskurzz_root+"\"");
		} else {
			this.new_uskurzz_root = this.nameutils.genUskurzzProposal(this.nameobject);
			logger.debug("importPersonGroup.NO_CDATA_USKURZZ.new_uskurzz_root=\""+this.new_uskurzz_root+"\"");
		}
		this.new_uskurzz = this.new_uskurzz_root;
		this.cdata.GRPID = this.query.parameters.grpid;
	}

#
# 1: look up person in CCUS
#
sql select USKURZZ from CCUS where USVORNAME=::vorname:: and USFAMILIENNAME=::familienname:: and USSTATUSCCUSTAT='ON'
sqlparams vorname,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);
		this.cdata.USKURZZ = this.old_uskurzz;
		if (this.cdata.USKURZZ) {
			if (this.cdata.USKURZZ!=this.old_uskurzz) {
				this.info5 = "IST_NICHT="+this.cdata.USKURZZ+", ABGEBROCHEN!";
				this.errcoll.collect(null,"gespeichertes USKURZZ nicht gleich dem neuen",this.cdata);
				throw new Error("USKURZZ stimmen nicht überein");
			} else {
				this.info5 = "IST_GLEICH="+this.cdata.USKURZZ+", KEINE_ÄNDERUNG.";
			}
		} else {
			this.info5 = "KEINE_ÄNDERUNG";
		}
		this.protocol.push(["PERSON_SCHON_BEKANNT","VORNAME="+this.cdata.vorname,"FAMILIENNAME="+this.cdata.familienname,"MIT_USKURZZ="+this.cdata.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	have_person

# 4: new person, check if calculated NEW_USKURZZ is already in use
TRYNEWUSKURZZ:
sql	select USVORNAME,USFAMILIENNAME from CCUS where USKURZZ=::NEW_USKURZZ::
sqlparams 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	create_new_us

#
# 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);
	this.protocol.push(["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
#
create_new_us:
sql	insert into CCUS(USKURZZ,USVORNAME,USFAMILIENNAME,USSTATUSCCUSTAT) values(::USKURZZ::,::vorname::,::familienname::,'ON')
sqlparams USKURZZ,vorname,familienname
post_qexpression
	this.result.setResultAttribute("new_uskurzz",this.cdata.NEW_USKURZZ);
	this.cdata.USKURZZ = this.cdata.NEW_USKURZZ;
	this.protocol.push(["NEUE_PERSON_ANGELEGT","USKURZZ="+this.cdata.NEW_USKURZZ,"VORNAME="+this.cdata.vorname,"FAMILIENNAME="+this.cdata.familienname,""]);
	
#
# 8: have person now look for EMAIL
#
have_person:
sql select ATTVALUE as email from CCATT att where ATTARCHTYP='USEMAIL' and ATTPARTYPE='CCUS' and ATTPARKURZ=::USKURZZ:: and ATTNAME='EMAIL'
sqlparams USKURZZ

#
# 9: check if we found an Email-address and if it's the same we want to create
#
qexpression
	proc: {
		if (this.last_sql_result.hasRows()) {
			this.found_email = true;
			this.pres_email = this.last_sql_result.getCellValue(0,0);
			if (this.pres_email.toUpperCase()==this.cdata.email.toUpperCase()) {
				this.email_matches = true;
			} else {
				this.email_matches = false;
			}
			this.protocol.push(["EMAIL_VORHANDEN","ZU_USKURZZ="+this.cdata.USKURZZ,"EMAIL_GESPEICHERT="+this.pres_email,"EMAIL_NEU="+this.cdata.email,(this.email_matches?"STIMMT_ÜBEREIN":"UNTERSCHEIDET_SICH")]);
		} else {
			this.protocol.push(["EMAIL_NICHT_VORHANDEN","ZU_USKURZZ="+this.cdata.USKURZZ,"","",""]);
			this.found_email = false;
		}
	}

#
# 10: check if email match was ok
#
if (this.found_email && this.email_matches)
goto have_email

#
# 11: check if we can create a new email-address
#
if (!this.found_email)
goto create_email

#
# 12: found email, but does not match, abort
#
qexpression
	this.errcoll.collect(null,"Error in EMAIL for "+this.cdata.vorname+" "+this.cdata.familienname+", stored email "+this.pres_email+" does not match new email "+this.cdata.email);
then_goto	abort

#
# 13: create new email record
#
create_email:
sql insert into CCATT(ATTARCHTYP,ATTPARTYPE,ATTPARKURZ,ATTNAME,ATTVALUE) values('USEMAIL','CCUS',::USKURZZ::,'EMAIL',::email::)
sqlparams USKURZZ,email

#
# 14: protocol new email
#
qexpression
	this.protocol.push(["NEUE_EMAIL_ANGELEGT","ZU_USKURZZ="+this.cdata.USKURZZ,"NAME="+this.cdata.vorname+" "+this.cdata.familienname,"EMAIL="+this.cdata.email,""]);

#
# 15: have email, check group membership
#
have_email:
sql select OE,ROLE from CCUSAS where USKURZZ=::USKURZZ:: and GRP=::GRPID::
sqlparams USKURZZ,GRPID

#
# 16: check if membership already there and matching
#
qexpression	
	proc: {
		if (this.last_sql_result.hasRows()) {
			this.found_ass = true;
			this.pres_oe = this.last_sql_result.getCellValue(0,0);
			this.pres_role = this.last_sql_result.getCellValue(0,1);
			if (this.pres_oe == this.cdata.oe && this.pres_role == this.cdata.rolle) {
				this.ass_matches = true;
			} else {
				this.ass_matches = false;
			}
		} else {
			this.found_ass = false;
		}
		this.protocol.push([(this.last_sql_result.hasRows()?"MITGLIEDSCHAFT_GEFUNDEN":"NOCH_KEINE_MITGLIEDSCHAFT"),"ZU_USKURZZ="+this.cdata.USKURZZ,"ZU_GROUPID="+this.cdata.GRPID,"",""]);
	}

#
# 17: check if new membership needed
#
if (!this.found_ass)
goto create_ass

#
# 18: check if membership match was OK 
#
if (this.ass_matches)
goto ass_done

#
# 19: mismatched membership
#
qexpression	
	this.errcoll.collect(null,"Error in group membership in GRPID=\""+this.cdata.GRPID+"\" for USKURZZ="+this.cdata.USKURZZ+", NAME=\""+this.cdata.vorname+" "+this.cdata.familienname+"\", stored membership "+
		this.pres_oe+"/"+this.pres_role+" does not match new "+this.cdata.oe+"/"+this.cdata.rolle);
then_goto abort

#
# 20: create new membership, prepare parameters
#
create_ass:
qexpression
	this.cdata.VONJAHR = this.query.parameters.vonjahr;
	this.cdata.VONMONAT = this.query.parameters.vonmonat;
	this.cdata.BISJAHR = this.query.parameters.bisjahr;
	this.cdata.BISMONAT = this.query.parameters.bismonat;
	this.cdata.GRPID = this.query.parameters.grpid;

#
# 21: create membership, execute SQL
#
sql insert into CCUSAS(USKURZZ,GRP,OE,ROLE,VONJAHR,VONMONAT,BISJAHR,BISMONAT,ASSFTE) values(::USKURZZ::,::GRPID::,::oe::,::rolle::,::VONJAHR::,::VONMONAT::,::BISJAHR::,::BISMONAT::,1)
sqlparams USKURZZ,GRPID,oe,rolle,VONJAHR,VONMONAT,BISJAHR,BISMONAT
post_qexpression
	this.protocol.push(["NEUE_MITGLIEDSCHAFT_ANGELEGT","USKURZZ="+this.cdata.NEW_USKURZZ,"NAME="+this.cdata.vorname+" "+this.cdata.familienname,"ROLLE="+this.cdata.rolle,"OE="+this.cdata.oe]);

#
# 22: have or created group membership with matching role
#
ass_done:
goto done
	
#
# 23: there was an error, abort
#
abort:
qexpression
	this.errcoll.collect(null,"abort");
	this.protocol.push(["ABBRUCH","","","",""]);
	
#
# 24: end of one record
#
done:
qexpression
	this.protocol.push(["RECORD_"+this.recnum+"_BEARBEITET","","","",""]);