# # ITSV GmbH # CCDB - Command and Control Database # # FILE: dquerymfile_LOADMVBRFAUSW.txt # DESCRIPTION: dquery definition for LOADMVBRFAUSW # this query loads Auswertung_MeldungenClearingfälle for 1 Träger and 1 Day from 1 maanged file into # table CCMVBRFAUSW # and update statistics counts for Meldungen, Clearingfälle, exOffo-mBGMs # in tables: SVCLMVBRFASVTTAG (Meldungen per SVT and day (meldungsdatum=dokumentDatum)), # svclmvbclfsvttag (Clearingfälle per SVT and day (meldungsdatum=dokumentDatum)), # svclexoffosvttag (exOffo-mBGMs per SVT and day (meldungsdatum=dokumentDatum)), # svclmvbclflswtag (Clearingfälle per LSW-ID and day (meldungsdatum=dokumentDatum) and rückfragetyp (RUECKFRAGETYPCODE) ) # # HISTORY: when who what # 20200910 WSC create CF and exOffo-counters only on demand # 20211221 WSC include traeger and file timestamp in SUMDUMP file name # new counters for CF per LSWID per day # 20220103 WSC convert updateMELDUNGEN/CF/EXOFFO to usage of subroutine objectDoForEach # convert CF counters to one table record per TRAEGER/DATNUM # # @group SVCLMVBRFAUSWDATALOAD @querytitle Träger-MVB-Auswertung_MeldungenClearingfaelle in Datenbank laden @querydescription lädt die tägliche Auswertung_MeldungenClearingfaelle für einen Träger in die Tabelle CCMVBRFAUSW @attributenames mfileid:mfileid:{{mfileidoptions}} @mfileidoptions {"labeltext":"Lade-Managed-File ID","typedesc":"Managed File ID der Datei, die geladen werden soll, diese muss den MFILETYPE \"dmvbrfausw\" haben"} @input_encoding iso-8859-15 @querytype function @function seqtrans.seqtrans @title MVB-Auswertungs-Datei [[mfilename]] geladen # field numbers when completed # 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 # field numbers when read from file # 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -- @FLIST TRAEGER,FTIMESTAMP,RECORDNUM,DOKUMENT_ID,DOKUMENTTYPCODE,DOKUMENTTYP_BEZ,DOKUMENTTYPGRUPPE_BEZ,DOKUMENT_DAT,DOKUMENTSTATUSCODE,DOKUMENTSTATUS,FACHINFO1,BEITRAGSKONTONUMMER,FACHINFO3,HERSTELLER,SOID,RUECKFRAGETYPCODE,EXTERN_JN,VERARBEITUNGSABBRUCH_KZ,VERARBEITUNGSABBRUCH_BEZ,PRIORITAETSACHBEARBEITER_JN,RUECKFRAGETEXTGESAMTSABE,RUECKFRAGESTATUSCODE,RUECKFRAGESTATUS,ORGANISATIONSEINHEIT_ID,ORGANISATIONSNAME,ERSTZUTEILUNG_DAT,WIEDERVORLAGE_DAT,WIEDERVORLAGEGRUND,LSWID,DOKDAT_DATNUM @FSIZELIST 2, 19, 19, 19, 19, 70, 32, 19, 3, 32, 32, 19, 32, 70, 70, 10, 1, 1, 70, 1, 500, 3, 32, 32, 70, 19, 19, 255, 32, 8 @TABLECREATESQL create table CCMVBRFAUSW (TRAEGER varchar(2),FTIMESTAMP varchar(19),DOKUMENT_ID varchar(19), DOKUMENTTYPCODE varchar(19),DOKUMENTTYP_BEZ varchar(70),DOKUMENTTYPGRUPPE_BEZ varchar(32), DOKUMENT_DAT varchar(19), DOKUMENTSTATUSCODE varchar(3),DOKUMENTSTATUS varchar(32), FACHINFO1 varchar(32),BEITRAGSKONTONUMMER varchar(19), FACHINFO3 varchar(32), HERSTELLER varchar(70),SOID varchar(70), RUECKFRAGETYPCODE varchar(10), EXTERN_JN varchar(1),VERARBEITUNGSABBRUCH_KZ varchar(1),VERARBEITUNGSABBRUCH_BEZ varchar(70), PRIORITAETSACHBEARBEITER_JN varchar(1), RUECKFRAGETEXTGESAMTSABE varchar(500), RUECKFRAGESTATUSCODE varchar(3), RUECKFRAGESTATUS varchar(32), ORGANISATIONSEINHEIT_ID varchar(32), ORGANISATIONSNAME varchar(70), ERSTZUTEILUNG_DAT varchar(19), WIEDERVORLAGE_DAT varchar(19), WIEDERVORLAGEGRUND varchar(255), LSWID varchar(32), DOKDAT_DATNUM varchar(8)) @TABLEKEYCREATESQL alter table ccmvbrfausw add constraint unique key ccmvbrfausw_utfd (TRAEGER,FTIMESTAMP,DOKUMENT_ID,RUECKFRAGETYPCODE,RUECKFRAGETEXTGESAMTSABE) @display_options { fullname_filter_regex: "dquery_progress\..*?\.(?:records_to_process|current_record|TRAEGER|FTIMESTAMP)" } @init.qexpression this.options.maxrecsteps = 1000000; this.monatlist = "JAN-FEB-MRZ-APR-MAI-JUN-JUL-AUG-SEP-OKT-NOV-DEZ-"; this.load_file = load_file; this.fieldsizes = this.query.FSIZELIST.split(","); for (let i=0; i25) { /* if #12 is no valid RUECKFRAGETYPCODE, HERSTELLER may be split up by a ";" in it */ let rex = /[A-Z]{1,2}[0-9]{2,5}/; while ((fldarr.length>25) && (fldarr[12]!="") && (!rex.test(fldarr[12]))) { rp.push("record has still "+fldarr.length+" fields, fld#12 is no valid RUECKFRAGETYPCODE (and also not empty), try merging #10+#11"); let rep = fldarr[10]+"%3B"+fldarr[11]; fldarr.splice(11,1); fldarr[10] = rep; linetext = fldarr.join(";"); } } if (fldarr.length>25) { /* check #18 for being valid RUECKFRAGESTATUSCODE */ let rex = /^[A-Z]{3}$/; if ((fldarr[18]=='') || (rex.test(fldarr[18]))) { rp.push("more than 25 fields, but fld#18 is valid RUECKFRAGESTATUSCODE \""+fldarr[18]+"\", probably no error in 17/18"); } else { while ((fldarr.length>25) && (fldarr[18]!='') && (!rex.test(fldarr[18]))) { rp.push("fld#18 is invalid as RUECKFRAGESTATUSCODE: \""+fldarr[18]+"\", try merging #17+#18"); let rep = fldarr[17] + "%3B" + fldarr[18]; fldarr.splice(18,1); fldarr[17] = rep; linetext = fldarr.join(";"); } } } if (fldarr.length>25) { /* if #22 and #23 are dates in dd-MMM-yyyy format and we have 26 fields or more, the last 2 or more can be joined (wiedervorlagegrund has ; in it) */ let rex = /[0-9]{2}\-[A-Z]{3}\-[0-9]{4}/; if (rex.test(fldarr[22])) { rp.push("fld#22 is a date"); if (rex.test(fldarr[23])) { rp.push("fld#23 is a date"); let rep = ""; let repi = 0; let rept = fldarr.length; while (fldarr.length>24) { if (repi>rept) { let errinfo = "LOADMVBRFAUSW_REPCSV.FLDARR.LENGTH="+fldarr.length+".REPI="+repi+".REPT="+rept+".REPCOLL=\""+rep+"\".REPT_EXCEEDED.BAIL_OUT"; logger.error(errinfo); if (repres) repres.info = rp.join("\n") + "\n" + errinfo; return linetext; } if (rep!="") rep += "%3B"; rep += fldarr[24]; fldarr.splice(24,1); repi++; } fldarr.push(rep); linetext = fldarr.join(";"); rp.push("possibly WIEDERVORLAGEGRUND had one or more \";\" in its data, last "+repi+" fields joined"); if (repres) repres.info = rp.join("\n"); return linetext; } else { rp.push("fld#23 is no date"); } } else { rp.push("fld#22 is no date"); /* if #22 is no date, there are chances that #17/#18 are in reality one field */ rex = /^[A-Z]{3}$/; if ((fldarr[18]=='') || (rex.test(fldarr[18]))) { rp.push("fld#18 is valid RUECKFRAGESTATUSCODE \""+fldarr[18]+"\", probably no error in 17/18"); } else { rp.push("fld#18 is invalid as RUECKFRAGESTATUSCODE \""+fldarr[18]+"\", try merging #17+#18"); let rep = fldarr[17] + "%3B" + fldarr[18]; fldarr.splice(18,1); fldarr[17] = rep; linetext = fldarr.join(";"); if (repres) repres.info = rp.join("\n"); return linetext; } } } if (fldarr.length>25) { /* try to correct "Referenzwert" */ let rex = /(Referenzwert\s.+\sgespeichert)/; let m = rex.exec(linetext); if (m) { let rep = m[0].replace(/\;/g,"%3B"); linetext = linetext.substring(0,m.index)+rep+linetext.substring(m.index+m[0].length); fldarr = linetext.split(";"); rp.push("Referenzwert-repair result: "+aux.objTxt(fldarr)); if (repres) repres.info = rp.join("\n"); return linetext; } else { rp.push("Referenzwert pattern not found, no repair here"); } /* check if HERSTELLER (#10) possibly has ; in the data, this may be the case if RUECKFRAGETYPCODE (#12) is not like "CC9999" */ rex = /[A-Z]{2}[0-9]{4}/; m = rex.exec(fldarr[12]); if (!m) { rp.push("RUECKFRAGETYPCODE("+fldarr[12]+") is not well formed, try concatenate fields 10+11"); let herst = fldarr[10]+"%3B"+fldarr[11]; fldarr.splice(11,1); fldarr[10] = herst; linetext = fldarr.join(";"); return linetext; } else { rp.push("HERSTELLER-repair not done, RUECKFRAGETYPCODE("+fldarr[12]+") is in right place"); } } if (repres) repres.info = rp.join("\n"); return linetext; }.bind(this); @pre_query this.fname = "LOADMVBRFAUSW"; ~query.tsteps # # <>: # if (this.init_done) goto record_proper # # <>: initialization: get MFILE info # qexpression proc: { if (!this.init_done) { this.progstat.current_mfileid = this.mfileinfo.mfileid; this.startmillis = Date.now(); this.resultAttributes = { startmillis: this.startmillis }; this.FLIST = this.query.FLIST; this.tfnames = this.FLIST.split(","); this.FVLIST = "::"+this.tfnames.join("::,::")+"::"; if (!this.mfileinfo) { this.errcoll.collect(null,"no MFILEINFO"); break proc; } if (!this.mfileinfo.mfileid) { this.errcoll.collect(null,"MFILEINFO has no MFILEID",this.mfileinfo); } this.load_mfileid = this.mfileinfo.mfileid; if (!this.mfileinfo.mfilename) { this.errcoll.collect(null,"MFILEINFO has no MFILENAME attribute",this.mfileinfo); break proc; } this.load_mfileattribs = this.mfileinfo.mfileattribs; this.load_mfileattributes = aux.txtObjDecode(this.load_mfileattribs) || {}; if (this.load_mfileattributes.loadstatus && this.load_mfileattributes.loadstatus=="loaded") { this.errcoll.collect(null,"LOAD_MFILE "+this.load_mfileid+" is already loaded",{ mfilinfo: this.mfileinfo, load_mfileattributes: this.load_mfileattributes}); break proc; } this.mfnparts = this.mfileinfo.mfilename.split("."); if (this.mfnparts.length<2) { this.errcoll.collect(null,"MFILENAME does not have at least 2 parts, separated by '.'",this.mfileinfo); break proc; } if (this.mfnparts[1]!="csv") { this.errcoll.collect(null,"MFILENAME does not signify CSV file",this.mfileinfo); break proc; } this.mfnfields = this.mfnparts[0].split("_"); if (this.mfnfields.length!=4) { this.errcoll.collect(null,"MFILENAME does not have 4 fields, separated by '_'",this.mfileinfo); break proc; } if (this.mfnfields[1]!="Auswertung") { this.errcoll.collect(null,"MFILENAME field 2 is not 'Auswertung'",this.mfileinfo); break proc; } if (this.mfnfields[2]!="MeldungenClearingfaelle") { this.errcoll.collect(null,"MFILENAME field 3 is not 'MeldungenClearingfaelle'",this.mfileinfo); break proc; } this.traeger = this.mfnfields[0]; this.fts = this.mfnfields[3]; this.protocol = new Array(); this.ppush([this.phead("started"),"MFILEID_TO_LOAD="+this.mfileid,"",""]); this.recsprocessed = 0; this.LOADSTARTED = aux.nowstring(); } this.progstat.TRAEGER = this.traeger; this.progstat.FTIMESTAMP = this.fts; this.sumbuffer = { meldungen: {}, clearingfaelle: {}, exoffo: {}, cflswid: {} } } # # <>: check if table there (if not, the SQL step will fail and everything rolled back) # sql describe CCMVBRFAUSW # # <>: init done # qexpression this.init_done = true; # # <>: check if a SOID is present # record_proper: pre_qexpression this.cdata.LSWID = ""; if (this.cdata.SOID) goto rec_has_soid # # <>: record has no SOID, try other means for determining LSWID # pre_qexpression if (this.altLSWIDmethods) goto have_altLSWIDmethods # # <>: load the alternate LSWID-determining methods # sql select LSWID,LSWHID,LSWATTRIBS from SVCLRWLSW result_varname lswres post_qexpression proc: { let li, lr, as; this.knownLSWs = {}; this.altLSWIDmethods = new Array(); for (li=0; li>: try the alternate methods one by one until success or methods exhausted # have_altLSWIDmethods: qexpression proc: { let cdone = false; let ami = 0; this.foundLSWID = null; let m; while (!cdone) { if (ami>=this.altLSWIDmethods.length) { break; } if (!this.altLSWIDmethods[ami].ident_qexpression) break; try { m = eval(this.altLSWIDmethods[ami].ident_qexpression); } catch(e) { this.errcoll.collect(e,"Error evaluating alternate LSWID-determination ident_expression for LSWID["+ami+"]=\""+this.altLSWIDmethods[ami].lswid+"\": \""+ this.altLSWIDmethods[ami].ident_qexpression+"\"",this.altLSWIDmethods); break proc; } if (m) { this.cdata.LSWID = this.altLSWIDmethods[ami].lswid; this.needLSWIDforSOID = null; cdone = true; break; } ami++; } } # # <>: regardless of success, the record is now inserted as it is # goto do_insert # # <>: record has a SOID, get LSWID for the current record # rec_has_soid: sql select LSWID from SVCLRWSOID where SOID=::SOID:: named_params SOID result_varname lswres post_qexpression if (this.lswres.getRowCount()>0) { this.cdata.LSWID=this.lswres.getCellValue(0,0); this.needLSWIDforSOID = null; } else { this.needLSWIDforSOID = getcvar(this,"SOID"); } # # <>: skip creation of new LSW if we found one # if (!this.needLSWIDforSOID) goto do_insert # # <>: don't have LSWID, check if we have an LSW with a soidrex, but get SOIDrexs to memory first # if (this.SOIDrexs) goto have_soidrexs # # <>: get the list of all SOIDrexs # sql select LSWID,LSWHID,LSWATTRIBS from SVCLRWLSW result_varname lswres post_qexpression proc: { let li, lr, as; this.knownLSWs = {}; this.SOIDrexs = new Array(); for (li=0; li>: check SOID against SOIDrexs # have_soidrexs: qexpression let cdone = false; let rexi = 0; this.foundLSWID = null; let m; while (!cdone) { if (rexi>=this.SOIDrexs.length) { break; } if (!this.SOIDrexs[rexi].rex) break; m = this.SOIDrexs[rexi].rex.exec(this.cdata.SOID); if (m) { this.cdata.LSWID = this.SOIDrexs[rexi].lswid; cdone = true; break; } rexi++; } # # <>: if we've got an LSW, we can skip creation of a new one # if (this.cdata.LSWID) goto create_SOID # # <>: create a new LSW # pre_qexpression this.cdata.LSWID = "LSW_"+aux.syncUniqueNowID(); this.cdata.LSWHID = "LSWH_"+aux.syncUniqueNowID(); this.cdata.LSWATTRIBS = "{}"; sql INSERT INTO SVCLRWLSW(LSWID,LSWHID,LSWATTRIBS) values(::LSWID::,::LSWHID::,::LSWATTRIBS::) named_params LSWID,LSWHID,LSWATTRIBS # # <>: create the new SOID-entry to map the current SOID to the found (or created) LSW # create_SOID: sql insert into SVCLRWSOID(SOID,LSWID) values(::SOID::,::LSWID::) named_params SOID,LSWID # # <>: insert one record, update statistic counters for Meldungen, Clearingfälle, exOffo-mBGM # do_insert: pre_qexpression proc: { let drex = /^[0-9]{2}\-[A-Z]{3}\-[0-9]{4}$/; if (!drex.test(this.cdata.DOKUMENT_DAT)) { this.errcoll.collect(null,"Field #4,DOKUMENT_DAT(\""+this.cdata.DOKUMENT_DAT+"\") of record #"+this.recnum+" of managed file \""+this.mfileid+"\" is not well formed like tt-MMM-JJJJ",this.cdata); break proc; } this.cdata.TRAEGER = this.traeger; this.cdata.FTIMESTAMP = this.fts; this.cdata.RECORDNUM = this.recnum+1; let monnum = Math.floor(this.monatlist.indexOf(this.cdata.DOKUMENT_DAT.substring(3,7))/4); if (monnum<0) { this.errcoll.collect(null,"Field #4,DOKUMENT_DAT(\""+this.cdata.DOKUMENT_DAT+"\") of record #"+this.recnum+" of managed file \""+this.mfileid+"\" has illegal month code of \""+this.cdata.DOKUMENT_DAT.substring(3,7)+"\"",this.cdata); break proc; } this.cdata.DOKDAT_DATNUM = this.cdata.DOKUMENT_DAT.substring(7,11)+ aux.DEC(monnum+1,2)+ this.cdata.DOKUMENT_DAT.substring(0,2); let fnam; let fval; let fsiz; let chkerr = ""; for (let i=0; ifsiz) { chkerr += "field "+fnam+" must be no more than "+fsiz+", but has "+fval.length+": \""+fval+"\""; } } } if (chkerr) { this.errcoll.collect(null,"Error checking data before inserting into CCMVBRFAUSW: "+chkerr,this.cdata); break proc; } } sql insert into CCMVBRFAUSW(@@FLIST@@) VALUES(@@FVLIST@@) named_params @@FLIST@@ post_qexpression if (!this.sumbuffer.meldungen[this.cdata.TRAEGER]) this.sumbuffer.meldungen[this.cdata.TRAEGER] = {}; if (!this.sumbuffer.meldungen[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM]) { this.sumbuffer.meldungen[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM] = { ANZAHL_MELDUNGEN: 0, DOKUMENT_DAT: this.cdata.DOKUMENT_DAT }; } this.sumbuffer.meldungen[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM].ANZAHL_MELDUNGEN++; if (this.cdata.RUECKFRAGETYPCODE!="") { /* at least one Rückfrage, register DOKUMENT_ID as Clearingfall */ if (!this.sumbuffer.clearingfaelle[this.cdata.TRAEGER]) this.sumbuffer.clearingfaelle[this.cdata.TRAEGER] = {}; if (!this.sumbuffer.clearingfaelle[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM]) { this.sumbuffer.clearingfaelle[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM] = { __ANZAHL_DOKUMENTE_MIT_CF: 0, __DOKUMENT_DAT: this.cdata.DOKUMENT_DAT }; } if (!this.sumbuffer.clearingfaelle[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.DOKUMENT_ID]) { this.sumbuffer.clearingfaelle[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.DOKUMENT_ID] = { ANZAHL_RF: 0}; this.sumbuffer.clearingfaelle[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM].__ANZAHL_DOKUMENTE_MIT_CF++; } this.sumbuffer.clearingfaelle[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.DOKUMENT_ID].ANZAHL_RF++; if (!this.sumbuffer["svtrf"]) this.sumbuffer.svtrf = {}; if (!this.sumbuffer.svtrf[this.cdata.TRAEGER]) this.sumbuffer.svtrf[this.cdata.TRAEGER] = {}; if (!this.sumbuffer.svtrf[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM]) this.sumbuffer.svtrf[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM] = {} if (!this.sumbuffer.svtrf[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.RUECKFRAGETYPCODE]) this.sumbuffer.svtrf[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.RUECKFRAGETYPCODE] = { ANZAHL_RF: 0 }; this.sumbuffer.svtrf[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.RUECKFRAGETYPCODE].ANZAHL_RF++ if (!this.sumbuffer["lswrf"]) this.sumbuffer.lswrf = {}; if (!this.sumbuffer.lswrf[this.cdata.LSWID]) this.sumbuffer.lswrf[this.cdata.LSWID] = {}; if (!this.sumbuffer.lswrf[this.cdata.LSWID][this.cdata.DOKDAT_DATNUM]) this.sumbuffer.lswrf[this.cdata.LSWID][this.cdata.DOKDAT_DATNUM] = {}; if (!this.sumbuffer.lswrf[this.cdata.LSWID][this.cdata.DOKDAT_DATNUM][this.cdata.RUECKFRAGETYPCODE]) this.sumbuffer.lswrf[this.cdata.LSWID][this.cdata.DOKDAT_DATNUM][this.cdata.RUECKFRAGETYPCODE] = { ANZAHL_RF: 0 }; this.sumbuffer.lswrf[this.cdata.LSWID][this.cdata.DOKDAT_DATNUM][this.cdata.RUECKFRAGETYPCODE].ANZAHL_RF++; } if (this.cdata.RUECKFRAGETYPCODE=="BW1916") { /* exOffo-mBGM, register this DOKUMENT_ID as exOffo */ /* logger.debug(this.phead("EXOFFORECORD")+".TRAEGER="+this.cdata.TRAEGER+".FTIMESTAMP="+this.cdata.FTIMESTAMP+".RECORDNUM="+this.cdata.RECORDNUM+".RUECKFRAGETYPCODE="+this.cdata.RUECKFRAGETYPCODE); */ if (!this.sumbuffer.exoffo[this.cdata.TRAEGER]) this.sumbuffer.exoffo[this.cdata.TRAEGER] = {}; if (!this.sumbuffer.exoffo[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM]) { this.sumbuffer.exoffo[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM] = { __ANZAHL_DOKUMENTE_MIT_EXOFFO: 0, __DOKUMENT_DAT: this.cdata.DOKUMENT_DAT }; } if (!this.sumbuffer.exoffo[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.DOKUMENT_ID]) { this.sumbuffer.exoffo[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.DOKUMENT_ID] = { ANZAHL_EXOFFO: 0}; this.sumbuffer.exoffo[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM].__ANZAHL_DOKUMENTE_MIT_EXOFFO++; } this.sumbuffer.exoffo[this.cdata.TRAEGER][this.cdata.DOKDAT_DATNUM][this.cdata.DOKUMENT_ID].ANZAHL_EXOFFO++; } this.recsprocessed++; # # <>: check if this was the last record # if (!this.is_last_record) goto record_done # # ------------------------------------------------------------------------------ # # <>: all records processed, update Summary tables # gosub updateSummaries # # <>: mark MFILE as loaded # this is done by setting the managed file attribute "loadstate" to the value "loaded" # pre_qexpression this.LOADFINISHED = aux.nowstring(); this.load_mfileattributes.loadstate = "loaded"; delete this.load_mfileattributes.to_reconvert; delete this.load_mfileattributes.unloadtime; this.load_mfileattribs = JSON.stringify(this.load_mfileattributes); sql update CCMFILES SET MFILEATTRIBS=::load_mfileattribs:: WHERE MFILEID=::mfileid:: named_params load_mfileattribs,mfileid post_qexpression this.ppush([this.phead("filecomplete"),"MFILEID_LOADED="+this.mfileid,"RECORDS_PROCESSED="+this.recsprocessed,""]); # # <>: register file in SVCLMAFILES # sql insert into SVCLMAFILES(TRAEGER,FTIMESTAMP,MFILEID,LOADSTARTED,LOADFINISHED) values(::TRAEGER::,::FTIMESTAMP::,::mfileid::,::LOADSTARTED::,::LOADFINISHED::) named_params TRAEGER,FTIMESTAMP,mfileid,LOADSTARTED,LOADFINISHED post_qexpression this.endmillis = Date.now(); this.elapsedmillis = this.endmillis - this.startmillis; this.elapsedseconds = this.elapsedmillis/1000; this.recspersecond = this.recsprocessed/this.elapsedseconds; this.traeger = this.cdata.TRAEGER; this.ftimestamp = this.cdata.FTIMESTAMP; this.copyResultAttributes("traeger,ftimestamp,startmillis,elapsedmillis,elapsedseconds,recsprocessed,recspersecond"); this.ppush([this.phead("finished,MAFILES_REGISTERED"), "TOTAL_SECONDS_ELAPSED="+this.elapsedseconds, "MFILEID="+this.mfileid, "TRAEGER="+this.cdata.TRAEGER+",FTIMESTAMP="+this.cdata.FTIMESTAMP+",LOADSTARTED="+this.LOADSTARTED+",LOADFINISHED="+this.LOADFINISHED]); # # ------------------------------------------------------------------------------ # # <>: end of record processing # record_done: end # # ============================================================================== # ============================================================================== # # Subroutines # # ============================================================================== # ============================================================================== # # <>: SUBROUTINE: updateSummaries # # save summary counters for this dataset into a temporary file # updateSummaries: qexpression let tfn = aux.makeUniqueTempFileName() + "_" + this.traeger + "_" + this.fts + "_SUMDUMP.JSON"; fs.writeFileSync(tfn,JSON.stringify(this.sumbuffer)); this.ppush([this.phead("SUMBUFFDUMPED"),"DUMPFILENAME="+tfn,"",""]); # # <>: update MELDUNGEN # gosub updateMeldungenSummary # # <>: # gosub updateCFsummary # # <>: # gosub updateExOffoSummary # # <>: persist LSWRF counters # gosub updateLSWRFsummary # # <>: persist SVTRF counters # gosub updateSVTRFsummary # # <>: end of updating summaries # return # # ============================================================================== # UPDATE database counters for MELDUNGEN # ------------------------------------------------------------------------------ # # SUBROUTINE: updateMeldungenSummary # # <>: # updateMeldungenSummary: pre_qexpression this.local.meldungen = this.sumbuffer.meldungen; /* logger.debug("updateMeldungenSummary.BEGIN: "+aux.objTxt({ local: aux.deepCopy(this.local) },99,999999)); */ gosub objectDoForEach params.obj ~this.local.meldungen params.sub updateMeldungenTraeger then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateMeldungenTraeger # INPUT: p0 - TRAEGER-ID of traeger whichs MELDUNGEN-counter(s) shall be persisted # p1 - sub-structure for TRAEGER-ID # ( p0 and p1 are passed automatically by objectDoForEach ) # # <>: iterate over all DATNUM-counter-objects inside TRAEGER-record # updateMeldungenTraeger: pre_qexpression this.local.traeger = this.local.params.p0; this.local.traegerrec = this.local.params.p1; /* logger.debug("updateMeldungenTraeger.BEGIN: "+aux.objTxt({ traeger: this.local.traeger, local: aux.deepCopy(this.local) },99,999999)); */ gosub objectDoForEach params.obj ~this.local.traegerrec params.sub updateMeldungenTraegerDatnum params.par2 ~this.local.traeger then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateMeldungenTraegerDatnum # INPUT: p0 - DATNUM for the day inside TRAEGER to persist counter for # p1 - Record for DATNUM inside the TRAEGER # p2 - TRAEGER-ID # DESCRIPTION: update counter of MELDUNGEN for TRAEGER/DATNUM # SQL in MySQL: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html # addendum 20220103: created unique index: # create unique index SVCLMVBRFASVTTAGUNIQUE on SVCLMVBRFASVTTAG(TRAEGER,MELDUNGS_DATUM) # # <>: persist all counters of DATNUM-record into table SVCLMVBRFASVTTAG # updateMeldungenTraegerDatnum: pre_qexpression this.local.datnum = this.local.params.p0; this.local.datrec = this.local.params.p1; this.local.traeger = this.local.params.p2; this.local.count = this.local.datrec.ANZAHL_MELDUNGEN; this.local.dokdat = this.local.datrec.DOKUMENT_DAT; /* logger.debug("updateMeldungenTraegerDatnum.BEGIN: "+aux.objTxt({ local: aux.deepCopy(this.local) },99,999999)); */ sql insert into SVCLMVBRFASVTTAG(TRAEGER,DOKUMENT_DAT,MELDUNGS_DATUM,ANZAHL_MELDUNGEN) values(::traeger::,::dokdat::,::datnum::,::count::) on duplicate key update ANZAHL_MELDUNGEN=ANZAHL_MELDUNGEN+::count:: named_params traeger,datnum,dokdat,count then_return # # ============================================================================== # update CLEARINGFALL counters in database # ------------------------------------------------------------------------------ # # SUBROUTINE: updateCFsummary # # <>: # updateCFsummary: pre_qexpression this.local.clearingfaelle = this.sumbuffer.clearingfaelle gosub objectDoForEach params.obj ~this.local.clearingfaelle params.sub updateCFTraeger then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateCFTraeger # INPUT: p0 - TRAEGER-ID # p1 - TRAEGER-record # # <>: # updateCFTraeger: pre_qexpression this.local.traeger = this.local.params.p0; this.local.traegerrec = this.local.params.p1; gosub objectDoForEach params.obj ~this.local.traegerrec params.sub updateCFTraegerDatnum params.par2 ~this.local.traeger then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateCFTraegerDatnum # INPUT: p0 - DATNUM # p1 - TRAEGER/DATNUM-record # p2 - TRAEGER-ID # DESCRIPTION: update clearfälle-counter for TRAEGER/DATNUM in table # SQL in MySQL: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html # preparations: # created unique index svclmvbclfsvttagunique on svclmvbclfsvttag: # create unique index svclmvbclfsvttagunique on svclmvbclfsvttag(TRAEGER,MELDUNGS_DATUM) # consolidated all rows in svclmvbclfsvttag for one TRAEGER/DATNUM to one row # # <>: # updateCFTraegerDatnum: pre_qexpression this.local.datnum = this.local.params.p0; this.local.tdrec = this.local.params.p1; this.local.traeger = this.local.params.p2; this.local.DOKUMENT_DAT = this.local.tdrec.__DOKUMENT_DAT; this.local.count = this.local.tdrec.__ANZAHL_DOKUMENTE_MIT_CF; sql insert into svclmvbclfsvttag(TRAEGER,DOKUMENT_DAT,MELDUNGS_DATUM,ANZAHL_MELDUNGEN_MIT_CLEARINGFAELLEN) values(::traeger::,::DOKUMENT_DAT::,::datnum::,::count::) on duplicate key update ANZAHL_MELDUNGEN_MIT_CLEARINGFAELLEN=ANZAHL_MELDUNGEN_MIT_CLEARINGFAELLEN+::count:: named_params traeger,DOKUMENT_DAT,datnum,count then_return # # ============================================================================== # update EXOFFO counters in database # ------------------------------------------------------------------------------ # # SUBROUTINE: updateExOffoSummary # DESCRIPTION: update summary counters for exOffo-mBGMs # by the numbers accumulated in this dataset (this.sumboffer.exoffo) # - one entry for each TRAEGER # -- one entry for each DATNUM # --- one entry for each # # <>: # updateExOffoSummary: pre_qexpression this.local.exoffo = this.sumbuffer.exoffo; gosub objectDoForEach params.obj ~this.local.exoffo params.sub updateExOffoTraeger then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateExOffoTraeger # DESCRIPTION: update persistent exOff-counters for one TRAEGER # in preparation for reports, ensure to have one sub-record for each calender-day # # <>: get a list of all valid days and fill up exOffo sumbuffer for this TRAEGER # updateExOffoTraeger: pre_qexpression this.local.traeger = this.local.params.p0 this.local.traegerrec = this.local.params.p1 sql select JAHR,MONAT,TAG from CCVCALD where JAHR>=2019 result_varname cres post_qexpression let datnum, datobj; for (let ti=0; ti>: iterate over all days for this TRAEGER # gosub objectDoForEach params.obj ~this.local.traegerrec params.sub updateExOffoTraegerDatnum params.par2 ~this.local.traeger then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateExOffoTraegerDatnum # DESCRIPTION: update persistent exOffo counter for this TRAEGER/DATNUM in table # SQL in MySQL: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html # preparations: # created unique index: # create unique index svclexoffosvttagunique on svclexoffosvttag(TRAEGER,DOKUMENT_DAT) # # <>: insert new record for TRAEGER/DATNUM with new counter # if record already present, update present record # updateExOffoTraegerDatnum: pre_qexpression proc: { this.local.traeger = this.local.params.p2; this.local.datnum = this.local.params.p0; this.local.tdrec = this.local.params.p1; this.local.dokdat = this.local.tdrec.__DOKUMENT_DAT if (!this.local.tdrec.hasOwnProperty("__ANZAHL_DOKUMENTE_MIT_EXOFFO")) { this.errcoll.collect(null,"summary buffer exoffo branch for TRAEGER "+this.local.traeger+", DATNUM \""+this.local.datnum+"\" has no __ANZAHL_DOKUMENTE_MIT_EXOFFO", { params: this.local.params, branch: this.sumbuffer.exoffo[this.local.traeger][this.local.datnum] } ); break proc; } this.local.count = this.local.tdrec.__ANZAHL_DOKUMENTE_MIT_EXOFFO } sql insert into svclexoffosvttag(TRAEGER,DOKUMENT_DAT,MELDUNGS_DATUM,ANZAHL_EXOFFO_MBGM) values(::traeger::,::dokdat::,::datnum::,::count::) on duplicate key update ANZAHL_EXOFFO_MBGM=ANZAHL_EXOFFO_MBGM+::count:: named_params traeger,dokdat,datnum,count then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateLSWRFsummary # DESCRIPTION: persist counters from this.sumbuffer.lswrf to table SVCLLSWIDRFDATCOUNT # the original creation of the table was done by: # ---------------------------------------------- # create table SVCLLSWIDRFDATCOUNT as # select LSWID,DOKDAT_DATNUM as DOKDATNUM, RUECKFRAGETYPCODE, COUNT(RUECKFRAGETYPCODE) as RFCOUNT # from CCMVBRFAUSW # where RUECKFRAGETYPCODE is not null and RUECKFRAGETYPCODE!='' and EXTERN_JN='J' # group by LSWID,DOKDAT_DATNUM,RUECKFRAGETYPCODE # ---------------------------------------------- # unique index added: # create unique index svcllswidrfdatcountunique on SVCLLSWIDRFDATCOUNT(LSWID,DOKDATNUM,RUECKFRAGETYPCODE) # # <>: first, check if table SVCLLSWIDRFDATCOUNT is here at all # updateLSWRFsummary: sql_mysql select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='svcllswidrfdatcount' result_varname lrfcires # # <>: # if (this.lrfcires.getRowCount()!=0) goto have_lrfctable # # <>: create SVCLLSWIDRFDATCOUNT table # sql_mysql create table SVCLLSWIDRFDATCOUNT (LSWID varchar(32), DOKDATNUM varchar(8), RUECKFRAGETYPCODE varchar(10), RFCOUNT DECIMAL(19)) # # <>: iterate over all LSWID/DATNUM/RFTYP counters # have_lrfctable: pre_qexpression this.local.lswrf = this.sumbuffer.lswrf; gosub objectDoForEach params.obj ~this.local.lswrf params.sub updateLSWRFlswid then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateLSWRFlswid (procmeldlswid) # INPUT: p0 - LSW-ID of software which counters shall be persisted # p1 - sub-structure (1 attribute for each day) of counter data known about this LSWID # DESCRIPTION: persist counters for LSWID # # <>: invoke iteration about all days in the range stored in # updateLSWRFlswid: pre_qexpression this.local.lswid = this.local.params.p0; this.local.lswrec = this.local.params.p1; gosub objectDoForEach params.obj ~this.local.lswrec params.sub updateLSWRFlswidtag params.par2 ~this.local.lswid then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateLSWRFlswidtag # DESCRIPTION: persist RFTYP counters for LSWID on day # # <>: # updateLSWRFlswidtag: pre_qexpression this.local.datnum = this.local.params.p0; this.local.tagrec = this.local.params.p1; this.local.lswid = this.local.params.p2; gosub objectDoForEach params.sub updateLSWRFlswidtagrf params.obj ~this.local.tagrec params.par2 ~this.local.lswid params.par3 ~this.local.datnum then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateLSWRFlswidtagrf # INPUT: p0 - RFTYP-code # p1 - counter record object # p2 - LSWID - ID of LSW for which this counter is valid # p3 - DATNUM - day for which this counter is valid # # <>: create counter in table SVCLLSWIDRFDATCOUNT, update if there already a persistant counter for LSWID/DATNUM/RFTYP # updateLSWRFlswidtagrf: pre_qexpression proc: { this.local.rftyp = this.local.params.p0; this.local.counter = this.local.params.p1; this.local.lswid = this.local.params.p2; this.local.datnum = this.local.params.p3; if (!this.local.counter.hasOwnProperty("ANZAHL_RF")) { this.errcoll.collect(null,"LSWID="+this.local.lswid+"/DATNUM="+this.local.datnum+"/RFTYP="+this.local.rftyp+" counter has no ANZAHL_RF",this.local); break proc; } this.local.rftcount = this.local.counter.ANZAHL_RF; } sql insert into SVCLLSWIDRFDATCOUNT(LSWID,DOKDATNUM,RUECKFRAGETYPCODE,RFCOUNT) values(::lswid::,::datnum::,::rftyp::,::rftcount::) on duplicate key update RFCOUNT=RFCOUNT+::rftcount:: named_params lswid,datnum,rftyp,rftcount then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateSVTRFsummary # DESCRIPTION: persist counters from this.sumbuffer.svtrf to table SVCLSVTRFDATCOUNT # the original creation of the table was done by: # ---------------------------------------------- # create table SVCLSVTRFDATCOUNT as # select TRAEGER,DOKDAT_DATNUM as DOKDATNUM, RUECKFRAGETYPCODE, COUNT(RUECKFRAGETYPCODE) as RFCOUNT # from CCMVBRFAUSW # where RUECKFRAGETYPCODE is not null and RUECKFRAGETYPCODE!='' and EXTERN_JN='J' # group by TRAEGER,DOKDAT_DATNUM,RUECKFRAGETYPCODE # ---------------------------------------------- # unique index added: # create unique index svcllswidrfdatcountunique on SVCLLSWIDRFDATCOUNT(TRAEGER,DOKDATNUM,RUECKFRAGETYPCODE) # # <>: first, check if table SVCLSVTRFDATCOUNT is here at all # updateSVTRFsummary: sql_mysql select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='svclsvtrfdatcount' result_varname srfcires # # <>: # if (this.srfcires.getRowCount()!=0) goto have_srfctable # # <>: create SVCLSVTRFDATCOUNT table # sql_mysql create table SVCLSVTRFDATCOUNT (TRAEGER varchar(32), DOKDATNUM varchar(8), RUECKFRAGETYPCODE varchar(10), RFCOUNT DECIMAL(19)) # # <>: iterate over all SVT/DATNUM/RFTYP counters # have_srfctable: pre_qexpression this.local.svtrf = this.sumbuffer.svtrf; gosub objectDoForEach params.obj ~this.local.svtrf params.sub updateSVTRFsvt then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateSVTRFsvt # INPUT: p0 - SVT ID of SVT whichs counters shall be persisted # p1 - sub-structure (1 attribute for each day) of counter data known about this SVT # DESCRIPTION: persist counters for SVT # # <>: invoke iteration about all days in the range stored in # updateSVTRFsvt: pre_qexpression this.local.svt = this.local.params.p0; this.local.svtrec = this.local.params.p1; gosub objectDoForEach params.obj ~this.local.svtrec params.sub updateSVTRFsvttag params.par2 ~this.local.svt then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateSVTRFsvttag # DESCRIPTION: persist RFTYP counters for SVT on day # # <>: # updateSVTRFsvttag: pre_qexpression this.local.datnum = this.local.params.p0; this.local.tagrec = this.local.params.p1; this.local.svt = this.local.params.p2; gosub objectDoForEach params.sub updateSVTRFsvttagrf params.obj ~this.local.tagrec params.par2 ~this.local.svt params.par3 ~this.local.datnum then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: updateSVTRFsvttagrf # INPUT: p0 - RFTYP-code # p1 - counter record object # p2 - SVT - ID of SVT for which this counter is valid # p3 - DATNUM - day for which this counter is valid # # <>: create counter in table SVCLSVTRFDATCOUNT, update if there already a persistant counter for SVT/DATNUM/RFTYP # updateSVTRFsvttagrf: pre_qexpression proc: { this.local.rftyp = this.local.params.p0; this.local.counter = this.local.params.p1; this.local.svt = this.local.params.p2; this.local.datnum = this.local.params.p3; if (!this.local.counter.hasOwnProperty("ANZAHL_RF")) { this.errcoll.collect(null,"SVT="+this.local.svt+"/DATNUM="+this.local.datnum+"/RFTYP="+this.local.rftyp+" counter has no ANZAHL_RF",this.local); break proc; } this.local.rftcount = this.local.counter.ANZAHL_RF; } sql insert into SVCLSVTRFDATCOUNT(TRAEGER,DOKDATNUM,RUECKFRAGETYPCODE,RFCOUNT) values(::svt::,::datnum::,::rftyp::,::rftcount::) on duplicate key update RFCOUNT=RFCOUNT+::rftcount:: named_params svt,datnum,rftyp,rftcount then_return # # ------------------------------------------------------------------------------ # # SUBROUTINE: objectDoForEach # INPUT: obj - Object to iterate over - each attribute of # sub - name of subroutine to be invoked with each attribute of # must have 3 parameters: "p0","p1", "p2", "p3" # p0 receives the name of the attribute # p1 receives the attribute # p2 receives the value of # p3 receives the value of # par2 - value of the 2nd parameter # par3 - value of the 3rd parameter # DESCRIPTION: iterates over all attributes of and invokes for each attribute # # <>: get parameters, setup iteration # objectDoForEach: qexpression proc: { /* this.debug = true; */ if (!this.local.params.obj) { this.errcoll.collect(null,"object has no content in objectDoForEach",{ local: aux.deepCopy(this.local), substack: aux.deepCopy(this.substack) }); break proc; } this.local.obj = this.local.params.obj; this.local.sub = this.local.params.sub; this.local.par2 = this.local.params.par2; this.local.par3 = this.local.params.par3; this.local.attribindex = 0; this.local.attribs = Object.keys(this.local.obj); if (this.debug) logger.debug("objectDoForEach.sub="+this.local.sub+".BEGIN.data: "+aux.objTxt(this.local,999,999999999)); } # # <>: check if more attributes to be processed # nextObjectDoForEach: if (this.local.attribindex>=this.local.attribs.length) return # # <>: invoke the processing subroutine # pre_qexpression this.local.attribname = this.local.attribs[this.local.attribindex]; this.local.attrib = this.local.obj[this.local.attribname]; if (this.debug) logger.debug("objectDoForEach.sub="+this.local.sub+".LOOPHEAD.index="+this.local.attribindex+".attribname=\""+this.local.attribname+"\""); gosub @@sub@@ params.p0 ~this.local.attribname params.p1 ~this.local.attrib params.p2 ~this.local.par2 params.p3 ~this.local.par3 # # <>: bump index, rinse and repeat # pre_qexpression this.local.attribindex++; if (this.debug) logger.debug("objectDoForEach.sub="+this.local.sub+".LOOPFOOT.index="+this.local.attribindex+".attribname=\""+this.local.attribname+"\""); goto nextObjectDoForEach # # ============================================================================== # ============================================================================== # END OF dqueryfile_LOADMVBRFAUSW.txt # ============================================================================== # ============================================================================== #