def select(_):
query = QSqlQueryModel()
query.setQuery(
"SELECT p.Name AS [Товар], SUM(s.Count) AS [Количество], SUM(s.Price) AS [Цена] "
"FROM Sales AS s INNER JOIN Pastries AS p ON s.Pastry = p.Id "
"GROUP BY p.Name")
return query
def parentTagListBetaUpdate(self):
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
parents = ["tags_parent", "yt_tags_parent"]
sort = ["P.parent_tag", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT P.parent_tag, P.parent_tag || ' (' || IFNULL(ct, 0) || ')', ct
FROM (
SELECT parent_tag
FROM {parents[self.tagType]}
WHERE child_tag = (?)
GROUP BY 1
) P LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
WHERE tag_name = (?)
GROUP BY 1
) C
ON P.parent_tag = C.tag_name
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
query = QSqlQuery()
query.prepare(queryTemplate)
childTag = self.childTag
query.addBindValue(childTag)
query.addBindValue(childTag)
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
self.parentTagListBeta.setModel(model)
self.parentTagListBeta.setModelColumn(1)
self.parentTagListBeta.show()
class TabTableNonEditable(InvertedTable):
"""an inverted table presenting a QSqlQueryModel
"""
def __init__(self, log, db, tab_nr, query, headers = None, myfilter = ""):
super().__init__(log, db)
self.nr = tab_nr
self.filter = myfilter
self.headers = headers
self.query = query
self.create_model()
self.invert_model()
self.add_headers()
def create_model(self):
"""creates the table model
"""
q = QSqlQuery(self.query + " " + self.filter)
self.model = QSqlQueryModel()
q.exec_(self.query)
self.model.setQuery(q)
def add_headers(self):
"""adds headers
"""
if self.headers:
for i in self.headers:
column = self.headers[i]
self.model.setHeaderData(i, Qt.Horizontal, column, Qt.DisplayRole)
def refresh(self, myfilter = ""):
"""refreshes the displayed data after data changes in the model
"""
if myfilter:
self.filter = myfilter
self.model.setQuery(self.query + " where " + self.filter)
def tagListUpdate(self):
types = ["tags", "yt_tags"]
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
sort = ["T.tag_name", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct
FROM {types[self.tagType]} T LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
GROUP BY 1
) C
ON T.tag_name = C.tag_name
WHERE T.tag_name LIKE (?)
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
searchText = self.tagSearchBar.text()
query = QSqlQuery()
query.prepare(queryTemplate)
query.addBindValue(f"%{searchText}%")
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
self.tagList.setModel(model)
self.tagList.setModelColumn(1)
self.tagList.show()
self.tagList.selectionModel().selectionChanged.connect(
self.tagListSelectionChanged)
def collect_data(ui, db): #! Please add cute Icons back, sir
num_model = QSqlQueryModel()
cust_model = QSqlQueryModel()
ui.treeView = QtWidgets.QTreeView(ui.comboBox)
db.open()
num_model.setQuery('SELECT Number FROM Room', db)
cust_model.setQuery('SELECT Name, ID, Sex FROM Customer', db)
db.close()
while cust_model.canFetchMore():
cust_model.fetchMore()
ui.comboBox_2.setModel(num_model)
ui.comboBox.setModel(cust_model)
ui.comboBox.setView(ui.treeView)
ui.treeView.setColumnHidden(2, True)
icon4 = QtGui.QIcon()
icon4.addPixmap(QtGui.QPixmap(":/ctmr/48px-Emblem-person-blue.svg.png"))
icon5 = QtGui.QIcon()
icon5.addPixmap(QtGui.QPixmap(":/ctmr/48px-User_icon_3.svg.png"))
i = 0
while i < ui.comboBox.count():
#print(cust_model.index(i, 1).data())
if cust_model.index(i, 2).data() == 'Male':
ui.comboBox.setItemIcon(i, icon4)
else:
ui.comboBox.setItemIcon(i, icon5)
i += 1
def remove_rule(self, rule):
query = QSqlQueryModel()
query.setQuery("delete from rules "\
"where id={}"\
.format(rule.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
def Delete(self):
dltxt, dlok = QInputDialog.getText(
self, 'Удаление',
'Введите ID препарата, который вы хотите удалить:')
if dlok:
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName("db.db")
db.open()
querydl = QSqlQuery()
querydl.prepare("DELETE FROM medications WHERE mId= ( ? )")
querydl.bindValue(0, dltxt)
querydl.exec()
qry = QSqlQuery()
qry = db.exec("SELECT mId AS 'ID',"
"mName AS 'Название',"
"mDescription AS 'Описание',"
"mPrice AS 'Цена' FROM medications")
tabmodel = QSqlQueryModel()
self.ui.tableView_2.setModel(tabmodel)
tabmodel.setQuery(qry)
self.ui.tableView_2.setColumnWidth(2, 310)
header = self.ui.tableView_2.horizontalHeader()
Lheader = self.ui.tableView_2.verticalHeader()
Lheader.setVisible(False)
header.setStretchLastSection(True)
self.ui.tableView_2.show
db.close()
def setupMilitaryCombo(self):
model = QSqlQueryModel(self)
model.setQuery(
"SELECT DISTINCT militaernummer as milnum FROM film WHERE militaernummer IS NOT NULL UNION ALL SELECT DISTINCT militaernummer_alt as milnum FROM film WHERE militaernummer_alt IS NOT NULL ORDER BY milnum",
self.dbm.db)
tv = QTableView()
self.uiMilitaryNumberCombo.setView(tv)
tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded)
tv.setSelectionMode(QAbstractItemView.SingleSelection)
tv.setSelectionBehavior(QAbstractItemView.SelectRows)
tv.setAutoScroll(False)
self.uiMilitaryNumberCombo.setModel(model)
self.uiMilitaryNumberCombo.setModelColumn(0)
self.uiMilitaryNumberCombo.setInsertPolicy(QComboBox.NoInsert)
tv.resizeColumnsToContents()
tv.resizeRowsToContents()
tv.verticalHeader().setVisible(False)
tv.horizontalHeader().setVisible(True)
#tv.setMinimumWidth(tv.horizontalHeader().length())
tv.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
def submission_models(self, db, table_name):
if db.open():
model = QSqlQueryModel()
model.setQuery(
"SELECT run_id,verdict,language,problem_number,time_stamp FROM my_submissions ORDER BY local_run_id DESC"
)
return model
def setupSearchComboBoxByQuery(self, editor, query, modelcolumn=0):
model = QSqlQueryModel(self)
model.setQuery(query, self.dbm.db)
tv = QTableView()
editor.setView(tv)
tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded)
#tv.setSelectionMode(QAbstractItemView.MultiSelection)
tv.setSelectionMode(QAbstractItemView.SingleSelection)
tv.setSelectionBehavior(QAbstractItemView.SelectRows)
tv.setAutoScroll(False)
editor.setModel(model)
editor.setModelColumn(modelcolumn)
editor.setInsertPolicy(QComboBox.NoInsert)
tv.resizeColumnsToContents()
tv.resizeRowsToContents()
tv.verticalHeader().setVisible(False)
tv.horizontalHeader().setVisible(True)
# tv.setMinimumWidth(tv.horizontalHeader().length())
tv.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# FIXME PyQt5 AutoCompletion
#editor.setAutoCompletion(True)
editor.setCurrentIndex(-1)
def tagListAlphaUpdate(self):
video_ids = self.mainWindow.getVideoTableSelectionVideoIds()
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
sort = ["T.tag_name", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct
FROM (
SELECT tag_name FROM {typesLink[self.tagType]}
WHERE video_id IN ({', '.join(['(?)' for video_id in video_ids])})
GROUP BY tag_name
HAVING count(*) = (?)
) T LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
GROUP BY 1
) C
ON T.tag_name = C.tag_name
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
query = QSqlQuery()
query.prepare(queryTemplate)
for video_id in video_ids:
query.addBindValue(video_id)
query.addBindValue(len(video_ids))
query.exec()
self.tagFilterSet = set()
while query.next():
self.tagFilterSet.add(query.value(0))
model = QSqlQueryModel()
model.setQuery(query)
self.tagListAlpha.setModel(model)
self.tagListAlpha.setModelColumn(1)
self.tagListAlpha.show()
def select(_):
query = QSqlQueryModel()
query.setQuery(
"SELECT p.Name AS [Должность], AVG(e.Salary) AS [Зарплата] "
"FROM Employees AS e INNER JOIN Posts AS p ON e.Post = p.Id "
"GROUP BY p.Name")
return query
def __init__(self, tag_number):
super().__init__()
print('processing query...')
qry = QSqlQuery(db)
query = 'SELECT name, ename, startno, starttime FROM name WHERE ecard = %i OR ecard2 = %i' % (
tag_number, tag_number)
qry.prepare(query)
qry.exec()
model = QSqlQueryModel()
model.setQuery(qry)
print(model.rowCount())
mapper = QDataWidgetMapper()
form = QFormLayout()
layout = QVBoxLayout()
first_name = QLineEdit()
start_number = QLineEdit()
form.addRow(QLabel("Startnummer"), start_number)
form.addRow(QLabel("Fornavn"), first_name)
mapper.setModel(model)
mapper.addMapping(first_name, 0)
mapper.addMapping(start_number, 2)
mapper.toFirst()
layout.addLayout(form)
widget = QWidget()
widget.setLayout(layout)
self.setCentralWidget(widget)
#controls = QHBoxLayout()
'''
def btn3f(self):
conn = sqlite3.connect('company.db')
curs = conn.cursor()
curs.execute("select name from sqlite_master where type='table'")
tabs = QTabWidget()
table_name = 'stock_price'
table_model = QSqlQueryModel()
for i, column_data in enumerate(curs.description):
table_model.setHeaderData(i, Qt.Horizontal, column_data[0])
table_model.setQuery(f"select * from {table_name}")
table_view = QTableView()
table_view.setModel(table_model)
tabs.addTab(table_view, table_name)
screen = QWidget()
content = QVBoxLayout()
line1 = QHBoxLayout()
line1.addWidget(tabs)
content.addLayout(line1)
self.k = popup3()
self.k.setGeometry(800, 350, 400, 200)
self.k.setWindowTitle("Stock Prices")
self.k.setLayout(content)
self.k.show()
def select(min_salary):
query = QSqlQueryModel()
query.setQuery(
"SELECT e.Id, e.Name, e.Surname, e.Patronymic, e.Salary, e.Passport, p.Name "
"FROM Employees AS e INNER JOIN Posts AS p ON e.Post = p.Id "
"WHERE e.Salary > %s" % min_salary)
return query
def select(_):
query = QSqlQueryModel()
query.setQuery(
"SELECT c.Name AS [Покупатель], SUM(s.Count) AS [Количество], SUM(s.Price) AS [Цена] "
"FROM Customers AS c INNER JOIN Sales AS s ON s.Customer = c.Id "
"GROUP BY c.Name")
return query
class Widget(QtWidgets.QWidget):
def __init__(self, parent=None):
super(Widget, self).__init__(parent)
self.label = QtWidgets.QLabel()
self.button_left = QtWidgets.QPushButton('<')
self.button_right = QtWidgets.QPushButton('>')
blayout = QtWidgets.QHBoxLayout()
blayout.addWidget(self.button_left)
blayout.addWidget(self.button_right)
layout = QtWidgets.QVBoxLayout(self)
layout.addWidget(self.label)
layout.addLayout(blayout)
self.mapper = QtWidgets.QDataWidgetMapper(self)
#self.db = create_db()
self.db = open_db()
self.model = QSqlQueryModel(self)
self.model.setQuery('SELECT DISTINCT form FROM threads')
self.mapper.setModel(self.model)
self.mapper.addMapping(self.label, 0, b'text')
self.mapper.toFirst()
self.button_left.clicked.connect(self.mapper.toPrevious)
self.button_right.clicked.connect(self.mapper.toNext)
def competitorView(self):
model = QSqlQueryModel()
model.setQuery('SELECT id, name FROM t_competitor')
model.setHeaderData(0, Qt.Horizontal, "ID")
model.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Competitor_List.setModel(model)
pass
def carregarTable(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
conexao = db.connectionName()
if db.open():
query = QSqlQueryModel(self)
query.setQuery(
"SELECT"
" livros_id, titulo, editoras.editora,"
" isbn, qtde_estoque, vl_unitario, consignado"
" FROM livros"
" INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id"
)
model = QSqlTableModel(self, db)
model.setQuery(query.query())
model.select()
self.ui.tableView.setModel(model)
self.ui.tableView.show()
# carregar combobox de editoras
query2 = QSqlQueryModel(self)
query2.setQuery("select(editora) from editoras")
model2 = QSqlTableModel(self, db)
model2.setQuery(query2.query())
model2.select()
self.ui.cbEditora.setModel(model2)
db.close()
def select(_):
query = QSqlQueryModel()
query.setQuery(
"SELECT p.Name AS [Должность], COUNT(e.Id) AS [Количество] "
"FROM Employees AS e INNER JOIN Posts AS p ON e.Post = p.Id "
"GROUP BY p.Name")
return query
def _createModel():
oi_get_query = QSqlQuery()
oi_get_query.prepare("""select symbol
,desc
,substr(oi_date, 1,4) || '-' || substr(oi_date, 5,2) || '-' || substr(oi_date,7,2)
,globex_volume
,volume
,open_interest
,change
from oi_reports
where symbol = "EUR"
order by cast(oi_date as int) desc
;
""")
#oi_get_query.addBindValue(symbol)
oi_get_query.exec()
queryModel = QSqlQueryModel()
queryModel.setQuery(oi_get_query)
headers = ['Symbol', 'Description', 'Date', 'Globex', 'Volume', 'Open Interest', 'Change']
for columnIndex, header in enumerate(headers):
queryModel.setHeaderData(columnIndex, Qt.Horizontal, header)
#queryModel.setHeaderData(0, Qt.Horizontal, 'Symbol')
#queryModel.setHeaderData(1, Qt.Horizontal, 'Description')
#queryModel.setHeaderData(2, Qt.Horizontal, 'Date')
#queryModel.setHeaderData(3, Qt.Horizontal, 'Globex')
#queryModel.setHeaderData(4, Qt.Horizontal, 'Volume')
#queryModel.setHeaderData(5, Qt.Horizontal, 'Open Interest')
#queryModel.setHeaderData(6, Qt.Horizontal, 'Change')
return queryModel
def attractionView(self):
model = QSqlQueryModel()
model.setQuery('SELECT id, name FROM t_attraction')
model.setHeaderData(0, Qt.Horizontal, "ID")
model.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Attraction_List.setModel(model)
pass
def inventory_used(self):
# 通过进度条表示库存余量
currentQryModel = QSqlQueryModel(self)
currentQryModel.setQuery("select RFID from 工件信息表")
allnum = currentQryModel.rowCount()
progress = int((100 * allnum / 156) + 0.5)
self.ui.progressBar.setValue(progress)
def select(max_price):
query = QSqlQueryModel()
query.setQuery("SELECT * "
"FROM Pastries "
"WHERE Price <= %s" % max_price)
LoaderModelsService.processingModelPastries(query, False)
return query
def parentTagListAlphaUpdate(self):
types = ["tags", "yt_tags"]
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
sort = ["T.tag_name", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct
FROM {types[self.tagType]} T LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
GROUP BY 1
) C
ON T.tag_name = C.tag_name
WHERE T.tag_name LIKE (?)
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
searchText = self.parentTagSearchBar.text()
query = QSqlQuery()
query.prepare(queryTemplate)
query.addBindValue(f"%{searchText}%")
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
pmodel = FilterProxyModel()
pmodel.setSourceModel(model)
filteredSet = self.parentTags
filteredSet.add(self.childTag)
pmodel.setFilteredSet(filteredSet)
self.parentTagListAlpha.setModel(pmodel)
self.parentTagListAlpha.setModelColumn(1)
self.parentTagListAlpha.show()
def dbToTableView(self,commandSQL):
try:
QApplication.processEvents()
if QSqlDatabase.contains("qt_sql_default_connection"):
db = QSqlDatabase.database("qt_sql_default_connection")
else:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(self.dbPath)
db.open()
projectModel = QSqlQueryModel()
projectModel.setQuery(commandSQL, db)
projectModel.setHeaderData(0, Qt.Horizontal, 'پلاک')
projectModel.setHeaderData(1, Qt.Horizontal, 'متقاضی')
projectModel.setHeaderData(2, Qt.Horizontal, 'نوع انجام کار')
projectModel.setHeaderData(3, Qt.Horizontal, 'تاریخ بازدید')
projectModel.setHeaderData(4, Qt.Horizontal, 'ساعت بازدید')
projectModel.setHeaderData(5, Qt.Horizontal, 'نقشه بردار')
projectModel.setHeaderData(6, Qt.Horizontal, 'نماینده')
projectModel.setHeaderData(7, Qt.Horizontal, 'تاریخ ثبت')
projectModel.setHeaderData(8, Qt.Horizontal, 'توضیحات')
self.ui.tableView_result.setModel(projectModel)
self.rowCount = projectModel.rowCount()
self.tableResult = projectModel
db.close()
QApplication.processEvents()
except:
self.errorM('مشکل در ارتباط با دیتابیس\n {}')
def new_addservice_dialog(self, window, db, res_index, thrd):
#Setup UI
ui = Ui_AddService()
new_addservice = QDialog()
ui.setupUi(new_addservice)
#Setup ComboBox view
db.open()
srv_model = QSqlQueryModel()
ui.treeView = QtWidgets.QTreeView(ui.comboBox)
srv_model.setQuery('SELECT Name, ID FROM Service', db)
db.close()
while srv_model.canFetchMore():
srv_model.fetchMore()
ui.comboBox.setModel(srv_model)
ui.comboBox.setView(ui.treeView)
db.close()
ui.pushButton.clicked.connect(lambda: thrd.tryStart(TableWorker(add_DB(ui, new_addservice, db,
"RoomService",
[res_index.siblingAtColumn(0).data(),res_index.siblingAtColumn(1).data(),res_index.siblingAtColumn(2).data(),ui.treeView.currentIndex().siblingAtColumn(1).data()],
"?, ?, ?, ?",
[]))))
ui.pushButton_2.clicked.connect(lambda: thrd.tryStart(TableWorker(del_DB(ui, new_addservice, db,
"RoomService", "ResID = ? AND SrvID = ?", [res_index.siblingAtColumn(0).data(), ui.treeView.currentIndex().siblingAtColumn(1).data()],
[]))))
new_addservice.setWindowTitle('Add or remove a Service')
new_addservice.exec()
def select(_):
query = QSqlQueryModel()
query.setQuery(
"SELECT p.Name AS [Товар], COUNT(p.Id) AS [Количество ингредиентов] "
"FROM Pastries AS p INNER JOIN Doses AS d ON d.Pastry = p.Id "
"GROUP BY p.Name")
return query
class DatabaseQueryView(QTableView):
def __init__(self, db, query, parent=None):
super().__init__(parent)
self.db = db
self.query = query
self._init_context_menu()
self._init_model()
def _init_context_menu(self):
self.setContextMenuPolicy(Qt.CustomContextMenu)
self.customContextMenuRequested.connect(self._on_context_menu)
def _init_model(self):
self.model = QSqlQueryModel()
self.model.setQuery(QSqlQuery(self.query, self.db))
self.setModel(self.model)
def set_query(self, query):
self.query = query
self._init_model()
def set_header_labels(self, labels):
for idx, label in enumerate(labels):
self.model.setHeaderData(idx + 1, Qt.Horizontal, label)
def _on_context_menu(self, pos):
_context_menu = QMenu(self)
_refresh_action = QAction("刷新")
_context_menu.addAction(_refresh_action)
action = _context_menu.exec_(self.viewport().mapToGlobal(pos))
if action == _refresh_action:
self.set_query(self.query)
def obtain_recipe(self):
"""
PURPOSE
-----
To obtain all information pertaining to the recipe clicked on by user, i.e. title, author, cuisine, etc. and
create a model to store that information
OUTPUT
-----
The model with the stored information
"""
recipe_info_cmd = \
f"""
SELECT R.`Recipe Title`, R.Author, R.Cuisine, R.Ingredients, R.`Recipe Procedure`, R.Tags, R.'Website URL'
FROM recipes R
WHERE R.`Recipe Title` = "{self.recipe_title}"
"""
# Query to get title, author, ingredients, and recipe procedure from desired recipe title
recipe_query = QSqlQuery(self.conn) # Same process as the title_query
recipe_query.prepare(recipe_info_cmd)
recipe_query.exec_()
recipe_model = QSqlQueryModel()
recipe_model.setQuery(recipe_query)
return recipe_model
def show_demo(self):
def test(x):
print(len(x))
# Создаем модель
sqm = QSqlQueryModel(parent=self)
sqm.setQuery(
'SELECT id, lastName, firstName, patrName, birthDate, sex, notes '
'FROM Client WHERE deleted = 0 AND deathDate IS NULL '
'LIMIT 20')
# Задаем заголовки для столбцов модели
sqm.setHeaderData(1, Qt.Horizontal, 'Фамилия')
sqm.setHeaderData(2, Qt.Horizontal, 'Имя')
sqm.setHeaderData(3, Qt.Horizontal, 'Отчество')
sqm.setHeaderData(4, Qt.Horizontal, 'Дата рождения')
sqm.setHeaderData(5, Qt.Horizontal, 'Пол')
sqm.setHeaderData(6, Qt.Horizontal, 'Примечание')
# Задаем для таблицы только что созданную модель
self.clients_table.setModel(sqm)
self.clients_table.hideColumn(0)
self.clients_table.resizeColumnsToContents()
self.clients_table.horizontalHeader().setStretchLastSection(True)
self.clients_table.horizontalHeader().setHighlightSections(False)
self.clients_table.verticalHeader().hide()
self.clients_table.clearSelection()
self.lbl_suitable_customers_count.setText(str(sqm.rowCount()))
self.lbl_selected_customers_count.setText('0')
self.clients_table.selectionModel().selectionChanged.connect(
self.show_selected_customers_count)
def do_search(self):
model = QSqlQueryModel()
filterStr = ""
current_status_int = self.STATUS_TO_I[self.selected_status]
filterStr = f"WHERE status_id >= '{current_status_int}'"
search_text = self.ui.leNameDescription.text().strip()
if len(search_text) > 0:
filterStr = f"{filterStr} AND (aircraft.name LIKE '%{search_text}%' OR aircraft.description LIKE '%{search_text}%')"
sql = f'''
SELECT
aircraft.name as name,
aircraft.description as description,
status.name as status,
aircraft.directory as directory
FROM aircraft
INNER JOIN status
ON aircraft.status_id == status.id
{filterStr}
ORDER BY aircraft.status_id DESC, aircraft.name ASC;
'''
model.setQuery(sql)
self.ui.tableView.setModel(model)
header = self.ui.tableView.horizontalHeader()
header.setSectionResizeMode(0, QHeaderView.ResizeToContents)
header.setSectionResizeMode(1, QHeaderView.ResizeToContents)
header.setSectionResizeMode(2, QHeaderView.ResizeToContents)
self.ui.tableView.setColumnHidden(3, True)
def filterListBetaUpdate(self):
'''
Update filterListBeta to select the filters of category categoryIndex
Displays the name of the filter with the count of how many videos the filter applies to
'''
filterDicts = {
"select": [
"A.playlist_id, B.playlist_title, ct, B.playlist_title",
"A.channel_id, B.channel_title, ct, B.channel_title",
"tag_name, tag_name, ct, tag_name",
"tag_name, tag_name, ct, tag_name"
],
"filterId": ["playlist_id", "channel_id", "tag_name", "tag_name"],
"filterName":
["B.playlist_title", "B.channel_title", "tag_name", "tag_name"],
"table": [
"videos_playlists_link", "videos", "videos_tags_link",
"videos_yt_tags_link"
],
"innerJoin": [
" A\nINNER JOIN playlists B ON\nA.playlist_id = B.playlist_id",
" A\nINNER JOIN channels B ON\nA.channel_id = B.channel_id",
"", ""
],
"ct": [
"ct", "ct", "ct", "ct"
] # Looks stupid for the purpose of matching the rest of the data structure
}
sort = ["filterName", "ct"]
sortDir = ["ASC", "DESC"]
categoryIndex = self.filterTypeDropDown.currentIndex()
sortStr = filterDicts[sort[
self.filterSortDropDown.currentIndex()]][categoryIndex]
sortDirStr = sortDir[self.filterSortDirDropDown.currentIndex()]
queryTemplate = f"""SELECT {filterDicts['select'][categoryIndex]} || ' (' || ct || ')'
FROM (
SELECT {filterDicts['filterId'][categoryIndex]}, count(*) as ct
FROM {filterDicts['table'][categoryIndex]}
GROUP BY 1
){filterDicts['innerJoin'][categoryIndex]}
WHERE {filterDicts['filterName'][categoryIndex]} LIKE (?)
ORDER BY {sortStr} {sortDirStr}"""
filterSearch = self.filterSearchBar.text()
query = QSqlQuery()
query.prepare(queryTemplate)
query.addBindValue(f"%{filterSearch}%")
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
pmodel = FilterProxyModel()
pmodel.setSourceModel(model)
filteredSets = self.filterListAlphaModel.getFilterSets()
pmodel.setFilteredSet(filteredSets[categoryIndex])
self.filterListBeta.setModel(pmodel)
self.filterListBeta.setModelColumn(3)
self.filterListBeta.show()
def songProperties(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_artist.name FROM fk_song_artist '
'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id '
'WHERE fk_song_artist.id_song = ' +
str(self.tableView_Song_List.currentIndex().sibling(
self.tableView_Song_List.currentIndex().row(), 0).data()))
model.setHeaderData(0, Qt.Horizontal, "Artist Name")
self.tableView_Song_Properties.setModel(model)
pass
def get_model(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('sports.db')
self.db.open()
model = QSqlQueryModel()
query_str = """ select name, count(name) as counts from domains group by name order by counts desc
"""
model.setQuery(query_str, db=self.db)
model.setHeaderData(0, Qt.Horizontal, "Word")
model.setHeaderData(1, Qt.Horizontal, "Count")
return model
class QueryDialog(QDialog):
"""a dialog to choose an item from a query
"""
choice = pyqtSignal(str)
def __init__(self, query):
super().__init__()
self.query = query
self.create_model()
self.init_UI()
def create_model(self):
"""creates the model as QSqlQueryModel,
using the given query
"""
self.model = QSqlQueryModel()
q = QSqlQuery()
q.exec_(self.query)
self.model.setQuery(q)
def init_UI(self):
"""setup the UI
"""
layout = QVBoxLayout()
self.setLayout(layout)
self.resize(200,200)
self.title = "Choose an existing project"
self.list = QListView(self)
layout.addWidget(self.list)
self.list.setModel(self.model)
self.list.setWhatsThis("Choose a project by clicking on it")
self.btn = QPushButton("Accept", self)
layout.addWidget(self.btn)
self.btn.clicked.connect(self.on_btn_clicked)
self.btn.setWhatsThis("Click here to accept your selection (works only if a project has been selected)")
def on_btn_clicked(self):
"""when self.btn is clicked, accept the choice and emit it as self.choice
"""
selected = self.list.selectedIndexes()
if selected:
index = selected[0]
chosen = self.model.data(index, Qt.DisplayRole)
self.choice.emit(chosen)
self.close()
self.choice.emit("")
self.close()
def songView(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_song.id, t_song.name, t_song.file, t_song_type.name, t_anime.name, t_anime_serie.name '
'FROM t_song '
'INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id '
'INNER JOIN t_anime ON t_song.id_anime = t_anime.id '
'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id')
model.setHeaderData(0, Qt.Horizontal, "Song ID")
model.setHeaderData(1, Qt.Horizontal, "Song Name")
model.setHeaderData(2, Qt.Horizontal, "Song File")
model.setHeaderData(3, Qt.Horizontal, "Song Type")
model.setHeaderData(4, Qt.Horizontal, "Anime Name")
model.setHeaderData(5, Qt.Horizontal, "Anime Serie")
self.tableView_Song_List.setModel(model)
pass
class SalesForm(QDialog):
def __init__(self, db):
super(QWidget, self).__init__()
self.ui = Ui_SalesDialog()
self.ui.setupUi(self)
self.db = db
self.queryModel = QSqlQueryModel()
self.ui.lstSales.setModel(self.queryModel)
self.onUpdateSales()
def onUpdateSales(self):
query = "SELECT sales.id, slabs.name, slabs.num, strftime('%d-%m-%Y', datetime(sales.date, 'unixepoch')) as date, sales.amount, sales.buyer, sales.notes FROM `slabs`, `sales` on slabs.id = sales.slabid;"
print(query)
self.queryModel.setQuery(query, self.db)
def gameProperties(self):
model = QSqlQueryModel()
model.setQuery('SELECT t_song.name, t_song_type.name, t_anime.name, t_anime_serie.name, t_artist.name '
'FROM t_song INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id '
'INNER JOIN t_anime ON t_song.id_anime = t_anime.id '
'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id '
'INNER JOIN fk_song_artist ON t_song.id = fk_song_artist.id_song '
'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id '
'WHERE t_song.id = ' +
str(self.tableView_Game_List.currentIndex().sibling(
self.tableView_Game_List.currentIndex().row(), 3).data()))
model.setHeaderData(0, Qt.Horizontal, "Song Name")
model.setHeaderData(1, Qt.Horizontal, "Song Type")
model.setHeaderData(2, Qt.Horizontal, "Anime Name")
model.setHeaderData(3, Qt.Horizontal, "Anime Serie")
model.setHeaderData(4, Qt.Horizontal, "Artist Name")
self.tableView_Game_Properties.setModel(model)
pass
def reloadGameCompetitors(self, id_attraction):
model_Game_Competitors = QSqlQueryModel()
model_Game_Competitors.setQuery('SELECT SUM(point), name FROM (SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor ON '
't_point.id_competitor = t_competitor.id '
'WHERE t_point.id_competitor_take = 1 '
'AND t_point.id_attraction = ' + id_attraction + ' UNION ALL '
'SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor '
'ON t_point.id_competitor_take = t_competitor.id '
'WHERE t_point.id_competitor_take != 1 '
'AND t_point.id_attraction = ' + id_attraction + ') '
'GROUP BY name ORDER BY point DESC')
model_Game_Competitors.setHeaderData(0, Qt.Horizontal, "Points")
model_Game_Competitors.setHeaderData(1, Qt.Horizontal, "Name")
self.tableView_Game_Competitors.setModel(model_Game_Competitors)
pass
def executeQuery(self):
"""
Public slot to execute the entered query.
"""
model = QSqlQueryModel(self.table)
model.setQuery(QSqlQuery(self.sqlEdit.toPlainText(), self.connections.currentDatabase()))
self.table.setModel(model)
if model.lastError().type() != QSqlError.NoError:
self.statusMessage.emit(model.lastError().text())
elif model.query().isSelect():
self.statusMessage.emit(self.tr("Query OK."))
else:
self.statusMessage.emit(
self.tr("Query OK, number of affected rows: {0}").format(model.query().numRowsAffected())
)
self.table.resizeColumnsToContents()
self.updateActions()
def model(self, prefix=None):
""" recreate the model each call; opening a new window will not
be needed to use the recent completions
"""
if prefix is None:
query_nav = QSqlQuery(
"select host || path from navigation " +
"order by count desc",
self.litedb)
else: # CFG02
query_nav = QSqlQuery(
"select host || path from navigation " +
"where prefix = '{}' ".format(prefix) +
"order by count desc",
self.litedb)
ret_model = QSqlQueryModel()
ret_model.setQuery(query_nav) # AB01
return ret_model
class ProjectsOverview(FilterableTable):
"""a widget to display a complete overview over
all projects
"""
changed_projects = pyqtSignal(str)
change_view = pyqtSignal(int)
deleted_project = pyqtSignal()
submit_to_ENA = pyqtSignal(str)
submit_to_IPD = pyqtSignal(str)
open_new_allele_form = pyqtSignal(str)
def __init__(self, log, mydb, parent = None):
super().__init__(log, mydb)
if parent:
self.settings = parent.settings
else:
import GUI_login
self.settings = GUI_login.get_settings("admin", self.log)
self.enhance_UI()
self.show_closed = False
self.filter_closed()
self.table.resizeColumnsToContents()
self.table.horizontalHeader().setSectionResizeMode(6, QHeaderView.Stretch)
self.table.horizontalHeader().setSectionResizeMode(7, QHeaderView.Stretch)
self.add_headers()
self.update_filterbox()
def enhance_UI(self):
self.toggle_btn = QPushButton(self)
self.toggle_btn.setText("Show closed projects!")
self.toggle_btn.setCheckable(True)
self.show_closed = False
self.grid.addWidget(self.toggle_btn, 1, 0)
self.toggle_btn.toggled.connect(self.on_toggleBtn_clicked)
self.table.customContextMenuRequested.connect(self.open_menu)
def add_headers(self):
headers = ["Project Name", "Project Status", "Creation Date", "User Name",
"Gene", "Pool", "Title", "Description", "Number of Alleles"]
for (i, column) in enumerate(headers):
self.proxy.setHeaderData(i, Qt.Horizontal, column)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
self.query_open = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
WHERE project_status = 'Open'
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.query_all = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.q = QSqlQuery()
self.log.debug("\t=> Done!")
@pyqtSlot()
def filter_closed(self):
"""filters the table either for only open projects (default)
or all projects
"""
if self.show_closed:
self.log.debug("Filtering for all projects...")
query = self.query_all
else:
self.log.debug("Filtering for only open projects...")
query = self.query_open
self.q.exec_(query)
self.check_error(self.q)
self.model.setQuery(self.q)
def on_toggleBtn_clicked(self, state):
"""when "ShowAll!" button is toggled, reset query
to appropriate filter
"""
self.show_closed = state
self.filter_closed()
def open_menu(self, pos):
"""provides a context menu
"""
menu = QMenu()
new_act = menu.addAction("Add new Target Allele")
open_act = menu.addAction("Open Project View")
select_act = menu.addAction("Select as current Project")
ena_act = menu.addAction("Submit Project to ENA")
ipd_act = menu.addAction("Submit Project to IPD")
del_act = menu.addAction("Delete project if empty")
action = menu.exec_(self.table.mapToGlobal(pos))
if action:
row = self.table.indexAt(pos).row()
myindex = self.model.index(row, 0)
project = self.model.data(myindex)
if action == select_act:
self.changed_projects.emit(project)
self.log.debug("ProjectsOverview emitted changed_projects")
elif action == open_act:
self.log.debug("ProjectsOverview emitted changed_projects & change_view")
self.changed_projects.emit(project)
self.change_view.emit(3)
elif action == new_act:
self.open_new_allele_form.emit(project)
elif action == ena_act:
self.submit_to_ENA.emit(project)
elif action == ipd_act:
self.submit_to_IPD.emit(project)
elif action == del_act:
self.delete_project(project, row)
@pyqtSlot(str, int)
def delete_project(self, project, row):
"""delete a project from the database & file system if it's empty
"""
self.log.debug("Attempting to delete project '{}' from database...".format(project))
q = QSqlQuery()
count_query = "select count(*) from alleles where project_name = '{}'".format(project)
q.exec_(count_query)
check_error(q, self.mydb, self.log)
alleles = 0
while q.next():
alleles = q.value(0)
if alleles == 0:
delete_query = "delete from projects where project_name = '{}'".format(project)
q.exec_(delete_query)
self.log.debug("\t=> Successfully deleted empty project")
self.log.debug("Attempting to delete project '{}' from file system...".format(project))
project_dir = os.path.join(self.settings["projects_dir"], project)
try:
shutil.rmtree(project_dir)
except Exception as E:
self.log.debug("=> File deletion did not work:")
self.log.error(E)
self.log.exception(E)
self.log.debug("=> Project '{}' successfully deleted from database and file system".format(project))
self.deleted_project.emit()
else:
self.log.debug("\t=> Project contains {} alleles => cannot delete!".format(alleles))
def refresh(self):
"""refreshes the table using setQuery
=> take care, this might lead to performance issues
"""
self.model.setQuery(self.model.query().lastQuery())
class OrderController(QObject):
def __init__(self, form, orderTable, addButton, editButton, deleteButton, dbase, mainwindow):
super().__init__()
self.form = form
self.orderTable = orderTable
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.mainwindow = mainwindow
form.currentRecordChanged.connect(self.recordChanged)
form.recordInserted.connect(lambda: self.recordChanged(None))
form.recordDeleted.connect(lambda: self.recordChanged(None))
if form.only_select:
self.addButton.setEnabled(False)
self.editButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def recordChanged(self, record):
#print("record changed", record)
if record is None:
for button in (self.addButton, self.editButton, self.deleteButton):
button.setEnabled(False)
self.orderTable.setModel(None)
self._hiddingHack(True)
else:
if not self.form.only_select:
self.addButton.setEnabled(True)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \
detail.price as sole_price, detail.price*order_detail.quantity as total_price\
FROM order_detail INNER JOIN detail \
ON order_detail.detail_id = detail.id \
WHERE order_detail.order_id={} ORDER BY article".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество")
self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку")
self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена")
self.orderTable.setModel(self.detailModel)
self.orderTable.hideColumn(0)
self.orderTable.resizeColumnsToContents()
self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self._hiddingHack(False)
def _hiddingHack(self, val):
ui = self.mainwindow.ui
if val is True:
ui.client_hack.setCurrentWidget(ui.client_hide_page)
ui.shop_hack.setCurrentWidget(ui.shop_hide_page)
ui.emp_hack.setCurrentWidget(ui.emp_hide_page)
else:
ui.client_hack.setCurrentWidget(ui.client_ok_page)
ui.shop_hack.setCurrentWidget(ui.shop_ok_page)
ui.emp_hack.setCurrentWidget(ui.emp_ok_page)
def tableSelectionChanged(self, cur, prev):
if self.form.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
order = self.form.currentRecord()
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \
WHERE order_id={}))".format(order.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1)
if not ok: return
detail_id = details[choice]
order_id = order.value("id")
query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(order_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.form.update()
def editButtonClicked(self):
detail = self.detailModel.record(self.orderTable.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
order = self.form.currentRecord()
if qnt > 0:
query = QSqlQuery("UPDATE order_detail SET quantity={} \
WHERE order_id={} AND detail_id={}".format(qnt,
order.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def deleteButtonClicked(self):
if not self.orderTable.currentIndex().isValid(): return
detail = self.detailModel.record(self.orderTable.currentIndex().row())
order = self.form.currentRecord()
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def selectRow(self, row):
self.form.selectRow(row)
def update(self):
self.form.update()
class WarehouseController(QObject):
def __init__(self, list, table, addButton, editButton, deleteButton, dbase):
super().__init__()
self.list = list
self.table = table
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.shopModel = QSqlTableModel(db = dbase)
self.shopModel.setTable("shop")
self.shopModel.select()
self.list.setModel(ComplexListModel(self.shopModel, "{name}"))
self.list.selectionModel().currentChanged.connect(self.listSelectionChanged)
self._checkPrivileges()
if self.only_select:
self.addButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def _checkPrivileges(self):
query = QSqlQuery("SHOW GRANTS")
only_select = None
table_pattern = "`{}`".format("shop_detail").lower()
while query.next():
s = query.value(0).lower()
if table_pattern in s:
if "select" in s and only_select is None:
only_select = True
else:
only_select = False
self.only_select = bool(only_select)
def listSelectionChanged(self, cur, prev):
if not cur.isValid():
return self.table.setModel(None)
else:
self.setShopIndex(cur.row())
def setShopIndex(self, row):
record = self.shopModel.record(row)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \
FROM shop_detail INNER JOIN detail \
ON shop_detail.detail_id = detail.id \
WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество")
self.table.setModel(self.detailModel)
self.table.hideColumn(0)
self.table.resizeColumnsToContents()
self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
def tableSelectionChanged(self, cur, prev):
if self.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \
WHERE shop_id={}))".format(shop.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар на склад: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
1, 1)
if not ok: return
detail_id = details[choice]
shop_id = shop.value("id")
query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(shop_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
self.table.selectionModel().clearSelection()
def editButtonClicked(self):
detail = self.detailModel.record(self.table.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
shop = self.shopModel.record(self.list.currentIndex().row())
if qnt > 0:
query = QSqlQuery("UPDATE shop_detail SET quantity={} \
WHERE shop_id={} AND detail_id={}".format(qnt,
shop.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def update(self):
cur = self.list.currentIndex()
if cur.isValid():
row = cur.row()
else:
row = 0
self.shopModel.select()
self.list.reset()
self.selectRow(row)
def selectRow(self, row):
self.list.selectionModel().clearSelection()
self.list.selectionModel().setCurrentIndex(
self.shopModel.index(row, 0), QItemSelectionModel.Select)
class MainWindow(QMainWindow):
def __init__(self):
super(QMainWindow, self).__init__()
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.txtNumber.setValidator(QIntValidator())
self.ui.txtName.textChanged.connect(self.onFilterChanged)
self.ui.txtNumber.textChanged.connect(self.onFilterChanged)
self.ui.btnNewMarble.clicked.connect(self.onNewMarble)
self.ui.btnDeleteMarble.clicked.connect(self.onDeleteMarble)
self.ui.btnSell.clicked.connect(self.onSell)
self.ui.lstMarble.doubleClicked.connect(self.onMarbleDoubleClicked)
self.ui.btnViewSales.clicked.connect(self.showSales)
self.queryModel = QSqlQueryModel()
self.ui.lstMarble.setModel(self.queryModel)
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('inventory.db')
self.db.open()
self.execSql('''PRAGMA foreign_keys = ON;''')
self.execSql('''CREATE TABLE IF NOT EXISTS `slabs` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` TEXT NOT NULL,
`num` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`dimL` INTEGER NOT NULL,
`dimW` INTEGER NOT NULL,
`dimH` INTEGER NOT NULL,
`notes` TEXT NOT NULL
);''')
self.execSql('''CREATE TABLE IF NOT EXISTS `sales` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`slabid` INTEGER NOT NULL,
`date` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`leftover` INTEGER NOT NULL,
`buyer` TEXT NOT NULL,
`notes` TEXT NOT NULL,
FOREIGN KEY(slabid) REFERENCES slabs(id)
);''')
self.onFilterChanged()
self.ui.lstMarble.setColumnHidden(0, True)
def onFilterChanged(self):
conditions = []
if self.ui.txtNumber.text() != '':
conditions.append('`num` = %d' % (int(self.ui.txtNumber.text()), ))
if self.ui.txtName.text() != '':
conditions.append("`name` LIKE '%%%s%%'" % (self.ui.txtName.text(), ))
conditionsStr = ''
if len(conditions) != 0:
conditionsStr = ' WHERE ' + ' AND '.join(conditions)
query = "SELECT id, name, num, amount, (dimH||'x'||dimL||'x'||dimW) as dimensions, notes FROM `slabs`" + conditionsStr + ' ORDER BY `name`, `num` ASC;'
print(query)
self.queryModel.setQuery(query, self.db)
def onNewMarble(self):
editMarble = EditMarble()
while True:
result = editMarble.exec()
if result == 0:
return
try:
name, number, amount, dimL, dimH, dimW, notes = editMarble.getValues()
break
except Exception as e:
QMessageBox.critical(self, "Error", "Invalid values entered: " + str(e))
query = '''SELECT `id`, `amount`, `notes` FROM `slabs` WHERE `name`='%s' AND `num`=%d AND `dimL`=%d AND `dimW`=%d AND `dimH`=%d''' % (sqlEscape(name), number, dimL, dimW, dimH)
result = self.execSql(query)
if result == None:
return #TOOD: error dialog? or maybe we should just try to insert?
if result.next() and result.value(2) == notes:
slabId = result.value(0)
previousAmount = result.value(1)
result.finish()
del result
result = None
query = '''UPDATE `slabs` SET `amount`=%d WHERE `id`=%d''' % (previousAmount + amount, slabId)
self.execSql(query)
return
del result
query = '''INSERT INTO `slabs` (`name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes`)
VALUES ('%s', %d, %d, %d, %d, %d, '%s');''' % (sqlEscape(name), number, amount, dimL, dimW, dimH, sqlEscape(notes))
self.execSql(query)
def onDeleteMarble(self):
if len(self.ui.lstMarble.selectedIndexes()) == 0: #nothing selected
QMessageBox.critical(self, "Error", "No slab selected.\nPlease select a slab of marble first")
return
rowIndex = self.ui.lstMarble.selectedIndexes()[0].row()
primaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0));
query = '''DELETE FROM `slabs` WHERE `id` == %d''' % (primaryKey, )
self.execSql(query)
def getSlabData(self, primaryKey):
query = '''SELECT `name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes` FROM `slabs` WHERE id=%d''' % (primaryKey, )
sqlQuery = QSqlQuery(query, self.db);
if not sqlQuery.next(): # user supplied an invalid primary key
QMessageBox.critical(self, "Error", 'slab with id %d was not found' % (primaryKey,))
raise Exception('slab with id %d was not found' % (primaryKey,))
name = sqlQuery.value(0)
number = sqlQuery.value(1)
amount = sqlQuery.value(2)
dimL = sqlQuery.value(3)
dimW = sqlQuery.value(4)
dimH = sqlQuery.value(5)
notes = sqlQuery.value(6)
return name, number, amount, dimL, dimW, dimH, notes
def onMarbleDoubleClicked(self, modelIndex):
rowIndex = modelIndex.row()
primaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0));
name, number, amount, dimL, dimW, dimH, notes = self.getSlabData(primaryKey)
editMarble = EditMarble()
editMarble.setValues(name, number, amount, dimL, dimW, dimH, notes)
result = editMarble.exec()
if result == 0:
return
name, number, amount, dimL, dimH, dimW, notes = editMarble.getValues()
self.execSql('''UPDATE `slabs` SET `name`='%s', `num`=%d, `amount`=%d, `dimL`=%d, `dimW`=%d, `dimH`=%d, `notes`='%s'
WHERE id = %d;''' % (sqlEscape(name), number, amount, dimL, dimW, dimH, sqlEscape(notes), primaryKey))
def execSql(self, query):
print(query)
sqlquery = QSqlQuery(query, self.db)
#self.db.exec(query)
if sqlquery.lastError().number() != -1: #TODO: print these in a dialog box
QMessageBox.critical(self, "Error", 'ERROR OCCURRED WHILE EXECUTING STATEMENT: ' + query + "\n" + \
'Database Text:' + sqlquery.lastError().databaseText() + \
'Databse Driver:' + sqlquery.lastError().driverText())
return None
self.onFilterChanged()
return sqlquery
def onSell(self):
if len(self.ui.lstMarble.selectedIndexes()) == 0: #nothing selected
QMessageBox.critical(self, "Error", "No slab selected.\nPlease select a slab of marble first")
return
rowIndex = self.ui.lstMarble.selectedIndexes()[0].row()
slabPrimaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0));
name, number, amount, dimL, dimW, dimH, notes = self.getSlabData(slabPrimaryKey)
sell = SellDialog(name, number, amount)
result = sell.exec()
if result == 0:
return
amountSold, amountOfLeftovers, buyer, datetime, notes = sell.getValues()
leftoverQueries = []
while amountOfLeftovers > 0:
newMarble = EditMarble()
newMarble.setValues(name, number, amountSold, dimL, dimW, dimH, notes)
newMarble.setMaxAmount(amountOfLeftovers)
result = newMarble.exec()
if result == 0:
return
newName, newNumber, newAmount, newDimL, newDimW, newDimH, newNotes = newMarble.getValues()
leftoverQueries.append('''INSERT INTO `slabs` (`name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes`)
VALUES ('%s', %d, %d, %d, %d, %d, '%s');''' % (sqlEscape(newName), newNumber, newAmount, newDimL, newDimW, newDimH, sqlEscape(newNotes)))
amountOfLeftovers = amountOfLeftovers - newAmount
queryUpdate = '''UPDATE `slabs` SET `amount`=`amount`-%d WHERE id = %d;''' % (amountSold, slabPrimaryKey)
querySale = '''INSERT INTO `sales` (`slabid`, `date`, `amount`, `leftover`, `buyer`, `notes`)
VALUES(%d, %d, %d, 0, '%s', '%s');''' % (slabPrimaryKey, datetime, amountSold, buyer, notes)
self.execSql('BEGIN TRANSACTION;')
self.execSql(queryUpdate)
self.execSql(querySale)
for leftoverQuery in leftoverQueries:
self.execSql(leftoverQuery)
self.execSql('COMMIT;')
self.onFilterChanged()
def showSales(self):
sales = SalesForm(self.db)
sales.exec()
class DataGrid(QWidget):
def __init__(self):
super().__init__()
self.setWindowTitle("分页查询例子")
self.resize(750,300)
# 查询模型
self.queryModel = None
# 数据表
self.tableView = None
# 总数页文本
self.totalPageLabel = None
# 当前页文本
self.currentPageLabel = None
# 转到页输入框
self.switchPageLineEdit = None
# 前一页按钮
self.prevButton = None
# 后一页按钮
self.nextButton = None
# 转到页按钮
self.switchPageButton = None
# 当前页
self.currentPage = 0
# 总页数
self.totalPage = 0
# 总记录数
self.totalRecrodCount = 0
# 每页显示记录数
self.PageRecordCount = 5
self.initUI()
def initUI(self):
# 创建窗口
self.createWindow()
# 设置表格
self.setTableView()
# 信号槽连接
self.prevButton.clicked.connect(self.onPrevButtonClick )
self.nextButton.clicked.connect(self.onNextButtonClick )
self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick )
# 创建数据库
# 创建窗口
def createWindow(self):
# 操作布局
operatorLayout = QHBoxLayout()
self.prevButton = QPushButton("前一页")
self.nextButton = QPushButton("后一页")
self.switchPageButton = QPushButton("Go")
self.switchPageLineEdit = QLineEdit()
self.switchPageLineEdit.setFixedWidth(40)
switchPage = QLabel("转到第")
page = QLabel("页")
operatorLayout.addWidget(self.prevButton)
operatorLayout.addWidget(self.nextButton)
operatorLayout.addWidget(switchPage)
operatorLayout.addWidget(self.switchPageLineEdit)
operatorLayout.addWidget(page)
operatorLayout.addWidget(self.switchPageButton)
operatorLayout.addWidget( QSplitter())
# 状态布局
statusLayout = QHBoxLayout()
self.totalPageLabel = QLabel()
self.totalPageLabel.setFixedWidth(70)
self.currentPageLabel = QLabel()
self.currentPageLabel.setFixedWidth(70)
self.totalRecordLabel = QLabel()
self.totalRecordLabel.setFixedWidth(70)
statusLayout.addWidget(self.totalPageLabel)
statusLayout.addWidget(self.currentPageLabel)
statusLayout.addWidget( QSplitter() )
statusLayout.addWidget(self.totalRecordLabel)
# 设置表格属性
self.tableView = QTableView()
# 表格宽度的自适应调整
self.tableView.horizontalHeader().setStretchLastSection(True)
self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 创建界面
mainLayout = QVBoxLayout(self);
mainLayout.addLayout(operatorLayout);
mainLayout.addWidget(self.tableView);
mainLayout.addLayout(statusLayout);
self.setLayout(mainLayout)
# 设置表格
def setTableView(self):
print('*** step2 SetTableView' )
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
# 得到记录数
def getTotalRecordCount(self):
self.queryModel.setQuery("select * from student")
rowCount = self.queryModel.rowCount()
print('rowCount=' + str(rowCount) )
return rowCount
# 得到页数
def getPageCount(self):
if self.totalRecrodCount % self.PageRecordCount == 0 :
return (self.totalRecrodCount / self.PageRecordCount )
else :
return (self.totalRecrodCount / self.PageRecordCount + 1)
# 记录查询
def recordQuery(self, limitIndex ):
szQuery = ("select * from student limit %d,%d" % ( limitIndex , self.PageRecordCount ) )
print('query sql=' + szQuery )
self.queryModel.setQuery(szQuery)
# 刷新状态
def updateStatus(self):
szCurrentText = ("当前第%d页" % self.currentPage )
self.currentPageLabel.setText( szCurrentText )
#设置按钮是否可用
if self.currentPage == 1 :
self.prevButton.setEnabled( False )
self.nextButton.setEnabled( True )
elif self.currentPage == self.totalPage :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( False )
else :
self.prevButton.setEnabled( True )
self.nextButton.setEnabled( True )
# 设置总数页文本
def setTotalPageLabel(self):
szPageCountText = ("总共%d页" % self.totalPage )
self.totalPageLabel.setText(szPageCountText)
# 设置总总记录数
def setTotalRecordLabel(self):
szTotalRecordText = ("共%d条" % self.totalRecrodCount )
print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText )
self.totalRecordLabel.setText(szTotalRecordText)
# 前一页按钮按下
def onPrevButtonClick(self):
print('*** onPrevButtonClick ');
limitIndex = (self.currentPage - 2) * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage -= 1
self.updateStatus()
# 后一页按钮按下
def onNextButtonClick(self):
print('*** onNextButtonClick ');
limitIndex = self.currentPage * self.PageRecordCount
self.recordQuery( limitIndex)
self.currentPage += 1
self.updateStatus()
# 转到页按钮按下
def onSwitchPageButtonClick(self):
# 得到输入字符串
szText = self.switchPageLineEdit.text()
#数字正则表达式
pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
match = pattern.match(szText)
# 判断是否为数字
if not match :
QMessageBox.information(self, "提示", "请输入数字" )
return
# 是否为空
if szText == '' :
QMessageBox.information(self, "提示" , "请输入跳转页面" )
return
#得到页数
pageIndex = int(szText)
#判断是否有指定页
if pageIndex > self.totalPage or pageIndex < 1 :
QMessageBox.information(self, "提示", "没有指定的页面，请重新输入" )
return
#得到查询起始行号
limitIndex = (pageIndex-1) * self.PageRecordCount
#记录查询
self.recordQuery(limitIndex);
#设置当前页
self.currentPage = pageIndex
#刷新状态
self.updateStatus();
model.setHeaderData(0, Qt.Horizontal, "Серія")
model.setHeaderData(1, Qt.Horizontal, "Проміжна")
model.setHeaderData(2, Qt.Horizontal, "Готова")
def createView(title, model):
view = QTableView()
view.setModel(model)
view.setWindowTitle(title)
return view
if __name__ == '__main__':
import sys
app = QApplication(sys.argv)
if not createConnection():
sys.exit(1)
# model = QSqlTableModel()
model = QSqlQueryModel()
model.setQuery("SELECT Id, PrepData, ReadyData FROM PData")
initializeModel(model)
view1 = createView("Table Model (View 1)", model)
view1.show()
sys.exit(app.exec_())
class ProjectAlleles(FilterableTable):
"""a widget to display all alleles of one project,
with their most important data
"""
changed_allele = pyqtSignal(str, int, str)
change_view = pyqtSignal(int)
def __init__(self, log, mydb):
super().__init__(log, mydb, add_color_proxy = (4,5))
self.proxy.setFilterKeyColumn(2)
self.filter_cb.setCurrentIndex(2)
self.header_lbl.setText("Alleles:")
self.table.verticalHeader().hide()
self.table.customContextMenuRequested.connect(self.open_menu)
self.project = ""
self.filter(self.project)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
q = QSqlQuery()
query = """SELECT project_name, project_nr,
(sample_id_int || ' #' || allele_nr || ' (' || gene || ')'),
local_name,
allele_status, lab_status,
sample_id_int, allele_nr
FROM alleles
order by project_nr
"""
q.exec_(query)
self.check_error(q)
self.model.setQuery(q)
self.model.setHeaderData(1, Qt.Horizontal, "Nr")
self.model.setHeaderData(2, Qt.Horizontal, "Target Allele")
self.model.setHeaderData(3, Qt.Horizontal, "Allele Name")
self.model.setHeaderData(4, Qt.Horizontal, "Allele Status")
self.model.setHeaderData(5, Qt.Horizontal, "Lab Status")
self.log.debug("\t=> Done!")
def filter(self, project):
self.project = project
self.proxy.layoutAboutToBeChanged.emit()
self.proxy.setFilterKeyColumn(0)
self.proxy.setFilterFixedString(project)
self.proxy.layoutChanged.emit()
for col in [0, 6, 7]:
self.table.hideColumn(col)
@pyqtSlot()
def refresh(self):
"""refreshes the table's content
"""
self.log.debug("\tRefreshing ProjectView's allele list...")
self.model.setQuery(self.model.query().lastQuery())
@pyqtSlot(QPoint)
def open_menu(self, pos):
"""provides a context menu
"""
try:
menu = QMenu()
open_allele_act = menu.addAction("Open Allele View")
action = menu.exec_(self.table.mapToGlobal(pos))
if action:
row = self.table.indexAt(pos).row()
sample = self.proxy.data(self.proxy.index(row, 6))
allele_nr = int(self.proxy.data(self.proxy.index(row, 7)))
if action == open_allele_act:
self.changed_allele.emit(sample, allele_nr, self.project)
self.change_view.emit(4)
self.log.debug("ProjectAlleles emitted changed_allele to {} #{} ({}) & change_view to AlleleView".format(sample, allele_nr, self.project))
except Exception as E:
self.log.exception(E)
from PyQt5.QtSql import QSqlQueryModel,QSqlDatabase,QSqlQuery
from PyQt5.QtWidgets import QTableView,QApplication
import sys
app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("patientData.db")
db.open()
projectModel = QSqlQueryModel()
projectModel.setQuery("select * from patient",db)
projectView = QTableView()
projectView.setModel(projectModel)
projectView.show()
app.exec_()
