def all_shipping_list(paramlsit): classshopsku_obj = classshopsku(db_conn=connection, redis_conn=redis_coon, shopname=paramlsit[0]) classlisting_obj = classlisting(db_conn=connection, redis_conn=redis_coon) shopskulist = classlisting_obj.getShopSKUList(paramlsit[1]) shippinglist = [] for shopsku in shopskulist: shipping = classshopsku_obj.getShipping(shopsku) shippinglist.append(float(shipping)) return shippinglist
def show_time(self, obj): classshopskuobjs = classshopsku(connection, redis_conn, obj.ShopName) rt = u'在线数据刷新:<br>%s <br>上架(UTC):<br>%s <br>平台最近更新(UTC):<br>%s' % \ (obj.RefreshTime, obj.DateUploaded, obj.LastUpdated) for shopsku in listingobjs.getShopSKUList(obj.ProductID): sku = classshopskuobjs.getSKU(shopsku) if sku is not None: # rt = rt + u'<br>商品最近刷新:<br>%s' % (py_SynRedis_pub_obj.getFromHashRedis('', sku, 'KC_updateTime')) rt = rt + u'<br>商品最近刷新:<br>%s' % ( classsku_obj.get_updatetime_by_sku(sku)) break return mark_safe(rt)
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 else: shopcode = '{}-{}'.format(shopname_codelist[0], shopname_codelist[1]) self.classshopsku_obj = classshopsku( db_conn=None, redis_conn=redis_coon, shopname=shopcode if shopname_codelist[0] == 'Wish' else None) self.shop_name_full = u'%s' % shop_name.strip()
def update_state_after_success(cur, shopsku_info, state): """ 在上架或下架成功后更改redis、t_goods_shelves、t_online_info里的商品状态 :param shopsku_info: :param state: :return: """ shopsku = shopsku_info[0] productID = shopsku_info[3] classshopsku_obj = classshopsku(db_conn=connection, redis_conn=redis_conn) classshopsku_obj.setStatus(shopsku=shopsku, status=state) goods_shelves_sql = 'update t_goods_shelves set Status=%s WHERE ProductID=%s AND ShopSKU=%s' online_info_sql = 'update t_online_info set Status=%s WHERE ProductID=%s AND ShopSKU=%s' cur.execute(goods_shelves_sql, (state, productID, shopsku)) cur.execute(online_info_sql, (state, productID, shopsku)) cur.execute('commit; ') # 更新listing在线状态 update_wish_listing_status(cur, productID)
def get_submitter(self, datestart, dateafter): classshopsku_obj = classshopsku(self.connection) mycur = self.connection.cursor() sql = '''select product_skus,shopName from t_erp_aliexpress_online_info where gmt_create > '%s' and gmt_create < '%s' ''' % ( datestart, dateafter) mycur.execute(sql) product_skus = mycur.fetchall() submitter_all = {} shopName_list = [] #店铺统计 submitter_list = [] #刊登人统计 for product_sku in product_skus: try: sku_infos = json.loads(product_sku[0]) sku_info_list = sku_infos.get('aeop_ae_product_sku', '') for i in range(len(sku_info_list)): shopsku = sku_info_list[i].get('sku_code', '') submitter = classshopsku_obj.getPublished(shopsku) if submitter is None: submitter = classshopsku_obj.getPublished( shopsku.split('\\')[0].split('*')[0]) if not submitter or len(submitter) < 2: submitter = '空'.decode('utf-8') key = '%s%s%s' % (product_sku[1], '|', submitter) #店铺|刊登人 if product_sku[1] not in shopName_list: #新的店铺 shopName_list.append(product_sku[1]) submitter_list.append(submitter_list) submitter_all.setdefault(key, 0) else: if submitter not in submitter_list: #新的刊登人 submitter_list.append(submitter_list) submitter_all.setdefault(key, 0) submitter_all[key] = submitter_all[key] + 1 break except Exception, e: print traceback.print_exc()
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 show_SKU_list( self, obj ): # <label><input type="checkbox" name="shopskucheck_%s_%s"></label> classshopskuobjs = classshopsku(connection, redis_conn, obj.ShopName) activeflag = self.request.GET.get('EXPRESS', 'STANDARD') rt = u'<table class="table table-condensed">' \ u'<thead><tr><th></th>' \ u'<th>商品SKU</th><th>商品状态</th><th>可卖天数</th>' \ u'<th>店铺SKU</th><th>价格</th>' if activeflag == 'DE': rt = rt + u'<th>德国仓运费</th><th>德国仓库存</th>' elif activeflag == 'GB': rt = rt + u'<th>英国仓运费</th><th>英国仓库存</th>' elif activeflag == 'US': rt = rt + u'<th>美国仓运费</th><th>美国仓库存</th>' else: rt = rt + u'<th>标准仓运费</th><th>标准仓库存</th>' rt = rt + u'<th>利润率(%)</th><th>变体状态</th></tr></thead>' \ u'<tbody>' shopskulist = listingobjs.getShopSKUList(obj.ProductID) infor = [] for i, shopsku in enumerate(shopskulist): eachinfor = {} eachinfor['SKU'] = classshopskuobjs.getSKU(shopsku) eachinfor['SKUKEY'] = [ 'GoodsStatus', 'CanSaleDay', 'CostPrice', 'Weight' ] eachinfor['ShopSKU'] = shopsku if activeflag == 'DE': eachinfor['ShopSKUKEY'] = [ '{}.Price'.format(obj.ShopName), '{}.DEShipping'.format(obj.ShopName), '{}.DEQuantity'.format(obj.ShopName), '{}.Shipping'.format(obj.ShopName), '{}.Status'.format(obj.ShopName) ] elif activeflag == 'GB': eachinfor['ShopSKUKEY'] = [ '{}.Price'.format(obj.ShopName), '{}.GBShipping'.format(obj.ShopName), '{}.GBQuantity'.format(obj.ShopName), '{}.Shipping'.format(obj.ShopName), '{}.Status'.format(obj.ShopName) ] elif activeflag == 'US': eachinfor['ShopSKUKEY'] = [ '{}.Price'.format(obj.ShopName), '{}.USShipping'.format(obj.ShopName), '{}.USQuantity'.format(obj.ShopName), '{}.Shipping'.format(obj.ShopName), '{}.Status'.format(obj.ShopName) ] else: eachinfor['ShopSKUKEY'] = [ '{}.Price'.format(obj.ShopName), '{}.Shipping'.format(obj.ShopName), '{}.Quantity'.format(obj.ShopName), '{}.Status'.format(obj.ShopName) ] infor.append(eachinfor) # 这里调取redis数据 sInfors = py_SynRedis_tables_obj.readData_Redis_table(infor) num = 0 for a, sinfor in enumerate(sInfors): try: if activeflag == 'STANDARD': yf = sinfor['ShopSKUKEY'][1] else: if activeflag in obj.WishExpress: yf = sinfor['ShopSKUKEY'][1] elif activeflag not in obj.WishExpress: yf = sinfor['ShopSKUKEY'][3] cb = sinfor['ShopSKUKEY'][0] if cb is None or cb.strip() == '': cb = 0 if yf is None or yf.strip() == '': yf = 0 sellingPrice = float(cb) + float(yf) calculate_price_obj = calculate_price( str(sinfor['SKU']), float(sinfor['SKUKEY'][2]), float(sinfor['SKUKEY'][3])) profitrate_info = calculate_price_obj.calculate_profitRate( sellingPrice, platformCountryCode='WISH-US', DestinationCountryCode='US') profitrate = profitrate_info['profitRate'] except: sellingPrice = '' profitrate = ' ' profit_id = str(sinfor['SKU']) + str(num) num += 1 goodsstatus = sinfor['SKUKEY'][0] if goodsstatus == '1': goodsstatus = u'正常' if goodsstatus == '2': goodsstatus = u'售完下架' if goodsstatus == '3': goodsstatus = u'临时下架' if goodsstatus == '4': goodsstatus = u'停售' style = '' if goodsstatus != u'正常': style = 'class ="danger"' # 非正常为红色 elif sinfor['ShopSKUKEY'][-1] == 'Disabled': style = 'class ="active"' # 正常 Disabled 为 灰色 elif sinfor['ShopSKUKEY'][-1] == 'Enabled': style = 'class ="success"' # 正常 Enabled 为 绿色 if not sinfor['ShopSKUKEY'][1]: sinfor['ShopSKUKEY'][1] = '' if not sinfor['ShopSKUKEY'][2]: sinfor['ShopSKUKEY'][2] = '' rt = u'%s <tr %s><td><label><input type="checkbox" name="shopskucheck" id="%s_%s_%s" ></label></td>' \ u'<td>%s</td><td>%s</td><td>%s</td><td>%s</td>' \ u'<td>%s</td><td>%s</td><td>%s</td><td><a><span id="%s">%s</span></a></td><td>%s</td>' % \ (rt, style, obj.id, a, obj.ShopName, sinfor['SKU'], goodsstatus, sinfor['SKUKEY'][1], sinfor['ShopSKU'].replace('<', '<').replace('>', '>'), sinfor['ShopSKUKEY'][0], sinfor['ShopSKUKEY'][1], sinfor['ShopSKUKEY'][2], profit_id, profitrate, sinfor['ShopSKUKEY'][-1],) rt = u"%s<script>$('#%s').on('click',function()" \ u"{layer.open({type:2,skin:'layui-layer-lan',title:'算价表'," \ u"fix:false,shadeClose: true,maxmin:true,area:['1300px','900px']," \ u"content:'/price_list/?SKU=%s&sellingPrice=%s&platformCountryCode=%s&DestinationCountryCode=%s',});});</script>" % ( rt, profit_id, sinfor['SKU'], sellingPrice, 'WISH-US', 'US') rt = u'%s<tr><td></td><td><a id="link_id_%s">查看变体</a></td></tr>' % ( rt, obj.id) rt = u"%s</tbody></table><script>$('#link_id_%s').on('click',function()" \ u"{var index = layer.open({type:2,skin:'layui-layer-lan',title:'%s'," \ u"fix:false,shadeClose: true,maxmin:true,area:['90%%','80%%'],btn: ['关闭页面']," \ u"content:'/t_online_info_wish_store/ShopSKU/?abc=%s&express=%s&readonly=readonly',});" \ u"});</script>" % (rt, obj.id, activeflag, obj.ProductID, activeflag) # rt = rt + u"<script>function en_id_%s(shopsku) {layer.confirm(shopsku + ' 请问确定要进行上架吗?'," \ # u"{btn: ['确定','算了'],btn1:function(){static_refresh('/up_dis_by_wish_api_shopsku/?enshopsku='+shopsku+" \ # u"'&shopname=%s&flag=0');},});}</script>" % (obj.id, obj.ShopName) # rt = rt + u"<script>function dis_id_%s(shopsku) {layer.confirm(shopsku + ' 请问确定要进行下架吗?'," \ # u"{btn: ['确定','算了'],btn1:function(){static_refresh('/up_dis_by_wish_api_shopsku/?disshopsku='+shopsku+" \ # u"'&shopname=%s&flag=0')},});}</script>" % (obj.id, obj.ShopName) return mark_safe(rt)
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 online_sku_binding_modify_puyuan(sku_link_data_list, opnum): """修改普源SKU绑定信息""" public_obj = public() operation_log_obj = t_operation_log_online_syn_py(DBConn=connection) pyconn_obj = py_conn() conn_result = pyconn_obj.py_conn_database() sqlserver_cursor = conn_result['py_cursor'] for sku_link_data in sku_link_data_list: old_sku = sku_link_data['old_sku'] new_sku = sku_link_data['new_sku'] shop_sku = sku_link_data['shop_sku'] person_code = sku_link_data['person_code'] memo = sku_link_data['memo'] shop_name = sku_link_data['shop_name'] if sqlserver_cursor: delete_data_dict = { 'sku': old_sku, 'shop_sku': shop_sku, 'person_code': person_code, 'memo': memo, 'shop_name': shop_name } add_data_dict = { 'sku': new_sku, 'shop_sku': shop_sku, 'person_code': person_code, 'memo': memo, 'shop_name': shop_name } b_goodsskulinkshop_data_delete_list = get_param( sku_link_data=delete_data_dict, opflag='delete') b_goodsskulinkshop_data_add_list = get_param( sku_link_data=add_data_dict, opflag='add') delete_result = public_obj.goodsskulinkshop_info_to_pydb( b_goodsskulinkshop_data_list= b_goodsskulinkshop_data_delete_list, sqlserverInfo=conn_result) if delete_result['errorcode'] == 0: add_result = public_obj.goodsskulinkshop_info_to_pydb( b_goodsskulinkshop_data_list= b_goodsskulinkshop_data_add_list, sqlserverInfo=conn_result) if add_result['errorcode'] == 0: status = 'over' error_info = str(delete_data_dict) + str(add_data_dict) link_id = sku_link_data['id'] b_goodsskulinkshop.objects.filter(NID=link_id).update( Falg=3, SKU=new_sku) shop_code_list = (u'{}'.format( delete_data_dict['memo'])).split('-') if len(shop_code_list) >= 2: shopname = u'{}-{}'.format(shop_code_list[0], shop_code_list[1]) else: shopname = delete_data_dict['memo'] classshopsku_obj = classshopsku(db_conn=None, redis_conn=redis_coon, shopname=shopname) classshopsku_obj.setSKU(shopsku=shop_sku, sku=new_sku) else: status = 'error' error_info = add_result['errortext'] else: status = 'error' error_info = delete_result['errortext'] else: status = 'error' error_info = u'普源数据库连接失败' ooResult = operation_log_obj.updateStatusP(opnum=opnum, opkey=shop_sku, status=status, elogs=error_info) assert ooResult['errorcode'] == 0, ooResult['errortext'] pyconn_obj.py_close_conn_database()
def ebay_unfold_distribution(id, client, request_user): """去铺货""" redis_conn = get_redis_connection(alias='product') classshopsku_obj = classshopsku(db_conn=client, redis_conn=redis_conn) keylist = [ "Category1", "Category2", "`Condition`", "ConditionBewrite", "Quantity", "LotSize", "Duration", "ReservePrice", "BestOffer", "BestOfferAutoAcceptPrice", "BestOfferAutoRefusedPrice", "AcceptPayment", "PayPalEmailAddress", "Location", "LocationCountry", "ReturnsAccepted", "RefundOptions", "ReturnsWithin", "ReturnPolicyShippingCostPaidBy", "ReturnPolicyDescription", "GalleryType", "Bold", "PrivateListing", "HitCounter", "sku", "PictureURL", "Title", "SubTitle", "IbayCategory", "StartPrice", "BuyItNowPrice", "other_itemnum", "ShippingService1", "ShippingServiceCost1", "ShippingServiceAdditionalCost1", "ShippingService2", "ShippingServiceCost2", "ShippingServiceAdditionalCost2", "ShippingService3", "ShippingServiceCost3", "ShippingServiceAdditionalCost3", "ShippingService4", "ShippingServiceCost4", "ShippingServiceAdditionalCost4", "InternationalShippingService1", "InternationalShippingServiceCost1", "InternationalShippingServiceAdditionalCost1", "InternationalShipToLocation1", "InternationalShippingService2", "InternationalShippingServiceCost2", "InternationalShippingServiceAdditionalCost2", "InternationalShipToLocation2", "InternationalShippingService3", "InternationalShippingServiceCost3", "InternationalShippingServiceAdditionalCost3", "InternationalShipToLocation3", "InternationalShippingService4", "InternationalShippingServiceCost4", "InternationalShippingServiceAdditionalCost4", "InternationalShipToLocation4", "InternationalShippingService5", "InternationalShippingServiceCost5", "InternationalShippingServiceAdditionalCost5", "InternationalShipToLocation5", "DispatchTimeMax", "ExcludeShipToLocation", "StoreCategory1", "StoreCategory2", "IbayTemplate", "IbayInformation", "IbayComment", "Description", "`Language`", "IbayOnlineInventoryHold", "IbayRelistSold", "IbayRelistUnsold", "IBayEffectType", "IbayEffectImg", "IbayCrossSelling", "Variation", "outofstockcontrol", "EPID", "ISBN", "UPC", "EAN", "SecondOffer", "ImmediatelyPay", "Currency", "LinkedPayPalAccount", "MBPVCount", "MBPVPeriod", "MUISICount", "MUISIPeriod", "MaximumItemCount", "MinimumFeedbackScore", "Specifics1", "Specifics2", "Specifics3", "Specifics4", "Specifics5", "Specifics6", "Specifics7", "Specifics8", "Specifics9", "Specifics10", "Specifics11", "Specifics12", "Specifics13", "Specifics14", "Specifics15", "Specifics16", "Specifics17", "Specifics18", "Specifics19", "Specifics20", "Specifics21", "Specifics22", "Specifics23", "Specifics24", "Specifics25", "Specifics26", "Specifics27", "Specifics28", "Specifics29", "Specifics30", "CreateTime", "CreateStaff", "UpdateTime", "UpdateStaff", "OssUrl", "CoreWords", "Flag", "Status", "ExcelFile", "variationSpecificsSet", "Images", "ShopSets", "TimePlan", "Site", "Selleruserid", "Seller" ] cur = client.cursor() sql = "select Category1,Category2,`Condition`,ConditionBewrite,Quantity,LotSize,Duration,"\ "ReservePrice,BestOffer,BestOfferAutoAcceptPrice,BestOfferAutoRefusedPrice,"\ "AcceptPayment,PayPalEmailAddress,Location,LocationCountry,ReturnsAccepted,"\ "RefundOptions,ReturnsWithin,ReturnPolicyShippingCostPaidBy,"\ "ReturnPolicyDescription,GalleryType,Bold,PrivateListing,HitCounter,"\ "sku,PictureURL,Title,SubTitle,IbayCategory,StartPrice,BuyItNowPrice,"\ "other_itemnum,ShippingService1,ShippingServiceCost1,ShippingServiceAdditionalCost1,"\ "ShippingService2,ShippingServiceCost2,ShippingServiceAdditionalCost2,"\ "ShippingService3,ShippingServiceCost3,ShippingServiceAdditionalCost3,"\ "ShippingService4,ShippingServiceCost4,ShippingServiceAdditionalCost4,"\ "InternationalShippingService1,InternationalShippingServiceCost1,"\ "InternationalShippingServiceAdditionalCost1,InternationalShipToLocation1,"\ "InternationalShippingService2,InternationalShippingServiceCost2,"\ "InternationalShippingServiceAdditionalCost2,InternationalShipToLocation2,"\ "InternationalShippingService3,InternationalShippingServiceCost3,"\ "InternationalShippingServiceAdditionalCost3,InternationalShipToLocation3,"\ "InternationalShippingService4,InternationalShippingServiceCost4,"\ "InternationalShippingServiceAdditionalCost4,InternationalShipToLocation4,"\ "InternationalShippingService5,InternationalShippingServiceCost5,"\ "InternationalShippingServiceAdditionalCost5,InternationalShipToLocation5,"\ "DispatchTimeMax,ExcludeShipToLocation,StoreCategory1,StoreCategory2,"\ "IbayTemplate,IbayInformation,IbayComment,Description,`Language`,"\ "IbayOnlineInventoryHold,IbayRelistSold,IbayRelistUnsold,IBayEffectType,"\ "IbayEffectImg,IbayCrossSelling,Variation,outofstockcontrol,"\ "EPID,ISBN,UPC,EAN,SecondOffer,ImmediatelyPay,Currency,LinkedPayPalAccount,"\ "MBPVCount,MBPVPeriod,MUISICount,MUISIPeriod,MaximumItemCount,MinimumFeedbackScore,"\ "Specifics1,Specifics2,Specifics3,Specifics4,Specifics5,Specifics6,Specifics7,"\ "Specifics8,Specifics9,Specifics10,Specifics11,Specifics12,Specifics13,Specifics14,"\ "Specifics15,Specifics16,Specifics17,Specifics18,Specifics19,Specifics20,Specifics21," \ "Specifics22,Specifics23,Specifics24,Specifics25,Specifics26,Specifics27,Specifics28," \ "Specifics29,Specifics30,CreateTime,CreateStaff,UpdateTime,UpdateStaff,OssUrl,CoreWords,"\ "Flag,`Status`,ExcelFile,variationSpecificsSet,Images,ShopSets,TimePlan,Site,Selleruserid,Seller" \ " from t_templet_ebay_wait_upload where `Status`='OPEN' and id = %s ;" cur.execute(sql, (id,)) objs = cur.fetchall() cur.close() sRes = {'code': -1, 'message': ''} for obj in objs: mydict = {} for i in range(0, len(obj)): mydict[keylist[i]] = obj[i] waitUploadList = [] if mydict['ShopSets'] is not None and mydict['ShopSets'].strip() != '': for shopcode in mydict['ShopSets'].split(','): # ebay 铺货目标店铺 newdict = copy.deepcopy(mydict) try: variation_obj = t_templet_ebay_wait_upload.objects.get(pk=id) except t_templet_ebay_wait_upload.DoesNotExist: sRes['message'] = 't_templet_ebay_wait_upload id: %s is not Exist' % id return sRes ExcelFile = variation_obj.ExcelFile Site = mydict['Site'] newdict['ExcelFile'] = ExcelFile newdict['ShopName'] = shopcode try: store_info_obj = t_config_store_ebay.objects.get(storeName=shopcode) Seller = store_info_obj.storeOwner shop_name = store_info_obj.ShopName # Site = store_info_obj.siteID except t_config_store_ebay.DoesNotExist: Seller = '' shop_name = '' # Site = mydict['Site'] newdict['Seller'] = Seller variations = variation_obj.Variation shop_skus = list() if variations: # 有变体信息 vars_info = eval(variations).get('Variation') if vars_info: for i in vars_info: shop_sku = i.get('SKU') if shop_sku: shop_skus.append(shop_sku) else: shop_sku = variation_obj.sku shop_skus.append(shop_sku) if shop_skus: for shop_sku in shop_skus: if shop_sku.find('+') != -1: sRes['message'] = 'Can not Publish combination shop sku.' return sRes else: sRes['message'] = 'This Product does not find shop sku.' return sRes pro_skus = list() shop_pro_info = dict() for shop_sku in shop_skus: if shop_sku.find('*') != -1: ss = shop_sku.split('*')[0] else: ss = shop_sku pro_sku = classshopsku_obj.getSKU(ss) if not pro_sku: sRes['message'] = 'Can not get product sku for shopsku: %s' % shop_sku return sRes pro_skus.append(pro_sku) shop_pro_info[shop_sku] = pro_sku sku_dict = dict() if pro_skus: product_skus = ','.join(pro_skus) result = shopsku_apply(product_skus, shop_name, apply_type="SONSKUAPPLY", first_name=request_user) if result['result'] == 'DEFEAT' or result.get('defeat_sku'): sRes['message'] = result.get('error_info', '') + str(result.get('defeat_sku', '')) return sRes shop_sku_pro_info = result['success_sku'] db_sku_info = dict() for i in shop_pro_info: num = '' if i.find('*') != -1: num = i.split('*')[-1] for j in shop_sku_pro_info: if shop_pro_info[i] == shop_sku_pro_info[j]: key = i if num: value = j + '*' + num else: value = j sku_dict[key] = value db_sku_info[shop_pro_info[i]] = j if sku_dict: newdict['sku_dict'] = sku_dict else: sRes['message'] = 'Apply New Shop SKU Falied.' return sRes newdict['product_sku'] = db_sku_info.keys() newdict['shopsku'] = db_sku_info.values() # 核心标题 CoreWordsList = get_core_list(client, newdict['CoreWords']) newdict['Title'] = getnewtitle(client, newdict['CoreWords'], CoreWordsList, newdict['Title']) newdict['Description'] = charProcess(newdict['Description']) newdict['Site'] = Site newdict['taskid'] = id waitUploaddict = {} waitUploaddict = newdict.copy() waitUploaddict['id'] = id insert_into_result(newdict, client) result_id = get_upload_result_id(newdict['taskid']) waitUploaddict['result_id'] = result_id waitUploadList.append(waitUploaddict) insert_into_MQ(id, waitUploadList) sRes['code'] = 0 return sRes
def OnTheShelf_OR_LowerFrame_BY_ShopSKU(shopsku, shopname, flag, flagname=''): myresult = {'Code': 0, 'messages': ''} try: upstatus = 'over' # 日志 uptext = None t_wish_store_oplogs_obj = t_wish_store_oplogs(connection) t_store_configuration_file_obj = t_store_configuration_file(connection) t_online_info_wish_obj = t_online_info_wish(connection) store_status = t_store_configuration_file_obj.getshopStatusbyshopcode( shopname) # 获取店铺状态 assert store_status['errorcode'] == 0, store_status['errortext'] if store_status['status'] == '0': # 店铺状态正常的 snstatus = '0' # 店铺状态 classshopsku_obj = classshopsku(redis_conn=redis_coon, shopname=shopname) cwishapi_obj = cwishapi() t_online_info_obj = t_online_info(shopname, connection) auth_info = verb_token(shopname, connection) assert auth_info['errorcode'] == 1, auth_info['errortext'] data = {} data['access_token'] = auth_info.get('access_token', '') data['ShopSKU'] = shopsku proinfo = t_online_info_obj.get_listingid_by_shopname_shopsku( shopsku) assert proinfo[ 'errorcode'] == 0, 'EDGetProductIDSuccess:%s' % proinfo[ 'errortext'] if flag == 'enshopsku': # ShopSKU上架 sstatus = 'Enabled' result = cwishapi_obj.update_to_enable(data, timeout=30) else: sstatus = 'Disabled' result = cwishapi_obj.update_to_disable(data, timeout=30) _content = eval(result._content) if result.status_code == 200 and _content['code'] == 0: uResult = t_online_info_obj.update_status_by_shopsku( sstatus, shopsku) # 更新shopsku状态数据 assert uResult[ 'errorcode'] == 0, 'EDSuccess:%s' % uResult['errortext'] classshopsku_obj.setStatus(shopsku, sstatus) # 修改redis数据 toResult = ToCalculateADS(proinfo['productid'], shopname, connection) assert toResult in [ '0', '1' ], toResult['errortext'] # 获取该product的在线不在线的状态 if flag == 'enshopsku': # ShopSKU上架 myresult['messages'] = u'%s:上架成功' % (shopsku, ) else: myresult['messages'] = u'%s:下架成功' % (shopsku, ) else: if _content.get('code') in [1000, 1028, 1006, 1031]: # 平台禁止修改加钻产品标题 toResult = ToCalculateADS(proinfo['productid'], shopname, connection) assert toResult in [ '0', '1' ], toResult['errortext'] # 获取该product的在线不在线的状态 else: toResult = '-1' upstatus = 'error' # 日志 uptext = business_Terms(_content) myresult['messages'] = u'%s:上下架操作失败!%s' % ( shopsku, business_Terms(_content)) refreshwishlisting.run({ 'ShopName': shopname, 'dbcnxn': connection, 'ProductID': proinfo['productid'] }) # Wish数据更新操作 adResult = t_online_info_wish_obj.UpdateWishStatusAD( proinfo['productid'], toResult) assert adResult['errorcode'] == 1, adResult['errortext'] myresult['_content'] = _content else: upstatus = 'error' snstatus = '-1' # listing 店铺状态标记 uptext = u'店铺状态被标记为异常' myresult['_content'] = uptext myresult['messages'] = u'%s:上下架操作失败!%s' % (shopsku, uptext) uPResult = t_wish_store_oplogs_obj.updateStatusP( flagname, shopsku, upstatus, uptext) assert uPResult['errorcode'] == 0, uPResult['errortext'] SNResult = t_online_info_wish_obj.UpdateWishSNameByShopName( shopname, snstatus) # shopname assert SNResult['errorcode'] == 1, SNResult['errortext'] myresult['Code'] = 1 except Exception, e: myresult['Code'] = -1 myresult['messages'] = '%s:%s:%s' % (shopsku, Exception, e)
def download_excel_by_porductid(list, opname='', warehouse='STANDARD'): myresult = {} try: classlisting_obj = classlisting(connection, redis_coon) t_wish_store_oplogs_obj = t_wish_store_oplogs(connection) username = opname.split('-')[-1] path = MEDIA_ROOT + 'download_xls/' + username mkdir_p(MEDIA_ROOT + 'download_xls') os.popen('chmod 777 %s' % (MEDIA_ROOT + 'download_xls')) mkdir_p(path) os.popen('chmod 777 %s' % (path, )) datalist = [] datalist.append( ['shopname', 'productid', 'sku', 'shopsku', 'Inventory']) num = len(list) for i, elist in enumerate(list): shopname = elist[0] productid = elist[1] parentsku = elist[2] classshopsku_obj = classshopsku(connection, redis_coon, shopname=shopname) shopskulist = classlisting_obj.getJoomShopSKUList(productid) for shopsku in shopskulist: sku = classshopsku_obj.getSKU(shopsku) if warehouse == 'STANDARD': inventory = classshopsku_obj.getQuantity(shopsku) else: q = getattr(classshopsku_obj, 'getWish%sQuantity' % warehouse) inventory = q(shopsku) datalist.append([shopname, productid, sku, shopsku, inventory]) if i < num - 1: uResult = t_wish_store_oplogs_obj.updateStatusP( opname, opname, 'runing', '') assert uResult['errorcode'] == 0, uResult['errortext'] filename = username + '_' + syntime.now().strftime( '%Y%m%d%H%M%S') + '.xls' eResult = generate_excel(datalist, path + '/' + filename) assert eResult['code'] == 0, eResult['error'] os.popen(r'chmod 777 %s' % (path + '/' + filename)) upload_to_oss_obj = upload_to_oss(BUCKETNAME_XLS) upResult = upload_to_oss_obj.upload_to_oss({ 'path': username, 'name': filename, 'byte': open(path + '/' + filename), 'del': 0 }) assert upResult['errorcode'] == 0, upResult['errortext'] filepath = upResult['result'] uResult = t_wish_store_oplogs_obj.updateStatusP( opname, opname, 'over', filepath) assert uResult['errorcode'] == 0, uResult['errortext'] myresult['Code'] = 1 myresult['messages'] = u'文件生成成功! \n 下载链接:%s' % (filepath, ) except Exception, e: myresult['Code'] = -1 myresult['messages'] = '%s:%s:%s' % (opname, Exception, e)
from django.utils.safestring import mark_safe from django.contrib import messages from app_djcelery.tasks import amazon_product_refresh from skuapp.table.t_online_info_amazon import t_online_info_amazon import datetime # from brick.pydata.py_redis.py_SynRedis_pub import py_SynRedis_pub from brick.classredis.classshopsku import classshopsku from django_redis import get_redis_connection from django.db import connection from skuapp.table.t_shopsku_information_binding import t_shopsku_information_binding from brick.classredis.classsku import classsku from skuapp.table.t_amazon_cpc_ad import t_amazon_cpc_ad # py_SynRedis_pub_obj = py_SynRedis_pub() redis_conn = get_redis_connection(alias='product') classshopskuobjs = classshopsku(connection, redis_conn) classsku_obj = classsku() import urllib from django.utils.safestring import mark_safe from django_redis import get_redis_connection from django.contrib import messages from django.db import connection from django.db.models import Q from django.http import HttpResponseRedirect # from skuapp.table.t_store_marketplan_execution import t_store_marketplan_execution # from skuapp.table.t_upload_shopname import t_upload_shopname from brick.public.django_wrap import django_wrap from brick.pydata.py_redis.py_SynRedis_tables import py_SynRedis_tables from brick.classredis.classlisting import classlisting
def run(params): # 获取需要刷新的数据 t_online_info_objs = t_online_info(params['ShopName'], params['dbcnxn']) t_report_orders1days_objs = t_report_orders1days(params['dbcnxn']) t_store_configuration_file_obj = t_store_configuration_file( params['dbcnxn']) classlisting_obj = classlisting(params['dbcnxn']) classmainsku_obj = classmainsku(params['dbcnxn']) t_templet_wish_upload_result_obj = t_templet_wish_upload_result( params['dbcnxn']) classshopsku_obj = classshopsku(params['dbcnxn'], shopname=params['ShopName']) t_online_info_wish_obj = t_online_info_wish(params['dbcnxn']) t_distribution_product_to_store_result_obj = t_distribution_product_to_store_result( params['dbcnxn']) t_large_small_corresponding_cate_obj = t_large_small_corresponding_cate( params['dbcnxn']) t_wish_pb_campaignproductstats_obj = t_wish_pb_campaignproductstats( params['dbcnxn']) infodata = t_online_info_objs.getshopproductdata(params['ProductID']) for obj in infodata: datedict = {} datedict['ProductID'] = obj['ProductID'] datedict['ShopName'] = obj['ShopName'] datedict['Title'] = obj['Title'] datedict['SKU'] = obj['SKU'] datedict['ShopSKU'] = obj['ShopSKU'] datedict['Price'] = obj['Price'] datedict['RefreshTime'] = obj['RefreshTime'] yyyymmdd = obj['RefreshTime'].strftime('%Y%m%d') datedict['SoldTheDay'] = t_report_orders1days_objs.getSoldTheDay( obj['ProductID'], yyyymmdd) datedict['SoldYesterday'] = t_report_orders1days_objs.getSoldYesterday( obj['ProductID'], yyyymmdd) datedict['Orders7Days'] = t_report_orders1days_objs.getOrders7Days( obj['ProductID'], yyyymmdd) # 默认 普通仓 # 海外仓 7天订单数,总销量 订单不足 数值偏小 datedict[ 'Order7daysDE'] = t_report_orders1days_objs.getOrders7Days_WarehouseName( obj['ProductID'], yyyymmdd, 'DE') datedict[ 'Order7daysGB'] = t_report_orders1days_objs.getOrders7Days_WarehouseName( obj['ProductID'], yyyymmdd, 'GB') datedict[ 'Order7daysUS'] = t_report_orders1days_objs.getOrders7Days_WarehouseName( obj['ProductID'], yyyymmdd, 'US') datedict[ 'Order7daysFBW'] = t_report_orders1days_objs.getOrders7Days_WarehouseName( obj['ProductID'], yyyymmdd, 'FBW') datedict[ 'OfsalesDE'] = t_report_orders1days_objs.ofSales_Of_ProductID_WarehouseName( obj['ProductID'], 'DE') datedict[ 'OfsalesGB'] = t_report_orders1days_objs.ofSales_Of_ProductID_WarehouseName( obj['ProductID'], 'GB') datedict[ 'OfsalesUS'] = t_report_orders1days_objs.ofSales_Of_ProductID_WarehouseName( obj['ProductID'], 'US') datedict[ 'OfsalesFBW'] = t_report_orders1days_objs.ofSales_Of_ProductID_WarehouseName( obj['ProductID'], 'FBW') classlisting_obj.set_order7days_listingid(datedict['ProductID'], datedict['Orders7Days']) datedict['SoldXXX'] = int(datedict['SoldTheDay']) - int( datedict['SoldYesterday']) datedict['DateOfOrder'] = None datedict['Image'] = obj['Image'] datedict['Status'] = obj['Status'] datedict['ReviewState'] = obj['ReviewState'] datedict['DateUploaded'] = obj['DateUploaded'] datedict['LastUpdated'] = obj['LastUpdated'] datedict['OfSales'] = obj['OfSales'] datedict['ParentSKU'] = obj['ParentSKU'] datedict['is_promoted'] = obj['is_promoted'] datedict['WishExpress'] = obj['WishExpress'] datedict[ 'Seller'] = t_store_configuration_file_obj.getsellerbyshopcode( params['ShopName'][0:9]) datedict['MainSKU'] = obj['MainSKU'] datedict['TortInfo'] = 'N' mainskulist = classlisting_obj.getmainsku(obj['ProductID']) if mainskulist: tortlist = [] for mainsku in set(mainskulist): tortsite = classmainsku_obj.get_tort_by_mainsku(mainsku) if tortsite is not None: tortlist = tortlist + tortsite if tortlist: if 'Wish' in tortlist: datedict['TortInfo'] = 'WY' else: datedict['TortInfo'] = 'Y' datedict['DataSources'] = "NORMAL" if t_templet_wish_upload_result_obj.get_count_num( obj['ParentSKU'], obj['ShopName']) >= 1: datedict['DataSources'] = "UPLOAD" else: if t_distribution_product_to_store_result_obj.get_count_num( obj['ParentSKU'], obj['ShopName']) >= 1: datedict['DataSources'] = "UPLOAD" datedict['OperationState'] = 'No' if obj['Status'] == 'Disabled': datedict['OperationState'] = 'Yes' # MainSKU,大类,小类 datedict['MainSKULargeCate'] = None datedict['MainSKUSmallCate'] = None mlist = [] if datedict['MainSKU']: mlist = re.findall(r'[0-9]+|[a-z]+|[A-Z|]+|[-]', datedict['MainSKU']) if len(mlist) >= 1: datedict['MainSKUSmallCate'] = mlist[0] # 小类 lResult = t_large_small_corresponding_cate_obj.getLargeClassBySmallClass( datedict['MainSKUSmallCate']) if lResult['code'] == 1: datedict['MainSKULargeCate'] = lResult['largecode'] datedict['SName'] = '0' # 店铺状态 0 正常 -1 异常 # 1 正常(在线); 0 正常(不在线); -1 api调用错误; -2 执行代码异常; if datedict['ReviewState'] == 'approved' and datedict[ 'Status'] == 'Enabled': datedict['AdStatus'] = '1' # 1 正常(在线) else: datedict['AdStatus'] = '0' # 0 正常(不在线) datedict['Published'] = None if obj['ShopSKU'] is not None and obj['ShopSKU'].strip() != '': pub = None for shopsku in obj['ShopSKU'].split(','): for each_shopsku in shopsku.split('+'): pub = classshopsku_obj.getPublished( each_shopsku.split('*')[0].split('\\')[0].split('/') [0]) # 按照店铺SKU来抓取刊登人 if pub: break if pub: break datedict['Published'] = pub datedict['market_time'] = None # 这里是商品状态结果集 statuslist = t_online_info_objs.get_goodsstatus_by_productid( params['ProductID']) a1 = 0 if '1' in statuslist: a1 = 1 a2 = 0 if '2' in statuslist: a2 = 1 a3 = 0 if '3' in statuslist: a3 = 1 a4 = 0 if '4' in statuslist: a4 = 1 datedict['GoodsFlag'] = a1 * 1000 + a2 * 100 + a3 * 10 + a4 # 店铺SKU状态结果集 sstatuslist = t_online_info_objs.get_shopskustatus_by_productid( params['ProductID']) b1 = 0 if 'Enabled' in sstatuslist: b1 = 1 b2 = 0 if 'Disabled' in sstatuslist: b2 = 1 datedict['ShopsFlag'] = b1 * 10 + b2 # 绑定状态结果集 datedict['BindingFlag'] = 1 # 默认全部绑定 # anum = obj[-1] # 变体数量 # unum = t_online_info_objs.get_binding_by_productid(params['ProductID']) # if int(anum) == int(unum): # datedict['BindingFlag'] = 3 # elif int(anum) > 0 and int(unum) == 0: # datedict['BindingFlag'] = 1 # elif int(anum) > 0 and int(unum) != 0: # datedict['BindingFlag'] = 2 # print datedict datedict['BeforeReviewState'] = obj['BeforeReviewState'] # 上一个wish查看状态 datedict['Rating'] = 0 # 链接的评分 if float(datedict['OfSales']) > 0: rResult = t_online_info_wish_obj.get_product_id_rating( datedict['ProductID']) old_rating = rResult.get('rating', 0) Gresult = get_rating(datedict['ProductID']) new_rating = Gresult.get('rating', 0) if new_rating == 0 and datedict[ 'ReviewState'] == 'rejected': # 被拒绝的连接 datedict['Rating'] = old_rating elif new_rating != 0: datedict['Rating'] = new_rating datedict['ADShow'] = 0 # 是否有广告 0 没有 1 有 adResult = t_wish_pb_campaignproductstats_obj.j_ad( datedict['ProductID']) if adResult['errorcode'] == 1: datedict['ADShow'] = 1 # 周销量趋势 -1:下降 0:平稳 1:上升 datedict['SalesTrend'] = get_salestrend(datedict['ProductID'], t_report_orders1days_objs) datedict['FBW_Flag'] = obj['FBW_Flag'] # fbw 标记 print "datedict['ProductID']=============%s" % datedict['ProductID'] t_online_info_wish_obj.refreshwishdata(datedict)
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_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 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 shopsku_apply(input_sku, shop_name, apply_type="SONSKUAPPLY", first_name="UPLOAD", dq_username=None, force_apply='NO', ZHtitle='', reason=None): cur = connection.cursor() defeat_sku_info = {} success_sku_info = {} shopsku_apply_result = { 'result': '', 'defeat_sku': '', 'success_sku': '', 'error_code': '', 'error_info': '' } ShopskuApply_obj = ShopskuApply(cur=cur, shop_name=shop_name, staff_name=first_name, apply_type=apply_type) ShopskuApply_obj.get_shop_name_full() # 查询店铺名全称 shop_name_temp_info = ShopskuApply_obj.get_shop_name_temp() # 查询店铺名简写 if shop_name_temp_info['error_code'] == 0: shop_name_temp = shop_name_temp_info['shop_name_temp'] shop_code_info = ShopskuApply_obj.get_shop_code() # 获取该店铺的特征码 shop_code = '' table_exists = False # 存在店铺特征码、店铺seq表 if shop_code_info['error_code'] == 0: shop_code = shop_code_info['shop_code'] table_exists = True # 存在店铺特征码,不存在店铺seq表 elif shop_code_info['error_code'] == 30000: shop_code = shop_code_info['shop_code'] create_shop_table_info = create_shop_table( cur=cur, shop_name=shop_name_temp) if create_shop_table_info['error_code'] == 0: table_exists = True else: shopsku_apply_result = change_global_result( result_info=create_shop_table_info, result='DEFEAT') # 不存在店铺特征码、店铺seq表 elif shop_code_info['error_code'] == 10000: create_shop_table_info = create_shop_table( cur=cur, shop_name=shop_name_temp) if create_shop_table_info['error_code'] == 0: shop_code = create_shop_table_info['shop_code'] table_exists = True else: shopsku_apply_result = change_global_result( result_info=create_shop_table_info, result='DEFEAT') else: shopsku_apply_result = change_global_result( result_info=shop_code_info, result='DEFEAT') if shop_code and table_exists: # 如果查询到店铺特征码,并且店铺seq表存在,则进行处理SKU文件 try: shopcode = shop_name.split( '-')[0].title() + '-' + shop_name.split('-')[1] classshopsku_obj = classshopsku( db_conn=None, redis_conn=redis_coon, shopname=shopcode if shop_name.split('-')[0].title() == 'Wish' else None) except Exception as ex: classshopsku_obj = classshopsku(db_conn=None, redis_conn=redis_coon) SKUProcess_obj = SKUProcess(cur=cur, input_sku=input_sku, file_type=apply_type, first_name=first_name, force_apply=force_apply, ZHtitle=ZHtitle) sku_process_info = SKUProcess_obj.sku_process() defeat_sku_info = SKUProcess_obj.defeat_sku_info if sku_process_info['error_code'] == 0: all_sku_list = sku_process_info['all_sku_list'] for sku in all_sku_list: get_random_code_info = ShopskuApply_obj.get_random_code() if get_random_code_info['error_code'] == 0: random_code = get_random_code_info['random_code'] insert_seq_info = ShopskuApply_obj.insert_seq( random_code=random_code, sku=sku) if insert_seq_info['error_code'] == 0: insert_id = insert_seq_info['insert_id'] shop_sku = shop_code + str(insert_id) ShopskuApply_obj.insert_log( sku=sku, status='APPLYSUCCESS', random_code=random_code, shop_sku=shop_sku, input_sku=input_sku, dq_username=dq_username, force_apply=force_apply, reason=reason) insert_link_info = ShopskuApply_obj.insert_link( sku=sku, shop_sku=shop_sku) if insert_link_info['error_code'] == 0: success_sku_info[shop_sku] = sku # 写入redis classshopsku_obj.setSKU(shopsku=shop_sku, sku=sku) else: defeat_sku_info[sku] = insert_link_info[ 'error_info'] else: defeat_sku_info[sku] = insert_seq_info[ 'error_info'] else: defeat_sku_info[sku] = get_random_code_info[ 'error_info'] shopsku_apply_result = change_global_result( result='SUCCESS', defeat_sku=defeat_sku_info, success_sku=success_sku_info) else: shopsku_apply_result = change_global_result( result_info=sku_process_info, result='DEFEAT') else: shopsku_apply_result = change_global_result( result_info=shop_name_temp_info, result='DEFEAT') for k, v in defeat_sku_info.items(): ShopskuApply_obj.insert_log(sku=k, status='APPLYDEFEAT', error_info=v, input_sku=input_sku, force_apply=force_apply, reason=reason) # 将错误的sku信息插入到log表 cur.execute('commit;') cur.close() return shopsku_apply_result
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 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'}
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 OnTheShelf_OR_LowerFrame(list, flag, synname): sresult = {'Code': 0, 'messages': ''} try: shopname = list[0] productid = list[1] parentsku = list[2] upstatus = 'over' # 日志 uptext = None t_store_configuration_file_obj = t_store_configuration_file(connection) t_wish_store_oplogs_obj = t_wish_store_oplogs(connection) t_online_info_wish_obj = t_online_info_wish(connection) store_status = t_store_configuration_file_obj.getshopStatusbyshopcode( shopname) # 获取店铺状态 assert store_status['errorcode'] == 0, store_status['errortext'] if store_status['status'] == '0': # 店铺状态正常的 snstatus = '0' # listing 店铺状态标记 classlisting_obj = classlisting(db_conn=connection, redis_conn=redis_coon) cwishapi_obj = cwishapi() classshopsku_obj = classshopsku(redis_conn=redis_coon, shopname=shopname) t_online_info_obj = t_online_info(shopname, connection, redis_coon) params = CreateParam(shopname, productid, parentsku) if flag == 'enable': Status = 'Enabled' result = cwishapi_obj.enable_by_wish_api(params, timeout=30) else: Status = 'Disabled' result = cwishapi_obj.disable_by_wish_api(params, timeout=30) _content = eval(result._content) if result.status_code == 200 and _content['code'] == 0: aResult = t_online_info_wish_obj.UpdateWishStatus( productid, Status) assert aResult['errorcode'] == 1, aResult['errortext'] iResult = t_online_info_obj.update_status_by_productid( Status, productid) assert iResult[ 'errorcode'] == 0, "SynSuccess:%s" % iResult['errortext'] shopskulist = classlisting_obj.getShopSKUList(productid) if shopskulist is None: shopskulist = [] for shopsku in shopskulist: classshopsku_obj.setStatus(shopsku, Status) toResult = ToCalculateADS(productid, shopname, connection) assert toResult in [ '0', '1' ], toResult['errortext'] # 获取该product的在线不在线的状态 if flag == 'enable': sresult['messages'] = u'%s:上架成功' % (productid, ) else: sresult['messages'] = u'%s:下架成功' % (productid, ) else: if _content.get('code') in [1000, 1028, 1006, 1031]: # 平台禁止修改加钻产品标题 toResult = ToCalculateADS(productid, shopname, connection) assert toResult in [ '0', '1' ], toResult['errortext'] # 获取该product的在线不在线的状态 else: toResult = '-1' # 单链接操作状态 upstatus = 'error' uptext = business_Terms(_content) sresult['messages'] = u'%s:%s' % (productid, business_Terms(_content)) refreshwishlisting.run({ 'ShopName': shopname, 'dbcnxn': connection, 'ProductID': productid }) # Wish数据更新操作 adResult = t_online_info_wish_obj.UpdateWishStatusAD( productid, toResult) assert adResult['errorcode'] == 1, adResult['errortext'] else: upstatus = 'error' snstatus = '-1' # listing 店铺状态标记 uptext = u'店铺状态被标记为异常' sresult['messages'] = u'%s:%s' % (productid, uptext) uResult = t_wish_store_oplogs_obj.updateStatusP( synname, productid, upstatus, uptext) assert uResult['errorcode'] == 0, uResult['errortext'] SNResult = t_online_info_wish_obj.UpdateWishSNameByShopName( shopname, snstatus) # listing assert SNResult['errorcode'] == 1, SNResult['errortext'] sresult['Code'] = 1 except Exception, e: sresult['Code'] = -1 sresult['messages'] = '%s:%s' % (Exception, e)
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'}