longbox

commit 859f32c4e09a3a87b4e5cb6a5e65cfd6eed7c21a
parent e9c3902de43e51136b81f0488eaa142bcea9dcf8
Author: pyratebeard <root@pyratebeard.net>
Date:   Fri, 29 Jul 2022 12:17:32 +0100

change to use a single query flag. multiple search terms. search using issue or series id

Diffstat:
M
longbox
 | 
98
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----------------

1 file changed, 79 insertions(+), 19 deletions(-)
diff --git a/longbox b/longbox
@@ -17,49 +17,109 @@
 #   * import from csv
 #   * search stash
 #   * organise
+# * credentials from variables
 
 from getpass import getpass
 from mysql.connector import connect, Error
 import argparse
 from prettytable import PrettyTable
+import re
 
 # parse arguments
 parser = argparse.ArgumentParser(description = "comic book stash manager")
-parser.add_argument("-s", "--series", help = "search for series title", required = False)
-parser.add_argument("-n", "--number", help = "search for issue number", required = False, default = '%')
-parser.add_argument("-c", "--current", help = "search for current series", required = False, default = 0)
-parser.add_argument("-d", "--date", help = "search for date (use '%' as wildcard, e.g. 'aug%2022')", required = False, default = '%')
+parser.add_argument("-q", "--query", help = "search for stuff", required = False, action='store', nargs='+')
+parser.add_argument("-i", "--issue-id", help = "show specific issue id", required = False, default = None)
+parser.add_argument("-s", "--series-id", help = "list all issues in series", required = False, default = None)
+## -g --gcd ; if gcd use gcd db
+## --import
 argument = parser.parse_args()
 
-search_series = argument.series
-search_number = argument.number
-search_current = argument.current
-search_date = argument.date
+query = argument.query
+issue_id = argument.issue_id
+series_id = argument.series_id
 
-search_terms = """
-SELECT gcd_issue.id,gcd_series.name,gcd_issue.number,gcd_issue.variant_name,gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+search_series = '%'
+search_number = None
+search_date = '%'
+
+if query:
+    search_series = query[0]
+    if len(query) >= 2:
+        numregexp = re.compile(r'^#')
+        dateregexp = re.compile(r'^[0-9]')
+        for q in query[1:]:
+            if numregexp.search(q):
+                search_number = q.lstrip('#')
+            if dateregexp.search(q):
+                search_date = q
+
+if search_number:
+    search_terms = """
+    SELECT gcd_issue.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),gcd_issue.publication_date,right(gcd_issue.barcode, 5)
+    FROM gcd_series JOIN gcd_issue
+    WHERE gcd_series.name LIKE CONCAT('%', %s, '%')
+    AND (gcd_issue.number = %s OR gcd_issue.number LIKE CONCAT(%s, ' (%)'))
+    AND gcd_issue.series_id = gcd_series.id
+    AND (gcd_series.year_began < %s AND gcd_series.year_ended > %s)
+    """
+else:
+    search_terms = """
+    SELECT gcd_series.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),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 CONCAT('%', %s, '%')
+    """
+
+show_issue = """
+SELECT gcd_issue.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),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
+"""
+
+show_series = """
+SELECT gcd_issue.id,left(gcd_series.name, 50),CONCAT(gcd_series.year_began, ' - ', gcd_series.year_ended),left(gcd_issue.number, 10),left(gcd_issue.variant_name, 50),gcd_issue.publication_date,right(gcd_issue.barcode, 5)
 FROM gcd_series JOIN gcd_issue
-WHERE gcd_series.name LIKE CONCAT('%', %s, '%')
-AND gcd_issue.number LIKE CONCAT(%s, '%')
+WHERE gcd_issue.series_id = %s
 AND gcd_issue.series_id = gcd_series.id
-AND gcd_issue.publication_date LIKE CONCAT('%', %s)
 """
 
 x = PrettyTable()
-x.field_names = ['id','series','issue','variant','date','barcode']
 x.align = "l"
 
 try:
     with connect(
         host="localhost",
-        user=input("username: "),
-        password=getpass("password: "),
+        user="gcd",
+        password="gcddump",
+        #user=input("username: "),
+        #password=getpass("password: "),
         database="gcd"
     ) as connection:
         with connection.cursor() as cursor:
-            cursor.execute(search_terms, (search_series, search_number, search_date,))
-            for issue in cursor.fetchall():
-                x.add_row(issue)
+            if issue_id:
+                x.field_names = ['id','series','year','issue','variant','issue date','barcode']
+                cursor.execute(show_issue, (issue_id,))
+                for issue in cursor.fetchall():
+                    x.add_row(issue)
+            elif series_id:
+                x.field_names = ['id','series','year','issue','variant','issue date','barcode']
+                cursor.execute(show_series, (series_id,))
+                for issue in cursor.fetchall():
+                    x.add_row(issue)
+            elif search_number:
+                x.field_names = ['id','series','year','issue','variant','issue date','barcode']
+                cursor.execute(search_terms, (search_series, search_number, search_number, search_date, search_date,))
+                for issue in cursor.fetchall():
+                    x.add_row(issue)
+            else:
+                x.field_names = ['id','series','year','first issue', 'last issue']
+                cursor.execute(search_terms, (search_series,))
+                for issue in cursor.fetchall():
+                    x.add_row(issue)
             print(x)
 except Error as e:
     print(e)