process.js

import("system.logging");
import("system.entities");
import("MarketingCondition_lib");
import("system.fileIO");
import("system.project");
import("Util_lib");
import("system.translate");
import("ActivityTask_lib");
import("system.util");
import("Contact_lib");
import("system.datetime");
import("system.neon");
import("Employee_lib");
import("system.vars");
import("KeywordRegistry_basic");
import("Sql_lib");
import("system.db");
import("DocumentTemplate_lib");
import("Communication_lib");
import("Email_lib");
import("system.process");
import("system.notification");
import("Document_lib");
import("system.db");
import("system.workflow");
import("system.logging");

/**
 * Functions for bulk mails.
 */
function BulkMailUtils () {}

/**
 * Executes a process to send bulk mails on the server and creates a notification when finished.
 * 
 * @param {String} pBulkMailId                      <p>
 *                                                  Id of the bulk mail.<br>
 * @param {Bool} pTestRun (optional)                <p>
 *                                                  True indicates a Testrun<br>
 * @param {String} pUser=currentUser (optional)     <p>
 *                                                  User that will get the notification, <br>
 *                                                  if null (not undefined!), no notification<br>
 *                                                  will be created.<br>
 */
BulkMailUtils.sendBulkMailOnServer = function (pBulkMailId, pTestRun, pUser)
{
    if (pUser === undefined)
        pUser = EmployeeUtils.getCurrentUserId();
    
    var processConfig = process.createStartAsyncConfig()
        .setName("sendBulkMail_serverProcess")
        .setLocalVariables({
            bulkMailId : pBulkMailId,
            testRun : pTestRun,
            user : pUser || ""
        })
        .setUser(pUser||"mailbridge")
    process.startAsync(processConfig);
}

/**
 * Sends a bulk mail. You should only call this function on the server because it
 * can take some time to execute, use BulkMailUtils.sendBulkMailOnServer instead.
 * 
 * @param {String} pBulkMailId                  <p>
 *                                              Id of the bulk mail.<br>
 * @param {Bool} pIsTestRun (optional)            <p>
 *                                              True indicates a Testrun<br>
 * @return {Object}                             <p>
 *                                              Count of sucessful and failed mails.<br>
 */
BulkMailUtils.sendBulkMail = function (pBulkMailId, pIsTestRun)
{
    if (pIsTestRun == undefined)
    {
        pIsTestRun = false;
    }
    
    var [templateId, subject, emailSender, createActivity, bulkMailName, useTemplateAttachments] = 
                newSelect("DOCUMENTTEMPLATE_ID, SUBJECT, SENDER_EMAIL_ADDRESS, CREATEACTIVITIES, NAME, USE_TEMPLATE_ATTACHMENTS")
                    .from("BULKMAIL")
                    .where("BULKMAIL.BULKMAILID", pBulkMailId)
                    .arrayRow();

    useTemplateAttachments = Utils.toBoolean(useTemplateAttachments);
        
    var template = BulkMailUtils.getBulkMailTemplate(pBulkMailId, templateId, true, useTemplateAttachments);
    var recipientData;
    var testRecipientData;
    
    var recipientLoadConfig = entities.createConfigForLoadingRows()
        .fields(["BULKMAILRECIPIENTID", "CONTACT_ID", "EMAIL_ADDRESS", "PERSON_ID", "ORGANISATION_ID"])
        .entity("BulkmailRecipient_entity")
        .provider("RecipientsToBeMailed")
        .addParameter("BulkMailId_param", pBulkMailId)
        .addParameter("IsTestMail_param", pIsTestRun);
    
    recipientData = entities.getRows(recipientLoadConfig);
    
    if (pIsTestRun)
    {
        testRecipientData = newSelect("BULKMAILTESTRECIPIENT.CONTACT_ID, BULKMAILTESTRECIPIENT.EMAIL_ADDRESS")
            .from("BULKMAILTESTRECIPIENT")
            .where("BULKMAILTESTRECIPIENT.BULKMAIL_ID", pBulkMailId)
            .table();
    }
    var mailrunId = util.getNewUUID();
    
    new SqlBuilder()
        .tableName("MAIL_RUN")
        .insertFields({
            "MAIL_RUNID": mailrunId,
            "OBJECT_ROWID": pBulkMailId,
            "OBJECT_TYPE": "Bulkmail",
            "DATE_RUN_START": vars.get("$sys.date"),
            "STATUS": $KeywordRegistry.bulkMailStatus$beingSent(),
            "TESTRUN": pIsTestRun ? 1 : 0
        });
    
    var mailLogIds = new Map();
    var contactIds = recipientData.map(function (recipient) 
    {
        var contactId = recipient["CONTACT_ID"]; 
        mailLogIds.set(contactId, util.getNewUUID());
        return contactId;
    });
    
    var baseUrl = vars.get("$sys.origin") + "/services/rest/redirect_rest?";
    var linkPlaceholders = newSelect(["PLACEHOLDER", "WEBLINKID", "URL", "ISREDIRECT"])
        .from("WEBLINK")
        .table()
        .map(function ([placeholder, weblinkId, url, isRedirect])
        {
            if (Utils.toBoolean(isRedirect))
            {
                var linkFn;
                //Mosaico places a url in front of the placeholder if it gets placed inside image src 
                //so we temporarily create the whole html tag with a placeholder for the opener until this is solved
                if (placeholder == "pixel")
                {
                    linkFn = function (pContactId)
                        {
                            return "<img src=\""+baseUrl + "link=" + weblinkId + "&log=" + mailLogIds.get(pContactId)+"\" alt=\"\" width=\"1\" height=\"1\" border=\"0\" hspace=\"0\" vspace=\"0\" align=\"top\">";
                        }  
                }
                else
                {
                    linkFn = function (pContactId)
                        {
                            return baseUrl + "link=" + weblinkId + "&log=" + mailLogIds.get(pContactId);
                        }  
                }
                
                return new Placeholder(placeholder, Placeholder.types.CALLBACKFUNCTION, linkFn);
            }
            return new Placeholder(placeholder, Placeholder.types.FIXEDVALUE, url);
        });
    
    var successIds = [];
    var failedIds = [];
    var sentDate = vars.get("$sys.date");
    var mails = template.getReplacedEmailsByContactIds(contactIds, linkPlaceholders);
    
    var subjectTemplate = new DocumentTemplate(subject, DocumentTemplate.types.PLAIN);
    var subjects = subjectTemplate.getReplacedContentByContactIds(contactIds);
    
    var bulkMailLink = [["BulkMail", pBulkMailId]];
    var activitySubject = translate.withArguments("Bulk mail \"%0\" sent", [bulkMailName]);
    if (!pIsTestRun)
    {
        recipientData.forEach(function (recipient)
        {
            let isSuccess = false;
            let recipientId = recipient["BULKMAILRECIPIENTID"];
            let contactId = recipient["CONTACT_ID"];
            let emailAddress = recipient["EMAIL_ADDRESS"];
            let personId = recipient["PERSON_ID"];
            let organisationId = recipient["ORGANISATION_ID"];
            let email = mails[contactId];
            let mailLogId = mailLogIds.get(contactId);
            if (email !== undefined && emailAddress)
            {
                email.toRecipients = [emailAddress];
                email.sender = emailSender;
                email.subject = subjects[contactId];

                BulkMailUtils.storeEmlFile(pBulkMailId, mailrunId, mailLogId,email.getEML());
                isSuccess = email.send();
            }

            //set the recipient status to 'sent' or 'failed'
            new SqlBuilder()
                .tableName("MAIL_LOG")
                .insertFields({
                    "MAIL_LOGID": mailLogId,
                    "MAIL_RUN_ID": mailrunId,
                    "CONTACT_ID": contactId,
                    "STATUS": isSuccess ? $KeywordRegistry.bulkMailRecipientStatus$sent() : $KeywordRegistry.bulkMailRecipientStatus$failed(),
                    "SENDER_EMAIL": emailSender,
                    "RECIPIENT_EMAIL": emailAddress,
                    "MAILING_SUBJECT": subjects[contactId],
                    "DATE_SEND": vars.get("$sys.date")
                });
                
            //TODO: Klären was von alter Logik noch bleiben soll. Status macht nur Sinn wenn jede Bulkmail nur einmal gesendet wird. Bleiben Activitys?
            Array.prototype.push.call(isSuccess ? successIds : failedIds, recipientId);
            if (isSuccess && createActivity == "1")
            {
                let activityData = {
                    categoryKeywordId : $KeywordRegistry.activityCategory$mail(),
                    directionKeywordId : $KeywordRegistry.activityDirection$outgoing(),
                    subject : activitySubject,
                    content : email.body
                };
                let contactLink = [[ContactUtils.getContextByPersOrg(personId, organisationId), contactId]];
                ActivityUtils.insertNewActivity(activityData, bulkMailLink.concat(contactLink));
            }
        });

        newWhereIfSet("BULKMAILRECIPIENT.BULKMAILRECIPIENTID", successIds, SqlBuilder.IN())
            .updateFields({
                "STATUS": $KeywordRegistry.bulkMailRecipientStatus$sent(),
                "SENTDATE": sentDate
            });

        newWhereIfSet("BULKMAILRECIPIENT.BULKMAILRECIPIENTID", failedIds, SqlBuilder.IN())
            .updateFields({
                "STATUS": $KeywordRegistry.bulkMailRecipientStatus$failed(),
                "SENTDATE": sentDate
            });

        newWhere("MAIL_RUN.MAIL_RUNID", mailrunId)
            .updateFields({
                "STATUS": $KeywordRegistry.bulkMailStatus$sent(),
                "DATE_RUN_FINISHED": vars.get("$sys.date")
            });

        newWhere("BULKMAIL.BULKMAILID", pBulkMailId)
            .updateFields({
                "STATUS": $KeywordRegistry.bulkMailStatus$sent()
            });
    }
    else
    {
        for (let i = 0, l = recipientData.length; i < l; i++)
        {
            
            let isSuccess = false;
            let contactId = recipientData[i]["CONTACT_ID"];
            let email = mails[contactId];

            if (email !== undefined)
            {
                email.sender = emailSender;
                email.subject = "Test: "+subjects[contactId];
                for (let j =0; j<testRecipientData.length;j++)
                {
                    if(testRecipientData[j][1])
                    {
                        email.toRecipients = [testRecipientData[j][1]];
                        isSuccess = email.send();

                        Array.prototype.push.call(isSuccess ? successIds : failedIds, recipientData[i]["BULKMAILRECIPIENTID"]);

                        if (testRecipientData[j][0])
                        {
                            let mailLogId = util.getNewUUID();
                            db.insertData("MAIL_LOG", ["MAIL_LOGID","MAIL_RUN_ID","CONTACT_ID","STATUS","SENDER_EMAIL","RECIPIENT_EMAIL","MAILING_SUBJECT","DATE_SEND"], null, [mailLogId,mailrunId,testRecipientData[j][0],(isSuccess ?$KeywordRegistry.bulkMailRecipientStatus$sent(): $KeywordRegistry.bulkMailRecipientStatus$failed()),emailSender,testRecipientData[j][1],email.subject,vars.get("$sys.date")]);
                            this.storeEmlFile(pBulkMailId, mailrunId, mailLogId,email.getEML());
                        }
                    }
                }

            }
        }
        newWhere("MAIL_RUN.MAIL_RUNID",mailrunId)
            .updateData(true,"MAIL_RUN",["STATUS","DATE_RUN_FINISHED"],null,[$KeywordRegistry.bulkMailStatus$sent(),vars.get("$sys.date")]);

    }
        
    return {
        sucessful : successIds.length,
        failed : failedIds.length
    };
}

/**
 * Opens a context to select a bulk mail to add recipients to.<br>
 * 
 * @param {String} pContext             the context of the contacts (Person or Organisation)
 * @param {String[]} pContactIds        Recipients that should be added.<br>
 * @param {String|Object} pFilter       the filter for the contacts that should be used if no contact is selected
 */
BulkMailUtils.openAddRecipientView = function (pContext, pContactIds, pFilter)
{
    if (!Utils.isString(pContactIds))
        pContactIds = JSON.stringify(pContactIds);
    if (!Utils.isString(pFilter))
        pFilter = JSON.stringify(pFilter);
    
    neon.openContext("BulkMailAddRecipients", "BulkMailAddRecipientsEdit_view", null, neon.OPERATINGSTATE_VIEW, {
        "ObjectType_param": pContext,
        "ContactIds_param": pContactIds,
        "ContactFilter_param": pFilter
    });
}

/**
 * Deletes all bulk mail recipients that have a commrestriction for emails.<br>
 * 
 * @param {String} pBulkMailId          <p>
 *                                      The mail id.<br>
 */
BulkMailUtils.removeCommRestrictionRecipients = function (pBulkMailId)
{
    var recipientIds = newSelect("BULKMAILRECIPIENTID")
        .from("BULKMAILRECIPIENT")
        .join("CONTACT", "BULKMAILRECIPIENT.CONTACT_ID = CONTACT.CONTACTID")
        .where("BULKMAILRECIPIENT.BULKMAIL_ID", pBulkMailId)
        .and(new CommunicationSettingsCondition()
            .emails("BULKMAILRECIPIENT.EMAIL_ADDRESS")
            .rejected()
            .existSettings()
            .buildCondition())
        .arrayColumn();

    newWhereIfSet("BULKMAILRECIPIENT.BULKMAILRECIPIENTID", recipientIds, SqlBuilder.IN())
        .deleteData();
}

/**
 * Adds recipients to a bulkmail.<br>
 * 
 * @param {String} pBulkMailId              <p>
 *                                          Bulk mail id.<br>
 * @param {String[]} pContactIds            <p>
 *                                          Contact ids of the recipients.<br>
 */
BulkMailUtils.addRecipients = function (pBulkMailId, pContactIds)
{
    if (pContactIds.length > 0)
    {
        var contactData = newSelect(["CONTACTID", "(" + CommUtil.getStandardSubSqlMail(newWhere("COMMUNICATION.CONTACT_ID = CONTACTID")) + ")"])
            .from("CONTACT")
            .where("CONTACT.CONTACTID", pContactIds, SqlBuilder.IN())
            .table();
        
        var sqlBuilder = new SqlBuilder();
        
        var inserts = contactData.map(function([contactId, standardMail])
        {
            return sqlBuilder.buildInsertStatement({
                "BULKMAIL_ID": pBulkMailId,
                "CONTACT_ID": contactId,
                "STATUS": $KeywordRegistry.bulkMailRecipientStatus$pending(),
                "EMAIL_ADDRESS": standardMail
            }, "BULKMAILRECIPIENT", "BULKMAILRECIPIENTID");
        });
        
        db.inserts(inserts);
    }
}

/**
 * Loads the document template of a bulk mail. If the bulk mail
 * itself has a template, it is preferred over the documentTemplate-id.
 * 
 * @param {String} pBulkMailId                                  <p>
 *                                                              The id of the bulk mail.<br>
 * @param {String} pDocumentTemplateId                          <p>
 *                                                              The id of the document template.<br>
 * @param {Boolean} pResolveSubtemplates=true (optional)        <p>
 *                                                              If true subtemplates are resolved (if the type is html)
 * @param {Boolean} pUseTemplateAttachments=false               <p>
 *                                                              If true the attachments from the document template is always used
 * @param {FileUpload} pUpload (optional)                       <p>
 *                                                              The upload value if a custom template is used.<br>
 * @return {DocumentTemplate}                                   <p>
 *                                                              The document template, null if no content was found.<br>
 */
BulkMailUtils.getBulkMailTemplate = function (pBulkMailId, pDocumentTemplateId, pResolveSubtemplates, pUseTemplateAttachments, pUpload)
{
    if (pUpload && pUpload.isFilled() && BulkMailUtils.isValidMimeType(pUpload.mimeType))
        return DocumentTemplate.fromUpload(pUpload);
        
    var bulkTemplate = DocumentTemplate.loadTemplate(pBulkMailId, "BULKMAIL", pResolveSubtemplates);
    var documentTemplate = DocumentTemplate.loadTemplate(pDocumentTemplateId, undefined, pResolveSubtemplates);

    if (!bulkTemplate.content)
    {
        return documentTemplate;
    }
    else
    {
        if (pUseTemplateAttachments)
            bulkTemplate.setAttachments(documentTemplate.getAttachments());
        
        return bulkTemplate;
    }
}

/**
 * Checks if a contact is a recipient of a bulk mail.<br>
 * 
 * @param {String} pBulkMailId                      <p>
 *                                                  The id of the bulk mail.<br>
 * @param {String} pContactId                       <p>
 *                                                  The contact id.<br>
 * @param {String} pRecipientId                     <p>
 *                                                  The contact id of the contact where,<br>
 *                                                  the bulk mail shall sent to.<br>
 * @return {boolean}                                <p>
 *                                                  True, if the contact is a recipient.<br>
 */
BulkMailUtils.isRecipient = function (pBulkMailId, pContactId, pRecipientId)
{
    return newSelect("count(*)")
                .from("BULKMAILRECIPIENT")
                .where("BULKMAILRECIPIENT.CONTACT_ID", pContactId)
                .and("BULKMAILRECIPIENT.BULKMAIL_ID", pBulkMailId)
                .andIfSet("BULKMAILRECIPIENT.BULKMAILRECIPIENTID", pRecipientId, SqlBuilder.NOT_EQUAL())
                .cell() != "0"; //TODO: is there a way exists could be used?
}

/**
 * Opens the BulkMail context in new mode.<br>
 * 
 * @param {String[]} pRecipients (optional)         <p>
 *                                                  Recipients that should be added after creation.<br>
 */
BulkMailUtils.newBulkMail = function (pRecipients)
{
    var params = {
        "PresetRecipients_param" : JSON.stringify(pRecipients)
    };
    neon.openContext("BulkMail", "BulkMailEdit_view", null, neon.OPERATINGSTATE_NEW, params);
}

/**
 * Filters the given contactIds if they can be added as new recipients.
 * Checks if a contact is already a recipient or if there is a advertising ban.
 * 
 * @param {String} pBulkMailId id of the bulk mail the contacts should be added to
 * @param {String[]} pContactIds contacts to filter
 * @return {String[]} contacts that can be added as recipients
 */
BulkMailUtils.filterNewRecipients = function (pBulkMailId, pContactIds)
{
    return newSelect("CONTACTID")
                .from("CONTACT")
                .whereIfSet("CONTACT.CONTACTID", pContactIds, SqlBuilder.IN())
                // only add contacts that aren't already recipients
                .and(null, newSelect("BULKMAILRECIPIENTID")
                                .from("BULKMAILRECIPIENT")
                                .where("BULKMAILRECIPIENT.CONTACT_ID = CONTACT.CONTACTID")
                                .and("BULKMAILRECIPIENT.BULKMAIL_ID", pBulkMailId)
                        , SqlBuilder.NOT_EXISTS())
                // check if there's a commrestriction
                .and(new CommunicationSettingsCondition()
                    .emails(CommUtil.getStandardSubSqlMail())
                    .rejected()
                    .existNoSettings()
                    .buildCondition())
                .arrayColumn();
}

/**
 * Opens the given bulk mail.
 * 
 * @param {String} pBulkMailId          <p>
 *                                      The id of the bulk mail.<br>
 */
BulkMailUtils.openBulkMail = function (pBulkMailId)
{
    neon.openContext("BulkMail", "BulkMailMain_view", [pBulkMailId], neon.OPERATINGSTATE_VIEW, null);
}

/**
 * Checks is the given mime type can be used for a bulk mail.<br>
 * 
 * @param {String} pMimeType                <p>
 *                                          The mime type.<br>
 * @return {Boolean}                        <p>
 *                                          Whether the type is usable or not.<br>
 */
BulkMailUtils.isValidMimeType = function (pMimeType)
{
    var templateType = DocumentTemplate.types.fromMimeType(pMimeType);
    return BulkMailUtils.isValidTemplateType(templateType)
}

/**
 * Checks is the given template type can be used for a bulk mail.<br>
 * 
 * @param {String} pTemplateType            <p>
 *                                          Template type.<br>
 * @return {Boolean}                        <p>
 *                                          Whether the type is usable or not.<br>
 */
BulkMailUtils.isValidTemplateType = function (pTemplateType)
{
    switch (pTemplateType)
    {
        case DocumentTemplate.types.EML:
        case DocumentTemplate.types.HTML:
        case DocumentTemplate.types.TXT:
            return true;
        default:
            return false;
    }
}

/**
 * Checks whether the given status id matches,<br>
 * to the status of a bulk mail which is sent or<br>
 * not.
 *
 * @param {String} pStatus              <p>
 *                                      The key id of the current status.<br>
 * @return {Boolean}                    <p>
 *                                      True if the status is "sent" or "sending".<br> 
 */
BulkMailUtils.isStatusSendingOrSent = function (pStatus)
{
    return pStatus == $KeywordRegistry.bulkMailStatus$sent() || pStatus == $KeywordRegistry.bulkMailStatus$beingSent()
}

/**
 * Opens BulkMail context in new mode, with the given bulk mail id.<br>
 * 
 * @param {String} pBulkMailId          <p>
 *                                      The id of the bulk mail.<br>
 */
BulkMailUtils.copy = function(pBulkMailId)
{
    var params = {
        "CopyBulkMailId_param" : pBulkMailId
    };
    neon.openContext("BulkMail", null, null, neon.OPERATINGSTATE_NEW, params);
}
/*
 *Stores the Eml file for a bulkmailrecipient in the Filesystem
 *
 * @param {String} pBulkMailId          <p>
 *                                      The id of the bulk mail.<br>
 * @param {String} pMailRunId          <p>
 *                                      The id of the bulk mail run.<br>
 * @param {String} pMailLogId          <p>
 *                                      The id of the corresponding mail log entry.<br>                                                                           
 **/
BulkMailUtils.storeEmlFile = function(pBulkMailId,pMailRunId, pMailLogId,pFile)
{
    var locationoption = project.getPreferenceValue("bulkmail.fileStorage","/bulkMailFiles/");
    var path = vars.get("$sys.serverdata")+locationoption +pBulkMailId+"/"+pMailRunId+"/";
    var filename = pMailLogId+".eml"
    var fullPath = path + filename;
    fileIO.storeData(fullPath, pFile, util.DATA_BINARY, false);
}

/*
 *Loads the Eml file for a bulkmailrecipient from the Filesystem
 *
 * @param {String} pBulkMailId          <p>
 *                                      The id of the bulk mail.<br>
 * @param {String} pMailRunId          <p>
 *                                      The id of the bulk mail run.<br>
 * @param {String} pMailLogId          <p>
 *                                      The id of the corresponding mail log entry.<br>   
 * @return {String}                     <p>
 *                                      The file as base64 String<br>                                                                                                              
 **/
BulkMailUtils.getEmlFile = function(pBulkMailId,pMailRunId, pMailLogId)
{
    var locationoption = project.getPreferenceValue("bulkmail.fileStorage","/bulkMailFiles/");
    var path = vars.get("$sys.serverdata")+locationoption +pBulkMailId+"/"+pMailRunId+"/";
    var filename = pMailLogId+".eml"
    var fullPath = path + filename;
    return (fileIO.getData(fullPath,util.DATA_BINARY));
}

/*
 *Gets the redirecturl for a link in a bulkmail
 *
 * @param {String} pLinkId          <p>
 *                                      The id of the link.<br>
 *                                      
 * @return {String}                 <p>
 *                                      The url<br>                                          
 **/
BulkMailUtils.getRedirectLink = function(pLinkId)
{
    if (pLinkId)
    {
        return newSelect("WEBLINK.URL").from("WEBLINK").where("WEBLINK.WEBLINKID", pLinkId).cell();
    }
    return null;
}
/*
 *Inserts the Redirect into the link_click table.
 *
 *If its the first Click the Id gets put as opener in mail_log
 *
 * @param {String} pMailLogId (required)<p>
 *                                      The id of the mail log.<br>
 * @param {String} pIpAddress           <p>
 *                                      the ip address of the client.<br>                                     
 * @param {String} pLinkId              <p>
 *                                      The id of link.<br>             
 * @param {String} pBrowsername         <p>
 *                                      The browser that was used to open the link.<br>
 * @param {String} pOperatingSystemName <p>
 *                                      The Operating System that was used to open the link.<br>
 * @param {String} pDeviceType          <p>
 *                                      The device type that was used to open the link.<br>                                                 
 **/

BulkMailUtils.insertClick = function (pMailLogId,pIpAddress,pLinkId,pBrowsername,pOperatingSystemName,pDeviceType)
{
    if (!pMailLogId || !pLinkId) 
    {
        return;
    }
    
    var linkClickId = util.getNewUUID();
    new SqlBuilder()
        .tableName("WEBLINK_CLICK")
        .insertFields({
            "WEBLINK_CLICKID": linkClickId,
            "WEBLINK_ID": pLinkId,
            "DEVICE_TYPE": pDeviceType || "desktop",
            "OPERATING_SYSTEM": pOperatingSystemName,
            "BROWSER": pBrowsername,
            "IP_ADDRESS": pIpAddress,
            "MAIL_LOG_ID": pMailLogId,
            "DATE_OPENED": vars.get("$sys.date")
        });
    
    newWhere("MAIL_LOG.MAIL_LOGID", pMailLogId)
        .and("MAIL_LOG.OPENER_LINK_CLICK_ID is null")
        .updateFields({"OPENER_LINK_CLICK_ID": linkClickId});
}

/*@TODO: Abändern nachdem geklärt ist wie wir IP-Adresse erhalten. Momentan bekommen wir nur Loadbalancer Ip.
 *
 **/

BulkMailUtils.getIpAddressFromHeader = function(pHttpHeader)
{
    return pHttpHeader["X-forwarded-for"].split(",")[0];
}

BulkMailUtils.startBulkmailWorkFlow = function(pMailLogId, pLinkId)
{
    if (!pMailLogId || !pLinkId) 
    {
        return
    }
    var [actionType, workflowKey, signalName] = newSelect(["ACTION_TYPE", "WORKFLOWPROCESSDEFINITION_KEY", "WORKFLOWSIGNAL_NAME"])
        .from("WEBLINK")
        .where("WEBLINK.WEBLINKID", pLinkId)
        .arrayRow();
        
    var variables = {
        "mailLogId": pMailLogId,
        "linkId": pLinkId
    };
    if (actionType == $KeywordRegistry.weblinkActionType$startWorkflow() && workflowKey)
    {
        workflow.startProcessByKey(workflowKey, variables);
    }
    else if (actionType == $KeywordRegistry.weblinkActionType$sendWorkflowSignal() && signalName)
    {
        workflow.signalEventReceived(signalName, variables);
    }
}



function SerialLetterUtils () {}

/**
 * Adds recipients to a serial letter.<br>
 * 
 * @param {String} pSerialLetterId      <p>
 *                                      The id of the serial letter.<br>
 * @param {String[]} pContactIds        <p>
 *                                      Contact ids of the recipients.<br>
 */
SerialLetterUtils.addRecipients = function (pSerialLetterId, pContactIds)
{
    var columns = [
        "LETTERRECIPIENTID",
        "SERIALLETTER_ID",
        "CONTACT_ID"
    ];
    var inserts = [];
    for (let i = 0, l = pContactIds.length; i < l; i++)
    {
        inserts.push(["LETTERRECIPIENT", columns, null, [util.getNewUUID(), pSerialLetterId, pContactIds[i]]]);
    }
    db.inserts(inserts);
}

/**
 * Opens a context to select a serial letter to add recipients to.<br>
 * 
 * @param {String} pContext             the context of the contacts (Person or Organisation)
 * @param {String[]} pContactIds        Recipients that should be added.<br>
 * @param {String|Object} pFilter       the filter for the contacts that should be used if no contact is selected
 */
SerialLetterUtils.openAddRecipientView = function (pContext, pContactIds, pFilter)
{
    if (!Utils.isString(pContactIds))
        pContactIds = JSON.stringify(pContactIds);
    if (!Utils.isString(pFilter))
        pFilter = JSON.stringify(pFilter);
    
    neon.openContext("SerialLetterAddRecipients", "SerialLetterAddRecipientsEdit_view", null, neon.OPERATINGSTATE_VIEW, {
        "ObjectType_param": pContext,
        "ContactIds_param": pContactIds,
        "ContactFilter_param": pFilter
    });
}

/**
 * Executes a server process that builds a serial letter.<br>
 * 
 * @param {String} pSerialLetterId              <p>
 *                                              The id of the serial letter.<br>
 * @param {String[]} pRecipientIds (optional)   <p>
 *                                              Letter recipient ids of that should be used.<br>
 *                                              If omitted, all recipients of the letter will be used.<br>
 */
SerialLetterUtils.buildSerialLetterOnServer = function (pSerialLetterId, pRecipientIds)
{
    var user = EmployeeUtils.getCurrentUserId();
    var processConfig = process.createStartAsyncConfig()
        .setName("buildSerialLetter_serverProcess")
        .setLocalVariables({
            "serialLetterId" : pSerialLetterId,
            "recipientIds" : JSON.stringify(pRecipientIds),
            "user" : user
        })
        .setUser(vars.get("$sys.user"));
    process.startAsync(processConfig);
}

/**
 * Executes a server process that builds a serial letter.<br>
 * 
 * @param {String} pSerialLetterId          <p>
 *                                          Serial letter id.<br>
 * @param {String[]} pRecipientIds          <p>
 *                                          Letter recipient ids of that should be used.<br>
 *                                          If omitted, all recipients of the letter will be used.<br>
 */
SerialLetterUtils.buildSerialLetter = function (pSerialLetterId, pRecipientIds)
{
    var [templateId, title] = newSelect("DOCUMENTTEMPLATE_ID, TITLE")
                        .from("SERIALLETTER")
                        .where("SERIALLETTER.SERIALLETTERID", pSerialLetterId)
                        .arrayRow(true);

    var template = SerialLetterUtils.getSerialLetterTemplate(pSerialLetterId, templateId);
    var contactIdsSelect = newSelect("CONTACT_ID")
                        .from("LETTERRECIPIENT")
                        .join("CONTACT", newWhere("LETTERRECIPIENT.CONTACT_ID = CONTACT.CONTACTID"))
                        .where("LETTERRECIPIENT.SERIALLETTER_ID", pSerialLetterId)
                        .andIfSet(new CommunicationSettingsCondition()
                            .postalAddress("LETTERRECIPIENT.ADDRESS_ID")
                            .rejected()
                            .existNoSettings()
                            .buildCondition());

    if (pRecipientIds && pRecipientIds.length > 0)
        contactIdsSelect.and("LETTERRECIPIENT.LETTERRECIPIENTID", pRecipientIds, SqlBuilder.IN());

    var contactIds = contactIdsSelect.table();
    if(template != null){
    return {
        content : template.getSerialLetterByContactIds(contactIds),
        filename : template.filename,
        title : title
    };}
    else{
        return{
            title : title
}

    }
}

/**
 * Checks if a contact is a recipient of a serial letter.<br>
 * 
 * @param {String} pSerialLetterId              <p>
 *                                              The id of the serial letter.<br>
 * @param {String} pContactId                   <p>
 *                                              The contact id of the contact.<br>
 * @param {String} pRecipientId (optional)      <p>
 *                                              Letter recipient id.<br>
 * @return {Boolean}                            <p>
 *                                              True, if the contact is a recipient<br>
 *                                              and otherwise false.<br>
 */
SerialLetterUtils.isRecipient = function (pSerialLetterId, pContactId, pRecipientId)
{
    return newSelect("count(*)")
                .from("LETTERRECIPIENT")
                .where("LETTERRECIPIENT.CONTACT_ID", pContactId)
                .and("LETTERRECIPIENT.SERIALLETTER_ID", pSerialLetterId)
                .andIfSet("LETTERRECIPIENT.LETTERRECIPIENTID", pRecipientId, SqlBuilder.NOT_EQUAL())
                .cell() != "0"; // TODO: is there a way exists could be used?
}

/**
 * Opens the serial letter context in new mode, with<br>
 * the given serial letter id.<br>
 * 
 * @param {String} pSerialLetterId          <p>
 *                                          The id of the serial letter.<br>
 */
SerialLetterUtils.openSerialLetter = function (pSerialLetterId)
{
    neon.openContext("SerialLetter", "SerialLetterMain_view", [pSerialLetterId], neon.OPERATINGSTATE_VIEW, null);
}

/**
 * Loads the document template of a serial letter. <br>
 * If the serial letter itself has a template, it is <br>
 * preferred over the documentTemplate-id.<br>
 * 
 * @param {String} pLetterId            <p>
 *                                      The id of the serial letter.<br>
 * @param {String} pDocumentTemplateId  <p>
 *                                      The id of the document template.<br>
 * @return {DocumentTemplate}           <p>
 *                                      The document template.<br>
 */
SerialLetterUtils.getSerialLetterTemplate = function (pLetterId, pDocumentTemplateId)
{
    var template = DocumentTemplate.loadTemplate(pLetterId, "SERIALLETTER");
    if (!template.type)
        template = DocumentTemplate.loadTemplate(pDocumentTemplateId);
    return template;
}

/**
 * Adds contacts or organistaions to a serial letter by contactIds.<br>
 * 
 * @param {String} pContactIds              <p>
 *                                      The contact ids as JSON array.<br>
 */
SerialLetterUtils.addParticipantsByRowIds = function(pContactIds)
{
    var params = {
        "ContactIds_param" : pContactIds
    };
    neon.openContext("SerialLetterAddRecipients", "SerialLetterAddRecipientsEdit_view", null, neon.OPERATINGSTATE_VIEW, params);
}

/**
 * Adds contacts or organistaions to a serial letter by condition (filter).<br>
 * 
 * @param {String} pCondition           <p>
 *                                      Contact ids.
 * @param {String} pSourceTableName     <p>
 *                                      The source table.<br>
 */
SerialLetterUtils.addParticipantsByCondition = function(pCondition, pSourceTableName)
{
    var params = {
        "ContactIds_param" : pCondition,
        "comingFrom_param" : pSourceTableName}
    neon.openContext("SerialLetterAddRecipients", "SerialLetterAddRecipientsEdit_view", null, neon.OPERATINGSTATE_VIEW, params);
}

/**
 * Filters the given contactIds if they can be added as new recipients.
 * Checks if a contact is already a recipient or if there is a advertising ban.
 * 
 * @param {String} pSerialLetterId id of the seroal letter the contacts should be added to
 * @param {String[]} pContactIds contacts to filter
 * @return {String[]} contacts that can be added as recipients
 */
SerialLetterUtils.filterNewRecipients = function (pSerialLetterId, pContactIds)
{
    return newSelect("CONTACTID")
                .from("CONTACT")
                .whereIfSet("CONTACT.CONTACTID", pContactIds, SqlBuilder.IN())
                // only add contacts that aren't already recipients
                .and(null, newSelect("LETTERRECIPIENTID")
                                    .from("LETTERRECIPIENT")
                                    .where("LETTERRECIPIENT.CONTACT_ID = CONTACT.CONTACTID")
                                    .and("LETTERRECIPIENT.SERIALLETTER_ID", pSerialLetterId)
                        , SqlBuilder.NOT_EXISTS())
                // check if there's a commrestriction
                .and(new CommunicationSettingsCondition()
                    .postalAddress("CONTACT.ADDRESS_ID")
                    .rejected()
                    .existNoSettings()
                    .buildCondition())
                .arrayColumn();
}