Here’s a little something I wrote at work today.
Basically we charge per e-mail sent out using PHPList.
Until now we have not been keeping track of who is sending out what and thus we haven’t been billing accordingly. I decided to fix that!
So below is a script that will be run at the beginning of every month.
It will go back to the previous month and grab how many e-mails were sent out from what databases and then e-mail it to our billing department so the client can be billed.
The only tricky part was the automatic date lookup. I hate dates…but I did some simple methods to get around the handicap and now it all works.
The first charge for last month is like $420.00 - and that was a slow month. So let’s say that every month gets $350 worth of e-mails…well that’s $4200 a year. I’d actually venture to say that the real number is more along $6000 a year. So woohoo for increased automated income.
Example Output:
This is an automated message.
Below is a list of all PHPList e-mails that went out for September
[5040] E-mails for Database: [ahhhmassage_db]
[24103] E-mails for Database: [clarkcountybar_newsletter]
[7978] E-mails for Database: [commacoffee_news]
[11665] E-mails for Database: [mckernan_newsletter]
See code below:
#!/usr/bin/php -q <?php /* PHPList Processed E-mail Counter * Author: Sterling Hamilton * Date: 10.08.2008 * * Use from the command line. * Example default use: # php PHPListCounter.php * -> This will count all processed e-mails in the current month for each database. */ $strMailAddress = "billing@exyst.com"; $strSubject = "Monthly PHPList Report"; $strMessage = ""; @$cnMain = mysql_connect("localhost", "root", "crUmethu2rUcretH"); if (!$cnMain) { die("\033[31mCould not connect\033[0m: ".mysql_error()."\n"); } $strQuery = "SHOW DATABASES"; $aryDatabases = mysql_query($strQuery); $strLastMonthEnd = mktime(0, 0, 0, date('m'), 0, date("Y")); $strLastMonthEnd = date('Y-m-d', $strLastMonthEnd); $aryTemp = explode(" ",$strLastMonthEnd); $aryDate = explode("-",$aryTemp[0]); if($aryDate[1] == 12) { $strLastMonthEnd = mktime(0, 0, 0, date("m"), 0, date("Y") - 1); $strLastMonthEnd = date('Y-m-d', $strLastMonthEnd); } $strLastMonthStart = mktime(0, 0, 0, date('m') - 1, 1, date("Y")); $strLastMonthStart = date('Y-m-d', $strLastMonthStart); $aryTemp = explode(" ",$strLastMonthStart); $aryDate = explode("-",$aryTemp[0]); if($aryDate[1] == 12) { $strLastMonthStart = mktime(0, 0, 0, date("m") - 1, 1, date("Y") - 1); $strLastMonthStart = date('Y-m-d', $strLastMonthStart); } $strQuery = "SELECT processed FROM `phplist_message` WHERE embargo BETWEEN '$strLastMonthStart' AND '$strLastMonthEnd'"; while ($rowDB = mysql_fetch_assoc($aryDatabases)) { $dbCurrent = mysql_select_db($rowDB["Database"],$cnMain); $intCounter = 0; $aryCount = mysql_query($strQuery); if($aryCount) { while ($rowCount = mysql_fetch_assoc($aryCount)) { $intCounter += ($rowCount["processed"] > 0) ? $rowCount["processed"]: 0; } if($intCounter > 0) { $strMessage .= "[$intCounter] E-mails for Database: [".$rowDB["Database"]."]\n"; } } } $strHeader = "From: Automated Script <esupport@exyst.com>\r"; $strMessage = "This is an automated message.\nBelow is a list of all PHPList e-mails that went out for ".date("F",mktime(0,0,0, date("m"), 0, date("Y")))."\n\n".$strMessage; mail($strMailAddress, $strSubject, $strMessage, $strHeader); mysql_close($cnMain); ?>