def __processRefundNotFound(self, _infoType, _platform, _refundAccount, _refundLocation, _refund): try: # find the account and location in the notfound table for _row in range(2, self.notfoundTable.max_row + 1): _nfType = self.notfoundTable["A" + str(_row)].value _nfPlatform = self.notfoundTable["B" + str(_row)].value _nfAccount = self.notfoundTable["C" + str(_row)].value _nfLocation = self.notfoundTable["E" + str(_row)].value.split(' ')[1] if _infoType == _nfType and _platform == _nfPlatform and _refundAccount == _nfAccount and _refundLocation == _nfLocation: self.notfoundTable["H" + str(_row)] = _refund return except Exception: processException() return # if do not be found, we insert and send warning _row = self.notfoundTable.max_row + 1 self.notfoundTable["A" + str(_row)].value = _infoType self.notfoundTable["B" + str(_row)].value = _platform self.notfoundTable["C" + str(_row)].value = _refundAccount self.notfoundTable["E" + str(_row)].value = "unkown " + _refundLocation self.notfoundTable["I" + str(_row)].value = _refund # count the failcount and send the error self.__failCount += 1 ErrorList.addError( NotFound( "REFUND ERROR!\tType = " + _infoType + "\tPlatform: " + _platform, "Can not match the refund account and location!!"))
def save(self, outPath=""): Logger.addPrefabLog(Logger.LOG_TYPE_SAVE, outPath) try: print("正在保存文件...") if outPath == "": self.updateBook.save(self.updatePath) else: self.updateBook.save(outPath) print("保存文件成功!!") except: processException()
def __setRefundInfo(self, _infoType, _refundInfoList): try: # if can not find correct type, return if _infoType not in self.summary.sheetnames: ErrorList.addError( Error("REFUND ERROR", "can not find correct sheet: " + _infoType)) return _refundTable = self.summary[_infoType] for _refundInstance in _refundInfoList: _index, _offset = utils.findPlatformIndex( _refundTable, _refundInstance.platform) # if can not find platform, send error if _index == -1: ErrorList.addError( Error( "REFUND ERROR", "can not find correct platform: " + _refundInstance.platform)) continue # create the dictionary accountDict = {} for _row in range(_index, _index + _offset): _account = _refundTable["D" + str(_row)].value.strip() _locationList = _refundTable["F" + str(_row)].value.split(' ') _location = _locationList[1].strip( ) if len(_locationList) > 1 else _locationList[0].strip() # add to the dict accountDict[_account + '_' + _location] = _row # process the refund for _refundIndex in range(0, _refundInstance.length): _refundAccount, _refundLocation, _refund = _refundInstance.get( _refundIndex) if _refundAccount + '_' + _refundLocation in accountDict: self.__correctCount += 1 _refundTable[ "I" + str(accountDict[_refundAccount + '_' + _refundLocation])].value = _refund else: self.__processRefundNotFound(_infoType, _refundInstance.platform, _refundAccount, _refundLocation, _refund) except Exception: processException() return
def __init__(self, path = ""): Logger.addLog("CREAT NotFound: " + path) if path == "": self.notfoundBook = Workbook() self.path = "notfound.xlsx" else: try: self.notfoundBook = load_workbook(path) self.path = path except: processException()
def __writeProfit(self, profitList): try: for profitInstance in profitList: if profitInstance.id in self.pidDict: index = self.pidDict[profitInstance.id] self.updateTable["C" + str(index)] = profitInstance.amount else: ErrorList.addError( NotFound("UPDATE ERROR!!! ID = " + profitInstance.id, '')) except: processException()
def __setupDict(self): try: row = self.updateTable.max_row pidDict = {} for index in range(1, row): pidDict[self.updateTable["A" + str(index)].value] = index return pidDict except: processException()
def getNotfoundTable(self): Logger.addLog("GET notfoundTable!!") try: if len(self.notfoundBook.sheetnames) == 0: notfoundTable = self.notfoundBook.active self.__initNotFoundTable(notfoundTable) else: notfoundTable = self.notfoundBook[self.notfoundBook.sheetnames[0]] return notfoundTable except: processException()
def __readProfit(self): try: row = self.originTable.nrows profitList = [] for index in range(1, row): pid = self.originTable.cell_value(index, 0) amount = self.originTable.cell_value(index, 1) profitList.append(profitInfo(pid, amount)) return profitList except: processException()
def __init__(self, originPath, updatePath): Logger.addLog("CREATE profit! originPath = {}, updatePath = {}".format( originPath, updatePath)) self.updatePath = updatePath try: originBook = open_workbook(originPath) self.originTable = originBook.sheet_by_index(0) self.updateBook = load_workbook(updatePath) self.updateTable = self.updateBook[self.updateBook.sheetnames[0]] self.pidDict = self.__setupDict() except: processException()
def processRefundInfo(self, _filePath): try: Logger.addLog("OPEN REFUND FILE!! Path = " + _filePath) _refundBook = open_workbook(_filePath, formatting_info=True) _refundSheets = _refundBook.sheets() except Exception: processException() for _refundsheet in _refundSheets: _infoType = _refundsheet.name print("正在处理 " + _infoType + " 退款金额...") Logger.addLog("PROCESS {} 退款".format(_infoType)) _refundInfoList = self.__getRefundInfo(_refundsheet) self.__setRefundInfo(_infoType, _refundInfoList)
def findPlatformIndex(_reportTable, _platform): try: # store the merge cell's info mergeList = _reportTable.merged_cells mergeDict = {} for mergeCell in mergeList: mergeDict[mergeCell.min_row] = mergeCell.max_row - mergeCell.min_row # to find the index of platform isFindPlatform = False index = 1 while index < _reportTable.max_row: _reportPlatform = _reportTable["C" + str(index)].value if _reportPlatform is None: index += 1 continue _reportPlatform = _reportPlatform if not _reportPlatform.strip().isalpha() else _reportPlatform.lower() if _reportPlatform.strip() == _platform.strip().lower(): isFindPlatform = True break else : if mergeDict.get(index) != None: index += mergeDict.get(index) + 1 else : index +=1 offset = mergeDict[index] + 1 if index in mergeDict else 1 except Exception: processException() return -1, 0 else: if isFindPlatform: return index, offset else: return -1, 0
def processInfoWithTime(self, _lastpath): print("正在处理文件:" + _lastpath) _startTime = datetime.now() try: _platform, _infoType, _infoList = self.__getInfoByXlrd(_lastpath) _endTime = datetime.now() _interval = (_endTime - _startTime).seconds print("\r文件已读取完成,用时 " + str(_interval) + " 秒") # ignore writing when info list is none if _infoList == []: ErrorList.addError(Warning(_lastpath, "读取数据为空,注意检查")) print("读取数据为空,已跳过写入!!") return _startTime = datetime.now() self.__setInfo(_platform, _infoType, _infoList, _lastpath) _endTime = datetime.now() _interval = (_endTime - _startTime).seconds print("数据已处理完成,用时 " + str(_interval) + " 秒\n") except Exception: processException()
def doCommand(): # forever loop while True: # split the command and remove the none cmdLine = input(">>-->") Logger.addLog("COMMAND: " + cmdLine) if cmdLine == "": continue cmdList = cmdLine.strip().split(" ") for cmdIteration in cmdList: if cmdIteration == '': cmdList.remove(cmdIteration) # we have no different cmd now, so ignore it # cmd = cmdList[0] index = 1 result = {} while index < len(cmdList): # if it is the path Argument, then save it if cmdList[index] in pathArguDict: index += 1 if index >= len(cmdList): Logger.addLog("DATA NONE: " + cmdList[index - 1]) print("data none!!" + cmdList[index - 1]) break elif cmdList[index - 1] != "-sa" and not utils.is_excel_file( cmdList[index]): Logger.addLog("DATA ERROR, NOT EXCEL FILE : " + cmdList[index - 1]) print("DATA ERROR!!NOT EXCEL FILE!!" + cmdList[index - 1]) break else: Logger.addLog("COMMAND: {}, DATA: {}".format( cmdList[index - 1], cmdList[index])) result[pathArguDict[cmdList[index - 1]]] = cmdList[index] index += 1 elif cmdList[index] in systemArguDict: Logger.addLog("COMMAND: {}".format(cmdList[index])) systemArguDict[cmdList[index]]() break else: Logger.addLog("COMMAND ERROR:" + cmdList[index]) print("command error! " + cmdList[index]) break # 处理金额和退款 if "salePath" in result and "summaryPath" in result: Logger.addLog("process SA ") try: summary = load_workbook(result["summaryPath"]) except: processException() # get the notfound table notfoundPath = result[ "notfoundTable"] if "notfoundPath" in result else "" NF = notfound.NotFound(notfoundPath) notfoundTable = NF.getNotfoundTable() # process salesAmount SA = saleAmount.SaleAmount(summary, notfoundTable) SA.processDir(result["salePath"]) if "refundPath" in result: # process refund Logger.addLog("process RF ") RF = refund.Refund(summary, notfoundTable) RF.processRefundInfo(result["refundPath"]) # save file savePath = result[ "savePath"] if "savePath" in result else "summary.xlsx" summary.save(savePath) Logger.addPrefabLog(Logger.LOG_TYPE_SAVE, savePath) NF.save() # 处理退款 elif "summaryPath" in result and "refundPath" in result: Logger.addLog("process RF ") try: summary = load_workbook(result["summaryPath"]) except: processException() # get the notfound table notfoundPath = result[ "notfoundTable"] if "notfoundPath" in result else "" NF = notfound.NotFound(notfoundPath) notfoundTable = NF.getNotfoundTable() # process refund RF = refund.Refund(summary, notfoundTable) RF.processRefundInfo(result["refundPath"]) # save file savePath = result[ "savePath"] if "savePath" in result else "summary.xlsx" summary.save(savePath) NF.save() Logger.addPrefabLog(Logger.LOG_TYPE_SAVE, savePath) # 更新成本 elif "originPath" in result and "updatePath" in result: Logger.addLog("process PF ") PF = profit.Profit(result["originPath"], result["updatePath"]) PF.processProfitUpdate() savePath = result["savePath"] if "savePath" in result else "" PF.save(savePath) # print if len(result) != 0: ErrorList.printErrorList()
def __getInfoByXlrd(self, filePath): try: # open the file data = open_workbook(filePath) # to store this file data which _platform and type is _platform = data.sheet_by_index(0).cell_value(1, 0).split('/')[0] _infoType = data.sheet_by_index(0).cell_value(1, 1).split('/', 2)[1] # use info list to temporary store data _infoList = [] # for each table to process data sheets = data.sheets() for table in sheets: # ignore the sheet of "原始" if table.name == "原始": continue _row = 1 nrow = table.nrows # for each module to find out useful info while _row < nrow: # if find the none row, add 1 to row for finding the next if table.cell(_row, 1).value == "": _row += 1 continue # if account is none, the wo think the line is bad row_3List = table.cell(_row, 2).value.split('/') if row_3List[0] == "" or utils.is_contains_chinese( row_3List[0]): _row += 1 continue # create the module info instance _infoInstance = info() # to process the account/location cell _infoInstance.account = row_3List[0] _infoInstance.location = row_3List[ 1] if not utils.is_contains_chinese( row_3List[1]) else row_3List[1][0:-1] # if cell has no name, then wo think this line is bad _nameInTable = table.cell(_row, 1).value.split('/', 2) _infoInstance.name = "/" if _nameInTable[ 0] == "" else _nameInTable[0] if _infoType == "类": _infoType = _nameInTable[1] # to judge this module is normal or not row_4 = table.cell(_row, 3).value if row_4 == "": _infoInstance.normal = False _infoInstance.salesAmount = table.cell(_row, 4).value else: _infoInstance.salesAmount = row_4 _infoInstance.profitRate = table.cell(_row + 1, 4).value # append the module instance into the list _infoList.append(_infoInstance) # add 3 to variable _row to move to next module _row += 2 data.release_resources() except Exception: processException() return "", "", [] else: return _platform, _infoType, _infoList
def __setInfo(self, _platform, _infoType, _infoList, _path): try: # find the infotype table if _infoType not in self.__summary.sheetnames: ErrorList.addError( Error(_path, "can not find correct sheet: " + _infoType)) return reportTable = self.__summary[_infoType] # to find the index of platform index, offset = utils.findPlatformIndex(reportTable, _platform) if index == -1: ErrorList.addError( Error(_path, "can not find correct platform: " + _platform)) return # for each data in infoList to write down in the report for infoInstance in _infoList: isFind = False # to mark the account is finded or not for row in range(index, index + offset): reportAccount = reportTable["D" + str(row)].value reportLocationList = reportTable["F" + str(row)].value.split(' ') reportLocation = reportLocationList[1] if len( reportLocationList) > 1 else reportLocationList[0] # to match corret account and location row if infoInstance.account == reportAccount and infoInstance.location == reportLocation: isFind = True self.__correctCount += 1 # if the name is wrong, then change the name reportName = reportTable["E" + str(row)].value if infoInstance.name != reportName: reportTable["E" + str(row)].value = infoInstance.name reportTable["F" + str( row )].value = infoInstance.name + " " + infoInstance.location # to judge the normal is true or not if infoInstance.normal: # if normal, then write down the salesAmount and profitRate reportTable[ "G" + str(row)].value = infoInstance.salesAmount reportTable[ "H" + str(row)].value = infoInstance.profitRate else: # else write down the margin reportTable[ "J" + str(row)].value = infoInstance.salesAmount # write down and break the for loop break if isFind: continue else: # insert a new row xlsutils.insert_rows(reportTable, index + offset + 1, True) offset += 1 # write the data row = index + offset reportTable["B" + str(row)].value = _infoType reportTable["D" + str(row)].value = infoInstance.account reportTable["E" + str(row)].value = infoInstance.name reportTable["F" + str( row )].value = infoInstance.name + " " + infoInstance.location if infoInstance.normal: reportTable["G" + str(row)].value = infoInstance.salesAmount reportTable["H" + str(row)].value = infoInstance.profitRate else: reportTable["J" + str(row)].value = infoInstance.salesAmount # set red color xlsutils.setColor(reportTable["D" + str(row)], "red") self.__failCount += 1 Logger.addLog("新增插入一行,插入时行号 {},账号 {}".format( row, infoInstance.account)) ErrorList.addError( NotFound(_path, "存在新增数据,请自行插入,数据已录入 notfound.xlsx")) self.__processNotFoundInfo(_platform, _infoType, infoInstance) except Exception: processException()
def __getRefundInfo(self, _refundTable): try: _refundInfoList = [] # use the merge cell to locate the useful cell for merge in _refundTable.merged_cells: rs, re, cs, ce = merge # ignore some merge cell if re - rs != 1 and ce - cs != 2: continue # ignore when it has no info if _refundTable.cell_value(re, cs) == "": continue # read the platform _platform = _refundTable.cell_value(rs, cs).strip().lower() if utils.is_contains_chinese(_platform): ErrorList.addError( Error( "REFUND ERROR! Type = " + _refundTable.name + "\tPlatform: " + _platform, "We can not identify the chinese as platform name") ) continue else: _refundInfoInstance = refundInfo(_platform) _row = re + 1 if _refundTable.cell_value(re, cs) == "账号" else re while True: if _row >= _refundTable.nrows: break _acclo = _refundTable.cell_value(_row, cs) if _acclo == "": break else: # split the account and location if '(' in _acclo: _accloList = _acclo.split("(", 1) _account = _accloList[0].strip() _location = _accloList[1].split(')', 1)[0].strip() elif '(' in _acclo: _accloList = _acclo.split("(", 1) _account = _accloList[0].strip() _location = _accloList[1].split(')', 1)[0].strip() else: _acclo = _acclo.strip() _accloList = _acclo.split(" ", 1) _account = _accloList[0].strip() # set the default value if len(_accloList) == 1: _location = "CN" else: _location = _accloList[1].strip() # read the refund _refund = _refundTable.cell_value(_row, cs + 1) _refundInfoInstance.add(_account, _location, _refund) # add 1 to row _row += 1 # add to the list _refundInfoList.append(_refundInfoInstance) except Exception: processException() return _refundInfoList