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 }