Display number of emails pending in each account?

[BenevolentDeity]

Hello,

I just installed hMailServer and got it working. Is there any way to display the number of emails available for reading in each account? I was previously using CMailServer, which displayed a GUI table with an entry for each account that indicated (among other things) the account name, the user name, and the number of emails available for reading. That table was handy for getting a general idea of what was going on.

Thanks!

[johang]

Hello,

I just installed hMailServer and got it working. Is there any way to display the number of emails available for reading in each account? I was previously using CMailServer, which displayed a GUI table with an entry for each account that indicated (among other things) the account name, the user name, and the number of emails available for reading. That table was handy for getting a general idea of what was going on.

Thanks!

it is not presented in the gui.

you can just go into your hmailserver Data folder where all domains reside, and via file explorer right click on a user choosing "properties" and read how many files a user has in their dir..
shouldnt be to hard to bat script something to put up as a report for a script knowledgeable person, but if you want it live to see if someones inbox is filling up in realtime someone have to do a good hack i guess

[palinka]

Is there any way to display the number of emails available for reading in each account?

Do you mean unread mail?

[jimimaseye]

There are these that you might find useful starters:

https://www.hmailserver.com/forum/viewt ... 20&t=28052

https://www.hmailserver.com/forum/viewt ... 20&t=35156

A bit of tailoring to look at message flags and you can get filter it to how you like.

[Entered by mobile. Excuse my spelling.]

[mattg]

I think perhaps the OP is talking about POP3 accounts, not IMAP

[palinka]

I think perhaps the OP is talking about POP3 accounts, not IMAP

I guess IMAP Inbox unread is more or less equivilant to POP not-downloaded messages, correct?

[palinka]

This works. It takes a long time to loop through every message. I think on any but the smallest servers, it will run into execution timeouts. But it does work

Is there a way to count unread messages instead of looping through them?

Counts unread messages for every active user.

PHP:

Code: Select all

<?php

	$hMSAdminPass = "supersecretpassword";
	$hMS = new COM("hMailServer.Application");
	$hMS->Authenticate("Administrator", $hMSAdminPass);

	echo "Unread Messages <br><br>";
	echo "<table>";
	
	for ($i = 0; $i < ($hMS->Domains->Count); $i++) {
		$hMSDomain = $hMS->Domains->Item($i);
		if ($hMSDomain->Active){
			for ($j = 0; $j < ($hMSDomain->Accounts->Count); $j++) {
				$hMSAccount = $hMSDomain->Accounts->Item($j);
				if ($hMSAccount->Active) {
					echo "<tr><td>".$hMSAccount->Address."</td>";
					$l = 0;
					for ($m = 0; $m < ($hMSAccount->IMAPFolders->ItemByName('INBOX')->Messages->Count); $m++) {
						$hMSMessage = $hMSAccount->IMAPFolders->ItemByName('INBOX')->Messages->Item($m);
						If ($hMSMessage->Flag(1)) {$l++;}
					}
					echo "<td>".($hMSAccount->IMAPFolders->ItemByName('INBOX')->Messages->Count - $l)."</td></tr>";
				}
			}
		}
	}

	echo "</table>";

?>

Probably better to do this by some other script and just have the results emailed. I did it in PHP because I thought it would be refresh, bang, results.

Edit - many thanks to Jimi's email cleardown script for help with the COM stuff.

[johang]

.
i think the cmailserver interface spoken of is this ( i might be wrong )

[palinka]

.
i think the cmailserver interface spoken of is this ( i might be wrong )

c < h



What is the flag for unread? 0 or null?

[palinka]

Just toying around now. Here's one to display message count for every folder.

I assume this would work for POP just as easily, although IMAP must be enabled (I think) in order to get the counts. I don't see POP anywhere in the COM documentation.

This counts the messages in each folder for each account. If the userbase were using POP exclusively, then only the INBOX would show up. The output is like below:

Code: Select all

battery@mydomain.tld     INBOX              0
                         Sent               0
                         Drafts             0
                         Spam               0
                         Trash              0
                         Archive            0
                         TOTAL MESSAGES:    0

brother-mfc@mydomain.tld INBOX              0
                         TOTAL MESSAGES:    0

actualuser@mydomain.tld	 INBOX           2450
                         Trash              3
                         Sent             855
                         Drafts             0
                         Archive            0
                         Spam               1
                         Bank              41
                         Dingbat           41
                         Junk E-mail        3
                         TOTAL MESSAGES: 3394

The first two are accounts for automated messages. The second one only has an INBOX because no one has ever logged in. Its just for sending. I assume this is how a POP only account would appear.

The first account is also an automated sending account, but one time I logged in via webmail and the webmail client created the folders automatically. Otherwise, there would only be an INBOX.

The third account is an actual user with user-created folders.

This one is very fast because it doesn't loop through each message.

PHP:

Code: Select all

<?php

	$hMSAdminPass = "supersecretpassword";
	$hMS = new COM("hMailServer.Application");
	$hMS->Authenticate("Administrator", $hMSAdminPass);

	echo "All Messages <br><br>";
	echo "<table>";
	
	for ($i = 0; $i < ($hMS->Domains->Count); $i++) {
		$hMSDomain = $hMS->Domains->Item($i);
		if ($hMSDomain->Active){
			for ($j = 0; $j < ($hMSDomain->Accounts->Count); $j++) {
				$hMSAccount = $hMSDomain->Accounts->Item($j);
				if ($hMSAccount->Active) {
					$totalCount = 0;
					for ($k = 0; $k < ($hMSAccount->IMAPFolders->Count); $k++) {
						$hMSIMAPFolder = $hMSAccount->IMAPFolders->Item($k);
						$totalCount += $hMSIMAPFolder->Messages->Count;
						if ($hMSIMAPFolder->Name == "INBOX"){
							echo "<tr><td>".$hMSAccount->Address."</td><td>".$hMSIMAPFolder->Name."</td><td style='text-align:right;'>".number_format($hMSIMAPFolder->Messages->Count)."</td></tr>";
						} else {
							echo "<tr><td></td><td>".$hMSIMAPFolder->Name."</td><td style='text-align:right;'>".number_format($hMSIMAPFolder->Messages->Count)."</td></tr>";
						}
					}
					echo "<tr style='font-weight:bold;'><td></td><td>TOTAL MESSAGES:</td><td style='text-align:right;'>".number_format($totalCount)."</td></tr>";
					echo "<tr><td colspan='3'><hr></td></td></tr>";
				}
			}
		}
	}

	echo "</table>";

?>

I like PHP because you just pull it up on a browser for real time information.

[SorenR]

Code: Select all

SELECT COUNT(hm_messages.messageid)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';

You are welccome...

[palinka]

Code: Select all

SELECT COUNT(hm_messages.messageid)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';

You are welccome...

Beeyooteefool! I'll push out an update tonight.

[SorenR]

Code: Select all

SELECT COUNT(hm_messages.messageid)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';

You are welccome...

Beeyooteefool! I'll push out an update tonight.

I'm tired in my head and soon off to bed so I won't add any more to this but (!) you should be able to create a query that will list all accounts in a domain and their number of unread messages in one go ...

[palinka]

Code: Select all

SELECT COUNT(hm_messages.messageid)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';

You are welccome...

Beeyooteefool! I'll push out an update tonight.

I'm tired in my head and soon off to bed so I won't add any more to this but (!) you should be able to create a query that will list all accounts in a domain and their number of unread messages in one go ...

That's probably true, but it was way easier to recycle! I recycle everything.

Also, the COM script cycles through all accounts on all domains, so you get a result for every ACTIVE account in one simple page.

Muuuuuuuccchhhhhhh FASTER than before... Thank ye kindly.

PHP:

Code: Select all

<?php

	/*	Database Variables 

		'dbtype' = database server type
		
			For MySQL use dbtype = 'mysql'
			For MSSQL use dbtype = 'mssql'

		'driver' = connection type
		
			For MySQL use driver = 'mysql'
			For MSSQL use driver = 'mssql'
			For ODBC  use driver = 'odbc'
			
			* When opting for ODBC use correct DSN! *
			* Example: "MariaDB ODBC 3.0 Driver"    *
			* Exact spelling is critical!           *
		
	*/

	$Database = array (
		'dbtype'      => 'mysql',
		'host'        => 'localhost',
		'username'    => 'hmailserver',
		'password'    => 'supersecretpassword',
		'dbname'      => 'hmailserver',
		'driver'      => 'mysql',
		'port'        => '3306',
		'dsn'         => 'MariaDB ODBC 3.0 Driver'
	);


	/*  hMailServer COM Authentication 
		Password for hMailServer "Administrator"
	*/

	$hMSAdminPass = "supersecretpassword";

	If ($Database['driver'] == 'mysql') {
		$pdo = new PDO("mysql:host=".$Database['host'].";port=".$Database['port'].";dbname=".$Database['dbname'], $Database['username'], $Database['password']);
	} ElseIf ($Database['driver'] == 'mssql') {
		$pdo = new PDO("sqlsrv:Server=".$Database['host'].",".$Database['port'].";Database=".$Database['dbname'], $Database['username'], $Database['password']);
	} ElseIf ($Database['driver'] == 'odbc') {
		$pdo = new PDO("odbc:Driver={".$Database['dsn']."};Server=".$Database['host'].";Port=".$Database['port'].";Database=".$Database['dbname'].";User=".$Database['username'].";Password=".$Database['password'].";");
	} Else {
		echo "Configuration Error - No database driver specified";
	}


	$hMS = new COM("hMailServer.Application");
	$hMS->Authenticate("Administrator", $hMSAdminPass);

	echo "Unread Messages <br><br>";
	echo "<table>";
	
	for ($i = 0; $i < ($hMS->Domains->Count); $i++) {
		$hMSDomain = $hMS->Domains->Item($i);
		if ($hMSDomain->Active){
			for ($j = 0; $j < ($hMSDomain->Accounts->Count); $j++) {
				$hMSAccount = $hMSDomain->Accounts->Item($j);
				if ($hMSAccount->Active) {
					echo "<tr><td>".$hMSAccount->Address."</td>";

					$sql = $pdo->prepare("					
						SELECT COUNT(hm_messages.messageid) AS unread
						FROM hm_messages
						INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
						WHERE hm_accounts.accountaddress = '".$hMSAccount->Address."' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';
					");
					$sql->execute();
					$unread = $sql->fetchColumn();
					
					echo "<td>".$unread."</td></tr>";
				}
			}
		}
	}

	echo "</table>";

?>

[SorenR]

Code: Select all

SELECT hm_accounts.accountaddress, COUNT(*)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress;

OK, it will ONLY list accounts with unread messages...

BUT ! You only need to run one SQL query so it should be faster

[palinka]

Code: Select all

SELECT hm_accounts.accountaddress, COUNT(*)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress;

OK, it will ONLY list accounts with unread messages...

BUT ! You only need to run one SQL query so it should be faster

Perfection. Anyway, what's the point of looking at accounts with NO unread messages if your goal is to look at accounts WITH unread messages?

This greatly simplifies things.

Code: Select all

<?php

	/*	Database Variables 

		'dbtype' = database server type
		
			For MySQL use dbtype = 'mysql'
			For MSSQL use dbtype = 'mssql'

		'driver' = connection type
		
			For MySQL use driver = 'mysql'
			For MSSQL use driver = 'mssql'
			For ODBC  use driver = 'odbc'
			
			* When opting for ODBC use correct DSN! *
			* Example: "MariaDB ODBC 3.0 Driver"    *
			* Exact spelling is critical!           *
	*/

	$Database = array (
		'dbtype'      => 'mysql',
		'host'        => 'localhost',
		'username'    => 'hmailserver',
		'password'    => 'supersecretpassword',
		'dbname'      => 'hmailserver',
		'driver'      => 'mysql',
		'port'        => '3306',
		'dsn'         => 'MariaDB ODBC 3.0 Driver'
	);


	If ($Database['driver'] == 'mysql') {
		$pdo = new PDO("mysql:host=".$Database['host'].";port=".$Database['port'].";dbname=".$Database['dbname'], $Database['username'], $Database['password']);
	} ElseIf ($Database['driver'] == 'mssql') {
		$pdo = new PDO("sqlsrv:Server=".$Database['host'].",".$Database['port'].";Database=".$Database['dbname'], $Database['username'], $Database['password']);
	} ElseIf ($Database['driver'] == 'odbc') {
		$pdo = new PDO("odbc:Driver={".$Database['dsn']."};Server=".$Database['host'].";Port=".$Database['port'].";Database=".$Database['dbname'].";User=".$Database['username'].";Password=".$Database['password'].";");
	} Else {
		echo "Configuration Error - No database driver specified";
	}

	echo "Unread Messages <br><br>";
	echo "<table>";
	
	$sql = $pdo->prepare("					
		SELECT hm_accounts.accountaddress AS address, COUNT(*) AS countunread
		FROM hm_messages
		INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
		WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
		GROUP BY hm_accounts.accountaddress;
	");
	$sql->execute();
	while($row = $sql->fetch(PDO::FETCH_ASSOC)){
		echo "<tr><td>".$row['address']."</td><td style='text-align:right;'>".number_format($row['countunread'])."</td></tr>";
	}
	
	echo "</table>";

?>

[palinka]

Found one minor flaw. Your all in one query outputs in alphabetical order by address, instead of by domain, then by address. Its not really a flaw, just a nitpick. Using the COM with your first query, and then using if unread > 0, puts them back into orderly fashion, without the no-unread accounts.

[tunis]

Found one minor flaw. Your all in one query outputs in alphabetical order by address, instead of by domain, then by address. Its not really a flaw, just a nitpick. Using the COM with your first query, and then using if unread > 0, puts them back into orderly fashion, without the no-unread accounts.

Code: Select all

SELECT hm_accounts.accountaddress,COUNT(*)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress
ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, "@", -1)

[palinka]

Code: Select all

SELECT hm_accounts.accountaddress,COUNT(*)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress
ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, "@", -1)

Haha! That's great, man! Crowd sourcing is awesome!

[palinka]

Code: Select all

SELECT hm_accounts.accountaddress,COUNT(*)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress
ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, "@", -1)

I made one small modification.

WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0' AND accountactive = '1'

So only active accounts are displayed. I think we can put a fork in this one!

Final Version: PHP (MySQL only, also enabled mobile viewport):

Code: Select all

<!DOCTYPE html> 
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body style="font-family:Verdana;">

<?php
	$Database = array (
		'host'        => 'localhost',
		'username'    => 'hmailserver',
		'password'    => 'supersecretpassword',
		'dbname'      => 'hmailserver',
		'port'        => '3306'
	);

	$pdo = new PDO("mysql:host=".$Database['host'].";port=".$Database['port'].";dbname=".$Database['dbname'], $Database['username'], $Database['password']);

	echo "Unread Messages <br><br>";
	echo "<table>";
	
	$sql = $pdo->prepare("					
		SELECT hm_accounts.accountaddress AS address, COUNT(*) AS countunread
		FROM hm_messages
		INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
		WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0' AND accountactive = '1'
		GROUP BY hm_accounts.accountaddress
		ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, '@', -1)
	");
	$sql->execute();
	while($row = $sql->fetch(PDO::FETCH_ASSOC)){
		echo "<tr><td>".$row['address']."</td><td style='text-align:right;'>".number_format($row['countunread'])."</td></tr>";
	}
	
	echo "</table>";
?>

</body>
</html>