def on_Button_pass_clicked(self): h = self.Quote_list.currentIndex().row() # 找到所选行的行数h bjdh = self.Quote_list.item(h, 1).text() # 找到所选h行的1位报价单号 # cur_4.execute("UPDATE 报价基本信息 SET 状态='通过' WHERE 报价单号 = '"+bjdh+"'") myMdb().update(table='报价基本信息', 状态='已审核', where="报价单号=" + bjdh) QMessageBox.information(QWidget(), "标题", "审核成功") self.Quote_list.clearContents() self.Quote_detail.clearContents() self.querylist()
def on_PBsave_clicked(self): """保存报价明细和汇总""" if self.CBcorporate.currentText() == "请选择公司名称": QMessageBox.warning(self, '警告', '公司名称未选择') return # 保存报价明细 rows = self.TWquote.rowCount() # 总行数 cols = self.TWquote.columnCount() # 总列数 for h in range(rows + 1): if not self.TWquote.item(h, 0): # 从0行开始找出空值所在行 break # 跳出循环 elif self.TWquote.item(h, 0).text() == "": #从0行开始找出空值所在行 break preSql = "insert into quote (" # 前一段拼接字段 subSql = "values(" # 后一段拼接字段 # exc = () # 作为execute的参数值,这是一个tuble类型 preSql += "公司名称" + "," subSql += "%s," preSql += "报价单号" + "," subSql += "%s," for i in range(cols): # 取出每一个子json的key和value值 x = self.TWquote.horizontalHeaderItem(i).text() # 列表头值 preSql += x + "," # 拼接前面sql的key值 subSql += "%s," # 拼接后面sql的value数量 preSql = preSql[0:preSql.__len__() - 1] + ")" # 去掉后面的“,”再添加“)” subSql = subSql[0:subSql.__len__() - 1] + ")" # 去掉后面的“,”再添加“)” sql = preSql + subSql # 前后相加成一个完整的sql param = [] # 建传入值的列表 for k in range(h): value_list = [] value_list.append(self.CBcorporate.currentText()) # 公司名称加入数组 value_list.append(self.quotationNo.text()) for i in range(cols): if i in (10, 11, 12, 13) and self.TWquote.item(k, i).text() == "": value_list.append("0") # 把空值的数字格设为0值 else: value_list.append(self.TWquote.item(k, i).text()) param.append(value_list) rowcount = myMdb().insert_many(sql, param) # 执行SQL,返回插入的条数 # 保存报价汇总============================================================== myMdb().insert(table='报价基本信息', 公司名称=self.CBcorporate.currentText(), 报价单号=self.quotationNo.text(), 总数量=self.total_quantity.text(), 总价=self.total_price.text(), 业务员=self.contact.text(), 报价日期=self.quotedate.text(), 状态='待审核') QMessageBox.about(self, "保存成功", "保存了" + str(rowcount) + "条报价记录")
def add_cmb_state(self): """列表框添加生产状态""" self.cmb_NO.clear() # self.clearData() mymdb = myMdb() res, cur = mymdb.fetchall(field='distinct {}'.format('生产状态'), table='{}'.format('order_list')) m_st = [tup[0] for tup in res] self.cmb_state.insertItem(0, "选择生产状态") self.cmb_state.addItems(m_st)
def dlg_data(self): """点击查询-->查询报价目录""" m_state = self.cbo_state.currentText() if self.cbo_filter.currentText() == "": res = myMdb().fetchall(table='报价基本信息', where="状态='待审核'") else: m_no = self.cbo_filter.currentText() res = myMdb().fetchall(table='报价基本信息', where="报价单号="+"'"+m_no+"'") if res[0] == (): return # data[1]是cur,data[0]是data数据 col_lst = [tup[0] for tup in res[1].description] data = [tup[0] for tup in res[0]] row = len(data) # 获得data的行数 vol = len(res[0][0]) # 获得data的列数.cur.description或len(data[0]) # print(data[0]) # 插入表格 # self.tableWidget = QTableWidget(row, vol) # 设置row行vol列的表格 self.tablewidget.setColumnCount(vol) self.tablewidget.setRowCount(row) font = QFont('微软雅黑', 9) self.tablewidget.setToolTip("查询") self.tablewidget.horizontalHeader().setFont(font) # 设置行表头字体 self.tablewidget.verticalHeader().setVisible(False) # 左垂直表头不显示 self.tablewidget.setSelectionMode(QAbstractItemView.SingleSelection) #只能选择单行 # 设置标题 self.tablewidget.setHorizontalHeaderLabels(col_lst) # 设置表格颜色 self.tablewidget.horizontalHeader().setStyleSheet( 'QHeaderView::section{background:skyblue}') self.tablewidget.setEditTriggers(QAbstractItemView.NoEditTriggers) # 设置表格禁止编辑 self.tablewidget.setSelectionBehavior(QAbstractItemView.SelectRows) # 设置整行选中 self.tablewidget.setFrameStyle(QFrame.Box | QFrame.Plain) # 构建表格插入数据 for i in range(row): # i到row-1的数量 for j in range(vol): temp_data = res[0][i][j] # 临时记录,不能直接插入表格 data1 = QTableWidgetItem(str(temp_data)) # 转换后可插入表格 self.tablewidget.setItem(i, j, data1) # self.tableWidget.resizeColumnsToContents() # 自适应宽度 self.tablewidget.resizeRowsToContents() # 自适应行高,放最后可以等数据写入后自动适应表格数据宽度 self.tablewidget.horizontalHeader().setStretchLastSection(True) # 最后一列对齐边框
def date_dateChanged(self): """查询指定发货日期的生产编号""" self.cmbNO.clear() mymdb = myMdb() m_date = self.dateEdit.date().toString("yyyy-MM-dd") res, cur = mymdb.fetchall(field='{}'.format('distinct 生产编号'), table='{}'.format('发货记录'), where="发货日期='{}'".format(m_date)) no_lst = [tup[0] for tup in res] self.cmbNO.addItems(no_lst)
def cmb_state_activated(self): """选择状态带出公司代码""" self.cmb_group.clear() # self.cmb_NO.clear() mymdb = myMdb() m_state = self.cmb_state.currentText() res, cur = mymdb.fetchall(field='distinct {}'.format('买方'), table='{}'.format('order_list'), where="生产状态='{}'".format(m_state)) m_st = [tup[0] for tup in res] self.cmb_group.addItems(m_st)
def cbo_state_currentIndexChanged(self): """选择审核状态带出公司名称""" self.cbo_group.clear() # self.clearData() m_state = self.cbo_state.currentText() res = myMdb().fetchall(field='distinct 公司名称', table='报价基本信息', where='状态='+"'"+m_state+"'") no_lst = [tup[0] for tup in res[0]] self.cbo_group.insertItem(0, "选择公司名称") self.cbo_group.addItems(no_lst)
def cbo_group_currentIndexChanged(self): """选择公司带出报价单号""" self.cbo_filter.clear() # self.clearData() m_state = self.cbo_state.currentText() m_group = self.cbo_group.currentText() res = myMdb().fetchall(field='报价单号', table='报价基本信息', where='公司名称='+"'"+m_group+"'"+" and 状态="+"'"+m_state+"'") no_lst = [tup[0] for tup in res[0]] self.cbo_filter.addItems(no_lst)
def querylist(self): # 查询报价清单 self.Quote_list.clearContents # clearContents清除内容,clear清空表格中所有内容(包含表头) lsearch = self.Line_search.text() # 搜索框 # sql = "SELECT * FROM 报价基本信息 WHERE 公司名称 LIKE '%"+lsearch+"%'" #'%"+bjdh+"%'" if lsearch == "": data_2 = myMdb().fetchall(table='报价基本信息', where="状态!='已审核'") else: data_2 = myMdb().fetchall(table='报价基本信息', where="公司名称=" + "'" + lsearch + "'" + "and 状态!='已审核'") # col_lst_2 = [tup[0] for tup in curr.description] # print(data_2) row_2 = len(data_2) #获得data的行数 vol_2 = len(data_2[0]) #获得data的列数.cur.description len(data[0]) self.Quote_list.setRowCount(row_2) #取查询到数据的行数,设表格行数 for i in range(row_2): #i到row-2的数量 for j in range(vol_2): temp_data = data_2[i][j] # 临时记录,不能直接插入表格 data2 = QTableWidgetItem(str(temp_data)) # 转换后可插入表格 self.Quote_list.setItem(i, j, data2)
def cmb_group_activated(self): """选择代码带出编号""" self.cmb_NO.clear() mymdb = myMdb() m_state = self.cmb_state.currentText() m_group = self.cmb_group.currentText() res, cur = mymdb.fetchall(field='distinct {}'.format('生产编号'), table='{}'.format('order_list'), where="买方='{}' and 生产状态='{}'".format( m_group, m_state)) m_gp = [tup[0] for tup in res] self.cmb_NO.addItems(m_gp)
def __init__(self, parent=None): super(Quote, self).__init__(parent) self.setupUi(self) #设置报价lneedit文本框显示当前日期 self.quotedate.setText(time.strftime("%Y-%m-%d", time.localtime())) #公司名称下拉列表框 result = myMdb().fetchall(field='公司名称', table='客户资料表') # vol = len(result[0]) # 循环取元祖数据,转为列表 col_lst = [tup[0] for tup in result] self.CBcorporate.insertItem(0, "请选择公司名称") self.CBcorporate.addItems(col_lst) #设置表格设置初始11行 self.TWquote.setRowCount(500) # 设置标题 # self.TWquote.setHorizontalHeaderLabels(input_table_header) # 设置每格为空值 for i in range(500): for j in range(15): new_item = QTableWidgetItem("") new_item.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter) self.TWquote.setItem(i, j, new_item) #表格格式设置 self.TWquote.horizontalHeader().setStyleSheet( 'QHeaderView::section{background:skyblue}') self.TWquote.setContextMenuPolicy(Qt.CustomContextMenu) # 允许右键产生菜单 self.TWquote.customContextMenuRequested.connect( self.right_menu) # 将右键绑定到槽 # self.TWquote.setSelectionBehavior(QAbstractItemView.SelectRows) # 设置整行选中 self.TWquote.verticalHeader().setVisible(False) # 左垂直表头不显示 # self.TWquote.setEditTriggers(QAbstractItemView.AnyKeyPressed) # 设置表格任何时候都能修改 self.TWquote.horizontalHeader().setStretchLastSection(True) #最后一列对齐边框 # self.TWquote.horizontalHeader().setResizeMode(QHeaderView.Stretch) # self.TWquote.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) #占满屏幕,平均分配列宽 self.TWquote.resizeColumnsToContents() # 自适应列宽度 # self.TWquote.resizeRowsToContents() # 自适应行高 # self.autoadd() # 自动编序号 # self.TWquote.hideColumn(0) # 隐藏第一列 # self.TWquote.showColumn(0) # 显示第一列 #设置文本框无边框 styleSheet = "background:transparent;border-width:0;border-style:outset" self.quotationNo.setStyleSheet(styleSheet) self.contact.setStyleSheet(styleSheet) self.lineEdit_5.setStyleSheet(styleSheet) self.lineEdit_6.setStyleSheet(styleSheet) self.lineEdit_8.setStyleSheet(styleSheet) self.lineEdit_9.setStyleSheet(styleSheet) self.quotedate.setStyleSheet(styleSheet) self.total_price.setStyleSheet(styleSheet) self.total_quantity.setStyleSheet(styleSheet) self.add_quote_No()
def add_quote_No(self): """自动生成报价单号""" # 格式化当前日期+后两位 date = time.strftime("%y%m%d", time.localtime()) + "00" bj = myMdb().fetchone(field='max(报价单号)', table='quote', where="报价单号>" + date) if bj[0] is None: bjdh = int(date) + 1 else: bjdh = bj[0] + 1 self.quotationNo.setText(str(bjdh))
def plateNumber(self): """查询指定生产编号中的车牌号""" self.cmbPN.clear() mymdb = myMdb() m_no = self.cmbNO.currentText() m_date = self.dateEdit.date().toString("yyyy-MM-dd") res, cur = mymdb.fetchall(field='{}'.format('distinct 车牌号'), table='{}'.format('发货记录'), where="生产编号='{}' and 发货日期='{}'".format( m_no, m_date)) m_lst = [tup[0] for tup in res] self.cmbPN.addItems(m_lst)
def cmbGroup_activated(self): """选择代码带出编号""" self.cmbNO.clear() mdb = myMdb() m_stt = self.cmbState.currentText() m_cmpn = self.cmbGroup.currentText() res = mdb.fetchall(field='distinct {}'.format(self.tag), table='{}'.format(self.tbl), where="{}='{}' and {}='{}'".format( self.company, m_cmpn, self.state, m_stt)) m_no = [tup[0] for tup in res[0]] self.cmbNO.addItems(m_no)
def cmb_NO_activated(self): """选中编号根据生产编号/发货状态不等于发货完成查询""" self.tablewidget.clearContents() mymdb = myMdb() m_state = self.cmb_state.currentText() # 状态 m_no = self.cmb_NO.currentText() # 编号 field = '{}'.format( '买方,合同编号,生产编号,序号,名称,制造标准,规格型号,材质,数量,工作令号,件号,已发货数,质保书,生产状态') # field = '{}'.format('序号,装箱编号') table = '{}'.format('order_list') where = "生产编号='{}' and (发货状态!='{}' or 发货状态 is Null)".format( m_no, '发货完成') if self.cmb_NO.currentText() == "": return else: res, cur = mymdb.fetchall(field=field, table=table, where=where) col_lst = [tup[0] for tup in cur.description] data = [tup[0] for tup in res] row = len(data) # 获得data的行数 vol = len(res[0]) # 获得data的列数 # 插入表格 self.tablewidget.setColumnCount(vol) self.tablewidget.setRowCount(row) font = QFont('微软雅黑', 9) self.tablewidget.setToolTip("查询") self.tablewidget.horizontalHeader().setFont(font) # 设置行表头字体 self.tablewidget.verticalHeader().setVisible(False) # 左垂直表头不显示 self.tablewidget.setSelectionMode( QAbstractItemView.SingleSelection) # 只能选择单行 # 设置标题 self.tablewidget.setHorizontalHeaderLabels(col_lst) # 设置表格颜色 self.tablewidget.horizontalHeader().setStyleSheet( 'QHeaderView::section{background:skyblue}') self.tablewidget.setEditTriggers( QAbstractItemView.NoEditTriggers) # 设置表格禁止编辑 self.tablewidget.setSelectionBehavior( QAbstractItemView.SelectRows) # 设置整行选中 self.tablewidget.setFrameStyle(QFrame.Box | QFrame.Plain) # 构建表格插入数据 for i in range(row): for j in range(vol): temp_data = res[i][j] data1 = QTableWidgetItem(str(temp_data)) if j == 0: data1.setCheckState(Qt.Unchecked) self.tablewidget.setItem(i, j, data1) # 适应宽度/高度/对齐边框 self.tablewidget.resizeColumnsToContents() # self.tablewidget.resizeRowsToContents() self.tablewidget.horizontalHeader().setStretchLastSection(True)
def add_cmbState(self): """列表框添加生产状态""" self.cmbNO.clear() # self.clearData() mymdb = myMdb() res = mymdb.fetchall(field='distinct {}'.format(self.state), table='{}'.format(self.tbl)) m_st = [tup[0] for tup in res[0]] try: m_st.remove(None) # 删除空值 finally: self.cmbState.insertItem(0, "选择状态") self.cmbState.addItems(m_st)
def cmbState_activated(self): """选择状态带出公司代码""" self.cmbGroup.clear() # self.cmb_NO.clear() mdb = myMdb() m_stt = self.cmbState.currentText() if m_stt == '': return else: res = mdb.fetchall(field='distinct {}'.format(self.company), table='{}'.format(self.tbl), where="{}='{}'".format(self.state, m_stt)) m_st = [tup[0] for tup in res[0]] self.cmbGroup.addItems(m_st)
def cmbNO_activated(self): """选择生产编号查询数据""" self.tableWidget.blockSignals(True) # 暂停单元格修改信号 self.tableWidget.clearContents() mymdb = myMdb() m_date = self.dateEdit.date().toString("yyyy-MM-dd") # 发货日期 m_no = self.cmbNO.currentText() # 编号 field = 'b.合同编号,b.序号,b.名称,b.制造标准,b.规格型号,b.材质,b.数量,b.已发货数,a.质保书,a.发货数量,a.装箱编号, \ a.收货单位,a.收货地址,a.联系方式' table = '发货记录 a inner join order_list b on a.生产编号=b.生产编号 and a.序号=b.序号 and a.发货日期=b.发货日期' res, cur = mymdb.fetchall(field='{}'.format(field), table='{}'.format(table), where="a.生产编号='{}' and a.发货日期='{}'".format( m_no, m_date)) tbl_header = [ '合同编号', '序号', '名称', '制造标准', '规格型号', '材质', '订单数量', '已发货数', '质保书', '发货数量', '装箱编号' ] data = [tup[0] for tup in res] row = len(data) # 获得data的行数 vol = len(tbl_header) # 获得data的列数 # 插入表格 self.tableWidget.setColumnCount(vol) self.tableWidget.setRowCount(row) # 设置标题 self.tableWidget.setHorizontalHeaderLabels(tbl_header) # 构建表格插入数据 for i in range(row): for j in range(vol): temp_data = res[i][j] data1 = QTableWidgetItem(str(temp_data)) if j in range(8): # 第0-7列禁止编辑 data1.setFlags(QtCore.Qt.ItemIsEnabled) self.tableWidget.setItem(i, j, data1) self.lineEdit_1.setText(res[0][11]) self.lineEdit_2.setText(res[0][12]) self.lineEdit_3.setText(res[0][13]) self.tableWidget.resizeColumnsToContents() # self.tableWidget.resizeRowsToContents() # self.tableWidget.horizontalHeader().setStretchLastSection(True) # 汇总发货数量 count = plusColumn(self, "tableWidget", 9) self.lineEdit_6.setText(str(count)) self.tableWidget.blockSignals(False) # 启动单元格修改信号
def quotelist(self): # 默认显示报价清单 data = myMdb().fetchall(table='报价基本信息', where="状态!='已审核'") # col_lst = [tup[0] for tup in cur.description] # 数据列字段名 tup:数组 #description:种类 row = len(data) # 获得data的行数 vol = len(data[0]) # 获得data的列数.cur.description或len(data[0]) # 插入表格 # self.Quote_list = QTableWidget(row, vol) # 设置row行vol列的表格 self.Quote_list.setRowCount(row) font = QtGui.QFont('微软雅黑', 9) self.Quote_list.horizontalHeader().setFont(font) # 设置行表头字体 # self.Quote_list.setHorizontalHeaderLabels(col_lst) # 设置标题 self.Quote_list.verticalHeader().setVisible(False) # 左垂直表头不显示 # 加单元格下拉列表框 comBox = QComboBox() comBox.addItems(['审核通过', '退回重报']) # comBox.setStyleSheet('QComboBox{margin:3px}') self.Quote_list.setCellWidget(0, 6, comBox) # 设置表格颜色 self.Quote_list.horizontalHeader().setStyleSheet( 'QHeaderView::section{background:skyblue}') # self.Quote_list.setContextMenuPolicy(Qt.CustomContextMenu) # 允许右键产生菜单 # self.Quote_list.customContextMenuRequested.connect(self.generateMenu) # 将右键绑定到槽 self.Quote_list.setEditTriggers( QAbstractItemView.NoEditTriggers) # 设置表格禁止编辑 self.Quote_list.setSelectionBehavior( QAbstractItemView.SelectRows) # 设置整行选中 splitter = QtWidgets.QSplitter(QtCore.Qt.Vertical) # 设置分割条 self.Quote_list.setFrameStyle(QtWidgets.QFrame.Box | QtWidgets.QFrame.Plain) # 构建表格插入数据 for i in range(row): # i到row-1的数量 for j in range(vol): temp_data = data[i][j] # 临时记录,不能直接插入表格 data1 = QTableWidgetItem(str(temp_data)) # 转换后可插入表格 self.Quote_list.setItem(i, j, data1) # self.Quote_list.resizeColumnsToContents() # 自适应宽度 self.Quote_list.resizeRowsToContents() # 自适应行高,放最后可以等数据写入后自动适应表格数据宽度 self.Quote_list.horizontalHeader().setStretchLastSection( True) # 最后一列对齐边框 splitter.addWidget(self.Quote_list) self.verticalLayout.addWidget(splitter) self.QuoteDetail()
def writeParam(self, param): """写入查询数据""" self.tableWidget.blockSignals(True) # 暂停单元格修改信号 # if param[0][13] == '清点装箱': # self.label.setText("修改送货清单") # self.label.setStyleSheet("color:#ff6600;") row = len(param) # 获得查询窗口选择数据的行数 # 找出送货地址,联系方式 mymdb = myMdb() res, cur = mymdb.fetchall(field='{}'.format('交货地点,联系方式'), table='{}'.format('orders'), where="生产编号='{}'".format(param[0][2])) # 设置标题 tbl_header = [ '序号', '名称', '制造标准', '规格型号', '材质', '订单数量', '工作令号', '件号', '已发货数', '质保书', '发货数量' ] col = len(tbl_header) self.tableWidget.setRowCount(row) self.tableWidget.setColumnCount(col) self.tableWidget.setHorizontalHeaderLabels(tbl_header) self.lineEdit_1.setText(param[0][0]) self.lineEdit_2.setText(str(res[0][0])) self.lineEdit_3.setText(str(res[0][1])) self.lineEdit_4.setText(param[0][2]) # 一生产编号有多合同号的情况,考虑用筛选法选出合同号加到cmb,再选择显示同一合同号 self.lineEdit_5.setText(param[0][1]) for i in range(row): # 取第4列到第12列 for j in range(3, 13): temp_data = param[i][j] if temp_data == 'None': temp_data = 0 data_1 = QTableWidgetItem(str(temp_data)) # j=12质保书列可编辑 if j != 12: data_1.setFlags(QtCore.Qt.ItemIsEnabled) # 禁止指定列编辑 self.tableWidget.setItem(i, j - 3, data_1) # 把送货单明细第11列None状态设为空值 self.tableWidget.setItem(i, 10, QTableWidgetItem('')) self.tableWidget.resizeColumnsToContents() self.tableWidget.blockSignals(False) # 启动单元格修改信号
def cmbNO_activated(self): """根据生产编号和发货状态等于'清单装箱'完成查询""" self.tableWidget.clearContents() if self.cmbNO.currentText() == "": return mymdb = myMdb() m_date = self.dateEdit.date().toString("yyyy-MM-dd") # 发货日期 m_no = self.cmbNO.currentText() # 编号 field = 'b.合同编号,b.序号,b.名称,b.制造标准,b.规格型号,b.材质,a.发货数量,a.装箱编号, a.物流公司,a.车牌号' table = '发货记录 a inner join order_list b on a.生产编号=b.生产编号 and a.序号=b.序号 and a.发货日期=b.发货日期' where = "a.生产编号='{}' and a.发货日期='{}'".format(m_no, m_date) res, cur = mymdb.fetchall(field='{}'.format(field), table='{}'.format(table), where=where) tbl_header = [tup[0] for tup in cur.description] data = [tup[0] for tup in res] row = len(data) # 获得data的行数 vol = len(tbl_header) # 插入表格 self.tableWidget.setColumnCount(vol) self.tableWidget.setRowCount(row) # 设置标题 self.tableWidget.setHorizontalHeaderLabels(tbl_header) # 构建表格插入数据 for i in range(row): for j in range(vol): temp_data = res[i][j] data1 = QTableWidgetItem(str(temp_data)) if j in range(7): # 第0-6列禁止编辑 data1.setFlags(QtCore.Qt.ItemIsEnabled) self.tableWidget.setItem(i, j, data1) self.tableWidget.resizeColumnsToContents() # self.tableWidget.resizeRowsToContents() # self.tableWidget.horizontalHeader().setStretchLastSection(True) # 汇总发货数量 count = plusColumn(self, "tableWidget", 6) self.lineEdit_6.setText(str(count)) # 带出车牌号 self.plateNumber()
def querydetail(self): # 查询报价明细 h = self.Quote_list.currentIndex().row() # 找到所选行的行数h bjdh = self.Quote_list.item(h, 1).text() # 找到所选h行的第2列报价单号 # sql = "SELECT * FROM 报价明细 WHERE 报价单号 LIKE '%"+bjdh+"%'" #'%"+bjdh+"%'" self.Quote_detail.clearContents() # 清除报价明细表内数据 data_3 = myMdb().fetchall(table='quote', where="报价单号=" + bjdh) # col_lst_3 = [tup[0] for tup in cur_3.description] # print(data_3) row_3 = len(data_3) #获得data的行数 vol_3 = len(data_3[0]) #获得data的列数.cur.description len(data[0]) self.Quote_detail.setRowCount(row_3) # self.Quote_detail.setColumnCount(0) #构建表格插入数据 for i in range(row_3): #i到row-1的数量 for j in range(vol_3): temp_data = data_3[i][j] # 临时记录,不能直接插入表格 data3 = QTableWidgetItem(str(temp_data)) # 转换后可插入表格 self.Quote_detail.setItem(i, j, data3) # 适应列宽/行高/最后一列对齐边框 self.Quote_detail.resizeColumnsToContents() self.Quote_detail.resizeRowsToContents() self.Quote_detail.horizontalHeader().setStretchLastSection(True)
def cmbNO_activated(self, param): """选择生产编号查询数据""" self.tableWidget.blockSignals(True) # 暂停单元格修改信号 self.tableWidget.clearContents() mymdb = myMdb() m_no = self.cmbNO.currentText() # 编号 field = '序号,数量,装箱编号' table = 'order_list' res, cur = mymdb.fetchall(field='{}'.format(field), table='{}'.format(table), where="生产编号='{}' and 发货状态='{}'".format( m_no, '清点装箱')) tbl_header = ['序号', '数量', '装箱单号', '物流公司', '车牌号'] data = [tup[0] for tup in res] row = len(data) # 获得data的行数 vol = len(tbl_header) # 获得data的列数 # 插入表格 self.tableWidget.setColumnCount(vol) self.tableWidget.setRowCount(row) # 设置标题 self.tableWidget.setHorizontalHeaderLabels(tbl_header) # 构建表格插入数据 for i in range(row): for j in range(3): temp_data = res[i][j] data1 = QTableWidgetItem(str(temp_data)) if j in range(3): # 第0-7列禁止编辑 data1.setFlags(QtCore.Qt.ItemIsEnabled) self.tableWidget.setItem(i, j, data1) # self.lineEdit_1.setText(res[0][11]) # self.lineEdit_2.setText(res[0][12]) # self.lineEdit_3.setText(res[0][13]) self.tableWidget.resizeColumnsToContents() # self.tableWidget.resizeRowsToContents() # self.tableWidget.horizontalHeader().setStretchLastSection(True) # 汇总发货数量 # count = plusColumn(self, "tableWidget", 9) # self.lineEdit_6.setText(str(count)) self.tableWidget.blockSignals(False) # 启动单元格修改信号
def savePack(self): """保存包装运输""" rows = self.tableWidget.rowCount() # 总行数 cols = self.tableWidget.columnCount() # 总列数 for row in range(rows): if not self.tableWidget.item(row, 7) or self.tableWidget.item( row, 7).text() == '': m_xh = self.tableWidget.item(row, 1).text() QMessageBox.warning(self, "警告", "序号{}装箱编号不能空".format(m_xh)) return button = QMessageBox.question(self, "注意", "请确认无误再保存,\n按OK继续,按Cancel退出", QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if button == QMessageBox.Cancel: return mymdb = myMdb() fields = '{}'.format('生产编号,序号,发货数量,装箱编号,物流公司,车牌号,发货日期') preSql = "insert into {} ({})".format('发货记录', fields) subSql = " values(%s, %s, %s, %s, %s, %s, %s)" sql_on = " ON DUPLICATE KEY UPDATE 装箱编号=VALUES(装箱编号),物流公司=VALUES(物流公司),车牌号=VALUES(车牌号)" sql = preSql + subSql + sql_on # 建更新订单明细用列表 param = [] for row in range(rows): value = [] value.append(self.cmbNO.currentText()) for col in (1, 6, 7, 8, 9): value.append(self.tableWidget.item(row, col).text()) value.append(self.dateEdit.date().toString("yyyy-MM-dd")) # 发货日期 param.append(value) # 数据库操作===================================================================================== mymdb.insert_many(sql, param) QMessageBox.about(self, "保存成功", "装箱运输记录已保存") self.tableWidget.clearContents()
def __init__(self, parent=None): super(Transport, self).__init__(parent) self.setupUi(self) self.initTableWidget() # 设置列表框手动输入生产编号 self.cmbNO = QtWidgets.QComboBox() self.cmbNO.setMinimumSize(QtCore.QSize(100, 26)) self.cmbNO.setEditable(False) self.cmbNO.setObjectName("生产编号") self.gridLayout.addWidget(self.cmbNO, 1, 5) self.dateEdit.setDate(QDate.currentDate()) # 显示清点装箱状态的生产编号 mymdb = myMdb() res, cur = mymdb.fetchall(field='{}'.format('distinct 生产编号'), table='{}'.format('order_list'), where="发货状态='{}'".format('清点装箱')) no_lst = [tup[0] for tup in res] self.cmbNO.addItems(no_lst) # 日期控件信号连接槽函数 self.cmbNO.activated.connect(self.cmbNO_activated)
def cmbNO_activated(self): """选中编号根据生产编号/发货状态不等于发货完成查询""" self.tablewidget.clearContents() m_stt = self.cmbState.currentText() # 状态 m_no = self.cmbNO.currentText() # 编号 where = '{}={}'.format(self.tag, m_no) # where = "生产编号='{}' and (发货状态!='{}' or 发货状态 is Null)".format(m_no, '发货完成') if self.cmbNO.currentText() == "": return else: mymdb = myMdb() res, cur = mymdb.fetchall(field=self.field, table=self.tbl, where=where) col_lst = [tup[0] for tup in cur.description] data = [tup[0] for tup in res] row = len(data) # 获得data的行数 vol = len(res[0]) # 获得data的列数 # 插入表格 self.tablewidget.setColumnCount(vol) self.tablewidget.setRowCount(row) # 设置标题 self.tablewidget.setHorizontalHeaderLabels(col_lst) # 构建表格插入数据 for i in range(row): for j in range(vol): temp_data = res[i][j] data1 = QTableWidgetItem(str(temp_data)) if j == 0: data1.setCheckState(Qt.Unchecked) self.tablewidget.setItem(i, j, data1) # 适应宽度/高度/对齐边框 self.tablewidget.resizeColumnsToContents() # self.tablewidget.resizeRowsToContents() self.tablewidget.horizontalHeader().setStretchLastSection(True)
def saveData(self): """保存发货清单""" rows = self.tableWidget.rowCount() # 总行数 cols = self.tableWidget.columnCount() # 总列数 for row in range(rows): if not self.tableWidget.item(row, 10) or self.tableWidget.item( row, 10).text() == '': m_xh = self.tableWidget.item(row, 0).text() QMessageBox.warning(self, "警告", "序号{}发货数量不能空".format(m_xh)) return if self.lineEdit_2 == '' or self.lineEdit_3 == '': QMessageBox.about(self, "警告", "发货地址/联系方式不能空") return button = QMessageBox.question(self, "注意", "请确认无误再保存发货清单,\n按OK继续,按Cancel退出", QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if button == QMessageBox.Cancel: return m_no = self.lineEdit_5.text() # 生产编号 mymdb = myMdb() # 建更新订单明细的发货清单明细sql field_list = '生产编号,序号,质保书,已发货数,发货日期,发货状态' preSql_list = "insert into order_list ({})".format(field_list) subSql_list = " values(%s, %s, %s, %s, %s, %s)" sql_list_on = " ON DUPLICATE KEY UPDATE 质保书=VALUES(质保书),已发货数=VALUES(已发货数),发货日期=VALUES(发货日期),发货状态=VALUES(发货状态)" sql_list = preSql_list + subSql_list + sql_list_on # 建发货记录的sql 考虑移到选择物流后再保存????? fields = '收货单位,收货地址,联系方式,生产编号,合同编号,序号,质保书,发货数量,发货日期' preSql = "insert into 发货记录 ({}) ".format(fields) subSql = " values(%s, %s, %s, %s, %s, %s, %s, %s, %s)" sql = preSql + subSql # 建更新订单明细用列表 param_list = [] # 建传入值的列表 param = [] for row in range(rows): value_list = [] value_list.append(self.lineEdit_5.text()) # 生产编号 m_fhsl = self.tableWidget.item(row, 10).text() # 发货数量 m_yfhs = self.tableWidget.item(row, 8).text() # 已发货数 for col in (0, 9, 10): if col == 10: value_list.append(int(m_fhsl) + int(m_yfhs)) else: value_list.append(self.tableWidget.item(row, col).text()) value_list.append( self.dateEdit.date().toString("yyyy-MM-dd")) # 发货日期 value_list.append('清点装箱') # 发货状态 param_list.append(value_list) # 循环执行静态字符串方式把文本框内容加入发货记录value列表 value = [] for i in range(1, 6): exec("value.append(self.lineEdit_{}.text())".format(i)) for col in (0, 9, 10): value.append(self.tableWidget.item(row, col).text()) value.append(self.dateEdit.date().toString("yyyy-MM-dd")) param.append(value) # 数据库操作============================================================== # 更新order_list发货情况 存在双倍数量,怀疑是ON DUPLICATE KEY UPDATE引起?????? rowcount = mymdb.insert_many(sql_list, param_list) # 发货汇总数据写入发货记录 mymdb.insert_many(sql, param) QMessageBox.about(self, "保存成功", "保存了" + str(int(rowcount / 2)) + "条发货记录") self.clearDeliveryData()
def saveDeliveryRevise(self): """保存修改的发货清单""" rows = self.tableWidget.rowCount() # 总行数 cols = self.tableWidget.columnCount() # 总列数 if self.lineEdit_2 == '' or self.lineEdit_3 == '': QMessageBox.about(self, "警告", "发货地址/联系方式不能空") return button = QMessageBox.question(self, "注意", "请确认无误再保存发货清单,\n按OK继续,按Cancel退出", QMessageBox.Ok | QMessageBox.Cancel, QMessageBox.Ok) if button == QMessageBox.Cancel: return mymdb = myMdb() # 建更新订单明细的发货清单明细sql 需加质保书/发货日期/发货状态判断有无发过 field_list = '{}'.format('生产编号,序号,已发货数,质保书,发货日期,发货状态') preSql_list = "insert into {} ({})".format('order_list', field_list) subSql_list = " values(%s, %s, %s, %s, %s, %s)" sql_list_on = " ON DUPLICATE KEY UPDATE 已发货数=VALUES(已发货数),质保书=VALUES(质保书),发货状态=VALUES(发货状态)" sql_list = preSql_list + subSql_list + sql_list_on fields = '{}'.format('收货单位,收货地址,联系方式,生产编号,序号,质保书,发货数量,装箱单号,发货日期') preSql = "insert into {} ({})".format('发货记录', fields) subSql = " values(%s, %s, %s, %s, %s, %s, %s, %s, %s)" sql_on = " ON DUPLICATE KEY UPDATE 收货单位=VALUES(收货单位),收货地址=VALUES(收货地址), \ 联系方式=VALUES(联系方式),质保书=VALUES(质保书),发货数量=VALUES(发货数量),装箱单号=VALUES(装箱单号)" sql = preSql + subSql + sql_on # 建更新订单明细用列表 param_list = [] # 建传入值的列表 param = [] for row in range(rows): value_list = [] value_list.append(self.cmbNO.currentText()) # 生产编号 for col in (1, 7, 8): value_list.append(self.tableWidget.item(row, col).text()) value_list.append( self.dateEdit.date().toString("yyyy-MM-dd")) # 发货日期 # 判断发货状态 if int(self.tableWidget.item(row, 9).text()) == 0 and int( self.tableWidget.item(row, 7).text()) > 0: value_list.append('部分发货') elif int(self.tableWidget.item(row, 7).text()) == 0: value_list.append('') elif int(self.tableWidget.item(row, 9).text()) > 0: value_list.append('清点装箱') param_list.append(value_list) # 发货记录value列表 value = [] value.append(self.lineEdit_1.text()) value.append(self.lineEdit_2.text()) value.append(self.lineEdit_3.text()) value.append(self.cmbNO.currentText()) for col in (1, 8, 9, 10): value.append(self.tableWidget.item(row, col).text()) value.append(self.dateEdit.date().toString("yyyy-MM-dd")) param.append(value) # 数据库操作============================================================== rowcount = mymdb.insert_many(sql_list, param_list) # 发货汇总数据写入发货记录 mymdb.insert_many(sql, param) QMessageBox.about(self, "修改成功", "成功修改发货清单") self.clearDeliveryData()
质保功能模块 """ import sys from PyQt5 import QtCore, QtGui, QtWidgets from PyQt5.QtGui import QColor, QBrush, QPixmap, QIcon, QFont from PyQt5.QtCore import pyqtSignal, QObject, Qt, pyqtSlot, QDate, QDateTime from PyQt5.QtWidgets import * import pymysql import time from ui.Ui_input import Ui_wgtInput from tools.mysql_conn import myMdb from tools.tools import * mymdb = myMdb() # 常用的实例和变量可以放最前 class Inspection(QWidget, Ui_wgtInput): """出厂检验类""" def __init__(self, parent=None): super(Inspection, self).__init__(parent) self.setupUi(self) # 隐藏不需要的控件 self.btn_query.hide() self.cmbWork.hide() self.comboBox_2.hide() # 设置列表框手动输入生产编号 # self.cmbNO.setEditable(True) self.cmbDpmt.addItems(['质检'])
from PyQt5 import QtCore, QtGui, QtWidgets from PyQt5.QtGui import QColor, QBrush, QPixmap, QIcon, QFont from PyQt5.QtCore import pyqtSignal, QObject, Qt, pyqtSlot, QDate, QDateTime from PyQt5.QtWidgets import * import pymysql import time from ui.Ui_plan import Ui_wgt_plan from ui.Ui_orderlist import Ui_Dialog # 查询订单明细子窗口 from ui.Ui_input import Ui_wgtInput from tools.mysql_conn import myMdb from tools.tools import * mymdb = myMdb() class Machine(QWidget, Ui_wgtInput): """机加类""" def __init__(self, parent=None): super(Machine, self).__init__(parent) self.setupUi(self) # self.mymdb = myMdb() # 隐藏不需要的控件 self.btn_query.hide() self.cmbWork.hide() self.comboBox_2.hide() self.cmbNO.setEditable(True) self.cmbDpmt.addItems(['金工车间', '机加外协'])