def _result_insert(fee_resp, renewal_data_dt):
log = Logger()
insureCarId = renewal_data_dt.get('insureCarId', '')
CPlateNo = renewal_data_dt.get('plateNumber', '')
client = renewal_data_dt.get('client', '')
isPhone = renewal_data_dt['isPhone']
sessionId = renewal_data_dt.get('sessionId', '')
insuranceType = renewal_data_dt.get("insuranceType", {})
if isinstance(insuranceType, list):
insureTypeGroupId = insuranceType[0].get("insuranceTypeGroupId", "")
insuranceTypeGroup = insuranceType[0].get("insuranceTypeGroup", "")
else:
insureTypeGroupId = insuranceType.get("insuranceTypeGroupId", "")
insuranceTypeGroup = insuranceType.get("insuranceTypeGroup", "")
if isinstance(fee_resp, dict):
data = []
data.append(fee_resp['c01beginTime'])
data.append(fee_resp['c01endTime'])
data.append(fee_resp['vehicleSeats'])
data.append(insureTypeGroupId)
data.append(insureCarId)
data.append("1")
# log.info("平安开始入库 %s" % CPlateNo)
soupDb(fee_resp['fee'], data)
log.info("平安入库成功 %s ", CPlateNo)
send_mq(client, CPlateNo, "", "1", "1", sessionId, isPhone,
insureTypeGroupId, insuranceTypeGroup)
else:
if isinstance(fee_resp, list):
fee_resp = json.dumps(fee_resp, encoding=False)
log.error(fee_resp)
else:
log.error(fee_resp)
send_mq(client, CPlateNo, fee_resp, "2", "1", sessionId, isPhone,
insureTypeGroupId, insuranceTypeGroup)
class ConfigHttp:
def __init__(self):
self.host = localReadConfig.get_http("baseurl")
self.port = localReadConfig.get_http("port")
self.timeout = localReadConfig.get_http("timeout")
self.logger = Logger("httpUtil.py").getlog()
# defined http get method
def get(self, url, param=None):
new_url = self.host + ":" + self.port + "/" + url
header = {'Content-Type': 'application/json'}
try:
if param != None:
textmod = parse.urlencode(param)
req = request.Request(url='%s%s%s' % (new_url, '?', textmod), headers=header)
self.logger.debug('%s%s%s' % (new_url, '?', textmod))
else:
req = request.Request(url='%s' % (new_url), headers=header)
self.logger.debug(new_url)
res = request.urlopen(req)
res = res.read()
hjson = json.loads(res)
self.logger.debug(json.dumps(hjson, indent=4))
return hjson
except TimeoutError:
self.logger.error("Time out!")
return None
# defined http post method
def post(self, url, param):
data = json.dumps(param)
data = bytes(data, "utf-8")
new_url = self.host + ":" + self.port + "/" + url
self.logger.info('post data: \n' + json.dumps(param, indent=4))
try:
headers = {'Content-Type': 'application/json'}
req = request.Request(new_url, data, headers)
response = request.urlopen(req)
res = response.read()
hjson = json.loads(res)
self.logger.debug(json.dumps(hjson, indent=4))
except Exception as err:
print(err, "\n")
return hjson
def is_hn_renewal(session, licenseNo="", vinNo=""):
log = Logger()
try:
url = "http://qcar.chinahuanong.com.cn/quotepriceasync/getOldPolicy.do"
headers[
'Content-Type'] = "application/x-www-form-urlencoded; charset=UTF-8"
data = {"licenseNo": licenseNo, "vinNo": vinNo, "engineNo": ""}
rsp = session.post(url=url, data=data, headers=headers)
if u"发生异常" in rsp.text:
return 0
else:
if len(rsp.json()) != 0:
log.info(u"是华农续保用户")
return rsp.json()
else:
return 0
except Exception as e:
log.error(e)
return 0
def _Get_RandomNum(session_req):
log = Logger()
try:
millis = int(round(time.time() * 1000))
url = "http://32.0.192.232:88/sinoiais/pages/login/RandomNumUtil.jsp?d={0}".format(
str(millis))
code_resp = session_req.get(url=url, headers=headers)
# 验证码
codestr = None
if code_resp:
code_base64 = base64.b64encode(code_resp.content)
codestr = pic2Str(base64.b64decode(code_base64))
if not codestr:
log.info("获取验证码异常")
log.info("codestr=%s" % codestr)
return codestr
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
return None
def _checkLoginInfo(session_req, codestr):
log = Logger()
try:
url = "http://32.0.192.232:88/sinoiais/checklogin/checkLoginInfo.do"
headers['Content-Type'] = "application/x-www-form-urlencoded"
data_1 = {
"sysUserCode": "CJCXwm01",
"sysPassWord": "******",
"random": codestr
}
login_1_resp = session_req.post(url=url, data=data_1, headers=headers)
if "success" in login_1_resp.text:
log.info(u"登录成功")
return 1
elif "randomError" in login_1_resp.text:
log.error(u"验证码错误")
return 0
else:
return 2
except Exception as e:
log.error(u"登录失败")
log.error(traceback.format_exc())
return 2
from common.log import Logger
logger = Logger(name='firms_fpb',
config_path='/FiRMS/builder/config/log/logging.json',
log_file="/FiRMS/builder/log/logfile")
#logger = Logger(name='firms_fpb', config_path='/FiRMS/builder/config/log/logging.json')
sys_logger = Logger(name='sys-logger',
config_path='/FiRMS/builder/config/log/logging.json')
logger.info('test')
sys_logger.info("test1")
# dayGap = compare_time40(data['endDate'])
# if dayGap>40:
# log.error(u"上期保单截止时间超过四十天，大地不可询价。上期保单截止时间：%s" % data['endDate'])
# send_mq(client, dt['licenseNo'], "上期保单截止时间超过四十天，不可询价。上期保单截止时间：%s" % data['endDate'], "2", "5", sessionId, isPhone, groupId,insuranceTypeGroup)
# return u"上期保单截止时间超过四十天，大地不可询价。上期保单截止时间：%s" % data['endDate']
# elif dayGap<0:
# pass
# else:
# dt['endDate'] = datetime.strptime(data['endDate'], "%Y-%m-%d").date()
#通过车架号获取校验码
DVM_res = get_check_img(dt)
DVM_res_items = jsonpath(DVM_res, "$.result.items")
if DVM_res_items:
if len(DVM_res_items) > 0:
log.info(u"车架号 %s，请求车管所完成" % dt['vinNo'])
dt['jsCheckCode'] = DVM_res_items[0][0].get(
'jsCheckCode', None)
dt['jsCheckNo'] = DVM_res_items[0][0].get('jsCheckNo', None)
#dt['checkAnswer'] = request_cicc.util.pic2Str(base64.b64decode(dt['jsCheckCode']))
dt['checkAnswer'] = dama("3", dt['jsCheckCode'])
log.info(dt['checkAnswer'])
#上报打码结果获取车管所返回信息
post_checkAnswer_res = post_checkAnswer(dt)
post_checkAnswer_res_items = jsonpath(post_checkAnswer_res,
"$.result.items")
count = 0
log.info(u"上报验证码完成")
log.info(dt['vinNo'])
# Note the start time so as to calculate the time time taken by the script to execute
start_time = time.time()
parser = argparse.ArgumentParser()
parser.add_argument("-i", "--input_file", required=True)
parser.add_argument("-t", "--ticket_number", required=True)
args = parser.parse_args()
# Get the excel sheet from the command line
input_file = args.input_file
# Get the SR Number from the command line
sr_number = args.ticket_number
sys_logger.info('SR Number: {}'.format(sr_number))
# Get the username
username = getpass.getuser()
#sys_logger.info ('Username: {}'.format(username))
# Get current date
today = str(date.today())
(yy, mm, dd) = today.split('-')
# Get timestamp
curr_time = datetime.now().strftime("%H:%M:%S")
curr_time = curr_time.replace(':', '')
user_dir = sr_number + '__' + str(curr_time)
def pre_process(input_file, sr_number, username, unix_proc_no, logfile):
global logger
log_json_path = LOG_CFG_PATH + '/logging.json'
logger = Logger(name='firms_fpb',
config_path=log_json_path,
log_file=logfile)
start_excel_time = time.time()
logger.info('Pre Process Start Time: {}'.format(start_excel_time))
# Convert the excel sheet to json
logger.info('Convert excel to JSON')
json_file = excel_to_json_convert(input_file, logfile)
end_excel_time = time.time() - start_excel_time
# Validate the json
logger.info('Validating JSON')
is_valid_json = validate_json(json_file, logfile)
#exit(1)
# If excel sheet is not valid
if not is_valid_json:
logger.error('Invalid excel sheet provided')
print("Invalid excel sheet provided")
exit(1)
start_read_json_time = time.time()
# Read the JSON data
json_data = read_json_file(json_file)
end_read_json_time = time.time() - start_excel_time
print("Total Time Taken for reading json: ",
time.strftime("%H:%M:%S", time.gmtime(end_read_json_time)))
exit(1)
start_prep_time = time.time()
# Open the json file and create the data structure for storing into db
(data_to_load, no_of_batches) = prepare_data(json_data, username,
sr_number, unix_proc_no)
end_prep_time = time.time() - start_prep_time
print("Total Time Taken for preparing data: ",
time.strftime("%H:%M:%S", time.gmtime(end_prep_time)))
# Create the insert query and put it here
insert_query = "insert into incoming_request (sr_number,username,src_ip,dst_ip,port,protocol,id,src_ip_int,dst_ip_int,process_no,unix_process_no,inc_req_row_id,request_date) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)"
start_load_time = time.time()
logger.info('Loading input data into incoming_request table')
logger.info('Load start time: {}'.format(start_load_time))
try:
#print ("pre processing")
is_loaded = load_data(data_to_load, insert_query)
except Exception as e:
logger.error('Failed to insert data for pre-processing', e)
print(e)
end_load_time = time.time()
total_load_time = end_load_time - start_load_time
logger.info('Load end time: {}'.format(end_load_time))
#print("Total Time Taken for loading the data: ", time.strftime("%H:%M:%S", time.gmtime(total_load_time)))
return no_of_batches
def is_pingan_renwal(session, plateNumber, repet=0):
log = Logger()
try:
if '-' not in plateNumber:
plateNumber = plateNumber[:2] + "-" + plateNumber[2:]
if session == '' or session is None:
login = Login()
session = login.login()
try:
repet = 0
user_data = is_renewal(session, plateNumber)
except Exception as e:
if repet == 0:
user_data = is_renewal(session, plateNumber)
repet += 1
# 续保用户
if isinstance(user_data, dict):
icorepnbs_session = user_data['request']
quick_resp = quickSearchVoucher(icorepnbs_session, user_data)
# 如果商业险的结束时间为空那么取交强险的结束时间作为保险时间
str_date = quick_resp['vehicleTarget']['firstRegisterDate']
insuranceType = quick_resp['insuranceType']
endDate = user_data.get("sy_insuranceEndTime", "").split(" ")[0]
if endDate == "":
endDate = user_data.get("jq_insuranceEndTime",
"").split(" ")[0]
out = {
"licenseNo":
user_data['vehicleLicenceCode'].replace('-', ''),
"vinNo":
user_data['vehicleFrameNo'],
"endDate":
endDate,
"CCardDetail":
quick_resp['extendInfo'].get("ownerVehicleTypeCode", ""),
"brandName":
quick_resp['vehicleTarget']['modifyAutoModelName'],
"insuredName":
user_data['ownerName'],
"identifyNumber":
quick_resp.get("insurantInfo", {}).get("certificateTypeNo",
""),
"CUsageCde":
"",
"NNewPurchaseValue":
"",
"insuredAddress":
"",
"mobile":
"",
"enrollDate":
str_date.split(" ")
[0], # str_date[:4]+"-"+str_date[4:6]+"-"+str_date[6:],
"engineNo":
user_data['engineNo'],
"CModelCde":
user_data['makerModel'],
"NSeatNum":
quick_resp['vehicleTarget']['vehicleSeats'],
"COMPANY_ID":
"1",
"insuranceType":
insuranceType,
"insuranceTime": {
'syEnd': user_data.get("sy_insuranceEndTime", ""),
'syStart': user_data.get("sy_insuranceBeginTime", ""),
'jqStart': user_data.get("jq_insuranceBeginTime", ""),
'jqEnd': user_data.get("jq_insuranceEndTime", "")
}
}
return out
else:
log.info(u"未查询到续信息%s", plateNumber)
return 0
except Exception, e:
log.error(e)
log.error(traceback.format_exc())
return 0
interimNo = str(re9.json()['interimNo'])
#####获取车管所验证码
headers['Referer'] = 'http://www.epicc.com.cn/wap/carProposal/car/carInput2'
data2 = 'licenseNo=' + urllib.quote(
licenseNo) + '&frameNo=' + rackNo + '&channelNo=2&sessionId=' + sessionid
re2 = s.post(
url='http://www.epicc.com.cn/wap/carProposal/JSArea/obtainVerificationCode',
data=data2)
body2 = re2.json()
code_msg = body2['message']
code_msg = eval(code_msg)
#log.info("code_msg=%s" % code_msg)
check_code = jsonpath.jsonpath(code_msg, '$.check_code')[0]
log.info("check_code=%s" % check_code)
fname = '../image/' + utils.getTimstamp() + '.jpg'
#log.info('code =%s'% check_code)
log.info('picnam =%s' % fname)
imagebse64.base642img(check_code, fname)
codestr = utils.pic2Str(fname)
log.info(u"验证码为=%s,文件名为=%s" % (codestr, fname))
#发送验证码
data3 = 'icationCode=' + codestr + '&channelNo=2&sessionId=' + sessionid
re3 = s.post(
url='http://www.epicc.com.cn/wap/carProposal/JSArea/obtainVerifQueryCar',
data=data3)
body3 = re3.json()
body3 = ast.literal_eval(body3['message'])
code = body3['head']['errorCode']
class Login(object):
def __init__(self, username=None, password=None):
self.log = Logger()
self.session_req = requests.session()
self.session_req.headers.update(se.headers)
if username is None or password is None:
self.username = se.login_username
self.password = se.login_password
# 刷新获取验证码
def __refresh_code(self, reqsss):
code_url = "https://icore-pts.pingan.com.cn/ebusiness/auto/rand-code-imgage.do" \
"?random=0.37327206414192915"
code_resp = reqsss.get(url=code_url, headers=se.headers, verify=False)
code_base64 = base64.b64encode(code_resp.content)
codestr = dama("2", code_base64)
self.log.info(u"平安登录验证码为%s", codestr)
return codestr
# 发送验证码
def __validate_code(self, reqsss, codestr):
set_code_url = "https://icore-pts.pingan.com.cn/ebusiness/auto/" \
"VerifyRandCodeController.do?randCode=" + codestr
set_code_resp = reqsss.get(url=set_code_url,
headers=se.headers,
verify=False)
set_code_result = set_code_resp.text
if set_code_result == "true":
self.log.info(u"平安验证码校验正确")
return 1
self.log.error(u"平安登录验证码错误")
return 0
def __use_login(self, reqsss):
# 登录前置请求
login_monitor_url = "https://icore-pts.pingan.com.cn/ebusiness/auto/monitor.do?" \
"type=insertLoginLog&flowid=&userId=STDJNDS-00001&_t=1487214110283"
reqsss.post(url=login_monitor_url, headers=se.headers, verify=False)
def __login(self, reqsss, codestr):
login_url = 'https://icore-pts.pingan.com.cn/ebusiness/j_security_check'
se.headers['Content-Type'] = "application/x-www-form-urlencoded"
headers = se.headers
login_data = "j_username="******"&j_password="******"&SMAUTHREASON=0&randCode=" + codestr
req = reqsss.post(url=login_url,
data=login_data,
headers=headers,
verify=False)
if req.url == u'https://icore-pts.pingan.com.cn/ebusiness/':
self.log.info(u"平安登录成功")
# 往redis中存储session
set_session(self.session_req, "1")
return self.session_req
self.log.error(u"平安登录失败")
self.log.error(req.text)
return 0
def login(self):
try:
# 刷新验证码
codestr = self.__refresh_code(self.session_req)
# 校验验证码
count = 1
validate_code_result = self.__validate_code(
self.session_req, codestr)
while validate_code_result == 0 and count < 4:
codestr = self.__refresh_code(self.session_req)
validate_code_result = self.__validate_code(
self.session_req, codestr)
count += 1
# 发送登录前置请求
self.__use_login(self.session_req)
out = self.__login(self.session_req, codestr)
# self.auto_index_do(out)
if out != 0:
#self.auto_index_do(out)
return out
except Exception as error:
self.log.info(error)
# 获取保持连接的id
def auto_index_do(self, session):
'''
:param session:登录后的session
:return:flow_id
'''
index_url = "https://icore-pts.pingan.com.cn/ebusiness/auto/newness/index.do"
index_rsp = session.get(url=index_url, verify=False)
index_rsp_text = index_rsp.text
flow_id = re.findall(r"<div id=\"main\" flowid=\"(.+?)\">",
index_rsp_text, re.S)[0]
r = CRedis()
r.set("pingan_keepid", flow_id)
'''
plateNo_getSource(carInfo)
# 爬取
def main():
try:
reactor.suggestThreadPoolSize(5)
Consumer().runrealtime()
reactor.run()
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
'''
#方法运行
threads = []
log.info(u"程序开始运行%s" % datetime.now())
for arg in xrange(10):
th = threading.Thread(target=runrealtime)
th.start()
threads.append(th)
try:
for th in threads:
th.join()
except Exception,e:
log.error(e)
log.error(traceback.format_exc())
log.info(u"程序结束运行%s" % datetime.now())
def validate_json(input_file, logfile):
log_json_file = LOG_CFG_PATH + '/logging.json'
global logger
logger = Logger(name='firms_fpb',
config_path=log_json_file,
log_file=logfile)
with open(input_file) as json_data:
jsonData = json.load(json_data)
# print the keys and values
count = 0
all_errors = OrderedDict()
final_errors = ""
for rec in jsonData:
for key, value in rec.iteritems():
line_number = str(rec['input_row_id'])
# Check 1 - Value should not be empty
if key not in 'protocol' and not value:
retVal = " Error : Record number :" + line_number + " Column " + key + " is empty \n"
final_errors += retVal
continue
# Check 2 - Only ASCII characters allowed
try:
temp_val = str(value)
temp_val.encode('ascii')
except:
retVal = "Error : Record number : " + line_number + " Column " + str(
key) + " has non ASCII character \n"
final_errors += retVal
continue
# Check 3 - Valid values
if key == 'source':
retVal = is_valid_format(key, str(value))
elif key == 'dest-port':
retVal = is_valid_port_format(str(value))
elif key == 'destination':
retVal = is_valid_format(key, str(value))
elif key == 'protocol':
retVal = is_valid_protocol(str(value))
elif key == 'input_row_id':
retVal = "Check passed"
if 'Error' in retVal:
final_errors += "Record number: " + line_number + " " + str(
retVal) + "\n"
count += 1
logger.info('Total records {}'.format(count))
print('Total records : ', count)
# IF there are validation errors
if final_errors:
pattern = re.compile('(.*?).json')
match = pattern.match(input_file)
filename = match.group(1)
new_filename = filename.replace("data", "out")
curr_time = time.time()
output_filename = new_filename + '__val__errors__' + str(
curr_time) + '.' + 'txt'
# Write to file
try:
with open(output_filename, 'w') as f:
f.write(final_errors)
print("Input file parsing errors generated at : ", output_filename)
logger.info('Excel validation errors generated at : {}'.format(
output_filename))
print("\nExiting FiRMS Gen, failed to validate input file \n")
exit(1)
except Exception as e:
print("Failed to create validation errors file", e)
logger.error(
'Failed to create validation errors file {}'.format(e))
exit(1)
return (count)
def excel_to_json_convert(input_file, logfile):
log_json_file = LOG_CFG_PATH + '/logging.json'
global logger
logger = Logger(name='firms_fpb',
config_path=log_json_file,
log_file=logfile)
# Open the workbook and select the first worksheet
wb = xlrd.open_workbook(input_file)
sh = wb.sheet_by_index(0)
# List to hold dictionaries
firewall_list = []
column_names = sh.row_values(0)
logger.info('Column Names in input excel {}'.format(column_names))
col1 = column_names[0].lower()
pattern = r'(^|[^\w]){}([^\w]|$)'.format(col1)
src_matches = re.search(pattern, col1)
if not src_matches:
logger.info(
'Input file is in invalid format, first column should denote Source'
)
# print ("Input file is in invalid format, first column should denote Source")
# exit (1)
col2 = column_names[1].lower()
pattern = r'(^|[^\w]){}([^\w]|$)'.format(col2)
ip_matches = re.search(pattern, col1)
pattern = re.compile(r'(ip|subnet)')
if not pattern.search(col2):
logger.info(
'Input file is in invalid format, second column should denote Source IP or Source Subnet'
)
# print("Input file is in invalid format, second column should denote Source IP or Source Subnet")
# exit(1)
col3 = column_names[2].lower()
pattern = re.compile(r'(port)')
if not pattern.search(col3):
logger.info(
'Input file is in invalid format, third column should denote port')
# print("Input file is in invalid format, third column should denote port")
# exit(1)
col4 = column_names[3].lower()
pattern = re.compile(r'(destination)')
if not pattern.search(col4):
logger.info(
'Input file is in invalid format, fourth column should denote Destination'
)
# print ("Input file is in invalid format, fourth column should denote Destination")
# exit(1)
col5 = column_names[4].lower()
pattern = re.compile(r'(ip|subnet)')
if not pattern.search(col5):
logger.info(
'Input file is in invalid format, fifth column should denote Destination IP or Destination Subnet'
)
# print ("Input file is in invalid format, fifth column should denote Destination IP or Destination Subnet")
# exit(1)
isProtocol = False
#print(len(column_names))
if len(column_names) >= 6:
#print('inside column_names')
col6 = column_names[5].lower()
if not re.match("protocol", col6):
logger.info('Sixth column should be Protocol')
print("Protocol column not provided, defaulting to TCP")
else:
isProtocol = True
# Iterate through each row in worksheet and fetch values into dict
for rownum in range(1, sh.nrows):
firewall = OrderedDict()
row_values = sh.row_values(rownum)
firewall['source'] = row_values[1]
dest_port = row_values[2]
firewall['dest-port'] = dest_port
firewall['destination'] = row_values[4]
if isProtocol:
if not row_values[5]:
protocol = 'TCP'
else:
#print('insideeeee')
protocol = row_values[5]
else:
#print('else block')
protocol = 'TCP'
#print('PROTOCOL')
#print (protocol,row_values[5])
firewall['protocol'] = protocol.upper()
firewall['input_row_id'] = rownum
firewall_list.append(firewall)
# Serialize the list of dicts to JSON
j = json.dumps(firewall_list)
pattern = re.compile('(.*?).xls')
match = pattern.match(input_file)
filename = match.group(1)
output_filename = filename + '.' + 'json'
# Write to file
with open(output_filename, 'w') as f:
f.write(j)
#print ("Generated output file is: ",output_filename)
logger.info('Generated JSON file is {}'.format(output_filename))
return (output_filename)
def post_process(sr_number, username, unix_proc_no, input_file, new_dir_json,
new_dir_txt, logfile):
global logger
logger = Logger(name='firms_fpb',
config_path=log_json_file,
log_file=logfile)
time_before_select = time.time()
# Select the data from incoming_request table for passed unix PID
select_query = "select route_issue,src_ip,dst_ip,port,protocol,src_firewall,src_zone,dst_zone,src_addr_book,src_addr_name,src_addr_nm_exists,dst_addr_book,dst_addr_name," \
"dst_addr_nm_exists,app_name,policy_exists,net_proto,app_name_exists,app_dst,dst_firewall,inc_req_row_id,src_ip_int,dst_ip_int,exception_msg,dst_firewall,policy_name," \
"src_zone_2,dst_zone_2,src_addr_book_2,src_addr_name_2,src_addr_nm_exists_2,dst_addr_book_2,dst_addr_name_2,dst_addr_nm_exists_2,app_name_2,app_name_exists_2,policy_exists_2,policy_name_2,src_subnet,dst_subnet" \
" from incoming_request where unix_process_no=:1"
logger.info('Select Query {}'.format(select_query))
bind_data = (unix_proc_no, )
temp_data = extract_data(select_query, bind_data)
data = []
for record in temp_data:
existing_record = list(record)
# Append the record
data.append(existing_record)
# If Source Firewall is different than Destination Firewall then we need to split the record into two
if record[5] != record[19] and record[0] != 'Y':
#if record[15] != 'Y' record[25] != 'Y':
if True:
#logger.info ('Source firewall is different than Destination Firewall')
new_record = list(record)
# Routing issue is per record so change the routing issue flag to N so as to avoid getting it printed twice
# in routing issues text file
new_record[0] = "N"
new_record[5] = record[24]
new_record[6] = record[26]
new_record[7] = record[27]
new_record[8] = record[28]
new_record[9] = record[29]
new_record[10] = record[30]
new_record[11] = record[31]
new_record[12] = record[32]
new_record[13] = record[33]
new_record[14] = record[34]
new_record[17] = record[35]
new_record[15] = record[36]
new_record[25] = record[37]
data.append(new_record)
#logger.info ('Data ready for post processing {}'. format (data))
time_after_select = time.time()
total_time_taken_select = time_after_select - time_before_select
#print("Total Time Taken for data Extraction: ", time.strftime("%H:%M:%S", time.gmtime(total_time_taken_select)))
all_policies = {}
src_dst_port_dict = {}
row_numbers = {}
route_issues = []
route_issue_count = 0
existing_policies = []
existing_pol_count = 0
issue_record_count = 0
counter = 0
all_src_addr = OrderedDict()
all_dst_addr = OrderedDict()
all_apps = OrderedDict()
# Loop through all the records
for record in data:
route_issue = record[0]
src_ip = record[1]
dst_ip = record[2]
port = record[3]
protocol = record[4]
src_firewall = record[5]
src_zone = record[6]
dst_zone = record[7]
src_addr_book = record[8]
src_addr_name = record[9]
src_addr_nm_exists = record[10]
dst_addr_book = record[11]
dst_addr_name = record[12]
dst_addr_nm_exists = record[13]
app_name = record[14]
policy_exists = str(record[15])
net_proto = record[16]
app_name_exists = record[17]
app_dest = record[18]
dst_firewall = record[19]
row_id = record[20]
src_ip_int = str(record[21])
dst_ip_int = str(record[22])
route_exc_message = record[23]
dst_firewall = record[24]
existing_pol_name = record[25]
src_zone_2 = record[26]
dst_zone_2 = record[27]
src_subnet_nw = record[38]
dst_subnet_nw = record[39]
total_rec_policies = 0
# If there is a route issue then add the route details to the route_issues data structure
if route_issue == 'Y':
route_issues.append({
'Source-IP': src_ip,
'Destination-IP': dst_ip,
'Port': port,
'Source-Firewall': src_firewall,
'Destination-Firewall': dst_firewall,
'Routing-Issue-Reason': route_exc_message,
'Source-Zone': src_zone,
'Dest-Zone': dst_zone,
'Source-Zone-2': src_zone_2,
'Dest-Zone-2': dst_zone_2,
'Input-Row-ID': row_id
})
route_issue_count += 1
else:
# First level of grouping of policies is done for SOURCE FIREWALL
# So if all_policies doesnt already have source firewall as the key then add it here
if src_firewall not in all_policies.keys():
all_policies[src_firewall] = {
'new_src_cmd': {},
'new_dst_cmd': {},
'new_app_cmd': {}
}
src_dst_port_dict[src_firewall] = {}
total_rec_policies += 1
# Check if source address exists, if not create
if src_addr_nm_exists == 'N' and 'N' in policy_exists:
# IF src_ip_int exists then it means its IP then append /32, else use the subnet as is
#if src_ip_int != 'None':
if '/' not in src_ip:
src_subnet = src_ip + '/32'
else:
# if src_subnet is not null (i.e. from network file if we get then use the same else use the one provided in input file)
if src_subnet_nw is None:
src_subnet = src_ip
else:
src_subnet = src_subnet_nw
all_policies[src_firewall]['new_src_cmd'][src_addr_name] = {
'subnet': src_subnet,
'src_addr_book': src_addr_book,
'src_addr_name': src_addr_name
}
# Check if dest address exists, if not create
if dst_addr_nm_exists == 'N' and 'N' in policy_exists:
# IF dst_ip_int exists then it means its IP then append /32, else use the subnet as is
#if dst_ip_int != 'None':
if '/' not in dst_ip:
dst_subnet = dst_ip + '/32'
else:
if dst_subnet_nw is None:
dst_subnet = dst_ip
else:
dst_subnet = dst_subnet_nw
all_policies[src_firewall]['new_src_cmd'][dst_addr_name] = {
'subnet': dst_subnet,
'src_addr_book': dst_addr_book,
'src_addr_name': dst_addr_name
}
# Check if application exists or not
# app_name_exists in the form of N,Y,N,N
# app_name_list in the form of app1,app2,app3
# app_dest_list in the form of port1,port2,port3
app_exists_list = str(app_name_exists).split(",")
app_name_list = str(app_name).split(",")
app_dest_list = str(app_dest).split(",")
i = 0
while i < len(app_name_list):
key = src_addr_name + '__' + dst_addr_name + '__' + app_name_list[
i]
try:
if key in row_numbers.keys():
row_numbers[key] = str(
row_numbers[key]) + ',' + str(row_id)
else:
row_numbers[key] = row_id
except Exception as e:
logger.error('Error occured in row numbers {}'.format(e))
i += 1
# Split the existing pol name column contents
pol_name_list = str(existing_pol_name).split(",")
port_list = str(port).split(";")
i = 0
# Need to check if there are multiple apps, there can be more than one app
if len(app_exists_list) > 0 and 'N' in policy_exists:
if 'new_app_cmd' not in all_policies[src_firewall].keys():
all_policies[src_firewall]['new_app_cmd'] = OrderedDict()
while i < len(app_exists_list):
is_exists = app_exists_list[i]
if is_exists == 'N':
new_app_name = app_name_list[i]
new_app_dest = app_dest_list[i]
all_policies[src_firewall]['new_app_cmd'][
new_app_name] = {
'app_dest': new_app_dest,
'net_proto': net_proto
}
i += 1
# Need to check for policy now
policy_exists_list = str(policy_exists).split(",")
if src_zone == None or dst_zone == None:
logger.error(
'FiRMS engine couldnt process record number {}'.format(
row_id))
print("FiRMS engine couldnt process record number ",
format(row_id))
issue_record_count += 1
continue
# Make a unique key src_zone_dst_zone
src_dst_key = src_zone + "_" + dst_zone
i = 0
# The input to this while loop is something of this form (Y,N,Y)
while i < len(policy_exists_list):
is_exist_policy = policy_exists_list[i]
app = app_name_list[i]
# Only if policy doesnt exists and a new one needs to be created
if is_exist_policy == 'N':
# Second level of grouping is done by Source Zone and Destination Zone
# Check if we have already encountered this src_cone and dst_zone before, if not then add
key = src_addr_name + '__' + dst_addr_name + '__' + app
if src_dst_key not in all_policies[src_firewall].keys():
all_policies[src_firewall][src_dst_key] = {key: key}
else:
all_policies[src_firewall][src_dst_key][key] = key
else:
# If we are here then that means the policy is already existing
# Add the existing policies
pol_name = pol_name_list[i]
existing_policies.append({
'Source-IP': src_ip,
'Destination-IP': dst_ip,
'Port': app_dest_list[i],
'Policy-Name': pol_name,
'Input-Row-ID': row_id,
'Firewall': src_firewall
})
i += 1
counter = 0
new_policies = copy.deepcopy(all_policies)
for each_fw in all_policies.keys():
for each_zone in all_policies[each_fw].keys():
# No action required if new_src_cmd or new_dst_cmd or new_app_cmd
if each_zone == 'new_src_cmd':
continue
if each_zone == 'new_dst_cmd':
continue
if each_zone == 'new_app_cmd':
continue
new_policies[each_fw].pop(each_zone, None)
for each_fw in all_policies.keys():
for each_zone in all_policies[each_fw].keys():
data = []
# No action required if new_src_cmd or new_dst_cmd or new_app_cmd
if each_zone == 'new_src_cmd':
continue
if each_zone == 'new_dst_cmd':
continue
if each_zone == 'new_app_cmd':
continue
# Create Data structure for group_data function
for each_key in all_policies[each_fw][each_zone].keys():
(src, dst, port) = each_key.split("__")
temp_rec = {'src': [src], 'dst': [dst], 'app': [port]}
data.append(temp_rec)
grouped_data = club(data)
new_policies[each_fw][each_zone] = grouped_data
logger.info('Possible Red Flags {}'.format(route_issue_count))
#sys_logger.info ('Possible Red Flags: {}'.format(route_issue_count))
#sys_logger.info ('Policies recommended for: {}'.format(total_rec_policies))
print("Possible Red Flags: ", route_issue_count)
final_policies = create_policy(new_policies, sr_number, username,
row_numbers)
# Write new policies into text file
policies_file = sr_number + "_policies"
json_file = policies_file + ".json"
json_new_pol_path = new_dir_json + json_file
txt_new_pol_path = new_dir_txt + policies_file + '.txt'
write_json_file(final_policies, json_new_pol_path)
logger.info('JSON Policies file generated {}'.format(json_new_pol_path))
convert_json_to_txt_policies(json_new_pol_path, txt_new_pol_path)
#generate_sep_fw_pol ( json_new_pol_path, new_dir_txt )
# Write red flags into text file
route_issues_file = sr_number + "_red_flags"
json_route_issue = route_issues_file + ".json"
json_red_flag_path = new_dir_json + json_route_issue
txt_red_flag_path = new_dir_txt + route_issues_file + '.txt'
write_json_file(route_issues, json_red_flag_path)
logger.info('JSON Red Flags file generated {}'.format(json_red_flag_path))
convert_json_to_txt_issues(json_red_flag_path, txt_red_flag_path)
# Write existing policies into text file
existing_pol_file = sr_number + "_existing_policies"
json_existing_pol = existing_pol_file + ".json"
json_ex_pol_path = new_dir_json + json_existing_pol
txt_ex_pol_path = new_dir_txt + existing_pol_file + '.txt'
write_json_file(existing_policies, json_ex_pol_path)
logger.info(
'JSON Existing Policies file generated {}'.format(json_ex_pol_path))
convert_json_to_existing_txt_policies(json_ex_pol_path, txt_ex_pol_path)
return 1
def getSource(dt):
log = Logger()
try:
sessiondt = ""
companyId = dt.get('companyId', [])
# sessiondt = getSession()
# requestcic = sessiondt['4']
# loginRes = sessiondt['5']
# pingansession = sessiondt['1']
# ancheng_session = sessiondt['12']
# hn_session = sessiondt['13']
requestcic = None
loginRes = None
pingansession = None
ancheng_session = None
hn_session = None
plateNumber = dt['plateNumber']
insureCarId = dt['insureCarId']
sessionId = dt['sessionId']
isPhone = dt['isPhone']
client = dt['client']
insuranceType = dt['insuranceType']
insureTypeGroupId = insuranceType.get("insuranceTypeGroupId", "")
insuranceTypeGroup = insuranceType.get("insuranceTypeGroup", "")
carInfo = query_user_renewal(plateNumber)
jq_sy_out = {}
if carInfo == 0:
carInfo = query_user_permium_time(plateNumber)
if carInfo != 0:
jq_sy_out = jq_sy_time(carInfo)
log.info(json.dumps(jq_sy_out, ensure_ascii=False))
else:
jq_sy_out = jq_sy_time(carInfo)
log.info(json.dumps(jq_sy_out, ensure_ascii=False))
if jq_sy_out.get("code", "0") == "1000":
dt['insuranceTime'] = jq_sy_out
elif jq_sy_out.get("code", "0") == "1001" or jq_sy_out.get(
"code", "0") == "1002":
cmp_id = '|'.join(
str(i)
for i in companyId) if len(companyId) != 0 else "1|2|4|5|12|13"
send_mq(client, plateNumber, jq_sy_out["msg"], "2", cmp_id,
sessionId, isPhone, insureTypeGroupId, insuranceTypeGroup)
return
else:
dt['insuranceTime'] = {
"code": "1003",
"msg": "数据库中未查询到信息自动生成当前时间",
"syStart": getlatedate(1),
"jqStart": getlatedate(1)
}
# 货车暂时只爬取人保的保费
licenseType = dt.get('licenseType', "02")
if licenseType == "01" and companyId and '12' not in companyId:
log.info(u"货车暂时只爬取人保的保费")
send_mq(client, plateNumber, "货车暂时只爬取人保的保费", "2", "2|4|5|12|13",
sessionId, isPhone, insureTypeGroupId, insuranceTypeGroup)
return
vinNo = dt.get('vinNo', '')
insureCarId = dt.get('insureCarId', '')
if vinNo == '' or insureCarId == '':
return
dt['endDate'] = dt.get("preInsureEndDate", "")
if dt.get('identitCard', '') == '':
dt['identitCard'] = '320123199106102810'
dt['insuranceType'] = dt.get("insuranceType", "")
log.info(u"程序开始运行%s" % datetime.datetime.now())
try:
g_list = []
if not companyId:
g1 = gevent.spawn(getData, requestcic, dt)
g2 = gevent.spawn(flow_main, loginRes, dt)
g3 = gevent.spawn(pingan_request, pingansession, dt)
g4 = gevent.spawn(get_premium, ancheng_session, dt)
g5 = gevent.spawn(hn_request, hn_session, dt)
gevent.joinall([g1, g2, g3, g4, g5])
if companyId and '4' in companyId:
g1 = gevent.spawn(getData_CPlateNo, requestcic, dt)
g_list.append(g1)
if companyId and '5' in companyId:
g2 = gevent.spawn(flow_main, loginRes, dt)
g_list.append(g2)
if companyId and '1' in companyId:
g3 = gevent.spawn(pingan_request, pingansession, dt)
g_list.append(g3)
if companyId and '12' in companyId:
g4 = gevent.spawn(get_premium, ancheng_session, dt)
g_list.append(g4)
if companyId and '13' in companyId:
g5 = gevent.spawn(hn_request, hn_session, dt)
g_list.append(g5)
if g_list:
gevent.joinall(g_list)
print u"程序结束运行%s" % datetime.datetime.now()
except Exception, e:
log.error(e)
log.error(traceback.format_exc())
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
def plateNo_getSource(dt):
log = Logger()
try:
requestcic = dt['4']
loginRes = dt['5']
pingansession = dt['1']
ancheng_session = dt['12']
hn_session = dt['13']
companyId = dt.get('companyId', [])
plateNumber = dt['licenseNo']
sessionId = dt['sessionId']
isPhone = dt['isPhone']
insuranceType = dt['insuranceType']
insureTypeGroupId = insuranceType.get("insuranceTypeGroupId", "")
insuranceTypeGroup = insuranceType.get("insuranceTypeGroup", "")
cdt = {}
cdt['identitCard'] = dt['identifyNumber']
cdt['custName'] = dt['insuredName']
cdt['plateNumber'] = plateNumber
cdt['vinNo'] = dt['vinNo']
cdt['engineNo'] = dt['engineNo']
cdt['insureCarId'] = dt['insureCarId']
cdt['cityCode'] = get_citycode(dt['licenseNo'])
cdt['endDate'] = dt['endDate']
cdt['firstRegister'] = dt['enrollDate']
cdt['vehicleBrand'] = dt['brandName']
cdt['NSeatNum'] = dt.get('NSeatNum', 999)
cdt['client'] = dt['client']
client = cdt['client']
cdt['sessionId'] = sessionId
cdt['isPhone'] = isPhone
cdt['insuranceType'] = insuranceType
cdt['insuranceTime'] = dt.get("insuranceTime", {})
cdt['licenseType'] = dt.get("licenseType", "02")
# print(cdt)
jq_sy_out = jq_sy_time(cdt)
log.info(json.dumps(jq_sy_out, ensure_ascii=False))
# print(cdt)
if jq_sy_out.get("code", "0") == "1000":
cdt['insuranceTime'] = jq_sy_out
elif jq_sy_out.get("code", "0") == "1001" or jq_sy_out.get(
"code", "0") == "1002":
cmp_id = '|'.join(
str(i)
for i in companyId) if len(companyId) != 0 else "1|2|4|5|12|13"
send_mq(client, plateNumber, jq_sy_out["msg"], "2", cmp_id,
sessionId, isPhone, insureTypeGroupId, insuranceTypeGroup)
return
# 货车暂时只爬取人保的保费
licenseType = dt.get('licenseType', "02")
if licenseType == "01" and companyId and '12' not in companyId:
log.info(u"货车暂时只爬取人保的保费")
send_mq(client, plateNumber, "货车暂时只爬取人保的保费", "2", "2|4|5|12|13",
sessionId, isPhone, insureTypeGroupId, insuranceTypeGroup)
return
log.info(u"程序开始运行%s" % datetime.datetime.now())
try:
#get_premium(ancheng_session, cdt)
# getData_CPlateNo(requestcic,cdt)
#return
g_list = []
if not companyId:
g1 = gevent.spawn(getData_CPlateNo, requestcic, cdt)
g2 = gevent.spawn(flow_main, loginRes, cdt)
g3 = gevent.spawn(pingan_request, pingansession, cdt)
g4 = gevent.spawn(get_premium, ancheng_session, cdt)
g5 = gevent.spawn(hn_request, hn_session, cdt)
gevent.joinall([g1, g2, g3, g4, g5])
if companyId and '4' in companyId:
g1 = gevent.spawn(getData_CPlateNo, requestcic, cdt)
g_list.append(g1)
if companyId and '5' in companyId:
g2 = gevent.spawn(flow_main, loginRes, cdt)
g_list.append(g2)
if companyId and '1' in companyId:
g3 = gevent.spawn(pingan_request, pingansession, cdt)
g_list.append(g3)
if companyId and '12' in companyId:
g4 = gevent.spawn(get_premium, ancheng_session, cdt)
g_list.append(g4)
if companyId and '13' in companyId:
g5 = gevent.spawn(hn_request, hn_session, cdt)
g_list.append(g5)
if g_list:
gevent.joinall(g_list)
print u"程序结束运行%s" % datetime.datetime.now()
except Exception, e:
log.error(e)
log.error(traceback.format_exc())
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
log.info(u"程序结束运行%s" % datetime.datetime.now())
class getmessage():
def __init__(self, conrd):
self.conrd = conrd
self.log = Logger(logpath)
self.havemids = [] # 存储已经核对的messageid
#标准数据库实例化
self.conn_standard = connDB(
self.conrd.get('sMySQL', 'host'),
self.conrd.getint('sMySQL', 'port'),
self.conrd.get('sMySQL', 'user'),
self.conrd.get('sMySQL', 'passwd'),
self.conrd.get('sMySQL', 'dbname'),
self.conrd.get('sMySQL', 'charset'),
self.conrd.getint('sMySQL', 'read_timeout'))
self.conns = self.conn_standard.connect()
self.curs = self.conn_standard.getcursor(self.conns)
#实际结果数据库实例化
self.conn_reality = connDB(self.conrd.get('rMySQL', 'host'),
self.conrd.getint('rMySQL', 'port'),
self.conrd.get('rMySQL', 'user'),
self.conrd.get('rMySQL', 'passwd'),
self.conrd.get('rMySQL', 'dbname'),
self.conrd.get('rMySQL', 'charset'),
self.conrd.getint('rMySQL', 'read_timeout'))
self.connr = self.conn_reality.connect()
self.curr = self.conn_reality.getcursor(self.connr)
def result(self, sqlRlt):
result = {}
for value in sqlRlt:
result[value[0]] = value[1].split(',')
return result
#获取标准数据库中的警示信息内容
def getMessage_standard(self, sql, dbname, starttime, endtime):
try:
self.curs.execute(sql.format(dbname, starttime, endtime))
rlt = self.curs.fetchall()
return self.result(rlt)
except Exception as error:
self.log.error(error)
# 获取需验证引擎的数据库中的警示信息内容
def getMessage_reality(self, sql, dbname, starttime, endtime):
try:
self.curr.execute(sql.format(dbname, starttime, endtime))
rlt = self.curr.fetchall()
return self.result(rlt)
except Exception as error:
self.log.error(error)
#比较两个数据库中的点评数据
def compareMessage(self, message_s, message_r):
samemessage = {} #存储警示信息相同的数据
standardHave = {} #存储同一个处方或患者或就诊流水号，标准库里面有跑出警示信息，实际对照的库里面没有跑出警示信息
realityHave = {} #存储同一个处方或患者或就诊流水号，标准库里面没有跑出警示信息，实际对照的库里面有跑出警示信息
for key, message in message_s.items():
if message_r:
if key in message_r:
if message_r[key] == message_s[key]:
samemessage[key] = message
elif message_r[key] != message_s[key]:
standardHave[key] = set(message_s[key]) - set(
message_r[key])
realityHave[key] = set(message_r[key]) - set(
message_s[key])
else:
self.log.info('message_r is null')
break
return samemessage, dict(standardHave), dict(realityHave)
#获取门诊的key_date
def getoptKeyDate_s(self, dbname):
self.sql = self.conrd.get('keydate', 'optalert')
try:
self.curs.execute(self.sql.format(dbname))
self.date = self.curs.fetchall()
self.starttime, self.endtime = self.date[4], self.date[11]
return self.starttime[0], self.endtime[0]
except Exception as error:
self.log.error(error)
# 获取住院的key_date
def getiptKeyDate_s(self, dbname):
self.sql = self.conrd.get('keydate', 'iptalert')
try:
self.curs.execute(self.sql.format(dbname))
self.date = self.curs.fetchall()
self.starttime, self.endtime = self.date[4], self.date[11]
return self.starttime[0], self.endtime[0]
except Exception as error:
self.log.error(error)
# 获取未跑出的警示信息的相关内容便于排查问题
def messageContent_s(self, db_knowledge, db_dp, messageid, id, sql):
self.sql = sql
# print(self.sql.format(db_knowledge,db_dp,messageid,id))
self.curs.execute(self.sql.format(db_knowledge, db_dp, messageid, id))
sqlrlt = self.curs.fetchall()
if sqlrlt:
rlt = sqlrlt[0]
return rlt
# 获取未跑出的警示信息的相关内容便于排查问题
def messageContent_r(self, db_knowledge, db_dp, messageid, id, sql):
self.sql = sql
# print(self.sql.format(db_knowledge,db_dp,messageid,id))
self.curr.execute(self.sql.format(db_knowledge, db_dp, messageid, id))
sqlrlt = self.curr.fetchall()
if sqlrlt:
rlt = sqlrlt[0]
return rlt
# 标准库里有跑出来，实际库里未跑出来的规则统计出来
def messageContents_s(self, standardHave, db_knowledge, db_dp, sql, name):
count = 1
self.xlsx = xlsw('./result/{}_{}_s.xlsx'.format(curdate, name))
for id, messageids in standardHave.items():
if set(messageids) & set(self.havemids) == set(
messageids): #如果messageid已经在其它处方或医嘱中验证过便跳出此次循环
# print(messageids)
continue
for messageid in messageids:
rlt = self.messageContent_s(db_knowledge, db_dp, messageid, id,
sql)
if rlt:
if messageid not in self.havemids:
self.xlsx.writeData(count, rlt)
# print(rlt)
self.havemids.append(messageid)
count += 1
# if count==30:break
# if count==30:break
self.xlsx.closeXlsx()
# 实际库里有跑出来，标准库里未跑出来的规则统计出来
def messageContents_r(self, realityHave, db_knowledge, db_dp, sql, name):
print("输出实际数据库跑出来的结果")
count = 1
havemids = [] #存储已经核对的messageid
self.xlsx_r = xlsw('./result/{}_{}_r.xlsx'.format(curdate, name))
for id, messageids in realityHave.items():
if set(messageids) & set(havemids) == set(
messageids): #如果messageid已经在其它处方或医嘱中验证过便跳出此次循环
# print(messageids)
continue
for messageid in messageids:
rlt = self.messageContent_r(db_knowledge, db_dp, messageid, id,
sql)
if rlt:
if messageid not in havemids:
self.xlsx_r.writeData(count, rlt)
havemids.append(messageid)
count += 1
# if count==30:break
# if count==30:break
self.xlsx_r.closeXlsx()
#获取知识建设库与点评库的messageid,公共的方法,代替以上备注的四个方法
def messages_data(self, dbname, sqlname, field):
self.sql = self.conrd.get(field, sqlname)
self.curs.execute(self.sql.format(dbname))
messages = self.curs.fetchall()
return ([str(i[0]).strip() for i in messages])
#获取点评库中跑出警示信息的recipeid/eventno
def getdpID(self, dbname, sqlname, messageid, field):
self.sql = self.conrd.get(field, sqlname)
# print(self.sql.format(dbname,messageid))
self.curs.execute(self.sql.format(dbname, messageid))
id = self.curs.fetchall()
if id:
return id[0][0]
else:
return None
#实际数据库是否有跑出警示信息
def getdbr_messageid(self, dbnamer_dp, dbnamer_k, dpid, longid, sqlname,
field):
self.sql = self.conrd.get(field, sqlname)
# print(self.sql)
# print(self.sql.format(dbnamer_dp,dpid,dbnamer_k,longid))
self.curr.execute(self.sql.format(dbnamer_dp, dpid, dbnamer_k, longid))
id = self.curr.fetchall()
if id:
return str(id[0][0])
else:
return None
def checkmessages(self, **kwargs):
#标准知识库中所有规则的messageid
self.knowledge_messages = self.messages_data(kwargs['dbnames_k'],
kwargs['sqlnameAll'],
kwargs['field1'])
#标准点评库中门诊处方跑出来的所有messageid
self.opt_messages = self.messages_data(kwargs['dbnames_dp'],
kwargs['sqlnameOpt'],
kwargs['field1'])
#标准点评库中住院跑出来的所有messageid
self.ipt_messages = self.messages_data(kwargs['dbnames_dp'],
kwargs['sqlnameIpt'],
kwargs['field1'])
# 标准点评库中门诊患者跑出来的所有messageid
self.optp_messages = self.messages_data(kwargs['dbnames_dp'],
kwargs['sqlnameOpt_p'],
kwargs['field1'])
print(self.knowledge_messages)
passmessages = []
failmessages = []
count_i, count_optr, count_optp = 1, 1, 1
# self.xlsx = xlsw('./result/{}2.xlsx'.format(curdate))
self.xlsx_ipt = xlsw('./result/{}_ipt.xlsx'.format(curdate))
self.xlsx_optr = xlsw('./result/{}_optr.xlsx'.format(curdate))
self.xlsx_optp = xlsw('./result/{}_optp.xlsx'.format(curdate))
for messageid in self.knowledge_messages:
if messageid in self.ipt_messages:
self.id = self.getdpID(kwargs['dbnames_dp'], kwargs['eventno'],
messageid, kwargs['field2'])
self.rlt = self.getdbr_messageid(kwargs['dbnamer_dp'],
kwargs['dbnamer_k'], self.id,
messageid, kwargs['iptrlt'],
kwargs['field3'])
if self.rlt and self.rlt == messageid:
passmessages.append(messageid)
else:
self.sql = self.conrd.get("enginecontent", 'iptalert')
# print(self.id,self.sql)
result = self.messageContent(kwargs['dbnames_k'],
kwargs['dbnames_dp'],
messageid, self.id, self.sql)
# print(result)
self.xlsx_ipt.writeData(count_i, result)
count_i += 1
# if count==30:break
elif messageid in self.opt_messages:
self.id = self.getdpID(kwargs['dbnames_dp'],
kwargs['recipeid'], messageid,
kwargs['field2'])
self.rlt = self.getdbr_messageid(kwargs['dbnamer_dp'],
kwargs['dbnamer_k'], self.id,
messageid, kwargs['optrlt'],
kwargs['field3'])
if self.rlt and self.rlt == messageid:
passmessages.append(messageid)
else:
self.sql = self.conrd.get("enginecontent", 'optalert')
result = self.messageContent(kwargs['dbnames_k'],
kwargs['dbnames_dp'],
messageid, self.id, self.sql)
self.xlsx_optr.writeData(count_optr, result)
count_optr += 1
# if count==23:break
elif messageid in self.optp_messages:
self.id = self.getdpID(kwargs['dbnames_dp'],
kwargs['patinetid'], messageid,
kwargs['field2'])
self.rlt = self.getdbr_messageid(kwargs['dbnamer_dp'],
kwargs['dbnamer_k'], self.id,
messageid, kwargs['optrlt_p'],
kwargs['field3'])
if self.rlt and self.rlt == messageid:
passmessages.append(messageid)
else:
self.sql = self.conrd.get("enginecontent", 'optalert_p')
result = self.messageContent(kwargs['dbnames_k'],
kwargs['dbnames_dp'],
messageid, self.id, self.sql)
self.xlsx_optp.writeData(count_optp, result)
count_optp += 1
# if count == 10: break
else:
self.rlt = None
failmessages.append(messageid)
print(len(passmessages), passmessages)
print(len(failmessages))
self.xlsx_ipt.closeXlsx()
self.xlsx_optr.closeXlsx()
self.xlsx_optp.closeXlsx()
def get_car_model1(session, carinfo, flag=0):
licenseType = carinfo['licenseType']
colorCode = carinfo['colorCode']
netWeight = carinfo['netWeight']
enrollDate = carinfo['enrollDate']
seatCount = carinfo['seatCount']
modelName = carinfo['modelName']
exhaustScale = carinfo['exhaustScale']
licenseNo = carinfo['licenseNo']
haulage = carinfo['haulage']
engineNo = carinfo['engineNo']
vinNo = carinfo['vinNo']
vehicleStyle = carinfo['vehicleStyle']
remark = carinfo['remark']
body = {
"licenseType": licenseType,
"colorCode": colorCode,
"netWeight": netWeight,
"carKindCodeNew": "",
"modelAlias": "",
"chgOwnerFlag": "0",
"carChecker": "",
"abnormalCarFlag": "0",
"lastYearDamage": "",
"carLicenceDate": "",
"enrollDate": enrollDate,
"seatCountOld": seatCount,
"carCheckerReason": "000",
"useNatureCode": "85",
"certificateType": "",
"fuelType": "0",
"licenseNo": licenseNo,
"modelName": modelName,
"exhaustScale": exhaustScale,
"transferDate": "",
"specialCarFlag": "",
"useNatureCodeNew": "N85",
"carKindCode": "KA",
"useYears": "0",
"industryModelCode": "",
"exhaustScaleOld": exhaustScale,
"haulage": haulage,
"engineNo": engineNo,
"tradeName": "",
"certificateNo": "",
"ecdemicVehicleFlag": "0",
"runAreaName": "中华人民共和国境内(不含港澳台)",
"powerScale": "",
"fixedLine": "",
"vinNo": vinNo,
"noticeType": "",
"carKindCatalogCode": "",
"sortCode": "",
"noDamageYears": "0",
"certificateDate": "",
"carRegiste": "0",
"runAreaCode": "11",
"seatCount": seatCount,
"purchasePriceOld": "",
"isPrintModelAlias": "0",
"carName": "",
"tonCount": "",
"vehicleDamaged": "1",
"bookingTime": "",
"runMiles": "45000",
"loanVehicleFlag": "0",
"noLicenseFlag": "0",
"expecLossRatio": "",
"isNewCarFlag": "0",
"purchasePrice": "",
"licenseColorCode": "01",
"actualValue": "",
"depreciation": "0.60",
"tonCountOld": "",
"carCheckTime": "",
"vehicleStyle": vehicleStyle,
"modelCode": "",
"carCheckStatus": "1"
}
log = Logger()
url = "http://qcar.chinahuanong.com.cn/quotepriceasync/carModelInquiry.do"
headers[
'Content-Type'] = "application/x-www-form-urlencoded; charset=UTF-8"
#print(json.dumps(body, ensure_ascii=False))
body = "car=" + urllib.quote(json.dumps(body)) + "&ptCode="
# print(json.dumps(body,ensure_ascii=False))
rsp = session.post(url=url, data=body, headers=headers)
if "carModel" in rsp.text:
log.info(u"查询出车辆信息")
carModelList = rsp.json()['carModel']
# print(json.dumps(carModelList,ensure_ascii=False))
carModelList = sorted(
carModelList, key=lambda x: float(x["vehicleJingyou"]["price"]))
# print(json.dumps(carModelList, ensure_ascii=False))
# print(carModelList)
# print(carModelList[0])
carModelbody['licenseType'] = licenseType
carModelbody['colorCode'] = colorCode
carModelbody['netWeight'] = netWeight
modelAlias = carModelList[0].get("vehicleJingyou",
{}).get("vehicleAlias", "")
if modelAlias == "":
modelAlias = carModelList[0].get("vehicleJingyou",
{}).get("vehicleClassName", "")
carModelbody['modelAlias'] = modelAlias
carModelbody['enrollDate'] = enrollDate
carModelbody['seatCountOld'] = seatCount
carModelbody['seatCountOld'] = seatCount
carModelbody['modelName'] = modelName
carModelbody['licenseNo'] = licenseNo
carModelbody['exhaustScale'] = exhaustScale
carModelbody['carKindCode'] = carModelList[0]['vehicleJingyou'][
'carKindCode']
carModelbody['industryModelCode'] = carModelList[0][
'industryModelCode']
carModelbody['exhaustScaleOld'] = exhaustScale
carModelbody['modelCodePlat'] = carModelList[0]['modelCode']
carModelbody['engineNo'] = engineNo
carModelbody['tradeName'] = carModelList[0]['vehicleJingyou'][
'factoryName']
carModelbody['brand'] = carModelList[0]['vehicleJingyou']['brandName']
carModelbody['modelNamePlat'] = modelName
carModelbody['vinNo'] = vinNo
# carModelbody['carKindCatalogCode']=carKindCatalogCode
carModelbody['purchasePriceOld'] = carModelList[0]['vehicleJingyou'][
'price']
carModelbody['netWeightOld'] = carModelList[0]['vehicleJingyou'][
'fullWeight']
carModelbody['deptName'] = carModelList[0]['vehicleJingyou'][
'familyName']
carModelbody['purchasePrice'] = carModelList[0]['vehicleJingyou'][
'price']
carModelbody['modelCode'] = carModelList[0]['modelCode']
carModelbody['vehicleStyle'] = vehicleStyle
body2 = "car=" + urllib.quote(
json.dumps(carModelbody)) + "&ptCode=&startDate=" + getCuttime(
).split(" ")[0] + "+0%3A0%3A0"
# print(json.dumps(body2, ensure_ascii=False))
rsp2 = session.post(url=url, data=body2, headers=headers)
if "carModel" in rsp2.text:
carModelList2 = rsp2.json()['carModel']
carModelList2 = sorted(
carModelList2,
key=lambda x: float(x["vehicleJingyou"]["price"]))
return carModelList2[0]
else:
return carModelList[0]
else:
if flag == 0:
log.error(u"通过中华联合去查询车辆信息")
car_model = get_car_model(vinNo)
if car_model != None:
modelName = car_model['gCIndustryModelName']
carinfo['modelName'] = modelName
return get_car_model1(session, carinfo, flag=1)
else:
log.error(u"未查询到车辆信息")
return 0
class Email:
def __init__(self):
global host, user, password, port, sender, title, content
host = localReadConfig.get_email("mail_host")
user = localReadConfig.get_email("mail_user")
password = localReadConfig.get_email("mail_pass")
port = localReadConfig.get_email("mail_port")
sender = localReadConfig.get_email("sender")
title = localReadConfig.get_email("subject")
content = localReadConfig.get_email("content")
self.value = localReadConfig.get_email("receiver")
self.receiver = []
# get receiver list
for n in str(self.value).split("/"):
self.receiver.append(n)
# defined email subject
date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
self.subject = title + " " + date
self.logger = Logger("emaliUtil.py").logger
self.msg = MIMEMultipart('mixed')
def config_header(self):
self.msg['subject'] = self.subject
self.msg['from'] = sender
self.msg['to'] = ";".join(self.receiver)
def config_content(self):
content_plain = MIMEText(content, 'plain', 'utf-8')
self.msg.attach(content_plain)
def config_htmlcontent(self):
content_plain = MIMEText(content, 'html', 'utf-8')
self.msg.attach(content_plain)
def config_html(self):
proDir = os.path.split(os.path.realpath(__file__))[0]
b = proDir.split("\\")[:-1]
currentPath = "\\".join(b)
f = open(currentPath + "\\" + '/result.html', 'r', encoding='utf-8')
payload = f.read()
text_html = MIMEText(payload, 'html', 'utf-8')
text_html["Content-Disposition"] = 'attachment; filename="result.html"'
self.msg.attach(text_html)
def config_file(self):
# if the file content is not null, then config the email file
if self.check_file():
reportpath = self.log.get_result_path()
zippath = os.path.join(readConfig.proDir, "result", "test.zip")
# zip file
files = glob.glob(reportpath + '\*')
f = zipfile.ZipFile(zippath, 'w', zipfile.ZIP_DEFLATED)
for file in files:
f.write(file)
f.close()
reportfile = open(zippath, 'rb').read()
filehtml = MIMEText(reportfile, 'base64', 'utf-8')
filehtml['Content-Type'] = 'application/octet-stream'
filehtml['Content-Disposition'] = 'attachment; filename="test.zip"'
self.msg.attach(filehtml)
def check_file(self):
reportpath = self.log.get_report_path()
if os.path.isfile(reportpath) and not os.stat(reportpath) == 0:
return True
else:
return False
def send_email(self):
self.config_header()
# self.config_content()
self.config_html()
self.config_htmlcontent()
# self.config_file()
try:
smtp = smtplib.SMTP()
smtp.connect(host)
smtp.login(user, password)
smtp.sendmail(sender, self.receiver, self.msg.as_string())
smtp.quit()
self.logger.info("The test report has send to developer by email.")
except Exception as ex:
self.logger.error(str(ex))
def pingan_request(session, renewal_data_dt):
'''
:param session:
:param dt:
:return:
'''
log = Logger()
try:
alldata = {}
# insureCarId = renewal_data_dt.get('insureCarId', '')
CPlateNo = renewal_data_dt.get('plateNumber', '')
searchVin = renewal_data_dt.get('vinNo', '')
client = renewal_data_dt.get('client', '')
isPhone = renewal_data_dt['isPhone']
sessionId = renewal_data_dt.get('sessionId', '')
#endDate = renewal_data_dt.get('endDate', '')
insuranceType = renewal_data_dt.get("insuranceType", {})
if isinstance(insuranceType, list):
insureTypeGroupId = insuranceType[0].get("insuranceTypeGroupId",
"")
insuranceTypeGroup = insuranceType[0].get("insuranceTypeGroup", "")
else:
insureTypeGroupId = insuranceType.get("insuranceTypeGroupId", "")
insuranceTypeGroup = insuranceType.get("insuranceTypeGroup", "")
'''
if endDate != "":
min_time = compare_time40(endDate)
if min_time > config.days:
log.error("平安保险查询在40天内的 保险结束时间为 %s", endDate)
send_mq(client, CPlateNo, "平安保险查询在40天内的 保险结束时间为 %s" % endDate, "2", "1", sessionId, isPhone,
insureTypeGroupId, insuranceTypeGroup)
return "平安保险查询在40天内的 保险结束时间为 %s" % endDate
if min_time < 0:
endDate = getlatedate(1)
'''
if not isinstance(insuranceType, list):
# 使用redis缓存进行查询
re_out = get_pingan_redis_body(session, renewal_data_dt)
if re_out == 1:
return
if '-' not in CPlateNo:
plateNumber = CPlateNo[:2] + "-" + CPlateNo[2:]
if session == '' or session is None:
login = Login()
session = login.login()
# 判断是否为续保用户 直接从续保信息中取用户信息 然后交换域名跳转的session
user_data = is_renewal(session, plateNumber=plateNumber)
if isinstance(user_data, dict):
sy_insuranceEndTime = user_data.get("sy_insuranceEndTime", "")
jq_insuranceEndTime = user_data.get("jq_insuranceEndTime", "")
if sy_insuranceEndTime == "" and jq_insuranceEndTime != "":
sy_insuranceEndTime = jq_insuranceEndTime
if sy_insuranceEndTime != "" and jq_insuranceEndTime == "":
jq_insuranceEndTime = sy_insuranceEndTime
icorepnbs_session = user_data['request']
quick_resp = quickSearchVoucher(icorepnbs_session, user_data)
# 获取车管所信息
session = quick_resp['request']
vehicleFrameNo = quick_resp['vehicleTarget']['vehicleFrameNo']
queryDMVehicleInfoConfirm_json = queryDMVehicleInfoConfirm(
session, vehicleFrameNo, carMark="")
if not isinstance(queryDMVehicleInfoConfirm_json, dict):
send_mq(client, CPlateNo, queryDMVehicleInfoConfirm_json, "2",
"1", sessionId, isPhone, insureTypeGroupId,
insuranceTypeGroup)
return
# 查询车辆具体型号
model = query_carmodel(session, vehicleFrameNo)
if model == 0:
model = queryAutoModelType(
session, queryDMVehicleInfoConfirm_json['vehicleModel'])
# 型号最后一位小写
if model == 0:
model = queryAutoModelType(
session,
queryDMVehicleInfoConfirm_json['vehicleModel'][0:-1] +
queryDMVehicleInfoConfirm_json['vehicleModel'][-1].lower())
log.info(model)
alldata['sy_insuranceEndTime'] = sy_insuranceEndTime
alldata['jq_insuranceEndTime'] = jq_insuranceEndTime
alldata['insurantInfo'] = quick_resp['insurantInfo']
alldata['vehicleTarget'] = quick_resp['vehicleTarget']
alldata['autoModelType'] = quick_resp['autoModelType']
alldata['DMVehicleInfo'] = queryDMVehicleInfoConfirm_json
alldata['model'] = model
alldata["insuranceType"] = insuranceType
if isinstance(insuranceType, list):
for itype in insuranceType:
alldata['insuranceType'] = itype
fee_resp = get_fee(session, alldata)
_result_insert(fee_resp, renewal_data_dt)
else:
fee_resp = get_fee(session, alldata)
_result_insert(fee_resp, renewal_data_dt)
else:
if searchVin != "":
sy_insuranceEndTime = renewal_data_dt.get("insuranceTime",
{}).get(
"syStart",
getlatedate(1))
jq_insuranceEndTime = renewal_data_dt.get("insuranceTime",
{}).get(
"jqStart",
getlatedate(1))
session = user_data
vehicleFrameNo = searchVin
# 使用redis缓存进行查询
re_out = get_pingan_redis_body(session, renewal_data_dt)
if re_out == 1:
return
# 请求车管所
queryDMVehicleInfoConfirm_json = queryDMVehicleInfoConfirm(
session, vehicleFrameNo, carMark="")
# log.info(json.dumps(queryDMVehicleInfoConfirm_json,ensure_ascii=False))
if not isinstance(queryDMVehicleInfoConfirm_json, dict):
send_mq(client, CPlateNo, queryDMVehicleInfoConfirm_json,
"2", "1", sessionId, isPhone, insureTypeGroupId,
insuranceTypeGroup)
return
# 通过车架号查询车辆类型
model = query_carmodel(session, vehicleFrameNo)
# 通过车辆编码查询车辆类型
if model == 0:
model = queryAutoModelType(
session,
queryDMVehicleInfoConfirm_json['vehicleModel'])
# 型号最后一位小写
if model == 0:
log.info(u"通过最后一位字母小写查询")
model = queryAutoModelType(
session,
queryDMVehicleInfoConfirm_json['vehicleModel'][0:-1] +
queryDMVehicleInfoConfirm_json['vehicleModel']
[-1].lower())
# log.info(model)
# 编码查询不到使用续保信息中的车辆名字进行查询
if model == 0 and renewal_data_dt.get("vehicleBrand",
"") != "":
model = queryAutoModelType(session,
renewal_data_dt['vehicleBrand'])
alldata['DMVehicleInfo'] = queryDMVehicleInfoConfirm_json
alldata['model'] = model
alldata['sy_insuranceEndTime'] = sy_insuranceEndTime
alldata['jq_insuranceEndTime'] = jq_insuranceEndTime
alldata["insuranceType"] = insuranceType
if isinstance(insuranceType, list):
for itype in insuranceType:
alldata['insuranceType'] = itype
fee_resp = get_fee(session, alldata)
_result_insert(fee_resp, renewal_data_dt)
else:
fee_resp = get_fee(session, alldata)
_result_insert(fee_resp, renewal_data_dt)
except Exception, e:
log.error(e)
log.error(traceback.format_exc())
send_mq(client, CPlateNo, "%s 未知错误" % CPlateNo, "2", "1", sessionId,
isPhone, insureTypeGroupId, insuranceTypeGroup)
class Request:
def __init__(self, host):
self.host = host
self.Log = Logger()
def request(self, url, data, type):
url = self.host + url
if type.upper() == "GET":
try:
self.Log.info("正在进GET：【%s】接口" % url)
req = requests.get(url, params=data)
result = req.json()
status_code = req.status_code
except Exception as e:
self.Log.error(e)
else:
if status_code == 200:
self.Log.info("请求完成【status:%s】" % status_code)
else:
self.Log.error("请求出错【status:%s】" % status_code)
return result
elif type.upper() == "POST":
try:
self.Log.info("正在进POST：【%s】接口" % url)
req = requests.post(url, data=data)
result = req.json()
status_code = req(url, data)
except Exception as e:
self.Log.error(e)
else:
if status_code == 200:
self.Log.info("请求完成【status:%s】" % status_code)
else:
self.Log.error("请求出错【status:%s】" % status_code)
return result
else:
self.Log.error("请求类型错误")
self.Log.close()
def requests(self, url, data, type):
url = self.host + url
if type.upper() == "GET":
try:
self.Log.info("正在进GET：【%s】接口" % url)
req = requests.get(url, params=data)
result = req.text
status_code = req.status_code
except Exception as e:
self.Log.error(e)
else:
if status_code == 200:
self.Log.info("请求完成【status:%s】" % status_code)
else:
self.Log.error("请求出错【status:%s】" % status_code)
return result
elif type.upper() == "POST":
try:
self.Log.info("正在进POST：【%s】接口" % url)
result = requests.post(url, data=data).json()
status_code = requests.post(url, data)
except Exception as e:
self.Log.error(e)
else:
if status_code == 200:
self.Log.info("请求完成【status:%s】" % status_code)
else:
self.Log.error("请求出错【status:%s】" % status_code)
return result
else:
self.Log.error("请求类型错误")
self.Log.close()
def hn_request(session, renewal_data_dt):
log = Logger()
try:
alldata = {}
insureCarId = renewal_data_dt.get('insureCarId', '')
CPlateNo = renewal_data_dt.get('plateNumber', '')
searchVin = renewal_data_dt.get('vinNo', '')
client = renewal_data_dt.get('client', '')
isPhone = renewal_data_dt['isPhone']
sessionId = renewal_data_dt.get('sessionId', '')
endDate = renewal_data_dt.get('endDate', '')
insuranceType = renewal_data_dt.get("insuranceType", {})
if isinstance(insuranceType, list):
insureTypeGroupId = insuranceType[0].get("insuranceTypeGroupId", "")
insuranceTypeGroup = insuranceType[0].get("insuranceTypeGroup", "")
else:
insureTypeGroupId = insuranceType.get("insuranceTypeGroupId", "")
insuranceTypeGroup = insuranceType.get("insuranceTypeGroup", "")
redisbody = r.get_vin(searchVin, "13")
if redisbody != None:
log.info(u"华农从缓存中查询出信息%s" , searchVin)
try:
redisbody = json.loads(redisbody)
kinds = get_hn_insurance_type(insuranceType, float(redisbody['car']['actualValueOld']),
redisbody['vehicleJingyou']['seat'],
redisbody['car']['purchasePriceOld'])
redisbody['kinds'] = kinds
if insuranceType.get("compulsoryInsurance","1") == "0":
redisbody["tax"]={}
premium = get_hn_premium(session, body_org=redisbody)
_result_insert(premium, renewal_data_dt)
return
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
'''
if endDate != "":
min_time = compare_time40(endDate)
if min_time > config.days:
log.error("华农保险查询在40天内的 保险结束时间为 %s" % endDate)
send_mq(client, CPlateNo, "华农保险查询在40天内的 保险结束时间为 %s" % endDate, "2", "13", sessionId, isPhone,
insureTypeGroupId, insuranceTypeGroup)
return "华农保险查询在40天内的 保险结束时间为 %s" % endDate
'''
# 获取车管所信息
carInfo = get_carInof(session, searchVin)
if not isinstance(carInfo, dict):
log.error(u"获取车管所信息失败")
send_mq(client, CPlateNo, "%s 获取车管所信息失败" % CPlateNo, "2", "13", sessionId, isPhone, insureTypeGroupId,
insuranceTypeGroup)
return
# 获取车辆型号信息
vehicleJingyou = get_car_model1(session, carInfo)
if not isinstance(vehicleJingyou, dict):
log.error(u"获取车辆信息失败")
send_mq(client, CPlateNo, "%s 获取车辆信息失败" % CPlateNo, "2", "13", sessionId, isPhone, insureTypeGroupId,
insuranceTypeGroup)
return
alldata = {}
alldata['car'] = carInfo
alldata['vehicleJingyou'] = vehicleJingyou['vehicleJingyou']
vehicleJingyou.pop('vehicleJingyou')
alldata['other'] = vehicleJingyou
alldata['carInfo'] = renewal_data_dt
premium = get_hn_premium(session, alldata=alldata)
_result_insert(premium, renewal_data_dt)
except Exception as e:
log.error(e)
log.error(traceback.format_exc())
send_mq(client, CPlateNo, "%s 未知错误" % CPlateNo, "2", "13", sessionId, isPhone, insureTypeGroupId,
insuranceTypeGroup)
