Project

General

Profile

Revision 11a04bd5

Added by G. Richardson 4 months ago

Konjunkturpaket 2020 Anpassung der Mehrwertsteuersätze und Test

Ab dem 01.07.2020 sollen 16% und 5% gelten.
Ab dem 01.01.2021 sollen wieder 19% und 7% gelten.

View differences:

sql/Pg-upgrade2/konjunkturpaket_2020.sql
1
-- @tag: konjunkturpaket_2020
2
-- @description: Anpassung der Steuersätze für 16%/5% für Deutsche DATEV-Kontenrahmen SKR03 und SKR04
3
-- @depends: release_3_5_5 konjunkturpaket_2020_SKR03 konjunkturpaket_2020_SKR04
4
-- @ignore: 0
5

  
6
-- begin;
7

  
8
DO $$
9

  
10
DECLARE
11
  -- variables for main taxkey creation loop, not all are needed
12
  _chart_id int;
13
  _accno text;
14
  _description text;
15
  _startdates date[];
16
  _tax_ids int[];
17
  _taxkeyentry_id int[];
18
  _taxkey_ids int[];
19
  _rates numeric[];
20
  _taxcharts text[];
21

  
22
  current_taxkey record;
23
  new_taxkey     record;
24
  _rate          numeric;
25
  _tax           record; -- store the new tax we need to assign to a chart, e.g. 5%, 16%
26

  
27
  _taxkey    int;
28
  _old_rate  numeric;
29
  _old_chart text;
30
  _new_chart numeric;
31
  _new_rate  text;
32

  
33
  _tax_conversion record;
34

  
35

  
36
BEGIN
37

  
38
IF ( select coa from defaults ) ~ 'DATEV' THEN
39

  
40
--begin;
41
--delete from taxkeys where startdate >= '2020-01-01';
42

  
43
--  create temp table temp_taxkey_conversions (taxkey int, old_rate numeric, new_rate numeric, tax_chart_skr03 text, tax_chart_skr04 text);
44
--  insert into temp_taxkey_conversions (taxkey, old_rate, new_rate, tax_chart_skr03, tax_chart_skr04) values
45
----    (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%
46
--    -- (8, 0.07, 0.05, null, null),
47
--    -- (3, 0.19, 0.16, null, null),
48
--    -- (9, 0.19, 0.16, null, null),
49
--   (13, 0.19, 0.16, null, null);
50

  
51

  
52
create temp table temp_taxkey_conversions (taxkey int, old_rate numeric, old_chart text, new_rate numeric, new_chart text);
53

  
54
IF ( select coa from defaults ) = 'Germany-DATEV-SKR03EU' THEN
55
  insert into temp_taxkey_conversions (taxkey, old_rate, old_chart, new_rate, new_chart)
56
  values (9, 0.19, '1576', 0.16, '1575'),
57
         (8, 0.07, '1571', 0.05, '1568'),
58
         (3, 0.19, '1776', 0.16, '1575'),
59
         (2, 0.07, '1771', 0.05, '1775');
60
         --1776 => 19%
61
         --1775 => 16%
62
         --1775 =>  5%
63
         --1771 =>  7%
64
         --
65
         --VSt:
66
         --1576 => 19%
67
         --1575 => 16%
68
         --1568 =>  5%
69
         --1571 =>  7%
70

  
71
  ELSE  -- Germany-DATEV-SKR04EU
72
    insert into temp_taxkey_conversions (taxkey, old_rate, old_chart, new_rate, new_chart)
73
    values (9, 0.19, '1406', 0.16, '1405'),
74
           (8, 0.07, '1401', 0.05, '1403'),
75
           (3, 0.19, '3806', 0.16, '3805'),
76
           (2, 0.07, '3801', 0.05, '3803');
77
  END IF;
78

  
79
  FOR _chart_id, _accno, _description, _startdates, _tax_ids, _taxkeyentry_id, _taxkey_ids, _rates, _taxcharts IN
80

  
81
      select c.id as chart_id,
82
             c.accno,
83
             c.description,
84
             array_agg(t.startdate order by t.startdate desc) as startdates,
85
             array_agg(t.tax_id    order by t.startdate desc) as tax_ids,
86
             array_agg(t.id        order by t.startdate desc) as taxkeyentry_id,
87
             array_agg(t.taxkey_id order by t.startdate desc) as taxkey_ids,
88
             array_agg(tax.rate    order by t.startdate desc) as rates,
89
             array_agg(tc.accno    order by t.startdate desc) as taxcharts
90
        from taxkeys t
91
             left join chart c  on (c.id         = t.chart_id)
92
             left join tax      on (tax.id       = t.tax_id)
93
             left join chart tc on (tax.chart_id = tc.id)
94
       where t.taxkey_id in (select taxkey from temp_taxkey_conversions)  -- 2, 3, 8, 9
95
             -- and (c.accno = '8400') -- debug
96
             -- you can't filter for valid taxrates 19% or 7% here, as that would still leave the 16% rates as the current one
97
    group by c.id,
98
             c.accno,
99
             c.description
100
    order by c.accno
101

  
102
    -- example output for human debugging:
103
    --  chart_id | accno |     description     |       startdates        |  tax_ids  | taxkeyentry_id | taxkey_ids |       rates       |  taxcharts
104
    -- ----------+-------+---------------------+-------------------------+-----------+----------------+------------+-------------------+-------------
105
    --       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}
106

  
107
  -- 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
108
  LOOP
109
    -- 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
110
    -- 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
111

  
112

  
113
    -- fetch tax information for 2020-06-30, one day before the change, this should also be the first entry in the ordered array aggregates
114
    -- this can be used as the template for the reset on 2021-01-01
115

  
116
    -- raise notice 'looking up current taxkey for chart % and taxkey %', (select accno from chart where id = _chart_id), _taxkey_ids[1];
117
    select into current_taxkey tk.*, t.rate, t.taxkey
118
           from taxkeys tk
119
                left join tax t on (t.id = tk.tax_id)
120
          where     tk.taxkey_id = _taxkey_ids[1] -- assume taxkey never changed, use the first one
121
                and tk.chart_id = _chart_id
122
                and tk.startdate <= '2020-06-30'
123
       order by tk.startdate desc
124
          limit 1;
125
    -- RAISE NOTICE 'found current_taxkey = %', current_taxkey;
126
    IF current_taxkey is null then continue; end if;
127
    -- RAISE NOTICE 'found chart % with current startdate % and taxkey % (current: %), rate = %', _accno, current_taxkey.startdate, _taxkey_ids[1], current_taxkey.taxkey, current_taxkey.rate;
128

  
129
    -- RAISE NOTICE 'current_taxkey = %', current_taxkey;
130
    -- RAISE NOTICE 'looking up tkc for chart_id % and taxkey  %', _chart_id, current_taxkey.taxkey;
131

  
132
    select into _taxkey, _old_rate, _old_chart, _new_chart, _new_rate
133
                 taxkey,  old_rate,  old_chart,  new_chart,  new_rate
134
    from temp_taxkey_conversions tkc
135
    where     tkc.taxkey    = current_taxkey.taxkey
136
          and tkc.old_rate = current_taxkey.rate;
137
          -- and tkc.new_chart = current_taxkey.new_chart;
138

  
139
    -- raise notice '_old_rate = %, _new_rate = %', _old_rate, _new_rate;
140

  
141
    -- don't do anything if current taxrate is 0, which might be the case for taxkey 13, if they were configured in that way
142
    IF current_taxkey.rate != 0 THEN  -- debug
143

  
144
      -- _rate := null;
145

  
146
      -- IF current_taxkey.rate = 0.19 THEN _rate := 0.16; END IF;
147
      -- IF current_taxkey.rate = 0.07 THEN _rate := 0.05; END IF;
148
      IF _old_rate is NULL THEN
149

  
150
        -- option A: ignore rates which don't make sense, useful for upgrade mode
151
        -- option B: throw exception, useful for manually testing script
152

  
153
        -- A:
154
        -- if the rate on 2020-06-30 is neither 19 or 7, simply ignore it, it is obviously not configured correctly
155
        -- This is the case for SKR03 and chart 8315 (taxkey 13)
156
        -- 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!
157
        CONTINUE;
158

  
159
        -- B:
160
        -- RAISE EXCEPTION 'illegal current taxrate % on 2020-06-30 (startdate = %) for chart % with taxkey %, should be either 0.19 or 0.07',
161
        --                 current_taxkey.rate, current_taxkey.startdate,
162
        --                 (select accno from chart where id = current_taxkey.chart_id),
163
        --                 current_taxkey.taxkey_id;
164
      END IF;
165
      -- RAISE NOTICE 'current_taxkey.rate = %, desired rate = %, looking for taxkey_id %', current_taxkey.rate, _rate, _taxkey_ids[1];
166

  
167
			-- if a chart was created way after 2007 and only ever configured for
168
      -- 19%, never 16%, which is the case for SKR04 and taxkey 13, there will only be 3
169
      -- taxkeys per chart after adding the two new ones
170

  
171
      -- RAISE NOTICE 'searching for tax with taxkey % and rate %', _taxkey_ids[1], _rate;
172
      select into _tax
173
                  *
174
             from tax
175
            where tax.rate = _old_rate
176
                  and tax.taxkey = _taxkey_ids[1]
177
         order by itime desc
178
            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
179
                     -- this also assumes taxkeys never change
180
      -- RAISE NOTICE 'tax = %', _tax;
181

  
182
      -- insert into taxkeys (chart_id,                 tax_id,   taxkey_id,                pos_ustva,    startdate)
183
      --              values ( (select id from chart where accno = 'kkkkgtkttttkk current_taxkey.chart_id, _tax.id, _tax.taxkey, current_taxkey.pos_ustva, '2020-07-01');
184
    END IF;
185

  
186
    -- raise notice 'inserting taxkey';
187
    insert into taxkeys (chart_id,                                tax_id,                taxkey_id,                pos_ustva, startdate   )
188
                 values (_chart_id,
189
                         (select id from tax where taxkey = current_taxkey.taxkey and rate = _new_rate::numeric),
190
                         current_taxkey.taxkey, -- 2, 3, 8, 9
191
                         current_taxkey.pos_ustva, '2020-07-01');
192

  
193
    -- finally insert a copy of the taxkey on 2020-06-30 with the new startdate 2021-01-01, thereby resetting the tax rates again
194
    insert into taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
195
                 values (_chart_id,
196
                         current_taxkey.tax_id,
197
                         current_taxkey.taxkey,
198
                         current_taxkey.pos_ustva, '2021-01-01');
199

  
200
    -- RAISE NOTICE 'inserted 2 taxkeys for chart % with taxkey %', (select accno from chart where id = current_taxkey.chart_id), current_taxkey.taxkey_id;
201
  END LOOP;  --
202
END IF; -- DATEV coa
203

  
204
END $$;
205

  
206
drop table temp_taxkey_conversions;
207

  
208
-- select * from taxkeys where startdate >= '2020-01-01';
209
-- rollback;
sql/Pg-upgrade2/konjunkturpaket_2020_SKR03.sql
1
-- @tag: konjunkturpaket_2020_SKR03
2
-- @description: Anpassung des Deutschen DATEV-Kontenrahmen für SKR03 Konjunkturpaket
3
-- @depends: release_3_5_5
4
-- @ignore: 0
5

  
6
-- TODO:
7
-- how to deal with old 16% charts in SKR03?
8
-- A) move to their correct taxkeys, 5 and 8, like for SKR04
9
--    and then create new versions of 3 and 9 with same taxkey
10
-- UST 5%, already exists in SKR03, so rename it, but also add new taxkeys
11

  
12
-- DEBUG
13

  
14
DO $$
15
BEGIN
16

  
17
IF ( select coa from defaults ) = 'Germany-DATEV-SKR03EU' THEN
18

  
19
  UPDATE tax SET taxdescription = 'OLD ' || taxdescription WHERE (taxkey = 3 or taxkey = 9) and rate = 0.16;
20

  
21
  -- rename some of the charts
22
  UPDATE chart SET description = 'Umsatzsteuer 5 %' where accno = '1773';
23

  
24
  -- rename charts if they weren't 't already changed
25
  UPDATE chart SET description = 'Erlöse 19 % / 16 % USt' where accno = '8400' and description = 'Erlöse 16%/19% USt.';
26
  UPDATE chart SET description = 'Erlöse 7 % / 5 % USt'   where accno = '8300' and description = 'Erlöse 7%USt';
27

  
28
  -- move old 16% taxkeys to their proper taxkeys, should be 5 and 7
29
  UPDATE tax SET taxkey = 5 WHERE taxkey = 3 and rate = 0.16;
30
  UPDATE tax SET taxkey = 7 WHERE taxkey = 9 and rate = 0.16;
31

  
32

  
33
  -- new charts
34
  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik)
35
         VALUES ('8732','Gewährte Skonti 5% USt','A', 'I', 'AR_paid', 2, 1, null,1, 't');
36

  
37
  -- SKR03
38
  -- 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
39
  -- so we don't need to to anything!
40
  --       3 | 0.16000 | Umsatzsteuer                                                 | 1775  | Umsatzsteuer 16%
41

  
42
  -- rename old 8735 to 8736
43
  UPDATE chart SET accno = '8736', description = 'Gewährte Skonti 19 % USt' where accno = '8735' and description = 'Gewährte Skonti 16%/19% USt.';
44
  -- create new 8735 with 16%
45

  
46
  -- create new 16% chart for Gewährte Skonti
47
  INSERT INTO chart(accno,                description, charttype, category,      link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er)
48
            VALUES ('8735','Gewährte Skonti 16 % USt',       'A',      'I', 'AR_paid',         3,       1,       null,       1,            't',      1);
49

  
50
  -- create new chart for Abziehbare Vorsteuer 5 % with taxkey 8
51
  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er)
52
         VALUES ('1568','Abziehbare Vorsteuer 5 %','A', 'E', 'AP_tax:IC_taxpart:IC_taxservice', 8, null, null, 27, 't', 27);
53

  
54
  -- taxkeys can't be inserted until the new taxes exist
55

  
56
  INSERT INTO tax (chart_id, rate, taxkey, taxdescription, chart_categories, skonto_sales_chart_id, skonto_purchase_chart_id)
57
  VALUES ( (select id from chart where accno = '1773'), 0.05, 2, 'Umsatzsteuer', 'I', (select id from chart where accno = '8732'), null),
58
         ( (select id from chart where accno = '1775'), 0.16, 3, 'Umsatzsteuer', 'I', (select id from chart where accno = '8735'), null),
59
         ( (select id from chart where accno = '1575'), 0.16, 9, 'Vorsteuer',    'E', null, (select id from chart where accno = '3735')),
60
         ( (select id from chart where accno = '1568'), 0.05, 8, 'Vorsteuer',    'E', null, (select id from chart where accno = '3732'));
61

  
62
  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;
63

  
64
  -- new taxkeys for 5% and 16% only need one startdate, not valid before and won't change back to anything later
65
  -- these taxkeys won't be valid on 2020-06-30, so won't be affected later by big taxkeys update
66
  INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
67
               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');
68

  
69
  INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
70
               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');
71

  
72
  -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
73
  --              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?
74

  
75
  -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
76
  --              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');
77

  
78
  -- the taxkeys for the existing charts will be updated in a later update
79
END IF;
80

  
81
END $$;
82

  
83

  
84
-- select t.taxkey,
85
--       t.rate,
86
--       t.taxdescription,
87
--       c.accno,
88
--       c.description
89
--  from tax t
90
--       left join chart c on (c.id = t.chart_id)
91
--order by t.taxkey;
92
-- taxkey |  rate   |                        taxdescription                        | accno |                  description
93
----------+---------+--------------------------------------------------------------+-------+------------------------------------------------
94
--      0 | 0.00000 | Keine Steuer                                                 | ☠     | ☠
95
--      1 | 0.00000 | USt-frei                                                     | ☠     | ☠
96
--      2 | 0.07000 | Umsatzsteuer                                                 | 1771  | Umsatzsteuer 7%
97
--      3 | 0.19000 | Umsatzsteuer                                                 | 1776  | Umsatzsteuer 19 %
98
--      3 | 0.16000 | Umsatzsteuer                                                 | 1775  | Umsatzsteuer 16%
99
--      8 | 0.07000 | Vorsteuer                                                    | 1571  | Abziehbare Vorsteuer 7%
100
--      9 | 0.16000 | Vorsteuer                                                    | 1575  | Abziehbare Vorsteuer 16%
101
--      9 | 0.19000 | Vorsteuer                                                    | 1576  | Abziehbare Vorsteuer 19 %
102
--     10 | 0.00000 | Im anderen EU-Staat steuerpflichtige Lieferung               | 1767  | Im anderen EG-Staat steuerpfl. Lieferung
103
--     11 | 0.00000 | Steuerfreie innergem. Lieferung an Abnehmer mit Id.-Nr.      | ☠     | ☠
104
--     12 | 0.07000 | Steuerpflichtige EG-Lieferung zum ermäßigten Steuersatz      | 1772  | Umsatzsteuer 7% innergem.Erwerb
105
--     13 | 0.19000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz          | 1774  | Umsatzsteuer aus innergem. Erwerb 19 %
106
--     13 | 0.16000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz          | 1773  | Umsatzsteuer 16% innergem.Erwerb
107
--     18 | 0.07000 | Steuerpflichtiger innergem. Erwerb zum ermäßigten Steuersatz | 1572  | Abziehbare Vorsteuer 7% innergem. Erwerb
108
--     19 | 0.19000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz     | 1574  | Abziehbare Vorsteuer aus innergem. Erwerb 19 %
109
--     19 | 0.16000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz     | 1572  | Abziehbare Vorsteuer 7% innergem. Erwerb
sql/Pg-upgrade2/konjunkturpaket_2020_SKR04.sql
1
-- @tag: konjunkturpaket_2020_SKR04
2
-- @description: Anpassung des Deutschen DATEV-Kontenrahmen für SKR04 Konjunkturpaket
3
-- @depends: release_3_5_5
4
-- @ignore: 0
5

  
6
-- TODO:
7
-- how to deal with old 16% charts in SKR03?
8
-- A) move to their correct taxkeys, 5 and 8, like for SKR04
9
--    and then create new versions of 3 and 9 with same taxkey
10
-- UST 5%, already exists in SKR03, so rename it, but also add new taxkeys
11

  
12

  
13
DO $$
14
BEGIN
15

  
16
IF ( select coa from defaults ) = 'Germany-DATEV-SKR04EU' THEN
17

  
18
  -- DEBUG
19

  
20
  UPDATE chart set description = 'Abziehbare Vorsteuer 5 %', taxkey_id = 8 where accno = '1403' and description = 'Abziehbare Vorsteuer aus innergemeinschftl. Erwerb 16%';
21

  
22
  UPDATE chart set description = 'Umsatzsteuer 5 %', taxkey_id = 2 where accno = '3803' and description = 'Umsatzsteuer aus innergemeinschftl. Erwerb 16%';
23

  
24
  -- create new chart for Abziehbare Vorsteuer 5 % with taxkey 8
25
  -- INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er)
26
  --       VALUES ('1403','Abziehbare Vorsteuer 5 %','A', 'E', 'AP_tax:IC_taxpart:IC_taxservice', 8, null, null, 27, 'f', 27);
27

  
28
  UPDATE tax SET taxdescription = 'OLD ' || taxdescription WHERE (taxkey = 5 or taxkey = 7); -- and rate = 0.16;
29

  
30
  UPDATE taxkeys SET tax_id = (SELECT id FROM tax WHERE taxkey = 5 and rate = 0.16)
31
   WHERE chart_id = (SELECT id FROM chart where accno = '4400')
32
     AND startdate = '1970-01-01';
33

  
34
  -- rename charts if they weren't 't already changed
35
  -- UPDATE chart SET description = 'Erlöse 19 % / 16 % USt' where accno = '8400' and description = 'Erlöse 16%/19% USt.';
36
  -- UPDATE chart SET description = 'Erlöse 7 % / 5 % USt'   where accno = '8300' and description = 'Erlöse 7%USt';
37

  
38
  -- move old 16% taxkeys to their proper taxkeys, should be 5 and 7
39
  -- UPDATE tax SET taxkey = 5 WHERE taxkey = 3 and rate = 0.16;
40
  -- UPDATE tax SET taxkey = 7 WHERE taxkey = 9 and rate = 0.16;
41

  
42

  
43
  -- new charts for 5%
44
  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik)
45
         VALUES ('4732','Gewährte Skonti 5 % USt','A', 'I', 'AR_paid', 2, 1, null, 1, 't');
46
  INSERT INTO chart (accno, description, charttype, category, link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik)
47
         VALUES ('5732','Erhaltene Skonti 5 % Vorsteuer','A', 'E', 'AP_paid', 8, 4, null, null, 't');
48

  
49

  
50
  -- SKR03
51
  -- 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
52
  -- so we don't need to to anything!
53
  --       3 | 0.16000 | Umsatzsteuer                                                 | 1775  | Umsatzsteuer 16%
54

  
55
  -- rename old 8735 to 8736
56
  -- UPDATE chart SET accno = '8736', description = 'Gewährte Skonti 19 % USt' where accno = '8735' and description = 'Gewährte Skonti 16%/19% USt.';
57
  -- create new 8735 with 16%
58

  
59
  -- create new 16% chart for Gewährte Skonti
60
  INSERT INTO chart(accno,                description, charttype, category,      link, taxkey_id, pos_bwa, pos_bilanz, pos_eur, datevautomatik, pos_er)
61
            VALUES ('8735','Gewährte Skonti 16 % USt',       'A',      'I', 'AR_paid',         3,       1,       null,       1,            't',      1);
62

  
63

  
64
  -- taxkeys can't be inserted until the new taxes exist
65

  
66
  INSERT INTO tax (chart_id, rate, taxkey, taxdescription, chart_categories, skonto_sales_chart_id, skonto_purchase_chart_id)
67
  VALUES ( (select id from chart where accno = '3803'), 0.05, 2, 'Umsatzsteuer', 'I', (select id from chart where accno = '4732'), null), -- ok
68
         ( (select id from chart where accno = '3805'), 0.16, 3, 'Umsatzsteuer', 'I', (select id from chart where accno = '4735'), null),
69
         ( (select id from chart where accno = '1405'), 0.16, 9, 'Vorsteuer',    'E', null, (select id from chart where accno = '5735')),
70
         ( (select id from chart where accno = '1403'), 0.05, 8, 'Vorsteuer',    'E', null, (select id from chart where accno = '5732'));
71

  
72
  -- 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;
73

  
74
  -- new taxkeys for 5% and 16% only need one startdate, not valid before and won't change back to anything later
75
  -- these taxkeys won't be valid on 2020-06-30, so won't be affected later by big taxkeys update
76
  INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
77
               VALUES ( (select id from chart where accno = '4732'),
78
                      ( 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?
79

  
80
  INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
81
               VALUES ( (select id from chart where accno = '5732'),
82
                      (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?
83
  -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
84
  --              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');
85

  
86
  -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
87
  --              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?
88

  
89
  -- INSERT INTO taxkeys (chart_id, tax_id, taxkey_id, pos_ustva, startdate)
90
  --              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');
91

  
92
  -- the taxkeys for the existing charts will be updated in a later update
93
END IF;
94

  
95
END $$;
96

  
97

  
98
-- do the same for all other accounts linked to 9
99

  
100

  
101
--  select t.taxkey,
102
--        t.rate,
103
--        t.taxdescription,
104
--        c.accno,
105
--        c.description
106
--   from tax t
107
--        left join chart c on (c.id = t.chart_id)
108
-- ;
109
--  taxkey |  rate   |                        taxdescription                        | accno |                      description                       
110
-- --------+---------+--------------------------------------------------------------+-------+--------------------------------------------------------
111
--       8 | 0.07000 | Vorsteuer                                                    | 1401  | Abziehbare Vorsteuer 7%
112
--      18 | 0.07000 | Steuerpflichtiger innergem. Erwerb zum ermäßigten Steuersatz | 1402  | Abziehbare Vorsteuer aus innergemeinschftl. Erwerb
113
--      17 | 0.16000 | Steuerpflicht. EG-Erwerb                                     | 1403  | Abziehbare Vorsteuer aus innergemeinschftl. Erwerb 16%
114
--      19 | 0.19000 | Steuerpflichtiger innergem. Erwerb zum vollen Steuersatz     | 1404  | Abziehbare Vorsteuer aus innergemeinschftl. Erwerb 19%
115
--       7 | 0.16000 | Vorsteuer                                                    | 1405  | Abziehbare Vorsteuer 16%
116
--       9 | 0.19000 | Vorsteuer                                                    | 1406  | Abziehbare Vorsteuer 19 %
117
--       9 | 0.19000 | Vorsteuer                                                    | 1406  | Abziehbare Vorsteuer 19 %
118
--       2 | 0.07000 | Umsatzsteuer                                                 | 3801  | Umsatzsteuer 7%
119
--      12 | 0.07000 | Steuerpflichtige EG-Lieferung zum ermäßigten Steuersatz      | 3802  | Umsatzsteuer aus innergemeinschftl. Erwerb
120
--      15 | 0.16000 | Steuerpflicht. EG-Lieferungen%                               | 3803  | Umsatzsteuer aus innergemeinschftl. Erwerb 16%
121
--      13 | 0.19000 | Steuerpflichtige EG-Lieferung zum vollen Steuersatz          | 3804  | Umsatzsteuer aus innergemeinschftl. Erwerb 19%
122
--       5 | 0.16000 | Umsatzsteuer                                                 | 3805  | Umsatzsteuer 16%
123
--       3 | 0.19000 | Umsatzsteuer                                                 | 3806  | Umsatzsteuer 19%
124
--       3 | 0.19000 | Umsatzsteuer                                                 | 3806  | Umsatzsteuer 19%
125
--       1 | 0.00000 | USt-frei                                                     | ☠     | ☠
126
--      11 | 0.00000 | Steuerfreie innergem. Lieferung an Abnehmer mit Id.-Nr.      | ☠     | ☠
127
--       0 | 0.00000 | Keine Steuer                                                 | ☠     | ☠
128
--      10 | 0.00000 | Im anderen EU-Staat steuerpflichtige Lieferung               | ☠     | ☠
t/tax/tax.t
1
use Test::More tests => 38;
2
use Test::Deep qw(cmp_deeply);
3

  
4
use strict;
5

  
6
use lib 't';
7
use utf8;
8

  
9
use Support::TestSetup;
10
use Test::Exception;
11

  
12
use SL::DB::Customer;
13
use SL::DB::Vendor;
14
use SL::DB::Invoice;
15
use SL::DB::GLTransaction;
16
use SL::DB::Part;
17
use SL::DBUtils qw(selectall_hashref_query);
18
use SL::Dev::Record qw(:ALL);
19
use SL::Dev::CustomerVendor qw(new_customer new_vendor);
20
use SL::Dev::Part qw(new_part);
21
use Data::Dumper;
22

  
23
Support::TestSetup::login();
24
my $dbh = SL::DB->client->dbh;
25

  
26
my $test_kontenrahmen = 'skr03';
27

  
28
clear_up();
29

  
30
# TODOs: Storno muß noch korrekt funktionieren
31
#  neue Konten für 5% anlegen
32
#  Leistungszeitraum vs. Datum Zuord. Steuerperiodest
33

  
34
note('checking if all tax entries exist for Konjunkturprogramm');
35

  
36
# create dates to test on
37
my $date_2006   = DateTime->new(year => 2006, month => 6, day => 15);
38
my $date_2020_1 = DateTime->new(year => 2020, month => 6, day => 15);
39
my $date_2020_2 = DateTime->new(year => 2020, month => 7, day => 15);
40
my $date_2021   = DateTime->new(year => 2021, month => 1, day => 15);
41

  
42
# The only way to discern the pre-2007 16% tax from the 2020 16% tax is by
43
# their configured automatic tax charts, so look them up here:
44

  
45
my ($chart_vst_19, $chart_vst_16, $chart_vst_5, $chart_vst_7);
46
my ($chart_ust_19, $chart_ust_16, $chart_ust_5, $chart_ust_7);
47
my ($income_19_accno, $income_7_accno);
48
my ($ar_accno, $ap_accno);
49
my ($chart_reisekosten_accno, $chart_cash_accno, $chart_bank_accno);
50

  
51

  
52
if ( $test_kontenrahmen eq 'skr03' ) {
53

  
54
  is(SL::DB::Default->get->coa, 'Germany-DATEV-SKR03EU', "coa SKR03 ok");
55

  
56
  $chart_vst_19 = '1776';
57
  $chart_vst_16 = '1775';
58
  $chart_vst_5  = '1773';
59
  $chart_vst_7  = '1771';
60

  
61
  $chart_ust_19 = '1576';
62
  $chart_ust_16 = '1575';
63
  $chart_ust_5  = '1568';
64
  $chart_ust_7  = '1571';
65

  
66
  $income_19_accno = '8400';
67
  $income_7_accno  = '8300';
68

  
69
  $chart_reisekosten_accno = 4660;
70
  $chart_cash_accno        = 1000;
71

  
72
  $ar_accno = 1400;
73
  $ap_accno = 1600;
74

  
75
} elsif ( $test_kontenrahmen eq 'skr04') { # skr04 - test can be ran manually by running t/000setup_database.t with coa for SKR04
76
  is(SL::DB::Default->get->coa, 'Germany-DATEV-SKR04EU', "coa SKR04 ok");
77
  $chart_ust_19 = '1406';
78
  $chart_ust_16 = '1405';
79
  $chart_ust_5  = '1403';
80
  $chart_ust_7  = '1401';
81

  
82
  $chart_vst_19 = '3806';
83
  $chart_vst_16 = '3805';
84
  $chart_vst_5  = '3803';
85
  $chart_vst_7  = '3801';
86

  
87
  $income_19_accno = '4400';
88
  $income_7_accno  = '4300';
89

  
90
  $chart_reisekosten_accno = 6650;
91
  $chart_cash_accno        = 1600;
92

  
93
  $ar_accno = 1200;
94
  $ap_accno = 3300;
95
}
96

  
97
my $tax_vst_19 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_19) or die; # 19%
98
my $tax_vst_16 = SL::DB::Manager::Chart->find_by(accno => $chart_vst_16) or die; # 16%
99
my $tax_vst_5  = SL::DB::Manager::Chart->find_by(accno => $chart_vst_5 ) or die; #  5%
100
my $tax_vst_7  = SL::DB::Manager::Chart->find_by(accno => $chart_vst_7 ) or die; #  7%
101

  
102
my $tax_ust_19 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_19) or die; # 19%
103
my $tax_ust_16 = SL::DB::Manager::Chart->find_by(accno => $chart_ust_16) or die; # 16%
104
my $tax_ust_5  = SL::DB::Manager::Chart->find_by(accno => $chart_ust_5) or die;  #  5%
105
my $tax_ust_7  = SL::DB::Manager::Chart->find_by(accno => $chart_ust_7) or die;  #  7%
106

  
107
my $chart_income_19  = SL::DB::Manager::Chart->find_by(accno => $income_19_accno) or die;
108
my $chart_income_7   = SL::DB::Manager::Chart->find_by(accno => $income_7_accno) or die;
109

  
110
my $chart_reisekosten = SL::DB::Manager::Chart->find_by(accno => $chart_reisekosten_accno) or die;
111
my $chart_cash        = SL::DB::Manager::Chart->find_by(accno => $chart_cash_accno) or die;
112

  
113
is(defined SL::DB::Manager::Tax->find_by(taxkey => 2, rate => 0.05), 1, "tax for taxkey 2 with 5% was created ok");
114
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");
115
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");
116
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");
117

  
118
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");
119
# 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");
120
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");
121
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");
122

  
123
my $vendor   = new_vendor(  name => 'Testvendor')->save;
124
my $customer = new_customer(name => 'Testcustomer')->save;
125

  
126
# 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");
127
# 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");
128
# cmp_ok($chart_income_7->get_active_taxkey($date_2021  )->tax->rate, '==', 0.07, "get_active_taxkey rate for 8300 in 2021   ok");
129
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");
130
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");
131
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");
132
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");
133

  
134
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");
135
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");
136
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");
137
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");
138

  
139
my $bugru19 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 19%') or die "Can't find bugru19";
140
my $bugru7  = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 7%' ) or die "Can't find bugru7";
141

  
142
my $part1 = new_part(partnumber => '1', description => 'part19', buchungsgruppen_id => $bugru19->id)->save;
143
my $part2 = new_part(partnumber => '2', description => 'part7',  buchungsgruppen_id => $bugru7->id )->save;
144

  
145
note('sales invoices');
146
my $sales_invoice_2006   = create_invoice_for_date('2006',   $date_2006);
147
my $sales_invoice_2020_1 = create_invoice_for_date('2020_1', $date_2020_1);
148
my $sales_invoice_2020_2 = create_invoice_for_date('2020_2', $date_2020_2);
149
my $sales_invoice_2021   = create_invoice_for_date('2021',   $date_2021);
150

  
151
is($sales_invoice_2006->amount,   223, '2006 sales invoice has 16% and 7% tax ok'   ); # 116 + 7
152
is($sales_invoice_2020_1->amount, 226, '2020_01 sales invoice has 19% and 7% tax ok'); # 119 + 7
153
is($sales_invoice_2020_2->amount, 221, '2020_02 sales invoice has 16% and 5% tax ok'); # 116 + 5
154
is($sales_invoice_2021->amount,   226, '2021 sales invoice has 19% and 7% tax ok'   ); # 119 + 7
155

  
156
&datev_test($sales_invoice_2020_2,
157
           [
158
             {
159
               'belegfeld1' => 'test is 2020_2',
160
               'buchungstext' => 'Testcustomer',
161
               'datum' => '15.07.2020',
162
               'leistungsdatum' => '15.07.2020', # should leistungsdatum be empty if it doesn't exist?
163
               'gegenkonto' => $income_7_accno,
164
               'konto' => $ar_accno,
165
               'kost1' => undef,
166
               'kost2' => undef,
167
               'locked' => undef,
168
               'umsatz' => 105,
169
               'waehrung' => 'EUR'
170
             },
171
             {
172
               'belegfeld1' => 'test is 2020_2',
173
               'buchungstext' => 'Testcustomer',
174
               'datum' => '15.07.2020',
175
               'leistungsdatum' => '15.07.2020',
176
               'gegenkonto' => $income_19_accno,
177
               'konto' => $ar_accno,
178
               'kost1' => undef,
179
               'kost2' => undef,
180
               'locked' => undef,
181
               'umsatz' => 116,
182
               'waehrung' => 'EUR'
183
             }
184
          ],
185
          "datev check for 16/5 ok, no taxkey"
186
);
187

  
188
note('sales invoice with differing delivery dates');
189
my $sales_invoice_2020_1_with_delivery_date_2020_2 = create_invoice_for_date('deliverydate 2020_1', $date_2020_1, $date_2020_2);
190
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");
191

  
192
my $sales_invoice_2020_2_with_delivery_date_2020_1 = create_invoice_for_date('deliverydate 2020_2', $date_2020_2, $date_2020_1);
193
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");
194

  
195
&datev_test($sales_invoice_2020_2_with_delivery_date_2020_1,
196
            [
197
              {
198
                'belegfeld1' => 'test is deliverydate 2020_2',
199
                'buchungstext' => 'Testcustomer',
200
                'datum' => '15.07.2020',
201
                'gegenkonto' => $income_7_accno,
202
                'konto' => $ar_accno,
203
                'kost1' => undef,
204
                'kost2' => undef,
205
                'leistungsdatum' => '15.06.2020',
206
                'locked' => undef,
207
                'umsatz' => 107,
208
                'waehrung' => 'EUR'
209
              },
210
              {
211
                'belegfeld1' => 'test is deliverydate 2020_2',
212
                'buchungstext' => 'Testcustomer',
213
                'datum' => '15.07.2020',
214
                'gegenkonto' => $income_19_accno,
215
                'konto' => $ar_accno,
216
                'kost1' => undef,
217
                'kost2' => undef,
218
                'leistungsdatum' => '15.06.2020',
219
                'locked' => undef,
220
                'umsatz' => 119,
221
                'waehrung' => 'EUR'
222
              }
223
            ],
224
            "datev check for datev export with delivery_date 19/7 ok, no taxkey"
225
);
226

  
227
my $sales_invoice_2021_with_delivery_date_2020_2   = create_invoice_for_date('deliverydate 2020_2', $date_2021, $date_2020_2);
228
is($sales_invoice_2021_with_delivery_date_2020_2->amount,   221, "sales_invoice from 2021   with   past delivery_date 2020_2 tax ok");
229

  
230
my $sales_invoice_2020_2_with_delivery_date_2021   = create_invoice_for_date('deliverydate 2021', $date_2020_2, $date_2021);
231
is($sales_invoice_2020_2_with_delivery_date_2021->amount,   226, "sales_invoice from 2020_2 with future delivery_date 2021   tax ok");
232

  
233

  
234
note('ap transactions');
235
# in the test we want to test for Reisekosten with 19% and 7%. Normally the user
236
# would select the entries from the dropdown, as they may differ from the
237
# default, so we have to pass the tax we want to create_ap_transaction
238

  
239
my $tax_9_16_old = SL::DB::Manager::Tax->find_by(taxkey => 7, rate => 0.16, chart_id => $tax_ust_16->id);
240
my $tax_9_19     = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19, chart_id => $tax_ust_19->id);
241
my $tax_9_16     = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.16, chart_id => $tax_ust_16->id);
242
my $tax_8_7      = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.07, chart_id => $tax_ust_7->id);
243
my $tax_8_5      = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.05, chart_id => $tax_ust_5->id);
244

  
245
# simulate user selecting the "correct" taxes in dropdown:
246
my $ap_transaction_2006   = create_ap_transaction_for_date('2006',   $date_2006,   undef, $tax_9_16_old, $tax_8_7);
247
my $ap_transaction_2020_1 = create_ap_transaction_for_date('2020_1', $date_2020_1, undef, $tax_9_19,     $tax_8_7);
248
my $ap_transaction_2020_2 = create_ap_transaction_for_date('2020_2', $date_2020_2, undef, $tax_9_16,     $tax_8_5);
249
my $ap_transaction_2021   = create_ap_transaction_for_date('2021',   $date_2021,   undef, $tax_9_19,     $tax_8_7);
250

  
251

  
252
is($ap_transaction_2006->amount,   223, '2006    ap transaction has 16% and 7% tax ok'); # 116 + 7
253
is($ap_transaction_2020_1->amount, 226, '2020_01 ap transaction has 19% and 7% tax ok'); # 119 + 7
254
is($ap_transaction_2020_2->amount, 221, '2020_02 ap transaction has 16% and 5% tax ok'); # 116 + 5
255
is($ap_transaction_2021->amount,   226, '2021    ap transaction has 19% and 7% tax ok'); # 119 + 7
256

  
257
# ap transaction in july, but use old tax
258
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);
259
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
260
&datev_test($ap_transaction_2020_2_with_delivery_date_2020_1,
261
            [
262
              {
263
                'belegfeld1' => 'test ap_transaction 2020_2 with delivery date 2020_1',
264
                'buchungsschluessel' => 8,
265
                'buchungstext' => 'Testvendor',
266
                'datum' => '15.07.2020',
267
                'gegenkonto' => $ap_accno,
268
                'konto' => $chart_reisekosten_accno,
269
                'kost1' => undef,
270
                'kost2' => undef,
271
                'leistungsdatum' => '15.06.2020',
272
                'locked' => undef,
273
                'umsatz' => 107,
274
                'waehrung' => 'EUR'
275
              },
276
              {
277
                'belegfeld1' => 'test ap_transaction 2020_2 with delivery date 2020_1',
278
                'buchungsschluessel' => 9,
279
                'buchungstext' => 'Testvendor',
280
                'datum' => '15.07.2020',
281
                'gegenkonto' => $ap_accno,
282
                'konto' => $chart_reisekosten_accno,
283
                'kost1' => undef,
284
                'kost2' => undef,
285
                'leistungsdatum' => '15.06.2020',
286
                'locked' => undef,
287
                'umsatz' => 119,
288
                'waehrung' => 'EUR'
289
              }
290
            ],
291
            "datev check for ap transaction 2020_2 with delivery date 2020_1, 19% and 7% tax ok"
292
);
293

  
294
note('ar transactions');
295

  
296
my $ar_transaction_2006   = create_ar_transaction_for_date('2006',   $date_2006);
297
my $ar_transaction_2020_1 = create_ar_transaction_for_date('2020_1', $date_2020_1);
298
my $ar_transaction_2020_2 = create_ar_transaction_for_date('2020_2', $date_2020_2);
299
my $ar_transaction_2021   = create_ar_transaction_for_date('2021',   $date_2021);
300

  
301
is($ar_transaction_2006->amount,   223, '2006    ar transaction has 16% and 7% tax ok'); # 116 + 7
302
is($ar_transaction_2020_1->amount, 226, '2020_01 ar transaction has 19% and 7% tax ok'); # 119 + 7
303
is($ar_transaction_2020_2->amount, 221, '2020_02 ar transaction has 16% and 5% tax ok'); # 116 + 5
304
is($ar_transaction_2021->amount,   226, '2021    ar transaction has 19% and 7% tax ok'); # 119 + 7
305

  
306
note('gl transactions');
307

  
308
my $gl_2006   = create_gl_transaction_for_date('glincome 2006',   $date_2006,   223);
309
my $gl_2020_1 = create_gl_transaction_for_date('glincome 2020_1', $date_2020_1, 226);
310
my $gl_2020_2 = create_gl_transaction_for_date('glincome 2020_2', $date_2020_2, 221);
311
my $gl_2021   = create_gl_transaction_for_date('glincome 2021',   $date_2021,   226);
312

  
313
is(SL::DB::Manager::GLTransaction->get_all_count(), 4, "4 gltransactions created correctly");
314

  
315
my $result = &get_account_balances;
316
# print Dumper($result);
317
is_deeply( &get_account_balances,
318
 [
319
          # {
320
          #   'accno' => '1000',
321
          #   # 'description' => 'Kasse',
322
          #   'sum' => '-896.00000'
323
          # },
324
          # {
325
          #   'accno' => '1400',
326
          #   # 'description' => 'Ford. a.Lieferungen und Leistungen',
327
          #   'sum' => '-2686.00000'
328
          # },
329
          {
330
            'accno' => '1568',
331
            # 'description' => 'Abziehbare Vorsteuer 7%',
332
            'sum' => '-5.00000'
333
          },
334
          {
335
            'accno' => '1571',
336
            # 'description' => 'Abziehbare Vorsteuer 7%',
337
            'sum' => '-28.00000'
338
          },
339
          {
340
            'accno' => '1575',
341
            # 'description' => 'Abziehbare Vorsteuer 16%',
342
            'sum' => '-32.00000'
343
          },
344
          {
345
            'accno' => '1576',
346
            # 'description' => 'Abziehbare Vorsteuer 19 %',
347
            'sum' => '-57.00000'
348
          },
349
          # {
350
          #   'accno' => '1600',
351
          #   # 'description' => 'Verbindlichkeiten aus Lief.u.Leist.',
352
          #   'sum' => '896.00000'
353
          # },
354
          {
355
            'accno' => '1771',
356
            # 'description' => 'Umsatzsteuer 7%',
357
            'sum' => '77.00000'
358
          },
359
          {
360
            'accno' => '1773',
361
            # 'description' => 'Umsatzsteuer 5 %',
362
            'sum' => '25.00000'
363
          },
364
          {
365
            'accno' => '1775',
366
            # 'description' => 'Umsatzsteuer 16%',
367
            'sum' => '128.00000'
368
          },
369
          {
370
            'accno' => '1776',
371
            # 'description' => 'Umsatzsteuer 19 %',
372
            'sum' => '152.00000'
373
          },
374
          # {
375
          #   'accno' => '4660',
376
          #   # 'description' => 'Reisekosten Arbeitnehmer',
377
          #   'sum' => '-800.00000'
378
          # },
379
          # {
380
          #   'accno' => $income_7_accno,
381
          #   # 'description' => "Erl\x{f6}se 7%USt",
382
          #   'sum' => '1600.00000'
383
          # },
384
          # {
385
          #   'accno' => $income_19_accno,
386
          #   # 'description' => "Erl\x{f6}se 16%/19% USt.",
387
          #   'sum' => '1600.00000'
388
          # }
389
        ],
390
        'account balances after invoices'
391
);
392

  
393
clear_up();
394

  
395
done_testing();
396

  
397
###### functions for setting up data
398

  
399
sub create_invoice_for_date {
400
  my ($invnumber, $transdate, $deliverydate) = @_;
401

  
402
  $deliverydate = $transdate unless defined $deliverydate;
403

  
404
  my $sales_invoice = create_sales_invoice(
405
    invnumber    => 'test is ' . $invnumber,
406
    transdate    => $transdate,
407
    customer     => $customer,
408
    deliverydate => $deliverydate,
409
    taxincluded  => 0,
410
    invoiceitems => [ create_invoice_item(part => $part1, qty => 10, sellprice => 10),
411
                      create_invoice_item(part => $part2, qty => 10, sellprice => 10),
412
                    ]
413
  );
414
  return $sales_invoice;
415
}
416

  
417
sub create_ar_transaction_for_date {
418
  my ($invnumber, $transdate) = @_;
419

  
420
  my $ar_transaction = create_ar_transaction(
421
    customer      => $customer,
422
    invnumber   => 'test ar' . $invnumber,
423
    taxincluded => 0,
424
    transdate   => $transdate,
425
    ar_chart     => SL::DB::Manager::Chart->find_by(accno => $ar_accno), # pass ar_chart, as it is hardcoded for SKR03 in SL::Dev::Record
426
    bookings    => [
427
                     {
428
                       chart  => $chart_income_19,
429
                       amount => 100,
430
                     },
431
                     {
432
                       chart  => $chart_income_7,
433
                       amount => 100,
434
                     },
435
                   ]
436
  );
437
  return $ar_transaction;
438
}
439

  
440
sub create_ap_transaction_for_date {
441
  my ($invnumber, $transdate, $deliverydate, $tax_high, $tax_low) = @_;
442

  
443
  # printf("invnumber = %s  tax_high = %s   tax_low = %s\n", $invnumber, $tax_high->accno , $tax_low->accno);
444
  my $taxkey_ = $chart_reisekosten->get_active_taxkey($transdate);
445

  
446
  my $ap_transaction = create_ap_transaction(
447
    vendor       => $vendor,
448
    invnumber    => 'test ap_transaction ' . $invnumber,
449
    taxincluded  => 0,
450
    transdate    => $transdate,
451
    deliverydate => $deliverydate,
452
    ap_chart     => SL::DB::Manager::Chart->find_by(accno => $ap_accno), # pass ap_chart, as it is hardcoded for SKR03 in SL::Dev::Record
453
    bookings     => [
454
                     {
455
                       chart  => $chart_reisekosten,
456
                       amount => 100,
457
                       tax_id => $tax_high->id,
458
                     },
459
                     {
460
                       chart  => $chart_reisekosten,
461
                       amount => 100,
462
                       tax_id => $tax_low->id,
463
                     },
464
                   ]
465
  );
466
  return $ap_transaction;
467
}
468

  
469
sub create_gl_transaction_for_date {
470
  my ($reference, $transdate, $debitamount) = @_;
471

  
472
  my $gl_transaction = create_gl_transaction(
473
    reference   => $reference,
474
    taxincluded => 0,
475
    transdate   => $transdate,
476
    bookings    => [
477
                     {
478
                       chart  => $chart_income_19,
479
                       memo   => 'gl 19',
480
                       source => 'gl 19',
481
                       credit => 100,
482
                     },
483
                     {
484
                       chart  => $chart_income_7,
485
                       memo   => 'gl 7',
486
                       source => 'gl 7',
487
                       credit => 100,
488
                     },
489
                     {
490
                       chart  => $chart_cash,
491
                       debit  => $debitamount,
492
                       memo   => 'gl 19+7',
493
                       source => 'gl 19+7',
494
                     },
495
                   ],
496
  );
497
  return $gl_transaction;
498
}
499

  
500
sub get_account_balances {
501
  my $query = <<SQL;
502
  select c.accno, sum(a.amount)
503
    from acc_trans a
504
         left join chart c on (c.id = a.chart_id)
505
   where c.accno ~ '^17' or c.accno ~ '^15'
506
group by c.accno, c.description
507
order by c.accno
508
SQL
509

  
510
  my $result = selectall_hashref_query($::form, $dbh, $query);
511
  return $result;
512
};
513

  
514
sub datev_test {
515
  my ($invoice, $expected_data, $msg) = @_;
516

  
517
  my $datev = SL::DATEV->new(
518
    dbh        => $invoice->db->dbh,
519
    trans_id   => $invoice->id,
520
  );
521

  
522
  $datev->generate_datev_data;
523
  my @data_datev   = sort { $a->{umsatz} <=> $b->{umsatz} } @{ $datev->generate_datev_lines() };
524

  
525
  # print Dumper(\@data_datev);
526

  
527
  cmp_deeply(\@data_datev, $expected_data, $msg);
528
}
529

  
530
sub clear_up {
531
  SL::DB::Manager::OrderItem->delete_all(all => 1);
532
  SL::DB::Manager::Order->delete_all(all => 1);
533
  SL::DB::Manager::InvoiceItem->delete_all(all => 1);
534
  SL::DB::Manager::Invoice->delete_all(all => 1);
535
  SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
536
  SL::DB::Manager::GLTransaction->delete_all(all => 1);
537
  SL::DB::Manager::Part->delete_all(all => 1);
538
  SL::DB::Manager::Customer->delete_all(all => 1);
539
  SL::DB::Manager::Vendor->delete_all(all => 1);
540
};
541

  
542
1;

Also available in: Unified diff