基于Python和PyQt的公司审计信息统计平台
技术：Python
概述
针对一个公司审计信息统计项目需求，利用PyQt5的图形界面技术和SQLite数据库技术，构建轻量级的公司审计信息统计平台。利用 Visual Studio 2013 集成开发环境，采用QtDesigner 布局、Widget、PyUIC转换代码 、读取Excel文件、写入Excel文件、模板生成表格HTML代码、操作系统打印功能等。
详细
一、运行效果
二、实现过程
①、审计会议模块
审计会议界面主体框架是QWidget，审计会议模块主要是通过SQLite数据库的metting表用来存储整改检查记录，整改检查模块支持查询、新增记录、修改记录、删除记录、分页、导入导出Excel、打印功能。
#验证日期格式
def validate_date(self,date_text):
try:
datetime.datetime.strptime(str(date_text), '%Y/%m/%d')
except ValueError:
return False
return True
# 添加一行数据行
@QtCore.pyqtSlot()
def add_row_data(self):
model = self.tableView.model()
id = model.rowCount()
addMeetingDialog = addDialog()
addMeetingDialog.show()
if(addMeetingDialog.exec_() == 1):
name = addMeetingDialog.txtName.text().strip()
meeting = addMeetingDialog.txtMeeting.text().strip()
date = addMeetingDialog.dateEdit.date().toString('yyyy/MM/dd')
address = addMeetingDialog.txtAddress.text().strip()
joinno = addMeetingDialog.txtWithPeople.text().strip()
remark = addMeetingDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("INSERT INTO metting(id,name,meeting,date,address,joinno,remark) SELECT MAX(id) + 1,'{0}','{1}','{2}','{3}','{4}','{5}' FROM metting".format(name,meeting,date,address,joinno,remark))
if addMeetingDialog.dateEdit.date() < self.min_date:
self.min_date = addMeetingDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addMeetingDialog.dateEdit.date() > self.max_date:
self.max_date = addMeetingDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","新增会议成功！",QMessageBox.Ok)
else:
if addMeetingDialog.dateEdit.date() < self.min_date:
self.min_date = addMeetingDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addMeetingDialog.dateEdit.date() > self.max_date:
self.max_date = addMeetingDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
# 修改数据行
@QtCore.pyqtSlot()
def mod_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择会议记录，然后修改。",QMessageBox.Ok)
return
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = self.queryModel.record(rows[0].row()).value("name")
meeting = self.queryModel.record(rows[0].row()).value("meeting")
date = self.queryModel.record(rows[0].row()).value("date")
address = self.queryModel.record(rows[0].row()).value("address")
joinno = self.queryModel.record(rows[0].row()).value("joinno")
remark = self.queryModel.record(rows[0].row()).value("remark")
modMeetingDialog = modDialog()
modMeetingDialog.id = id
modMeetingDialog.txtName.setText(str(name))
modMeetingDialog.txtMeeting.setText(str(meeting))
if self.validate_date(date):
modMeetingDialog.dateEdit.setDateTime(datetime.datetime.strptime(date,"%Y/%m/%d"))
modMeetingDialog.txtAddress.setText(str(address))
modMeetingDialog.txtWithPeople.setText(str(joinno))
modMeetingDialog.txtComment.setText(str(remark))
modMeetingDialog.show()
if(modMeetingDialog.exec_() == 1):
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = modMeetingDialog.txtName.text().strip()
meeting = modMeetingDialog.txtMeeting.text().strip()
date = modMeetingDialog.dateEdit.date().toString('yyyy/MM/dd')
address = modMeetingDialog.txtAddress.text().strip()
joinno = modMeetingDialog.txtWithPeople.text().strip()
remark = modMeetingDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("UPDATE metting SET name = '{1}', meeting = '{2}', date = '{3}', address = '{4}', joinno = '{5}', remark = '{6}' WHERE id = {0}".format(id,name,meeting,date,address,joinno,remark))
if modMeetingDialog.dateEdit.date() < self.min_date:
self.min_date = modMeetingDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modMeetingDialog.dateEdit.date() > self.max_date:
self.max_date = modMeetingDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","修改会议成功！",QMessageBox.Ok)
else:
if modMeetingDialog.dateEdit.date() < self.min_date:
self.min_date = modMeetingDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modMeetingDialog.dateEdit.date() > self.max_date:
self.max_date = modMeetingDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择会议记录，然后修改。",QMessageBox.Ok)
return
# 删除一行数据
@QtCore.pyqtSlot()
def del_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择会议记录，然后删除。",QMessageBox.Ok)
return
rows = set()
for idx in self.tableView.selectedIndexes():
record = self.queryModel.record(idx.row())
id = record.value("id")
rows.add(id)
if (QMessageBox.question(self, "XX公司审计信息统计平台",("是否删除选中的会议?"),QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
query = QSqlQuery()
for id in rows:
query.exec_("DELETE FROM metting WHERE id = {0}".format(id))
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择会议记录，然后删除。",QMessageBox.Ok)
return
def setButtonStatus(self):
if (self.currentPage == 1 and self.totalPage == 1):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(False)
elif (self.currentPage > 1 and self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
elif (self.currentPage == 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(True)
elif (self.currentPage > 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery(self.queryCondition2)
self.totalRecord = self.queryModel.rowCount()
return
# 得到总页数
def getPageCount(self):
self.getTotalRecordCount()
# 上取整
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
return
# 分页记录查询
def recordQuery(self, index):
self.queryCondition = ""
conditionChoice = self.condisionComboBox.currentText()
if (conditionChoice == "按审计员姓名查询"):
conditionChoice = 'name'
elif (conditionChoice == "按会议查询"):
conditionChoice = 'meeting'
elif (conditionChoice == '按地点查询'):
conditionChoice = 'address'
else:
conditionChoice = 'joinno'
if (self.searchEdit.text() == ""):
if self.rbAll.isChecked():
self.queryCondition = "SELECT * FROM metting"
else:
self.queryCondition = "SELECT * FROM metting WHERE date BETWEEN '%s' AND '%s'" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(self.queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
self.queryCondition = ("SELECT * FROM metting ORDER BY id limit %d,%d " % (index, self.pageRecord))
self.queryCondition2 = "SELECT * FROM metting ORDER BY id"
else:
self.queryCondition = ("SELECT * FROM metting WHERE date BETWEEN '%s' AND '%s' ORDER BY id limit %d,%d " % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM metting WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(self.queryCondition)
self.setButtonStatus()
return
# 得到模糊查询条件
temp = self.searchEdit.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
if self.rbAll.isChecked():
self.queryCondition = ("SELECT * FROM metting WHERE %s LIKE '%s'" % (conditionChoice, s))
else:
self.queryCondition = ("SELECT * FROM metting WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s'" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(self.queryCondition)
self.totalRecord = self.queryModel.rowCount()
# 当查询无记录时的操作
if (self.totalRecord == 0):
QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)
if self.rbAll.isChecked():
self.queryCondition = "SELECT * FROM metting"
else:
self.queryCondition = "SELECT * FROM metting WHERE date BETWEEN '%s' AND '%s'" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(self.queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
self.queryCondition = ("SELECT * FROM metting ORDER BY id limit %d,%d " % (index, self.pageRecord))
self.queryCondition2 = "SELECT * FROM metting ORDER BY id"
else:
self.queryCondition = ("SELECT * FROM metting WHERE date BETWEEN '%s' AND '%s' ORDER BY id limit %d,%d " % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM metting WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(self.queryCondition)
self.setButtonStatus()
return
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
self.queryCondition = ("SELECT * FROM metting WHERE %s LIKE '%s' ORDER BY id LIMIT %d,%d " % (conditionChoice, s, index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM metting WHERE %s LIKE '%s' ORDER BY id" % (conditionChoice,s))
else:
self.queryCondition = ("SELECT * FROM metting WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id LIMIT %d,%d " % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM metting WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(self.queryCondition)
self.setButtonStatus()
return
# 点击查询
def searchButtonClicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击查询
def searchButtonClicked2(self):
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向前翻页
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向后翻页
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击跳转
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
# 打印数据
@QtCore.pyqtSlot()
def prt_row_data(self):
"""
这个函数就是告诉我们调用QPrintDialog准备进行打印了。
QPrintDialog类提供了一个用于指定打印机配置的对话框。对话框允许用户更改文档相关设置，如纸张尺寸和方向，打印类型（颜色或灰度），页面范围和打印份数。
还提供控制以使用户可以从可用的打印机中进行选择，包括任何配置的网络打印机。通常，QPrintDialog对象使用QPrinter对象构造，并使用exec()函数执行。
"""
printdialog = QPrintDialog(self.printer,self)
"""
在我们选择好打印机等等后，点击打印（即对话框被用户接受，则QPrinter对象被正确配置为打印），我们会调用QTextEdit中的print方法进行相关的打印
"""
if QDialog.Accepted == printdialog.exec_():
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
cur.execute(self.queryCondition2)
test = cur.fetchall()
template = Template("""
<table border="1" cellspacing="0" cellpadding="2">
<tr>
<th>审计员姓名</th>
<th>会议</th>
<th>时间</th>
<th>地点</th>
<th>参加人员</th>
<th>备注</th>
</tr>
{% for row in test %}
<tr>
<td> {{ row[1] if row[1] != None }}</td>
<td max-width="50%"> {{ row[2] if row[2] != None }}</td>
<td> {{ row[3] if row[3] != None }}</td>
<td max-width="50%"> {{ row[4] if row[4] != None }}</td>
<td> {{ row[5] if row[5] != None }}</td>
<td> {{ row[6] if row[6] != None }}</td>
</tr>
{% endfor %}
</table>
""")
text = template.render(test=test)
self.editor.setHtml(text)
self.editor.document().print_(printdialog.printer())
cur.close()
c.close()
QMessageBox.question(self, "XX公司审计信息统计平台","会议表格已经提交打印！",QMessageBox.Ok)
def prt_setup_data(self):
"""
QPageSetupDialog类为打印机上的页面相关选项提供了一个配置对话框。这个就必须使用到QPrinter对象了。
"""
printsetdialog = QPageSetupDialog(self.printer,self)
printsetdialog.exec_()#这句话就相当于我们执行确认的页面设置信息。
# 导入Excel文件
@QtCore.pyqtSlot()
def imp_excel_data(self):
fileName, filetype = QFileDialog.getOpenFileName(self,
"导入Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
if(self.readExcelFile(fileName)):
#设置日期最大值与最小值
self.min_date = QDate.currentDate()
self.max_date = QDate.currentDate()
query = QSqlQuery()
if not query.exec_('SELECT MIN(date) AS Min_Date,MAX(date) AS Max_Date from metting'):
query.lastError()
else:
QMessageBox.question(self, "XX公司审计信息统计平台","导入Excel文件成功！",QMessageBox.Ok)
while query.next():
self.min_date = QDate.fromString(query.value(0),'yyyy/MM/dd')
self.max_date = QDate.fromString(query.value(1),'yyyy/MM/dd')
self.dateStartEdit.setMinimumDate(self.min_date)
self.dateStartEdit.setMaximumDate(self.max_date)
self.dateEndEdit.setMinimumDate(self.min_date)
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked()
else:
QMessageBox.critical(self, "XX公司审计信息统计平台","Excel文件格式错误！",QMessageBox.Ok)
'''数据库插入操作'''
def insert(self,name,meeting,date,address,joinno,remark):
sql = "insert into metting(name,meeting,date,address,joinno,remark) values ('%s','%s','%s','%s','%s','%s')" % (name,meeting,date,address,joinno,remark)
self.cursor.execute(sql)
'''读取Excel文件'''
def readExcelFile(self, file):
data = xlrd.open_workbook(file)
for sheet in data.sheets():
if sheet.name == 'meeting':
conn = sqlite3.connect('cmdb.sqlite')
self.cursor = conn.cursor()
for rowId in range(1, sheet.nrows):
row = sheet.row_values(rowId)
if row:
self.insert(row[1],row[2],row[3],row[4],row[5],row[6])
conn.commit()
self.cursor.close()
conn.close()
return True
return False
@QtCore.pyqtSlot()
def sqlite_get_col_names(self,cur, select_sql):
cur.execute(select_sql)
return [tuple[0] for tuple in cur.description]
@QtCore.pyqtSlot()
def query_by_sql(self,cur, select_sql):
cur.execute(select_sql)
return cur.fetchall()
# 获取字符串长度，一个中文的长度为2
def len_byte(self,value):
length = len(value)
utf8_length = len(value.encode('utf-8'))
length = (utf8_length - length) / 2 + length
return int(length)
@QtCore.pyqtSlot()
def sqlite_to_workbook_with_head(self,cur, table, select_sql, workbook,style):
ws = workbook.add_sheet(table)
#enumerate针对一个可迭代对象，生成的是序号加上内容
for colx, heading in enumerate(self.sqlite_get_col_names(cur, select_sql)):
ws.write(0, colx, self.queryModel.headerData(colx,Qt.Horizontal),style) #在第1行的colx列写上头部信息
#序号
id = 1
#确定栏位宽度
col_width = []
for rowy, row in enumerate(self.query_by_sql(cur, select_sql)):
for colx, text in enumerate(row): #row是一行的内容
t = id if colx == 0 else text
ws.write(rowy + 1,colx , t,style) #在rowy+1行，colx写入数据库内容text
if rowy == 0:
col_width.append(self.len_byte(str(t)))
elif col_width[colx] < self.len_byte(str(t)):
col_width[colx] = self.len_byte(str(t))
id+=1
#设置栏位宽度，栏位宽度小于10时候采用默认宽度
for i in range(len(col_width)):
if col_width[i] > 10:
ws.col(i).width = 256 * col_width[i]
# 导出Excel文件
@QtCore.pyqtSlot()
def xpt_excel_data(self):
fileName, filetype = QFileDialog.getSaveFileName(self,
"导出Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
select_sql = self.queryCondition2
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style1 = xlwt.XFStyle()
style1.borders = borders
workbook = xlwt.Workbook(encoding='utf-8')
self.sqlite_to_workbook_with_head(cur, 'meeting', select_sql, workbook,style1)
cur.close()
c.close()
workbook.save(fileName)
QMessageBox.question(self, "XX公司审计信息统计平台","导出Excel文件成功！",QMessageBox.Ok)②、审计调查模块
审计调查界面主体框架是QWidget，审计调查模块主要是通过SQLite数据库的invest表用来存储整改检查记录，整改检查模块支持查询、新增记录、修改记录、删除记录、分页、导入导出Excel、打印功能。
#验证日期格式
def validate_date(self,date_text):
try:
datetime.datetime.strptime(str(date_text), '%Y/%m/%d')
except ValueError:
return False
return True
# 添加一行数据行
@QtCore.pyqtSlot()
def add_row_data(self):
model = self.tableView.model()
id = model.rowCount()
addInvestDialog = addDialog()
addInvestDialog.show()
if(addInvestDialog.exec_() == 1):
name = addInvestDialog.txtName.text().strip()
invest = addInvestDialog.txtInvest.text().strip()
date = addInvestDialog.dateEdit.date().toString('yyyy/MM/dd')
address = addInvestDialog.txtAddress.text().strip()
remark = addInvestDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("INSERT INTO invest(name,invDetils,date,address,remark) VALUES('{0}','{1}','{2}','{3}','{4}')".format(name,invest,date,address,remark))
if addInvestDialog.dateEdit.date() < self.min_date:
self.min_date = addInvestDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addInvestDialog.dateEdit.date() > self.max_date:
self.max_date = addInvestDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","新增审计调查成功！",QMessageBox.Ok)
else:
if addInvestDialog.dateEdit.date() < self.min_date:
self.min_date = addInvestDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addInvestDialog.dateEdit.date() > self.max_date:
self.max_date = addInvestDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
# 修改数据行
@QtCore.pyqtSlot()
def mod_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择审计调查记录，然后修改。",QMessageBox.Ok)
return
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = self.queryModel.record(rows[0].row()).value("name")
invDetils = self.queryModel.record(rows[0].row()).value("invDetils")
date = self.queryModel.record(rows[0].row()).value("date")
address = self.queryModel.record(rows[0].row()).value("address")
remark = self.queryModel.record(rows[0].row()).value("remark")
modInvestDialog = modDialog()
modInvestDialog.id = id
modInvestDialog.txtName.setText(str(name))
modInvestDialog.txtInvest.setText(str(invDetils))
if self.validate_date(date):
modInvestDialog.dateEdit.setDateTime(datetime.datetime.strptime(date,"%Y/%m/%d"))
modInvestDialog.txtAddress.setText(str(address))
modInvestDialog.txtComment.setText(str(remark))
modInvestDialog.show()
if(modInvestDialog.exec_() == 1):
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = modInvestDialog.txtName.text().strip()
invDetils = modInvestDialog.txtInvest.text().strip()
date = modInvestDialog.dateEdit.date().toString('yyyy/MM/dd')
address = modInvestDialog.txtAddress.text().strip()
remark = modInvestDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("UPDATE invest SET name = '{1}', invDetils = '{2}', date = '{3}', address = '{4}', remark = '{5}' WHERE id = {0}".format(id,name,invDetils,date,address,remark))
if modInvestDialog.dateEdit.date() < self.min_date:
self.min_date = modInvestDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modInvestDialog.dateEdit.date() > self.max_date:
self.max_date = modInvestDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","修改审计调查成功！",QMessageBox.Ok)
else:
if modInvestDialog.dateEdit.date() < self.min_date:
self.min_date = modInvestDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modInvestDialog.dateEdit.date() > self.max_date:
self.max_date = modInvestDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择审计调查记录，然后修改。",QMessageBox.Ok)
return
# 删除一行数据
@QtCore.pyqtSlot()
def del_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择审计调查记录，然后删除。",QMessageBox.Ok)
return
rows = set()
for idx in self.tableView.selectedIndexes():
record = self.queryModel.record(idx.row())
id = record.value("id")
rows.add(id)
if (QMessageBox.question(self, "XX公司审计信息统计平台","是否删除选中的审计调查?",QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
query = QSqlQuery()
for id in rows:
query.exec_("DELETE FROM invest WHERE id = {0}".format(id))
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择审计调查记录，然后删除。",QMessageBox.Ok)
return
def setButtonStatus(self):
if (self.currentPage == 1 and self.totalPage == 1):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(False)
elif (self.currentPage > 1 and self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
elif (self.currentPage == 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(True)
elif (self.currentPage > 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery(self.queryCondition2)
self.totalRecord = self.queryModel.rowCount()
return
# 得到总页数
def getPageCount(self):
self.getTotalRecordCount()
# 上取整
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
return
# 分页记录查询
def recordQuery(self, index):
queryCondition = ""
conditionChoice = self.condisionComboBox.currentText()
if (conditionChoice == "按审计员姓名查询"):
conditionChoice = 'name'
elif (conditionChoice == "按审计调查内容查询"):
conditionChoice = 'invDetils'
else:
conditionChoice = 'address'
if (self.searchEdit.text() == ""):
if self.rbAll.isChecked():
queryCondition = "SELECT * FROM invest"
else:
queryCondition = "SELECT * FROM invest WHERE date BETWEEN '%s' AND '%s'" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM invest ORDER BY id LIMIT %d,%d" % (index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM invest WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
else:
queryCondition = ("SELECT * FROM invest WHERE date BETWEEN '%s' AND '%s' ORDER BY id LIMIT %d,%d " % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM invest WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 得到模糊查询条件
temp = self.searchEdit.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM invest WHERE %s LIKE '%s'" % (conditionChoice,s))
else:
queryCondition = ("SELECT * FROM invest WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s'" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
# 当查询无记录时的操作
if (self.totalRecord == 0):
QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)
if self.rbAll.isChecked():
queryCondition = "SELECT * FROM invest"
else:
queryCondition = "SELECT * FROM invest WHERE date BETWEEN '%s' AND '%s'" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM invest ORDER BY id LIMIT %d,%d" %( index, self.pageRecord))
self.queryCondition2 = "SELECT * FROM invest ORDER BY id"
else:
queryCondition = ("SELECT * FROM invest WHERE date BETWEEN '%s' AND '%s' ORDER BY id LIMIT %d,%d" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM invest WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM invest WHERE %s LIKE '%s' ORDER BY id LIMIT %d,%d" % (conditionChoice, s, index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM invest WHERE %s LIKE '%s' ORDER BY id" % (conditionChoice,s))
else:
queryCondition = ("SELECT * FROM invest WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id LIMIT %d,%d" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM invest WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 点击查询
def searchButtonClicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击查询
def searchButtonClicked2(self):
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向前翻页
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向后翻页
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击跳转
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
# 打印数据
@QtCore.pyqtSlot()
def prt_row_data(self):
"""
这个函数就是告诉我们调用QPrintDialog准备进行打印了。
QPrintDialog类提供了一个用于指定打印机配置的对话框。对话框允许用户更改文档相关设置，如纸张尺寸和方向，打印类型（颜色或灰度），页面范围和打印份数。
还提供控制以使用户可以从可用的打印机中进行选择，包括任何配置的网络打印机。通常，QPrintDialog对象使用QPrinter对象构造，并使用exec()函数执行。
"""
printdialog = QPrintDialog(self.printer,self)
"""
在我们选择好打印机等等后，点击打印（即对话框被用户接受，则QPrinter对象被正确配置为打印），我们会调用QTextEdit中的print方法进行相关的打印
"""
if QDialog.Accepted == printdialog.exec_():
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
cur.execute(self.queryCondition2)
test = cur.fetchall()
template = Template("""
<table border="1" cellspacing="0" cellpadding="2">
<tr>
<th>审计员姓名</th>
<th>审计调查内容</th>
<th>时间</th>
<th>地点</th>
<th>备注</th>
</tr>
{% for row in test %}
<tr>
<td> {{ row[1] if row[1] != None }}</td>
<td max-width="50%"> {{ row[2] if row[2] != None }}</td>
<td> {{ row[3] if row[3] != None }}</td>
<td max-width="50%"> {{ row[4] if row[4] != None }}</td>
<td> {{ row[5] if row[5] != None }}</td>
</tr>
{% endfor %}
</table>
""")
text = template.render(test=test)
self.editor.setHtml(text)
self.editor.document().print_(printdialog.printer())
cur.close()
c.close()
QMessageBox.question(self, "XX公司审计信息统计平台","审计调查表格已经提交打印！",QMessageBox.Ok)
def prt_setup_data(self):
"""
QPageSetupDialog类为打印机上的页面相关选项提供了一个配置对话框。这个就必须使用到QPrinter对象了。
"""
printsetdialog = QPageSetupDialog(self.printer,self)
printsetdialog.exec_()#这句话就相当于我们执行确认的页面设置信息。
# 导入Excel文件
@QtCore.pyqtSlot()
def imp_excel_data(self):
fileName, filetype = QFileDialog.getOpenFileName(self,
"导入Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
if(self.readExcelFile(fileName)):
#设置日期最大值与最小值
self.min_date = QDate.currentDate()
self.max_date = QDate.currentDate()
query = QSqlQuery()
if not query.exec_('SELECT MIN(date) AS Min_Date,MAX(date) AS Max_Date from invest'):
query.lastError()
else:
QMessageBox.question(self, "XX公司审计信息统计平台","导入Excel文件成功！",QMessageBox.Ok)
while query.next():
self.min_date = QDate.fromString(query.value(0),'yyyy/MM/dd')
self.max_date = QDate.fromString(query.value(1),'yyyy/MM/dd')
self.dateStartEdit.setMinimumDate(self.min_date)
self.dateStartEdit.setMaximumDate(self.max_date)
self.dateEndEdit.setMinimumDate(self.min_date)
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked()
else:
QMessageBox.critical(self, "XX公司审计信息统计平台","Excel文件格式错误！",QMessageBox.Ok)
'''数据库插入操作'''
def insert(self,name,invDetils,date,address,remark):
sql = "insert into invest(name,invDetils,date,address,remark) values ('%s','%s','%s','%s','%s')" % (name,invDetils,date,address,remark)
self.cursor.execute(sql)
'''读取Excel文件'''
def readExcelFile(self, file):
data = xlrd.open_workbook(file)
for sheet in data.sheets():
if sheet.name == 'invest':
conn = sqlite3.connect('cmdb.sqlite')
self.cursor = conn.cursor()
for rowId in range(1, sheet.nrows):
row = sheet.row_values(rowId)
if row:
self.insert(row[1],row[2],row[3],row[4],row[5])
conn.commit()
self.cursor.close()
conn.close()
return True
return False
@QtCore.pyqtSlot()
def sqlite_get_col_names(self,cur, select_sql):
cur.execute(select_sql)
return [tuple[0] for tuple in cur.description]
@QtCore.pyqtSlot()
def query_by_sql(self,cur, select_sql):
cur.execute(select_sql)
return cur.fetchall()
# 获取字符串长度，一个中文的长度为2
def len_byte(self,value):
length = len(value)
utf8_length = len(value.encode('utf-8'))
length = (utf8_length - length) / 2 + length
return int(length)
@QtCore.pyqtSlot()
def sqlite_to_workbook_with_head(self,cur, table, select_sql, workbook,style):
ws = workbook.add_sheet(table)
#enumerate针对一个可迭代对象，生成的是序号加上内容
for colx, heading in enumerate(self.sqlite_get_col_names(cur, select_sql)):
ws.write(0, colx, self.queryModel.headerData(colx,Qt.Horizontal),style) #在第1行的colx列写上头部信息
#序号
id = 1
#确定栏位宽度
col_width = []
for rowy, row in enumerate(self.query_by_sql(cur, select_sql)):
for colx, text in enumerate(row): #row是一行的内容
t = id if colx == 0 else text
ws.write(rowy + 1,colx , t,style) #在rowy+1行，colx写入数据库内容text
if rowy == 0:
col_width.append(self.len_byte(str(t)))
elif col_width[colx] < self.len_byte(str(t)):
col_width[colx] = self.len_byte(str(t))
id+=1
#设置栏位宽度，栏位宽度小于10时候采用默认宽度
for i in range(len(col_width)):
if col_width[i] > 10:
ws.col(i).width = 256 * col_width[i]
# 导出Excel文件
@QtCore.pyqtSlot()
def xpt_excel_data(self):
fileName, filetype = QFileDialog.getSaveFileName(self,
"导出Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
select_sql = self.queryCondition2
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style1 = xlwt.XFStyle()
style1.borders = borders
workbook = xlwt.Workbook(encoding='utf-8') #Workbook()
self.sqlite_to_workbook_with_head(cur, 'invest', select_sql, workbook,style1)
cur.close()
c.close()
workbook.save(fileName)
QMessageBox.question(self, "XX公司审计信息统计平台","导出Excel文件成功！",QMessageBox.Ok)③、整改意见模块
整改意见界面主体框架是QWidget，整改意见模块主要是通过SQLite数据库的instruct表用来存储整改检查记录，整改检查模块支持查询、新增记录、修改记录、删除记录、分页、导入导出Excel、打印功能。
#验证日期格式
def validate_date(self,date_text):
try:
datetime.datetime.strptime(str(date_text), '%Y/%m/%d')
except ValueError:
return False
return True
# 添加一行数据行
@QtCore.pyqtSlot()
def add_row_data(self):
model = self.tableView.model()
id = model.rowCount()
addInstructDialog = addDialog()
addInstructDialog.show()
if(addInstructDialog.exec_() == 1):
name = addInstructDialog.txtName.text().strip()
instruct = addInstructDialog.txtInstruct.text().strip()
date = addInstructDialog.dateEdit.date().toString('yyyy/MM/dd')
docName = addInstructDialog.txtDocName.text().strip()
docNo = addInstructDialog.txtDocNo.text().strip()
performance = addInstructDialog.txtPerformance.text().strip()
remark = addInstructDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("INSERT INTO instruct(name,instrDetils,date,docName,docNo,performance,remark) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}')".format(name,instruct,date,docName,docNo,performance,remark))
if addInstructDialog.dateEdit.date() < self.min_date:
self.min_date = addInstructDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addInstructDialog.dateEdit.date() > self.max_date:
self.max_date = addInstructDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","新增整改意见成功！",QMessageBox.Ok)
else:
if addInstructDialog.dateEdit.date() < self.min_date:
self.min_date = addInstructDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addInstructDialog.dateEdit.date() > self.max_date:
self.max_date = addInstructDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
# 修改数据行
@QtCore.pyqtSlot()
def mod_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择整改意见记录，然后修改。",QMessageBox.Ok)
return
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = self.queryModel.record(rows[0].row()).value("name")
instruct = self.queryModel.record(rows[0].row()).value("instrDetils")
date = self.queryModel.record(rows[0].row()).value("date")
docName = self.queryModel.record(rows[0].row()).value("docName")
docNo = self.queryModel.record(rows[0].row()).value("docNo")
performance = self.queryModel.record(rows[0].row()).value("performance")
remark = self.queryModel.record(rows[0].row()).value("remark")
modInstructDialog = modDialog()
modInstructDialog.id = id
modInstructDialog.txtName.setText(str(name))
modInstructDialog.txtInstruct.setText(str(instruct))
if self.validate_date(date):
modInstructDialog.dateEdit.setDateTime(datetime.datetime.strptime(date,"%Y/%m/%d"))
modInstructDialog.txtDocName.setText(str(docName))
modInstructDialog.txtDocNo.setText(str(docNo))
modInstructDialog.txtPerformance.setText(str(performance))
modInstructDialog.txtComment.setText(str(remark))
modInstructDialog.show()
if(modInstructDialog.exec_() == 1):
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = modInstructDialog.txtName.text().strip()
instruct = modInstructDialog.txtInstruct.text().strip()
date = modInstructDialog.dateEdit.date().toString('yyyy/MM/dd')
docName = modInstructDialog.txtDocName.text().strip()
docNo = modInstructDialog.txtDocNo.text().strip()
performance = modInstructDialog.txtPerformance.text().strip()
remark = modInstructDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("UPDATE instruct SET name = '{1}', instrDetils = '{2}', date = '{3}', docName = '{4}', docNo = '{5}', performance = '{6}', remark = '{7}' WHERE id = {0}".format(id,name,instruct,date,docName,docNo,performance,remark))
if modInstructDialog.dateEdit.date() < self.min_date:
self.min_date = modInstructDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modInstructDialog.dateEdit.date() > self.max_date:
self.max_date = modInstructDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","修改整改意见成功！",QMessageBox.Ok)
else:
if modInstructDialog.dateEdit.date() < self.min_date:
self.min_date = modInstructDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modInstructDialog.dateEdit.date() > self.max_date:
self.max_date = modInstructDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self,
"XX公司审计信息统计平台","请选择整改意见记录，然后修改。",QMessageBox.Ok)
return
# 删除一行数据
@QtCore.pyqtSlot()
def del_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择整改意见记录，然后删除。",QMessageBox.Ok)
return
rows = set()
for idx in self.tableView.selectedIndexes():
record = self.queryModel.record(idx.row())
id = record.value("id")
rows.add(id)
if (QMessageBox.question(self, "XX公司审计信息统计平台","是否删除选中的整改意见?",QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
query = QSqlQuery()
for id in rows:
query.exec_("DELETE FROM instruct WHERE id = {0}".format(id))
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择整改意见记录，然后删除。",QMessageBox.Ok)
return
def setButtonStatus(self):
if (self.currentPage == 1 and self.totalPage == 1):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(False)
elif (self.currentPage > 1 and self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
elif (self.currentPage == 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(True)
elif (self.currentPage > 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery(self.queryCondition2)
self.totalRecord = self.queryModel.rowCount()
return
# 得到总页数
def getPageCount(self):
self.getTotalRecordCount()
# 上取整
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
return
# 分页记录查询
def recordQuery(self, index):
queryCondition = ""
conditionChoice = self.condisionComboBox.currentText()
if (conditionChoice == "按审计员查询"):
conditionChoice = 'name'
elif (conditionChoice == "按整改意见内容查询"):
conditionChoice = 'instrDetils'
elif (conditionChoice == '按文档名查询'):
conditionChoice = 'docName'
elif (conditionChoice == '按文档编号查询'):
conditionChoice = 'docNo'
else:
conditionChoice = 'performance'
if (self.searchEdit.text() == ""):
if self.rbAll.isChecked():
queryCondition = "select 1 from instruct"
else:
self.queryCondition = "SELECT * FROM instruct WHERE date BETWEEN '%s' AND '%s'" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM instruct ORDER BY id limit %d,%d " % (index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM instruct WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
else:
queryCondition = ("SELECT * FROM instruct WHERE date BETWEEN '%s' AND '%s' ORDER BY id limit %d,%d " % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM instruct WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 得到模糊查询条件
temp = self.searchEdit.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM instruct WHERE %s LIKE '%s'" % (conditionChoice,s))
else:
queryCondition = ("SELECT * FROM instruct WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s'" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
# 当查询无记录时的操作
if (self.totalRecord == 0):
QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)
if self.rbAll.isChecked():
queryCondition = "SELECT * FROM instruct"
else:
queryCondition = "SELECT * FROM instruct WHERE date BETWEEN '%s' AND '%s'" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM instruct ORDER BY id limit %d,%d " % (index, self.pageRecord))
self.queryCondition2 = "SELECT * FROM instruct ORDER BY id"
else:
queryCondition = ("SELECT * FROM instruct WHERE date BETWEEN '%s' AND '%s' ORDER BY id limit %d,%d " % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM instruct WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM instruct WHERE %s LIKE '%s' ORDER BY id LIMIT %d,%d " % (conditionChoice, s, index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM instruct WHERE %s LIKE '%s' ORDER BY id" % (conditionChoice,s))
else:
queryCondition = ("SELECT * FROM instruct WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id LIMIT %d,%d " % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM instruct WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 点击查询
def searchButtonClicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击查询
def searchButtonClicked2(self):
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向前翻页
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向后翻页
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击跳转
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
# 打印数据
@QtCore.pyqtSlot()
def prt_row_data(self):
"""
这个函数就是告诉我们调用QPrintDialog准备进行打印了。
QPrintDialog类提供了一个用于指定打印机配置的对话框。对话框允许用户更改文档相关设置，如纸张尺寸和方向，打印类型（颜色或灰度），页面范围和打印份数。
还提供控制以使用户可以从可用的打印机中进行选择，包括任何配置的网络打印机。通常，QPrintDialog对象使用QPrinter对象构造，并使用exec()函数执行。
"""
printdialog = QPrintDialog(self.printer,self)
"""
在我们选择好打印机等等后，点击打印（即对话框被用户接受，则QPrinter对象被正确配置为打印），我们会调用QTextEdit中的print方法进行相关的打印
"""
if QDialog.Accepted == printdialog.exec_():
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
cur.execute(self.queryCondition2)
test = cur.fetchall()
template = Template("""
<table border="1" cellspacing="0" cellpadding="2">
<tr>
<th>审计员</th>
<th>整改意见内容</th>
<th>整改时间</th>
<th>文档名</th>
<th>文档编号</th>
<th>整改情况</th>
<th>备注</th>
</tr>
{% for row in test %}
<tr>
<td> {{ row[1] if row[1] != None }}</td>
<td max-width="50%"> {{ row[2] if row[2] != None }}</td>
<td> {{ row[3] if row[3] != None }}</td>
<td max-width="50%"> {{ row[4] if row[4] != None }}</td>
<td> {{ row[5] if row[5] != None }}</td>
<td> {{ row[6] if row[6] != None }}</td>
</tr>
{% endfor %}
</table>
""")
text = template.render(test=test)
self.editor.setHtml(text)
self.editor.document().print_(printdialog.printer())
cur.close()
c.close()
QMessageBox.question(self, "XX公司审计信息统计平台","整改意见表格已经提交打印！",QMessageBox.Ok)
def prt_setup_data(self):
"""
QPageSetupDialog类为打印机上的页面相关选项提供了一个配置对话框。这个就必须使用到QPrinter对象了。
"""
printsetdialog = QPageSetupDialog(self.printer,self)
printsetdialog.exec_()#这句话就相当于我们执行确认的页面设置信息。
# 导入Excel文件
@QtCore.pyqtSlot()
def imp_excel_data(self):
fileName, filetype = QFileDialog.getOpenFileName(self,
"导入Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
if(self.readExcelFile(fileName)):
#设置日期最大值与最小值
self.min_date = QDate.currentDate()
self.max_date = QDate.currentDate()
query = QSqlQuery()
if not query.exec_('SELECT MIN(date) AS Min_Date,MAX(date) AS Max_Date from instruct'):
query.lastError()
else:
QMessageBox.question(self, "XX公司审计信息统计平台","导入Excel文件成功！",QMessageBox.Ok)
while query.next():
self.min_date = QDate.fromString(query.value(0),'yyyy/MM/dd')
self.max_date = QDate.fromString(query.value(1),'yyyy/MM/dd')
self.dateStartEdit.setMinimumDate(self.min_date)
self.dateStartEdit.setMaximumDate(self.max_date)
self.dateEndEdit.setMinimumDate(self.min_date)
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked()
else:
QMessageBox.critical(self, "XX公司审计信息统计平台","Excel文件格式错误！",QMessageBox.Ok)
'''数据库插入操作'''
def insert(self,name,instrDetils,date,docName,docNo,performance,remark):
sql = "insert into instruct(id,name,instrDetils,date,docName,docNo,performance,remark) SELECT MAX(id)+1, '%s','%s','%s','%s','%s','%s','%s' FROM instruct" % (name,instrDetils,date,docName,docNo,performance,remark)
self.cursor.execute(sql)
'''读取Excel文件'''
def readExcelFile(self, file):
data = xlrd.open_workbook(file)
for sheet in data.sheets():
if sheet.name == 'instruct':
conn = sqlite3.connect('cmdb.sqlite')
self.cursor = conn.cursor()
for rowId in range(1, sheet.nrows):
row = sheet.row_values(rowId)
if row:
self.insert(row[1],row[2],row[3],row[4],row[5],row[6],row[7])
conn.commit()
self.cursor.close()
conn.close()
return True
return False
@QtCore.pyqtSlot()
def sqlite_get_col_names(self,cur, select_sql):
cur.execute(select_sql)
return [tuple[0] for tuple in cur.description]
@QtCore.pyqtSlot()
def query_by_sql(self,cur, select_sql):
cur.execute(select_sql)
return cur.fetchall()
# 获取字符串长度，一个中文的长度为2
def len_byte(self,value):
length = len(value)
utf8_length = len(value.encode('utf-8'))
length = (utf8_length - length) / 2 + length
return int(length)
@QtCore.pyqtSlot()
def sqlite_to_workbook_with_head(self,cur, table, select_sql, workbook,style):
ws = workbook.add_sheet(table)
#enumerate针对一个可迭代对象，生成的是序号加上内容
for colx, heading in enumerate(self.sqlite_get_col_names(cur, select_sql)):
ws.write(0, colx, self.queryModel.headerData(colx,Qt.Horizontal),style) #在第1行的colx列写上头部信息
#序号
id = 1
#确定栏位宽度
col_width = []
for rowy, row in enumerate(self.query_by_sql(cur, select_sql)):
for colx, text in enumerate(row): #row是一行的内容
t = id if colx == 0 else text
ws.write(rowy + 1,colx , t,style) #在rowy+1行，colx写入数据库内容text
if rowy == 0:
col_width.append(self.len_byte(str(t)))
elif col_width[colx] < self.len_byte(str(t)):
col_width[colx] = self.len_byte(str(t))
id+=1
#设置栏位宽度，栏位宽度小于10时候采用默认宽度
for i in range(len(col_width)):
if col_width[i] > 10:
ws.col(i).width = 256 * col_width[i]
# 导出Excel文件
@QtCore.pyqtSlot()
def xpt_excel_data(self):
fileName, filetype = QFileDialog.getSaveFileName(self,
"导出Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
select_sql = self.queryCondition2
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style1 = xlwt.XFStyle()
style1.borders = borders
workbook = xlwt.Workbook(encoding='utf-8') #Workbook()
self.sqlite_to_workbook_with_head(cur, 'instruct', select_sql, workbook,style1)
cur.close()
c.close()
workbook.save(fileName)
QMessageBox.question(self, "XX公司审计信息统计平台","导出Excel文件成功！",QMessageBox.Ok)④、整改检查模块
整改检查界面主体框架是QWidget，整改检查模块主要是通过SQLite数据库的inspect表用来存储整改检查记录，整改检查模块支持查询、新增记录、修改记录、删除记录、分页、导入导出Excel、打印功能。
#验证日期格式
def validate_date(self,date_text):
try:
datetime.datetime.strptime(str(date_text), '%Y/%m/%d')
except ValueError:
return False
return True
# 添加一行数据行
@QtCore.pyqtSlot()
def add_row_data(self):
model = self.tableView.model()
id = model.rowCount()
addInspectDialog = addDialog()
addInspectDialog.show()
if(addInspectDialog.exec_() == 1):
name = addInspectDialog.txtName.text().strip()
inspDetils = addInspectDialog.txtInspect.text().strip()
date = addInspectDialog.dateEdit.date().toString('yyyy/MM/dd')
address = addInspectDialog.txtAddress.text().strip()
joinDep = addInspectDialog.txtWithPeople.text().strip()
remark = addInspectDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("INSERT INTO inspect(id,name,inspDetils,date,address,joinDep,remark) SELECT MAX(id) + 1,'{0}','{1}','{2}','{3}','{4}','{5}' FROM inspect".format(name,inspDetils,date,address,joinDep,remark))
if addInspectDialog.dateEdit.date() < self.min_date:
self.min_date = addInspectDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addInspectDialog.dateEdit.date() > self.max_date:
self.max_date = addInspectDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","新增检查成功！",QMessageBox.Ok)
else:
if addInspectDialog.dateEdit.date() < self.min_date:
self.min_date = addInspectDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if addInspectDialog.dateEdit.date() > self.max_date:
self.max_date = addInspectDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
# 修改数据行
@QtCore.pyqtSlot()
def mod_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择检查记录，然后修改。",QMessageBox.Ok)
return
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = self.queryModel.record(rows[0].row()).value("name")
inspect = self.queryModel.record(rows[0].row()).value("inspDetils")
date = self.queryModel.record(rows[0].row()).value("date")
address = self.queryModel.record(rows[0].row()).value("address")
joinDep = self.queryModel.record(rows[0].row()).value("joinDep")
remark = self.queryModel.record(rows[0].row()).value("remark")
modInspectDialog = modDialog()
modInspectDialog.id = id
modInspectDialog.txtName.setText(str(name))
modInspectDialog.txtInspect.setText(str(inspect))
if self.validate_date(date):
modInspectDialog.dateEdit.setDateTime(datetime.datetime.strptime(date,"%Y/%m/%d"))
modInspectDialog.txtAddress.setText(str(address))
modInspectDialog.txtWithPeople.setText(str(joinDep))
modInspectDialog.txtComment.setText(str(remark))
modInspectDialog.show()
if(modInspectDialog.exec_() == 1):
rows = self.tableView.selectionModel().selectedIndexes()
id = self.queryModel.record(rows[0].row()).value("id")
name = modInspectDialog.txtName.text().strip()
inspect = modInspectDialog.txtInspect.text().strip()
date = modInspectDialog.dateEdit.date().toString('yyyy/MM/dd')
address = modInspectDialog.txtAddress.text().strip()
joinDep = modInspectDialog.txtWithPeople.text().strip()
remark = modInspectDialog.txtComment.text().strip()
if name != "":
query = QSqlQuery()
query.exec_("UPDATE inspect SET name = '{1}', inspDetils = '{2}', date = '{3}', address = '{4}', joinDep = '{5}', remark = '{6}' WHERE id = {0}".format(id,name,inspect,date,address,joinDep,remark))
if modInspectDialog.dateEdit.date() < self.min_date:
self.min_date = modInspectDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modInspectDialog.dateEdit.date() > self.max_date:
self.max_date = modInspectDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
QMessageBox.question(self, "XX公司审计信息统计平台","修改检查成功！",QMessageBox.Ok)
else:
if modInspectDialog.dateEdit.date() < self.min_date:
self.min_date = modInspectDialog.dateEdit.date()
self.dateStartEdit.setMinimumDate(self.min_date)
if modInspectDialog.dateEdit.date() > self.max_date:
self.max_date = modInspectDialog.dateEdit.date()
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择检查记录，然后修改。",QMessageBox.Ok)
return
# 删除一行数据
@QtCore.pyqtSlot()
def del_row_data(self):
try:
index = self.tableView.currentIndex()
if not index.isValid():
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择检查记录，然后删除。",QMessageBox.Ok)
return
rows = set()
for idx in self.tableView.selectedIndexes():
record = self.queryModel.record(idx.row())
id = record.value("id")
rows.add(id)
if (QMessageBox.question(self, "XX公司审计信息统计平台","是否删除选中的检查?",QMessageBox.Yes | QMessageBox.No) == QMessageBox.No):
return
query = QSqlQuery()
for id in rows:
query.exec_("DELETE FROM inspect WHERE id = {0}".format(id))
self.searchButtonClicked2()
except Exception as e:
QMessageBox.critical(self, "XX公司审计信息统计平台","请选择检查记录，然后删除。",QMessageBox.Ok)
return
def setButtonStatus(self):
if (self.currentPage == 1 and self.totalPage == 1):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(False)
elif (self.currentPage > 1 and self.currentPage == self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(False)
elif (self.currentPage == 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(False)
self.backButton.setEnabled(True)
elif (self.currentPage > 1 and self.currentPage < self.totalPage):
self.prevButton.setEnabled(True)
self.backButton.setEnabled(True)
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery(self.queryCondition2)
self.totalRecord = self.queryModel.rowCount()
return
# 得到总页数
def getPageCount(self):
self.getTotalRecordCount()
# 上取整
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
return
# 分页记录查询
def recordQuery(self, index):
queryCondition = ""
conditionChoice = self.condisionComboBox.currentText()
if (conditionChoice == "按审计员姓名查询"):
conditionChoice = 'name'
elif (conditionChoice == "按整改检查内容查询"):
conditionChoice = 'inspectDetils'
elif (conditionChoice == '按地点查询'):
conditionChoice = 'address'
else:
conditionChoice = 'joinDep'
if (self.searchEdit.text() == ""):
if self.rbAll.isChecked():
queryCondition = "SELECT * FROM inspect"
else:
queryCondition = "SELECT * FROM inspect WHERE date BETWEEN '%s' AND '%s'" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM inspect ORDER BY id limit %d,%d " % (index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM inspect WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
else:
queryCondition = ("SELECT * FROM inspect WHERE date BETWEEN '%s' AND '%s' ORDER BY id limit %d,%d " % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM inspect WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 得到模糊查询条件
temp = self.searchEdit.text()
s = '%'
for i in range(0, len(temp)):
s = s + temp[i] + "%"
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM inspect WHERE %s LIKE '%s' ORDER BY id" % (conditionChoice,s))
else:
queryCondition = ("SELECT * FROM inspect WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
# 当查询无记录时的操作
if (self.totalRecord == 0):
QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)
if self.rbAll.isChecked():
queryCondition = "SELECT * FROM inspect ORDER BY id"
else:
queryCondition = "SELECT * FROM inspect WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'))
self.queryModel.setQuery(queryCondition)
self.totalRecord = self.queryModel.rowCount()
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM inspect ORDER BY id limit %d,%d " % (index, self.pageRecord))
self.queryCondition2 = "SELECT * FROM inspect ORDER BY id"
else:
queryCondition = ("SELECT * FROM inspect WHERE date BETWEEN '%s' AND '%s' ORDER BY id limit %d,%d " % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM inspect WHERE date BETWEEN '%s' AND '%s' ORDER BY id" % (self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
self.totalPage = int((self.totalRecord + self.pageRecord - 1) / self.pageRecord)
label = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(label)
if self.rbAll.isChecked():
queryCondition = ("SELECT * FROM inspect WHERE %s LIKE '%s' ORDER BY id LIMIT %d,%d " % (conditionChoice, s, index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM inspect WHERE %s LIKE '%s' ORDER BY id" % (conditionChoice,s))
else:
queryCondition = ("SELECT * FROM inspect WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id LIMIT %d,%d " % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd'), index, self.pageRecord))
self.queryCondition2 = ("SELECT * FROM inspect WHERE %s LIKE '%s' AND date BETWEEN '%s' AND '%s' ORDER BY id" % (conditionChoice, s,self.dateStartEdit.dateTime().toString('yyyy/MM/dd'),self.dateEndEdit.dateTime().toString('yyyy/MM/dd')))
self.queryModel.setQuery(queryCondition)
self.setButtonStatus()
return
# 点击查询
def searchButtonClicked(self):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击查询
def searchButtonClicked2(self):
self.pageEdit.setText(str(self.currentPage))
self.getPageCount()
s = "/" + str(int(self.totalPage)) + "页"
self.pageLabel.setText(s)
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向前翻页
def prevButtonClicked(self):
self.currentPage -= 1
if (self.currentPage <= 1):
self.currentPage = 1
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 向后翻页
def backButtonClicked(self):
self.currentPage += 1
if (self.currentPage >= int(self.totalPage)):
self.currentPage = int(self.totalPage)
self.pageEdit.setText(str(self.currentPage))
index = (self.currentPage - 1) * self.pageRecord
self.recordQuery(index)
return
# 点击跳转
def jumpToButtonClicked(self):
if (self.pageEdit.text().isdigit()):
self.currentPage = int(self.pageEdit.text())
if (self.currentPage > self.totalPage):
self.currentPage = self.totalPage
if (self.currentPage <= 1):
self.currentPage = 1
else:
self.currentPage = 1
index = (self.currentPage - 1) * self.pageRecord
self.pageEdit.setText(str(self.currentPage))
self.recordQuery(index)
return
# 打印数据
@QtCore.pyqtSlot()
def prt_row_data(self):
"""
这个函数就是告诉我们调用QPrintDialog准备进行打印了。
QPrintDialog类提供了一个用于指定打印机配置的对话框。对话框允许用户更改文档相关设置，如纸张尺寸和方向，打印类型（颜色或灰度），页面范围和打印份数。
还提供控制以使用户可以从可用的打印机中进行选择，包括任何配置的网络打印机。通常，QPrintDialog对象使用QPrinter对象构造，并使用exec()函数执行。
"""
printdialog = QPrintDialog(self.printer,self)
"""
在我们选择好打印机等等后，点击打印（即对话框被用户接受，则QPrinter对象被正确配置为打印），我们会调用QTextEdit中的print方法进行相关的打印
"""
if QDialog.Accepted == printdialog.exec_():
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
cur.execute(self.queryCondition2)
test = cur.fetchall()
template = Template("""
<table border="1" cellspacing="0" cellpadding="2">
<tr>
<th>审计员姓名</th>
<th>整改检查内容</th>
<th>时间</th>
<th>地点</th>
<th>部门相关人员</th>
<th>备注</th>
</tr>
{% for row in test %}
<tr>
<td> {{ row[1] if row[1] != None }}</td>
<td max-width="50%"> {{ row[2] if row[2] != None }}</td>
<td> {{ row[3] if row[3] != None }}</td>
<td max-width="50%"> {{ row[4] if row[4] != None }}</td>
<td> {{ row[5] if row[5] != None }}</td>
<td> {{ row[6] if row[6] != None }}</td>
</tr>
{% endfor %}
</table>
""")
text = template.render(test=test)
self.editor.setHtml(text)
self.editor.document().print_(printdialog.printer())
cur.close()
c.close()
QMessageBox.question(self, "XX公司审计信息统计平台","整改检查表格已经提交打印！",QMessageBox.Ok)
def prt_setup_data(self):
"""
QPageSetupDialog类为打印机上的页面相关选项提供了一个配置对话框。这个就必须使用到QPrinter对象了。
"""
printsetdialog = QPageSetupDialog(self.printer,self)
printsetdialog.exec_()#这句话就相当于我们执行确认的页面设置信息。
# 导入Excel文件
@QtCore.pyqtSlot()
def imp_excel_data(self):
fileName, filetype = QFileDialog.getOpenFileName(self,
"导入Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
if(self.readExcelFile(fileName)):
#设置日期最大值与最小值
self.min_date = QDate.currentDate()
self.max_date = QDate.currentDate()
query = QSqlQuery()
if not query.exec_('SELECT MIN(date) AS Min_Date,MAX(date) AS Max_Date from inspect'):
query.lastError()
else:
QMessageBox.question(self, "XX公司审计信息统计平台","导入Excel文件成功！",QMessageBox.Ok)
while query.next():
self.min_date = QDate.fromString(query.value(0),'yyyy/MM/dd')
self.max_date = QDate.fromString(query.value(1),'yyyy/MM/dd')
self.dateStartEdit.setMinimumDate(self.min_date)
self.dateStartEdit.setMaximumDate(self.max_date)
self.dateEndEdit.setMinimumDate(self.min_date)
self.dateEndEdit.setMaximumDate(self.max_date)
self.searchButtonClicked()
else:
QMessageBox.critical(self, "XX公司审计信息统计平台","Excel文件格式错误！",QMessageBox.Ok)
'''数据库插入操作'''
def insert(self,name,inspDetils,date,address,joinDep,remark):
sql = "insert into inspect(name,inspDetils,date,address,joinDep,remark) values ('%s','%s','%s','%s','%s','%s')" % (name,inspDetils,date,address,joinDep,remark)
self.cursor.execute(sql)
'''读取Excel文件'''
def readExcelFile(self, file):
data = xlrd.open_workbook(file)
for sheet in data.sheets():
if sheet.name == 'inspect':
conn = sqlite3.connect('cmdb.sqlite')
self.cursor = conn.cursor()
for rowId in range(1, sheet.nrows):
row = sheet.row_values(rowId)
if row:
self.insert(row[1],row[2],row[3],row[4],row[5],row[6])
conn.commit()
self.cursor.close()
conn.close()
return True
return False
@QtCore.pyqtSlot()
def sqlite_get_col_names(self,cur, select_sql):
cur.execute(select_sql)
return [tuple[0] for tuple in cur.description]
@QtCore.pyqtSlot()
def query_by_sql(self,cur, select_sql):
cur.execute(select_sql)
return cur.fetchall()
# 获取字符串长度，一个中文的长度为2
def len_byte(self,value):
length = len(value)
utf8_length = len(value.encode('utf-8'))
length = (utf8_length - length) / 2 + length
return int(length)
@QtCore.pyqtSlot()
def sqlite_to_workbook_with_head(self,cur, table, select_sql, workbook,style):
ws = workbook.add_sheet(table)
#enumerate针对一个可迭代对象，生成的是序号加上内容
for colx, heading in enumerate(self.sqlite_get_col_names(cur, select_sql)):
ws.write(0, colx, self.queryModel.headerData(colx,Qt.Horizontal),style) #在第1行的colx列写上头部信息
#序号
id = 1
#确定栏位宽度
col_width = []
for rowy, row in enumerate(self.query_by_sql(cur, select_sql)):
for colx, text in enumerate(row): #row是一行的内容
t = id if colx == 0 else text
ws.write(rowy + 1,colx , t,style) #在rowy+1行，colx写入数据库内容text
if rowy == 0:
col_width.append(self.len_byte(str(t)))
elif col_width[colx] < self.len_byte(str(t)):
col_width[colx] = self.len_byte(str(t))
id+=1
#设置栏位宽度，栏位宽度小于10时候采用默认宽度
for i in range(len(col_width)):
if col_width[i] > 10:
ws.col(i).width = 256 * col_width[i]
# 导出Excel文件
@QtCore.pyqtSlot()
def xpt_excel_data(self):
fileName, filetype = QFileDialog.getSaveFileName(self,
"导出Excel文件",
"./",
"Excel Files (*.xls)") #设置文件扩展名过滤,注意用双分号间隔
if fileName != "":
c = sqlite3.connect('cmdb.sqlite')
cur = c.cursor()
select_sql = self.queryCondition2
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style1 = xlwt.XFStyle()
style1.borders = borders
workbook = xlwt.Workbook(encoding='utf-8')
self.sqlite_to_workbook_with_head(cur, 'inspect', select_sql, workbook,style1)
cur.close()
c.close()
workbook.save(fileName)
QMessageBox.question(self, "XX公司审计信息统计平台","导出Excel文件成功！",QMessageBox.Ok)⑤、聊天模块
登录模块主要是通过SQLite数据库的user表用来存储用户的帐号和密码，读取和存入都很方便。
def word_get(self):
login_user = self.lineUserEdit.text()
login_password = self.linePasswordEdit.text()
query = QSqlQuery()
query.prepare("SELECT count(username) FROM users where username=? and password=?")
query.addBindValue(login_user)
query.addBindValue(login_password)
query.exec_()
query.next()
count = query.value(0)
if count == 1:
QMessageBox.information(self, '提示：', "登录成功!")
ui_main.show()
MainWindow.close()
else:
QMessageBox.information(self, '提示：', "用户名或者密码错误!")
self.lineUserEdit.setFocus()
self.lineUserEdit.clear()
self.linePasswordEdit.clear()三、项目结构图
四、补充
程序从userLoginDialog.py文件的Main函数启动。
本实例支付的费用只是购买源码的费用，如有疑问欢迎在文末留言交流，如需作者在线代码指导、定制等，在作者开启付费服务后，可以点击“购买服务”进行实时联系，请知悉，谢谢
手机上随时阅读、收藏该文章 ？请扫下方二维码
