|
一、实现效果
如图1所示:
二、实现思路
三、具体操作
1、如图2,选择图报表引擎。
2、如图3所示,现在这里面添加一个数据源,从后端获取数据。
3、如图4,在这里添加报表,将数据源载入进来,然后画出报表的样子,单击数据源上的字段将数据源载入进表格即可。
四、相关代码(可选)
- /*必须按照如下格式返回数据,否则数据出错,params(过滤条件)可以省略*/
- function(params){
- var pkValue = Ext.value(params.BFSY_ND,"");
- var pkValueXm = Ext.value(params.SY_XMID);
- var xmcode = Ext.value(params.XM_CODE);
- var whereSql = "";
- if(JE.isEmpty(pkValueXm)){
- whereSql = "";
- }else{
- whereSql = " and ( SY_XMID = '" + pkValueXm +"' or SY_XMID IN ( SELECT BFSY_BASE_XM_ID FROM BFSY_BASE_XM WHERE SY_PARENT= '" + pkValueXm +"' ) )";
- }
- //debugger;
- if(pkValue == ''){
- /*var data = new Date();
- var data = JE.DATE.formatDate(new Date())
- pkValue = data.substring(0,4);*/
- pkValue = ''
- }
- // //debugger
- // //签约管理
- // var qyglDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT SUBSTRING(DDQY_QDRQ,1,4) nd,sum(DD_FINALPRICE) AS htje,sum(FK_YFJE) AS sfje FROM BFSY_XS_DDQY where SUBSTRING(DDQY_QDRQ,1,4) = '"+pkValue + "'"}}).rows;
- // //年度考核
- // var ndkhDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT * FROM BFSY_JH_NDKH where NDKH_YEAR = '"+pkValue+"'"}}).rows;
- // //目标成本
- // var mbcbDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT sum(MBCB_MBCB) AS mbcbsjz FROM BFSY_CB_MBCB where MBCB_ND_CODE = '"+pkValue+ "'"}}).rows;
- // //动态成本
- //签约管理
- var qyglDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT SUBSTRING(DDQY_QDRQ,1,4) nd,sum(DD_FINALPRICE) AS htje,sum(FK_YFJE) AS sfje FROM BFSY_XS_DDQY where SUBSTRING(DDQY_QDRQ,1,4) = '"+pkValue+ "' and DDQY_FKBS_CODE='QDZ' " + whereSql}}).rows;
- //年度考核
- var ndkhDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT sum(NDKH_SALEHT_MB) as NDKH_SALEHT_MB,sum(NDKH_HK_MB) as NDKH_HK_MB,sum(NDKH_MANAGECOST_MB) as NDKH_MANAGECOST_MB,sum(NDKH_CWCOST_MB) as NDKH_CWCOST_MB, sum(NDKH_SALECOST_MB) as NDKH_SALECOST_MB FROM BFSY_JH_NDKH where NDKH_YEAR = '"+pkValue+"'" + whereSql}}).rows;
- //目标成本
- var mbcbDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT sum(MBCB_MBJDZCB) AS mbcbsjz,sum(MBCB_GHYL) AS GHYL FROM BFSY_CB_MBCB where MBCB_ND_CODE = '"+pkValue+"' AND KM_CODE='"+xmcode+"' AND MBCB_CBBB_CODE='TZ' " + whereSql}}).rows;
- //动态成本
- var hymxDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT SUBSTRING(SY_CREATETIME,1,4) nd,sum(HYMX_CONTRACTANDCHANGE) AS htjbg,sum(HYMX_PREPAREDCONTRACT) AS dfshy,sum(HYMX_EXECUTECHANGE) AS ygbg FROM BFSY_CB_HYMX where SUBSTRING(SY_CREATETIME,1,4) = '"+pkValue+ "'" + whereSql}}).rows;
- var dtcbDatas = JE.ajax({url:"/je/report/select",params:{sql:"SELECT SUBSTRING(SY_CREATETIME,1,4) nd,sum(MBCB_MBJDZCB) AS mbjtz,sum(MBCB_JSCE) AS jsce FROM BFSY_CB_MBCB where SUBSTRING(SY_CREATETIME,1,4) = '"+pkValue+ "'" + whereSql+" AND MBCB_CBBB_CODE = 'TZ' "}}).rows;
- var results = new Array();
- Ext.each(qyglDatas,function(qygl){
- Ext.each(ndkhDatas,function(ndkh){
- Ext.each(mbcbDatas,function(mbcb){
- Ext.each(hymxDatas,function(hymx){
- Ext.each(dtcbDatas,function(dtcb){
- var rate1 = 0;//合同金额差额比率
- var rate2 = 0;//回款差额比率
- var rate3 = 0;//目标成本差额比率
- var A = JE.toNum(ndkh.NDKH_SALEHT_MB);//销售合同额目标值--年度考核表
- var B = JE.toNum(ndkh.NDKH_HK_MB);//最终价格总和--签约表
- if(A>0){
- //合同金额差额比率=(销售合同额目标值-最终价格总和)/销售合同额目标值
- rate1 = ((B - A)/A*100).toFixed(2)
- //rate1 = (((JE.toNum(ndkh.NDKH_SALEHT_MB) - JE.toNum(qygl.htje))/JE.toNum(ndkh.NDKH_SALEHT_MB))*100).toFixed(2)
- //rate1 = (((JE.toNum(ndkh.NDKH_SALEHT_MB) - JE.toNum(qygl.htje))/JE.toNum(ndkh.NDKH_SALEHT_MB))*100).toFixed(2)
- }
- var C = JE.toNum(ndkh.NDKH_HK_MB);//回款额目标值--年度考核表
- var D = JE.toNum(qygl.sfje);//已付金额总和--签约表
-
- if(C > 0){
- rate2 = (((D - C)/C)*100).toFixed(2);
- }
- var E = JE.toNum(mbcb.mbcbsjz);//调整后目标成本--目标成本表
- var F = JE.toNum(mbcb.GHYL);//规划余量--目标成本表
- if(E.toFixed(2)>0){
- rate3 = (((F-E).toFixed(2) - (E.toFixed(2)) )/E).toFixed(2);
- }
- var G = JE.toNum(hymx.htjbg);//合同加变更总和--合约明细表
- var H = JE.toNum(hymx.dfshy);//待发生合约总和--合约明细表
- var I = JE.toNum(hymx.ygbg);//执行预估变更总和--合约明细表
- var J = JE.toNum(dtcb.jsce);//结算差额--目标成本表
- var K = JE.toNum(dtcb.mbjtz);//调整后目标成本--目标成本表
- if(K>0){
- var L = ((G+H+I+J-K)/K);
- }else{
- var L = 0;
- }
- debugger;
- results.push({
- "年度": pkValue,
- "合同金额目标值": amountOfConversion(A.toFixed(2)),//销售合同额目标值--年度考核表
- "回款额目标值": amountOfConversion(B.toFixed(2)),//最终价格总和--签约表
- "目标成本目标值": amountOfConversion(E.toFixed(2)),//调整后目标成本--目标成本表
- "管理费用目标值": amountOfConversion(JE.toNum(ndkh.NDKH_MANAGECOST_MB).toFixed(2)),//管理费用目标值--年度考核表
- "财务费用目标值": amountOfConversion(JE.toNum(ndkh.NDKH_CWCOST_MB).toFixed(2)),//财务费用目标值--年度考核表
- "销售费用目标值": amountOfConversion(JE.toNum(ndkh.NDKH_SALECOST_MB).toFixed(2)),//销售费用目标值--年度考核表
- "合同金额实际值": amountOfConversion(B.toFixed(2)),//最终价格总和--签约表
- "回款金额实际值": amountOfConversion(D.toFixed(2)),//已付金额总和--签约表
- "目标成本实际值": amountOfConversion((E-F).toFixed(2)),//调整后目标成本 - 规划余量
- "动态成本实际值": amountOfConversion((G+H+I+J).toFixed(2)),// 合同加变更总和+待发生合约总和+执行预估变更总和+结算差额
- //(合同加变更总和+待发生合约总和+执行预估变更总和+结算差额-调整后目标成本)/调整后目标成本
- "动态成本偏差率": L.toFixed(2),
- "管理费用实际值": "",
- "财务费用实际值": "",
- "销售费用实际值": "",
-
- "合同金额差额": amountOfConversion((A - B).toFixed(2)), //合同金额差额 = 合同金额目标值 - 合同金额实际值
- "回款额差额": amountOfConversion((C - D).toFixed(2)), //回款额差额 = 回款额目标值 - 回款额实际值
- "目标成本差额": amountOfConversion(((E-F).toFixed(2)) - (E.toFixed(2))), //目标成本差额 = 目标成本目标值 - 目标成本实际值
- "合同金额差额比率": rate1+"%",
- "回款差额比率": rate2+"%",
- "目标成本差额比率": rate3+"%",
-
- })
- })
- })
- })
-
- })
- });
- function amountOfConversion(value){
- value = JE.toNum(value);
- var newStr = "";
- var str="";
- var count = 0;
- if(value>=0){
- str=value+"";
- // 当数字是整数
- if (str.indexOf(".") == -1) {
- for (var i = str.length - 1; i >= 0; i--) {
- if (count % 3 == 0 && count != 0) {
- newStr = str.charAt(i) + "," + newStr;
- } else {
- newStr = str.charAt(i) + newStr;
- }
- count++;
- }
- str = newStr + ".00"; //自动补小数点后两位
- return str;
- }
- // 当数字带有小数
- else {
- for (var i = str.indexOf(".") - 1; i >= 0; i--) {
- if (count % 3 == 0 && count != 0) {
- newStr = str.charAt(i) + "," + newStr;
- } else {
- newStr = str.charAt(i) + newStr; //逐个字符相接起来
- }
- count++;
- }
- str = newStr + (str + "00").substr((str + "00").indexOf("."), 3);
- return str;
- }
- }else{
- value = Math.abs(value);
- str=value+"";
- // 当数字是整数
- if (str.indexOf(".") == -1) {
- for (var i = str.length - 1; i >= 0; i--) {
- if (count % 3 == 0 && count != 0) {
- newStr = str.charAt(i) + "," + newStr;
- } else {
- newStr = str.charAt(i) + newStr;
- }
- count++;
- }
- str = newStr + ".00"; //自动补小数点后两位
- return "-"+str;
- }
- // 当数字带有小数
- else {
- for (var i = str.indexOf(".") - 1; i >= 0; i--) {
- if (count % 3 == 0 && count != 0) {
- newStr = str.charAt(i) + "," + newStr;
- } else {
- newStr = str.charAt(i) + newStr; //逐个字符相接起来
- }
- count++;
- }
- str = newStr + (str + "00").substr((str + "00").indexOf("."), 3);
- return "-"+str;
- }
- }
-
- }
- return {fields:["年度","合同金额目标值","回款额目标值","目标成本目标值","管理费用目标值","财务费用目标值","销售费用目标值","合同金额实际值","回款金额实际值","目标成本实际值","动态成本实际值","动态成本偏差率","管理费用实际值","财务费用实际值","销售费用实际值","合同金额差额","回款额差额","目标成本差额","合同金额差额比率","回款差额比率","目标成本差额比率"],data:results};
- }
复制代码
五、总结(可选)
这是报表的大概实现思路,具体的细节可以参考别的帖子。
六、关键字
统计报表,报表,数据源,图标
|
|