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