Changeset 611686e

Timestamp:

05/01/25 11:33:45 (2 weeks ago)

Author:

ManuelTrajcev <manueltrajcev7@…>

Branches:

master

Children:

4abe330

Parents:

17ed1da

Message:

Querry - Invoice Created by Customer after a given date

Files:

• music/__pycache__/urls.cpython-312.pyc (modified) ( previous)
• music/__pycache__/views.cpython-312.pyc (modified) ( previous)
• music/urls.py (modified) (1 diff)
• music/views.py (modified) (2 diffs)
• templates/genres_per_customer.html (modified) (1 diff)
• templates/invoices_per_customer_after_date.html (added)

music/urls.py

@@ -10 +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('media-type/percentage', views.media_type_percentage, name='media_type_percentage'),

music/views.py

@@ -127 +127 @@
             data = [{'first_name': row[0], 'last_name': row[1], 'genre': row[2], 'track_count': row[3]} for row in rows]
 
-
     return render(request, 'genres_per_customer.html', {
         'customers': customers,
@@ -133 +132 @@
         'selected_customer_id': selected_customer_id,
     })
+
+
+def invoice_per_customer_after_date(request):
+    customers = Customer.objects.all()
+    selected_customer_id = request.GET.get('customer_id')
+    selected_date = request.GET.get('invoice_date', '2000-01-01')
+    data = []
+    sum = 0
+
+    if selected_customer_id:
+        if selected_date:
+            query = """
+                SELECT c.first_name, c.last_name, i.invoice_date::date, i.total
+                FROM customer c
+                JOIN invoice i ON c.customer_id = i.customer_id
+                WHERE c.customer_id = %s AND i.invoice_date > %s
+                ORDER BY i.invoice_date
+            """
+            with connection.cursor() as cursor:
+                cursor.execute(query, [selected_customer_id, selected_date])
+                rows = cursor.fetchall()
+                for r in rows:
+                    sum += r[3]
+
+                data = [{'first_name': row[0], 'last_name': row[1], 'invoice_date': row[2], 'total': row[3]} for row in
+                        rows]
+        else:
+            query = """
+                            SELECT c.first_name, c.last_name, i.invoice_date::date, i.total
+                            FROM customer c
+                            JOIN invoice i ON c.customer_id = i.customer_id
+                            WHERE c.customer_id = %s
+                            ORDER BY i.invoice_date
+                        """
+            with connection.cursor() as cursor:
+                cursor.execute(query, [selected_customer_id])
+                rows = cursor.fetchall()
+                for r in rows:
+                    sum += r[3]
+
+                data = [{'first_name': row[0], 'last_name': row[1], 'invoice_date': row[2], 'total': row[3]}
+                        for row in
+                        rows]
+
+    return render(request, 'invoices_per_customer_after_date.html', {
+        'customers': customers,
+        'total_sum': sum,
+        'data': data,
+        'selected_customer_id': selected_customer_id,
+    })

templates/genres_per_customer.html

@@ -21 +21 @@
 </form>
 
-<h4>First Name&nbsp;&nbsp;Last Name&nbsp;&nbsp;-&nbsp;&nbsp;Genre - Num of Tracks</h4>
 
 <ol>
+    <h4>First Name&nbsp;&nbsp;Last Name&nbsp;&nbsp;-&nbsp;&nbsp;Genre - Num of Tracks</h4>
     {% for row in data %}
         <li>