Query1_7548.sql

SELECT
  '??' AS "Conjunt",
  COALESCE(anal_a.cuenta_analitica, anal_sol.cuenta_analitica) AS "Cuenta analítica",
  b.name AS "Número Factura",
  CASE
    WHEN b.move_type = 'out_refund'
      THEN replace(to_char(a.price_subtotal * (-1)::numeric, '999999990D99'), '.', ',')
    ELSE replace(to_char(a.price_subtotal, '999999990D99'), '.', ',')
  END AS "Subtotal",
  a.name AS "Descripción",
  e.name AS "Cliente",
  soinfo.oferta AS "Oferta",
  b.invoice_date AS "Fecha factura",
  b.invoice_date_due AS "Vencimiento",
  f.name AS "Compañía",
  CASE
    WHEN b.state = 'draft' THEN 'Borrador'
    WHEN b.state = 'posted' AND b.payment_state IN ('not_paid','partial','in_payment') THEN 'Facturado'
    WHEN b.state = 'posted' AND b.payment_state = 'paid' THEN 'Pagado'
    ELSE b.state
  END AS "Estado factura",
  soinfo.fecha_aprobacion AS "Fecha aprobación",
  COALESCE(anal_a.proyecto, anal_sol.proyecto) AS "Proyecto",
  '??' AS "Previsión de Cierre",
  '??' AS "Estado Proyecto",
  replace(to_char(soinfo.importe_vendido, '9999999999D99'), '.', ',') AS "Importe vendido",
  '??' AS "Responsable Proyecto"
FROM account_move_line a
JOIN account_move b ON b.id = a.move_id
LEFT JOIN res_partner e ON e.id = b.partner_id
LEFT JOIN res_company f ON f.id = b.company_id
LEFT JOIN LATERAL (
  SELECT
    string_agg(DISTINCT
      CASE WHEN pg_typeof(ap.name) = 'jsonb'::regtype
           THEN COALESCE(ap.name->>'es_ES', ap.name->>'en_US')
           ELSE ap.name::text END, ', ') AS cuenta_analitica,
    string_agg(DISTINCT
      CASE WHEN pg_typeof(aa.name) = 'jsonb'::regtype
           THEN COALESCE(aa.name->>'es_ES', aa.name->>'en_US')
           ELSE aa.name::text END, ', ') AS proyecto
  FROM jsonb_each(COALESCE(a.analytic_distribution::jsonb, '{}'::jsonb)) kv
  JOIN account_analytic_account aa ON aa.id = (kv.key)::int
  LEFT JOIN account_analytic_plan ap ON ap.id = aa.plan_id
) anal_a ON TRUE
LEFT JOIN (
  SELECT
    j.invoice_line_id AS aml_id,
    string_agg(DISTINCT so.name::text, ', ') AS oferta,
    MIN(so.date_order) AS fecha_aprobacion,
    SUM(DISTINCT so.amount_untaxed) AS importe_vendido
  FROM sale_order_line_invoice_rel j
  JOIN sale_order_line sol ON sol.id = j.order_line_id
  JOIN sale_order so ON so.id = sol.order_id
  GROUP BY j.invoice_line_id
) soinfo ON soinfo.aml_id = a.id
LEFT JOIN LATERAL (
  SELECT
    string_agg(DISTINCT
      CASE WHEN pg_typeof(ap.name) = 'jsonb'::regtype
           THEN COALESCE(ap.name->>'es_ES', ap.name->>'en_US')
           ELSE ap.name::text END, ', ') AS cuenta_analitica,
    string_agg(DISTINCT
      CASE WHEN pg_typeof(aa.name) = 'jsonb'::regtype
           THEN COALESCE(aa.name->>'es_ES', aa.name->>'en_US')
           ELSE aa.name::text END, ', ') AS proyecto
  FROM sale_order_line_invoice_rel j2
  JOIN sale_order_line sol2 ON sol2.id = j2.order_line_id
  JOIN LATERAL jsonb_each(COALESCE(sol2.analytic_distribution::jsonb, '{}'::jsonb)) kv2 ON TRUE
  JOIN account_analytic_account aa ON aa.id = (kv2.key)::int
  LEFT JOIN account_analytic_plan ap ON ap.id = aa.plan_id
  WHERE j2.invoice_line_id = a.id
) anal_sol ON TRUE

WHERE b.state <> 'cancel'
  AND b.invoice_date >= DATE '2018-12-31'
  AND b.move_type IN ('out_invoice','out_refund')
  AND (a.display_type IS NULL OR a.display_type = 'product');