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