python

Qt6 Python SQLServer 取引入力 QTableWidget

Qt6 Python SQLServer 取引入力 QTableWidget

Qt6Creatorでプロジェクト inptorihikid を作成

inptorihikid.ui ui_inptorihikid を作成( pyside6-uic inptorihikid.ui -o ui_inptorihikid.py )

Qt6Creatorでプロジェクト kentorihiki を作成

kentorihikisaki.ui ui_kentorihikisaki を作成( pyside6-uic kentorihikisaki.ui -o ui_kentorihikisaki.py )

Qt6Creatorでプロジェクト inptorihiki を作成

inptorihiki.py を作成。 上記のui_inptorihikid、ui_kentorihikisaki をセット

# This Python file uses the following encoding: utf-8
import sys

from typing import Any, List
from datetime import date,datetime

from PySide6.QtCore import (QCoreApplication, QDate, QDateTime, QLocale,
    QMetaObject, QObject, QPoint, QRect,
    QSize, QTime, QUrl, Qt,QAbstractTableModel)
from PySide6.QtGui import (QBrush, QColor, QConicalGradient, QCursor,
    QFont, QFontDatabase, QGradient, QIcon,
    QImage, QKeySequence, QLinearGradient, QPainter,
    QPalette, QPixmap, QRadialGradient, QTransform)
from PySide6.QtWidgets import (QApplication, QLineEdit, QMainWindow, QMenuBar,
     QDialog, QPushButton, QSizePolicy, QStatusBar, QWidget,QMessageBox,QCheckBox,QHeaderView,QTableWidgetItem,QVBoxLayout,QHBoxLayout)
# 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 PyQt5 import QtCore, QtGui, QtWidgets

import ui_inptorihikid
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 inptorihiki(QMainWindow):
        def __init__(self, parent=None):
            super().__init__(parent)
            self.ui = ui_inptorihikid.Ui_inptorihikid()
            self.ui.setupUi(self)
            self.ui.tableWidget.installEventFilter(self)

            self.ui.lineEdit.returnPressed.connect(self.edit)
            self.ui.lineEdit_2.returnPressed.connect(self.select)

            self.ui.pushButton.clicked.connect(self.koshin)
            self.ui.pushButton_2.clicked.connect(self.torikeshi)
            self.ui.pushButton_3.clicked.connect(self.end)
            self.ui.pushButton_4.clicked.connect(self.ctrl_c)
            self.ui.pushButton_5.clicked.connect(self.ctrl_v)
            self.ui.pushButton_6.clicked.connect(self.ctrl_s)
            self.ui.pushButton_7.clicked.connect(self.sub_dlg)

            self.ui.label.setStyleSheet("background-color: pink")
            self.ui.label_2.setStyleSheet("background-color: violet")
            self.ui.label_4.setStyleSheet("background-color: yellow")
            self.ui.label_5.setStyleSheet("background-color: aqua")
            self.ui.label_6.setStyleSheet("background-color: pink")

            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: yellow")
            self.ui.pushButton_5.setStyleSheet("background-color: aqua")
            self.ui.pushButton_6.setStyleSheet("background-color: pink")

            self.ui.tableWidget.setRowCount(0)
            self.ui.tableWidget.setColumnCount(13)
            self.ui.tableWidget.setHorizontalHeaderItem(0,  QTableWidgetItem(" 取引日 "))
            self.ui.tableWidget.resizeColumnToContents(0)
            self.ui.tableWidget.setHorizontalHeaderItem(1,  QTableWidgetItem("連番"))
            self.ui.tableWidget.resizeColumnToContents(1)
            self.ui.tableWidget.setHorizontalHeaderItem(2,  QTableWidgetItem(" 摘 要 "))
            self.ui.tableWidget.resizeColumnToContents(2)
            self.ui.tableWidget.setHorizontalHeaderItem(3,  QTableWidgetItem(" 入金額 "))
            self.ui.tableWidget.resizeColumnToContents(3)
            self.ui.tableWidget.setHorizontalHeaderItem(4,  QTableWidgetItem(" 出金額 "))
            self.ui.tableWidget.resizeColumnToContents(4)
            self.ui.tableWidget.setHorizontalHeaderItem(5,  QTableWidgetItem("取引先"))
            self.ui.tableWidget.resizeColumnToContents(5)
            self.ui.tableWidget.setHorizontalHeaderItem(6,  QTableWidgetItem(" 取引先名 "))
            self.ui.tableWidget.resizeColumnToContents(6)
            self.ui.tableWidget.setHorizontalHeaderItem(7,  QTableWidgetItem("支払先"))
            self.ui.tableWidget.resizeColumnToContents(7)
            self.ui.tableWidget.setHorizontalHeaderItem(8,  QTableWidgetItem(" 支払先名 "))
            self.ui.tableWidget.resizeColumnToContents(8)
            self.ui.tableWidget.setHorizontalHeaderItem(9,  QTableWidgetItem(" 支払日 "))
            self.ui.tableWidget.resizeColumnToContents(9)
            self.ui.tableWidget.setHorizontalHeaderItem(10,  QTableWidgetItem("区分"))
            self.ui.tableWidget.resizeColumnToContents(10)            
            self.ui.tableWidget.setHorizontalHeaderItem(11,  QTableWidgetItem(" 削除 "))
            self.ui.tableWidget.resizeColumnToContents(11)
            self.ui.tableWidget.setHorizontalHeaderItem(12,  QTableWidgetItem("明細番号"))
            self.ui.tableWidget.resizeColumnToContents(12)

            self.ui.lineEdit.setFocus()

            self.show()

        def keyPressEvent(self, event):
                    super().keyPressEvent(event)
                    #tablewidget copy
                    if event.key() == Qt.Key_C and (event.modifiers() & Qt.ControlModifier):
                        self.ui.tableWidget.copied_cells = sorted(self.ui.tableWidget.selectedIndexes())
                    #tablewidget paste
                    elif event.key() == Qt.Key_V and (event.modifiers() & Qt.ControlModifier):
                        r = self.ui.tableWidget.currentRow() - self.ui.tableWidget.copied_cells[0].row()
                        c = self.ui.tableWidget.currentColumn() - self.ui.tableWidget.copied_cells[0].column()
                        for cell in self.ui.tableWidget.copied_cells:
                            item = QTableWidgetItem(cell.data())
                            if cell.column() == 2 or cell.column() == 6 or cell.column() == 8:
                               item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                            elif cell.column() == 10:
                               item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
                            else:
                               item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                            self.ui.tableWidget.setItem(cell.row() + r, cell.column() + c, item)
                    #clipboard copy
                    elif event.key() == Qt.Key.Key_S and (event.modifiers() & Qt.KeyboardModifier.ControlModifier):
                        copied_cells = sorted(self.ui.tableWidget.selectedIndexes())

                        copy_text = ''
                        max_column = copied_cells[-1].column()
                        for c in copied_cells:
                            copy_text += self.ui.tableWidget.item(c.row(), c.column()).text()
                            if c.column() == max_column:
                               copy_text += '\n'
                            else:
                               copy_text += '\t'

                        QApplication.clipboard().setText(copy_text)

        def ctrl_c(self):
            #tablewidget copy
            self.ui.tableWidget.copied_cells = sorted(self.ui.tableWidget.selectedIndexes())

        def ctrl_v(self):
            #tablewidget paste
            r = self.ui.tableWidget.currentRow() - self.ui.tableWidget.copied_cells[0].row()
            c = self.ui.tableWidget.currentColumn() - self.ui.tableWidget.copied_cells[0].column()
            for cell in self.ui.tableWidget.copied_cells:
                item = QTableWidgetItem(cell.data())
                if cell.column() == 2 or cell.column() == 6 or cell.column() == 8:
                   item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                elif cell.column() == 10:
                   item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
                else:
                   item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                self.ui.tableWidget.setItem(cell.row() + r, cell.column() + c, item)

        def ctrl_s(self):
            #clipboard copy
            copied_cells = sorted(self.ui.tableWidget.selectedIndexes())

            copy_text = ''
            max_column = copied_cells[-1].column()
            for c in copied_cells:
                copy_text += self.ui.tableWidget.item(c.row(), c.column()).text()
                if c.column() == max_column:
                   copy_text += '\n'
                else:
                   copy_text += '\t'

            QApplication.clipboard().setText(copy_text)

        def eventFilter(self, source, event):
                    if (event.type() == QtCore.QEvent.KeyPress and
                        event.key() in (QtCore.Qt.Key_Return, QtCore.Qt.Key_Enter)):
                            # ensure that the table receives the key event first
                            res = super().eventFilter(source, event)
                            current = self.ui.tableWidget.currentIndex()

                            if current.column() == 10:
                               nextIndex = current.sibling(current.row() + 1, 0)
                            else:
                                nextIndex = current.sibling(current.row() , current.column()+1)

                            if nextIndex.isValid():
                                self.ui.tableWidget.setCurrentIndex(nextIndex)
                                self.ui.tableWidget.edit(nextIndex)
                            return res
                    #return super().eventFilter(source, event)

                    if (event.type() == QtCore.QEvent.FocusOut):
                            # ensure that the table receives the key event first
                            res = super().eventFilter(source, event)
                            current = self.ui.tableWidget.currentIndex()

                            if current.column() == 1:
                                valstr=str(self.ui.tableWidget.item(current.row(),0).text())
                                x = self.hzkchk(valstr)
                                if x == 1:
                                   nextIndex = current.sibling(current.row() , 0)
                                   if nextIndex.isValid():
                                      self.ui.tableWidget.setCurrentIndex(nextIndex)
                                      self.ui.tableWidget.edit(nextIndex)
                                return res

                            if current.column() == 6:
                                try:
                                    valint = int(self.ui.tableWidget.item(current.row(),5).text())
                                except:
                                    valint = 0
                                else:
                                    pass
                                x = self.readtorihikisaki(valint)
                                item6 = QTableWidgetItem(str(x[1]))
                                self.ui.tableWidget.setItem(current.row(), 6, item6)
                                self.ui.tableWidget.resizeColumnToContents(6)
                                nextIndex = current.sibling(current.row() , current.column()+1)
                                if nextIndex.isValid():
                                    self.ui.tableWidget.setCurrentIndex(nextIndex)
                                    self.ui.tableWidget.edit(nextIndex)
                                return res

                            if current.column() == 8:
                                try:
                                    valint=int(self.ui.tableWidget.item(current.row(),7).text())
                                except:
                                    valint = 0
                                else:
                                    pass
                                x = self.readtorihikisaki(valint)
                                item8 = QTableWidgetItem(str(x[1]))
                                self.ui.tableWidget.setItem(current.row(), 8, item8)
                                self.ui.tableWidget.resizeColumnToContents(8)
                                nextIndex = current.sibling(current.row() , current.column()+1)
                                if nextIndex.isValid():
                                    self.ui.tableWidget.setCurrentIndex(nextIndex)
                                    self.ui.tableWidget.edit(nextIndex)
                                return res

                            if current.column() == 10:
                                valstr=str(self.ui.tableWidget.item(current.row(),9).text())
                                x = self.hzkchk(valstr)
                                if x == 1:
                                   nextIndex = current.sibling(current.row() , 9)
                                   if nextIndex.isValid():
                                      self.ui.tableWidget.setCurrentIndex(nextIndex)
                                      self.ui.tableWidget.edit(nextIndex)
                                return res

                    return super().eventFilter(source, event)

        def sub_dlg(self):
            dialog = Subkentorihikisaki()
            if dialog.exec():
                r = self.ui.tableWidget.currentRow()
                c = self.ui.tableWidget.currentColumn()
                item = QTableWidgetItem(dialog.dlg.lineEdit.text())
                item.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                self.ui.tableWidget.setItem(r,c, item)
                self.ui.tableWidget.setFocus()

        def hzkchk(self,hizuke):
            if hizuke == "" or hizuke == "0":
               err = 0
            elif hizuke != "":
               hizukestr = str(hizuke)
               try:
                   dte = datetime.strptime(hizukestr, '%Y%m%d')
                   err = 0
               except:
                   #QMessageBox.warning(self,'日付エラー','日付エラー')
                   err = 1
               else:
                   pass
            return err

        def readtorihikisaki(self,code):
            try:
                cursor = conn.cursor(as_dict=True)
                sql = "select * from 取引先マスター"
                sql += " where 取引先コード = "  + str(code)

                cursor.execute(sql)
                results = cursor.fetchall()
                sonzai = 0
                torihikisakimei = ""
                for res in results:
                    torihikisakimei = str(res["取引先名"])
                    sonzai = 1

            except Exception as e:
                QMessageBox.warning(self,'エラー',sql + e.args)

            else:
                pass

            cursor.close()

            return (sonzai,torihikisakimei)

        def end(self):
            self.close()

        def torikeshi(self):
            self.ui.lineEdit.setText('')
            self.ui.lineEdit_2.setText('')
            self.ui.tableWidget.setRowCount(0)
            self.ui.lineEdit.setFocus()

        def edit(self):
            x = self.hzkchk(self.ui.lineEdit.text())
            if x == 1:
               QMessageBox.warning(self,'エラー','開始日付エラー')
               self.ui.lineEdit.setFocus
            else:
               self.ui.lineEdit.focusNextChild()

        def select(self):
            x = self.hzkchk(self.ui.lineEdit_2.text())
            if x == 1:
               QMessageBox.warning(self,'エラー','終了日付エラー')
               self.ui.lineEdit_2.setFocus
               return
            else:
               pass

            codestt = self.ui.lineEdit.text()
            codeend = self.ui.lineEdit_2.text()
            try:
                codesttint = int(codestt)
                codeendint = int(codeend)
            except:
                QMessageBox.warning(self,'エラー','数値エラー')
                self.ui.lineEdit.setFocus()
                return
            else:
                if codesttint < 0 or codesttint > 99991231:
                   QMessageBox.warning(self,'エラー','開始日付エラー')
                   self.ui.lineEdit.setFocus()
                   return

                if codeendint < 0 or codeendint > 99991231:
                   QMessageBox.warning(self,'エラー','終了日付エラー')
                   self.ui.lineEdit_2.setFocus()
                   return

                if codesttint > codeendint:
                   QMessageBox.warning(self,'エラー','日付範囲エラー')
                   self.ui.lineEdit.setFocus()
                   return


            try:
                cursor = conn.cursor(as_dict=True)

                sql = "select 取引明細.*,coalesce(T.取引先名,'') as 取引先名称,coalesce(S.取引先名,'') as 支払先名称 from 取引明細"
                sql += " left outer join 取引先マスター as T on 取引明細.取引先=T.取引先コード"
                sql += " left outer join 取引先マスター as S on 取引明細.支払先=S.取引先コード"
                sql += " where 取引日 >= "  + str(codesttint)
                sql += " and 取引日 <= "  + str(codeendint)
                sql += " order by 取引日,連番"

                cursor.execute(sql)
                results = cursor.fetchall()
                self.ui.tableWidget.setRowCount(cursor.rowcount+20)
                row = 0
                for res in results:
                    item0 = QTableWidgetItem(str(res["取引日"]))
                    item1 = QTableWidgetItem(str(res["連番"]))
                    item2 = QTableWidgetItem(str(res["摘要"]))
                    item3 = QTableWidgetItem(str(res["入金額"]))
                    item4 = QTableWidgetItem(str(res["出金額"]))
                    item5 = QTableWidgetItem(str(res["取引先"]))
                    item6 = QTableWidgetItem(str(res["取引先名称"]))
                    item7 = QTableWidgetItem(str(res["支払先"]))
                    item8 = QTableWidgetItem(str(res["支払先名称"]))
                    item9 = QTableWidgetItem(str(res["支払日"]))
                    item10 = QTableWidgetItem(str(res["取引区分"]))

                    widget = QWidget()
                    checkbox = QCheckBox("check")
                    widget.checkState = checkbox.checkState()
                    #checkbox.setCheckState(Qt.Checked)
                    playout = QHBoxLayout(widget)
                    playout.addWidget(checkbox)
                    playout.setAlignment(Qt.AlignCenter)
                    playout.setContentsMargins(0,0,0,0)
                    widget.setLayout(playout)

                    item12 = QTableWidgetItem(str(res["明細番号"]))

                    #item6.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
                    #item8.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
                    item12.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)

                    #v AlignVCenter:80 Top:20 Bottom:40 Baseline:100
                    #h AlignHCenter:4 Left:1 Right:2 Justify:8
                    item0.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item1.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item2.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                    item3.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item4.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item5.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item6.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                    item7.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item8.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                    item9.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item10.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
                    item12.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)

                    self.ui.tableWidget.setItem(row, 0,item0)
                    self.ui.tableWidget.resizeColumnToContents(0)
                    self.ui.tableWidget.setItem(row, 1, item1)
                    self.ui.tableWidget.resizeColumnToContents(1)
                    self.ui.tableWidget.setItem(row, 2, item2)
                    self.ui.tableWidget.resizeColumnToContents(2)
                    self.ui.tableWidget.setItem(row, 3, item3)
                    self.ui.tableWidget.resizeColumnToContents(3)
                    self.ui.tableWidget.setItem(row, 4, item4)
                    self.ui.tableWidget.resizeColumnToContents(4)
                    self.ui.tableWidget.setItem(row, 5, item5)
                    self.ui.tableWidget.resizeColumnToContents(5)
                    self.ui.tableWidget.setItem(row, 6, item6)
                    self.ui.tableWidget.resizeColumnToContents(6)
                    self.ui.tableWidget.setItem(row, 7, item7)
                    self.ui.tableWidget.resizeColumnToContents(7)
                    self.ui.tableWidget.setItem(row, 8, item8)
                    self.ui.tableWidget.resizeColumnToContents(8)
                    self.ui.tableWidget.setItem(row, 9, item9)
                    self.ui.tableWidget.resizeColumnToContents(9)
                    self.ui.tableWidget.setItem(row, 10, item10)
                    self.ui.tableWidget.resizeColumnToContents(10)

                    self.ui.tableWidget.setCellWidget(row, 11, checkbox)
                    #self.ui.tableWidget.resizeColumnToContents(11)

                    self.ui.tableWidget.setItem(row, 12, item12)
                    self.ui.tableWidget.resizeColumnToContents(12)
                    row += 1

                for i in range(20):
                    item0 = QTableWidgetItem("")
                    item1 = QTableWidgetItem("")
                    item2 = QTableWidgetItem("")
                    item3 = QTableWidgetItem("")
                    item4 = QTableWidgetItem("")
                    item5 = QTableWidgetItem("")
                    item6 = QTableWidgetItem("")
                    item7 = QTableWidgetItem("")
                    item8 = QTableWidgetItem("")
                    item9 = QTableWidgetItem("")
                    item10 = QTableWidgetItem("")

                    widget = QWidget()
                    checkbox = QCheckBox()
                    widget.checkState = checkbox.checkState
                    #checkbox.setCheckState(Qt.Checked)
                    playout = QHBoxLayout(widget)
                    playout.addWidget(checkbox)
                    playout.setAlignment(Qt.AlignCenter)
                    playout.setContentsMargins(0,0,0,0)
                    widget.setLayout(playout)

                    item12 = QTableWidgetItem("")

                    #item6.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
                    #item8.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
                    item12.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)

                    #v AlignVCenter:80 Top:20 Bottom:40 Baseline:100
                    #h AlignHCenter:4 Left:1 Right:2 Justify:8
                    item0.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item1.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item2.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                    item3.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item4.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item5.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item6.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                    item7.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item8.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignLeft)
                    item9.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)
                    item10.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignHCenter)
                    item12.setTextAlignment(QtCore.Qt.AlignVCenter | QtCore.Qt.AlignRight)

                    self.ui.tableWidget.setItem(row, 0,item0)
                    self.ui.tableWidget.resizeColumnToContents(0)
                    self.ui.tableWidget.setItem(row, 1, item1)
                    self.ui.tableWidget.resizeColumnToContents(1)
                    self.ui.tableWidget.setItem(row, 2, item2)
                    self.ui.tableWidget.resizeColumnToContents(2)
                    self.ui.tableWidget.setItem(row, 3, item3)
                    self.ui.tableWidget.resizeColumnToContents(3)
                    self.ui.tableWidget.setItem(row, 4, item4)
                    self.ui.tableWidget.resizeColumnToContents(4)
                    self.ui.tableWidget.setItem(row, 5, item5)
                    self.ui.tableWidget.resizeColumnToContents(5)
                    self.ui.tableWidget.setItem(row, 6, item6)
                    self.ui.tableWidget.resizeColumnToContents(6)
                    self.ui.tableWidget.setItem(row, 7, item7)
                    self.ui.tableWidget.resizeColumnToContents(7)
                    self.ui.tableWidget.setItem(row, 8, item8)
                    self.ui.tableWidget.resizeColumnToContents(8)
                    self.ui.tableWidget.setItem(row, 9, item9)
                    self.ui.tableWidget.resizeColumnToContents(9)
                    self.ui.tableWidget.setItem(row, 10, item10)
                    self.ui.tableWidget.resizeColumnToContents(10)
                    self.ui.tableWidget.setCellWidget(row, 11, checkbox)
                    #self.ui.tableWidget.resizeColumnToContents(11)
                    self.ui.tableWidget.setItem(row, 12, item12)
                    self.ui.tableWidget.resizeColumnToContents(12)
                    row += 1

                self.ui.tableWidget.setFocus()

            except Exception as e:
                QMessageBox.warning(self,'エラー',sql + e.args)
            else:
                pass

            cursor.close()

            return

        def koshin(self):
            row = self.ui.tableWidget.rowCount()
            column = self.ui.tableWidget.columnCount()
            results = []
            for r in range(row):
                A = []
                for c in range(column):
                    if c !=  11:
                       it = self.ui.tableWidget.item(r,c)
                       text = ""
                       if it:
                            text = it.text()
                       else:
                            print("!")
                    else:
                       if self.ui.tableWidget.cellWidget(r,c).checkState() == QtCore.Qt.Checked:
                            text = "true"
                       else:
                            text = "false"

                    A.append(text)

                results.append(A,)

            row=0
            for res in results:
                row += 1
                if res[0] != "":
                   #print(res[0],res[1],res[2],res[3],res[4],res[5], res[6],res[7],res[8],res[9],res[10],res[11],res[12] )
                   f0 = res[0]
                   f1 = res[1]
                   f2 = res[2]
                   f3 = res[3]
                   f4 = res[4]
                   f5 = res[5]
                   f6 = res[6]
                   f7 = res[7]
                   f8 = res[8]
                   f9 = res[9]
                   f10 = res[10]
                   f11 = res[11]
                   f12 = res[12]
                   x = self.hzkchk(f0)
                   if x == 1:
                       QMessageBox.warning(self,'エラー','日付エラー' + str(row) + "行目")
                       return
                   if f1 == "":
                       f1 = "0"
                   if f3 == "":
                       f3 = "0"
                   if f4 == "":
                       f4 = "0"
                   if f5 == "":
                       f5 = "0"
                   if f7 == "":
                       f7 = "0"
                   if f9 == "":
                       f9 = "0"
                   if f10 == "":
                       f10 = "0"
                   if f12 == "":
                       f12 = "0"
                   try:
                       f0int = int(f0)
                       f1int = int(f1)
                       f3int = int(f3)
                       f4int = int(f4)
                       f5int = int(f5)
                       f7int = int(f7)
                       f9int = int(f9)
                       f10int = int(f10)
                       f12int = int(f12)
                   except:
                       QMessageBox.warning(self,'エラー','数値エラー' + str(row) + "行目")
                   else:
                       pass

            row=0
            for res in results:
                row += 1
                if res[0] != "":
                   #print(res[0],res[1],res[2],res[3],res[4],res[5], res[6],res[7],res[8],res[9],res[10],res[11],res[12] )
                   f0 = res[0]
                   f1 = res[1]
                   f2 = res[2]
                   f3 = res[3]
                   f4 = res[4]
                   f5 = res[5]
                   f6 = res[6]
                   f7 = res[7]
                   f8 = res[8]
                   f9 = res[9]
                   f10 = res[10]
                   f11 = res[11]
                   f12 = res[12]
                   x = self.hzkchk(f0)
                   if x == 1:
                      QMessageBox.warning(self,'エラー','日付エラー' + str(row) + "行目")
                      return
                   if f1 == "":
                      f1 = "0"
                   if f3 == "":
                      f3 = "0"
                   if f4 == "":
                      f4 = "0"
                   if f5 == "":
                      f5 = "0"
                   if f7 == "":
                      f7 = "0"
                   if f9 == "":
                      f9 = "0"
                   if f10 == "":
                      f10 = "0"
                   if f12 == "":
                      f12 = "0"
                   try:
                      f0int = int(f0)
                      f1int = int(f1)
                      f3int = int(f3)
                      f4int = int(f4)
                      f5int = int(f5)
                      f7int = int(f7)
                      f9int = int(f9)
                      f10int = int(f10)
                      f12int = int(f12)
                   except:
                      QMessageBox.warning(self,'エラー','数値エラー' + str(row) + "行目")
                   else:
                      pass

                   if f12int == 0:
                      #新規
                      sqlstr = " insert into 取引明細 "
                      sqlstr += " (明細番号,取引日,連番,摘要,入金額,出金額,取引先,支払先,支払日,取引区分) "
                      sqlstr += " values( " + "(select coalesce(max(明細番号),0)+1 from 取引明細)" + ","
                      sqlstr += f0 + "," + f1 + ",'" + f2 + "'," + f3 + "," + f4 + "," + f5 + ","
                      sqlstr += f7 + "," + f9 + "," + f10 + ")"

                   else:
                      #既存
                      if f11 == "false":
                         sqlstr = " update 取引明細 set "
                         sqlstr += " 取引日=" + f0
                         sqlstr += ",連番=" + f1
                         sqlstr += ",摘要='" + f2 + "'"
                         sqlstr += ",入金額=" + f3
                         sqlstr += ",出金額=" + f4
                         sqlstr += ",取引先=" + f5
                         sqlstr += ",支払先=" + f7
                         sqlstr += ",支払日=" + f9
                         sqlstr += ",取引区分=" + f10
                         sqlstr += " where 明細番号=" + f12
                      else:
                         sqlstr =  " delete from 取引明細 "
                         sqlstr += " where 明細番号=" + f12

                   try:
                      cursor = conn.cursor(as_dict=True)
                      cursor.execute(sqlstr)

                   except:
                      QMessageBox.warning(self,'エラー',sqlstr)
                      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)
    w = inptorihiki()
    w.show()
    #sys.exit(app.exec_())
    sys.exit(app.exec())

実行画面

検索ボタン押下、選択後OKボタン押下で値がセットされます。

登録ボタンで取引明細へ追加、更新、削除を行います。明細番号がある場合が、更新か削除の対象になります。削除のチェックボックスへチェックすると削除されます。

明細番号がない場合は、新規として追加されます。新規分は、20行用意しています。不足の場合は、登録後再度日付指定します。20行追加できます。

取引先と支払先は、コード入力で取引先マスターより名称を表示します。検索ボタンで検索画面からコードを取得できます。セル移動は、Enterキーを使用します。

テーブル内の選択範囲のコピーは、Ctrl + c テーブルへの貼り付けは、Ctrl + v クリップボードへのコピーは、Ctrl + s で行います。右のボタンも同様。

-python

PAGE TOP