#
#			ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:			dquerymfile_loadcatsilvd.txt
#	DESCRIPTION:	DQUERY definition for DQUERY loadcatsilvd
#					this DQUEY loads a managed file containing ITSV CATS dump into table CCILVD
#
@querytitle			ITSV CATS-Auszug in CCILVD laden
@querydescription	lädt einen Auszug aus ITSV CATS in Form eines Excel-Spreadsheets in die Tabelle CCILVD
@group				ILV
@attributenames		mfileid:mfileid:{{mfileidoptions}},uskurzz:uskurzz:{{uskurzzoptions}},erasebefore:noyes:{{erasebeforeoptions}}
@mfileidoptions {
	labeltext:		"Lade-Datei",
	typedesc:		"Datei, die in die Leistungserfassungstabelle CCILVD geladen werden soll. Muss eine Excel-Datei oder CSV mit Spalten Datum,Kontierungselement,Bezeichnung,Stunden sein" }
@uskurzzoptions {
	labeltext:		"Mitarbeiter",
	typedesc:		"Kurzzeichen des Mitarbeiter, für den Zeiterfassng ist" }
@erasebeforeoptions {
	labeltext:		"Vorher löschen",
	typedesc:		"wenn \"ja\" werden eventuell für die im Auszug enthaltenen Jahre bereits in CCILVD für den Mitarbeiter vorhandene Daten gelöscht, wenn \"Nein\" wird bei vorhandenen Daten die Verarbeitung abgebrochen" }
@querytype			tseq
@input_options	{
	debug:			true }
@FLIST				Datum,Kontierungselement,Bezeichnung,Aktivität,Stunden

~query.tsteps

#
# <<TSTEPNUM:0>>: 
#
if 		(this.init_done)
goto	record_proper

#
# <<TSTEPNUM:1>>: init
#
qexpression
	proc: {
		if (!this.init_done) {
			this.copyqvars("uskurzz,erasebefore");
			this.erasebefore = aux.boolFromString(this.erasebefore);
			if (this.inputresult.getRowCount()<1) {
				this.errcoll.collect(null,"input data has no rows",{ inputresult: this.inputresult });
				break proc;
			}
			let crow = this.inputresult.getRowObject(0);
			this.ppush([this.phead("ROWSTRUCTURE"),"<pre>ROW[0]: "+aux.objTxt(crow)+"</pre>","",""]);
			this.recsprocessed = 0;
			this.years = {};
			this.allyears = new Array();
			let cr,cy;
			let have_years = false;
			for (let ri=0; ri<this.inputresult.getRowCount(); ri++) {
				cr = this.inputresult.getRowObject(ri);
				cy = cr.Datum.getFullYear();
				if (!this.years[cy]) {
					have_years = true;
					this.allyears.push(cy);
					this.years[cy] = { present: true };
				}
			}
			this.yearlist = this.allyears.join(",");
			logger.debug(this.phead("OLDDATAYEARS")+".old_years_data="+this.yearlist);
			this.ppush([this.phead("OLDDATAYEARS"),"old_years_data="+this.yearlist,"",""]);
			this.init_done = true;
		}
	}

#
# <<TSTEPNUM:2>>:
#
pre_qexpression
	this.ssql = "select count(*) from CCILVD where USKURZZ='"+this.uskurzz+"' and JAHR in ("+this.yearlist+")";
	logger.debug(this.phead("CHKOLDDATA")+".SQL=\""+this.ssql+"\"");
sql_varname		ssql
result_varname	yres
post_qexpression
	logger.debug(this.phead("OLDTABLEDATA: ")+aux.objTxt(this.yres));
	this.have_old_data = (this.yres.getCellValue(0,0)>0);

#
# <<TSTEPNUM:3>>:
#
if 		(!this.have_old_data)
goto	record_proper

#
# <<TSTEPNUM:4>>:
#
qexpression
	proc: {
		if (!this.erasebefore) {
			this.errcoll.collect(null,"There is old data for "+this.uskurzz+" in years "+this.yearlist+" in the database and <erasebefore> is not given");
			break proc;
		}
	}

#
# <<TSTEPNUM:5>>:
#
sql				delete from CCILVD where USKURZZ=::uskurzz:: and JAHR in (@@yearlist@@)
named_params	uskurzz
result_varname	dres
post_qexpression
	proc: {
		logger.debug(this.phead("OLDDATADELETED: ")+aux.objTxt(this.dres));
		if (this.dres.resulttype && this.dres.resulttype=='object' && this.dres.resultobject && this.dres.resultobject.affectedRows) {
			this.ppush([this.phead("ERASEDOLDDATA"),"records_deleted="+this.dres.resultobject.affectedRows,"",""]);
		} else {
			this.errcoll.collect(null,"Unknown result in erasing old data",{ result: this.dres } );
			break proc;
		}
	}

#
# <<TSTEPNUM:3>>: store one row
#
record_proper:
pre_qexpression
	this.jahr		= this.cdata.Datum.getFullYear();
	this.monat		= this.cdata.Datum.getMonth()+1;
	this.tag		= this.cdata.Datum.getDate();
	this.psp		= this.cdata.Kontierungselement;
	this.pspdetail	= this.cdata.Bezeichnung;
	this.stunden	= this.cdata.Stunden;
record_proper:
sql				insert into CCILVD(USKURZZ,JAHR,MONAT,TAG,PSP,PSPDETAIL,STUNDEN,KOSTEN) values(::uskurzz::,::jahr::,::monat::,::tag::,::psp::,::pspdetail::,::stunden::,0)
named_params	uskurzz,jahr,monat,tag,psp,pspdetail,stunden
result_varname	ires
post_qexpression
	proc: {
		if (this.ires.resulttype && this.ires.resulttype=='object' && this.ires.resultobject && this.ires.resultobject.affectedRows) {
			this.recsprocessed += this.ires.resultobject.affectedRows;
		} else {
			this.errcoll.collect(null,"Unknown result in inserting row data",{ rownum: this.rownum, cdata: this.cdata } );
			break proc;
		}
	}

#
# <<TSTEPNUM:4>>: finish protocol if last record
#
qexpression
	if (this.is_last_record) {
		this.ppush([this.phead("FINISHED"),"RECSPROCESSED="+this.recsprocessed,"",""]);
	}