Index: .idea/sqldialects.xml
===================================================================
--- .idea/sqldialects.xml (revision e0242b026f70749276fa4a002d90d6c1be4d4287)
+++ .idea/sqldialects.xml (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
@@ -6,8 +6,8 @@
-
+
Index: MuiscOrganizationSystem/settings.py
===================================================================
--- MuiscOrganizationSystem/settings.py (revision e0242b026f70749276fa4a002d90d6c1be4d4287)
+++ MuiscOrganizationSystem/settings.py (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
@@ -74,4 +74,5 @@
# https://docs.djangoproject.com/en/5.1/ref/settings/#databases
+# REMOTE DM
DATABASES = {
"default": {
@@ -91,4 +92,23 @@
}
}
+
+# LOCAL DB
+# DATABASES = {
+# "default": {
+# # "ENGINE": "django.db.backends.sqlite3",
+# # "NAME": BASE_DIR / "db.sqlite3",
+# 'ENGINE': 'django.db.backends.postgresql',
+#
+# 'NAME': 'chinook',
+#
+# 'USER': 'postgres',
+#
+# 'PASSWORD': 'postgres',
+#
+# 'HOST': 'localhost',
+#
+# 'PORT': '5432',
+# }
+# }
# Password validation
Index: SQL/DDL/primary_ddl.sql
===================================================================
--- SQL/DDL/primary_ddl.sql (revision e0242b026f70749276fa4a002d90d6c1be4d4287)
+++ SQL/DDL/primary_ddl.sql (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
@@ -1,8 +1,8 @@
CREATE TABLE album
(
- album_id INT NOT NULL,
- title VARCHAR(160) NOT NULL,
- artist_id INT NOT NULL,
- CONSTRAINT album_pkey PRIMARY KEY (album_id)
+ album_id INT NOT NULL,
+ title VARCHAR(160) NOT NULL,
+ artist_id INT NOT NULL,
+ CONSTRAINT album_pkey PRIMARY KEY (album_id)
);
@@ -10,44 +10,32 @@
(
artist_id INT NOT NULL,
- name VARCHAR(120),
- CONSTRAINT artist_pkey PRIMARY KEY (artist_id)
+ name VARCHAR(120),
+ CONSTRAINT artist_pkey PRIMARY KEY (artist_id)
);
CREATE TABLE customer
(
- customer_id INT NOT NULL,
- first_name VARCHAR(40) NOT NULL,
- last_name VARCHAR(20) NOT NULL,
- company VARCHAR(80),
- address VARCHAR(70),
- city VARCHAR(40),
- state VARCHAR(40),
- country VARCHAR(40),
- postal_code VARCHAR(10),
- phone VARCHAR(24),
- fax VARCHAR(24),
- email VARCHAR(60) NOT NULL,
+ customer_id INT NOT NULL,
+ first_name VARCHAR(40) NOT NULL,
+ last_name VARCHAR(20) NOT NULL,
+ company VARCHAR(80),
support_rep_id INT,
- CONSTRAINT customer_pkey PRIMARY KEY (customer_id)
+ contact_id INT,
+ address_info_id INT,
+ CONSTRAINT customer_pkey PRIMARY KEY (customer_id)
);
CREATE TABLE employee
(
- employee_id INT NOT NULL,
- last_name VARCHAR(20) NOT NULL,
- first_name VARCHAR(20) NOT NULL,
- title VARCHAR(30),
- reports_to INT,
- birth_date TIMESTAMP,
- hire_date TIMESTAMP,
- address VARCHAR(70),
- city VARCHAR(40),
- state VARCHAR(40),
- country VARCHAR(40),
- postal_code VARCHAR(10),
- phone VARCHAR(24),
- fax VARCHAR(24),
- email VARCHAR(60),
- CONSTRAINT employee_pkey PRIMARY KEY (employee_id)
+ employee_id INT NOT NULL,
+ last_name VARCHAR(20) NOT NULL,
+ first_name VARCHAR(20) NOT NULL,
+ title VARCHAR(30),
+ reports_to INT,
+ birth_date TIMESTAMP,
+ hire_date TIMESTAMP,
+ contact_id INT,
+ address_info_id INT,
+ CONSTRAINT employee_pkey PRIMARY KEY (employee_id)
);
@@ -55,30 +43,30 @@
(
genre_id INT NOT NULL,
- name VARCHAR(120),
- CONSTRAINT genre_pkey PRIMARY KEY (genre_id)
+ name VARCHAR(120),
+ CONSTRAINT genre_pkey PRIMARY KEY (genre_id)
);
CREATE TABLE invoice
(
- invoice_id INT NOT NULL,
- customer_id INT NOT NULL,
- invoice_date TIMESTAMP NOT NULL,
- billing_address VARCHAR(70),
- billing_city VARCHAR(40),
- billing_state VARCHAR(40),
- billing_country VARCHAR(40),
+ invoice_id INT NOT NULL,
+ customer_id INT NOT NULL,
+ invoice_date TIMESTAMP NOT NULL,
+ billing_address VARCHAR(70),
+ billing_city VARCHAR(40),
+ billing_state VARCHAR(40),
+ billing_country VARCHAR(40),
billing_postal_code VARCHAR(10),
- total NUMERIC(10,2) NOT NULL,
- CONSTRAINT invoice_pkey PRIMARY KEY (invoice_id)
+ total NUMERIC(10, 2) NOT NULL,
+ CONSTRAINT invoice_pkey PRIMARY KEY (invoice_id)
);
CREATE TABLE invoice_line
(
- invoice_line_id INT NOT NULL,
- invoice_id INT NOT NULL,
- track_id INT NOT NULL,
- unit_price NUMERIC(10,2) NOT NULL,
- quantity INT NOT NULL,
- CONSTRAINT invoice_line_pkey PRIMARY KEY (invoice_line_id)
+ invoice_line_id INT NOT NULL,
+ invoice_id INT NOT NULL,
+ track_id INT NOT NULL,
+ unit_price NUMERIC(10, 2) NOT NULL,
+ quantity INT NOT NULL,
+ CONSTRAINT invoice_line_pkey PRIMARY KEY (invoice_line_id)
);
@@ -86,6 +74,6 @@
(
media_type_id INT NOT NULL,
- name VARCHAR(120),
- CONSTRAINT media_type_pkey PRIMARY KEY (media_type_id)
+ name VARCHAR(120),
+ CONSTRAINT media_type_pkey PRIMARY KEY (media_type_id)
);
@@ -93,6 +81,6 @@
(
playlist_id INT NOT NULL,
- name VARCHAR(120),
- CONSTRAINT playlist_pkey PRIMARY KEY (playlist_id)
+ name VARCHAR(120),
+ CONSTRAINT playlist_pkey PRIMARY KEY (playlist_id)
);
@@ -100,19 +88,50 @@
(
playlist_id INT NOT NULL,
- track_id INT NOT NULL,
- CONSTRAINT playlist_track_pkey PRIMARY KEY (playlist_id, track_id)
+ track_id INT NOT NULL,
+ CONSTRAINT playlist_track_pkey PRIMARY KEY (playlist_id, track_id)
);
CREATE TABLE track
(
- track_id INT NOT NULL,
- name VARCHAR(200) NOT NULL,
- album_id INT,
- media_type_id INT NOT NULL,
- genre_id INT,
- composer VARCHAR(220),
- milliseconds INT NOT NULL,
- bytes INT,
- unit_price NUMERIC(10,2) NOT NULL,
- CONSTRAINT track_pkey PRIMARY KEY (track_id)
+ track_id INT NOT NULL,
+ name VARCHAR(200) NOT NULL,
+ album_id INT,
+ media_type_id INT NOT NULL,
+ genre_id INT,
+ composer VARCHAR(220),
+ milliseconds INT NOT NULL,
+ bytes INT,
+ unit_price NUMERIC(10, 2) NOT NULL,
+ CONSTRAINT track_pkey PRIMARY KEY (track_id)
);
+
+CREATE TABLE price
+(
+ price_id SERIAL PRIMARY KEY,
+ value NUMERIC(10, 2) NOT NULL,
+ date TIMESTAMP NOT NULL,
+ track_id INT NOT NULL
+);
+
+CREATE TABLE Contact
+(
+ contact_id INT PRIMARY KEY,
+ phone VARCHAR(50),
+ fax VARCHAR(50),
+ email VARCHAR(100),
+ CONSTRAINT contact_pkey PRIMARY KEY (contact_id)
+);
+
+CREATE TABLE AddressInfo
+(
+ address_info_id SERIAL PRIMARY KEY,
+ address TEXT,
+ city TEXT,
+ state TEXT,
+ country TEXT,
+ postalcode TEXT,
+ phone TEXT,
+ fax TEXT,
+ email TEXT,
+ CONSTRAINT address_info_pkey PRIMARY KEY (address_info_id)
+);
Index: SQL/constrains/columns_rename.sql
===================================================================
--- SQL/constrains/columns_rename.sql (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
+++ SQL/constrains/columns_rename.sql (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
@@ -0,0 +1,20 @@
+ALTER TABLE artist
+RENAME COLUMN name TO artist_name;
+
+ALTER TABLE track
+RENAME COLUMN name TO track_name;
+
+ALTER TABLE price
+RENAME COLUMN date TO price_date;
+
+ALTER TABLE media_type
+RENAME COLUMN name TO media_type_name;
+
+ALTER TABLE genre
+RENAME COLUMN name TO genre_name;
+
+ALTER TABLE album
+RENAME COLUMN title TO album_title;
+
+ALTER TABLE employee
+RENAME COLUMN title TO employee_title;
Index: SQL/indices/index.sql
===================================================================
--- SQL/indices/index.sql (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
+++ SQL/indices/index.sql (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
@@ -0,0 +1,20 @@
+CREATE INDEX idx_invoice_customer_id
+ ON invoice(customer_id);
+
+CREATE INDEX idx_invoice_line_invoice_id
+ ON invoice_line(invoice_id);
+
+CREATE INDEX idx_track_genre_id
+ ON track(genre_id);
+
+CREATE INDEX idx_track_album_id
+ ON track(album_id);
+
+CREATE INDEX idx_album_artist_id
+ ON album(artist_id);
+
+CREATE INDEX idx_genre_genreid_name
+ ON genre(genre_id, name);
+
+CREATE INDEX idx_artist_artistid_name
+ ON artist(artist_id, name);
Index: music/views.py
===================================================================
--- music/views.py (revision e0242b026f70749276fa4a002d90d6c1be4d4287)
+++ music/views.py (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
@@ -7,4 +7,5 @@
from django.shortcuts import redirect
from django.utils import timezone
+
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'MuiscOrganizationSystem.settings')
django.setup()
@@ -172,13 +173,37 @@
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
- FROM customer c
- LEFT JOIN invoice i ON c.customer_id = i.customer_id
- LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
- LEFT JOIN track tr ON il.track_id = tr.track_id
- LEFT JOIN genre g ON tr.genre_id = g.genre_id
- WHERE c.customer_id = %s
- GROUP BY c.customer_id, c.first_name, c.last_name, g.genre_id, g.name
- ORDER BY c.first_name
+ WITH CustomerTracks AS (
+ SELECT
+ c.customer_id,
+ c.first_name,
+ c.last_name,
+ g.genre_id,
+ g.name AS genre_name,
+ tr.track_id
+ FROM customer c
+ LEFT JOIN invoice i ON c.customer_id = i.customer_id
+ LEFT JOIN invoice_line il ON i.invoice_id = il.invoice_id
+ LEFT JOIN track tr ON il.track_id = tr.track_id
+ LEFT JOIN genre g ON tr.genre_id = g.genre_id
+ WHERE c.customer_id = %s
+ ),
+ GenreCounts AS (
+ SELECT
+ customer_id,
+ first_name,
+ last_name,
+ genre_id,
+ genre_name,
+ COUNT(track_id) AS track_count
+ FROM CustomerTracks
+ GROUP BY customer_id, first_name, last_name, genre_id, genre_name
+ )
+ SELECT
+ first_name,
+ last_name,
+ genre_name,
+ track_count
+ FROM GenreCounts
+ ORDER BY first_name, genre_name;
"""
@@ -186,5 +211,13 @@
cursor.execute(query, [selected_customer_id])
rows = cursor.fetchall()
- data = [{'first_name': row[0], 'last_name': row[1], 'genre': row[2], 'track_count': row[3]} for row in rows]
+ 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', {
@@ -245,51 +278,68 @@
customers = Customer.objects.all()
selected_customer_id = request.GET.get('customer_id')
- selected_date = request.GET.get('invoice_date', '2000-01-01')
+ selected_date = request.GET.get('invoice_date')
+
+ # normalize empty or None date
+ if not selected_date:
+ selected_date = '2000-01-01'
+
data = []
- sum = 0
+ total_sum = 0
customer = None
if selected_customer_id:
customer = Customer.objects.filter(customer_id=selected_customer_id).first()
- if selected_date:
- query = """
- SELECT c.first_name, c.last_name, i.invoice_date::date, i.total
+
+ query = """
+ WITH CustomerInvoices AS (
+ SELECT
+ c.customer_id,
+ c.first_name,
+ c.last_name,
+ i.invoice_date::date AS invoice_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]
+ WHERE c.customer_id = %s
+ AND i.invoice_date > %s
+ ),
+ InvoiceTotals AS (
+ SELECT customer_id, SUM(total) AS total_sum
+ FROM CustomerInvoices
+ GROUP BY customer_id
+ )
+ SELECT
+ ci.first_name,
+ ci.last_name,
+ ci.invoice_date,
+ ci.total,
+ it.total_sum
+ FROM CustomerInvoices ci
+ JOIN InvoiceTotals it ON ci.customer_id = it.customer_id
+ ORDER BY ci.invoice_date;
+ """
+
+ with connection.cursor() as cursor:
+ cursor.execute(query, [selected_customer_id, selected_date])
+ rows = cursor.fetchall()
+ if rows:
+ total_sum = rows[0][4]
+ 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,
+ 'total_sum': total_sum,
'data': data,
'customer': customer,
'selected_customer_id': selected_customer_id,
+ 'selected_date': selected_date, # keep it in context if needed
})
@@ -487,4 +537,5 @@
return render(request, "create_playlist.html")
+
def list_tables():
with connection.cursor() as cursor:
@@ -496,4 +547,5 @@
""")
return [row[0] for row in cursor.fetchall()]
+
def delete_records(request):
Index: templates/home.html
===================================================================
--- templates/home.html (revision e0242b026f70749276fa4a002d90d6c1be4d4287)
+++ templates/home.html (revision a2410a982f33cc4a7d72a3d3e6658141c2a32cc9)
@@ -15,4 +15,6 @@
Home
+
+ Welcome to Music Organization System