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

75 lines
7.2 KiB
MySQL
Raw Permalink Normal View History

Add the SQL for the demo
2023-01-17 21:30:01 +00:00
begin;
set search_path to auth, numerus, public;
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence user_user_id_seq restart with 123;
Restart sequences for the demo I was using explicit IDs because i need them to satisfy foreign key constraints, and also to look them up within the file, but then i had the problem that the sequences would be left at 1, preventing me to add new contacts or products, for instance. Now i use the sequence exactly how the application will (i.e., with default values), but i have to reset them to 1 to make the ID stable even when i make tests with pgTAP on the same database.
2023-02-07 15:59:00 +00:00
insert into auth."user" (email, name, password, role)
values ('demo@numerus', 'Demo User', 'demo', 'invoicer')
, ('admin@numerus', 'Demo Admin', 'admin', 'admin')
Add the company relation and read-only form to edit I do not have more time to update the update to the company today, but i believe this is already a good amount of work for a commit. The company is going to be used for row level security, as users will only have access to the data from companies they are granted access, by virtue of being in the company_user relation. I did not know how add a row level security policy to the company_user because i needed the to select on the same relation and this is not allowed, because it would create an infinite loop. Had to add the vat, pg_libphonenumber, and uri extensions in order to validate VAT identification numbers, phone numbers, and URIs, repectively. These libraries are not in Debian, but i created packages for them all in https://dev.tandem.ws/tandem.
2023-01-24 20:46:07 +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" deferred;
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence company_company_id_seq restart with 123;
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
insert into company (business_name, vatin, trade_name, phone, email, web, address, city, province, postal_code, country_code, currency_code, default_payment_method_id, legal_disclaimer)
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
values ('Juli Verd', 'ES40404040D', 'Pesebre', parse_packed_phone_number('972 50 60 70', 'ES'), 'info@numerus.cat', 'https://numerus.cat/', 'C/ de l’Hort', 'Castelló d’Empúries', 'Girona', '17486', 'ES', 'EUR', 124, 'Juli Verd és responsable del tractament de les seves dades d’acord amb el RGPD i la LOPDGDD, i les tracta per a mantenir una relació mercantil/comercial amb vostè. Les conservarà mentre es mantingui aquesta relació i no es comunicaran a tercers. Pot exercir els drets d’accés, rectificació, portabilitat, supressió, limitació i oposició a Juli Verd, amb domicili Carrer de l’Hort 71, 17486 Castelló d’Empúries o enviant un correu electrònic a info@numerus.cat. Per a qualsevol reclamació pot acudir a agpd.es. Per a més informació pot consultar la nostra política de privacitat a numerus.cat.');
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
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence payment_method_payment_method_id_seq restart with 123;
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
insert into payment_method (company_id, name, instructions)
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
values (123, 'Efectiu', 'Pagament en efectiu al comptat.')
, (123, 'Transferència', E'Pagament per transferència bancària al compte:\n\nES0123456789012345678901\n\nBIC AAAABBCCDD')
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;
Add the company relation and read-only form to edit I do not have more time to update the update to the company today, but i believe this is already a good amount of work for a commit. The company is going to be used for row level security, as users will only have access to the data from companies they are granted access, by virtue of being in the company_user relation. I did not know how add a row level security policy to the company_user because i needed the to select on the same relation and this is not allowed, because it would create an infinite loop. Had to add the vat, pg_libphonenumber, and uri extensions in order to validate VAT identification numbers, phone numbers, and URIs, repectively. These libraries are not in Debian, but i created packages for them all in https://dev.tandem.ws/tandem.
2023-01-24 20:46:07 +00:00
insert into company_user (company_id, user_id)
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
values (123, 123)
, (123, 124)
Add the SQL for the demo
2023-01-17 21:30:01 +00:00
;
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence tax_class_tax_class_id_seq restart with 123;
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 (company_id, name)
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
values (123, 'IRPF')
, (123, 'IVA')
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
;
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence tax_tax_id_seq restart with 123;
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 (company_id, tax_class_id, name, rate)
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
values (123, 123, 'Retenció 15 %', -0.15)
, (123, 124, 'IVA 21 %', 0.21)
, (123, 124, 'IVA 10 %', 0.10)
, (123, 124, 'IVA 4 %', 0.04)
Add the tax relation with very rough form and handler
2023-01-28 13:18:58 +00:00
;
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence contact_contact_id_seq restart with 123;
Add empty IBAN and BIC to demo contacts
2023-07-02 00:26:35 +00:00
select add_contact (123, 'Melcior', '0732621', 'melcio@reismags.cat', '', '(Rei Blanc,1,"C/ Principal, 1",Shiraz,Fars,1,IR)', '', '', array['pesebre', 'mag']);
select add_contact (123, 'Gaspar', '111', 'gaspar@reismags.cat', '', '(Rei Ros,2,"C/ Principal, 2",Nova Delhi,Delhi,2,IN)', '', '', array['pesebre', 'mag']);
select add_contact (123, 'Baltasar', '1-111-111', 'baltasar@reismags.cat', '', '(Rei Negre,3,"C/ Principal, 3",Sanaa,Sanaa,3,YE)', '', '', array['pesebre', 'mag']);
select add_contact (123, 'Caganera', '222 222 222', 'caganera@pesebre.cat', '', '(Caganera,41414141L,"C/ De l’Hort, 4",Olot,Girona,17800,ES)', '', '', array['pesebre', 'persona']);
select add_contact (123, 'Bou', '333 333 333', 'bou@pesebre.cat', '', '(Bou,41414142C,"C/ De la Palla, 5",Sant Climent Sescebes,Girona,17751,ES)', '', '', array['pesebre', 'bestia']);
select add_contact (123, 'Rabadà', '444 444 444', 'rabada@pesebre.cat', '', '(Rabadà,41414143K,"C/ De les Ovelles, 6",Fornells de la Selva,Girona,17458,ES)', '', '', array['pesebre', 'persona']);
Add sample contacts to the demo
2023-02-03 13:16:46 +00:00
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence product_product_id_seq restart with 123;
select add_product(123, 'Or', 'Metall de transició tou, brillant, groc, pesant, mal·leable, dúctil i que no reacciona amb la majoria de productes químics, però és sensible al clor i a l’aigua règia.', '55.92', array[124], array['metall']);
select add_product(123, 'Encens', 'Goma resina fragrant que desprèn una olor característica quan es crema.', '2.15', array[124], array['resina']);
select add_product(123, 'Mirra', 'Goma resinosa aromàtica de color gris groguenc i gust amargant.', '6.90', array[124], array['resina']);
select add_product(123, 'Paper higiènic (pack de 32 U)', 'Paper que s’usa per mantenir la higiene personal després de defecar o orinar.', '7.99', array[126], array['necessitat']);
select add_product(123, 'Cavall Fort', 'Revista quinzenal en llengua catalana i de còmic en català, destinada a infants i joves.', '3.64', array[124], array['revista']);
select add_product(123, 'Palla', 'Tija seca dels cereals després que el gra o llavor ha estat separat mitjançant la trilla.', '25.00', array[125], array['necessitat']);
select add_product(123, 'Teia', 'Fusta resinosa de pi i d’altres arbres, provinent sobretot del cor de l’arbre, que crema amb molta facilitat.', '7.00', array[124], array['obsolet']);
Add demo products
2023-02-06 12:35:56 +00:00
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
alter sequence invoice_invoice_id_seq restart with 123;
alter sequence invoice_product_invoice_product_id_seq restart with 123;
select add_invoice(123, (current_date - '28 days'::interval)::date, 128, 'Vol esmorzar!', 123, '{producte}','{"(129,Teia,\"Fusta resinosa de pi i d’altres arbres, provinent sobretot del cor de l’arbre, que crema amb molta facilitat.\",7.00,1,0.0,{124})","(127,Cavall Fort,\"Revista quinzenal en llengua catalana i de còmic en català, destinada a infants i joves.\",3.64,1,0.0,{124})"}');
select add_invoice(123, (current_date - '24 days'::interval)::date, 127, '', 123, '{producte,bestia}','{"(128,Palla,Tija seca dels cereals després que el gra o llavor ha estat separat mitjançant la trilla.,25.00,25,0.0,{125})","(127,Cavall Fort,\"Revista quinzenal en llengua catalana i de còmic en català, destinada a infants i joves.\",3.64,1,0.0,{124})"}');
select add_invoice(123, (current_date - '17 days'::interval)::date, 126, '', 123, '{producte,higiene}','{"(126,\"Paper higiènic (pack de 32 U)\",Paper que s’usa per mantenir la higiene personal després de defecar o orinar.,7.99,10,0.0,{126})","(127,Cavall Fort,\"Revista quinzenal en llengua catalana i de còmic en català, destinada a infants i joves.\",3.64,1,0.0,{124})"}');
select add_invoice(123, (current_date - '7 days'::interval)::date, 125, '', 123, '{producte,mag}','{"(125,Mirra,Goma resinosa aromàtica de color gris groguenc i gust amargant.,7.22,144,0.05,{124})","(127,Cavall Fort,\"Revista quinzenal en llengua catalana i de còmic en català, destinada a infants i joves.\",4.45,1,0.0,{124})"}');
select add_invoice(123, (current_date - '4 days'::interval)::date, 124, '', 123, '{producte,mag}','{"(124,Encens,Goma resina fragrant que desprèn una olor característica quan es crema.,2.26,460,0.05,{124})","(127,Cavall Fort,\"Revista quinzenal en llengua catalana i de còmic en català, destinada a infants i joves.\",4.53,1,0.0,{124})"}');
select add_invoice(123, (current_date - '1 days'::interval)::date, 123, '', 123, '{producte,mag}','{"(123,Or,\"Metall de transició tou, brillant, groc, pesant, mal·leable, dúctil i que no reacciona amb la majoria de productes químics, però és sensible al clor i a l’aigua règia.\",57.82,18,0.05,{124})","(127,Cavall Fort,\"Revista quinzenal en llengua catalana i de còmic en català, destinada a infants i joves.\",3.43,1,0.0,{124})"}');
Add invoices to the demo data
2023-03-06 10:23:11 +00:00
Change IDs of demo SQL script to prevent coincidences This is to avoid the problem that if i mistype `company_id = 1` instead of `company_id = $1`, like i fixed in ee0b5d0b, at least it won’t work in testing now, as all IDs have a three digits number.
2023-06-21 08:02:06 +00:00
update invoice set invoice_status = 'paid' where invoice_id in (123, 127);
update invoice set invoice_status = 'unpaid' where invoice_id = 125;
update invoice set invoice_status = 'sent' where invoice_id = 126;
Add invoices to the demo data
2023-03-06 10:23:11 +00:00
Add the SQL for the demo
2023-01-17 21:30:01 +00:00
commit;