Index: MuiscOrganizationSystem/settings.py =================================================================== --- MuiscOrganizationSystem/settings.py (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ MuiscOrganizationSystem/settings.py (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -16,5 +16,4 @@ BASE_DIR = Path(__file__).resolve().parent.parent - # Quick-start development settings - unsuitable for production # See https://docs.djangoproject.com/en/5.1/howto/deployment/checklist/ @@ -27,5 +26,4 @@ ALLOWED_HOSTS = [] - # Application definition @@ -49,4 +47,5 @@ "django.contrib.messages.middleware.MessageMiddleware", "django.middleware.clickjacking.XFrameOptionsMiddleware", + 'music.middleware.Redirect404ToHomeMiddleware', ] @@ -72,5 +71,4 @@ WSGI_APPLICATION = "MuiscOrganizationSystem.wsgi.application" - # Database # https://docs.djangoproject.com/en/5.1/ref/settings/#databases @@ -94,5 +92,4 @@ } - # Password validation # https://docs.djangoproject.com/en/5.1/ref/settings/#auth-password-validators @@ -113,5 +110,4 @@ ] - # Internationalization # https://docs.djangoproject.com/en/5.1/topics/i18n/ @@ -125,5 +121,4 @@ USE_TZ = True - # Static files (CSS, JavaScript, Images) # https://docs.djangoproject.com/en/5.1/howto/static-files/ Index: music/middleware.py =================================================================== --- music/middleware.py (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) +++ music/middleware.py (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -0,0 +1,13 @@ +from django.shortcuts import redirect +from django.http import Http404 + +class Redirect404ToHomeMiddleware: + def __init__(self, get_response): + self.get_response = get_response + + def __call__(self, request): + try: + response = self.get_response(request) + return response + except Http404: + return redirect('homepage') Index: music/urls.py =================================================================== --- music/urls.py (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ music/urls.py (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -1,6 +1,9 @@ from django.urls import path +from django.views.generic import RedirectView + from . import views urlpatterns = [ + path('', views.home_page, name='homepage'), path('album/', views.album_list, name='album_list'), path('artist/', views.artist_list, name='artist_list'), @@ -10,6 +13,10 @@ path('track/', views.track_list, name='track_list'), path('customer/genres-per-customer', views.genres_per_customer, name='genres_per_customer'), - path('customer/invoices-per-customer', views.invoice_per_customer_after_date, name='invoice_per_customer_after_date'), - path('customer/most-popular-genre-per-customer', views.most_listened_genre_per_customer, name='most_listened_genre_per_customer'), + path('customer/invoices-per-customer', views.invoice_per_customer_after_date, + name='invoice_per_customer_after_date'), + path('customer/artist-per-genre', views.most_popular_artist_per_customer_per_genre, + name='most_popular_artist_per_customer_per_genre'), + path('customer/most-popular-genre-per-customer', views.most_listened_genre_per_customer, + name='most_listened_genre_per_customer'), path('media-type/percentage', views.media_type_percentage, name='media_type_percentage'), path('track/per-genre', views.tracks_count_per_genre, name='track_count_per_genre'), Index: music/views.py =================================================================== --- music/views.py (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ music/views.py (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -3,4 +3,5 @@ import django from django.db import connection +from django.shortcuts import redirect os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'MuiscOrganizationSystem.settings') @@ -11,4 +12,10 @@ # Create your views here. + +def home_page(request): + return render(request, 'home.html') + +def redirect_to_home(request, exception): + return redirect('home_page') def album_list(request): @@ -27,5 +34,5 @@ heading = request.GET.get('model', 'All Artists') data = Artist.objects.values_list('name', flat=True) - print(data) + return render(request, 'list.html', {'data': data, 'heading': heading}) @@ -38,5 +45,4 @@ data = [{'genre': row[0], 'count': row[1]} for row in rows] - print(data) return render(request, 'track_count_per_genre.html', {'data': data}) @@ -49,5 +55,4 @@ data = [{'artist': row[0], 'avg': row[1]} for row in rows] - print(data) return render(request, 'avg_track_duration.html', {'data': data}) @@ -108,6 +113,8 @@ selected_customer_id = request.GET.get('customer_id') data = [] + customer = None if selected_customer_id: + customer = Customer.objects.filter(customer_id=selected_customer_id).first() query = """ SELECT c.first_name as first_name, c.last_name as last_name, g.name as genre, COUNT(tr.track_id) as track_count @@ -130,4 +137,50 @@ 'customers': customers, 'data': data, + 'customer': customer, + 'selected_customer_id': selected_customer_id, + }) + +def most_popular_artist_per_customer_per_genre(request): + customers = Customer.objects.all() + selected_customer_id = request.GET.get('customer_id') + data = [] + customer = [] + if selected_customer_id: + query = """WITH PlayCounts AS ( + SELECT + g.genre_id, + g.name AS genre_name, + ar.name AS artist_name, + COUNT(*) AS play_count + FROM customer c + JOIN invoice i ON c.customer_id = i.customer_id + JOIN invoice_line il ON i.invoice_id = il.invoice_id + JOIN track tr ON il.track_id = tr.track_id + JOIN genre g ON tr.genre_id = g.genre_id + JOIN album a ON tr.album_id = a.album_id + JOIN artist ar ON a.artist_id = ar.artist_id + WHERE c.customer_id = %s + GROUP BY g.genre_id, g.name, ar.name + ), + MaxPlayCounts AS ( + SELECT genre_id, MAX(play_count) AS max_count + FROM PlayCounts + GROUP BY genre_id + ) + SELECT pc.genre_name, pc.artist_name, pc.play_count + FROM PlayCounts pc + JOIN MaxPlayCounts mpc ON pc.genre_id = mpc.genre_id AND pc.play_count = mpc.max_count; + """ + customer = Customer.objects.filter(customer_id=selected_customer_id).first() + + with connection.cursor() as cursor: + cursor.execute(query, [selected_customer_id]) + rows = cursor.fetchall() + data = [{'genre': row[0], 'arist': row[1]} for row in rows] + + return render(request, 'most_popular_artist_per_customer_per_genre.html', { + 'customers': customers, + 'customer': customer, + 'data': data, 'selected_customer_id': selected_customer_id, }) @@ -140,6 +193,8 @@ data = [] sum = 0 + customer = None if selected_customer_id: + customer = Customer.objects.filter(customer_id=selected_customer_id).first() if selected_date: query = """ @@ -180,4 +235,5 @@ 'total_sum': sum, 'data': data, + 'customer': customer, 'selected_customer_id': selected_customer_id, }) Index: templates/avg_price_per_artist.html =================================================================== --- templates/avg_price_per_artist.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ templates/avg_price_per_artist.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -29,5 +29,5 @@
  • - {{ row.name }} + {{ row.name }}
    Index: templates/avg_track_duration.html =================================================================== --- templates/avg_track_duration.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ templates/avg_track_duration.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -19,5 +19,5 @@
    - {{ row.artist }} + {{ row.artist }}
    {{ row.avg }} Index: templates/genres_per_customer.html =================================================================== --- templates/genres_per_customer.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ templates/genres_per_customer.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -32,5 +32,11 @@
    - + {% if customer %} +
    +
    + {{ customer }} +
    +
    + {% endif %} {% if not data %} {% else %} -
    - - - - - - - - - - +
    +
    +
    + Genre +
    +
    + Number of tracks +
    +
    +
      {% for row in data %} -
    - - - - - +
  • +
    + {{ row.genre }} +
    +
    + Number of Tracks: {{ row.track_count }} +
    +
    • + {% empty %} +
  • No data available for this customer.
    • {% endfor %} - -
    First NameLast NameGenreNumber of Tracks
    {{ row.first_name }}{{ row.last_name }}{{ row.genre }}{{ row.track_count }}
    +
    {% endif %} Index: templates/home.html =================================================================== --- templates/home.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) +++ templates/home.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -0,0 +1,19 @@ + + + + + + Number of Tracks per Customer + + HomePage + + +{% include 'sidebar.html' %} +
    +

    Home

    +
    + + Index: templates/invoices_per_customer_after_date.html =================================================================== --- templates/invoices_per_customer_after_date.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ templates/invoices_per_customer_after_date.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -27,5 +27,4 @@ -
    @@ -38,4 +37,12 @@
    + + {% if customer %} +
    +
    + {{ customer }} +
    +
    + {% endif %} {% if not selected_customer_id %} Index: templates/most_listened_genre_per_customer.html =================================================================== --- templates/most_listened_genre_per_customer.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ templates/most_listened_genre_per_customer.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -10,21 +10,29 @@ - {% include 'sidebar.html' %} -
    -

    Most Listened Genre per Customer

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

    Most Listened Genre per Customer

    -
    -

    First Name   Last Name   -   Genre

    - {% for row in data %} -     - {{ row.first_name }}  -  {{ row.last_name }}  -  {{ row.most_listened_genre }} - - {% empty %} - +
    Index: templates/most_popular_artist_per_customer_per_genre.html =================================================================== --- templates/most_popular_artist_per_customer_per_genre.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) +++ templates/most_popular_artist_per_customer_per_genre.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -0,0 +1,62 @@ + + + + + + Most Listened Artist of each Genre per Customer + + +{% include 'sidebar.html' %} +
    +

    Most Listened Artist of each Genre per Customer

    + +
    +
    + + +
    + +
    + +
    +
    + {% if customer %} +
    +
    + {{ customer }} +
    +
    + {% endif %} + +
    +
    +
    Genre
    +
    Artist
    +
    + {% for row in data %} + +
    +
    {{ row.genre }}
    +
    {{ row.arist }}
    +
    +     + {% empty %} + + {% endfor %} +
    +
    + + Index: templates/sidebar.html =================================================================== --- templates/sidebar.html (revision 4abe33096ad7903537ae92161eabe3110b18b4d2) +++ templates/sidebar.html (revision 59b2e9cdcf9053e1764ec21a504c56b5f7ef7aba) @@ -1,4 +1,4 @@ -
    -

    Navigation

    +
    +

    Menu