def select_inboundlistItem_between_time(shipmentId,result): cursor, conn = common_unit.database_connection() # rs = cursor.execute("SELECT * FROM test_inboundlist_item where time Between '2018-11-23 14:00:00' AND '2018-11-23 16:00:00'") rs = cursor.execute("SELECT * FROM test_inboundlist_item where shipment_id='%s'" % (shipmentId)) if rs!=0: result = json.loads(result) members = result['ListInboundShipmentItemsResponse']['ListInboundShipmentItemsResult']['ItemData']['member'] members.clear() for i in cursor.fetchall(): dict_m = {} id = i[0] shipment_id = i[1] seller_sku = i[2] quantity_shiped = i[3] netword_sku = i[4] dict_m['ShipmentId'] = shipment_id dict_m['SellerSKU'] = seller_sku dict_m['QuantityShipped'] =quantity_shiped dict_m['QuantityInCase'] =0 dict_m['QuantityReceived'] =0 dict_m['FulfillmentNetworkSKU'] = netword_sku members.append(dict_m) rr = result # print(rr) else: rr = inboundShipmentsItems(shipmentId,result) cursor.close() conn.close() return rr
def insert_test_outboundlist_item(outbound_item_list): cursor, conn = common_unit.database_connection() cursor.execute('INSERT INTO test_outboundlist_item(seller_order_id,sku,quantity,order_time,asin,time) values(%s,%s,%s,%s,%s,%s)',outbound_item_list) conn.commit() cursor.close() conn.close() return
def getFulfillmentOrder(sellerOrderID,result): # print(sellerOrderID) cursor, conn = common_unit.database_connection() rs = cursor.execute("SELECT seller_order_id,sku,quantity,order_time,asin FROM test_outboundlist_item where seller_order_id='%s'"%(sellerOrderID)) if rs!=0: outbound_list_item = cursor.fetchone() result = json.loads(result) # print(result) FulfillmentOrderItem = result['GetFulfillmentOrderResponse']['GetFulfillmentOrderResult']['FulfillmentOrderItem']['member'] # print(FulfillmentOrderItem) FulfillmentOrderItem['SellerSKU'] = outbound_list_item[1] FulfillmentOrderItem['EstimatedShipDateTime'] = outbound_list_item[3] FulfillmentOrderItem['EstimatedArrivalDateTime'] = outbound_list_item[3] FulfillmentOrder = result['GetFulfillmentOrderResponse']['GetFulfillmentOrderResult']['FulfillmentOrder'] # print(FulfillmentOrder) FulfillmentOrder['SellerFulfillmentOrderId'] = outbound_list_item[0] FulfillmentOrder['StatusUpdatedDateTime'] = outbound_list_item[3] FulfillmentOrder['DisplayableOrderDateTime'] = outbound_list_item[3] FulfillmentOrder['ReceivedDateTime'] = outbound_list_item[3] FulfillmentOrder['DisplayableOrderId'] = outbound_list_item[0] FulfillmentShipment = result['GetFulfillmentOrderResponse']['GetFulfillmentOrderResult']['FulfillmentShipment']['member'] # print(FulfillmentShipment) FulfillmentShipment['FulfillmentShipmentItem']['member']['SellerSKU'] = outbound_list_item[1] FulfillmentShipment['FulfillmentShipmentItem']['member']['SellerFulfillmentOrderItemId'] = outbound_list_item[0] FulfillmentShipment['ShippingDateTime'] = outbound_list_item[3] FulfillmentShipment['EstimatedArrivalDateTime'] = outbound_list_item[3] cursor.close() conn.close() return result
def select_order_orderItem(): cursor, conn = common_unit.database_connection() rs = cursor.execute("SELECT order_id,time FROM test_orderlist where status=0") if rs!=0: for orderID in cursor.fetchall(): ll = random_letter() order_id = orderID[0] order_time = orderID[1] seller_order_id = 'DF-CONSUMER-' + str(ll) name = 'DF-' + str(ll) + '-NAME' order_status = 'COMPLETE' outbound_list = [seller_order_id,name,order_time,order_status,order_time,create_time,order_time,order_id,create_time] insert_test_outboundlist(outbound_list) #查询到新增的订单时(状态为0的订单数据),创建出库单据 update_test_orderlist_status(order_id) cursor.execute("SELECT order_item_id,order_id,asin,seller_sku FROM test_orderlist_item where order_id=%s"%(order_id)) # for sku_quantity in cursor.fetchall(): sku_quantity = cursor.fetchone() # order_item_id = sku_quantity[0] asin = sku_quantity[2] sku = sku_quantity[3] quantity = 1 ounbound_list_item = [seller_order_id,sku,quantity,order_time,asin,create_time] insert_test_outboundlist_item(ounbound_list_item) cursor.close() conn.close() return
def select_inboundlist_between_time(start_time,end_time,result): cursor, conn = common_unit.database_connection() start_time = conver_date_time(start_time) end_time = conver_date_time(end_time) rs = cursor.execute("SELECT * FROM test_inboundlist where time Between '%s' AND '%s'"%(start_time,end_time)) if rs!=0: result = json.loads(result) members = result['ListInboundShipmentsResponse']['ListInboundShipmentsResult']['ShipmentData']['member'] members.clear() for i in cursor.fetchall(): dict_m = {} # id = i[0] shipment_id = i[1] shipment_name = i[2] # print(id,shipment_id,shipment_name) dict_m['ShipFromAddress'] = {"PostalCode": "V5V 1A1","Name": "jsowprni Devo CA20","CountryCode": "CA","StateOrProvinceCode": "BC","AddressLine1": "Address Line 1","City": "Vancouver"} dict_m['ShipmentId'] = 'DF-FBA' + str(shipment_id) dict_m['ShipmentName'] = 'DF-' + str(shipment_name) + '-SHIPNAME' dict_m['ShipmentStatus'] = 'WORKING' dict_m['LabelPrepType'] = "NO_LABEL" dict_m['DestinationFulfillmentCenterId'] ="YYZ1" members.append(dict_m) rr = result else: rr = inboundShipments(start_time,result) cursor.close() conn.close() return rr
def list_orders(execute_command,result): # print(result) result = json.loads(result) try: response = result['ListOrdersResponse']['ListOrdersResult']['Orders']['Order'] status = 0 except: response = [] if len(response) == 0: return -1 # 这个真的是气死人了…… # 她妈妈的如果说上面那个是四秒,这个接口起码四十秒(并不 # 亚马逊维护对外接口的那群人是食屎的么??? # 之前上传商品的时候还踏马的错误信息和真正的错误原因不匹配 # 你家代码十年不维护啊?? order_no_list = [] cursor,conn = common_unit.database_connection() for i in response: result = get_attributes(i,cursor,conn) #添加公司和店铺的id result['store_id'] = execute_command['store_id'] result['company_id'] = execute_command['company_id'] status += write_into_database(result,cursor,conn) order_no_list.append(result['order_no']) conn.close() return status,order_no_list
def get_skuList(self,store_id): cursor, conn = common_unit.database_connection() cursor.execute("select sku from syn_fba_stocks where store_id='%s'" % (store_id)) sellerSku_list = [] for sku in cursor.fetchall(): sellerSku_list.append(sku[0]) return sellerSku_list
def insert_test_inboundlist(inbound_list): cursor, conn = common_unit.database_connection() cursor.execute('INSERT INTO test_inboundlist(shipment_id,shipment_name,time) values(%s,%s,%s)',inbound_list) conn.commit() cursor.close() conn.close() return
def insert_test_inboundlist_item(inbound_item_list): cursor, conn = common_unit.database_connection() cursor.execute('INSERT INTO test_inboundlist_item(shipment_id,seller_sku,quantity_shiped,network_sku,time) values(%s,%s,%s,%s,%s)',inbound_item_list) conn.commit() cursor.close() conn.close() return
def order_id_to_order_no(order_id): cursor, conn = common_unit.database_connection() select_sql = 'SELECT * FROM db_erp.`order` WHERE order_no = "%s"' % order_id cursor.execute(select_sql) order_index = cursor.fetchall()[0][0] conn.close() return order_index
def insert_test_inventory(inventory_list): cursor, conn = common_unit.database_connection() cursor.execute('INSERT INTO test_inventory(sku,asin,quantity,time) values(%s,%s,%s,%s)',inventory_list) conn.commit() cursor.close() conn.close() return
def insert_test_outboundlist(outbound_list): cursor, conn = common_unit.database_connection() cursor.execute('INSERT INTO test_outboundlist(seller_order_id,name,display_order_time,order_status,status_update_time,status_time,receive_date_time,display_order_id,time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)',outbound_list) conn.commit() cursor.close() conn.close() return
def update_test_inventory(new_add,sku): cursor, conn = common_unit.database_connection() cursor.execute("SELECT quantity FROM test_inventory where sku='%s'" % (sku)) quant = cursor.fetchone()[0] total = int(new_add)+int(quant) cursor.execute("update test_inventory set quantity='%s',time='%s' where sku='%s'" % (total,create_time,sku)) conn.commit() cursor.close() conn.close()
def write_into_fba_stock(sele,stock_list): cursor, conn = common_unit.database_connection() sku = stock_list[1] rs = cursor.execute("select * from syn_fba_stocks where sku='%s'" % (sku)) if rs >= 1: print('已经同步过了,需要做的是更新操作') else: cursor.execute( 'INSERT INTO syn_fba_stocks(fnsku,sku,term,total,asin,instock_quant,supply_detail,tpoint_type,store_id) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)', stock_list) conn.commit()
def get_joom_access(store_id): cursor, conn = common_unit.database_connection() search_access_id_query = 'SELECT mws_token FROM store WHERE id = "%s"' % store_id cursor.execute(search_access_id_query) token_list = cursor.fetchall() if len(token_list) == 0: access_token = "0" else: access_token = token_list[0][0] conn.close() return access_token
def refresh_access_token(): cursor,conn = common_unit.database_connection() get_refresh_token_list_query = 'SELECT seller_id,secret_key,refresh_token FROM store WHERE website = "35"' cursor.execute(get_refresh_token_list_query) wish_refresh_token_list = cursor.fetchall() conn.close() count = 0 for token_line in wish_refresh_token_list: result = refresh_execute(token_line) count += result[0] if result[1] != 0: common_unit.write_log(result[1]) return 0
def listAllfulfillmentOrders_between_time(start_date, result): cursor, conn = common_unit.database_connection() select_order_orderItem() #对新生成的订单进行创建出库单 rs = cursor.execute( "SELECT * FROM test_outboundlist where status_time > '%s'" % (start_date)) if rs != 0: result = json.loads(result) members = result['ListAllFulfillmentOrdersResponse'][ 'ListAllFulfillmentOrdersResult']['FulfillmentOrders']['member'] members.clear() for i in cursor.fetchall(): dict_m = {} # id = i[0] dict_m['SellerFulfillmentOrderId'] = i[1] dict_m['DestinationAddress'] = { "PhoneNumber": "7047951585", "City": "CONCORD", "CountryCode": "US", "PostalCode": "28025-1273", "Name": "Kathy Irminger", "StateOrProvinceCode": "NC", "Line3": 'null', "DistrictOrCounty": 'null', "Line2": 'null', "Line1": "7015 ERINBROOK DR" } dict_m['DestinationAddress']['Name'] = i[2] dict_m['DisplayableOrderDateTime'] = i[3] dict_m['FulfillmentOrderStatus'] = i[4] dict_m['StatusUpdatedDateTime'] = i[5] dict_m['ReceivedDateTime'] = i[7] dict_m['DisplayableOrderId'] = i[8] dict_m['ShippingSpeedCategory'] = 'Expedited' dict_m['FulfillmentMethod'] = 'Consumer' dict_m['FulfillmentPolicy'] = 'FillOrKill' dict_m['DisplayableOrderComment'] = '感谢您的订购!' members.append(dict_m) rr = result else: rr = listAllfulfillment(start_date, result) # nowTime = int(time.time()) # timeStruct = time.strptime(start_date, "%Y-%m-%d") # startDate_time = int(time.mktime(timeStruct)) # # print(nowTime,startDate_time) # if nowTime - startDate_time > 604800: #退7天 生成历史的出库单的话传入的时间的是 : (当前时间 - 7天) # rr = listAllfulfillment(start_date,result) # else: # rr={} return rr
def refresh_execute(token_line): try: url = 'https://merchant.wish.com/api/v2/oauth/refresh_token?client_id=%s&client_secret=%s&refresh_token=%s&grant_type=refresh_token'%token_line r = requests.post(url) result_data = json.loads(r.text)['data'] common_unit.write_log(r.text) cursor,conn = common_unit.database_connection() update_token_query = 'UPDATE store SET mws_token = "%s",refresh_token="%s" WHERE seller_id = "%s"'%(result_data["access_token"],result_data["refresh_token"],result_data["client_id"]) cursor.execute(update_token_query) conn.commit() cursor.close() return (0,0) except (Exception) as e: return (1,str(e))
def select_inboundItem_by_shipmentId(shipmentId): cursor, conn = common_unit.database_connection() cursor.execute("SELECT time FROM test_inboundlist where shipment_id='%s'" % (shipmentId)) # id = cursor.fetchone()[0] ship_time = cursor.fetchone()[0] ship_time = datetime.datetime.strftime(ship_time, "%Y-%m-%d") # print(type(ship_time)) shipment_date = conver_date(str(ship_time)) # print(shipment_date,nowdate_time) cursor.close() conn.close() if shipment_date==nowdate_time: return 0 else: return 1
def get_order(execute_command,result): # print(result) result = json.loads(result) response = result['GetOrderResponse']['GetOrderResult']['Orders']['Order'] if response == {}: return -1 attribute_line = get_attributes(response,cursor,conn) #添加公司和店铺的id attribute_line['store_id'] = execute_command['store_id'] attribute_line['company_id'] = execute_command['company_id'] #天啊……幸好后端靠谱决定直接把公司的id传给我,不然我她妈的……还要去检索一遍么? #现在实测了一下,这个借口她妈的!调用一次至少要四秒!其中我自己的运行效率问题还不到0.01秒!全她妈的是亚马逊 #一个四秒的延迟里面,和亚马逊通讯的延迟就占用了五秒!(夸张来说 _(:з」∠)_ cursor,conn = common_unit.database_connection() status = write_into_database(attribute_line,cursor,conn) conn.close() return status
def interface_amazon_ListOrders(execute_command): cursor,conn = common_unit.database_connection() if execute_command['create_time'] == '': execute_command['create_time'] == '1970-01-01' if execute_command['store_id'] != '': # result = list_order_by_store_id(execute_command) # return json.dumps(result) params = ['Action=ListOrders']+api_version+['Timestamp='+common_unit.get_time_stamp()] user_access_dict = common_unit.get_amazon_keys(execute_command['store_id']) # 获取认证参数 # 把认证参数添加进请求头 params += common_unit.make_access_param(user_access_dict,execute_command) params[-1] = 'MarketplaceId.Id.1='+params[-1].split('=')[1] if execute_command['create_time'] != '': params += ['CreatedAfter='+quote(execute_command['create_time']+'T00:00:00')] else: params += ['CreatedAfter='+quote('1970-01-01T00:00:00')] params = params + default_params # print(params) params = sorted(params) # 拼接公有请求参数,认证请求参数,和特征请求参数,并进行排序,拼接请求身,需要按首字母排序 params = '&'.join(params) # 对请求身进行分割 # print(params) sig_string = 'POST\n' + host_name + '\n' + port_point + '\n' + params # 连接签名字符串 signature = quote(str(common_unit.cal_signature(sig_string, user_access_dict['secret_key']))) # 计算字符串的加密签名 url = connect_url(params, signature) # 拼接请求字符串 # print(url) r = requests.post(url, headers=headers) # 发起请求 content = common_unit.xmltojson(r.text) # print(content) status,order_no_list = list_orders(execute_command,content) if status == 0: result = {'status_code':0,'message':'同步成功了所有订单,好哥哥你真棒!'} elif status == -1: result = {'status_code':-1,'message':'没有订单啊,你会不会查询啊?'} else: result = {'status_code':1,'message':'好像有'+str(status)+'条订单数据库里已经有了,要不要试试直接查查数据库?'}
def select_inventory_list(result): mb = 0 cursor, conn = common_unit.database_connection() result = json.loads(result) members = result['ListInventorySupplyResponse'][ 'ListInventorySupplyResult']['InventorySupplyList']['member'] rs = cursor.execute("SELECT sku,asin,quantity FROM test_inventory") if rs != 0: for inventory in cursor.fetchall(): members[mb]['SellerSKU'] = inventory[0] members[mb]['ASIN'] = inventory[1] members[mb]['TotalSupplyQuantity'] = inventory[2] mb = mb + 1 rr = result else: lr = list_inventory_supply(result) rr = lr return rr
def save_result_into_db(json_content,execute_command): product_info = json.loads(json_content) cursor,conn = common_unit.database_connection() product_attribute = {} if 'asin' in execute_command: product_attribute['sfs_id'] = get_sfs_id_by_asin(conn,cursor,execute_command['asin']) product_attribute['sku'] = get_sku_by_asin(conn,cursor,execute_command['asin']) elif 'sku' in execute_command: product_attribute['sfs_id'] = get_sfs_id_by_sku(conn,cursor,execute_command['sku']) product_attribute['sku'] = execute_command['sku'] attribute = json.dumps(product_info["GetMatchingProductResponse"]["GetMatchingProductResult"]["Product"]) attribute = attribute.replace('"','\\"') product_attribute['attribute'] = attribute product_attribute['platform'] = product_info["GetMatchingProductResponse"]["GetMatchingProductResult"]["Product"]['Identifiers']['MarketplaceASIN']['MarketplaceId'] product_attribute['marketpalce'] = product_info["GetMatchingProductResponse"]["GetMatchingProductResult"]["Product"]['Identifiers']['MarketplaceASIN']['MarketplaceId'] product_attribute['store_id'] = execute_command['store_id'] result = write_product_info_into_db(conn,cursor,product_attribute) ranking_attribute = {} ranking_attribute['listing_id'] = product_info["GetMatchingProductResponse"]["GetMatchingProductResult"]["Product"]['Identifiers']['MarketplaceASIN']['ASIN'] ranking_attribute['sku'] = product_attribute['sku'] ranking_attribute['marketplace_id'] = product_info["GetMatchingProductResponse"]["GetMatchingProductResult"]["Product"]['Identifiers']['MarketplaceASIN']['MarketplaceId'] try: ranking_attribute_list = product_info["GetMatchingProductResponse"]["GetMatchingProductResult"]["Product"]['SalesRankings'] ranking_attribute['cid_one'] = ranking_attribute_list['SalesRank']["ProductCategoryId"] ranking_attribute['rank_one'] = ranking_attribute_list['SalesRank']["Rank"] except: pass ranking_attribute['time'] = common_unit.get_time_stamp_now() result = write_product_rank_into_db(conn,cursor,ranking_attribute) conn.close() return result
def ListOrders(execute_command): cursor,conn = common_unit.database_connection() # 连接数据库,这个让我写得神烦 # 我本来想法是,在调用这个方法的时候只连接一次数据库就行了 # 可是没办法,公司的服务器实在是令人发指 # 数据库平均两分钟断一次,所以不能永久连接,只能需要的时候连接然后断开 if execute_command['create_time'] == '': execute_command['create_time'] == '1970-01-01' if execute_command['store_id'] != '': result = list_order_by_store_id(execute_command) return json.dumps(result) elif 'company_id' in execute_command: company_id_in_execute_command = common_unit.anti_sql_inject_attack(str(execute_command['company_id'])) # 清洗一下数据,把公司的id拿出来 search_query = 'SELECT id FROM store WHERE company_id = "%s"'%company_id_in_execute_command # 拼接数据条 cursor.execute(search_query) # 执行查询 store_list = cursor.fetchall() result = [] # 获取该公司的全部店铺的id order_no_list_all = [] print(len(store_list)) for i in store_list: execute_command['store_id']=i[0] return_json,order_no_list = list_order_by_store_id(execute_command) order_no_list = list(set(order_no_list)) # executable = (execute_command,order_no_list) # result = list_order_items(executable) # result.append(return_json) # 拿店铺id去查询,最后把数据插入到一个列表中 # print(order_no_list_all) conn.close() # 关闭数据库连接,防止连接数过高导致溢出,然后把列表转化为json数组 else: result = {'status_code':'-9527','message':'傻逼啊……你会不会请求啊……params都弄错了'} result = return_json return result
def write_order_item_into_db(item_attribute): cursor, conn = common_unit.database_connection() key_list = [] value_list = [] for key in item_attribute: key_list.append(key) value_list.append('"' + str(item_attribute[key]) + '"') key_database = ','.join(key_list) value_database = ','.join(value_list) sql_search_query = 'SELECT * FROM order_item WHERE order_id = "%s"' % str( item_attribute['order_id']) cursor.execute(sql_insert_query) if len(cursor.fetchall()) == 0: sql_insert_query = 'INSERT INTO order_item(%s) VALUES(%s)' % ( key_database, value_database) cursor.execute(sql_insert_query) conn.commit() conn.close() return 0 else: conn.close() return 1
def processFile(inputFile): #定义一个函数 index = 0 fin = open(inputFile, 'r') #以读的方式打开文件 # fout = open(outputFile, 'w') #以写得方式打开文件 for eachLine in fin: #读取文件的每一行 class_type = [] index = index + 1 if index == 25: break try: line = eachLine.strip() #去除每行的首位空格,并且将文件编码转换成Unicode编码 line = cc.xmltojson(str(line)) line = json.loads(line) print(line) # tag1 = line['xsd:element']['@ref'] # print(tag1) # class_type.append(tag1) tag2 = line['xsd:element']['@ref'] print(tag2) class_type.append(tag2) parent_name = 'Wireless' class_type.append(parent_name) remark = '' class_type.append(remark) print(class_type) cursor, conn = cc.database_connection() cursor.execute( 'INSERT INTO category_upload(category_name,parent_category,remark) values(%s,%s,%s)', class_type) conn.commit() # outStr = line #我没对读入的文本进行处理,只是直接将其输出到文件 # fout.write(outStr.strip().encode('utf-8') + '\n') #去除首位的空格,并转回到utf-8编码,然后输出 except: continue fin.close() #关闭文件
def update_store_status(self,store_id): cursor, conn = common_unit.database_connection() cursor.execute("update stores set status=1 where id='%s'" % (store_id)) conn.commit()
def update_test_orderlist_status(order_id): cursor, conn = common_unit.database_connection() cursor.execute("update test_orderlist set status=1 where order_id='%s'" % (order_id)) conn.commit() cursor.close() conn.close()