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 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
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
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
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 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
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
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
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
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
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
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
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)
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)