import sys
import os
from PySide6.QtWidgets import *
from PySide6.QtGui import *
from PySide6.QtCore import *
from PySide6.QtSql import *

class Demo(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.parent = parent
        self.dbFile=os.path.join(self.parent.getAppDir(),'test.db')
        layout = QVBoxLayout()
        layout.setAlignment(Qt.AlignmentFlag.AlignTop)
        
        toolBar=QToolBar()
        self.filterEdit=QLineEdit()
        self.filterEdit.setText('user')
        toolBar.addWidget(self.filterEdit)

        btn=QPushButton('Filter')
        btn.clicked.connect(self.Filter)
        toolBar.addWidget(btn)

        btn=QPushButton('SelectTable')
        btn.clicked.connect(self.SelectTable)
        toolBar.addWidget(btn)

        btn=QPushButton('OrderBy ASC')
        btn.clicked.connect(self.OrderByASC)
        toolBar.addWidget(btn)

        btn=QPushButton('OrderBy DESC')
        btn.clicked.connect(self.OrderByDESC)
        toolBar.addWidget(btn)

        btn=QPushButton('Update')
        btn.clicked.connect(self.Update)
        toolBar.addWidget(btn)

        btn=QPushButton('RevertAll')
        btn.clicked.connect(self.RevertAll)
        toolBar.addWidget(btn)

        btn=QPushButton('Add')
        btn.clicked.connect(self.Add)
        toolBar.addWidget(btn)

        btn=QPushButton('Insert')
        btn.clicked.connect(self.Insert)
        toolBar.addWidget(btn)

        btn=QPushButton('Delete')
        btn.clicked.connect(self.Delete)
        toolBar.addWidget(btn)

        layout.addWidget(toolBar)
        db=QSqlDatabase().addDatabase("QSQLITE")
        db.setDatabaseName(self.dbFile)
        
        self.model=QSqlTableModel(db=db)

        self.tableView=QTableView()
        self.tableView.setSelectionBehavior(QTableView.SelectionBehavior.SelectRows)#行选择
        self.tableView.setModel(self.model)
        layout.addWidget(self.tableView)

        self.setLayout(layout)
    def Filter(self):        
        if len(self.filterEdit.text())==0:return
        filter=self.filterEdit.text()
        self.model.database().open()
        self.model.setTable('User')
        #匹配模式 Name='str'; Name LIKE '%str';Name LIKE 'str%'; Name LIKE '%str%'
        #self.model.setFilter(f"Name='{filter}'")#完全匹配
        #self.model.setFilter(f"Name LIKE '{filter}%'")#以XX开头
        #self.model.setFilter(f"Name LIKE '%{filter}'")#以XX结尾
        self.model.setFilter(f"Name LIKE '%{filter}%'")#包含XX
        self.model.select()#执行查询
        self.model.database().close()
    def SelectTable(self):
        self.model.database().open()        
        self.model.setTable('User')
        self.model.select()#执行查询
        self.model.database().close()
    def OrderByASC(self):
        self.model.database().open()        
        self.model.setTable('User')
        self.model.setSort(0,Qt.SortOrder.AscendingOrder)
        self.model.select()#执行查询
        self.model.database().close()
    def OrderByDESC(self):
        self.model.database().open()        
        self.model.setTable('User')
        self.model.setSort(0,Qt.SortOrder.DescendingOrder)
        self.model.select()#执行查询
        self.model.database().close()
    def Update(self):
        index=self.tableView.currentIndex()
        if index.isValid()==False or len(self.filterEdit.text())==0:return
        self.model.database().open() 
        self.model.database().transaction()#开启事务
        #操作1
        #操作2
        self.model.setData(self.model.index(index.row(),1),self.filterEdit.text())
        if self.model.submitAll():
            self.model.database().commit()
        else:
            self.model.database().rollback()
        self.model.database().close()
    def RevertAll(self):
        self.model.database().open() 
        self.model.revertAll()
        self.model.database().close()
    def Add(self):
        self.model.database().open()
        rowCount=self.model.rowCount()
        self.model.insertRow(rowCount)
        self.model.setData(self.model.index(rowCount,0),rowCount)
        self.model.setData(self.model.index(rowCount,1),f'user{rowCount}')
        self.model.submitAll()
        self.model.database().close()
    def Insert(self):
        index=self.tableView.currentIndex()
        if index.isValid()==False:return
        self.model.database().open()
        rowCount=self.model.rowCount()
        self.model.insertRow(index.row())
        self.model.setData(self.model.index(index.row(),0),rowCount)
        self.model.setData(self.model.index(index.row(),1),f'user{rowCount}')
        self.model.submitAll()
        self.model.database().close()
    def Delete(self):
        index=self.tableView.currentIndex()
        if index.isValid()==False:return        
        result= QMessageBox.warning(self,'删除确认','确定删除选中记录？',QMessageBox.StandardButton.Yes,QMessageBox.StandardButton.No)
        if result==QMessageBox.StandardButton.Yes:
            self.model.database().open()        
            self.model.removeRow(index.row())
            self.model.submitAll()
            self.model.database().close()
        else:
            pass
        
def runDemo(parent):
    wigdet =  Demo(parent)
    return wigdet