def hierarchy(self):
        prods = []

        _2dig = self.attr_cls.query.get(self.attr.id[:2])
        prods.append(_2dig)

        '''if this is a 2 digit product show only its children,
            on the other hand if its a 4 or 6 digit product show
            the single 4 digit prod and all 6 digit children with
            itself included'''
        if self.attr == _2dig:
            children = self.attr_cls.query \
                        .filter(self.attr_cls.id.startswith(_2dig.id)) \
                        .filter(func.char_length(self.attr_cls.id) == 6) \
                        .order_by("id") \
                        .all()
            prods = prods + list(children)
        else:
            _4dig = self.attr_cls.query.get(self.attr.id[:6])
            prods.append(_4dig)
            children = self.attr_cls.query \
                        .filter(self.attr_cls.id.startswith(_4dig.id)) \
                        .filter(func.char_length(self.attr_cls.id) == 8) \
                        .order_by("id") \
                        .all()
            prods = prods + list(children)

        return prods

def get_geo_location(ip):
    req = urllib2.Request("http://freegeoip.net/json/" + ip)
    opener = urllib2.build_opener()
    try:
        f = opener.open(req)
    except:
        return None
    json_resp = json.loads(f.read())
    city = json_resp["city"]
    # city = "Viana"
    state = json_resp["region_name"]
    # state = "Espírito Santo"
    # state = "Maranhão"

    # first try to find the exact city within the state
    bra_state = Bra.query.filter_by(name_pt=state).filter(
        func.char_length(Bra.id) == 3).first()
    bra_cities = Bra.query.filter_by(name_pt=city).filter(
        func.char_length(Bra.id) == 9)
    if bra_state:
        if bra_cities.count() == 1:
            return bra_cities.first()
        elif bra_cities.count() > 1:
            return bra_cities.filter(Bra.id.like(bra_state.id+'%')).first()
        return None
    return None

 def prev(self):
     c = self.__class__
     return self.query.filter(c.id < self.id) \
                     .filter(~c.id.in_(excluded_countries)) \
                     .filter(c.id_3char != None) \
                     .filter(func.char_length(c.id)==len(self.id)) \
                     .order_by(c.id.desc()).first()

 def prev(self):
     c = self.__class__
     return (
         self.query.filter(c.id < self.id)
         .filter(func.char_length(c.id) == len(self.id))
         .order_by(c.id.desc())
         .first()
     )

    def test_constructor(self):
        try:
            func.current_timestamp('somearg')
            assert False
        except TypeError:
            assert True

        try:
            func.char_length('a', 'b')
            assert False
        except TypeError:
            assert True

        try:
            func.char_length()
            assert False
        except TypeError:
            assert True

    def dump_project(self, project, session):
        print('Looking in project %s' % (project.name))
        query = session.query(Result) \
            .filter_by(project=project) \
            .order_by(func.char_length(Result.shortcode), Result.shortcode)

        if self.after:
            query = query.filter(Result.datetime > self.after)

        count = query.count()
        if count == 0:
            return

        self.projects_count += 1

        assert project.url_template.endswith('{shortcode}'), \
            'Writer only supports URL with prefix'

        # XXX: Use regex \{shortcode\}$ instead?
        site = project.url_template.replace('{shortcode}', '')

        self.fp = None
        self.writer = None
        last_filename = ''
        i = 0

        for item in query:
            self.items_count += 1
            i += 1

            if i % 1000 == 0:
                print('%d/%d' % (i, count))

            # we can do this as the query is sorted
            # so that item that would end up together
            # would returned together
            filename = self.get_filename(project, item)
            if filename != last_filename:
                self.close_fp()

                assert not os.path.isfile(filename), 'Target file %s already exists' % (filename)

                self.fp = self.get_fp(filename)
                self.writer = self.format(self.fp)
                self.writer.write_header(site)

                last_filename = filename

            self.writer.write_shortcode(item.shortcode, item.url, item.encoding)

            if not self.last_date or item.datetime > self.last_date:
                self.last_date = item.datetime

            if self.settings['delete']:
                session.delete(item)

        self.close_fp()

def compute_stats(metric, shows, limit=None, offset=None, sort="desc", depth=None, filters=[]):
    cache_key = CAROUSEL_NS + "".join(([metric] + shows) + ([str(limit), str(offset),sort,str(depth)]))
    prev = cached_query(cache_key)
    if prev:
        return pickle.loads(prev)
    
    kwargs = {metric:"dummy"}
    kwargs[shows[0]] = 'show'
    for show in shows[1:]:
        kwargs[show] = "dummy"

    table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)

    if not table:
        raise Exception("No Valid Table Available!")

    filters = []

    show_columns = [getattr(table, show) for show in shows]
    metric_col = getattr(table, metric)
    i = 0
    for show_column in show_columns:
        show=shows[i]
        if table in no_length_column:
            depth_val = depth or max_depth[show]
            filters.append(func.char_length(show_column) == depth_val )
        elif show in max_depth:
            depth_val = depth or max_depth[show]
            filters.append(getattr(table, show + table_helper.LEN) == depth_val )
        i+=1

    if table in filters_map:
        filters += filters_map[table]

    growth_regex = re.match('(num_emp)_growth(_5)?', metric)
    VAL_THRESOLD = 10000
    if growth_regex:
        orig_col_name = growth_regex.group(1)
        orig_col = getattr(table, orig_col_name)
        filters.append(orig_col >= VAL_THRESOLD)
    elif metric == "wage_avg" and len(shows) == 1 and shows[0] == "bra_id":
        # when looking at wage_avg for cities, only look at places
        # with >= 50k people
        cities = cities_by_pop(50000)
        filters.append(table.bra_id.in_(cities))
    columns = show_columns + [metric_col]
    results = query_helper.query_table(table, columns, filters, order=metric, limit=limit, sort=sort, offset=offset)

    cached_query(cache_key, pickle.dumps(results))
    return results

 def look_in_db(search_term):
     base_q = name_tbl.query.filter_by(lang=lang)
     if len_greater_than:
         base_q = base_q.filter(func.char_length(name_tbl.id) > len_greater_than)
     
     exact_match = base_q.filter_by(name=search_term).first()
     if exact_match:
         return [exact_match]
     
     starts_with_match = base_q.filter(name_tbl.name.startswith(search_term)).all()
     if len(starts_with_match):
         return starts_with_match
     if attr_tbl_backref == "sitc" or attr_tbl_backref == "hs":
         return base_q.filter(name_tbl.name.like("%"+search_term+"%")).all()
     else:
         return []

def update_startup_info(session):
    """ Insert tracking information for each startup for today into database
    """
    startups = session.query(CBCompany, ALCompany).filter(
            CBCompany.name==ALCompany.name).filter(
            CBCompany.twitter is not None).filter(
            func.char_length(CBCompany.twitter)>0).all()
    
    for startup in startups:
        al_id = startup.ALCompany.angellist_id
        print al_id
        
        count = session.query(StartupInfo).filter(StartupInfo.al_id==al_id).filter(
                    StartupInfo.info_date==date.today()).count()
        if count > 0:
            continue
        else:
            record = StartupInfo()
        
        al_url = "https://api.angel.co/1/startups/%d?access_token=%s" % \
            (al_id, config.ANGELLIST_TOKEN)
        resp = urllib2.urlopen(al_url)
        profile = json.loads(resp.read())
        
        record.info_date = date.today()
        record.al_id = al_id
        if (not "follower_count" in profile) or (not 'quality' in profile):
            prec = session.query(StartupInfo).filter(StartupInfo.al_id==al_id
                    ).filter(StartupInfo.info_date==(date.today()-timedelta(1))).first()
            record.al_follower = prec.al_follower
            record.al_quality = prec.al_quality
        else:
            record.al_follower = profile['follower_count']
            record.al_quality = profile['quality']
        
        twitter_profile = socialmedia.twitter_user_show(startup.CBCompany.twitter)
        record.twitter_follower = twitter_profile['followers_count']
        
        record.bitly_click = socialmedia.bitly_click_count(
                startup.ALCompany.bitly_hash)
        
        session.add(record)
        session.commit()

 def parse_bras(bra_str):
     if ".show." in bra_str:
         # the '.show.' indicates that we are looking for a specific nesting
         bar_id, nesting = bra_str.split(".show.")
         # filter table by requested nesting level
         bras = Bra.query \
                 .filter(Bra.id.startswith(bra_id)) \
                 .filter(func.char_length(Attr.id) == nesting).all()
         bras = [b.serialize() for b in bras]
     elif "." in bra_str:
         # the '.' indicates we are looking for bras within a given distance
         bra_id, distance = bra_str.split(".")
         bras = exist_or_404(Bra, bra_id)
         neighbors = bras.get_neighbors(distance)
         bras = [g.bra.serialize() for g in neighbors]
     else:
         # we allow the user to specify bras separated by '+'
         bras = bra_str.split("+")
         # Make sure the bra_id requested actually exists in the DB
         bras = [exist_or_404(Bra, bra_id).serialize() for bra_id in bras]
     return bras

def load_microsoft_suggestions_by_lang(active_messages, language, origin_language = None):
    """ Attempt to translate all the messages to a language """
    if language == 'en':
        return True, 0

    found = False
    for ms_language in microsoft_translator.languages:
        if ms_language == language:
            found = True

    if not found:
        return True, 0 # Focus on those not available in Microsoft

    last_month = datetime.datetime.utcnow() - datetime.timedelta(days=32)

    row = db.session.query(func.sum(func.char_length(TranslationExternalSuggestion.value))).filter(TranslationExternalSuggestion.origin_language == u'en', TranslationExternalSuggestion.engine==u'microsoft', TranslationExternalSuggestion.created>=last_month).first()
    # we don't have the right size of the human_key. we can use the value, but it's not accurate.
    # we have 2M per month. So we select 1.5M max (which is around 1.7M real)
    if row[0] > 1500000:
        return False, 0

    return _load_generic_suggestions_by_lang(active_messages, language, origin_language, 'microsoft', translation_func = _mstranslate, bulk_messages=True)

def searchFood(searchTerm, brandTerm, Food, FoodKey):
    searchTermList = searchTerm.split()
    keywords = []
    for each in searchTermList:
        keywords.append(each.lower())
    a = FoodKey.query.filter(FoodKey.word.in_(keywords)).subquery()
    q = (
        Food.query.filter(Food.id == a.c.keyid)
        .group_by(Food.id)
        .having(func.count(distinct(a.c.word)) == len(keywords))
    )

    brandTermList = brandTerm.split()
    orTerm = "|"
    brandTerm = orTerm.join(brandTermList)
    brandTerm.rstrip("|")
    if brandTerm != "":
        q = q.filter("Food.source @@ to_tsquery(:searchTerm)").params(searchTerm=brandTerm)

    if len(keywords) == 1:
        q = q.order_by(asc(func.char_length(Food.tag))).limit(40)

    foodIDs = [each.id for each in q]
    return foodIDs

 def prev(self):
     attr_class = getattr(attrs, self.type.capitalize())
     return attr_class.query \
             .filter(attr_class.id < self.attr.id) \
             .filter(func.char_length(attr_class.id) == len(self.attr.id)) \
             .order_by(attr_class.id.desc()).first()

    def setItem(self, release, path, item_type, data):
        """
        Set the data for the specified item.

        =============== =================================
        Parameter       Description
        =============== =================================
        release         Release path
        path            Item path inside of the structure
        item_type       The item type to create
        data            The JSON encoded data item
        =============== =================================

        ``Return:`` True on success
        """
        result = None
        session = None

        # Check if this item type is supported
        if not item_type in self._supportedItems:
            raise ValueError("unknown item type '%s'" % item_type)

        # Acquire name from path
        name = os.path.basename(path)

        try:
            session = self._manager.getSession()

            # Load parent object
            parent = self._get_parent(release, path)
            if not parent:
                raise ValueError("cannot find parent object for '%s'" % path)
            parent = session.merge(parent)

            # Check if the current path is a container for that kind of
            # item type
            if not item_type in self._supportedItems[parent.item_type]['container']:
                raise ValueError("'%s' is not allowed for container '%s'" %
                        (item_type, parent.item_type))

            # Load instance of ConfigItem
            item = self._manager._getConfigItem(name=name, item_type=item_type, release=release, add=True)
            session.commit()
            item = session.merge(item)
            item.path = path

            # Check if item will be renamed
            if "name" in data and name != data["name"]:
                item.name = data["name"]

            # Updated marker for assignable elements
            item.assignable = bool(self.getItemsAssignableElements(release, item))

            # Add us as child
            release_object = self._manager._getRelease(release)
            release_object = session.merge(release_object)
            release_object.config_items.append(item)
            parent.children.append(item)

            # Try to commit the changes
            session.commit()

            # Check if path has changed
            if "name" in data:
                newPath = os.path.dirname(path)
                if newPath != "/":
                    newPath = newPath + "/"
                newPath= newPath + data['name']

                if newPath != path:

                    # Update path values for the child config items.
                    # Get those entries that starts with 'oldB' and then replace the oldB part in the path.
                    oldB = path.rstrip("/")
                    newB = newPath.rstrip("/")
                    length = len(oldB)
                    session.query(ConfigItem).filter(ConfigItem.path.startswith(oldB)).update( \
                        {ConfigItem.path: func.concat(newB, func.right(ConfigItem.path, func.char_length(ConfigItem.path) - length))}, \
                        synchronize_session=False)
                    session.commit()

            result = True
        except:
            self.log.error("Caught unknown exception %s" % sys.exc_info()[0])
            session.rollback()
            raise
        finally:
            session.close()
        return result

def display_details(name):
    """Display detailed series information, ie. series show NAME"""

    from flexget.manager import Session
    with contextlib.closing(Session()) as session:

        name = normalize_series_name(name)
        # Sort by length of name, so that partial matches always show shortest matching title
        matches = (session.query(Series).filter(Series._name_normalized.contains(name)).
                   order_by(func.char_length(Series.name)).all())
        if not matches:
            console('ERROR: Unknown series `%s`' % name)
            return
        # Pick the best matching series
        series = matches[0]
        console('Showing results for `%s`.' % series.name)
        if len(matches) > 1:
            console('WARNING: Multiple series match to `%s`.' % name)
            console('Be more specific to see the results of other matches:')
            for s in matches[1:]:
                console(' - %s' % s.name)

        console(' %-63s%-15s' % ('Identifier, Title', 'Quality'))
        console('-' * 79)

        # Query episodes in sane order instead of iterating from series.episodes
        episodes = session.query(Episode).filter(Episode.series_id == series.id)
        if series.identified_by == 'sequence':
            episodes = episodes.order_by(Episode.number).all()
        elif series.identified_by == 'ep':
            episodes = episodes.order_by(Episode.season, Episode.number).all()
        else:
            episodes = episodes.order_by(Episode.identifier).all()

        for episode in episodes:

            if episode.identifier is None:
                console(' None <--- Broken!')
            else:
                console(' %s (%s) - %s' % (episode.identifier, episode.identified_by or 'N/A', episode.age))

            for release in episode.releases:
                status = release.quality.name
                title = release.title
                if len(title) > 55:
                    title = title[:55] + '...'
                if release.proper_count > 0:
                    status += '-proper'
                    if release.proper_count > 1:
                        status += str(release.proper_count)
                if release.downloaded:
                    console('  * %-60s%-15s' % (title, status))
                else:
                    console('    %-60s%-15s' % (title, status))

        console('-' * 79)
        console(' * = downloaded')
        if not series.identified_by:
            console('')
            console(' Series plugin is still learning which episode numbering mode is ')
            console(' correct for this series (identified_by: auto).')
            console(' Few duplicate downloads can happen with different numbering schemes')
            console(' during this time.')
        else:
            console(' Series uses `%s` mode to identify episode numbering (identified_by).' % series.identified_by)
        console(' See option `identified_by` for more information.')
        if series.begin:
            console(' Begin episode for this series set to `%s`.' % series.begin.identifier)

#.........这里部分代码省略.........
                trade_direction = _("exports") if net_trade >= 0 else _("imports")
                tb_subtitle = _(u"As of %(year)s %(country)s had a %(positive_negative)s trade balance of $%(net_trade)s in net %(exports_imports)s.",
                                year=self.year, country=self.attr.get_name(article=True), positive_negative=trade_balance, net_trade=num_format(abs(net_trade)), exports_imports=trade_direction)
                old_yo = self.models.Yo.query.filter_by(year = available_years["hs92"][0], country = self.attr).first()
                if old_yo:
                    old_net_trade = old_yo.export_val - old_yo.import_val
                    old_trade_balance = _("positive") if old_net_trade >= 0 else _("negative")
                    old_trade_direction = _("exports") if old_net_trade >= 0 else _("imports")
                    is_diff = True if old_trade_balance != trade_balance else False
                    still_or_not = _("still") if old_trade_balance == trade_balance else ""
                    tb_subtitle += u" "
                    tb_subtitle += _(u"As compared to their trade balance in %(year)s when they %(still)s had a %(positive_negative)s trade balance of $%(net_trade)s in net %(exports_imports)s.",
                                    year=available_years["hs92"][0], still=still_or_not, positive_negative=old_trade_balance, net_trade=num_format(abs(old_net_trade)), exports_imports=old_trade_direction)

            trade_section = {
                "builds": [
                    {"title": _(u"Exports"), "build": export_tmap, "subtitle": export_subtitle, "tour":"This is just a test", "seq":5},
                    {"title": _(u"Imports"), "build": import_tmap, "subtitle": import_subtitle},
                    {"title": _(u"Trade Balance"), "build": tb_build, "subtitle": tb_subtitle},
                    {"title": _(u"Destinations"), "build": dests_tmap, "subtitle": dest_subtitle},
                    {"title": _(u"Origins"), "build": origins_tmap, "subtitle": origin_subtitle},
                ]
            }

        sections.append(trade_section)

        ''' Product Space Section
        '''
        subtitle = False
        if self.attr.id != "xxwld":
            num_exports_w_rca = db.session.query(func.count(self.models.Yop.hs92_id)) \
                        .filter_by(year = self.year, origin = self.attr) \
                        .filter(self.models.Yop.export_rca >= 1) \
                        .filter(func.char_length(self.models.Yop.hs92_id)==6) \
                        .scalar()
            this_attr_yo = attrs.Yo.query.filter_by(year = self.year, country = self.attr).first()
            if this_attr_yo:
                eci = this_attr_yo.eci
                eci_rank = this_attr_yo.eci_rank
                if eci_rank:
                    subtitle = _(u"The economy %(of_country)s has an Economic Complexity Index (ECI) of %(eci)s making it the %(eci_rank)s most complex country.",
                                of_country=self.attr.get_name(article="of"), eci=num_format(eci), eci_rank=num_format(eci_rank, "ordinal"))
                    subtitle += u" "
                else:
                    subtitle = ""
                subtitle += _(u"%(country)s exports %(num_of_exports)s products with revealed comparative advantage " \
                    u"(meaning that its share of global exports is larger than what " \
                    u"would be expected from the size of its export economy " \
                    u"and from the size of a product’s global market).",
                    country=self.attr.get_name(article=True), num_of_exports=num_exports_w_rca)
        product_space = Build("network", "hs92", "export", self.attr, "all", "show", self.year)
        ps_text = _(u"The product space is a network connecting products that are likely to be co-exported and can be used to predict the evolution of a country’s export structure.")
        if subtitle:
            ps_text = u"{}</p><p>{}".format(ps_text, subtitle)
        ps_section = {
            "title": _(u"Economic Complexity %(of_country)s", of_country=self.attr.get_name(article="of")),
            "builds": [
                {"title": _(u"Product Space"), "build": product_space, "subtitle": ps_text, "tour":"The product space...", "seq":6}
            ]
        }

        ''' PGI Section
        '''
        if self.attr.id != "xxwld":
            pgi_product_space = Build("network", "sitc", "pgi", self.attr, "all", "show", available_years["sitc"][-1])
            subtitle = _("In this version of the product space products are colored according to their Product Gini Index, or PGI. The PGI of a product is the level of income inequality that we expect for the countries that export a product. For more information see: %(paper1)s and %(paper2)s.", country=self.attr.get_name(article=True), paper1="<a target='_blank' href='https://arxiv.org/abs/1505.07907'>Linking Economic Complexity, Institutions and Income Inequality</a>", paper2="<a target='_blank' href='https://arxiv.org/abs/1701.03770'>The structural constraints of income inequality in Latin America</a>")

def attrs(attr="bra",Attr_id=None, depth=None):

    Attr = globals()[attr.capitalize()]
    Attr_weight_mergeid = "{0}_id".format(attr)

    if attr == "bra":
        Attr_weight_tbl = Yb
        Attr_weight_col = "population"
    elif attr == "cnae":
        Attr_weight_tbl = Yi
        Attr_weight_col = "num_jobs"
    elif attr == "cbo":
        Attr_weight_tbl = Yo
        Attr_weight_col = "num_jobs"
    elif attr == "hs":
        Attr_weight_tbl = Ymp
        Attr_weight_col = "export_val"
    elif attr == "wld":
        Attr_weight_tbl = Ymw
        Attr_weight_col = "export_val"
    elif attr == "course_hedu":
        Attr_weight_tbl = Yc_hedu
        Attr_weight_col = "enrolled"
    elif attr == "university":
        Attr_weight_tbl = Yu
        Attr_weight_col = "enrolled"
    elif attr == "school":
        Attr_weight_tbl = Ys
        Attr_weight_col = "enrolled"
    elif attr == "course_sc":
        Attr_weight_tbl = Yc_sc
        Attr_weight_col = "enrolled"

    depths = {}
    depths["bra"] = [1, 3, 5, 7, 9]
    depths["cnae"] = [1, 3, 6]
    depths["cbo"] = [1, 4]
    depths["hs"] = [2, 6]
    depths["wld"] = [2, 5]
    depths["course_hedu"] = [2, 6]
    depths["university"] = [5]
    depths["course_sc"] = [2, 5]
    depths["school"] = [8]

    depth = request.args.get('depth', depth)
    order = request.args.get('order', None)
    offset = request.args.get('offset', None)
    limit = request.args.get('limit', None)

    if offset:
        offset = float(offset)
        limit = limit or 50
    elif limit:
        offset = float(0)

    lang = request.args.get('lang', None) or g.locale
    ret = {}
    dataset = "rais"
    if Attr == Wld or Attr == Hs:
        dataset = "secex"
    elif Attr == Course_hedu or Attr == University:
        dataset = "hedu"
    elif Attr == Course_sc or Attr == School:
        dataset = "sc"
    elif Attr == Bra:
        dataset = "population"

    cache_id = "attrs:" + request.path + lang
    if depth:
        cache_id = cache_id + "/" + depth
    # first lets test if this query is cached
    cached_q = cached_query(cache_id)
    if cached_q and limit is None:
        ret = make_response(cached_q)
        ret.headers['Content-Encoding'] = 'gzip'
        ret.headers['Content-Length'] = str(len(ret.data))
        return ret

    # if an ID is supplied only return that
    if Attr_id:

        # the '.show.' indicates that we are looking for a specific nesting
        if ".show." in Attr_id:
            this_attr, ret["nesting_level"] = Attr_id.split(".show.")
            # filter table by requested nesting level
            attrs = Attr.query \
                    .filter(Attr.id.startswith(this_attr)) \
                    .filter(func.char_length(Attr.id) == ret["nesting_level"]).all()

        # the 'show.' indicates that we are looking for a specific nesting
        elif "show." in Attr_id:
            ret["nesting_level"] = Attr_id.split(".")[1]
            # filter table by requested nesting level
            attrs = Attr.query.filter(func.char_length(Attr.id) == ret["nesting_level"]).all()

        # the '.' here means we want to see all attrs within a certain distance
        elif "." in Attr_id:
            this_attr, distance = Attr_id.split(".")
            this_attr = Attr.query.get_or_404(this_attr)
            attrs = this_attr.get_neighbors(distance)
#.........这里部分代码省略.........

def collection_by_depth(base, depth=None):
    return db.session.query(base).filter(
        func.char_length(base.id) == depth
    )

def cities_by_pop(value):
    Ybs = attrs.Ybs
    filters = [Ybs.stat_id == 'pop', Ybs.stat_val >= value, Ybs.year == __latest_year__['stats'], func.char_length(Ybs.bra_id) == 9]
    res = Ybs.query.filter(*filters).with_entities(Ybs.bra_id).all()
    if res:
        return [row[0] for row in res]
    return res

 def next(self):
     c = self.__class__
     return self.query.filter(c.id > self.id).filter(func.char_length(c.id) == len(self.id)).order_by(c.id).first()