Ajuster les stats par rapport à la nouvelle année

[pskl]

• Les nombres de pfmps ne sont pas intéressants, colonne F et I en euros -> garder les colonnes telles qu'elles mais rajouter deux colonnes avec les montants en euros correspondant
• Sortir les stats par année scolaire:
  2023-24 -> sortir les stats avec un peu moins de fréquence
  2024-25 -> sortir les stats hebdomadaires

Comparer les approches actuelles disponibles:

• requêtes de Guillaume
• libraire de Valentin

1. collecter les requêtes et voir comment les effectuer puis stocker leur résultat en base dans l'application

Argument en faveur de la lib de Valentin:

• agnostic de la couche de persistence
• méchanisme des exclusions dans la couche applicative

[gtournadre]

-- Requête stats globales
select to_char(CURRENT_DATE, 'DD/MM/YYYY') as date_ref,
'2023-2024' as annee_scolaire,
tab_da.nb_sco as nb_scolarites,
tab_da.nb_da_editees as nb_da_editees,
'' as pct_da_editees,
tab_rib.nb_eleves as nb_eleves,
tab_rib.nb_eleves_avec_rib_present as nb_eleves_avec_rib_present,
'' as pct_eleves_avec_rib_present,
tab_data_el.nb_donnees_eleves_presentes,
'' as pct_donnees_eleves_presentes,
tab_toutes_pfmp.nb_pfmp as toutes_les_pfmps,
tab_pfmp_valid.nb_pfmp_terminees_et_validees as nb_pfmp_terminees_et_validees,
tab_pfmp_valid.montant_pfmp_terminees_et_validee as montant_pfmp_terminees_et_validee,
tab_pfmp_comp.nb_pfmp_completees as nb_pfmp_completees,
tab_pfmp_comp.montant_pfmp_completees as montant_pfmp_completees,
tab_pfmp_pending.nb_pfmp_pending as nb_pfmp_pending,
tab_pfmp_pending.montant_theorique_pfmp_pending as montant_theorique_pfmp_pending,
tab_pfmp_fantome.nb_pfmp_fantome as nb_pfmp_fantome,
tab_pfmp_fantome.montant_theorique_pfmp_fantome as montant_theorique_pfmp_fantome,
tab_asp_paiement_reels.asp_nb_demande_paiement as asp_nb_demande_paiement,
tab_asp_paiement_reels.asp_montant_paye as asp_paiement_reel
from
(
select count(s.id) as nb_sco,
sum(case s.attributive_decision_version
when 0 then 0
else 1
end) as nb_da_editees
from schoolings s,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
) tab_da,
(
select count(tab_eleves_2023.id_eleve) as nb_eleves,
sum(case tab_ribs.id_rib > 0
when true then 1
else 0
end) as nb_eleves_avec_rib_present
from (select e.id as id_etablissement,
max(e.uai) as code_uai,
st.id as id_eleve
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id) tab_eleves_2023 left outer join
(select r.id as id_rib,
r.student_id as id_eleve,
r.establishment_id as id_etablissement
from ribs r
where r.archived_at is null
group by r.id, r.student_id, r.establishment_id) tab_ribs on tab_eleves_2023.id_eleve = tab_ribs.id_eleve and tab_eleves_2023.id_etablissement = tab_ribs.id_etablissement
) tab_rib,
(
select count(tab_eleves_2023.id_eleve) as nb_eleves,
sum(
case tab_donnees_eleves.donnees_eleve_ok is not null
when true then 1
else 0
end) as nb_donnees_eleves_presentes
from (select e.id as id_etablissement,
max(e.uai) as code_uai,
st.id as id_eleve
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id) tab_eleves_2023
left outer join
(select st.id as id_eleve,
'donnees_eleve_ok' as donnees_eleve_ok
from students st
where st.first_name is not null
and st.last_name is not null
and st.biological_sex is not null
and st.birthdate is not null
and st.birthplace_country_insee_code is not null
and (
((st.birthplace_country_insee_code = '100' or st.birthplace_country_insee_code = '99100') and st.birthplace_city_insee_code is not null) or
(st.birthplace_country_insee_code <> '100' and st.birthplace_country_insee_code <> '99100')
)
and st.address_country_code is not null
and (
((st.address_country_code = '100' or st.address_country_code = '99100') and st.address_city_insee_code is not null and st.address_postal_code is not null) or
(st.address_country_code <> '100' and st.address_country_code <> '99100')
)) tab_donnees_eleves
on tab_eleves_2023.id_eleve = tab_donnees_eleves.id_eleve
) tab_data_el,
(
select count(p.id) as nb_pfmp
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and sy.start_year = '2023'
) tab_toutes_pfmp,
(
select count(p.id) as nb_pfmp_terminees_et_validees,
sum(p.amount) as montant_pfmp_terminees_et_validee
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and sy.start_year = '2023'
and pt.to_state = 'validated'
and pt.most_recent = true
) tab_pfmp_valid,
(
select count(p.id) as nb_pfmp_completees,
sum(p.amount) as montant_pfmp_completees
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and m.id = c.mef_id
and sy.start_year = '2023'
and pt.to_state = 'completed'
and pt.most_recent = true
) tab_pfmp_comp,
(
select count(p.id) as nb_pfmp_pending,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_pending
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
) tab_pfmp_pending,
(
select count(p.id) as nb_pfmp_fantome,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_fantome
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
and (p.start_date > s.end_date or s.status=1)
) tab_pfmp_fantome,
(
select count(aprt.id) as asp_nb_demande_paiement,
sum(to_number(((aprt.metadata::json#>'{PAIEMENT}')::json->>'MTNET'), '999G9999DS')) as asp_montant_paye
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
asp_payment_requests apr,
asp_payment_request_transitions aprt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = apr.pfmp_id
and apr.id = aprt.asp_payment_request_id
and m.id = c.mef_id
and sy.start_year = '2023'
and aprt.to_state = 'paid'
and aprt.most_recent = true
) tab_asp_paiement_reels;

[gtournadre]

-- Req stats par BOP
select to_char(CURRENT_DATE, 'DD/MM/YYYY') as date_ref,
'2023-2024' as annee_scolaire,
tab_da.bop as bop,
tab_da.nb_sco as nb_scolarites,
tab_da.nb_da_editees as nb_da_editees,
'' as pct_da_editees,
tab_rib.nb_eleves as nb_eleves,
tab_rib.nb_eleves_avec_rib_present as nb_eleves_avec_rib_present,
'' as pct_eleves_avec_rib_present,
tab_data_el.nb_donnees_eleves_presentes,
'' as pct_donnees_eleves_presentes,
tab_toutes_pfmp.nb_pfmp as toutes_les_pfmps,
tab_pfmp_valid.nb_pfmp_terminees_et_validees as nb_pfmp_terminees_et_validees,
tab_pfmp_valid.montant_pfmp_terminees_et_validee as montant_pfmp_terminees_et_validee,
tab_pfmp_comp.nb_pfmp_completees as nb_pfmp_completees,
tab_pfmp_comp.montant_pfmp_completees as montant_pfmp_completees,
tab_pfmp_pending.nb_pfmp_pending as nb_pfmp_pending,
tab_pfmp_pending.montant_theorique_pfmp_pending as montant_theorique_pfmp_pending,
tab_pfmp_fantome.nb_pfmp_fantome as nb_pfmp_fantome,
tab_pfmp_fantome.montant_theorique_pfmp_fantome as montant_theorique_pfmp_fantome,
tab_asp_paiement_reels.asp_nb_demande_paiement as asp_nb_demande_paiement,
tab_asp_paiement_reels.asp_montant_paye as asp_paiement_reel
from
(
select case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop,
count(s.id) as nb_sco,
sum(case s.attributive_decision_version
when 0 then 0
else 1
end) as nb_da_editees
from schoolings s,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end
) tab_da left outer join
(
select count(tab_eleves_2023.id_eleve) as nb_eleves,
sum(case tab_ribs.id_rib > 0
when true then 1
else 0
end) as nb_eleves_avec_rib_present,
tab_eleves_2023.bop
from (select e.id as id_etablissement,
max(e.uai) as code_uai,
st.id as id_eleve,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end) tab_eleves_2023 left outer join
(select r.id as id_rib,
r.student_id as id_eleve,
r.establishment_id as id_etablissement
from ribs r
where r.archived_at is null
group by r.id, r.student_id, r.establishment_id) tab_ribs on tab_eleves_2023.id_eleve = tab_ribs.id_eleve and tab_eleves_2023.id_etablissement = tab_ribs.id_etablissement
group by tab_eleves_2023.bop
) tab_rib on tab_da.bop = tab_rib.bop left outer join
(
select count(tab_eleves_2023.id_eleve) as nb_eleves,
sum(
case tab_donnees_eleves.donnees_eleve_ok is not null
when true then 1
else 0
end) as nb_donnees_eleves_presentes,
tab_eleves_2023.bop
from (select e.id as id_etablissement,
max(e.uai) as code_uai,
st.id as id_eleve,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end) tab_eleves_2023
left outer join
(select st.id as id_eleve,
'donnees_eleve_ok' as donnees_eleve_ok
from students st
where st.first_name is not null
and st.last_name is not null
and st.biological_sex is not null
and st.birthdate is not null
and st.birthplace_country_insee_code is not null
and (
((st.birthplace_country_insee_code = '100' or st.birthplace_country_insee_code = '99100') and st.birthplace_city_insee_code is not null) or
(st.birthplace_country_insee_code <> '100' and st.birthplace_country_insee_code <> '99100')
)
and st.address_country_code is not null
and (
((st.address_country_code = '100' or st.address_country_code = '99100') and st.address_city_insee_code is not null and st.address_postal_code is not null) or
(st.address_country_code <> '100' and st.address_country_code <> '99100')
)) tab_donnees_eleves
on tab_eleves_2023.id_eleve = tab_donnees_eleves.id_eleve
group by tab_eleves_2023.bop
) tab_data_el on tab_da.bop = tab_data_el.bop left outer join
(
select case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop,
count(p.id) as nb_pfmp
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and sy.start_year = '2023'
group by case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end
) tab_toutes_pfmp on tab_da.bop = tab_toutes_pfmp.bop left outer join
(
select case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop,
count(p.id) as nb_pfmp_terminees_et_validees,
sum(p.amount) as montant_pfmp_terminees_et_validee
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and sy.start_year = '2023'
and pt.to_state = 'validated'
and pt.most_recent = true
group by case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end
) tab_pfmp_valid on tab_da.bop = tab_pfmp_valid.bop left outer join
(
select case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop,
count(p.id) as nb_pfmp_completees,
sum(p.amount) as montant_pfmp_completees
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and m.id = c.mef_id
and sy.start_year = '2023'
and pt.to_state = 'completed'
and pt.most_recent = true
group by case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end
) tab_pfmp_comp on tab_da.bop = tab_pfmp_comp.bop left outer join
(
select case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop,
count(p.id) as nb_pfmp_pending,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_pending
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
group by case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end
) tab_pfmp_pending on tab_da.bop = tab_pfmp_pending.bop left outer join
(
select case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop,
count(p.id) as nb_pfmp_fantome,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_fantome
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
and (p.start_date > s.end_date or s.status=1)
group by case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end
) tab_pfmp_fantome on tab_da.bop = tab_pfmp_fantome.bop left outer join
(
select case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop,
count(aprt.id) as asp_nb_demande_paiement,
sum(to_number(((aprt.metadata::json#>'{PAIEMENT}')::json->>'MTNET'), '999G9999DS')) as asp_montant_paye
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
asp_payment_requests apr,
asp_payment_request_transitions aprt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = apr.pfmp_id
and apr.id = aprt.asp_payment_request_id
and m.id = c.mef_id
and sy.start_year = '2023'
and aprt.to_state = 'paid'
and aprt.most_recent = true
group by case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end
) tab_asp_paiement_reels on tab_da.bop = tab_asp_paiement_reels.bop;

[gtournadre]

-- req stats par académie
select to_char(CURRENT_DATE, 'DD/MM/YYYY') as date_ref,
'2023-2024' as annee_scolaire,
tab_da.code_academie as code_academie,
tab_da.lib_academie as lib_academie,
tab_da.nb_sco as nb_scolarites,
tab_da.nb_da_editees as nb_da_editees,
'' as pct_da_editees,
tab_rib.nb_eleves as nb_eleves,
tab_rib.nb_eleves_avec_rib_present as nb_eleves_avec_rib_present,
'' as pct_eleves_avec_rib_present,
tab_data_el.nb_donnees_eleves_presentes,
'' as pct_donnees_eleves_presentes,
tab_toutes_pfmp.toutes_les_pfmps as toutes_les_pfmps,
tab_pfmp_valid.nb_pfmp_valid as nb_pfmp_valid,
tab_pfmp_valid.montant_pfmp_valid as montant_pfmp_valid,
tab_pfmp_complete.nb_pfmp_complete as nb_pfmp_complete,
tab_pfmp_complete.montant_pfmp_complete as montant_pfmp_complete,
tab_pfmp_pending.nb_pfmp_pending as nb_pfmp_pending,
tab_pfmp_pending.montant_theorique_pfmp_pending as montant_theorique_pfmp_pending,
tab_pfmp_fantome.nb_pfmp_fantome as nb_pfmp_fantome,
tab_pfmp_fantome.montant_theorique_pfmp_fantome as montant_theorique_pfmp_fantomepfmp_fantome,
tab_asp_paiement.asp_nb_demande_paiement as asp_nb_demande_paiement,
tab_asp_paiement.asp_montant_paye as asp_montant_paye
from
(
select e.academy_code as code_academie,
max(e.academy_label) as lib_academie,
count(s.id) as nb_sco,
sum(case s.attributive_decision_version
when 0 then 0
else 1
end) as nb_da_editees,
round(
(sum(case s.attributive_decision_version
when 0 then 0
else 1
end)::real / count(s.id)::real) * 10000) / 100 as part_DA_Editees
from schoolings s,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.academy_code
) tab_da left outer join
(
select tab_eleves_2023.code_academie,
count(tab_eleves_2023.id_eleve) as nb_eleves,
sum(case tab_ribs.id_rib > 0
when true then 1
else 0
end) as nb_eleves_avec_rib_present
from (select e.id as id_etablissement,
st.id as id_eleve,
max(e.academy_code) as code_academie
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id) tab_eleves_2023
left outer join
(select r.id as id_rib,
r.student_id as id_eleve,
r.establishment_id as id_etablissement
from ribs r
where r.archived_at is null
group by r.id, r.student_id, r.establishment_id) tab_ribs
on tab_eleves_2023.id_eleve = tab_ribs.id_eleve and tab_eleves_2023.id_etablissement = tab_ribs.id_etablissement
group by tab_eleves_2023.code_academie
) tab_rib on tab_da.code_academie = tab_rib.code_academie
left outer join
(
select tab_eleves_2023.code_academie,
sum(
case tab_donnees_eleves.donnees_eleve_ok is not null
when true then 1
else 0
end) as nb_donnees_eleves_presentes
from (select e.id as id_etablissement,
st.id as id_eleve,
max(e.academy_code) as code_academie
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id) tab_eleves_2023
left outer join
(select st.id as id_eleve,
'donnees_eleve_ok' as donnees_eleve_ok
from students st
where st.first_name is not null
and st.last_name is not null
and st.biological_sex is not null
and st.birthdate is not null
and st.birthplace_country_insee_code is not null
and (
((st.birthplace_country_insee_code = '100' or st.birthplace_country_insee_code = '99100') and st.birthplace_city_insee_code is not null) or
(st.birthplace_country_insee_code <> '100' and st.birthplace_country_insee_code <> '99100')
)
and st.address_country_code is not null
and (
((st.address_country_code = '100' or st.address_country_code = '99100') and st.address_city_insee_code is not null and st.address_postal_code is not null) or
(st.address_country_code <> '100' and st.address_country_code <> '99100')
)) tab_donnees_eleves
on tab_eleves_2023.id_eleve = tab_donnees_eleves.id_eleve
group by tab_eleves_2023.code_academie
) tab_data_el on tab_da.code_academie = tab_data_el.code_academie
left outer join
(
select e.academy_code as code_academie,
count(p.id) as toutes_les_pfmps
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and sy.start_year = '2023'
group by e.academy_code
) tab_toutes_pfmp on tab_da.code_academie = tab_toutes_pfmp.code_academie
left outer join
(
select e.academy_code as code_academie,
count(p.id) as nb_pfmp_valid,
sum(p.amount) as montant_pfmp_valid
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and sy.start_year = '2023'
and pt.to_state = 'validated'
and pt.most_recent = true
group by e.academy_code
) tab_pfmp_valid on tab_da.code_academie = tab_pfmp_valid.code_academie
left outer join
(
select e.academy_code as code_academie,
count(p.id) as nb_pfmp_complete,
sum(p.amount) as montant_pfmp_complete
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and m.id = c.mef_id
and sy.start_year = '2023'
and pt.to_state = 'completed'
and pt.most_recent = true
group by e.academy_code
) tab_pfmp_complete on tab_da.code_academie = tab_pfmp_complete.code_academie
left outer join
(
select e.academy_code as code_academie,
count(p.id) as nb_pfmp_pending,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_pending
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
group by e.academy_code
) tab_pfmp_pending on tab_da.code_academie = tab_pfmp_pending.code_academie
left outer join
(
select e.academy_code as code_academie,
count(p.id) as nb_pfmp_fantome,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_fantome
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
and (p.start_date > s.end_date or
s.status = 1)
group by e.academy_code
) tab_pfmp_fantome on tab_da.code_academie = tab_pfmp_fantome.code_academie
left outer join
(
select e.academy_code as code_academie,
count(aprt.id) as asp_nb_demande_paiement,
sum(to_number(((aprt.metadata::json#>'{PAIEMENT}')::json->>'MTNET'), '999G9999DS')) as asp_montant_paye
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
asp_payment_requests apr,
asp_payment_request_transitions aprt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = apr.pfmp_id
and apr.id = aprt.asp_payment_request_id
and m.id = c.mef_id
and sy.start_year = '2023'
and aprt.to_state = 'paid'
and aprt.most_recent = true
group by e.academy_code
) as tab_asp_paiement on tab_da.code_academie = tab_asp_paiement.code_academie
order by tab_da.lib_academie;

[gtournadre]

-- req stats par établissements
select to_char(CURRENT_DATE, 'DD/MM/YYYY') as date_ref,
'2023-2024' as annee_scolaire,
tab_da.code_uai as code_uai,
tab_da.etablissement as etablissement,
tab_da.commune as commune,
tab_da.lib_academie as academie,
tab_da.ministere_tutelle as ministere_tutelle,
tab_da.pub_priv as public_prive,
tab_da.nb_sco as nb_scolarites,
tab_da.nb_da_editees as nb_da_editees,
'' as pct_da_editees,
tab_rib.nb_eleves as nb_eleves,
tab_rib.nb_eleves_avec_rib_present as nb_eleves_avec_rib_present,
'' as pct_eleves_avec_rib_present,
tab_data_el.nb_donnees_eleves_presentes,
'' as pct_donnees_eleves_presentes,
tab_toutes_pfmp.toutes_les_pfmps as toutes_les_pfmps,
tab_pfmp_valid.nb_pfmp_valid as nb_pfmp_valid,
tab_pfmp_valid.montant_pfmp_valid as montant_pfmp_valid,
tab_pfmp_complete.nb_pfmp_complete as nb_pfmp_complete,
tab_pfmp_complete.montant_pfmp_complete as montant_pfmp_complete,
tab_pfmp_pending.nb_pfmp_pending as nb_pfmp_pending,
tab_pfmp_pending.montant_theorique_pfmp_pending as montant_theorique_pfmp_pending,
tab_pfmp_fantome.nb_pfmp_fantome as nb_pfmp_fantome,
tab_pfmp_fantome.montant_theorique_pfmp_fantome as montant_theorique_pfmp_fantomepfmp_fantome,
tab_asp_paiement.asp_nb_demande_paiement as asp_nb_demande_paiement,
tab_asp_paiement.asp_montant_paye as asp_montant_paye
from
(
select e.uai as code_uai,
max(e.name) as etablissement,
max(e.city) as commune,
max(e.academy_label) as lib_academie,
max(e.ministry) as ministere_tutelle,
max(case e.private_contract_type_code
when '99' then 'Public'
else 'Privé'
end) as pub_priv,
count(s.id) as nb_sco,
sum(case s.attributive_decision_version
when 0 then 0
else 1
end) as nb_da_editees
from schoolings s,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.uai
) tab_da left outer join
(
select tab_eleves_2023.code_uai as code_uai,
count(tab_eleves_2023.id_eleve) as nb_eleves,
sum(case tab_ribs.id_rib > 0
when true then 1
else 0
end) as nb_eleves_avec_rib_present
from (select e.id as id_etablissement,
max(e.uai) as code_uai,
max(e.name) as etablissement,
max(e.city) as commune,
st.id as id_eleve,
max(e.academy_code) as code_academie,
max(e.academy_label) as lib_academie,
max(e.ministry) as ministere_tutelle,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end) tab_eleves_2023
left outer join
(select r.id as id_rib,
r.student_id as id_eleve,
r.establishment_id as id_etablissement
from ribs r
where r.archived_at is null
group by r.id, r.student_id, r.establishment_id) tab_ribs
on tab_eleves_2023.id_eleve = tab_ribs.id_eleve and tab_eleves_2023.id_etablissement = tab_ribs.id_etablissement
group by tab_eleves_2023.code_uai
) tab_rib on tab_da.code_uai = tab_rib.code_uai
left outer join
(
select tab_eleves_2023.code_uai as code_uai,
sum(
case tab_donnees_eleves.donnees_eleve_ok is not null
when true then 1
else 0
end) as nb_donnees_eleves_presentes
from (select e.id as id_etablissement,
max(e.uai) as code_uai,
max(e.name) as etablissement,
max(e.city) as commune,
st.id as id_eleve,
max(e.academy_code) as code_academie,
max(e.academy_label) as lib_academie,
max(e.ministry) as ministere_tutelle,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end as bop
from schoolings s,
students st,
classes c,
establishments e,
mefs m,
wages w,
school_years sy
where s.classe_id = c.id
and s.student_id = st.id
and c.establishment_id = e.id
and c.mef_id = m.id
and m.school_year_id = sy.id
and w.school_year_id = sy.id
and w.mef_codes::jsonb ? m.code = true
and sy.start_year='2023'
group by e.id, st.id,
case m.ministry
when 0 then
case e.private_contract_type_code
when '99' then 'ENPU'
else 'ENPR'
end
when 1 then 'MASA'
when 3 then 'MER'
end) tab_eleves_2023
left outer join
(select st.id as id_eleve,
'donnees_eleve_ok' as donnees_eleve_ok
from students st
where st.first_name is not null
and st.last_name is not null
and st.biological_sex is not null
and st.birthdate is not null
and st.birthplace_country_insee_code is not null
and (
((st.birthplace_country_insee_code = '100' or st.birthplace_country_insee_code = '99100') and st.birthplace_city_insee_code is not null) or
(st.birthplace_country_insee_code <> '100' and st.birthplace_country_insee_code <> '99100')
)
and st.address_country_code is not null
and (
((st.address_country_code = '100' or st.address_country_code = '99100') and st.address_city_insee_code is not null and st.address_postal_code is not null) or
(st.address_country_code <> '100' and st.address_country_code <> '99100')
)) tab_donnees_eleves
on tab_eleves_2023.id_eleve = tab_donnees_eleves.id_eleve
group by tab_eleves_2023.code_uai
) tab_data_el on tab_da.code_uai = tab_data_el.code_uai
left outer join
(
select e.uai as code_uai,
count(p.id) as toutes_les_pfmps
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and sy.start_year = '2023'
group by e.uai
order by e.uai
) tab_toutes_pfmp on tab_da.code_uai = tab_toutes_pfmp.code_uai
left outer join
(
select e.uai as code_uai,
count(p.id) as nb_pfmp_valid,
sum(p.amount) as montant_pfmp_valid
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and m.id = c.mef_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and sy.start_year = '2023'
and pt.to_state = 'validated'
and pt.most_recent = true
group by e.uai
order by e.uai
) tab_pfmp_valid on tab_da.code_uai = tab_pfmp_valid.code_uai
left outer join
(
select e.uai as code_uai,
count(p.id) as nb_pfmp_complete,
sum(p.amount) as montant_pfmp_complete
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
pfmp_transitions pt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = pt.pfmp_id
and m.id = c.mef_id
and sy.start_year = '2023'
and pt.to_state = 'completed'
and pt.most_recent = true
group by e.uai
) tab_pfmp_complete on tab_da.code_uai = tab_pfmp_complete.code_uai
left outer join
(
select e.uai as code_uai,
count(p.id) as nb_pfmp_pending,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_pending
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
group by e.uai
) tab_pfmp_pending on tab_da.code_uai = tab_pfmp_pending.code_uai
left outer join
(
select e.uai as code_uai,
count(p.id) as nb_pfmp_fantome,
sum(
case round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate > w.yearly_cap
when true then
w.yearly_cap
else
round(((p.end_date - p.start_date) * 5 / 7), 0) * w.daily_rate
end
) as montant_theorique_pfmp_fantome
from students st,
schoolings s,
classes c,
establishments e,
pfmps p,
school_years sy,
mefs m,
wages w
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and m.id = c.mef_id
and w.mef_codes::jsonb ? m.code = true
and sy.id = w.school_year_id
and sy.start_year = '2023'
and p.amount is null
and p.day_count is null
and (p.start_date > s.end_date or
s.status = 1)
group by e.uai
) tab_pfmp_fantome on tab_da.code_uai = tab_pfmp_fantome.code_uai
left outer join
(
select e.uai as code_uai,
count(aprt.id) as asp_nb_demande_paiement,
sum(to_number(((aprt.metadata::json#>'{PAIEMENT}')::json->>'MTNET'), '999G9999DS')) as asp_montant_paye
from students st,
schoolings s,
classes c,
mefs m,
establishments e,
pfmps p,
school_years sy,
asp_payment_requests apr,
asp_payment_request_transitions aprt
where st.id = s.student_id
and c.id = s.classe_id
and e.id = c.establishment_id
and s.id = p.schooling_id
and sy.id = c.school_year_id
and p.id = apr.pfmp_id
and apr.id = aprt.asp_payment_request_id
and m.id = c.mef_id
and sy.start_year = '2023'
and aprt.to_state = 'paid'
and aprt.most_recent = true
group by e.uai
) as tab_asp_paiement on tab_da.code_uai = tab_asp_paiement.code_uai
order by tab_da.code_uai;

[gtournadre]

Fonctionnement :

une sous-requête par indicateur de même composition :
1. tab_da : Nb de scolarités et de DA éditées (la part du nb de DA éditées
par rapport au nombre de sco est calculée dans Exel)
2. tab_rib : Nb d'élève et de coordonnées bancaires saisies (la part du nb de DA éditées
par rapport au nombre de sco est calculée dans Exel)
3. tab_data_el : Nb d'élève dont les données nécessaires au paiement sont présentes
(la part du nb de DA éditées par rapport au nombre de sco est calculée dans Exel)
4. tab_toutes_pfmp : le nombre total de PFMP saisies
5. tab_pfmp_valid : le nombre de PFMP dont la transition est validated et leur montant correspondant (PFMP.amount)
6. tab_pfmp_comp : le nombre de PFMP dont la transition est completed et leur montant correspondant (PFMP.amount)
7. tab_pfmp_pending : le nombre de PFMP dont la transition est pending ou qui n'ont pas de transition et le montant
et leur montant théorique correspondant (nb de jour * 5/7 * taux journalier dans la limite
de plafond
8 tab_pfmp_fantome : le nombre de PFMP dont la transition est pending ou qui n'ont pas de transition et le montant
et leur montant théorique correspondant (nb de jour * 5/7 * taux journalier dans la limite
de plafond. On entantd par PFMP fantome une pfmp qui ne sera jamais complétée ni validée :
date de début de PFMP > date de fin de scolarité associée ou scolarité associée a un statut
apprenti
9 tab_asp_paiement_reels : nombre de demandes de paiement et montant des paiements versés par l'ASP. Basé sur les
ASP_payment_request dont la dernière transition est à paid.

Versions :

06/11/2024 : première Version 
07/11/2024 : prise en compte des PFMP qui n'ont pas de transition, considées pending
12/11/2024 : ajout de la notion de PFMP fantôme

A faire : prendre en compte les PFMP dont la transition est rectified (indus déclarés). Normalement en prenant PFMP.amount
on est bon. Les PFMP Rectified seront regroupées avec validated
A faire : Pour les PFMPs rectified, qui ont débouché sur des ordres de reversement, voir les retours ASP Pour
déduire les sommes des montants payés effectivement
A faire : filtrer les données des académies exclues du dispositif
(Nouvelle Calédonie, Wallis et Futuna, Polynésie Française)
A faire : rendre cette requête paramétrable avec l'année scolaire (2023 ET 2024)

[pskl]

De mon point de vue:

• les requêtes bruts ne sont pas maintenables car 1) bypass des validations (visible dans le cas des Exclusions) 2) La librairie de Valentin est très claire

Définir les types d'utilisateurs possibles de cette fameuse table de stats:

• Utilité 1: trouver les établissements qui ont besoin d'être relancé, ribs non présents, da non éditées, pfmp non validées
• Utilité 2: budget global ministère, montants anticipés, montants réels dans l'optique de parler aux supérieurs, de négocier les enveloppes
• Utilité 3: référents académiques -> à clarifier

Stockage:

• Moi je suis partant pour une table avec chaque ligne avec possiblement des attributs en jsonb pour les indicateurs complexes, attention à la dénormalisation des tables: problème avec les BOPs versus Etab par exemple

Etape 1:

• mettre à jour la lib de Valentin
• comparer les deux resultats et ajuster si besoin
• definir un format de stockage intelligent et sensé