def empty_catalogs():
sql1 = 'delete from jd_catalog'
sql2 = 'delete from jd_catalog_map'
ret1 = dbhelper.executeSqlWrite1(sql1)
ret2 = dbhelper.executeSqlWrite1(sql2)
print 'removed from jd_catalog rows = ' %ret1
print 'removed from jd_catalog_map rows = ' %ret2
def empty_catalogs():
sql1 = 'delete from jd_catalog'
sql2 = 'delete from jd_catalog_map'
ret1 = dbhelper.executeSqlWrite1(sql1)
ret2 = dbhelper.executeSqlWrite1(sql2)
print 'removed from jd_catalog rows = ' % ret1
print 'removed from jd_catalog_map rows = ' % ret2
def update_history_lowest_store():
skulist = getHistoryLowest_SkuIds()
dt = timeHelper.getNowLong()
vlist = []
for sku_id in skulist:
vlist.append([sku_id, dt])
sql = 'insert ignore into jd_notification_history_lowest values (%s,%s)'
afr = dbhelper.executeSqlWriteMany(sql,vlist)
sql2 = 'replace into jd_notification_job_status values("%s","%s")'%(NOTIFICATION_JOB_NAME,dt)
afr2 = dbhelper.executeSqlWrite1(sql2)
afr3 = _removeOldNotifications()
afr4 = _removeOutdated_Nonhistory_lowest()
return [afr,afr2,afr3,afr4]
def do_log_user_event(device_id, query, catalog_id, remote_ip):
catalog_name = ""
if query is not None and len(query) > 0:
catalog_id = ""
else:
query = ""
sql2 = 'select * from jd_catalog where catalog_id="%s"' %catalog_id
# print sql2
retrows = dbhelper.executeSqlRead(sql2)
if len(retrows) > 0:
catalog_name = retrows[0]['catalog_name']
else:
catalog_name = 'Unknown'
sql_user_event = 'insert into user_events values("%s","%s","%s","%s","%s","%s")' %(device_id,query,catalog_id,catalog_name, timeHelper.getNowLong(),remote_ip)
afr = dbhelper.executeSqlWrite1(sql_user_event)
# print afr
return afr
def do_log_user_event(device_id, query, catalog_id, remote_ip):
catalog_name = ""
if query is not None and len(query) > 0:
catalog_id = ""
else:
query = ""
sql2 = 'select * from jd_catalog where catalog_id="%s"' % catalog_id
# print sql2
retrows = dbhelper.executeSqlRead(sql2)
if len(retrows) > 0:
catalog_name = retrows[0]['catalog_name']
else:
catalog_name = 'Unknown'
sql_user_event = 'insert into user_events values("%s","%s","%s","%s","%s","%s")' % (
device_id, query, catalog_id, catalog_name, timeHelper.getNowLong(),
remote_ip)
afr = dbhelper.executeSqlWrite1(sql_user_event)
# print afr
return afr
def _removeOutdated_Nonhistory_lowest():
sql = '''
DELETE
FROM
jd_notification_history_lowest
WHERE
sku_id IN (
SELECT
sku_id
FROM
(
SELECT DISTINCT
a.sku_id AS sku_id
FROM
jd_notification_history_lowest a
LEFT JOIN jd_worthy_latest b USING (sku_id)
WHERE
b.min_price_reached < 2
) pp
)
'''
afr = dbhelper.executeSqlWrite1(sql)
return afr
def calculate_rating_diff():
sql = '''
replace into jd_analytic_item_rating_diff
select
a.SkuId,
'%s' as dt,
c.category_id,
d.name as category_name,
a.CommentCount,
c.sample_count as category_sample_count,
((a.Score1Count)*1.0+(a.Score2Count)*2.0+(a.Score3Count)*3.0+(a.Score4Count)*4.0+(a.Score5Count)*5.0)/a.CommentCount as rating_score,
c.rating_score as category_rating_score,
((a.Score1Count)*1.0+(a.Score2Count)*2.0+(a.Score3Count)*3.0+(a.Score4Count)*4.0+(a.Score5Count)*5.0)/a.CommentCount*1.0/c.rating_score as rating_score_diff,
(a.Score4Count+a.Score5Count)/a.CommentCount as rate_good,
c.rate_good as category_rate_good,
Format(((a.Score4Count+a.Score5Count)/a.CommentCount - c.rate_good)*100/c.rate_good,1) as rate_good_diff,
(a.Score1Count+a.Score2Count)/a.CommentCount as rate_bad,
c.rate_bad as category_rate_bad,
Format(((a.Score1Count+a.Score2Count)/a.CommentCount - c.rate_bad)*100/c.rate_bad,1) as rate_bad_diff,
a.Score1Count/a.CommentCount as rate_1,
c.rate_1 as category_rate_1,
Format((a.Score1Count/a.CommentCount - c.rate_1)*100/c.rate_1,1) as rate_1_diff,
a.Score2Count/a.CommentCount as rate_2,
c.rate_2 as category_rate_2,
Format((a.Score2Count/a.CommentCount - c.rate_2)*100/c.rate_2,1) as rate_2_diff,
a.Score3Count/a.CommentCount as rate_3,
c.rate_3 as category_rate_3,
Format((a.Score3Count/a.CommentCount - c.rate_3)*100/c.rate_3,1) as rate_3_diff,
a.Score4Count/a.CommentCount as rate_4,
c.rate_4 as category_rate_4,
Format((a.Score4Count/a.CommentCount - c.rate_4)*100/c.rate_4,1) as rate_4_diff,
a.Score5Count/a.CommentCount as rate_5,
c.rate_5 as category_rate_5,
Format((a.Score5Count/a.CommentCount - c.rate_5)*100/c.rate_5,1) as rate_5_diff,
a.dt as item_origin_dt,
c.dt as category_origin_dt,
c.origin_dt as raw_origin_dt
FROM
jd_item_comment_count_latest a
left JOIN
jd_item_category b
on a.SkuId = b.sku_id and b.sku_id is not NULL
left join
jd_analytic_category_rating_latest c
on b.category_id = c.category_id
left join
jd_category d
on d.id = c.category_id
where a.CommentCount>0
''' % timeHelper.getNow()
# print sql
afr = dbhelper.executeSqlWrite1(sql,
is_dirty=True,
isolation_type='serializable')
return afr
def _removeOldNotifications():
ut = timeHelper.getTimeAheadOfNowHours(24,format=timeHelper.FORMAT_LONG)
sql = 'delete from jd_notification_history_lowest where update_time <= "%s"' %ut
afr = dbhelper.executeSqlWrite1(sql)
return afr
def calculate_rating_diff() :
sql = '''
replace into jd_analytic_item_rating_diff
select
a.SkuId,
'%s' as dt,
c.category_id,
d.name as category_name,
a.CommentCount,
c.sample_count as category_sample_count,
((a.Score1Count)*1.0+(a.Score2Count)*2.0+(a.Score3Count)*3.0+(a.Score4Count)*4.0+(a.Score5Count)*5.0)/a.CommentCount as rating_score,
c.rating_score as category_rating_score,
((a.Score1Count)*1.0+(a.Score2Count)*2.0+(a.Score3Count)*3.0+(a.Score4Count)*4.0+(a.Score5Count)*5.0)/a.CommentCount*1.0/c.rating_score as rating_score_diff,
(a.Score4Count+a.Score5Count)/a.CommentCount as rate_good,
c.rate_good as category_rate_good,
Format(((a.Score4Count+a.Score5Count)/a.CommentCount - c.rate_good)*100/c.rate_good,1) as rate_good_diff,
(a.Score1Count+a.Score2Count)/a.CommentCount as rate_bad,
c.rate_bad as category_rate_bad,
Format(((a.Score1Count+a.Score2Count)/a.CommentCount - c.rate_bad)*100/c.rate_bad,1) as rate_bad_diff,
a.Score1Count/a.CommentCount as rate_1,
c.rate_1 as category_rate_1,
Format((a.Score1Count/a.CommentCount - c.rate_1)*100/c.rate_1,1) as rate_1_diff,
a.Score2Count/a.CommentCount as rate_2,
c.rate_2 as category_rate_2,
Format((a.Score2Count/a.CommentCount - c.rate_2)*100/c.rate_2,1) as rate_2_diff,
a.Score3Count/a.CommentCount as rate_3,
c.rate_3 as category_rate_3,
Format((a.Score3Count/a.CommentCount - c.rate_3)*100/c.rate_3,1) as rate_3_diff,
a.Score4Count/a.CommentCount as rate_4,
c.rate_4 as category_rate_4,
Format((a.Score4Count/a.CommentCount - c.rate_4)*100/c.rate_4,1) as rate_4_diff,
a.Score5Count/a.CommentCount as rate_5,
c.rate_5 as category_rate_5,
Format((a.Score5Count/a.CommentCount - c.rate_5)*100/c.rate_5,1) as rate_5_diff,
a.dt as item_origin_dt,
c.dt as category_origin_dt,
c.origin_dt as raw_origin_dt
FROM
jd_item_comment_count_latest a
left JOIN
jd_item_category b
on a.SkuId = b.sku_id and b.sku_id is not NULL
left join
jd_analytic_category_rating_latest c
on b.category_id = c.category_id
left join
jd_category d
on d.id = c.category_id
where a.CommentCount>0
''' %timeHelper.getNow()
# print sql
afr = dbhelper.executeSqlWrite1(sql,is_dirty=True,isolation_type='serializable')
return afr
