· 6 years ago · Dec 23, 2019, 09:04 AM
1DELIMITER @@
2DROP PROCEDURE IF EXISTS `sp_panel_info`;
3CREATE PROCEDURE `sp_panel_info`(IN `code` VARCHAR(20))
4BEGIN
5
6 if (left(code,
7 1) = 'C') then
8
9 DROP TABLE IF EXISTS panel_infos;
10
11 CREATE TABLE
12 panel_infos
13 (
14 ID int,
15 enabled_domains int,
16 disabled_domains int,
17 email8gEnabled int,
18 email8gDisabled int,
19 email25g int,
20 email25gDisabled int,
21 email50g int,
22 email50gDisabled int,
23 archive_enabled int,
24 archive_disabled int,
25 postmaster_domain_enabled int,
26 postmaster_domain_disabled int,
27 alias_domains int,
28 alias_emails int,
29 managers int,
30 team_members int,
31 mail_overquota int,
32 total_domains int,
33 total_emails8GB int,
34 total_emails25GB int,
35 total_emails50GB int,
36 total_archives int,
37 billableEmails8GB int,
38 billableEmails25GB int,
39 billableEmails50GB int
40 );
41
42 insert into panel_infos (ID, enabled_domains, disabled_domains)
43 select `users`.`id` as ID,
44 ifnull(sum(case when d.status = 'enabled' then 1 else 0 end), 0) AS `enabled_domains`,
45 ifnull(sum(case when d.status = 'disabled' then 1 else 0 end), 0) AS `disabled_domains`
46 from `users`
47 join `customers` `c` on
48 `users`.`id` = `c`.`id`
49 left join `domains` `d` on
50 `d`.`customer_id` = `c`.`id`
51 and `d`.`status` in ('enabled', 'disabled')
52 where c.code = code
53 group by `users`.`id`;
54
55 update panel_infos p,
56 (select sum(
57 case when `qbpanel`.`email_accounts`.`status` = 'enabled' then 1 else 0 end
58 ) AS `postmaster_domain_enabled`,
59 sum(
60 case when `qbpanel`.`email_accounts`.`status` = 'disabled' then 1 else 0 end
61 ) AS `postmaster_domain_disabled`
62 from `qbpanel`.`email_accounts`
63 join `qbpanel`.`domains` d on `qbpanel`.`email_accounts`.`domain_id` = d.`id`
64 join `customers` on
65 `customers`.`id` = d.`customer_id`
66 where `qbpanel`.`customers`.`code` = code
67 and not exists(select 0
68 from `qbpanel`.`email_accounts` a
69 where a.domain_id = d.id
70 and a.name != 'postmaster'
71 )
72 ) a
73 set p.postmaster_domain_enabled = a.postmaster_domain_enabled,
74 p.postmaster_domain_disabled =a.postmaster_domain_disabled;
75
76 update panel_infos p,
77 (select ifnull(sum(case when `email_accounts`.`status` = 'enabled' then 1 else 0 end), 0) as email8gEnabled,
78 ifnull(sum(case when `email_accounts`.`status` = 'disabled' then 1 else 0 end),
79 0) as email8gDisabled
80 from `email_accounts`
81 join `domains` on
82 `email_accounts`.`domain_id` = `domains`.`id`
83 join `customers` on
84 `customers`.`id` = `domains`.`customer_id`
85 and `customers`.`code` = code
86 where `email_accounts`.`max_email_quota` <= 8589934592
87 and `email_accounts`.`name` != 'postmaster') a
88 set p.email8gEnabled = a.email8gEnabled,
89 p.email8gDisabled=a.email8gDisabled;
90
91
92 update panel_infos p,
93 (select ifnull(sum(case when `email_accounts`.`status_detail` = 'ok' then 1 else 0 end), 0) as email25g,
94 ifnull(sum(case when `email_accounts`.`status` = 'disabled' then 1 else 0 end),
95 0) as email25gDisabled
96 from `email_accounts`
97 join `domains` on
98 `email_accounts`.`domain_id` = `domains`.`id`
99 join `customers` on
100 `customers`.`id` = `domains`.`customer_id`
101 and `customers`.`code` = code
102 where `email_accounts`.`max_email_quota` > 8589934592 and `email_accounts`.`max_email_quota` <= 26843545600
103 and `email_accounts`.`name` != 'postmaster') a
104 set p.email25g = a.email25g,
105 p.email25gDisabled = a.email25gDisabled;
106
107
108 update panel_infos p,
109 (select ifnull(sum(case when `email_accounts`.`status_detail` = 'ok' then 1 else 0 end), 0) as email50g,
110 ifnull(sum(case when `email_accounts`.`status` = 'disabled' then 1 else 0 end),
111 0) as email50gDisabled
112 from `email_accounts`
113 join `domains` on
114 `email_accounts`.`domain_id` = `domains`.`id`
115 join `customers` on
116 `customers`.`id` = `domains`.`customer_id`
117 and `customers`.`code` = code
118 where `email_accounts`.`max_email_quota` > 26843545600
119 and `email_accounts`.`name` != 'postmaster') a
120 set p.email50g = a.email50g,
121 p.email50gDisabled = a.email50gDisabled;
122
123 update panel_infos p,
124 (select ifnull(sum(case when `email_accounts`.`archive_status` = 'enabled' then 1 else 0 end),
125 0) as archive_enabled,
126 ifnull(sum(case when `email_accounts`.`archive_status` = 'disabled' then 1 else 0 end),
127 0) as archive_disabled
128 from `email_accounts`
129 join `domains` on
130 `email_accounts`.`domain_id` = `domains`.`id`
131 join `customers` on
132 `customers`.`id` = `domains`.`customer_id`
133 and `customers`.`code` = code) a
134 set p.archive_enabled = a.archive_enabled,
135 p.archive_disabled=a.archive_disabled;
136
137 update panel_infos p,
138 (select count(0) as alias_domains
139 from `alias_domains`
140 join `domains` on
141 `alias_domains`.`domain_id` = `domains`.`id`
142 join `customers` on
143 `customers`.`id` = `domains`.`customer_id`
144 and `customers`.`code` = code) a
145 set p.alias_domains = a.alias_domains;
146
147 update panel_infos p,
148 (select count(0) as alias_emails
149 from `alias_email_accounts`
150 join `domains` on
151 `alias_email_accounts`.`domain_id` = `domains`.`id`
152 join `customers` on
153 `customers`.`id` = `domains`.`customer_id`
154 and `customers`.`code` = code) a
155 set p.alias_emails = a.alias_emails;
156
157 update panel_infos p,
158 (select count(0) as managers
159 from `managers`
160 join `customers` on
161 `customers`.`id` = `managers`.`customer_id`
162 and `customers`.`code` = code) a
163 set p.managers = a.managers;
164
165 update panel_infos p,
166 (select count(0) as team_members
167 from `team_members`
168 join `customers` on
169 `customers`.`id` = `team_members`.`customer_id`
170 and `customers`.`code` = code) a
171 set p.team_members = a.team_members;
172
173 update panel_infos p,
174 (select count(0) as mail_overquota
175 from `email_accounts`
176 join `domains` on
177 `email_accounts`.`domain_id` = `domains`.`id`
178 join `customers` on
179 `customers`.`id` = `domains`.`customer_id`
180 and
181 `customers`.`code` = code
182 where `email_accounts`.quota_level in (1, 2)) a
183 set p.mail_overquota=a.mail_overquota;
184
185
186 update panel_infos p,
187 (select count(d.id) as `total_domains`
188 from `users`
189 join `customers` `c` on
190 `users`.`id` = `c`.`id`
191 left join `domains` `d` on
192 `d`.`customer_id` = `c`.`id`
193 where c.code = code) a
194 set p.total_domains=a.total_domains;
195
196 update panel_infos p,
197 (select count(email_accounts.id) as `total_emails8GB`
198 from `email_accounts`
199 join `domains` on
200 `email_accounts`.`domain_id` = `domains`.`id`
201 join `customers` on
202 `customers`.`id` = `domains`.`customer_id`
203 and `customers`.`code` = code
204 where `email_accounts`.`max_email_quota` <= 8589934592
205 and `email_accounts`.`name` != 'postmaster') a
206 set p.total_emails8GB = a.total_emails8GB;
207
208 update panel_infos p,
209 (select count(email_accounts.id) as `total_emails25GB`
210 from `email_accounts`
211 join `domains` on
212 `email_accounts`.`domain_id` = `domains`.`id`
213 join `customers` on
214 `customers`.`id` = `domains`.`customer_id`
215 and `customers`.`code` = code
216 where `email_accounts`.`max_email_quota` > 8589934592 and `email_accounts`.`max_email_quota` <= 26843545600
217 and `email_accounts`.`name` != 'postmaster') a
218 set p.total_emails25GB = a.total_emails25GB;
219
220
221 update panel_infos p,
222 (select count(email_accounts.id) as `total_emails50GB`
223 from `email_accounts`
224 join `domains` on
225 `email_accounts`.`domain_id` = `domains`.`id`
226 join `customers` on
227 `customers`.`id` = `domains`.`customer_id`
228 and `customers`.`code` = code
229 where `email_accounts`.`max_email_quota` > 26843545600
230 and `email_accounts`.`name` != 'postmaster') a
231 set p.total_emails50GB = a.total_emails50GB;
232
233 update panel_infos p,
234 (select count(email_accounts.id) as `total_archives`
235 from `email_accounts`
236 join `domains` on
237 `email_accounts`.`domain_id` = `domains`.`id`
238 join `customers` on
239 `customers`.`id` = `domains`.`customer_id`
240 and `customers`.`code` = code
241 where `email_accounts`.`archive` = 1) a
242 set p.total_archives = a.total_archives;
243
244 update panel_infos p,
245 (
246 select sum(a.emails) as `billableEmails8GB`
247 from (
248 select count(email_accounts.id) as `emails`
249 from `email_accounts`
250 join `domains` on
251 `email_accounts`.`domain_id` = `domains`.`id`
252 join `customers` on
253 `customers`.`id` = `domains`.`customer_id`
254 and `customers`.`code` = code
255 where `email_accounts`.`max_email_quota` <= 8589934592
256 and `email_accounts`.`name` != 'postmaster'
257 and domains.id not in (select domain_id from v_postmasters)
258 union all
259 select count(email_accounts.id) as `emails`
260 from `email_accounts`
261 join `domains` on
262 `email_accounts`.`domain_id` = `domains`.`id`
263 join `customers` on
264 `customers`.`id` = `domains`.`customer_id`
265 and `customers`.`code` = code
266 where `email_accounts`.`max_email_quota` <= 8589934592
267 and `email_accounts`.`name` = 'postmaster'
268 and domains.id in (select domain_id from v_postmasters)
269 ) a
270 ) m
271 set p.billableEmails8GB = m.billableEmails8GB;
272
273 update panel_infos p,
274 (select total_emails25GB
275 from `panel_infos`) a
276 set p.billableEmails25GB = a.total_emails25GB;
277
278 update panel_infos p,
279 (select total_emails50GB
280 from `panel_infos`) a
281 set p.billableEmails50GB = a.total_emails50GB;
282
283 select * from panel_infos;
284
285 elseif (left(code,
286 1) = 'T') then
287
288 DROP TABLE IF EXISTS panel_infos;
289
290 set @customer_code = '';
291
292 select customers.code
293 into @customer_code
294 from team_members
295 inner join customers on team_members.customer_id = customers.id
296 where team_members.code = code;
297
298 CREATE TABLE
299 panel_infos
300 (
301 ID int,
302 enabled_domains int,
303 disabled_domains int,
304 email8gEnabled int,
305 email8gDisabled int,
306 email25g int,
307 email25gDisabled int,
308 email50g int,
309 email50gDisabled int,
310 archive_enabled int,
311 archive_disabled int,
312 postmaster_domain_enabled int,
313 postmaster_domain_disabled int,
314 alias_domains int,
315 alias_emails int,
316 managers int,
317 team_members int,
318 mail_overquota int,
319 total_domains int,
320 total_emails8GB int,
321 total_emails25GB int,
322 total_emails50GB int,
323 total_archives int,
324 billableEmails8GB int,
325 billableEmails25GB int,
326 billableEmails50GB int
327 );
328
329 insert into panel_infos (ID, enabled_domains, disabled_domains)
330 select `users`.`id` as ID,
331 ifnull(sum(case when d.status = 'enabled' then 1 else 0 end), 0) AS `enabled_domains`,
332 ifnull(sum(case when d.status = 'disabled' then 1 else 0 end), 0) AS `disabled_domains`
333 from `users`
334 join `customers` `c` on
335 `users`.`id` = `c`.`id`
336 left join `domains` `d` on
337 `d`.`customer_id` = `c`.`id`
338 and `d`.`status` in ('enabled', 'disabled')
339 where c.code = @customer_code
340 group by `users`.`id`;
341
342 update panel_infos p,
343 (select sum(
344 case when `qbpanel`.`email_accounts`.`status` = 'enabled' then 1 else 0 end
345 ) AS `postmaster_domain_enabled`,
346 sum(
347 case when `qbpanel`.`email_accounts`.`status` = 'disabled' then 1 else 0 end
348 ) AS `postmaster_domain_disabled`
349 from `qbpanel`.`email_accounts`
350 join `qbpanel`.`domains` d on `qbpanel`.`email_accounts`.`domain_id` = d.`id`
351 join `customers` on
352 `customers`.`id` = d.`customer_id`
353 where `qbpanel`.`customers`.`code` = @customer_code
354 and not exists(select 0
355 from `qbpanel`.`email_accounts` a
356 where a.domain_id = d.id
357 and a.name != 'postmaster'
358 )
359 ) a
360 set p.postmaster_domain_enabled = a.postmaster_domain_enabled,
361 p.postmaster_domain_disabled =a.postmaster_domain_disabled;
362
363 update panel_infos p,
364 (select ifnull(sum(case when `email_accounts`.`status` = 'enabled' then 1 else 0 end), 0) as email8gEnabled,
365 ifnull(sum(case when `email_accounts`.`status` = 'disabled' then 1 else 0 end),
366 0) as email8gDisabled
367 from `email_accounts`
368 join `domains` on
369 `email_accounts`.`domain_id` = `domains`.`id`
370 join `customers` on
371 `customers`.`id` = `domains`.`customer_id`
372 and `customers`.`code` = @customer_code
373 where `email_accounts`.`max_email_quota` <= 8589934592
374 and `email_accounts`.`name` != 'postmaster') a
375 set p.email8gEnabled = a.email8gEnabled,
376 p.email8gDisabled=a.email8gDisabled;
377
378
379 update panel_infos p,
380 (select ifnull(sum(case when `email_accounts`.`status_detail` = 'ok' then 1 else 0 end), 0) as email25g,
381 ifnull(sum(case when `email_accounts`.`status` = 'disabled' then 1 else 0 end),
382 0) as email25gDisabled
383 from `email_accounts`
384 join `domains` on
385 `email_accounts`.`domain_id` = `domains`.`id`
386 join `customers` on
387 `customers`.`id` = `domains`.`customer_id`
388 and `customers`.`code` = @customer_code
389 where `email_accounts`.`max_email_quota` > 8589934592 and `email_accounts `.` max_email_quota` <= 26843545600
390 and `email_accounts`.`name` != 'postmaster') a
391 set p.email25g = a.email25g,
392 p.email25gDisabled = a.email25gDisabled;
393
394
395 update panel_infos p,
396 (select ifnull(sum(case when `email_accounts`.`status_detail` = 'ok' then 1 else 0 end), 0) as email50g,
397 ifnull(sum(case when `email_accounts`.`status` = 'disabled' then 1 else 0 end),
398 0) as email50gDisabled
399 from `email_accounts`
400 join `domains` on
401 `email_accounts`.`domain_id` = `domains`.`id`
402 join `customers` on
403 `customers`.`id` = `domains`.`customer_id`
404 and `customers`.`code` = @customer_code
405 where `email_accounts`.`max_email_quota` > 26843545600
406 and `email_accounts`.`name` != 'postmaster') a
407 set p.email50g = a.email50g,
408 p.email50gDisabled = a.email50gDisabled;
409
410 update panel_infos p,
411 (select ifnull(sum(case when `email_accounts`.`archive_status` = 'enabled' then 1 else 0 end),
412 0) as archive_enabled,
413 ifnull(sum(case when `email_accounts`.`archive_status` = 'disabled' then 1 else 0 end),
414 0) as archive_disabled
415 from `email_accounts`
416 join `domains` on
417 `email_accounts`.`domain_id` = `domains`.`id`
418 join `customers` on
419 `customers`.`id` = `domains`.`customer_id`
420 and `customers`.`code` = @customer_code) a
421 set p.archive_enabled = a.archive_enabled,
422 p.archive_disabled=a.archive_disabled;
423
424 update panel_infos p,
425 (select count(0) as alias_domains
426 from `alias_domains`
427 join `domains` on
428 `alias_domains`.`domain_id` = `domains`.`id`
429 join `customers` on
430 `customers`.`id` = `domains`.`customer_id`
431 and `customers`.`code` = @customer_code) a
432 set p.alias_domains = a.alias_domains;
433
434 update panel_infos p,
435 (select count(0) as alias_emails
436 from `alias_email_accounts`
437 join `domains` on
438 `alias_email_accounts`.`domain_id` = `domains`.`id`
439 join `customers` on
440 `customers`.`id` = `domains`.`customer_id`
441 and `customers`.`code` = @customer_code) a
442 set p.alias_emails = a.alias_emails;
443
444 update panel_infos p,
445 (select count(0) as managers
446 from `managers`
447 join `customers` on
448 `customers`.`id` = `managers`.`customer_id`
449 and `customers`.`code` = @customer_code) a
450 set p.managers = a.managers;
451
452 update panel_infos p,
453 (select count(0) as team_members
454 from `team_members`
455 join `customers` on
456 `customers`.`id` = `team_members`.`customer_id`
457 and `customers`.`code` = @customer_code) a
458 set p.team_members = a.team_members;
459
460 update panel_infos p,
461 (select count(0) as mail_overquota
462 from `email_accounts`
463 join `domains` on
464 `email_accounts`.`domain_id` = `domains`.`id`
465 join `customers` on
466 `customers`.`id` = `domains`.`customer_id`
467 and
468 `customers`.`code` = @customer_code
469 where `email_accounts`.quota_level in (1, 2)) a
470 set p.mail_overquota=a.mail_overquota;
471
472 update panel_infos p,
473 (select count(d.id) as `total_domains`
474 from `users`
475 join `customers` `c` on
476 `users`.`id` = `c`.`id`
477 left join `domains` `d` on
478 `d`.`customer_id` = `c`.`id`
479 where c.code = @customer_code) a
480 set p.total_domains=a.total_domains;
481
482 update panel_infos p,
483 (select count(email_accounts.id) as `total_emails8GB`
484 from `email_accounts`
485 join `domains` on
486 `email_accounts`.`domain_id` = `domains`.`id`
487 join `customers` on
488 `customers`.`id` = `domains`.`customer_id`
489 and `customers`.`code` = @customer_code
490 where `email_accounts`.`max_email_quota` <= 8589934592
491 and `email_accounts`.`name` != 'postmaster') a
492 set p.total_emails8GB = a.total_emails8GB;
493
494 update panel_infos p,
495 (select count(email_accounts.id) as `total_emails25GB`
496 from `email_accounts`
497 join `domains` on
498 `email_accounts`.`domain_id` = `domains`.`id`
499 join `customers` on
500 `customers`.`id` = `domains`.`customer_id`
501 and `customers`.`code` = @customer_code
502 where `email_accounts`.`max_email_quota` > 8589934592
503 and `email_accounts`.`name` != 'postmaster') a
504 set p.total_emails25GB = a.total_emails25GB;
505
506 update panel_infos p,
507 (select count(email_accounts.id) as `total_archives`
508 from `email_accounts`
509 join `domains` on
510 `email_accounts`.`domain_id` = `domains`.`id`
511 join `customers` on
512 `customers`.`id` = `domains`.`customer_id`
513 and `customers`.`code` = @customer_code
514 where `email_accounts`.`archive` = 1) a
515 set p.total_archives = a.total_archives;
516
517 update panel_infos p,
518 (
519 select sum(a.emails) as `billableEmails8GB`
520 from (
521 select count(email_accounts.id) as `emails`
522 from `email_accounts`
523 join `domains` on
524 `email_accounts`.`domain_id` = `domains`.`id`
525 join `customers` on
526 `customers`.`id` = `domains`.`customer_id`
527 and `customers`.`code` = @customer_code
528 where `email_accounts`.`max_email_quota` <= 8589934592
529 and `email_accounts`.`name` != 'postmaster'
530 and domains.id not in (select domain_id from v_postmasters)
531 union all
532 select count(email_accounts.id) as `emails`
533 from `email_accounts`
534 join `domains` on
535 `email_accounts`.`domain_id` = `domains`.`id`
536 join `customers` on
537 `customers`.`id` = `domains`.`customer_id`
538 and `customers`.`code` = @customer_code
539 where `email_accounts`.`max_email_quota` <= 8589934592
540 and `email_accounts`.`name` = 'postmaster'
541 and domains.id in (select domain_id from v_postmasters)
542 ) a
543 ) m
544 set p.billableEmails8GB = m.billableEmails8GB;
545
546 update panel_infos p,
547 (select total_emails25GB
548 from `panel_infos`) a
549 set p.billableEmails25GB = a.total_emails25GB;
550
551 select * from panel_infos;
552
553 elseif (left(code,
554 2) = 'MN') then
555 select (
556 select id
557 from `managers` `m`
558 where `m`.code = code
559 ) as ID,
560
561 ifnull((
562 select count(0)
563 from `domains`
564 where `domains`.`manager_id` = (
565 select id
566 from `managers` `m`
567 where `m`.code = code)
568 and `domains`.status = 'enabled'), 0) AS `enabled_domains`,
569 ifnull((
570 select count(0)
571 from `domains`
572 where `domains`.`manager_id` = (
573 select id
574 from `managers` `m`
575 where `m`.code = code)
576 and `domains`.status = 'disabled'), 0) AS `disabled_domains`,
577 ifnull((
578 select count(0)
579 from `email_accounts`
580 join `domains` on
581 `email_accounts`.`domain_id` = `domains`.`id`
582
583 join `customers` on
584 `customers`.`id` = `domains`.`customer_id`
585 where `domains`.`manager_id` = (
586 select id
587 from `managers` `m`
588 where `m`.code = code)
589 and `email_accounts`.`max_email_quota` <= 8589934592
590 and `email_accounts`.`status` = 'enabled'
591 and `email_accounts`.`name` != 'postmaster'), 0) AS `email8gEnabled`,
592 ifnull((
593 select count(0)
594 from `email_accounts`
595 join `domains` on
596 `email_accounts`.`domain_id` = `domains`.`id`
597
598 join `customers` on
599 `customers`.`id` = `domains`.`customer_id`
600 where `domains`.`manager_id` = (
601 select id
602 from `managers` `m`
603 where `m`.code = code)
604 and `email_accounts`.`max_email_quota` <= 8589934592
605 and `email_accounts`.`status` = 'disabled'
606 and `email_accounts`.`name` != 'postmaster'), 0) AS `email8gDisabled`,
607 ifnull((
608 select count(0)
609 from `email_accounts`
610 join `domains` on
611 `email_accounts`.`domain_id` = `domains`.`id`
612
613 join `customers` on
614 `customers`.`id` = `domains`.`customer_id`
615 where `domains`.`manager_id` = (
616 select id
617 from `managers` `m`
618 where `m`.code = code)
619 and `email_accounts`.`max_email_quota` > 8589934592
620 and `email_accounts`.`max_email_quota` <= 26843545600
621 and `email_accounts`.`status_detail` = 'ok'
622 and `email_accounts`.`name` != 'postmaster'), 0) AS `email25g`,
623 ifnull((
624 select count(0)
625 from `email_accounts`
626 join `domains` on
627 `email_accounts`.`domain_id` = `domains`.`id`
628
629 join `customers` on
630 `customers`.`id` = `domains`.`customer_id`
631 where `domains`.`manager_id` = (
632 select id
633 from `managers` `m`
634 where `m`.code = code)
635 and `email_accounts`.`max_email_quota` > 8589934592
636 and `email_accounts`.`max_email_quota` <= 26843545600
637 and `email_accounts`.`status` = 'disabled'
638 and `email_accounts`.`name` != 'postmaster'), 0) AS `email25gDisabled`,
639 ifnull((
640 select count(0)
641 from `email_accounts`
642 join `domains` on
643 `email_accounts`.`domain_id` = `domains`.`id`
644
645 join `customers` on
646 `customers`.`id` = `domains`.`customer_id`
647 where `domains`.`manager_id` = (
648 select id
649 from `managers` `m`
650 where `m`.code = code)
651 and `email_accounts`.`max_email_quota` > 26843545600
652 and `email_accounts`.`status_detail` = 'ok'
653 and `email_accounts`.`name` != 'postmaster'), 0) AS `email50g`,
654 ifnull((
655 select count(0)
656 from `email_accounts`
657 join `domains` on
658 `email_accounts`.`domain_id` = `domains`.`id`
659
660 join `customers` on
661 `customers`.`id` = `domains`.`customer_id`
662 where `domains`.`manager_id` = (
663 select id
664 from `managers` `m`
665 where `m`.code = code)
666 and `email_accounts`.`max_email_quota` > 26843545600
667 and `email_accounts`.`status` = 'disabled'
668 and `email_accounts`.`name` != 'postmaster'), 0) AS `email50gDisabled`,
669 ifnull((
670 select count(0)
671 from `email_accounts`
672 join `domains` on
673 `email_accounts`.`domain_id` = `domains`.`id`
674
675 join `customers` on
676 `customers`.`id` = `domains`.`customer_id`
677 where `domains`.`manager_id` = (
678 select id
679 from `managers` `m`
680 where `m`.code = code)
681 and `email_accounts`.archive_status = 'enabled'), 0) AS `archive_enabled`,
682 ifnull((
683 select count(0)
684 from `email_accounts`
685 join `domains` on
686 `email_accounts`.`domain_id` = `domains`.`id`
687
688 join `customers` on
689 `customers`.`id` = `domains`.`customer_id`
690 where `domains`.`manager_id` = (
691 select id
692 from `managers` `m`
693 where `m`.code = code)
694 and `email_accounts`.archive_status = 'disabled'), 0) AS `archive_disabled`,
695 ifnull(`dom`.`postmaster_domain_enabled`,
696 0) AS `postmaster_domain_enabled`,
697 ifnull(`dom`.`postmaster_domain_disabled`,
698 0) AS `postmaster_domain_disabled`,
699 ifnull((
700 select count(0)
701 from `alias_domains`
702 join `domains` on
703 `alias_domains`.`domain_id` = `domains`.`id`
704
705 join `customers` on
706 `customers`.`id` = `domains`.`customer_id`
707 where `domains`.`manager_id` = (
708 select id
709 from `managers` `m`
710 where `m`.code = code)), 0) AS `alias_domains`,
711 ifnull((
712 select count(0)
713 from `alias_email_accounts`
714 join `domains` on
715 `alias_email_accounts`.`domain_id` = `domains`.`id`
716
717 join `customers` on
718 `customers`.`id` = `domains`.`customer_id`
719 where `domains`.`manager_id` = (
720 select id
721 from `managers` `m`
722 where `m`.code = code)), 0) AS `alias_emails`,
723 0 AS `managers`,
724 0 AS `team_members`,
725 ifnull((
726 select count(0)
727 from `email_accounts`
728 join `domains` on
729 `email_accounts`.`domain_id` = `domains`.`id`
730
731 join `customers` on
732 `customers`.`id` = `domains`.`customer_id`
733 where `domains`.`manager_id` = (
734 select id
735 from `managers` `m`
736 where `m`.code = code)
737 and `email_accounts`.quota_level in (1,
738 2)), 0) AS `mail_overquota`,
739 ifnull((
740 select count(0)
741 from `domains`
742 where `domains`.`manager_id` = (
743 select id
744 from `managers` `m`
745 where `m`.code = code)
746 ), 0) AS total_domains,
747
748 ifnull((
749 select count(0)
750 from `email_accounts`
751 join `domains` on
752 `email_accounts`.`domain_id` = `domains`.`id`
753
754 join `customers` on
755 `customers`.`id` = `domains`.`customer_id`
756 where `domains`.`manager_id` = (
757 select id
758 from `managers` `m`
759 where `m`.code = code)
760 and `email_accounts`.`max_email_quota` <= 8589934592
761 and `email_accounts`.`name` != 'postmaster'), 0) AS `total_emails8GB`,
762
763 ifnull((
764 select count(0)
765 from `email_accounts`
766 join `domains` on
767 `email_accounts`.`domain_id` = `domains`.`id`
768
769 join `customers` on
770 `customers`.`id` = `domains`.`customer_id`
771 where `domains`.`manager_id` = (
772 select id
773 from `managers` `m`
774 where `m`.code = code)
775 and `email_accounts`.`max_email_quota` > 8589934592
776 and `email_accounts`.`max_email_quota` <= 26843545600
777 and `email_accounts`.`name` != 'postmaster'), 0) AS `total_emails25GB`,
778
779 ifnull((
780 select count(0)
781 from `email_accounts`
782 join `domains` on
783 `email_accounts`.`domain_id` = `domains`.`id`
784
785 join `customers` on
786 `customers`.`id` = `domains`.`customer_id`
787 where `domains`.`manager_id` = (
788 select id
789 from `managers` `m`
790 where `m`.code = code)
791 and `email_accounts`.`max_email_quota` > 26843545600
792 and `email_accounts`.`name` != 'postmaster'), 0) AS `total_emails50GB`,
793
794 ifnull((
795 select count(0)
796 from `email_accounts`
797 join `domains` on
798 `email_accounts`.`domain_id` = `domains`.`id`
799
800 join `customers` on
801 `customers`.`id` = `domains`.`customer_id`
802 where `domains`.`manager_id` = (
803 select id
804 from `managers` `m`
805 where `m`.code = code)
806 and `email_accounts`.archive = 1), 0) AS `total_archives`,
807
808
809 ifnull((select sum(a.emails)
810 from (
811 select count(email_accounts.id) as `emails`
812 from `email_accounts`
813 join `domains` on
814 `email_accounts`.`domain_id` = `domains`.`id`
815 where `email_accounts`.`max_email_quota` <= 8589934592
816 and `email_accounts`.`name` != 'postmaster'
817 and `domains`.`manager_id` = (
818 select id
819 from `managers` `m`
820 where `m`.code = code)
821 and domains.id not in (select domain_id from v_postmasters)
822 union all
823 select count(email_accounts.id) as `emails`
824 from `email_accounts`
825 join `domains` on
826 `email_accounts`.`domain_id` = `domains`.`id`
827
828 where `email_accounts`.`max_email_quota` <= 8589934592
829 and `email_accounts`.`name` = 'postmaster'
830 and `domains`.`manager_id` = (
831 select id
832 from `managers` `m`
833 where `m`.code = code)
834 and domains.id in (select domain_id from v_postmasters)
835 ) a), 0) as billableEmails8GB,
836
837 ifnull((
838 select count(0)
839 from `email_accounts`
840 join `domains` on
841 `email_accounts`.`domain_id` = `domains`.`id`
842 where `domains`.`manager_id` = (
843 select id
844 from `managers` `m`
845 where `m`.code = code)
846 and `email_accounts`.`max_email_quota` > 8589934592
847 and `email_accounts`.`name` != 'postmaster'), 0) AS `billableEmails25GB`
848
849 from `users`
850 join `customers` `c` on
851 `users`.`id` = `c`.`id`
852 left join (
853 select `customers`.`id` as `customer_id`,
854 sum(
855 case when `qbpanel`.`email_accounts`.`status` = 'enabled' then 1 else 0 end
856 ) AS `postmaster_domain_enabled`,
857 sum(
858 case when `qbpanel`.`email_accounts`.`status` = 'disabled' then 1 else 0 end
859 ) AS `postmaster_domain_disabled`
860 from `qbpanel`.`email_accounts`
861 join `qbpanel`.`domains` on `qbpanel`.`email_accounts`.`domain_id` = `qbpanel`.`domains`.`id`
862 and `qbpanel`.`domains`.`manager_id` in (
863 select id
864 from `managers` `m`
865 where `m`.code = code)
866 and `qbpanel`.`domains`.`id` in (
867 select `qbpanel`.`email_accounts`.`domain_id`
868 from `qbpanel`.`email_accounts`
869 group by `qbpanel`.`email_accounts`.`domain_id`
870 having count(`qbpanel`.`email_accounts`.`id`) = 1
871 )
872 join `customers` on
873 `customers`.`id` = `domains`.`customer_id`
874 group by `customers`.`id`
875 ) `dom` on
876 `c`.`id` = `dom`.`customer_id`
877 where c.id = (select customer_id
878 from `managers` `m`
879 where `m`.code = code)
880 group by `users`.`id`;
881 elseif (left(code,
882 1) = 'P') then
883 select `users`.`id` as ID,
884 ifnull(sum(case when d.status = 'enabled' then 1 else 0 end), 0) AS `enabled_domains`,
885 ifnull(sum(case when d.status = 'disabled' then 1 else 0 end), 0) AS `disabled_domains`,
886 ifnull((
887 select count(0)
888 from ((`email_accounts`
889 join `domains` on
890 ((`email_accounts`.`domain_id` = `domains`.`id`)))
891 join `customers` on
892 ((`customers`.`id` = `domains`.`customer_id`)))
893 where ((`email_accounts`.`domain_id` = `d`.`id`)
894 and (`email_accounts`.`max_email_quota` <= 8589934592)
895 and (`email_accounts`.`status` = 'enabled')
896 and (`email_accounts`.`name` != 'postmaster'))), 0) AS `email8gEnabled`,
897 ifnull((
898 select count(0)
899 from ((`email_accounts`
900 join `domains` on
901 ((`email_accounts`.`domain_id` = `domains`.`id`)))
902 join `customers` on
903 ((`customers`.`id` = `domains`.`customer_id`)))
904 where ((`email_accounts`.`domain_id` = `d`.`id`)
905 and (`email_accounts`.`max_email_quota` <= 8589934592)
906 and (`email_accounts`.`status` = 'disabled')
907 and (`email_accounts`.`name` != 'postmaster'))), 0) AS `email8gDisabled`,
908 ifnull((
909 select count(0)
910 from ((`email_accounts`
911 join `domains` on
912 ((`email_accounts`.`domain_id` = `domains`.`id`)))
913 join `customers` on
914 ((`customers`.`id` = `domains`.`customer_id`)))
915 where ((`email_accounts`.`domain_id` = `d`.`id`)
916 and (`email_accounts`.`max_email_quota` > 8589934592)
917 and (`email_accounts`.`status_detail` = 'ok')
918 and (`email_accounts`.`name` != 'postmaster'))), 0) AS `email25g`,
919 ifnull((
920 select count(0)
921 from ((`email_accounts`
922 join `domains` on
923 ((`email_accounts`.`domain_id` = `domains`.`id`)))
924 join `customers` on
925 ((`customers`.`id` = `domains`.`customer_id`)))
926 where ((`email_accounts`.`domain_id` = `d`.`id`)
927 and (`email_accounts`.`max_email_quota` > 8589934592)
928 and (`email_accounts`.`status` = 'disabled')
929 and (`email_accounts`.`name` != 'postmaster'))), 0) AS `email25gDisabled`,
930
931 ifnull((
932 select count(0)
933 from `email_accounts`
934 join `domains` on
935 `email_accounts`.`domain_id` = `domains`.`id`
936 join `customers` on
937 `customers`.`id` = `domains`.`customer_id`
938 where `email_accounts`.`domain_id` = `d`.`id` and `email_accounts`.archive_status = 'enabled'
939 ), 0) AS `archive_enabled`,
940 ifnull((
941 select count(0)
942 from `email_accounts`
943 join `domains` on
944 `email_accounts`.`domain_id` = `domains`.`id`
945 join `customers` on
946 `customers`.`id` = `domains`.`customer_id`
947 where `email_accounts`.`domain_id` = `d`.`id` and `email_accounts`.archive_status = 'disabled'
948 ), 0) AS `archive_disabled`,
949 ifnull(`dom`.`postmaster_domain_enabled`,
950 0) AS `postmaster_domain_enabled`,
951 ifnull(`dom`.`postmaster_domain_disabled`,
952 0) AS `postmaster_domain_disabled`,
953 ifnull((
954 select count(0)
955 from `alias_domains`
956 join `domains` on
957 `alias_domains`.`domain_id` = `domains`.`id`
958 join `customers` on
959 `customers`.`id` = `domains`.`customer_id`
960 where `domains`.`id` = `d`.`id`
961 ), 0) AS `alias_domains`,
962
963 ifnull((
964 select count(0)
965 from `alias_email_accounts`
966 join `domains` on
967 `alias_email_accounts`.`domain_id` = `domains`.`id`
968 join `customers` on
969 `customers`.`id` = `domains`.`customer_id`
970 where `domains`.`id` = `d`.`id`
971 ), 0) AS `alias_emails`,
972
973 0 AS `managers`,
974 0 AS `team_members`,
975
976 ifnull((
977 select count(0)
978 from `email_accounts`
979 join `domains` on
980 `email_accounts`.`domain_id` = `domains`.`id`
981 join `customers` on
982 `customers`.`id` = `domains`.`customer_id`
983 where `email_accounts`.`domain_id` = `d`.`id` and `email_accounts`.quota_level in (1, 2)
984 ), 0) AS `mail_overquota`,
985
986 ifnull((
987 select count(0)
988 from `domains`
989 join `email_accounts` `ea` on `domains`.`id` = `ea`.`domain_id`
990 where `ea`.`code` = code
991 ), 0) AS total_domains,
992
993 ifnull((
994 select count(0)
995 from `email_accounts`
996 join `domains` on
997 `email_accounts`.`domain_id` = `domains`.`id`
998 where `email_accounts`.`code` = code
999 and (`email_accounts`.`max_email_quota` <= 8589934592)
1000 and (`email_accounts`.`status` = 'enabled')
1001 and (`email_accounts`.`name` != 'postmaster')), 0) as total_emails8GB,
1002
1003 ifnull((
1004 select count(0)
1005 from `email_accounts`
1006 join `domains` on
1007 `email_accounts`.`domain_id` = `domains`.`id`
1008 where `email_accounts`.`code` = code
1009 and (`email_accounts`.`max_email_quota` > 8589934592)
1010 and (`email_accounts`.`status` = 'enabled')
1011 and (`email_accounts`.`name` != 'postmaster')), 0) as total_emails25GB,
1012
1013 ifnull((
1014 select count(0)
1015 from `email_accounts`
1016 join `domains` on
1017 `email_accounts`.`domain_id` = `domains`.`id`
1018 where `email_accounts`.`code` = code and email_accounts.archive = 1
1019 ), 0) AS `total_archives`,
1020
1021 ifnull((select sum(a.emails)
1022 from (
1023 select count(email_accounts.id) as `emails`
1024 from `email_accounts`
1025 join `domains` on
1026 `email_accounts`.`domain_id` = `domains`.`id`
1027 where `email_accounts`.`max_email_quota` <= 8589934592
1028 and `email_accounts`.`name` != 'postmaster'
1029 and `email_accounts`.`code` = code
1030 and domains.id not in (select domain_id from v_postmasters)
1031 union all
1032 select count(email_accounts.id) as `emails`
1033 from `email_accounts`
1034 join `domains` on
1035 `email_accounts`.`domain_id` = `domains`.`id`
1036 where `email_accounts`.`max_email_quota` <= 8589934592
1037 and `email_accounts`.`name` = 'postmaster'
1038 and `email_accounts`.`code` = code
1039 and domains.id in (select domain_id from v_postmasters)
1040 ) a), 0) as billableEmails8GB,
1041
1042 ifnull((
1043 select count(0)
1044 from `email_accounts`
1045 join `domains` on
1046 `email_accounts`.`domain_id` = `domains`.`id`
1047 where `email_accounts`.`code` = code
1048 and (`email_accounts`.`max_email_quota` > 8589934592)
1049 and (`email_accounts`.`status` = 'enabled')
1050 and (`email_accounts`.`name` != 'postmaster')), 0) as billableEmails25GB
1051
1052 FROM `users`
1053 join `customers` `c` on
1054 `users`.`id` = `c`.`id`
1055 join `domains` `d` on
1056 `d`.`customer_id` = `c`.`id`
1057 and `d`.`status` in ('enabled', 'disabled')
1058 join `email_accounts` `ea` on `d`.`id` = `ea`.`domain_id`
1059 left join (
1060 select `customers`.`id` as `customer_id`,
1061 sum(
1062 case when `qbpanel`.`email_accounts`.`status` = 'enabled' then 1 else 0 end
1063 ) AS `postmaster_domain_enabled`,
1064 sum(
1065 case when `qbpanel`.`email_accounts`.`status` = 'disabled' then 1 else 0 end
1066 ) AS `postmaster_domain_disabled`
1067 from `qbpanel`.`email_accounts`
1068 join `qbpanel`.`domains` on `qbpanel`.`email_accounts`.`domain_id` = `qbpanel`.`domains`.`id`
1069 and `qbpanel`.`domains`.`id` in (
1070 select `qbpanel`.`email_accounts`.`domain_id`
1071 from `qbpanel`.`email_accounts`
1072 group by `qbpanel`.`email_accounts`.`domain_id`
1073 having count(`qbpanel`.`email_accounts`.`id`) = 1
1074 )
1075 join `customers` on
1076 `customers`.`id` = `domains`.`customer_id`
1077 group by `customers`.`id`
1078 ) `dom` on
1079 `c`.`id` = `dom`.`customer_id`
1080 where `ea`.`code` = code
1081 group by `users`.`id`;
1082 else
1083 select ifnull(
1084 sum(case when d.status = 'enabled' then 1 else 0 end),
1085 0
1086 ) AS `enabled_domains`,
1087 ifnull(
1088 sum(
1089 case when d.status = 'disabled' then 1 else 0 end
1090 ),
1091 0
1092 ) AS `disabled_domains`,
1093 ifnull(
1094 (
1095 select count(0)
1096 from `customers`
1097 where `status` != 'disabled'
1098 ),
1099 0
1100 ) AS `enabled_customers`,
1101 ifnull(
1102 (
1103 select count(0)
1104 from `customers`
1105 where `status` = 'disabled'
1106 ),
1107 0
1108 ) AS `disabled_customers`,
1109 ifnull(
1110 (
1111 select count(0)
1112 from `email_accounts`
1113 where `max_email_quota` <= 8589934592
1114 and `status` = 'enabled'
1115 and `name` != 'postmaster'
1116 ),
1117 0
1118 ) AS `email8gEnabled`,
1119 ifnull(
1120 (
1121 select count(0) as n
1122 from `email_accounts`
1123 where `max_email_quota` <= 8589934592
1124 and `status` = 'disabled'
1125 and `name` != 'postmaster'
1126 ),
1127 0
1128 ) AS `email8gDisabled`,
1129 ifnull(
1130 (
1131 select count(0) as n
1132 from `email_accounts`
1133 where `max_email_quota` > 8589934592
1134 and `status_detail` = 'ok'
1135 and `name` != 'postmaster'
1136 ),
1137 0
1138 ) AS `email25g`,
1139 ifnull(
1140 (
1141 select count(0)
1142 from `email_accounts`
1143 where `max_email_quota` > 8589934592
1144 and `status` = 'disabled'
1145 and `name` != 'postmaster'
1146 ),
1147 0
1148 ) AS `email25gDisabled`,
1149 ifnull(
1150 (
1151 select count(0)
1152 from `email_accounts`
1153 where archive_status = 'enabled'
1154 ),
1155 0
1156 ) AS `archive_enabled`,
1157 ifnull(
1158 (
1159 select count(0)
1160 from `email_accounts`
1161 where archive_status = 'disabled'
1162 ),
1163 0
1164 ) AS `archive_disabled`,
1165 ifnull(
1166 (
1167 select count(0)
1168 from `email_accounts`
1169 join `v_postmasters`
1170 on `email_accounts`.`domain_id` = `v_postmasters`.`domain_id` where `status` = 'enabled'
1171 ),
1172 0
1173 ) AS `postmaster_domain_enabled`,
1174 ifnull(
1175 (
1176 select count(0)
1177 from `email_accounts`
1178 join `v_postmasters`
1179 on `email_accounts`.`domain_id` = `v_postmasters`.`domain_id` where `status` = 'disabled'
1180 ),
1181 0
1182 ) AS `postmaster_domain_disabled`,
1183 ifnull(
1184 (
1185 select count(0) as n
1186 from `alias_domains`
1187 ),
1188 0
1189 ) AS `alias_domains`,
1190 ifnull(
1191 (
1192 select count(0) as n
1193 from `alias_email_accounts`
1194 ),
1195 0
1196 ) AS `alias_emails`,
1197 ifnull(
1198 (
1199 select count(0) as n
1200 from `managers`
1201 ),
1202 0
1203 ) AS `managers`,
1204 ifnull(
1205 (
1206 select count(0) as n
1207 from `team_members`
1208 ),
1209 0
1210 ) AS `team_members`,
1211 ifnull(
1212 (
1213 select count(0) as n
1214 from `email_accounts`
1215 where `email_accounts`.quota_level in (1, 2)
1216 ),
1217 0
1218 ) AS `mail_overquota`,
1219
1220 ifnull((
1221 select count(0)
1222 from `domains`
1223 ), 0) AS total_domains,
1224
1225
1226 ifnull(
1227 (
1228 select count(0)
1229 from `email_accounts`
1230 where `max_email_quota` <= 8589934592
1231 and `name` != 'postmaster'
1232 ),
1233 0
1234 ) AS total_emails8GB,
1235 ifnull(
1236 (
1237 select count(0)
1238 from `email_accounts`
1239 where `max_email_quota` > 8589934592
1240 and `name` != 'postmaster'
1241 ),
1242 0
1243 ) as total_emails25GB,
1244
1245 ifnull(
1246 (
1247 select count(0)
1248 from `email_accounts`
1249 where archive = 1
1250 ),
1251 0
1252 ) AS total_archives,
1253
1254 ifnull((select sum(a.emails)
1255 from (
1256 select count(email_accounts.id) as `emails`
1257 from `email_accounts`
1258 join `domains` on
1259 `email_accounts`.`domain_id` = `domains`.`id`
1260 where `email_accounts`.`max_email_quota` <= 8589934592
1261 and `email_accounts`.`name` != 'postmaster'
1262 and domains.id not in (select domain_id from v_postmasters)
1263 union all
1264 select count(email_accounts.id) as `emails`
1265 from `email_accounts`
1266 join `domains` on
1267 `email_accounts`.`domain_id` = `domains`.`id`
1268 where `email_accounts`.`max_email_quota` <= 8589934592
1269 and `email_accounts`.`name` = 'postmaster'
1270 and domains.id in (select domain_id from v_postmasters)
1271 ) a), 0) as billableEmails8GB,
1272 ifnull(
1273 (
1274 select count(0)
1275 from `email_accounts`
1276 where `max_email_quota` > 8589934592
1277 and `name` != 'postmaster'
1278 ),
1279 0
1280 ) as billableEmails25GB
1281
1282
1283 from `users`
1284 join `customers` `c` on `users`.`id` = `c`.`id`
1285 left join `domains` `d` on `d`.`customer_id` = `c`.`id`
1286 and `d`.`status` in ('enabled', 'disabled');
1287 end if;
1288
1289END;
1290@@