From 7b90f73ce49541f95d78e07cad984e85edf47b2a Mon Sep 17 00:00:00 2001 From: simith Date: Mon, 16 Jun 2014 15:51:05 -0400 Subject: [PATCH] Bug 11062 - Invoice's shipment/shipping cost should be included PRE-gst tax (as an option) Koha's users don't have the option to enter shipping tax. This fix allow to vendors add a specific tax (included in gist preference) to shipping cost. Modified: added two columns, shippingincgst and sgstrate in aqbooksellers. Some files changed: C4/Bookseller.pm installer/data/mysql/kohastructure.sql installer/data/mysql/updatedatabase.pl acqui/updatesupplier.pl acqui/supplier.pl acqui/parcels.pl t/db_dependent/Bookseller.t acqui/invoice.pl - total_gste_shipment and total_gsti_shipment calcul changed koha-tmpl/intranet-tmpl/prog/en/modiles/acqui/supplier.tt - Shipping tax rate and shipping include tax or don't include tax, added koha-tmpl/intranet-tmpl/prog/en/modiles/acqui/parcels.tt - "(tax exc.)" or "(tax inc)" added beside "Shipping cost". koha-tmpl/intranet-tmpl/prog/en/modiles/acqui/invoice.tt - if shipping "tax exc", add "Shipping cost (tax inc)" below "Shipping cost" 0) Apply the patch 1) Run updatedatabase.pl to add the two columns to aqbooksellers 2) Add some tax rates in gist preferences 3) Edit a vendor 4) Chose shipping prices: include tax or not 5) Chose a shipping tax rate 6) Save 7) Create an order in a vendor's basket 8) click button receive shipment 9) Validate "(tax exc.)" or "(tax inc)" added beside "Shipping cost". 10) Click link "Receive order" in pending orders 11) Save 12) Click Finish receiving 13) Validate shipping "tax ex", add "Shipping cost (tax inc)" below "Shipping cost" if shipping "tax exc". 14) Validate last line "Total + Shipment cost (CDN)" --- C4/Bookseller.pm | 23 ++++++++++++--------- acqui/invoice.pl | 12 ++++++++--- acqui/parcels.pl | 1 + acqui/supplier.pl | 4 ++++ acqui/updatesupplier.pl | 2 ++ installer/data/mysql/kohastructure.sql | 23 +++++++++++---------- installer/data/mysql/updatedatabase.pl | 8 ++++++++ .../intranet-tmpl/prog/en/modules/acqui/invoice.tt | 5 ++++- .../intranet-tmpl/prog/en/modules/acqui/parcels.tt | 2 +- .../prog/en/modules/acqui/supplier.tt | 24 +++++++++++++++++++++- t/db_dependent/Bookseller.t | 12 ++++++++++- 11 files changed, 88 insertions(+), 28 deletions(-) diff --git a/C4/Bookseller.pm b/C4/Bookseller.pm index f317719..c3695bd 100644 --- a/C4/Bookseller.pm +++ b/C4/Bookseller.pm @@ -181,14 +181,15 @@ sub AddBookseller { my $query = q| INSERT INTO aqbooksellers ( - name, address1, address2, address3, address4, - postal, phone, accountnumber,fax, url, - contact, contpos, contphone, contfax, contaltphone, - contemail, contnotes, active, listprice, invoiceprice, - gstreg, listincgst, invoiceincgst,gstrate, discount, + name, address1, address2, address3, address4, + postal, phone, accountnumber, fax, url, + contact, contpos, contphone, contfax, contaltphone, + contemail, contnotes, active, listprice, invoiceprice, + gstreg, listincgst, invoiceincgst, shippingincgst, + gstrate, sgstrate, discount, notes, deliverytime ) - VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) | + VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) | ; my $sth = $dbh->prepare($query); $sth->execute( @@ -204,7 +205,8 @@ sub AddBookseller { $data->{active} ,$data->{listprice}, $data->{invoiceprice} ,$data->{gstreg}, $data->{listincgst} ,$data->{invoiceincgst}, - $data->{gstrate} ,$data->{discount}, + $data->{shippingincgst} ,$data->{gstrate}, + $data->{sgstrate} ,$data->{discount}, $data->{notes} ,$data->{deliverytime}, ); @@ -238,8 +240,8 @@ sub ModBookseller { postal=?,phone=?,accountnumber=?,fax=?,url=?,contact=?,contpos=?, contphone=?,contfax=?,contaltphone=?,contemail=?, contnotes=?,active=?,listprice=?, invoiceprice=?, - gstreg=?,listincgst=?,invoiceincgst=?, - discount=?,notes=?,gstrate=?,deliverytime=? + gstreg=?,listincgst=?,invoiceincgst=?,shippingincgst=?, + discount=?,notes=?,gstrate=?,sgstrate=?,deliverytime=? WHERE id=?'; my $sth = $dbh->prepare($query); return $sth->execute( @@ -255,8 +257,9 @@ sub ModBookseller { $data->{'active'}, $data->{'listprice'}, $data->{'invoiceprice'}, $data->{'gstreg'}, $data->{'listincgst'}, $data->{'invoiceincgst'}, + $data->{'shippingincgst'}, $data->{'discount'}, $data->{'notes'}, - $data->{'gstrate'}, + $data->{'gstrate'}, $data->{'sgstrate'}, $data->{deliverytime}, $data->{'id'} ); diff --git a/acqui/invoice.pl b/acqui/invoice.pl index 33e5592..aa3e288 100755 --- a/acqui/invoice.pl +++ b/acqui/invoice.pl @@ -152,6 +152,9 @@ foreach my $budget (@$budgets) { } push @budgets_loop, \%line; } +my $total_gste_shipment = $bookseller->{'shippingincgst'}?$total_gste + ((1 - $bookseller->{'sgstrate'}) * $details->{shipmentcost}):$total_gste + $details->{shipmentcost}; + +my $total_gsti_shipment = $bookseller->{'shippingincgst'}?$total_gsti + $details->{shipmentcost}:$total_gsti + (($bookseller->{'sgstrate'} + 1) * $details->{shipmentcost}); $template->param( invoiceid => $details->{'invoiceid'}, @@ -159,17 +162,20 @@ $template->param( suppliername => $details->{'suppliername'}, booksellerid => $details->{'booksellerid'}, shipmentdate => $details->{'shipmentdate'}, + shippingincgst => $bookseller->{'shippingincgst'}, billingdate => $details->{'billingdate'}, invoiceclosedate => $details->{'closedate'}, shipmentcost => $details->{'shipmentcost'}, + shippingincgstval => sprintf( $format, ($bookseller->{'sgstrate'} + 1) * $details->{shipmentcost}), orders_loop => \@orders_loop, foot_loop => \@foot_loop, total_quantity => $total_quantity, total_gste => sprintf( $format, $total_gste ), total_gsti => sprintf( $format, $total_gsti ), - total_gstvalue => sprintf( $format, $total_gstvalue ), - total_gste_shipment => sprintf( $format, $total_gste + $details->{shipmentcost}), - total_gsti_shipment => sprintf( $format, $total_gsti + $details->{shipmentcost}), + total_gstvalue => sprintf( $format, $total_gstvalue), + total_gstvalue_shipment => sprintf( $format, $total_gstvalue + $bookseller->{'sgstrate'} * $details->{shipmentcost}), + total_gste_shipment => sprintf( $format, $total_gste_shipment), + total_gsti_shipment => sprintf( $format, $total_gsti_shipment), invoiceincgst => $bookseller->{invoiceincgst}, currency => GetCurrency()->{currency}, budgets_loop => \@budgets_loop, diff --git a/acqui/parcels.pl b/acqui/parcels.pl index acf2edf..9ba6e89 100755 --- a/acqui/parcels.pl +++ b/acqui/parcels.pl @@ -205,6 +205,7 @@ $template->param( dateto => $dateto, resultsperpage => $resultsperpage, name => $bookseller->{'name'}, + shippingincgst => $bookseller->{'shippingincgst'}, shipmentdate_today => C4::Dates->new()->output(), booksellerid => $booksellerid, GST => C4::Context->preference('gist'), diff --git a/acqui/supplier.pl b/acqui/supplier.pl index e2c688f..a96ae7f 100755 --- a/acqui/supplier.pl +++ b/acqui/supplier.pl @@ -96,7 +96,9 @@ if ( $op eq 'display' ) { gstreg => $supplier->{'gstreg'}, listincgst => $supplier->{'listincgst'}, invoiceincgst => $supplier->{'invoiceincgst'}, + shippingincgst => $supplier->{'shippingincgst'}, gstrate => $supplier->{'gstrate'} + 0.0, + sgstreg => $supplier->{'sgstreg'} + 0.0, discount => $supplier->{'discount'}, deliverytime => $supplier->{deliverytime}, invoiceprice => $supplier->{'invoiceprice'}, @@ -164,7 +166,9 @@ if ( $op eq 'display' ) { gstreg => $supplier->{'gstreg'}, listincgst => $supplier->{'listincgst'}, invoiceincgst => $supplier->{'invoiceincgst'}, + shippingincgst => $supplier->{'shippingincgst'}, gstrate => $supplier->{gstrate} ? $supplier->{'gstrate'}+0.0 : 0, + sgstrate => $supplier->{sgstrate} ? $supplier->{'sgstrate'}+0.0 : 0, gst_values => \@gst_values, discount => $supplier->{'discount'}, deliverytime => $supplier->{deliverytime}, diff --git a/acqui/updatesupplier.pl b/acqui/updatesupplier.pl index 7b3bf9d..caee384 100755 --- a/acqui/updatesupplier.pl +++ b/acqui/updatesupplier.pl @@ -99,8 +99,10 @@ $data{'invoiceprice'}=$input->param('invoice_currency'); $data{'gstreg'}=$input->param('gst'); $data{'listincgst'}=$input->param('list_gst'); $data{'invoiceincgst'}=$input->param('invoice_gst'); +$data{'shippingincgst'}=$input->param('shipping_gst'); #have to transform this into fraction so it's easier to use $data{'gstrate'} = $input->param('gstrate'); +$data{'sgstrate'} = $input->param('sgstrate'); $data{'discount'} = $input->param('discount'); $data{deliverytime} = $input->param('deliverytime'); $data{'active'}=$input->param('status'); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index b46dc6a..1d5120e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2174,10 +2174,10 @@ CREATE TABLE `suggestions` ( -- purchase suggestions branchcode VARCHAR(10) default NULL, -- foreign key linking the suggested branch to the branches table collectiontitle text default NULL, -- collection name for the suggested item itemtype VARCHAR(30) default NULL, -- suggested item type - quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased - currency VARCHAR(3) default NULL, -- suggested currency for the suggested price - price DECIMAL(28,6) default NULL, -- suggested price - total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) + quantity SMALLINT(6) default NULL, -- suggested quantity to be purchased + currency VARCHAR(3) default NULL, -- suggested currency for the suggested price + price DECIMAL(28,6) default NULL, -- suggested price + total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) PRIMARY KEY (`suggestionid`), KEY `suggestedby` (`suggestedby`), KEY `managedby` (`managedby`) @@ -2475,12 +2475,12 @@ CREATE TABLE `permissions` ( DROP TABLE IF EXISTS `serialitems`; CREATE TABLE `serialitems` ( - `itemnumber` int(11) NOT NULL, - `serialid` int(11) NOT NULL, - UNIQUE KEY `serialitemsidx` (`itemnumber`), - KEY `serialitems_sfk_1` (`serialid`), - CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE + `itemnumber` int(11) NOT NULL, + `serialid` int(11) NOT NULL, + UNIQUE KEY `serialitemsidx` (`itemnumber`), + KEY `serialitems_sfk_1` (`serialid`), + CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2879,7 +2879,9 @@ CREATE TABLE `aqbooksellers` ( -- information about the vendors listed in acquis `gstreg` tinyint(4) default NULL, -- is your library charged tax (1 for yes, 0 for no) `listincgst` tinyint(4) default NULL, -- is tax included in list prices (1 for yes, 0 for no) `invoiceincgst` tinyint(4) default NULL, -- is tax included in invoice prices (1 for yes, 0 for no) + `shippingincgst` tinyint(4) NOT NULL DEFAULT '0', -- is tax included in shipping prices (1 for yes, 0 for no) `gstrate` decimal(6,4) default NULL, -- the tax rate the library is charged + `sgstrate` decimal(6,4) default NULL, -- the shipping tax rate is charged `discount` float(6,4) default NULL, -- discount offered on all items ordered from this vendor `fax` varchar(50) default NULL, -- vendor fax number deliverytime int(11) default NULL, -- vendor delivery time @@ -3430,4 +3432,3 @@ CREATE TABLE IF NOT EXISTS `misc_files` ( -- miscellaneous files attached to rec /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; - diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index de77bf3..2ec85b8 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -8613,6 +8613,14 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.17.00.XXX"; +if (CheckVersion($DBversion)) { + $dbh->do("ALTER TABLE aqbooksellers ADD shippingincgst tinyint(4) NOT NULL DEFAULT '0'"); + $dbh->do("ALTER TABLE aqbooksellers ADD sgstrate decimal(6,4) default NULL"); + print "Upgrade to $DBversion done (Bug 11062 - Invoice's shipment/shipping cost should be included PRE-gst tax (as an option))\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/invoice.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/invoice.tt index 8056c1d..ee68967 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/invoice.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/invoice.tt @@ -80,6 +80,9 @@
  • + [% IF (shippingincgst == 0) %] +
  • Shipping cost:[% shippingincgstval %] (tax inc)
  • + [% END %]
  • + [% IF (shippingincgst == 1) %](tax inc.)[% ELSE %](tax exc.)[% END %]
  • diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/supplier.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/supplier.tt index f54b1ea..8cf901b 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/supplier.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/supplier.tt @@ -140,6 +140,14 @@ function confirm_deletion() { [% END %]
  • +
  • + [% IF ( shippingincgst ) %] + + + [% ELSE %] + + + [% END %]
  • [% IF gst_values %]
      @@ -155,6 +163,18 @@ function confirm_deletion() { [% END %] +
    1. + + +
    [% ELSE %] @@ -221,7 +241,9 @@ function confirm_deletion() {

    List item price includes tax: [% IF ( listincgst ) %]Yes[% ELSE %]No[% END %]

    Invoice item price includes tax: - [% IF ( invoiceincgst ) %]Yes[% ELSE %]No[% END %]

    [% END %] + [% IF ( invoiceincgst ) %]Yes[% ELSE %]No[% END %]

    +

    Shipping price includes tax: + [% IF ( shippingincgst ) %]Yes[% ELSE %]No[% END %]

    [% END %]

    Discount: [% discount | format("%.1f") %] %

    Tax rate: diff --git a/t/db_dependent/Bookseller.t b/t/db_dependent/Bookseller.t index c7ea24c..4330fb0 100644 --- a/t/db_dependent/Bookseller.t +++ b/t/db_dependent/Bookseller.t @@ -62,7 +62,9 @@ my $sample_supplier1 = { gstreg => 1, listincgst => 1, invoiceincgst => 1, + shippingincgst => 1, gstrate => '1.0000', + sgstrate => '1.0000', discount => '1.0000', notes => 'notes1', deliverytime => undef @@ -89,7 +91,9 @@ my $sample_supplier2 = { gstreg => 1, listincgst => 1, invoiceincgst => 1, + shippingincgst => 1, gstrate => '2.0000', + sgstrate => '2.0000', discount => '2.0000', notes => 'notes2', deliverytime => 2, @@ -241,7 +245,9 @@ $sample_supplier2 = { gstreg => 1, listincgst => 1, invoiceincgst => 1, - gstrate => '2.0000 ', + shippingincgst => 1, + gstrate => '2.0000', + sgstrate => '2.0000', discount => '2.0000', notes => 'notes2 modified', deliverytime => 2, @@ -288,7 +294,9 @@ my $sample_supplier3 = { gstreg => 1, listincgst => 1, invoiceincgst => 1, + shippingincgst => 1, gstrate => '3.0000', + sgstrate => '3.0000', discount => '3.0000', notes => 'notes3', deliverytime => 3 @@ -315,7 +323,9 @@ my $sample_supplier4 = { gstreg => 1, listincgst => 1, invoiceincgst => 1, + shippingincgst => 1, gstrate => '3.0000', + sgstrate => '3.0000', discount => '3.0000', notes => 'notes3', }; -- 1.9.1