From 11a04bd50f0ec94871c75834b8b9497df46f5357 Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Fri, 19 Jun 2020 23:37:21 +0200 Subject: [PATCH] =?utf8?q?Konjunkturpaket=202020=20Anpassung=20der=20Mehrw?= =?utf8?q?ertsteuers=C3=A4tze=20und=20Test?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Ab dem 01.07.2020 sollen 16% und 5% gelten. Ab dem 01.01.2021 sollen wieder 19% und 7% gelten. --- sql/Pg-upgrade2/konjunkturpaket_2020.sql | 209 +++++++ .../konjunkturpaket_2020_SKR03.sql | 109 ++++ .../konjunkturpaket_2020_SKR04.sql | 128 +++++ t/tax/tax.t | 542 ++++++++++++++++++ 4 files changed, 988 insertions(+) create mode 100644 sql/Pg-upgrade2/konjunkturpaket_2020.sql create mode 100644 sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql create mode 100644 sql/Pg-upgrade2/konjunkturpaket_2020_SKR04.sql create mode 100644 t/tax/tax.t diff --git a/sql/Pg-upgrade2/konjunkturpaket_2020.sql b/sql/Pg-upgrade2/konjunkturpaket_2020.sql new file mode 100644 index 000000000..71a7421ec --- /dev/null +++ b/sql/Pg-upgrade2/konjunkturpaket_2020.sql @@ -0,0 +1,209 @@ +-- @tag: konjunkturpaket_2020 +-- @description: Anpassung der Steuersätze für 16%/5% für Deutsche DATEV-Kontenrahmen SKR03 und SKR04 +-- @depends: release_3_5_5 konjunkturpaket_2020_SKR03 konjunkturpaket_2020_SKR04 +-- @ignore: 0 + +-- begin; + +DO $$ + +DECLARE + -- variables for main taxkey creation loop, not all are needed + _chart_id int; + _accno text; + _description text; + _startdates date[]; + _tax_ids int[]; + _taxkeyentry_id int[]; + _taxkey_ids int[]; + _rates numeric[]; + _taxcharts text[]; + + current_taxkey record; + new_taxkey record; + _rate numeric; + _tax record; -- store the new tax we need to assign to a chart, e.g. 5%, 16% + + _taxkey int; + _old_rate numeric; + _old_chart text; + _new_chart numeric; + _new_rate text; + + _tax_conversion record; + + +BEGIN + +IF ( select coa from defaults ) ~ 'DATEV' THEN + +--begin; +--delete from taxkeys where startdate >= '2020-01-01'; + +-- create temp table temp_taxkey_conversions (taxkey int, old_rate numeric, new_rate numeric, tax_chart_skr03 text, tax_chart_skr04 text); +-- insert into temp_taxkey_conversions (taxkey, old_rate, new_rate, tax_chart_skr03, tax_chart_skr04) values +---- (2, 0.07, 0.05, '1773', '3803'), -- 5% case is handled by skr03 case -> needs different automatic chart: 1773 Umsatzsteuer 5% (SKR03, instead of 1771 Umsatzsteuer 7%) or 3803 Umsatzsteuer 5% +-- -- (8, 0.07, 0.05, null, null), +-- -- (3, 0.19, 0.16, null, null), +-- -- (9, 0.19, 0.16, null, null), +-- (13, 0.19, 0.16, null, null); + + +create temp table temp_taxkey_conversions (taxkey int, old_rate numeric, old_chart text, new_rate numeric, new_chart text); + +IF ( select coa from defaults ) = 'Germany-DATEV-SKR03EU' THEN + insert into temp_taxkey_conversions (taxkey, old_rate, old_chart, new_rate, new_chart) + values (9, 0.19, '1576', 0.16, '1575'), + (8, 0.07, '1571', 0.05, '1568'), + (3, 0.19, '1776', 0.16, '1575'), + (2, 0.07, '1771', 0.05, '1775'); + --1776 => 19% + --1775 => 16% + --1775 => 5% + --1771 => 7% + -- + --VSt: + --1576 => 19% + --1575 => 16% + --1568 => 5% + --1571 => 7% + + ELSE -- Germany-DATEV-SKR04EU + insert into temp_taxkey_conversions (taxkey, old_rate, old_chart, new_rate, new_chart) + values (9, 0.19, '1406', 0.16, '1405'), + (8, 0.07, '1401', 0.05, '1403'), + (3, 0.19, '3806', 0.16, '3805'), + (2, 0.07, '3801', 0.05, '3803'); + END IF; + + FOR _chart_id, _accno, _description, _startdates, _tax_ids, _taxkeyentry_id, _taxkey_ids, _rates, _taxcharts IN + + select c.id as chart_id, + c.accno, + c.description, + array_agg(t.startdate order by t.startdate desc) as startdates, + array_agg(t.tax_id order by t.startdate desc) as tax_ids, + array_agg(t.id order by t.startdate desc) as taxkeyentry_id, + array_agg(t.taxkey_id order by t.startdate desc) as taxkey_ids, + array_agg(tax.rate order by t.startdate desc) as rates, + array_agg(tc.accno order by t.startdate desc) as taxcharts + from taxkeys t + left join chart c on (c.id = t.chart_id) + left join tax on (tax.id = t.tax_id) + left join chart tc on (tax.chart_id = tc.id) + where t.taxkey_id in (select taxkey from temp_taxkey_conversions) -- 2, 3, 8, 9 + -- and (c.accno = '8400') -- debug + -- you can't filter for valid taxrates 19% or 7% here, as that would still leave the 16% rates as the current one + group by c.id, + c.accno, + c.description + order by c.accno + + -- example output for human debugging: + -- chart_id | accno | description | startdates | tax_ids | taxkeyentry_id | taxkey_ids | rates | taxcharts + -- ----------+-------+---------------------+-------------------------+-----------+----------------+------------+-------------------+------------- + -- 184 | 8400 | Erlöse 16%/19% USt. | {2007-01-01,1970-01-01} | {777,379} | {793,676} | {3,3} | {0.19000,0.16000} | {1776,1775} + + -- each chart with one of the applicable taxkeys should receive two new entries, one starting on 01.07.2020, the other on 01.01.2021 + LOOP + -- 1. create new taxkey entry on 2020-07-01, using the active taxkey on 2020-06-30 as a template, but linking to a tax with a different tax rate + -- 2. create new taxkey entry on 2021-01-01, using the active taxkey on 2020-06-30 as a template, but with the new date + + + -- fetch tax information for 2020-06-30, one day before the change, this should also be the first entry in the ordered array aggregates + -- this can be used as the template for the reset on 2021-01-01 + + -- raise notice 'looking up current taxkey for chart % and taxkey %', (select accno from chart where id = _chart_id), _taxkey_ids[1]; + select into current_taxkey tk.*, t.rate, t.taxkey + from taxkeys tk + left join tax t on (t.id = tk.tax_id) + where tk.taxkey_id = _taxkey_ids[1] -- assume taxkey never changed, use the first one + and tk.chart_id = _chart_id + and tk.startdate <= '2020-06-30' + order by tk.startdate desc + limit 1; + -- RAISE NOTICE 'found current_taxkey = %', current_taxkey; + IF current_taxkey is null then continue; end if; + -- RAISE NOTICE 'found chart % with current startdate % and taxkey % (current: %), rate = %', _accno, current_taxkey.startdate, _taxkey_ids[1], current_taxkey.taxkey, current_taxkey.rate; + + -- RAISE NOTICE 'current_taxkey = %', current_taxkey; + -- RAISE NOTICE 'looking up tkc for chart_id % and taxkey %', _chart_id, current_taxkey.taxkey; + + select into _taxkey, _old_rate, _old_chart, _new_chart, _new_rate + taxkey, old_rate, old_chart, new_chart, new_rate + from temp_taxkey_conversions tkc + where tkc.taxkey = current_taxkey.taxkey + and tkc.old_rate = current_taxkey.rate; + -- and tkc.new_chart = current_taxkey.new_chart; + + -- raise notice '_old_rate = %, _new_rate = %', _old_rate, _new_rate; + + -- don't do anything if current taxrate is 0, which might be the case for taxkey 13, if they were configured in that way + IF current_taxkey.rate != 0 THEN -- debug + + -- _rate := null; + + -- IF current_taxkey.rate = 0.19 THEN _rate := 0.16; END IF; + -- IF current_taxkey.rate = 0.07 THEN _rate := 0.05; END IF; + IF _old_rate is NULL THEN + + -- option A: ignore rates which don't make sense, useful for upgrade mode + -- option B: throw exception, useful for manually testing script + + -- A: + -- if the rate on 2020-06-30 is neither 19 or 7, simply ignore it, it is obviously not configured correctly + -- This is the case for SKR03 and chart 8315 (taxkey 13) + -- It might be better to throw an exception, however then the test cases don't run. Or just fix the chart via an upgrade script! + CONTINUE; + + -- B: + -- RAISE EXCEPTION 'illegal current taxrate % on 2020-06-30 (startdate = %) for chart % with taxkey %, should be either 0.19 or 0.07', + -- current_taxkey.rate, current_taxkey.startdate, + -- (select accno from chart where id = current_taxkey.chart_id), + -- current_taxkey.taxkey_id; + END IF; + -- RAISE NOTICE 'current_taxkey.rate = %, desired rate = %, looking for taxkey_id %', current_taxkey.rate, _rate, _taxkey_ids[1]; + + -- if a chart was created way after 2007 and only ever configured for + -- 19%, never 16%, which is the case for SKR04 and taxkey 13, there will only be 3 + -- taxkeys per chart after adding the two new ones + + -- RAISE NOTICE 'searching for tax with taxkey % and rate %', _taxkey_ids[1], _rate; + select into _tax + * + from tax + where tax.rate = _old_rate + and tax.taxkey = _taxkey_ids[1] + order by itime desc + limit 1; -- look up tax with same taxkey but corresponding rate. As there will now be two entries for e.g. taxkey 9 with rate of 0.16, the old pre-2007 entry and the new 2020-entry. They can only be differentiated by their (automatic tax) chart_id, or during this upgrade script, via itime, use the later one + -- this also assumes taxkeys never change + -- RAISE NOTICE 'tax = %', _tax; + + -- insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + -- values ( (select id from chart where accno = 'kkkkgtkttttkk current_taxkey.chart_id, _tax.id, _tax.taxkey, current_taxkey.pos_ustva, '2020-07-01'); + END IF; + + -- raise notice 'inserting taxkey'; + insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate ) + values (_chart_id, + (select id from tax where taxkey = current_taxkey.taxkey and rate = _new_rate::numeric), + current_taxkey.taxkey, -- 2, 3, 8, 9 + current_taxkey.pos_ustva, '2020-07-01'); + + -- finally insert a copy of the taxkey on 2020-06-30 with the new startdate 2021-01-01, thereby resetting the tax rates again + insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + values (_chart_id, + current_taxkey.tax_id, + current_taxkey.taxkey, + current_taxkey.pos_ustva, '2021-01-01'); + + -- RAISE NOTICE 'inserted 2 taxkeys for chart % with taxkey %', (select accno from chart where id = current_taxkey.chart_id), current_taxkey.taxkey_id; + END LOOP; -- +END IF; -- DATEV coa + +END $$; + +drop table temp_taxkey_conversions; + +-- select * from taxkeys where startdate >= '2020-01-01'; +-- rollback; diff --git a/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql b/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql new file mode 100644 index 000000000..8925d10c4 --- /dev/null +++ b/sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql @@ -0,0 +1,109 @@ +-- @tag: konjunkturpaket_2020_SKR03 +-- @description: Anpassung des Deutschen DATEV-Kontenrahmen für SKR03 Konjunkturpaket +-- @depends: release_3_5_5 +-- @ignore: 0 + +-- TODO: +-- how to deal with old 16% charts in SKR03? +-- A) move to their correct taxkeys, 5 and 8, like for SKR04 +-- and then create new versions of 3 and 9 with same taxkey +-- UST 5%, already exists in SKR03, so rename it, but also add new taxkeys + +-- DEBUG + +DO $$ +BEGIN + +IF ( select coa from defaults ) = 'Germany-DATEV-SKR03EU' THEN + + UPDATE tax SET taxdescription = 'OLD ' || taxdescription WHERE (taxkey = 3 or taxkey = 9) and rate = 0.16; + + -- rename some of the charts + UPDATE chart SET description = 'Umsatzsteuer 5 %' where accno = '1773'; + + -- rename charts if they weren't 't already changed + UPDATE chart SET description = 'Erlöse 19 % / 16 % USt' where accno = '8400' and description = 'Erlöse 16%/19% USt.'; + UPDATE chart SET description = 'Erlöse 7 % / 5 % USt' where accno = '8300' and description = 'Erlöse 7%USt'; + + -- move old 16% taxkeys to their proper taxkeys, should be 5 and 7 + UPDATE tax SET taxkey = 5 WHERE taxkey = 3 and rate = 0.16; + UPDATE tax SET taxkey = 7 WHERE taxkey = 9 and rate = 0.16; + + + -- new charts + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik) + VALUES ('8732','Gewährte Skonti 5% USt','A', 'I', 'AR_paid', 2, 1, null,1, 't'); + + -- SKR03 + -- datev reactivated the previously reserved chart 1775 in 2020, but it still exists in kivitendo (at least for SKR03) with a taxkey starting from 2007 and pointing to the existing automatic tax chart 1775 + -- so we don't need to to anything! + -- 3 | 0.16000 | Umsatzsteuer | 1775 | Umsatzsteuer 16% + + -- rename old 8735 to 8736 + UPDATE chart SET accno = '8736', description = 'Gewährte Skonti 19 % USt' where accno = '8735' and description = 'Gewährte Skonti 16%/19% USt.'; + -- create new 8735 with 16% + + -- create new 16% chart for Gewährte Skonti + INSERT INTO chart(accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er) + VALUES ('8735','Gewährte Skonti 16 % USt', 'A', 'I', 'AR_paid', 3, 1, null, 1, 't', 1); + + -- create new chart for Abziehbare Vorsteuer 5 % with taxkey 8 + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er) + VALUES ('1568','Abziehbare Vorsteuer 5 %','A', 'E', 'AP_tax:IC_taxpart:IC_taxservice', 8, null, null, 27, 't', 27); + + -- taxkeys can't be inserted until the new taxes exist + + INSERT INTO tax (chart_id, rate, taxkey, taxdescription, chart_categories, skonto_sales_chart_id, skonto_purchase_chart_id) + VALUES ( (select id from chart where accno = '1773'), 0.05, 2, 'Umsatzsteuer', 'I', (select id from chart where accno = '8732'), null), + ( (select id from chart where accno = '1775'), 0.16, 3, 'Umsatzsteuer', 'I', (select id from chart where accno = '8735'), null), + ( (select id from chart where accno = '1575'), 0.16, 9, 'Vorsteuer', 'E', null, (select id from chart where accno = '3735')), + ( (select id from chart where accno = '1568'), 0.05, 8, 'Vorsteuer', 'E', null, (select id from chart where accno = '3732')); + + UPDATE tax SET skonto_sales_chart_id = (select id from chart where accno = '8735') where taxkey = 3 and rate = 0.16 and skonto_sales_chart_id is null; + + -- new taxkeys for 5% and 16% only need one startdate, not valid before and won't change back to anything later + -- these taxkeys won't be valid on 2020-06-30, so won't be affected later by big taxkeys update + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + VALUES ( (select id from chart where accno = '8732'), (select id from tax where rate = 0.05 and taxkey = 2 and chart_id = (select id from chart where accno = '1773')), 2, 861, '2020-07-01'); + + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + VALUES ( (select id from chart where accno = '8735'), (select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775')), 3, 81, '2020-07-01'); + + -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + -- VALUES ( (select id from chart where accno = '8400'), (select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775')), 3, 81, '2020-07-01'); -- is 81 correct, or 51? + + -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + -- VALUES ( (select id from chart where accno = '8400'), (select id from tax where rate = 0.19 and taxkey = 3 and chart_id = (select id from chart where accno = '1776')), 3, 81, '2021-01-01'); + + -- the taxkeys for the existing charts will be updated in a later update +END IF; + +END $$; + + +-- select t.taxkey, +-- t.rate, +-- t.taxdescription, +-- c.accno, +-- c.description +-- from tax t +-- left join chart c on (c.id = t.chart_id) +--order by t.taxkey; +-- taxkey | rate | taxdescription | accno | description +----------+---------+--------------------------------------------------------------+-------+------------------------------------------------ +-- 0 | 0.00000 | Keine Steuer | ☠ | ☠ +-- 1 | 0.00000 | USt-frei | ☠ | ☠ +-- 2 | 0.07000 | Umsatzsteuer | 1771 | Umsatzsteuer 7% +-- 3 | 0.19000 | Umsatzsteuer | 1776 | Umsatzsteuer 19 % +-- 3 | 0.16000 | Umsatzsteuer | 1775 | Umsatzsteuer 16% +-- 8 | 0.07000 | Vorsteuer | 1571 | Abziehbare Vorsteuer 7% +-- 9 | 0.16000 | Vorsteuer | 1575 | Abziehbare Vorsteuer 16% +-- 9 | 0.19000 | Vorsteuer | 1576 | Abziehbare Vorsteuer 19 % +-- 10 | 0.00000 | Im anderen EU-Staat steuerpflichtige Lieferung | 1767 | Im anderen EG-Staat steuerpfl. Lieferung +-- 11 | 0.00000 | Steuerfreie innergem. Lieferung an Abnehmer mit Id.-Nr. | ☠ | ☠ +-- 12 | 0.07000 | Steuerpflichtige EG-Lieferung zum ermäßigten Steuersatz | 1772 | Umsatzsteuer 7% innergem.Erwerb +-- 13 | 0.19000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz | 1774 | Umsatzsteuer aus innergem. Erwerb 19 % +-- 13 | 0.16000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz | 1773 | Umsatzsteuer 16% innergem.Erwerb +-- 18 | 0.07000 | Steuerpflichtiger innergem. Erwerb zum ermäßigten Steuersatz | 1572 | Abziehbare Vorsteuer 7% innergem. Erwerb +-- 19 | 0.19000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz | 1574 | Abziehbare Vorsteuer aus innergem. Erwerb 19 % +-- 19 | 0.16000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz | 1572 | Abziehbare Vorsteuer 7% innergem. Erwerb diff --git a/sql/Pg-upgrade2/konjunkturpaket_2020_SKR04.sql b/sql/Pg-upgrade2/konjunkturpaket_2020_SKR04.sql new file mode 100644 index 000000000..8df8e8261 --- /dev/null +++ b/sql/Pg-upgrade2/konjunkturpaket_2020_SKR04.sql @@ -0,0 +1,128 @@ +-- @tag: konjunkturpaket_2020_SKR04 +-- @description: Anpassung des Deutschen DATEV-Kontenrahmen für SKR04 Konjunkturpaket +-- @depends: release_3_5_5 +-- @ignore: 0 + +-- TODO: +-- how to deal with old 16% charts in SKR03? +-- A) move to their correct taxkeys, 5 and 8, like for SKR04 +-- and then create new versions of 3 and 9 with same taxkey +-- UST 5%, already exists in SKR03, so rename it, but also add new taxkeys + + +DO $$ +BEGIN + +IF ( select coa from defaults ) = 'Germany-DATEV-SKR04EU' THEN + + -- DEBUG + + UPDATE chart set description = 'Abziehbare Vorsteuer 5 %', taxkey_id = 8 where accno = '1403' and description = 'Abziehbare Vorsteuer aus innergemeinschftl. Erwerb 16%'; + + UPDATE chart set description = 'Umsatzsteuer 5 %', taxkey_id = 2 where accno = '3803' and description = 'Umsatzsteuer aus innergemeinschftl. Erwerb 16%'; + + -- create new chart for Abziehbare Vorsteuer 5 % with taxkey 8 + -- INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er) + -- VALUES ('1403','Abziehbare Vorsteuer 5 %','A', 'E', 'AP_tax:IC_taxpart:IC_taxservice', 8, null, null, 27, 'f', 27); + + UPDATE tax SET taxdescription = 'OLD ' || taxdescription WHERE (taxkey = 5 or taxkey = 7); -- and rate = 0.16; + + UPDATE taxkeys SET tax_id = (SELECT id FROM tax WHERE taxkey = 5 and rate = 0.16) + WHERE chart_id = (SELECT id FROM chart where accno = '4400') + AND startdate = '1970-01-01'; + + -- rename charts if they weren't 't already changed + -- UPDATE chart SET description = 'Erlöse 19 % / 16 % USt' where accno = '8400' and description = 'Erlöse 16%/19% USt.'; + -- UPDATE chart SET description = 'Erlöse 7 % / 5 % USt' where accno = '8300' and description = 'Erlöse 7%USt'; + + -- move old 16% taxkeys to their proper taxkeys, should be 5 and 7 + -- UPDATE tax SET taxkey = 5 WHERE taxkey = 3 and rate = 0.16; + -- UPDATE tax SET taxkey = 7 WHERE taxkey = 9 and rate = 0.16; + + + -- new charts for 5% + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik) + VALUES ('4732','Gewährte Skonti 5 % USt','A', 'I', 'AR_paid', 2, 1, null, 1, 't'); + INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik) + VALUES ('5732','Erhaltene Skonti 5 % Vorsteuer','A', 'E', 'AP_paid', 8, 4, null, null, 't'); + + + -- SKR03 + -- datev reactivated the previously reserved chart 1775 in 2020, but it still exists in kivitendo (at least for SKR03) with a taxkey starting from 2007 and pointing to the existing automatic tax chart 1775 + -- so we don't need to to anything! + -- 3 | 0.16000 | Umsatzsteuer | 1775 | Umsatzsteuer 16% + + -- rename old 8735 to 8736 + -- UPDATE chart SET accno = '8736', description = 'Gewährte Skonti 19 % USt' where accno = '8735' and description = 'Gewährte Skonti 16%/19% USt.'; + -- create new 8735 with 16% + + -- create new 16% chart for Gewährte Skonti + INSERT INTO chart(accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er) + VALUES ('8735','Gewährte Skonti 16 % USt', 'A', 'I', 'AR_paid', 3, 1, null, 1, 't', 1); + + + -- taxkeys can't be inserted until the new taxes exist + + INSERT INTO tax (chart_id, rate, taxkey, taxdescription, chart_categories, skonto_sales_chart_id, skonto_purchase_chart_id) + VALUES ( (select id from chart where accno = '3803'), 0.05, 2, 'Umsatzsteuer', 'I', (select id from chart where accno = '4732'), null), -- ok + ( (select id from chart where accno = '3805'), 0.16, 3, 'Umsatzsteuer', 'I', (select id from chart where accno = '4735'), null), + ( (select id from chart where accno = '1405'), 0.16, 9, 'Vorsteuer', 'E', null, (select id from chart where accno = '5735')), + ( (select id from chart where accno = '1403'), 0.05, 8, 'Vorsteuer', 'E', null, (select id from chart where accno = '5732')); + + -- UPDATE tax SET skonto_sales_chart_id = (select id from chart where accno = '8735') where taxkey = 3 and rate = 0.16 and skonto_sales_chart_id is null; + + -- new taxkeys for 5% and 16% only need one startdate, not valid before and won't change back to anything later + -- these taxkeys won't be valid on 2020-06-30, so won't be affected later by big taxkeys update + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + VALUES ( (select id from chart where accno = '4732'), + ( select id from tax where rate = 0.05 and taxkey = 2 and chart_id = (select id from chart where accno = '3803')), 2, 861, '2020-07-01'); -- is ustva correct? + + INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + VALUES ( (select id from chart where accno = '5732'), + (select id from tax where rate = 0.05 and taxkey = 8 and chart_id = (select id from chart where accno = '1403')), 8, 861, '2020-07-01'); -- is ustva correct? + -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + -- VALUES ( (select id from chart where accno = '8735'), (select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775')), 3, 81, '2020-07-01'); + + -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + -- VALUES ( (select id from chart where accno = '8400'), (select id from tax where rate = 0.16 and taxkey = 3 and chart_id = (select id from chart where accno = '1775')), 3, 81, '2020-07-01'); -- is 81 correct, or 51? + + -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate) + -- VALUES ( (select id from chart where accno = '8400'), (select id from tax where rate = 0.19 and taxkey = 3 and chart_id = (select id from chart where accno = '1776')), 3, 81, '2021-01-01'); + + -- the taxkeys for the existing charts will be updated in a later update +END IF; + +END $$; + + +-- do the same for all other accounts linked to 9 + + +-- select t.taxkey, +-- t.rate, +-- t.taxdescription, +-- c.accno, +-- c.description +-- from tax t +-- left join chart c on (c.id = t.chart_id) +-- ; +-- taxkey | rate | taxdescription | accno | description +-- --------+---------+--------------------------------------------------------------+-------+-------------------------------------------------------- +-- 8 | 0.07000 | Vorsteuer | 1401 | Abziehbare Vorsteuer 7% +-- 18 | 0.07000 | Steuerpflichtiger innergem. Erwerb zum ermäßigten Steuersatz | 1402 | Abziehbare Vorsteuer aus innergemeinschftl. Erwerb +-- 17 | 0.16000 | Steuerpflicht. EG-Erwerb | 1403 | Abziehbare Vorsteuer aus innergemeinschftl. Erwerb 16% +-- 19 | 0.19000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz | 1404 | Abziehbare Vorsteuer aus innergemeinschftl. Erwerb 19% +-- 7 | 0.16000 | Vorsteuer | 1405 | Abziehbare Vorsteuer 16% +-- 9 | 0.19000 | Vorsteuer | 1406 | Abziehbare Vorsteuer 19 % +-- 9 | 0.19000 | Vorsteuer | 1406 | Abziehbare Vorsteuer 19 % +-- 2 | 0.07000 | Umsatzsteuer | 3801 | Umsatzsteuer 7% +-- 12 | 0.07000 | Steuerpflichtige EG-Lieferung zum ermäßigten Steuersatz | 3802 | Umsatzsteuer aus innergemeinschftl. Erwerb +-- 15 | 0.16000 | Steuerpflicht. EG-Lieferungen% | 3803 | Umsatzsteuer aus innergemeinschftl. Erwerb 16% +-- 13 | 0.19000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz | 3804 | Umsatzsteuer aus innergemeinschftl. Erwerb 19% +-- 5 | 0.16000 | Umsatzsteuer | 3805 | Umsatzsteuer 16% +-- 3 | 0.19000 | Umsatzsteuer | 3806 | Umsatzsteuer 19% +-- 3 | 0.19000 | Umsatzsteuer | 3806 | Umsatzsteuer 19% +-- 1 | 0.00000 | USt-frei | ☠ | ☠ +-- 11 | 0.00000 | Steuerfreie innergem. Lieferung an Abnehmer mit Id.-Nr. | ☠ | ☠ +-- 0 | 0.00000 | Keine Steuer | ☠ | ☠ +-- 10 | 0.00000 | Im anderen EU-Staat steuerpflichtige Lieferung | ☠ | ☠ diff --git a/t/tax/tax.t b/t/tax/tax.t new file mode 100644 index 000000000..4096b2abb --- /dev/null +++ b/t/tax/tax.t @@ -0,0 +1,542 @@ +use Test::More tests => 38; +use Test::Deep qw(cmp_deeply); + +use strict; + +use lib 't'; +use utf8; + +use Support::TestSetup; +use Test::Exception; + +use SL::DB::Customer; +use SL::DB::Vendor; +use SL::DB::Invoice; +use SL::DB::GLTransaction; +use SL::DB::Part; +use SL::DBUtils qw(selectall_hashref_query); +use SL::Dev::Record qw(:ALL); +use SL::Dev::CustomerVendor qw(new_customer new_vendor); +use SL::Dev::Part qw(new_part); +use Data::Dumper; + +Support::TestSetup::login(); +my $dbh = SL::DB->client->dbh; + +my $test_kontenrahmen = 'skr03'; + +clear_up(); + +# TODOs: Storno muß noch korrekt funktionieren +# neue Konten für 5% anlegen +# Leistungszeitraum vs. Datum Zuord. Steuerperiodest + +note('checking if all tax entries exist for Konjunkturprogramm'); + +# create dates to test on +my $date_2006 = DateTime->new(year => 2006, month => 6, day => 15); +my $date_2020_1 = DateTime->new(year => 2020, month => 6, day => 15); +my $date_2020_2 = DateTime->new(year => 2020, month => 7, day => 15); +my $date_2021 = DateTime->new(year => 2021, month => 1, day => 15); + +# The only way to discern the pre-2007 16% tax from the 2020 16% tax is by +# their configured automatic tax charts, so look them up here: + +my ($chart_vst_19, $chart_vst_16, $chart_vst_5, $chart_vst_7); +my ($chart_ust_19, $chart_ust_16, $chart_ust_5, $chart_ust_7); +my ($income_19_accno, $income_7_accno); +my ($ar_accno, $ap_accno); +my ($chart_reisekosten_accno, $chart_cash_accno, $chart_bank_accno); + + +if ( $test_kontenrahmen eq 'skr03' ) { + + is(SL::DB::Default->get->coa, 'Germany-DATEV-SKR03EU', "coa SKR03 ok"); + + $chart_vst_19 = '1776'; + $chart_vst_16 = '1775'; + $chart_vst_5 = '1773'; + $chart_vst_7 = '1771'; + + $chart_ust_19 = '1576'; + $chart_ust_16 = '1575'; + $chart_ust_5 = '1568'; + $chart_ust_7 = '1571'; + + $income_19_accno = '8400'; + $income_7_accno = '8300'; + + $chart_reisekosten_accno = 4660; + $chart_cash_accno = 1000; + + $ar_accno = 1400; + $ap_accno = 1600; + +} elsif ( $test_kontenrahmen eq 'skr04') { # skr04 - test can be ran manually by running t/000setup_database.t with coa for SKR04 + is(SL::DB::Default->get->coa, 'Germany-DATEV-SKR04EU', "coa SKR04 ok"); + $chart_ust_19 = '1406'; + $chart_ust_16 = '1405'; + $chart_ust_5 = '1403'; + $chart_ust_7 = '1401'; + + $chart_vst_19 = '3806'; + $chart_vst_16 = '3805'; + $chart_vst_5 = '3803'; + $chart_vst_7 = '3801'; + + $income_19_accno = '4400'; + $income_7_accno = '4300'; + + $chart_reisekosten_accno = 6650; + $chart_cash_accno = 1600; + + $ar_accno = 1200; + $ap_accno = 3300; +} + +my $tax_vst_19 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_19) or die; # 19% +my $tax_vst_16 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_16) or die; # 16% +my $tax_vst_5 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_5 ) or die; # 5% +my $tax_vst_7 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_7 ) or die; # 7% + +my $tax_ust_19 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_19) or die; # 19% +my $tax_ust_16 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_16) or die; # 16% +my $tax_ust_5 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_5) or die; # 5% +my $tax_ust_7 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_7) or die; # 7% + +my $chart_income_19 = SL::DB::Manager::Chart->find_by(accno => $income_19_accno) or die; +my $chart_income_7 = SL::DB::Manager::Chart->find_by(accno => $income_7_accno) or die; + +my $chart_reisekosten = SL::DB::Manager::Chart->find_by(accno => $chart_reisekosten_accno) or die; +my $chart_cash = SL::DB::Manager::Chart->find_by(accno => $chart_cash_accno) or die; + +is(defined SL::DB::Manager::Tax->find_by(taxkey => 2, rate => 0.05), 1, "tax for taxkey 2 with 5% was created ok"); +is(defined SL::DB::Manager::Tax->find_by(taxkey => 3, rate => 0.16, chart_id => $tax_vst_16->id), 1, "new sales tax for taxkey 3 with 16% exists ok"); +is(defined SL::DB::Manager::Tax->find_by(taxkey => 3, rate => 0.19, chart_id => $tax_vst_19->id), 1, "old sales tax for taxkey 3 with 19% exists ok"); +is(defined SL::DB::Manager::Tax->find_by(taxkey => 5, rate => 0.16, chart_id => $tax_vst_16->id), 1, "new sales tax for taxkey 5 with 16% exists ok"); + +is(defined SL::DB::Manager::Tax->find_by(taxkey => 7, rate => 0.16, chart_id => $tax_ust_16->id), 1, "old purchase tax for taxkey 7 with 16% exists ok"); +# is(defined SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.07, chart_id => $tax_ust_16->id), 1, "old purchase tax for taxkey 7 with 16% exists ok"); +is(defined SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19, chart_id => $tax_ust_19->id), 1, "old purchase tax for taxkey 9 with 19% exists ok"); +is(defined SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.16, chart_id => $tax_ust_16->id), 1, "new purchase tax for taxkey 9 with 16% exists ok"); + +my $vendor = new_vendor( name => 'Testvendor')->save; +my $customer = new_customer(name => 'Testcustomer')->save; + +# cmp_ok($chart_income_7->get_active_taxkey($date_2020_1)->tax->rate, '==', 0.07, "get_active_taxkey rate for 8300 in 2020_1 ok"); +# cmp_ok($chart_income_7->get_active_taxkey($date_2020_2)->tax->rate, '==', 0.05, "get_active_taxkey rate for 8300 in 2020_2 ok"); +# cmp_ok($chart_income_7->get_active_taxkey($date_2021 )->tax->rate, '==', 0.07, "get_active_taxkey rate for 8300 in 2021 ok"); +cmp_ok($chart_income_7->get_active_taxkey($date_2020_1)->tax->rate, '==', 0.07, "get_active_taxkey rate for $income_7_accno in 2020_1 ok"); +cmp_ok($chart_income_7->get_active_taxkey($date_2020_2)->tax->rate, '==', 0.05, "get_active_taxkey rate for $income_7_accno in 2020_2 ok"); +cmp_ok($chart_income_7->get_active_taxkey($date_2021 )->tax->rate, '==', 0.07, "get_active_taxkey rate for $income_7_accno in 2021 ok"); +cmp_ok($chart_income_7->get_active_taxkey($date_2006 )->tax->rate, '==', 0.07, "get_active_taxkey rate for $income_7_accno in 2016 ok"); + +cmp_ok($chart_income_19->get_active_taxkey($date_2020_1)->tax->rate, '==', 0.19, "get_active_taxkey rate for $income_19_accno in 2020_1 ok"); +cmp_ok($chart_income_19->get_active_taxkey($date_2020_2)->tax->rate, '==', 0.16, "get_active_taxkey rate for $income_19_accno in 2020_2 ok"); +cmp_ok($chart_income_19->get_active_taxkey($date_2021 )->tax->rate, '==', 0.19, "get_active_taxkey rate for $income_19_accno in 2021 ok"); +cmp_ok($chart_income_19->get_active_taxkey($date_2006 )->tax->rate, '==', 0.16, "get_active_taxkey rate for $income_19_accno in 2016 ok"); + +my $bugru19 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 19%') or die "Can't find bugru19"; +my $bugru7 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 7%' ) or die "Can't find bugru7"; + +my $part1 = new_part(partnumber => '1', description => 'part19', buchungsgruppen_id => $bugru19->id)->save; +my $part2 = new_part(partnumber => '2', description => 'part7', buchungsgruppen_id => $bugru7->id )->save; + +note('sales invoices'); +my $sales_invoice_2006 = create_invoice_for_date('2006', $date_2006); +my $sales_invoice_2020_1 = create_invoice_for_date('2020_1', $date_2020_1); +my $sales_invoice_2020_2 = create_invoice_for_date('2020_2', $date_2020_2); +my $sales_invoice_2021 = create_invoice_for_date('2021', $date_2021); + +is($sales_invoice_2006->amount, 223, '2006 sales invoice has 16% and 7% tax ok' ); # 116 + 7 +is($sales_invoice_2020_1->amount, 226, '2020_01 sales invoice has 19% and 7% tax ok'); # 119 + 7 +is($sales_invoice_2020_2->amount, 221, '2020_02 sales invoice has 16% and 5% tax ok'); # 116 + 5 +is($sales_invoice_2021->amount, 226, '2021 sales invoice has 19% and 7% tax ok' ); # 119 + 7 + +&datev_test($sales_invoice_2020_2, + [ + { + 'belegfeld1' => 'test is 2020_2', + 'buchungstext' => 'Testcustomer', + 'datum' => '15.07.2020', + 'leistungsdatum' => '15.07.2020', # should leistungsdatum be empty if it doesn't exist? + 'gegenkonto' => $income_7_accno, + 'konto' => $ar_accno, + 'kost1' => undef, + 'kost2' => undef, + 'locked' => undef, + 'umsatz' => 105, + 'waehrung' => 'EUR' + }, + { + 'belegfeld1' => 'test is 2020_2', + 'buchungstext' => 'Testcustomer', + 'datum' => '15.07.2020', + 'leistungsdatum' => '15.07.2020', + 'gegenkonto' => $income_19_accno, + 'konto' => $ar_accno, + 'kost1' => undef, + 'kost2' => undef, + 'locked' => undef, + 'umsatz' => 116, + 'waehrung' => 'EUR' + } + ], + "datev check for 16/5 ok, no taxkey" +); + +note('sales invoice with differing delivery dates'); +my $sales_invoice_2020_1_with_delivery_date_2020_2 = create_invoice_for_date('deliverydate 2020_1', $date_2020_1, $date_2020_2); +is($sales_invoice_2020_1_with_delivery_date_2020_2->amount, 221, "sales_invoice from 2020_1 with future delivery_date 2020_2 tax ok"); + +my $sales_invoice_2020_2_with_delivery_date_2020_1 = create_invoice_for_date('deliverydate 2020_2', $date_2020_2, $date_2020_1); +is($sales_invoice_2020_2_with_delivery_date_2020_1->amount, 226, "sales_invoice from 2020_2 with past delivery_date 2020_1 tax ok"); + +&datev_test($sales_invoice_2020_2_with_delivery_date_2020_1, + [ + { + 'belegfeld1' => 'test is deliverydate 2020_2', + 'buchungstext' => 'Testcustomer', + 'datum' => '15.07.2020', + 'gegenkonto' => $income_7_accno, + 'konto' => $ar_accno, + 'kost1' => undef, + 'kost2' => undef, + 'leistungsdatum' => '15.06.2020', + 'locked' => undef, + 'umsatz' => 107, + 'waehrung' => 'EUR' + }, + { + 'belegfeld1' => 'test is deliverydate 2020_2', + 'buchungstext' => 'Testcustomer', + 'datum' => '15.07.2020', + 'gegenkonto' => $income_19_accno, + 'konto' => $ar_accno, + 'kost1' => undef, + 'kost2' => undef, + 'leistungsdatum' => '15.06.2020', + 'locked' => undef, + 'umsatz' => 119, + 'waehrung' => 'EUR' + } + ], + "datev check for datev export with delivery_date 19/7 ok, no taxkey" +); + +my $sales_invoice_2021_with_delivery_date_2020_2 = create_invoice_for_date('deliverydate 2020_2', $date_2021, $date_2020_2); +is($sales_invoice_2021_with_delivery_date_2020_2->amount, 221, "sales_invoice from 2021 with past delivery_date 2020_2 tax ok"); + +my $sales_invoice_2020_2_with_delivery_date_2021 = create_invoice_for_date('deliverydate 2021', $date_2020_2, $date_2021); +is($sales_invoice_2020_2_with_delivery_date_2021->amount, 226, "sales_invoice from 2020_2 with future delivery_date 2021 tax ok"); + + +note('ap transactions'); +# in the test we want to test for Reisekosten with 19% and 7%. Normally the user +# would select the entries from the dropdown, as they may differ from the +# default, so we have to pass the tax we want to create_ap_transaction + +my $tax_9_16_old = SL::DB::Manager::Tax->find_by(taxkey => 7, rate => 0.16, chart_id => $tax_ust_16->id); +my $tax_9_19 = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19, chart_id => $tax_ust_19->id); +my $tax_9_16 = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.16, chart_id => $tax_ust_16->id); +my $tax_8_7 = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.07, chart_id => $tax_ust_7->id); +my $tax_8_5 = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.05, chart_id => $tax_ust_5->id); + +# simulate user selecting the "correct" taxes in dropdown: +my $ap_transaction_2006 = create_ap_transaction_for_date('2006', $date_2006, undef, $tax_9_16_old, $tax_8_7); +my $ap_transaction_2020_1 = create_ap_transaction_for_date('2020_1', $date_2020_1, undef, $tax_9_19, $tax_8_7); +my $ap_transaction_2020_2 = create_ap_transaction_for_date('2020_2', $date_2020_2, undef, $tax_9_16, $tax_8_5); +my $ap_transaction_2021 = create_ap_transaction_for_date('2021', $date_2021, undef, $tax_9_19, $tax_8_7); + + +is($ap_transaction_2006->amount, 223, '2006 ap transaction has 16% and 7% tax ok'); # 116 + 7 +is($ap_transaction_2020_1->amount, 226, '2020_01 ap transaction has 19% and 7% tax ok'); # 119 + 7 +is($ap_transaction_2020_2->amount, 221, '2020_02 ap transaction has 16% and 5% tax ok'); # 116 + 5 +is($ap_transaction_2021->amount, 226, '2021 ap transaction has 19% and 7% tax ok'); # 119 + 7 + +# ap transaction in july, but use old tax +my $ap_transaction_2020_2_with_delivery_date_2020_1 = create_ap_transaction_for_date('2020_2 with delivery date 2020_1', $date_2020_2, $date_2020_1, $tax_9_19, $tax_8_7); +is($ap_transaction_2020_2_with_delivery_date_2020_1->amount, 226, 'ap transaction 2020_2 with delivery date 2020_1, 19% and 7% tax ok'); # 119 + 7 +&datev_test($ap_transaction_2020_2_with_delivery_date_2020_1, + [ + { + 'belegfeld1' => 'test ap_transaction 2020_2 with delivery date 2020_1', + 'buchungsschluessel' => 8, + 'buchungstext' => 'Testvendor', + 'datum' => '15.07.2020', + 'gegenkonto' => $ap_accno, + 'konto' => $chart_reisekosten_accno, + 'kost1' => undef, + 'kost2' => undef, + 'leistungsdatum' => '15.06.2020', + 'locked' => undef, + 'umsatz' => 107, + 'waehrung' => 'EUR' + }, + { + 'belegfeld1' => 'test ap_transaction 2020_2 with delivery date 2020_1', + 'buchungsschluessel' => 9, + 'buchungstext' => 'Testvendor', + 'datum' => '15.07.2020', + 'gegenkonto' => $ap_accno, + 'konto' => $chart_reisekosten_accno, + 'kost1' => undef, + 'kost2' => undef, + 'leistungsdatum' => '15.06.2020', + 'locked' => undef, + 'umsatz' => 119, + 'waehrung' => 'EUR' + } + ], + "datev check for ap transaction 2020_2 with delivery date 2020_1, 19% and 7% tax ok" +); + +note('ar transactions'); + +my $ar_transaction_2006 = create_ar_transaction_for_date('2006', $date_2006); +my $ar_transaction_2020_1 = create_ar_transaction_for_date('2020_1', $date_2020_1); +my $ar_transaction_2020_2 = create_ar_transaction_for_date('2020_2', $date_2020_2); +my $ar_transaction_2021 = create_ar_transaction_for_date('2021', $date_2021); + +is($ar_transaction_2006->amount, 223, '2006 ar transaction has 16% and 7% tax ok'); # 116 + 7 +is($ar_transaction_2020_1->amount, 226, '2020_01 ar transaction has 19% and 7% tax ok'); # 119 + 7 +is($ar_transaction_2020_2->amount, 221, '2020_02 ar transaction has 16% and 5% tax ok'); # 116 + 5 +is($ar_transaction_2021->amount, 226, '2021 ar transaction has 19% and 7% tax ok'); # 119 + 7 + +note('gl transactions'); + +my $gl_2006 = create_gl_transaction_for_date('glincome 2006', $date_2006, 223); +my $gl_2020_1 = create_gl_transaction_for_date('glincome 2020_1', $date_2020_1, 226); +my $gl_2020_2 = create_gl_transaction_for_date('glincome 2020_2', $date_2020_2, 221); +my $gl_2021 = create_gl_transaction_for_date('glincome 2021', $date_2021, 226); + +is(SL::DB::Manager::GLTransaction->get_all_count(), 4, "4 gltransactions created correctly"); + +my $result = &get_account_balances; +# print Dumper($result); +is_deeply( &get_account_balances, + [ + # { + # 'accno' => '1000', + # # 'description' => 'Kasse', + # 'sum' => '-896.00000' + # }, + # { + # 'accno' => '1400', + # # 'description' => 'Ford. a.Lieferungen und Leistungen', + # 'sum' => '-2686.00000' + # }, + { + 'accno' => '1568', + # 'description' => 'Abziehbare Vorsteuer 7%', + 'sum' => '-5.00000' + }, + { + 'accno' => '1571', + # 'description' => 'Abziehbare Vorsteuer 7%', + 'sum' => '-28.00000' + }, + { + 'accno' => '1575', + # 'description' => 'Abziehbare Vorsteuer 16%', + 'sum' => '-32.00000' + }, + { + 'accno' => '1576', + # 'description' => 'Abziehbare Vorsteuer 19 %', + 'sum' => '-57.00000' + }, + # { + # 'accno' => '1600', + # # 'description' => 'Verbindlichkeiten aus Lief.u.Leist.', + # 'sum' => '896.00000' + # }, + { + 'accno' => '1771', + # 'description' => 'Umsatzsteuer 7%', + 'sum' => '77.00000' + }, + { + 'accno' => '1773', + # 'description' => 'Umsatzsteuer 5 %', + 'sum' => '25.00000' + }, + { + 'accno' => '1775', + # 'description' => 'Umsatzsteuer 16%', + 'sum' => '128.00000' + }, + { + 'accno' => '1776', + # 'description' => 'Umsatzsteuer 19 %', + 'sum' => '152.00000' + }, + # { + # 'accno' => '4660', + # # 'description' => 'Reisekosten Arbeitnehmer', + # 'sum' => '-800.00000' + # }, + # { + # 'accno' => $income_7_accno, + # # 'description' => "Erl\x{f6}se 7%USt", + # 'sum' => '1600.00000' + # }, + # { + # 'accno' => $income_19_accno, + # # 'description' => "Erl\x{f6}se 16%/19% USt.", + # 'sum' => '1600.00000' + # } + ], + 'account balances after invoices' +); + +clear_up(); + +done_testing(); + +###### functions for setting up data + +sub create_invoice_for_date { + my ($invnumber, $transdate, $deliverydate) = @_; + + $deliverydate = $transdate unless defined $deliverydate; + + my $sales_invoice = create_sales_invoice( + invnumber => 'test is ' . $invnumber, + transdate => $transdate, + customer => $customer, + deliverydate => $deliverydate, + taxincluded => 0, + invoiceitems => [ create_invoice_item(part => $part1, qty => 10, sellprice => 10), + create_invoice_item(part => $part2, qty => 10, sellprice => 10), + ] + ); + return $sales_invoice; +} + +sub create_ar_transaction_for_date { + my ($invnumber, $transdate) = @_; + + my $ar_transaction = create_ar_transaction( + customer => $customer, + invnumber => 'test ar' . $invnumber, + taxincluded => 0, + transdate => $transdate, + ar_chart => SL::DB::Manager::Chart->find_by(accno => $ar_accno), # pass ar_chart, as it is hardcoded for SKR03 in SL::Dev::Record + bookings => [ + { + chart => $chart_income_19, + amount => 100, + }, + { + chart => $chart_income_7, + amount => 100, + }, + ] + ); + return $ar_transaction; +} + +sub create_ap_transaction_for_date { + my ($invnumber, $transdate, $deliverydate, $tax_high, $tax_low) = @_; + + # printf("invnumber = %s tax_high = %s tax_low = %s\n", $invnumber, $tax_high->accno , $tax_low->accno); + my $taxkey_ = $chart_reisekosten->get_active_taxkey($transdate); + + my $ap_transaction = create_ap_transaction( + vendor => $vendor, + invnumber => 'test ap_transaction ' . $invnumber, + taxincluded => 0, + transdate => $transdate, + deliverydate => $deliverydate, + ap_chart => SL::DB::Manager::Chart->find_by(accno => $ap_accno), # pass ap_chart, as it is hardcoded for SKR03 in SL::Dev::Record + bookings => [ + { + chart => $chart_reisekosten, + amount => 100, + tax_id => $tax_high->id, + }, + { + chart => $chart_reisekosten, + amount => 100, + tax_id => $tax_low->id, + }, + ] + ); + return $ap_transaction; +} + +sub create_gl_transaction_for_date { + my ($reference, $transdate, $debitamount) = @_; + + my $gl_transaction = create_gl_transaction( + reference => $reference, + taxincluded => 0, + transdate => $transdate, + bookings => [ + { + chart => $chart_income_19, + memo => 'gl 19', + source => 'gl 19', + credit => 100, + }, + { + chart => $chart_income_7, + memo => 'gl 7', + source => 'gl 7', + credit => 100, + }, + { + chart => $chart_cash, + debit => $debitamount, + memo => 'gl 19+7', + source => 'gl 19+7', + }, + ], + ); + return $gl_transaction; +} + +sub get_account_balances { + my $query = <new( + dbh => $invoice->db->dbh, + trans_id => $invoice->id, + ); + + $datev->generate_datev_data; + my @data_datev = sort { $a->{umsatz} <=> $b->{umsatz} } @{ $datev->generate_datev_lines() }; + + # print Dumper(\@data_datev); + + cmp_deeply(\@data_datev, $expected_data, $msg); +} + +sub clear_up { + SL::DB::Manager::OrderItem->delete_all(all => 1); + SL::DB::Manager::Order->delete_all(all => 1); + SL::DB::Manager::InvoiceItem->delete_all(all => 1); + SL::DB::Manager::Invoice->delete_all(all => 1); + SL::DB::Manager::PurchaseInvoice->delete_all(all => 1); + SL::DB::Manager::GLTransaction->delete_all(all => 1); + SL::DB::Manager::Part->delete_all(all => 1); + SL::DB::Manager::Customer->delete_all(all => 1); + SL::DB::Manager::Vendor->delete_all(all => 1); +}; + +1; -- 2.20.1