def update_can_price_parity_status():
sRes = {'code': 0, 'message': ''}
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
sRes['code'] = db_res['errorcode']
sRes['message'] = db_res['errortext']
return sRes
re_set_status_sql = "UPDATE t_aliexpress_online_info SET CanPriceParity=NULL;"
# re_set_status_sql = "UPDATE t_aliexpress_online_info_test0512 SET CanPriceParity=NULL;"
set_status_sql = "UPDATE t_aliexpress_online_info SET CanPriceParity=1 WHERE id IN (SELECT q.id FROM ( " \
"SELECT MAX(id) AS id FROM t_aliexpress_online_info WHERE CONCAT(Orders7Days,MainSKU) IN " \
"(SELECT CONCAT(MAX(Orders7Days),MainSKU) FROM t_aliexpress_online_info WHERE Status='1' " \
"GROUP BY MainSKU ) GROUP BY MainSKU) q);"
# set_status_sql = "UPDATE t_aliexpress_online_info_test0512 SET CanPriceParity=1 WHERE id IN (SELECT q.id FROM ( " \
# "SELECT MAX(id) AS id FROM t_aliexpress_online_info_test0512 WHERE CONCAT(Orders7Days,MainSKU) IN " \
# "(SELECT CONCAT(MAX(Orders7Days),MainSKU) FROM t_aliexpress_online_info_test0512 WHERE Status='1' " \
# "GROUP BY MainSKU ) GROUP BY MainSKU) q);"
try:
reset_infos = execute_db(re_set_status_sql, db_res['db_conn'], 'update')
set_infos = execute_db(set_status_sql, db_res['db_conn'], 'update')
if reset_infos['code'] != 0:
sRes['code'] = reset_infos['code']
sRes['message'] += 'reset CanPriceParity all null error: ' + reset_infos['message']
if set_infos['code'] != 0:
sRes['code'] = set_infos['code']
sRes['message'] += 'set CanPriceParity 1 error: ' + set_infos['message']
except Exception as e:
sRes['code'] = -1
sRes['message'] = str(e)
return sRes
def get_image_by_sku(sku, db_conn):
sql = "SELECT Bmpurl FROM py_db.b_goods WHERE SKU='%s';" % sku
res = execute_db(sql, db_conn, 'select')
if res:
image = res[0]['Bmpurl']
else:
image = None
return image
def getlogbyproductid(self, product_id):
product_sql = "SELECT ShopSKU, OldPrice, Discount, ShopName FROM t_joom_cutprice_log WHERE ProductID='%s'" % product_id
product_res = execute_db(product_sql, self.db_conn, 'select')
if not product_res:
res = t_joom_cutprice_log_table.objects.filter(
ProductID=product_id).values('ShopSKU', 'OldPrice', 'Discount')
product_res = res
return product_res
def update_can_price_parity_status():
sRes = {'code': 0, 'message': ''}
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
sRes['code'] = db_res['errorcode']
sRes['message'] = db_res['errortext']
return sRes
re_set_status_sql = "UPDATE t_online_info_joom SET CanPriceParity=NULL;"
set_status_sql = "UPDATE t_online_info_joom SET CanPriceParity=1 WHERE id IN (SELECT q.id FROM ( " \
"SELECT MAX(a.id) AS id FROM t_online_info_joom a, (SELECT MAX(b.Orders7Days) AS maxorder7days, " \
"b.MainSKU FROM t_online_info_joom AS b WHERE b.MainSKU IS NOT NULL AND b.MainSKU<>'' AND " \
"b.`Status`='1' AND b.ReviewState='0' AND b.cutprice_flag<>1 GROUP BY b.MainSKU) c WHERE " \
"a.MainSKU=c.MainSKU AND a.Orders7Days=c.maxorder7days GROUP BY a.MainSKU) q);"
# re_set_status_sql = "UPDATE t_online_info_joom_test_sj SET CanPriceParity=NULL;"
# set_status_sql = "UPDATE t_online_info_joom_test_sj SET CanPriceParity=1 WHERE id IN (SELECT q.id FROM ( " \
# "SELECT MAX(a.id) AS id FROM t_online_info_joom_test_sj a, (SELECT MAX(b.Orders7Days) AS maxorder7days, " \
# "b.MainSKU FROM t_online_info_joom_test_sj AS b WHERE b.MainSKU IS NOT NULL AND b.MainSKU<>'' AND " \
# "b.`Status`='1' AND b.ReviewState='0' AND b.cutprice_flag<>1 GROUP BY b.MainSKU) c WHERE " \
# "a.MainSKU=c.MainSKU AND a.Orders7Days=c.maxorder7days GROUP BY a.MainSKU) q);"
try:
reset_infos = execute_db(re_set_status_sql, db_res['db_conn'],
'update')
set_infos = execute_db(set_status_sql, db_res['db_conn'], 'update')
if reset_infos['code'] != 0:
sRes['code'] = reset_infos['code']
sRes[
'message'] += 'reset CanPriceParity all null error: ' + reset_infos[
'message']
if set_infos['code'] != 0:
sRes['code'] = set_infos['code']
sRes['message'] += 'set CanPriceParity 1 error: ' + set_infos[
'message']
except Exception as e:
sRes['code'] = -1
sRes['message'] = str(e)
return sRes
def getalljoomauth(self):
sql = "SELECT IP, `ShopName`, K, V FROM t_config_online_joom;"
res = execute_db(sql, self.db_conn, 'select')
auth_infos = dict()
for i in res:
if auth_infos.get(i['ShopName']):
auth_infos[i['ShopName']]['IP'] = i['IP']
auth_infos[i['ShopName']][i['K']] = i['V']
else:
auth_infos[i['ShopName']] = dict()
auth_infos[i['ShopName']]['ShopName'] = i['ShopName']
auth_info_list = list()
for i in auth_infos.keys():
auth_info_list.append(auth_infos[i])
return auth_info_list
def get_rabbitmq_info_by_name_platform(self, Name, PlatformName):
sql = "SELECT IP, K, V FROM t_config_mq_info WHERE Name='%s' AND PlatformName='%s';" % (Name, PlatformName)
res = execute_db(sql, self.cnxn, 'select')
RABBITMQ = dict()
for i in res:
if i.get('K') == 'MQPort':
RABBITMQ['port'] = i.get('V')
elif i.get('K') == 'MQUser':
RABBITMQ['username'] = i.get('V')
elif i.get('K') == 'MQPassword':
RABBITMQ['password'] = i.get('V')
else:
pass
RABBITMQ['hostname'] = i.get('IP')
return RABBITMQ
def get_duplicate_pros_by_sevenordernum(self):
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
return
# sql = "SELECT id FROM (SELECT id, MainSKU FROM t_aliexpress_online_info AS a WHERE Orders7Days=(SELECT MAX(b.Orders7Days) FROM " \
# "t_aliexpress_online_info AS b WHERE b.MainSKU IS NOT NULL AND b.MainSKU<>'' AND a.MainSKU = b.MainSKU AND b.`Status`='True' AND " \
# "b.ReviewState='approved')) AS c GROUP BY MainSKU"
sql = "SELECT max(a.id) AS id FROM t_aliexpress_online_info a,( SELECT MAX(b.Orders7Days) AS maxorder7days,b.MainSKU " \
"FROM t_aliexpress_online_info AS b WHERE b.MainSKU IS NOT NULL AND b.MainSKU <> '' AND b.`Status` = 'True' " \
"GROUP BY b.MainSKU) c WHERE a.MainSKU=c.MainSKU AND a.Orders7Days=c.maxorder7days GROUP BY a.MainSKU"
duplicate_infos = execute_db(sql, db_res['db_conn'], 'select')
ids = list()
for i in duplicate_infos:
ids.append(i['id'])
return ids
def get_all_source_products(self):
sql = "SELECT id, ShopName, ProductsInfo FROM t_joom_source_products WHERE flag=0 LIMIT 1000;"
res = execute_db(sql, self.db_conn, 'select')
return res
def delete_info(self):
sql = "DELETE FROM t_joom_source_products WHERE flag=1;"
execute_db(sql, self.db_conn, 'delete')
def set_hanle_over(self, source_id):
sql = "UPDATE t_joom_source_products SET flag=1 WHERE id=%s;" % source_id
execute_db(sql, self.db_conn, 'update')
def get_cutpricing_products(self):
cutprice_products_sql = "SELECT * FROM t_joom_cutprice_log WHERE RecoverResult IS NULL OR RecoverResult='SOME SUCCESS' OR RecoverResult='ALL FALIED';"
products = execute_db(cutprice_products_sql, self.db_conn, 'select')
return products
def get_joom_products_by_productids(self, product_ids):
joom_products = "SELECT ProductID, OfSales FROM t_online_info_joom WHERE ProductID IN (%s);" % product_ids
joom_products_info = execute_db(joom_products, self.db_conn, 'select')
return joom_products_info
def set_joom_ratingvalue(self, product_id, ratingValue):
sql_orders = "UPDATE t_online_info_joom SET ratingValue=%s WHERE ProductID='%s';" % (ratingValue, product_id)
res = execute_db(sql_orders, self.db_conn, 'update')
return res
def set_joom_seven_orders(self, sevenorders, product_id):
sql_orders = "UPDATE t_online_info_joom SET Orders7Days=%s WHERE ProductID='%s';" % (sevenorders, product_id)
res = execute_db(sql_orders, self.db_conn, 'update')
return res
def set_joom_products_cut_price_status(self, products_id, status):
joom_status = "UPDATE t_online_info_joom SET cutprice_flag=%s WHERE ProductID='%s'" % (status, products_id)
res = execute_db(joom_status, self.db_conn, 'update')
return res