def actTbSearchClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj)
self.vlCategoryForm.insertWidget(1, self.search_box, 1)
self.vlCategoryForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
def actTbSearchDetailsClicked(self):
if self.searchDetails_box is None:
self.searchDetails_box = WidgetSearchField(self,
self.searchDetailsObj)
self.vlReportC.insertWidget(1, self.searchDetails_box, 1)
self.vlReportC.setStretch(2, 100)
elif self.searchDetails_box.isHidden():
self.searchDetails_box.setHidden(False)
self.searchDetails_box.myWidget.close()
else:
self.searchDetails_box.setHidden(True)
pass
def actTbSearchNavClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj,
self.CHOICES_COMBINE_TYPE)
self.vlDate.insertWidget(1, self.search_box, 1)
self.vlDate.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
def actTbSearchClicked(self):
try:
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj)
self.vlCategoryForm.insertWidget(1, self.search_box, 1)
self.vlCategoryForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
except Exception as e:
import traceback
traceback.print_exc()
print(e)
pass
class WidgetNewaimCategory(QWidget, Ui_NewaimCategoryForm):
_ = QCoreApplication.translate
def __init__(self, mainWin):
super(WidgetNewaimCategory, self).__init__()
self.mainWin = mainWin
self.myWidget = QWidget()
self.setupUi(self.myWidget)
self.canMoveView = False
# 搜索窗口
self.search_box = None
self.searchObj = {
"fields": [{
'name': 'title',
'label': 'Title',
'type': 'string'
}, {
'name': 'code',
'label': 'Code',
'type': 'string'
}, {
'name': 'updated_at',
'label': 'Updated At',
'type': 'date'
}, {
'name': 'created_at',
'label': 'Created At',
'type': 'date'
}, {
'name': 'level',
'label': 'Level',
'type': 'int'
}, {
'name': 'parent_id',
'label': 'Parent Id',
'type': 'int'
}],
}
self.pageSize = 10000
self.tbInputCsv.clicked.connect(self.actTbInputCsvClicked)
self.tbRefresh.clicked.connect(self.actTbRefreshClicked)
#隐藏导入SKU与分类关系按钮
self.pushButton.clicked.connect(self.actTbInputSkuAndCategoryClicked)
self.tbActionLog.clicked.connect(self.actTbCatLog)
self.tbSelection.clicked.connect(self.actTbSelectionClicked)
self.tbUnselection.clicked.connect(self.actTbUnselectionClicked)
self.tbInvertSelection.clicked.connect(
self.actTbInvertSelectionClicked)
self.tbDelete.clicked.connect(self.actTbDeleteClicked)
self.tbInputCsv.setHidden(True)
self.pushButton.setHidden(True)
self.tbDelete.setHidden(True)
self.tbActionLog.setHidden(True)
self.twNewaimProductCategoryList.itemSelectionChanged.connect(
self.actTwProductListSelectionChanged)
self.twNewaimProductCategoryList.horizontalHeader(
).sectionClicked.connect(self.actTwProductListHorSectionClicked)
self.tbSearch.clicked.connect(self.actTbSearchClicked)
# 初始化布局
self.initLayout()
self.initData()
self.setLayout(self.myWidget.layout())
def initLayout(self):
self.twNewaimProductCategoryList.setColumnHidden(0, True),
self.twNewaimProductCategoryList.setColumnWidth(1, 260)
self.twNewaimProductCategoryList.setColumnWidth(2, 180)
self.twNewaimProductCategoryList.setColumnWidth(3, 140)
self.twNewaimProductCategoryList.setColumnWidth(4, 140)
self.twNewaimProductCategoryList.setColumnWidth(5, 80)
self.twNewaimProductCategoryList.setColumnWidth(6, 80)
self.twNewaimProductCategoryList.setColumnHidden(5, True),
self.twNewaimProductCategoryList.setColumnHidden(6, True),
pass
'''高级搜索数据'''
def goSearch(self, conditions, method=None):
_table_ent = 'Pub_Product_Category'
_where = QueryBuildToStr(_table_ent, conditions)
self.initData(_where)
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 actTbRefreshClicked(self):
self.initData()
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):
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 actTbSelectionClicked(self):
self.twNewaimProductCategoryList.setFocus()
selectedCount = self.twNewaimProductCategoryList.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twNewaimProductCategoryList.columnCount() - 1)
self.twNewaimProductCategoryList.setRangeSelected(qtwsr, True)
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''报表列表取消选择'''
def actTbUnselectionClicked(self):
self.twNewaimProductCategoryList.setFocus()
selectedCount = self.twNewaimProductCategoryList.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twNewaimProductCategoryList.columnCount() - 1)
self.twNewaimProductCategoryList.setRangeSelected(qtwsr, False)
self.mainWin.statusMsg('您选中了{0}行'.format(0))
'''报表列表反选'''
def actTbInvertSelectionClicked(self):
self.twNewaimProductCategoryList.setFocus()
row_num = self.twNewaimProductCategoryList.rowCount()
column_num = self.twNewaimProductCategoryList.columnCount() - 1
selectedCount = 0
if (row_num > 0):
listRows = set(self.getTwProductListSelectionChangedRowsNum())
for i in range(row_num):
qtwsr = QTableWidgetSelectionRange(i, 0, i, column_num)
if (i in listRows):
self.twNewaimProductCategoryList.setRangeSelected(
qtwsr, False)
else:
self.twNewaimProductCategoryList.setRangeSelected(
qtwsr, True)
selectedCount += 1
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''行选中时提示'''
def actTwProductListSelectionChanged(self):
row = self.getTwProductListSelectionChangedRowsNum()
self.mainWin.statusMsg('您选中了{0}行'.format(row.__len__()))
pass
def actTwProductListHorSectionClicked(self, index):
print(index)
pass
'''打开高级搜索'''
def actTbSearchClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj)
self.vlCategoryForm.insertWidget(1, self.search_box, 1)
self.vlCategoryForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
'''返回选中行'''
def getTwProductListSelectionChangedRowsNum(self):
result = []
try:
ranges = self.twNewaimProductCategoryList.selectedRanges()
for i in range(len(ranges)):
for row in range(ranges[i].topRow(),
ranges[i].bottomRow() + 1):
result.append(row)
except Exception as e:
print(e)
# 去重返回
return list(set(result))
pass
'''删除选择行'''
def actTbDeleteClicked(self):
rowIndex = self.getTwProductListSelectionChangedRowsNum()
selectedCount = 0
if rowIndex.__len__() > 0:
if QMessageBox.information(None, '操作提示', '您确定要删除选中的记录吗？',
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes:
for i in rowIndex:
id = self.twNewaimProductCategoryList.item(i, 0).text()
#将Status设置为3（删除）
Pub_Product_Category.update(status=3).where(
Pub_Product_Category.id == id).execute()
#删除与SKU关系表下的记录
try:
row = Pub_Product_Asin_Category_Relation.get(
Pub_Product_Asin_Category_Relation.
productcategorymodel_id == id)
row.delete_instance()
except Pub_Product_Asin_Category_Relation.DoesNotExist:
print('Categories are not associated with SKU')
selectedCount += 1
self.actTbRefreshClicked()
self.mainWin.statusMsg('您删除了{0}行'.format(selectedCount))
else:
QMessageBox.information(None, '操作提示', '请先选择要删除的记录',
QMessageBox.Yes)
pass
'''保存到数据库'''
def actTbSve(self, row, index):
nowTime = datetime.datetime.now()
cur_time = getTime()
cur_user = self.mainWin.app.user.id
newItem = QTableWidgetItem(str(row[3]))
self.twNewaimProductCategoryList.setItem(index - 1, 1, newItem)
newItem = QTableWidgetItem(str(row[0]))
self.twNewaimProductCategoryList.setItem(index - 1, 2, newItem)
newItem = QTableWidgetItem(str(nowTime))
self.twNewaimProductCategoryList.setItem(index - 1, 3, newItem)
newItem = QTableWidgetItem(str(nowTime))
self.twNewaimProductCategoryList.setItem(index - 1, 4, newItem)
newItem = QTableWidgetItem(str(row[5]))
self.twNewaimProductCategoryList.setItem(index - 1, 5, newItem)
newItem = QTableWidgetItem(str(row[2]))
self.twNewaimProductCategoryList.setItem(index - 1, 6, newItem)
try:
# 非空而且已修改的记录
try:
row_before_edit = Pub_Product_Category.get(
Pub_Product_Category.code == str(row[0]))
is_added = False
except Exception as e:
# 新增操作
is_added = True
result = Pub_Product_Category.insert(
code=row[0],
title=row[3],
creator_id=cur_user,
status=1,
sort=0,
level=row[5],
#parent_id=int(row[2])
).on_conflict(conflict_target=(Pub_Product_Category.code, ),
preserve=(Pub_Product_Category.code),
update={
'title': row[3],
'updated_at': cur_time
}).execute()
row_after_edit = Pub_Product_Category.get_by_id(result)
print(row[3])
if not is_added:
if str(row_after_edit.__data__['name']) != str(
row_before_edit.__data__['name']):
print(row_before_edit.__data__['name'])
print(is_added)
result = DlgCateHistoryLog.insert(
category=str(row[0]),
old_val=row_before_edit.__data__['name'],
new_val=str(row[3]),
modify_fields='name',
status=1,
sort=0,
creator_id=cur_user,
updated_at=cur_time,
created_at=cur_time,
).execute()
else:
DlgCateHistoryLog.insert({
'category':
str(row[0]),
'modify_fields':
'Add New Category',
'new_val':
str(row_after_edit.__data__),
'create_at':
cur_time,
'creator_id':
cur_user
}).execute()
except Exception as e:
import traceback
traceback.print_exc()
print(e)
pass
'''导入SKU和Categeroy的关系表'''
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 actTbCatLog(self):
"""
弹出SKU修改历史
"""
# self.progressBar.hide()
# self.progressText.hide()
select_items = self.twNewaimProductCategoryList.selectedItems()
select_cats = [x.text() for x in select_items if x.column() == 2]
select_titles = [x.text() for x in select_items if x.column() == 1]
if len(select_cats) > 20:
QMessageBox.information(None, '警告', '查看修改历史可选择的Category不能超过20个',
QMessageBox.Yes)
elif len(select_cats) < 1:
QMessageBox.information(None, '警告', '没有选择Category',
QMessageBox.Yes)
else:
DlgCateHistoryLog(self, select_cats, select_titles)
pass
class WidgetSkuKeyword(QWidget, Ui_AmazonKeywordForm):
_ = QCoreApplication.translate
fwin = None
searchText = ''
searchItems = []
curDisplayIndex = -1
is_ready = False # 初始化标志位
is_edit_lock = True # 编辑标志位
is_after_edit = False # 已编辑标志位
table_is_has_edit_col = True # 表格是否编辑标志列
# 表格标题字段配置
table_fields = {
'id': 'ID',
'title': 'Title',
'platform': 'Platform',
'updated_at': 'Updated At',
'created_at': 'Created At',
'keyword_type': 'Keyword Type'
}
# 表格隐藏列设置
table_fields_hidden = ['id', 'has_edit', 'keyword_type']
# 表格隐藏列id - 初始化自动根据table_fields_hidden转化
table_fields_hidden_index = []
# 表格标题字段配置
def __init__(self, mainWin):
super(WidgetSkuKeyword, self).__init__()
self.mainWin = mainWin
self.myWidget = QWidget()
self.setupUi(self.myWidget)
self.canMoveView = False
# 搜索窗口
self.search_box = None
self.searchObj = {
"fields":
[{
'name': 'title',
'label': 'Title',
'type': 'string'
}, {
'name': 'platform',
'label': 'Platform',
'type': 'string'
}, {
'name': 'updated_at',
'label': 'Updated At',
'type': 'date'
}, {
'name': 'created_at',
'label': 'Created At',
'type': 'date'
}
# , {'name': 'keyword_type', 'label': 'Keyword Type', 'type': 'number'}
# , {'name': 'is_edit', 'label': 'Edited', 'type': 'number'}
],
}
self.table_fields_hidden_index = [
i for i, x in enumerate(self.table_fields)
if x in self.table_fields_hidden
]
self.twAmazonKeyword.setColumnCount(len(self.table_fields))
self.twAmazonKeyword.setHorizontalHeaderLabels(
self.table_fields.values())
self.twAmazonKeyword.setSortingEnabled(False)
self.pageSize = 10000
self.tbSave.clicked.connect(self.actTbSave)
self.tbInputCsv.clicked.connect(self.actTbInputCsvClicked)
self.tbRefresh.clicked.connect(self.actTbRefreshClicked)
self.tbSelection.clicked.connect(self.actTbSelectionClicked)
self.tbUnselection.clicked.connect(self.actTbUnselectionClicked)
self.tbInvertSelection.clicked.connect(
self.actTbInvertSelectionClicked)
self.tbDelete.clicked.connect(self.actTbDeleteClicked)
self.tbSearch.clicked.connect(self.actTbSearchClicked)
self.twAmazonKeyword.cellChanged.connect(self.tableCellChanged)
self.twAmazonKeyword.doubleClicked.connect(self.tableCellClicked)
self.twAmazonKeyword.itemSelectionChanged.connect(
self.acttwAmazonKeywordSelectionChanged)
self.twAmazonKeyword.horizontalHeader().sectionClicked.connect(
self.acttwAmazonKeywordHorSectionClicked)
# self.twAmazonKeyword.doubleClicked.connect(self.dlgCategory)
self.tbAssociate.clicked.connect(self.actTbStbAssociateClicked)
#
datas = Pub_Sku_Keyword.keywordListAll(self)
qt5TableLoadDatas(self.twAmazonKeyword,
datas,
self.table_fields,
has_edit_flag=self.table_is_has_edit_col,
process_bar=False,
is_can_sort=False)
qt5TableSetHiddenHeaders(self.twAmazonKeyword,
self.table_fields_hidden,
self.table_fields,
has_edit_flag=self.table_is_has_edit_col)
self.twAmazonKeyword.setColumnWidth(len(
self.table_fields), 0) if self.table_is_has_edit_col else None
self.is_ready = True
# 初始化布局
self.initLayout()
#self.initData()
self.setLayout(self.myWidget.layout())
self.is_ready = True
def initLayout(self):
self.twAmazonKeyword.setColumnWidth(0, 80)
self.twAmazonKeyword.setColumnWidth(1, 220)
self.twAmazonKeyword.setColumnWidth(2, 150)
self.twAmazonKeyword.setColumnWidth(3, 140)
self.twAmazonKeyword.setColumnHidden(0, True)
pass
'''高级搜索数据'''
def goSearch(self, conditions, method=None):
_table_ent = 'Pub_Sku_Keyword'
_where = QueryBuildToStr(_table_ent, conditions)
self.initData(_where)
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 actTbRefreshClicked(self):
self.initData()
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")
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 actTbSelectionClicked(self):
self.twAmazonKeyword.setFocus()
selectedCount = self.twAmazonKeyword.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twAmazonKeyword.columnCount() - 1)
self.twAmazonKeyword.setRangeSelected(qtwsr, True)
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''报表列表取消选择'''
def actTbUnselectionClicked(self):
self.twAmazonKeyword.setFocus()
selectedCount = self.twAmazonKeyword.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twAmazonKeyword.columnCount() - 1)
self.twAmazonKeyword.setRangeSelected(qtwsr, False)
self.mainWin.statusMsg('您选中了{0}行'.format(0))
'''报表列表反选'''
def actTbInvertSelectionClicked(self):
self.twAmazonKeyword.setFocus()
row_num = self.twAmazonKeyword.rowCount()
column_num = self.twAmazonKeyword.columnCount() - 1
selectedCount = 0
if (row_num > 0):
listRows = set(self.gettwAmazonKeywordSelectionChangedRowsNum())
for i in range(row_num):
qtwsr = QTableWidgetSelectionRange(i, 0, i, column_num)
if (i in listRows):
self.twAmazonKeyword.setRangeSelected(qtwsr, False)
else:
self.twAmazonKeyword.setRangeSelected(qtwsr, True)
selectedCount += 1
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''行选中时提示'''
def acttwAmazonKeywordSelectionChanged(self):
row = self.gettwAmazonKeywordSelectionChangedRowsNum()
self.mainWin.statusMsg('您选中了{0}行'.format(row.__len__()))
pass
def acttwAmazonKeywordHorSectionClicked(self, index):
print(index)
pass
'''打开高级搜索'''
def actTbSearchClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj)
self.vlAmazonKeywordForm.insertWidget(1, self.search_box, 1)
self.vlAmazonKeywordForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
'''返回选中行'''
def gettwAmazonKeywordSelectionChangedRowsNum(self):
result = []
try:
ranges = self.twAmazonKeyword.selectedRanges()
for i in range(len(ranges)):
for row in range(ranges[i].topRow(),
ranges[i].bottomRow() + 1):
result.append(row)
except Exception as e:
print(e)
# 去重返回
return list(set(result))
pass
'''删除选择行'''
def actTbDeleteClicked(self):
rowIndex = self.gettwAmazonKeywordSelectionChangedRowsNum()
selectedCount = 0
if rowIndex.__len__() > 0:
if QMessageBox.information(None, '操作提示', '您确定要删除选中的记录吗？',
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes:
for i in rowIndex:
id = self.twAmazonKeyword.item(i, 0).text()
Pub_Sku_Keyword.delete_by_id(id)
selectedCount += 1
self.actTbRefreshClicked()
self.mainWin.statusMsg('您删除了{0}行'.format(selectedCount))
else:
QMessageBox.information(None, '操作提示', '请先选择要删除的记录',
QMessageBox.Yes)
def actTbSave(self):
rowCount = self.twAmazonKeyword.rowCount()
if (not rowCount):
QMessageBox.information(None, "操作提示", "没有数据，不用保存", QMessageBox.Yes)
return
nowTime = datetime.datetime.now()
# self.mainWin.progressBar.setRange(0, rowCount + 10)
# self.mainWin.progressBar.show()
# self.mainWin.progressText.setText('开始保存 {0}...'.format(nowTime.strftime('%H:%M:%S')))
# self.mainWin.progressText.show()
rows = []
try:
# 远端
cur_time = getTime()
cur_user = self.mainWin.app.user.id
self.is_ready = True
# 保存验证
valid_flag = True
for i in range(rowCount):
try:
# 检查导入数据类型
if (self.twAmazonKeyword.item(i, 1) is None
or self.twAmazonKeyword.item(i, 2) is None
or self.twAmazonKeyword.item(i, 3) is None
or self.twAmazonKeyword.item(i, 4) is None):
raise Exception('第 %s 行的数据填写不全' % i)
# 检查Title是否为空
if len(self.twAmazonKeyword.item(i, 2).text().strip()) < 1:
raise Exception('第 %s 行的Title不能为空' % (i + 1))
except Exception as e:
QMessageBox.information(None, '错误', str(e),
QMessageBox.Yes)
valid_flag = False
break
if valid_flag is False:
return
self.mainWin.progressBarShow(rowCount)
with pbConn.atomic() as transaction:
for i in range(rowCount):
if (self.twAmazonKeyword.item(i, 1) is not None
and self.twAmazonKeyword.item(i, 2) is not None
and self.twAmazonKeyword.item(i, 3) is not None
and self.twAmazonKeyword.item(
i, len(self.table_fields)).text() == '1'):
# 非空而且已修改的记录
try:
row_before_edit = Pub_Sku_Keyword().get(
Pub_Sku_Keyword.title ==
self.twAmazonKeyword.item(i, 1).text())
is_added = False
except Exception as e:
# 新增操作
is_added = True
if is_added:
result = (Pub_Sku_Keyword.insert(
title=self.twAmazonKeyword.item(i, 1).text(),
created_at=cur_time,
creator_id=cur_user,
updated_at=cur_time,
status=1,
sort=0,
platform='amazon',
platform_id=1,
keyword_type=1,
).execute())
# self.fwin.fwin.send_msg('lock')
row_after_edit = Pub_Sku_Keyword.get_by_id(result)
'''TODO'''
# if is_added:
# Pub_Sku_Keyword.insert({
# 'sku': self.twData.item(i, 2).text(),
# 'modify_fields': 'Add New SKU',
# 'new_val': str(row_after_edit.__data__),
# 'create_at': cur_time,
# 'creator_id': cur_user.id
# }).execute()
# else:
# edit_logs = []
#
# fields_need_log = list_filter(self.table_fields.keys(),
# ['last_ver', 'creator', 'creator_id', 'create_at',
# 'updator',
# 'updator_id', 'update_at'])
#
# fields_need_log.append('is_delete')
# # 轮询被修改的字段
# for field in fields_need_log:
# if field and str(row_after_edit.__data__[field]) != str(
# row_before_edit.__data__[field]):
# edit_logs.append({
# 'sku': self.twData.item(i, 2).text(),
# 'modify_fields': field,
# 'old_val': row_before_edit.__data__[field],
# 'new_val': row_after_edit.__data__[field],
# 'create_at': cur_time,
# 'creator_id': cur_user.id
# })
# if len(edit_logs) > 0:
# for x in range(0, len(edit_logs), 100):
# AppProductImageLogModel.insert_many(edit_logs[x:x + 100]).execute()
# # 清内存
# edit_logs = []
self.mainWin.progressBarContinue()
# self.progressBar.setValue(self.progressBar.value() + 1)
# self.progressBar.setValue(self.progressBar.value() + 10)
self.mainWin.progressBarShow(rowCount)
endTime = datetime.datetime.now()
self.mainWin.statusMsg('完成保存，用时{0}'.format((endTime - nowTime)))
self.mainWin.progressBarHide()
# self.fwin.fwin.send_msg('lock')
# self.is_after_edit = False
req = QMessageBox.information(None, '系统提示', '关键词保存成功',
QMessageBox.Yes)
if req == QMessageBox.Yes:
self.actTbRefresh()
#self.initData()
except Exception as e:
import traceback
traceback.print_exc()
req = QMessageBox.information(None, '保存失败', '保存操作失败,请检查数据或联系系统管理员',
QMessageBox.Yes)
# saveCount = MyDb.imageHistorySaveAll(self, rows=rows)
def tableCellChanged(self, row_id, col_id):
"""
单元格编辑事件
:param row_id:
:param col_id:
"""
print(row_id)
if self.is_ready == False or col_id == len(self.table_fields):
return None
if self.is_ready and col_id != len(
self.table_fields) and self.table_is_has_edit_col:
print('row:%s,col:%s' % (row_id, col_id))
self.is_after_edit = True
qt5TableRowAppendField(self.twAmazonKeyword, row_id,
len(self.table_fields), 1)
# 设置编辑行的背景为浅蓝色
self.is_ready = False
for i in range(len(self.table_fields)):
item = self.twAmazonKeyword.item(row_id, i)
if type(item) is QTableWidgetItem:
item.setBackground(QColor(229, 255, 255))
self.is_ready = True
'''
从数据库中载入
'''
def actTbRefresh(self):
self.is_ready = False
datas = Pub_Sku_Keyword.keywordListAll(self)
nowTime = datetime.datetime.now()
# self.progressBar.setRange(0, datas.__len__())
# self.progressBar.setValue(0)
# self.progressBar.show()
# self.progressText.setText('开始重载 {0}...'.format(nowTime.strftime('%H:%M:%S')))
# self.progressText.show()
qt5TableLoadDatas(self.twAmazonKeyword,
datas,
self.table_fields,
has_edit_flag=self.table_is_has_edit_col,
process_bar=False,
is_can_sort=False)
qt5TableSetHiddenHeaders(self.twAmazonKeyword,
self.table_fields_hidden,
self.table_fields,
has_edit_flag=self.table_is_has_edit_col)
self.twAmazonKeyword.setColumnWidth(len(
self.table_fields), 0) if self.table_is_has_edit_col else None
# self.actTbAdd()
endTime = datetime.datetime.now()
# self.progressText.setText('完成重载，用时{0}'.format((endTime - nowTime)))
# self.is_ready = True
# self.signal_received_update(False)
if self.table_is_has_edit_col:
qt5TableRowAppendField(self.twAmazonKeyword,
self.twAmazonKeyword.rowCount() - 1,
len(self.table_fields))
pass
def dlgCategory(self):
print('dlgCategory')
pass
def actTbStbAssociateClicked(self):
"""
弹出Category列表
"""
# self.progressBar.hide()
# self.progressText.hide()
select_items = self.twAmazonKeyword.selectedItems()
select_titles = [x.text() for x in select_items if x.column() == 1]
select_cats = [x.text() for x in select_items if x.column() == 2]
if len(select_cats) > 1:
QMessageBox.information(None, '警告', '请选择一个关键词', QMessageBox.Yes)
elif len(select_cats) < 1:
QMessageBox.information(None, '警告', '没有选择关键词', QMessageBox.Yes)
else:
DlgCateList(self, select_cats, select_titles)
pass
def tableCellClicked(self):
select_items = self.twAmazonKeyword.selectedItems()
print(select_items)
pass
class WidgetBusinessReport(QWidget, Ui_BusinessReportForm):
_ = QCoreApplication.translate
# todo 转数据字典
# CHOICES_COMBINE_TYPE = ((0, _('Single SKU')),
# (1, _('Combo SKU')),
# (2, _('Variation SKU')),
# (3, _('FBA SKU')),
# (4, _('Parent SKU')),)
CHOICES_COMBINE_TYPE = {
1: 'Single SKU',
2: 'Combo SKU',
3: 'Variation SKU',
4: 'FBA SKU',
5: 'Parent SKU',
}
def __init__(self, mainWin):
super(WidgetBusinessReport, self).__init__()
self.mainWin = mainWin
self.myWidget = QWidget()
self.setupUi(self.myWidget)
self.canMoveView = False
self.pageSize = 2000
self.lineVerTabSpace.pressed.connect(self.actLineVerTabSpaceClicked)
self.tbInputCsv.clicked.connect(self.actTbInputCsvClicked)
self.tbRefresh.clicked.connect(self.actTbRefreshClicked)
self.tbSelection.clicked.connect(self.actTbSelectionClicked)
self.tbUnselection.clicked.connect(self.actTbUnselectionClicked)
self.tbInvertSelection.clicked.connect(
self.actTbInvertSelectionClicked)
self.tbDelete.clicked.connect(self.actTbDeleteClicked)
self.tbPreview.clicked.connect(self.actTbPreviewClicked)
self.twSkuList.itemClicked.connect(self.actTwSkuListClicked)
self.twSkuList.sortByColumn(0, Qt.AscendingOrder)
self.twDateList.itemClicked.connect(self.actTwDateListClicked)
self.twDateList.sortByColumn(0, Qt.DescendingOrder)
self.twReportList.sortByColumn(14, Qt.DescendingOrder)
self.twSkuList.setSortingEnabled(False)
self.twDateList.setSortingEnabled(False)
self.twReportList.setSortingEnabled(False)
# 导航关键字
self.searchNavField = []
# SKU导航搜索窗口参数
self.search_box = None
self.searchObj = {
"fields": [{
'name': 'sku',
'label': 'SKU',
'type': 'string'
}, {
'name': 'asin',
'label': 'ASIN',
'type': 'string'
}, {
'name': 'asin',
'label': 'ASIN',
'type': 'string'
}, {
'name': 'combine_type',
'label': 'Combine Type',
'type': 'combo'
}],
}
self.tbSearchNav.clicked.connect(self.actTbSearchNavClicked)
# REPORT导航搜索窗口参数
self.searchDetails_box = None
self.searchDetailsObj = {
"fields": [
{
'name': 'asin_parent',
'label': 'ASIN (parent)',
'type': 'string'
},
{
'name': 'asin_child',
'label': 'ASIN (child)',
'type': 'string'
},
{
'name': 'title',
'label': 'Title',
'type': 'string'
},
{
'name': 'sessions',
'label': 'Sessions',
'type': 'int'
},
{
'name': 'sessions_percentage',
'label': 'Sessions per.',
'type': 'float'
},
{
'name': 'page_view',
'label': 'Page Views',
'type': 'int'
},
{
'name': 'page_view_percentage',
'label': 'Page Views per.',
'type': 'float'
},
{
'name': 'buy_box_percentage',
'label': 'BuyBox per.',
'type': 'int'
},
{
'name': 'units_ordered',
'label': 'Units Ordered',
'type': 'int'
},
{
'name': 'unit_session_percentage',
'label': 'Units Ordered per.',
'type': 'float'
},
{
'name': 'ordered_product_sales',
'label': 'Ordered Product Sales',
'type': 'float'
},
{
'name': 'total_order_items',
'label': 'Total Order Items',
'type': 'int'
},
{
'name': 'report_date',
'label': 'Report Date',
'type': 'date'
},
],
"method":
2
}
self.tbSearchDetails.clicked.connect(self.actTbSearchDetailsClicked)
# 初始化布局
self.initLayout()
self.initData()
self.setLayout(self.myWidget.layout())
'''打开SKU高级搜索'''
def actTbSearchNavClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj,
self.CHOICES_COMBINE_TYPE)
self.vlDate.insertWidget(1, self.search_box, 1)
self.vlDate.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
'''打开SKU高级搜索'''
def actTbSearchDetailsClicked(self):
if self.searchDetails_box is None:
self.searchDetails_box = WidgetSearchField(self,
self.searchDetailsObj)
self.vlReportC.insertWidget(1, self.searchDetails_box, 1)
self.vlReportC.setStretch(2, 100)
elif self.searchDetails_box.isHidden():
self.searchDetails_box.setHidden(False)
self.searchDetails_box.myWidget.close()
else:
self.searchDetails_box.setHidden(True)
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 initLayout(self):
self.lhBusinessReportForm.setStretch(0, 20)
self.lhBusinessReportForm.setStretch(2, 100)
self.twSubNav.setMinimumWidth(250)
self.twDateList.setColumnWidth(0, 200)
self.twSkuList.setColumnWidth(0, 200)
self.twSkuList.setColumnWidth(1, 150)
self.twReportList.setColumnWidth(1, 100)
self.twReportList.setColumnWidth(2, 100)
self.twReportList.setColumnWidth(3, 100)
self.twReportList.setColumnWidth(4, 250)
self.twReportList.setColumnWidth(5, 70)
self.twReportList.setColumnWidth(6, 90)
self.twReportList.setColumnWidth(7, 90)
self.twReportList.setColumnWidth(8, 100)
self.twReportList.setColumnWidth(9, 100)
self.twReportList.setColumnWidth(10, 100)
self.twReportList.setColumnWidth(11, 105)
self.twReportList.setColumnWidth(12, 130)
self.twReportList.setColumnWidth(13, 110)
self.twReportList.setColumnHidden(0, True)
self.twReportList.setColumnHidden(1, True)
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 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 actTbRefreshClicked(self):
self.searchNavField = []
self.initData()
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 actTbSelectionClicked(self):
self.twReportList.setFocus()
selectedCount = self.twReportList.rowCount()
qtwsr = QTableWidgetSelectionRange(0, 0, selectedCount - 1,
self.twReportList.columnCount() - 1)
self.twReportList.setRangeSelected(qtwsr, True)
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''报表列表取消选择'''
def actTbUnselectionClicked(self):
self.twReportList.setFocus()
selectedCount = self.twReportList.rowCount()
qtwsr = QTableWidgetSelectionRange(0, 0, selectedCount - 1,
self.twReportList.columnCount() - 1)
self.twReportList.setRangeSelected(qtwsr, False)
self.mainWin.statusMsg('您选中了{0}行'.format(0))
'''报表列表反选'''
def actTbInvertSelectionClicked(self):
self.twReportList.setFocus()
row_num = self.twReportList.rowCount()
column_num = self.twReportList.columnCount() - 1
selectedCount = 0
if (row_num > 0):
listRows = set(self.getTwReportListSelectionChangedRowsNum())
for i in range(row_num):
qtwsr = QTableWidgetSelectionRange(i, 0, i, column_num)
if (i in listRows):
self.twReportList.setRangeSelected(qtwsr, False)
else:
self.twReportList.setRangeSelected(qtwsr, True)
selectedCount += 1
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''返回选中行'''
def getTwReportListSelectionChangedRowsNum(self):
result = []
try:
ranges = self.twReportList.selectedRanges()
for i in range(len(ranges)):
for row in range(ranges[i].topRow(),
ranges[i].bottomRow() + 1):
result.append(row)
except Exception as e:
print(e)
# 去重返回
return list(set(result))
pass
'''点击SKU列表'''
def actTwSkuListClicked(self, rowObj):
try:
sku = self.twSkuList.item(rowObj.row(), 0).text()
asin = self.twSkuList.item(rowObj.row(), 1).text()
if (asin != None):
self.searchNavField = [['asin', asin]]
self.loadReportList(pageNo=1,
pageSize=5000,
asin=asin,
sku=sku)
except Exception as e:
print(e)
pass
'''点击SKU列表'''
def actTwDateListClicked(self, rowObj):
try:
report_date = self.twDateList.item(rowObj.row(), 0).text()
if (report_date != None):
self.searchNavField = [['report_date', report_date]]
self.loadReportList(pageNo=1,
pageSize=5000,
reportDate=report_date)
except Exception as e:
import traceback
traceback.print_exc()
print(e)
pass
'''删除选择行'''
def actTbDeleteClicked(self):
rowIndex = self.getTwReportListSelectionChangedRowsNum()
selectedCount = 0
if rowIndex.__len__() > 0:
if QMessageBox.information(None, '操作提示', '您确定要删除选中的记录吗？',
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes:
for i in rowIndex:
id = self.twReportList.item(i, 0).text()
Na_Business_Report.delete_by_id(id)
selectedCount += 1
self.actTbRefreshClicked()
self.mainWin.statusMsg('您删除了{0}行'.format(selectedCount))
else:
QMessageBox.information(None, '操作提示', '请先选择要删除的记录',
QMessageBox.Yes)
pass
'''设置可以移动'''
def actLineVerTabSpaceClicked(self):
self.canMoveView = True
'''调整切分窗口比例'''
def mouseMoveEvent(self, event):
# 鼠标左键
if (event.buttons() == Qt.LeftButton and self.canMoveView == True):
self.lhBusinessReportForm.setStretch(0, event.x())
self.lhBusinessReportForm.setStretch(2, self.width() - event.x())
self.lhBusinessReportForm.update()
'''调整切分窗口比例'''
def mouseReleaseEvent(self, event):
# 鼠标左键
if (event.buttons() == Qt.LeftButton and self.canMoveView == True):
self.lhBusinessReportForm.setStretch(0, event.x())
self.lhBusinessReportForm.setStretch(2, self.width() - event.x())
self.lhBusinessReportForm.update()
self.canMoveView = False
'''保存到数据库'''
def actTbSve(self, row, reportDate):
title = row[2][0:255] if len(row[2]) > 255 else row[2]
try:
result = (Na_Business_Report.insert(
asin_parent=row[0],
asin_child=row[1],
title=title,
sessions=utStr2Int(row[3]),
sessions_percentage=utStr2Float(row[4]),
page_view=utStr2Int(row[5]),
page_view_percentage=utStr2Float(row[6]),
buy_box_percentage=utStr2Float(row[7]),
units_ordered=utStr2Int(row[8]),
unit_session_percentage=utStr2Float(row[9]),
ordered_product_sales=utStr2Float(row[10]),
total_order_items=utStr2Int(row[11]),
report_date=reportDate).execute())
except Exception as e:
import traceback
traceback.print_exc()
print(e)
'''跳到Chart TAG'''
def actTbPreviewClicked(self):
self.mainWin.actLoadModelView('CHARTS')
pass
class WidgetProduct(QWidget, Ui_ProductForm):
_ = QCoreApplication.translate
# todo 转数据字典
# CHOICES_COMBINE_TYPE = ((0, _('Single SKU')),
# (1, _('Combo SKU')),
# (2, _('Variation SKU')),
# (3, _('FBA SKU')),
# (4, _('Parent SKU')),)
CHOICES_COMBINE_TYPE = {
1: 'Single SKU',
2: 'Combo SKU',
3: 'Variation SKU',
4: 'FBA SKU',
5: 'Parent SKU',
}
def __init__(self, mainWin):
super(WidgetProduct, self).__init__()
self.mainWin = mainWin
self.myWidget = QWidget()
self.setupUi(self.myWidget)
self.canMoveView = False
# 搜索窗口
self.search_box = None
self.searchObj = {
"fields": [{
'name': 'sku',
'label': 'SKU',
'type': 'string'
}, {
'name': 'asin',
'label': 'ASIN',
'type': 'string'
}, {
'name': 'updated_at',
'label': 'Updated At',
'type': 'date'
}, {
'name': 'combine_type',
'label': 'Combine Type',
'type': 'combo'
}],
}
self.pageSize = 10000
self.tbInputCsv.clicked.connect(self.actTbInputCsvClicked)
self.tbRefresh.clicked.connect(self.actTbRefreshClicked)
self.tbSelection.clicked.connect(self.actTbSelectionClicked)
self.tbUnselection.clicked.connect(self.actTbUnselectionClicked)
self.tbInvertSelection.clicked.connect(
self.actTbInvertSelectionClicked)
self.tbDelete.clicked.connect(self.actTbDeleteClicked)
self.twProductList.itemSelectionChanged.connect(
self.actTwProductListSelectionChanged)
self.twProductList.horizontalHeader().sectionClicked.connect(
self.actTwProductListHorSectionClicked)
self.tbSearch.clicked.connect(self.actTbSearchClicked)
# 初始化布局
self.initLayout()
self.initData()
self.setLayout(self.myWidget.layout())
def initLayout(self):
self.twProductList.setColumnWidth(0, 80)
self.twProductList.setColumnWidth(1, 260)
self.twProductList.setColumnWidth(2, 150)
self.twProductList.setColumnWidth(3, 140)
self.twProductList.setColumnWidth(4, 140)
self.twProductList.setColumnHidden(0, True)
pass
'''高级搜索数据'''
def goSearch(self, conditions, method=None):
_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(_where)
pass
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 actTbRefreshClicked(self):
self.initData()
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 actTbSelectionClicked(self):
self.twProductList.setFocus()
selectedCount = self.twProductList.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twProductList.columnCount() - 1)
self.twProductList.setRangeSelected(qtwsr, True)
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''报表列表取消选择'''
def actTbUnselectionClicked(self):
self.twProductList.setFocus()
selectedCount = self.twProductList.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twProductList.columnCount() - 1)
self.twProductList.setRangeSelected(qtwsr, False)
self.mainWin.statusMsg('您选中了{0}行'.format(0))
'''报表列表反选'''
def actTbInvertSelectionClicked(self):
self.twProductList.setFocus()
row_num = self.twProductList.rowCount()
column_num = self.twProductList.columnCount() - 1
selectedCount = 0
if (row_num > 0):
listRows = set(self.getTwProductListSelectionChangedRowsNum())
for i in range(row_num):
qtwsr = QTableWidgetSelectionRange(i, 0, i, column_num)
if (i in listRows):
self.twProductList.setRangeSelected(qtwsr, False)
else:
self.twProductList.setRangeSelected(qtwsr, True)
selectedCount += 1
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''行选中时提示'''
def actTwProductListSelectionChanged(self):
row = self.getTwProductListSelectionChangedRowsNum()
self.mainWin.statusMsg('您选中了{0}行'.format(row.__len__()))
pass
def actTwProductListHorSectionClicked(self, index):
print(index)
pass
'''打开高级搜索'''
def actTbSearchClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj,
self.CHOICES_COMBINE_TYPE)
self.vlProductForm.insertWidget(1, self.search_box, 1)
self.vlProductForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
'''返回选中行'''
def getTwProductListSelectionChangedRowsNum(self):
result = []
try:
ranges = self.twProductList.selectedRanges()
for i in range(len(ranges)):
for row in range(ranges[i].topRow(),
ranges[i].bottomRow() + 1):
result.append(row)
except Exception as e:
print(e)
# 去重返回
return list(set(result))
pass
'''删除选择行'''
def actTbDeleteClicked(self):
rowIndex = self.getTwProductListSelectionChangedRowsNum()
selectedCount = 0
if rowIndex.__len__() > 0:
if QMessageBox.information(None, '操作提示', '您确定要删除选中的记录吗？',
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes:
for i in rowIndex:
id = self.twProductList.item(i, 0).text()
Na_Product_Asin.delete_by_id(id)
selectedCount += 1
self.actTbRefreshClicked()
self.mainWin.statusMsg('您删除了{0}行'.format(selectedCount))
else:
QMessageBox.information(None, '操作提示', '请先选择要删除的记录',
QMessageBox.Yes)
pass
'''保存到数据库'''
def actTbSve(self, row):
nowTime = datetime.datetime.now()
cur_time = getTime()
print(row[0])
selectResult = Na_Product_Asin.select().where(
Na_Product_Asin.sku == row[0]).execute()
if len(selectResult) > 0:
result = Na_Product_Asin.update({
Na_Product_Asin.updated_at:
cur_time
}).where(Na_Product_Asin.sku == row[0]).execute()
else:
try:
result = (Na_Product_Asin.insert(
sku=row[0],
asin=row[1],
platform='amazon',
platform_id=1,
sort=0,
).execute())
except Exception as e:
import traceback
traceback.print_exc()
print(e)
pass
class WidgetAmazonSkuKeywordRelation(QWidget, Ui_AmazonSkuKeywordRelationForm):
_ = QCoreApplication.translate
def __init__(self, mainWin):
super(WidgetAmazonSkuKeywordRelation, self).__init__()
self.mainWin = mainWin
self.myWidget = QWidget()
self.setupUi(self.myWidget)
self.canMoveView = False
# 搜索窗口
self.search_box = None
self.searchObj = {
"fields": [
{
'name': 'sku',
'label': 'SKU',
'type': 'string'
},
{
'name': 'title',
'label': 'Title',
'type': 'string'
},
],
}
self.pageSize = 10000
self.tbInputCsv.clicked.connect(self.actTbInputCsvClicked)
self.tbRefresh.clicked.connect(self.actTbRefreshClicked)
self.tbSelection.clicked.connect(self.actTbSelectionClicked)
self.tbUnselection.clicked.connect(self.actTbUnselectionClicked)
self.tbInvertSelection.clicked.connect(
self.actTbInvertSelectionClicked)
self.tbDelete.clicked.connect(self.actTbDeleteClicked)
self.twAmazonSKuKeywordRelation.itemSelectionChanged.connect(
self.acttwAmazonSKuKeywordRelationSelectionChanged)
self.twAmazonSKuKeywordRelation.horizontalHeader(
).sectionClicked.connect(
self.acttwAmazonSKuKeywordRelationHorSectionClicked)
self.tbSearch.clicked.connect(self.actTbSearchClicked)
# 初始化布局
self.initLayout()
self.initData()
self.setLayout(self.myWidget.layout())
def initLayout(self):
self.twAmazonSKuKeywordRelation.setColumnWidth(0, 80)
self.twAmazonSKuKeywordRelation.setColumnWidth(1, 150)
self.twAmazonSKuKeywordRelation.setColumnWidth(2, 150)
self.twAmazonSKuKeywordRelation.setColumnWidth(3, 140)
self.twAmazonSKuKeywordRelation.setColumnHidden(0, True)
pass
'''高级搜索数据'''
def goSearch(self, conditions, method=None):
_product_table_ent = 'Na_Product_Asin'
_keyword_table_ent = 'Pub_Sku_Keyword'
_where = []
print(conditions)
if (len(conditions) > 0):
for condition in conditions:
if condition[0] == 'sku':
_productWhere = QueryBuildToStr(_product_table_ent,
[condition])
_where.append(_productWhere)
elif condition[0] == 'keyword':
_keywordWhere = QueryBuildToStr(_keyword_table_ent,
[condition])
_where.append(_keywordWhere)
print(_where)
self.initData(_where)
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 actTbRefreshClicked(self):
self.initData()
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 = Na_Product_Asin.select().where(
Na_Product_Asin.sku == sku).get()
skuId = skuRow.id
except Na_Product_Asin.DoesNotExist:
skuId = 0
print(keywordId)
print(skuId)
# return
try:
if int(keywordId) > 0 and int(skuId) > 0:
# 掺入到NA PRODUCT ASIN CATEGORY表中
Pub_Product_Asin_Keyword_Relation.insert(
product_id=skuId,
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()
pass
'''报表列表全选'''
def actTbSelectionClicked(self):
self.twAmazonSKuKeywordRelation.setFocus()
selectedCount = self.twAmazonSKuKeywordRelation.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twAmazonSKuKeywordRelation.columnCount() - 1)
self.twAmazonSKuKeywordRelation.setRangeSelected(qtwsr, True)
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''报表列表取消选择'''
def actTbUnselectionClicked(self):
self.twAmazonSKuKeywordRelation.setFocus()
selectedCount = self.twAmazonSKuKeywordRelation.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twAmazonSKuKeywordRelation.columnCount() - 1)
self.twAmazonSKuKeywordRelation.setRangeSelected(qtwsr, False)
self.mainWin.statusMsg('您选中了{0}行'.format(0))
'''报表列表反选'''
def actTbInvertSelectionClicked(self):
self.twAmazonSKuKeywordRelation.setFocus()
row_num = self.twAmazonSKuKeywordRelation.rowCount()
column_num = self.twAmazonSKuKeywordRelation.columnCount() - 1
selectedCount = 0
if (row_num > 0):
listRows = set(
self.gettwAmazonSKuKeywordRelationSelectionChangedRowsNum())
for i in range(row_num):
qtwsr = QTableWidgetSelectionRange(i, 0, i, column_num)
if (i in listRows):
self.twAmazonSKuKeywordRelation.setRangeSelected(
qtwsr, False)
else:
self.twAmazonSKuKeywordRelation.setRangeSelected(
qtwsr, True)
selectedCount += 1
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''行选中时提示'''
def acttwAmazonSKuKeywordRelationSelectionChanged(self):
row = self.gettwAmazonSKuKeywordRelationSelectionChangedRowsNum()
self.mainWin.statusMsg('您选中了{0}行'.format(row.__len__()))
pass
def acttwAmazonSKuKeywordRelationHorSectionClicked(self, index):
print(index)
pass
'''打开高级搜索'''
def actTbSearchClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj)
self.vlAmazonSkuKeywordRelationForm.insertWidget(
1, self.search_box, 1)
self.vlAmazonSkuKeywordRelationForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
'''返回选中行'''
def gettwAmazonSKuKeywordRelationSelectionChangedRowsNum(self):
result = []
try:
ranges = self.twAmazonSKuKeywordRelation.selectedRanges()
for i in range(len(ranges)):
for row in range(ranges[i].topRow(),
ranges[i].bottomRow() + 1):
result.append(row)
except Exception as e:
print(e)
# 去重返回
return list(set(result))
pass
'''删除选择行'''
def actTbDeleteClicked(self):
rowIndex = self.gettwAmazonSKuKeywordRelationSelectionChangedRowsNum()
selectedCount = 0
if rowIndex.__len__() > 0:
if QMessageBox.information(None, '操作提示', '您确定要删除选中的记录吗？',
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes:
for i in rowIndex:
id = self.twAmazonSKuKeywordRelation.item(i, 0).text()
Pub_Product_Asin_Keyword_Relation.delete_by_id(id)
selectedCount += 1
self.actTbRefreshClicked()
self.mainWin.statusMsg('您删除了{0}行'.format(selectedCount))
else:
QMessageBox.information(None, '操作提示', '请先选择要删除的记录',
QMessageBox.Yes)
pass
class WidgetProductNewaimCategory(QWidget, Ui_ProductAsinNewaimCategoryForm):
def __init__(self, mainWin):
super(WidgetProductNewaimCategory, self).__init__()
self.mainWin = mainWin
self.myWidget = QWidget()
self.setupUi(self.myWidget)
self.canMoveView = False
# 搜索窗口
self.search_box = None
self.searchObj = {
"fields": [{
'name': 'sku',
'label': 'SKU',
'type': 'string'
}, {
'name': 'category',
'label': 'Category',
'type': 'string'
}],
}
self.pageSize = 10000
self.tbInputCsv.clicked.connect(self.actTbInputSkuAndCategoryClicked)
self.tbRefresh.clicked.connect(self.actTbRefreshClicked)
# 隐藏导入SKU与分类关系按钮
#self.pushButton.clicked.connect(self.actTbInputSkuAndCategoryClicked)
self.pushButton.setHidden(True)
#self.tbActionLog.clicked.connect(self.actTbCatLog)
self.tbActionLog.setHidden(True)
self.tbSelection.clicked.connect(self.actTbSelectionClicked)
self.tbUnselection.clicked.connect(self.actTbUnselectionClicked)
self.tbInvertSelection.clicked.connect(
self.actTbInvertSelectionClicked)
self.tbDelete.clicked.connect(self.actTbDeleteClicked)
self.tbDelete.setHidden(True)
self.tbInputCsv.setHidden(True)
self.twProductAsinNewaimCategoryList.itemSelectionChanged.connect(
self.actTwProductListSelectionChanged)
# self.twProductAsinNewaimCategoryList.horizontalHeader().sectionClicked.connect(self.actTwProductListHorSectionClicked)
self.tbSearch.clicked.connect(self.actTbSearchClicked)
# 初始化布局
self.initLayout()
self.initData()
self.setLayout(self.myWidget.layout())
def initLayout(self):
self.twProductAsinNewaimCategoryList.setColumnHidden(0, True),
self.twProductAsinNewaimCategoryList.setColumnWidth(1, 250)
self.twProductAsinNewaimCategoryList.setColumnWidth(2, 260)
pass
'''高级搜索数据'''
def goSearch(self, conditions, method=None):
_table_ent = 'View_I_Amazon_Sku_Asin_Category_Normal'
_where = QueryBuildToStr(_table_ent, conditions)
print(conditions)
self.initData(_where)
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 actTbRefreshClicked(self):
self.initData()
pass
'''报表列表全选'''
def actTbSelectionClicked(self):
self.twProductAsinNewaimCategoryList.setFocus()
selectedCount = self.twProductAsinNewaimCategoryList.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twProductAsinNewaimCategoryList.columnCount() - 1)
self.twProductAsinNewaimCategoryList.setRangeSelected(qtwsr, True)
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''报表列表取消选择'''
def actTbUnselectionClicked(self):
self.twProductAsinNewaimCategoryList.setFocus()
selectedCount = self.twProductAsinNewaimCategoryList.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twProductAsinNewaimCategoryList.columnCount() - 1)
self.twProductAsinNewaimCategoryList.setRangeSelected(qtwsr, False)
self.mainWin.statusMsg('您选中了{0}行'.format(0))
'''报表列表反选'''
def actTbInvertSelectionClicked(self):
self.twProductAsinNewaimCategoryList.setFocus()
row_num = self.twProductAsinNewaimCategoryList.rowCount()
column_num = self.twProductAsinNewaimCategoryList.columnCount() - 1
selectedCount = 0
if (row_num > 0):
listRows = set(self.getTwProductListSelectionChangedRowsNum())
for i in range(row_num):
qtwsr = QTableWidgetSelectionRange(i, 0, i, column_num)
if (i in listRows):
self.twProductAsinNewaimCategoryList.setRangeSelected(
qtwsr, False)
else:
self.twProductAsinNewaimCategoryList.setRangeSelected(
qtwsr, True)
selectedCount += 1
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''行选中时提示'''
def actTwProductListSelectionChanged(self):
row = self.getTwProductListSelectionChangedRowsNum()
self.mainWin.statusMsg('您选中了{0}行'.format(row.__len__()))
pass
def actTwProductListHorSectionClicked(self, index):
print(index)
pass
'''打开高级搜索'''
def actTbSearchClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj)
self.vlProductAsinNewaimCategoryForm.insertWidget(
1, self.search_box, 1)
self.vlProductAsinNewaimCategoryForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
'''返回选中行'''
def getTwProductListSelectionChangedRowsNum(self):
result = []
try:
ranges = self.twProductAsinNewaimCategoryList.selectedRanges()
for i in range(len(ranges)):
for row in range(ranges[i].topRow(),
ranges[i].bottomRow() + 1):
result.append(row)
except Exception as e:
print(e)
# 去重返回
return list(set(result))
pass
'''删除选择行'''
def actTbDeleteClicked(self):
rowIndex = self.getTwProductListSelectionChangedRowsNum()
selectedCount = 0
if rowIndex.__len__() > 0:
if QMessageBox.information(None, '操作提示', '您确定要删除选中的记录吗？',
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes:
for i in rowIndex:
id = self.twProductAsinNewaimCategoryList.item(i, 0).text()
# 将Status设置为3（删除）
Pub_Product_Category.update(status=3).where(
Pub_Product_Category.id == id).execute()
# 删除与SKU关系表下的记录
try:
row = Pub_Product_Asin_Category_Relation.get(
Pub_Product_Asin_Category_Relation.category_id ==
id)
row.delete_instance()
except Pub_Product_Asin_Category_Relation.DoesNotExist:
print('Categories are not associated with SKU')
selectedCount += 1
self.actTbRefreshClicked()
self.mainWin.statusMsg('您删除了{0}行'.format(selectedCount))
else:
QMessageBox.information(None, '操作提示', '请先选择要删除的记录',
QMessageBox.Yes)
pass
'''导入SKU和Categeroy的关系表'''
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):
# 根据分类名查询对应分类表ID
nodeId = row[2]
sku = row[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 CATEGORY表中
Pub_Product_Asin_Category_Relation.insert(
product_id=skuId,
category_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()
pass
class WidgetProductLine(QWidget, Ui_ProductLineForm):
_ = QCoreApplication.translate
def __init__(self, mainWin):
super(WidgetProductLine, self).__init__()
self.mainWin = mainWin
self.myWidget = QWidget()
self.setupUi(self.myWidget)
self.canMoveView = False
# 搜索窗口
self.search_box = None
self.searchObj = {
"fields": [{
'name': 'code',
'label': 'Code',
'type': 'string'
}, {
'name': 'title',
'label': 'Title',
'type': 'string'
}, {
'name': 'updated_at',
'label': 'Updated At',
'type': 'date'
}, {
'name': 'created_at',
'label': 'Created At',
'type': 'date'
}],
}
self.pageSize = 10000
self.tbRefresh.clicked.connect(self.actTbRefreshClicked)
self.tbSelection.clicked.connect(self.actTbSelectionClicked)
self.tbUnselection.clicked.connect(self.actTbUnselectionClicked)
self.tbInvertSelection.clicked.connect(
self.actTbInvertSelectionClicked)
self.twProductLine.itemSelectionChanged.connect(
self.acttwProductLineSelectionChanged)
self.twProductLine.horizontalHeader().sectionClicked.connect(
self.acttwProductLineHorSectionClicked)
self.tbSearch.clicked.connect(self.actTbSearchClicked)
# 初始化布局
self.initLayout()
self.initData()
self.setLayout(self.myWidget.layout())
def initLayout(self):
self.twProductLine.setColumnWidth(0, 80)
self.twProductLine.setColumnWidth(1, 240)
self.twProductLine.setColumnWidth(2, 280)
self.twProductLine.setColumnWidth(3, 140)
self.twProductLine.setColumnWidth(4, 140)
self.twProductLine.setColumnHidden(0, True)
pass
'''高级搜索数据'''
def goSearch(self, conditions, method=None):
_table_ent = 'Na_Product_Line'
_where = QueryBuildToStr(_table_ent, conditions)
self.initData(_where)
pass
def initData(self, _where=''):
try:
# 初始加载第一页最新数据
rows = Na_Product_Line.select().paginate(1, self.pageSize)
if (_where != ''): rows = eval('rows.where({0})'.format(_where))
self.twProductLine.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.twProductLine.setRowCount(_rowCount)
for i in range(_rowCount):
row = Na_Product_Line(**(rows[i].__data__))
self.mainWin.progressBarContinue()
item = utQTableWidgetItem(row.id)
# item.setCheckState(Qt.Unchecked)
self.twProductLine.setItem(i, 0, item)
self.twProductLine.setItem(i, 1,
utQTableWidgetItem(row.code))
self.twProductLine.setItem(i, 2,
utQTableWidgetItem(row.title))
self.twProductLine.setItem(
i, 3, utQTableWidgetItem(row.updated_at))
self.twProductLine.setItem(
i, 4, 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 actTbRefreshClicked(self):
self.initData()
pass
'''报表列表全选'''
def actTbSelectionClicked(self):
self.twProductLine.setFocus()
selectedCount = self.twProductLine.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twProductLine.columnCount() - 1)
self.twProductLine.setRangeSelected(qtwsr, True)
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''报表列表取消选择'''
def actTbUnselectionClicked(self):
self.twProductLine.setFocus()
selectedCount = self.twProductLine.rowCount()
qtwsr = QTableWidgetSelectionRange(
0, 0, selectedCount - 1,
self.twProductLine.columnCount() - 1)
self.twProductLine.setRangeSelected(qtwsr, False)
self.mainWin.statusMsg('您选中了{0}行'.format(0))
'''报表列表反选'''
def actTbInvertSelectionClicked(self):
self.twProductLine.setFocus()
row_num = self.twProductLine.rowCount()
column_num = self.twProductLine.columnCount() - 1
selectedCount = 0
if (row_num > 0):
listRows = set(self.gettwProductLineSelectionChangedRowsNum())
for i in range(row_num):
qtwsr = QTableWidgetSelectionRange(i, 0, i, column_num)
if (i in listRows):
self.twProductLine.setRangeSelected(qtwsr, False)
else:
self.twProductLine.setRangeSelected(qtwsr, True)
selectedCount += 1
self.mainWin.statusMsg('您选中了{0}行'.format(selectedCount))
'''行选中时提示'''
def acttwProductLineSelectionChanged(self):
row = self.gettwProductLineSelectionChangedRowsNum()
self.mainWin.statusMsg('您选中了{0}行'.format(row.__len__()))
pass
def acttwProductLineHorSectionClicked(self, index):
print(index)
pass
'''打开高级搜索'''
def actTbSearchClicked(self):
if self.search_box is None:
self.search_box = WidgetSearchField(self, self.searchObj)
self.vlProductLineForm.insertWidget(1, self.search_box, 1)
self.vlProductLineForm.setStretch(2, 100)
elif self.search_box.isHidden():
self.search_box.setHidden(False)
self.search_box.myWidget.close()
else:
self.search_box.setHidden(True)
pass
'''返回选中行'''
def gettwProductLineSelectionChangedRowsNum(self):
result = []
try:
ranges = self.twProductLine.selectedRanges()
for i in range(len(ranges)):
for row in range(ranges[i].topRow(),
ranges[i].bottomRow() + 1):
result.append(row)
except Exception as e:
print(e)
# 去重返回
return list(set(result))
pass