## 效率-商户情况-蜜月期商户情况（中长尾）（金额算率）分地区
import datetime
import pymysql

# 风控生产库
db1 = pymysql.connect(
    host="",
    port=,
    user="",
    passwd="",
    db="",
    charset='utf8'
)

# 核心库

db2 = pymysql.connect(
    host="",
    port=,
    user="",
    passwd="",
    db="",
    charset='utf8'
)

# 资管库

db3 = pymysql.connect(
    host="",
    port=,
    user="",
    passwd="",
    db="",
    charset='utf8'
)

# BI库

db5 = pymysql.connect(
    host="",
    port=,
    user="",
    passwd="",
    db="",
    charset='utf8'
)

db5 = pymysql.connect(

    host="",
    port=,
    user="",
    passwd="",
    db="",
    charset='utf8'
)

##业态
# cursor5 = db5.cursor()
# cursor5.execute("SELECT  DISTINCT m.code FROM master_merchant_sub m  LEFT JOIN (SELECT * FROM master_comm_code WHERE  KEY_CODE='MERCHANT_TYPE') AS  comm_code ON m.type=comm_code.CODE  WHERE merchant_code IN ('M107000010', 'M107000043') and name='植发' ")
# areas = cursor5.fetchall()
#指定地区的商户
cursor4 = db4.cursor()
cursor4.execute("select DISTINCT company_id from Bi_Mid_Ylfq_qr_group where group_name ='西南地区'")
areas = cursor4.fetchall()
#蜜月期的所有商户
cursor4 = db4.cursor()
cursor4.execute("SELECT DISTINCT merchant_number_sub FROM Report_jt_merchant_number where jt='中长尾'")
honeymoon = cursor4.fetchall()
#某地区的蜜月期商户
list_honeymoon = []
list_honeymoon_areas = []
for i in range(len(honeymoon)):
    list_honeymoon.append(honeymoon[i][0])
for i in range(len(areas)):
    if areas[i][0] in list_honeymoon:
        list_honeymoon_areas.append(areas[i][0])

end = datetime.date(2019, 6, 9)
list0=[]
list1 = []
list2 = []
list3 = []
for i in range(0, 7):
    oneday = datetime.timedelta(days=i)
    day = end + oneday
    list0.append(str(day))

for i in range(1, 8):
  oneday = datetime.timedelta(days=i)
  day = end - oneday
  list1.append(str(day))

# 风控申请金额
cursor1 = db1.cursor()
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "') AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
risk_apply_amount = cursor1.fetchone()[0]

cursor1.execute("SELECT distinct CONTRACT_NUMBER FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "')AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
risk_contract_number = cursor1.fetchall()
print(risk_contract_number)

cursor3 = db3.cursor()
cursor3.execute(
    "SELECT sum(apply_amount) FROM asset_apply WHERE NOT (approved_status = '-1'AND (INSTR(contract_number, '-') > 0 OR INSTR(contract_number, '_') > 0))"
    "AND LEFT(contract_number, 32) in " + "('" + "', '".join([i[0] for i in risk_contract_number]) + "')")
data_asset_amount = cursor3.fetchone()[0]
print('进入资方的金额:',data_asset_amount)

cursor3.execute(
    "SELECT sum(apply_amount) FROM asset_apply WHERE approved_status IN ('1','5','7','11','13','16','17','-1','-2') and  NOT (approved_status = '-1'AND (INSTR(contract_number, '-') > 0 OR INSTR(contract_number, '_') > 0)) "
    "AND LEFT(contract_number, 32) in " + "('" + "', '".join([i[0] for i in risk_contract_number]) + "')")
data_asset_approval_amount =cursor3.fetchone()[0]
print('资方通过的金额:',data_asset_approval_amount)

#出量商户数
cursor1.execute("SELECT count(distinct MERCHANT_NUMBER)  FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "') AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
merchant_apply_amount = cursor1.fetchone()[0]

cursor1.execute("SELECT distinct MERCHANT_NUMBER  FROM credit_apply WHERE date_format(apply_time, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list0]) + "') AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in"+ "('" + "', '".join([i for i in list_honeymoon_areas]) + "')")
merchant_apply_amount_detail = cursor1.fetchall()
print(merchant_apply_amount_detail)
#通过金额
cursor2 = db2.cursor()
cursor2.execute("SELECT sum(APPROVAL_AMOUNT) FROM biz_contract WHERE`STATUS` IN (1, 2, 3)"
               "AND NUMBER IN " + "('" + "', '".join([i[0] for i in risk_contract_number]) + "')")
approve_amount = cursor2.fetchone()[0]


#w-1的申请金额
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(CREATE_TIME, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list1]) + "') "+" AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in   "+ "('" + "', '".join([j for j in list_honeymoon_areas]) + "')")
amount_last_week = cursor1.fetchone()[0]

for i in range(8, 15):
  oneday = datetime.timedelta(days=i)
  day = end - oneday
  list2.append(str(day))

#w-2的申请金额
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(CREATE_TIME, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list2]) + "') "+" AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in   "+ "('" + "', '".join([j for j in list_honeymoon_areas]) + "')")
amount_last_two_week = cursor1.fetchone()[0]

for i in range(15, 22):
  oneday = datetime.timedelta(days=i)
  day = end - oneday
  list3.append(str(day))

#w-3的申请金额
cursor1.execute("SELECT sum(amount) FROM credit_apply WHERE date_format(CREATE_TIME, '%Y-%m-%d') in "+ "('" + "', '".join([i for i in list3]) + "') "+" AND PARTNER_NUMBER IN ('M107000010', 'M107000043') and MERCHANT_NUMBER in   "+ "('" + "', '".join([j for j in list_honeymoon_areas]) + "')")
amount_last_three_week = cursor1.fetchone()[0]

print("商户总数量：%s,出量商户数：%s ,申请金额：%s 通过金额：%s,w-1申请金额：%s ,w-2申请金额：%s w-3申请金额：%s"%(len(list_honeymoon_areas),merchant_apply_amount,risk_apply_amount,approve_amount,amount_last_week,amount_last_two_week,amount_last_three_week))