52 lines
1.8 KiB
SQL
52 lines
1.8 KiB
SQL
-- Paket 0: lesende Bestandsaufnahme (Testinstanz). Keine Schreibzugriffe.
|
|
|
|
\echo '=== Angebote mit mehr als einer aktiven Fassung ==='
|
|
SELECT o.id AS offer_id,
|
|
o.name AS offer_name,
|
|
count(*)::int AS active_count,
|
|
string_agg(ov.number::text, ',' ORDER BY ov.number) AS active_numbers,
|
|
string_agg(DISTINCT ov.grant_kind, ',') AS grant_kinds
|
|
FROM offers o
|
|
JOIN offer_versions ov ON ov.offer_id = o.id AND ov.status = 'aktiv'
|
|
GROUP BY o.id, o.name
|
|
HAVING count(*) > 1
|
|
ORDER BY o.id;
|
|
|
|
\echo '=== Alle aktiven Registrierungsfassungen ==='
|
|
SELECT ov.offer_id,
|
|
o.name AS offer_name,
|
|
ov.id AS version_id,
|
|
ov.number,
|
|
ov.grant_kind
|
|
FROM offer_versions ov
|
|
JOIN offers o ON o.id = ov.offer_id
|
|
WHERE ov.status = 'aktiv'
|
|
AND ov.grant_kind = 'bei_registrierung'
|
|
AND ov.holder_kind = 'person'
|
|
ORDER BY ov.offer_id, ov.number;
|
|
|
|
\echo '=== Spalte offer_name auf order_lines ==='
|
|
SELECT column_name
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'order_lines'
|
|
AND column_name = 'offer_name';
|
|
|
|
\echo '=== Anzahl Bestellzeilen gesamt ==='
|
|
SELECT count(*)::int AS order_line_count FROM order_lines;
|
|
|
|
\echo '=== Grants mit offer_version_id in conditions (aktive Dubletten-Angebote) ==='
|
|
SELECT ov.offer_id,
|
|
ov.number AS version_number,
|
|
(a.conditions->>'offer_version_id') AS stored_version_id,
|
|
count(g.id)::int AS grant_count
|
|
FROM offer_versions ov
|
|
JOIN offers o ON o.id = ov.offer_id
|
|
LEFT JOIN acquisitions a ON (a.conditions->>'offer_version_id')::bigint = ov.id
|
|
LEFT JOIN grants g ON g.acquisition_id = a.id
|
|
WHERE ov.status = 'aktiv'
|
|
AND ov.offer_id IN (
|
|
SELECT offer_id FROM offer_versions WHERE status = 'aktiv' GROUP BY offer_id HAVING count(*) > 1
|
|
)
|
|
GROUP BY ov.offer_id, ov.number, a.conditions->>'offer_version_id'
|
|
ORDER BY ov.offer_id, ov.number; |