def orhistory(bid):
query = "SELECT order_id, order_quantity, order_date, order_price, delivery_status, payment_method, delivery_address, med_id, med_role, order_total, buyer_id\
FROM orders\
WHERE buyer_id= %s"
connection = connect()
cur = connection.cursor()
try:
params = (str(bid), )
cur.execute(query, params)
items = cur.fetchall()
print(items)
ite, subtotal, len_items = cart_items()
# image = ("static/images/d1.jpg","static/images/d7.jpg","static/images/d2.jpg","static/images/d21.jpg","static/images/d23.jpg","static/images/img16.jpg","static/images/img17.jpg","static/images/img21.jpg","static/images/img15.jpg")
except mysql.connector.Error as err:
print(err)
return []
finally:
cur.close()
connection.close()
return render_template("orderhistory.html",
items=items,
subtotal=subtotal,
len_items=len_items,
buid=bid)
def cart_items():
query = "SELECT item_id, med_price, med_quantity, med_name,\
medicine.med_id, med_brandname, med_purpose,\
med_role, dosage_form, cart_items.item_quantity FROM medicine\
INNER JOIN cart_items ON medicine.med_id = cart_items.med_id and medicine.med_role = cart_items.med_rol\
WHERE buyer_user = %s"
connection = connect()
cur = connection.cursor()
try:
params = (session['user'], )
cur.execute(query, params)
items = cur.fetchall()
print(items)
except mysql.connector.Error as err:
return [], 0, 0
finally:
cur.close()
connection.close()
subtotal = 0
couponcode = 0
for item in items:
subtotal += item[1] * item[9]
items_len = len(items)
return items, subtotal, items_len
def supupdate():
name = request.form.get('mname', None)
rol = request.form.get('role', None)
pric = request.form.get('price', None)
quantit = request.form.get('quantity', None)
q = "SELECT med_brandname FROM medicine WHERE med_name=%s and med_role=%s"
connection = connect()
cur = connection.cursor()
params = (
name,
rol,
)
cur.execute(q, params)
it = cur.fetchone()
try:
if it:
q1 = "SELECT med_supplier FROM medicine WHERE med_name = %s and med_role=%s"
params = (
name,
rol,
)
cur.execute(q1, params)
it1 = cur.fetchone()
if it1[0] == session['role']:
query = "UPDATE medicine SET med_quantity = %s WHERE med_name = %s and med_role = %s and med_supplier = %s"
query1 = "UPDATE medicine SET med_price = %s WHERE med_name = %s and med_role = %s and med_supplier = %s"
params = (
str(quantit),
name,
rol,
session['role'],
)
params1 = (
str(pric),
name,
rol,
session['role'],
)
cur.execute(query, params)
cur.execute(query1, params1)
# flash("Medicine updated successfully!!", category="success")
connection.commit()
msg = "Medicine updated in the database!!"
category = "success"
return msg, category
else:
msg = "Medicine is not supplied by you!!"
category = "warning"
return msg, category
else:
msg = "Medicine does not exist!!"
category = "danger"
return msg, category
except mysql.connector.Error as err:
print(err)
return []
finally:
cur.close()
connection.close()
def login():
# m=message
if request.method == 'POST':
email = request.form['email'] # access the data inside
password = request.form['pass']
query = "SELECT * FROM login WHERE user_email = " + "'" + email + "'"
# query1 = "SELECT user_category FROM login WHERE user_email=%s"
connection = connect()
cur = connection.cursor()
try:
cur.execute(query)
user = cur.fetchone()
connection.commit()
if user:
if bcrypt.check_password_hash(user[2], password):
session['email'] = email
session['user'] = user[0]
session['type'] = user[6]
session['person'] = user[3]
session['cdis'] = 0.00
session['pdis'] = 0.00
# if session['user'] == 'customew'
if user[6] == "supplier":
session['role'] = user[3]
flash("Login Successfull!!", 'success')
return redirect(url_for('supplier'))
else:
flash("Login Successfull!!", 'success')
return redirect(url_for('mhome'))
else:
flash("Password incorrect!!", 'danger')
return redirect(url_for('login'))
else:
flash("Email id does not exist!!", 'danger')
return redirect(url_for('login'))
except mysql.connector.Error as e:
print(e)
return None
finally:
cur.close()
connection.close()
# user = Login.query.filter_by(user_email=email).first()
# if user and bcrypt.check_password(password=password):
# connection.commit()
# cur.close()
# # message = 'Logged In Successfully!!'
# return redirect(url_for('customer'))
# else:
# return redirect(url_for('login'))
# # message = 'Wrong email or password'
return render_template("abc.html", title='Login')
def delete_item(item_id):
query = "DELETE FROM cart_items WHERE item_id = %s"
connection = connect()
cur = connection.cursor()
try:
params = (item_id, )
cur.execute(query, params)
connection.commit()
except mysql.connector.Error as err:
print(err)
finally:
cur.close()
connection.close()
def update_item(item_id, quantity, med_id, med_role):
query = "SELECT med_quantity FROM medicine WHERE med_id = %s and med_role = %s"
connection = connect()
cur = connection.cursor()
try:
params = (
med_id,
med_role,
)
print(params)
cur.execute(query, params)
med_quantity = cur.fetchone()
if not med_quantity:
print("wrong id")
else:
if int(quantity) > int(med_quantity[0]):
msg = "Could not update quantity. Total " + med_quantity[
0] + " sheets available!!"
cat = "danger"
return msg, cat
except mysql.connector.Error as err:
print(err)
finally:
cur.close()
connection.close()
query = "UPDATE cart_items SET item_quantity = %s WHERE item_id = %s and med_rol = %s"
try:
cur = connection.cursor()
params = (
int(quantity),
item_id,
med_role,
)
cur.execute(query, params)
connection.commit()
except mysql.connector.Error as err:
print(err)
finally:
cur.close()
connection.close()
msg = "Quantity Updated successfully!!"
cat = "success"
return msg, cat
def supadd():
mid = request.form.get('medid', None)
nam = request.form.get('mname', None)
bran = request.form.get('bname', None)
purpos = request.form.get('purpose', None)
typ = request.form.get('role', None)
expir = request.form.get('expiry', None)
dosag = request.form.get('dform', None)
pric = request.form.get('price', None)
quantit = request.form.get('quantity', None)
query = "INSERT into medicine(med_id, med_name, med_brandname, med_purpose,\
med_expiry, dosage_form,\
med_price, med_role, med_quantity,\
med_supplier)\
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
connection = connect()
cur = connection.cursor()
try:
params = (
str(mid),
nam,
bran,
purpos,
str(expir),
dosag,
str(pric),
typ,
str(quantit),
session['role'],
)
print(params)
print(query)
cur.execute(query, params)
# flash("Medicine added to the database!!", category="success")
connection.commit()
except mysql.connector.Error as err:
print(err)
return False
finally:
cur.close()
connection.close()
return True
def add_item(med_id, quantity, med_role):
query = "SELECT med_quantity FROM medicine WHERE med_id = %s and med_role = %s"
connection = connect()
cur = connection.cursor()
try:
params = (
med_id,
med_role,
)
cur.execute(query, params)
med_quantity = cur.fetchone()
if not med_quantity:
print("wrong id")
elif int(quantity) > int(med_quantity[0]):
quantity = int(med_quantity[0])
flash("Available Quantity is " + str(med_quantity[0]) +
" sheets only !! Minimum quantity should be 200 sheets",
category="danger")
else:
buyer_user = session['user']
query = "INSERT into cart_items \
(item_quantity, buyer_user, med_id, med_rol)\
VALUES (%s, %s, %s, %s)"
try:
params = (
quantity,
buyer_user,
med_id,
med_role,
)
cur.execute(query, params)
flash("Medicine added to the cart!!", "success")
connection.commit()
except mysql.connector.Error as err:
print(err)
except mysql.connector.Error as err:
print(err)
return
finally:
cur.close()
connection.close()
def product_detail(pur):
query = "SELECT med_name,med_brandname,med_purpose,med_price,med_role,dosage_form,med_id\
FROM medicine\
WHERE med_purpose = %s"
try:
connection = connect()
cur = connection.cursor()
params = (pur, )
cur.execute(query, params)
items = cur.fetchall()
ite, subtotal, items_len = cart_items()
buid=buyid()
# image = ("static/images/d1.jpg","static/images/d7.jpg","static/images/d2.jpg","static/images/d21.jpg","static/images/d23.jpg","static/images/img16.jpg","static/images/img17.jpg","static/images/img21.jpg","static/images/img15.jpg")
except mysql.connector.Error as err:
print(err)
return []
finally:
cur.close()
connection.close()
return render_template("product.html", items=items, subtotal=subtotal, items_len=items_len, buid=buid)
def single_product(pid, rol):
query = "SELECT med_name,med_brandname,med_purpose,med_price,med_role,dosage_form,med_quantity,med_id,med_expiry\
FROM medicine\
WHERE med_id= %s and med_role = %s"
connection = connect()
cur = connection.cursor()
try:
params = (
str(pid),
str(rol),
)
cur.execute(query, params)
items = cur.fetchone()
ite, subtotal, items_len = cart_items()
buid = buyid()
query = "SELECT med_name,med_brandname,med_purpose,med_price,med_role,med_id\
FROM medicine\
WHERE med_purpose = %s and med_role = %s LIMIT 3"
param = (
str(items[2]),
str(items[4]),
)
cur.execute(query, param)
rel = cur.fetchall()
# image = ("static/images/d1.jpg","static/images/d7.jpg","static/images/d2.jpg","static/images/d21.jpg","static/images/d23.jpg","static/images/img16.jpg","static/images/img17.jpg","static/images/img21.jpg","static/images/img15.jpg")
except mysql.connector.Error as err:
print(err)
return []
finally:
cur.close()
connection.close()
return render_template("single-product.html",
items=items,
subtotal=subtotal,
items_len=items_len,
rel=rel,
buid=buid)
def query_search():
ite, subtotal, items_len = cart_items()
se = request.form.get('searc', None)
typ = request.form.get('type', None)
buid=buyid()
if typ:
query = "SELECT med_name,med_brandname,med_purpose,med_price,med_role,dosage_form,med_id,med_quantity\
FROM medicine\
WHERE med_name LIKE %s and med_role=%s\
OR med_name LIKE %s and med_role=%s\
OR med_name LIKE %s and med_role=%s\
OR med_name LIKE %s and med_role=%s"
params = (se+"%", typ, "%"+se, typ, "%"+se+"%", typ, se, typ, )
else:
query = "SELECT med_name,med_brandname,med_purpose,med_price,med_role,dosage_form,med_id,med_quantity\
FROM medicine\
WHERE med_name LIKE %s \
OR med_name LIKE %s \
OR med_name LIKE %s \
OR med_name = %s "
params = (se+"%", "%"+se, "%"+se+"%", se, )
connection = connect()
cur = connection.cursor()
try:
cur.execute(query, params)
items = cur.fetchall()
print(query)
if len(items)==0:
flash("No results found related to your search!!", category="warning")
return redirect(url_for('mhome'))
# image = ("static/images/d1.jpg","static/images/d7.jpg","static/images/d2.jpg","static/images/d21.jpg","static/images/d23.jpg","static/images/img16.jpg","static/images/img17.jpg","static/images/img21.jpg","static/images/img15.jpg")
except mysql.connector.Error as err:
print(err)
return render_template('search.html', items=ite, subtotal=subtotal, items_len=items_len, buid=buid)
finally:
connection.commit()
cur.close()
connection.close()
return render_template('search.html', items=items, subtotal=subtotal, items_len=items_len, buid=buid)
def signup():
# message = ' '
if request.method == 'POST':
hashed_pass = bcrypt.generate_password_hash(
request.form['pass']).decode('utf-8')
fname = request.form['fname']
lname = request.form['lname']
email = request.form['email']
address = request.form['address']
category = request.form['category']
connection = connect()
cur = connection.cursor()
cur.execute("SELECT * FROM login WHERE user_email = %s", (email, ))
existing_user = cur.fetchone()
if existing_user is None:
cur.execute(
"INSERT INTO login(user_email, user_pass, user_first_name, user_last_name, user_address, user_category) VALUES(%s, %s, %s, %s, %s, %s)",
(email, hashed_pass, fname, lname, address, category))
connection.commit()
cur.close()
connection.close()
# message = "SignUp successfull!!"
flash("SignUp Successfull!!", 'success')
return redirect(url_for('login'))
# existing_user = Login.query.filter_by(user_email=email).first() # Check if user exists
# if existing_user is None:
# user = Login(user_id=i, user_email=email, user_pass=hashed_pass, user_first_name=fname, user_last_name=lname, user_address=address, user_category=category)
# db.session.add(user)
# db.session.commit()
else:
flash("Email id already exists!!", 'danger')
cur.close()
connection.close()
return redirect(url_for('signup'))
# message = "User already exists with that email id"
return render_template("signup.html", title='SignUp')
def supproduct_detail(pur):
query = "SELECT med_name,med_brandname,med_purpose,med_price,med_role,dosage_form,med_id,med_quantity\
FROM medicine\
WHERE med_purpose = %s and med_supplier=%s"
connection = connect()
cur = connection.cursor()
try:
params = (
pur,
session['role'],
)
cur.execute(query, params)
items = cur.fetchall()
# image = ("static/images/d1.jpg","static/images/d7.jpg","static/images/d2.jpg","static/images/d21.jpg","static/images/d23.jpg","static/images/img16.jpg","static/images/img17.jpg","static/images/img21.jpg","static/images/img15.jpg")
except mysql.connector.Error as err:
print(err)
return []
finally:
connection.commit()
cur.close()
connection.close()
return render_template("supproduct.html", items=items)
def checkout_details():
connection = connect()
cur = connection.cursor()
address = (str(request.form.get('address', None)) + ", " +
str(request.form.get('city', None)) + ", " +
str(request.form.get('postcode', None)) + ", " +
str(request.form.get('zone_id', None)))
try:
query = "INSERT into address(buyer_id, buyer_address)\
VALUES (%s, %s)"
buid = buyid()
params = (
session['user'],
address,
)
cur.execute(query, params)
items, subtotal, item_len = cart_items()
sum = 0.00
for item in items:
sum = sum + round(item[1] * item[9] * 1.2, 2)
sum = sum + 60 - session['cdis'] - session['pdis']
orders = []
for item in items:
orders.append((
item[9],
round(item[1] * item[9] * 1.2, 2),
'Pending',
request.form.get('payment_method', None),
address,
item[4],
item[7],
sum,
))
values = []
for order in orders:
params = (session['user'], order[0], order[1], order[2], order[3],
order[4], order[5], order[6], order[7])
values.append(params)
query = "INSERT into orders(buyer_id, order_quantity,\
order_date, order_price,\
delivery_status, payment_method, delivery_address,\
med_id, med_role, order_total)\
VALUES (%s, %s, NOW() , %s, %s, %s, %s, %s, %s, %s)"
cur.executemany(query, values)
values = []
for item in items:
params = (
item[9],
item[4],
item[7],
)
values.append(params)
query = "UPDATE medicine SET med_quantity = med_quantity - %s\
WHERE med_id = %s and med_role=%s"
cur.executemany(query, values)
values = []
for item in items:
params = (item[0], )
values.append(params)
query = "DELETE FROM cart_items WHERE buyer_user = %s "
params = (str(buid), )
cur.execute(query, params)
connection.commit()
except mysql.connector.Error as err:
print(err)
flash("Could not place your order. Try again later.",
category="warning")
connection.rollback()
return redirect(url_for('checkout'))
finally:
cur.close()
connection.close()
session['cdis'] = 0.00
flash("Order has been placed successfully!!", category="success")
return redirect(url_for('mhome'))
