8 Pro
2020
8 Pro
'20
16:27
On 8 Dec 2020 at 12:51, Jiri Sadek wrote:
Hello,
if you are using invoices as we do than something like:
I have tested this and it seems to work well, it lists credit balance for all registrars
per zone in
one interesting list.
Thanks, it will greatly assist me to respond to this query and save my skin !
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
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:
--
This email has been checked for viruses by AVG.
https://www.avg.com
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