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()