def get_price_and_weight_color_by_sku(self,all_skus, params):
import re
if len(all_skus) > 1:
color_map = 'Mixed Color'
else:
sku_temp = all_skus.keys()[0]
if sku_temp[-3:].isalnum():
sub_temp = re.sub(r'([\d]+)', '', sku_temp[-5:])
t_sku_color_obj = t_sku_color(connection)
color_map = t_sku_color_obj.get_color_name_us_by_sku(sub_temp)
if color_map:
color_map = color_map.get('color_name_us','')
else:
color_map = 'Mixed Color'
else:
color_map = 'Mixed Color'
weight = 0
price = decimal.Decimal("%.2f" % float(0))
IsCharged = 0
for sku, sku_values in all_skus.items():
each_weight = classsku(db_cnxn=connection).get_weight_by_sku(sku)
each_price = classsku(db_cnxn=connection).get_price_by_sku(sku)
if each_weight:
weight += int(float(each_weight) * float(sku_values))
if each_price:
price += decimal.Decimal("%.2f" % float(each_price)) * int(sku_values)
if classsku().get_isCharged_by_sku(sku_values) == '1':
IsCharged = 1
max_price = float(0)
if weight > 0 and price > 0:
# EXCHANGE_RATE 汇率
# TRACK_PRICE_ELEC 带电
# TRACK_DEAL_WEIGHT 处理克重500
# PROFIT_RATE 利润率
# track_price = decimal.Decimal("%.2f" % (params['EXCHANGE_RATE'] * params['TRACK_PRICE_ELEC'])) * weight
# if IsCharged == 1:
# track_price = decimal.Decimal("%.2f" % (params['EXCHANGE_RATE'] * params['TRACK_PRICE_UNELEC'])) * weight
weight_count = int(weight / params['TRACK_DEAL_WEIGHT'])
if (weight - params['TRACK_DEAL_WEIGHT']) % params['TRACK_DEAL_WEIGHT'] == 0:
lastCount = weight_count
else:
lastCount = weight_count + 1
track_deal_price = params['EXCHANGE_RATE'] * lastCount * params['TRACK_DEAL_PRICE']
all_price = (float(track_deal_price) + (1.083325 * float(price) + params['TRACK_PRICE_ELEC'] * weight) * params['EXCHANGE_RATE'] +
float(23.6)) * float(1 + params['PROFIT_RATE']) + (float(price) * params['EXCHANGE_RATE'])
max_price = all_price / (0.6705 - 0.3295 * params['PROFIT_RATE'])
max_price = (int(max_price) / 10) * 10 + 9
# max_price = decimal.Decimal("%.2f" % float(4000))*(track_price + decimal.Decimal("%.2f" % track_deal_price)
# + decimal.Decimal("%.2f" % float(1.103))*price_india
# + decimal.Decimal("%.2f" % float(24.6)))/decimal.Decimal("%.2f" % float(1187))
result_sku_info = {'color': color_map, 'price': max_price, 'weight': weight}
return result_sku_info
def refresh_templet_listing_status():
# connection = connnectToMysql()
cur = connection.cursor()
redis_coon = get_redis_connection(alias='product')
classsku_obj = classsku(connection, redis_coon)
public_info_list = select_public_info(cur)
i = 0
for public_info in public_info_list:
i += 1
update_id, variants, product_id = public_info[0], public_info[1], public_info[2]
print 'public_id--------------------%s' % update_id
print 'before_variants-------------------%s' % variants
online_info_list = select_online_info(cur, product_id)
for online_info in online_info_list:
product_sku = online_info[0]
print 'product_sku--%s' % product_sku
for variant in variants:
if variant['Variant']['productSKU'] == product_sku:
goodsStatus = classsku_obj.get_goodsstatus_by_sku(product_sku)
if goodsStatus == u'正常':
variant['Variant']['enabled'] = True
else:
variant['Variant']['enabled'] = False
update_public_info(cur, update_id, variants)
print 'after_variants-------------------%s' % variants
if i % 500 == 0:
cur.execute('commit;')
cur.execute('commit;')
cur.close()
connection.close()
def show_sevenOrderCount(self,obj) :
from brick.classredis.classsku import classsku
classskuObj = classsku()
sellCount1 = classskuObj.get_sellcount1_by_sku(obj.ProductSKU) # 7天销量
sellCount1 = 0 if (sellCount1 is None or sellCount1 == '') else sellCount1
rt = u'%s'%(sellCount1)
return mark_safe(rt)
def show_status(self,obj):
classskuobjs = classsku(connection, redis_conn)
rt = '<ul>'
ProductSKU_objs = t_product_mainsku_sku.objects.filter(MainSKU=obj.MainSKU).values_list('ProductSKU',flat=True)
for ProductSKU in ProductSKU_objs:
status = classskuobjs.get_goodsstatus_by_sku(ProductSKU)
rt = '%s<li>%s %s</li>'%(rt,ProductSKU,status)
rt = '%s</ul>'%rt
return mark_safe(rt)
def show_canSellCount(self,obj) :
from brick.classredis.classsku import classsku
classskuObj = classsku()
number = classskuObj.get_number_by_sku(obj.ProductSKU)
reservationnum = classskuObj.get_reservationnum_by_sku(obj.ProductSKU)
number = number if (number is not None and number != '') else 0
reservationnum = reservationnum if (reservationnum is not None and reservationnum != '') else 0
canSellCount = int(number) - int(reservationnum)
rt = u'%s'%(canSellCount)
return mark_safe(rt)
def save_models(self,):
try:
obj = self.new_obj
request = self.request
old_obj = None
if obj is None or obj.id is None or obj.id <= 0:
pass
else:
old_obj = self.model.objects.get(pk=obj.pk)
obj.save()
obj.Stocking_plan_number = ddtime.now().strftime('%Y%m%d%H%M%S') + '_' + str(obj.id)
obj.Stock_plan_date = ddtime.now()
obj.OplogTime = ddtime.now()
obj.Demand_people = request.user.first_name
py_b_goods_objs = py_b_goods.objects.filter(SKU=obj.ProductSKU)
if py_b_goods_objs.exists():
obj.ProductImage = u'http://fancyqube.net:89/ShopElf/images/%s.jpg'%py_b_goods_objs[0].SKU.replace('OAS-','').replace('FBA-','')
obj.ProductName = py_b_goods_objs[0].GoodsName
obj.ProductPrice = py_b_goods_objs[0].CostPrice
obj.ProductWeight= py_b_goods_objs[0].Weight
from brick.classredis.classsku import classsku
classskuObj = classsku()
sellCount1 = classskuObj.get_sellcount1_by_sku(obj.ProductSKU) # 7天销量
number = classskuObj.get_number_by_sku(obj.ProductSKU)
reservationnum = classskuObj.get_reservationnum_by_sku(obj.ProductSKU)
packInfo = classskuObj.get_packinfo_by_sku(obj.ProductSKU)
position = classskuObj.get_location_by_sku(obj.ProductSKU)
obj.servenOrder = 0 if (sellCount1 is None or sellCount1 == '') else sellCount1
number = number if (number is not None and number != '') else 0
reservationnum = reservationnum if (reservationnum is not None and reservationnum != '') else 0
obj.canSellCount = int(number) - int(reservationnum)
obj.packFormat = '' if packInfo is None else packInfo
obj.position = '' if position is None else position
obj.DemandMoney = int(obj.Stocking_quantity) * float(obj.ProductPrice)
obj.DeliverMoney = int(obj.Stocking_quantity) * float(obj.ProductPrice)
obj.Status = u'notyet' # 未生成采购计划
obj.QTY = obj.Stocking_quantity
obj.save()
except Exception, ex:
messages.info(self.request,"保存报错:%s"%(str(ex)))
def __private_base_judge_tort(self,sku,platform):
#判断子SKU状态
classsku_obj = classsku(self.db_conn,self.redis_conn)
goodsstatus = classsku_obj.get_goodsstatus_by_sku(sku)
if goodsstatus is None or goodsstatus.strip() != u'正常':
return False
#判断是否侵权
classmainsku_obj = classmainsku(self.db_conn,self.redis_conn)
mainsku = classsku_obj.get_bemainsku_by_sku(sku)
skutort = None
if mainsku is not None and mainsku.strip() != '':
skutort = classmainsku_obj.get_tort_by_mainsku(mainsku)
if skutort is not None and platform in skutort:
return False
else:
return True
def handle(self, *args, **options):
start_time = datetime.datetime.now()
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
return
db_conn = db_res['db_conn']
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
num = t_aliexpress_online_info.objects.filter(Status='1').count()
num = int(num / 1000) + 1
for i in range(num):
start_num = i * 1000
i += 1
end_num = i * 1000
print '==============handle start num range, start_num: %s, end_num: %s' % (start_num, end_num)
products_info = t_aliexpress_online_info.objects.filter(Status='1')[start_num:end_num]
for i in products_info:
pro_detail = t_aliexpress_online_info_detail.objects.filter(ProductID=i.ProductID)
# if i.SKU:
# pro_detail = i.SKU.split(',')
# else:
# continue
weight_list = list()
for j in pro_detail:
sku = j.SKU
# sku = j
weight = classsku_obj.get_weight_by_sku(sku)
j.Weight = weight
j.save()
weight_list.append(weight)
weight_list.sort()
min_weight = weight_list[0]
i.Weight = min_weight
i.save()
print '==============handle over num range, start_num: %s, end_num: %s' % (start_num, end_num)
# if i > 5:
# break
end_time = datetime.datetime.now()
print 'handle time: %s' % (end_time - start_time).total_seconds()
def update_shopee_info(shopname='',
partner_id='',
shopid='',
flag='',
opid=''):
# flag=1是增量同步店铺，为0或无值为全量更新
db_conn = connection
cursor = db_conn.cursor()
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
classskuobjs = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
shopee_p = Shopee_Public_API(shopname)
item_data = dict()
item_data['cursor'] = cursor
item_data['datas'] = list()
if opid != '':
t_shopee_oplogs_obj = t_shopee_oplogs(connection)
error_data = dict()
error_data['cursor'] = cursor
error_data['datas'] = list()
item_detail_data = dict()
item_detail_data['cursor'] = cursor
item_detail_data['datas'] = list()
if flag == '1':
now = datetime.datetime.now()
last_time = now + datetime.timedelta(days=-15)
count = 0
while True:
timestamp = int(time.time())
pagination_offset = count
pagination_entries_per_page = 100
update_time_from = None
update_time_to = None
if flag == '1':
get_shopname_time(cursor, partner_id, shopname, shopid)
# api接口给的最大时间差就是15天，所以已最大时间差取增量更新
update_time_from = int(time.mktime(last_time.timetuple()))
update_time_to = int(time.mktime(now.timetuple()))
try:
Item_data = shopee_p.item_GetItemsList(
pagination_offset, pagination_entries_per_page, partner_id,
shopid, timestamp, update_time_from, update_time_to)
except Exception as e:
print "item_GetItemsList获取数据报错：shopid: %s, partner_id: %s" % (
shopid, partner_id)
print repr(e)
if opid != '':
isResult = t_shopee_oplogs_obj.MoreupdateNum(opid, repr(e))
assert isResult['errorcode'] == 0, "upload log error."
break
try:
Item_data = json.loads(Item_data.content)
except Exception as e:
print "item_GetItemsList报错：shopid: %s, partner_id: %s" % (
shopid, partner_id)
print Item_data.content
if opid != '':
isResult = t_shopee_oplogs_obj.MoreupdateNum(opid, repr(e))
assert isResult['errorcode'] == 0, "upload log error."
break
try:
more = Item_data['more']
Item_data = Item_data['items']
except Exception as e:
print repr(e)
print Item_data
print "shopid: %s, partner_id: %s, error: %s" % (
shopid, partner_id, repr(e))
if opid != '':
isResult = t_shopee_oplogs_obj.MoreupdateNum(opid, repr(e))
assert isResult['errorcode'] == 0, "upload log error."
break
for i in Item_data:
item_dict = dict()
item_id = i.get('item_id')
item_dict['ItemID'] = item_id
item_dict['Shopid'] = i.get('shopid')
item_dict['ShopName'] = shopname
item_dict['ItemSKU'] = i.get('item_sku')
sku = classshopsku_obj.getskueach(item_dict['ItemSKU'])
item_dict['SKU'] = sku
item_dict['MainSKU'] = classsku_obj.get_bemainsku_by_sku(sku)
seller = ''
published = ''
try:
shopee_info = t_store_configuration_file.objects.get(
ShopName=shopname)
seller = shopee_info.Seller
published = shopee_info.Published
except t_store_configuration_file.DoesNotExist:
pass
item_dict['Seller'] = seller
item_dict['Published'] = published
# 变体数据
timestamp2 = int(time.time())
try:
var_data = shopee_p.item_GetItemDetail(item_id, partner_id,
shopid, timestamp2)
except Exception as e:
print "item_GetItemDetail获取数据报错：shopid: %s, partner_id: %s, item_id: %s" % (
shopid, partner_id, item_id)
print repr(e)
error_dict = dict()
error_dict['ShopName'] = shopname
error_dict['Shopid'] = shopid
error_dict['ItemID'] = item_id
error_dict['RefreshTime'] = datetime.datetime.now()
error_dict['Description'] = repr(e)
error_dict['Status'] = '-1'
if error_dict:
save_error_info_detail2(**error_dict)
continue
try:
var_data = json.loads(var_data.content)
except Exception as e:
print "item_GetItemDetail报错：shopid: %s, partner_id: %s, item_id: %s" % (
shopid, partner_id, item_id)
print var_data.content
error_dict = dict()
error_dict['ShopName'] = shopname
error_dict['Shopid'] = shopid
error_dict['ItemID'] = item_id
error_dict['RefreshTime'] = datetime.datetime.now()
error_dict['Description'] = repr(e)
error_dict['Status'] = '-1'
if error_dict:
save_error_info_detail2(**error_dict)
continue
try:
var_d = var_data['item']
except Exception as e:
print "shopid: %s, partner_id: %s, item_id: %s, error: %s" % (
shopid, partner_id, item_id, repr(e))
if var_data.get('error') == 'error_auth':
delete_ItemID(item_id, shopid)
continue
error_dict = dict()
error_dict['ShopName'] = shopname
error_dict['Shopid'] = shopid
error_dict['ItemID'] = item_id
error_dict['ItemSKU'] = item_dict['ItemSKU']
error_dict['SKU'] = item_dict['SKU']
error_dict['MainSKU'] = item_dict['MainSKU']
error_dict['RefreshTime'] = datetime.datetime.now()
error_dict['Description'] = json.dumps(
var_data) + '||||' + repr(e)
error_dict['Status'] = '-1'
error_dict_tuple = (
error_dict['ShopName'], error_dict['Shopid'],
error_dict['ItemID'], error_dict['ItemSKU'],
error_dict['SKU'], error_dict['MainSKU'],
error_dict['RefreshTime'], error_dict['Description'],
error_dict['Status'], error_dict['ShopName'],
error_dict['Shopid'], error_dict['SKU'],
error_dict['MainSKU'], error_dict['RefreshTime'],
error_dict['Description'], error_dict['Status'])
error_data['datas'].append(error_dict_tuple)
if error_data['datas']:
save_error_info_detail(**error_data)
continue
if var_d:
item_dict['Price'] = var_d.get('price')
item_dict['Original_price'] = var_d.get('original_price')
item_dict['PackageWidth'] = var_d.get('package_width')
item_dict['PackageLength'] = var_d.get('package_length')
item_dict['PackageHeight'] = var_d.get('package_height')
item_dict['CmtCount'] = var_d.get('cmt_count')
item_dict['Conditions'] = var_d.get('condition')
item_dict['SizeChart'] = var_d.get('size_chart')
item_dict['Currency'] = var_d.get('currency').replace("'", "")
item_dict['Weight'] = var_d.get('weight')
item_dict['Likes'] = var_d.get('likes')
item_dict['Image'] = var_d.get('images')[0] if var_d.get(
'images') else ''
item_dict['ExtraImages'] = ','.join(
var_d.get('images')) if var_d.get('images') else ''
item_dict['Views'] = var_d.get('views')
item_dict['DaysToShip'] = var_d.get('days_to_ship')
item_dict['HasVariation'] = var_d.get('has_variation')
item_dict['RatingStar'] = var_d.get('rating_star')
item_dict['Sales'] = var_d.get('sales')
item_dict['Title'] = var_d.get('name')
item_dict['CategoryId'] = var_d.get('category_id')
item_dict['DateUploaded'] = var_d.get('create_time')
if item_dict['DateUploaded']:
item_dict['DateUploaded'] = (
datetime.datetime.utcfromtimestamp(
var_d.get('create_time')) + datetime.timedelta(
hours=8)).strftime("%Y-%m-%d %H:%M:%S")
item_dict['LastUpdated'] = var_d.get('update_time')
if item_dict['LastUpdated']:
item_dict['LastUpdated'] = (
datetime.datetime.utcfromtimestamp(
var_d.get('update_time')) + datetime.timedelta(
hours=8)).strftime("%Y-%m-%d %H:%M:%S")
item_dict['RefreshTime'] = datetime.datetime.now()
item_dict['Stock'] = var_d.get('stock')
item_dict['Description'] = var_d.get('description')
if var_d.get('status') == 'NORMAL':
item_dict['Status'] = '1'
elif var_d.get('status') == 'BANNED':
item_dict['Status'] = '0'
elif var_d.get('Status') == 'DELETED':
item_dict['Status'] = '2'
else:
item_dict['Status'] = var_d.get('status')
orders7Days = 0
Item_Shopstatus = []
for v_d_v in var_d.get('variations', []):
var_dict = dict()
var_dict['ItemID'] = item_id
var_dict['VariantID'] = v_d_v.get('variation_id')
var_dict['ItemSKU'] = var_d.get('item_sku')
orders7Days_shupsku = classsku_obj.get_shopsevensale_by_sku(
v_d_v.get('variation_sku'))
if orders7Days_shupsku:
try:
orders7Days += int(orders7Days_shupsku)
except:
pass
var_dict['VariationSKU'] = v_d_v.get('variation_sku')
sku = classshopsku_obj.getskueach(var_dict['VariationSKU'])
var_dict['SKU'] = sku
var_dict['MainSKU'] = classsku_obj.get_bemainsku_by_sku(
sku)
var_dict[
'Shopstatus'] = classskuobjs.get_goodsstatus_by_sku(
sku)
if var_dict['Shopstatus']:
Item_Shopstatus.append(var_dict['Shopstatus'])
var_dict['Price'] = v_d_v.get('price')
var_dict['Original_price'] = v_d_v.get('original_price')
var_dict['Stock'] = v_d_v.get('stock')
var_dict['Different'] = v_d_v.get('name')
if v_d_v.get('status') == 'MODEL_NORMAL':
var_dict['Status'] = '1'
elif v_d_v.get('status') == 'MODEL_DELETED':
var_dict['Status'] = '0'
else:
var_dict['Status'] = v_d_v.get('status')
var_dict['CreateTime'] = v_d_v.get('create_time')
if var_dict['CreateTime']:
var_dict['CreateTime'] = (
datetime.datetime.utcfromtimestamp(
v_d_v.get('create_time')) + datetime.timedelta(
hours=8)).strftime("%Y-%m-%d %H:%M:%S")
var_dict['UpdateTime'] = v_d_v.get('update_time')
if var_dict['UpdateTime']:
var_dict['UpdateTime'] = (
datetime.datetime.utcfromtimestamp(
v_d_v.get('update_time')) + datetime.timedelta(
hours=8)).strftime("%Y-%m-%d %H:%M:%S")
try:
get_num = get_AvailableNum(var_dict['VariantID'],
item_id, var_dict['SKU'],
var_dict['VariationSKU'])
var_dict['AvailableNum'] = get_num[0]
var_dict['SellDays'] = get_num[1]
except Exception as e:
var_dict['AvailableNum'] = ''
var_dict['SellDays'] = ''
var_dict_tuple = (
var_dict['ItemID'], var_dict['VariantID'],
var_dict['SKU'], var_dict['MainSKU'],
var_dict['ItemSKU'], var_dict['VariationSKU'],
var_dict['Price'], var_dict['Original_price'],
var_dict['Stock'], var_dict['Different'],
var_dict['Status'], var_dict['CreateTime'],
var_dict['UpdateTime'], var_dict['Shopstatus'],
var_dict['AvailableNum'], var_dict['SellDays'],
var_dict['ItemID'], var_dict['ItemSKU'],
var_dict['Price'], var_dict['Original_price'],
var_dict['Stock'], var_dict['Different'],
var_dict['Status'], var_dict['CreateTime'],
var_dict['UpdateTime'], var_dict['Shopstatus'],
var_dict['AvailableNum'], var_dict['SellDays'])
item_detail_data['datas'].append(var_dict_tuple)
if len(item_detail_data['datas']) > 0:
save_variant_infos(**item_detail_data)
item_detail_data['datas'] = []
else:
# print "shopid: %s, partner_id: %s, item_id: %s" % (shopid, partner_id, item_id)
# error_dict = dict()
# error_dict['ShopName'] = shopname
# error_dict['Shopid'] = shopid
# error_dict['ItemID'] = item_id
# error_dict['ItemSKU'] = item_dict['ItemSKU']
# error_dict['SKU'] = item_dict['SKU']
# error_dict['MainSKU'] = item_dict['MainSKU']
# error_dict['RefreshTime'] = datetime.datetime.now()
# error_dict['Description'] = u"暂没有商品信息"
# error_dict['Status'] = '-1'
# error_dict_tuple = (
# error_dict['ShopName'], error_dict['Shopid'], error_dict['ItemID'], error_dict['ItemSKU'], error_dict['SKU'],
# error_dict['MainSKU'], error_dict['RefreshTime'], error_dict['Description'], error_dict['Status'],
# error_dict['ShopName'], error_dict['Shopid'], error_dict['SKU'], error_dict['MainSKU'], error_dict['RefreshTime'],
# error_dict['Description'], error_dict['Status']
# )
# error_data['datas'].append(error_dict_tuple)
# if error_data['datas']:
# save_error_info_detail(**error_data)
delete_ItemID(item_id, shopid)
continue
item_dict['Orders7Days'] = orders7Days
if not Item_Shopstatus:
if int(item_dict['HasVariation']
) == 0 or item_dict['HasVariation'] == False:
Item_Shopstatus.append(
classskuobjs.get_goodsstatus_by_sku(item_dict['SKU']))
item_dict['Shopstatus'] = ','.join(Item_Shopstatus)
item_dict_tuple = (
item_dict['ItemID'], item_dict['Shopid'],
item_dict['ShopName'], item_dict['SKU'], item_dict['MainSKU'],
item_dict['ItemSKU'], item_dict['Seller'],
item_dict['Published'], item_dict['Price'],
item_dict['Original_price'], item_dict['PackageWidth'],
item_dict['PackageLength'], item_dict['PackageHeight'],
item_dict['CmtCount'], item_dict['Conditions'],
item_dict['SizeChart'], item_dict['Currency'],
item_dict['Weight'], item_dict['Likes'], item_dict['Image'],
item_dict['ExtraImages'], item_dict['Views'],
item_dict['DaysToShip'], item_dict['HasVariation'],
item_dict['RatingStar'], item_dict['Sales'],
item_dict['Title'], item_dict['CategoryId'],
item_dict['DateUploaded'], item_dict['LastUpdated'],
item_dict['RefreshTime'], item_dict['Stock'],
item_dict['Description'], item_dict['Status'],
item_dict['Orders7Days'], item_dict['Shopstatus'],
item_dict['Shopid'], item_dict['ShopName'], item_dict['SKU'],
item_dict['MainSKU'], item_dict['ItemSKU'],
item_dict['Seller'], item_dict['Published'],
item_dict['Price'], item_dict['Original_price'],
item_dict['PackageWidth'], item_dict['PackageLength'],
item_dict['PackageHeight'], item_dict['CmtCount'],
item_dict['Conditions'], item_dict['SizeChart'],
item_dict['Currency'], item_dict['Weight'], item_dict['Likes'],
item_dict['Image'], item_dict['ExtraImages'],
item_dict['Views'], item_dict['DaysToShip'],
item_dict['HasVariation'], item_dict['RatingStar'],
item_dict['Sales'], item_dict['Title'],
item_dict['CategoryId'], item_dict['DateUploaded'],
item_dict['LastUpdated'], item_dict['RefreshTime'],
item_dict['Stock'], item_dict['Description'],
item_dict['Status'], item_dict['Orders7Days'],
item_dict['Shopstatus'])
item_data['datas'].append(item_dict_tuple)
save_item_info(**item_data)
if opid != '':
isResult = t_shopee_oplogs_obj.MoreupdateNum(opid)
assert isResult['errorcode'] == 0, "upload log error."
item_data['datas'] = []
count += 100
try:
if not more:
if opid != '':
isResult = t_shopee_oplogs_obj.MoreupdateStatus(
opid, 'over')
assert isResult['errorcode'] == 0, "upload log error."
break
except Exception as e:
if len(Item_data) < 100:
if opid != '':
isResult = t_shopee_oplogs_obj.MoreupdateStatus(
opid, 'over')
assert isResult['errorcode'] == 0, "upload log error."
break
def update_shopee_info_detail(shopname, partner_id, shopid, itemids):
db_conn = connection
cursor = db_conn.cursor()
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
classskuobjs = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
item_data = dict()
item_data['cursor'] = cursor
item_data['datas'] = list()
item_detail_data = dict()
item_detail_data['cursor'] = cursor
item_detail_data['datas'] = list()
shopee_p = Shopee_Public_API(shopname)
for item_id in itemids:
item_dict = dict()
item_dict['ItemID'] = item_id
item_dict['ShopName'] = shopname
item_dict['Shopid'] = shopid
timestamp = int(time.time())
seller = ''
published = ''
try:
shopee_info = t_store_configuration_file.objects.get(
ShopName=shopname)
seller = shopee_info.Seller
published = shopee_info.Published
except t_store_configuration_file.DoesNotExist:
pass
item_dict['Seller'] = seller
item_dict['Published'] = published
try:
var_data = shopee_p.item_GetItemDetail(item_id, partner_id, shopid,
timestamp)
except Exception as e:
print "item_GetItemDetail获取数据报错：shopid: %s, partner_id: %s, item_id: %s" % (
shopid, partner_id, item_id)
print repr(e)
error_dict = dict()
error_dict['ShopName'] = shopname
error_dict['Shopid'] = shopid
error_dict['ItemID'] = item_id
error_dict['RefreshTime'] = datetime.datetime.now()
error_dict['Description'] = repr(e)
error_dict['Status'] = '-1'
if error_dict:
save_error_info_detail2(**error_dict)
continue
try:
var_data = json.loads(var_data.content)
except Exception as e:
print "item_GetItemDetail报错：shopid: %s, partner_id: %s, item_id: %s" % (
shopid, partner_id, item_id)
print var_data.content
error_dict = dict()
error_dict['ShopName'] = shopname
error_dict['Shopid'] = shopid
error_dict['ItemID'] = item_id
error_dict['RefreshTime'] = datetime.datetime.now()
error_dict['Description'] = repr(e)
error_dict['Status'] = '-1'
if error_dict:
save_error_info_detail2(**error_dict)
continue
try:
var_d = var_data['item']
except Exception as e:
print "shopid: %s, partner_id: %s, item_id: %s, error: %s" % (
shopid, partner_id, item_id, repr(e))
if var_data.get('error') == 'error_auth':
delete_ItemID(item_id, shopid)
continue
error_dict = dict()
error_dict['ShopName'] = shopname
error_dict['Shopid'] = shopid
error_dict['ItemID'] = item_id
error_dict['ItemSKU'] = item_dict['ItemSKU']
error_dict['SKU'] = item_dict['SKU']
error_dict['MainSKU'] = item_dict['MainSKU']
error_dict['RefreshTime'] = datetime.datetime.now()
error_dict['Description'] = json.dumps(var_data) + '||||' + repr(e)
error_dict['Status'] = '-1'
error_dict_tuple = (error_dict['ShopName'], error_dict['Shopid'],
error_dict['ItemID'], error_dict['ItemSKU'],
error_dict['SKU'], error_dict['MainSKU'],
error_dict['RefreshTime'],
error_dict['Description'],
error_dict['Status'], error_dict['ShopName'],
error_dict['Shopid'], error_dict['SKU'],
error_dict['MainSKU'],
error_dict['RefreshTime'],
error_dict['Description'],
error_dict['Status'])
error_data['datas'].append(error_dict_tuple)
if error_data['datas']:
save_error_info_detail(**error_data)
continue
if var_d:
item_dict['ItemSKU'] = var_d.get('item_sku')
item_sku = classshopsku_obj.getskueach(item_dict['ItemSKU'])
item_dict['SKU'] = item_sku
item_dict['MainSKU'] = classsku_obj.get_bemainsku_by_sku(item_sku)
item_dict['Price'] = var_d.get('price')
item_dict['Original_price'] = var_d.get('original_price')
item_dict['PackageWidth'] = var_d.get('package_width')
item_dict['PackageLength'] = var_d.get('package_length')
item_dict['PackageHeight'] = var_d.get('package_height')
item_dict['CmtCount'] = var_d.get('cmt_count')
item_dict['Conditions'] = var_d.get('condition')
item_dict['SizeChart'] = var_d.get('size_chart')
item_dict['Currency'] = var_d.get('currency').replace("'", "")
item_dict['Weight'] = var_d.get('weight')
item_dict['Likes'] = var_d.get('likes')
item_dict['Image'] = var_d.get('images')[0] if var_d.get(
'images') else ''
item_dict['ExtraImages'] = ','.join(
var_d.get('images')) if var_d.get('images') else ''
item_dict['Views'] = var_d.get('views')
item_dict['DaysToShip'] = var_d.get('days_to_ship')
item_dict['HasVariation'] = var_d.get('has_variation')
item_dict['RatingStar'] = var_d.get('rating_star')
item_dict['Sales'] = var_d.get('sales')
item_dict['Title'] = var_d.get('name')
item_dict['CategoryId'] = var_d.get('category_id')
item_dict['DateUploaded'] = var_d.get('create_time')
if item_dict['DateUploaded']:
item_dict['DateUploaded'] = (
datetime.datetime.utcfromtimestamp(
var_d.get('create_time')) +
datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
item_dict['LastUpdated'] = var_d.get('update_time')
if item_dict['LastUpdated']:
item_dict['LastUpdated'] = (
datetime.datetime.utcfromtimestamp(
var_d.get('update_time')) +
datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
item_dict['RefreshTime'] = datetime.datetime.now()
item_dict['Stock'] = var_d.get('stock')
item_dict['Description'] = var_d.get('description')
if var_d.get('status') == 'NORMAL':
item_dict['Status'] = '1'
elif var_d.get('status') == 'BANNED':
item_dict['Status'] = '0'
elif var_d.get('status') == 'DELETED':
item_dict['Status'] = '2'
else:
item_dict['Status'] = var_d.get('status')
orders7Days = 0
Item_Shopstatus = []
for v_d_v in var_d.get('variations', []):
var_dict = dict()
var_dict['ItemID'] = item_id
var_dict['VariantID'] = v_d_v.get('variation_id')
var_dict['ItemSKU'] = var_d.get('item_sku')
orders7Days_shupsku = classsku_obj.get_shopsevensale_by_sku(
v_d_v.get('variation_sku'))
if orders7Days_shupsku:
try:
orders7Days += int(orders7Days_shupsku)
except:
pass
var_dict['VariationSKU'] = v_d_v.get('variation_sku')
sku = classshopsku_obj.getskueach(var_dict['VariationSKU'])
var_dict['SKU'] = sku
var_dict['MainSKU'] = classsku_obj.get_bemainsku_by_sku(sku)
var_dict['Shopstatus'] = classskuobjs.get_goodsstatus_by_sku(
sku)
if var_dict['Shopstatus']:
Item_Shopstatus.append(var_dict['Shopstatus'])
var_dict['Price'] = v_d_v.get('price')
var_dict['Original_price'] = v_d_v.get('original_price')
var_dict['Stock'] = v_d_v.get('stock')
var_dict['Different'] = v_d_v.get('name')
if v_d_v.get('status') == 'MODEL_NORMAL':
var_dict['Status'] = '1'
elif v_d_v.get('status') == 'MODEL_DELETED':
var_dict['Status'] = '0'
else:
var_dict['Status'] = v_d_v.get('status')
var_dict['CreateTime'] = v_d_v.get('create_time')
if var_dict['CreateTime']:
var_dict['CreateTime'] = (
datetime.datetime.utcfromtimestamp(
v_d_v.get('create_time')) + datetime.timedelta(
hours=8)).strftime("%Y-%m-%d %H:%M:%S")
var_dict['UpdateTime'] = v_d_v.get('update_time')
if var_dict['UpdateTime']:
var_dict['UpdateTime'] = (
datetime.datetime.utcfromtimestamp(
v_d_v.get('update_time')) + datetime.timedelta(
hours=8)).strftime("%Y-%m-%d %H:%M:%S")
try:
get_num = get_AvailableNum(var_dict['VariantID'], item_id,
var_dict['SKU'],
var_dict['VariationSKU'])
var_dict['AvailableNum'] = get_num[0]
var_dict['SellDays'] = get_num[1]
except Exception as e:
var_dict['AvailableNum'] = ''
var_dict['SellDays'] = ''
var_dict_tuple = (
var_dict['ItemID'], var_dict['VariantID'], var_dict['SKU'],
var_dict['MainSKU'], var_dict['ItemSKU'],
var_dict['VariationSKU'], var_dict['Price'],
var_dict['Original_price'], var_dict['Stock'],
var_dict['Different'], var_dict['Status'],
var_dict['CreateTime'], var_dict['UpdateTime'],
var_dict['Shopstatus'], var_dict['AvailableNum'],
var_dict['SellDays'], var_dict['ItemID'],
var_dict['ItemSKU'], var_dict['Price'],
var_dict['Original_price'], var_dict['Stock'],
var_dict['Different'], var_dict['Status'],
var_dict['CreateTime'], var_dict['UpdateTime'],
var_dict['Shopstatus'], var_dict['AvailableNum'],
var_dict['SellDays'])
item_detail_data['datas'].append(var_dict_tuple)
if len(item_detail_data['datas']) > 0:
save_variant_infos(**item_detail_data)
item_detail_data['datas'] = []
else:
delete_ItemID(item_id, shopid)
continue
item_dict['Orders7Days'] = orders7Days
if not Item_Shopstatus:
if int(item_dict['HasVariation']
) == 0 or item_dict['HasVariation'] == False:
if classskuobjs.get_goodsstatus_by_sku(item_dict['SKU']):
Item_Shopstatus.append(
classskuobjs.get_goodsstatus_by_sku(item_dict['SKU']))
item_dict['Shopstatus'] = ','.join(Item_Shopstatus)
item_dict_tuple = (
item_dict['ItemID'], item_dict['Shopid'], item_dict['ShopName'],
item_dict['SKU'], item_dict['MainSKU'], item_dict['ItemSKU'],
item_dict['Seller'], item_dict['Published'], item_dict['Price'],
item_dict['Original_price'], item_dict['PackageWidth'],
item_dict['PackageLength'], item_dict['PackageHeight'],
item_dict['CmtCount'], item_dict['Conditions'],
item_dict['SizeChart'], item_dict['Currency'], item_dict['Weight'],
item_dict['Likes'], item_dict['Image'], item_dict['ExtraImages'],
item_dict['Views'], item_dict['DaysToShip'],
item_dict['HasVariation'], item_dict['RatingStar'],
item_dict['Sales'], item_dict['Title'], item_dict['CategoryId'],
item_dict['DateUploaded'], item_dict['LastUpdated'],
item_dict['RefreshTime'], item_dict['Stock'],
item_dict['Description'], item_dict['Status'],
item_dict['Orders7Days'], item_dict['Shopstatus'],
item_dict['Shopid'], item_dict['ShopName'], item_dict['SKU'],
item_dict['MainSKU'], item_dict['ItemSKU'], item_dict['Seller'],
item_dict['Published'], item_dict['Price'],
item_dict['Original_price'], item_dict['PackageWidth'],
item_dict['PackageLength'], item_dict['PackageHeight'],
item_dict['CmtCount'], item_dict['Conditions'],
item_dict['SizeChart'], item_dict['Currency'], item_dict['Weight'],
item_dict['Likes'], item_dict['Image'], item_dict['ExtraImages'],
item_dict['Views'], item_dict['DaysToShip'],
item_dict['HasVariation'], item_dict['RatingStar'],
item_dict['Sales'], item_dict['Title'], item_dict['CategoryId'],
item_dict['DateUploaded'], item_dict['LastUpdated'],
item_dict['RefreshTime'], item_dict['Stock'],
item_dict['Description'], item_dict['Status'],
item_dict['Orders7Days'], item_dict['Shopstatus'])
item_data['datas'].append(item_dict_tuple)
save_item_info(**item_data)
item_data['datas'] = []
def update_shopee_info(shopname='', partner_id='', shopid=''):
db_conn = connection
cursor = db_conn.cursor()
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
shopee_p = Shopee_Public_API(shopname)
count = 0
item_data = dict()
item_data['cursor'] = cursor
item_data['datas'] = list()
while True:
if count > 0:
num = count * 100
else:
num = count
timestamp = int(time.time())
pagination_offset = num
pagination_entries_per_page = 100
Item_data = shopee_p.item_GetItemsList(pagination_offset,
pagination_entries_per_page,
partner_id, shopid,
timestamp).json()
try:
more = Item_data['more']
Item_data = Item_data['items']
except Exception as e:
if Item_data.get('error'):
error = Item_data.get('error')
msg = Item_data.get('msg')
print u'partner_id: %s, shopid: %s 错误: %s, 错误信息: %s' % (
str(partner_id), str(shopid), error, msg)
break
else:
print u'partner_id: %s, shopid: %s, pagination_offset: %s 报错' % (
str(partner_id), str(shopid), str(pagination_offset))
with open(BASE_DIR + '/brick/shopee/error_item.txt', 'a') as f:
f.write(str(e) + ' ||| ' + str(pagination_offset) + '\n')
count += 1
continue
for i in Item_data:
item_dict = dict()
item_dict['ItemID'] = i.get('item_id')
item_dict['Shopid'] = i.get('shopid')
item_dict['ShopName'] = shopname
item_dict['LastUpdated'] = i.get('update_time')
if item_dict['LastUpdated']:
item_dict['LastUpdated'] = (
datetime.datetime.utcfromtimestamp(i.get('update_time')) +
datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
item_dict['ItemSKU'] = i.get('item_sku')
sku = classshopsku_obj.getSKU(item_dict['ItemSKU'])
item_dict['SKU'] = sku
item_dict['MainSKU'] = classsku_obj.get_bemainsku_by_sku(sku)
if i.get('status') == 'NORMAL':
item_dict['Status'] = '1'
elif i.get('status') == 'BANNED':
item_dict['Status'] = '0'
else:
item_dict['Status'] = '2'
seller = ''
published = ''
try:
shopee_info = t_store_configuration_file.objects.get(
ShopName=shopname)
seller = shopee_info.Seller
published = shopee_info.Published
except t_store_configuration_file.DoesNotExist:
pass
item_dict['Seller'] = seller
item_dict['Published'] = published
item_dict_tuple = (item_dict['ItemID'], item_dict['Shopid'],
item_dict['ShopName'], item_dict['LastUpdated'],
item_dict['SKU'], item_dict['MainSKU'],
item_dict['ItemSKU'], item_dict['Status'],
item_dict['Seller'], item_dict['Published'],
item_dict['Shopid'], item_dict['ShopName'],
item_dict['LastUpdated'], item_dict['SKU'],
item_dict['MainSKU'], item_dict['ItemSKU'],
item_dict['Status'], item_dict['Seller'],
item_dict['Published'])
item_data['datas'].append(item_dict_tuple)
try:
save_item_info(**item_data)
except Exception as e:
print u'partner_id: %s, shopid: %s, pagination_offset: %s 报错' % (
str(partner_id), str(shopid), str(pagination_offset))
with open(BASE_DIR + '/brick/shopee/error_item.txt', 'a') as f:
f.write(str(e) + ' ||| ' + str(pagination_offset) + '\n')
count += 1
continue
item_data['datas'] = []
count += 1
if not more:
break
def update_else_info():
item_ids = [1376156177]
partner_id = 10041
shopid = 13399960
shopname = 'SHP-002-Kuhong-MY/EB'
timestamp = int(time.time())
db_conn = connection
cursor = db_conn.cursor()
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
shopee_p = Shopee_Public_API(shopname)
for item_id in item_ids:
Item_detail = shopee_p.item_GetItemDetail(item_id, partner_id, shopid,
timestamp)
Item_detail = Item_detail.json()['item']
item_data = dict()
item_data['cursor'] = cursor
item_data['datas'] = list()
item_detail_data = dict()
item_detail_data['cursor'] = cursor
item_detail_data['datas'] = list()
item_dict = dict()
item_dict['Price'] = Item_detail.get('price')
item_dict['Original_price'] = Item_detail.get('original_price')
item_dict['PackageWidth'] = Item_detail.get('package_width')
item_dict['PackageLength'] = Item_detail.get('package_length')
item_dict['PackageHeight'] = Item_detail.get('package_height')
item_dict['CmtCount'] = Item_detail.get('cmt_count')
item_dict['Conditions'] = Item_detail.get('condition')
item_dict['SizeChart'] = Item_detail.get('size_chart')
item_dict['Currency'] = Item_detail.get('currency')
item_dict['Weight'] = Item_detail.get('weight')
item_dict['Likes'] = Item_detail.get('likes')
item_dict['Image'] = Item_detail.get('images')[0] if Item_detail.get(
'images') else ''
item_dict['ExtraImages'] = ','.join(
Item_detail.get('images')) if Item_detail.get('images') else ''
item_dict['Views'] = Item_detail.get('views')
item_dict['DaysToShip'] = Item_detail.get('days_to_ship')
item_dict['HasVariation'] = Item_detail.get('has_variation')
item_dict['RatingStar'] = Item_detail.get('rating_star')
item_dict['Sales'] = Item_detail.get('sales')
item_dict['Title'] = Item_detail.get('name')
item_dict['CategoryId'] = Item_detail.get('category_id')
item_dict['DateUploaded'] = Item_detail.get('create_time')
if item_dict['DateUploaded']:
item_dict['DateUploaded'] = (
datetime.datetime.utcfromtimestamp(
Item_detail.get('create_time')) +
datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
item_dict['LastUpdated'] = Item_detail.get('update_time')
if item_dict['LastUpdated']:
item_dict['LastUpdated'] = (
datetime.datetime.utcfromtimestamp(
Item_detail.get('update_time')) +
datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
item_dict['RefreshTime'] = datetime.datetime.now()
item_dict['Stock'] = Item_detail.get('stock')
item_dict['Description'] = Item_detail.get('description')
if Item_detail.get('status') == 'NORMAL':
item_dict['Status'] = '1'
elif Item_detail.get('status') == 'BANNED':
item_dict['Status'] = '0'
else:
item_dict['Status'] = '2'
orders7Days = 0
for i_d_v in Item_detail['variations']:
var_dict = dict()
var_dict['ItemID'] = item_id
var_dict['VariantID'] = i_d_v.get('variation_id')
var_dict['ItemSKU'] = i['ItemSKU']
orders7Days_shupsku = classsku_obj.get_shopsevensale_by_sku(
i_d_v.get('variation_sku'))
if orders7Days_shupsku:
try:
orders7Days += int(orders7Days_shupsku)
except:
pass
var_dict['VariationSKU'] = i_d_v.get('variation_sku')
sku = classshopsku_obj.getSKU(var_dict['VariationSKU'])
var_dict['SKU'] = sku
var_dict['MainSKU'] = classsku_obj.get_bemainsku_by_sku(sku)
var_dict['Price'] = i_d_v.get('price')
var_dict['Original_price'] = i_d_v.get('original_price')
var_dict['Stock'] = i_d_v.get('stock')
var_dict['Different'] = i_d_v.get('name')
if i_d_v.get('status') == 'MODEL_NORMAL':
var_dict['Status'] = '1'
elif i_d_v.get('status') == 'MODEL_DELETED':
var_dict['Status'] = '0'
var_dict['CreateTime'] = i_d_v.get('create_time')
if var_dict['CreateTime']:
var_dict['CreateTime'] = (
datetime.datetime.utcfromtimestamp(
i_d_v.get('create_time')) +
datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
var_dict['UpdateTime'] = i_d_v.get('update_time')
if var_dict['UpdateTime']:
var_dict['UpdateTime'] = (
datetime.datetime.utcfromtimestamp(
i_d_v.get('update_time')) +
datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
var_dict_tuple = (var_dict['ItemID'], var_dict['VariantID'],
var_dict['SKU'], var_dict['MainSKU'],
var_dict['ItemSKU'], var_dict['VariationSKU'],
var_dict['Price'], var_dict['Original_price'],
var_dict['Stock'], var_dict['Different'],
var_dict['Status'], var_dict['CreateTime'],
var_dict['UpdateTime'], var_dict['Price'],
var_dict['Original_price'], var_dict['Stock'],
var_dict['Different'], var_dict['Status'],
var_dict['CreateTime'], var_dict['UpdateTime'])
item_detail_data['datas'].append(var_dict_tuple)
try:
save_variant_infos(**item_detail_data)
except Exception as e:
print u"产品ID为: %s, 新增 t_shopee_online_detail 表报错; %s" % (
str(item_id), str(e))
with open(BASE_DIR + '/brick/shopee/error_info_detail.txt',
'a') as f:
f.write(str(e) + ' ||| ' + str(item_id) + '\n')
continue
item_detail_data['datas'] = []
item_dict['Orders7Days'] = orders7Days
item_dict_tuple = (item_dict['Price'], item_dict['Original_price'],
item_dict['PackageWidth'],
item_dict['PackageLength'],
item_dict['PackageHeight'], item_dict['CmtCount'],
item_dict['Conditions'], item_dict['SizeChart'],
item_dict['Currency'], item_dict['Weight'],
item_dict['Likes'], item_dict['Image'],
item_dict['ExtraImages'], item_dict['Views'],
item_dict['DaysToShip'], item_dict['HasVariation'],
item_dict['RatingStar'], item_dict['Sales'],
item_dict['Title'], item_dict['CategoryId'],
item_dict['DateUploaded'], item_dict['LastUpdated'],
item_dict['RefreshTime'], item_dict['Stock'],
item_dict['Description'], item_dict['Status'],
item_dict['Orders7Days'], item_id)
item_data['datas'].append(item_dict_tuple)
try:
update_item_infos(**item_data)
except Exception as e:
print u"产品ID为: %s, 更新 t_shopee_online_info 表报错; %s" % (
str(item_id), str(e))
with open(BASE_DIR + '/brick/shopee/error_info_detail.txt',
'a') as f:
f.write(str(e) + ' ||| ' + str(item_id) + '\n')
continue
item_data['datas'] = []
def handle_mymall_products(filename_params='', flag='True', shopname=''):
start_read_file_time = datetime.datetime.now()
print '============= start_read_file_time: %s' % start_read_file_time
db_conn = connection
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
if not filename_params:
return {'code': '-1', 'message': ' No file'}
oss_file_obj = get_obj_from_oss(settings.BUCKETNAME_XLS)
oss_res = oss_file_obj.get_obj_from_oss(filename_params)
file_obj = oss_res['result']
if oss_res['errorcode'] != 0 or file_obj == '':
return {'code': '-1', 'message': 'Get oss file Failed'}
# filename = settings.MEDIA_ROOT + 'mymall_salling_product_file/2018/06/mall_mycom_products_1.xlsx'
# if True:
try:
pro_wb = xlrd.open_workbook(filename=None,
file_contents=file_obj.read())
# pro_wb = xlrd.open_workbook(filename=filename)
end_read_file_time = datetime.datetime.now()
print '============= end_read_file_time: %s' % end_read_file_time
print '============= handle_read_file_time: %s' % (
end_read_file_time - start_read_file_time).total_seconds()
start_insert_data_time = datetime.datetime.now()
print '============= start_insert_data_time: %s' % start_insert_data_time
sheet_name = pro_wb.sheet_names()[0]
sheet_obj = pro_wb.sheet_by_name(sheet_name)
nrows = sheet_obj.nrows
heads = sheet_obj.row_values(0)
head_dict = dict()
for k, v in enumerate(heads):
if v in MYMALL_EXCEL_HEADS:
head_dict[v] = k
sku_list = list()
main_sku_list = list()
shop_sku_list = list()
extra_image_list = list()
status_list = list()
cursor = db_conn.cursor()
mm_infos_dict = dict()
mm_infos_detail_dict = dict()
mm_infos_dict['cursor'] = cursor
mm_infos_dict['datas'] = list()
mm_infos_detail_dict['cursor'] = cursor
mm_infos_detail_dict['datas'] = list()
num = 1
while True:
if num + 1 >= nrows:
break
# if num > 10000:
# break
for row in range(num, nrows):
shop_sku = str(sheet_obj.cell_value(row, head_dict['SKU']))
sku = classshopsku_obj.getSKU(shop_sku)
mainsku = classsku_obj.get_bemainsku_by_sku(sku)
weight = classsku_obj.get_weight_by_sku(sku)
enable = str(sheet_obj.cell_value(row, head_dict['enable']))
if enable == '1':
status = enable
else:
status = '0'
try:
quantity = int(
sheet_obj.cell_value(row, head_dict['stock']))
except Exception as e:
print '[x] row: %s, Get MyMall product quantity error: %s' % (
row, e)
quantity = 0
title = str(sheet_obj.cell_value(row, head_dict['name']))
price = str(sheet_obj.cell_value(row, head_dict['price']))
msrp = str(sheet_obj.cell_value(row, head_dict['old_price']))
color = str(sheet_obj.cell_value(row, head_dict['color']))
size = str(sheet_obj.cell_value(row, head_dict['size']))
tags = str(sheet_obj.cell_value(row, head_dict['tags']))
brand = str(sheet_obj.cell_value(row, head_dict['brand']))
UPC = str(sheet_obj.cell_value(row, head_dict['UPC']))
description = str(
sheet_obj.cell_value(row, head_dict['description']))
main_image_url = str(
sheet_obj.cell_value(row, head_dict['main_image_url']))
extra_images_dict = dict()
extra_images_dict['image_url_1'] = str(
sheet_obj.cell_value(row, head_dict['image_url_1']))
extra_images_dict['image_url_2'] = str(
sheet_obj.cell_value(row, head_dict['image_url_2']))
extra_images_dict['image_url_3'] = str(
sheet_obj.cell_value(row, head_dict['image_url_3']))
extra_images_dict['image_url_4'] = str(
sheet_obj.cell_value(row, head_dict['image_url_4']))
extra_images_dict['image_url_5'] = str(
sheet_obj.cell_value(row, head_dict['image_url_5']))
extra_images_dict['image_url_6'] = str(
sheet_obj.cell_value(row, head_dict['image_url_6']))
extra_images_dict['image_url_7'] = str(
sheet_obj.cell_value(row, head_dict['image_url_7']))
extra_images_dict['image_url_8'] = str(
sheet_obj.cell_value(row, head_dict['image_url_8']))
extra_images_dict['image_url_9'] = str(
sheet_obj.cell_value(row, head_dict['image_url_9']))
extra_images_dict['image_url_10'] = str(
sheet_obj.cell_value(row, head_dict['image_url_10']))
shipping_time = str(
sheet_obj.cell_value(row, head_dict['shipping_time']))
shipping_price = str(
sheet_obj.cell_value(row, head_dict['shipping_price']))
landing_page_url = str(
sheet_obj.cell_value(row, head_dict['landing_page_url']))
product_id = str(
sheet_obj.cell_value(row, head_dict['product_id']))
product_variation_id = str(
sheet_obj.cell_value(row,
head_dict['product_variation_id']))
# print '11111111111111111111 shop_sku: %s' % shop_sku
# print '11111111111111111111 enable: %s' % enable
# print '11111111111111111111 title: %s' % title
# print '11111111111111111111 price: %s' % price
# print '11111111111111111111 msrp: %s' % msrp
# print '11111111111111111111 color: %s' % color
# print '11111111111111111111 size: %s' % size
# print '11111111111111111111 tags: %s' % tags
# print '11111111111111111111 brand: %s' % brand
# print '11111111111111111111 UPC: %s' % UPC
# print '11111111111111111111 description: %s' % description
# print '11111111111111111111 main_image_url: %s' % main_image_url
# print '11111111111111111111 extra_images_dict: %s' % extra_images_dict
# print '11111111111111111111 shipping_time: %s' % shipping_time
# print '11111111111111111111 shipping_price: %s' % shipping_price
# print '11111111111111111111 landing_page_url: %s' % landing_page_url
# print '11111111111111111111 product_id: %s' % product_id
# print '11111111111111111111 product_variation_id: %s' % product_variation_id
if not price and not msrp:
continue
elif price or msrp:
try:
float(price)
except Exception as e:
print e
continue
try:
float(msrp)
except Exception as e:
print e
continue
else:
pass
try:
next_product_id = str(
sheet_obj.cell_value(row + 1, head_dict['product_id']))
except Exception as e:
next_product_id = ''
mymall_product_detail_info = dict()
mymall_product_detail_info['ProductID'] = product_id
mymall_product_detail_info['VariantID'] = product_variation_id
mymall_product_detail_info['SKU'] = sku
mymall_product_detail_info['MainSKU'] = mainsku
mymall_product_detail_info['ShopSKU'] = shop_sku
mymall_product_detail_info['Price'] = price
mymall_product_detail_info['Quantity'] = quantity
mymall_product_detail_info['Status'] = status
mymall_product_detail_info['Shipping'] = shipping_price
mymall_product_detail_info['ShippingTime'] = shipping_time
mymall_product_detail_info['Color'] = color
mymall_product_detail_info['Size'] = size
mymall_product_detail_info['Msrp'] = msrp
mymall_product_detail_info['Weight'] = weight
mymall_product_detail_info['cursor'] = cursor
mymall_product_detail_info_tuple = (
mymall_product_detail_info['ProductID'],
mymall_product_detail_info['VariantID'],
mymall_product_detail_info['SKU'],
mymall_product_detail_info['MainSKU'],
mymall_product_detail_info['ShopSKU'],
mymall_product_detail_info['Price'],
mymall_product_detail_info['Quantity'],
mymall_product_detail_info['Status'],
mymall_product_detail_info['Shipping'],
mymall_product_detail_info['ShippingTime'],
mymall_product_detail_info['Color'],
mymall_product_detail_info['Size'],
mymall_product_detail_info['Msrp'],
mymall_product_detail_info['Weight'],
mymall_product_detail_info['Price'],
mymall_product_detail_info['Quantity'],
mymall_product_detail_info['Status'],
mymall_product_detail_info['Shipping'],
mymall_product_detail_info['ShippingTime'],
mymall_product_detail_info['Color'],
mymall_product_detail_info['Size'],
mymall_product_detail_info['Msrp'],
mymall_product_detail_info['Weight'],
)
mm_infos_detail_dict['datas'].append(
mymall_product_detail_info_tuple)
if product_id != next_product_id:
for i in range(1, 11):
image_num = 'image_url_' + str(i)
if extra_images_dict[image_num]:
extra_image_list.append(
extra_images_dict[image_num])
if sku:
sku_list.append(sku)
if mainsku and mainsku not in main_sku_list:
main_sku_list.append(mainsku)
if shop_sku:
shop_sku_list.append(shop_sku)
if status:
status_list.append(status)
if '1' in status_list:
pro_status = '1'
else:
pro_status = '0'
extra_images = ','.join(extra_image_list)
skus = ','.join(sku_list)
mainskus = ','.join(main_sku_list)
shop_skus = ','.join(shop_sku_list)
if len(mainskus) >= 255:
mainskus = mainskus[:250]
mymall_product_info = dict()
mymall_product_info['ProductID'] = product_id
mymall_product_info['ShopName'] = shopname
mymall_product_info['Title'] = title
mymall_product_info['SKU'] = skus
mymall_product_info['MainSKU'] = mainskus
mymall_product_info['ShopSKU'] = shop_skus
mymall_product_info['RefreshTime'] = datetime.datetime.now(
)
mymall_product_info['Tags'] = tags
mymall_product_info['Brand'] = brand
mymall_product_info['Description'] = description
mymall_product_info['LandingPageUrl'] = landing_page_url
mymall_product_info['Upc'] = UPC
mymall_product_info['Image'] = main_image_url
mymall_product_info['ExtraImages'] = extra_images
mymall_product_info['Status'] = pro_status
mymall_product_info['cursor'] = cursor
mymall_product_info_tuple = (
mymall_product_info['ProductID'],
mymall_product_info['ShopName'],
mymall_product_info['Title'],
mymall_product_info['SKU'],
mymall_product_info['MainSKU'],
mymall_product_info['ShopSKU'],
mymall_product_info['RefreshTime'],
mymall_product_info['Tags'],
mymall_product_info['Brand'],
mymall_product_info['Description'],
mymall_product_info['LandingPageUrl'],
mymall_product_info['Upc'],
mymall_product_info['Image'],
mymall_product_info['ExtraImages'],
mymall_product_info['Status'],
mymall_product_info['Title'],
mymall_product_info['RefreshTime'],
mymall_product_info['Tags'],
mymall_product_info['Brand'],
mymall_product_info['Description'],
mymall_product_info['LandingPageUrl'],
mymall_product_info['Upc'],
mymall_product_info['Image'],
mymall_product_info['ExtraImages'],
mymall_product_info['Status'],
)
mm_infos_dict['datas'].append(mymall_product_info_tuple)
# update_mymall_info(**mymall_product_info)
sku_list = []
main_sku_list = []
shop_sku_list = []
extra_image_list = []
status_list = []
if row + 1 == nrows:
num = row + 1
break
if (row - num) > 2000:
num = row + 1
break
else:
if sku:
sku_list.append(sku)
if mainsku and mainsku not in main_sku_list:
main_sku_list.append(mainsku)
if shop_sku:
shop_sku_list.append(shop_sku)
if status:
status_list.append(status)
update_mymall_info(**mm_infos_dict)
update_mymall_info_detail(**mm_infos_detail_dict)
mm_infos_dict['datas'] = []
mm_infos_detail_dict['datas'] = []
cursor.execute('commit;')
cursor.close()
except Exception as e:
return {'code': '-1', 'message': '%s' % str(e)}
end_insert_data_time = datetime.datetime.now()
print '============= end_insert_data_time: %s' % end_insert_data_time
print '============= handle_insert_data_time: %s' % (
end_insert_data_time - start_insert_data_time).total_seconds()
return {'code': '0', 'message': 'SUCCESS'}
"""
from django.db import connection
import time, sys
from random import randint
from datetime import datetime
from brick.public.create_shop_table import create_shop_table
from brick.classredis.classshopsku import classshopsku
from brick.classredis.classsku import classsku
from brick.public.combination_sku import G_ZHSKU
from skuapp.table.t_product_mainsku_sku import t_product_mainsku_sku
from skuapp.table.t_sys_department_staff import t_sys_department_staff
from django_redis import get_redis_connection
redis_coon = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=connection, redis_cnxn=redis_coon)
ABNORMAL_STATUS = [u'停售', u'清仓', u'清仓（合并）', u'']
class ShopskuApply(object):
def __init__(self, cur, shop_name, staff_name, apply_type):
self.cur = cur
self.shop_name = u'%s' % shop_name.strip()
self.shop_name_original = u'%s' % shop_name.strip()
self.staff_name = staff_name
self.apply_type = apply_type
shopname_codelist = self.shop_name.split('-')
if len(shopname_codelist) <= 1:
shopcode = self.shop_name
#!/usr/bin/python
# -*- coding: utf-8 -*-
from brick.db.dbconnect import run
# from brick.db.dbconnect import execute_db
from mymall_app.table.t_mymall_online_info import t_mymall_online_info
import datetime
from brick.classredis.classsku import classsku
classsku_obj = classsku()
def mymall_update_seven_orders():
start = datetime.datetime.now()
print 'start', start
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
return
cursor = db_res['db_conn'].cursor()
clear_sql = "UPDATE t_mymall_online_info SET Orders7Days=NULL"
cursor.execute(clear_sql)
cursor.execute("commit;")
pro_shop_sku = t_mymall_online_info.objects.filter(Status='1').values(
'ProductID', 'ShopSKU', 'ShopName')
counts_num = len(pro_shop_sku) / 10000 + 1
for count_num in range(counts_num):
# if count_num == 1:
# break
start_num = count_num * 10000
def handle_aliexpress_products_new(filename_params='', flag='True', shopname=''):
# Excel not from zip
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
return
db_conn = db_res['db_conn']
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
# classsku_obj = classsku(db_cnxn=db_conn)
# classshopsku_obj = classshopsku(db_conn=db_conn)
# if not filename:
# filename = u'/data/djangostack-1.9.7/apps/django/django_projects/Project/media/aliexpress_salling_product_file/2018/04/\u5b59\u5065-16_20_04-products.xls'
# if not shopname:
# shopname = 'Ali-0001-fancyqube/PJ'
# try:
# # pro_wb = xlrd.open_workbook(filename)
# pro_wb = xlrd.open_workbook(filename=None, file_contents=filename.read())
# except Exception as e:
# return {'code': '-1', 'message': '%s' % str(e)}
if not filename_params:
return {'code': '-1', 'message': ' No file'}
oss_file_obj = get_obj_from_oss(settings.BUCKETNAME_XLS)
oss_res = oss_file_obj.get_obj_from_oss(filename_params)
file_obj = oss_res['result']
if oss_res['errorcode'] != 0 or file_obj == '':
return {'code': '-1', 'message': 'Get oss file Failed'}
try:
pro_wb = xlrd.open_workbook(filename=None, file_contents=file_obj.read())
# pro_wb = filename
sheet_name = pro_wb.sheet_names()[0]
sheet_obj = pro_wb.sheet_by_name(sheet_name)
nrows = sheet_obj.nrows
heads = sheet_obj.row_values(0)
head_dict = dict()
for k, v in enumerate(heads):
if v in ALIEXPRESS_EXCEL_HEADS_NEW:
head_dict[v] = k
for row in range(nrows):
row += 1
if row == nrows:
break
product_id = str(int(sheet_obj.cell_value(row, head_dict[u'产品ID'])))
if product_id == '0':
datasource = str(sheet_obj.cell_value(row, head_dict[u'来源']))
product_id = datasource.split('/')[-1].split('.')[0]
title = str(sheet_obj.cell_value(row, head_dict[u'标题']))
price = str(sheet_obj.cell_value(row, head_dict[u'售价(US $)']))
# weight = str(sheet_obj.cell_value(row, head_dict[u'产品包装后的重量']))
try:
quantity = int(sheet_obj.cell_value(row, head_dict[u'库存']))
except Exception as e:
print 'Get Aliexpress product quantity error: %s' % e
quantity = 0
pic_url = str(sheet_obj.cell_value(row, head_dict[u'图片路径']))
image = None
if pic_url:
image_url = pic_url.split(',')[0]
if image_url.startswith('http'):
image = image_url
else:
pass
try:
shopskus_info = str(sheet_obj.cell_value(row, head_dict[u'商家编码']))
shopskus_info = shopskus_info.split(';')
except Exception as e:
print 'Get shopskus price error %s' % e
continue
shopskus = list()
sku_list = list()
mainsku_list = list()
weight_list = list()
for shopsku in shopskus_info:
shop_sku = shopsku
sku = classshopsku_obj.getSKU(shop_sku)
mainsku = classsku_obj.get_bemainsku_by_sku(sku)
weight = classsku_obj.get_weight_by_sku(sku)
ali_detail_info = dict()
ali_detail_info['product_id'] = product_id
ali_detail_info['sku'] = sku
ali_detail_info['mainsku'] = mainsku
ali_detail_info['shop_sku'] = shop_sku
ali_detail_info['status'] = flag
if weight:
ali_detail_info['weight'] = float(weight)
else:
ali_detail_info['weight'] = None
shopskus.append(shop_sku)
print 'ali_detail_info', ali_detail_info
update_aliexpress_info_detail(**ali_detail_info)
if sku:
sku_list.append(sku)
if mainsku and mainsku not in mainsku_list:
mainsku_list.append(mainsku)
if weight:
weight_list.append(float(weight))
if not image:
bmpurl = get_image_by_sku(sku, db_conn)
if bmpurl:
image = bmpurl
print 'image', image
mainsku_list_str = None
sku_list_str = None
max_weight = None
if mainsku_list:
mainsku_list_str = ','.join(mainsku_list)
if sku_list:
sku_list_str = ','.join(sku_list)
if weight_list:
weight_list.sort()
max_weight = weight_list[0]
profitrate = get_aliexpress_profitrate(price, sku)
ali_info = dict()
ali_info['product_id'] = product_id
ali_info['title'] = title
ali_info['price'] = price
ali_info['shopskus'] = ','.join(shopskus)
ali_info['quantity'] = quantity
ali_info['mainsku_list_str'] = mainsku_list_str
ali_info['sku_list_str'] = sku_list_str
if flag == 'True':
ali_info['status'] = '1'
else:
ali_info['status'] = '0'
ali_info['shopname'] = shopname
ali_info['image'] = image
ali_info['weight'] = max_weight
ali_info['profitRate'] = profitrate
update_aliexpress_info(**ali_info)
except Exception as e:
return {'code': '-1', 'message': '%s' % str(e)}
return {'code': '0', 'message': 'SUCCESS'}
def update_joom_product_sku_and_mainsku():
from brick.classredis.classsku import classsku
from brick.classredis.classshopsku import classshopsku
from django_redis import get_redis_connection
start = datetime.datetime.now()
print 'start', start
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
return
db_conn = db_res['db_conn']
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
# joom_pros = t_online_info_joom.objects.filter(Status='True', ReviewState='approved').values('ProductID', 'ShopSKU', 'ShopName')
joom_pros = t_online_info_joom.objects.filter(
Status='1', ReviewState='0').values('ProductID', 'ShopSKU', 'ShopName')
curs = db_conn.cursor()
no_mainsku_pro_list = list()
for pro in joom_pros:
# print pro
shopsku_list = pro['ShopSKU'].split(',')
product_id = pro['ProductID']
shopname = pro['ShopName']
sku_list = list()
mainsku_list = list()
for shopsku in shopsku_list:
sku = classshopsku_obj.getSKU(shopsku)
# print sku
mainsku = classsku_obj.get_bemainsku_by_sku(sku)
# print mainsku
if sku:
sku_list.append(sku)
if mainsku and mainsku not in mainsku_list:
mainsku_list.append(mainsku)
sql_detail = "UPDATE t_online_info_joom_detail SET SKU=%s, MainSKU=%s WHERE ProductID=%s AND ShopSKU=%s;"
curs.execute(sql_detail, (sku, mainsku, product_id, shopsku))
skus = None
if sku_list:
skus = ','.join(sku_list)
mainskus = None
if mainsku_list:
mainskus = ','.join(mainsku_list)
if not sku_list or not mainsku_list:
# print 'product_id', product_id
# print 'shopsku_list', shopsku_list
a_on_info = dict()
a_on_info['shopname'] = shopname
a_on_info['product_id'] = product_id
a_on_info['shopsku_list'] = shopsku_list
no_mainsku_pro_list.append(a_on_info)
sql_joom = "UPDATE t_online_info_joom SET SKU=%s, MainSKU=%s WHERE ProductID=%s;"
curs.execute(sql_joom, (skus, mainskus, product_id))
# print 'no_mainsku_pro_list num', len(no_mainsku_pro_list)
print 'no_mainsku_pro_list', no_mainsku_pro_list
curs.execute('commit;')
curs.close()
end = datetime.datetime.now()
print 'end', end
how_long = end - start
print 'how_long', how_long
def insertWishV2(self, data):
refreshdict = {}
refreshdict['ShopName'] = ''
prolist = []
#cnxn = MySQLdb.connect(DATABASES['HOST'],DATABASES['USER'],DATABASES['PASSWORD'],DATABASES['NAME'] )
cursor = self.cnxn.cursor()
#ShopIP = socket.gethostbyname(socket.gethostname())
#sql_delete = 'delete from t_online_info where ShopName =\'%s\' '%self.ShopName.strip()
#cursor.execute(sql_delete)
classlisting_obj = classlisting.classlisting(self.cnxn,
self.redis_conn)
classshopsku_obj = classshopsku.classshopsku(self.cnxn,
self.redis_conn,
self.ShopName)
# classshopname_obj = classshopname.classshopname(db_conn = self.cnxn)
t_store_configuration_file_obj = t_store_configuration_file(self.cnxn)
classsku_obj = classsku.classsku(self.cnxn, self.redis_conn)
t_event_class_obj = t_event_class()
cwishapi_obj = cwishapi()
insert_product = []
delete_product = []
update_product = []
# logger = logging.getLogger('sourceDns.webdns.views')
for row in data:
#print(row)
ProductID = row['Product']['id']
# logger.error("ProductID===%s" % (ProductID, ))
prolist.append(ProductID)
is_promoted = row['Product'].get('is_promoted', '')
classlisting_obj.set_is_promoted_listingid(ProductID, is_promoted)
WishExpress = '%s' % row['Product'].get(
'wish_express_country_codes', '[]')
classlisting_obj.set_WishExpress_listingid(ProductID, WishExpress)
ProductName = row['Product']['name']
if '\u' in ProductName:
try:
ProductName = ProductName.decode("unicode_escape")
except:
pass
OfWishes = row['Product']['number_saves']
OfSales = row['Product']['number_sold']
ParentSKU = row['Product']['parent_sku'].replace(
'<', '<').replace('>',
'>').replace("'",
"'").replace('\\/', '/')
if '\u' in ParentSKU:
try:
ParentSKU = ParentSKU.decode("unicode_escape")
except:
pass
ReviewState = row['Product']['review_status'] #当前 wish查看状态
be_sql = "select DISTINCT ReviewState,BeforeReviewState from t_online_info WHERE ProductID=%s LIMIT 1;"
cursor.execute(be_sql, (ProductID, ))
beforers = cursor.fetchone()
BeforeReviewState = None # 上一个wish查看状态
if beforers and len(beforers) == 2:
if ReviewState == 'rejected' and beforers[
0] == 'rejected' and beforers[1]:
BeforeReviewState = beforers[1]
elif beforers[0]:
BeforeReviewState = beforers[0]
ImageURL = row['Product']['main_image'].replace('\\', '')
DateUploaded = time.strftime(
"%Y-%m-%d",
time.strptime(
row['Product']['date_uploaded'],
"%m-%d-%Y")) # row['Product']['date_uploaded'] 07-01-2017
LastUpdated = time.strftime(
"%Y-%m-%dT%H:%M:%S",
time.strptime(row['Product']['last_updated'],
"%m-%d-%YT%H:%M:%S")
) # row['Product']['last_updated'] 08-03-2017T10:21:09
ExtraImages = row['Product']['extra_images'].replace('\\', '')
Description = row['Product']['description']
if '\u' in Description:
try:
Description = Description.decode("unicode_escape")
except:
pass
ShopName = self.ShopName.strip()
refreshdict['ShopName'] = ShopName
PlatformName = 'Wish'
Tags_dict = row['Product']['tags']
Title = ProductName
DepartmentID = t_store_configuration_file_obj.getDepartmentbyshopcode(
ShopName) # 获取该店铺的部门编号
seller = t_store_configuration_file_obj.getsellerbyshopcode(
ShopName) # 获取该店铺的 销售员 目前没有走redis
Published = t_store_configuration_file_obj.getPublishedbyshopcode(
ShopName) # 获取该店铺的 刊登人 目前没有走redis
if seller is None or seller.strip() == '':
seller = Published
SKU = ''
RefreshTime = datetime.datetime.now()
#print RefreshTime
Tags = ''
for Tag_dict in Tags_dict:
if Tags == '':
Tags = Tag_dict['Tag']['name']
else:
Tags = '%s,%s' % (Tags, Tag_dict['Tag']['name'])
# cursor.execute('Delete from t_online_info where ProductID= %s ; ',(ProductID,))
shopskulist = [] # 定义ShopSKU列表
filterdict = {} # 用于 存放 需要修改的 商品SKU
vidlist = []
fbw_flag_list = [] # fbw标记列表
for variant in row['Product']['variants']:
VariationID = variant['Variant']['id'] # 平台唯一ID
vidlist.append(VariationID)
shopskutmp = variant['Variant']['sku'].replace(
'<', '<').replace('>',
'>').replace('&', '&').replace(
"'", "'").replace('\\/', '/')
# logger.error("shopskutmp===%s" % (shopskutmp, ))
if '\u' in shopskutmp:
try:
shopskutmp = shopskutmp.decode('unicode-escape')
except:
pass
eshopsku = shopskutmp
# 刷新fbw标记
fbw_flag = refresh_fbw_flag(product_id=ProductID,
shopsku=eshopsku,
shopname=ShopName,
connection=self.cnxn)
fbw_flag_list.append(fbw_flag)
SKU = classshopsku_obj.getSKU(eshopsku) # 商品SKU get
sku_goodsstatus = None
if SKU is not None and SKU.strip() != '':
sku_goodsstatus = classsku_obj.get_goodsstatus_by_sku(
SKU) # 获取商品SKU的商品状态
# 下面是简单的转换
if sku_goodsstatus == u'正常':
sku_goodsstatus = '1'
elif sku_goodsstatus == u'售完下架':
sku_goodsstatus = '2'
elif sku_goodsstatus == u'临时下架':
sku_goodsstatus = '3'
elif sku_goodsstatus == u'停售':
sku_goodsstatus = '4'
#print sku_goodsstatus
ShopSKUImage = ''
if variant['Variant'].has_key('main_image'):
ShopSKUImage = variant['Variant']['main_image'].replace(
'\\', '')
classshopsku_obj.setImage(eshopsku, ShopSKUImage) # 变体图 set
Price = variant['Variant']['price']
classshopsku_obj.setPrice(eshopsku, Price) # 价格 set
Inventory = variant['Variant']['inventory']
if Inventory == '':
Inventory = None
classshopsku_obj.setQuantity(eshopsku, Inventory) # 库存 set
Status = variant['Variant'].get('enabled',
'') # "enabled": "False",
if Status.strip() == 'False':
Statusssss = 'Disabled'
elif Status.strip() == 'True': # True
Statusssss = 'Enabled'
else:
Statusssss = Status
classshopsku_obj.setStatus(eshopsku, Statusssss) # 状态 set
if Statusssss == 'Enabled':
filterdict[SKU] = 0
Shipping = variant['Variant']['shipping']
if Shipping == '':
Shipping = None
classshopsku_obj.setShipping(eshopsku, Shipping) # 运费 set
Color = ''
if variant['Variant'].has_key('color'):
Color = variant['Variant']['color'].replace('&',
'&') # &
classshopsku_obj.setColor(eshopsku, Color) # 颜色 set
Size = ''
if variant['Variant'].has_key('size'):
Size = variant['Variant']['size'][:30]
classshopsku_obj.setSize(eshopsku, Size) # 尺寸 set
msrp = variant['Variant']['msrp']
classshopsku_obj.setmsrp(eshopsku, msrp) # 标签价 set
ShippingTime = variant['Variant']['shipping_time']
classshopsku_obj.setshippingtime(eshopsku,
ShippingTime) # 运输时间 set
# Quantity = Inventory
cursor.execute(
"select count(VariationID) from t_online_info WHERE ProductID=%s and VariationID=%s;",
(
ProductID,
VariationID,
))
somecount = cursor.fetchone()
if somecount[0] > 0:
sql_update = "update t_online_info set Title=%s,Price=%s,Quantity=%s,RefreshTime=%s,Image=%s,Status=%s," \
"ReviewState=%s,OfWishes=%s,OfSales=%s,LastUpdated=%s,Shipping=%s,Color=%s,`Size`=%s,msrp=%s," \
"ShippingTime=%s,ExtraImages=%s,Description=%s,Tags=%s,ShopSKUImage=%s,is_promoted=%s," \
"WishExpress=%s,seller=%s,Published=%s,SKU=%s,MainSKU=%s,BeforeReviewState=%s,ShopSKU=%s" \
" WHERE ProductID=%s and VariationID=%s;"
cursor.execute(
sql_update,
(Title, Price, Inventory, RefreshTime, ImageURL,
Statusssss, ReviewState, OfWishes, OfSales,
LastUpdated, Shipping, Color, Size, msrp,
ShippingTime, ExtraImages, Description, Tags,
ShopSKUImage, is_promoted, WishExpress,
seller, Published, SKU,
classsku_obj.get_bemainsku_by_sku(SKU),
BeforeReviewState, shopskutmp, ProductID,
VariationID))
# if not SKU:
update_product.append(ProductID)
else:
sql_insert = 'INSERT INTO t_online_info(ShopIP,ShopName,PlatformName,ProductID,Title,' \
'SKU,ShopSKU,Price,Quantity,RefreshTime,Image,Status,DateUploaded,ParentSKU,' \
'ReviewState,OfWishes,OfSales,LastUpdated,Shipping,Color,`Size`,msrp,' \
'ShippingTime,ExtraImages,VariationID,Description,Tags,' \
'MainSKU,ShopSKUImage,is_promoted,WishExpress,DepartmentID,seller,Published,' \
'GoodsStatus,filtervalue,APIState,BeforeReviewState)' \
' VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,' \
'%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ; '
cursor.execute(
sql_insert,
(self.ShopIP, ShopName, PlatformName, ProductID, Title,
SKU, shopskutmp, Price, Inventory, RefreshTime,
ImageURL, Statusssss, DateUploaded, ParentSKU,
ReviewState, OfWishes, OfSales, LastUpdated, Shipping,
Color, Size, msrp, ShippingTime, ExtraImages,
VariationID, Description, Tags,
classsku_obj.get_bemainsku_by_sku(SKU), ShopSKUImage,
is_promoted, WishExpress, DepartmentID, seller,
Published, sku_goodsstatus, 1, 'nothing',
BeforeReviewState))
insert_product.append(ProductID)
# sql_update = "update py_db.b_goodsskulinkshop a, t_online_info b set b.sku = a.sku ,b.MainSKU = getMainSKU(a.SKU) ,b.MainShopSKU = getMainSKU(b.shopSKU) where a.shopsku = b.shopsku and b.ProductID = %s and b.ShopSKU = %s "
# cursor.execute(sql_update,(ProductID,ShopSKU))
shopskulist.append(shopskutmp)
# logger.error("shopskulist===%s" % (shopskulist, ))
# logger.error("join===%s" % ('|'.join(shopskulist), ))
#sql_update_mainsku = 'update t_online_info set MainSKU = getMainSKU(SKU) ,MainShopSKU = getMainSKU(shopSKU) where ProductID= %s and ShopSKU =%s '
#cursor.execute(sql_update_mainsku,(ProductID,ShopSKU))
# 获取该产品的海外仓运费和库存
for we in eval(WishExpress): # 是属于海外仓的
# raise Exception(self.warehouse)
if self.warehouse.has_key(we) and we in ['DE', 'GB', 'US']:
warehouseid = self.warehouse[we]
vgoodsinfos = cwishapi_obj.warehouse_vgoodsinfo(
self.access_token, ProductID, warehouseid)
# assert vgoodsinfos['errorcode'] == 1, vgoodsinfos['errortext']
if vgoodsinfos['errorcode'] == 1:
for vgoods in vgoodsinfos['data']:
wvid = vgoods['Variant']['id']
shopsku = vgoods['Variant']['sku'].replace(
'<', '<').replace('>', '>').replace(
'&',
'&').replace("'",
"'").replace('\\/', '/')
if '\u' in shopsku:
try:
shopsku = shopsku.decode('unicode-escape')
except:
pass
wshipping = vgoods['Variant'][
'shipping'] # DEExpressShipping
if wshipping == '':
wshipping = None
ws = getattr(classshopsku_obj,
"setWish%sShipping" % we)
ws(shopsku, wshipping)
# eval("classshopsku_obj.setWish%sShipping" % we)(shopsku, wshipping)
winventory = vgoods['Variant'][
'inventory'] # DEExpressInventory
if winventory == '':
winventory = None
wi = getattr(classshopsku_obj,
"setWish%sQuantity" % we)
wi(shopsku, winventory)
# eval("classshopsku_obj.setWish%sQuantity" % we)(shopsku, winventory)
vsql = "update t_online_info set " + we + "ExpressShipping=%s," + we + \
"ExpressInventory=%s WHERE ProductID=%s and VariationID=%s;"
cursor.execute(
vsql, (wshipping, winventory, ProductID, wvid))
if vidlist: # 删除多余的变体信息
del_param_list = []
old_shopsku_list = classlisting_obj.getShopSKUList(ProductID)
if old_shopsku_list:
for del_shopsku in old_shopsku_list:
if del_shopsku not in shopskulist:
del_param_list.append([ProductID, del_shopsku])
delete_product.append(ProductID)
# del_sku = classshopsku_obj.getSKU(del_shopsku)
# if del_sku:
# delete_sku.append(del_sku)
cursor.executemany(
"delete from t_online_info WHERE ProductID=%s and ShopSKU=%s ;",
del_param_list)
classlisting_obj.setShopSKUList(
ProductID, '|'.join(shopskulist)) # ProductID ShopSKUList
# for k,v in filterdict.items():
# cursor.execute("update t_online_info set filtervalue = 0 WHERE ProductID=%s and SKU=%s;",(ProductID,k,))
if fbw_flag_list and 'True' in fbw_flag_list:
cursor.execute(
"update t_online_info set FBW_Flag = 'True' WHERE ProductID=%s ;",
(ProductID, ))
elif 'False' in fbw_flag_list:
cursor.execute(
"update t_online_info set FBW_Flag = 'False' WHERE ProductID=%s ;",
(ProductID, ))
self.cnxn.commit()
for inproduct in set(insert_product):
iResult = t_event_class_obj.t_online_info_insert_trigger(inproduct)
for delproduct in set(delete_product):
del_Result = t_event_class_obj.t_online_info_delete_trigger(
delproduct)
for updproduct in set(update_product):
upd_Result = t_event_class_obj.t_online_info_update_trigger(
updproduct)
cursor.close()
refreshdict['ProductID'] = prolist
return refreshdict
def getTortInfo(self, productSku):
classsku_obj = classsku(connection)
mainSKU = classsku_obj.get_bemainsku_by_sku(productSku)
classmainsku_obj = classmainsku(connection)
tortInfo = classmainsku_obj.get_tort_by_mainsku(mainSKU)
return tortInfo
#!/usr/bin/python
# -*- coding: utf-8 -*-
import datetime
from brick.db.dbconnect import run
# from brick.db.dbconnect import execute_db
from brick.classredis.classsku import classsku
from skuapp.table.t_online_info_amazon_listing import t_online_info_amazon_listing
# from brick.pydata.py_redis.py_SynRedis_tables import connRedis
classsku1_obj = classsku()
def amazon_update_seven_orders():
start = datetime.datetime.now()
print 'start', start
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
return
pro_shop_sku = t_online_info_amazon_listing.objects.filter(
Status='Active').values('product_id', 'seller_sku')
cursor = db_res['db_conn'].cursor()
# CLear SevenOrders
clear_sql = "UPDATE t_online_info_amazon_listing SET order7days=NULL"
cursor.execute(clear_sql)
cursor.execute("commit;")
for ss in pro_shop_sku:
shopskus = list()
connRedis = get_redis_connection(alias='product')
# from Project.settings import connRedis
from brick.public.create_dir import mkdir_p
from storeapp.models import t_online_info_wish_store
from skuapp.table.t_product_enter_ed import t_product_enter_ed
from pyapp.models import b_goods as py_b_goods
from brick.spider.get import readAliexpress
from brick.classredis.classmainsku import classmainsku
from brick.classredis.classsku import classsku
from .t_work_flow_of_plate_house_admin import t_work_flow_of_plate_house_admin
classmainsku_obj = classmainsku(db_cnxn=connection)
classsku_obj = classsku(db_cnxn=connection)
def get_price_weight_pylink_by_mainsku(mainsku):
price = None
weight = None
py_link = None
purchaser = None
sizelist = []
skulist = classmainsku_obj.get_sku_by_mainsku(mainsku)
if skulist:
for sku in skulist:
price = classsku_obj.get_price_by_sku(sku)
weight = classsku_obj.get_weight_by_sku(sku)
if price or weight:
break
def handle_aliexpress_products(filename='', flag='True', shopname=''):
db_res = run({})
if db_res['errorcode'] == -1:
print "result['errortext']: %s" % db_res['errortext']
return
db_conn = db_res['db_conn']
redis_conn = get_redis_connection(alias='product')
classsku_obj = classsku(db_cnxn=db_conn, redis_cnxn=redis_conn)
classshopsku_obj = classshopsku(db_conn=db_conn, redis_conn=redis_conn)
# classsku_obj = classsku(db_cnxn=db_conn)
# classshopsku_obj = classshopsku(db_conn=db_conn)
# if not filename:
# filename = u'/data/djangostack-1.9.7/apps/django/django_projects/Project/media/aliexpress_salling_product_file/2018/04/\u5b59\u5065-16_20_04-products.xls'
# if not shopname:
# shopname = 'Ali-0001-fancyqube/PJ'
try:
pro_wb = xlrd.open_workbook(filename)
except Exception as e:
return {'code': '-1', 'message': '%s' % str(e)}
sheet_name = pro_wb.sheet_names()[0]
sheet_obj = pro_wb.sheet_by_name(sheet_name)
nrows = sheet_obj.nrows
heads = sheet_obj.row_values(0)
head_dict = dict()
for k, v in enumerate(heads):
if v in ALIEXPRESS_EXCEL_HEADS:
head_dict[v] = k
for row in range(nrows):
row += 1
if row == nrows:
break
product_id = str(int(sheet_obj.cell_value(row, head_dict[u'产品ID'])))
if product_id == '0':
datasource = str(sheet_obj.cell_value(row, head_dict[u'数据来源']))
product_id = datasource.split('/')[-1].split('.')[0]
title = str(sheet_obj.cell_value(row, head_dict[u'产品名称']))
price = str(sheet_obj.cell_value(row, head_dict[u'零售价']))
# weight = str(sheet_obj.cell_value(row, head_dict[u'产品包装后的重量']))
try:
quantity = int(sheet_obj.cell_value(row, head_dict[u'库存']))
except Exception as e:
print 'Get Aliexpress product quantity error: %s' % e
quantity = 0
pic_url = str(sheet_obj.cell_value(row, head_dict[u'商品图片']))
image = None
if pic_url:
image_url = pic_url.split(',')[0]
if image_url.startswith('http'):
image = image_url
else:
pass
try:
shopskus_info = json.loads(str(sheet_obj.cell_value(row, head_dict[u'价格信息'])))
shopskus_info = shopskus_info['skuArray']
except Exception as e:
print 'Get shopskus price error %s' % e
continue
shopskus = list()
sku_list = list()
mainsku_list = list()
weight_list = list()
for shopsku in shopskus_info:
shop_sku = shopsku[u'商家编码']
shop_sku_quantity = shopsku[u'库存']
shop_sku_price = shopsku[u'价格']
sku = classshopsku_obj.getSKU(shop_sku)
mainsku = classsku_obj.get_bemainsku_by_sku(sku)
weight = classsku_obj.get_weight_by_sku(sku)
ali_detail_info = dict()
ali_detail_info['product_id'] = product_id
ali_detail_info['sku'] = sku
ali_detail_info['mainsku'] = mainsku
ali_detail_info['shop_sku'] = shop_sku
ali_detail_info['shop_sku_price'] = shop_sku_price
ali_detail_info['shop_sku_quantity'] = shop_sku_quantity
ali_detail_info['status'] = flag
if weight:
ali_detail_info['weight'] = float(weight)
else:
ali_detail_info['weight'] = None
shopskus.append(shop_sku)
update_aliexpress_info_detail(**ali_detail_info)
if sku:
sku_list.append(sku)
if mainsku and mainsku not in mainsku_list:
mainsku_list.append(mainsku)
if weight:
weight_list.append(float(weight))
if not image:
bmpurl = get_image_by_sku(sku, db_conn)
if bmpurl:
image = bmpurl
mainsku_list_str = None
sku_list_str = None
max_weight = None
if mainsku_list:
mainsku_list_str = ','.join(mainsku_list)
if sku_list:
sku_list_str = ','.join(sku_list)
if weight_list:
weight_list.sort()
max_weight = weight_list[0]
ali_info = dict()
ali_info['product_id'] = product_id
ali_info['title'] = title
ali_info['price'] = price
ali_info['shopskus'] = ','.join(shopskus)
ali_info['quantity'] = quantity
ali_info['mainsku_list_str'] = mainsku_list_str
ali_info['sku_list_str'] = sku_list_str
ali_info['status'] = flag
ali_info['shopname'] = shopname
ali_info['image'] = image
ali_info['weight'] = max_weight
update_aliexpress_info(**ali_info)
return {'code': '0', 'message': 'SUCCESS'}
from skuapp.table.t_product_inventory_warnning import t_product_inventory_warnning
from django.contrib import messages
import requests,oss2,math
from Project.settings import *
from django.http import HttpResponseRedirect
#from brick.pydata.py_redis.py_SynRedis_pub import py_SynRedis_pub
from brick.classredis.classsku import classsku
from brick.classredis.classmainsku import classmainsku
from django.db import connection
from datetime import datetime
from .t_product_Admin import *
from django_redis import get_redis_connection
redis_conn = get_redis_connection(alias='product')
classmainsku_obj = classmainsku(connection,redis_conn)
#py_SynRedis_obj = py_SynRedis_pub()
classsku_obj = classsku(connection,redis_conn)
from pyapp.table.t_product_b_goods import t_product_b_goods
def get_ontheWayNumber(MainSKU,storeName):
number = 0.0
if storeName == '浦江仓库':
mainsku_list = classmainsku_obj.get_sku_by_mainsku(MainSKU)
if mainsku_list:
for sku in mainsku_list:
#notInNumber = py_SynRedis_obj.getFromHashRedis('',sku,'19')
notInNumber = classsku_obj.get_uninstore_by_sku(sku)
if notInNumber == -1 or notInNumber == -2 or notInNumber is None:
number = 0.0
continue
number += float(notInNumber)
return number
def save_models(self):
obj = self.new_obj
old_obj = None
if obj is None or obj.id is None or obj.id <= 0:
pass
else:
old_obj = self.model.objects.get(pk=obj.pk)
try:
clothes = self.request.GET.get('clothes', '0')
v_progress = clothes
if clothes == '1':
self.request.POST[
'_redirect'] = "/Project/admin/skuapp/t_progress_tracking_of_product_customization_table/?clothes=1"
bemainsku = classsku(connection).get_bemainsku_by_sku(obj.MainSKU)
enclosure = obj.Enclosure
if obj.Enclosure:
filename = '%s/%s/%s' % (
self.request.user.username,
datetime.now().strftime('%Y%m%d%H%M%S'), obj.Enclosure)
auth = oss2.Auth(ACCESS_KEY_ID, ACCESS_KEY_SECRET)
bucket = oss2.Bucket(auth, ENDPOINT, BUCKETNAME_DOWNLOAD)
presult = bucket.put_object(filename, obj.Enclosure)
_content = presult.__dict__
enclosure = PREFIX + BUCKETNAME_DOWNLOAD + '.' + ENDPOINT_OUT + '/' + filename
if _content['status'] != 200:
raise Exception(u'附件上传失败!请稍后重试 code: %s' %
_content['status'])
image_list = self.request.POST.getlist('product_image', [])
sku_list = self.request.POST.getlist('sku', [])
name_list = self.request.POST.getlist('name', [])
keyword_list = self.request.POST.getlist('keywords', [])
surveyname_list = self.request.POST.getlist('surveyname', [])
surveytime_list = self.request.POST.getlist('surveytime', [])
reverselink_list = self.request.POST.getlist('reverselink', [])
supplierlink_list = self.request.POST.getlist('supplierlink', [])
developer_list = self.request.POST.getlist('salerName2', [])
finishtime_list = self.request.POST.getlist('FinishTime', [])
insert_into = []
for i in range(len(sku_list)):
insert_into.append(
self.model(ImageURL=image_list[i],
MainSKU=bemainsku,
SKU=sku_list[i],
Name=name_list[i],
KeyWords=keyword_list[i],
ReverseLink=reverselink_list[i],
SupplierLink=supplierlink_list[i],
SurveyPerson=surveyname_list[i],
SurveyTime=surveytime_list[i],
SupplyChainDeveloper=developer_list[i],
FinishTime=finishtime_list[i],
SurveyAnalysis='',
MakeDemand='',
DevelopRemark='',
CheckPerson='',
CheckTime=None,
CheckRemark='',
RateOfProgress=v_progress,
Enclosure=enclosure,
Submiter=self.request.user.first_name,
SubmitTime=datetime.now(),
FromClothes=clothes))
self.model.objects.bulk_create(insert_into)
except Exception, e:
messages.error(self.request, u'%s:%s' % (Exception, e))
def save_models(self, ):
try:
from django.contrib.auth.models import User
userID = [
each.id for each in User.objects.filter(groups__id__in=[65])
]
#if request.user.is_superuser or request.user.id in userID:
if self.request.user.id in userID:
obj = self.new_obj
request = self.request
old_obj = None
if obj is None or obj.id is None or obj.id <= 0:
pass
else:
old_obj = self.model.objects.get(pk=obj.pk)
obj.save()
obj.Stocking_plan_number = ddtime.now().strftime(
'%Y%m%d%H%M%S') + '_' + str(obj.id)
obj.Stock_plan_date = ddtime.now()
obj.OplogTime = ddtime.now()
obj.QTY = obj.Stocking_quantity
from brick.classredis.classsku import classsku
classskuObj = classsku()
Number = classskuObj.get_number_by_sku(obj.ProductSKU)
obj.Number = 0 if (Number is None or Number == '') else Number
if obj.Demand_people is None or obj.Demand_people.strip(
) == "":
obj.Demand_people = request.user.first_name
py_b_goods_objs = py_b_goods.objects.filter(SKU=obj.ProductSKU)
if py_b_goods_objs.exists():
obj.ProductImage = u'http://fancyqube.net:89/ShopElf/images/%s.jpg' % py_b_goods_objs[
0].SKU.replace('OAS-', '').replace('FBA-', '')
obj.ProductName = py_b_goods_objs[0].GoodsName
obj.ProductPrice = py_b_goods_objs[0].CostPrice
obj.ProductWeight = py_b_goods_objs[0].Weight
obj.Supplierlink = py_b_goods_objs[0].LinkUrl
py_b_Supplier_objs = py_b_Supplier.objects.filter(
NID=py_b_goods_objs[0].SupplierID)
if py_b_Supplier_objs.exists():
obj.Supplier = py_b_Supplier_objs[0].SupplierName
obj.Buyer = ''
obj.Status = u'notgenpurchase' # 未生成采购计划
obj.save()
#插入详细信息表
insertSKU = []
insertSKU.append(
t_stocking_demand_fba_detail(
ProductSKU=obj.ProductSKU,
Stocking_plan_number=obj.Stocking_plan_number,
CreateDate=ddtime.now(),
Status='notgenpurchase',
AuditFlag=0))
t_stocking_demand_fba_detail.objects.bulk_create(insertSKU)
else:
messages.error(self.request, "无权限操作。")
except Exception, ex:
messages.info(self.request, "保存报错:%s" % (str(ex)))
