# TODO：连接数据库
import pymysql
import pandas as pd
import matplotlib.pyplot as plt




# 打开数据库连接
db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='crm',
    charset='utf8'
)

# 获取游标
# cursor = db.cursor()
# sql = "select * from person where id = '%s'"
# data = ('20000006',)
# cursor.execute(sql % data)
# print(cursor.fetchall())

# 第二步：查询person、apply、call三张表
# 保存表内容，方便后续使用

# 查询person表
# person_sql = "select * from person"
# person_detail=pd.read_sql(sql=person_sql,con=db)
# print(person_detail)

# # 查询apply表
# apply_sql = "select applicant,status from apply where applicant = '%s' "
# apply_detail=pd.read_sql(sql=apply_sql,con=db)

# # 查询call表
# call_sql = "select * from call"
# call_detail=pd.read_sql(sql=call_sql,con=db)



# 任务1：查询某个用户是否有处于OVERDUE状态的进件
# 方法1: 通过代码查询
 # 获取当前借款人是否有OVERDUE状态的进件（通过代码实现）
def get_od_apply(person_id):
    apply_sql = "select applicant,status from apply"
    apply_detail = pd.read_sql(sql=apply_sql, con=db)
    apply_list = []
    apply_list.append(apply_detail)
    print(apply_list)
    # 通过apply_detail拿到'applicant','status'两列的值
    for per_id,status in apply_detail:
        if apply_detail[per_id] == person_id:
            if apply_detail[status] == "OVERDUE":
                return True
            else:
                return False

    # apply_list =
    # 遍历apply_list,找到person_id，如果有OVERDUE状态进件则返回True，否则返回False

# print(get_od_apply(20000001))    # False
# print(get_od_apply("20000035"))    # True
# 方法2: 通过sql语句查询
def get_od_apply1(person_id):
    apply_sql = "select count(*) from apply where status = 'OVERDUE' and applicant = %s"
    apply_detail = pd.read_sql(sql=apply_sql, con=db, params=[person_id])
    print(apply_detail.values)
    if apply_detail.values == 0:
        return False
    else:return True

# The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
print(get_od_apply1("20000001"))
print(get_od_apply1("20000035"))