Changeset 4abe330


Ignore:
Timestamp:
05/01/25 12:32:20 (2 weeks ago)
Author:
ManuelTrajcev <manueltrajcev7@…>
Branches:
master
Children:
59b2e9c
Parents:
611686e
Message:

rank_list_artists view update + bootstrap styling

Files:
1 added
13 edited

Legend:

Unmodified
Added
Removed

  • music/views.py

    r611686e r4abe330  
    7979        rows = cursor.fetchall()
    8080
    81     data = [{'name': row[0], 'num_invoices': row[1]} for row in rows]
     81    data = [{'name': row[0], 'num_invoices': row[1], 'money_earned': row[2]} for row in rows]
    8282
    8383    return render(request, 'rank_list_artists.html', {'data': data})

  • music/views/rank_list_artists.sql

    r611686e r4abe330  
    11CREATE VIEW rank_list_artists AS
    2     SELECT
    3             (ar.name),
    4             count(il.invoice_line_id)
    5 as num_invoices
    6     FROM artist ar
    7     left join album al on ar.artist_id = al.album_id
    8     left join track tr on al.album_id = tr.album_id
    9     left join invoice_line il on tr.track_id = il.track_id
    10     group by ar.name
    11     order by num_invoices desc
     2SELECT ar.name,
     3       count(il.invoice_line_id) AS num_invoices,
     4       COALESCE(SUM(i.total), 0) AS money_earned
     5FROM artist ar
     6         LEFT JOIN album al ON ar.artist_id = al.album_id
     7         LEFT JOIN track tr ON al.album_id = tr.album_id
     8         LEFT JOIN invoice_line il ON tr.track_id = il.track_id
     9         LEFT JOIN invoice i on il.invoice_id = i.invoice_id
     10GROUP BY ar.name
     11ORDER BY money_earned DESC;

  • templates/avg_price_per_artist.html

    r611686e r4abe330  
    11<!DOCTYPE html>
    2 <html>
     2<html lang="en">
    33<head>
     4    <meta charset="UTF-8">
     5    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    46    <title>Average Track Price per Artist</title>
     7    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     8          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     9    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     10            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     11            crossorigin="anonymous"></script>
    512</head>
    6 <body>
    7 <h1>Average Track Price per Artist</h1>
    8 <ol>
    9     <h4>Name&emsp;-&emsp;Avg. track price</h4>
     13<body class="d-flex bg-light">
    1014
    11     {% for row in data %}
    12         <li>{{ row.name }}&emsp;-&emsp;{{ row.avg_price_per_track }}</li>
    13     {% endfor %}
    14 </ol>
     15{% include 'sidebar.html' %}
     16
     17<div class="container mt-5">
     18    <h1 class="mb-4 text-center">Average Track Price per Artist</h1>
     19
     20    <h4 class="mb-4 text-center">Name - Average Track Price</h4>
     21
     22    {% if not data %}
     23        <div class="alert alert-warning" role="alert">
     24            No data available.
     25        </div>
     26    {% else %}
     27        <ul class="list-group">
     28            {% for row in data %}
     29                <li class="list-group-item d-flex justify-content-between align-items-center">
     30                    <div class="col-6">
     31                        <strong>{{ row.name }}</strong>
     32                    </div>
     33                    <div class="col-6 text-end">
     34                        {% if row.avg_price_per_track == 'not enogu data' %}
     35                            <span class="badge bg-danger rounded-pill">No data available</span>
     36                        {% else %}
     37                            <span class="badge bg-success rounded-pill">${{ row.avg_price_per_track|floatformat:2 }}</span>
     38                        {% endif %}
     39                    </div>
     40                </li>
     41            {% endfor %}
     42        </ul>
     43    {% endif %}
     44</div>
     45
    1546</body>
    1647</html>
    17 

  • templates/avg_track_duration.html

    r611686e r4abe330  
    22<html>
    33<head>
    4     <title>Average track duration per artist</title>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
     9    <title>Average Track Duration per Artist</title>
    510</head>
    6 <body>
    7 <h1>Average track duration per artist</h1>
    8 <ul>
    9     {% for row in data %}
    10         <li>{{ row.artist }} - {{ row.avg }}</li>
    11     {% endfor %}
    12 </ul>
     11<body class="d-flex bg-light">
     12    {% include 'sidebar.html' %}
     13
     14    <div class="container mt-5">
     15        <h1 class="text-center mb-4">Average Track Duration per Artist</h1>
     16
     17        <div class="list-group">
     18            {% for row in data %}
     19                <a href="#" class="list-group-item list-group-item-action d-flex justify-content-between align-items-center">
     20                    <div>
     21                        <strong>{{ row.artist }}</strong>
     22                    </div>
     23                    <span class="badge bg-primary rounded-pill">{{ row.avg }}</span>
     24                </a>
     25            {% endfor %}
     26        </div>
     27    </div>
    1328</body>
    1429</html>

  • templates/genres_per_customer.html

    r611686e r4abe330  
    22<html>
    33<head>
    4     <title>Number of tracks from each genre per Customer</title>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
     9    <title>Number of Tracks from Each Genre per Customer</title>
    510</head>
    6 <body>
    7 <h1>Number of tracks from each genre per Customer</h1>
     11<body class="d-flex bg-light">
     12{% include 'sidebar.html' %}
     13<div class="container mt-5">
     14    <h1 class="mb-4 text-center">Number of Tracks from Each Genre per Customer</h1>
    815
    9 <form method="GET" action="">
    10     <label for="customer_id">Select Customer:</label>
    11     <select name="customer_id" id="customer_id">
    12         <option value="">All Customers</option>
    13         {% for customer in customers %}
    14             <option value="{{ customer.customer_id }}"
    15                     {% if request.GET.customer_id == customer.0|stringformat:'s' %}selected{% endif %}>
    16                 {{ customer.first_name }} {{ customer.last_name }}
    17             </option>
    18         {% endfor %}
    19     </select>
    20     <button type="submit">Filter</button>
    21 </form>
     16    <form method="GET" action="" class="row g-3 mb-4">
     17        <div class="col-md-4">
     18            <label for="customer_id" class="form-label">Select Customer:</label>
     19            <select name="customer_id" id="customer_id" class="form-select">
     20                <option value="">All Customers</option>
     21                {% for customer in customers %}
     22                    <option value="{{ customer.customer_id }}"
     23                            {% if request.GET.customer_id == customer.0|stringformat:'s' %}selected{% endif %}>
     24                        {{ customer.first_name }} {{ customer.last_name }}
     25                    </option>
     26                {% endfor %}
     27            </select>
     28        </div>
    2229
     30        <div class="col-md-4 d-flex align-items-end">
     31            <button type="submit" class="btn btn-primary w-100">Filter</button>
     32        </div>
     33    </form>
    2334
    24 <ol>
    25     <h4>First Name&nbsp;&nbsp;Last Name&nbsp;&nbsp;-&nbsp;&nbsp;Genre - Num of Tracks</h4>
    26     {% for row in data %}
    27         <li>
    28             {{ row.first_name }}&emsp;-&emsp;{{ row.last_name }}&emsp;-&emsp;{{ row.genre }}&emsp;-&emsp;{{ row.track_count }}</li>
    29     {% endfor %}
    30 </ol>
     35    {% if not data %}
     36        <div class="alert alert-warning" role="alert">
     37            No data available for the selected customer.
     38        </div>
     39    {% 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>
     51                {% 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>
     58                {% endfor %}
     59                </tbody>
     60            </table>
     61        </div>
     62    {% endif %}
     63</div>
    3164
    3265</body>

  • templates/invoices_per_customer_after_date.html

    r611686e r4abe330  
    99    <title>Number of Tracks per Customer</title>
    1010</head>
    11 <body class="bg-light">
    12 
     11<body class="d-flex bg-light">
     12{% include 'sidebar.html' %}
    1313<div class="container mt-5">
    1414    <h1 class="mb-4 text-center">Invoice Created by Customer</h1>

  • templates/list.html

    r611686e r4abe330  
    22<html>
    33<head>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
    49    <title>{{ heading }}</title>
    510</head>
    6 <body>
    7     <h1>{{ heading }}</h1>
    8     <ul>
    9         {% for d in data %}
    10             <li>{{ d }}</li>
    11         {% empty %}
    12             <li>No data found.</li>
    13         {% endfor %}
    14     </ul>
     11<body class="d-flex bg-light">
     12    {% include 'sidebar.html' %}
     13    <div class="container mt-5">
     14        <h1 class="text-center mb-4">{{ heading }}</h1>
     15
     16        <div class="list-group">
     17            {% for d in data %}
     18                <a href="#" class="list-group-item list-group-item-action">
     19                    {{ d }}
     20                </a>
     21            {% empty %}
     22                <div class="alert alert-warning" role="alert">
     23                    No data found.
     24                </div>
     25            {% endfor %}
     26        </div>
     27    </div>
    1528</body>
    1629</html>

  • templates/media_type_percentage.html

    r611686e r4abe330  
    22<html>
    33<head>
    4     <title>Most active customers</title>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
     9    <title>Most Active Customers</title>
    510</head>
    6 <body>
    7 <h1>Most active customers</h1>
    8 <ol>
    9     <h4>Media Type-&emsp;Num of tracks&emsp;-&emsp;Percentage</h4>
    10 
    11     {% for row in data %}
    12         <li>{{ row.name }}&emsp;-&emsp;{{ row.num_of_tracks }}&emsp;-&emsp;{{ row.percentage }}%</li>
    13     {% endfor %}
    14 </ol>
     11<body class="d-flex bg-light">
     12    {% include 'sidebar.html' %}
     13    <div class="container mt-5">
     14        <h1 class="text-center mb-4">Most Active Customers</h1>
     15        <div class="table-responsive">
     16            <table class="table table-striped">
     17                <thead>
     18                    <tr>
     19                        <th scope="col">Media Type</th>
     20                        <th scope="col">Number of Tracks</th>
     21                        <th scope="col">Percentage</th>
     22                    </tr>
     23                </thead>
     24                <tbody>
     25                    {% for row in data %}
     26                        <tr>
     27                            <td>{{ row.name }}</td>
     28                            <td>{{ row.num_of_tracks }}</td>
     29                            <td>{{ row.percentage }}%</td>
     30                        </tr>
     31                    {% endfor %}
     32                </tbody>
     33            </table>
     34        </div>
     35    </div>
    1536</body>
    1637</html>
    17 

  • templates/most_listened_genre_per_customer.html

    r611686e r4abe330  
    22<html>
    33<head>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
    49    <title>Most listened Genre per Customer</title>
    510</head>
    6 <body>
    7 <h1>Most listened Genre per Customer</h1>
    8 <ol>
    9     <h4>First Name-&emsp;Last Name&emsp;-&emsp;Genre</h4>
     11<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>
    1015
    11     {% for row in data %}
    12         <li>{{ row.first_name }}&emsp;-&emsp;{{ row.last_name }}&emsp;-&emsp;{{ row.most_listened_genre }}</li>
    13     {% endfor %}
    14 </ol>
     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.
     25                </div>
     26            {% endfor %}
     27        </div>
     28    </div>
    1529</body>
    1630</html>
    17 

  • templates/rank_list_artists.html

    r611686e r4abe330  
    22<html>
    33<head>
    4     <title>Most popular Artist</title>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
     9    <title>Artist with Highest Earnings</title>
    510</head>
    6 <body>
    7 <h1>Most popular Artist</h1>
    8 <ol>
    9     <h4>Name&emsp;-&emsp;Total invoices</h4>
     11<body class="d-flex bg-light">
     12{% include 'sidebar.html' %}
     13<div class="container mt-5">
     14    <h1 class="text-center mb-4">Artist with Highest Earnings</h1>
    1015
    11     {% for row in data %}
    12         <li>{{ row.name }}&emsp;-&emsp;{{ row.num_invoices }}</li>
    13     {% endfor %}
    14 </ol>
     16    <div class="list-group">
     17        <div class="row">
     18            <div class="col-4 font-weight-bold"><strong>Name</strong></div>
     19            <div class="col-4 font-weight-bold"><strong>Total Invoices</strong></div>
     20            <div class="col-4 font-weight-bold"><strong>Total Money Earned</strong></div>
     21        </div>
     22
     23        {% for row in data %}
     24
     25            <a href="#" class="list-group-item list-group-item-action">
     26                <div class="row">
     27                    <div class="col-4 font-weight-bold">{{ row.name }}</div>
     28                    <div class="col-4 font-weight-bold">{{ row.num_invoices }}</div>
     29                    <div class="col-4 font-weight-bold">${{ row.money_earned }}</div>
     30                </div>
     31            </a>
     32
     33        {% empty %}
     34            <div class="alert alert-warning" role="alert">
     35                No data found.
     36            </div>
     37        {% endfor %}
     38    </div>
     39</div>
    1540</body>
    1641</html>
    17 

  • templates/rank_list_most_active_customers.html

    r611686e r4abe330  
    22<html>
    33<head>
    4     <title>Most active customers</title>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
     9    <title>Most Active Customers</title>
    510</head>
    6 <body>
    7 <h1>Most active customers</h1>
    8 <ol>
    9     <h4>Name&emsp;-&emsp;Total orders&emsp;-&emsp;Total money spent</h4>
     11<body class="d-flex bg-light">
     12{% include 'sidebar.html' %}
     13<div class="container mt-5">
     14    <h1 class="text-center mb-4">Most Active Customers</h1>
    1015
    11     {% for row in data %}
    12         <li>{{ row.name }}&emsp;-&emsp;{{ row.total_orders }}&emsp;-&emsp;${{ row.total_money_spent }}</li>
    13     {% endfor %}
    14 </ol>
     16    <div class="list-group">
     17        <div class="row">
     18            <div class="col-4 font-weight-bold"><strong>Name</strong></div>
     19            <div class="col-4 font-weight-bold"><strong>Total Orders</strong></div>
     20            <div class="col-4 font-weight-bold"><strong>Total Money Spent</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.name }}</div>
     26                    <div class="col-4 font-weight-bold">{{ row.total_orders }}</div>
     27                    <div class="col-4 font-weight-bold"> ${{ row.total_money_spent }}</div>
     28                </div>
     29            </a>
     30        {% empty %}
     31            <div class="alert alert-warning" role="alert">
     32                No data found.
     33            </div>
     34        {% endfor %}
     35    </div>
     36</div>
    1537</body>
    1638</html>
    17 

  • templates/track_count_per_genre.html

    r611686e r4abe330  
    22<html>
    33<head>
    4     <title>Track count per Genre</title>
     4    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/css/bootstrap.min.css" rel="stylesheet"
     5          integrity="sha384-SgOJa3DmI69IUzQ2PVdRZhwQ+dy64/BUtbMJw1MZ8t5HZApcHrRKUc4W0kG879m7" crossorigin="anonymous">
     6    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.5/dist/js/bootstrap.bundle.min.js"
     7            integrity="sha384-k6d4wzSIapyDyv1kpU366/PK5hCdSbCRGRCMv+eplOQJWyd1fbcAu9OCUj5zNLiq"
     8            crossorigin="anonymous"></script>
     9    <title>Track Count Per Genre</title>
    510</head>
    6 <body>
    7 <h1>Track Count Per Genre</h1>
    8 <ul>
    9     {% for row in data %}
    10         <li>{{ row.genre }} - {{ row.count }} tracks</li>
    11     {% endfor %}
    12 </ul>
     11<body class="d-flex bg-light">
     12    {% include 'sidebar.html' %}
     13    <div class="container mt-5">
     14        <h1 class="text-center mb-4">Track Count Per Genre</h1>
     15
     16        <div class="list-group">
     17            {% for row in data %}
     18                <a href="#" class="list-group-item list-group-item-action">
     19                    {{ row.genre }} - {{ row.count }} tracks
     20                </a>
     21            {% empty %}
     22                <div class="alert alert-warning" role="alert">
     23                    No data available.
     24                </div>
     25            {% endfor %}
     26        </div>
     27    </div>
    1328</body>
    1429</html>
Note: See TracChangeset for help on using the changeset viewer.