import("system.util");
import("system.report");
import("system.neon");
import("system.vars");




var sumOfMonthTurnover = db.table("select year(SALESORDERDATE) yearNum, 'turnover', month(SALESORDERDATE) monthNum, sum(NET + VAT) from SALESORDER group by year(SALESORDERDATE), month(SALESORDERDATE) order by yearNum, monthNum");
var sumOfMonthForecast = db.table("select year(DATE_START) yearNum, 'forecast', month(DATE_START) monthNum, sum(VOLUME) from SALESPROJECT_FORECAST group by year(DATE_START), month(DATE_START) order by yearNum, monthNum");


var chartData = [];

var turnoverSkippedCount = 0;
var forecastSkippedCount = 0;

for (let i = 0; i < 4; i++)
{
    var year = i + 2016;
    
    _addMonthRows(year);
}

result.object(chartData);

function _addRow(pRow) 
{
    //logging.log(pRow.toSource())
    // month = 0 --> sum of the whole year
    var parent = "";
    var dateDisplay = pRow[0];
    
    if (pRow[2] != 0) 
    {
        parent = pRow[0] + "0" + pRow[1].trim();

        var rowDate = new Date(pRow[0], pRow[2]-1);
        dateDisplay = datetime.toDate(rowDate.getTime(), "MMM yyyy", "UTC")
    }
    
    chartData.push([pRow[0] + pRow[2] + pRow[1].trim(), parent, 
                    ((pRow[1].trim() == "turnover") ? translate.text("Turnover") : translate.text("Forecast")), parseFloat(pRow[3]), dateDisplay]);
}

function _addMonthRows(pYear) 
{
    var turnoverSkippedCount = 0;
   // var turnoverNotCorrectYearCount = 0;
    
    var forecastSkippedCount = 0;
    var forecastNotCorrectYearCount = 0;

    var turnoverYearSum = 0;
    var forecastYearSum = 0;

    var filteredTurnover = sumOfMonthTurnover.filter(function(row)
    {
        return row[0] == pYear
    });
    
    var filteredForecast = sumOfMonthForecast.filter(function(row)
    {
        return row[0] == pYear
    });

    for (let i = 1; i <= 12; i++) 
    {
        var turnoverSum = filteredTurnover[i - turnoverSkippedCount - 1];
        var forecastSum = filteredForecast[i - forecastSkippedCount - 1];
        
        if (turnoverSum != undefined && turnoverSum[0] == pYear && turnoverSum[2] == i.toString()) 
        {
            _addRow(turnoverSum);
            turnoverYearSum += turnoverSum[3];
        }
        else
        {
            _addRow([year.toString(), 'turnover', i, 0.0]);
            
            turnoverSkippedCount++;
        }
        
        if (forecastSum != undefined && forecastSum[0] == pYear && forecastSum[2] == i) 
        {
            _addRow(forecastSum);
            forecastYearSum += forecastSum[3];
        }
        else
        {
            _addRow([year.toString(), 'forecast', i, 0.0]);
            
            forecastSkippedCount++;
        }
    }
    
    _addRow([year.toString(), 'turnover', 0, turnoverYearSum]);
    _addRow([year.toString(), 'forecast', 0, forecastYearSum]);
}




/**
 *  
 * @class
 */
function MultiDataChart() 
{
    this._ID = 0;
    this._GROUP = 1;
    this._X = 2;
    this._Y = 3;
    
    this.dataSources = {};
    this.drilldowns = {};
    this._resultData = [];
}

MultiDataChart.begin = function() 
{
    var chart = new MultiDataChart();    
    return chart;
}

/**
 * Add a new datasource
 * Has to be an array with [[id, x, y, ...], [id, x, y, ...]]
 * ... means you can add additional columns, used as identification and naming of drilldowns
 */
MultiDataChart.prototype.addDataSource = function(pName, pData) 
{        
    this.dataSources[pName] = pData;
    
    return this;
}

/**
 * 
 */
MultiDataChart.prototype.addCumulativeDrillDown = function(pDataSourceName, pGetParentXCallback, pGetCummulationCallback) 
{        
    if (this.dataSources[pDataSourceName] != undefined) 
    {
        this.drilldowns[pDataSourceName].type = "cumulative";
        this.drilldowns[pDataSourceName].dataSource = pDataSourceName;
        this.drilldowns[pDataSourceName].getParentX = pGetParentXCallback;
        this.drilldowns[pDataSourceName].getCumulation = pGetCummulationCallback;
    }
    else 
    {
        throw Error("MultiDataChart::addCumulativeDrillDown: Data source " + pDataSourceName + " doesn't exist!");
    }
    
    return this;
}

/**
 * 
 */
MultiDataChart.prototype.build = function() 
{        
    for (dataSource in this.dataSources)
    {
        for (let i = 0; i < dataSources[dataSource].length; i++) 
        {
            var dataRow = dataSources[dataSource][i];
            var rowId = util.getNewUUID();
                                  // id, parent, group,     x,                      y
            this._resultData.push([rowId, "", dataSource, dataRow[this._X], dataRow[this._Y]]);
        }
    }
    
}

MultiDataChart.prototype._processDrilldowns = function()
{
    for (drilldown in this.drilldowns)
    {
        if (drilldown.type == "cumulative")
        {
            this._processCumulativeDrilldown(drilldown);
        }
    }
}

MultiDataChart.prototype._processCumulativeDrilldown = function(pDrilldown)
{
    var parentId = util.getNewUUID();
    var data = this.dataSources[pDrilldown.dataSource];
    
    
    
    
   
}