projekt kivitendo

-- @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;

-- @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

-- @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               | ☠     | ☠

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 = <<SQL;

  select c.accno, sum(a.amount)

    from acc_trans a

         left join chart c on (c.id = a.chart_id)

   where c.accno ~ '^17' or c.accno ~ '^15'

group by c.accno, c.description

order by c.accno

SQL

  my $result = selectall_hashref_query($::form, $dbh, $query);

  return $result;

};

sub datev_test {

  my ($invoice, $expected_data, $msg) = @_;

  my $datev = SL::DATEV->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;