music-tools/music-report.rb at main · nhr/music-tools · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
#!/usr/bin/env ruby

require "date"
require "sqlite3"

DBPATH = "/Users/hripps/.music-history.sqlite"
RPPATH = "/Users/hripps/Music/play_records.html"

def sanitize(text)
  return text.gsub("'","''")
end

def stringify_list(list)
  if list.length == 0
    return ''
  elsif list.length == 1
    return list[0]
  end
  return list.join(' or ')
end

def generate_row(tkey,info)
  at_parts = tkey.split('::::')
  artist   = at_parts[0]
  title    = at_parts[1]
  return [title,info[:last_played],info[:total_plays],artist,info[:genre],info[:path]]
end

dbh = SQLite3::Database.new DBPATH

puts "Pulling track info from DB"
all_tracks = {}
dbh.execute( "SELECT NAME, ARTIST, GENRE, PATH, OLD_PATH FROM TRACKS WHERE SAMPLE = 0 ORDER BY lower(NAME) ASC" ) do |row|
  old_path = row[4] == nil ? '' : row[4]
  info = {
    :title => row[0],
    :artist => row[1],
    :genre => row[2],
    :path => row[3],
    :old_path => old_path,
    :last_played => '',
    :total_plays => 0,
  }
  tkey = "#{info[:artist]}::::#{info[:title]}"
  if not all_tracks.has_key?(tkey)
    all_tracks[tkey] = info
  else
    puts "Unexpected same-track collision: #{tkey}"
  end
end

puts "Correlating set list data"
play_history = []
all_tracks.keys.each do |tkey|
  artist   = all_tracks[tkey][:artist]
  title    = all_tracks[tkey][:title]
  old_path = all_tracks[tkey][:old_path]
  dbh.execute("SELECT count() AS COUNT, max(AIRDATE) AS LATEST FROM HISTORY WHERE ARTIST = '#{sanitize(artist)}' AND TRACK = '#{sanitize(title)}'") do |row|
    if not row.nil?
      if not row[0].nil?
        all_tracks[tkey][:total_plays] = row[0]
      end
      if not row[1].nil?
        all_tracks[tkey][:last_played] = row[1]
      end
    end
  end
  if all_tracks[tkey][:total_plays] == 0 and old_path != ''
    # Older sets have the old file system organization
    dbh.execute("SELECT count() AS COUNT, max(AIRDATE) AS LATEST FROM HISTORY WHERE TRACK_PATH = '#{sanitize(old_path)}'") do |row|
      if not row.nil?
        if not row[0].nil?
          all_tracks[tkey][:total_plays] = row[0]
        end
        if not row[1].nil?
          all_tracks[tkey][:last_played] = row[1]
        end
      end
    end
  end
  next if all_tracks[tkey][:total_plays] == 0
  play_history << generate_row(tkey,all_tracks[tkey])
end

report = <<-TEXT
<html lang="en">
 <head>
 <meta charset="utf-8">
 <meta name="viewport" content="width=device-width, initial-scale=1">
 <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.0/dist/css/bootstrap.min.css" integrity="sha384-B0vP5xmATw1+K9KRQjQERJvTumQW0nPEzvF6L/Z6nronJ3oUOFUFpCjEUQouq2+l" crossorigin="anonymous">
 <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.css">
 <title>Track History Report</title>
 </head>
 <body>
  <div class="container">
   <h1>Track History Report</h1>
   <table id="tracks" class="table display">
    <thead>
     <tr>
      <th scope="col">Name</th>
      <th scope="col">Date</th>
      <th scope="col">Plays</th>
      <th scope="col">Artist</th>
      <th scope="col">Genre</th>
      <th scope="col">Path</th>
     </tr>
    </thead>
    <tbody>
TEXT

play_history.each do |row|
  html = <<-TEXT
     <tr>
      <th scope="row">#{row[0]}</th>
      <td>#{row[1]}</td>
      <td>#{row[2]}</td>
      <td>#{row[3]}</td>
      <td>#{row[4]}</td>
      <td>#{row[5]}</td>
     </tr>
TEXT
  report << html
end

footer = <<-TEXT
    </tbody>
   </table>
  </div>
  <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.0/dist/js/bootstrap.bundle.min.js" integrity="sha384-Piv4xVNRyMGpqkS2by6br4gNJ7DXjqk09RmUpJ8jgGtD7zP9yug3goQfGII0yAns" crossorigin="anonymous"></script>
  <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.js"></script>
  <script type="text/javascript">
   $(document).ready( function () {
     $('#tracks').DataTable();
   } );
  </script>
</body>
</html>
TEXT

report << footer

File.write(RPPATH, report)

exit