Archiving bounced email attachments

I have created a simple php script to archive all email attachments that have been imported as bounced mails from email campaigns. The attachments are saved in /upload as all email attachments are. imho they serve no further purpose, so I’d like to save some web server space and get rid of them.

So the script queries the DB for all emails with the intent “bounce”, grabs the related note.id and moves the respective attachment files to a new folder, in my example /upload_archive.
I can then compress, download and delete these files from there.

This file need to be moved to a folder that allows web access to it directly.

I’m grateful for anyone who feels like adapting this script to:

  • work only when logged in to SuiteCRM (instead of using a GET parameter as added security layer)
  • add a web form to interatively set parameters on the page instead of using GET parameters.
  • write an admin panel from which to control and launch this script
  • any other fancy stuff…

And someone can tell me whether the bounce email records in the database serve any use, I’d be interested. I don’t yet understand whether they are used only once, to compile the campaign log statistics, or whether they need to remain in the system indefinitely for the campaign log to function properly.

Couldn’t get file upload to work, so I’ll paste the code in here:

<?php

/*
* Published under License CC-BY-SA
*
* John Dickinson, Energie Zukunft Schweiz - j.dickinson@ezs.ch
* April 2018
*
* example URL for listing files: https://crm-url.com/scriptfolder/scriptfilename.php?auth_key=1234&mode=list&limit=100
* example URL for archiving files: https://crm-url.com/scriptfolder/scriptfilename.php?auth_key=1234&mode=archive&limit=none
*/


# params
$limit_default = 10; # defauilt SQL query limit
$auth_key = "1234"; #auth_key that must be given as GET parameter for script to be executed.

# database params
$db_name = "crm"; # database name
$db_user = "username";
$db_pass = "password";

$sourcedir = "/var/www/crm_folder/upload/";     # path to source directory
$targetdir = "/var/www/crm_folder/upload_archive/";     # path to target directory


# GET parameters
$limit = $_GET['limit'] ? $_GET['limit'] : $limit_default; # to run without limit, use '?limit=nolimit'
$movefiles = ($_GET['mode'] == "archive") ? true : false; # copy files or only print report



# check if target folder exists, else die()
if (!file_exists($targetdir)) {

  die("target folder doesn't exist => first manually create folder " . $targetdir); # PM: mkdir doesn't work (permissions on crm folder?)

}


# security: don't execute script unless auth_key given as GET parameter
$die_msg = "<html><head><title>Testpage</title></head>";
$die_msg .= "<body>No Hay Banda!</body>";
$die_msg .= "</html>";

if ($_GET['auth_key'] != $auth_key) {
  die($die_msg);
}


# connect to DB
$dblink = dbConnect($db_name, $db_user, $db_pass);


# create SQL query
if ($limit == "none") {

  $sql = "SELECT n.id, n.parent_type, e.name, e.date_entered FROM notes AS n JOIN emails AS e ON n.parent_id = e.id AND e.intent = 'bounce'";  #no limit
  $sql .= " ORDER BY e.date_entered ASC";

} else {

  $sql = "SELECT n.id, n.parent_type, e.name, e.date_entered FROM notes AS n JOIN emails AS e ON n.parent_id = e.id AND e.intent = 'bounce'";
  $sql .= " ORDER BY e.date_entered ASC";
  $sql .= " LIMIT " . $limit; # limit row number as given in GET parameter
}


$sql_query = mysql_query($sql);


# start looping through file IDs
$counter = 1;
while ($data = mysql_fetch_assoc($sql_query)) {

  #move file and save status - we can manually delete files in seperate folder afterwards
  $sourcefile = $sourcedir . utf8_encode($data["id"]);
  $targetfile = $targetdir . utf8_encode($data["id"]);

  # if source file present in folder
  if (file_exists($sourcefile)) {

    $sourcefilesize = filesize($sourcefile)/1000; # filesize in KB
    $filedate = date("d.m.Y", filemtime($sourcefile)); # get file modified time

    if ($movefiles) { # read GET mode parameter
        $movestatus = rename($sourcefile, $targetfile) ? "success" : "failure";
      } else {
        $movestatus = "report only";
    }


  } elseif (file_exists($targetfile)) {

    $targetfilesize = filesize($targetfile)/1000; # filesize in KB
    $filedate = date("d.m.Y", filemtime($targetfile)); # get file modified time
    $movestatus = "file already moved to target directory";

  } else {

      # if file can't be found
      $sourcefilesize = 0;
      $filedate = "n/a";
      $movestatus = "file can't be found";

  }



  # populate table row
  $tablerows .= "<tr>";
  $tablerows .= "<td>" . $counter . "</td>";
  $tablerows .= "<td>" . utf8_encode($data["parent_type"]) . "</td>";
  $tablerows .= "<td>" . utf8_encode($data["name"]) . "</td>";
  $tablerows .= "<td><nobr>" . date("d.m.Y", strtotime(utf8_encode($data["date_entered"]))) . "</nobr></td>";
  $tablerows .= "<td>" . utf8_encode($data["id"]) . "</td>";
  $tablerows .= "<td>" . $movestatus . "</td>";
  $tablerows .= "<td>" . max(round($sourcefilesize/1000, 1), round($targetfilesize/1000, 1)) . "MB </td>";
  $tablerows .= "<td><nobr>" . $filedate . "</nobr></td>";
  $tablerows .= "<td>" . $sourcefile . "</td>";
  $tablerows .= "<td>" . $targetfile . "</td>";
  $tablerows .= "</tr>";


  $counter++;

}



# output html
echo "<html><head><title>CRM Archive Script: All attachments from bounced email</title></head>";
echo "<body>";
echo "<h3>Parameters used</h3>";
echo "<div>File Archive Mode: " . ($movefiles ? "Archive" : "List only (default)") . "</div>";
echo "<div>Email intent filter: 'bounce'</div>";
echo "<div>Database Query LIMIT: " . $limit . ($limit == 10 ? " (default)" : "") . "</div>";
echo "<br />";
echo "<div>potential total filesize: " . $sourcefilesize_sum . "MB.<br />Total filesize already moved: " . $targetfilesize_sum . "MB</div><br /><br />";

# echo data table
echo "<h3>Results</h3>";
echo tableCreateHeader(); # populate table title bar (function)
echo $tablerows;
echo "</tbody></table>";

echo "</body></html>";


?>




<?php ### functions

# function: create title bar for table
function tableCreateHeader () {

  $html .= "<table><tbody><tr>";
  $html .= "<th>counter</th>";
  $html .= "<th>parent type</th>";
  $html .= "<th>email name</th>";
  $html .= "<th>date_entered</th>";
  $html .= "<th>ID</th>";
  $html .= "<th>archiving status</th>";
  $html .= "<th><nobr>file size</nobr></th>";
  $html .= "<th><nobr>file date</nobr></th>";
  $html .= "<th>sourcefile path</th>";
  $html .= "<th>targetfile path</th>";
  $html .= "</tr>";

  return $html;
}


# DB connection
function dbConnect($db, $user, $pass) {

   $link = mysql_connect("localhost", $user, $pass);
    if (!$link) {
      die('Connection failed: ' . mysql_error());
    }

    $db_selected = mysql_select_db($db, $link);
    if (!$db_selected) {
        die ('Cannot use ' . $db . mysql_error());
    }
    return $link;
}

?>

Hi John (and others)

I have the exact same question and that’s how I found your topic as a result of a search. We are using Notes extensively but the “Delivery Status Notifications” with the “bounce.eml” attachments are getting in the way.

I’ve paraphrased the original questions here:

Does anyone know if “the bounce email records in the database serve any use” after they’ve been processed?

Do they “need to remain in the system indefinitely for the campaign log to function properly”?

Can we delete them once they’ve been processed by the Campaign?

TIA