def run():
for yr in range(2018, date.today().year + 1):
for qtr in range(1, 5):
sql = "SELECT artist.artistid, artist.artistname as Artist, count(log.logid) as Plays, sum(albumlengths.albumlength) as Time, Totals.TotalPlays as TotalPlays, Totals.TotalTime "
sql += "FROM log INNER JOIN albumlengths on log.albumid = albumlengths.albumid "
sql += "INNER JOIN albumartist on albumlengths.albumid = albumartist.albumid "
sql += "INNER JOIN artist on artist.artistid = albumartist.artistid "
sql += "INNER JOIN albumview as album on album.albumid = albumartist.albumid "
sql += "JOIN (SELECT COUNT(log.logid) as TotalPlays, SUM(albumlengths.albumlength) as TotalTime FROM log inner join albumlengths on log.albumid = albumlengths.albumid) Totals "
sql += "WHERE YEAR(log.logdate) = " + str(
yr) + " and MONTH(log.logdate) IN (" + monthstring[
qtr] + ") and album.albumtypeid <> 16 GROUP BY Artist;"
chart = pd.read_sql(sql, common.conn)
chart['TimeScore'] = (chart['Time'] / chart['TotalTime']) * 100
chart['FreqScore'] = (chart['Plays'] / chart['TotalPlays']) * 100
chart['WeightedScore'] = (chart['TimeScore'] *
0.5) + (chart['FreqScore'] * 0.5)
chart.sort_values('WeightedScore', ascending=False, inplace=True)
chart['Rank'] = chart['WeightedScore'].rank(ascending=False)
chart_formatted = chart[[
'Rank', 'artistid', 'Artist', 'TimeScore', 'FreqScore',
'WeightedScore'
]][:30]
chart_array = chart_formatted.values.tolist()
base_filename = "Artist Chart - {} Q{}.txt".format(yr, qtr)
if len(chart_array) > 1:
basedir = os.path.join(common.basedir, 'Seasonal', 'Artist')
if not os.path.exists(basedir):
os.mkdir(basedir)
with open(os.path.join(basedir, base_filename),
'w',
encoding='utf-8') as outfile:
header = "{:<5}{:<80}{:>10}{:>10}{:>10}\n".format(
"RANK", "", "TIME", "FREQ", "TOTAL")
outfile.write(seperator + header + seperator)
for a in chart_array:
rank, artistid, artist, timescore, freqscore, weightedscore = a
common.add_chart_history(yr, qtr, artistid, 0, rank,
weightedscore, 0)
textline = "{:<5}{:<80}{:>10.2f}{:>10.2f}{:>10.2f}\n".format(
int(rank),
common.shorten_by_word(artist.upper(), 80),
timescore, freqscore, weightedscore)
outfile.write(textline)
outfile.write(seperator)
def run():
for yr in range(2018, date.today().year + 1):
sql = "SELECT album.artistcredit as Artist, album.albumid as albumid, albumlengths.album as Album, count(log.logid) as Plays, sum(albumlengths.albumlength) as Time, Totals.TotalPlays, Totals.TotalTime "
sql += "FROM log INNER JOIN albumlengths on log.albumid = albumlengths.albumid "
sql += "INNER JOIN albumview as album on albumlengths.albumid = album.albumid "
sql += "JOIN (SELECT YEAR(log.logdate) as Y, COUNT(log.logid) as TotalPlays, SUM(albumlengths.albumlength) as TotalTime FROM log inner join albumlengths on log.albumid = albumlengths.albumid) Totals "
sql += "WHERE album.yearreleased = " + str(
yr) + " and album.albumtypeid <> 16 GROUP BY Artist, Album;"
chart = pd.read_sql(sql, common.conn)
chart['TimeScore'] = (chart['Time'] / chart['TotalTime']) * 100
chart['FreqScore'] = (chart['Plays'] / chart['TotalPlays']) * 100
chart['WeightedScore'] = (chart['TimeScore'] *
0.5) + (chart['FreqScore'] * 0.5)
chart.sort_values('WeightedScore', ascending=False, inplace=True)
chart['Rank'] = chart['WeightedScore'].rank(ascending=False)
chart_formatted = chart[[
'Rank', 'albumid', 'Artist', 'Album', 'TimeScore', 'FreqScore',
'WeightedScore'
]][:50]
chart_array = chart_formatted.values.tolist()
base_filename = "Album Chart (This Year's Releases) - {}.txt".format(
yr)
full_dir = os.path.join(common.basedir, 'Annual', str(yr))
if not os.path.exists(full_dir):
os.makedirs(full_dir)
with open(os.path.join(full_dir, base_filename), 'w',
encoding='utf-8') as outfile:
header = "{:<5}{:<80}{:>10}{:>10}{:>10}\n".format(
"RANK", "", "TIME", "FREQ", "TOTAL")
outfile.write(seperator + header + seperator)
for a in chart_array:
rank, albumid, artist, album, timescore, freqscore, weightedscore = a
textline = "{:<5}{:<80}{:>10.2f}{:>10.2f}{:>10.2f}\n".format(
int(rank),
common.shorten_by_word(artist.upper() + ": " + album, 80),
timescore, freqscore, weightedscore)
common.add_chart_history(yr, 0, 0, albumid, rank,
weightedscore, 1)
outfile.write(textline)
outfile.write(seperator)
def run():
sql = "SELECT artist.artistid, artist.artistname as Artist, count(log.logid) as Plays, sum(albumlengths.albumlength) as Time, Totals.TotalPlays, Totals.TotalTime "
sql += "FROM log INNER JOIN albumartist ON log.albumid = albumartist.albumid "
sql += "INNER JOIN albumlengths on albumartist.albumid = albumlengths.albumid "
sql += "INNER JOIN albumview as album on albumlengths.albumid = album.albumid "
sql += "INNER JOIN artist on albumartist.artistid = artist.artistid "
sql += "JOIN (SELECT COUNT(log.logid) as TotalPlays, SUM(albumlengths.albumlength) as TotalTime FROM log inner join albumlengths on log.albumid = albumlengths.albumid) Totals "
sql += "WHERE album.albumtypeid <> 16 GROUP BY Artist;"
chart = pd.read_sql(sql, common.conn)
chart['TimeScore'] = chart['Time'] / chart['TotalTime'] * 100
chart['FreqScore'] = chart['Plays'] / chart['TotalPlays'] * 100
chart['WeightedScore'] = (chart['TimeScore'] * 0.5) + (chart['FreqScore'] *
0.5)
chart.sort_values('WeightedScore', ascending=False, inplace=True)
chart['Rank'] = chart['WeightedScore'].rank(ascending=False)
chart_formatted = chart[[
'Rank', 'artistid', 'Artist', 'TimeScore', 'FreqScore', 'WeightedScore'
]][:100]
chart_array = chart_formatted.values.tolist()
base_filename = "Artist Chart (All Time).txt"
with open(os.path.join(common.basedir, 'All Time', base_filename),
'w',
encoding='utf-8') as outfile:
header = "{:<5}{:<80}{:>10}{:>10}{:>10}\n".format(
"RANK", "", "TIME", "FREQ", "TOTAL")
outfile.write(seperator + header + seperator)
for a in chart_array:
rank, artistid, artist, timescore, freqscore, weightedscore = a
textline = "{:<5}{:<80}{:>10.2f}{:>10.2f}{:>10.2f}\n".format(
int(rank), common.shorten_by_word(artist.upper(), 80),
timescore, freqscore, weightedscore)
common.add_chart_history(0, 0, artistid, 0, rank, weightedscore, 0)
outfile.write(textline)
outfile.write(seperator)