var headerCount;//头部行高
var nextDataRowIndex;//下条数据所在行
// var categoryCodeCol=31;
var categoryBackColorArray=['rgb(180,198,231)','#FFE699','rgb(169,208,142)'];//0 工程 1保洁 2办公
var priceAmountBackColor='yellow';//后两列颜色
$(function(){
    spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
    initSpread();
    // setDoubleCheck(spread);
});

function initSpread(){
    spread.suspendPaint();

    if(dbJson){//数据库
        spread.fromJSON(dbJson);
    }
    var sheet = spread.getActiveSheet();
    changeRightSum(sheet);
    removeMenu(sheet);

    if(readOnly) {
        setSheetReadOnly(sheet);
        spread.resumePaint();
        return;
    }else {
        setInstDefault(spread);
    }
    addMenu(sheet);
    spread.options.newTabVisible = false;//取消新增sheet按钮
    spread.options.allowExtendPasteRange =true;//扩展粘贴区域
    // bindEvent(spread);

    setDefaultLock(sheet);
    setCellEdit(sheet);
    var setting = sheet.tag();
    if(setting){
        if(setting.sheetVar) {
            headerCount = setting.sheetVar.headerCount;
        }
        if(setting.nextDataRowIndex){
            nextDataRowIndex = setting.nextDataRowIndex;
        }
    }
    if(!nextDataRowIndex){
        nextDataRowIndex = headerCount;
    }

    spread.bind(GC.Spread.Sheets.Events.CellChanged, function (e, info) {
        if(info.sheetArea === GC.Spread.Sheets.SheetArea.viewport){
            var sheet =info.sheet;
            var row = info.row;
            var col = info.col;
            if(info.propertyName=='value'){//值变化
                changeRightSum(sheet);
            }
        }
    });
    spread.bind(GC.Spread.Sheets.Events.RowChanging, function (e, info) {
        //info.propertyName="deleteRows" //删除行
        if(info.propertyName="deleteRows"){
            var deleteRow = info.row;
            var deleteCount = info.count;
            var sheetTag = sheet.tag();
            var ids = sheetTag.ids;
            for(var i=deleteRow;i<deleteRow+deleteCount;i++) {
                if(nextDataRowIndex>headerCount) nextDataRowIndex--;
                var rowTag = sheet.getTag(i,-1);
                if(!rowTag) continue;
                for(var j=0;j<ids.length;j++){
                    if(ids[j]==rowTag.id){
                        delete  ids[j];
                    }
                }
            }
            addSheetTagValue(sheet,{ids:ids,nextDataRowIndex:nextDataRowIndex});
        }
    });

    if(ordersKindType=='m') {//耗材类
       /* var spreadNS = GC.Spread.Sheets;
        var cfs = sheet.conditionalFormats;
        var dataBarRule2 = new spreadNS.ConditionalFormatting.DataBarRule();
        dataBarRule2.minType(spreadNS.ConditionalFormatting.ScaleValueType.automin);
        dataBarRule2.maxType(spreadNS.ConditionalFormatting.ScaleValueType.automax);
        dataBarRule2.ranges([new spreadNS.Range(-1, 8, -1, 1)]);
        dataBarRule2.color("orange").showBorder(true).borderColor("rgb(0,138,239)");
        cfs.addRule(dataBarRule2);*/
    }
    spread.resumePaint();
}

function setDefaultLock(sheet){
    var option = {
        allowSelectLockedCells:true,
        allowSelectUnlockedCells:true,
        allowFilter: true,
        allowSort: false,
        allowResizeRows: true,
        allowResizeColumns: true,
        allowEditObjects: false,
        allowDragInsertRows: false,
        allowDragInsertColumns: false,
        allowInsertRows: false,
        allowInsertColumns: false,
        allowDeleteRows: true,
        allowDeleteColumns: false
    };
    sheet.options.protectionOptions = option;
}

function setCellEdit(sheet){
    var rowCount = sheet.getRowCount();
    var colCount = sheet.getColumnCount();
    for(var i=0;i<rowCount;i++){
        for(var j=0;j<colCount;j++){
            // dataCell
            var tag = sheet.getTag(i,j);
            if(tag){
                if(tag.dataCell){
                    if(!tag.readOnly || tag.readOnly==0){
                        sheet.getRange(i,j,1,1).locked(false);
                    }
                }
            }
        }
    }
}
function addOne(){
    if(ordersKindType=='m'||ordersKindType=='dizhi'||ordersKindType=='dcg'){
        addGoods();
    }else if(ordersKindType=='gzf'){
        addClothing();
    }
}
function addGoods(){//单个加
    var url="budgetDataController.do?selectReferGoods4Budget" +
        "&classname=cn.com.lzt.goodsinfo.entity.TBGoodsInfoEntity" +
        "&codefield=goodsCode" +
        "&categoryfield=belongCategoryCode" +
        "&categoryNamefield=belongCategoryName" +
        "&namefield=goodsName" +
        "&purchaseUnit=purchaseUnit" +
        "&specification=specification" +
        "&goodsDesc=goodsDesc" +
        "&strEmpty=strEmpty" +
        "&supplierName=defaultProvider"
        +"&initCategoryCode="+ordersCategoryCode;
    _openDialog(url,'选择货品',function(iframe){
        spread.suspendPaint();
        var selected = iframe.getSelectRows();
        for(var i=0;i<selected.length;i++){
            var one =selected[i];
            if(ordersKindType=='m'){
                $.ajax({
                    url: 'budgetInstController.do?getOneGoodsUesdCount'
                    ,data:{goodsId:one.id,year:refYear,entityId:entityId}
                    ,dataType:'json'
                    ,type:'POST'
                    ,async:false
                    ,success:function (d) {
                        if(d.success){
                            one.refYearCountArray=d.obj;
                        }
                    }
                });
            }
            addRow(one);
        }
        fitColumn();
        spread.resumePaint();
    },'800px','600px');
}

function addClothing(){
    var url="budgetDataController.do?selectClothing";
    _openDialog(url,'选择工作服',function(iframe){
        spread.suspendPaint();
        var selected = iframe.getSelectRows();
        for(var i=0;i<selected.length;i++){
            addRow(selected[i]);
        }
        fitColumn();
        spread.resumePaint();
    },'800px','600px');
}


function addRefYearGoods() {//加去年使用的
    var url ='budgetInstController.do?getUsedGoods';
  /*  var layerIndex = layer.msg('载入中，请稍候···', {
        time : 3000*1000,
        shade: 0.1});*/
    _alert('载入货品可能需要2-3分钟，请耐心等待',function(){
        $.ajax({
            url: url
            ,data:{initCategoryCode:ordersCategoryCode,year:refYear,entityId:entityId}
            ,dataType:'json'
            ,type:'POST'
            ,async:false
            ,success:function (d) {
                if(d.success){
                    var obj = d.obj;
                    spread.suspendPaint();
                    for(var i=0;i<obj.length;i++){
                        // $('div.layui-layer-content').html('载入中('+(i+1)+'/'+obj.length+')，请稍候···');
                        addRow(obj[i]);
                        // if(i==20) break;
                    }
                    fitColumn();
                    spread.resumePaint();
                    _alert('载入成功');
                }
            }
        });
    });
    // layer.close(layerIndex);
}

function traceTime(step){
    if(window._lastStepTime){
        var current = new Date().getTime();
        console.log(step+":"+(current-window._lastStepTime));
        window._lastStepTime=current;
    }else {
        window._lastStepTime = new Date().getTime();
    }
}

function getDoubleValidator() {
    return GC.Spread.Sheets.DataValidation.createFormulaValidator('^(-?\d+)(\.\d+)?$');
}

function addRow(one){
    var sheet = spread.getActiveSheet();
    var sheetTag = sheet.tag();
    if(!sheetTag) sheetTag={};
    if(!sheetTag.ids) sheetTag.ids=[];
    var id = one.id;
    if($.inArray(id, sheetTag.ids)!=-1){
        return;
    }
    sheetTag.ids.push(id);
    addSheetTagValue(sheet,{ids:sheetTag.ids});
    var row = nextDataRowIndex++;
    var sheetRowCount = sheet.getRowCount();
    if(row>sheetRowCount-1){
        sheet.addRows(sheetRowCount,1)
    }
    var unLockedColArray = [];
    var rowSetting={};//行变量

    if(ordersKindType=='m') {//消耗类
        sheet.setFormula(row, 0, "row()-"+headerCount);
        // sheet.setValue(row, 0, row-headerCount+1);//序号
        sheet.setValue(row, 1, one.goodsName);//品名
        sheet.setValue(row, 2, one.specification);//型号
        sheet.setValue(row, 3, typeList[one.purchaseUnit]);//单位
        sheet.setValue(row, 4, one.costPrice);//单价
        var priceColumnString = getCellStr(sheet, row, 4);//单价列
        var startIndex = 9;
        var allNumCol = '';
        var allRefNumCol='';
        for (var i = 0; i < 6; i++) {
            var refYearColIndex = startIndex + i * 3;//往年数量列
            var numIndex = refYearColIndex+1;//数量列
            var amountIndex = numIndex + 1;//金额列
            // var dv = getDoubleValidator();
            // sheet.getCell(row,numIndex).validator(dv);
            unLockedColArray.push(numIndex);
            var refYearColString = getCellStr(sheet, row, refYearColIndex);
            var numColString = getCellStr(sheet, row, numIndex);
            if(one.refYearCountArray){
                var refMonthCount = one.refYearCountArray[i];
                sheet.setValue(row,refYearColIndex,refMonthCount);
            }
            sheet.setFormula(row, amountIndex, numColString + "*" + priceColumnString);
            if (i > 0) {
                allNumCol += ',';
                allRefNumCol+=',';
            }
            allNumCol += numColString;
            allRefNumCol+=refYearColString;
        }
        // 5往年汇总 6年度数量 7年度金额 8数量差
        sheet.setFormula(row, 5, "sum(" + allRefNumCol + ")");
        unLockedColArray.push(6);
        sheet.setFormula(row, 6, "sum(" + allNumCol + ")");
        var c5String = getCellStr(sheet, row, 5);
        var c6String = getCellStr(sheet, row, 6);
        sheet.setFormula(row, 7, c6String+"*"+priceColumnString);
        sheet.setFormula(row, 8, c6String+"-"+c5String);
        rowSetting.categoryCode=one.belongCategoryCode;
    }else if(ordersKindType=='dizhi') { //低值、固定资产
        sheet.setFormula(row, 0, "row()-"+headerCount);
        // sheet.setValue(row, 0, row-headerCount+1);//序号
        sheet.setValue(row, 1, one.belongCategoryName);//类别
        sheet.setValue(row, 2, one.goodsName);//品名
        sheet.setValue(row, 3, one.brand);//品牌
        sheet.setValue(row, 4, one.specification);//型号
        sheet.setValue(row, 5, typeList[one.purchaseUnit]);//单位
        sheet.setValue(row, 6, one.costPrice);//单价
        var priceColumnString = getCellStr(sheet, row, 6);//单价列

        var startIndex = 7;
        var allNumCol = '';
        var numIndex = startIndex;
        for (var i = 0; i < 12; i++) {
            var amountIndex = numIndex + 1;//金额列
            // sheet.getRange(row, numIndex).locked(false);
            unLockedColArray.push(numIndex);
            var numColString = getCellStr(sheet, row, numIndex);
            sheet.setFormula(row, amountIndex, numColString + "*" + priceColumnString);
            if (i > 0) {
                allNumCol += ',';
            }
            allNumCol += numColString;
            numIndex+=2;
        }
        //数量
        sheet.setFormula(row, numIndex, "sum(" + allNumCol + ")");
        unLockedColArray.push(numIndex);
        var totalNumString = getCellStr(sheet, row, numIndex);
        numIndex++;
        //金额
        sheet.setFormula(row, numIndex,totalNumString+"*"+ priceColumnString);
        numIndex++;
        if(one.belongCategoryCode){
            sheet.setValue(row,numIndex,one.belongCategoryCode);
        }
        if(ordersKind=='rootA04') {//固定资产
            sheet.setValue(row,numIndex+1,one.propertytype);
        }

        rowSetting.categoryCode=one.belongCategoryCode;
    }else if(ordersKindType=='dcg') { //代采购
        sheet.setFormula(row, 0, "row()-"+headerCount);
        // sheet.setValue(row, 0, row-headerCount+1);//序号
        sheet.setValue(row, 1, one.goodsName);//维保名称
        sheet.setValue(row, 2, typeList[one.purchaseUnit]);//单位
        sheet.setValue(row, 3, one.costPrice);//单价
        var priceColumnString = getCellStr(sheet, row, 3);//单价列

        var startIndex = 4;
        var allNumCol = '';
        var numIndex = startIndex;
        for (var i = 0; i < 12; i++) {
            var amountIndex = numIndex + 1;//金额列
            // sheet.getRange(row, numIndex).locked(false);
            unLockedColArray.push(numIndex);
            var numColString = getCellStr(sheet, row, numIndex);
            sheet.setFormula(row, amountIndex, numColString + "*" + priceColumnString);
            if (i > 0) {
                allNumCol += ',';
            }
            allNumCol += numColString;
            numIndex+=2;
        }
        //数量
        sheet.setFormula(row, numIndex, "sum(" + allNumCol + ")");
        var totalNumString = getCellStr(sheet, row, numIndex);
        unLockedColArray.push(numIndex);
        numIndex++;
        //金额
        sheet.setFormula(row, numIndex++,totalNumString+"*"+ priceColumnString);
        var oneLevelCode = '';
        if(one.belongCategoryCode){
            oneLevelCode=one.belongCategoryCode.substr(0,7);
            sheet.setValue(row,numIndex++,oneLevelCode);
        }
        rowSetting.categoryCode=one.belongCategoryCode;
    }else if(ordersKindType=='gzf') { //工作服
        sheet.setFormula(row, 0, "row()-"+headerCount);
        // sheet.setValue(row, 0, row-headerCount+1);//序号
        sheet.setValue(row, 1,clothingusertypeList[one.clothingusertype]);//类别
        sheet.setValue(row, 2, one.name); // 品名
        sheet.setValue(row, 3, typeList[one.unit]);//单位
        var price = one.price;
        if(!price){
            price=0;
        }
        sheet.setValue(row, 4, price);//单价
        var priceColumnString = getCellStr(sheet, row, 4);//单价列

        var startIndex = 5;
        var allNumCol = '';
        var numIndex = startIndex;
        for (var i = 0; i < 12; i++) {
            var amountIndex = numIndex + 1;//金额列
            // sheet.getRange(row, numIndex).locked(false);
            unLockedColArray.push(numIndex);
            var numColString = getCellStr(sheet, row, numIndex);
            sheet.setFormula(row, amountIndex, numColString + "*" + priceColumnString);
            if (i > 0) {
                allNumCol += ',';
            }
            allNumCol += numColString;
            numIndex+=2;
        }
        //数量
        sheet.setFormula(row, numIndex, "sum(" + allNumCol + ")");
        unLockedColArray.push(numIndex);
        var totalNumString = getCellStr(sheet, row, numIndex);
        numIndex++;
        //合计
        sheet.setFormula(row, numIndex,totalNumString+"*"+ priceColumnString);
    }

    var columnCount = sheet.getColumnCount();
    var styleRowIndex = headerCount-1;
    for(var ci=0;ci<columnCount;ci++) {
        var styleColStyle=sheet.getStyle(styleRowIndex,ci);
        if(styleColStyle){
            sheet.setStyle(row,ci,styleColStyle);
        }
        var styleColFormatter = sheet.getFormatter(styleRowIndex,ci);
        if(styleColFormatter){
            sheet.setFormatter(row,ci,styleColFormatter);
        }
        if($.inArray(ci,unLockedColArray)!=-1){
            sheet.getCell(row,ci).locked(false);
        }
    }
    //边框
    /*var lineStyle = GC.Spread.Sheets.LineStyle.thin;
    var lineBorder = new GC.Spread.Sheets.LineBorder('black', lineStyle);
    var sheetArea = GC.Spread.Sheets.SheetArea.viewport;
    var columnCount = sheet.getColumnCount();
    for(var col=0;col<columnCount;col++) {
        sheet.getRange(row,col).setBorder(lineBorder, { left: true, right: true,top:true,bottom:true}, sheetArea);
    }*/
    // sheet.getCell(row,1).text(one.costPrice);
    addSheetTagValue(sheet,{nextDataRowIndex:nextDataRowIndex});
    rowSetting.id=id;
    addCellTagValue(sheet,row,-1,rowSetting);
}

var changeSumTimer;
function changeRightSum(sheet){
    return;
    if(changeSumTimer){
        clearTimeout(changeSumTimer);
    }
    changeSumTimer=setTimeout(_changeSum,200);
    function _changeSum() {
        var categoryTotalCol = 32;
        var rowIndex =0;
        while(true){
            var cell = sheet.getCell(rowIndex,categoryTotalCol);
            var cs =cell.tag();
            if(!cs) break;
            var code = cs.categoryCode;
            var value =cell.value();
            value=parseFloat(value).toFixed(2);
            $('#'+code).val(value);
            categoryTotalCol++;
        }
        setEchart();
        changeSumTimer=null;
    }
}

function setEchart(){
    var option = {
        title: {
            text: '',
            subtext: '',
            left: 'center'
        },
        tooltip: {
            trigger: 'item',
            formatter: '{a} <br/>{b} : {c} ({d}%)'
        },
        legend: {
            orient: 'horizontal',
            left: 'left',
            data: []
        },
        series: [
            {
                name: '',
                type: 'pie',
                radius: '55%',
                center: ['50%', '60%'],
                data: [],
                emphasis: {
                    itemStyle: {
                        shadowBlur: 10,
                        shadowOffsetX: 0,
                        shadowColor: 'rgba(0, 0, 0, 0.5)'
                    }
                }
            }
        ]
    };

    var categoryItems = [];
    var categoryItemData = [];
    var inputs = $('#cateSumTable input');
    for (var i = 0; i < inputs.length; i++) {
        var t = $(inputs[i]);
        var name = t.attr('categoryName');
        var v = t.val();
        categoryItems.push(name);
        var d = {};
        d.name = name;
        d.value = v;
        categoryItemData.push(d);
    }
    option.legend.data = categoryItems;
    option.series[0].data = categoryItemData;
    var myChart = echarts.init(document.getElementById('e1'));
    myChart.setOption(option);
}

function fitColumn(){
    var sheet = spread.getActiveSheet();
    sheet.autoFitColumn(0);sheet.autoFitColumn(1);sheet.autoFitColumn(2);
}

function removeMenu(){
    for(var i=0;i< spread.contextMenu.menuData.length;i++){
        var menu = spread.contextMenu.menuData[i];
        var text=menu.text;
        if(text=='清除' || text=='插入'|| text=='隐藏'|| text=='显示' ){//去掉过滤、排序
            delete spread.contextMenu.menuData[i];
         }
    }
}

function addMenu(){
    if(ordersKindType=='m') {//耗材类
        var oneRowHistoryMenu ={text: '历史详情',name: 'oneRowHistory',workArea: 'viewport',command:oneRowHistory};
        spread.contextMenu.menuData.push(oneRowHistoryMenu);
    }
    spread.contextMenu.onOpenMenu =function(menuData, itemsDataForShown, hitInfo, spread){
        //itemsDataForShown 实际工作菜单 可以删 不影响其他显示
        var info =hitInfo.worksheetHitInfo;
        if(info){
            var row =info.row;
            var col = info.col ;
        }
    }
}

function oneRowHistory(spread){
    var sheet = spread.getActiveSheet();
    var selections = sheet.getSelections();
    var select = selections[0];
    var row = select.row;
    var col = select.col;
    var rowSetting = sheet.getTag(row,-1);
    if(rowSetting){
        var url = 'budgetInstController.do?goOneHistry';
        var goods=rowSetting.id;
        var name = sheet.getValue(row,1);
        var unit=sheet.getValue(row,3);
        var d1=sheet.getValue(row,9);
        var v1=sheet.getValue(row,10);
        var d2=sheet.getValue(row,12);
        var v2=sheet.getValue(row,13);
        var d3=sheet.getValue(row,15);
        var v3=sheet.getValue(row,16);
        var d4=sheet.getValue(row,18);
        var v4=sheet.getValue(row,19);
        var d5=sheet.getValue(row,21);
        var v5=sheet.getValue(row,22);
        var d6=sheet.getValue(row,24);
        var v6=sheet.getValue(row,25);
        url +="&unit="+unit+"&refYear="+refYear+"&name="+name
             +"&d1="+d1+"&d2="+d2+"&d3="+d3+"&d4="+d4+"&d5="+d5+"&d6="+d6
             +"&v1="+v1+"&v2="+v2+"&v3="+v3+"&v4="+v4+"&v5="+v5+"&v6="+v6
            ;
        createdetailwindow('历史详情',url,'100%','100%');
    }
}


function bindCheckNum(spread) {
    spread.bind(GC.Spread.Sheets.Events.EditEnding, function (sender, args) {
        var sheet = args.sheet;
        var row=args.row;
        var col=args.col;
        var cellTag = sheet.getTag(row,col);
        if(cellTag) {
            if(cellTag.datatype!='txt'){
                var value =args.editingText;
                var reg=/^(-?\d+)(\.\d+)?$/;
                if(value){
                    if(value.indexOf('=')!=0) {
                        if (!reg.test(value)) {
                            args.cancel = true;
                        }
                    }
                }
            }
        }
    });
}


function createAlignRightStyle(locked){
    var style = new GC.Spread.Sheets.Style();
    style.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
    style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    style.locked=locked;
    return style;
}