From 911e7fef3af113d197b15dedf322a5ed636d8a40 Mon Sep 17 00:00:00 2001
From: Kilian Saffran <ksaffran@dks.lu>
Date: Mon, 8 Oct 2018 14:04:55 +0200
Subject: [PATCH] =?utf8?q?bugfix=20d=C3=A9compte=20et=20ajout=20pour=20ver?=
 =?utf8?q?ifier=20l'int=C3=A9gartion=20de=20la=20b.d.=20creorga?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 Tools/creorgadb.pl                            |  27 +-
 app_v3/chrome/content/js/database.js          |  75 ++--
 app_v3/chrome/content/js/system.js            |  14 +-
 .../modules/accounting/accounting.html        |   5 +-
 .../modules/accounting/accountingchild.html   |   4 +-
 .../accounting/accountingopeninvoice.html     |  15 +-
 .../modules/accounting/js/accounting.js       |  29 +-
 .../modules/accounting/js/accountingchild.js  | 328 +++++++++++-------
 .../accounting/js/accountingopeninvoice.js    | 156 ++++++---
 .../modules/presences/js/dailypresences.js    |  26 +-
 .../content/modules/support/js/multisite.js   |  44 ++-
 .../content/modules/support/multisite.html    |   7 +-
 app_v3/defaults/profile/creorga.sqlite        | Bin 71680 -> 72704 bytes
 install/create_setup_windows.pl               |   2 +-
 14 files changed, 455 insertions(+), 277 deletions(-)

diff --git a/Tools/creorgadb.pl b/Tools/creorgadb.pl
index 3a5d607..4966f1a 100644
--- a/Tools/creorgadb.pl
+++ b/Tools/creorgadb.pl
@@ -6,6 +6,8 @@ use Getopt::Long;
 use Data::Dumper;
 use File::Copy qw/copy/;
 use Time::Local;
+use UUID::Generator::PurePerl;
+
 use utf8;
 use lib ('.');
 use sqlite;
@@ -17,7 +19,7 @@ GetOptions("dbfile|db=s" => \$dbfile,
            "template|t=s" => \$templatedb
           ); 
 #-db "C:\\Users\\ksaff\\DKS\\projects\\Creorga\\lechatpotte\\7c41cbd9-92b6-6d49-8682-a5404b726d10.sqlite" -t "C:\\Users\\ksaff\\Workspace\\creorga\\app\\defaults\\profile\\creorga.sqlite"
-
+my $ug = UUID::Generator::PurePerl->new();
 sub	getcoldef($){
 	my $strddl = shift;
 	my $curddl = $strddl;
@@ -107,7 +109,28 @@ foreach my $pd (sort {$a <=> $b} keys(%{$defdbschemacfg})){
    $sql_tmptbl =~ s/CREATE\ TABLE\ /CREATE TABLE new_/;
    $sql_tmptbl =~ s/"//g;
    push(@ainssql,$sql_tmptbl);
-   push(@ainssql,"INSERT INTO new_".$defdbschemacfg->{$pd}->{tbl_name}." (".join(',',@copycols).") SELECT ".join(',',@copycols)." FROM ".$defdbschemacfg->{$pd}->{tbl_name}.";");  
+
+   if ($defdbschemacfg->{$pd}->{tbl_name} ne "accounting"){
+     push(@ainssql,"INSERT INTO new_".$defdbschemacfg->{$pd}->{tbl_name}." (".join(',',@copycols).") SELECT ".join(',',@copycols)." FROM ".$defdbschemacfg->{$pd}->{tbl_name}.";");  
+   }elsif ((index($sql_tmptbl,"PRIMARY KEY (uuid)") > 0) && ($defdbschemacfg->{$pd}->{tbl_name} eq "accounting")) {
+      # adding primary keys
+      my $data = $db->dbquerysorted("select * from ".$defdbschemacfg->{$pd}->{tbl_name}.";");
+      push (@copycols,'uuid');
+      foreach my $da ( keys(%{$data})){
+        my @ndata = ();
+        foreach my $x (@copycols){
+         if ($x eq "uuid"){
+           push(@ndata,$ug->generate_v4());
+         } else {
+           push(@ndata,$data->{$da}->{$x});
+         }
+        }
+       push(@ainssql,"INSERT INTO new_".$defdbschemacfg->{$pd}->{tbl_name}." (".join(',',@copycols).") VALUES ('".join("','",@ndata)."');");
+     }
+   }
+   else {
+      push(@ainssql,"INSERT INTO new_".$defdbschemacfg->{$pd}->{tbl_name}." (".join(',',@copycols).") SELECT ".join(',',@copycols)." FROM ".$defdbschemacfg->{$pd}->{tbl_name}.";");
+   }
    push(@ainssql,"DROP TABLE ".$defdbschemacfg->{$pd}->{tbl_name}.";");
    push(@ainssql,"ALTER TABLE new_".$defdbschemacfg->{$pd}->{tbl_name}. " RENAME TO ".$defdbschemacfg->{$pd}->{tbl_name}.";");
    $bvaccum = 1;
diff --git a/app_v3/chrome/content/js/database.js b/app_v3/chrome/content/js/database.js
index df9792d..68d699a 100644
--- a/app_v3/chrome/content/js/database.js
+++ b/app_v3/chrome/content/js/database.js
@@ -266,16 +266,19 @@ var appdb = {
 	    return curdbfiles;
 	  },
 
-  check_schema: function(){
-    alert("TEST");
+  check_schema: function(dbid){
+    //alert("TEST");
+    var dbconf = apppref.getpreference("support.database." + dbid);
+    var sitecfg = JSON.parse(dbconf);
+    var dbpath = sitecfg.path.local + system.sep() + sitecfg.uuid + ".sqlite";
     var defdb = system.appdir() + system.sep() + 'defaults'+ system.sep() + 'profile' + system.sep() + 'creorga.sqlite';
 
-    var dbfiles = appdb.getdatabases();
-    alert(JSON.stringify(dbfiles));
-    for (var cdb in dbfiles){
-    	if (system.fileexists(dbfiles[cdb])){
+    // var dbfiles = appdb.getdatabases();
+    alert(JSON.stringify(sitecfg));
+    // for (var cdb in dbfiles){
+    	if (system.fileexists(dbpath)){
     		
-    		var args = ["-db",dbfiles[cdb],"-t",defdb];
+    		var args = ["-db",dbpath,"-t",defdb];
     		  
     	        var bincheck = system.toolsdir() + system.sep() + "creorgadb.exe";
     	        if (system.os != "WINNT") {
@@ -284,38 +287,38 @@ var appdb = {
     	        system.runcmdline(bincheck,args,null);
 
     	}
-    }
-    OS.File.remove(system.profilerootdir() + system.sep() + '/checkschema.txt',{ignoreAbsent:true});
+    // }
+    // OS.File.remove(system.profilerootdir() + system.sep() + '/checkschema.txt',{ignoreAbsent:true});
 
   }//,
 
 }
 
-var sync = {
-		//TODO: dynamic url and dynamic (curcfg) schema -> ident
+// var sync = {
+// 		//TODO: dynamic url and dynamic (curcfg) schema -> ident
 		
-		//url: "http://localhost:6060/localsyncup",
-		up: function(ddlquery){
-			//dump("dbfile" +  + "\n");
-			/*var request = Components.classes["@mozilla.org/xmlextras/xmlhttprequest;1"].createInstance();
-			request.onload = function(aEvent) {
-				  //dump("Response Text: " + aEvent.target.responseText + "\n");
-				};
-			request.onerror = function(aEvent) {
-				//dump("Error Msg:" + aEvent.target.responseText + "\n");
-				//dump("Error Status: " + aEvent.target.status + "\n");
-			};
-			request.open("POST", sync.url, true);
-			request.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");*/
-			var cts = new Date();
-			var milli = cts.getMilliseconds();
-    		if (milli < 10){
-    			milli = "00" + milli;
-    		} else if (milli < 100){
-    			milli = "0" + milli;
-    		} 
-			var dbident = system.getFileName(appdb.dbFile.path).replace(/\.sqlite/,'');
-		    dump("SQL;;"+ cts.toLocaleFormat('%Y%m%d%H%M%S')+ milli +";;" + dbident +";;"+ddlquery+ "\n");
-			//request.send("ts="+ cts.toLocaleFormat('%Y-%m-%d %H:%M:%S') +"&q=" +encodeURIComponent(ddlquery)+ "&ident="+dbident+"&rh="+ system.hostname());
-		}
-}
+// 		//url: "http://localhost:6060/localsyncup",
+// 		up: function(ddlquery){
+// 			//dump("dbfile" +  + "\n");
+// 			/*var request = Components.classes["@mozilla.org/xmlextras/xmlhttprequest;1"].createInstance();
+// 			request.onload = function(aEvent) {
+// 				  //dump("Response Text: " + aEvent.target.responseText + "\n");
+// 				};
+// 			request.onerror = function(aEvent) {
+// 				//dump("Error Msg:" + aEvent.target.responseText + "\n");
+// 				//dump("Error Status: " + aEvent.target.status + "\n");
+// 			};
+// 			request.open("POST", sync.url, true);
+// 			request.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");*/
+// 			var cts = new Date();
+// 			var milli = cts.getMilliseconds();
+//     		if (milli < 10){
+//     			milli = "00" + milli;
+//     		} else if (milli < 100){
+//     			milli = "0" + milli;
+//     		} 
+// 			var dbident = system.getFileName(appdb.dbFile.path).replace(/\.sqlite/,'');
+// 		    dump("SQL;;"+ cts.toLocaleFormat('%Y%m%d%H%M%S')+ milli +";;" + dbident +";;"+ddlquery+ "\n");
+// 			//request.send("ts="+ cts.toLocaleFormat('%Y-%m-%d %H:%M:%S') +"&q=" +encodeURIComponent(ddlquery)+ "&ident="+dbident+"&rh="+ system.hostname());
+// 		}
+// }
diff --git a/app_v3/chrome/content/js/system.js b/app_v3/chrome/content/js/system.js
index 4c37839..9cca41a 100644
--- a/app_v3/chrome/content/js/system.js
+++ b/app_v3/chrome/content/js/system.js
@@ -1,9 +1,9 @@
 //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)
-//             .logStringMessage(str);
-// }
+function jsdump(str) {
+  Components.classes['@mozilla.org/consoleservice;1']
+            .getService(Components.interfaces.nsIConsoleService)
+            .logStringMessage(str);
+}
 
 Components.utils["import"]("resource://gre/modules/FileUtils.jsm");
 Components.utils["import"]("resource://gre/modules/Services.jsm");
@@ -315,10 +315,10 @@ var system = {
   runcmdline: function(cmdlapp,args,callback){
     var file = Components.classes["@mozilla.org/file/local;1"].createInstance(Components.interfaces.nsILocalFile);
     var process = Components.classes["@mozilla.org/process/util;1"].createInstance(Components.interfaces.nsIProcess);
-    dump(cmdlapp + " "+ JSON.stringify(args) + "\n");
+    //dump(cmdlapp + " "+ JSON.stringify(args) + "\n");
     file.initWithPath(cmdlapp);
     process.init(file);
-    dump(cmdlapp + " "+ JSON.stringify(args) + "\n");
+    //dump(cmdlapp + " "+ JSON.stringify(args) + "\n");
     process.runwAsync( args, args.length,callback);
     return true;
   },
diff --git a/app_v3/chrome/content/modules/accounting/accounting.html b/app_v3/chrome/content/modules/accounting/accounting.html
index 530aa59..be04fec 100644
--- a/app_v3/chrome/content/modules/accounting/accounting.html
+++ b/app_v3/chrome/content/modules/accounting/accounting.html
@@ -60,10 +60,9 @@
               <div class="modal-body" style="max-height: calc(100vh - 200px);overflow-y: auto; ">
 
                 <div id="frmeditaccounting">
-                  <input type="hidden" value="0" id="childuuid" />
+                  <input type="hidden" value="0" id="uuid" />
                   <input type="hidden" value="" id="action" />
-                  <input type="hidden" value="" id="oldref" />
-                  <input type="hidden" value="" id="oldaccmonth" />
+                  <input type="hidden" value="" id="childuuid" />
                   <div class="form-group">
                     <div class="checkbox" >
                       <input type="checkbox" value="1" id="nocheckservice">
diff --git a/app_v3/chrome/content/modules/accounting/accountingchild.html b/app_v3/chrome/content/modules/accounting/accountingchild.html
index c831148..6ff7f84 100644
--- a/app_v3/chrome/content/modules/accounting/accountingchild.html
+++ b/app_v3/chrome/content/modules/accounting/accountingchild.html
@@ -60,9 +60,9 @@
               <div class="modal-body" style="max-height: calc(100vh - 300px);overflow-y: auto; ">
 
                 <div id="frmeditaccounting">
-                  <input type="hidden" value="" id="oldaccmonth" />
+                  <input type="hidden" value="" id="uuid" />
                   <input type="hidden" value="" id="action" />
-                  <input type="hidden" value="" id="oldref" />
+                  <input type="hidden" value="" id="childuuid" />
                   
                   <div class="form-group">
                   <div class="checkbox" >
diff --git a/app_v3/chrome/content/modules/accounting/accountingopeninvoice.html b/app_v3/chrome/content/modules/accounting/accountingopeninvoice.html
index f86a1ed..b210ca0 100644
--- a/app_v3/chrome/content/modules/accounting/accountingopeninvoice.html
+++ b/app_v3/chrome/content/modules/accounting/accountingopeninvoice.html
@@ -43,15 +43,26 @@
               <div class="modal-body" style="max-height: calc(100vh - 300px);overflow-y: auto; ">
 
                 <div id="frmeditaccounting">
-                  <input type="hidden" value="0" id="accmonth" />
+                  <input type="hidden" value="" id="uuid" />
                   <input type="hidden" value="" id="action" />
+                  <input type="hidden" value="" id="childuuid" />
+                  <div class="form-group">
+                  <div class="checkbox" >
+                    <input type="checkbox" value="1" id="nocheckservice">
+                    <label for="nocheckservice">facture NON Check-Service</label>
+                  </div>
+                  </div>
+                  <div class="form-group">
+                    <label for="name">Enfant</label>
+                    <input type="text" value="" id="childname"  nullable="true" readonly="true" class="form-control">
+                  </div>
                   <div class="form-group">
                     <label for="name">Référence</label>
                     <input type="text" value="" id="reference"  nullable="true" class="input- form-control">
                   </div>
                   <div class="form-group">
                     <label for="invoicefile">Fichier</label>
-                    <input type="file" value="" id="invoicefile" nullable="true" class="form-control" disabled="1">
+                    <input type="file" value="" id="invoicefile" nullable="true" class="form-control"">
                   </div>
                   <div class="form-group col-md-6" id="date-container">
                     <label for="invoicedate">Date de la Facture</label>
diff --git a/app_v3/chrome/content/modules/accounting/js/accounting.js b/app_v3/chrome/content/modules/accounting/js/accounting.js
index 891937a..ac1bee0 100644
--- a/app_v3/chrome/content/modules/accounting/js/accounting.js
+++ b/app_v3/chrome/content/modules/accounting/js/accounting.js
@@ -134,7 +134,7 @@ function load_accounting_table(){
   "case when acc.invoiceamount is not null then printf(\"%.2f\",COALESCE(acc.invoiceamount,0.00)) || '€' || case when acc.invoicedate is not null then '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," +
 "'' as benefitamount," +
-"'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.childuuid || ''',''' || acc.reference || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.childuuid || ''',''' || acc.accmonth || ''',''' || acc.reference || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act  " +
+"'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.uuid || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' 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 ( " +
@@ -157,7 +157,7 @@ function load_accounting_table(){
 "case when acc.invoiceamount is not null then printf(\"%.2f\",COALESCE(acc.invoiceamount,0.00)) || '€' || case when acc.invoicedate is not null then '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>R3:' || strftime('%d.%m.%Y',acc.reminderdate3) ELSE '' END else '' end else '' end  as invoiced,replace(acc.reference,'NOCHECKSERVICE:','<span style=\"color: red\">(facture non check-service)</span><br/>') 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 '<br/>(' || 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," +
-" '<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || ttl.childuuid || ''',''' || acc.reference || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\" confirm_delete_accounting(''' || acc.childuuid || ''',''' || acc.accmonth || ''',''' || acc.reference || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act " +
+" '<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\" confirm_delete_accounting(''' || acc.uuid || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' 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 " + 
@@ -417,15 +417,13 @@ function set_accmonth(){
       });
 }
 
-function confirm_delete_accounting(dchilduuid,daccmonth,dref) {
-  delacc.childuuid = dchilduuid;
-  delacc.accmonth=daccmonth;
-  delacc.accref=dref;
+function confirm_delete_accounting(duuid) {
+  delacc.uuid = duuid;
   $('#confirm_delete_accounting').modal('show');
 }
 
 function delete_accounting() {
-  var xsql = "DELETE FROM accounting where childuuid='"+ delacc.childuuid+"' and accmonth='"+delacc.accmonth+" and reference='"+delacc.accref+"';";
+  var xsql = "DELETE FROM accounting where uuid='"+ delacc.uuid+"';";
 
   appdb.dbexec(xsql);
   load_accounting_table();
@@ -434,22 +432,21 @@ function delete_accounting() {
   $('#confirm_delete_accounting').modal('hide');
 }
 
-function dlg_accounting(childuuid,xref){
+function dlg_accounting(myuuid){
   
   //alert("TEST");
-  var acc1sql = "SELECT acc.accmonth,ch.uuid as childuuid,COALESCE( ch.prename, '' ) || ' ' || COALESCE( ch.surname, '' ) || '(' || ch.checkservicenumber || ')' AS childname,case when acc.invoicedate is not null then strftime('%d.%m.%Y',acc.invoicedate) else null end as invoicedate, case when acc.payeddate is not null then strftime('%d.%m.%Y',acc.payeddate) else null end as payeddate, payedamount, invoiceamount,benefitamount, replace(acc.reference,'NOCHECKSERVICE:','') as reference, instr(acc.reference,'NOCHECKSERVICE:') as ischeckservice, case when acc.reminderdate1 is not null then strftime('%d.%m.%Y',acc.reminderdate1) else null end as reminderdate1, case when acc.reminderdate2 is not null then strftime('%d.%m.%Y',acc.reminderdate2) else null end as reminderdate2, case when acc.reminderdate3 is not null then strftime('%d.%m.%Y',acc.reminderdate3) else null end as reminderdate3, case when acc.invoicefile is not null and acc.invoicefile != '' then '"+ importpath.path + system.sep() +"' || acc.invoicefile else null end as invoicefile,'upd' as action FROM accounting acc join childs ch on (ch.uuid=acc.childuuid) where acc.accmonth = '"+ initdate.toLocaleFormat('%Y-%m-%d')+"' and acc.childuuid='"+childuuid+"' and acc.reference='"+ xref +"';";
+  var acc1sql = "SELECT acc.uuid,acc.accmonth,ch.uuid as childuuid,COALESCE( ch.prename, '' ) || ' ' || COALESCE( ch.surname, '' ) || '(' || ch.checkservicenumber || ')' AS childname,case when acc.invoicedate is not null then strftime('%d.%m.%Y',acc.invoicedate) else null end as invoicedate, case when acc.payeddate is not null then strftime('%d.%m.%Y',acc.payeddate) else null end as payeddate, payedamount, invoiceamount,benefitamount, replace(acc.reference,'NOCHECKSERVICE:','') as reference, instr(acc.reference,'NOCHECKSERVICE:') as ischeckservice, case when acc.reminderdate1 is not null then strftime('%d.%m.%Y',acc.reminderdate1) else null end as reminderdate1, case when acc.reminderdate2 is not null then strftime('%d.%m.%Y',acc.reminderdate2) else null end as reminderdate2, case when acc.reminderdate3 is not null then strftime('%d.%m.%Y',acc.reminderdate3) else null end as reminderdate3, case when acc.invoicefile is not null and acc.invoicefile != '' then '"+ importpath.path + system.sep() +"' || acc.invoicefile else null end as invoicefile,'upd' as action FROM accounting acc join childs ch on (ch.uuid=acc.childuuid) where acc.uuid = '"+ myuuid+"';";
  
   //jsdump(acc1sql);
   var acc1data = appdb.dbquery(acc1sql);
   
   if (!acc1data.sqldata) {
-  
-    acc1sql = "SELECT '"+ childuuid+"' as childuuid, 1 as ischeckservice, null as invoicedate, null as payeddate, null as payedamount, null as invoiceamount, null as reference,null as benefitamount, null as reminderdate1, null as reminderdate2, null as reminderdate3, null as invoicefile, 'ins' as action;";
-    acc1data = appdb.dbquery(acc1sql);
+    return false;
+    //acc1sql = "SELECT '0' as uuid,'"+ childuuid+"' as childuuid, 1 as ischeckservice, null as invoicedate, null as payeddate, null as payedamount, null as invoiceamount, null as reference,null as benefitamount, null as reminderdate1, null as reminderdate2, null as reminderdate3, null as invoicefile, 'ins' as action;";
+    //acc1data = appdb.dbquery(acc1sql);
   }
   acc1data = acc1data.sqldata[0];
-  $("#frmeditaccounting #oldref").val(acc1data.reference);
-  $("#frmeditaccounting #oldaccmonth").val(acc1data.accmonth);
+  $("#frmeditaccounting #uuid").val(acc1data.uuid);
   if (acc1data.ischeckservice > 0){
     $("#frmeditaccounting #nocheckservice").prop("checked",true);
   } else {
@@ -465,7 +462,7 @@ function dlg_accounting(childuuid,xref){
   $("#edit_accounting").modal('show');
   
 }
-//TODO: important
+
 function save_accounting(){
 
   var sql2 = new Array();
@@ -512,7 +509,7 @@ function save_accounting(){
         }
       }
     });
-  var xsql = "UPDATE accounting SET "+sql2.join(',')+" where \"childuuid\"='" + $("#frmeditaccounting #childuuid").val() +"' and accmonth='"+ $("#frmeditaccounting #oldaccmonth").val()+"' and reference='"+ $("#frmeditaccounting #oldref").val() +"';";
+  var xsql = "UPDATE accounting SET "+sql2.join(',')+" where \"uuid\"='" + $("#frmeditaccounting #uuid").val() + "';";
   //}
   // else {
   //   xsql = "INSERT INTO accounting ("+sql1.join(',')+") VALUES ("+sql2.join(',')+");"; 
diff --git a/app_v3/chrome/content/modules/accounting/js/accountingchild.js b/app_v3/chrome/content/modules/accounting/js/accountingchild.js
index 68c4a81..120e785 100644
--- a/app_v3/chrome/content/modules/accounting/js/accountingchild.js
+++ b/app_v3/chrome/content/modules/accounting/js/accountingchild.js
@@ -23,7 +23,7 @@ function initdata(){
   var ccreche = appdb.dbquery("select uuid from creche LIMIT 1;");
   crecheuuid = ccreche.sqldata[0].uuid;
   importpath = FileUtils.File(curcfg.path.local  + system.sep() +"imports" + system.sep() + crecheuuid);
-  check_accountingtable();
+  //check_accountingtable();
   load_childlist();
   if (pconfig.accchild) {
   //  
@@ -52,97 +52,178 @@ function initdata(){
 
 function load_accounting_table(){
   //dump("accountingchild: " + JSON.stringify(pconfig) + "\n");
-  var daterangesql = "select case when strftime('%Y',date(yeardaybegin)) == '"+pconfig.accyear+"' then yeardaybegin else strftime('%Y-%m-%d',date(date(yeardaybegin),'+7 days')) end as yeardaybegin,"+
-  "strftime('%Y-%m-%d',date(date(yeardayend),'+6 days')) as yeardayend "+
-  "from ( " +
-  "select case when strftime('%w','"+pconfig.accyear+"-01-01') = '1' then strftime('%Y-%m-%d','"+pconfig.accyear+"-01-01') else strftime('%Y-%m-%d', date(date('"+pconfig.accyear+"-01-01','-7 days'),'weekday 1')) end as yeardaybegin, "+
-  "case when strftime('%w','"+pconfig.accyear+"-12-31') = '1' then strftime('%Y-%m-%d','"+pconfig.accyear+"-12-31') else strftime('%Y-%m-%d', date(date('"+pconfig.accyear+"-12-31','-7 days'),'weekday 1')) end as yeardayend "+
-  ");";
-  var daterange = appdb.dbquery(daterangesql);
-  daterange = daterange.sqldata[0];
-  var yearmin = daterange.yeardaybegin;
-  var yearmax = daterange.yeardayend;
-  
-  
-  var chbornes = appdb.dbquery("select min(daydate) as mindate,max(daydate) as maxdate from presence where strftime('%Y',daydate)='" + pconfig.accyear + "' and childuuid='" + pconfig.accchild + "';");
-  
-  var monthmin= chbornes.sqldata[0].mindate;
-  var monthmax= chbornes.sqldata[0].maxdate;
-  var accsql = "select strftime( '%m.%Y',acc.accmonth) as calmonth,'' as weekdetails, '' as monthtotalhourcosts,'' as monthtotallunchcosts, '' as monthtotalcosts,replace(acc.reference,'NOCHECKSERVICE:','<span style=\"color: red;\">(facture non check-service)</span><br/>') 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 '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," +
-"'' as benefitamount," +
-"'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.accmonth || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.childuuid || ''',''' || acc.accmonth || ''',''' || acc.reference || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act  " +
-" from accounting acc where acc.childuuid='"+ pconfig.accchild +"' and strftime( '%Y',acc.accmonth)='"+pconfig.accyear+"' and acc.reference LIKE 'NOCHECKSERVICE:%' "
-  //|| ' (' || weekhourcosts || '€/h)' -- || ' (' || weeklunchcosts || '€/déj.)
-  accsql += "UNION SELECT calmonth, " +
-  "group_concat( '<span class=\"glyphicon glyphicon-calendar\"></span>:' || calweek || '&nbsp;<span class=\"glyphicon glyphicon-time\"></span>: ' || durationweek  || '&nbsp;h&nbsp;<span class=\"glyphicon glyphicon-cutlery\"></span>: ' || lunchweek ,'<br/>')   AS weekdetails, " +
-  "printf(\"%.2f\",SUM(weektotalhourcosts)) AS monthtotalhourcosts, " +
-  "printf(\"%.2f\",SUM(weektotallunchcosts)) AS monthtotallunchcosts, " +
-  "printf(\"%.2f\",SUM(weektotalhourcosts + weektotallunchcosts)) AS monthtotalcosts, " +
-  "reference, " +
-  "invoiced,payement,benefitamount,act FROM ( " +
-  "SELECT childname,ttl.childuuid,printf(\"%02d\",calweek) AS calweek,calmonth, " +
-  "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, " +
-  "REPLACE(acc.reference,'NOCHECKSERVICE:','') 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 '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || 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," +
-  "'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.accmonth || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.childuuid || ''',''' || acc.accmonth || ''',''' || acc.reference || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act  " +
-  "FROM ( SELECT pch.childname, pch.childuuid, px.calweek, px.calmonth, 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,'') || '<br>(' || " +
-  "COALESCE(ch.checkservicenumber,'') || ')' AS childname,ch.uuid AS childuuid,ch.checkservicenumber " +
-  "FROM childs ch JOIN planning pl ON ( ch.uuid=pl.childuuid) WHERE childuuid='"+pconfig.accchild+"' ) 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 ,calmonth, 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,"
-  +"calmonth, MAX(monlunch) + MAX(tuelunch) + MAX(wedlunch) + MAX(thulunch) + MAX(frilunch) 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 FROM " +
-  "( SELECT xx.childuuid, xx.daydate,strftime('%Y',xx.daydate) as calyear, " +
-  "case when strftime('%w',xx.daydate)='0' then strftime('%W',date(xx.daydate,'-6 days')) else strftime('%W',date(date(xx.daydate),'-' || strftime('%w',date(xx.daydate)) || ' days','+1 day')) end as calweek, " +
-  "case when strftime('%w',xx.daydate)='0' then strftime('%m.%Y',date(xx.daydate,'-6 days')) else strftime('%m.%Y',date(date(xx.daydate),'-' || strftime('%w',date(xx.daydate)) || ' days','+1 day')) end as calmonth, ";
-  var aax = ['mon','tue','wed','thu','fri','sat'];
-  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,";
-  }
+  // var daterangesql = "select case when strftime('%Y',date(yeardaybegin)) == '"+pconfig.accyear+"' then yeardaybegin else strftime('%Y-%m-%d',date(date(yeardaybegin),'+7 days')) end as yeardaybegin,"+
+  // "strftime('%Y-%m-%d',date(date(yeardayend),'+6 days')) as yeardayend "+
+  // "from ( " +
+  // "select case when strftime('%w','"+pconfig.accyear+"-01-01') = '1' then strftime('%Y-%m-%d','"+pconfig.accyear+"-01-01') else strftime('%Y-%m-%d', date(date('"+pconfig.accyear+"-01-01','-7 days'),'weekday 1')) end as yeardaybegin, "+
+  // "case when strftime('%w','"+pconfig.accyear+"-12-31') = '1' then strftime('%Y-%m-%d','"+pconfig.accyear+"-12-31') else strftime('%Y-%m-%d', date(date('"+pconfig.accyear+"-12-31','-7 days'),'weekday 1')) end as yeardayend "+
+  // ");";
+  // var daterange = appdb.dbquery(daterangesql);
+  // daterange = daterange.sqldata[0];
+  // var yearmin = daterange.yeardaybegin;
+  // var yearmax = daterange.yeardayend;
   
-  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 chbornes = appdb.dbquery("select min(daydate) as mindate,max(daydate) as maxdate from presence where strftime('%Y',daydate)='" + pconfig.accyear + "' and childuuid='" + pconfig.accchild + "';");
   
-  for (var d3 in aax){
-    var dw3 = d3;dw3++;
-    accsql += "CASE WHEN xx.status IS NOT NULL AND strftime('%w',xx.daydate) = '"+dw3+"' 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,";
-  }
+  // var monthmin= chbornes.sqldata[0].mindate;
+  // var monthmax= chbornes.sqldata[0].maxdate;
+  var accsql = "select strftime('%m.%Y', acc.accmonth) as calmonth," +
+  "case when nocheckservice = '1' then '<span style=\"color: red;\">(facture non check-service)</span><br/>' else '' end || acc.reference 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 '<br/>(' || strftime('%d.%m.%Y', acc.invoicedate) || ')' || " +
+   " CASE  WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y', acc.reminderdate1)  ELSE '' END || "+
+   " CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y', acc.reminderdate2)  ELSE ''  END || "+
+   "  CASE  WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || 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," +
+   " '<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || " +
+   " CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile || ''');\"  title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>' else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.uuid || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act " +
+  " from accounting acc where childuuid='"+ pconfig.accchild+"' and strftime('%Y',acc.accmonth) = '" + pconfig.accyear +"' order by accmonth;";
   
-  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 and pl.childuuid='"+pconfig.accchild+"') " + 
-  " 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 and pr.childuuid='"+pconfig.accchild+"') WHERE pr.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"' "+
-  ") xx " +
-  "WHERE xx.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"') GROUP BY calweek) GROUP BY calweek) px " +
-  "ON ( pch.childuuid=px.childuuid) GROUP BY px.calweek ) ttl LEFT JOIN accounting acc ON (ttl.childuuid = acc.childuuid AND  ttl.calmonth=strftime('%m.%Y',acc.accmonth) AND acc.reference NOT LIKE 'NOCHECKSERVICE:%') WHERE calweek IS NOT NULL) group by calmonth ORDER BY calmonth; ";
-   
- 
+//   var accsql = "select strftime( '%m.%Y',acc.accmonth) as calmonth,'' as weekdetails, '' as monthtotalhourcosts,'' as monthtotallunchcosts, '' as monthtotalcosts,replace(acc.reference,'NOCHECKSERVICE:','<span style=\"color: red;\">(facture non check-service)</span><br/>') 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 '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," +
+// "'' as benefitamount," +
+// "'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.uuid || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act  " +
+// " from accounting acc where acc.childuuid='"+ pconfig.accchild +"' and strftime( '%Y',acc.accmonth)='"+pconfig.accyear+"' and acc.reference LIKE 'NOCHECKSERVICE:%' "
+//   //|| ' (' || weekhourcosts || '€/h)' -- || ' (' || weeklunchcosts || '€/déj.)
+//   accsql += "UNION SELECT calmonth, " +
+//   "group_concat( '<span class=\"glyphicon glyphicon-calendar\"></span>:' || calweek || '&nbsp;<span class=\"glyphicon glyphicon-time\"></span>: ' || durationweek  || '&nbsp;h&nbsp;<span class=\"glyphicon glyphicon-cutlery\"></span>: ' || lunchweek ,'<br/>')   AS weekdetails, " +
+//   "printf(\"%.2f\",SUM(weektotalhourcosts)) AS monthtotalhourcosts, " +
+//   "printf(\"%.2f\",SUM(weektotallunchcosts)) AS monthtotallunchcosts, " +
+//   "printf(\"%.2f\",SUM(weektotalhourcosts + weektotallunchcosts)) AS monthtotalcosts, " +
+//   "reference, " +
+//   "invoiced,payement,benefitamount,act FROM ( " +
+//   "SELECT childname,ttl.childuuid,printf(\"%02d\",calweek) AS calweek,calmonth, " +
+//   "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, " +
+//   "REPLACE(acc.reference,'NOCHECKSERVICE:','') 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 '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || 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," +
+//   "'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.uuid || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act  " +
+//   "FROM ( SELECT pch.childname, pch.childuuid, px.calweek, px.calmonth, 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,'') || '<br>(' || " +
+//   "COALESCE(ch.checkservicenumber,'') || ')' AS childname,ch.uuid AS childuuid,ch.checkservicenumber " +
+//   "FROM childs ch JOIN planning pl ON ( ch.uuid=pl.childuuid) WHERE childuuid='"+pconfig.accchild+"' ) 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 ,calmonth, 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,"
+//   +"calmonth, MAX(monlunch) + MAX(tuelunch) + MAX(wedlunch) + MAX(thulunch) + MAX(frilunch) 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 FROM " +
+//   "( SELECT xx.childuuid, xx.daydate,strftime('%Y',xx.daydate) as calyear, " +
+//   "case when strftime('%w',xx.daydate)='0' then strftime('%W',date(xx.daydate,'-6 days')) else strftime('%W',date(date(xx.daydate),'-' || strftime('%w',date(xx.daydate)) || ' days','+1 day')) end as calweek, " +
+//   "case when strftime('%w',xx.daydate)='0' then strftime('%m.%Y',date(xx.daydate,'-6 days')) else strftime('%m.%Y',date(date(xx.daydate),'-' || strftime('%w',date(xx.daydate)) || ' days','+1 day')) end as calmonth, ";
+//   var aax = ['mon','tue','wed','thu','fri','sat'];
+//   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) = '"+dw3+"' 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 and pl.childuuid='"+pconfig.accchild+"') " + 
+//   " 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 and pr.childuuid='"+pconfig.accchild+"') WHERE pr.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"' "+
+//   ") xx " +
+//   "WHERE xx.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"') GROUP BY calweek) GROUP BY calweek) px " +
+//   "ON ( pch.childuuid=px.childuuid) GROUP BY px.calweek ) ttl LEFT JOIN accounting acc ON (ttl.childuuid = acc.childuuid AND  ttl.calmonth=strftime('%m.%Y',acc.accmonth) AND acc.reference NOT LIKE 'NOCHECKSERVICE:%') WHERE calweek IS NOT NULL) group by calmonth ORDER BY calmonth; ";var accsql = "select strftime( '%m.%Y',acc.accmonth) as calmonth,'' as weekdetails, '' as monthtotalhourcosts,'' as monthtotallunchcosts, '' as monthtotalcosts,replace(acc.reference,'NOCHECKSERVICE:','<span style=\"color: red;\">(facture non check-service)</span><br/>') 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 '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || strftime('%d.%m.%Y',acc.payeddate) || ')' else '' end else '' end as payement," +
+// "'' as benefitamount," +
+// "'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.uuid || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act  " +
+// " from accounting acc where acc.childuuid='"+ pconfig.accchild +"' and strftime( '%Y',acc.accmonth)='"+pconfig.accyear+"' and acc.reference LIKE 'NOCHECKSERVICE:%' "
+//   //|| ' (' || weekhourcosts || '€/h)' -- || ' (' || weeklunchcosts || '€/déj.)
+//   accsql += "UNION SELECT calmonth, " +
+//   "group_concat( '<span class=\"glyphicon glyphicon-calendar\"></span>:' || calweek || '&nbsp;<span class=\"glyphicon glyphicon-time\"></span>: ' || durationweek  || '&nbsp;h&nbsp;<span class=\"glyphicon glyphicon-cutlery\"></span>: ' || lunchweek ,'<br/>')   AS weekdetails, " +
+//   "printf(\"%.2f\",SUM(weektotalhourcosts)) AS monthtotalhourcosts, " +
+//   "printf(\"%.2f\",SUM(weektotallunchcosts)) AS monthtotallunchcosts, " +
+//   "printf(\"%.2f\",SUM(weektotalhourcosts + weektotallunchcosts)) AS monthtotalcosts, " +
+//   "reference, " +
+//   "invoiced,payement,benefitamount,act FROM ( " +
+//   "SELECT childname,ttl.childuuid,printf(\"%02d\",calweek) AS calweek,calmonth, " +
+//   "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, " +
+//   "REPLACE(acc.reference,'NOCHECKSERVICE:','') 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 '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || 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," +
+//   "'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || CASE WHEN acc.invoicefile IS NOT NULL and acc.invoicefile != '' THEN '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile ||  ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span>'  else '' end || CASE WHEN acc.childuuid IS NOT NULL AND acc.accmonth IS NOT NULL THEN '<button type=\"button\" class=\"btn btn-danger\" onclick=\" confirm_delete_accounting(''' || acc.uuid || ''');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>' ELSE '' END AS act  " +
+//   "FROM ( SELECT pch.childname, pch.childuuid, px.calweek, px.calmonth, 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,'') || '<br>(' || " +
+//   "COALESCE(ch.checkservicenumber,'') || ')' AS childname,ch.uuid AS childuuid,ch.checkservicenumber " +
+//   "FROM childs ch JOIN planning pl ON ( ch.uuid=pl.childuuid) WHERE childuuid='"+pconfig.accchild+"' ) 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 ,calmonth, 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,"
+//   +"calmonth, MAX(monlunch) + MAX(tuelunch) + MAX(wedlunch) + MAX(thulunch) + MAX(frilunch) 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 FROM " +
+//   "( SELECT xx.childuuid, xx.daydate,strftime('%Y',xx.daydate) as calyear, " +
+//   "case when strftime('%w',xx.daydate)='0' then strftime('%W',date(xx.daydate,'-6 days')) else strftime('%W',date(date(xx.daydate),'-' || strftime('%w',date(xx.daydate)) || ' days','+1 day')) end as calweek, " +
+//   "case when strftime('%w',xx.daydate)='0' then strftime('%m.%Y',date(xx.daydate,'-6 days')) else strftime('%m.%Y',date(date(xx.daydate),'-' || strftime('%w',date(xx.daydate)) || ' days','+1 day')) end as calmonth, ";
+//   var aax = ['mon','tue','wed','thu','fri','sat'];
+//   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) = '"+dw3+"' 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 and pl.childuuid='"+pconfig.accchild+"') " + 
+//   " 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 and pr.childuuid='"+pconfig.accchild+"') WHERE pr.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"' "+
+//   ") xx " +
+//   "WHERE xx.daydate BETWEEN '"+monthmin+"' AND '"+monthmax+"') GROUP BY calweek) GROUP BY calweek) px " +
+//   "ON ( pch.childuuid=px.childuuid) GROUP BY px.calweek ) ttl LEFT JOIN accounting acc ON (ttl.childuuid = acc.childuuid AND  ttl.calmonth=strftime('%m.%Y',acc.accmonth) AND acc.reference NOT LIKE 'NOCHECKSERVICE:%') WHERE calweek IS NOT NULL) group by calmonth ORDER BY calmonth; ";
+   jsdump("accsql");
+ jsdump(accsql);
   
   var accdata = appdb.dbquery(accsql);
   //jsdump("accchildsql");
   //jsdump(accsql);
-  var headerdata = getlabels("calmonth,weekdetails,reference,invoiced,payement,benefitamount,act");
+  var headerdata = getlabels("calmonth,reference,invoiced,payement,benefitamount,act");
  //,monthtotalhourcosts,monthtotallunchcosts,monthtotalcosts
   
 
@@ -178,7 +259,7 @@ function load_accounting_table(){
   for (var i in accdata.sqldata){
     var row = accdata.sqldata[i];
         var tr = '<tr><td>' + row.calmonth + '</td>';
-        tr += '<td class="infoweek noprint '+ (((pconfig.hidden_columns) && (pconfig.hidden_columns.indexOf('weekdetails') != -1))?" colhidden":"") +'">' + row.weekdetails + '</td>';
+        //tr += '<td class="infoweek noprint '+ (((pconfig.hidden_columns) && (pconfig.hidden_columns.indexOf('weekdetails') != -1))?" colhidden":"") +'">' + row.weekdetails + '</td>';
         //tr += '<td class="alignright totalwidth '+ (((pconfig.hidden_columns) && (pconfig.hidden_columns.indexOf('monthtotalhourcosts') != -1))?" colhidden":"") +'">' + row.monthtotalhourcosts + '€</td>';
         //tr += '<td class="alignright totalwidth '+ (((pconfig.hidden_columns) && (pconfig.hidden_columns.indexOf('monthtotallunchcosts') != -1))?" colhidden":"") +'">' + row.monthtotallunchcosts + '€</td>';
         //tr += '<td class="alignright totalwidth '+ (((pconfig.hidden_columns) && (pconfig.hidden_columns.indexOf('monthtotalcosts') != -1))?" colhidden":"") +'">' + row.monthtotalcosts + '€</td>';
@@ -229,19 +310,19 @@ function load_accounting_table(){
             //        return intVal(a) + intVal(b);
             //    }, 0 );
             fTotal =  api
-                .column( 3, { page: 'current'} )
+                .column( 2, { page: 'current'} )
                 .data()
                 .reduce( function (a, b) {
                     return intVal(a) + intVal(b);
                 }, 0 );
             pTotal =  api
-                .column( 4, { page: 'current'} )
+                .column( 3, { page: 'current'} )
                 .data()
                 .reduce( function (a, b) {
                     return intVal(a) + intVal(b);
                 }, 0 );
             bTotal =  api
-                .column( 5, { page: 'current'} )
+                .column( 4, { page: 'current'} )
                 .data()
                 .reduce( function (a, b) {
                     return intVal(a) + intVal(b);
@@ -254,13 +335,13 @@ function load_accounting_table(){
             //    }, 0 );
  
             // Update footer
-            $( api.column( 3 ).footer() ).html(
+            $( api.column( 2 ).footer() ).html(
                 ''+fTotal.toFixed(2)  +' €' 
             );
-            $( api.column( 4 ).footer() ).html(
+            $( api.column( 3 ).footer() ).html(
                 ''+pTotal.toFixed(2)  +' €' 
             );
-            $( api.column( 5 ).footer() ).html(
+            $( api.column( 4 ).footer() ).html(
                 ''+bTotal.toFixed(2) +' €' 
             );
             //$( api.column( 4 ).footer() ).html(
@@ -327,21 +408,22 @@ function load_childlist() {
 }
 
 
-function dlg_accounting(accmonth){
+function dlg_accounting(myuuid){
   $("#frmeditaccounting #action").val("upd");
   $("#head_invoice").html("Editer facture");
   $("#frmeditaccounting #childname").val($("#accchild  option:selected").text());
-  var acc1sql = "SELECT accmonth,childuuid,case when invoicedate is not null then strftime('%d.%m.%Y',invoicedate) else null end as invoicedate, case when payeddate is not null then strftime('%d.%m.%Y',payeddate) else null end as payeddate, payedamount, benefitamount,invoiceamount, replace(reference,'NOCHECKSERVICE:','') as reference, instr(reference,'NOCHECKSERVICE:') as ischeckservice, case when reminderdate1 is not null then strftime('%d.%m.%Y',reminderdate1) else null end as reminderdate1, case when reminderdate2 is not null then strftime('%d.%m.%Y',reminderdate2) else null end as reminderdate2, case when reminderdate3 is not null then strftime('%d.%m.%Y',reminderdate3) else null end as reminderdate3,case when invoicefile is not null  and invoicefile != '' then '"+ importpath.path + system.sep() +"' || invoicefile else null end as invoicefile,'upd' as action FROM accounting where accmonth = '" + accmonth +"' and childuuid='"+pconfig.accchild+"';";
+  
+  var acc1sql = "SELECT uuid,accmonth,childuuid,case when invoicedate is not null then strftime('%d.%m.%Y',invoicedate) else null end as invoicedate, case when payeddate is not null then strftime('%d.%m.%Y',payeddate) else null end as payeddate, payedamount, benefitamount,invoiceamount, reference, nocheckservice, case when reminderdate1 is not null then strftime('%d.%m.%Y',reminderdate1) else null end as reminderdate1, case when reminderdate2 is not null then strftime('%d.%m.%Y',reminderdate2) else null end as reminderdate2, case when reminderdate3 is not null then strftime('%d.%m.%Y',reminderdate3) else null end as reminderdate3,case when invoicefile is not null  and invoicefile != '' then '"+ importpath.path + system.sep() +"' || invoicefile else null end as invoicefile,'upd' as action FROM accounting where uuid='" + myuuid +"';";
   
   var acc1data = appdb.dbquery(acc1sql);
   
   if (acc1data.sqldata.length == 0) {
  
-    acc1sql = "SELECT '"+ accmonth.substring(3) + "-" + accmonth.substring(0,2) +"-01' as accmonth,'"+ pconfig.accchild+"' as childuuid, null as invoicedate, null as payeddate, null as payedamount, null as invoiceamount,1 as ischeckservice, null as reference, null as reminderdate1, null as reminderdate2, null as reminderdate3,null as benefitamount ,null as invoicefile, 'ins' as action;";
+    acc1sql = "SELECT '0' as uuid,"+ accmonth.substring(3) + "-" + accmonth.substring(0,2) +"-01' as accmonth,'"+ pconfig.accchild+"' as childuuid, null as invoicedate, null as payeddate, null as payedamount, null as invoiceamount,1 as ischeckservice, null as reference, null as reminderdate1, null as reminderdate2, null as reminderdate3,null as benefitamount ,null as invoicefile, 'ins' as action;";
     acc1data = appdb.dbquery(acc1sql);
   }
   acc1data = acc1data.sqldata[0];
-  if (acc1data.ischeckservice > 0){
+  if (acc1data.nocheckservice == 1){
     $("#frmeditaccounting #nocheckservice").prop("checked",true);
   } else {
     $("#frmeditaccounting #nocheckservice").prop("checked",false);
@@ -358,13 +440,21 @@ function dlg_accounting(accmonth){
 }
 
 function newinvoice(){
-  $("#frmeditaccounting #nocheckservice").prop("checked",true);
-  $("#frmeditaccounting #action").val("ins");
-  $("#head_invoice").html("Ajouter une facture");
   if ($("#accchild").val() == ""){
     alert("Vous devez d'abort selectionner un enfant!");
     return;
   }
+  $("#frmeditaccounting :input").each(function(){
+    var input = $(this);
+    input.val("");
+  });
+  $("#frmeditaccounting #nocheckservice").prop("checked",true);
+  $("#frmeditaccounting #action").val("ins");
+  $("#frmeditaccounting #uuid").val("0");
+  $("#frmeditaccounting #childuuid").val($("#accchild").val());
+  $("#head_invoice").html("Ajouter une facture");
+
+  
   $("#frmeditaccounting #childname").val($("#accchild  option:selected").text());
   $("#edit_accounting").modal('show');
 }
@@ -383,7 +473,8 @@ function save_accounting(){
         $("#frmeditaccounting :input").each(function(){
           var input = $(this);
           if (input.attr("id") ){
-            if ((type == 'ins') && (input.attr("id") != 'action') && (input.attr("id") != 'accmonth') && (input.attr("id") != 'childname')  && (input.attr("id") != 'ischeckservice')&& (input.attr("id") != 'oldref') && (input.attr("id") != 'oldaccmonth')) {
+            if ((type == 'ins') && (input.attr("type") != 'hidden') && (input.attr("id") != 'accmonth') && (input.attr("id") != 'childname')  && (input.attr("id") != 'ischeckservice')) {
+              
               sql1.push('"' + input.attr("id") + '"');
               if (input.attr("type") == "file") {
                 var newlocation= importpath.path + system.sep() + system.getFileName(input.val());
@@ -394,7 +485,8 @@ function save_accounting(){
               }
               var ival = input.val();
               if ((input.attr("id") == 'reference') && $("#frmeditaccounting #ischeckservice").is(":checked")){
-                ival = "NOCHECKSERVICE:" + ival;
+                sql1.push("nocheckservice");
+                sql2.push("'1'");
               }
               var ival = encodeparam(input.val(),input.attr('type'));
               sql2.push(ival);
@@ -417,7 +509,7 @@ function save_accounting(){
                 }
                 var ival = input.val();
                 if ((input.attr("id") == 'reference') && $("#frmeditaccounting #ischeckservice").is(":checked")){
-                  ival = "NOCHECKSERVICE:" + ival;
+                  sql2.push("nocheckservice='1'");
                 }
                 var ival = encodeparam(ival,input.attr('type'));
                 sql2.push('"' + input.attr("id") +'"='+ ival);
@@ -436,8 +528,10 @@ function save_accounting(){
         });
         var xsql = "";
         if (type == 'upd') {
-          xsql = "UPDATE accounting SET "+sql2.join(',')+" where \"childuuid\"='" + pconfig.accchild +"' and accmonth='"+ $("#frmeditaccounting #oldaccmonth").val() +"' and reference='"+$("#frmeditaccounting #oldref").val()+"';";
+          xsql = "UPDATE accounting SET "+sql2.join(',')+" where \"uuid\"='" + $("#frmeditaccounting #uuid").val() +"';";
         }else {
+          sql1.push("uuid");
+          sql2.push("'" + appdb.generate_uuid() + "'");
           xsql = "INSERT INTO accounting ("+sql1.join(',')+") VALUES ("+sql2.join(',')+");"; 
         }
         //jsdump(xsql);
@@ -481,15 +575,13 @@ function set_selectyears() {
   
 }
 
-function confirm_delete_accounting(dchilduuid,daccmonth,dref) {
-  delacc.childuuid = dchilduuid;
-  delacc.accmonth=daccmonth;
-  delacc.accref=dref;
+function confirm_delete_accounting(myuuid) {
+  delacc.uuid = uuid;
   $('#confirm_delete_accounting').modal('show');
 }
 
 function delete_accounting() {
-  var xsql = "DELETE FROM accounting where childuuid='"+ delacc.childuuid+"' and accmonth='"+delacc.accmonth+" and reference='"+delacc.accref+"';";
+  var xsql = "DELETE FROM accounting where uuid='"+ delacc.uuid+"';";
 
   appdb.dbexec(xsql);
   load_accounting_table();
@@ -528,14 +620,14 @@ function onhidecolumn(coltohide) {
   //dump("Hide column " + coltohide + "\n");
 }
 
-function check_accountingtable(){
-  var cols = appdb.dbquery("SELECT type, name,tbl_name,sql FROM sqlite_master where tbl_name='accounting';");
-  //jsdump(JSON.stringify(cols));
-  if (cols.sqldata[0].sql.indexOf("nocheckservice") == -1){
-    //jsdump("Install missing column! ALTER TABLE accounting add column nocheckservice integer");
-    appdb.dbexec("ALTER TABLE accounting add column nocheckservice integer;");
-  }
-  // else {
-  //   jsdump("missing column found!");
-  // }
-}
+// function check_accountingtable(){
+//   var cols = appdb.dbquery("SELECT type, name,tbl_name,sql FROM sqlite_master where tbl_name='accounting';");
+//   //jsdump(JSON.stringify(cols));
+//   if (cols.sqldata[0].sql.indexOf("nocheckservice") == -1){
+//     //jsdump("Install missing column! ALTER TABLE accounting add column nocheckservice integer");
+//     appdb.dbexec("ALTER TABLE accounting add column nocheckservice integer;");
+//   }
+//   // else {
+//   //   jsdump("missing column found!");
+//   // }
+// }
diff --git a/app_v3/chrome/content/modules/accounting/js/accountingopeninvoice.js b/app_v3/chrome/content/modules/accounting/js/accountingopeninvoice.js
index 68a502a..e9af6e3 100644
--- a/app_v3/chrome/content/modules/accounting/js/accountingopeninvoice.js
+++ b/app_v3/chrome/content/modules/accounting/js/accountingopeninvoice.js
@@ -31,17 +31,17 @@ function initdata(){
 function load_accounting_table(){
   var xsql = "DELETE from accounting where accmonth='' or accmonth is null;";
   appdb.dbexec(xsql);
-  var accsql = "select strftime('%m.%Y',acc.accmonth) as calmonth, "  +
-"COALESCE(ch.prename,'') || ' ' || COALESCE(ch.surname,'') || '<br>(' || COALESCE(ch.checkservicenumber,'') || ')' AS childname, acc.reference as reference, "  +
+  var accsql = "select strftime('%m.%Y',acc.accmonth) as calmonth,ch.clientnumber, "  +
+"COALESCE(ch.prename,'') || ' ' || COALESCE(ch.surname,'')  AS childname,ch.checkservicenumber, acc.reference 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 '<br/>(' || strftime('%d.%m.%Y',acc.invoicedate) || ')' || CASE WHEN acc.reminderdate1 IS NOT NULL THEN '<br/>R1:' || strftime('%d.%m.%Y',acc.reminderdate1) ELSE '' END || CASE WHEN acc.reminderdate2 IS NOT NULL THEN '<br/>R2:' || strftime('%d.%m.%Y',acc.reminderdate2) ELSE '' END || CASE WHEN acc.reminderdate3 IS NOT NULL THEN '<br/>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 '<br/>(' || strftime('%d.%m.%Y',acc.payeddate) || ')' ELSE '' END ELSE '' END AS payement, "  +
-"'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' || strftime(\"%m.%Y\",acc.accmonth) || ''',''' || ch.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || case when acc.invoicefile is not null and acc.invoicefile != '' then '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span></button>' else '' end AS act "  +
+"'<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_accounting(''' ||  acc.uuid || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-pencil\"></span></button>' || case when acc.invoicefile is not null and acc.invoicefile != '' then '<button type=\"button\" class=\"btn btn-primary\" onclick=\"openfile(''' || acc.invoicefile || ''');\" title=\"editer\"><span class=\"glyphicon glyphicon-open\"></span></button>' else '' end AS act "  +
 "from accounting acc join childs ch on (acc.childuuid=ch.uuid) "  +
 "where (acc.invoiceamount is not null and acc.payedamount is null) or (acc.payedamount < acc.invoiceamount) order by acc.accmonth desc;";
 
   
   var accdata = appdb.dbquery(accsql);
-  var headerdata = getlabels("calmonth,childname,reference,invoiced,payement,act");
+  var headerdata = getlabels("calmonth,clientnumber,childname,checkservicenumber,reference,invoiced,payement,act");
   
 
   $("#tbl_accounting").html("");
@@ -70,7 +70,9 @@ function load_accounting_table(){
   for (var i in accdata.sqldata){
     var row = accdata.sqldata[i];
         var tr = '<tr><td>' + row.calmonth + '</td>';
+        tr += '<td>' + row.clientnumber + '</td>';
         tr += '<td>' + row.childname + '</td>';
+        tr += '<td>' + row.checkservicenumber + '</td>';
         tr += '<td>' + row.reference + '</td>';
         tr += '<td class="alignright">' + row.invoiced + '</td>';
         tr += '<td class="alignright">' + row.payement + '</td>';
@@ -102,23 +104,23 @@ function load_accounting_table(){
             };
             
             fTotal =  api
-                .column( 2, { page: 'current'} )
+                .column( 5, { page: 'current'} )
                 .data()
                 .reduce( function (a, b) {
                     return intVal(a) + intVal(b);
                 }, 0 );
             pTotal =  api
-                .column( 3, { page: 'current'} )
+                .column( 6, { page: 'current'} )
                 .data()
                 .reduce( function (a, b) {
                     return intVal(a) + intVal(b);
                 }, 0 );
   
             // Update footer
-            $( api.column( 2 ).footer() ).html(
+            $( api.column( 5 ).footer() ).html(
                 ''+fTotal.toFixed(2)  +' €' 
             );
-            $( api.column( 3 ).footer() ).html(
+            $( api.column( 6 ).footer() ).html(
                 ''+pTotal.toFixed(2)  +' €' 
             );
             
@@ -157,22 +159,29 @@ $('#tbl_accounting tbody').on( 'click', 'tr', function () {
 }
 
 
-function dlg_accounting(accmonth,paccchild){
-
-  gpaccchild=paccchild;
-  var acc1sql = "SELECT accmonth,childuuid,case when invoicedate is not null then strftime('%d.%m.%Y',invoicedate) else null end as invoicedate, case when payeddate is not null then strftime('%d.%m.%Y',payeddate) else null end as payeddate, payedamount,benefitamount, invoiceamount, reference, case when reminderdate1 is not null then strftime('%d.%m.%Y',reminderdate1) else null end as reminderdate1, case when reminderdate2 is not null then strftime('%d.%m.%Y',reminderdate2) else null end as reminderdate2, case when reminderdate3 is not null then strftime('%d.%m.%Y',reminderdate3) else null end as reminderdate3,case when invoicefile is not null and invoicefile != '' then '"+ importpath.path + system.sep() +"' || invoicefile else null end as invoicefile,'upd' as action FROM accounting where accmonth = '"+ accmonth.substring(3) + "-" + accmonth.substring(0,2) +"-01' and childuuid='"+paccchild+"';";
+function dlg_accounting(myuuid){
 
+  
+  var acc1sql = "SELECT acc.uuid as uuid,acc.accmonth,ch.prename || ' ' || ch.surname as childname,acc.nocheckservice,acc.childuuid,case when acc.invoicedate is not null then strftime('%d.%m.%Y',acc.invoicedate) else null end as invoicedate, case when acc.payeddate is not null then strftime('%d.%m.%Y',acc.payeddate) else null end as payeddate, acc.payedamount,acc.benefitamount, acc.invoiceamount, acc.reference, case when acc.reminderdate1 is not null then strftime('%d.%m.%Y',acc.reminderdate1) else null end as reminderdate1, case when acc.reminderdate2 is not null then strftime('%d.%m.%Y',acc.reminderdate2) else null end as reminderdate2, case when acc.reminderdate3 is not null then strftime('%d.%m.%Y',acc.reminderdate3) else null end as reminderdate3,case when acc.invoicefile is not null and acc.invoicefile != '' then '"+ importpath.path + system.sep() +"' || acc.invoicefile else null end as invoicefile,'upd' as action FROM accounting acc join childs ch on (acc.childuuid=ch.uuid) where acc.uuid='"+myuuid+"';";
+  
+  jsdump("acc1sql");
+  jsdump(acc1sql);
   var acc1data = appdb.dbquery(acc1sql);
  
-  if (acc1data.sqldata.length == 0) {
+  if (!acc1data.sqldata) {
  
-    acc1sql = "SELECT '"+ accmonth.substring(3) + "-" + accmonth.substring(0,2) +"-01' as accmonth,'"+ paccchild+"' as childuuid, null as invoicedate, null as payeddate, null as payedamount,null as benefitamount, null as invoiceamount, null as reference, null as reminderdate1, null as reminderdate2, null as reminderdate3, null as invoicefile, 'ins' as action;";
-    acc1data = appdb.dbquery(acc1sql);
+    return false;
   }
   acc1data = acc1data.sqldata[0];
   
   for (var a in acc1data){
-
+    if (a == "nocheckservice"){
+      if (acc1data[a] == 1){
+        $("#frmeditaccounting #nocheckservice").prop("checked",true);
+      } else {
+        $("#frmeditaccounting #nocheckservice").prop("checked",false);
+      }
+    }
     if ($("#frmeditaccounting #" +a)) {
 
       $("#frmeditaccounting #"+a).val(acc1data[a]);
@@ -183,51 +192,86 @@ function dlg_accounting(accmonth,paccchild){
 }
 
 function save_accounting(){
-        var sql1 = new Array();
-        var sql2 = new Array();
-
-        var type = $("#frmeditaccounting #action").val();
-        if (type == 'ins'){
-          sql1.push('childuuid');
-          sql2.push("'"+ gpaccchild +"'");
+  var sql1 = new Array();
+  var sql2 = new Array();
+  // var cntnum = "01"; 
+  var type = $("#frmeditaccounting #action").val();
+  if (type == 'ins'){
+    sql1.push('childuuid');
+    sql2.push("'"+ pconfig.accchild +"'");
+    
+  }
+  
+  $("#frmeditaccounting :input").each(function(){
+    var input = $(this);
+    if (input.attr("id") ){
+      if ((type == 'ins') && (input.attr("type") != 'hidden') && (input.attr("id") != 'accmonth') && (input.attr("id") != 'childname')  && (input.attr("id") != 'ischeckservice')) {
+        
+        sql1.push('"' + input.attr("id") + '"');
+        if (input.attr("type") == "file") {
+          var newlocation= importpath.path + system.sep() + system.getFileName(input.val());
+          if (!system.fileexists(importpath.path)){
+            importpath.create(1,0755);
+          }
+          OS.File.copy(input.val(), newlocation);
         }
-        $("#frmeditaccounting :input").each(function(){
-          var input = $(this);
-          if (input.attr("id") ){
-            if ((type == 'ins') && (input.attr("id") != 'action')) {
-              sql1.push('"' + input.attr("id") + '"');
-              if (input.attr("type") == "file") {
-                var newlocation= importpath.path + system.sep() + system.getFileName(input.val());
-                OS.File.copy(input.val(), newlocation);
-              }
-              var ival = encodeparam(input.val(),input.attr('type'));
-              sql2.push(ival);
-            }else {
-              if ((input.attr("id") != 'childuuid') && (input.attr("id") != 'action')) {
-                if (input.attr("type") == "file") {
-                  if (system.getDirectory(input.val()) != importpath.path) {
-                    var newlocation= importpath.path + system.sep() + system.getFileName(input.val());
-                    OS.File.copy(input.val(), newlocation);
-                  }
-                }
-                var ival = encodeparam(input.val(),input.attr('type'));
-                sql2.push('"' + input.attr("id") +'"='+ ival);
-              }
+        var ival = input.val();
+        if ((input.attr("id") == 'reference') && $("#frmeditaccounting #ischeckservice").is(":checked")){
+          sql1.push("nocheckservice");
+          sql2.push("'1'");
+        }
+        var ival = encodeparam(input.val(),input.attr('type'));
+        sql2.push(ival);
+        if (input.attr("id") == 'invoicedate'){
+          sql1.push('"accmonth"');
+          
+          var newaccmonth = ival.replace(/'/g,"");
+          // var cntx = appdb.dbquery("select count(accmonth)+1 as cnt from accounting where childuuid = '"+ pconfig.accchild +"' and accmonth between date('"+newaccmonth.substring(0,4) + "-" + newaccmonth.substring(5,7)+"-01') and date('"+newaccmonth.substring(0,4) + "-" + newaccmonth.substring(5,7)+"-01','+1 month','-1 day'); ");
+          // cntnum = cntx.sqldata[0].cntx;
+          // if (cntnum < 10){ cntnum = "0" + cntnum;}
+          sql2.push('\'' + newaccmonth.substring(0,4) + "-" + newaccmonth.substring(5,7) +"-01'");
+        }
+      }else {
+        if ((input.attr("type") != 'hidden') &&  (input.attr("id") != 'childname')  && (input.attr("id") != 'ischeckservice')) {
+          if (input.attr("type") == "file") {
+            if (system.getDirectory(input.val()) != importpath.path) {
+              var newlocation= importpath.path + system.sep() + system.getFileName(input.val());
+              OS.File.copy(input.val(), newlocation);
             }
           }
-
-        });
-        var xsql = "";
-        if (type == 'upd') {
-          xsql = "UPDATE accounting SET "+sql2.join(',')+" where \"childuuid\"='" + gpaccchild +"' and accmonth='"+ $("#frmeditaccounting #accmonth").val() +"';";
-        }else {
-          xsql = "INSERT INTO accounting ("+sql1.join(',')+") VALUES ("+sql2.join(',')+");"; 
+          var ival = input.val();
+          if ((input.attr("id") == 'reference') && $("#frmeditaccounting #ischeckservice").is(":checked")){
+            sql2.push("nocheckservice='1'");
+          }
+          var ival = encodeparam(ival,input.attr('type'));
+          sql2.push('"' + input.attr("id") +'"='+ ival);
+          if (input.attr("id") == 'invoicedate'){
+            var newaccmonth = ival.replace(/'/g,"");
+          //   var cntx = appdb.dbquery("select count(accmonth)+1 as cnt from accounting where childuuid = '"+ pconfig.accchild +"' and accmonth between date('"+newaccmonth.substring(0,4) + "-" + newaccmonth.substring(5,7)+"-01') and date('"+newaccmonth.substring(0,4) + "-" + newaccmonth.substring(5,7)+"-01','+1 month','-1 day'); ");
+          // cntnum = cntx.sqldata[0].cntx;
+          // if (cntnum < 10){ cntnum = "0" + cntnum;}
+            sql2.push('"accmonth"=\'' + newaccmonth.substring(0,4) + "-" + newaccmonth.substring(5,7) +"-01'");
+          }
+          
         }
-        appdb.dbexec(xsql);
-        load_accounting_table();
-        $("#frmeditaccounting #uuid").val("0");
-        $("#edit_accounting").modal('hide');
       }
+    }
+
+  });
+  var xsql = "";
+  if (type == 'upd') {
+    xsql = "UPDATE accounting SET "+sql2.join(',')+" where \"uuid\"='" + $("#frmeditaccounting #uuid").val() +"';";
+  }else {
+    sql1.push("uuid");
+    sql2.push("'" + appdb.generate_uuid() + "'");
+    xsql = "INSERT INTO accounting ("+sql1.join(',')+") VALUES ("+sql2.join(',')+");"; 
+  }
+  //jsdump(xsql);
+  appdb.dbexec(xsql);
+  load_accounting_table();
+  $("#frmeditaccounting #uuid").val("0");
+  $("#edit_accounting").modal('hide');
+}
 
 function print_list(){
   globelreq.send_request({page:'preview2',data:{page: 'accountingopeninvoice'}});
diff --git a/app_v3/chrome/content/modules/presences/js/dailypresences.js b/app_v3/chrome/content/modules/presences/js/dailypresences.js
index 9eb6f30..e2142f9 100644
--- a/app_v3/chrome/content/modules/presences/js/dailypresences.js
+++ b/app_v3/chrome/content/modules/presences/js/dailypresences.js
@@ -90,20 +90,20 @@ function initdata() {
       });
       set_infoheader("");
       cdate = initdate.toLocaleFormat('%Y-%m-%d');
-      
-      //get initdate weekday
-      var daygroupsql =  "SELECT ch.uuid as childuuid,gr.uuid as groupuuid, gr.grpname, grpname,ch.prename || ' ' || ch.surname AS childname,pr.begintime1 as begintime1,pr.endtime1 as endtime1,pr.begintime2 as begintime2,pr.endtime2 as endtime2,pr.status as status,pr.lunch as lunch, " +
-      wdays[weekday] +"timebegin as plantimebegin, "+wdays[weekday]+"timeend as plantimeend,"+
-      wdays[weekday] +"timebegin2 as plantimebegin2, "+wdays[weekday]+"timeend2 as plantimeend2,"+
-      wdays[weekday]+"lunch as planlunch ,case when pr.childuuid is not null then 'upd' else 'ins' end as ddltype " +
-      "  FROM  planning pl " +
-      "  LEFT JOIN childs ch ON  (pl.childuuid=ch.uuid) " +
-      "  LEFT JOIN groups gr ON (pl.groupuuid=gr.uuid) " +
-      "  LEFT JOIN presence pr ON (pr.childuuid=ch.uuid and pr.daydate='"+cdate+"') " +
-      "  WHERE pl.datebegin <= '"+cdate+"'  AND pl.dateend >= '"+cdate+"';";
-      
+      var daygroupsql = "SELECT ch.uuid as childuuid, ch.prename || ' ' || ch.surname AS childname, "+
+            "gr.uuid as groupuuid, gr.grpname as grpname, " +
+            "pr.begintime1 as begintime1, pr.endtime1 as endtime1, pr.begintime2 as begintime2, pr.endtime2 as endtime2, pr.status as status, pr.lunch as lunch, "+
+            wdays[weekday]+"timebegin as plantimebegin, "+wdays[weekday]+"timeend as plantimeend, "+
+            wdays[weekday]+"timebegin2 as plantimebegin2, "+wdays[weekday]+"timeend2 as plantimeend2, "+wdays[weekday]+"lunch as planlunch , "+
+            "case when pr.childuuid is not null then 'upd' else 'ins' end as ddltype "+
+            "from childs ch "+
+            "LEFT JOIN presence pr ON (pr.childuuid = ch.uuid and pr.daydate = date('"+cdate+"')) "+
+            "left join planning pl on (pl.childuuid = ch.uuid and date('"+cdate+"') between pl.datebegin and pl.dateend ) "+
+            "LEFT JOIN groups gr ON (pl.groupuuid = gr.uuid) "+
+            " where ch.entrydate <= date('"+cdate+"') and ch.leavedate >= date('"+cdate+"')";
        set_infoheader("Date du " + initdate.toLocaleFormat("%d.%m.%Y"));
-
+       //jsdump("DAY GROUP SQL");
+      //jsdump(daygroupsql);
       var dpresdata = appdb.dbquery(daygroupsql);
  
             var headerdata = getlabels("act,grpname,childname,effectiftime,status,plannedtime");
diff --git a/app_v3/chrome/content/modules/support/js/multisite.js b/app_v3/chrome/content/modules/support/js/multisite.js
index eebd49c..c69a88a 100644
--- a/app_v3/chrome/content/modules/support/js/multisite.js
+++ b/app_v3/chrome/content/modules/support/js/multisite.js
@@ -9,6 +9,7 @@ var maxsites = 1;
 var shared = false;
 var newrsite = false;
 var higestdbnum = 0;
+var checkdb = 0;
 function initdata(){
   //load_helpers();
   var pdata = apppref.getpreference("pageconfig.multisite");
@@ -29,14 +30,18 @@ function initdata(){
   }
   set_infoheader("");
   curcfg.loadconfig(apppref.getpreference("support.database.currentdb.num"));
+ 
   //$('#btncheckdb').hide();
-  /*if (system.os == "Darwin"){
-	  $('#btnvpnconfig').show();
-	  $('#btnvpnconnection').show();
+  if (system.os == "Darwin"){
+    if (system.fileexists(system.toolsdir() + system.sep() + "creorgadb")){
+      checkdb = 1;
+    }
   } else {
-	  
+	  if (system.fileexists(system.toolsdir() + system.sep() + "creorgadb.exe")){
+      checkdb = 1;
+    }
       
-  }*/
+  }
   //$('#btnsyncconfig').show();
   load_multisite_table();
   //$("#remoteshared").on('change',function(){
@@ -79,19 +84,19 @@ function load_multisite_table(){
         //var btnvpn = '';
 		var msgaccess = '';
         if (curcfg.dbnum != sitecfg.dbnum) {
-          btndel = "<button type=\"button\" class=\"btn btn-primary\" onclick=\"confirm_delete_multisite('"+sitecfg.dbnum +"');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>";  
+          btndel = "<button type=\"button\" class=\"btn btn-danger\" onclick=\"confirm_delete_multisite('"+sitecfg.dbnum +"');\" title=\"supprimer\"><span class=\"glyphicon glyphicon-remove\"></span></button>";  
         }
-        if (system.fileexists(sitecfg.path.local + system.sep() +sitecfg.uuid+".sqlite")){
+    if (system.fileexists(sitecfg.path.local + system.sep() +sitecfg.uuid+".sqlite")){
 			btnshare = "<button type=\"button\" class=\"btn btn-primary\" onclick=\"dlg_remotesite('"+sitecfg.dbnum +"');\" title=\"partager\"><span class=\"glyphicons glyphicons-folder-open\"></span></button>";
 		} else {
 			msgaccess = "<span style=\"color: red;\">cette base de donnée n'est pas accessible! Vérifiez l'access au répertoire (et/ou la VPN)</span>";
-		}
-//        if (sitecfg.vpncheck){
-//        	btnvpn = "<button type=\"button\" class=\"btn btn-warning\" onclick=\"run_vpncheck('"+sitecfg.vpncheck +"');\" title=\"check VPN\"><span class=\"glyphicons glyphicons-unlock\"></span></button>";
-//        }
-        
+    }
+    var msgcheckdb = "";
+    if (checkdb == 1){
+      var msgcheckdb = '<button onclick="dbcheck(\''+sitecfg.dbnum +'\');" class="btn btn-info" type="button" id="btncheckdb"><span class="glyphicons glyphicons-database" aria-hidden="true"></span></button>';
+    }
         
-        tbodydata += "<tr><td>"+btndel+btnshare+"</td><td>"+sitecfg.name+"</td><td><input type=\"text\" style=\"width: 90%;\" readonly=\"true\" value=\""+ sitecfg.path.local + system.sep() +sitecfg.uuid+".sqlite\"/>"+
+        tbodydata += "<tr><td>"+btndel+btnshare+msgcheckdb+"</td><td>"+sitecfg.name+"</td><td><input type=\"text\" style=\"width: 90%;\" readonly=\"true\" value=\""+ sitecfg.path.local + system.sep() +sitecfg.uuid+".sqlite\"/>"+
         ((msgaccess != "")?'<br/>' + msgaccess:"")
         +"</td>";
         tbodydata +="</tr>";
@@ -100,7 +105,13 @@ function load_multisite_table(){
     }
 		tbodydata += '</tbody>';
 		$("#tbl_multisite").append(tbodydata);
-    $("#btnnewsite").show();
+    if (system.appinfo().ID.indexOf("director") > 0){
+      $("#btnnewsite").show();  
+    } else {
+      $("#btnnewsite").hide();
+    }
+    
+    
     $("#btnremotesite").show();
 
   tblmultisite = $("#tbl_multisite").dataTable({
@@ -366,8 +377,9 @@ function remove_vpnconfig(){
           $('#btnvpnconnection').show();
      }
    
-     function dbcheck(){
-       appdb.check_schema();
+     function dbcheck(mydbid){
+       
+       appdb.check_schema(mydbid);
      } 
 
    
\ No newline at end of file
diff --git a/app_v3/chrome/content/modules/support/multisite.html b/app_v3/chrome/content/modules/support/multisite.html
index 5d38733..55d8ab4 100644
--- a/app_v3/chrome/content/modules/support/multisite.html
+++ b/app_v3/chrome/content/modules/support/multisite.html
@@ -30,11 +30,8 @@
    <button onclick="dlg_vpn();" class="btn btn-primary" type="button" id="btnvpnconfig" ><span class="glyphicons glyphicons-unlock" aria-hidden="true"></span>&nbsp; Configuration VPN</button>
 <!--    <button onclick="checkdatabases();" class="btn btn-info" type="button" id="btncheckdb" style="display: none;"><span class="glyphicon glyphicon-reload" aria-hidden="true"></span>&nbsp; verification DB</button> -->
    <button onclick="vpnconnection();" class="btn btn-warning" type="button" id="btnvpnconnection"><span class="glyphicons glyphicons-database-lock" aria-hidden="true"></span>&nbsp; Connecter VPN</button>
-   <button onclick="dbcheck();" class="btn btn-primary" type="button" id="btncheckdb"><span class="glyphicons glyphicons-database-lock" aria-hidden="true"></span>&nbsp; Verification base de données</button>
-   <!-- <button onclick="dlg_sync();" class="btn btn-primary" type="button" id="btnsyncconfig" style="display: none;"><span class="glyphicons glyphicons-reload" aria-hidden="true"></span>&nbsp; Configuration de Synchronisation</button>
-   <button onclick="syncstart();" class="btn btn-warning" type="button" id="btnsyncstart" style="display: none;"><span class="glyphicons glyphicons-upload" aria-hidden="true"></span>&nbsp; Activer Synchronisation</button>
-   <button onclick="syncstop();" class="btn btn-warning" type="button" id="btnsyncstop" style="display: none;"><span class="glyphicons glyphicons-download" aria-hidden="true"></span>&nbsp; Désactiver Synchronisation</button> -->
-      <table class="display dataTable" width="100%" style="width: 100%;" id="tbl_multisite" role="grid"></table>
+   
+   <table class="display dataTable" width="100%" style="width: 100%;" id="tbl_multisite" role="grid"></table>
 </div>
    <div class="modal fade" id="dlg_movesite">
           <div class="modal-dialog">
diff --git a/app_v3/defaults/profile/creorga.sqlite b/app_v3/defaults/profile/creorga.sqlite
index 6a8476ac4c9cf895d6ef3c00dd8384483091db89..e830b9e3c4b18da866a258c4ad7fd60497a645ea 100644
GIT binary patch
delta 285
zcmZqJz|ydTWrDPzH3I{KI}pQw^+X+GprD?@Oje+f5c4vSg!{$<D`rN|=GDyGS2Ht4
zatN?69AID)XG~!@z#Pud&Coqvgom-5jaiPt)Ln71;T4I=^U}l^UAG_LVN7IVw4AQQ
z&8W-6=EVlKq*2Y2eY!Lc<5C_j5T6AsD7#sZWhx^F;|_5}MaDMM$p=zQx8D<FRAS@f
z1<ErssxmN1F+OEf1u7F~<Z86_VV5>k<Y+RUzJrA^l9QK9!O7L#(~oz$1S_N6bbnq(
z0Un^3tDlRpH=8)ZN!+f@$zJRrE7}D?Rxt8TKKMcmWKkI-lMe4>K`BvYUM@`lUcEb4

delta 151
zcmZqJ!P2mSWrDPz1p@<v8xX^Q#Y7!rprD?@Oje+f5c4vSgzLrvD`rNI=GDyGS2Ht4
za)>iC9AID)XG{T7;><t5dYR=IR!)!RXI#4d01sm#6XT=lO5BXsMA*F8z|y=xl8JFX
k19Ldz{LO+alNq_1O}*H~6%`rVOeY^mG2MPokWq;Z0G16N`2YX_

diff --git a/install/create_setup_windows.pl b/install/create_setup_windows.pl
index e2f7b4b..4748910 100644
--- a/install/create_setup_windows.pl
+++ b/install/create_setup_windows.pl
@@ -193,7 +193,7 @@ if (($setup eq "director")){
 #  unlink("C:\\Users\\ksaff\\Release\\tag\\app_v3\\Tools\\vpncfg.txt");
   #compile executeables
   chdir("C:\\Users\\ksaff\\Release\\tag\\Tools");
-#  system('"C:\\Strawberry\\perl\\site\\bin\\pp" -o "C:\\Users\\ksaff\\Release\\dist\\Tools\\creorgadb.exe" creorgadb.pl');
+  system('"C:\\Strawberry\\perl\\site\\bin\\pp" -o "C:\\Users\\ksaff\\Release\\dist\\Tools\\creorgadb.exe" creorgadb.pl');
   system('"C:\\Strawberry\\perl\\site\\bin\\pp" -o "C:\\Users\\ksaff\\Release\\dist\\Tools\\pdfextract.exe"  --module="XML::Simple" --compress 6 pdfextract.pl');
   #copy("C:\\Debug\\Creorga\\Tools\\pdfextract2.exe","C:\\Users\\ksaff\\Release\\dist\\Tools\\pdfextract.exe");
   #system('"C:\\Strawberry\\perl\\site\\bin\\pp" -o "C:\\Debug\\Creorga\\Tools\\pdfextract.exe" --module="XML::LibXML" pdfextract.pl');
-- 
2.39.5