1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313 | WITH
/*
* CTE 1 — Organisations B2B avec leur type de marché.
* On remonte : organization → organization_offer → organization_offer_type.
* Le type de marché est porté par l'offre (organization_offer_type_code).
*/
b2b_orgs AS (
SELECT
o.id AS org_id,
oot.code AS market_code,
oot.name AS market_name
FROM organization o
JOIN organization_offer oo ON oo.organization_id = o.id
AND oo.deleted_at IS NULL
JOIN organization_offer_type oot ON oot.code = oo.organization_offer_type_code
WHERE o.organization_business_type_code = 'B2B'
AND o.deleted_at IS NULL
-- Exclure les offres de type marché B2C (distinct du type org B2B/B2C)
AND oot.code != 'B2C'
),
/*
* CTE 2 — Contrats actifs (is_active = 1) appartenant à des orgs B2B,
* en excluant les types TEACHER et CREDIT_PACK.
*/
active_contracts AS (
SELECT
oc.id,
oc.organization_offer_id,
oc.max_organization_licenses,
oc.organization_contract_type_code,
oc.ref_id
FROM organization_contract oc
JOIN organization_offer oo ON oo.id = oc.organization_offer_id
AND oo.deleted_at IS NULL
JOIN b2b_orgs bo ON bo.org_id = oo.organization_id
WHERE oc.is_active = 1
AND oc.deleted_at IS NULL
AND (
oc.organization_contract_type_code NOT IN ('TEACHER', 'CREDIT_PACK')
OR oc.organization_contract_type_code IS NULL
)
),
/*
* CTE 3 — Contrats SSO parents qui possèdent au moins un contrat enfant
* (renouvellement) actif.
*
* Un enfant est identifié par : ref_id IS NOT NULL AND ref_id != id.
* Ces parents sont EXCLUS du calcul direct : leurs enfants les représentent
* et contribuent chacun leur propre max_organization_licenses.
*
* Comportement avec plusieurs renouvellements actifs simultanément :
* chaque enfant est une ligne indépendante dans eligible_contracts,
* ce qui revient à sommer tous les renouvellements actifs — identique
* à la logique ActiveSSOWithRenewalsLicenseInfoProvider dans l'admin.
*/
sso_parents_with_active_renewals AS (
SELECT DISTINCT ac_child.ref_id AS parent_id
FROM active_contracts ac_child
WHERE ac_child.ref_id IS NOT NULL
AND ac_child.ref_id != ac_child.id
AND ac_child.organization_contract_type_code = 'SSO'
),
/*
* CTE 4 — Contrats éligibles au calcul :
* - tous les contrats actifs B2B non-exclus (CTE 2)
* - SAUF les parents SSO dont les enfants sont actifs (CTE 3)
*/
eligible_contracts AS (
SELECT ac.*
FROM active_contracts ac
WHERE ac.id NOT IN (SELECT parent_id FROM sso_parents_with_active_renewals)
),
/*
* CTE 5a — Toutes les licences utilisateur (user_role_code = USER), sans filtre de date,
* agrégées par organization_contract_id (toutes valeurs de renewal_id incluses).
*
* Utilisations :
* - Stock non-SSO : remaining_seats + cnt
* - Total licences : cnt (non-SSO et SSO direct)
*
* IMPORTANT — pas de filtre sur organization_contract_renewal_id :
* certains contrats SSO "directs" (sans enfant actif) ont des licences
* dont le renewal_id pointe vers un ancien enfant inactif. Les inclure ici
* garantit un stock et un total corrects.
*/
license_counts_all_by_contract AS (
SELECT
ol.organization_contract_id,
COUNT(ol.id) AS cnt
FROM organization_license ol
JOIN `user` u ON u.id = ol.user_id
WHERE ol.deleted_at IS NULL
AND u.deleted_at IS NULL
AND u.user_role_code = 'USER'
GROUP BY ol.organization_contract_id
),
/*
* CTE 5b — Licences ACTIVES (scopeActiveV2) agrégées par organization_contract_id.
*
* Règle "licence active" :
* starts_at <= NOW() AND (ends_at >= NOW() OR ends_at IS NULL)
*
* Même logique que 5a : pas de filtre sur renewal_id pour couvrir les SSO
* directs dont les licences pointent vers des renouvellements inactifs.
*/
license_counts_active_by_contract AS (
SELECT
ol.organization_contract_id,
COUNT(ol.id) AS cnt
FROM organization_license ol
JOIN `user` u ON u.id = ol.user_id
WHERE ol.deleted_at IS NULL
AND u.deleted_at IS NULL
AND u.user_role_code = 'USER'
AND ol.starts_at <= NOW()
AND (ol.ends_at >= NOW() OR ol.ends_at IS NULL)
GROUP BY ol.organization_contract_id
),
/*
* CTE 5c — Toutes les licences utilisateur agrégées par
* organization_contract_renewal_id.
* Utilisé pour le total et le stock des contrats SSO enfants (renouvellements).
*/
license_counts_all_by_renewal AS (
SELECT
ol.organization_contract_renewal_id,
COUNT(ol.id) AS cnt
FROM organization_license ol
JOIN `user` u ON u.id = ol.user_id
WHERE ol.deleted_at IS NULL
AND u.deleted_at IS NULL
AND u.user_role_code = 'USER'
AND ol.organization_contract_renewal_id IS NOT NULL
GROUP BY ol.organization_contract_renewal_id
),
/*
* CTE 5d — Licences ACTIVES (scopeActiveV2) agrégées par
* organization_contract_renewal_id.
* Utilisé pour les actives des contrats SSO enfants (renouvellements).
*/
license_counts_active_by_renewal AS (
SELECT
ol.organization_contract_renewal_id,
COUNT(ol.id) AS cnt
FROM organization_license ol
JOIN `user` u ON u.id = ol.user_id
WHERE ol.deleted_at IS NULL
AND u.deleted_at IS NULL
AND u.user_role_code = 'USER'
AND ol.organization_contract_renewal_id IS NOT NULL
AND ol.starts_at <= NOW()
AND (ol.ends_at >= NOW() OR ol.ends_at IS NULL)
GROUP BY ol.organization_contract_renewal_id
),
/*
* CTE 6 — Sièges restants par contrat (utilisé uniquement pour les
* contrats non-SSO).
* remaining_seats représente les licences allouées mais pas encore
* consommées par un utilisateur.
*/
remaining_seats AS (
SELECT
ocs.organization_contract_id,
SUM(ocs.remaining_seats) AS total
FROM organization_contract_seat ocs
WHERE ocs.deleted_at IS NULL
GROUP BY ocs.organization_contract_id
),
/*
* CTE 7 — Métriques par contrat éligible.
*
* Stock (dénominateur commun des deux taux) :
* - SSO (direct ou renouvellement) → max_organization_licenses
* - Non-SSO → remaining_seats + all user licenses
*
* Total licences (numérateur taux total) :
* - SSO enfant → via organization_contract_renewal_id (CTE 5c)
* - SSO direct + non-SSO → via organization_contract_id (CTE 5a)
*
* Licences actives (numérateur taux actif, scopeActiveV2) :
* - SSO enfant → via organization_contract_renewal_id (CTE 5d)
* - SSO direct + non-SSO → via organization_contract_id (CTE 5b)
*/
contract_metrics AS (
SELECT
ec.organization_offer_id,
-- Stock du contrat
CASE
WHEN ec.organization_contract_type_code = 'SSO'
THEN ec.max_organization_licenses
ELSE
COALESCE(rs.total, 0) + COALESCE(lc_all_contract.cnt, 0)
END AS stock_licenses,
-- Total licences (toutes dates)
CASE
WHEN ec.organization_contract_type_code = 'SSO'
AND ec.ref_id IS NOT NULL
AND ec.ref_id != ec.id
THEN COALESCE(lc_all_renewal.cnt, 0)
ELSE
COALESCE(lc_all_contract.cnt, 0)
END AS total_licenses,
-- Licences actives (scopeActiveV2)
CASE
WHEN ec.organization_contract_type_code = 'SSO'
AND ec.ref_id IS NOT NULL
AND ec.ref_id != ec.id
THEN COALESCE(lc_active_renewal.cnt, 0)
ELSE
COALESCE(lc_active_contract.cnt, 0)
END AS active_licenses
FROM eligible_contracts ec
LEFT JOIN remaining_seats rs
ON rs.organization_contract_id = ec.id
LEFT JOIN license_counts_all_by_contract lc_all_contract
ON lc_all_contract.organization_contract_id = ec.id
LEFT JOIN license_counts_active_by_contract lc_active_contract
ON lc_active_contract.organization_contract_id = ec.id
LEFT JOIN license_counts_all_by_renewal lc_all_renewal
ON lc_all_renewal.organization_contract_renewal_id = ec.id
LEFT JOIN license_counts_active_by_renewal lc_active_renewal
ON lc_active_renewal.organization_contract_renewal_id = ec.id
)
/*
* Agrégation finale par type de marché + ligne Total.
* Taux total = total licences / stock × 100
* Taux actif = licences actives / stock × 100
* Taux inactif = licences inactives / stock × 100
*/
SELECT * FROM (
SELECT
bo.market_code,
bo.market_name,
SUM(cm.stock_licenses) AS total_stock,
SUM(cm.total_licenses) AS total_licenses,
ROUND(
CASE
WHEN SUM(cm.stock_licenses) > 0
THEN SUM(cm.total_licenses) / SUM(cm.stock_licenses) * 100
ELSE 0
END, 1
) AS total_rate_pct,
SUM(cm.active_licenses) AS total_active,
ROUND(
CASE
WHEN SUM(cm.stock_licenses) > 0
THEN SUM(cm.active_licenses) / SUM(cm.stock_licenses) * 100
ELSE 0
END, 1
) AS active_rate_pct,
SUM(cm.total_licenses) - SUM(cm.active_licenses) AS inactive_licenses,
ROUND(
CASE
WHEN SUM(cm.stock_licenses) > 0
THEN (SUM(cm.total_licenses) - SUM(cm.active_licenses)) / SUM(cm.stock_licenses) * 100
ELSE 0
END, 1
) AS inactive_rate_pct
FROM contract_metrics cm
JOIN organization_offer oo ON oo.id = cm.organization_offer_id
AND oo.deleted_at IS NULL
JOIN b2b_orgs bo ON bo.org_id = oo.organization_id
GROUP BY bo.market_code, bo.market_name
UNION ALL
SELECT
'TOTAL',
'Total',
SUM(cm.stock_licenses),
SUM(cm.total_licenses),
ROUND(
CASE
WHEN SUM(cm.stock_licenses) > 0
THEN SUM(cm.total_licenses) / SUM(cm.stock_licenses) * 100
ELSE 0
END, 1
),
SUM(cm.active_licenses),
ROUND(
CASE
WHEN SUM(cm.stock_licenses) > 0
THEN SUM(cm.active_licenses) / SUM(cm.stock_licenses) * 100
ELSE 0
END, 1
),
SUM(cm.total_licenses) - SUM(cm.active_licenses),
ROUND(
CASE
WHEN SUM(cm.stock_licenses) > 0
THEN (SUM(cm.total_licenses) - SUM(cm.active_licenses)) / SUM(cm.stock_licenses) * 100
ELSE 0
END, 1
)
FROM contract_metrics cm
JOIN organization_offer oo ON oo.id = cm.organization_offer_id
AND oo.deleted_at IS NULL
JOIN b2b_orgs bo ON bo.org_id = oo.organization_id
) AS combined
ORDER BY CASE WHEN market_code = 'TOTAL' THEN 1 ELSE 0 END, active_rate_pct DESC;
|