def get_rests_by_kw(cls, keyword, kw_country, country):
connection = mysql.connect()
cursor = connection.cursor()
print keyword
print kw_country
print country
if not country:
country = kw_country
sql = "select * from CountryRating where country=%s"
cursor.execute(sql, (country,))
data = cursor.fetchall()
if not data:
sql = "select * from Rest where Rest.city='{0}' or Rest.state='{0}' or Rest.country='{0}' order by rating desc".format(keyword)
else:
sql = "select * from Rest inner join CountryRating on Rest.restid=CountryRating.restid where (Rest.city='{0}' or Rest.state='{0}' or Rest.country='{0}') and CountryRating.country='{1}' order by CountryRating.rating desc".format(keyword, country)
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(*t[:12]) for t in data] if data else []
def update_rating(cls, rating, restid):
connection = mysql.connect()
cursor = connection.cursor()
sql = "update Rest set rating=%s where restid=%s"
cursor.execute(sql, (rating, restid))
connection.commit()
cursor.close()
connection.close()
def insert_course(cls, menuid, course_name, course_content):
connection = mysql.connect()
cursor = connection.cursor()
sql = "insert into Course(menuid, course_name, course_content) values(%s, %s, %s)"
cursor.execute(sql, (menuid, course_name, course_content))
connection.commit()
cursor.close()
connection.close()
def get_rest_object(cls, restid):
connection = mysql.connect()
cursor = connection.cursor()
sql = "select * from Rest where restid=%s"
cursor.execute(sql,(restid,))
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(*t) for t in data] if data else []
def get_rest_reviews(cls, restid):
connection = mysql.connect()
cursor = connection.cursor()
sql = "select Review.revid, Review.rating,Review.country, Review.content, Review.created, Review.lastupdated from Review inner join ReviewContain on Review.revid=ReviewContain.revid where ReviewContain.restid=%s order by Review.lastupdated desc"
cursor.execute(sql, (restid,))
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(t[0], t[1], t[2].title(), t[3], t[4], t[5]) for t in data] if data else []
def get_courses(cls, revid):
connection = mysql.connect()
cursor = connection.cursor()
sql = "select Course.course_name, Course.course_content from Course inner join Menu where Menu.menuid=Course.menuid and Menu.revid=%s"
cursor.execute(sql, (revid,))
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(*t) for t in data] if data else None
def get_country_rating(cls, restid):
connection = mysql.connect()
cursor = connection.cursor()
sql = "select * from CountryRating where restid=%s"
cursor.execute(sql, (restid,))
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(t[0], t[1], t[2].title()) for t in data] if data else None
def get_all_by_kw(cls, keyword):
connection = mysql.connect()
cursor = connection.cursor()
sql = "select * from Rest where Rest.city='{0}' or Rest.state='{0}' or Rest.country='{0}' order by rating desc".format(keyword)
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(*t) for t in data] if data else []
def get_rest_pics(cls, restid):
connection = mysql.connect()
cursor = connection.cursor()
sql = "select Photo.picid, Photo.url, Photo.created from Photo inner join PhotoContain on Photo.picid=PhotoContain.picid where PhotoContain.restid=%s order by PhotoContain.sequencenum asc"
cursor.execute(sql, (restid,))
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(*t) for t in data] if data else []
def get_rests_by_distance(cls, keyword, latitude, longitude):
connection = mysql.connect()
cursor = connection.cursor()
print keyword
print latitude
print longitude
sql = "select Rest.restid, Rest.restname, Rest.picurl, Rest.resttag, Rest.description, Rest.rating, Rest.address, Rest.city, Rest.state, Rest.country, Rest.created, Rest.lastupdated, 3956 * ACOS(COS(RADIANS({1})) * COS(RADIANS(RestGeo.latitude)) * COS(RADIANS({2}) - RADIANS(RestGeo.longitude)) + SIN(RADIANS({1})) * SIN(RADIANS(RestGeo.latitude))) AS distance from Rest inner join RestGeo on Rest.restid=RestGeo.restid where Rest.city='{0}' or Rest.state='{0}' or Rest.country='{0}' order by distance asc".format(keyword, latitude, longitude)
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
connection.close()
return [cls(*t[:12]) for t in data] if data else []
def insert_menu(cls, restid, revid):
connection = mysql.connect()
cursor = connection.cursor()
sql = "insert into Menu(restid, revid) values (%s, %s)"
cursor.execute(sql, (restid, revid))
connection.commit()
cursor.execute("select menuid from Menu order by menuid desc")
menuid = cursor.fetchall()
menuid = menuid[0][0]
cursor.close()
connection.close()
return menuid
def __init__(self):
from handlers import urls
from libs.handler import DefaultHandler
db = mysql.connect(
options.db_host, options.db_database,
options.db_user, options.db_passwd
)
settings = {
'static_path': join_path(__file__, 'static'),
'template_path': join_path(__file__, 'templates'),
'cookie_secret': options.cookie_secret,
'xsrf_cookies': options.xsrf_cookies,
'login_url': options.login_url,
'debug': options.debug,
'default_handler_class': DefaultHandler,
'db': db,
'rds': rds.Rds(),
'msg_producer': MessageProducter(),
}
tornado.web.Application.__init__(self, urls, **settings)
def __init__(self):
from handlers import urls
from libs.handler import DefaultHandler
db = mysql.connect(
options.db_host, options.db_database,
options.db_user, options.db_passwd
)
settings = {
'static_path': join_path(__file__, 'static'),
'template_path': join_path(__file__, 'templates'),
'cookie_secret': options.cookie_secret,
'xsrf_cookies': options.xsrf_cookies,
'login_url': options.login_url,
'debug': options.debug,
'default_handler_class': DefaultHandler,
'db': db,
'rds': rds.Rds(),
'msg_producer': MessageProducter(),
}
tornado.web.Application.__init__(self, urls, **settings)
def insert(cls, restid, rating, content, country):
country = country.lower()
connection = mysql.connect()
cursor = connection.cursor()
sql = "insert into Review(rating, content, country) values (%s, %s, %s)"
cursor.execute(sql, (rating, content, country))
connection.commit()
cursor.execute("select revid from Review order by revid desc")
revid = cursor.fetchall()
revid = revid[0][0]
cursor.execute("select sequencenum from ReviewContain where restid=%s order by sequencenum desc"%restid)
sn = cursor.fetchall()
sn = sn[0][0]+1 if sn else 1
sql = "insert into ReviewContain(restid, revid, sequencenum) values (%s, %s, %s)"
cursor.execute(sql, (restid, revid, sn))
connection.commit()
sql = "select rating from CountryRating where restid=%s and country=%s"
cursor.execute(sql, (restid, country))
data = cursor.fetchall()
if not data:
rating_new = rating
sql = "insert into CountryRating(rating, restid, country) values (%s, %s, %s)"
else:
rating_new = (float(data[0][0]) + float(rating))*0.5
sql = "update CountryRating set rating=%s where restid=%s and country=%s"
cursor.execute(sql, (rating_new, restid, country))
connection.commit()
cursor.close()
connection.close()
return revid
