import openpyxl
import os
import warnings
from lxml import etree
from datetime import datetime

# Отключаем предупреждения openpyxl
warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')

def excel_to_xml_educated_person(excel_file, xml_file):
    """
    Преобразует данные из Excel в XML по схеме educated_person_import_v1.0.9.xsd
    """
    try:
        # Загружаем Excel файл
        workbook = openpyxl.load_workbook(excel_file)
        sheet = workbook.active
        
        # Создаем корневой элемент
        root = etree.Element('RegistrySet')
        
        # Пропускаем заголовки (первая строка)
        for row in sheet.iter_rows(min_row=2, values_only=True):
            # Пропускаем пустые строки
            if all(cell is None for cell in row):
                continue
                
            # Создаем запись RegistryRecord
            record = etree.SubElement(root, 'RegistryRecord')
            
            # Блок Worker
            worker = etree.SubElement(record, 'Worker')
            etree.SubElement(worker, 'LastName').text = str(row[0])  # Фамилия
            etree.SubElement(worker, 'FirstName').text = str(row[1])  # Имя
            etree.SubElement(worker, 'MiddleName').text = str(row[2])  # Отчество
            
            etree.SubElement(worker, 'Snils').text = str(row[3]) # СНИЛC
            etree.SubElement(worker, 'Position').text = str(row[4]) # Должность
            
            # Работодатель
            etree.SubElement(worker, 'EmployerInn').text = str(row[10])
            etree.SubElement(worker, 'EmployerTitle').text = str(row[5])
            
            # Блок Organization
            organization = etree.SubElement(record, 'Organization')
            etree.SubElement(organization, 'Inn').text = str(row[11])  
            etree.SubElement(organization, 'Title').text = str(row[12])  
            
            # Блок Test
            test = etree.SubElement(record, 'Test') # Оценка
            if str(row[6]) == "Удовлетворительно":
                test.set('isPassed', "true")
            if str(row[6]) == "Неудовлетворительно":
                test.set('isPassed', "false")

            test.set('learnProgramId', str(int(row[13])))  # ID программы обучения
            
            # Дата теста (преобразуем в правильный формат)
            if isinstance(row[7], datetime):
                etree.SubElement(test, 'Date').text = row[7].strftime('%Y-%m-%d')
            else:
                etree.SubElement(test, 'Date').text = str(row[7])
            
            etree.SubElement(test, 'ProtocolNumber').text = str(row[8])
            etree.SubElement(test, 'LearnProgramTitle').text = str(row[9])
        
        # Создаем XML дерево
        tree = etree.ElementTree(root)
        
        # Сохраняем с форматированием
        tree.write(xml_file, 
                   pretty_print=True, 
                   encoding='utf-8', 
                   xml_declaration=True)
        
        print(f"XML файл успешно создан: {xml_file}")
        print(f"Обработано записей: {len(root)}")
        
    except FileNotFoundError:
        print(f"Ошибка: Файл {excel_file} не найден!")
    except Exception as e:
        print(f"Ошибка при обработке: {e}")

# Определяем путь к директории скрипта
def get_script_directory():
    """Возвращает абсолютный путь к директории, где находится скрипт"""
    path = os.path.dirname(os.path.realpath(__file__))
    my_path, base_name = os.path.split(path)
    print(f"Путь к скрипту: {path}")

    return path

# Основная программа
if __name__ == "__main__":
    # Получаем путь к директории скрипта
    script_dir = get_script_directory()
    
    # Формируем полные пути к файлам
    excel_file_path = os.path.join(script_dir, 'Python_reestr-protocol.xlsx')
    xml_file_path = os.path.join(script_dir, 'XML_reestr-protocol.xml')
    
    # Проверяем существование Excel файла
    if os.path.exists(excel_file_path):
        print("Запускаем конвертацию...")
        excel_to_xml_educated_person(excel_file_path, xml_file_path)
    else:
        print("Файл data.xlsx не найден в директории скрипта!")
        print("Доступные файлы:")
        for file in os.listdir(script_dir):
            if file.endswith('.xlsx'):
                print(f" - {file}")