取引先保守(検索機能付) 子ウィンド利用
取引先保守(親ウィンド)
mnttorihikisaki.ui mnttorihikisaki.py MntTorihikisaki.project QtCreator で作成
pyside6-uic mnttorihikisaki.ui -o ui_mnttorihikisaki.py
取引先検索(子ウィンド)
kentorihikisaki.ui kentorihikisaki.py kentorihikisaki.project QtCreator で作成
signals と slots を buttonBox へ設定します。子ウィンドの戻り値をセットします。
pyside6-uic kentorihikisaki.ui -o ui_kentorihikisaki.py
取引先保守の作成
MntTorihikisaki のフォルダーへ ui_kentorihikisaki.py をコピーする
mnttorihikisaki.py
実行画面
# This Python file uses the following encoding: utf-8
import os
from pathlib import Path
import sys
import io
import csv
from datetime import date,datetime
from PySide6 import *
from PySide6.QtCore import *
from PySide6.QtGui import *
from PySide6.QtWidgets import *
# Important:
# You need to run the following command to generate the ui_form.py file
# pyside6-uic form.ui -o ui_form.py, or
# pyside2-uic form.ui -o ui_form.py
from ui_mnttorihikisaki import Ui_MntTorihikisaki
from ui_kentorihikisaki import Ui_kentorihikisaki
import pymssql
server = '192.168.10.111'
username = 'sa'
password = 'xxxxxxxx'
database = 'kakei'
try:
conn = pymssql.connect( server , username , password, database )
except Exception as e:
print('接続エラー' + e.args)
else:
pass
class SimpleTableModel(QAbstractTableModel):
def __init__(self, list, headers = [], parent = None):
QAbstractTableModel.__init__(self, parent)
self.list = list
self.headers = headers
def rowCount(self, parent):
return len(self.list)
def columnCount(self, parent):
return len(self.list[0])
def flags(self, index):
return Qt.ItemIsEditable | Qt.ItemIsEnabled | Qt.ItemIsSelectable #Qt.ItemIsEditable | Qt.ItemIsEnabled | Qt.ItemIsSelectable
def data(self, index, role):
if role == Qt.EditRole:
row = index.row()
column = index.column()
return self.list[row][column]
elif role == Qt.DisplayRole:
row = index.row()
column = index.column()
value = self.list[row][column]
# Perform per-type checks and render accordingly.
if isinstance(value, datetime):
# Render time to YYY-MM-DD.
return value.strftime("%Y-%m-%d")
elif isinstance(value, int):
# Render int to 0 dp
return "%d" % value
elif isinstance(value, float):
# Render float to 2 dp
return "%.2f" % value
elif isinstance(value, str):
# Render strings with quotes
return "%s" % value
# Default (anything not captured above: e.g. int)
else:
return value
elif role == Qt.TextAlignmentRole:
row = index.row()
column = index.column()
value = self.list[row][column]
if isinstance(value, int) or isinstance(value, float) or column == 2 or column == 3 or column == 4 :#例外とした
# Align right, vertical middle.
return int(Qt.AlignRight) + int(Qt.AlignVCenter)#,Qt.AlignHCenter,Qt.AlignRight,Qt.AlignCenter,Qt.AlignTop,Qt.AlignVCenter,Qt.AlignBottom
else:
return int(Qt.AlignLeft) + int(Qt.AlignVCenter)
def setData(self, index, value, role= Qt.EditRole):
if role == Qt.EditRole:
row = index.row()
column = index.column()
self.list[row][column] = value
self.dataChanged.emit(index, index)
return True
return False
def headerData(self, section, orientation, role):
if role == Qt.DisplayRole:
if orientation == Qt.Horizontal:
if section < len(self.headers):
return self.headers[section]
else:
return "not implemented"
else:
return "%d" % (section + 1)
class ValClass:
sonzai = 0
class MntTorihikisaki(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MntTorihikisaki()
self.ui.setupUi(self)
self.popup_dlg = None
# フォームと動作の関連付け
self.ui.pushButton_4.clicked.connect(self.sub_dlg)
self.ui.lineEdit.returnPressed.connect(self.select)
self.ui.lineEdit_2.returnPressed.connect(self.edit_2)
self.ui.lineEdit_3.returnPressed.connect(self.edit_3)
self.ui.lineEdit_4.returnPressed.connect(self.edit_4)
self.ui.lineEdit_5.returnPressed.connect(self.edit_5)
self.ui.lineEdit_6.returnPressed.connect(self.edit_6)
self.ui.lineEdit_7.returnPressed.connect(self.edit_7)
self.ui.lineEdit_8.returnPressed.connect(self.edit_8)
self.ui.pushButton.clicked.connect(self.koshin)
self.ui.pushButton_2.clicked.connect(self.sakujo)
self.ui.pushButton_3.clicked.connect(self.exit)
self.ui.pushButton_5.clicked.connect(self.torikeshi)
self.ui.label.setStyleSheet("background-color: pink")
self.ui.label_2.setStyleSheet("background-color: violet")
self.ui.label_3.setStyleSheet("background-color: violet")
self.ui.label_4.setStyleSheet("background-color: violet")
self.ui.label_5.setStyleSheet("background-color: violet")
self.ui.label_6.setStyleSheet("background-color: violet")
self.ui.label_7.setStyleSheet("background-color: violet")
self.ui.label_8.setStyleSheet("background-color: violet")
self.ui.label_9.setStyleSheet("background-color: violet")
self.ui.label_10.setStyleSheet("background-color: aqua")
self.ui.pushButton.setStyleSheet("background-color: aqua")
self.ui.pushButton_2.setStyleSheet("background-color: yellow")
self.ui.pushButton_3.setStyleSheet("background-color: green")
self.ui.pushButton_4.setStyleSheet("background-color: blue")
self.ui.pushButton_5.setStyleSheet("background-color: violet")
def sub_dlg(self):
dialog = Subkentorihikisaki()
if dialog.exec():
self.ui.lineEdit.setText(dialog.dlg.lineEdit.text())
self.ui.lineEdit.setFocus()
def exit(self): #-> None:
sys.exit()
#self.close()
def torikeshi(self):
self.ui.lineEdit_2.setText('')
self.ui.lineEdit_3.setText('')
self.ui.lineEdit_4.setText('')
self.ui.lineEdit_5.setText('')
self.ui.lineEdit_6.setText('')
self.ui.lineEdit_7.setText('')
self.ui.lineEdit_8.setText('')
self.ui.label_10.setText('')
self.ui.lineEdit.setEnabled(True)
self.ui.lineEdit.setFocus()
def select(self):
codestr = self.ui.lineEdit.text()
try:
codeint = int(codestr)
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.ui.lineEdit.setFocus()
return
else:
if codeint <= 0 or codeint > 9999:
QMessageBox.warning(self,'エラー','コードエラー')
self.ui.lineEdit.setFocus()
return
try:
tablenm = "取引先マスター"
cursor = conn.cursor()
sql = "select * from " + tablenm
sql += " where 取引先コード = " + str(codeint)
sql += " order by 取引先コード"
cursor.execute(sql)
results = cursor.fetchall()
sonzai = 0
for res in results:
self.ui.lineEdit_2.setText(str(res[1]))
self.ui.lineEdit_3.setText(str(res[2]))
self.ui.lineEdit_4.setText(str(res[3]))
self.ui.lineEdit_5.setText(str(res[4]))
self.ui.lineEdit_6.setText(str(res[5]))
self.ui.lineEdit_7.setText(str(res[6]))
self.ui.lineEdit_8.setText(str(res[7]))
sonzai = 1
except Exception as e:
QMessageBox.warning(self,'エラー','selectエラー' + e.args)
else:
pass
cursor.close()
self.ui.lineEdit.setEnabled(False)
self.ui.lineEdit_2.setFocus()
if sonzai == 1:
self.ui.label_10.setText('既 存')
else:
self.ui.label_10.setText('新 規')
self.ui.lineEdit_2.setText('')
self.ui.lineEdit_3.setText('0')
self.ui.lineEdit_4.setText('0')
self.ui.lineEdit_5.setText('0')
self.ui.lineEdit_6.setText('')
self.ui.lineEdit_7.setText('')
self.ui.lineEdit_8.setText('')
ValClass.sonzai = sonzai
return
def edit_2(self):
self.ui.lineEdit_2.focusNextChild()
def edit_3(self):
codestr = self.ui.lineEdit_3.text()
try:
codeint = int(codestr)
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.ui.lineEdit_3.setFocus()
return
else:
if codeint < 0 or codeint > 9:
QMessageBox.warning(self,'エラー','コードエラー')
self.ui.lineEdit_3.setFocus()
return
self.ui.lineEdit_3.focusNextChild()
def edit_4(self):
codestr = self.ui.lineEdit_4.text()
try:
codeint = int(codestr)
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.ui.lineEdit_4.setFocus()
return
else:
if codeint < 0 or codeint > 9999:
QMessageBox.warning(self,'エラー','コードエラー')
self.ui.lineEdit_4.setFocus()
return
self.ui.lineEdit_4.focusNextChild()
def edit_5(self):
codestr = self.ui.lineEdit_5.text()
try:
codeint = int(codestr)
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.ui.lineEdit_5.setFocus()
return
else:
if codeint < 0 or codeint > 999:
QMessageBox.warning(self,'エラー','コードエラー')
self.ui.lineEdit_5.setFocus()
return
self.ui.lineEdit_5.focusNextChild()
def edit_6(self):
self.ui.lineEdit_6.focusNextChild()
def edit_7(self):
self.ui.lineEdit_7.focusNextChild()
def edit_8(self):
self.ui.lineEdit_2.setFocus()
def koshin(self):
try:
v1 = int(self.ui.lineEdit.text())
v3 = int(self.ui.lineEdit_3.text())
v4 = int(self.ui.lineEdit_4.text())
v5 = int(self.ui.lineEdit_5.text())
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.ui.lineEdit_3.setFocus()
return
else:
if v1 <= 0 or v1 > 9999:
QMessageBox.warning(self,'エラー','取引先コードエラー')
self.ui.lineEdit.setFocus()
return
elif v3 < 0 or v3 > 9:
QMessageBox.warning(self,'エラー','取引先区分エラー')
self.ui.lineEdit_3.setFocus()
return
elif v4 < 0 or v4 > 9999:
QMessageBox.warning(self,'エラー','銀行コードエラー')
self.ui.lineEdit_4.setFocus()
return
elif v5 < 0 or v5 > 999:
QMessageBox.warning(self,'エラー','支店コードエラー')
self.ui.lineEdit_5.setFocus()
return
v2 = self.ui.lineEdit_2.text()
v6 = self.ui.lineEdit_6.text()
v7 = self.ui.lineEdit_7.text()
v8 = self.ui.lineEdit_8.text()
if ValClass.sonzai == 0: #新規
sqlstr = " insert into 取引先マスター (取引先コード,取引先名,取引先区分,銀行コード,支店コード,口座番号,名義人,備考) "
sqlstr += " values ( " + str(v1) + ",'" + v2 + "'," + str(v3) + "," + str(v4) + "," + str(v5) + ",'"
sqlstr += v6 + "','" + v7 + "','" + v8 + "')"
else: #既存
sqlstr = " update 取引先マスター "
sqlstr += " set "
sqlstr += " 取引先名 = '" + v2 + "'"
sqlstr += ",取引先区分 = " + str(v3)
sqlstr += ",銀行コード = " + str(v4)
sqlstr += ",支店コード = " + str(v5)
sqlstr += ",口座番号 = '" + v6 + "'"
sqlstr += ",名義人 = '" + v7 +"'"
sqlstr += ",備考 = '" + v8 +"'"
sqlstr += " where 取引先コード = " + str(v1)
try:
cursor = conn.cursor()
cursor.execute(sqlstr)
except Exception as e:
QMessageBox.warning(self,'結 果','更新できません。' + sqlstr)
cursor.close()
self.ui.lineEdit_2.setFocus()
return
else:
pass
conn.commit()
cursor.close()
QMessageBox.warning(self,'結 果','更新しました。')
self.torikeshi()
self.ui.lineEdit.setFocus()
def sakujo(self):
if ValClass.sonzai == 0:
QMessageBox.warning(self,'結 果','更新できません。レコードなし')
try:
v1 = int(self.ui.lineEdit.text())
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.ui.lineEdit.setFocus()
return
else:
if v1 <= 0 or v1 > 9999:
QMessageBox.warning(self,'エラー','取引先コードエラー')
self.ui.lineEdit.setFocus()
return
sqlstr = " delete from 取引先マスター "
sqlstr += " where 取引先コード = " + str(v1)
try:
cursor = conn.cursor()
cursor.execute(sqlstr)
except Exception as e:
QMessageBox.warning(self,'結 果','削除できません。' + sqlstr )
cursor.close()
self.ui.lineEdit.setFocus()
return
else:
pass
conn.commit()
cursor.close()
QMessageBox.warning(self,'結 果','削除しました。')
self.torikeshi()
self.ui.lineEdit.setFocus()
class Subkentorihikisaki(QDialog):
def __init__(self, parent=None):
super().__init__(parent)
self.dlg = Ui_kentorihikisaki()
self.dlg.setupUi(self)
self.view()
self.dlg.tableView.clicked.connect(self.valset)
def valset(self):
selection = self.dlg.tableView.selectedIndexes()
if selection:
rows = sorted(index.row() for index in selection)
columns = sorted(index.column() for index in selection)
rowcount = rows[-1] - rows[0] + 1
colcount = columns[-1] - columns[0] + 1
table = [[''] * colcount for _ in range(rowcount)]
for index in selection:
row = index.row() - rows[0]
column = index.column() - columns[0]
table[row][column] = index.data()
self.dlg.lineEdit.setText(str(table[0][0]))
def view(self):
server = '192.168.10.111'
username = 'sa'
password = 'xxxxxxxx'
database = 'kakei'
try:
conn = pymssql.connect( server , username , password, database )
cursor = conn.cursor()
sqlstr = " select * from 取引先マスター "
sqlstr += " order by 取引先コード"
cursor.execute(sqlstr)
results = cursor.fetchall()
# データを表示
coldata = ['コード','取引先名','取引先区分']
rowdata = []
row = 0
for res in results:
i=0
rowone = []
while i <= 2:
try:
strint = int(str(res[i]))
strfloat = float(str(res[i]))
if strint == strfloat:
rowone.append(strint)
else:
rowone.append(strfloat)
except:
rowone.append(str(res[i]))
else:
pass
i += 1
row += 1
rowdata.append(rowone)
model = SimpleTableModel(rowdata, coldata)
self.dlg.tableView.setModel(model) # create model and set
self.dlg.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents) #列幅の自動調整 0:Interactive 1:Stretch 2:Fixed 3:ResizeToContents
cursor.close()
conn.close()
except Exception as e:
QMessageBox.warning(self,'エラー','接続エラー' + e.args)
else:
pass
if __name__ == "__main__":
app = QApplication(sys.argv)
widget = MntTorihikisaki()
widget.show()
sys.exit(app.exec())
検索ボタン押下
セブンプラスの行をクリック
ＯＫボタン押下
検索画面で選択した値がセットされます。
 子ウィンド利用 取引先保守(親ウィンド) mnttorihikisaki.ui mnttorihikisaki.py MntTorihikisaki.project QtCr){kind=link}