#
#		ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:		dquermfile_loadCuCCCallStat.txt
#	DESCRIPTION:	DQUERY definition for CCDB DQUERY "loadCuCCCallStat"
#
@querytitle							CuCC Statistiken aus managed File nach CCSERVSTAT laden
@querydescription					lädt CuCC-Anruf-Statistiken (MS,WS)
@group								SVCLSLSTATSTAT
@attributenames						mfileid:cucccallstatmfileid,filedatatype:string
@querytype							function
@function							seqtrans.seqtrans
@input_mode							MFILE                             
@resultobject_options.expandall		true
@debug								false
@init.aexpression
	proc: {
		if (!this.protocol) this.protocol = new Array();
		if (!this.connectionBundle) {
			this.connectionBundle = new db.ConnectionBundle();
		}
		this.cucc = require("./cucc.js")(logger,"seqtrans_tsteps_loadCuCCCallStat",prefs);
		this.ccservstat_insert_sql =	
			"insert into CCSERVSTAT(accPeriodType,datum,eventCategory,eventNumber,eventTime,eventType,eventUnit,jahr,kw,loadMfileId,monat,serviceName,tag) "+
			"values(::accPeriodType::,::datum::,::eventCategory::,::eventNumber::,::eventTime::,::eventType::,::eventUnit::,::jahr::,::kw::,::loadMfileId::,::monat::,::serviceName::,::tag::)";
		this.ccservstat_insert_sqlparams = "accPeriodType,datum,eventCategory,eventNumber,eventTime,eventType,eventUnit,jahr,kw,loadMfileId,monat,serviceName,tag";
		switch (this.query.filedatatype) {
			case "Call":
			case "Email":
			case "Cat":
				break;
			default:
				this.errcoll.collect(null,"Illegal filedatatype \""+this.query.filedatatype+"\"",this.query);
				break proc;
		}
		this.filedatatype = this.query.filedatatype;
		this.filedatatypes = {
			Call:		{	file_columns:		[	"Datum","Ankommend","Angenommen","Verloren","VerlorenKl10","AngenommenImSL",
													"durchGSZ","maxGSZ","GSZgesamt","durchWarteZeit","maxWarteZeit","handledCallRate",
													"serviceLevel","durchNBZ","NBZgesamt"	],
							startline:			6,	
							endexpression: 		"((typeof(lba[0])==\"string\") && (lba[0].startsWith(\"Total:\")))",
							headersInFirstLine:	false
						},
			Email:		{	file_columns:		[	"Datum","Ankommend","AngenommenImSL","serviceLevel"	],
							startline:			8,
							endexpression:		"((typeof(lba[0])==\"string\") && (lba[0].startsWith(\"Summe:\")))",
							headersInFirstLine:	false
						},
			Cat:		{	file_columns:		[	"Kategorietext","Anzahl"	],
							startline:			9,
							endexpression:		null,
							headersInFirstLine:	false
						}
		};
		this.filetypecontrols = this.filedatatypes[this.filedatatype];
		this.input_options 						= this.input_options || {};
		this.input_options.startline			= this.filetypecontrols.startline;
		this.input_options.colnames				= this.filetypecontrols.file_columns.join(",");
		this.input_options.endexpression		= this.filetypecontrols.endexpression;
		this.input_options.headersInFirstLine	= this.filetypecontrols.headersInFirstLine;
		this.cucc.loadCategoryTree(this,this);
	}
@setup.qexpression
	this.ppush([this.phead("setup"),"query","input_option",aux.objTxt(this.query.input_options,99,999999)]);
	this.ppush([this.phead("setup"),"mfileinfo",aux.objTxt(this.mfileinfo)]);
	proc: {
		this.gld = { mfileid: this.mfileinfo.mfileid };
		this.mfileattributes = this.mfileinfo.mfileattributes;
		if (!this.mfileattributes.state) {
			this.errcoll.collect(null,"Error in loadCuCCStatRecord: mfile has no valid state attribute",this.mfileinfo);
			break proc;
		}
		if (this.mfileattributes.state!="to_load") {
			logger.error("Error in loadCuCCStatRecord: mfile attribute \"state\" should be \"to_load\", but is \""+this.mfileattributes.state+"\"");
			this.errcoll.collect(null,"Error in loadCuCCStatRecord: mfile attribute \"state\" should be \"to_load\", but is \""+this.mfileattributes.state+"\"",this.mfileinfo);
			break proc;
		}
		this.nfs = this.mfileinfo.mfilename.split("_");
		if (this.nfs.length<4) {
			this.errcoll.collect(null,"Error in loadCuCCCallStat: mfilename has less than 4 fields",this.mfileinfo);
			break proc;
		}
		this.ppush([this.phead("loadCuCCCallStat.setup"),"","nfs",aux.objTxt(this.nfs)]);
		switch (this.nfs[0]) {
			case "WS":
				if (this.nfs.length<5) {
					this.errcoll.collect(null,"Error in loadCuCCCallStat: acc period is weekly but too few fields in filename",this.mfileinfo);
					break proc;
				}
				this.gld.accpertype = "weekly";
				this.gld.jahr = this.nfs[3];
				if (!this.nfs[4]) {
					this.errcoll.collect(null,"Error in loadCuCCStatRecord: acc period is weekly but week field is not given",this.mfileinfo);
					break proc;
				}
				if (!this.nfs[4].startsWith("KW")) {
					this.errcoll.collect(null,"Error in loadCuCCCallStat: acc period is weekly but week field does not start with KW",this.mfileinfo);
					break proc;
				}
				this.gld.monat = null;
				this.gld.kw = this.nfs[4].substr(2,2);
				break;
			case "MS":
				this.gld.accpertype = "monthly";
				this.gld.accper = this.nfs[3].split(".");
				this.gld.jahr = this.gld.accper[0];
				this.gld.monat = this.gld.accper[1];
				this.gld.kw = null;
				break;
			default:
				this.errcoll.collect(null,"Error in loadCuCCCallStat: illegal accounting period marker '"+this.nfs[0]+"'",this.mfileinfo);
				break proc;
		}
		this.ppush([this.phead("loadCuCCCallStat.setup"),"","gld",aux.objTxt(this.gld)]);
		this.gld.service = this.nfs[2];
		if (this.gld.service=="SV-Clearing") this.gld.service = "SV-Clearingsystem";
		this.gld.eventtype = this.nfs[1];
		if (this.gld.eventtype=="Mails") this.gld.eventtype = "Email";
		this.load_options = { info: "dummy load options as base for missing_categories", missing_categories: null};
		this.ppush([this.phead("setup"),"","load_options",aux.objTxt(this.load_options)]);
	}
@final.aexpression
	this.ppush([this.phead("final"),"aexpression","load_options",aux.objTxt(this.load_options)]);
	this.result = new aux.Result({resulttype: 'object', resultobject: { messages: new Array(), protocol: this.protocol}});
	this.mfileattributes.state = "loaded";
	mfile.setMfileAttributes(this.mfileinfo.mfileid,this.mfileattributes,
		function(err,result) {
			if (err) {
				this.errcoll.collect(err,"Error setting loaded MFILE attribute state to \"loaded\"",result);
				return;
			}
			this.result.resultobject.messages.push("set state of MFILE <a href=\"/dquery?dataname=showfile&mfileid="+this.mfileinfo.mfileid+"\">"+this.mfileinfo.mfileid+"</a> to \"loaded\"");
			if (this.load_options.missing_categories) {
				this.cucc.saveMissingCategories(this,this.load_options.missing_categories,
					function(err,result) {
						if (err) {
							this.errcoll.collect(err,"Error saving missing categories",result);
							return;
					}
					this.result.resultobject.messages.push("Saved missing categories");
					this.result.resultobject.misscatsaveres = result;
					this();
				}.bind(this));
			} else {
				this();
			}
		}.bind(this));

~query.tsteps

#
# loadCuCCCallStat_tsteps
#
# this sequence of tsteps will be invoked for each record in the input dataset for loading CuCC call statistics data files
# CuCC call statistics datafiles are either monthly or weekly statistic files
# each record contains 13 counters for one day in the time period
#
#

#
# <<TSTEPNUM:0>>: 
#
qexpression 
	this.recordinfo = { mfileinfo: this.mfileinfo, filedatatype: this.filedatatype, recnum: this.recnum, cdata: this.cdata, input_options: this.input_options };
	this.ppush([this.phead("recnum="),"","",this.recnum]);


#
# <<TSTEPNUM:1>>: determine appropriate sequence from filedatatype
#
if (this.filedatatype=='Call')
goto create_Call_records

#
# <<TSTEPNUM:2>>: 
#
if (this.filedatatype=='Email')
goto create_Email_records

#
# <<TSTEPNUM:3>>:
#
if (this.filedatatype=='Cat')
goto create_Cat_records

#
# <<TSTEPNUM:4>>: if we came here, there's an error in the file data type
#
qexpression
	this.errcoll.collect(null,"Error in loadCuCCCallStat: illegal file data type \""+this.filedatatype+"\"",this.gld);

#
# <<TSTEPNUM:5>>: create_Call_records:	create the CCSERVSTAT records for one cucccallstat record, prepare common fields
#
create_Call_records:
qexpression
	this.ppush([this.phead("Call_records"),"","CDATA",aux.objTxt(this.cdata)]);
	this.idata	= aux.deepCopy(this.cdata);
	this.idata.handledCallRate	=	aux.parseDimensionedNumber(this.idata.handledCallRate);
	this.idata.serviceLevel		=	aux.parseDimensionedNumber(this.idata.serviceLevel);
	this.cdata = {};
	this.cdata.serviceName		=	this.gld.service;
	this.cdata.accPeriodType	=	this.gld.accpertype;
	this.cdata.loadMfileId		=	this.gld.mfileid;
	this.cdata.jahr				=	this.gld.jahr;
	this.cdata.monat			=	this.gld.monat;
	this.cdata.kw				=	this.gld.kw;
	this.cdata.tag				=	0;
	this.cdata.eventType		=	this.gld.eventtype;
	this.cdata.datum			=	this.idata.Datum;
	this.cdata.eventCategory	=	"<undef>";
	this.cdata.eventUnit		=	"<undef>";
	this.cdata.eventNumber		=	null;
	this.cdata.eventTime		=	null;

#
# <<TSTEPNUM:6>>: create "Ankommend"
#
pre_qexpression 
	this.cdata.eventCategory	= "Ankommend";
	this.cdata.eventUnit		= "Anrufe";
	this.cdata.eventNumber		= this.idata.Ankommend;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@

#
# <<TSTEPNUM:7>>: create "Angenommen"
#
pre_qexpression 
	this.cdata.eventCategory	= "Angenommen";
	this.cdata.eventUnit		= "Anrufe";
	this.cdata.eventNumber		= this.idata.Angenommen;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:8>>: create "Verloren"
#
pre_qexpression 
	this.cdata.eventCategory	= "Verloren";
	this.cdata.eventUnit		= "Anrufe";
	this.cdata.eventNumber		= this.idata.Verloren;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:9>>: create "VerlorenKl10"
#
pre_qexpression 
	this.cdata.eventCategory	= "VerlorenKl10";
	this.cdata.eventUnit		= "Anrufe";
	this.cdata.eventNumber		= this.idata.VerlorenKl10;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:10>>: create "AngenommenImSL"
#
pre_qexpression 
	this.cdata.eventCategory	= "AngenommenImSL";
	this.cdata.eventUnit		= "Anrufe";
	this.cdata.eventNumber		= this.idata.AngenommenImSL;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:11>>: create "durchGSZ"
#
pre_qexpression 
	this.cdata.eventCategory	= "durchGSZ";
	this.cdata.eventUnit		= "Zeit";
	this.cdata.eventNumber		= null;
	this.cdata.eventTime		= this.idata.durchGSZ;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:12>>: create "maxGSZ"
#
pre_qexpression 
	this.cdata.eventCategory	= "maxGSZ";
	this.cdata.eventUnit		= "Zeit";
	this.cdata.eventNumber		= null;
	this.cdata.eventTime		= this.idata.maxGSZ;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:13>>: create "durchWarteZeit"
#
pre_qexpression 
	this.cdata.eventCategory	= "durchWarteZeit";
	this.cdata.eventUnit		= "Zeit";
	this.cdata.eventNumber		= null;
	this.cdata.eventTime		= this.idata.durchWarteZeit;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:14>>: create "maxWarteZeit"
#
pre_qexpression 
	this.cdata.eventCategory	= "maxWarteZeit";
	this.cdata.eventUnit		= "Zeit";
	this.cdata.eventNumber		= null;
	this.cdata.eventTime		= this.idata.maxWarteZeit;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:15>>: create "handledCallRate"
#
pre_qexpression 
	this.cdata.eventCategory	= "handledCallRate";
	this.cdata.eventUnit		= "Ratio";
	this.cdata.eventNumber		= this.idata.handledCallRate;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:16>>: create "serviceLevel"
#
pre_qexpression 
	this.cdata.eventCategory	= "serviceLevel";
	this.cdata.eventUnit		= "Ratio";
	this.cdata.eventNumber		= this.idata.serviceLevel;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:17>>: create "durchNBZ"
#
pre_qexpression 
	this.cdata.eventCategory	= "durchNBZ";
	this.cdata.eventUnit		= "Zeit";
	this.cdata.eventNumber		= null;
	this.cdata.eventTime		= this.idata.durchNBZ;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:18>>: create "NBZgesamt"
#
pre_qexpression 
	this.cdata.eventCategory	= "NBZgesamt";
	this.cdata.eventUnit		= "Zeit";
	this.cdata.eventNumber		= null;
	this.cdata.eventTime		= this.idata.NBZgesamt;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:19>>: all callstat fields processed
#
end

#
# <<TSTEPNUM:20>>: create_Email_records: create the CCSERVSTAT records for one cuccemailstat record, prepare common fields
#
label create_Email_records
qexpression
	this.idata	= aux.deepCopy(this.cdata);
	this.cdata = {};
	this.idata.serviceLevel		=	aux.parseDimensionedNumber(this.idata.serviceLevel);
	this.cdata.serviceName		=	this.gld.service;
	this.cdata.accPeriodType	=	this.gld.accpertype;
	this.cdata.loadMfileId		=	this.gld.mfileid;
	this.cdata.jahr				=	this.gld.jahr;
	this.cdata.monat			=	this.gld.monat;
	this.cdata.kw				=	this.gld.kw;
	this.cdata.tag				=	null;
	this.cdata.eventType		=	this.gld.eventtype;
	this.cdata.datum			=	this.idata.Datum;
	this.cdata.eventCategory	=	"<undef>";
	this.cdata.eventUnit		=	"<undef>";
	this.cdata.eventNumber		=	null;
	this.cdata.eventTime		=	null;

#
# <<TSTEPNUM:21>>: create "Eingegangen"
#
pre_qexpression 
	this.cdata.eventCategory	= "Eingegangen";
	this.cdata.eventUnit		= "Emails";
	this.cdata.eventNumber		= this.idata.Eingegangen;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@ 

#
# <<TSTEPNUM:22>>: create "BearbeitetImSL"
#
pre_qexpression 
	this.cdata.eventCategory	= "BearbeitetImSL";
	this.cdata.eventUnit		= "Emails";
	this.cdata.eventNumber		= this.idata.BearbeitetImSL;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@

#
# <<TSTEPNUM:23>>: create "ServiceLevel"
#
pre_qexpression 
	this.cdata.eventCategory	= "ServiceLevel";
	this.cdata.eventUnit		= "ServiceLevel";
	this.cdata.eventNumber		= this.idata.ServiceLevel;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@

end

#
# <<TSTEPNUM:24>>: create_Cat_records: create the CCSERVSTAT records for one cuccCat record, prepare common fields
#
label create_Cat_records
qexpression
	this.idata	= aux.deepCopy(this.cdata);
	this.cdata = {};
	this.cdata.serviceName		=	this.gld.service;
	this.cdata.accPeriodType	=	this.gld.accpretype;
	this.cdata.loadMfileId		=	this.gld.mfileid;
	this.cdata.jahr				=	this.gld.jahr;
	this.cdata.monat			=	this.gld.monat;
	this.cdata.kw				=	this.gld.kw;
	this.cdata.tag				=	null;
	this.cdata.eventType		=	this.gld.eventtype;
	this.cdata.datum			=	this.idata.Datum;
	this.cdata.eventCategory	=	"<undef>";
	this.cdata.eventUnit		=	"<undef>";
	this.cdata.eventNumber		=	null;
	this.cdata.eventTime		=	null;

#
# <<TSTEPNUM:25>>: create "Anzahl"
#
pre_qexpression 
	this.cdata.eventCategory	= this.cucc.categorizeCatText(this,this.idata.Kategorietext);
	this.cdata.eventUnit		= "AnzahlKontakte";
	this.cdata.eventNumber		= this.idata.Anzahl;
	this.cdata.eventTime		= null;
sql @@ccservstat_insert_sql@@
sqlparams @@ccservstat_insert_sqlparams@@

end