Saiku根据入参日期查询出对应的数据
之前好像有写过一篇博客关于saiku date range的，现在进一步更新啦！！！
这里的日期筛选会更完善一些，需要提供两个参数 开始日期与结束日期（startdate and enddate）
参数的日期格式为： yyyy-MM-dd (2019-03-26)
主要是更改了saiku原代码中的两个js文件： js/saiku/models/Query.js 与 js/saiku/views/SelectionsModal.js
>> 如果是改saiku源码请修改改对应 saiku-ui项目下的 对应目录文件。
>> 如果是编译好的saiku请修改\saiku-server\tomcat\webapps\ROOT 下对应目录的js文件。
1.首先我们数据库中需要一个只有年月日的日期字段，我这里用的是 statisdate，数据类型为 date (mysql数据库)
2.statisdate对应saiku的xml配置为：
<Dimension name="statisdate" foreignKey="ID" >
<Hierarchy hasAll="true" primaryKey="ID" allMemberName="statisdate">
<Level name="statisdate" column="statisdate" type='Date' uniqueMembers="false" />
</Hierarchy>
</Dimension>
3. 将statisdate放入saiku的过滤栏位，到时候过滤也会从Filter中取此字段
4.修改Query.js文件中的查询方法
思路：查询方法被调用时，使用 Saiku.URLParams.paramsURI() 方法获取入参信息，判断Url中是否存在参数名为 startdate , enddate 并且参数值都不为null的参数信息
>> 存在：拿到过滤对应信息，判断过滤栏位中有没有statisdate字段，如果有的话就开始我们的参数过滤之旅啦，如果没有的话就还是会正常执行查询
>> 不存在： 正常查询出数据信息
以下代码在run方法的最后，这是判断是否使用date range的主要代码：
/**根据Fliter中的statisdate字段过滤！*/
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies; //取出FILTER信息中的所有维度信息 dimension,用一个数组接收
var statisdateFlag = "no";
for(var i=;i<dimensionArr.length;i++){
//判断维度信息中是否有statisdate这个时间维度（这是固定的）
if(dimensionArr[i]!=null && ((dimensionArr[i].dimension == "statisdate")||(dimensionArr[i].levels.statisdate != null)) ){
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
//判断参数是否为空
if(paramsURI.startdate != null && paramsURI.startdate != undefined && paramsURI.startdate != ""&&
paramsURI.enddate != null && paramsURI.enddate != undefined && paramsURI.enddate != ""){
statisdateFlag = "yes";
this.startdateThis=null;
this.enddateThis=null;
var key="statisdateSpec";
(new SelectionsModal({
key: key,
exModel: exModel,
result: this.result,
workspace: this.workspace
})); }
}
} //根据入参是否有statisdate字段来判断是否执行以下查询
if(statisdateFlag == 'no'){
this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}
5.嵌入 jsp页面设计如下：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Saiku嵌入 (時間篩選)</title>
<script type="text/javascript" src="${pageContext.request.contextPath}/resource/bootstrap/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/resource/bootstrap/bootstrap-datepicker/css/bootstrap-datepicker3.min.css"/>
<script src="${pageContext.request.contextPath}/resource/bootstrap/bootstrap-datepicker/js/bootstrap-datepicker.min.js"></script>
<link rel="stylesheet" href="${pageContext.request.contextPath}/resource/bootstrap/bootstrap.min.css" />
<link rel="stylesheet" href="${pageContext.request.contextPath}/resource/css/font-awesome.min.css" />
<script> $(function() {// 初始化内容
$("#startdate").datepicker({
orientation: "left",
autoclose: true, })
$("#enddate").datepicker({
orientation: "left",
autoclose: true, });
//初始化日期賦值
$("#startdate").val(getFirstDayOfWeek());
$("#enddate").val(new Date());
var userid = '${sessionScope.LoginUserInfo.userid}';//获取当前登录的用户信息 userid
userid = userid.toUpperCase();
document.getElementById("timeLimitFrame").src="http://10.22.33.44:8080/?username="+userid+"&password=11111&plugin=false&mode=view#query/open//KPI/時間篩選.saiku";
}); /*獲取本週的第一天*/
function getFirstDayOfWeek () {
var date = new Date();
var weekday = date.getDay()||7; //获取星期几,getDay()返回值是 0（周日） 到 6（周六） 之间的一个整数。0||7为7，即weekday的值为1-7
date.setDate(date.getDate()-weekday);//往前算（weekday）天，年份、月份会自动变化
return date;
} /*根據用戶輸入的日期篩選數據*/
function getDataByInputDate(){
var startdate = document.getElementById("startdate").value;
var enddate = document.getElementById("enddate").value;
if(startdate != "" && startdate != null && startdate != undefined ){
if(enddate != "" && enddate != null && enddate != undefined ){
var userid = '${sessionScope.LoginUserInfo.userid}';//获取当前登录的用户信息 userid
userid = userid.toUpperCase();
document.getElementById("timeLimitFrame").src="http://10.22.33.44:8080/?username="+userid+"&password=11111&startdate="+startdate+"&enddate="+enddate+"&plugin=false&mode=view#query/open//KPI/時間篩選.saiku"; }else{
alert("請輸入結束日期");
}
}else{
alert("請輸入開始日期");
}
}
</script>
</head>
<body> <div class="col-md-3">
<label for="startdate" class="col-md-1 control-label" style="width: 103px;line-height: 30px;">開始日期：</label><!-- 開始日期 -->
<input class="form-control input-sm" id="startdate" type="text" name="startdate" data-date-format="yyyy-mm-dd" style="width: 185px;"/>
</div> <div class="col-md-3">
<label class="col-md-1 control-label" style="width: 103px;line-height: 30px;">結束日期：</label><!--結束日期 -->
<input class="form-control input-sm" id="enddate" type="text" name="enddate" data-date-format="yyyy-mm-dd" style="width: 185px;"/>
</div> <button onclick="getDataByInputDate()" style="line-height: 23px;border-radius:3px;">確認</button>
<h3>時間篩選示例</h3>
<iframe id="timeLimitFrame" src="" width="100%" height="792" frameborder="0" > </iframe> </body> </html>
6.发送请求Url格式如下：
http://10.22.33.44:8080/?username=test&password=11111&startdate=2019-03-06&enddate=2019-03-18&plugin=false&mode=view#query/open//KPI/時間篩選.saiku
整体实现思路：
1.先查询出所有的statisdate数据。（参考SelectionMondal.js文件中的get_members 方法，发送请求给后台，查询出所有statisdate数据）
2.使用入参对数据进行过滤主要思路是先将所有日期查询出来并将结果放入数组中，然后将用户输入的入参与日期数组匹配，看看日期数组中是否包含当前日期。
>> 如果不包含开始日期，则将开始日期往后挪一天：比如 startdate=2019-03-01 ,日期不存在 则startdate=2019-03-02 ,这样一直循环判断，直到被包含或者是开始日期>结束日期了，就结束方法!
>> 如果不包含结束日期，则将结束日期往前挪一天：比如 enddate=2019-03-20,日期不存在 则ennddate=2019-03-19,这样一直循环判断，直到被包含或者是结束日期<开始日期了，就结束方法！
这样就能避免saiku抛出日期不存在的异常信息了 ！！！
实现结果：
日期范围内有数据就会查询出所有符合条件的数据
日期范围内没有数据则会抛出找不到对应的日期的异常！
>>>>>>这个异常可以避免，通过更改saiku-server\tomcat\webapps\saiku\WEB-INF\classes\mondrian.properties的这两个参数实现：
打开注释，将参数值改为true,即可！
mondrian.rolap.ignoreInvalidMembers=true
mondrian.rolap.ignoreInvalidMembersDuringQuery=true
主要改动：
修改了Query.js的initialize方法： 主要是新增了判断此方法是不是从SelectionModal.js文件中的 get_members 方法传过来的！
/*初始化方法*/
initialize: function(args, options) {
if(args != null && args != undefined && args != "" && args.flag == "resultForstatisdate"){ this.get_all_statisdate(args);
}else{
// Save cube
_.extend(this, options); // Bind `this`
_.bindAll(this, "run"); // Generate a unique query id
this.uuid = 'xxxxxxxx-xxxx-xxxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
function (c) {
var r = Math.random() * 16 | 0,
v = c == 'x' ? r : (r & 0x3 | 0x8);
return v.toString(16);
}).toUpperCase(); this.model = _.extend({ name: this.uuid }, SaikuOlapQueryTemplate);
if (args.cube) {
this.model.cube = args.cube;
}
this.helper = new SaikuOlapQueryHelper(this); // Initialize properties, action handler, and result handler
this.action = new QueryAction({}, { query: this });
this.result = new Result({ limit: Settings.RESULT_LIMIT }, { query: this });
this.scenario = new QueryScenario({}, { query: this }); // A flag to tell who changed selection members
this.updatedSelectionFromModal = false;
}
},
修改了Query.js的run方法：主要增加了判断参数中是否有startdate,enddate，来决定是否使用自定义的date range! 日期范围查询
/*执行查询的方法*/
run: function(force, mdx) {
this.syncSelectionsModalAndUpdateParameters(); var self = this;
// Check for automatic execution
Saiku.ui.unblock();
if (typeof this.model.properties != "undefined" && this.model.properties['saiku.olap.query.automatic_execution'] === false &&
(force === false || force === undefined || force === null)) {
return;
}
this.workspace.unblock(); $(this.workspace.el).find(".workspace_results_info").empty();
this.workspace.trigger('query:run');
this.result.result = null;
var validated = false;
var errorMessage = '<span class="i18n">Query Validation failed!</span>'; var exModel = this.helper.model();
for(var k in this.attributes) {
var att = this.attributes[k];
if(k.substring(0,5)==="PARAM"){
var p = k.substring(5, k.length);
exModel.parameters[p] = att;
} }
if (exModel.queryType == "OLAP") {
if (exModel.type == "QUERYMODEL") {
var columnsOk = Object.keys(exModel.queryModel.axes.COLUMNS.hierarchies).length > 0;
var rowsOk = Object.keys(exModel.queryModel.axes.ROWS.hierarchies).length > 0;
var detailsOk = exModel.queryModel.details.axis == 'COLUMNS' && exModel.queryModel.details.measures.length > 0;
if (!rowsOk || !columnsOk || !detailsOk) {
errorMessage = "";
}
if (!columnsOk && !detailsOk) {
errorMessage += '<span class="i18n">You need to include at least one measure or a level on columns for a valid query.</span>';
}
if(!rowsOk) {
errorMessage += '<span class="i18n">You need to include at least one level on rows for a valid query.</span>'; }
if ( (columnsOk || detailsOk) && rowsOk) {
validated = true;
} } else if (exModel.type == "MDX") {
validated = (exModel.mdx && exModel.mdx.length > 0);
if (!validated) {
errorMessage = '<span class="i18n">You need to enter some MDX statement to execute.</span>';
}
}
}
if (!validated) {
this.workspace.table.clearOut();
$(this.workspace.processing).html(errorMessage).show();
this.workspace.adjust();
Saiku.i18n.translate();
return;
} // Run it
this.workspace.table.clearOut();
$(this.workspace.processing).html('<span class="processing_image"> </span> <span class="i18n">Running query...</span> [ <a class="cancel i18n" href="#cancel">Cancel</a> ]').show();
this.workspace.adjust();
this.workspace.trigger('query:fetch');
Saiku.i18n.translate();
var message = '<span class="processing_image"> </span> <span class="i18n">Running query...</span> [ <a class="cancel i18n" href="#cancel">Cancel</a> ]';
this.workspace.block(message);
/*
TODO: i wonder if we should clean up the model (name and captions etc.)
delete this.model.queryModel.axes['FILTER'].name;
*/ /**根据ROWS中的statisdate字段过滤！*/
//根據用戶輸入的開始日期與結束日期查詢範圍數據
/*
var dimensionArr = exModel.queryModel.axes.ROWS.hierarchies; //取出行信息中的所有维度信息 dimension,用一个数组接收
var statisdateFlag = "no";
for(var i=0;i<dimensionArr.length;i++){
//判断维度信息中是否有statisdate这个时间维度（这是固定的）
if(dimensionArr[i]!=null && ((dimensionArr[i].dimension == "statisdate")||(dimensionArr[i].levels.statisdate != null)) ){
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
//判断参数是否为空
if(paramsURI.startdate != null && paramsURI.startdate != undefined && paramsURI.startdate != ""&&
paramsURI.enddate != null && paramsURI.enddate != undefined && paramsURI.enddate != ""){
statisdateFlag = "yes";
this.startdateThis=null;
this.enddateThis=null;
var key="statisdateSpec";
(new SelectionsModal({
key: key,
exModel: exModel,
result: this.result,
workspace: this.workspace
})); }
}
}
*/ /**根据Fliter中的statisdate字段过滤！*/
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies; //取出FILTER信息中的所有维度信息 dimension,用一个数组接收
var statisdateFlag = "no";
for(var i=0;i<dimensionArr.length;i++){
//判断维度信息中是否有statisdate这个时间维度（这是固定的）
if(dimensionArr[i]!=null && ((dimensionArr[i].dimension == "statisdate")||(dimensionArr[i].levels.statisdate != null)) ){
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
//判断参数是否为空
if(paramsURI.startdate != null && paramsURI.startdate != undefined && paramsURI.startdate != ""&&
paramsURI.enddate != null && paramsURI.enddate != undefined && paramsURI.enddate != ""){
statisdateFlag = "yes";
this.startdateThis=null;
this.enddateThis=null;
var key="statisdateSpec";
(new SelectionsModal({
key: key,
exModel: exModel,
result: this.result,
workspace: this.workspace
})); }
}
} //根据入参是否有statisdate字段来判断是否执行以下查询
if(statisdateFlag == 'no'){
this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}
},
在Query.js文件中新增了 get_all_statisdate 方法： 用于得到所有的statisdate数据，以及修改saiku参数，使用日期过滤去查询数据！
//得到返回的所有statisdate參數信息
get_all_statisdate: function(args){
var response = args.response;
this.result = args.result;
//var exModel = args.exModel;
var statisdateArr= new Array();
for(var i=0;i<response.length;i++){
statisdateArr[i]=response[i].name;
}
//this.helper = new SaikuOlapQueryHelper(this);
var exModel = args.exModel;
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
var startdate=null;
var enddate=null;
if(this.startdateThis != null){
startdate=this.dateToStr(this.startdateThis); //获取修正后的开始日期
}else{
startdate=paramsURI.startdate;//开始日期（参数中的）
} if(this.enddateThis != null){
enddate=this.dateToStr(this.enddateThis); //获取修正后的结束日期
}else{
enddate=paramsURI.enddate;//结束日期（参数中的）
} //startdate = this.dateToStr(startdate); // 日期格式化
//enddate = this.dateToStr(enddate); // 日期格式化
if(statisdateArr.indexOf(startdate) > -1){// 这里是判断开始日期是否属于statisdateArr if(statisdateArr.indexOf(enddate) > -1){// 这里是判断结束日期是否属于statisdateArr //var dimensionArr = exModel.queryModel.axes.ROWS.hierarchies; //根据ROWS
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies; //根据Filter
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式
}
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}else{//结束日期不存在时：如果结束日期大于开始日期，将结束日期往前减一天 var tmpStartdate = this.strToDate(startdate);//StringToDate方法不存在
var tmpEnddate = this.strToDate(enddate); if(tmpEnddate>=tmpStartdate){
tmpEnddate = tmpEnddate.valueOf();
tmpEnddate = tmpEnddate - 1*24*60*60*1000;
tmpEnddate = new Date(tmpEnddate); //这里改变paramURL中的结束日期参数值，然后回调当前函数 get_all_statisdate
this.enddateThis=tmpEnddate;
this.get_all_statisdate(args); }else{ //否则的话直接执行查询，直接无数据返回 var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
enddate = this.dateToStr(tmpStartdate)
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+enddate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式 }
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} }); }
}
}else{ //开始日期不存在时: 如果开始日期小于结束日期，将开始日期往后加一天 var tmpStartdate = this.strToDate(startdate);
var tmpEnddate = this.strToDate(enddate);
if(tmpStartdate<=tmpEnddate){
tmpStartdate = tmpStartdate.valueOf();
tmpStartdate = tmpStartdate + 1*24*60*60*1000;
tmpStartdate = new Date(tmpStartdate); //这里改变paramURL中的结束日期参数值，然后回调当前函数get_all_statisdate
this.startdateThis=tmpStartdate;
this.get_all_statisdate(args);
}else{ //否则的话直接执行查询，直接无数据返回 var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
startdate = this.dateToStr(tmpEnddate)
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+startdate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式
}
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} }); }
}
},
在Query.js文件中增加了strToDate方法： 将string类型的数据转换为date类型 （用于做日期的比较与加减）
/*將string類型的數據轉換為date類型*/
strToDate: function(strDate){
strDate = strDate.toLocaleString();
strDate = strDate.substring(-1,10);
var convert = Date.parse(strDate);
var tmpdate = new Date(convert);
return tmpdate;
},
在Query.js文件中增加了dateToStr方法： 将date类型的数据转换为string类型 （用于匹配当前日期与日期数组中的数据）
/*将Date类型转换为String类型 格式为：2019-03-03*/
dateToStr: function(dateStr){
var tmpYear= dateStr.getFullYear();
var tmpMonth = dateStr.getMonth()+1;
var tmpDay = dateStr.getDate();
if(tmpMonth < 10){ tmpMonth = "0"+tmpMonth;}
if(tmpDay < 10){ tmpDay = "0"+tmpDay;}
var tmpdate = tmpYear+"-"+tmpMonth+"-"+tmpDay;
return tmpdate;
},
修改了SelectionModal.js文件中的initialize方法：判断调用SelectionModal.js的源方法是不是和日期范围筛选有关，如果是的话就直接调用get_members方法，返回statisdate所有数据
initialize: function(args) {
if(args != null && args != undefined && args != "" && args.key == "statisdateSpec"){
this.query = args.workspace.query;
this.get_members(args);
}else{
// Initialize properties
var self = this;
_.extend(this, args);
this.options.title = "<span class='i18n'>Selections for</span> " + this.name;
this.message = "Fetching members...";
this.query = args.workspace.query;
this.selected_members = [];
this.available_members = [];
this.topLevel;
_.bindAll(this, "fetch_members", "populate", "finished", "get_members", "use_result_action", "per_metrics_totals_action");
// Determine axis
this.axis = "undefined";
if (args.axis) {
this.axis = args.axis;
if (args.axis == "FILTER") {
this.use_result_option = false;
}
} else {
if (args.target.parents('.fields_list_body').hasClass('rows')) {
this.axis = "ROWS";
}
if (args.target.parents('.fields_list_body').hasClass('columns')) {
this.axis = "COLUMNS";
}
if (args.target.parents('.fields_list_body').hasClass('filter')) {
this.axis = "FILTER";
this.use_result_option = false;
}
}
// Resize when rendered
this.bind('open', this.post_render);
this.render();
$(this.el).parent().find('.ui-dialog-titlebar-close').bind('click',this.finished);
// Fetch available members
this.member = new Member({}, {
cube: args.workspace.selected_cube,
dimension: args.key
});
// Load template
$(this.el).find('.dialog_body')
.html(_.template($("#template-selections").html())(this));
var hName = this.member.hierarchy;
var lName = this.member.level;
var hierarchy = this.workspace.query.helper.getHierarchy(hName);
var level = null;
if (hierarchy && hierarchy.levels.hasOwnProperty(lName)) {
level = hierarchy.levels[lName];
}
if ((this.source === 'DateFilterModal' && (_.has(level, 'selection') && level.selection.members.length === 0)) ||
(this.source === 'DateFilterModal' && (_.size(level) === 1 && _.has(level, 'name')))) {
this.$el.find('.dialog_footer a:nth-child(2)').show();
}
else {
this.$el.find('.dialog_footer a:nth-child(2)').hide();
}
if (Settings.ALLOW_PARAMETERS) {
if (level) {
var pName = level.selection ? level.selection.parameterName : null;
if (pName) {
$(this.el).find('#parameter').val(pName);
if(this.query.helper.model().parameters[pName]!=undefined) {
this.paramvalue = this.query.helper.model().parameters[pName].split(",");
}
}
}
$(this.el).find('.parameter').removeClass('hidden');
}
this.init_totals(hierarchy, level);
$(this.el).find('#use_result').attr('checked', this.use_result_option);
$(this.el).find('.search_limit').text(this.members_search_limit);
$(this.el).find('.members_limit').text(this.members_limit);
var calcMembers = this.workspace.query.helper.getCalculatedMembers();
if (calcMembers.length > 0) {
this.fetch_calcmembers_levels();
}
else {
this.get_members();
}
}
},
修改了SelectionModal.js文件中get_member方法： 如果有参数标记就直接返回statisdate所有数据，然后回调Query.js
get_members: function(args) {
if(args != null && args != undefined && args != "" && args.key == "statisdateSpec"){
var url ="/result/metadata/hierarchies/%5Bstatisdate%5D.%5Bstatisdate%5D/levels/statisdate";
this.workspace = args.workspace;
var exModel = args.exModel;
var result =args.result;
//var tmpThis = this;
this.workspace.query.action.gett(url, {
success: function(model,response){
//alert(response); 調用Query.js,將結果再次返回給Query.js頁面的方法中去匹配
(new Query({
flag: "resultForstatisdate",
response: response,
exModel: exModel,
result: result
}));
},
error: function() {
self.workspace.unblock();
},
data: {result: false, searchlimit: 30000 }});
}else{
var self = this;
var path = "/result/metadata/hierarchies/" + encodeURIComponent(this.member.hierarchy) + "/levels/" + encodeURIComponent(this.member.level);
this.search_path = path;
var message = '<span class="processing_image"> </span> <span class="i18n">' + self.message + '</span> ';
self.workspace.block(message);
/**
* gett isn't a typo, although someone should probably rename that method to avoid confusion.
*/
this.workspace.query.action.gett(path, {
success: this.fetch_members,
error: function() {
self.workspace.unblock();
},
data: {result: this.use_result_option, searchlimit: this.members_limit }});
}
},
ps: 在这些方法调用时，有些需要保存的数据信息我都通过参数的形式来回传输了，所以千万别丢了参数哦！！！
完整的实现代码（可直接替换本地的Query.js，SelectionsModal.js文件）：
ps:如果你的日期字段命名为其它，可以全局替换 statisdate为其它命名信息。
Query.js
/*
* Copyright 2012 OSBI Ltd
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/ /**
* Workspace query
*/
var Query = Backbone.Model.extend({ formatter: Settings.CELLSET_FORMATTER,
properties: null, /*初始化方法*/
initialize: function(args, options) {
if(args != null && args != undefined && args != "" && args.flag == "resultForstatisdate"){ this.get_all_statisdate(args);
}else{
// Save cube
_.extend(this, options); // Bind `this`
_.bindAll(this, "run"); // Generate a unique query id
this.uuid = 'xxxxxxxx-xxxx-xxxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
function (c) {
var r = Math.random() * 16 | 0,
v = c == 'x' ? r : (r & 0x3 | 0x8);
return v.toString(16);
}).toUpperCase(); this.model = _.extend({ name: this.uuid }, SaikuOlapQueryTemplate);
if (args.cube) {
this.model.cube = args.cube;
}
this.helper = new SaikuOlapQueryHelper(this); // Initialize properties, action handler, and result handler
this.action = new QueryAction({}, { query: this });
this.result = new Result({ limit: Settings.RESULT_LIMIT }, { query: this });
this.scenario = new QueryScenario({}, { query: this }); // A flag to tell who changed selection members
this.updatedSelectionFromModal = false;
}
}, parse: function(response) {
// Assign id so Backbone knows to PUT instead of POST
this.id = this.uuid;
if (response.name) {
this.id = response.name;
this.uuid = response.name;
}
this.model = _.extend(this.model, response);
this.model.properties = _.extend({}, Settings.QUERY_PROPERTIES, this.model.properties);
}, setProperty: function(key, value) {
this.model.properties[key] = value;
}, getProperty: function(key) {
return this.model.properties[key];
}, syncSelectionsModalAndUpdateParameters: function() {
if (this.updatedSelectionFromModal) {
var mParameters = this.helper.model().parameters;
for (var mKey in mParameters) {
var mVal = mParameters[mKey];
var selections = this.helper.getSelectionsForParameter(mKey); mVal = selections.map(function(sel) { return sel.caption; }).join();
mParameters[mKey] = mVal;
}
} else {
var mParameters = this.helper.model().parameters;
for (var mKey in mParameters) {
var mVal = mParameters[mKey];
var mLevel = this.helper.getLevelForParameter(mKey);
var selections = this.helper.getSelectionsForParameter(mKey); if (mVal !== null && mVal !== undefined) {
this.helper.setSelectionsForParameter(mKey, _.filter(selections, function(sel) {
var containsParam = false;
_.each(mVal.split(','), function (v) {
if (sel.caption === v) {
containsParam = true;
return false;
}
});
return containsParam;
}));
}
}
} this.updatedSelectionFromModal = false;
}, /*执行查询的方法*/
run: function(force, mdx) {
this.syncSelectionsModalAndUpdateParameters(); var self = this;
// Check for automatic execution
Saiku.ui.unblock();
if (typeof this.model.properties != "undefined" && this.model.properties['saiku.olap.query.automatic_execution'] === false &&
(force === false || force === undefined || force === null)) {
return;
}
this.workspace.unblock(); $(this.workspace.el).find(".workspace_results_info").empty();
this.workspace.trigger('query:run');
this.result.result = null;
var validated = false;
var errorMessage = '<span class="i18n">Query Validation failed!</span>'; var exModel = this.helper.model();
for(var k in this.attributes) {
var att = this.attributes[k];
if(k.substring(0,5)==="PARAM"){
var p = k.substring(5, k.length);
exModel.parameters[p] = att;
} }
if (exModel.queryType == "OLAP") {
if (exModel.type == "QUERYMODEL") {
var columnsOk = Object.keys(exModel.queryModel.axes.COLUMNS.hierarchies).length > 0;
var rowsOk = Object.keys(exModel.queryModel.axes.ROWS.hierarchies).length > 0;
var detailsOk = exModel.queryModel.details.axis == 'COLUMNS' && exModel.queryModel.details.measures.length > 0;
if (!rowsOk || !columnsOk || !detailsOk) {
errorMessage = "";
}
if (!columnsOk && !detailsOk) {
errorMessage += '<span class="i18n">You need to include at least one measure or a level on columns for a valid query.</span>';
}
if(!rowsOk) {
errorMessage += '<span class="i18n">You need to include at least one level on rows for a valid query.</span>'; }
if ( (columnsOk || detailsOk) && rowsOk) {
validated = true;
} } else if (exModel.type == "MDX") {
validated = (exModel.mdx && exModel.mdx.length > 0);
if (!validated) {
errorMessage = '<span class="i18n">You need to enter some MDX statement to execute.</span>';
}
}
}
if (!validated) {
this.workspace.table.clearOut();
$(this.workspace.processing).html(errorMessage).show();
this.workspace.adjust();
Saiku.i18n.translate();
return;
} // Run it
this.workspace.table.clearOut();
$(this.workspace.processing).html('<span class="processing_image"> </span> <span class="i18n">Running query...</span> [ <a class="cancel i18n" href="#cancel">Cancel</a> ]').show();
this.workspace.adjust();
this.workspace.trigger('query:fetch');
Saiku.i18n.translate();
var message = '<span class="processing_image"> </span> <span class="i18n">Running query...</span> [ <a class="cancel i18n" href="#cancel">Cancel</a> ]';
this.workspace.block(message);
/*
TODO: i wonder if we should clean up the model (name and captions etc.)
delete this.model.queryModel.axes['FILTER'].name;
*/ /**根据ROWS中的statisdate字段过滤！*/
//根據用戶輸入的開始日期與結束日期查詢範圍數據
/*
var dimensionArr = exModel.queryModel.axes.ROWS.hierarchies; //取出行信息中的所有维度信息 dimension,用一个数组接收
var statisdateFlag = "no";
for(var i=0;i<dimensionArr.length;i++){
//判断维度信息中是否有statisdate这个时间维度（这是固定的）
if(dimensionArr[i]!=null && ((dimensionArr[i].dimension == "statisdate")||(dimensionArr[i].levels.statisdate != null)) ){
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
//判断参数是否为空
if(paramsURI.startdate != null && paramsURI.startdate != undefined && paramsURI.startdate != ""&&
paramsURI.enddate != null && paramsURI.enddate != undefined && paramsURI.enddate != ""){
statisdateFlag = "yes";
this.startdateThis=null;
this.enddateThis=null;
var key="statisdateSpec";
(new SelectionsModal({
key: key,
exModel: exModel,
result: this.result,
workspace: this.workspace
})); }
}
}
*/ /**根据Fliter中的statisdate字段过滤！*/
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies; //取出FILTER信息中的所有维度信息 dimension,用一个数组接收
var statisdateFlag = "no";
for(var i=0;i<dimensionArr.length;i++){
//判断维度信息中是否有statisdate这个时间维度（这是固定的）
if(dimensionArr[i]!=null && ((dimensionArr[i].dimension == "statisdate")||(dimensionArr[i].levels.statisdate != null)) ){
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
//判断参数是否为空
if(paramsURI.startdate != null && paramsURI.startdate != undefined && paramsURI.startdate != ""&&
paramsURI.enddate != null && paramsURI.enddate != undefined && paramsURI.enddate != ""){
statisdateFlag = "yes";
this.startdateThis=null;
this.enddateThis=null;
var key="statisdateSpec";
(new SelectionsModal({
key: key,
exModel: exModel,
result: this.result,
workspace: this.workspace
})); }
}
} //根据入参是否有statisdate字段来判断是否执行以下查询
if(statisdateFlag == 'no'){
this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}
}, //得到返回的所有statisdate參數信息
get_all_statisdate: function(args){
var response = args.response;
this.result = args.result;
//var exModel = args.exModel;
var statisdateArr= new Array();
for(var i=0;i<response.length;i++){
statisdateArr[i]=response[i].name;
}
//this.helper = new SaikuOlapQueryHelper(this);
var exModel = args.exModel;
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
var startdate=null;
var enddate=null;
if(this.startdateThis != null){
startdate=this.dateToStr(this.startdateThis); //获取修正后的开始日期
}else{
startdate=paramsURI.startdate;//开始日期（参数中的）
} if(this.enddateThis != null){
enddate=this.dateToStr(this.enddateThis); //获取修正后的结束日期
}else{
enddate=paramsURI.enddate;//结束日期（参数中的）
} //startdate = this.dateToStr(startdate); // 日期格式化
//enddate = this.dateToStr(enddate); // 日期格式化
if(statisdateArr.indexOf(startdate) > -1){// 这里是判断开始日期是否属于statisdateArr if(statisdateArr.indexOf(enddate) > -1){// 这里是判断结束日期是否属于statisdateArr //var dimensionArr = exModel.queryModel.axes.ROWS.hierarchies; //根据ROWS
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies; //根据Filter
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式
}
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}else{//结束日期不存在时：如果结束日期大于开始日期，将结束日期往前减一天 var tmpStartdate = this.strToDate(startdate);//StringToDate方法不存在
var tmpEnddate = this.strToDate(enddate); if(tmpEnddate>=tmpStartdate){
tmpEnddate = tmpEnddate.valueOf();
tmpEnddate = tmpEnddate - 1*24*60*60*1000;
tmpEnddate = new Date(tmpEnddate); //这里改变paramURL中的结束日期参数值，然后回调当前函数 get_all_statisdate
this.enddateThis=tmpEnddate;
this.get_all_statisdate(args); }else{ //否则的话直接执行查询，直接无数据返回 var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
enddate = this.dateToStr(tmpStartdate)
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+enddate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式 }
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} }); }
}
}else{ //开始日期不存在时: 如果开始日期小于结束日期，将开始日期往后加一天 var tmpStartdate = this.strToDate(startdate);
var tmpEnddate = this.strToDate(enddate);
if(tmpStartdate<=tmpEnddate){
tmpStartdate = tmpStartdate.valueOf();
tmpStartdate = tmpStartdate + 1*24*60*60*1000;
tmpStartdate = new Date(tmpStartdate); //这里改变paramURL中的结束日期参数值，然后回调当前函数get_all_statisdate
this.startdateThis=tmpStartdate;
this.get_all_statisdate(args);
}else{ //否则的话直接执行查询，直接无数据返回 var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
startdate = this.dateToStr(tmpEnddate)
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+startdate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式
}
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} }); }
}
}, /*將string類型的數據轉換為date類型*/
strToDate: function(strDate){
strDate = strDate.toLocaleString();
strDate = strDate.substring(-1,10);
var convert = Date.parse(strDate);
var tmpdate = new Date(convert);
return tmpdate;
}, /*将Date类型转换为String类型 格式为：2019-03-03*/
dateToStr: function(dateStr){
//dateStr = this.strToDate(dateStr);
var tmpYear= dateStr.getFullYear();
var tmpMonth = dateStr.getMonth()+1;
var tmpDay = dateStr.getDate();
if(tmpMonth < 10){ tmpMonth = "0"+tmpMonth;}
if(tmpDay < 10){ tmpDay = "0"+tmpDay;}
var tmpdate = tmpYear+"-"+tmpMonth+"-"+tmpDay;
return tmpdate;
}, enrich: function() {
var self = this;
this.workspace.query.action.post("/../enrich", {
contentType: "application/json",
data: JSON.stringify(self.model),
async: false,
success: function(response, model) {
self.model = model;
}
});
}, url: function() {
return "api/query/" + encodeURI(this.uuid);
}
/**
get_statisdate_members: function() {
var path = "/result/metadata/hierarchies/%5Bstatisdate%5D.%5Bstatisdate%5D/levels/statisdate"; * gett isn't a typo, although someone should probably rename that method to avoid confusion. this.workspace.query.action.gett(path, {
success: this.fetch_statisdate_members,
error: function() {
alert("error");
},
data: {result: true, searchlimit: 30000 }});
}, 得到獲取statisdate字段的結果信息然後處理
fetch_statisdate_members: function(model, response) { if (response && response.length > 0) {
alert("Fetch_statisdate_member++++++++++++"+response);
} }, */
});
SelectionsModal.js
/*
* Copyright 2012 OSBI Ltd
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/ /**
* Dialog for member selections
*/
var SelectionsModal = Modal.extend({
type: "selections", paramvalue: null, buttons: [
{ text: "OK", method: "save" },
{ text: "Open Date Filter", method: "open_date_filter" },
{ text: "Cancel", method: "close" }
], events: {
'click a': 'call',
'click .search_term' : 'search_members',
'click .clear_search' : 'clear_search',
'change #show_unique': 'show_unique_action',
'change #use_result': 'use_result_action',
'dblclick .selection_options li.option_value label' : 'click_move_selection',
'click li.all_options' : 'click_all_member_selection',
'change #per_metrics_totals_checkbox': 'per_metrics_totals_action'
//,'click div.updown_buttons a.form_button': 'updown_selection'
}, show_unique_option: false,
per_metrics_totals: false, use_result_option: Settings.MEMBERS_FROM_RESULT,
show_totals_option: [],
members_limit: Settings.MEMBERS_LIMIT,
members_search_limit: Settings.MEMBERS_SEARCH_LIMIT,
members_search_server: false,
selection_type: "INCLUSION", initialize: function(args) {
if(args != null && args != undefined && args != "" && args.key == "statisdateSpec"){
this.query = args.workspace.query;
this.get_members(args);
}else{
// Initialize properties
var self = this;
_.extend(this, args);
this.options.title = "<span class='i18n'>Selections for</span> " + this.name;
this.message = "Fetching members...";
this.query = args.workspace.query;
this.selected_members = [];
this.available_members = [];
this.topLevel; _.bindAll(this, "fetch_members", "populate", "finished", "get_members", "use_result_action", "per_metrics_totals_action"); // Determine axis
this.axis = "undefined";
if (args.axis) {
this.axis = args.axis;
if (args.axis == "FILTER") {
this.use_result_option = false;
}
} else {
if (args.target.parents('.fields_list_body').hasClass('rows')) {
this.axis = "ROWS";
}
if (args.target.parents('.fields_list_body').hasClass('columns')) {
this.axis = "COLUMNS";
}
if (args.target.parents('.fields_list_body').hasClass('filter')) {
this.axis = "FILTER";
this.use_result_option = false;
}
}
// Resize when rendered
this.bind('open', this.post_render);
this.render(); $(this.el).parent().find('.ui-dialog-titlebar-close').bind('click',this.finished); // Fetch available members
this.member = new Member({}, {
cube: args.workspace.selected_cube,
dimension: args.key
}); // Load template
$(this.el).find('.dialog_body')
.html(_.template($("#template-selections").html())(this)); var hName = this.member.hierarchy;
var lName = this.member.level;
var hierarchy = this.workspace.query.helper.getHierarchy(hName);
var level = null;
if (hierarchy && hierarchy.levels.hasOwnProperty(lName)) {
level = hierarchy.levels[lName];
} if ((this.source === 'DateFilterModal' && (_.has(level, 'selection') && level.selection.members.length === 0)) ||
(this.source === 'DateFilterModal' && (_.size(level) === 1 && _.has(level, 'name')))) {
this.$el.find('.dialog_footer a:nth-child(2)').show();
}
else {
this.$el.find('.dialog_footer a:nth-child(2)').hide();
} if (Settings.ALLOW_PARAMETERS) {
if (level) {
var pName = level.selection ? level.selection.parameterName : null;
if (pName) {
$(this.el).find('#parameter').val(pName); if(this.query.helper.model().parameters[pName]!=undefined) {
this.paramvalue = this.query.helper.model().parameters[pName].split(",");
} }
}
$(this.el).find('.parameter').removeClass('hidden');
} this.init_totals(hierarchy, level); $(this.el).find('#use_result').attr('checked', this.use_result_option);
$(this.el).find('.search_limit').text(this.members_search_limit);
$(this.el).find('.members_limit').text(this.members_limit); var calcMembers = this.workspace.query.helper.getCalculatedMembers(); if (calcMembers.length > 0) {
this.fetch_calcmembers_levels();
}
else {
this.get_members();
}
}
}, init_totals: function (hierarchy, level) {
var self = this;
// Add the selections totals
var measuresArray = self.workspace.query.model.queryModel.details.measures;
for (var j = 0; j < measuresArray.length; j++) {
$(this.el).find('#div-totals-container').append(_.template($("#template-selections-totals").html())({measure: measuresArray[j]}));
} if (_.size(hierarchy.levels) > 1 && level && level.hasOwnProperty('aggregators') && level.aggregators && level.aggregators.length > 0) {
this.show_totals_option = level.aggregators;
if (_.uniq(this.show_totals_option).length > 1) {
this.per_metrics_totals_action();
}
} else {
this.show_totals_option = [];
} if (this.show_totals_option.length > 0) {
if (this.per_metrics_totals) {
$(this.el).find('.show_totals_select').each(function (index) {
if (typeof self.show_totals_option[index] !== 'undefined') {
$(this).val(self.show_totals_option[index]);
}
});
} else {
$(this.el).find('#all_measures_select').val(self.show_totals_option[0])
}
}
$(this.el).find('#per_metrics_totals_checkbox').attr('checked', this.per_metrics_totals); }, open_date_filter: function(event) {
event.preventDefault(); // Launch date filter dialog
(new DateFilterModal({
dimension: this.objDateFilter.dimension,
hierarchy: this.objDateFilter.hierarchy,
target: this.target,
name: this.name,
data: this.objDateFilter.data,
analyzerDateFormat: this.objDateFilter.analyzerDateFormat,
dimHier: this.objDateFilter.dimHier,
key: this.key,
workspace: this.workspace
})).open(); this.$el.dialog('destroy').remove();
}, per_metrics_totals_action: function(event) {
this.per_metrics_totals = ! this.per_metrics_totals; if(this.per_metrics_totals === true) {
$(this.el).find('.per_metrics_container').show();
$(this.el).find('.all_metrics_container').hide();
} else {
$(this.el).find('.per_metrics_container').hide();
$(this.el).find('.all_metrics_container').show();
}
}, get_members: function(args) {
if(args != null && args != undefined && args != "" && args.key == "statisdateSpec"){
var url ="/result/metadata/hierarchies/%5Bstatisdate%5D.%5Bstatisdate%5D/levels/statisdate";
this.workspace = args.workspace;
var exModel = args.exModel;
var result =args.result;
//var tmpThis = this;
this.workspace.query.action.gett(url, {
success: function(model,response){
//alert(response); 調用Query.js,將結果再次返回給Query.js頁面的方法中去匹配
(new Query({
flag: "resultForstatisdate",
response: response,
exModel: exModel,
result: result
}));
},
error: function() {
self.workspace.unblock();
},
data: {result: false, searchlimit: 30000 }});
}else{
var self = this;
var path = "/result/metadata/hierarchies/" + encodeURIComponent(this.member.hierarchy) + "/levels/" + encodeURIComponent(this.member.level);
this.search_path = path; var message = '<span class="processing_image"> </span> <span class="i18n">' + self.message + '</span> ';
self.workspace.block(message); /**
* gett isn't a typo, although someone should probably rename that method to avoid confusion.
*/
this.workspace.query.action.gett(path, {
success: this.fetch_members,
error: function() {
self.workspace.unblock();
},
data: {result: this.use_result_option, searchlimit: this.members_limit }}); }
}, clear_search: function() {
$(this.el).find('.filterbox').val('');
this.available_members = [];
this.get_members();
}, search_members: function() {
var self = this;
var search_term = $(this.el).find('.filterbox').val();
if (!search_term)
return false; var message = '<span class="processing_image"> </span> <span class="i18n">Searching for members matching:</span> ' + search_term;
self.workspace.block(message);
self.workspace.query.action.gett(self.search_path, {
async: false, success: function(response, model) {
if (model && model.length > 0) {
self.available_members = model;
}
self.populate();
},
error: function () {
self.workspace.unblock();
},
data: { search: search_term, searchlimit: self.members_search_limit }
});
}, fetch_calcmembers_levels: function() {
var dimHier = this.member.hierarchy.split('].[');
var m4=true;
if(dimHier.length===1){
m4=false;
dimHier = this.member.hierarchy.split('.'); }
if(dimHier.length>1){
var hName = dimHier[1].replace(/[\[\]]/gi, '');
}
var dName = dimHier[0].replace(/[\[\]]/gi, ''); var message = '<span class="processing_image"> </span> <span class="i18n">' + this.message + '</span> ';
this.workspace.block(message); if(!m4){
if(hName!=undefined) {
hName = dName + "." + hName;
}
else{
hName = dName;
}
}
var level = new Level({}, {
ui: this,
cube: this.workspace.selected_cube,
dimension: dName,
hierarchy: hName
}); level.fetch({
success: this.get_levels
});
}, get_levels: function(model, response) {
if (response && response.length > 0) {
model.ui.topLevel = response[0];
model.ui.get_members();
}
}, get_calcmembers: function() {
var self = this;
var hName = this.member.hierarchy;
var calcMembers = this.workspace.query.helper.getCalculatedMembers();
var arrCalcMembers = []; if (this.topLevel.name === this.member.level) {
_.filter(calcMembers, function(value) {
if (value.hierarchyName === hName && _.isEmpty(value.parentMember)) {
value.uniqueName = value.hierarchyName + '.[' + value.name + ']';
arrCalcMembers.push(value);
}
});
}
else {
_.filter(calcMembers, function(value) {
if (value.hierarchyName === hName && value.parentMemberLevel === self.member.level) {
value.uniqueName = value.parentMember + '.[' + value.name + ']';
arrCalcMembers.push(value);
}
});
} return arrCalcMembers;
}, fetch_members: function(model, response) {
var self = this;
if (response && response.length > 0) {
_.each(response, function(f){
var cmem = self.workspace.query.helper.getCalculatedMembers();
var calc = false;
if(cmem && cmem.length>0){
_.each(cmem, function(c){
if(c.uniqueName === f.uniqueName){
calc = true;
}
})
}
self.available_members.push({obj:f, calc:calc}); });
}
this.populate();
}, populate: function(model, response) {
var self = this;
self.workspace.unblock();
this.members_search_server = (this.available_members.length >= this.members_limit || this.available_members.length == 0); self.show_unique_option = false;
$(this.el).find('.options #show_unique').attr('checked',false); var calcMembers = this.workspace.query.helper.getCalculatedMembers(); if (calcMembers.length > 0) {
var newCalcMembers = this.get_calcmembers();
var len = newCalcMembers.length; for (var i = 0; i < len; i++) {
var calc = false;
if(calcMembers && calcMembers.length>0){
_.each(calcMembers, function(c){
if(c.uniqueName === newCalcMembers[i].uniqueName){
calc = true;
}
})
}
this.available_members.push({obj:newCalcMembers[i], calc:calc});
}
} $(this.el).find('.items_size').text(this.available_members.length); if (this.members_search_server) {
$(this.el).find('.warning').text("More items available than listed. Pre-Filter on server.");
} else {
$(this.el).find('.warning').text("");
} var hName = self.member.hierarchy;
var lName = self.member.level;
var hierarchy = self.workspace.query.helper.getHierarchy(hName); if (hierarchy && hierarchy.levels.hasOwnProperty(lName)) {
this.selected_members = [];
if(hierarchy.levels[lName].selection){
_.each(hierarchy.levels[lName].selection.members, function(f){
var cmem = self.workspace.query.helper.getCalculatedMembers();
var calc = false; if(cmem && cmem.length > 0){
_.each(cmem, function(c){
if(c.uniqueName === f.uniqueName){
calc = true;
}
});
} self.selected_members.push({obj: f, calc: calc});
});
}
this.selection_type = hierarchy.levels[lName].selection ? hierarchy.levels[lName].selection.type : "INCLUSION";
} var used_members = []; // Populate both boxes
for (var j = 0, len = this.selected_members.length; j < len; j++) {
var member = this.selected_members[j];
used_members.push(member.obj.caption);
} if ($(this.el).find('.used_selections .selection_options li.option_value' ).length == 0) {
var selectedMembers = $(this.el).find('.used_selections .selection_options');
selectedMembers.empty();
var selectedHtml = _.template($("#template-selections-options").html())({ options: this.selected_members });
$(selectedMembers).html(selectedHtml);
} // Filter out used members
this.available_members = _.select(this.available_members, function(o) {
return used_members.indexOf(o.obj ? o.obj.caption : o.caption) === -1;
}); if (this.available_members.length > 0) {
var availableMembersSelect = $(this.el).find('.available_selections .selection_options');
availableMembersSelect.empty();
var selectedHtml = _.template($("#template-selections-options").html())({ options: this.available_members });
$(availableMembersSelect).html(selectedHtml);
} if ($(self.el).find( ".selection_options.ui-selectable" ).length > 0) {
$(self.el).find( ".selection_options" ).selectable( "destroy" );
} $(self.el).find( ".selection_options" ).selectable({ distance: 20, filter: "li", stop: function( event, ui ) {
$(self.el).find( ".selection_options li.ui-selected input").each(function(index, element) {
if (element && element.hasAttribute('checked')) {
element.checked = true;
} else {
$(element).attr('checked', true);
}
$(element).parents('.selection_options').find('li.all_options input').prop('checked', true);
});
$(self.el).find( ".selection_options li.ui-selected").removeClass('ui-selected');
}}); $(this.el).find('.filterbox').autocomplete({
minLength: 1, //(self.members_search_server ? 2 : 1),
delay: 200, //(self.members_search_server ? 400 : 300),
appendTo: ".autocomplete",
source: function(request, response ) {
var searchlist = self.available_members;
var search_target = self.show_unique_option == false ? "caption" : "name";
var result = $.map( searchlist, function( item ) {
var st = item.obj;
var obj; if(st === undefined){
st = item;
obj = st[search_target];
} else{
obj = st.caption;
} if (obj.toLowerCase().indexOf(request.term.toLowerCase()) > -1) {
var label = self.show_unique_option == false? st.caption : st.uniqueName;
var value = self.show_unique_option == false? st.uniqueName : st.caption; return {
label: label,
value: value
};
}
}); response(result);
},
select: function(event, ui) {
var value = encodeURIComponent(ui.item.value);
var label = ui.item.label;
var searchVal = self.show_unique_option == false? ui.item.value : ui.item.label;
var cap = self.show_unique_option == false? ui.item.label : ui.item.value; $(self.el).find('.available_selections .selection_options input[value="' + encodeURIComponent(searchVal) + '"]').parent().remove();
$(self.el).find('.used_selections .selection_options input[value="' + encodeURIComponent(searchVal) + '"]').parent().remove(); var option = '<li class="option_value"><input type="checkbox" class="check_option" value="'
+ encodeURIComponent(searchVal) + '" label="' + encodeURIComponent(cap) + '">' + label + '</input></li>'; $(option).appendTo($(self.el).find('.used_selections .selection_options ul'));
$(self.el).find('.filterbox').val('');
ui.item.value = ""; },
close: function(event, ui) {
//$('#filter_selections').val('');
//$(self.el).find('.filterbox').css({ "text-align" : " left"});
},
open: function( event, ui ) {
//$(self.el).find('.filterbox').css({ "text-align" : " right"});
}
}); $(this.el).find('.filterbox').autocomplete("enable");
if (this.selection_type === "EXCLUSION") {
$(this.el).find('.selection_type_inclusion').prop('checked', false);
$(this.el).find('.selection_type_exclusion').prop('checked', true);
} else {
$(this.el).find('.selection_type_inclusion').prop('checked', true);
$(this.el).find('.selection_type_exclusion').prop('checked', false);
} // Translate
Saiku.i18n.translate();
// Show dialog
Saiku.ui.unblock();
}, post_render: function(args) {
var left = ($(window).width() - 1000)/2;
var width = $(window).width() < 1040 ? $(window).width() : 1040;
$(args.modal.el).parents('.ui-dialog')
.css({ width: width, left: "inherit", margin:"0", height: 630 })
.offset({ left: left}); $('#filter_selections').attr("disabled", false);
$(this.el).find('a[href=#save]').focus();
$(this.el).find('a[href=#save]').blur();
}, move_selection: function(event) {
event.preventDefault();
var action = $(event.target).attr('id');
var $to = action.indexOf('add') !== -1 ?
$(this.el).find('.used_selections .selection_options ul') :
$(this.el).find('.available_selections .selection_options ul');
var $from = action.indexOf('add') !== -1 ?
$(this.el).find('.available_selections .selection_options ul') :
$(this.el).find('.used_selections .selection_options ul');
var $els = action.indexOf('all') !== -1 ?
$from.find('li.option_value input').parent() : $from.find('li.option_value input:checked').parent();
$els.detach().appendTo($to);
$(this.el).find('.selection_options ul li.option_value input:checked').prop('checked', false);
$(this.el).find('.selection_options li.all_options input').prop('checked', false);
}, updown_selection: function(event) {
event.preventDefault();
return false;
/*
var action = $(event.target).attr('href').replace('#','');
if (typeof action != "undefined") {
if ("up" == action) {
$(this.el).find('.used_selections option:selected').insertBefore( $('.used_selections option:selected:first').prev());
} else if ("down" == action) {
$(this.el).find('.used_selections option:selected').insertAfter( $('.used_selections option:selected:last').next());
} }
*/
}, click_all_member_selection: function(event, ui) {
var checked = $(event.currentTarget).find('input').is(':checked');
if (!checked) {
$(event.currentTarget).parent().find('li.option_value input').removeAttr('checked');
} else {
$(event.currentTarget).parent().find('li.option_value input').prop('checked', true);
} }, click_move_selection: function(event, ui) {
event.preventDefault();
var to = ($(event.target).parent().parent().parent().parent().hasClass('used_selections')) ? '.available_selections' : '.used_selections';
$(event.target).parent().appendTo($(this.el).find(to +' .selection_options ul'));
}, show_unique_action: function() {
var self = this;
this.show_unique_option= ! this.show_unique_option; if(this.show_unique_option === true) {
$(this.el).find('.available_selections, .used_selections').addClass('unique');
$(this.el).find('.available_selections, .used_selections').removeClass('caption');
} else {
$(this.el).find('.available_selections, .used_selections').addClass('caption');
$(this.el).find('.available_selections, .used_selections').removeClass('unique');
} }, use_result_action: function() {
this.use_result_option = !this.use_result_option;
this.get_members();
}, save: function() {
this.query.updatedSelectionFromModal = true;
var self = this;
// Notify user that updates are in progress
var $loading = $("<div>Saving...</div>");
$(this.el).find('.dialog_body').children().hide();
$(this.el).find('.dialog_body').prepend($loading);
var show_u = this.show_unique_option; var hName = decodeURIComponent(self.member.hierarchy);
var lName = decodeURIComponent(self.member.level)
var hierarchy = self.workspace.query.helper.getHierarchy(hName); // Determine updates
var updates = []; // If no selections are used, add level
if ($(this.el).find('.used_selections input').length === 0) {
// nothing to do - include all members of this level
} else {
self.workspace.query.helper.removeAllLevelCalculatedMember(hName); // Loop through selections
$(this.el).find('.used_selections .option_value input')
.each(function(i, selection) {
var value = $(selection).val();
if($(selection).hasClass("cmember")){
var caption = $(selection).attr('label'); self.workspace.toolbar.group_parents(); self.workspace.query.helper.includeLevelCalculatedMember(self.axis, hName, lName, decodeURIComponent(value), 0);
updates.push({
uniqueName: decodeURIComponent(value),
caption: decodeURIComponent(caption),
type: "calculatedmember"
});
}
else {
var caption = $(selection).attr('label');
updates.push({
uniqueName: decodeURIComponent(value),
caption: decodeURIComponent(caption)
});
} });
} var parameterName = $('#parameter').val();
if (hierarchy && hierarchy.levels.hasOwnProperty(lName)) {
var totalsArray = []; if(this.per_metrics_totals === true) {
$('.show_totals_select').each(function() {
totalsArray.push($(this).val());
});
} else {
var measuresArray = self.workspace.query.model.queryModel.details.measures;
for (var j = 0; j < measuresArray.length; j++) {
totalsArray.push($('#all_measures_select').val());
}
} hierarchy.levels[lName]["aggregators"] = totalsArray; var selectionType = $(self.el).find('input.selection_type:checked').val();
selectionType = selectionType ? selectionType : "INCLUSION";
hierarchy.levels[lName].selection = { "type": selectionType, "members": updates };
if (Settings.ALLOW_PARAMETERS && parameterName) {
hierarchy.levels[lName].selection["parameterName"] = parameterName;
var parameters = self.workspace.query.helper.model().parameters;
if (!parameters[parameterName]) {
// self.workspace.query.helper.model().parameters[parameterName] = "";
} } } this.finished();
}, finished: function() {
$('#filter_selections').remove();
this.available_members = null;
$(this.el).find('.filterbox').autocomplete('destroy').remove();
$(this.el).dialog('destroy');
$(this.el).remove();
this.query.run();
}
});
================二次更新==因为开始日期结束日期为同一天且两个日期都不存在时，缺少了判断，现在添加进来~ 不然数据就不准确了 20190530==========
if(this.enddateThis != null){
enddate=this.dateToStr(this.enddateThis); //获取修正后的结束日期
}else{
enddate=paramsURI.enddate;//结束日期（参数中的）
}
/*上方代码是之前就添加好的，这次添加的是下方的代码*/
//如果开始日期与结束日期相等且都不存在，则直接查询
if((startdate == enddate) && (statisdateArr.indexOf(startdate) == -1) && (statisdateArr.indexOf(enddate) == -1)){
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
//exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式
}
}
this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}else
/*下方代码是之前就添加好的，这次添加的是上方的代码*/
if(statisdateArr.indexOf(startdate) > -1){// 这里是判断开始日期是否属于statisdateArr
更改之后完整的Query.js文件
/*
* Copyright 2012 OSBI Ltd
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/ /**
* Workspace query
*/
var Query = Backbone.Model.extend({ formatter: Settings.CELLSET_FORMATTER,
properties: null, /*初始化方法*/
initialize: function(args, options) {
if(args != null && args != undefined && args != "" && args.flag == "resultForstatisdate"){ this.get_all_statisdate(args);
}else{
// Save cube
_.extend(this, options); // Bind `this`
_.bindAll(this, "run"); // Generate a unique query id
this.uuid = 'xxxxxxxx-xxxx-xxxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g,
function (c) {
var r = Math.random() * 16 | 0,
v = c == 'x' ? r : (r & 0x3 | 0x8);
return v.toString(16);
}).toUpperCase(); this.model = _.extend({ name: this.uuid }, SaikuOlapQueryTemplate);
if (args.cube) {
this.model.cube = args.cube;
}
this.helper = new SaikuOlapQueryHelper(this); // Initialize properties, action handler, and result handler
this.action = new QueryAction({}, { query: this });
this.result = new Result({ limit: Settings.RESULT_LIMIT }, { query: this });
this.scenario = new QueryScenario({}, { query: this }); // A flag to tell who changed selection members
this.updatedSelectionFromModal = false;
}
}, parse: function(response) {
// Assign id so Backbone knows to PUT instead of POST
this.id = this.uuid;
if (response.name) {
this.id = response.name;
this.uuid = response.name;
}
this.model = _.extend(this.model, response);
this.model.properties = _.extend({}, Settings.QUERY_PROPERTIES, this.model.properties);
}, setProperty: function(key, value) {
this.model.properties[key] = value;
}, getProperty: function(key) {
return this.model.properties[key];
}, syncSelectionsModalAndUpdateParameters: function() {
if (this.updatedSelectionFromModal) {
var mParameters = this.helper.model().parameters;
for (var mKey in mParameters) {
var mVal = mParameters[mKey];
var selections = this.helper.getSelectionsForParameter(mKey); mVal = selections.map(function(sel) { return sel.caption; }).join();
mParameters[mKey] = mVal;
}
} else {
var mParameters = this.helper.model().parameters;
for (var mKey in mParameters) {
var mVal = mParameters[mKey];
var mLevel = this.helper.getLevelForParameter(mKey);
var selections = this.helper.getSelectionsForParameter(mKey); if (mVal !== null && mVal !== undefined) {
this.helper.setSelectionsForParameter(mKey, _.filter(selections, function(sel) {
var containsParam = false;
_.each(mVal.split(','), function (v) {
if (sel.caption === v) {
containsParam = true;
return false;
}
});
return containsParam;
}));
}
}
} this.updatedSelectionFromModal = false;
}, /*执行查询的方法*/
run: function(force, mdx) { //add embed flag,when the parameter embedFlag equals hideToolbar,hide the toolbar. 20190508
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
if(paramsURI.embedFlag != undefined && paramsURI.embedFlag == "hideToolbar"){
Saiku.toolbar.remove();//hide toolbar
$('#header').remove(); //hide tabs
$('#new_icon').remove(); //hide open query button
$('#fullscreen_icon').remove(); //hide fullscreen_icon button
$("#save_icon").remove(); //hide save data button
} this.syncSelectionsModalAndUpdateParameters(); var self = this;
// Check for automatic execution
Saiku.ui.unblock();
if (typeof this.model.properties != "undefined" && this.model.properties['saiku.olap.query.automatic_execution'] === false &&
(force === false || force === undefined || force === null)) {
return;
}
this.workspace.unblock(); $(this.workspace.el).find(".workspace_results_info").empty();
this.workspace.trigger('query:run');
this.result.result = null;
var validated = false;
var errorMessage = '<span class="i18n">Query Validation failed!</span>'; var exModel = this.helper.model(); //适用于所有指标为空的的行数据也展示 不隐藏！！！ （最初是針對Summary數據做出更改 當行數據為空的時候 也需要展示出來）
// var showEmptyRows = paramsURI.showEmptyRows;
// if(showEmptyRows != null && showEmptyRows != undefined && showEmptyRows != '' && showEmptyRows=='yes') {
//針對Summary數據做出更改 當行數據為空的時候 也需要展示出來
var cubename = exModel.cube.name ;
if(cubename == "SummaryKPI_2018_ext" ||cubename == "SummaryKPI_2019_ext" ||cubename == "SummaryKPI_2019_Dynamic" ) {
exModel.queryModel.axes.ROWS.nonEmpty=false;//設置行數據為空的時候也顯示數據！ 20190425 for summaryKPI Data 20190517 for all param showEmptyRows=yes
}else{
exModel.queryModel.axes.ROWS.nonEmpty=true;
}
for(var k in this.attributes) {
var att = this.attributes[k];
if(k.substring(0,5)==="PARAM"){
var p = k.substring(5, k.length);
exModel.parameters[p] = att;
} }
if (exModel.queryType == "OLAP") {
if (exModel.type == "QUERYMODEL") {
var columnsOk = Object.keys(exModel.queryModel.axes.COLUMNS.hierarchies).length > 0;
var rowsOk = Object.keys(exModel.queryModel.axes.ROWS.hierarchies).length > 0;
var detailsOk = exModel.queryModel.details.axis == 'COLUMNS' && exModel.queryModel.details.measures.length > 0;
if (!rowsOk || !columnsOk || !detailsOk) {
errorMessage = "";
}
if (!columnsOk && !detailsOk) {
errorMessage += '<span class="i18n">You need to include at least one measure or a level on columns for a valid query.</span>';
}
if(!rowsOk) {
errorMessage += '<span class="i18n">You need to include at least one level on rows for a valid query.</span>'; }
if ( (columnsOk || detailsOk) && rowsOk) {
validated = true;
} } else if (exModel.type == "MDX") {
validated = (exModel.mdx && exModel.mdx.length > 0);
if (!validated) {
errorMessage = '<span class="i18n">You need to enter some MDX statement to execute.</span>';
}
}
}
if (!validated) {
this.workspace.table.clearOut();
$(this.workspace.processing).html(errorMessage).show();
this.workspace.adjust();
Saiku.i18n.translate();
return;
} // Run it
this.workspace.table.clearOut();
$(this.workspace.processing).html('<span class="processing_image"> </span> <span class="i18n">Running query...</span> [ <a class="cancel i18n" href="#cancel">Cancel</a> ]').show();
this.workspace.adjust();
this.workspace.trigger('query:fetch');
Saiku.i18n.translate();
var message = '<span class="processing_image"> </span> <span class="i18n">Running query...</span> [ <a class="cancel i18n" href="#cancel">Cancel</a> ]';
this.workspace.block(message);
/*
TODO: i wonder if we should clean up the model (name and captions etc.)
delete this.model.queryModel.axes['FILTER'].name;
*/ /**根据ROWS中的statisdate字段过滤！*/
//根據用戶輸入的開始日期與結束日期查詢範圍數據
/*
var dimensionArr = exModel.queryModel.axes.ROWS.hierarchies; //取出行信息中的所有维度信息 dimension,用一个数组接收
var statisdateFlag = "no";
for(var i=0;i<dimensionArr.length;i++){
//判断维度信息中是否有statisdate这个时间维度（这是固定的）
if(dimensionArr[i]!=null && ((dimensionArr[i].dimension == "statisdate")||(dimensionArr[i].levels.statisdate != null)) ){
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
//判断参数是否为空
if(paramsURI.startdate != null && paramsURI.startdate != undefined && paramsURI.startdate != ""&&
paramsURI.enddate != null && paramsURI.enddate != undefined && paramsURI.enddate != ""){
statisdateFlag = "yes";
this.startdateThis=null;
this.enddateThis=null;
var key="statisdateSpec";
(new SelectionsModal({
key: key,
exModel: exModel,
result: this.result,
workspace: this.workspace
})); }
}
}
*/ /**根据Fliter中的statisdate字段过滤！*/
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies; //取出FILTER信息中的所有维度信息 dimension,用一个数组接收
var statisdateFlag = "no";
for(var i=0;i<dimensionArr.length;i++){
//判断维度信息中是否有statisdate这个时间维度（这是固定的）
if(dimensionArr[i]!=null && ((dimensionArr[i].dimension == "statisdate")||(dimensionArr[i].levels.statisdate != null)) ){
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
//判断参数是否为空
if(paramsURI.startdate != null && paramsURI.startdate != undefined && paramsURI.startdate != ""&&
paramsURI.enddate != null && paramsURI.enddate != undefined && paramsURI.enddate != ""){
statisdateFlag = "yes";
this.startdateThis=null;
this.enddateThis=null;
var key="statisdateSpec";
(new SelectionsModal({
key: key,
exModel: exModel,
result: this.result,
workspace: this.workspace
})); }
}
} //根据入参是否有statisdate字段来判断是否执行以下查询
if(statisdateFlag == 'no'){
this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}
}, //得到返回的所有statisdate參數信息,并且执行查询返回结果数据。
get_all_statisdate: function(args){
var response = args.response;
this.result = args.result;
//var exModel = args.exModel;
var statisdateArr= new Array();
for(var i=0;i<response.length;i++){
statisdateArr[i]=response[i].name;
}
//this.helper = new SaikuOlapQueryHelper(this);
var exModel = args.exModel;
var paramsURI = Saiku.URLParams.paramsURI(); //get the param from url
var startdate=null;
var enddate=null;
if(this.startdateThis != null){
startdate=this.dateToStr(this.startdateThis); //获取修正后的开始日期
}else{
startdate=paramsURI.startdate;//开始日期（参数中的）
} if(this.enddateThis != null){
enddate=this.dateToStr(this.enddateThis); //获取修正后的结束日期
}else{
enddate=paramsURI.enddate;//结束日期（参数中的）
} //如果开始日期与结束日期相等且都不存在，则直接查询
if((startdate == enddate) && (statisdateArr.indexOf(startdate) == -1) && (statisdateArr.indexOf(enddate) == -1)){
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){ //更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
//exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式 }
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}else if(statisdateArr.indexOf(startdate) > -1){// 这里是判断开始日期是否属于statisdateArr if(statisdateArr.indexOf(enddate) > -1){// 这里是判断结束日期是否属于statisdateArr //var dimensionArr = exModel.queryModel.axes.ROWS.hierarchies; //根据ROWS
var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies; //根据Filter
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
//exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式
}
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} });
}else{//结束日期不存在时：如果结束日期大于开始日期，将结束日期往前减一天 var tmpStartdate = this.strToDate(startdate);//StringToDate方法不存在
var tmpEnddate = this.strToDate(enddate); if(tmpEnddate>=tmpStartdate){
tmpEnddate = tmpEnddate.valueOf();
tmpEnddate = tmpEnddate - 1*24*60*60*1000;
tmpEnddate = new Date(tmpEnddate); //这里改变paramURL中的结束日期参数值，然后回调当前函数 get_all_statisdate
this.enddateThis=tmpEnddate;
this.get_all_statisdate(args); }else{ //否则的话直接执行查询，直接无数据返回 var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
enddate = this.dateToStr(tmpStartdate)
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+enddate+"]:[statisdate].[statisdate].[statisdate].["+enddate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
//exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式 }
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} }); }
}
}else{ //开始日期不存在时: 如果开始日期小于结束日期，将开始日期往后加一天 var tmpStartdate = this.strToDate(startdate);
var tmpEnddate = this.strToDate(enddate);
if(tmpStartdate<=tmpEnddate){
tmpStartdate = tmpStartdate.valueOf();
tmpStartdate = tmpStartdate + 1*24*60*60*1000;
tmpStartdate = new Date(tmpStartdate); //这里改变paramURL中的结束日期参数值，然后回调当前函数get_all_statisdate
this.startdateThis=tmpStartdate;
this.get_all_statisdate(args);
}else{ //否则的话直接执行查询，直接无数据返回 var dimensionArr = exModel.queryModel.axes.FILTER.hierarchies;
for(var i=0;i<dimensionArr.length;i++){
if(dimensionArr[i]!=null && (dimensionArr[i].dimension == "statisdate" || dimensionArr[i].levels.statisdate != null ) ){
startdate = this.dateToStr(tmpEnddate)
//更改level下的mdx表达式(使用開始日期與結束日期來控制過濾數據)
dimensionArr[i].levels.statisdate.mdx="[statisdate].[statisdate].[statisdate].["+startdate+"]:[statisdate].[statisdate].[statisdate].["+startdate+"]";
//需要清除之前默認展示本週數據的過濾腳本信息
//exModel.queryModel.axes.ROWS.filters=[];//在ROWS中定义了过滤表达式时，清除此表达式
exModel.queryModel.axes.FILTER.filters=[];//在Filter中定义了过滤表达式时，清除此表达式
}
} this.result.save({},{ contentType: "application/json", data: JSON.stringify(exModel), error: function() {
Saiku.ui.unblock();
var errorMessage = '<span class="i18n">Error executing query. Please check the server logs or contact your administrator!</span>';
self.workspace.table.clearOut();
$(self.workspace.processing).html(errorMessage).show();
self.workspace.adjust();
Saiku.i18n.translate();
} }); }
}
}, /*將string類型的數據轉換為date類型*/
strToDate: function(strDate){
strDate = strDate.toLocaleString();
strDate = strDate.substring(-1,10);
var convert = Date.parse(strDate);
var tmpdate = new Date(convert);
return tmpdate;
}, /*将Date类型转换为String类型 格式为：2019-03-03*/
dateToStr: function(dateStr){
//dateStr = this.strToDate(dateStr);
var tmpYear= dateStr.getFullYear();
var tmpMonth = dateStr.getMonth()+1;
var tmpDay = dateStr.getDate();
if(tmpMonth < 10){ tmpMonth = "0"+tmpMonth;}
if(tmpDay < 10){ tmpDay = "0"+tmpDay;}
var tmpdate = tmpYear+"-"+tmpMonth+"-"+tmpDay;
return tmpdate;
}, enrich: function() {
var self = this;
this.workspace.query.action.post("/../enrich", {
contentType: "application/json",
data: JSON.stringify(self.model),
async: false,
success: function(response, model) {
self.model = model;
}
});
}, url: function() {
return "api/query/" + encodeURI(this.uuid);
}
/**
get_statisdate_members: function() {
var path = "/result/metadata/hierarchies/%5Bstatisdate%5D.%5Bstatisdate%5D/levels/statisdate"; * gett isn't a typo, although someone should probably rename that method to avoid confusion. this.workspace.query.action.gett(path, {
success: this.fetch_statisdate_members,
error: function() {
alert("error");
},
data: {result: true, searchlimit: 30000 }});
}, 得到獲取statisdate字段的結果信息然後處理
fetch_statisdate_members: function(model, response) { if (response && response.length > 0) {
alert("Fetch_statisdate_member++++++++++++"+response);
} }, */
});
================二次更新==因为开始日期结束日期为同一天且两个日期都不存在时，缺少了判断，现在添加进来~ 不然数据就不准确了 20190530==========