#
#				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
	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
#
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>>:
#
qexpression
		this.testpjrex = function(rex,tpc,pj) {
			if (!rex) return null;
			let re = new RegExp(rex);
			return (re.test(tpc)?pj:null);
		}

#
# <<TSTEPNUM:9>>:
#
qexpression
		this.matchpj = function(tpc,pj) {
			if (pj.regexes) {
				for (let rex of pj.regexes) {
					if (this.testpjrex(rex,tpc,pj)) return pj;
				}
			}
			if (pj.regex) {
				if (this.testpjrex(pj.regex,tpc,pj)) return pj;
			}
			return null;
		}

#
# <<TSTEPNUM:10>>:
#
qexpression
		this.findpj = function (tpc,pjl) {
			for (let pj of pjl) {
				if (this.matchpj(tpc,pj)) return pj;
			}
			return null;
		}
		this.findpjbypsp = function(psp,pjl) {
			for (let pj of pjl) {
				if (pj.psp==psp) return pj;
			}
			return null;
		}
		this.findpjbyproject = function(project,pjl) {
			for (let pj of pjl) {
				if (pj.project==project) return pj;
			}
			return null;
		}
		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>>:
#
qexpression
		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;
			}		
		}
		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") };
			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);
				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>>: 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;
		}	
		let jahr = vondatnum.substr(0,4);
		if (!this.wndata[jahr]) {
			this.errcoll.collect(null,"kein Jahres-Eintrag für "+jahr,this.wndata);
			break proc;
		}
		let projects = this.wndata[jahr].K_Elemente;
		if (!projects) {
			this.errcoll.collect(null,"keine K_Elemente für das Jahr "+jahr,this.wndata);
			break proc;
		}
		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.daydate = this.datnum2date(datnum);
					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("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});
									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);
												if (!dpj) {
													this.errcoll.collect(null,"could not find distribution project "+dest.project,pj.distribute);
													break proc;
												}
											} else if (dest.psp) {
												let dpj = this.findpjbypsp(dest.psp,projects);
												if (!dpj) {
													this.errcoll.collect(null,"could not find distribution destination project PSP="+dest.psp,pj.distribute);
													break proc;
												}
											} else {
												this.errcoll.collect(null,"distribution destination has neither project nor psp",dest);
												break proc;
											}
											if (!dest.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;
										}
										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.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;
				} catch (e) {
					this.errcoll.collect(e,"Error processing day data",{ dacc: dacc, dayrec: dayrec, act: act });
					break proc;
				}
			}
		}
		this.ppush({ type: "result", 
					 result: new aux.Result({	resulttype: "object", 
												resultobject: {	vondatnum: vondatnum,
																bisdatnum: bisdatnum, 
																leistungen: this.leistungen, 
																nichtleistungen: this.nichtleistungen}})});
	}

#
# <<TSTEPNUM:13>>: now make a table for CATS accounting: all days in range as columns, accounting in half-hour chunks
#
qexpression
	proc: {
		let pjrows = {};
		let crow;
		let trows = new Array();
		let mdata = new Array();
		mdata.push({ name: "Projekt"});
		mdata.push({ name: "PSP-Element"});
		mdata.push({ name: "Summe"});
		crow = new Array();
		crow.push("Projekte");
		crow.push("Tagessumme");
		crow.push(0);
		trows.push(crow);		
		let pj, srn;
		for (let ipsp in this.leistungen.projekte) {
			pj = this.leistungen.projekte[ipsp];
			if (!pj.hasOwnProperty("seq")) continue;
			crow = new Array();
			crow.push(pj.project);
			crow.push(ipsp);
			crow.push(0);
			srn = Number(pj.seq)+1;
			while (trows.length<=srn) {
				trows.push(null);
			}
			if (trows[srn]) {
				this.errcoll.collect(null,"project sequence row "+srn+" for psp="+ipsp+" already existing",{ trows: trows });
				break proc;
			}
			trows[srn] = crow;
			pjrows[ipsp] = { psp: ipsp, rownum: srn, bufmins: 0, acctsum: 0.0 };
		}
		let datnum = this.query.vondatnum;
		let datrec, mins, bufmins, psp, daysum, accamt;
		let rangesum = 0.0;
		while (datnum<=this.query.bisdatnum) {
			mdata.push({name: datnum, class: "vertical"});
			daysum = 0.0;
			if (!this.leistungen[datnum]) {		/* empty day */
				for (let pnum=1; pnum<trows.length; pnum++) trows[pnum].push(""); 
			} else {							/* day with accounting data */
				for (let pnum=1; pnum<trows.length; pnum++) {
					psp = trows[pnum][1];
					bufmins = pjrows[psp].bufmins;
					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 */
					this.leistungen.projekte[psp].restminuten = bufmins;	/* remember remaining minutes for possible further use */
				}
			}
			datnum = this.nextdatnum(datnum);
			if (daysum) {
				trows[0].push(daysum.toLocaleString("DE"));							/* day sum */
				rangesum += daysum;
			} else {
				trows[0].push("");
			}
		}
		trows[0][2] = rangesum.toLocaleString("DE");
		let tres = new aux.Result({resulttype: "dbresult", metaData: mdata, rows: trows});
		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 });
	}