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")


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

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

recommendationSQLData = recommendationSQLData.table();

for( let i = 0; i < recommendationSQLData.length; i++)
{    
    tmpData = [];

    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[0] = recommendationSQLData[i][0]; //UID.value
    tmpData[1] = ContactUtils.getFullTitleByContactId(recommendationSQLData[i][1]); //ORGANISATION_NAME.value
    tmpData[2] = recommendationSQLData[i][6]; //STREET.value
    tmpData[3] = recommendationSQLData[i][7]; //COUNTRY.value
    tmpData[4] = recommendationSQLData[i][8]; //ZIP.value
    tmpData[5] = recommendationSQLData[i][9]; //CITY.value
    tmpData[6] = prio; //PRIORITY.value
    tmpData[7] = recommendationSQLData[i][4]; //PRIORITY_SOURCE.value
    tmpData[8] = KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPriority(), prio) //PRIORITY.displayValue
    tmpData[9] = recommendationSQLData[i][3]; //DUE_DATE.value
    tmpData[10] = recommendationSQLData[i][5]; //INFO.value
    tmpData[11] = recommendationSQLData[i][1]; //CONTACT_ID.value
    tmpData[12] = KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPrioSource(), recommendationSQLData[i][4]) //PRIORITY_SOURCE.displayValue
    tmpData[13] = ContactUtils.getFullTitleByContactId(recommendationSQLData[i][10], false); //USER_NEW.displayvalue
    tmpData[14] = recommendationSQLData[i][10]; //USER_NEW.value
    tmpData[15] = ContactUtils.getFullTitleByContactId(recommendationSQLData[i][11], false); //CONTACT_PERSON_ID.displayvalue
    tmpData[16] = recommendationSQLData[i][11]; //CONTACT_PERSON_ID.value
    tmpData[17] = ContactUtils.getFullTitleByContactId(recommendationSQLData[i][12], false); //RESPONSIBLE.displayvalue
    tmpData[18] = 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 = org.ORGANISATIONID")
                        .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())
                                                .toString() + 
                                            ")"            //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++)
    {
        tmpData = [];
        tmpData[0] = visitFrequencyData[i][1]; //UID.value
        tmpData[1] = visitFrequencyData[i][9]; //ORGANISATION_NAME.value
        tmpData[2] = visitFrequencyData[i][3]; //STREET.value
        tmpData[3] = visitFrequencyData[i][4]; //COUNTRY.value
        tmpData[4] = visitFrequencyData[i][5]; //ZIP.value
        tmpData[5] = visitFrequencyData[i][6]; //CITY.value
        tmpData[6] = ""; //PRIORITY.value
        tmpData[7] = $KeywordRegistry.visitRecommendationPrioSource$visitFrequency(); //PRIORITY_SOURCE.value
        tmpData[8] = ""; //PRIORITY.displayValue
        tmpData[9] = ""; //DUE_DATE.value
        tmpData[10] = ""; //INFO.value
        tmpData[11] = visitFrequencyData[i][1]; //CONTACT_ID.value
        tmpData[12] = KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPrioSource(), $KeywordRegistry.visitRecommendationPrioSource$visitFrequency()); //PRIORITY_SOURCE.displayValue
        tmpData[13] = ""; //USER_NEW.displayValue
        tmpData[14] = ""; //USER_NEW.value
        tmpData[15] = ContactUtils.getFullTitleByContactId(visitFrequencyData[i][1], false); //CONTACT_PERSON_ID.displayValue
        tmpData[16] = visitFrequencyData[i][1]; //CONTACT_PERSON_ID.value
        tmpData[17] = ""; //RESPONSIBLE.displayValue
        tmpData[18] = ""; //RESPONSIBLE.value
        
        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[6] = getPrioByDueDate(dueDate); //PRIORITY.value
        tmpData[8] = KeywordUtils.getViewValue($KeywordRegistry.visitRecommendationPriority(), tmpData[6]) //PRIORITY.displayValue
        tmpData[9] = dueDate; //DUE_DATE.value
        
    }
}

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

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