ConstructionCost/web/building_saas/complementary_glj_lib/js/sheetOpr.js

/**
 * Created by Zhong on 2017/8/24.
 */
let sheetOpr = {
    createSpread: function(container, SheetCount){
        var me = this;
        var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: SheetCount });
        spreadBook.options.allowCopyPasteExcelStyle = false;
        spreadBook.options.tabStripVisible = false;
        spreadBook.options.scrollbarMaxAlign = true;
        spreadBook.options.allowContextMenu = false;
        //spreadBook.options.showHorizontalScrollbar = false;
        spreadBook.options.allowUserDragDrop = false;
        return spreadBook;
    },

    initSheet: function(sheet, setting, rowCount) {
        var me = this;
        var spreadNS = GC.Spread.Sheets;
        sheet.suspendPaint();
        sheet.suspendEvent();
        sheet.setRowCount(1, spreadNS.SheetArea.colHeader);
        sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport);
        sheet.options.colHeaderAutoTextIndex = 1;
        sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
        sheet.options.protectionOptions = {
            allowResizeRows: true,
            allowResizeColumns: true
        };
        sheet.showRowOutline(false);
        me.buildHeader(sheet, setting);
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.resumeEvent();
        sheet.resumePaint();
    },

    buildSheet: function(container, setting, rowCount, buildHeader = true) {
        var me = this;
        var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: 1 });
        spreadBook.options.tabStripVisible = false;
        //spreadBook.options.showHorizontalScrollbar = false;
        spreadBook.options.scrollbarMaxAlign = true;
        spreadBook.options.allowCopyPasteExcelStyle = false;
        spreadBook.options.allowExtendPasteRange = true;
        spreadBook.options.allowUserDragDrop = false;
        spreadBook.options.allowUserDragFill = false;
        spreadBook.options.allowContextMenu = false;
        var spreadNS = GC.Spread.Sheets;
        var sheet = spreadBook.getSheet(0);
        sheet.suspendPaint();
        sheet.suspendEvent();
        //Set rowHeader count and columnHeader count.
        sheet.setRowCount(1, spreadNS.SheetArea.colHeader);
        sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport);
        sheet.options.clipBoardOptions = GC.Spread.Sheets.ClipboardPasteOptions.values;
        sheet.options.colHeaderAutoTextIndex = 1;
        sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
        sheet.options.protectionOptions = {
            allowResizeRows: true,
            allowResizeColumns: true
        };
        sheet.showRowOutline(false);
        //setup column header
        if (buildHeader) {
            me.buildHeader(sheet, setting);
        }
        //setup cells
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.resumeEvent();
        sheet.resumePaint();
        return spreadBook;
    },
    buildHeader: function(sheet, setting){
        var me = this, ch = GC.Spread.Sheets.SheetArea.colHeader;
        for (var i = 0; i < setting.header.length; i++) {
            sheet.setValue(0, i, setting.header[i].headerName, ch);
            sheet.setColumnWidth(i, setting.header[i].headerWidth?setting.header[i].headerWidth:100);
        }
    },
    cleanSheet: function(sheet, setting, rowCount) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.clearSelection();
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    cleanData: function(sheet, setting, rowCount) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    setAreaAlign: function(area, hAlign, vAlign){
        if (!(hAlign) || hAlign === "left") {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
        } else if (hAlign === "right") {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.right);
        } else if (hAlign === "center") {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
        } else {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
        }
        if (!(vAlign) || vAlign === "center") {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
        } else if (vAlign === "top") {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.top);
        } else if (vAlign === "bottom") {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.bottom);
        } else {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
        }
    },
    /* showData: function(sheet, setting, data, distTypeTree, machineModelIdx) {
        var me = this, ch = GC.Spread.Sheets.SheetArea.viewport;
        sheet.suspendPaint();
        sheet.suspendEvent();
        let checkBoxType = new GC.Spread.Sheets.CellTypes.CheckBox();
        if(typeof setting.owner !== 'undefined' && setting.owner === 'gljComponent'){
            sheet.setRowCount(data.length + 5);
        }
        else{
            sheet.setRowCount(typeof repositoryGljObj !== 'undefined' && repositoryGljObj.currentOprParent === 1 ? data.length : data.length + 10);
        }
        for (var col = 0; col < setting.header.length; col++) {
            var hAlign = "left", vAlign = "center";
            if (setting.header[col].hAlign) {
                hAlign = setting.header[col].hAlign;
            } else if (setting.header[col].dataType !== "String"){
                hAlign = "right";
            }
            vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign;
            me.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign);
            if (setting.header[col].formatter) {
                sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
            }
            for (var row = 0; row < data.length; row++) {
                if(setting.header[col].dataCode === 'gljType' && data[row].gljType){
                    let distTypeVal =  distTypeTree.distTypes[distTypeTree.prefix + data[row].gljType].data.fullName;
                    sheet.setValue(row, col, distTypeVal, ch);
                }
                else if(setting.header[col].dataCode === 'model' && data[row].model){
                    sheet.setValue(row, col, machineModelIdx ? machineModelIdx[data[row].model] : '');
                }
                else {
                    sheet.setValue(row, col, data[row][setting.header[col].dataCode], ch);
                    sheet.setTag(row, 0, data[row].ID, ch);
                }
                //复选框
                if(setting.header[col].dataCode === 'isComplementary'){
                    sheet.setCellType(row, col, checkBoxType);
                    sheet.getCell(row, col).value(1);
                }
                //新增组成物表，选择复选框
                if(setting.header[col].dataCode === 'select'){
                    sheet.setCellType(row, col, checkBoxType)
                    if(data[row].isChecked === true){
                        sheet.getCell(row, col).value(1);
                    }
                }
            }
            for(let i = data.length; i < sheet.getRowCount(); i++){
                sheet.setCellType(i, 6, null);
            }
        }
        sheet.resumeEvent();
        sheet.resumePaint();
        //me.shieldAllCells(sheet);
    }, */
    analyzePasteData: function(setting, pastedInfo) {
        var rst = [], propId = pastedInfo.cellRange.col, preStrIdx = 0, itemObj = {};
        for (var i = 0; i < pastedInfo.pasteData.text.length; i++) {
            if (pastedInfo.pasteData.text[i] === "\n") {
                propId = pastedInfo.cellRange.col;
                preStrIdx = i + 1;
                rst.push(itemObj);
                if (i < pastedInfo.pasteData.text.length - 1) {
                    itemObj = {};
                }
            } else if (pastedInfo.pasteData.text[i] === "\t" || pastedInfo.pasteData.text[i] === "\r") {
                itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx, i);
                propId++;
                preStrIdx = i + 1;
                //if the last copied-cell were empty, should check whether the end of text
                if (i == pastedInfo.pasteData.text.length - 1) {
                    itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
                    rst.push(itemObj);
                }
            } else if (i == pastedInfo.pasteData.text.length - 1) {
                itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
                rst.push(itemObj);
            }
        }
        return rst;
    },
    showData: function(obj, sheet, setting, data, distTypeTree, materialTypeIdx, machineModelIdx) {
        var me = this, ch = GC.Spread.Sheets.SheetArea.viewport;
        sheet.suspendPaint();
        sheet.suspendEvent();
        if(typeof setting.owner !== 'undefined' && setting.owner === 'gljComponent'){
            sheet.setRowCount(data.length + 5);
        }
        else{
            sheet.setRowCount(typeof repositoryGljObj !== 'undefined' && repositoryGljObj.currentOprParent === 1 ? data.length : data.length + 10);
        }
        let checkBoxType = new GC.Spread.Sheets.CellTypes.CheckBox();
        for (var col = 0; col < setting.header.length; col++) {
            var hAlign = "left", vAlign = "center";
            if (setting.header[col].hAlign) {
                hAlign = setting.header[col].hAlign;
            } else if (setting.header[col].dataType !== "String"){
                hAlign = "right";
            }
            vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign;
            me.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign);
            if (setting.header[col].formatter) {
                sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
            }
            for (var row = 0; row < data.length; row++) {
                if(obj.pricePropertyCols.includes(col)){
                    let price = data[row]['priceProperty'] && data[row]['priceProperty'][obj.colMapping.colToField[col]] !== undefined
                        && data[row]['priceProperty'][obj.colMapping.colToField[col]] !== null
                        ? data[row]['priceProperty'][obj.colMapping.colToField[col]] : '';
                    sheet.setValue(row, col, price);
                }
                else if(obj.consumeAmtPropertyCols && obj.consumeAmtPropertyCols.includes(col)){
                    let consumeAmt = data[row]['consumeAmtProperty'] && data[row]['consumeAmtProperty'][obj.colMapping.colToField[col]] !== undefined
                    && data[row]['consumeAmtProperty'][obj.colMapping.colToField[col]] !== null
                        ? data[row]['consumeAmtProperty'][obj.colMapping.colToField[col]] : '';
                    sheet.setValue(row, col, consumeAmt);
                }
                else if(setting.header[col].dataCode === 'gljType' && data[row].gljType){
                    let distTypeVal =  distTypeTree.distTypes[distTypeTree.prefix + data[row].gljType].data.fullName;
                    sheet.setValue(row, col, distTypeVal, ch);
                }
                else if(setting.header[col].dataCode === 'materialType' && data[row].materialType){
                    sheet.setValue(row, col, materialTypeIdx[data[row].materialType]);
                }
                else if(setting.header[col].dataCode === 'model' && data[row].model){
                    sheet.setValue(row, col, machineModelIdx[data[row].model]);
                }
                else if(setting.header[col].dataCode === 'select'){
                    if(data[row].isChecked === true){
                        sheet.getCell(row, col).value(1);
                    }
                }
                else {
                    sheet.setValue(row, col, data[row][setting.header[col].dataCode], ch);
                    sheet.setTag(row, 0, data[row].ID, ch);
                }
                //复选框
                if(setting.header[col].dataCode === 'isComplementary'){
                    sheet.setCellType(row, col, checkBoxType);
                    sheet.getCell(row, col).value(1);
                }
                //新增组成物表，选择复选框
                if(setting.header[col].dataCode === 'select'){
                    sheet.setCellType(row, col, checkBoxType)
                    if(data[row].isChecked === true){
                        sheet.getCell(row, col).value(1);
                    }
                }
            }
        }
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    combineRowData: function(sheet, setting, row, repositoryGljObj) {
        let me = this;
        var rst = {priceProperty: {}};
        let gljTypeCombo = sheet.getCellType(row, repositoryGljObj.colMapping.fieldToCol['gljType']);
        let items = gljTypeCombo.items();
        let materialTypeCombo = sheet.getCellType(row, repositoryGljObj.colMapping.fieldToCol['materialType']);
        let machineItems = sheet.getCellType(row, repositoryGljObj.colMapping.fieldToCol['model']).items();
        let codeCol = 0;
        for (var col = 0; col < setting.header.length; col++) {
            if(repositoryGljObj.pricePropertyCols.includes(col)){
                v = sheet.getValue(row, col);
                rst.priceProperty[setting.header[col].dataCode] = v && v !== '' ? v : 0;
            }
            else if(setting.header[col].dataCode === 'gljType'){
                items.forEach(function(item){
                    if(sheet.getValue(row, col) === item.text){
                        rst[setting.header[col].dataCode] = item.value;
                        if(repositoryGljObj){
                            rst.shortName = repositoryGljObj.distTypeTree.distTypes[repositoryGljObj.distTypeTree.prefix + item.value].data.shortName;
                        }
                    }
                });
            }
            else if(setting.header[col].dataCode === 'model'){
                machineItems.forEach(function(item){
                    if(sheet.getValue(row, col) === item.text){
                        rst[setting.header[col].dataCode] = item.value;
                    }
                });
            }
            else if (setting.header[col].dataCode === 'code'){
                codeCol = col;
                if(repositoryGljObj){
                    let gljList = repositoryGljObj.gljList,
                        orgCode = repositoryGljObj.orgCode,
                        isExist = false;
                    for(let i=0; i< gljList.length; i++){
                        if(gljList[i].code === sheet.getValue(row, col) && sheet.getValue(row, col)!== orgCode){
                            $('#alertText').text("输入的编号已存在，请重新输入!");
                            $('#codeAlertBtn').click();
                            $('#codAleConfBtn').click(function () {
                                sheet.setValue(row, codeCol, orgCode);
                                sheet.getCell(row, codeCol).formatter("@");
                                sheet.setActiveCell(row, codeCol);
                            });
                            $('#codAleClose').click(function () {
                                sheet.setValue(row, codeCol, orgCode);
                                sheet.getCell(row, codeCol).formatter("@");
                                sheet.setActiveCell(row, codeCol);
                            });
                            isExist = true
                        }
                    }
                    if(!isExist){
                        rst[setting.header[col].dataCode] = sheet.getValue(row, col);
                    }
                }
                else{
                    rst[setting.header[col].dataCode] = sheet.getValue(row, col);
                }
            }
            else{
                rst[setting.header[col].dataCode] = sheet.getValue(row, col);
            }
        }
        return rst;
    },
    /* combineRowData: function(sheet, setting, row, repositoryGljObj) {
        let me = this;
        var rst = {};
        const gljTypeCol = setting.header.findIndex(item => item.dataCode === 'gljType');
        const modelCol = setting.header.findIndex(item => item.dataCode === 'model');
        let comboBoxCellType = sheet.getCellType(row, gljTypeCol);
        let items = comboBoxCellType.items();
        let machineItems = sheet.getCellType(row, modelCol).items();
        for (var col = 0; col < setting.header.length; col++) {
            if(setting.header[col].dataCode === 'gljType'){
                items.forEach(function(item){
                    if(sheet.getValue(row, col) === item.text){
                        rst[setting.header[col].dataCode] = item.value;
                        if(repositoryGljObj){
                            rst.shortName = repositoryGljObj.distTypeTree.distTypes[repositoryGljObj.distTypeTree.prefix + item.value].data.shortName;
                        }
                    }
                });
            }
            else if(setting.header[col].dataCode === 'model'){
                machineItems.forEach(function(item){
                    if(sheet.getValue(row, col) === item.text){
                        rst[setting.header[col].dataCode] = item.value;
                    }
                });
            }
            else if (setting.header[col].dataCode === 'code'){
                if(repositoryGljObj){
                    let stdGljList = repositoryGljObj.stdGljList,
                        complementaryGljList = repositoryGljObj.complementaryGljList,
                        orgCode = repositoryGljObj.orgCode,
                        isExist = false;
                    for(let i=0; i< stdGljList.length; i++){
                        if(stdGljList[i].code == sheet.getValue(row, col) && sheet.getValue(row, col)!== orgCode){
                            $('#alertText').text("输入的编号已存在，请重新输入!");
                            $('#codeAlertBtn').click();
                            $('#codAleConfBtn').click(function () {
                                sheet.getCell(row, 0).formatter("@");
                                sheet.getCell(row, 0).text("");
                                sheet.setActiveCell(row, 0);
                            });
                            $('#codAleClose').click(function () {
                                sheet.getCell(row, 0).formatter("@");
                                sheet.getCell(row, 0).text("");
                                sheet.setActiveCell(row, 0);
                            });
                            isExist = true
                            break;
                        }
                    }
                    if(!isExist){
                        for(let i=0; i< complementaryGljList.length; i++){
                            if(complementaryGljList[i].code == sheet.getValue(row, col) && sheet.getValue(row, col)!== orgCode){
                                $('#alertText').text("输入的编号已存在，请重新输入!");
                                $('#codeAlertBtn').click();
                                $('#codAleConfBtn').click(function () {
                                    sheet.getCell(row, 0).formatter("@");
                                    sheet.getCell(row, 0).text("");
                                    sheet.setActiveCell(row, 0);
                                });
                                $('#codAleClose').click(function () {
                                    sheet.getCell(row, 0).formatter("@");
                                    sheet.getCell(row, 0).text("");
                                    sheet.setActiveCell(row, 0);
                                });
                                isExist = true
                                break;
                            }
                        }
                    }
                    if(!isExist){
                        rst[setting.header[col].dataCode] = sheet.getValue(row, col);
                    }
                }
                else{
                    rst[setting.header[col].dataCode] = sheet.getValue(row, col);
                }
            }
            else{
                rst[setting.header[col].dataCode] = sheet.getValue(row, col);
            }
        }
        return rst;
    }, */
    combineRationRowData: function(sheet, setting, row) {
        var rst = {};
        for (var col = 0; col < setting.header.length; col++) {
            rst[setting.header[col].dataCode] = sheet.getValue(row, col);
        }
        return rst;
    },
    shieldAllCells: function(sheet) {
        sheet.options.isProtected = true;
    },
    unShieldAllCells: function(sheet) {
        sheet.options.isProtected = false;
    },
    reLockSomeCodes: function (sheet, beginRow, endRow) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.locked = false;
        sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
        let unLockStyle = new GC.Spread.Sheets.Style();
        unLockStyle.locked = false;
        let lockStyle = new GC.Spread.Sheets.Style();
        lockStyle.locked = true;
        for(let row = beginRow; row < endRow; row++){
            sheet.setStyle(row, 0, lockStyle);
        }
        for(let row = endRow; row < sheet.getRowCount(); row++){
            sheet.setStyle(row, 0, unLockStyle);
        }
        sheet.options.isProtected = true;
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    unLockAllCells: function (sheet) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.locked = false;
        sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
        sheet.setStyle(-1, 0, defaultStyle);
        sheet.options.isProtected = false;
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    lockAllCells: function (sheet) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.locked = true;
        sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
        for(let i = 0; i < sheet.getRowCount(); i++){
            sheet.setStyle(i, 0, defaultStyle);
        }
        sheet.options.isProtected = true;
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    lockSomeCodes: function (sheet, beginRow, endRow) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.locked = false;
        sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
        let style = new  GC.Spread.Sheets.Style();
        style.locked = true;
        for(let i = beginRow; i < endRow; i++){
            sheet.setStyle(i, 0, style);
        }
        sheet.options.isProtected = true;
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    setLockCols: function (sheet, cols, locked) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        for(let i = 0, len = cols.length; i < len; i++){
            for(let row = 0, rLen = sheet.getRowCount(); row < rLen; row++){
                sheet.getCell(row, cols[i]).locked(locked);
            }
        }
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    lockCodeCells: function (sheet, rowCount) {
        let sheetRowCount = sheet.getRowCount();
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.locked = false;
        sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
        let style = new GC.Spread.Sheets.Style();
        style.locked = true;
        sheet.setStyle(-1, 0, style);
        sheet.suspendPaint();
        sheet.suspendEvent();
        for(let i =rowCount; i<sheetRowCount; i++){
            sheet.setStyle(i, -1, style);
        }
        sheet.options.isProtected = true;
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    lockCells: function(sheet, setting){
        if (setting && setting.view.lockColumns && setting.view.lockColumns.length > 0) {
            sheet.options.isProtected = true;
            sheet.getRange(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport).locked(false);
            for (var i = 0; i < setting.view.lockColumns.length; i++) {
                sheet.getRange(-1,setting.view.lockColumns[i] , -1, 1, GC.Spread.Sheets.SheetArea.viewport).locked(true);
            }
        }
    },
    chkIfEmpty: function(rObj, setting) {
        var rst = true;
        if (rObj) {
            for (var i = 0; i < setting.header.length; i++) {
                if (rObj[setting.header[i].dataCode]) {
                    rst = false;
                    break;
                }
            }
        }
        return rst;
    }
}