#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""eSaver_utils_read_excel_to_App_data"""

# %% === Imports
# benötigte Packages
import logging
from datetime import datetime
from pathlib import Path
from time import sleep
from time import time

import xlwings as xw
# import openpyxl as ox

from eSaver_Data_App import App_Data

from eSaver_utils import logger_Off
from eSaver_utils import logger_On
from eSaver_utils import logger_Settings

# ## === Variables Global anlegen
# ## === Variables Global anlegen
app_data = App_Data()


pass  # ## ==========


# ? =============================================
# todo: read_excel_init in eigne datei überführen
# %% === Liste_nach_Name_suchen
def liste_nach_name_scan(listToScan: list, scan: str) -> int:
    """Liste wird nach Inhalt gescannt

    Args:
        listToScan (list): zu durchsuchende Liste
        scan (str): zu suchender Text

    Returns:
        int: Indes vom Text in der liste
            0 = Text nicht gefunden
    """
    if scan in listToScan:
        cnt = 1 + listToScan.index(scan)
        return cnt

    return 0


# * =============================================
# %% === read_excel_inits_exportFiles
def read_excel_inits_exportFiles(list_Col_A: list, sht) -> int:
    """schreiben der Export-Dateien in add_Data

    (Excel)ExportFiles => app_data.tupl_strExportFile

    Args:
        list_Col_A (list):  Spalte, die durchsucht werden soll
        sht (Sheets): Excel-Tabellen in der gesucht wird

    Returns:
        int: Anzahl der gefundenen Positionen
    """
    # Tuble in Liste umwandeln
    list_strExportFile = list(app_data.tupl_strExportFile)
    list_Postfix = list(app_data.list_Postfix)

    # scan's Export-VIDA Dateien
    scan_col = app_data.intExcelInitValue
    strPraefix = 'Export_File'

    strSearch = ''
    cnt = 0
    for i in range(len(list_Postfix)):
        # scannen der Excel-Datei
        strSearch = strPraefix + list_Postfix[cnt]
        scan_row = liste_nach_name_scan(list_Col_A, strSearch)
        if scan_row:
            list_strExportFile[i] = sht.range((scan_row, scan_col)).value
        else:
            print('Wert NICHT gefunden')

            wb = xw.Book.caller()
            sheet = wb.sheets[0]
            msg = f'Kennung "{strSearch}" \n wurde nicht gefunden'
            displaytext = wb.macro('module1.pyDisplayText')
            displaytext(f'Fehler in Excel-Datei\n[Export-VIDA Main] Spalte "A": \n {msg}')
            exit(-1)

        cnt += 1

    # Liste in Tuble zurück schreiben
    app_data.tupl_strExportFile = list(list_strExportFile)

    return cnt


# %% === read_excel_inits_importFiles
def read_excel_inits_importFiles(list_Col_A: list, sht) -> int:
    """schreiben der Import-Dateien in add_Data

    (Excel)ImportFiles => app_data.tupl_strImportFile

    Args:
        list_Col_A (list):  Spalte, die durchsucht werden soll
        sht (Sheets): Excel-Tabellen in der gesucht wird

    Returns:
        int: Anzahl der gefundenen Positionen
    """
    # Tuble in Liste umwandeln
    list_strImportFile = list(app_data.tupl_strImportFile)
    list_Postfix = list(app_data.list_Postfix)

    # scan's Import-VIDA Dateien
    scan_col = app_data.intExcelInitValue
    strPraefix = 'Import_File'

    strPostfix_File = '_Imp.xlsx'
    strSearch = ''
    cnt = 0

    strSearch = ''
    cnt = 0
    for i in range(len(list_Postfix)):
        # scannen der Excel-Datei
        strSearch = strPraefix + list_Postfix[cnt]
        scan_row = liste_nach_name_scan(list_Col_A, strSearch)
        if scan_row:
            list_strImportFile[i] = sht.range((scan_row, scan_col)).value
        else:
            print('Wert NICHT gefunden')

            wb = xw.Book.caller()
            sheet = wb.sheets[0]
            msg = f'Kennung "{strSearch}" \n wurde nicht gefunden'
            displaytext = wb.macro('module1.pyDisplayText')
            displaytext(f'Fehler in Excel-Datei\n[Export-VIDA Main] Spalte "A": \n {msg}')
            exit(-1)

        cnt += 1

    # Liste in Tuble zurück schreiben
    app_data.tupl_strImportFile = list(list_strImportFile)

    return cnt


# %% === read_excel_inits_paths_and_names
def read_excel_inits_paths_and_names(list_Col_A: list, sht) -> int:
    """schreiben der Import-Dateien in add_Data

    (Excel)ImportFiles => app_data.tupl_strImportFile

    Args:
        list_Col_A (list):  Spalte, die durchsucht werden soll
        sht (Sheets): Excel-Tabellen in der gesucht wird

    Returns:
        int: Anzahl der gefundenen Positionen
    """
    # scan's Import-VIDA Dateien
    scan_col = app_data.intExcelInitValue
    cnt = 0

    # strPath_Export_Files
    strSearch = 'Path_Export_Files'

    if strSearch:
        # scannen der Excel-Datei
        scan_row = liste_nach_name_scan(list_Col_A, strSearch)
        if scan_row:
            strTmp = sht.range((scan_row, scan_col)).value
            app_data.strPath_Export_Files = strTmp
        else:
            print('Wert NICHT gefunden')

            wb = xw.Book.caller()
            sheet = wb.sheets[0]
            msg = f'Kennung "{strSearch}" \n wurde nicht gefunden'
            displaytext = wb.macro('module1.pyDisplayText')
            displaytext(f'Fehler in Excel-Datei\n[Export-VIDA Main] Spalte "A": \n {msg}')
            exit(-1)

        cnt += 1

    pass

    # strPath_Import_Files
    strSearch = 'Path_Import_Files'

    if strSearch:
        # scannen der Excel-Datei
        scan_row = liste_nach_name_scan(list_Col_A, strSearch)
        if scan_row:
            strTmp = sht.range((scan_row, scan_col)).value
            app_data.strPath_Import_Files = strTmp
        else:
            print('Wert NICHT gefunden')

            wb = xw.Book.caller()
            sheet = wb.sheets[0]
            msg = f'Kennung "{strSearch}" \n wurde nicht gefunden'
            displaytext = wb.macro('module1.pyDisplayText')
            displaytext(f'Fehler in Excel-Datei\n[Export-VIDA Main] Spalte "A": \n {msg}')
            exit(-1)

        cnt += 1

    pass

    # strXlsm_FileNameDest
    strSearch = 'PJJ0X0XX_Ersparnis_File'

    if strSearch:
        # scannen der Excel-Datei
        scan_row = liste_nach_name_scan(list_Col_A, strSearch)
        if scan_row:
            strTmp = sht.range((scan_row, scan_col)).value
            app_data.strXlsm_FileNameDest = strTmp
        else:
            print('Wert NICHT gefunden')

            wb = xw.Book.caller()
            sheet = wb.sheets[0]
            msg = f'Kennung "{strSearch}" \n wurde nicht gefunden'
            displaytext = wb.macro('module1.pyDisplayText')
            displaytext(f'Fehler in Excel-Datei\n[Export-VIDA Main] Spalte "A": \n {msg}')
            exit(-1)

        cnt += 1

    pass

    return cnt


# %% === read_excel_inits
def read_excel_inits():
    """lesen der excel-Init-Datei

    alle inits Import, Export
    ___
    """
    tStart = time()  # todo: Zeitmessung
    strTimeFtime = datetime.now().strftime(app_data.strftime_format)
    logging.info(f'utils.read_excel_inits - started                             / Start {strTimeFtime}')

    print('Start -> read_excel_inits')

    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    sheet = wb.sheets['Export-VIDA Main']

    sheet['A6'].value = 'call_2: read_excel_inits in Arbeit!'
    print('eSaver_Ausw_2_xxx_to_excel - read_excel_inits: ' + 'excel_read_inits in Arbeit')

    print('wait for XLwings')
    sleep(1)  # warten bis excel geladen wurde
    pass

    # max_count abfragen
    sht = wb.sheets[0]
    last_row = sht.used_range.last_cell.row
    last_col = sht.used_range.last_cell.column
    sht.range('A7').value = f'last_row: {last_row}'
    sht.range('A8').value = f'last_col: {last_col}'

    # Loop über Spalte
    list_Col_A = []
    for i in range(1, last_row + 1):
        value_tmp = sht.range((i, 1)).value
        list_Col_A.append(value_tmp)

    print(list_Col_A)

    # Init-Werte Export-VIDA einlesen
    cnt_export = read_excel_inits_exportFiles(list_Col_A, sht)
    print(cnt_export)
    pass

    # Init-Werte Import-VIDA einlesen
    cnt_import = read_excel_inits_importFiles(list_Col_A, sht)
    print(cnt_import)
    pass

    cnt_paths_ans_names = read_excel_inits_paths_and_names(list_Col_A, sht)
    print(cnt_paths_ans_names)

    pass

    sheet['A6'].value = 'call_2: read_excel_inits Fertig!'
    print('eSaver_Ausw_2_xxx_to_excel - read_excel_inits: ' + 'excel_read_inits Fertig')

    pass

    print('Stopp -> def read_excel_inits')

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    logging.info(f'utils.read_excel_inits - finished                            / tDauer {tDauer}')

    return


# todo: read_excel_init in eigne datei überführen
# ? =============================================
# %% === main_app_read_Init
def main_app_read_Init(app_data_tmp: App_Data, debug=False):

    global app_data

    app_data = app_data_tmp

    if not debug:
        xw.Book.caller()
    else:
        strXlsm_FileNameMain = app_data.strXlsm_FileNameMain
        xw.Book(strXlsm_FileNameMain).set_mock_caller()

    read_excel_inits()

    app_data_tmp = app_data

    return app_data_tmp


# %% === main
def main():
    """main Programm

    INIT Eintagungen aus Excel lesen
    in App_Data eintragen
    """
    tStart = time()  # todo: Zeitmessung

    app_data = App_Data()

    app_data.strTimeFtime = datetime.now().strftime('%Y%m%d_%H%M')

    # ## === Path-Variablen holen ===
    cwd = Path.cwd()
    parent = cwd.parent

    # ========== logger aktivieren ==========
    # ?: Hier sollte der Dateiname aus der App_Data.xx gelesen werden.
    logger_On(parent, 'x_eSaver_util_read_excel_to_App_data_Log_')
    logger_Settings()

    tStart2 = time()  # todo: Zeitmessung
    tDauer = tStart2 - tStart
    logging.info(f'main_testen - started                                        / tDauer {tDauer}')

    pass

    # ========== main_app ==========
    main_app_read_Init(app_data)

    pass

    tEnde = time()  # todo: Zeitmessung
    tDauer = tEnde - tStart
    logging.info(f'main_testen - stopped                                        / tDauer {tDauer}')

    logger_Off()

    return


# %% === Start __main__ ==========
if __name__ == '__main__':
    """Einsprung Start der Anlage

    """
    # ## === Variables Global anlegen
    pass

    main()

    pass  # ## ==========

# EOF
