consolidator

1
import glob
2
import ntpath
3
import os
4
import pandas as pd
5
from MyConfig import MyConfig
6
from OraWorker import OraWorker
7

8
cur_path = os.path.dirname(os.path.realpath(__file__))
9
settings = MyConfig(f"{cur_path}\\config.json")
10
user = settings.get("OracleConfig",defaultValue=None)["user"]
11
passw = settings.get("OracleConfig",defaultValue=None)["pass"]
12
dsn = settings.get("OracleConfig",defaultValue=None)["dsn"]
13
cl = settings.get("OracleConfig",defaultValue=None)["client"]
14
ora = OraWorker(user,passw,dsn,cl)
15

16
def vm_handler(row:pd.Series)->pd.Series:
17
    try:
18
        row["Вид материала в SAP ERP"] = str(row["Вид материала в SAP ERP"])[0:4]
19
        check = ora.product_check("-",str(row["ID в SAP ERP VMZ"]))
20
        row["Укрупнено в продукт УП"] = check[0]
21
        print(f"check: {check[0]}")
22
    except Exception as exp:
23
        row["Полное наименование материала"]=f"{exp}"
24
        row["Укрупнено в продукт УП"] = "-"
25
    finally:
26
        return row
27

28
if __name__=="__main__":
29
    try:
30

31
        pattern="D:\\work\\Укрупнение\\current\\data\\2024*\\*.xlsx"
32
        dump_dir="D:\\work\\Укрупнение\\current\\debug_data\\"
33
        exclude_pattern="нение_"
34
        neuro_columns=["ID в SAP ERP VMZ", "Вид материала в SAP ERP","Полное наименование материала"]
35
        files=[f for f in glob.glob(pattern) if exclude_pattern not in f] if exclude_pattern is not None else [f for f in glob.glob(pattern)]
36
        data={}
37
        list_data=[]
38
        for f in files:
39
            short_name = ntpath.basename(f)
40
            print(f"{f} -> {short_name} ---------------")
41
            xl = pd.ExcelFile(f)
42
            for sheet_name in xl.sheet_names:
43
                df=xl.parse(sheet_name=sheet_name)
44
                key = f"{dump_dir}[{short_name.replace('.xlsx','')}][{sheet_name}].xlsx"
45
                if not df.empty:
46
                    if key not in data.keys():
47
                        df=df[neuro_columns].apply(vm_handler,axis=1)
48
                        list_data.append(df) 
49
        result = pd.concat(list_data)
50
        result = result.drop_duplicates()
51
        result.to_excel(f"{dump_dir}neuro_dump.xlsx",index=False)
52
        print(f"{result.shape} -> done")
53
        ora.close()   
54
    except Exception as exp:
55
        print(f"error: {exp}")