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)