#!/usr/bin/env python """ This module provides routines to calculate rating of users in tripster.ru project. Main publick functions: calc_all_cities_rating() calc_cities_per_continent_rating() calc_cities_per_country_rating() calc_all_countries_rating() calc_countries_per_continent_rating() """ from settings import settings from lib.models import engine import types_ cursor = engine.connect().connection.cursor() def userid_sql(userid): """ Build sql expression for where clause. """ if userid is None: where = '' else: where = ' AND user = %d' % userid return where def clear_rating(type, userid=None): """ Delete all records with given type from ratings table. """ cursor = engine.connect().connection.cursor() where = userid_sql(userid) cursor.execute("""DELETE FROM ratings WHERE type = %%s %s""" % where, (type)) def insert_rating(user_id, position, count, type, object=None): """ Create record in ratings table. """ cursor.execute("""INSERT INTO ratings SET user = %s, position = %s, count = %s, type= %s, object=%s""", (user_id, position, count, type, object)) def calc_all_cities_rating(userid=None): """ Calculate rating of visited cities for each user. """ cursor.execute("""SELECT u.id, COUNT(*) as count FROM users u LEFT JOIN cities_in_users ciu ON ciu.user = u.id WHERE u.fraud_user = 0 GROUP BY u.id ORDER BY count DESC""") clear_rating(types_.RATING_ALL_CITIES, userid) for position, row in enumerate(cursor.fetchall()): iter_userid, count = row if userid and userid != iter_userid: continue insert_rating(iter_userid, position + 1, count, types_.RATING_ALL_CITIES) def calc_cities_per_continent_rating(userid=None): """ Calculate rating of visited cities on each continent for each user """ cursor.execute("""SELECT u.id, countries.continent, COUNT(*) AS count FROM users u LEFT JOIN cities_in_users ciu ON ciu.user = u.id LEFT JOIN cities ON cities.id = ciu.city LEFT JOIN countries ON countries.id = cities.country WHERE countries.continent != 'RU' AND u.fraud_user = 0 GROUP BY u.id, countries.continent ORDER BY count DESC""") clear_rating(types_.RATING_CITIES_PER_CONTINENT) for position, row in enumerate(cursor.fetchall()): iter_userid, continent, count = row if userid and userid != iter_userid: continue insert_rating(iter_userid, position + 1, count, types_.RATING_CITIES_PER_CONTINENT, continent) def calc_cities_per_country_rating(userid=None): """ Calculate rating of visited cities in each country for each user """ cursor.execute("""SELECT u.id, countries.id, COUNT(*) AS count FROM users u LEFT JOIN cities_in_users ciu ON ciu.user = u.id LEFT JOIN cities ON cities.id = ciu.city LEFT JOIN countries ON countries.id = cities.country WHERE u.fraud_user = 0 GROUP BY u.id, countries.id ORDER BY count DESC""") clear_rating(types_.RATING_CITIES_PER_COUNTRY) for position, row in enumerate(cursor.fetchall()): iter_userid, country, count = row if userid and userid != iter_userid: continue insert_rating(iter_userid, position + 1, count, types_.RATING_CITIES_PER_COUNTRY, country) def calc_all_countries_rating(userid=None): """ Calculate rating of visited countries for each user. """ cursor.execute("""SELECT u.id, COUNT(DISTINCT countries.id) as count FROM users u LEFT JOIN cities_in_users ciu ON ciu.user = u.id LEFT JOIN cities ON cities.id = ciu.city LEFT JOIN countries ON countries.id = cities.country WHERE u.fraud_user = 0 GROUP BY u.id ORDER BY count DESC""") clear_rating(types_.RATING_ALL_COUNTRIES) for position, row in enumerate(cursor.fetchall()): iter_userid, count = row if userid and userid != iter_userid: continue insert_rating(iter_userid, position + 1, count, types_.RATING_ALL_COUNTRIES) def calc_countries_per_continent_rating(userid=None): """ Calculate rating of visited countries on each continent for each user """ cursor.execute("""SELECT u.id, countries.continent, COUNT(DISTINCT countries.id) AS count FROM users u LEFT JOIN cities_in_users ciu ON ciu.user = u.id LEFT JOIN cities ON cities.id = ciu.city LEFT JOIN countries ON countries.id = cities.country WHERE u.fraud_user = 0 GROUP BY u.id, countries.continent ORDER BY count DESC""") clear_rating(types_.RATING_COUNTRIES_PER_CONTINENT) for position, row in enumerate(cursor.fetchall()): iter_userid, continent, count = row if userid and userid != iter_userid: continue insert_rating(iter_userid, position + 1, count, types_.RATING_COUNTRIES_PER_CONTINENT, continent) def update(login=None): """ Rebuild all ratings. """ if login is None: userid = None else: cursor.execute("SELECT id FROM users WHERE login = %s", [login]) userid = cursor.fetchone()[0] print 'Cities rating' calc_all_cities_rating(userid) print 'Cities per continent rating' calc_cities_per_continent_rating(userid) print 'Cities per country rating' calc_cities_per_country_rating(userid) print 'Countries rating' calc_all_countries_rating(userid) print 'Countries per continet rating' calc_countries_per_continent_rating(userid) cursor.execute("COMMIT") if __name__ == '__main__': print 'Calculating ratings...' update()