import("JditoFilter_lib");
import("system.translate");
import("system.result");
import("system.db");
import("Sql_lib");
import("system.vars");
import("system.datetime");

var dataArray = new Array(24).fill(0);

var sql = newSelect("WEBLINK_CLICK.DATE_OPENED")
.from("MAIL_RUN")
.join("MAIL_LOG","MAIL_LOG.MAIL_RUN_ID = MAIL_RUN.MAIL_RUNID")
.join("WEBLINK_CLICK","MAIL_LOG.OPENER_LINK_CLICK_ID = WEBLINK_CLICK.WEBLINK_CLICKID")
.where("MAIL_RUN.TESTRUN",0)
.and("MAIL_RUN.OBJECT_TYPE","bulkmail")
.and("MAIL_LOG.OPENER_LINK_CLICK_ID is not null")
.andIfSet("MAIL_RUN.OBJECT_ROWID", "$param.BulkMailId_param")

var filter = vars.get("$local.filter");
if (filter.filter && !(new FilterConditionGroup(filter.filter).isEmpty()))
{
   sql.leftJoin("BULKMAIL",newWhere("BULKMAIL.BULKMAILID = MAIL_RUN.OBJECT_ROWID").and("MAIL_RUN.OBJECT_TYPE","Bulkmail"));
   sql.and(JditoFilterUtils.getSqlCondition(filter.filter, "MAIL_RUN", null, {
                                                "#EXTENSION.BulkMail_filter.BulkMail_filter#TEXT": "BULKMAIL.BULKMAILID",
                                                "#EXTENSION.Campaign_filter.Campaign_filter#TEXT": "BULKMAIL.CAMPAIGN_ID",
                                                "#EXTENSION.Contact_filter.Contact_filter#TEXT": "MAIL_LOG.CONTACT_ID",
                                                "#EXTENSION.SendDate_filter.SendDate_filter#DATE": "MAIL_LOG.DATE_SEND"
                                            }));
}

sql.arrayColumn()
.forEach(function (pDate)
{
    var hour = new Date(datetime.localTimestamp(pDate)).getHours();
    dataArray[hour]++;
});

var categories = {
    OPENER : "OPENER"
};
var records = dataArray.map(function (clicksPerHour, i) 
{
    return [
        i,
        categories.OPENER,
        translate.text("Openers"),
        i,
        i.toString().padStart(2,0),
        clicksPerHour || 0
    ];
})

result.object(records);