Changeset 59b2e9c


Ignore:
Timestamp:
05/01/25 21:17:02 (2 weeks ago)
Author:
ManuelTrajcev <manueltrajcev7@…>
Branches:
master
Children:
ac66823
Parents:
4abe330
Message:

querry mostr popular artist per customer for each genre

Files:
4 added
12 edited

Legend:

Unmodified
Added
Removed

  • MuiscOrganizationSystem/settings.py

    r4abe330 r59b2e9c  
    1616BASE_DIR = Path(__file__).resolve().parent.parent
    1717
    18 
    1918# Quick-start development settings - unsuitable for production
    2019# See https://docs.djangoproject.com/en/5.1/howto/deployment/checklist/
    … …  
    2726
    2827ALLOWED_HOSTS = []
    29 
    3028
    3129# Application definition
    … …  
    4947    "django.contrib.messages.middleware.MessageMiddleware",
    5048    "django.middleware.clickjacking.XFrameOptionsMiddleware",
     49    'music.middleware.Redirect404ToHomeMiddleware',
    5150]
    5251
    … …  
    7271WSGI_APPLICATION = "MuiscOrganizationSystem.wsgi.application"
    7372
    74 
    7573# Database
    7674# https://docs.djangoproject.com/en/5.1/ref/settings/#databases
    … …  
    9492}
    9593
    96 
    9794# Password validation
    9895# https://docs.djangoproject.com/en/5.1/ref/settings/#auth-password-validators
    … …  
    113110]
    114111
    115 
    116112# Internationalization
    117113# https://docs.djangoproject.com/en/5.1/topics/i18n/
    … …  
    125121USE_TZ = True
    126122
    127 
    128123# Static files (CSS, JavaScript, Images)
    129124# https://docs.djangoproject.com/en/5.1/howto/static-files/

  • music/urls.py

    r4abe330 r59b2e9c  
    11from django.urls import path
     2from django.views.generic import RedirectView
     3
    24from . import views
    35
    46urlpatterns = [
     7    path('', views.home_page, name='homepage'),
    58    path('album/', views.album_list, name='album_list'),
    69    path('artist/', views.artist_list, name='artist_list'),
    … …  
    1013    path('track/', views.track_list, name='track_list'),
    1114    path('customer/genres-per-customer', views.genres_per_customer, name='genres_per_customer'),
    12     path('customer/invoices-per-customer', views.invoice_per_customer_after_date, name='invoice_per_customer_after_date'),
    13     path('customer/most-popular-genre-per-customer', views.most_listened_genre_per_customer, name='most_listened_genre_per_customer'),
     15    path('customer/invoices-per-customer', views.invoice_per_customer_after_date,
     16         name='invoice_per_customer_after_date'),
     17    path('customer/artist-per-genre', views.most_popular_artist_per_customer_per_genre,
     18         name='most_popular_artist_per_customer_per_genre'),
     19    path('customer/most-popular-genre-per-customer', views.most_listened_genre_per_customer,
     20         name='most_listened_genre_per_customer'),
    1421    path('media-type/percentage', views.media_type_percentage, name='media_type_percentage'),
    1522    path('track/per-genre', views.tracks_count_per_genre, name='track_count_per_genre'),

  • music/views.py

    r4abe330 r59b2e9c  
    33import django
    44from django.db import connection
     5from django.shortcuts import redirect
    56
    67os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'MuiscOrganizationSystem.settings')
    … …  
    1112
    1213# Create your views here.
     14
     15def home_page(request):
     16    return render(request, 'home.html')
     17
     18def redirect_to_home(request, exception):
     19    return redirect('home_page')
    1320
    1421def album_list(request):
    … …  
    2734    heading = request.GET.get('model', 'All Artists')
    2835    data = Artist.objects.values_list('name', flat=True)
    29     print(data)
     36
    3037    return render(request, 'list.html', {'data': data, 'heading': heading})
    3138
    … …  
    3845
    3946    data = [{'genre': row[0], 'count': row[1]} for row in rows]
    40     print(data)
    4147
    4248    return render(request, 'track_count_per_genre.html', {'data': data})
    … …  
    4955
    5056    data = [{'artist': row[0], 'avg': row[1]} for row in rows]
    51     print(data)
    5257
    5358    return render(request, 'avg_track_duration.html', {'data': data})
    … …  
    108113    selected_customer_id = request.GET.get('customer_id')
    109114    data = []
     115    customer = None
    110116
    111117    if selected_customer_id:
     118        customer = Customer.objects.filter(customer_id=selected_customer_id).first()
    112119        query = """
    113120            SELECT c.first_name as first_name, c.last_name as last_name, g.name as genre, COUNT(tr.track_id) as track_count
    … …  
    130137        'customers': customers,
    131138        'data': data,
     139        'customer': customer,
     140        'selected_customer_id': selected_customer_id,
     141    })
     142
     143def most_popular_artist_per_customer_per_genre(request):
     144    customers = Customer.objects.all()
     145    selected_customer_id = request.GET.get('customer_id')
     146    data = []
     147    customer = []
     148    if selected_customer_id:
     149        query = """WITH PlayCounts AS (
     150                SELECT
     151                    g.genre_id,
     152                    g.name AS genre_name,
     153                    ar.name AS artist_name,
     154                    COUNT(*) AS play_count
     155                FROM customer c
     156                JOIN invoice i ON c.customer_id = i.customer_id
     157                JOIN invoice_line il ON i.invoice_id = il.invoice_id
     158                JOIN track tr ON il.track_id = tr.track_id
     159                JOIN genre g ON tr.genre_id = g.genre_id
     160                JOIN album a ON tr.album_id = a.album_id
     161                JOIN artist ar ON a.artist_id = ar.artist_id
     162                WHERE c.customer_id = %s
     163                GROUP BY g.genre_id, g.name, ar.name
     164            ),
     165            MaxPlayCounts AS (
     166                SELECT genre_id, MAX(play_count) AS max_count
     167                FROM PlayCounts
     168                GROUP BY genre_id
     169            )
     170            SELECT pc.genre_name, pc.artist_name, pc.play_count
     171            FROM PlayCounts pc
     172            JOIN MaxPlayCounts mpc ON pc.genre_id = mpc.genre_id AND pc.play_count = mpc.max_count;
     173        """
     174        customer = Customer.objects.filter(customer_id=selected_customer_id).first()
     175
     176        with connection.cursor() as cursor:
     177            cursor.execute(query, [selected_customer_id])
     178            rows = cursor.fetchall()
     179            data = [{'genre': row[0], 'arist': row[1]} for row in rows]
     180
     181    return render(request, 'most_popular_artist_per_customer_per_genre.html', {
     182        'customers': customers,
     183        'customer': customer,
     184        'data': data,
    132185        'selected_customer_id': selected_customer_id,
    133186    })
    … …  
    140193    data = []
    141194    sum = 0
     195    customer = None
    142196
    143197    if selected_customer_id:
     198        customer = Customer.objects.filter(customer_id=selected_customer_id).first()
    144199        if selected_date:
    145200            query = """
    … …  
    180235        'total_sum': sum,
    181236        'data': data,
     237        'customer': customer,
    182238        'selected_customer_id': selected_customer_id,
    183239    })

  • templates/avg_price_per_artist.html

    r4abe330 r59b2e9c  
    2929                <li class="list-group-item d-flex justify-content-between align-items-center">
    3030                    <div class="col-6">
    31                         <strong>{{ row.name }}</strong>
     31                       {{ row.name }}
    3232                    </div>
    3333                    <div class="col-6 text-end">

  • templates/avg_track_duration.html

    r4abe330 r59b2e9c  
    1919                <a href="#" class="list-group-item list-group-item-action d-flex justify-content-between align-items-center">
    2020                    <div>
    21                         <strong>{{ row.artist }}</strong>
     21                        {{ row.artist }}
    2222                    </div>
    2323                    <span class="badge bg-primary rounded-pill">{{ row.avg }}</span>

  • templates/genres_per_customer.html

    r4abe330 r59b2e9c  
    3232        </div>
    3333    </form>
    34 
     34    {% if customer %}
     35        <div class="row display-5">
     36            <div class="col-3 bg-light-subtle m-1 pb-3 border border-secondary-subtle rounded-3">
     37                {{ customer }}
     38            </div>
     39        </div>
     40    {% endif %}
    3541    {% if not data %}
    3642        <div class="alert alert-warning" role="alert">
    … …  
    3844        </div>
    3945    {% else %}
    40         <div class="table-responsive">
    41             <table class="table table-striped">
    42                 <thead>
    43                 <tr>
    44                     <th scope="col">First Name</th>
    45                     <th scope="col">Last Name</th>
    46                     <th scope="col">Genre</th>
    47                     <th scope="col">Number of Tracks</th>
    48                 </tr>
    49                 </thead>
    50                 <tbody>
     46        <div class="container">
     47            <div class="row col-6 justify-content-between mb-3">
     48                <div class="col-6 font-weight-bold">
     49                    <strong>Genre</strong>
     50                </div>
     51                <div class="col-6 font-weight-bold px-4">
     52                    <strong>Number of tracks</strong>
     53                </div>
     54            </div>
     55            <ul class="row list-group col-6">
    5156                {% for row in data %}
    52                     <tr>
    53                         <td>{{ row.first_name }}</td>
    54                         <td>{{ row.last_name }}</td>
    55                         <td>{{ row.genre }}</td>
    56                         <td>{{ row.track_count }}</td>
    57                     </tr>
     57                    <li class="list-group-item d-flex justify-content-between align-items-center">
     58                        <div class="col-6">
     59                            <span>{{ row.genre }}</span>
     60                        </div>
     61                        <div class="col-6">
     62                             <span>Number of Tracks: {{ row.track_count }}</span>
     63                        </div>
     64                    </li>
     65                {% empty %}
     66                    <li class="list-group-item">No data available for this customer.</li>
    5867                {% endfor %}
    59                 </tbody>
    60             </table>
     68            </ul>
    6169        </div>
    6270    {% endif %}

  • templates/invoices_per_customer_after_date.html

    r4abe330 r59b2e9c  
    2727            </select>
    2828        </div>
    29 
    3029        <div class="col-md-4">
    3130            <label for="invoice_date" class="form-label">Select Date:</label>
    … …  
    3837        </div>
    3938    </form>
     39
     40      {% if customer %}
     41            <div class="row display-5">
     42                <div class="col-3 bg-light-subtle m-1 pb-3 border border-secondary-subtle rounded-3">
     43                    {{ customer }}
     44                </div>
     45            </div>
     46        {% endif %}
    4047
    4148    {% if not selected_customer_id %}

  • templates/most_listened_genre_per_customer.html

    r4abe330 r59b2e9c  
    1010</head>
    1111<body class="d-flex bg-light">
    12     {% include 'sidebar.html' %}
    13     <div class="container mt-5">
    14         <h1 class="text-center mb-4">Most Listened Genre per Customer</h1>
     12{% include 'sidebar.html' %}
     13<div class="container mt-5">
     14    <h1 class="text-center mb-4">Most Listened Genre per Customer</h1>
    1515
    16         <div class="list-group">
    17             <h4 class="mb-3">First Name &emsp; Last Name &emsp; - &emsp; Genre</h4>
    18             {% for row in data %}
    19                 <a href="#" class="list-group-item list-group-item-action">
    20                     {{ row.first_name }} &emsp;-&emsp; {{ row.last_name }} &emsp;-&emsp; {{ row.most_listened_genre }}
    21                 </a>
    22             {% empty %}
    23                 <div class="alert alert-warning" role="alert">
    24                     No data found.
     16    <div class="list-group">
     17        <div class="row">
     18            <div class="col-4 font-weight-bold"><strong>First Name</strong></div>
     19            <div class="col-4 font-weight-bold"><strong>Last Name</strong></div>
     20            <div class="col-4 font-weight-bold"><strong>Genre</strong></div>
     21        </div>
     22        {% for row in data %}
     23            <a href="#" class="list-group-item list-group-item-action">
     24                <div class="row">
     25                    <div class="col-4 font-weight-bold">{{ row.first_name }}</div>
     26                    <div class="col-4 font-weight-bold">{{ row.last_name }}</div>
     27                    <div class="col-4 font-weight-bold">{{ row.most_listened_genre }}</div>
    2528                </div>
    26             {% endfor %}
    27         </div>
     29            </a>
     30        {% empty %}
     31            <div class="alert alert-warning" role="alert">
     32                No data found.
     33            </div>
     34        {% endfor %}
    2835    </div>
     36</div>
    2937</body>
    3038</html>

  • templates/sidebar.html

    r4abe330 r59b2e9c  
    1 <div class="d-flex flex-column flex-shrink-0 p-3 bg-light" style="width: 250px;">
    2     <h4 class="mb-4">Navigation</h4>
     1<div class="h-100 d-flex flex-column flex-shrink-0 p-3 bg-light-subtle border border-2 border-secondary-subtle rounded-end-5" style="width: 250px;">
     2    <h4 class="mb-4">Menu</h4>
    33    <ul class="nav nav-pills flex-column mb-auto">
    44        <li class="nav-item">
    … …  
    6262            </a>
    6363        </li>
     64     <li class="nav-item">
     65            <a href="{% url 'most_popular_artist_per_customer_per_genre' %}" class="nav-link {% if request.path == '/customer/artist-per-genre' %}active{% endif %}">
     66                Most Popular Artists Per Customer
     67            </a>
     68        </li>
    6469    </ul>
    6570</div>
Note: See TracChangeset for help on using the changeset viewer.