Index: music/views.py =================================================================== --- music/views.py (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ music/views.py (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -79,5 +79,5 @@ rows = cursor.fetchall() - data = [{'name': row[0], 'num_invoices': row[1]} for row in rows] + data = [{'name': row[0], 'num_invoices': row[1], 'money_earned': row[2]} for row in rows] return render(request, 'rank_list_artists.html', {'data': data}) Index: music/views/rank_list_artists.sql =================================================================== --- music/views/rank_list_artists.sql (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ music/views/rank_list_artists.sql (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -1,11 +1,11 @@ CREATE VIEW rank_list_artists AS - SELECT - (ar.name), - count(il.invoice_line_id) -as num_invoices - FROM artist ar - left join album al on ar.artist_id = al.album_id - left join track tr on al.album_id = tr.album_id - left join invoice_line il on tr.track_id = il.track_id - group by ar.name - order by num_invoices desc +SELECT ar.name, + count(il.invoice_line_id) AS num_invoices, + COALESCE(SUM(i.total), 0) AS money_earned +FROM artist ar + LEFT JOIN album al ON ar.artist_id = al.album_id + LEFT JOIN track tr ON al.album_id = tr.album_id + LEFT JOIN invoice_line il ON tr.track_id = il.track_id + LEFT JOIN invoice i on il.invoice_id = i.invoice_id +GROUP BY ar.name +ORDER BY money_earned DESC; Index: templates/avg_price_per_artist.html =================================================================== --- templates/avg_price_per_artist.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/avg_price_per_artist.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -1,17 +1,47 @@ - + + + Average Track Price per Artist + + - -

Average Track Price per Artist

-
    -

    Name - Avg. track price

    + - {% for row in data %} -
  1. {{ row.name }} - {{ row.avg_price_per_track }}
    2. - {% endfor %} -
+{% include 'sidebar.html' %} + +
+

Average Track Price per Artist

+ +

Name - Average Track Price

+ + {% if not data %} + + {% else %} + + {% endif %} +
+ - Index: templates/avg_track_duration.html =================================================================== --- templates/avg_track_duration.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/avg_track_duration.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,13 +2,28 @@ - Average track duration per artist + + + Average Track Duration per Artist - -

Average track duration per artist

- + + {% include 'sidebar.html' %} + +
+

Average Track Duration per Artist

+ +
+ {% for row in data %} + +
+ {{ row.artist }} +
+ {{ row.avg }} + + {% endfor %} +
+
Index: templates/genres_per_customer.html =================================================================== --- templates/genres_per_customer.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/genres_per_customer.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,31 +2,64 @@ - Number of tracks from each genre per Customer + + + Number of Tracks from Each Genre per Customer - -

Number of tracks from each genre per Customer

+ +{% include 'sidebar.html' %} +
+

Number of Tracks from Each Genre per Customer

-
- - - -
+
+
+ + +
+
+ +
+
-
    -

    First Name  Last Name  -  Genre - Num of Tracks

    - {% for row in data %} -
  1. - {{ row.first_name }} - {{ row.last_name }} - {{ row.genre }} - {{ row.track_count }}
    2. - {% endfor %} -
+ {% if not data %} + + {% else %} +
+ + + + + + + + + + + {% for row in data %} + + + + + + + {% endfor %} + +
First NameLast NameGenreNumber of Tracks
{{ row.first_name }}{{ row.last_name }}{{ row.genre }}{{ row.track_count }}
+
+ {% endif %} +
Index: templates/invoices_per_customer_after_date.html =================================================================== --- templates/invoices_per_customer_after_date.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/invoices_per_customer_after_date.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -9,6 +9,6 @@ Number of Tracks per Customer - - + +{% include 'sidebar.html' %}

Invoice Created by Customer

Index: templates/list.html =================================================================== --- templates/list.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/list.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,15 +2,28 @@ + + {{ heading }} - -

{{ heading }}

- + + {% include 'sidebar.html' %} +
+

{{ heading }}

+ +
+ {% for d in data %} + + {{ d }} + + {% empty %} + + {% endfor %} +
+
Index: templates/media_type_percentage.html =================================================================== --- templates/media_type_percentage.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/media_type_percentage.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,16 +2,36 @@ - Most active customers + + + Most Active Customers - -

Most active customers

-
    -

    Media Type- Num of tracks - Percentage

    - - {% for row in data %} -
  1. {{ row.name }} - {{ row.num_of_tracks }} - {{ row.percentage }}%
    2. - {% endfor %} -
+ + {% include 'sidebar.html' %} +
+

Most Active Customers

+
+ + + + + + + + + + {% for row in data %} + + + + + + {% endfor %} + +
Media TypeNumber of TracksPercentage
{{ row.name }}{{ row.num_of_tracks }}{{ row.percentage }}%
+
+
- Index: templates/most_listened_genre_per_customer.html =================================================================== --- templates/most_listened_genre_per_customer.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/most_listened_genre_per_customer.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,16 +2,29 @@ + + Most listened Genre per Customer - -

Most listened Genre per Customer

-
    -

    First Name- Last Name - Genre

    + + {% include 'sidebar.html' %} +
    +

    Most Listened Genre per Customer

    - {% for row in data %} -
  1. {{ row.first_name }} - {{ row.last_name }} - {{ row.most_listened_genre }}
    2. - {% endfor %} -
+
+

First Name   Last Name   -   Genre

+ {% for row in data %} + + {{ row.first_name }}  -  {{ row.last_name }}  -  {{ row.most_listened_genre }} + + {% empty %} + + {% endfor %} +
+
- Index: templates/rank_list_artists.html =================================================================== --- templates/rank_list_artists.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/rank_list_artists.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,16 +2,40 @@ - Most popular Artist + + + Artist with Highest Earnings - -

Most popular Artist

-
    -

    Name - Total invoices

    + +{% include 'sidebar.html' %} +
    +

    Artist with Highest Earnings

    - {% for row in data %} -
  1. {{ row.name }} - {{ row.num_invoices }}
    2. - {% endfor %} -
+
+
+
Name
+
Total Invoices
+
Total Money Earned
+
+ + {% for row in data %} + + +
+
{{ row.name }}
+
{{ row.num_invoices }}
+
${{ row.money_earned }}
+
+ + + {% empty %} + + {% endfor %} +
+ - Index: templates/rank_list_most_active_customers.html =================================================================== --- templates/rank_list_most_active_customers.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/rank_list_most_active_customers.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,16 +2,37 @@ - Most active customers + + + Most Active Customers - -

Most active customers

-
    -

    Name - Total orders - Total money spent

    + +{% include 'sidebar.html' %} +
    +

    Most Active Customers

    - {% for row in data %} -
  1. {{ row.name }} - {{ row.total_orders }} - ${{ row.total_money_spent }}
    2. - {% endfor %} -
+
+
+
Name
+
Total Orders
+
Total Money Spent
+
+ {% for row in data %} + +
+
{{ row.name }}
+
{{ row.total_orders }}
+
${{ row.total_money_spent }}
+
+ + {% empty %} + + {% endfor %} +
+ - Index: templates/sidebar.html =================================================================== --- templates/sidebar.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ templates/sidebar.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -0,0 +1,65 @@ +
+

Navigation

+ +
Index: templates/track_count_per_genre.html =================================================================== --- templates/track_count_per_genre.html (revision 611686e81670ba9e8f1d5a0aaffcb2d604f86584) +++ templates/track_count_per_genre.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) @@ -2,13 +2,28 @@ - Track count per Genre + + + Track Count Per Genre - -

Track Count Per Genre

- + + {% include 'sidebar.html' %} +
+

Track Count Per Genre

+ +
+ {% for row in data %} + + {{ row.genre }} - {{ row.count }} tracks + + {% empty %} + + {% endfor %} +
+