def saveDish(self, val):
db = localDb_Class()
#query = "INSERT INTO dish"
#db.exec_query(query)
section = db.select_val_by_col("section", "name", "\'%s\'" % val["section"])["rows"][0]["id"]
db.insert_val("dish", (val["name"], section, val["mass"]), iid=val["id"])
db.close_db()
def getSections(self):
"""Берет список разделов"""
db = localDb_Class()
query = "SELECT DISTINCT s.id, s.name FROM section as s, dish as d WHERE d.section=s.id"
vals = db.exec_query(query)
db.close_db()
return vals
def save_sale(self,row):
"""Записывает данные о продажах в базу"""
db = localDb_Class()
db.insert_val('sale',(row['menu_id'],row['shift'],row['worker'],row['portions']))
balance = db.select_val_by_id('menu', row['menu_id'])['rows'][0]['balance']
db.update_val_by_id_name('menu', row['menu_id'], 'balance', balance-row['portions'])
db.close_db()
def checkProduct(self, row, cell, prow, pcell):
if pcell==0:
try:
db = localDb_Class()
query = "SELECT c.percent FROM coefficient as c, product as p WHERE p.id = c.product AND p.name='%s'" % self.ui.consumptionTableWidget.cellWidget(row, 0).currentText()
query += " AND c.period_start<='{0}' AND c.period_end>='{0}' ".format(datetime.now().strftime("%d-%m"))
try:
percent = db.exec_query(query)['rows'][0]['percent']
except:
percent = False
#print int(percent['rows'][0]['percent'])
#TODO ЧОЗАНАХ!?!???!
if (not prow in self.percent) and percent:
#Проценты есть, а переменной нет -- блокируем ячейку и создаем переменную
item = self.ui.consumptionTableWidget.item(prow, 1)
item.setFlags(QtCore.Qt.NoItemFlags)
self.percent[prow] = percent
self.calc_brutto(prow)
elif (prow in self.percent) and percent:
#Проценты есть и переменная есть -- меняем переменную
self.percent[prow] = percent
elif (not percent) and (prow in self.percent):
#Процентов нет, а переменная есть -- разблокируем ячейку и удаляем переменную
item = self.ui.consumptionTableWidget.item(prow, 1)
item.setFlags(QtCore.Qt.ItemIsEnabled|QtCore.Qt.ItemIsEditable|QtCore.Qt.ItemIsSelectable)
del self.percent[prow]
db.close_db()
except:
None
elif pcell==2:
#TODO проверяем если есть % то высчитываем
self.calc_brutto(prow)
def delRow(self):
"""Ну тут удаляет"""
db = localDb_Class()
db.open_db()
answer = db.del_row_by_id(self.table,self.ui.toolBox.currentWidget().ui.tableWidget.item(0, 1).text())
self.ui.toolBox.removeItem(self.ui.toolBox.currentIndex())
db.close_db()
def getSections(self):
"""Берет список разделов Menu_Class дубль"""
db = localDb_Class()
query = "SELECT DISTINCT s.id, s.name FROM section as s"
vals = db.exec_query(query)['rows']
db.close_db()
return vals
def save_for_sale(self,row):
"""Записывает данные о продажах в базу"""
db = localDb_Class()
income = db.select_val_by_id('income', row['menu_id'])['rows'][0]
balance = int(income['rest'])-int(income['mass'])*row['portions']
db.update_val_by_id_name('income', row['menu_id'], 'rest', balance)
db.close_db()
def select_brutto(self, pid):
db = localDb_Class()
data = (pid, datetime.now().strftime("%d-%m"))
query = "SELECT c.percent FROM coefficient as c, product as p WHERE p.id = c.product AND p.id={0} AND period_start<={1} AND period_finish>={1}".format(data)
#print query
db.exec_query(query)
db.close_db()
def getColNames(self):
"""Было гдето знач вынести, берет колонок имена"""
db = localDb_Class()
db.open_db()
answer = [tuple[1] for tuple in db.select_col_names(self.table)['rows']]
db.close_db()
return answer
def export_base(self):
db = localDb_Class()
rootXML = etree.Element("root", base="%s" % self.base, start=self.start, finish=self.finish)
tables = db.exec_query("SELECT name FROM sqlite_master WHERE type='table' AND name <> 'sqlite_sequence'")[
"rows"
]
for table in tables:
try:
tableXML = etree.SubElement(rootXML, "%s" % table[0])
try:
query = db.exec_query(
"SELECT * FROM {} WHERE date>='{}' AND date<='{}' AND base='{}'".format(
table[0], self.start, self.finish, self.base
)
)
except:
query = db.exec_query(
"SELECT * FROM {} WHERE date>='{}' AND date<='{}'".format(table[0], self.start, self.finish)
)
rows, keys = query["rows"], query["keys"]
for row in rows:
rowXML = etree.SubElement(tableXML, "row", value="%s" % row["id"])
for key in keys:
rowXML.append(etree.Element("data", field="%s" % key, value="%s" % row["%s" % key]))
except:
None
db.close_db()
with open("export/base_%s.xml" % self.shift.base, "w") as f:
f.write(etree.tostring(rootXML, pretty_print=True, encoding="UTF-8"))
def select_menu(self,shift):
"""Выбор строк меню"""
db = localDb_Class()
query = "SELECT m.id, d.name, m.price FROM menu as m,dish as d,shift as s WHERE "
query += "s.shift=m.shift AND d.id=m.id AND s.shift=%s" % shift
rows = db.exec_query(query)
db.close_db()
return rows
def select_from_goods(self,shift):
"""Выбираем сегодняшнее меню из базы"""
db = localDb_Class()
query = "SELECT 's' || i.id, i.name, i.rest / i.mass as rest, i.price FROM product as p, income as i WHERE "
query += "p.for_sale AND p.id=i.product AND i.product=p.id AND i.rest>0"
rows = db.exec_query(query)
db.close_db()
return rows
def select_from_old_menu(self,shift):
"""Выбираем сегодняшнее меню из базы"""
db = localDb_Class()
query = "SELECT m.id, '[С] ' || d.name, m.balance, m.price FROM menu as m,dish as d,shift as s WHERE "
query += "s.shift=m.shift AND d.id=m.dish AND s.shift<'%s' AND m.balance>0" % shift
rows = db.exec_query(query)
db.close_db()
return rows
def currentShift(self,day,meals):
"""текущая смена, разница между первой сменой и текущей"""
db = localDb_Class()
first_shift = db.get_first_shift()
db.close_db()
daydiff = abs(datetime.strptime(day,"%Y-%m-%d") - datetime.strptime(first_shift['actual_date'].encode(),"%Y-%m-%d"))
shiftdiff = daydiff.days*3 + (meals - first_shift['period'])
return shiftdiff+1
def getProductRest(self):
"""Выбираем остаток продуктов на складе
ID прихода, ID продукта, остаток в граммах"""
db = localDb_Class()
query = "SELECT p.id, SUM(i.rest) as sum FROM income AS i, product AS p WHERE p.id=i.product AND i.rest>0 AND i.active AND p.active GROUP BY p.id"
rows = {pid:val for pid,val in db.exec_query(query)['rows']}
db.close_db()
return rows
def save_data(self, table, val):
"""Записывает """
db = localDb_Class()
query = "INSERT INTO %s " % table
query += " (%s) " % ",".join([ "%s" % field for field in val])
query += " VALUES (%s) " % ",".join([ "'%s'" % val[field] for field in val])
db.exec_query(query)
db.close_db()
def get_data(self, vals):
"""Выбирает из отчета все необходимые переменные, формирует запрос по выборке этих переменных за определенный период"""
self.vals = vals
self.parse_file(vals['type'])
db = localDb_Class()
self.report_data = db.exec_query(self.report_param['body_query'])
db.close_db()
self.form_report()
def select_incomes(self):
"""Выбирает все необходимые значения из базы"""
db = localDb_Class()
query = "SELECT i.nomenculature, i.name, m.name, i.count, i.rest, i.price, p.for_sale, i.date FROM income as i, measure as m, product as p WHERE "
query += "i.active<>0 AND m.code = i.measure AND p.id = i.product"
incom = db.exec_query(query)['rows']
db.close_db()
return incom
def select_consumption(self):
"""Формирует цену на продукт на порцию блюда"""
db = localDb_Class()
query = "SELECT p.id as product, c.brutto "# i.price*i.coefficient*c.brutto/i.mass as price "
query += "FROM consumption as c, product as p, income as i WHERE "
query += "c.product = p.id AND i.product=p.id AND c.dish=" + "\'%s\'" % self.id
rows = db.exec_query(query)
db.close_db()
return rows
def getParamsFromWindow(self):
name = self.ui.nameEdit.text()
card = "{:0>16}".format(self.ui.cardEdit.text())
employee = "{:0>16}".format(self.ui.employeeEdit.text())
db = localDb_Class()
company = db.select_val_by_col("company", "name", "'%s'" % self.ui.comboBox.currentText())["rows"][0][0]
db.close_db()
param = (company, card, name, employee)
return param
def getProductPrice(self,prid):
db = localDb_Class()
query = "SELECT i.rest/{0} as count, i.price*{0}/i.mass as price, i.id as id ".format(self.consumption[prid])
query += "FROM income as i WHERE "
query += "i.product=" + "\'%s\'" % prid
query += "ORDER BY date"
rows = db.exec_query(query)
db.close_db()
return round(rows['rows'][0]['price'],2)
def addShift(self):
"""Выбираем из базы по номеру смены, если пусто, то записываем"""
db = localDb_Class()
if not db.select_val_by_col('shift','shift',"%s" % self.shift)['rows']:
db.insert_val('shift',(self.shift,self.today,self.meals,self.cashier))
db.close_db()
f = open("temp.data","w")
f.write("%s" % self.shift)
f.close()
def saveRow(self):
"""Нечто хитрое, а потом записывает"""
db = localDb_Class()
db.open_db()
strg = [self.ui.toolBox.currentWidget().ui.tableWidget.item(i, 1).text() for i in range(self.ui.toolBox.currentWidget().ui.tableWidget.rowCount())]
print strg[1:-2]
answer = db.insert_val(self.table,strg[1:-2])
db.close_db()
def saveRow(self, vals, dish):
db = localDb_Class()
#query = "INSERT INTO "
#db.exec_query(query)
dish = db.select_val_by_col("dish", "name", "\'%s\'" % dish["name"])["rows"][0]["id"]
for val in vals:
product = db.select_val_by_col("product", "name", "\'%s\'" % val["name"])["rows"][0]["id"]
db.insert_val("consumption", (dish, product, val["brutto"], val["netto"]))
db.close_db()
def check_refs(self,id_to_del):
"""Проверяет есть ли в базе ссылки на этот"""
db = localDb_Class()
answer = db.select_val_by_col('calculate','income',"%s" % id_to_del)
db.close_db()
try:
return answer['rows'][0]
except:
return False
def check_value(self,table,val,col = 'id'):
"""Сравнивает на наличие подобной записи"""
try:
self.save_data(table,val)
except:
db = localDb_Class()
answer = db.select_val_by_col(table,col,val[col])
db.close_db()
self.save_to_compare(answer['rows'],val)
def getProducts(self):
"""Берет список разделов Menu_Class дубль"""
db = localDb_Class()
query = "SELECT DISTINCT p.id, p.name FROM product as p WHERE p.active"
#Временно заменим запрос для заполнения рецептур
#query = "SELECT DISTINCT p.id, p.name FROM product as p, income as i WHERE p.id=i.product AND i.count>0 AND i.active=1 AND p.active=1"
vals = db.exec_query(query)['rows']
db.close_db()
return vals
def getSumByWorker(self, worker):
db = localDb_Class()
query = (
"SELECT SUM(m.price*s.number) AS sum FROM worker AS w, menu AS m, sale AS s WHERE s.menu=m.id AND s.worker='%s'"
% worker
)
result = db.exec_query(query)["rows"][0]["sum"]
db.close_db()
return result
def selectVals(self):
"""Берет данные из базы, вставляет построчно в таблицу на листе"""
db = localDb_Class()
db.open_db()
answer = db.select_all_val(self.table)
self.rows = answer["rows"]
self.keys = answer["keys"]
for i in range(len(self.rows)):
self.insertValsIntoPages(self.rows[i],self.keys)
db.close_db()
def saveMenu(self):
"""Сохраняет меню"""
db = localDb_Class()
#try:
for did in self.menuWidget:
self.save_calculate(did,db)
self.renew()
#except:
#None
db.close_db()
