def get_filtered_sales(form): db = get_db() search_date = form['date'] if search_date: search_date = Sale._format_date(search_date) data = (search_date, ) query = """ SELECT * FROM sale JOIN user on sale.user_id = user.id JOIN client on sale.client_id = client.id JOIN pay_method on sale.pay_method_id = pay_method.id WHERE date = ? """ sales = db.execute(query, data).fetchall() filtered_sale = True return sales, filtered_sale for key, value in form.items(): if value: data = (value, ) query = 'SELECT * FROM sale '\ 'JOIN user on sale.user_id = user.id '\ 'JOIN client on sale.client_id = client.id '\ 'JOIN pay_method on sale.pay_method_id = pay_method.id '\ f'WHERE sale.{key} = ?' sales = db.execute(query, data).fetchall() filtered_sale = True return sales, filtered_sale else: sales, filtered_sale = Sale.get_all_sales() return sales, filtered_sale
def get_all_users(): db = get_db() users = db.execute(""" SELECT * FROM user JOIN branch on user.branch_id = branch.id """).fetchall() return users
def delete_article(id): db = get_db() data = (id,) query = 'DELETE FROM article WHERE id=?' db.execute(query, data) db.commit() return
def delete_client(id): db = get_db() data = (id, ) query = 'DELETE FROM client WHERE id=?' db.execute(query, data) db.commit() return
def get_all_articles(): db = get_db() query = """ SELECT * FROM article JOIN category ON article.category_id = category.id """ articles = db.execute(query).fetchall() return articles
def get_article_object_by_sku(sku): db = get_db() data = (sku,) query = """ SELECT * FROM article WHERE SKU = ? """ article = db.execute(query, data).fetchone() return Article(article)
def delete_cart_item(id): db = get_db() data = (id, ) query = """ DELETE FROM cart WHERE id = ? """ db.execute(query, data) db.commit() return
def log_in_user(form): db = get_db() username = form['username'] password = form['password'] user = User._get_user_by_name(username) if User._is_valid_login(user, password): session.clear() session['user_id'] = user['id'] return
def _get_article_id(self): db = get_db() data = (self.name,) query = 'SELECT id FROM article WHERE name = ?' article_id = db.execute(query, data).fetchone() if article_id is not None: return article_id['id'] else: article_id = db.execute(f"SELECT id FROM article").fetchall() return article_id[-1]['id'] + 1
def create_client(self): db = get_db() data = (self.name, self.contact_phone, self.has_credit) query = """ INSERT INTO client (name, contact_phone, has_credit) VALUES (?, ?, ?) """ db.execute(query, data) db.commit() return
def get_category_by_id(id): db = get_db() data = (id, ) query = """ SELECT * FROM category WHERE id=? """ category = db.execute(query, data).fetchone() return category
def get_article_by_id(id): db = get_db() data = (id,) query = """ SELECT * FROM article JOIN category on article.category_id = category.id WHERE article.id = ? """ article = db.execute(query, data).fetchone() return article
def create_category(self): db = get_db() data = (self.category_name, ) query = """ INSERT INTO category (category_name) VALUES (?) """ db.execute(query, data) db.commit() return
def get_username_by_id(id): db = get_db() data = (id, ) query = """ SELECT username from user JOIN branch on user.branch_id = branch.id WHERE user.id=? """ user = db.execute(query, data).fetchone() return user['username']
def update_category(self, id): db = get_db() data = (self.category_name, id) query = """ UPDATE category SET category_name=? WHERE id=? """ db.execute(query, data) db.commit() return
def _create_credit_database(self, cartItems): db = get_db() tableQuery = self._get_items_database_query(cartItems) itemsQuery = self._get_items_query(cartItems) try: db.execute(tableQuery) except: db.execute(f'DROP TABLE credit_{self.id}_items') db.execute(tableQuery) db.execute(itemsQuery) db.commit() return
def create_user(self): db = get_db() data = (self.username, self.password, self.isSuper, self.canDiscount, self.branchId) query = """ INSERT INTO user (username, password, is_super, can_discount, branch_id) VALUES(?,?,?,?,?) """ db.execute(query, data) db.commit() return
def add_cart_item(id): article = Article.get_article_object_by_id(id) db = get_db() data = (article.name, article.price, article.sku, article.stock, article.on_branch_1, article.on_branch_2, article.is_regular) query = """ INSERT INTO cart (name, price, SKU, stock, on_branch_1, on_branch_2, is_regular) VALUES (?, ?, ?, ?, ?, ?, ?) """ db.execute(query, data) db.commit() return
def get_filtered_categories(form): db = get_db() for key, value in form.items(): if value: data = (value, ) query = 'SELECT * FROM category '\ f'WHERE {key}=?' categories = db.execute(query, data).fetchall() break else: categories = Category.get_all_categories() return categories
def get_filtered_clients(form): db = get_db() for key, value in form.items(): if value: data = (value, ) query = 'SELECT * FROM client '\ f'WHERE {key}=?' clients = db.execute(query, data).fetchall() break else: clients = Client.get_all_clients() return clients
def get_filtered_articles(form): db = get_db() for key,value in form.items(): if value: data = (value,) query = 'SELECT * FROM article '\ 'JOIN category ON article.category_id = category.id ' \ f'WHERE article.{key}=?' articles = db.execute(query, data) break else: articles = Article.get_all_articles() return articles
def update_client(self, id): db = get_db() data = (self.name, self.contact_phone, self.has_credit, id) query = """ UPDATE client SET name=?, contact_phone=?, has_credit=? WHERE id=? """ db.execute(query, data) db.commit() return
def get_filtered_users(form): db = get_db() for key, value in form.items(): if value: data = (value, ) query = 'SELECT * FROM user '\ 'JOIN branch on user.branch_id = branch.id '\ f'WHERE user.{key}=?' users = db.execute(query, data).fetchall() break else: users = User.get_all_users() return users
def create_article(self): db = get_db() data = (self.name, self.category_id, self.description, self.price, self.sku, self.stock, self.on_branch_1, self.on_branch_2, self.is_regular) query = """ INSERT INTO article (name, category_id, description, price, SKU, stock, on_branch_1, on_branch_2, is_regular) VALUES(?,?,?,?,?,?,?,?,?) """ db.execute(query, data) db.commit() return
def update_user(self, id): db = get_db() data = (self.username, self.password, self.isSuper, self.canDiscount, self.branchId, id) query = """ UPDATE user SET username=?, password=?, is_super=?, can_discount=?, branch_id=? WHERE id=? """ db.execute(query, data) db.commit() return
def update_cart_items_stock(self, branchId): db = get_db() ticket = self._get_sale_ticket() for sku, quantity in ticket.items(): cartItem = Article.get_article_by_sku(sku) stock = self.get_cartItem_stock(cartItem) stock_on_branch = self.get_cartItem_stock_on_branch( cartItem, branchId) new_stock = stock - quantity new_stock_on_branch = stock_on_branch - quantity data = (new_stock_on_branch, new_stock, sku) query = 'UPDATE article ' \ f'SET on_branch_{branchId + 1}=?, '\ f'stock =? '\ 'WHERE SKU=?' db.execute(query, data) db.commit() return
def update_article(self, id): db = get_db() data = (self.name, self.category_id, self.price, self.sku, self.stock, self.on_branch_1, self.on_branch_2, self.is_regular, id) query = """ UPDATE article SET name=?, category_id=?, price=?, SKU=?, stock=?, on_branch_1=?, on_branch_2=?, is_regular=? WHERE id=? """ db.execute(query, data) db.commit() return
def create_sale(self, cartItems, branchId): db = get_db() if self.operationType == 'credit': self._create_credit_database(cartItems) data = (self.userId, branchId, self.clientId, self.total, self.payMethodId, self.recivedCash, self.date, self.creditTime) query = """ INSERT INTO credit(user_id, branch_id, client_id, total, pay_method_id, payed, date, credit_time ) VALUES(?, ?, ?, ?, ?, ?, ?, ?) """ else: data = (self.userId, branchId, self.clientId, self.total, self.payMethodId, self.date) query = """ INSERT INTO sale(user_id, branch_id, client_id, total, pay_method_id, date) VALUES(?, ?, ?, ?, ?, ? ) """ db.execute(query, data) db.commit() return
def clear_cart(self): db = get_db() query = 'DELETE FROM cart' db.execute(query) db.commit() return
def _get_all_cart_items(self): db = get_db() query = 'SELECT * from cart' cart_items = db.execute(query).fetchall() return cart_items