# -*- coding: utf-8 -*-
__license__   = 'GPL v3'
__copyright__ = '2015,2016,2017,2018,2019,2020,2021,2022,2023 DaltonST 2024 DJG'
__my_version__ = "2.0.1"  # Add back self.detect_isbn_changes_to_refresh

from qt.core import QMenu, QDialog, QIcon, QAction, QSize, QWidget, QFileDialog, QApplication

import os, sys, subprocess
import apsw
import csv
from functools import partial
import re
import time
from time import sleep

from calibre import isbytestring, entity_to_unicode
from calibre.constants import filesystem_encoding, iswindows, DEBUG
from calibre.ebooks.metadata.book.base import Metadata
from calibre.gui2 import error_dialog, info_dialog, question_dialog, FileDialog
from calibre.gui2.actions import InterfaceAction
from calibre.utils.config import JSONConfig

from polyglot.builtins import as_unicode, iteritems, map, unicode_type

SOURCE_TYPE_VIAF_AUTHOR_ID = "http://viaf.org/viaf/[REFERENCENUMBER]/"  # get isni and lccn using viaf
SOURCE_TYPE_GOOGLE_BOOK_SEARCH = "https://books.google.com/books?isbn="

from calibre_plugins.library_codes.config import prefs, ConfigWidget
from calibre_plugins.library_codes.classify_web_service_webscraping import oclc_classify_webscraping_stdnbr, oclc_classify_webscraping_author_title
from calibre_plugins.library_codes.library_codes_webscraping import library_codes_generic_webscraping
from calibre_plugins.library_codes.common_utils import set_plugin_icon_resources, get_icon, create_menu_action_unique
from calibre_plugins.library_codes.library_codes_dialog import LibraryCodesDialog
from calibre_plugins.library_codes.library_codes_pdftohtml import pdftohtml_extract_pdf_issn

## Bookmark
from urllib.parse import quote_plus
import unicodedata

PLUGIN_ICONS = ['images/librarycodesicon.png','images/wrench-hammer.png','images/change.png','images/minus.png','images/plus.png','images/ddclccicon.png','images/swap.png','images/import.png','images/genre.png','images/piechart.png']

S_FALSE = "False"
S_TRUE = "True"

STDNR = "STDNR"
#~ ISBN = "ISBN"
AUTHORTITLE = "AUTHORTITLE"
AUTHORTITLE_MOD = "AUTHORTITLE_MOD"

### Bookmark (modify title and author custom menu)
from PyQt5.QtWidgets import QDialog, QVBoxLayout, QHBoxLayout, QLabel, QLineEdit, QPushButton, QMessageBox

class TitleAuthorDialog(QDialog):
    def __init__(self, title='', author=''):
        super().__init__()
        self.setWindowTitle('Edit Title and Author')
        self.resize(400, 150)

        self.title_input = QLineEdit(title)
        self.author_input = QLineEdit(author)

        layout = QVBoxLayout()

        # Preserve full, unedited title and author
        self.full_title = title
        self.full_author = author

        title_layout = QHBoxLayout()
        title_layout.addWidget(QLabel('Title:'))
        title_layout.addWidget(self.title_input)

        author_layout = QHBoxLayout()
        author_layout.addWidget(QLabel('Author:'))
        author_layout.addWidget(self.author_input)

        layout.addLayout(title_layout)
        layout.addLayout(author_layout)

        button_layout = QHBoxLayout()
        ok_button = QPushButton('OK')
        cancel_button = QPushButton('Cancel')
        ok_button.clicked.connect(self.accept)
        cancel_button.clicked.connect(self.cancel_clicked)
        button_layout.addWidget(ok_button)
        button_layout.addWidget(cancel_button)

        layout.addLayout(button_layout)
        self.setLayout(layout)

    def get_values(self):
        return self.title_input.text(), self.author_input.text()

    def cancel_clicked(self):
        QMessageBox.information(self, "Cancelled", "Operation was cancelled by the user.")
        # Optional: raise an exception or set a flag to stop further execution
        self.done(0)  # This closes the dialog like reject(), but you can customize behavior afterwards

#--------------------------------------------------------------------------------------------
class ActionLibraryCodes(InterfaceAction):

    name = 'Library Codes'
    action_spec = ('LC','images/librarycodesicon.png', "Library Codes:  Derive Library of Congress Code, Dewey Decimal Code, FAST Tags, Identifiers (OCLC-OWI, OCLC-WORLDCAT, and VIAF-AUTHOR-ID) Using ISBN or ISSN", None)
    action_type = 'global'
    accepts_drops = False
    auto_repeat = False
    priority = 9
    popup_type = 1

    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def genesis(self):
        self.maingui = self.gui
        icon_resources = self.load_resources(PLUGIN_ICONS)
        set_plugin_icon_resources(self.name, icon_resources )
        self.menu = QMenu(self.gui)
        self.build_menus(self.gui)
        self.qaction.setMenu(self.menu)
        self.qaction.setIcon(get_icon(PLUGIN_ICONS[0]))
        self.qaction.triggered.connect(self.derive_from_isbn_issn_stdnr)
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def initialization_complete(self):
        self.maingui = self.gui
        self.guidb = self.maingui.library_view.model().db
        self.selected_books_list = []
        must_save = False
        for k,v in iteritems(prefs.defaults):
            if k in prefs:
                pass
            else:
                prefs[k] = v
                must_save = True
        #END FOR
        if must_save:
            prefs
        #~ ----------------------------------------------------------------
        #~ Convert prefs keys to be more precise
        #~ ----------------------------------------------------------------
        if 'OCLC_IS_ACTIVE' in prefs:
            prefs['OCLC_OWI_IS_ACTIVE'] = prefs['OCLC_IS_ACTIVE']
            prefs
            del prefs['OCLC_IS_ACTIVE']
            prefs
        if 'OCLC_IDENTIFIER' in prefs:
            prefs['OCLC_OWI_IDENTIFIER'] = prefs['OCLC_IDENTIFIER']
            prefs
            del prefs['OCLC_IDENTIFIER']
            prefs
        #~ ----------------------------------------------------------------
        #~ Delete prefs keys that are deprecated
        #~ ----------------------------------------------------------------
        if 'LCEAD' in prefs:
            del prefs['LCEAD']
            prefs
        if 'LCEAD_IS_ACTIVE' in prefs:
            del prefs['LCEAD_IS_ACTIVE']
            prefs
        #~ ----------------------------------------------------------------
        #~ Synchronize OCLC_OWI Prefs
        #~ ----------------------------------------------------------------
        if prefs['OCLC_OWI_IS_ACTIVE'] == "True":
            prefs['OCLC_OWI_IDENTIFIER'] = "True"
            prefs
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def library_changed(self,guidb):
        self.guidb = self.maingui.library_view.model().db
        self.qaction.setIcon(get_icon(PLUGIN_ICONS[0]))
        try:
            self.librarycodesdialog.close()
        except:
            pass
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def build_menus(self,gui):

        lc_menu = self.menu
        lc_menu.clear()

        lc_menu.setTearOffEnabled(True)
        lc_menu.setWindowTitle('Library Codes Menu')

        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        unique_name = "LC:Configuration && ToolTip-Instructions"
        create_menu_action_unique(self, lc_menu, 'Configuration && ToolTip-Instructions', 'images/wrench-hammer.png',
                              triggered=partial(self.init_librarycodesdialog),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        unique_name = "LC:Using ISBN/ISSN/OCLC-WI:  Derive DDC/LCC/FAST/OCLC-OWI Custom Columns [Selected Books]"
        create_menu_action_unique(self, lc_menu, 'Using ISBN/ISSN/OCLC-WI:  Derive DDC/LCC/FAST/OCLC-OWI Custom Columns [Selected Books]', 'images/ddclccicon.png',
                              triggered=partial(self.derive_from_isbn_issn_stdnr),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        unique_name = "LC:Using Author/Title:  Derive OCLC-WI [Selected Books]"
        create_menu_action_unique(self, lc_menu, 'Using Author/Title:  Derive DDC/LCC/FAST [Selected Books]', 'images/ddclccicon.png',
                              triggered=partial(self.derive_from_author_title),unique_name=unique_name, favourites_menu_unique_name=unique_name)

        ### Bookmark
        unique_name = "LC:Using Author/Title (Tweak Search):  Derive OCLC-WI [Selected Books]"
        create_menu_action_unique(self, lc_menu, 'Using Author/Title (Modified Search):  Derive DDC/LCC/FAST [Selected Books]', 'images/ddclccicon.png',
                              triggered=partial(self.derive_from_author_title, True),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        unique_name = "LC:Find Alternatives for a Non-Responsive ISBN [Selected Single Book]"
        create_menu_action_unique(self, lc_menu, 'Find Alternatives for a Non-Responsive ISBN [Selected Single Book]', 'images/swap.png',
                              triggered=partial(self.find_alternatives_for_nonresponsive_isbn),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        unique_name = "LC:Scrub ISBNs [Selected Books]"
        create_menu_action_unique(self, lc_menu, 'Scrub ISBNs [Selected Books]', 'images/change.png',
                              triggered=partial(self.scrub_isbn),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        unique_name = "LC:Convert ISBN-10 to ISBN-13 [All Books]"
        create_menu_action_unique(self, lc_menu, 'Convert ISBN-10 to ISBN-13 [All Books]', 'images/change.png',
                              triggered=partial(self.scrub_isbns),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        unique_name = "LC:Extract ISSNs from Magazine/Periodical PDFs [Selected Books]"
        create_menu_action_unique(self, lc_menu, 'Extract ISSNs from Magazine/Periodical PDFs [Selected Books]', 'images/plus.png',
                              triggered=partial(self.extract_issn_from_pdf_control),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        #BEGIN submenu m1 --------------------------------------------------------------------------
        self.m1 = QMenu(_('[Menu] DDC/LCC to Genre Mapping'))
        self.m1_action = lc_menu.addMenu(self.m1)
        self.m1.setIcon(get_icon('images/genre.png'))

        self.m1.setTearOffEnabled(True)
        self.m1.setWindowTitle('DDC/LCC to Custom Column Mapping')

        self.m1.addSeparator()
        unique_name = "LC:Update Genre using Table _lc_genre_mapping [Selected Books]"
        create_menu_action_unique(self, self.m1, 'Update Genre using Table _lc_genre_mapping [Selected Books]', 'images/genre.png',
                              triggered=partial(self.update_genre_control),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        self.m1.addSeparator()
        create_menu_action_unique(self, self.m1, ' ', ' ',
                              triggered=None)
        self.m1.addSeparator()
        unique_name = "LC:Import Add/Change .CSV file to Table _lc_genre_mapping"
        create_menu_action_unique(self, self.m1, 'Import Add/Change .CSV file to Table _lc_genre_mapping', 'images/import.png',
                              triggered=partial(self.import_csv_mappings),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        self.m1.addSeparator()
        create_menu_action_unique(self, self.m1, ' ', ' ',
                              triggered=None)

        m1tool_tip = "<p style='white-space:wrap'>Update any regular Text custom column, such as #genre, by mapping a DDC or LCC to a value, such as a Genre, in Table _lc_genre_mapping."
        m1tool_tip = m1tool_tip + "<br><br>LC comes pre-loaded with the mapping table.  However, you may add or change (but not delete) as you wish by importing a CSV file.\
                                                     <br><br>The CSV file must be a typical UTF-8 encoded text file that is comma-separated, and with both columns enclosed in quotes.  \
                                                     <br><br>The CSV file must have exactly 2 textual columns.  Column 1 must be a library_code (either DDC or LCC), and Column 2 must be its mapped genre."
        m1tool_tip = m1tool_tip + '<br><br>Example of a CSV row displayed in a text editor to show the comma delimiter and the enclosing quotes: ' + '  "001.944","Monsters (unexplained phenomena)"  '
        m1tool_tip = m1tool_tip + "<br> "
        self.m1.setToolTip(m1tool_tip)
        #END submenu m1 --------------------------------------------------------------------------
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)
        lc_menu.addSeparator()
        unique_name = "LC:Show DDC Top 20 Pie Chart [NN]"
        create_menu_action_unique(self, lc_menu, 'Show DDC Top 20 Pie Chart [NN]', 'images/piechart.png',
                              triggered=partial(self.ddc_pie_chart_1),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        unique_name = "LC:Show DDC Top 20 Pie Chart [NNN]"
        create_menu_action_unique(self, lc_menu, 'Show DDC Top 20 Pie Chart [NNN]', 'images/piechart.png',
                              triggered=partial(self.ddc_pie_chart_2),unique_name=unique_name, favourites_menu_unique_name=unique_name)
        lc_menu.addSeparator()
        create_menu_action_unique(self, lc_menu, ' ', ' ',
                              triggered=None)

        lc_menu.setToolTip("<p style='white-space:wrap'>[1] Use any available ISBN, ISSN, or OCLC-WI Identifier to derive all other Library Codes Identifiers and Metadata, such as DDC and LC.\
                                                                                        <br><br>[2] FAST Tags are derived simultaneously from the identical source as DDC and LCC.\
                                                                                        <br><br>[3] OCLC-WI is derived solely using a book's Author & Title in the indicated LC Menu Item.\
                                                                                        <br><br>After deriving the OCLC-WI for a book having no ISBN and no ISSN, \
                                                                                        use the ISSBN/ISSN/OCLC-WI derivation LC Menu Item to \
                                                                                        derive the same Identifiers and Metadata as if a book had an ISBN or ISSN.  ")

        self.maingui.keyboard.finalize()
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def init_librarycodesdialog(self):
        self.guidb = self.maingui.library_view.model().db
        try:
            self.librarycodesdialog.close()
        except:
            pass

        self.qaction.setIcon(get_icon(PLUGIN_ICONS[0]))
        self.librarycodesdialog = LibraryCodesDialog(self.maingui,self.qaction.icon(),self.guidb,self.plugin_path,self.ui_exit,self.action_type)
        self.librarycodesdialog.show()

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        self.create_lc_genre_mapping_table(my_db,my_cursor)   # ensures table _lc_genre_mapping exists in current library so user can manually update it in a sqlite tool...
        my_db.close()
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def ui_exit(self):
        self.librarycodesdialog.close()
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def derive_from_isbn_issn_stdnr(self):
        self.guidb = self.maingui.library_view.model().db
        if question_dialog(self.gui, "Library Codes", "Derive Library Codes from ISBN/ISSN/OCLC-WI for Selected Books?"):
            msg = 'LC is deriving Library Codes from ISBN/ISSN/OCLC-WI: Wait'
            self.maingui.status_bar.show_message(msg)
            QApplication.instance().processEvents()
            source = "STDNR"
            self.derive_all_library_codes_control(source)
        else:
            return
    #-----------------------------------------------------------------------------------------
    def derive_from_author_title(self, mod=False):
        self.guidb = self.maingui.library_view.model().db
        if question_dialog(self.gui, "Library Codes", "Derive Library Codes from Author/Title for Selected Books?"):
            msg = 'LC is deriving Library Codes from Author/Title: Wait'
            self.maingui.status_bar.show_message(msg)
            QApplication.instance().processEvents()
            ### Bookmark
            if mod == True:
                source = "AUTHORTITLE_MOD"
            else:
                source = "AUTHORTITLE"
            self.derive_all_library_codes_control(source)
        else:
            return
    #-----------------------------------------------------------------------------------------
    def derive_all_library_codes_control(self,source):

        self.guidb = self.maingui.library_view.model().db

        number_active = 0

        if prefs['DDC_IS_ACTIVE'] == as_unicode(S_TRUE):
            number_active = number_active + 1
        if prefs['LCC_IS_ACTIVE'] == as_unicode(S_TRUE):
            number_active = number_active + 1
        if prefs['FAST_IS_ACTIVE'] == as_unicode(S_TRUE):
            number_active = number_active + 1
        if prefs['OCLC_OWI_IS_ACTIVE'] == as_unicode(S_TRUE):
            number_active = number_active + 1

        if number_active == 0:
            return error_dialog(self.gui, _('Derive Library Codes'),_('No Active Library Codes.'), show=True)

        self.ddc_name = prefs['DDC'].replace("#","").strip()
        self.lcc_name = prefs['LCC'].replace("#","").strip()
        self.fast_name = prefs['FAST'].replace("#","").strip()
        self.oclc_owi_name = prefs['OCLC_OWI'].replace("#","").strip()

        if prefs['OCLC_OWI_IDENTIFIER'] == as_unicode(S_TRUE):   #but if you want oclc_owi, you get all of the available identifiers.
            self.oclc_owi_identifier_is_desired = True
        else:
            self.oclc_owi_identifier_is_desired = False

        #-------------------------------------
        # get selected books
        #-------------------------------------
        self.selected_books_list = self.get_selected_books()
        n_books = len(self.selected_books_list)
        if n_books == 0:
             return error_dialog(self.gui, _('Derive Library Codes'),_('No Books Were Selected.'), show=True)
        self.selected_books_list.sort()
        #-------------------------------------
        # process selected books
        #-------------------------------------
        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-------------------------------------
        #-------------------------------------
        if source == STDNR:
            self.populate_available_metadata_using_classify_webscraping_stdnr(my_db,my_cursor,source)
        ### Bookmark
        elif source == AUTHORTITLE or source == AUTHORTITLE_MOD:
            self.populate_available_metadata_using_classify_webscraping_author_title(my_db,my_cursor,source)
        #-------------------------------------
        #-------------------------------------
        self.mark_selected_books()
        #-------------------------------------
        del self.selected_books_list
        #-------------------------------------
        self.copy_legacy_oclc_identifier_into_oclc_worldcat_identifier()
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    def populate_available_metadata_using_classify_webscraping_stdnr(self,my_db,my_cursor,source):

        ddc_exists = True
        lcc_exists = True
        self.data_was_found = False
        self.all_identifiers_should_be_kept_override = False

        n = len(self.ddc_name)
        if self.ddc_name == "none" or self.ddc_name == "":
            ddc_exists = False
        n = len(self.lcc_name)
        if self.lcc_name == "none" or self.lcc_name == "" :
            lcc_exists = False

        if ((not ddc_exists) and (not lcc_exists)):
            self.lc_derivation_messages_label.setText("Cannot Update Library Codes for Selected Books")
            return error_dialog(self.gui, _('Cannot Update Library Codes for Selected Books'),
                                                          _('You Must Configure the Lookup Name for at least one of DDC or LCC.'), show=True)

        # custom column value updated:
        ddc_dict = {}
        lcc_dict = {}
        fast_dict = {}

        # custom column value updated, or is a identifier:  #but may use a composite cc to show the identifier
        oclc_owi_dict = {}

        # identifier only:  #but may use a composite cc to show the identifier
        oclc_worldcat_dict = {}
        oclc_wi_dict = {}
        viaf_author_id_dict = {}

        #--------------------------------
        isbn_dict = self.get_isbn_identifiers_for_selected_books(my_db,my_cursor)
        oclc_wi_dict = self.get_oclc_wi_identifiers_for_selected_books(my_db,my_cursor)

        my_db.close()

        if len(isbn_dict) == 0 and len(oclc_wi_dict) == 0:
            return error_dialog(self.gui, _('Cannot Update DDC, LCC or OCLC for Selected Books'),
                                                      _('An ISBN/ISSN/OCLC-WI for at least one of the selected books, plus DDC and/or LCC and/or OCLC-OWI Custom Columns, must exist in this library order to perform this action.'), show=True)

        stdnr_dict = {}

        for book in self.selected_books_list:
            book = str(book)
            if book in oclc_wi_dict:
               stdnr_dict[book] = oclc_wi_dict[book]
               if DEBUG:
                    print("Book has an OCLC-WI Standard Number: ", str(book), oclc_wi_dict[book])
                    if book in isbn_dict:
                        print("Book also has an ISBN, but it has been *ignored* since the book also has an OCLC-WI:  Its ISBN", isbn_dict[book])
            elif book in isbn_dict:
               stdnr_dict[book] = isbn_dict[book]
               if DEBUG: print("Book has an ISBN: ", str(book), isbn_dict[book])
            else:
                if DEBUG: print("Book has neither an ISBN or OCLC-WI Standard Number; skipping: ", str(book))
                continue
        #END FOR

        #---------------------------------
        #---------------------------------

        book_ids_list = []
        redo_book_ids_list = []
        final_book_ids_list = []

        self.ddc_name = '#' + self.ddc_name
        self.lcc_name = '#' + self.lcc_name
        self.fast_name = '#' + self.fast_name
        self.oclc_owi_name = '#' + self.oclc_owi_name

        self.guidb = self.maingui.library_view.model().db
        db = self.guidb.new_api

        n_total_books = len(self.selected_books_list)
        if n_total_books == 0:
            info_dialog(self.gui, "Library Codes", "No Selected Books.").show()
            return

        QApplication.instance().processEvents()

        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------
        id_map = {}
        n_progress = 0
        n_sleep_counter = 0
        for book in self.selected_books_list:
            s_book = as_unicode(book)
            n_book = int(book)
            self.data_was_found = False
            try:
                if not s_book in stdnr_dict:
                    continue
                stdnr = stdnr_dict[s_book]
                if stdnr:
                    if DEBUG: print("ISBN/ISSN/OCLC-WI:  value being used: ", stdnr)
                    QApplication.instance().processEvents()
                    n_progress = n_progress + 1
                    n_sleep_counter = n_sleep_counter + 1
                    if n_sleep_counter == 10:
                        sleep(0.5)
                        n_sleep_counter = 0
                        sleep(0)
                    msg = 'LC is deriving DDC/LCC from ISBN/ISSN/OCLC-WI:  ' + as_unicode(n_progress)
                    if DEBUG: print(msg)
                    self.maingui.status_bar.show_message(msg)
                    QApplication.instance().processEvents()
                    paramtype = "stdnbr"           # "stdnbr"  includes isbn, issn, upc, owi, wi, etc.
                    paramvalue = stdnr                  # isbn OR issn (from Calibre) OR oclc-wi (from here via immediate re-do (see re-do below), or from LC webscraping using only Author/Title)
                    QApplication.instance().processEvents()
                    ddc_return, lcc_return, fast_return_list, oclc_owi_return, viaf_author_id_return, oclc_worldcat_return, oclc_wi_return = oclc_classify_webscraping_stdnbr(paramtype,paramvalue)
                    #~ --------------------
                    #~ --------------------
                    QApplication.instance().processEvents()
                    self.data_was_found = False
                    if ddc_return != "NONE":
                        ddc_dict[s_book] = ddc_return
                        self.data_was_found = True
                    if lcc_return != "NONE":
                        lcc_dict[s_book] = lcc_return
                        self.data_was_found = True
                    if isinstance(fast_return_list,list):
                        if len(fast_return_list) > 0:
                            fast_dict[s_book] = fast_return_list
                            self.data_was_found = True
                    if oclc_owi_return != "NONE":
                        oclc_owi_dict[s_book] = oclc_owi_return
                        self.data_was_found = True
                    if oclc_worldcat_return != "NONE":
                        oclc_worldcat_dict[s_book] = oclc_worldcat_return
                        self.data_was_found = True
                    if viaf_author_id_return != "NONE":
                        viaf_author_id_dict[s_book] = viaf_author_id_return
                        self.data_was_found = True
                    #~ -------------------------------------------------------------------
                    #~ Check for Multiple Response & Need to Re-do
                    #~ -------------------------------------------------------------------
                    if oclc_wi_return != "NONE":
                        oclc_wi_dict[s_book] = oclc_wi_return
                        if not self.data_was_found:
                            redo_book_ids_list.append(n_book)
                            all_identifiers_should_be_kept = True
                    #~ -------------------------------------------------------------------
                    if self.data_was_found:
                        book_ids_list.append(n_book)
                        if DEBUG: print("appending book with downloaded data to book_ids_list: ", as_unicode(n_book))
                    del ddc_return
                    del lcc_return
                    del oclc_owi_return
                    del oclc_worldcat_return
                    del oclc_wi_return
                    del viaf_author_id_return
                else:
                    continue
            except Exception as e:
                if DEBUG: print("Exception within using ISBN to call oclc_classify_webscraping_stdnbr: ", as_unicode(e))
                continue
                #------------------------------------------------------------------
        #END FOR

        #~ -------------------------------------------------------------------
        #~ Multiple Response Re-do Using OCLC-WI
        #~ -------------------------------------------------------------------
        if len(redo_book_ids_list) > 0:
            self.all_identifiers_should_be_kept_override = True
            n_progress = 0
            n_sleep_counter = 0
            for book in redo_book_ids_list:
                s_book = as_unicode(book)
                n_book = int(book)
                self.data_was_found = False
                try:
                    if not s_book in oclc_wi_dict:
                        continue
                    stdnr = oclc_wi_dict[s_book]  #redo using oclc-wi
                    if stdnr:
                        if DEBUG: print("Multiple Response Re-do Using OCLC-WI:  value being used: ", stdnr)
                        QApplication.instance().processEvents()
                        n_progress = n_progress + 1
                        n_sleep_counter = n_sleep_counter + 1
                        if n_sleep_counter == 10:
                            sleep(0.5)
                            n_sleep_counter = 0
                            sleep(0)
                        msg = 'LC is deriving DDC/LCC from OCLC-WI:  ' + as_unicode(n_progress)
                        if DEBUG: print(msg)
                        self.maingui.status_bar.show_message(msg)
                        QApplication.instance().processEvents()
                        paramtype = "stdnbr"           # "stdnbr"  includes isbn, issn, upc, owi, wi, etc.
                        paramvalue = stdnr                 # isbn OR issn (from Calibre) OR oclc-wi (from here via immediate re-do, or from LC webscraping using only Author/Title)
                        QApplication.instance().processEvents()
                        ddc_return, lcc_return, fast_return_list, oclc_owi_return, viaf_author_id_return, oclc_worldcat_return, oclc_wi_return = oclc_classify_webscraping_stdnbr(paramtype,paramvalue)
                        #~ --------------------
                        #~ --------------------
                        QApplication.instance().processEvents()
                        self.data_was_found = False
                        if ddc_return != "NONE":
                            ddc_dict[s_book] = ddc_return
                            self.data_was_found = True
                        if lcc_return != "NONE":
                            lcc_dict[s_book] = lcc_return
                            self.data_was_found = True
                        if isinstance(fast_return_list,list):
                            if len(fast_return_list) > 0:
                                fast_dict[s_book] = fast_return_list
                                self.data_was_found = True
                        if oclc_owi_return != "NONE":
                            oclc_owi_dict[s_book] = oclc_owi_return
                            self.data_was_found = True
                        if oclc_worldcat_return != "NONE":
                            oclc_worldcat_dict[s_book] = oclc_worldcat_return
                            self.data_was_found = True
                        if viaf_author_id_return != "NONE":
                            viaf_author_id_dict[s_book] = viaf_author_id_return
                            self.data_was_found = True
                        #~ ----- oclc_wi_dict was populated the 1st time to enable this re-do for this book, so it still exists.
                        if self.data_was_found:
                            book_ids_list.append(n_book)
                            if DEBUG: print("Multiple Response Re-do Using OCLC-WI: appending book with downloaded data to book_ids_list: ", as_unicode(n_book))
                        del ddc_return
                        del lcc_return
                        del oclc_owi_return
                        del oclc_worldcat_return
                        del oclc_wi_return
                        del viaf_author_id_return
                    else:
                        continue
                except Exception as e:
                    if DEBUG: print("Exception within Multiple Response Re-do Using OCLC-WI: ", as_unicode(e))
                    continue
                #------------------------------------------------------------------
            #END FOR
        #END IF

        QApplication.instance().processEvents()

        nd = len(ddc_dict)
        nl = len(lcc_dict)
        noclc = len(oclc_owi_dict)
        nwi = len(oclc_wi_dict)
        if nd == 0 and nl == 0 and noclc == 0 and nwi == 0:
            info_dialog(self.gui, "Library Codes", "Nothing Found For Any Selected Book.  Visit:  http://classify.oclc.org/classify2/  ").show()
            return

        custom_columns = self.maingui.current_db.field_metadata.custom_field_metadata()

        n = len(book_ids_list)
        if n == 0:
            if DEBUG: print("no books in book_ids_list; returning...")
            return

        id_map = {}
        for row in book_ids_list:
            data_changed = False
            s_book = as_unicode(row)
            n_book = int(row)
            mi = Metadata(_('Unknown'))
            if prefs['DDC_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    custcol1 = custom_columns[self.ddc_name]         #  custcol = custom_columns["#ddc"]
                    val = ddc_dict[s_book]
                    if not isinstance(val,list):
                        custcol1['#value#'] = as_unicode(ddc_dict[s_book])
                        mi.set_user_metadata(self.ddc_name, custcol1)   # class Metadata in  src>calibre>ebooks>metadata>book>base.py
                        data_changed = True
                    else:
                        if DEBUG: print("error: ddc_dict[s_book] val was a list, but should not be...")
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in ddc_dict: book id: ", as_unicode(e))
                    pass
            if prefs['LCC_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    val = lcc_dict[s_book]
                    if not isinstance(val,list):
                        custcol2 = custom_columns[self.lcc_name]
                        custcol2['#value#'] = as_unicode(lcc_dict[s_book])
                        mi.set_user_metadata(self.lcc_name, custcol2)
                        data_changed = True
                    else:
                        if DEBUG: print("error: lcc_dict[s_book] val was a list, but should not be...")
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in lcc_dict: book id: ", as_unicode(e))
                    pass

            if prefs['FAST_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    custcol4 = custom_columns[self.fast_name]
                    fast_list = fast_dict[s_book]
                    tmp_list = []
                    for new_tag in fast_list:
                        if not isinstance(new_tag,list):
                            if not isinstance(new_tag,unicode_type):
                                new_tag = as_unicode(new_tag)
                            if new_tag.count(",") > 0:
                                new_tag = new_tag.replace(",",";")  # so Calibre will not split it into 2 tags.
                            tmp_list.append(new_tag)
                        else:
                            if DEBUG: print("error: FAST new_tag was a list, but should not be...")
                    #END FOR
                    custcol4['#value#'] = tmp_list
                    mi.set_user_metadata(self.fast_name, custcol4)     # class Metadata in  src>calibre>ebooks>metadata>book>base.py
                    data_changed = True
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in fast_dict: book id: ", as_unicode(e))
                    pass

            if prefs['OCLC_OWI_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    val = oclc_owi_dict[s_book]
                    if not isinstance(val,list):
                        custcol3 = custom_columns[self.oclc_owi_name]
                        custcol3['#value#'] = as_unicode(oclc_owi_dict[s_book])
                        mi.set_user_metadata(self.oclc_owi_name, custcol3)
                        data_changed = True
                    else:
                        if DEBUG: print("error: oclc_owi_dict[s_book] val was a list, but should not be...")
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in oclc_owi_dict: book id: ", as_unicode(e))
                    pass

            #~ ------ multi-return yields only a oclc_wi_dict, and nothing else; that is an Identifier, not a custom column...

            if data_changed:
                id_map[n_book] = mi
                final_book_ids_list.append(n_book)

            if len(id_map) == 0:
                self.data_was_found = False
            else:
                self.data_was_found = True

            if not self.data_was_found:
                return info_dialog(self.gui, "Library Codes", "Nothing Updated For Any Books, But There Were No Errors.  Visit: http://classify.oclc.org/classify2/ ").show()

            final_book_ids_list = list(set(final_book_ids_list))
            final_book_ids_list.sort()

        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------
        #~ Perform the following before edit_metadata_action to avoid db lock errors.
        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------
        if len(final_book_ids_list) > 0:

            if self.all_identifiers_should_be_kept_override:  # required for future multiple responses regardless of what the user may have otherwise customized.
                self.oclc_owi_identifier_is_desired = True
                prefs['OCLC_OWI_IDENTIFIER'] == as_unicode(S_TRUE)
                prefs

            if self.oclc_owi_identifier_is_desired:  #if you get one, you get them all.

                if len(oclc_owi_dict) > 0:
                    sleep(0)
                    self.add_oclc_owi_identifiers(oclc_owi_dict,final_book_ids_list)
                    QApplication.instance().processEvents()

                if len(oclc_worldcat_dict) > 0:
                    sleep(0)
                    self.add_oclc_worldcat_identifiers(oclc_worldcat_dict,final_book_ids_list)
                    QApplication.instance().processEvents()

                if len(oclc_wi_dict) > 0:
                    sleep(0)
                    self.add_oclc_wi_identifiers(oclc_wi_dict,final_book_ids_list)
                    QApplication.instance().processEvents()

                if len(viaf_author_id_dict) > 0:
                    sleep(0)
                    msg = "LC is adding 'viaf_author_identifiers' to metadata.db"
                    self.maingui.status_bar.show_message(msg)
                    self.add_viaf_author_id_identifiers(viaf_author_id_dict,final_book_ids_list)
                    msg = "LC is downloading identifiers 'isni' and 'lccn' using 'viaf_author_identifier'"
                    self.maingui.status_bar.show_message(msg)
                    QApplication.instance().processEvents()
                    final_results_dict = library_codes_generic_webscraping(SOURCE_TYPE_VIAF_AUTHOR_ID,viaf_author_id_dict,final_book_ids_list)
                    msg = "LC is adding identifiers 'isni' and 'lccn' to metadata.db"
                    self.maingui.status_bar.show_message(msg)
                    QApplication.instance().processEvents()
                    self.add_isni_lccn_for_viaf(final_results_dict)
                    QApplication.instance().processEvents()


                sleep(0)
                QApplication.instance().processEvents()

                self.force_refresh_of_cache(final_book_ids_list)

        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------
        #~ Perform edit_metadata_action at the very end to avoid metadata.db busy/lock errors.
        #---------------------------------------------------------------------------------
        msg = "LC is updating  #ddc, #lcc, #fast, #oclc-owi via 'Edit Metadata'"
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()
        payload = final_book_ids_list, oclc_owi_dict
        edit_metadata_action = self.maingui.iactions['Edit Metadata']
        edit_metadata_action.apply_metadata_changes(id_map, callback=self.finish_displaying_results_of_ddc_lcc(payload))
        QApplication.instance().processEvents()

        if DEBUG:
            print("----------------------------------------------------------------------------------------------------------")
            print("edit_metadata_action.apply_metadata_changes(id_map, callback=self.finish_displaying_results_of_ddc_lcc(payload)): ")
            for k,v in iteritems(id_map):
                print("id_map - book: ", as_unicode(k))
                print("id_map - mi:     ", as_unicode(v))
            print("----------------------------------------------------------------------------------------------------------")

        msg = "LC is complete."
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------

    def populate_available_metadata_using_classify_webscraping_author_title(self,my_db,my_cursor,source):
        ddc_exists = True
        lcc_exists = True
        self.data_was_found = False
        self.all_identifiers_should_be_kept_override = False

        n = len(self.ddc_name)
        if self.ddc_name == "none" or self.ddc_name == "":
            ddc_exists = False
        n = len(self.lcc_name)
        if self.lcc_name == "none" or self.lcc_name == "" :
            lcc_exists = False

        if ((not ddc_exists) and (not lcc_exists)):
            self.lc_derivation_messages_label.setText("Cannot Update Library Codes for Selected Books")
            return error_dialog(self.gui, _('Cannot Update Library Codes for Selected Books'),
                                                          _('You Must Configure the Lookup Name for at least one of DDC or LCC.'), show=True)

        # custom column value updated:
        ddc_dict = {}
        lcc_dict = {}
        fast_dict = {}

        # custom column value updated, or is a identifier:  #but may use a composite cc to show the identifier
        oclc_owi_dict = {}

        # identifier only:  #but may use a composite cc to show the identifier
        oclc_worldcat_dict = {}
        oclc_wi_dict = {}
        viaf_author_id_dict = {}

        my_db.close()

        #--------------------------------
        #---------------------------------
        #---------------------------------

        book_ids_list = []
        redo_book_ids_list = []
        final_book_ids_list = []

        self.ddc_name = '#' + self.ddc_name
        self.lcc_name = '#' + self.lcc_name
        self.fast_name = '#' + self.fast_name
        self.oclc_owi_name = '#' + self.oclc_owi_name


        self.guidb = self.maingui.library_view.model().db
        db = self.guidb.new_api

        n_total_books = len(self.selected_books_list)
        if n_total_books == 0:
            info_dialog(self.gui, "Library Codes", "No Selected Books.").show()
            return

        QApplication.instance().processEvents()

        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------

        db = self.maingui.current_db.new_api

        convert_dict = {
            '<' : '&lt;',
            '.' : '',
            ';' : '',
            ' ' : '+',  # &nbsp;
            '>' : '&gt;',
            "'" : '&apos;',
            '"' : '&quot;',
            '&' : '&amp;',
            '¢' : '&cent;',
            '©' : '&copy;',
            '®' : '&reg;',
            }

        for book in self.selected_books_list:
            s_book = as_unicode(book)
            n_book = int(book)
            try:
                msg = 'LC is deriving Metadata from Author/Title:  '
                if DEBUG: print(msg)
                self.maingui.status_bar.show_message(msg)
                QApplication.instance().processEvents()
                book_mi_object = db.get_metadata(n_book, get_cover=False, get_user_categories=False, cover_as_data=False)
                if not book_mi_object:
                    continue

                ## Bookmark
                colname = 'authors'
                authors_list = book_mi_object.__getattribute__(colname)
                if authors_list: author = authors_list[0]
                #authors_list = []
                #if len(tmp) > 1:
                #    for author in tmp:
                #        authors_list.append(author)
                if DEBUG: print("author is: ", author)
                if DEBUG: print("authors_list is: ", authors_list)

                #tmp = book_mi_object.__getattribute__(colname)
                #author = ""
                #for row in tmp:
                #    author = row
                #    if DEBUG: print("author is: ", author)
                #    break
                #END FOR
                #del tmp
                colname = 'title'
                title = book_mi_object.__getattribute__(colname)
                if DEBUG: print("title is: ", title)
                if not author:
                    continue
                if not title:
                    continue

                ### Bookmark
                full_title = title
                full_author = author

                if source == AUTHORTITLE_MOD:
                    dialog = TitleAuthorDialog(title=title, author=author)
                    if dialog.exec_() == QDialog.Accepted:
                        #title, author, full_title, full_author = dialog.get_values()
                        title, author = dialog.get_values()
                    else:
                        if DEBUG: print("Script stopped by user.")
                        break


                # The for below this wasn't working as expected when a title had an
                # apostrophe. Besides, the SRU was giving "Unsupported parameter" errors.
                def strip_accents(text):
                    text = unicodedata.normalize("NFD", text)
                    return ''.join(c for c in text if unicodedata.category(c) != 'Mn')

                title = re.sub(r"[.,:;\"'!?()\&]", "", title)

                title = strip_accents(title)
                author = strip_accents(author)

                title = quote_plus(title)
                author = quote_plus(author)
                ###

                #for k,v in convert_dict.items():
                #    author = author.replace(k,v)
                #    title = title.replace(k,v)
                #END FOR

                if DEBUG: print("html url author is: ", author)
                if DEBUG: print("html url title is: ", title)

                param_dict = {}
                param_dict["author"] = author
                param_dict["title"] = title
                ### Bookmark
                param_dict["full_author"] = full_author
                param_dict["full_title"] = full_title
                param_dict["authors_list"] = authors_list
                #~ --------------------
                QApplication.instance().processEvents()
                #~ --------------------
                ddc_return, lcc_return, fast_return_list, oclc_owi_return, viaf_author_id_return, oclc_worldcat_return, oclc_wi_return = oclc_classify_webscraping_author_title(param_dict)
                #~ --------------------
                QApplication.instance().processEvents()
                #~ --------------------
                self.data_was_found = False
                s_book = str(n_book)
                if ddc_return != "NONE":
                    ddc_dict[s_book] = ddc_return
                    self.data_was_found = True
                if lcc_return != "NONE":
                    lcc_dict[s_book] = lcc_return
                    self.data_was_found = True
                if isinstance(fast_return_list,list):
                    if len(fast_return_list) > 0:
                        fast_dict[s_book] = fast_return_list
                        self.data_was_found = True
                if oclc_owi_return != "NONE":
                    oclc_owi_dict[s_book] = oclc_owi_return
                    self.data_was_found = True
                if oclc_worldcat_return != "NONE":
                    oclc_worldcat_dict[s_book] = oclc_worldcat_return
                    self.data_was_found = True
                if viaf_author_id_return != "NONE":
                    viaf_author_id_dict[s_book] = viaf_author_id_return
                    self.data_was_found = True
                if oclc_wi_return != "NONE":
                    oclc_wi_dict[s_book] = oclc_wi_return
                    if not self.data_was_found:
                        redo_book_ids_list.append(n_book)
                        all_identifiers_should_be_kept = True
                #~ ----- oclc_wi_dict was populated the 1st time to enable this re-do for this book, so it still exists.
                if self.data_was_found:
                    book_ids_list.append(n_book)
                    if DEBUG: print("Multiple Response Re-do Using OCLC-WI: appending book with downloaded data to book_ids_list: ", as_unicode(n_book))
                del ddc_return
                del lcc_return
                del oclc_owi_return
                del oclc_worldcat_return
                del oclc_wi_return
                del viaf_author_id_return

            except Exception as e:
                if DEBUG: print("Exception within Multiple Response Re-do Using OCLC-WI: ", as_unicode(e))
                continue
                #------------------------------------------------------------------
            #END FOR
        #END IF

        QApplication.instance().processEvents()

        nd = len(ddc_dict)
        nl = len(lcc_dict)
        noclc = len(oclc_owi_dict)
        nwi = len(oclc_wi_dict)
        if nd == 0 and nl == 0 and noclc == 0 and nwi == 0:
            info_dialog(self.gui, "Library Codes", "Nothing Found For Any Selected Book.  Visit:  http://classify.oclc.org/classify2/  ").show()
            return

        custom_columns = self.maingui.current_db.field_metadata.custom_field_metadata()

        n = len(book_ids_list)
        if n == 0:
            if DEBUG: print("no books in book_ids_list; returning...")
            return

        id_map = {}
        for row in book_ids_list:
            data_changed = False
            s_book = as_unicode(row)
            n_book = int(row)
            mi = Metadata(_('Unknown'))
            if prefs['DDC_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    custcol1 = custom_columns[self.ddc_name]         #  custcol = custom_columns["#ddc"]
                    val = ddc_dict[s_book]
                    if not isinstance(val,list):
                        custcol1['#value#'] = as_unicode(ddc_dict[s_book])
                        mi.set_user_metadata(self.ddc_name, custcol1)   # class Metadata in  src>calibre>ebooks>metadata>book>base.py
                        data_changed = True
                    else:
                        if DEBUG: print("error: ddc_dict[s_book] val was a list, but should not be...")
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in ddc_dict: book id: ", as_unicode(e))
                    pass
            if prefs['LCC_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    val = lcc_dict[s_book]
                    if not isinstance(val,list):
                        custcol2 = custom_columns[self.lcc_name]
                        custcol2['#value#'] = as_unicode(lcc_dict[s_book])
                        mi.set_user_metadata(self.lcc_name, custcol2)
                        data_changed = True
                    else:
                        if DEBUG: print("error: lcc_dict[s_book] val was a list, but should not be...")
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in lcc_dict: book id: ", as_unicode(e))
                    pass

            if prefs['FAST_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    custcol4 = custom_columns[self.fast_name]
                    fast_list = fast_dict[s_book]
                    tmp_list = []
                    for new_tag in fast_list:
                        if not isinstance(new_tag,list):
                            if not isinstance(new_tag,unicode_type):
                                new_tag = as_unicode(new_tag)
                            if new_tag.count(",") > 0:
                                new_tag = new_tag.replace(",",";")  # so Calibre will not split it into 2 tags.
                            tmp_list.append(new_tag)
                        else:
                            if DEBUG: print("error: FAST new_tag was a list, but should not be...")
                    #END FOR
                    custcol4['#value#'] = tmp_list
                    mi.set_user_metadata(self.fast_name, custcol4)     # class Metadata in  src>calibre>ebooks>metadata>book>base.py
                    data_changed = True
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in fast_dict: book id: ", as_unicode(e))
                    pass

            if prefs['OCLC_OWI_IS_ACTIVE'] == as_unicode(S_TRUE):
                try:
                    val = oclc_owi_dict[s_book]
                    if not isinstance(val,list):
                        custcol3 = custom_columns[self.oclc_owi_name]
                        custcol3['#value#'] = as_unicode(oclc_owi_dict[s_book])
                        mi.set_user_metadata(self.oclc_owi_name, custcol3)
                        data_changed = True
                    else:
                        if DEBUG: print("error: oclc_owi_dict[s_book] val was a list, but should not be...")
                except Exception as e:
                    #~ if DEBUG: print("no-data for book in oclc_owi_dict: book id: ", as_unicode(e))
                    pass

            #~ ------ multi-return yields only a oclc_wi_dict, and nothing else; that is an Identifier, not a custom column...

            if data_changed:
                id_map[n_book] = mi
                final_book_ids_list.append(n_book)

            if len(id_map) == 0:
                self.data_was_found = False
            else:
                self.data_was_found = True

            if not self.data_was_found:
                return info_dialog(self.gui, "Library Codes", "Nothing Updated For Any Books, But There Were No Errors.  Visit: http://classify.oclc.org/classify2/ ").show()

            final_book_ids_list = list(set(final_book_ids_list))
            final_book_ids_list.sort()
        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------
        #~ Perform the following before edit_metadata_action to avoid db lock errors.
        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------
        if len(final_book_ids_list) > 0:

            if self.all_identifiers_should_be_kept_override:  # required for future multiple responses regardless of what the user may have otherwise customized.
                self.oclc_owi_identifier_is_desired = True
                prefs['OCLC_OWI_IDENTIFIER'] == as_unicode(S_TRUE)
                prefs

            if self.oclc_owi_identifier_is_desired:  #if you get one, you get them all.

                if len(oclc_owi_dict) > 0:
                    sleep(0)
                    self.add_oclc_owi_identifiers(oclc_owi_dict,final_book_ids_list)
                    QApplication.instance().processEvents()

                if len(oclc_worldcat_dict) > 0:
                    sleep(0)
                    self.add_oclc_worldcat_identifiers(oclc_worldcat_dict,final_book_ids_list)
                    QApplication.instance().processEvents()

                if len(oclc_wi_dict) > 0:
                    sleep(0)
                    self.add_oclc_wi_identifiers(oclc_wi_dict,final_book_ids_list)
                    QApplication.instance().processEvents()

                if len(viaf_author_id_dict) > 0:
                    sleep(0)
                    msg = "LC is adding 'viaf_author_identifiers' to metadata.db"
                    self.maingui.status_bar.show_message(msg)
                    self.add_viaf_author_id_identifiers(viaf_author_id_dict,final_book_ids_list)
                    msg = "LC is downloading identifiers 'isni' and 'lccn' using 'viaf_author_identifier'"
                    self.maingui.status_bar.show_message(msg)
                    QApplication.instance().processEvents()
                    final_results_dict = library_codes_generic_webscraping(SOURCE_TYPE_VIAF_AUTHOR_ID,viaf_author_id_dict,final_book_ids_list)
                    msg = "LC is adding identifiers 'isni' and 'lccn' to metadata.db"
                    self.maingui.status_bar.show_message(msg)
                    QApplication.instance().processEvents()
                    self.add_isni_lccn_for_viaf(final_results_dict)
                    QApplication.instance().processEvents()


                sleep(0)
                QApplication.instance().processEvents()

                self.force_refresh_of_cache(final_book_ids_list)



        #~ ----------------------------------------------------------------
        QApplication.instance().processEvents()
        #---------------------------------------------------------------------------------
        #---------------------------------------------------------------------------------
        #~ Perform edit_metadata_action at the very end to avoid metadata.db busy/lock errors.
        #---------------------------------------------------------------------------------
        msg = "LC is updating  #ddc, #lcc, #fast, #oclc-owi via 'Edit Metadata'"
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()
        payload = final_book_ids_list, oclc_owi_dict
        edit_metadata_action = self.maingui.iactions['Edit Metadata']
        edit_metadata_action.apply_metadata_changes(id_map, callback=self.finish_displaying_results_of_ddc_lcc(payload))
        QApplication.instance().processEvents()

        if DEBUG:
            print("----------------------------------------------------------------------------------------------------------")
            print("edit_metadata_action.apply_metadata_changes(id_map, callback=self.finish_displaying_results_of_ddc_lcc(payload)): ")
            for k,v in iteritems(id_map):
                print("id_map - book: ", as_unicode(k))
                print("id_map - mi:     ", as_unicode(v))
            print("----------------------------------------------------------------------------------------------------------")

        msg = "LC is complete."
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()


    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    #--------------------------------------------------------------------------------------------------------------------------------------
    def finish_displaying_results_of_ddc_lcc(self, payload):
        if DEBUG: print("...edit_metadata_action has completed...nothing else remains to be done...")
        final_book_ids_list, oclc_owi_dict = payload
        self.force_refresh_of_cache(final_book_ids_list)
    #-------------------------------------------------------------------------------------------------------------------------------------
    def get_isbn_identifiers_for_selected_books(self,my_db,my_cursor):

        try:
            del isbn_dict
        except:
            pass

        isbn_dict = {}

        #-----------------------------
        #ensure that at least one of the possible LC custom columns exists in this library...
        self.ddc_name = self.ddc_name.replace("#","")
        self.lcc_name = self.lcc_name.replace("#","")
        self.oclc_owi_name = self.oclc_owi_name.replace("#","")
        mysql = "SELECT count(*) FROM custom_columns WHERE label = ? OR label = ? OR label = ?"
        my_cursor.execute(mysql,(self.ddc_name,self.lcc_name,self.oclc_owi_name))
        s_count = my_cursor.fetchall()
        if s_count is None:
            s_count = 0
        else:
            for row in s_count:
                if row is None:
                    s_count = 0
                    break
                for col in row:
                    if col is None:
                        col = 0
                    s_count = col
                    if isinstance(s_count,str):
                        s_count = int(s_count)
                    if DEBUG: print("s_count: ", type(s_count), str(s_count))
                #END FOR
            #END FOR
        if not isinstance(s_count,int):
            s_count = 0
        if not s_count > 0 :
            if DEBUG: print("lcc and ddc and oclc have not been configured as custom columns in this library")
            return isbn_dict

        #-----------------------------

        for book in self.selected_books_list:
            book = as_unicode(book)
            mysql = "SELECT val FROM identifiers WHERE book = [BOOK] AND (type = 'isbn' OR type = 'issn') AND val IS NOT NULL "
            mysql = mysql.replace("[BOOK]", book)
            my_cursor.execute(mysql)
            tmp_rows = []
            del tmp_rows
            tmp_rows = my_cursor.fetchall()
            if tmp_rows:
                tmp_rows.sort(reverse=True)    #this forces isbn to be used instead of issn if both exist
                for row in tmp_rows:
                    for col in row:
                        isbn_dict[book] = col
                    #END FOR
                    break  #can have BOTH isbn and issn; isbn.  The ISBN identifies the individual book in a series or a specific year for an annual or biennial. The ISSN identifies the ongoing series, or the ongoing annual or biennial serial.
                #END FOR
            else:
                continue
        #END FOR

        return isbn_dict
    #-------------------------------------------------------------------------------------------------------------------------------------
    #-------------------------------------------------------------------------------------------------------------------------------------
    def get_oclc_wi_identifiers_for_selected_books(self,my_db,my_cursor):

        try:
            del wi_dict
        except:
            pass

        wi_dict = {}

        for book in self.selected_books_list:
            book = as_unicode(book)
            mysql = "SELECT val FROM identifiers WHERE book = [BOOK] AND (type = 'oclc-wi') AND val IS NOT NULL "
            mysql = mysql.replace("[BOOK]", book)
            my_cursor.execute(mysql)
            tmp_rows = []
            del tmp_rows
            tmp_rows = my_cursor.fetchall()
            if tmp_rows:
                tmp_rows.sort(reverse=True)    #this forces isbn to be used instead of issn if both exist
                for row in tmp_rows:
                    for col in row:
                        wi_dict[book] = col
                    #END FOR
                    break
                #END FOR
            else:
                continue
        #END FOR

        return wi_dict
    #-------------------------------------------------------------------------------------------------------------------------------------
    def add_oclc_owi_identifiers(self,oclc_owi_dict,final_book_ids_list):
        # oclc-owi is an identifier at oclc.org:        http://classify.oclc.org/classify2/api_docs/classify.html
        if len(oclc_owi_dict) == 0:
            return

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-----------------------------
        my_cursor.execute("begin")
        for book in final_book_ids_list:
            book = as_unicode(book)
            try:
                if book in oclc_owi_dict:
                    owi = oclc_owi_dict[book]
                    if DEBUG: print("Adding 'oclc-owi' identifier: ", as_unicode(owi))
                else:
                    continue
                owi = as_unicode(owi)
                book = int(book)
                mysql = "INSERT OR REPLACE INTO identifiers (id,book,type,val) VALUES (?,?,'oclc-owi', ?)  "
                my_cursor.execute(mysql,(None,book,owi))
            except Exception as e:
                if DEBUG: print("Exception: ", as_unicode(e))
                continue
        #END FOR
        my_cursor.execute("commit")
        my_db.close()
    #-------------------------------------------------------------------------------------------------------------------------------------
    def add_oclc_worldcat_identifiers(self,oclc_worldcat_dict,final_book_ids_list):
        # oclc_worldcat is an identifier that exists in classify that appears in an href with a worldcat url.
        if len(oclc_worldcat_dict) == 0:
            return

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-----------------------------
        my_cursor.execute("begin")
        for book in final_book_ids_list:
            book = as_unicode(book)
            try:
                if book in oclc_worldcat_dict:
                    oclc_worldcat = oclc_worldcat_dict[book]
                    if DEBUG: print("Adding 'oclc-worldcat' identifier: ", as_unicode(oclc_worldcat))
                else:
                    continue
                oclc_worldcat = as_unicode(oclc_worldcat)
                book = int(book)
                mysql = "INSERT OR REPLACE INTO identifiers (id,book,type,val) VALUES (?,?,'oclc-worldcat', ?)  "
                my_cursor.execute(mysql,(None,book,oclc_worldcat))
            except Exception as e:
                if DEBUG: print("Exception: ", as_unicode(e))
                continue
        #END FOR
        my_cursor.execute("commit")

        my_db.close()
    #-------------------------------------------------------------------------------------------------------------------------------------
    def add_viaf_author_id_identifiers(self,viaf_author_id_dict,final_book_ids_list):

        if len(viaf_author_id_dict) == 0:
            return

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-----------------------------
        #-----------------------------
        my_cursor.execute("begin")
        for book in final_book_ids_list:
            book = as_unicode(book)
            try:
                if book in viaf_author_id_dict:
                    viaf_author_id = viaf_author_id_dict[book]
                else:
                    continue
                if not viaf_author_id:
                    continue
                viaf_author_id = as_unicode(viaf_author_id)
                viaf_author_id = viaf_author_id.strip()
                if not viaf_author_id > "":
                    continue
                if as_unicode(viaf_author_id) == as_unicode("null"):
                    continue
                book = int(book)
                mysql = "INSERT OR REPLACE INTO identifiers (id,book,type,val) VALUES (?,?,'viaf_author_id', ?)  "
                my_cursor.execute(mysql,(None,book,viaf_author_id))
            except Exception as e:
                if DEBUG: print("Exception: ", as_unicode(e))
                continue
        #END FOR
        my_cursor.execute("commit")
        #-----------------------------
        my_db.close()

        del viaf_author_id_dict
    #-------------------------------------------------------------------------------------------------------------------------------------
    def add_isni_lccn_for_viaf(self,final_results_dict):

        msg = 'LC is now adding ISNI and LCCN identifiers for VIAF'
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()

        source_type = "viaf_author_id"

        if DEBUG: print("-------------------------------------------------------------------")
        if DEBUG: print("now adding isni and lccn for books with same viaf_author_id")
        if DEBUG: print("-------------------------------------------------------------------")

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
            del final_results_dict
            return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-----------------------------
        mysql = "SELECT book,val FROM identifiers WHERE type = ? "
        my_cursor.execute(mysql,([source_type]))
        book_type_list = my_cursor.fetchall()
        if not book_type_list:
            del final_results_dict
            return
        else:
            if not isinstance(book_type_list,list):
                book_type_list = list(book_type_list)
            if len(book_type_list) == 0:
                del final_results_dict
                return
            else:
                book_type_list.sort()
                if DEBUG: print("book_type_list: ", as_unicode(book_type_list))
        #-----------------------------
        my_cursor.execute("begin")
        for k,v in iteritems(final_results_dict):
            if DEBUG: print("final_results_dict:  k: ", str(k), "  v: ", str(v))
            #k is the val for the identifier source_type   e.g.  "123456" for "viaf_author_id"
            #v is the list of other lists of other identifiers by other types to add for books having "123456" as a val for type k: "viaf_author_id":
            #       new_row1 = "isni",v,isni            v = 123456        new_type, key_type_val, new_type_val = new_row
            #       new_row2 = "lccn",v,lccn          v = 123456
            for row in v:
                new_type, key_type_val, new_type_val = row
                if DEBUG: print("for row in v: ", new_type, key_type_val, new_type_val)
                for item in book_type_list:   # all key_type_vals are for key_type == source_type per:  mysql = "SELECT book,val FROM identifiers WHERE type = ? "
                    book,val = item
                    if as_unicode(val) == as_unicode(key_type_val):      # if 123456 == 123456:
                        # add a new identifier for the current book with a type of new_type and a val of new_type_val
                        book = int(book)
                        mysql = "INSERT OR REPLACE INTO identifiers (id,book,type,val) VALUES (?,?,?,?)  "
                        my_cursor.execute(mysql,(None,book,new_type,new_type_val))
                        if DEBUG: print("new: ", new_type, " value: ", as_unicode(new_type_val), " for book: ", as_unicode(book), " with key_type_val of: ", key_type_val, " for key_type: ", source_type)
                    else:
                        continue
                #END FOR
            #END FOR
            msg = 'LC has added ISNI and LCCN identifiers for all books with a VIAF of: ' + as_unicode(k)
            self.maingui.status_bar.show_message(msg)
            QApplication.instance().processEvents()
        #END FOR
        my_cursor.execute("commit")
        #-----------------------------
        my_db.close()
        #-----------------------------
        del final_results_dict

        msg = "LC is converting legacy LCCN Identifier values to be Permalink-ready"
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()

        self.convert_legacy_lccn_identifier_into_permalink_ready_format()  #as of: 20230405 so permalink can be used:  example:  https://lccn.loc.gov/n79018774

        msg = "LC is continuing..."
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()

    #-------------------------------------------------------------------------------------------------------------------------------------
    #-------------------------------------------------------------------------------------------------------------------------------------
    def add_oclc_wi_identifiers(self,wi_dict,book_ids_list):

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-----------------------------
        #-----------------------------
        my_cursor.execute("begin")
        for book in book_ids_list:
            book = as_unicode(book)
            try:
                if book in wi_dict:
                    wi = wi_dict[book]
                else:
                    continue
                if not wi:
                    continue
                wi = as_unicode(wi)
                wi = wi.strip()
                if not wi > "":
                    continue
                book = int(book)
                mysql = "INSERT OR IGNORE INTO identifiers (id,book,type,val) VALUES (?,?,'oclc-wi',?)  "
                my_cursor.execute(mysql,(None,book,wi))
            except Exception as e:
                if DEBUG: print("Exception: ", as_unicode(e))
                continue
        #END FOR
        my_cursor.execute("commit")
        #-----------------------------
        my_db.close()
        del wi_dict
    #-------------------------------------------------------------------------------------------------------------------------------------
    def copy_legacy_oclc_identifier_into_oclc_worldcat_identifier(self):
        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-----------------------------
        mysql = "SELECT id,book,type,val FROM identifiers WHERE type = 'oclc' "
        my_cursor.execute(mysql)
        rows = my_cursor.fetchall()
        if rows is None:
            my_db.close()
            return
        if len(rows) == 0:
            my_db.close()
            return
        if DEBUG:
            for r in rows:
                print(str(r))
            #END FOR

        book_list = []

        mysql = "INSERT OR IGNORE INTO identifiers (id,book,type,val) VALUES (?,?,?,?)  "
        my_cursor.execute("begin")
        for r in rows:
            id,book,type,val = r
            book_list.append(book)
            type = 'oclc-worldcat'
            my_cursor.execute(mysql,(None,book,type,val))
        #END FOR
        my_cursor.execute("commit")

        if question_dialog(self.gui, "Library Codes", "Your legacy 'oclc' Identifiers have been copied into their new name, 'oclc-worldcat'.<br><br>Do you want to delete the legacy 'oclc' Identifiers?"):
            mysql = "DELETE FROM identifiers WHERE type = 'oclc' "
            my_cursor.execute("begin")
            my_cursor.execute(mysql)
            my_cursor.execute("commit")
            my_db.close()
            self.force_refresh_of_cache(book_list)
        else:
            my_db.close()
    #-------------------------------------------------------------------------------------------------------------------------------------
    def convert_legacy_lccn_identifier_into_permalink_ready_format(self):
        #~ #as of: 20230405 so permalink can be used:  example:  https://lccn.loc.gov/n79018774
        #~ 'n79018774' instead of prior 'lccn-n79018774' for Identifier Type 'lccn'.

        #~ to create test data only:   mysql = "update identifiers set val = ('lccn-'||val) where type = 'lccn' and val not like 'lccn%'"

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Derive Library Codes'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
        #-----------------------------
        mysql = "SELECT id,book,type,val FROM identifiers WHERE type = 'lccn' AND val LIKE 'lccn-%'"
        my_cursor.execute(mysql)
        rows = my_cursor.fetchall()
        if rows is None:
            my_db.close()
            return
        if len(rows) == 0:
            my_db.close()
            return
        book_lccn_val_dict = {}
        book_list = []
        for row in rows:
            id,book,type,val = row
            if type == "lccn":                             #  lccn:lccn-n98016773
                if val.startswith("lccn-n"):            #  lccn-n98016773
                    val = val.replace("lccn-","")     # n98016773
                    val = val.strip()
                    if val.startswith("n"):
                        book_lccn_val_dict[book] = val
                        book_list.append(book)
        #END FOR

        mysql = "UPDATE identifiers SET val = ? WHERE book = ? AND type = 'lccn'  "
        my_cursor.execute("begin")
        for book,val in book_lccn_val_dict.items():
            my_cursor.execute(mysql,(val,book))
        #END FOR
        my_cursor.execute("commit")
        my_db.close()
        del book_lccn_val_dict
        self.force_refresh_of_cache(book_list)
        del book_list
        if DEBUG: print("\nLegacy format of LCCN Identifier Type has been changed to the LOC Permalink-ready format of 'n999999999'.\n https://lccn.loc.gov/n79018774\n")
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def mark_selected_books(self):

        found_dict = {}
        s_true = 'true'
        for row in self.selected_books_list:
            key = int(row)
            found_dict[key] = s_true
        #END FOR

        marked_ids = dict.fromkeys(found_dict, s_true)
        self.maingui.current_db.set_marked_ids(marked_ids)
        self.maingui.search.clear()
        self.maingui.search.set_search_string('marked:true')

        del found_dict
    #-----------------------------------------------------------------------------------------
    def get_selected_books(self):

        try:
            del self.selected_books_list
        except:
            pass

        self.selected_books_list = []

        book_ids_list = []

        book_ids_list = list(map(partial(self.convert_id_to_book), self.maingui.library_view.get_selected_ids()))  #https://stackoverflow.com/questions/50671360/map-in-python-3-vs-python-2
        n = len(book_ids_list)
        if n == 0:
            del book_ids_list
            return self.selected_books_list
        for item in book_ids_list:
            s = as_unicode(item['calibre_id'])
            self.selected_books_list.append(s)
        #END FOR

        del book_ids_list

        return self.selected_books_list
    #-----------------------------------------------------------------------------------------
    def convert_id_to_book(self, idval):
        book = {}
        book['calibre_id'] = idval
        return book
    #-----------------------------------------------------------------------------------------
    def force_refresh_of_cache(self,selected_book_list):
        db = self.maingui.current_db.new_api
        db.reload_from_db(clear_caches=False)
        frozen = db.all_book_ids()
        books = list(frozen)
        self.maingui.library_view.model().refresh_ids(books)
        self.maingui.tags_view.recount()
        QApplication.instance().processEvents()
        if DEBUG: print("number of books in selected_books_list to be re-selected after cache refresh: ", as_unicode(len(selected_book_list)))
        if len(selected_book_list) > 0:
            identifiers = set(selected_book_list)
            self.maingui.library_view.select_rows(identifiers,using_ids=True,change_current=True,scroll=True)  #refreshing the cache also clears the currently selected books...
    #-----------------------------------------------------------------------------------------
    def apsw_connect_to_library(self):

        my_db = self.maingui.library_view.model().db

        self.lib_path = my_db.library_path
        self.lib_path = self.lib_path.replace(os.sep, '/')
        if isbytestring(self.lib_path):
            self.lib_path = self.lib_path.decode(filesystem_encoding)

        path = my_db.library_path
        if isbytestring(path):
            path = path.decode(filesystem_encoding)
        path = path.replace(os.sep, '/')
        path = os.path.join(path, 'metadata.db')
        path = path.replace(os.sep, '/')

        if isbytestring(path):
            path = path.decode(filesystem_encoding)

        if path.endswith("/"):
            path = path[0:-1]

        if path.count("metadata.db") == 0:
            path = path + "/metadata.db"

        try:
            my_db =apsw.Connection(path)
            is_valid = True
        except Exception as e:
            if DEBUG: print("path to metadata.db is: ", path)
            if DEBUG: print("error: ", as_unicode(e))
            is_valid = False
            return None,None,is_valid

        my_cursor = my_db.cursor()

        mysql = "PRAGMA main.busy_timeout = 10000;"      #PRAGMA busy_timeout = milliseconds;
        my_cursor.execute(mysql)

        return my_db,my_cursor,is_valid
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def scrub_isbn(self):
        #~ This menu option is just to encourage users to scrub their ISBNs even if they are already ISBN-13s.
        msg = "ISBNs Scrubbed. 'E'dit to View New Value."
        self.scrub_isbns(msg)
    #-----------------------------------------------------------------------------------------
    def scrub_isbns(self,msg=None):
        #~ This menu option will always convert 10s to 13s, and always does scrubbing too.

        self.selected_books_list = self.get_selected_books()

        self.lc_isbns_scrubbed_books_list = []

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
             return error_dialog(self.gui, _('Convert ISBNs'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)

        self.scrub_isbn_values(my_db,my_cursor)

        self.convert_identifiers_isbn_from_10_to_13(my_db, my_cursor)

        self.detect_isbn_changes_to_refresh(my_db, my_cursor)

        my_db.close()

        self.mark_selected_books()

        del self.selected_books_list

        if not msg:
            msg = "Any ISBN-10s were Converted to ISBN-13s.  'E'dit to View New Value."

        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()
    #-----------------------------------------------------------------------------------------
    def convert_identifiers_isbn_from_10_to_13(self,my_db, my_cursor):
        #~ Important: this is alway done for all books, even if they were NOT selected otherwise.

        mysql = "SELECT book, val FROM identifiers WHERE type = 'isbn' \
                       AND val NOT LIKE '977%'  AND val NOT LIKE '978%'  \
                        AND val NOT LIKE '979%'  AND val NOT NULL;"
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall()
        if not tmp_rows:
            if DEBUG: print("No ISBN 10's Were Found to Convert")
            return
        else:
            n = len(tmp_rows)
            if DEBUG: print("Total Number of ISBN 10s Found: " + as_unicode(n))
            if n == 0 :
                return
            my_counter = 0
            my_total = 0
            my_cursor.execute("begin")  #apsw
            for row in tmp_rows:
                book, old_isbn = row
                book = int(book)
                old_isbn = as_unicode(old_isbn).strip()
                if len(old_isbn) == 10:
                    self.lc_isbns_scrubbed_books_list.append(book)
                    new_isbn = as_unicode(self._convert_isbn_convert_10_to_13(old_isbn))
                    if len(new_isbn) == 13:
                        if isinstance(new_isbn, str):
                            new_isbn = as_unicode(new_isbn)
                        mysql = "UPDATE identifiers SET val = ? WHERE book = ? AND type = 'isbn' "
                        my_cursor.execute(mysql,(new_isbn, book))
                        if DEBUG: print("ISBN10 Converted to ISBN13: " + as_unicode(old_isbn) + " >>> " + as_unicode(new_isbn))
                    else:
                        if isinstance(old_isbn, str):
                            old_isbn = as_unicode(old_isbn)
                        if DEBUG: print("This ISBN10 appears to not really be an ISBN10, and was deleted:  " + as_unicode(old_isbn) )
                        mysql = "DELETE FROM identifiers WHERE val = ? AND book = ? AND type = 'isbn' "
                        my_cursor.execute(mysql,(old_isbn, book))
                    self.lc_isbns_scrubbed_books_list.append(book)
                else:
                    if isinstance(old_isbn, str):
                        old_isbn = as_unicode(old_isbn)
                    if DEBUG: print("This ISBN appears to not really be any kind of ISBN, and was deleted:  " + as_unicode(old_isbn) )
                    mysql = "DELETE FROM identifiers WHERE val = ? AND book = ? AND type = 'isbn' "
                    my_cursor.execute(mysql,(old_isbn, book))
                    self.lc_isbns_scrubbed_books_list.append(book)
            #END FOR
            my_cursor.execute("commit")
    #-----------------------------------------------------------------------------------------
    def _convert_isbn_check_digit_13(self,isbn):
        try:
            assert len(isbn) == 12
            sum = 0
            for i in range(len(isbn)):
                c = int(isbn[i])
                if i % 2: w = 3
                else: w = 1
                sum += w * c
            r = 10 - (sum % 10)
            if r == 10: return '0'
            else: return as_unicode(r)
        except:
            return isbn
    #-----------------------------------------------------------------------------------------
    def _convert_isbn_convert_10_to_13(self,isbn):
        try:
            assert len(isbn) == 10
            prefix = '978' + isbn[:-1]
            check = self._convert_isbn_check_digit_13(prefix)
            return prefix + check
        except:
            return isbn
    #-----------------------------------------------------------------------------------------
    def scrub_isbn_values(self,my_db,my_cursor):
        try:
            #~ ----------
            my_cursor.execute("begin")
            mysql = "UPDATE OR IGNORE identifiers SET type = 'isbn',val=type WHERE type LIKE '%978%' AND val NOT LIKE '%978%'   "    # type 978000123456 value xxxxx  should be:  isbn:978000123456
            my_cursor.execute (mysql)
            mysql = "UPDATE OR IGNORE identifiers SET type = 'isbn',val=type WHERE type LIKE '979%' AND val NOT LIKE '979%'   "          # 979 is reservef for use by the same int'l agency that assigns 978
            my_cursor.execute (mysql)
            mysql = "UPDATE OR IGNORE identifiers SET type = 'isbn',val=type WHERE type LIKE '977%' AND val NOT LIKE '977%'   "          # 977 is reservef for use by the same int'l agency that assigns 978
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            #~ ----------
            my_cursor.execute("begin")
            mysql = "UPDATE OR IGNORE identifiers SET type = 'isbn' WHERE type LIKE '%isbn%' AND type != 'isbn'   "                               # type urnisbn            value 978000123456   should be:  isbn:978000123456
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            #~ ----------
            my_cursor.execute("begin")
            mysql = "UPDATE OR IGNORE identifiers SET type = (trim(type)) WHERE type LIKE '%isbn%'  "
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            #~ ----------
            my_cursor.execute("begin")
            mysql = "UPDATE identifiers SET val = (replace(val,'-','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%-%')"
            my_cursor.execute (mysql)
            mysql = "UPDATE identifiers SET val = (replace(val,' ','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '% %') "
            my_cursor.execute (mysql)
            mysql = "UPDATE identifiers SET val = (replace(val,':','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%:%') "
            my_cursor.execute (mysql)
            mysql = "UPDATE identifiers SET val = (replace(val,'/','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%/%') "
            my_cursor.execute (mysql)
            mysql = "UPDATE identifiers SET val = (trim(val)) WHERE type = 'isbn'  "
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            #~ ----------
            my_cursor.execute("begin")
            mysql = "UPDATE identifiers SET val = (replace(val,'urn','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%urn%') "
            my_cursor.execute (mysql)
            mysql = "UPDATE identifiers SET val = (replace(val,'ebook','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%ebook%') "
            my_cursor.execute (mysql)
            mysql = "UPDATE identifiers SET val = (replace(val,'eBook','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%eBook%') "
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            #~ ----------
            my_cursor.execute("begin")
            mysql = "UPDATE identifiers SET val = (replace(val,'isbn','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%isbn%') "
            my_cursor.execute (mysql)
            my_cursor.execute("commit")
            #~ ----------
            if DEBUG: print("All ISBN13s have been standardized by removing any dashes, spaces and prefixes.")
        except Exception as e:
            if DEBUG: print(as_unicode(e))
            try:
                my_cursor.execute("commit")
            except:
                pass
    #---------------------------------------------------------------------------------------------------------------------------------------
    def detect_isbn_changes_to_refresh(self,my_db, my_cursor):

        mysql = "SELECT book, val FROM identifiers WHERE type = 'isbn' "
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall()
        if not tmp_rows:
            return
        if len(tmp_rows) == 0:
            return

        tmp_rows.sort()

        for row in tmp_rows:
            book,val = row
            mi = self.guidb.new_api.get_metadata(book)
            identifiers_dict = mi.get_identifiers()
            if 'isbn' in identifiers_dict:
                cache_val = identifiers_dict['isbn']
            else:
                cache_val = "NONE"
            if val != cache_val:
                self.lc_isbns_scrubbed_books_list.append(book)
                if DEBUG: print("book: ", as_unicode(book), "  db val: ", as_unicode(val), "  cache val: ", as_unicode(cache_val))
        #END FOR

        self.lc_isbns_scrubbed_books_list = list(set(self.lc_isbns_scrubbed_books_list))

        if len(self.lc_isbns_scrubbed_books_list) > 0:
            if DEBUG: print("Cache being refreshed from metadata.db due to ISBN key/value changes")
            self.force_refresh_of_cache(self.lc_isbns_scrubbed_books_list)

        for book in self.lc_isbns_scrubbed_books_list:
            if not book in self.selected_books_list:
                self.selected_books_list.append(book)    #so will get marked as if it were selected, since it was changed although not first selected by the user...
        #END FOR

        del self.lc_isbns_scrubbed_books_list
    #-----------------------------------------------------------------------------------------



    #-------------------------------------------------------------------------------------------------------------------------------------
    #-------------------------------------------------------------------------------------------------------------------------------------
    def find_alternatives_for_nonresponsive_isbn(self):
        # only a single selected book is allowed...

        self.guidb = self.maingui.library_view.model().db

        self.selected_books_list = self.get_selected_books()
        n_books = len(self.selected_books_list)
        if n_books != 1:
             return error_dialog(self.gui, _('Derive Library Codes'),_('One (1) Book Must Be Selected; Cancelled. '), show=True)
        self.selected_books_list.sort()

        book = self.selected_books_list[0]
        book = int(book)

        mi = self.guidb.new_api.get_metadata(book)
        identifiers_dict = mi.get_identifiers()
        if 'isbn' in identifiers_dict:
            isbn_nonresponsive = identifiers_dict['isbn']
        else:
            del mi
            del identifiers_dict
            del self.selected_books_list
            return

        url = "https://books.google.com/books?isbn="

        url = url + isbn_nonresponsive

        if DEBUG: print("nonresponsive isbn url is : ", url)

        msg = "LC opening: " + url + "     Click 'More Editions' to find Alternative ISBNs "
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()

        try:
            if sys.platform == 'win32':
                os.startfile(url)
            elif sys.platform == 'win64':
                os.startfile(url)
            elif sys.platform == 'darwin':
                subprocess.Popen(['open', url])
            else:
                try:
                    subprocess.Popen(['xdg-open', url])
                except Exception as e:
                    if DEBUG: print("Exception opening web browser: ", url, "       ", as_unicode(e))
        except Exception as e:
            if DEBUG: print("Exception opening web browser: ", url, "       ", as_unicode(e))


        del mi
        del identifiers_dict
        del self.selected_books_list
        del url
        del isbn_nonresponsive
    #-------------------------------------------------------------------------------------------------------------------------------------
    #-------------------------------------------------------------------------------------------------------------------------------------
    def extract_issn_from_pdf_control(self):

        self.selected_book_list = self.get_selected_books()

        if len(self.selected_book_list) == 0:
            return

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
            return

        self.issn_results_dict = {}

        for current_book in self.selected_book_list:
            full_book_path = self.build_book_path(my_db,my_cursor,current_book,self.lib_path)
            if not full_book_path == "":
                msg = "Extracting ISSN for: " + full_book_path
                self.maingui.status_bar.show_message(msg)
                QApplication.instance().processEvents()
                issn = self.pdf_control(full_book_path)
                if as_unicode("-") in as_unicode(issn):
                    self.issn_results_dict[current_book] = as_unicode(issn)
                else:
                    pass
                    #~ if DEBUG: print("issn not found for book: ", as_unicode(current_book))
            else:
                if DEBUG: print("full book path not found for book: ", as_unicode(current_book))
        #END FOR

        new_list = []

        if len(self.issn_results_dict) > 0:
            my_cursor.execute("begin")
            #~ for current_book,issn in self.issn_results_dict.iteritems():
            for current_book,issn in iteritems(self.issn_results_dict):
                current_book = int(current_book)
                mysql = "INSERT OR REPLACE INTO identifiers (id,book,type,val) VALUES (NULL,?,'issn',?)  "
                my_cursor.execute(mysql,(current_book,issn))
                new_list.append(current_book)
                if DEBUG: print("ISSN updated for: ", as_unicode(current_book), "  ", issn)
            #END FOR
            my_cursor.execute("commit")
            self.force_refresh_of_cache(new_list)
        else:
            pass

        my_db.close()

        n = len(self.issn_results_dict)
        msg = "LC: Extracting ISSNs from PDFs has Completed.  Updated: " + as_unicode(n)
        self.maingui.status_bar.show_message(msg)
        QApplication.instance().processEvents()

        del self.issn_results_dict
        del self.selected_book_list
        del new_list
    #-------------------------------------------------------------------------------------------------------------------------------------
    def build_book_path(self,my_db,my_cursor,current_book,library_path):

        full_book_path = ""
        path_to_use = ""
        format_to_use = ""
        name = ""

        mysql = 'SELECT path FROM books WHERE id = ?'
        my_cursor.execute(mysql,([current_book]))
        tmp = my_cursor.fetchall()
        if not tmp:
            return full_book_path
        else:
            if len(tmp) == 0:
                return full_book_path
            else:
                for row in tmp:
                    for col in row:
                        path_to_use = col

        mysql = "SELECT format,name FROM data WHERE book = ? AND format = 'PDF' "
        my_cursor.execute(mysql,([current_book]))
        tmp = my_cursor.fetchall()
        if not tmp:
            pass
        else:
            if len(tmp) == 0:
                pass
            else:
                for row in tmp:
                    format_to_use,name = row
                    break
                del tmp

        if not format_to_use == "PDF":
            return full_book_path

        s_lower = format_to_use.lower()

        name = name + "." + s_lower

        path_to_use = os.path.join(path_to_use,name)

        full_book_path = os.path.join(library_path,path_to_use)

        full_book_path = full_book_path.replace(os.sep,"/")

        del path_to_use
        del name
        del s_lower

        return full_book_path
    #-------------------------------------------------------------------------------------------------------------------------------------
    def pdf_control(self,path):

        issn = ""

        path = path.replace(os.sep,"/")
        if isbytestring(path):
            path = path.decode(filesystem_encoding)

        issn = self.extract_pdf_issn(path)

        del path

        return issn
    #-------------------------------------------------------------------------------------------------------------------------------------
    def extract_pdf_issn(self,pdf_path):

        issn = ""
        pdf_html_ugly = []

        try:

            html_dir = pdftohtml_extract_pdf_issn(pdf_path)
            html_path = os.path.join(html_dir, 'index.html')

            with open(html_path, 'rb') as f:
                pdf_html_ugly = f.readlines()
            f.close()

            try:
                if os.isfile(html_path):
                    os.remove(html_path)
            except:
                pass

            try:
                del f
                del html_path
                del html_dir
                del pdf_path
            except:
                pass

            n = len(pdf_html_ugly)
            if DEBUG: print("Extraction of PDF HTML was successful; number of rows of text: ", as_unicode(n))

        except Exception as e:
            if DEBUG: print("Extraction of PDF HTML Failed using 'pdftohtml(os.getcwdu(), stream.name, options.no_images)': " + as_unicode(e))
            return issn

        sleep(0)
        is_in_next_row = False
        re1 = 'ISSN[:]*[ ]*[No.]*[ ]*[0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9X]'
        p1 = re.compile(re1, re.IGNORECASE)
        re2 = '[0-9][0-9][0-9][0-9][- ][0-9][0-9][0-9][0-9X]'
        p2 = re.compile(re2, re.IGNORECASE)
        for row in pdf_html_ugly:
            r = as_unicode(row)
            if as_unicode("ISSN") in r or as_unicode("issn") in r or as_unicode("Issn") in r or is_in_next_row:
                r = as_unicode(r.replace("&#160;"," "))
                r = as_unicode(r.replace("<br>"," "))
                try:
                    if is_in_next_row:
                        match1 = p2.search(r)
                        is_in_next_row = False
                    else:
                        match1 = p1.search(r)
                    if match1:
                        is_in_next_row = False
                        s = match1.group(0)
                        issn = as_unicode(s)
                        #~ if DEBUG: print(issn)
                        issn = issn.upper()
                        issn = issn.replace("ISSN","")
                        issn = issn.replace(":","")
                        issn = issn.replace(" ","")
                        issn = issn.replace(";","")
                        issn = issn.replace("NO","")
                        issn = issn.replace(".","")
                        issn = issn.strip()
                        if len(issn) == 8:
                            p1 = issn[0:4]
                            p2 = issn[4: ]
                            issn = p1 + "-" + p2
                        break
                    else:
                        is_in_next_row = True
                except Exception as e:
                    if DEBUG: print("regex error: ", as_unicode(e))
                    continue
        #END FOR

        del pdf_html_ugly

        sleep(0)

        return issn
    #---------------------------------------------------------------------------------------------------------------------------------------
    #-------------------------------------------------------------------------------------------------------------------------------------
    def update_genre_control(self):

        #~ for k,v in iteritems(prefs):
            #~ if DEBUG: print(as_unicode(k),as_unicode(v))

        if prefs['GENRE_IS_INACTIVE'] == as_unicode(S_TRUE):
            return
        if prefs['GENRE_DDC_IS_ACTIVE'] == as_unicode(S_FALSE) and prefs['GENRE_LCC_IS_ACTIVE'] == as_unicode(S_FALSE):
            return
        if not prefs['GENRE'].startswith("#"):
            return

        self.genre_label = prefs['GENRE']
        self.genre_label = self.genre_label[1: ]
        self.genre_label = self.genre_label.strip()
        if len(self.genre_label) == 0:
            return

        if prefs['GENRE_DDC_IS_ACTIVE'] == as_unicode(S_TRUE):
            self.lc_label = prefs['DDC']
        else:
            self.lc_label = prefs['LCC']

        if not self.lc_label.startswith("#"):
            return
        else:
            self.lc_label = self.lc_label[1: ]
            self.lc_label = self.lc_label.strip()
            if len(self.lc_label) == 0:
                return

        self.selected_books_list = self.get_selected_books()

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
            return error_dialog(self.gui, _('Update Genre'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)

        self.create_lc_genre_mapping_table(my_db,my_cursor)   #only if not exists

        self.custom_column_id_genre,self.custom_column_id_library_code = self.get_custom_column_table(my_db,my_cursor)
        if self.custom_column_id_genre > '0' and self.custom_column_id_library_code > '0':
            self.lc_dict = self.get_library_codes_for_selected_books(my_db,my_cursor)
            if len(self.lc_dict) > 0:
                self.mapping_dict = self.get_mapping_table(my_db,my_cursor)
                if len(self.mapping_dict) > 0:
                    self.update_genres(my_db,my_cursor)
                    self.force_refresh_of_cache(self.selected_books_list)
                    self.mark_selected_books()

        my_db.close()

        del self.custom_column_id_genre
        del self.custom_column_id_library_code
        try:
            del self.lc_dict
            del self.mapping_dict
            del self.genre_dict
            del self.genre_set
        except:
            pass
        del self.selected_books_list
#-------------------------------------------------------------------------------------------------------------------------------------
    def update_genres(self,my_db,my_cursor):

        if prefs['GENRE_DDC_IS_ACTIVE'] == as_unicode(S_TRUE):
            self.ddc_match()
        elif prefs['GENRE_LCC_IS_ACTIVE'] == as_unicode(S_TRUE):
            self.lcc_match()
        else:
            return

        mysql = "INSERT OR IGNORE INTO custom_column_[N] (id,value) VALUES (null,?)"
        mysql = mysql.replace("[N]",self.custom_column_id_genre)
        my_cursor.execute("begin")
        for genre in self.genre_set:
            my_cursor.execute(mysql,([genre]))
        #END FOR
        my_cursor.execute("commit")

        mysql = "INSERT OR IGNORE INTO books_custom_column_[N]_link (id,book,value) VALUES (null,?,(SELECT id FROM custom_column_[N] WHERE value = ? AND value NOT NULL) )"
        mysql = mysql.replace("[N]",self.custom_column_id_genre)
        my_cursor.execute("begin")
        for book,genre in iteritems(self.genre_dict):
            my_cursor.execute(mysql,(book,genre))
        #END FOR
        my_cursor.execute("commit")
#-------------------------------------------------------------------------------------------------------------------------------------
    def ddc_match(self):

        self.genre_dict = {}
        self.genre_set = set()

        if prefs['GENRE_EXACT_MATCH'] == as_unicode(S_TRUE):
            use_exact_match = True
        else:
            use_exact_match = False

        #~ for book,lc in self.lc_dict.iteritems():
        for book,lc in iteritems(self.lc_dict):
            #~ if DEBUG: print("book,lc: ", as_unicode(book), lc)
            if lc in self.mapping_dict:
                genre = self.mapping_dict[lc]
                #~ if DEBUG: print("exact match: ", genre)
            else:
                if not use_exact_match:
                    #~ if DEBUG: print("not use_exact_match")
                    if lc.count(".") > 0:
                        do_while = True
                        #~ if DEBUG: print("original lc: ", lc)
                        while do_while:
                            lc = lc[0:-1]
                            if lc in self.mapping_dict:
                                genre = self.mapping_dict[lc]
                                do_while = False
                            else:
                                if lc.count(".") > 0:
                                    #~ if DEBUG: print("iterating: ", lc)
                                    pass
                                else:
                                    #on final shortening (e.g. 050. is now 050), but never shorten 050 to 05
                                    #~ if DEBUG: print("final shortening: ", lc)
                                    if lc in self.mapping_dict:
                                        genre = self.mapping_dict[lc]
                                    else:
                                        genre = ""
                                    do_while = False  #no matter what...
                        #END WHILE
                    else:
                        #~ if DEBUG: print("no decimal found in original lc")
                        genre = ""
                else:
                    #~ if DEBUG: print("exact match specified, so nothing done...")
                    genre = ""
            self.genre_dict[book] = genre
            self.genre_set.add(genre)
        #END FOR
#-------------------------------------------------------------------------------------------------------------------------------------
    def lcc_match(self):

        self.genre_dict = {}
        self.genre_set = set()

        n_max_match_length = int(prefs['GENRE_LCC_MATCH_LENGTH'])

        #~ for book,lc in self.lc_dict.iteritems():
        for book,lc in iteritems(self.lc_dict):
            lc = lc.strip()
            if len(lc) == 0:
                continue
            lc = lc.upper()
            if len(lc) > n_max_match_length:
                lc = lc[0:n_max_match_length]
            #~ if DEBUG: print("book,lc: ", as_unicode(book), lc)
            if lc in self.mapping_dict:
                genre = self.mapping_dict[lc]
                #~ if DEBUG: print("[1] match found: ", genre)
            else:
                if len(lc) == 2:
                    if lc[0].isalpha():
                        if lc[1].isdigit():
                            lc = lc[0]
                            if lc in self.mapping_dict:
                                genre = self.mapping_dict[lc]
                                #~ if DEBUG: print("[2] match found: ", genre)
                            else:
                                genre = ""
                        else:
                            genre = ""
                    else:
                        genre = ""
                else:
                    genre = ""
            self.genre_dict[book] = genre
            self.genre_set.add(genre)
        #END FOR
#-------------------------------------------------------------------------------------------------------------------------------------
    def get_mapping_table(self,my_db,my_cursor):

        self.mapping_dict = {}

        mysql = "SELECT library_code,genre FROM _lc_genre_mapping WHERE genre NOT NULL"
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall()
        if not tmp_rows:
            tmp_rows = []
        for row in tmp_rows:
            library_code,genre = row
            if library_code:
                if genre:
                    library_code = library_code.upper()
                    self.mapping_dict[library_code] = genre
        #END FOR

        del tmp_rows

        return self.mapping_dict
#-------------------------------------------------------------------------------------------------------------------------------------
    def get_library_codes_for_selected_books(self,my_db,my_cursor):

        self.lc_dict = {}

        mysql = "SELECT id,value FROM custom_column_[N] WHERE id = (SELECT value FROM books_custom_column_[N]_link WHERE book = ?)"
        mysql = mysql.replace("[N]",self.custom_column_id_library_code)
        for book in self.selected_books_list:
            my_cursor.execute(mysql,([book]))
            tmp_rows = my_cursor.fetchall()
            if not tmp_rows:
                tmp_rows = []
            for row in tmp_rows:
                id,value = row
                if id:
                    if value:
                        self.lc_dict[book] = value
            #END FOR
            del tmp_rows
        #END FOR

        return self.lc_dict
#-------------------------------------------------------------------------------------------------------------------------------------
    def get_custom_column_table(self,my_db,my_cursor):

        self.custom_column_id_genre = 0
        self.custom_column_id_library_code = 0

        mysql = "SELECT id,label FROM custom_columns WHERE datatype = 'text' AND normalized = 1"  # should not be tag-like...
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall()
        if not tmp_rows:
            tmp_rows = []
        for row in tmp_rows:
            id,label = row
            if as_unicode(label) == as_unicode(self.genre_label):
                self.custom_column_id_genre = as_unicode(id)
            if as_unicode(label) == as_unicode(self.lc_label):
                self.custom_column_id_library_code = as_unicode(id)
        #END FOR

        del tmp_rows

        return self.custom_column_id_genre,self.custom_column_id_library_code
#-------------------------------------------------------------------------------------------------------------------------------------
    def create_lc_genre_mapping_table(self,my_db,my_cursor):

        try:
            my_cursor.execute("begin")
            mysql = "CREATE TABLE  _lc_genre_mapping (library_code TEXT PRIMARY KEY  NOT NULL  UNIQUE , genre TEXT DEFAULT TBD)"
            my_cursor.execute(mysql)
            my_cursor.execute("commit")
        except Exception as e:
            try:
                my_cursor.execute("commit")
            except Exception as e:
                pass
            #~ if DEBUG: print("[1] already created and seeded; continuing normally: ",as_unicode(e))
            return  # already seeded...

        # seed the table
        from calibre_plugins.library_codes.library_codes_genre_mapping import return_lc_genre_mapping_table_mysql
        mysql = return_lc_genre_mapping_table_mysql()

        try:
            my_cursor.execute(mysql)
        except Exception as e:
            if DEBUG: print("[2]",as_unicode(e))
            try:
                my_cursor.execute("commit")
            except Exception as e:
                pass
#-------------------------------------------------------------------------------------------------------------------------------------
    def import_csv_mappings(self):

        if prefs['GENRE_IS_INACTIVE'] == as_unicode(S_TRUE):
            info_dialog(self.gui, 'ERROR','LC has not (yet) been configured and activated for this function; Execution canceled.').show()
            return

        chosen_files = self.choose_csv_file_to_import()
        if not chosen_files:
            info_dialog(self.gui, 'Canceled','No CSV File was was selected; Execution canceled.').show()
            return
        else:
            csv_path = chosen_files[0]

        csv_mappings_list = self.import_csv_file(csv_path)
        if not len(csv_mappings_list) > 0:
            if DEBUG: print("CSV File was empty.  Nothing done.")
            return
        is_valid,n_total = self.add_csv_mappings(csv_mappings_list)
        if not is_valid:
            info_dialog(self.gui, 'Failed to Import CSV File','Nothing was imported due to errors.').show()
        else:
            msg = 'CSV File rows imported and added to (or changed in) the mapping table: ' + as_unicode(n_total)
            info_dialog(self.gui, 'Success Importing CSV File',msg).show()

        del chosen_files
        del csv_mappings_list
#-------------------------------------------------------------------------------------------------------------------------------------
    def choose_csv_file_to_import(self):

        name = "choose_mappings_csv_file"
        title = "Choose Mapping Table CSV File"
        all_files=True
        select_only_single_file=True
        filters=[]
        window = None   # parent = None

        mode = QFileDialog.FileMode.ExistingFile if select_only_single_file else QFileDialog.FileMode.ExistingFiles
        fd = FileDialog(title=title, name=name, filters=filters, parent=window, add_all_files_filter=all_files, mode=mode)
        fd.setParent(None)
        if fd.accepted:
            return fd.get_files()
        return None
#---------------------------------------------------------------------------------------------------------------------------------------------
    def import_csv_file(self,csv_path):

        tmp_list = []
        csv_mappings_list  = []

        if DEBUG: print("CSV File path: " + csv_path)

        if csv_path == as_unicode(""):
            return csv_mappings_list

        try:
            with open (csv_path,'r') as csvfile:
                lc_csv_reader = csv.reader(csvfile,dialect='excel')
                for row in lc_csv_reader:
                    tmp_list.append(row)
                #END FOR
            csvfile.close()
            del csv_path
            del lc_csv_reader
            for row in tmp_list:
                if isinstance(row,list):
                    csv_mappings_list.append(row)
                elif isinstance(row,unicode_type):
                    pass
        except Exception as e:
            if DEBUG: print("CSV File Error: " + as_unicode(e))
            error_dialog(self.gui, _('CSV File Error'),as_unicode(e), show=True)

        return csv_mappings_list
#---------------------------------------------------------------------------------------------------------------------------------------------
    def add_csv_mappings(self,csv_mappings_list):

        is_valid = False

        n_total_csv_mappings_to_add = len(csv_mappings_list)

        if DEBUG: print("Number of mappings in the specified CSV file: " + as_unicode(n_total_csv_mappings_to_add))

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
            error_dialog(self.gui, _('Update Genre'),_('Database Connection Error.  Cannot Connect to the Current Library.'), show=True)
            return is_valid,0

        self.create_lc_genre_mapping_table(my_db,my_cursor)

        my_cursor.execute("begin")
        i = 0
        n_total = 0
        for row in csv_mappings_list:        # row = ['"811.56","Fiction - 21st Century"']
            try:
                #~ if DEBUG: print("raw csv row: " + as_unicode(row))
                library_code,genre = row
            except Exception as e:
                if DEBUG: print("library_code,genre = row in csv file error.  invalid csv file format and/or data: " + as_unicode(e))
                msg = "library_code,genre = row in csv file error.  invalid csv file format and/or data: " + as_unicode(e)
                error_dialog(self.gui, _('CSV data error for mapping'),_(msg), show=True)
                break
            try:
                if library_code:
                    library_code = as_unicode(library_code)
                    library_code = library_code.replace('"',"")
                    library_code = library_code.replace("'","")
                    library_code = library_code.strip()
                    if genre:
                        try:
                            genre = as_unicode(genre, encoding='utf-8', errors='replace') # if bytes, decodes, else returns as unique_type, which returns the original if already unicode
                        except Exception as e:
                            if DEBUG: print("utf-8 decode error: ", as_unicode(e))
                        genre = genre.strip()
                        if genre != '':
                            mysql = "INSERT OR REPLACE INTO _lc_genre_mapping (library_code,genre) VALUES (?,?)"
                            my_cursor.execute(mysql,(library_code,genre))
                            #~ if DEBUG: print("added or replaced: " + library_code + "   " + genre)
                            n_total = n_total + 1
                            i = i + 1
                            if i >= 100:
                                my_cursor.execute("commit")
                                my_cursor.execute("begin")
                                i = 0
                            else:
                                continue
            except Exception as e:
                if DEBUG: print("CSV data error for mapping: " + library_code + " --- " + genre + " with the reason: " + as_unicode(e) )
                msg = "CSV data error for mapping: " + library_code + " --- " + genre + " with the reason: " + as_unicode(e)
                error_dialog(self.gui, _('CSV data error for mapping'),_(msg), show=True)
                break
        #END FOR
        try:
            my_cursor.execute("commit")
        except:
            pass

        my_db.close()

        del csv_mappings_list

        is_valid = True

        return is_valid,n_total
    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
    def ddc_pie_chart_1(self):
        self.show_ddc_pie_chart(1)
    def ddc_pie_chart_2(self):
        self.show_ddc_pie_chart(2)
    def show_ddc_pie_chart(self,type):

        my_db,my_cursor,is_valid = self.apsw_connect_to_library()
        if not is_valid:
            return

        self.ddc_name = prefs['DDC'].replace("#","").strip()

        #-----------------------------
        #before anything else, ensure that the ddc custom column exists in this library...
        self.ddc_name = self.ddc_name.replace("#","")
        mysql = "SELECT id,label FROM custom_columns WHERE label = ? and normalized = 1 and datatype = 'text' "
        my_cursor.execute(mysql,([self.ddc_name]))
        tmp_rows= my_cursor.fetchall()
        if not tmp_rows:
            tmp_rows = []
        if len(tmp_rows) == 0:
            my_db.close()
            if DEBUG: print("ddc has not been configured as a proper custom column in this library")
            return
        for row in tmp_rows:
            id,dummy = row
        #END FOR
        del tmp_rows
        s_id = as_unicode(id)
        s_id = s_id.strip()
        #-----------------------------
        mysql = "SELECT count(book) AS number,(SELECT substr(value,1,[L]) FROM custom_column_[N] WHERE id = books_custom_column_[N]_link.value) AS ddc \
                        FROM books_custom_column_[N]_link  GROUP BY ddc ORDER BY number DESC, ddc ASC "
        if type == 1:
            mysql = mysql.replace("[L]","2")
        else:
            mysql = mysql.replace("[L]","3")
        mysql = mysql.replace("[N]",s_id)
        my_cursor.execute(mysql)
        tmp_rows= my_cursor.fetchall()
        my_db.close()
        if not tmp_rows:
            tmp_rows = []
        if len(tmp_rows) == 0:
            if DEBUG: print("DDC has not been populated in this library.  Nothing to show.")
            return

        from calibre_plugins.library_codes.qpainter_charts import Chart, PieChart, Viewer, DialogViewer, DataTable

        table = DataTable()
        table.addColumn('DDC')
        table.addColumn('Count')
        i = 0
        for row in tmp_rows:
            n_count,ddc = row
            if i < 20:
                table.addRow([ddc,n_count])
                i = i + 1
            else:
                break
        #END FOR
        del tmp_rows
        #-----------------------------

        chart = PieChart(table)

        view = DialogViewer()
        view.setGraph(chart)

        if type == 1:
            view.setWindowTitle('DDC Top 20 [NN]')
        else:
            view.setWindowTitle('DDC Top 20 [NNN]')

        view.resize(480, 360)

        view.exec_()

        try:
            del table
            del chart
            del view
        except:
            pass

    #-----------------------------------------------------------------------------------------
    #-----------------------------------------------------------------------------------------
#-------------------------------------------------------------------------------------------------------------------------------------
#-------------------------------------------------------------------------------------------------------------------------------------
#-------------------------------------------------------------------------------------------------------------------------------------
#-------------------------------------------------------------------------------------------------------------------------------------
#-------------------------------------------------------------------------------------------------------------------------------------
#-------------------------------------------------------------------------------------------------------------------------------------
#END of ui.py
