Friday, September 3, 2021

I am basically done with #2406. One “little detail” is still missing: the result ;-) The button to export the intra-community statement works and produces a validated XML file, but the numbers are still wrong. I need to understand what is wrong with the SQL statement generated by lino_xl.lib.vat.VatDeclaration.intracom_statement_iterator(). Here is an indented example without the quote chars for readability:

SELECT DISTINCT
  contacts_partner.id, contacts_partner.email,
  contacts_partner.language, contacts_partner.url, contacts_partner.phone,
  contacts_partner.gsm, contacts_partner.fax, contacts_partner.country_id,
  contacts_partner.city_id, contacts_partner.zip_code, contacts_partner.region_id,
  contacts_partner.addr1, contacts_partner.street_prefix, contacts_partner.street,
  contacts_partner.street_no, contacts_partner.street_box, contacts_partner.addr2,
  contacts_partner.vat_regime, contacts_partner.vat_id, contacts_partner.prefix,
  contacts_partner.name, contacts_partner.remarks,
  contacts_partner.payment_term_id, contacts_partner.purchase_account_id,
  CAST(SUM(vat_vataccountinvoice.total_base) AS NUMERIC) AS vataccountinvoice_base,
  CAST(SUM(sales_vatproductinvoice.total_base) AS NUMERIC) AS vatproductinvoice_base
FROM contacts_partner
LEFT OUTER JOIN vat_vataccountinvoice ON (contacts_partner.id = vat_vataccountinvoice.partner_id)
LEFT OUTER JOIN sales_vatproductinvoice ON (contacts_partner.id = sales_vatproductinvoice.partner_id)
LEFT OUTER JOIN vat_vataccountinvoice T4 ON (contacts_partner.id = T4.partner_id)
LEFT OUTER JOIN ledger_voucher ON (T4.voucher_ptr_id = ledger_voucher.id)
LEFT OUTER JOIN ledger_journal ON (ledger_voucher.journal_id = ledger_journal.id)
LEFT OUTER JOIN sales_vatproductinvoice T8 ON (contacts_partner.id = T8.partner_id)
LEFT OUTER JOIN ledger_voucher T9 ON (T8.voucher_ptr_id = T9.id)
LEFT OUTER JOIN ledger_journal T11 ON (T9.journal_id = T11.id)
GROUP BY contacts_partner.id,
  contacts_partner.email, contacts_partner.language, contacts_partner.url,
  contacts_partner.phone, contacts_partner.gsm, contacts_partner.fax,
  contacts_partner.country_id, contacts_partner.city_id,
  contacts_partner.zip_code, contacts_partner.region_id, contacts_partner.addr1,
  contacts_partner.street_prefix, contacts_partner.street,
  contacts_partner.street_no, contacts_partner.street_box, contacts_partner.addr2,
  contacts_partner.vat_regime, contacts_partner.vat_id, contacts_partner.prefix,
  contacts_partner.name, contacts_partner.remarks,
  contacts_partner.payment_term_id, contacts_partner.purchase_account_id,
  ledger_voucher.accounting_period_id, ledger_journal.preliminary,
  ledger_journal.trade_type, T4.vat_regime, T9.accounting_period_id,
  T11.preliminary, T11.trade_type, T8.vat_regime
  HAVING (
      (ledger_voucher.accounting_period_id = 2
      AND NOT ledger_journal.preliminary
      AND ledger_journal.trade_type = S
      AND T4.vat_regime IN (40, 30, 31)
      AND CAST(SUM(vat_vataccountinvoice.total_base) AS NUMERIC) >= 0)
    OR
      (T9.accounting_period_id = 2
      AND NOT T11.preliminary
      AND T11.trade_type = S
      AND T8.vat_regime IN (40, 30, 31)
      AND CAST(SUM(sales_vatproductinvoice.total_base) AS NUMERIC) >= 0))
  ORDER BY contacts_partner.vat_id ASC