# 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)


      #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="");
#         warning(colName)
          p=(dta[,3]==v);
#          warning(sum(p))
          if(sum(p)>0) {
            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("^J",f)) { #----- Calculate J section -------------
      table="J";
      dta=data.frame(data$tableJ$tens)

      #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) {
          colName=paste(table,v,sep="");
#          print(colName)
          p=(dta[,"CODE"]==v);
#          print(sum(p))
          if(sum(p)>0) {
            tk=aggregate(dta[p,"AMOUNT"],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 vars


    } #end if J



  }

  attr(result,"formula")=formulaString;

  if(onlyValue) {return(result$value);}
  else{return(result);}

}


#' Dissagregates a string formula to a list(add=c("SE610","J830(2)","#289","#267..270"),substract=c("SE626","M632..634(2)"))
#'
#' @param formula a formula string, see examples
#' @return list(add=c(),substract=())
#' @export
#' @examples
#' formula="K120..148(7)+K120..148(8)+K120..148(9)+K120..148(10)-K120..148(6)"
#' formula="#48+#49+#50"
#

analyzeFormula=function(formula) {
  result=list(add=character(0),substract=character(0));
  formula=gsub("\\s","",formula);

  s1=strsplit(formula,"(?<=[-+])|(?=[-+])",perl=T)[[1]]

  doAdd=T;
  for(s in s1) {

    if(grepl("\\+",s)) {
      doAdd=T;
    }
    else if(grepl("\\-",s)) {
      doAdd=F;
    }
    else {
      if(doAdd){result$add=c(result$add,s)}
      else {result$substract=c(result$substract,s)}
    }
  }

  return(result);
}




translateTableCell=function(table,heading,col) {
  theMap=list();
  theMap$E=list("51"=c(232:233),
               "52"=c(234:236),
               "53"=c(237:239),
               "54"=c(240:242),
               "55"=c(243:245),
               "56"=c(246:248),
               "57"=c(249:251),
               "58"=c(252:254)
             );
  theMap$D=list("22"=c(86:90),
                "23"=c(91:95),
                "24"=c(96:100),
                "25"=c(101:105),
                "26"=c(106:110),
                "27"=c(111:115),
                "28"=c(116:120),
                "29"=c(121:125),
                "30"=c(126:130),
                "31"=c(131:135),
                "32"=c(136:140),
                "33"=c(141:145),
                "34"=c(146:150),
                "35"=c(151:155),
                "36"=c(156:160),
                "37"=c(161:165),
                "38"=c(166:170),
                "39"=c(171:175),
                "40"=c(176:180),
                "41"=c(181:185),
                "42"=c(186:190),
                "43"=c(191:195),
                "44"=c(196:200),
                "45"=c(201:205),
                "46"=c(206:210),
                "47"=c(211:215),
                "48"=c(216:220),
                "49"=c(221:225),
                "50"=c(NULL,227,NULL,229)
    );
}