tandem
/
numerus
2
0
Fork 0
Code Issues 39 Pull Requests Packages Projects 1 Releases 1 Wiki Activity
numerus/test/invoice_amount.sql

111 lines
3.4 KiB
MySQL
Raw Permalink Normal View History

Add views to compute taxes and total amount of invoices They are not functions because i need to join them with the main invoice relation, and although possible is a bit more awkward with functions. The taxes have their own relation because i will need them grouped by their name in the PDF, so it will probably be a select for that relation.
2023-02-22 13:39:38 +00:00
-- Test invoice_amount
set client_min_messages to warning;
create extension if not exists pgtap;
reset client_min_messages;
begin;
select plan(12);
set search_path to numerus, auth, public;
select has_view('invoice_amount');
select table_privs_are('invoice_amount', 'guest', array[]::text[]);
select table_privs_are('invoice_amount', 'invoicer', array['SELECT']);
select table_privs_are('invoice_amount', 'admin', array['SELECT']);
select table_privs_are('invoice_amount', 'authenticator', array[]::text[]);
select has_column('invoice_amount', 'invoice_id');
select col_type_is('invoice_amount', 'invoice_id', 'integer');
select has_column('invoice_amount', 'subtotal');
select col_type_is('invoice_amount', 'subtotal', 'integer');
select has_column('invoice_amount', 'total');
select col_type_is('invoice_amount', 'total', 'integer');
set client_min_messages to warning;
truncate invoice_product_tax cascade;
truncate invoice_product cascade;
truncate invoice cascade;
truncate contact cascade;
truncate tax cascade;
Introduce the concept of tax class We want to show the percentage of the tax as columns in the invoice, but until now it was not possible to have a single VAT column when products have different VAT (e.g., 4 % and 10 %), because, as far as the application is concerned, these where ”different taxes”. We also think it would be hard later on to compute the tax due to the government. So, tax classes is just a taxonomy to be able to have different names and rates for the same type of tax, mostly VAT and retention in our case.
2023-02-28 11:02:27 +00:00
truncate tax_class cascade;
Add company’s default payment method I had to use a deferrable foreign key because the payment methods have a reference to the company, and the company now a circular reference to payment method.
2023-03-04 21:15:52 +00:00
truncate payment_method cascade;
Add views to compute taxes and total amount of invoices They are not functions because i need to join them with the main invoice relation, and although possible is a bit more awkward with functions. The taxes have their own relation because i will need them grouped by their name in the PDF, so it will probably be a select for that relation.
2023-02-22 13:39:38 +00:00
truncate company cascade;
reset client_min_messages;
Add company’s default payment method I had to use a deferrable foreign key because the payment methods have a reference to the company, and the company now a circular reference to payment method.
2023-03-04 21:15:52 +00:00
set constraints "company_default_payment_method_id_fkey" deferred;
insert into company (company_id, business_name, vatin, trade_name, phone, email, web, address, city, province, postal_code, country_code, currency_code, default_payment_method_id)
values (1, 'Company 1', 'XX123', '', '555-555-555', 'a@a', '', '', '', '', '', 'ES', 'EUR', 111)
;
insert into payment_method (payment_method_id, company_id, name, instructions)
values (111, 1, 'cash', 'cash')
Add views to compute taxes and total amount of invoices They are not functions because i need to join them with the main invoice relation, and although possible is a bit more awkward with functions. The taxes have their own relation because i will need them grouped by their name in the PDF, so it will probably be a select for that relation.
2023-02-22 13:39:38 +00:00
;
Add company’s default payment method I had to use a deferrable foreign key because the payment methods have a reference to the company, and the company now a circular reference to payment method.
2023-03-04 21:15:52 +00:00
set constraints "company_default_payment_method_id_fkey" immediate;
Introduce the concept of tax class We want to show the percentage of the tax as columns in the invoice, but until now it was not possible to have a single VAT column when products have different VAT (e.g., 4 % and 10 %), because, as far as the application is concerned, these where ”different taxes”. We also think it would be hard later on to compute the tax due to the government. So, tax classes is just a taxonomy to be able to have different names and rates for the same type of tax, mostly VAT and retention in our case.
2023-02-28 11:02:27 +00:00
insert into tax_class (tax_class_id, company_id, name)
values (11, 1, 'tax')
;
insert into tax (tax_id, company_id, tax_class_id, name, rate)
values (2, 1, 11, 'IRPF -15 %', -0.15)
, (3, 1, 11, 'IVA 4 %', 0.04)
, (4, 1, 11, 'IVA 10 %', 0.10)
, (5, 1, 11, 'IVA 21 %', 0.21)
Add views to compute taxes and total amount of invoices They are not functions because i need to join them with the main invoice relation, and although possible is a bit more awkward with functions. The taxes have their own relation because i will need them grouped by their name in the PDF, so it will probably be a select for that relation.
2023-02-22 13:39:38 +00:00
;
insert into contact (contact_id, company_id, business_name, vatin, trade_name, phone, email, web, address, city, province, postal_code, country_code)
values (7, 1, 'Contact', 'XX555', '', '777-777-777', 'c@c', '', '', '', '', '', 'ES')
;
Add the payment method to invoices
2023-03-05 17:50:57 +00:00
insert into invoice (invoice_id, company_id, invoice_number, invoice_date, contact_id, currency_code, payment_method_id)
Use the correct integer literal for invoice_amount PostgreSQL actually already casts the strings to integers, but best if everything is as it should.
2023-06-07 12:54:29 +00:00
values ( 8, 1, 'I1', current_date, 7, 'EUR', 111)
, ( 9, 1, 'I2', current_date, 7, 'EUR', 111)
, (10, 1, 'I3', current_date, 7, 'EUR', 111)
, (11, 1, 'I4', current_date, 7, 'EUR', 111)
Add views to compute taxes and total amount of invoices They are not functions because i need to join them with the main invoice relation, and although possible is a bit more awkward with functions. The taxes have their own relation because i will need them grouped by their name in the PDF, so it will probably be a select for that relation.
2023-02-22 13:39:38 +00:00
;
Move the product_id field from invoice_product to a separate table We are going to allow invoices with products that are not (yet) inserted into the products table. We always allowed to have products in invoices with a totally different name, description, price, and whatnot, but until now we had the product id in these invoice lines for statistics purposes. However, Oriol raised the concern that this requires for the products to be inserted before we can create an invoice with them, and we do not plan to have a “create product while invoicing” feature, thus it would mean that people would need to cancel the new invoice, create the new product, and then start the invoice again from scratch. The compromise is to allow products in the invoice that do not have a product_id, meaning that at the time the invoice was created they were not (yet) in the products table. Oriol sees this stop-invoice-create- product issue more important than the accurate statistics of product sales, as it will probably be only one or two units off, anyway. I did not want to allow NULL values to the invoice product’s product_id field, because NULL means “dunno” instead of “no product”, so i had to split that field to a separate table that relates an invoice product with a registered product.
2023-04-19 17:30:12 +00:00
insert into invoice_product (invoice_product_id, invoice_id, name, price, quantity, discount_rate)
values (12, 8, 'P', 100, 1, 0.0)
, (13, 8, 'P', 200, 2, 0.1)
, (14, 9, 'P', 222, 3, 0.0)
, (15, 9, 'P', 333, 4, 0.2)
, (16, 10, 'P', 444, 5, 0.0)
, (17, 10, 'P', 555, 6, 0.1)
, (18, 11, 'P', 777, 8, 0.0)
Add views to compute taxes and total amount of invoices They are not functions because i need to join them with the main invoice relation, and although possible is a bit more awkward with functions. The taxes have their own relation because i will need them grouped by their name in the PDF, so it will probably be a select for that relation.
2023-02-22 13:39:38 +00:00
;
insert into invoice_product_tax (invoice_product_id, tax_id, tax_rate)
values (12, 2, -0.15)
, (12, 5, 0.21)
, (13, 3, 0.04)
, (14, 4, 0.10)
, (14, 5, 0.21)
, (14, 2, -0.07)
, (15, 4, 0.10)
, (16, 4, 0.10)
, (16, 5, 0.21)
, (17, 5, 0.21)
, (17, 3, 0.04)
;
select bag_eq(
$$ select invoice_id, subtotal, total from invoice_amount $$,
$$ values ( 8, 460, 480)
, ( 9, 1732, 1999)
, (10, 5217, 6654)
, (11, 6216, 6216)
$$,
'Should compute the amount for all taxes in the invoiced products.'
);
select *
from finish();
rollback;