From c31190d2a27b543abc40961e84de4ec20baeed0c Mon Sep 17 00:00:00 2001 From: kilian Date: Sun, 19 Apr 2020 16:25:38 +0200 Subject: [PATCH] v20200419 --- server/tmpl/module/invoices.tt | 6 ++-- server/tmpl/module/invoices/invoice.js | 50 +++++++++++++++++++++----- server/tmpl/module/invoices/invoice.tt | 42 ++++++++++------------ 3 files changed, 63 insertions(+), 35 deletions(-) diff --git a/server/tmpl/module/invoices.tt b/server/tmpl/module/invoices.tt index bae8e5c..b27ca9d 100644 --- a/server/tmpl/module/invoices.tt +++ b/server/tmpl/module/invoices.tt @@ -36,9 +36,9 @@ [% qinv = dksdb.prepare("select strftime('%d.%m.%Y',inv.invoicedate) as invoicedate,inv.id, inv.direction,case when inv.direction= 'out' then inv.receipientname else inv.sendername end as receipient,inv.reference,inv.status -,replace(printf('%.2f',sum(round(coalesce(bk.quantity,0.0) * coalesce(bk.unitamount,0.0),2))),'.',',') as netamount -,replace(printf('%.2f',sum(round(case when bk.taxpercent is not null then round(coalesce(bk.quantity,0.0) * coalesce(bk.unitamount,0.0),2) * coalesce(bk.taxpercent/100.00,0.0) else coalesce(bk.taxamount,0.00 ) end,2))),'.',',') as vatamount -,replace(printf('%.2f',sum(round(coalesce(bk.quantity,0.0) * coalesce(bk.unitamount,0.0),2) + round(case when bk.taxpercent is not null then round(coalesce(bk.quantity,0.0) * coalesce(bk.unitamount,0.0),2) * coalesce(bk.taxpercent/100.00,0.0) else coalesce(bk.taxamount,0.00 ) end,2))),'.',',') as grossamount +,replace(printf('%.2f',sum(coalesce(bk.netamount,0.0))),'.',',') as netamount +,replace(printf('%.2f',sum(coalesce(bk.taxamount,0.0))),'.',',') as vatamount +,replace(printf('%.2f',sum(coalesce(bk.netamount,0.0) + coalesce(bk.taxamount,0.0) )),'.',',') as grossamount from invoices inv left join bookings bk on (inv.id=bk.id_invoices) where businessyear= ? group by inv.id order by inv.invoicedate desc") %] [% FOREACH inv = qinv.execute(cbj.0.defvalue) %] diff --git a/server/tmpl/module/invoices/invoice.js b/server/tmpl/module/invoices/invoice.js index 45db63a..4df109a 100644 --- a/server/tmpl/module/invoices/invoice.js +++ b/server/tmpl/module/invoices/invoice.js @@ -46,15 +46,47 @@ var idbk = document.getElementById("bookings_id"); if (data && data.sqldata){ console.log(data); - record.setValue(document.getElementById("bookings_unit-" + idbk),data.sqldata.unit); - record.setValue(document.getElementById("bookings_unitamount-" + idbk),data.sqldata.salesprice); - record.setValue(document.getElementById("bookings_taxpercent-" + idbk),data.sqldata.vatpercent); - record.setValue(document.getElementById("bookings_vatamount-" + idbk),""); - record.setValue(document.getElementById("bookings_sku-" + idbk),data.sqldata.sku); - record.setValue(document.getElementById("bookings_name-" + idbk),data.sqldata.name); - record.setValue(document.getElementById("bookings_description-" + idbk),data.sqldata.description); + record.setValue(document.getElementById("bookings_id_product"),data.sqldata.id); + record.setValue(document.getElementById("bookings_unit"),data.sqldata.unit); + record.setValue(document.getElementById("bookings_unitamount"),data.sqldata.salesprice); + record.setValue(document.getElementById("bookings_taxpercent"),data.sqldata.vatpercent); + //record.setValue(document.getElementById("bookings_vatamount-" + idbk),""); + record.setValue(document.getElementById("bookings_sku"),data.sqldata.sku); + record.setValue(document.getElementById("bookings_name"),data.sqldata.name); + record.setValue(document.getElementById("bookings_description"),data.sqldata.description); + invoice.calculate(); } }, + calculate: function(){ + var invid = document.getElementById("invoices_id").value; + var direction = document.getElementById("invoices_direction-" + invid).value; + //var idbk = document.getElementById("bookings_id"); + var qu = document.getElementById("bookings_quantity").value; + if (qu){ qu = parseFloat(qu);} else {qu = 1;} + var price = document.getElementById("bookings_unitamount").value; + if (price){ price = parseFloat(price).toFixed(2);} else {price = 0.0;} + var vatpercent = null; + var vatamount = null; + var grossamount = null; + console.log(qu + " * "+ price); + var netamount = (qu * price).toFixed(2); + record.setValue(document.getElementById("bookings_netamount"),netamount); + if (direction == "out"){ + var vatpercent = document.getElementById("bookings_taxpercent").value; + if (vatpercent){ vatpercent = parseFloat(vatpercent).toFixed(2);} else {vatpercent = 0.0;} + console.log("VAT(%):" + vatpercent); + record.setValue(document.getElementById("bookings_taxpercent"),vatpercent); + vatamount = ((price * (vatpercent/100)).toFixed(2) * qu).toFixed(2); + console.log("VAT:" + vatamount); + } + if (direction == "in"){ + vatamount = document.getElementById("bookings_taxamount").value; + if (vatamount){ vatamount = parseFloat(vatamount).toFixed(2);} else {vatamount = 0.0;} + } + record.setValue(document.getElementById("bookings_taxamount"),vatamount); + grossamount = parseFloat(netamount) + parseFloat(vatamount); + document.getElementById("bookings_grossamount").value = grossamount; + }, changedStatus: function(obj){ if (obj.value == "payed" ){ console.log("Set invoice payed!"); @@ -86,7 +118,7 @@ console.log("can not create"); } document.getElementById("bookings_taxpercent").style.display = 'block'; - document.getElementById("bookings_vatamount").style.display = 'none'; + //document.getElementById("bookings_vatamount").style.display = 'none'; document.getElementById("btnAddProduct").style.display = 'block'; document.getElementById("pnlCatalog").style.display = 'block'; } @@ -94,7 +126,7 @@ document.getElementById("btnCreateInvoice").style.display = 'none'; document.getElementById("btnSendEmail").style.display = 'none'; document.getElementById("bookings_taxpercent").style.display = 'none'; - document.getElementById("bookings_vatamount").style.display = 'block'; + //document.getElementById("bookings_vatamount").style.display = 'block'; document.getElementById("bookings_vatamount").style.display = 'block'; document.getElementById("btnAddProduct").style.display = 'none'; document.getElementById("pnlCatalog").style.display = 'none'; diff --git a/server/tmpl/module/invoices/invoice.tt b/server/tmpl/module/invoices/invoice.tt index 04379c0..12d21b6 100644 --- a/server/tmpl/module/invoices/invoice.tt +++ b/server/tmpl/module/invoices/invoice.tt @@ -8,23 +8,18 @@ [% PROCESS lists/receipientlist.tt %] [% PROCESS lists/reportlist.tt %] [% USE Dumper %] -[% qinv =dksdb.query("select *,printf('%.2f',calcvat) as vatamount -,printf('%.2f',calcnet) as netamount -,printf('%.2f',round(calcvat + calcnet,2)) as grossamount -from (select inv.* -,sum(round(coalesce(bk.quantity,0.0) * coalesce(bk.unitamount,0.0),2)) as calcnet -,sum(case when bk.taxpercent is not null then coalesce(bk.quantity,0.0) * round(coalesce(bk.unitamount,0.0) * coalesce(bk.taxpercent/100.00,0.0),2) else round(coalesce(bk.taxamount,0.0),2) end) as calcvat -from invoices inv join bookings bk on (inv.id=bk.id_invoices) where inv.id = '${params.id}');") %] +[% qinv =dksdb.query("select inv.* +,printf('%.2f',sum(netamount)) as netamount +,printf('%.2f',sum(taxamount)) as vatamount +,printf('%.2f',sum(netamount + taxamount)) as grossamount +from invoices inv join bookings bk on (inv.id=bk.id_invoices) where inv.id='${params.id}';") %] -[% qbook =dksdb.prepare("select * -,replace(printf('%.2f',calcvat),'.',',') as vatamount -,replace(printf('%.2f',calcnet),'.',',') as netamount -,replace(printf('%.2f',round(calcvat + calcnet,2)),'.',',') as grossamount from ( -SELECT id, id_invoices, id_products, producttype, sku, replace(quantity,'.',',') as quantity, replace(printf('%.2f',coalesce(unitamount,0.0)),'.',',') as unitamount, unit,name, description, taxamount,taxpercent +[% qbook =dksdb.prepare("SELECT id, id_invoices, id_products, producttype, sku, replace(quantity,'.',',') as quantity, replace(printf('%.2f',coalesce(unitamount,0.0)),'.',',') as unitamount, unit,name, description, taxpercent ,replace(printf('%.2f',taxpercent),'.',',') as vatpercent -,round(coalesce(quantity,0.0) * coalesce(unitamount,0.0),2) as calcnet -,case when taxpercent is not null then coalesce(quantity,0.0) * round(coalesce(unitamount,0.0) * coalesce(taxpercent/100.00,0.0),2) else round(coalesce(taxamount,0.0),2) end as calcvat -from bookings where id_invoices='${params.id}');") %] +,replace(printf('%.2f',taxamount),'.',',') as taxamount +,replace(printf('%.2f',netamount),'.',',') as netamount +,replace(printf('%.2f',netamount + taxamount),'.',',') as grossamount +from bookings where id_invoices='${params.id}';") %] [% inv = qinv.get_all() %]
@@ -231,26 +226,27 @@ from bookings where id_invoices='${params.id}');") %]
- [% inputbox('quantity','bookings','',lbl.quantity,'number','','','','','') %] + [% inputbox('quantity','bookings','',lbl.quantity,'number','','','','','','invoice.calculate();') %]
- [% inputbox('unit','bookings','',lbl.unit,'text','','','','','') %] + [% inputbox('unit','bookings','',lbl.unit,'text','','','','','','') %]
- [% inputbox('unitamount','bookings','',lbl.price,'currency','','','','','') %] + [% inputbox('unitamount','bookings','',lbl.price,'currency','','','','','','invoice.calculate();') %]
- [% inputbox('taxpercent','bookings','',"${lbl.vat_short}(%)",'number','','','','','') %] + [% inputbox('taxpercent','bookings','',"${lbl.vat_short}(%)",'number','','','','','','invoice.calculate();') %]
- [% inputbox('vatamount','bookings','',lbl.vat_long,'currency','','','','','') %] + [% inputbox('taxamount','bookings','',lbl.vat_long,'currency','','','','','','invoice.calculate();') %]
- [% inputbox('netamount','bookings','','Netto','currency','','','readonly','','','') %] -
-
[% inputbox('grossamount','bookings','','Brutto','currency','','','readonly','','','') %]
+
+ [% inputbox('netamount','bookings','','Netto','currency','','','readonly','','','') %] +
+
-- 2.39.5