From 85adcef825de4c9f4b94558d88e10c5e09f911d8 Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Thu, 18 Oct 2018 17:44:14 +0200 Subject: [PATCH] Accounting changes and presence set time auto --- app_v3/chrome/content/js/system.js | 1 + .../modules/accounting/js/accounting.js | 191 ++++++++++-------- .../modules/accounting/js/accountingchild.js | 4 +- .../accounting/js/accountingopeninvoice.js | 4 +- .../modules/presences/js/dailypresences.js | 15 +- app_v3/defaults/profile/creorga.sqlite | Bin 72704 -> 73728 bytes 6 files changed, 128 insertions(+), 87 deletions(-) diff --git a/app_v3/chrome/content/js/system.js b/app_v3/chrome/content/js/system.js index 149f273..618aa0a 100644 --- a/app_v3/chrome/content/js/system.js +++ b/app_v3/chrome/content/js/system.js @@ -1,4 +1,5 @@ //Source-code licensed under EUPL v1.1 ( Copyright 2016 By DKS s.à r.l. - Kilian Saffran - Luxembourg ) + // function jsdump(str) { // Components.classes['@mozilla.org/consoleservice;1'] // .getService(Components.interfaces.nsIConsoleService) diff --git a/app_v3/chrome/content/modules/accounting/js/accounting.js b/app_v3/chrome/content/modules/accounting/js/accounting.js index ac1bee0..ac97aa6 100644 --- a/app_v3/chrome/content/modules/accounting/js/accounting.js +++ b/app_v3/chrome/content/modules/accounting/js/accounting.js @@ -128,87 +128,120 @@ function load_accounting_table(){ strminweek = cweeks[0].cw; monthmin = cweeks[0].weekbegin; monthmax = cweeks[cweeks.length-1].weekend; - aax = ['mon','tue','wed','thu','fri','sat']; + aax = ['','mon','tue','wed','thu','fri','sat']; // || ' (' || weekhourcosts || '€/h)' -- || ' (' || weeklunchcosts || '€/déj.)'+ - var accsql = "select planned.uuid as childuuid, planned.clientnumber as clientnumber, planned.checkservicenumber as checkservicenumber, COALESCE( planned.prename, '' ) || ' ' || COALESCE( planned.surname, '' ) AS childname, '' as presencedetails, '' as planneddetails,replace(acc.reference,'NOCHECKSERVICE:','(facture non check-service)
') as reference, " + - "case when acc.invoiceamount is not null then printf(\"%.2f\",COALESCE(acc.invoiceamount,0.00)) || '€' || case when acc.invoicedate is not null then '
(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '
R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '
R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '
R3:' || strftime('%d.%m.%Y',acc.reminderdate3) ELSE '' END else '' end else '' end as invoiced, " + -"case when acc.payedamount is not null then printf(\"%.2f\",COALESCE(acc.payedamount,0.00)) || '€' || case when acc.payeddate is not null then '
(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," + -"'' as benefitamount," + -"'' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '' ELSE '' END AS act " + -" from accounting acc join childs planned on (acc.childuuid=planned.uuid) WHERE acc.accmonth='"+ initdate.toLocaleFormat("%Y-%m-%d")+ "' and acc.reference LIKE 'NOCHECKSERVICE:%' " - - accsql += "UNION select planned.childuuid,planned.clientnumber,planned.checkservicenumber,planned.childname,d1.weekdetails as presencedetails,planned.planneddetails,d1.reference,d1.invoiced,d1.payement,d1.benefitamount,d1.act from ( " + - "select childuuid,group_concat( ':' || pcalweek || ' : ' || weekplantime , '
' ) as planneddetails,clientnumber,childname,checkservicenumber from ("+ - "select pcalweek,childuuid,sum(monplantime) + sum(tueplantime) + sum(wedplantime) + sum(thuplantime) + sum(friplantime), sum(satplantime) as weekplantime,clientnumber,childname,checkservicenumber from ("+ - "select strftime(\"%W\",dd.daydate) as pcalweek, dd.daydate, ch.uuid as childuuid, ch.clientnumber, ch.prename || ' ' || ch.surname as childname, ch.checkservicenumber,"; - for (var d4 in aax){ - var dw4 = d4;dw4++; - accsql += "case when strftime(\"%w\",dd.daydate) = '"+ dw4+"' then cast( ( strftime( \"%s\", "+ aax[d4]+"timeend )- strftime( \"%s\", "+ aax[d4]+"timebegin ) )/ 3600.0 AS REAL )+ case when "+ aax[d4]+"timebegin2 is not null then CAST( ( strftime( \"%s\", "+ aax[d4]+"timeend2 )- strftime( \"%s\", "+ aax[d4]+"timebegin2 ) )/ 3600.0 AS REAL ) else 0.0 end else 0.00 end as "+ aax[d4]+"plantime,"; - } - accsql += " null as nouse from childs ch join planning pl on ( ch.uuid = pl.childuuid ) JOIN( " ; - accsql += getdaterangesql(monthmin,monthmax); - accsql += ") dd on ( dd.daydate BETWEEN pl.datebegin AND pl.dateend ) ) group by childuuid,pcalweek ) group by childuuid) planned "; - accsql += "left join ( " + - "select clientnumber,checkservicenumber,childname,childuuid,group_concat( ':' || calweek || ' : ' || durationweek || ' h : ' || lunchweek ,'
') as weekdetails, " + - //"printf(\"%.2f\",sum(weektotalhourcosts)) as monthtotalhourcosts, printf(\"%.2f\",sum(weektotallunchcosts)) as monthtotallunchcosts, printf(\"%.2f\",sum(weektotalhourcosts + weektotallunchcosts)) as monthtotalcosts," + - "invoiced,payement,benefitamount,reference, act from (SELECT childname, checkservicenumber, clientnumber,ttl.childuuid, printf(\"%02d\",calweek) as calweek, printf(\"%.2f\",sumdurationcalweek) AS durationweek, sumlunchcalweek as lunchweek, " + -//"printf(\"%.2f\",weekhourcosts) as weekhourcosts, printf(\"%.2f\",weeklunchcosts) as weeklunchcosts, " + -//"sumdurationcalweek * weekhourcosts as weektotalhourcosts, sumlunchcalweek * weeklunchcosts as weektotallunchcosts, " + -"case when acc.invoiceamount is not null then printf(\"%.2f\",COALESCE(acc.invoiceamount,0.00)) || '€' || case when acc.invoicedate is not null then '
(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '
R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '
R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '
R3:' || strftime('%d.%m.%Y',acc.reminderdate3) ELSE '' END else '' end else '' end as invoiced,replace(acc.reference,'NOCHECKSERVICE:','(facture non check-service)
') as reference, " + -"case when acc.payedamount is not null then printf(\"%.2f\",COALESCE(acc.payedamount,0.00)) || '€' || case when acc.payeddate is not null then '
(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," + -"case when acc.benefitamount is not null then printf(\"%.2f\",COALESCE(acc.benefitamount,0.00)) || '€' else '' end as benefitamount," + -" '' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '' ELSE '' END AS act " + -"FROM ( SELECT pch.childname, pch.checkservicenumber, pch.clientnumber, pch.childuuid, px.calweek, px.sumdurationcalweek, px.sumlunchcalweek " + -//"CASE WHEN px.sumdurationcalweek IS NOT NULL AND pch.checkservicenumber IS NOT NULL THEN ( SELECT coalesce(wc0.costsperhour,0.00) FROM costs wc0 WHERE wc0.startdate < date('"+yearmin+"','+' || caldays || ' days') and wc0.weeklyhourslimit <= px.sumdurationcalweek ORDER BY wc0.startdate DESC,wc0.weeklyhourslimit DESC LIMIT 1 ) WHEN px.sumdurationcalweek IS NOT NULL AND pch.checkservicenumber IS NULL THEN ( SELECT coalesce(wc0.costperhourfallback,0.00) FROM costs wc0 WHERE wc0.startdate < date('"+yearmin+"','+' || caldays || ' days') and wc0.weeklyhourslimit <= px.sumdurationcalweek ORDER BY wc0.startdate DESC,wc0.weeklyhourslimit DESC LIMIT 1 ) ELSE NULL END AS weekhourcosts,"+ -//"CASE WHEN px.sumlunchcalweek IS NOT NULL THEN ( SELECT coalesce(wc0.dailylunchcosts,0.00) FROM costs wc0 WHERE wc0.startdate < date('"+yearmin+"','+' || caldays || ' days') and wc0.weeklyhourslimit <= px.sumdurationcalweek ORDER BY wc0.startdate DESC,wc0.weeklyhourslimit DESC LIMIT 1) ELSE NULL END AS weeklunchcosts " + -"FROM ( SELECT COALESCE(ch.prename,'') || ' ' || COALESCE(ch.surname,'') AS childname,ch.uuid AS childuuid,ch.clientnumber,ch.checkservicenumber " + -"FROM childs ch JOIN planning pl ON ( ch.uuid=pl.childuuid) " + -"WHERE " + -"(( pl.datebegin <= DATE('"+monthmin+"') AND pl.dateend >= DATE('"+monthmax+"')) OR pl.datebegin BETWEEN '"+monthmin+"' AND '"+monthmax+"' OR pl.dateend BETWEEN '"+monthmin+"' AND '"+monthmax+"')) pch " + -"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) else calweek end as calweek," + -"max(monlunch) + max(tuelunch) + max(wedlunch) + max(thulunch) + max(frilunch) , max(satlunch) AS sumlunchcalweek, " + -"MAX(monduration) + MAX(monduration2) AS monduration, " + -"MAX(tueduration) + MAX(tueduration2) AS tueduration, " + -"MAX(wedduration) + MAX(wedduration2) AS wedduration, " + -"MAX(thuduration) + MAX(thuduration2) AS thuduration, " + -"MAX(friduration) + MAX(friduration2) AS friduration, " + -"MAX(satduration) + MAX(satduration2) AS satduration " + -"FROM ( "+ -"SELECT childuuid, daydate, strftime('%Y',daydate) as calyear," + -"case when strftime('%w',daydate)='0' then strftime('%W',date(daydate,'-6 days')) else strftime('%W',date(date(daydate),'-' || strftime('%w',date(daydate)) || ' days','+1 day')) end as calweek, "; - -for (var d1 in aax){ - var dw = d1;dw++; - accsql += "CASE WHEN lunch=1 AND status IN (1,5) AND strftime('%w',daydate) = '"+dw+"' THEN 1 ELSE 0 END AS "+aax[d1]+"lunch,"; -} - -for (var d2 in aax){ - var dw2 = d2;dw2++; - accsql += "CASE WHEN xx.status IS NOT NULL AND strftime('%w',xx.daydate) = '"+dw2+"' AND xx.daydate BETWEEN xx.datebegin AND xx.dateend THEN CAST(( (( CAST(SUBSTR(xx."+ aax[d2]+"timeend,1,2) AS INT) *3600)+( CAST(SUBSTR(xx."+ aax[d2]+"timeend,4,2) AS INT) *60))- ((CAST(SUBSTR(xx."+ aax[d2]+"timebegin,1,2) AS INT) *3600)+( CAST(SUBSTR(xx."+ aax[d2]+"timebegin,4,2) AS INT) *60)) ) / 3600 AS REAL) ELSE 0.0 END AS "+ aax[d2]+"duration,"; +// var accsql = "select planned.uuid as childuuid, planned.clientnumber as clientnumber, planned.checkservicenumber as checkservicenumber, COALESCE( planned.prename, '' ) || ' ' || COALESCE( planned.surname, '' ) AS childname, '' as presencedetails, '' as planneddetails,replace(acc.reference,'NOCHECKSERVICE:','(facture non check-service)
') as reference, " + +// "case when acc.invoiceamount is not null then printf(\"%.2f\",COALESCE(acc.invoiceamount,0.00)) || '€' || case when acc.invoicedate is not null then '
(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '
R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '
R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '
R3:' || strftime('%d.%m.%Y',acc.reminderdate3) ELSE '' END else '' end else '' end as invoiced, " + +// "case when acc.payedamount is not null then printf(\"%.2f\",COALESCE(acc.payedamount,0.00)) || '€' || case when acc.payeddate is not null then '
(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," + +// "'' as benefitamount," + +// "'' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '' ELSE '' END AS act " + +// " from accounting acc join childs planned on (acc.childuuid=planned.uuid) WHERE acc.accmonth='"+ initdate.toLocaleFormat("%Y-%m-%d")+ "' and acc.reference LIKE 'NOCHECKSERVICE:%' " + +// accsql += "UNION select planned.childuuid,planned.clientnumber,planned.checkservicenumber,planned.childname,d1.weekdetails as presencedetails,planned.planneddetails,d1.reference,d1.invoiced,d1.payement,d1.benefitamount,d1.act from ( " + +// "select childuuid,group_concat( ':' || pcalweek || ' : ' || weekplantime , '
' ) as planneddetails,clientnumber,childname,checkservicenumber from ("+ +// "select pcalweek,childuuid,sum(monplantime) + sum(tueplantime) + sum(wedplantime) + sum(thuplantime) + sum(friplantime), sum(satplantime) as weekplantime,clientnumber,childname,checkservicenumber from ("+ +// "select strftime(\"%W\",dd.daydate) as pcalweek, dd.daydate, ch.uuid as childuuid, ch.clientnumber, ch.prename || ' ' || ch.surname as childname, ch.checkservicenumber,"; +// for (var d4 in aax){ +// var dw4 = d4;dw4++; +// accsql += "case when strftime(\"%w\",dd.daydate) = '"+ dw4+"' then cast( ( strftime( \"%s\", "+ aax[d4]+"timeend )- strftime( \"%s\", "+ aax[d4]+"timebegin ) )/ 3600.0 AS REAL )+ case when "+ aax[d4]+"timebegin2 is not null then CAST( ( strftime( \"%s\", "+ aax[d4]+"timeend2 )- strftime( \"%s\", "+ aax[d4]+"timebegin2 ) )/ 3600.0 AS REAL ) else 0.0 end else 0.00 end as "+ aax[d4]+"plantime,"; +// } +// accsql += " null as nouse from childs ch join planning pl on ( ch.uuid = pl.childuuid ) JOIN( " ; +// accsql += getdaterangesql(monthmin,monthmax); +// accsql += ") dd on ( dd.daydate BETWEEN pl.datebegin AND pl.dateend ) ) group by childuuid,pcalweek ) group by childuuid) planned "; +// accsql += "left join ( " + +// "select clientnumber,checkservicenumber,childname,childuuid,group_concat( ':' || calweek || ' : ' || durationweek || ' h : ' || lunchweek ,'
') as weekdetails, " + +// //"printf(\"%.2f\",sum(weektotalhourcosts)) as monthtotalhourcosts, printf(\"%.2f\",sum(weektotallunchcosts)) as monthtotallunchcosts, printf(\"%.2f\",sum(weektotalhourcosts + weektotallunchcosts)) as monthtotalcosts," + +// "invoiced,payement,benefitamount,reference, act from (SELECT childname, checkservicenumber, clientnumber,ttl.childuuid, printf(\"%02d\",calweek) as calweek, printf(\"%.2f\",sumdurationcalweek) AS durationweek, sumlunchcalweek as lunchweek, " + +// //"printf(\"%.2f\",weekhourcosts) as weekhourcosts, printf(\"%.2f\",weeklunchcosts) as weeklunchcosts, " + +// //"sumdurationcalweek * weekhourcosts as weektotalhourcosts, sumlunchcalweek * weeklunchcosts as weektotallunchcosts, " + +// "case when acc.invoiceamount is not null then printf(\"%.2f\",COALESCE(acc.invoiceamount,0.00)) || '€' || case when acc.invoicedate is not null then '
(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '
R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '
R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '
R3:' || strftime('%d.%m.%Y',acc.reminderdate3) ELSE '' END else '' end else '' end as invoiced,replace(acc.reference,'NOCHECKSERVICE:','(facture non check-service)
') as reference, " + +// "case when acc.payedamount is not null then printf(\"%.2f\",COALESCE(acc.payedamount,0.00)) || '€' || case when acc.payeddate is not null then '
(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," + +// "case when acc.benefitamount is not null then printf(\"%.2f\",COALESCE(acc.benefitamount,0.00)) || '€' else '' end as benefitamount," + +// " '' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '' ELSE '' END AS act " + +// "FROM ( SELECT pch.childname, pch.checkservicenumber, pch.clientnumber, pch.childuuid, px.calweek, px.sumdurationcalweek, px.sumlunchcalweek " + +// //"CASE WHEN px.sumdurationcalweek IS NOT NULL AND pch.checkservicenumber IS NOT NULL THEN ( SELECT coalesce(wc0.costsperhour,0.00) FROM costs wc0 WHERE wc0.startdate < date('"+yearmin+"','+' || caldays || ' days') and wc0.weeklyhourslimit <= px.sumdurationcalweek ORDER BY wc0.startdate DESC,wc0.weeklyhourslimit DESC LIMIT 1 ) WHEN px.sumdurationcalweek IS NOT NULL AND pch.checkservicenumber IS NULL THEN ( SELECT coalesce(wc0.costperhourfallback,0.00) FROM costs wc0 WHERE wc0.startdate < date('"+yearmin+"','+' || caldays || ' days') and wc0.weeklyhourslimit <= px.sumdurationcalweek ORDER BY wc0.startdate DESC,wc0.weeklyhourslimit DESC LIMIT 1 ) ELSE NULL END AS weekhourcosts,"+ +// //"CASE WHEN px.sumlunchcalweek IS NOT NULL THEN ( SELECT coalesce(wc0.dailylunchcosts,0.00) FROM costs wc0 WHERE wc0.startdate < date('"+yearmin+"','+' || caldays || ' days') and wc0.weeklyhourslimit <= px.sumdurationcalweek ORDER BY wc0.startdate DESC,wc0.weeklyhourslimit DESC LIMIT 1) ELSE NULL END AS weeklunchcosts " + +// "FROM ( SELECT COALESCE(ch.prename,'') || ' ' || COALESCE(ch.surname,'') AS childname,ch.uuid AS childuuid,ch.clientnumber,ch.checkservicenumber " + +// "FROM childs ch JOIN planning pl ON ( ch.uuid=pl.childuuid) " + +// "WHERE " + +// "(( pl.datebegin <= DATE('"+monthmin+"') AND pl.dateend >= DATE('"+monthmax+"')) OR pl.datebegin BETWEEN '"+monthmin+"' AND '"+monthmax+"' OR pl.dateend BETWEEN '"+monthmin+"' AND '"+monthmax+"')) pch " + +// "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) else calweek end as calweek," + +// "max(monlunch) + max(tuelunch) + max(wedlunch) + max(thulunch) + max(frilunch) , max(satlunch) AS sumlunchcalweek, " + +// "MAX(monduration) + MAX(monduration2) AS monduration, " + +// "MAX(tueduration) + MAX(tueduration2) AS tueduration, " + +// "MAX(wedduration) + MAX(wedduration2) AS wedduration, " + +// "MAX(thuduration) + MAX(thuduration2) AS thuduration, " + +// "MAX(friduration) + MAX(friduration2) AS friduration, " + +// "MAX(satduration) + MAX(satduration2) AS satduration " + +// "FROM ( "+ +// "SELECT childuuid, daydate, strftime('%Y',daydate) as calyear," + +// "case when strftime('%w',daydate)='0' then strftime('%W',date(daydate,'-6 days')) else strftime('%W',date(date(daydate),'-' || strftime('%w',date(daydate)) || ' days','+1 day')) end as calweek, "; + +// for (var d1 in aax){ +// var dw = d1;dw++; +// accsql += "CASE WHEN lunch=1 AND status IN (1,5) AND strftime('%w',daydate) = '"+dw+"' THEN 1 ELSE 0 END AS "+aax[d1]+"lunch,"; +// } + +// for (var d2 in aax){ +// var dw2 = d2;dw2++; +// accsql += "CASE WHEN xx.status IS NOT NULL AND strftime('%w',xx.daydate) = '"+dw2+"' AND xx.daydate BETWEEN xx.datebegin AND xx.dateend THEN CAST(( (( CAST(SUBSTR(xx."+ aax[d2]+"timeend,1,2) AS INT) *3600)+( CAST(SUBSTR(xx."+ aax[d2]+"timeend,4,2) AS INT) *60))- ((CAST(SUBSTR(xx."+ aax[d2]+"timebegin,1,2) AS INT) *3600)+( CAST(SUBSTR(xx."+ aax[d2]+"timebegin,4,2) AS INT) *60)) ) / 3600 AS REAL) ELSE 0.0 END AS "+ aax[d2]+"duration,"; +// } + + +// for (var d3 in aax){ +// var dw3 = d3;dw3++; +// accsql += "CASE WHEN xx.status IS NOT NULL AND strftime('%w',xx.daydate) = '1' AND xx.daydate BETWEEN xx.datebegin AND xx.dateend AND xx."+ aax[d3]+"timebegin2 IS NOT NULL AND xx."+ aax[d3]+"timeend2 IS NOT NULL THEN CAST(( (( CAST(SUBSTR(xx."+ aax[d3]+"timeend2,1,2) AS INT) *3600)+( CAST(SUBSTR(xx."+ aax[d3]+"timeend2,4,2) AS INT) *60))- ((CAST(SUBSTR(xx."+ aax[d3]+"timebegin2,1,2) AS INT) *3600)+( CAST(SUBSTR(xx."+ aax[d3]+"timebegin2,4,2) AS INT) *60)) ) / 3600 AS REAL) ELSE 0.0 END AS "+ aax[d3]+"duration2,"; +// } + +// accsql += "null as nouse FROM (" + +// " select vacancydate as daydate, CAST('0' as boolean) as lunch,1 as status,pl.* from vacancy va " + +// " left join planning pl on (va.vacancydate between pl.datebegin and pl.dateend) " + +// " where va.vacancydateto is null and va.vacancydate BETWEEN '"+monthmin+"' AND '"+monthmax+"' AND strftime('%w',va.vacancydate) not in ('0') " + +// " group by va.vacancydate,pl.childuuid " + +// " union " + +// " select pr.daydate,pr.lunch,pr.status,pl.* FROM presence pr " + +// " JOIN planning pl ON ( pr.childuuid=pl.childuuid) WHERE pr.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"' "+ +// ") xx " + +// "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) d1 "; +// accsql += " on (planned.childuuid=d1.childuuid) order by planned.childname;"; +/* READY TO INSERT: */ +accsql = 'select plx.childuuid, plx.clientnumber, plx.checkservicenumber, plx.childname, plx.planneddetails,acc.invoiced, acc.reference, acc.payement, acc.benefitamount, acc.act,prd.presencedetails ' + +'from ( select childuuid, group_concat( \':\' || pcalweek || \' : \' || weekplantime , \'
\' ) as planneddetails, clientnumber, childname, checkservicenumber from (' + +'select pcalweek, childuuid, coalesce(sum(monplantime),0.00) + coalesce(sum(tueplantime),0.00) + coalesce(sum(wedplantime),0.00) + coalesce(sum(thuplantime),0.00) + coalesce(sum(friplantime),0.00) +coalesce(sum(satplantime),0.00) as weekplantime, clientnumber, childname, checkservicenumber ' + + 'from ( select strftime("%W", dd.daydate) as pcalweek, strftime("%w", dd.daydate) as weekday, dd.daydate, ch.uuid as childuuid, ch.clientnumber, ch.prename || \' \' || ch.surname as childname, ch.checkservicenumber, '; +for (var i=1;i %d.%m.%Y', a2.invoicedate),'') || coalesce(', R1: ' || strftime('%d.%m.%Y', a2.reminderdate1),'') || coalesce(', R2: ' || strftime('%d.%m.%Y', a2.reminderdate2),'') || coalesce(', R3: ' || strftime('%d.%m.%Y', a2.reminderdate3),'') as invoiced, a2.reference, "+ + "coalesce(printf(\"%.2f €\",a2.payedamount), 0.00) || coalesce (strftime('
%d.%m.%Y', a2.payeddate),'') as payement,"+ + "coalesce(printf(\"%.2f €\",a2.benefitamount), 0.00) as benefitamount," + + "'' || CASE WHEN a2.invoicefile IS NOT NULL and a2.invoicefile != '' THEN '' " + + " ELSE '' END AS act from accounting a2 " + + " where a2.accmonth = '2017-03-01' and a2.nocheckservice != '1' ) acc on (plx.childuuid=acc.childuuid) left join (" + +"select childuuid, group_concat( ':' || calweek || ' : ' || weekduration || ' : ' || weeklunch,'
') as presencedetails from ( "+ +"select childuuid, calweek, monlunch + tuelunch + wedlunch + thulunch + frilunch + satlunch as weeklunch, "+ +"monduration + tueduration + wedduration + thuduration + friduration + satduration + monduration2 + tueduration2 + wedduration2 + thuduration2 + friduration2 + satduration2 as weekduration " + +" from ( " + "select childuuid,daydate,strftime('%Y', daydate) as calyear, " + + "case when strftime('%w', daydate)= '0' then strftime('%W', date(daydate, '-6 days')) else strftime('%W', date(date(daydate), '-' || strftime('%w', date(daydate)) || ' days', '+1 day')) end as calweek, "; + for (var i=1;i'+ ' de: '+ '
' + - '
'+ + ' '+ ' à: '+ '
'+ - '
' + + '' + 'et de: '+ '
' + - '
'+ + ' '+ ' à: '+ '
'+ - '
' + + '' + '
'; rdata += ''+ '
' + @@ -388,6 +388,13 @@ function onnextday() { }); } +function timefocus(obj){ + if (!obj.value || obj.value == ''){ + var currentdate = new Date(); + obj.value=currentdate.toLocaleFormat("%H:%M"); + } + obj.select(); +} diff --git a/app_v3/defaults/profile/creorga.sqlite b/app_v3/defaults/profile/creorga.sqlite index e830b9e3c4b18da866a258c4ad7fd60497a645ea..75bd7cde2a33286c7bcd1b92a2f9dcb7ee6285a4 100644 GIT binary patch delta 672 zcmZqJ!P0PmWrDPzJp%)S2N1)6{X`vOprD?@Oje+fF!Mr?gvZ1J6(;6|lLc8qHon@< z1d?(0nY@hIp2tlL$d(cS5~AFjKQc>sFbZ!Lbok6X`9C)+9}7bq1M_*N4NT2IhVn#5 z5oT*&^~r`;6t;VcFz#mF%);`QUz=$@12bb51M^YF*^F5r$nXIOL4fH3qrbSiI*{@Z zmz8B~iQRlFh>1~LAtS%Es8}J$)zL@6#nsI*)F(v2K+j$1d21V zF)%QyIm-gAlbpOSNQeb!;Md7lfHv=Iijs`?nfQ2tj$vk;%)m5{DVK5bWK{dlLlGBOr$0J*MyF8Y2z?-_8$`?7-qq*ZY8tss48 zpp-&sX=aK-h-*ZM4v3eMSecSolB(e17~%>R$SKWB&QNgj_xEvi^aG0&mn4>y7AtuA zg}A!A22F32Vw9NfD$Qt6uK+S2DK$MauOu@!)evGRSTHp&1ziZP&InT-R0w2TYD#8F Yei2j&_SgeEDj>*n@}poGW?n8$0IW}|QUCw| delta 523 zcmZoTz|ydTWrDPzH3I{KI}pQw^+X+GprD?@Oje+f5c4vSg!{w-mC1rEVH;mpSy#@UQn4AKlAfDi<>r-?H9 zGcxH5OuiT_v$-Qx9LVMavSlalOB3R1(&c3rmz8B~(cP?*!o)Z^cB8=b*Yb=)OkA3q zSXcyjn3=XRFdt?5z_gXAVY`3`V;Ezoj}DiDLQ!g3YEf!law^D8ArO(=%)FG;qLjpv zR6_+9#}HR^Q6sFP#$eIO8|Ry=X6BXUXC|knW#%A^O-juJ3X~wkrZ13Y6jKE{A+@5U zC^0cNzcjBzA;{IyM@K;^FF!dWH95ODwWthej?&}@(vsqd$;r9-c_kS-$r+hBDW#>E zDVkiG3=GqIc^Hk@ff}hW`E|PJ=D2`m%v{Wjstin0 Uj87R=Hw&_eGj3OvWW3D;05y<{%>V!Z -- 2.39.5