skyversation
/
meidu2021


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471
							<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@include file="/context/mytags.jsp"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

  <t:base type="jquery,easyui,tools,DatePicker"></t:base>
  
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  
<head>

  <meta name="spreadjs culture" content="zh-cn" />
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
          
    <link rel="stylesheet" type="text/css" href="plug-in/SpreadJS/css/gc.spread.sheets.excel2016colorful.13.1.0.css">
    <script src="plug-in/SpreadJS/scripts/gc.spread.sheets.all.13.1.0.min.js" type="text/javascript"></script>
    <script src="plug-in/SpreadJS/scripts/resources/zh/gc.spread.sheets.resources.zh.13.1.0.min.js" type="text/javascript"></script>
   <script src = "webpage/cn/com/lzt/budget/tempsheet/dataDetail.js" type="text/javascript"></script>
   <script src="plug-in/SpreadJS/license.js" type="text/javascript"></script>
<style>
.sample-tutorial {
    position: relative;
    height: 100%;
    overflow: hidden;
}
.sample-spreadsheets {
    width: calc(100%);
    height: 100%;
    overflow: hidden;
    float: left;
}

.options-container {
    float: right;
    width: 40%;
    /* padding: 12px; */
    padding-left: 12px;
    padding-right: 12px;
    height: 100%;
    box-sizing: border-box;
    background: #fbfbfb;
    overflow: auto;
}

.option-row {
    font-size: 14px;
    margin-top: 10px;
}

label {
    margin-bottom: 6px;
}

input {
    padding: 4px 6px;
}

input[type=button] {
    margin-top: 6px;
}

.buttonStyle{
    width:240px;
    height:30px;
}

.colorPickerContent{
    width: 100%;
    background-color:white;
}

.colorDom{
    width: 14px;
    height: 14px;
    margin:0 0 0 6px;
    display: inline-block;
    border: solid 1px #333333;
    vertical-align: top;
}
body {
    position: absolute;
    top: 0;
    bottom: 0;
    left: 0;
    right: 0;
}
    </style>

  
</head>
  
<body style="background:#FFFFFF">
<!-- <form id="form1" runat="server"> -->
        <div>
             <div id="div2">
             <%--查询框begin --%>
		<table cellpadding="0"  cellspacing="1" class="formtable" style="position:fixed;top:5px;"> <!--  bottom -->
		 <tr >
		 	<td align="right" width="600px" style="text-align: left;">
				<label class="Validform_label">预算主体: </label>
			<!-- </td>
		    <td class="value" nowrap> -->
          		 <input id="warehouseId" name="warehouseId" type="hidden"  >
         	     <input name="warehouseName"   type="text" class="inputxt"   style="width: 150px" id="warehouseName" readonly="readonly" />
         		  <span class="Validform_checktip"></span>
                 <label class="Validform_label" style="display: none;">领料点</label>
         			 <t:choose hiddenName="warehouseId" hiddenid="id" url="pOrdersController.do?selectReferWareHouse&classname=cn.com.lzt.warehouse.entity.WarehouseEntity&codefield=warehouseCode&namefield=warehouseName" name="list"
                     left="40%" width="450px" height="700px" icon="icon-search" title="领料点"  textname="warehouseName" isclear="true" isInit="true" ></t:choose>
     		 </td>
     	</tr>
	   <tr>
     		 <td class="value">
     		 <span class="spancon"	title="attType">预算年度：</span>
			</span>
			<input type="text" id="attYear" name="attYear" style="width: 94px;" class="Wdate" onclick="WdatePicker({readOnly:true,enableKeyboard:false,enableInputMask:false,lang:'zh-cn',dateFmt:'yyyy'})">
				<span	style="display:-moz-inline-box;display:inline-block;width: 20px;text-align:center;">年</span>
				<input	type="text" id="attMonth" name="period" style="width: 94px;" class="Wdate" onclick="WdatePicker({readOnly:true,enableKeyboard:false,enableInputMask:false,lang:'zh-cn',dateFmt:'MM'})">
				<span	style="display:-moz-inline-box;display:inline-block;width: 20px;text-align:center;">月</span>
     		 </td>
			<span style="float: left;padding-left: 750px;padding-top: 34px;" > 
			<td height="50px" align="right" colspan="8" valign="top">
				<a  href="#" class="easyui-linkbutton l-btn"  iconcls="icon-search"  onclick="btn_query()">保存</a>  
				<a  href="#" class="easyui-linkbutton l-btn"  iconcls="icon-putout"  onclick="btn_output()">导出excel</a>  
			</td>
		</span>
		</tr>
	</table>
	<%--查询框end --%>
	 </div>
	 <br/><br/><br/>
	 <div class="sample-tutorial" style="top: 20px;width:calc(100vw - 80px); height: calc(100vh - 120px);">
        <div id="ss" class="sample-spreadsheets" ></div>
       <!--  <div  id="detail" class="options-container"> -->
        	<!-- <div class="option-row">
                操作<br/>
	            <div class="option-row">
	                <input id="addBackgroundColorMenuSelection" type="checkbox" />
	                <label for="addBackgroundColorMenuSelection">Add background color menu selection</label>
	            </div>
	        </div> -->
	    </div>
    </div>

<script>

var spreadNS = GC.Spread.Sheets;

window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
    initSpread(spread);
	//initEvents(spread);
	initMenu(spread);
};
function initMenu(spread){
	var commandManager = spread.commandManager();
    var selectActualDetailbtn = {
        text: "实际发生明细",
        name: "selectActualDetail" ,
        workArea: "viewport",
        command: "selectActualDetailcommand"
    };
    //清空原有按钮
    spread.contextMenu.menuData = new Array();
    //添加自定义按钮
    spread.contextMenu.menuData.push(selectActualDetailbtn);
    //响应事件在这里写代码
    var selectActualDetailCommand = {
        canUndo: false,
        execute: function (spread, options) {
        	showBudgetSub();
       /*  var sheet = spread.getSheetFromName(options.sheetName);
        var cell = sheet.getCell(options.selections[0].row, options.selections[0].col);
        var spreadDetail = new GC.Spread.Sheets.Workbook(document.getElementById('detail'), {
    		sheetCount: 1
    	});
        var sd = detaildataSource;
        var actSheet = spreadDetail.getActiveSheet();
        if (sd.length > 0) {
        	actSheet.setDataSource(sd);
        }
        actSheet.setColumnWidth(0, 120);
        actSheet.setColumnWidth(1, 80);
        actSheet.setColumnWidth(2, 80);
        actSheet.setColumnWidth(3, 80);
        actSheet.setColumnWidth(4, 100);
        actSheet.setColumnWidth(6, 110);
        var border = new GC.Spread.Sheets.LineBorder();
        var styleInfo = new GC.Spread.Sheets.Tables.TableStyle('red', 'black', '10px arial', border, border, border, border, border, border);
        var tableStyle = new GC.Spread.Sheets.Tables.TableTheme();
        tableStyle.name('tableStyle1');
        tableStyle.headerRowStyle(styleInfo)
        var mystyle = spreadNS.Tables.TableThemes.light1;
        var table = actSheet.tables.add('table1', 0, 0, 44, 5, spreadNS.Tables.TableThemes.medium2); */
        return true;
        }
    };
    commandManager.register("selectActualDetailcommand", selectActualDetailCommand, null, false, false, false, false);
}

function initSpread(spread) {
    var sheet = spread.getSheet(0);
    // 自定义命令替换右键菜单命令
    spread.commandManager().register("customInsertRows",
        {
            canUndo: true,
            execute: function (context, options, isUndo) {
                var Commands = GC.Spread.Sheets.Commands;
                // 在此加cmd
                options.cmd = "customInsertRows";
                if (isUndo) {
                    Commands.undoTransaction(context, options);
                    return true;
                } else {
                    Commands.startTransaction(context, options);
                    spread.commandManager().execute({
                        cmd: "gc.spread.contextMenu.insertRows",
                        sheetName: sheet.name(),
                        activeRow: options.activeRow,
                        activeCol: options.activeCol,
                        selections: options.selections
                    });
                    // 在此定义用户自己的逻辑
                    var colCount = sheet.getColumnCount();
                    for(var i = 0; i < colCount; i++){
                        sheet.getCell(options.activeRow, i).locked(false);
                    }
                    Commands.endTransaction(context, options);
                    return true;
                }
            }
        });
    
    sheet.suspendPaint();
    var cfs = sheet.conditionalFormats ;
//11
    var table = sheet.tables.add("tblOperatingExpenses2", 4, 1, 51, 6, new spreadNS.Tables.TableTheme());
    table.showFooter(true);

    var dataTable = [];
    dataTable[0] = new Array("状态", "预算指标", "预算金额", "实际发生额", "差额 (￥)", "差率 (%)");
    dataTable[1] = new Array(0, "管理人员薪资及附加", 600, 545, 0, 0);
    dataTable[2] = new Array(0, "    工资", 125, 150, 0, 0);
    dataTable[3] = new Array(0, "    社保", 100, 100, 0, 0);
    dataTable[4] = new Array(0, "    公积金", 100, 90, 0, 0);
    dataTable[5] = new Array(0, "    福利费", 150, 145, 0, 0);
    dataTable[6] = new Array(0, "        员工补助", 1000, 1000, 0, 0);
    dataTable[7] = new Array(0, "        节日福利费", 500, 630, 0, 0);
    dataTable[8] = new Array(0, "        团队建设费", 350, 375, 0, 0);
    dataTable[9] = new Array(0, "经济补偿及附加", 400, 370, 0, 0);
    dataTable[10] = new Array(0, "    劳动关系纠纷", 500, 435, 0, 0);
    dataTable[11] = new Array(0, "    工伤医疗", 600, 545, 0, 0);
    dataTable[12] = new Array(0, "    案件服务费", 125, 150, 0, 0);
    dataTable[13] = new Array(0, "管理培训费", 100, 100, 0, 0);
    dataTable[14] = new Array(0, "    资格证书", 100, 90, 0, 0);
    dataTable[15] = new Array(0, "    复证费", 150, 145, 0, 0);
    dataTable[16] = new Array(0, "    培训课程及书籍", 1000, 1000, 0, 0);
    dataTable[17] = new Array(0, "    培训差旅餐费", 500, 630, 0, 0);
    dataTable[18] = new Array(0, "    在职培训", 350, 375, 0, 0);
    dataTable[19] = new Array(0, "固定资产", 400, 370, 0, 0);
    dataTable[20] = new Array(0, "    专业设备", 500, 435, 0, 0);
    dataTable[21] = new Array(0, "    电子设备", 600, 545, 0, 0);
    dataTable[22] = new Array(0, "    运输工具", 125, 150, 0, 0);
    dataTable[23] = new Array(0, "	  办公家具电器", 100, 100, 0, 0);
    dataTable[24] = new Array(0, "	  房屋", 100, 90, 0, 0);
    dataTable[25] = new Array(0, "场地租赁及附加", 150, 145, 0, 0);
    dataTable[26] = new Array(0, "	  厂房和仓库", 1000, 1000, 0, 0);
    dataTable[27] = new Array(0, "	  办公场地", 500, 630, 0, 0);
    dataTable[28] = new Array(0, "	  员工宿舍", 350, 375, 0, 0);
    dataTable[29] = new Array(0, "办公杂费", 400, 370, 0, 0);
    dataTable[30] = new Array(0, "    办公用品费", 500, 435, 0, 0);
    dataTable[31] = new Array(0, "    差旅费", 600, 545, 0, 0);
    dataTable[32] = new Array(0, "    印刷、纸张", 125, 150, 0, 0);
    dataTable[33] = new Array(0, "	  物业费", 100, 100, 0, 0);
    dataTable[34] = new Array(0, "	  维护修理", 100, 90, 0, 0);
    dataTable[35] = new Array(0, "    办公室用水", 150, 145, 0, 0);
    dataTable[36] = new Array(0, "	  快递费", 1000, 1000, 0, 0);
    dataTable[37] = new Array(0, "	  会务费", 500, 630, 0, 0);
    dataTable[38] = new Array(0, "	  设备检测费", 350, 375, 0, 0);
    dataTable[39] = new Array(0, "    绿植费", 400, 370, 0, 0);
    dataTable[40] = new Array(0, "    网络费", 500, 435, 0, 0);
    dataTable[41] = new Array(0, "    水费", 600, 545, 0, 0);
    dataTable[42] = new Array(0, "    电费", 125, 150, 0, 0);
    dataTable[43] = new Array(0, "	  煤气费", 100, 100, 0, 0);
    dataTable[44] = new Array(0, "绩效评价指标",null ,null, null, null);
    dataTable[45] = new Array(0, "    劳动纠纷发生率", 2.00,  1.28, 0, 0);
    dataTable[46] = new Array(0, "	   经济补偿率", 2.00,  1.89, 0, 0);
    dataTable[47] = new Array(0, "	   特困使用率", 2.00,  2.1, 0, 0);
    dataTable[48] = new Array(0, "	   缴金率", 2.00,  1.33, 0, 0);
    dataTable[49] = new Array(0, "    节税比例", 2.00,  3.58, 0, 0);
    dataTable[50] = new Array(0, "    外包服务节本率", 2.00,  3.58, 0, 0);
    dataTable[51] = new Array(0, "合计", 0, 0, 0, 0);

    sheet.addSpan(0, 1, 1, 4);
    sheet.addSpan(0, 5, 1, 2);
    sheet.addSpan(1, 1, 1, 6);
    sheet.addSpan(2, 1, 1, 6);
    sheet.addSpan(3, 1, 1, 2);
   // sheet.addSpan(16, 1, 1, 6);
   // sheet.addSpan(17, 1, 1, 6);

    sheet.getDefaultStyle().vAlign = spreadNS.VerticalAlign.center;
    sheet.getDefaultStyle().font = "lighter 10pt Calibri";
    sheet.getDefaultStyle().foreColor = "rgb(68, 84, 106)";
    sheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
    sheet.getCell(0, 1).value("预算执行明细表").font("lighter 28pt Calibri");
    sheet.getCell(0, 5).value("美都环卫物业-人力资源部, 2020").font("11pt Calibri").foreColor("rgb(64, 64, 64)")
        .hAlign(spreadNS.HorizontalAlign.right).vAlign(spreadNS.VerticalAlign.bottom);
    sheet.getCell(3, 1).value("指标预算").font("bold 13pt Calibri ").foreColor("rgb(64, 64, 64)");
    sheet.getRange(4, 1, 1, 6).font("bold 10pt Calibri")
        .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
    //15
    sheet.getRange(55, 1, 1, 6).font("bold 10pt Calibri")
        .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
    sheet.getRange(3, 1, 1, 2)
        .borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))
        .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))
        .borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
    //12 getRange (row col rowcount colcount)
    sheet.getRange(4, 1, 52, 1).borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin)).hAlign(spreadNS.HorizontalAlign.center);
    //12
    sheet.getRange(4, 6, 52, 1).borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
    //10
    sheet.getRange(5, 1, 50, 6).borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.dashed));;
//16
    for (var row = 4; row < 56; row++) {
        for (var col = 1; col < 7; col++) {
            sheet.setValue(row, col, dataTable[row - 4][col - 1]);
        }
    }

    var rowHeights = new Array(74, 4, 20);
    for (var row = 0; row < 3; row++) {
        sheet.setRowHeight(row, rowHeights[row]);
    }
    //
    for (var row = 3; row < 56; row++) {
        sheet.setRowHeight(row, 24);
    }
    //
    sheet.setRowHeight(56, 4);

    //var colWidths = new Array(18, 83, 111, 84, 85, 126, 129);
    var colWidths = new Array(18, 120, 200, 150, 150, 150, 129);
    for (var col = 0; col < 7; col++) {
        sheet.setColumnWidth(col, colWidths[col]);
    }

    sheet.getRange(1, 1, 1, 6)
        .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))
        .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
//17
    sheet.getRange(56, 1, 1, 6)
        .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))
        .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
//13
    sheet.getRange(3, 3, 45, 3).formatter('"￥"#,##0.00');
    
    sheet.getRange(49, 3, 6, 3).formatter('##0.00"%"');
    //12
    sheet.getRange(3, 6, 52, 1).formatter('0%');
    //15
    sheet.getCell(55, 6).formatter('0.0%');
    //
    sheet.getCell(5, 3).formula('=SUM(D7:D10)');
    sheet.getCell(5, 4).formula('=SUM(E7:E10)');
    
    sheet.getCell(9, 3).formula('=SUM(D11:D13)');
    sheet.getCell(9, 4).formula('=SUM(E11:E13)');

    var iconSetRule1 = new spreadNS.ConditionalFormatting.IconSetRule();
    //15
    iconSetRule1.ranges([new spreadNS.Range(55, 1, 1, 1)]);
    iconSetRule1.iconSetType(spreadNS.ConditionalFormatting.IconSetType.threeSymbolsCircled)
        .showIconOnly(true);
    var icons = iconSetRule1.iconCriteria();
    icons[0] = new spreadNS.ConditionalFormatting.IconCriterion(false, spreadNS.ConditionalFormatting.IconValueType.number, -1);
    icons[1] = new spreadNS.ConditionalFormatting.IconCriterion(true, spreadNS.ConditionalFormatting.IconValueType.number, 1);
    cfs.addRule(iconSetRule1);


    var style = new spreadNS.Style();
    style.foreColor = "red";
    //15
    cfs.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.lessThan, 0, 0, style, [new spreadNS.Range(55, 5, 1, 2)]);


    var dataBarRule1 = new spreadNS.ConditionalFormatting.DataBarRule();
    dataBarRule1.minType(spreadNS.ConditionalFormatting.ScaleValueType.automin);
    dataBarRule1.maxType(spreadNS.ConditionalFormatting.ScaleValueType.automax);
    //10
    dataBarRule1.ranges([new spreadNS.Range(5, 4, 50, 1)]);
    dataBarRule1.color("orange").showBorder(true).borderColor("orange").dataBarDirection(spreadNS.ConditionalFormatting.BarDirection.rightToLeft);
    cfs.addRule(dataBarRule1);


    var dataBarRule2 = new spreadNS.ConditionalFormatting.DataBarRule();
    dataBarRule2.minType(spreadNS.ConditionalFormatting.ScaleValueType.automin);
    dataBarRule2.maxType(spreadNS.ConditionalFormatting.ScaleValueType.automax);
    //10
    dataBarRule2.ranges([new spreadNS.Range(5, 3, 50, 1)]);
    dataBarRule2.color("rgb(0,138,239)").showBorder(true).borderColor("rgb(0,138,239)");
    cfs.addRule(dataBarRule2);


    var dataBarRule3 = new spreadNS.ConditionalFormatting.DataBarRule();
    dataBarRule3.minType(spreadNS.ConditionalFormatting.ScaleValueType.lowestValue);
    dataBarRule3.maxType(spreadNS.ConditionalFormatting.ScaleValueType.highestValue);
    //10
    dataBarRule3.ranges([new spreadNS.Range(5, 6, 50, 1)]);
    dataBarRule3.color("rgb(91,155,213)").showBorder(true).borderColor("rgb(91,155,213)")
        .negativeFillColor("rgb(237,125,49)").useNegativeBorderColor(true).negativeBorderColor("rgb(237,125,49)")
        .axisPosition(spreadNS.ConditionalFormatting.DataBarAxisPosition.cellMidPoint);
    cfs.addRule(dataBarRule3);

    var iconSetRule2 = new spreadNS.ConditionalFormatting.IconSetRule();
    //10
    iconSetRule2.ranges([new spreadNS.Range(5, 1, 50, 1)]);
    iconSetRule2.iconSetType(spreadNS.ConditionalFormatting.IconSetType.threeSymbolsUncircled)
        .reverseIconOrder(true)
        .showIconOnly(true);
    cfs.addRule(iconSetRule2);

    table.setColumnFormula(0, "=tblOperatingExpenses2[[#Totals],[差率 (%)]]");
    table.setColumnFormula(2, "=SUBTOTAL(109,[预算金额])");
    table.setColumnFormula(3, "=SUBTOTAL(109,[实际发生额])");
    table.setColumnFormula(4, "=SUBTOTAL(109,[差额 (￥)])");
    table.setColumnFormula(5, '=IFERROR(SUM(tblOperatingExpenses2[[#Totals],[差额 (￥)]]/tblOperatingExpenses2[[#Totals],[预算金额]]),"")');

    table.setColumnDataFormula(0, '=IFERROR([@实际发生额]/[@预算金额],"")');
    table.setColumnDataFormula(4, '=[@预算金额]-[@实际发生额]');
    //去掉一个单元格的公式
    sheet.getCell(48,5).formula('').text('');
    table.setColumnDataFormula(5, '=IFERROR([@[差额 (￥)]]/[@预算金额],"")');

    sheet.frozenRowCount(5);
    
    sheet.resumePaint();
}

function showBudgetSub(){
	var url = 'budgetTempSheetController.do?budgetActualSubspreadjsdemo';
	$.dialog({
		title:'预实明细',
	    content: 'url:'+ url,
		zIndex: getzIndex(),
		lock: true, 
		width: window.top.document.body.offsetWidth ,
	    height: window.top.document.body.offsetHeight-100,
	    cache:false,
	    button: [
	        {
	            name: '<t:mutiLang langKey="common.confirm"/>',
	            callback: function(){
	            },
	            focus: true
	        },
	        {
	            name: '<t:mutiLang langKey="common.cancel"/>',
	            callback: function(){
	            	//refresh();
	            }
	        }
	    ]
	});
}
</script>
  
</body>
  
</html>