setScience(proj="StoreMomentum", create=TRUE)

minDate <- "2013-06-01"

tbl.release <- "dim_release"
Q.release <- sprintf("select releaseid as upc, releasename, releasedate, sale_start_date, mkt_priority, labelid  from dim_release where not mkt_priority = '' and releasedate > '%s'", minDate)
(DT.releases <- runQry(Q.release))
DT.releases[, upc := as.idcol(upc)]

upc.list <- DT.releases[["upc"]]
upcs.flat <- pasteQ(upc.list)

# ---------------- #

tbl.spotify <- "staging_raw_spotify"
Q.spotify <- sprintf("SELECT customerid,  upc, download_datetime, country 
                      FROM %s
                      WHERE download_datetime > '%s'
                      AND UPC in %s", tbl.spotify, minDate, upcs.flat)

DT.spotify <- runQry(Q.spotify, emailStatus="Spotify Qry Complete")
DT.spotify[, upc := as.idcol(upc)]

# ---------------- #
tbl.itunes <- "staging_raw_itunes"
Q.itunes  <- sprintf("SELECT upc as upc, units, download_activity_date, customer_identifier as customerid, sale_return, preorder FROM %s WHERE download_activity_date > '%s' AND upc in %s", tbl.itunes, minDate, upcs.flat)
DT.itunes <- runQry(Q.itunes, emailStatus="iTunes Qry Complete")
DT.itunes[, upc := as.idcol(upc)]
# ---------------- #


tbl.amazon <- staging_raw_amazon
Q.amazon  <- sprintf("SELECT upc, isrc, units, cost, territory_code FROM %s WHERE download_activity_date > '%s' AND upc in %s", tbl.amazon, minDate, upcs.flat)
DT.amazon <- runQry(Q.amazon, emailStatus="iTunes Qry Complete")
DT.amazon[, upc := as.idcol(upc)]
# ---------------- #

EmailStatusUpdate("All Queries are Complete, proceeding to Save")
saveImageTo()
EmailStatusUpdate("Save Complete. Waiting for you.")

# ---------------- #


## SPOTIFY
 [1] "ingestion_time"    "filename"          "filesize"          "customerid"        "trackid"
 [6] "download_date"     "mobile"            "isrc"              "upc"               "artistname"
[11] "trackname"         "albumname"         "zip"               "product"           "country"
[16] "gender"            "birthyear"         "download_datetime" "original_upc"

                                                                           releasename releasedate   releaseid sale_start_date mkt_priority labelid
 1:                                                              Ultimate Gaz Reynolds  2010-11-08 8.88609e+11      2010-11-08                17292
 2:                                                            Dark Lady (Usa Version)  2007-05-01 8.88609e+11      2007-04-24                17292
 3:                          Sbi Gallery Series - The Eagles, Vol. 1 (Karaoke Version)  2014-01-21 8.88608e+11      2014-01-21                 8673
 4:                                                  Fuck the System (Special Edition)  2014-03-14 8.88609e+11      2014-03-14                10942
 5:                                                                              Tango  2004-02-05 8.88609e+11      2004-02-05                11783
 6:                                                                   Rotos y Quemados  2014-01-31 8.88609e+11      2014-01-31                17181
 7:             In My Mind (Originally Performed by Heather Headley) [Karaoke Version]  2014-02-08 8.88609e+11      2014-02-08                17441
 8: Nothin' but a Love Thang (Originally Performed by Darryl Worley) [Karaoke Version]  2014-02-08 8.88609e+11      2014-02-08                17441
 9:            Tell Me 'Bout It (Originally Performed by Joss Stone) [Karaoke Version]  2014-02-08 8.88609e+11      2014-02-08                17441
10:                                                                                S/T  2014-03-11 8.88609e+11      2014-03-11                18805

(StoreMomentum) Revo> qShowCols("dim_release")
 [1] "releaseid"                 "releasename"               "releasedate"               "genreid"                   "artistid"                  "vendor_catalog_number"
 [7] "imprint"                   "sale_start_date"           "date_added"                "original_release_date"     "deletions"                 "mkt_priority"
[13] "manufacturer_upc"          "version"                   "last_updated"              "date_created"              "labelid"                   "subaccountid"
[19] "display_upc"               "vendor_release_identifier" "product_type_id"           "catalogid"                 "imprintid"



(StoreMomentum) Revo> qShowCols("staging_raw_itunes")
 [1] "ingestion_time"           "filename"                 "filesize"                 "provider"                 "service_provider_country" "vendor_identifier"
 [7] "upc"                      "isrc"                     "artist"                   "title"                    "record_company"           "product_type_identifier"
[13] "units"                    "royalty_price"            "download_activity_date"   "order_id"                 "postal_code"              "customer_identifier"
[19] "download_date"            "sale_return"              "customer_currency"        "country_code"             "royalty_currency"         "preorder"
[25] "isan"                     "customer_price"           "apple_id"                 "cma"                      "asset"                    "vendor_offer_code"
[31] "grid"                     "promo_code"               "parent_id"                "season_pass"              "belongs_to_ioda"          "attributable_purchase"
[37] "primary_genre"            "parent_type_id"           "original_upc"
(StoreMomentum) Revo>

(StoreMomentum) Revo> qShowCols("staging_raw_amazon")

 [1] "ingestion_time"               "filename"                     "filesize"                     "download_date"                "territory_code"
 [6] "vendor_production_identifier" "asin"                         "upc"                          "album_track_flag"             "isrc"
[11] "artist"                       "product_title"                "label_name"                   "cost_currency"                "units"
[16] "cost"                         "original_upc"

upc, isrc, units, cost, territory_code