8 Pro
2020
8 Pro
'20
12:51
Hello,
if you are using invoices as we do than something like:
WITH end_timestamp AS (
SELECT ('2020-09-01 00:00:00' AT TIME ZONE 'UTC') AT TIME ZONE
'Europe/Prague' AS value
)
SELECT r.handle AS registrar_handle, r.organization AS
registrar_organization, z.fqdn AS zone, sum(rct.balance_change) AS credit
FROM registrar_credit_transaction rct
JOIN registrar_credit rc ON rc.id = rct.registrar_credit_id
JOIN registrar r ON r.id = rc.registrar_id
JOIN zone z ON z.id = rc.zone_id
JOIN registrarinvoice ri ON ri.registrarid = r.id AND ri.zone = z.id
LEFT JOIN invoice_registrar_credit_transaction_map im ON
im.registrar_credit_transaction_id = rct.id
LEFT JOIN invoice i ON i.id = im.invoice_id
LEFT JOIN invoice_operation io ON
io.registrar_credit_transaction_id = rct.id
WHERE COALESCE(io.crdate, i.crdate) < (SELECT value FROM
end_timestamp)
AND ri.fromdate <= (SELECT value FROM end_timestamp) AND
(ri.todate is null OR ri.todate >= (SELECT value FROM end_timestamp))
GROUP BY 1, 2, 3
ORDER BY 1, 3
should do the trick. The `end_timestamp` is input parameter to which you
want credit to count. Test to see if it works for you.
Best regards
Jiri
On 12/7/20 5:07 PM, Dr P Nyirenda wrote:
Hello,
Hope this finds you well in these troubled times.
We have a persistent request from a registrar asking us to " confirm
what our balance was on the 1st of December 2019" ... about 12 montsh ago.
We have repeatedly told the registrar that by the nature of EPP
transactions on a registry running FRED, we do not keep such balances on
the registry on a day to day basis except in regular backups of the
database.
I would like to hear what you think about such a request from a
registrar which connects to a registry running the FRED registry system
and how you would respond to it.
Is there an easier way of dealing with such a request other than by
remounting a whole FRED database on a test server from a previous FRED
backup of that date ?
Remounting a FRED database backup is a major task and we have told them
that if they insist then we would raise a fee or charge for it.
Any help?
Regards,
Paulos
=============================
Dr Paulos B Nyirenda
NIC.MW & .mw ccTLD
http://www.nic.mw
SDNP: http://www.sdnp.org.mw
Tel: +265-(0)-882 089 166
Cell: +265-(0)-888-824787
WhatsApp: +265-(0)-887386433
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
Virus-free. www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
_______________________________________________
fred-users mailing list
fred-users(a)lists.nic.cz
https://lists.nic.cz/mailman/listinfo/fred-users