projekt kivitendo

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

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

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

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

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

                      ( 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?

                      (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');

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