longbox

commit 3a5ac2a48fcd179b8f5c3929205a25d3928d9554
parent 881f0831cc7939a4f409d662f1b1586ebfaf4cf2
Author: pyratebeard <root@pyratebeard.net>
Date:   Thu, 29 Sep 2022 22:33:36 +0100

build mysql statements

Diffstat:
M
longbox
 | 
70
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1 file changed, 70 insertions(+), 0 deletions(-)
diff --git a/longbox b/longbox
@@ -89,3 +89,73 @@ if query:
             if dateregexp.search(q):
                 search_date = q
 
+#░ build some mysql statements
+#░ if we are searching with an issue number
+if search_number:
+    if variants:
+        search_terms = """
+        SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), '-', coalesce(gcd_series.year_ended, 'unknown')),gcd_series.id,gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+        FROM gcd_series JOIN gcd_issue
+        WHERE gcd_series.name LIKE %(search_series)s
+        AND (gcd_issue.number = %(search_number)s OR gcd_issue.number LIKE CONCAT(%(search_number)s, ' (%)'))
+        AND gcd_issue.series_id = gcd_series.id
+        AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null))
+        """
+    else:
+        search_terms = """
+        SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), '-', coalesce(gcd_series.year_ended, 'unknown')),gcd_series.id,gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+        FROM gcd_series JOIN gcd_issue
+        WHERE gcd_series.name LIKE %(search_series)s
+        AND (gcd_issue.number = %(search_number)s OR gcd_issue.number LIKE CONCAT(%(search_number)s, ' (%)'))
+        AND gcd_issue.series_id = gcd_series.id
+        AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null))
+        GROUP BY gcd_issue.number
+        """
+else:
+#░ if we are only searching with a date
+    if search_date:
+        search_terms = """
+        SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),first.number,last.number
+        FROM gcd_series
+            INNER JOIN gcd_issue first
+            ON first.id = gcd_series.first_issue_id
+            INNER JOIN gcd_issue last
+            ON last.id = gcd_series.last_issue_id
+        WHERE gcd_series.name LIKE %(search_series)s
+        AND (gcd_series.year_began <= %(search_began)s AND (gcd_series.year_ended >= %(search_ended)s or gcd_series.year_ended is null))
+        """
+    else:
+        search_terms = """
+        SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),first.number,last.number
+        FROM gcd_series
+            INNER JOIN gcd_issue first
+            ON first.id = gcd_series.first_issue_id
+            INNER JOIN gcd_issue last
+            ON last.id = gcd_series.last_issue_id
+        WHERE gcd_series.name LIKE %s
+        """
+
+#░ if issue id given
+show_issue = """
+SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),gcd_series.id,gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+FROM gcd_series JOIN gcd_issue
+WHERE gcd_issue.id = %s
+AND gcd_issue.series_id = gcd_series.id
+"""
+
+#░ if series id given
+if variants:
+    show_series = """
+    SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+    FROM gcd_series JOIN gcd_issue
+    WHERE gcd_issue.series_id = %s
+    AND gcd_issue.series_id = gcd_series.id
+    """
+else:
+    show_series = """
+    SELECT gcd_issue.id,left(gcd_series.name, 50),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),CONCAT(coalesce(gcd_series.year_began, 'unknown'), ' - ', coalesce(gcd_series.year_ended, 'unknown')),gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+    FROM gcd_series JOIN gcd_issue
+    WHERE gcd_issue.series_id = %s
+    AND gcd_issue.series_id = gcd_series.id
+    GROUP BY gcd_issue.number
+    """