function amavis_logger($fullpath)
{
$q = new mysql();
$ini = new iniFrameWork($fullpath);
$message_id = $ini->_params["TIME"]["message-id"];
$time_amavis = $ini->_params["TIME"]["time_amavis"];
$smtp_sender = $ini->_params["TIME"]["server_from"];
$mailfrom = $ini->_params["TIME"]["mailfrom"];
$mailto = $ini->_params["TIME"]["mailto"];
$Country = $ini->_params["TIME"]["Country"];
$Region = $ini->_params["TIME"]["Region"];
$City = $ini->_params["TIME"]["City"];
$kas = $ini->_params["TIME"]["kas"];
$banned = $ini->_params["TIME"]["banned"];
$infected = $ini->_params["TIME"]["infected"];
$spammy = $ini->_params["TIME"]["spammy"];
$spam = $ini->_params["TIME"]["spam"];
$blacklisted = $ini->_params["TIME"]["blacklisted"];
$whitelisted = $ini->_params["TIME"]["whitelisted"];
$size = $ini->_params["TIME"]["size"];
$subject = trim($ini->_params["TIME"]["subject"]);
events("amavis_logger():: " . basename($fullpath) . " ({$message_id}) from=<{$mailfrom}> to=<{$mailto}>");
$Region = trim(str_replace("'", '`', $Region));
$Country = trim(str_replace("'", '`', $Country));
$City = trim(str_replace("'", '`', $City));
if ($Country == null) {
$array_geo = GeoIP($smtp_sender);
$Country = $array_geo[0];
$City = $array_geo[1];
$City = addslashes($City);
$Country = addslashes($Country);
}
if (preg_match('#(.+?)@(.+)#', $mailfrom, $re)) {
$domain_from = $re[2];
}
if (!is_numeric($whitelisted)) {
$whitelisted = 0;
}
if (!is_numeric($blacklisted)) {
$blacklisted = 0;
}
if (!is_numeric($kas)) {
$kas = 0;
}
if (!is_numeric($banned)) {
$banned = 0;
}
if (!is_numeric($infected)) {
$infected = 0;
}
if (!is_numeric($spammy)) {
$spammy = 0;
}
if (!is_numeric($spam)) {
$spam = 0;
}
if (!is_numeric($size)) {
$size = 0;
}
$mailto = str_replace("'", "", $mailto);
$mailfrom = str_replace("'", '', $mailfrom);
$mailto = $mailto . ",";
$mailto_array = explode(",", $mailto);
if (!is_array($mailto_array)) {
return null;
}
//mb_internal_encoding("UTF-8");
//$subject = mb_decode_mimeheader($subject);
events("amavis_logger():: Delete id <{$message_id}> in mysql");
if ($message_id != null) {
deleteid_from_messageid($message_id, $q);
}
if ($message_id == null) {
$message_id = md5($ini->toString());
}
events("amavis_logger():: Start loop for Recipients number=" . count($mailto_array) . " id=<{$message_id}>");
$bounce_error = null;
if ($bounce_error == null) {
if ($infected == 1) {
$bounce_error = "INFECTED";
}
}
if ($bounce_error == null) {
if ($banned == 1) {
$bounce_error = "BANNED";
}
}
if ($bounce_error == null) {
if ($spammy == 1) {
$bounce_error = "SPAM";
}
}
if ($bounce_error == null) {
if ($spam == 1) {
$bounce_error = "SPAM";
}
}
if ($bounce_error == null) {
if ($blacklisted == 1) {
$bounce_error = "BLACKLISTED";
}
}
if ($bounce_error == null) {
if ($whitelisted == 1) {
$bounce_error = "WHITELISTED";
}
}
if ($bounce_error == null) {
if ($kas > 90) {
$bounce_error = "KAS3";
}
}
if ($bounce_error == null) {
$bounce_error = "PASS";
}
$prefix = "INSERT INTO amavis_event (`from`,`from_domain`,`to`,`to_domain`,`subject`,`size`,`bounce_error`,`country`,`city`,`zDate`,`ipaddr`) VALUES";
$subject = addslashes($subject);
$inserted_number = 0;
while (list($num, $destataire) = each($mailto_array)) {
$destataire = trim($destataire);
if ($message_id == null) {
continue;
}
if ($destataire == null) {
continue;
}
if (preg_match('#(.+?)@(.+)#', $destataire, $re)) {
$domain_to = $re[2];
}
$inserted_number++;
events("amavis_logger():: {$time_amavis} {$message_id} rcpt=<{$destataire}> From=<{$mailfrom}> {$bounce_error} Geo:{$Country}/{$City}");
$f[] = "('{$mailfrom}','{$domain_from}','{$destataire}','{$domain_to}','{$subject}','{$size}','{$bounce_error}','{$Country}','{$City}','{$time_amavis}','{$smtp_sender}')";
}
$sql = $prefix . " " . @implode(",", $f);
$q->QUERY_SQL($sql, "artica_events");
if (!$q->ok) {
events("amavis_logger():: FAILED {$sql}");
return null;
}
events("amavis_logger():: DELETE {$fullpath}");
if (!@unlink("{$fullpath}")) {
events("amavis_logger():: WARNING UNABLE TO DELETE " . basename($fullpath));
}
}
function visited_sites()
{
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$oldpid = @file_get_contents($pidfile);
if ($oldpid < 100) {
$oldpid = null;
}
$unix = new unix();
if ($unix->process_exists($oldpid)) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$oldpid}\n";
}
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
$t1 = time();
$sql = "SELECT sitename,country,category FROM visited_sites";
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
$num_rows = mysql_num_rows($results);
if ($num_rows == 0) {
if ($GLOBALS["VERBOSE"]) {
echo "No datas " . __FUNCTION__ . " " . __LINE__ . "\n";
}
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$country = null;
$array = _visited_sites_calculate($ligne["sitename"]);
if (!is_array($array)) {
continue;
}
if (trim($ligne["country"] == null)) {
$array_country = GeoIP($ligne["sitename"]);
}
if (isset($array_country)) {
if (isset($array_country[0])) {
$country = $array_country[0];
}
}
if ($country != null) {
$country = ",country='" . addslashes($country) . "'";
}
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["sitename"]} {$array[0]} hits, {$array[1]} size Country '{$country}' on {$array[2]} tables\n";
}
$categories = $GLOBALS["Q"]->GET_CATEGORIES($ligne["sitename"], true);
$categories = addslashes($categories);
$sql = "UPDATE visited_sites SET HitsNumber='{$array[0]}',Querysize='{$array[1]}'{$country},category='{$categories}' WHERE sitename='{$ligne["sitename"]}'";
$GLOBALS["Q"]->QUERY_SQL($sql);
}
$took = $unix->distanceOfTimeInWords($t1, time());
writelogs_squid("Scanned {$num_rows} visisted websites {$took}", __FUNCTION__, __FILE__, __LINE__);
}
function squid_events_sites(){
$q=new mysql();
if($GLOBALS["VERBOSE"]){echo "Empty table...\n";}
$sql="TRUNCATE TABLE `squid_events_sites`";
$q->QUERY_SQL($sql,"artica_events");
$sql="SELECT SUM(hits) as thits, SUM(size) as tsize, websites,category,country FROM squid_events_clients_sites GROUP BY websites,category,country";
$results=$q->QUERY_SQL($sql,"artica_events");
if(!$q->ok){
$unix=new unix();
$unix->send_email_events("Error: Web Proxy Category statistics (squid_events_sites)","Errors:
$sql\n$q->mysql_error\nTable squid_events_sites, database artica_events
server:$q->mysql_server:$q->mysql_port
user:$q->mysql_admin
","proxy");
return;
}
if($GLOBALS["VERBOSE"]){echo mysql_num_rows($results) ." rows\n";}
$prefix="INSERT IGNORE INTO squid_events_sites (website,hits,size,category,country) VALUES ";
$already=array();
while($ligne=@mysql_fetch_array($results,MYSQL_ASSOC)){
if(!isset($already[$ligne["websites"]])){
$country=addslashes($ligne["country"]);
if(is_numeric($country)){$country=null;}
if($country==null){
$country_array=GeoIP($ligne["websites"]);$country=$country_array[0];
if($country<>null){
$sql="UPDATE squid_events_clients_sites SET `country`='".addslashes($country)."' WHERE websites='{$ligne["websites"]}'";
$q->QUERY_SQL($sql,"artica_events");
}
}
if($country==null){$country_array=GeoIP("www.".$ligne["websites"]);$country=$country_array[0];}
if(trim($ligne["category"])==null){$ligne["category"]=GetCategory($ligne["websites"]);}
if(preg_match("#^www\.(.+?)$#i",trim($ligne["websites"]),$re)){$ligne["websites"]=$re[1];}
$country=addslashes($country);
$qs[]="('{$ligne["websites"]}','{$ligne["thits"]}','{$ligne["tsize"]}','{$ligne["category"]}','$country')";
$already[$ligne["websites"]]=true;
}
if(count($qs)>500){
$sql=$prefix.@implode(",",$qs);
$q->QUERY_SQL($sql,"artica_events");
$qs=array();
}
}
if(count($qs)>0){
$sql=$prefix.@implode(",",$qs);
$q->QUERY_SQL($sql,"artica_events");
$qs=array();
}
}
function visited_sites()
{
if ($GLOBALS["VERBOSE"]) {
$GLOBALS["FORCE"] = true;
}
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
$pid = @file_get_contents($pidfile);
if ($pid < 100) {
$pid = null;
}
$unix = new unix();
if ($unix->process_exists($pid)) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
if (!$GLOBALS["FORCE"]) {
$timeOfFile = $unix->file_time_min($timefile);
if ($timeOfFile < 240) {
if ($GLOBALS["VERBOSE"]) {
echo "{$timeOfFile}Mn,require 240Mn\n";
}
return;
}
}
@unlink($timefile);
@file_put_contents($timefile, time());
squid_status_sub_percentage("Running query", 5);
$t1 = time();
if (!$GLOBALS["Q"]->TABLE_EXISTS("visited_sites_catz")) {
$GLOBALS["Q"]->CheckTables();
}
squid_status_sub_percentage();
$sql = "SELECT sitename,country,category FROM visited_sites WHERE LENGTH(category)=0 LIMIT 0,1500";
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
$num_rows = mysql_num_rows($results);
events("visited_sites(): {$num_rows} items");
stats_admin_events(2, "visited_sites {$num_rows} items", null, __FILE__, __LINE__);
if ($num_rows == 0) {
squid_status_sub_percentage("Done", 100);
if ($GLOBALS["VERBOSE"]) {
echo "No datas " . __FUNCTION__ . " " . __LINE__ . "\n";
}
return;
}
visited_sites_percentage("{$num_rows} entries");
if ($GLOBALS["VERBOSE"]) {
echo "{$num_rows} entries... in " . __FUNCTION__ . " " . __LINE__ . "\n";
}
squid_status_sub_percentage("{$num_rows} entries... in", 10);
$ROWS_visited_sites_catz = array();
$ROWS_visited_sites_prefix = "INSERT IGNORE INTO visited_sites_catz (`zmd5`,`category`,`familysite`) VALUES ";
$z = 0;
$MinutesFile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".minutes";
@unlink($MinutesFile);
@file_put_contents($MinutesFile, time());
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$z++;
$perc = round($z / $num_rows) * 100;
$country = null;
$FamilySite_update = null;
$array = _visited_sites_calculate($ligne["sitename"]);
if (!is_array($array)) {
continue;
}
if (trim($ligne["country"] == null)) {
$array_country = GeoIP($ligne["sitename"]);
}
if (isset($array_country)) {
if (isset($array_country[0])) {
$country = $array_country[0];
}
}
if ($country != null) {
$country = ",country='" . addslashes($country) . "'";
}
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["sitename"]} {$array[0]} hits, {$array[1]} size Country '{$country}' on {$array[2]} tables\n";
}
$TimeLoop = $unix->file_time_min($MinutesFile);
if ($TimeLoop > 4) {
if (count($ROWS_visited_sites_prefix) > 0) {
$GLOBALS["Q"]->QUERY_SQL($ROWS_visited_sites_prefix . @implode(",", $ROWS_visited_sites_catz));
$ROWS_visited_sites_catz = array();
}
visited_sites_percentage("{$perc}% {$z}/{$num_rows} rows");
stats_admin_events(2, "{$perc}% {$z}/{$num_rows} rows", null, __FILE__, __LINE__);
if (SquidStatisticsTasksOverTime()) {
stats_admin_events(1, "Statistics overtime... Aborting", null, __FILE__, __LINE__);
return;
}
@unlink($MinutesFile);
@file_put_contents($MinutesFile, time());
}
$categories = $GLOBALS["Q"]->GET_CATEGORIES($ligne["sitename"], true);
if ($categories != null) {
if ($GLOBALS["VERBOSE"]) {
echo "UPDATE {$ligne["sitename"]} ->{$categories}\n";
}
$GLOBALS["Q"]->QUERY_SQL("UPDATE visited_sites SET category='{$categories}' WHERE sitename='{$ligne["sitename"]}'");
if (!$GLOBALS["Q"]->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "UPDATE {$GLOBALS["Q"]->mysql_error}\n";
}
}
}
squid_status_sub_percentage("visited_sites();{$z}/{$num_rows} entries... in", $perc);
$FamilySite = $GLOBALS["Q"]->GetFamilySites($ligne["sitename"]);
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["sitename"]} categories = {$categories}\n";
}
thumbnail_site($ligne["sitename"]);
if ($FamilySite != null) {
$FamilySite_update = ",familysite='{$FamilySite}'";
if (strpos(" {$categories}", ",") > 0) {
$bb = explode(",", $categories);
while (list($hh, $ii) = each($bb)) {
if (trim($ii) == null) {
continue;
}
$mmd5 = md5("{$FamilySite}{$ii}");
$ROWS_visited_sites_catz[] = "('{$mmd5}','{$FamilySite}','{$ii}')";
}
} else {
$mmd5 = md5("{$FamilySite}{$categories}");
$ROWS_visited_sites_catz[] = "('{$mmd5}','{$FamilySite}','{$categories}')";
}
if (count($ROWS_visited_sites_catz) > 1500) {
if ($GLOBALS["VERBOSE"]) {
echo "visited_sites_catz:: 1500\n";
}
$GLOBALS["Q"]->QUERY_SQL($ROWS_visited_sites_prefix . @implode(",", $ROWS_visited_sites_catz));
$ROWS_visited_sites_catz = array();
}
}
$categories = addslashes($categories);
$sql = "UPDATE visited_sites SET HitsNumber='{$array[0]}',Querysize='{$array[1]}'{$country},category='{$categories}'{$FamilySite_update} WHERE sitename='{$ligne["sitename"]}'";
$GLOBALS["Q"]->QUERY_SQL($sql);
}
if (count($ROWS_visited_sites_catz) > 0) {
$GLOBALS["Q"]->QUERY_SQL($ROWS_visited_sites_prefix . @implode(",", $ROWS_visited_sites_catz));
$ROWS_visited_sites_catz = array();
}
$took = $unix->distanceOfTimeInWords($t1, time());
ufdbguard_admin_events("Scanned {$num_rows} visisted websites {$took}", __FUNCTION__, __FILE__, __LINE__, "visited");
visited_sites_whois();
WeekDaysNums();
}
function query()
{
$unix = new unix();
$sql = "SELECT email FROM postfix_viptrack WHERE `enabled`=1 ORDER BY email";
$q = new mysql();
$results = $q->QUERY_SQL($sql, "artica_backup");
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo $q->mysql_error;
}
$unix->send_email_events("VipTrack failed MySQL Error", $q->mysql_error, "postfix");
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
if ($ligne["email"] == null) {
continue;
}
$array_inbound[] = "(delivery_user='******')";
$array_outbound[] = "(sender_user='******')";
}
if (count($array_outbound) == 0) {
if ($GLOBALS["VERBOSE"]) {
echo "No users to check (LINE:" . __LINE__ . ")\n";
}
return;
}
$rquested[] = "`time_connect`";
$rquested[] = "`time_stamp`";
$rquested[] = "`sender_domain`";
$rquested[] = "`delivery_domain`";
$rquested[] = "`delivery_user`";
$rquested[] = "`sender_user`";
$rquested[] = "`smtp_sender`";
$rquested[] = "`bounce_error`";
$sql = "SELECT `zDate` FROM viptrack_connections ORDER BY `zDate` DESC LIMIT 0 , 1";
$ligne = mysql_fetch_array($q->QUERY_SQL($sql, "artica_events"));
$q_one = "1";
if ($ligne["zDate"] != null) {
if ($GLOBALS["VERBOSE"]) {
echo "No users to check\n";
}
$q_one = "`time_connect`>'{$ligne["zDate"]}'";
}
$sql_inbound = "SELECT " . @implode(",", $rquested) . " FROM smtp_logs WHERE {$q_one} AND (" . @implode(" OR ", $array_inbound) . ") ORDER BY time_connect";
$sql_outbound = "SELECT " . @implode(",", $rquested) . " FROM smtp_logs WHERE {$q_one} AND (" . @implode(" OR ", $array_outbound) . ") ORDER BY time_connect";
$results = $q->QUERY_SQL($sql_inbound, "artica_events");
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo $q->mysql_error;
}
$unix->send_email_events("VipTrack failed MySQL Error", $q->mysql_error . "\n{$sql}", "postfix");
return;
}
$prefix = "INSERT INTO viptrack_connections (`zmd5`,`domain_from`,`domain_to`, `from`,`to`, `zDate`, `bounce_error`,`smtp_sender`,`Country`) VALUES ";
$cti = 0;
if (mysql_num_rows($results) > 0) {
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$c = array();
while (list($a, $b) = each($ligne)) {
$c[] = $b;
}
$md5 = md5(@implode("", $c));
$ligne["delivery_domain"] = strtolower($ligne["delivery_domain"]);
$ligne["sender_domain"] = strtolower($ligne["sender_domain"]);
$ligne["delivery_user"] = strtolower($ligne["delivery_user"]);
$ligne["sender_user"] = strtolower($ligne["sender_user"]);
if ($ligne["delivery_user"] == null) {
$ligne["delivery_user"] = "******";
}
if ($ligne["sender_user"] == null) {
$ligne["sender_user"] = "******";
}
if ($ligne["delivery_domain"] == null) {
$ligne["delivery_domain"] = "undisclosed";
}
if ($ligne["sender_user"] == null) {
$ligne["sender_user"] = "******";
}
if ($ligne["sender_domain"] == null) {
$ligne["sender_domain"] = "undisclosed";
}
$country = GeoIP($ligne["smtp_sender"]);
if (!isset($country[0])) {
$country[0] = "unknown";
}
$sq[] = "('{$md5}','{$ligne["sender_domain"]}','{$ligne["delivery_domain"]}','{$ligne["sender_user"]}','{$ligne["delivery_user"]}',\n\t\t\t'{$ligne["time_connect"]}','{$ligne["bounce_error"]}','{$ligne["smtp_sender"]}','{$country[0]}')";
if (count($sq) > 500) {
$cti = $cti + 500;
if ($GLOBALS["VERBOSE"]) {
echo "INBOUND:{$cti}\n";
}
$sql = $prefix . " " . @implode(",", $sq);
unset($sq);
$q->QUERY_SQL($sql, "artica_events");
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo $q->mysql_error;
}
$unix->send_email_events("VipTrack failed MySQL Error " . __LINE__, $q->mysql_error . "\n{$sql}", "postfix");
return;
}
}
}
if (count($sq) > 0) {
$cti = $cti + count($sq);
if ($GLOBALS["VERBOSE"]) {
echo "INBOUND:{$cti}\n";
}
$sql = $prefix . " " . @implode(",", $sq);
unset($sq);
$q->QUERY_SQL($sql, "artica_events");
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo $q->mysql_error;
}
$unix->send_email_events("VipTrack failed MySQL Error " . __LINE__, $q->mysql_error . "\n{$sql}", "postfix");
return;
}
}
}
//----------------------------------------------------------------------------------------------------------------------------------------------
$results = $q->QUERY_SQL($sql_outbound, "artica_events");
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo $q->mysql_error;
}
$unix->send_email_events("VipTrack failed MySQL Error (outbound) " . __LINE__, $q->mysql_error . "\n{$sql}", "postfix");
return;
}
if (mysql_num_rows($results) > 0) {
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$c = array();
while (list($a, $b) = each($ligne)) {
$c[] = $b;
}
$md5 = md5(@implode("", $c));
$ligne["delivery_domain"] = strtolower($ligne["delivery_domain"]);
$ligne["sender_domain"] = strtolower($ligne["sender_domain"]);
$ligne["delivery_user"] = strtolower($ligne["delivery_user"]);
$ligne["sender_user"] = strtolower($ligne["sender_user"]);
$ligne["delivery_user"] = strtolower($ligne["delivery_user"]);
if ($ligne["delivery_user"] == null) {
$ligne["delivery_user"] = "******";
}
if ($ligne["sender_user"] == null) {
$ligne["sender_user"] = "******";
}
if ($ligne["delivery_domain"] == null) {
$ligne["delivery_domain"] = "undisclosed";
}
if ($ligne["sender_user"] == null) {
$ligne["sender_user"] = "******";
}
if ($ligne["sender_domain"] == null) {
$ligne["sender_domain"] = "undisclosed";
}
$country = GeoIP($ligne["smtp_sender"]);
if (!isset($country[0])) {
$country[0] = "unknown";
}
$sq[] = "('{$md5}','{$ligne["sender_domain"]}','{$ligne["delivery_domain"]}','{$ligne["sender_user"]}','{$ligne["delivery_user"]}',\n\t\t\t'{$ligne["time_connect"]}','{$ligne["bounce_error"]}','{$ligne["smtp_sender"]}','{$country[0]}')";
if (count($sq) > 500) {
$cti = $cti + 500;
if ($GLOBALS["VERBOSE"]) {
echo "OUTBOUND:{$cti}\n";
}
$sql = $prefix . " " . @implode(",", $sq);
unset($sq);
$q->QUERY_SQL($sql, "artica_events");
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo $q->mysql_error;
}
$unix->send_email_events("VipTrack failed MySQL Error (outbound) " . __LINE__, $q->mysql_error . "\n{$sql}", "postfix");
return;
}
}
}
if (count($sq) > 0) {
$cti = $cti + count($sq);
if ($GLOBALS["VERBOSE"]) {
echo "OUTBOUND:{$cti}\n";
}
$sql = $prefix . " " . @implode(",", $sq);
unset($sq);
$q->QUERY_SQL($sql, "artica_events");
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo $q->mysql_error;
}
$unix->send_email_events("VipTrack failed MySQL Error (outbound) " . __LINE__, $q->mysql_error . "\n{$sql}", "postfix");
return;
}
}
}
}
