From 2f2ac3c2bc82ad1f03043453bc4b6e8dcf8415c2 Mon Sep 17 00:00:00 2001
From: kilian <ksaffran@dks.lu>
Date: Mon, 2 Nov 2020 18:34:22 +0100
Subject: [PATCH] v20201102

---
 desktopapp/index.html             |   1 +
 desktopapp/js/global/myapp.js     | 151 ++++--------------------------
 desktopapp/js/global/sqlite.js    |   5 +
 desktopapp/js/modules/index.js    |  15 +--
 desktopapp/js/modules/invoice.js  |  30 ++++--
 desktopapp/js/modules/invoices.js |  52 +++++++---
 desktopapp/panels/invoice.html    |  18 ++--
 7 files changed, 104 insertions(+), 168 deletions(-)

diff --git a/desktopapp/index.html b/desktopapp/index.html
index a8e07fc..d837e83 100644
--- a/desktopapp/index.html
+++ b/desktopapp/index.html
@@ -46,6 +46,7 @@
       <script src="js/global/request.js?v=6" type="text/javascript"></script> 
       <script src="js/global/myapp.js?v=6" type="text/javascript"></script>
       <script src="js/dataform.js?v=6" type="text/javascript"></script> 
+      <script src="js/timecalc.js?v=6" type="text/javascript"></script> 
       <script src="js/global/renderer.js?v=6" type="text/javascript"></script> 
       <script src="js/global/sqlite.js?v=6" type="text/javascript"></script>
       <script src="js/global/report.js?v=6" type="text/javascript"></script> 
diff --git a/desktopapp/js/global/myapp.js b/desktopapp/js/global/myapp.js
index 2ca8af5..8810bfe 100644
--- a/desktopapp/js/global/myapp.js
+++ b/desktopapp/js/global/myapp.js
@@ -1,10 +1,11 @@
 
-var preferences = {
+let preferences = {
   global: null,
   page: null,
+  defaultdata: {}
 }
 
-var myapp = {
+let myapp = {
     loadpanel: function(idpanel){
       return new Promise(
         function(resolve, reject){
@@ -74,137 +75,20 @@ var myapp = {
       document.getElementById('dlgmessage').style.display='block';
       return false;
     },
-    connectdb: function(){
-
-    }
-    // loadpage(page){
-    //   location.href = page; 
-    // },
-    // loadpage: function(data){
-    //   if (table){
-    //     table.selection = null;
-    //   }
-    //   document.getElementById("modulescripts").innerHTML ="";
-    //   document.getElementById("mainapp").innerHTML = '';
-    //   myapp.addmoduledata(data);
-    //   document.getElementById("mainapp").insertAdjacentHTML('afterbegin',data);
-    //   var re = '\<script src=\"(.+)\"\>';
-    //   var found = data.match(re);
-      
-    //   if (found){
-    //     console.log('script found!');
-    //     console.log(found);
-    //     var se = document.createElement('script');
-    //     se.setAttribute('type', 'text/javascript');
-    //     se.setAttribute('src',found[1]);
-    //     document.getElementById("modulescripts").appendChild(se);
-    //   }
-    //   return false;
-    // },
-    // setpref: function(obj){
-    //   fdata = obj.dataset;
-    //   fdata["save"] = "pref";
-    //   if (obj.tagName == 'INPUT' || obj.tagName == 'SELECT' || obj.tagName == 'TEXTAREA'){
-    //     if (obj.type == 'checkbox' || obj.type == 'radio'){
-    //       if (obj.checked == true){
-    //         fdata["value"] = obj.value;
-    //       } else {
-    //         fdata["value"] = null;
-    //       }
-    //     }else {
-    //       fdata["value"] = obj.value;
-    //     }
-    //   }
-    //   req.reqdata(fdata,record.saveresult);
-    // },
-    
-  // loadconfig: function() {
-  //     this.cfg = this.getSearchParams();
-  // },
-    
-
-    // loadappview: function(panelid) {
-    //   document.getElementById('modules').style.display = 'none';
-    //   document.getElementById('appview').style.display = 'block';
-    //     //console.log(appident);
-    //     //console.log(location.origin + '/app/' + appident + '/index.html');
-    //     //location.href = location.origin + '/app/' + appident + '.html';
-    // },
-  //   loadapppage: function(pagename) {
-       
-  //     console.log(location.origin + '/' + pagename + '.html' + ((app.dataset)?'?db=' + myapp.dataset:''));
-  //     location.href = location.origin + '/' + pagename + '.html' + ((app.dataset)?'?db=' + myapp.dataset:'');
-  // },
-    // loadmodulepage: function(modulepage, data) {
-    //     var pm = [];
-    //     if (typeof data == 'object') {
-    //       var xdata = [];
-    //       for (var i in data) {
-    //           var value = '';
-    //           if (typeof(data[i]) == 'object') {
-    //               value = encodeURIComponent(JSON.stringify(data[i]));
-    //           } else {
-    //               value = encodeURIComponent(data[i]);
-    //           }
-    //           xdata.push(i + "=" + value);
-    //       }
-    //       rdata = xdata.join("&");
-    //     } 
-    //     if (app.dataset){
-    //       pm.push("db=" + myapp.dataset);
-    //     }
-    //     if (pm.length > 0) {
-    //         modulepage = modulepage + "?" + pm.join("&") + ((rdata != "")?'&' + rdata:"");
-    //     }
-        
-    //     document.getElementById("moduleframe").setAttribute('src', 'modules/' + modulepage);
-    //     document.getElementById('appview').style.display = 'none';
-    //     document.getElementById('modules').style.display = 'block';
-        
-          
-    // },
-    
-    // logout: function() {
-    //     req.reqdata("POST", location.href, { "logout": "1" }, admin.reloadpage);
-
-    // },
-    // reloadpage: function() {
-    //     location.href = location.href;
-    // },
-    ,getdatasets: function() {
-      //req.reqdata("POST", "app.cgi", { "databases": "1" }, myapp.loaddatasets);
-    },
-    // getpreference: function(pagename, callback) {
-    //     req.reqdata("POST", "db.cgi", { "db": basedb, "type": "array", "sql": "select * from preferences where page='" + pagename + "' and ident='" + admin.getCookie("dksident") + "';" }, callback);
-    // },
-    // setpreference: function(pagename, prefdata) {
-    //     var txtprefdata = encodeURIComponent(prefdata);
-    //     if (typeof prefdata == 'object') {
-    //         txtprefdata = encodeURIComponent(JSON.stringify(prefdata));
-    //     }
-    //     req.reqdata("POST", "db.cgi", { "db": basedb, "exec": "INSERT INTO preferences (ident,page,preferences) VALUES ('" + admin.getCookie("dksident") + "','" + pagename + "','" + txtprefdata + "');" }, null);
-    // },
-    loaddatasets: function(data) {
-      console.log("loadeddatasets");
-      console.log(data);
-      myapp.dataset=data.databases[0].id;
-    //     var gdt = document.getElementById('globaldatasets');
-    //     //console.log(gdt);
-    //     gdt.innerHTML = '';
-    //     if (data) {
-    //         var opts = '';
-    //         for (var i in data) {
-    //             opts += '<option value="' + data[i].datastore + '">' + data[i].name + '</option>';
-    //         }
-    //         gdt.innerHTML = opts;
-    //     }
-    //     if (admin.current_dataset.db) {
-
-    //     } else {
-    //         admin.current_dataset["db"] = gdt.value;
-    //     }
-    //     //console.log(admin.current_dataset);
+    setpref: function(key,value){
+      db.execAsync("REPLACE INTO defaultdata (id,pref) VALUES ('"+key+"','"+value+"');").then(res => {
+        myapp.loadpreferences();
+      });
+      return false;
     },
+    loadpreferences: function(){
+      db.queryarray("select id,pref from defaultdata;").then(res => {
+        for (var i=0;i<res.length;i++){
+          preferences["defaultdata"][res[i].id] = res[i].pref;
+        }
+      });
+      return false;
+    }
     // getCookie: function(cname) {
     //     var name = cname + "=";
     //     var decodedCookie = decodeURIComponent(document.cookie);
@@ -248,9 +132,10 @@ document.addEventListener("DOMContentLoaded", function() {
       preferences.global = usersystem.getPreference('invoicejournal');
       preferences.global["currentdataset"] = usersystem.getPreference(preferences.global.dataset);
     }
+    
     console.log("Content loaded",preferences);
     db.connect(preferences.global.currentdataset.datapath + "/" + preferences.global.currentdataset.database);
-    
+    myapp.loadpreferences();
     //.newuuid().then(data => {console.log(data);});
     myapp.viewpanel("index");
     //index.init();
diff --git a/desktopapp/js/global/sqlite.js b/desktopapp/js/global/sqlite.js
index 771fffc..2b08b63 100644
--- a/desktopapp/js/global/sqlite.js
+++ b/desktopapp/js/global/sqlite.js
@@ -9,6 +9,7 @@ let db = {
     db.dbh = new sqlite3.Database(dbfile);
   },
   query: function(sql){
+    process.stderr.write("Query:\n-----\n"+sql+"\n-----\n");
     return new Promise((resolve, reject) => {
       db.dbh.get(`${sql}`, (err, row) => {
           if (err) {
@@ -20,6 +21,7 @@ let db = {
     });
   },
   queryarray: function(sql){
+    process.stderr.write("QueryArray:\n-----\n"+sql+"\n-----\n");
     return new Promise((resolve, reject) => {
       let queries = [];
       db.dbh.each(`${sql}`, (err, row) => {
@@ -38,6 +40,7 @@ let db = {
     });
   },
   querybykey: function(key,sql){
+    process.stderr.write("QueryByKey:\n-----\n"+sql+"\n-----\n");
     return new Promise((resolve, reject) => {
       let ret = {}
       db.dbh.each(`${sql}`, (err, row) => {
@@ -56,6 +59,7 @@ let db = {
     });
   },
   exec: function(sql,callback){
+    process.stderr.write("Exec:\n-----\n"+sql+"\n-----\n");
     try {
       return db.dbh.exec(sql);
     } catch(e){
@@ -64,6 +68,7 @@ let db = {
     
   },
   execAsync: function(sql){
+    process.stderr.write("ExecAsync:\n-----\n"+sql+"\n-----\n");
     return new Promise((resolve, reject) => {
       db.dbh.exec(`${sql}`, (err, row) => {
           if (err) {
diff --git a/desktopapp/js/modules/index.js b/desktopapp/js/modules/index.js
index 77863c9..5711e30 100644
--- a/desktopapp/js/modules/index.js
+++ b/desktopapp/js/modules/index.js
@@ -4,12 +4,15 @@ let index={
     myapp.viewpanel('index');
   },
   init(){
-    db.queryarray("select id,pref from defaultdata;").then(res => {
-      for (var i=0;i<res.length;i++){
-        preferences["defaultdata"][res[i].id] = res[i].pref;
-      }
-      console.log(preferences);
-    });
+    // db.queryarray("select id,pref from defaultdata;").then(res => {
+    //   console.log(res);
+    //   for (var i=0;i<res.length;i++){
+    //     console.log(res[i]);
+    //     preferences["defaultdata"][res[i].id] = res[i].pref;
+    //   }
+    //   console.log("preferences",preferences);
+      
+    // });
     // index.selects["bussinessyear"] = new SlimSelect({
     //   select: "#bussinessyear",
     //   showSearch: false,
diff --git a/desktopapp/js/modules/invoice.js b/desktopapp/js/modules/invoice.js
index b0e0e5d..c7841f3 100644
--- a/desktopapp/js/modules/invoice.js
+++ b/desktopapp/js/modules/invoice.js
@@ -118,8 +118,18 @@ let invoice = {
       dataform.filldatalist(document.getElementById("list_invoicebooking_units"),units,'unit');
     })
   },
+  dlgSelectAddress(){
+    db.queryarray("select * from addresses order by receipient").then(data => {
+      addresses.tblselector.setData(data).then(dd => {
+        if ((document.getElementById("invocies_id_account")) && (document.getElementById("invocies_id_account").value != '')){
+          addresses.tblselector.selectRow(document.getElementById("invoices_id_accounts").value);
+        }
+      });
+      myapp.viewdialog("SelectAddress");
+    });
+},
   changeAccount: function(obj){
-    let adrsel = adresses.tblselector.getSelectedData();
+    let adrsel = addresses.tblselector.getSelectedData();
     if (adrsel[0]){
       document.getElementById("invoices_id_accounts").value= adrsel[0].id;
       document.getElementById("invoices_accountname").value= adrsel[0].receipient;
@@ -146,7 +156,7 @@ let invoice = {
     let dldate = moment(document.getElementById("invoices_deadlinedate").value);
     dataform.setValue(document.getElementById("invoices_deadlinedays"),dldate.diff(invdate,'days'));
   },
-  setReference(data){
+  setReference(){
     // console.log("Set Reference");
     // console.log(data);
     document.getElementById("invoices_reference").value = data[0]["setinvoicereference"];
@@ -169,6 +179,13 @@ let invoice = {
     //   invoice.calculate();
     // }
   },
+  getNewReference(strdate){
+    preferences.defaultdata.invoicesequence = parseInt(preferences.defaultdata.invoicesequence) + 1;
+    myapp.setpref('invoicesequence',preferences.defaultdata.invoicesequence);
+    let newref = moment(strdate).format('YYYYMMDD') + '-' + timecalc.lpad(preferences.defaultdata.invoicesequence,4,'0');
+    console.log(newref);
+    return newref;
+  },
   calculate: function(){
     //let invid = document.getElementById("invoices_id").value;
     let invtype = document.getElementById("invoices_invoicetype").value;
@@ -312,14 +329,7 @@ let invoice = {
     let payedval = document.getElementById("invoices_grossamount").value;
     dataform.setValue(document.getElementById("invoices_payedamount"),payedval);
   },
-  dlgSelectAddress(){
-      db.queryarray("select * from addresses order by receipient").then(data => {
-        addresses.tblselector.setData(data).then(dd => {
-          addresses.tblselector.selectRow(document.getElementById("invoices_id_accounts").value);
-        });
-        myapp.viewdialog("SelectAddress");
-      });
-  },
+  
   addBooking:function(){
     dataform.cleanform();
     db.newuuid().then( nuuid => {
diff --git a/desktopapp/js/modules/invoices.js b/desktopapp/js/modules/invoices.js
index d64360d..0c590ae 100644
--- a/desktopapp/js/modules/invoices.js
+++ b/desktopapp/js/modules/invoices.js
@@ -5,7 +5,10 @@ let statustypes = {
   "sended":{"value":"sended","text" : "verschickt", "class": "text-orange"},
   "overdue":{"value":"overdue","text" : "überfällig", "class": "text-red"},
   "payed":{"value":"payed","text" : 'bezahlt', "class": "text-green"},
-  "canceled":{"value":"canceled","text" : "storniert", "class": "text-grey"}
+  "canceled":{"value":"canceled","text" : "storniert", "class": "text-grey"},
+  "reminder1":{"value":"reminder1","text" : "Mahnung 1", "class": "text-red"},
+  "reminder2":{"value":"reminder2","text" : 'Mahnung 2', "class": "text-red"},
+  "reminder3":{"value":"reminder3","text" : "Mahnung 3", "class": "text-red"}
 }
 
 let headerstatustypes = {
@@ -16,7 +19,10 @@ let headerstatustypes = {
   "sended":"verschickt",
   "overdue":"überfällig",
   "payed":"bezahlt",
-  "canceled":"storniert"
+  "canceled":"storniert",
+  "reminder1":"Mahnung 1",
+  "reminder2":"Mahnung 2",
+  "reminder3":"Mahnung 3"
 };
 
 
@@ -50,6 +56,7 @@ let invoices = {
   //selectedfilter: '2020',
   viewpanel: function(){
     myapp.viewpanel('invoices');
+    this.gettbldata();
   },
   init: function(){
     let cyear = moment(new Date()).format('YYYY');
@@ -74,12 +81,12 @@ let invoices = {
       ]
     });
     
-    this.gettbldata();
+    
   },
   gettbldata: function(){
     let sel= invoices.tbl.getSelectedData();
     /*--(to_char(inv.invoicedate::timestamp with time zone, 'YYYY'::text) || '-Q'::text) || to_char(inv.invoicedate::timestamp with time zone, 'Q'::text) AS quarter,  --to_char(inv.invoicedate::timestamp with time zone, 'YYYY-MM'::text) AS month,*/
-    db.queryarray("SELECT inv.invoicedate, inv.id,  inv.direction,  inv.accountname,  inv.reference,  inv.status,  sum(COALESCE(bk.netamount, 0.0)) AS netamount,  sum(COALESCE(bk.taxamount, 0.0)) AS vatamount,  sum(COALESCE(bk.netamount, 0.0) + COALESCE(bk.taxamount, 0.0)) AS grossamount,  inv.businessyear,    inv.id_accounts,  inv.invoicetype  FROM invoices inv   LEFT JOIN bookings bk ON inv.id = bk.id_invoices  GROUP BY inv.id ORDER BY inv.invoicedate DESC;") .then(data => {
+    db.queryarray("SELECT inv.invoicedate, inv.id, inv.accountname,  inv.reference,  inv.status,  sum(COALESCE(bk.netamount, 0.0)) AS netamount,  sum(COALESCE(bk.taxamount, 0.0)) AS vatamount,  sum(COALESCE(bk.netamount, 0.0) + COALESCE(bk.taxamount, 0.0)) AS grossamount,  inv.businessyear,    inv.id_accounts,  inv.invoicetype  FROM invoices inv   LEFT JOIN bookings bk ON inv.id = bk.id_invoices  GROUP BY inv.id ORDER BY inv.invoicedate DESC;") .then(data => {
       invoices.tbl.setData(data).then(dd => {
         if (sel[0]){
            invoices.tbl.selectRow(sel[0].id);
@@ -91,7 +98,6 @@ let invoices = {
     });
     return false;
   },
-
   loadfilterdata: function(data){
     if (data && data.sqldata) { invoices.tblfilter.setData(data.sqldata);}
   },
@@ -109,10 +115,10 @@ let invoices = {
       let today=moment().format('YYYY-MM-DD');
       let deadline=moment().add(preferences.defaultdata.invoicedeadlinedays,'days').format('YYYY-MM-DD');
       
-      db.execAsync("INSERT INTO invoices (id,invoicedate,deadlinedays,deadlinedate,status,id_template) VALUES ('"+newid.id+"',date('"+today+"'),'"+preferences.defaultdata.invoicedeadlinedays+"',date('"+deadline+"'),'preparation','"+preferences.defaultdata.id_invoicetemplate+"');").then( res =>{
+      db.execAsync("INSERT INTO invoices (id,invoicedate,deadlinedays,deadlinedate,status,id_template,invoicetype) VALUES ('"+newid.id+"',date('"+today+"'),'"+preferences.defaultdata.invoicedeadlinedays+"',date('"+deadline+"'),'preparation','"+preferences.defaultdata.id_invoicetemplate+"','invoice-out');").then( res =>{
         
         db.newuuid().then(newbookingid => {
-          db.execAsync("INSERT INTO bookings (id,id_invoices)  VALUES ('"+ newbookingid.id+"','"+ newid+"');").then(res => {
+          db.execAsync("INSERT INTO bookings (id,id_invoices)  VALUES ('"+ newbookingid.id+"','"+ newid.id+"');").then(res => {
             invoice.viewpanel(newid);
           });
         });
@@ -140,11 +146,33 @@ let invoices = {
   duplicate: function(){
     var sel = invoices.tbl.getSelectedData();
     if (sel[0]) {
-      //req.reqdata({"dbschema":"dks","fn":"duplicate_invoice","params":{0:sel[0].id}},invoices.gettbldata);
-      postData({"dbschema":"dks","fn":"duplicate_invoice","id":sel[0].id}).then(data => {
-        invoices.gettbldata();
-      })
+      console.log("Duplicate:",sel[0].id);
+      db.newuuid().then(newid => {
+
+        let today=moment().format('YYYY-MM-DD');
+        let deadline=moment().add(preferences.defaultdata.invoicedeadlinedays,'days').format('YYYY-MM-DD');
+        let newref= invoice.getNewReference(today);
+        let inssql = "INSERT INTO invoices (id, id_template, id_offer, accountvatid, clientnumber, isinnereu, payedamount, businessyear, invoicedate, deadlinedays, deadlinedate, status, footertext, reference, invoicetype, headertext, pdffile, offerreference, referencenumber, id_accounts, accountname, accountaddress, accountzip, accountcity, accountcountry) SELECT '"+ newid.id+"' as id, id_template, id_offer, accountvatid, clientnumber, isinnereu, null as payedamount, businessyear, date('"+today+"') as invoicedate,'"+preferences.defaultdata.invoicedeadlinedays+"' as deadlinedays,date('"+deadline+"') as deadlinedate, 'preparation' as status, footertext, '"+newref +"' AS reference, invoicetype, headertext, pdffile, offerreference, referencenumber, id_accounts, accountname, accountaddress, accountzip, accountcity, accountcountry FROM invoices where id='"+ sel[0].id+"';";
+        console.log("Execute:" + inssql);
+        db.execAsync(inssql).then(res => {
+          db.queryarray("select * from bookings where id_invoices='"+ sel[0].id+"'").then( bc => {
+            for (var b in bc){
+              let nbc = bc[b];
+              console.log(nbc);
+              db.newuuid().then(nbuuid => {
+                console.log(nbc);
+                let bsql = "INSERT INTO bookings (id, id_invoices, id_products, producttype, sku, quantity, unitamount, unit, name, description, taxamount, taxpercent, bookingdate, netamount) SELECT '"+nbuuid.id+"' as id, '"+newid.id+"' as id_invoices, id_products, producttype, sku, quantity, unitamount, unit, name, description, taxamount, taxpercent, bookingdate, netamount FROM bookings where id='"+ nbc.id+"';";
+                console.log(bsql);
+                db.execAsync(bsql).then(res => {
+                  if (b==bc.length-1){
+                    invoice.viewpanel(newid.id);
+                  }
+                });
+              });
+            }
+          });
+        });
+      });
     }
-      
   }
 }
\ No newline at end of file
diff --git a/desktopapp/panels/invoice.html b/desktopapp/panels/invoice.html
index 146d47a..f7a5f47 100644
--- a/desktopapp/panels/invoice.html
+++ b/desktopapp/panels/invoice.html
@@ -74,6 +74,15 @@
             <option class="text-grey" value="canceled">
               storniert
             </option>
+            <option class="text-red" value="reminder1">
+              Mahnung 1
+            </option>
+            <option class="text-red" value="reminder2">
+              Mahnung 2
+            </option>
+            <option class="text-red" value="reminder3">
+              Mahnung 3
+            </option>
           </select>
         </div>
       </div>
@@ -144,7 +153,7 @@
           "text">
         </div>
         <div class="cell" style="width: 200px;">
-          <input class="input border text data_invoices" data-column="receipientvatid" data-id="" data-table="invoices" id="invoices_receipientvatid" name="invoices_receipientvatid" onblur="dataform.savefield(this);" placeholder="USt.-ID" type="text">
+          <input class="input border text data_invoices" data-column="accountvatid" data-id="" data-table="invoices" id="invoices_accountvatid" name="invoices_accountvatid" onblur="dataform.savefield(this);" placeholder="USt.-ID" type="text">
         </div>
       </div>
       <div class="row">
@@ -153,12 +162,7 @@
           "dataform.savefield(this);" type="text">
         </div>
       </div>
-      <div class="row">
-        <div class="cell" style="width: 300px;">
-          <label class="label" for="invoices_reminderdates">Mahnungsdaten</label> <input class="input border text data_invoices" data-column="reminderdates" data-id="" data-table="invoices" id="invoices_reminderdates" name=
-          "invoices_reminderdates" onblur="dataform.savefield(this);" type="text">
-        </div>
-      </div>
+      
     </div>
   </div>
   <div class="cell">
-- 
2.39.5