/*
workbook = FileAttachment("output/xls/casestudies/dashboard.xlsx").xlsx()
//dashboarddata = workbook.sheet(0, {
//    headers: true,
    // range: "A1:J10"
  })
*/

test = "test"
//dashboarddata = getGoogleSheet('2PACX-1vSSeF-1yoFVB6f5DnSd8r5VDVkZVVe3V-KQ_1TyAZeqFfRvdCB9rS5im4daUII1UUc7sfjj2khgewzf')

cecb = new Map([
  ['A+',0.25],
  ['A',0.5],
  ['B+',0.75],
  ['B',1],
  ['C+',1.25],
  ['C',1.5],
  ['D+',1.75],
  ['D',2],
  ['E+',2.25],
  ['E',2.5],
  ['F+',2.75],
  ['F',3]
]);


/*
// kg CO2eq / kWh from Enercoach in kg/MJ (* 3.6)
THG_traeger = new Map([
  ['Holz',0.008 * 3.6], // holzpellet or stückholz
  ['Oel',0.084 * 3.6], // Oelheizung
  ['Gas',0.036 * 3.6], // biogas heizung
  ['Elektro',0.0039 * 3.6], // Energy mix ESB (other tab)
  //['PV',0.0139], // TODO to check with Effizienzpfad SIA 390/1
  ['PV',0.001], 
  ['WP',0.018 * 3.6], // WP luft wasser
  ['FW',0.022 * 3.6], // Champagne gurzelen mix (other tab)
]);
*/

/*
THG_traeger = new Map([
  ['Holz',THG_Holz],
  ['Oel',THG_Oel],
  ['Gas',THG_Gas],
  ['Elektro',THG_Elektro],
  ['PV',THG_PV],
  ['WP',THG_WP],
  ['FW',THG_FW],
]);


NEE_traeger = new Map([
  ['Holz',0.2], // TODO check if this makes sense should be 0.35
  ['Oel',1],
  ['Gas',1],
  ['Elektro',0.0001],
  ['PV',1],
  ['WP',0.36],
  ['FW',0.4],
]);
*/


function get_THG(we,fw,anlage) {
  return (
    we === 'FW' ? 
      FW_THG2[fw] : 
      sitesdata.filter(d => d.Anlage==anlage)[0].THG //THG_traeger.get(we)
  ) 
  }

function get_NEE(we,fw,anlage) {
  return (
    we === 'FW' ? 
      FW_NEE2[fw] : 
      sitesdata.filter(d => d.Anlage==anlage)[0].NEE //THG_traeger.get(we)
  ) 
}



function exclude(step,year) {
  return (year < 0 ?  step : 0)
}

function exclude_relative(step,year) {
  return (year < 0 ?  step : 1)
}


/*
Etraeger = [
  'Solarthermie',
  'Umweltwaerme',
  'Holzpellets',
  'Erdgas',
  'Biogas',
  'Heizoel',
  'Fernwaerme',
  'Elektro_NE',
  'Elektro_E',
  'ProdPV',
  'WE1',
  'WE2',
  'WE3',
]
*/

Etraeger = dashboarddatafactors.map(row => {return row['WE / FW']}).slice(0, dashboarddatafactors.map(row => {return row['WE / FW']}).indexOf('XXXXXXXXXX'))
Etraeger.push('Fernwaerme')

FWnetze = dashboarddatafactors.map(row => {return row['WE / FW']}).slice( dashboarddatafactors.map(row => {return row['WE / FW']}).indexOf('XXXXXXXXXX')+1,-1)


// Qh_li data from SIA 380/1:2016 p23
SIA = [
  {SIAcat:'I Wohnen MFH',qh_li0:13,delta_qh_li:15},
  {SIAcat:'II Wohnen EFH',qh_li0:16,delta_qh_li:15},
  {SIAcat:'III Verwaltung',qh_li0:13,delta_qh_li:15},
  {SIAcat:'IV Schulen',qh_li0:14,delta_qh_li:15},
  {SIAcat:'V Verkauf',qh_li0:7,delta_qh_li:14},
  {SIAcat:'VI Restaurants',qh_li0:16,delta_qh_li:15},
  {SIAcat:'VII Versammlunglokale',qh_li0:18,delta_qh_li:15},
  {SIAcat:'VIII Spitäler',qh_li0:18,delta_qh_li:17},
  {SIAcat:'IX Industrie',qh_li0:10,delta_qh_li:14},
  {SIAcat:'X Lager',qh_li0:14,delta_qh_li:14},
  {SIAcat:'XI Sportbauten',qh_li0:16,delta_qh_li:14},
  {SIAcat:'XII Hallenbäder',qh_li0:15,delta_qh_li:18}
  ]


sitesdata = dashboarddatasites.map(row => {

  // compute site GHZ from EBF-weighted buildings GHZ 
  // TODO harmonize EBF (sum buildings EBF != site EBF)
  let ghz = d3.sum(dashboarddata.filter(d => d.Anlage == row.Anlage).map(d => d.EBF*d.GHZ))/d3.sum(dashboarddata.filter(d => d.Anlage == row.Anlage).map(d => d.EBF)) 

  // get FW value from first building in Anlage 
  let fw = dashboarddata.filter(d => d.Anlage == row.Anlage)[0].FW

  let pef = d3.sum(Etraeger.map(j => (j=="Fernwaerme" ? FW_PEF[fw] : WE_PEF[j]) * row[j]/ row.Korrekturfaktor ))
  
  let nee = d3.sum(Etraeger.map(j => (j=="Fernwaerme" ? FW_NEE2[fw] : WE_NEE[j]) * row[j]/ row.Korrekturfaktor ))

  let thg = d3.sum(Etraeger.map(j => (j=="Fernwaerme" ? FW_THG2[fw] : WE_THG[j]) * row[j]/ row.Korrekturfaktor ))


  // all values are absolute 
  // to be divided when attributing later to buildings
  return {
    ...row,
    GHZ : ghz,
    qhli : d3.sum(SIA.map(e => row[e.SIAcat] * (e.qh_li0 + ghz * e.delta_qh_li) * (1+(9.4-9.1)*0.06))),
    PEF : pef,
    NEE : nee/pef,
    THG : thg/pef,
  }
})





// only compute step-wise influence, which require to compute for each step subsequently 
// Actuel/Renov are absolute
// Optim/CAD are relative
// PVx are deltas
qh_step = dashboarddata.map(row => {
    let anlage = sitesdata.filter(d => d.Anlage==row.Anlage)[0]
    let qh = anlage.PEF * row.Anteil / row.EBF
    //let qh = row.PEF / row.EBF
    // compute Qhli from SIA 380/1:2016 p23 using school limit values.
    // example computation for schools 
    //let qh_li = (14 + 15 *row.GHZ) * (1+(9.4-9.1)*0.06) // (Qhli0 + deltaQhli x GHZ) x f_corr
    let qh_li = anlage.qhli * row.Anteil / row.EBF
    return {
      ...row,
      Actuel: qh,              // absolute
      Optim:  (1-row.Optim/100),   // relative
      CAD: 1,                  // relative
      PV1: - row.PV1 / row.EBF, // delta
      //yearPV1: row.yearPV1,  
      PV2: - row.PV2 / row.EBF, // delta
      //yearPV2: row.yearPV2,  
      PV3: - row.PV3 / row.EBF, // delta
      //yearPV3: row.yearPV3,
      Renov: (cecb.has(row.Renov)) ? qh_li * cecb.get(row.Renov) : qh,   // absolute
      RenovGlob: (cecb.has(row.RenovGlob)) ? qh_li * cecb.get(row.RenovGlob) : qh   // absolute
    }
})



function to_NEE(_data) { return _data.map(row => {
    let holz = (row.WE === 'Holz' ?  WE_NEE['Holzpellet'] : 1)
    //let fw = (row.WE === 'Holz' ?  WE_NEE['Holzpellet'] : (row.WE === 'FW' ?  1 : FW_NEE2[row.FW]))
    let fw = (row.WE === 'Holz' ?  1 : (row.WE === 'FW' ?  1 : FW_NEE2[row.FW]))
    let fw_abs = (row.WE === 'Holz' ?  WE_NEE['Holzpellet'] : (row.WE === 'FW' ?  1 : FW_NEE2[row.FW]))
    return {
      ...row,
      Actuel: row.Actuel, //* holz,
      Optim:  row.Optim,
      CAD: fw * row.CAD,
      //PV1: row.PV1,
      //PV2: row.PV2,
      //PV2: row.PV2,
      Renov: row.Renov * (steps.includes('CAD') ? fw_abs : 1), 
      RenovGlob: row.RenovGlob * (steps.includes('CAD') ? fw_abs : 1) 
    }
  })
}

function to_THG(_data) { return _data.map(row => {
    //let holz_fw = (row.WE === 'FW' ?  1 : (row.WE === 'Holz' ?  get_THG(row.WE,row.FW,row.Anlage) : get_THG(row.WE,row.FW,row.Anlage)))
    //let holz_fw = (row.WE === 'FW' ?  1 :  get_THG(row.WE,row.FW,row.Anlage) )
    return {
      ...row,
      Actuel: row.Actuel * get_THG(row.WE,row.FW,row.Anlage),
      Optim: row.Optim,
      //CAD: row.CAD * (row.WE === 'Holz' ?  THG_traeger.get('Holz') : FW_THG2[row.FW] / get_THG(row.WE,row.FW,row.Anlage)), // divide because multiplied on Actuel in scenario
      CAD: row.CAD * (row.WE === 'Holz' ?  1 : FW_THG2[row.FW] / get_THG(row.WE,row.FW,row.Anlage)), // divide because multiplied on Actuel in scenario
      PV1: row.PV1 * WE_THG['ProdPV'],
      PV2: row.PV2 * WE_THG['ProdPV'],
      PV3: row.PV3 * WE_THG['ProdPV'],
      Renov: row.Renov * (steps.includes('CAD') ? (row.WE === 'Holz' ?  WE_THG['Holzpellet'] : FW_THG2[row.FW]) : get_THG(row.WE,row.FW,row.Anlage)),
      RenovGlob: row.RenovGlob * (steps.includes('CAD') ? (row.WE === 'Holz' ?  WE_THG['Holzpellet'] : FW_THG2[row.FW]) : get_THG(row.WE,row.FW,row.Anlage))
    }
  })
}

function to_absolute(_data) {
  return _data.map(row => {
    return {
      ...row,
      Actuel: row.Actuel * row.EBF,
      Optim: row.Optim * row.EBF,
      CAD: row.CAD * row.EBF,
      PV1: row.PV1 * row.EBF,
      PV2: row.PV2 * row.EBF,
      PV3: row.PV3 * row.EBF,
      Renov: row.Renov * row.EBF,
      RenovGlob: row.RenovGlob * row.EBF
    }
  })
}


// this function defines what is included in each step
function scenario(_data,_steps) {

  return _data.map(row => {
    let optim = (steps.includes('Optim') ? row.Optim : 1) * row.Actuel
    let cad = optim * row.CAD 
    let cad_or_not = (_steps.includes('CAD') ? cad : (_steps.includes('Optim') ? optim : row.Actuel))
    let optimRenov = (steps.includes('Optim') ? row.Optim : 1) * row.Renov
    let optimRenovGlob = (steps.includes('Optim') ? row.Optim : 1) * row.RenovGlob
    return {
      ...row,
      //Actuel: row.Actuel,
      Optim: optim,
      CAD: cad,
      PV1: cad_or_not + (_steps.includes('PV1') ? row.PV1 : 0),
      PV2: cad_or_not + (_steps.includes('PV1') ? row.PV1 : 0) + (_steps.includes('PV2') ? row.PV2 : 0),
      PV3: cad_or_not + (_steps.includes('PV1') ? row.PV1 : 0) + (_steps.includes('PV2') ? row.PV2 : 0) + (_steps.includes('PV3') ? row.PV3 : 0),
      Renov: optimRenov + (_steps.includes('PV1') ? row.PV1 : 0) + (_steps.includes('PV2') ? row.PV2 : 0) + (_steps.includes('PV3') ? row.PV3 : 0),
      RenovGlob: optimRenovGlob + (_steps.includes('PV1') ? row.PV1 : 0) + (_steps.includes('PV2') ? row.PV2 : 0) + (_steps.includes('PV3') ? row.PV3 : 0)
    }
  })
}





steplist = ['Actuel','Optim','CAD','PV1','PV2','PV3','Renov','RenovGlob']

// create base data structure for plotting (adding the anlage column)
datatoplot = d3.cross(scenario(to_NEE(qh_step),steps).map(r => r.Gebäude),steplist).map(row => _.zipObject(['Gebäude','Step'],row)).map(row => {
  return {
  ...row,
  Anlage: qh_step.filter(r => r.Gebäude === row.Gebäude)[0].Anlage
  }
})

NEE = scenario(to_NEE(qh_step),steps)
THG = scenario(to_THG(qh_step),steps)

     //NEE: scenario(data_NEE,steps).filter(r => r.Anlage == row.Anlage ).map(r => r[row.Step])[0],
      //THG: scenario(data_THG,steps).filter(r => r.Anlage == row.Anlage ).map(r => r[row.Step])[0],
 
// populate base data structure with computed data
data = datatoplot.map(row => { 
  return {...row,
      NEE: NEE.filter(r => r.Gebäude == row.Gebäude ).map(r => r[row.Step])[0],
      THG: THG.filter(r => r.Gebäude == row.Gebäude ).map(r => r[row.Step])[0],
      'NEE abs': to_absolute(NEE).filter(r => r.Gebäude == row.Gebäude ).map(r => r[row.Step])[0],
      'THG abs': to_absolute(THG).filter(r => r.Gebäude == row.Gebäude ).map(r => r[row.Step])[0]
     }
  }
)

// moyenne

function meanPerformance(_name,_data_NEE,_data_THG,sumSRE) {
  return [ 
      { Gebäude:_name, Step: 'Actuel', NEE: d3.sum(_data_NEE,c => c.Actuel) / sumSRE, THG: d3.sum(_data_THG,c => c.Actuel) / sumSRE },
      { Gebäude:_name, Step: 'Optim', NEE: d3.sum(_data_NEE,c => c.Optim) / sumSRE, THG: d3.sum(_data_THG,c => c.Optim) / sumSRE },
      { Gebäude:_name, Step: 'CAD', NEE: d3.sum(_data_NEE,c => c.CAD) / sumSRE, THG: d3.sum(_data_THG,c => c.CAD) / sumSRE },
      { Gebäude:_name, Step: 'PV1', NEE: d3.sum(_data_NEE,c => c.PV1) / sumSRE, THG: d3.sum(_data_THG,c => c.PV1) / sumSRE },
      { Gebäude:_name, Step: 'PV2', NEE: d3.sum(_data_NEE,c => c.PV2) / sumSRE, THG: d3.sum(_data_THG,c => c.PV2) / sumSRE },
      { Gebäude:_name, Step: 'PV3', NEE: d3.sum(_data_NEE,c => c.PV3) / sumSRE, THG: d3.sum(_data_THG,c => c.PV3) / sumSRE },
      { Gebäude:_name, Step: 'Renov', NEE: d3.sum(_data_NEE,c => c.Renov) / sumSRE, THG: d3.sum(_data_THG,c => c.Renov) / sumSRE },
      { Gebäude:_name, Step: 'RenovGlob', NEE: d3.sum(_data_NEE,c => c.RenovGlob) / sumSRE, THG: d3.sum(_data_THG,c => c.RenovGlob) / sumSRE },
    ]
}

mean = meanPerformance('mean',to_absolute(NEE),to_absolute(THG),d3.sum(NEE, d => d.EBF))


filtered = data.filter(function(b) {
  return steps.includes(b.Step) && sites.includes(b.Anlage);
  //return sites.includes(b.Anlage);
})

filtered_mean = mean.filter(function(b) {
  return steps.includes(b.Step);
})

// mean filtered by anlage
mean_anlage = meanPerformance('mean selected',
  to_absolute(NEE.filter(function(b) {return sites.includes(b.Anlage)})),
  to_absolute(THG.filter(function(b) {return sites.includes(b.Anlage)})),
  d3.sum(NEE.filter(function(b) {return sites.includes(b.Anlage)}), d => d.EBF))

filtered_mean_anlage = mean_anlage.filter(function(b) {
  return steps.includes(b.Step);
})


// //////////////////////////
// Timeline
// //////////////////////////




stepYear = new Map([
  ['Actuel','yearActuel'],
  ['Optim','yearOptim'],
  ['CAD','yearFW'],
  ['PV1','yearPV1'],
  ['PV2','yearPV2'],
  ['PV3','yearPV3'],
  ['Renov','yearRenov'],
  ['RenovGlob','yearRenovGlob'],
]);


function to_date(year) {
  return new Date(Date.parse(year+"-01-01"))
}


function get_year(row,type) {
  let Ystr = row[stepYear.get(type)]
  return to_date(Ystr)
}

function deltaPV_comp(row,step) {
  let ret = 0
  ret = ret + ((get_year(row,'PV1') <= step.year) && steps.includes('PV1') ? row.PV1 : 0)
  ret = ret + ((get_year(row,'PV2') <= step.year) && steps.includes('PV2')  ? row.PV2 : 0)
  ret = ret + ((get_year(row,'PV3')  <= step.year) && steps.includes('PV3')  ? row.PV3 : 0)    

// - ((row[stepYear.get('PV1')] < step.year) && steps.includes('PV1') ? -row.PV1 : 0) - ((row[stepYear.get('PV2')] < step.year) && steps.includes('PV2')  ? -row.PV2 : 0) - ((row[stepYear.get('PV3')]  < step.year) && steps.includes('PV3')  ? -row.PV3 : 0)
  return ret
}



// qh_step
//      Actuel: qh,              // absolute
//      Optim:  (1-row.Optim/100),   // relative
//      CAD: 1,                  // relative
//      PV1: - row.PV1 / row.EBF, // delta
//      PV2: - row.PV2 / row.EBF, // delta
//      PV3: - row.PV3 / row.EBF, // delta
//      Renov: (cecb.has(row.Renov)) ? qh_li * cecb.get(row.Renov) : qh   // absolute
//      RenovGlob: (cecb.has(row.RenovGlob)) ? qh_li * cecb.get(row.RenovGlob) : qh   // absolute


// TODO optim only for renov <= D
function optim_step(row,prev,step,type) {
  
  let deltaPV = deltaPV_comp(row,step)

  if (type == 'THG') {
    deltaPV = deltaPV * WE_THG['ProdPV']//THG_traeger.get('PV')
  }
  // TODO make optimisation renovation-dependent
  //return ((row[stepYear.get('Renov')] < step.year) && (row.Renov in ["A","B","C","D"]) ? prev : (prev - deltaPV) * step.qh + deltaPV)
  //return (get_year(row,'Renov') < step.year)  ? prev : (prev - deltaPV) * step.qh + deltaPV
  return (prev - deltaPV) * step.qh + deltaPV

}

function CAD_step(row,prev,step,type) {

  let deltaPV =  deltaPV_comp(row,step)

  let fw = -1

  if (type == 'NEE') {
    // if WE = FW NEE computation already included in PEF
    // TODO PV also included?
    // divide by existing since can be not optimal
    fw = (row.WE === 'FW' ?  1 : FW_NEE2[row.FW]/get_NEE(row.WE,row.FW,row.Anlage))
  }
  else if (type == 'THG') {
    deltaPV = deltaPV * WE_THG['ProdPV']//THG_traeger.get('PV')

    // TODO if WE = Holz do not connect ?
    //let fw = (row.WE === 'Holz' ?  THG_traeger.get('Holz') : FW_THG2[row.FW] / get_THG(row.WE,row.FW,row.Anlage)) // divide because multiplied in Actuel
    // do not divide if Holz because == 0
    //fw = (row.WE === 'Holz' ?  FW_THG2[row.FW] : FW_THG2[row.FW] / get_THG(row.WE,row.FW,row.Anlage)) // divide     
    fw = FW_THG2[row.FW] / get_THG(row.WE,row.FW,row.Anlage) // divide because multiplied in Actuel
  }

  return (prev - deltaPV) * fw + deltaPV
}


function PV_step(prev,step,type) {

  if (type == 'NEE') {
    return prev + step.qh
  }
  else if (type == 'THG') {
    return prev + step.qh * WE_THG['ProdPV']//THG_traeger.get('PV')
  }
}




// TODO optim only for renov <= D
function Renov_step(row,step,type) {

  let deltaPV =  deltaPV_comp(row,step)

  // if optim before and included, apply it
  let optim = ((get_year(row,'Optim') <= step.year) && steps.includes('Optim')) ? row.Optim : 1 

  let fw_abs = -1
  if (type == 'NEE') {

    // no CAD
    //fw_abs = (row.WE === 'Holz' ?  WE_NEE.get('Holzpellet') : (row.WE === 'FW' ?  1 : NEE_traeger.get(row.WE)))
    fw_abs = get_NEE(row.WE,row.FW,row.Anlage)
    // if CAD
    fw_abs = ((get_year(row,'CAD') <= step.year) && steps.includes('CAD')) ? FW_NEE2[row.FW] : fw_abs 
    
  }
  else if (type == 'THG') {

    deltaPV = deltaPV * WE_THG['ProdPV']//THG_traeger.get('PV')

    // no CAD
    //fw_abs = (row.WE === 'Holz' ?  THG_traeger.get('Holz') : get_THG(row.WE,row.FW,row.Anlage))
    fw_abs = get_THG(row.WE,row.FW,row.Anlage)
    
    // if CAD
    fw_abs = ((get_year(row,'CAD') <= step.year) && steps.includes('CAD')) ?  FW_THG2[row.FW] : fw_abs 


    //fw_abs = (row.WE === 'Holz' ?  THG_traeger.get('Holz') : ((get_year(row,'CAD') < step.year) && steps.includes('CAD')) ?  FW_THG2[row.FW] :  get_THG(row.WE,row.FW,row.Anlage) )
        
  }

  return (step.qh * optim * fw_abs) + deltaPV
}








function computestep_NEE(prev,step) {

  if (steps.includes(step.Step)) {
    
    let row = qh_step.filter(r => r.Gebäude == step.Gebäude)[0]

    let deltaPV =  deltaPV_comp(row,step)
    
    if (step.Step === 'Optim' ) {
      return optim_step(row,prev,step,'NEE') //((get_year(row,'Renov') < step.year)  ? prev : (prev - deltaPV) * step.qh + deltaPV)
    } else if (step.Step === 'CAD') {
      //let fw = (row.WE === 'FW' ?  1 : FW_NEE2[row.FW])
      return CAD_step(row,prev,step,'NEE') //(prev - deltaPV) * fw + deltaPV
    } else if (step.Step === 'PV1') {
      return PV_step(prev,step,'NEE') //prev + step.qh
    } else if (step.Step === 'PV2') {
      return PV_step(prev,step,'NEE') //prev + step.qh
    } else if (step.Step === 'PV3') {
      return PV_step(prev,step,'NEE') //prev + step.qh
    } else if (step.Step === 'Renov') { //
      //let fw_abs = (row.WE === 'Holz' ?  NEE_traeger.get('Holz') : (row.WE === 'FW' ?  1 : NEE_traeger.get(row.WE)))
      //fw_abs = ((get_year(row,'CAD') < step.year) && steps.includes('CAD')) ? FW_NEE2[row.FW)] : fw_abs 
      //let optim = ((get_year(row,'Optim') < step.year) && steps.includes('Optim')) ? row.Optim : 1 
      return Renov_step(row,step,'NEE') //(step.qh * optim * fw_abs) + deltaPV
      //return deltaPV
    } else if (step.Step === 'RenovGlob') { //
      return Renov_step(row,step,'NEE') 
    }
  }
  else {return prev}
}



function computestep_THG(prev,step) {
  if (steps.includes(step.Step)) {

    let row = qh_step.filter(r => r.Gebäude == step.Gebäude)[0]

    let deltaPV =  deltaPV_comp(row,step) * WE_THG['ProdPV']//THG_traeger.get('PV')


    if (step.Step === 'Optim') {
      return optim_step(row,prev,step,'THG') //((get_year(row,'Renov') < step.year) ? prev : (prev - deltaPV) * step.qh + deltaPV)
    } else if (step.Step === 'CAD') {
      //let fw = (row.WE === 'Holz' ?  THG_traeger.get('Holz') : FW_THG2[row.FW] / get_THG(row.WE,row.FW,row.Anlage)) // divide because multiplied in Actuel
      return CAD_step(row,prev,step,'THG') //(prev - deltaPV) * fw + deltaPV
    } else if (step.Step === 'PV1') {
      return PV_step(prev,step,'THG') //prev + step.qh * THG_traeger.get('PV')
    } else if (step.Step === 'PV2') {
      return PV_step(prev,step,'THG') //prev + step.qh * THG_traeger.get('PV')
    } else if (step.Step === 'PV3') {
      return PV_step(prev,step,'THG') //prev + step.qh * THG_traeger.get('PV')
    } else if (step.Step === 'Renov') {
      //let fw_abs = (row.WE === 'Holz' ?  THG_traeger.get('Holz') : ((get_year(row,'CAD') < step.year) && steps.includes('CAD')) ?  FW_THG2[row.FW] :  get_THG(row.WE,row.FW,row.Anlage) )
      //let optim = ((get_year(row,'Optim') < step.year) && steps.includes('Optim')) ? row.Optim : 1
      return Renov_step(row,step,'THG') //(step.qh * optim * fw_abs) + deltaPV
    } else if (step.Step === 'RenovGlob') { //
      return Renov_step(row,step,'THG') 
    }
  }
  else {return prev}
}


// initialize step to current traeger (NEE or THG)
function traeger(col,an) {

  let row = qh_step.filter(r => r.Gebäude == an)[0]

  if (col.includes('NEE')) {
    //return (row.WE === 'Holz' ?  NEE_traeger.get('Holz') : 1)
    //return NEE_traeger.get(row.WE)
    return get_NEE(row.WE,row.FW,row.Anlage)
  }
  else if (col.includes('THG')) {
    return get_THG(row.WE,row.FW,row.Anlage)
  }
}


function step_by_step(_data_time,func,col) {

  //let ret = JSON.parse(JSON.stringify(_data_time));
  let ret = _data_time

  // group by building_grou 
  let _gr = d3.groups(ret,d => d.Gebäude)
  for (let i = 0; i < _gr.length ; ++i) {
    let building_group = _gr[i][1]
    
    let step = []

    // first step
    
    // compute NEE / THG
    step[0] = building_group[0].qh * traeger(col,building_group[0].Gebäude)
    // assign value in table
    ret.filter(r => r.Gebäude == building_group[0].Gebäude && r.Step == building_group[0].Step)[0][col] = step[0]

    // next steps
    for (let j = 1; j < building_group.length ; j++) {
      // compute NEE / THG
      step[j] = func(step[j-1],building_group[j])
      // assign value in table
      ret.filter(r => r.Gebäude == building_group[j].Gebäude && r.Step == building_group[j].Step)[0][col] = step[j] 
    }
  }
  return ret;
}

// sort steps by year
data_time = d3.sort(
  datatoplot.filter(row => { 
            return qh_step.filter(r => r.Gebäude == row.Gebäude).map(r => r[stepYear.get(row.Step)])[0] > 0 // select only defined years
          }).map(row => { 
            return {...row,
              qh: qh_step.filter(r => r.Gebäude == row.Gebäude ).map(r => r[row.Step])[0],
              year: to_date(qh_step.filter(r => r.Gebäude == row.Gebäude ).map(r => r[stepYear.get(row.Step)])[0]),
            }
          }
  ), (d) => d.year)

// apply step by step functions NEE and THG
data_timeline = step_by_step(step_by_step(data_time,computestep_NEE,'NEE_step'),computestep_THG,'THG_step').filter(function(b) { return steps.includes(b.Step) && sites.includes(b.Anlage); })

data_timeline_all = step_by_step(step_by_step(data_time,computestep_NEE,'NEE_step'),computestep_THG,'THG_step').filter(function(b) { return steps.includes(b.Step) })


function to_absolute_step(_data_step,_qh_step) {

  return _data_step.map(row => {

    let EBF = _qh_step.filter(b => b.Gebäude == row.Gebäude)[0].EBF

    return {
      ...row,
      NEE_step: row.NEE_step * EBF,
      THG_step: row.THG_step * EBF
    }
  })
}

data_timeline_absolute = to_absolute_step(data_timeline,qh_step)
data_timeline_all_absolute = to_absolute_step(data_timeline_all,qh_step)



// //////////////////////
// timeline means
// //////////////////////

function mean_tmp(_ddata,_qhstep,_value) {

  return d3.groups(_ddata,d => d.Gebäude).map(d => { 
      let arr = _.range(2025, 2051)
      let ret = {}
      let j=0
      let geb = d[0] // gebäude name
      let row = d[1][j] // step year
      let curr = row[_value] 
      for (let i = 0; i < arr.length; i++) {
        // check next step if exists
        if (j < d[1].length-1) {
          if (new Date(arr[i]+'-01-01') >= d[1][j+1].year) {
            // switch to next step
            j=j+1
            row = d[1][j]
          }
          curr = row[_value] 
        }
        // raise to absolute value
        ret[arr[i]] = curr //* _qhstep.filter(d => d.Gebäude == geb)[0].EBF
      }
      return ret
    })
}

function mean_step(_data,_qh_step, abs, filter) {

  let mean_NEE = mean_tmp(_data,_qh_step,'NEE_step')
  let mean_THG = mean_tmp(_data,_qh_step,'THG_step')

  // if computing absolute value, divide
  let sumEBF = -1

  let _qh = (filter) ? _qh_step.filter(b => sites.includes(b.Anlage)) : _qh_step

  if (abs) {
    sumEBF = d3.sum( _qh, d => 1)
  }
  else {
    sumEBF = d3.sum( _qh, d => d.EBF)
  }
  //return [{test:sumEBF},{test:sumEBF}]
  //return mean_NEE
  return _.range(2025, 2051).map((c) => {
      return {year : to_date(c),NEE : d3.sum(mean_NEE, (d) => d[c]/sumEBF),THG : d3.sum(mean_THG, (d) => d[c]/sumEBF)}
  }) 
}

mean_steps = mean_step(data_timeline_all_absolute,qh_step, false, false)
mean_steps_absolute = mean_step(data_timeline_all_absolute,qh_step, true, false)

mean_steps_filter = mean_step(data_timeline_absolute,qh_step, false, true)
mean_steps_absolute_filter = mean_step(data_timeline_absolute,qh_step, true, true)