#
#				ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:				dquerymfile_TIMEACCOUNT.txt
#	DESCRIPTION:		DQUERY Definition of DQUERY TIMEACCOUNT
#						produces time-accounting data for a time range stored in an time record managed file in OBJCOMP format
#
#
@querytitle				Aufwandsauswertung aus Arbeitsnotizen
@querydescription		Arbeitsaufwände aus Arbeitsnotizen ermitteln
@group					ILV
@attributenames			wnmfid:mfileid:{{wnmfidoptions}},vondatnum:integer:{{vondatnumoptions}},bisdatnum:integer:{{bisdatnumoptions}}
@wnmfidoptions			{
	labeltext:			"Arbeitsnotizen-Datei",
	typedesc:			"ID des managed files, aus dem die Arbeitsnotizen zur Auswertung geladen werden sollen" }
@vondatnumoptions		{
	labeltext:			"Von-Datum",
	typedesc:			"Erster Tag des Zeitraums, für den Leistingsdaten ausgewertet werden sollen" }
@bisdatnumoptions		{
	labeltext:			"Bis-Datum",
	typedesc:			"Letzter Tag des Zeitraums, für den Leistungsdaten ausgewertet werden sollen" }
@querytype				function
@function				seqtrans.seqtrans
@init.qexpression
	this.inputresult = new aux.Result({ resulttype: 'dbresult', rows: [[0]], metaData: [ { name: "column0" } ] });

~query.tsteps

#
# <<TSTEPNUM:0>>: load/compile data from mfile
#
pre_qexpression
	this.wndata = {};
aexpression
	this.hldebuglog = new Array();
	this.hldbg = function(logstr) {
		this.hldebuglog.push(logstr);
	}
	objcomp.compileMfile(this.query.wnmfid,this.wndata,{},
		function(err,res) {
			if (err) {
				this.errcoll.collect(err,"Error object-compiling managed file ID="+this.query.wnmfid,res);
				this();
			}
			this();
		}.bind(this));

#
# <<TSTEPNUM:1>>: set up utility functions: datnum2date(), date2datnum(), nextdatnum()
#
qexpression
		this.datnum2date = function(datnum) {
			if (datnum.length<8) return null;
			return new Date(0+datnum.substr(0,4),0+datnum.substr(4,2)-1,0+datnum.substr(6,2))
		}
		this.date2datnum = function(dat) {
			return aux.DEC(dat.getFullYear(),4)+aux.DEC(dat.getMonth()+1,2)+aux.DEC(dat.getDate(),2);
		}
		this.nextdatnum = function(datnum) {
			let dat = this.datnum2date(datnum);
			dat.setDate(dat.getDate()+1);
			return this.date2datnum(dat);
		}

#
# <<TSTEPNUM:2>>:
#
qexpression
		this.time2date = function(timstr,refdate) {
			if (!refdate) throw new Error("time2date: refdate not defined");
			let tcs = timstr.split(":");
			let nd = new Date(refdate.getFullYear(),refdate.getMonth(),refdate.getDate(),tcs[0],tcs[1]);
			/* logger.debug("time2date: time: "+timstr+", result date: "+nd); */
			return nd
		}
		this.time2expmins = function(timstr) {
			let tcs = timstr.split(":");
			let emin = 60*tcs[0] + 1*tcs[1];
			/* logger.debug("time2expmins: time: "+timstr+", tcs: "+tcs+", expmins: "+emin); */
			return emin;
		}

#
# <<TSTEPNUM:3>>:
#
qexpression
		this.expminutes = function(sdate,edate) {
			let diff = edate-sdate;
			let secs = Math.floor(diff/1000);
			let mins = Math.floor(secs/60);
			/* logger.debug("expmins: sdate: "+sdate+", edate: "+edate+", mins: "+mins); */
			return mins;
		}
		this.timeless = function(time1,time2) {
			return time1 < time2;
		}

#
# <<TSTEPNUM:4>>:
#
qexpression
		this.mins2exptime = function(mins) {
			let hours = Math.floor(mins/60);
			let hdigs = 2;
			if (hours>=100) hdigs=3;
			if (hours>=1000) hdigs=4;
			if (hours>=10000) hdigs=5;
			let minutes = mins % 60;
			let hhmm = aux.DEC(hours,hdigs)+":"+aux.DEC(minutes,2);
			/* logger.debug("mins2exptime: mins: "+mins+", exptime: "+hhmm); */
			return hhmm;
		}

#
# <<TSTEPNUM:5>>:
#
qexpression
		this.mins2exphours = function(mins) {
			return mins/60;
		}
		this.addMinsToTime = function(mins,tim) {
			/* logger.debug("addMinsToTime: tim: "+aux.objTxt(tim)+", mins: "+aux.objTxt(mins)); */
			tim.setMinutes(tim.getMinutes()+mins);
			return tim;
		}

#
# <<TSTEPNUM:6>>:
#
qexpression
		this.exptime = function(sdate,edate) {
			let mins = this.expminutes(sdate,edate);
			return this.mins2exptime(mins);
		}

#
# <<TSTEPNUM:7>>:
#
qexpression
		this.time2difftime = function(timstr,refdate) {
			return this.exptime(refdate,this.time2date(timstr,refdate));
		}

#
# <<TSTEPNUM:8>>: testpjrex()
#
qexpression
		/* check topic caption <tpc> against <rex>, if it matches return project object <pj>, else null */
		this.testpjrex = function(rex,tpc,pj) {
			if (!rex) return null;
			let re = new RegExp(rex);
			mat = (re.test(tpc)?pj:null);
			/* if (rex=="^AMPS.*" || rex=="AMPS.*") this.hldbg("testpjrex.topic=\""+tpc+"\".rex=\""+rex+"\".match="+(mat?"true":"false")); */
			return mat;
		}

#
# <<TSTEPNUM:9>>: isInVRange(), matchpj()
#
qexpression
		/* check if <datnum> is in validity range of project <pj> */
		this.isInVRange = function(pj,datnum) {
			let mtch = true;
			if (!datnum) {
				if (false) this.hldbg("ISINVRANGE.DATNUM=NONE="+datnum+".VONDATNUM="+pj.vondatnum+".BISDATNUM="+pj.bisdatnum+".MTCH="+mtch);
				if (false) this.hldbg(new Error().stack);
				return true;
			}
			if (mtch && pj.vondatnum) {
				if (datnum<pj.vondatnum) mtch = false;
			}
			if (mtch && pj.bisdatnum) {
				if (datnum>pj.bisdatnum) mtch = false;
			}
			if (false) this.hldbg("ISINVRANGE.PROJECT="+pj.project+".DATNUM="+datnum+".VONDATNUM="+pj.vondatnum+".BISDATNUM="+pj.bisdatnum+".MTCH="+mtch);
			return mtch;
		}
		/* check if the project <pj> matches the topic in <tpc> */
		/* if <datnum> is given and <pj> has validity range (.validfrom,.validto), */
		/*             <datnum> must be in range to match */
		this.matchpj = function(tpc,pj,datnum) {
			if (pj.regexes) {
				for (let rex of pj.regexes) {
					let mtch = this.testpjrex(rex,tpc,pj);
					if (mtch) mtch = this.isInVRange(pj,datnum);
					if (pj.debuglog) {
						this.hldbg("MATCHPJ.PJ="+pj.project+".REX="+rex+".MTCH="+mtch);
					}
					if (mtch) return pj;
				}
			}
			if (pj.regex) {
				if (this.testpjrex(pj.regex,tpc,pj)) return pj;
			}
			return null;
		}

#
# <<TSTEPNUM:10>>: findpj(), findpjbypsp(), findpjbyproject(), assignworktime()
#
qexpression
		/* find project by matching <pjc> against the match criteria in each project using matchpj() */
		this.findpj = function (tpc,pjl,datnum) {
			for (let pj of pjl) {
				if (this.matchpj(tpc,pj,datnum) && this.isInVRange(pj,datnum)) {
					if (tpc=="AMPS-Daily") this.hldbg("AMPS-Daily found in "+datnum+" matched to "+aux.objTxt(pj));
					return pj;
				}
			}
			if (tpc=="AMPS-Daily") this.hldbg("AMPS-Daily not matched for datnum="+datnum);
			return null;
		}
		/* find project given a PSP */
		this.findpjbypsp = function(psp,pjl,datnum) {
			for (let pj of pjl) {
				if (pj.psp==psp && this.isInVRange(pj,datnum)) return pj;
			}
			return null;
		}
		/* find a project given a <project> */
		this.findpjbyproject = function(project,pjl,datnum) {
			for (let pj of pjl) {
				if (pj.project==project && this.isInVRange(pj,datnum)) return pj;
			}
			return null;
		}
		/* assign working time of <workrec> to project <pj> in the context of <dayrec> */
		this.assignworktime = function(dayrec,workrec,pj,worktime) {
			/* logger.debug("assignworktime.day="+dayrec.daydate+".project="+pj.project); */
			if (!dayrec.leistungen[pj.psp]) {
				dayrec.leistungen[pj.psp] = { minuten: 0, project: pj.project };
			}
			dayrec.leistungen[pj.psp].minuten += worktime;
			dayrec.leistungen[pj.psp].stunden = this.mins2exphours(dayrec.leistungen[pj.psp].minuten);
			dayrec.leistungsminuten += worktime;
			dayrec.leistungsstunden = this.mins2exphours(dayrec.leistungsminuten);
			if (!workrec.projekte) {
				throw new Error("no workrec.projekte");
				workrec.projekte = {};
			}
			if (!workrec.projekte[pj.psp]) {
				throw new Error("no workrec.projekte.projekt for PSP="+pj.psp);
				workrec.projekte[pj.psp] = { psp: pj.psp, project: pj.project, name: pj.name, minuten: 0, stunden: "" };
				if (pj.planstundenmonat) workrec.projekte[pj.psp].planstundenmonat = pj.planstundenmonat;
			}
			workrec.projekte[pj.psp].minuten += worktime;
			workrec.projekte[pj.psp].stunden = this.mins2exptime(workrec.projekte[pj.psp].minuten);
			if (!workrec.total) {
				workrec.total = { leistungsminuten: 0, leistungsstunden: ""};
			}
			workrec.total.leistungsminuten += worktime;
			workrec.total.leistungsstunden = this.mins2exptime(workrec.total.leistungsminuten);
		}
		
#
# <<TSTEPNUM:11>>:	detslottime(), detworktime()
#
qexpression
		/* determine the assignable working time for a worknote entry <ae> in the context of <dayrec> */
		/* the working time in minutes is assigned to <ae.workmins>                                   */
		this.detslottime = function(ae,dayrec,tagname) {
			if (ae.work) {
				ae.workmins = this.time2expmins(ae.work);
			} else if (ae.out) {
				ae.outmins = this.time2expmins(ae.out);
			} else if (ae[tagname]) {
				ae.workmins = this.time2expmins(ae[tagname]);
			} else if (ae.start && ae.end) {
				ae.stime = this.time2date(ae.start,dayrec.daydate);
				ae.etime = this.time2date(ae.end,dayrec.daydate);
				ae.workmins = this.expminutes(ae.stime,ae.etime);
			} else {
				ae.workmins = 0;
			}		
		}
		/* determine the assignable working time for a worknote entry <ae> in the context of <dayrec> */
		/* RESULT: a record containing:                                                               */
		/* 			.worktime		-	the assignable working time in minutes                        */
		/*          .psp            -	the PSP code of the project account, if available             */
		/*          .pj				-	a project record                                              */
		/*          .err			-	an error information, if an error occured                     */
		this.detworktime = function(ae,dayrec,pjlist) {
			let stime, etime, work;
			if (!dayrec.daydate)	return { worktime: null, pj: null, err: new Error("dayrec has no daydate") };
			if (!dayrec.datnum)		return { worktime: null, pj: null, err: new Error("dayrec has no datnum") };
			if (dayrec.datnum<18000000 || dayrec.datnum>=29999999) 
				return { worktime: null, pj: null, err: new Error("dayrec.datnum invalid: "+dayrec.datnum) };
			this.detslottime(ae,dayrec,"no_time_attr");
			if (!ae.workmins) {
				return {worktime: 0, psp: null, pj: null, err: null};
			}
			if (ae.topic) {
				let pj = this.findpj(ae.topic,pjlist,dayrec.datnum);
				if (pj) {
					return {worktime: ae.workmins, pj: pj, err: null};
				} else {
					return {worktime: ae.workmins, pj: null, err: null};
				}
			} else {
				return {worktime: null, pj: null, err: new Error("no topic in activity element")};
			}
		}

#
# <<TSTEPNUM:12>> define datnumrangematches(), findKEdefsByDatnumRange(globalcontext,kedeflist,vondatnum,bisdatnum)
#
qexpression
	proc: {
		this.datnumrangematches = function(o,dnv,dnb) {
			if (!o) return false;
			if (!o.datnumvon) o.datnumvon = 00000000;
			if (!o.datnumbis) o.datnumbis = 99999999;
			if (!dnv) dnv = 18000000;
			if (!dnb) dnb = 29999999;
			if (dnb<dnv) return false;
			if (o.dnb<o.dnv) return false;
			let mtch = ((dnv>=o.datnumvon) && (dnb<=o.datnumbis))
			this.hldbg("DATNUMRANGEMATCHES.DNV="+dnv+".DATNUMVON="+o.datnumvon+".DNB="+dnb+".DATNUMBIS="+o.datnumbis+".MTCH="+mtch);
			return mtch;
		}
		this.findKEdefsByDatnumRange = function(ctx,kedeflist,vondatnum,bisdatnum) {
			if (!kedeflist) {
				this.hldbg({ label: "findKEdefsByDatnumRange.begin_nolist" }); 
				return null;
			}
			for (let kedefname in kedeflist) {
				let kedef = kedeflist[kedefname];
				let kd_datnumvon = (kedef.datnumvon)?kedef.datnumvon:((ctx[kedefname+"_datnumvon"])?ctx[kedefname+"_datnumvon"]:00000000);
				let kd_datnumbis = (kedef.datnumbis)?kedef.datnumbis:((ctx[kedefname+"_datnumbis"])?ctx[kedefname+"_datnumbis"]:00000000);
				kedef.datnumvon = kd_datnumvon;
				kedef.datnumbis = kd_datnumbis;
				this.hldbg({ 	label: "findKEdefsByDatnumRange.loop", 
								vondatnum: vondatnum, bisdatnum: bisdatnum, 
								kedefname: kedefname,
								kd_datnumvon: kd_datnumvon, kd_datnumbis: kd_datnumbis,
								kedef_datnumvon: kedef.datnumvon, kedef_datnumbis: kedef.datnumbis
								});
				if (this.datnumrangematches(kedef,vondatnum,bisdatnum)) {
					this.hldbg({ text: "findKEdefsByDatnumRange.matches", kedef: kedef });
					return kedef;
				}
			}
		}
	}

#
# <<TSTEPNUM:13>>: process all activities on all days in <vondatnum> .. <bisdatnum> that have a "topic"
#
qexpression
	proc: {
		let err = null;
		let vondatnum = this.query.vondatnum;
		let bisdatnum = this.query.bisdatnum;
		if (vondatnum<20000000) {
			this.errcoll.collect(null,"VONDATNUM ungültig: "+vondatnum);
			break proc;
		}
		if (bisdatnum<20000000) {
			this.errcoll.collect(null,"BISDATNUM ungültig: "+bisdatnum);
			break proc;
		}	
		this.hldbg({ label: "wndata", wndata: this.wndata });
		let jahr = vondatnum.substr(0,4);
		let projects = null;
		if (!projects) {
			let kedeflist = this.wndata.K_Elemente;
			this.hldbg({ label: "K_Elemente", kedeflist: kedeflist });
			if (kedeflist) {
				projects = this.findKEdefsByDatnumRange(this.wndata,kedeflist,vondatnum,bisdatnum);
				this.projects = { info: aux.deepCopy(projects[0]) };
			}
		}
		this.hldbg({ label: "K_Elemente_nach_Range_Search", projects: projects });
		if (!projects) {
			if (!this.wndata[jahr]) {
				this.errcoll.collect(null,"kein Jahres-Eintrag für "+jahr,this.wndata);
				break proc;
			}
			projects = this.wndata[jahr].K_Elemente;
		}
		this.hldbg({ label: "K_Elemente_nach_jahr", jahr: jahr, projects: projects });
		if (!projects) {
			this.errcoll.collect(null,"keine K_Elemente für den Bereich "+vondatnum+".."+bisdatnum+" oder das Jahr "+jahr,this.wndata);
			break proc;
		}
		this.days = {};
		this.leistungen = {};
		this.nichtleistungen = {};
		this.leistungen.projekte = {};
		for (let pj of projects) {
			if (!pj) {
				this.errcoll.collect(null,"pj is undefined",{ pj: pj, projects: projects});
				break proc;
			}
			this.leistungen.projekte[pj.psp] = { psp: pj.psp, project: pj.project, name: pj.name, minuten: 0, stunden: "" };
			if (pj.hasOwnProperty("seq")) this.leistungen.projekte[pj.psp].seq = pj.seq;
		}
		let dayrec, dacc, act;
		for (let datnum in this.wndata) {
			dacc = this.wndata[datnum];
			if ((datnum>=vondatnum) && (datnum<=bisdatnum)) {
				try {
					dayrec = {};
					dayrec.datnum  = datnum;
					dayrec.daydate = this.datnum2date(datnum);
					if (!dayrec.daydate) {
						this.errcoll.collect(null,"could not get date from datnum", { datnum: datnum });
						break proc;
					}
					if (!aux.isDate(dayrec.daydate)) {
						this.errcoll.collect(null,"datnum did not produce a Date", { datnum: datnum, dateresult: dayrec.daydate });
						break;
					}
					dayrec.jahr    = dayrec.daydate.getFullYear();
					dayrec.monat   = dayrec.daydate.getMonth()+1;
					dayrec.tag     = dayrec.daydate.getDate();
					dayrec.leistungen = {};
					dayrec.leistungsminuten = 0;
					dayrec.leistungsstunden = "";
					dayrec.abwesenheitsminuten = 0;
					dayrec.last_end = null;
					dayrec.first_start = null;
					if (!this.leistungen[datnum]) {
						this.leistungen[datnum] = dayrec;
					} else {
						this.errcoll.collect(null,"dayrec for "+datnum+" already exists",this.leistungen);
						break proc;
					}
					for (act of dacc) {
						/*
						logger.debug("ACT: line: "+act.__line+
									(act.topic?(", topic: "+act.topic):"")+
									(act.start?(", start: "+act.start):"")+
									(act.end?(", end: "+act.end):"")+
									(act.anwesend?(", anwesend: "+act.anwesend):"")+
									(act.kommen?(", kommen: "+act.kommen):"")+
									(act.gehen?(", gehen: "+act.gehen):"")+
									(act.work?(", work: "+act.work):"")+
									(act.hasOwnProperty("MP")?(", MP: "+act.MP):"")+
									(act.hasOwnProperty("ABW")?(", ABW: "+act.ABW):"")
									);
						*/
						if (act.anwesend) {
							dayrec.anwesend = this.time2difftime(act.anwesend,dayrec.daydate);
						} else if (act.kommen) {
							dayrec.kommzeit = this.time2date(act.kommen,dayrec.daydate);
							if (!dayrec.last_end) {
								dayrec.last_end = dayrec.kommzeit;
							} else {
								this.errcoll.collect(null,"kommen overwrites existing last_end",{last_end: dayrec.last_end, kommen: act});
								break proc;
							}
						} else if (act.gehen) {
							dayrec.gehzeit = this.time2date(act.gehen,dayrec.daydate);
						} else if (act.hasOwnProperty("krank")) {
							// krank-zeiten ignorieren
						} else if (act.hasOwnProperty("timeacct") && act.start && act.end) {
							dayrec.startime = this.time2date(act.start,dayrec.daydate);
							dayrec.endtime  = this.time2date(act.end,dayrec.daydate);
						} else if (act.hasOwnProperty("MP")) {
							this.detslottime(act,dayrec,"MP");
							if (!act.stime) {
								if (dayrec.last_end) {
									act.stime = dayrec.last_end;
								} else {
									this.errcoll.collect(null,"MP without preceeding activity",{act: act, dayrec: dayrec});
									break proc;
								}
							}
							if (!act.etime) {
								if (!act.workmins) {
									act.workmins = 30;
								}
								act.etime = this.addMinsToTime(act.workmins,act.stime);
							} else {
								if (!act.workmins) {
									act.workmins = this.expmins(act.etime,act.stime);
								}
							}
							dayrec.abwesenheitsminuten += act.workmins;
							dayrec.hatMP = true;
						} else if (act.hasOwnProperty("ABW")) {
							this.detslottime(act,dayrec,"ABW");
							if (!act.stime) {
								if (dayrec.last_end) {
									act.stime = dayrec.last_end;
								} else {
									this.errcoll.collect(null,"ABW without preceeding activity",{act: act, dayrec: dayrec});
									break proc;
								}
							}
							if (!act.etime) {
								if (act.workmins) {
									act.etime = this.addMinsToTime(act.workmins,act.stime);
								} else {
									/* try memorizing open-AWB */
									if (dayrec.hasOwnProperty("openABW")) {
										this.errcoll.collect(null,"ABW has not enough information (neither end nor work)",{ act: act, dayrec: dayrec });
										break proc;
									} else {	/* memorize open-ABW and hope for resolution */
										dayrec.openABW = act.stime;
										dayrec.isIncomplete = true;
									}
								}
							} else {
								if (!act.workmins) {
									act.workmins = this.expmins(act.etime,act.stime);
								}
							}
							dayrec.abwesenheitsminuten += act.workmins;
						} else if (act.topic && ((act.start && act.end) || (act.work))) {
							let {worktime,pj,err} = this.detworktime(act,dayrec,projects);
							if (err) {
								this.errcoll.collect(err,"Error determining worktime for activity element",act);
								break proc;
							}
							if (!dayrec.first_start || this.timeless(act.stime,dayrec.first_start)) {
								dayrec.first_start = act.stime;
							}
							if (!dayrec.last_end || this.timeless(dayrec.last_end,act.etime)) {
								dayrec.last_end = act.etime;
							}
							if (worktime>0) {
								if (!pj || !pj.psp) {
									if (!this.nichtleistungen[act.topic]) {
										this.nichtleistungen[act.topic] = new Array();
									}
									this.nichtleistungen[act.topic].push({ datnum: datnum, act: act, reason: "no project found/matched or project has no PSP-Code"});
									if (!dayrec.nichtleistung) {
										dayrec.nichtleistung = { minuten: 0 };
									}
									dayrec.nichtleistung.minuten += worktime;
									if (!this.nichtleistungen.total) {
										this.nichtleistungen.total = { nichtleistungsminuten: 0, nichtleistungsstunden: "" };
									}
									this.nichtleistungen.total.nichtleistungsminuten += worktime;
									this.nichtleistungen.total.nichtleistungsstunden = this.mins2exphours(this.nichtleistungen.total.nichtleistungsminuten);
								} else {
									if (pj.distribute) {
										let distcount = 0;
										for (let dest of pj.distribute) {
											let dpj = null;
											if (dest.project) {
												dpj = this.findpjbyproject(dest.project,projects,dayrec.datnum);
												if (!dpj) {
													this.errcoll.collect(null,"could not find distribution destination project named \""+dest.project+"\"",
																				{ project: dest.project, distribute: pj.distribute, datnum: dayrec.datnum });
													break proc;
												}
											} else if (dest.psp) {
												let dpj = this.findpjbypsp(dest.psp,projects,dayrec.datnum);
												if (!dpj) {
													this.errcoll.collect(null,"could not find distribution destination project",
																			{ psp: dest.psp, distribute: pj.distribute, datnum: dayrec.datnum });
													break proc;
												}
											} else {
												this.errcoll.collect(null,"distribution destination for \""+pj.project+"\" has neither project nor psp",dest);
												break proc;
											}
											if (!dest.hasOwnProperty("factor")) {
												this.errcoll.collect(null,"distribution destination has no factor",dest);
												break proc;
											}
											this.assignworktime(dayrec, this.leistungen, dpj, Math.floor(worktime*dest.factor));
											distcount += dest.factor;
										}
										distcount = Math.round(distcount*10000)/10000;
										if (distcount!=1) {
											this.errcoll.collect(null,"project with distribution seems to have invalid destinations, cumulative distribution factor="+distcount,pj);
											break proc;
										}
									} else {
										this.assignworktime(dayrec,this.leistungen,pj,worktime);
									} 
								}
							}
						} else {
							this.errcoll.collect(null,"illegal activity record",act);
							break proc;
						}
					} /* end for act */
					if (dayrec.starttime) {
						dayrec.kommzeit = dayrec.starttime;
					}
					if (dayrec.endtime) {
						dayrec.gehzeit = dayrec.endtime;
					}
					if (!dayrec.kommzeit) {
						if (dayrec.first_start) {
							dayrec.kommzeit = dayrec.first_start;
						}
					}
					if (!dayrec.gehzeit) {
						if (dayrec.last_end) {
							dayrec.gehzeit = dayrec.last_end;
						}
					}
					if (dayrec.kommzeit && dayrec.gehzeit) {
						dayrec.anwesenheitsminuten = this.expminutes(dayrec.kommzeit,dayrec.gehzeit);
						if (dayrec.anwesenheitsminuten>360) {
							if (!dayrec.hatMP) {
								dayrec.anwesenheitsminuten -= 30;	/* Mittagspause, if not explicitely noted */
							}
						}
					} else if (dayrec.anwesend) {
						dayrec.anwesenheitsminuten = this.time2expmins(dayrec.anwesend)
					} else {
						this.errcoll.collect(null,"cannot calculate anwesenheit for day: neither anwesend nor (kommen and gehen)",dayrec);
						break proc;
					}
					dayrec.vergangeneminuten = dayrec.anwesenheitsminuten;
					dayrec.vergangenestunden = this.mins2exptime(dayrec.anwesenheitsminuten);
					dayrec.anwesenheitsminuten -= dayrec.abwesenheitsminuten;
					this.days[datnum] = aux.deepCopy(dayrec);
				} catch (e) {
					this.errcoll.collect(e,"Error processing day data",{ dacc: dacc, dayrec: dayrec, act: act });
					break proc;
				}
			}
		}
		aux.escapeAllHTMLAllStrings(this.hldebuglog);
		this.ppush({ type: "result", 
					 result: new aux.Result({	resulttype: "object", 
												resultobject: {	vondatnum:			vondatnum,
																bisdatnum:			bisdatnum, 
																days:				this.days,
																leistungen:			this.leistungen, 
																nichtleistungen:	this.nichtleistungen,
																hldebuglog:			this.hldebuglog
															  }})});
	}

#
# <<TSTEPNUM:14>>: now make a table for CATS accounting: all days in range as columns, accounting in half-hour chunks
#
qexpression
	proc: {
		let pjrows = {};
		let crow, brow;
		let trows = new Array();
		let mdata = new Array();
		let dlog = new Array();
		mdata.push({ name: "Projekt"});
		mdata.push({ name: "PSP-Element"});
		mdata.push({ name: "Summe"});
		crow = new Array();
		crow.push("Anwesenheiten");
		crow.push("Tagessumme");
		crow.push(0);
		trows.push(crow);
		crow = new Array();
		crow.push("Projekte");
		crow.push("Tagessumme");
		crow.push(0);
		trows.push(crow);
		let pj, srn;
		dlog.push("start creating CATS table, projekte: "+aux.objTxt(this.leistungen.projekte));
		for (let ipsp in this.leistungen.projekte) {
			pj = this.leistungen.projekte[ipsp];
			dlog.push(aux.objTxt({ ipsp: ipsp, pj: pj }));
			if (!pj.hasOwnProperty("seq")) continue;
			crow = new Array();
			crow.push(pj.project);
			crow.push(ipsp);
			crow.push(0);
			srn = Number(pj.seq)+2;
			while (trows.length<srn) {
				brow = new Array();
				brow.push("---");
				brow.push("---");
				brow.push(0);
				trows.push(brow);
				dlog.push("pushed empty filler row with sequence number "+srn);
			}
			if (trows.length==srn) trows.push(null);
			if (trows[srn] && trows[srn][0]!="---") {  /* row is occupied (but not with "blank cover" "---") */
				this.errcoll.collect(null,"project sequence row "+srn+" for seq="+pj.seq+" for psp="+ipsp+" already existing",{ trows: trows, dlog: dlog });
				break proc;
			}
			trows[srn] = crow;
			pjrows[ipsp] = { psp: ipsp, rownum: srn, bufmins: 0, acctsum: 0.0 };
			dlog.push("create project row ipsp="+ipsp+": "+aux.objTxt(pjrows[ipsp]));
		}
		let datnum = this.query.vondatnum;
		let datrec, mins, bufmins, psp, daysum, accamt, daypresence;
		let rangesum = 0.0;
		let rangepresence = 0.0;
		while (datnum<=this.query.bisdatnum) {					/* make one set of 2 columns for each day to be accounted     */
			mdata.push({name: datnum, class: "vertical"});		/* column for day work hours */
			mdata.push({name: "Details", class: "vertical"});	/* column for "Details", helps in copy/paste to SAP CATS form */
			daysum = 0.0;
			daypresence = 0.0;
			/* logger.debug("678: datnum="+datnum); */
			if (this.days[datnum]) {
				if (this.days[datnum].anwesenheitsminuten) {
					daypresence = this.days[datnum].anwesenheitsminuten / 60;
				}
			}
			if (!this.leistungen[datnum]) {		/* empty day */
				for (let pnum=2; pnum<trows.length; pnum++) trows[pnum].push(""); 
			} else {							/* day with accounting data */
				for (let pnum=2; pnum<trows.length; pnum++) {
					if (!trows[pnum]) trows.pnum = { psp: "NO_PSP", bufmins: 0 };
					/* logger.debug("688: trows: "+aux.objTxt(trows)); */
					psp = trows[pnum][1];
					if (!pjrows[psp]) pjrows[psp] = { psp: "NO_PSP", bufmins: 0 };
					bufmins = pjrows[psp]?(pjrows[psp].bufmins):0;
					if (this.leistungen[datnum].leistungen[psp]) {
						mins = this.leistungen[datnum].leistungen[psp].minuten;
						if ((mins+bufmins) < 30) {				/* less than half hour to be accounted */
							trows[pnum].push("");				/* account nothing */
							bufmins = mins+bufmins;				/* save (accumulated) remainder */
						} else {
							mins = bufmins+mins;				/* total minutes so far for this PSP */
							bufmins = mins % 30;				/* save remainder from half-hours for further accounting */
							mins = mins-bufmins;				/* calculate whole half-hours */
							accamt = (mins/60);
							trows[pnum].push(accamt.toLocaleString("DE"));				/* account whole half-hours */
							pjrows[psp].acctsum += accamt;
							trows[pnum][2] = pjrows[psp].acctsum.toLocaleString("DE");	/* update project sum */
							daysum += accamt;
						}
					} else {
						trows[pnum].push("");
					}
					pjrows[psp].bufmins = bufmins;			/* keep remainder for further accounting */
					if (!this.leistungen.projekte[psp]) this.leistungen.projekte[psp] = { psp: "NO_PSP", restminuten: 0 };
					this.leistungen.projekte[psp].restminuten = bufmins;	/* remember remaining minutes for possible further use */
				}
			}
			datnum = this.nextdatnum(datnum);
			if (daypresence) {
				trows[0].push(daypresence.toLocaleString("DE"));					/* daily presence sum */
				rangepresence += daypresence;
			} else {
				trows[0].push("");
			}
			if (daysum) {
				trows[1].push(daysum.toLocaleString("DE"));							/* day sum */
				rangesum += daysum;
			} else {
				trows[1].push("");
			}
			for (let pnum=0; pnum<trows.length; pnum++) trows[pnum].push("");		/* empty "Details" column follows each day work column */
		}
		trows[0][2] = rangepresence.toLocaleString("DE");
		trows[1][2] = rangesum.toLocaleString("DE");
		let tres = new aux.Result({resulttype: "dbresult", metaData: mdata, rows: trows});
		if (typeof(tres["setResultAttribute"])!='function') {
			this.errcoll.collect(null,"tres is bad",{ tres: tres });
			break proc;
		}
		let title = "Leistungs-Aufstellung aus MFILE "+this.query.wnmfid+" von "+this.query.vondatnum+" bis "+this.query.bisdatnum; 
		this.protocol.title = title;
		title = "CATS-Buchungen für "+title;
		tres.setResultAttribute("title",title);
		this.ppush({ type: "result", result: tres });
	}