def populate_fileds(self):
self.hideBox.show()
connection = sqlite3.connect("ims.db")
if connection:
self.cursor = connection.cursor()
self.cursor.execute(
"select * from products where product_name = '{0}'".format(
str(self.product.currentText())))
rows = self.cursor.fetchall()
model = QtSql.QSqlQueryModel()
qry = QtSql.QSqlQuery()
qry.exec_("select distinct name from categories")
model.setQuery(qry)
mod = QtSql.QSqlQueryModel()
q = QtSql.QSqlQuery()
q.exec_("select distinct name from suppliers")
mod.setQuery(q)
if rows:
self.productName.setText(rows[0][2])
self.productCode.setText(rows[0][1])
self.realPrice.setText(str(rows[0][3]))
self.salePrice.setText(str(rows[0][4]))
self.total.setText(str(rows[0][5]))
self.quantity.setText(str(rows[0][6]))
self.category.setModel(model)
self.dateAdded.setCalendarPopup(True)
self.supplier.setModel(mod)
else:
self.hideBox.hide()
self.btnCancel.show()
def update_view(self):
sql.connectDB()
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("Select distinct product_code, product_name from returns")
model.setQuery(query)
self.choose.setModel(model)
self.choose.setModelColumn(1)
comp = QtWidgets.QCompleter()
comp.setModel(model)
comp.setCompletionMode(QtWidgets.QCompleter.PopupCompletion)
comp.setFilterMode(QtCore.Qt.MatchContains)
comp.setCaseSensitivity(QtCore.Qt.CaseInsensitive)
self.productCode.setCompleter(comp)
complet = QtWidgets.QCompleter()
complet.setFilterMode(QtCore.Qt.MatchContains)
complet.setCaseSensitivity(QtCore.Qt.CaseInsensitive)
complet.setCompletionMode(QtWidgets.QCompleter.PopupCompletion)
qery = QtSql.QSqlQuery()
qery.exec_("select * from returns")
modal = QtSql.QSqlQueryModel()
modal.setQuery(qery)
self.search.setCompleter(complet)
complet.setModel(modal)
self.search.textChanged.connect(lambda: self.get_return())
self.formGroupBox.setDisabled(True)
def search_invoices(self):
if not self.search.text() == '' or not self.search.text().isspace():
qry = QtSql.QSqlQuery()
# self.setWindowTitle("Search results for " + self.search.text())
qry.prepare("select * from invoices where product_code LIKE '%{0}%' or product_name LIKE '%{0}%' or "
"customer_name LIKE '%{0}%' or invoice_number LIKE '%{0}%' or date_bought LIKE '%{0}%'".format(
str(self.search.text())))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Customer")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Invoice No.")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Product Code")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Product Name")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Sale Price")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Amount Paid")
model.setHeaderData(8, QtCore.Qt.Horizontal, "Balance")
model.setHeaderData(9, QtCore.Qt.Horizontal, "Quantity")
model.setHeaderData(10, QtCore.Qt.Horizontal, "Total")
model.setHeaderData(11, QtCore.Qt.Horizontal, "Date")
self.invoiceView.setModel(model)
self.invoiceView.horizontalHeader().setMinimumHeight(40)
self.invoiceView.horizontalHeader().setDefaultSectionSize(180)
self.invoiceView.setColumnHidden(0, True)
self.invoiceView.setColumnHidden(6, True)
self.invoiceView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.invoiceView.setShowGrid(True)
self.invoiceView.show()
def t09_spec_remove(self, id):
self.__execute(
'''delete from specializations where specializations.idspecializations = '''
+ id)
model = QtSql.QSqlQueryModel()
model.setQuery(QtSql.QSqlQuery('select * from specializations'))
return model
def t10_activity_dissmis(self, id, date):
self.__execute(
'''insert into activity(activitytype, activitydate, idworker)
values ('was dismissed', '%s', %s)''' % (date, id))
model = QtSql.QSqlQueryModel()
model.setQuery(QtSql.QSqlQuery('select * from activity'))
return model
def del_image(self):
# по умолчанию в MessageBox ставим Cancel
buttonReply = QMessageBox.question(
self, 'Подтвердите действие', "картинка будет удалена",
QMessageBox.Yes | QMessageBox.Cancel, QMessageBox.Cancel)
# если пользователь согласен
if buttonReply == QMessageBox.Yes:
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("image.db")
db.open()
# модель для запроса к базе
model = QtSql.QSqlQueryModel(parent=None)
# модель для получения ID записи которую нужно удалить
model_table = QtSql.QSqlTableModel()
model_table.setTable("images")
model_table.select()
# берем первый индекс из списка выбранных строк.
# по нему строку с этим индексом в моделе и значение в этой строке value(0) в первом столбце - ID
id = model_table.record(
self.im_table.selectedIndexes()[0].row()).value(0)
# удаляем
sql_text = f"DELETE FROM images WHERE id = {id}"
model.setQuery(sql_text)
model.query().exec_()
self.refresh_table()
db.close()
def search_contacts(self):
if self.searchName.text() == '' or self.searchName.text().isspace():
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Enter search query"),
QtWidgets.qApp.tr("Please enter search criterion to search!"),
QtWidgets.QMessageBox.Ok)
else:
qry = QtSql.QSqlQuery()
qry.prepare(
"select * from contacts where contact_name LIKE '%{0}%' or contact_phone LIKE '%{0}%' or "
"date_added LIKE '%{0}%'".format(str(self.searchName.text())))
# qry.bindValue(0, str(self.search.text()))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Contact Name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Phone Number")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Date Saved")
self.contactView.setModel(model)
self.contactView.horizontalHeader().setDefaultSectionSize(200)
self.contactView.horizontalHeader().setMinimumHeight(40)
self.contactView.setColumnHidden(0, True)
self.contactView.show()
def get_debtor(self):
if self.search.text() == '' or self.search.text().isspace():
QtWidgets.QMessageBox.critical(
None, QtWidgets.qApp.tr("Enter search query"),
QtWidgets.qApp.tr("Please enter search criterion to search!"),
QtWidgets.QMessageBox.Ok)
else:
qry = QtSql.QSqlQuery()
qry.prepare(
"select * from debts where customer_name LIKE '%{0}%' or product_name LIKE '%{0}%' or "
"city LIKE '%{0}%' or area LIKE '%{0}%' or address LIKE '%{0}%' or due_date LIKE '%{0}%'"
.format(str(self.search.text())))
# qry.bindValue(0, str(self.search.text()))
if qry.exec_():
my_model = QtSql.QSqlQueryModel()
my_model.setQuery(qry)
my_model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
my_model.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
my_model.setHeaderData(2, QtCore.Qt.Horizontal, "Phone")
my_model.setHeaderData(3, QtCore.Qt.Horizontal, "City")
my_model.setHeaderData(4, QtCore.Qt.Horizontal, "Area")
my_model.setHeaderData(5, QtCore.Qt.Horizontal, "Address")
my_model.setHeaderData(6, QtCore.Qt.Horizontal, "Product")
my_model.setHeaderData(7, QtCore.Qt.Horizontal, "Paid")
my_model.setHeaderData(8, QtCore.Qt.Horizontal, "Owed")
my_model.setHeaderData(9, QtCore.Qt.Horizontal, "Total")
my_model.setHeaderData(10, QtCore.Qt.Horizontal, "Quantity")
my_model.setHeaderData(11, QtCore.Qt.Horizontal, "Due Date")
self.debtorView.setModel(my_model)
self.debtorView.setColumnHidden(0, True)
self.debtorView.setSelectionBehavior(
QtWidgets.QTableView.SelectRows)
self.debtorView.show()
def populate_fileds(self):
connection = sqlite3.connect("ims.db")
if connection:
self.cursor = connection.cursor()
self.cursor.execute(
"select * from returns where product_name = '{0}'".format(
str(self.choose.currentText())))
rows = self.cursor.fetchall()
if rows:
self.formGroupBox.setEnabled(True)
self.productName.setText(rows[0][2])
self.productCode.setText(rows[0][1])
self.returnee.setText(str(rows[0][3]))
self.returnType.setText(str(rows[0][4]))
self.price.setText(str(rows[0][5]))
self.quantity.setText(str(rows[0][6]))
self.returnDate.setText(rows[0][7])
self.reason.setPlainText(rows[0][8])
self.btnUpdate.setEnabled(True)
sql.connectDB()
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("Select distinct product_name from returns")
model.setQuery(query)
self.choose.setModel(model)
else:
self.formGroupBox.setDisabled(True)
self.btnUpdate.setDisabled(True)
def search_suppliers(self):
if not self.search.text() == '' or not self.search.text().isspace():
qry = QtSql.QSqlQuery()
qry.prepare("select * from supplier_info where id LIKE '%{0}%' or name LIKE '%{0}%' or "
"city LIKE '%{0}%' or region LIKE '%{0}%' or date_added LIKE '%{0}%' or email LIKE '%{0}%'"
" or postal_zip LIKE '%{0}%' or contact LIKE '%{0}%' or registration_no LIKE '%{0}%'".format(
str(self.search.text())))
# qry.bindValue(0, str(self.search.text()))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Contact")
model.setHeaderData(3, QtCore.Qt.Horizontal, "City")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Region")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Fax")
model.setHeaderData(6, QtCore.Qt.Horizontal, "Address")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Postal/Zip")
model.setHeaderData(8, QtCore.Qt.Horizontal, "Email")
model.setHeaderData(9, QtCore.Qt.Horizontal, "Registration No.")
model.setHeaderData(10, QtCore.Qt.Horizontal, "No. of Products")
model.setHeaderData(11, QtCore.Qt.Horizontal, "Remarks")
model.setHeaderData(12, QtCore.Qt.Horizontal, "Date Added")
self.supplierView.setModel(model)
self.supplierView.horizontalHeader().setMinimumHeight(40)
self.supplierView.horizontalHeader().setDefaultSectionSize(120)
self.supplierView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.supplierView.setColumnHidden(0, True)
self.supplierView.setShowGrid(True)
self.supplierView.show()
def selectStock(self):
if sql.connectDB():
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_("select product_name from products")
model.setQuery(query)
self.productName.setModel(model)
def search_customer(self):
filter_prop = self.customerName.currentText()
model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
query.exec_(
"select * from customers where name like '%{0}%' or phone like '%{0}%' or address like '%{0}%' or "
"city like '%{0}%' or region like '%{0}%' or email like '%{0}%' or post_office like '%{0}%'"
.format(str(self.search.text())))
model.setQuery(query)
if model.rowCount() > 0:
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Phone")
model.setHeaderData(3, QtCore.Qt.Horizontal, "City")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Region")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Gender")
model.setHeaderData(6, QtCore.Qt.Horizontal, "Address")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Post")
model.setHeaderData(8, QtCore.Qt.Horizontal, "Email")
model.setHeaderData(9, QtCore.Qt.Horizontal, "Registered Date")
model.setHeaderData(10, QtCore.Qt.Horizontal, "Remarks")
self.tableView.setModel(model)
self.tableView.setMinimumWidth(1110)
self.tableView.horizontalHeader().setDefaultSectionSize(130)
self.tableView.horizontalHeader().setMinimumHeight(40)
self.tableView.setColumnHidden(0, True)
self.tableView.show()
def search(self):
sql.connectDB()
if not self.search_users.text() == '' or not self.search_users.text(
).isspace():
qry = QtSql.QSqlQuery()
qry.prepare(
"select * from users where name LIKE '%{0}%' or username LIKE '%{0}%' or phone LIKE '%{0}%'"
" or email LIKE '%{0}%'".format(str(self.search_users.text())))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Full Name")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Password")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Email")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Phone")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Username")
model.setHeaderData(6, QtCore.Qt.Horizontal, "Type")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Status")
self.widgetUsers.setModel(model)
self.widgetUsers.setColumnHidden(0, True)
self.widgetUsers.setColumnHidden(2, True)
self.widgetUsers.setSelectionBehavior(
QtWidgets.QTableView.SelectRows)
self.widgetUsers.show()
def __init__(self, parent=None):
QtWidgets.QWidget.__init__(self, parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
db = QtSql.QSqlDatabase.addDatabase("QODBC")
db.setDatabaseName(
"DRIVER={SQL SERVER};SERVER=LAPTOP-0R0CFR1B;DATABASE=courseWork;Trusted_Connection=yes;"
)
db.open()
if (not (db.open())):
QMessageBox.warning(0, "Ошибка",
"Не удалось подключиться к базе данных")
self.ui.cBTable.addItem("Фильмы")
self.ui.cBTable.addItem("Режиссеры")
self.ui.cBTable.addItem("Актеры")
self.ui.btAdd.clicked.connect(self.onClickAdd)
self.ui.btDelete.clicked.connect(self.onClickDelete)
self.ui.btUpdate.clicked.connect(self.onIndexChange)
self.ui.cBTable.currentIndexChanged.connect(self.onIndexChange)
self.ui.btEdit.clicked.connect(self.onClickEdit)
self.ui.tableView.horizontalHeader().setStretchLastSection(1)
query = QtSql.QSqlQuery()
query.prepare("SELECT* FROM getMovieView")
query.exec()
model = QtSql.QSqlQueryModel()
model.setQuery(query)
self.ui.tableView.setModel(model)
def search_sales(self):
sql.connectDB()
if not self.search.text() == '' or not self.search.text().isspace():
qry = QtSql.QSqlQuery()
# self.setWindowTitle("Search results for " + self.search.text())
qry.prepare("select * from sales where customer_name LIKE '%{0}%' or product_name LIKE '%{0}%' or "
"quantity LIKE '%{0}%' or amount_paid LIKE '%{0}%' or date_bought LIKE '%{0}%'".format(
str(self.search.text())))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
self.lblTotal.setText(str(model.rowCount()))
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Customer")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Product Name")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Quantity")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Amount Paid")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Date")
self.saleView.setModel(model)
self.saleView.horizontalHeader().setMinimumHeight(40)
self.saleView.horizontalHeader().setDefaultSectionSize(180)
self.saleView.setColumnHidden(0, True)
self.saleView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.saleView.setShowGrid(True)
self.saleView.show()
def get_creditor(self):
if not self.search_query.text() == '' or not self.search_query.text().isspace():
qry = QtSql.QSqlQuery()
qry.prepare("select * from creditors where creditor_name LIKE '%{0}%' or product_name LIKE '%{0}%' or "
"due_date LIKE '%{0}%'".format(str(self.search_query.text())))
if qry.exec_():
self.model = QtSql.QSqlQueryModel()
self.model.setQuery(qry)
self.model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
self.model.setHeaderData(1, QtCore.Qt.Horizontal, "Name")
self.model.setHeaderData(2, QtCore.Qt.Horizontal, "Phone")
self.model.setHeaderData(3, QtCore.Qt.Horizontal, "Product")
self.model.setHeaderData(4, QtCore.Qt.Horizontal, "Amount Paid")
self.model.setHeaderData(5, QtCore.Qt.Horizontal, "Amount Owed")
self.model.setHeaderData(6, QtCore.Qt.Horizontal, "Total")
self.model.setHeaderData(7, QtCore.Qt.Horizontal, "Quantity")
self.model.setHeaderData(8, QtCore.Qt.Horizontal, "Due")
self.creditorView.setModel(self.model)
self.creditorView.horizontalHeader().setMinimumHeight(40)
self.creditorView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.creditorView.setColumnHidden(0, True)
self.creditorView.show()
self.creditorView.setModel(self.model)
self.creditorView.setColumnHidden(0, True)
self.creditorView.setSelectionBehavior(QtWidgets.QTableView.SelectRows)
self.creditorView.show()
def on_query_run(self):
# Создаем объект 'модель на основе запроса'
sqm = QtSql.QSqlQueryModel(parent=db_data_table)
# Выполняем запрос, помещаем результаты в модель
sqm.setQuery(self.query_edit.text())
# Помещаем модель в виджет db_data_table
db_data_table.setModel(sqm)
def get_tables(self):
if self.isConnectionOpen():
model = QtSql.QSqlQueryModel()
model.setQuery(
"SELECT table_schema || '.' || table_name || ' (' || LEFT(table_type,1) || ')' "
"FROM information_schema.tables")
return model
def update_view(self):
sql.connectDB()
self.model = QtSql.QSqlQueryModel()
self.query = QtSql.QSqlQuery()
self.query.exec_("select distinct contact_name from contacts")
self.model.setQuery(self.query)
self.selectContact.setModel(self.model)
def __init__(self, parent=None):
QtWidgets.QWidget.__init__(self, parent)
self.ui = CrearEvento_Ui()
self.ui.setupUi(self)
self.model = QtSql.QSqlQueryModel(self)
self.sql = 'SELECT personal.id_personal, personal.nombre, apellidos, dni, \
telefono, email, autonomo, notas FROM personal'
self.model.setQuery(self.sql)
self.ui.personal_table.setModel(self.model)
self.ui.personal_table.setSelectionBehavior(
self.ui.personal_table.SelectRows)
#-------------Escribe la cabecera de la tabla----------------------------------
self.model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
self.model.setHeaderData(1, QtCore.Qt.Horizontal, "Nombre")
self.model.setHeaderData(2, QtCore.Qt.Horizontal, "Apellidos")
self.model.setHeaderData(3, QtCore.Qt.Horizontal, "DNI")
self.model.setHeaderData(4, QtCore.Qt.Horizontal, "Teléfono")
self.model.setHeaderData(5, QtCore.Qt.Horizontal, "Email")
self.model.setHeaderData(6, QtCore.Qt.Horizontal, "Autónomo")
self.model.setHeaderData(7, QtCore.Qt.Horizontal, "Notas")
#-------Si el combobox se activa llama a la funcion de filtrado----------------
self.ui.combo_filtrar_cargos.activated[str].connect(self.filtro_checks)
#--------Conexión del boton añadir fechas-------------------------------------
self.ui.buttonAddDate.clicked.connect(self.open_dias_evento)
def retranslateUi(self, EditProduct):
_translate = QtCore.QCoreApplication.translate
EditProduct.setWindowTitle(_translate("EditProduct", "Edit Product"))
self.label.setText(_translate("EditProduct", "Edit Product"))
self.label_3.setText(_translate("EditProduct", "Select product:"))
self.lblTotal.setText(_translate("EditProduct", "Total:"))
self.lblDate.setText(_translate("EditProduct", "Date:"))
self.lblRealPrice.setText(_translate("EditProduct", "Real Price:"))
self.lblSalePrice.setText(_translate("EditProduct", "Sale Price:"))
self.lblQuantity.setText(_translate("EditProduct", "Quantity:"))
self.lblProductName.setText(_translate("EditProduct", "Product Name:"))
self.btnSave.setText(_translate("EditProduct", "Save"))
self.lblSupplier.setText(_translate("EditProduct", "Supplier:"))
self.btnAddSupplier.setToolTip(
_translate("EditProduct", "Add new supplier"))
self.btnAddSupplier.setText(_translate("EditProduct", "+"))
self.lblProductCode.setText(_translate("EditProduct", "Product Code:"))
self.btnCancel.setText(_translate("EditProduct", "Cancel"))
self.label_2.setText(_translate("EditProduct", "Category:"))
sql.connectDB()
self.model = QtSql.QSqlQueryModel()
query = QtSql.QSqlQuery()
self.hideBox.setDisabled(True)
query.prepare("SELECT DISTINCT product_name FROM products")
if query.exec_():
self.hideBox.setEnabled(True)
self.model.setQuery(query)
self.product.setModel(self.model)
self.btnRefresh.setEnabled(True)
else:
self.btnRefresh.setDisabled(True)
def gradient(self):
# получаем кол-во файлов с названием градиент
con = sqlite3.connect("image.db")
cur = con.cursor()
coun = cur.execute("""select count(*) from images where genre = 8""").fetchall()
con.close()
new_image = Image.new("RGB", (512, 200), (0, 0, 0))
color = self.rgbgrad.currentText()
draw = ImageDraw.Draw(new_image)
# делаем градиент
if color == "Красный":
for i in range(257):
draw.line((i * 2, 0, i * 2, 200), fill=(i, 0, 0), width=2)
elif color == "Зеленый":
for i in range(257):
draw.line((i * 2, 0, i * 2, 200), fill=(0, i, 0), width=2)
elif color == "Синий":
for i in range(257):
draw.line((i * 2, 0, i * 2, 200), fill=(0, 0, i), width=2)
# сохраняем в файл и бд
new_image.save(f"images\grad{coun[0]}.png", "PNG")
self.pixmap = QPixmap(f'images\grad{coun[0]}.png')
self.l_image.setPixmap(self.pixmap)
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("image.db")
db.open()
model = QtSql.QSqlQueryModel(parent=None)
sql_text = f"INSERT INTO images (im_file, genre, im_name) VALUES ('grad{coun[0]}',8,'Градиент{coun[0]}')"
model.setQuery(sql_text)
model.query().exec_()
self.refresh_table()
def add_image(self):
filename = QFileDialog.getOpenFileName(self, 'Выбрать картинку', '')[0]
# диалог возвращает полный путь к файлу "D:\.."
# нам нужно только имя файла - функция filename()
fname = QtCore.QFileInfo(filename).fileName()
con = sqlite3.connect("image.db")
cur = con.cursor()
# Создаем список жанров чтобы пользователь выбрал к какому жанру будет относится картинка
result = cur.execute("""SELECT genre from genres""").fetchall()
genres = []
for elem in result:
genres.append(elem[0])
con.close()
i, okBtnPressed = QInputDialog.getItem(self, "Выберите тему", "Тема",
genres, 0, False)
# к индексу в псике добавляем 1 так как ID у жанров в таблице начинаются с 1
genre_id = genres.index(i) + 1
if okBtnPressed:
im_name, okPressed = QInputDialog.getText(
self, "Ввод данных", "Введите название картинки:",
QLineEdit.Normal, "")
if okPressed:
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("image.db")
db.open()
model = QtSql.QSqlQueryModel(parent=None)
# добавляем запись в images
sql_text = f"INSERT INTO images (im_file, genre, im_name) VALUES ('{fname}',{genre_id},'{im_name}')"
model.setQuery(sql_text)
model.query().exec_()
# и обновляем таблицу
self.refresh_table()
db.close()
def gau(self):
im = self.pixmap.toImage()
x, y = im.height(), im.width()
gaus_image = Image.new("RGB", (y, x), (0, 0, 0))
# создаем новую картинку
pixels_gaus = gaus_image.load()
for i in range(x):
for j in range(y):
r, g, b, a = QColor(im.pixel(j, i)).getRgb()
pixels_gaus[j, i] = r, g, b
gaus_image = gaus_image.filter(ImageFilter.GaussianBlur(radius=10))
# сохраняем картинку в файл и бд
gaus_image.save(f"images\{self.ni}(гаус).png", "PNG")
self.pixmap = QPixmap(f'images\{self.ni}(гаус).png')
self.l_image.setPixmap(self.pixmap)
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("image.db")
db.open()
model = QtSql.QSqlQueryModel(parent=None)
sql_text = f"INSERT INTO images (im_file, genre, im_name)" \
f" VALUES ('images\{self.ni}(гаус)',8 ,'{self.ni}(гаус))'"
model.setQuery(sql_text)
model.query().exec_()
# обновляем файл
self.refresh_table()
def add_genre(self):
new_genre, okPressed = QInputDialog.getText(
self, "Ввод данных", "Введите название новой темы:",
QLineEdit.Normal, "")
if okPressed and new_genre != '':
con = sqlite3.connect("image.db")
cur = con.cursor()
# Проверяем есть ли уже такая тема. Count(*) взвращает количество строк с данным условие
# Если 0 значит таких строк нет
result = cur.execute(
f"""SELECT count(*) cc from genres where genre = '{new_genre}'"""
).fetchall()[0]
con.close()
if result[0] == 0:
db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("image.db")
db.open()
model = QtSql.QSqlQueryModel(parent=None)
# Добавляем строку в таблицу
sql_text = f"INSERT INTO genres(genre) VALUES('{new_genre}')"
model.setQuery(sql_text)
model.query().exec_()
# Обновляем данные в TableView
self.refresh_table()
db.close()
QMessageBox.about(self, "Результат",
f"Тема <{new_genre}> добавлена")
# Обновляем содержимое комбобокса с темами
self.load_genres()
else:
# Если такая тема уже есть - сообщаем об ошибке
QMessageBox().warning(self, "Ошибка", "Такая тема уже есть",
QMessageBox.Ok)
def search_by_supplier(self):
data = self.searchBySupplierName.text()
if not data == "" or not data.isspace():
qry = QtSql.QSqlQuery()
qry.prepare("select * from products where supplier = '{0}'".format(
str(data)))
# qry.bindValue(0, str(self.search.text()))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Product Code")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Product Name")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Product Price")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Sale Price")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Total Price")
model.setHeaderData(6, QtCore.Qt.Horizontal, "Quantity")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Category")
model.setHeaderData(8, QtCore.Qt.Horizontal, "Date Added")
model.setHeaderData(9, QtCore.Qt.Horizontal, "Supplier")
self.viewStock.setModel(model)
self.viewStock.horizontalHeader().setMinimumHeight(40)
self.viewStock.horizontalHeader().setDefaultSectionSize(150)
self.viewStock.setColumnHidden(0, True)
self.viewStock.setSelectionBehavior(
QtWidgets.QTableView.SelectRows)
self.viewStock.setShowGrid(True)
self.viewStock.show()
def t09_patient_add(self, sex, name, birthdate, medicalid):
self.__execute(
'''insert into patients(sex, name, birthdate, medicalid) values (%s,'%s','%s','%s')'''
% (sex, name, birthdate, medicalid))
model = QtSql.QSqlQueryModel()
model.setQuery(QtSql.QSqlQuery('select * from patients'))
return model
def search_products(self):
if not self.search.text() == '' or not self.search.text().isspace():
qry = QtSql.QSqlQuery()
qry.prepare(
"select * from products where product_code LIKE '%{0}%' or product_name LIKE '%{0}%' or "
"product_category LIKE '%{0}%' or supplier LIKE '%{0}%' or date_added LIKE '%{0}%'"
.format(str(self.search.text())))
# qry.bindValue(0, str(self.search.text()))
if qry.exec_():
model = QtSql.QSqlQueryModel()
model.setQuery(qry)
model.setHeaderData(0, QtCore.Qt.Horizontal, "ID")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Product Code")
model.setHeaderData(2, QtCore.Qt.Horizontal, "Product Name")
model.setHeaderData(3, QtCore.Qt.Horizontal, "Product Price")
model.setHeaderData(4, QtCore.Qt.Horizontal, "Sale Price")
model.setHeaderData(5, QtCore.Qt.Horizontal, "Total Price")
model.setHeaderData(6, QtCore.Qt.Horizontal, "Quantity")
model.setHeaderData(7, QtCore.Qt.Horizontal, "Category")
model.setHeaderData(8, QtCore.Qt.Horizontal, "Date Added")
model.setHeaderData(9, QtCore.Qt.Horizontal, "Supplier")
self.tableView.setModel(model)
self.tableView.horizontalHeader().setMinimumHeight(40)
self.tableView.setSelectionBehavior(
QtWidgets.QTableView.SelectRows)
self.tableView.setShowGrid(True)
self.tableView.show()
def searchTable(self, comboBox, searchLabels, searchEdits, tableView):
tablename = comboBox.currentText()
argnames = []
args = []
print(len(searchLabels))
for i in range(len(searchLabels)):
if len(searchEdits[i].text()) > 0:
args.append(searchEdits[i].text())
argnames.append(searchLabels[i].text())
query = QtSql.QSqlQuery(self.conn)
if args:
args = self.textTr.wrapStringsWith(args, "'")
parsed_args = [
argnames[i] + "=" + args[i] for i in range(len(args))
]
parsed_args = self.textTr.listToCommaStr(parsed_args, False)
print(parsed_args)
sql = "SELECT * FROM " + tablename + " WHERE " + parsed_args
else:
sql = "SELECT * FROM " + tablename
projectModel = QtSql.QSqlQueryModel()
projectModel.setQuery(sql, self.conn)
print(sql)
query.exec_(sql)
tableView.setModel(projectModel)
tableView.show()
def populate(self):
if sql.connectDB():
self.model = QtSql.QSqlQueryModel()
self.query = QtSql.QSqlQuery()
self.query.exec_("select distinct name from suppliers")
self.model.setQuery(self.query)
self.supplierName.setModel(self.model)