contentProcess.js

import("JditoFilter_lib");
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");

var recommendationData = [];
var tmpData = [];
var activitySubQuery = "";

var recommendationSQLData = newSelect(
                            [
                                "VISITRECOMMENDATIONID",                //0
                                "VISITRECOMMENDATION.CONTACT_ID",       //1
                                "PRIORITY",                             //2
                                "DUE_DATE",                             //3
                                "SOURCE",                               //4
                                "INFO",                                 //5
                                "ADDRESS.ADDRESS",                      //6
                                "ADDRESS.COUNTRY",                      //7
                                "ADDRESS.ZIP",                          //8
                                "ADDRESS.CITY",                         //9
                                "VISITRECOMMENDATION.USER_NEW",         //10
                                "VISITRECOMMENDATION.CONTACT_PERSON_ID",//11
                                "VISITRECOMMENDATION.RESPONSIBLE",      //12
                            ]) 
                            .from("VISITRECOMMENDATION")
                            .join("CONTACT", "coalesce(VISITRECOMMENDATION.CONTACT_PERSON_ID, VISITRECOMMENDATION.CONTACT_ID) = CONTACT.CONTACTID")
                            .leftJoin("ADDRESS", "CONTACT.ADDRESS_ID = ADDRESS.ADDRESSID")
                            .where("VISITRECOMMENDATION.STATUS is null")


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

if(idValues == true)
    recommendationSQLData.and("VISITRECOMMENDATION.VISITRECOMMENDATIONID", vars.get("$local.idvalues"), SqlBuilder.IN())

recommendationSQLData = recommendationSQLData.table();

for( let i = 0; i < recommendationSQLData.length; i++)
{
    if(recommendationSQLData[i][2])
        var prio = recommendationSQLData[i][2];
    else if(recommendationSQLData[i][3])
        prio = getPrioByDueDate(recommendationSQLData[i][3]);
    else
        prio = $KeywordRegistry.visitRecommendationPriority$low();  

    tmpData = [
        recommendationSQLData[i][0], //UID.value
        ContactUtils.getFullTitleByContactId(recommendationSQLData[i][1]), //ORGANISATION_NAME.value
        recommendationSQLData[i][6], //STREET.value
        recommendationSQLData[i][7], //COUNTRY.value
        recommendationSQLData[i][8], //ZIP.value
        recommendationSQLData[i][9], //CITY.value
        prio, //PRIORITY.value
        recommendationSQLData[i][4], //PRIORITY_SOURCE.value
        KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPriority(), prio), //PRIORITY.displayValue
        recommendationSQLData[i][3], //DUE_DATE.value
        recommendationSQLData[i][5], //INFO.value
        recommendationSQLData[i][1], //CONTACT_ID.value
        KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPrioSource(), recommendationSQLData[i][4]), //PRIORITY_SOURCE.displayValue
        ContactUtils.getFullTitleByContactId(recommendationSQLData[i][10], false), //USER_NEW.displayvalue
        recommendationSQLData[i][10], //USER_NEW.value
        ContactUtils.getFullTitleByContactId(recommendationSQLData[i][11], false), //CONTACT_PERSON_ID.displayvalue
        recommendationSQLData[i][11], //CONTACT_PERSON_ID.value
        ContactUtils.getFullTitleByContactId(recommendationSQLData[i][12], false), //RESPONSIBLE.displayvalue
        recommendationSQLData[i][12] //RESPONSIBLE.value
    ];

    recommendationData.push(tmpData);
}

//===========Visitfrequency============
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);

//dynamic Recommendations
//recommended Organisations containing the attribute Visit Frequency
if(idValues == false)
{
    
    activitySubQuery = newSelect("max(ENTRYDATE)")
                        .from("ACTIVITY")
                        .join("ACTIVITYLINK", "ACTIVITYID = ACTIVITY_ID")
                        .where("ACTIVITYLINK.OBJECT_ROWID = CONTACT.CONTACTID")
                        .and("ACTIVITY.CATEGORY", "VISIT")

    var visitFrequencyData = newSelect(["''"    //0
                                        , "CONTACT.CONTACTID"   //1
                                        , "(" + 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())
                                                + 
                                            ")"            //2
                                        ,"ADDRESS.ADDRESS" //3
                                        ,"ADDRESS.COUNTRY" //4
                                        ,"ADDRESS.ZIP"     //5
                                        ,"ADDRESS.CITY"    //6
                                        , "visitPlanFrequency.ID_VALUE"//7
                                        , activitySubQuery //8
                                        , "org.NAME"])//9
                            .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()
     
    for (var i = 0; i < visitFrequencyData.length; i++)
    {
        var dueDate = "";
        var lastVisitDate = "";
        var today = datetime.date();
        if((visitFrequencyData[i][8]).length == 0)
        {
            dueDate = today;
        }
        else
        {
            lastVisitDate = visitFrequencyData[i][8];
            switch(visitFrequencyData[i][7])
            {
                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 = [
            visitFrequencyData[i][1], //UID.value
            visitFrequencyData[i][9], //ORGANISATION_NAME.value
            visitFrequencyData[i][3], //STREET.value
            visitFrequencyData[i][4], //COUNTRY.value
            visitFrequencyData[i][5], //ZIP.value
            visitFrequencyData[i][6], //CITY.value
            getPrioByDueDate(dueDate), //PRIORITY.value
            $KeywordRegistry.visitRecommendationPrioSource$visitFrequency(), //PRIORITY_SOURCE.value
            KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPriority(), getPrioByDueDate(dueDate)), //PRIORITY.displayValue
            dueDate, //DUE_DATE.value
            "", //INFO.value
            visitFrequencyData[i][1], //CONTACT_ID.value
            KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPrioSource(), $KeywordRegistry.visitRecommendationPrioSource$visitFrequency()), //PRIORITY_SOURCE.displayValue
            "", //USER_NEW.displayValue
            "", //USER_NEW.value
            ContactUtils.getFullTitleByContactId(visitFrequencyData[i][1], false), //CONTACT_PERSON_ID.displayValue
            visitFrequencyData[i][1], //CONTACT_PERSON_ID.value
            "", //RESPONSIBLE.displayValue
            "" //RESPONSIBLE.value
        ];
        
        var manualRec = newSelect("VISITRECOMMENDATION.VISITRECOMMENDATIONID")
        .from("VISITRECOMMENDATION")
        .where("VISITRECOMMENDATION.CONTACT_ID", tmpData[0])
        .or("VISITRECOMMENDATION.CONTACT_PERSON_ID", tmpData[0])
        .cell()
        
        var visitEntry = newSelect("VISITPLANENTRY.VISITPLANENTRYID")
        .from("VISITPLANENTRY")
        .where(newWhere("VISITPLANENTRY.CONTACT_ID", tmpData[0])
            .or("VISITPLANENTRY.ORGANISATION_CONTACT_ID", tmpData[0]))
        .and(newWhere("VISITPLANENTRY.ENTRYDATE", eMath.subInt(vars.get("$sys.today"), datetime.ONE_DAY * 7), SqlBuilder.LESS_OR_EQUAL())
            .and("VISITPLANENTRY.STATUS", $KeywordRegistry.visitPlanEntryStatus$Visitreportcreated(), SqlBuilder.NOT_EQUAL()))
            .cell();
        
        if(!manualRec || visitEntry)
            recommendationData.push(tmpData);
    }
}

var filter = vars.get("$local.filter"); 
//TODO: this is a workaround that filters the records manually, it should be possible to filter the users with a tools.* method
var filterFields = ["", "", "STREET", "COUNTRY" , "ZIP", "CITY", "PRIORITY", "PRIORITY_SOURCE", "", "DUE_DATE", "INFO", "CONTACT_ID"
    , "", "", "USER_NEW", "", "CONTACT_PERSON_ID", "", "RESPONSIBLE"];
var filterFns = {
    "ROLE_FILTER" : function (pRecordVal, pFilterVal, pOperator)
    {
        if (!pRecordVal)
            pRecordVal = [];
        switch (pOperator)
        {
            case "EQUAL":
                return pRecordVal.indexOf(pFilterVal) !== -1;
            case "NOT_EQUAL":
                return pRecordVal.indexOf(pFilterVal) === -1;
            case "ISNULL":
                return pRecordVal.length === 0;
            case "ISNOTNULL":
                return pRecordVal.length !== 0;
            default:
                return false;
        }
    },
    "$$$LOOKUPFIELD$$$" : function (pRecordValue, pFilterValue, pOperator, pRow)
    {
        if (pOperator == "CONTAINS")
        {
            pRow = [pRow[1], pRow[3], pRow[4], pRow[5]];
            var filterValues = pFilterValue.split(" ").filter(function (val) {return val.trim();});
            return filterValues.every(function (filterValue)
            {
                return pRow.some(function (fieldValue)
                {
                    return (new RegExp(filterValue, "i")).test(fieldValue);
                });
            });
        }
        return false;
    }
};
recommendationData = JditoFilterUtils.filterRecords(filterFields, recommendationData, filter.filter, filterFns);

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

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;
}