def pd_index():
db = get_db()
form = AddProductsForm()
if request.method == 'GET':
products = db.execute(
'SELECT id, prod_nbr, prod_line, size, color, price FROM products'
).fetchall()
return render_template('products.html', products = products, form = form)
else:
if form.validate_on_submit():
db = get_db()
prod_nbr = form.prod_nbr.data
prod_line = form.prod_line.data
size = form.size.data
color = form.color.data
price = form.price.data
db.execute(
'INSERT INTO products (prod_nbr, prod_line, size, color, price)'
' VALUES (?, ?, ?, ?, ?)',
(prod_nbr, prod_line, size, color, price)
)
db.commit()
flash('You have add a new product!')
else:
flash(form.errors)
return redirect(url_for('products.pd_index'))
def update_product(id):
product = get_product(id)
form = AddProductsForm()
if request.method == 'POST':
if form.validate_on_submit():
prod_nbr = form.prod_nbr.data
prod_line = form.prod_line.data
size = form.size.data
color = form.color.data
price = form.price.data
db = get_db()
db.execute(
'UPDATE products SET prod_nbr = ?, prod_line = ?, size = ?, color =?, price =? WHERE id = ?',
(prod_nbr, prod_line, size, color, price, id)
)
db.commit()
flash('You have modify a product')
return redirect(url_for('products.pd_index'))
elif request.method == 'GET':
form.prod_nbr.data = product['prod_nbr']
form.prod_line.data = product['prod_line']
form.size.data = product['size']
form.color.data = product['color']
form.price.data = product['price']
return render_template('update.html', form=form)
def delete(id):
order = get_order(id)
db = get_db()
db.execute('DELETE FROM orders WHERE id = ?', (id, ))
db.commit()
flash('You have delete an order')
return redirect(url_for('orders.index'))
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM users WHERE id = ?',
(user_id, )).fetchone()
def get_product(id):
product = get_db().execute(
'SELECT id, prod_nbr, prod_line, size, color, price FROM products'
' WHERE id = ?',
(id,)
).fetchone()
if product is None:
abort(404, "Product id {0} doesn't exist.".format(id))
return product
def get_order(id):
order = get_db().execute(
'SELECT id, ord_nbr, ord_date, ord_qty, prod_id FROM orders'
' WHERE id = ?', (id, )).fetchone()
if order is None:
abort(404, "Order id {0} doesn't exist.".format(id))
return order
def search():
if not g.search_form.validate():
return redirect(url_for('orders.index'))
db = get_db()
word = g.search_form.q.data
products = db.execute('''SELECT rowid, prod_nbr, prod_line, size, color, price
FROM products_index
WHERE Products_index
MATCH ?''', (word,)
).fetchall()
return render_template('search.html', products = products)
def index():
db = get_db()
form = AddOrdersForm()
if request.method == 'GET':
orders = db.execute(
'SELECT id, ord_nbr, ord_date, ord_qty, prod_id FROM orders ORDER BY ord_date DESC'
).fetchall()
return render_template('orders.html', orders=orders, form=form)
else:
if form.validate_on_submit():
db = get_db()
ord_nbr = form.ord_nbr.data
ord_date = form.ord_date.data
ord_qty = form.ord_qty.data
prod_id = form.prod_id.data
db.execute(
'INSERT INTO orders (ord_nbr, ord_date, ord_qty, prod_id)'
' VALUES (?, ?, ?, ?)', (ord_nbr, ord_date, ord_qty, prod_id))
db.commit()
flash('You have add a new order!')
else:
flash(form.errors)
return redirect(url_for('orders.index'))
def create_view():
db = get_db()
db.executescript('''
DROP VIEW IF EXISTS v_ord;
CREATE VIEW IF NOT EXISTS v_ord AS
SELECT P.prod_nbr, P.prod_line, P.color, P.size, P.price,
O.ord_nbr, O.ord_qty,
P.price * ifnull( O.ord_qty, 0) AS ord_amt,
D.date, D.month,
CASE D.day WHEN '0' THEN 'SUNDAY'
WHEN '1' THEN 'MONDAY'
WHEN '2' THEN 'TUESDAY'
WHEN '3' THEN 'WEDNESDAY'
WHEN '4' THEN 'THURSDAY'
WHEN '5' THEN 'FRIDAY'
WHEN '6' THEN 'SATURDAY'
ELSE 'OTHER' END AS day_name
FROM products P
INNER JOIN orders O ON P.id = O.prod_id
INNER JOIN dates D ON O.ord_date = D.date;
''')
db.commit()
def report():
db = get_db()
create_view()
#labels = ["SUNDAY", "MONDAY", "TUESDAY","WEDNESDAY", "THURSDAY","FRIDAY","SATURDAY"]
#labels1 = ['JUL', 'AUG', 'SEP']
ord_qty_rows = db.execute(
'SELECT sum(ord_qty) as total, day_name FROM v_ord group by day_name order by total desc;'
).fetchall()
values = convert_row_list(ord_qty_rows)
labels = convert_row_list_key(ord_qty_rows)
ord_amt_rows = db.execute(
'SELECT sum(ord_amt), month FROM v_ord group by month order by month asc;'
).fetchall()
values1 = convert_row_list(ord_amt_rows)
labels1 = convert_row_list_key(ord_amt_rows)
ord_amt_pro_nbr_rows = db.execute(
'SELECT sum(ord_amt), prod_nbr FROM v_ord group by prod_nbr order by sum(ord_amt) desc;'
).fetchall()
values2 = convert_row_list(ord_amt_pro_nbr_rows)
labels2 = convert_row_list_key(ord_amt_pro_nbr_rows)
ord_qty_color_rows = db.execute(
'SELECT sum(ord_qty), color FROM v_ord group by color;').fetchall()
values3 = convert_row_list(ord_qty_color_rows)
labels3 = convert_row_list_key(ord_qty_color_rows)
ord_qty_size_rows = db.execute(
'SELECT sum(ord_qty), size FROM v_ord group by size').fetchall()
values4 = convert_row_list(ord_qty_size_rows)
labels4 = convert_row_list_key(ord_qty_size_rows)
return render_template('report.html',
values=values,
labels=labels,
labels1=labels1,
labels2=labels2,
values1=values1,
values2=values2,
labels3=labels3,
values3=values3,
labels4=labels4,
values4=values4)
def update(id):
order = get_order(id)
form = AddOrdersForm()
#form.prod_id.choices = v_prod_id()
if request.method == 'POST':
if form.validate_on_submit():
ord_nbr = form.ord_nbr.data
ord_date = form.ord_date.data
ord_qty = form.ord_qty.data
prod_id = form.prod_id.data
db = get_db()
db.execute(
'UPDATE orders SET ord_nbr = ?, ord_date = ?, ord_qty = ?, prod_id =? WHERE id = ?',
(ord_nbr, ord_date, ord_qty, prod_id, id))
db.commit()
flash('You have modify an order')
return redirect(url_for('orders.index'))
elif request.method == 'GET':
form.ord_nbr.data = order['ord_nbr']
form.ord_date.data = order['ord_date']
form.ord_qty.data = order['ord_qty']
form.prod_id.data = order['prod_id']
return render_template('update.html', form=form)
def login():
form = LoginForm()
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
user = db.execute('SELECT * FROM users WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not user['password'] == password:
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('orders.index'))
flash(error)
return render_template('login.html', form=form)
def dashboard():
create_date_table()
db = get_db()
best_sellers = db.execute(''' SELECT P.prod_nbr,
price * ord_qty AS Total_Sales
FROM products P
INNER JOIN orders O ON P.id = O.prod_id
GROUP BY P.id
ORDER BY Total_Sales DESC
Limit 1
;''').fetchone()
total_amount = db.execute('''SELECT SUM (Total_Sales) AS total
FROM (
SELECT price * ord_qty AS Total_Sales
FROM products P
INNER JOIN orders O ON P.id = O.prod_id
);''').fetchone()
total_orders = db.execute(''' SELECT
count(ord_nbr) AS Order_Count,
sum(ord_qty) AS Order_Quauntity
FROM products P
INNER JOIN orders O ON P.id = O.prod_id;
''').fetchone()
sales_record = db.execute('''SELECT date, sum(ord_qty) AS sum_ord_qty
FROM products P
INNER JOIN orders O ON P.id = O.prod_id
INNER JOIN dates D ON O.ord_date = D.date
GROUP BY date
order by sum_ord_qty desc
limit 1;
''').fetchone()
return render_template('dashboard.html',
sales_record=sales_record,
best_sellers=best_sellers,
total_orders=total_orders,
total_amount=total_amount)
