ticket_email_process

ticket_email_process   db   message

Takes an incoming message and inserts a message into the ticket system and notifies the relevant users.

Source code:

    
    # extract the headers 
    set from_addr ""
    set date ""
    set subject ""
    set msgtext ""
    set msg_id ""
    set reply_to ""

    # We want to grab headers for 
    # Date: Thu, 11 Mar 1999 01:42:24 -0500
    # From: Henry Minsky <hqm@ai.mit.edu>
    # Subject: Re: test message

    set parsed_msg [parse_email_message $message]

    set msgbody        [ns_set iget $parsed_msg "message_body"]
    set from_header    [ns_set iget $parsed_msg "from"]
    set subject [ns_set iget $parsed_msg "subject"]
    set date_header    [ns_set iget $parsed_msg "date"]
    set reply_to       [ns_set iget $parsed_msg "reply-to"]
    set to_header       [ns_set iget $parsed_msg "to"]
    set cc_header       [ns_set iget $parsed_msg "cc"]


    # look for address of form "Reply-To: foo@bar.com" since Reply-To if present is 
    # generally canonical.

    if {![regexp -nocase  "(\[A-Za-z0-9._/%&!-\]+@\[A-Za-z0-9.-\]+)" $reply_to from_line from_addr]} {
	regexp -nocase  "(\[^<\]*)<(\[A-Za-z0-9._/%&!-\]+@\[A-Za-z0-9.-\]+)" $reply_to from_line from_name from_addr
    }
    if {[empty_string_p $from_addr]} { 
        # if we did not get Reply-To resort to From for the users address.
        if {![regexp -nocase  "(\[A-Za-z0-9._/%&!-\]+@\[A-Za-z0-9.-\]+)" $from_header from_line from_addr]} {
            regexp -nocase  "(\[^<\]*)<(\[A-Za-z0-9._/%&!-\]+@\[A-Za-z0-9.-\]+)" $from_header from_line from_name from_addr
        }
    }

    if {[empty_string_p $from_addr]} {
	ns_log Notice "ticket_email_process could not parse from_addr from incoming message header: |$from_header| message=|$message|"
    }

    # Figure out the user_id and msg_id from the From info.
    set to_addr {}

    if {![regexp -nocase  "(\[A-Za-z0-9._/%&!-\]+@\[A-Za-z0-9.-\]+)" $to_header to_line to_addr]} {
	regexp -nocase  "(\[^<\]*)<(\[A-Za-z0-9._/%&!-\]+@\[A-Za-z0-9.-\]+)" $to_header to_line to_name to_addr
    }

    if {[empty_string_p $to_addr]} {
        # we bail here since w/o to_addr we do not know where to put it.
	ns_log Notice "EVIL MESSAGE ticket_email_process could not parse to_addr from incoming message header: |$to_header| message=|$message|"
	return
    }


    # figure out which ticket to book the comment on.
    set msg_id {} 
    set user_id {} 

    if {[regexp -nocase {ticket-([0-9]+)-([0-9]+)@} $to_addr match msg_id user_id] 
        || [regexp -nocase {ticket-([0-9]+)-([0-9]+)@} $cc_header match msg_id user_id] } { 
        # if we get anything back the user and msg exist
        set test [ns_db 0or1row $db "select msg_id from ticket_issues where msg_id = $msg_id"]
        if {[empty_string_p $test]} { 
            ns_log Notice "ticket_email_process could not find ticket $msg_id message=|$message|"
            return
        }
        set selection [ns_db 0or1row $db "select first_names || ' ' || last_name || ' ' || email as who from users where user_id = $user_id"]
        if {[empty_string_p $selection]} { 
            ns_log Notice "ticket_email_process could not find user_id $user_id message=|$message|"
            set user_id {} 
            set who $from_header
        } else { 
            set_variables_after_query
        }
    } else {  
        set who $from_header
    }

    
    
    # We need to have some user_id we can use as the author of the comment
    if {[empty_string_p $user_id]} {
        # We try to look up a user, based on their from address
	set user_id [database_to_tcl_string_or_null $db "select user_id from users where lower(email) = '[string tolower $from_addr]'"]
        
        if {[empty_string_p $user_id]} {
            # failing that we put it in as system but include the headers since 
            set user_id [database_to_tcl_string $db "select system_user_id from dual"]
            
            # Make a cleaner looking mail message, just reconstruct a couple of the headers
            append msgtext "From: <strong>[ns_quotehtml $from_header]</strong><br>"
            if {![empty_string_p $reply_to]} {
                append msgtext "Reply-to: <strong>[ns_quotehtml $reply_to]</strong><br>"
            }
            if {![empty_string_p $to_header]} {
                append msgtext "To: <strong>[ns_quotehtml $to_header]</strong><br>"
            }
            if {![empty_string_p $cc_header]} {
                append msgtext "CC: <strong>[ns_quotehtml $cc_header]</strong><br>"
            }
            if {![empty_string_p $date_header]} {
                append msgtext "Date: [ns_quotehtml $date_header]<br>"
            }

            append who " (unregistered saved as system user)"

            ns_log Notice "Could not find registered user $from_addr, using system user user_id=$user_id"

        }
        ns_log Notice "Could not find registered user for $to_addr, using user_id=$user_id"
    }

    if {[empty_string_p $subject]} { 
        set subject "To: [ns_quotehtml $to_header]"
    }

    append msgtext "<pre>$msgbody</pre>"

    if {[empty_string_p $msg_id]} { 
        ns_log Notice "No ticket msg_id found for message:\n-------\n$message\n--------\n"

    } else {
        # as long as we have a message id we should insert 
        # the message.

        with_transaction $db {
            set comment_id [database_to_tcl_string $db "select general_comment_id_sequence.nextval from dual"]
            ad_general_comment_add $db $comment_id {ticket_issues} $msg_id "ticket \#$msg_id" $msgtext $user_id "000.000.000.000" {t} {t} {}
        } { 
            ns_log Notice "Insert of comment failed $errmsg"
        }
    
        if {[catch {ticket_notify $db comment $msg_id $subject "Comment from $who\n\n$msgbody" $user_id} errmsg]} { 
            ns_log notice "Error notifying for Message:\n$message\n\nincoming ticket mail.  Error:\n$errmsg"
        }
    }
    
    return {}