table-report-sheet.js 19.4 KB
Newer Older
kevin's avatar
kevin committed
1 2 3 4 5 6 7 8
analysisModule.directive('tableReportSheet', ['$rootScope', '$log', '$timeout', '$q', 'templateService', 'enums', 'keyValueDataService', 'spreadJsTipService',
    '$interval', 'vatSessionService',
    function ($rootScope, $log, $timeout, $q, templateService, enums, keyValueDataService, spreadJsTipService, $interval, vatSessionService) {
        'use strict';
        $log.debug('tableReportSheet.ctor()...');

        return {
            restrict: 'E',
Memorydoc's avatar
#  
Memorydoc committed
9
            template: '<div class="report" style="height: 100%;width: 100%;"></div>',
kevin's avatar
kevin committed
10 11 12 13 14 15 16 17 18 19 20 21
            replace: true,
            scope: {
                templateId: '=',
                spread: '=',
                reportSource: '=',
                formulaBlocks: '=',
                manualDataSources: '=',
                isReadOnly: '=',
                serviceType: '=',
                initRow: '=?',
                initCol: '=?',
                reportApi: '=?', // In: onCellClick, onCellDoubleClick; Out: refreshReport,
kevin's avatar
#  
kevin committed
22
                relation: '='
kevin's avatar
kevin committed
23 24 25 26 27 28 29 30 31 32 33 34 35 36
            },
            link: function (scope, element) {
                $log.debug('vatReportSheet.link()...');

                // Register api functions in internalApi
                // If selectorApi passed is not undefined, we will use it as internalApi
                scope.internalApi = scope.reportApi || {};

                scope.projectYear = vatSessionService.year;
                scope.projectPeriod = scope.serviceType === enums.serviceType.VAT ? vatSessionService.month : 0;

                var profileList = ["EBIT考虑资产减值损失", "加:特殊因素考虑", "EBIT(考虑特殊因素)", "EBIT rate", "关联交易金额", "6%增值税", "价税合计金额"];
                // 首次进入页面时,通过promise模式保证在加载完成后再调用locateCell以focus在特定单元格
                // 如果是在当前页面要focus在特定单元格,通过watch行列号来完成
kevin's avatar
#  
kevin committed
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
                /*      scope.$watchGroup(['initRow', 'initCol'], function (newVal, oldVal) {
                          if (_.isNumber(scope.initRow) && _.isNumber(scope.initCol)) {
                              var spreadCtrl = getSpreadControl();
                              if (spreadCtrl) {
                                  locateCell(spreadCtrl);
                              }
                              spreadCtrl = null;
                          }
                      });*/

                /*  var locateCell = function (spread, forceCloseModal) {
                      if (_.isNumber(scope.initRow) && _.isNumber(scope.initCol)) {
                          var sheet;
                          if (constant.regesterInformation.active) {
                              sheet = spread.getActiveSheet();
                          }
                          else {
                              sheet = spread.getSheet(1);
                          }

                          //var sheet = spread.getActiveSheet();
                          //var sheet = spread.getSheet(1);

                          sheet.setSelection(scope.initRow, scope.initCol, 1, 1);
                          if (_.isFunction(scope.internalApi.onCellClick) && !forceCloseModal) {
                              scope.internalApi.onCellClick({
                                  'args': {row: scope.initRow, col: scope.initCol},
                                  'data': JSON.parse(sheet.getTag(scope.initRow, scope.initCol))
                              });
                          }

                          $log.debug(sheet.getTag(scope.initRow, scope.initCol));

                          scope.initRow = null;
                          scope.initCol = null;
                          sheet = null;
                          return $q.when();
                      }
                  };*/

Memorydoc's avatar
#  
Memorydoc committed
77
               /* scope.$watchGroup(['relation.period', 'relation.orgId', 'relation.data'], function (newValue, oldValue) {
kevin's avatar
#  
kevin committed
78
                    if (scope.relation.orgId != null && scope.relation.period != null) {
Memorydoc's avatar
#  
Memorydoc committed
79
                      /!*  setTimeout(function () {
kevin's avatar
#  
kevin committed
80
                            if ((scope.relation.broadcast && scope.relation.broadcast == true) || scope.spread != undefined) {
kevin's avatar
#  
kevin committed
81
                                loadSheet(scope.templateId, false)
kevin's avatar
#  
kevin committed
82 83
                            } else {
                                loadSheet(scope.templateId, true);
kevin's avatar
#  
kevin committed
84
                            }
Memorydoc's avatar
#  
Memorydoc committed
85
                        }, 200);*!/
kevin's avatar
#  
kevin committed
86
                        loadSheet(scope.templateId);
kevin's avatar
kevin committed
87 88
                    }
                });
kevin's avatar
#  
kevin committed
89 90
                scope.$watch('relation.broadcast', function (newValue, oldValue) {
                    if (scope.relation.orgId != null && scope.relation.period != null) {
kevin's avatar
#  
kevin committed
91 92
                       // loadSheet(scope.templateId, false)
                        loadSheet(scope.templateId)
kevin's avatar
#  
kevin committed
93
                    }
Memorydoc's avatar
#  
Memorydoc committed
94
                });*/
kevin's avatar
#  
kevin committed
95 96 97



Memorydoc's avatar
#  
Memorydoc committed
98
                var loadSheet = function (templateId ) {
kevin's avatar
kevin committed
99 100
                    //var prokjectId = vatSessionService.project.id;
                    //var period = vatSessionService.month;
kevin's avatar
#  
kevin committed
101
                  /*  if (!init) {
Memorydoc's avatar
#  
Memorydoc committed
102
                        scope.relation.addEbitRow();
kevin's avatar
#  
kevin committed
103 104
                        setData(init)
                        return;
kevin's avatar
#  
kevin committed
105 106 107
                    }*/
                    if(scope.relation.orgId == undefined){
                        scope.relation.orgId = null;
kevin's avatar
kevin committed
108
                    }
Memorydoc's avatar
#  
Memorydoc committed
109 110
                    if(templateId == undefined )
                        return;
kevin's avatar
#  
kevin committed
111
                    return templateService.getPeriodTemplateJsonByOrg(templateId, scope.relation.period, scope.relation.orgId).then(function (reportSpread) {
kevin's avatar
#  
kevin committed
112 113 114 115 116
                        var spreadCtrl = getSpreadControl();
                        if (spreadCtrl) {
                            spreadCtrl.destroy();
                        }
                        spreadCtrl = null;
kevin's avatar
#  
kevin committed
117 118
                        if (!_.isEmpty(reportSpread)) {
                            initSpreadExcel(reportSpread).then(function (spread) {
kevin's avatar
#  
kevin committed
119
                                /*  return locateCell(spread, true);*/
kevin's avatar
#  
kevin committed
120 121 122 123 124
                            });
                        }
                    }, function (data) {
                        $log.info(data);
                    });
kevin's avatar
kevin committed
125 126
                };

kevin's avatar
#  
kevin committed
127
                //初始化spread
kevin's avatar
#  
kevin committed
128
               /* var _init = function(){
kevin's avatar
#  
kevin committed
129 130
                    return templateService.getPeriodTemplateJsonByOrg(templateId, scope.relation.period, scope.relation.orgId).then(function (reportSpread) {
                        if (!_.isEmpty(reportSpread)) {
kevin's avatar
#  
kevin committed
131 132 133 134 135
                            var spreadCtrl = getSpreadControl();
                            if (spreadCtrl) {
                                spreadCtrl.destroy();
                            }
                            spreadCtrl = null;
kevin's avatar
#  
kevin committed
136
                            initSpreadExcel(reportSpread, true).then(function (spread) {
kevin's avatar
#  
kevin committed
137
                                /!*  return locateCell(spread, true);*!/
kevin's avatar
#  
kevin committed
138 139 140 141 142
                            });
                        }
                    }, function (data) {
                        $log.info(data);
                    });
kevin's avatar
#  
kevin committed
143
                }*/
kevin's avatar
#  
kevin committed
144

Memorydoc's avatar
#  
Memorydoc committed
145 146 147 148 149 150 151 152 153
               var setColWidth = function(sheet){
                   for(var i  = 0; i < sheet.getColumnCount(); i++){
                        if(i ==0){
                            sheet.setColumnWidth(0, 360)
                            continue
                        }
                       sheet.setColumnWidth(i, 180);
                   }
               }
Memorydoc's avatar
#  
Memorydoc committed
154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

               //创建自定义格式
                function CustomPerFormat() {

                }
                CustomPerFormat.prototype = new GC.Spread.Formatter.FormatterBase();
                CustomPerFormat.prototype.format = function (obj, formattedData) {
                    return perFormat(obj, formattedData);
                }
                function perFormat(value, formattedData){
                    value = value.toString();
                    if(0 <= value <= 100){
                           if( value.indexOf("%") != -1){
                               value += "%";
                           }
                    }else{
                        formattedData.conditionalForeColor = "red";
                    }
                    return value;
                }
kevin's avatar
#  
kevin committed
174
                var initSpreadExcel = function (reportSpread) {
kevin's avatar
kevin committed
175 176

                    var spread = getSpreadControl();
kevin's avatar
#  
kevin committed
177

kevin's avatar
kevin committed
178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209
                    spread.suspendPaint();
                    spread.fromJSON(reportSpread);
                    spread.options.showVerticalScrollbar = true;
                    spread.options.showHorizontalScrollbar = true;
                    spread.options.scrollbarMaxAlign = true;
                    spread.options.scrollbarShowMax = true;
                    spread.options.tabNavigationVisible = true;
                    if (constant.regesterInformation.active) {
                        spread.options.tabEditable = false;
                        spread.options.tabStripVisible = false;
                        spread.options.newTabVisible = false;
                    }
                    spread.options.allowUndo = false;
                    spread.options.allowUserResize = false;
                    spread.options.allowUserDragDrop = false;
                    spread.options.allowUserDragFill = false;
                    spread.options.allowUserEditFormula = false;
                    spread.options.allowContextMenu = false;
                    initInterFunction(spread);
                    //todo:注册之后这里去掉注释
                    //var sheet = spread.getActiveSheet();

                    var sheet;
                    if (constant.regesterInformation.active) {
                        sheet = spread.getActiveSheet();
                    }
                    else {
                        sheet = spread.getSheet(1);
                    }
                    if (sheet != null) {
                        sheet.options.rowHeaderVisible = true;
                        sheet.options.colHeaderVisible = true;
kevin's avatar
#  
kevin committed
210 211
                        sheet.options.gridline.showVerticalGridline = true;
                        sheet.options.gridline.showHorizontalGridline = true;
kevin's avatar
kevin committed
212 213 214 215
                        sheet.options.isProtected = true;
                        sheet.clearSelection();
                    }
                    scope.spread = spread;
Memorydoc's avatar
#  
Memorydoc committed
216
                    scope.relation.sheet = spread.getActiveSheet();
Memorydoc's avatar
#  
Memorydoc committed
217
                    setColWidth(sheet);
kevin's avatar
#  
kevin committed
218
                    if(sheet.getRowCount() <= 38)
Memorydoc's avatar
#  
Memorydoc committed
219
                        scope.relation.addEbitRow(sheet)
kevin's avatar
#  
kevin committed
220
                    if (scope.templateId && scope.reportSource) {
Memorydoc's avatar
#  
Memorydoc committed
221
                        setData();
kevin's avatar
#  
kevin committed
222
                    }
Memorydoc's avatar
#  
Memorydoc committed
223
                    sheet.setRowResizable(0,false,GC.Spread.Sheets.SheetArea.viewport);
kevin's avatar
#  
kevin committed
224
                    spread.resumePaint();
kevin's avatar
#  
kevin committed
225 226
                    return $q.when(spread);
                };
kevin's avatar
#  
kevin committed
227

Memorydoc's avatar
#  
Memorydoc committed
228
                    scope.relation.addEbitRow = function (sheet) {
kevin's avatar
#  
kevin committed
229
                    if (sheet == undefined || sheet == null)
kevin's avatar
#  
kevin committed
230
                        sheet = scope.spread.getActiveSheet();
kevin's avatar
#  
kevin committed
231
                    //添加单元格
kevin's avatar
#  
kevin committed
232
                    if (sheet.getRowCount() > 43)
kevin's avatar
#  
kevin committed
233
                        return;
kevin's avatar
#  
kevin committed
234 235 236
                    sheet.addRows(sheet.getRowCount(scope.relation.sheetArea.viewport), 1);
                    sheet.addRows(sheet.getRowCount(scope.relation.sheetArea.viewport), 1);
                    sheet.addRows(sheet.getRowCount(scope.relation.sheetArea.viewport), 1);
kevin's avatar
#  
kevin committed
237
                    for (var i = 0; i <= 7; i++) {
kevin's avatar
#  
kevin committed
238
                        sheet.addRows(sheet.getRowCount(scope.relation.sheetArea.viewport), 1);
kevin's avatar
#  
kevin committed
239
                        sheet.setValue(i + 37, 0, profileList[i]);
kevin's avatar
kevin committed
240
                    }
Memorydoc's avatar
#  
Memorydoc committed
241
                    scope.relation.lockCell(scope.spread);
kevin's avatar
#  
kevin committed
242
                }
kevin's avatar
kevin committed
243

Memorydoc's avatar
#  
Memorydoc committed
244
                    scope.relation.lockCell = function (spread) {
kevin's avatar
kevin committed
245 246 247 248 249 250 251 252
                    //todo:注册之后这里去掉注释
                    var sheet;
                    if (constant.regesterInformation.active) {
                        sheet = spread.getActiveSheet();
                    }
                    else {
                        sheet = spread.getSheet(1);
                    }
kevin's avatar
#  
kevin committed
253 254 255
                    for (var i = 0; i <= 36; i++) {
                        for (var j = 0; j <= 5; j++) {
                            sheet.getCell(i, j).locked(true);
kevin's avatar
kevin committed
256 257
                        }
                    }
kevin's avatar
#  
kevin committed
258 259 260
                    ///解锁指定单元格
                    sheet.getCell(38, 2).locked(false);//特殊因素
                    sheet.getCell(40, 2).locked(false);//ebit比率
kevin's avatar
#  
kevin committed
261 262
                    //
                    sheet.setFormatter( 38, 2,"0.00");
Memorydoc's avatar
#  
Memorydoc committed
263 264 265
                    sheet.setFormatter( 40, 2,"0.00%");
                        //sheet.setFormatter(40, 2, new CustomPerFormat());
                        //sheet.getCell(40, 2).formatter(new CustomPerFormat());
kevin's avatar
kevin committed
266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318
                };

                // 每个单元格大致由以下部分构成:_SumAll(_Inter(...)+_Manual(...)+C1)
                // _Inter: IronPython公式计算结果
                // _Manual: 手工数据源值
                // C1: 类似的Excel公式,通过SpreadJS公式引擎计算
                var initInterFunction = function (spread) {
                    var activeSheet;
                    if (constant.regesterInformation.active) {
                        activeSheet = spread.getActiveSheet();
                    }
                    else {
                        activeSheet = spread.getSheet(1);
                    }
                    var manualFunc = function () {
                    };
                    manualFunc.prototype = new GC.Spread.CalcEngine.Functions.Function("_Manual", 1, 1);
                    manualFunc.prototype.evaluate = function (args) {
                        if (!_.isString(args)) {
                            return "#VALUE!";
                        }

                        //var cellTemplateID = Number(args);
                        var ds = _.where(scope.manualDataSources, {item1: args});
                        if (!_.isEmpty(ds)) {
                            return _.reduce(ds, function (memo, x) {
                                if (x.item2.dataSourceType === 6) {
                                    var rtn = x.item2.amount;
                                    rtn = PWC.tryParseStringToNum(rtn);

                                    // 如果是数值数据源,只有OperationType为2时,该数据源的数值应被减去;其他情况下应被加上
                                    if (_.isNumber(rtn) && !isNaN(rtn)) {
                                        var operationType = x.item2.operationType;
                                        rtn = operationType === 2 ? (-rtn) : rtn;
                                    }

                                    return memo + rtn;
                                }
                                else {
                                    return memo;
                                }
                            }, 0);
                        }
                        return 0;
                    };

                    spread.clearCustomFunctions();
                    activeSheet.addCustomFunction(new manualFunc());
                };


                // Get spreadJS control.
                var getSpreadControl = function () {
kevin's avatar
#  
kevin committed
319
                    scope.relation.sheetArea = GC.Spread.Sheets.SheetArea;
kevin's avatar
kevin committed
320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338
                    return new GC.Spread.Sheets.Workbook(getSpreadControlElement());
                };

                // Get spreadJS control element
                var getSpreadControlElement = function () {
                    //var elem = document.getElementsByClassName('report-view');
                    var elem = document.getElementById('r_' + scope.templateId);
                    if (!elem) {
                        var elems = document.getElementsByClassName('report');
                        if (!elems || elems.length === 0) {
                            return undefined;
                        }
                        return elems[0];
                    }
                    return elem;
                };


                // 根据已有信息通过spreadJS计算各单元格的值
kevin's avatar
#  
kevin committed
339
                var setData = function (upload) {
kevin's avatar
kevin committed
340
                    var sheet = scope.spread.sheets[0];
kevin's avatar
#  
kevin committed
341 342
                  /*  if (!init && scope.relation.broadcast == true) {
                        scope.relation.loadEbitCell(sheet);
kevin's avatar
#  
kevin committed
343
                        return;
kevin's avatar
#  
kevin committed
344
                    }*/
Memorydoc's avatar
#  
Memorydoc committed
345
                    if(upload){ //上传上来的文件也要动态添加行
Memorydoc's avatar
#  
Memorydoc committed
346
                        scope.relation.addEbitRow(sheet);
Memorydoc's avatar
#  
Memorydoc committed
347
                        scope.relation.loadEbitCell(sheet);
Memorydoc's avatar
#  
Memorydoc committed
348
                        scope.relation.lockCell(scope.spread);
Memorydoc's avatar
#  
Memorydoc committed
349 350 351 352 353 354
                        setColWidth(sheet);
                        scope.spread.resumePaint();
                        return
                    }else{
                        scope.relation.loadEbitCell(sheet);
                    }
kevin's avatar
kevin committed
355
                    if (angular.isArray(scope.reportSource)) {
kevin's avatar
#  
kevin committed
356 357 358 359

                        //spreadJsTipService.initialize(sheet);

                        /*if (!scope.isReadOnly) {
Memorydoc's avatar
#  
Memorydoc committed
360
                            scope.relation.lockCell(scope.spread, false, true);
kevin's avatar
#  
kevin committed
361 362 363
                        }*/

                        scope.spread.suspendPaint();
kevin's avatar
#  
kevin committed
364
                      /*  if (scope.reportSource.length == 0 && scope.relation.emptyData && scope.relation.emptyData == true) {
kevin's avatar
#  
kevin committed
365
                            _init(scope.templateId, true);///如果没有数据,需要重新加载
Memorydoc's avatar
#  
Memorydoc committed
366
                            scope.relation.lockCell(scope.spread);
kevin's avatar
#  
kevin committed
367
                            return;
kevin's avatar
kevin committed
368
                        }
kevin's avatar
#  
kevin committed
369
*/
kevin's avatar
kevin committed
370 371 372 373
                        scope.reportSource.forEach(function (data) {
                            //fix bug11737 导出需要显示千分位
                            // 避免直接使用data.value = parseFloat(data.value)导致非数字型value无法显示
                            data.value = PWC.tryParseStringToNum(data.value);
kevin's avatar
#  
kevin committed
374 375 376
                            //var cell = sheet.getCell(data.rowIndex, data.columnIndex);
                            if (_.isNumber(data.value) && !isNaN(data.value)) {
                                data.value = data.value.toFixed(4);
kevin's avatar
kevin committed
377
                            }
kevin's avatar
#  
kevin committed
378
                            sheet.setValue(data.rowIndex, data.columnIndex, data.value); // 只有数值,设置为数值
kevin's avatar
kevin committed
379 380 381 382 383 384 385 386 387 388 389 390 391
                        });
                        // 设置破折号单元格的值和显示格式,将其替换为带破折号格式的0值,这样引用到的其他单元格才能正常计算
                        var rowCount = sheet.getRowCount();
                        var columnCount = sheet.getColumnCount();
                        for (var r = 0; r < rowCount; r++) {
                            for (var c = 0; c < columnCount; c++) {
                                var cell = sheet.getCell(r, c);
                                if (cell.value() === '——') {
                                    cell.value(0);
                                    cell.formatter("[=0]——;0.00");
                                }
                            }
                        }
Memorydoc's avatar
#  
Memorydoc committed
392
                        scope.relation.lockCell(scope.spread);
kevin's avatar
#  
kevin committed
393
                        scope.spread.resumePaint();
kevin's avatar
#  
kevin committed
394 395
                        scope.relation.saveToDbFlag = Math.random();

kevin's avatar
kevin committed
396 397
                    }
                };
Memorydoc's avatar
#  
Memorydoc committed
398 399 400
                scope.relation.setData = setData;//绑定单元格数据设置
                scope.relation.loadSheet = loadSheet;//绑定单元格加载

kevin's avatar
kevin committed
401
                (function initialize() {
kevin's avatar
#  
kevin committed
402
                    loadSheet(scope.templateId);
kevin's avatar
kevin committed
403 404 405 406 407 408
                })();

            }
        };
    }
]);