setScience("IODA_DATA", subProj="DetailedSalesReport")

# NOTE: The exchange_rate is from what the STORE paid us to USD (or perhaps to the preferred_currency_code)
# For the case of ABC, all of preferred_currency_code is USD
# Hence, dropping "_oc" columns and other currency information from the agg table

setGitBranchToSystem(); .g();
loadFromJesus("DT.country", over=TRUE)

f.in <- ingest.p("Label_18000_IODA_Detailed_Sales.csv")
DT.dsr <- fread(f.in, sep=",")
cleanColNamesForSQL_(DT.dsr)

include_store <- FALSE
include_activity_month <- FALSE

## NEEDS AGGREGATING
cbind(names(DT.dsr))


setInfo(DT.dsr, "Detailed Sales report for labelid 18000 Pulled via PHP script.")

colsToPull <- c(store_name_IODA_DB="service_name", country_name="country", "year", "period", "statement_month", "original_currency_code", "exchange_rate", currency="preferred_currency_code")

## Actually ... drop the currency info
colsToPull %<>% .[. %ni% (c("original_currency_code", "exchange_rate"))]


## Drop activity month fields, if not including it
if (!include_activity_month)
  colsToPull %<>% .[. %ni% (c("year", "period"))]
## Drop service_name (store name) if not including it
if (!include_store)
  colsToPull %<>% .[. %ni% (c("service_name"))]

## These cols will be aggregated and given the new names
colsToAgg <- c(net_earnings="net_earnings", net_earnings_original_currency="net_earnings_oc", gross="total_sales", gross_original_currency="total_sales_oc")
colsToAgg <- c(net_earnings="net_earnings", gross="total_sales")

## Aggregate
DT.dsr_aggd <- aggregateDT(DT.dsr, by=colsToPull, colsToAgg=colsToAgg)

## setnames for colsToPull, since it is not done automatically
setnames(DT.dsr_aggd, colsToPull, colNamesFromVector(colsToPull))

## Clean USA
DT.dsr_aggd[country_name == "United States", country_name := "USA"]
DT.dsr_aggd[country_name == "United States Virgin Islands", country_name := "US Virgin Islands"]
DT.dsr_aggd[country_name == "Russian Federation", country_name := "Russia"]
DT.dsr_aggd[country_name == "Republic of Korea", country_name := "South Korea"]
DT.dsr_aggd[country_name == "Republic of Moldova", country_name := "Moldova"]
DT.dsr_aggd[country_name == "Viet Nam", country_name := "Vietnam"]
DT.dsr_aggd[country_name == "Macedonia (frm Yugoslavia)", country_name := "Macedonia"]
DT.dsr_aggd[country_name == "R\xe9union", country_name := "Réunion"]

## Add Accounting and Activity month
## Accounting
DT.dsr_aggd[, accounting_month := as.Date(paste0(statement_month, " 01"), format="%b %Y %d")]
## Activity :: Only addif flagged to do so
if (include_activity_month)
  DT.dsr_aggd[, activity_month := as.Date(paste0(year, "-", topropper(period), "-01"), format="%Y-%b-%d")]

setkeyIfNot(DT.dsr_aggd, c("accounting_month", if (include_activity_month) "activity_month"), organize=TRUE, verbose=FALSE)

suppressWarnings(DT.dsr_aggd[, c("year", "period", "statement_month") := NULL])

addColsFrom_(DT.dsr_aggd, DT.country, joinCols="country_name", colsToBring=c("country_code", "continent"))

## TODO: If more than one row exists, need to aggregate. For now, just assume only one row.
stopifnot(nrow(DT.dsr_aggd[is.na(country_code)]) == 1)


DT.dsr_aggd[is.na(country_code), country_code := "ROW"]


f.out <- writeDT(DT.dsr_aggd, base.file.name="ABC_gross_USD_from_detailed_sales_report")

reveal(f.out)

DT.dsr_aggd.by_month <- aggregateDT(DT.dsr_aggd, by=c("accounting_month", "currency"), colsToAgg=c("net_earnings", "gross"))
formnumb(DT.dsr_aggd.by_month, round=1)[]