#
#				ITSV GmbH
#	CCDB - Command and Control Database
#
#	FILE:				dquerymfile_LOADBIGFILETABLE.txt
#	DESCRIPTION:	DQUERY definition for DQUERY LOADBIGFILETABLE
#					loads a BIG file into a table
#
#
@querytitle				große Datei aus managed file in Tabelle laden
@querydescription		lädt eine Datei direkt mit Mitteln der Datenbank aus einem managed file, muss eine CSV-Datei sein
@group					DATAFILE
@attributenames			mfid:mfileid:{{mfidoptions}},cfgname:dbconfigname:{{cfgnameoptions}},tabname:string:{{tabnameoptions}},linesep:linesep:{{linesepoptions}},fieldsep:string:{{fieldsepoptions}},headfirstline:yesno:{{headfirstlineoptions}},charset:string:{{charsetoptions}}
@mfidoptions			{
	labeltext:	"Managed File",
	typedesc:	"Managed File, das in die Tabelle geladen werden soll"	}
@cfgnameoptions			{ "labeltext":"Datenbank","typedesc":"Name der Datenbank-Konfiguration, in der die Datei in die Tabelle geladen werden soll", "is_optional": true}
@tabnameoptions			{ "labeltext":"Tabelle","typetext":"Name der Tabelle in die Datenbank, in die die Datei geladen werden soll"}
@linesepoptions	{
	type:		{	structure: "scalar.enum", 
					vlist:	"CR:CarriageReturn:Carriage Return,"+
							"LF:LineFeed:Line Feed,"+
							"CRLF:CR+LF:CarriageReturn dann Line Feed,"+
							"LFCR:LF+CR:Line Feed dann Carriage Return" 
				},
	labeltext:	"Zeilentrenner",
	typedesc:	"Zeichen(folge), durch die Zeilen (Datensätze) in der Datei getrennt sind",
	is_optional:	true }
@fieldsepoptions		{ "labeltext":"Feldtrennzeichen","typetext":"Zeichen(folge), mit dem(der) Felder untereinander getrennt sein sollen","is_optional":"true","default":";"}
@headfirstlineoptions	{ "labeltext":"Kopf in erster Zeile","typetext":"gibt an, ob die erste Zeile übersprungen werden soll weil sie Spaltenkopfnamen enthält","is_optional":"true"}
@charsetoptions	{
	type:		{	structure: "scalar.enum",
					vlist:		"UTF-8:UTF-8:UTF-8,latin1:ISO-Latin-1:ISO-Latin-1"
				},
	labeltext:	"Zeichensatz",
	typedesc:	"Zeichensatz/Encoding, in dem die CSV-Datei dargetsellt ist",
	is_optional:	true }
@querytype				function
@function				seqtrans.seqtrans
@init.qexpression
	this.inputresult = new aux.Result({ resulttype: 'dbresult', rows: [[0]], metaData: [ { name: "column0" } ] });

~query.tsteps

#
# <<TSTEPNUM:0>>: prepare parameters
#
qexpression
	logger.debug(this.phead("queryparameters")+".query: "+aux.objTxt(this.query));
	if (this.query.fieldsep) {
		this.fieldsep = ";";
		switch (this.query.fieldsep) {
			case "TAB":		this.fieldsep = "\\t";					break;
			case "COMMA":	this.fieldsep = ",";					break;
			default:		this.fieldsep = this.query.fieldsep;
		}
	} else {
		this.fieldsep = ";";
	}
	if (this.query.cfgname) {
		this.cfgname = this.query.cfgname;
	} else {
		this.cfgname = db.getCurrentConfig().getName();
	}
	if (this.query.linesep) {
		this.linesep = "\\r\\n";
		switch (this.query.linesep) {
			case "CR":		this.linesep = "\\r"; 		break;
			case "LF":		this.linesep = "\\n"; 		break;
			case "CRLF":	this.linesep = "\\r\\n";	break;
			case "LFCR":	this.linesep = "\\n\\r";	break;
			default:		this.errcoll.collect(null,"Illegal token \""+this.query.linesep+"\" for line separator",this.query);
		}
	} else {
		this.linesep = "\\r\\n";
	}
	if (this.query.headerfieldnames) {
		if (Array.isArray(this.query.headerfieldnames)) {
			this.headerfieldnames = aux.deepCopy(this.query.headerfieldnames);
		} else if (typeof(this.query.headerfieldnames)=="string") {
			this.headerfieldnames = this.query.headerfieldnames.split(",");
		} else {
			this.errcoll.collect(null,"illegal form for query parameter \"headerfieldnames\"",this.query);
		}
	}
	this.tabname = this.query.tabname;
	this.headfirstline = aux.getBoolOption(this.query,"headfirstline",true);
	if (this.query.hasOwnProperty("charset")) {
		this.charset = this.query.charset;
	}
	
#
# <<TSTEPNUM:1>>: get MFILE information
#
aexpression
	mfile.get_mfile_data(this.query.mfid,
							function(err,res) {
								this.mferr     = err;
								this.mfileinfo = res;
								if (this.mferr) {
									this.nerr = new Error("Error getting MFILE information for "+this.query.mfileid+": "+this.mferr.message);
									this(nerr,this.mfileinfo);
									return;
								}
								if (this.mfileinfo && this.mfileinfo.ftype && this.mfileinfo.ftype.ext) {
									this.filetype = String(this.mfileinfo.ftype.ext).toUpperCase();
								} else {
									this.nerr = new Error("Invalid MFILE information for "+this.query.mfileid+
									                      ", no result or no filetype(ftype)/extension(ftype.ext): "+aux.objTxt(this.mfileinfo));
									this(this.nerr,this.mfileinfo);
									return;
								}	
								if (this.mfileinfo && this.mfileinfo.mfilepath) {
									this.mfilepath = this.mfileinfo.mfilepath;
								} else {
									this.nerr = new Error("Invalid MFILE information for "+this.query.mfileid+", no result or no mfilepath: "+aux.objTxt(this.mfileinfo));
									this(this.nerr,this.mfileinfo);
									return;
								}
								this();
							}.bind(this),
						null,
						{ getfiledata: false }
						);
post_qexpression
	this.ppush([this.phead("GOTMFILEPATH"),"MFILEPATH="+this.mfilepath,"",""]);

#
# TODO: <<TSTEPNUM:2>>: if MFILE is a (ZIP) archive, unpack it
#
aexpression
	load_file.determineDataContainerTypeFromMfileInfo(this.mfileinfo,null,
		function(err,res) {
			this.containerType = res.container_type;
			this.ppush([this.phead("MFILECONTAINERTYPE"),"CONTAINER_TYPE="+this.containerType,"",""]);
			this();
		}.bind(this)
	);

#
# <<TSTEPNUM:4>>: 
#
if		(this.container_type!="gz")
goto 	nogz

#
# <<TSTEPNUM:5>>:
#
qexpression
	this.errcoll.collect(null,"container type GZ not yet implemented");

#
# <<TSTEPNUM:5>>: 
#
nogz:
if		(this.container_type!="zip")
goto	nozip

#
#
#
qexpression
	this.errcoll.collect(null,"container type ZIP not yet implemented");

#
# <<TSTEPNUM:6>>: no archive or archive already unpacked
#
nozip:
qexpression
	this.ppush([this.phead("CONTAINER_TYPE"),"input file container type is <"+this.container_type+">","",""]);

#
# <<TSTEPNUM:3>>: get mysql base path and path to command line client executable
#
prep_mysql:
sql					select @@basedir
result_varname		sqlres
post_qexpression
	this.mysql_basedir = this.sqlres.getCellValue(0,0);
	this.mysql_bindir = this.mysql_basedir + "\\bin";
	this.mysql_clientexe = this.mysql_bindir + "\\mysql.exe";

#
# <<TSTEPNUM:4>>: create SQL command file and invocation console command for MS-Windows(!)
#
#	prior to DB-loading, a conversion command may be issued to convert the input file to CSV
#		note: XLSX is detected as ZIP by the filetype-detector, so ZIP also leads to excel2csv
#
#	ATTENTION: the LOAD DATA LOCAL INFILE command needs the "local_infile" variable set, see:
#		https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile 
#
qexpression
	proc: {
		let fsepclause = " FIELDS TERMINATED BY '"+this.fieldsep+"' ";
		let quotclause = " OPTIONALLY ENCLOSED BY '\"' ";
		let ignlclause = this.headfirstline?" IGNORE 1 LINES ":"";
		let lsepclause = " LINES TERMINATED BY '"+this.linesep+"' ";
		let charsetclause = "";
		if (this.charset) {
			charsetclause = " CHARACTER SET "+this.charset+" ";
		}
		let loadfilepath = this.mfilepath;
		this.batcmd = "";
		if (this.filetype!="CSV") {
			if (this.filetype=="ZIP" || this.filetype=="XLS" || this.filetype=="XLSX" || this.filetype=="xls" || this.filetype=="xlsx") {
				this.batcmd += "set Path=%Path%;c:\\ta3j\\prg\\pmtools\\ccdb\\app\r\n";
				loadfilepath = aux.makeUniqueTempFileName() + "_LOADBIGFILE.csv";
				this.batcmd += "excel2csv "+this.mfilepath+" "+loadfilepath+"\r\n";
			} else {
				this.errcoll.collect(null,"cannot process filetype <"+this.filetype+">",{ mfileinfo: this.mfileinfo, container_type: this.container_type });
				break proc;
			}
		}
		let loadfilepathesc = loadfilepath.replace(/\\/g,"\\\\");
		this.loadsqlcmd = "set GLOBAL \"local_infile\"='ON';";
		this.loadsqlcmd +=	"\r\nLOAD DATA LOCAL INFILE '"+loadfilepathesc+"' INTO TABLE "+this.tabname+
							" "+charsetclause+
							" "+fsepclause+
							" "+quotclause+
							" "+lsepclause+
							" "+ignlclause;
		this.sqlfilename = aux.makeUniqueTempFileName() + "_LOADBIGFILE.sql";
		fs.writeFileSync(this.sqlfilename,this.loadsqlcmd);
		this.batfilename = aux.makeUniqueTempFileName() + "_LOADBIGFILE.bat";
		this.batcmd += this.mysql_clientexe + " -uroot -p8May2K051 -Dccdb --local-infile=1 <"+this.sqlfilename;
		fs.writeFileSync(this.batfilename,this.batcmd);
		this.batcmd = this.batfilename;
		this.ppush([this.phead("CMDGENERATED"),"SQLFILE="+this.sqlfilename,"BATFILE="+this.batfilename,"BATCMD="+this.batcmd]);
	}

#
# <<TSTEPNUM:5>>: execute load command for windows
#
aexpression
	childprocess.exec(this.batcmd, { timeout: 30000 }, 
					function(err,stdout,stderr) {
						logger.debug(this.phead("EXEC_CALLBACK"));
						if (err) {
							logger.error("Error in executing child process");
							logger.error("Error: "+aux.objTxt({ err:err, stdout: stdout, stderr: stderr },9999,999999));
							this.errcoll.collect(err,"Error in loading "+this.query.mfid+", executing \""+this.batcmd+"\"", { err: err, stdout: stdout, stderr: stderr });
							this();
							return;
						}
						this.ppush([this.phead("cmdlineexecuted"),{ rawhtml: "STDOUT:<br/><pre>"+stdout+"</pre>" },{ rawhtml: "STDERR:<br/><pre>"+stderr+"</pre>"},""]);
						this();
					}.bind(this)
					);