## itunes Zero.r

.us()
setScience(proj="iTunes Zero Issue", subl=FALSE, create=TRUE)
analytics()

isrcs <- c("USA371421074", "CAS269900007", "GBSUW0702259", "USA371041252", "USA560567608", "USA560568236", "USA560568220", "USA560568230", "USA560568221", "USA560568233", "USA560568222", "USA560791702", "USA560954419", "GBSUW0702255", "USA560913747", "USH3G0702039", "USA371349287", "USA371302162", "USA371251706", "USA371299931", "US28E0948201", "US28E1170601", "US28E1170602", "US28E0948202", "US28E0948203", "US28E1170603", "USPG21000002", "US29Q0510217", "US29Q0510216", "US29Q0510218", "US29Q0510215", "US29Q0510219", "US29Q0510214", "US29Q0510213", "US29Q0510220", "US29Q0510221", "INH100511007", "GBCKK8600806", "GBCKK1015614", "GBJVL0500012", "GBJVL0500010", "USA371439013", "USA370694871", "US36T0609201", "US36T0300606", "US36T0600216", "US36T0402211", "US36T0600209", "US36T0402208", "US36T0403306", "US36T0600212", "US36T0600204", "USA560684639", "USA560684648", "USBS50310283", "USBS50830839", "USBS50510433", "USASE0500026", "USASE0500045", "USASE0500005", "USASE0510168", "USBCS0700146", "USA560751773", "USA560751768", "USA560751767", "USA560751770", "USA370651086", "USA370651087", "US4GZ0701105", "US4GZ0701102", "US4GZ0701101", "MXF368829996", "MXF366510836", "MXF368628632", "MXF368830202", "MXF368629070", "MXF368629063", "MXF368729108", "MXF368629061", "MXF369332612", "MXF369231784", "MXF369231968", "MXF369231844", "MXF369231772", "MXF369231787", "MXF369231770", "MXF369231769", "MXF369231785", "MXF369231969", "MXF367623291", "MXF399200936", "MXF368427939", "MXF360637349", "MXF360034919", "MXF369231721", "MXF369332769", "MXF360637358", "MXF369834243", "MXF367925061", "MXF369131356", "MXF360637355", "MXF360034913", "MXF368427944", "MXF360034920", "MXF360235881", "MXF369433089", "MXF360034915", "MXF367824679", "MXF360637505", "MXF369332431", "MXF369232239", "MXF369232238", "MXF369332433", "US2BR9700001", "US2BR9700006", "US2BR9700044", "US2BR9700052", "MXF399802213", "MXF360135258", "MXF369834352", "MXF369834349", "MXF360034977", "MXF360135218", "MXF369834466", "MXF360035211", "MXF369934730", "MXF369834344", "MXF369934736", "MXF369231983", "MXF368930655", "USJMZ0800027", "GBBRL9610030", "USA370662656", "USA370527243", "GBCLQ0601039", "HKOA10616988", "DED830368402", "DED830703627", "DED830710205", "DED830703330", "DED830703548", "DED830703546", "DED830703554", "DED830703547", "DED830703549", "DED830703550", "DED830703552", "DED830703553", "DED830703551", "DED830703374", "DED830482105", "DED830482101", "DED830797905", "DED830800082", "DED830691805", "GBQRF0707992", "GBQRF0712696", "USA370669740", "USAMK0610022", "USR3R0700073", "US2R90701002", "USA370538984", "USA370538986", "USA370538542", "USA370538438", "USA370538604", "USA370538807", "USA370538803", "PHU010002246", "PHU010204430", "USVJ10401101", "INV119800029", "USA370544298")


metaNames <- c("artist", "release", "label", "store", "track")
selfname_(metaNames)
qShowCols(metaNames)

Qry.Sub <- sprintf("SELECT artistid, accountingperiodid, activityperiodid, labelid, storeid, releaseid, trackid, isrcid, sum(gross) AS Revenue, sum(sales) as Units \n  FROM FACT_SALES\n    WHERE isrcid in (SELECT isrcid FROM dim_isrc WHERE isrc in %s)\n    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8 \n     ORDER BY 9 DESC", pasteQ(isrcs))
Qry.Joins <- pasteC(sprintf("LEFT JOIN dim_%s ON F.%1$sid = dim_%1$s.%1$sid", metaNames), C="\n")
Qry <- sprintf(paste0("SELECT ", pasteC(sprintf("%sname", metaNames), C=", "), ", F.* \n FROM (\n  %s\n )\n F \n %s"), Qry.Sub, Qry.Joins)

cat(Qry)

Qres <- runQry(Qry)

Revenue_by_ISRC <- Qres
jesusForData(Revenue_by_ISRC)
saveImageTo()