main.py

import os
import sys
import csv
import threading
from sqlalchemy import text, create_engine
from tkinter import Tk, Toplevel, Label, messagebox, ttk
from tkinter.simpledialog import askinteger
from tkinter.filedialog import asksaveasfilename

DB_URL = "postgresql+psycopg2://USERID:PASSWORD@host:port/db"


report_query = """
WITH review_items AS (
    SELECT bs.record_metadata_id
    FROM sierra_view.bool_set bs
    JOIN sierra_view.bool_info bi 
        ON bs.bool_info_id = bi.id
    WHERE bi.id = :review_id
),
src AS (
  SELECT
    regexp_replace(
      btrim(COALESCE(irp.call_number, irp.call_number_norm)),
      '(\||\$)[a-zA-Z]',
      '',
      'g'
    ) AS clean_callnum,
    regexp_replace(
      irp.call_number,
      '(\||\$)[a-zA-Z]',
      '',
      'g'
    ) AS original_callnum,
    irp.item_record_id
  FROM review_items ri
  JOIN sierra_view.item_record_property irp
    ON irp.item_record_id = ri.record_metadata_id
)
SELECT 
    'b' ||
    brm.record_num::text ||
    CASE
        WHEN (
            (substring(brm.record_num::text from 7 for 1)::int * 2 +
             substring(brm.record_num::text from 6 for 1)::int * 3 +
             substring(brm.record_num::text from 5 for 1)::int * 4 +
             substring(brm.record_num::text from 4 for 1)::int * 5 +
             substring(brm.record_num::text from 3 for 1)::int * 6 +
             substring(brm.record_num::text from 2 for 1)::int * 7 +
             substring(brm.record_num::text from 1 for 1)::int * 8 
            ) % 11) = 10 THEN 'x'
        ELSE (
            (substring(brm.record_num::text from 7 for 1)::int * 2 +
             substring(brm.record_num::text from 6 for 1)::int * 3 +
             substring(brm.record_num::text from 5 for 1)::int * 4 +
             substring(brm.record_num::text from 4 for 1)::int * 5 +
             substring(brm.record_num::text from 3 for 1)::int * 6 +
             substring(brm.record_num::text from 2 for 1)::int * 7 +
             substring(brm.record_num::text from 1 for 1)::int * 8 
            ) % 11)::text
  	END AS "Bib Record",
    'i' || 
    rm.record_num::text ||
    CASE
        WHEN (
            (substring(rm.record_num::text from 7 for 1)::int * 2 +
             substring(rm.record_num::text from 6 for 1)::int * 3 +
             substring(rm.record_num::text from 5 for 1)::int * 4 +
             substring(rm.record_num::text from 4 for 1)::int * 5 +
             substring(rm.record_num::text from 3 for 1)::int * 6 +
             substring(rm.record_num::text from 2 for 1)::int * 7 +
             substring(rm.record_num::text from 1 for 1)::int * 8 
            ) % 11) = 10 THEN 'x'
        ELSE (
            (substring(rm.record_num::text from 7 for 1)::int * 2 +
             substring(rm.record_num::text from 6 for 1)::int * 3 +
             substring(rm.record_num::text from 5 for 1)::int * 4 +
             substring(rm.record_num::text from 4 for 1)::int * 5 +
             substring(rm.record_num::text from 3 for 1)::int * 6 +
             substring(rm.record_num::text from 2 for 1)::int * 7 +
             substring(rm.record_num::text from 1 for 1)::int * 8 
            ) % 11)::text
  	END AS "Item Record",
    src.original_callnum AS "Call Number", 
	brp.best_title AS "Title",
	ir.location_code AS "Location",
	ir.item_status_code AS "Item Status Code",
	ir.icode2 AS "Item Code 2",
	br.bcode3 AS "Bib Code 3",
	irp.barcode AS "Barcode"
FROM src
JOIN sierra_view.item_record ir
	ON ir.id = src.item_record_id
JOIN sierra_view.item_record_property irp
	ON irp.item_record_id = ir.id
JOIN sierra_view.record_metadata rm
	ON rm.id = ir.record_id
JOIN sierra_view.bib_record_item_record_link brl
	ON brl.item_record_id = ir.id
JOIN sierra_view.bib_record br
	ON br.id = brl.bib_record_id
JOIN sierra_view.bib_record_property brp
	ON brp.bib_record_id = br.id
JOIN sierra_view.record_metadata brm
	ON brm.id = br.id	
LEFT JOIN sierra_view.volume_record_item_record_link vi
	ON vi.item_record_id = ir.id
LEFT JOIN sierra_view.volume_record vr
	ON vi.volume_record_id = vr.id
ORDER BY 
    CASE
      WHEN substring(src.clean_callnum FROM '^\s*(\d+)') IS NOT NULL
        THEN substring(src.clean_callnum FROM '^\s*(\d+)')::BIGINT
      ELSE NULL
    END ASC NULLS LAST,
    COALESCE(
      NULLIF(substring(src.clean_callnum FROM '^\s*(\d+)'), ''),
      src.clean_callnum
    ) ASC;
"""

def on_done(success, wait_win, root, save_path=None, error=None):
    wait_win.destroy()
    if success:
        messagebox.showinfo("Success", f"Report saved to:\n{save_path}")
    else:
        messagebox.showerror("Error", error)
    root.quit()  # exit main loop --> stop running the script!

def run_report(review_id, save_path, wait_win, root):
    def task():
        try:
            engine = create_engine(DB_URL)
            with engine.connect() as connection:
                result = connection.execute(text(report_query), {"review_id": int(review_id)})
                rows = result.fetchall()

            with open(save_path, "w", newline="", encoding="utf-8-sig") as f:
                writer = csv.writer(f, quoting=csv.QUOTE_MINIMAL)
                headers = list(result.keys())
                writer.writerow(headers)

                try:
                    barcode_index = headers.index("Barcode")
                except ValueError:
                    barcode_index = None

                for row in rows:
                    row_list = list(row)
                    if barcode_index is not None and row_list[barcode_index] is not None:
                        # force excel to treat barcode as text
                        row_list[barcode_index] = f'="{row_list[barcode_index]}"'
                    writer.writerow(row_list)

            root.after(0, lambda: on_done(success=True, wait_win=wait_win, root=root, save_path=save_path))

        except Exception as e:
            error_message = str(e)
            root.after(0, lambda em=error_message: on_done(success=False, wait_win=wait_win, root=root, error=em))

    threading.Thread(target=task, daemon=True).start()

if __name__ == "__main__":
    root = Tk()
    root.withdraw()  # hide main window

    # prompt for review file number
    review_id = askinteger("Input", "Enter the Sierra review file number:", parent=root)
    if review_id is None:
        sys.exit("Cancelled by user")

    # prompt for save location
    save_path = asksaveasfilename(
        title="Save CSV file",
        defaultextension=".csv",
        filetypes=[("CSV files", "*.csv")],
        parent=root,
    )
    if not save_path:
        sys.exit("Cancelled by user")
    save_path = os.path.normpath(save_path)

    # create wait window
    wait_win = Toplevel(root)
    wait_win.title("Working")
    Label(wait_win, text="Running report, please wait...").pack(padx=20, pady=10)
    pb = ttk.Progressbar(wait_win, mode="indeterminate")
    pb.pack(padx=20, pady=10, fill="x")
    pb.start(10)

    # run report in background thread
    run_report(review_id, save_path, wait_win, root)

    root.mainloop()