VAsynonyms <- c(
    "Varios Artistas"                
  , "Various Artist"                 
  , "Artisti Vari"                   
  , "VARIOUS  ARTIST"                
  , "Various Artistes"               
  , "Varous Artists"                 
  , "Varioius Artists"               
  , "VARIOUS ARTISTS"                
  , "Variuos Artists"                
  , "Various Artistis"               
  , "varies artist"                  
  , "Vartious Artists"               
  , "various artists"                
  , ".Various Artists"               
  , "Various artists"                
  , "Vari Artisti"                   
  , "Various  Artists"               
  , "Varius Artists"                 
  , "Varios Artists"                 
  , "Various artist"                 
  , "Various Artistas"               
  , "Various Artists ."              
  , "Artistas Varios"                
  , "Varios Artista"                 
  , "Artistas Variados"              
  , "Varias Artists"                 
  , "Variours Artists"               
  , "Varioust Artists"               
  , "Varius Artist"                  
  , "Various Artists, Various Artist"
  , "Varioust Artist"                
  , "Various Artist's"               
  , "Les artistes varies"            
  , "varius Artist"                  
  , "Varies Artist"                  
  , "Varioues Artist"
  , "Varios"
  , "Various"
  , "Various Musique"            
)



## COLUMNS
c("artistId", "statement_detail_id", "accountingPeriodId", "activityPeriodId", 
"labelId", "storeId", "genreId", "releaseId", "trackId", "countryId", 
"transactionTypeId", "catalogId", "isrcId", "imprintId", "gross", 
"sales", "actual_net", "adjusted_gross", "distribution_fees", 
"dpd_publishing", "net_receipt", "oms_fees", "partner_share", 
"ringtone_publishing", "retail_price", "subaccountId", "dateCreated", 
"original_currency_id", "fx_spread_fee", "payout_currency_id", 
"activity_fx_rate", "fx_adjusted_exchange_rate", "fx_gross", 
"fx_actual_net", "fx_adjusted_gross", "fx_distribution_fees", 
"fx_dpd_publishing", "fx_net_receipt", "fx_oms_fees", "fx_ringtone_publishing")




source("~/git/misc/rscripts/utilsRS.r")
utilSource()

library(data.table)
library(RMySQL)

infDB <- "10.156.228.102"
minDate <- "2013-08-20"
tbl <- "fact_sales"
aidtbl <- "dim_artist"
limit <- FALSE
dropVarArist <- TRUE

con   <- getDB()
# qShowCols(tbl, con)
# headDB(tbl)

colsUsing <- c("artistName", "statement_detail_id", "isrcId", "imprintId", "gross", "sales", "dateCreated")

Qry <- 	paste(  "\n SELECT "
          ,    pasteC(colsUsing, C=", ")
          , "\n FROM"
          ,    tbl
          , "\n LEFT JOIN "
          ,    aidtbl
          , "\n ON  dim_artist.artistId = fact_sales.artistID"
          , "\n WHERE dateCreated >= "
          ,    pasteQ(minDate)
          )

# Lots of info to V/A
if (dropVarArist)
	Qry <- paste(Qry, "\n AND NOT (artistName in ", pasteQ(VAsynonyms), ")")

if (limit)
	Qry <- paste(Qry, "\n LIMIT 10")

## Run the Query
s.t(Sales <- runQry(Qry))

## Conver to DT
Sales <- data.table(Sales, key=artistName)

# rename the artistId column if present
if ("artistId" %in% names(Sales))
	 setnames(Sales, "artistId", "artistID")

s.t(Sales[, dateCreated := as.POSIXct(as.character(dateCreated), tz="EST")])

> c4(Sales)
artistName statement_detail_id   isrcId imprintId    gross sales         dateCreated
1:     Prince Nico Mbarga & Rocafil Jazz Intl. Band          1324590541 10631640     18455 0.001045    23 2013-08-28 16:48:01
2:     Prince Nico Mbarga & Rocafil Jazz Intl. Band          1323759592 10908922     18455 2.661170   500 2013-08-28 16:48:01
3:     Prince Nico Mbarga & Rocafil Jazz Intl. Band          1323759593 10908922     18455 0.051901     7 2013-08-28 16:48:01
4:     Prince Nico Mbarga & Rocafil Jazz Intl. Band          1323759594 10908922     18455 0.023178     3 2013-08-28 16:48:01
5:     Prince Nico Mbarga & Rocafil Jazz Intl. Band          1323759595 10908922     18455 0.045694    21 2013-08-28 16:48:01
---                                                                                                                           
  54500212:                          Ｔhee 50's Ｈigh Ｔeens          1293879568  6547147     29834 0.009910     1 2013-08-28 16:48:01
54500213:                          Ｔhee 50's Ｈigh Ｔeens          1308830471  9544631     29834 0.532440     1 2013-08-28 16:48:01
54500214:                                           Ｖ．Ａ          1327815396  3046774      2956 0.002506     3 2013-08-28 16:48:01
54500215:                                           Ｖ．Ａ          1309693389  3046778      2956 0.898900     1 2013-08-28 16:48:01
54500216:                                           Ｖ．Ａ          1295790822  3046778      2956 0.009016     1 2013-08-28 16:48:01



Rows <- nrow(Sales)

## Dropping Various Artist, just for now
Sales

# Sales <- sampleChunks(Sales, perc=.005, NumberOfChunks=3000)

   artistId statement_detail_id  isrcId imprintId  gross sales dateCreated
1:   285132            63133184 6518729     42598 713961 21090           1