gctools/database/gebos_import.py

from pathlib import Path
import subprocess

cfg = {
    "schema": "temp",
    "scripts_dir": "E:\\GlobalCube\\Tasks\\scripts",
    "query_dir": "E:\\GlobalCube\\System\\NAVISION\\SQL\\exec",
}
target_tables = {
    "account2": "GEBOS_Account",
    "activity2": "GEBOS_Activity",
    "activitytype2": "GEBOS_Activity_Type",
    "costcenter2": "GEBOS_Cost_Center",
    "department2": "GEBOS_Department",
    "employee2": "GEBOS_Employee",
    "employeeaccount2": "GEBOS_Employee_Account",
    "employmenttype2": "GEBOS_Employment_Type",
    "timemodel2": "GEBOS_Time_Model",
    "timetracking2": "GEBOS_Time_Tracking",
}


def task(args):
    return subprocess.Popen(args, stdout=subprocess.DEVNULL).wait()


def gebos_import(csv_file, target_table):
    bulkcopy = str(Path(cfg["scripts_dir"]) / "bcp_import.bat")
    sqlquery = str(Path(cfg["scripts_dir"]) / "sqlquery.bat")
    sqlexec = str(Path(cfg["scripts_dir"]) / "sqlexec.bat")
    query_file = target_table + ".sql"

    task(f"{sqlquery} \"TRUNCATE TABLE [{cfg['schema']}].[{target_table}]\" ")
    task(f"{bulkcopy} [{cfg['schema']}].[{target_table}] \"{csv_file}\" ")
    task(f'{sqlexec} {query_file} "{csv_file}" ')


def import_dir(base_dir=None):
    if base_dir is None:
        base_dir = "E:\\GEBOS\\staging"

    for csv_file in Path(base_dir).glob("*.csv"):
        print(csv_file.name)
        for key, table in target_tables.items():
            if not csv_file.name.startswith(key):
                continue
            gebos_import(str(csv_file), table)
            csv_file.unlink()


if __name__ == "__main__":
    import_dir()