实现效果
from PyQt5 import QtCore, QtWidgets
from roside_order_test import order_check2
import sys
import pymysql
class MyClass(QtWidgets.QMainWindow, order_check2.Ui_MainWindow):
def __init__(self):
super().__init__()
self.InitUi()
#self.My_Sql()
self.tablename={
"order_id": "订单编号",
"enterprise_id": "商家 id",
"park_id": "停车编号",
"start_time": "驶入时间",
"end_time": "驶出时间",
"residence_time": "停留时间(分钟)",
"advance": "预缴金额",
"alipay": "支付宝(单位分)",
"advance_time":"提前支付时间",
"backpay_time": "补缴时间",
"change": "零钱",
"coin": "停车币",
"cash": "现金",
"coupon_id": "优惠券id",
"coupon": "优惠券",
"card_id": "停车卡id",
"card": "停车卡(单位分)",
"card_money": "停车卡(单位分)",
"payment": "应缴金额",
"refund": "退款零钱",
"refund2": "实际退款",
"discount": "优惠金额",
"auditing_discount": "审核优惠",
"recover_discount": "补缴优惠",
"is_discount": "申请优惠 1是",
"discount2": "支付折扣金额(单位分)",
"discount_no": "支付折扣id",
"discount_expire": "支付折扣过期时间",
"pay_sum": "折扣金额对应的支付金额",
"pay_type": "折扣金额对应的支付方式",
"car_no": "车牌号码",
"car_url": "车牌图片",
"rent": "月租车 1是",
"new_energy": "新能源 1是",
"charge_count": "优惠限制",
"order_source": "1=手动创建",
"order_status": "订单状态 0=空 1=待结算 2=已结算 3=逃欠费 4=空 5=空 6=免费 7=异常",
"area_id": "区域 id",
"create_id": "新建人",
"create_name": "新建人名字",
"update_id": "置为异常的人",
"update_name": "置为异常队人",
"is_free": "是否免费(0付费1免费)",
"creator": "创建者",
"user_id": "支付者 id",
"pay_id": "支付 id",
"area_name": "地区名称",
"mode": "路段经营方式(1:自营, 2:代运营, 3:承包...)",
"create_date":"创建时间",
"update_date": "更新时间"
}
def InitUi(self):
self.setupUi(self)
self.retranslateUi(self)
self.setWindowTitle("路内订单数据查询工具")
self.show()
# self.pushButton.clicked.connect(self.fun)
# self.pushButton_2.clicked.connect(self.fun2)
# self.pushButton_3.clicked.connect(self.fun3)
# self.pushButton_2.clicked.connect(self.buttonclicked)
# self.pushButton.clicked.connect(self.buttonclicked2)
self.pushButton.clicked.connect(self.car_nomb)
self._translate = QtCore.QCoreApplication.translate
def Table_Data(self, i, j, data):
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setItem(i, j, item)
item = self.tableWidget.item(i, j)
item.setText(self._translate("Form", str(data)))
def car_nomb(self):
if self.lineEdit.text() == "" and self.lineEdit_2.text() != "" and self.comboBox.currentText()=='全部':
self.My_Sql2(self.lineEdit_2.text())
elif self.lineEdit.text() == "" and self.lineEdit_2.text()== "" and self.comboBox.currentText()=='全部':
self.My_Sql3()
elif self.lineEdit.text() != "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '全部':
self.My_Sql(self.lineEdit.text())
elif self.lineEdit.text() != "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '待结算':
self.My_Sql4(self.lineEdit.text(),1)
elif self.lineEdit.text() != "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '已结算':
self.My_Sql4(self.lineEdit.text(), 2)
elif self.lineEdit.text() != "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '逃欠费':
self.My_Sql4(self.lineEdit.text(), 3)
elif self.lineEdit.text() != "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '免费':
self.My_Sql4(self.lineEdit.text(), 6)
elif self.lineEdit.text() != "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '异常':
self.My_Sql4(self.lineEdit.text(), 7)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() != "" and self.comboBox.currentText() == '全部':
self.My_Sql2(self.lineEdit_2.text())
elif self.lineEdit.text() == "" and self.lineEdit_2.text() != "" and self.comboBox.currentText() == '待结算':
self.My_Sql5(self.lineEdit_2.text(),1)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() != "" and self.comboBox.currentText() == '已结算':
self.My_Sql5(self.lineEdit_2.text(), 2)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() != "" and self.comboBox.currentText() == '逃欠费':
self.My_Sql5(self.lineEdit_2.text(), 3)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() != "" and self.comboBox.currentText() == '免费':
self.My_Sql5(self.lineEdit_2.text(), 6)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() != "" and self.comboBox.currentText() == '异常':
self.My_Sql5(self.lineEdit_2.text(), 7)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '待结算':
self.My_Sql6(1)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '已结算':
self.My_Sql6(2)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '逃欠费':
self.My_Sql6( 3)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '免费':
self.My_Sql6(6)
elif self.lineEdit.text() == "" and self.lineEdit_2.text() == "" and self.comboBox.currentText() == '异常':
self.My_Sql6(7)
else:
pass
def My_Sql(self,car_nom): # 连接mysql数据库
connection = pymysql.connect(host="223.98.100.194", port=13306, user='root', password="test@2020", db="rosid")
print('successfully connect')
cur = connection.cursor()
sql = f'SELECT *FROM t_roside_order WHERE car_no="{car_nom}"'
print(sql)
cur.execute(sql) # 将数据从数据库中拿出来
total = cur.fetchall()
connection.close()
col_result = cur.description
print(col_result)
self.row = cur.rowcount # 取得记录个数,用于设置表格的行数
self.vol = len(total[0]) # 取得字段数,用于设置表格的列数
col_result = list(col_result)
a = 0
self.tableWidget.setColumnCount(self.vol)
self.tableWidget.setRowCount(self.row)
for i in col_result: # 设置表头信息,将mysql数据表中的表头信息拿出来,放进TableWidget中
print(i)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(a, item)
item = self.tableWidget.horizontalHeaderItem(a)
item.setText(self._translate("Form", self.tablename.get(i[0]) if self.tablename.get(i[0]) else i[0] ))#将数据库中取出的头部映射字段的键值
a = a + 1
total = list(total) # 将数据格式改为列表形式,其是将数据库中取出的数据整体改为列表形式
for i in range(len(total)): # 将相关的数据
total[i] = list(total[i]) # 将获取的数据转为列表形式
for i in range(self.row):
for j in range(self.vol):
self.Table_Data(i, j, total[i][j])
def My_Sql2(self,order_id2): # 连接mysql数据库
connection = pymysql.connect(host="223.98.100.194", port=13306, user='root', password="test@2020", db="rosid")
print('successfully connect')
cur = connection.cursor()
sql = f'SELECT *FROM t_roside_order WHERE order_id="{order_id2}"'
print(sql)
cur.execute(sql) # 将数据从数据库中拿出来
total = cur.fetchall()
connection.close()
col_result = cur.description
print(col_result)
self.row = cur.rowcount # 取得记录个数,用于设置表格的行数
self.vol = len(total[0]) # 取得字段数,用于设置表格的列数
col_result = list(col_result)
a = 0
self.tableWidget.setColumnCount(self.vol)
self.tableWidget.setRowCount(self.row)
for i in col_result: # 设置表头信息,将mysql数据表中的表头信息拿出来,放进TableWidget中
print(i)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(a, item)
item = self.tableWidget.horizontalHeaderItem(a)
item.setText(self._translate("Form", self.tablename.get(i[0]) if self.tablename.get(i[0]) else i[0] ))#将数据库中取出的头部映射字段的键值
a = a + 1
total = list(total) # 将数据格式改为列表形式,其是将数据库中取出的数据整体改为列表形式
for i in range(len(total)): # 将相关的数据
total[i] = list(total[i]) # 将获取的数据转为列表形式
for i in range(self.row):
for j in range(self.vol):
self.Table_Data(i, j, total[i][j])
def My_Sql3(self): # 连接mysql数据库
connection = pymysql.connect(host="223.98.100.194", port=13306, user='root', password="test@2020", db="rosid")
print('successfully connect')
cur = connection.cursor()
sql = f'SELECT *FROM t_roside_order '
print(sql)
cur.execute(sql) # 将数据从数据库中拿出来
total = cur.fetchall()
connection.close()
col_result = cur.description
print(col_result)
self.row = cur.rowcount # 取得记录个数,用于设置表格的行数
self.vol = len(total[0]) # 取得字段数,用于设置表格的列数
col_result = list(col_result)
a = 0
self.tableWidget.setColumnCount(self.vol)
self.tableWidget.setRowCount(self.row)
for i in col_result: # 设置表头信息,将mysql数据表中的表头信息拿出来,放进TableWidget中
print(i)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(a, item)
item = self.tableWidget.horizontalHeaderItem(a)
item.setText(self._translate("Form", self.tablename.get(i[0]) if self.tablename.get(i[0]) else i[0] ))#将数据库中取出的头部映射字段的键值
a = a + 1
total = list(total) # 将数据格式改为列表形式,其是将数据库中取出的数据整体改为列表形式
for i in range(len(total)): # 将相关的数据
total[i] = list(total[i]) # 将获取的数据转为列表形式
for i in range(self.row):
for j in range(self.vol):
self.Table_Data(i, j, total[i][j])
def My_Sql4(self,car_nom,combotxt): # 连接mysql数据库
connection = pymysql.connect(host="223.98.100.194", port=13306, user='root', password="test@2020", db="rosid")
print('successfully connect')
cur = connection.cursor()
sql = f'SELECT *FROM t_roside_order WHERE car_no="{car_nom}" and order_status={combotxt}'
print(sql)
cur.execute(sql) # 将数据从数据库中拿出来
total = cur.fetchall()
connection.close()
col_result = cur.description
print(col_result)
self.row = cur.rowcount # 取得记录个数,用于设置表格的行数
self.vol = len(total[0]) # 取得字段数,用于设置表格的列数
col_result = list(col_result)
a = 0
self.tableWidget.setColumnCount(self.vol)
self.tableWidget.setRowCount(self.row)
for i in col_result: # 设置表头信息,将mysql数据表中的表头信息拿出来,放进TableWidget中
print(i)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(a, item)
item = self.tableWidget.horizontalHeaderItem(a)
item.setText(self._translate("Form", self.tablename.get(i[0]) if self.tablename.get(i[0]) else i[0] ))#将数据库中取出的头部映射字段的键值
a = a + 1
total = list(total) # 将数据格式改为列表形式,其是将数据库中取出的数据整体改为列表形式
for i in range(len(total)): # 将相关的数据
total[i] = list(total[i]) # 将获取的数据转为列表形式
for i in range(self.row):
for j in range(self.vol):
self.Table_Data(i, j, total[i][j])
def My_Sql5(self,order_id2,combotxt): # 连接mysql数据库
connection = pymysql.connect(host="223.98.100.194", port=13306, user='root', password="test@2020", db="rosid")
print('successfully connect')
cur = connection.cursor()
sql = f'SELECT *FROM t_roside_order WHERE order_id="{order_id2}" and order_status={combotxt}'
print(sql)
cur.execute(sql) # 将数据从数据库中拿出来
total = cur.fetchall()
connection.close()
col_result = cur.description
#print(col_result)
self.row = cur.rowcount # 取得记录个数,用于设置表格的行数
self.vol = len(total[0]) # 取得字段数,用于设置表格的列数
col_result = list(col_result)
a = 0
self.tableWidget.setColumnCount(self.vol)
self.tableWidget.setRowCount(self.row)
for i in col_result: # 设置表头信息,将mysql数据表中的表头信息拿出来,放进TableWidget中
#print(i)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(a, item)
item = self.tableWidget.horizontalHeaderItem(a)
item.setText(self._translate("Form", self.tablename.get(i[0]) if self.tablename.get(i[0]) else i[0] ))#将数据库中取出的头部映射字段的键值
a = a + 1
total = list(total) # 将数据格式改为列表形式,其是将数据库中取出的数据整体改为列表形式
print(total)
for i in range(len(total)): # 将相关的数据
total[i] = list(total[i]) # 将获取的数据转为列表形式
for i in range(self.row):
for j in range(self.vol):
self.Table_Data(i, j, total[i][j])
def My_Sql6(self, combotxt): # 连接mysql数据库
connection = pymysql.connecthost="223.98.100.194", port=13306, user='root', password="test@2020", db="rosid")
print('successfully connect')
cur = connection.cursor()
sql = f'SELECT *FROM t_roside_order WHERE order_status="{combotxt}"'
print(sql)
cur.execute(sql) # 将数据从数据库中拿出来
total = cur.fetchall()
connection.close()
col_result = cur.description
print(col_result)
self.row = cur.rowcount # 取得记录个数,用于设置表格的行数
self.vol = len(total[0]) # 取得字段数,用于设置表格的列数
col_result = list(col_result)
a = 0
self.tableWidget.setColumnCount(self.vol)
self.tableWidget.setRowCount(self.row)
for i in col_result: # 设置表头信息,将mysql数据表中的表头信息拿出来,放进TableWidget中
print(i)
item = QtWidgets.QTableWidgetItem()
self.tableWidget.setHorizontalHeaderItem(a, item)
item = self.tableWidget.horizontalHeaderItem(a)
item.setText(self._translate("Form", self.tablename.get(i[0]) if self.tablename.get(i[0]) else i[
0])) # 将数据库中取出的头部映射字段的键值
a = a + 1
total = list(total) # 将数据格式改为列表形式,其是将数据库中取出的数据整体改为列表形式
for i in range(len(total)): # 将相关的数据
total[i] = list(total[i]) # 将获取的数据转为列表形式
for i in range(self.row):
for j in range(self.vol):
self.Table_Data(i, j, total[i][j])
if __name__ == "__main__":
app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
ui = MyClass()
sys.exit(app.exec_())