Provides a HTML/Jquery/DataTables display of all H-Sphere email resources for a given accountid, call via https with script.php?accountid=123
Note. I only needed to handle full domains (childtype = 2) for this purpose (see the switch !=2 todo) line 43. <?php if(empty($REQUEST[‘account_id’])) { exit; }
class pg {
public function __construct() {
$this->pgcon = pg_connect("host=1.2.3.4 port=5432 dbname=hsphere user=custom password=yourpassword") or die('DB Connection ERROR: ' . pg_last_error());
}
public function q($_q) {
$_res = pg_query($this->pgcon,$_q);
return $_res;
}
public function srl($_q,$_index) {
$_res = $this->q($_q);
while($_row = pg_fetch_assoc($_res)) {
$_data[$_row[$_index]] = $_row;
}
return $_data;
}
public function srr($_q) {
$_res = $this->q($_q);
return pg_fetch_assoc($_res);
}
}
$_pg = new pg();
$_pcms = $_pg->srl("select * from parent_child where account_id='".pg_escape_string($_REQUEST['account_id'])."' and child_type='1000';","child_id");
if(!empty($_pcms)) {
foreach($_pcms as $_pcmsv) {
/* mail domain */
$_pcdom = $_pg->srr("select * from parent_child where account_id='".pg_escape_string($_REQUEST['account_id'])."' and child_id='".pg_escape_string($_pcmsv['child_id'])."';");
switch($_pcdom['parent_type']) {
case 2:
$_dom = $_pg->srr("select * from domains where id='".pg_escape_string($_pcdom['parent_id'])."';");
$_domain = $_dom['name'];
break;
default:
$_domain = "todo";
break;
}
$_acmultistat['mailservices'] += 1;
$_pcmd = $_pg->srl("select * from parent_child where account_id='".pg_escape_string($_REQUEST['account_id'])."' and parent_id='".pg_escape_string($_pcmsv['child_id'])."' and child_type='1001';","child_id");
if(!empty($_pcmd)) {
foreach($_pcmd as $_pcmdv) {
/* mailboxes */
$_pcmb = $_pg->srl("select * from parent_child where account_id='".pg_escape_string($_REQUEST['account_id'])."' and parent_id='".pg_escape_string($_pcmdv['child_id'])."' and child_type='1002';","child_id");
if(!empty($_pcmb)) { foreach($_pcmb as $_pcmbv) {
$_mbs = $_pg->srr("select * from mailboxes where id='".pg_escape_string($_pcmbv['child_id'])."';");
$_pcmbq = $_pg->srr("select * from parent_child where parent_id='".pg_escape_string($_pcmbv['child_id'])."' and child_type='1008';");
$_mbq = $_pg->srr("select * from quotas where id='".pg_escape_string($_pcmbq['child_id'])."';");
$_acmulti[$_domain]['mailbox'][$_mbs['full_email']] = array('email'=>$_mbs['full_email'],'quota_mb'=>$_mbq['size_mb'],'discard_incoming'=>$_mbs['discard_mail']);
$_acmulti[$_domain]['quota_mb_domain'] += $_mbq['size_mb'];
$_acmulti[$_domain]['stat_mailboxes'] += 1;
$_acmultistat['quota_mb'] += $_mbq['size_mb'];
$_acmultistat['mailboxes'] += 1;
} }
/* forwarders */
$_pcmf = $_pg->srl("select * from parent_child where account_id='".pg_escape_string($_REQUEST['account_id'])."' and parent_id='".pg_escape_string($_pcmdv['child_id'])."' and child_type='1004';","child_id");
if(!empty($_pcmf)) { foreach($_pcmf as $_pcmfv) {
$_mfs = $_pg->srr("select * from mail_forwards where id='".pg_escape_string($_pcmfv['child_id'])."';");
$_acmulti[$_domain]['forward'][$_mfs['email_local'].'@'.$_domain] = array('email'=>$_mfs['email_local'].'@'.$_domain,'email_foreign'=>$_mfs['email_foreign']);
$_acmulti[$_domain]['stat_forwarders'] += 1;
$_acmultistat['forwarders'] += 1;
} }
/* aliases */
$_pcma = $_pg->srl("select * from parent_child where account_id='".pg_escape_string($_REQUEST['account_id'])."' and parent_id='".pg_escape_string($_pcmdv['child_id'])."' and child_type='1006';","child_id");
if(!empty($_pcma)) { foreach($_pcma as $_pcmav) {
$_mas = $_pg->srr("select * from mail_aliases where id='".pg_escape_string($_pcmav['child_id'])."';");
$_acmulti[$_domain]['alias'][$_mas['email_local'].'@'.$_domain] = array('email'=>$_mas['email_local'].'@'.$_domain,'email_foreign'=>$_mas['email_foreign']);
$_acmulti[$_domain]['stat_aliases'] += 1;
$_acmultistat['aliases'] += 1;
} }
}
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="initial-scale=1.0, maximum-scale=2.0">
<title>Mail Summary</title>
<link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css">
<style type="text/css" class="init">
</style>
<script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript" language="javascript" src="//cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" language="javascript" class="init">
$(document).ready(function() {
$('#ac').DataTable({
filter:false,
paging:false,
ordering:false,
info:false,
columnDefs: [
{
orderData: [[ 1, 'asc' ], [ 2, 'asc' ]]
}
]
});
$('#domains').DataTable();
$('#mailboxes').DataTable({
columnDefs: [
{
orderData: [[ 1, 'asc' ], [ 2, 'asc' ]]
}
]
});
$('#forwarders').DataTable({
columnDefs: [
{
orderData: [[ 1, 'asc' ], [ 2, 'asc' ]]
}
]
});
$('#aliases').DataTable({
columnDefs: [
{
orderData: [[ 1, 'asc' ], [ 2, 'asc' ]]
}
]
});
} );
</script>
</head>
<body
<div class="container">
<h2>account summary (#<?=$_REQUEST['account_id']; ?>)</h2>
<table id="ac" border=1 width=100%>
<thead>
<tr>
<th width="20%">domains/mailservices</th>
<th width="20%">mailboxes</th>
<th width="20%">forwarders</th>
<th width="20%">aliases</th>
<th width="20%">total mb</th>
</tr>
</thead>
<tbody>
<tr>
<td align="center"><?=$_acmultistat['mailservices']; ?></td>
<td align="center"><?=$_acmultistat['mailboxes']; ?></td>
<td align="center"><?=$_acmultistat['forwarders']; ?></td>
<td align="center"><?=$_acmultistat['aliases']; ?></td>
<td align="center"><?=$_acmultistat['quota_mb']; ?></td>
</tr>
</table>
<br/>
<h2>domains summary (<?=$_acmultistat['mailservices']; ?>)</h2>
<table id="domains" border=1 width=100%>
<thead>
<tr>
<th>domain</th>
<th>mailboxes</th>
<th>forwarders</th>
<th>aliases</th>
<th>total mb</th>
</tr>
</thead>
<tbody>
<?php
foreach($_acmulti as $_domain => $_objects) {
?>
<tr>
<td><?=$_domain; ?></td>
<td align="center"><?=number_format($_objects['stat_mailboxes'],0); ?></td>
<td align="center"><?=number_format($_objects['stat_forwarders'],0); ?></td>
<td align="center"><?=number_format($_objects['stat_aliases'],0); ?></td>
<td align="center"><?=number_format($_objects['quota_mb_domain'],0); ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<br/>
<h2>mailboxes (<?=$_acmultistat['mailboxes']; ?>)</h2>
<table id="mailboxes" border=1 width=100%>
<thead>
<tr>
<th>domain</th>
<th>email</th>
<th>quota_mb</th>
<th>discard_incoming</th>
</tr>
</thead>
<tbody>
<?php
foreach($_acmulti as $_domain => $_objects) {
if(!empty($_objects['mailbox'])) {
foreach($_objects['mailbox'] as $_email => $_opts) {
?>
<tr>
<td><?=$_domain; ?></td>
<td><?=$_email; ?></td>
<td align="center"><?=number_format($_opts['quota_mb'],0); ?></td>
<td align="center"><?=number_format($_opts['discard_incoming'],0); ?></td>
</tr>
<?php
}
}
}
?>
</tbody>
</table>
<br/>
<h2>forwarders (<?=$_acmultistat['forwarders']; ?>)</h2>
<table id="forwarders" border=1 width=100%>
<thead>
<tr>
<th>domain</th>
<th>from</th>
<th>email_foreign</th>
</tr>
</thead>
<tbody>
<?php
foreach($_acmulti as $_domain => $_objects) {
if(!empty($_objects['forward'])) {
foreach($_objects['forward'] as $_email => $_opts) {
?>
<tr>
<td><?=$_domain; ?></td>
<td><?=$_email; ?></td>
<td><?=$_opts['email_foreign']; ?></td>
</tr>
<?php
}
}
}
?>
</tbody>
</table>
<br/>
<h2>aliases (<?=$_acmultistat['aliases']; ?>)</h2>
<table id="aliases" border=1 width=100%>
<thead>
<tr>
<th>domain</th>
<th>from</th>
<th>to</th>
</tr>
</thead>
<tbody>
<?php
foreach($_acmulti as $_domain => $_objects) {
if(!empty($_objects['alias'])) {
foreach($_objects['alias'] as $_email => $_opts) {
?>
<tr>
<td><?=$_domain; ?></td>
<td><?=$_email; ?></td>
<td><?=$_opts['email_foreign']; ?></td>
</tr>
<?php
}
}
}
?>
</tbody>
</table>
</div>
</body>
</html>