def signup():
if request.method != "POST":
return index()
else:
username = request.form.get("username").lower()
email = request.form.get("email").lower()
password = generate_password_hash(request.form.get("password"))
# Verifica si ya existe usuario y email
db_username = select_query(
f"select username from users where username='******'")
db_email = select_query(
f"select email from users where username='******'")
if db_username or db_email:
print("Usuario o email existente")
return index()
elif request.form.get("password") == request.form.get("password2"):
# Guarda el registro en la DB
modify_query(
f"insert into users (username, email, password) values ('{username}', '{email}', '{password}')"
)
# Registra la sesión
session["username"] = username
return index()
def view_past_order(cursor, username, startdate, enddate):
uid = select_query(cursor,
'select uid from users where username = %s', (username,))[0][0]
if startdate is None and enddate is None:
order = select_query(cursor,
'select orderTime, rName, fName, price, review '
'from users join orders on uid = cid '
'join contains using(oid) '
'join restaurants using(rid) '
'join foodItems using(fid) '
'join menu using(rid, fid) '
'where uid = %s', (uid,))
elif startdate is None:
order = select_query(cursor,
'select orderTime, rName, fName, price, review '
'from users join orders on uid = cid '
'join contains using(oid) '
'join restaurants using(rid) '
'join foodItems using(fid) '
'join menu using(rid, fid) '
'where uid = %s and date(orderTime) <= %s', (uid, enddate))
elif enddate is None:
order = select_query(cursor,
'select orderTime, rName, fName, price, review '
'from users join orders on uid = cid '
'join contains using(oid) '
'join restaurants using(rid) '
'join foodItems using(fid) '
'join menu using(rid, fid) '
'where uid = %s and date(orderTime) >= %s', (uid, startdate))
else:
order = select_query(cursor,
'select orderTime, rName, fName, price, review '
'from users join orders on uid = cid '
'join contains using(oid) '
'join restaurants using(rid) '
'join foodItems using(fid) '
'join menu using(rid, fid) '
'where uid = %s and date(orderTime) >= %s and date(orderTime) <= %s',
(uid, startdate, enddate))
return [
{
"order time": item[0],
"restaurant": item[1],
"foodItem": item[2],
"price": item[3],
'review': item[4]
} for item in order
]
def create_promotion(connection, cursor, restaurant, foodItem, endDate,
startDate, promotionType, percent, maxAmount, flatAmount,
minAmount):
update_query(
connection, cursor,
'insert into Promotions (pid, startDate, endDate) values '
'((select count(*) from Promotions) + 1, %s, %s);',
(startDate, endDate))
if len(restaurant) == 0 and len(foodItem) == 0:
update_query(
connection, cursor, 'insert into Promotes (pid, rid, fid) '
'values ((select count(*) from Promotions), NULL, NULL);')
elif len(restaurant) != 0 and len(foodItem) == 0:
rid = select_query(cursor,
'select rid from Restaurants where rName = %s',
(restaurant, ))[0][0]
update_query(
connection, cursor, 'insert into Promotes (pid, rid, fid) '
'values ((select count(*) from Promotions), %s, NULL);', (rid, ))
elif len(restaurant) == 0 and len(foodItem) != 0:
fid = select_query(cursor,
'select fid from FoodItems where fName = %s',
(foodItem, ))[0][0]
update_query(
connection, cursor, 'insert into Promotes (pid, rid, fid) '
'values ((select count(*) from Promotions), NULL, %s);', (fid, ))
else:
rid = select_query(cursor,
'select rid from Restaurants where rName = %s',
(restaurant, ))[0][0]
fid = select_query(cursor,
'select fid from FoodItems where fName = %s',
(foodItem, ))[0][0]
update_query(
connection, cursor, 'insert into Promotes (pid, rid, fid) '
'values ((select count(*) from Promotions), %s, %s);', (rid, fid))
if promotionType == 'percent':
update_query(
connection, cursor,
'insert into Percentage (pid, percent, maxAmount) '
'values ((select count(*) from Promotions), %s, %s);',
(percent, maxAmount))
else:
update_query(
connection, cursor,
'insert into Flat (pid, flatAmount, minAmount) '
'values ((select count(*) from Promotions), %s, %s);',
(flatAmount, minAmount))
def update_menu(connection, cursor, username, min_spent, avai, day_limit, no_orders, price, fid):
rid = select_query(cursor,
'select rid from manages join users using(uid) '
'where username = %s', (username,))[0][0]
if min_spent is not None:
update_query(connection, cursor,
'update restaurants '
'set minSpent = %s '
'where rid = %s;', (float(min_spent), rid))
if avai is not None:
update_query(connection, cursor,
'update menu '
'set availability = %s '
'where rid = %s and fid = %s;', ((avai,), rid, fid))
if day_limit is not None:
update_query(connection, cursor,
'update menu '
'set dayLimit = %s '
'where rid = %s and fid = %s;', (int(day_limit), rid, fid))
if no_orders is not None:
update_query(connection, cursor,
'update menu '
'set noOfOrders = %s '
'where rid = %s and fid = %s;', (int(no_orders), rid, fid))
if price is not None:
update_query(connection, cursor,
'update menu '
'set price = %s '
'where rid = %s and fid = %s;', (float(price), rid, fid))
def order_summary(cursor, area, day, starttime, endtime):
data = select_query(cursor,
'select oid, orderTime, rName, fName, cid '
'from orders o join delivers d using(oid) '
'join contains using(oid) '
'join restaurants using(rid) '
'join foodItems using(fid) '
'where d.location = %s '
'and date(orderTime) = %s and (select orderTime::time) >= %s '
'and (select orderTime::time) <= %s;',
(area, day, starttime, endtime))
ret = [
{
'oid': item[0],
'orderTime': item[1],
'rName': item[2],
'fName': item[3],
'uid': item[4]
}
for item in data
]
return {
'no. of order': len(ret),
'order': ret
}
def verify_customer(cursor, username, creditcard, cvv):
verified_creditcard, verified_cvv = select_query(
cursor, 'select creditCardNumber, cvv from '
'customers join users using(uid) '
'where username = %s;', (username, ))[0]
if creditcard != verified_creditcard or cvv != verified_cvv:
raise Exception
def register(connection, cursor, username, password, phone, user_type,
rider_type):
uid = select_query(cursor, 'select count(*) from users;')[0][0] + 1
update_query(
connection, cursor,
'insert into Users (uid, username, password, phone) values '
'(%s, %s, %s, %s);', (uid, username, password, (phone, )))
if user_type == 'customer':
today = date.today()
update_query(
connection, cursor,
'insert into Customers (uid, rewardPoints, registerDate) values '
'(%s, 0, %s);', (uid, today))
if user_type == 'rider':
update_query(connection, cursor,
'insert into Riders (uid) values (%s);', (uid, ))
if rider_type == 'partTime':
update_query(
connection, cursor,
'insert into PartTimeRiders (uid, psalary) values (%s, 12);',
(uid, ))
if rider_type == 'fullTime':
update_query(
connection, cursor,
'insert into FullTimeRiders (uid, fsalary) values (%s, 40);',
(uid, ))
if user_type == 'staff':
update_query(
connection, cursor,
'insert into Staffs (uid) values ((select count(*) from Users));')
if username == 'manager':
update_query(
connection, cursor,
'insert into Managers (uid) values ((select count(*) from Users));'
)
def get_restaurant(cursor, restaurant):
if restaurant is None:
all_restaurants = select_query(cursor,
"select rName, location from Restaurants;",
(restaurant,))
else:
all_restaurants = select_query(cursor,
"""select rName, location from Restaurants
where lower(rName) like '%%' || %s || '%%';""",
(restaurant,))
return [
{
'rName': item[0],
'location': item[1]
} for item in all_restaurants
]
def get_recent_location(cursor, username):
locations = select_query(cursor,
'select distinct location '
'from users join orders on(uid = cid) '
'join delivers using(oid) '
'order by startTime desc '
'limit 5;')
return locations
def month_summary(cursor, month, year):
new_customer = select_query(cursor,
'select count(*) '
'from customers '
'where extract(month from registerDate) = %s '
'and extract(year from registerDate) = %s;',
(month, year))[0][0]
all_order = select_query(cursor,
'select count(distinct oid), sum(price*quantity), sum(deliverCost) '
'from orders join menu using(rid) '
'join delivers using(oid) '
'join contains using(oid) '
'where (select extract(month from orderTime)) = %s '
'and (select extract(year from orderTime)) = %s;',
(month, year))[0]
user_order_in_month = select_query(cursor,
'select distinct cid '
'from orders '
'where (select extract(month from orderTime)) = %s '
'and (select extract(year from orderTime)) = %s;',
(month, year))
user_ids = tuple([item[0] for item in user_order_in_month])
user_summary = select_query(cursor,
'select cid, count(distinct oid), sum(price*quantity), sum(deliverCost) '
'from orders join menu using(rid) '
'join delivers using(oid) '
'join contains using(oid) '
'where (select extract(month from orderTime)) = %s '
'and (select extract(year from orderTime)) = %s '
'group by (cid) '
'having cid in %s;',
(month, year, user_ids))
ret = {
'new user': new_customer,
'no. of orders': all_order[0],
'total price': all_order[1] + all_order[2],
'user': [
{
'cid': item[0],
'no. of orders': item[1],
'total price': item[2] + item[3]
}
for item in user_summary
]
}
return ret
def get_unique_groups(query, params=None):
all_groups = []
append = all_groups.append
all_groups_raw = select_query(query, params)
for item in all_groups_raw:
if item[0] not in all_groups:
append(item[0])
log.info("fetched {} unique groups".format(len(all_groups)))
return all_groups
def view_promotion(cursor):
today = date.today()
flat = select_query(
cursor,
'select pid, startDate, endDate, flatAmount, minAmount, rid, fid, rName, fName '
'from promotions join flat using(pid) '
'join promotes using(pid) '
'join restaurants using(rid) '
'join FoodItems using(fid) '
'where startDate <= %s and endDate >= %s;', (today, today))
update_flat = [{
'type': 'flat',
'pid': item[0],
'startDate': item[1],
'endDate': item[2],
'flatAmount': item[3],
'minAmount': item[4],
'rid': item[5],
'fid': item[6],
'rName': item[7],
'fName': item[8]
} for item in flat]
percentage = select_query(
cursor,
'select pid, startDate, endDate, percent, maxAmount, rid, fid, rName, fName '
'from promotions join percentage using(pid) '
'join promotes using(pid) '
'join restaurants using(rid) '
'join FoodItems using(fid) '
'where startDate <= %s and endDate >= %s;', (today, today))
update_percentage = [{
'type': 'percentage',
'pid': item[0],
'startDate': item[1],
'endDate': item[2],
'percent': item[3],
'maxAmount': item[4],
'rid': item[5],
'fid': item[6],
'rName': item[7],
'fName': item[8]
} for item in percentage]
return update_flat + update_percentage
def delete_promotion(connection, cursor, pid):
percent_query = select_query(
cursor,
'select 1 from Promotions join Percentage using(pid) where pid = %s;',
(pid, ))
if len(percent_query) != 0:
update_query(connection, cursor,
'delete from Percentage where pid = %s;', (pid, ))
flat_query = select_query(
cursor,
'select 1 from Promotions join Flat using(pid) where pid = %s;',
(pid, ))
if len(flat_query) != 0:
update_query(connection, cursor, 'delete from Flat where pid = %s;',
(pid, ))
update_query(connection, cursor, 'delete from Promotes where pid = %s;',
(pid, ))
update_query(connection, cursor, 'delete from Promotions where pid = %s;',
(pid, ))
def view_review(cursor, rid):
review = select_query(
cursor, 'select username, orderTime, review '
'from orders join users on (cid = uid) '
'where rid = %s and review is not null;', (rid, ))
return [{
'username': item[0],
'orderTime': item[1].strftime("%d/%m/%Y %H:%M:%S"),
'review': item[2]
} for item in review]
def login(cursor, username, password):
ret = select_query(
cursor,
'select username, password from customers join users using(uid) '
'where username = %s and password = %s', (username, password))
if len(ret) != 0:
return {
'position': 'customer',
}
ret = select_query(
cursor, 'select username, password from riders join users using(uid) '
'join parttimeriders using(uid) '
'where username = %s and password = %s', (username, password))
if len(ret) != 0:
return {
'position': 'partTime rider',
}
ret = select_query(
cursor, 'select username, password from riders join users using(uid) '
'join fulltimeriders using(uid) '
'where username = %s and password = %s', (username, password))
if len(ret) != 0:
return {
'position': 'fullTime rider',
}
ret = select_query(
cursor, 'select username, password from staffs join users using(uid) '
'where username = %s and password = %s', (username, password))
if len(ret) != 0:
return {
'position': 'staff',
}
ret = select_query(
cursor,
'select username, password from managers join users using(uid) '
'where username = %s and password = %s', (username, password))
if len(ret) != 0:
return {
'position': 'manager',
}
return None
def view_monthly_order(cursor, username, month, year):
uid = select_query(cursor,
'select uid from users where username = %s;',
(username,))[0][0]
all_order = select_query(cursor,
'select count(distinct oid), sum(price), rName, location '
'from orders join contains using(oid) '
'join menu using(rid, fid) '
'join manages using(rid) '
'join restaurants using(rid) '
'where (select extract(month from orderTime)) = %s '
'and (select extract(year from orderTime)) = %s '
'group by (rName, location, uid) '
'having uid = %s;',
(month, year, uid))[0]
top_5 = select_query(cursor,
'select fName, count(*) '
'from orders join contains using(oid) '
'join menu using(rid, fid) '
'join restaurants using(rid) '
'join foodItems using(fid) '
'join manages using(rid) '
'where (select extract(month from orderTime)) = %s '
'and (select extract(year from orderTime)) = %s '
'and uid = %s '
'group by (fName) '
'order by count(*) desc '
'limit 5;',
(month, year, uid))
return {
'total orders': all_order[0],
'total cost': all_order[1],
'rName': all_order[2],
'location': all_order[3],
'favorite food': [
{
'fName': item[0],
'times': item[1]
} for item in top_5
]
}
def validatefield():
print(request)
field = request.args.get("field")
value = request.args.get("value")
print(value)
result = select_query(f"select {field} from users where {field}='{value}'")
if result:
print("Usuario/email ya registado")
return "Used"
else:
print("Usuario y email disponibles")
return "Available"
def part_time_summary(cursor, month, year):
data = select_query(cursor,
'with '
'RiderRating as '
'(select uid, count(*) as numRating, sum(rating) as totalRating, '
'(select extract(month from orderTime)) as month, '
'(select extract(year from orderTime)) as year '
'from delivers join parttimeriders using(uid) '
'join orders using(oid) '
'where rating is not null '
'group by uid, month, year), '
''
'RiderOrder as '
'(select uid, count(*) as numOrder, '
'(select extract(month from orderTime)) as month, '
'(select extract(year from orderTime)) as year '
'from delivers join parttimeriders using(uid) '
'join orders using(oid) '
'group by uid, month, year), '
''
'RiderWork as '
'(select uid, psalary, '
'sum(EXTRACT(HOUR FROM endTime) - EXTRACT(HOUR FROM startTime)) as totalTime '
'from parttimeriders join riders using(uid) '
'join weeklyworks using(uid) '
'join schedules using(sid) '
'group by uid, psalary), '
''
'RiderDeliver as '
'(select uid, sum(completeTime - departTime) as deliverTime '
'from delivers '
'group by (uid))'
''
'select uid, numRating, totalRating, numOrder, psalary, totalTime, deliverTime '
'from RiderRating right join RiderOrder '
'using(uid, month, year) '
'join RiderWork using(uid) '
'join RiderDeliver using(uid) '
'where month = %s and year = %s;',
(month, year,))
return [
{
'type': 'part time',
'uid': item[0],
'no. rating': (item[1] if item[1] else 0),
'average rating': item[2] / item[1] if item[1] else None,
'no. order': item[3],
'salary': item[4] * 4,
'total time': item[5],
'average deliver time': str(time.strftime('%H:%M:%S', time.gmtime(round((item[6] / item[3]).total_seconds()))))
}
for item in data
]
def customer_update(connection, cursor, username, card_number=None, cvv=None):
uid = select_query(cursor, 'select uid from users where username = %s',
(username, ))[0][0]
if card_number is None:
update_query(connection, cursor,
'update customers set cvv = %s where uid = %s;',
(cvv, uid))
elif cvv is None:
update_query(
connection, cursor,
'update customers set creditCardNumber = %s where uid = %s;',
(card_number, uid))
else:
update_query(
connection, cursor,
'update customers set creditCardNumber = %s, cvv = %s where uid = %s;',
(card_number, cvv, uid))
def get_menu(cursor, rName, rCategory, location, fName, fCategory):
if len(rName) == 0:
rName = tuple(select_query(cursor, 'select rName from restaurants'))
if len(rCategory) == 0:
rCategory = tuple(
select_query(cursor, 'select rCategory from restaurants'))
if len(location) == 0:
location = tuple(
select_query(cursor, 'select location from restaurants'))
if len(fName) == 0:
fName = tuple(select_query(cursor, 'select fName from foodItems'))
if len(fCategory) == 0:
fCategory = tuple(
select_query(cursor, 'select fCategory from foodItems'))
ret = select_query(
cursor, 'select * '
'from restaurants join menu using(rid) '
'join foodItems using(fid) '
'where '
'rName in %s '
'and rCategory in %s '
'and location in %s '
'and fName in %s '
'and fCategory in %s;', (
rName,
rCategory,
location,
fName,
fCategory,
))
query = list()
for item in ret:
query.append({
'rid': item[0],
'fid': item[1],
'rName': item[2],
'rCategory': item[3],
'location': item[4],
'minSpent': item[5],
'availability': item[6],
'noOfOrders': item[8],
'price': item[9],
'fName': item[10],
'fCategory': item[11]
})
return query
def signin():
if request.method != "POST":
return index()
else:
username = request.form.get("username").lower()
password = request.form.get("password")
result = select_query(
f"select email, password from users where username='******'")
print("Resultado de la query: " + str(result))
if result:
if check_password_hash(result[0][1], password):
# Registra la sesión
session["username"] = username
else:
print("password incorrecto")
else:
print("usuario no registrado")
return index()
def get_profile(cursor, username, phone, user_type, reward_points):
if len(phone) == 0:
phone = select_query(cursor,
'select phone from users where username = %s',
(username, ))[0][0]
customer_query = select_query(
cursor,
'select username from customers join users using(uid) where username = %s',
(username, ))
if len(customer_query) != 0:
user_type = 'customer'
rider_query = select_query(
cursor,
'select username from riders join users using(uid) where username = %s',
(username, ))
if len(rider_query) != 0:
user_type = 'rider'
staff_query = select_query(
cursor,
'select username from staffs join users using(uid) where username = %s',
(username, ))
if len(staff_query) != 0:
user_type = 'staff'
manager_query = select_query(
cursor,
'select username from managers join users using(uid) where username = %s',
(username, ))
if len(manager_query) != 0:
user_type = 'manager'
if user_type == 'customer' and len(reward_points) == 0:
reward_points = select_query(
cursor, 'select rewardPoints from customers join users using(uid) '
'where username = %s', (username, ))
return {
'userName': username,
'phone': phone,
'userType': user_type,
'rewardPoints': reward_points
}
return {'userName': username, 'phone': phone, 'userType': user_type}
def get_percentage(cursor, uid, startdate, enddate):
if enddate is None:
promotion = select_query(
cursor, 'with Temp as '
'(select pid, startDate, endDate, percent, maxAmount, fid, uid '
'from promotions join percentage using(pid) '
'join promotes p using(pid) '
'join manages m '
'on (p.rid is null or p.rid = m.rid) '
'where uid = %s) '
''
'select pid, startDate, endDate, percent, maxAmount, mana.rid, count(*), sum(price), max(orderTime), min(orderTime) '
'from Temp t, orders o, menu m, manages mana, contains c '
'where mana.uid = t.uid '
'and mana.rid = o.rid and '
'mana.rid = m.rid and '
'm.fid = c.fid and '
'o.oid = c.oid and '
'case '
'when t.fid is not null then c.fid = t.fid '
'else true '
'end '
'and date(orderTime) >= %s '
'group by (pid, startDate, endDate, percent, maxAmount, mana.rid);',
(uid, startdate))
else:
promotion = select_query(
cursor, 'with Temp as '
'(select pid, startDate, endDate, percent, maxAmount, fid, uid '
'from promotions join percentage using(pid) '
'join promotes p using(pid) '
'join manages m '
'on (p.rid is null or p.rid = m.rid) '
'where uid = %s) '
''
'select pid, startDate, endDate, percent, maxAmount, mana.rid, count(*), sum(price), max(orderTime), min(orderTime) '
'from Temp t, orders o, menu m, manages mana, contains c '
'where mana.uid = t.uid '
'and mana.rid = o.rid and '
'mana.rid = m.rid and '
'm.fid = c.fid and '
'o.oid = c.oid and '
'case '
'when t.fid is not null then c.fid = t.fid '
'else true '
'end '
'and date(orderTime) >= %s and date(orderTime) <= %s '
'group by (pid, startDate, endDate, percent, maxAmount, mana.rid);',
(uid, startdate, enddate))
return [{
'type': 'percentage promotion',
'pid': item[0],
'startDate': item[1].strftime('%d/%m/%Y'),
'endDate': item[2].strftime('%d/%m/%Y'),
'percentage': item[3],
'maxAmount': item[4],
'rid': item[5],
'no. of orders': item[6],
'orders per day': item[6] / ((item[8] - item[9]).days + 1),
'total price': item[7],
} for item in promotion]
def full_cycle_v2(processed_groups, all_):
"""Actually scans only 1 group, save parsed groups"""
buf_all_groups = list(all_)
i = 0
chosen_id = -1
while i < len(buf_all_groups):
try:
group_id = buf_all_groups[i]
if group_id not in processed_groups:
break
else:
buf_all_groups.pop(i)
except Exception as e:
log.error("Alert! Data error: {}. Raw: {}".format(
e, buf_all_groups))
try:
if chosen_id == -1:
group_id = buf_all_groups[0] # -- first group of leftover
else:
group_id = chosen_id
except Exception as e:
log.error("choose_id exception: {}, buf_all_groups: {}".format(
e, buf_all_groups))
return None
auth_token = None
ret = getA(group_id, auth_token, 0, 1)
# determine here if token is required
ret_keys = ret.keys()
if "count" in ret_keys:
count = ret["count"]
elif "code" in ret_keys:
log.info("refreshing access_token...")
user_id = select_query(
"select g.`user_id` from `groups` g where g.`group_id`=%s order by g.`added` desc",
(group_id, ))[0][0]
auth_token = select_query(
"select u.`auth_token` from `userinfo` u where u.`user_id`=%s",
(user_id, ))[0][0]
ret = getA(group_id, auth_token, 0, 1)
if "count" in ret.keys():
count = int(ret["count"])
else:
log.error("sorry... error_code={0}, message: {1}".format(
ret["code"], ret["message"]))
log.info(ret)
return group_id
else:
return group_id
log.info("--OK")
update_query("update groups set is_old=1 where group_id=%s", (group_id, ))
update_query("delete from postinfo where group_id=%s", (group_id, ))
screen_name = select_query(
"SELECT g.`screen_name` FROM `groups` g WHERE g.`group_id`=%s",
(group_id, ))[0][0]
log.info("screen_name: {}, group_id: {}, nums to parse: {}".format(
screen_name, group_id, str(count)))
if count > 30000 or count == 0:
update_query("delete from groups where group_id=%s", (group_id, ))
log.info("removing this group from queue")
return group_id
try:
req = "https://api.vk.com/method/groups.getById?group_id={0}".format(
group_id)
written_name = short_value(
requests.get(req).json()["response"][0]["name"].replace(
"&", "&"), 35)
except Exception as ex:
log.error("no written_name: {0}".format(ex))
written_name = short_value(screen_name, 35)
print "name: {}".format(written_name)
# UPDATE GROUP-INFO IN THE STATS
try:
with open(os.path.join(os.getcwd(), "statistics.txt"), "r") as f:
data = json.loads(f.read())
except:
data = {}
data["name"] = written_name
data["count"] = count
data["group_id"] = group_id
data["totalgroups"] = len(all_)
with open(os.path.join(os.getcwd(), "statistics.txt"), "w") as f:
f.write(json.dumps(data))
# LOAD AND PROCESS VK-POSTS
offset = count // 100 + 1
for i in range(0, offset):
posts = getA(group_id, auth_token, i * 100, 100)
for post in posts["items"]:
try:
link = "http://vk.com/{0}?w=wall-{1}_{2}".format(
screen_name, group_id, post["id"])
try:
comm = post["comments"]["count"]
except:
comm = 0
try:
like = post["likes"]["count"]
except:
like = 0
try:
repo = post["reposts"]["count"]
except:
repo = 0
try:
picture = None
if "attachments" in post.keys():
a_type = post["attachments"][0]["type"]
if a_type in ["photo", "video"]:
picture = post["attachments"][0][a_type][
"photo_130"]
except Exception as ex:
picture = None
update_query(
"insert into `postinfo` (`group_id`, `link`, `like`, `comm`, `repo`, `picture`) values (%s, %s, %s, %s, %s, %s)",
(group_id, link, like, comm, repo, picture))
except BaseException as ex:
log.error(
"link error: {}, full post: {}, full response: {}".format(
ex, post, posts))
# UPDATE POSTs-INFO IN THE STATS
with open(os.path.join(os.getcwd(), "statistics.txt"), "r") as f:
data = json.loads(f.read())
data["count"] -= len(posts["items"])
if data["count"] < 0:
data["count"] = 0
with open(os.path.join(os.getcwd(), "statistics.txt"), "w") as f:
f.write(json.dumps(data))
time.sleep(0.05)
# now load datas back and save only best 300
limit = 100 # x3 = 300
if count > limit * 3.05:
ts = time.time()
bestlikes = select_query(
"select p.`group_id`, p.`link`, p.`like`, p.`comm`, p.`repo`, p.`picture` from `postinfo` p where p.`group_id`=%s order by p.`like` desc limit %s",
(group_id, limit))
bestrepos = select_query(
"select p.`group_id`, p.`link`, p.`like`, p.`comm`, p.`repo`, p.`picture` from `postinfo` p where p.`group_id`=%s order by p.`repo` desc limit %s",
(group_id, limit))
bestcomms = select_query(
"select p.`group_id`, p.`link`, p.`like`, p.`comm`, p.`repo`, p.`picture` from `postinfo` p where p.`group_id`=%s order by p.`comm` desc limit %s",
(group_id, limit))
update_query("delete from postinfo where group_id=%s", (group_id, ))
toti = 0
bestdatas = []
append = bestdatas.append
for post in bestlikes + bestrepos + bestcomms:
if post not in bestdatas:
toti += 1
append(post)
update_query(
"INSERT INTO `postinfo` (`group_id`, `link`, `like`, `comm`, `repo`, `picture`) VALUES (%s, %s, %s, %s, %s, %s)",
(post[0], post[1], post[2], post[3], post[4], post[5]))
time.sleep(0.03)
log.info("{} seconds to re save {} posts".format(
int(time.time() - ts), toti))
else:
log.info("no re-save needed")
return group_id
def summary(cursor, username, month, year):
uid = select_query(cursor, 'select uid from users where username = %s;',
(username, ))[0][0]
is_full_time = len(
select_query(cursor, 'select 1 '
'from fulltimeriders '
'where uid = %s;', (uid, ))) > 0
if is_full_time:
data = select_query(
cursor, 'with '
'RiderRating as '
'(select uid, count(*) as numRating, sum(rating) as totalRating, '
'(select extract(month from orderTime)) as month, '
'(select extract(year from orderTime)) as year '
'from delivers join fulltimeriders using(uid) '
'join orders using(oid) '
'where rating is not null '
'group by uid, month, year), '
''
'RiderOrder as '
'(select uid, count(*) as numOrder, '
'(select extract(month from orderTime)) as month, '
'(select extract(year from orderTime)) as year '
'from delivers join fulltimeriders using(uid) '
'join orders using(oid) '
'group by uid, month, year), '
''
'RiderWork as '
'(select uid, fsalary, '
'sum(EXTRACT(HOUR FROM endTime) - EXTRACT(HOUR FROM startTime)) as totalTime '
'from fulltimeriders join riders using(uid) '
'join monthlyworks using(uid) '
'join schedules using(sid) '
'group by uid, fsalary), '
''
'RiderDeliver as '
'(select uid, sum(completeTime - departTime) as deliverTime '
'from delivers '
'group by (uid))'
''
'select uid, numRating, totalRating, numOrder, fsalary, totalTime, deliverTime '
'from RiderRating right join RiderOrder '
'using(uid, month, year) '
'join RiderWork using(uid) '
'join RiderDeliver using(uid) '
'where month = %s and year = %s and uid = %s;',
(month, year, uid))[0]
return {
'type':
'full time',
'uid':
data[0],
'no. rating': (data[1] if data[1] else 0),
'average rating':
data[2] / data[1] if data[1] else None,
'no. order':
data[3],
'salary':
data[4] * 4,
'total time':
data[5] * 4,
'average deliver time':
str(
time.strftime(
'%H:%M:%S',
time.gmtime(round((data[6] / data[3]).total_seconds()))))
}
else:
data = select_query(
cursor, 'with '
'RiderRating as '
'(select uid, count(*) as numRating, sum(rating) as totalRating, '
'(select extract(month from orderTime)) as month, '
'(select extract(year from orderTime)) as year '
'from delivers join parttimeriders using(uid) '
'join orders using(oid) '
'where rating is not null '
'group by uid, month, year), '
''
'RiderOrder as '
'(select uid, count(*) as numOrder, '
'(select extract(month from orderTime)) as month, '
'(select extract(year from orderTime)) as year '
'from delivers join parttimeriders using(uid) '
'join orders using(oid) '
'group by uid, month, year), '
''
'RiderWork as '
'(select uid, psalary, '
'sum(EXTRACT(HOUR FROM endTime) - EXTRACT(HOUR FROM startTime)) as totalTime '
'from parttimeriders join riders using(uid) '
'join weeklyworks using(uid) '
'join schedules using(sid) '
'group by uid, psalary), '
''
'RiderDeliver as '
'(select uid, sum(completeTime - departTime) as deliverTime '
'from delivers '
'group by (uid))'
''
'select uid, numRating, totalRating, numOrder, psalary, totalTime, deliverTime '
'from RiderRating right join RiderOrder '
'using(uid, month, year) '
'join RiderWork using(uid) '
'join RiderDeliver using(uid) '
'where month = %s and year = %s and uid = %s;',
(month, year, uid))[0]
return {
'type':
'part time',
'uid':
data[0],
'no. rating': (data[1] if data[1] else 0),
'average rating':
data[2] / data[1] if data[1] else None,
'no. order':
data[3],
'salary':
data[4] * 4,
'total time':
data[5],
'average deliver time':
str(
time.strftime(
'%H:%M:%S',
time.gmtime(round((data[6] / data[3]).total_seconds()))))
}
def parse_vk_responce():
code = request.args.get('code')
if code:
try:
# render link and get auth_token, user_id
url = "https://oauth.vk.com/access_token?client_id={}&client_secret={}&code={}&redirect_uri=http://vksmm.info{}".format(
CLIENT_ID, CLIENT_SECRET, code, url_for('parse_vk_responce'))
res = requests.get(url).json()
user_id = res["user_id"]
access_token = res["access_token"]
# LOAD OLD SORTING
res = select_query(
"SELECT u.`sort_type` FROM `userinfo` u WHERE u.`user_id`=%s",
(user_id, ))
try:
sort_type = res[0][0]
if sort_type not in ['like', 'repo', 'comm']:
sort_type = 'like'
except:
sort_type = 'like'
# LOAD USER-DATA
try:
url = "https://api.vk.com/method/execute.name_pic?access_token={0}&id={1}".format(
access_token, user_id)
response = requests.get(url).json()["response"]
username = response["name"]
picture = response["picture"] # avatar 100px
except Exception as e:
log.error("load user-datas: {}".format(e))
username = "******"
picture = None
log.info("+ {} online".format(username))
# delete old personal data, save new
# WTF???
update_query(
"DELETE FROM `userinfo` WHERE `userinfo`.`user_id`=%s",
(user_id, ))
update_query("DELETE FROM `groups` where `groups`.`user_id`=%s",
(user_id, ))
update_query(
"INSERT INTO `userinfo` (`user_id`, `auth_token`, `sort_type`, `last_seen`, `username`, `picture`) VALUES (%s, %s, %s, %s, %s, %s)",
(user_id, access_token, sort_type, datetime.now(), username,
picture))
try:
# load fresh groups from account;
req = "https://api.vk.com/method/execute.get_all_groups?access_token={}".format(
access_token)
buf = requests.get(req).json()["response"]
len_buf = len(buf)
limit = 100
steps = len_buf // limit + 1
for st in range(steps):
offset = st * limit
i = offset
group_ids = ""
while i < offset + limit and i < len_buf:
group_ids += "{},".format(buf[i])
i += 1
req = "https://api.vk.com/method/groups.getById?group_ids={0}".format(
group_ids[:-1])
groups = requests.get(req)
groups_json = groups.json()["response"]
for item in groups_json:
group_name = wrap_value(item["name"])
try:
update_query(
"INSERT INTO `groups` (`user_id`, `group_id`, `screen_name`, `picture`, `added`, `is_old`, `groupname`) VALUES (%s, %s, %s, %s, %s, 0, %s)",
(int(user_id), int(item["gid"]),
item["screen_name"], item["photo_medium"],
int(time.time()), group_name))
except Exception as e:
log.error(repr(e))
except Exception:
log.error(format_exception())
return redirect(
url_for('index_page',
user_id=user_id,
access_token=access_token))
except Exception:
log.error("/vk_login err:\n{}".format(format_exception()))
return redirect(url_for('landing_page')) # add an error-message here ?
def index_page():
try:
user_id = int(request.args.get('user_id'))
except:
return redirect(
url_for('landing_page')
) # add an error-message here ? "'user_id' error: int expected"
try:
offset = int(request.args.get('offset'))
except:
offset = 0
sort_type = request.args.get('sort_type')
access_token = request.args.get('access_token')
if user_id and access_token:
try:
groups = select_query(
"SELECT g.`group_id`, g.`groupname`, g.`picture` FROM `groups` g WHERE g.`user_id`=%s",
(user_id, ))
try:
group_id = int(request.args.get('group_id'))
except:
group_id = groups[0][0]
current_group_name = None
current_group_picture = None
group_list = []
append = group_list.append
for item in groups:
buf_group_name = short_value(unwrap_value(item[1]), 30)
append([item[0], buf_group_name, item[2]])
if int(item[0]) == int(group_id):
current_group_name = unwrap_value(item[1])
current_group_picture = item[2]
if not current_group_name and not current_group_picture:
# then load from vk
req = "https://api.vk.com/method/groups.getById?group_ids={0}".format(
group_id)
other_group_data = requests.get(req).json()["response"][0]
current_group_name = unwrap_value(other_group_data["name"])
current_group_picture = other_group_data["photo_medium"]
# UPDATE SORTING
if sort_type in ('like', 'repo', 'comm'):
update_query(
"UPDATE `userinfo` SET last_seen = %s, sort_type = %s WHERE user_id = %s",
(datetime.now(), sort_type, user_id))
else:
update_query(
"UPDATE `userinfo` SET last_seen = %s WHERE user_id=%s",
(datetime.now(), user_id))
res = select_query(
"SELECT u.`sort_type` FROM `userinfo` u WHERE u.`user_id`=%s",
(user_id, ))
sort_type = res[0][0]
try:
w = int(request.args.get('w'))
h = int(request.args.get('h'))
except:
user_ip = request.remote_addr
sizes = select_query(
"SELECT s.`w`, s.`h` FROM `screen_size` s WHERE s.`user_ip`=%s",
(user_ip, ))
w, h = sizes[0]
log.debug("width = {0}, height = {1}; user_ip = {2}".format(
w, h, user_ip))
try:
cols = int((w * 0.8 - 235) / 125) # x
rows = int((h - 120.0) / 120) # y
count = rows * cols
except:
count = 35
posts = select_query(
"SELECT p.`like`, p.`repo`, p.`comm`, p.`link`, p.`picture` FROM `postinfo` p WHERE p.`group_id`=%s ORDER BY %s DESC LIMIT %s OFFSET %s",
(group_id, sort_type, count, offset * count))
if posts:
recommendation = None
else:
max_range = select_query(
"SELECT COUNT(g.*) FROM `groups` g WHERE g.`is_old`=1"
)[0][0]
try:
rlimit = int((h - 300) / 36.0)
if rlimit > max_range:
log.debug("big screen :)")
rlimit = max_range - 1
except Exception as e:
log.error("rlimit e:", e)
rlimit = 13
roffset = int((max_range - rlimit) * random()) + 1
groups = select_query(
"SELECT g.`group_id`, g.`groupname`, g.`picture` FROM `groups` g WHERE g.`is_old`=1 ORDER BY g.`group_id` ASC LIMIT %s OFFSER %s",
(rlimit, roffset))
recommendation = []
append = recommendation.append
for item in groups:
try:
buf_group_name = short_value(unwrap_value(item[1]), 50)
if [item[0], buf_group_name,
item[2]] not in recommendation:
append([item[0], buf_group_name, item[2]])
except Exception:
log.error(traceback.print_exc())
# PAGE-NAVIGATION LINKS
offset_prev = None
if offset > 0:
offset_prev = url_for(
'index_page'
) + "?user_id={0}&access_token={1}&group_id={2}&offset={3}".format(
user_id, access_token, group_id, offset - 1)
offset_next = None
count_postinfo = select_query(
"SELECT COUNT(p.*) FROM `postinfo` p WHERE p.`group_id`=%s",
(group_id, ))[0][0]
if count * (offset + 1) < count_postinfo:
offset_next = url_for(
'index_page'
) + "?user_id={0}&access_token={1}&group_id={2}&offset={3}".format(
user_id, access_token, group_id, offset + 1)
base_link = url_for(
'index_page'
) + "?user_id={0}&access_token={1}&group_id={2}&offset={3}&sort_type=".format(
user_id, access_token, group_id, offset)
# LOAD USER DATA
user_name, avatar = select_query(
"SELECT u.`username`, u.`picture` FROM `userinfo` u WHERE u.`user_id`=%s",
(user_id, ))[0]
# LOAD STATS
try:
with open("statistics.txt", "r") as f:
stats = json.loads(f.read())
except:
stats = None
return render_template("index.html",
group_list=group_list,
posts=posts,
user_id=user_id,
user_name=user_name,
avatar=avatar,
access_token=access_token,
current_group_name=current_group_name,
current_group_picture=current_group_picture,
offset_prev=offset_prev,
offset_next=offset_next,
offset=offset,
base_link=base_link,
stats=stats,
group_id=group_id,
count_postinfo=count_postinfo,
sort_type=sort_type,
recomendation=recommendation)
except Exception:
log.error("Exception at index_page:\n{}".format(
format_exception()))
return redirect(url_for('landing_page'))
def get_food(cursor, string):
all_food = select_query(cursor,
"""select fName from foodItems where
lower(fName) like '%%' || %s || '%%';""",
(string,))
return [item[0] for item in all_food]
def summary(cursor, startdate, enddate, username):
uid = select_query(cursor, 'select uid from users where username = %s;',
(username, ))[0][0]
flat_promotion = get_flat(cursor, uid, startdate, enddate)
percentage_promotion = get_percentage(cursor, uid, startdate, enddate)
return flat_promotion + percentage_promotion
def checkout(connection, cursor, username, rid, fid, quantity, ptype, pid, location):
availability = select_query(cursor,
'select availability '
'from menu '
'where rid = %s and fid in %s;',
(rid, fid))[0][0]
if not availability:
raise Exception('This item is not available')
now = datetime.now()
oid = select_query(cursor,
'select count(*) + 1 from orders;')[0][0]
update_query(connection, cursor,
'insert into orders (oid, orderTime, rid, cid) '
'values '
'('
'%s, %s, %s, '
'(select uid from users where username = %s)'
');',
(oid, now, rid, username))
for i in range(len(fid)):
update_query(connection, cursor,
'insert into contains (oid, fid, quantity) '
'values '
'(%s, %s, %s);', (oid, fid[i], quantity[i]))
update_query(connection, cursor,
'update menu m '
'set noOfOrders = noOfOrders + '
'(select quantity '
'from contains c '
'where c.fid = %s and oid = %s'
') '
'where rid = %s and fid = %s;',
(fid[i], oid, rid, fid[i]))
total_price = select_query(cursor,
'select sum(price*quantity) as totalPrice '
'from contains join orders using(oid) '
'join menu using(rid, fid) '
'where oid = %s', (oid,))[0][0]
min_spent = select_query(cursor,
'select minSpent '
'from restaurants join orders using(rid) '
'where oid = %s;', (oid,))[0][0]
if total_price < min_spent:
raise Exception('Minimum spent is not met')
discount = 0
if ptype == 'flat':
flat_promo = select_query(cursor,
'select distinct flatAmount '
'from flat join promotes using(pid) '
'where pid = %s '
'and (fid is null or fid in %s) '
'and (rid is null or rid = %s) '
'and minAmount <= %s;', (pid, fid, rid, total_price))
if len(flat_promo) > 0:
discount = flat_promo[0][0]
elif ptype == 'percentage':
percent_promo = select_query(cursor,
'select percent, quantity, price, maxAmount '
'from percentage join promotes pr using(pid) '
'join contains c on('
'pr.fid is null or c.fid = pr.fid) '
'join menu m on('
'pr.rid is null or m.rid = pr.rid) '
'where pid = %s '
'and c.fid in %s '
'and m.rid = %s;', (pid, fid, rid))
if len(percent_promo) > 0:
discount = sum(item[0] * item[1] * item[2] for item in percent_promo)
discount = min(discount, percent_promo[0][3])
update_query(connection, cursor,
'update customers '
'set rewardPoints = rewardPoints + round(%s) '
'where uid = ('
'select uid '
'from customers join users using(uid) '
'where username = %s);',
(total_price - discount, username))
part_time = select_query(cursor,
'select w.uid '
'from weeklyworks w join schedules using(sid) '
'where startTime <= %s and endTime >= %s '
'and dayOfWeek = %s '
'and not exists ('
'select 1 '
'from delivers d '
'where (completeTime is null or completeTime > %s) and d.uid = w.uid'
');',
(now.strftime('%H:%M'), now.strftime('%H:%M'), convert_date(now.strftime("%A")), now))
full_time = select_query(cursor,
'select uid '
'from monthlyworks w join schedules using(sid) '
'where startTime <= %s and endTime >= %s '
'and dayOfWeek = %s '
'and not exists ('
'select 1 '
'from delivers d '
'where (completeTime is null or completeTime > %s) and d.uid = w.uid'
');',
(now.strftime('%H:%M'), now.strftime('%H:%M'), convert_date(now.strftime("%A")), now))
rider = (part_time + full_time)[0][0]
update_query(connection, cursor,
'insert into delivers (uid, oid, startTime, deliverCost, location) '
'values (%s, %s, %s, 10, %s);',
(rider, oid, now, location))
return {
'oid': oid,
'food price': total_price,
'discount': discount,
'deliver cost': 10,
'total price': total_price - discount + 10,
'rider': rider
}
