def get_current_course(self):
db = Bicycle_db()
query = db.insert("SELECT value FROM settings WHERE name = 'Курс'")
db.close()
if len((query)) != 0 :
course = int(query[0][0])
else:
course = 20.3
self.USD_value.setText(f"USD-{str(course)}")
return course
def cur_category_handler(self):
db = Bicycle_db()
cur_category = db.exists("cur_category")
if cur_category:
res = db.insert(
"select name_category from cur_category where id=(select max(id) from cur_category)"
)
cur_category = res[0][0]
else:
cur_category = "Всі"
return cur_category
def transtlate_category(self, category):
db = Bicycle_db()
if isinstance(category, str):
category_id = db.insert(
'SELECT id from categories where name like "%{}%"'.format(category)
)
return category_id[0]
elif isinstance(category, int):
category_id = db.insert(
"SELECT name from categories where id ={}".format(category)
)
return category_id[0][0]
def update_table(self):
db = Bicycle_db()
try:
cur_category = (
db.insert(
"select name_category from cur_category where id=(SELECT MAX(id) from cur_category)"
)
)[0][0]
except:
cur_category = None
if cur_category:
self.display_goods(category=cur_category)
else:
self.display_goods(category="Всі")
def additional_actions(self, Form):
self.add_actions(Form)
self.fill_tree()
self.treeWidget.setHeaderHidden(True)
self.lineEdit_3.setEnabled(False)
self.lineEdit_5.setEnabled(False)
self.lineEdit_6.setEnabled(False)
if self.values:
# when change_item
self.lineEdit_4.setText(self.values["Продаж"])
self.lineEdit_3.setText(self.values["Нац"])
self.lineEdit_3.setEnabled(False)
self.lineEdit_2.setText(self.values["Закупка"])
self.lineEdit_6.setText(self.values["Арт"])
self.lineEdit_5.setText(self.values["ГРН"])
self.lineEdit.setText(self.values["Название"])
self.spinBox.setValue(int(self.values["Кол-во."]))
self.label_9.setText("")
self.comboBox.hide()
self.label_4.hide()
self.pushButton_2.clicked.connect(self.edit_good)
self.pushButton_2.clicked.connect(lambda: Form.close())
# move categories
category = self.cur_category_handler()
item = QtWidgets.QTreeWidgetItem(category)
self.category_title.setText(category)
index = self.find_element_index_in_tree(category)
if len(index) == 1:
pass
# self.treeWidget.model().index(index[0])
# handle for child or not child element
else:
self.lineEdit_6.setEnabled(False)
ids_for_new_good = "SELECT MAX(article)from goods"
db = Bicycle_db()
query = db.insert(ids_for_new_good)[0][0]
good_id += 1
self.lineEdit_6.setText(str(good_id))
self.spinBox.setValue(1)
self.treeWidget.setCurrentItem(QtWidgets.QTreeWidgetItem("Bci"))
category = self.cur_category_handler()
self.category_title.setText(category)
self.pushButton_2.clicked.connect(self.store_good)
db.close()
def edit_good(self):
values = self.get_values_from_good_windows()
db = Bicycle_db()
query = """UPDATE goods SET (name,qty,buy,sell,profit,category,sell_uah,article)=("{}",{},{},{},"{}",{},{},{}) WHERE article like "%{}%";""".format(
values["name"],
values["qty"],
values["buy"],
values["sell"],
values["profit"],
values["category"],
values["sell_uah"],
values["article"],
values["article"],
)
db.insert(query)
self.table.update_table()
def set_current_category(self, category):
db = Bicycle_db()
query = "CREATE TABLE IF NOT EXISTS cur_category (id INTEGER PRIMARY KEY AUTOINCREMENT, name_category TEXT);"
query_2 = 'insert into cur_category(name_category) values("{}")'.format(
category)
db.insert(query)
db.insert(query_2)
db.close()
def get_category_values(self):
list_dict_with_results = []
db = Bicycle_db()
result = db.edit("Select * FROM categories")
count = len(result)
for item in result:
id = item[0]
name_category = item[1]
parent_id = item[2]
export_date = item[3]
list_dict_with_results.append({
"id": id,
"name_category": name_category,
"parent_id": parent_id,
"export_date": export_date,
})
# sort_by_id
return sorted(list_dict_with_results, key=lambda k: k["parent_id"])
def make_buy(self, button, cart_array, cart_table):
button_value = button.text()
if button_value == 'карта':
button_value = 'Картка'
if button_value == 'наличные':
button_value = 'Готівка'
if button_value == 'терминал':
button_value = 'Термінал'
print(button_value)
if len(cart_array) > 0:
"""values from second cart table"""
cart_values_from_table = cart_table.get_values_from_cart()
db = Bicycle_db()
schema = db.schema['basket']
date = datetime.datetime.now()
dated = date.strftime('%M/%d/%Y %H:%m:00')
values = []
for cart_item in range(len(cart_values_from_table)):
if cart_values_from_table[cart_item] in cart_array:
values.append(
'VALUES((SELECT MAX(id)from basket)+1,{},{},{},{},{},{},{},{},"","{}"'
.format(cart_values_from_table[cart_item]['цена'],
cart_values_from_table[cart_item]['кол-во'],
cart_values_from_table[cart_item]['сумма'],
cart_array[cart_item]['Арт'], button_value,
cart_array[cart_item]['Продаж'], dated,
cart_array[cart_item]['Название']))
else:
values.append(
'VALUES((SELECT MAX(id)from basket)+1,{},{},{},"",{},{},{},{},"",{}'
.format(cart_values_from_table['цена'],
cart_values_from_table['кол-во'],
cart_values_from_table['сумма'], button_value,
cart_values_from_table['цена'], dated,
cart_values_from_table['Название']))
main_query = 'INSERT INTO basket ({})'.format(schema)
for value in values:
main_query += value
db.insert(main_query)
else:
error_dialog = QtWidgets.QErrorMessage(self.tab)
error_dialog.showMessage("Нет товаров в корзине")
def store_good(self):
values = self.get_values_from_good_windows()
db = Bicycle_db()
schema = db.schema["goods"]
str_schema = ",".join(schema)
query = 'insert into "goods"({}) values("","{}",{},{},{},{},{},"{}","{}",{})'.format(
str_schema,
values["name"],
values["qty"],
values["buy"],
values["sell"],
str(values["profit"]).split("%")[0],
values["category"],
"USD",
values["sell_uah"],
values["article"],
)
db.insert(query)
self.table.update_table()
self.update_good_window()
def get_goods(self, category_name=False, display_all=None):
db = Bicycle_db()
if display_all or category_name == "Всі":
goods = db.edit("Select * from goods")
db.close()
return self.from_sqlgoods_to_dict(goods)
else:
if category_name is None:
category_name = "Всі"
category_id = db.select(
'SELECT id from categories where name like "%{}%"'.format(
category_name
)
)
goods = db.edit(
'Select * from goods where category like "%{}%";'.format(category_id[0])
)
db.close()
return self.from_sqlgoods_to_dict(goods)
def contextMenuEvent(self, event):
menu = QtWidgets.QMenu(self)
add_Action = menu.addAction("добавить")
edit_Action = menu.addAction("редактировать")
remove_Action = menu.addAction("удалить")
action = menu.exec_(self.mapToGlobal(event.pos()))
if action == add_Action:
widget = QDialog()
ui = GoodsForm(table=self)
ui.setupUi(widget)
widget.exec_()
if action == remove_Action:
reply = QtWidgets.QMessageBox.question(
self,
"Удалить товар?",
"Вы уверенны что хотите удалить?",
QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No,
QtWidgets.QMessageBox.No,
)
if reply == QtWidgets.QMessageBox.Yes:
values = self.parse_row()
if values["Кол-во."] != "0":
# widget = QDialog()
error_dialog = QtWidgets.QErrorMessage(self)
error_dialog.showMessage("товар с количеством удалить нельзя")
else:
db = Bicycle_db()
db.insert(
"DELETE FROM goods WHERE article LIKE '%{}%'".format(
values["Арт"]
)
)
self.update_table()
db.close()
if action == edit_Action:
widget = QDialog()
ui = GoodsForm(
table=self,
values=self.parse_row(),
category_widget=self.category_widget,
)
ui.setupUi(widget)
widget.exec_()
def get_casher_elements():
query = f'SELECT * from basket where dated between "{date_from}" and "{date_by}"'
db = Bicycle_db()
res = db.insert(query)
return res
def contextMenuEvent(self, event):
# handle right_click
menu = QtWidgets.QMenu(self)
add_category_Action = menu.addAction("добавить категорию")
add_sub_category_Action = menu.addAction("добавить подкатегорию")
edit_category_Action = menu.addAction("редактировать категорию")
remove_category_Action = menu.addAction("удалить категорию")
action = menu.exec_(self.mapToGlobal(event.pos()))
db = Bicycle_db()
if action == add_category_Action:
text, ok = QtWidgets.QInputDialog.getText(self,
"Добавить категорию",
"Имя для категории:")
if ok and text != "":
rowcount = self.topLevelItemCount()
self.addTopLevelItem(QtWidgets.QTreeWidgetItem(rowcount))
self.topLevelItem(rowcount).setText(0, text)
res = db.insert(
'INSERT INTO categories (id,name,parent_id) values((SELECT MAX(id)from categories)+1,"{}",-1);'
.format(text))
print(res)
db.close()
if action == remove_category_Action:
item = self.currentItem()
for SelectedItem in self.selectedItems():
if SelectedItem.text(0) == item.text(0):
# check category is not null
qm = QtWidgets.QMessageBox()
ret = qm.question(self, "", "Удалить категорию?",
qm.Yes | qm.No)
if ret == qm.Yes:
SelectedItem.removeChild(item)
text = item.text(0)
query = 'DELETE FROM categories WHERE name LIKE "%{}%";'.format(
text)
db.insert(query)
db.close()
if action == edit_category_Action:
if self.selectedItems():
old_name = self.currentItem().text(0)
item = self.selectedItems()[0]
text, ok = QtWidgets.QInputDialog.getText(
self,
"Редактировать",
"редактировать:",
QtWidgets.QLineEdit.Normal,
item.text(0),
)
if ok and text != "":
item.setText(0, text)
res = db.insert(
'UPDATE categories set name = "{}" where name like "%{}%";'
.format(text, old_name))
if action == add_sub_category_Action:
text, ok = QtWidgets.QInputDialog.getText(self,
"Добавить подкатегорию",
"Имя для подкатегории:")
if ok and text != "":
db = Bicycle_db()
if len(self.selectedItems()) > 0:
QtWidgets.QTreeWidgetItem(self.selectedItems()[0], [text])
parent_id = db.insert(
'select id from categories where name like "%{}%"'.
format(self.currentItem().text(0)))
db.insert(
'INSERT INTO categories (id,name,parent_id) values((SELECT MAX(id)from categories)+1,"{}",{});'
.format(text, parent_id[0][0]))
else:
print("here")
QtWidgets.QTreeWidgetItem(self, [text])
res = db.insert(
'INSERT INTO categories (id,name,parent_id) values((SELECT MAX(id)from categories)+1,"{}",-1);'
.format(text))
def closeEvent(self, event):
db = Bicycle_db()
db.insert("drop table if exists cur_category")
db.close()
event.accept()
def make_buy(self, button, cart_array, cart_table):
button_value = button.text()
if button_value == "карта":
button_value = "Картка"
if button_value == "Наличные":
button_value = "Готівка"
if button_value == "терминал":
button_value = "Термінал"
print(button_value)
if len(cart_array) > 0:
"""values from second cart table"""
cart_values_from_table = cart_table.get_values_from_cart()
db = Bicycle_db()
schema = ",".join(db.schema["basket"])
date = datetime.datetime.now()
dated = date.strftime("%m/%d/%y %H:%M:00")
values = []
for cart_item in range(len(cart_values_from_table)):
for cart_array_item in cart_array:
if "Aрт" in cart_values_from_table[cart_item]:
if (cart_values_from_table[cart_item]["название"] ==
cart_array_item["Название"]):
values.append(
'((SELECT MAX(id)from basket)+1,{},{},{},{},"{}",{},"{}","","{}")'
.format(
cart_values_from_table[cart_item]["цена"],
cart_values_from_table[cart_item]
["кол-во"],
cart_values_from_table[cart_item]["сумма"],
cart_array[cart_item]["Арт"],
button_value,
cart_array_item["Продаж"],
dated,
cart_array_item["Название"],
))
break
else:
values.append(
'((SELECT MAX(id)from basket)+1,{},{},{},"","{}",{},"{}","","{}")'
.format(
cart_values_from_table[cart_item]["цена"],
cart_values_from_table[cart_item]["кол-во"],
cart_values_from_table[cart_item]["сумма"],
button_value,
cart_values_from_table[cart_item]["цена"],
dated,
cart_values_from_table[cart_item]["название"],
))
break
main_query = "INSERT INTO basket ({}) VALUES".format(schema)
for value in range(len(values)):
main_query += values[value]
if value + 1 != len(values):
main_query += ","
db.insert(main_query)
self.setDefaultTime()
self.get_basket_items_by_date()
self.tableWidget_2.clean_table()
# TODO add operation for delete get values from cart array and delete each from goods table
self.cart_items = []
# update main_table
else:
error_dialog = QtWidgets.QErrorMessage(self.tab)
error_dialog.showMessage("Нет товаров в корзине")