class GetOrderData():
'''
获取前一天的订单数据(status in (12,14,15,16,17) )
:status = 1 是需要有合同的单子，其他相反
'''
def __init__(self):
self.Data = Data()
self.yesterday = datetime.date.today() + datetime.timedelta(-1)
print('时间:', self.yesterday)
def getFreightId(self, status=1):
if status == 1:
self.freight_data = self.Data.query('tms_test',
"SELECT freight_id FROM tms_wl_freight WHERE (last_update_time BETWEEN '{} 00:00:00'"
" AND '{} 23:59:59') AND `status` in (12,14,15,16,17);".format(
self.yesterday, self.yesterday))
else:
self.freight_data = self.Data.query('tms_test',
"SELECT freight_id FROM tms_wl_freight WHERE (last_update_time BETWEEN '{} 00:00:00'"
" AND '{} 23:59:59') AND `status` in (11,13);".format(
self.yesterday, self.yesterday))
return self.freight_data
def getOrderId(self, status=1):
if status == 1:
self.order_data = self.Data.query('tms_test',
"SELECT first_order_id FROM tms_wl_freight WHERE (last_update_time BETWEEN '{} 00:00:00'"
" AND '{} 23:59:59') AND `status` in (12,14,15,16,17);".format(
self.yesterday,
self.yesterday))
else:
self.order_data = self.Data.query('tms_test',
"SELECT first_order_id FROM tms_wl_freight WHERE (last_update_time BETWEEN '{} 00:00:00'"
" AND '{} 23:59:59') AND `status` in (11,13);".format(
self.yesterday,
self.yesterday))
return self.order_data
class VerifySigningContract():
def __init__(self):
self.data = Data()
yesterday = datetime.date.today() + datetime.timedelta(-1)
self.startTime = '{} 00:00:00'.format(yesterday)
self.endTime = '{} 23:59:59'.format(yesterday)
self.startTimestamp = int(time.mktime(time.strptime(self.startTime, "%Y-%m-%d %H:%M:%S")))
self.endTimestamp = int(time.mktime(time.strptime(self.endTime, "%Y-%m-%d %H:%M:%S")))
def contractOfAffreightment(self):
'''
检验承运合同
:return:
'''
try:
sql = "SELECT * FROM (SELECT fre1.freight_id FROM tms_wl_freight fre1 WHERE ( fre1.create_time BETWEEN" \
" '{}' AND '{}' ) AND fre1.`status` IN ( 12, 14, 15, 16, 17 )) B WHERE (SELECT count(1) num FROM (SELECT com1.freight_id FROM tms_commission_contract" \
" com1 WHERE com1.contract_type = 2 ) A WHERE A.freight_id = B.freight_id ) = 0 ;".format(
self.startTime, self.endTime)
print("检验承运合同")
isTrue = self.data.query('tms_live_r', sql)
writeLog('未签署承运合同的运单', isTrue)
return isTrue
except Exception as err:
writeLog('contractOfAffreightment>>>error:', err)
return err
def checkTheContract(self):
'''
检验托运合同
:return:
'''
try:
sql = "SELECT * FROM (SELECT fre1.first_order_id FROM tms_wl_freight fre1 " \
"WHERE (fre1.create_time BETWEEN '{}' AND '{}') " \
"AND fre1.`status` IN ( 12, 14, 15, 16, 17 )) B WHERE (SELECT count( 1 ) num FROM ( " \
"SELECT com1.order_id FROM tms_commission_contract com1 WHERE com1.contract_type = 3 ) " \
"A WHERE A.order_id = B.first_order_id) = 0;".format(self.startTime, self.endTime, )
isTrue = self.data.query('tms_live_r', sql)
writeLog('未签署托运合同的订单', isTrue)
print("检验托运合同")
return isTrue
except Exception as err:
writeLog('checkTheContract>>>error:', err)
return err
def letterOfEntrustment(self):
list_s = []
'''
委托函
:return:
'''
try:
signFreightIdSQL = "SELECT C.waybill_sn,D.phone,D.card_name FROM zyb_pay_order A JOIN zyb_tms_wallet_out B " \
"ON A.pay_out_id = B.id JOIN zyb_tms_wallet_log C ON A.pay_log_id = C.id JOIN zyb_tms_wallet_bankcard " \
"D on B.bankcard_id = D.id WHERE add_time BETWEEN '{}' AND '{}'".format(
self.startTimestamp, self.endTimestamp)
responseData = self.data.query('zyb_live_r', signFreightIdSQL)
print("检测委托函")
for i in responseData:
sql = "SELECT driver_name,driver_mobile FROM tms_wl_freight WHERE freight_id ='{}';".format(
i['waybill_sn'])
response = self.data.query('tms_live_r', sql)
if i['phone'] != response[0]['driver_mobile']:
comm_sql = "SELECT count(*) A FROM tms_commission_contract WHERE contract_type = 1 AND freight_id = '{}'".format(
i['waybill_sn'])
response_comm = self.data.query('tms_live_r', comm_sql)
if response_comm[0]['A'] == 0:
list_s.append(i['waybill_sn'])
if len(list_s) > 1:
writeLog('未签署w委托合同的运单', list_s)
except Exception as err:
writeLog('letterOfEntrustment>>>error:', err)