def getServiceCategoryListAll(level = 1):
    mckey = 'category_service.py::getServiceCategoryListAll_%s' %level
    #mv  = mc.get(mckey)
    mv = None
    if mv is not None:
        return mv

    white_list = []
    sql = 'select category_id, category_name from jd_category_white_list where is_service=1'
    retrows = dbhelper_read.executeSqlRead(sql)
    for row in retrows:
        white_list.append(row['category_id'])

    retlist = []
    for row in retrows:
        retlist.append(row)
    if level==0:
        return retlist

    for catid in white_list:
        sql2 = '''
          select id as category_id, name as category_name from jd_category where (id='%s' or id like '%s-%%')
          and (LENGTH(id) - LENGTH(REPLACE(id,"-", ""))) / LENGTH("-") <= %s
        ''' %(catid, catid, level)
        #print sql2
        retrows2 = dbhelper_read.executeSqlRead(sql2)
        #print len(retrows2)
        retlist += retrows2

    #mc.set(mckey,retlist,SERVICE_CATEGORY_CACHE_TIMEOUT)
    return retlist
Exemple #2
0
def getServiceCategoryListAll(level=1):
    mckey = 'category_service.py::getServiceCategoryListAll_%s' % level
    #mv  = mc.get(mckey)
    mv = None
    if mv is not None:
        return mv

    white_list = []
    sql = 'select category_id, category_name from jd_category_white_list where is_service=1'
    retrows = dbhelper_read.executeSqlRead(sql)
    for row in retrows:
        white_list.append(row['category_id'])

    retlist = []
    for row in retrows:
        retlist.append(row)
    if level == 0:
        return retlist

    for catid in white_list:
        sql2 = '''
          select id as category_id, name as category_name from jd_category where (id='%s' or id like '%s-%%')
          and (LENGTH(id) - LENGTH(REPLACE(id,"-", ""))) / LENGTH("-") <= %s
        ''' % (catid, catid, level)
        #print sql2
        retrows2 = dbhelper_read.executeSqlRead(sql2)
        #print len(retrows2)
        retlist += retrows2

    #mc.set(mckey,retlist,SERVICE_CATEGORY_CACHE_TIMEOUT)
    return retlist
def get_sub_categories(category_id):
    sql = 'select id as category_id, name as category_name from jd_category where id like "%s-%%" and id not like "%s-%%-%%"' %(category_id, category_id)
    retrows = dbhelper_read.executeSqlRead(sql)

    if len(retrows) == 0:
        sql = 'select id as category_id, name as category_name from jd_category where id like "%s-%%" and id not like "%s-%%-%%-%%"' %(category_id, category_id)
        retrows = dbhelper_read.executeSqlRead(sql)

    # for row in retrows:
    #     row['sub_categories'] = _expand_to_sub_categories(row['category_id'])
    #     print row['category_id']
    return retrows
Exemple #4
0
def get_sub_categories(category_id):
    sql = 'select id as category_id, name as category_name from jd_category where id like "%s-%%" and id not like "%s-%%-%%"' % (
        category_id, category_id)
    retrows = dbhelper_read.executeSqlRead(sql)

    if len(retrows) == 0:
        sql = 'select id as category_id, name as category_name from jd_category where id like "%s-%%" and id not like "%s-%%-%%-%%"' % (
            category_id, category_id)
        retrows = dbhelper_read.executeSqlRead(sql)

    # for row in retrows:
    #     row['sub_categories'] = _expand_to_sub_categories(row['category_id'])
    #     print row['category_id']
    return retrows
Exemple #5
0
def getWorthyInfo_of_skuid_list(sku_id_list):
    if len(sku_id_list) == 0:
        return []
    sku_id_list2 = []
    for item in sku_id_list:
        sku_id_list2.append("%s" % item)
    dt = timeHelper.getTimeAheadOfNowHours(
        service_config.SKU_LIST_APP_WORTHY_RECENCY_HOURS,
        timeHelper.FORMAT_LONG)
    id_clause = ','.join(sku_id_list2)

    skulist3 = []
    skuid_clause = "("
    for sku_id in sku_id_list2:
        skulist3.append("sku_id = %s" % sku_id)
    skuid_clause += ' OR '.join(skulist3)
    skuid_clause += ")"

    sql = '''
            select
            *, instr('%s',sku_id) as dd
            from
            jd_worthy_latest
            where
            -- this_update_time > '%s'
            -- and sku_id in (%s)
             %s
            order by dd ASC
        ''' % (id_clause, dt, id_clause, skuid_clause)
    # print sql
    retrows = dbhelper_read.executeSqlRead(sql, is_dirty=True)
    return retrows
def getQueryHistory(device_id):
    sql = 'select distinct query from user_events where device_id="%s" and query is not NULL and query<>"" order by event_time DESC limit %s' %(device_id,service_config.QUERY_HISTORY_MAX_NUM)
    retrows = dbhelper_read.executeSqlRead(sql,is_dirty=True)
    rlist = []
    for row in retrows:
        rlist.append(common_analytics.make_space_separated(row['query']))
    vlist = common_analytics.dedup_leave_max(rlist)
    return vlist
def getCatalogs():

    sql = 'select catalog_id, catalog_name as category_name from jd_catalog order by order_weight DESC'
    retrows = dbhelper_read.executeSqlRead(sql)
    for row in retrows:
        row['category_id'] = '%s' %(row['catalog_id'])
        row.pop('catalog_id')
    return retrows
def getCatalogHistory(device_id):
    sql = 'select distinct catalog_id, catalog_name from user_events where device_id="%s" and catalog_id is not NULL and catalog_id<>"" order by event_time DESC limit %s' %(device_id,service_config.CATALOG_HISTORY_MAX_NUM)
    # print sql
    retrows = dbhelper_read.executeSqlRead(sql,is_dirty=True)

    rlist = []
    for row in retrows:
        rlist.append([row['catalog_id'],row['catalog_name']])
    return rlist
Exemple #9
0
def getQueryHistory(device_id):
    sql = 'select distinct query from user_events where device_id="%s" and query is not NULL and query<>"" order by event_time DESC limit %s' % (
        device_id, service_config.QUERY_HISTORY_MAX_NUM)
    retrows = dbhelper_read.executeSqlRead(sql, is_dirty=True)
    rlist = []
    for row in retrows:
        rlist.append(common_analytics.make_space_separated(row['query']))
    vlist = common_analytics.dedup_leave_max(rlist)
    return vlist
Exemple #10
0
def getCatalogHistory(device_id):
    sql = 'select distinct catalog_id, catalog_name from user_events where device_id="%s" and catalog_id is not NULL and catalog_id<>"" order by event_time DESC limit %s' % (
        device_id, service_config.CATALOG_HISTORY_MAX_NUM)
    # print sql
    retrows = dbhelper_read.executeSqlRead(sql, is_dirty=True)

    rlist = []
    for row in retrows:
        rlist.append([row['catalog_id'], row['catalog_name']])
    return rlist
Exemple #11
0
def _getPriceHistory(sku_id):
    sql = '''
    select a.update_date, a.price from

    (select * from jd_item_price
    where sku_id = %s and price>0
    order by price ASC) a

    -- group by a.update_date
    order by a.update_date ASC
    ''' % sku_id
    retrows = dbhelper_read.executeSqlRead(sql, is_dirty=True)
    return retrows
def getServiceCategoryList():
    mckey = 'category_service.py::getServiceCategoryList'
    #mv  = mc.get(mckey)
    mv = None
    if mv is not None:
        return mv
    sql = 'select * from jd_category_white_list where is_service=1 order by id ASC'
    retrows = dbhelper_read.executeSqlRead(sql)
    # for row in retrows:
    #     sub_rows = get_sub_categories(row['category_id'])
    #     row['sub_categories'] = sub_rows

    #mc.set(mckey,retrows,SERVICE_CATEGORY_CACHE_TIMEOUT)
    return retrows
Exemple #13
0
def getServiceCategoryList():
    mckey = 'category_service.py::getServiceCategoryList'
    #mv  = mc.get(mckey)
    mv = None
    if mv is not None:
        return mv
    sql = 'select * from jd_category_white_list where is_service=1 order by id ASC'
    retrows = dbhelper_read.executeSqlRead(sql)
    # for row in retrows:
    #     sub_rows = get_sub_categories(row['category_id'])
    #     row['sub_categories'] = sub_rows

    #mc.set(mckey,retrows,SERVICE_CATEGORY_CACHE_TIMEOUT)
    return retrows
Exemple #14
0
def _getDiscountHistory_of_Sku_as_list(sku_id):
    sql = '''
    select dt, type, content, score from
        (select this_update_date as dt, 'deduction' as type, content_deduction as content, deduction_score as score from jd_worthy where sku_id=%s and content_deduction is not NULL
        UNION
        select this_update_date as dt, 'discount' as type, content_discount as content, 1.0-rf_ratio as score from jd_worthy where sku_id=%s and content_discount is not NULL
        UNION
        select this_update_date as dt, 'gift' as type, gift_price*gift_num as content, 0 as score from jd_worthy where sku_id=%s and gift_price is not NULL
        ) a
    order by dt DESC
    ''' % (sku_id, sku_id, sku_id)
    retrows = dbhelper_read.executeSqlRead(sql, is_dirty=True)
    for row in retrows:
        types = row['type']
        if types == 'gift':
            row['content'] = u'赠送礼物,价值¥%0.0f' % row['content']
        score = 0
        try:
            score = float(row['score'])
        except:
            pass
        row['score'] = int(min(int(score * 10), int(5)))

    return retrows
Exemple #15
0
def getWorthyInfo_of_skuid(sku_id):
    sql = 'select * from jd_worthy_latest where sku_id=%s limit 1' % sku_id
    retrows = dbhelper_read.executeSqlRead(sql, is_dirty=True)
    _processSkuThumbInfo(retrows)
    return retrows
def get_indicator_given_part_of_query(query):
    mckey = memcachedStatic.getKey("GET_INDICATOR2::%s" %query)

    mcv = mc.get(mckey)
    if mcv is not None:
        return mcv

    retlist = sku_index_access.getSearchResult(query)
    sku_id_list = []
    i = 0
    for sku_id in retlist:
        sku_id_list.append("%s" %sku_id)
        i += 1
        if i >= 30:
            break

    in_clause = ','.join(sku_id_list)
    sql = '''
        select
            category_id,
            count(1) as count_hits,
            catalog_id,
            catalog_name,
            category_name
        from
        jd_worthy_latest
        where sku_id in (%s) and catalog_name is not NULL
        group by category_id
        order by count_hits DESC
        limit %s
    ''' %(in_clause,service_config.CATEGORY_INDICATOR_MAX_NUM)
    retrows = dbhelper_read.executeSqlRead(sql, is_dirty=True)

    retlist = []

    for row in retrows:
        category_id = row['category_id']
        # if category_id in ['670-677-5009','670-677-683','670-677-687','670-671-672']:
        #     continue
        category_name = row['category_name']
        catalog_id = row['catalog_id']
        catalog_name = row['catalog_name']
        sql0 = '''
            select
            p_value,
            count(1) as count_hits,
            p_key

            from

            jd_analytic_property_latest

            where sku_id in (%s)
            and category_id = "%s"
            and p_value like "%%%s%%"
            group by p_value
            -- having count(1) > 1
            order by count_hits DESC
            limit %s
        ''' %(in_clause, category_id,  query, service_config.PROPERTY_INDICATOR_MAX_NUM)

        sql1 = '''
            select
            p_value,
            count(1) as count_hits,
            p_key

            from

            jd_analytic_property_latest

            where sku_id in (%s)
            and category_id = "%s"
            and p_key = '品牌'
            group by p_value
            -- having count(1) > 1
            order by count_hits DESC
            limit %s
        ''' %(in_clause, category_id, service_config.PROPERTY_INDICATOR_MAX_NUM)

        sql2 = '''
            select
            p_value,
            count(1) as count_hits,
            p_key

            from

            jd_analytic_property_latest

            where sku_id in (%s)
            and category_id = "%s"
            and p_key <> '品牌'
            group by p_value
            -- having count(1) > 1
            order by count_hits DESC
            limit %s
        ''' %(in_clause, category_id, service_config.PROPERTY_INDICATOR_MAX_NUM)

        retrows0 = dbhelper_read.executeSqlRead(sql0)
        retrows1 = dbhelper_read.executeSqlRead(sql1)
        retrows2 = dbhelper_read.executeSqlRead(sql2)
        plist = common_analytics.dedup_leave_max(_retrows_to_list(retrows0+retrows1+retrows2, 'p_value'))
        query2 = common_analytics.dedup_inline(query)
        if query2 not in plist:
            if query2 not in category_name:
                plist = [query2] + plist
        plist = common_analytics.remove_string_from_list(category_name,plist)
        plist = collection_utils.expand_list(plist, P_VALUE_SPLIT_LIST)
        qlist = []
        for item in plist:
            if item not in P_VALUE_BLACK_LIST:
                item = item.lower()
                if not regex_dict_helper.is_regex_match_list(item, P_VALUE_BLACK_LIST_REGEX):
                    qlist.append(item)
        retlist.append({
            'category': [category_id,category_name],
            'property': qlist,
        })

    mc.set(mckey,retlist)
    return retlist
Exemple #17
0
def getSku_ID_ListByCatalogID(
    category_id="_ALL_",
    startpos=0,
    min_allowed_price=service_config.SKU_LIST_MIN_ALLOWED_PRICE,
    min_allowed_discount_rate=service_config.SKU_LIST_MIN_ALLOWED_WORTHY_VALUE
):

    retrows = None
    t1 = time.time()

    catalog_id_constraint = ""
    for idc in service_config.PRESET_CATALOG_ID_CONSTRAINTS:
        catalog_id_constraint += " catalog_id <> %s AND " % idc
    blackword_constraint = ""
    for blackword in service_config.PRESET_CATALOG_CATEGORY_WILDCARD_BLACK_WORDS:
        blackword_constraint += " category_name not like '%%%s%%' AND " % blackword.strip(
        )

    catalog_constraint = " catalog_id is not null AND %s %s " % (
        catalog_id_constraint, blackword_constraint)

    if category_id == "_ALL_":
        catalog_sql_part = catalog_constraint
    elif category_id == "_EXPENSIVE_":
        min_allowed_price = service_config.SKU_LIST_MIN_PRICE_FOR_EXPENSIVE
        catalog_sql_part = catalog_constraint
    else:
        catalog_sql_part = 'catalog_id = %s and ' % category_id

    dt = timeHelper.getTimeAheadOfNowHours(
        service_config.SKU_LIST_APP_WORTHY_RECENCY_HOURS,
        timeHelper.FORMAT_LONG)
    sql = '''
        select
        sku_id
        -- ,if(a=34,0,1) as stock_bit
        from
        jd_worthy_latest
        where
        %s
        worthy_value1 < %s
        and median_price >= %s
        and median_price < %s
        and this_update_time > '%s'
        order by
        -- stock_bit DESC,
        worthy_value1 ASC
        -- limit %s, %s
    ''' % (catalog_sql_part, min_allowed_discount_rate, min_allowed_price,
           service_config.SKU_LIST_MAX_ALLOWED_PRICE, dt, startpos,
           service_config.SKU_LIST_FRAME_SIZE)

    if category_id == '_HISTORY_LOWEST_':
        sql = '''
        select
        sku_id
        from
        jd_worthy_latest
        where
        %s
        min_price_reached = 2
        and this_update_time > '%s'
        and a<>34
        order by
        worthy_value1 ASC
        ''' % (catalog_constraint, dt)

    elif category_id == 'HOT':
        dt_hot = timeHelper.getTimeAheadOfNowHours(
            service_config.SKU_LIST_DISCOVERY_RECENCY_HOURS,
            format=timeHelper.FORMAT_LONG)
        sql = '''
        select

        distinct a.sku_id

        from

        jd_notification_history_lowest a
        left join
        jd_worthy_latest b
        using(sku_id)

        where
        %s
        a.update_time > '%s'
        and b.a<>34

        order by
        a.update_time DESC, worthy_value1 ASC
        ''' % (catalog_constraint, dt_hot)

    # print sql
    retrows = dbhelper_read.executeSqlRead(sql)
    vlist = []
    for row in retrows:
        vlist.append(row['sku_id'])
    return vlist
def _get_sub_categories_all(category_id):
    sql = 'select id as category_id, name as category_name from jd_category where id like "%s-%%"' %(category_id)
    retrows = dbhelper_read.executeSqlRead(sql)
    return retrows
Exemple #19
0
def _get_sub_categories_all(category_id):
    sql = 'select id as category_id, name as category_name from jd_category where id like "%s-%%"' % (
        category_id)
    retrows = dbhelper_read.executeSqlRead(sql)
    return retrows
Exemple #20
0
def getDiscounts_of_sku_as_list(sku_id):
    sql = 'select name, content from jd_analytic_promo_item_latest where (code=15 or code=19) and sku_id=%s order by code ASC, content DESC' % sku_id
    return dbhelper_read.executeSqlRead(sql, is_dirty=True)
Exemple #21
0
def getImages_of_sku_as_list(sku_id):
    sql = 'select image_url from jd_item_images_latest where sku_id=%s' % sku_id
    return dbhelper_read.executeSqlRead(sql, is_dirty=True)