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
