library(XBRL)

inst <- "http://edgar.sec.gov/Archives/edgar/data/21344/000002134414000008/ko-20131231.xml"
options(stringsAsFactors = FALSE)
xbrl.vars <- xbrlDoAll(inst, cache.dir = "XBRLcache", prefix.out = NULL)

str(xbrl.vars, max.level = 1)

library(dplyr)

xbrl.vars$fact %>%
  filter(elementId == "us-gaap_SalesRevenueGoodsNet") %>%
  left_join(xbrl.vars$context, by = "contextId") %>%
  filter(is.na(dimension1)) %>%
  select(startDate, endDate, fact, unitId, elementId) %>% 
  (knitr::kable)(format = "markdown")

table(xbrl.vars$role$type)

htmlTable::htmlTable(data.frame(Statements=
  with(
    xbrl.vars$role[xbrl.vars$role$type=="Statement", ],
    paste(roleId, "\n<br/>", definition, "\n<p/>")
  )),
  align = "l",
  rnames = FALSE
)

library(tidyr)
library(dplyr)

# let's get the balace sheet
role_id <- "http://www.thecocacolacompany.com/role/ConsolidatedBalanceSheets"

# prepare presentation linkbase : 
# filter by role_id an convert order to numeric
pres <- 
  xbrl.vars$presentation %>%
  filter(roleId %in% role_id) %>%
  mutate(order = as.numeric(order))

# start with top element of the presentation tree
pres_df <- 
  pres %>%
  anti_join(pres, by = c("fromElementId" = "toElementId")) %>%
  select(elementId = fromElementId)

# breadth-first search
while({
  df1 <- pres_df %>%
    na.omit() %>%
    left_join( pres, by = c("elementId" = "fromElementId")) %>%
    arrange(elementId, order) %>%
    select(elementId, child = toElementId);
  nrow(df1) > 0
}) 
{
  # add each new level to data frame
  pres_df <- pres_df %>% left_join(df1, by = "elementId")
  names(pres_df) <-  c(sprintf("level%d", 1:(ncol(pres_df)-1)), "elementId")
}
# add last level as special column (the hierarchy may not be uniformly deep)
pres_df["elementId"] <- 
  apply( t(pres_df), 2, function(x){tail( x[!is.na(x)], 1)})
pres_df["elOrder"] <- 1:nrow(pres_df) 

# the final data frame structure is
str(pres_df, vec.len = 1 )

# join concepts with context, facts
pres_df_num <-
  pres_df %>%
  left_join(xbrl.vars$fact, by = "elementId") %>%
  left_join(xbrl.vars$context, by = "contextId") %>%
  filter(is.na(dimension1)) %>%
  filter(!is.na(endDate)) %>%
  select(elOrder, contains("level"), elementId, fact, decimals, endDate) %>%
  mutate( fact = as.numeric(fact) * 10^as.numeric(decimals)) %>%
  spread(endDate, fact ) %>%
  arrange(elOrder)

library(pander)
pres_df_num %>% 
  select(elementId, contains("2013"), contains("2012")) %>%
  pandoc.table(
    style = "rmarkdown",
    split.table = 200,
    justify = c("left", "right", "right")
  )

# labels for our financial statement (role_id) in "en-US" language:
x_labels <-
  xbrl.vars$presentation %>%
  filter(roleId == role_id) %>%
  select(elementId = toElementId, labelRole = preferredLabel) %>%
  semi_join(pres_df_num, by = "elementId") %>%
  left_join(xbrl.vars$label, by = c("elementId", "labelRole")) %>%
  filter(lang == "en-US") %>%
  select(elementId, labelString)

# calculated elements in this statement component
x_calc <- xbrl.vars$calculation %>%
  filter(roleId == role_id) %>%
  select(elementId = fromElementId, calcRoleId = arcrole) %>%
  unique()

# join concepts and numbers with labels
balance_sheet_pretty <- pres_df_num %>%
  left_join(x_labels, by = "elementId") %>%
  left_join(x_calc, by = "elementId") %>%
  select(labelString, contains("2013"), contains("2012"), calcRoleId)


names(balance_sheet_pretty)[1] <- 
  "CONDENSED CONSOLIDATED BALANCE SHEETS (mio USD $)"

names(balance_sheet_pretty)[2:3] <-
 format(as.Date(names(balance_sheet_pretty)[2:3]), "%Y")
# rendering balance sheet

pandoc.table(
  balance_sheet_pretty[,1:3],
  style = "rmarkdown",
  justify = c("left", "right", "right"),
  split.table = 300,
  big.mark = ",",
  emphasize.strong.rows = which(!is.na(balance_sheet_pretty$calcRoleId))
)

role_id <- "http://www.thecocacolacompany.com/role/ConsolidatedBalanceSheets"

relations <- 
  xbrl.vars$calculation %>% 
  filter(roleId == role_id) %>% 
  select(fromElementId, toElementId, order)

elements <-
  data.frame( 
    elementId = with(relations, unique(c(fromElementId, toElementId))),
    stringsAsFactors = FALSE
  )  %>%
  left_join(xbrl.vars$element, by = c("elementId")) %>%
  left_join(relations, by = c("elementId" = "toElementId")) %>%
  left_join(xbrl.vars$label, by = c("elementId")) %>%
  filter(labelRole == "http://www.xbrl.org/2003/role/label") %>% 
  transmute(elementId, parentId = fromElementId, order, balance, labelString)

# get top element(s) in hierarchy  
level <- 1
df1 <- elements %>%
  filter(is.na(parentId)) %>%
  mutate(id = "") %>% 
  arrange(desc(balance))

# search the tree
while({
  level_str <- 
    unname(unlist(lapply(split(df1$id, df1$id), function(x) {
      sprintf("%s%02d", x, 1:length(x))
    })))
  
  elements[elements$elementId %in% df1$elementId, "level"] <- level
  to_update <- elements[elements$elementId %in% df1$elementId, "elementId"]
  elements[ 
    #order(match(elements$elementId, to_update))[1:length(level_str)], 
    order(match(elements$elementId, df1$elementId))[1:length(level_str)], 
    "id"] <- level_str
  
  df1 <- elements %>%
    filter(parentId %in% df1$elementId) %>%
    arrange(order) %>%
    select(elementId, parentId) %>%
    left_join(elements, by=c("parentId"="elementId")) %>%
    arrange(id)
  nrow(df1) > 0})
{
  level <- level + 1
}

# order by hierarchy ID and mark terminal nodes 
elements <- 
  elements %>%  
  dplyr::arrange_(~id) %>% 
  dplyr::mutate( 
    terminal = !elementId %in% parentId,
    Element = paste(
      substring(paste(rep("&nbsp;",10), collapse = ""), 1, (level-1)*2*6),
      gsub("us-gaap_", "",elementId)
    )
  )

pandoc.table(
  elements[, c("Element", "balance", "id")],
  style = "rmarkdown",
  justify = c("left", "left", "left"),
  split.table = 300,
  emphasize.strong.rows = which(elements$level == 1)
)