def set_start_settings(self):
query = "SELECT Id, Name FROM accessories_name WHERE For_Beika = 1"
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения разрешеной фурнитуры", sql_info.msg, QMessageBox.Ok)
return False
if len(sql_info) > 1:
QMessageBox.critical(self, "Ошибка фурнитуры", "Выбрано 2 фурнитуры! Это не правильно!", QMessageBox.Ok)
self.close()
self.destroy()
return False
elif len(sql_info) == 1:
self.le_accessories_name.setWhatsThis(str(sql_info[0][0]))
self.le_accessories_name.setText(str(sql_info[0][1]))
elif len(sql_info) == 0:
self.le_accessories_name.setWhatsThis("None")
self.le_accessories_name.setText("None")
query = "SELECT Id, Name FROM material_name WHERE For_Beika = 1 ORDER BY Name"
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения разрешеной ткани", sql_info.msg, QMessageBox.Ok)
return False
self.lw_material_name.clear()
for material in sql_info:
item = QListWidgetItem(material[1])
item.setData(-1, material[0])
self.lw_material_name.addItem(item)
def ui_beika(self):
self.timer.start(900000)
query = "SELECT Id, Name FROM accessories_name WHERE For_Beika = 1"
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
return False
if len(sql_info) == 1:
self.beika_accessories_id = sql_info[0][0]
else:
return False
query = """SELECT Id, Name FROM material_name WHERE For_Beika = 1 ORDER BY Name"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
return False
for material in sql_info:
item = QListWidgetItem(material[1])
item.setData(-1, (material[0]))
item.setTextAlignment(Qt.AlignHCenter)
self.lw_material.addItem(item)
self.lw_material.setSpacing(5)
self.sw_main.setCurrentIndex(9)
def set_id(self, _id):
self.__access = {}
query = """SELECT staff_worker_info.Id, staff_worker_info.First_Name, staff_worker_info.Last_Name, staff_position.Id, staff_position.Name
FROM staff_worker_info LEFT JOIN staff_position ON staff_worker_info.Position_Id = staff_position.Id
WHERE staff_worker_info.Id = %s"""
sql_info = my_sql.sql_select(query, (_id, ))
if "mysql.connector.errors" in str(type(sql_info)):
raise RuntimeError("Не смог получить операции артикула")
self.__id = sql_info[0][0]
self.__f_name = sql_info[0][1]
self.__l_name = sql_info[0][2]
self.__position_id = sql_info[0][3]
self.__position_name = sql_info[0][4]
query = """SELECT Class, Atr1, Atr2, Atr_Value FROM access WHERE Worker_Id = %s ORDER BY Class"""
sql_info = my_sql.sql_select(query, (_id,))
if "mysql.connector.errors" in str(type(sql_info)):
raise RuntimeError("Не смог получить список доступов по работнику")
if not sql_info:
query = """SELECT Class, Atr1, Atr2, Atr_Value FROM access WHERE Staff_Position_Id = %s ORDER BY Class"""
sql_info = my_sql.sql_select(query, (self.__position_id,))
if "mysql.connector.errors" in str(type(sql_info)):
raise RuntimeError("Не смог получить список доступов по категории")
for item in sql_info:
if item[0] not in self.__access:
self.__access[item[0]] = []
self.__access[item[0]].append({"atr1": item[1], "atr2": item[2], "value":item[3]})
print(self.__access)
def ui_beika(self):
self.timer.start(900000)
query = "SELECT Id, Name FROM accessories_name WHERE For_Beika = 1"
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
return False
if len(sql_info) == 1:
self.beika_accessories_id = sql_info[0][0]
else:
return False
query = """SELECT Id, Name FROM material_name WHERE For_Beika = 1 ORDER BY Name"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
return False
for material in sql_info:
item = QListWidgetItem(material[1])
item.setData(-1, (material[0]))
item.setTextAlignment(Qt.AlignHCenter)
self.lw_material.addItem(item)
self.lw_material.setSpacing(5)
self.sw_main.setCurrentIndex(9)
def set_table_road(self):
date_old = self.de_road_date.date()
date_old = datetime.date(date_old.year(), date_old.month(), date_old.day())
query = """SELECT One_year, Many_year FROM program_settings_road"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение цен оплаты проезда", sql_info.msg, QMessageBox.Ok)
return False
self.one_year_price = sql_info[0][0]
self.many_year_price = sql_info[0][1]
query = """SELECT Id, First_Name, Last_Name, Date_Recruitment
FROM staff_worker_info
WHERE Date_Recruitment < %s AND `Leave` = 0"""
sql_info = my_sql.sql_select(query, (date_old.replace(date_old.year-1), ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение сотрудников со стажем", sql_info.msg, QMessageBox.Ok)
return False
self.tw_road.clearContents()
self.tw_road.setRowCount(0)
for row, worker in enumerate(sql_info):
self.tw_road.insertRow(row)
delta_date = date_old - worker[3]
if delta_date.days < 730:
color = QBrush(QColor(252, 252, 139, 255))
price = self.one_year_price
else:
color = QBrush(QColor(252, 190, 139, 255))
price = self.many_year_price
new_table_item = QTableWidgetItem(str(worker[2]) + " " + str(worker[1]))
new_table_item.setData(-2, worker[0])
new_table_item.setBackground(color)
new_table_item.setFlags(Qt.ItemIsEnabled)
self.tw_road.setItem(row, 0, new_table_item)
new_table_item = QTableWidgetItem(worker[3].strftime("%d.%m.%Y"))
new_table_item.setData(-2, worker[0])
new_table_item.setBackground(color)
new_table_item.setFlags(Qt.ItemIsEnabled)
self.tw_road.setItem(row, 1, new_table_item)
new_table_item = QTableWidgetItem(str(delta_date.days))
new_table_item.setData(-2, worker[0])
new_table_item.setBackground(color)
new_table_item.setFlags(Qt.ItemIsEnabled)
self.tw_road.setItem(row, 2, new_table_item)
new_table_item = QTableWidgetItem(str(price))
new_table_item.setData(-2, worker[0])
new_table_item.setBackground(color)
self.tw_road.setItem(row, 3, new_table_item)
def ui_calc(self):
query = """SELECT staff_worker_info.Id, staff_worker_info.Last_Name,
min((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
avg((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
max((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id)))
FROM cut LEFT JOIN staff_worker_info ON cut.Worker_Id = staff_worker_info.Id
WHERE cut.Date_Cut >= %s AND cut.Date_Cut <= %s
GROUP BY cut.Worker_Id"""
sql_info = my_sql.sql_select(query, (self.de_date_from.date().toString(
Qt.ISODate), self.de_date_to.date().toString(Qt.ISODate)))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение значение обрези",
sql_info.msg, QMessageBox.Ok)
return False
self.tw_work.clearContents()
self.tw_work.setRowCount(0)
if not sql_info:
return False
list_awg = []
for table_typle in sql_info:
self.tw_work.insertRow(self.tw_work.rowCount())
list_awg.append(table_typle[3])
for column in range(1, len(table_typle)):
if isinstance(table_typle[column], Decimal):
text = str(round(float(table_typle[column]), 4))
else:
text = str(table_typle[column])
item = QTableWidgetItem(text)
item.setData(5, table_typle[0])
self.tw_work.setItem(self.tw_work.rowCount() - 1, column - 1,
item)
query = """SELECT min((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
avg((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
max((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id)))
FROM cut WHERE cut.Date_Cut >= %s AND cut.Date_Cut <= %s"""
sql_info = my_sql.sql_select(query, (self.de_date_from.date().toString(
Qt.ISODate), self.de_date_to.date().toString(Qt.ISODate)))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение значение обрези",
sql_info.msg, QMessageBox.Ok)
return False
if not sql_info:
return False
self.le_min.setText(str(round(sql_info[0][0], 4)))
self.le_awg.setText(str(round(sql_info[0][1], 4)))
self.le_max.setText(str(round(sql_info[0][2], 4)))
def ui_calc(self):
query = """SELECT staff_worker_info.Id, staff_worker_info.Last_Name,
min((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
avg((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
max((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id)))
FROM cut LEFT JOIN staff_worker_info ON cut.Worker_Id = staff_worker_info.Id
WHERE cut.Date_Cut >= %s AND cut.Date_Cut <= %s
GROUP BY cut.Worker_Id"""
sql_info = my_sql.sql_select(query, (self.de_date_from.date().toString(Qt.ISODate), self.de_date_to.date().toString(Qt.ISODate)))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение значение обрези", sql_info.msg, QMessageBox.Ok)
return False
self.tw_work.clearContents()
self.tw_work.setRowCount(0)
if not sql_info:
return False
list_awg = []
for table_typle in sql_info:
self.tw_work.insertRow(self.tw_work.rowCount())
list_awg.append(table_typle[3])
for column in range(1, len(table_typle)):
if isinstance(table_typle[column], Decimal):
text = str(round(float(table_typle[column]), 4))
else:
text = str(table_typle[column])
item = QTableWidgetItem(text)
item.setData(5, table_typle[0])
self.tw_work.setItem(self.tw_work.rowCount() - 1, column - 1, item)
query = """SELECT min((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
avg((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id))),
max((cut.Weight_Rest * 100) / (cut.Weight_Rest + (SELECT SUM(Weight) FROM pack WHERE pack.Cut_Id = cut.Id)))
FROM cut WHERE cut.Date_Cut >= %s AND cut.Date_Cut <= %s"""
sql_info = my_sql.sql_select(query, (self.de_date_from.date().toString(Qt.ISODate), self.de_date_to.date().toString(Qt.ISODate)))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение значение обрези", sql_info.msg, QMessageBox.Ok)
return False
if not sql_info:
return False
self.le_min.setText(str(round(sql_info[0][0], 4)))
self.le_awg.setText(str(round(sql_info[0][1], 4)))
self.le_max.setText(str(round(sql_info[0][2], 4)))
def ui_calc(self):
date = self.de_pay_date.date()
if date.toString(Qt.ISODate) == "2000-01-01":
QMessageBox.critical(self, "Ошибка",
"Выберите дату начисления зарплаты",
QMessageBox.Ok)
return False
elif date.day() != date.daysInMonth():
QMessageBox.critical(
self, "Ошибка",
"Вы выбрали не последний день. Пока это запрещено",
QMessageBox.Ok)
return False
query = """SELECT COUNT(*)
FROM pack_operation
WHERE Date_Pay = %s"""
sql_info = my_sql.sql_select(query, (date.toString(1), ))
if "mysql.connector.errors" in str(type(sql_info)):
print("Не смог проверить дату")
return False
if sql_info[0][0] != 0:
QMessageBox.critical(self, "Ошибка", "На эту дату уже был расчет",
QMessageBox.Ok)
return False
self.date_pay = datetime(date.year(), date.month(), date.day()).date()
self.start_sql_info()
def set_pack_info(self):
query = """SELECT pack.Value_Pieces, product_article.Article, pack.Size, clients.Name, DATE_FORMAT(cut.Date_Cut, '%d.%m.%Y')
FROM pack LEFT JOIN cut ON pack.Cut_Id = cut.Id
LEFT JOIN product_article_parametrs ON pack.Article_Parametr_Id = product_article_parametrs.Id
LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
LEFT JOIN clients ON pack.Client_Id = clients.Id
WHERE pack.Id = %s"""
sql_info = my_sql.sql_select(query, (self.cut["pack_id"], ))
if "mysql.connector.errors" in str(type(sql_info)):
self.lb_pack_error.setText(
'<html><head/><body><p align="center"><span style=" color:#ff0000;">Не смог получить пачку (Err BD)</span></p></body></html>'
)
return False
if sql_info:
self.cut["pack_value"] = sql_info[0][0]
self.cut["article_name"] = sql_info[0][1]
self.cut["pack_size"] = sql_info[0][2]
self.cut["pack_client"] = sql_info[0][3]
self.cut["cut_date"] = sql_info[0][4]
else:
self.lb_pack_error.setText(
'<html><head/><body><p align="center"><span style=" color:#ff0000;">Пустой ответ пачки (Err BD)</span></p></body></html>'
)
self.le_operation_cut.setText(str(self.cut["cut_id"]))
self.le_operation_pack.setText(str(self.cut["pack_number"]))
self.le_operation_pack_value.setText(str(self.cut["pack_value"]))
self.le_operation_article.setText(str(self.cut["article_name"]))
self.le_operation_size.setText(str(self.cut["pack_size"]))
self.le_operation_client.setText(str(self.cut["pack_client"]))
self.le_operation_cut_date.setText(str(self.cut["cut_date"]))
def set_table_info(self):
self.table_widget.setSortingEnabled(False)
self.table_items = my_sql.sql_select(self.query_table_select)
if "mysql.connector.errors" in str(type(self.table_items)):
QMessageBox.critical(self, "Ошибка sql получение таблицы", self.table_items.msg, QMessageBox.Ok)
return False
self.table_widget.clearContents()
self.table_widget.setRowCount(0)
if not self.table_items:
return False
for table_typle in self.table_items:
self.table_widget.insertRow(self.table_widget.rowCount())
for column in range(2, len(table_typle)):
item = QTableWidgetItem(str(table_typle[column]))
item.setData(5, table_typle[0])
self.table_widget.setItem(self.table_widget.rowCount() - 1, column - 2, item)
self.table_widget.setSortingEnabled(True)
try:
item = self.tree_widget.currentItem()
if item.data(0, 5) >= 0: # Проверка не выбрано ли показать все!
self.ui_sorting(item)
except:
pass
if self.select_item:
self.open_id(self.select_item)
self.select_item = None
def ui_other(self):
try:
item_id = self.table_widget.selectedItems()[0].data(5)
except:
QMessageBox.critical(self, "Ошибка ", "Выделите операцию", QMessageBox.Ok)
return False
query = """SELECT CONCAT(product_article.Article, ' ', product_article_size.Size, ' ', product_article_parametrs.Name) FROM operations
LEFT JOIN product_article_operation ON operations.Id = product_article_operation.Operation_Id
LEFT JOIN product_article_parametrs ON product_article_operation.Product_Article_Parametrs_Id = product_article_parametrs.Id
LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
WHERE operations.Id = %s"""
sql_art = my_sql.sql_select(query, (item_id, ))
if "mysql.connector.errors" in str(type(sql_art)):
QMessageBox.critical(self, "Ошибка sql получения зависимости", sql_art.msg, QMessageBox.Ok)
return False
text = ""
count_art = 0
for art in sql_art:
if art[0] is None:
text = "Нет совпадений"
break
text += art[0] + "\n"
count_art += 1
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setWindowTitle("Совпадения")
msg.setText("Найдено %s совпадений" % count_art)
msg.setDetailedText(text)
msg.exec()
def ui_acc_id(self):
try:
id_scan = int(self.le_number.text())
except:
return False
query = """SELECT COUNT(Id) FROM pack WHERE Id = %s"""
count_id = my_sql.sql_select(query, (id_scan, ))
if "mysql.connector.errors" in str(type(count_id)):
QMessageBox.critical(self, "Ошибка sql поиска пачки", count_id.msg,
QMessageBox.Ok)
return False
if count_id[0][0] != 1:
if self.scan_article:
res = QMessageBox.information(
self, "Ошибка номера",
"Нет пачки с таким номером, искать артикул?",
QMessageBox.Yes, QMessageBox.No)
if res == QMessageBox.Yes:
self.search_article()
self.le_number.setText("")
return False
else:
QMessageBox.information(self, "Ошибка номера",
"Нет пачки с таким номером",
QMessageBox.Ok)
return False
self.le_number.setText("")
self.pack = pack.PackBrows(pack_id=id_scan)
self.pack.setModal(True)
self.pack.show()
def ui_change_item(self, id=False):
if id:
id_select = id
else:
try:
id_select = self.lw_list.selectedItems()[0].data(3)
except:
QMessageBox.critical(self, "Ошибка", "Выберете элемент",
QMessageBox.Ok)
return False
sql_result = my_sql.sql_select(self.sql_change_select, (id_select, ))
if "mysql.connector.errors" in str(type(sql_result)):
QMessageBox.critical(self, "Ошибка sql получение предмета",
sql_result.msg, QMessageBox.Ok)
return False
change_item = item_2.Item2()
change_item.set_settings(self.set_new_win)
change_item.le_name.setText(sql_result[0][0])
change_item.pe_note.appendPlainText(sql_result[0][1])
change_item.setModal(True)
change_item.show()
if change_item.exec() == 0:
return False
sql_result = my_sql.sql_change(
self.sql_update_select,
(change_item.le_name.text(), change_item.pe_note.toPlainText(),
id_select))
if "mysql.connector.errors" in str(type(sql_result)):
QMessageBox.critical(self, "Ошибка sql добавление предмета",
sql_result.msg, QMessageBox.Ok)
return False
self.sql_set_list()
def set_table_info(self):
self.table_items = my_sql.sql_select(self.query_table_select,
(self.other_value, ))
if "mysql.connector.errors" in str(type(self.table_items)):
QMessageBox.critical(self, "Ошибка sql получение таблицы",
self.table_items.msg, QMessageBox.Ok)
return False
self.table_widget.clearContents()
self.table_widget.setRowCount(0)
if not self.table_items:
return False
for table_typle in self.table_items:
self.table_widget.insertRow(self.table_widget.rowCount())
for column in range(1, len(table_typle)):
if isinstance(table_typle[column], Decimal):
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ',
str(table_typle[column]))
elif isinstance(table_typle[column], datetime.date):
text = table_typle[column].strftime("%d.%m.%Y")
else:
text = str(table_typle[column])
if table_typle[2] >= 0:
color = QBrush(QColor(150, 255, 161, 255))
else:
color = QBrush(QColor(255, 255, 153, 255))
item = QTableWidgetItem(text)
item.setData(5, table_typle[0])
item.setBackground(color)
self.table_widget.setItem(self.table_widget.rowCount() - 1,
column - 1, item)
def load_data(self, sql_id):
query = """SELECT Id, audit_date, workers, supply_id, type_material, supply_provider, supply_act, supply_date,
supply_value, sertificat, type_pack, audit_location, gost, audit_value, quality, density
FROM audit WHERE Id = %s"""
sql_info = my_sql.sql_select(query, (sql_id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql при получении информации",
sql_info.msg, QMessageBox.Ok)
return False
self.ui_le_id.setText(str(sql_info[0][0]))
self.ui_de_act_date.setDate(sql_info[0][1])
self.ui_le_workers.setText(sql_info[0][2])
self.ui_le_supply.setText(str(sql_info[0][3]))
self.ui_le_type_material.setText(sql_info[0][4])
self.ui_le_supply_provider.setText(sql_info[0][5])
self.ui_le_supply_act.setText(sql_info[0][6])
self.ui_de_supply_date.setDate(sql_info[0][7])
self.ui_le_supply_value.setText(str(sql_info[0][8]))
self.ui_le_sertificat.setText(sql_info[0][9])
self.ui_le_type_pack.setText(sql_info[0][10])
self.ui_le_audit_location.setText(sql_info[0][11])
self.ui_le_gost.setText(sql_info[0][12])
self.ui_le_audit_value.setText(str(sql_info[0][13]))
self.ui_cb_quality.setCurrentText(sql_info[0][14])
if sql_info[0][15]:
self.ui_le_density.setText(str(sql_info[0][15]))
def ui_select_position(self):
query = """SELECT Id, Class, Atr1, Atr2, Atr_Value FROM access WHERE Staff_Position_Id = %s ORDER BY Class"""
sql_info = my_sql.sql_select(query, (self.cb_position.currentData(),))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql при получении доступов", sql_info.msg, QMessageBox.Ok)
return False
self.worker = None
self.position = self.cb_position.currentData()
self.tw_access.clearContents()
self.tw_access.setRowCount(0)
for row, item in enumerate(sql_info):
self.tw_access.insertRow(row)
new_table_item = QTableWidgetItem(str(item[1]))
new_table_item.setData(-2, item[0])
self.tw_access.setItem(row, 0, new_table_item)
new_table_item = QTableWidgetItem(str(item[2]))
new_table_item.setData(-2, item[0])
self.tw_access.setItem(row, 1, new_table_item)
new_table_item = QTableWidgetItem(str(item[3]))
new_table_item.setData(-2, item[0])
self.tw_access.setItem(row, 2, new_table_item)
new_table_item = QTableWidgetItem(str(item[4]))
new_table_item.setData(-2, item[0])
self.tw_access.setItem(row, 3, new_table_item)
def of_tree_select_order(self, order):
query = """SELECT `order`.Id, clients.Name, clients_actual_address.Name, `order`.Number_Order, `order`.Date_Shipment, `order`.Number_Doc
FROM `order` LEFT JOIN clients ON `order`.Client_Id = clients.Id
LEFT JOIN clients_actual_address ON `order`.Clients_Adress_Id = clients_actual_address.Id
WHERE `order`.Id = %s"""
sql_info = my_sql.sql_select(query, (order[1],))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения информации о заказе", sql_info.msg, QMessageBox.Ok)
return False
if not sql_info:
return False
order = sql_info[0]
self.tw_order.insertRow(self.tw_order.rowCount())
table_item = QTableWidgetItem(str(order[1]))
table_item.setData(-1, order[0])
self.tw_order.setItem(self.tw_order.rowCount()-1, 0, table_item)
table_item = QTableWidgetItem(str(order[2]))
table_item.setData(-1, order[0])
self.tw_order.setItem(self.tw_order.rowCount()-1, 1, table_item)
table_item = QTableWidgetItem(str(order[3]))
table_item.setData(-1, order[0])
self.tw_order.setItem(self.tw_order.rowCount()-1, 2, table_item)
table_item = QTableWidgetItem(order[4].strftime("%d.%m.%Y"))
table_item.setData(-1, order[0])
self.tw_order.setItem(self.tw_order.rowCount()-1, 3, table_item)
table_item = QTableWidgetItem(str(order[5]))
table_item.setData(-1, order[0])
self.tw_order.setItem(self.tw_order.rowCount()-1, 4, table_item)
def ui_calc(self):
query = """SELECT pack.Cut_Id, pack.Number, CONCAT(product_article.Article, ' (', product_article_size.Size, ') [', product_article_parametrs.Name, ']'),
clients.Name, pack.Date_Make
FROM pack LEFT JOIN clients ON pack.Client_Id = clients.Id
LEFT JOIN product_article_parametrs ON pack.Article_Parametr_Id = product_article_parametrs.Id
LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
WHERE pack.Date_Make >= %s AND pack.Date_Make <= %s"""
sql_info = my_sql.sql_select(query, (self.de_date_from.date().toString(Qt.ISODate), self.de_date_to.date().toString(Qt.ISODate)))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения принятых пачек", sql_info.msg, QMessageBox.Ok)
return False
self.tableWidget.clearContents()
self.tableWidget.setRowCount(0)
if not sql_info:
return False
for table_typle in sql_info:
self.tableWidget.insertRow(self.tableWidget.rowCount())
for column in range(len(table_typle)):
if isinstance(table_typle[column], datetime.date):
text = table_typle[column].strftime("%d.%m.%Y")
else:
text = str(table_typle[column])
item = QTableWidgetItem(text)
item.setData(5, table_typle[0])
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, column , item)
def set_table_info(self):
self.table_widget.setSortingEnabled(False)
self.table_items = my_sql.sql_select(self.query_table_select)
if "mysql.connector.errors" in str(type(self.table_items)):
QMessageBox.critical(self, "Ошибка sql получение таблицы",
self.table_items.msg, QMessageBox.Ok)
return False
self.table_widget.clearContents()
self.table_widget.setRowCount(0)
if not self.table_items:
return False
for table_typle in self.table_items:
self.table_widget.insertRow(self.table_widget.rowCount())
for column in range(2, len(table_typle)):
item = QTableWidgetItem(str(table_typle[column]))
item.setData(5, table_typle[0])
self.table_widget.setItem(self.table_widget.rowCount() - 1,
column - 2, item)
self.table_widget.setSortingEnabled(True)
try:
item = self.tree_widget.currentItem()
if item.data(0, 5) >= 0: # Проверка не выбрано ли показать все!
self.ui_sorting(item)
except:
pass
if self.select_item:
self.open_id(self.select_item)
self.select_item = None
def ui_change_weight(self):
try:
weight = float(self.le_weight.text().replace(",", "."))
self.need_weight = False
query = """SELECT SUM(material_balance.BalanceWeight) FROM material_balance
LEFT JOIN material_supplyposition ON material_balance.Material_SupplyPositionId = material_supplyposition.Id
WHERE material_supplyposition.Material_NameId = %s"""
sql_info = my_sql.sql_select(query, (self.le_material_name.whatsThis(),))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql баланс ткани", sql_info.msg, QMessageBox.Ok)
return False
if sql_info[0][0] is None:
self.le_weight.setStyleSheet("border: 4px solid;\nborder-color: rgb(247, 84, 84);")
self.le_weight.setToolTip("Нету такой ткани на складе")
elif weight is not None and float(sql_info[0][0]) < weight:
self.le_weight.setStyleSheet("border: 4px solid;\nborder-color: rgb(247, 84, 84);")
self.le_weight.setToolTip("Этой ткани не хватит для изменения расходов")
elif weight is None or float(sql_info[0][0]) >= weight:
self.le_weight.setStyleSheet("border: 4px solid;\nborder-color: rgb(122, 247, 84);")
self.le_weight.setToolTip("Новой ткани хватает")
self.need_weight = True
self.ui_material_sum_calc()
else:
self.le_weight.setStyleSheet("border: 4px solid;\nborder-color: rgb(247, 84, 84);")
self.le_weight.setToolTip("Что то не так при проверке новой ткани (Обратитесь к админу)")
except:
self.le_weight.setStyleSheet("border: 4px solid;\nborder-color: rgb(247, 84, 84);")
self.le_weight.setToolTip("Что то не так при проверке новой ткани (Обратитесь к админу)")
def set_pack_info(self):
query = """SELECT pack.Value_Pieces, product_article.Article, pack.Size, clients.Name, DATE_FORMAT(cut.Date_Cut, '%d.%m.%Y')
FROM pack LEFT JOIN cut ON pack.Cut_Id = cut.Id
LEFT JOIN product_article_parametrs ON pack.Article_Parametr_Id = product_article_parametrs.Id
LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
LEFT JOIN clients ON pack.Client_Id = clients.Id
WHERE pack.Id = %s"""
sql_info = my_sql.sql_select(query, (self.cut["pack_id"], ))
if "mysql.connector.errors" in str(type(sql_info)):
self.lb_pack_error.setText('<html><head/><body><p align="center"><span style=" color:#ff0000;">Не смог получить пачку (Err BD)</span></p></body></html>')
return False
if sql_info:
self.cut["pack_value"] = sql_info[0][0]
self.cut["article_name"] = sql_info[0][1]
self.cut["pack_size"] = sql_info[0][2]
self.cut["pack_client"] = sql_info[0][3]
self.cut["cut_date"] = sql_info[0][4]
else:
self.lb_pack_error.setText('<html><head/><body><p align="center"><span style=" color:#ff0000;">Пустой ответ пачки (Err BD)</span></p></body></html>')
self.le_operation_cut.setText(str(self.cut["cut_id"]))
self.le_operation_pack.setText(str(self.cut["pack_number"]))
self.le_operation_pack_value.setText(str(self.cut["pack_value"]))
self.le_operation_article.setText(str(self.cut["article_name"]))
self.le_operation_size.setText(str(self.cut["pack_size"]))
self.le_operation_client.setText(str(self.cut["pack_client"]))
self.le_operation_cut_date.setText(str(self.cut["cut_date"]))
def calc_order_position(self, order_id):
"""Собирает все артикула заказа в словарь вида
{
Артикул:{
Размер: кол-во
Размер: кол-во
...
}
...
}"""
query = """SELECT product_article.Article, product_article_size.Size, order_position.Value
FROM order_position LEFT JOIN product_article_parametrs ON order_position.Product_Article_Parametr_Id = product_article_parametrs.Id
LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
WHERE Order_Id = %s"""
sql_info = my_sql.sql_select(query, (order_id,))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения позиций заказа", sql_info.msg, QMessageBox.Ok)
return False
all_position = OrderedDict()
for position_sql in sql_info:
article = all_position.setdefault(position_sql[0], {})
value = article.setdefault(position_sql[1], 0)
article[position_sql[1]] = value + position_sql[2]
return all_position
def set_settings(self):
if self.traffic_id:
query = """SELECT Position, Data, Table_Data, Note FROM staff_worker_traffic WHERE Id = %s"""
sql_traffic = my_sql.sql_select(query, (self.traffic_id, ))
if "mysql.connector.errors" in str(type(sql_traffic)):
QMessageBox.critical(self, "Ошибка sql получения записей", sql_traffic.msg, QMessageBox.Ok)
return False
self.le_position.setText(str(sql_traffic[0][0]))
self.dt_date.setDateTime(sql_traffic[0][1])
self.le_note.setText(sql_traffic[0][3])
if sql_traffic[0][2]:
self.dt_tabel_date.setDateTime(sql_traffic[0][2])
else:
min = sql_traffic[0][1].minute
if 0 <= min <= 15:
tab_date = sql_traffic[0][1].replace(minute=0)
elif 16 <= min <= 45:
tab_date = sql_traffic[0][1].replace(minute=30)
else:
tab_date = sql_traffic[0][1].replace(minute=0)
hour_up = sql_traffic[0][1].hour + 1
if hour_up < 24:
tab_date = tab_date.replace(hour=hour_up)
else:
day = sql_traffic[0][1].day
tab_date = tab_date.replace(day=day + 1, hour=hour_up-24)
self.dt_tabel_date.setDateTime(tab_date)
else:
date = self.select_date or QDateTime.currentDateTime()
datetime = QDateTime(date, QTime.currentTime())
self.dt_date.setDateTime(datetime)
self.dt_tabel_date.setDateTime(datetime)
def inspection_path(self, dir_name,
sql_dir_name): # Находим путь работника
if not hasattr(self, 'path_work'):
query = 'SELECT `Values` FROM program_settings_path WHERE Name = "%s"' % sql_dir_name
info_sql = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(info_sql)):
QMessageBox.critical(self, "Ошибка sql", info_sql.msg,
QMessageBox.Ok)
return False
self.path_wor = info_sql[0][0]
if not path.isdir("%s/%s" %
(self.path_wor, dir_name.replace('"', "'"))):
try:
mkdir("%s/%s" %
(self.path_wor, dir_name.replace('"', "'")))
return "%s/%s" % (self.path_wor, dir_name.replace(
'"', "'"))
except:
QMessageBox.critical(
self, "Ошибка файлы",
"Нет доступа к корневому диалогу, файлы недоступны",
QMessageBox.Ok)
return False
else:
return "%s/%s" % (self.path_wor, dir_name.replace('"', "'"))
def ui_other(self):
try:
item_id = self.table_widget.selectedItems()[0].data(5)
except:
QMessageBox.critical(self, "Ошибка ", "Выделите операцию",
QMessageBox.Ok)
return False
query = """SELECT CONCAT(product_article.Article, ' ', product_article_size.Size, ' ', product_article_parametrs.Name) FROM operations
LEFT JOIN product_article_operation ON operations.Id = product_article_operation.Operation_Id
LEFT JOIN product_article_parametrs ON product_article_operation.Product_Article_Parametrs_Id = product_article_parametrs.Id
LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
WHERE operations.Id = %s"""
sql_art = my_sql.sql_select(query, (item_id, ))
if "mysql.connector.errors" in str(type(sql_art)):
QMessageBox.critical(self, "Ошибка sql получения зависимости",
sql_art.msg, QMessageBox.Ok)
return False
text = ""
count_art = 0
for art in sql_art:
if art[0] is None:
text = "Нет совпадений"
break
text += art[0] + "\n"
count_art += 1
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setWindowTitle("Совпадения")
msg.setText("Найдено %s совпадений" % count_art)
msg.setDetailedText(text)
msg.exec()
def set_table_info(self):
self.table_items = my_sql.sql_select(self.query_table_select)
if "mysql.connector.errors" in str(type(self.table_items)):
QMessageBox.critical(self, "Ошибка sql получение таблицы", self.table_items.msg, QMessageBox.Ok)
return False
self.table_widget.clearContents()
self.table_widget.setRowCount(0)
if not self.table_items:
return False
for table_typle in self.table_items:
self.table_widget.insertRow(self.table_widget.rowCount())
for column in range(1, len(table_typle)):
if isinstance(table_typle[column], Decimal):
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(table_typle[column]))
elif isinstance(table_typle[column], datetime.date):
text = table_typle[column].strftime("%d.%m.%Y")
else:
text = str(table_typle[column])
item = QTableWidgetItem(text)
if column == 8:
if table_typle[0] in self.other_value:
item.setCheckState(Qt.Checked)
else:
item.setCheckState(Qt.Unchecked)
item.setData(5, table_typle[0])
self.table_widget.setItem(self.table_widget.rowCount() - 1, column - 1, item)
def ui_calc_accessories_info(self):
# Расчет подробный фурнитуры
self.tw_accesories_info.clearContents()
self.tw_accesories_info.setRowCount(0)
filter_date = (self.de_accessories_from.date().toString(Qt.ISODate), self.de_accessories_to.date().toString(Qt.ISODate))
query = """SELECT SUM(transaction_records_accessories.Balance),
SUM(transaction_records_accessories.Balance * accessories_supplyposition.Price)
FROM transaction_records_accessories
LEFT JOIN accessories_balance ON transaction_records_accessories.Supply_Balance_Id = accessories_balance.Id
LEFT JOIN accessories_supplyposition ON accessories_balance.accessories_SupplyPositionId = accessories_supplyposition.Id
WHERE transaction_records_accessories.Pack_Accessories_Id IN (SELECT pack_accessories.Id
FROM pack_accessories LEFT JOIN pack ON pack_accessories.Pack_Id = pack.Id
LEFT JOIN cut ON pack.Cut_Id = cut.Id
WHERE cut.Date_Cut >= %s AND cut.Date_Cut <= %s)"""
sql_info = my_sql.sql_select(query, filter_date)[0]
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения расхода на пачки", sql_info.msg, QMessageBox.Ok)
return False
self.tw_accesories_info.insertRow(self.tw_accesories_info.rowCount())
item = QTableWidgetItem("Пачки")
self.tw_accesories_info.setItem(self.tw_accesories_info.rowCount() - 1, 0, item)
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(sql_info[0], 2)))
item = QTableWidgetItem(text)
self.tw_accesories_info.setItem(self.tw_accesories_info.rowCount() - 1, 1, item)
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(sql_info[1], 2)))
item = QTableWidgetItem(text)
self.tw_accesories_info.setItem(self.tw_accesories_info.rowCount() - 1, 2, item)
def set_table_info(self):
self.table_items = my_sql.sql_select(self.query_table_select)
if "mysql.connector.errors" in str(type(self.table_items)):
QMessageBox.critical(self, "Ошибка sql получение таблицы", self.table_items.msg, QMessageBox.Ok)
return False
if not self.table_items:
self.table_widget.clearContents()
self.table_widget.setRowCount(0)
return False
self.table_widget.clearContents()
self.table_widget.setRowCount(0)
for table_typle in self.table_items:
self.table_widget.insertRow(self.table_widget.rowCount())
if table_typle[3] > 0:
color = QBrush(QColor(150, 255, 161, 255))
else:
color = QBrush(QColor(252, 141, 141, 255))
for column in range(1, len(table_typle)):
if isinstance(table_typle[column], Decimal):
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(table_typle[column]))
elif isinstance(table_typle[column], datetime.date):
text = table_typle[column].strftime("%d.%m.%Y")
else:
text = str(table_typle[column])
item = QTableWidgetItem(text)
item.setData(5, table_typle[0])
item.setBackground(color)
self.table_widget.setItem(self.table_widget.rowCount() - 1, column - 1, item)
def ui_calc(self):
self.tableWidget.clearContents()
self.tableWidget.setRowCount(0)
query = """SELECT product_article_parametrs.Id, CONCAT(product_article.Article, '(', product_article_size.Size, ')[', product_article_parametrs.Name, ']'),
SUM(pack.Value_Pieces + pack.Value_Damage), SUM(pack.Value_Damage), (SUM(pack.Value_Damage) * 100 / SUM(pack.Value_Pieces + pack.Value_Damage))
FROM pack LEFT JOIN cut ON pack.Cut_Id = cut.Id
LEFT JOIN product_article_parametrs ON pack.Article_Parametr_Id = product_article_parametrs.Id
LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
WHERE pack.Value_Damage != 0 AND cut.Date_Cut >= %s AND cut.Date_Cut <= %s
GROUP BY product_article_parametrs.Id"""
sql_info = my_sql.sql_select(query,
(self.de_date_from.date().toPyDate(),
self.de_date_to.date().toPyDate()))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения брака",
sql_info.msg, QMessageBox.Ok)
return False
all_value, all_value_damage = 0, 0
for article in sql_info:
self.tableWidget.insertRow(self.tableWidget.rowCount())
item = QTableWidgetItem(article[1])
item.setData(5, article[0])
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, 0, item)
all_value += article[2]
item = QTableWidgetItem(str(article[2]))
item.setData(5, article[0])
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, 1, item)
all_value_damage += article[3]
item = QTableWidgetItem(str(article[3]))
item.setData(5, article[0])
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, 2, item)
item = QTableWidgetItem(str(round(article[4], 4)))
item.setData(5, article[0])
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, 3, item)
else:
self.tableWidget.insertRow(self.tableWidget.rowCount())
item = QTableWidgetItem(str(all_value))
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, 1, item)
item = QTableWidgetItem(str(all_value_damage))
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, 2, item)
avg = all_value_damage * 100 / all_value
item = QTableWidgetItem(str(round(avg, 4)))
self.tableWidget.setItem(self.tableWidget.rowCount() - 1, 3, item)
self.pb_calc_sum.setEnabled(True)
def start_settings(self):
query = """SELECT Id, Name FROM staff_position"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql при получении должностей", sql_info.msg, QMessageBox.Ok)
return False
for position in sql_info:
self.cb_position.addItem(position[1], position[0])
def beika_no_finished(self):
query = """SELECT COUNT(*) FROM beika WHERE Finished = 0"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
beika_txt = "error sql"
else:
beika_txt = "Не зкарыто бейки: " + str(sql_info[0][0])
beika = QLabel(beika_txt)
self.statusBar().addPermanentWidget(beika)
def beika_no_finished(self):
query = """SELECT COUNT(*) FROM beika WHERE Finished = 0"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
beika_txt = "error sql"
else:
beika_txt = "Не зкарыто бейки: " + str(sql_info[0][0])
beika = QLabel(beika_txt)
self.statusBar().addPermanentWidget(beika)
def set_start_info(self):
query = """SELECT Weight FROM rest_warehouse"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения склада", sql_info.msg, QMessageBox.Ok)
return False
self.le_weight.setText(str(sql_info[0][0]))
query = """SELECT Id, Balance, Date, Note, Cut_Id FROM transaction_records_rest ORDER BY Date DESC, Id DESC LIMIT 30"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения информации", sql_info.msg, QMessageBox.Ok)
return False
self.tw_info.clearContents()
self.tw_info.setRowCount(0)
for row, info in enumerate(sql_info):
self.tw_info.insertRow(row)
if info[1] >= 0:
color = QBrush(QColor(150, 255, 161, 255))
else:
color = QBrush(QColor(252, 141, 141, 255))
new_table_item = QTableWidgetItem(str(info[0]))
new_table_item.setData(-2, info[4])
new_table_item.setBackground(color)
self.tw_info.setItem(row, 0, new_table_item)
new_table_item = QTableWidgetItem(str(info[1]))
new_table_item.setData(-2, info[4])
new_table_item.setBackground(color)
self.tw_info.setItem(row, 1, new_table_item)
new_table_item = QTableWidgetItem(info[2].strftime("%d.%m.%Y"))
new_table_item.setData(-2, info[4])
new_table_item.setBackground(color)
self.tw_info.setItem(row, 2, new_table_item)
new_table_item = QTableWidgetItem(str(info[3]))
new_table_item.setData(-2, info[4])
new_table_item.setBackground(color)
self.tw_info.setItem(row, 3, new_table_item)
def ui_print(self):
query = """SELECT Last_Name, First_Name FROM staff_worker_info WHERE Id = %s"""
sql_info = my_sql.sql_select(query, (self.id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения имени работника", sql_info.msg, QMessageBox.Ok)
return False
head = "Часы работника " + sql_info[0][0] + " " + sql_info[0][1]
html = table_to_html.tab_html(self.tw_calc_traffic, table_head=head)
self.print_class = print_qt.PrintHtml(self, html)
def set_info(self):
query = 'SELECT Id, One_year, Many_year FROM program_settings_road'
sql_ret = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_ret)):
QMessageBox.critical(self, "Ошибка sql", sql_ret.msg, QMessageBox.Ok)
return False
self.row_id = sql_ret[0][0]
self.le_one_year.setText(str(sql_ret[0][1]))
self.le_many_year.setText(str(sql_ret[0][2]))
def ui_print(self):
query = """SELECT Last_Name, First_Name FROM staff_worker_info WHERE Id = %s"""
sql_info = my_sql.sql_select(query, (self.id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения имени работника",
sql_info.msg, QMessageBox.Ok)
return False
head = "Часы работника " + sql_info[0][0] + " " + sql_info[0][1]
html = table_to_html.tab_html(self.tw_calc_traffic, table_head=head)
self.print_class = print_qt.PrintHtml(self, html)
def set_start_settings(self):
if self.id:
query = """SELECT CONCAT(Last_Name, ' ', First_Name), staff_worker_kard.Card_Id FROM staff_worker_info
LEFT JOIN staff_worker_kard ON staff_worker_info.Id = staff_worker_kard.Worker_Id
WHERE Id = %s"""
sql_info = my_sql.sql_select(query, (self.id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql сохр. карты", sql_info.msg, QMessageBox.Ok)
return False
self.le_worker.setWhatsThis(str(self.id))
self.le_worker.setText(sql_info[0][0])
self.le_card.setText(sql_info[0][1])
def sql_set_list(self):
sql_result = my_sql.sql_select(self.sql_list)
if "mysql.connector.errors" in str(type(sql_result)):
QMessageBox.critical(self, "Ошибка sql вывода списка",
sql_result.msg, QMessageBox.Ok)
return False
else:
self.lw_list.clear()
for item in sql_result:
item_list = QListWidgetItem(item[1])
item_list.setData(3, item[0])
self.lw_list.addItem(item_list)
def ui_salary_p_m(self):
self.timer.start(900000)
query = """SELECT pay_reason.Name, pay_worker.Balance, pay_worker.Date_In_Pay, pay_worker.Note
FROM pay_worker LEFT JOIN pay_reason ON pay_worker.Reason_Id = pay_reason.Id
WHERE pay_worker.Worker_Id = %s AND pay_worker.Pay = 0"""
sql_info = my_sql.sql_select(query, (self.user["id"], ))
if "mysql.connector.errors" in str(type(sql_info)):
return False
self.tw_salary_p_m.clearContents()
self.tw_salary_p_m.setRowCount(0)
p_m_plus = 0
p_m_minus = 0
p_m_all = 0
for row, p_m in enumerate(sql_info):
if p_m[1] >= 0:
color = QBrush(QColor(246, 250, 127, 255))
p_m_plus += p_m[1]
else:
color = QBrush(QColor(245, 113, 113, 255))
p_m_minus += -p_m[1]
self.tw_salary_p_m.insertRow(row)
new_table_item = QTableWidgetItem(str(p_m[0]))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 0, new_table_item)
new_table_item = QTableWidgetItem(str(round(p_m[1], 2)))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 1, new_table_item)
new_table_item = QTableWidgetItem(str(p_m[2].strftime("%d.%m.%Y")))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 2, new_table_item)
new_table_item = QTableWidgetItem(str(p_m[3]))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 3, new_table_item)
p_m_all = p_m_plus - p_m_minus
self.tw_salary_p_m_all.setText(
re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(p_m_all, 2))))
self.tw_salary_p_m_minus.setText(
re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(p_m_minus, 2))))
self.tw_salary_p_m_plus.setText(
re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(p_m_plus, 2))))
self.sw_main.setCurrentIndex(7)
def set_sql_info(self):
query = """SELECT operations.Name, operations.Price, sewing_machine.Name, sewing_machine.Id, operations.Note, operations.Note2
FROM operations LEFT JOIN sewing_machine ON operations.Sewing_Machine_Id = sewing_machine.Id WHERE operations.Id = %s"""
sql_info = my_sql.sql_select(query, (self.id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение информации", sql_info.msg, QMessageBox.Ok)
return False
self.le_name.setText(sql_info[0][0])
self.le_price.setText(str(sql_info[0][1]))
self.le_machine.setText(sql_info[0][2])
self.le_machine.setWhatsThis(str(sql_info[0][3]))
self.pe_note.appendPlainText(sql_info[0][4])
self.le_note_2.setText(sql_info[0][5])
def set_start_settings(self):
if self.id:
query = """SELECT CONCAT(Last_Name, ' ', First_Name), staff_worker_kard.Card_Id FROM staff_worker_info
LEFT JOIN staff_worker_kard ON staff_worker_info.Id = staff_worker_kard.Worker_Id
WHERE Id = %s"""
sql_info = my_sql.sql_select(query, (self.id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql сохр. карты",
sql_info.msg, QMessageBox.Ok)
return False
self.le_worker.setWhatsThis(str(self.id))
self.le_worker.setText(sql_info[0][0])
self.le_card.setText(sql_info[0][1])
def set_sql_salary_date(self):
query = """(SELECT Date_Pay FROM pack_operation WHERE Pay = 1 GROUP BY Date_Pay ORDER BY Date_Pay DESC)
UNION
(SELECT Date_Pay FROM pay_worker WHERE Pay = 1 GROUP BY Date_Pay ORDER BY Date_Pay DESC)
ORDER BY Date_Pay DESC"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
raise RuntimeError("Не смог получыить даты закрытых месяцов")
for date in sql_info:
date_item = QListWidgetItem(date[0].strftime("%d.%m.%Y"))
date_item.setData(-1, date[0])
self.lw_date_salary.addItem(date_item)
def set_sql_salary_date(self):
query = """(SELECT Date_Pay FROM pack_operation WHERE Pay = 1 GROUP BY Date_Pay ORDER BY Date_Pay DESC)
UNION
(SELECT Date_Pay FROM pay_worker WHERE Pay = 1 GROUP BY Date_Pay ORDER BY Date_Pay DESC)
ORDER BY Date_Pay DESC"""
sql_info = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_info)):
raise RuntimeError("Не смог получыить даты закрытых месяцов")
for date in sql_info:
date_item = QListWidgetItem(date[0].strftime("%d.%m.%Y"))
date_item.setData(-1, date[0])
self.lw_date_salary.addItem(date_item)
def search_article(self):
barcode = int(self.le_number.text())
query = """SELECT COUNT(Barcode) FROM product_article_parametrs WHERE Barcode = %s"""
count_id = my_sql.sql_select(query, (barcode, ))
if "mysql.connector.errors" in str(type(count_id)):
QMessageBox.critical(self, "Ошибка sql поиска штрихкода",
barcode.msg, QMessageBox.Ok)
return False
self.art_list = ListArticle()
self.art_list.search_barcode(barcode)
self.art_list.setModal(True)
self.art_list.show()
def set_sql_salary_date(self):
query = """(SELECT Date_Pay FROM pack_operation WHERE Pay = 1 AND Worker_Id = %s GROUP BY Date_Pay ORDER BY Date_Pay DESC)
UNION
(SELECT Date_Pay FROM pay_worker WHERE Pay = 1 AND Worker_Id = %s GROUP BY Date_Pay ORDER BY Date_Pay DESC)
ORDER BY Date_Pay DESC LIMIT 3"""
sql_info = my_sql.sql_select(query, (self.work, self.work))
if "mysql.connector.errors" in str(type(sql_info)):
return False
for date in sql_info:
date_item = QListWidgetItem(date[0].strftime("%d.%m.%Y"))
date_item.setData(-1, date[0])
date_item.setTextAlignment(Qt.AlignHCenter)
self.lw_date_salary.addItem(date_item)
def set_info(self):
query = 'SELECT Name, program_settings_path.Values FROM program_settings_path'
sql_ret = my_sql.sql_select(query)
if "mysql.connector.errors" in str(type(sql_ret)):
QMessageBox.critical(self, "Ошибка sql", sql_ret.msg, QMessageBox.Ok)
return False
for path in sql_ret:
if path[0] == "Путь корень рабочие":
self.le_work_path.setText(path[1])
elif path[0] == "Путь корень клиенты":
self.le_clients_path.setText(path[1])
elif path[0] == "Путь корень бирки":
self.le_article_label_path.setText(path[1])
def set_sql_info(self):
query = """SELECT operations.Name, operations.Price, sewing_machine.Name, sewing_machine.Id, operations.Note, operations.Note2
FROM operations LEFT JOIN sewing_machine ON operations.Sewing_Machine_Id = sewing_machine.Id WHERE operations.Id = %s"""
sql_info = my_sql.sql_select(query, (self.id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение информации",
sql_info.msg, QMessageBox.Ok)
return False
self.le_name.setText(sql_info[0][0])
self.le_price.setText(str(sql_info[0][1]))
self.le_machine.setText(sql_info[0][2])
self.le_machine.setWhatsThis(str(sql_info[0][3]))
self.pe_note.appendPlainText(sql_info[0][4])
self.le_note_2.setText(sql_info[0][5])
def search_barcode(self, barcode):
query = """SELECT product_article_parametrs.Id, product_article.Article, product_article_size.Size, product_article_parametrs.Name, product_article_parametrs.Client_Name
FROM product_article_parametrs LEFT JOIN product_article_size ON product_article_parametrs.Product_Article_Size_Id = product_article_size.Id
LEFT JOIN product_article ON product_article_size.Article_Id = product_article.Id
WHERE Barcode = %s"""
article_sql = my_sql.sql_select(query, (barcode, ))
if "mysql.connector.errors" in str(type(article_sql)):
QMessageBox.critical(self,
"Ошибка sql поиска артикула по штрихкоду",
article_sql.msg, QMessageBox.Ok)
return False
if article_sql:
self.set_table_info(article_sql)
def set_sql_salary_date(self):
query = """(SELECT Date_Pay FROM pack_operation WHERE Pay = 1 AND Worker_Id = %s GROUP BY Date_Pay ORDER BY Date_Pay DESC)
UNION
(SELECT Date_Pay FROM pay_worker WHERE Pay = 1 AND Worker_Id = %s GROUP BY Date_Pay ORDER BY Date_Pay DESC)
ORDER BY Date_Pay DESC LIMIT 6"""
sql_info = my_sql.sql_select(query, (self.work, self.work))
if "mysql.connector.errors" in str(type(sql_info)):
return False
for date in sql_info:
date_item = QListWidgetItem(date[0].strftime("%d.%m.%Y"))
date_item.setData(-1, date[0])
date_item.setTextAlignment(Qt.AlignHCenter)
self.lw_date_salary.addItem(date_item)
def start_settings(self):
if self.id is None:
self.de_plus_date.setDate(QDate.currentDate())
self.de_minus_date.setDate(QDate.currentDate())
self.de_road_date.setDate(QDate.currentDate())
self.de_p_m_date.setDate(QDate.currentDate())
self.de_bye_date.setDate(QDate.currentDate())
else:
query = """SELECT work.Id, work.First_Name, work.Last_Name, pay_worker.Balance, pay_worker.Date_In_Pay, pay_reason.Id, pay_reason.Name, pay_worker.Note
FROM pay_worker
LEFT JOIN staff_worker_info AS work ON pay_worker.Worker_Id = work.Id
LEFT JOIN pay_reason ON pay_worker.Reason_Id = pay_reason.Id
WHERE pay_worker.Id = %s"""
sql_info = my_sql.sql_select(query, (self.id, ))
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получение операции", sql_info.msg, QMessageBox.Ok)
return False
if sql_info[0][3] > 0:
self.menu_text = "Доплата"
self.sw_main.setCurrentIndex(1)
self.le_work_plus.setWhatsThis(str(sql_info[0][0]))
self.le_work_plus.setText(str(sql_info[0][1]) + " " + str(sql_info[0][2]))
self.le_value_plus.setText(str(sql_info[0][3]))
self.de_plus_date.setDate(sql_info[0][4])
self.le_reason_plus.setWhatsThis(str(sql_info[0][5]))
self.le_reason_plus.setText(str(sql_info[0][6]))
self.le_note_plus.setText(str(sql_info[0][7]))
else:
self.menu_text = "Вычет"
self.sw_main.setCurrentIndex(2)
self.le_work_minus.setWhatsThis(str(sql_info[0][0]))
self.le_work_minus.setText(str(sql_info[0][1]) + " " + str(sql_info[0][2]))
self.le_value_minus.setText(str(-sql_info[0][3]))
self.de_minus_date.setDate(sql_info[0][4])
self.le_reason_minus.setWhatsThis(str(sql_info[0][5]))
self.le_reason_minus.setText(str(sql_info[0][6]))
self.le_note_minus.setText(str(sql_info[0][7]))
self.lw_menu.setEnabled(False)
def ui_salary_p_m(self):
self.timer.start(900000)
query = """SELECT pay_reason.Name, pay_worker.Balance, pay_worker.Date_In_Pay, pay_worker.Note
FROM pay_worker LEFT JOIN pay_reason ON pay_worker.Reason_Id = pay_reason.Id
WHERE pay_worker.Worker_Id = %s AND pay_worker.Pay = 0"""
sql_info = my_sql.sql_select(query, (self.user["id"],))
if "mysql.connector.errors" in str(type(sql_info)):
return False
self.tw_salary_p_m.clearContents()
self.tw_salary_p_m.setRowCount(0)
p_m_plus = 0
p_m_minus = 0
p_m_all = 0
for row, p_m in enumerate(sql_info):
if p_m[1] >= 0:
color = QBrush(QColor(246, 250, 127, 255))
p_m_plus += p_m[1]
else:
color = QBrush(QColor(245, 113, 113, 255))
p_m_minus += -p_m[1]
self.tw_salary_p_m.insertRow(row)
new_table_item = QTableWidgetItem(str(p_m[0]))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 0, new_table_item)
new_table_item = QTableWidgetItem(str(round(p_m[1], 2)))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 1, new_table_item)
new_table_item = QTableWidgetItem(str(p_m[2].strftime("%d.%m.%Y")))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 2, new_table_item)
new_table_item = QTableWidgetItem(str(p_m[3]))
new_table_item.setBackground(color)
self.tw_salary_p_m.setItem(row, 3, new_table_item)
p_m_all = p_m_plus - p_m_minus
self.tw_salary_p_m_all.setText(re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(p_m_all, 2))))
self.tw_salary_p_m_minus.setText(re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(p_m_minus, 2))))
self.tw_salary_p_m_plus.setText(re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(p_m_plus, 2))))
self.sw_main.setCurrentIndex(7)
def check_login(self):
query = """SELECT staff_worker_login.Worker_Info_Id
FROM staff_worker_login
WHERE staff_worker_login.Login = %s AND BINARY staff_worker_login.Password = %s"""
login = self.le_login.text()
password = self.le_password.text()
sql_result = my_sql.sql_select(query, (login, password))
if not sql_result:
QMessageBox.information(self, "Что то не так", "Не верный логин или пароль", QMessageBox.Ok)
else:
User().set_id(sql_result[0][0])
self.main.login_access()
self.close()
self.destroy()
def set_tree_info(self): # заполняем девево
self.tree = my_sql.sql_select(self.query_tree_select)
if "mysql.connector.errors" in str(type(self.tree)):
QMessageBox.critical(self, "Ошибка sql вывода дерева", self.tree.msg, QMessageBox.Ok)
return False
self.tree_widget.clear()
for item_tree in self.tree:
if item_tree[1] == 0:
add_item = QTreeWidgetItem((item_tree[2], ))
add_item.setData(0, 5, item_tree[0])
self.tree_widget.addTopLevelItem(add_item)
else:
for n in range(self.tree_widget.topLevelItemCount()):
item = self.tree_widget.topLevelItem(n)
self.search(item, item_tree)
def ui_calc_comparing(self):
# Расчет прочих затрат
self.tw_comparing.clearContents()
self.tw_comparing.setRowCount(0)
filter_date = (self.de_comparing_from.date().toString(Qt.ISODate), self.de_comparing_to.date().toString(Qt.ISODate))
query = """SELECT comparing_name.Name, SUM(comparing_supplyposition.Value), SUM(comparing_supplyposition.Value * comparing_supplyposition.Price)
FROM comparing_supplyposition LEFT JOIN material_supply ON comparing_supplyposition.Material_SupplyId = material_supply.Id
LEFT JOIN accessories_supply ON comparing_supplyposition.Accessories_SupplyId = accessories_supply.Id
LEFT JOIN comparing_name ON comparing_supplyposition.Comparing_NameId = comparing_name.Id
WHERE (material_supply.Data >= %s AND material_supply.Data <= %s) OR (accessories_supply.Data >= %s AND accessories_supply.Data <= %s)
GROUP BY comparing_name.Id"""
sql_info = my_sql.sql_select(query, filter_date*2)
if "mysql.connector.errors" in str(type(sql_info)):
QMessageBox.critical(self, "Ошибка sql получения прочих расходоа", sql_info.msg, QMessageBox.Ok)
return False
self.tw_comparing.insertRow(self.tw_comparing.rowCount())
all_value = 0
all_sum = 0
for i in sql_info:
self.tw_comparing.insertRow(self.tw_comparing.rowCount())
item = QTableWidgetItem(str(i[0]))
self.tw_comparing.setItem(self.tw_comparing.rowCount() - 1, 0, item)
all_value += i[1]
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(i[1], 2)))
item = QTableWidgetItem(text)
self.tw_comparing.setItem(self.tw_comparing.rowCount() - 1, 1, item)
all_sum += i[2]
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(i[2], 2)))
item = QTableWidgetItem(text)
self.tw_comparing.setItem(self.tw_comparing.rowCount() - 1, 2, item)
self.tw_comparing.insertRow(self.tw_comparing.rowCount())
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(all_value, 2)))
item = QTableWidgetItem(text)
self.tw_comparing.setItem(self.tw_comparing.rowCount() - 1, 1, item)
text = re.sub(r'(?<=\d)(?=(\d\d\d)+\b.)', ' ', str(round(all_sum, 2)))
item = QTableWidgetItem(text)
self.tw_comparing.setItem(self.tw_comparing.rowCount() - 1, 2, item)