class Commontool():
def __init__(self, envi):
self.mysql = MySql(envi)
self.envi = envi
self.dirname = os.path.join(os.getcwd(), "log")
self.nowtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
def getCustNo(self, cust_no=''):
sqls = "select p_org_code from zs_org WHERE org_code = '%s'" % cust_no
top_cust_no = self.mysql.searchs(sqls=sqls)[0][0]
if top_cust_no == '0':
top_cust_no = cust_no
return top_cust_no
def getTableNum(self, orderId=None, orderItemNo=''):
#通过订单获取分表
if orderId != None:
top_org_code = self.mysql.searchs(
"SELECT top_org_id from srcorder_info_index where gc_order_no='{}'"
.format(orderId))
else:
top_org_code = self.mysql.searchs(
"SELECT top_org_id from srcorder_info_index where gc_item_no='{}'"
.format(orderItemNo))
top_org_code = top_org_code[0][0]
table = self.mysql.searchs(
"select table_num from gc_order_split_strategy_config where user_code='{}'"
.format(top_org_code))
if table:
table_num = list(table)[0][0]
else:
table_num = ''
return table_num
#!/usr/bin/env python
# encoding: utf-8
'''
@author: guohailong
@project: ApiShopping
@file: T001_BillingInfo.py
@time: 2019-05-20 08:46:05
@desc: 手动写账单信息
'''
from Tools.mysql import MySql
import time
import datetime
gcms_pre = MySql(envi='pre')
gcbi_pre = MySql(envi='pre', db='GCBI')
times = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
id = int(time.time() * 100000)
def getCustNo(cust_no=''):
sqls = "select p_org_code from zs_org WHERE org_code = '%s'" % cust_no
top_cust_no = gcms_pre.searchs(sqls=sqls)[0][0]
if top_cust_no == '0':
top_cust_no = cust_no
return top_cust_no
def orderItemAccount(orderItemCode=None):
"""
说明：手动写账单信息
def __init__(self):
self.mysql = MySql(envi='pre')
self.times = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
self.gcbi_pre = MySql(envi='pre', db='GCBI')
self.common = Commontool(envi='pre')
class ReturnPart():
def __init__(self):
self.mysql = MySql(envi='pre')
self.times = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
self.gcbi_pre = MySql(envi='pre', db='GCBI')
self.common = Commontool(envi='pre')
def fetchData(self, orderItemId):
tn = self.common.getTableNum(orderItemNo=orderItemId)
itemResult = self.mysql.searchs(
"select gc_order_no,oms_item_no,commdty_code,quantiy,unit_price,freight,order_header_no,cust_no,order_item_no,return_goods_num from srcorder_item%s where gc_item_no = '%s'"
% (tn, orderItemId))
gc_order_no = orderItemId[:-2]
headerResult = self.mysql.searchs(
"select oms_order_no from srcorder_header%s where gc_order_no = '%s'"
% (tn, gc_order_no))
oms_order_no = str(headerResult[0])
for i in itemResult:
#gc_order_no = str(i[0])
oms_item_no = str(i[1])
commdty_code = str(i[2])
quantiy = str(i[3])
unit_price = str(i[4])
freight = str(i[5])
order_header_no = str(i[6])
cust_no = str(i[7])
order_item_no = str(i[8])
return_goods_num = str(i[9])
top_org_code = self.common.getCustNo(cust_no=cust_no)
rtn_oms_item_no = str(int(time.time())) + '00001'
# ('100001816649', '00102588006001', '000000000121347740', '5', '1000.000000', '0.000000', '1640000000000350510', '7018048617', '201809171245243362', '7018048617', '157337332800001', '1')
return gc_order_no, oms_item_no, commdty_code, quantiy, unit_price, freight, order_header_no, cust_no, order_item_no, top_org_code, rtn_oms_item_no, return_goods_num
# 检查退货数量
def checkNum(self, orderItemId=''):
gc_order_no = orderItemId[:-2]
sqls = "select max(portion_return_num) from srcorder_item_portion_return where gc_item_no ='%s'" % orderItemId
returnedNum = self.mysql.searchs(sqls=sqls)[0][0]
if returnedNum is None:
returnedNum = 0
quantity = self.fetchData(orderItemId=orderItemId)[3]
if returnedNum >= int(quantity):
return True # 已退完
else:
return int(returnedNum) # 未退完，则返回已退数量
# 查询退货次数
def checkCount(self, orderItemId=''):
sqls = "select max(portion_return_count) from srcorder_item_portion_return where gc_item_no = '%s'" % orderItemId
returnCount = self.mysql.searchs(sqls=sqls)[0][0]
if returnCount is None:
return int(0)
else:
return int(returnCount)
# 退货商品分摊运费
def servFeePerNum(self, orderItemId='', num=0):
total_freight = self.fetchData(orderItemId=orderItemId)[5]
quantity = self.fetchData(orderItemId=orderItemId)[3]
perNum = float(total_freight) / int(quantity)
return round(perNum * int(num), 2)
def insertData(self, orderItemId='', returnNum=''):
gc_order_no = orderItemId[:-2]
quantity = self.fetchData(orderItemId=orderItemId)[3] #订单行商品总数量
returnedNum = self.checkNum(orderItemId=orderItemId) #已退数量
cc = self.checkCount(orderItemId=orderItemId) #已退次数
oms_item_no = self.fetchData(orderItemId=orderItemId)[1]
unit_price = self.fetchData(orderItemId=orderItemId)[4]
cust_no = self.fetchData(orderItemId=orderItemId)[7]
table_num = self.common.getTableNum(orderItemNo=orderItemId)
top_cust_no = self.fetchData(orderItemId=orderItemId)[9]
table_num_msg = int(top_cust_no) % 64
unit_price = "%.2f" % (float(unit_price)) #商品单价
total_freight = self.fetchData(orderItemId=orderItemId)[5] #订单行分摊运费
portion_return_count = cc + 1 # 退货次数 = 已退货次数 + 1
rtn_oms_item_no = str(int(time.time())) + '00001'
if isinstance(returnedNum, bool) or cc >= 3:
print("此订单已退完，无须再退")
else:
if (int(returnedNum) + int(returnNum)
) >= int(quantity) or portion_return_count == 3: #全部退
portion_return_price = int(quantity) * float(
unit_price) + float(total_freight) # 总退货金额
returnNum = int(quantity) - int(returnedNum) #本次退货数量
print("本次退货数量returnNum为：" + str(returnNum))
reNum = quantity #总退货数量
servFee = self.servFeePerNum(orderItemId=orderItemId,
num=int(returnedNum)) # 退货商品分摊运费
current_rtn_freight = float(total_freight) - float(
servFee) # 本次退运费金额
current_return_price = returnNum * float(
unit_price) + current_rtn_freight # 本次退货金额（含运费）
status = '08'
else: #部分退
servFee = self.servFeePerNum(orderItemId=orderItemId,
num=(int(returnedNum) +
int(returnNum))) # 退货商品分摊运费
portion_return_price = (int(returnedNum) + int(returnNum)
) * float(unit_price) + float(
servFee) # 总退货金额
current_rtn_freight = int(returnNum) * float(
servFee) # 本次退运费金额
reNum = int(returnedNum) + int(returnNum) # 总退货数量
cuservFee = self.servFeePerNum(orderItemId=orderItemId,
num=int(returnNum))
current_return_price = int(returnNum) * float(
unit_price) + float(cuservFee) # 本次退货金额（含运费）
status = '06'
up_sql = "update srcorder_item set status = '%s',portion_return_status = '1',return_goods_num = '%s' WHERE gc_item_no = '%s'" % (
status, reNum, orderItemId)
self.mysql.updates(sqls=up_sql)
if table_num:
# table_num = list(table_num)[0][0]
up_sql4 = "update srcorder_item%s set status = '%s',portion_return_status = '1',return_goods_num = '%s' WHERE gc_item_no = '%s'" % (
table_num, status, reNum, orderItemId)
self.mysql.updates(sqls=up_sql4)
in_sql = "insert into srcorder_item_portion_return (`gc_order_no`,`gc_item_no`,`oms_item_no`," \
"`portion_return_num`,`portion_return_count`,`portion_return_price`,`oms_opreate_time`," \
"`update_time`,`create_time`) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s')" \
% (
gc_order_no, orderItemId, oms_item_no, reNum, portion_return_count, round(portion_return_price,2),
self.times, self.times, self.times)
up_sql2 = "update srcorder_item_portion_return set portion_return_num = '%s' , portion_return_count = '%s' " \
", portion_return_price = '%s' , update_time = '%s' WHERE gc_item_no = '%s'" \
% (reNum, portion_return_count, round(portion_return_price,2), self.times, orderItemId)
if cc == 0: # 如果没有发生过部分退，就insert
self.mysql.inserts(sqls=in_sql)
else: # 如果发生过部分退，则update
self.mysql.updates(sqls=up_sql2)
# 向srcorder_item_portion_return_detail表中插入数据，以供售后进度查询使用
in_sql2 = "insert into srcorder_item_portion_return_detail (`gc_order_no`,`gc_item_no`,`oms_item_no`," \
"`order_status`,`current_return_num`,`total_return_num`,`rtn_oms_item_no`,`rtn_price`,`rtn_freight`," \
"`oms_opreate_time`,`update_time`,`create_time`,`invoice_status`,`invoice_apply_date`,`cust_no`,`top_cust_no`)" \
" VALUES ('%s','%s','%s','06','%s','%s','%s','%s','%s','%s','%s','%s','0',NULL ,'%s','%s')" % \
(gc_order_no, orderItemId, oms_item_no,int(returnNum), reNum, rtn_oms_item_no, round(current_return_price,2),
round(current_rtn_freight,2),
self.times, self.times, self.times, cust_no, top_cust_no)
# 2020/4/7日新增 退货时产生12-4消息
message_status = '4'
commdty_code = self.fetchData(orderItemId=orderItemId)[2]
commdty_code = str(int(commdty_code))
message = {
"cmmdtyCode": commdty_code,
"orderNo": gc_order_no,
"status": message_status,
"orderItemNo": orderItemId,
"num": returnNum
}
message = json.dumps(message, separators=(',', ':'))
in_sql3 = "insert into gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('12','%s','%s','%s','%s','%s','%s','%s','1','X','%s','system')" "" % \
(table_num_msg, str(message), top_cust_no, self.times, self.times, gc_order_no,orderItemId,commdty_code, message_status)
self.mysql.inserts(in_sql2)
self.gcbi_pre.inserts(in_sql3)
def __init__(self, envi):
self.mysql = MySql(envi)
self.envi = envi
self.dirname = os.path.join(os.getcwd(), "log")
self.nowtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
self.common = Commontool(envi=envi)
class Common():
def __init__(self, envi):
self.mysql = MySql(envi)
self.envi = envi
self.dirname = os.path.join(os.getcwd(), "log")
self.nowtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
self.common = Commontool(envi=envi)
def getToken(self, appKey, token):
"""判断token是否过期"""
result = self.mysql.searchs(
"select token,expire_date from api_token where api_account='{}'".
format(appKey))
nowtime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
if result:
if str(result[0][1]) > nowtime:
token = result[0][0]
else:
token = token()
else:
token = token()
return token
def getToken2(self, appKey):
"""不判断token是否过期"""
result = self.mysql.searchs(
"select token,expire_date from api_token where api_account='{}'".
format(appKey))
if result:
token = result[0][0]
return token
def scHost(self):
"""天线测试环境"""
if str(self.envi) == "pre":
return "http://txapipre.cnsuning.com"
elif str(self.envi) == "sit":
return "http://txapisit.cnsuning.com"
elif str(self.envi) == "xgpre":
return "http://txapixgpre.cnsuning.com"
elif str(self.envi) == "prd":
return "http://txapi.suning.com"
def scHost_bz(self):
"""标准环境"""
if str(self.envi) == "pre":
return "http://openpre.cnsuning.com/ospos/apipage/testApiMethodInvoke.do"
elif str(self.envi) == "sit":
return "http://opensit.cnsuning.com/ospos/apipage/testApiMethodInvoke.do"
elif str(self.envi) == "xgpre":
return "http://openxgpre.cnsuning.com/ospos/apipage/testApiMethodInvoke.do"
elif str(self.envi) == "online":
return "https://open.suning.com/ospos/apipage/testApiMethodInvoke.do"
def post(self,
url=None,
data=None,
headers={},
FuncName=None,
isJson='N',
isLog='Y',
isReturnJson='N',
isUrlEncode='N',
isText='N'):
"初始化HTTP请求的Header和Data"
#isUrlEncode='N'时，请求data会进行url编码
if isText == 'N':
if isLog == "Y":
logger = str(FuncName) + "--" + "入参：" + str(
json.dumps(data, ensure_ascii=False,
separators=(',', ':')))
self.loger(info=logger)
if isJson == 'N':
if headers == {}:
headers = {
'Content-Type': 'application/x-www-form-urlencoded'
}
if isUrlEncode == 'N':
data = parse.urlencode(data).encode("utf-8")
else:
data = parse.urlencode(data)
data = (parse.unquote(data)).encode('utf-8')
else:
if headers == {}:
headers = {'Content-Type': 'application/json'}
data = json.dumps(data)
else:
if isLog == "Y":
logger = str(FuncName) + "--" + "入参：" + str(data)
self.loger(info=logger)
if headers == {}:
headers = {'Content-Type': 'application/text/xml'}
try:
req = requests.post(url=url, headers=headers, data=data)
if isLog == "Y":
logger = str(FuncName) + "--" + "出参：" + str(req.text)
self.loger(info=logger)
except Exception as e:
if isLog == "Y":
logger = str(FuncName) + "--" + str(e)
self.loger(error=logger)
raise e
##增加判断是否返回json 2019.05.17
if isReturnJson == 'N':
try:
redata = json.loads(req.text)
except:
redata = req.text
else:
redata = req.text
return redata
def loger(self, info="", error=""):
"""打印日志"""
nowtimes = time.strftime("%Y-%m-%d", time.localtime())
logger = logging.getLogger(__name__)
if os.path.exists(self.dirname):
pass
else:
os.makedirs(self.dirname)
handler = logging.FileHandler(
os.path.join(self.dirname,
str(nowtimes) + '.txt'))
# handler.setLevel(logging.INFO)
formatter = logging.Formatter(
'%(asctime)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)
if error == "":
logger.setLevel(level=logging.INFO)
logger.info(info)
else:
logger.setLevel(level=logging.ERROR)
logger.error(error)
logger.removeHandler(handler)
def getInvoicePice(self, itemNo, isRe='N'):
table = self.common.getTableNum(orderItemNo=itemNo)
result = self.mysql.searchs(
"SELECT unit_price,quantiy FROM srcorder_item{} WHERE gc_item_no in ({})"
.format(table, itemNo))
amount = 0
for k in result:
price = k[0]
num = k[1]
val = float(price) * int(num)
amount = float('%.2f' % Decimal(amount)) + float(
'%.2f' % Decimal(val))
if isRe == 'Y':
result = self.mysql.searchs(
"select portion_return_price from srcorder_item_portion_return where gc_item_no in ({})"
.format(itemNo))
for i in result:
return_price = i[0]
amount = float('%.2f' % Decimal(amount)) - float(
'%.2f' % Decimal(return_price))
return amount
def getSign(self,
appKey=None,
appSecret=None,
appMethod=None,
reqTime=None,
version="v1.2",
data=None):
"""
接口说明：生成标准3.0接口验签
:param appKey: appkey
:param appSecret: secret
:param appMethod: 接口调用方法
:param reqTime: 调用接口时间
:param version: 开放平台接口版本
:param data: 接口请求报文
:return: 返回生成的验签
"""
md5 = Md5()
dataBase64 = str(base64.b64encode(data.encode('utf-8')),
encoding="utf-8")
sign = appSecret + appMethod + reqTime + appKey + version + dataBase64
md5_text = md5.getMd5_32(text=sign)
return md5_text
def getSecret(self, appKey):
"""获取定制用户appSecret"""
result = self.mysql.searchs(
"select api_secret from gc_secret where api_account='{}'".format(
appKey))
return result[0][0]
def updatOrderStatus(self, orderId='', orderItemNo='', types=''):
"""更改订单状态并写标准消息"""
#types: out/access/reject/return 对应状态出库/妥投/拒收/退货
result = PushMessage().outing(orderId=orderId,
orderItemNo=orderItemNo,
types=types)
return result
def get_desktop(self):
key = winreg.OpenKey(
winreg.HKEY_CURRENT_USER,
'Software\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders'
)
return winreg.QueryValueEx(key, "Desktop")[0]
def loads_jsonp(self, _jsonp):
"""
解析jsonp数据格式为json
:return:
"""
try:
return json.loads(re.match(".*?({.*}).*", _jsonp, re.S).group(1))
except:
print(_jsonp)
def __init__(self):
self.mysql = MySql(envi='pre')
pass
class OrderRecon():
def __init__(self):
self.mysql = MySql(envi='pre')
pass
def insertData(self,orderItemId='',create_time=''):
sql = "update srcorder_item set status='06',create_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s'),update_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s') where gc_item_no='{}'".format(
create_time, create_time, orderItemId)
self.mysql.updates(sql)
sql1 = "update srcorder_header set create_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s'),update_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s') where gc_order_no='{}'".format(
create_time, create_time, orderItemId[0:-2])
self.mysql.updates(sql1)
id = str(time.strftime('%Y%m%d%H%M%S', time.localtime())) + str(int(time.time()*1000000))
sql3="select order_no,oms_item_no,create_time,commdty_code,supplier_code,unit_price,quantiy,freight,cust_no from srcorder_item where gc_item_no = '%s'" % orderItemId
itemResult = self.mysql.searchs(sql3)
gc_order_no = itemResult[0][0] #政企订单号
oms_item_no = itemResult[0][1] #OMS订单行号
oms_order_no = oms_item_no[0:-2] # OMS订单号
# create_time = itemResult[0][2] #订单创建时间
commdty_code = itemResult[0][3] #商品编码
supplier_code = itemResult[0][4] #供应商编号
unit_price = itemResult[0][5] #单价
quantiy = itemResult[0][6] #数量
total_freight = itemResult[0][7] #运费
cust_no = itemResult[0][8] #用户
cart_two_no = 'mix15736981341648125'
sqls = "select org_name,org_account,p_org_code from zs_org where org_code='%s'" % cust_no
org_result = self.mysql.searchs(sqls=sqls)
org_name = org_result[0][0]
org_account = org_result[0][1]
p_org_code = org_result[0][2]
if p_org_code == '0':
p_org_code = cust_no
p_org_name = org_name
p_org_account = org_account
else:
sqls = "select org_name,org_account from zs_org where org_code='%s'" % p_org_code
p_org_result = self.mysql.searchs(sqls=sqls)
p_org_name = p_org_result[0][0]
p_org_account = p_org_result[0][1]
table_num = int(p_org_code)%64
sqls = "select table_num from gc_order_split_strategy_config where user_code='%s'" % p_org_code
tableNum = self.mysql.searchs(sqls=sqls)
if tableNum:
sql = "update srcorder_item{} set status='06',create_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s'),update_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s') where gc_item_no='{}'".format(
tableNum[0][0],create_time, create_time, orderItemId)
self.mysql.updates(sql)
sql1 = "update srcorder_header{} set create_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s'),update_time=DATE_FORMAT('{}','%Y-%m-%d %H:%i:%s') where gc_order_no='{}'".format(
tableNum[0][0],create_time, create_time, orderItemId[0:-2])
self.mysql.updates(sql1)
sql = "INSERT INTO `gcmspre1`.`real_time_order_info%s`(`id`, `oms_order_no`, `gc_order_no`, `cart_two_no`, `gc_item_no`,\
`oms_item_no`, `rtn_oms_item_no`, `status`, `is_refund_order`, `is_return`, `create_time`, `update_time`, `pay_time`,\
`commdty_code`, `supplier_code`, `brand_name`, `first_category`, `second_category`, `third_category`, `commdty_name`,\
`unit_price`, `quantiy`, `total_freight`, `total_amt`, `order_source`, `zs_order_source`, `pay_way`, `level`,\
`balance_unit_account`, `balance_unit_name`, `balance_unit_code`, `purchase_unit_account`, `purchase_unit_name`, \
`purchase_unit_code`, `linker_name`, `linker_mobile`, `prov_code`, `city_code`, `district_code`, `addr_detail`, \
`invoice_type`, `invoice_title`, `invoice_status`, `first_puser_code`, `second_puser_code`, `third_puser_code`,\
`online_pay_amount`, `company_pay_amount`, `storage_time`, `order_storage`, `package_number`,\
`portion_return_count`, `po_no`, `promot_tax`, `external_order_no`, `invoice_remark`, `order_status`, \
`operator_time`, `order_remark`, `user_nick_name`, `activity_main_id`, `activity_main_name`, `cost_center_user`, \
`finish_time`) VALUES ('%s','%s','%s','%s','%s','%s', NULL, '4', 0, 2,'%s','%s','%s','%s','%s',NULL, NULL, NULL, NULL, NULL,%s,%s,%s,\
15.000000, 'API', NULL, '08', 1,'%s','%s','%s','%s','%s','%s','品如', '15811109281', '江苏省', '南京市','玄武区', \
'北清路68号用友软件园', 5, '品如', '2', '0', '0', '0', 0.000000,0.000000, NULL, '0', NULL, '0', NULL, 0.17, NULL, NULL,\
'06','%s', NULL, '%s',NULL, NULL, NULL, NULL)"%(table_num,id,oms_order_no,gc_order_no,cart_two_no,orderItemId,\
oms_item_no,create_time,create_time,create_time,commdty_code,supplier_code,unit_price,quantiy,total_freight,\
p_org_account,p_org_name,p_org_code,org_account,org_name,cust_no,create_time,p_org_account)
cc=self.mysql.inserts(sql)
class PushMessage(object):
"""
往api_push_message表插入消息
1.outing写入标准type 12 物流消息
2.product写入标准type 10 商品消息
3.Bill写入标准type 17 账单消息
4.changePrice写入定制价格变动type 2 价格消息
"""
def __init__(self):
self.gcms_pre = MySql(envi='pre')
self.times = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
self.gcbi_pre = MySql(envi='pre', db='GCBI')
self.common = Commontool(envi='pre')
def outing(self,orderId=None,orderItemNo=None,types="out",ret_num=''):
"""
手动写入出库和妥投的消息
out/access/reject/return 对应状态出库/妥投/拒收/退货
"""
if types=="out":
status = '1'
orderStatus = "05"
elif types=="access":
status = '2'
orderStatus = "06"
elif types=="reject":
status = '3'
orderStatus = "08"
elif types=="return":
status = '4'
orderStatus = "08"
res_code = 'ture'
#获取分表号
table = self.common.getTableNum(orderId=orderId, orderItemNo=orderItemNo)
# print("订单分表是：" + "srcorder_item" + str(table))
try:
if orderId:
result = self.gcms_pre.searchs("select commdty_code,gc_item_no,gc_order_no,quantiy,cust_no from srcorder_item%s where gc_order_no='%s'" % (table,orderId))
if types=="access":
self.gcms_pre.updates("update srcorder_item%s set DELIVERY_OVER_TIME='%s' WHERE gc_order_no='%s'" % (table,self.times,orderId))
else:
result = self.gcms_pre.searchs("select commdty_code,gc_item_no,gc_order_no,quantiy,cust_no from srcorder_item%s where gc_item_no='%s'" % (table,orderItemNo))
if types=="access":
self.gcms_pre.updates("update srcorder_item%s set DELIVERY_OVER_TIME='%s' WHERE gc_item_no='%s'" % (table,self.times,orderItemNo))
for i in result:
commdty_code = str(i[0]) #商品编码
gc_item_no = str(i[1]) #政企订单行号
gc_order_no = str(i[2]) #政企订单号
sku_num = str(i[3]) #商品数量
cust_no = str(i[4]) #会员编码
self.gcms_pre.updates(sqls="update srcorder_item{} set status='{}' where gc_item_no='{}'".format(table,orderStatus,gc_item_no))
if types=="return":
if ret_num=='':
return_num = sku_num
else:
return_num = ret_num
commdty_code = commdty_code.lstrip("0")
message = {"cmmdtyCode": commdty_code, "orderNo": gc_order_no, "status": status,
"orderItemNo": gc_item_no,"num":return_num}
message = json.dumps(message, separators=(',', ':'))
else:
message = {"cmmdtyCode":commdty_code,"orderNo":gc_order_no,"status":status,"orderItemNo":gc_item_no}
message = json.dumps(message,separators=(',', ':'))
# sqls = """INSERT INTO api_push_message (type,message,app_key,\
# create_time,update_time,order_no,order_item_no,\
# cmmdty_code,status,message_status) VALUES %s""" %((('12',str(message),top_org_code,self.times, \
# self.times,gc_order_no,gc_item_no,commdty_code,'1',status)),)
# self.gcms_pre.inserts(sqls=sqls)
# 写分表
top_org_code = self.common.getCustNo(cust_no=cust_no)
num = int(top_org_code) % 64
# print("消息分表是：" + "gcapi_push_message" + str(num))
sqls = """INSERT INTO gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('12','%s','%s','%s','%s','%s','%s','%s','1','X','%s','system')""" % \
(num, str(message), top_org_code, self.times, self.times, gc_order_no,gc_item_no,commdty_code, status)
self.gcbi_pre.inserts(sqls=sqls)
self.gcms_pre.updates(sqls="update invoice_merge_reissue_order_info{} set status='{}' where gc_item_no='{}'".format(num,orderStatus,gc_item_no))
# if not t:
# result = self.gcms_pre.searchs(
# "select message,`status` from api_push_message where order_item_no='{}' order by update_time desc limit 1".format(
# gc_item_no))
# result = result[0]
# print('消息：%s 状态：%s' % (result[0], result[1]))
# else:
# print(t)
if types=="reject":
status = '4'
message = {"cmmdtyCode": commdty_code, "orderNo": gc_order_no, "status": status,
"orderItemNo": gc_item_no,"num":sku_num}
message = json.dumps(message, separators=(',', ':'))
#写主表
# sqls = """INSERT INTO api_push_message (type,message,app_key,\
# create_time,update_time,order_no,order_item_no,\
# cmmdty_code,status,message_status) VALUES %s""" % (
# (('12', str(message), top_org_code, self.times, \
# self.times, gc_order_no, gc_item_no, commdty_code, '1', status)),)
# self.gcms_pre.inserts(sqls=sqls)
#写分表
sqls = """INSERT INTO gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('12','%s','%s','%s','%s','%s','%s','%s','1','X','%s','system')""" % \
(num, str(message), top_org_code, self.times, self.times, gc_order_no,gc_item_no,commdty_code, status)
self.gcbi_pre.inserts(sqls=sqls)
status = '3' #处理多行时，拒收状态重置
# if not t1:
# result = self.gcms_pre.searchs(
# "select message,`status` from api_push_message where order_item_no='{}' and message_status='{}' order by update_time desc limit 1".format(
# gc_item_no,status))
# result = result[0]
# print('消息：%s 状态：%s' % (result[0], result[1]))
# else:
# print(t1)
except:
res_code = 'false'
return res_code
def product(self,skuId=None,custNo=None,state=None):
'''
手工写商品消息
# 上架1 下架2 添加0 删除4 修改3 主图6
'''
num = self.gcms_pre.searchs("select table_num from gc_product_pool_split_strategy_config where user_code='{}'".format(custNo))[0][0]
page_num = self.gcms_pre.searchs("select my_category_code from zs_prd_pool_single{} where org_id='{}' and partnumber like '%{}'".format(num,custNo,skuId))
if state in '3,6':
message = '{"cmmdtyCode":"%s","time":"%s","status":"%s","page_num":"%s"}' % (skuId, self.times, state, str(page_num[0][0]))
else:
message = '{"cmmdtyCode":"%s","status":"%s","page_num":"%s"}' % (skuId, state, str(page_num[0][0]))
#写主表
# sqls = """INSERT INTO api_push_message (type,message,app_key,\
# create_time,update_time,order_no,order_item_no,\
# cmmdty_code,status,message_status) VALUES %s""" % ((('10', str(message), custNo, self.times, \
# self.times, 'X', 'X', '000000000'+skuId, '1',state)),)
# self.gcms_pre.inserts(sqls=sqls)
# print(sqls)
#写分表
num = int(custNo)%64
sqls = """INSERT INTO gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('10','%s','%s','%s','%s','X','X','%s','1','X','%s','system')""" % \
(num, str(message), custNo, self.times,self.times, '000000000' + skuId, state)
self.gcbi_pre.inserts(sqls=sqls)
print(sqls)
def Bill(self,account=None,custNo=None):
"""
手工写账单消息
:param account:会员账号
:param custNo: 母账号会员编码
"""
num = int(custNo) % 64
message = '{"time":"%s","status":"1","pin":"%s","billid":"201905010030007378"}' % (self.times,account)
#写主表
# sqls = """INSERT INTO api_push_message (type,message,app_key,\
# create_time,update_time,order_no,order_item_no,\
# cmmdty_code,status) values %s""" %(('17',str(message),custNo,self.times,self.times,'X','X','X','1'),)
# self.gcms_pre.inserts(sqls=sqls)
# print(sqls)
#写分表
sqls = """INSERT INTO gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('17','%s','%s','%s','%s','X','X','X','1','X','1','system')""" % \
(num, str(message), custNo, self.times,self.times)
self.gcbi_pre.inserts(sqls=sqls)
print(sqls)
def changePrice(self,skus="",custNo="",city=""):
"""
手工写价格变动消息
:param skus:商品编码
:param custNo:母账号会员编码
:param city:价格变动城市
:return:
"""
num = int(custNo) % 64
sku = []
for i in skus.split(','):
message = '{"cmmdtyCode":"%s","time":"%s","cityId":"%s"}' % (i, self.times, city)
# sqls = """INSERT INTO api_push_message(type,message,app_key,create_time,update_time,\
# order_no,order_item_no,cmmdty_code,status,message_status) VALUES %s""" % (('2',str(message),\
# custNo,self.times,self.times,'X','X',i,'1','0'),)
# self.gcms_pre.inserts(sqls=sqls)
# print(sqls)
# 写分表
sqls = """INSERT INTO gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('2','%s','%s','%s','%s','X','X','X','1','X','1','system')""" % \
(num, str(message), custNo, self.times, self.times)
self.gcbi_pre.inserts(sqls=sqls)
print(sqls)
def invoiceMsg(self,orderId='',orderItemId='',markId='',invoiceType='1',status='2',invoiceSign='1',custNo=''):
"""
手动写发票消息
:param orderId: 政企订单号
:param orderItemId: 政企订单行号
:param markId: 发票markId
:param invoiceType: 发票类型 1-增票 3-普票 4-电子发票
:param status: 发票状态 2-开票 3-邮寄 4-妥投
:param invoiceSign: 发票标识 1：蓝票 -1：红票
:return: null
说明：markId不为空时，orderId和orderItemId为空
"""
num = int(custNo) % 64
if markId != '': # {"markId":"zzspq0007test","invoiceSign":"1","invoiceType":"3","status":"3"}
msg = '{"markId":"%s","invoiceSign":"%s","invoiceType":"%s","status":"%s"}' % (markId,invoiceSign,invoiceType,status)
elif markId == '': # {"orderNo":"100001565566","invoiceSign":"1","orderItemNo":"10000156556601","invoiceType":"4","status":"2"}
if orderId != '' and orderItemId == '':
# 需要遍历订单行表，取出所有订单行号
result = self.gcms_pre.searchs(sqls="SELECT gc_item_no from srcorder_item WHERE gc_order_no='%s'" %(orderId))
for i in result:
msg = '{"orderNo":"%s","invoiceSign":"%s","orderItemNo":"%s","invoiceType":"%s","status":"%s"}' % \
(orderId,invoiceSign,i[0],invoiceType,status)
# print(msg)
# sqls = """INSERT INTO api_push_message ( type, message, app_key, create_time, update_time, status, message_status)
# VALUES %s""" % (('20', str(msg), custNo, self.times, self.times, '1', status),)
# # print(sqls)
# self.gcms_pre.inserts(sqls=sqls)
sqls = """INSERT INTO gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('20','%s','%s','%s','%s','X','X','X','1','X','%s','system')""" % \
(num, str(msg), custNo, self.times, self.times,status)
self.gcbi_pre.inserts(sqls=sqls)
elif orderId == '' and orderItemId != '':
orderId = orderItemId[0:-2]
msg = '{"orderNo":"%s","invoiceSign":"%s","orderItemNo":"%s","invoiceType":"%s","status":"%s"}' % \
(orderId, invoiceSign, orderItemId, invoiceType, status)
# print(msg)
elif orderId != '' and orderItemId !='':
orderId = orderItemId[0:-2]
msg = '{"orderNo":"%s","invoiceSign":"%s","orderItemNo":"%s","invoiceType":"%s","status":"%s"}' % \
(orderId, invoiceSign, orderItemId, invoiceType, status)
else:
print("至少要输入一个吧？")
# sqls = """INSERT INTO api_push_message ( type, message, app_key, create_time, update_time, status, message_status)
# VALUES %s""" % (('20',str(msg),custNo,self.times,self.times,'1',status),)
# # print(sqls)
# self.gcms_pre.inserts(sqls=sqls)
# 写分表
sqls = """INSERT INTO gcapi_push_message%s (type,message,cust_no,\
create_time,update_time,gc_order_no,gc_item_no,\
cmmdty_code,status,supplier_code,message_status,message_source) VALUES \
('20','%s','%s','%s','%s','X','X','X','1','X','%s','system')""" % \
(num, str(msg), custNo, self.times, self.times,status)
self.gcbi_pre.inserts(sqls=sqls)
print(sqls)