contentProcess.js

import("system.SQLTYPES");
import("system.eMath");
import("system.tools");
import("PostalAddress_lib");
import("system.translate");
import("Contact_lib");
import("KeywordRegistry_basic");
import("system.logging");
import("system.datetime");
import("system.util");
import("system.result");
import("system.vars");
import("system.db");
import("Sql_lib");
import("Keyword_lib");
import("AttributeRegistry_basic");
import("KeywordRegistry_basic");
import("Util_lib");
import("Address_lib");
import("JditoFilter_lib");

var sqlMasker = new SqlMaskingUtils();

var activitySubQuery = newSelect("max(ENTRYDATE)")
                                .from("ACTIVITY")
                                .join("ACTIVITYLINK", "ACTIVITYID = ACTIVITY_ID")
                                .where("ACTIVITYLINK.OBJECT_ROWID = org.ORGANISATIONID")
                                .and("ACTIVITY.CATEGORY", "VISIT")

var idValues = null;
if (vars.exists("$local.idvalues") && vars.get("$local.idvalues"))
    idValues = vars.get("$local.idvalues");

//dynamic Recommendations
//recommended Organisations containing the attribute Visit Frequency

if (!idValues)
{
    var visitFrequencyData = newSelect([
            "org.ORGANISATIONID", 
            "NAME", 
            newSelect("CONTACT.CONTACTID")
                .from("CONTACT")
                .leftJoin("AB_ATTRIBUTERELATION", "CONTACT.CONTACTID = AB_ATTRIBUTERELATION.OBJECT_ROWID")
                .leftJoin("AB_ATTRIBUTE", "AB_ATTRIBUTE.AB_ATTRIBUTEID = AB_ATTRIBUTERELATION.AB_ATTRIBUTE_ID")
                .leftJoin("ORGANISATION", "ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID")
                .where("AB_ATTRIBUTE.AB_ATTRIBUTEID", $AttributeRegistry.visitPlanPointOfContact()), 
            AddressUtils.formatOnelineSql(), 
            "visitPlanFrequency.ID_VALUE", 
            "'" + $KeywordRegistry.visitRecommendationPrioSource$visitFrequency()+"'" , 
            activitySubQuery, 
            "CONTACTID",
            newSelect("min(ENTRYDATE)")
                .from("VISITPLANENTRY") 
                .where("CONTACT.CONTACTID", "VISITPLANENTRY.ORGANISATION_CONTACT_ID") 
                .and(newWhere("VISITPLANENTRY.STATUS", $KeywordRegistry.visitPlanEntryStatus$planned())
                    .or("VISITPLANENTRY.STATUS", $KeywordRegistry.visitPlanEntryStatus$Appointmentarranged()))
                .and("VISITPLANENTRY.ENTRYDATE", newSelect("min(vp.ENTRYDATE)")
                    .from("VISITPLANENTRY", "vp")
                    .where(["VISITPLANENTRY", "ORGANISATION_CONTACT_ID", "vp"], "visitplanentry.ORGANISATION_CONTACT_ID")
                    .and(["VISITPLANENTRY", "ENTRYDATE", "vp"], datetime.today())
                    .and(newWhere(["VISITPLANENTRY", "STATUS", "vp"], 
                    $KeywordRegistry.visitPlanEntryStatus$planned())
                    .or(["VISITPLANENTRY", "STATUS", "vp"], 
                    $KeywordRegistry.visitPlanEntryStatus$Appointmentarranged()))
                    .cell()),
            "ADDRESS.ZIP"
        ])
        .from("CONTACT")
        .join("ORGANISATION", "CONTACT.ORGANISATION_ID = org.ORGANISATIONID", "org")
        .leftJoin("PERSON", "PERSON.PERSONID = CONTACT.PERSON_ID")
        .leftJoin("ADDRESS", "ADDRESS_ID = ADDRESSID")
        .join("AB_ATTRIBUTERELATION", "visitPlanFrequency.OBJECT_ROWID = CONTACT.CONTACTID", "visitPlanFrequency")
        .where(["AB_ATTRIBUTERELATION", "AB_ATTRIBUTE_ID", "visitPlanFrequency"], $AttributeRegistry.visitPlanFrequency())
        .table();
        
    
 }

var prioSubSql = _getPrioByDueDateSubSql("VISITRECOMMENDATION.DUE_DATE", "VISITRECOMMENDATION.PRIORITY");

var recommendationSQLData = newSelect([
    "VISITRECOMMENDATIONID", 
    "VISITRECOMMENDATION.CONTACT_ID", 
    "ORGANISATION.NAME", 
    prioSubSql, 
    "VISITRECOMMENDATION.DUE_DATE", 
    "VISITRECOMMENDATION.SOURCE", 
    "VISITRECOMMENDATION.INFO", 
    AddressUtils.formatOnelineSql(), 
    newSelect("CONTACT.CONTACTID")
                .from("CONTACT")
                .leftJoin("AB_ATTRIBUTERELATION", "CONTACT.CONTACTID = AB_ATTRIBUTERELATION.OBJECT_ROWID")
                .leftJoin("AB_ATTRIBUTE", "AB_ATTRIBUTE.AB_ATTRIBUTEID = AB_ATTRIBUTERELATION.AB_ATTRIBUTE_ID")
                .leftJoin("ORGANISATION", "ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID")
                .where("AB_ATTRIBUTE.AB_ATTRIBUTEID", $AttributeRegistry.visitPlanPointOfContact())
                .and("CONTACT.CONTACTID", "VISITRECOMMENDATION.CONTACT_ID")
                .cell(),
                
    newSelect("min(ENTRYDATE)")
                .from("VISITPLANENTRY")
                .where("VISITPLANENTRY.ORGANISATION_CONTACT_ID", "VISITRECOMMENDATION.CONTACT_ID")
                .and("VISITPLANENTRY.STATUS", $KeywordRegistry.visitPlanEntryStatus$planned())
                .or("VISITPLANENTRY.STATUS", $KeywordRegistry.visitPlanEntryStatus$Appointmentarranged())
                .and("VISITPLANENTRY.entrydate", datetime.today(), SqlBuilder.GREATER())
                .cell(),
                
    newSelect("VISITRECOMMENDATION.CONTACT_ID")
                .from("VISITPLANEMPLOYEEWEEK, VISITPLANENTRY")
                .where("VISITPLANENTRY.VISITPLANEMPLOYEEWEEK_ID", "VISITPLANEMPLOYEEWEEK.VISITPLANEMPLOYEEWEEKID")
                .and("VISITRECOMMENDATION.CONTACT_ID", "VISITPLANENTRY.ORGANISATION_CONTACT_ID")
                .and(newWhere("VISITPLANENTRY.STATUS", $KeywordRegistry.visitPlanEntryStatus$planned())
                .or("VISITPLANENTRY.STATUS", $KeywordRegistry.visitPlanEntryStatus$Appointmentarranged()))
                .and("VISITPLANENTRY.entrydate", newSelect("min(vp.entrydate)")
                                                                .from("visitplanentry", "VP")
                                                                .where(["VISITPLANENTRY", "entrydate", "vp"], datetime.today(), SqlBuilder.GREATER())
                                                                .and(["VISITPLANENTRY", "STATUS", "vp"], $KeywordRegistry.visitPlanEntryStatus$planned())
                                                                .or(["VISITPLANENTRY", "STATUS", "vp"], $KeywordRegistry.visitPlanEntryStatus$Appointmentarranged()).cell()),
    "ADDRESS.ZIP"
])
        .from("VISITRECOMMENDATION")
        .join("CONTACT", "VISITRECOMMENDATION.CONTACT_ID = CONTACT.CONTACTID")
        .leftJoin("ORGANISATION", "CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID")
        .join("ADDRESS", "CONTACT.ADDRESS_ID = ADDRESS.ADDRESSID")
        .whereIfSet("VISITRECOMMENDATION.VISITRECOMMENDATIONID", idValues, SqlBuilder.IN());

var filterCond = new FilterSqlTranslator(vars.get("$local.filter"), "VISITRECOMMENDATION")
    .addSqlFieldMapping("ORGANISATION_NAME", "ORGANISATION.NAME")
    .addSqlFieldMapping("ADDRESS_ZIP", "ADDRESS.ZIP")
    .addSpecialFieldConditionFn("PRIORITY", function (pFilterValue, pOperator)
    {
        switch (pOperator)
        {
            case "EQUAL":
                return [prioSubSql[0] + " = ?", prioSubSql[1].concat([[pFilterValue, SQLTYPES.VARCHAR]])];
            case "NOT_EQUAL":
                return [prioSubSql[0] + " != ?", prioSubSql[1].concat([[pFilterValue, SQLTYPES.VARCHAR]])];
            case "ISNULL":
                return [prioSubSql[0] + " is null", prioSubSql[1]];
            case "ISNOTNULL":
                return [prioSubSql[0] + " is not null", prioSubSql[1]];
            default:
                return false;
        }
    })
    .getSqlCondition();

recommendationSQLData = recommendationSQLData.andIfSet(filterCond).table();

var recommendationData = recommendationSQLData.map(function ([uid, contactId, organisationName, priority, dueDate, prioSource, info, address, what, plannedDate, visitContact, addressZip])
{
    var title = "";
    if(visitContact)
    {
        var user = tools.getUserByAttribute(tools.CONTACTID, visitContact);
        title = user[tools.TITLE]
    }
    
    return [
        uid,
        organisationName,
        plannedDate,
        address,
        priority,
        prioSource,
        KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPrioSource(), prioSource), //Source of Priority (displayvalue)
        dueDate,
        info,
        contactId,
        "",
        addressZip
    ];
});

var monthly = parseInt(datetime.ONE_DAY * 31);
var semiannually = parseInt(datetime.ONE_DAY * 183);
var quarterly = parseInt(datetime.ONE_DAY * 93);
var yearly = parseInt(datetime.ONE_DAY * 365);

if (!idValues)
{
    var frequencyData = [];
    for (var i = 0; i < visitFrequencyData.length; i++)
    {
        let title = "";

        if(visitFrequencyData[i][10])
        {
            let user = tools.getUserByAttribute(tools.CONTACTID, visitFrequencyData[i][9]);
            title = user[tools.TITLE]
        }
        
        let tmpData = [
            visitFrequencyData[i][0],                 //UID
            visitFrequencyData[i][1],                 //Organisation Name
            visitFrequencyData[i][2],                 //point of contact
            visitFrequencyData[i][3],                 //Address
            "",                                       //prio
            visitFrequencyData[i][5],                 //Source of Priority (Id)
            "",                                       //Source of Priority (displayvalue)
            "",                                       //dueDate
            "",                                       //info
            visitFrequencyData[i][7],                 //ContactId
            "",
            visitFrequencyData[i][9]
        ];
        var dueDate = "";
        var lastVisitDate = visitFrequencyData[i][8];

        if (!lastVisitDate)
        {
            dueDate = datetime.date();
        }
        else
        {
            switch(visitFrequencyData[i][4])
            {
                case $AttributeRegistry.visitPlanFrequency$monthly():
                {
                    dueDate = eMath.addInt(lastVisitDate, monthly);
                    break;
                }
                case $AttributeRegistry.visitPlanFrequency$quarterly():
                {
                    dueDate = eMath.addInt(lastVisitDate, quarterly);
                    break;
                }
                case $AttributeRegistry.visitPlanFrequency$semiannually():
                {
                    dueDate = eMath.addInt(lastVisitDate, semiannually);
                    break;
                }
                case $AttributeRegistry.visitPlanFrequency$yearly():
                {
                    dueDate = eMath.addInt(lastVisitDate, yearly);
                    break;
                }
                default:
                {
                    break;
                }

            }
        }
        tmpData[7] = dueDate;                                       //Due Date
        tmpData[4] = getPrioByDueDate(dueDate);                     //Priority

        frequencyData.push(tmpData);
    }
    var recordFilter = vars.get("$local.filter");
    if (recordFilter && recordFilter.filter)
        frequencyData = JditoFilterUtils.filterRecords(["UID", "ORGANISATION_NAME", "", "ORGANISATION_ADDRESS", "PRIORITY", "", "", "DUE_DATE", "INFO", "CONTACT_ID", "", "ADDRESS_ZIP"], 
            frequencyData, recordFilter.filter);
    recommendationData = recommendationData.concat(frequencyData);
}


//add Display Value for Priority and PrioritySource
for (let i = 0; i < recommendationData.length; i++)
{
    recommendationData[i][6] = KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPriority(), recommendationData[i][4])
    recommendationData[i][10] = KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPrioSource(), recommendationData[i][5])
}

//sort Array by priority; highest priority first
ArrayUtils.sort2d(recommendationData, 4, true)

result.object(recommendationData);

function _getPrioByDueDateSubSql (pDueDateField, pPriorityField)
{
    var currentDate = datetime.date();
    var sqlMasker = new SqlMaskingUtils();
    
    var subSql = "case when " + pDueDateField + " < ? then '" + $KeywordRegistry.visitRecommendationPriority$critical()
        + "' when " + pDueDateField + " < ? then '" + $KeywordRegistry.visitRecommendationPriority$veryHigh()
        + "' when " + pDueDateField + " < ? then '" + $KeywordRegistry.visitRecommendationPriority$high()
        + "' when " + pDueDateField + " < ? then '" + $KeywordRegistry.visitRecommendationPriority$medium()
        + "' else '" + $KeywordRegistry.visitRecommendationPriority$low() + "' end";
    
    var [table, field] = pDueDateField.split(".");
    var dateFieldType = db.getColumnTypes(table, [field])[0];
    
    var dateDiffs = [
        0,                      //critical
        datetime.ONE_DAY * 3,   //very high
        datetime.ONE_WEEK,      //high
        datetime.ONE_WEEK * 2   //medium
    ];
    
    var preparedValues = dateDiffs.map(function (dateDiff)
    {
        return [(currentDate + dateDiff).toString(), dateFieldType];
    });
    
    if (pPriorityField)
        subSql = sqlMasker.isNull(pPriorityField, "(" + subSql + ")");
    
    return [subSql, preparedValues];
}

function getPrioByDueDate(pDueDate)
{
    var prio = "";
    var currentDate = datetime.date();
    
    if(pDueDate != "")
    {
        var dateDifference = pDueDate - currentDate;

        if(dateDifference < 0)
            prio = $KeywordRegistry.visitRecommendationPriority$critical();
        else if(dateDifference < datetime.ONE_DAY * 3)
            prio = $KeywordRegistry.visitRecommendationPriority$veryHigh();
        else if(dateDifference < datetime.ONE_DAY * 7)
            prio = $KeywordRegistry.visitRecommendationPriority$high();
        else if(dateDifference < datetime.ONE_DAY * 14)
            prio = $KeywordRegistry.visitRecommendationPriority$medium();
        else
            prio = $KeywordRegistry.visitRecommendationPriority$low();
    }
    
    return prio;
}