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