# Calculate standard results
#


#' Aggregates columns for each farms using a formula
#'
#' @param data a fadn.container, containing all tables
#' @param SEdata a data.table of already calculated SE
#' @param formulaString The formula String to use for aggregation
#' @return [FID VALUE]
#' @export
#' @examples
#' #definition of formula SE610+SE615+SE624-SE626
#' formula=list(add=c("SE610","J830(2)","#289","#267..270"),substract=c("SE626","M632..634(2)"))
#' list(add=c("#48","#49","#50"),substract=list())
#'
#
getFormulaResult=function(data,SEdata,formulaString,aggregator=sum,onlyValue=T) {
  if(! ("fadn.container"%in%class(data))) {stop('data is not a fadn.container class')}

  result=data.frame(FID=data$tableAI$FID,value=rep(0,nrow(data$tableAI)));
  formula=analyzeFormula(formulaString);

  for(f in c(formula$add,formula$substract)) {

    #warning(f);

    if(sum(grepl(f,formula$add,fixed=T))>0) {doAdd=T;} else {doAdd=F;}


    if(grepl("^SE.",f)) { #----- Calculate SE section -------------
      if(! (f %in% names(SEdata))) {
        warning(paste(f," was not found on SEdata. It was ommited from the calculations.",sep=""))
      }
      else {
        result[,f]=SEdata[,f,with=F];
        if(doAdd) {result$value=result$value+result[,f];}
        else {result$value=result$value-result[,f];}
      }


    }
    else if(grepl("^#.",f)) { #----- Calculate fixed columns section -------------
      #warning("^#.");

      if(grepl("\\.\\.",f)) { #if a range of vars is given
        #warning("range")
        reg="^.(.+)(\\.\\.)(.+)$";

        from=as.numeric(gsub(reg,"\\1",f));
        to=as.numeric(gsub(reg,"\\3",f));
        vars=c(seq(from,to));
      }
      else {
        #warning("single")
        reg="^.(.+)$";
        vars=gsub(reg,"\\1",f);
      }

      for(v in vars) {
        #print(as.numeric(v))
        colName=paste("V",v,sep="");
        #print(colName);
        result[,colName]=data$tableAI[,as.numeric(v),with=FALSE];
        #print(data$tableAI[,as.numeric(v),with=FALSE])

        if(doAdd) {result$value=result$value+result[,colName];}
        else {result$value=result$value-result[,colName];}
      }


    }

    else if(grepl("^[K]",f)) { #----- Calculate K table section -------------
      #      warning("^[K]")
      table=gsub('^(.).+$','\\1',f)

      dta=as.data.frame(data$tableK)
#      print(dta)

      #find col range
      col_raw=gsub('^(.+?)(\\()(.+)(\\))$','\\3',f)
#      print(col_raw); print(grepl("\\|",col_raw));
      if(!grepl("\\:",col_raw)) { #check if there is conditional
        col=col_raw;
        cond.check=F
      }else { #if there is conditional
        cond.check=T
        col=gsub('(.+?)(\\:)(.+)$','\\1',col_raw);

        cond_raw=gsub('(.+?)(\\:)(.+)$','\\3',col_raw);
        cond.data=list();

        cond.data.raw=strsplit(cond_raw,"&",fixed = T)[[1]];
        for(cond.cur in cond.data.raw) {
          cond.data[[cond.cur]]=character();
          cond.col.val=strsplit(cond.cur,"=",fixed = T)[[1]]

          cond.data[[cond.cur]]["col"]=cond.col.val[1]
          cond.data[[cond.cur]]["val"]=cond.col.val[2]
          if(grepl("\\(",cond.col.val[2])) {
            cond.data[[cond.cur]]["val_type"]="c"
          } else {
            cond.data[[cond.cur]]["val_type"]="val"
          }
        }
#        print(cond_raw);print(cond_col);print(cond_val)
      } #end if conditional exists

      if(grepl("\\.\\.",col)) {
        reg='^(.+?)(\\.\\.)(.+)$'
        from=as.numeric(gsub(reg,"\\1",col));
        to=as.numeric(gsub(reg,"\\3",col));
        cols=c(seq(from,to));
      } else {
        cols=c(as.numeric(col));
      }
      #      print(cols)


      #find var range
      if(grepl("^(.+?)(\\.\\.)(.+?)\\(",f)) { #if a range of vars is given
        reg="^.(.+?)(\\.\\.)(.+?)\\((.+)$";
        from=as.numeric(gsub(reg,"\\1",f));
        to=as.numeric(gsub(reg,"\\3",f));
        vars=c(seq(from,to))
      }
      else {
        reg="^.(.+?)(\\(.+\\))$";
        vars=gsub(reg,"\\1",f);
      }
      #      print(vars)

      for(v in vars) {
        for(c in cols) {
          colName=paste(table,v,"(",c,")",sep="");
#          print(colName);
          if(cond.check==T) {
            cmd1="(dta[,1]==v";
            cmd2=""
            for(cond.cur in cond.data) {
#              print(cond.cur)
              cmd2=paste0(cmd2," & dta[,",cond.cur["col"],"]",sep="" )
              if(cond.cur["val_type"]=="val") {
                cmd2=paste0(cmd2,"==",cond.cur["val"],sep="")
              }else {
                cmd2=paste0(cmd2,"%in%c",cond.cur["val"],sep="")
              }

            }

            cmd=paste0(cmd1,cmd2,")",sep="");
#            print(cmd)
            p=eval(parse(text=cmd))
            #p=(dta[,1]==v & dta[,as.numeric(cond_col)]==as.numeric(cond_val));
#            print(sum(p))
#            str(dta)
#            print(dta[p,])
          }
          else {
            p=(dta[,1]==v);
          }

          if(sum(p)>0) {
#            print(c)
            tk=aggregate(dta[p,c],by=list(dta[p,"FID"]),aggregator)
          }
          else {
            tk=data.frame(FID=seq(1,nrow(data$tableAI)),value=rep(0,nrow(data$tableAI)));
          }
          names(tk)=c("FID",colName);
          #          print(head(tk))
          result=merge(result,tk,all.x=T,by="FID");
          result[is.na(result[,colName]),colName]=0;
          if(doAdd) {result$value=result$value+result[,colName];}
          else {result$value=result$value-result[,colName];}
        } #end for cols
      } #end for vars
    }

    else if(grepl("^[L,M,N]",f)) { #----- Calculate LMN section -------------
#      warning("^[L,M,N]")
      table=gsub('^(.).+$','\\1',f)

      dta=as.data.frame(data$tableJ$tens)

      #find col range
      col=gsub('^(.+)(\\()(.+)(\\))$','\\3',f)
      if(grepl("\\.\\.",col)) {
        reg='^(.+)(\\.\\.)(.+)$'
        from=as.numeric(gsub(reg,"\\1",col));
        to=as.numeric(gsub(reg,"\\3",col));
        cols=c(seq(from,to));
      }
      else {
        cols=c(as.numeric(col));
      }
#      print(cols)