class Lodgers(QtWidgets.QMainWindow, Ui_lodgers):
def __init__(self, parent=None):
super(Lodgers, self).__init__(parent)
self.setupUi(self)
sdb = QSqlDatabase.addDatabase('QSQLITE')
sdb.setDatabaseName('baza.db')
sdb.open()
self.model = QSqlTableModel(db=sdb)
self.model.setTable("users")
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.select()
self.tableView_1.setModel(self.model)
self.rоом_beds = []
self.pushButton_9.clicked.connect(
self.on_add_record) # Добавить запись
self.pushButton_10.clicked.connect(
self.on_del_record) # Удалить запись
def on_add_record(self):
row = self.tableView_1.selectionModel().currentIndex().row()
if self.tableView_1.selectionModel().currentIndex().row() == -1:
row = 0
self.model.insertRow(row)
else:
row = row + 1
self.model.insertRow(row)
self.model.setData(self.model.index(row, 0), 'ФИО NEW')
self.model.setData(self.model.index(row, 1), '0000')
self.model.setData(self.model.index(row, 2), '0')
self.model.setData(self.model.index(row, 3), '0')
self.model.submit()
def on_del_record(self):
self.model.removeRow(self.tableView_1.currentIndex().row())
self.model.select()
def _getRooms(self):
self.rоом_beds = []
for row in range(self.model.rowCount()):
if self.model.data(self.model.index(row, 2)) == 1:
rоом_bed = str(self.model.data(
self.model.index(row, 1)))[:3], \
str(self.model.data(self.model.index(row, 1)))[3]
self.rоом_beds.append(rоом_bed)
def sql_exec(self):
model = QSqlTableModel()
model.setTable('students')
model.setEditStrategy(QSqlTableModel.OnFieldChange)
model.setHeaderData(0, Qt.Horizontal, 'ID')
model.setHeaderData(1, Qt.Horizontal, 'Class')
model.setHeaderData(2, Qt.Horizontal, 'Name')
model.setHeaderData(3, Qt.Horizontal, 'Score')
model.select()
model.insertRow(0) # 1
model.setData(model.index(0, 0), 201801010111)
model.setData(model.index(0, 1), '0101')
model.setData(model.index(0, 2), 'Who Cares')
model.setData(model.index(0, 3), 0.5)
model.submit()
self.setModel(model)
class SqlModel(object):
def __init__(self,databasetype="QSQLITE",databasename="data.db",sqltablename="里程碑"):
db=QSqlDatabase.addDatabase(databasetype)
db.setDatabaseName(databasename)
self.sqltablename=sqltablename
self.__tablemodel()
def __tablemodel(self):
self.model=QSqlTableModel()
self.model.setTable(self.sqltablename)
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.select()
def modelview(self):
self.view=QTableView()
self.view.setModel(self.model)
# modelwidget=QTabWidget()
# modelwidget.addTab(self.view, viewname)
return self.view
def tabadd(self):
row = self.model.rowCount()
self.model.insertRow(row)
index = self.model.index(row)
self.view.setCurrentIndex(index)
self.view.edit(index)
def tabdel(self):
index = self.modelview().currentIndex()
print(index)
# self.model.removeRow(index.row())
def tabupdate(self):
self.model.updateRowInTable()
def row(self):
row=self.model.rowCount()
return row
def line(self):
line=self.model.columnCount()
return line
def rowvalue(self):
rowvalue=self.model.insertRowIntoTable(QSqlRecord="")
def data(self):
self.model.data()
def run(self,WindowTitle= "data"):
app = QApplication(sys.argv)
dig=QDialog()
layout=QHBoxLayout()
layout.addWidget(self.modelview())
dig.setLayout(layout)
dig.setWindowTitle(WindowTitle)
dig.resize(430,450)
# dig.hide()
# time.sleep(10)
dig.show()
sys.exit(app.exec_())
class MainWindowView(QMainWindow, Ui_MainWindow):
def __init__(self,dbPath,tblName,parent=None):
super(MainWindowView, self).__init__(parent)
self.setupUi(self)
self.addBtn.clicked.connect(self.addBtnFunc)
self.updBtn.clicked.connect(self.updBtnFunc)
self.delBtn.clicked.connect(self.delBtnFunc)
self.dbPath="test.db"
self.curTable="test2"
###tableView与model绑定
self.tableModel=QSqlTableModel(self,QSqlDatabase.addDatabase('QSQLITE'))
self.tableModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.tableView.setModel(self.tableModel)
###self.model数据初始化
self.tableModel.database().setDatabaseName(self.dbPath)
self.tableModel.database().open()
self.tableModel.setTable(self.curTable)
self.tableModel.select()
#新增按钮关联的槽函数
def addBtnFunc(self,event):
f1=random.randint(1, 99)
self.tableModel.insertRows(0, 1)
self.tableModel.setData(self.tableModel.index(0, 0), f1)
self.tableModel.setData(self.tableModel.index(0, 1), "test")
self.tableModel.submitAll()
#修改按钮关联的槽函数
def updBtnFunc(self,event):
QMessageBox.information( self, '提醒', "updBtnFunc Call!")
#删除按钮关联的槽函数
def delBtnFunc(self,event):
rs=list(map(lambda x:x.row(),self.tableView.selectedIndexes()))
if len(rs)==0:
QMessageBox.information(self,'提醒','请先选中至少一行，再点击此按钮！')
return
for i in reversed(rs):
self.tableModel.removeRows(i,1)
self.tableModel.submitAll()
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.splitter)
## tableView显示属性设置
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.tabModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.ui.comboFields.addItem(fieldName)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
def __openTable(self): ##打开数据表
self.tabModel = QSqlTableModel(self, self.DB) #数据模型
self.tabModel.setTable("employee") #设置数据表
self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit
) #数据保存方式，OnManualSubmit , OnRowChange
self.tabModel.setSort(self.tabModel.fieldIndex("empNo"),
Qt.AscendingOrder) #排序
if (self.tabModel.select() == False): #查询数据失败
QMessageBox.critical(
self, "错误信息",
"打开数据表错误,错误信息\n" + self.tabModel.lastError().text())
return
self.__getFieldNames() #获取字段名和序号
##字段显示名
self.tabModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal, "工号")
self.tabModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名")
self.tabModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别")
self.tabModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal,
"出生日期")
self.tabModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal,
"省份")
self.tabModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal,
"部门")
self.tabModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资")
self.tabModel.setHeaderData(self.fldNum["Memo"], Qt.Horizontal,
"备注") #这两个字段不在tableView中显示
self.tabModel.setHeaderData(self.fldNum["Photo"], Qt.Horizontal, "照片")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("empNo"), Qt.Horizontal, "工号")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Name"), Qt.Horizontal, "姓名")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Gender"), Qt.Horizontal, "性别")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Birthday"), Qt.Horizontal, "出生日期")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Province"), Qt.Horizontal, "省份")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Department"),Qt.Horizontal, "部门")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Salary"), Qt.Horizontal, "工资")
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Memo"), Qt.Horizontal, "备注") #这两个字段不在tableView中显示
## self.tabModel.setHeaderData(self.tabModel.fieldIndex("Photo"), Qt.Horizontal, "照片")
##创建界面组件与数据模型的字段之间的数据映射
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.tabModel) #设置数据模型
self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)
##界面组件与tabModel的具体字段之间的联系
self.mapper.addMapping(self.ui.dbSpinEmpNo, self.fldNum["empNo"])
self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"])
self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"])
self.mapper.addMapping(self.ui.dbEditBirth, self.fldNum["Birthday"])
self.mapper.addMapping(self.ui.dbComboProvince,
self.fldNum["Province"])
self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"])
self.mapper.addMapping(self.ui.dbSpinSalary, self.fldNum["Salary"])
self.mapper.addMapping(self.ui.dbEditMemo, self.fldNum["Memo"])
self.mapper.toFirst() #移动到首记录
self.selModel = QItemSelectionModel(self.tabModel) #选择模型
self.selModel.currentChanged.connect(self.do_currentChanged) #当前项变化时触发
self.selModel.currentRowChanged.connect(
self.do_currentRowChanged) #选择行变化时
self.ui.tableView.setModel(self.tabModel) #设置数据模型
self.ui.tableView.setSelectionModel(self.selModel) #设置选择模型
self.ui.tableView.setColumnHidden(self.fldNum["Memo"], True) #隐藏列
self.ui.tableView.setColumnHidden(self.fldNum["Photo"], True) #隐藏列
##tableView上为“性别”和“部门”两个字段设置自定义代理组件
strList = ("男", "女")
self.__delegateSex = QmyComboBoxDelegate()
self.__delegateSex.setItems(strList, False)
self.ui.tableView.setItemDelegateForColumn(
self.fldNum["Gender"], self.__delegateSex) #Combbox选择型
strList = ("销售部", "技术部", "生产部", "行政部")
self.__delegateDepart = QmyComboBoxDelegate()
self.__delegateDepart.setItems(strList, True)
self.ui.tableView.setItemDelegateForColumn(self.fldNum["Department"],
self.__delegateDepart)
##更新actions和界面组件的使能状态
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecAppend.setEnabled(True)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actScan.setEnabled(True)
self.ui.groupBoxSort.setEnabled(True)
self.ui.groupBoxFilter.setEnabled(True)
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##选择数据库，打开数据表
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQLITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #打开数据表
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##保存修改
def on_actSubmit_triggered(self):
res = self.tabModel.submitAll()
if (res == False):
QMessageBox.information(
self, "消息", "数据保存错误,错误信息\n" + self.tabModel.lastError().text())
else:
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot() ##取消修改
def on_actRevert_triggered(self):
self.tabModel.revertAll()
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot() ##添加记录
def on_actRecAppend_triggered(self):
self.tabModel.insertRow(self.tabModel.rowCount(),
QModelIndex()) #在末尾添加一个记录
curIndex = self.tabModel.index(self.tabModel.rowCount() - 1,
1) #创建最后一行的ModelIndex
self.selModel.clearSelection() #清空选择项
self.selModel.setCurrentIndex(
curIndex, QItemSelectionModel.Select) #设置刚插入的行为当前选择行
currow = curIndex.row() #获得当前行
self.tabModel.setData(self.tabModel.index(currow,
self.fldNum["empNo"]),
2000 + self.tabModel.rowCount()) #自动生成编号
self.tabModel.setData(
self.tabModel.index(currow, self.fldNum["Gender"]), "男")
@pyqtSlot() ##插入记录
def on_actRecInsert_triggered(self):
curIndex = self.ui.tableView.currentIndex() #QModelIndex
self.tabModel.insertRow(curIndex.row(), QModelIndex())
self.selModel.clearSelection() #清除已有选择
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
@pyqtSlot() ##删除记录
def on_actRecDelete_triggered(self):
curIndex = self.selModel.currentIndex() #获取当前选择单元格的模型索引
self.tabModel.removeRow(curIndex.row()) #删除当前行
@pyqtSlot() ##清除照片
def on_actPhotoClear_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.tabModel.record(curRecNo) #获取当前记录,QSqlRecord
curRec.setNull("Photo") #设置为空值
self.tabModel.setRecord(curRecNo, curRec)
self.ui.dbLabPhoto.clear() #清除界面上的图片显示
@pyqtSlot() ##设置照片
def on_actPhoto_triggered(self):
fileName, filt = QFileDialog.getOpenFileName(self, "选择图片文件", "",
"照片(*.jpg)")
if (fileName == ''):
return
file = QFile(fileName) #fileName为图片文件名
file.open(QIODevice.ReadOnly)
try:
data = file.readAll() #QByteArray
finally:
file.close()
curRecNo = self.selModel.currentIndex().row()
curRec = self.tabModel.record(curRecNo) #获取当前记录QSqlRecord
curRec.setValue("Photo", data) #设置字段数据
self.tabModel.setRecord(curRecNo, curRec)
pic = QPixmap()
pic.loadFromData(data)
W = self.ui.dbLabPhoto.width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W)) #在界面上显示
@pyqtSlot() ##涨工资，遍历数据表所有记录
def on_actScan_triggered(self):
if (self.tabModel.rowCount() == 0):
return
for i in range(self.tabModel.rowCount()):
aRec = self.tabModel.record(i) #获取当前记录
## salary=aRec.value("Salary").toFloat() #错误，无需再使用toFloat()函数
salary = aRec.value("Salary")
salary = salary * 1.1
aRec.setValue("Salary", salary)
self.tabModel.setRecord(i, aRec)
if (self.tabModel.submitAll()):
QMessageBox.information(self, "消息", "涨工资计算完毕")
@pyqtSlot(int) ##排序字段变化
def on_comboFields_currentIndexChanged(self, index):
if self.ui.radioBtnAscend.isChecked():
self.tabModel.setSort(index, Qt.AscendingOrder)
else:
self.tabModel.setSort(index, Qt.DescendingOrder)
self.tabModel.select()
@pyqtSlot() ##升序
def on_radioBtnAscend_clicked(self):
self.tabModel.setSort(self.ui.comboFields.currentIndex(),
Qt.AscendingOrder)
self.tabModel.select()
@pyqtSlot() ##降序
def on_radioBtnDescend_clicked(self):
self.tabModel.setSort(self.ui.comboFields.currentIndex(),
Qt.DescendingOrder)
self.tabModel.select()
@pyqtSlot() ##过滤，男
def on_radioBtnMan_clicked(self):
self.tabModel.setFilter("Gender='男'")
## print(self.tabModel.filter())
## self.tabModel.select()
@pyqtSlot() ##数据过滤，女
def on_radioBtnWoman_clicked(self):
self.tabModel.setFilter("Gender='女' ")
## print(self.tabModel.filter())
## self.tabModel.select()
@pyqtSlot() ##取消数据过滤
def on_radioBtnBoth_clicked(self):
self.tabModel.setFilter("")
## print(self.tabModel.filter())
## self.tabModel.select()
## =============自定义槽函数===============================
def do_currentChanged(self, current, previous): ##更新actPost和actCancel 的状态
self.ui.actSubmit.setEnabled(self.tabModel.isDirty()) #有未保存修改时可用
self.ui.actRevert.setEnabled(self.tabModel.isDirty())
def do_currentRowChanged(self, current, previous): #行切换时的状态控制
self.ui.actRecDelete.setEnabled(current.isValid())
self.ui.actPhoto.setEnabled(current.isValid())
self.ui.actPhotoClear.setEnabled(current.isValid())
if (current.isValid() == False):
self.ui.dbLabPhoto.clear() #清除图片显示
return
self.mapper.setCurrentIndex(current.row()) #更新数据映射的行号
curRec = self.tabModel.record(current.row()) #获取当前记录,QSqlRecord类型
if (curRec.isNull("Photo")): #图片字段内容为空
self.ui.dbLabPhoto.clear()
else:
## data=bytearray(curRec.value("Photo")) #可以工作
data = curRec.value("Photo") # 也可以工作
pic = QPixmap()
pic.loadFromData(data)
W = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(W))
class Ui(Ui_Form):
def function(self):
self.OutExcelButton.clicked.connect(self.OutExcel) # 导出表格按钮
self.InSQLButton.clicked.connect(self.InSQL) # Excel导入信息按钮
self.OutPutSQLButton.clicked.connect(self.OutSQL) # 基本信息导出按钮
self.CementDateButton.clicked.connect(self.CementDate) # 水泥一览表信息按钮
self.MixpushButton.clicked.connect(self.MixDate) # 配合比表信息按钮
self.ChangePrarmerButton.clicked.connect(
self.ChangePrarmer) # 确认修改参数按钮
self.CementrefreshButton.clicked.connect(self.cement_refresh) # 刷新
self.CementsubmitButton.clicked.connect(self.cement_submit) # 提交
self.CementdelButton.clicked.connect(self.cement_del) # 删除
self.CementQueryButton.clicked.connect(self.query_cement) # 查询
self.mixrefreshButton.clicked.connect(self.mix_refresh) # 刷新
self.mixsubmitButton.clicked.connect(self.mix_submit) # 提交
self.MixDelButton.clicked.connect(self.mix_del) # 删除
self.MixqueryButton.clicked.connect(self.query_mix) # 查询
self.deletArtButton.clicked.connect(self.empty_Art) # 水泥一览表数据清空
self.deleMIXButton.clicked.connect(self.empty_Mix) # 配合比表数据清空
self.CementAddButton.clicked.connect(self.addArt) # 添加水泥按钮
self.mixAddButton.clicked.connect(self.addMix) # 添加配合比按钮
self.CementInfoButton.clicked.connect(self.CementInfo) # 选择水泥一览表记录
self.MixPoportionButton.clicked.connect(self.ConMixInsert) # 选择配合比表记录
self.ChoicConcreteUsageRecordButton.clicked.connect(
self.ChoicConcreteUsageRecord) # 选择使用记录表
self.OutSQLButton_2.clicked.connect(self.ChoicSQLPath) # 选择OutPutSQL路径
self.OutSQLButton_3.clicked.connect(self.OutPutSQL) # 导出SQL
self.OutPutButton.clicked.connect(self.ouputexcel) # 确认导出按钮
self.OutPutVoidButton.clicked.connect(self.outputvoidexcel) # 确认导出按钮
# 查看数据库是否存在
try:
parm = get_parm()
print('已获取SQL')
except BaseException:
print('创建SQL')
from DateBase.creat import creat_table
from DateBase.insert_value import parm_init
creat_table()
parm_init()
# 填充信息
parm = get_parm()
self.MinC_StrengthEdit.setText(str(parm.MinC_Strength))
self.MaxC_StrengthEdit.setText(str(parm.MaxC_Strength))
self.MinS_FinenessDensityEdit.setText(str(parm.MinS_FinenessDensity))
self.MaxS_FinenessDensityEdit.setText(str(parm.MaxS_FinenessDensity))
self.MinS_SurfaceDensityEdit.setText(str(parm.MinS_SurfaceDensity))
self.MaxS_SurfaceDensityEdit.setText(str(parm.MaxS_SurfaceDensity))
self.MinS_DensityEdit.setText(str(parm.MinS_Density))
self.MaxS_DensityEdit.setText(str(parm.MaxS_Density))
self.MinS_SlitContentEdit.setText(str(parm.MinS_SlitContent))
self.MaxS_SlitContentEdit.setText(str(parm.MaxS_SlitContent))
self.MinS_WaterContentEdit.setText(str(parm.MinS_WaterContent))
self.MaxS_WaterContentEdit.setText(str(parm.MaxS_WaterContent))
self.MinG_GrainContentEdit.setText(str(parm.MinG_GrainContent))
self.MaxG_GrainContentEdit.setText(str(parm.MaxG_GrainContent))
self.MinG_CrushLevelEdit.setText(str(parm.MinG_CrushLevel))
self.MaxG_CrushLevelEdit.setText(str(parm.MaxG_CrushLevel))
self.MinG_DensityEdit.setText(str(parm.MinG_Density))
self.MaxG_DensityEdit.setText(str(parm.MaxG_Density))
self.MinG_SlitContentEdit.setText(str(parm.MinG_SlitContent))
self.MaxG_SlitContentEdit.setText(str(parm.MaxG_SlitContent))
self.MinG_WaterContentEdit.setText(str(parm.MinG_WaterContent))
self.MaxG_WaterContentEdit.setText(str(parm.MaxG_WaterContent))
self.MinA_DensityEdit.setText(str(parm.MinA_Density))
self.MaxA_DensityEdit.setText(str(parm.MaxA_Density))
self.MinR7_CompressionEdit.setText(str(parm.MinR7_Compression))
self.MaxR7_CompressionEdit.setText(str(parm.MaxR7_Compression))
self.MinR28_CompressionEdit.setText(str(parm.MinR28_Compression))
self.MaxR28_CompressionEdit.setText(str(parm.MaxR28_Compression))
_translate = QtCore.QCoreApplication.translate
PicName = getpiname()
for i in range(len(PicName)):
self.Project1Manager.addItem("")
self.Project1Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project1FillSheeter.addItem("")
self.Project1FillSheeter.setItemText(
i, _translate("Form", PicName[i]))
self.Project2Manager.addItem("")
self.Project2Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project2Checker.addItem("")
self.Project2Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project2Try.addItem("")
self.Project2Try.setItemText(i, _translate("Form", PicName[i]))
self.Project3MakeSheet.addItem("")
self.Project3MakeSheet.setItemText(i,
_translate("Form", PicName[i]))
self.Project4Manager.addItem("")
self.Project4Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project4Checker.addItem("")
self.Project4Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project4Calculate.addItem("")
self.Project4Calculate.setItemText(i,
_translate("Form", PicName[i]))
self.Project5Manager.addItem("")
self.Project5Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project5Filler.addItem("")
self.Project5Filler.setItemText(i, _translate("Form", PicName[i]))
self.Project7Manager.addItem("")
self.Project7Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project7Checker.addItem("")
self.Project7Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project7try.addItem("")
self.Project7try.setItemText(i, _translate("Form", PicName[i]))
self.Project9Manager.addItem("")
self.Project9Manager.setItemText(i, _translate("Form", PicName[i]))
self.Project9Checker.addItem("")
self.Project9Checker.setItemText(i, _translate("Form", PicName[i]))
self.Project9Record.addItem("")
self.Project9Record.setItemText(i, _translate("Form", PicName[i]))
self.Project10Manager.addItem("")
self.Project10Manager.setItemText(i,
_translate("Form", PicName[i]))
self.Project10Examine.addItem("")
self.Project10Examine.setItemText(i,
_translate("Form", PicName[i]))
self.Project10Checker.addItem("")
self.Project10Checker.setItemText(i,
_translate("Form", PicName[i]))
self.Project11Manager.addItem("")
self.Project11Manager.setItemText(i,
_translate("Form", PicName[i]))
self.Project11Checker.addItem("")
self.Project11Checker.setItemText(i,
_translate("Form", PicName[i]))
self.Project1Manager.setCurrentText(str(parm.Project1Manager))
self.Project1FillSheeter.setCurrentText(str(parm.Project1FillSheeter))
self.Project2InspectCodeEdit.setText(str(parm.Project2InspectCodeEdit))
self.Project2Manager.setCurrentText(str(parm.Project2Manager))
self.Project2Checker.setCurrentText(str(parm.Project2Checker))
self.Project2Try.setCurrentText(str(parm.Project2Try))
self.Project3MakeSheet.setCurrentText(str(parm.Project3MakeSheet))
self.Project3InspectCodeEdit.setText(str(parm.Project3InspectCodeEdit))
self.Project4Manager.setCurrentText(str(parm.Project4Manager))
self.Project4Checker.setCurrentText(str(parm.Project4Checker))
self.Project4Calculate.setCurrentText(str(parm.Project4Calculate))
self.Project4InspectCodeEdit.setText(str(parm.Project4InspectCodeEdit))
self.Project5Manager.setCurrentText(str(parm.Project5Manager))
self.Project5Filler.setCurrentText(str(parm.Project5Filler))
self.Project7ConDesignSpeciEdit.setText(
str(parm.Project7ConDesignSpeciEdit))
self.Project7CodeEdit.setText(str(parm.Project7CodeEdit))
self.Project7Manager.setCurrentText(str(parm.Project7Manager))
self.Project7Checker.setCurrentText(str(parm.Project7Checker))
self.Project7try.setCurrentText(str(parm.Project7try))
self.Project9Manager.setCurrentText(str(parm.Project9Manager))
self.Project9Checker.setCurrentText(str(parm.Project9Checker))
self.Project9Record.setCurrentText(str(parm.Project9Record))
self.Project10ConTestReportTestBasisEdit.setText(
str(parm.Project10ConTestReportTestBasisEdit))
self.Project10InspectCodeEdit.setText(
str(parm.Project10InspectCodeEdit))
from PyQt5.QtCore import QTime
time = str(parm.Project10TimeEdit).split(':')
time = QTime(int(time[0]), int(time[1]))
self.Project10TimeEdit.setTime(time)
self.Project10MaxCreepEdit.setText(str(parm.Project10MaxCreepEdit))
self.Project10MinCreepEdit.setText(str(parm.Project10MinCreepEdit))
self.Project10Manager.setCurrentText(str(parm.Project10Manager))
self.Project10Examine.setCurrentText(str(parm.Project10Examine))
self.Project10Checker.setCurrentText(str(parm.Project10Checker))
self.Project11Manager.setCurrentText(str(parm.Project11Manager))
self.Project11Checker.setCurrentText(str(parm.Project11Checker))
# 插入水泥一览表
self.con2 = QSqlDatabase.addDatabase('QSQLITE')
self.con2.setDatabaseName(db_path)
# con2.exec_("PRAGMA foreign_keys = ON;")
self.CementMode = QSqlTableModel()
self.CementMode.setTable("cement_attribute_data")
self.CementMode.setSort(0, Qt.AscendingOrder)
self.CementMode.setEditStrategy(self.CementMode.OnManualSubmit)
self.CementMode.setHeaderData(0, Qt.Horizontal, "id")
self.CementMode.setHeaderData(1, Qt.Horizontal, "进场日期")
self.CementMode.setHeaderData(2, Qt.Horizontal, "水泥品种")
self.CementMode.setHeaderData(3, Qt.Horizontal, "生产厂家")
self.CementMode.setHeaderData(4, Qt.Horizontal, "生产日期")
self.CementMode.setHeaderData(5, Qt.Horizontal, "编号")
self.CementMode.setHeaderData(6, Qt.Horizontal, "数量(T)")
self.CementMode.setHeaderData(7, Qt.Horizontal, "安定性")
self.CementMode.setHeaderData(8, Qt.Horizontal, "初凝")
self.CementMode.setHeaderData(9, Qt.Horizontal, "终凝")
self.CementMode.setHeaderData(10, Qt.Horizontal, "R3抗压")
self.CementMode.setHeaderData(11, Qt.Horizontal, "R28抗压")
self.CementMode.setHeaderData(12, Qt.Horizontal, "R3抗折")
self.CementMode.setHeaderData(13, Qt.Horizontal, "R28抗折")
self.CementMode.setHeaderData(14, Qt.Horizontal, "是否优先")
self.CementMode.select()
self.CementtableView.setModel(self.CementMode)
self.CementtableView.setSelectionMode(QTableView.SingleSelection)
self.CementtableView.setSelectionBehavior(QTableView.SelectRows)
self.CementtableView.resizeColumnsToContents()
# 配合比表
self.MixMode = QSqlTableModel()
self.MixMode.setTable("concrete_mix")
self.MixMode.setSort(0, Qt.AscendingOrder)
self.MixMode.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.MixMode.setHeaderData(1 - 1, Qt.Horizontal, "名称")
self.MixMode.setHeaderData(2 - 1, Qt.Horizontal, "配合比编号")
self.MixMode.setHeaderData(3 - 1, Qt.Horizontal, "强度等级 ")
self.MixMode.setHeaderData(4 - 1, Qt.Horizontal, "抗渗等级")
self.MixMode.setHeaderData(5 - 1, Qt.Horizontal, "膨胀")
self.MixMode.setHeaderData(6 - 1, Qt.Horizontal, "配合比编号2")
self.MixMode.setHeaderData(7 - 1, Qt.Horizontal, "坍落度")
self.MixMode.setHeaderData(8 - 1, Qt.Horizontal, "标准差(MPa)")
self.MixMode.setHeaderData(9 - 1, Qt.Horizontal, "配制强度(MPa)")
self.MixMode.setHeaderData(10 - 1, Qt.Horizontal, "水W")
self.MixMode.setHeaderData(11 - 1, Qt.Horizontal, "水泥C")
self.MixMode.setHeaderData(12 - 1, Qt.Horizontal, "粉煤灰F")
self.MixMode.setHeaderData(13 - 1, Qt.Horizontal, "砂S")
self.MixMode.setHeaderData(14 - 1, Qt.Horizontal, "石G")
self.MixMode.setHeaderData(15 - 1, Qt.Horizontal, "水胶比A/P")
self.MixMode.setHeaderData(16 - 1, Qt.Horizontal, "砂率 BS")
self.MixMode.setHeaderData(17 - 1, Qt.Horizontal, "外加剂掺量A%")
self.MixMode.setHeaderData(18 - 1, Qt.Horizontal, "外加剂用量LS-JS(B)")
self.MixMode.setHeaderData(19 - 1, Qt.Horizontal, "膨胀剂用量")
self.MixMode.setHeaderData(20 - 1, Qt.Horizontal, "质量密度 (容重）Mcp")
self.MixMode.setHeaderData(21 - 1, Qt.Horizontal, "初凝时间")
self.MixMode.setHeaderData(22 - 1, Qt.Horizontal, "终凝时间")
self.MixMode.select()
self.MixtableView.setModel(self.MixMode)
self.MixtableView.setSelectionMode(QTableView.SingleSelection)
self.MixtableView.setSelectionBehavior(QTableView.SelectRows)
self.MixtableView.resizeColumnsToContents()
if self.con2.isOpen():
self.con2.close()
def OutExcel(self):
if self.con2.isOpen():
self.con2.close()
self.stackedWidget.setCurrentIndex(0)
def InSQL(self):
if self.con2.isOpen():
self.con2.close()
self.stackedWidget.setCurrentIndex(1)
def OutSQL(self):
if self.con2.isOpen():
self.con2.close()
self.stackedWidget.setCurrentIndex(2)
def CementDate(self):
self.stackedWidget.setCurrentIndex(3)
self.cement_refresh()
if not self.con2.isOpen():
self.con2.open()
self.CementMode = QSqlTableModel()
self.CementMode.setTable("cement_attribute_data")
self.CementMode.setSort(0, Qt.AscendingOrder)
self.CementMode.setEditStrategy(self.CementMode.OnManualSubmit)
self.CementMode.setHeaderData(0, Qt.Horizontal, "id")
self.CementMode.setHeaderData(1, Qt.Horizontal, "进场日期")
self.CementMode.setHeaderData(2, Qt.Horizontal, "水泥品种")
self.CementMode.setHeaderData(3, Qt.Horizontal, "生产厂家")
self.CementMode.setHeaderData(4, Qt.Horizontal, "生产日期")
self.CementMode.setHeaderData(5, Qt.Horizontal, "编号")
self.CementMode.setHeaderData(6, Qt.Horizontal, "数量(T)")
self.CementMode.setHeaderData(7, Qt.Horizontal, "安定性")
self.CementMode.setHeaderData(8, Qt.Horizontal, "初凝")
self.CementMode.setHeaderData(9, Qt.Horizontal, "终凝")
self.CementMode.setHeaderData(10, Qt.Horizontal, "R3抗压")
self.CementMode.setHeaderData(11, Qt.Horizontal, "R28抗压")
self.CementMode.setHeaderData(12, Qt.Horizontal, "R3抗折")
self.CementMode.setHeaderData(13, Qt.Horizontal, "R28抗折")
self.CementMode.setHeaderData(14, Qt.Horizontal, "是否优先")
self.CementMode.select()
self.CementtableView.setModel(self.CementMode)
self.CementtableView.setSelectionMode(QTableView.SingleSelection)
self.CementtableView.setSelectionBehavior(QTableView.SelectRows)
self.CementtableView.resizeColumnsToContents()
def MixDate(self):
self.stackedWidget.setCurrentIndex(4)
self.mix_refresh()
if not self.con2.isOpen():
self.con2.open()
self.MixMode = QSqlTableModel()
self.MixMode.setTable("concrete_mix")
self.MixMode.setSort(0, Qt.AscendingOrder)
self.MixMode.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.MixMode.setHeaderData(1 - 1, Qt.Horizontal, "名称")
self.MixMode.setHeaderData(2 - 1, Qt.Horizontal, "配合比编号")
self.MixMode.setHeaderData(3 - 1, Qt.Horizontal, "强度等级 ")
self.MixMode.setHeaderData(4 - 1, Qt.Horizontal, "抗渗等级")
self.MixMode.setHeaderData(5 - 1, Qt.Horizontal, "膨胀")
self.MixMode.setHeaderData(6 - 1, Qt.Horizontal, "配合比编号2")
self.MixMode.setHeaderData(7 - 1, Qt.Horizontal, "坍落度")
self.MixMode.setHeaderData(8 - 1, Qt.Horizontal, "标准差(MPa)")
self.MixMode.setHeaderData(9 - 1, Qt.Horizontal, "配制强度(MPa)")
self.MixMode.setHeaderData(10 - 1, Qt.Horizontal, "水W")
self.MixMode.setHeaderData(11 - 1, Qt.Horizontal, "水泥C")
self.MixMode.setHeaderData(12 - 1, Qt.Horizontal, "粉煤灰F")
self.MixMode.setHeaderData(13 - 1, Qt.Horizontal, "砂S")
self.MixMode.setHeaderData(14 - 1, Qt.Horizontal, "石G")
self.MixMode.setHeaderData(15 - 1, Qt.Horizontal, "水胶比A/P")
self.MixMode.setHeaderData(16 - 1, Qt.Horizontal, "砂率 BS")
self.MixMode.setHeaderData(17 - 1, Qt.Horizontal, "外加剂掺量A%")
self.MixMode.setHeaderData(18 - 1, Qt.Horizontal, "外加剂用量LS-JS(B)")
self.MixMode.setHeaderData(19 - 1, Qt.Horizontal, "膨胀剂用量")
self.MixMode.setHeaderData(20 - 1, Qt.Horizontal, "质量密度 (容重）Mcp")
self.MixMode.setHeaderData(21 - 1, Qt.Horizontal, "初凝时间")
self.MixMode.setHeaderData(22 - 1, Qt.Horizontal, "终凝时间")
self.MixMode.select()
self.MixtableView.setModel(self.MixMode)
self.MixtableView.setSelectionMode(QTableView.SingleSelection)
self.MixtableView.setSelectionBehavior(QTableView.SelectRows)
self.MixtableView.resizeColumnsToContents()
def ChangePrarmer(self):
if QMessageBox.question(QWidget(), "Question", "是否确定修改参数？",
QMessageBox.Ok | QMessageBox.Cancel,
QMessageBox.Ok) == QMessageBox.Ok:
try:
parm = Parameter(
MinC_Strength=float(self.MinC_StrengthEdit.text()),
MaxC_Strength=float(self.MaxC_StrengthEdit.text()),
MinS_FinenessDensity=float(
self.MinS_FinenessDensityEdit.text()),
MaxS_FinenessDensity=float(
self.MaxS_FinenessDensityEdit.text()),
MinS_SurfaceDensity=float(
self.MinS_SurfaceDensityEdit.text()),
MaxS_SurfaceDensity=float(
self.MaxS_SurfaceDensityEdit.text()),
MinS_Density=float(self.MinS_DensityEdit.text()),
MaxS_Density=float(self.MaxS_DensityEdit.text()),
MinS_SlitContent=float(self.MinS_SlitContentEdit.text()),
MaxS_SlitContent=float(self.MaxS_SlitContentEdit.text()),
MinS_WaterContent=float(self.MinS_WaterContentEdit.text()),
MaxS_WaterContent=float(self.MaxS_WaterContentEdit.text()),
MinG_GrainContent=float(self.MinG_GrainContentEdit.text()),
MaxG_GrainContent=float(self.MaxG_GrainContentEdit.text()),
MinG_CrushLevel=float(self.MinG_CrushLevelEdit.text()),
MaxG_CrushLevel=float(self.MaxG_CrushLevelEdit.text()),
MinG_Density=float(self.MinG_DensityEdit.text()),
MaxG_Density=float(self.MaxG_DensityEdit.text()),
MinG_SlitContent=float(self.MinG_SlitContentEdit.text()),
MaxG_SlitContent=float(self.MaxG_SlitContentEdit.text()),
MinG_WaterContent=float(self.MinG_WaterContentEdit.text()),
MaxG_WaterContent=float(self.MaxG_WaterContentEdit.text()),
MinA_Density=float(self.MinA_DensityEdit.text()),
MaxA_Density=float(self.MaxA_DensityEdit.text()),
MinR7_Compression=float(self.MinR7_CompressionEdit.text()),
MaxR7_Compression=float(self.MaxR7_CompressionEdit.text()),
MinR28_Compression=float(
self.MinR28_CompressionEdit.text()),
MaxR28_Compression=float(
self.MaxR28_CompressionEdit.text()),
Project1Manager=self.Project1Manager.currentText(),
Project1FillSheeter=self.Project1FillSheeter.currentText(),
Project2InspectCodeEdit=self.Project2InspectCodeEdit.text(
),
Project2Manager=self.Project2Manager.currentText(),
Project2Checker=self.Project2Checker.currentText(),
Project2Try=self.Project2Try.currentText(),
Project3MakeSheet=self.Project3MakeSheet.currentText(),
Project3InspectCodeEdit=self.Project3InspectCodeEdit.text(
),
Project4Manager=self.Project4Manager.currentText(),
Project4Checker=self.Project4Checker.currentText(),
Project4Calculate=self.Project4Calculate.currentText(),
Project4InspectCodeEdit=self.Project4InspectCodeEdit.text(
),
Project5Manager=self.Project5Manager.currentText(),
Project5Filler=self.Project5Filler.currentText(),
Project7ConDesignSpeciEdit=self.Project7ConDesignSpeciEdit.
text(),
Project7CodeEdit=self.Project7CodeEdit.text(),
Project7Manager=self.Project7Manager.currentText(),
Project7Checker=self.Project7Checker.currentText(),
Project7try=self.Project7try.currentText(),
Project9Manager=self.Project9Manager.currentText(),
Project9Checker=self.Project9Checker.currentText(),
Project9Record=self.Project9Record.currentText(),
Project10ConTestReportTestBasisEdit=self.
Project10ConTestReportTestBasisEdit.text(),
Project10InspectCodeEdit=self.Project10InspectCodeEdit.
text(),
Project10TimeEdit=self.Project10TimeEdit.text(),
Project10MaxCreepEdit=self.Project10MaxCreepEdit.text(),
Project10MinCreepEdit=self.Project10MinCreepEdit.text(),
Project10Manager=self.Project10Manager.currentText(),
Project10Examine=self.Project10Examine.currentText(),
Project10Checker=self.Project10Checker.currentText(),
Project11Manager=self.Project11Manager.currentText(),
Project11Checker=self.Project11Checker.currentText())
session.add(parm)
session.commit()
QMessageBox.information(QWidget(), "修改", "成功")
except BaseException:
QMessageBox.information(QWidget(), "错误",
"修改失败！！！！\n请核对数据是否有误或留空白。")
def cement_refresh(self):
self.CementMode.setFilter("1=1")
self.CementMode.select()
def cement_submit(self):
if (QMessageBox.question(QWidget(), "修改", "是否确定修改", QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes):
try:
self.CementMode.submitAll()
QMessageBox.about(QWidget(), '修改', '修改成功')
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def cement_del(self):
try:
index = self.CementtableView.currentIndex()
self.CementMode.removeRow(index.row())
self.CementMode.submitAll()
self.CementMode.select()
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def query_cement(self):
CementId = self.CementIdEdit_2.text()
IsPriority = self.IsPriorityEdit.text()
sql = "1=1 "
if CementId != '':
sql = sql + \
"and CementId like '%CementId_1%' ".replace('CementId_1', CementId)
if IsPriority != '':
sql = sql + \
"and PriorityLevel like '%PriorityLevel_1%' ".replace('PriorityLevel_1', IsPriority)
print(sql)
self.CementMode.setFilter(sql)
self.CementMode.select()
def mix_refresh(self):
self.MixMode.setFilter("1=1")
self.CementMode.select()
def mix_submit(self):
if (QMessageBox.question(QWidget(), "修改", "是否确定修改", QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes):
try:
self.MixMode.submitAll()
QMessageBox.about(QWidget(), '修改', '修改成功')
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def mix_del(self):
try:
index = self.MixtableView.currentIndex()
self.MixMode.removeRow(index.row())
self.MixMode.submitAll()
self.MixMode.select()
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def query_mix(self):
MixName = self.MixNameEdit.text()
MixId = self.MixIdEdit.text()
MixStrength = self.MixStrengthEdit.text()
MixLevel = self.MixLevelEdit.text()
sql = "1=1 "
if MixName != '':
sql = sql + \
"and ConcreteName like '%MixName_1%' ".replace('MixName_1', MixName)
if MixId != '':
sql = sql + \
"and MixRatioID like '%MixId_1%' ".replace('MixId_1', MixId)
if MixStrength != '':
sql = sql + \
"and StrengthLevel like '%MixStrength_1%' ".replace('MixStrength_1', MixStrength)
if MixLevel != '':
sql = sql + \
"and ImperLevel like '%MixLevel_1%' ".replace('MixLevel_1', MixLevel)
print(sql)
self.MixMode.setFilter(sql)
self.MixMode.select()
def empty_Art(self):
if (QMessageBox.question(QWidget(), "删除", "是否确定删除所有数据", QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes):
try:
self.con2.exec_("delete from cement_attribute_data")
self.CementMode.select()
QMessageBox.about(QWidget(), '删除', '删除成功')
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def empty_Mix(self):
if (QMessageBox.question(QWidget(), "删除", "是否确定删除所有数据", QMessageBox.Yes
| QMessageBox.No) == QMessageBox.Yes):
try:
self.con2.exec_("delete from concrete_mix")
self.MixMode.select()
QMessageBox.about(QWidget(), '删除', '删除成功')
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def addArt(self):
try:
row = self.CementMode.rowCount()
self.CementMode.insertRow(row)
index = self.CementMode.index(row, 0)
self.CementtableView.setCurrentIndex(index)
self.CementtableView.edit(index)
self.CementMode.submit()
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def addMix(self):
try:
row = self.MixMode.rowCount()
self.MixMode.insertRow(row)
index = self.MixMode.index(row, 0)
self.MixtableView.setCurrentIndex(index)
self.MixtableView.edit(index)
self.MixMode.submit()
except BaseException:
QMessageBox.about(QWidget(), '失败', '失败')
def CementInfo(self):
from DateBase.insert_value import insert_cement_attribute_data
try:
filePath, filetype = QFileDialog.getOpenFileName(
QWidget(), "选取水泥资料一览表", "../", "Text Files (*.xlsx)")
if filePath == '':
raise Exception('请选择文件夹')
self.CementInfoPath.setText(filePath)
filename = self.CementInfoPath.text()
print(filename)
insert_cement_attribute_data(filename)
QMessageBox.information(QWidget(), "成功", "水泥资料一览表导入成功")
except BaseException:
QMessageBox.information(QWidget(), "错误",
"添加失败！！！！！\n请检查水泥资料一览表表格的数据格式。\n添加失败！！！！")
def ConMixInsert(self):
from DateBase.insert_value import insert_concrete_mix
try:
filePath, filetype = QFileDialog.getOpenFileName(
QWidget(), "选取配合比选用汇总表", "../", "Text Files (*.xlsx)")
if filePath == '':
raise Exception('请选择文件夹')
self.MixPoportionPath.setText(filePath)
filename = self.MixPoportionPath.text()
print('ss')
insert_concrete_mix(filename)
QMessageBox.information(QWidget(), "成功", "配合比选用汇总表导入成功")
except BaseException:
QMessageBox.information(QWidget(), "错误",
"添加失败！！！！！\n请检查配合比选用汇总表格的数据格式。\n添加失败！！！！")
def ChoicConcreteUsageRecord(self):
try:
filePath, filetype = QFileDialog.getOpenFileName(
QWidget(), "选取水泥使用一览表", "../", "Text Files (*.xlsx)")
if filePath == '':
raise Exception('请选择文件夹')
self.ConcreteUsageRecordPath.setText(filePath)
self.OutPutButton.setEnabled(True)
self.OutPutVoidButton.setEnabled(True)
except BaseException:
pass
def ChoicSQLPath(self):
try:
filePath = QFileDialog.getExistingDirectory(
QWidget(),
"选取生成表格的文件夹",
"../",
)
print(filePath)
if filePath == '':
print('没有选中文件')
raise Exception('请选择文件夹')
self.OutSQLButton_3.setEnabled(True)
self.OutSQLPath.setText(filePath)
except BaseException:
pass
def OutPutSQL(self):
try:
art = session.query(CementAttributeDatum).all()
mix = session.query(ConcreteMix).all()
wb = Workbook()
ws = wb.active
ws['A1'] = '水泥资料一览表'
ws.merge_cells('A1:N1')
ws['A2'] = '进场日期'
ws['B2'] = '水泥品种'
ws['C2'] = '生产厂家'
ws['D2'] = '生产日期'
ws['E2'] = '编号'
ws['F2'] = '数量(T)'
ws['G2'] = '安定性'
ws['H2'] = '初凝'
ws['I2'] = '终凝'
ws['J2'] = 'R3抗压'
ws['K2'] = 'R28抗压'
ws['L2'] = 'R3抗折'
ws['M2'] = 'R28抗折'
ws['N2'] = '是否优先'
for i in range(len(art)):
one_art = art[i]
ws['A' + str(i + 3)] = one_art.ArrivalTime
ws['B' + str(i + 3)] = one_art.CementVariety
ws['C' + str(i + 3)] = one_art.Manufacturer
ws['D' + str(i + 3)] = one_art.ProductionDate
ws['E' + str(i + 3)] = one_art.CementId
ws['F' + str(i + 3)] = one_art.CementNumber
if one_art.IsStability == 1:
ws['G' + str(i + 3)] = '合格'
else:
ws['G' + str(i + 3)] = '不合格'
ws['H' + str(i + 3)] = one_art.InitialTime
ws['I' + str(i + 3)] = one_art.FinalTime
ws['J' + str(i + 3)] = one_art.R3_Compression
ws['K' + str(i + 3)] = one_art.R28_Compression
ws['L' + str(i + 3)] = one_art.R3_Bending
ws['M' + str(i + 3)] = one_art.R28_Bending
ws['N' + str(i + 3)] = one_art.PriorityLevel
wb.save(self.OutSQLPath.text() + '/水泥一览表test.xlsx')
# SQL配合比表
wb = Workbook()
ws = wb.active
ws['A1'] = '佛山市三水区建友混凝土有限公司'
ws['A2'] = '配合比选用汇总表'
ws.merge_cells('A1:W1')
ws.merge_cells('A2:W2')
ws['A4'] = '名称'
ws['B4'] = '配合比编号'
ws['C4'] = '强度等级 '
ws.merge_cells('C4:D4')
ws['E4'] = '抗渗等级'
ws['F4'] = '膨胀'
ws['G4'] = '配合比编号'
ws['H4'] = '坍落度'
ws['I4'] = '标准差(MPa)'
ws['J4'] = '配制强度(MPa)W'
ws['K4'] = '水W'
ws['L4'] = '水泥C'
ws['M4'] = '粉煤灰F'
ws['N4'] = '砂S'
ws['O4'] = '石G'
ws['P4'] = '水胶比A/P'
ws['Q4'] = '砂率 BS'
ws['R4'] = '外加剂掺量A%'
ws['S4'] = '外加剂用量LS-JS(B)'
ws['T4'] = '膨胀剂用量'
ws['U4'] = '质量密度 (容重）Mcp'
ws['V4'] = '初凝时间'
ws['W4'] = '终凝时间'
for i in range(len(mix)):
ws['C' + str(i + 5)] = 'C'
ws['A' + str(i + 5)] = mix[i].ConcreteName
ws['B' + str(i + 5)] = mix[i].MixRatioID
ws['D' + str(i + 5)] = mix[i].StrengthLevel
ws['E' + str(i + 5)] = mix[i].ImperLevel
if mix[i].ImperLevel is None:
ws['E' + str(i + 5)] = '/'
ws['F' + str(i + 5)] = mix[i].SwellLevel
if mix[i].SwellLevel is None:
ws['F' + str(i + 5)] = '/'
ws['G' + str(i + 5)] = mix[i].MixRatioName
ws['H' + str(i + 5)] = mix[i].SlumpNum
ws['I' + str(i + 5)] = mix[i].StandardDeviation
ws['J' + str(i + 5)] = mix[i].ConcreteStrengh
ws['K' + str(i + 5)] = mix[i].WaterNum
ws['L' + str(i + 5)] = mix[i].CementNum
ws['N' + str(i + 5)] = mix[i].FlyashNum
ws['M' + str(i + 5)] = mix[i].SandNum
ws['O' + str(i + 5)] = mix[i].GravelNum
ws['P' + str(i + 5)] = mix[i].CementRatio
ws['Q' + str(i + 5)] = mix[i].SandRatio
ws['R' + str(i + 5)] = mix[i].AdmixtureAmount
ws['S' + str(i + 5)] = mix[i].AdmixtureNum
ws['T' + str(i + 5)] = mix[i].SwellingNum
ws['U' + str(i + 5)] = mix[i].MassDensity
ws['V' + str(i + 5)] = mix[i].InitialTime
ws['W' + str(i + 5)] = mix[i].FinalTime
wb.save(self.OutSQLPath.text() + '/配合比表test.xlsx')
QMessageBox.information(QWidget(), "成功", "请在刚刚选中的目录下查看文件")
except BaseException:
QMessageBox.information(QWidget(), "错误",
"添加失败！！！！！\n请检查配合比选用汇总表格的数据格式。\n添加失败！！！！")
def ouputexcel(self):
try:
books = []
book = load_workbook(self.ConcreteUsageRecordPath.text())
print('成功读取水泥使用记录表')
# 生成12份表格
book1, book7 = ConUseBuyRecord(book)
print("17")
book2, book3, book4 = ConStrengReport(book)
print("234")
book5 = CreateConFacyoryCard(book, book1)
print("5")
book6 = CreateSlumpCard(book5)
print("6")
book8 = CreteConUseProve(book)
print("8")
book9 = ConQualityRecord(book, book3, book7)
print("9")
book10 = PermeabilityTestReport(book, book7)
print("10")
books.append(book1)
books.append(book2)
books.append(book3)
books.append(book4)
books.append(book5)
books.append(book6)
books.append(book7)
books.append(book8)
books.append(book9)
books.append(book10)
except BaseException:
print("读取失败")
try:
# 文件名处理
fileNames = [
'1、水泥购进、使用一览表', '2、混凝土试件抗压强度检验报告', '3、混凝土试块强度试验结果汇总表',
'4、标准养护混凝土抗压强度计算表', '5、建友商品混凝土出厂合格证', '6、混凝土坍落度验收表',
'7、混凝土配合比设计报告', '8、工地使用预拌混凝土证明书', '9、混凝土搅拌质量记录表',
'10、抗渗性能检测报告', '11、施工配合比'
]
filePath = QFileDialog.getExistingDirectory(
QWidget(),
"选取生成表格的文件夹",
"C:/",
)
print(filePath)
if filePath == '':
print('没有选中文件')
raise Exception('请选择文件夹')
filePaths = [] # 存放文件路劲
for i in fileNames:
filePaths.append(filePath + '/' + i + '.xlsx')
for j in range(1, 11):
books[j - 1].save(filePaths[j - 1])
# 记录
filePaths = []
filePath = his()
for i in fileNames:
filePaths.append(filePath + '/' + i + '.xlsx')
print(filePaths)
for j in range(1, 11):
books[j - 1].save(filePaths[j - 1])
QMessageBox.information(QWidget(), "成功导出", "请在您刚刚选定的文件夹内查看生成的文件")
except BaseException:
QMessageBox.information(QWidget(), "错误", "添加失败！！！！！\n添加失败！！！！")
def outputvoidexcel(self):
from Excel.CreateMode.CreateConFactoryCardMode17 import ConUseBuyRecordMode
from Excel.CreateMode.CreateConStrentReportMode234 import ConStrengReportMode
from Excel.CreateMode.CreateConFactoryCardMode56 import CreateConFacyoryCardMode, CreateSlumpCardMode
from Excel.CreateMode.ConUseProveMode8 import CreteConUseProveMode
from Excel.CreateMode.ConQualityRecordMode9 import ConQualityRecordMode
from Excel.CreateMode.PermeabilityTestReportMode10 import PermeabilityTestReportMode
try:
books = []
book = load_workbook(self.ConcreteUsageRecordPath.text())
print('成功读取水泥使用记录表')
# 生成12份表格
book1, book7 = ConUseBuyRecordMode(book)
print("17")
book2, book3, book4 = ConStrengReportMode(book)
print("234")
book5 = CreateConFacyoryCardMode(book)
print("5")
book6 = CreateSlumpCardMode(book5)
print("6")
book8 = CreteConUseProveMode(book)
print("8")
book9 = ConQualityRecordMode(book)
print("9")
book10 = PermeabilityTestReportMode(book)
print("10")
books.append(book1)
books.append(book2)
books.append(book3)
books.append(book4)
books.append(book5)
books.append(book6)
books.append(book7)
books.append(book8)
books.append(book9)
books.append(book10)
except BaseException:
print("读取失败")
try:
# 文件名处理
fileNames = [
'1、水泥购进、使用一览表', '2、混凝土试件抗压强度检验报告', '3、混凝土试块强度试验结果汇总表',
'4、标准养护混凝土抗压强度计算表', '5、建友商品混凝土出厂合格证', '6、混凝土坍落度验收表',
'7、混凝土配合比设计报告', '8、工地使用预拌混凝土证明书', '9、混凝土搅拌质量记录表',
'10、抗渗性能检测报告', '11、施工配合比'
]
filePath = QFileDialog.getExistingDirectory(
QWidget(),
"选取生成表格的文件夹",
"C:/",
)
print(filePath)
if filePath == '':
print('没有选中文件')
raise Exception('请选择文件夹')
filePaths = [] # 存放文件路劲
for i in fileNames:
filePaths.append(filePath + '/' + i + '空白.xlsx')
for j in range(1, 11):
books[j - 1].save(filePaths[j - 1])
QMessageBox.information(QWidget(), "成功导出", "请在您刚刚选定的文件夹内查看生成的文件")
except BaseException:
QMessageBox.information(QWidget(), "错误", "添加失败！！！！！\n添加失败！！！！")
class QmyMainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.setCentralWidget(self.ui.splitter)
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
def __openTable(self):
self.tabModel = QSqlTableModel(self, self.DB)
self.tabModel.setTable("employee")
self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.tabModel.setSort(self.tabModel.fieldIndex("empNo"), Qt.AscendingOrder)
if(self.tabModel.select()==False):
QMessageBox.critical(self, "错误信息", "打开数据表错误，错误信息\n"+self.tabModel.lastError().text())
return
self.__getFieldNames()
self.tabModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal, "工号")
self.tabModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal, "姓名")
self.tabModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal, "性别")
self.tabModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal, "出生日期")
self.tabModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal, "省份")
self.tabModel.setHeaderData(self.fldNum["Department"], Qt.Horizontal, "部门")
self.tabModel.setHeaderData(self.fldNum["Salary"], Qt.Horizontal, "工资")
self.tabModel.setHeaderData(self.fldNum["Memo"], Qt.Horizontal, "备注")
self.tabModel.setHeaderData(self.fldNum["Photo"], Qt.Horizontal, "照片")
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.tabModel)
self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)
self.mapper.addMapping(self.ui.dbSpinEmpNo, self.fldNum["empNo"])
self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"])
self.mapper.addMapping(self.ui.dbComboSex, self.fldNum["Gender"])
self.mapper.addMapping(self.ui.dbEditBirth, self.fldNum["Birthday"])
self.mapper.addMapping(self.ui.dbComboProvince, self.fldNum["Province"])
self.mapper.addMapping(self.ui.dbComboDep, self.fldNum["Department"])
self.mapper.addMapping(self.ui.dbSpinSalary, self.fldNum["Salary"])
self.mapper.addMapping(self.ui.dbEditMemo, self.fldNum["Memo"])
self.mapper.toFirst()
self.selModel = QItemSelectionModel(self.tabModel)
self.selModel.currentChanged.connect(self.do_currentChanged)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.tabModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.tableView.setColumnHidden(self.fldNum["Memo"], True)
self.ui.tableView.setColumnHidden(self.fldNum["Photo"], True)
strList = ("男", "女")
self.__delegatesex = QmyComboBoxDelegate()
self.__delegatesex.setItems(strList, False)
self.ui.tableView.setItemDelegateForColumn(self.fldNum["Gender"], self.__delegatesex)
strList = ("销售部", "技术部", "生产部", "行政部")
self.__delegateDepart = QmyComboBoxDelegate()
self.__delegateDepart.setItems(strList, True)
self.ui.tableView.setItemDelegateForColumn(self.fldNum["Department"], self.__delegateDepart)
self.ui.actOpenDB.setEnabled(False)
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecAppend.setEnabled(True)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actScan.setEnabled(True)
self.ui.groupBoxSort.setEnabled(True)
self.ui.groupBoxFilter.setEnabled(True)
def __getFieldNames(self):
emptyRec = self.tabModel.record()
self.fldNum = {}
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.ui.comboFields.addItem(fieldName)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName]=i
print(self.fldNum)
def do_currentChanged(self, current, previous):
self.ui.actSubmit.setEnabled(self.tabModel.isDirty())
self.ui.actRevert.setEnabled(self.tabModel.isDirty())
def do_currentRowChanged(self, current, previous):
self.ui.actRecDelete.setEnabled(current.isValid())
self.ui.actPhoto.setEnabled(current.isValid())
self.ui.actPhotoClear.setEnabled(current.isValid())
if(current.isValid() == False):
self.ui.dbLabPhoto.clear()
return
self.mapper.setCurrentIndex(current.row())
curRec = self.tabModel.record(current.row())
if(curRec.isNull("Photo")):
self.ui.dbLabPhoto.clear()
else:
data = curRec.value("Photo")
pic = QPixmap()
pic.loadFromData(data)
w = self.ui.dbLabPhoto.size().width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(w))
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename ,flt = QFileDialog.getOpenFileName(self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename)
if self.DB.open():
self.__openTable()
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot()
def on_actSubmit_triggered(self):
res = self.tabModel.submitAll()
if(res == False):
QMessageBox.information(self, "消息", "数据保存错误，错误信息\n" + self.tabModel.lastError().text())
else:
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot()
def on_actRevert_triggered(self):
self.tabModel.revertAll()
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot()
def on_actRecAppend_triggered(self):
self.tabModel.insertRow(self.tabModel.rowCount(), QModelIndex())
curIndex = self.tabModel.index(self.tabModel.rowCount()-1, 1)
self.selModel.clearSelection()
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
currow = curIndex.row()
self.tabModel.setData(self.tabModel.index(currow, self.fldNum["empNo"]), 2000+self.tabModel.rowCount())
self.tabModel.setData(self.tabModel.index(currow, self.fldNum["Gender"]), "男")
@pyqtSlot()
def on_actRecInsert_triggered(self):
curIndex = self.ui.tableView.currentIndex()
self.tabModel.insertRow(curIndex.row(), QModelIndex())
self.selModel.clearSelection()
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
@pyqtSlot()
def on_actRecDelete_triggered(self):
curIndex = self.selModel.currentIndex()
self.tabModel.removeRow(curIndex.row())
@pyqtSlot()
def on_actPhotoClear_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.tabModel.record(curRecNo)
curRec.setNull("Photo")
self.tabModel.setRecord(curRecNo, curRec)
self.ui.dbLabPhoto.clear()
@pyqtSlot()
def on_actPhoto_triggered(self):
fileName, filt = QFileDialog.getOpenFileName(self, "选择图片文件", "", "照片(*.jpg")
if(fileName==''):
return
file=QFile(fileName)
file.open(QIODevice.ReadOnly)
try:
data = file.readAll()
finally:
file.close()
curRecNo = self.selModel.currentIndex().row()
curRec = self.tabModel.record(curRecNo)
curRec.setValue("Photo", data)
self.tabModel.setRecord(curRecNo, curRec)
pic = QPixmap()
pic.loadFromData(data)
w = self.ui.dbLabPhoto.width()
self.ui.dbLabPhoto.setPixmap(pic.scaledToWidth(w))
@pyqtSlot()
def on_actScan_triggered(self):
if(self.tabModel.rowCount()==0):
return
for i in range(self.tabModel.rowCount()):
aRec = self.tabModel.record(i)
salary = aRec.value("Salary")
salary = salary*1.1
aRec.setValue("Salary", salary)
self.tabModel.setRecord(i, aRec)
if(self.tabModel.submitAll()):
QMessageBox.information(self, "消息", "涨工资计算完毕了")
@pyqtSlot()
def on_comboFields_currentIndexChanged(self, index):
if self.ui.radioBtnAscend.isChecked():
self.tabModel.setSort(index, Qt.AscendingOrder)
else:
self.tabModel.setSort(index, Qt.DescendingOrder)
self.tabModel.select()
@pyqtSlot()
def on_radioBtnAscend_clicked(self):
self.tabModel.setSort(self.ui.comboFields.currentIndex(), Qt.AscendingOrder)
self.tabModel.select()
@pyqtSlot()
def on_radioBtnDescend_clicked(self):
self.tabModel.setSort(self.ui.comboFields.currentIndex(), Qt.DescendingOrder)
self.tabModel.select()
@pyqtSlot()
def on_radioBtnMan_clicked(self):
self.tabModel.setFilter("Gender='男'")
@pyqtSlot()
def on_radioBtnWoman_clicked(self):
self.tabModel.setFilter("Gender='女'")
@pyqtSlot()
def on_radioBtnBoth_clicked(self):
self.tabModel.setFilter("")
class Commodity(QTableView):
def __init__(self):
super().__init__()
self.db = None
self.db_connect()
self.CreateTable()
self.sql_exec()
def db_connect(self):
self.db = QSqlDatabase.addDatabase('QSQLITE') # 1
self.db.setDatabaseName('./tes.db') # 2
if not self.db.open(): # 3
QMessageBox.critical(self, 'Database Connection',
self.db.lastError().text())
def closeEvent(self, QCloseEvent): # 4
self.db.close()
def CreateTable(self):
query = QSqlQuery()
query.exec_(
"create table yingyee(date VARCHAR(20) primary key,name VARCHAR(20),total VARCHAR(20),status VARCHAR(20))"
)
query.exec_(
"CREATE TABLE sh(number VARCHAR(20) PRIMARY KEY, name VARCHAR(20) NOT NULL, unit VARCHAR(20))"
)
s = ""
for i in range(1, 11):
s += "No{0} VARCHAR(20),goods{6} VARCHAR(20),unit{1} VARCHAR(20),guige{2} VARCHAR(20),amount{3} VARCHAR(20),danjia{4} VARCHAR(20),money{7} VARCHAR(20),beizhu{5} VARCHAR(20),".format(
i, i, i, i, i, i, i, i)
print(s)
query.exec_("CREATE TABLE Kucun("
"date VARCHAR(20) primary key,"
" name VARCHAR(20) NOT NULL,"
" bianhao VARCHAR(20),"
"cangku VARCHAR(20),"
"tel VARCHAR(20),"
"address VARCHAR(20),"
"zhidan VARCHAR(20),"
"jinshou VARCHAR(20),"
"shoukuan VARCHAR(20),"
"fahuo VARCHAR(20),"
"kehuqianzi VARCHAR(20)," + s + "total VARCHAR(20))")
"""
query.exec_("insert into Kucun(date,name,bianhao,cangku,tel,address,zhidan,jinshou,shoukuan"
",fahuo,kehuqianzi,No1,goods1,unit1,guige1,amount1,danjia1,money1,beizhu1,total)"
"values ({}".format("\'"+datetime.datetime.now().strftime('%Y/%m/%d %H:%M:%S')+"\'")+",'黄小泽','2017151024','20','15768497448','广东',"
"'小红','小黄','小兰','小明','小倩','2017','牙膏','根','加工','8','12','96','没有','96.0')")
query.exec_("insert into Kucun(date,name,bianhao) values('2018/11/26 5','黄泽文','2017151024')")
"""
query.exec_(
"SELECT date,name,bianhao,cangku,tel,address,zhidan,jinshou,shoukuan"
",fahuo,kehuqianzi,No1,unit1,guige1,amount1,danjia1,beizhu1 FROM Kucun"
) # 4
while query.next():
print(query.value(0), query.value(1), query.value(2))
query.exec_("insert into sh(number,name)" "values ('1023','不锈钢')")
query.exec_("SELECT number, name FROM sh") # 4
while query.next():
stu_name = query.value(0)
stu_class = query.value(1)
print(stu_name, stu_class)
def SetTable(self, table):
self.model.setTable(table)
def sql_exec(self):
self.model = QSqlTableModel()
self.model.setTable('sh')
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
self.model.setHeaderData(0, Qt.Horizontal, "商品编号")
self.model.setHeaderData(1, Qt.Horizontal, "商品名称")
self.model.setHeaderData(2, Qt.Horizontal, "单位")
self.model.select()
self.setModel(self.model)
def InsertData(self, number, name, unit):
if number == "" or name == "" or unit == "":
return
self.model.insertRow(0)
self.model.setData(self.model.index(0, 0), number)
self.model.setData(self.model.index(0, 1), name)
self.model.setData(self.model.index(0, 2), unit)
self.model.submit()
def Find(self, number):
self.model.setFilter('number==' + number)
self.model.select()
if self.model.record(0).value("number") == None:
return False
return True
def getData(self, number):
if self.Find(number):
record = self.model.record(0)
l = [
record.value("number"),
record.value("name"),
record.value("unit")
]
return l
else:
return None
def UpDate(self, number, type, data):
dic = {"number": 0, "name": 1, "unit": 2}
if self.Find(number):
self.model.setData(self.model.index(0, dic[type]), data)
return True
return False
class TablePage(QWidget):
exportSignal = QtCore.pyqtSignal(object)
def __init__(self, db, table_name, condition, crossTable=False):
"""当crossTable为False时，为单张表查看，可读可写，table_name为表名，condition为过滤条件
当crossTable为True时，为同类型多张表查询，只可以读不可以写，table_name为表类型名，condition为过滤条件"""
super(QWidget, self).__init__()
self.exportSignal.connect(self.exportSlot)
self.db = db
if self.db.open(): # 开启数据库连接
logging.info("db is open")
else:
logging.critical("db is not open!!!")
err = self.db.lastError()
logging.critical(err.text())
logging.critical(QtSql.QSqlDatabase.drivers())
logging.critical(QApplication.libraryPaths())
self.__table_name = table_name
self.__crossTable = crossTable
self.__dm = DataManager()
if crossTable:
self.model = QSqlQueryModel(self)
for section, value in enumerate(
['id'] + ExcelCheck.headers[table_name]): # 设置表头为该类型表的表头，包括id
self.model.setHeaderData(section, Qt.Horizontal, value)
if condition == "":
query = " union ".join([
f"select * from {t}"
for t in self.__dm.get_my_tables(table_name)
])
else:
query = " union ".join([
f"select * from {t} where {condition}"
for t in self.__dm.get_my_tables(table_name)
])
print(query)
try:
self.model.setQuery(query, self.db)
except Exception as e:
logging.exception(e)
QMessageBox.warning(self, "查询出错", str(e))
else:
self.model = QSqlTableModel(self, db=self.db)
self.model.setTable(table_name)
self.model.setFilter(condition)
self.model.select()
tableView = QTableView()
tableView.setModel(self.model)
layout = QGridLayout()
layout.addWidget(tableView)
self.setLayout(layout)
def exportToExcel(self, filePath):
# 填写表头list
if self.__crossTable:
header = ["id"] + ExcelCheck.headers[self.__table_name] # 在前面加上id列
else:
header = self.__dm.get_column_names(table_name=self.__table_name)
# 确定s_num集合
s_num = set()
for i, name in enumerate(header):
if name in ["发票号码"]: # 属于s_num的列
s_num.add(i)
# 填写表格内容二维数组
content = [["" for c in range(self.model.columnCount())]
for r in range(self.model.rowCount())]
for i in range(self.model.rowCount()):
for j in range(self.model.columnCount()):
content[i][j] = self.model.index(i, j).data()
print(header)
print(content)
logging.info("正在启动子线程")
# 设置成为守护主线程，主线程退出后子线程直接销毁不再执行子线程的代码
threading.Thread(target=self.writeToExcelWork,
args=(filePath, header, content, s_num),
daemon=True).start()
def writeToExcelWork(self, filePath, header, content, s_num):
try:
logging.info("子线程已启动，正在调用Easyexcel进行导出")
pythoncom.CoInitialize()
excel = Easyexcel(filepath=filePath, visible=False)
logging.info("Easyexcel对象创建成功，正在写入")
excel.set_sheet("Sheet1", header, content, s_num)
logging.info("数据写入完成，正在关闭Easyexcel对象")
excel.close()
logging.info("Easyexcel已正常关闭")
self.exportSignal.emit("文件已导出，路径为" + filePath)
except Exception as e:
logging.exception(e)
self.exportSignal.emit(e)
def exportSlot(self, info):
if isinstance(info, str):
QMessageBox.information(self, "文件已导出", info)
elif isinstance(info, Exception):
QMessageBox.warning(self, "文件导出出错", str(info))
class MainWindow(QMainWindow, Ui_MainWindow):
def __init__(self):
super(MainWindow, self).__init__()
self.folder_path = QDir.current().path()
self.init_ui()
def init_ui(self):
"""Initialize ui."""
self.setupUi(self)
self.move_to_center()
self.init_table_view()
icon = QIcon('subtitle_analyze_gui.ico')
self.setWindowIcon(icon)
movie = QMovie('processing.gif')
self.label.setMovie(movie)
movie.start()
self.label.setVisible(False)
self.tableView.resizeColumnsToContents()
self.tableView.resizeRowsToContents()
self.tableView.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeToContents)
self.actionOpen_Folder.triggered.connect(self.open_folder)
self.actionSave_As.triggered.connect(self.save_result)
self.actionExit.triggered.connect(self.close)
self.refreshPushButton.released.connect(self.refresh)
def init_table_view(self):
"""Initialize the table view"""
if not create_connection():
sys.exit(1)
self.model = QSqlTableModel()
initialize_model(self.model)
self.tableView.setModel(self.model)
self.tableView.setSortingEnabled(True)
reflesh_model(self.model)
def move_to_center(self):
"""Move windows to the center of the screen."""
screen = QDesktopWidget().screenGeometry()
size = self.geometry()
self.move((screen.width() - size.width()) / 2,
(screen.height() - size.height()) / 2)
def open_folder(self):
"""Set target folder."""
self.folder_path = QFileDialog.getExistingDirectory(
self, 'Open the folder', QDir.currentPath())
if not self.folder_path:
self.folder_path = QDir.current().path()
def save_result(self):
"""Save analyze result."""
filename = QFileDialog.getSaveFileName(
self, 'Save analyze result',
os.path.join(QDir.current().path(), 'result.csv'), 'File (*.csv)')
with open(filename[0], 'w', encoding='utf-8') as file:
row = self.model.rowCount()
col = self.model.columnCount()
for i in range(row):
text = ''
for j in range(col):
text += str(self.model.data(self.model.index(i, j))) + '\t'
text += '\n'
file.writelines(text)
def analyze_subtitle(self):
"""Analyze subtitles"""
filename_list = get_subtitle_filename_list(self.folder_path, mode='r')
result_dict = single_thread_analyze(filename_list)
for key in result_dict.keys():
add_record(result_dict[key][0], result_dict[key][1],
result_dict[key][2] / 1000, result_dict[key][3])
def refresh(self):
"""Execute analysis task."""
self.label.setVisible(True)
self.refreshPushButton.setEnabled(False)
reflesh_model(self.model)
self.task_thread = TaskThread()
self.task_thread.set_task(self.analyze_subtitle)
self.task_thread.finish_signal.connect(self.process_thread_message)
self.task_thread.start()
def process_thread_message(self, message):
"""Processing thread message."""
reflesh_model(self.model)
self.label.setVisible(False)
self.refreshPushButton.setEnabled(True)
class MainMenu(QMainWindow):
def __init__(self, parent=None):
super(MainMenu, self).__init__(parent)
# ----------------- UI --------------------------#
loadUi("./UI/main_ui.ui", self)
self.setWindowTitle("密码保险柜")
self.setWindowIcon(QIcon("./image/1.ico"))
# ----------------- 按键 --------------------------#
self.pushButtonExit.clicked.connect(QCoreApplication.instance().quit)
self.pushButtonDel.clicked.connect(self.delete)
self.pushButtonAdd.clicked.connect(self.add)
self.refresh.clicked.connect(self.reselect)
# ----------------- db --------------------------#
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("./db/word.db")
# ----------------- model --------------------------#
self.model = QSqlTableModel()
self.model.setTable("warehouse")
self.model.setSort(ID, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, "ID")
self.model.setHeaderData(SITE, Qt.Horizontal, "站点")
self.model.setHeaderData(NAME, Qt.Horizontal, "账号")
self.model.setHeaderData(PASS, Qt.Horizontal, "密码")
self.model.setHeaderData(REMARK, Qt.Horizontal, "备注")
self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
# ----------------- tableView --------------------------#
self.tableView.setModel(self.model)
self.tableView.horizontalHeader().setStretchLastSection(True)
# ----------------- query --------------------------#
self.query = QSqlQuery()
def contextMenuEvent(self, event):
cmenu = QMenu()
quitAct = cmenu.addAction("退出")
action = cmenu.exec_(self.mapToGlobal(event.pos()))
if action == quitAct:
qApp.quit()
def add(self):
self.query.exec(
"insert into `warehouse` VALUES (NULL ,'example.com','example','example','example')"
)
self.reselect()
rows = self.model.rowCount()
self.tableView.selectRow(rows)
def delete(self):
row = self.tableView.currentIndex().row()
site_id = self.model.data(self.model.index(row, 0))
sql = "delete from `warehouse` where id =" + str(site_id)
self.query.exec(sql)
self.reselect()
def reselect(self):
if self.model.data(self.model.index(self.model.rowCount(), 0)) != "":
self.model.select()
class ClerkDialog(QDialog, Ui_Dialog):
def __init__(self, table, mainModel, parent=None):
super(ClerkDialog, self).__init__(parent)
self.setupUi(self)
self.table = table
self.mainModel = mainModel
# 关联待写
self.model = QSqlTableModel(self)
self.model.setTable(table)
self.model.setSort(ID, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, "Order")
self.model.setHeaderData(NAME, Qt.Horizontal, "Name")
self.model.setHeaderData(GENDER, Qt.Horizontal, "Gender")
self.model.setHeaderData(DEPARTMENT, Qt.Horizontal, "Department")
self.model.select()
self.tableView.setModel(self.model)
self.tableView.setSelectionMode(QTableView.SingleSelection)
self.tableView.setSelectionBehavior(QTableView.SelectRows)
self.tableView.setColumnHidden(ID, True)
self.tableView.setColumnHidden(GENDER, True)
self.tableView.setEditTriggers(
QAbstractItemView.NoEditTriggers) # 不允许编辑
# self.tableView.verticalHeader(False)
self.tableView.resizeColumnsToContents()
items = [] # 获取所有部门名称
query = QSqlQuery()
query.exec("SELECT dpt_name FROM departments{0}".format(createmoth))
while query.next(): # 找到第一条记录，每次后移一条记录
items.append(query.value("dpt_name"))
self.clerk_dpt_comboBox.addItems(items)
@pyqtSlot()
def on_clk_del_pb_clicked(self):
"""删除职员"""
index = self.tableView.currentIndex()
if not index.isValid():
return
self.model.removeRow(index.row())
self.model.submitAll()
self.model.select()
self.mainModel.select() # 同步更新
@pyqtSlot()
def on_clk_add_pb_clicked(self):
"""增加职员"""
id = self.clk_seq_le.text() # 获取id
name = self.clk_name_le.text() # 获取姓名
dpt = self.clerk_dpt_comboBox.currentText() # 获取部门
query = QSqlQuery() # 将名称转换成部门id
query.exec(
"SELECT dpt_id FROM departments{0} WHERE dpt_name='{1}'".format(
createmoth, dpt))
query.next()
dpt_id = query.value(0)
row = self.model.rowCount() # 在最后一行加上去
# row = self.tableView.currentIndex().row() # 在所选取的行插入，在这里不可行，因为已经有排序策略
self.model.database().transaction()
self.model.insertRow(row)
self.model.setData(self.model.index(row, ID), id)
self.model.setData(self.model.index(row, NAME), name)
self.model.setData(self.model.index(row, DEPARTMENT), dpt_id)
self.model.submitAll()
self.model.select()
self.model.database().commit()
self.mainModel.select()
@pyqtSlot()
def on_clk_modify_pb_clicked(self):
"""修改职员"""
index = self.tableView.currentIndex()
if not index.isValid():
return
self.clk_id = self.model.record(index.row()).value(ID)
clk_name = self.model.record(index.row()).value(NAME)
clk_dpt = self.model.record(index.row()).value(DEPARTMENT)
from change_slot import ChangeDialog
dialog = ChangeDialog(self.clk_id, clk_name, clk_dpt, self)
dialog.okClickedSignal.connect(self.ok_slot)
if dialog.exec():
print("Done")
def ok_slot(self, cid, name, dpt):
QSqlDatabase.database().transaction()
query = QSqlQuery()
query.exec(
"UPDATE clerks{0} SET clk_id='{1}',clk_name='{2}',dpt_id='{3}'"
"WHERE clk_id='{4}'".format(createmoth, cid, name, dpt,
self.clk_id))
if 'Yes':
QSqlDatabase.database().commit()
self.model.select()
self.mainModel.select()
else:
QSqlDatabase.database().rollback()
@pyqtSlot()
def on_clk_up_pb_clicked(self):
"""职员上移"""
# 先避开
pass
@pyqtSlot()
def on_clk_down_pb_clicked(self):
"""职员下移"""
# 先避开
pass
@pyqtSlot()
def on_clk_save_pb_clicked(self):
"""保存"""
# 先避开
pass
@pyqtSlot()
def on_clk_quit_pb_clicked(self):
QDialog.done(self, 1)
class WinsWidgetView(QWidget, Ui_Wins_Widget):
def __init__(self,parent=None):
logger.debug("_init__:begin")
super(WinsWidgetView, self).__init__(parent)
self.setupUi(self)
self.dbPath="test.db"
self.curTable="test2"
self.setObjectName('winsWidget')
self.setWindowFlags(Qt.FramelessWindowHint)
self.setWindowTitle('wins的小工具')
self.setWindowIcon(QIcon('icons/titleIcon.png'))
self.tableModel=QSqlTableModel(self,QSqlDatabase.addDatabase('QSQLITE'))
self.initThread()
self.set_buttons()
self.set_labels()
self.set_lines()
self.load_tableview()
self.init_bottom()
logger.debug("_init__:end")
def init_bottom(self):
self.progressBar.hide()
self.progresslable.hide()
self.statusBar= QStatusBar(self);
self.rightBottom.addWidget(self.statusBar)
#self.statusBar.setGeometry(30,40,200,25);
def initThread(self):
logger.debug("_init__:thread")
self.thread=QThread()
self.tsWork=TushareWorkObject()
self.tsWork.moveToThread(self.thread) ##另起子线程，防止ui主线程卡死
self.thread.start()
#槽函数不要加()，否则会报参数异常
self.tsWork.procegressBarSignal.connect(self.setProcegressBar)
logger.debug("_init end__:thread")
def setProcegressBar(self,i):
print("setProcegressBar:")
print(i)
self.progressBar.show()
self.progressBar.setRange(0, i)
pass
"""重写鼠标事件，实现窗口拖动。"""
def mousePressEvent(self, event):
#logger.debug("mousePressEvent:")
if event.buttons() == Qt.LeftButton:
self.m_drag = True
self.m_DragPosition = event.globalPos()-self.pos()
event.accept()
def mouseMoveEvent(self, event):
#logger.debug("mouseMoveEvent:")
try:
if event.buttons() and Qt.LeftButton:
self.move(event.globalPos()-self.m_DragPosition)
event.accept()
except AttributeError:
pass
def mouseReleaseEvent(self, event):
#logger.debug("mouseReleaseEvent:")
self.m_drag = False
# 设置布局。
def set_layouts(self):
logger.debug("set_layouts:begin")
self.page1group.setAlignment(Qt.AlignCenter)
self.page1layout.setAlignment(Qt.AlignCenter)
logger.debug("set_layouts:end")
# 设置按钮
def set_buttons(self):
logger.debug("set_buttons:begin")
self.closeBtn.setText('×')
self.closeBtn.clicked.connect(self.close)
self.closeBtn.setToolTip('退出')
# 最小化。
self.minBtn.setText('-')
self.minBtn.clicked.connect(self.showMinimized)
self.minBtn.setToolTip('最小化')
# 最大化。
self.maxBtn.setText('□')
self.maxBtn.setToolTip('^_^此功能已上火星')
# 登陆。
self.loginBtn.setText('')
self.loginBtn.setToolTip('登陆')
# 搜索输入框。
self.searchInput.resize(48, 48)
# 搜索按钮
self.searchBtn.setText('')
self.searchBtn.setToolTip('点击搜索')
# 查询。
self.qryBtn.setIcon(QIcon('icons/qryBtn.png'))
self.qryBtn.setText("查询")
self.qryBtn.clicked.connect(self.qryFunc)
self.qryBtn.setAutoRaise(True)
self.qryBtn.setToolButtonStyle(Qt.ToolButtonTextBesideIcon)
# 增加。
self.addBtn.setIcon(QIcon('icons/addBtn.png'))
self.addBtn.setText("增加")
self.addBtn.clicked.connect(self.addFunc)
self.addBtn.setAutoRaise(True)
self.addBtn.setToolButtonStyle(Qt.ToolButtonTextBesideIcon)
# 删除。
self.delBtn.setIcon(QIcon('icons/delBtn.png'))
self.delBtn.setText("删除")
self.delBtn.clicked.connect(self.delFunc)
self.delBtn.setAutoRaise(True)
self.delBtn.setToolButtonStyle(Qt.ToolButtonTextBesideIcon)
logger.debug("set_buttons:end")
self.toolBox.setCurrentIndex(1)
self.toolBox.setItemIcon(0,QIcon("icons/homeIcon.png"))
self.toolBox.setItemIcon(1,QIcon("icons/homeIcon.png"))
self.page1btn1.setIcon(QIcon("icons/homeIcon.png"))
self.page1btn1.setText(self.tr("首页"))
self.page1btn1.setIconSize(QSize(40,40))
self.page1btn1.setAutoRaise(True)
self.page1btn1.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
self.page1btn2.setIcon(QIcon("icons/graphIcon.png"))
self.page1btn2.setText(self.tr("图表"))
self.page1btn2.setIconSize(QSize(40,40))
self.page1btn2.setAutoRaise(True)
self.page1btn2.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
self.page1btn3.setIcon(QIcon("icons/taskIcon.png"))
self.page1btn3.setText(self.tr("任务"))
self.page1btn3.setIconSize(QSize(40,40))
self.page1btn3.setAutoRaise(True)
self.page1btn3.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
self.page1btn4.setIcon(QIcon("icons/clockIcon.png"))
self.page1btn4.setText(self.tr("提醒"))
self.page1btn4.setIconSize(QSize(40,40))
self.page1btn4.setAutoRaise(True)
self.page1btn4.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
self.page1btn5.setIcon(QIcon("icons/favIcon.png"))
self.page1btn5.setText(self.tr("收藏"))
self.page1btn5.setIconSize(QSize(40,40))
self.page1btn5.setAutoRaise(True)
self.page1btn5.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
self.page1btn6.setIcon(QIcon("icons/kxianIcon.png"))
self.page1btn6.setText(self.tr("k线"))
self.page1btn6.setIconSize(QSize(40,40))
self.page1btn6.setAutoRaise(True)
self.page1btn6.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
self.page2btn1.setIcon(QIcon("icons/marketIcon.png"))
self.page2btn1.setText(self.tr("行情"))
self.page2btn1.setIconSize(QSize(40,40))
self.page2btn1.setAutoRaise(True)
self.page2btn1.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
logging.debug("self.page2btn1.clicked.connect(self.tsWork.findLimitupStocks)")
self.page2btn1.clicked.connect(self.tsWork.findLimitupStocks)
self.page2btn2.setIcon(QIcon("icons/stockIcon.png"))
self.page2btn2.setText(self.tr("通联"))
self.page2btn2.setIconSize(QSize(40,40))
self.page2btn2.setAutoRaise(True)
self.page2btn2.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
self.page2btn3.setIcon(QIcon("icons/contactIcon.png"))
self.page2btn3.setText(self.tr("货币供应"))
self.page2btn3.setIconSize(QSize(40,40))
self.page2btn3.setAutoRaise(True)
self.page2btn3.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
logging.debug("self.page2btn1.clicked.connect(self.tsWork.findLimitupStocks)")
self.page2btn3.clicked.connect(self.tsWork.getMoneySupply)
self.page2btn4.setIcon(QIcon("icons/calIcon.png"))
self.page2btn4.setText(self.tr("绩效"))
self.page2btn4.setIconSize(QSize(40,40))
self.page2btn4.setAutoRaise(True)
self.page2btn4.setToolButtonStyle(Qt.ToolButtonTextUnderIcon)
def getLimitupStock_work(self):
logging.debug("winview:begin getLimitupStock_work")
tsWork=TushareWorkObject()
tsWork.moveToThread(self.thread) ##另起子线程，防止ui主线程卡死
self.limupStockThread.setRun(6)
logging.debug("winview:begin beginRun")
self.limupStockThread.outSignal.connect(self.limupStockThread_outfunc)
def limupStockThread_outfunc(self,text):
QMessageBox.about( self, "涨停板股票收集",text)
print("limupStockThread_outfunc"+text)
def set_labels(self):
logger.debug("set_labels:begin")
titlePix = QPixmap()
titlePix.load('icons/titleIcon.png')
self.headIcon.setPixmap(titlePix.scaled(40, 40))
self.headTitle.setText("Wins百宝箱")
self.topSpace.setText("")
logger.debug("set_labels:end")
def set_lines(self):
logger.debug("set_lines:begin")
self.searchInput.setPlaceholderText('搜索')
logger.debug("set_lines:end")
def load_tableview(self):
logger.debug("load_tableview:begin")
###self.model数据初始化
self.tableModel.database().setDatabaseName(self.dbPath)
self.tableModel.database().open()
self.tableModel.setTable(self.curTable)
self.tableModel.select()
self.tableModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
# 数据更新的策略，详细可以查看Qt文档
self.tableView.setModel(self.tableModel)
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tableView.setSelectionMode(QAbstractItemView.ExtendedSelection)
logger.debug("load_tableview:end")
#查询按钮关联的槽函数
def qryFunc(self):
#self.db.execSQL("select * from t1")
QMessageBox.about( self, 'qryFuncCall', "qryFunc")
#插入按钮关联的槽函数
def addFunc(self):
f1=random.randint(1, 99)
self.tableModel.insertRows(0, 1)
self.tableModel.setData(self.tableModel.index(0, 0), f1)
self.tableModel.setData(self.tableModel.index(0, 1), "test")
self.tableModel.submitAll()
QMessageBox.about( self, 'addFuncCall', "addFunc")
def delFunc(self):
rs=list(map(lambda x:x.row(),self.tableView.selectedIndexes()))
if len(rs)==0:
QMessageBox.information(self,'提醒','请先选中至少一行，再点击此按钮！')
return
for i in reversed(rs):
self.tableModel.removeRows(i,1)
self.tableModel.submitAll()
class Main(QMainWindow):
def __init__(self):
super(Main, self).__init__() # 初始化,super集成父类的属性
self.ui = PreviewApp.Ui_MainWindow() # 实例化
self.ui.setupUi(self)
self.setWindowIcon(QIcon('db/logo.jpg'))
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('db/App.db')
self.db.open()
self.tableView = self.ui.tableView # 获取TableView对象
self.tableView1 = self.ui.tableView1
self.model = QSqlTableModel(self) # 设置数据库模型
self.model.setTable("Preview") # 选择db的其中一个表
self.tableView.setModel(self.model) # 设置TableView的模型
self.tableView.setEditTriggers(QTableView.NoEditTriggers) # 设置表1内容不可编辑
self.model.select()
# model1为页面2对象
self.model1 = QSqlTableModel(self)
self.model1.setTable("Container")
self.tableView1.setModel(self.model1)
self.model1.select()
self.ui.btn1.clicked.connect(self.addRow)
self.ui.btn2.clicked.connect(self.delRow)
self.ui.btn3.clicked.connect(self.CPU)
self.ui.btn4.clicked.connect(self.Memory)
self.ui.btn5.clicked.connect(self.CPU_temp)
self.ip_list = []
self.tcpserver = TcpServer(self)
self.tcpserver.listen(QHostAddress("0,0,0,0,"), Port) # 绑定监听端口
self.tcpserver.signRecv.connect(self.Recv)
# self.tcpserver.signGetAddress.connect(self.updateCombox)
self.updateCombox()
def updateCombox(self):
if self.db.open():
print("open") # 打开了数据库
query1 = QSqlQuery()
query1.exec_("SELECT adress FROM IP") #打开查询数据库表
for i in range(query1.size()):
print(query1.value(0))
self.ui.comboBox1.addItem(query1.value(0)) # 添加下拉列表框事件
query1.next()
def Recv(self, msg):
reply = QMessageBox.information(self, "标题", msg,
QMessageBox.Yes | QMessageBox.No)
def Send(self, msg): # 调用接口函数,向客户端发送消息
print('发送信号发出')
for id in self.tcpserver.socketList:
self.tcpserver.signSend.emit(msg, id)
def addRow(self): # 添加一行
row = self.model1.rowCount()
self.model1.insertRow(row)
index = self.model1.index(row, CONTAINER)
self.tableView1.setCurrentIndex(index)
self.tableView1.edit(index)
def delRow(self):
index = self.tableView1.currentIndex()
if not index.isValid():
return
record = self.model1.record(index.row())
container = record.value(CONTAINER)
state = record.value(STATE)
if (QMessageBox.question(
self,
"Container Data", # 删容器时弹框
("Delete this record?".format(state, container)),
QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
self.model1.removeRow(index.row())
self.model1.submitAll()
self.model1.select()
def CPU(self): # CPU占有率曲线
self.ui.label2.setPixmap(QPixmap("db/CPU_percent.png"))
def Memory(self): # 内存曲线
self.ui.label2.setPixmap(QPixmap("db/memory.jpg"))
def CPU_temp(self): # CPU温度
self.ui.label2.setPixmap(QPixmap("db/CPU.jpg"))
class ReferenceDataDlg(QDialog):
def __init__(self, parent=None):
super(ReferenceDataDlg, self).__init__(parent)
self.model = QSqlTableModel(self)
self.model.setTable("reference")
self.model.setSort(ID, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, "ID")
self.model.setHeaderData(NAME, Qt.Horizontal, "NAME")
self.model.setHeaderData(Description, Qt.Horizontal, "Description")
self.model.setHeaderData(AGE, Qt.Horizontal, "AGE")
self.model.select()
self.view = QTableView()
self.view.setModel(self.model)
self.view.setSelectionMode(QTableView.SingleSelection)
self.view.setSelectionBehavior(QTableView.SelectRows)
self.view.setColumnHidden(ID, True)
self.view.resizeColumnsToContents()
buttonBox = QDialogButtonBox()
addButton = buttonBox.addButton("&Add", QDialogButtonBox.ActionRole)
deleteButton = buttonBox.addButton("&Delete",
QDialogButtonBox.ActionRole)
sortButton = buttonBox.addButton("&Sort", QDialogButtonBox.ActionRole)
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
sortButton.setFocusPolicy(Qt.NoFocus)
menu = QMenu(self)
sortByNAMEAction = menu.addAction("Sort by &NAME")
sortByDescriptionAction = menu.addAction("Sort by &Description")
sortByIDAction = menu.addAction("Sort by &ID")
sortButton.setMenu(menu)
closeButton = buttonBox.addButton(QDialogButtonBox.Close)
layout = QVBoxLayout()
layout.addWidget(self.view)
layout.addWidget(buttonBox)
self.setLayout(layout)
addButton.clicked.connect(self.addRecord)
deleteButton.clicked.connect(self.deleteRecord)
sortByNAMEAction.triggered.connect(lambda: self.sort(NAME))
sortByDescriptionAction.triggered.connect(
lambda: self.sort(Description))
sortByIDAction.triggered.connect(lambda: self.sort(ID))
closeButton.clicked.connect(self.accept)
self.setWindowTitle("数据库小程序")
def addRecord(self):
row = self.model.rowCount()
self.model.insertRow(row)
index = self.model.index(row, NAME)
self.view.setCurrentIndex(index)
self.view.edit(index)
def deleteRecord(self):
index = self.view.currentIndex()
if not index.isValid():
return
record = self.model.record(index.row())
NAME = record.value(NAME)
desc = record.value(Description)
if (QMessageBox.question(
self, "数据库小程序",
("Delete {0} from NAME {1}?".format(desc, NAME)),
QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
self.model.removeRow(index.row())
self.model.submitAll()
self.model.select()
def sort(self, column):
self.model.setSort(column, Qt.AscendingOrder)
self.model.select()
class TableToTreeModel2 (QAbstractItemModel):
"""
Более новый вариант - используем композицию, то есть абстрактная модель
включает в себя sql табличную модель только для работы с базой данных
"""
def __init__(self, m, connection):
QAbstractItemModel.__init__(self, m)
# попробуем сделать композицию
self.dbmodel = QSqlTableModel(self, connection)
self.dbmodel.setEditStrategy(0) # при каждом изменении поля
#print (self.dbmodel.hasIndex(1,12))
self.headers=['id',
'_buy',
'deadline',
'made',
'significance',
'urgency',
'_children',
'_next',
'_parents',
'_prev',
'season',
'short text',
'tags',
'text']
self.rootItem = TreeItem (self.headers)
def setTable(self, tname):
self.dbmodel.setTable(tname)
def select(self):
self.dbmodel.select()
#здесь должны грузиться данные
dct = dict () #словарь айди - список строк с данными
for ind in range (self.dbmodel.rowCount()):
#dct[int(self.dbmodel.data(self.dbmodel.index(ind,0)))] = [self.dbmodel.data(self.dbmodel.index(ind,j)) for j in range(self.dbmodel.columnCount())]
dct[int(self.dbmodel.data(self.dbmodel.index(ind,0)))] = [self.dbmodel.data(self.dbmodel.index(ind,j)) for j in range(self.dbmodel.columnCount())]
def find_children_and_append (item:TreeItem, dct):
chlist = eval(item.data(6))
for ch in chlist:
tri = TreeItem(dct[ch], item)
if tri.data(6) != '[]':
find_children_and_append(tri,dct)
item.appendChild(tri)
for i in [j for j in dct.values() if j[8]=='[]']:
tri = TreeItem(i, self.rootItem)
find_children_and_append(tri,dct)
self.rootItem.appendChild(tri)
def columnCount(self, QModelIndex_parent=None, *args, **kwargs):
return self.dbmodel.columnCount()
def data(self, index, role):
if not index.isValid():
return None
if role != Qt.DisplayRole:
return None
item = index.internalPointer()
return item.data(index.column())
def flags(self, index):
if not index.isValid():
return Qt.NoItemFlags
return Qt.ItemIsEnabled | Qt.ItemIsSelectable |Qt.ItemIsEditable
def headerData(self, section, orientation, role):
if orientation == Qt.Horizontal and role == Qt.DisplayRole:
return self.headers[section]
#return self.dbmodel.headerData(section,orientation,role)
def index(self, row, column, parent):
if not self.hasIndex(row, column, parent):
return QModelIndex()
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
childItem = parentItem.child(row)
if childItem:
return self.createIndex(row, column, childItem)
else:
return QModelIndex()
def rowCount(self, parent):
if parent.column() > 0:
return 0
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
return parentItem.childCount()
def parent(self, index):
if not index.isValid():
return QModelIndex()
childItem = index.internalPointer()
parentItem = childItem.parent()
if parentItem == self.rootItem:
return QModelIndex()
return self.createIndex(parentItem.row(), 0, parentItem)
def getIndexById(self, _id):
for i in range (self.dbmodel.rowCount()):
if int (self.dbmodel.data(self.dbmodel.index(i,0)))==_id:
return self.dbmodel.index(i,0)
return QModelIndex()
def setData(self, modelIndex, value, int_role=Qt.EditRole):
"""
Функция вызывается при установке данных
:param QModelIndex:
:param QVariant:
:param int_role:
:return:
"""
print (self.dbmodel.record(0).setValue(12, 'sdfsfsdf'))
r = self.dbmodel.record(1)
r.setValue(12, 'Krevedko')
print (self.dbmodel.setRecord(0,r))
print (r)
return 1
class MyMainWindowView(QMainWindow, Ui_myMainWindow):
# 增加自定义信号
_selfSignal = QtCore.pyqtSignal(str)
def __init__(self):
super(MyMainWindowView, self).__init__()
self.setupUi(self)
self._selfSignal.connect(self.allBtnShow)
self.actionAbout.triggered.connect(self.aboutFun)
self.actionExit.triggered.connect(QApplication.instance().quit)
self.actionChange_title.triggered.connect(self.changeTitle)
self.qryBtn.clicked.connect(self.qryFunc)
self.addBtn.clicked.connect(self.addFunc)
self.delBtn.clicked.connect(self.delFunc)
self.del2Btn.clicked.connect(self.del2Func)
self.db=DBConn()
#model 与 view绑定
self.myTableModel=QSqlTableModel(self)
self.myTableModel.setTable("t1")
self.myTableModel.select()
self.myTableModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
# 数据更新的策略，详细可以查看Qt文档
self.myTableView.setModel(self.myTableModel)
self.myTableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.myTableView.setSelectionMode(QAbstractItemView.ExtendedSelection)
# 增加自定义槽函数
def allBtnHide(self):
print("test2")
self.pushButton.hide()
self.pushButton_2.hide()
time.sleep(1)
self._selfSignal.emit("我是自定义信号")
# self.pushButton_3.hide()
#增加自定义槽函数
def allBtnShow(self, signalstr):
self.pushButton.show()
self.pushButton_2.show()
self.pushButton_3.setText(signalstr)
# 增加自定义槽函数
def aboutFun(self):
QMessageBox.about( self, 'PyQt', "About")
def changeTitle(self):
self.setWindowTitle("xxx Window")
#查询按钮关联的槽函数
def qryFunc(self):
#self.db.execSQL("select * from t1")
QMessageBox.about( self, 'qryFuncCall', "qryFunc")
#插入按钮关联的槽函数
def addFunc(self):
# f1=random.randint(1, 9999)
# sql = "insert into t1(f1,f2) values (%s,%s)"%(f1,f1)
# self.db.execSQL(sql)
f1=random.randint(1, 99)
self.myTableModel.insertRows(0, 1)
self.myTableModel.setData(self.myTableModel.index(0, 0), f1)
self.myTableModel.setData(self.myTableModel.index(0, 1), "test")
self.myTableModel.submitAll()
QMessageBox.about( self, 'addFuncCall', "addFunc")
# #删除按钮关联的槽函数
def del2Func(self):
if self.myTableModel.select() :
if self.myTableModel.rowCount() != 0:
for i in self.myTableView.selectedIndexes():
self.myTableModel.removeRows(i.row(), 1)
self.myTableModel.submitAll()
# #删除按钮关联的槽函数
# def delFunc(self):
# if self.myTableModel.select() :
# if self.myTableModel.rowCount() != 0:
# index = self.myTableView.currentIndex()
# self.myTableModel.removeRows(0,1)
# self.myTableModel.submitAll()
# QMessageBox.about( self, 'delFuncCall', "delFunc")
def delFunc(self):
rs=list(map(lambda x:x.row(),self.myTableView.selectedIndexes()))
if len(rs)==0:
QMessageBox.information(self,'提醒','请先选中至少一行，再点击此按钮！')
return
for i in reversed(rs):
self.myTableModel.removeRows(i,1)
self.myTableModel.submitAll()
class DatabaseTableView(QTableView):
"""customized table view"""
TABLE = ''
def __init__(self, db, parent=None):
super().__init__(parent)
self.db = db
self._init_model()
self._init_context_menu()
def _init_model(self):
self.model = QSqlTableModel(db=self.db)
self.model.setTable(self.TABLE)
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.setModel(self.model)
self.model.select()
def _init_context_menu(self):
self.setContextMenuPolicy(Qt.CustomContextMenu)
self._context_menu = QMenu(self)
self.customContextMenuRequested.connect(self._on_context_menu)
self._add_row_action = QAction("添加行", self)
self._del_row_action = QAction("删除行", self)
self._submit_action = QAction("提交修改", self)
self._modify_filter_action = QAction("筛选", self)
self._update_action = QAction("刷新", self)
self._show_detail_action = QAction("详情", self)
self._context_menu.addAction(self._add_row_action)
self._context_menu.addAction(self._del_row_action)
self._context_menu.addAction(self._submit_action)
self._context_menu.addAction(self._modify_filter_action)
self._context_menu.addAction(self._update_action)
self._context_menu.addAction(self._show_detail_action)
def _on_context_menu(self, pos):
action = self._context_menu.exec_(self.viewport().mapToGlobal(pos))
if action == self._add_row_action:
self.model.insertRows(self.model.rowCount(), 1)
elif action == self._del_row_action:
self.model.removeRow(self.currentIndex().row())
elif action == self._submit_action:
res = self.model.submitAll()
if not res:
QMessageBox.warning(self, "Sql Error",
self.model.lastError().text())
elif action == self._modify_filter_action:
self._modify_filter()
elif action == self._update_action:
self._update_model()
elif action == self._show_detail_action:
self._show_detail()
def _modify_filter(self):
filter_cmd = self.model.filter()
new_filter, succ = QInputDialog.getText(self,
"过滤",
"过滤表达式",
text=filter_cmd)
if succ:
self.model.setFilter(new_filter)
self.model.select()
def _show_detail(self):
row = self.currentIndex().row()
idn = self.model.index(row, 0).data()
if idn is None:
return
self._get_detail(idn).show()
def _get_detail(self, idn):
raise NotImplementedError()
def _update_model(self):
self.model.select()
class ReferenceDataDlg(QDialog):
def __init__(self, table, title, parent=None):
super(ReferenceDataDlg, self).__init__(parent)
self.model = QSqlTableModel(self)
self.model.setTable(table)
self.model.setSort(NAME, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, "ID")
self.model.setHeaderData(NAME, Qt.Horizontal, "Name")
self.model.setHeaderData(DESCRIPTION, Qt.Horizontal, "Description")
self.model.select()
self.view = QTableView()
self.view.setModel(self.model)
self.view.setSelectionMode(QTableView.SingleSelection)
self.view.setSelectionBehavior(QTableView.SelectRows)
self.view.setColumnHidden(ID, True)
self.view.resizeColumnsToContents()
addButton = QPushButton("&Add")
deleteButton = QPushButton("&Delete")
okButton = QPushButton("&OK")
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
buttonLayout = QHBoxLayout()
buttonLayout.addWidget(addButton)
buttonLayout.addWidget(deleteButton)
buttonLayout.addStretch()
buttonLayout.addWidget(okButton)
layout = QVBoxLayout()
layout.addWidget(self.view)
layout.addLayout(buttonLayout)
self.setLayout(layout)
addButton.clicked.connect(self.addRecord)
deleteButton.clicked.connect(self.deleteRecord)
okButton.clicked.connect(self.accept)
self.setWindowTitle(
"Asset Manager - Edit {0} Reference Data".format(title))
def addRecord(self):
row = self.model.rowCount()
self.model.insertRow(row)
index = self.model.index(row, NAME)
self.view.setCurrentIndex(index)
self.view.edit(index)
def deleteRecord(self):
index = self.view.currentIndex()
if not index.isValid():
return
#QSqlDatabase.database().transaction()
record = self.model.record(index.row())
id = record.value(ID)
table = self.model.tableName()
query = QSqlQuery()
if table == "actions":
query.exec_("SELECT COUNT(*) FROM logs "
"WHERE actionid = {0}".format(id))
elif table == "categories":
query.exec_("SELECT COUNT(*) FROM assets "
"WHERE categoryid = {0}".format(id))
count = 0
if query.next():
count = query.value(0)
if count:
QMessageBox.information(
self, "Delete {0}".format(table),
("Cannot delete {0}<br>"
"from the {1} table because it is used by "
"{2} records").format(record.value(NAME), table, count))
#QSqlDatabase.database().rollback()
return
self.model.removeRow(index.row())
self.model.submitAll()
self.model.select()
class WarehouseController(QObject):
def __init__(self, list, table, addButton, editButton, deleteButton, dbase):
super().__init__()
self.list = list
self.table = table
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.shopModel = QSqlTableModel(db = dbase)
self.shopModel.setTable("shop")
self.shopModel.select()
self.list.setModel(ComplexListModel(self.shopModel, "{name}"))
self.list.selectionModel().currentChanged.connect(self.listSelectionChanged)
self._checkPrivileges()
if self.only_select:
self.addButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def _checkPrivileges(self):
query = QSqlQuery("SHOW GRANTS")
only_select = None
table_pattern = "`{}`".format("shop_detail").lower()
while query.next():
s = query.value(0).lower()
if table_pattern in s:
if "select" in s and only_select is None:
only_select = True
else:
only_select = False
self.only_select = bool(only_select)
def listSelectionChanged(self, cur, prev):
if not cur.isValid():
return self.table.setModel(None)
else:
self.setShopIndex(cur.row())
def setShopIndex(self, row):
record = self.shopModel.record(row)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \
FROM shop_detail INNER JOIN detail \
ON shop_detail.detail_id = detail.id \
WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество")
self.table.setModel(self.detailModel)
self.table.hideColumn(0)
self.table.resizeColumnsToContents()
self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
def tableSelectionChanged(self, cur, prev):
if self.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \
WHERE shop_id={}))".format(shop.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар на склад: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
1, 1)
if not ok: return
detail_id = details[choice]
shop_id = shop.value("id")
query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(shop_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
self.table.selectionModel().clearSelection()
def editButtonClicked(self):
detail = self.detailModel.record(self.table.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
shop = self.shopModel.record(self.list.currentIndex().row())
if qnt > 0:
query = QSqlQuery("UPDATE shop_detail SET quantity={} \
WHERE shop_id={} AND detail_id={}".format(qnt,
shop.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def update(self):
cur = self.list.currentIndex()
if cur.isValid():
row = cur.row()
else:
row = 0
self.shopModel.select()
self.list.reset()
self.selectRow(row)
def selectRow(self, row):
self.list.selectionModel().clearSelection()
self.list.selectionModel().setCurrentIndex(
self.shopModel.index(row, 0), QItemSelectionModel.Select)
class TableToTreeModel2(QAbstractItemModel):
"""
Более новый вариант - используем композицию, то есть абстрактная модель
включает в себя sql табличную модель только для работы с базой данных
"""
def __init__(self, m, connection):
QAbstractItemModel.__init__(self, m)
# попробуем сделать композицию
self.dbmodel = QSqlTableModel(self, connection)
self.dbmodel.setEditStrategy(0) # при каждом изменении поля
#print (self.dbmodel.hasIndex(1,12))
self.headers = [
'id', '_buy', 'deadline', 'made', 'significance', 'urgency',
'_children', '_next', '_parents', '_prev', 'season', 'short text',
'tags', 'text'
]
self.rootItem = TreeItem(self.headers)
def setTable(self, tname):
self.dbmodel.setTable(tname)
def select(self):
self.dbmodel.select()
#здесь должны грузиться данные
dct = dict() #словарь айди - список строк с данными
for ind in range(self.dbmodel.rowCount()):
#dct[int(self.dbmodel.data(self.dbmodel.index(ind,0)))] = [self.dbmodel.data(self.dbmodel.index(ind,j)) for j in range(self.dbmodel.columnCount())]
dct[int(self.dbmodel.data(self.dbmodel.index(ind, 0)))] = [
self.dbmodel.data(self.dbmodel.index(ind, j))
for j in range(self.dbmodel.columnCount())
]
def find_children_and_append(item: TreeItem, dct):
chlist = eval(item.data(6))
for ch in chlist:
tri = TreeItem(dct[ch], item)
if tri.data(6) != '[]':
find_children_and_append(tri, dct)
item.appendChild(tri)
for i in [j for j in dct.values() if j[8] == '[]']:
tri = TreeItem(i, self.rootItem)
find_children_and_append(tri, dct)
self.rootItem.appendChild(tri)
def columnCount(self, QModelIndex_parent=None, *args, **kwargs):
return self.dbmodel.columnCount()
def data(self, index, role):
if not index.isValid():
return None
if role != Qt.DisplayRole:
return None
item = index.internalPointer()
return item.data(index.column())
def flags(self, index):
if not index.isValid():
return Qt.NoItemFlags
return Qt.ItemIsEnabled | Qt.ItemIsSelectable | Qt.ItemIsEditable
def headerData(self, section, orientation, role):
if orientation == Qt.Horizontal and role == Qt.DisplayRole:
return self.headers[section]
#return self.dbmodel.headerData(section,orientation,role)
def index(self, row, column, parent):
if not self.hasIndex(row, column, parent):
return QModelIndex()
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
childItem = parentItem.child(row)
if childItem:
return self.createIndex(row, column, childItem)
else:
return QModelIndex()
def rowCount(self, parent):
if parent.column() > 0:
return 0
if not parent.isValid():
parentItem = self.rootItem
else:
parentItem = parent.internalPointer()
return parentItem.childCount()
def parent(self, index):
if not index.isValid():
return QModelIndex()
childItem = index.internalPointer()
parentItem = childItem.parent()
if parentItem == self.rootItem:
return QModelIndex()
return self.createIndex(parentItem.row(), 0, parentItem)
def getIndexById(self, _id):
for i in range(self.dbmodel.rowCount()):
if int(self.dbmodel.data(self.dbmodel.index(i, 0))) == _id:
return self.dbmodel.index(i, 0)
return QModelIndex()
def setData(self, modelIndex, value, int_role=Qt.EditRole):
"""
Функция вызывается при установке данных
:param QModelIndex:
:param QVariant:
:param int_role:
:return:
"""
print(self.dbmodel.record(0).setValue(12, 'sdfsfsdf'))
r = self.dbmodel.record(1)
r.setValue(12, 'Krevedko')
print(self.dbmodel.setRecord(0, r))
print(r)
return 1
class ReferenceDataDlg(QDialog):
def __init__(self, parent=None):
super(ReferenceDataDlg, self).__init__(parent)
self.model = QSqlTableModel(self)
self.model.setTable("reference")
self.model.setSort(ID, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, "ID")
self.model.setHeaderData(CATEGORY, Qt.Horizontal, "小车编号")
self.model.setHeaderData(SHORTDESC, Qt.Horizontal, "检查记录")
self.model.setHeaderData(LONGDESC, Qt.Horizontal, "巡检日期")
self.model.select()
self.view = QTableView()
self.view.setModel(self.model)
self.view.setSelectionMode(QTableView.SingleSelection)
self.view.setSelectionBehavior(QTableView.SelectRows)
self.view.setColumnHidden(ID, True)
self.view.resizeColumnsToContents()
buttonBox = QDialogButtonBox()
addButton = buttonBox.addButton("&添加", QDialogButtonBox.ActionRole)
deleteButton = buttonBox.addButton("&删除", QDialogButtonBox.ActionRole)
sortButton = buttonBox.addButton("&排序", QDialogButtonBox.ActionRole)
if not MAC:
addButton.setFocusPolicy(Qt.NoFocus)
deleteButton.setFocusPolicy(Qt.NoFocus)
sortButton.setFocusPolicy(Qt.NoFocus)
menu = QMenu(self)
sortByCategoryAction = menu.addAction("按小车编号排序")
sortByDescriptionAction = menu.addAction("按检查记录排序")
sortByIDAction = menu.addAction("按编号顺序排序")
sortButton.setMenu(menu)
closeButton = buttonBox.addButton(QDialogButtonBox.Close)
layout = QVBoxLayout()
layout.addWidget(self.view)
layout.addWidget(buttonBox)
self.setLayout(layout)
addButton.clicked.connect(self.addRecord)
deleteButton.clicked.connect(self.deleteRecord)
sortByCategoryAction.triggered.connect(lambda: self.sort(CATEGORY))
sortByDescriptionAction.triggered.connect(lambda: self.sort(SHORTDESC))
sortByIDAction.triggered.connect(lambda: self.sort(ID))
closeButton.clicked.connect(self.accept)
self.setWindowTitle("巡检历史数据")
self.setWindowIcon(
QtGui.QIcon('icon/update_128px_1156069_easyicon.net.ico'))
def addRecord(self):
row = self.model.rowCount()
self.model.insertRow(row)
index = self.model.index(row, CATEGORY)
self.view.setCurrentIndex(index)
self.view.edit(index)
def deleteRecord(self):
index = self.view.currentIndex()
if not index.isValid():
return
record = self.model.record(index.row())
category = record.value(CATEGORY)
desc = record.value(SHORTDESC)
if (QMessageBox.question(self, "Reference Data",
("确定删除 {1} 的数据?".format(desc, category)),
QMessageBox.Yes
| QMessageBox.No) == QMessageBox.No):
return
self.model.removeRow(index.row())
self.model.submitAll()
self.model.select()
def sort(self, column):
self.model.setSort(column, Qt.AscendingOrder)
self.model.select()
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None, dbFilename='None'):
super().__init__(parent) # Call the parent class. Create window.
self.ui = Ui_MainWindow() # Create UI object
self.ui.setupUi(self) # Create UI interface
self.ui.tabWidget.setVisible(True)
self.setCentralWidget(self.ui.tabWidget)
# tableView setting
self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectItems)
self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(44)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(70)
self.ui.tableView.setSortingEnabled(True)
# Initialize chart
self.__iniPieChart() # Initialize pie chart
self.__iniStackedBar() # Stacked bar
mpl.rcParams['font.sans-serif'] = ['Calibri']
mpl.rcParams['font.size'] = 8
# Choose the SQLITE database drive
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename) # Set the name of database
if self.DB.open(): # Open database
self.__openTable() # Open tables
else:
QMessageBox.warning(self, "Warning",
"Failed to open the database.")
# ==============Self-defined Fuctions============
def __getFieldNames(self): # Get names of all fields.
# Get empty records, only field name.
emptyRec = self.tabModel.record()
self.fldNum = {} # Dictionary of field name and index.
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
def __openTable(self): # Open the table of database
self.tabModel = QSqlTableModel(self, self.DB) # Data model
# Set the table of database TODO: Can user insert their own tables from
# the database?
self.tabModel.setTable("battery")
# Data storage，OnManualSubmit , OnRowChange
self.tabModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
# self.tabModel.setSort(
# self.tabModel.fieldIndex("RANDOM"),
# Qt.DescendingOrder) # Sorting
if (self.tabModel.select() == False): # Failed to query the data
QMessageBox.critical(
self, "Wrong",
"Something wrong. Failed to open the database\n" +
self.tabModel.lastError().text())
return
# self.tabModel.setFilter("NUM_RECORDS LIKE 'NONE'")
self.__getFieldNames() # Get the field name and index
# Field name shown
for i in self.fldNum:
self.tabModel.setHeaderData(self.fldNum[i], Qt.Horizontal,
i.capitalize())
# Create mappings between interface widget and the field name of data model
self.mapper = QDataWidgetMapper()
self.mapper.setModel(self.tabModel) # Setting data model
self.mapper.setSubmitPolicy(QDataWidgetMapper.AutoSubmit)
# The relations between interface widget and field name of tabModel
self.mapper.addMapping(self.ui.dbEditValue, self.fldNum["Value"])
self.mapper.addMapping(self.ui.dbEditRunit, self.fldNum["Raw_unit"])
self.mapper.addMapping(self.ui.dbComboProperty,
self.fldNum["Property"])
self.mapper.addMapping(self.ui.dbEditUnit, self.fldNum["Unit"])
self.mapper.addMapping(self.ui.dbEditName, self.fldNum["Name"])
self.mapper.addMapping(self.ui.dbEditEname,
self.fldNum["Extracted_name"])
self.mapper.addMapping(self.ui.dbEditRvalue, self.fldNum["Raw_value"])
self.mapper.addMapping(self.ui.dbEditDOI_2, self.fldNum["DOI"])
self.mapper.addMapping(self.ui.dbEditDOI_4, self.fldNum["Date"])
self.mapper.addMapping(self.ui.dbEditDOI_3, self.fldNum["Title"])
self.mapper.addMapping(self.ui.dbEditDOI, self.fldNum["Journal"])
self.mapper.addMapping(self.ui.dbEditTag, self.fldNum["Tag"])
self.mapper.addMapping(self.ui.dbEditInfo, self.fldNum["Info"])
self.mapper.addMapping(self.ui.dbEditType, self.fldNum["Type"])
self.mapper.addMapping(self.ui.dbEditWarning, self.fldNum["Warning"])
self.mapper.addMapping(self.ui.dbEditSpecifier,
self.fldNum["Specifier"])
self.mapper.toFirst() # Move to the first record
self.selModel = QItemSelectionModel(self.tabModel) # Select model
self.selModel.currentChanged.connect(
self.do_currentChanged) # Trigger when the current changed
self.selModel.currentRowChanged.connect(
self.do_currentRowChanged) # Trigger when the current row changed
self.ui.tableView.setModel(self.tabModel) # Setting the data model
self.ui.tableView.setSelectionModel(
self.selModel) # Setting the selection model
# self.ui.tableView.setColumnHidden(self.fldNum["TAG"], True) #Hide columns
# self.ui.tableView.setColumnHidden(self.fldNum["INFO"], True) #Hide
# columns
self.ui.tableView.setColumnHidden(self.fldNum["Extracted_name"],
True) # Hide columns
self.ui.tableView.setColumnHidden(self.fldNum["Unit"],
True) # Hide columns
self.ui.tableView.setColumnHidden(self.fldNum["Num_records"],
True) # Hide columns
# self.ui.tableView.setColumnHidden(
# self.fldNum["RAW_VALUE"], True) # Hide columns
# Update the conditions of actions of interface widget
self.ui.actRecAppend.setEnabled(True)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.btnDrawPieChart.setEnabled(True) # Pie Chart
self.ui.spinPieSize.setEnabled(True)
self.ui.spinHoleSize.setEnabled(True)
self.ui.chkBox_PieLegend.setEnabled(True)
self.ui.generateButton_3.setEnabled(True)
self.ui.searchButton_3.setEnabled(True)
self.ui.clearButton_3.setEnabled(True)
self.ui.frame_4.setEnabled(True)
self.ui.frame_3.setEnabled(True)
self.ui.searchInput.returnPressed.connect(self.ui.searchButton.click)
self.ui.searchInput_3.returnPressed.connect(
self.ui.searchButton_3.click)
def __iniPieChart(self): # Initialize pie chart
chart = QChart()
# chart.setTitle("Piechart")
chart.setAnimationOptions(QChart.SeriesAnimations)
chart.setTheme(QChart.ChartTheme(0))
self.ui.chartViewPie.setChart(chart) # Setting chart for chart view
self.ui.chartViewPie.setRenderHint(QPainter.Antialiasing)
self.ui.chartViewPie.setCursor(Qt.CrossCursor) # Setting cross cursor
def __iniStackedBar(self): # Initialize stacked bar chart
chart = QChart()
# chart.setTitle("Number of property records for each chemical")
chart.setAnimationOptions(QChart.SeriesAnimations)
chart.setTheme(QChart.ChartTheme(0))
self.ui.chartViewStackedBar.setChart(chart) # Set chart
self.ui.chartViewStackedBar.setRenderHint(QPainter.Antialiasing)
self.ui.chartViewStackedBar.setCursor(Qt.CrossCursor) # Set mouse
# ==========Table tab slot function==================
@pyqtSlot() # Save changes
def on_actSubmit_triggered(self):
res = self.tabModel.submitAll()
if (res == False):
QMessageBox.information(
self, "Information",
"Failed to store the changes. Wrong information. \n" +
self.tabModel.lastError().text())
else:
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot() # Cancel changes
def on_actRevert_triggered(self):
self.tabModel.revertAll()
self.ui.actSubmit.setEnabled(False)
self.ui.actRevert.setEnabled(False)
@pyqtSlot() # Add records
def on_actRecAppend_triggered(self):
self.tabModel.insertRow(
self.tabModel.rowCount(),
QModelIndex()) # Add one record in the last row
curIndex = self.tabModel.index(self.tabModel.rowCount() - 1,
1) # Create ModelIndex of the last row
self.selModel.clearSelection() # Clear selections
# Choosing the current row when selection.
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
currow = curIndex.row() # Get current row
@pyqtSlot() # Insert records
def on_actRecInsert_triggered(self):
curIndex = self.ui.tableView.currentIndex() # QModelIndex
self.tabModel.insertRow(curIndex.row(), QModelIndex())
self.selModel.clearSelection() # Clear selections
self.selModel.setCurrentIndex(curIndex, QItemSelectionModel.Select)
@pyqtSlot() # Delete records
def on_actRecDelete_triggered(self):
# Get the current index of current model
curIndex = self.selModel.currentIndex()
self.tabModel.removeRow(curIndex.row(),
QModelIndex()) # Delete the current row
self.tabModel.submit()
@pyqtSlot() # Help message box
def on_actHelp_triggered(self):
msg = QMessageBox()
msg.about(
self, "Help", '<div>'
'<h3>Table: Query the database according to data types and name or DOI.</h3>'
'<ul>'
'<li>Search the exact compound name in "<em>Exact Match</em>". Search the element or part of compound name in "<em>Generic Match</em>". </li>'
'<li>Click the "<em>Home</em>" or "<em>All</em>" button to view the full database</li>'
'<li>Refer to "<em>Correctness</em>" column for database evaluation details. </li>'
'<li>You can add your own database entries using the "<em>Insert</em>" tab; click "<em>Save</em>" to save the changes.</li>'
'</ul>'
'<h3>Figure: Statistical analysis of the database.</h3>'
'<ul>'
'<li><em>Pie chart</em> shows the proportion of data records.</li>'
'<li><em>Stacked bar chart</em> shows the data types for each compound. First input the compound name, click "<em>Add</em>" and then "<em>Generate</em>" data for overview. </li>'
'<li><em>Histogram </em>shows the distribution of each data type.</li>'
'<li><em>Venn diagram</em> shows the correlation of each data type.</li>'
'</ul>'
'</div>')
@pyqtSlot() # Filtering
def on_radioBtnVoltage_clicked(self):
flag, sqlmerge = self.merged_or_not()
print(sqlmerge)
self.tabModel.setFilter("PROPERTY LIKE 'Voltage' AND %s" % sqlmerge)
@pyqtSlot() # Filtering
def on_radioBtnCoulombic_clicked(self):
flag, sqlmerge = self.merged_or_not()
self.tabModel.setFilter("PROPERTY LIKE 'Coulombic Efficiency' AND %s" %
sqlmerge)
@pyqtSlot() # Filtering
def on_radioBtnConductivity_clicked(self):
flag, sqlmerge = self.merged_or_not()
self.tabModel.setFilter("PROPERTY LIKE 'Conductivity' AND %s" %
sqlmerge)
@pyqtSlot() # Filetering
def on_radioBtnCapacity_clicked(self):
flag, sqlmerge = self.merged_or_not()
self.tabModel.setFilter("PROPERTY LIKE 'Capacity' AND %s" % sqlmerge)
@pyqtSlot() # Filtering
def on_radioBtnEnergy_clicked(self):
flag, sqlmerge = self.merged_or_not()
self.tabModel.setFilter("PROPERTY LIKE 'Energy' AND %s" % sqlmerge)
@pyqtSlot() # Cancel Filetering
def on_radioBtnAll_clicked(self):
flag, sqlmerge = self.merged_or_not()
self.tabModel.setFilter("%s" % sqlmerge)
# print(self.tabModel.filter())
# self.tabModel.select()
def get_elements(self):
element_dic = {
"Hydrogen": "H",
"Helium": "He",
"Lithium": "Li",
"Beryllium": "Be",
"Boron": "B",
"Carbon": "C",
"Nitrogen": "N",
"Oxygen": "O",
"Fluorine": "F",
"Neon": "Ne",
"Sodium": "Na",
"Magnesium": "Mg",
"Aluminum": "Al",
"Silicon": "Si",
"Phosphorus": "P",
"Sulfur": "S",
"Chlorine": "Cl",
"Argon": "Ar",
"Potassium": "K",
"Calcium": "Ca",
"Scandium": "Sc",
"Titanium": "Ti",
"Vanadium": "V",
"Chromium": "Cr",
"Manganese": "Mn",
"Iron": "Fe",
"Cobalt": "Co",
"Nickel": "Ni",
"Copper": "Cu",
"Zinc": "Zn",
"Gallium": "Ga",
"Germanium": "Ge",
"Arsenic": "As",
"Selenium": "Se",
"Bromine": "Br",
"Krypton": "Kr",
"Rubidium": "Rb",
"Strontium": "Sr",
"Yttrium": "Y",
"Zirconium": "Zr",
"Niobium": "Nb",
"Molybdenum": "Mo",
"Technetium": "Tc",
"Ruthenium": "Ru",
"Rhodium": "Rh",
"Palladium": "Pd",
"Silver": "Ag",
"Cadmium": "Cd",
"Indium": "In",
"Tin": "Sn",
"Antimony": "Sb",
"Tellurium": "Te",
"Iodine": "I",
"Xenon": "Xe",
"Cesium": "Cs",
"Barium": "Ba",
"Lanthanum": "La",
"Cerium": "Ce",
"Praseodymium": "Pr",
"Neodymium": "Nd",
"Promethium": "Pm",
"Samarium": "Sm",
"Europium": "Eu",
"Gadolinium": "Gd",
"Terbium": "Tb",
"Dysprosium": "Dy",
"Holmium": "Ho",
"Erbium": "Er",
"Thulium": "Tm",
"Ytterbium": "Yb",
"Lutetium": "Lu",
"Hafnium": "Hf",
"Tantalum": "Ta",
"Tungsten": "W",
"Rhenium": "Re",
"Osmium": "Os",
"Iridium": "Ir",
"Platinum": "Pt",
"Gold": "Au",
"Mercury": "Hg",
"Thallium": "Tl",
"Lead": "Pb",
"Bismuth": "Bi",
"Polonium": "Po",
"Astatine": "At",
"Radon": "Rn",
"Francium": "Fr",
"Radium": "Ra",
"Actinium": "Ac",
"Thorium": "Th",
"Protactinium": "Pa",
"Uranium": "U",
"Neptunium": "Np",
"Plutonium": "Pu",
"Americium": "Am",
"Curium": "Cm",
"Berkelium": "Bk",
"Californium": "Cf",
"Einsteinium": "Es",
"Fermium": "Fm",
"Mendelevium": "Md",
"Nobelium": "No",
"Lawrencium": "Lr",
"Rutherfordium": "Rf",
"Dubnium": "Db",
"Seaborgium": "Sg",
"Bohrium": "Bh",
"Hassium": "Hs",
"Meitnerium": "Mt",
"Darmstadtium": "Ds",
"Roentgenium": "Rg",
"Copernicium": "Cn",
"Nihonium": "Nh",
"Flerovium": "Fl",
"Moscovium": "Mc",
"Livermorium": "Lv",
"Tennessine": "Ts",
"Oganesson": "Og"
}
return element_dic
def merged_or_not(self):
flag = self.ui.mergeBox.isChecked()
if flag:
sqlmerge = "NUM_RECORDS NOT LIKE 'NONE'"
else:
sqlmerge = "NUM_RECORDS LIKE 'NONE'"
return flag, sqlmerge
@pyqtSlot()
def on_homeButton_clicked(self):
self.tabModel.setFilter("NUM_RECORDS LIKE 'NONE'")
self.ui.mergeBox.setChecked(False)
@pyqtSlot()
def on_mergeButton_clicked(self):
self.tabModel.setFilter("NUM_RECORDS NOT LIKE 'NONE'")
self.ui.mergeBox.setChecked(True)
@pyqtSlot()
def on_searchButton_clicked(self):
searchtext = self.ui.searchInput.text()
searchclass = self.ui.searchClass.currentText()
matchtype = self.ui.matchType.currentText()
flag, sqlmerge = self.merged_or_not()
if searchclass == "DOI":
if matchtype == "Exact Match":
self.tabModel.setFilter("DOI LIKE '%s' AND %s" %
(searchtext, sqlmerge))
elif matchtype == "Generic Match":
self.tabModel.setFilter("DOI LIKE '%%%s%%' AND %s" %
(searchtext, sqlmerge))
if self.tabModel.rowCount() == 0:
self.tabModel.setFilter("")
QMessageBox.warning(
self, "Warning",
"No such DOIs in the database. Please search new DOI.")
elif searchclass == "Warning":
if matchtype == "Exact Match":
self.tabModel.setFilter("WARNING LIKE '%s' AND %s" %
(searchtext, sqlmerge))
elif matchtype == "Generic Match":
self.tabModel.setFilter("WARNING LIKE '%%%s%%' AND %s" %
(searchtext, sqlmerge))
if self.tabModel.rowCount() == 0:
self.tabModel.setFilter("")
QMessageBox.warning(
self, "Warning",
"No such DOIs in the database. Please search new DOI.")
elif searchclass == 'Name':
try:
searchtext = self.get_elements()[searchtext.capitalize()]
except BaseException:
pass
if matchtype == "Exact Match":
if self.ui.radioBtnAll.isChecked():
self.tabModel.setFilter("%s LIKE '%s' " %
(searchclass, searchtext))
elif self.ui.radioBtnVoltage.isChecked():
self.tabModel.setFilter(
"%s LIKE '%s' AND PROPERTY LIKE 'VOLTAGE' AND %s" %
(searchclass, searchtext, sqlmerge))
elif self.ui.radioBtnCapacity.isChecked():
self.tabModel.setFilter(
"%s LIKE '%s' AND PROPERTY LIKE 'CAPACITY' AND %s" %
(searchclass, searchtext, sqlmerge))
elif self.ui.radioBtnConductivity.isChecked():
self.tabModel.setFilter(
"%s LIKE '%s' AND PROPERTY LIKE 'CONDUCTIVITY' AND %s"
% (searchclass, searchtext, sqlmerge))
elif self.ui.radioBtnCoulombic.isChecked():
self.tabModel.setFilter(
"%s LIKE '%s' AND PROPERTY LIKE 'COULOMBIC EFFICIENCY' AND %s "
% (searchclass, searchtext, sqlmerge))
elif self.ui.radioBtnEnergy.isChecked():
self.tabModel.setFilter(
"%s LIKE '%s' AND PROPERTY LIKE 'ENERGY'AND %s " %
(searchclass, searchtext, sqlmerge))
elif matchtype == "Generic Match":
if self.ui.radioBtnAll.isChecked():
self.tabModel.setFilter(
"(EXTRACTED_NAME LIKE '%%''%s''%%' OR NAME LIKE '%s') AND %s"
% (searchtext, searchtext, sqlmerge))
elif self.ui.radioBtnVoltage.isChecked():
self.tabModel.setFilter(
"EXTRACTED_NAME LIKE '%%''%s''%%' OR NAME LIKE '%s' AND %s"
% (searchtext, searchtext, sqlmerge))
elif self.ui.radioBtnCapacity.isChecked():
self.tabModel.setFilter(
"EXTRACTED_NAME LIKE '%%''%s''%%' OR NAME LIKE '%s' AND %s"
% (searchtext, searchtext, sqlmerge))
elif self.ui.radioBtnConductivity.isChecked():
self.tabModel.setFilter(
"EXTRACTED_NAME LIKE '%%''%s''%%' OR NAME LIKE '%s' AND %s"
% (searchtext, searchtext, sqlmerge))
elif self.ui.radioBtnCoulombic.isChecked():
self.tabModel.setFilter(
"EXTRACTED_NAME LIKE '%%''%s''%%' OR NAME LIKE '%s' AND %s"
% (searchtext, searchtext, sqlmerge))
elif self.ui.radioBtnEnergy.isChecked():
self.tabModel.setFilter(
"EXTRACTED_NAME LIKE '%%''%s''%%' OR NAME LIKE '%s' AND %s"
% (searchtext, searchtext, sqlmerge))
if self.tabModel.rowCount() == 0:
self.tabModel.setFilter("")
QMessageBox.warning(
self, "Warning",
"No such compounds in the database. Please search new compounds."
)
# ============Picture Tab 1, Pie Chart=====================
@pyqtSlot() # Draw the pie chart
def on_btnDrawPieChart_clicked(self):
self.draw_pieChart()
@pyqtSlot(float) # Set holeSize
def on_spinHoleSize_valueChanged(self, arg1):
seriesPie = self.ui.chartViewPie.chart().series()[0]
seriesPie.setHoleSize(arg1)
@pyqtSlot(float) # Set pieSize
def on_spinPieSize_valueChanged(self, arg1):
seriesPie = self.ui.chartViewPie.chart().series()[0]
seriesPie.setPieSize(arg1)
@pyqtSlot(bool) # Set legend checkbox
def on_chkBox_PieLegend_clicked(self, checked):
self.ui.chartViewPie.chart().legend().setVisible(checked)
def pie_data(
self): # Return a list of property name and the number of property
num_list = []
pro_list = [
"CAPACITY", "CONDUCTIVITY", "COULOMBIC EFFICIENCY", "ENERGY",
"VOLTAGE"
]
for i in range(len(pro_list)):
query = QSqlQuery(
db=self.DB,
query=
"SELECT COUNT(NAME) FROM BATTERY WHERE PROPERTY LIKE '%s' AND NUM_RECORDS LIKE 'NONE'"
% pro_list[i]) # Query database
while query.next():
num_value = query.value(0) # Returned value for each query
item = self.ui.treeWidget_2.topLevelItem(i) # The ith row
# The 2nd column
item.setText(1, str(num_value))
item.setTextAlignment(1, Qt.AlignHCenter)
num_list.append(num_value)
return pro_list, num_list
def draw_pieChart(self): # Draw the pie chart
chart = self.ui.chartViewPie.chart()
chart.legend().setAlignment(Qt.AlignRight) # AlignRight,AlignBottom
chart.removeAllSeries() # Delete all series
seriesPie = QPieSeries() # Pie chart series
seriesPie.setHoleSize(self.ui.spinHoleSize.value()) # Hole size
seriesPie.setPieSize(self.ui.spinPieSize.value()) # Pie size
sec_count = 5 # Number of properties
seriesPie.setLabelsVisible(True) # Label
pro, num = self.pie_data()
for i in range(sec_count):
seriesPie.append(pro[i], num[i])
seriesPie.setLabelsVisible(True) # Label
# Pie hoverd when mouse selected
seriesPie.hovered.connect(self.do_pieHovered)
chart.addSeries(seriesPie)
chart.setTitle("Proportion of data records for each property")
font = QFont()
font.setPointSize(12)
font.setWeight(75)
chart.setTitleFont(font)
font = QFont()
font.setPointSize(12)
font.setBold(False)
font.setWeight(35)
legend = chart.legend()
legend.setFont(font)
# =========Picture tab 2. StackedBar=========
@pyqtSlot() # Draw StackedBar
def on_btnStackedBar_clicked(self):
self.draw_stackedBar()
@pyqtSlot() # Draw horizontal StackedBar
def on_btnStackedBarH_clicked(self):
self.draw_stackedBar(False)
# Search button in the Stacked bar chart tab. Add name to the first column
# of the table.
@pyqtSlot()
def on_searchButton_3_clicked(self):
searchtext = self.ui.searchInput_3.text()
current_index = self.ui.stackedWidget.topLevelItemCount()
item_0 = QtWidgets.QTreeWidgetItem(self.ui.stackedWidget)
item_0.setText(0, searchtext)
@pyqtSlot() # Generate data using the input chemical names
def on_generateButton_3_clicked(self):
self.ui.zoominButton.setEnabled(True)
self.ui.zoomoutButton.setEnabled(True)
self.ui.originalButton.setEnabled(True)
self.ui.btnStackedBar.setEnabled(True) # Stacked bar chart
self.ui.btnStackedBarH.setEnabled(True)
current_index = self.ui.stackedWidget.topLevelItemCount()
chemical_list = [
self.ui.stackedWidget.topLevelItem(index).text(0)
for index in range(current_index)
] # Get a list of the inputed chemical name
pro_list = [
"CAPACITY", "CONDUCTIVITY", "COULOMBIC EFFICIENCY", "ENERGY",
"VOLTAGE"
]
for row, chemical in enumerate(chemical_list):
for index, pro in enumerate(pro_list):
query = QSqlQuery(
db=self.DB,
query=
"SELECT SUM(NUM_RECORDS) FROM BATTERY WHERE PROPERTY LIKE '%s' AND NAME LIKE '%s' "
% (pro, chemical))
while query.next():
num_value = query.value(0)
print(num_value)
if num_value == "":
num_value = 0
item = self.ui.stackedWidget.topLevelItem(
row) # The row'th row
item.setText(index + 1, str(num_value))
item.setTextAlignment(index + 1, Qt.AlignHCenter)
@pyqtSlot() # Clear button
def on_clearButton_3_clicked(self):
self.ui.stackedWidget.clear()
@pyqtSlot() # Zoom in
def on_zoominButton_clicked(self):
self.ui.chartViewStackedBar.chart().zoom(1.2)
@pyqtSlot() # Zoom out
def on_zoomoutButton_clicked(self):
self.ui.chartViewStackedBar.chart().zoom(0.8)
@pyqtSlot() # Reset original size
def on_originalButton_clicked(self):
self.ui.chartViewStackedBar.chart().zoomReset()
def draw_stackedBar(self, isVertical=True): # Stacked bar chart
chart = self.ui.chartViewStackedBar.chart()
chart.removeAllSeries() # Remove all series
chart.removeAxis(chart.axisX()) # remove axis
chart.removeAxis(chart.axisY())
if isVertical: # Vertical
chart.setTitle("Number of property records for each chemical ")
chart.legend().setAlignment(Qt.AlignBottom)
else: # Horizontal
chart.setTitle("Number of property records for each chemical")
chart.legend().setAlignment(Qt.AlignRight)
# Create data sets
setCapacity = QBarSet("Capacity")
setConductivity = QBarSet("Conductivity")
setCoulombic = QBarSet("Coulombic")
setEnergy = QBarSet("Energy")
setVoltage = QBarSet("Voltage")
chemical_Count = self.ui.stackedWidget.topLevelItemCount()
nameList = [] # Chemical lists
for i in range(chemical_Count):
item = self.ui.stackedWidget.topLevelItem(i)
# print(item.text(1))
nameList.append(item.text(0))
setCapacity.append(float(item.text(1)))
setConductivity.append(float(item.text(2)))
setCoulombic.append(float(item.text(3)))
setEnergy.append(float(item.text(4)))
setVoltage.append(float(item.text(5)))
# Create series
if isVertical:
seriesBar = QStackedBarSeries()
else:
seriesBar = QHorizontalStackedBarSeries()
seriesBar.append(setCapacity)
seriesBar.append(setConductivity)
seriesBar.append(setCoulombic)
seriesBar.append(setEnergy)
seriesBar.append(setVoltage)
seriesBar.setLabelsVisible(True) # Show labels for each bar
seriesBar.setLabelsFormat("@value")
seriesBar.setLabelsPosition(QAbstractBarSeries.LabelsCenter)
seriesBar.setBarWidth(0.3)
chart.addSeries(seriesBar)
axisStud = QBarCategoryAxis() # Category axis
axisStud.append(nameList)
axisStud.setRange(nameList[0], nameList[chemical_Count - 1])
axisValue = QValueAxis() # Value axis
# axisValue.setRange(0, 300)
axisValue.setTitleText("Number of records")
# axisValue.setTickCount(6)
axisValue.applyNiceNumbers()
if isVertical:
chart.setAxisX(axisStud, seriesBar)
chart.setAxisY(axisValue, seriesBar)
else:
chart.setAxisY(axisStud, seriesBar)
chart.setAxisX(axisValue, seriesBar)
for marker in chart.legend().markers(): # QLegendMarker lists
marker.clicked.connect(self.do_LegendMarkerClicked)
# =========Picture tab 3. Histogram=========
@pyqtSlot(bool) # Show toolbar
def on_gBoxHist_toolbar_2_clicked(self, checked):
self.ui.widgetHist_2.setToolbarVisible(checked)
@pyqtSlot() # Draw
def on_histButton_clicked(self):
self.__drawHist()
def hist_data(self, pro):
query = QSqlQuery(
db=self.DB,
query=
"SELECT VALUE FROM BATTERY WHERE PROPERTY LIKE '%s' AND NUM_RECORDS LIKE 'NONE'"
% pro)
data = []
while query.next():
num_value = query.value(0)
data.append(num_value)
return data
def __drawHist(self): # Histogram
pro = self.ui.propertycomboBox_2.currentText()
data = self.hist_data(pro)
self.ui.widgetHist_2.figure.clear() # Clear figure
ax = self.ui.widgetHist_2.figure.add_subplot(1, 1, 1)
if pro == 'Capacity':
M, bins, patches = ax.hist(
[5000 if float(i) > 4999 else float(i) for i in data],
bins='auto',
color='darkgreen',
alpha=0.5,
rwidth=1)
ax.set_xlim(0, 5001)
# ax.set_ylim(0,15000)
ax.tick_params(labelsize=12)
ax.set_xticklabels(['0', '1000', '2000', '3000', '4000', '5000+'])
ax.set_xlabel('Capacity (mAh/g)', fontsize=14)
ax.set_ylabel('Frequency', fontsize=14)
ax.set_title('Battery Capacity Distrbution', fontsize=14)
ax.figure.canvas.draw()
elif pro == "Voltage":
count1 = []
for i in data:
count1.append(float(i))
n, bins, patches = ax.hist(x=count1,
bins='auto',
range=(0, 8),
color='r',
alpha=0.5,
rwidth=1)
ax.set_xticklabels(['0', '1', '2', '3', '4', '5', '6', '7', '8+'])
ax.set_xlim(0, 8)
ax.tick_params(labelsize=12)
ax.set_xlabel('Voltage (V)', fontsize=14)
ax.set_ylabel('Frequency', fontsize=14)
ax.set_title('Battery Voltage Distrbution', fontsize=14)
ax.figure.canvas.draw()
elif pro == "Energy":
n, bins, patches = ax.hist(
[3000 if float(i) > 2999 else float(i) for i in data],
bins='auto',
color='y',
alpha=0.5,
rwidth=1)
ax.set_xticklabels(
['0', '500', '1000', '1500', '2000', '2500', '3000+'])
ax.set_xlim(0, 3001)
ax.tick_params(labelsize=12)
ax.set_xlabel('Energy (Wh/kg)', fontsize=14)
ax.set_ylabel('Frequency', fontsize=14)
ax.set_title('Battery Energy Distrbution', fontsize=14)
ax.figure.canvas.draw()
elif pro == "Coulombic Efficiency":
dataplot = [float(i) for i in data]
n, bins, patches = ax.hist(x=dataplot,
bins='auto',
color='c',
alpha=0.5,
rwidth=1)
ax.set_xlim(0, 100)
ax.tick_params(labelsize=12)
ax.set_xlabel('Coulombic Effciency (%)', fontsize=14)
ax.set_ylabel('Frequency', fontsize=14)
ax.set_title('Battery Coulombic Effciency Distrbution',
fontsize=14)
ax.figure.canvas.draw()
elif pro == "Conductivity":
dataplot = [float(i) for i in data]
n, bins, patches = ax.hist(x=dataplot,
bins=np.logspace(
np.log10(1e-15), np.log10(1)),
color='k',
alpha=0.5,
rwidth=1)
ax.set_xlim(1e-20, 10)
ax.set_xscale('log')
ax.tick_params(labelsize=12)
ax.set_xlabel('log10 (Conductivity (S/cm))', fontsize=14)
ax.set_ylabel('Frequency', fontsize=14)
ax.set_title('Battery Conductivity Distrbution', fontsize=14)
ax.figure.canvas.draw()
# =========Picture tab 4. Venn diagram=========
@pyqtSlot() # Draw
def on_btnVenn_clicked(self):
self.__drawVenn()
@pyqtSlot(bool) # Show toolbar
def on_gBoxHist_toolbar_3_clicked(self, checked):
self.ui.widgetVenn.setToolbarVisible(checked)
def __drawVenn(self):
self.ui.widgetVenn.figure.clear() # Clear figure
pro_list = [
"Capacity", "Conductivity", "Coulombic Efficiency", "Energy",
"Voltage"
]
data_dic = {}
color_dic = dict(zip(pro_list, ['C0', 'C2', 'C6', 'C8', 'C9']))
for i in pro_list:
query = QSqlQuery(
db=self.DB,
query=
"SELECT COUNT(DISTINCT NAME) FROM BATTERY WHERE PROPERTY LIKE '%s' AND NUM_RECORDS LIKE 'NONE'"
% i)
while query.next():
num_value = query.value(0)
data_dic[i] = num_value
for i, combo in enumerate(itertools.combinations(pro_list, 2)):
query = QSqlQuery(
db=self.DB,
query=
"SELECT COUNT() FROM (SELECT DISTINCT NAME AS PRO1 FROM BATTERY WHERE PROPERTY LIKE '%s'AND NUM_RECORDS LIKE 'NONE') INNER JOIN (SELECT DISTINCT NAME AS PRO2 FROM BATTERY WHERE PROPERTY LIKE '%s'AND NUM_RECORDS LIKE 'NONE') ON PRO1 = PRO2"
% combo)
while query.next():
num = query.value(0)
x3 = num
x1 = data_dic[combo[0]] - x3
x2 = data_dic[combo[1]] - x3
hf = self.ui.widgetVenn.figure
# hf.set_figheight(30)
# hf.set_figwidth(30)
# print(dir(hf))
hf.set_size_inches((10, 10))
ax1 = hf.add_subplot(5, 2, i + 1)
v = venn2(subsets=(x1, x2, x3),
set_labels=(combo[0], combo[1]),
ax=ax1)
v.get_patch_by_id('A').set_alpha(1)
v.get_patch_by_id('A').set_color(color_dic[combo[0]])
v.get_patch_by_id('B').set_color(color_dic[combo[1]])
ax1.figure.canvas.draw()
# =============Self-defined slot function===============================
# Update the conditions of actPost and actCancel
def do_currentChanged(self, current, previous):
self.ui.actSubmit.setEnabled(
self.tabModel.isDirty()) # Use when not saving changes
self.ui.actRevert.setEnabled(self.tabModel.isDirty())
def do_currentRowChanged(self, current,
previous): # Control during row changes
self.ui.actRecDelete.setEnabled(current.isValid())
# Update current row index of mapping
self.mapper.setCurrentIndex(current.row())
# Get current record,QSqlRecord
curRec = self.tabModel.record(current.row())
def do_pieHovered(self, pieSlice,
state): # Mouse move in and out in the pie chart
pieSlice.setExploded(state) # Pop-up animation
if state: # Show the tab of percentages
self.__oldLabel = pieSlice.label() # Save original labels
pieSlice.setLabel(self.__oldLabel + ": %.1f%%" %
(pieSlice.percentage() * 100))
font = QFont()
font.setPointSize(10)
font.setBold(False)
font.setWeight(25)
pieSlice.setLabelFont(font)
else: # show original labels
pieSlice.setLabel(self.__oldLabel)
font = QFont()
font.setPointSize(10)
font.setBold(False)
font.setWeight(25)
pieSlice.setLabelFont(font)
def do_LegendMarkerClicked(self): # Click legend marker
marker = self.sender() # QLegendMarker
marker.series().setVisible(not marker.series().isVisible())
marker.setVisible(True)
alpha = 1.0
if not marker.series().isVisible():
alpha = 0.5
brush = marker.labelBrush() # QBrush
color = brush.color() # QColor
color.setAlphaF(alpha)
brush.setColor(color)
marker.setLabelBrush(brush)
brush = marker.brush()
color = brush.color()
color.setAlphaF(alpha)
brush.setColor(color)
marker.setBrush(brush)
pen = marker.pen() # QPen
color = pen.color()
color.setAlphaF(alpha)
pen.setColor(color)
marker.setPen(pen)
