import("Keyword_lib");
import("system.result");
import("JditoFilter_lib");
import("Sql_lib");
import("KeywordRegistry_basic");
import("Salesproject_lib");
import("system.vars");

let filter = vars.get("$local.filters");
let gruppingFields = SalesprojectConversionRate.groupMapping(false);
var filterPhasenIDs = [];

var phasenManager = new SalesprojectConversionRate();

var sql = new SqlBuilder()
.from("AB_KEYWORD_ENTRY")
.where("AB_KEYWORD_ENTRY.AB_KEYWORD_CATEGORY_ID", KeywordUtils.getCategoryIdByName($KeywordRegistry.salesprojectPhase()));

if (filter != null)
{
    if (JSON.stringify(filter).indexOf("AB_KEYWORD_ENTRYID_KEYID") > -1 && vars.exists("$local.grouped"))
    {
        var phasenFilter = filter.childs.filter(function (child) {
            return (child.childs.filter(function (child) {
                return child.name == "AB_KEYWORD_ENTRYID_KEYID";
            })).length > 0
        });
        
        phasenFilter.forEach(function (pFilter) {
            filterPhasenIDs = filterPhasenIDs.concat(pFilter.childs.map(function (child) { return child.key;}));
            pFilter.childs = [];
        });
    }
    else 
        filterPhasenIDs = null;
    
    let filterCondition = JditoFilterUtils.getSqlCondition(filter, "AB_KEYWORD_ENTRY", null, SalesprojectConversionRate.filterMapping(null));
    sql.andIfSet(filterCondition);
}

if (vars.exists("$local.grouped"))
{
    let groupedfield = vars.get("$local.grouped");
    sql.join("SALESPROJECT_MILESTONE", newWhere("SALESPROJECT_MILESTONE.MILESTONEVALUE = AB_KEYWORD_ENTRY.KEYID"))
        // COUNT, ID, TITLE
        .select(["SALESPROJECT_MILESTONE.SALESPROJECT_ID", "SALESPROJECT_MILESTONE.MILESTONEVALUE", gruppingFields[groupedfield][0]]);
        
    if (filter != null)
    {
        filterCondition = JditoFilterUtils.getSqlCondition(filter, "SALESPROJECT_MILESTONE", null, SalesprojectConversionRate.filterMapping("SALESPROJECT_MILESTONE.DATE_START", false, "SALESPROJECT_MILESTONE"));
        sql.andIfSet(filterCondition);
    }
    
    sql.table().forEach(function (row) {
        phasenManager.insertMilestone(row[0], row[1],groupedfield == "AB_KEYWORD_ENTRYID_KEYID" ? null :row[2] ); // TODO: Gruppingfield 
    });

    result.object(phasenManager.getConversionRates(filterPhasenIDs).length);
} 
else 
{
    sql.select(["AB_KEYWORD_ENTRY.AB_KEYWORD_ENTRYID", "AB_KEYWORD_ENTRY.KEYID", "AB_KEYWORD_ENTRY.TITLE"])
        .orderBy("AB_KEYWORD_ENTRY.SORTING");
    result.object(sql.table().length);
}