Support Cases Tracking

From ESM Wiki
Jump to: navigation, search

Contents

Introduction

This document provides an example implementation of a simple CRM function that automatically relates emails to support cases on SMTP transport level. Implemented use-case is the following:

  • check whether email is to/from support;
  • if email to/from support address then search for ticket ID in email subject or body;
  • if ticket ID is not found in subject or body or corresponding case is not found in DB then create new case, relate email to new case and add ticket ID in email subject;
  • if ticket ID is found and corresponding case is found in DB then relate email with this case.

Database structure

Implementation uses the following MySQL database structure:

CREATE TABLE `mailspect_case_object` (
    `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    
    `subject`           VARCHAR(255) CHARACTER SET utf8 NOT NULL,
    `description`       VARCHAR(4096) CHARACTER SET utf8 NOT NULL,
    
    PRIMARY KEY (`id`)
)  CHARSET=latin1 ENGINE=INNODB;

CREATE TABLE `email_item` (
    `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    
    `from`              VARCHAR(2048) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `to`                VARCHAR(2048) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `cc`                VARCHAR(2048) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `bcc`               VARCHAR(2048) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `subject`           VARCHAR(2048) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `date`              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `size`              INT UNSIGNED NOT NULL DEFAULT 0,
    `uri`               VARCHAR(4096) NOT NULL DEFAULT '',
        
    PRIMARY KEY (`id`)
)  CHARSET=latin1 ENGINE=INNODB;

CREATE TABLE `relation` (
    `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,

    `local`             VARCHAR(1024) NOT NULL DEFAULT '',
    `reverse_domain`    VARCHAR(1024) NOT NULL DEFAULT '',
    
    `object_type`       VARCHAR(255) NOT NULL,
    `object_id`         INT UNSIGNED NOT NULL,
    `object_name`       VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
    
    `relation_is_new`   BOOL NOT NULL DEFAULT 1,
    
    `item_type`         VARCHAR(255) NOT NULL,
    `item_id`           INT UNSIGNED NOT NULL,
    `item_name`         VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `item_direction`    ENUM ('Incoming', 'Outgoing'),
        
    PRIMARY KEY (`id`)
)  CHARSET=latin1 ENGINE=INNODB;

ESM config full listing

ESM config defines processing logic. Full listing is provided bellow and detailed step-by-step explanation is provided in subsequent sections.

<?xml version="1.0"?>
<mppd>
    <common>
        <email_server>smtp</email_server>
        <email_server_in_protocol>lmtp</email_server_in_protocol>
        <passwords>/usr/local/MPP/passwords.txt</passwords>
    </common>
    <groups>
        <group id="default">
            <scanner>
                <scan_engines/>
            </scanner>
            <strip_body_enabled>yes</strip_body_enabled>
            <strip_body_engines>
                extract_case
                db.select_case
                db.insert_case
                disk.save_email
                db.insert_email
                make_relation_case_email_fields
                db.insert_relation_case_email_for_sender
                db.insert_relation_case_email_for_recipient
            </strip_body_engines>
            <strip_body_replacers target="headers.subject">engines.db.insert_case.marked_subject</strip_body_replacers>
            <strip_body_exception_engine>
                log_exception
            </strip_body_exception_engine>
        </group>
    </groups>
    <engines>
        <boorex id="extract_case">
            <precondition>
                    $sender     $EQ support@company.com
                $OR $recipient  $EQ support@company.com
            </precondition>
            <expression options="*,icase">(?:^|\\W)MSX([\\d]\{10\})CS(?:\$|\\W)</expression>
            <data>$headers.subject $body $body.html</data>
            <result id="id">
                <result>${boorex_first_matched_value 1}</result>
            </result>
        </boorex>
        <boorex id="make_relation_case_email_fields">
            <result id="object_id">
                <result>${not_empty $engines.db.select_case.id $engines.db.insert_case.id}</result>
            </result>
            <result id="object_name">
                <result>${text_truncate
                    {Case #${not_empty
                                $engines.db.select_case.id
                                $engines.db.insert_case.id
                            }: ${not_empty
                                $engines.db.select_case.subject
                                $engines.db.insert_case.subject
                    }}
                    255
                    ...
                }</result>
            </result>
            <result id="item_name">
                <result>${text_truncate ${not_empty $headers.subject $body} 255 ...}</result>
            </result>
        </boorex>
        <boorex id="log_exception">
            <result>
                <result>$empty</result>
                <log level="error">[support_case_tracking] Failed: $exception</log>
            </result>
        </boorex>
        <mysql id="db">
            <connection>
                <database>support_case_tracking</database>
                <defaults_file>/etc/my.cnf</defaults_file>
                <user>mppd</user>
                <password_id>mysql_mppd_password</password_id>
            </connection>
            <query id="select_case">
                <precondition>$engines.extract_case.id $NE $empty</precondition>
                <template>
                    SELECT `id`, `subject` FROM `mailspect_case_object`
                    WHERE `id`=$engines.extract_case.id
                </template>
                <result id="id">
                    <if_empty_table>
                        <result>$empty</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>${mysql_field id}</result>
                    </if_filled_table>
                </result>
                <result id="subject">
                    <if_empty_table>
                        <result>$empty</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>${mysql_field subject}</result>
                    </if_filled_table>
                </result>
            </query>
            <query id="insert_case">
                <precondition>
                        $engines.extract_case.id                        $EQ $empty
                    $OR ${not_empty $engines.db.select_case.id $empty}  $EQ $empty
                </precondition>
                <template>
                    INSERT INTO `mailspect_case_object` (`subject`, `description`)
                    VALUES (
                                '${mysql_escape ${text_truncate ${not_empty $headers.subject $body} 255 ...}}'
                            ,   '${mysql_escape ${text_truncate $body 4096 ...}}'
                            )
                </template>
                <result id="id">
                    <if_empty_table>
                        <result>$mysql_insert_id</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>$mysql_insert_id</result>
                    </if_filled_table>
                </result>
                <result id="subject">
                    <if_empty_table>
                        <result>${text_truncate ${not_empty $headers.subject $body} 255 ...}</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>${text_truncate ${not_empty $headers.subject $body} 255 ...}</result>
                    </if_filled_table>
                </result>
                <result id="marked_subject">
                    <if_empty_table>
                        <result>$headers.subject (Ticket# MSX${text_pad_left $mysql_insert_id 10 0}CS)</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>$headers.subject (Ticket# MSX${text_pad_left $mysql_insert_id 10 0}CS)</result>
                    </if_filled_table>
                </result>
            </query>
            <query id="insert_email">
                <template><![CDATA[
                    INSERT INTO `email_item` (`from`, `to`, `cc`, `bcc`, `subject`, `date`, `size`, `uri`)
                    VALUES (
                                '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.from.name $sp}<$headers.from>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.to.name $sp}<$headers.to>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.cc.name $sp}<$headers.cc>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.bcc.name $sp}<$headers.bcc>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate $headers.subject 2048 ...}}'
                            ,   ${not_empty ${if_not_empty FROM_UNIXTIME( $headers.date.timestamp )} NOW()}
                            ,   ${size $data}
                            ,   '${mysql_escape file://$engines.disk.save_email.full.path}'
                            )
                ]]></template>
                <result id="id">
                    <if_empty_table>
                        <result>$mysql_insert_id</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>$mysql_insert_id</result>
                    </if_filled_table>
                </result>
            </query>
            <query id="insert_relation_case_email_for_sender">
                <template>
                    INSERT INTO `relation` (
                                `local`, `reverse_domain`
                            ,   `object_type`, `object_id`
                            ,   `object_name`
                            ,   `relation_is_new`
                            ,   `item_type`, `item_id`
                            ,   `item_name`, `item_direction`
                                            )
                    VALUES (
                                '${mysql_escape $sender.local}', REVERSE('${mysql_escape $sender.domain}')
                            ,   'mailspect_case_object', $engines.make_relation_case_email_fields.object_id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.object_name}'
                            ,   1
                            ,   'email_message', $engines.db.insert_email.id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.item_name}', 'Outgoing'
                           )
                </template>
            </query>
            <query id="insert_relation_case_email_for_recipient">
                <template>
                    INSERT INTO `relation` (
                                `local`, `reverse_domain`
                            ,   `object_type`, `object_id`
                            ,   `object_name`
                            ,   `relation_is_new`
                            ,   `item_type`, `item_id`
                            ,   `item_name`, `item_direction`
                                            )
                    VALUES (
                                '${mysql_escape $recipient.local}', REVERSE('${mysql_escape $recipient.domain}')
                            ,   'mailspect_case_object', $engines.make_relation_case_email_fields.object_id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.object_name}'
                            ,   1
                            ,   'email_message', $engines.db.insert_email.id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.item_name}', 'Incoming'
                           )
                </template>
            </query>
        </mysql>
        <file id="disk">
            <save id="save_email">
                <precondition>
                        ${not_empty $engines.db.select_case.id $empty}      $NE $empty
                    $OR ${not_empty $engines.db.insert_case.id $empty}      $NE $empty
                </precondition>
                <base>
                    <path>/var/archive/data</path>
                </base>
                <tmp>
                    <path>/var/archive/tmp/mppd_$pid$time6$counter16</path>
                </tmp>
                <relative>
                    <path>${year}_${month}_${day}/${hour}_${minute}/$time6$counter16</path>
                </relative>
                <data>$data</data>
            </save>
        </file>
    </engines>
</mppd>

Email server

Interaction with email server is defined by two options:

        <email_server>smtp</email_server>
        <email_server_in_protocol>lmtp</email_server_in_protocol>

Application accepts emails on localhost:10025 via LMTP protocol and passes processed emails to localhost:10026 via SMTP.

Passwords

Pass to passwords file is defined with:

        <passwords>/usr/local/MPP/passwords.txt</passwords>

Passwords file contains pairs of password id/value used by application for different purposes. For the need of this example passwords file must contain an entry with id mysql_mppd_password and valid password value for MySQL DB for user mppd. For example:

mysql_mppd_password qwerty1223

Default policy group

Default (and the only) processing policy group is defined with:

    <groups>
        <group id="default">
           ...
        </group>
    </groups>

Scan engines

For simplicity empty list of scan engines is specified:

                <scan_engines/>

Though it is possible to use any standard scan engine(s) (virus/spam filters, etc.).

Processing pipeline

Strip body pipeline is used for processing because it is executed after all other processing and only if email is to be passed further. To enable the pipeline the following option is used:

            <strip_body_enabled>yes</strip_body_enabled>

Pipeline engines

Sequence of engines (operations) that are executed in turns is defined with:

            <strip_body_engines>
                extract_case
                db.select_case
                db.insert_case
                disk.save_email
                db.insert_email
                make_relation_case_email_fields
                db.insert_relation_case_email_for_sender
                db.insert_relation_case_email_for_recipient
            </strip_body_engines>

Engines are reference using their full id (extract_case, db.select_case, etc.).

Extract_case engine

Engine extract_case is a boorex engine that searches for ticket ID in email subject and body and extracts case ID. It is defined in <engines> section as following:

        <boorex id="extract_case">
            <precondition>
                    $sender     $EQ support@company.com
                $OR $recipient  $EQ support@company.com
            </precondition>
            <expression options="*,icase">(?:^|\\W)MSX([\\d]\{10\})CS(?:\$|\\W)</expression>
            <data>$headers.subject $body $body.html</data>
            <result id="id">
                <result>${boorex_first_matched_value 1}</result>
            </result>
        </boorex>

Engine's precondition checks whether email SMTP sender or one of recipients is a support address support@company.com. One should change this value there to correspond to company-specific address. Failed precondition causes engine action execution and result calculation to be skipped.

Option <expression> defines a regular expression to be used to match ticket ID and extract case ID. Ticket ID is constructed to contain needed information and to have a signature that is unlikely to appear in usual communication. In current example ticket ID is constructed from concatenation of "MSX" prefix string, 10 decimal digits of case ID and "CS" suffix. Attribute options="*,icase" defines expression syntax and matching options. It uses default syntax (*) plus case-insensitivity flag (icase). Default syntax is perl-style regular expressions. Note that <expression> option value is a template thus escapes must be taken into account. For example for symbol '\' to appear in regular expression it must be specified escaped: "\\". Same rule for '{', '}' and '$'.

Option <data> defines text data to be used for regular expression matching. It is a concatenation of space-separated email subject, text and html bodies.

Engine exports one result with id="id". Result is a value of first sub-match (index 1) as returned by $boorex_first_matched_value macro. If expression is not matched the value will be empty string.

Db engine

Engine db is an aggregator of methods (such as db.select_case, db.insert_case, etc.) to perform different MySQL queries. The engine is defined with:

        <mysql id="db">
            ...
        </mysql>

Methods share same database connection properties defined with:

            <connection>
                <database>support_case_tracking</database>
                <defaults_file>/etc/my.cnf</defaults_file>
                <user>mppd</user>
                <password_id>mysql_mppd_password</password_id>
            </connection>

Option <password_id> defines ID of a password from passwords file.

Db.select_case engine

Engine db.select_case is a method of MySQL db engine that searches for a case with extracted ID in the database. Method is defined with:

            <query id="select_case">
                <precondition>$engines.extract_case.id $NE $empty</precondition>
                <template>
                    SELECT `id`, `subject` FROM `mailspect_case_object`
                    WHERE `id`=$engines.extract_case.id
                </template>
                <result id="id">
                    <if_empty_table>
                        <result>$empty</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>${mysql_field id}</result>
                    </if_filled_table>
                </result>
                <result id="subject">
                    <if_empty_table>
                        <result>$empty</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>${mysql_field subject}</result>
                    </if_filled_table>
                </result>
            </query>

Engine's precondition checks whether case ID was extracted by previous extract_case engine. For this purpose it uses $engines.extract_case.id variable in conditional expression and compares it with empty string using special $empty macro.

MySQL query template is specified with <template> option. It uses $engine.extract_case.id variable to include extracted case ID into query.

There are two results of the engine: id and subject. They correspond to fields of a selected row. Fields are extracted using $mysql_field macro in <if_filled_table> branches. In case matched row is not found <if_empty_table> branches are taken that sets both results to empty strings.

Db.insert_case engine

Engine db.insert_case is a method of MySQL db engine that insert an entry for a new case into database. Method is defined with:

            <query id="insert_case">
                <precondition>
                        $engines.extract_case.id                        $EQ $empty
                    $OR ${not_empty $engines.db.select_case.id $empty}  $EQ $empty
                </precondition>
                <template>
                    INSERT INTO `mailspect_case_object` (`subject`, `description`)
                    VALUES (
                                '${mysql_escape ${text_truncate ${not_empty $headers.subject $body} 255 ...}}'
                            ,   '${mysql_escape ${text_truncate $body 4096 ...}}'
                            )
                </template>
                <result id="id">
                    <if_empty_table>
                        <result>$mysql_insert_id</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>$mysql_insert_id</result>
                    </if_filled_table>
                </result>
                <result id="subject">
                    <if_empty_table>
                        <result>${text_truncate ${not_empty $headers.subject $body} 255 ...}</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>${text_truncate ${not_empty $headers.subject $body} 255 ...}</result>
                    </if_filled_table>
                </result>
                <result id="marked_subject">
                    <if_empty_table>
                        <result>$headers.subject (Ticket# MSX${text_pad_left $mysql_insert_id 10 0}CS)</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>$headers.subject (Ticket# MSX${text_pad_left $mysql_insert_id 10 0}CS)</result>
                    </if_filled_table>
                </result>
            </query>

Engine's precondition checks if there is no ticket ID in a email or case ID is not found in the database. First comparison:

$engines.extract_case.id                        $EQ $empty

in the precondition is met if there is no ticket ID in a email. Also it brings precondition chaining principle into play, i.e. if $engines.extract_case.id result is NULL (not calculated) due to failed precondition of previous extract_case engine then current db.insert_case engine is skipped and its result get NULL values too. Second comparison:

${not_empty $engines.db.select_case.id $empty}  $EQ $empty

in the precondition is met if case ID is not found in the database though ticket ID is found in a email. In contrast with first comparison here precondition chaining is harmful an is suppressed with special trick ${not_empty ... $empty}. This construct maps NULL's to just empty strings using special properties of $not_empty macro. The net result is that current engine is not skipped even if execution has come throug branch where db.select_case was skipped.

Query template specifies INSERT statement that creates new case with specified subject and description. Subject is composed from email's subject and description - from email's body text. Macro $text_truncate limits number of charactes to the maximum length of a corresponding column. If length is larger then value is truncated to a lower length with "..." at the end. MySQL escaping using $mysql_escape macro and quoting using single quotes is highly recomended for values that are originated from an email to avoid MySQL injection security flaws.

The engine exports three results. First result is an inserted record id obtained with $mysql_insert_id macro. Second result is a new case subject constructed same way as in query template but without escaping and qouting. Third result is a modified original subject with ticket ID appended to the end. This result will be used by postprocessor to replace original subject in an email.

Disk.save_email engine

Engine disk.save_email is a method of file engine that saves email data to local filesystem (disk). It is specified with:

        <file id="disk">
            <save id="save_email">
                <precondition>
                        ${not_empty $engines.db.select_case.id $empty}      $NE $empty
                    $OR ${not_empty $engines.db.insert_case.id $empty}      $NE $empty
                </precondition>
                <base>
                    <path>/var/archive/data</path>
                </base>
                <tmp>
                    <path>/var/archive/tmp/mppd_$pid$time6$counter16</path>
                </tmp>
                <relative>
                    <path>${year}_${month}_${day}/${hour}_${minute}/$time6$counter16</path>
                </relative>
                <data>$data</data>
            </save>
        </file>

Engine's precondition is checks if case ID where found in the database or new case where created. Again construct ${not_empty ... $empty} is used to suppress precondition chaining and merge two branches of execution.

Full path to save file to is composed from base path:

                <base>
                    <path>/var/archive/data</path>
                </base>

and relative path:

                <relative>
                    <path>${year}_${month}_${day}/${hour}_${minute}/$time6$counter16</path>
                </relative>

that use self-explained macros: $year, $month, $day, $hour, $minute, $second, $time6 and method-specific $counter16. These macros are used to generate unique file names. Writing is done through writing to and relinking of temporary file:

                <tmp>
                    <path>/var/archive/tmp/mppd_$pid$time6$counter16</path>
                </tmp>

File data is specified with <data> option and $data macro which represents whole email data.

The engine exports implicit result with id full.path that is used further as $engines.disk.save_email.full.path variable.

Db.insert_email engine

Engine db.insert_email records email metadata into the database. It is defined with:

            <query id="insert_email">
                <template><![CDATA[
                    INSERT INTO `email_item` (`from`, `to`, `cc`, `bcc`, `subject`, `date`, `size`, `uri`)
                    VALUES (
                                '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.from.name $sp}<$headers.from>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.to.name $sp}<$headers.to>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.cc.name $sp}<$headers.cc>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate ${not_empty ${wrap $#{, } {${if_not_empty $empty $headers.bcc.name $sp}<$headers.bcc>}} $empty} 2048 ...}}'
                            ,   '${mysql_escape ${text_truncate $headers.subject 2048 ...}}'
                            ,   ${not_empty ${if_not_empty FROM_UNIXTIME( $headers.date.timestamp )} NOW()}
                            ,   ${size $data}
                            ,   '${mysql_escape file://$engines.disk.save_email.full.path}'
                            )
                ]]></template>
                <result id="id">
                    <if_empty_table>
                        <result>$mysql_insert_id</result>
                    </if_empty_table>
                    <if_filled_table>
                        <result>$mysql_insert_id</result>
                    </if_filled_table>
                </result>
            </query>

There is no engine's precondition but due to preconditions chaining this engine is executed only if disk.save_email was executed.

Query template uses $wrap macro to make a list of comma-separated values of dimensional macros: $headers.from.name, $headers.from, etc. Constructs ${not_empty ... $empty} are needed to map NULL values to empty strings in case there are no value of dimensional macros when corresponding headers are absent in email. Special macro $if_not_empty is used to surround not empty values with prefix and/or suffix. For empty values this macro returns empty string - no surrounding occurs. For `date` column $not_empty macro is used to replace empty value of $headers.date.timestamp macro with default NOW() statement. For `uri` column $engines.disk.save_email.full.path result from previous engine is used to make local file URI. Note that option value is surrounded with <![CDATA[ ... ]]> counstruct needed to correctly handle XML character data that contains special XML characters such as '<' and '>'.

Engine exports inserted record ID using $mysql_insert_id macro.

Make_relation_case_email_fields engine

Engine make_relation_case_email_fields is a helper engine that serves as merge node for different execution branches. It is defined with:

        <boorex id="make_relation_case_email_fields">
            <precondition>${not_empty $engines.db.select_case.id $engines.db.insert_case.id} $NE $empty</precondition>
            <result id="object_id">
                <result>${not_empty $engines.db.select_case.id $engines.db.insert_case.id}</result>
            </result>
            <result id="object_name">
                <result>${text_truncate
                    {Case #${not_empty
                                $engines.db.select_case.id
                                $engines.db.insert_case.id
                            }: ${not_empty
                                $engines.db.select_case.subject
                                $engines.db.insert_case.subject
                    }}
                    255
                    ...
                }</result>
            </result>
            <result id="item_name">
                <result>${text_truncate ${not_empty $headers.subject $body} 255 ...}</result>
            </result>
        </boorex>

Engine's precondition checks whether one of needed branches was executed. Macro $not_empty is used to select a value from an execution branch. If all listed values are NULL then macro returns NULL value and the engine is skipped due to precondition chaining principle.

Engine exports three results that are used further to create relation entries.

Db.insert_relation_case_email_for_sender engine

Engine db.insert_relation_case_email_for_sender inserts a relation between case object and email item for a user specified as SMTP sender. Engine is defined with:

            <query id="insert_relation_case_email_for_sender">
                <template>
                    INSERT INTO `relation` (
                                `local`, `reverse_domain`
                            ,   `object_type`, `object_id`
                            ,   `object_name`
                            ,   `relation_is_new`
                            ,   `item_type`, `item_id`
                            ,   `item_name`, `item_direction`
                                            )
                    VALUES (
                                '${mysql_escape $sender.local}', REVERSE('${mysql_escape $sender.domain}')
                            ,   'mailspect_case_object', $engines.make_relation_case_email_fields.object_id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.object_name}'
                            ,   1
                            ,   'email_message', $engines.db.insert_email.id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.item_name}', 'Outgoing'
                           )
                </template>
            </query>

Fields `local` and `reverse_domain` identify a user and are set using $sender.local and $sender.domain macros.

Db.insert_relation_case_email_for_recipient engine

Engine db.insert_relation_case_email_for_recipient inserts a relation between case object and email item for a user specified as SMTP recipient. Engine is defined with:

            <query id="insert_relation_case_email_for_recipient">
                <template>
                    INSERT INTO `relation` (
                                `local`, `reverse_domain`
                            ,   `object_type`, `object_id`
                            ,   `object_name`
                            ,   `relation_is_new`
                            ,   `item_type`, `item_id`
                            ,   `item_name`, `item_direction`
                                            )
                    VALUES (
                                '${mysql_escape $recipient.local}', REVERSE('${mysql_escape $recipient.domain}')
                            ,   'mailspect_case_object', $engines.make_relation_case_email_fields.object_id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.object_name}'
                            ,   1
                            ,   'email_message', $engines.db.insert_email.id
                            ,   '${mysql_escape $engines.make_relation_case_email_fields.item_name}', 'Incoming'
                           )
                </template>
            </query>

Fields `local` and `reverse_domain` identify a user and are set using $recipient.local and $recipient.domain macros. Due to implicit iteration principle the query is repeated for each SMTP recipient.

Subject replacer

Back to default policy group. Subject replacer is defined with:

            <strip_body_replacers target="headers.subject">engines.db.insert_case.marked_subject</strip_body_replacers>

It is a pipeline's replacer postprocessor that targets email subject. Option's value defines from which variable to get value for new subject. If value is NULL subject replacement won't be done.

Errors processing engines

Errors processing engines sequences is defined with:

            <strip_body_exception_engine>
                log_exception
            </strip_body_exception_engine>

This sequence is executed if the pipeline terminates with an error (exception).

Log_exception engine

This engine just logs error message using $exception macro that is available only in exception engines.

Result of an engine is a dummy (not used) value.

Personal tools