Files
airflow/dags/api/dag_njcb_village_statement.py

209 lines
7.6 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# -*- coding: utf-8 -*-
import os
import datetime
import calendar
import json
import pandas as pd
from goojia_utils import LOG_MONGO_CLIENT
from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.utils.email import send_email
import pendulum
def get_last_month_range(ref_date: datetime.date = None):
"""
获取参考日期的上个月第一天 00:00:00 与最后一天 23:59:59.999999
以北京时间为准
"""
if ref_date is None:
ref_date = datetime.date.today()
first_day_current_month = ref_date.replace(day=1)
last_day_last_month = first_day_current_month - datetime.timedelta(days=1)
first_day_last_month = last_day_last_month.replace(day=1)
start_time = datetime.datetime.combine(first_day_last_month, datetime.time.min)
end_time = datetime.datetime.combine(last_day_last_month, datetime.time.max)
return start_time, end_time, first_day_last_month.year, first_day_last_month.month
def parse_dict(val):
"""将字符串类型的 JSON 或字典解析为 dict"""
if isinstance(val, dict):
return val
if isinstance(val, str) and val.strip():
try:
return json.loads(val)
except Exception:
return {}
return {}
# Excel 中文表头定义:区分【输入】和【输出】
EXCEL_COLUMNS = [
'请求时间',
# 输入参数
'输入-城市',
'输入-地址',
# 响应参数 (取自 res_map.result)
'输出-省份',
'输出-省份编码',
'输出-城市名称',
'输出-城市编码',
'输出-区域',
'输出-区域编码',
'输出-乡镇街道',
'输出-乡镇街道编码',
'输出-是否涉农',
'输出-所属区域',
'输出-地址验证',
'输出-是否为县级市',
'输出-是否县城城区',
'输出-是否为省国家级开发区',
'输出-省国家级开发区名称',
'输出-开发区等级'
]
def export_and_send_njcb_statement(**context):
"""
查询上月对公涉农API日志,生成全中文表头Excel对账单并通过邮件发送给顾老师
"""
now = datetime.datetime.now()
start_time, end_time, last_year, last_month = get_last_month_range(now.date())
print(f"统计时间范围: {start_time} 至 {end_time} (归属年月: {last_year}年{last_month:02d}月)")
open_uri_log_conn = LOG_MONGO_CLIENT.get_collection('business', 'open_uri_log')
# 查询条件:
# user_id = 1007801, request_uri = /addrSvc/njcbVillageIdentify, c_date在时间范围内, 且只输出code是200的响应
query = {
'user_id': '1007801',
'request_uri': '/addrSvc/njcbVillageIdentify',
'c_date': {
'$gte': start_time,
'$lte': end_time
},
'$or': [
{'code': 200},
{'code': '200'}
]
}
print(f"开始查询 MongoDB business.open_uri_log,条件: {query}")
cursor = open_uri_log_conn.find(query).batch_size(2000)
rows = []
scanned_count = 0
for doc in cursor:
scanned_count += 1
param_map = parse_dict(doc.get('parameter_map'))
res_map = parse_dict(doc.get('res_map'))
# res_map 结构中有 code, msg, result 嵌套
# 实际业务返回字段在 res_map['result'] 中,若无 result 则回退直接使用 res_map
result_map = parse_dict(res_map.get('result')) if res_map.get('result') else res_map
# 过滤只保留"对公涉农"的请求
# 涉农类型: 1 对私,2 对公 (默认为对私)
req_type = str(param_map.get('type', '1')).strip()
if req_type != '2':
continue
# 仅输出"输出-是否涉农"为"农"的部分
verify_flag = str(result_map.get('verifyFlag', '')).strip()
if verify_flag != '农':
continue
c_date = doc.get('c_date')
if isinstance(c_date, datetime.datetime):
c_date_str = c_date.strftime('%Y-%m-%d %H:%M:%S')
else:
c_date_str = str(c_date) if c_date is not None else ''
row = {
'请求时间': c_date_str,
# 输入参数
'输入-城市': param_map.get('city', ''),
'输入-地址': param_map.get('address', ''),
# 响应参数 (取自 result_map)
'输出-省份': result_map.get('province', ''),
'输出-省份编码': result_map.get('provinceCode', ''),
'输出-城市名称': result_map.get('city', ''),
'输出-城市编码': result_map.get('cityCode', ''),
'输出-区域': result_map.get('district', ''),
'输出-区域编码': result_map.get('districtCode', ''),
'输出-乡镇街道': result_map.get('town', ''),
'输出-乡镇街道编码': result_map.get('townCode', ''),
'输出-是否涉农': verify_flag,
'输出-所属区域': result_map.get('addrArea', ''),
'输出-地址验证': result_map.get('addrVerify', ''),
'输出-是否为县级市': result_map.get('countyLevelCity', ''),
'输出-是否县城城区': result_map.get('countyGov', ''),
'输出-是否为省国家级开发区': result_map.get('devDistrict', ''),
'输出-省国家级开发区名称': result_map.get('devDistrictName', ''),
'输出-开发区等级': result_map.get('devLevel', '')
}
rows.append(row)
total_count = len(rows)
print(f"扫描命中记录: {scanned_count} 条,其中符合对公且是否涉农为'农'的记录: {total_count} 条")
# 生成 Excel 文件
tmp_dir = os.path.join(os.path.expanduser('~'), 'tmp', 'njcb_statement')
os.makedirs(tmp_dir, exist_ok=True)
filename = f"南京银行涉农API对公涉农调用流水_{last_year}{last_month:02d}.xlsx"
excel_path = os.path.join(tmp_dir, filename)
if rows:
df = pd.DataFrame(rows, columns=EXCEL_COLUMNS)
else:
df = pd.DataFrame(columns=EXCEL_COLUMNS)
df.to_excel(excel_path, index=False, engine='openpyxl')
print(f"Excel文件生成成功: {excel_path}")
# 发送邮件
receiver = 'guyzh@njcb.com.cn'
cc_receiver = ['wangwenyan@fangjia.com', 'daijinshui@fangjia.com']
subject = f"南京银行{last_year}年{last_month:02d}月涉农API对公涉农调用流水"
html_content = f"""
<h3>顾老师:</h3>
<p>您好!</p>
<p>附件为南京银行 <b>{last_year}年{last_month:02d}月</b> 涉农识别API(/addrSvc/njcbVillageIdentify)对公涉农调用流水,请查收。</p>
<ul>
<li><b>对账周期:</b> {start_time.strftime('%Y-%m-%d')} 至 {end_time.strftime('%Y-%m-%d')}</li>
<li><b>调用有效成功条数(Code=200 且 对公且判定为农):</b> {total_count} 条</li>
</ul>
<p>如有任何疑问,请随时与我们联系。</p>
"""
print(f"正在发送邮件至: {receiver}, 抄送: {cc_receiver}, 附件: {excel_path}")
send_email(
to=receiver,
subject=subject,
html_content=html_content,
files=[excel_path],
cc=cc_receiver
)
print("邮件发送完成。")
# Airflow DAG 定义:每月2号上午9:00执行(北京时间)
with DAG(
dag_id="njcb_village_identify_statement_dag",
schedule="0 9 2 * *",
start_date=pendulum.datetime(2024, 1, 1, tz="Asia/Shanghai"),
catchup=False,
description="每月2号给南京银行顾老师发送上个月涉农API对公涉农调用流水",
tags=["api", "njcb", "statement"]
) as dag:
send_statement_task = PythonOperator(
task_id="send_njcb_village_statement",
python_callable=export_and_send_njcb_statement
)