smartBI宏定义01

function main(chartView) {
var chart = chartView.getChartObject();
var option = chart.getOptions();
var data = [];
var series = option.series;

for (var i in series) {
var seriesItem = series[i];
data = seriesItem.data;
delete seriesItem.data; //初始option中去掉data数据，拼在独立的options中。
}

var val = getSqlResult();

var data1 = [];
//data1 = val;

var test = 22;

var daDiShiName = val[0][1];
var didiChuXingName = val[12][1];
var gongJiaoCheName = val[24][1];
var ziXingCheName = val[36][1];

var daDiShiData = [];
var didiChuXingData = [];
var gongJiaoCheData = [];
var ziXingCheData = [];

for (var i = 0; i < val.length; i++) {
if (series.name == val[i][1].value) {

}
if(i <= 11) {
daDiShiData.push(val[i][0].value);
}
if(i >= 12 && i <= 23) {
didiChuXingData.push(val[i][0].value);
}
if(i >= 24 && i <= 35) {
gongJiaoCheData.push(val[i][0].value);
}
if(i >= 36) {
ziXingCheData.push(val[i][0].value);
}

data1.push(val[i][0].value);
test = val[i][0].value;
}

series[0].data = ziXingCheData;
series[1].data = gongJiaoCheData;
series[2].data = daDiShiData;
series[3].data = didiChuXingData;

//设置标题
/*
option.title = {
text: test + '',
textAlign: 'center',
left: '63%',
top: '55%',
textStyle: {
fontSize: 60
}
};
*/

//series.data = data1;
option.series = series;

chart.setOptions(option);
}

function getSqlResult() {

var sql = " select * FROM ( select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 1 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 01 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 2 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 02 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 3 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 03 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 4 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 04 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 5 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 05 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 6 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 06 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 7 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 07 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 8 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 08 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 9 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 09 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 10 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 10 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 11 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 11 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t union all select * FROM ( select count(t2.cxgj) as 出行次数, t2.cxgj as 出行工具, t2.mm as 月份 FROM ( select cxgj from SP_RKCXSJ group by cxgj ) t1 inner join ( select cxgj, 12 as mm from SP_RKCXSJ where to_char(cxrq,'mm') = 12 )t2 on T2.CXGJ = T1.cxgj group by t2.cxgj, t2.mm order by count(t2.cxgj) desc ) t ) t12 order by 出行工具,月份 ";

var util = jsloader.resolve("freequery.common.util");
var ret = util.remoteInvoke("DataSourceService", "getDataByQuerySql", ["DS.sparrow", sql, 0, 10000]);
if (ret.succeeded && ret.result) {
var data = ret.result.data; //结果集数据，返回一个二维数组
return data;
} else {

var data2 = [];
data2.push(11);
data2.push(110);
data2.push(71);
data2.push(51);
data2.push(11);
data2.push(31);
data2.push(11);

return ret.result.data;
}

 

};