consolidator

1
import os
2
import sys
3
import time
4
from PySide6.QtGui import QIcon,QTextCursor
5
from PySide6.QtCore import (Slot,QObject,Signal,QRunnable,QThreadPool,QSettings)
6
from PySide6.QtWidgets import (
7
    QLineEdit,
8
    QWidget, 
9
    QMainWindow, 
10
    QPushButton,
11
    QFileDialog,
12
    QMessageBox,
13
    QPlainTextEdit,
14
    QDialog,
15
    QVBoxLayout)
16
from PySide6.QtUiTools import QUiLoader
17
from MyConfig import MyConfig
18
from MyLogger import MyLogger,EmittingStream
19
import pandas as pd
20
import ntpath
21
import warnings
22
from  MainConsolidator import MainConsolidator
23
from OraWorker import OraWorker
24
from TodoWindow import TodoWindow
25
from GuidsWindow import GuidsWindow
26
import pandas_xlwt # не убирать!!!
27
import urllib.request 
28
from http import client
29
from GuidsSync import GuidsSync
30
import traceback
31

32

33
class Semaphore(QObject):
34
    """ Класс-семафор для отслеживания состояния потока """
35
    started = Signal()
36
    finished = Signal()
37

38
class Worker(QRunnable):
39
    """"
40
    Worker thread
41

42
    Inherits from QRunnable to handler worker thread setup, signals and wrap-up.
43

44
    :param callback: The function callback to run on this worker thread. Supplied args and
45
                     kwargs will be passed through to the runner.
46
    :type callback: function
47
    :param args: Arguments to pass to the callback function
48
    :param kwargs: Keywords to pass to the callback function
49

50
    """
51

52
    def __init__(self, fn, *args, **kwargs):
53
        super(Worker, self).__init__()
54
        # Store constructor arguments (re-used for processing)
55
        self.fn = fn
56
        self.args = args
57
        self.kwargs = kwargs
58
        self.semaphore = Semaphore()
59

60
    @Slot()  # QtCore.Slot
61
    def run(self):
62
        """" Initialise the runner function with passed args, kwargs. """
63
        self.semaphore.started.emit()
64
        self.fn(*self.args, **self.kwargs)
65
        self.semaphore.finished.emit()
66

67
class ConsWindow(object):
68

69

70
    def __init__(self,metadata:dict=None,update_flag=False,update_url:str=None) -> None:
71
        try:
72
            self.version:int = 0
73
            self.busy = False
74
            try:
75
                self.cur_path = sys._MEIPASS
76
            except:
77
                self.cur_path = os.path.dirname(os.path.realpath(__file__))
78
                
79
            windowIcon = QIcon(f"{self.cur_path}\\consolidation.ico")
80
            self.output_dir = f"{self.cur_path}\\output\\"
81
            if not os.path.exists(self.output_dir): os.mkdir(self.output_dir)
82
            self.threadpool = QThreadPool()
83
            print(f"Multithreading with maximum {self.threadpool.maxThreadCount()} threads")
84
            loader = QUiLoader()
85
            es = EmittingStream()
86
            self.selected_files = []
87
            self.settings = MyConfig(f"{self.cur_path}\\config.json")
88
            logFile:str = self.settings.get("LogFile",defaultValue="default.log")
89
            consConfig:str = self.settings.get("ConsolidationConfig",defaultValue=f"{self.cur_path}\\consolidation.json")
90
            self.consCfg = MyConfig(consConfig)
91
            self.todoList:list= self.consCfg.get("todo",None)
92
            guidsDir:str = self.settings.get("GuidsDir_",defaultValue=f"{self.cur_path}\\guids") #bug fix GuidsDir -> GuidsDir_
93
            
94
            if not os.path.isdir(guidsDir): guidsDir=f"{self.cur_path}\\guids"
95
            self.guids_path = guidsDir            
96
            self.mlog= MyLogger(__name__, log_file=logFile, outStream=es)
97

98
            # init components
99
            self.MainWindow:QMainWindow = loader.load(f"{self.cur_path}\\ConsWindow.ui", None)
100
            self.MainWindow.setWindowIcon(windowIcon)
101
            if metadata is not None:
102
                prodName = metadata.get("FileDescription","module")
103
                prodVersion = metadata.get("Version","0.0.0.0")
104
                self.MainWindow.setWindowTitle(f"{prodName} ver.{prodVersion}")
105
            self.consoleBox:QPlainTextEdit  = self.MainWindow.consoleBox
106
            self.openFilesButton:QPushButton = self.MainWindow.openFilesButton
107
            self.consButton:QPushButton = self.MainWindow.consButton
108
            self.filesInput:QLineEdit = self.MainWindow.filesInput
109
            self.todoButton:QPushButton = self.MainWindow.todoButton
110
            self.guidsButton:QPushButton = self.MainWindow.guidsButton            
111

112
            # init slot connections
113
            es.textSignal.connect(self.write_to_console)
114
            self.openFilesButton.clicked.connect(self.select_input_files)
115
            self.consButton.clicked.connect(self.start_consolidate)
116
            self.todoButton.clicked.connect(self.show_todo_list)
117
            self.guidsButton.clicked.connect(self.show_guids_window)
118
            self.MainWindow.destroyed.connect(self.app_destroy)
119

120
            if update_flag:
121
                self.mlog.info("Start update procedure")
122
                self.openFilesButton.setEnabled(False)
123
                self.todoButton.setEnabled(False)
124
                self.consButton.setEnabled(False)
125
                self.guidsButton.setEnabled(False)
126
                self.start_download_update(update_url)
127
            else:
128
                # init MainConsolidator
129
                self.consolidator = MainConsolidator(logFile,logStream=es)
130
                self.consolidator.load_guids_x(guidsDir)
131
                self.consolidator.columns = self.consCfg.get("columns",[])                
132
                # init 2 level handlers
133
                self.handlers={}
134
                self.hdict={}
135
                hlist = self.consCfg.get("handlers",None)
136
                for item in hlist:
137
                    module_path = f"{self.cur_path}\\handlers2\\{item['module']}.py"
138
                    with open(module_path,encoding="utf-8",mode="r") as f:
139
                        module_src = f.read()
140
                        self.handlers[item["key"]]=compile(module_src,"","exec")
141
                        self.hdict[item["key"]]=item["module"]
142
                        self.mlog.info(f"{item['key']} : {item['module']}")
143
                self.mlog.info("2 level handlers loaded")
144
                self.checkFile = self.settings.get("CheckFile","")
145

146
                # oracle connect
147
                user = self.settings.get("OracleConfig",defaultValue=None)["user"]
148
                passw = self.settings.get("OracleConfig",defaultValue=None)["pass"]
149
                dsn = self.settings.get("OracleConfig",defaultValue=None)["dsn"]
150
                cl = self.settings.get("OracleConfig",defaultValue=None)["client"]
151
                self.ora = OraWorker(user,passw,dsn,cl)
152
                ora_inf= self.ora.ora_version()
153
                self.mlog.info(f"Соединение с oracle:\n {ora_inf}")
154
                self.mlog.info(f"pid: {os.getpid()}")
155
        except:
156
            self.mlog.error("ConsWindow init error",exc_info=True)
157

158
    def app_destroy(self):
159
        try:
160
            self.ora.close()
161
            self.mlog.info("oracle disconnected")
162
        except:
163
            self.mlog.error("app destroy",exc_info=True)
164

165
    def app_error(self,mess:str):
166
        """ Сообщение об ошибке """
167
        QMessageBox.critical(self.MainWindow,"Ошибка",mess,QMessageBox.StandardButton.Yes,QMessageBox.StandardButton.Yes)
168
        self.mlog.error(mess)
169

170
    def write_to_console(self,mess:str):
171
        """Вывод в консоль приложения
172

173
        Args:
174
            mess (str): Текст сообщения\n
175
        """
176
        try:
177
            self.consoleBox.appendHtml(mess) 
178
        except Exception as exp:
179
            self.app_error(f"write_to_console error: {exp}")
180

181
    def write_char_to_console(self,char:str):
182
        self.consoleBox.moveCursor(QTextCursor.MoveOperation.PreviousCharacter,QTextCursor.MoveMode.KeepAnchor)
183
        self.consoleBox.insertPlainText(char)
184
        # self.consoleBox.setTextCursor(prev_cursor)
185
        
186

187
    def show_todo_list(self):
188
        try:
189
            df = pd.DataFrame(self.consCfg.get("handlers",None))
190
            todoWindow = TodoWindow(df)
191
            if todoWindow.show()==1:
192
                self.consCfg.set("handlers",todoWindow.data.to_dict("records"))
193
                self.consCfg.sync()
194
        except Exception as exp:
195
            self.app_error(f"show_todo_list error: {exp}")
196

197
    def show_guids_window(self):
198
        """ Показать окно справочников """
199
        try:
200
            # gs:GuidsSync=None
201
            # try:
202
            #     remoteHost=self.settings.get("RemoteGuidsHost",None)
203
            #     remoteUser=self.settings.get("RemoteUser",None)
204
            #     remotePass=self.settings.get("RemotePassword",None)
205
            #     remoteDir=self.settings.get("RemoteGuidsDir",None)
206
            #     gs = GuidsSync(remoteHost,remoteUser,remotePass)
207
            #     gs.set_remote_dir(remoteDir)
208
            #     gs.set_local_dir(self.guids_path)
209
            # except Exception as e1: self.mlog.warning("Не удалось инициализировать модуль синхронизации справочников",exc_info=True)
210
            # guidsWindow = GuidsWindow(self.guids_path,syncModule=gs)
211
            guidsWindow = GuidsWindow()
212
            guidsWindow.show()
213
            self.mlog.info(f"Справочники изменены: {guidsWindow.guids_changed}")
214
            # issue fix http://vsys01775:8282/flea/pyexcelcons3/-/issues/5
215
            if guidsWindow.guids_changed: self.consolidator.load_guids_x(self.guids_path)
216
        except Exception as exp:
217
            self.app_error(f"show_guids_window error: {exp}")
218
            stacktrace = traceback.format_exc()
219
            self.app_error(stacktrace)
220

221
    def files_loaded(self):
222
        self.mlog.info("Файлы загружены")
223
        self.consButton.setDisabled(False)
224

225
    def select_input_files(self):
226
        """ Выбор файлов с шаблонами """
227
        try:
228
            fdialog=QFileDialog(parent=self.MainWindow,filter="MS Access 97 (*.mdb);; Excel (*.xlsx)")
229
            fdialog.setFileMode(QFileDialog.FileMode.ExistingFiles)
230
            if fdialog.exec()==1:
231
                self.selected_files = fdialog.selectedFiles()
232
                files_str = ", ".join([f"[{ntpath.basename(f)}]" for f in self.selected_files])
233
                self.filesInput.setText(files_str)
234
                self.mlog.info(f"Входные файлы: {files_str}")
235
                self.consButton.setDisabled(True)
236
                worker = Worker(self.start_load_input_files)
237
                worker.semaphore.finished.connect(self.files_loaded)
238
                self.threadpool.start(worker)
239
        except Exception as exp:
240
            self.app_error(f"select_input_files error: {exp}")
241

242
    def start_load_input_files(self):
243
        try:
244
            self.consolidator.load_input_data(self.selected_files)
245
            self.consolidator.dump_input_data(f"{self.output_dir}input_data.xlsx")
246
        except Exception as exp:
247
            self.mlog.error("start_load_input_files error",exc_info=True)
248

249

250
    def start_consolidate(self):
251
        try:
252
            worker = Worker(self.consolidate_items)
253
            worker.semaphore.finished.connect(lambda: self.mlog.info("Все готово"))
254
            self.threadpool.start(worker)
255
        except Exception as exp:
256
            self.mlog.error("start_consolidate error",exc_info=True)
257

258

259
    def handler_cc(self,rec:pd.Series)->pd.Series:
260
        try:
261
            result:pd.Series={"f1":0}
262
            module_name=f"__{self.hdict[rec['Производство2']]}__"
263
            namespace={"consolidator":self.consolidator , "record":rec, "result":result,"__name__":module_name}
264
            # self.mlog.info(module_name)
265
            exec(self.handlers[rec["Производство2"]],namespace)
266
            result = namespace["result"]
267
            self.mlog.info(f"{result['ID в SAP ERP VMZ']} -> {result['Продукт УП']}")
268
            return result
269
        except Exception as exp: 
270
            self.mlog.error("handler2 error",exc_info=True)
271
            rec["error"]=f"handler2_cc error: {exp}"
272
            return rec
273

274
    def check_handler(self,rec:pd.Series)->pd.Series:
275
        # rec["exists"] = self.ora.product_exists(str(rec["Продукт УП"]))
276
        # rec["ok"]=rec["Продукт УП"]==rec["Название продукта УП"]
277
        check = self.ora.product_check(str(rec["Продукт УП"]),str(rec["ID в SAP ERP VMZ"]))
278
        # rec["exists"] = ora.product_exists(str(rec["Продукт УП"]))
279
        # rec["ok"]=rec["Продукт УП"]==rec["Название продукта УП"]
280
        rec["ID в SAP ERP VMZ"]=str(rec["ID в SAP ERP VMZ"])
281
        rec["Есть в УП"] = check[1]
282
        rec["Уже укрупнено"] = check[2]
283
        rec["Уже укрупнено в"] = check[0]
284
        self.mlog.info(f"{rec['ID в SAP ERP VMZ']} : {rec['Продукт УП']} -> {check}")
285
        return rec
286
    
287
    def consolidate_items(self):
288
        try:
289
            if self.consolidator.input_data.empty: raise Exception("Отсутствуют входные данные")
290
            final_df = self.consolidator.input_data.apply(self.handler_cc,axis=1)
291
            final_df[self.consolidator.columns].to_excel(f"{self.output_dir}final_result.xlsx",index=False)
292
            self.mlog.info("Финальный результат сохранен на диск")
293

294
            ############# проверка #######################################
295
            df3:pd.DataFrame = final_df[["ID в SAP ERP VMZ","Полное наименование материала","Вид материала в SAP ERP","Продукт УП","error"]]
296
                        
297
            df_checked:pd.DataFrame = df3.apply(self.check_handler,axis=1)
298
            
299
            # outfile=f"{self.output_dir}\\final_checked.xls"
300
            # df_checked.to_excel(outfile,index=False,engine="xlwt")
301
            ### issue http://vsys01775:8282/flea/pyexcelcons3/-/issues/7 
302
            
303
            outfile=f"{self.output_dir}\\final_checked.xlsx"
304
            df_checked.to_excel(outfile,index=False,engine="openpyxl")            
305
            
306
            self.mlog.info("checked")
307
            os.system(f"start EXCEL.exe {outfile}")
308
            
309
            ##############################################################
310
        except Exception as exp:
311
            self.mlog.error("consolidate_items error",exc_info=True)   
312
            
313
    def start_download_update(self,update_url:str):
314
        try:
315
            worker = Worker(self.download_update,update_url)
316
            worker.semaphore.finished.connect(self.update_downloaded)
317
            self.threadpool.start(worker)
318
        except: raise
319
        
320
    def download_update(self,update_url:str):
321
        self.mlog.info(f"start download {update_url} \n")
322
        download_dir = f"{self.cur_path}\\update\\" if "_internal" not in self.cur_path else f"{self.cur_path}\\..\\..\\update\\"
323
        if not os.path.isdir(download_dir): os.makedirs(download_dir)
324
        mess=urllib.request.urlretrieve(update_url,f"{download_dir}update.zip")[0]
325
        self.mlog.info(mess)
326
        if "_internal" in self.cur_path:
327
            with open(f"{download_dir}update.bat",mode="w",encoding="windows-1251") as f:
328
                f.write(f'chcp 1251\ntaskkill /PID {os.getpid()}\npowershell -command "Expand-Archive -Force '+f"'{download_dir}update.zip' '{download_dir}..'"+'"\nexit')
329
                f.flush()
330
                f.close()
331
                os.system(f"start {download_dir}update.bat")
332
            
333
    def update_downloaded(self):
334
        self.openFilesButton.setEnabled(True)
335
        self.todoButton.setEnabled(True)
336
        self.consButton.setEnabled(True)
337
        self.guidsButton.setEnabled(True)
338
        self.mlog.info("update downloaded") 
339
        self.busy=False