def albums_by_length():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format(
"by Length", "Count", "%", "Plays", "%", "Played", "%", "Ratio"))
f.write("-" * 105 + "\n")
sql = "SELECT ((albumlength/60) DIV 15) * 15 as LengthGroup, COUNT(albumlengths.albumid) as TypeCount, " \
"SUM(album.playcount) as PlayCount, sum(album.played) as Played " \
"FROM albumlengths INNER JOIN albumview as album on albumlengths.albumid = album.albumid " \
"where albumlength < 120*60 and album.albumtypeid<>16 GROUP BY LengthGroup ORDER BY LengthGroup;"
results = common.get_results(sql)
for r in results:
y = int(r[0])
count = int(r[1])
percent = 0 if albumcount == 0 else (count / albumcount) * 100
playcount = int(r[2])
logpercent = 0 if logcount == 0 else (playcount / logcount) * 100
played = int(r[3])
playedpercent = (played / count) * 100
ratio = 0 if played == 0 else playcount / played
tstring = "{} - {} min".format(y, y + 14)
line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format(
tstring, count, percent, playcount, logpercent, played,
playedpercent, ratio)
f.write(line)
if (y + 15) % 60 == 0:
f.write("-" * 105 + "\n")
sql = "SELECT ((albumlength/60) DIV 15) * 15 as LengthGroup, COUNT(albumlengths.albumid) as TypeCount, " \
"SUM(album.playcount) as PlayCount, sum(album.played) as Played " \
"FROM albumlengths INNER JOIN album on albumlengths.albumid = album.albumid " \
"where albumlength >= 120*60 and album.albumtypeid<>16;"
results = common.get_results(sql)
for r in results:
y = int(r[0])
count = int(r[1])
percent = 0 if albumcount == 0 else (count / albumcount) * 100
playcount = int(r[2])
logpercent = 0 if logcount == 0 else (playcount / logcount) * 100
played = int(r[3])
playedpercent = (played / count) * 100
ratio = 0 if played == 0 else playcount / played
tstring = "{} - {} min".format(y, y + 14)
line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format(
"120+ min", count, percent, playcount, logpercent, played,
playedpercent, ratio)
f.write(line)
f.write("\n\n")
def albums_by_type():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format(
"by Type", "Count", "%", "Plays", "%", "Played", "%", "Ratio"))
f.write("-" * 105 + "\n")
sql = "SELECT albumtype, COUNT(albumid) as TypeCount, SUM(playcount) as PlayCount, SUM(played) as Played FROM albumview as album inner join albumtype on " \
"album.albumtypeid = albumtype.albumtypeid where album.albumtypeid<>16 GROUP BY albumtype ORDER BY TypeCount desc;"
results = common.get_results(sql)
for r in results:
albumtype = r[0]
count = int(r[1])
percent = 0 if albumcount == 0 else (count / albumcount) * 100
playcount = int(r[2])
logpercent = 0 if logcount == 0 else (playcount / logcount) * 100
played = int(r[3])
playedpercent = 0 if count == 0 else (played / count) * 100
ratio = 0 if played == 0 else playcount / played
line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format(
albumtype, count, percent, playcount, logpercent, played,
playedpercent, ratio)
f.write(line)
f.write("\n\n")
def top_ten_artists_log():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format(
"by Plays", "Count", "%", "Plays", "%", "Played", "%", "Ratio"))
f.write("-" * 105 + "\n")
sql = "select artist.ArtistName, count(album.AlbumID) as Albums, sum(album.PlayCount) as Plays, sum(album.played) as Played " \
"from artist inner join albumartist on artist.artistid = albumartist.artistid " \
"inner join album on album.albumid = albumartist.albumid " \
"where album.albumtypeid<>16 group by artist.ArtistName order by Plays desc limit 10;"
results = common.get_results(sql)
for r in results:
artistname = common.shorten_by_word(r[0], 25)
count = int(r[1])
percent = 0 if albumcount == 0 else (count / albumcount) * 100
playcount = int(r[2])
logpercent = 0 if logcount == 0 else (playcount / logcount) * 100
played = int(r[3])
playedpercent = 0 if count == 0 else (played / count) * 100
ratio = 0 if played == 0 else playcount / played
line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format(
artistname.upper(), count, percent, playcount, logpercent, played,
playedpercent, ratio)
f.write(line)
f.write("\n\n")
def top_ten_albums_last_28days():
global albumcount
global logcount
f.write("{:<80}{:>10}{:>10}\n".format("Last 28 Days", "Plays", "Hours"))
f.write("-" * 105 + "\n")
sql = "select `artist`.`ArtistName` AS `ArtistName`,`album`.`Album` AS `Album`," \
"sum(`albumlengths`.`albumlength`) AS `Points`,count(`log`.`logID`) AS `Plays` " \
"from ((((`artist` join albumartist on artist.artistid = albumartist.artistid inner join `album` " \
"on((`albumartist`.`albumID` = `album`.`albumID`))) " \
"join `albumlengths` on((`album`.`AlbumID` = `albumlengths`.`albumid`))) " \
"join `log` on((`log`.`AlbumID` = `album`.`AlbumID`))) " \
"join `albumtype` on((`album`.`AlbumTypeID` = `albumtype`.`AlbumTypeID`))) " \
"where log.logDate > (DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY)) and album.albumtypeid<>16 " \
"group by `artist`.`ArtistID`,`album`.`AlbumID`,`artist`.`ArtistName`,`album`.`Album` " \
"order by sum(`albumlengths`.`albumlength`) desc limit 10"
results = common.get_results(sql)
for r in results:
artistname = common.shorten_by_word(r[0], 35)
title = common.shorten_by_word(r[1], 35)
playcount = int(r[3])
playedtime = r[2] / 3600
line = "{:<40}{:<40}{:>10}{:>10.2f}\n".format(artistname.upper(),
title, playcount,
playedtime)
f.write(line)
f.write("\n\n")
def weekly_stats():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format(
"by Week", "Count", "Hrs", "Avg. Len (min)", "Missing", "Total"))
f.write("-" * 105 + "\n")
sql = "select * FROM (SELECT * from listen_perweek where (Y>=2018) OR (Y = YEAR(CURRENT_DATE) AND W<>WEEK(CURRENT_DATE)+1) ORDER BY Y DESC, W DESC LIMIT 26) rr ORDER BY Y,W ASC;"
results = common.get_results(sql)
for r in results:
y = str(r[0]) + "-" + str(r[1]).zfill(2)
missing = missing_logs_week(r[0], r[1])
count = int(r[3])
logtime = format_to_HM(r[2])
avg = format_to_MS(((r[2] * 60) / count))
total = count + missing
line = "{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format(
y, count, logtime, avg, missing, total)
f.write(line)
if r[1] % 13 == 0:
f.write("-" * 105 + "\n")
f.write("\n\n")
def albums_by_year():
global albumcount
global logcount
global albumsplayed
f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format(
"by Year of Release", "Count", " ", "Plays", " ", "Played", "%",
"Ratio"))
f.write("-" * 105 + "\n")
sql = "SELECT yearreleased, COUNT(albumid) as TypeCount, SUM(playcount) as PlayCount, Sum(played) as Played FROM albumview as album " \
"WHERE yearreleased >= 2018 and album.albumtypeid<>16 GROUP BY yearreleased ORDER BY yearreleased;"
results = common.get_results(sql)
for r in results:
y = int(r[0])
count = int(r[1])
percent = 0 if albumcount == 0 else (count / albumcount) * 100
playcount = int(r[2])
logpercent = 0 if logcount == 0 else (playcount / logcount) * 100
played = int(r[3])
playedpercent = (played / count) * 100
ratio = 0 if played == 0 else playcount / played
if y % 10 == 0:
f.write("-" * 105 + "\n")
line = "{:<30}{:>10}{:>21}{:>21}{:>10.2f}%{:>10.2f}\n".format(
y, count, playcount, played, playedpercent, ratio)
f.write(line)
f.write("\n\n")
def thisweek_stats():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format(
"This Week", "Count", "Hrs", "Avg. Len (min)", "Missing", "Total"))
f.write("-" * 105 + "\n")
sql = "select `log`.`logDate`, sum(`albumlengths`.`albumlength`) / 3600 AS `time`, count(`log`.`logID`) AS `logcount` " \
"from `log` inner join `albumlengths` on `log`.`AlbumID` = `albumlengths`.`albumid` inner join album on albumlengths.albumid = album.albumid " \
"where (year(logDate) = year(CURRENT_DATE)) and (week(logDate) = week(CURRENT_DATE)) group by logDate order by logDate"
results = common.get_results(sql)
for r in results:
logdate = r[0].strftime("%Y-%m-%d")
missing = missing_logs_date(logdate)
count = int(r[2])
logtime = format_to_HM(r[1])
avg = format_to_MS(((r[1] * 60) / count))
total = count + missing
line = "{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format(
logdate, count, logtime, avg, missing, total)
f.write(line)
if r[1] % 13 == 0:
f.write("-" * 105 + "\n")
f.write("\n\n")
def albums_by_decade():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>11}{:>10}{:>11}{:>10}{:>11}{:>10}\n".format(
"by Decade", "Count", "%", "Plays", "%", "Played", "%", "Ratio"))
f.write("-" * 105 + "\n")
sql = "SELECT (yearreleased DIV 10) * 10 as Decade, COUNT(albumid) as TypeCount, SUM(playcount) as PlayCount, sum(played) as Played FROM albumview as album " \
"where album.albumtypeid<>16 GROUP BY Decade ORDER BY Decade;"
results = common.get_results(sql)
for r in results:
y = int(r[0])
count = int(r[1])
percent = 0 if albumcount == 0 else (count / albumcount) * 100
playcount = int(r[2])
logpercent = 0 if logcount == 0 else (playcount / logcount) * 100
played = int(r[3])
playedpercent = 0 if count == 0 else (played / count) * 100
ratio = 0 if played == 0 else playcount / played
line = "{:<30}{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10}{:>10.2f}%{:>10.2f}\n".format(
y, count, percent, playcount, logpercent, played, playedpercent,
ratio)
f.write(line)
f.write("\n\n")
def annual(outfile):
sql = "SELECT DISTINCT artist.artistid, artist.artistname, artist.sortname, album.albumid, album.album " \
"FROM chart_history inner join albumartist on chart_history.albumid = albumartist.albumid " \
"INNER JOIN album on albumartist.albumid = album.albumid " \
"INNER JOIN artist on albumartist.artistid = artist.artistid " \
"where Y <> 0 AND Q = 0 and tyr = 0 " \
"order by artist.sortname, album.album;"
artist_list = common.get_results(sql)
years = range(2018, date.today().year + 1)
yrstring = " ".join(str(y) for y in years)
last_initial = ""
outfile.write("{:<60}{}\n".format("", yrstring))
for a in artist_list:
artistid, artist, sortname, albumid, album = a
initial = sortname[0].upper()
yranks = []
yrankstr = ""
for y in years:
yrank = get_y_rank(albumid, y)
if yrank is None:
yrankstr += "{:>7}".format("-")
else:
yrankstr += "{:>7}".format(yrank)
linestr = "{:<63}{}".format((artist.upper() + ": " + album)[:60],
yrankstr)
if initial != last_initial:
outfile.write("-" * len(linestr) + "\n")
last_initial = initial
outfile.write(linestr + "\n")
def seasonal(outfile):
sql = "SELECT DISTINCT artist.artistid, artist.artistname, artist.sortname " \
"FROM chart_history inner join artist on chart_history.artistid = artist.artistid " \
"where Y <> 0 AND Q <> 0 AND albumid = 0 " \
"order by artist.sortname;"
artist_list = common.get_results(sql)
years = range(2018, date.today().year + 1)
quarters = range(1, 5)
yrstring = ""
for y in years:
for q in quarters:
yrstring += " " + "{}Q{}".format(y, q)
last_initial = ""
outfile.write("{:<60}{}\n".format("", yrstring))
for a in artist_list:
artistid, artist, sortname = a
initial = sortname[0]
yranks = []
yrankstr = ""
for y in years:
for q in quarters:
yrank = get_q_rank(artistid, y, q)
if yrank is None:
yrankstr += "{:>9}".format("-")
else:
yrankstr += "{:>9}".format(yrank)
linestr = "{:<68}{}".format(artist.upper(), yrankstr)
if initial != last_initial:
outfile.write("-" * len(linestr) + "\n")
last_initial = initial
outfile.write(linestr + "\n")
def top_ten_artists_28days():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>11}\n".format("Last 28 Days", "Plays", "Hours"))
f.write("-" * 80 + "\n")
sql = "select artist.ArtistName, sum(albumlengths.albumlength)/3600 as hours, count(log.logID) as logged " \
"from log inner join albumlengths on log.AlbumID = albumlengths.AlbumID " \
"inner join album on album.albumid = albumlengths.albumid " \
"inner join albumartist on album.albumid = albumartist.albumid " \
"inner join artist on albumartist.artistid = artist.ArtistID " \
"where log.logDate > (DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY)) and album.albumtypeid<>16 " \
"group by artist.ArtistName order by hours desc limit 10;"
results = common.get_results(sql)
for r in results:
artistname = common.shorten_by_word(r[0], 25)
count = int(r[2])
time = r[1]
line = "{:<30}{:>10}{:>11.2f}\n".format(artistname.upper(), count,
time)
f.write(line)
f.write("\n\n")
def annual_stats():
global albumcount
global logcount
f.write("{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format(
"Annual Stats", "Count", "Hrs", "Avg. Len (min)", "Missing", "Total"))
f.write("-" * 105 + "\n")
sql = "select year(`log`.`logDate`) as Y, sum(`albumlengths`.`albumlength`) / 3600 AS `time`, count(`log`.`logID`) AS `logcount` " \
"from `log` inner join `albumlengths` on `log`.`AlbumID` = `albumlengths`.`albumid` inner join album on albumlengths.albumid = album.albumid " \
"where year(log.logdate) >= 2017 group by Y order by Y"
results = common.get_results(sql)
for r in results:
y = r[0]
missing = missing_logs_year(y)
count = int(r[2])
logtime = format_to_HM(r[1])
avg = format_to_MS(((r[1] * 60) / count))
total = count + missing
line = "{:<30}{:>10}{:>10}{:>20}{:>11}{:>11}\n".format(
y, count, logtime, avg, missing, total)
f.write(line)
if r[1] % 13 == 0:
f.write("-" * 105 + "\n")
f.write("\n\n")
def get_y_rank(artistid, yr):
sql = "SELECT rank FROM chart_history WHERE artistid = {} AND y = {} AND Q = 0 AND albumid = 0;".format(
artistid, yr)
results = common.get_results(sql)
if len(results) == 0:
return None
else:
return results[0][0]
def get_last_run(albumid):
results = common.get_results(
"SELECT MAX(chartrun) FROM chart_history_rolling WHERE albumid={};".
format(albumid))
if results[0][0] is None:
return 0
else:
return results[0][0]
def get_albums():
sql = "SELECT album.albumid, artistname, album " \
"FROM albumview as album INNER JOIN albumartist on album.albumid = albumartist.albumid " \
"INNER JOIN artist on albumartist.artistid = artist.artistid " \
"WHERE sourceid=4 and albumtypeid<>7 " \
"and recordedtocassette is null " \
"order by SortName, yearreleased, album;"
return common.get_results(sql)
def get_total_plays(y):
if y > 0:
sql = "SELECT SUM(playcount) FROM album where SourceID<>6 and yearreleased={};".format(
y)
else:
sql = "SELECT SUM(playcount) FROM album where SourceID<>6;"
results = common.get_results(sql)
return results[0][0]
def get_last_rank(artistid, chart_date):
last_week = chart_date - timedelta(days=7)
results = common.get_results("SELECT rank FROM chart_history_rolling "
"WHERE artistid={} and chartdate='{}';".format(artistid, last_week.strftime("%Y-%m-%d")))
if results is None or len(results) == 0:
return None
else:
return results[0][0]
def get_data(query, condition=None):
sql = "SELECT ArtistName, Album, Points, Plays from {} ".format(query)
if condition is not None:
sql += "WHERE {} ".format(condition)
sql += "LIMIT 100;".format(query.lower())
return common.get_results(sql)
def get_media_count(physical):
if physical:
criteria = "<= 3"
else:
criteria = "BETWEEN 4 and 5"
sql = "SELECT COUNT(albumid) as AlbumCount, sum(played) as Played FROM albumview as album where SourceID {};".format(
criteria)
results = common.get_results(sql)
return results[0][0], results[0][1]
def albums_played_multi_times_in_single_day():
sql = "select distinct artistcredit, album from multiple_plays_per_day;"
results = common.get_results(sql)
last_log_date = None
for r in results:
artistname = common.shorten_by_word(r[0], 35)
album = common.shorten_by_word(r[1], 35)
line = "{:<40}{:<40}\n".format(artistname.upper(), album)
f.write(line)
f.write("\n\n")
def get_total_time(y):
if y > 0:
sql = "SELECT SUM(tracklength) FROM tracklengths INNER JOIN album on album.albumid = tracklengths.albumid " \
"INNER JOIN log on log.albumid = album.albumid " \
"where bonustrack = 0 and yearreleased={};".format(y)
else:
sql = "SELECT SUM(tracklength) FROM tracklengths INNER JOIN album on album.albumid = tracklengths.albumid " \
"INNER JOIN log on log.albumid = album.albumid " \
"where bonustrack = 0;"
results = common.get_results(sql)
return results[0][0]
def run():
conn = common.conn
thresh_date = (date.today() - timedelta(weeks=13)).strftime("%Y-%m-%d")
sql = "Select * from (select 'Added' as Status, cast(album.dateadded as date) as `Date`, album.artistcredit as Artist, " \
"album.album as Title from albumview as album " \
"UNION " \
"select 'Played' as status, min(log.logdate) as `Date`, album.artistcredit as Artist, album.album as title " \
"from log inner join albumview as album on album.albumid = log.albumid " \
"group by log.albumid) x " \
f"where `Date` >= '{thresh_date}' " \
"order by `Date`, Status, Artist, Title;"
results = common.get_results(sql)
outfile = os.path.join(common.basedir, "Growth Report.txt")
out = io.open(outfile, "w", encoding='utf-8')
last_date = date(1990, 1, 1)
progress = 0
positive = 0
neutral = 0
negative = 0
overall = 0
for r in results:
status, dt, artist, album = r
if dt > last_date:
dts = dt.strftime("%Y-%b-%d")
if last_date != date(1990, 1, 1):
out.write(f"\n\tProgress: {progress}\n")
out.write(f"\n{dts}\n\n")
last_date = dt
positive += 1 if progress > 0 else 0
neutral += 1 if progress == 0 else 0
negative += 1 if progress < 0 else 0
overall += progress
progress = 0
out.write(f"\t{status.upper()}\t{artist}: {album}\n")
progress += 1 if status == 'Played' else -1
out.write(f"\nPositive Progress:\t{positive}\n")
out.write(f"Neutral Progress:\t{neutral}\n")
out.write(f"Negative Progress:\t{negative}\n")
out.write(f"\n13 Week Progress:\t{overall}\n")
out.flush()
out.close()
def get_plays_in_week(artistid, chart_date):
startofweek = (chart_date - timedelta(weeks=13) +
timedelta(days=1)).strftime("%Y-%m-%d")
endofweek = chart_date.strftime("%Y-%m-%d")
results = common.get_results(
"SELECT count(*) as logcount "
"FROM log INNER JOIN albumartist ON albumartist.albumid = log.albumid "
"INNER JOIN albumview ON albumartist.albumid = albumview.albumid "
f"WHERE albumartist.artistid = {artistid} and log.logdate between '{startofweek}' and '{endofweek}';"
)
return results[0][0]
def run():
conn = common.conn
seperator = "\n\n" + ("-" * 120) + "\n"
results = common.get_results(
"SELECT artist.artistid, artist.artistname, chr.chartdate, chr.rank, chr.chartrun "
"FROM artist INNER JOIN chart_history_rolling chr "
"ON artist.artistid = chr.artistid "
"ORDER BY artist.sortname, chr.chartdate;")
out = io.open(str(Path.home()) + "\Charts\Rolling Artist Stats.txt",
"w",
encoding='utf-8')
last_artist = ""
last_run = 0
last_rank = 99
for r in results:
artistid, artist, chartdate, rank, run = r
indicator = ""
if artist != last_artist:
out.write(seperator)
out.write(artist.upper() + "\n")
last_artist = artist
last_run = 0
last_rank = 99
if run > last_run:
out.write("\n")
indicator = "N" if last_run == 0 else "R"
last_run = run
last_rank = 99
if rank == 1:
indicator += "*"
elif rank < last_rank and indicator == "":
indicator = "+"
plays = get_plays_in_week(artistid, chartdate)
linestr = "\t\t{:<25}{:>5}{:>5}{:>10}{}\n".format(
chartdate.strftime("%Y-%b-%d"), rank, indicator, "",
"*" * plays if plays > 0 else "")
out.write(linestr)
last_rank = rank
out.flush()
out.close()
def albums_played_last_14days():
sql = "select artistcredit, album, logdate from log_history where logdate > (DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY))"
results = common.get_results(sql)
last_log_date = None
for r in results:
artistname = common.shorten_by_word(r[0], 35)
album = common.shorten_by_word(r[1], 35)
logdate = r[2]
if logdate != last_log_date:
f.write("\n" + ("-" * 5) + " " + str(logdate) + " " + ("-" * 88) +
"\n\n")
last_log_date = logdate
line = "{:<40}{:<40}\n".format(artistname.upper(), album)
f.write(line)
f.write("\n\n")
def top_ten_artists_time_played():
global albumcount
global logcount
f.write("{:<31}{:>9}{:>11}\n".format("by Time Played", "Plays", "Hours"))
f.write("-" * 80 + "\n")
sql = "select artistname, plays, points / 3600 as hours from chart_artist_alltime order by hours desc limit 10;"
results = common.get_results(sql)
for r in results:
artistname = common.shorten_by_word(r[0], 25)
count = int(r[1])
time = r[2]
line = "{:<30}{:>10}{:>11.2f}\n".format(artistname.upper(), count,
time)
f.write(line)
f.write("\n\n")
def albums_added_last_14days():
sql = "select artistcredit, album, datepurchased, source from recent_additions where source <> 'Digital' and " \
"datepurchased > (DATE_SUB(CURRENT_DATE, INTERVAL 28 DAY))"
results = common.get_results(sql)
last_log_date = None
for r in results:
artistname = common.shorten_by_word(r[0], 35)
album = common.shorten_by_word(r[1], 35)
datepurchased = r[2]
source = r[3]
if datepurchased != last_log_date:
f.write("\n" + ("-" * 5) + " " + str(datepurchased) + " " +
("-" * 88) + "\n\n")
last_log_date = datepurchased
line = "{:<40}{:<40}{:<10}\n".format(artistname.upper(), album, source)
f.write(line)
f.write("\n\n")
def albums_requiring_purchase():
global albumcount
global logcount
f.write("{:<80}{:>10}\n".format("Requiring Purchase", "Plays"))
f.write("-" * 105 + "\n")
sql = "select artistname, title, count(*) as Plays from log_reimport lr where lr.input is null and lr.requires_rerip is null " \
"group by artistname, title having Plays >= 3 order by plays desc, artistname, title LIMIT 10"
results = common.get_results(sql)
for r in results:
artistname = common.shorten_by_word(r[0], 35)
title = common.shorten_by_word(r[1], 35)
playcount = int(r[2])
line = "{:<40}{:<40}{:>10}\n".format(artistname.upper(), title,
playcount)
f.write(line)
f.write("\n\n")
def albums_requiring_rerip():
global albumcount
global logcount
f.write("{:<80}{:>10}\n".format("Requiring Re-Rip/Re-Import", "Plays"))
f.write("-" * 105 + "\n")
sql = "select artistcredit, album, playcount FROM albumview as album where TBA=1 " \
"order by playcount desc, artistcredit, album; "
results = common.get_results(sql)
for r in results:
artistname = common.shorten_by_word(r[0], 35)
title = common.shorten_by_word(r[1], 35)
playcount = int(r[2])
line = "{:<40}{:<40}{:>10}\n".format(artistname.upper(), title,
playcount)
f.write(line)
f.write("\n\n")
def top_ten_streams_bycount():
global albumcount
global logcount
f.write("{:<80}{:>10}{:>10}\n".format("Streams", "Plays", "Hours"))
f.write("-" * 105 + "\n")
sql = "select artistname, album, Plays, Points from chart_streamed_alltime order by Plays desc LIMIT 10"
results = common.get_results(sql)
for r in results:
artistname = common.shorten_by_word(r[0], 35)
title = common.shorten_by_word(r[1], 35)
playcount = int(r[2])
playedtime = r[3] / 3600
line = "{:<40}{:<40}{:>10}{:>10.2f}\n".format(artistname.upper(),
title, playcount,
playedtime)
f.write(line)
f.write("\n\n")
