def fyzf_Excel():
beg_date = request.form["beg_date"] + " 00:00:00"
end_date = request.form["end_date"] + " 23:59:59"
is_paid = request.form["is_paid"]
sql = "SELECT b.name,c.real_name,d.project_name,a.amount,a.`describe`,a.create_date,(case is_paid when '0' then '未支付' \
when '1' then '已支付' end) as paid,a.paid_date FROM oa_reimbursement a, oa_org b, oa_user c,oa_project d WHERE\
a.org_id = b.id AND a.create_user = c.id and a.project_id=d.id"
sql += " and a.create_date between '" + beg_date + "' and '" + end_date + "'"
if float(is_paid) != -1:
sql += " and a.is_paid=" + str(is_paid)
sql += "order by a.paid_date"
data = db.session.execute(sql).fetchall()
exl_hdngs = ["费用所属单位", "申请人", "项目", "金额", "报销事由", "创建时间", "审批状态", "支付时间"]
types = "text text text text text datetime text datetime".split()
exl_hdngs_xf = ezxf("font: bold on;align: wrap on,vert centre,horiz center")
types_to_xf_map = {
"int": ezxf(num_format_str="#,##0"),
"date": ezxf(num_format_str="yyyy-mm-dd"),
"datetime": ezxf(num_format_str="yyyy-mm-dd HH:MM:SS"),
"ratio": ezxf(num_format_str="#,##0.00%"),
"text": ezxf(),
"price": ezxf(num_format_str="￥#,##0.00"),
}
data_xfs = [types_to_xf_map[t] for t in types]
date = datetime.datetime.now()
year = date.year
month = date.month
day = date.day
filename = str(year) + "_" + str(month) + "_" + str(day) + "_" + "报销费用支付详情" + ".xls"
exp = export_excel()
return exp.export_download(filename, "报销费用支付详情表", exl_hdngs, data, exl_hdngs_xf, data_xfs)
def download_loan_banlance():
data=db.engine.execute("select * from view_loan_balance")
exl_hdngs=['贷款编号','客户名称','放款日期','贷款金额','贷款余额','已还本金','已还利息','贷款类型','利率','负责客户经理']
types= 'int text date price price price price text ratio text'.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
#码值转换
for row in data:
if row['loan_type']=='1':
row['loan_type']='微贷'
elif row['loan_type']=='2':
row['loan_type']='小额贷'
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'贷款余额统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'贷款余额统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def dkgjztfl_4_export():
customer_name = request.form['customer_name']
loan_type = request.form['loan_type']
sql = " loan_status ='5'"
if loan_type != '0':
sql = "loan_type='"+loan_type+"' and "
if customer_name:
sql += " and customer_name like '%"+customer_name+"%'"
data = View_Bank_Loans_Main.query.filter(sql)
exl_hdngs=['贷款编号','客户名称','利率','放款日期','贷款金额','负责客户经理','贷款状态']
type_str = 'text text text date text text text'#1
types= type_str.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'到期终止的贷款统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'到期终止的贷款统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def download_loan_disbursed():
data = db.engine.execute("select * from view_loan_disbursed")
exl_hdngs = ["贷款编号", "客户名称", "贷款金额", "年利率", "放款日期", "贷款状态", "负责客户经理"]
types = "int text price ratio date text text".split()
exl_hdngs_xf = ezxf("font: bold on;align: wrap on,vert centre,horiz center")
types_to_xf_map = {
"int": ezxf(num_format_str="#,##0"),
"date": ezxf(num_format_str="yyyy-mm-dd"),
"ratio": ezxf(num_format_str="#,##0.00%"),
"text": ezxf(),
"price": ezxf(num_format_str="￥#,##0.00"),
}
data_xfs = [types_to_xf_map[t] for t in types]
# 码值转换
for row in data:
if row["loan_status"] == "11":
row["loan_status"] = "贷款期"
elif row["loan_status"] == "12":
row["loan_status"] = "结清"
elif row["loan_status"] == "13":
row["loan_status"] = "注销"
date = datetime.datetime.now()
year = date.year
month = date.month
day = date.day
filename = str(year) + "_" + str(month) + "_" + str(day) + "_" + "已发放贷款统计表" + ".xls"
exp = export_excel()
return exp.export_download(filename, "已发放贷款统计表", exl_hdngs, data, exl_hdngs_xf, data_xfs)
def dhgl_export_bz():
# 模糊查询
customer_name = request.form["customer_name"]
loan_type = request.form["loan_type"]
sql = "select sc_loan_apply.customer_name,sc_user.real_name,sc_monitor.monitor_date,"
sql += "sc_monitor.monitor_type,sc_monitor.monitor_remark from sc_monitor left join "
sql += "(select id,loan_type,customer_name from sc_loan_apply where "
if loan_type != "0":
sql += "loan_type='" + loan_type + "' and "
sql += " process_status='" + PROCESS_STATUS_DKFKJH + "'"
role = SC_UserRole.query.filter_by(user_id=current_user.id).first().role
if role.role_level >= 2:
sql += (
" and (A_loan_officer="
+ str(current_user.id)
+ " or B_loan_officer="
+ str(current_user.id)
+ " or yunying_loan_officer="
+ str(current_user.id)
+ ")"
)
if customer_name:
sql += " and customer_name like '%" + customer_name + "%'"
sql += ")sc_loan_apply on sc_monitor.loan_apply_id = sc_loan_apply.id "
sql += "left join sc_user on sc_monitor.create_user = sc_user.id"
data = db.session.execute(sql)
# for row in data:
# row['reception_type'] = my_dic['reception_type'][str(dic['reception_type'])]
exl_hdngs = ["客户名称", "客户经理", "日期", "监控方式", "备注"]
type_str = "text text date text text"
types = type_str.split()
exl_hdngs_xf = ezxf("font: bold on;align: wrap on,vert centre,horiz center")
types_to_xf_map = {
"int": ezxf(num_format_str="#,##0"),
"date": ezxf(num_format_str="yyyy-mm-dd"),
"datetime": ezxf(num_format_str="yyyy-mm-dd HH:MM:SS"),
"ratio": ezxf(num_format_str="#,##0.00%"),
"text": ezxf(),
"price": ezxf(num_format_str="￥#,##0.00"),
}
data_xfs = [types_to_xf_map[t] for t in types]
date = datetime.datetime.now()
year = date.year
month = date.month
day = date.day
filename = str(year) + "_" + str(month) + "_" + str(day) + "_" + "标准监控统计表" + ".xls"
exp = export_excel()
return exp.export_download(filename, "标准监控统计表", exl_hdngs, data, exl_hdngs_xf, data_xfs)
def cust_mgr_search_result(page):
if request.method=='GET':
user_id=request.args.get('yunying_loan_officer',None)
s_beg_date=str(request.args.get('beg_date','1999-01-01'))
s_end_date=str(request.args.get('end_date','2999-01-01'))
r_beg_date = re.search('\d\d\d\d-\d\d-\d\d', s_beg_date).group()
r_end_date = re.search('\d\d\d\d-\d\d-\d\d', s_end_date).group()
beg_date=datetime.datetime.strptime(r_beg_date,'%Y-%m-%d')
end_date=datetime.datetime.strptime(r_end_date,'%Y-%m-%d')
end_date=end_date.replace(hour=23,minute=59,second=59)
data=sc_day_work.get_data_by_conditions(page,beg_date,end_date,user_id,current_user.id,'PAGE')
return render_template('/Performance/gsjl/gsjl.html',data=data,beg_date=beg_date,end_date=end_date,user_id=user_id)
elif request.method=='POST':
user_id=request.form['yunying_loan_officer']
s_beg_date=str(request.form['beg_date'])
s_end_date=str(request.form['end_date'])
beg_date=datetime.datetime.strptime(s_beg_date,'%Y-%m-%d %H:%M:%S')
end_date=datetime.datetime.strptime(s_end_date,'%Y-%m-%d %H:%M:%S')
end_date=end_date.replace(hour=23,minute=59,second=59)
data=sc_day_work.get_data_by_conditions(page,s_beg_date,s_end_date,user_id,current_user.id)
exl_hdngs=['日期','姓名','工作类型','工作名称','工作开始时间','工作结束时间','耗时','备注']
types= 'date text text text datetime datetime text text'.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'客户经理工时统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'客户经理工时统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def dkgjztfl_1_export():
customer_name = request.form["customer_name"]
loan_type = request.form["loan_type"]
loan_status = request.form["loan_status"]
sql = " status = '" + PROCESS_STATUS_DKFKJH + "'"
if loan_type != "0":
sql += " and loan_type='" + loan_type + "'"
if loan_status != "0":
if loan_status == "5":
sql += " and loan_status='" + loan_status + "'"
else:
sql += " and loan_status!='5'"
if customer_name:
sql += " and customer_name like '%" + customer_name + "%'"
# data = View_Bank_Loans_Main.query.filter(sql).order_by("id").all()
query_sql = "select loan_apply_id,customer_name,ratio,loan_deliver_date,amount,loan_manager,"
query_sql += "(case loan_status when 1 then '正常' when 2 then '逾期' when 3 then '–非应计' "
query_sql += "when 5 then '结清' when 6 then '部分逾期' end)loan_status from view_bank_loans_main where "
query_sql += sql
data = db.engine.execute(query_sql)
exl_hdngs = ["贷款编号", "客户名称", "月利率", "放款日期", "贷款金额", "负责客户经理", "贷款状态"]
type_str = "text text text date text text text" # 1
types = type_str.split()
exl_hdngs_xf = ezxf("font: bold on;align: wrap on,vert centre,horiz center")
types_to_xf_map = {
"int": ezxf(num_format_str="#,##0"),
"date": ezxf(num_format_str="yyyy-mm-dd"),
"datetime": ezxf(num_format_str="yyyy-mm-dd HH:MM:SS"),
"ratio": ezxf(num_format_str="#,##0.00%"),
"text": ezxf(),
"price": ezxf(num_format_str="￥#,##0.00"),
}
data_xfs = [types_to_xf_map[t] for t in types]
date = datetime.datetime.now()
year = date.year
month = date.month
day = date.day
filename = str(year) + "_" + str(month) + "_" + str(day) + "_" + "已发放的贷款统计表" + ".xls"
exp = export_excel()
return exp.export_download(filename, "已发放的贷款统计表", exl_hdngs, data, exl_hdngs_xf, data_xfs)
def download_loan_refuse():
data=db.engine.execute("select * from view_loan_refuse")
exl_hdngs=['贷款编号','客户名称','贷款金额','申请日期','拒绝日期','拒绝原因','拒绝人']
types= 'int text price date date text text'.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'拒绝贷款统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'拒绝贷款统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def download_loan_repayment():
data=db.engine.execute("select * from view_loan_repayment")
exl_hdngs=['实际还款日期','贷款编号','客户名称','还款总额','本金','利息','期数','利率','负责客户经理']
types= 'date text text price price price int ratio text'.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'贷款还款统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'贷款还款统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def fytj_Excel():
beg_date = request.form['beg_date'] + " 00:00:00"
end_date = request.form['end_date'] + " 23:59:59"
is_paid = request.form['is_paid']
node_id = request.form['node_id']
node_type = request.form['node_type']
sql = "SELECT b.name,c.real_name,d.project_name,a.amount,a.`describe`,a.create_date,(case is_paid when '0' then '未支付' \
when '1' then '已支付' end) as paid,a.paid_date FROM oa_reimbursement a, oa_org b, oa_user c,oa_project d WHERE\
a.org_id = b.id AND a.create_user = c.id and a.project_id=d.id"
if is_paid != '-1':
sql += " and a.is_paid = "+is_paid
sql += " and a.create_date between '" + beg_date + "' and '" + end_date + "'"
sql += " and a.project_id in ("
ids = recursion.get_recursion_prjs(node_id, node_type)
for obj in ids:
sql += str(obj) + ","
sql += "-1)"
data=db.session.execute(sql).fetchall()
exl_hdngs=['费用所属单位','申请人','项目','金额','报销事由','创建时间','审批状态','支付时间']
types= 'text text text text text datetime text datetime'.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'报销费用支付详情'+'.xls'
exp=export_excel()
return exp.export_download(filename,'报销费用支付详情表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def dkgjztfl_2_export():
customer_name = request.form['customer_name']
loan_type = request.form['loan_type']
sql = " 1=1 "
if loan_type != '0':
sql += " and loan_type='"+loan_type+"'"
sql += " and (loan_status='"+PROCESS_STATUS_DKSQSH_JUJUE+"' or loan_status='"+PROCESS_STATUS_DKSP_JUJUE+"' or loan_status='"+PROCESS_STATUS_SPJY_JUJUE+"')"
if customer_name:
sql += " and customer_name like '%"+customer_name+"%'"
#data = View_Loan_Disbursed.query.filter(sql)
query_sql = "select id,create_date,loan_manager,loan_amount_num,'' as refuse_date,'' as refuse_reason,customer_name "
query_sql += "from view_loan_disbursed where "
query_sql += sql
data=db.engine.execute(query_sql)
exl_hdngs=['贷款编号','申请日期','拒绝人','贷款金额','拒绝日期','拒绝原因','客户名称']
type_str = 'text text text date text text text'#1
types= type_str.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'拒绝的贷款统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'拒绝的贷款统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def dkgjztfl_5_export():
customer_name = request.form['customer_name']
loan_type = request.form['loan_type']
sql = " 1=1"
if loan_type != '0':
sql = "loan_type='"+loan_type+"' and "
if customer_name:
sql += " and customer_name like '%"+customer_name+"%'"
#data = View_Bank_Loans_Main.query.filter(sql)
query_sql = "select loan_apply_id,customer_name,ratio,loan_deliver_date,amount,loan_manager,"
query_sql += "loan_balance from view_bank_loans_main where "
query_sql += sql
data=db.engine.execute(query_sql)
exl_hdngs=['贷款编号','客户名称','月利率','放款日期','贷款金额','负责客户经理','贷款余额']
type_str = 'text text text date text text text'#1
types= type_str.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'贷款余额统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'贷款余额统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def dkgjztfl_7_export():
customer_name = request.form['customer_name']
loan_type = request.form['loan_type']
sql = " 1=1"
if loan_type != '0':
sql = " and loan_type='"+loan_type+"' "
if customer_name:
sql += " and customer_name like '%"+customer_name+"%'"
#data = View_Loan_Expected.query.filter(sql)
query_sql = "select clear_date,customer_name,itelephone,loan_account,total,principal,interest,"
query_sql += "ratio,installmenst,loan_manager from view_loan_expected where "
query_sql += sql
data=db.engine.execute(query_sql)
exl_hdngs=['还贷日期','客户名称','电话','还款帐号','还款总额','应还本金','应还利息','贷款利率','还款顺序号','信贷员']
type_str = 'date text text text text text text text text text'#1
types= type_str.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'预期的贷款统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'预期的贷款统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def goto_export_customer_info(belong_customer_type):
if belong_customer_type == 'Company':
print 'do nothing'
else :
sql = "select 'Ind01' as zjlx,credentials_no,'321281055' as ghjg,customer_name,'L025503' as ghrbh,"#1
sql += "'356' as hjbh,if(sex='1','1','2') as xb,birthday,residence_address,'1' as sfhz,"#2
sql += "'71' as jtjs,'2' as sfgd,'2' as sfsy,'2' as sfgxr,'01' as mz,"#3
sql += "'04' as zzmm,if(marriage='0','10','20') as hyzk,'001' as jkzk,'10' as zgxl,'1' as zgxw,'' as gzdw,"#4
sql += "'1' as zw,'1' as zc,'' as dzm,'1' as sfxyh,residence,"#5
sql += "'1' as sfxyx,residence_address as xzcmc,'1' as sfxyc,living_conditions,home_address,"#6
sql += "zip_code,telephone,mobile,'' as email,profession,"#7
sql += "'2' as sfzhsx,'' as zhsxyy,'' as ydtkh,'' as jszh,'' as tdmj,"#8
sql += "create_date,'' as wycs,'' as ckye,'' as yyck,'010' as jjms,"#9
sql += "'' as jygm,'' as jydz,'' as jyxm, '' as xqm,'' as fczh,"#10
sql += "'' as fwmj,'' as jqxydj,'' as jqpjsj,'' as bz"#11
sql += " from sc_individual_customer where is_have_export = '0'"
data=db.engine.execute(sql)
exl_hdngs = ['证件类型(*)','证件号码(*)','管户机构(*)','姓名(*)','管户人编号(*)', #1
'户籍编号（*）','性别（*）','出生日期（*）','户籍地址','是否为户主（*）',#2
'家庭角色','是否本行/社股东','是否本行/社员工','是否本行/社其他关系人','民族',#3
'政治面貌','婚姻状况','健康状况','最高学历','最高学位','工作单位名称',#4
'职务','职称','地址码','是否信用户','所属行政乡(镇)',#5
'是否信用乡（镇）','所属行政村名称','是否信用村','居住状况','居住地址',#6
'居住地址邮编','住宅电话','联系号码（短信提醒）','电子邮箱','职业（国标）',#7
'是否暂缓授信','暂缓授信原因','易贷通卡号','本行/社结算账户','承包土地面积',#8
'与我行首次建立信贷关系时间','违约次数','一年日均存款金额','预约存款','经营模式',#9
'经营规模','经营地址','主要经营项目及收入来源','房屋小区名','房产证号',#10
'房屋面积','即期信用等级','即期评级时间','备注']#11
type_str = 'text text text text text'#1
type_str += ' text text date text text'#2
type_str += ' text text text text text'#3
type_str += ' text text text text text text'#4
type_str += ' text text text text text'#5
type_str += ' text text text text text'#6
type_str += ' text text text text text'#7
type_str += ' text text text text text'#8
type_str += ' date text text text text'#9
type_str += ' text text text text text'#10
type_str += ' text text date text'#11
types = type_str.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy/mm/dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'客户信息表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'客户信息表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
def export_lfdj():
#模糊查询
manager = request.form['manager']
customer_name = request.form['customer_name']
beg_date = request.form['beg_date'] + " 00:00:00"
end_date = request.form['end_date'] + " 23:59:59"
sql = "SELECT sc_user.real_name,"
sql += "(case reception_type when '1' then '咨询' when '2' then '扫街' when '3' then '转介绍' end)reception_type ,sc_target_customer.create_date,"
sql += "(case yingxiao_status when 1 then '已营销' when 0 then '未营销' end)yingxiao_status,"
sql += "(case client_status when 12 then '老板不在员工不提供有效信息' when 13 then '店铺关门或转让中' "
sql += "when 1 then '现在有需求' when 2 then '态度良好无需求拒绝' when 3 then '态度恶劣拒绝' "
sql += "when 14 then '有需求但有管户问题' when 4 then '以后会有需求并填回执' when 5 then '以后有需求未填回执' "
sql += "when 15 then '有需求但认为利率高' when 16 then '现在有需求并填申请' "
sql += "when 6 then '有需求但不符要求-年龄不符合要求' when 7 then '有需求但不符要求-经营年限不足一年' "
sql += "when 8 then '有需求但不符要求-外地人在本地居住低于两年' when 9 then '有需求但不符要求-家属不同意' "
sql += "when 10 then '有需求但不符要求-有不良嗜号' when 11 then '有需求但不符要求-其他' end)client_status,"
sql += "(case is_apply_form when 1 then '已申请' when 0 then '未申请' end)is_apply_form,"
sql += "remark,customer_name,sc_target_customer.mobile,"
sql += "(case sc_target_customer.sex when 1 then '男' when 0 then '女' end)sex,"
sql += "sc_target_customer.age,address,sc_industry.type_name as industry,business_content,shop_name,period, "
sql += "property_scope,monthly_sales,employees,sc_business_type.type_name as business_type,is_need_loan,sc_loan_purpose.type_name as loan_purpose,loan_amount, "
sql += "repayment_type,guarantee_type,house_property,loan_attention,"
sql += "(case is_have_loan when 1 then '是' when 0 then '否' end)is_have_loan,"
sql += "(case is_known_xhnsh when 1 then '知道' when 0 then '不知道' end)is_known_xhnsh,business_with_xhnsh,"
sql += "concat((case CONVERT(is_need_service,SIGNED)&1 when 1 then '手机银行 ' else '' end),(case CONVERT(is_need_service,SIGNED)&2 when 2 then '转账电话 ' else '' end),"
sql += "(case CONVERT(is_need_service,SIGNED)&4 when 4 then 'pos机 ' else '' end),(case CONVERT(is_need_service,SIGNED)&8 when 8 then '网上银行 ' else '' end),"
sql += "(case CONVERT(is_need_service,SIGNED)&16 when 16 then '借记卡 ' else '' end),(case CONVERT(is_need_service,SIGNED)&32 when 32 then '贷记卡 ' else '' end),"
sql += "(case CONVERT(is_need_service,SIGNED)&64 when 64 then '无需求 ' else '' end)) as is_need_service "
#sql += "status "
sql += "FROM (select * from sc_target_customer where "
sql += " 1=1"
if manager != '0':
sql += " and receiver="+manager
sql += " and create_date between '"+beg_date+"' and '"+end_date + "' "
if customer_name:
sql += " and (customer_name like '%"+customer_name+"%' or shop_name like '%"+customer_name+"%') "
sql += ")sc_target_customer INNER JOIN sc_user ON sc_target_customer.receiver = sc_user.id "
sql += "left JOIN sc_industry ON sc_target_customer.industry = sc_industry.id "
sql += "left JOIN sc_loan_purpose ON sc_target_customer.loan_purpose = sc_loan_purpose.id "
sql += "left JOIN sc_business_type ON sc_target_customer.business_type = sc_business_type.id "
data=db.engine.execute(sql)
#for row in data:
# row['reception_type'] = my_dic['reception_type'][str(dic['reception_type'])]
exl_hdngs=['营销人','营销方式','营销时间','营销状态','客户状态','是否向小微支行填写申请表？',#1
'备注','客户名称','电话','性别','年龄','地址','所属行业','经营内容',#2
'店铺名称','经营期限','资产规模','月销售额','雇员数量','企业类别',#3
'是否有贷款需求','贷款目的','贷款数额','希望的还款方式','能提供的担保方式','房产产权情况','贷款关注程度',#4
'是否在他行有借款','知道兴化农商行吗？','您在兴化农村商业银行办理过什么业务？','您是否需要办理以下银行产品']#5
type_str = 'text text date text text text text text'#1
type_str += ' text text text text text text text text'#2
type_str += ' text text text text text text'#3
type_str += ' text text text text text text text'#4
type_str += ' text text text text'#5
types= type_str.split()
exl_hdngs_xf=ezxf('font: bold on;align: wrap on,vert centre,horiz center')
types_to_xf_map={
'int':ezxf(num_format_str='#,##0'),
'date':ezxf(num_format_str='yyyy-mm-dd'),
'datetime':ezxf(num_format_str='yyyy-mm-dd HH:MM:SS'),
'ratio':ezxf(num_format_str='#,##0.00%'),
'text':ezxf(),
'price':ezxf(num_format_str='￥#,##0.00')
}
data_xfs=[types_to_xf_map[t] for t in types]
date=datetime.datetime.now()
year=date.year
month=date.month
day=date.day
filename=str(year)+'_'+str(month)+'_'+str(day)+'_'+'来访登记统计表'+'.xls'
exp=export_excel()
return exp.export_download(filename,'来访登记统计表',exl_hdngs,data,exl_hdngs_xf,data_xfs)
