def showMembsPayments():
cmd = 'SELECT M.FirstName, M.LastName, Amount, Date, Type, Comment FROM FinancialTrans F, Member M WHERE M.ID={0} AND F.MID=M.ID ORDER BY DATE ASC'.format(
self.parent.currentMember.ID)
query = self.db.execQuery(
cmd,
'TabTransMan.py -> showMembsPayments -> showPaymentWindow')
model = QtSql.QSqlQueryModel()
model.setQuery(query)
model.setHeaderData(0, QtCore.Qt.Horizontal, 'First Name')
model.setHeaderData(1, QtCore.Qt.Horizontal, 'Last Name')
model.setHeaderData(2, QtCore.Qt.Horizontal, 'Ammount')
model.setHeaderData(3, QtCore.Qt.Horizontal, 'Date')
model.setHeaderData(4, QtCore.Qt.Horizontal, 'Type')
model.setHeaderData(5, QtCore.Qt.Horizontal, 'Comment')
self.viewMembers = QtGui.QTableView(None)
self.viewMembers.setWindowTitle('Member Transactions')
self.viewMembers.setWindowIcon(self.parent.icon)
self.viewMembers.resize(600, 500)
self.viewMembers.setModel(model)
self.viewMembers.setEditTriggers(
QtGui.QAbstractItemView.NoEditTriggers)
self.viewMembers.setSelectionMode(
QtGui.QAbstractItemView.SingleSelection)
self.viewMembers.setSelectionBehavior(
QtGui.QAbstractItemView.SelectRows)
self.viewMembers.resizeColumnsToContents()
self.viewMembers.show()
def show_shit_list(self):
query = self.db.getShitListQuery()
model = QtSql.QSqlQueryModel()
model.setQuery(query)
model.setHeaderData(0, QtCore.Qt.Horizontal, 'Member First Name')
model.setHeaderData(1, QtCore.Qt.Horizontal, 'Member Last Name')
model.setHeaderData(2, QtCore.Qt.Horizontal, 'Gear Name')
model.setHeaderData(3, QtCore.Qt.Horizontal, 'Gear ID')
model.setHeaderData(4, QtCore.Qt.Horizontal, 'Check Out Date/Time')
model.setHeaderData(5, QtCore.Qt.Horizontal, 'Due Date')
self.viewShitList.setWindowTitle('Shit List')
self.viewShitList.setWindowIcon(self.parent.icon)
self.viewShitList.resize(600, 500)
self.viewShitList.setModel(model)
self.viewShitList.setEditTriggers(
QtGui.QAbstractItemView.NoEditTriggers)
self.viewShitList.setSelectionMode(
QtGui.QAbstractItemView.SingleSelection)
self.viewShitList.setSelectionBehavior(
QtGui.QAbstractItemView.SelectRows)
self.viewShitList.resizeColumnsToContents()
self.viewShitList.show()
def openTable(self):
selectedItem = self.ui.treeWidgetDB.currentItem()
table = str(selectedItem.text(0))
cmd = "SELECT * FROM %s" % table
model = QtSql.QSqlQueryModel()
model.setQuery(cmd)
self.ui.tableViewTable.setModel(model)
def createModel(self):
self.model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("Select * from Cars")
self.model.setQuery(query)
self.model.removeColumn(0)
self.model.setHeaderData(0, QtCore.Qt.Horizontal, "Name")
self.model.setHeaderData(1, QtCore.Qt.Horizontal, "Price")
def button_show_gear_history_table(self):
# Clear the message window
self.parent.set_stat_message()
if self.parent.currentGear:
cmd = (
'SELECT (M1.FirstName||'
'||M1.LastName), (M2.FirstName||'
'||M2.LastName), G.Name, '
'strftime("%m/%d/%Y %H:%M:%S", AT.CheckOutDateTime), strftime("%m/%d/%Y", AT.DueDate), '
'strftime("%m/%d/%Y %H:%M:%S", AT.CheckInDateTime) '
'FROM ArchiveTrans AT, Member M1, Member M2, Gear G WHERE AT.GID="{GID}" '
'AND AT.MID_OUT=M1.ID AND AT.GID=G.ID AND AT.MID_IN=M2.ID '
'ORDER BY AT.CheckInDateTime ASC').format(
GID=self.parent.currentGear.ID)
query = self.db.execQuery(cmd, 'TabGearMan.py -> showGearHist')
model = QtSql.QSqlQueryModel()
model.setQuery(query)
model.setHeaderData(0, QtCore.Qt.Horizontal, 'Rented By')
model.setHeaderData(1, QtCore.Qt.Horizontal, 'Returned By')
model.setHeaderData(2, QtCore.Qt.Horizontal, 'Gear Name')
model.setHeaderData(3, QtCore.Qt.Horizontal, 'Checkout Date/Time')
model.setHeaderData(4, QtCore.Qt.Horizontal, 'DueDate')
model.setHeaderData(5, QtCore.Qt.Horizontal, 'Checkin Date/Time')
self.view_gear_hist.setWindowTitle('Gear Transaction History')
self.view_gear_hist.setWindowIcon(self.parent.icon)
self.view_gear_hist.resize(600, 500)
self.view_gear_hist.setModel(model)
self.view_gear_hist.setEditTriggers(
QtGui.QAbstractItemView.NoEditTriggers)
self.view_gear_hist.setSelectionMode(
QtGui.QAbstractItemView.SingleSelection)
self.view_gear_hist.setSelectionBehavior(
QtGui.QAbstractItemView.SelectRows)
self.view_gear_hist.clicked.connect(
self.get_gear_id_from_gear_history_table)
self.view_gear_hist.resizeColumnsToContents()
self.view_gear_hist.show()
else:
self.parent.set_stat_message(Util.noActiveGear, c='red')
if self.gDissAmbSearch.count(
) > 1 and self.gDissAmbSearch.currentIndex() == 1:
self.gDissAmbSearch.showPopup()
def Button_showTrans(self):
# Clear the message window
self.parent.set_stat_message()
cmd = 'SELECT M.FirstName, M.LastName, strftime("%m/%d/%Y", M.Birthday), G.Name, T.GID, ' \
'strftime("%m/%d/%Y %H:%M:%S", T.CheckOutDateTime), strftime("%m/%d/%Y", T.DueDate) ' \
'FROM Transactions T ' \
'JOIN Member M ON T.MID=M.ID ' \
'JOIN Gear G ON T.GID=G.ID'# ORDER BY T.DueDate ASC')
query = self.db.execQuery(cmd, 'database.py -> showTrans')
model = QtSql.QSqlQueryModel()
model.setQuery(query)
model.setHeaderData(0, QtCore.Qt.Horizontal, 'First Name')
model.setHeaderData(1, QtCore.Qt.Horizontal, 'Last Name')
model.setHeaderData(2, QtCore.Qt.Horizontal, 'Birthday')
model.setHeaderData(3, QtCore.Qt.Horizontal, 'Gear Name')
model.setHeaderData(4, QtCore.Qt.Horizontal, 'Gear ID')
model.setHeaderData(5, QtCore.Qt.Horizontal, 'Check Out Date/Time')
model.setHeaderData(6, QtCore.Qt.Horizontal, 'Due Date')
self.viewTransactions = QtGui.QTableView()
self.viewTransactions.setWindowTitle('Active Transactions List')
self.viewTransactions.setWindowIcon(self.parent.icon)
self.viewTransactions.resize(600, 500)
self.viewTransactions.setModel(model)
self.viewTransactions.setEditTriggers(
QtGui.QAbstractItemView.NoEditTriggers)
self.viewTransactions.setSelectionMode(
QtGui.QAbstractItemView.SingleSelection)
self.viewTransactions.setSelectionBehavior(
QtGui.QAbstractItemView.SelectRows)
self.viewTransactions.clicked.connect(
self.get_member_id_and_gear_id_from_transaction_table)
self.viewTransactions.resizeColumnsToContents()
self.viewTransactions.setSortingEnabled(True)
# self.viewTransactions.sortByColumn(7, QtCore.Qt.AscendingOrder)
self.viewTransactions.show()
def executeSQL(self):
cmd = self.ui.txtSqlEdit.text()
model = QtSql.QSqlQueryModel()
model.setQuery(cmd)
self.ui.tableViewTable.setModel(model)
def __init__(self):
super(MainForm, self).__init__()
#Database:
self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
self.db.setHostName("localhost")
self.db.setDatabaseName("voc-app-db-test")
self.db.setUserName("root")
self.db.setPassword("passwd")
ok = self.db.open()
if not ok:
QtGui.QMessageBox.warning(self, "Error", "Invalid database!")
return
# GUI:
hbox = QtGui.QHBoxLayout(self)
self.setLayout(hbox)
left_vbox = QtGui.QVBoxLayout()
right_vbox = QtGui.QVBoxLayout()
grid = QtGui.QGridLayout()
grid.setSpacing(10)
self.db_table = QtGui.QTableView(self)
hbox.addLayout(left_vbox)
hbox.addLayout(right_vbox)
left_vbox.addWidget(self.db_table)
left_vbox.addLayout(grid)
label_fn = QtGui.QLabel('First Name:')
label_ln = QtGui.QLabel('Last Name:')
self.e_fn = QtGui.QLineEdit()
self.e_ln = QtGui.QLineEdit()
grid.addWidget(label_fn, 1, 0)
grid.addWidget(self.e_fn, 1, 1)
grid.addWidget(label_ln, 2, 0)
grid.addWidget(self.e_ln, 2, 1)
self.clear_btn = QtGui.QPushButton("Clear")
self.delete_btn = QtGui.QPushButton("Delete")
self.refresh_btn = QtGui.QPushButton("Refresh")
self.insert_btn = QtGui.QPushButton("Insert")
self.update_btn = QtGui.QPushButton("Update")
self.exit_btn = QtGui.QPushButton("Exit")
right_vbox.addWidget(self.clear_btn)
right_vbox.addWidget(self.delete_btn)
right_vbox.addWidget(self.refresh_btn)
right_vbox.addStretch()
right_vbox.addWidget(self.insert_btn)
right_vbox.addWidget(self.update_btn)
right_vbox.addWidget(self.exit_btn)
self.setGeometry(300, 100, 800, 600)
self.setWindowTitle('Simpsons')
#Event Listeners:
self.refresh_btn.clicked.connect(self.refresh_btn_clicked)
self.insert_btn.clicked.connect(self.insert_btn_clicked)
self.delete_btn.clicked.connect(self.delete_btn_clicked)
self.clear_btn.clicked.connect(self.clear_btn_clicked)
self.update_btn.clicked.connect(self.update_btn_clicked)
self.exit_btn.clicked.connect(self.exit_btn_clicked)
#Table:
self.model = QtSql.QSqlQueryModel()
self.model.setQuery("SELECT * FROM simpsons")
print '0 rowcount = {}'.format(self.model.rowCount())
print '0 columncount = {}'.format(self.model.columnCount())
self.model.setHeaderData(1, QtCore.Qt.Horizontal,
self.tr("First Name"))
self.model.setHeaderData(2, QtCore.Qt.Horizontal, self.tr("Last Name"))
self.db_table.setModel(self.model)
self.db_table.hideColumn(0) #hide column 'id'
self.db_table.setSelectionBehavior(
QtGui.QAbstractItemView.SelectRows) #select Row
self.db_table.setSelectionMode(
QtGui.QAbstractItemView.SingleSelection) #disable multiselect
self.show()
view = QtGui.QTableView()
views.append(view)
view.setModel(model)
view.setWindowTitle(title)
view.move(100 + offset, 100 + offset)
offset += 20
view.show()
if __name__ == '__main__':
import sys
app = QtGui.QApplication(sys.argv)
if not connection.createConnection():
sys.exit(1)
plainModel = QtSql.QSqlQueryModel()
editableModel = EditableSqlModel()
customModel = CustomSqlModel()
initializeModel(plainModel)
initializeModel(editableModel)
initializeModel(customModel)
createView("Plain Query Model", plainModel)
createView("Editable Query Model", editableModel)
createView("Custom Query Model", customModel)
sys.exit(app.exec_())
