@@ -, +, @@ included PRE-gst tax (as an option) --- 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(-) --- a/C4/Bookseller.pm +++ a/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'} ); --- a/acqui/invoice.pl +++ a/acqui/invoice.pl @@ -152,24 +152,30 @@ 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'}, invoicenumber => $details->{'invoicenumber'}, suppliername => $details->{'suppliername'}, booksellerid => $details->{'booksellerid'}, + shippingincgst => $bookseller->{'shippingincgst'}, datereceived => $details->{'datereceived'}, 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, --- a/acqui/parcels.pl +++ a/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'), --- a/acqui/supplier.pl +++ a/acqui/supplier.pl @@ -97,7 +97,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'}, @@ -165,7 +167,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}, --- a/acqui/updatesupplier.pl +++ a/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'); --- a/installer/data/mysql/kohastructure.sql +++ a/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 */; - --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -8560,6 +8560,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) --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/invoice.tt +++ a/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 %]
  • --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/supplier.tt +++ a/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: --- a/t/db_dependent/Bookseller.t +++ a/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, @@ -243,7 +247,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, @@ -290,7 +296,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 @@ -317,7 +325,9 @@ my $sample_supplier4 = { gstreg => 1, listincgst => 1, invoiceincgst => 1, + shippingincgst => 1, gstrate => '3.0000', + sgstrate => '3.0000', discount => '3.0000', notes => 'notes3', }; --