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