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