· 9 years ago · Jun 23, 2017, 09:32 AM
1<?php
2// This file will be executed from shell also. So, check if DOCUMENT_ROOT is set. If not, set it - Start
3if ($_SERVER['DOCUMENT_ROOT'] == '')
4 {
5 /*
6 IMPORTANT:
7 If this CRON script is executed in CLI mode, do not forget that PHP will try to connect your MySQL server
8 through /var/mysql/mysql.sock socket. The path of socket file can not be changed. If mysql.sock is in different
9 location on your server, simply create a symbolic link on /var/mysql/mysql.sock
10
11 This is a limitation of PHP and there's nothing to do. Sleep on it ;)
12 */
13 $_SERVER['DOCUMENT_ROOT'] = str_replace('includes/cli/include_csv_acct.php', '', __FILE__);
14 }
15// This file will be executed from shell also. So, check if DOCUMENT_ROOT is set. If not, set it - End
16
17$IsCLI = true;
18
19// Include main module - Start
20include_once($_SERVER['DOCUMENT_ROOT'].'/data/config.inc.php');
21// Include main module - End
22
23if (Core::RunningFromCLI() == false)
24 {
25 Core::DisplayCLIBrowserError('EMAIL_PIPE');
26 exit;
27 }
28
29// Load other modules - Start
30Core::LoadObject('powowcli');
31Core::LoadObject('statistics');
32// Load other modules - End
33
34// Script configuration - Start
35define("PROCESS_FILE_LIMIT", 1); // limit of max files processing at the same instant
36define("VERBOSE_MODE", in_array('-v', $argv)); // true if '-v' is CLI argument
37// Script configuration - End
38
39// Retrieve 1 available acct file - Start
40// Notes:
41// * while transfering files, they will be terminated with .tmp
42// * we need to transfert file to MYSQL data directory to be able to use LOAD DATA INFILE
43// * verbose mode allow you to know "execution time (% of total execution time)" for each step
44
45// Initialisation - Start
46$PMTADir = $_SERVER['DOCUMENT_ROOT']."data/pmtastats/";
47$MYSQLDir = "/var/lib/mysql/";
48// Initialisation - End
49
50// Check if current processed file limit is elapsed - Start
51if (false !== ($ArrayFiles = scandir($MYSQLDir)))
52 {
53 $ArrayProcessingFiles = preg_grep('/^acct-[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{4}\.csv$/', $ArrayFiles);
54 if (count($ArrayProcessingFiles) >= PROCESS_FILE_LIMIT)
55 {
56 echo "[".getmypid()."][".date('Y-m-d H:i:s')."] ".PROCESS_FILE_LIMIT." Files already processing, CLI aborded\n";
57 exit;
58 }
59 }
60else
61 {
62 exit("[".getmypid()."][".date('Y-m-d H:i:s')."] Can't use scandir() on \"".$MYSQLDir."\", please check its existence & rights\n");
63 }
64// Check if current processed file limit is elapsed - End
65
66// Retrieve 1 available acct file - End
67if (false !== ($ArrayFiles = scandir($PMTADir)))
68 {
69 $ArrayFilesToProcess = preg_grep('/^acct-[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{4}\.csv$/', $ArrayFiles);
70 if (count($ArrayFilesToProcess) > 0)
71 {
72 // Get first file
73 $Filename = array_shift($ArrayFilesToProcess);
74
75 // Move file into mysql data dir - Start
76 if(file_exists($MYSQLDir.$Filename))
77 {
78 // Delete protection if file already in "mysql" dir
79 if (unlink($MYSQLDir.$Filename) === false)
80 {
81 exit("[".getmypid()."][".date('Y-m-d H:i:s')."] Can't use unlink() on \"".$MYSQLDir.$Filename."\", please take a look at it. CLI aborded\n");
82 }
83 }
84 if (rename($PMTADir.$Filename, $MYSQLDir.$Filename) === false)
85 {
86 exit("[".getmypid()."][".date('Y-m-d H:i:s')."] Can't rename \"".$PMTADir.$Filename."\" into \"".$MYSQLDir.$Filename."\", CLI aborded\n");
87 }
88 // Move file into mysql data dir - End
89
90 // process file
91 processAcctFile($Filename);
92 }
93 else
94 {
95 echo "[".getmypid()."][".date('Y-m-d H:i:s')."] No file to process\n";
96 exit;
97 }
98 }
99else
100 {
101 exit("[".getmypid()."][".date('Y-m-d H:i:s')."] Can't use scandir() on \"".$PMTADir."\", please check its existence & rights\n");
102 }
103// Retrieve 1 available acct file - End
104
105
106function processAcctFile($Filename)
107 {
108 global $PMTADir, $MYSQLDir;
109 $Start = microtime(true);
110 $TableName = array_shift(explode('.', $Filename)); // strip ".csv"
111 $TableName = "temporary_" . str_replace('-', '_', $TableName);
112
113 $ArrayCLISteps = array();
114 $TotalProcessed = 0;
115 $TotalBAT = 0;
116 $TotalInvalid = 0;
117 $TotalInserted = 0;
118 $TotalDuplicates = 0;
119
120 // Delete table if exists - Start
121 $SQLQuery = 'DROP TABLE IF EXISTS '.$TableName.';';
122 Database::$Interface->ExecuteQuery($SQLQuery);
123 // Delete table if exists - End
124
125 // Create TEMPORARY table - Start
126 $SQLQuery = 'CREATE TEMPORARY TABLE '.$TableName.' (Line INT(11) NOT NULL AUTO_INCREMENT, `type` VARCHAR(2), timeLogged DATETIME, orig VARCHAR(250), CampaignID INT(11) DEFAULT NULL, AutoResponderID INT(11) DEFAULT NULL, TransactionalID INT(11) DEFAULT NULL, EmailID INT(11) DEFAULT NULL, SubscriberID INT(11) DEFAULT NULL, ListID INT(11) DEFAULT NULL, UserID INT(11) DEFAULT NULL, IsPreview TINYINT(1) DEFAULT 0, rcpt VARCHAR(250), orcpt VARCHAR(250), dsnAction VARCHAR(250), dsnStatus VARCHAR(250), dsnDiag VARCHAR(250), dsnMta VARCHAR(250), bounceCat VARCHAR(250), srcType VARCHAR(250), srcMta VARCHAR(250), dlvType VARCHAR(250), dlvSourceIp VARCHAR(250), dlvDestinationIp VARCHAR(250), dlvSize VARCHAR(250), vmta VARCHAR(250), jobId VARCHAR(250), envId VARCHAR(250), PRIMARY KEY (Line)) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;';
127 Database::$Interface->ExecuteQuery($SQLQuery);
128 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'table created', 'time' => microtime(true)); }
129 // Create TEMPORARY table - End
130
131 // Load data into table - Start
132 $SQLQuery = 'LOAD DATA INFILE "./'.$Filename.'" INTO TABLE '.$TableName.' FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\' IGNORE 1 LINES (`type`, timeLogged, orig, rcpt, orcpt, dsnAction, dsnStatus, dsnDiag, dsnMta, bounceCat, srcType, srcMta, dlvType, dlvSourceIp, dlvDestinationIp, dlvSize, vmta, jobId, envId);';
133 Database::$Interface->ExecuteQuery($SQLQuery);
134 $TotalProcessed = Database::$Interface->GetAffectedRows();
135 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'data loaded', 'time' => microtime(true)); }
136 // Load data into table - End
137
138 // Decode ORIG - Start
139 // DECRYPT_ORIG is a MYSQL user function => DECRYPT_ORIG(orig, index)
140 // ex: DECRYPT_ORIG('bounce-1148768-5552-708128-91640-44-1219964-44-44@info.cci-paris-idf.fr', 8); => 0 pour le TransactionalID
141 $SQLQuery = 'UPDATE '.$TableName.' SET CampaignID = DECRYPT_ORIG(orig, 2), SubscriberID = DECRYPT_ORIG(orig, 3), ListID = DECRYPT_ORIG(orig, 4), UserID = DECRYPT_ORIG(orig, 5), AutoResponderID = DECRYPT_ORIG(orig, 6), EmailID = DECRYPT_ORIG(orig, 7), TransactionalID = DECRYPT_ORIG(orig, 8), IsPreview = DECRYPT_ORIG(orig, 9);';
142 Database::$Interface->ExecuteQuery($SQLQuery);
143 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'orig decrypted', 'time' => microtime(true)); }
144 // Decode ORIG - End
145
146 // Validate data - Start
147 // Delete BAT - Start
148 $SQLQuery = 'DELETE FROM '.$TableName.' WHERE IsPreview = 1;';
149 Database::$Interface->ExecuteQuery($SQLQuery);
150 $TotalBAT = Database::$Interface->GetAffectedRows();
151 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'BAT deleted', 'time' => microtime(true)); }
152 // Delete BAT - End
153
154 // Delete invalid campaigns - Start
155 $SQLQuery = 'DELETE t.* FROM '.$TableName.' t LEFT JOIN '.MYSQL_TABLE_PREFIX.'campaigns c ON t.CampaignID = c.CampaignID AND t.UserID = c.RelOwnerUserID WHERE t.CampaignID IS NULL OR (t.CampaignID != 0 AND c.CampaignID IS NULL);';
156 Database::$Interface->ExecuteQuery($SQLQuery);
157 $TotalInvalid += Database::$Interface->GetAffectedRows();
158 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => '"invalid campaigns" lines deleted', 'time' => microtime(true)); }
159 // Delete invalid campaigns - End
160
161 // Delete invalid auto responders - Start
162 $SQLQuery = 'DELETE t.* FROM '.$TableName.' t LEFT JOIN '.MYSQL_TABLE_PREFIX.'auto_responders a ON t.AutoResponderID = a.AutoResponderID AND t.UserID = a.RelOwnerUserID WHERE t.AutoResponderID IS NULL OR (t.AutoResponderID != 0 AND a.AutoResponderID IS NULL);';
163 Database::$Interface->ExecuteQuery($SQLQuery);
164 $TotalInvalid += Database::$Interface->GetAffectedRows();
165 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => '"invalid auto responders" lines deleted', 'time' => microtime(true)); }
166 // Delete invalid auto responders - End
167
168 // Delete data already in esp_acct_history - Start
169 $SQLQuery = 'DELETE t.* FROM '.$TableName.' t LEFT JOIN '.MYSQL_TABLE_PREFIX.'acct_history h ON t.CampaignID = h.CampaignID AND t.AutoResponderID = h.AutoResponderID AND t.TransactionalID = h.TransactionalID AND t.SubscriberID = h.SubscriberID AND t.timeLogged = h.timeLogged WHERE h.acct_id IS NOT NULL;';
170 Database::$Interface->ExecuteQuery($SQLQuery);
171 $TotalDuplicates += Database::$Interface->GetAffectedRows();
172 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'duplicates lines deleted', 'time' => microtime(true)); }
173 // Delete data already in esp_acct_history - End
174 // Validate data - End
175
176 // Insert new data - Start
177 $SQLQuery = "INSERT IGNORE INTO ".MYSQL_TABLE_PREFIX."acct_history (`type`, timeLogged, orig, CampaignID, AutoResponderID, TransactionalID, EmailID, SubscriberID, ListID, UserID, rcpt, orcpt, dsnAction, dsnStatus, dsnDiag, dsnMta, bounceCat, srcType, srcMta, dlvType, dlvSourceIp, dlvDestinationIp, dlvSize, vmta, jobId, envId) SELECT `type`, timeLogged, orig, CampaignID, AutoResponderID, TransactionalID, EmailID, SubscriberID, ListID, UserID, rcpt, orcpt, dsnAction, dsnStatus, dsnDiag, dsnMta, bounceCat, srcType, srcMta, dlvType, dlvSourceIp, dlvDestinationIp, dlvSize, vmta, jobId, envId FROM ".$TableName.";";
178 Database::$Interface->ExecuteQuery($SQLQuery);
179 $TotalInserted = Database::$Interface->GetAffectedRows();
180 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'Lines inserted in '.MYSQL_TABLE_PREFIX.'acct_history', 'time' => microtime(true)); }
181 // Insert new data - End
182
183 // Update bounced subscribers - Start
184 $SQLQuery = 'SET SESSION group_concat_max_len = 1048576'; // GROUP_CONCAT function can now return up to 1048576 chars, if SubscriberID length 8 chars => up to 131000 bounces before it fails
185 Database::$Interface->ExecuteQuery($SQLQuery);
186
187 $SQLQuery = "SELECT ListID, UserID, CASE WHEN bounceCat = 'bad-domain' OR bounceCat = 'bad-mailbox' OR bounceCat = 'bad-configuration' OR bounceCat = 'inactive-mailbox' OR bounceCat = 'routing-errors' THEN 'Hard' ELSE 'Soft' END AS 'BounceType',
188 GROUP_CONCAT(DISTINCT SubscriberID ORDER BY SubscriberID) AS 'SubscriberIDs' FROM ".$TableName." WHERE `type` = 'b' GROUP BY ListID, UserID, BounceType;"; // retrieve all bounced subscriber ids for all lists/user id tupple
189 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
190
191 while ($EachRow = mysql_fetch_assoc($SQLResult))
192 {
193 // update each lists
194 if ($EachRow['SubscriberIDs'] != '')
195 {
196 $ListUpdateQuery = "UPDATE ".MYSQL_TABLE_PREFIX."subscribers_".$EachRow['ListID']." SET BounceType='".$EachRow['BounceType']."' WHERE SubscriberID IN (".$EachRow['SubscriberIDs'].");";
197 Database::$Interface->ExecuteQuery($ListUpdateQuery);
198
199 // Update activity statistics - Start
200 // TODO - Note not actually the true calculation, we might want to check all subscribers status before update
201 // Ex - "Soft bounce" updated to "Hard bounce" will count TotalHardBounce + 1 but not TotalSoftBounce - 1
202 $UpdatedSubscribersTotal = Database::$Interface->getAffectedRows();
203 if ($UpdatedSubscribersTotal > 0)
204 {
205 $ArrayActivities = array('Total'.$EachRow['BounceType'].'Bounce' => $UpdatedSubscribersTotal);
206 Statistics::UpdateListActivityStatistics($EachRow['ListID'], $EachRow['UserID'], $ArrayActivities);
207 }
208 // Update activity statistics - End
209 }
210 }
211 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'Subscriber bounces updated', 'time' => microtime(true)); }
212 // Update bounced subscribers - End
213
214 // Update campaigns/auto responders TotalDelivery, TotalSoftBounces, TotalHardBounces - Start
215 $SQLQuery = "SELECT CampaignID, AutoResponderID, CASE
216 WHEN `type` = 'd' THEN
217 CASE
218 WHEN rcpt LIKE '%@gmail.com' THEN
219 'GmailTotalDelivery'
220 WHEN rcpt LIKE '%@laposte.net' THEN
221 'LaPosteTotalDelivery'
222 WHEN rcpt LIKE '%@orange.fr' OR rcpt LIKE '%@wanadoo.fr' THEN
223 'OrangeTotalDelivery'
224 WHEN rcpt LIKE '%@free.fr' OR rcpt LIKE '%@aliceadsl.fr' OR rcpt LIKE '%@libertysurf.fr' OR rcpt LIKE '%@chez.com' OR rcpt LIKE '%@freesbee.fr' OR rcpt LIKE '%@infonie.fr' OR rcpt LIKE '%@online.fr' OR rcpt LIKE '%@worldonline.fr' OR rcpt LIKE '%@alicepro.fr' OR rcpt LIKE '%@nomade.fr' THEN
225 'FreeTotalDelivery'
226 WHEN rcpt LIKE '%@sfr.fr' OR rcpt LIKE '%@club-internet.fr' OR rcpt LIKE '%@neuf.fr' OR rcpt LIKE '%@club.fr' OR rcpt LIKE '%@cegetel.net' OR rcpt LIKE '%@9online.fr' OR rcpt LIKE '%@akeonet.com' OR rcpt LIKE '%@9business.fr' OR rcpt LIKE '%@cario.fr' OR rcpt LIKE '%@fnac.net' OR rcpt LIKE '%@guideo.fr' OR rcpt LIKE '%@mageos.com' OR rcpt LIKE '%@waika9.com' THEN
227 'SFRTotalDelivery'
228 WHEN rcpt LIKE '%@yahoo.com.ar' OR rcpt LIKE '%@yahoo.com.au' OR rcpt LIKE '%@yahoo.com.br' OR rcpt LIKE '%@yahoo.ca' OR rcpt LIKE '%@yahoo.cl' OR rcpt LIKE '%@yahoo.cn' OR rcpt LIKE '%@yahoo.com.co' OR rcpt LIKE '%@yahoo.com.mx' OR rcpt LIKE '%@yahoo.co.nz' OR rcpt LIKE '%@yahoo.com.pe' OR rcpt LIKE '%@yahoo.com.tr' OR rcpt LIKE '%@rocketmail.com' OR rcpt LIKE '%@yahoo.com' OR rcpt LIKE '%@ymail.com' OR rcpt LIKE '%@y7mail.com' OR rcpt LIKE '%@yahoo.com.ve' OR rcpt LIKE '%@btinternet.com' OR rcpt LIKE '%@btopenworld.com' OR rcpt LIKE '%@yahoo.dk' OR rcpt LIKE '%@yahoo.fr' OR rcpt LIKE '%@yahoo.de' OR rcpt LIKE '%@yahoo.gr' OR rcpt LIKE '%@yahoo.it' OR rcpt LIKE '%@yahoo.no' OR rcpt LIKE '%@yahoo.pl' OR rcpt LIKE '%@yahoo.se' OR rcpt LIKE '%@yahoo.co.uk' OR rcpt LIKE '%@yahoo.ie' OR rcpt LIKE '%@yahoo.es' THEN
229 'YahooTotalDelivery'
230 WHEN rcpt LIKE '%@hotmail.com' OR rcpt LIKE '%@outlook.com' OR rcpt LIKE '%@outlook.fr' OR rcpt LIKE '%@live.com' OR rcpt LIKE '%@hotmail.co.il' OR rcpt LIKE '%@msn.com' OR rcpt LIKE '%@msn.fr' OR rcpt LIKE '%@hotmail.com.ar' OR rcpt LIKE '%@hotmail.com.br' OR rcpt LIKE '%@hotmail.com.tr' OR rcpt LIKE '%@hotmail.co.th' OR rcpt LIKE '%@hotmail.co.uk' OR rcpt LIKE '%@hotmail.de' OR rcpt LIKE '%@hotmail.es' OR rcpt LIKE '%@hotmail.fr' OR rcpt LIKE '%@hotmail.it' OR rcpt LIKE '%@hotmail.jp' OR rcpt LIKE '%@hotmail.se' OR rcpt LIKE '%@live.at' OR rcpt LIKE '%@live.be' OR rcpt LIKE '%@live.ca' OR rcpt LIKE '%@live.cl' OR rcpt LIKE '%@live.cn' OR rcpt LIKE '%@live.co.kr' OR rcpt LIKE '%@live.com.ar' OR rcpt LIKE '%@live.com.au' OR rcpt LIKE '%@live.com.mx' OR rcpt LIKE '%@live.com.my' OR rcpt LIKE '%@live.com.sg' OR rcpt LIKE '%@live.co.za' OR rcpt LIKE '%@live.de' OR rcpt LIKE '%@live.dk' OR rcpt LIKE '%@live.fr' OR rcpt LIKE '%@live.hk' OR rcpt LIKE '%@live.ie' OR rcpt LIKE '%@live.in' OR rcpt LIKE '%@live.it' OR rcpt LIKE '%@live.jp' OR rcpt LIKE '%@live.nl' OR rcpt LIKE '%@live.no' OR rcpt LIKE '%@live.ru' OR rcpt LIKE '%@live.se' THEN
231 'HotmailTotalDelivery'
232 ELSE 'OthersTotalDelivery'
233 END
234 WHEN `type` = 'b' THEN
235 CASE
236 WHEN bounceCat IN ('bad-domain', 'bad-mailbox', 'bad-configuration', 'inactive-mailbox', 'routing-errors') THEN
237 CASE
238 WHEN rcpt LIKE '%@gmail.com' THEN
239 'GmailTotalHardBounces'
240 WHEN rcpt LIKE '%@laposte.net' THEN
241 'LaPosteTotalHardBounces'
242 WHEN rcpt LIKE '%@orange.fr' OR rcpt LIKE '%@wanadoo.fr' THEN
243 'OrangeTotalHardBounces'
244 WHEN rcpt LIKE '%@free.fr' OR rcpt LIKE '%@aliceadsl.fr' OR rcpt LIKE '%@libertysurf.fr' OR rcpt LIKE '%@chez.com' OR rcpt LIKE '%@freesbee.fr' OR rcpt LIKE '%@infonie.fr' OR rcpt LIKE '%@online.fr' OR rcpt LIKE '%@worldonline.fr' OR rcpt LIKE '%@alicepro.fr' OR rcpt LIKE '%@nomade.fr' THEN
245 'FreeTotalHardBounces'
246 WHEN rcpt LIKE '%@sfr.fr' OR rcpt LIKE '%@club-internet.fr' OR rcpt LIKE '%@neuf.fr' OR rcpt LIKE '%@club.fr' OR rcpt LIKE '%@cegetel.net' OR rcpt LIKE '%@9online.fr' OR rcpt LIKE '%@akeonet.com' OR rcpt LIKE '%@9business.fr' OR rcpt LIKE '%@cario.fr' OR rcpt LIKE '%@fnac.net' OR rcpt LIKE '%@guideo.fr' OR rcpt LIKE '%@mageos.com' OR rcpt LIKE '%@waika9.com' THEN
247 'SFRTotalHardBounces'
248 WHEN rcpt LIKE '%@yahoo.com.ar' OR rcpt LIKE '%@yahoo.com.au' OR rcpt LIKE '%@yahoo.com.br' OR rcpt LIKE '%@yahoo.ca' OR rcpt LIKE '%@yahoo.cl' OR rcpt LIKE '%@yahoo.cn' OR rcpt LIKE '%@yahoo.com.co' OR rcpt LIKE '%@yahoo.com.mx' OR rcpt LIKE '%@yahoo.co.nz' OR rcpt LIKE '%@yahoo.com.pe' OR rcpt LIKE '%@yahoo.com.tr' OR rcpt LIKE '%@rocketmail.com' OR rcpt LIKE '%@yahoo.com' OR rcpt LIKE '%@ymail.com' OR rcpt LIKE '%@y7mail.com' OR rcpt LIKE '%@yahoo.com.ve' OR rcpt LIKE '%@btinternet.com' OR rcpt LIKE '%@btopenworld.com' OR rcpt LIKE '%@yahoo.dk' OR rcpt LIKE '%@yahoo.fr' OR rcpt LIKE '%@yahoo.de' OR rcpt LIKE '%@yahoo.gr' OR rcpt LIKE '%@yahoo.it' OR rcpt LIKE '%@yahoo.no' OR rcpt LIKE '%@yahoo.pl' OR rcpt LIKE '%@yahoo.se' OR rcpt LIKE '%@yahoo.co.uk' OR rcpt LIKE '%@yahoo.ie' OR rcpt LIKE '%@yahoo.es' THEN
249 'YahooTotalHardBounces'
250 WHEN rcpt LIKE '%@hotmail.com' OR rcpt LIKE '%@outlook.com' OR rcpt LIKE '%@outlook.fr' OR rcpt LIKE '%@live.com' OR rcpt LIKE '%@hotmail.co.il' OR rcpt LIKE '%@msn.com' OR rcpt LIKE '%@msn.fr' OR rcpt LIKE '%@hotmail.com.ar' OR rcpt LIKE '%@hotmail.com.br' OR rcpt LIKE '%@hotmail.com.tr' OR rcpt LIKE '%@hotmail.co.th' OR rcpt LIKE '%@hotmail.co.uk' OR rcpt LIKE '%@hotmail.de' OR rcpt LIKE '%@hotmail.es' OR rcpt LIKE '%@hotmail.fr' OR rcpt LIKE '%@hotmail.it' OR rcpt LIKE '%@hotmail.jp' OR rcpt LIKE '%@hotmail.se' OR rcpt LIKE '%@live.at' OR rcpt LIKE '%@live.be' OR rcpt LIKE '%@live.ca' OR rcpt LIKE '%@live.cl' OR rcpt LIKE '%@live.cn' OR rcpt LIKE '%@live.co.kr' OR rcpt LIKE '%@live.com.ar' OR rcpt LIKE '%@live.com.au' OR rcpt LIKE '%@live.com.mx' OR rcpt LIKE '%@live.com.my' OR rcpt LIKE '%@live.com.sg' OR rcpt LIKE '%@live.co.za' OR rcpt LIKE '%@live.de' OR rcpt LIKE '%@live.dk' OR rcpt LIKE '%@live.fr' OR rcpt LIKE '%@live.hk' OR rcpt LIKE '%@live.ie' OR rcpt LIKE '%@live.in' OR rcpt LIKE '%@live.it' OR rcpt LIKE '%@live.jp' OR rcpt LIKE '%@live.nl' OR rcpt LIKE '%@live.no' OR rcpt LIKE '%@live.ru' OR rcpt LIKE '%@live.se' THEN
251 'HotmailTotalHardBounces'
252 ELSE 'OthersTotalHardBounces'
253 END
254 ELSE
255 CASE
256 WHEN rcpt LIKE '%@gmail.com' THEN
257 'GmailTotalSoftBounces'
258 WHEN rcpt LIKE '%@laposte.net' THEN
259 'LaPosteTotalSoftBounces'
260 WHEN rcpt LIKE '%@orange.fr' OR rcpt LIKE '%@wanadoo.fr' THEN
261 'OrangeTotalSoftBounces'
262 WHEN rcpt LIKE '%@free.fr' OR rcpt LIKE '%@aliceadsl.fr' OR rcpt LIKE '%@libertysurf.fr' OR rcpt LIKE '%@chez.com' OR rcpt LIKE '%@freesbee.fr' OR rcpt LIKE '%@infonie.fr' OR rcpt LIKE '%@online.fr' OR rcpt LIKE '%@worldonline.fr' OR rcpt LIKE '%@alicepro.fr' OR rcpt LIKE '%@nomade.fr' THEN
263 'FreeTotalSoftBounces'
264 WHEN rcpt LIKE '%@sfr.fr' OR rcpt LIKE '%@club-internet.fr' OR rcpt LIKE '%@neuf.fr' OR rcpt LIKE '%@club.fr' OR rcpt LIKE '%@cegetel.net' OR rcpt LIKE '%@9online.fr' OR rcpt LIKE '%@akeonet.com' OR rcpt LIKE '%@9business.fr' OR rcpt LIKE '%@cario.fr' OR rcpt LIKE '%@fnac.net' OR rcpt LIKE '%@guideo.fr' OR rcpt LIKE '%@mageos.com' OR rcpt LIKE '%@waika9.com' THEN
265 'SFRTotalSoftBounces'
266 WHEN rcpt LIKE '%@yahoo.com.ar' OR rcpt LIKE '%@yahoo.com.au' OR rcpt LIKE '%@yahoo.com.br' OR rcpt LIKE '%@yahoo.ca' OR rcpt LIKE '%@yahoo.cl' OR rcpt LIKE '%@yahoo.cn' OR rcpt LIKE '%@yahoo.com.co' OR rcpt LIKE '%@yahoo.com.mx' OR rcpt LIKE '%@yahoo.co.nz' OR rcpt LIKE '%@yahoo.com.pe' OR rcpt LIKE '%@yahoo.com.tr' OR rcpt LIKE '%@rocketmail.com' OR rcpt LIKE '%@yahoo.com' OR rcpt LIKE '%@ymail.com' OR rcpt LIKE '%@y7mail.com' OR rcpt LIKE '%@yahoo.com.ve' OR rcpt LIKE '%@btinternet.com' OR rcpt LIKE '%@btopenworld.com' OR rcpt LIKE '%@yahoo.dk' OR rcpt LIKE '%@yahoo.fr' OR rcpt LIKE '%@yahoo.de' OR rcpt LIKE '%@yahoo.gr' OR rcpt LIKE '%@yahoo.it' OR rcpt LIKE '%@yahoo.no' OR rcpt LIKE '%@yahoo.pl' OR rcpt LIKE '%@yahoo.se' OR rcpt LIKE '%@yahoo.co.uk' OR rcpt LIKE '%@yahoo.ie' OR rcpt LIKE '%@yahoo.es' THEN
267 'YahooTotalSoftBounces'
268 WHEN rcpt LIKE '%@hotmail.com' OR rcpt LIKE '%@outlook.com' OR rcpt LIKE '%@outlook.fr' OR rcpt LIKE '%@live.com' OR rcpt LIKE '%@hotmail.co.il' OR rcpt LIKE '%@msn.com' OR rcpt LIKE '%@msn.fr' OR rcpt LIKE '%@hotmail.com.ar' OR rcpt LIKE '%@hotmail.com.br' OR rcpt LIKE '%@hotmail.com.tr' OR rcpt LIKE '%@hotmail.co.th' OR rcpt LIKE '%@hotmail.co.uk' OR rcpt LIKE '%@hotmail.de' OR rcpt LIKE '%@hotmail.es' OR rcpt LIKE '%@hotmail.fr' OR rcpt LIKE '%@hotmail.it' OR rcpt LIKE '%@hotmail.jp' OR rcpt LIKE '%@hotmail.se' OR rcpt LIKE '%@live.at' OR rcpt LIKE '%@live.be' OR rcpt LIKE '%@live.ca' OR rcpt LIKE '%@live.cl' OR rcpt LIKE '%@live.cn' OR rcpt LIKE '%@live.co.kr' OR rcpt LIKE '%@live.com.ar' OR rcpt LIKE '%@live.com.au' OR rcpt LIKE '%@live.com.mx' OR rcpt LIKE '%@live.com.my' OR rcpt LIKE '%@live.com.sg' OR rcpt LIKE '%@live.co.za' OR rcpt LIKE '%@live.de' OR rcpt LIKE '%@live.dk' OR rcpt LIKE '%@live.fr' OR rcpt LIKE '%@live.hk' OR rcpt LIKE '%@live.ie' OR rcpt LIKE '%@live.in' OR rcpt LIKE '%@live.it' OR rcpt LIKE '%@live.jp' OR rcpt LIKE '%@live.nl' OR rcpt LIKE '%@live.no' OR rcpt LIKE '%@live.ru' OR rcpt LIKE '%@live.se' THEN
269 'HotmailTotalSoftBounces'
270 ELSE 'OthersTotalSoftBounces'
271 END
272 END
273 END AS 'ColumnName',
274 COUNT(*) AS Total
275 FROM ".$TableName." WHERE TransactionalID = 0 GROUP BY CampaignID, AutoResponderID, ColumnName;";
276 $SQLResult = Database::$Interface->ExecuteQuery($SQLQuery);
277
278 $ArrayTotals = array('Campaign' => array(), 'AutoResponder' => array(), 'Transactional' => array());
279 while ($EachRow = mysql_fetch_assoc($SQLResult))
280 {
281 $keyType = $EachRow['CampaignID'] != 0 ? 'Campaign' : ($EachRow['AutoResponderID'] != 0 ? 'AutoResponder' : 'Transactional');
282 // campaign/auto responder
283 if (isset($ArrayTotals[$keyType][$EachRow[$keyType.'ID']]) === false)
284 {
285 $ArrayTotals[$keyType][$EachRow[$keyType.'ID']] = array('TotalDelivery' => 0, 'TotalSoftBounces' => 0, 'TotalHardBounces' => 0, 'Domains' => array());
286 }
287
288 if (strpos($EachRow['ColumnName'], 'TotalDelivery') !== false)
289 {
290 $ArrayTotals[$keyType][$EachRow[$keyType.'ID']]['TotalDelivery'] += $EachRow['Total'];
291 }
292 else if (strpos($EachRow['ColumnName'], 'TotalSoftBounces') !== false)
293 {
294 $ArrayTotals[$keyType][$EachRow[$keyType.'ID']]['TotalSoftBounces'] += $EachRow['Total'];
295 }
296 else if (strpos($EachRow['ColumnName'], 'TotalHardBounces') !== false)
297 {
298 $ArrayTotals[$keyType][$EachRow[$keyType.'ID']]['TotalHardBounces'] += $EachRow['Total'];
299 }
300 // domains
301 if (isset($ArrayTotals[$keyType][$EachRow[$keyType.'ID']]['Domains'][$EachRow['ColumnName']]) === false)
302 {
303 $ArrayTotals[$keyType][$EachRow[$keyType.'ID']]['Domains'][$EachRow['ColumnName']] = 0;
304 }
305 $ArrayTotals[$keyType][$EachRow[$keyType.'ID']]['Domains'][$EachRow['ColumnName']] += $EachRow['Total'];
306 }
307
308 // Update campaigns totals - Start
309 foreach ($ArrayTotals['Campaign'] as $CampaignID => $ArrayTotal)
310 {
311 // update esp_campaigns
312 $SQLQuery = 'UPDATE '.MYSQL_TABLE_PREFIX.'campaigns SET TotalDelivery = TotalDelivery + '.$ArrayTotal['TotalDelivery'].', TotalSoftBounces = TotalSoftBounces + '.$ArrayTotal['TotalSoftBounces'].', TotalHardBounces = TotalHardBounces + '.$ArrayTotal['TotalHardBounces'].' WHERE CampaignID = '.$CampaignID.';';
313 Database::$Interface->ExecuteQuery($SQLQuery);
314 if (count($ArrayTotal['Domains']) > 0)
315 {
316 // update esp_stats_campaign_domains
317 $ArrayDomainFields = array();
318 foreach ($ArrayTotal['Domains'] as $Field => $Total)
319 {
320 $ArrayDomainFields[] = $Field.' = '.$Field.' + '.$Total;
321 }
322 $SQLQuery = 'UPDATE '.MYSQL_TABLE_PREFIX.'stats_campaign_domains SET '.implode(', ', $ArrayDomainFields).' WHERE RelCampaignID = '.$CampaignID.';';
323 Database::$Interface->ExecuteQuery($SQLQuery);
324 }
325 }
326 // Update campaigns totals - End
327
328 // Update auto responders totals - Start
329 foreach ($ArrayTotals['AutoResponder'] as $AutoResponderID => $ArrayTotal)
330 {
331 // update esp_auto_responders
332 $SQLQuery = 'UPDATE '.MYSQL_TABLE_PREFIX.'auto_responders SET TotalDelivery = TotalDelivery + '.$ArrayTotal['TotalDelivery'].', TotalSoftBounces = TotalSoftBounces + '.$ArrayTotal['TotalSoftBounces'].', TotalHardBounces = TotalHardBounces + '.$ArrayTotal['TotalHardBounces'].' WHERE AutoResponderID = '.$AutoResponderID.';';
333 Database::$Interface->ExecuteQuery($SQLQuery);
334
335 // TODO - currently there is no domains tracking for auto responders
336 // if (count($ArrayTotal['Domains']) > 0)
337 // {
338 // // update esp_stats_auto_responder_domains
339 // $ArrayDomainFields = array();
340 // foreach ($ArrayTotal['Domains'] as $Field => $Total)
341 // {
342 // $ArrayDomainFields[] = $Field.' = '.$Field.' + '.$Total;
343 // }
344 // $SQLQuery = 'UPDATE '.MYSQL_TABLE_PREFIX.'stats_auto_responder_domains SET '.implode(', ', $ArrayDomainFields).' WHERE RelAutoResponderID = '.$AutoResponderID.';';
345 // Database::$Interface->ExecuteQuery($SQLQuery);
346 // }
347 }
348 // Update auto responders totals - End
349
350 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'campaigns/auto responders TotalDelivery, TotalSoftBounces & TotalHardBounces updated', 'time' => microtime(true)); }
351 // Update campaigns/auto responders TotalDelivery, TotalSoftBounces, TotalHardBounces - End
352
353 // Delete table if exists - Start
354 $SQLQuery = 'DROP TABLE IF EXISTS '.$TableName.';';
355 Database::$Interface->ExecuteQuery($SQLQuery);
356 if (VERBOSE_MODE) { $ArrayCLISteps[] = array('name' => 'table deleted', 'time' => microtime(true)); }
357 // Delete table if exists - End
358
359 // Move file into "archive" folder - Start
360 if(file_exists($PMTADir."archive/".$Filename))
361 {
362 // Delete protection if file already in "archive" dir
363 if (unlink($PMTADir."archive/".$Filename) === false)
364 {
365 exit("[".getmypid()."][".date('Y-m-d H:i:s')."] Can't use unlink() on \"".$PMTADir."archive/".$Filename."\", please take a look at it. CLI aborded\n");
366 }
367 }
368 if (rename($MYSQLDir.$Filename, $PMTADir."archive/".$Filename) === false)
369 {
370 exit("[".getmypid()."][".date('Y-m-d H:i:s')."] Can't use rename() on \"".$MYSQLDir.$Filename."\" to \"".$PMTADir."archive/".$Filename."\", please take a look at it. CLI aborded\n");
371 }
372 // Move file into "archive" folder - End
373
374 // Display end of CLI stats - Start
375 $End = microtime(true);
376
377 if (VERBOSE_MODE)
378 {
379 // Computed each steps execution time/% of total execution time - Start
380 foreach ($ArrayCLISteps as $key => $eachCLIStep)
381 {
382 $previousTime = array_key_exists($key -1, $ArrayCLISteps) ? $ArrayCLISteps[$key - 1]['time'] : $Start;
383 echo "[".getmypid()."][".date('Y-m-d H:i:s', $eachCLIStep['time'])."] ".$Filename." - ".$eachCLIStep['name']." - ".number_format($eachCLIStep['time'] - $previousTime, 2)."s (".number_format(100 * ($eachCLIStep['time'] - $previousTime) / ($End - $Start), 2)."%)\n";
384 }
385 // Computed each steps execution time/% of total execution time - Start
386 }
387
388 echo "[".getmypid()."][".date('Y-m-d H:i:s')."] ".$Filename." - ".$TotalProcessed." lines processed in ".number_format($End - $Start, 2)."s - ".$TotalInserted." insertions, ".$TotalDuplicates." duplicates, ".$TotalInvalid." invalids, ".$TotalBAT." BAT skipped - Memory peak usage=". number_format((float) memory_get_peak_usage(true) / 1024 / 1024, 2, '.', '')."Mo\n";
389 // Display end of CLI stats - End
390 }
391
392?>