read_pdf_plt/parseCreditPdf.py0602

#coding=utf-8
import shutil
import pdfplumber
import pandas as pd
import numpy as np;
import sys
import os
import traceback
from prp import PrpCrypt
#指标相关
import loanIndexParser as lip;
import payRcdIndexParser as prp;
import creditCardIndexParser as cip
import queryInfoIndexParser as qip
import requests
import utils;
import time;
import consts;
import math
import dfParser;
import gc
from dbController import DbController
from ini_op import Config;
base_dir = os.path.dirname(os.path.abspath(__file__))
config = Config(base_dir+"/config.ini");
#连接数据库
dbController = DbController();
pd.set_option('mode.chained_assignment', None)
import log
logger = log.logger

# 查询信息
dfMap = {};
allHeaders = []  # 所有表头
queryInfoDf = pd.DataFrame();
queryInfoDf_header = ["被查询者姓名", "被查询者证件类型", "被查询者证件号码", "查询机构", "查询原因"];
dfMap["queryInfoDf"] = {"df": queryInfoDf, "nextDf": None};
allHeaders.append(queryInfoDf_header);

# 身份信息
identityDf = pd.DataFrame();
identity_header = ['性别', '出生日期', '婚姻状况', '学历', '学位', '就业状况', '国籍', '电子邮箱']
addressDf = pd.DataFrame();  # 通讯地址
dfMap["identityDf"] = {"df": identityDf, "nextDf": None, "mobiles": None};
allHeaders.append(identity_header);

# 配偶信息
mateDf = pd.DataFrame();
mateDf_header = ['姓名', '证件类型', '证件号码', '工作单位', '联系电话']
dfMap["mateDf"] = {"df": mateDf, "nextDf": None};
allHeaders.append(mateDf_header);

# 居住信息====暂时该信息没有用到先不解析
liveInfoDf = pd.DataFrame();
liveInfoDf_header = ['编号', '居住地址', '住宅电话', '居住状况', '信息更新日期']
dfMap["liveInfoDf"] = {"df": liveInfoDf, "nextDf": None};
allHeaders.append(liveInfoDf_header);

# 职业信息
occupationDf = pd.DataFrame();
occupationInfo_header = ['编号', '工作单位', '单位性质', '单位地址', '单位电话']
occupationInfo_header1 = ['编号', '职业', '行业',  '职务', '职称', '进入本单位年份', '信息更新日期']
dfMap["occupationDf"] = ({"df": occupationDf, "nextDf": None});
# allHeaders.append(occupationInfo_header1);
allHeaders.append(occupationInfo_header);
# 上次查询记录
preQueryRcd_header0 = ['上一次查询记录']
allHeaders.append(preQueryRcd_header0);
# 查询记录概要
# queryInfoBriefDf = pd.DataFrame();
# queryInfoBrief_header0 = ['最近1个月内的查询机构数', '最近1个月内的查询次数', '最近2年内的查询次数']
# queryInfoBrief_header1 = ['贷款审批', '信用卡审批', '贷款审批', '信用卡\n审批', '本人查询', '贷后管理', '担保资格\n审查', '特约商户\n实名审查']
# dfMap["queryInfoBriefDf"] = ({"df": queryInfoBriefDf, "nextDf": None});
# allHeaders.append(queryInfoBrief_header0);
# allHeaders.append(queryInfoBrief_header1);

# 信贷交易信息提示
loanTradeInfoDf = pd.DataFrame();
loanTradeInfo_header = ['业务类型', '账户数', '首笔业务发放月份'];
dfMap["loanTradeInfoDf"] = ({"df": loanTradeInfoDf, "nextDf": None});
allHeaders.append(loanTradeInfo_header)
# 信贷交易违约信息概要
# 被追偿信息汇总 资产处置和垫款业务
recoveryInfoSumDf = pd.DataFrame();
recoveryInfoSumDf_header = ['业务种类', '账户数', '余额'];
dfMap["recoveryInfoSumDf"] = ({"df": recoveryInfoSumDf, "nextDf": None});
allHeaders.append(recoveryInfoSumDf_header)

# 呆账信息汇总
badDebtsInfoSumDf = pd.DataFrame();
badDebtsInfoSumDf_header = ['账户数', '余额'];  # 被追偿信息汇总
dfMap["badDebtsInfoSumDf"] = ({"df": badDebtsInfoSumDf, "nextDf": None});
allHeaders.append(badDebtsInfoSumDf_header)

# 逾期透资信息汇总
overdueInfoSumDf = pd.DataFrame();
overdueInfoSumDf_header = ['账户类型', '账户数', '月份数', '单月最高逾期/透支总额', '最长逾期/透支月数']
dfMap["overdueInfoSumDf"] = ({"df": overdueInfoSumDf, "nextDf": None});
allHeaders.append(overdueInfoSumDf_header)

# 非循环贷账户信息汇总
loanAccountInfoSumDf = pd.DataFrame();
loanAccountInfoSumDf_header0 = ['非循环贷账户信息汇总']
loanAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款']
dfMap["loanAccountInfoSumDf"] = ({"df": loanAccountInfoSumDf, "nextDf": None});
allHeaders.append(loanAccountInfoSumDf_header0)
allHeaders.append(loanAccountInfoSumDf_header1)

# 循环额度下分账户信息汇总
cycleCreditAccountInfoSumDf = pd.DataFrame();
cycleCreditAccountInfoSumDf_header0 = ['循环额度下分账户信息汇总']
cycleCreditAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款'],
dfMap["cycleCreditAccountInfoSumDf"] = ({"df": cycleCreditAccountInfoSumDf, "nextDf": None});
allHeaders.append(cycleCreditAccountInfoSumDf_header0)
allHeaders.append(cycleCreditAccountInfoSumDf_header1)

# 循环贷账户信息汇总
cycleLoanAccountInfoSumDf = pd.DataFrame();
cycleLoanAccountInfoSumDf_header0 = ['循环贷账户信息汇总']
cycleLoanAccountInfoSumDf_header1 = ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款']
dfMap["cycleLoanAccountInfoSumDf"] = ({"df": cycleLoanAccountInfoSumDf, "nextDf": None});
allHeaders.append(cycleLoanAccountInfoSumDf_header0)
allHeaders.append(cycleLoanAccountInfoSumDf_header1)

# 贷记卡账户信息汇总
creditCardInfoSumDf = pd.DataFrame();
creditCardInfoSumDf_header0 = ['贷记卡账户信息汇总']
creditCardInfoSumDf_header1 = ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '已用额度', '最近6个月平\n均使用额度']
dfMap["creditCardInfoSumDf"] = ({"df": creditCardInfoSumDf, "nextDf": None});
allHeaders.append(creditCardInfoSumDf_header0)
allHeaders.append(creditCardInfoSumDf_header1)

# 准贷记卡账户信息汇总
creditCardInfoSumDfZ = pd.DataFrame();
creditCardInfoSumDfZ_header0 = ['准贷记卡账户信息汇总']#'准贷记卡账户信息汇总'
creditCardInfoSumDfZ_header1 = ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '透支余额', '最近6个月平\n均透支余额']
dfMap["creditCardInfoSumDfZ"] = ({"df": creditCardInfoSumDfZ, "nextDf": None});
allHeaders.append(creditCardInfoSumDfZ_header0)
allHeaders.append(creditCardInfoSumDfZ_header1)

#公共信息概要
publicInfoBriefDf = pd.DataFrame();
publicInfoBriefDf_header0 = ['公共信息汇总']
dfMap["publicInfoBriefDf"] = ({"df": publicInfoBriefDf, "nextDf": None});
allHeaders.append(publicInfoBriefDf_header0)

#查询记录汇总
queryRecordSumDf_header0=['最近1个月内的查询机构数', '最近1个月内的查询次数', '最近2年内的查询次数']
queryRecordSumDf = pd.DataFrame();
dfMap["queryRecordSumDf"] = ({"df": queryRecordSumDf, "nextDf": None});
allHeaders.append(queryRecordSumDf_header0)

# 非循环贷账户，循环额度下分账户
# 循环贷账户
loan_header = ['管理机构', '账户标识', '开立日期', '到期日期', '借款金额', '账户币种']
loanDfs = [];
dfMap["loanDfs"] = ({"dfs": loanDfs, "nextDf": []});
allHeaders.append(loan_header)
# 贷记卡账户
creditCard_header = ['发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '业务种类', '担保方式']
creditCardDfs = [];
dfMap["creditCardDfs"] = ({"dfs": creditCardDfs, "nextDf": []});
allHeaders.append(creditCard_header)

# 准备贷记卡账户
creditCardZ_header = ['发卡机构', '账户标识', '开立日期', '账户授信额度', '共享授信额度', '币种', '担保方式']
creditCardDfsZ = [];
dfMap["creditCardDfsZ"] = ({"dfs": creditCardDfsZ, "nextDf": []});
allHeaders.append(creditCardZ_header)
#
# 相关还款责任信息汇总 未使用到

# 信贷交易信息明细
# 被追偿信息 未使用到
recoveryInfoDfs_header = ['管理机构','业务种类','债权接收日期','债权金额','债权转移时的还款状态']
recoveryInfoDfs = [];
dfMap["recoveryInfoDfs"] = ({"dfs": recoveryInfoDfs, "nextDf": []});
allHeaders.append(recoveryInfoDfs_header)
# 公共信息明细
# 强制执行记录
forceExecRcdDfs_header = ['编号', '执行法院', '执行案由', '立案日期', '结案方式']
forceExecRcdDfs = [];
dfMap["forceExecRcdDfs"] = ({"dfs": forceExecRcdDfs, "nextDf": []});
allHeaders.append(forceExecRcdDfs_header)

# 查询记录
queryRecordDetailDf_header = ['编号', '查询日期', '查询机构', '查询原因']
dfMap["queryRecordDetailDf"] = ({"df": pd.DataFrame(), "nextDf": []});
allHeaders.append(queryRecordDetailDf_header)

#住房公积金参缴记录
housingFundRcdDfs_header =['参缴地', '参缴日期', '初缴月份', '缴至月份', '缴费状态', '月缴存额', '个人缴存比例', '单位缴存比例']
housingFundRcdDfs = []
dfMap["housingFundRcdDfs"] = ({"dfs": housingFundRcdDfs, "nextDf": []});
allHeaders.append(housingFundRcdDfs_header)

repaymentSumDf_header0=['相关还款责任信息汇总']
dfMap["repaymentSumDf"] = ({"df": pd.DataFrame(), "nextDf": None});
allHeaders.append(repaymentSumDf_header0)
# 处理分页思路
# df估计得放到对象里面,然后存储下一个df，一个对象里包含key
# 然后判断对象的df的完整性，如果不完整代表被分页了，把nextdf合并到当前的df
# 针对可合并的列的场景

# =======
keyList = []  # 存储所有的df的key列表
# pd.Series()

# 检查数据是否带表头
# 应该是每一页开头的一行和每个表头对比一次，确认是不是表头，或者表头有什么共同的规律也可以看下
import timeit



# 定义指标部分======================start
reportTime = "";  # 报告时间
# 被查询者姓名
queryInfoName = "";
queryInfoCardId = ""  # 被查询者证件号码
# 定义指标部分======================end
# 被查询信息-基础信息
# 报告时间
# 被查询者姓名
# 被查询者证件号码

# 基础信息
queryInfo = {"reportTime":"","queryInfoCardId":""}
# 身份信息
identity = {}
# 配偶信息
mate = {}
# 信贷交易信息提示-信用提示
loanTradeInfo = {'perHouseLoanAccount': 0, 'perBusHouseLoanAccount': 0, 'otherLoanAccount': 0, 'loanMonthMin': 0,
                 'creditCardMonthMin': 0, 'creditAccount': 0, 'creditAccountZ': 0}

# 逾期及违约信息概要
overdueBrief = {}

# 逾期及透资信息汇总
# 贷款逾期账户数 loanOverdueAccount
# 贷款逾期月份数 loanOverdueMonth
# 贷款单月最高逾期总额 loanCurMonthOverdueMaxTotal
# 贷款最长逾期月数 loanMaxOverdueMonth
overdueInfo = {"loanOverdueAccount": "", "loanOverdueMonth": "", "loanCurMonthOverdueMaxTotal": "",
               "loanMaxOverdueMonth": "",
               "creditCardOverdueAccount": "", "creditCardOverdueMonth": "", "creditCardCurMonthOverdueMaxTotal": "",
               "creditCardMaxOverdueMonth": ""}

# 未结清贷款信息汇总
# ['管理机构数', '账户数', '授信总额', '余额', '最近6个月平均应还款']
loanAccountInfoSum = {"mgrOrgCount": 0, "account": 0, "creditTotalAmt": 0, "balance": 0, "last6AvgPayAmt": 0}
# 未销户贷记卡发卡法人机构数
# 未销户贷记卡发卡机构数
# 未销户贷记卡账户数
# 未销户贷记卡授信总额
# 未销户贷记卡单家行最高授信额
# 未销户贷记卡单家行最低授信额
# 未销户贷记卡已用额度
# 未销户贷记卡近6月平均使用额度
# 未结清贷记卡信息汇总
# ['发卡机构数', '账户数', '授信总额', '单家机构最高\n授信额', '单家机构最低\n授信额', '已用额度', '最近6个月平\n均使用额度']
creditCardInfoSum = {"awardOrgCount": 0, "account": 0, "creditTotalAmt": 0, "perMaxCreditTotalAmt": 0,
                     "perMinCreditTotalAmt": 0, "useAmt": 0, "last6AvgUseAmt": 0}

# 信 贷 审 批 查 询 记 录 明 细
queryRecordDetail = {"last1MonthQueryTimes": 0, "last3MothLoanApproveTimes": 0, "last3MonthQueryTimes": 0,
                   "lastTimeLoanApproveMonth": 0}

#最近一笔结清贷款的贷款金额 
loanAccountInfo = {"lastSettleLoanAmt": 0}
loanAccountDfs=[];#横向合并

creditCardAccountDfs=[];#贷记卡账户合并

creditCardAccountDfsZ=[];#准贷记卡账户合并

recoveryInfoAccountDfs=[];#被追偿账户合并

housingFundRcdAccountDfs=[];#公积金账户合并
#============================指标定义区 start=============================
#基本信息 拆分
# basicInfoDf = pd.DataFrame(columns=consts.basicInfoHeader, index=[0])
#身份信息
identityInfoIndex = '身份信息'
identityInfoDf = pd.DataFrame(columns=consts.identityInfoHeader,index=[identityInfoIndex])
#配偶信息
mateInfoIndex = '配偶信息'
mateInfoDf = pd.DataFrame(columns=consts.mateInfoHeader,index=[mateInfoIndex])
#居住信息
liveInfoIndex = '居住信息'
liveInfoDf = pd.DataFrame(columns=consts.liveInfoHeader,index=[liveInfoIndex])
#职业信息
occupationInfoIndex = '职业信息'
occupationInfoDf = pd.DataFrame(columns=consts.occupationInfoHeader,index=[occupationInfoIndex])

#信贷交易信息提示
loanTradeInfoIndex = '信贷交易信息提示'
briefInfoDf_loanTradeInfo =  pd.DataFrame(columns=consts.briefInfoHeader_loanTradeInfo,index=[loanTradeInfoIndex])
#被追偿信息汇总及呆账信息汇总
recoveryInfoSumIndex = '信贷交易违约信息概要'
briefInfoDf_recoveryInfoSum =  pd.DataFrame(columns=consts.briefInfoHeader_recoveryInfo,index=[recoveryInfoSumIndex])

#呆账信息汇总
badDebtsInfoIndex = '呆账信息汇总'
briefInfoDf_badDebtsInfoSum =  pd.DataFrame(columns=consts.briefInfoHeader_badDebtsInfoSum,index=[badDebtsInfoIndex])


#逾期（透支）信息汇总
overdueInfoSumIndex='逾期（透支）信息汇总'
briefInfoDf_overdueInfoSum =  pd.DataFrame(columns=consts.briefInfoHeader_overdueInfoSum,index=[overdueInfoSumIndex])
#信贷交易授信及负债信息概要
loanTradeCreditInfoIndex='信贷交易授信及负债信息概要'
briefInfoDf_loanTradeCreditInfo =  pd.DataFrame(columns=consts.briefInfoHeader_loanTradeCreditInfo,index=[loanTradeCreditInfoIndex]).fillna(0.0)
#公共信息概要
publicInfoBriefIndex = '公共信息概要'
publicInfoBriefDf =  pd.DataFrame(columns=consts.publicInfoBriefHeader,index=[publicInfoBriefIndex])

#查询记录汇总
queryRecordSumIndex = '查询记录汇总'
queryRecordSumDf =  pd.DataFrame(columns=consts.queryRecordSumHeader,index=[queryRecordSumIndex])

#信贷交易明细-被追偿信息
recoveryInfoIndex='被追偿信息'
creditTradeDetailDf_recoveryInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_recoveryInfo,index=[recoveryInfoIndex])

#信贷交易明细-特殊交易
specialTradeIndex='特殊交易'
creditTradeDetailHeader_specialTrade = pd.DataFrame(columns=consts.creditTradeDetailHeader_specialTrade,index=[specialTradeIndex])


#信贷交易明细
#非循环贷账户
loanInfoIndex='非循环贷账户'
creditTradeDetailDf_loanAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_loanAccountInfo,index=[loanInfoIndex])

#循环额度下分账户
cycleCreditAccountInfoIndex='循环额度下分账户'
creditTradeDetailDf_cycleCreditAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_cycleCreditAccountInfo,index=[cycleCreditAccountInfoIndex])

#循环贷账户
cycleLoanAccountInfoIndex='循环贷账户'
creditTradeDetailDf_cycleLoanAccountInfo = pd.DataFrame(columns=consts.creditTradeDetailHeader_cycleLoanAccountInfo,index=[cycleLoanAccountInfoIndex])

#贷款信息
loanAccountInfoIndex='贷款信息'
loanAccountInfoDf =  pd.DataFrame(columns=consts.loanAccountInfoHeader,index=[loanAccountInfoIndex])
#贷记卡信息
creditCardAccountInfoIndex = '贷记卡账户'
creditCardAccountInfoDf = pd.DataFrame(columns=consts.creditCardAccountInfoHeader,index=[creditCardAccountInfoIndex])

#准贷记卡
creditCardAccountInfoIndexZ = '准贷记卡账户'
creditCardAccountInfoDfZ = pd.DataFrame(columns=consts.creditCardAccountInfoHeaderZ,index=[creditCardAccountInfoIndexZ])

useRateIndex = '使用率'
useRateDf =  pd.DataFrame(columns=consts.creditTradeDetailHeader_useRate,index=[useRateIndex])
openAccountIndex = '开户数'
openAccountDf =  pd.DataFrame(columns=consts.creditTradeDetailHeader_openAccount,index=[openAccountIndex])
payRcdStatusIndex = '24期还款状态'
payRcdStatusDf =  pd.DataFrame(columns=consts.creditTradeDetailHeader_payRcdStatus,index=[payRcdStatusIndex])



#查询记录明细指标
queryRecordDetailIndex = '信贷审批查询记录明细'
queryRecordDetailDf = pd.DataFrame(columns=consts.queryRecordDetailHeader,index=[queryRecordDetailIndex])

#住房公积金
housingFundRcdIndex = '住房公积金参缴记录'
housingFundRcdDf = pd.DataFrame(columns=consts.housingFundRcdHeader,index=[housingFundRcdIndex])
#============================指标定义区 end=============================
# 解析被查询信息指标
def parseQueryInfo(dfObj):
    df = dfObj["df"];
    reportTime = df.loc[0, :][3]
    reportTime = reportTime.split("：")[1]
    reportTime = reportTime.replace(".", "-");  # 报告时间
    queryInfo["reportTime"] = reportTime
    row = df.loc[2, :]
    queryInfo["queryInfoName"] = row[0];  # 被查询者姓名
    # basicInfoDf.loc[0, '姓名'] = row[0]
    queryInfo["queryInfoCardId"] = row[2].replace("\n", "");  # 被查询者证件号码
    # basicInfoDf.loc[0, '身份证'] = row[2].replace("\n", "")

# 婚姻状况
# 学历
# 单位电话
# 住宅电话
# 通讯地址
def parseIdentity(dfObj):
    df = dfObj["df"];
    if not df.empty:
        row1 = df.loc[1, :].dropna().reset_index(drop=True)
        # identity["marital"] = row1[3]  # 婚姻状况
        # identity["education"] = row1[4]  # 学历
        # identity["commAddress"] = row1[9].replace("\n", "");  # 通讯地址

        identityInfoDf.loc[identityInfoIndex, '性别'] = row1[0]
        identityInfoDf.loc[identityInfoIndex, '出生日期'] = dfParser.formatDate(row1[1])[0:7]
        identityInfoDf.loc[identityInfoIndex, '国籍'] = row1[6]
        identityInfoDf.loc[identityInfoIndex, '户籍地址'] = row1[9].replace("\n", "")
        identityInfoDf.loc[identityInfoIndex, '婚姻状况'] = row1[2]
        identityInfoDf.loc[identityInfoIndex, '学历'] = row1[3].replace("\n", "")
        identityInfoDf.loc[identityInfoIndex, '学位'] = row1[4]
        identityInfoDf.loc[identityInfoIndex, '通讯地址'] = row1[8].replace("\n", "")
        identityInfoDf.loc[identityInfoIndex, '就业状况'] = row1[5]
        mobileDf = dfObj["mobileDf"];
        identityInfoDf.loc[identityInfoIndex, '历史手机号码数'] = mobileDf.index.size
        reportTime = queryInfo["reportTime"]
        identityInfoDf.loc[identityInfoIndex, '近3个月手机号码数'] = getLastMonthMobileCount(mobileDf,3,reportTime)
        identityInfoDf.loc[identityInfoIndex, '近6个月手机号码数'] = getLastMonthMobileCount(mobileDf, 6,reportTime)
        identityInfoDf.loc[identityInfoIndex, '近12个月手机号码数'] = getLastMonthMobileCount(mobileDf, 12,reportTime)
        identityInfoDf.loc[identityInfoIndex, '近24个月手机号码数'] = getLastMonthMobileCount(mobileDf, 24,reportTime)

#最近几个月电话号码数
def getLastMonthMobileCount(df, month,reportTime):
    # 当前日期
    last1MonthDateStr = reportTime
    # 最近一个月
    lastMonthDate = np.datetime64(last1MonthDateStr, "D") - np.timedelta64(30 * month, 'D')
    lastMonthMobileDf = df[df[5] >= str(lastMonthDate)]

    return lastMonthMobileDf.shape[0];

# 配偶姓名
# 配偶证件号码
# 配偶工作单位
# 配偶联系电话
def parseMate(dfObj):
    df = dfObj["df"];
    if not df.empty:
        row1 = df.loc[1, :]
        mate["mateName"] = row1[0]  # 配偶姓名
        mate["mateCardId"] = row1[2]  # 配偶证件号码
        mate["mateWorkCompany"] = row1[3].replace("\n", "");  # 配偶工作单位
        mate["mateContactTel"] = row1[4];  # 配偶联系电话
        mateInfoDf.loc[mateInfoIndex, '姓名'] = row1[0]
        mateInfoDf.loc[mateInfoIndex, '证件号码'] = row1[2]
        mateInfoDf.loc[mateInfoIndex, '工作单位'] = row1[3].replace("\n", "");
        mateInfoDf.loc[mateInfoIndex, '联系电话'] = row1[4].replace("\n", "");

#解析居住信息
def parseLiveInfo(dfObj):
    df = dfObj["df"];
    if not df.empty:
        row1 = df.loc[1, :]
        liveInfoDf.loc[liveInfoIndex, '居住地址'] = row1[1]
        liveInfoDf.loc[liveInfoIndex, '住宅电话'] = row1[2]
        liveInfoDf.loc[liveInfoIndex, '历史居住地址个数'] = df.index.size-1;
        curDate = np.datetime64(time.strftime("%Y-%m-%d"));
        last3year = str(curDate)[0:4]
        last3yearDate = str(int(last3year)-3)+str(curDate)[4:10]
        lastLiveDf = df[df[4]>=last3yearDate];
        liveInfoDf.loc[liveInfoIndex, '最近3年内居住地址个数'] = lastLiveDf.index.size-1;
        houseIndex = df[df[3]=='自置'].index.size>0
        if (houseIndex):
            houseStr = '是'
        else:
            houseStr= '否'
        liveInfoDf.loc[liveInfoIndex, '当前居住状况-是否具有自有住房'] = houseStr;
        liveInfoDf.loc[liveInfoIndex, '居住状况'] = row1[3]
        liveInfoDf.loc[liveInfoIndex, '信息更新日期'] = row1[4]

#解析职业信息
def parseOccupationInfoDf(dfObj):
    df = dfObj["df"];
    if not df.empty:
        occIndex1 = 0#判断职业从哪行开始
        for i in range(0,df.index.size):
           if df.loc[i,:].dropna().tolist()==occupationInfo_header1:
                occIndex1=i;
                break;
        occDf = df[1:occIndex1].reset_index(drop=True)#工作单位
        occDfNew = pd.DataFrame()
        occDf1New = pd.DataFrame()
        #删除为none的列 合并的bug TODO
        for i in range(0,occDf.index.size):
            occDfNew = occDfNew.append([pd.DataFrame(occDf.iloc[i].dropna().reset_index(drop=True)).T],ignore_index=True)
        occDf1 = df[occIndex1+1:df.index.size].reset_index(drop=True) #职业
        for i in range(0,occDf1.index.size):
            occDf1New = occDf1New.append([pd.DataFrame(occDf1.iloc[i].dropna().reset_index(drop=True)).T], ignore_index=True)

        occDf = pd.concat([occDfNew, occDf1New], axis=1, ignore_index=True)#合并df
        row = occDf.loc[0, :].dropna()#取最新
        occupationInfoDf.loc[occupationInfoIndex, '工作单位'] = row[1]
        last3yearDate = utils.getLastMonthDate(queryInfo['reportTime'],12*3)
        occDf = utils.replaceDateColIdx(occDf,occDf.columns.size-1)
        dateIndex = occDf.columns.size-1;#日期列
        last3yearOccDf = occDf[occDf[dateIndex]>=last3yearDate]
        occupationInfoDf.loc[occupationInfoIndex, '最近3年内工作单位数'] = last3yearOccDf.index.size;
        occupationInfoDf.loc[occupationInfoIndex, '单位电话'] = row[4];
        reportTime = queryInfo['reportTime']
        try:
            minDateIndex = np.argmin(occDf[dateIndex]);
            maxDateIndex = np.argmax(occDf[dateIndex]);
            rowYearMin = occDf.loc[minDateIndex, :].dropna()
            rowYearMax = occDf.loc[maxDateIndex, :].dropna()
            if rowYearMin[10]!="--":
                occupationInfoDf.loc[occupationInfoIndex, '最早进入本单位年份距报告日期时长'] = int(str(np.datetime64(reportTime, "Y")))-int(rowYearMin[10])
            if rowYearMax[10]!="--":
                occupationInfoDf.loc[occupationInfoIndex, '最新进入本单位年份距报告日期时长'] = int(str(np.datetime64(reportTime, "Y")))-int(rowYearMax[10])
        except:
            logger.error("最早进入本单位年份距报告日期时长解析异常")
        row0 = occDf.loc[0,:].dropna().reset_index(drop=True)#最新
        occupationInfoDf.loc[occupationInfoIndex, '单位性质'] =row0[2]
        occupationInfoDf.loc[occupationInfoIndex, '单位地址'] = row0[3].replace("\n","")
        occupationInfoDf.loc[occupationInfoIndex, '职业'] = row0[6]
        occupationInfoDf.loc[occupationInfoIndex, '行业'] = row0[7]
        occupationInfoDf.loc[occupationInfoIndex, '职务'] = row0[8]
        occupationInfoDf.loc[occupationInfoIndex, '职称'] = row0[9]
        occupationInfoDf.loc[occupationInfoIndex, '进入本单位年份'] = row0[10]
        occupationInfoDf.loc[occupationInfoIndex, '信息更新日期'] = row0[11]
        occupationInfoDf.loc[occupationInfoIndex, '历史工作单位数'] = occDf1.index.size
# 日期相减离当前时间月份
# 贷款账龄（月数）=当前日期(2020-04-01)-最小月份的1日(2019.2->2019-12-01)=4
# def difMonth(dateStr):
#     return int(int(str(np.datetime64(time.strftime("%Y-%m-%d")) -
#                        np.datetime64(dateStr.replace('.', '-'), "D")).split(" ")[0]) / 30);


# 信贷交易明细汇总
def parseLoanTradeInfo(dfObj):
    df = dfObj["df"];
    # row1 = df.loc[1, :]
    if not df.empty:
        loanMonthDf = df[1: 4]
        loanMonthDf = loanMonthDf.reset_index(drop=True)
        briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '个人住房贷款账户数'] = utils.toInt(loanMonthDf.loc[0, :][2])
        briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex,'个人商用房贷款（包括商住两用）账户数']=utils.toInt(loanMonthDf.loc[1, :][2])
        briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '其他类贷款账户数'] = utils.toInt(loanMonthDf.loc[2, :][2])
        creditCardDf = df[4: 6];
        creditCardDf = creditCardDf.reset_index(drop=True)
        briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '贷记卡账户数'] = utils.toInt(creditCardDf.loc[0, :][2])
        briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '准贷记卡账户数'] = utils.toInt(creditCardDf.loc[1, :][2])

# 解析呆账信息汇总
def parseBadDebtsInfoSumDf(dfObj):
    df = dfObj["df"];
    if not df.empty:
        row1 = df.loc[2, :]
        briefInfoDf_badDebtsInfoSum.loc[badDebtsInfoIndex, '账户数'] = row1[0];
        briefInfoDf_badDebtsInfoSum.loc[badDebtsInfoIndex, '余额'] = utils.replaceAmt(row1[1]);

# 解析被追偿信息汇总
def parseRecoveryInfoSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        row1 = df.loc[2, :]
        row2 = df.loc[3, :]
        row3 = df.loc[4, :]
        overdueBrief["disposalInfoSumAccount"] = row1[1];  # 资产处置信息汇总笔数
        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '资产处置业务账户数'] = row1[1];
        overdueBrief["disposalInfoSumAmt"] = row1[2];  # 资产处置信息汇总余额
        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '资产处置业务余额'] = utils.replaceAmt(row1[2]);
        overdueBrief["advanceInfoSumAccount"] = row2[1];  # 垫款业务笔数
        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '垫款业务账户数'] = row2[1];
        overdueBrief["advanceInfoSumAmt"] = row2[2];  # 垫款业务余额
        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '垫款业务余额'] = utils.replaceAmt(row2[2]);

        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '合计总账户数'] = row3[1];
        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '合计总余额'] = utils.replaceAmt(row3[2]);

# 贷款逾期账户数
# 贷款逾期月份数
# 贷款单月最高逾期总额
# 贷款最长逾期月数
def parseOverdueInfoSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        row2= df.loc[2, :]
        row3 = df.loc[3, :]
        row4 = df.loc[4, :]
        row5 = df.loc[5, :]
        row6 = df.loc[6, :]
        #这块的数据需要进行出来 TODO
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户账户数'] = utils.toInt(row2[1]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户月份数'] = utils.toInt(row2[2]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户单月最高逾期总额'] = utils.replaceAmt(row2[3]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '非循环贷帐户最长逾期月数'] = utils.toInt(row2[4]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户账户数'] = utils.toInt(row3[1]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户月份数'] = utils.toInt(row3[2]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户单月最高逾期总额'] = utils.replaceAmt(row3[3]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环额度下分账户最长逾期月数'] = utils.toInt(row3[4]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户账户数'] = utils.toInt(row4[1]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户月份数'] = utils.toInt(row4[2]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户单月最高逾期总额'] = utils.replaceAmt(row4[3]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '循环贷账户最长逾期月数'] = utils.toInt(row4[4]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户账户数'] = utils.toInt(row5[1]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户月份数'] = utils.toInt(row5[2]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户单月逾期总额'] = utils.replaceAmt(row5[3]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '贷记卡账户最长逾期月数'] = utils.toInt(row5[4]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户账户数'] = utils.toInt(row6[1]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户月份数'] = utils.toInt(row6[2]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户单月透支总额'] = utils.replaceAmt(row6[3]);
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '准贷记卡账户最长透支月数'] = utils.toInt(row6[4]);
        overdueInfoAccountDf = df[df[1] != '--'];
        overdueInfoAccountDf = overdueInfoAccountDf[2:7]
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户所有逾期账户最长逾期/透支月数最大值']=np.max(overdueInfoAccountDf[4].astype('int'))
        #np.sum(overdueInfoAccountDf[1])
        briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户所有逾期账户数加总']= np.sum(overdueInfoAccountDf[1].astype('int'))# TODO
        # briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户过去5年出现逾期的所有账户数目']=None# TODO

# 未结清贷款法人机构数	从“未结清贷款信息汇总”中直接提取LoanLegalOrgNum
# 未结清贷款机构数	从“未结清贷款信息汇总”中直接提取LoanOrgNum
# 未结清贷款笔数	从“未结清贷款信息汇总”中直接提取CountNum
# 未结清贷款合同总额	从“未结清贷款信息汇总”中直接提取ContractProfits
# 未结清贷款合同余额	从“未结清贷款信息汇总”中直接提取Balance
# 未结清贷款近6月平均应还款	从“未结清贷款信息汇总”中直接提取Last6MothsAvgRepayAmount
# 个人贷款未结清笔数	"从“未结清贷款信息汇总”计算客户符合以下条件的贷款笔数
# 1.贷款类型不为（'%个人助学贷款%' ，'%农户贷款%'）
# 2.贷款额度>100元
# 3.贷款状态不为“结清”"
# 非循环贷账户信息汇总
def doFilterCalc(dfx):
    dfx = dfx.replace('--', 0)
    return dfx;

# 科学计数法转换
def replaceAmt(dfx):
    return dfx.str.replace(',', '')

# 非循环贷账户信息汇总
def parseLoanAccountInfoSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        loanAccountInfoSumDf = df[2:3];
        loanAccountInfoSumDf = doFilterCalc(loanAccountInfoSumDf);  # 替换--为0
        loanAccountInfoSumDf = loanAccountInfoSumDf.reset_index(drop=True)
        row0 = loanAccountInfoSumDf.loc[0,:]
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户管理机构数'] = int(row0[0])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'] = int(row0[1])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户授信总额'] = int(utils.replaceAmt(row0[2]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户余额'] = int(utils.replaceAmt(row0[3]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户6月平均应还款'] = int(utils.replaceAmt(row0[4]))
# 循环额度下分账户
def parseCycleCreditAccountInfoSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        cycleCreditAccountInfoSumDf = df[2:3];
        cycleCreditAccountInfoSumDf = doFilterCalc(cycleCreditAccountInfoSumDf);  # 替换--为0
        cycleCreditAccountInfoSumDf = cycleCreditAccountInfoSumDf.reset_index(drop=True)
        row0 = cycleCreditAccountInfoSumDf.loc[0,:]
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户管理机构数']  = int(row0[0])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数'] = int(row0[1])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户授信总额'] = int(utils.replaceAmt(row0[2]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户余额'] = int(utils.replaceAmt(row0[3]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户6月平均应还款'] = int(utils.replaceAmt(row0[4]))

    # 循环贷账户信息
def parseCyleLoanAccountInfoSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        cycleLoanAccountInfoSumDf = df[2:3];
        cycleLoanAccountInfoSumDf = doFilterCalc(cycleLoanAccountInfoSumDf);  # 替换--为0
        cycleLoanAccountInfoSumDf = cycleLoanAccountInfoSumDf.reset_index(drop=True)
        row0 = cycleLoanAccountInfoSumDf.loc[0,:]
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户管理机构数']  = int(row0[0])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户账户数'] = int(row0[1])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户授信总额'] = int(utils.replaceAmt(row0[2]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户余额'] = int(utils.replaceAmt(row0[3]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户6月平均应还款'] = int(utils.replaceAmt(row0[4]))

# 解析贷记卡信息汇总,包含准贷记卡
def parseCreditCardInfoSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        creditCardInfoSumDf = df[2:3];
        creditCardInfoSumDf = doFilterCalc(creditCardInfoSumDf);  # 替换--为0
        creditCardInfoSumDf = creditCardInfoSumDf.reset_index(drop=True)
        row0 = creditCardInfoSumDf.loc[0, :]
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡发卡机构数'] = int(row0[0])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡账户数'] = int(row0[1])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡授信总额'] = int(utils.replaceAmt(row0[2]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡单家机构最高授信额'] = int(utils.replaceAmt(row0[3]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡单家机构最低授信额'] = int(utils.replaceAmt(row0[4]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡已用额度'] = int(utils.replaceAmt(row0[5]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡最近6个月平均使用额度'] = int(utils.replaceAmt(row0[6]))
        # 解析贷记卡信息汇总,包含准贷记卡
def parseCreditCardInfoSumZ(dfObj):
    df = dfObj["df"];
    if not df.empty:
        creditCardInfoSumDfZ = df[2:3];
        creditCardInfoSumDfZ = doFilterCalc(creditCardInfoSumDfZ);
        creditCardInfoSumDfZ = creditCardInfoSumDfZ.reset_index(drop=True)
        row0 = creditCardInfoSumDfZ.loc[0, :]
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡发卡机构数'] = int(row0[0])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡账户数'] = int(row0[1])
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡授信总额'] = int(utils.replaceAmt(row0[2]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡单家机构最高授信额'] = int(utils.replaceAmt(row0[3]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡单家机构最低授信额'] = int(utils.replaceAmt(row0[4]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡已用额度'] = int(utils.replaceAmt(row0[5]))
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡最近6个月平均使用额度'] = int(utils.replaceAmt(row0[6]))

#相关还款责任
def parseRepaymentSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        row4 = df.loc[4,:].dropna().reset_index(drop=True)#第4行 为个人
        row8 = None
        if df.index.size ==9:
            row8 = df.loc[8,:].dropna().reset_index(drop=True)#第8行 为企业
        perAccountNum = 0;#个人账户数
        orgAccountNum = 0;  # 企业账户数
        totalAccountNum = 0;#总账户数
        guaranteeAccountNum = 0;#相关还款责任总账户数-担保责任
        otherAccountNum =0;#相关还款责任总账户数-其他

        perGuaranteeAmt = 0#个人担保金额及其他
        orgGuaranteeAmt = 0#企业担保金额及其他
        totalGuaranteeAmt = 0;#总担保金额
        guaranteeAmt = 0;#相关还款责任总担保金额
        otherPaymentAmt = 0;#其他还款责任金额

        perGuaranteeBalance = 0  # 个人担保余额及其他
        orgGuaranteeBalance = 0  # 企业担保余额及其他
        totalGuaranteeBalance = 0;#总担保余额
        guaranteeBalance = 0;#相关还款责任总担保余额
        otherPaymentBalance = 0;  # 其他还款责任余额

        #计算总账户数
        if row4[0] !="--":
            perAccountNum=perAccountNum+utils.toInt(row4[0])
            guaranteeAccountNum = guaranteeAccountNum + utils.toInt(row4[0])#个人担保责任账户数
        if row4[3] !="--":
            perAccountNum = perAccountNum + utils.toInt(row4[3])#其他
            otherAccountNum = otherAccountNum + utils.toInt(row4[3])  # 其他
        if row8 != None:
            if row8[0] != "--":
                orgAccountNum = orgAccountNum + utils.toInt(row8[0])
                guaranteeAccountNum = guaranteeAccountNum + utils.toInt(row8[0])#企业担保责任账户数
            if row8[3] != "--":
                orgAccountNum = orgAccountNum + utils.toInt(row8[3])#其他
                otherAccountNum = otherAccountNum + utils.toInt(row8[3])  # 其他
        totalAccountNum = perAccountNum+orgAccountNum
        #计算担保金额
        if row4[1] !="--":
            perGuaranteeAmt=perGuaranteeAmt+utils.replaceAmt(row4[1])#担保
            guaranteeAmt = guaranteeAmt + utils.replaceAmt(row4[1])  # 担保
        if row4[4] !="--":
            perGuaranteeAmt = perGuaranteeAmt + utils.replaceAmt(row4[4])#其他
            otherPaymentAmt = otherPaymentAmt + utils.replaceAmt(row4[4])  # 其他
        if row8 != None:
            if row8[1] != "--":
                orgGuaranteeAmt = orgGuaranteeAmt + utils.replaceAmt(row8[1])#担保
                guaranteeAmt = guaranteeAmt + utils.replaceAmt(row8[1])  # 担保
            if row8[4] != "--":
                orgGuaranteeAmt = orgGuaranteeAmt + utils.replaceAmt(row8[4])#其他
                otherPaymentAmt = otherPaymentAmt + utils.replaceAmt(row8[4])  # 其他
        totalGuaranteeAmt = perGuaranteeAmt + orgGuaranteeAmt
        # 计算余额
        if row4[2] !="--":
            perGuaranteeBalance=perGuaranteeBalance+utils.replaceAmt(row4[2])
            guaranteeBalance=guaranteeBalance+utils.replaceAmt(row4[2])#个人担保余额
        if row4[5] !="--":
            perGuaranteeBalance = perGuaranteeBalance + utils.replaceAmt(row4[5])#其他
            otherPaymentBalance = otherPaymentBalance + utils.replaceAmt(row4[5])  # 其他

        if row8 != None:
            if row8[2] != "--":
                orgGuaranteeBalance = orgGuaranteeBalance + utils.replaceAmt(row8[2])
                guaranteeBalance = guaranteeBalance + utils.replaceAmt(row8[2])#企业担保余额
            if row8[5] != "--":
                orgGuaranteeBalance = orgGuaranteeBalance + utils.replaceAmt(row8[5])
                otherPaymentBalance = otherPaymentBalance + utils.replaceAmt(row8[5])  # 其他
        totalGuaranteeBalance = perGuaranteeBalance + orgGuaranteeBalance

        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数（担保+其他+个人+企业）'] =totalAccountNum
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额+总还款责任金额（个人+企业）'] =totalGuaranteeAmt
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额+总其他余额（个人+企业）'] =totalGuaranteeBalance
        if totalGuaranteeAmt !=0:
            briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额+总其他余额（个人+企业）/相关还款责任账户总担保金额+总其他金额（个人+企业）'] =\
                                                                            round(totalGuaranteeBalance / totalGuaranteeAmt, 2)
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任担保总账户数-个人'] =perAccountNum
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-个人'] =perGuaranteeAmt
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-个人'] =perGuaranteeBalance
        if perGuaranteeBalance !=0:
            briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-个人/相关还款责任总担保金额-个人'] = round(perGuaranteeBalance/perGuaranteeBalance,2)
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-企业'] =orgAccountNum
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-企业'] =orgGuaranteeAmt
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-企业'] =orgGuaranteeBalance
        if orgGuaranteeAmt!=0:
            briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-企业/相关还款责任总担保金额-企业'] = round(orgGuaranteeBalance/orgGuaranteeAmt,2)
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-担保责任'] =guaranteeAccountNum
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-担保责任'] =guaranteeAmt
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额-担保责任'] =guaranteeBalance
        if guaranteeAmt!=0:
            briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-担保责任/相关还款责任总担保金额-担保责任'] =round(guaranteeBalance/guaranteeAmt,2)

        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总账户数-其他'] =otherAccountNum
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保金额-其他'] =otherPaymentAmt
        briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任总担保余额-其他'] =otherPaymentBalance
        if otherPaymentAmt!=0:
            briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '相关还款责任账户总担保余额-其他/相关还款责任账户总担保金额-其他'] =round(otherPaymentBalance/otherPaymentAmt,2)


#解析公共信息汇总
def parsePublicInfoBrief(dfObj):
    df = dfObj["df"];
    if not df.empty:
        publicInfoBrief = df[1:6];
        publicInfoBrief = publicInfoBrief.reset_index(drop=True)
        row0 = publicInfoBrief.loc[0, :]
        row1 = publicInfoBrief.loc[1, :]
        row2 = publicInfoBrief.loc[2, :]
        row3 = publicInfoBrief.loc[3, :]

        publicInfoBriefDf.loc[publicInfoBriefIndex, '欠税信息-记录数'] = int(row0[1])
        publicInfoBriefDf.loc[publicInfoBriefIndex, '欠税信息-涉及金额'] = int(utils.replaceAmt(row0[2]))
        publicInfoBriefDf.loc[publicInfoBriefIndex, '民事判决信息-记录数'] = int(row1[1])
        publicInfoBriefDf.loc[publicInfoBriefIndex, '民事判决信息-涉及金额'] = int(utils.replaceAmt(row1[2]))
        publicInfoBriefDf.loc[publicInfoBriefIndex, '强制执行信息-记录数'] = int(row2[1])
        publicInfoBriefDf.loc[publicInfoBriefIndex, '强制执行信息-涉及金额'] = int(utils.replaceAmt(row2[2]))
        publicInfoBriefDf.loc[publicInfoBriefIndex, '行政处罚信息-记录数'] = int(row3[1])
        publicInfoBriefDf.loc[publicInfoBriefIndex, '行政处罚信息-涉及金额'] = int(utils.replaceAmt(row3[2]))

#解析查询信息汇总
def parseQueryRecordSum(dfObj):
    df = dfObj["df"];
    if not df.empty:
        queryRecordSumDfTmp = df[2:3];
        queryRecordSumDfTmp = queryRecordSumDfTmp.reset_index(drop=True)
        row0 = queryRecordSumDfTmp.loc[0, :]
        queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询机构数-贷款审批'] =int(row0[0])
        queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询机构数-信用卡审批'] =int(row0[1])
        queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-贷款审批'] =int(row0[2])
        queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-信用卡审批'] =int(row0[3])
        queryRecordSumDf.loc[queryRecordSumIndex, '近1月内的查询次数-本人查询'] =int(row0[4])
        queryRecordSumDf.loc[queryRecordSumIndex, '近2年内的查询次数-贷后管理'] =int(row0[5])
        queryRecordSumDf.loc[queryRecordSumIndex, '近2年内的查询次数-担保资格审查'] =int(row0[6])
# 解析查询记录明细
def parseQueryInfoDetail(dfObj):
    df = dfObj["df"];
    reportTime = queryInfo["reportTime"];
    if not df.empty:
        df = utils.replaceDateCol(df)
        df = df[1:df.index.size] # 去掉表头
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近1月查询次数'] =qip.getLastMonthQueryTimes(df, 1, "",reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数'] =qip.getLastMonthQueryTimes(df, 3, "",reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数'] =qip.getLastMonthQueryTimes(df, 6, "",reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数'] =qip.getLastMonthQueryTimes(df, 12, "",reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近1个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 1, "", reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近3个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 3, "", reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 6, "", reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近12个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 12, "", reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月查询机构数'] =qip.getLastMonthQueryOrgTimes(df, 24, "", reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数贷款审批'] =qip.getLastMonthQueryTimes(df, 3, consts.loanApprove, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询次数信用卡审批'] =qip.getLastMonthQueryTimes(df, 3, consts.creditCard, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数贷款审批'] =qip.getLastMonthQueryTimes(df, 6, consts.loanApprove, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询次数信用卡审批'] = qip.getLastMonthQueryTimes(df, 6, consts.creditCard, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数贷款审批'] = qip.getLastMonthQueryTimes(df, 12, consts.loanApprove, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询次数信用卡审批'] =qip.getLastMonthQueryTimes(df, 12, consts.creditCard, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询机构数贷款审批'] =qip.getLastMonthQueryOrgTimes(df, 3, consts.loanApprove, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近3月查询机构数信用卡审批'] =qip.getLastMonthQueryOrgTimes(df, 3, consts.creditCard, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询机构数贷款审批'] =qip.getLastMonthQueryOrgTimes(df, 6, consts.loanApprove, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近6月查询机构数信用卡审批'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.creditCard,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询机构数贷款审批'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.loanApprove, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近12月查询机构数信用卡审批'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.creditCard,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月担保资格审查查询次数'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.insuranceAprove,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '近12个月担保资格审查查询次数'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.insuranceAprove,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近6个月贷后管理查询次数'] = qip.getLastMonthQueryOrgTimes(df, 6, consts.loanAfterMgr,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近12个月贷后管理查询次数'] = qip.getLastMonthQueryOrgTimes(df, 12, consts.loanAfterMgr,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最后一次查询距离现在的月数贷款审批'] = qip.getLastTimeQueryMonth(df, consts.loanApprove,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月贷后管理查询次数']  = qip.getLastMonthQueryTimes(df, 24, consts.loanAfterMgr, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月贷款审批审批次数'] = qip.getLastMonthQueryTimes(df, 24, consts.loanApprove, reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月信用卡审批查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.creditCard,reportTime)
        queryRecordDetailDf.loc[queryRecordDetailIndex, '最近24个月担保资格审查查询次数'] = qip.getLastMonthQueryTimes(df, 24, consts.insuranceAprove,reportTime)

#解析住房公积金
def parseHousingFundRcd(df):
    if not df.empty:
        lastHousingFundRcdDf = df.sort_values(by=["信息更新日期"] , ascending=(False)).reset_index(drop=True)
        lastHousingFundRcdDf = lastHousingFundRcdDf[0:1]#最新
        row1 = lastHousingFundRcdDf.loc[0,:].dropna().reset_index(drop=True)
        housingFundRcdDf.loc[housingFundRcdIndex, '参缴地'] =row1[1]
        housingFundRcdDf.loc[housingFundRcdIndex, '参缴日期'] =row1[2]
        housingFundRcdDf.loc[housingFundRcdIndex, '初缴月份'] =row1[3]#初缴日期
        housingFundRcdDf.loc[housingFundRcdIndex, '缴至月份'] =row1[4]
        housingFundRcdDf.loc[housingFundRcdIndex, '缴费状态'] =row1[5]
        housingFundRcdDf.loc[housingFundRcdIndex, '月缴存额'] =row1[6]
        housingFundRcdDf.loc[housingFundRcdIndex, '个人存缴比例'] =row1[7]
        housingFundRcdDf.loc[housingFundRcdIndex, '单位存缴比例'] =row1[8]
        housingFundRcdDf.loc[housingFundRcdIndex, '缴费单位'] =row1[9]#扣缴单位
        housingFundRcdDf.loc[housingFundRcdIndex, '信息更新日期'] =row1[10]
        reportTime = queryInfo["reportTime"];
        lastDateStr = utils.getLastMonthDate(reportTime,12)
        avgHousingFundDf = df[df['缴至月份']>=lastDateStr]
        housingFundRcdDf.loc[housingFundRcdIndex, '最近1年公积金平均值'] = round(np.mean(avgHousingFundDf['月缴存额']),2)
        lastDateStr = utils.getLastMonthDate(reportTime, 12*3)
        avgHousingFundDf = df[df['缴至月份'] >= lastDateStr]
        housingFundRcdDf.loc[housingFundRcdIndex, '最近3年公积金平均值']= round(np.mean(avgHousingFundDf['月缴存额']),2)

#解析贷款还款记录指标
def parseLoanMergeAndPayRecordDf(df,payRcdDf):
    if not df.empty and not payRcdDf.empty:
        #正常
        normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')]
        overduePayRcdDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
        overduePayRcdDf = utils.replacePayRcdStatus(overduePayRcdDf)
        #计算当前贷款，为还款记录的最后一期 0529
        curOverduePayRcdDf=overduePayRcdDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False))
        curOverduePayRcdDf = curOverduePayRcdDf.groupby(['账户编号']).head(1)
        curOverduePayRcdDf = curOverduePayRcdDf[curOverduePayRcdDf['还款状态'] > 0]
        #临时保存,不用过滤还款状态为0的
        payRcdMaxOverdueDf = overduePayRcdDf;
        #所有逾期的记录
        overduePayRcdDf = overduePayRcdDf[overduePayRcdDf['还款状态']>0]
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数'] = curOverduePayRcdDf['账户编号'].unique().size
        if normalDf.index.size>0:
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数占比'] = round(loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期账户数']/normalDf.index.size,3)
        #存在逾期的贷款账户 非结清的过滤出逾期的账户号
        overdueLoanDf = normalDf[normalDf['账户编号'].isin(curOverduePayRcdDf['账户编号'].values)]
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数'] = overdueLoanDf['管理机构'].unique().size
        if normalDf['管理机构'].unique().size>0:
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数占比'] = round(loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款逾期机构数'] / normalDf['管理机构'].unique().size,2)

        #还款记录按日期排序最近3笔的最大逾期期数
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近1月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf,1);
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 3);
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 6);
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近9月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 9);
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近24月贷款的最大逾期期数'] = prp.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 24);
        reportTime = queryInfo["reportTime"]
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近24月贷款最大逾期距离现在的月数'] = prp.getPayRcdMaxOverdueNumMonth(payRcdMaxOverdueDf,normalDf,reportTime, 24);
        payStatus= ["G","D","C","N","M","1","2","3","4","5","6","7"]
        # 贷款24期还款记录次数 剔除结清 转出 呆账
        payRcdTimesDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
        payRcdTimesDf = payRcdTimesDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False))
        payRcdTimesDf = payRcdTimesDf.groupby(['账户编号']).head(24)
        #从“贷款信息”中提取，剔除“账户状态”为结清、转出、呆账、呆帐后，各账户的还款次数统计“24个月（账户）还款状态”包含"G","D","C","N","M"及数字的个数，MAX（各账户的还款次数）
        payRcdTimesDf = payRcdTimesDf[payRcdTimesDf['还款状态'].isin(payStatus)]
        payRcdTimes = payRcdTimesDf.groupby(['账户编号'])['还款状态'].count()
        loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款24期还款记录次数'] = np.max(payRcdTimes)
#解析信贷交易明细-特殊交易
def parseSpecialTrade(df):
    if not df.empty:
        creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '当前用户发生特殊交易的严重程度'] = np.max(df['严重程度'])#加工的指标
        maxChangeMonthIndex = np.argmax(np.abs(df['变更月数']))
        meanMonthValue = np.mean(np.abs(df['变更月数']))
        row0 = df.loc[maxChangeMonthIndex, :]
        settleDf = df[(df['特殊交易类型']=='提前结清') | (df['特殊交易类型']=='提前还款')]
        debtDf = df[(df['特殊交易类型'] == '以资抵债')]
        creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户发生特殊交易变更月数的最大差值'] = row0[3]
        creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户发生特殊交易变更月数的平均差值'] = round(meanMonthValue,2)
        creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户特殊交易涉及的发生金额的最大值'] = np.max(df['发生金额'])
        creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户特殊交易涉及的发生金额的平均值'] = round(np.mean(df['发生金额']),2)
        creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户所有帐户发生提前还款交易的次数统计'] = settleDf.index.size
        creditTradeDetailHeader_specialTrade.loc[specialTradeIndex, '用户所有帐户发生不良特殊交易的次数统计'] = debtDf.index.size;
#信贷交易明细-非循环贷账户
def parseLoanAccountInfo(df):
    if not df.empty:
        loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'])
        normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')].reset_index(drop=True)
        normalDf = normalDf[0:loanAccountNum]#根据非循环贷账户数进行计算进行截取
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '本月应还款（合计）'] = np.sum(normalDf['本月应还款'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '本月实还款（合计）'] = np.sum(normalDf['本月实还款'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '当前一共逾期期数']  = np.sum(normalDf['当前逾期期数'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '当前一共逾期总额']  = np.sum(normalDf['当前逾期总额'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期31-60天未还本金（合计）'] = np.sum(normalDf['逾期31-60天未还本金'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期61-90天未还本金（合计）'] = np.sum(normalDf['逾期61-90天未还本金'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期91-180天未还本金（合计）'] = np.sum(normalDf['逾期91-180天未还本金'])
        creditTradeDetailDf_loanAccountInfo.loc[loanInfoIndex, '逾期180天以上未还本金（合计）']= np.sum(normalDf['逾期180天以上未还本金'])

#信贷交易明细-循环额度分账户
def parseCycleCreditAccountInfo(df):
    if not df.empty:
        normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')].reset_index(drop=True)
        loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'])
        cycleCreditAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数'])
        normalDf = normalDf[loanAccountNum:(loanAccountNum + cycleCreditAccountNum)]
        if not normalDf.empty:
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '本月应还款（合计）'] = np.sum(normalDf['本月应还款'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '本月实还款（合计）'] = np.sum(normalDf['本月实还款'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '当前一共逾期期数']  = np.sum(normalDf['当前逾期期数'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '当前一共逾期总额']  = np.sum(normalDf['当前逾期总额'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期31-60天未还本金（合计）'] = np.sum(normalDf['逾期31-60天未还本金'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期61-90天未还本金（合计）'] = np.sum(normalDf['逾期61-90天未还本金'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期91-180天未还本金（合计）'] = np.sum(normalDf['逾期91-180天未还本金'])
            creditTradeDetailDf_cycleCreditAccountInfo.loc[cycleCreditAccountInfoIndex, '逾期180天以上未还本金（合计）']= np.sum(normalDf['逾期180天以上未还本金'])
#信贷交易明细-循环贷账户
def parseCycleLoanAccountInfo(df):
    if not df.empty:
        normalDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['账户状态'] != '呆账')]
        loanAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户账户数'])
        cycleCreditAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户账户数'])
        cycleAccountNum = int(briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户账户数'])
        normalDf = normalDf[(loanAccountNum+cycleCreditAccountNum):normalDf.index.size]
        if not normalDf.empty:
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '本月应还款（合计）'] = np.sum(normalDf['本月应还款'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '本月实还款（合计）'] = np.sum(normalDf['本月实还款'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '最近一次还款日期'] = np.max(normalDf['最近一次还款日期'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '当前一共逾期期数']  = np.sum(normalDf['当前逾期期数'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '当前一共逾期总额']  = np.sum(normalDf['当前逾期总额'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期31-60天未还本金（合计）'] = np.sum(normalDf['逾期31-60天未还本金'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期61-90天未还本金（合计）'] = np.sum(normalDf['逾期61-90天未还本金'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期91-180天未还本金（合计）'] = np.sum(normalDf['逾期91-180天未还本金'])
            creditTradeDetailDf_cycleLoanAccountInfo.loc[cycleLoanAccountInfoIndex, '逾期180天以上未还本金（合计）']= np.sum(normalDf['逾期180天以上未还本金'])
#解析贷款账户信息指标
def parseLoanMergeDf(df):
    if not df.empty:
        sortDf = df.sort_values(by=["账户关闭日期","借款金额（本金)"] , ascending=(False,False))
        sortDf = sortDf[sortDf['账户状态'] == '结清'];
        sortDf = sortDf.reset_index(drop=True)
        if not sortDf.empty:
            row0 = sortDf.loc[0, :]
            loanAccountInfo["lastSettleLoanAmt"] = row0['借款金额（本金)']
            loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的贷款金额'] = row0['借款金额（本金)']
            openDate = dfParser.formatDate(row0['开立日期'])
            loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的发放距今月数'] = utils.difMonthReportTime(openDate,queryInfo["reportTime"])
            settleDate = dfParser.formatDate(row0['账户关闭日期'])
            loanAccountInfoDf.loc[loanAccountInfoIndex, '最近一笔结清贷款的结清距今月数'] = utils.difMonthReportTime(settleDate,queryInfo["reportTime"])
        loanAccountInfoDf.loc[loanAccountInfoIndex, '历史贷款总法人机构数'] = df['管理机构'].unique().size
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前同时在用的贷款机构数'] = df[df['余额（本金)']>0]['管理机构'].unique().size
        statusDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出')]
        bankDf = statusDf[statusDf['管理机构'].str.contains('银行')]
        #没有记录
        if statusDf.index.size==0:
            isNotBankCust = -1
        else:
            if bankDf.index.size >0:#有一条以上不为结清，请包含银行
                isNotBankCust = 1;
            else:
                isNotBankCust = 0;
        loanAccountInfoDf.loc[loanAccountInfoIndex, '是否有非银行贷款客户'] = isNotBankCust
        #最严重的五级分类
        # fiveType = ""
        # for fiveTypeTmp in consts.fiveType:
        #     fiveTypeDf = statusDf[statusDf['五级分类']==fiveTypeTmp];
        #     if not fiveTypeDf.empty:
        #         fiveType = fiveTypeTmp;
        #         break;
        # loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款五级分类'] = fiveType
        #当前贷款LTV
        # 从“贷款信息”中提取，剔除“账户状态”为结清及转出，并剔除“账户状态”为呆账且本金余额 = 0
        # 的记录后，SUM(本金余额) / SUM(贷款本金)
        # 如本金余额为空和贷款本金为0或为空，则当条记录不计算
        loanLtvDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出') & (df['借款金额（本金)']>0) & (df['余额（本金)']!='--')]
        badSetDf = loanLtvDf[~((loanLtvDf['账户状态'] == '呆账') & (loanLtvDf['余额（本金)']==0))]
        balanceSum = np.sum(badSetDf['余额（本金)'].astype('int'))
        loanAmtSum = np.sum(badSetDf['借款金额（本金)'].astype('int'))
        if(loanAmtSum !=0):
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款LTV'] =  round(np.divide(balanceSum,loanAmtSum),2)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最高LTV'] = round(np.max(np.divide(badSetDf['余额（本金)'].astype('int'), badSetDf['借款金额（本金)'].astype('int'))),2)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最低LTV'] = round(np.min(np.divide(badSetDf['余额（本金)'].astype('int'), badSetDf['借款金额（本金)'].astype('int'))), 2)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款平均LTV'] = round(np.mean(np.divide(badSetDf['余额（本金)'].astype('int'), badSetDf['借款金额（本金)'].astype('int'))), 2)

        #['个人住房商业贷款','个人商用房（含商住两用）贷款','个人住房公积金贷款','房'],
        houseLtvList = consts.houseLtvList;
        # houseLtvDf = badSetDf[badSetDf['业务种类'].isin(houseLtvList)]
        # if not houseLtvDf.empty:
        #     loanAccountInfoDf.loc[loanAccountInfoIndex, '当前房贷LTV'] = round(np.divide(np.sum(houseLtvDf['余额（本金)'].astype('int')),np.sum(houseLtvDf['借款金额（本金)'].astype('int'))), 2)
        #['个人住房贷款','个人商用房（包括商住两用）贷款']
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前房贷LTV'] = lip.getCurLtv(badSetDf, houseLtvList)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款机构数量'] = loanLtvDf['管理机构'].unique().size
        cardLtvList = ['个人汽车消费贷款','车']
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前车贷LTV'] = lip.getCurLtv(badSetDf, cardLtvList)
        operateLtvList = ['个人经营性贷款']
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前经营贷LTV'] = lip.getCurLtv(badSetDf, operateLtvList)
        consumeLtvList = ['其他个人消费贷款']
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前消费贷LTV'] = lip.getCurLtv(badSetDf, consumeLtvList)

        bankLtvList = ['商业银行','外资银行','村镇银行','住房储蓄银行','财务公司']
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前银行贷LTV'] = lip.getCurBankLtv(badSetDf, bankLtvList)

        bankLtvList = ['消费金融公司','汽车金融公司','信托公司']# TODO
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前消金贷LTV'] = lip.getCurBankLtv(badSetDf, bankLtvList)
        smallLoanLtvList = ['小额信贷公司']
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前小贷LTV'] = lip.getCurBankLtv(badSetDf, smallLoanLtvList)
        #当前贷款最大逾期期数
        # 从“贷款信息”中提取，剔除“账户状态”为结清、转出、呆账、呆帐后，MAX(每笔贷款的当前逾期期数)
        loanOverdueLtvDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出')  & (df['账户状态'] != '呆账')]
        if not loanOverdueLtvDf.empty:
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期期数'] = np.max(loanOverdueLtvDf['当前逾期期数'])
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期金额'] = np.max(loanOverdueLtvDf['当前逾期总额'])
            loanOverdueLtvDf=loanOverdueLtvDf.reset_index(drop=True)
            maxOverdueIndex = np.argmax(loanOverdueLtvDf['当前逾期期数'])
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款最大逾期期数对应的最大逾期金额'] = loanOverdueLtvDf.loc[maxOverdueIndex,:]['当前逾期总额']
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户最高贷款本金'] = lip.getLastLoanAmtMax(df,queryInfo["reportTime"],3)#贷款指标加工单独放到一个文件里
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 3)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近3月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 3)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户最高贷款本金'] = lip.getLastLoanAmtMax(df, queryInfo["reportTime"], 6)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 6)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近6月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 6)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户最高贷款本金'] = lip.getLastLoanAmtMax(df, queryInfo["reportTime"], 12)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户最低贷款本金'] = lip.getLastLoanAmtMin(df, queryInfo["reportTime"], 12)
        loanAccountInfoDf.loc[loanAccountInfoIndex, '近12月开户平均贷款本金'] = lip.getLastLoanAmtAvg(df, queryInfo["reportTime"], 12)

        lastLoanDf = loanOverdueLtvDf;
        if not lastLoanDf.empty:
            loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款最近一次还款日期距今时长'] = lip.getLastPayDateMinDays(lastLoanDf,queryInfo["reportTime"])
        normalDf = df[(df['账户状态'] == '正常') & (df['当前逾期期数'] == 0)]
        #未结清贷款总账户数：账户状态不等于结清和转出的记录数
        notSettleDf = df[(df['账户状态'] != '结清') & (df['账户状态'] != '转出')]
        if not notSettleDf.empty:
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户数'] = normalDf.index.size
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户数占比'] = round(normalDf.index.size/notSettleDf.index.size,2)
            #当前未结清贷款余额总和
            # ltvDf = tmpDf[tmpDf['业务种类'].isin(bizTypeList)]
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清贷款余额总和'] = np.sum(notSettleDf['余额（本金)'])
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清贷款余额总和'] = np.sum(notSettleDf['余额（本金)'])
            # 当前未结清住房贷款余额总和
            houseDf = notSettleDf[notSettleDf['业务种类'].isin(houseLtvList)]
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清住房贷款余额总和'] = np.sum(houseDf['余额（本金)'])
            # 当前未结清汽车贷款余额总和
            cardDf = notSettleDf[notSettleDf['业务种类'].isin(cardLtvList)]
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清汽车贷款余额总和'] = np.sum(cardDf['余额（本金)'])
            # 当前未结清个人经营性贷款余额总和
            operateLtvDf = notSettleDf[notSettleDf['业务种类'].isin(operateLtvList)]
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前未结清个人经营性贷款余额总和'] = np.sum(operateLtvDf['余额（本金)'])
            # 当前平均每月贷款余额总和
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前平均每月贷款余额总和'] = round(np.sum(notSettleDf['余额（本金)'])/12,2)
            #当前正常贷款账户余额
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户余额'] = np.sum(normalDf['余额（本金)'])
            # "从“贷款信息”中提取，剔除结清、转出，当前正常贷款账户余额/未结清贷款总余额（本金余额加总）
            loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常贷款账户余额占总余额比'] = round(np.sum(normalDf['余额（本金)'])/np.sum(notSettleDf['余额（本金)']),2)

        settleDf = df[(df['账户状态'] == '结清')]
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常结清贷款账户数'] = settleDf.index.size
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前正常结清贷款账户数占比'] = round(settleDf.index.size/df.index.size,2)
        #贷款24期还款记录次数 TODO
        # 最近3个月个人消费贷款发放额度
        loanAccountInfoDf.loc[loanAccountInfoIndex, '贷款本月实还款金额'] = np.sum(loanOverdueLtvDf['本月应还款'])
        loanAccountInfoDf.loc[loanAccountInfoIndex, '最近3个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df,3,queryInfo["reportTime"])
        loanAccountInfoDf.loc[loanAccountInfoIndex, '最近6个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df, 6,queryInfo["reportTime"])
        loanAccountInfoDf.loc[loanAccountInfoIndex, '最近12个月个人消费贷款发放额度'] = lip.getLastPerConsumeAmt(df, 12,queryInfo["reportTime"])
        #未结清贷款平均剩余还款期数
        payPieDf = notSettleDf[notSettleDf['还款期数']!='--']
        if payPieDf.index.size!=0:
            loanAccountInfoDf.loc[loanAccountInfoIndex, '未结清贷款平均剩余还款期数'] = round(np.sum(payPieDf['剩余还款期数'])/payPieDf.index.size,2)
        # 当前贷款本月应还金额总和
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款本月应还金额总和'] = np.sum(notSettleDf['本月应还款'])
        # 当前贷款本月实还金额总额
        loanAccountInfoDf.loc[loanAccountInfoIndex, '当前贷款本月实还金额总额'] = np.sum(notSettleDf['本月实还款'])


#解析贷记卡账户信息指标
def parseCreditCardMergeDf(df):
    if not df.empty:
        # 历史信用卡总法人机构数
        # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'历史信用卡总法人机构数'] = df['发卡机构'].unique().size
        # creditCardUseDf = df[df['已用额度']>0];
        # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'当前同时在用的信用卡机构数'] = creditCardUseDf['发卡机构'].unique().size
        #统一排除
        creditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
        totalAmtDf =  df[(df['币种'] == '人民币元') & (df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
        #大额专项分期额度（合计）
        # 已用分期金额(合计）
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '大额专项分期额度（合计）'] = np.sum(creditDf['大额专项分期额度'])
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '已用分期金额(合计）'] = np.sum(creditDf['已用分期金额'])
        # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex,'贷记卡账户当前总额度'] = cip.getMaxCreditAmt(creditDf)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近新发放的3张贷记卡平均额度'] = cip.getAvgCreditAmt(creditDf)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡额度使用率超过90%的机构数占比'] = cip.getUseRate(creditDf,df,0.9)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡额度使用率超过100%的机构数占比'] = cip.getUseRate(creditDf, totalAmtDf, 1)
        # 从“贷记卡信息”中提取，计算授信额度时剔除销户，计算已用额度时剔除呆账、呆帐、销户后，SUM(各账户已用额度) / SUM(各账户授信额度)
        useCreditDf =  df[(df['币种'] == '人民币元') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
        totalCreditDf = df[(df['币种'] == '人民币元') & (df['账户状态'] != '销户')]
        totalCreditAmt = np.sum(totalCreditDf['账户授信额度'])
        if totalCreditAmt != 0:#授信额度不能为0
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户当前总额度使用率'] = round(np.sum(useCreditDf['已用额度'])/np.sum(totalCreditDf['账户授信额度']),2)
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户最高使用额度总的使用率'] = round(np.sum(useCreditDf['最大使用额']) / np.sum(totalCreditDf['账户授信额度']), 2)
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡账户近6月平均额度总的使用率'] = round(np.sum(useCreditDf['最近6个月平均使用额度']) / np.sum(totalCreditDf['账户授信额度']), 2)
        # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期期数'] = np.max(creditDf['当前逾期期数'])#用于计算
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期金额'] = np.max(creditDf['当前逾期总额'])
        if not creditDf.empty:
            creditDf = creditDf.reset_index(drop=True)
            maxOverdueIndex = np.argmax(creditDf['当前逾期期数'])
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡最大逾期期数对应的最大逾期金额'] = creditDf.loc[maxOverdueIndex,:]['当前逾期总额']
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df,queryInfo["reportTime"],3)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 3)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 3)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df, queryInfo["reportTime"], 6)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 6)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 6)

        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡最高额度'] = cip.getLastMonthMaxCreditAmt(df, queryInfo["reportTime"], 12)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡最低额度'] = cip.getLastMonthMinCreditAmt(df, queryInfo["reportTime"], 12)
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月开卡平均额度'] = cip.getLastMonthAvgCreditAmt(df, queryInfo["reportTime"], 12)

        if not creditDf.empty:
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最近一次还款日期距今时长'] = cip.getLastPayDateMinDays(creditDf,queryInfo["reportTime"])
            paySo = np.sum(creditDf['本月应还款'])
            if(paySo)!=0:
                creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡还款比例'] = round(np.sum(creditDf['本月实还款'])/np.sum(creditDf['本月应还款']),2)
                creditDfTmp = creditDf[creditDf['本月应还款']>0]
                creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最高还款比例'] = round(np.max(np.divide(creditDfTmp['本月实还款'] , creditDfTmp['本月应还款'])), 2)
                creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡最低还款比例'] =  round(np.min(np.divide(creditDfTmp['本月实还款'] , creditDfTmp['本月应还款'])), 2)
        normalDf = df[(df['币种'] == '人民币元') & (df['账户状态'] == '正常') & (df['当前逾期期数']==0)];
        notCloseDf = df[(df['账户状态'] != '销户')]

        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户数'] = normalDf.index.size
        if not notCloseDf.empty and not normalDf.empty:
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户数占比'] = round(normalDf.index.size/notCloseDf.index.size,2)
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡已用额度'] = np.sum(normalDf['已用额度'])
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数'] = normalDf[normalDf['已用额度']>0].index.size
        if not creditDf.empty:
            creditUseAmt = np.sum(creditDf['已用额度'])
            if creditUseAmt!=0:
                creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常贷记卡账户余额占总余额比'] = round(np.sum(normalDf['已用额度']) / np.sum(creditDf['已用额度']), 2)

        if notCloseDf.empty:
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数占比'] = -99
        else:
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数占比'] = \
                round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前正常且有余额的贷记卡账户数']/notCloseDf.index.size,3)
        #当前正常贷记卡账户余额占总余额比

        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡本月实还金额总和'] = np.sum(creditDf['本月实还款'])
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡本月应还金额总和'] = np.sum(creditDf['本月应还款'])
        maxAmtDf = df[(df['币种'] == '人民币元')]
        if not maxAmtDf.empty:
            maxAmtDf = maxAmtDf.reset_index(drop=True)
            maxAmtIndex = np.argmax(maxAmtDf['账户授信额度'])
            maxOpenDate = maxAmtDf.loc[maxAmtIndex,:]['开立日期'];
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '额度最高的人民币贷记卡开卡距今月份数'] = utils.difMonthReportTime(maxOpenDate,queryInfo["reportTime"]);
        # 名下贷记卡数量-状态正常
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态正常'] = df[(df['账户状态'] != '销户')].index.size
        # 名下贷记卡数量-状态未激活
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态未激活'] = df[(df['账户状态'] == '未激活')].index.size
        # 名下贷记卡数量-状态异常--异常包含（2－冻结,3－止付,5－呆帐，10-其他）
        abnormalList = ['冻结','止付','呆帐','其他']
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态异常'] = df[(df['账户状态'].isin(abnormalList))].index.size
        # 名下贷记卡比例-状态正常
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态正常'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态正常'] / df.index.size,2)
        # 名下贷记卡比例-状态未激活
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态未激活'] =round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态未激活'] / df.index.size,2)
        # 名下贷记卡比例-状态异常
        creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡比例-状态异常'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '名下贷记卡数量-状态异常'] / df.index.size,2)

#解析准贷记卡账户信息指标
def parseCreditCardMergeDfZ(df,payRcd):
    if not df.empty:
        overdueCreditCardRcdDf = payRcd[payRcd['账户编号'].isin(df['账户编号'].values)];
        overdueCreditCardRcdDf = utils.replacePayRcdStatusOverdue(overdueCreditCardRcdDf)
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '本月应还款（合计）'] = np.nansum(df['透支余额'])
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '本月实还款（合计）'] = np.nansum(df['本月实还款'])
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '最近一次还款日期'] = np.max(df['最近一次还款日期'])
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前一共透支期数'] = cip.getCurOverdueNum(overdueCreditCardRcdDf);
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前一共透支总额'] = np.nansum(df['透支余额'])
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '透支180天以上未支付余额（合计）'] = np.nansum(df['透支180天以上未付余额'])
        creditDf = df[(df['账户状态'] != '未激活') & (df['账户状态'] != '销户')]
        if not creditDf.empty:
            totalAmt = np.nansum(creditDf['账户授信额度'])
            creditAmt =  np.nansum(creditDf['透支余额'])
            if totalAmt !=0:
                #从“贷记卡信息”中提取，剔除未激活、销户后，所有账户透支金额/所有账户账户授信额度。
                creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '全部准贷记卡账户当前总额度使用率']=round(creditAmt/totalAmt,2)
            #从“贷记卡信息”中提取，剔除未激活、销户后，MAX（单账户最高透支金额/单账户授信额度）
            creditMaxDf = creditDf[creditDf['账户授信额度']>0]
            if not creditMaxDf.empty:
                creditMaxDf = creditMaxDf.fillna(0.0)
                creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '准贷记卡账户最高使用额度总的使用率'] = round(np.max(np.divide(creditMaxDf['最大透支余额'],creditMaxDf['账户授信额度'])),2)
            creditMaxDf = creditDf[creditDf['最大透支余额'] > 0]
            if not creditMaxDf.empty:
                creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡最大透支金额'] = np.max(creditMaxDf['最大透支余额'])
            #从“贷记卡信息”中提取，剔除未激活、销户后，当前透支准贷记卡账户数/总准贷记卡账户数，透支账户判断：透支余额不为0的账户
            creditDfTmp = creditDf[creditDf['透支余额']>0]
            creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡透支账户数占比'] = round(creditDfTmp.index.size / creditDf.index.size,2)
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡本月应还金额总和'] = np.nansum(df['透支余额'])
        creditCardAccountInfoDfZ.loc[creditCardAccountInfoIndexZ, '当前准贷记卡本月实还金额总和'] = np.nansum(df['本月实还款'])




#解析使用率 TODO 使用汇总计算还是使用明细计算
def parseUseRate():
    # useRateDf.loc[useRateIndex, '贷记卡账户使用率（已用额度/授信总额）']
    # 从“信贷交易授信及负债信息概要”中“非循环贷账户信息汇总”、“循环额度下分账户信息汇总”、“循环贷账户信息汇总”、“贷记卡账户信息汇总”和“准贷记卡账户信息汇总”里提取，SUM(
    #     所有“余额”、“已用额度”和“透支余额”） / SUM(所有“授信总额”和“授信额度”)

    loanUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户余额']
    cycleCreditUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户余额']
    cycleUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户余额']
    creditUseAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡已用额度']
    creditAmtUseZ = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡已用额度']

    loanTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '非循环贷账户授信总额']
    cycleCreditTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环额度下分账户授信总额']
    cycleTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '循环贷账户授信总额']
    creditTotalAmt = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '贷记卡授信总额']
    creditAmtTotalZ = briefInfoDf_loanTradeCreditInfo.loc[loanTradeCreditInfoIndex, '准贷记卡授信总额']
    # if str(loanUseAmt)=="nan":
    #     loanUseAmt = 0;
    # if str(cycleCreditUseAmt) == "nan":
    #     loanUseAmt = 0;
    # if str(cycleCreditUseAmt) == "nan":
    #     loanUseAmt = 0;

    useAmt = loanUseAmt+cycleCreditUseAmt+cycleUseAmt+creditUseAmt+creditAmtUseZ
    totalAmt = loanTotalAmt+cycleCreditTotalAmt+cycleTotalAmt+creditTotalAmt+creditAmtTotalZ
    if totalAmt !=0:
        useRateDf.loc[useRateIndex, '全账户使用率（已用额度/授信总额）'] = round(useAmt / totalAmt,2)
    if loanTotalAmt!=0:
        useRateDf.loc[useRateIndex, '非循环贷账户使用率（已用额度/授信总额）'] = round(loanUseAmt / loanTotalAmt,2)
    if cycleCreditTotalAmt !=0:
        useRateDf.loc[useRateIndex, '循环额度下分账户使用率（已用额度/授信总额）'] = round(cycleCreditTotalAmt / cycleCreditTotalAmt,2)
    if cycleTotalAmt !=0:
        useRateDf.loc[useRateIndex, '循环贷账户使用率（已用额度/授信总额）'] = round(cycleUseAmt / cycleTotalAmt,2)
    if creditTotalAmt !=0:
        useRateDf.loc[useRateIndex, '贷记卡账户使用率（已用额度/授信总额）'] = round(creditUseAmt / creditTotalAmt,2)
    if creditAmtTotalZ !=0:
        useRateDf.loc[useRateIndex, '准贷记卡账户使用率（已用额度/授信总额）'] = round(creditAmtUseZ / creditAmtTotalZ,2)
#解析开户数
def parseOpenAccount(loanDf,creditCardDf,creditCardDfZ,recoveryInfoMergeDf,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ):
    reportTime = queryInfo["reportTime"];

    openAccountDf.loc[openAccountIndex, '近3个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,3)+cip.getOpenAccount(creditCardDf,reportTime,3)+cip.getOpenAccount(creditCardDfZ,reportTime,3)
    openAccountDf.loc[openAccountIndex, '近6个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,6)+cip.getOpenAccount(creditCardDf,reportTime,6)+cip.getOpenAccount(creditCardDfZ,reportTime,6)
    openAccountDf.loc[openAccountIndex, '近9个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,9)+cip.getOpenAccount(creditCardDf,reportTime,9)+cip.getOpenAccount(creditCardDfZ,reportTime,9)
    openAccountDf.loc[openAccountIndex, '近12个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,12)+cip.getOpenAccount(creditCardDf,reportTime,12)+cip.getOpenAccount(creditCardDfZ,reportTime,12)
    openAccountDf.loc[openAccountIndex, '近24个月全账户开户数'] = cip.getOpenAccount(loanDf,reportTime,24)+cip.getOpenAccount(creditCardDf,reportTime,24)+cip.getOpenAccount(creditCardDfZ,reportTime,24)


    openAccountDf.loc[openAccountIndex, '近3个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,3,consts.bankList)
    openAccountDf.loc[openAccountIndex, '近6个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,6,consts.bankList)
    openAccountDf.loc[openAccountIndex, '近9个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,9,consts.bankList)
    openAccountDf.loc[openAccountIndex, '近12个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,12,consts.bankList)
    openAccountDf.loc[openAccountIndex, '近24个月消费金融类账户开户数'] = lip.getOpenAccount(loanDf,reportTime,24,consts.bankList)

    openAccountDf.loc[openAccountIndex, '近3个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,3,"")
    openAccountDf.loc[openAccountIndex, '近6个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,6,"")
    openAccountDf.loc[openAccountIndex, '近9个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,9,"")
    openAccountDf.loc[openAccountIndex, '近12个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,12,"")
    openAccountDf.loc[openAccountIndex, '近24个月贷款账户开户数'] = lip.getOpenAccount(loanDf,reportTime,24,"")


    openAccountDf.loc[openAccountIndex, '近3个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,3)
    openAccountDf.loc[openAccountIndex, '近6个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,6)
    openAccountDf.loc[openAccountIndex, '近9个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,9)
    openAccountDf.loc[openAccountIndex, '近12个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,12)
    openAccountDf.loc[openAccountIndex, '近24个月贷记卡账户开户数'] = cip.getOpenAccount(creditCardDf,reportTime,24)

    openAccountDf.loc[openAccountIndex, '近3个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,3)
    openAccountDf.loc[openAccountIndex, '近6个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,6)
    openAccountDf.loc[openAccountIndex, '近9个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,9)
    openAccountDf.loc[openAccountIndex, '近12个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,12)
    openAccountDf.loc[openAccountIndex, '近24个月准贷记卡账户开户数'] = cip.getOpenAccount(creditCardDfZ,reportTime,24)

    #从“信贷交易信息明细”中“非循环贷账户”、“循环额度下分账户”、“循环贷账户”、“贷记卡账户”和“准贷记卡账户”里提取，5年里账户还款状态出现“1、2、3、4、5、6、7、D、Z、G、B”的账户数/所有账户数
    overdueLoanPayRcdDf = loanPayRecordMergeDf[loanPayRecordMergeDf['账户编号'].isin(loanDf['账户编号'].values)]
    overdueLoanPayRcdDf = utils.replacePayRcdStatusOverdue(overdueLoanPayRcdDf)
    overdueLoanPayRcdDf = overdueLoanPayRcdDf[overdueLoanPayRcdDf['还款状态'] > 0]
    overdueCreditPayRcdDf = creditCardPayRecordMergeDf[creditCardPayRecordMergeDf['账户编号'].isin(creditCardDf['账户编号'].values)]
    overdueCreditPayRcdDf = utils.replacePayRcdStatusOverdue(overdueCreditPayRcdDf)
    overdueCreditPayRcdDf = overdueCreditPayRcdDf[overdueCreditPayRcdDf['还款状态'] > 0]
    overdueCreditPayRcdDfZ = creditCardPayRecordMergeDfZ[creditCardPayRecordMergeDfZ['账户编号'].isin(creditCardDfZ['账户编号'].values)]
    overdueCreditPayRcdDfZ = utils.replacePayRcdStatusOverdue(overdueCreditPayRcdDfZ)
    overdueCreditPayRcdDfZ = overdueCreditPayRcdDfZ[overdueCreditPayRcdDfZ['还款状态'] > 0]

    loanAccountNum = loanPayRecordMergeDf['账户编号'].unique().size
    creditAccountNum = creditCardPayRecordMergeDf['账户编号'].unique().size
    creditAccountNumZ = creditCardPayRecordMergeDfZ['账户编号'].unique().size
    overdueLoanNum = overdueLoanPayRcdDf['账户编号'].unique().size
    overdueCreditNum = overdueCreditPayRcdDf['账户编号'].unique().size
    overdueCreditNumZ = overdueCreditPayRcdDfZ['账户编号'].unique().size
    if (loanAccountNum+creditAccountNum+creditAccountNumZ) >0:
        openAccountDf.loc[openAccountIndex, '有过逾期记录的账户/全账户数'] = round((overdueLoanNum+overdueCreditNum+overdueCreditNumZ)/(loanAccountNum+creditAccountNum+creditAccountNumZ),2)

    otherPerLoanDf = loanDf[loanDf['业务种类'].isin(consts.otherPerLoan)]
    otherPerLoanNum = otherPerLoanDf.index.size;
    overdueOtherPerLoanNum = otherPerLoanDf[otherPerLoanDf['账户编号'].isin(overdueLoanPayRcdDf['账户编号'].values)].index.size;
    if otherPerLoanNum!=0:
        openAccountDf.loc[openAccountIndex, '有过逾期记录的消费金融类账户/全消费金融类账户数'] = round(overdueOtherPerLoanNum/otherPerLoanNum,2)
    if loanAccountNum!=0:
        openAccountDf.loc[openAccountIndex, '有过逾期记录的贷款账户/全贷款账户数'] = round(overdueLoanNum/loanAccountNum,2)
    if creditAccountNum!=0:
        openAccountDf.loc[openAccountIndex, '有过逾期记录的贷记卡账户/全贷记卡账户数'] = round(overdueCreditNum/creditAccountNum,2)
    if creditAccountNumZ!=0:
        openAccountDf.loc[openAccountIndex, '有过透支记录的准贷记卡账户/全准贷记卡账户数']= round(overdueCreditNumZ/creditAccountNumZ,2)

    # 0525新增
    pledgeLoanDf = loanDf[loanDf['担保方式'] =='抵押']
    pledgeCreditCardDf = creditCardDf[creditCardDf['担保方式'] == '抵押']
    pledgeCreditCardDfZ = creditCardDfZ[creditCardDfZ['担保方式'] == '抵押']
    isPledge = "否"
    if pledgeLoanDf.index.size+pledgeCreditCardDf.index.size+pledgeCreditCardDfZ.index.size >0:
        isPledge = "是"
    creditLoanDf = loanDf[loanDf['担保方式'] == '信用/免担保']
    creditCreditCardDf = creditCardDf[creditCardDf['担保方式'] == '信用/免担保']
    creditCreditCardDfZ = creditCardDfZ[creditCardDfZ['担保方式'] == '信用/免担保']
    isCredit = 0
    if creditLoanDf.index.size + creditCreditCardDf.index.size + creditCreditCardDfZ.index.size > 0:
        isCredit = creditLoanDf.index.size + creditCreditCardDf.index.size + creditCreditCardDfZ.index.size
    briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '是否存在担保方式为抵押的贷款'] = isPledge
    briefInfoDf_loanTradeInfo.loc[loanTradeInfoIndex, '担保方式为信用的贷款数量'] = isCredit
#解析24期还款状态指标
def parsePayRcdStatus(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ):
    #creditCardPayRecordMergeDf
    # 去掉外币
    creditCardMergeDf = creditCardMergeDf[creditCardMergeDf['币种']=='人民币元']
    creditCardPayRecordMergeDf = creditCardPayRecordMergeDf[creditCardPayRecordMergeDf['账户编号'].isin(creditCardMergeDf['账户编号'].values)]
    reportTime = queryInfo["reportTime"];
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近3月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,3)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“2”的次数']  = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“2”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近6月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“3”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近12月逾期期数大于或大等于“4”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24月逾期期数大于或等于“4”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,24)

    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近3月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,3)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“1”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“2”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近6月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近12月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,24)

    payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近6月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近12月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24月逾期期数大于或等于“3”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近6月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近12月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24月逾期期数大于或等于“4”的次数'] = cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,24)

    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近3月逾期期数大于或等于“1”的次数'] = prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,3)\
                                                 +cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,3)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,3)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“1”的次数'] = \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,6)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“1”的次数'] =  \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,12)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“1”的次数'] =  \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,1,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,1,24)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,1,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“2”的次数'] =  \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,6)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“2”的次数'] =  \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,12)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“2”的次数'] =  \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,2,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,2,24)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,2,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近6月逾期期数大于或等于“3”的次数'] =  \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,6)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,6)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“3”的次数'] =   \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,12)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“3”的次数'] =   \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,3,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,3,24)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,3,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近12月逾期期数大于或等于“4”的次数'] =   \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,12)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,12)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '全账户近24月逾期期数大于或等于“4”的次数'] =   \
        prp.getLoanOverdueTimes(loanPayRecordMergeDf,reportTime,4,24)+cip.getLoanOverdueTimes(creditCardPayRecordMergeDf,reportTime,4,24)\
                                                                     +cip.getLoanOverdueTimes(creditCardPayRecordMergeDfZ,reportTime,4,24)

    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24个月是否出现"G"'] = prp.isExistsInd(loanPayRecordMergeDf,reportTime,"G",24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷记卡账户近24个月是否出现"G"'] = prp.isExistsInd(creditCardPayRecordMergeDf,reportTime,"G",24)
    payRcdStatusDf.loc[payRcdStatusIndex, '准贷记卡账户近24个月是否出现"G"'] = prp.isExistsInd(creditCardPayRecordMergeDfZ,reportTime,"G",24)
    payRcdStatusDf.loc[payRcdStatusIndex, '贷款账户近24个月是否出现"Z"'] = prp.isExistsInd(loanPayRecordMergeDf,reportTime,"Z",24)

    payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷款账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(loanPayRecordMergeDf,reportTime,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷款账户过去24个月状态正常账户数目'] = prp.getLoanNormalCount(loanPayRecordMergeDf,reportTime,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷记卡账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(creditCardPayRecordMergeDf,reportTime,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户所有贷记卡账户过去24个月状态正常的账户数目'] = prp.getLoanNormalCount(creditCardPayRecordMergeDf,reportTime,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户所有准贷记卡账户过去24个月存在逾期的账户数目'] = prp.getLoanOverdueCount(creditCardPayRecordMergeDfZ,reportTime,24)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户所有准贷记卡账户过去24个月状态正常的账户数目'] = prp.getLoanNormalCount(creditCardPayRecordMergeDfZ,reportTime,24)

    payRcdStatusDf.loc[payRcdStatusIndex, '用户过去3个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,3)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户过去6个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,6)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户过去12个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,12)
    payRcdStatusDf.loc[payRcdStatusIndex, '用户过去24个月最大逾期期数'] = prp.getPayRcdMaxOverdueNumAllAccout(loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ,reportTime,24)

    #概要信息里的字段，从还款状态计算
    briefInfoDf_overdueInfoSum.loc[overdueInfoSumIndex, '该用户过去5年出现逾期的所有账户数目'] = \
        prp.getLoanOverdueCount(loanPayRecordMergeDf,reportTime,24*5)+prp.getLoanOverdueCount(creditCardPayRecordMergeDf,reportTime,24*5)\
        +prp.getLoanOverdueCount(creditCardPayRecordMergeDfZ,reportTime,24*5)
#解析贷款还款记录指标
def parseCreditCardMergeAndPayRecordDf(df,payRcdDf):
    if not df.empty and not payRcdDf.empty:
        # 正常
        normalDf = df[(df['账户状态'] != '未激活') & (df['账户状态'] != '销户') & (df['账户状态'] != '呆账')]
        if not normalDf.empty:
            overduePayRcdDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
            overduePayRcdDf = utils.replacePayRcdStatus(overduePayRcdDf)
            # 临时保存,不用过滤还款状态为0的
            payRcdMaxOverdueDf = overduePayRcdDf;
            overduePayRcdDf = overduePayRcdDf[overduePayRcdDf['还款状态'] > 0]
            # creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期账户数'] = overduePayRcdDf['账户编号'].unique().size
            #从“贷记卡信息”中提取，剔除“账户状态”为未激活、销户、呆账、呆帐后，“当前信用卡逾期账户数”/未销户贷记卡账户数（剔除“账户状态”为未激活、销户、呆账、呆帐后记录条数）
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期账户数占比'] = round(overduePayRcdDf['账户编号'].unique().size / normalDf.index.size, 2)
            #从“贷记卡信息”中提取，剔除“账户状态”为未激活、销户、呆账、呆帐后，对（当前信用卡逾期账户数）按“开户机构代码”去重统计账户状态为逾期，按按“开户机构代码”去重后的记录条数
            overdueCreditCardDf = normalDf[normalDf['账户编号'].isin(overduePayRcdDf['账户编号'].values)]
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数'] = overdueCreditCardDf['发卡机构'].unique().size
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数占比'] = round(creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '当前贷记卡逾期机构数'] / normalDf['发卡机构'].unique().size, 2)
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近3月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 3);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近6月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 6);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近9月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 9);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近12月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 12);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近24月贷记卡最大逾期期数'] = cip.getPayRcdMaxOverdueNum(payRcdMaxOverdueDf, 24);
            reportTime = queryInfo["reportTime"]
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '近24月贷记卡最大逾期距离现在的月数'] = cip.getPayRcdMaxOverdueNumMonth(payRcdMaxOverdueDf,normalDf,reportTime, 24);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近3个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,3);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近6个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,6);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近9个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,9);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近12个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,12);
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '最近24个月贷记卡最大连续逾期月份数'] = cip.getContinuousOverdueMonth(payRcdMaxOverdueDf,normalDf,24);
            payRcdTimesDf = payRcdDf[payRcdDf['账户编号'].isin(normalDf['账户编号'].values)]
            payRcdTimesDf = payRcdTimesDf.sort_values(by=["账户编号", "还款日期"], ascending=(True, False))
            payRcdTimesDf = payRcdTimesDf.groupby(['账户编号']).head(24)
            payStatus = ["G", "D", "C", "N", "M", "1", "2", "3", "4", "5", "6", "7"]
            payRcdTimesDf = payRcdTimesDf[payRcdTimesDf['还款状态'].isin(payStatus)]
            payRcdTimes = payRcdTimesDf.groupby(['账户编号'])['还款状态'].count()
                #从“贷记卡信息”中提取，剔除未激活、销户、呆账、呆帐后，各账户的还款次数统计“24个月（账户）还款状态”包含"G","D","C","N","M"及数字的个数
            creditCardAccountInfoDf.loc[creditCardAccountInfoIndex, '贷记卡24期还款记录次数'] = np.max(payRcdTimes)
# 解析被追偿信息汇总
def parseRecoveryInfoMergeDf(df):
    if not df.empty:
        recoveryMaxPayDf = df[df['债权转移时的还款状态'] !='--']
        recoveryStatusCs = df[df['账户状态'] == '催收']
        if not recoveryMaxPayDf.empty:
            briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时的最大还款状态'] = np.max(recoveryMaxPayDf['债权转移时的还款状态']);
        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时属于催收状态的账户数'] = recoveryStatusCs.index.size;
        briefInfoDf_recoveryInfoSum.loc[recoveryInfoSumIndex, '债权转移时属于催收状态的账户数/被追偿信息总数'] = round(recoveryStatusCs.index.size/df.index.size,2);
        #creditTradeDetailDf_recoveryInfo
        # 被追偿账户总数
        creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex,'被追偿账户总数'] = df.index.size;
        creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '被追偿业务种类'] = df['业务种类'].unique().size;
        creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '最新一笔被追偿债券接收时间'] = np.max(df['债权接收日期']);
        creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '总债权金额'] = np.max(df['债权金额']);
        creditTradeDetailDf_recoveryInfo.loc[recoveryInfoIndex, '债权转移时的最大还款状态'] = np.max(recoveryMaxPayDf['债权转移时的还款状态']);
def main(pdf_path):
    # 解析pdf开始
    fileName = os.path.basename(pdf_path)
    print(fileName)
    with pdfplumber.open(pdf_path) as pdf:
        for p in range(0, len(pdf.pages)):
            page = pdf.pages[p]
            # first_page = pdf.pages[1]
            # if p == 3:
            #     print(3)
            tables = page.extract_tables();
            for i in range(0, len(tables)):
                table = tables[i]
                df = pd.DataFrame(table);
                # if p==12:
                #     logger.info(p)
                if len(keyList) > 1 and i == 0:  # 判断是否被分页了
                    if not utils.checkHeader(df, allHeaders):
                        key = keyList[-1];
                        dfObj = dfMap[key]
                        # dfObj["nextDf"]=df;
                        # 贷款信息 贷记卡信息  强制执行记录
                        if key == "loanDfs" or key == "creditCardDfs" or key == "creditCardDfsZ" or key == "forceExecRcdDfs" or key == 'recoveryInfoDfs' or key == "housingFundRcdDfs":  # 属于列表
                            lastDfObj = dfObj["dfs"][-1];
                            lastDfObj["isByPage"] = str(p + 1);
                            if len(dfObj["dfs"][-1]["df"].columns) == len(df.columns):  # 列数相同
                                lastDfObj["df"] = pd.concat([lastDfObj["df"], df], axis=0,ignore_index=True);  # 去最后一个进行合并
                                # print("key-" + key + "-page-" + str(p + 1) + "-" + "###列数相同####-被分页")
                            else:
                                # print("key-" + key + "-page-" + str(p + 1) + "-" + "列数不同-被分页")
                                lastDfObj["df"] = pd.concat([lastDfObj["df"], df], axis=0, ignore_index=True);
                        else:  # 查询记录明细 为单个列表
                            dfObj["isByPage"] = str(p + 1);
                            logger.info(fileName+"#"+key)
                            if len(dfObj["df"].columns) == len(df.columns):
                                # print("key-" + key + "-page-" + str(p + 1) + "-" + "###列数相同####-被分页")
                                dfObj["df"] = pd.concat([dfObj["df"], df], axis=0, ignore_index=True)
                            else:
                                # print("key-" + key + "-page-" + str(p + 1) + "-" + "列数不同-被分页")
                                dfObj["df"] = pd.concat([dfObj["df"], df], axis=0, ignore_index=True)
                                # dfObj["nextDf"] = df;
                        # 如果列数相等合并df
                        continue;
                headerList0 = df.loc[0, :].tolist()  # 第0行为表头
                headerList0 = list(filter(None, headerList0))
                
                headerList1 = []
                if df.index.size>1:
                    headerList1 = df.loc[1, :].tolist()  # 第1行为表头
                    headerList1 = list(filter(None, headerList1))

                if headerList1 == queryInfoDf_header:  # 被查询信息 第二行为数据
                    queryInfoDf = df;
                    dfKey = "queryInfoDf"
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == identity_header:  # 身份信息
                    identityDf = df[:2]  # 截取前2行
                    addressDf = df.loc[2:4,:] # 截取3到4行的第一和6
                    addressDf = addressDf.reset_index(drop=True)
                    mobileDf = utils.replaceDateColIdx(df[5:df.index.size], 5)
                    identityDf = pd.concat([identityDf, addressDf], axis=1, ignore_index=True)  # 横向合并
                    dfKey = "identityDf"
                    dfMap[dfKey]["df"] = identityDf;
                    keyList.append(dfKey);
                    # 组装电话号码df
                    dfMap[dfKey]["mobileDf"] = mobileDf
                elif headerList0 == mateDf_header:  # 配偶信息
                    mateDf = df;
                    dfKey = "mateDf"
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == liveInfoDf_header:  # 居住信息
                    mateDf = df;
                    dfKey = "liveInfoDf"
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == occupationInfo_header:  # 职业信息 可能存在分页
                    occupationDf = df;
                    dfKey = "occupationDf"
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                # elif headerList0 == queryInfoBrief_header0 and headerList1 == queryInfoBrief_header1:  # 查询信息概要 第二行为数据
                #     queryInfoBriefDf = df;
                #     dfKey = "queryInfoBriefDf"
                #     dfMap[dfKey]["df"] = df;
                #     keyList.append(dfKey);
                elif headerList0 == loanTradeInfo_header:  # 信贷交易信息
                    loanTradeInfoDf = df;
                    dfKey = "loanTradeInfoDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList1 == recoveryInfoSumDf_header:  # 被追偿信息汇总
                    recoveryInfoSumDf = df;
                    dfKey = "recoveryInfoSumDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList1 == badDebtsInfoSumDf_header:  # 呆账信息
                    badDebtsInfoSumDf = df;
                    dfKey = "badDebtsInfoSumDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList1 == overdueInfoSumDf_header:  # 逾期透资信息汇总
                    overdueInfoSumDf = df;
                    dfKey = "overdueInfoSumDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == loanAccountInfoSumDf_header0 and headerList1 == loanAccountInfoSumDf_header1:  # 非循环贷账户信息汇总
                    loanAccountInfoSumDf = df;
                    dfKey = "loanAccountInfoSumDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == creditCardInfoSumDf_header0 and headerList1 == creditCardInfoSumDf_header1:  # 贷记卡信息汇总
                    creditCardInfoSumDf = df;
                    dfKey = "creditCardInfoSumDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == creditCardInfoSumDfZ_header0 and headerList1 == creditCardInfoSumDfZ_header1:  # 准贷记卡信息汇总 目前没有数据
                    dfKey = "creditCardInfoSumDfZ";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == repaymentSumDf_header0:#相关还款责任汇总
                    dfKey = "repaymentSumDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == publicInfoBriefDf_header0:  #公共信息概要
                    dfKey = "publicInfoBriefDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == queryRecordSumDf_header0:#查询记录汇总
                    dfKey = "queryRecordSumDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == loan_header:  # 贷款账户 包括循环贷，非循环贷 循环额度下分账户
                    dfKey = "loanDfs";
                    dfMap[dfKey]["dfs"].append({"df": df});
                    keyList.append(dfKey);
                elif headerList0 == creditCard_header:  # 贷记卡账户
                    dfKey = "creditCardDfs";
                    dfMap[dfKey]["dfs"].append({"df": df});
                    keyList.append(dfKey);
                elif headerList0 == creditCardZ_header:  # 准贷记卡账户 还不能和贷记卡合并
                    dfKey = "creditCardDfsZ";
                    dfMap[dfKey]["dfs"].append({"df": df});
                    keyList.append(dfKey);
                elif headerList0 == queryRecordDetailDf_header:  # 查询记录明细
                    dfKey = "queryRecordDetailDf";
                    dfMap[dfKey]["df"] = df;
                    keyList.append(dfKey);
                elif headerList0 == housingFundRcdDfs_header:  # 查询记录明细
                    dfKey = "housingFundRcdDfs";
                    dfMap[dfKey]["dfs"].append({"df": df});
                    keyList.append(dfKey);
                elif headerList0 == forceExecRcdDfs_header:  # 强制执行记录
                    dfKey = "forceExecRcdDfs";
                    dfMap[dfKey]["dfs"].append({"df": df});
                    keyList.append(dfKey);
                elif headerList0 == recoveryInfoDfs_header:  # 被追偿信息
                    dfKey = "recoveryInfoDfs";
                    dfMap[dfKey]["dfs"].append({"df": df});
                    keyList.append(dfKey);

                # 设置分页
                dfMap[dfKey]["page"] = p + 1;
        logger.info(fileName+"#"+"组装pdf数据完成")
        logger.info(fileName+"#"+"解析基础pdf数据开始")
        # 打印结果解析并构建指标
        for key in dfMap:
            tempDfObjx = dfMap[key];
            if tempDfObjx.__contains__("page"):
                logger.info(fileName+"#"+key + "-page-" + str(tempDfObjx["page"]))
            if tempDfObjx.__contains__("dfs"):
                if key == "loanDfs":  # 贷款账户
                    for idx in range(0, len(tempDfObjx["dfs"])):
                        tempDfObj = tempDfObjx["dfs"][idx];
                        loanAccountDfs.append(dfParser.mergeLoanDf(tempDfObj, idx,queryInfo['reportTime']))
                elif key == "creditCardDfs":  # 贷记卡账户合并
                    for idx in range(0, len(tempDfObjx["dfs"])):
                        tempDfObj = tempDfObjx["dfs"][idx];
                        tempCreditCardDf = dfParser.mergeCreditCardDf(tempDfObj, idx,queryInfo['reportTime']);
                        if tempCreditCardDf!=None:
                            creditCardAccountDfs.append(tempCreditCardDf)
                elif key == "creditCardDfsZ":  # 贷记卡账户合并
                    for idx in range(0, len(tempDfObjx["dfs"])):
                        tempDfObj = tempDfObjx["dfs"][idx];
                        tempCreditCardDfZ = dfParser.mergeCreditCardDfZ(tempDfObj, idx,queryInfo['reportTime'])
                        if tempCreditCardDfZ!=None:
                            creditCardAccountDfsZ.append(tempCreditCardDfZ)
                elif key == "recoveryInfoDfs":  # 贷记卡账户合并
                    for idx in range(0, len(tempDfObjx["dfs"])):
                        tempDfObj = tempDfObjx["dfs"][idx];
                        recoveryInfoAccountDfs.append(dfParser.mergeRecoveryInfoDf(tempDfObj, idx, queryInfo['reportTime']))
                elif key == "housingFundRcdDfs":  # 贷记卡账户合并
                    for idx in range(0, len(tempDfObjx["dfs"])):
                        tempDfObj = tempDfObjx["dfs"][idx];
                        housingFundRcdAccountDfs.append(dfParser.mergeHousingFundRcdDf(tempDfObj, idx, queryInfo['reportTime']))
                else:  # 其他
                    for tempDfObj in (tempDfObjx["dfs"]):
                        if tempDfObj.__contains__("isByPage"):
                            logger.info(fileName+"#"+key + "============其他被分页页数============" + str(tempDfObj["isByPage"]))
                        # logger.info(fileName+"#"+tempDfObj["df"].values)
            else:  # 单笔
                tempDfObj = tempDfObjx;
                if tempDfObj.__contains__("isByPage"):
                    logger.info(fileName+"#"+key + "============被分页页数================" + str(tempDfObj["isByPage"]))
                # logger.info(fileName+"#"+tempDfObj["df"].values)
                if key == "queryInfoDf":  # 解析被查询信息
                    parseQueryInfo(tempDfObj);
                    # print("\033[1;31m +查询信息+ \033[0m")
                    # print(queryInfo)
                elif key == "identityDf":  # 身份信息
                    parseIdentity(tempDfObj)
                    # print("\033[1;31m +身份信息+ \033[0m")
                    # print(identity)
                elif key == "mateDf":  # 配偶信息
                    parseMate(tempDfObj)
                    # print("\033[1;31m +配偶信息+ \033[0m")
                    # print(mate)
                elif key == "liveInfoDf":  # 居住信息
                    parseLiveInfo(tempDfObj)
                    # print("\033[1;31m +居住信息+ \033[0m")
                elif key == "occupationDf":  # 居住信息
                    parseOccupationInfoDf(tempDfObj)
                elif key == "loanTradeInfoDf":  # 信贷交易信息提示
                    parseLoanTradeInfo(tempDfObj);
                    # print("\033[1;31m +信贷交易信息提示+ \033[0m")
                    # print(loanTradeInfo)
                elif key == "badDebtsInfoSumDf":  # 呆账信息汇总
                    parseBadDebtsInfoSumDf(tempDfObj)
                    # print("\033[1;31m +呆账信息汇总+ \033[0m")
                    # print(overdueBrief)
                elif key == "recoveryInfoSumDf":  # 被追偿信息汇总-资产处置和垫款
                    parseRecoveryInfoSum(tempDfObj)
                    # print("\033[1;31m +资产处置和垫款+ \033[0m")
                    # print(overdueBrief)
                elif key == "overdueInfoSumDf":  # 逾期（透支）信息汇总
                    parseOverdueInfoSum(tempDfObj)
                    # print("\033[1;31m +逾期（透支）信息汇总+ \033[0m")
                    # print(overdueInfo)
                elif key == "loanAccountInfoSumDf":  # 非循环贷账户信息汇总 TODO
                    parseLoanAccountInfoSum(tempDfObj)
                elif key == "cycleCreditAccountInfoSumDf":#循环额度
                    parseCycleCreditAccountInfoSum(tempDfObj)
                elif key == "cycleLoanAccountInfoSumDf":#循环贷
                    parseCyleLoanAccountInfoSum(tempDfObj)
                elif key == "creditCardInfoSumDf":#贷记卡
                    parseCreditCardInfoSum(tempDfObj)
                elif key == "creditCardInfoSumDfZ":  # 准贷记卡
                    parseCreditCardInfoSumZ(tempDfObj)
                elif key == "repaymentSumDf":  # 相关还款责任
                    parseRepaymentSum(tempDfObj)
                elif key == "publicInfoBriefDf":
                    parsePublicInfoBrief(tempDfObj);
                elif key == "queryRecordSumDf":
                    parseQueryRecordSum(tempDfObj);
                elif key == "queryRecordDetailDf":  # 查询记录明细
                    parseQueryInfoDetail(tempDfObj)#
        logger.info(fileName+"#"+"解析基础pdf数据完成")
    result = "{"
    # 基本信息
    # result+=("\033[1;34m +身份信息+ \033[0m")+"\n"
    result+=utils.toJson(identityInfoDf)+","
    result += utils.toJson(mateInfoDf) + ","
    result += utils.toJson(liveInfoDf) + ","
    result += utils.toJson(occupationInfoDf) + ","
    # result+=("\033[1;34m +概要信息+ \033[0m")+","
    # result+=("\033[1;34m +信贷交易信息提示+ \033[0m")+","
    # result+=utils.toJson(briefInfoDf_loanTradeInfo)+","
    result += "briefInfoDf_loanTradeInfo" + ","  # 占位符
    # result+=("\033[1;34m +被追偿信息汇总及呆账信息汇总+ \033[0m")+","
    result+="briefInfoDf_recoveryInfoSum"+"," #占位符
    result += utils.toJson(briefInfoDf_badDebtsInfoSum) + ","
    # result+=("\033[1;34m +逾期（透支）信息汇总+ \033[0m")+","
    #此信息先占位
    result+="briefInfoDf_overdueInfoSum"+","
    # result+=("\033[1;34m +信贷交易授信及负债信息概要+ \033[0m")+","
    result+=utils.toJson(briefInfoDf_loanTradeCreditInfo)+","
    #公共信息
    result += utils.toJson(publicInfoBriefDf) + ","
    #查询记录汇总
    result += utils.toJson(queryRecordSumDf) + ","
    # 单独输出贷款df
    # logger.info(fileName+"#"+"\033[1;34m +贷款信息Dataframe+ \033[0m")
    # logger.info(fileName+"#"+dfParser.dfHeaderLoan)
    logger.info(fileName+"#"+pdf_path+"解析贷款数据开始")
    loanMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoan)
    loanPayRecordMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoanPayRecord)
    loanSpecialTradeMergeDf = pd.DataFrame(columns=dfParser.dfHeaderLoanSpecialTrade)#特殊交易
    # 输出数据
    for loanDfObj in loanAccountDfs:
        loanMergeDf = pd.concat([loanMergeDf, loanDfObj["loanDf"]], axis=0, ignore_index=True);
        loanPayRecordMergeDf = pd.concat([loanPayRecordMergeDf, loanDfObj["loanPayRecordDf"]], axis=0,ignore_index=True);
        loanSpecialTradeMergeDf = pd.concat([loanSpecialTradeMergeDf, loanDfObj["specialTradeDf"]], axis=0, ignore_index=True);
    # logger.info(fileName+"#"+loanMergeDf.values)
    # logger.info(fileName+"#"+"\033[1;34m +贷款信息还款记录Dataframe+ \033[0m")
    # logger.info(fileName+"#"+dfParser.dfHeaderLoanPayRecord)
    # logger.info(fileName+"#"+loanPayRecordMergeDf.values)
    #

    #==============================信贷交易明细 ===============================
    #被追偿信息
    # 被追偿信息合并df
    recoveryInfoMergeDf = pd.DataFrame(columns=dfParser.dfHeaderRecoveryInfo)
    for recoveryInfoDfObj in recoveryInfoAccountDfs:
        recoveryInfoMergeDf = pd.concat([recoveryInfoMergeDf, recoveryInfoDfObj["recoveryInfoDf"]], axis=0,
                                        ignore_index=True);
    parseRecoveryInfoMergeDf(recoveryInfoMergeDf);
    #被追偿信息

    result = result.replace("briefInfoDf_recoveryInfoSum", utils.toJson(briefInfoDf_recoveryInfoSum))#替换汇总中的指标
    result += utils.toJson(creditTradeDetailDf_recoveryInfo) + "," #设置占位符，由于存在概要的指标在明细中计算
    #特殊交易
    parseSpecialTrade(loanSpecialTradeMergeDf)
    result += utils.toJson(creditTradeDetailHeader_specialTrade) + ","
    # 信贷交易明细-解析非循环贷账户
    parseLoanAccountInfo(loanMergeDf);
    result += utils.toJson(creditTradeDetailDf_loanAccountInfo) + ","
    #循环额度分账户
    parseCycleCreditAccountInfo(loanMergeDf);
    result += utils.toJson(creditTradeDetailDf_cycleCreditAccountInfo) + ","
    #循环贷
    parseCycleLoanAccountInfo(loanMergeDf);
    result += utils.toJson(creditTradeDetailDf_cycleLoanAccountInfo) + ","
    # 解析贷款账户指标
    parseLoanMergeDf(loanMergeDf);

    # 解析还款记录相关指标
    parseLoanMergeAndPayRecordDf(loanMergeDf, loanPayRecordMergeDf);
    # logger.info(fileName+"#"+loanAccountInfo)
    # logger.info(fileName+"#"+consts.loanAccountInfoHeader)
    # logger.info(fileName+"#"+loanAccountInfoDf.values)

    # result+=("\033[1;34m +贷款账户信息+ \033[0m")+","
    result+=utils.toJson(loanAccountInfoDf)+","
    logger.info(fileName+"#"+"解析贷款数据完成")
    logger.info(fileName+"#"+"解析贷记卡数据开始")
    #贷记卡合并df
    creditCardMergeDf = pd.DataFrame(columns=dfParser.dfHeaderCreditCard)
    creditCardPayRecordMergeDf = pd.DataFrame(columns=dfParser.dfHeaderCreditCardPayRecord)
    # logger.info(fileName+"#"+"\033[1;34m +贷记卡信息Dataframe+ \033[0m")
    # logger.info(fileName+"#"+dfParser.dfHeaderCreditCard)
    # 输出数据
    for creditCardDfObj in creditCardAccountDfs:
        creditCardMergeDf = pd.concat([creditCardMergeDf, creditCardDfObj["creditCardDf"]], axis=0, ignore_index=True);
        creditCardPayRecordMergeDf = pd.concat([creditCardPayRecordMergeDf, creditCardDfObj["creditCardPayRecordDf"]], axis=0,ignore_index=True);
    # logger.info(fileName+"#"+creditCardMergeDf.values)
    # 解析贷记卡账户指标
    parseCreditCardMergeDf(creditCardMergeDf);
    parseCreditCardMergeAndPayRecordDf(creditCardMergeDf,creditCardPayRecordMergeDf)

    #准贷记卡合并df
    creditCardMergeDfZ = pd.DataFrame(columns=dfParser.dfHeaderCreditCardZ)
    creditCardPayRecordMergeDfZ = pd.DataFrame(columns=dfParser.dfHeaderCreditCardPayRecordZ)
    for creditCardDfObj in creditCardAccountDfsZ:
        creditCardMergeDfZ = pd.concat([creditCardMergeDfZ, creditCardDfObj["creditCardDfZ"]], axis=0, ignore_index=True);
        creditCardPayRecordMergeDfZ = pd.concat([creditCardPayRecordMergeDfZ, creditCardDfObj["creditCardPayRecordDfZ"]], axis=0,ignore_index=True);

    #解析准贷记卡相关指标
    parseCreditCardMergeDfZ(creditCardMergeDfZ,creditCardPayRecordMergeDfZ);
    logger.info(fileName+"#"+"解析贷记卡数据完成")
    #加工使用率指标


    # result+=("\033[1;34m +贷记卡账户信息+ \033[0m")+","
    result+=utils.toJson(creditCardAccountInfoDf)+","

    result += utils.toJson(creditCardAccountInfoDfZ) + ","

    #使用率

    parseUseRate()
    result += utils.toJson(useRateDf) + ","
    #开户数
    parseOpenAccount(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,recoveryInfoMergeDf,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ)
    result += utils.toJson(openAccountDf) + ","

    #24期还款状态
    parsePayRcdStatus(loanMergeDf, creditCardMergeDf, creditCardMergeDfZ,loanPayRecordMergeDf,creditCardPayRecordMergeDf,creditCardPayRecordMergeDfZ)
    result += utils.toJson(payRcdStatusDf) + ","
    #由于逾期汇总的指标再还款状态之后需要替换占位 TODO
    result = result.replace("briefInfoDf_overdueInfoSum",utils.toJson(briefInfoDf_overdueInfoSum))
    #0525 由于在开户数后，统计信贷信息概要的指标，替换占位符
    result = result.replace("briefInfoDf_loanTradeInfo", utils.toJson(briefInfoDf_loanTradeInfo))
    #公积金
    # 被追偿信息合并df
    housingFundRcdMergeDf = pd.DataFrame(columns=dfParser.dfHeaderHousingFundRcd)
    for housingFundRcdDfObj in housingFundRcdAccountDfs:
        housingFundRcdMergeDf = pd.concat([housingFundRcdMergeDf, housingFundRcdDfObj["housingFundRcdDf"]], axis=0,ignore_index=True);
    parseHousingFundRcd(housingFundRcdMergeDf);
    result += utils.toJson(housingFundRcdDf) + ","

    # result+=("\033[1;34m +查询记录明细+ \033[0m")+","
    result+=utils.toJson(queryRecordDetailDf)+""
    result +="}"
    return result;
def uploadReportResult(basePath,pdf_path):
    # ===================================
    fileName = os.path.basename(pdf_path)
    logger.info(fileName+"#"+fileName+"#"+"准备上传文件")
    uploadApiUrl = config.get("baseconf", "uploadApiUrl");
    uploadApiUrl = uploadApiUrl + "?access_token=" + dbController.getToken()
    files = {'file': open(outPath, 'rb')}
    businessNum = dbController.getBussinessNum(queryInfo["queryInfoCardId"]);  # 根据身份证获取业务编号
    logger.info(fileName+"#"+fileName+"#"+"businessNum:"+businessNum)
    logger.info(fileName+"#"+"queryInfoCardId:" + queryInfo["queryInfoCardId"])
    data = {'docType': "23", 'businessNum': businessNum}
    response = requests.post(uploadApiUrl, files=files, data=data)
    text = response.text
    p = PrpCrypt(config.get("baseconf", "AESKey"))
    # logger.info(fileName+"#"+"token:"+token)
    # logger.info(fileName+"#"+url)
    # logger.info(fileName+"#"+result.text)
    resultText = p.decrypt(text)
    logger.info(fileName+"#"+"upload_result:" + resultText)
    try:
        descPdfPath = basePath + "execed/" + os.path.basename(pdf_path)
        if not os.path.exists(basePath+"execed/"):
            os.mkdir(basePath+"execed/")
        shutil.move(pdf_path, descPdfPath)
        # shutil.move(pdf_path.replace("pdf","txt"), descPdfPath.replace("pdf","txt"))
    except:
        info = sys.exc_info()
        logger.error(info[0])
        logger.error(info[1])
        # logging.log(logging.ERROR, info[2])
        logger.error(traceback.extract_tb(info[2], 1))
def updateParseInd(file_name):
    # 检查是否存在已执行
    parseInd = "1"
    try:
        if file_name != "":
            arCert = file_name[0:-4].split("_")
            if len(arCert) == 2:
                cert_id = arCert[1]
                parseIndTmp = dbController.getParseInd(cert_id)
                if parseIndTmp =="1":
                    return True
                dbController.updateParseInd(cert_id, parseInd)
    except:
        logger.error("update parse ind error")

    return False
# grouped.to_csv(r'C:\Users\Mortal\Desktop\ex.csv',index=False, encoding='utf_8_sig')
if __name__ == '__main__':
    file_name = ""
    # basePath = "D:/mydocument/myproject/git/busscredit/20200430_report/";
    basePath = "D:/mydocument/myprojects/creditreport/parse/"
    # file_name =  "周颖500108199002111229.pdf"#准贷记卡已销户 呆账
    # file_name =  "王思13052819911012122X.pdf"#公积金
    # file_name =  "杨夏龙440902198410014270.pdf"#转出
    # file_name = "翟彦超230125199004174216.pdf"#准贷记卡 呆账
    # file_name =  "蔡月辉330326198502116146.pdf"  # 配偶
    # file_name =  "周芳芳342501198706111782.pdf" #被追偿信息
    # file_name = "付春雁533001198507220344.pdf"  # 公积金记录
    # pdf_path = basePath + "陈洁350122199005027726.pdf" #  相关还款责任
    # file_name = "叶翔_330126197005200077.pdf" #  准贷记卡分页
    file_name = "安静513723198707074647.pdf"  #
    pdf_path = basePath + file_name
    if len(sys.argv)>1:
        basePath = sys.argv[1]
        pdf_path = basePath + sys.argv[2]
        file_name = sys.argv[2]
    # print(sys.argv)
    isBat = False#批量的有问题
    isPlt = config.get("baseconf", "isPlt");
    if isBat:#批量生成数据不对
        for file in os.listdir(basePath):
            if file.endswith("pdf"):
                start = timeit.default_timer();
                pdf_path = basePath+file;
                outPath = pdf_path.replace("pdf",'txt')
                if os.path.exists(outPath):
                    continue;
                logger.info(file + "解析开始...")
                try:
                    result = main(pdf_path)
                except:
                    info = sys.exc_info()
                    logger.error(info[0])
                    logger.error( info[1])
                    # logging.log(logging.ERROR, info[2])
                    logger.error(traceback.extract_tb(info[2], 1))
                # print(result)
                #输出到文件
                sys.stdout = open(outPath, mode='w', encoding='utf-8')
                print(result.replace("\033[1;34m","").replace("\033[0m",""))
                logger.info(file+"解析完成")
                gc.collect()
                s = timeit.default_timer() - start;
                logger.info(str(s) + " 秒")
    else:
        if pdf_path.endswith("pdf"):
            start = timeit.default_timer();
            outPath = pdf_path.replace("pdf", 'txt')
            result = ""
            if isPlt == "1":#生产模式
                if not os.path.exists(outPath):#不存在才生成
                    try:
                        isExec = updateParseInd(file_name)
                        if not isExec:#没有在执行
                            logger.info(file_name + "解析开始...")
                            result = main(pdf_path)
                            sys.stdout = open(outPath, mode='w', encoding='utf-8')
                            print(result.replace("\033[1;34m", "").replace("\033[0m", ""))
                            logger.info(file_name + "解析完成")
                            s = timeit.default_timer() - start;
                            logger.info(file_name+"#"+str(s) + " 秒")
                            uploadReportResult(basePath,pdf_path);
                    except:
                        info = sys.exc_info()
                        logger.error(file_name+"#"+"解析失败")
                        logger.error(info[0])
                        logger.error(info[1])
                        logger.error(traceback.extract_tb(info[2]))
            else:
                isExec = updateParseInd(file_name)
                if not isExec:  # 没有在执行
                    result = main(pdf_path)
                    sys.stdout = open(outPath, mode='w', encoding='utf-8')
                    print(result.replace("\033[1;34m", "").replace("\033[0m", ""))
                    logger.info(file_name + "解析完成")
                    s = timeit.default_timer() - start;
                    logger.info(file_name+"#"+str(s) + " 秒")
                    # uploadReportResult(basePath,pdf_path);