|
leftover when bar closes
Registriert seit: 29.06.2006
Ort: Bern
Beiträge: 123
|
Hey Bruchpilot
Ich habe vor einiger Zeit dasselbe programmiert. Fuer die Korrespondenz mit unseren Kunden holt ein durch crons ausgefuehrtes Script die Mails via IMAP ab, speichert saemtliche Infos inkl. Anhaengen auf dem Server (als file bzw. das Mail in der Datenbank) und wir administrieren alles ueber ein Web-Frontend.
Was die Geschwindigkeit anbelangt, so faherst du so bestimmt besser. hier kommt es natuerlich auf die Geschwindigkeit deines Servers drauf an, wie schnell er die Mails aus der Datenbank holt und die Seiten aufbereitet... Wir mit unserem Standard-Server haben jedenfalls keine Muehe, 1000 Mails auf einmal anzuzeigen...
Das folgende Script ist die erste Version und enthaelt noch vereinzelt Bugs, besonders in der Dekodierung der Mails. Es sollte dir jedoch fuer einen Start helfen...
Gruss aus Brasilien 
dsxs
PHP-Code:
<pre>
<?php
error_reporting('ALL');
/**
* imap2sql / TZ2
*
* Type: script
* Name: email2sql
* Date: June 6., 2006
* Purpose: script for getting email to SQL database
* @version 1.0 @ June 6., 2006
*
* Change Log:
* - 1.0 initial release
**/
// set execution-time start
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;
// set up runtime arrays
$my_config = array(); // configuration parameters
$my_mail = array(); // mail parameters for each mail looped
$my_master = array(); // script master variables
// set configuration settings
$my_config['debug'] = TRUE; // print debug
$my_config['sendmail'] = FALSE; // send copy of each email to given adress
$my_config['sendmail_to'] = 'mail@host.tld';
$my_config['display_content'] = TRUE; // display email content during runtime.
$my_config['delete_mail'] = TRUE; // delete mail from IMAP box after storing to database
$my_config['move_mail'] = ''; // if other than "", mails will be moved to given folder
$my_config['writetoDB'] = TRUE; // save email to database
$my_config['folder'] = 'inbox'; // store mail in virtual folder (e.g. archiv, bestellungen...)
$my_config['save_attachments'] = TRUE; // save mail attachments on server
$my_config['send_invalidattach_mail'] = TRUE; // reply to email origin if attachment type is not accepted
$my_config['sql']['host'] = "localhost";
$my_config['sql']['user'] = "user";
$my_config['sql']['pass'] = "pass";
$my_config['sql']['db'] = "dbname";
$my_config['IMAP']['host'] = "{localhost:143/notls}";
$my_config['IMAP']['user'] = "mail@host.tld";
$my_config['IMAP']['pass'] = "pass";
$my_config['IMAP']['folder'] = "INBOX";
// set up debug function
function debug($string) {
global $my_config;
if ($my_config['debug']) {
echo $string;
echo "\n\r<br>";
return TRUE;
}
}
// set up randomizer
function random($length=6) {
$seeds = 'abcdefghijklmnopqrstuvwxyz123456789';
$seeds_count = strlen($seeds);
// seed
list($usec, $sec) = explode(' ', microtime());
$seed = (float) $sec + ((float) $usec * 100000);
mt_srand($seed);
// generate
for ($i = 0; $length > $i; $i++) {
$str .= $seeds{mt_rand(0, $seeds_count - 1)};
}
return $str;
}
// set up object to full-array converter
function object_to_array($the_object) {
$the_array=array();
if(!is_scalar($the_object)) {
foreach($the_object as $id => $object) {
if(is_scalar($object)) {
$the_array[$id]=$object;
} else {
$the_array[$id]=object_to_array($object);
}
}
return $the_array;
} else {
return $the_object;
}
}
/* <--> begin script </--> */
/* */
/* */
/* */
// connect to database
if (!$dbh = mysql_connect('localhost','dbuser','dbpass')) {
debug ("mySQL connection failed -> ".mysql_error());
die ("Script terminating");
} else {
debug ("mySQL connection established on localhost");
}
mysql_select_db('dbname',$dbh);
// connect to IMAP mailbox
if (!$mbh = imap_open ($my_config['IMAP']['host'].$my_config['IMAP']['folder'], $my_config['IMAP']['user'], $my_config['IMAP']['pass'])) {
debug("IMAP connection failed -> ".imap_last_error()."");
die("Script terminating!");
} else {
debug("IMAP connection established on ".$my_config['IMAP']['host']."");
}
// get some mailbox information
$imapcheck = imap_check($mbh);
// number of messages in inbox
$my_master['Nmsgs'] = $imapcheck->Nmsgs;
debug($my_master['Nmsgs']." messages in mailbox");
// start looping over each email
$my_master['loopindex'] = 1;
$my_master['mailsdeleted'] = 0;
while ($my_master['loopindex'] <= $my_master['Nmsgs']) {
debug("<b>Analyzing message nr. ".$my_master['loopindex']." <br>-------------------------------------------------</b>");
// start analyzing individual message
// get mail headers
$header = object_to_array(imap_headerinfo($mbh, $my_master['loopindex'],255,255));
if ($my_config['debug']) {
//debug('<b>Header information:</b><br>');
print_r($header);
}
// fetch mail strucutre
$structure = object_to_array(imap_fetchstructure($mbh, $my_master['loopindex']));
if ($my_config['debug']) {
debug('<b>Structure information:</b><br>');
print_r($structure);
}
// assign whole stuff to @my_mail
$my_mail['timestamp'] = $header['udate'];
$my_mail['date'] = date("Y/m/d H:i",$my_mail['timestamp']);
$my_mail['subject'] = $header['subject'];
$my_mail['origin']['host'] = $header['from'][0]['host'];
$my_mail['origin']['box'] = $header['from'][0]['mailbox'];
$my_mail['origin']['from'] = $my_mail['origin']['box'].'@'.$my_mail['origin']['host'];
$my_mail['origin']['from_name'] = $header['from'][0]['personal'];
$my_mail['destination']['host'] = $header['to'][0]['host'];
$my_mail['destination']['box'] = $header['to'][0]['mailbox'];
$my_mail['destination']['to'] = $my_mail['destination']['box'].'@'.$my_mail['destination']['host'];
$my_mail['destination']['to_name'] = $header['to'][0]['personal'];
//$my_mail['reply_to']['host'] = $header['reply_to'][0]['host'];
//$my_mail['reply_to']['box'] = $header['reply_to'][0]['mailbox'];
//$my_mail['reply_to']['reply_to'] = $my_mail['reply_to']['box'].'@'.$my_mail['reply_to']['host'];
//$my_mail['reply_to']['reply_to_name'] = $header['reply_to'][0]['personal'];
//$my_mail['cc'] = $header['ccaddress']; // multiple CC's resp. BCC's possible
//$my_mail['bcc'] = $header['bccaddress'];
// decode the mess
$my_mail['origin']['from'] = utf8_decode(imap_utf8($my_mail['origin']['from']));
$my_mail['origin']['from_name'] = utf8_decode(imap_utf8($my_mail['origin']['from_name']));
$my_mail['destination']['to'] = utf8_decode(imap_utf8($my_mail['destination']['to']));
$my_mail['destination']['to_name'] = utf8_decode(imap_utf8($my_mail['destination']['to_name']));
//$my_mail['replyto']['name'] = utf8_decode(imap_utf8($my_mail['replyto']['name']));
$my_mail['subject'] = utf8_decode(imap_utf8($my_mail['subject']));
$my_mail['subject'] = quoted_printable_decode($my_mail['subject']);
if (ereg("iso-8859-1",$my_mail['subject'])) {
$my_mail['subject'] = html_entity_decode(utf8_decode($my_mail['subject']));
}
if ($my_mail['subject'] == '') {
$my_mail['subject'] = 'Kein Betreff';
}
// clean up the mess
if ($my_mail['origin']['from_name'] == "") {
$my_mail['origin']['from_name'] = $my_mail['origin']['from'];
}
if ($my_mail['destination']['to_name'] == "") {
$my_mail['destination']['to_name'] = $my_mail['destination']['to'];
}
//if ($my_mail['reply_to']['from_name'] == "") {
// $my_mail['reply_to']['reply_to_name'] = $my_mail['reply_to']['reply_to_name'];
//}
// fetch mail body //
$my_mail['type'] = $structure['type']; // Mail type [0=PLAIN / 1=MULTIPART]
$my_mail['encode'] = $structure['encoding']; // Mail encoding
debug('<b>Fetch Mail Body</b><br>');
// type is 0
if ($my_mail['type'] == 0) {
debug('Body Type is PLAIN');
$my_mail['body'] = imap_body($mbh, $my_master['loopindex']);
if ($my_mail['encode'] == 3) {
$my_mail['body'] = base64_decode($my_mail['body']);
}
if ($my_mail['encode'] == 4) {
$my_mail['body'] = quoted_printable_decode($my_mail['body']);
}
// else {
// $my_mail['text'] = quoted_printable_decode($my_mail['text']);
//}
debug($my_mail['text']);
}
// type is 1 (multipart)
if ($my_mail['type'] == 1) {
debug('Body Type is MULTIPART');
$struct = $structure;
$mid = $my_master['loopindex'];
$parts = $struct['parts'];
$i = 0;
$endwhile = false;
$stack = array(); // Stack while parsing message
$content = ""; // Message content
$html = "";
$my_mail['attachment'] = array(); // Attachments
while (!$endwhile) {
if (!$parts[$i]) {
if (count($stack) > 0) {
$parts = $stack[count($stack)-1]["p"];
$i = $stack[count($stack)-1]["i"] + 1;
array_pop($stack);
} else {
$endwhile = true;
}
}
if (!$endwhile) {
// Get message part (1.1.1 eg.)
$partstring = "";
foreach ($stack as $s) {
$partstring .= ($s["i"]+1) . ".";
}
$partstring .= ($i+1);
if (strtoupper($parts[$i]['disposition']) == "ATTACHMENT") { // Part is attachment
$my_mail['attachment'][] = array("filename" => $parts[$i]['parameters'][0]['value'],"filedata" => imap_fetchbody($mbh, $mid, $partstring));
} elseif (strtoupper($parts[$i]['subtype']) == "PLAIN") { // Part is Message
// Part is text, check encoding
if ($parts[$i]['parameters']['0']['value'] == 'utf-8') {
// utf-8 encoded, decode
$content .= utf8_decode(imap_fetchbody($mbh, $mid, $partstring));
} elseif ($parts[$i]['parameters']['0']['value'] == 'iso-8859-1') {
// iso-8859-1 encoded, decode
$content .= quoted_printable_decode(html_entity_decode(htmlentities(imap_fetchbody($mbh, $mid, $partstring))));
} else {
$content .= imap_fetchbody($mbh, $mid, $partstring);
}
} elseif (strtoupper($parts[$i]['subtype']) == "HTML") { // Part is HTML Message
//$html .= imap_fetchbody($mbh, $mid, $partstring);
}
}
if ($parts[$i]['parts']) {
$stack[] = array("p" => $parts, "i" => $i);
$parts = $parts[$i]['parts'];
$i = 0;
} else {
$i++;
}
}
$my_mail['body'] = $content;
//$my_mail['html'] = $html;
// ---------------------------------------------------------------------------- //
// extract and save attachments, if any
$my_mail['nbattachs'] = count($my_mail['attachment']);
if ($my_mail['nbattachs'] > 0) {
// attachments found
debug("Mail has ".$my_mail['nbattachs']." Attachments");
// Define Attachment Loop functions
$attach_invalid = 0;
$attachs_error_echo = '';
$a = 0;
$str = "";
$dirname = "";
//create specific folders
$dirname = date("Ynj");
if (!is_dir("/home/xxx/public_html/xx/mail_attachments/".$dirname."")) {
mkdir("/home/xxx/public_html/xx/mail_attachments/".$dirname."");
}
$random = random();
$dirpath = "/home/xxx/public_html/xx/mail_attachments/".$dirname."/".$random;
while(is_dir($dirpath)) {
$random = random();
$dirpath = "/home/xxx/public_html/xx/mail_attachments/".$dirname."/".$random;
}
if ($my_config['save_attachments']) {
mkdir("$dirpath");
debug("(Created folder => $dirpath)");
} else {
debug("(Folder not created: save_attachments OFF)");
}
// loop attachments, decode and save them
while ($a <= $my_mail['nbattachs']-1) {
$filename = "";
$filename = utf8_decode(imap_utf8($my_mail['attachment'][$a]['filename']));
if ($filename == '') {
$filename = 'no filename found';
}
//$filename = quoted_printable_decode($my_mail['attachment'][$a]['filename']);
debug("(".$a ." => ".$filename." | ");
// Check if Attachment is valid
if (!preg_match("=.jpg$|.avi$|.mpg$|.mpeg$|.gif$|.bmp$|.png$|.rar$|.zip$|.pdf$|.msg$|.mp3$|.xls$|.doc$|.txt$|.htm$|.pps$|.dat$|.html$|.jpeg$=i",$filename)) {
// File Type is invalid
debug("INVALID)");
$attach_invalid++;
$attachs_error_echo .= "* ".$filename."\n";
} else {
// File Type is valid, proceed
debug("VALID) ");
$attachs_error_echo .= $filename."\n";
// decode content
if (preg_match("=.txt$=i",$filename)) {
$input = $my_mail['attachment'][$a]['filedata'];
} else {
$input = base64_decode($my_mail['attachment'][$a]['filedata']);
}
// save attachments
if ($my_config['save_attachments']) {
// check if filename exists in that directory and open handle
if ($filename != "winmail.dat") {
while (@!$handle = fopen($dirpath."/".$filename, "x+")){
$filename = random(2).$filename;
}
if (fwrite($handle, $input)) {
debug("Saved => ".$filename);
} else {
debug("Save => ".$filename." failed!");
}
fclose($handle);
} else {
$attach_invalid++;
}
} else {
debug("save_attachments OFF");
}
}
$a++;
}
// One or more Attachments were invalid
if ($attach_invalid == $my_mail['nbattachs'] && $attach_invalid != 0) {
// None of the attach(s) valid -> delete folder
@rmdir("$dirpath");
debug ("NONE OF THE ATTACH(S) VALID -> $dirpath DELETED");
$att_folder = '0';
/*
$new_subject = "Re: ".$my_mail['subject'];
$new_body = "Dies ist eine automatisch generierte E-Mail\n\n\nWir haben Deine Nachricht erhalten. Eine oder mehrere Anlagen wurden jedoch aus Sicherheitsgründen automatisch vom System gelöscht:\n\n$attachs_error_echo\n\nMit * markierte Dateien mussten wir leider abweisen.\n\n\nSollte dieser Fehler zu Unrecht aufgetreten sein, so kontaktiere uns bitte erneut unter mail@host.tld\n\nMit besten Grüssen\n\n\nThis message was automatically generated\n\n\nWe have received your message. Due to security reasons, one or more attachments had to be declined:\n\n$attachs_error_echo\n\n*-marked files had to be declined.\n\n\nIf you think this error occured without reason, please contact us again.";
if ($my_config['send_invalidattach_mail']) {
//mail($my_mail['from']['email'],$new_subject,$new_body,"From: name <mail@host.tld>\nReply-To: name <mail@host.tld>");
mail("mail@host.tld",$new_subject,$new_body,"From: name <mail@host.tld>\nReply-To: name <mail@host.tld>");
//$insertDB = mysql_query("INSERT INTO `email` ( `id` , `date` , `type` , `encoding` , `ordner` , `answer` , `answer_by` , `answered_date` , `seen_thomas` , `seen_hannes` , `seen_steven` , `from_name` , `from_mail` , `to_name` , `to_mail` , `replyto_name` , `replyto_mail` , `subj` , `text` , `cc` , `bcc`, `dir` ) VALUES ('' , 'NOW()' , '0' , '0' , 'sent' , '0' , '' , '' , '0' , '0' , '0' , 'x' , 'mail@host.tld' , '".$my_mail['from']['name']."' , '".$my_mail['from']['email']."' , '".$my_mail['from']['name']."' , '".$my_mail['from']['email']."' , '".$new_subject."' , '".$new_body."' , '' , '', '0')",$dbh);
debug ("INVALID ATTACH(S) MAIL SENT TO ".$my_mail['from']['email']."");
} else {
debug("Send invalid_attach_mail OFF");
}
*/
} else {
// there are valid attachments
$att_folder = $dirname.'/'.$random;
}
} else {
// no attachments found in email
debug("No attachments were found");
}
}
if ($my_config['debug']) {
debug('<b>Extracted Mail information:</b><br>');
unset($my_mail['attachment']);
print_r($my_mail);
}
if ($my_mail['origin']['from'] == 'mail@host.tld') {
$query = "INSERT INTO mails (id,timestamp,folder,subject,origin,origin_name,destination,destination_name,body,attachments,seen_thomas,seen_hannes,seen_steven) VALUES ('','".mysql_real_escape_string($my_mail['date'])."','outbox','".mysql_real_escape_string($my_mail['subject'])."','".mysql_real_escape_string($my_mail['origin']['from'])."','".mysql_real_escape_string($my_mail['origin']['from_name'])."','".mysql_real_escape_string($my_mail['destination']['to'])."','".mysql_real_escape_string($my_mail['destination']['to_name'])."','".mysql_real_escape_string(strip_tags($my_mail['body']))."','".mysql_real_escape_string($att_folder)."','1','1','1')";
} else {
// save email to database
$query = "INSERT INTO mails (id,timestamp,folder,subject,origin,origin_name,destination,destination_name,body,attachments) VALUES ('','".mysql_real_escape_string($my_mail['date'])."','inbox','".mysql_real_escape_string($my_mail['subject'])."','".mysql_real_escape_string($my_mail['origin']['from'])."','".mysql_real_escape_string($my_mail['origin']['from_name'])."','".mysql_real_escape_string($my_mail['destination']['to'])."','".mysql_real_escape_string($my_mail['destination']['to_name'])."','".mysql_real_escape_string(strip_tags($my_mail['body']))."','".mysql_real_escape_string($att_folder)."')";
}
if (mysql_query($query,$dbh)) {
debug ("Mail successfully saved to database");
// move mail to other folder
if ($my_config['move_mail'] != "") {
if (imap_mail_move($mbh,$my_master['loopindex'],$my_config['move_mail'])) {
debug("Mail successfully moved to ".$imaphost.$my_config['move_mail']."");
} else {
debug("Mail could not be moved to ".$imaphost.$my_config['move_mail']."");
}
} else {
debug("Move Mail turned OFF");
if (imap_delete($mbh,$my_master['loopindex'])) {
debug("Mail successfully marked for deletion");
} else {
debug("Mail could not be marked for deletion");
}
}
} else {
debug ("Mail could not be saved to database! -> ".mysql_error());
}
// free temporary header and structure information
unset($header,$structure);
unset($dirpath, $dirname);
// increment loopindex to get to next message
$my_master['loopindex']++;
}
// delete mails marked for deletion
if (imap_expunge($mbh)) {
debug("Mails marked for deletion successfully deleted from server");
} else {
debug("Mails marked for deletion could not be deleted from server");
}
// terminate IMAP connection
if (imap_close($mbh)) {
debug("Connection to mailserver successfully terminated.");
} else {
debug("Connection to mailserver could not be terminated! ->".imap_last_error()."");
}
if (mysql_close($dbh)) {
debug ("Connection to mySQL Server successfully terminated.");
} else {
debug ("Connectiont o mailserver could not be terminated! ->".mysql_error()."");
}
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
debug ("Execution time: $total_time s");
?>
</pre>
EDIT:
Hier noch ein DB Tabledump, damit du das Script einfacher testen kannst.
Code:
-- Table structure for table 'mails'
--
CREATE TABLE mails (
id int(32) NOT NULL auto_increment,
`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
folder varchar(32) NOT NULL default 'inbox',
int_comments text NOT NULL,
`subject` varchar(255) NOT NULL default 'no subject',
origin varchar(255) NOT NULL default '',
origin_name varchar(255) NOT NULL default '',
destination varchar(255) NOT NULL default '',
destination_name varchar(255) NOT NULL default '',
body text NOT NULL,
attachments varchar(32) NOT NULL default '',
seen_thomas char(1) NOT NULL default '0',
seen_hannes char(1) NOT NULL default '0',
seen_steven char(1) NOT NULL default '0',
PRIMARY KEY (id),
KEY folder (folder)
) ENGINE=MyISAM;
__________________
Unkraut ist die Opposition der Natur gegen die Regierung der Gärtner.
 ticketbörse
Geändert von dsxs (15.12.2006 um 03:50 Uhr).
Grund: noch was vergessen...
|
29.11.2006, 17:40
