How to use the original sender date instead of the fetch date in OTRS

Question

When OTRS fetches emails from an IMAP account it forgets the original send date and replaces this with the fetch (!) date.
Normally this is not a problem, but when you're importing an existing IMAP folder with many emails into OTRS all dates are set to the import data.

This is not cool and I would like to know if there's some way I can have OTRS read the email headers and use that to replace the create_time field in the article table. I was thinking of using the filter module of the postmaster (or something similar).

How do I get OTRS to read the date header in the email and use that date for the create_time?

You could patch Article.pm if that's an option for you. – Sascha Mar 22 '15 at 13:10 — Sascha, Mar 22 '15 at 13:10

score 2 · Answer 1 · answered Jul 17 '12 at 08:59

2

The short answer is: this is not possible. The long answer is: if you want to do this you need to modify the TicketCreate() method, because it will currently only take the current time as create date for a new ticket.

answered Jul 17 '12 at 08:59

MichielB

591
2
6

score 1 · Accepted Answer · answered Dec 19 '12 at 12:16

The solution I found is to add a trigger to MySQL like so:

On article_attachement

CREATE TRIGGER bi_article_attachement_each BEFORE INSERT ON article_attachment
FOR EACH ROW
BEGIN

  DECLARE newdate datetime;
  SET newdate = null;

  SELECT create_time INTO newdate FROM article_plain ap WHERE ap.article_id = new.article_id LIMIT 1;

  IF newdate IS NOT NULL THEN
    SET new.create_time = newdate;
    SET new.change_time = newdate;
  END IF;


END

On article_plain

CREATE TRIGGER bi_article_plain_each BEFORE INSERT ON article_plain
FOR EACH ROW
BEGIN
  DECLARE datestr CHAR(40);
  DECLARE newdate DATETIME;
  DECLARE mindate DATETIME;
  DECLARE myticket BIGINT;

  SET datestr = mid(new.body,locate('Date: ',new.body)+6,40);
  SET newdate = coalesce(str_to_date(datestr,'%a, %e %M %Y %k:%i:%s'),str_to_date(datestr,'%e %M %Y %k:%i:%s'),new.create_time);  

  SET new.create_time = newdate;

  UPDATE article a SET a.create_time = newdate WHERE a.id = new.article_id; 

  SELECT a.ticket_id INTO myticket FROM article a WHERE a.id = new.article_id LIMIT 1;
  SELECT least(min(a.create_time),new.create_time) INTO mindate FROM article a WHERE a.ticket_id = myticket;

  UPDATE ticket t
    SET t.create_time = least(t.create_time, mindate)
        , t.create_time_unix = unix_timestamp(least(t.create_time, mindate))
    WHERE t.id = myticket;

END

Same table, but after update

CREATE TRIGGER ua_article_plain_each AFTER UPDATE ON article_plain
FOR EACH ROW
BEGIN

  IF old.create_time <> new.create_time THEN BEGIN

    UPDATE article a SET a.create_time = new.create_time, a.change_time = new.create_time WHERE a.id = new.article_id;

  END; END IF;

END

Finally a trigger on ticket:

CREATE TRIGGER bu_ticket_each BEFORE UPDATE ON ticket
FOR EACH ROW
BEGIN
  DECLARE newtn varchar(50);
  DECLARE newdate varchar(12);
  DECLARE filter varchar(13);
  SET newdate = date_format(old.create_time,'%Y%m%d%H%i');
  SET filter = concat(newdate,'%');
  SELECT concat(newdate,ifnull(right(concat('0000',right(max(tn),4)+1),4),'0001')) INTO newtn 
    FROM ticket
    WHERE tn LIKE filter;
  SET new.tn = newtn;
END

score 1 · Answer 3 · edited Jan 20 '15 at 14:52

I had the same Problem. Thank you very much for this Help. The Procedures didn´t work for me, so i changed it and work with a temporary-Table.

CREATE TABLE `article_plain_date` (
  `article_plain_FK` INT(11) NOT NULL,
  `atricle_ID` INT(11) DEFAULT NULL,
  `ticket_ID` INT(11) DEFAULT NULL,
  `create_date` DATETIME DEFAULT NULL,
  PRIMARY KEY (`article_plain_FK`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO article_plain_date
SELECT p.id,a.id,t.id,IF(STR_TO_DATE(MID(body,LOCATE('Date: ',body)+6,25),'%a, %e %M %Y %k:%i:%s'),STR_TO_DATE(MID(body,LOCATE('Date: ',body)+6,25),'%a, %e %M %Y %k:%i:%s'),p.create_time) AS create_date  FROM otrs.article_plain p

JOIN article a ON (p.`article_id` = a.id)
JOIN ticket t ON (a.`ticket_id` = t.id);

UPDATE article_plain p
JOIN article_plain_date d ON (p.`article_id` = d.`atricle_ID`)
SET p.`create_time` = d.`create_date`;

UPDATE article a
JOIN article_plain_date d ON (a.id = d.`atricle_ID`)
SET a.`create_time` = d.`create_date`;

UPDATE ticket t
JOIN article_plain_date d ON (t.id = d.`ticket_ID`)
SET t.`create_time` = d.`create_date`, t.`create_time_unix` = UNIX_TIMESTAMP(d.`create_date`);

How to use the original sender date instead of the fetch date in OTRS

3 Answers3