import { getSheetIndex, getRangetxt } from '../methods/get';
import {
replaceHtml,
getObjType,
ABCatNum,
numFormat,
numfloatlen,
showrightclickmenu,
mouseclickposition
} from '../utils/util';
import { getdatabyselectionD, getcellvalue, datagridgrowth } from '../global/getdata';
import { isdatetime, diff, isdatatypemulti, isdatatype } from '../global/datecontroll';
import { genarate, update } from '../global/format';
import { isRealNull, isEditMode } from '../global/validate';
import { orderbydata1D } from '../global/sort';
import { jfrefreshgrid, jfrefreshgridall } from '../global/refresh';
import tooltip from '../global/tooltip';
import editor from '../global/editor';
import cleargridelement from '../global/cleargridelement';
import luckysheetArray from '../global/array';
import analysis from '../global/analysis';
import { selectHightlightShow } from './select';
import { luckysheet_searcharray } from './sheetSearch';
import {
modelHTML,
filtermenuHTML,
filtersubmenuHTML,
pivottableconfigHTML,
pivottablesumHTML,
luckysheetPivotTableHTML
} from './constant';
import sheetmanage from './sheetmanage';
import luckysheetsizeauto from './resize';
import server from './server';
import {checkProtectionAuthorityNormal} from './protection';
import Store from '../store';
import locale from '../locale/locale';
import numeral from 'numeral';
import { luckysheetlodingHTML } from '../controllers/constant';
const pivotTable = {
pivotDatas: null,
pivotSheetIndex: 0,
pivotDataSheetIndex: 0,
celldata: null,
origindata: null,
getCellData: function (cursheetindex, datasheetindex, data_select_save) {
let _this = this;
let sheetIndex;
if (cursheetindex != null) {
sheetIndex = cursheetindex;
}
else {
sheetIndex = Store.currentSheetIndex;
}
let realIndex = getSheetIndex(sheetIndex);
if (getObjType(Store.luckysheetfile[realIndex].pivotTable) != "object"){
Store.luckysheetfile[realIndex].pivotTable = new Function("return " + Store.luckysheetfile[realIndex].pivotTable )();
}
if (Store.luckysheetfile[realIndex].pivotTable != null) {
_this.column = Store.luckysheetfile[realIndex].pivotTable.column;
_this.row = Store.luckysheetfile[realIndex].pivotTable.row;
_this.values = Store.luckysheetfile[realIndex].pivotTable.values;
_this.filter = Store.luckysheetfile[realIndex].pivotTable.filter;
_this.showType = Store.luckysheetfile[realIndex].pivotTable.showType;
_this.filterparm = Store.luckysheetfile[realIndex].pivotTable.filterparm;
if (Store.luckysheetfile[realIndex].pivotTable.drawPivotTable != null) {
_this.drawPivotTable = Store.luckysheetfile[realIndex].pivotTable.drawPivotTable;
}
else {
_this.drawPivotTable = true;
}
if (Store.luckysheetfile[realIndex].pivotTable.pivotTableBoundary != null) {
_this.pivotTableBoundary = Store.luckysheetfile[realIndex].pivotTable.pivotTableBoundary;
}
else {
_this.pivotTableBoundary = [12, 6];
}
if (data_select_save != null) {
_this.pivot_select_save = data_select_save;
}
else {
_this.pivot_select_save = Store.luckysheetfile[realIndex].pivotTable.pivot_select_save;
}
if (datasheetindex != null) {
_this.pivotDataSheetIndex = datasheetindex;
}
else {
_this.pivotDataSheetIndex = Store.luckysheetfile[realIndex].pivotTable.pivotDataSheetIndex;
}
}
else {
_this.column = null;
_this.row = null;
_this.values = null;
_this.filter = null;
_this.showType = null;
_this.filterparm = null;
_this.drawPivotTable = true;
_this.pivotTableBoundary = [12, 6];
if (data_select_save != null) {
_this.pivot_select_save = data_select_save;
}
else {
_this.pivot_select_save = Store.luckysheet_select_save;
}
if (datasheetindex != null) {
_this.pivotDataSheetIndex = datasheetindex;
}
else {
_this.pivotDataSheetIndex = sheetIndex;
}
}
let pivotrealIndex = getSheetIndex(_this.pivotDataSheetIndex);
let otherfile = Store.luckysheetfile[pivotrealIndex];
if(otherfile["data"] == null){
otherfile["data"] = sheetmanage.buildGridData(otherfile);
}
_this.origindata = getdatabyselectionD(otherfile.data, _this.pivot_select_save);
let rowhidden = {};
if (_this.filterparm != null) {
for (let f in _this.filterparm) {
// 目的是取出rowhidden
for (let h in _this.filterparm[f]) {
if (h === 'rowhidden' && _this.filterparm[f][h] != null) {
rowhidden = $.extend(true, rowhidden, _this.filterparm[f][h]);
}
}
}
}
_this.rowhidden = rowhidden;
_this.pivotSheetIndex = sheetIndex;
let newdata = [];
for (let i = 0; i < _this.origindata.length; i++) {
if (_this.rowhidden != null && _this.rowhidden[i] != null) {
continue;
}
newdata.push([].concat(_this.origindata[i]));
}
_this.celldata = newdata;
_this.pivot_data_type = {};
for (let c = 0; c < _this.celldata[1].length; c++) {
let type = isdatatype(_this.celldata[1][c]);
_this.pivot_data_type[c.toString()] = type;
}
},
pivot_data_type: {},
pivot_select_save: null,
column: null,
row: null,
values: null,
filter: null,
showType: null,
rowhidden: null,
selected: null,
caljs: null,
initial: true,
filterparm: null,
luckysheet_pivotTable_select_state: false,
jgridCurrentPivotInput: null,
movestate: false,
moveitemposition: [],
movesave: {},
showvaluecolrow: function () {
let _this = this;
if ($("#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item").length >= 2) {
$("#luckysheetpivottablevaluecolrowshow").show();
if (_this.showType == "column") {
$("#luckysheetpivottablevaluecolrow").prop("checked", true);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']").addClass("ui-state-active");
$("#luckysheetpivottablevaluecolrow1").prop("checked", false);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']").removeClass("ui-state-active");
}
else {
$("#luckysheetpivottablevaluecolrow1").prop("checked", true);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']").addClass("ui-state-active");
$("#luckysheetpivottablevaluecolrow").prop("checked", false);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']").removeClass("ui-state-active");
}
}
else {
$("#luckysheetpivottablevaluecolrowshow").hide();
}
},
resetOrderby: function (obj) {
let orderby = $("#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item").index(obj);
$("#luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column").find(".luckysheet-modal-dialog-slider-config-item").each(function () {
if ($(this).data("orderby") == orderby) {
$(this).data("orderby", "self");
}
});
},
luckysheetsliderlistclearfilter: function ($filter) {
let _this = this;
let $t = $filter.parent();
let cindex = $t.data("index");
let rowhidden = {},
selected = {},
d = _this.origindata,
filterdata = {};
$t.data("rowhidden", "").find(".luckysheet-slider-list-item-filtered").hide();
_this.setDatatojsfile("selected", {}, cindex);
_this.setDatatojsfile("rowhidden", null, cindex);
let newdata = [];
for (let i = 0; i < d.length; i++) {
if (rowhidden[i] != null) {
continue;
}
newdata.push([].concat(d[i]));
}
_this.celldata = newdata;
_this.refreshPivotTable();
$("#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu").hide();
},
luckysheetsliderlistitemfilter: function ($filter) {
let _this = this;
const _locale = locale();
const locale_filter = _locale.filter;
let $t = $filter.parent(),
toffset = $t.offset(),
$menu = $("#luckysheet-pivotTableFilter-menu"),
winH = $(window).height(),
winW = $(window).width();
let cindex = $t.data("index");
let rowhidden = $t.data("rowhidden");
if(rowhidden == null || rowhidden == ""){
rowhidden = {};
}
else if(getObjType(rowhidden) == "string"){
rowhidden = JSON.parse(rowhidden);
}
$("body .luckysheet-cols-menu").hide();
$("#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu").hide();
$("#luckysheet-pivotTableFilter-byvalue-input").val("");
$("#luckysheet-pivotTableFilter-bycondition").next().hide();
$("#luckysheet-pivotTableFilter-byvalue").next().show();
$menu.data("index", cindex);
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input").hide().find("input").val();
$("#luckysheet-pivotTableFilter-selected span").data("type", "0").data("type", null).text(locale_filter.filiterInputNone);
let byconditiontype = $t.data("byconditiontype");
$("#luckysheet-pivotTableFilter-selected span").data("value", $t.data("byconditionvalue")).data("type", byconditiontype).text($t.data("byconditiontext"));
if (byconditiontype == "2") {
let $input = $("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input2").show().find("input");
$input.eq(0).val($t.data("byconditionvalue1"));
$input.eq(1).val($t.data("byconditionvalue2"));
}
else if (byconditiontype == "1") {
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input").eq(0).show().find("input").val($t.data("byconditionvalue1"));
}
const loadingObj = luckysheetlodingHTML("#luckysheet-pivotTableFilter-byvalue-select",{text:locale_filter.filiterMoreDataTip});
$("#luckysheet-pivotTableFilter-byvalue-select").empty().append(loadingObj.el)
let rowhiddenother = {}; //其它筛选列的隐藏行
$("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").not($t.get(0)).each(function () {
let $t = $(this), rh = $t.data("rowhidden");
if (rh == null || rh == "") {
return true;
}
if(getObjType(rh) == "string"){
rh = JSON.parse(rh);
}
for (let r in rh) {
rowhiddenother[r] = 0;
}
});
let data = _this.origindata;
setTimeout(function () {
//日期值
let dvmap = {};
let dvmap_uncheck = {};
//除日期以外的值
let vmap = {};
let vmap_uncheck = {};
for (let r = 1; r < data.length; r++) {
if(r in rowhiddenother){
continue;
}
if(data[r] == null){
continue;
}
let cell = data[r][cindex];
if(cell != null && cell.ct != null && cell.ct.t == "d"){ //单元格是日期
let v = update("YYYY-MM-DD", cell.v);
let y = v.split("-")[0];
let m = v.split("-")[1];
let d = v.split("-")[2];
if(!(y in dvmap)){
dvmap[y] = {};
}
if(!(m in dvmap[y])){
dvmap[y][m] = {};
}
if(!(d in dvmap[y][m])){
dvmap[y][m][d] = 0;
}
dvmap[y][m][d]++;
if(r in rowhidden){
dvmap_uncheck[y] = 0;
dvmap_uncheck[m] = 0;
dvmap_uncheck[d] = 0;
}
}
else{
let v, m;
if(cell == null || isRealNull(cell.v)){
v = null;
m = null;
}
else{
v = cell.v;
m = cell.m;
}
if(!(v in vmap)){
vmap[v] = {};
}
if(!(m in vmap[v])){
vmap[v][m] = 0;
}
vmap[v][m]++;
if(r in rowhidden){
vmap_uncheck[v + "#$$$#" + m] = 0;
}
}
}
//遍历数据加到页面
let item = [];
if(JSON.stringify(dvmap).length > 2){
for(let y in dvmap){
let ysum = 0;
let monthHtml = '';
for(let m in dvmap[y]){
let msum = 0;
let dayHtml = '';
for(let d in dvmap[y][m]){
let dayL = dvmap[y][m][d];
msum += dayL;
//月 小于 10
let mT;
if(Number(m) < 10){
mT = "0" + Number(m);
}
else{
mT = m;
}
//日 小于 10
let dT;
if(Number(d) < 10){
dT = "0" + Number(d);
}
else{
dT = d;
}
//日是否选中状态
if((y in dvmap_uncheck) && (m in dvmap_uncheck) && (d in dvmap_uncheck)){
dayHtml += '<div class="day luckysheet-mousedown-cancel cf" data-check="false" title="'+ y +'-'+ mT +'-'+ dT +'">' +
'<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
'<label class="luckysheet-mousedown-cancel">' + d + '</label>' +
'<span class="count luckysheet-mousedown-cancel">( ' + dayL + ' )</span>' +
'</div>';
}
else{
dayHtml += '<div class="day luckysheet-mousedown-cancel cf" data-check="true" title="'+ y +'-'+ mT +'-'+ dT +'">' +
'<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
'<label class="luckysheet-mousedown-cancel">' + d + '</label>' +
'<span class="count luckysheet-mousedown-cancel">( ' + dayL + ' )</span>' +
'</div>';
}
}
ysum += msum;
//月 小于 10
let mT2;
if(Number(m) < 10){
mT2 = "0" + Number(m);
}
else{
mT2 = m;
}
//月是否选中状态
if((y in dvmap_uncheck) && (m in dvmap_uncheck)){
monthHtml += '<div class="monthBox luckysheet-mousedown-cancel">' +
'<div class="month luckysheet-mousedown-cancel cf" data-check="false" title="'+ y +'-'+ mT2 +'">' +
'<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
'<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
'<label class="luckysheet-mousedown-cancel">' + m + ''+locale_filter.filiterMonthText+'</label>' +
'<span class="count luckysheet-mousedown-cancel">( ' + msum + ' )</span>' +
'</div>' +
'<div class="dayList luckysheet-mousedown-cancel">' + dayHtml + '</div>' +
'</div>';
}
else{
monthHtml += '<div class="monthBox luckysheet-mousedown-cancel">' +
'<div class="month luckysheet-mousedown-cancel cf" data-check="true" title="'+ y +'-'+ mT2 +'">' +
'<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
'<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
'<label class="luckysheet-mousedown-cancel">' + m + ''+locale_filter.filiterMonthText+'</label>' +
'<span class="count luckysheet-mousedown-cancel">( ' + msum + ' )</span>' +
'</div>' +
'<div class="dayList luckysheet-mousedown-cancel">' + dayHtml + '</div>' +
'</div>';
}
}
//年是否选中状态
let yearHtml;
if(y in dvmap_uncheck){
yearHtml = '<div class="yearBox luckysheet-mousedown-cancel">' +
'<div class="year luckysheet-mousedown-cancel cf" data-check="false" title="'+ y +'">' +
'<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
'<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
'<label class="luckysheet-mousedown-cancel">' + y + ''+locale_filter.filiterYearText+'</label>' +
'<span class="count luckysheet-mousedown-cancel">( ' + ysum + ' )</span>' +
'</div>' +
'<div class="monthList luckysheet-mousedown-cancel">' + monthHtml + '</div>' +
'</div>';
}
else{
yearHtml = '<div class="yearBox luckysheet-mousedown-cancel">' +
'<div class="year luckysheet-mousedown-cancel cf" data-check="true" title="'+ y +'">' +
'<i class="fa fa-caret-right luckysheet-mousedown-cancel" aria-hidden="true"></i>' +
'<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
'<label class="luckysheet-mousedown-cancel">' + y + ''+locale_filter.filiterYearText+'</label>' +
'<span class="count luckysheet-mousedown-cancel">( ' + ysum + ' )</span>' +
'</div>' +
'<div class="monthList luckysheet-mousedown-cancel">' + monthHtml + '</div>' +
'</div>';
}
item.unshift(yearHtml);
}
}
if(JSON.stringify(vmap).length > 2){
let vmapKeys = Object.keys(vmap);
vmapKeys = orderbydata1D(vmapKeys, true);
for(let i = 0; i < vmapKeys.length; i++){
let v = vmapKeys[i];
for(let x in vmap[v]){
let text;
if((v + "#$$$#" + x) == "null#$$$#null"){
text = locale_filter.valueBlank;
}
else{
text = x;
}
//是否选中状态
let dataHtml;
if((v + "#$$$#" + x) in vmap_uncheck){
dataHtml = '<div class="textBox luckysheet-mousedown-cancel cf" data-check="false" data-filter="'+ (v + "#$$$#" + x) +'" title="'+ x +'">' +
'<input class="luckysheet-mousedown-cancel" type="checkbox"/>' +
'<label class="luckysheet-mousedown-cancel">' + text + '</label>' +
'<span class="luckysheet-mousedown-cancel count">( ' + vmap[v][x] + ' )</span>' +
'</div>';
}
else{
dataHtml = '<div class="textBox luckysheet-mousedown-cancel cf" data-check="true" data-filter="'+ (v + "#$$$#" + x) +'" title="'+ x +'">' +
'<input class="luckysheet-mousedown-cancel" type="checkbox" checked="checked"/>' +
'<label class="luckysheet-mousedown-cancel">' + text + '</label>' +
'<span class="luckysheet-mousedown-cancel count">( ' + vmap[v][x] + ' )</span>' +
'</div>';
}
item.push(dataHtml);
}
}
}
// 适配小屏设备
let containerH = winH - toffset.top - 350
if (containerH < 0) containerH = 100
//$("#luckysheet-pivotTableFilter-byvalue-select").html("<div class='ListBox luckysheet-mousedown-cancel' style='max-height:" + containerH + "px;overflow-y:auto;overflow-x:hidden;'>" + item.join("") + "</div>");
$("#luckysheet-pivotTableFilter-byvalue-select").append("<div class='ListBox luckysheet-mousedown-cancel' style='max-height:" + containerH + "px;overflow-y:auto;overflow-x:hidden;'>" + item.join("") + "</div>");
loadingObj.close()
}, 1);
showrightclickmenu($menu, toffset.left - 250, toffset.top);
},
getSumTypeName: function (type) {
let name = "";
const _locale = locale();
const locale_pivotTable = _locale.pivotTable;
if (type == "SUM") {
name = locale_pivotTable.valueStatisticsSUM;
}
else if (type == "COUNT") {
name = locale_pivotTable.valueStatisticsCOUNT;
}
else if (type == "COUNTA") {
name = locale_pivotTable.valueStatisticsCOUNTA;
}
else if (type == "COUNTUNIQUE") {
name = locale_pivotTable.valueStatisticsCOUNTUNIQUE;
}
else if (type == "AVERAGE") {
name = locale_pivotTable.valueStatisticsAVERAGE;
}
else if (type == "MAX") {
name = locale_pivotTable.valueStatisticsMAX;
}
else if (type == "MIN") {
name = locale_pivotTable.valueStatisticsMIN;
}
else if (type == "MEDIAN") {
name = locale_pivotTable.valueStatisticsMEDIAN;
}
else if (type == "PRODUCT") {
name = locale_pivotTable.valueStatisticsPRODUCT;
}
else if (type == "STDEV") {
name = locale_pivotTable.valueStatisticsSTDEV;
}
else if (type == "STDEVP") {
name = locale_pivotTable.valueStatisticsSTDEVP;
}
else if (type == "let") {
name = locale_pivotTable.valueStatisticslet;
}
else if (type == "VARP") {
name = locale_pivotTable.valueStatisticsVARP;
}
return name;
},
setDatatojsfile: function (attr, value, cindex) {
let _this = this;
let index = getSheetIndex(_this.pivotSheetIndex);
if (Store.luckysheetfile[index]["pivotTable"] == null) {
Store.luckysheetfile[index]["pivotTable"] = {};
}
if (cindex == null) {
Store.luckysheetfile[index]["pivotTable"][attr] = value;
_this[attr] = value;
}
else {
if (Store.luckysheetfile[index]["pivotTable"]["filterparm"] == null) {
Store.luckysheetfile[index]["pivotTable"]["filterparm"] = {};
}
if (Store.luckysheetfile[index]["pivotTable"]["filterparm"][cindex.toString()] == null) {
Store.luckysheetfile[index]["pivotTable"]["filterparm"][cindex.toString()] = {};
}
Store.luckysheetfile[index]["pivotTable"]["filterparm"][cindex.toString()][attr] = value;
if (_this["filterparm"] == null) {
_this["filterparm"] = {};
}
if (_this["filterparm"][cindex.toString()] == null) {
_this["filterparm"][cindex.toString()] = {};
}
_this["filterparm"][cindex.toString()][attr] = value;
}
},
createPivotTable: function (e) {
if(isEditMode() || Store.allowEdit===false){
return;
}
let _this = this;
let datasheetindex = Store.currentSheetIndex;
const _locale = locale();
const locale_pivotTable = _locale.pivotTable;
if(isEditMode()){
alert(locale_pivotTable.errorNotAllowEdit);
return;
}
if(Store.luckysheet_select_save.length > 1){
tooltip.info("", locale_pivotTable.errorNotAllowMulti);
return
}
if (Store.luckysheet_select_save.length == 0 || Store.luckysheet_select_save[0].row[0] == Store.luckysheet_select_save[0].row[1] || Store.luckysheet_select_save[0].column[0] == Store.luckysheet_select_save[0].column[1]) {
tooltip.info("", locale_pivotTable.errorSelectRange);
return;
}
let select_save = $.extend(true, {}, Store.luckysheet_select_save[0]);
sheetmanage.addNewSheet(e, true);
_this.getCellData(Store.currentSheetIndex, datasheetindex, select_save);
_this.setDatatojsfile("pivot_select_save", select_save);
_this.setDatatojsfile("pivotDataSheetIndex", datasheetindex);
_this.initialPivotManage();
},
changePivotTable: function (index) {
let _this = this;
const _locale = locale();
const locale_pivotTable = _locale.pivotTable;
let pivotDataSheetIndex = Store.luckysheetfile[getSheetIndex(index)].pivotTable.pivotDataSheetIndex;
let real_pivotDataSheetIndex = getSheetIndex(pivotDataSheetIndex);
if(real_pivotDataSheetIndex == null){
tooltip.info(locale_pivotTable.errorIsDamage, "");
return;
}
_this.getCellData(index);
_this.initialPivotManage(true);
_this.refreshPivotTable(); //初始化在一个普通sheet页，从此普通sheet页切换到数据透视表页时，需要刷新下数据，否则还是旧数据
},
refreshPivotTable: function (isRefreshCanvas=true) {
let _this = this;
let redo = {};
redo["pivotTable"] = pivotTable;
redo["data"] = editor.deepCopyFlowData(Store.flowdata);//取数据
_this.storePivotTableParam();
let ret = _this.dataHandler(_this.column, _this.row, _this.values, _this.showType, _this.celldata);
_this.setDatatojsfile("pivotDatas", ret);
let d = $.extend(true, [], sheetmanage.nulldata);
let data = d;
let addr = 0, addc = 0;
if (ret.length == 0) {
_this.setDatatojsfile("drawPivotTable", true);
_this.setDatatojsfile("pivotTableBoundary", [12, 6]);
}
else {
_this.setDatatojsfile("drawPivotTable", false);
_this.setDatatojsfile("pivotTableBoundary", [ret.length, ret[0].length]);
let rlen = ret.length,
clen = ret[0].length;
addr = rlen - d.length;
addc = clen - d[0].length;
data = datagridgrowth(d, addr + 20, addc + 10, true);
for (let r = 0; r < rlen; r++) {
let x = [].concat(data[r]);
for (let c = 0; c < clen; c++) {
let value = "";
if (ret[r] != null && ret[r][c] != null) {
value = getcellvalue(r, c, ret);
}
x[c] = value;
}
data[r] = x;
}
}
redo["type"] = "pivotTable_change";
redo["curdata"] = $.extend(true, [], data);
redo["sheetIndex"] = Store.currentSheetIndex;
redo["pivotTablecur"] = _this.getPivotTableData();
if(Store.clearjfundo){
Store.jfundo.length = 0;
Store.jfredo.push(redo);
}
cleargridelement();
Store.clearjfundo = false;
if (addr > 0 || addc > 0) {
jfrefreshgridall(data[0].length, data.length, data, null, Store.luckysheet_select_save, "datachangeAll", undefined, undefined,isRefreshCanvas);
}
else {
jfrefreshgrid(data, Store.luckysheet_select_save, {}, null, isRefreshCanvas);
selectHightlightShow();
}
Store.clearjfundo = true;
},
drawPivotTable: true,
pivotTableBoundary: [12, 6],
pivotclick: function (row_index, col_index, index) {
if(index == null){
index = Store.currentSheetIndex;
}
let file = Store.luckysheetfile[getSheetIndex(index)];
if(!file.isPivotTable){
return;
}
let pivotDataSheetIndex = file.pivotTable.pivotDataSheetIndex;
let real_pivotDataSheetIndex = getSheetIndex(pivotDataSheetIndex);
if(real_pivotDataSheetIndex == null){
return;
}
let slider = $("#luckysheet-modal-dialog-slider-pivot");
let isRangeClick = this.isPivotRange(row_index, col_index);
if (isRangeClick && slider.is(":hidden")) {
if(!checkProtectionAuthorityNormal(index, "usePivotTablereports",false)){
// Store.luckysheet_select_status = false;
return;
}
slider.show();
luckysheetsizeauto();
$("#luckysheet-sta-content").css("padding-right", 260);
}
else if(!isRangeClick && slider.is(":visible")) {
slider.hide();
luckysheetsizeauto();
$("#luckysheet-sta-content").css("padding-right", 10);
}
},
isPivotRange: function (row_index, col_index) {
let _this = this;
if (!!Store.luckysheetcurrentisPivotTable) {
if (row_index < _this.pivotTableBoundary[0] && col_index < _this.pivotTableBoundary[1]) {
return true;
}
else {
return false;
}
}
},
storePivotTableParam: function () {
let _this = this;
let columnarr = [], rowarr = [], filterarr = [], valuesarr = [];
$("#luckysheet-modal-dialog-config-filter .luckysheet-modal-dialog-slider-config-item").each(function () {
let item = {};
item["index"] = $(this).data("index");
item["name"] = $(this).data("name");
item["fullname"] = $(this).find(".luckysheet-modal-dialog-slider-config-item-txt").text();
filterarr.push(item);
});
$("#luckysheet-modal-dialog-config-row .luckysheet-modal-dialog-slider-config-item").each(function () {
let item = {};
item["index"] = $(this).data("index");
item["name"] = $(this).data("name");
item["fullname"] = $(this).find(".luckysheet-modal-dialog-slider-config-item-txt").text();
item["order"] = $(this).data("order");
item["orderby"] = $(this).data("orderby");
item["stastic"] = $(this).data("stastic");
rowarr.push(item);
});
$("#luckysheet-modal-dialog-config-column .luckysheet-modal-dialog-slider-config-item").each(function () {
let item = {};
item["index"] = $(this).data("index");
item["name"] = $(this).data("name");
item["fullname"] = $(this).find(".luckysheet-modal-dialog-slider-config-item-txt").text();
item["order"] = $(this).data("order");
item["orderby"] = $(this).data("orderby");
item["stastic"] = $(this).data("stastic");
columnarr.push(item);
});
$("#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item").each(function () {
let item = {};
item["index"] = $(this).data("index");
item["name"] = $(this).data("name");
item["fullname"] = $(this).find(".luckysheet-modal-dialog-slider-config-item-txt").text();
item["sumtype"] = $(this).data("sumtype");
item["nameindex"] = $(this).data("nameindex");
valuesarr.push(item);
});
_this.setDatatojsfile("column", columnarr);
_this.setDatatojsfile("row", rowarr);
_this.setDatatojsfile("filter", filterarr);
_this.setDatatojsfile("values", valuesarr);
let showtype = $("#luckysheetpivottablevaluecolrow:checked, #luckysheetpivottablevaluecolrow1:checked").val();
_this.setDatatojsfile("showType", showtype == "0" ? "row" : "column");
let pivotTable = _this.getPivotTableData();
delete pivotTable.pivotDatas;
server.saveParam("all", _this.pivotSheetIndex, pivotTable, { "k": "pivotTable" });
},
getPivotTableData: function(dataindex){
if(dataindex == null){
dataindex = this.pivotSheetIndex;
}
let index = getSheetIndex(dataindex);
let pivotTable = Store.luckysheetfile[index]["pivotTable"];
if(getObjType(pivotTable) == "object"){
pivotTable = $.extend(true, {}, Store.luckysheetfile[index]["pivotTable"]);
}
else{
pivotTable = new Function("return " + pivotTable )();
}
return pivotTable
},
addValuesToTitle: function (titles, values) {
let rowLen = titles.length * values.length,
colLen = titles[0].length + 1;
let retdata = [];
if (titles.length == 0 && values.length > 0) {
for (let v = 0; v < values.length; v++) {
retdata.push(values[v].fullname);
}
return retdata;
}
if (values.length == 0 && titles.length > 0) {
return titles;
}
for (let r = 0; r < rowLen; r++) {
retdata[r] = new Array(colLen);
for (let c = 0; c < colLen - 1; c++) {
retdata[r][c] = titles[Math.floor(r / values.length)][c];
}
retdata[r][colLen - 1] = values[r % values.length].fullname;
}
return retdata;
},
initialPivotManage: function (restore) {
let _this = this;
const _locale = locale();
const locale_pivotTable = _locale.pivotTable;
const locale_button = _locale.button;
const locale_filter = _locale.filter;
if (_this.initial) {
_this.initial = false;
$("body").first().append(luckysheetPivotTableHTML());
$("#luckysheet-modal-dialog-slider-close").click(function () {
$("#luckysheet-modal-dialog-slider-pivot").hide();
luckysheetsizeauto();
});
$("body").first().append(replaceHtml(modelHTML, { "id": "luckysheet-data-pivotTable-selection", "addclass": "luckysheet-data-pivotTable-selection", "title": locale_pivotTable.titleSelectionDataRange, "content": '<input id="luckysheet-pivotTable-range-selection-input" class="luckysheet-datavisual-range-container" style="font-size: 14px;padding:5px;max-width:none;" spellcheck="false" aria-label="'+locale_pivotTable.titleDataRange+'" placeholder="'+locale_pivotTable.titleDataRange+'">', "botton": '<button id="luckysheet-pivotTable-selection-confirm" class="btn btn-primary">'+locale_button.confirm+'</button><button class="btn btn-default luckysheet-model-close-btn">'+locale_button.cancel+'</button>' }));
$("body").first().append(replaceHtml(filtermenuHTML(), { "menuid": "pivotTableFilter" }));
$("body").first().append(replaceHtml(filtersubmenuHTML(), { "menuid": "pivotTableFilter" }));
$("body").first().append(pivottableconfigHTML());
$("body").first().append(pivottablesumHTML());
$("#luckysheet-pivotTableFilter-orderby-asc").remove();
$("#luckysheet-pivotTableFilter-orderby-desc").next().remove();
$("#luckysheet-pivotTableFilter-orderby-desc").remove();
$("#luckysheet-pivotTableFilter-orderby-color").next().remove();
$("#luckysheet-pivotTableFilter-orderby-color").remove();
$("#luckysheetpivottablevaluecolrow, #luckysheetpivottablevaluecolrow1").checkboxradio({
icon: false
}).change(function () {
_this.refreshPivotTable();
});
let hidefilersubmenu = null;
$("#luckysheet-pivotTableFilter-menu").mouseover(function () {
clearTimeout(hidefilersubmenu);
hidefilersubmenu = setTimeout(function () {
$("#luckysheet-pivotTableFilter-submenu").hide();
}, 500);
});
//点击复选框
$(document).off("click.ptFilterCheckbox1").on("click.ptFilterCheckbox1", "#luckysheet-pivotTableFilter-byvalue-select .textBox",function(){
if($(this).attr("data-check") == "true"){
$(this).attr("data-check", "false");
$(this).find("input[type='checkbox']").removeAttr("checked");
}
else{
$(this).attr("data-check", "true");
$(this).find("input[type='checkbox']").prop("checked", true);
}
})
$(document).off("click.ptFilterCheckbox2").on("click.ptFilterCheckbox2", "#luckysheet-pivotTableFilter-byvalue-select .year",function(){
if($(this).attr("data-check") == "true"){
$(this).attr("data-check", "false");
$(this).parents(".yearBox").find(".month").attr("data-check", "false");
$(this).parents(".yearBox").find(".day").attr("data-check", "false");
$(this).parents(".yearBox").find("input[type='checkbox']").removeAttr("checked");
}
else{
$(this).attr("data-check", "true");
$(this).parents(".yearBox").find(".month").attr("data-check", "true");
$(this).parents(".yearBox").find(".day").attr("data-check", "true");
$(this).parents(".yearBox").find("input[type='checkbox']").prop("checked", true);
}
})
$(document).off("click.ptFilterCheckbox3").on("click.ptFilterCheckbox3", "#luckysheet-pivotTableFilter-byvalue-select .month",function(){
//月份 对应的 天
if($(this).attr("data-check") == "true"){
$(this).attr("data-check", "false");
$(this).parents(".monthBox").find(".day").attr("data-check", "false");
$(this).parents(".monthBox").find("input[type='checkbox']").removeAttr("checked");
}
else{
$(this).attr("data-check", "true");
$(this).parents(".monthBox").find(".day").attr("data-check", "true");
$(this).parents(".monthBox").find("input[type='checkbox']").prop("checked", true);
}
//月份 对应的 年份
let yearDayAllCheck = true;
let $yearDay = $(this).parents(".yearBox").find(".day");
$yearDay.each(function(i,e){
if($(e).attr("data-check") == "true"){
}
else{
yearDayAllCheck = false;
}
});
if(yearDayAllCheck){
$(this).parents(".yearBox").find(".year").attr("data-check", "true");
$(this).parents(".yearBox").find(".year input[type='checkbox']").prop("checked", true);
}
else{
$(this).parents(".yearBox").find(".year").attr("data-check", "false");
$(this).parents(".yearBox").find(".year input[type='checkbox']").removeAttr("checked");
}
})
$(document).off("click.ptFilterCheckbox4").on("click.ptFilterCheckbox4", "#luckysheet-pivotTableFilter-byvalue-select .day",function(){
if($(this).attr("data-check") == "true"){
$(this).attr("data-check", "false");
$(this).find("input[type='checkbox']").removeAttr("checked");
}
else{
$(this).attr("data-check", "true");
$(this).find("input[type='checkbox']").prop("checked", true);
}
//天 对应的 月份
let monthDayAllCheck = true;
let $monthDay = $(this).parents(".monthBox").find(".day");
$monthDay.each(function(i,e){
if($(e).attr("data-check") == "true"){
}
else{
monthDayAllCheck = false;
}
});
if(monthDayAllCheck){
$(this).parents(".monthBox").find(".month").attr("data-check", "true");
$(this).parents(".monthBox").find(".month input[type='checkbox']").prop("checked", true);
}
else{
$(this).parents(".monthBox").find(".month").attr("data-check", "false");
$(this).parents(".monthBox").find(".month input[type='checkbox']").removeAttr("checked");
}
//天 对应的 年份
let yearDayAllCheck = true;
let $yearDay = $(this).parents(".yearBox").find(".day");
$yearDay.each(function(i,e){
if($(e).attr("data-check") == "true"){
}
else{
yearDayAllCheck = false;
}
});
if(yearDayAllCheck){
$(this).parents(".yearBox").find(".year").attr("data-check", "true");
$(this).parents(".yearBox").find(".year input[type='checkbox']").prop("checked", true);
}
else{
$(this).parents(".yearBox").find(".year").attr("data-check", "false");
$(this).parents(".yearBox").find(".year input[type='checkbox']").removeAttr("checked");
}
})
//日期 三级下拉显示
$(document).off("click.ptFilterYearDropdown").on("click.ptFilterYearDropdown", "#luckysheet-pivotTableFilter-byvalue-select .yearBox .fa-caret-right",function(){
let $p = $(this).parents(".luckysheet-mousedown-cancel");
if($p.hasClass("year")){
$(this).parents(".yearBox").find(".monthList").slideToggle();
}
if($p.hasClass("month")){
$(this).parents(".monthBox").find(".dayList").slideToggle();
}
});
//全选
$("#luckysheet-pivotTableFilter-byvalue-btn-all").click(function () {
$("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']").prop("checked", true);
$("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']").parents(".luckysheet-mousedown-cancel").attr("data-check", "true");
});
//反选
$("#luckysheet-pivotTableFilter-byvalue-btn-contra").click(function () {
let $input = $("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']");
$input.each(function(i, e){
if($(e).is(":checked")){
$(e).removeAttr("checked");
$(e).parents(".luckysheet-mousedown-cancel").attr("data-check", "false");
}
else{
$(e).prop("checked", true);
$(e).parents(".luckysheet-mousedown-cancel").attr("data-check", "true");
}
});
//天 对应的 月份
let $month = $("#luckysheet-pivotTableFilter-byvalue-select .ListBox .monthBox");
$month.each(function(index, event){
let monthDayAllCheck = true;
let $monthDay = $(event).find(".day input[type='checkbox']");
$monthDay.each(function(i,e){
if($(e).is(":checked")){
}
else{
monthDayAllCheck = false;
}
});
if(monthDayAllCheck){
$(event).find(".month input[type='checkbox']").prop("checked", true);
$(event).attr("data-check", "true");
}
else{
$(event).find(".month input[type='checkbox']").removeAttr("checked");
$(event).attr("data-check", "false");
}
});
//天 对应的 年份
let $year = $("#luckysheet-pivotTableFilter-byvalue-select .ListBox .yearBox");
$year.each(function(index, event){
let yearDayAllCheck = true;
let $yearDay = $(event).find(".day input[type='checkbox']");
$yearDay.each(function(i,e){
if($(e).is(":checked")){
}
else{
yearDayAllCheck = false;
}
});
if(yearDayAllCheck){
$(event).find(".year input[type='checkbox']").prop("checked", true);
$(event).attr("data-check", "true");
}
else{
$(event).find(".year input[type='checkbox']").removeAttr("checked");
$(event).attr("data-check", "false");
}
});
});
//清除
$("#luckysheet-pivotTableFilter-byvalue-btn-clear").click(function () {
$("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']").removeAttr("checked");
$("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']").parents(".luckysheet-mousedown-cancel").attr("data-check", "false");
});
//按照值进行筛选
$("#luckysheet-pivotTableFilter-byvalue-input").on('input propertychange', function () {
let v = $(this).val().toString();
$("#luckysheet-pivotTableFilter-byvalue-select .ListBox .luckysheet-mousedown-cancel").show();
if(v != ""){
let $check = $("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']");
$check.each(function(i, e){
let $p = $(e).parents(".luckysheet-mousedown-cancel");
if($p.hasClass("day")){ //日期
let day = $(e).siblings("label").text().toString();
let month = $(e).parents(".monthBox").find(".month label").text().toString();
let year = $(e).parents(".yearBox").find(".year label").text().toString();
let itemV = year + "-" + month + "-" + day;
if(itemV.indexOf(v) == -1){
$(e).parents(".day").hide();
//天 对应的 月份
let $monthDay = $(e).parents(".dayList").find(".day:visible");
if($monthDay.length == 0){
$(e).parents(".monthBox").find(".month").hide();
}
//天 对应的 年份
let $yearDay = $(e).parents(".monthList").find(".day:visible");
if($yearDay.length == 0){
$(e).parents(".yearBox").find(".year").hide();
}
}
}
if($p.hasClass("textBox")){ //其它
let itemV = $(e).siblings("label").text().toString();
if(itemV.indexOf(v) == -1){
$(e).parents(".textBox").hide();
}
}
});
}
});
$("#luckysheet-pivotTableFilter-bycondition, #luckysheet-pivotTableFilter-byvalue").click(function () {
let $t = $(this);
$t.next().slideToggle(200);
setTimeout(function () {
if ($t.attr("id") == "luckysheet-pivotTableFilter-bycondition" && $("#luckysheet-pivotTableFilter-bycondition").next().is(":visible")) {
if ($("#luckysheet-pivotTableFilter-selected span").text() != locale_filter.filiterInputNone) {
$("#luckysheet-pivotTableFilter-byvalue").next().slideUp(200);
}
}
if ($t.is($("#luckysheet-pivotTableFilter-bycondition"))) {
if ($("#luckysheet-pivotTableFilter-bycondition").next().is(":hidden") && $("#luckysheet-pivotTableFilter-byvalue").next().is(":hidden")) {
$("#luckysheet-pivotTableFilter-byvalue").next().slideDown(200);
}
}
}, 300);
});
//取消按钮
$("#luckysheet-pivotTableFilter-cancel").click(function () {
$("#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu").hide();
});
$("#luckysheet-pivotTableFilter-selected").click(function () {
let $t = $(this), toffset = $t.offset(), $menu = $("#luckysheet-pivotTableFilter-submenu");
$menu.hide();
let winH = $(window).height(), winW = $(window).width();
let menuW = $menu.width(), menuH = $menu.height();
let top = toffset.top, left = toffset.left, mheight = winH - toffset.top - 20;
if (toffset.left + menuW > winW) {
left = toffset.left - menuW;
}
if (toffset.top > winH / 2) {
top = winH - toffset.top;
if (top < 0) {
top = 0;
}
mheight = toffset.top - 20;
}
$menu.css({ "top": top, "left": left, "height": mheight }).show();
clearTimeout(hidefilersubmenu);
});
//按条件过滤
$("#luckysheet-pivotTableFilter-submenu").mouseover(function () {
clearTimeout(hidefilersubmenu);
}).find(".luckysheet-cols-menuitem").click(function (e) {
$("#luckysheet-pivotTableFilter-selected span").html($(this).find(".luckysheet-cols-menuitem-content").text()).data("value", $(this).data("value"));
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input").hide();
if ($(this).data("type") == "2") {
$("#luckysheet-pivotTableFilter-selected span").data("type", "2");
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input2").show();
}
else if ($(this).data("type") == "0") {
$("#luckysheet-pivotTableFilter-selected span").data("type", "0");
}
else {
$("#luckysheet-pivotTableFilter-selected span").data("type", "1");
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input").eq(0).show();
//若是日期 改变input type类型为date
if($(this).attr("data-value") == "dateequal" || $(this).attr("data-value") == "datelessthan" || $(this).attr("data-value") == "datemorethan"){
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input input").prop("type", "date");
}
else{
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input input").prop("type", "text");
}
}
$("#luckysheet-pivotTableFilter-byvalue").next().slideUp();
$("#luckysheet-pivotTableFilter-submenu").hide();
});
$("#luckysheet-modal-dialog-pivotTable-list").on("click", " .luckysheet-slider-list-item-filter", function (e) {
_this.luckysheetsliderlistitemfilter($(this));
e.stopPropagation();
return false;
});
$("#luckysheet-modal-dialog-pivotTable-list").on("click", " .luckysheet-slider-list-item-filtered", function (e) {
_this.luckysheetsliderlistclearfilter($(this).next());
e.stopPropagation();
return false;
});
$("#luckysheet-dialog-pivotTable-range-seleted").click(function () {
$("#luckysheet-modal-dialog-slider-pivot").hide();
luckysheetsizeauto();
let $t = $("#luckysheet-data-pivotTable-selection"),
myh = $t.outerHeight(),
myw = $t.outerWidth();
let winw = $(window).width(), winh = $(window).height();
let scrollLeft = $(document).scrollLeft(), scrollTop = $(document).scrollTop();
$("#luckysheet-data-pivotTable-selection").css({ "left": (winw + scrollLeft - myw) / 2, "top": (winh + scrollTop - myh) / 4 }).show();
_this.jgridCurrentPivotInput = $("#luckysheet-dialog-pivotTable-range").html();
$("#luckysheet-pivotTable-range-selection-input").val(_this.jgridCurrentPivotInput);
_this.luckysheet_pivotTable_select_state = true;
});
//清除筛选按钮
$("#luckysheet-pivotTableFilter-initial").click(function () {
$("#luckysheet-modal-dialog-pivotTable-list .luckysheet-slider-list-item-filtered").hide();
$("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").data("rowhidden", "");
$("#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu").hide();
$("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input").hide().find("input").val();
$("#luckysheet-pivotTableFilter-selected span").data("type", "0").data("type", null).text(locale_filter.filiterInputNone);
_this.setDatatojsfile("filterparm", null);
_this.celldata = _this.origindata;
_this.refreshPivotTable();
});
$("#luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column").on("click", ".luckysheet-modal-dialog-slider-config-item-icon", function (e) {
let $t = $(e.target),
$item = $t.closest(".luckysheet-modal-dialog-slider-config-item"),
cindex = $item.data("index"),
toffset = $item.offset();
let order = $item.data("order"),
orderby = $item.data("orderby"),
stastic = $item.data("stastic");
if (order == null) {
order = "default";
}
let option = '<option value="self">' + $item.find(".luckysheet-modal-dialog-slider-config-item-txt").data("name") + '</option>';
$("#luckysheet-modal-dialog-config-value .luckysheet-modal-dialog-slider-config-item").each(function (i) {
option += '<option value="' + i + '">' + $(this).find(".luckysheet-modal-dialog-slider-config-item-txt").text() + '</option>';
});
$("#luckysheet-pivotTable-config-option-orderby").empty().html(option);
if (orderby == null) {
orderby = "self";
}
if (stastic == null) {
stastic = "1";
}
$("#luckysheet-pivotTable-config-option-order").val(order).data("index", cindex);
$("#luckysheet-pivotTable-config-option-orderby").val(orderby).data("index", cindex);
$("#luckysheet-pivotTable-config-option-stastic").val(stastic).data("index", cindex);
mouseclickposition($("#luckysheet-pivotTable-config-option"), toffset.left + $item.outerWidth(), toffset.top - 13, "rightbottom");
e.stopPropagation();
return false;
});
$("#luckysheet-pivotTable-config-option-order,#luckysheet-pivotTable-config-option-orderby,#luckysheet-pivotTable-config-option-stastic").change(function () {
let $t = $(this), cindex = $t.data("index");
$("#luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column").find(".luckysheet-modal-dialog-slider-config-item").each(function () {
if ($(this).data("index") == cindex) {
$(this).data($t.attr("id").replace("luckysheet-pivotTable-config-option-", ""), $t.val());
}
});
_this.refreshPivotTable();
});
$("#luckysheet-modal-dialog-config-value").on("click", ".luckysheet-modal-dialog-slider-config-item-icon", function (e) {
let $t = $(e.target),
$item = $t.closest(".luckysheet-modal-dialog-slider-config-item"),
cindex = $item.data("index"),
toffset = $item.offset(),
sumtype = $item.data("sumtype");
let type = _this.pivot_data_type[cindex.toString()];
if (sumtype == null) {
if (type == "num") {
sumtype = "SUM";
}
else {
sumtype = "COUNTA";
}
}
let $menu = $("#luckysheet-pivotTable-config-option-sumtype");
$menu.find(".luckysheet-submenu-arrow").hide();
$menu.find(".luckysheet-cols-menuitem[sumtype='" + sumtype + "'] .luckysheet-submenu-arrow").css("display", "inline");
$menu.data("item", $item);
mouseclickposition($menu, toffset.left + $item.outerWidth(), toffset.top - 13, "rightbottom");
e.stopPropagation();
return false;
});
$("#luckysheet-pivotTable-config-option-sumtype .luckysheet-cols-menuitem").click(function () {
let $item = $("#luckysheet-pivotTable-config-option-sumtype").data("item");
let sumtype = $(this).attr("sumtype");
$item.data("sumtype", $(this).attr("sumtype"));
let name = _this.getSumTypeName(sumtype) + ":" + $item.data("name");
$item.attr("title", name).find(".luckysheet-modal-dialog-slider-config-item-txt").html(name);
$("#luckysheet-pivotTable-config-option-sumtype").hide();
_this.refreshPivotTable();
});
$("#luckysheet-modal-dialog-config-filter").on("click", ".luckysheet-modal-dialog-slider-config-item-icon", function (e) {
let $t = $(e.target),
cindex = $t.closest(".luckysheet-modal-dialog-slider-config-item").data("index");
_this.luckysheetsliderlistitemfilter($("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").eq(cindex).find(".luckysheet-slider-list-item-filter"));
e.stopPropagation();
return false;
});
//确认按钮
$("#luckysheet-pivotTableFilter-confirm").click(function () {
let $menu = $("#luckysheet-pivotTableFilter-menu");
let cindex = $menu.data("index");
let rowhiddenother = {}; //其它筛选列的隐藏行
$("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").each(function () {
let $t = $(this), rh = $t.data("rowhidden");
if($t.data("index") != cindex){
if (rh == null || rh == "") {
return true;
}
if(getObjType(rh) == "string"){
rh = JSON.parse(rh);
}
for (let r in rh) {
rowhiddenother[r] = 0;
}
}
});
let d = _this.origindata;
let filterdata = {};
let rowhidden = {};
let caljs = {};
if ($("#luckysheet-pivotTableFilter-bycondition").next().is(":visible") && $("#luckysheet-pivotTableFilter-byvalue").next().is(":hidden") && $("#luckysheet-pivotTableFilter-selected span").data("value") != "null") {
let $t = $("#luckysheet-pivotTableFilter-selected span");
let type = $t.data("type"), value = $t.data("value");
caljs["value"] = value;
caljs["text"] = $t.text();
if (type == "0") {
caljs["type"] = "0";
}
else if (type == "2") {
let $input = $("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input2 input");
caljs["type"] = "2";
caljs["value1"] = $input.eq(0).val();
caljs["value2"] = $input.eq(1).val();
}
else {
caljs["type"] = "1";
caljs["value1"] = $("#luckysheet-pivotTableFilter-menu .luckysheet-pivotTableFilter-selected-input").eq(0).find("input").val();
}
for (let r = 1; r < d.length; r++) {
if(r in rowhiddenother){
continue;
}
if(d[r] == null){
continue;
}
let cell = d[r][cindex];
if (value == "cellnull") { //单元格为空
if(cell != null && !isRealNull(cell.v)){
rowhidden[r] = 0;
}
}
else if (value == "cellnonull") { //单元格有数据
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
}
else if (value == "textinclude") { //文本包含
let value1 = caljs["value1"];
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else{
if(cell.m.indexOf(value1) == -1){
rowhidden[r] = 0;
}
}
}
else if (value == "textnotinclude") { //文本不包含
let value1 = caljs["value1"];
if(cell == null || isRealNull(cell.v)){
}
else{
if(cell.m.indexOf(value1) > -1){
rowhidden[r] = 0;
}
}
}
else if (value == "textstart") { //文本开头为
let value1 = caljs["value1"], valuelen = value1.length;
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else{
if(cell.m.substr(0, valuelen) != value1){
rowhidden[r] = 0;
}
}
}
else if (value == "textend") { //文本结尾为
let value1 = caljs["value1"], valuelen = value1.length;
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else{
if(valuelen > cell.m.length || cell.m.substr(cell.m.length - valuelen, valuelen) != value1){
rowhidden[r] = 0;
}
}
}
else if (value == "textequal") { //文本等于
let value1 = caljs["value1"];
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else{
if(cell.m != value1){
rowhidden[r] = 0;
}
}
}
else if (value == "dateequal") { //日期等于
let value1 = genarate(caljs["value1"])[2];
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "d"){
if(parseInt(cell.v) != value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "datelessthan") { //日期早于
let value1 = genarate(caljs["value1"])[2];
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "d"){
if(parseInt(cell.v) >= value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "datemorethan") { //日期晚于
let value1 = genarate(caljs["value1"])[2];
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "d"){
if(parseInt(cell.v) <= value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "morethan") { //大于
let value1 = parseFloat(caljs["value1"]);
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v <= value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "moreequalthan") { //大于等于
let value1 = parseFloat(caljs["value1"]);
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v < value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "lessthan") { //小于
let value1 = parseFloat(caljs["value1"]);
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v >= value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "lessequalthan") { //小于等于
let value1 = parseFloat(caljs["value1"]);
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v > value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "equal") { //等于
let value1 = parseFloat(caljs["value1"]);
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v != value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "noequal") { //不等于
let value1 = parseFloat(caljs["value1"]);
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v == value1){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "include") { //介于
let value1 = parseFloat(caljs["value1"]), value2 = parseFloat(caljs["value2"]);
let min, max;
if(value1 < value2){
min = value1;
max = value2;
}
else{
max = value1;
min = value2;
}
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v < min || cell.v > max){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
else if (value == "noinclude") { //不在其中
let value1 = parseFloat(caljs["value1"]), value2 = parseFloat(caljs["value2"]);
let min, max;
if(value1 < value2){
min = value1;
max = value2;
}
else{
max = value1;
min = value2;
}
if(cell == null || isRealNull(cell.v)){
rowhidden[r] = 0;
}
else if(cell.ct != null && cell.ct.t == "n"){
if(cell.v >= min && cell.v <= max){
rowhidden[r] = 0;
}
}
else{
rowhidden[r] = 0;
}
}
}
}
else {
$("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']").each(function(i, e){
if($(e).is(":visible") && $(e).is(":checked")){
return true;
}
if($(e).closest(".day").length > 0){
let day = $(e).siblings("label").text();
if(Number(day) < 10){
day = "0" + day;
}
let month = $(e).closest(".monthBox").find(".month label").text().replace(locale_filter.filiterMonthText, "");
if(Number(month) < 10){
month = "0" + month;
}
let year = $(e).closest(".yearBox").find(".year label").text().replace(locale_filter.filiterYearText, "");
let itemV = locale_filter.filterDateFormatTip+"#$$$#" + year + "-" + month + "-" + day;
filterdata[itemV] = "1";
}
if($(e).closest(".textBox").length > 0){
let itemV = $(e).closest(".textBox").data("filter");
filterdata[itemV] = "1";
}
})
for (let r = 1; r < d.length; r++) {
if(r in rowhiddenother){
continue;
}
if(d[r] == null){
continue;
}
let cell = d[r][cindex];
let value;
if(cell == null || isRealNull(cell.v)){
value = "null#$$$#null";
}
else if(cell.ct != null && cell.ct.t == "d"){
let fmt = update("YYYY-MM-DD", cell.v);
value = locale_filter.filterDateFormatTip+"#$$$#" + fmt;
}
else{
value = cell.v + "#$$$#" + cell.m;
}
if(value in filterdata){
rowhidden[r] = 0;
}
}
}
let $top = $("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").eq(cindex);
if ($("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']:visible:checked").length < $("#luckysheet-pivotTableFilter-byvalue-select .ListBox input[type='checkbox']:visible").length || $("#luckysheet-pivotTableFilter-byvalue-input").val().length > 0 || ($("#luckysheet-pivotTableFilter-bycondition").next().is(":visible") && $("#luckysheet-pivotTableFilter-byvalue").next().is(":hidden") && $("#luckysheet-pivotTableFilter-selected span").data("value") != "null")) {
$top.data("rowhidden", JSON.stringify(rowhidden)).find(".luckysheet-slider-list-item-filtered").show();
_this.setDatatojsfile("rowhidden", rowhidden, cindex);
if (caljs != null) {
$top.data("byconditionvalue", caljs["value"]).data("byconditiontype", caljs["type"]).data("byconditiontext", caljs["text"]);
if (caljs["value1"] != null) {
$top.data("byconditionvalue1", caljs["value1"]);
}
if (caljs["value2"] != null) {
$top.data("byconditionvalue2", caljs["value2"]);
}
_this.setDatatojsfile("caljs", caljs, cindex);
}
}
else {
$top.data("rowhidden", "").find(".luckysheet-slider-list-item-filtered").hide();
_this.setDatatojsfile("rowhidden", null, cindex);
}
let newdata = [];
for (let i = 0; i < d.length; i++) {
if(i in rowhidden || i in rowhiddenother){
continue;
}
newdata.push([].concat(d[i]));
}
_this.celldata = newdata;
_this.refreshPivotTable();
$("#luckysheet-pivotTableFilter-menu, #luckysheet-pivotTableFilter-submenu").hide();
cleargridelement();
});
$("#luckysheet-data-pivotTable-selection .luckysheet-model-close-btn, #luckysheet-data-pivotTable-selection .luckysheet-modal-dialog-title-close").click(function () {
$("#luckysheet-modal-dialog-slider-pivot").show();
luckysheetsizeauto();
$("#luckysheet-cell-main .luckysheet-pivotTable-selection-set div").show();
$("#luckysheet-data-pivotTable-selection").hide();
sheetmanage.changeSheetExec(_this.pivotSheetIndex);
_this.luckysheet_pivotTable_select_state = false;
cleargridelement();
});
$("#luckysheet-pivotTable-selection-confirm").click(function () {
let $input = $("#luckysheet-pivotTable-range-selection-input"), val = $input.val();
if ($.trim(val).length == 0 || $.trim(val).toUpperCase() == _this.jgridCurrentPivotInput.toUpperCase()) {
$input.val(_this.jgridCurrentPivotInput);
$("#luckysheet-data-pivotTable-selection .luckysheet-model-close-btn").click();
return;
}
else {
let val1 = val.split("!");
let sheettxt = "", rangetxt = "", sheetIndex = -1;
if (val1.length > 1) {
sheettxt = val1[0];
rangetxt = val1[1];
for (let i in Store.luckysheetfile) {
if (sheettxt == Store.luckysheetfile[i].name) {
sheetIndex = Store.luckysheetfile[i].index;
break;
}
}
if (sheetIndex == -1) {
sheetIndex = 0;
}
}
else {
let index = getSheetIndex(Store.currentSheetIndex);
sheettxt = Store.luckysheetfile[index].name;
sheetIndex = Store.luckysheetfile[index].index;
rangetxt = val1[0];
}
if(Store.luckysheetfile[getSheetIndex(sheetIndex)].isPivotTable){
if(isEditMode()){
alert(locale_pivotTable.errorNotAllowPivotData);
}
else{
tooltip.info("", locale_pivotTable.errorNotAllowPivotData);
}
$input.val(_this.jgridCurrentPivotInput);
return;
}
if (rangetxt.indexOf(":") == -1) {
if(isEditMode()){
alert(locale_pivotTable.errorSelectionRange);
}
else{
tooltip.info("", locale_pivotTable.errorSelectionRange);
}
$input.val(_this.jgridCurrentPivotInput);
return;
}
rangetxt = rangetxt.split(":");
let row = [], col = [];
row[0] = parseInt(rangetxt[0].replace(/[^0-9]/g, "")) - 1;
row[1] = parseInt(rangetxt[1].replace(/[^0-9]/g, "")) - 1;
if (row[0] > row[1]) {
if(isEditMode()){
alert(locale_pivotTable.errorSelectionRange);
}
else{
tooltip.info("", locale_pivotTable.errorSelectionRange);
}
$input.val(_this.jgridCurrentPivotInput);
return;
}
col[0] = ABCatNum(rangetxt[0].replace(/[^A-Za-z]/g, ""));
col[1] = ABCatNum(rangetxt[1].replace(/[^A-Za-z]/g, ""));
if (col[0] > col[1]) {
if(isEditMode()){
alert(locale_pivotTable.errorSelectionRange);
}
else{
tooltip.info(locale_pivotTable.errorSelectionRange);
}
$input.val(_this.jgridCurrentPivotInput);
return;
}
sheetmanage.changeSheetExec(_this.pivotSheetIndex);
_this.setDatatojsfile("pivot_select_save", { "row": row, "column": col });
_this.setDatatojsfile("pivotDataSheetIndex", sheetIndex);
_this.getCellData(_this.pivotSheetIndex, sheetIndex, { "row": row, "column": col });
_this.initialPivotManage();
$("#luckysheet-dialog-pivotTable-range").html(val);
$("#luckysheet-modal-dialog-slider-pivot").show();
$("#luckysheet-data-pivotTable-selection").hide();
_this.luckysheet_pivotTable_select_state = false;
_this.refreshPivotTable();
luckysheetsizeauto();
cleargridelement();
}
});
$("#luckysheet-modal-dialog-slider-pivot").on("mousedown", ".luckysheet-slider-list-item-name, .luckysheet-modal-dialog-slider-config-item-txt", function (e) {
let $cur = $(e.target);
_this.movestate = true;
_this.movesave.obj = $cur.parent();
_this.movesave.name = $cur.data("name");
_this.movesave.containerid = $cur.parent().parent().attr("id");
_this.movesave.index = $cur.data("index");
if ($("#luckysheet-modal-dialog-slider-pivot-move").length == 0) {
$("body").first().append('<div id="luckysheet-modal-dialog-slider-pivot-move">' + _this.movesave.name + '</div>');
}
_this.movesave.width = $("#luckysheet-modal-dialog-slider-pivot-move").outerWidth();
_this.movesave.height = $("#luckysheet-modal-dialog-slider-pivot-move").outerHeight();
$("#luckysheet-modal-dialog-pivotTable-list, #luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value").css("cursor", "default");
});
$("#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value").mousemove(function (e) {
if (_this.movestate) {
if (_this.moveitemposition.length == 0) {
_this.moveitemposition = [0];
$(this).find(".luckysheet-modal-dialog-slider-config-item").each(function (i) {
let $t = $(this), h = $t.outerHeight();
_this.moveitemposition.push(_this.moveitemposition[i] + h + 2);
});
$(this).append('<div id="luckysheet-modal-dialog-config-order-help" style="position:absolute;height:3px;width:100%;background:#007ACC;z-index:1;pointer-events: none;user-select:none;"></div>');
}
$("#luckysheet-modal-dialog-slider-pivot-move").css({ "background": "#FD8585", "color": "#fff", "border": "1px solid #FD7070" });
let x = event.pageX, y = event.pageY, $container = $(this);
let curtop = y - $container.offset().top + $container.scrollTop();
let position = _this.moveitemposition;
let row_index = luckysheet_searcharray(position, curtop);
if (row_index == -1) {
$("#luckysheet-modal-dialog-config-order-help").css({ "top": position[position.length - 1] });
}
else if ((curtop - position[row_index - 1]) > (position[row_index] - position[row_index - 1]) / 2) {
$("#luckysheet-modal-dialog-config-order-help").css({ "top": position[row_index] });
}
else {
$("#luckysheet-modal-dialog-config-order-help").css({ "top": position[row_index - 1] });
}
}
}).mouseleave(function () {
if (_this.movestate) {
$("#luckysheet-modal-dialog-slider-pivot-move").css({ "background": "#fff", "color": "#000", "border": "1px dotted #000" });
_this.moveitemposition = [];
$("#luckysheet-modal-dialog-config-order-help").remove();
}
}).mouseup(function (e) {
if (_this.movestate) {
let $t = $(this);
let itemHTML;
if (_this.movesave.containerid == $t.attr("id")) {
itemHTML = _this.movesave.obj.clone();
}
else {
let name = _this.movesave.name,
sumtype = "",
nameindex = "";
if ($t.attr("id") == "luckysheet-modal-dialog-config-value") {
let type = _this.pivot_data_type[_this.movesave.index.toString()];
if (type == "num") {
name = locale_pivotTable.valueStatisticsSUM+":" + name;
sumtype = "data-sumtype='SUM'";
nameindex = "data-nameindex='0'";
}
else {
name = locale_pivotTable.valueStatisticsCOUNTA+":" + name;
sumtype = "data-sumtype='COUNTA'";
nameindex = "data-nameindex='0'";
}
$("#luckysheet-modal-dialog-config-value").find(".luckysheet-modal-dialog-slider-config-item").each(function () {
if ($(this).find(".luckysheet-modal-dialog-slider-config-item-txt").text() == name) {
let ni = parseFloat($(this).data("nameindex")) + 1;
name = name + ni.toString();
$(this).data("nameindex", ni);
return false;
}
});
}
itemHTML = '<div title="' + name + '" class="luckysheet-modal-dialog-slider-config-item" ' + nameindex + ' ' + sumtype + ' data-index="' + _this.movesave.index + '" data-name="' + _this.movesave.name + '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' + nameindex + ' ' + sumtype + ' data-index="' + _this.movesave.index + '" data-name="' + _this.movesave.name + '">' + name + '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
}
let x = event.pageX, y = event.pageY, $container = $(this);
let curtop = y - $container.offset().top + $container.scrollTop();
let position = _this.moveitemposition;
let row_index = luckysheet_searcharray(position, curtop);
if ((_this.movesave.containerid == "luckysheet-modal-dialog-pivotTable-list") || (_this.movesave.containerid == "luckysheet-modal-dialog-config-value" && _this.movesave.containerid != $t.attr("id"))) {
$("#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column").find(".luckysheet-modal-dialog-slider-config-item").each(function () {
if ($(this).data("index") == _this.movesave.index) {
$(this).remove();
}
});
}
if (row_index == -1) {
if ($t.find(".luckysheet-modal-dialog-slider-config-item").length == 0) {
$t.append(itemHTML);
}
else {
$t.find(".luckysheet-modal-dialog-slider-config-item").last().after(itemHTML);
}
}
else if ((curtop - position[row_index - 1]) > (position[row_index] - position[row_index - 1]) / 2) {
$t.find(".luckysheet-modal-dialog-slider-config-item").eq(row_index - 1).after(itemHTML);
}
else {
$t.find(".luckysheet-modal-dialog-slider-config-item").eq(row_index - 1).before(itemHTML);
}
if (_this.movesave.containerid == "luckysheet-modal-dialog-pivotTable-list") {
}
else if (_this.movesave.containerid == "luckysheet-modal-dialog-config-value" && _this.movesave.containerid != $t.attr("id")) {
}
else {
_this.movesave.obj.remove();
}
$("#luckysheet-modal-dialog-pivotTable-list").find(".luckysheet-modal-dialog-slider-list-item").each(function () {
let $seleted = $(this).find(".luckysheet-slider-list-item-selected");
if ($(this).data("index") == _this.movesave.index && $seleted.find("i").length == 0) {
$seleted.append('<i class="fa fa-check luckysheet-mousedown-cancel"></i>');
}
});
_this.refreshPivotTable();
$("#luckysheet-modal-dialog-slider-pivot-move").remove();
_this.movestate = false;
$("#luckysheet-modal-dialog-pivotTable-list, #luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value").css("cursor", "default");
_this.moveitemposition = [];
$("#luckysheet-modal-dialog-config-order-help").remove();
_this.showvaluecolrow();
e.stopPropagation();
}
});
$("#luckysheet-modal-dialog-pivotTable-list").on("click", ".luckysheet-slider-list-item-selected", function () {
let $t = $(this),
$item = $t.parent(),
index = $item.data("index"),
name = $item.data("name");
if ($t.find("i").length == 0) {
$t.append('<i class="fa fa-check luckysheet-mousedown-cancel"></i>');
let type = _this.pivot_data_type[index.toString()],
itemHTML;
if (type == "num") {
itemHTML = '<div title="' + name + '" class="luckysheet-modal-dialog-slider-config-item" data-nameindex="0" data-sumtype="SUM" data-index="' + index + '" data-name="' + name + '"><div class="luckysheet-modal-dialog-slider-config-item-txt" data-nameindex="0" data-sumtype="SUM" data-index="' + index + '" data-name="' + name + '">求和:' + name + '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
$("#luckysheet-modal-dialog-config-value").append(itemHTML);
}
else {
itemHTML = '<div title="' + name + '" class="luckysheet-modal-dialog-slider-config-item" data-index="' + index + '" data-name="' + name + '"><div class="luckysheet-modal-dialog-slider-config-item-txt" data-index="' + index + '" data-name="' + name + '">' + name + '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
let $column = $("#luckysheet-modal-dialog-config-column"),
$row = $("#luckysheet-modal-dialog-config-row");
let columnitem = $column.find(".luckysheet-modal-dialog-slider-config-item"),
rowitem = $row.find(".luckysheet-modal-dialog-slider-config-item");
if (columnitem.length < 2) {
$column.append(itemHTML);
}
else if (rowitem.length < 2) {
$row.append(itemHTML);
}
else {
$column.append(itemHTML);
}
}
}
else {
$t.find("i").remove();
$("#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value").find(".luckysheet-modal-dialog-slider-config-item").each(function () {
if ($(this).data("index") == index) {
if ($(this).parent().attr("id") == "luckysheet-modal-dialog-config-value") {
_this.resetOrderby($(this));
}
$(this).remove();
}
});
}
_this.refreshPivotTable();
_this.showvaluecolrow();
});
$("#luckysheet-dialog-pivotTable-clearitem").click(function () {
$("#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value").find(".luckysheet-modal-dialog-slider-config-item").each(function () {
$(this).remove();
});
$("#luckysheet-modal-dialog-pivotTable-list").find(".luckysheet-modal-dialog-slider-list-item").each(function () {
$(this).find(".luckysheet-slider-list-item-selected").find("i").remove();
});
_this.refreshPivotTable();
_this.showvaluecolrow();
});
}
if (restore == null) {
restore = false;
}
if (_this.celldata.length <= 1 && _this.celldata[0].length <= 1) {
if(isEditMode()){
alert(locale_pivotTable.errorIncreaseRange);
}
else{
tooltip.info("", locale_pivotTable.errorIncreaseRange);
}
}
let selecteditem = "",
selecteditemIndex = 1,
selecteditemtest = {},
selecteditemNullIndex = 1;
for (let i = 0; i < _this.celldata[0].length; i++) {
let name;
if(!!_this.celldata[0][i] && !!_this.celldata[0][i]["m"]){
name = _this.celldata[0][i]["m"];
}
else{
name = getcellvalue(0, i, _this.celldata);
}
if(name != null){
name = name.toString();
}
if (name == null || $.trim(name.toString()).length == 0) {
name = locale_pivotTable.titleColumn+" " + selecteditemNullIndex;
}
selecteditemNullIndex++
if (name in selecteditemtest) {
name = name + selecteditemIndex++;
if (name in selecteditemtest) {
name = name + selecteditemIndex++;
if (name in selecteditemtest) {
name = name + selecteditemIndex++;
}
}
}
selecteditemtest[name] = 1;
let dataother = "", style = "";
if (restore && _this.filterparm != null) {
if (_this.filterparm[i.toString()] != null) {
let itemset = _this.filterparm[i.toString()];
if (itemset.rowhidden != null) {
dataother += "data-rowhidden='" + JSON.stringify(itemset.rowhidden) + "'";
}
if (itemset.selected != null) {
dataother += "data-selected='" + JSON.stringify(itemset.selected) + "'";
}
if (itemset.caljs != null) {
let caljsset = itemset.caljs;
if (caljsset.value != null) {
dataother += "data-byconditionvalue='" + caljsset.value + "'";
}
if (caljsset.type != null) {
dataother += "data-byconditiontype='" + caljsset.type + "'";
}
if (caljsset.text != null) {
dataother += "data-byconditiontext='" + caljsset.text + "'";
}
if (caljsset.value1 != null) {
dataother += "data-byconditionvalue1='" + caljsset.value1 + "'";
}
if (caljsset.value2 != null) {
dataother += "data-byconditionvalue2='" + caljsset.value2 + "'";
}
}
}
}
if (dataother.length > 0) {
style = "display:block;";
}
selecteditem += '<div class="luckysheet-modal-dialog-slider-list-item" ' + dataother + ' data-index="' + i + '" data-name="' + name + '"><div title="'+locale_pivotTable.titleAddColumn+'" class="luckysheet-slider-list-item-selected"><div></div></div><div title="'+locale_pivotTable.titleMoveColumn+'" class="luckysheet-slider-list-item-name" ' + dataother + ' data-index="' + i + '" data-name="' + name + '">' + name + '</div><div title="'+locale_pivotTable.titleClearColumnFilter+'" class="luckysheet-slider-list-item-filtered" style="' + style + '"><i class="fa fa-filter luckysheet-mousedown-cancel" aria-hidden="true"></i><i class="fa fa-times" aria-hidden="true"></i></div><div title="'+locale_pivotTable.titleFilterColumn+'" class="luckysheet-slider-list-item-filter"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
}
$("#luckysheet-modal-dialog-pivotTable-list").html(selecteditem);
$("#luckysheetpivottablevaluecolrowshow").hide();
$("#luckysheetpivottablevaluecolrow").prop("checked", true);
$("#luckysheetpivottablevaluecolrow1").prop("checked", false);
$("#luckysheet-modal-dialog-config-filter, #luckysheet-modal-dialog-config-row, #luckysheet-modal-dialog-config-column, #luckysheet-modal-dialog-config-value").empty();
if (restore) {
if (_this.filter != null && _this.filter.length > 0) {
for (let i = 0; i < _this.filter.length; i++) {
let item = _this.filter[i];
let itemHTML = '<div title="' + name + '" class="luckysheet-modal-dialog-slider-config-item" data-index="' + item.index + '" data-name="' + item.name + '"><div class="luckysheet-modal-dialog-slider-config-item-txt" data-index="' + item.index + '" data-name="' + item.name + '">' + item.name + '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
$("#luckysheet-modal-dialog-config-filter").append(itemHTML);
let $seleted = $("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").eq(item.index).find(".luckysheet-slider-list-item-selected");
if ($seleted.find("i").length == 0) {
$seleted.append('<i class="fa fa-check luckysheet-mousedown-cancel"></i>');
}
}
}
if (_this.row != null && _this.row.length > 0) {
for (let i = 0; i < _this.row.length; i++) {
let item = _this.row[i];
let otherset = "";
if (item.order != null) {
otherset += "data-order = '" + item.order + "'";
}
if (item.orderby != null) {
otherset += "data-orderby = '" + item.orderby + "'";
}
if (item.order != null) {
otherset += "data-stastic = '" + item.stastic + "'";
}
let itemHTML = '<div title="' + name + '" class="luckysheet-modal-dialog-slider-config-item" ' + otherset + ' data-index="' + item.index + '" data-name="' + item.name + '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' + otherset + ' data-index="' + item.index + '" data-name="' + item.name + '">' + item.name + '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
$("#luckysheet-modal-dialog-config-row").append(itemHTML);
let $seleted = $("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").eq(item.index).find(".luckysheet-slider-list-item-selected");
if ($seleted.find("i").length == 0) {
$seleted.append('<i class="fa fa-check luckysheet-mousedown-cancel"></i>');
}
}
}
if (_this.column != null && _this.column.length > 0) {
for (let i = 0; i < _this.column.length; i++) {
let item = _this.column[i];
let otherset = "";
if (item.order != null) {
otherset += "data-order = '" + item.order + "'";
}
if (item.orderby != null) {
otherset += "data-orderby = '" + item.orderby + "'";
}
if (item.order != null) {
otherset += "data-stastic = '" + item.stastic + "'";
}
let itemHTML = '<div title="' + name + '" class="luckysheet-modal-dialog-slider-config-item" ' + otherset + ' data-index="' + item.index + '" data-name="' + item.name + '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' + otherset + ' data-index="' + item.index + '" data-name="' + item.name + '">' + item.name + '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
$("#luckysheet-modal-dialog-config-column").append(itemHTML);
let $seleted = $("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").eq(item.index).find(".luckysheet-slider-list-item-selected");
if ($seleted.find("i").length == 0) {
$seleted.append('<i class="fa fa-check luckysheet-mousedown-cancel"></i>');
}
}
}
if (_this.values != null && _this.values.length > 0) {
for (let i = 0; i < _this.values.length; i++) {
let item = _this.values[i];
let otherset = "";
if (item.sumtype != null) {
otherset += "data-sumtype = '" + item.sumtype + "'";
}
if (item.nameindex != null) {
otherset += "data-nameindex = '" + item.nameindex + "'";
}
let itemHTML = '<div title="' + name + '" class="luckysheet-modal-dialog-slider-config-item" ' + otherset + ' data-index="' + item.index + '" data-name="' + item.name + '"><div class="luckysheet-modal-dialog-slider-config-item-txt" ' + otherset + ' data-index="' + item.index + '" data-name="' + item.name + '">' + _this.getSumTypeName(item.sumtype) + ":" + item.name + '</div><div class="luckysheet-modal-dialog-slider-config-item-icon"><i class="fa fa-sort-desc" aria-hidden="true"></i></div></div>';
$("#luckysheet-modal-dialog-config-value").append(itemHTML);
let $seleted = $("#luckysheet-modal-dialog-pivotTable-list .luckysheet-modal-dialog-slider-list-item").eq(item.index).find(".luckysheet-slider-list-item-selected");
if ($seleted.find("i").length == 0) {
$seleted.append('<i class="fa fa-check luckysheet-mousedown-cancel"></i>');
}
}
if (_this.values.length >= 2) {
$("#luckysheetpivottablevaluecolrowshow").show();
if (_this.showType == "column") {
$("#luckysheetpivottablevaluecolrow").prop("checked", true);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']").addClass("ui-state-active");
$("#luckysheetpivottablevaluecolrow1").prop("checked", false);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']").removeClass("ui-state-active");
}
else {
$("#luckysheetpivottablevaluecolrow1").prop("checked", true);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow1']").addClass("ui-state-active");
$("#luckysheetpivottablevaluecolrow").prop("checked", false);
$("#luckysheetpivottablevaluecolrowshow label[for='luckysheetpivottablevaluecolrow']").removeClass("ui-state-active");
}
}
}
}
$("#luckysheet-dialog-pivotTable-range").html(getRangetxt(_this.pivotDataSheetIndex, _this.pivot_select_save));
$("#luckysheet-modal-dialog-slider-pivot").show();
luckysheetsizeauto(false);
},
getComposeArray: function (data) {
if (data.length == 0) {
return [];
}
let ret = [];
for (let i = 0; i < data.length; i++) {
let name = "";
for (let x = 0; x <= i; x++) {
if(!!data[x] && !!data[x]["m"]){
name += data[x]["m"];
}
else{
name += getcellvalue(x, null, data);
}
}
ret.push(name);
}
return ret;
},
getnameArray: function (data, field) {
if (data.length == 0) {
return [];
}
if (field.length == 0) {
return [];
}
let ret = [];
for (let i = 0; i < field.length; i++) {
let c_value;
if(!!data[field[i].index] && !!data[field[i].index]["m"]){
c_value = data[field[i].index]["m"];
}
else{
c_value = getcellvalue(field[i].index, null, data);
}
ret.push(c_value);
}
return ret;
},
getTitleFromGroup: function (group, config, dataposition) {
let _this = this;
let orderbygroup = _this.orderbygroup(group, config, dataposition);
return _this.generategrouparraymain(orderbygroup, config);
},
orderbygroup: function (group, config, dataposition) {
let _this = this;
let stackset = [];
if (group.length == 0) {
return [];
}
stackset = group;
let d = null,
alllength = stackset.length,
alllengthInital = stackset.length,
a = 0;
while (alllength != 0) {
d = stackset[a++];
alllength--;
if (d.children != null && d.children.length > 0) {
d.children = _this.orderbygroupchildren(d.children, config[d.index].orderby, config[d.index].order, dataposition);
for (let i = 0; i < d.children.length; i++) {
stackset.push(d.children[i]);
alllength++;
}
}
}
return group.splice(0, alllengthInital);
},
orderbygroupchildren: function (childrens, orderby, order, dataposition) {
if (childrens.length == 0) {
return [];
}
let isAsc = false;
if (order == null || order == "asc") {
isAsc = true;
}
const _locale = locale();
const locale_filter = _locale.filter;
let a = function (x, y) {
let f = null, s = null;
if (orderby == "self" || orderby == null) {
if(x.name == null){
f = locale_filter.valueBlank;
}
else{
f = x.name.toString();
}
if(y.name == null){
s = locale_filter.valueBlank;
}
else{
s = y.name.toString();
}
if (isdatetime(f) && isdatetime(s)) {
return diff(f, s);
}
}
else {
f = parseFloat(dataposition[x.orderby].result);
s = parseFloat(dataposition[y.orderby].result);
}
if (!isNaN(f) && !isNaN(s)) {
return numeral(f).value() - numeral(s).value();
}
else if(isNaN(f) && isNaN(s)){
return f.localeCompare(s);
}
else if (isNaN(f)) {
return 1;
}
else if (isNaN(s)) {
return -1;
}
}
let d = function (x, y) {
let f = null, s = null;
if (orderby == "self" || orderby == null) {
if(x.name == null){
f = locale_filter.valueBlank;
}
else{
f = x.name.toString();
}
if(y.name == null){
s = locale_filter.valueBlank;
}
else{
s = y.name.toString();
}
if (isdatetime(f) && isdatetime(s)) {
return diff(f, s);
}
}
else {
f = parseFloat(dataposition[x.orderby].result);
s = parseFloat(dataposition[y.orderby].result);
}
if (!isNaN(f) && !isNaN(s)) {
return numeral(s).value() - numeral(f).value();
}
else if(isNaN(f) && isNaN(s)){
return s.localeCompare(f);
}
else if (isNaN(f)) {
return -1;
}
else if (isNaN(s)) {
return 1;
}
}
if (isAsc) {
return childrens.sort(a);
}
else {
return childrens.sort(d);
}
},
generategroupaddstatic: function (arr, name) {
let stasticarr = [];
const _locale = locale();
const locale_pivotTable = _locale.pivotTable;
for (let a = 0; a < arr[0].length; a++) {
if (a == 0) {
if (name == locale_pivotTable.valueSum) {
stasticarr.push(name);
}
else {
stasticarr.push({ "name": name, "issum": true });
}
}
else {
stasticarr.push("");
}
}
return stasticarr;
},
generategrouparraymain: function (group, config) {
let _this = this;
//生成数组
let ret = [];
for (let i = 0; i < group.length; i++) {
let name = group[i].name;
let arr = _this.generategrouparray(group[i].children, config, 1);
if (config[0].stastic == "1" || config[0].stastic == null) {
arr.push(_this.generategroupaddstatic(arr, name));
}
ret = ret.concat(arr);
}
return ret;
},
generategrouparray: function (group, config, level) {
let _this = this;
let ret = [];
for (let i = 0; i < group.length; i++) {
let name = group[i].name;
let arr;
if (group[i].children == 0 || group[i].children.length == 0) {
arr = [name];
ret.push(arr);
}
else {
arr = _this.generategrouparray(group[i].children, config, level + 1);
for (let a = 0; a < arr.length; a++) {
arr[a].unshift(name);
}
if (config[level].stastic == "1" || config[level].stastic == null) {
arr.push(_this.generategroupaddstatic(arr, name));
}
ret = ret.concat(arr);
}
}
return ret;
},
addStatisticsData: function (dataposition, valueobj, indicator, d_value) {
if (dataposition[indicator] == null) {
dataposition[indicator] = {
"data": [],
"count": 0,
"max": -Infinity,
"min": Infinity,
"counta": 0,
"countunique": 0,
"countuniquedata": {},
"sum": 0,
"digitaldata": [],
"sumtype": valueobj.sumtype,
"index": valueobj.index,
"name": valueobj.fullname,
"acc": 0
};
}
if (isdatatypemulti(d_value)["num"] === true) {
//fix issue 265
let num = numFormat(d_value,6);
dataposition[indicator]["digitaldata"].push(num);
dataposition[indicator]["count"] += 1;
dataposition[indicator]["sum"] += num;
if (num > dataposition[indicator]["max"]) {
dataposition[indicator]["max"] = num;
}
if (num < dataposition[indicator]["min"]) {
dataposition[indicator]["min"] = num;
}
let newAcc = numfloatlen(num);
if(newAcc > dataposition[indicator]["acc"]){
dataposition[indicator]["acc"] = newAcc;
}
}
if (d_value != "") {
dataposition[indicator]["data"].push(d_value);
dataposition[indicator]["counta"] += 1;
if (!(d_value in dataposition[indicator]["countuniquedata"])) {
dataposition[indicator]["countuniquedata"][d_value] = 1;
dataposition[indicator]["countunique"] += 1;
}
}
},
dataHandler: function (column, row, values, showType, celldata) {
//column:[{"index":1, name:"列1", "order":"asc", "orderby":"self/0/1/2", "stastic":"0/1"}]
//row:[{"index":1, name:"列3", "order":"asc", "orderby":"self/0/1/2", "stastic":"0/1"}]
//values:[{"index":1, "sumtype":"SUM/COUNT/COUNTA/COUNTUNIQUE/AVERAGE/MAX/MIN/MEDIAN/PRODUCT/STDEV/STDEVP/let/VARP", "name":"求和:fyc"}]
let _this = this;
const _locale = locale();
const locale_filter = _locale.filter;
const locale_pivotTable = _locale.pivotTable;
if (showType == null) {
showType = "column";
}
if ((column.length == 0 && row.length == 0 && values.length == 0) || celldata.length == 0) {
_this.pivotDatas = [];
return [];
}
//生成透视表值及定位
let dataposition = {},
data = celldata,
datarowtitle = [],
datarowtitlegroup = [],
datarowposition = {},
datarowposition_i = 0,
datacoltitle = [],
datacoltitlegroup = [],
datacolposition = {},
datacolposition_i = 0;
for (let i = 1; i < data.length; i++) {
let d = data[i];
let groupbyrowtxt = "",
groupbycoltxt = "",
rowtxt = "",
rowtitle = [],
rowtitlename = [],
coltxt = "",
coltitle = [],
coltitlename = [];
//["四川", "成都", "邛崃"] 转换为 ["四川", "四川成都", "四川成都邛崃"]
rowtitlename = _this.getnameArray(d, row);
coltitlename = _this.getnameArray(d, column);
rowtitle = _this.getComposeArray(rowtitlename);
coltitle = _this.getComposeArray(coltitlename);
if (rowtitle.length > 0) {
rowtitle.unshift(locale_pivotTable.valueSum);
}
if (coltitle.length > 0) {
coltitle.unshift(locale_pivotTable.valueSum);
}
let curentLevelobj_row = datarowposition,
curentLevelarr_row = datarowtitlegroup;
for (let r = 0; r < rowtitle.length; r++) {
let item = rowtitle[r], name = r == 0 ? locale_pivotTable.valueSum : rowtitlename[r - 1];//修改
if (curentLevelobj_row[r.toString()] != null && curentLevelobj_row[r.toString()][item] != null) {//修改
curentLevelarr_row = curentLevelarr_row[curentLevelobj_row[r.toString()][item]].children;
}
else {
let orderby = r == 0 ? "self" : ((row[r - 1].orderby == "self" || row[r - 1].orderby == null) ? item : (showType == "column" ? item + values[parseInt(row[r - 1].orderby)].fullname : item + locale_pivotTable.valueSum));
if(name == null){
name = locale_filter.valueBlank;
}
curentLevelarr_row.push({ "name": name, "fullname": item, "index": r, "orderby": orderby, "children": [] });
if (curentLevelobj_row[r.toString()] == null) {
curentLevelobj_row[r.toString()] = {};
}
if (curentLevelobj_row[r.toString()][item] == null) {
curentLevelobj_row[r.toString()][item] = curentLevelarr_row.length - 1;
}
curentLevelarr_row = curentLevelarr_row[curentLevelarr_row.length - 1].children;
}
}
let curentLevelobj_col = datacolposition,
curentLevelarr_col = datacoltitlegroup;
for (let r = 0; r < coltitle.length; r++) {
let item = coltitle[r], name = r == 0 ? locale_pivotTable.valueSum : coltitlename[r - 1];
if (curentLevelobj_col[r.toString()] != null && curentLevelobj_col[r.toString()][item] != null) {
curentLevelarr_col = curentLevelarr_col[curentLevelobj_col[r.toString()][item]].children;
}
else {
let orderby = r == 0 ? "self" : ((column[r - 1].orderby == "self" || column[r - 1].orderby == null) ? item : (showType == "column" ? locale_pivotTable.valueSum + item : values[parseInt(column[r - 1].orderby)].fullname + item));
if(name == null){
name = locale_filter.valueBlank;
}
curentLevelarr_col.push({ "name": name, "fullname": item, "index": r, "orderby": orderby, "children": [] });
if (curentLevelobj_col[r.toString()] == null) {
curentLevelobj_col[r.toString()] = {};
}
if (curentLevelobj_col[r.toString()][item] == null) {
curentLevelobj_col[r.toString()][item] = curentLevelarr_col.length - 1;
}
curentLevelarr_col = curentLevelarr_col[curentLevelarr_col.length - 1].children;
}
}
let v_str = "";
for (let v = 0; v < values.length; v++) {
let d_value = getcellvalue(values[v].index, null, d);
let coltitle_c = [].concat(coltitle), rowtitle_c = [].concat(rowtitle);
if (showType == "column") {
if (coltitle_c.length > 0) {
coltitle_c.push("")
coltitle_c = coltitle_c.join(values[v].fullname + "|||").split("|||").slice(0, coltitle_c.length - 1);
}
else {
coltitle_c.push(values[v].fullname);
}
}
else {
if (rowtitle_c.length > 0) {
rowtitle_c.push("")
rowtitle_c = rowtitle_c.join(values[v].fullname + "|||").split("|||").slice(0, rowtitle_c.length - 1);
}
else {
rowtitle_c.push(values[v].fullname);
}
}
if (coltitle_c.length == 0) {
coltitle_c.push("");
}
if (rowtitle_c.length == 0) {
rowtitle_c.push("");
}
for (let r = 0; r < rowtitle_c.length; r++) {
for (let c = 0; c < coltitle_c.length; c++) {
let indicator = rowtitle_c[r] + coltitle_c[c];
_this.addStatisticsData(dataposition, values[v], indicator, d_value);
}
}
}
}
//计算值列
//SUM/COUNT/COUNTA/COUNTUNIQUE/AVERAGE/MAX/MIN/MEDIAN/PRODUCT/STDEV/STDEVP/let/VARP
for (let indicator in dataposition) {
let json = dataposition[indicator];
if (json.sumtype == "SUM") {
json.result = json.sum;
}
else if (json.sumtype == "COUNT") {
json.result = json.count;
}
else if (json.sumtype == "COUNTA") {
json.result = json.counta;
}
else if (json.sumtype == "COUNTUNIQUE") {
json.result = json.countunique;
}
else if (json.sumtype == "AVERAGE") {
json.result = numFormat(json.sum / json.count);
}
else if (json.sumtype == "MAX") {
json.result = json.max;
}
else if (json.sumtype == "MIN") {
json.result = json.min;
}
else if (json.sumtype == "MEDIAN") {
let numArr = json.digitaldata.sort(function(a, b){ return a - b });
let numLen = numArr.length;
let numindex = parseInt(numLen / 2);
if(numLen % 2 == 0){
json.result = (numArr[numindex - 1] + numArr[numindex]) / 2;
}
else{
json.result = numArr[numindex];
}
}
else if (json.sumtype == "PRODUCT") {
json.result = new Function("return " + json.digitaldata.join("*"))();
}
else if (json.sumtype == "STDEV") {
let mean = json.sum / json.count;
json.result = analysis.STDEV(mean, json.digitaldata);
}
else if (json.sumtype == "STDEVP") {
let mean = json.sum / json.count;
json.result = analysis.STDEVP(mean, json.digitaldata);
}
else if (json.sumtype == "let") {
let mean = json.sum / json.count;
json.result = analysis.let(mean, json.digitaldata);
}
else if (json.sumtype == "VARP") {
let mean = json.sum / json.count;
json.result = analysis.VARP(mean, json.digitaldata);
}
let newAcc = numfloatlen(json.result);
if(newAcc > json.acc){
json.acc = newAcc;
}
json.result = numFormat(json.result, json.acc);
}
datarowtitle = _this.getTitleFromGroup(datarowtitlegroup, row, dataposition);
datacoltitle = _this.getTitleFromGroup(datacoltitlegroup, column, dataposition);
//加入值到列/行形成新的表头
if (showType == "column") {
if (datacoltitle.length > 0 && datacoltitle[0].length > 0) {
datacoltitle = _this.addValuesToTitle(datacoltitle, values);
}
else {
for (let v = 0; v < values.length; v++) {
datacoltitle.push([values[v].fullname]);
}
}
}
else {
if (datarowtitle.length > 0 && datarowtitle[0].length > 0) {
datarowtitle = _this.addValuesToTitle(datarowtitle, values);
}
else {
for (let v = 0; v < values.length; v++) {
datarowtitle.push([values[v].fullname]);
}
}
}
let datacoltitle_index = datacoltitle;
datacoltitle = luckysheetArray.transpose(datacoltitle, false);
let valuenslen = values.length == 0 ? 0 : 1;
let rowLen = (datacoltitle.length == 0 ? valuenslen : datacoltitle.length) + (datarowtitle.length == 0 ? valuenslen : datarowtitle.length), colLen = (datacoltitle.length == 0 ? valuenslen : datacoltitle[0].length) + (datarowtitle.length == 0 ? valuenslen : datarowtitle[0].length);
let rowOver = datacoltitle.length, colOver = datarowtitle.length == 0 ? 0 : datarowtitle[0].length;
let retdata = [];
for (let r = 0; r < rowLen; r++) {
retdata[r] = new Array(colLen);
for (let c = 0; c < colLen; c++) {
let drt = datarowtitle[r - rowOver];
if (r < rowOver && c < colOver) {
//空白列头
retdata[r][c] = "";
}
else if (r < rowOver && c >= colOver) {
//列标题
if (datacoltitle[r] != null) {
if (getObjType(datacoltitle[r][c - colOver]) == "object") {
retdata[r][c] = datacoltitle[r][c - colOver].name + locale_pivotTable.valueSum;
}
else {
retdata[r][c] = datacoltitle[r][c - colOver];
}
}
else {
retdata[r][c] = "";
}
}
else if (r >= rowOver && c < colOver) {
//行标题
if (drt != null) {
if (getObjType(drt[c]) == "object") {
retdata[r][c] = drt[c].name + locale_pivotTable.valueSum;
}
else {
retdata[r][c] = drt[c];
}
}
else {
retdata[r][c] = "";
}
}
else {
//单元格内容
let prefix = "";
if (drt != null) {
if (!(drt instanceof Array) || drt.length == 1) {
if (drt instanceof Array) {
prefix = drt[0];
}
else {
prefix = drt;
}
}
else {
for (let x = 0; x < drt.length; x++) {
if (getObjType(drt[x]) == "object") {
prefix += drt[x].name;
}
else {
prefix += drt[x];
}
}
}
}
let suffix = "";
let dct = datacoltitle_index[c - colOver];
if (dct != null) {
if (!(dct instanceof Array) || dct.length == 1) {
if (dct instanceof Array) {
suffix = dct[0];
}
else {
suffix = dct;
}
}
else {
for (let x = 0; x < dct.length; x++) {
if (getObjType(dct[x]) == "object") {
suffix += dct[x].name;
}
else {
suffix += dct[x];
}
}
}
}
let indicator = prefix;
if (prefix != "" && suffix != "") {
indicator = prefix + suffix;
}
else if (prefix == "") {
indicator = suffix;
}
if (dataposition[indicator] == null) {
retdata[r][c] = "";
}
else {
retdata[r][c] = dataposition[indicator].result;
}
}
}
}
if (values.length == 1 && column.length > 0 && row.length > 0 ) {
retdata[0][0] = values[0].fullname;
retdata.splice(column.length, 1);
}
else if(values.length == 1 && column.length > 0){
// 0: (6) ["English", "foreign language", "mathematics", "science", "Sum", undefined]
// 1: (6) ["CountA:score", "CountA:score", "CountA:score", "CountA:score", "CountA:score", undefined]
// 2: (6) [3, 3, 3, 3, 12, ""]
//The above format does not meet viewing habits,Process retdata into the correct format
let titleRow = retdata.splice(column.length, 1);
let newRetdata = [];
for(let r=0;r<retdata.length;r++){
let row = [];
if(r==retdata.length-1){
row.push(titleRow[0][0]);
}
else{
row.push("");
}
for(let c=0;c<retdata[r].length-1;c++){
row.push(retdata[r][c]);
}
newRetdata.push(row);
}
retdata = newRetdata;
}
_this.pivotDatas = retdata;
return retdata;
},
drillDown: function(row_index, col_index){
if(!checkProtectionAuthorityNormal(Store.currentSheetIndex, "usePivotTablereports")){
return;
}
let _this = this;
let cell = _this.pivotDatas[row_index][col_index];
let d = $.extend(true, [], sheetmanage.nulldata);
const _locale = locale();
const locale_filter = _locale.filter;
const locale_pivotTable = _locale.pivotTable;
let selecteditemNullIndex = 1;
for(let i = 0; i < _this.celldata[0].length; i++){
let name;
if(!!_this.celldata[0][i] && !!_this.celldata[0][i]["m"]){
name = _this.celldata[0][i]["m"];
}
else{
name = getcellvalue(0, i, _this.celldata);
}
if(name != null){
name = name.toString();
}
if (name == null || $.trim(name.toString()).length == 0) {
name = locale_pivotTable.titleColumn+" " + selecteditemNullIndex;
}
selecteditemNullIndex++
d[0][i] = name;
}
let obj = {};
//行
if(_this.row != null && _this.row.length > 0){
for(let a = 0; a < _this.row.length; a++){
obj[_this.row[a]["index"]] = _this.pivotDatas[row_index][a];
}
}
//列
if(_this.column != null && _this.column.length > 0){
for(let b = 0; b < _this.column.length; b++){
obj[_this.column[b]["index"]] = _this.pivotDatas[b][col_index];
}
}
let rowArr = [];
for(let j = 1; j < _this.celldata.length; j++){
let isEqual = true
for(let x in obj){
let value;
if(!!_this.celldata[j][x] && !!_this.celldata[j][x]["m"]){
value = _this.celldata[j][x]["m"];
}
else{
value = getcellvalue(j, x, _this.celldata);
}
if(value != null){
value = value.toString();
}
else{
value = locale_filter.valueBlank;
}
if(value != obj[x]){
isEqual = false;
break;
}
}
if(isEqual){
rowArr.push(j);
}
}
for(let r = 0; r < rowArr.length; r++){
for(let c = 0; c < _this.celldata[0].length; c++){
let value;
if(!!_this.celldata[rowArr[r]][c] && !!_this.celldata[rowArr[r]][c]["m"]){
value = _this.celldata[rowArr[r]][c]["m"];
}
else{
value = getcellvalue(rowArr[r], c, _this.celldata);
}
if(value != null){
value = value.toString();
}
else{
value = "";
}
d[r + 1][c] = value;
}
}
Store.luckysheet_select_save = [{ "row": [0, rowArr.length], "column": [0, _this.celldata[0].length - 1] }];
Store.clearjfundo = false;
jfrefreshgrid(d, Store.luckysheet_select_save);
selectHightlightShow();
Store.clearjfundo = true;
}
}
export default pivotTable;