Beispiel #1
0
    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 []
Beispiel #2
0
    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()
Beispiel #3
0
    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()
Beispiel #4
0
    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 []
Beispiel #5
0
    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 []
Beispiel #6
0
    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
Beispiel #7
0
    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
Beispiel #8
0
    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 []
Beispiel #9
0
    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 []
Beispiel #10
0
    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 []
Beispiel #11
0
    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
Beispiel #12
0
    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)
Beispiel #13
0
    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)
Beispiel #14
0
    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