#!/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()