Skip to content

Commit 6826cf2

Browse files
author
=
committed
解决中文乱码
1 parent 236d089 commit 6826cf2

4 files changed

Lines changed: 230 additions & 11 deletions

File tree

‎datebase/loan_after_sql.py‎

Lines changed: 153 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,153 @@
1+
# -*- coding:utf-8 -*-
2+
"""
3+
用来保存贷后SQL
4+
"""
5+
# 每日到期订单
6+
expired_daily = """
7+
SELECT tmp.loanDate AS '放款日期',
8+
sum((CASE WHEN (tmp.paymentNum = 1 AND datediff(now(),tmp.repaymentDate)>0) THEN 1 ELSE 0 END)) AS '到期笔数1',
9+
sum((CASE WHEN (tmp.paymentNum = 2 AND datediff(now(),tmp.repaymentDate)>0) THEN 1 ELSE 0 END)) AS '到期笔数2',
10+
sum((CASE WHEN (tmp.paymentNum >= 3 AND datediff(now(),tmp.repaymentDate)>0) THEN 1 ELSE 0 END)) AS '到期笔数3+'
11+
FROM ( SELECT
12+
(CASE WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NOT NULL AND lo.`status` = 1000 )
13+
THEN datediff(lo.`actual_repayment_date`, lo.`repayment_date` )
14+
WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NULL ) THEN datediff(now(),lo.repayment_date)
15+
WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NOT NULL AND lo.`status` != 1000 )
16+
THEN datediff(now(),lo.repayment_date)
17+
ELSE '0' END) AS 'dueDays',
18+
DATE_FORMAT(lo.`loan_date`, '%y-%m-%d') AS 'loanDate',
19+
lo.`repayment_date` AS 'repaymentDate',
20+
(CASE WHEN (lo.due = 1) THEN '是'
21+
WHEN (lo.due = 0) THEN '否'
22+
ELSE '未知' END) AS 'isDUe',
23+
(SELECT COUNT(a.id) FROM pdl_loan_order a WHERE a.user_id = lo.user_id
24+
AND a.`create_time` > '2017-02-16 00:00:00' AND a.`create_time` <= lo.create_time
25+
AND a.status IN (30,35,-60,-100,1000)) AS 'paymentNum'
26+
FROM
27+
`pdl_loan_order` lo
28+
29+
WHERE lo.`create_time` >= '2017-02-16 00:00:00' AND lo.`loan_date` >= '2017-07-01'
30+
AND lo.`loan_date` <=date_sub(curdate(),INTERVAL 0 DAY)
31+
AND lo.`user_id` NOT IN('8688485082575304' ,'8688485139642602','8688487288128300','8688494921401201',
32+
'8688496375422203','8688485081906005','8688495287722601','8688485081962305','8688493352520000',
33+
'8688489997182404','8688490150744709','8688490174210505','8688490254451201','8688492163295307',
34+
'8688498541093603','8688493352858707') AND lo.`status` IN (30,35,-60,-100,1000) ) AS tmp
35+
WHERE loanDate IS NOT NULL
36+
GROUP BY loanDate
37+
ORDER BY loanDate ASC;
38+
"""
39+
40+
# 到期一个月以上
41+
expired_one_month_more = """
42+
SELECT tmp.loanDate AS '放款月份',
43+
sum((CASE WHEN (tmp.paymentNum= 1) THEN 1 ELSE 0 END)) AS '放款1',
44+
sum((CASE WHEN (tmp.paymentNum= 2) THEN 1 ELSE 0 END)) AS '放款2',
45+
sum((CASE WHEN (tmp.paymentNum >= 3) THEN 1 ELSE 0 END)) AS '放款3+',
46+
sum((CASE WHEN (tmp.paymentNum = 1 AND datediff(now(),tmp.repaymentDate)>30) THEN 1 ELSE 0 END)) AS '到期一个月以上笔数1',
47+
sum((CASE WHEN (tmp.paymentNum = 2 AND datediff(now(),tmp.repaymentDate)>30) THEN 1 ELSE 0 END)) AS '到期一个月以上笔数2',
48+
sum((CASE WHEN (tmp.paymentNum >= 3 AND datediff(now(),tmp.repaymentDate)>30) THEN 1 ELSE 0 END)) AS '到期一个月以上笔数3+'
49+
FROM ( SELECT
50+
(CASE WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NOT NULL AND lo.`status` = 1000 )
51+
THEN datediff(lo.`actual_repayment_date`, lo.`repayment_date` )
52+
WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NULL ) THEN datediff(now(),lo.repayment_date)
53+
WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NOT NULL AND lo.`status` != 1000 )
54+
THEN datediff(now(),lo.repayment_date)
55+
ELSE '0' END) AS 'dueDays',
56+
DATE_FORMAT(lo.`loan_date`, '%y-%m') AS 'loanDate',
57+
lo.`repayment_date` AS 'repaymentDate',
58+
(SELECT COUNT(a.id) FROM pdl_loan_order a WHERE a.user_id = lo.user_id
59+
AND a.`create_time` > '2017-02-16 00:00:00' AND a.`create_time` <= lo.create_time
60+
AND a.status IN (30,35,-60,-100,1000)) AS 'paymentNum'
61+
FROM
62+
`pdl_loan_order` lo
63+
64+
WHERE lo.`create_time` >= '2017-02-16 00:00:00' AND lo.`loan_date` <=date_sub(curdate(),INTERVAL 0 DAY)
65+
AND lo.`user_id` NOT IN('8688485082575304' ,'8688485139642602','8688487288128300','8688494921401201',
66+
'8688496375422203','8688485081906005','8688495287722601','8688485081962305','8688493352520000',
67+
'8688489997182404','8688490150744709','8688490174210505','8688490254451201','8688492163295307',
68+
'8688498541093603','8688493352858707') AND lo.`status` IN (30,35,-60,-100,1000) ) AS tmp
69+
WHERE loanDate IS NOT NULL
70+
GROUP BY loanDate
71+
ORDER BY loanDate ASC;
72+
"""
73+
74+
# 到期
75+
expired_all = """
76+
SELECT tmp.loanDate AS '放款月份',
77+
sum((CASE WHEN (tmp.paymentNum= 1) THEN 1 ELSE 0 END)) AS '放款1',
78+
sum((CASE WHEN (tmp.paymentNum= 2) THEN 1 ELSE 0 END)) AS '放款2',
79+
sum((CASE WHEN (tmp.paymentNum >= 3) THEN 1 ELSE 0 END)) AS '放款3+',
80+
sum((CASE WHEN (tmp.paymentNum = 1 AND datediff(now(),tmp.repaymentDate)>0) THEN 1 ELSE 0 END)) AS '到期笔数1',
81+
sum((CASE WHEN (tmp.paymentNum = 2 AND datediff(now(),tmp.repaymentDate)>0) THEN 1 ELSE 0 END)) AS '到期笔数2',
82+
sum((CASE WHEN (tmp.paymentNum >= 3 AND datediff(now(),tmp.repaymentDate)>0) THEN 1 ELSE 0 END)) AS '到期笔数3+'
83+
FROM ( SELECT
84+
(CASE WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NOT NULL AND lo.`status` = 1000 )
85+
THEN datediff(lo.`actual_repayment_date`, lo.`repayment_date` )
86+
WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NULL ) THEN datediff(now(),lo.repayment_date)
87+
WHEN (lo.due = 1 AND lo.`actual_repayment_date` IS NOT NULL AND lo.`status` != 1000 )
88+
THEN datediff(now(),lo.repayment_date)
89+
ELSE '0' END) AS 'dueDays',
90+
DATE_FORMAT(lo.`loan_date`, '%y-%m') AS 'loanDate',
91+
lo.`repayment_date` AS 'repaymentDate',
92+
(CASE WHEN (lo.due = 1) THEN '是'
93+
WHEN (lo.due = 0) THEN '否'
94+
ELSE '未知' END) AS 'isDUe',
95+
(SELECT COUNT(a.id) FROM pdl_loan_order a WHERE a.user_id = lo.user_id
96+
AND a.`create_time` > '2017-02-16 00:00:00' AND a.`create_time` <= lo.create_time
97+
AND a.status IN (30,35,-60,-100,1000)) AS 'paymentNum'
98+
FROM
99+
`pdl_loan_order` lo
100+
WHERE lo.`create_time` >= '2017-02-16 00:00:00' AND lo.`loan_date` <=date_sub(curdate(),INTERVAL 0 DAY)
101+
AND lo.`user_id` NOT IN('8688485082575304' ,'8688485139642602','8688487288128300','8688494921401201',
102+
'8688496375422203','8688485081906005','8688495287722601','8688485081962305','8688493352520000',
103+
'8688489997182404','8688490150744709','8688490174210505','8688490254451201','8688492163295307',
104+
'8688498541093603','8688493352858707') AND lo.`status` IN (30,35,-60,-100,1000) ) AS tmp
105+
WHERE loanDate IS NOT NULL
106+
GROUP BY loanDate
107+
ORDER BY loanDate ASC;
108+
"""
109+
110+
# 决策订单逾期率
111+
decision_sys_due_rate = """
112+
select
113+
date(loan_date) AS '放款日期',
114+
count(*) AS '放款笔数',
115+
sum((case when (datediff(now(),o.`repayment_date` )>0)then 1 else 0 end )) AS '到期笔数',
116+
sum((case when (due = 1) then 1 else 0 end )) AS '逾期笔数',
117+
o.`loan_period` AS '借款期限',
118+
o.`review_by` AS 'review_by(0:贷前,1:决策系统,2:被拒订单分流)'
119+
from `pdl_decision_sys_result` pd
120+
LEFT JOIN `pdl_loan_order` o ON pd.`order_id` = o.`id`
121+
LEFT JOIN `pdl_user_id_card` ui ON ui.`user_id` = o.`user_id`
122+
LEFT JOIN `pdl_user_basic` u ON o.`user_id` = u.`id`
123+
where o.create_time >= '2017-07-11' and o.status in(30,35,-60,-100,1000)
124+
and o.`review_by` != 0
125+
and o.`user_id` not in('8688485082575304' ,'8688485139642602','8688487288128300','8688487131293707',
126+
'8688494921401201','8688496375422203','8688485081906005','8688495287722601','8688485081962305',
127+
'8688493352520000','8688489997182404','8688490150744709','8688490174210505','8688490254451201',
128+
'8688492163295307','8688498541093603','8688493352858707')
129+
group by date(loan_date),o.`loan_period` ,o.`review_by`
130+
"""
131+
132+
# 决策订单通过率
133+
decision_sys_pass_rate = """
134+
select
135+
tmp.o_time AS '申请时间',
136+
tmp.o_count AS '申请笔数',
137+
tmp.tg_count AS '通过笔数',
138+
concat(FORMAT((tmp.tg_count/tmp.o_count)*100,2),'%') AS '通过率',
139+
tmp.review_by AS 'review_by(0:贷前,1:决策系统,2:被拒订单分流)'
140+
from ( select Date(o.`create_time` )AS 'o_time', count(*) AS 'o_count',
141+
sum((case when(o.status in (30,35,-60,-100,1000,20,25)) then 1 else 0 end)) As 'tg_count',
142+
o.`review_by` AS 'review_by'
143+
from `pdl_decision_sys_result` pd
144+
LEFT JOIN `pdl_loan_order` o ON pd.`order_id` = o.`id`
145+
LEFT JOIN `pdl_user_id_card` ui ON ui.`user_id` = o.`user_id`
146+
LEFT JOIN `pdl_user_basic` u ON o.`user_id` = u.`id`
147+
where o.`review_by` != 0
148+
and o.`user_id` not in('8688485082575304' ,'8688485139642602','8688487288128300','8688487131293707',
149+
'8688494921401201','8688496375422203','8688485081906005','8688495287722601','8688485081962305',
150+
'8688493352520000','8688489997182404','8688490150744709','8688490174210505','8688490254451201',
151+
'8688492163295307','8688498541093603','8688493352858707')
152+
group by Date(o.`create_time` ),o.`review_by` ) tmp
153+
"""

‎datebase/sql.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,5 +4,5 @@
44
"""
55

66
sql = """
7-
SELECT id, user_id FROM `pdl_loan_order` LIMIT 0, 1000
7+
SELECT id AS '订单ID', user_id AS '用户ID' FROM `pdl_loan_order` LIMIT 0, 1000
88
"""

‎mail/mail_util.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -70,7 +70,7 @@ def attach_mail(file_list, to_list, subject, content):
7070

7171
# 添加附件
7272
for attach_file in file_list:
73-
with open(attach_file, 'rb') as f:
73+
with open(str(attach_file).decode('utf-8'), 'rb') as f:
7474
mime_type, mime_encoding = mimetypes.guess_type(path.basename(attach_file))
7575
if (mime_encoding is None) and (mime_type is None):
7676
mime_type = 'application/octet-stream'

‎schedule/schedule_manager.py‎

Lines changed: 75 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2,8 +2,10 @@
22
from apscheduler.schedulers.background import BackgroundScheduler
33
from log import logger
44
from mail import mail_util
5-
from datebase import sql, db_util
5+
from datebase import sql, loan_after_sql, db_util
66
from datetime import datetime
7+
import sys
8+
import os
79

810

911
def send_mail():
@@ -20,24 +22,88 @@ def send_mail():
2022
</html>
2123
"""
2224

23-
import sys
24-
reload(sys)
25-
sys.setdefaultencoding('utf8')
2625
# 查询数据
2726
data = db_util.select(sql.sql)
28-
data['订单ID'] = data['订单ID'].astype('string')
29-
data['用户ID'] = data['用户ID'].astype('string')
30-
data.to_excel(datetime.now().strftime('%Y-%m-%d') + '-data.xls', sheet_name='Sheet1', index=False, engine='xlsxwriter', encoding='utf-8')
27+
# data['user_id'] = data['user_id'].astype('string')
28+
# data['id'] = data['id'].astype('string')
29+
reload(sys)
30+
sys.setdefaultencoding('utf8')
31+
now = datetime.now().strftime('%Y%m%d')
32+
decision_sys_due_pass_name = now + '-决策订单逾期率.xlsx'.encode('utf-8')
33+
data.to_excel(decision_sys_due_pass_name.decode('utf-8'), sheet_name='Sheet1', index=False,
34+
engine='xlsxwriter')
35+
36+
# 发送邮件
37+
result = mail_util.attach_mail([decision_sys_due_pass_name, ], to_address, sub, cont)
38+
# logger.info(result)
39+
40+
41+
def expired_daily():
42+
# 收件人
43+
to_address = ['nan.wang@htouhui.com', ]
44+
# 邮件标题
45+
sub = '基础数据和决策订单'
46+
# 邮件内容
47+
cont = """
48+
<html>
49+
<body>
50+
<h1>你好!</h1>
51+
</body>
52+
</html>
53+
"""
54+
# 设置系统编码
55+
reload(sys)
56+
sys.setdefaultencoding('utf8')
57+
now = datetime.now().strftime('%Y%m%d')
58+
59+
# 每日到期订单
60+
expired_daily_name = now + '-每日到期订单.xlsx'
61+
expired_daily_data = db_util.select(loan_after_sql.expired_daily)
62+
expired_daily_data.to_excel(expired_daily_name, sheet_name='Sheet1', index=False,
63+
engine='xlsxwriter')
64+
65+
# 到期一个月以上
66+
expired_one_month_more_name = now + '-到期一个月以上.xlsx'
67+
expired_one_month_more_data = db_util.select(loan_after_sql.expired_one_month_more)
68+
expired_one_month_more_data.to_excel(expired_one_month_more_name, sheet_name='Sheet1', index=False,
69+
engine='xlsxwriter')
3170

71+
# 到期
72+
expired_all_name = now + '-到期.xlsx'
73+
expired_all_data = db_util.select(loan_after_sql.expired_all)
74+
expired_all_data.to_excel(expired_all_name, sheet_name='Sheet1', index=False,
75+
engine='xlsxwriter')
76+
77+
# 决策订单逾期率
78+
decision_sys_due_rate_name = now + '-决策订单逾期率.xlsx'
79+
decision_sys_due_rate_data = db_util.select(loan_after_sql.decision_sys_due_rate)
80+
decision_sys_due_rate_data.to_excel(decision_sys_due_rate_name, sheet_name='Sheet1', index=False,
81+
engine='xlsxwriter')
82+
83+
# 决策订单通过率
84+
decision_sys_due_pass_name = now + '-决策订单通过率.xlsx'
85+
decision_sys_due_pass_data = db_util.select(loan_after_sql.decision_sys_pass_rate)
86+
decision_sys_due_pass_data.to_excel(decision_sys_due_pass_name, sheet_name='Sheet1', index=False,
87+
engine='xlsxwriter')
88+
89+
# files = [expired_daily_name, expired_one_month_more_name, expired_all_name, decision_sys_due_rate_name,
90+
# decision_sys_due_pass_name, ]
91+
files = [decision_sys_due_rate_name, decision_sys_due_pass_name, ]
3292
# 发送邮件
33-
result = mail_util.attach_mail([datetime.now().strftime('%Y-%m-%d') + '-data.xls', ], to_address, sub, cont)
93+
result = mail_util.attach_mail(files, to_address, sub, cont)
3494
logger.info(result)
3595

96+
# 删除文件
97+
# for file in files:
98+
# if os.path.exists(file):
99+
# os.remove(file)
100+
36101

37102
# 调度定时任务
38103
def start_schedule():
39104
scheduler = BackgroundScheduler()
40-
scheduler.add_job(send_mail, 'interval', minutes=5)
105+
scheduler.add_job(expired_daily, 'interval', minutes=5)
41106
scheduler.start()
42107

108+
43109
send_mail()

0 commit comments

Comments
 (0)