DownloadMail

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 (toledoh.com.au)

By Toledoh - March 10, 2013

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 (toledoh.com.au)

By Dave - March 10, 2013

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

By Toledoh - March 10, 2013

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 (toledoh.com.au)

By Perchpole - March 19, 2013

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

By Dave - March 20, 2013

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

By Perchpole - March 21, 2013

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

By Dave - March 24, 2013

Hi Perch, 

Ok, sure.  Here's some code to create an array of emails from the accounts table:

<?php
// get account emails
  $allAccounts = mysql_select('accounts');
  $allEmails = array_pluck($allAccounts, 'email');
  // showme($allEmails);
?>

And here's some to filter results based on that list:

<?php foreach ($incoming_mail as $mail): ?>
  <?php if (!in_array($incoming_mail['from'], $allEmails) ( continue; } // skip if email doesn't match ?>
  <?php echo $mail['subject'] ?><br/>
  <?php echo $mail['text'] ?><br/><br/>
<?php endforeach ?>

There's lots of issues to consider, like what happens if there's thousands of users (loading the email list might use a lot of memory), or what happens if spam or other emails start to build up in the mail table (eventually it would slow things down), or if users email from their home emails, etc, or names don't exactly match.  But the above code should help get you started.

Hope that helps!

Dave Edis - Senior Developer

interactivetools.com