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

"""Saver_Ausw_Erspar v1.02.3.5 _py31404 Git Version"""

# %% === Imports
# benötigte Packages
import tkinter as tk
from pathlib import Path
from tkinter import filedialog

import xlwings as xw


# REF: https://docs.xlwings.org/en/stable/debugging.html
# REF: https://docs.xlwings.org/en/stable/debugging.html


# ## === Variables Global anlegen
file_path = Path(__file__).parent / 'hello.txt'


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


# ? =============================================
# %% === xw_main_cmd            # ?=> Muster aus xlwings-Vorlage
def xw_main_cmd():
    """Funktion aus Excel heraus ausführen

    hier liegt die eigendliche Funktion, die keiner beeinflussen kann
    """
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet['A1'].value == 'Hello xlwings!':
        sheet['A1'].value = 'Bye xlwings!'
        print('myproject - xw_main_cmd: ' + 'Bye xlwings!' + 'geschrieben')
    else:
        sheet['A1'].value = 'Hello xlwings!'
        print('myproject - xw_main_cmd: ' + 'Hello xlwings!' + 'geschrieben')

    # * MSG an Excel
    sheet['A3'].value = 'Called from [ExcelName_py.xw_main_req:]'

    excel_value = sheet.range('A1').value
    with open(file_path, 'w') as file:
        file.write(f'Called from xw_main_cmd: {excel_value}')

    # displaytext = wb.macro('module1.pyDisplayText')
    # displaytext('I was called from Python [from xw_main_cmd]')

    return


# %% === xw_ahoy_cmd            # ?=> Muster aus xlwings-Vorlage
@xw.func
def xw_ahoy_cmd():
    """Funktion aus Excel heraus ausführen

    hier liegt die eigendliche Funktion, die keiner beeinflussen kann
    """
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet['A1'].value == 'Hello xlwings!':
        sheet['A1'].value = 'Bye xlwings!'
        print('myproject - xw_ahoy_cmd: ' + 'Bye xlwings!' + 'geschrieben')
    else:
        sheet['A1'].value = 'Hello xlwings!'
        print('myproject - xw_ahoy_cmd: ' + 'Hello xlwings!' + 'geschrieben')

    # * MSG an Excel
    sheet['A3'].value = 'Called from [ExcelName_py.xw_ahoy_req:]'

    excel_value = sheet.range('A1').value
    with open(file_path, 'w') as file:
        file.write(f'Called from xw_ahoy_cmd: {excel_value}')

    # displaytext = wb.macro('module1.pyDisplayText')
    # displaytext('I was called from Python [from xw_ahoy_cmd]')

    return


# ? =============================================
# ? =============================================
# ## == Erspar-Funktionen
# %% === gen_Column_List
def gen_Column_List(list_Col_A, row, sht):
    """Spalte in Liste sammeln

    Args:
        list_Col_A (list): Liste in der die Daten gesammelt wrden
        row (int): Spalte die gelesen werden soll
        sht (sheet): Tabellen Blatt aus dem gelesen werden soll

    Returns:
        _type_: _description_
    """
    last_row = sht.used_range.last_cell.row
    last_col = sht.used_range.last_cell.column
    if not row:
        row = 1

    for i in range(row, last_row + 1):
        value_tmp = sht.range((i, row)).value
        list_Col_A.append(value_tmp)

    return list_Col_A


# %% === 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


# %% === xw_from_cell_select_dir
def xw_from_cell_select_dir() -> str:
    cwd = Path.cwd()
    parent = cwd.parent
    str_parent = str(parent)
    print(f'xw_from_cell_select_dir - parent:\n>> {parent}')

    # Root-Fenster verstecken
    root = tk.Tk()
    root.withdraw()  # Versteckt das Hauptfenster, falls nur der Dialog nötig ist

    # Datei-Dialog öffnen
    title_dlg = 'Verzeichnis auswählen'
    initial_dir = parent

    selected_dir = filedialog.askdirectory(
        title=title_dlg,
        parent=root,
        initialdir=initial_dir,
        mustexist=True,
    )

    print('\n>>> Ausgewähltes Verzeichnis:', selected_dir)

    return selected_dir


# %% === xw_from_cell_select_file
def xw_from_cell_select_file(cwd_dir) -> str:
    cwd = Path.cwd()
    parent = cwd.parent
    str_parent = str(parent)
    print(f'xw_from_cell_select_file - parent:\n>> {parent}')

    # Root-Fenster verstecken
    root = tk.Tk()
    root.withdraw()  # Versteckt das Hauptfenster, falls nur der Dialog nötig ist

    cwd_dir = Path(cwd_dir)

    # Datei-Dialog öffnen
    title_dlg = 'Datei auswählen'
    initial_dir = cwd_dir
    initial_file = ''
    # file_types = [('Excel-Dateien', '*.xlsx .csv'), ('CSV-Dateien', '*.csv'), ('Alle Dateien', '*.*')]
    file_types = [('Export_VIDA-Datas', '*.xlsx .csv'), ('CSV-Dateien', '*.csv'), ('Alle Dateien', '*.*')]

    select_file_name = filedialog.askopenfilename(
        title=title_dlg,
        parent=root,
        initialdir=initial_dir,
        initialfile=initial_file,
        filetypes=file_types,
    )

    print('\n>>> Ausgewählte Datei:', select_file_name)

    return select_file_name


# %% === xw_from_cell_cmd
@xw.func
def xw_from_cell_cmd():
    """Funktion aus Excel heraus ausführen

    hier liegt die eigendliche Funktion, die keiner beeinflussen kann
    """
    wb = xw.Book.caller()
    sheet = wb.sheets[0]

    # * MSG an Excel
    sheet['A3'].value = 'Called from [ExcelName_py.xw_from_cell_req:]'

    row = sheet['D1'].options(numbers=int).value
    col = sheet['D2'].options(numbers=int).value

    excel_value = sheet.range(row, col).value
    print(excel_value)

    if excel_value != '[Select_Verz]' and excel_value != '[Select_file]':
        return

    sheet.range(row, col + 1).value = 'in Arbeit'
    sheet.range(row, col - 1).value = excel_value
    # sheet.cells(row, col - 1).value = excel_value     # ? range oder cells ist gleichwertig

    print(excel_value)
    # sleep(0.2)

    sheet['C25'].value = f'[from_cell_cmd] row: {row}, col: {col}'
    print(f'[from_cell_cmd] row: {row}, col: {col}')

    pass

    if excel_value == '[Select_Verz]':
        print('starte Verzeichnis')
        selected_dir = xw_from_cell_select_dir()
        if selected_dir:
            selected_dir = selected_dir.replace('/', '\\')
            sheet.range(row, col - 2).value = selected_dir

    if excel_value == '[Select_file]':
        print('starte FileNames')
        rcdir2 = sheet.range(26, 2).value
        selected_file_name = xw_from_cell_select_file(rcdir2)
        if selected_file_name:
            selected_file_name = selected_file_name.replace('/', '\\')
            cwd_file = Path(selected_file_name)
            selected_file_name2 = cwd_file.name

            sheet.range(row, col - 2).value = selected_file_name2
        sheet.range(row, col + 1).value = 'Fertig'

    return


# %% === main_app_init
def main_app_init():
    """___

    ___
    """

    print('Start -> def main_app_init')
    print('Hallo => hier kommt das Programm')

    print('Bitte eine Zahl eingeben')
    print('Eingabe der Zahl war: ' + input('Bitte zahl eingeben:  '))

    print('Stopp -> def main_app_init.py')

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


# %% === xw_main_app_extern_cmd
@xw.func
def xw_main_app_extern_cmd():
    """___Starten von Excel aber dann auf neue .py übergeben

    dient nur aus Aufrufverteiler
    """
    import eSaver_Ausw_1_xlsm_Init_reads as eS_init_reads

    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet['A1'].value == 'Hello xlwings!':
        sheet['A1'].value = 'Bye xlwings!'
        print('myproject_cmd - main_app_extern: ' + 'Bye xlwings!' + 'geschrieben')
    else:
        sheet['A1'].value = 'Hello xlwings!'
        print('myproject_cmd - main_app_extern: ' + 'Hello xlwings!' + 'geschrieben')

    # * MSG an Excel
    sheet['A3'].value = 'Called from [ExcelName_py.xw_app_extern_req:]'

    excel_value = sheet.range('A1').value
    with open(file_path, 'w') as file:
        file.write(f'Called from xw_main_app_extern_cmd: {excel_value}')

    eS_init_reads.main_app_init()

    return


# %% === xw_main_csv_to_excel_cmd
@xw.func
def xw_main_csv_to_excel_cmd():
    """Starten von Excel aber dann auf neue .py übergeben

    dient nur aus Aufrufverteiler
    """
    import eSaver_Ausw_2_csv_to_excel_and_scan_kw_v as eS_csv_2_excel
    import eSaver_Ausw_2_xlsx_to_excel_and_scan_kw_v as eS_xlsx_2_excel

    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet['A1'].value == 'Hello xlwings!':
        sheet['A1'].value = 'Bye xlwings!'
        print('myproject_cmd - main_csv_to_excel: ' + 'Bye xlwings!' + 'geschrieben')
    else:
        sheet['A1'].value = 'Hello xlwings!'
        print('myproject_cmd - main_csv_to_excel: ' + 'Hello xlwings!' + 'geschrieben')

    # * MSG an Excel
    sheet['A3'].value = 'Called from [ExcelName_py.xw_main_csv_to_excel_req:]'

    excel_value = sheet.range('A1').value
    with open(file_path, 'w') as file:
        file.write(f'Called from xw_main_csv_to_excel_cmd: {excel_value}')

    # Grid-Vis oder eCon scannen
    list_Col_A = []
    gen_Column_List(list_Col_A, 1, sheet)

    scan_col = 2
    strSearch = ''
    strSearch = 'VIDA-Version'
    if strSearch:
        scan_row = liste_nach_name_scan(list_Col_A, strSearch)
        if scan_row:
            strVIDA_Version = sheet.range((scan_row, scan_col)).value

    if strVIDA_Version == 'GridVis':
        print('GridVis-Version')
        eS_csv_2_excel.main()

    if strVIDA_Version == 'eCon':
        print('eCon-Version')
        eS_xlsx_2_excel.main()

    return


# %% === xw_main_ImpF_to_Ausw_cmd
@xw.func
def xw_main_ImpF_to_Ausw_cmd():
    """Starten von Excel aber dann auf neue .py übergeben

    dient nur aus Aufrufverteiler
    """
    import eSaver_Ausw_3_excel_Imp_to_excel_Ausw as eS_Imp_to_Ausw

    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet['A1'].value == 'Hello xlwings!':
        sheet['A1'].value = 'Bye xlwings!'
        print('myproject_cmd - main_ImpF_to_Ausw: ' + 'Bye xlwings!' + 'geschrieben')
    else:
        sheet['A1'].value = 'Hello xlwings!'
        print('myproject_cmd - main_ImpF_to_Ausw: ' + 'Hello xlwings!' + 'geschrieben')

    # * MSG an Excel
    sheet['A3'].value = 'Called from [ExcelName_py.xw_main_ImpF_to_Ausw_req:]'

    excel_value = sheet.range('A1').value
    with open(file_path, 'w') as file:
        file.write(f'Called from xw_main_ImpF_to_Ausw_cmd: {excel_value}')

    eS_Imp_to_Ausw.main()

    return


# ## ========== Testen eSaver_Export_VIDA_Ausw_Erspar_SVB ==========
# %% === Start __main__
if __name__ == '__main__':
    """Testen der funktionen con VSCode aus.

    ___
    """
    xw.Book('eSaver_Export_VIDA_Ausw_Erspar_SVB.xlsm').set_mock_caller()

    pass  # debug

    xw_main_app_extern_cmd()  # type: ignore[call-arg]

    pass  # debug


# %%
# # EOF                   // End of File
