Pentaho - Demo 源码

时间 2018/3/31 0:27:45 加载中...

最后说一下,最开始的demo


2018-03-30_140455.jpg


我们已经知道如何使用echarts来展示mysql数据源中的数据了

后面就是写各种统计的sql语句,并组装成各种echarts的option了

然后就是对其就行布局就可以了。布局用到了一个前端框架 UIkit


数据源CDA


一个xml中有多个查询


<?xml version="1.0" encoding="UTF-8"?>
<CDADescriptor>
   <DataSources>
      <Connection id="mysql" type="sql.jdbc">
         <Driver>com.mysql.jdbc.Driver</Driver>
         <Pass>123456</Pass>
         <Url>jdbc:mysql://192.168.103.90:3306</Url>
         <User>root</User>
      </Connection>
   </DataSources>
   <DataAccess access="public" connection="mysql" id="query1" type="sql">
      <Name>query1</Name>
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters/>
      <Query><![CDATA[
      SELECT 单位名称,count(0) as num FROM pentaho.salesdata group by 单位名称 ORDER BY count(0) desc LIMIT 10;
      ]]></Query>
   </DataAccess>
   <DataAccess access="public" connection="mysql" id="query2" type="sql">
      <Name>query2</Name>
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters/>
      <Query><![CDATA[
   select c.`销售`,c.`销售额` from (SELECT m.`销售` as 销售,SUM(m.`售价`) as 销售额 FROM pentaho.salesdata m  GROUP BY m.`销售`  ORDER BY SUM(m.`售价`)  desc  LIMIT 0,10 ) as c ORDER BY c.`销售额`;
      ]]></Query>
   </DataAccess>
   <DataAccess access="public" connection="mysql" id="query3" type="sql">
      <Name>query2</Name>
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters/>
      <Query><![CDATA[
   SELECT m.`产品类别名称`,SUM(m.`数量`) as 销售总量 FROM pentaho.salesdata m GROUP BY m.`产品类别名称`  ORDER BY SUM(m.`数量`) desc  LIMIT 10;
      ]]></Query>
   </DataAccess>
   <DataAccess access="public" connection="mysql" id="query4" type="sql">
      <Name>query2</Name>
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters/>
      <Query><![CDATA[
   SELECT m.`订单日期`,m.`订单数` as 订单数 FROM pentaho.t_daycount m GROUP BY m.`订单日期` ;
      ]]></Query>
   </DataAccess>
    <DataAccess access="public" connection="mysql" id="query5" type="sql">
      <Name>query2</Name>
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters/>
      <Query><![CDATA[
   select t.`产品类别名称`,SUM(t.`总价`) as 总销售额 from t_price t GROUP BY t.`产品类别名称` ORDER BY  SUM(t.`总价`) desc LIMIT 0,5
      ]]></Query>
   </DataAccess>
   <DataAccess access="public" connection="mysql" id="query6" type="sql">
      <Name>query6</Name>
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters/>
      <Query><![CDATA[
   SELECT t.`订单日期`,t.type,t.`订单量` FROM pentaho.t_addorcontinue t;
      ]]></Query>
   </DataAccess>
</CDADescriptor>


前端结构


front.jpg


mycss:是自定义样式(内置类型)

echarts:引入的echarts文件

china:地图文件

myjs:初始化图表的文件

containerabc:写html的对象


mycss 内容:


.container{
    width:100%;
    background-color:#030A19;
} 

.uk-panel-box
{
    background:#151726 !important;
}


containerabc 的HTML属性:


<script src="https://cdn.bootcss.com/uikit/2.27.5/js/uikit.min.js"></script>
<link href="https://cdn.bootcss.com/uikit/2.27.5/css/uikit.min.css" rel="stylesheet">

<div class='uk-grid uk-grid-small'>
	<div class='uk-width-2-10'>
        <div class='uk-grid'>
            <div class='uk-width-1-1'>
                <div class='uk-panel uk-panel-box uk-panel-primary'>
                    <div id='container' style='width:100%;height:400px;'>加载中... </div> 
                </div>
            </div>                
        </div>
        <div class='uk-grid'>
            <div class='uk-width-1-1'>
                <div class='uk-panel uk-panel-box uk-panel-primary' >
                    <div id='container2' style='width:100%;height:400px;'>加载中... </div>
                </div>
            </div>                
        </div>
	</div>
	<div class='uk-width-6-10'>
        <div class='uk-grid'>
            <div class='uk-width-1-1'>
                <div class='uk-panel uk-panel-box uk-panel-primary'>
                    <div id="containermap" style="width:100%;height:600px;">加载中...</div>                        
                </div>
            </div>
            <div class='uk-width-1-1'>
                <div class='uk-panel uk-panel-box uk-panel-primary'>            
                     <div id="container5" style="width:100%;height:236px;">加载中...</div>
                </div>
            </div>
        </div>
        <div class='uk-grid'>
            
        </div>
	</div>
    <div class='uk-width-2-10'>
        <div class='uk-grid'>
            <div class='uk-width-1-1'>
                <div class='uk-panel uk-panel-box uk-panel-primary'>
                    <div id='container3' style='width:100%;height:400px;'>加载中... </div> 
                </div>
            </div>                
        </div>
        <div class='uk-grid'>
            <div class='uk-width-1-1'>
                <div class='uk-panel uk-panel-box uk-panel-primary' >
                    <div id='container4' style='width:100%;height:400px;'>加载中... </div>
                </div>
            </div>                
        </div>
	</div>
</div>


myjs文件:


function getCDAData(url){
    var result;
    $.ajax({
        type:'get',
        url:url,
        async:false,
        success:function(data){
            result = data;
        }
    });
    return result;
}

function chartSet(ele,option){  
    var dom = document.getElementById(ele);
    var myChart = echarts.init(dom);
    var app = {};    
    if (option && typeof option === "object") {
        myChart.setOption(option, true);
    }
}

function getLineOption(){
    return {
        title: {
                left: 'center',
                text: '',
                textStyle:{
                    color:'#fff'
                }
            },
        tooltip: {
            trigger: 'axis'
        },
        xAxis: {
            type: 'category',
            data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'],
            splitLine : {
                show:false
            },
             axisLabel:{
                rotate:45,
                color:'#fff',
                fontSize :10
            }
        },
        yAxis: {
            type: 'value',
            data:[],
            splitLine : {
                show:false
            },
             axisLabel:{
                rotate:45,
                color:'#fff',
                fontSize :10
            }
        },
        series: [{
            data: [820, 932, 901, 934, 1290, 1330, 1320],
            type: 'line',
             smooth: false,
        }]
    };
}
function getPieOption()
{
    return{
             title: {
                left: 'center',
                text: '',
                textStyle:{
                    color:'#fff'
                }
            },
            tooltip: {
                trigger: 'item',
                formatter: "{a} <br/>{b}: {c} ({d}%)"
            },
            legend: {
                //orient: 'vertical',
                x : 'center',
                y : 'bottom',
                padding  :[15,0,0,0],
                data:['直接访问','邮件营销','联盟广告','视频广告','搜索引擎'],
                textStyle :{
                    color:'#fff'
                }
            },
            series: [
                    {
                        center: ['50%', '40%'],
                        name:'访问来源',
                        type:'pie',
                        radius: ['50%', '70%'],
                        avoidLabelOverlap: false,
                        label: {
                            normal: {
                                show: false,
                                position: 'center'
                            },
                            emphasis: {
                                show: true,
                                textStyle: {
                                    fontSize: '30',
                                    fontWeight: 'bold'
                                }
                            }
                        },
                        labelLine: {
                            normal: {
                                show: false
                            }
                        },
                        data:[
                            {value:335, name:'直接访问'},
                            {value:310, name:'邮件营销'},
                            {value:234, name:'联盟广告'},
                            {value:135, name:'视频广告'},
                            {value:1548, name:'搜索引擎'}
                        ]
                    }
                ]
            }
}
function getBarOption(){
    return {
            title: {
            left: 'center',
            text: '',
             textStyle:{
                    color:'#fff'
                }
        },
        tooltip: {
            trigger: 'axis',
               
        },
        xAxis: {
            type: 'category',
            data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'],
            axisLabel:{
                rotate:45,
                color:'#fff',
                fontSize :8
            },
            splitLine : {
                show:false
            }
        },
        yAxis: {
            type: 'value',
             axisLabel:{
                rotate:45,
                color:'#fff',
                fontSize :8
            },
            splitLine : {
                show:false
            }

        },
        series: [{
            center: ['55%', '40%'],
            data: [120, 200, 150, 80, 70, 110, 130],
            type: 'bar',
            show:true,
            label:{
                rotate:90
            }
        }]
    };

}


function initChart1(){    
    
    var result = getCDAData('/pentaho/plugin/cda/api/doQuery?path=/public/demo/pentahodemo.cda&dataAccessId=query1');    
    
    //var option = getLineOption();
    var option = getBarOption();
    
    var xdata = [];
    var ydata = [];
    for(var i=0; i<result.resultset.length; i++){
        xdata.push(result.resultset[i][0]);
        ydata.push(result.resultset[i][1]);
    }
     option.title.text='订单量  top 10'
    option.xAxis.data = xdata;
    option.series[0].data = ydata;
    
    chartSet('container',option);
}
function initChart2(){    
    
    var result = getCDAData('/pentaho/plugin/cda/api/doQuery?path=/public/demo/pentahodemo.cda&dataAccessId=query2');    
    var option = getBarOption();
    
    var xdata = [];
    var ydata = [];
    for(var i=0; i<result.resultset.length; i++){
        xdata.push(result.resultset[i][0]);
        ydata.push(result.resultset[i][1]);
    }
    option.title.text='销售额 top 10'
    option.xAxis.type = 'value';
    option.xAxis.data = [];
    option.yAxis.type = 'category';
    option.yAxis.data = xdata;
    option.series[0].data = ydata;
    option.series[0].center=['60%', '40%'];
    option.series[0].itemStyle ={ color:'#4a86e8' }
    chartSet('container2',option);
}
function initChart3(){    
    
    var result = getCDAData('/pentaho/plugin/cda/api/doQuery?path=/public/demo/pentahodemo.cda&dataAccessId=query3');    
    var option = getPieOption();
    
    var sdata = [];
    var ldata = [];
    for(var i=0; i<result.resultset.length; i++){
        sdata.push(
            {
                'name':result.resultset[i][0],
                'value':result.resultset[i][1]
            });
        ldata.push(result.resultset[i][0]);
    }
    option.title.text='畅销产品类别  top 10'
    option.legend.data = ldata;
    option.series[0].name='销售量  '
    option.series[0].data = sdata;
    
    chartSet('container3',option);
}
function initChart4(){    
    
    var result = getCDAData('/pentaho/plugin/cda/api/doQuery?path=/public/demo/pentahodemo.cda&dataAccessId=query4');    
    
    var option = getLineOption();
    var xdata = [];
    var ydata = [];
    for(var i=0; i<result.resultset.length; i++){
        xdata.push(result.resultset[i][0]);
        ydata.push(result.resultset[i][1]);
    }
    option.title.text='日销量趋势分析 '
    option.xAxis.data = xdata;
    option.series[0].data = ydata;
    option.series[0].smooth = true;
    option.series[0].center= ['55%', '40%'];
    option.series[0].markPoint= {
        data: [
            {type: 'max', name: '最大值'},
            {type: 'min', name: '最小值'}
        ]
            };
     option.series[0].markLine= {
        data: [
            {type: 'average', name: '平均值'}
        ]
    }
    chartSet('container4',option);
}
function initChart5(){    
    
    var result = getCDAData('/pentaho/plugin/cda/api/doQuery?path=/public/demo/pentahodemo.cda&dataAccessId=query6');    
    
    option = {
    title: {
        text: '销量堆叠区域图',
         textStyle:{
                    color:'#fff'
                }
    },
    tooltip : {
        trigger: 'axis',
        axisPointer: {
            type: 'cross',
            label: {
                backgroundColor: '#6a7985'
            }
        }
    },
    legend: {
        data:['新增','续订'],
        textStyle:{
            color:'#fff'
        } 
    },
  
    grid: {
        left: '3%',
        right: '4%',
        bottom: '3%',
        containLabel: true
    },
    xAxis : [
        {
            type : 'category',
            boundaryGap : false,
            data : [],
            splitLine : {
                show:false
            },
             axisLabel:{
                color:'#fff',
            }
        }
    ],
    yAxis : [
        {
            type : 'value',
            splitLine : {
                show:false
            },
            axisLabel:{
                color:'#fff',
            }
        }
    ],
    series : [
        {
            name:'新增',
            type:'line',
            stack: '总量',
            areaStyle: {normal: {}},
            data:[120, 132, 101, 134, 90, 230, 210]
        },
        {
            name:'续订',
            type:'line',
            stack: '总量',
            areaStyle: {normal: {}},
            data:[220, 182, 191, 234, 290, 330, 310]
        }
    ]
};

    var xdata = [];
    var ydata = [];
     var xdata1 = [];
    var ydata1 = [];
    for(var i=0; i<result.resultset.length; i++){
       if(result.resultset[i][1]=='新增')
       {
            xdata.push(result.resultset[i][0]);
        ydata.push(result.resultset[i][2]);
       }else{
        xdata1.push(result.resultset[i][0]);
        ydata1.push(result.resultset[i][2]);
       }
       
       
    }
    debugger
    option.xAxis[0].data = xdata;
    option.series[0].data = ydata;
    option.series[1].data= ydata1;
    chartSet('container5',option);
}

var convertData = function (data) {
    var res = [];
    for (var i = 0; i < data.length; i++) {
        var geoCoord = geoCoordMap[data[i].name];
        if (geoCoord) {
            res.push({
                name: data[i].name,
                value: geoCoord.concat(data[i].value)
            });
        }
    }
    return res;
};



function getMapOption(){
   return {
    backgroundColor: '#030A19',
    title: {
        text: '销售量分布',
        subtext: '',
        sublink: '',
        left: 'left',
        textStyle: {
            color: '#fff'
        }
    },
    tooltip : {
        trigger: 'item'
    },
    legend: {
        orient: 'vertical',
        y: 'bottom',
        x:'right',
        data:['销量'],
        textStyle: {
            color: '#fff'
        }
    },
    geo: {
        zoom:1.2,
        map: 'china',
        label: {
            emphasis: {
                show: false
            }
        },
        roam: true,
        itemStyle: {
            normal: {
                areaColor: '#151726',
                borderColor: '#111'
            },
            emphasis: {
                areaColor: '#2a333d'
            }
        }
    },
    series : [
        {
            name: '销量',
            type: 'scatter',
            coordinateSystem: 'geo',
            data: convertData(data),
            symbolSize: function (val) {
                return val[2] / 80;
            },
            label: {
                normal: {
                    formatter: '{b}',
                    position: 'right',
                    show: false
                },
                emphasis: {
                    show: true
                }
            },
            itemStyle: {
                normal: {
                    color: '#ddb926'
                }
            }
        },
        {
            name: 'Top 5',
            type: 'effectScatter',
            coordinateSystem: 'geo',
            data: convertData(data.sort(function (a, b) {
                return b.value - a.value;
            }).slice(0, 6)),
            symbolSize: function (val) {
                return val[2] / 80;
            },
            showEffectOn: 'render',
            rippleEffect: {
                brushType: 'stroke'
            },
            hoverAnimation: true,
            label: {
                normal: {
                    formatter: '{b}',
                    position: 'right',
                    show: true
                }
            },
            itemStyle: {
                normal: {
                    color: '#f4e925',
                    shadowBlur: 10,
                    shadowColor: '#333'
                }
            },
            zlevel: 1
        }
    ]
};
}

var data = [];
var geoCoordMap = {};

function initChart21(){    
    
    var result = getCDAData('/pentaho/plugin/cda/api/doQuery?path=/public/demo/mapsource.cda&dataAccessId=query11');    
         
    
    for(var i=0; i<result.resultset.length; i++){
                
        geoCoordMap[result.resultset[i][0]]=[result.resultset[i][2],result.resultset[i][3]];//只有这种方法可以变量传入 
        data.push({name:result.resultset[i][0],value:result.resultset[i][1] });        
    }
    
    //var option = getLineOption();
    var option = getMapOption();
        
    chartSet('containermap',option);
}




function init(){    
    initChart1();
    initChart2();
    initChart3();
    initChart4();
    
    initChart21();
    initChart5();
}


//window.onload = init();
$(function(){
    init();
});


扫码分享
版权说明
作者:SQBER
文章来源:http://blog.sqber.com/articles/bi-pentaho-demo-source.html
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。