By Toledoh - March 8, 2013 - edited: March 8, 2013

Hi Guys,

Just playing around, no specific project...  is it possible to use the dowloadMail plugin but have it go to a section other than incoming mail?

I have a section "to_do" that is basically an action list.  I wish to be able to send an email to to_do@XXXXXX.com and it to be added to my to_do section. 

Also (not important) can I have multiple email addresses go to multiple sections?

Finally - at a real push.  Would it be possible to add some kind of patterns to the subject line that adds custom fields. 

ie Subject:  Chase invoice ABC ##high##

-  title field = Chase invoice ABC, and
 - priority field = high

Cheers,

Tim Forrest
Toledoh Enterprises
www.toledoh.com.au

Hi Tim, 

Great questions. Download Mail takes care of the first step of downloading the mail and getting it into MySQL.  Once it's in MySQL we can forget about the fact that they're emails and just think of it as records in the database.

So for the todo example, the next step would be to write some code to move the records from the incoming mail table.  So some pseudo-code (untested) for that might be something like this:

// process todo emails
$todoEmails = mysql_select('incoming_mail', " to = 'todo@example.com ");
foreach ($todoEmails as $message) {

  // add to todos
  mysql_insert("todo_section", array(
    'todo_name' => $message['subject'],
    'todo_desc' => $message['text'],
  ));

  // remove from incoming mail
  mysql_delete('incoming_mail', $message['num']);
}

And you'd run that after /downloadMail/run.php or on a cron job every few minutes.

Finally - at a real push.  Would it be possible to add some kind of patterns to the subject line that adds custom fields. 

ie Subject:  Chase invoice ABC ##high##

-  title field = Chase invoice ABC, and 
 - priority field = high

Absolutely, all that's needed is a few lines of PHP code to match the patterns and then change the fields.  The way I do this is to write out examples and instructions on how to process the subject lines so that a human can do it.  (If it contains "this" do that, If it contains "the other thing" do something else, and if it doesn't match any of the rules then do something else).  It's then pretty quick to turn it into PHP code.

Hope that helps, let me know any questions.

Dave Edis - Senior Developer
interactivetools.com

Hey Dave,


Thanks for this.

I've added that code to the run.php, but I get an error: MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to = 'todo@example.com'' at line 1 (at old_alias_functions.php line 142 in function mysql_select_query)

I've played with various combinations of ' and " but think it must be something else?

$todoEmails = mysql_select('incoming_mail', " to = 'todo@example.com' ");

Cheers,

Tim Forrest
Toledoh Enterprises
www.toledoh.com.au

Hi Tim,

That was untested mockup code so there will probably be a few issues, but I think that one is because the "Download Mail" fieldname of "to" is actually a reserved keyword in MySQL: http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html

MySQL is picky about us naming fields things like select, insert, etc.  Try adding `backticks` around it to let MySQL know that we mean it to be a fieldname, eg:

$todoEmails = mysql_select('incoming_mail', " `to` = 'todo@example.com' ");

Let me know if you run into any more issues.

PS:  If you want to use Download Mail on a project shortly, let me know and I can convert it to use the new "Background Tasks" cronjob system so you don't need to setup multiple cronjobs.  We're going to be porting everything over to that new system as we move forward.

Dave Edis - Senior Developer
interactivetools.com

Woo hoo.  done and working.

For anyone else interested, I also had to create all the default bits.

  // add to todos
  mysql_insert("to_do", array(
    'title' => $message['subject'],
    'content' => $message['text'],
    'createdDate' => $message['createdDate'],
    'updatedDate' => $message['createdDate'],
    'date_due' => $message['createdDate'],
    'createdByUserNum' => '1',
    'updatedByUserNum' => '1',
    'hidden' => '0',
  ));

 Now onto the pattern matching:)

Thanks Dave.

Cheers,

Tim Forrest
Toledoh Enterprises
www.toledoh.com.au

Some good stuff for the downoadMail plugin. Here's a question of my own:

I want to filter the mail records into an array - but only if the "from" email matches the email of a registered user in the users database?

The idea is that I am using DLMail to provide a feedback/letters page. I want all submissions from registered users to go live on the site immediatey - whereas any messages from unknown users would need to be "approved" first.

:0)

Perchpole

Hi Perch,

It's basically a custom PHP/MySQL question.  There's a few ways to do it.  Here's some (untested) code that processes one incoming email at a time: 

  // process next email
  $email = mysql_get('_incoming_mail'); // get next email
  if ($email) {
    print "Processing next email...<br/>\n";
    $fromMatchesMember = mysql_count('accounts', array('email' => $email['from']));

    // for emails from members, do this:
    if ($fromMatchesMember) {
      print "<h1>This email IS from a member!</h1>";
      showme($email);
      // add further processing here
    }

    // if email isn't from member, do this:
    else {
      print "<h1>This email is *NOT* from a member!</h1>";
      showme($email);
      // add further processing here
    }

    // once finished with email, delete it from queue
    //mysql_delete('_incoming_mail', $email['num']);
  }

  print "done!";
  exit;

If you can extend that to do what you need you could then run it from a cronjob so it processed emails as they came in.  I've got the delete code commented out for testing, but once you've got it working you can uncomment that.

You might create a new article with something like this:

  $colsToValues = array();
  $colsToValues['title']   = $email['subject'];
  $colsToValues['content'] = $email['html'];
  mysql_insert('articles', $colsToValues, true);

Hope that helps, let me know any questions.

Dave Edis - Senior Developer
interactivetools.com

Hi, Dave -

Thanks for the code. It's a bit more complex than I had imagined! I'm looking for a solution I can apply before looping the results, like this:

<?php foreach($incoming_mail as $mail): ?>
<?if(incoming_mail "from" does not match accounts "email") {continue}; ?>
<?php echo $mail['subject'] ?>
<?php echo $mail['text'] ?>
etc...
<?php endforeach ?>

If the "from" email does not match an email in the accounts table it means the mail has been sent from an unregistered user. In this eventuality the foreach loop continues to the next row.

Does that makes sense?

:oS

Perch