Changeset 59b2e9c for music

Timestamp:

05/01/25 21:17:02 (3 weeks ago)

Author:

ManuelTrajcev <manueltrajcev7@…>

Branches:

master

Children:

ac66823

Parents:

4abe330

Message:

querry mostr popular artist per customer for each genre

Location:

music

Files:

• __pycache__/middleware.cpython-312.pyc (added)
• __pycache__/urls.cpython-312.pyc (modified) ( previous)
• __pycache__/views.cpython-312.pyc (modified) ( previous)
• middleware.py (added)
• urls.py (modified) (2 diffs)
• views.py (modified) (9 diffs)

music/urls.py

@@ -1 +1 @@
 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 +13 @@
     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'),

music/views.py

@@ -3 +3 @@
 import django
 from django.db import connection
+from django.shortcuts import redirect
 
 os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'MuiscOrganizationSystem.settings')
@@ -11 +12 @@
 
 # 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 +34 @@
     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 +45 @@
 
     data = [{'genre': row[0], 'count': row[1]} for row in rows]
-    print(data)
 
     return render(request, 'track_count_per_genre.html', {'data': data})
@@ -49 +55 @@
 
     data = [{'artist': row[0], 'avg': row[1]} for row in rows]
-    print(data)
 
     return render(request, 'avg_track_duration.html', {'data': data})
@@ -108 +113 @@
     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 +137 @@
         '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 +193 @@
     data = []
     sum = 0
+    customer = None
 
     if selected_customer_id:
+        customer = Customer.objects.filter(customer_id=selected_customer_id).first()
         if selected_date:
             query = """
@@ -180 +235 @@
         'total_sum': sum,
         'data': data,
+        'customer': customer,
         'selected_customer_id': selected_customer_id,
     })