睿思BI-数据仪表盘，开源商业智能，数据可视化系统

package com.ruisitech.bi.service.bireport; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.math.BigDecimal; import java.net.URLDecoder; import java.text.ParseException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.PostConstruct; import javax.annotation.PreDestroy; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Service; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.ruisi.ext.engine.ExtConstants; import com.ruisi.ext.engine.init.TemplateManager; import com.ruisi.ext.engine.util.IdCreater; import com.ruisi.ext.engine.view.context.Element; import com.ruisi.ext.engine.view.context.ExtContext; import com.ruisi.ext.engine.view.context.MVContext; import com.ruisi.ext.engine.view.context.MVContextImpl; import com.ruisi.ext.engine.view.context.cross.CrossCols; import com.ruisi.ext.engine.view.context.cross.CrossField; import com.ruisi.ext.engine.view.context.cross.CrossReportContext; import com.ruisi.ext.engine.view.context.cross.CrossReportContextImpl; import com.ruisi.ext.engine.view.context.cross.CrossRows; import com.ruisi.ext.engine.view.context.cross.RowDimContext; import com.ruisi.ext.engine.view.context.cross.RowLinkContext; import com.ruisi.ext.engine.view.context.dc.grid.ComputeMoveAvgContext; import com.ruisi.ext.engine.view.context.dc.grid.GridAccountContext; import com.ruisi.ext.engine.view.context.dc.grid.GridDataCenterContext; import com.ruisi.ext.engine.view.context.dc.grid.GridDataCenterContextImpl; import com.ruisi.ext.engine.view.context.dc.grid.GridFilterContext; import com.ruisi.ext.engine.view.context.dc.grid.GridSetConfContext; import com.ruisi.ext.engine.view.context.dc.grid.GridShiftContext; import com.ruisi.ext.engine.view.context.dc.grid.GridSortContext; import com.ruisi.ext.engine.view.context.form.InputField; import com.ruisi.ispire.dc.grid.GridFilter; import com.ruisi.ispire.dc.grid.GridProcContext; import com.ruisitech.bi.entity.bireport.DimDto; import com.ruisitech.bi.entity.bireport.KpiDto; import com.ruisitech.bi.entity.bireport.ParamDto; import com.ruisitech.bi.entity.bireport.TableQueryDto; import com.ruisitech.bi.util.RSBIUtils; import com.ruisitech.ext.service.DataControlInterface; @Service @Scope("prototype") public class TableService extends BaseCompService { public final static String deftMvId = "mv.tmp.table"; private Map<String, InputField> mvParams = new HashMap<String, InputField>(); //mv的参数 private StringBuffer scripts = new StringBuffer(); //用来构造js 脚本的字符串对象 /*** * 当指标有计算指标时，需要计算上期、同期等值，在显示数据时需要对偏移的数据进行过滤， */ private List<GridFilterContext> filters = new ArrayList<GridFilterContext>(); /** * 数据权限接口 */ @Autowired private DataControlInterface dataControl; @Autowired private ModelCacheService cacheService; public @PostConstruct void init() { } public @PreDestroy void destory() { mvParams.clear(); filters.clear(); } public MVContext json2MV(TableQueryDto table) throws ParseException, IOException{ //创建MV MVContext mv = new MVContextImpl(); mv.setChildren(new ArrayList<Element>()); String formId = ExtConstants.formIdPrefix + IdCreater.create(); mv.setFormId(formId); mv.setMvid(deftMvId); //创建corssReport //添加kpiOther DimDto kpiOther = new DimDto(); kpiOther.setType("kpiOther"); table.getCols().add(kpiOther); CrossReportContext cr = json2Table(table); //移除kpiOther table.getCols().remove(table.getCols().size() - 1); //设置ID String id = ExtConstants.reportIdPrefix + IdCreater.create(); cr.setId(id); cr.setOut("olap"); cr.setShowData(true); //cr.setExportName(title); mv.getChildren().add(cr); cr.setParent(mv); Map<String, CrossReportContext> crs = new HashMap<String, CrossReportContext>(); crs.put(cr.getId(), cr); mv.setCrossReports(crs); //创建datacenter String sql = this.createSql(table, 0); GridDataCenterContext dc = this.createDataCenter(sql, table); cr.setRefDataCetner(dc.getId()); if(mv.getGridDataCenters() == null){ mv.setGridDataCenters(new HashMap<String, GridDataCenterContext>()); } mv.getGridDataCenters().put(dc.getId(), dc); //判断是否需要创建数据源 String dsid = super.createDsource(cacheService.getDsource(table.getDsid()), mv); dc.getConf().setRefDsource(dsid); String scripts = this.scripts.toString(); if(scripts != null && scripts.length() > 0){ mv.setScripts(scripts); } return mv; } public CrossReportContext json2Table(TableQueryDto table) throws ParseException{ CrossReportContext ctx = new CrossReportContextImpl(); CrossCols cols = new CrossCols(); cols.setCols(new ArrayList<CrossField>()); ctx.setCrossCols(cols); CrossRows rows = new CrossRows(); rows.setRows(new ArrayList<CrossField>()); ctx.setCrossRows(rows); ctx.setLabel(table.getCompId()); //给组件设置label boolean uselink = false; Map<String, Object> link = table.getLink(); if(link != null && !link.isEmpty()){ RowLinkContext rlink = new RowLinkContext(); rlink.setParamName((String)link.get("paramName")); String url = (String)link.get("url"); //url 优先于联动组件 if(url != null && url.length() >0){ rlink.setUrl(url); }else{ String target = (String)link.get("target"); String type = (String)link.get("type"); rlink.setTarget(target.split(",")); rlink.setType(type.split(",")); } ctx.getCrossRows().setLink(rlink); uselink = true; } //表格钻取维度 List<RowDimContext> drill = this.getDrillDim(table); if(drill != null && drill.size() > 0){ ctx.setDims(drill); uselink = true; } loopJsonField(table.getCols(), cols.getCols(), table.getKpiJson(), "col", uselink); loopJsonField(table.getRows(), rows.getRows(), table.getKpiJson(), "row", uselink); //如果没有维度，添加none维度 if(cols.getCols().size() == 0){ CrossField cf = new CrossField(); cf.setType("none"); cf.setDesc("合计"); cols.getCols().add(cf); } if(rows.getRows().size() == 0){ CrossField cf = new CrossField(); cf.setType("none"); cf.setDesc("合计"); rows.getRows().add(cf); } return ctx; } /** * 生成表格SQL * @param sqlVO * @param tinfo * @param params * @param release 判断当前是否为发布状态, 0 表示不是发布，1表示发布到多维分析，2表示发布到仪表盘 * @return * @throws ParseException */ public String createSql(TableQueryDto table, int release) throws ParseException{ JSONObject dset = cacheService.getDset(table.getDsetId()); Map<String, String> tableAlias = createTableAlias(dset); //判断是否需要计算上期、同期值 int jstype = table.getKpiComputeType(); StringBuffer sql = new StringBuffer(); sql.append("select "); List<DimDto> dims = table.getDims(); for(int i=0; i<dims.size(); i++){ DimDto dim = dims.get(i); String key = dim.getTableColKey(); String txt = dim.getTableColName(); String tname = dim.getTableName(); if(key != null && txt != null && key.length() >0 && txt.length() >0){ sql.append( tableAlias.get(tname) + "."+key+", " + tableAlias.get(tname) + "." + txt + ","); }else{ if(dim.getCalc() == 0){ sql.append( tableAlias.get(dim.getTname()) + "." + dim.getColname()+" as "+dim.getAlias()+", "); }else{ sql.append( dim.getColname()+" as "+dim.getAlias()+", "); } } } List<KpiDto> kpis = table.getKpiJson();