136 lines
4.7 KiB
Python
136 lines
4.7 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
"""
|
|
KremplImport GUI - Desktop CSV Import Tool für Intelectra
|
|
Version: Full-Tunnel-Mode™
|
|
"""
|
|
|
|
import tkinter as tk
|
|
from tkinter import filedialog, messagebox, scrolledtext
|
|
import csv
|
|
import os
|
|
from dotenv import load_dotenv
|
|
from datetime import datetime
|
|
import paramiko
|
|
from scp import SCPClient
|
|
import threading
|
|
|
|
load_dotenv()
|
|
|
|
# DB-Konfiguration aus .env-Datei
|
|
DB_CONFIG = {
|
|
'host': os.getenv('DB_HOST', 'localhost'),
|
|
'port': int(os.getenv('DB_PORT', 3306)),
|
|
'user': os.getenv('DB_USER', 'root'),
|
|
'password': os.getenv('DB_PASSWORD', ''),
|
|
'database': os.getenv('DB_NAME', 'webshop-sql'),
|
|
'autocommit': True
|
|
}
|
|
|
|
SSH_CONFIG = {
|
|
'ssh_host': os.getenv('SSH_HOST', 'remotehost'),
|
|
'ssh_user': os.getenv('SSH_USER', 'user'),
|
|
'ssh_password': os.getenv('SSH_PASSWORD', 'password'),
|
|
'remote_path': os.getenv('REMOTE_PATH', '/tmp/artikel_Export.csv'),
|
|
'remote_script': os.getenv('REMOTE_SCRIPT', '/opt/import/import_script.py')
|
|
}
|
|
|
|
REQUIRED_COLUMNS = {
|
|
'id', 'altenr', 'originalnummer', 'navisionid', 'zolltarifnummer',
|
|
'marke', 'ersatzteilvertreiber', 'ean', 'ersatzteilvorwaerts',
|
|
'ersatzteilvorwaertsnavisionid'
|
|
}
|
|
|
|
class KremplImporterApp:
|
|
def __init__(self, master):
|
|
self.master = master
|
|
self.master.title("KremplImport GUI (Full-Tunnel-Mode™)")
|
|
self.master.geometry("700x550")
|
|
|
|
self.csv_path = tk.StringVar()
|
|
|
|
tk.Label(master, text="1. CSV-Datei auswählen:").pack(pady=(10, 0))
|
|
tk.Entry(master, textvariable=self.csv_path, width=80).pack(padx=10)
|
|
tk.Button(master, text="Durchsuchen", command=self.select_file).pack(pady=5)
|
|
|
|
tk.Button(master, text="CSV validieren", command=self.validate_csv).pack(pady=10)
|
|
tk.Button(master, text="Hochladen & Import starten (Remote)", command=self.upload_and_import).pack(pady=5)
|
|
|
|
tk.Label(master, text="Protokoll:").pack(pady=(10, 0))
|
|
self.log_output = scrolledtext.ScrolledText(master, height=20)
|
|
self.log_output.pack(padx=10, fill='both', expand=True)
|
|
|
|
def log(self, msg):
|
|
self.log_output.insert(tk.END, msg + '\n')
|
|
self.log_output.see(tk.END)
|
|
|
|
def select_file(self):
|
|
file_path = filedialog.askopenfilename(filetypes=[("CSV files", "*.csv")])
|
|
if file_path:
|
|
self.csv_path.set(file_path)
|
|
self.log(f"Datei ausgewählt: {file_path}")
|
|
|
|
def validate_csv(self):
|
|
path = self.csv_path.get()
|
|
if not os.path.exists(path):
|
|
messagebox.showerror("Fehler", "Datei nicht gefunden!")
|
|
return False
|
|
|
|
try:
|
|
with open(path, 'r', encoding='utf-8') as f:
|
|
reader = csv.reader(f, delimiter=';')
|
|
headers = next(reader)
|
|
missing = REQUIRED_COLUMNS - set(headers)
|
|
if missing:
|
|
self.log(f"FEHLENDE SPALTEN: {', '.join(missing)}")
|
|
messagebox.showerror("CSV ungültig", "Spalten fehlen!")
|
|
return False
|
|
self.log(f"CSV validiert. Spalten ok: {', '.join(headers)}")
|
|
return True
|
|
except Exception as e:
|
|
self.log(f"Fehler beim Validieren: {e}")
|
|
return False
|
|
|
|
def upload_and_import(self):
|
|
if not self.validate_csv():
|
|
return
|
|
|
|
self.log("[🔄] Starte Tunnel-Upload-Prozess...")
|
|
threading.Thread(target=self.run_remote_process).start()
|
|
|
|
def run_remote_process(self):
|
|
try:
|
|
ssh = paramiko.SSHClient()
|
|
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
|
|
ssh.connect(
|
|
hostname=SSH_CONFIG['ssh_host'],
|
|
username=SSH_CONFIG['ssh_user'],
|
|
password=SSH_CONFIG['ssh_password']
|
|
)
|
|
|
|
with SCPClient(ssh.get_transport()) as scp:
|
|
scp.put(self.csv_path.get(), SSH_CONFIG['remote_path'])
|
|
self.log(f"[📤] Datei hochgeladen nach {SSH_CONFIG['remote_path']}")
|
|
|
|
cmd = f"python3 {SSH_CONFIG['remote_script']} {SSH_CONFIG['remote_path']}"
|
|
self.log(f"[🚀] Starte Remote-Skript: {cmd}")
|
|
stdin, stdout, stderr = ssh.exec_command(cmd)
|
|
output = stdout.read().decode()
|
|
error = stderr.read().decode()
|
|
|
|
if output:
|
|
self.log(f"[✅ OUTPUT]:\n{output}")
|
|
if error:
|
|
self.log(f"[⚠️ ERROR]:\n{error}")
|
|
|
|
ssh.close()
|
|
|
|
except Exception as e:
|
|
self.log(f"[❌] Fehler beim Remote-Vorgang: {e}")
|
|
messagebox.showerror("Fehler", str(e))
|
|
|
|
if __name__ == '__main__':
|
|
root = tk.Tk()
|
|
app = KremplImporterApp(root)
|
|
root.mainloop()
|