Jun Omae avatar Jun Omae committed ac96167 Draft

Comments (0)

Files changed (52)

Add a comment to this file

mailarchiveplugin/license.txt

File contents unchanged.

mailarchiveplugin/mailarchive/__init__.py

 from api import *
 from attachment import *
+from macro import *
 from mailarchiveadmin import *
 from model import *
-from web_ui import *
+from web_ui import *

mailarchiveplugin/mailarchive/api.py

-# -*- coding: utf-8 -*-
-
-from trac.core import Interface
-
-class EmailException(Exception):
-    """error exception when processing email messages"""
-    
-class IEmailHandler(Interface):
-
-    def match(mail):
-        """
-        whether this handler can be used on this message
-        """
-
-    def invoke(mail, warnings):
-        """
-        what to do on receiving an email;
-        returns the message if it is availble to other 
-        IEmailHandler plugins or None
-        if the message is consumed
-        warnings is a list of warnings to append to
-        """
-
-    def order():
-        """
-        what order to process the IEmailHandler in.
-        higher order == higher precedence;
-        None = no precedence
-        """
+# -*- coding: utf-8 -*-
+
+from trac.core import Interface
+
+class EmailException(Exception):
+    """error exception when processing email messages"""
+    
+class IEmailHandler(Interface):
+
+    def match(mail):
+        """
+        whether this handler can be used on this message
+        """
+
+    def invoke(mail, warnings):
+        """
+        what to do on receiving an email;
+        returns the message if it is availble to other 
+        IEmailHandler plugins or None
+        if the message is consumed
+        warnings is a list of warnings to append to
+        """
+
+    def order():
+        """
+        what order to process the IEmailHandler in.
+        higher order == higher precedence;
+        None = no precedence
+        """
Add a comment to this file

mailarchiveplugin/mailarchive/attachment.py

File contents unchanged.

mailarchiveplugin/mailarchive/env.py

-# -*- coding: utf-8 -*-
-
-import re
-
-from trac.core import *
-from trac.env import IEnvironmentSetupParticipant
-
-class MailArchiveSetup(Component):
-    implements(IEnvironmentSetupParticipant)
-    
-    # IEnvironmentSetupParticipant methods
-    def environment_created(self):
-        self.found_db_version = 0
-        self.upgrade_environment(self.env.get_db_cnx())
-
-    def environment_needs_upgrade(self, db):
-        # check for our custom fields
-        if 'mail_id' not in self.config['ticket-custom']:
-            return True
-        
-        # check for database table
-        cursor = db.cursor()
-        try:
-            cursor.execute("SELECT id FROM mailarc WHERE id='1'")
-        except :
-            db.rollback()
-            return True
-            
-        # fall through
-        return False
-
-    def upgrade_environment(self, db):
-        # create custom field
-        custom = self.config['ticket-custom']
-        if 'mail_id' not in custom:
-            custom.set('mail_id', 'text')
-            custom.set('mail_id.label', 'Mail ID')
-            self.config.save()
-            
-        # create database tables
-        sql = [
-"""
-CREATE TABLE mailarc (id integer, category text, messageid text,
- utcdate integer, zoneoffset integer, 
- subject text, fromname text, fromaddr text, header text, text text, 
- threadroot text, threadparent text);
-""",
-"""
-CREATE TABLE mailarc_category ( category text, mlid text, yearmonth text, count integer);
-""",
-"""
-CREATE UNIQUE INDEX mailarc_messageid_idx ON mailarc (messageid)
-""",
-"""
-CREATE INDEX mailarc_id_idx ON mailarc (id)
-""",
-"""
-CREATE INDEX mailarc_category_idx ON mailarc (category)
-""",
-"""
-CREATE INDEX mailarc_utcdate_idx ON mailarc (utcdate)
-""",
-"""
-CREATE UNIQUE INDEX mailarc_category_category_idx ON mailarc_category (category)
-""",
-        ]
-        
-        cursor = db.cursor()
-        for s in sql:
-            cursor.execute(s)
-            self.log.debug('%s' % s)
+# -*- coding: utf-8 -*-
+
+import re
+
+from trac.core import *
+from trac.db import Table, Column, Index, DatabaseManager
+from trac.env import IEnvironmentSetupParticipant
+from trac.util.translation import _
+
+class MailArchiveSetup(Component):
+    implements(IEnvironmentSetupParticipant)
+    
+    # IEnvironmentSetupParticipant methods
+    def environment_created(self):
+        self.found_db_version = 0
+        self.upgrade_environment(self.env.get_db_cnx())
+
+    def environment_needs_upgrade(self, db):
+        # check for our custom fields
+        if 'mail_id' not in self.config['ticket-custom']:
+            return True
+        
+        if self.is_no_table(db):
+            return True
+        
+        # old db schema?
+        if self.is_old_schema(db):
+            self.log.info("mailarc table is old schema. please update your environment.")
+            return True
+        return False
+
+    def upgrade_environment(self, db):
+        # create custom field
+        custom = self.config['ticket-custom']
+        if 'mail_id' not in custom:
+            custom.set('mail_id', 'text')
+            custom.set('mail_id.label', 'Mail ID')
+            self.config.save()
+            
+        db = self.env.get_db_cnx()
+        
+        # create database tables
+        if self.is_no_table(db):
+            self.create_db(db)
+        
+        db.commit()
+            
+        if self.is_old_schema(db):
+            self.change_schema(db)
+            
+        self.update_thread_root(db)
+            
+        db.commit()
+
+    def create_db(self, db):
+        mailarc_table = Table('mailarc', key=('id'))[
+            Column('id', auto_increment=True),
+            Column('category'),
+            Column('messageid'),
+            Column('utcdate', type='int'),
+            Column('zoneoffset', type='int'),
+            Column('subject'),
+            Column('fromname'),
+            Column('fromaddr'),
+            Column('header'),
+            Column('text'),
+            Column('threadroot'),
+            Column('threadparent'),
+            Index(['messageid']),
+            Index(['id']),
+            Index(['category']),
+            Index(['utcdate']),
+            Index(['threadroot'])]
+
+        mailarc_category_table = Table('mailarc_category', key=('category'))[
+            Column('category'),
+            Column('mlid'),
+            Column('yearmonth'),
+            Column('count', type='int'),
+            Index(['category']),
+            Index(['mlid']),
+            Index(['yearmonth'])]
+
+        self.create_table(db, mailarc_table)
+        self.create_table(db, mailarc_category_table)
+        
+    def update_thread_root(self, db):
+        from model import MailFinder
+        
+        self.log.info("update thread_root...")
+        
+        mails = MailFinder.find_not_root(self.env)
+        
+        cursor = db.cursor()
+        for mail in mails:
+            root_id = mail.get_thread_root().messageid
+            sql = "UPDATE mailarc SET threadroot = %s WHERE messageid = %s" 
+            self.log.debug(_('root_id=%s, messageid=%s' % (root_id, mail.messageid)))
+            if root_id == mail.messageid:
+                #自分が親(親メッセージIDのメールがDBに存在しない)の場合、更新しない
+                continue
+            cursor.execute(sql, (root_id, mail.messageid))
+            self.log.debug('%s' % sql)
+            
+        self.log.info("update thread_root is done.")
+        
+    def change_schema(self, db):
+        cursor = db.cursor()
+        
+        all_column = "id,category,messageid,utcdate,zoneoffset,subject,fromname,fromaddr,header,text,threadroot,threadparent"
+        all_column_c = "category,mlid,yearmonth,count"
+        try:
+            cursor.execute("CREATE TEMPORARY TABLE mailarc_backup"
+                           "(%s)" % all_column)
+            cursor.execute("CREATE TEMPORARY TABLE mailarc_category_backup"
+                           "(%s)" % all_column_c)
+            
+            self.log.info("create temporary tables is done.")
+            
+            cursor.execute("INSERT INTO mailarc_backup SELECT %s FROM mailarc" % all_column)
+            cursor.execute("INSERT INTO mailarc_category_backup SELECT %s FROM mailarc_category" % all_column_c)
+            
+            self.log.info("copy to backup table is done.")
+
+            cursor.execute("DROP TABLE mailarc")
+            cursor.execute("DROP TABLE mailarc_category")
+            
+            self.log.info("drop old schema tables is done.")
+            
+            self.create_db(db)
+            
+            self.log.info("create new tables is done.")
+            
+            cursor.execute("INSERT INTO mailarc SELECT %s FROM mailarc_backup" % all_column)
+            cursor.execute("INSERT INTO mailarc_category SELECT %s FROM mailarc_category_backup" % all_column_c)
+            
+            self.log.info("restore data is done.")
+            
+            cursor.execute("DROP TABLE mailarc_backup")
+            cursor.execute("DROP TABLE mailarc_category_backup")
+            
+            self.log.info("drop temporary tables is done.")
+            
+            cursor.execute("SELECT count(*) FROM mailarc")
+            row = cursor.fetchone()[0]
+            
+            self.log.info("converted count: %s" % row)
+            
+        except Exception, e:
+            import traceback
+            traceback.print_exc(e)
+            db.rollback()
+        
+    def is_no_table(self, db):
+        # check for database table
+        cursor = db.cursor()
+        try:
+            cursor.execute("SELECT id FROM mailarc WHERE id='1'")
+            cursor.execute("SELECT category FROM mailarc_category WHERE category='1'")
+        except Exception, e:
+#            import traceback
+#            traceback.print_exc(e)
+            return True
+        return False
+        
+    def is_old_schema(self, db):
+        cursor = db.cursor()
+        try:
+            cursor.execute("INSERT INTO mailarc ("
+                    "category, messageid) "
+                    "VALUES (%s,%s)", ('testcategory', 'testid'))
+            cursor.execute("SELECT id from mailarc WHERE messageid = 'testid'")
+            id = cursor.fetchone()[0]
+            if id is None:
+                db.rollback()
+                return True
+        except :
+            db.rollback()
+            return True
+            
+        db.rollback()
+        return False
+
+    def create_table(self, db, table):
+        db_connector, _ = DatabaseManager(self.env)._get_connector()
+        
+        stmts = db_connector.to_sql(table)
+        for stmt in stmts:
+            self.execute_query(db, stmt)
+    
+    def execute_query(self, db, sql, *params):
+        cur = db.cursor()
+        try:
+            cur.execute(sql, params)
+        except Exception, e:
+            pass

mailarchiveplugin/mailarchive/htdocs/css/mailarchive.css

-fieldset.collapsed {
-	border-width: 0px;
-	margin-bottom: 0px;
-	padding: 0px .5em;
-}
-
-fieldset.collapsed div {
-	display: none
-}
-
-.thread_ul {
-	padding-left: 20px;
-	margin-left: 0px;
-}
-
-.thread_li {
-	padding-left: 0px;
-	margin-left: 0px;
-}
-
-.thread_subject_clip {
-	padding-right: 20px;
-	background-image: url(../png/clip.png);
-	background-repeat: no-repeat;
-	background-position: right top;
-}
-
-.thread_from {
-	font-size: 80%;
-	color: #666
-}
-
-.thread_senddate {
-	font-size: 80%;
-	color: #666
-}
-
-.related_tickets {
-	font-size: 80%;
-	color: #666
-}
-
-#mail_threads {
-	border: 1px outset #999966;
-	padding: 1em;
-}
-
-#prefs ul {
-	padding-left: 10px;
-	margin: 0px 3px;
-}
-
-#prefs li {
-	padding-left: 0px;
-	margin: 0px 3px;
-}
-
-#prefs .prefs_from {
-	color: #666
-}
-
-#prefs {
-	width: 120px;
-}
-
-* html #prefs {
-	width: 14em
-}
-
+fieldset.collapsed {
+	border-width: 0px;
+	margin-bottom: 0px;
+	padding: 0px .5em;
+}
+
+fieldset.collapsed div {
+	display: none
+}
+
+.thread_ul {
+	padding-left: 20px;
+	margin-left: 0px;
+}
+
+.thread_li {
+	padding-left: 0px;
+	margin-left: 0px;
+}
+
+.thread_subject_clip {
+	padding-right: 20px;
+	background-image: url(../png/clip.png);
+	background-repeat: no-repeat;
+	background-position: right top;
+}
+
+.thread_from {
+	font-size: 80%;
+	color: #666
+}
+
+.thread_senddate {
+	font-size: 80%;
+	color: #666
+}
+
+.related_tickets {
+	color: #666
+}
+
+.related_mail {
+	font-weight:bold;
+}
+
+a.open_mail {
+	background-image: url(../png/new-window.png);
+	background-repeat: no-repeat;
+	padding-right: 8px;
+}
+
+div.mail_loading {
+	background-image: url(../png/loading.gif);
+	background-repeat: no-repeat;
+	padding-right: 8px;
+}
+
+a.reply_mail {
+	background-image: url(../png/arrow_left_16.png);
+	background-repeat: no-repeat;
+	padding-right: 12px;
+}
+
+.mail_body {
+	background: #FFFFDD none repeat scroll 0 0;
+	border: 1px outset #999966;
+	margin-top: 1em;
+	padding: 0.5em 1em;
+	position: relative;
+}
+
+.mail_clickable {
+	border-bottom: 1px dotted #BBBBBB;
+	color: #BB0000;
+	text-decoration: none;
+}
+
+.mail_threads {
+	border: 1px outset #999966;
+	padding: 1em;
+}
+
+h3.mail_group_header {
+	background: #F7F7F7 none repeat scroll 0 0;
+	border-bottom: 1px solid #D7D7D7;
+	margin: 2em 0 0;
+	padding: 0 0.33em;
+}
+
+#prefs ul {
+	padding-left: 10px;
+	margin: 0px 3px;
+}
+
+#prefs li {
+	padding-left: 0px;
+	margin: 0px 3px;
+}
+
+#prefs .prefs_from {
+	color: #666
+}
+
+#prefs {
+	width: 120px;
+}
+
+* html #prefs {
+	width: 14em
+}
+
 /* Set width only for IE */ /*
 ** Style used for displaying Mail icon in Timeline
-*/
-.timeline dt.mailarchive a {
-	background-image: url(../png/mail.png);
+*/
+.timeline dt.mailarchive a {
+	background-image: url(../png/mail.png);
 }

mailarchiveplugin/mailarchive/htdocs/css/mailarchivereport.css

+.mailarchivereport tbody tr.expanded td .expander {
+	background-image: url(../png/toggle-collapse-dark.png);
+}
+
+.mailarchivereport tbody tr.collapsed td .expander {
+	background-image: url(../png/toggle-expand-dark.png);
+}
+
+.expander {
+	background-position: left center;
+	background-repeat: no-repeat;
+	cursor: pointer;
+	padding-left: 16px;
+	padding-right: 0px;
+}

mailarchiveplugin/mailarchive/htdocs/js/mailarchive.js

+//mailarchive.js
+
+if (ajaxmail_base_url == undefined) {
+  var ajaxmail_base_url = '../';
+}
+
+jQuery(function(){
+  initMailTicketReport();
+});
+
+function initMailTicketReport() {
+  //mailarchive report
+  $(".mailarchivereport .expander").click(function(){
+    target = $(this).parent().parent();
+    if (target.hasClass("expanded")) {
+      target.removeClass('expanded').addClass('collapsed');
+      target.next(".mail_data").hide();
+      
+    } else if (target.hasClass("collapsed")) {
+      target.removeClass('collapsed').addClass('expanded');
+      mail_data = target.next('.mail_data').show();
+      holder = mail_data.children('td').children('.mailticketreportplaceholder');
+
+      if (holder.children().length == 0) {
+        showRelatedMailThread(holder);
+      }
+      return false;
+    }
+  });
+}
+
+function initMailThread(thread_id) {
+  //show mail
+  id = "#" + thread_id;
+  $(id + " .mail_subject").click(function(){
+    holder = $(this).parent().children(".mailplaceholder");
+    if (holder.children().length == 0) {
+      showMail(holder);
+    } else {
+      holder.toggle();
+    }
+    return false;
+  });
+
+  //reply edit mail
+  $(id + " .reply_mail").click(function(arg){
+    holder = $(this).parent().children(".mailreplyplaceholder");
+    if (holder.children().length == 0) {
+      showMailEditor(holder);
+    } else {
+      holder.toggle();
+    }
+    return false;
+  });
+}
+
+function showMail(holder) {
+  holderParent = holder.parent();
+  mailId = holderParent.children(".mail_id").text();
+  
+  jQuery.ajax({
+    type: "GET",
+    url: ajaxmail_base_url + "ajaxmailarchive/" + mailId,
+    dataType: "html",
+    success: function (data) {
+      holder.append(data);
+    },
+    beforeSend: function() {
+      holderParent.children(".mail_loading").show();
+    },
+    complete: function() {
+      holderParent.children(".mail_loading").hide();
+    }
+  });
+}
+
+function showRelatedMailThread(holder) {
+  holderParent = holder.parent();
+  mailId = holderParent.children(".mail_id").text();
+  
+  jQuery.ajax({
+    type: "GET",
+    url: ajaxmail_base_url + "ajaxmailthread/" + mailId,
+    dataType: "html",
+    success: function (data) {
+      holder.append(data);
+    },
+    beforeSend: function() {
+      holderParent.children(".mail_loading").show();
+    },
+    complete: function() {
+      holderParent.children(".mail_loading").hide();
+    }
+  });
+}
+
+
+function showMailEditor(holder) {
+  holderParent = holder.parent();
+  mailId = holderParent.children(".mail_id").text();
+
+  jQuery.ajax({
+    type: "GET",
+    url: ajaxmail_base_url + "ajaxmaileditor/" + mailId,
+    dataType: "html",
+    success: function (data) {
+      holder.append(data).fadeIn(200);
+    },
+    beforeSend: function() {
+      holderParent.children(".mail_loading").show();
+    },
+    complete: function() {
+      holderParent.children(".mail_loading").hide();
+    }
+  });
+}
+
+function insertReplyTemplate(target, template, commentTarget, comment) {
+  //insert template
+  if (template != '') {
+    textArea = jQuery(target);
+    text = textArea.val();
+    text = template + '\n' + text;
+    textArea.val(text);
+  }
+  
+  //insert comment
+  if (comment != '') {
+    textField = jQuery(commentTarget);
+    textField.val(comment);
+  }
+}
Add a comment to this file

mailarchiveplugin/mailarchive/htdocs/png/arrow_left_16.png

Added
New image
Add a comment to this file

mailarchiveplugin/mailarchive/htdocs/png/clip.png

Old
Old image
New
New image
Add a comment to this file

mailarchiveplugin/mailarchive/htdocs/png/loading.gif

Added
New image
Add a comment to this file

mailarchiveplugin/mailarchive/htdocs/png/mail.png

Old
Old image
New
New image
Add a comment to this file

mailarchiveplugin/mailarchive/htdocs/png/new-window.png

Added
New image
Add a comment to this file

mailarchiveplugin/mailarchive/htdocs/png/toggle-collapse-dark.png

Added
New image
Add a comment to this file

mailarchiveplugin/mailarchive/htdocs/png/toggle-expand-dark.png

Added
New image

mailarchiveplugin/mailarchive/macro.py

+# -*- coding: utf-8 -*-
+
+import pkg_resources
+import re
+
+from genshi.builder import tag
+
+from trac.core import *
+from trac.config import Option
+from trac.util import TracError
+from trac.util.datefmt import format_datetime, format_date, format_time
+from trac.util.html import Markup
+from trac.util.text import to_unicode
+from trac.util.translation import _
+from trac.mimeview.api import Mimeview, get_mimetype, Context, WikiTextRenderer
+from trac.resource import ResourceNotFound
+from trac.ticket.report import ReportModule
+from trac.web.api import ITemplateStreamFilter
+from trac.web.chrome import add_link, add_script, add_stylesheet, ITemplateProvider
+from trac.wiki.api import IWikiMacroProvider
+from trac.wiki.model import WikiPage
+
+from util import *
+
+class MailArchiveReportMacro(Component):
+    implements(IWikiMacroProvider, ITemplateProvider)
+    
+    # ITemplateProvider methods
+    def get_htdocs_dirs(self):
+        return [('mailarchive',
+                 pkg_resources.resource_filename(__name__, 'htdocs'))]
+
+    def get_templates_dirs(self):
+        return [pkg_resources.resource_filename(__name__, 'templates')]
+    
+    # IWikiMacroProvider methods
+    def get_macros(self):
+        yield 'MailArchiveReport'
+
+    def get_macro_description(self, name):
+        return u'''
+以下のようにWikiにマクロを記載することで、TracReportで定義されているレポートを表示します。
+また、オプションを設定することで、レポートの表からグラフを生成することもできます。
+{{{
+[[ReportInclude(args1,args2,...)]]
+}}}
+
+args:
+ * 表示したいTracReportのIDを指定します。必ず第1引数に指定する必要があります。
+ * title: レポート、グラフのタイトル文字列を指定することができます。コロンに続けてタイトル文字列を指定します。未指定の場合は、TracReportのタイトル文字列が自動的に使用されます。
+ * async: 非同期でレポート、グラフを描画するかどうか指定します。コロンに続けて、以下の記載が可能です。未指定の場合は、trueとなります。
+   * true[[BR]]
+     非同期で描画します。
+   * false[[BR]]
+     非同期で描画しません。
+
+表からグラフの生成は、以下のルールに従って行われます。
+ * 一番左の列が、X軸の値になります。
+  * デフォルトではyyyy-MM-dd形式の場合は日付と見なして、時系列データとして扱います。
+ * 2列目以降がグラフのデータとなります。
+ * ヘッダ行の値がラベルになります。
+
+例:
+ * report:1 を表示する。
+{{{
+[[ReportInclude(1)]]
+}}}
+ * report:9 を棒グラフととともに表示する。
+{{{
+[[ReportInclude(9,graph:bars)]]
+}}}
+ * report:14 を折れ線グラフで表示する。テーブルは表示しない。
+{{{
+[[ReportInclude(14,graph:lines,table:hide)]]
+}}}
+'''
+
+    def expand_macro(self, formatter, name, content):
+        
+        if not content:
+            raise ResourceNotFound(u"[[MailArchiveReport(1)]]のように引数を指定してください。") 
+        req = formatter.req
+        
+        self._add_script(req)
+        
+        if self._is_async(content):
+            return self._render(req, content)
+        else:
+            return self._render(req, content)
+
+    def _add_script(self, req):
+        if not hasattr(req, '_mailarchivereportmacro'):
+            add_script(req, 'mailarchive/js/mailarchive.js')
+            add_stylesheet(req, 'common/css/report.css')
+            add_stylesheet(req, 'mailarchive/css/mailarchive.css')
+            add_stylesheet(req, 'mailarchive/css/mailarchivereport.css')
+            req._mailarchivereport = 0
+        else:
+            req._mailarchivereport = req._mailarchivereport + 1
+            
+    def _is_async(self, content):
+        match = re.match(r'.*(async\s*:\s*false).*', content.lower())
+        if match:
+            return False
+        # default Ajax Mode
+        return True
+            
+    def _render(self, req, content):
+        u"""TracReportsの結果をHTMLで返す。
+        """
+        self.index = req._mailarchivereport
+        
+        id, vars, opts = self._parse_params(content)
+        report = self._get_report_info(id)
+        
+        if len(report) == 1:
+            title, sql, desc = report[0]
+            
+            if opts['title'] == '':
+                opts['title'] = title
+                
+            return self._render_table(req, id, vars, opts, title, sql, desc)
+        else:
+            raise ResourceNotFound(
+                _('Report [%(num)] does not exist.', num=id),
+                _('Invalid Report Number'))
+    
+    def _parse_params(self, params):
+        default_opts = {
+           'title':'',
+           }
+        
+        vars = {}
+        for (index, param) in enumerate(params.split(',')):
+            if index == 0:
+                id = param
+                id, vars = self._parse_vars(id)
+                continue
+            
+            colon_split = param.split(':')
+            key = colon_split[0].strip()
+            value = ''
+            if len(colon_split) > 1:
+                value = ':'.join(colon_split[1:])
+            else:
+                value = True
+            default_opts[key] = value
+        return id, vars, default_opts
+        
+    def _parse_vars(self, id):
+        vars = {}
+        
+        if id.find('?') == -1:
+            return id, vars
+        
+        id_and_params = id.split('?')
+        params = id_and_params[1]
+        id = id_and_params[0]
+        
+        if params.find('&') != -1:
+            for (index, param) in enumerate(params.split('&')):
+                if param.find('=') == -1:
+                    continue
+                entry = param.split('=')
+                vars[entry[0]] = entry[1]
+        elif params.find('=') != -1:
+            entry = params.split('=')
+            vars[entry[0]] = entry[1]
+        
+        return id, vars
+    
+    def _get_report_info(self, id):
+        db = self.env.get_db_cnx()
+        cursor = db.cursor()
+        cursor.execute("SELECT title,query,description from report "
+                       "WHERE id=%s", (id,))
+        
+        report = [(title, sql, desc) for title, sql, desc in cursor]
+        return report
+    
+    def _render_table(self, req, id, vars, opts, title, sql, desc):
+        db = self.env.get_db_cnx()
+        
+        sql, vars = self._sql_sub_vars(sql, vars, db)
+        
+        cursor = db.cursor()
+        cursor.execute(sql, vars)
+        
+        columns = []
+        g_idx = None
+        c_idx = None
+        ticket_idx = None
+        hidden_idxs = []
+        for idx, col in enumerate(cursor.description):
+            columns.append(col[0])
+            if col[0] == '__group__':
+                g_idx = idx
+                hidden_idxs.append(idx)
+            if col[0] == '__color__':
+                c_idx = idx
+                hidden_idxs.append(idx)
+            if col[0].startswith('_'):
+                hidden_idxs.append(idx)
+            if col[0].lower().strip() in ('id', 'ticket', u'チケット'):
+                ticket_idx = idx
+            
+        rows = []
+        groups = []
+        current_group = None
+        ticket_ids = []
+        for idx, record in enumerate(cursor):
+            if g_idx is not None:
+                group = record[g_idx]
+                if current_group and current_group['name'] != group:
+                    current_group['end'] = idx - 1
+                    groups.append(current_group)
+                    current_group = None
+                    
+                if not current_group:
+                    current_group = {'name':group, 'start':idx}
+            
+            row = []
+            for col_idx, data in enumerate(record):
+                row.append(data)
+                
+            ticket_id = None
+            if ticket_idx is not None:
+                ticket_id = record[ticket_idx]
+                ticket_ids.append(ticket_id)
+            rows.append((row, ticket_id))
+        
+        if current_group:
+            current_group['end'] = len(rows) - 1
+            groups.append(current_group)
+            
+            
+        mail_ids = self._get_related_mails(db, ticket_ids)
+            
+        def is_hidden(col_idx):
+            return col_idx in hidden_idxs
+        
+        def has_mail_ids(ticket_id):
+            mids = mail_ids.get(ticket_id, [])
+            if len(mids) > 0:
+                return True
+            else:
+                return False
+            
+        data = {}
+        data['req'] = req
+        data['id'] = atomic_id()
+        data['report_id'] = id
+        data['title'] = title
+        data['has_group'] = g_idx != None
+        data['g_idx'] = g_idx
+        data['groups'] = groups
+        data['c_idx'] = c_idx
+        data['columns'] = columns
+        data['rows'] = rows
+        data['mail_ids'] = mail_ids
+        data['format_time'] = format_time
+        data['format_datetime'] = format_datetime
+        data['is_hidden'] = is_hidden
+        data['has_mail_ids'] = has_mail_ids
+        
+        table = template(self.env, 'mail_ticketreport.html', data)
+        
+        return table
+    
+    def _get_related_mails(self, db, ticket_ids):
+        if not ticket_ids or len(ticket_ids) == 0:
+            return {}
+        
+        sql = """
+        SELECT t.id, tc1.value FROM ticket t LEFT OUTER JOIN ticket_custom tc1 ON tc1.ticket = t.id AND tc1.name = 'mail_id' WHERE t.id IN (%s)
+        """ % ('%s,' * len(ticket_ids))[:-1]
+        
+        cursor = db.cursor()
+        cursor.execute(sql, ticket_ids)
+        
+        mails = {}
+        for ticket_id, mail_id in cursor:
+            mail_ids = [x.strip() for x in mail_id.split(',') if x != '']
+            mails[ticket_id] = mail_ids
+        return mails
+    
+    def _convert(self, column, data):
+        if column == 'time':
+            data = data != None and format_time(int(data)) or '--'
+        if column in ('date', 'created', 'modified'):
+            data = data != None and format_date(int(data)) or '--'
+        if column == 'datetime':
+            data = data != None and format_datetime(int(data)) or '--'
+        if column.lower() in ('ticket', 'id', u'チケット'):
+            data = tag.a('#' + str(col), title='View Ticket', class_='ticket',
+                        href=req.href.ticket(str(col)))
+    
+    def _sql_sub_vars(self, sql, vars, db):
+        rm = ReportModule(self.env)
+        sql, vars = rm.sql_sub_vars(sql, vars, db)
+        return sql, vars

mailarchiveplugin/mailarchive/mail_parser.py

+# -*- coding: utf-8 -*-
+
+import calendar
+import email
+import os
+import re
+import tempfile
+import time
+import traceback
+
+from trac.attachment import Attachment, AttachmentModule
+from trac.util import NaivePopen, Markup
+
+from util import Logger, to_localdate
+
+OUTPUT_ENCODING = 'utf-8'
+
+class MailParser(object):
+    
+    def __init__(self, env, db):
+        self.env = env
+        self.db = db
+        self.log = Logger(env)
+        
+    def parse(self, author, msg):
+        header = self.parse_header(msg)
+        body = self.parse_body(author, msg)
+        return header, body
+        
+    def parse_header(self, msg):
+        mail_header = MailHeader(self.env, msg)
+        mail_header.parse()
+        return mail_header
+    
+    def parse_body(self, author, msg):
+        mail_body = None
+        if msg.is_multipart():
+            mail_body = MailMultipartBody(self.env, self.db, author, msg)
+        else:
+            mail_body = MailBody(self.env, self.db, msg)
+            
+        mail_body.parse()
+        return mail_body
+            
+class MailHeader(object):
+    
+    def __init__(self, env, msg):
+        self.env = env
+        self.msg = msg
+        self.log = Logger(env)
+        
+    def parse(self):
+        msg = self.msg
+        self._parse_messageid(msg)
+        self._parse_date(msg)
+        self._parse_subject(msg)
+        self._parse_reference(msg)
+        
+    def _parse_messageid(self, msg):
+        self.messageid = msg['message-id'].strip('<>')
+
+    def _parse_date(self, msg):
+        if 'date' in msg:
+            datetuple_tz = email.Utils.parsedate_tz(msg['date'])
+            localdate = calendar.timegm(datetuple_tz[:9]) #toDB
+            zoneoffset = datetuple_tz[9] # toDB
+            utcdate = localdate - zoneoffset # toDB
+            #make zone ( +HHMM or -HHMM
+            zone = ''
+            if zoneoffset > 0:
+                zone = '+' + time.strftime('%H%M', time.gmtime(zoneoffset))
+            elif zoneoffset < 0:
+                zone = '-' + time.strftime('%H%M', time.gmtime(-1 * zoneoffset))
+            #self.log.debug( time.strftime("%y/%m/%d %H:%M:%S %z",datetuple_tz[:9]))
+            
+            self.log.debug(time.strftime("%Y/%m/%d %H:%M:%S", time.gmtime(utcdate)))
+            self.log.debug(time.strftime("%Y/%m/%d %H:%M:%S", time.gmtime(localdate)))
+            self.log.debug(zone)
+        
+        self.headers = msg.items()
+        #Return-Path, To, Ccに絞ってヘッダを保存する
+        self.headers = [x for x in msg.items() if x[0] in ('Return-Path', 'To', 'Cc')]
+        
+        fromname, fromaddr = email.Utils.parseaddr(msg['from'])
+        
+        self.fromname = _decode_to_unicode(self, fromname)
+        self.fromaddr = _decode_to_unicode(self, fromaddr)
+        self.zone = zone
+        self.utcdate = utcdate
+        self.zoneoffset = zoneoffset
+        self.localdate = to_localdate(utcdate, zoneoffset)
+        
+        self.log.info('  ' + self.localdate + ' ' + zone + ' ' + fromaddr)
+        
+    def _parse_subject(self, msg):
+        if 'subject' in msg:
+            self.subject = _decode_to_unicode(self, msg['subject'])
+
+    def _parse_reference(self, msg):
+        # make thread infomations
+        ref_messageid = ''
+        if 'in-reply-to' in msg:
+            ref_messageid = ref_messageid + msg['In-Reply-To'] + ' '
+            self.log.debug('In-Reply-To:%s' % ref_messageid)
+
+        if 'references' in msg:
+            ref_messageid = ref_messageid + msg['References'] + ' '
+
+        m = re.findall(r'<(.+?)>', ref_messageid)
+        ref_messageid = ''
+        for text in m:
+            ref_messageid = ref_messageid + "'%s'," % text
+            
+        ref_messageid = ref_messageid.strip(',')
+        
+        self.log.debug('RefMessage-ID:%s' % ref_messageid)
+        
+        self.ref_messageid = ref_messageid
+
+class MailBody(object):
+    
+    def __init__(self, env, db, msg):
+        self.env = env
+        self.db = db
+        self.msg = msg
+        self.log = Logger(env)
+
+    def parse(self):
+        msg = self.msg
+        
+        content_type = msg.get_content_type()
+        self.log.debug('Content-Type:' + content_type)
+        
+        if content_type == 'text/html':
+            body = msg.get_payload(decode=True)
+            charset = msg.get_content_charset()
+ 
+            # need try:
+            if charset != None:
+                self.log.debug("charset:" + charset)
+                body = _to_unicode(self, body, charset)
+
+            body = unicode(body)
+            
+            from stripogram import html2text, html2safehtml
+            body = html2text(body)
+
+        else:
+            #body
+            #self.log.debug(msg.get_content_type())
+            body = msg.get_payload(decode=1)
+            charset = msg.get_content_charset()
+
+            # need try:
+            if charset != None:
+                self.log.debug("charset:" + charset)
+                body = _to_unicode(self, body, charset)
+
+        self.text = body
+        
+    def commit(self, id):
+        pass
+    
+    def rollback(self):
+        pass
+             
+class MailMultipartBody(MailBody):
+
+    def __init__(self, env, db, author, msg):
+        MailBody.__init__(self, env, db, msg)
+        self.author = author
+    
+    def parse(self):
+        msg = self.msg
+        body = ''
+
+        tmp_files = []
+
+        for part in msg.walk():
+            content_type = part.get_content_type()
+            self.log.debug('Content-Type:' + content_type)
+            file_counter = 1
+
+            if content_type == 'multipart/mixed':
+                pass
+            
+            elif content_type == 'text/html' and _is_file(part) == False:
+                if body != '':
+                    body += "\n------------------------------\n\n"
+                    
+                body = part.get_payload(decode=True)
+                charset = part.get_content_charset()
+                
+                self.log.debug('charset:' + str(charset))
+                # Todo:need try
+                if charset != None:
+                    body = _to_unicode(self, body, charset)
+                
+            elif content_type == 'text/plain' and _is_file(part) == False:
+                #body = part.get_payload(decode=True)
+                if body != '':
+                    body += "\n------------------------------\n\n"
+                    
+                current_body = part.get_payload(decode=True)
+                charset = part.get_content_charset()
+                
+                self.log.debug('charset:' + str(charset))
+                # Todo:need try
+                if charset != None:
+                    #body = to_unicode(self, body, charset)
+                    body += _to_unicode(self, current_body, charset)
+                else:
+                    body += current_body
+                
+            elif part.get_payload(decode=True) == None:
+                pass
+            
+            # file attachment
+            else:
+                self.log.debug(part.get_content_type())
+                # get filename
+                # Applications should really sanitize the given filename so that an
+                # email message can't be used to overwrite important files
+                
+                filename = _get_filename(self, part)
+                if not filename:
+                    import mimetypes
+                    
+                    ext = mimetypes.guess_extension(part.get_content_type())
+                    if not ext:
+                        # Use a generic bag-of-bits extension
+                        ext = '.bin'
+                    filename = 'part-%03d%s' % (file_counter, ext)
+                    file_counter += 1
+
+                self.log.debug("filename:" + filename.encode(OUTPUT_ENCODING))
+
+                # make attachment
+                tmp = tempfile.TemporaryFile()
+
+                tempsize = len(part.get_payload(decode=True))
+                tmp.write(part.get_payload(decode=True))
+
+                tmp.flush()
+                tmp.seek(0, 0)
+                
+                tmp_files.append((tmp, tempsize, filename, file_counter))
+ 
+        self.text = body
+        self.attachment_files = tmp_files
+        
+    def commit(self, id):
+        if len(self.attachment_files) == 0:
+            return
+        
+        self._delete_attachments(id)
+        
+        for tmp, tempsize, filename, file_counter in self.attachment_files:
+            new_filename = None
+            
+            attachment = Attachment(self.env, 'mailarchive', id)
+    
+            attachment.description = '' # req.args.get('description', '')
+            attachment.author = self.author #req.args.get('author', '')
+            attachment.ipnr = '127.0.0.1'
+            
+            print filename
+            
+            try:
+                attachment.insert(filename,
+                        tmp, tempsize, None, self.db)
+            except Exception, e:
+                #ファイル名を変えてリトライ
+                try:
+                    ext = filename.split('.')[-1]
+                    if ext == filename:
+                        ext = '.bin'
+                    else:
+                        ext = '.' + ext
+                    new_filename = 'part-%03d%s' % (file_counter, ext)
+                    file_counter += 1
+                    attachment.description += ', Original FileName: %s' % filename
+                    attachment.insert(new_filename,
+                            tmp, tempsize, None, self.db)
+                    self.log.warn('As name is too long, the attached file is renamed : ' + new_filename)
+                except Exception, e:
+                    self.log.error('Exception at attach file of Mail-ID: %d, filename: %s' % (id, new_filename))
+                    traceback.print_exc(e)
+                tmp.close()
+            
+    def rollback(self):
+        for tmp in self.attachment_files:
+            tmp[0].close()
+            
+    def _delete_attachments(self, id):
+        Attachment.delete_all(self.env, 'mailarchive', id, self.db)
+        
+def _is_file(part):
+    """Return True:filename associated with the payload if present.
+    """
+    missing = object()
+    filename = part.get_param('filename', missing, 'content-disposition')
+    if filename is missing:
+        filename = part.get_param('name', missing, 'content-disposition')
+    if filename is missing:
+        return False
+    return True
+    
+def _get_filename(com, part, failobj=None):
+    """Return the filename associated with the payload if present.
+
+    The filename is extracted from the Content-Disposition header's
+    `filename' parameter, and it is unquoted.  If that header is missing
+    the `filename' parameter, this method falls back to looking for the
+    `name' parameter.
+    """
+    missing = object()
+    filename = part.get_param('filename', missing, 'content-disposition')
+    if filename is missing:
+        filename = part.get_param('name', missing, 'content-disposition')
+    if filename is missing:
+        return failobj
+
+    errors = 'replace'
+    fallback_charset = 'us-ascii'
+    if isinstance(filename, tuple):
+        rawval = email.Utils.unquote(filename[2])
+        charset = filename[0] or 'us-ascii'
+        try:
+            return _to_unicode(com, rawval, charset)
+        except LookupError:
+            # XXX charset is unknown to Python.
+            return unicode(rawval, fallback_charset, errors)
+    else:
+        return _decode_to_unicode(com, email.Utils.unquote(filename))
+    
+def _decode_to_unicode(com, basestr):
+    # http://www.python.jp/pipermail/python-ml-jp/2004-June/002932.html
+    # Make mail header string to unicode string
+
+    decodefrag = email.Header.decode_header(basestr)
+    subj_fragments = ['', ]
+    for frag, enc in decodefrag:
+        if enc:
+            frag = _to_unicode(com, frag, enc)
+        subj_fragments.append(frag)
+    return ''.join(subj_fragments)
+
+def _to_unicode(com, text, charset):
+    if text == '':
+        return ''
+
+    default_charset = com.env.config.get('mailarchive', 'default_charset', None)
+    if default_charset:
+        chaerset = default_charset
+
+    # to unicode with codecaliases
+    # codecaliases change mail charset to python charset
+    charset = charset.lower()
+    aliases = {}
+    aliases_text = com.env.config.get('mailarchive', 'codecaliases')
+    for alias in aliases_text.split(','):
+        alias_s = alias.split(':')
+        if len(alias_s) >= 2:
+            if alias_s[1] == 'cmd':
+                aliases[alias_s[0].lower()] = ('cmd', alias_s[2])
+            else:
+                aliases[alias_s[0].lower()] = ('codec', alias_s[1])
+
+    if aliases.has_key(charset):
+        (type, alias) = aliases[charset]
+        if type == 'codec':
+            text = unicode(text, alias)
+        elif type == 'cmd':
+            np = NaivePopen(alias, text, capturestderr=1)
+            if np.errorlevel or np.err:
+                err = 'Running (%s) failed: %s, %s.' % (alias, np.errorlevel,
+                                                        np.err)
+                print err
+                raise Exception, err
+            text = unicode(np.out, 'utf-8')
+            
+            if u'\ufeff' in text:
+                p = re.compile(u'\ufeff')
+                text = p.sub('', text)
+    else:
+        text = unicode(text, charset)
+        
+    return text
+

mailarchiveplugin/mailarchive/mailarchiveadmin.py

 # -*- coding: utf-8 -*-
+# MailArchive plugin
 
-import cmd
-import sys
-
-import urllib
-import time
 import calendar
-import re
-import os
-import tempfile
+import email
 import email.Errors
 import email.Utils
 import mailbox
 import mimetypes
-import email
-#from email.Parser  import Parser
-from email.Header import decode_header
-#from email.Utils import collapse_rfc2231_value
+import os
+import poplib
+import re
+import time
 import traceback
+from email.Utils import unquote
 
-#011
-import pkg_resources
+from trac.core import Component, implements
+from trac.admin.api import IAdminCommandProvider
+from trac.util import NaivePopen
+from trac.attachment import Attachment
 
-from genshi.builder import tag
 
-from genshi.core import Stream, Markup as GenshiMarkup
-from genshi.input import HTMLParser, ParseError, HTML
+class MailArchiveAdmin(Component):
+    implements(IAdminCommandProvider)
 
-from trac.core import *
-from trac.env import IEnvironmentSetupParticipant
-#from trac.Search import ISearchSource, search_to_sql, shorten_result
-from trac.search import ISearchSource, search_to_sql, shorten_result
+    def get_admin_commands(self):
+        yield ('mailarchive import', '<mlname> <filepath>',
+               'import UnixMail',
+               None, self._do_import)
+        yield ('mailarchive pop3', '<mlname>',
+               'import from pop3 server',
+               None, self._do_pop3)
 
-from trac.ticket.model import Ticket
-
-from trac.web import IRequestHandler
-from trac.util import NaivePopen
-from trac.util.translation import _
-from StringIO import StringIO
-
-import poplib
-
-from trac.wiki import wiki_to_html,wiki_to_oneliner, IWikiSyntaxProvider
-from trac.wiki.api import WikiSystem
-from trac.util.html import html, Markup #0.10
-
-from trac.util.text import to_unicode, wrap, unicode_quote, unicode_unquote, \
-                           print_table, console_print
-                           
-from trac.web.chrome import add_link, add_stylesheet, INavigationContributor, ITemplateProvider
-
-#0.11 from trac.attachment import attachment_to_hdf, attachments_to_hdf, Attachment, AttachmentModule
-from trac.attachment import Attachment,AttachmentModule
-
-from trac.mimeview import *
-#from trac.mimeview.api import Mimeview, IContentConverter #0.10
-
-
-from trac.admin.console import TracAdmin
-
-from api import IEmailHandler
-from model import Mail
-from util import printout, printerr
-
-
-PLUGIN_VERSION = pkg_resources.get_distribution('TracMailArchive').version
-
-class MailArchiveAdmin(TracAdmin):
-    intro = ''
-    doc_header = 'Trac MailArchive Plugin Admin Console %(version)s\n' \
-                 % {'version': PLUGIN_VERSION}
-    
-    ruler = ''
-    prompt = "MailArchiveAdmin> "
-    __env = None
-    _date_format = '%Y-%m-%d'
-    _datetime_format = '%Y-%m-%d %H:%M:%S'
-    _date_format_hint = 'YYYY-MM-DD'
-
-    ## Help
-    _help_help = [('help', 'Show documentation')]
 
     def all_docs(cls):
-        return (cls._help_help + cls._help_import + cls._help_pop3 + cls._help_updatedb)
+        return (cls._help_help)
     all_docs = classmethod(all_docs)
 
+
+
     def msgfactory(self,fp):
         try:
             return email.message_from_file(fp)
         except email.Errors.MessageParseError:
             # Don't return None since that will
-	    # stop the mailbox iterator
-	    return ''
+            # stop the mailbox iterator
+            return ''
 
-    def import_message(self, msg, author, mlid, db):
-        mail = Mail(self.env, db=db)
-        mail.populate(author, msg, mlid)
-        
-        self.handleMail(mail)
+    def decode_to_unicode(self, basestr):
+        # http://www.python.jp/pipermail/python-ml-jp/2004-June/002932.html
+        # Make mail header string to unicode string
 
-    def handleMail(self, mail):
-        # handle the message
-        handlers = ExtensionPoint(IEmailHandler).extensions(self.env)
-        
-        handlers.sort(key=lambda x: x.order(), reverse=True)
-        warnings = []
-        for handler in handlers:
-            try:
-                message = handler.invoke(mail, warnings)
-            except Exception, e:
-                traceback.print_exc(e)
-                printerr(_('Handler Error. Subject: %s\n\n%s' % (str(mail.subject), str(e))))
-        
+        decodefrag = email.Header.decode_header(basestr)
+        subj_fragments = ['',]
+        for frag, enc in decodefrag:
+            if enc:
+                frag = self.to_unicode(frag, enc)
+            subj_fragments.append(frag)
+        return ''.join(subj_fragments)
+
+    def to_unicode(self,text,charset):
+        if text=='':
+            return ''
+
+        default_charset = self.env.config.get('mailarchive', 'default_charset',None)
+        if default_charset :
+            charset = default_charset
+
+        # to unicode with codecaliases
+        # codecaliases change mail charset to python charset
+        charset = charset.lower( )
+        aliases = {}
+        aliases_text = self.env.config.get('mailarchive', 'codecaliases')
+        for alias in aliases_text.split(','):
+            alias_s = alias.split(':')
+            if len(alias_s) >=2:
+                if alias_s[1] == 'cmd':
+                    aliases[alias_s[0].lower()] = ('cmd',alias_s[2])
+                else:
+                    aliases[alias_s[0].lower()] = ('codec',alias_s[1])
+
+        if aliases.has_key(charset):
+            (type,alias) = aliases[charset]
+            if type == 'codec':
+                text = unicode(text,alias)
+            elif type == 'cmd':
+                np = NaivePopen(alias, text, capturestderr=1)
+                if np.errorlevel or np.err:
+                    err = 'Failed: %s, %s.' % (np.errorlevel, np.err)
+                    raise Exception, err
+                text = unicode(np.out,'utf-8')
+        else:
+            text = unicode(text,charset)
+        return text
+
+    def import_message(self, msg, author,mlid, db):
+        OUTPUT_ENCODING = 'utf-8'
+        subject = ''
+        messageid = ''
+        utcdate = 0
+        localdate = 0
+        zoneoffset = 0
+        text = ''
+        body = ''
+        ref_messageid = ''
+
+        cursor = db.cursor()
+        is_newid = False
+
+        if 'message-id' in msg:
+            messageid = msg['message-id']
+            if messageid[:1] == '<':
+                messageid = messageid[1:]
+            if messageid[-1:] == '>':
+                messageid = messageid[:-1]
+            self.print_debug('Message-ID:%s' % messageid )
+
+            #check messageid is unique
+            self.print_debug("Creating new mailarc '%s'" % 'mailarc')
+            cursor.execute("SELECT id from mailarc WHERE messageid=%s",(messageid,))
+            row = cursor.fetchone()
+            id = None
+            if row:
+                id = row[0]
+            if id == None or id == "":
+                # why? get_last_id return 0 at first.
+                #id = db.get_last_id(cursor, 'mailarc')
+                is_newid = True
+                cursor.execute("SELECT Max(id)+1 as id from mailarc")
+                row = cursor.fetchone()
+                if row and row[0] != None:
+                    id = row[0]
+                else:
+                    id = 1
+            id = int(id) # Because id might be 'n.0', int() is called.
+
+
+        if 'date' in msg:
+            datetuple_tz = email.Utils.parsedate_tz(msg['date'])
+            localdate = calendar.timegm(datetuple_tz[:9]) #toDB
+            zoneoffset = datetuple_tz[9] # toDB
+            utcdate = localdate-zoneoffset # toDB
+            #make zone ( +HHMM or -HHMM
+            zone = ''
+            if zoneoffset >0:
+                zone = '+' + time.strftime('%H%M',time.gmtime(zoneoffset))
+            elif zoneoffset < 0:
+                zone = '-' + time.strftime('%H%M',time.gmtime(-1*zoneoffset))
+
+            #self.print_debug( time.strftime("%y/%m/%d %H:%M:%S %z",datetuple_tz[:9]))
+            self.print_debug( time.strftime("%Y/%m/%d %H:%M:%S",time.gmtime(utcdate)))
+            self.print_debug( time.strftime("%Y/%m/%d %H:%M:%S",time.gmtime(localdate)))
+            self.print_debug(zone)
+
+        fromname,fromaddr = email.Utils.parseaddr(msg['from'])
+        fromname = self.decode_to_unicode(fromname)
+        fromaddr = self.decode_to_unicode(fromaddr)
+
+        self.print_info( '  ' + time.strftime("%Y/%m/%d %H:%M:%S",time.gmtime(localdate))+' ' + zone +' '+ fromaddr)
+
+        if 'subject' in msg:
+            subject = self.decode_to_unicode(msg['subject'])
+            self.print_debug( subject.encode(OUTPUT_ENCODING))
+
+        # make thread infomations
+        ref_messageid = ''
+        if 'in-reply-to' in msg:
+            ref_messageid = ref_messageid + msg['In-Reply-To'] + ' '
+            self.print_debug('In-Reply-To:%s' % ref_messageid )
+
+        if 'references' in msg:
+            ref_messageid = ref_messageid + msg['References'] + ' '
+
+        m = re.findall(r'<(.+?)>', ref_messageid)
+        ref_messageid = ''
+        for text in m:
+            ref_messageid = ref_messageid + "'%s'," % text
+        ref_messageid = ref_messageid.strip(',')
+        self.print_debug('RefMessage-ID:%s' % ref_messageid )
+
+
+        # multipart mail
+        if msg.is_multipart():
+            body = ''
+            # delete all attachement at message-id
+            Attachment.delete_all(self.env, 'mailarchive', id, db)
+
+            for part in msg.walk():
+                content_type = part.get_content_type()
+                self.print_debug('Content-Type:'+content_type)
+                file_counter = 1
+
+                if content_type == 'multipart/mixed':
+                    pass
+                elif content_type == 'text/html' and self.is_file(part) == False:
+                    body = part.get_payload(decode=1)
+                elif content_type == 'text/plain' and self.is_file(part) == False:
+                    body = part.get_payload(decode=1)
+                    charset = part.get_content_charset()
+                    self.print_debug('charset:'+str(charset))
+                    # Todo:need try
+                    if charset != None:
+                        body = self.to_unicode(body,charset)
+                elif part.get_payload(decode=1) == None:
+                    pass
+                else:
+                    self.print_debug( part.get_content_type())
+                    # get filename
+                    # Applications should really sanitize the given filename so that an
+                    # email message can't be used to overwrite important files
+                    filename = self.get_filename(part)
+                    if not filename:
+                        ext = mimetypes.guess_extension(part.get_content_type())
+                        if not ext:
+                            # Use a generic bag-of-bits extension
+                            ext = '.bin'
+                        filename = 'part-%03d%s' % (file_counter, ext)
+                        file_counter += 1
+
+                    self.print_debug("filename:" + filename.encode(OUTPUT_ENCODING))
+
+                    # make attachment
+                    tmp = os.tmpfile()
+                    tempsize =len(part.get_payload(decode=1))
+                    tmp.write(part.get_payload(decode=1))
+
+                    tmp.flush()
+                    tmp.seek(0,0)
+
+                    attachment = Attachment(self.env,'mailarchive', id)
+
+                    attachment.description = '' # req.args.get('description', '')
+                    attachment.author = author #req.args.get('author', '')
+                    attachment.ipnr = '127.0.0.1'
+
+                    try:
+                        attachment.insert(filename,
+                                tmp, tempsize,None,db)
+                    except Exception, e:
+                        try:
+                            ext = filename.split('.')[-1]
+                            if ext == filename:
+                                ext = '.bin'
+                            else:
+                                ext = '.' + ext
+                            filename = 'part-%03d%s' % (file_counter, ext)
+                            file_counter += 1
+                            attachment.insert(filename,
+                                    tmp, tempsize,None,db)
+                            self.print_warning('As name is too long, the attached file is renamed : '+filename)
+
+                        except Exception, e:
+                            self.print_error('Exception at attach file of Message-ID:'+messageid)
+                            self.print_error( e )
+
+                    tmp.close()
+
+        # not multipart mail
+        else:
+            # Todo:if Content-Type = text/html then convert htmlMail to text
+            content_type = msg.get_content_type()
+            self.print_debug('Content-Type:'+content_type)
+            if content_type == 'text/html':
+                body = 'html'
+            else:
+                #body
+                #self.print_debug(msg.get_content_type())
+                body = msg.get_payload(decode=1)
+                charset = msg.get_content_charset()
+
+                # need try:
+                if charset != None:
+                    self.print_debug("charset:"+charset)
+                    body = self.to_unicode(body,charset)
+
+
+        #body = body.replace(os.linesep,'\n')
+        self.print_debug('Thread')
+
+        thread_parent = ref_messageid.replace("'",'').replace(',',' ')
+        thread_root = ''
+        if thread_parent !='':
+        # sarch first parent id
+            self.print_debug("SearchThread;"+thread_parent)
+            cursor = db.cursor()
+            sql = "SELECT threadroot,messageid FROM mailarc where messageid in (%s)" % ref_messageid
+            self.print_debug(sql)
+            cursor.execute(sql)
+
+            row = cursor.fetchone()
+            if row:
+                #thread_parent = row[1]
+                if row[0] == '':
+                    thread_root = thread_parent.split(' ').pop()
+                    self.print_debug("AddToThread;"+thread_root)
+                else:
+                    thread_root = row[0]
+                    self.print_debug("NewThread;"+thread_root)
+            else:
+                    self.print_debug("NoThread;"+thread_parent)
+        thread_root = thread_root.strip()
+
+        self.print_debug('Insert')
+
+        if messageid != '':
+
+            # insert or update  mailarc_category
+
+            yearmonth = time.strftime("%Y%m",time.gmtime(utcdate))
+            category = mlid+yearmonth
+            cursor.execute("SELECT category,mlid,yearmonth,count FROM mailarc_category WHERE category=%s",
+                           (category,))
+            row = cursor.fetchone()
+            count = 0
+            if row:
+                count = row[3]
+                pass
+            else:
+                cursor.execute("INSERT INTO mailarc_category (category,mlid,yearmonth,count) VALUES(%s,%s,%s,%s)",
+                               (category, mlid, yearmonth, 0))
+            if is_newid == True:
+                count = count +1
+            cursor.execute("UPDATE mailarc_category SET count=%s WHERE category=%s" ,
+                           (count, category))
+
+            # insert or update mailarc
+
+            #self.print_debug(
+            #    "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" %(str(id),
+            #    category.encode('utf-8'),
+            #    messageid,
+            #     utcdate,
+            #      zoneoffset,
+            #     subject.encode('utf-8'), fromname.encode('utf-8'),
+            #     fromaddr.encode('utf-8'),'','',
+            #     thread_root,thread_parent))
+            cursor.execute("DELETE FROM mailarc where messageid=%s",(messageid,))
+            cursor.execute("INSERT INTO mailarc ("
+                "id,category,messageid,utcdate,zoneoffset,subject,"
+                "fromname,fromaddr,header,text, threadroot,threadparent ) "
+                "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
+                (id, category, messageid, utcdate, zoneoffset, subject,
+                 fromname, fromaddr,'',body, thread_root,thread_parent))
+
+        db.commit()
+
     def do_refresh_category(self,line):
         db = self.db_open()
         self.env = self.env_open()
             cursor2.execute("INSERT INTO mailarc_category (category,mlid,yearmonth,count) VALUES(%s,%s,%s,%s)",(category,category[:-6],category[-6:],cnt))
         db.commit()
 
-    ## Help
-    _help_import = [('import <mlname> <filepath>', 'import UnixMail')]
+    def _do_import(self, mlname, filepath):
+        @self.env.with_transaction()
+        def do_import(db):
+            self._import_unixmailbox('cmd', db, mlname, filepath)
 
-    def do_import(self, line):
-        arg = self.arg_tokenize(line)
-        if len(arg) < 2 :
-            self.print_error(_('import MLname filepath'))
-            return
-        
-        db = self.db_open()
-        self.env = self.env_open()
-        self._import_unixmailbox('cmd', db, arg[0], arg[1])
+    def _do_pop3(self, mlname):
+        @self.env.with_transaction()
+        def do_pop3(db):
+            self._import_from_pop3('cmd', db, mlname)
 
-    ## Help
-    _help_pop3 = [('pop3 <mlname>', 'import from pop3 server')]
+    def print_info(self,line):
+        print "%s" % line
 
-    def do_pop3(self, line):
-        arg = self.arg_tokenize(line)
-        if len(arg) < 1 :
-            printerr("pop3 MLname")
-        db = self.db_open()
-        self.env = self.env_open()
-        self._import_from_pop3('cmd', db, arg[0])
-        
-    ## Help
-    _help_updatedb = [('updatedb', 'update db for new version plugin.')]
-
-    def do_updatedb(self, line):
-        db = self.db_open()
-        self.env = self.env_open()
-        
-        from model import MailFinder
-        
-        mails = MailFinder.find_not_root(self.env)
-        
-        cursor = db.cursor()
-        for mail in mails:
-            root_id = mail.get_thread_root().messageid
-            sql = "UPDATE mailarc SET threadroot = %s WHERE messageid = %s" 
-            printout(_('root_id=%s, messageid=%s' % (root_id, mail.messageid)))
-            if root_id == mail.messageid:
-                #自分が親(親メッセージIDのメールがDBに存在しない)場合、更新しない
-                continue
-            cursor.execute(sql, (root_id, mail.messageid))
-            self.print_info('%s' % sql)
-            
-        db.commit()
-
-    ## Help
-    _help_help = [('help', 'Show documentation')]
-
-    def do_help(self, line=None):
-        arg = self.arg_tokenize(line)
-        if arg[0]:
-            try:
-                doc = getattr(self, "_help_" + arg[0])
-                self.print_doc (doc)
-            except AttributeError:
-                printerr(_("No documentation found for '%(cmd)s'", cmd=arg[0]))
-        else:
-            printout(_("mailarc-admin - The Trac MailArchivePlugin Administration Console "
-                       "%(version)s", version=PLUGIN_VERSION))
-            if not self.interactive:
-                print
-                printout(_("Usage: mailarc-admin </path/to/projenv> "
-                           "[command [subcommand] [option ...]]\n")
-                    )
-            self.print_doc(self.all_docs())
-
-    def print_info(self, line):
-        printout(line)
-
-    def print_debug(self, line):
+    def print_debug(self,line):
         #print "[Debug] %s" % line
         pass
 
-    def print_error(self, line):
-        printout("[Error] %s" % line)
+    def print_error(self,line):
+        print "[Error] %s" % line
 
-    def print_warning(self, line):
-        printout("[Warning] %s" % line)
-        
-    def print_import_error(self, msg):
-        self.print_error(_('Exception At Message-ID:%(messageid)s,\
-                            Subject:%(subject)s', messageid=msg['message-id'],
-                            subject=msg['Subject']))
+    def print_warning(self,line):
+        print "[Warning] %s" % line
 
-    def _import_unixmailbox(self, author, db, mlid, msgfile_path):
+    def _import_unixmailbox(self,author, db, mlid, msgfile_path):
         self.print_debug('import_mail')
-        if not db:
-            #db = self.env.get_db_cnx()
-            handle_ta = True
-        else:
-            handle_ta = False
 
-        printout(_("%(time)s Start Importing %(file_path)s ...",
-                   time=time.strftime("%Y/%m/%d %H:%M:%S", time.gmtime()),
-                  file_path=msgfile_path))
+        #paser = Parser()
 
-        fp = open(msgfile_path,"rb")
-        mbox = mailbox.UnixMailbox(fp, self.msgfactory)
+        self.print_info("%s Start Importing %s ..." %