Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment
up vote 0 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.