class FetchTransaction():
# 接口的header
#headers = Headers.HEADERS["tnf_demo"]
def FetchTransactionDetails(cellValues):
# 接口的url
url = "https://api.capillarytech.cn.com/v1.1/transaction/get?format=json&number=%s"% cellValues
r = requests.request("get", url, headers=Headers.HEADERS["tnf_demo"])
status = r.status_code
if status != 200:
print ("\x1b[31mfail: status-> {} {}\x1b[0m".format(status, url))
resp = json.loads(r.text)
resp_code = resp["response"]["transactions"]["transaction"][0]["item_status"]["code"]
resp_message = resp["response"]["transactions"]["transaction"][0]["item_status"]["message"]
if resp_code== 600 and resp_message == "Transaction retrieved successfully":
print("\x1b[32mpass: {}\x1b[0m".format(url))
else:
print("\x1b[31mfail: {} code: {}, message:{}\x1b[0m".format(url,resp_code,resp_message))
#assert resp_code== 600 and resp_message == "Transaction retrieved successfully", "failed"
return resp_code,resp_message
getdata = readexcel.read_excel()
sheet1 = getdata.sheet_by_index(0) # sheet索引从0开始
#print(sheet1.name, sheet1.nrows, sheet1.ncols)
for i in xrange(1,sheet1.nrows): #行数
cellValues = sheet1.cell_value(i,0)
print(cellValues)
resp_code,resp_message=FetchTransactionDetails(cellValues)
print(resp_code,resp_message)
writeexcel.write_excel(i,resp_code,resp_message)
class ExportMemberV2():
filename = "/pythonjulia/files/TNF Demo -- Fetch Transaction1.xls"
def ExportMemberV2Details(cellValues):
# 接口的url
preUrl = Urls.URLS["tnf_demo"]["url"]
source = Urls.URLS["tnf_demo"]["source"]
accountId = Urls.URLS["tnf_demo"]["accountId"]
url = "%sv2/customers/%s?source=%s&accountId=%s" % (preUrl, cellValues,
source, accountId)
print(url)
r = requests.request("get", url, headers=Headers.HEADERS["tnf_demo"])
resp = json.loads(r.text)
fields = resp["profiles"][0]["fields"]
#print(fields)
resp_bday = ''
resp_tnflastupdate = ''
resp_gender = ''
if "birthday" in fields:
resp_bday = fields['birthday']
if "tnflastupdate" in fields:
resp_tnflastupdate = fields['tnflastupdate']
if "gender" in fields:
resp_gender = fields['gender']
resp_mobile = '',
resp_externalId = ''
for i in resp["profiles"][0]["identifiers"]:
if i["type"] == "mobile":
resp_mobile = i["value"]
if i["type"] == "externalId":
resp_externalId = i["value"]
resplist = [
resp_mobile, resp_externalId, resp_bday, resp_tnflastupdate,
resp_gender
]
return resplist
getdata = readexcel.read_excel(filename)
sheet1 = getdata.sheet_by_index(0) # sheet索引从0开始
#print(sheet1.name, sheet1.nrows, sheet1.ncols)
for i in xrange(1, sheet1.nrows): # 行数
cellValues = sheet1.cell_value(i, 0)
#resp_mobile, resp_externalId, resp_bday = ExportMemberV2Details(cellValues)
outputlist = ExportMemberV2Details(cellValues)
start = 1
writeexcel.write_excel(filename, i, outputlist, start)
def write_excel(filename, i, outputlist, start): # start 传的值是开始列
oldWb = readexcel.read_excel(filename)
newWb = copy(oldWb)
newWs = newWb.get_sheet(0)
num = len(outputlist)
for a in range(0, num):
print("newWs.write(", i, start, a, outputlist[a],
"set_style('Arial', 220, True))")
newWs.write(i, start, outputlist[a], set_style('Arial', 220, True))
for n in range(start + 1, num + start):
print("newWs.write(", i, n, a + n - start,
outputlist[a + n - start], "set_style('Arial', 220, True))")
newWs.write(i, n, outputlist[a + n - start],
set_style('Arial', 220, True))
break
print("write new values ok")
newWb.save(filename)
print("save with same name ok")
#!/usr/bin/python
# -*- coding: UTF-8 -*-
from Utilities import readexcel
from Utilities import writeexcel
from xlrd.timemachine import xrange
from Fetch_Member_API_V2.ExportMemberV2_1 import ExportMemberV2_1
filename = "/pythonjulia/files/TNF Demo -- GetCustomerDetailsById.xls"
getdata = readexcel.read_excel(filename)
sheet1 = getdata.sheet_by_index(0) # sheet索引从0开始
for i in xrange(1, sheet1.nrows): # 行数
cellValues = sheet1.cell_value(i, 0)
outputlist = ExportMemberV2_1.ExportMemberV2_1Details(cellValues)
start = 1
writeexcel.write_excel(filename, i, outputlist, start)
def RegisterWeChat():
# 接口的url
headers = Headers.HEADERS["tnf_demo_wechat"]
preUrl = Urls.URLS["tnf_demo"]["url"]
source = Urls.URLS["tnf_demo"]["source2"]
getdata = readexcel.read_excel(filename)
sheet1 = getdata.sheet_by_index(0) # sheet索引从0开始
print(sheet1)
for i in xrange(1, sheet1.nrows): # 行数
accountId = sheet1.cell_value(i, 9)
url = "%sv2/customers?source=%s&accountId=%s" % (preUrl, source,
accountId)
print(url)
email = sheet1.cell_value(i, 0)
mobile = sheet1.cell_value(i, 1)
createDateS = sheet1.cell_value(i, 2)
if createDateS != '':
createDate = datetimeformat.string_toDatetime(createDateS)
else:
createDate = ''
externalId = sheet1.cell_value(i, 3)
firstName = sheet1.cell_value(i, 4)
birthdayS = sheet1.cell_value(i, 5)
if birthdayS != '':
birthday = datetimeformat.string_toDate(birthdayS)
else:
birthday = ''
gender = sheet1.cell_value(i, 6)
openid = sheet1.cell_value(i, 7)
if birthday != '' and gender != '':
body = "{\"profiles\":[{\"firstName\":\"%s\",\"fields\":{\"gender\":\"%s\",\"birthday\":\"%s\"},\"identifiers\":[{\"type\":\"wechat\",\"value\":\"%s\"},{\"type\":\"mobile\",\"value\":\"%s\"},{\"type\":\"externalId\",\"value\":\"%s\"},{\"type\":\"email\",\"value\":\"%s\"}],\"commChannels\":[{\"type\":\"wechat\",\"value\":\"%s\",\"primary\":true,\"verified\":true},{\"type\":\"mobile\",\"value\":\"%s\",\"primary\":true,\"verified\":true}]}],\"loyaltyInfo\":{\"loyaltyType\":\"loyalty\",\"attributionV2\": {\"createDate\": \"%s\"}}}" \
% (firstName, gender, birthday, openid, mobile, externalId, email, openid, mobile, createDate)
elif birthday != '' and gender == '':
body = "{\"profiles\":[{\"firstName\":\"%s\",\"fields\":{\"birthday\":\"%s\"},\"identifiers\":[{\"type\":\"wechat\",\"value\":\"%s\"},{\"type\":\"mobile\",\"value\":\"%s\"},{\"type\":\"externalId\",\"value\":\"%s\"},{\"type\":\"email\",\"value\":\"%s\"}],\"commChannels\":[{\"type\":\"wechat\",\"value\":\"%s\",\"primary\":true,\"verified\":true},{\"type\":\"mobile\",\"value\":\"%s\",\"primary\":true,\"verified\":true}]}],\"loyaltyInfo\":{\"loyaltyType\":\"loyalty\",\"attributionV2\": {\"createDate\": \"%s\"}}}" \
% (firstName, birthday, openid, mobile, externalId, email, openid, mobile, createDate)
elif birthday == '' and gender != '':
body = "{\"profiles\":[{\"firstName\":\"%s\",\"fields\":{\"gender\":\"%s\"},\"identifiers\":[{\"type\":\"wechat\",\"value\":\"%s\"},{\"type\":\"mobile\",\"value\":\"%s\"},{\"type\":\"externalId\",\"value\":\"%s\"},{\"type\":\"email\",\"value\":\"%s\"}],\"commChannels\":[{\"type\":\"wechat\",\"value\":\"%s\",\"primary\":true,\"verified\":true},{\"type\":\"mobile\",\"value\":\"%s\",\"primary\":true,\"verified\":true}]}],\"loyaltyInfo\":{\"loyaltyType\":\"loyalty\",\"attributionV2\": {\"createDate\": \"%s\"}}}" \
% (firstName, gender, openid, mobile, externalId, email, openid, mobile, createDate)
elif birthday == '' and gender == '':
body = "{\"profiles\":[{\"firstName\":\"%s\",\"identifiers\":[{\"type\":\"wechat\",\"value\":\"%s\"},{\"type\":\"mobile\",\"value\":\"%s\"},{\"type\":\"externalId\",\"value\":\"%s\"},{\"type\":\"email\",\"value\":\"%s\"}],\"commChannels\":[{\"type\":\"wechat\",\"value\":\"%s\",\"primary\":true,\"verified\":true},{\"type\":\"mobile\",\"value\":\"%s\",\"primary\":true,\"verified\":true}]}],\"loyaltyInfo\":{\"loyaltyType\":\"loyalty\",\"attributionV2\": {\"createDate\": \"%s\"}}}" \
% (firstName, openid, mobile, externalId, email, openid, mobile, createDate)
print(body)
r = requests.request("post",
url,
data=body.encode('utf-8'),
headers=headers)
print(r)
resp = json.loads(r.text)
print(resp)
if "createdId" in resp and "code" not in resp:
userid = resp["createdId"]
outputlist = [userid]
elif "createdId" in resp and "code" in resp:
userid = resp["createdId"]
code = resp["warnings"][0]["code"]
status = resp["warnings"][0]["status"]
message = resp["warnings"][0]["message"]
outputlist = [userid, code, status, message]
else:
code = resp["errors"][0]["code"]
status = resp["errors"][0]["status"]
message = resp["errors"][0]["message"]
outputlist = [code, status, message]
print(outputlist)
print(len(outputlist))
start = 11
writeexcel.write_excel(filename, i, outputlist, start)