def test_unregister():
req = request.get_json()
res = {}
if request.method == 'GET':
if 'user_id' in req:
sql1 = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.user_id = :param_1
"""
user_temp = engine.execute(text(sql1), {
'param_1': req['user_id']
}).fetchone()
if not user_temp == None:
if not user_temp.deleted:
sql2 = """
UPDATE user
SET deleted=:deleted
WHERE user.user_id = :user_id
"""
param = {'deleted': True, 'user_id': req['user_id']}
engine.execute(text(sql2), param)
try:
connection = engine.connect()
trans = connection.begin()
trans.commit()
except:
trans.rollback()
return "commit failed, failure in system!"
return redirect(url_for('show_all_user'))
else:
return "No user having this user_id"
else:
return "Bad request, request should be json object that include key of 'user_id'"
return "(test_unregister)No request received, request should be GET method"
def insert(engine, table, uid, phone_number, address, gender, birth_date, password, username):
assert type(table) == str
sql_text = """
INSERT INTO users
VALUES ('%s', '%s','%s','%s','%s','%s','%s')
""" % (uid,password, username, phone_number, address, gender, birth_date)
engine.execute(sql_text)
print("Invalid insertion")
def register():
username = request.args.get('username')
password = request.args.get('password')
password_hash = generate_password_hash(password)
account = Table('accounts', metadata, autoload=True)
engine.execute(account.insert(),
user_name=username,
password=password_hash)
return jsonify({'user_added': True})
def save_edited_image():
# db.session.expire_all()
req = request.get_json()
if request.method == 'POST':
# if ("user_id" in req) and ("org_id" in req) and ("photo" in req):
if ("user_id" in req) and ("org_id" in req) and ("photo" in req) and (
"date_edited" in req):
# assume photo is in string base64 form. we need to change to longblob form
photo_decoded = b64decode(req['photo'])
edited = Edit(user_id=req['user_id'],
org_id=req['org_id'],
photo=photo_decoded,
date_edited=req['date_edited'])
db.session.add(edited)
db.session.commit()
db.session.refresh(edited)
# edited_id = edited.id
# edited.set
#edit_obj=db.session.query(Edit).get(edited.id)
sql1 = '''
UPDATE edit
SET mark_id = mark_id + 1
WHERE edit.edit_id = :edit_id
'''
param1 = {'edit_id': edited.id}
engine.execute(text(sql1), param1)
sql2 = '''
UPDATE original
SET mark_num = mark_num + 1
WHERE original.org_id = :org_id
'''
param2 = {'org_id': req['org_id']}
engine.execute(text(sql2), param2)
try:
connection = engine.connect()
trans = connection.begin()
trans.commit()
except:
trans.rollback()
return "commit failed, failure in system!"
# edit_obj.set()
#return f"'{edit_obj.mark_id}'"
return redirect(url_for('show_all_edit'))
else:
return "Bad request, request should be json object that inclue key of 'user_id', 'org_id', 'photo', 'date'!"
else:
return f"(save_edited_image)No request received, request should be POST method"
def test_register():
req = request.get_json()
res = {}
if request.method == 'POST':
if ("username" in req) and ("email" in req) and ("password" in req):
sql1 = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.username = :username_1
"""
user_temp = engine.execute(text(sql1), {
'username_1': req["username"]
}).fetchall()
if not user_temp == []:
for user in user_temp:
if not user.deleted:
return f"'{user_temp}'existing username!"
sql2 = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.email = :email_1
"""
user_temp = engine.execute(text(sql2), {
'email_1': req["email"]
}).fetchall()
if not user_temp == []:
for user in user_temp:
if not user.deleted:
return f"'{user_temp}'existing email!"
sql3 = """
INSERT INTO user (username, email, password, deleted) VALUES (:username, :email, :password, :deleted)
"""
param = {
'username': req['username'],
'email': req['email'],
'password': req['password'],
'deleted': False
}
engine.execute(text(sql3), param)
try:
connection = engine.connect()
trans = connection.begin()
trans.commit()
except:
trans.rollback()
return "commit failed, failure in system!"
return redirect(url_for('show_all_user'))
else:
return "Bad request, request should be json object that include key of 'username','email','password'"
return "(test_register)No request received, request should be POST method"
def add_book():
isbn = request.args.get('isbn')
book_title = request.args.get('book_title')
book_author = request.args.get('book_author')
publication_year = request.args.get('publication_year')
image_url = request.args.get('image_url')
price = request.args.get('price')
books = Table('books', metadata, autoload=True)
engine.execute(books.insert(), isbn=isbn,
book_title=book_title, book_author=book_author, publication_year=publication_year,
image_url=image_url, price=price)
return jsonify({'book_added': True})
def add_comments():
if sign_in().status_code == 200:
user_name = request.args.get('user_name')
post_title = request.args.get('post_title')
comment_text = request.args.get('comment_text')
comment = Table('comments', metadata, autoload=True)
engine.execute(comment.insert(),
user_name=user_name,
post_title=post_title,
comment_text=comment_text)
return jsonify({'comment_added': True})
else:
return jsonify({"error": "No credentials given. Try logging in"})
def create_post():
if sign_in().status_code == 200:
post_created_by = request.args.get("post_created_by")
post_title = request.args.get("post_title")
post_text = request.args.get("post_text")
post = Table('posts', metadata, autoload=True)
engine.execute(post.insert(),
user_name=post_created_by,
post_title=post_title,
post_text=post_text)
return jsonify({'post_added': True})
else:
return jsonify({'error': "No credentials given. Try logging in"})
def search_product(engine, keyword):
return engine.execute("""
SELECT pid, p.name as product_name, b.name as brand_name, price
FROM products p, brands b
WHERE lower(p.name) LIKE '%%%%%s%%%%' and p.bid=b.bid
""" % keyword
)
def get_products(engine, data):
return engine.execute("""
SELECT products.name as product_name, brands.name as brand_name,
price, products.pid as pid, describe.content as content, image
FROM products, brands, describe
WHERE products.pid = describe.pid and products.pid = '%s' and products.bid = brands.bid;
""" % (data)).fetchone()
def get_product_brand(engine):
return engine.execute("""
SELECT p.pid as pid, p.name as product_name, b.name as brand_name,
price, d.content as content, image
FROM products as p, brands as b, describe as d
WHERE p.pid = d.pid and p.bid = b.bid;
""")
def get_orders(engine, data):
return engine.execute("""
SELECT name, oid, price , p.pid as pid, uid
from place_order as o, products as p
where o.uid = '%s' and p.pid = o. pid;
"""%(data)
)
def find_address(engine, data): sql_text = ''' SELECT * FROM user_payment WHERE uid = '%s' ''' % (data) return engine.execute(sql_text)
def get_item_to_checkout(engine, data):
return engine.execute("""
select *
from add_to_cart as a, products as p
where a.pid = p.pid and a.uid = '%s'
""" % (data)
)
def login():
db.session.expire_all()
res = {}
req = request.get_json()
if request.method == "POST":
if ("username" in req) and ("password" in req):
#username_1 = req['username']
#password_1 = req['password']
# return user_name
sql = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.username = :username_1
AND user.password = :password_1
"""
param = {
'username_1': req['username'],
'password_1': req['password']
}
users = engine.execute(text(sql), param).fetchall()
if users is None:
return "User is not registered. Check your username or password."
else:
for user in users:
res[f"'{user.id}'"] = user
return f"'{res}''"
else:
return 'Please enter username and password'
if current_user.is_authenticated:
return redirect(url_for('home'))
def search_product(engine, keyword):
return engine.execute("""
SELECT p.pid, p.name as product_name, b.name as brand_name,
price, d.content as content, image
FROM products p, brands b, describe as d
WHERE lower(p.name) LIKE '%%%%%s%%%%' and p.bid=b.bid and p.pid = d.pid
""" % keyword)
def delete():
form = DeleteForm()
ps = engine.execute("""
select * from products;
""")
form.pid.choices = [(str(p['pid']), str(p['pid']) + str(p['name']))
for p in ps]
if form.validate_on_submit():
try:
engine.execute("""
DELETE FROM products
where products.pid = '%s';
""" % (form.pid.data))
except:
print('Can not delete this product')
return redirect(url_for('home'))
return render_template('delete.html', form=form)
def get_item_in_cart(engine, data1, data2): sql_text=''' SELECT * FROM add_to_cart WHERE uid = '%s' and pid = '%s' ''' % (data1, data2) cursor = engine.execute(sql_text) fetch = cursor.first() cursor.close() return fetch
def find_user_id(engine, data): sql_text = ''' SELECT * FROM users WHERE uid = '%s' ''' % (data) cursor = engine.execute(sql_text) fetch = cursor.first() cursor.close() return fetch
def show_all_user():
res = {}
sql = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.deleted = false
"""
users = engine.execute(text(sql)).fetchall()
for user in users:
res[f"'{user.id}'"] = user
return f"'{res}'"
def show_all_edit():
res = {}
sql = """
SELECT edit.edit_id AS id, edit.user_id AS user_id, edit.org_id AS org_id, edit.date_edited AS date_edited, edit.deleted AS deleted
FROM edit
WHERE edit.deleted = false
"""
edits = engine.execute(text(sql)).fetchall()
for edit in edits:
res[f"'{edit.id}'"] = edit
return f"'{res}'"
def show_one_user():
user_id = request.args.get('id', default='0', type=str)
db.session.expire_all()
req = request.get_json()
res = {}
if request.method == 'GET':
if user_id != '0':
sql1 = """
SELECT user.user_id AS id, user.username AS username, user.email AS email, user.password AS password, user.deleted AS deleted
FROM user
WHERE user.user_id = :param_1
"""
user_temp = engine.execute(text(sql1), {
'param_1': user_id
}).fetchall()
sql2 = """
SELECT edit.edit_id AS edit_edit_id, edit.photo AS edit_photo, edit.user_id AS edit_user_id, edit.org_id AS edit_org_id, edit.deleted AS edit_deleted, edit.mark_id AS edit_mark_id, edit.date_edited AS edit_date_edited
FROM edit
WHERE edit.user_id = :param_2
"""
history = engine.execute(text(sql2), {
'param_2': user_id
}).fetchall()
if not user_temp == []:
user_temp = user_temp[0]
if user_temp.deleted:
return "no longer exist"
else:
res['user_id'] = user_temp.id
res['user_username'] = user_temp.username
res['user_email'] = user_temp.email
res['user_password'] = user_temp.password
res['user_history'] = history
return jsonify(res)
else:
return "no user found"
else:
return "Bad request, request should be json object that include key of 'user_id'"
return "(show_one_user)No request received, request should be GET method"
def get_comments(engine, data):
return engine.execute("""
WITH temp(oid, cid, content, uid, ctime, rating) as
(SELECT p.oid, c.cid, c.content, c.uid, c.time, c.rating
FROM place_order as p, comments_followed_post as c
WHERE p.oid = c.oid and p.pid = '%s')
SELECT DISTINCT oid, cid, content, username, ctime, rating, avg(rating) as avg_rating
FROM temp, users
WHERE temp.uid = users.uid
GROUP BY oid, cid, content, username, ctime, rating
ORDER BY ctime DESC;
""" % (data))
def delete_edited_image():
req = request.get_json()
# res = {}
if request.method == 'GET':
if 'edit_id' in req:
sql1 = """
SELECT edit.edit_id AS id, edit.user_id AS user_id, edit.org_id AS org_id, edit.deleted AS deleted
FROM edit
WHERE edit.edit_id = :param_1
"""
edit_temp = engine.execute(text(sql1), {
'param_1': req['edit_id']
}).fetchone()
if not edit_temp == None:
if not edit_temp.deleted:
sql2 = """
UPDATE edit
SET deleted=:deleted
WHERE edit.edit_id = :edit_id
"""
param = {'deleted': True, 'edit_id': req['edit_id']}
engine.execute(text(sql2), param)
return f"'{param}'"
# try:
# connection = engine.connect()
# trans = connection.begin()
# trans.commit()
# except:
# trans.rollback()
# return "commit failed, failure in system!"
# return redirect(url_for('show_all_edit'))
else:
return "No edited image having this edit_id"
else:
return "Bad request, request should be json object that include key of 'edit_id'"
else:
return "(delete_edited_image)No request received, request should be GET method"
def show_one_image():
org_id = request.args.get('id', default='0', type=str)
db.session.expire_all()
res = {}
if request.method == 'GET':
if org_id != '0':
sql1 = """
SELECT original.org_id AS id,
original.path AS path,
original.image_code AS image_code,
original.seg_num AS seg_num,
original.part_num AS part_num,
original.mark_num AS mark_num,
original.collection_num AS collection_num,
original.photo AS photo
FROM original
WHERE original.org_id = :param_1
"""
org_temp = engine.execute(text(sql1), {
'param_1': org_id
}).fetchone()
if not org_temp == None:
res['id'] = org_temp.id
res['path'] = org_temp.path
res['image_code'] = org_temp.image_code
res['seg_num'] = org_temp.seg_num
res['part_num'] = org_temp.part_num
res['mark_num'] = org_temp.mark_num
res['collection_num'] = org_temp.collection_num
photo_encoded = b64encode(org_temp.photo)
photo_decoded = photo_encoded.decode('utf-8')
res['photo'] = photo_decoded
pixel = [(905, 14), (1389, 77), (1584, 22), (2702, 162),
(4267, 249), (5278, 161)]
res['pixel_x'] = pixel[res['part_num']][0]
res['pixel_y'] = pixel[res['part_num']][1]
return jsonify(res)
else:
return "no original image found"
else:
return "Bad request, request should be json object that include key of 'id'"
return "(show_one_image)No request received, request should be GET method"
def manager():
form = ManagerForm()
if form.validate_on_submit():
brand = engine.execute(
"""Select bid from brands where brands.name = '%s';""" %
(form.brand)).fetchone()
if brand == None:
brand_id = engine.execute("""
SELECT max(bid)
FROM brands
""").fetchone()[0] + 1
engine.execute("""
INSERT INTO brands
values('%s', '%s', 'No Description');
""" % (brand_id, form.brand.data))
else:
brand_id = brand[0]
try:
product_id = engine.execute("""
SELECT max(pid)
FROM products
""").fetchone()[0] + 1
did = add_id(engine, "did", "describe")
print(did)
engine.execute("""
INSERT INTO products
VALUES ('%s','%s','%s','%s');
""" % (product_id, brand_id, form.price.data, form.name.data))
engine.execute("""
INSERT INTO describe
VALUES ('%s','%s','%s','%s');
""" % (did, product_id, form.content.data, form.image.data))
except:
print("invalid price type")
return redirect(url_for('home'))
return render_template('manager.html', form=form)
def profile(username):
user = find_user(engine, username)
user = Customer(user)
form = ProfileForm()
if form.validate_on_submit():
cid = add_id(engine, 'cid', 'comments_followed_post')
print(cid, form.oid.data, user.uid, form.pid.data, form.comment.data,
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
form.rating.data)
print(engine.execute('select * from comments_followed_post').keys())
insert_to_comments(
engine, cid, form.oid.data, user.uid, form.pid.data,
form.comment.data,
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
form.rating.data)
orders = get_orders(engine, user.uid)
return render_template('profile.html', user=user, orders=orders, form=form)
def delete_item_in_cart(engine, data1, data2):
engine.execute("""
DELETE FROM add_to_cart
WHERE uid = '%s' and pid = '%s'
""" % (data1, data2)
)
def get_products(engine, data):
return engine.execute("""
SELECT products.name as product_name, brands.name as brand_name, price, pid
FROM products, brands
WHERE pid = '%s' and products.bid = brands.bid;
""" % (data)).fetchone()
def get_product_brand(engine):
return engine.execute("""
SELECT pid, p.name as product_name, b.name as brand_name, price
FROM products as p, brands as b
WHERE p.bid = b.bid;
""")