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