def modify_userMail(userId, newUserMail):
    try:
        db.query("update users set mail=$um where userId=$uId", vars={"um": newUserMail, "uId": userId})
        return get_user(userId)
    except Exception, e:
        web.debug("users.modify_userMail: failed in modify database", e)
        return None

def modify_userName(userId, newUserName):
    try:
        db.query("update users set userName=$nun where userId=$uId", vars={"uId": userId, "nun": newUserName})
        return get_user(userId)
    except Exception, e:
        web.debug("users.modify_userName: failed in modify database", e)
        return None

 def GET(self, zipcode, name):
     names = name.lower().replace('_', ' ').split(' ')
     if len(names) > 1: name = names[-1]+', '+' '.join(names[:-1])
     else: name = names[0]
     candidates = list(db.query("""SELECT count(*) AS how_many, 
         sum(amount) AS how_much, p.firstname, p.lastname, 
         cm.name AS committee, cm.id as committee_id, occupation, 
         employer_stem, employer, p.id as polid ,
         min(cn.sent) as from_date, max(cn.sent) as to_date 
         FROM contribution cn, committee cm, politician_fec_ids pfi, 
         politician p WHERE cn.recipient_id = cm.id 
         AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id 
         AND lower(cn.name) = $name AND cn.zip = $zipcode 
         GROUP BY cm.id, cm.name, p.lastname, p.firstname, cn.occupation, 
         cn.employer_stem, cn.employer, p.id ORDER BY lower(cn.employer_stem), 
         lower(occupation), to_date DESC, how_much DESC""", vars=locals()))
     committees = list(db.query("""SELECT count(*) AS how_many, 
         sum(amount) AS how_much, cm.name, cm.id, occupation, 
         employer_stem, employer, max(cn.sent) as to_date, min(cn.sent) as from_date 
         FROM contribution cn, committee cm WHERE cn.recipient_id = cm.id 
         AND lower(cn.name) = $name AND cn.zip = $zipcode 
         GROUP BY cm.id, cm.name, cn.occupation, cn.employer_stem, cn.employer
         ORDER BY lower(cn.employer_stem), 
         lower(occupation), to_date DESC, how_much DESC""", vars=locals()))
     return render.contributor(candidates, committees, zipcode, name)

    def process_item(self, item, spider):
        if item.get('song_name') is None:
            # 分页完
            raise DropItem('ajax page over.')
        singer = db.query(
            Singer.pk).filter_by(face=item['singer_face']).first()
        if singer is None:
            singer = Singer(name=item['singer'], face=item['singer_face'])
            db.add(singer)

        album_name = item.get('album_name')
        if album_name is not None:
            cover = item.get('album_cover')
            album = db.query(Album.pk).filter_by(cover=cover).first()
            if album is None:
                album = Album(
                    name=album_name,
                    intro=item.get('album_intro'),
                    rdt=item['release_date'],
                    cover=cover)
                db.add(album)
        else:
            album = Empty()

        db.commit()

        lrc = item.get('lrc')
        song = db.query(Song).filter_by(
            name=item['song_name'], singer=singer.pk).first()
        if song is None:
            song = Song(
                name=item['song_name'],
                singer=singer.pk,
                album=album.pk,
                lrc=lrc)
            db.add(song)
            db.commit()
        elif None not in (lrc, song.lrc):
            song.lrc = lrc

        tag_objs = []
        for tag in item['tags']:
            t = db.query(Tag.pk).filter_by(name=tag).first()
            if t is None:
                t = Tag(name=tag)
                db.add(t)
            tag_objs.append(t)
        db.commit()

        for tag in tag_objs:
            db.merge(SongTag(sid=song.pk, tid=tag.pk))
        db.commit()

        return item

    def setUp(self):
        db.query(self.model_class).delete()
        db.commit()

        for x in range(5):
            p = self.model_class(
                    username=u'rodrigocesar.savian%s' % x,
                    facebook_id='100003194166055%s' % x,
                    name=u'Rodrigo Cesar Savian%s' % x,
                    gender=u'male')
            db.add(p)
        db.commit()
        self.object_list = db.query(self.model_class).all()
        self.object = self.object_list[0]

def zip2dist(zip5, scale_column='population'):
    ## ARRRG, The census provides the congressional districts down to the tract
    # level, but not to the block level. The ZCTA are provided at the block
    # level, but NOT at the tract level. 
    # This would be ok if tracts didn't overlap ZCTAs, but they do. Not sure
    # how to solve this problem.
    if scale_column=='zip4':
        return zip2dist_by_zip4(zip5)
    pop_zip = db.select('census_population', what='sum('+scale_column+')',
            where="sumlev='ZCTA' and zip_id=$zip5",
            vars=locals()).list()
    if pop_zip and len(pop_zip)==1:
        pop_zip = pop_zip[0].sum
    else: print "oops"; return None
    # Limit our search to known intersecting districts
    dists = db.select('zip4', what='district_id', 
            where="zip=$zip5", group='district_id', 
            vars=locals())

    intersect_pops = db.query("select a.district_id, b.state_id, SUM(b."+scale_column+") from (SELECT * FROM census_population WHERE sumlev='TRACT' AND district_id != '') as a INNER JOIN (SELECT * FROM census_population WHERE sumlev='BLOCK' AND zip_id=$zip5) as b ON (a.state_id=b.state_id AND a.county_id=b.county_id AND a.tract_id=b.tract_id) group by a.district_id, b.state_id", vars=locals()).list()

    # NOTE: This is not the correct behavior, but for now just adjust this to
    #       give us something that sums to 1.0.
    pop_zip2 = sum(map(lambda x: x.sum if x.sum else 0.0, intersect_pops))
    print >>sys.stderr, "Pop Zip:",pop_zip, pop_zip2
    pop_zip = pop_zip2

    ret = {}
    for ip in intersect_pops:
        print >>sys.stderr, ip.sum, pop_zip
        ret['%s-%s' % (ip.state_id, ip.district_id)] = Decimal(ip.sum) / pop_zip if pop_zip else 0.0
    return ret

def modify_userPicKey(userId, newPicKey):
    try:
        results = db.query("update users set picKey=$pk where userId=$uId", vars={"pk": newPicKey, "uId": userId})
        return get_user(userId)
    except Exception, e:
        web.debug("users.modify_userPicKey: failed in modify database", e)
        return None

    def filter(cls, orderby='', rows=0, offset=10, **kwargs):
        """
        复杂的查询，返回包装后的model对象列表。
        :param orderby: orderby排序字典
        :param rows: limit参数
        :param offset:  limit参数，偏移量
        :param kwargs: 查询where条件

        >>> users = User.filter(orderby={"password": "DESC"}, rows=0, offset=2, email='[email protected]')
        """
        where, args = cls._where(**kwargs)
        if orderby:
            sorts = []
            for field, sort in orderby.iteritems():
                sorts.append("{} {} ".format(field, sort.upper()))
            orderby = "ORDER BY {}".format(', '.join(sorts))

        if where:
            query = ("SELECT * FROM `{table}` "
                     "WHERE {where} "
                     "{orderby} "
                     "LIMIT {rows}, {offset}".format(table=cls.__table__, where=where, orderby=orderby,
                                                     rows=rows, offset=offset))

        else:
            query = ("SELECT * FROM `{table}` "
                     "{orderby} "
                     "LIMIT {rows}, {offset}".format(table=cls.__table__, where=where, orderby=orderby,
                                                     rows=rows, offset=offset))

        logger.info('the SQL is {0}'.format(query))
        result = conn.query(query, *args)
        return [cls(**d) for d in result if result]

def test(formtype=None):
    def getdistzipdict(zipdump):
        """returns a dict with district names as keys zipcodes falling in it as values"""
        d = {}
        for line in zipdump.strip().split('\n'):
            zip5, zip4, dist = line.split('\t')
            d[dist] = (zip5, zip4)
        return d

    try:        
       dist_zip_dict =  getdistzipdict(file('zip_per_dist.tsv').read())
    except:
       import os, sys
       path = os.path.dirname(sys.modules[__name__].__file__)
       dist_zip_dict =  getdistzipdict(file(path + '/zip_per_dist.tsv').read())

    def getzip(dist):
        return dist_zip_dict[dist]
          
    query = "select district from wyr " 
    if formtype == 'wyr':  query += "where contacttype='W'"
    elif formtype == 'ima': query += "where contacttype='I'"
    elif formtype == 'zipauth': query += "where contacttype='Z'"
    elif formtype =='email': query += "where contacttype='E'"
    
    dists = [r.district for r in db.query(query)]
    for dist in dists:
        print dist,        
        zip5, zip4 = getzip(dist)
        msg_sent = writerep(dist, zipcode=zip5, zip4=zip4, prefix='Mr.', 
                    fname='watchdog', lname ='Tester', addr1='111 av', addr2='addr extn', city='test city', 
                    phone='001-001-001', email='[email protected]', msg='testing...')
        print msg_sent and 'Success' or 'Failure'

def query_census(location, hr_keys):
    # Use DISTINCT since some hr_keys map to multiple internal_keys (but should
    # have same value).
    #q = db.select('census', what='SUM(DISTINCT(value))', where=web.sqlors('hr_key=', hr_keys)+' AND location='+web.sqlquote(location))
    q = db.query('SELECT SUM(value) FROM (SELECT DISTINCT value, hr_key FROM census WHERE '+web.sqlors('hr_key=', hr_keys)+' AND district_id='+web.sqlquote(location)+') AS foo;')
    if not q: return None
    return q[0].sum

    def setUp(self):
        db.query(self.model_class).delete()
        db.commit()

        self.object_list = []
        for x in range(1, 5):
            p = self.model_class(
                id=x,
                username=u"rodrigocesar.savian%s" % x,
                facebook_id="100003194166055%s" % x,
                name=u"Rodrigo Cesar Savian%s" % x,
                gender=u"male",
            )
            self.object_list.append(p)
        self.object = self.object_list[0]
        self.serializer_object = self.serializer_class(self.object)

    def is_table_exist(cls, dbname=config.MYSQLDB['db']):
        table = cls.__dict__['__table__']

        query = "SELECT table_name FROM information_schema.TABLES WHERE table_name='{}' AND table_schema='{}'".format(
            table, dbname)
        row = conn.query(query)
        return True if row else False

def politician_contributor_employers(polid):
    return db.query("""SELECT cn.employer_stem, 
            sum(cn.amount) as amt FROM committee cm, politician_fec_ids pfi, 
            politician p, contribution cn WHERE cn.recipient_id = cm.id 
            AND cm.candidate_id = pfi.fec_id AND pfi.politician_id = p.id 
            AND p.id = $polid AND cn.employer_stem != '' GROUP BY cn.employer_stem 
            ORDER BY amt DESC""", vars=locals())

def interest_group_support(bill_id):
    "Get the support of interest groups for a bill."
    return db.query('select g.longname as longname, sum(gb.support) as support '
             'from  interest_group_bill_support gb , interest_group g '
             'where gb.bill_id = $bill_id and g.id = gb.group_id '
             'group by  gb.bill_id, g.longname '
             'order by sum(gb.support) desc', vars=locals()).list()

    def get_app(self):
        # first clear all
        db.query(self.model_class).delete()
        db.commit()

        for x in range(5):
            p = self.model_class(
                    username=u'rodrigocesar.savian%s' % x,
                    facebook_id='100003194166055%s' % x,
                    name=u'Rodrigo Cesar Savian%s' % x,
                    gender=u'male')
            db.add(p)
        db.commit()
        self.object_list = db.query(self.model_class).all()
        self.object = self.object_list[0]
        return app.make_app_test()

def init():
    db.query("CREATE VIEW census AS select * from census_meta NATURAL JOIN census_data")
    db.query("CREATE VIEW curr_politician AS SELECT politician.* FROM politician, congress WHERE politician.id = politician_id AND congress_num='111' AND current_member = 't' ;")
    db.query("GRANT ALL on curr_politician TO watchdog;")

    try:
        db.query("GRANT ALL on census TO watchdog")
    except:
        pass # group doesn't exist

def modify_userPasswd(userId, newUserPasswd):
    try:
        results = db.query("update users set passwd=$pw where userId=$uId", vars={"pw": newUserPasswd, "uId": userId})
        user = get_user(userId)
        return user
    except Exception, e:
        web.debug("users.modify_userPasswd: failed in modify database", e)
        return None

 def findall(cls, **kwargs):
     where, args = cls._where(**kwargs)
     if where:
         query = "SELECT * FROM `{table}` WHERE {where}".format(table=cls.__table__, where=where)
     else:
         query = "SELECT * FROM `{table}`".format(table=cls.__table__)
     logger.info('the SQL is {0}'.format(query))
     result = conn.query(query, *args)
     return [cls(**d) for d in result if result]

def get_commentByCommentId(commentId):
    try:
        results = db.query("select * from comments where commentId=$cId",vars={'cId':commentId})
        if len(results)==0:
            return None
        else:
            return bigComment.BigComment(results[0])
    except Exception,e:
        web.debug('comments.commentByCommentId: failed in select from database')

def delete_commentOfVideo(videoId):
    try:
        results = db.query("delete from comments where videoId=$vId",vars={'vId':videoId})
        if results==0:
            web.debug('comments.delete_commentOfVideo: failed delete comments by videoId, maybe not exist')
        return True
    except:
        web.debug('comments.delete_commentOfVideo: failed delete comments from database')
        return False