setScience(proj="Currency_Exchange_Rates", subProj="ConstantCurrency")
setGitBranchToSystem(); .g()

wh <- "LOOKER_WH_LARGE"
dbname <- "prod"
setSnowflake(wh=wh, dbname=dbname)

tbl <- "accounting"
schema <- "bi"

colsToPull <- c(
  "ACCOUNTING_MONTH", 
  "ACTIVITY_MONTH", 
  "COUNTRY_CODE", 
  "STOREID", 
  "STORE_NAME", 
  "STORE_USES_ROYALTYDOLLAR", 
  "LABELID", 
  "LABEL_COUNTRY_CODE", 
  "STREAM_VS_DOWNLOAD", 
  "TRANSAC_TYPE_ABBR"
)

currencyCols <- c(
  "ORIGINAL_CURRENCY_ID", 
  "ORIGINAL_CURRENCY_CODE", 
  "PAYOUT_CURRENCY_ID",
  "PAYOUT_CURRENCY_CODE"
)

ratesCols <- c(
  "CURRENCY_EXCHANGE_RATE_AT_INBOUND", 
  "CURRENCY_EXCHANGE_RATE_AT_PAYOUT", 
  "UNKNOWN_FX_SPREAD_USD"
)

colsToAgg <- c(
  "ADJUSTED_GROSS_CCUR",
  "ADJUSTED_GROSS_REVENUE_USD",
  "CLIENT_ACTUAL_NET_CCUR",
  "CLIENT_ACTUAL_NET_USD",
  "CLIENT_NET_RECEIPT_CCUR",
  "CLIENT_NET_RECEIPT_USD",
  "GROSS_REVENUE_CCUR",
  "GROSS_REVENUE_USD",
  "UNITS"
)


minDate <- as.Date("2015-05-01")
dateCol <- "accounting_month"
whereClause.currency <- "original_currency_id != payout_currency_id and payout_currency_id != 170 and original_currency_id != 170"
qry.no_agg <- makeQry(tbl=tbl, schema=schema, colsToPull=c(colsToPull, currencyCols, ratesCols), colsWithaggFunc=colsToAgg, minDate=minDate, dateCol=dateCol, where=whereClause.currency, limit=6000)
DT.no_agg <- sfQry(qry.no_agg)

qry.agg <- makeQry(tbl=tbl, schema=schema, colsToPull=c(colsToPull, currencyCols, ratesCols), colsToAgg=colsToAgg, minDate=minDate, dateCol=dateCol, where=whereClause.currency, limit=6000)
DT.agg <- sfQry(qry.agg)


qry.conversion_rates <- 
  makeQry(tbl=tbl, schema=schema, distinct=TRUE, colsToPull=c(currencyCols, ratesCols), colsWithaggFunc=colsToAgg, minDate=minDate, dateCol=dateCol, where=whereClause.currency, limit=6000)


setWidth()
DT.no_agg