pskl
commented
1 month ago today = Date.today
formatted_date = today.strftime('%d/%m/%Y')
results = {}
query1 = ActiveRecord::Base.connection.execute(
"SELECT COUNT(p.id), SUM(p.amount)
FROM pfmps p
JOIN asp_payment_requests apr ON p.id = apr.pfmp_id
JOIN asp_payment_request_transitions aprt ON apr.id = aprt.asp_payment_request_id
WHERE (aprt.to_state = 'integrated' OR aprt.to_state = 'sent')
AND aprt.most_recent = true
AND aprt.updated_at >= TO_DATE('#{formatted_date}', 'DD/MM/YYYY')"
)
results[:phrase1] = { count: query1[0]['count'], amount: query1[0]['sum'] }
# Phrase 2
query2 = ActiveRecord::Base.connection.execute(
"SELECT COUNT(p.id), SUM(p.amount)
FROM pfmps p
JOIN asp_payment_requests apr ON p.id = apr.pfmp_id
JOIN asp_payment_request_transitions aprt ON apr.id = aprt.asp_payment_request_id
WHERE (aprt.to_state = 'ready' OR aprt.to_state = 'pending')
AND aprt.most_recent = true
AND aprt.updated_at >= TO_DATE('#{formatted_date}', 'DD/MM/YYYY')"
)
results[:phrase2] = { count: query2[0]['count'], amount: query2[0]['sum'] }
query3 = ActiveRecord::Base.connection.execute(
"SELECT COUNT(p.id), SUM(p.amount)
FROM pfmps p
JOIN asp_payment_requests apr ON p.id = apr.pfmp_id
JOIN asp_payment_request_transitions aprt ON apr.id = aprt.asp_payment_request_id
WHERE aprt.to_state = 'paid'
AND aprt.most_recent = true
AND aprt.created_at > TO_DATE('#{formatted_date}', 'DD/MM/YYYY')"
)
results[:phrase3] = { count: query3[0]['count'], amount: query3[0]['sum'] }
puts "le nombre de PFMP envoyées en paiement à l'ASP : #{results[:phrase1][:count]} pour un montant de #{results[:phrase1][:amount]} €"
puts "le nombre de PFMP prêtes à être envoyées en paiement à l'ASP #{results[:phrase2][:count]} qui ne partiront pas cette semaine, le plafond étant atteint"
puts "le nombre de PFMP payées par l'ASP pour cette semaine. #{results[:phrase3][:count]} PFMP ont été intégrées pour un montant de #{results[:phrase3][:amount]} €"
Le lundi :
Le jeudi :
Voir le fichier des requêtes SQL
-- Phrase 1 select count(p.id), min(p.start_date), max(p.end_date), sum(p.amount), aprt.to_state, max(aprt.updated_at) from pfmps p, asp_payment_requests apr, asp_payment_request_transitions aprt where p.id = apr.pfmp_id and apr.id = aprt.asp_payment_request_id and (aprt.to_state = 'integrated' or aprt.to_state = 'send') and aprt.most_recent = true and aprt.updated_at >= to_date('18/07/2024', 'DD/MM/YYYY') group by aprt.to_state;
-- Phrase 2 select count(p.id), min(p.start_date), max(p.end_date), sum(p.amount), aprt.to_state, max(aprt.updated_at) from pfmps p, asp_payment_requests apr, asp_payment_request_transitions aprt where p.id = apr.pfmp_id and apr.id = aprt.asp_payment_request_id and (aprt.to_state = 'ready' or aprt.to_state = 'pending') and aprt.most_recent = true and aprt.updated_at >= to_date('18/07/2024', 'DD/MM/YYYY') group by aprt.to_state;
-- Phrase 3 select count(p.id), min(p.start_date), max(p.end_date), sum(p.amount), aprt.to_state, max(aprt.updated_at) from pfmps p, asp_payment_requests apr, asp_payment_request_transitions aprt where p.id = apr.pfmp_id and apr.id = aprt.asp_payment_request_id and (aprt.to_state = 'paid') and aprt.most_recent = true and aprt.created_at > to_date('18/07/2024', 'DD/MM/YYYY') group by aprt.to_state;