Changeset d715225 for music

Timestamp:

05/07/25 11:05:41 (3 weeks ago)

Author:

ManuelTrajcev <manueltrajcev7@…>

Branches:

master

Children:

75ea229

Parents:

d1a8cb0

Message:

deleted customer log trigger

Location:

music

Files:

• __pycache__/admin.cpython-312.pyc (modified) ( previous)
• __pycache__/models.cpython-312.pyc (modified) ( previous)
• __pycache__/views.cpython-312.pyc (modified) ( previous)
• admin.py (modified) (1 diff)
• migrations/0004_deletedcustomerlog_delete_tracks_count_per_genre_and_more.py (added)
• migrations/0005_alter_deletedcustomerlog_options_and_more.py (added)
• migrations/__pycache__/0004_deletedcustomerlog_delete_tracks_count_per_genre_and_more.cpython-312.pyc (added)
• migrations/__pycache__/0005_alter_deletedcustomerlog_options_and_more.cpython-312.pyc (added)
• models.py (modified) (3 diffs)
• triggers/customer_deletion.sql (modified) (1 diff)

music/admin.py

@@ -16 +16 @@
 admin.site.register(PlaylistTrack, PlaylistTrackAdmin)
 admin.site.register(Track)
+admin.site.register(DeletedCustomerLog)

music/models.py

@@ -113 +113 @@
 class Invoice(models.Model):
     invoice_id = models.AutoField(primary_key=True)
-    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, db_column='customer_id')
+    customer = models.ForeignKey(Customer, on_delete=models.SET_NULL, db_column='customer_id', null=True)
     invoice_date = models.DateTimeField()
     billing_address = models.CharField(max_length=70, blank=True, null=True)
@@ -158 +158 @@
     class Meta:
         db_table = 'playlist_track'
-        managed = False
         unique_together = (('playlist', 'track'),)
 
@@ -170 +169 @@
     last_name = models.CharField(max_length=20)
     deleted_at = models.DateTimeField()
-
+    total_spent = models.DecimalField(max_digits=10, decimal_places=2)
+    invoice_count = models.IntegerField()
     class Meta:
         db_table = 'deleted_customer_log'
-        managed = False
 
     def __str__(self):
-        return f"{self.first_name} {self.last_name} {self.deleted_at}"
+        return f"{self.first_name} {self.last_name} - Deleted on {self.deleted_at} - Total Spent: ${self.total_spent} from {self.invoice_count} invoices"

music/triggers/customer_deletion.sql

@@ +1 @@
+DROP TRIGGER IF EXISTS trg_customer_deletion ON customer;
+DROP FUNCTION IF EXISTS customer_deletion();
+
 CREATE OR REPLACE FUNCTION customer_deletion()
 RETURNS TRIGGER AS $$
+DECLARE
+    total_spent NUMERIC(10, 2);
+    invoice_count INTEGER;
 BEGIN
-    INSERT INTO deleted_customer_log (first_name, last_name, deleted_at)
-    VALUES (OLD.first_name, OLD.last_name, NOW());
+    SELECT COALESCE(SUM(total), 0), COUNT(*)
+    INTO total_spent, invoice_count
+    FROM invoice
+    WHERE customer_id = OLD.customer_id;
+
+    INSERT INTO deleted_customer_log (
+        first_name, last_name, deleted_at, total_spent, invoice_count
+    )
+    VALUES (
+        OLD.first_name, OLD.last_name, NOW(), total_spent, invoice_count
+    );
+
     RETURN OLD;
 END;
 $$ LANGUAGE plpgsql;
 
+DROP TRIGGER IF EXISTS trg_customer_deletion ON customer;
+
 CREATE TRIGGER trg_customer_deletion
-AFTER DELETE ON customer
+BEFORE DELETE ON customer
 FOR EACH ROW
 EXECUTE FUNCTION customer_deletion();