def Report_create_pie(org,time):
exp = flash_pic()
sql ="select b.reason_name as name,sum(a.amount) as amount from oa_reimbursement a,oa_reason b where a.org_id="+org+" \
and a.reason=b.id and is_paid=1 and date_format(a.create_date,'%Y-%m')='"+time+"' GROUP BY a.reason"
data=db.session.execute(sql).fetchall()
obj = OA_Org.query.filter_by(id=org).first()
title = obj.name+"部门"+time+"月费用开支"
return exp.pie(data,title)
def Report_create_bar_3d(org,time):
exp = flash_pic()
sql = "SELECT concat(b.month,'月') AS MONTH,IFNULL(a.amount,0) FROM oa_month b LEFT JOIN \
(SELECT SUBSTR(create_date, 6, 2) as create_date, IFNULL(SUM(amount), 0) AS amount FROM oa_reimbursement\
WHERE org_id IN ( SELECT id FROM oa_org WHERE pid = "+org+" or id="+org+") AND is_paid = 1 AND SUBSTR(create_date, 1, 4) = "+time+"\
GROUP BY SUBSTR(create_date, 1, 7)) a ON b.`month` = create_date ORDER BY b.`month`"
data=db.session.execute(sql).fetchall()
column_text=[u'金额(元)']
obj = OA_Org.query.filter_by(id=org).first()
return exp.bar_3d(data,u"",column_text,obj.name,time)
def Report_create_bar_3d():
exp = flash_pic()
#测试sql
data=db.session.execute("select sc_user.real_name,sum(IF (process_status = '101', 1, 0)) AS s_1, "
"sum(IF (process_status = '201', 1, 0)) AS s_2, "
"sum(IF (process_status = '301', 1, 0)) AS s_3, "
"sum(IF (process_status = '401', 1, 0)) AS s_4, "
"sum(IF (process_status = '501', 1, 0)) AS s_5, "
"sum(IF (process_status = '601', 1, 0)) AS s_6 "
"FROM"
"(SELECT sc_user.id AS id,sc_role.role_level AS role_level,sc_user.real_name AS real_name "
"FROM sc_userrole "
"INNER JOIN sc_role ON sc_userrole.role_id = sc_role.id "
"INNER JOIN sc_user ON sc_user.id = sc_userrole.user_id "
"where sc_role.role_level > 1)sc_user "
"LEFT JOIN "
"(SELECT * FROM sc_loan_apply WHERE sc_loan_apply.create_date BETWEEN '2014-01-01' AND '2014-03-11') "
"temp ON sc_user.id = temp.marketing_loan_officer "
"GROUP BY sc_user.id").fetchall()
column_text=[u'新申请',u'申请通过',u'已调查',u'调查通过',u'已上会',u'已通过']
return exp.bar_3d(data,u"笔",column_text)
def Report_create_pieQuarter(org,year,quarter):
exp = flash_pic()
time = ""
string = ""
if quarter=='1':
time+="('"+year+"-01','"+year+"-02','"+year+"-03')"
string="第一季度"
if quarter=='2':
time+="('"+year+"-04','"+year+"-05','"+year+"-06')"
string="第二季度"
if quarter=='3':
time+="('"+year+"-07','"+year+"-08','"+year+"-09')"
string="第三季度"
if quarter=='4':
time+="('"+year+"-10','"+year+"-11','"+year+"-12')"
string="第四季度"
sql ="select b.reason_name,sum(a.amount) from oa_reimbursement a,oa_reason b where a.org_id="+org+" \
and a.reason=b.id and is_paid=1 and date_format(a.create_date,'%Y-%m') in "+time+" GROUP BY a.reason"
data=db.session.execute(sql).fetchall()
obj = OA_Org.query.filter_by(id=org).first()
title = obj.name+"部门"+year+string+"费用开支"
return exp.pie(data,title)
def Export_pic(): imageData = request.form['imageData'] exp = flash_pic() return exp.export(imageData,'myimg')