From 8ab4168e80af275917be60c861036b3226860c30 Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Thu, 17 May 2018 15:21:23 +0200 Subject: [PATCH] correction of export CSV (check-service) --- .../content/modules/accounting/js/accounting.js | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/app_v3/chrome/content/modules/accounting/js/accounting.js b/app_v3/chrome/content/modules/accounting/js/accounting.js index 054d46d..da4cef0 100644 --- a/app_v3/chrome/content/modules/accounting/js/accounting.js +++ b/app_v3/chrome/content/modules/accounting/js/accounting.js @@ -155,7 +155,7 @@ function load_accounting_table(){ "LEFT JOIN ( "+ "SELECT childuuid,calweek,case when strftime('%w',date(calyear ||'-01-01')) in ('1','2','3','4') then (cast(calweek as int)*7)-7 else (cast(calweek as int)*7) end as caldays ,"+ "sumlunchcalweek, coalesce(sum(monduration + tueduration + wedduration + thuduration + friduration),0.0) as sumdurationcalweek " + -"FROM ( SELECT childuuid, daydate,calyear, case when strftime('%w',date(calyear ||'-01-01')) in ('1','2','3','4') then case when calweek < '10' then '0' else '' end || cast(calweek as int)+1 else calweek end as calweek," + +"FROM ( SELECT childuuid, daydate,calyear, case when strftime('%w',date(calyear ||'-01-01')) in ('1','2','3','4') then case when calweek < '10' then '0' else '' end || cast(calweek as int) else calweek end as calweek," + "max(monlunch) + max(tuelunch) + max(wedlunch) + max(thulunch) + max(frilunch) AS sumlunchcalweek, " + "MAX(monduration) + MAX(monduration2) AS monduration, " + "MAX(tueduration) + MAX(tueduration2) AS tueduration, " + @@ -544,7 +544,7 @@ function generatecsv(){ var val = $("#accmonth").val(); var cdate = new Date(); var accsql = "select planned.childuuid,planned.checkservicenumber,planned.pcalweek,replace(printf(\"%.2f\",COALESCE(planned.weekplantime,0.00)),'.',',') as weekplantime,d1.calweek,replace(printf(\"%.2f\",COALESCE(d1.durationweek,0.00)),'.',',') as durationweek,COALESCE(d1.lunchweek,0) as lunchweek from ( " + - "select childuuid,pcalweek, weekplantime,checkservicenumber from ("+ + "select checkservicenumber || '-' || pcalweek as idjoin,childuuid,pcalweek, weekplantime,checkservicenumber from ("+ "select pcalweek,childuuid,sum(monplantime) + sum(tueplantime) + sum(wedplantime) + sum(thuplantime) + sum(friplantime) as weekplantime,checkservicenumber from ("+ "select strftime(\"%W\",dd.daydate) as pcalweek, dd.daydate, ch.uuid as childuuid, ch.checkservicenumber,"; for (var d4 in aax){ @@ -555,7 +555,7 @@ function generatecsv(){ accsql += getdaterangesql(monthmin,monthmax); accsql += ") dd on ( dd.daydate BETWEEN pl.datebegin AND pl.dateend ) ) group by childuuid,pcalweek ) group by childuuid,pcalweek) planned "; accsql += "left join ( " + - "select checkservicenumber,childuuid, calweek , durationweek , lunchweek " + + "select checkservicenumber || '-' || calweek as idjoin,checkservicenumber,childuuid, calweek , durationweek , lunchweek " + " from (SELECT checkservicenumber,ttl.childuuid,calweek, sumdurationcalweek AS durationweek, sumlunchcalweek as lunchweek " + @@ -568,7 +568,7 @@ function generatecsv(){ "LEFT JOIN ( "+ "SELECT childuuid,calweek,case when strftime('%w',date(calyear ||'-01-01')) in ('1','2','3','4') then (cast(calweek as int)*7)-7 else (cast(calweek as int)*7) end as caldays ,"+ "sumlunchcalweek, coalesce(sum(monduration + tueduration + wedduration + thuduration + friduration),0.0) as sumdurationcalweek " + - "FROM ( SELECT childuuid, daydate,calyear, case when strftime('%w',date(calyear ||'-01-01')) in ('1','2','3','4') then case when calweek < '10' then '0' else '' end || cast(calweek as int)+1 else calweek end as calweek," + + "FROM ( SELECT childuuid, daydate,calyear, case when strftime('%w',date(calyear ||'-01-01')) in ('1','2','3','4') then case when calweek < '10' then '0' else '' end || cast(calweek as int) else calweek end as calweek," + "max(monlunch) + max(tuelunch) + max(wedlunch) + max(thulunch) + max(frilunch) AS sumlunchcalweek, " + "MAX(monduration) + MAX(monduration2) AS monduration, " + "MAX(tueduration) + MAX(tueduration2) AS tueduration, " + @@ -607,13 +607,13 @@ function generatecsv(){ "WHERE xx.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"') GROUP BY childuuid,calweek) GROUP BY childuuid,calweek) px " + "ON ( pch.childuuid=px.childuuid) GROUP BY pch.childuuid,px.calweek ) ttl " + "LEFT JOIN accounting acc ON ( ttl.childuuid = acc.childuuid AND acc.accmonth='"+ initdate.toLocaleFormat("%Y-%m-%d")+ "') where calweek is not null) group by childuuid,calweek) d1 "; - accsql += " on (planned.childuuid=d1.childuuid and planned.pcalweek=d1.calweek) order by planned.childuuid,planned.pcalweek;"; + accsql += " on (d1.idjoin=planned.idjoin) order by planned.childuuid,planned.pcalweek;"; dump("EXPSQL:" + accsql + "\n"); var expdbdata = appdb.dbquery(accsql); //dump(JSON.stringify(expdbdata)); - numchilds = $("#tbl_accounting tbody tr").length; + numchilds = $("#tbl_accounting tbody tr").length -1; var idchkserv = ccreche.sqldata[0].idcheckservice; dump(JSON.stringify(cweeks) + "\n"); var wlen = cweeks.length; -- 2.39.5