def index(id, position):
g.current = "profile"
db = get_db()
cursor = db.cursor()
cursor.execute("SELECT phone FROM %s_phone WHERE id = '%d'" % (position, id,))
phone = cursor.fetchone()
if phone == None:
cursor.execute("SELECT pos.id, pos.position position, pos.username username, pos.level level, "
"pos.birthday birthday, pos.home home"
" FROM %s pos"
" WHERE pos.id = '%d'" % (position, id,))
profiles = cursor.fetchone()
profiles['phone'] = None
else:
cursor.execute("SELECT pos.id, pos.position position, pos.username username, pos.level level, "
"pos.birthday birthday, pos.home home, MAX(phone.phone) phone"
" FROM %s pos, %s_phone phone"
" WHERE pos.id = '%d' AND"
" pos.id = phone.id" % (position, position, id,))
profiles = cursor.fetchone()
if profiles['position'] == 'aftereffect':
profiles['position'] = 'After Effect'
if profiles['position'] == 'devicemanager':
profiles['position'] = 'Device Manager'
if profiles['position'] == 'projectmanager':
profiles['position'] = 'Project Manager'
if profiles['position'] == 'photographer':
profiles['position'] = 'Photographer'
return render_template('profile/profile_index.html', profiles=profiles)
def get_all_name(position):
db = get_db()
cursor = db.cursor()
cursor.execute("SELECT username FROM %s " % (position, ))
names = cursor.fetchall()
return names
def delete(id):
get_post(id)
db = get_db()
cursor = db.cursor()
cursor.execute("DELETE FROM post WHERE id = '%d'" % (id,))
db.commit()
return redirect(url_for('dashboard.index',orders=None, sales = None))
def update(id):
post = get_post(id)
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
db = get_db()
cursor = db.cursor()
cursor.execute(
"UPDATE post SET title = '%s', body = '%s'"
" WHERE id = '%d'" % \
(title, body, id)
)
db.commit()
return redirect(url_for('dashboard.index'))
return render_template('dashboard/update.html', post=post)
def delete(id):
position = 'photographer' # just in case
g.current = "profile"
get_profile(id, position)
db = get_db()
cursor = db.cursor()
cursor.execute("DELETE FROM post WHERE id = '%d'" % (id,))
db.commit()
return redirect(url_for('dashboard.index'))
def update(id, position):
g.current = "profile"
profiles = get_profile(id, position)
if profiles['position'] == 'aftereffect':
profiles['position'] = 'After Effect'
if profiles['position'] == 'devicemanager':
profiles['position'] = 'Device Manager'
if profiles['position'] == 'projectmanager':
profiles['position'] = 'Project Manager'
if profiles['position'] == 'photographer':
profiles['position'] = 'Photographer'
if request.method == 'POST':
username = request.form['username']
birthday = request.form['birthday']
phone = request.form['phone']
password = request.form['password']
password2 = request.form['password2']
home = request.form['address']
username = str(username)
birthday = str(birthday)
phone = str(phone)
password = str(password)
password2 = str(password2)
error = None
if not username:
error = 'Username is required.'
if password != password2:
error = 'Password is not consistent'
if not (len(phone) == 11 or len(phone) == 8) or not phone.isdigit():
error = 'Incorrect phone'
if error is not None:
flash(error)
return render_template('profile/profile_update.html', profiles=profiles, error = error)
else:
db = get_db()
cursor = db.cursor()
cursor.execute("DELETE FROM %s_phone WHERE id = '%d'" % (position, id))
cursor.execute(
"UPDATE %s SET username = '******', birthday = '%s', password = '******', home = '%s'"
" WHERE id = '%d'" % \
(position, username, birthday, generate_password_hash(password), home, id)
)
cursor.execute("INSERT INTO %s_phone(id, phone) VALUES ('%d', '%s')" % (position, id, phone))
db.commit()
return redirect(url_for('profile.index', id=id, position=position))
return render_template('profile/profile_update.html', profiles=profiles, error = error)
return render_template('profile/profile_update.html', profiles=profiles)
def detail_delete(id):
db = get_db()
cursor = db.cursor()
val = (id, )
cursor.execute("DELETE FROM takephoto WHERE orderid = %s", val)
cursor.execute("DELETE FROM doeffect WHERE orderid = %s", val)
cursor.execute("DELETE FROM boughtby WHERE orderid = %s", val)
cursor.execute("DELETE FROM photodevice WHERE orderid = %s", val)
cursor.execute("DELETE FROM porder WHERE orderid = %s", val)
cursor.execute("DELETE FROM vehicle WHERE orderid = %s", val)
db.commit()
return redirect(url_for('dashboard.index'))
def load_logged_in_user():
user_id = session.get('user_id')
user_position = session.get('user_position')
user_position = str(user_position)
if user_id is None:
g.user = None
else:
db = get_db()
cursor = db.cursor()
cursor.execute("SELECT * FROM %s WHERE id = '%d'" % (
user_position,
user_id,
))
g.user = cursor.fetchone()
def get_photographers(id, check_author=True):
db = get_db()
cursor = db.cursor()
val = (id, )
cursor.execute(
"SELECT photo.id id, photo.username name, photo.level level, MAX(pp.phone) phone"
" FROM photographer photo, photographer_phone pp"
" WHERE photo.id = pp.id AND"
" photo.id in (SELECT photographerid"
" FROM takephoto"
" WHERE orderid = %s)"
" GROUP BY photo.id, photo.username, photo.level", val)
photographers = cursor.fetchall()
return photographers
def history_index():
if (g.user):
g.current = "history_order.history_index"
db = get_db()
cursor = db.cursor()
cursor.execute(
"SELECT *"
" FROM porder"
" WHERE status = 'complete'"
" ORDER BY satisfaction DESC"
)
orders = cursor.fetchall()
return render_template('history_order/history_index.html', orders=orders)
else:
return redirect(url_for('auth.login'))
def get_aftereffects(id, check_author=True):
db = get_db()
cursor = db.cursor()
val = (id, )
cursor.execute(
"SELECT effect.id id, effect.username name, effect.level level, MAX(ap.phone) phone"
" FROM aftereffect effect, aftereffect_phone ap"
" WHERE effect.id = ap.id AND"
" effect.id in (SELECT effectid"
" FROM doeffect"
" WHERE orderid = %s)"
" GROUP BY effect.id, effect.username, effect.level", val)
aftereffects = cursor.fetchall()
return aftereffects
def index():
if (g.user):
g.current = "index"
db = get_db()
cursor = db.cursor()
if g.user['position'] == 'projectmanager':
cursor.execute(
"SELECT *"
" FROM porder"
" WHERE NOT status = 'complete' AND managerid = '%d' "
" ORDER BY orderid" % (g.user['id'])
)
if g.user['position'] == 'photographer':
cursor.execute(
"SELECT *"
" FROM porder"
" WHERE NOT status = 'complete' AND orderid IN (SELECT"
" orderid FROM takephoto WHERE photographerid = '%d')"
" ORDER BY orderid" % (g.user['id'])
)
if g.user['position'] == 'aftereffect':
cursor.execute(
"SELECT *"
" FROM porder"
" WHERE NOT status = 'complete' AND orderid IN (SELECT "
"orderid FROM doeffect WHERE effectid = '%d')"
" ORDER BY orderid" % (g.user['id'])
)
print(g.user['position'])
orders = cursor.fetchall()
cursor.execute(
"SELECT MONTH(startdate) month, SUM(price) sale"
" FROM porder"
" WHERE YEAR(startdate) = YEAR(CURDATE())"
" GROUP BY MONTH(startdate)"
)
sales = cursor.fetchall()
# cursor.execute(
# "SELECT o.managerid o.SUM(price) m.username"
# "FROM porder o, projectmanager m"
# "WHERE o.managerid = m.id AND"
# )
return render_template('dashboard/index.html', orders=orders, sales = sales)
else:
return redirect(url_for('auth.login'))
def login():
g.current = "unlogin"
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
position = request.form['position']
position = str(position)
db = get_db()
error = None
cursor = db.cursor()
if position == 'boss' or position == 'Device Manager':
error = 'This position is not available in the demo'
position = position.lower()
if error is None:
if position == 'project manager':
position = 'projectmanager'
val = (username)
cursor.execute(
"SELECT * FROM projectmanager WHERE username = %s", val)
if position == 'after effect':
position = 'aftereffect'
val = (username)
cursor.execute("SELECT * FROM aftereffect WHERE username = %s",
val)
if position == 'photographer':
val = (username)
cursor.execute(
"SELECT * FROM photographer WHERE username = %s", val)
user = cursor.fetchone()
if user is None:
error = 'Incorrect username.'
else:
if not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['id']
session['user_position'] = user['position']
return redirect(url_for('index'))
return render_template('auth/login.html', error=error)
return render_template('auth/login.html')
def get_post(id, check_author=True):
db = get_db()
cursor = db.cursor()
cursor.execute(
"SELECT p.id, title, body, created, author_id, username"
" FROM post p JOIN user u ON p.author_id = u.id"
" WHERE p.id = '%d'" % \
(id,)
)
post = cursor.fetchone()
if post is None:
abort(404, "Post id {0} doesn't exist.".format(id))
if check_author and post['author_id'] != g.user['id']:
abort(403)
return post
def get_order(id, check_author=True):
db = get_db()
cursor = db.cursor()
val = (id, )
cursor.execute(
"SELECT ord.orderid orderid, ord.startdate startdate,"
" ord.status status, ord.expectduration expectduration,"
" ord.price price, ord.place place, ord.ordertype ordertype, ord.description description,"
" ord.satisfaction satisfaction, ma.username managername"
" FROM porder ord, projectmanager ma"
" WHERE ord.orderid = %s AND"
" ord.managerid = ma.id", val)
order = cursor.fetchone()
if order is None:
abort(404, "Order id {0} doesn't exist.".format(id))
# if check_author and post['author_id'] != g.user['id']:
# abort(403)
return order
def get_profile(id, position, check_author=True):
db = get_db()
cursor = db.cursor()
# position = "".join(position.split()) ## remove space
sql = ("SELECT * FROM %s WHERE id = '%d'" % (position, id,))
cursor.execute(sql)
profiles = cursor.fetchone()
cursor.execute("SELECT phone FROM %s_phone WHERE id = '%d'" % (position, id,))
phone = cursor.fetchone()
if phone == None:
profiles['phone'] = None
else:
profiles['phone'] = phone['phone']
if profiles is None:
abort(404, "Post id {0} doesn't exist.".format(id))
if check_author and profiles['id'] != g.user['id']:
abort(403)
return profiles
def create():
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
db = get_db()
cursor = db.cursor()
cursor.execute(
"INSERT INTO post (title, body, author_id)"
" VALUES ('%s', '%s', '%d')" % \
(title, body, g.user['id'])
)
db.commit()
return redirect(url_for('dashboard.index'))
return render_template('dashboard/create.html')
def order_check(id=-1):
error = None
flag = False
if request.method == 'POST':
status = request.form['status']
startdate = request.form['startdate']
expectduration = request.form['expectduration']
price = request.form['price']
ordertype = request.form['ordertype']
managername = request.form['managername']
description = request.form['description']
photographernames = request.form.getlist('photographer_name')
aftereffectnames = request.form.getlist('aftereffect_name')
status = str(status)
startdate = str(startdate)
expectduration = int(expectduration)
price = int(price)
ordertype = str(ordertype)
managername = str(managername)
description = str(description)
ordertype = ordertype.lower()
error = None
flag = True
if not status or not startdate or not expectduration or not price \
or not ordertype or not managername or not photographernames or \
not aftereffectnames or not description:
error = 'Basic information is not complete.'
if not photographernames or not aftereffectnames:
error = 'Photographer and aftereffect information is not complete.'
if ordertype != 'wedding' and ordertype != 'art' and ordertype != 'business':
error = 'This order type does not exist'
if expectduration > 1000:
error = 'Expect duration is larger than 1000 days'
db = get_db()
cursor = db.cursor()
val = (managername, )
cursor.execute("SELECT id from projectmanager WHERE username = %s",
val)
manager = cursor.fetchone()
if manager is None:
error = 'Incorrect manager'
if error is not None:
# flash(error)
flag = False
else:
flag = True
managerid = manager['id']
managerid = int(managerid)
if id == -1:
val = (startdate, status, expectduration, price, ordertype,
managerid)
cursor.execute(
"INSERT INTO porder(startdate, status, expectduration, price, ordertype, managerid)"
"VALUES (%s, %s, %s, %s, %s, %s);", val)
cursor.execute(
" SELECT orderid FROM porder ORDER BY orderid DESC")
return_order = cursor.fetchone()
id = return_order['orderid']
if id != -1:
val = (startdate, status, expectduration, price, ordertype,
managerid, description, id)
cursor.execute(
"UPDATE porder SET startdate = %s, status = %s,"
" expectduration = %s, price = %s, ordertype = %s,"
" managerid = %s, description = %s"
" WHERE orderid = %s", val)
val = (id, )
cursor.execute("DELETE FROM takephoto WHERE orderid = %s", val)
cursor.execute("DELETE FROM doeffect WHERE orderid = %s", val)
for photographername in photographernames:
val = (photographername)
cursor.execute(
"SELECT id FROM photographer WHERE username = %s", val)
photographerid = cursor.fetchone()
val = (id, photographerid['id'])
cursor.execute(
"INSERT INTO takephoto(orderid, photographerid) VALUES (%s, %s)",
val)
for aftereffectname in aftereffectnames:
val = (aftereffectname)
cursor.execute(
"SELECT id FROM aftereffect WHERE username = %s", val)
aftereffectid = cursor.fetchone()
val = (id, aftereffectid['id'])
cursor.execute(
"INSERT INTO doeffect(orderid, effectid) VALUES (%s, %s)",
val)
db.commit()
return flag, error, id
def register():
g.current = "unlogin"
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
password2 = request.form['password2']
position = request.form['position']
position = str(position)
db = get_db()
error = None
cursor = db.cursor()
if not username:
error = 'Username is required.'
elif password != password2:
error = 'Password is inconsistant.'
elif not position:
error = 'position is required.'
elif position == 'Boss' or position == 'Device Manager':
error = 'This position is not available in the demo'
else:
position = position.lower()
if position == 'project manager':
position = 'projectmanager'
val = (username)
cursor.execute(
"SELECT id FROM projectmanager WHERE username = %s", val)
if position == 'after effect':
position = 'aftereffect'
val = (username)
cursor.execute(
"SELECT id FROM aftereffect WHERE username = %s", val)
if position == 'photographer':
val = (username)
cursor.execute(
"SELECT id FROM photographer WHERE username = %s", val)
if cursor.fetchone() == None:
error = 'User {} Does not exist. Or you enter the wrong position'.format(
username)
if error is None:
cursor.execute(
"UPDATE %s SET password = '******' WHERE username = '******'" % \
(position, generate_password_hash(password), username))
if position == 'projectmanager':
val = (generate_password_hash(password), username)
cursor.execute(
"UPDATE projectmanager SET password = %s WHERE username = %s",
val)
if position == 'photographer':
val = (generate_password_hash(password), username)
cursor.execute(
"UPDATE photographer SET password = %s WHERE username = %s",
val)
if position == 'aftereffect':
val = (generate_password_hash(password), username)
cursor.execute(
"UPDATE aftereffect SET password = %s WHERE username = %s",
val)
db.commit()
return redirect(url_for('auth.login'))
print("resigter page error is: ", error)
return render_template('auth/register.html', error=error)
return render_template('auth/register.html')