def actTbInputCsvClicked(self):
dlg = QMessageBox.question(None, "操作提示", "您“确定”从CSV导入数据吗?",
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileName(None, "请选择CSV文件打开", "",
"Csv File (*.csv)")
if (file.__len__() > 0):
# 读取文件总行数
try:
tempfile = open(file, "r")
importLines = len(tempfile.readlines()) - 1
tempfile.close()
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(importLines)
self.mainWin.statusMsg('开始导入 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
# 读取csv文件方式
csvFile = open(file, "r")
reader = csv.reader(csvFile) # 返回的是迭代类型
nowTime = datetime.datetime.now()
cur_time = getTime()
cur_user = self.mainWin.app.user.id
i = 0
for row in reader:
if (i > 0):
Pub_Sku_Keyword.insert(
title=row[2],
platform='amazon',
platform_id=1,
sort=0,
status=1,
creator_id=cur_user,
created_at=cur_time,
updated_at=cur_time,
).on_conflict_ignore(
conflict_target=(Pub_Sku_Keyword.title, ),
preserve=(Pub_Sku_Keyword.title),
update={
'updator': cur_user,
'update_at': cur_time,
}).execute()
i = i + 1
self.mainWin.progressBarContinue()
csvFile.close()
endTime = datetime.datetime.now()
self.mainWin.statusMsg('完成导出 {0}条数据，用时{1}'.format(
importLines, (endTime - nowTime)))
self.mainWin.progressBarHide()
self.actTbRefreshClicked()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
pass
def actTbInputCsvClicked(self):
dlg = QMessageBox.question(None, "操作提示", "您“确定”从CSV导入数据吗?", QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileName(None, "请选择CSV文件打开", "", "Csv File (*.csv)")
if (file.__len__() > 0):
# 读取文件总行数
try:
tempfile = open(file, "r", encoding='utf-8')
importLines = len(tempfile.readlines()) - 1
tempfile.close()
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(importLines)
self.mainWin.statusMsg('开始导入 {0}...'.format(nowTime.strftime('%H:%M:%S')))
# 读取csv文件方式
csvFile = open(file, "r", encoding='utf-8')
reader = csv.reader(csvFile) # 返回的是迭代类型
cur_time = getTime()
i = 0
for row in reader:
if (i > 0):
# 根据NodeID查询对应分类表ID
keyword = row[2]
sku = row[0]
try:
keywordRow = Pub_Sku_Keyword.select().where(Pub_Sku_Keyword.title == keyword).get()
keywordId = keywordRow.id
except Pub_Sku_Keyword.DoesNotExist:
keywordId = 0
try:
skuRow = Amazon_Product_Category.select().where(Amazon_Product_Category.sku == sku).get()
skuId = skuRow.id
except Amazon_Product_Category.DoesNotExist:
skuId = 0
print(keywordId)
print(skuId)
# return
try:
if int(keywordId) > 0 and int(skuId) > 0:
# 掺入到NA PRODUCT ASIN CATEGORY表中
Amazon_Product_Category_Keyword_Relation.insert(
amazon_category_id=skuId,
amazon_keyword_id=keywordId,
update_at=cur_time
).execute()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
i = i + 1
self.mainWin.progressBarContinue()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
self.actTbRefreshClicked()
def actTbSearchClicked(self):
# 拼装查询参数
num = self.twOperators.rowCount()
try:
try:
method = self.searchObj['method'] if self.searchObj[
'method'] is not None else None
except Exception as e:
method = None
if (num > 0):
params = []
for i in range(num):
params.append([
self.twOperators.item(i, 0).text(),
self.twOperators.item(i, 2).text(),
self.twOperators.item(i, 3).text(),
self.twOperators.item(i, 4).text(),
self.twOperators.item(i, 5).text()
])
self.mainWin.goSearch(conditions=params, method=method)
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
pass
def initData(self):
try:
if (len(self.searchObj['fields'])):
for field in self.searchObj['fields']:
self.cbField.addItem(field['label'])
except Exception as e:
logger.info(e)
pass
def getValueByType(self, type):
try:
if (type == 'date'): return self.dteValue.text()
elif (type == 'int'): return self.intValue.text()
elif (type == 'float'): return self.floatValue.text()
elif (type == 'combo'): return self.comboValue.currentText()
else: return self.leValue.text()
except Exception as e:
logger.info(e)
def actCbFieldTextChanged(self, index):
try:
if (len(self.searchObj['fields'])):
for field in self.searchObj['fields']:
if (field['label'] == self.cbField.currentText()):
self.showValueByType(field['type'])
break
except Exception as e:
logger.info(e)
def actTbInputCsvClicked(self):
dlg = QMessageBox.question(None, "操作提示", "您“确定”从CSV导入数据吗?",
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileName(None, "请选择CSV文件打开", "",
"Csv File (*.csv)")
if (file.__len__() > 0):
self.twNewaimProductCategoryList.clear()
#self.twNewaimProductCategoryList.setRowCount(0)
self.twNewaimProductCategoryList.setHorizontalHeaderLabels([
'ID',
'Name',
'Code',
'Updated At',
'Created At',
'Level',
'Parent Id',
])
# 读取文件总行数
try:
nowTime = datetime.datetime.now()
tempfile = open(file, "rb")
importLines = len(tempfile.readlines()) - 1
tempfile.close()
# 读取csv文件方式
csvFile = open(file, "r", encoding='UTF-8')
reader = csv.reader(csvFile) # 返回的是迭代类型
self.twNewaimProductCategoryList.setRowCount(importLines
or 2)
self.mainWin.progressBarShow(importLines)
self.mainWin.statusMsg('开始导入 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
#tempfile.close()
i = 0
for row in reader:
if (i > 0):
self.actTbSve(row, i)
#csvFile.close()
i = i + 1
self.mainWin.progressBarContinue()
csvFile.close()
endTime = datetime.datetime.now()
self.mainWin.statusMsg('完成导入 {0}条数据，用时{1}'.format(
importLines, (endTime - nowTime)))
self.mainWin.progressBarHide()
# self.actTbRefreshClicked()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
pass
def initData(self, skuWhere=''):
try:
# 初始加载日期Tab
rows = Na_Business_Report.select(
Na_Business_Report.report_date).group_by(
Na_Business_Report.report_date).order_by(
Na_Business_Report.report_date.desc())
self.twDateList.setRowCount(0)
_rowCount = len(rows)
if (_rowCount > 0):
self.twDateList.setRowCount(_rowCount)
for i in range(_rowCount):
self.twDateList.setItem(
i, 0, utQTableWidgetItem(rows[i].report_date))
pass
# 初始加载SKU Tab
rows = Na_Product_Asin.select()
if (skuWhere != ''):
rows = eval('rows.where({0})'.format(skuWhere))
self.twSkuList.setRowCount(0)
_rowCount = len(rows)
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
if (_rowCount > 0):
self.twSkuList.setRowCount(_rowCount)
for i in range(_rowCount):
self.mainWin.progressBarContinue()
self.twSkuList.setItem(i, 0,
utQTableWidgetItem(rows[i].sku))
self.twSkuList.setItem(i, 1,
utQTableWidgetItem(rows[i].asin))
self.twSkuList.setItem(
i, 2,
utQTableWidgetItem(
self.CHOICES_COMBINE_TYPE[rows[i].combine_type]))
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(
_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
logger.info('加载了 %s 条, 每页 %s 条' % (i, self.pageSize))
pass
self.loadReportList()
except Exception as e:
logger.info(e)
pass
def initData(self, _where=''):
try:
# 初始加载第一页最新数据，过滤Status为3的记录
try:
rows = Pub_Product_Category.select().paginate(
1, self.pageSize).where(Pub_Product_Category.status != 3)
dataType = True
except Pub_Product_Category.DoesNotExist:
# 新增操作
dataType = False
if (_where != ''): rows = eval('rows.where({0})'.format(_where))
self.twNewaimProductCategoryList.setRowCount(0)
if dataType:
_rowCount = len(rows)
self.lbPagerInfo.setText(' R:{0}'.format(_rowCount))
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
if (_rowCount > 0):
# logger.info('加载了 %s 条, 每页 %s 条' %(_rowCount, self.pageSize))
self.twNewaimProductCategoryList.setRowCount(_rowCount)
for i in range(_rowCount):
self.mainWin.progressBarContinue()
row = Pub_Product_Category(**(rows[i].__data__))
item = utQTableWidgetItem(row.id)
# item.setCheckState(Qt.Unchecked)
self.twNewaimProductCategoryList.setItem(i, 0, item)
self.twNewaimProductCategoryList.setItem(
i, 1, utQTableWidgetItem(row.title))
self.twNewaimProductCategoryList.setItem(
i, 2, utQTableWidgetItem(row.code))
self.twNewaimProductCategoryList.setItem(
i, 3, utQTableWidgetItem(row.updated_at))
self.twNewaimProductCategoryList.setItem(
i, 4, utQTableWidgetItem(row.created_at))
self.twNewaimProductCategoryList.setItem(
i, 5, utQTableWidgetItem(row.level))
self.twNewaimProductCategoryList.setItem(
i, 6, utQTableWidgetItem(row.parent_id))
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(
_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
logger.info('加载了 %s 条, 每页 %s 条' % (i, self.pageSize))
except Exception as e:
logger.info(e)
pass
def actTbInputCsvClicked(self):
dataCount = self.twDateList.rowCount()
dlg = QMessageBox.question(None, "操作提示", "您“确定”从CSV导入数据吗?",
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes and dataCount > 0):
for i in range(dataCount):
self.twDateList.removeRow(dataCount - i - 1)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileName(None, "请选择CSV文件打开", "",
"Csv File (*.csv)")
if (file.__len__() > 0):
# 读取文件总行数
try:
# 获取Report 日期
_fileName = os.path.basename(file)
reportDate = re.search('\d{4}-\d{1,2}-\d{1,2}',
_fileName).group(0) if (re.search(
'\d{4}-\d{1,2}-\d{1,2}',
_fileName) is not None) else ''
tempfile = open(file, "r", encoding='utf-8')
importLines = len(tempfile.readlines()) - 1
tempfile.close()
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(importLines)
self.mainWin.statusMsg('开始导入 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
# 读取csv文件方式
csvFile = open(file, "r", encoding='utf-8')
reader = csv.reader(csvFile) # 返回的是迭代类型
i = 0
for row in reader:
if (i > 0):
self.actTbSve(row, reportDate)
i = i + 1
self.mainWin.progressBarContinue()
csvFile.close()
endTime = datetime.datetime.now()
self.mainWin.statusMsg('完成导入 {0}条数据，用时{1}'.format(
importLines, (endTime - nowTime)))
self.mainWin.progressBarHide()
self.actTbRefreshClicked()
except Exception as e:
logger.info(e)
pass
def showValueByType(self, type):
try:
self.leValue.setHidden(True)
self.floatValue.setHidden(True)
self.intValue.setHidden(True)
self.dteValue.setHidden(True)
self.comboValue.setHidden(True)
if (type == 'date'): self.dteValue.setHidden(False)
elif (type == 'int'): self.intValue.setHidden(False)
elif (type == 'float'): self.floatValue.setHidden(False)
elif (type == 'combo'): self.comboValue.setHidden(False)
else: self.leValue.setHidden(False)
except Exception as e:
logger.info(e)
def setValueByType(self, type, value):
try:
# self.showValueByType(type)
# self.dateTimeEdit.setDateTime(QDateTime.fromString(now_time, 'yyyy-MM-dd hh:mm:ss'))
if (type == 'date'):
self.dteValue.setDateTime(
QDateTime.fromString(value, 'yyyy-MM-dd hh:mm:ss'))
elif (type == 'int'):
self.intValue.setValue(int(value))
elif (type == 'float'):
self.floatValue.setValue(float(value))
elif (type == 'combo'):
self.comboValue.setCurrentText(self.comboObj[1])
else:
self.leValue.setText(value)
except Exception as e:
logger.info(e)
def initData(self, _where=''):
try:
# 初始加载第一页最新数据
rows = Na_Product_Asin.select().paginate(1, self.pageSize)
if (_where != ''): rows = eval('rows.where({0})'.format(_where))
self.twProductList.setRowCount(0)
_rowCount = len(rows)
self.lbPagerInfo.setText(' R:{0}'.format(_rowCount))
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
if (_rowCount > 0):
# logger.info('加载了 %s 条, 每页 %s 条' %(_rowCount, self.pageSize))
self.twProductList.setRowCount(_rowCount)
for i in range(_rowCount):
row = Na_Product_Asin(**(rows[i].__data__))
self.mainWin.progressBarContinue()
item = utQTableWidgetItem(row.id)
# item.setCheckState(Qt.Unchecked)
self.twProductList.setItem(i, 0, item)
self.twProductList.setItem(i, 1,
utQTableWidgetItem(row.sku))
self.twProductList.setItem(i, 2,
utQTableWidgetItem(row.asin))
self.twProductList.setItem(
i, 3,
utQTableWidgetItem(
self.CHOICES_COMBINE_TYPE[row.combine_type]))
self.twProductList.setItem(
i, 4, utQTableWidgetItem(row.updated_at))
self.twProductList.setItem(
i, 5, utQTableWidgetItem(row.created_at))
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(
_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
logger.info('加载了 %s 条, 每页 %s 条' % (i, self.pageSize))
except Exception as e:
logger.info(e)
pass
def actTbInputCsvClicked(self):
dlg = QMessageBox.question(None, "操作提示", "您“确定”从CSV导入数据吗?",
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileName(None, "请选择CSV文件打开", "",
"Csv File (*.csv)")
if (file.__len__() > 0):
# 读取文件总行数
try:
tempfile = open(file, "r", encoding='utf-8')
importLines = len(tempfile.readlines()) - 1
tempfile.close()
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(importLines)
self.mainWin.statusMsg('开始导入 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
# 读取csv文件方式
csvFile = open(file, "r", encoding='utf-8')
reader = csv.reader(csvFile) # 返回的是迭代类型
i = 0
for row in reader:
if (i > 0):
self.actTbSve(row)
# Na_Product_Asin.create(
# sku = row[0]
# ,asin = row[1]
# )
i = i + 1
self.mainWin.progressBarContinue()
csvFile.close()
endTime = datetime.datetime.now()
self.mainWin.statusMsg('完成导出 {0}条数据，用时{1}'.format(
importLines, (endTime - nowTime)))
self.mainWin.progressBarHide()
self.actTbRefreshClicked()
except Exception as e:
logger.info(e)
pass
def initData(self, _where=''):
try:
# 初始加载第一页最新数据
rows = Pub_Sku_Keyword.select().paginate(1, self.pageSize)
if (_where != ''): rows = eval('rows.where({0})'.format(_where))
self.twAmazonKeyword.setRowCount(0)
_rowCount = len(rows)
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
self.lbPagerInfo.setText(' R:{0}'.format(_rowCount))
if (_rowCount > 0):
# logger.info('加载了 %s 条, 每页 %s 条' %(_rowCount, self.pageSize))
self.twAmazonKeyword.setRowCount(_rowCount)
for i in range(_rowCount):
row = Pub_Sku_Keyword(**(rows[i].__data__))
self.mainWin.progressBarContinue()
item = utQTableWidgetItem(row.id)
# item.setCheckState(Qt.Unchecked)
self.twAmazonKeyword.setItem(i, 0, item)
self.twAmazonKeyword.setItem(i, 1,
utQTableWidgetItem(row.title))
self.twAmazonKeyword.setItem(
i, 2, utQTableWidgetItem(row.platform))
self.twAmazonKeyword.setItem(
i, 3, utQTableWidgetItem(row.updated_at))
self.twAmazonKeyword.setItem(
i, 4, utQTableWidgetItem(row.created_at))
self.twAmazonKeyword.setItem(i, 5, utQTableWidgetItem(1))
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(
_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
except Exception as e:
logger.info(e)
pass
def goSearch(self, conditions, method=None):
try:
if method is None:
# 搜索SKU
_table_ent = 'Na_Product_Asin'
for condition in conditions:
if condition[-1] == 'combo':
for index in self.CHOICES_COMBINE_TYPE:
if self.CHOICES_COMBINE_TYPE[index] == condition[
2]:
condition[2] = index
print(self.CHOICES_COMBINE_TYPE[index])
_where = QueryBuildToStr(_table_ent, conditions)
self.initData(skuWhere=_where)
else:
# 搜索 Report
_table_ent = 'Na_Business_Report'
_where = QueryBuildToStr(_table_ent, conditions)
if self.searchNavField.__len__() > 0:
if (self.searchNavField[0][0] == 'asin'):
self.loadReportList(asin=self.searchNavField[0][1],
_where=_where)
else:
# todo
self.loadReportList(
reportDate=self.searchNavField[0][1],
_where=_where)
#self.loadReportList()
else:
self.loadReportList(_where=_where)
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
pass
def loadReportList(self,
pageNo=1,
pageSize=0,
asin=None,
sku=None,
reportDate=None,
_where=''):
try:
# 初始加载最新数据
_pageSize = pageSize if pageSize > 0 else self.pageSize
rows = Na_Business_Report.select().paginate(
pageNo,
_pageSize).order_by(Na_Business_Report.report_date.desc())
if asin != None:
if _where == '':
rows = rows.where(Na_Business_Report.asin_child == asin)
else:
rows = eval(
'rows.where((Na_Business_Report.asin_child=="{0}") & {1})'
.format(asin, _where))
elif reportDate != None:
if _where == '':
rows = rows.where(
Na_Business_Report.report_date == reportDate)
else:
rows = eval(
'rows.where((Na_Business_Report.report_date=="{0}") & {1})'
.format(reportDate, _where))
elif _where != '':
rows = eval('rows.where({0})'.format(_where))
self.twReportList.setRowCount(0)
_rowCount = len(rows)
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
if (_rowCount > 0):
# logger.info('加载了 %s 条, 每页 %s 条' %(_rowCount, self.pageSize))
self.lbPagerInfo.setText('P:{0} | R:{1}'.format(1, _rowCount))
self.twReportList.setRowCount(_rowCount)
for i in range(_rowCount):
self.mainWin.progressBarContinue()
row = Na_Business_Report(**(rows[i].__data__))
self.twReportList.setItem(i, 0, utQTableWidgetItem(row.id))
item = utQTableWidgetItem('')
self.twReportList.setItem(i, 1, item)
# item = QTableWidgetItem('%.4f' % row.run_time)
# item.setTextAlignment(Qt.AlignRight + Qt.AlignVCenter)
# item = QTableWidgetItem('{0}'.format( cuNone2Object(row.asin_parent) ))
self.twReportList.setItem(
i, 2, utQTableWidgetItem(row.asin_parent))
self.twReportList.setItem(
i, 3, utQTableWidgetItem(row.asin_child))
self.twReportList.setItem(i, 4,
utQTableWidgetItem(row.title))
self.twReportList.setItem(
i, 5, utQTableWidgetItem(row.sessions, 0))
self.twReportList.setItem(
i, 6, utQTableWidgetItem(row.sessions_percentage,
0.00))
self.twReportList.setItem(
i, 7, utQTableWidgetItem(row.page_view, 0))
self.twReportList.setItem(
i, 8, utQTableWidgetItem(row.page_view_percentage,
0.00))
self.twReportList.setItem(
i, 9, utQTableWidgetItem(row.buy_box_percentage, 0.00))
self.twReportList.setItem(
i, 10, utQTableWidgetItem(row.units_ordered, 0))
self.twReportList.setItem(
i, 11,
utQTableWidgetItem(row.unit_session_percentage, 0.00))
self.twReportList.setItem(
i, 12,
utQTableWidgetItem(row.ordered_product_sales, 0.00))
self.twReportList.setItem(
i, 13, utQTableWidgetItem(row.total_order_items, 0))
self.twReportList.setItem(
i, 14, utQTableWidgetItem(row.report_date))
self.twReportList.setItem(
i, 15, utQTableWidgetItem(row.created_at))
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(
_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
if _rowCount == 0:
self.mainWin.statusMsg('该SKU {0} 对应的报告数据没有记录'.format(sku))
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
def initData(self, _where = ''):
try:
# 初始加载第一页最新数据，过滤Status为3的记录
# rows = Amazon_Product_Category_Keyword_Relation.select().paginate(1, self.pageSize)
rows = View_I_Amazon_Keyword_Category_Normal.select(View_I_Amazon_Keyword_Category_Normal.keyword,View_I_Amazon_Keyword_Category_Normal.category).where(View_I_Amazon_Keyword_Category_Normal.category != None).paginate(1, self.pageSize)
if (_where != ''): rows = eval('rows.where({0})'.format(_where))
self.twAmazonCategoryKeywordRelation.setRowCount(0)
_rowCount = len(rows)
self.lbPagerInfo.setText(' R:{0}'.format(_rowCount))
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(nowTime.strftime('%H:%M:%S')))
if (_rowCount > 0):
# for i in range(_rowCount):
# # 去掉第一行
# self.View_I_Amazon_Keyword_Category.removeRow(_rowCount - i - 1)
self.twAmazonCategoryKeywordRelation.setRowCount(_rowCount)
rowIndex = 0
for i in range(_rowCount):
# 获取关系表数据
row = View_I_Amazon_Keyword_Category_Normal(**(rows[i].__data__))
print(row.keyword)
print(row.category)
# if rows[i].__data__['amazon_category_id'] ==None or rows[i].__data__['amazon_keyword_id'] == None:
# continue
# self.twAmazonCategoryKeywordRelation.setRowCount(rowIndex + 1)
# #获取SKU
# #skuData = (Amazon_Product_Category.select(Pub_Product_Category.title, Amazon_Product_Category.sku).join(Pub_Product_Category, on=(Pub_Product_Category.id==rows[i].__data__['category_id'])).where(Amazon_Product_Category.id==rows[i].__data__['amazon_category_id']).get())
# # sql = 'SELECT keyword.title, product.sku FROM Pub_Sku_Keyword keyword JOIN Amazon_Product_Category product ON product.id = {} WHERE category.id={}'.format(rows[i].__data__['amazon_category_id'] , rows[i].__data__['amazon_keyword_id'])
# #
# # row = pgDbClection.Conn(self).execute_sql(sql).fetchall()
#
# try:
# categoryData = Amazon_Product_Category.select().where((Amazon_Product_Category.id == rows[i].__data__['amazon_category_id']) , products).get()
# except Amazon_Product_Category.DoesNotExist:
# continue
#
# try:
# keywordData = Pub_Sku_Keyword.select().where((Pub_Sku_Keyword.id == rows[i].__data__['amazon_keyword_id']) , keywords).get()
# except Pub_Sku_Keyword.DoesNotExist:
# continue
# print(skuData.sku)
self.mainWin.progressBarContinue()
item = utQTableWidgetItem(rowIndex)
# item.setCheckState(Qt.Unchecked)
self.twAmazonCategoryKeywordRelation.setItem(rowIndex,0 ,item)
self.twAmazonCategoryKeywordRelation.setItem(rowIndex, 1, utQTableWidgetItem(row.keyword))
self.twAmazonCategoryKeywordRelation.setItem(rowIndex, 2, utQTableWidgetItem(row.category))
rowIndex = rowIndex + 1
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
logger.info('加载了 %s 条, 每页 %s 条' % (rowIndex, self.pageSize))
except Exception as e:
import traceback
traceback.print_exc()
print(e)
logger.info(e)
pass
def initData(self, _where=''):
try:
# 初始加载第一页最新数据，过滤Status为3的记录
rows = Pub_Product_Asin_Keyword_Relation.select().paginate(
1, self.pageSize)
products = True
keywords = True
if (len(_where) > 0):
# products = eval(_where[0])
#categories = eval(_where(1).format(_where[1])) if len(_where) > 1 else categories
products = eval(_where[0])
keywords = eval(_where[1]) if len(_where) > 1 else keywords
self.twAmazonSKuKeywordRelation.setRowCount(0)
_rowCount = len(rows)
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
self.lbPagerInfo.setText(' R:{0}'.format(_rowCount))
if (_rowCount > 0):
for i in range(_rowCount):
# 去掉第一行
self.twAmazonSKuKeywordRelation.removeRow(_rowCount - i -
1)
#self.twAmazonSKuKeywordRelation.setRowCount(_rowCount)
rowIndex = 0
for i in range(_rowCount):
# 获取关系表数据
row = Pub_Product_Asin_Keyword_Relation(
**(rows[i].__data__))
if rows[i].__data__['product_id'] == None or rows[
i].__data__['keyword_id'] == None:
continue
self.twAmazonSKuKeywordRelation.setRowCount(rowIndex + 1)
#获取SKU
#skuData = (Na_Product_Asin.select(Pub_Product_Category.title, Na_Product_Asin.sku).join(Pub_Product_Category, on=(Pub_Product_Category.id==rows[i].__data__['category_id'])).where(Na_Product_Asin.id==rows[i].__data__['product_id']).get())
# sql = 'SELECT keyword.title, product.sku FROM Pub_Sku_Keyword keyword JOIN Na_Product_Asin product ON product.id = {} WHERE category.id={}'.format(rows[i].__data__['product_id'] , rows[i].__data__['keyword_id'])
#
# row = pgDbClection.Conn(self).execute_sql(sql).fetchall()
try:
skuData = Na_Product_Asin.select().where(
(Na_Product_Asin.id
== rows[i].__data__['product_id']),
products).get()
except Na_Product_Asin.DoesNotExist:
continue
try:
keywordData = Pub_Sku_Keyword.select().where(
(Pub_Sku_Keyword.id
== rows[i].__data__['keyword_id']),
keywords).get()
except Pub_Sku_Keyword.DoesNotExist:
continue
# print(skuData.sku)
self.mainWin.progressBarContinue()
item = utQTableWidgetItem(rowIndex)
# item.setCheckState(Qt.Unchecked)
self.twAmazonSKuKeywordRelation.setItem(rowIndex, 0, item)
self.twAmazonSKuKeywordRelation.setItem(
rowIndex, 1, utQTableWidgetItem(skuData.sku))
self.twAmazonSKuKeywordRelation.setItem(
rowIndex, 2, utQTableWidgetItem(keywordData.title))
rowIndex = rowIndex + 1
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(
_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
logger.info('加载了 %s 条, 每页 %s 条' % (rowIndex, self.pageSize))
except Exception as e:
import traceback
traceback.print_exc()
print(e)
logger.info(e)
pass
def actTbInputCsvClicked(self):
dlg = QMessageBox.question(None, "操作提示", "您“确定”从XLS导入数据吗?",
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileNames(None, "请选择Xls文件打开", "",
"Xls File (*.xls)")
if (file.__len__() > 0):
#self.twProductCategoryList.clear()
#self.twProductCategoryList.setRowCount(0)
#self.twProductCategoryList.setHorizontalHeaderLabels(['ID', 'Title', 'Code', 'Updated At', 'Created At', 'Level', 'Parent Id', ])
# 读取文件总行数
try:
nowTime = datetime.datetime.now()
#tempfile = open(file, "r")
#打开XLS文件
data = xlrd.open_workbook(file[0])
#获取XLS文件里的第二个表格
table = data.sheet_by_index(1)
importLines = table.nrows - 1
#self.twProductCategoryList.setRowCount(importLines or 2)
self.mainWin.progressBarShow(importLines)
self.mainWin.statusMsg('处理数据中...')
#检查数据库与上传文件内容配对
'''优化导入检查'''
differenceRecords = {}
categories = Amazon_Product_Category.select(
Amazon_Product_Category.title,
Amazon_Product_Category.code).order_by(
Amazon_Product_Category.code.desc()).execute()
rowCount = self.twProductCategoryList.rowCount()
stratTime = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime())
print("开始时间：" + stratTime)
for i in range(importLines):
title_list = table.row_values(i)[1].split('/')
if (i > 0):
for index in range(rowCount):
# print(self.twProductCategoryList.item(i, 1).text())
if str(
self.twProductCategoryList.item(
index, 2).text()
) == str(table.row_values(i)[0]) and str(
title_list[-1]
) != self.twProductCategoryList.item(index,
2).text():
differenceRecords[
self.twProductCategoryList.item(
index, 2
)] = self.twProductCategoryList.item(
index, 2)
endTime = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime())
print("结束时间：" + endTime)
self.mainWin.statusMsg('开始导入 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
msg = ",".join(str(i) for i in differenceRecords.keys())
if (len(differenceRecords) > 0):
dlg = QMessageBox.question(
None, "操作提示",
'Node ID为' + msg + '的已修改，您“确定”要更新修改记录吗',
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
skipRecords = {}
else:
skipRecords = differenceRecords
else:
skipRecords = {}
for i in range(importLines):
if (i > 0):
self.actTbSve(table.row_values(i), i, skipRecords)
#csvFile.close()
self.mainWin.progressBarContinue()
endTime = datetime.datetime.now()
self.mainWin.statusMsg('完成导入 {0}条数据，用时{1}'.format(
importLines, (endTime - nowTime)))
self.mainWin.progressBarHide()
self.actTbRefreshClicked()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
pass
def initData(self, _where=''):
try:
# 初始加载第一页最新数据，过滤Status为3的记录
rows = View_I_Amazon_Sku_Asin_Category_Normal.select(
View_I_Amazon_Sku_Asin_Category_Normal.sku,
View_I_Amazon_Sku_Asin_Category_Normal.category).where(
View_I_Amazon_Sku_Asin_Category_Normal.category != None
).paginate(1, self.pageSize)
# print(rows)
if (_where != ''): rows = eval('rows.where({0})'.format(_where))
# print(productRows)
self.twProductAsinNewaimCategoryList.setRowCount(0)
_rowCount = len(rows)
self.lbPagerInfo.setText(' R:{0}'.format(_rowCount))
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(_rowCount)
self.mainWin.statusMsg('开始加载 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
if (_rowCount > 0):
for i in range(_rowCount):
# 去掉第一行
self.twProductAsinNewaimCategoryList.removeRow(_rowCount -
i - 1)
self.twProductAsinNewaimCategoryList.setRowCount(_rowCount)
rowIndex = 0
for i in range(_rowCount):
# 获取关系表数据
row = View_I_Amazon_Sku_Asin_Category_Normal(
**(rows[i].__data__))
self.mainWin.progressBarContinue()
item = utQTableWidgetItem(rowIndex)
# item.setCheckState(Qt.Unchecked)
# print(skuData.sku)
self.twProductAsinNewaimCategoryList.setItem(
rowIndex, 0, item)
self.twProductAsinNewaimCategoryList.setItem(
rowIndex, 1, utQTableWidgetItem(row.sku))
self.twProductAsinNewaimCategoryList.setItem(
rowIndex, 2, utQTableWidgetItem(row.category))
rowIndex = rowIndex + 1
#break
endTime = datetime.datetime.now()
self.mainWin.statusMsg('加载 {0}条数据，用时{1}'.format(
_rowCount, (endTime - nowTime)))
self.mainWin.progressBarHide()
logger.info('加载了 %s 条, 每页 %s 条' % (rowIndex, self.pageSize))
except Exception as e:
import traceback
traceback.print_exc()
print(e)
logger.info(e)
pass
def actTbInputSkuAndCategoryClicked(self):
dlg = QMessageBox.question(None, "操作提示", "您“确定”从CSV导入数据吗?",
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileNames(None, "请选择XLS文件打开", "",
"Xls File (*.xls)")
if (file.__len__() > 0):
try:
# 打开XLS文件
data = xlrd.open_workbook(file[0])
# 获取XLS文件里的第二个表格
table = data.sheet_by_index(0)
importLines = table.nrows - 1
for i in range(importLines):
if (i > 0):
#根据NodeID查询对应分类表ID
nodeId = table.row_values(i)[2]
sku = table.row_values(i)[0]
try:
categoryRow = Pub_Product_Category.select(
).where(Pub_Product_Category.title ==
nodeId).get()
categoryId = categoryRow.id
except Pub_Product_Category.DoesNotExist:
categoryId = 0
try:
skuRow = Na_Product_Asin.select().where(
Na_Product_Asin.sku == sku).get()
skuId = skuRow.id
except Na_Product_Asin.DoesNotExist:
skuId = 0
print(categoryId)
print(skuId)
#return
try:
if int(categoryId) > 0 and int(skuId) > 0:
#掺入到NA PRODUCT ASIN CATEGORYB表中
Pub_Product_Asin_Category_Relation.insert(
productasinmodel_id=skuId,
productcategorymodel_id=categoryId,
).on_conflict(
conflict_target=(
Pub_Product_Asin_Category_Relation.
id, ),
preserve=(
Pub_Product_Asin_Category_Relation.
id),
update={
'productasinmodel_id': skuId,
'productcategorymodel_id':
categoryId
}).execute()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
pass
def actTbInputSkuAndCategoryClicked(self):
dlg = QMessageBox.question(None, "操作提示", "您“确定”从CSV导入数据吗?",
QMessageBox.Yes | QMessageBox.No)
if (dlg == QMessageBox.Yes):
file, ok1 = QFileDialog.getOpenFileName(None, "请选择CSV文件打开", "",
"Csv File (*.csv)")
if (file.__len__() > 0):
# 读取文件总行数
try:
tempfile = open(file, "r", encoding='utf-8')
importLines = len(tempfile.readlines()) - 1
tempfile.close()
nowTime = datetime.datetime.now()
self.mainWin.progressBarShow(importLines)
self.mainWin.statusMsg('开始导入 {0}...'.format(
nowTime.strftime('%H:%M:%S')))
# 读取csv文件方式
csvFile = open(file, "r", encoding='utf-8')
reader = csv.reader(csvFile) # 返回的是迭代类型
i = 0
for row in reader:
if (i > 0):
# 根据NodeID查询对应分类表ID
nodeId = row[1]
sku = row[0]
try:
categoryRow = Amazon_Product_Category.select(
).where(Amazon_Product_Category.code ==
nodeId).get()
categoryId = categoryRow.id
except Amazon_Product_Category.DoesNotExist:
categoryId = 0
try:
skuRow = Na_Product_Asin.select().where(
Na_Product_Asin.sku == sku).get()
skuId = skuRow.id
except Na_Product_Asin.DoesNotExist:
skuId = 0
print(categoryId)
print(skuId)
# return
try:
if int(categoryId) > 0 and int(skuId) > 0:
# 掺入到NA PRODUCT ASIN CATEGORY表中
View_I_Amazon_Sku_Asin_Category.insert(
productasinmodel_id=skuId,
amazonproductcategorymodel_id=
categoryId,
).execute()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
i = i + 1
self.mainWin.progressBarContinue()
except Exception as e:
import traceback
traceback.print_exc()
logger.info(e)
self.actTbRefreshClicked()
