From 9fb0cfaf4b2bf9a818b2c6fa48041cbd31aed14d Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Fri, 18 Dec 2020 08:27:27 +0100 Subject: [PATCH] v20201218 --- desktopapp/index.html | 1 - desktopapp/js/dataform.js | 39 +------ desktopapp/js/global/myapp.js | 9 ++ desktopapp/js/global/renderer.js | 18 +++ desktopapp/js/modules/addresses.js | 19 +--- desktopapp/js/modules/invoice.js | 15 +-- desktopapp/js/modules/invoices.js | 8 +- desktopapp/js/modules/products.js | 7 +- desktopapp/js/modules/templates.js | 66 +++++++++++ desktopapp/panels/index.html | 12 +- desktopapp/panels/products.html | 1 + desktopapp/panels/templates.html | 68 ++++++++++- dev/exporttojson.pl | 105 +++++++++++++++++ dev/sqlite.pm | 177 +++++++++++++++++++++++++++++ 14 files changed, 469 insertions(+), 76 deletions(-) create mode 100644 dev/exporttojson.pl create mode 100644 dev/sqlite.pm diff --git a/desktopapp/index.html b/desktopapp/index.html index db443a9..bb2f445 100644 --- a/desktopapp/index.html +++ b/desktopapp/index.html @@ -24,7 +24,6 @@ - diff --git a/desktopapp/js/dataform.js b/desktopapp/js/dataform.js index 89f9fd8..3f4f264 100644 --- a/desktopapp/js/dataform.js +++ b/desktopapp/js/dataform.js @@ -104,44 +104,7 @@ let dataform = { } return false; }, - // cleanform2: function(frmname,choices){ - // var frm = document.getElementById("frm_" + frmname); - // for (var f in frm){ - // if (frm[f] && frm[f].id){ - // if (frm[f].tagName == 'INPUT'){ - // if (frm[f].type == "checkbox"){ - // frm[f].checked = false; - // } else if (frm[f].classList.contains("datefield")){ - // if (frm[f]._flatpickr){ frm[f]._flatpickr.clear(); } - // } else if (frm[f].classList.contains("choices__input")){ - // if (choices[frm[f].id]){ - // choices[frm[f].id].removeActiveItems(); - // } - // } else { - // frm[f].value = ""; - // } - // } - // if (frm[f].tagName == 'SELECT'){ - // if (frm[f].multiple == true){ - // if (frm[f].classList.contains("choices__input")){ - // choices[frm[f].id].removeActiveItems(); - // } - // } else { - // frm[f].value = ""; - // } - - // } - // if (frm[f].tagName == 'TEXTAREA'){ - // if (frm[f].classList.contains("richeditarea")){ - // tinymce.get(frm[f].id).setContent(""); - // } else { - // frm[f].innerHTML = ""; - // } - // } - // } - // } - // return false; - // }, + fillform: function(dataclass,choices,data){ var frm = document.querySelectorAll('.data_'+ dataclass); //console.log("frmdata",frm); diff --git a/desktopapp/js/global/myapp.js b/desktopapp/js/global/myapp.js index 7692348..aaa4c64 100644 --- a/desktopapp/js/global/myapp.js +++ b/desktopapp/js/global/myapp.js @@ -5,6 +5,13 @@ let preferences = { defaultdata: {} } +let appdata = { + invoices: [], + addresses: [], + offers: [], + products: [] +} + let myapp = { loadpanel: function(idpanel){ return new Promise( @@ -26,7 +33,9 @@ let myapp = { }); }, viewpanel: function( idpanel){ + console.log("View panel:" + idpanel) myapp.loadpanel(idpanel).then(result => { + console.log("result loadpanel",result); if (result){ let panels = document.getElementsByClassName("panel"); for (let i=0;i { + console.log("ID Invoice:",id); + db.query("SELECT inv.*,sum(bk.netamount) AS netamount,sum(bk.taxamount) AS vatamount,sum(bk.netamount + bk.taxamount) AS grossamount FROM invoices inv JOIN bookings bk ON (inv.id = bk.id_invoices) WHERE inv.id='"+id+"' GROUP BY inv.id;").then(data => { console.log("invoicedata",data); dataform.fillform('invoices',invoice.selects,data); if (data.pdffile){ @@ -35,7 +34,7 @@ let invoice = { dataform.savefield(document.getElementById(instance.element.id)); //console.log("change DATE"); invoice.setDeadlineDate(); - invoice.setReference(); + invoice.getNewReference(); } }); @@ -215,7 +214,8 @@ let invoice = { newref = moment(strdate).format('YYYYMMDD') + '-' + timecalc.lpad(preferences.defaultdata.invoicesequence,4,'0'); } } - + document.getElementById("invoices_reference").value=newref; + dataform.savefield(document.getElementById("invoices_reference")); console.log("new Ref",newref); return newref; }, @@ -249,8 +249,9 @@ let invoice = { grossamount = parseFloat(netamount) + parseFloat(vatamount); document.getElementById("invoicebooking_grossamount").value = grossamount.toFixed(2); }, - changedStatus: function(obj){ - if (obj.value == "payed" ){ + changedStatus: function(){ + let status = invoice.selects["invoices_status"].selected(); + if (status == "payed" ){ console.log("Set invoice payed!"); //document.getElementById document.getElementById("invoices_payedamount").value=invoice.getSumGrossAmount(); diff --git a/desktopapp/js/modules/invoices.js b/desktopapp/js/modules/invoices.js index 9e42666..61db3d5 100644 --- a/desktopapp/js/modules/invoices.js +++ b/desktopapp/js/modules/invoices.js @@ -89,10 +89,14 @@ let invoices = { }, gettbldata: function(){ + // console.log(preferences.global); + //let newdata = usersystem.getDataSet('C:/Users/ksaff/AppData/Roaming/invoicejournal/dks/data/invoices/2020'); + //console.log(newdata); 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.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 => { + invoices.tbl.setData(data).then(dd => { + //console.log(newdata); if (sel[0]){ console.log(sel[0]); invoices.tbl.selectRow(sel[0].id); @@ -100,8 +104,8 @@ let invoices = { // table.scrollToRow(table.getSelectedRows(), "top", false); // }); } - }); }); + }); return false; }, loadfilterdata: function(data){ diff --git a/desktopapp/js/modules/products.js b/desktopapp/js/modules/products.js index 981f556..472d508 100644 --- a/desktopapp/js/modules/products.js +++ b/desktopapp/js/modules/products.js @@ -12,8 +12,8 @@ let products = { init: function(){ products.tbl = new Tabulator("#tbl_products",{ headerFilterPlaceholder: "filter...", - groupBy:["producttype", "productgroup"], - groupStartOpen:[true, true], + //groupBy:["producttype", "productgroup"], + //groupStartOpen:[true, true], height: "calc(100vh - 60px)", layout: "fitColumns", selectable: 1, @@ -29,6 +29,9 @@ let products = { ] }); products.gettbldata(); + myapp.loaddialog("confirm").then(result => { + + }); }, initSelector: function(){ diff --git a/desktopapp/js/modules/templates.js b/desktopapp/js/modules/templates.js index 25f5d88..795ae29 100644 --- a/desktopapp/js/modules/templates.js +++ b/desktopapp/js/modules/templates.js @@ -4,6 +4,72 @@ let templates = { myapp.viewpanel('templates'); }, init: function(){ + templates.tbl = new Tabulator("#tbl_templates",{ + headerFilterPlaceholder: "filter...", + height: "calc(100vh - 60px)", + layout: "fitDataStretch", + selectable: 1, + rowClick:function(e, row){ + console.log("Filter selected"); + let fsel = addresses.tbl.getSelectedData(); + console.log(fsel); + addresses.current_id = fsel[0].id; + addresses.edit(fsel[0].id); + //invoices.getsums(fsel); + }, + rowContext:function(e, row){ e.preventDefault(); }, + columns: [ + {title: "Name", field: "reportname", resizable: false}, + {title: "Name", field: "reporttype", resizable: false}, + ] + }); + tinymce.init({ + selector: '#templates_headerhtml, #templates_contenthtml, #templates_footerhtml', + plugins: 'preview paste importcss searchreplace autolink directionality code visualblocks visualchars fullscreen image link media template table charmap hr nonbreaking anchor advlist lists wordcount imagetools textpattern noneditable charmap emoticons autoresize ', + imagetools_cors_hosts: ['picsum.photos'], + menubar: 'file edit view insert format tools table', + toolbar: 'undo redo | bold italic underline strikethrough | fontsizeselect | alignleft aligncenter alignright alignjustify | outdent indent | numlist bullist | forecolor removeformat | charmap emoticons | fullscreen preview | insertfile image media link anchor', + toolbar_sticky: true, + language: 'de', + forced_root_block : '', + min_height: 350, + branding: false, + importcss_append: true, + image_advtab: true, + image_title: true, + automatic_uploads: true, + file_picker_types: 'image', + file_picker_callback: function (cb, value, meta) { + var input = document.createElement('input'); + input.setAttribute('type', 'file'); + input.setAttribute('accept', 'image/*'); + input.onchange = function () { + var file = this.files[0]; + + var reader = new FileReader(); + reader.onload = function () { + var id = 'blobid' + (new Date()).getTime(); + var blobCache = tinymce.activeEditor.editorUpload.blobCache; + var base64 = reader.result.split(',')[1]; + var blobInfo = blobCache.create(id, file, base64); + blobCache.add(blobInfo); + cb(blobInfo.blobUri(), { title: file.name }); + }; + reader.readAsDataURL(file); + }; + + input.click(); + + }, + + + image_caption: true, + + noneditable_noneditable_class: "mceNonEditable", + + contextmenu: "link image imagetools table", + }); + console.log("init templates"); } } \ No newline at end of file diff --git a/desktopapp/panels/index.html b/desktopapp/panels/index.html index 30ff1a9..7edfe28 100644 --- a/desktopapp/panels/index.html +++ b/desktopapp/panels/index.html @@ -16,11 +16,9 @@ - - - + + +
@@ -35,8 +33,8 @@ - - + +
\ No newline at end of file diff --git a/desktopapp/panels/products.html b/desktopapp/panels/products.html index 7997c2f..520c83c 100644 --- a/desktopapp/panels/products.html +++ b/desktopapp/panels/products.html @@ -1,3 +1,4 @@ +
diff --git a/desktopapp/panels/templates.html b/desktopapp/panels/templates.html index e2d9be9..c729f42 100644 --- a/desktopapp/panels/templates.html +++ b/desktopapp/panels/templates.html @@ -8,4 +8,70 @@ "font-size: 16px;">dupl -
\ No newline at end of file + +
+ +
+
+
+
+
+
+
+
+
+
+
+
+ +
+
+
+
+
+ + +
+
+
+
+ + +
+
+
+
+ + +
+
+
+
+
+
+
+ +
+
+ +
+
+ +
+
+ +
+
+ +
+
+ +
+
+
+
+ +
+
+
diff --git a/dev/exporttojson.pl b/dev/exporttojson.pl new file mode 100644 index 0000000..e5a30a7 --- /dev/null +++ b/dev/exporttojson.pl @@ -0,0 +1,105 @@ +#!C:\Strawberry\perl\bin\perl.exe + + +use strict; + +use DBI; +use DBD::SQLite; +use Encode; +use JSON::PP; +use Data::Dumper; + +my $dbfile = "C:/Users/ksaff/AppData/Roaming/invoicejournal/dks/dks.sqlite"; +my $outpath = "C:/Users/ksaff/AppData/Roaming/invoicejournal/dks/data/offers"; +my $dbh = DBI->connect('DBI:SQLite:dbname='.$dbfile,"","",{PrintError=>1,RaiseError=>1,AutoCommit=>1}) or print "dbquery Connection Error!".$!."\n"; +my $sql = "select * from offers;"; +my $res = &dbquerysorted($sql); +#print Dumper($res); +foreach my $i (keys(%{$res})){ + #print Dumper($res->{$i}->{id}); + my $sqls = "SELECT sum(bk.netamount) AS netamount,sum(bk.taxamount) AS vatamount,sum(bk.netamount + bk.taxamount) AS grossamount FROM offers inv JOIN offerdetails bk ON (inv.id = bk.id_offers) WHERE inv.id='".$res->{$i}->{id}."' GROUP BY inv.id;"; + my $sums = &dbquerysorted($sqls); + #print Dumper($sums); + foreach my $x (keys(%{$sums->{0}})){ + print Dumper($x); + $res->{$i}->{$x} = $sums->{0}->{$x}; + } + my $sqlx = "select * from offerdetails where id_offers='".$res->{$i}->{id}."';"; + #print $sqlx."\n"; + my $sqlr = "select reportname from newreports where id='".$res->{$i}->{id_template}."';"; + my $rep = &dbquerysorted($sqlr); + $res->{report} = $rep->{0}->{reportname}; + #print $sqlx."\n"; + my $res2 = &dbqueryarray($sqlx); + + $res->{$i}->{bookings}= $res2; + my $fileid = $res->{$i}->{reference}; + $fileid =~ s/^\s+//; + $fileid =~ s/\s+$//; + $fileid = $res->{$i}->{shortname}.'-'.$res->{$i}->{reference}; + &write_jsonfile($fileid,$res->{$i}); +} +$dbh->disconnect(); + + +sub write_jsonfile(){ + + my $id = shift; + my $jsondata = shift; + if (! -d $outpath){ + mkdir($outpath); + } + my $json = JSON::PP::encode_json($jsondata); + $json = decode("utf-8",$json); + open(JS,">".$outpath.'/'.$id.'.json'); + print JS $json; + close(JS); +} + +sub dbquerysorted(){ + my $stat = shift; + my $retdata = (); + + $stat = encode("utf8", $stat); + + my $sth = $dbh->prepare($stat) or print "Failed prepare: ".$stat."\n"; + + $sth->execute() or print "dbquery: ".$sth->errstr."\n"; + my $count = 0; + while(my $data = $sth->fetchrow_hashref()) + { + foreach my $k (keys %{$data}){ + $retdata->{$count}->{$k} = $data->{$k}; + } + $count++; + } + + $sth->finish(); + + + return $retdata; +} + +sub dbqueryarray(){ + my $stat = shift; + my @retdata = (); + + $stat = encode("utf8", $stat); + + my $sth = $dbh->prepare($stat) or print "Failed prepare: ".$stat."\n"; + + $sth->execute() or die "dbquery: ".$sth->errstr; + my $count = 0; + while(my $data = $sth->fetchrow_hashref()) + { + my $ret = {}; + foreach my $k (keys %{$data}){ + $ret->{$k} = $data->{$k}; + } + push @retdata,$ret; + } + + $sth->finish(); + + return \@retdata; +} \ No newline at end of file diff --git a/dev/sqlite.pm b/dev/sqlite.pm new file mode 100644 index 0000000..80b794a --- /dev/null +++ b/dev/sqlite.pm @@ -0,0 +1,177 @@ +package sqlite; +use strict; +use DBI; +use DBD::SQLite; +use Encode; +use File::Basename; + +sub new { + my $class = shift; + my $p = shift; + my $self = bless {}, $class; + $self->{dbfile} =$p; + return $self; +} + +sub strreplace(){ + my $self = shift; + my $text = shift; + $text =~ s/'/''/g; + return $text; +} + +sub dbquery(){ + my $self = shift; + my $key = shift; + my $stat = shift; + my $retdata =(); + my $dbh = DBI->connect('DBI:SQLite:dbname='.$self->{dbfile},"","",{PrintError=>1,RaiseError=>1,AutoCommit=>1}) or print "dbquery Connection Error!".$!."\n"; + $stat = encode("utf8", $stat); + + #open FILE,">>/tmp/sql.log"; + # print FILE "key:".$key.";$stat\n"; + # close FILE; + my $sth = $dbh->prepare($stat) ; + $sth->execute() or print "dbquery: ".$sth->errstr."\n"; + while(my $data = $sth->fetchrow_hashref()) + { + if (exists $data->{$key}){ + foreach my $k (keys %{$data}){ + $retdata->{$data->{$key}}{$k} = $data->{$k}; + } + } + } + if (keys(%{$retdata}) == 0){ + $retdata =(); + } + $sth->finish(); + $dbh->disconnect(); + return $retdata; +} + +sub dbquerysorted(){ + my $self = shift; + my $stat = shift; + my $retdata = (); + my $dbh = DBI->connect('DBI:SQLite:dbname='.$self->{dbfile},"","",{PrintError=>1,RaiseError=>1,AutoCommit=>1}) or print "dbquery Connection Error!".$!."\n"; + $stat = encode("utf8", $stat); + #open FILE,">>/tmp/sql.log"; + #print "Query Sorted: $stat\n"; + # close FILE; + my $sth = $dbh->prepare($stat) or print "Failed prepare: ".$stat."\n"; + + $sth->execute() or print "dbquery: ".$sth->errstr."\n"; + my $count = 0; + while(my $data = $sth->fetchrow_hashref()) + { + foreach my $k (keys %{$data}){ + $retdata->{$count}->{$k} = $data->{$k}; + } + $count++; + } + + $sth->finish(); + $dbh->disconnect(); + #%retdata = sort {$a <=> $b} keys %retdata; + return $retdata; +} + +sub dbqueryarray(){ + my $self = shift; + my $stat = shift; + my @retdata = (); + my $dbh = DBI->connect('DBI:SQLite:dbname='.$self->{dbfile},"","",{PrintError=>1,RaiseError=>1,AutoCommit=>1}) or print "dbquery Connection Error!".$!."\n"; + $stat = encode("utf8", $stat); + open FILE,">>sqlarray.log"; + print FILE "$stat\n"; + close FILE; + my $sth = $dbh->prepare($stat) or print "Failed prepare: ".$stat."\n"; + + $sth->execute() or die "dbquery: ".$sth->errstr; + my $count = 0; + while(my $data = $sth->fetchrow_hashref()) + { + my $ret = {}; + foreach my $k (keys %{$data}){ + $ret->{$k} = $data->{$k}; + } + push @retdata,$ret; + } + + $sth->finish(); + $dbh->disconnect(); + #%retdata = sort {$a <=> $b} keys %retdata; + return @retdata; +} + +sub dbexec(){ + my $self = shift; + my $stat = shift; + my $dbh = DBI->connect('DBI:SQLite:dbname='.$self->{dbfile},"","",{PrintError=>1,AutoCommit=>1}) or print "dbexec Connection Error!".$!."\n"; + $stat = encode("utf8", $stat); + #print $stat."\n"; + #open FILE,">>/Users/kilian/sql.log"; + #print "$stat\n"; + #close FILE; + my $sth = $dbh->prepare($stat); + my $rv =$dbh->do($stat) or print "dbexec '".$stat. "\n".$dbh->errstr()." failed!\n"; + $dbh->disconnect(); + return $rv; +} + + +# sub dbbackup(){ +# my $self = shift; +# my $path = shift; +# my $type = shift; + +# my @dx = localtime(); +# $dx[5] = $dx[5] +1900; +# $dx[4] = $dx[4] +1; +# if ($dx[4] < 10){$dx[4] = '0'.$dx[4];} +# if ($dx[3] < 10){$dx[3] = '0'.$dx[3];} +# if ($dx[2] < 10){$dx[2] = '0'.$dx[2];} +# if ($dx[1] < 10){$dx[1] = '0'.$dx[1];} +# if ($dx[0] < 10){$dx[0] = '0'.$dx[0];} +# my $xdd = $dx[5].$dx[4].$dx[3].'_'.$dx[2].$dx[1].$dx[0]; +# my $bfile = ""; +# if ($type eq "binary" ) { +# $bfile = $path.'/'.basename($self->{dbfile}).'_'.$xdd.'.db'; +# my $dbh = DBI->connect('DBI:SQLite:dbname='.$self->{dbfile},"","",{PrintError=>1,RaiseError=>1,AutoCommit=>1}) or print "dbexec Connection Error!".$!."\n"; +# $dbh->sqlite_backup_to_file($bfile); +# $dbh->disconnect(); +# }elsif($type eq "sql"){ +# $bfile = $path.'/'.basename($self->{dbfile}).'_'.$xdd.'.sql'; +# my $st = system('sqlite3 "'.$self->{dbfile}.'" ".dump" > '.$bfile); +# } +# return $bfile; +# } + +# sub dbrestore(){ +# my $self = shift; +# my $file = shift; +# my $type = shift; +# if ($type eq "binary" ) { +# my $dbh = DBI->connect('DBI:SQLite:dbname='.$self->{dbfile},"","",{PrintError=>1,RaiseError=>1,AutoCommit=>1}) or print "dbexec Connection Error!".$!."\n"; +# $dbh->sqlite_backup_from_file($file); +# $dbh->disconnect(); +# }elsif($type eq "sql"){ +# open(REST,$file) or die "cannot open restore file $file!\n"; +# my $rsql = ""; +# while (my $l = ) { +# $rsql .= $l; +# } +# close(REST); +# unlink($self->{dbfile}); +# $self->dbexec($rsql); +# } +# } + +# sub dbrepair(){ +# my $self = shift; +# my $bfile = $self->dbbackup($ENV{'TMPDIR'},'sql'); +# $self->dbrestore($bfile,'sql'); +# unlink($bfile); +# } + +1; -- 2.39.5