Relatório de capital social

[vitormattos]

O capital social pode ser:

• pago diretamente pelo cooperado na conta da coop (transaction)
• registrado pelo financeiro como uma fatura e pago pelo cooperado (documents)
• descontado na produção cooperativista (documents with items)

-- Transactions
SELECT contact_id, c.name, amount, paid_at
FROM `transactions` t
join contacts c on c.id = t.contact_id
WHERE category_id = 25
and document_id is null
AND t.deleted_at IS NULL;

-- Documents
SELECT contact_id, contact_Name, amount, due_at
FROM `documents`
WHERE category_id = 25
AND d.deleted_at IS NULL;

-- Documents with items
SELECT d.contact_id, d.contact_Name, di.price, d.due_at
FROM `document_items` as di
JOIN documents as d on di.document_id = d.id
where di.item_id = 11
AND d.deleted_at IS NULL
AND di.deleted_at IS NULL

Necessário montar relatório com estas informações

[vitormattos]

Query com UNION

SELECT * FROM (
-- Transactions
SELECT contact_id, c.name, amount,
    paid_at AS due_at,
    t.id,
    'transaction' as 'table'
FROM transactions t
join contacts c on c.id = t.contact_id
WHERE category_id = 25
and document_id is null
AND t.deleted_at IS NULL
UNION
-- Documents
SELECT contact_id, contact_Name, amount, due_at,
    d.id,
    'documents' as 'table'
FROM documents d
WHERE d.category_id = 25
AND d.deleted_at IS NULL
UNION
-- Documents with items
SELECT d.contact_id, d.contact_Name, di.price, d.due_at,
    d.id,
    'documents_item' as 'table'
FROM document_items as di
JOIN documents as d on di.document_id = d.id
where di.item_id = 11
AND d.deleted_at IS NULL
AND di.deleted_at IS NULL
) x
ORDER BY x.name, x.due_at