44 lines
1.8 KiB
Python
44 lines
1.8 KiB
Python
allow_connection = 'alter database "{name}" with allow_connections {option};'
|
|
connection_limit = 'alter database "{name}" with allow_connections {limit};'
|
|
getnumberdocuments = """
|
|
SELECT z.period,
|
|
sum(z.records) FILTER (WHERE z.type_doc = 'invoice_in') as invoice_in,
|
|
sum(z.records) FILTER (WHERE z.type_doc = 'invoice_out') as invoice_out,
|
|
sum(z.records) FILTER (WHERE z.type_doc = 'payroll') as payroll,
|
|
sum(z.records) FILTER (WHERE z.type_doc = 'event') as "event"
|
|
FROM (
|
|
SELECT
|
|
date_trunc('month',a.date_effective) as period,
|
|
concat('payroll') as type_doc,
|
|
count(a.id) as records
|
|
FROM staff_payroll_electronic AS a
|
|
WHERE a.electronic_state = 'authorized'
|
|
GROUP by a.date_effective, type_doc
|
|
UNION ALL
|
|
SELECT
|
|
date_trunc('month',b.invoice_date) as period,
|
|
case
|
|
when b.type = 'out' then concat('invoice_out')
|
|
when b.type = 'in' then concat('invoice_in')
|
|
else null
|
|
end as type_doc,
|
|
count(b.id) as records
|
|
FROM account_invoice AS b
|
|
WHERE b.electronic_state = 'authorized'
|
|
GROUP BY b.invoice_date, type_doc
|
|
UNION ALL
|
|
SELECT date_trunc('month', d.date_acceptance)::TIMESTAMP WITH TIME ZONE as period,
|
|
concat('event') as type_doc,
|
|
count(d.id) as records
|
|
FROM account_invoice_event_radian as d
|
|
WHERE d.response_acceptance IS NOT NULL
|
|
GROUP BY date_trunc('month', d.date_acceptance), type_doc
|
|
UNION ALL
|
|
SELECT date_trunc('month', e.date_receive_good_service)::TIMESTAMP WITH TIME ZONE as period,
|
|
concat('event') as type_doc,
|
|
count(e.id) as records
|
|
FROM account_invoice_event_radian as e
|
|
WHERE e.response_receive_good_service IS NOT NULL
|
|
GROUP BY date_trunc('month', e.date_receive_good_service), type_doc) as z where z.period = {period};
|
|
"""
|