cld/protected/class/receipt.func.php

<?php

/*
 * 办事处汇总明细
 */
function _getReceiptSummaryHtml($cglist,$aiAllList,$dataSummary,$year){
	
	
	$html=array();
	foreach ($cglist as $cgk=>$value){
		
		
		//初始化年合计数组
		foreach ($aiAllList as $yptempk=>$yptempv){
			$yearPriceArray[$value['cid']][$yptempk]=0;
			//栏目细则
			foreach ($yptempv as $k=>$v){
				$yearLawsPriceArray[$value['cid']][$yptempk][$v['name']]=0;
			}
		}
		
		
		//办事处每月数据展示
		$summaryDateHtml='';
		for($month=1;$month<=12;$month++){
			$summaryDateHtml.='<tr>';
			$summaryDateHtml.='<th>'.$value['title'].$month.'月合计</th>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR colGreen"></td>';
			
			$itemHtml='';
			if(isset($dataSummary[$value['cid']])){
				foreach ($aiAllList as $aiak=>$aiav){//总项目
					
					$tempHtml='';$tempPrice=0;
					foreach ($aiav as $k=>$v){//项目细则
						$falg=true;
						foreach ($dataSummary[$value['cid']][$month][$aiak] as $dsk=>$dsv){//数据实体
							if($dsv['item']==$v['name']){
								$tempHtml.='<td class="taR">'.$dsv['price'].'</td>';
								$tempPrice+=$dsv['price'];
								$yearLawsPriceArray[$value['cid']][$aiak][$v['name']]+=$dsv['price'];//该办事处项目细则金额合计
								$falg=false;
								break;
							}
						}
						if($falg){
							$tempHtml.='<td class="taR"></td>';
						}
					}
					
					$yearPriceArray[$value['cid']][$aiak]+=$tempPrice;
					
					$itemHtml.='<td class="taR colRed">'.$tempPrice.'</td>';//每个月份 项目金额合计
					$itemHtml.=$tempHtml;//每个月份项目细则金额
				}
			}else{
				foreach ($aiAllList as $aiak=>$aiav){
					$itemHtml.='<td class="taR colRed"></td>';
					foreach ($aiav as $k=>$v){
						$itemHtml.='<td class="taR"></td>';
					}
				}
			}
			
			$summaryDateHtml.=$itemHtml;
			$summaryDateHtml.='</tr>';
		}
		
		
		
		//办事处全年合计---全年全办事处合计
		$summaryYearHtml='<tr><th>'.$value['title'].'年合计</th>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR"></td>
										<td class="taR colGreen"></td>';
		foreach ($aiAllList as $aalk=>$aalv){
			if(!isset($topYearPrice[$aalk])){
				$topYearPrice[$aalk]=0;
			}
			//栏目
			$summaryYearHtml.='<td class="taR colRed">'.$yearPriceArray[$value['cid']][$aalk].'</td>';
			$topYearPrice[$aalk]+=$yearPriceArray[$value['cid']][$aalk];
			//栏目细则
			foreach ($aalv as $k=>$v){
				if(!isset($topYearLawsPrice[$aalk][$v['name']])){
					$topYearLawsPrice[$aalk][$v['name']]=0;
				}
				
				$summaryYearHtml.='<th>'.$yearLawsPriceArray[$value['cid']][$aalk][$v['name']].'</th>';
				$topYearLawsPrice[$aalk][$v['name']]+=$yearLawsPriceArray[$value['cid']][$aalk][$v['name']];
			}
		}
		$summaryYearHtml.='</tr>';
		
		$html[$value['cid']]['total']=$summaryYearHtml; //办事处合计
		$html[$value['cid']]['allMonth']=$summaryDateHtml; //办事处每月合计
	}
	
	//全办事处全年合计
	$topYearTempHtml='<tr><th>'.$year.'年合计</th>
										<td class="taR"></td><td class="taR"></td><td class="taR"></td><td class="taR"></td><td class="taR"></td>
										<td class="taR colGreen"></td>';
	foreach ($aiAllList as $aalk=>$aalv){
		//栏目
		$topYearTempHtml.='<td class="taR colRed">'.$topYearPrice[$aalk].'</td>';
		//栏目细则
		foreach ($aalv as $k=>$v){
			$topYearTempHtml.='<th>'.$topYearLawsPrice[$aalk][$v['name']].'</th>';
		}
	}
	$topYearTempHtml.='</tr>';
	
	//各办事处占比
	$categoryHtml='';
	foreach ($cglist as $cgk=>$value){
		$categoryProportionHtml='<tr><th>'.$value['title'].'费用占比</th>
										<td class="taR"></td><td class="taR"></td><td class="taR"></td><td class="taR"></td><td class="taR"></td>
										<td class="taR colGreen"></td>';
		foreach ($aiAllList as $aalk=>$aalv){
			//合计占比-栏目
			if(empty($yearPriceArray[$value['cid']][$aalk])||empty($topYearPrice[$aalk])){
				$categoryProportionHtml.='<td class="taR colRed"></td>';
			}else{
				
				$proportionTotal=sprintf("%.2f",$yearPriceArray[$value['cid']][$aalk]/$topYearPrice[$aalk]*100);
				$categoryProportionHtml.='<td class="taR colRed">'.$proportionTotal.'%</td>';
			}
			
			//栏目细则
			foreach ($aalv as $k=>$v){
				//细则占比计算
				if(empty($yearLawsPriceArray[$value['cid']][$aalk][$v['name']])||empty($topYearLawsPrice[$aalk][$v['name']])){
					$categoryProportionHtml.='<th></th>';
				}else{
					
					$proportionLawsTotal=sprintf("%.2f",$yearLawsPriceArray[$value['cid']][$aalk][$v['name']]/$topYearLawsPrice[$aalk][$v['name']]*100);;
					$categoryProportionHtml.='<th>'.$proportionLawsTotal.'%</th>';
				}
			}
		}
		$categoryProportionHtml.='</tr>';
		
		
		$categoryHtml.=$html[$value['cid']]['total'];
		$categoryHtml.=$categoryProportionHtml;
		$categoryHtml.=$html[$value['cid']]['allMonth'];
	}
	
	$topHtml='';
	$topHtml.=$topYearTempHtml;//全年合计
	$topHtml.=$categoryHtml;//办事处合计
	
	return $topHtml;
}


/**
 * 获得借款单详情
 */
function _getReceiptLoanHtml($receiptDetail, $verifylist, $extendHtml = '') {
	$receiptDetail ['loanItem'] = json_decode ( $receiptDetail ['loanItem'], true );
	$receiptDetail ['sumCN'] = num_to_rmb ( $receiptDetail ['sum'] );
	$receiptDetail ['loanSumCN'] = num_to_rmb ( $receiptDetail ['loanSum'] );
	$receiptDetail ['amountCN'] = num_to_rmb ( $receiptDetail ['amount'] );
	
	$executeCopy = getExecuteStaff ( $receiptDetail );
	// print_r ( $executeCopy );
	
	$html = '<div class="saeaList">
							<div class="entry loan-list" style="margin-bottom: 50px">
								<div class="seal"></div>
								<div class="seTable">
									<table class="table table-bordered table-condensed table-hover">
										<tbody>
											<tr>
												<th colspan="2" class="taC">借款费用</th>
											</tr>
											<tr>
												<th>借款费用</th>
												<td class="taR" width="140">￥' . $receiptDetail ['loanItem'] ['借款费用'] [0] ['price'] . '</td>
											</tr>
											
											<tr>
												<th class="taC" colspan="2">产生费用所在办事处</th>
											</tr>
											<tr>
												<th style="text-align: center" colspan="2">' . $receiptDetail ['category'] [0] ['title'] . '</th>
											</tr>
											<tr>
												<th colspan="2" class="taC">汇款信息</th>
											</tr>
											<tr>
												<th>汇款人户名</th>
												<td>' . $receiptDetail ['remittanceName'] . '</td>
											</tr>
											<tr>
												<th>汇款银行</th>';
	
	if ($receiptDetail ['remittanceBankType'] == 0)
		$html .= '<td>广发银行</td>';
	elseif ($receiptDetail ['remittanceBankType'] == 1)
		$html .= '<td>其他</td>';
	
	$html .= '</tr>
											<tr>
												<th>汇款开户银行名称</th>
												<td>' . $receiptDetail ['bankName'] . '</td>
											</tr>
											<tr>
												<th>汇款帐号</th>
												<td>' . $receiptDetail ['bankNumber'] . '</td>
											</tr>';
	if ($receiptDetail ['remittanceBankType'] == 1)
		$html .= '<tr>
												<th>联行号</th>
												<td>' . $receiptDetail ['coupletNumber'] . '</td>
											</tr>';
	
	$html .= '<tr>
												<th class="taC" colspan="2">费用说明</th>
											</tr>
											<tr>
												<td colspan="2">' . $receiptDetail ['explanation'] . '</td>
											</tr>
											
											<tr class="warning">
												<td class="taR"><b>（' . $receiptDetail ['receiptOrder'] . '）本次费用合计</b></td>
												<td class="taR"><b style="font-size: 24px">￥' . $receiptDetail ['sum'] . '</b><br>' . $receiptDetail ['sumCN'] . '</td>
											</tr>
											<tr class="warning">
												<td class="taR"><b>汇款金额</b></td>
												<td class="taR" width="140"><b style="font-size: 24px">￥' . $receiptDetail ['amount'] . '</b><br>' . $receiptDetail ['amountCN'] . '</td>
											</tr>
										</tbody>
									</table>
								</div>
								<div class="seCensor loan-censor">
									<div class="title">' . $receiptDetail ['receiptOrder'] . '</div>
									<div class="censorList clearfix censorHeight" style="height: 548px;">
										<ul>
											<li class="post">
												<div class="avtra">
													<img src="' . $receiptDetail ['staffDetail'] ['avatar'] . '_2.jpg" width="32">
												</div>
												<div class="comment">
													<p>
														<span class="fR">' . $receiptDetail ['date'] . '</span>' . $receiptDetail ['staffDetail'] ['username'] . '
													</p>
													<p>提交审批</p>
												</div>
											</li>';
	foreach ( $verifylist as $key => $value ) {
		$html .= '<li ';
		if ($value ['status'] == 1) {
			$html .= 'class="done"';
		} elseif ($value ['status'] == 3) {
			$html .= 'class="undone"';
		}
		$html .= '>
												<div class="avtra">
													<img src="' . $value [2] . '_2.jpg" width="32">
												</div>
												<div class="comment">
													<p class="colGray">
														<span class="fR">' . $value ['date'] . '</span>' . $value [1] . '';
		if (isset ( $value ['rolename'] )) {
			$html .= '（' . $value ['rolename'] . '）';
		}
		$html .= '</p>
													<p>' . $value ['opinion'] . '</p>
												</div>
											</li>';
	}
	
	foreach ( $executeCopy as $key => $value ) {
		$html .= '<li ';
		if ($value ['status'] == 1) {
			$html .= 'class="done"';
		} elseif ($value ['status'] == 3) {
			$html .= 'class="undone"';
		}
		$html .= '>
												<div class="avtra">
													<img src="' . $value [2] . '_2.jpg" width="32">
												</div>
												<div class="comment">
													<p class="colGray">
														<span class="fR">' . $value ['date'] . '</span>' . $value [1] . '（费用执行）
													</p>
													<p>' . $value ['opinion'] . '</p>
												</div>
											</li>';
	}
	$html .= '
										</ul>
									</div>
								</div>
							</div>
												
							' . $extendHtml . '
							
			</div>';
	return $html;
}
function _getPaymentsCollectPayByYearHtml($statisticsPay = array()) {
	$html = '';
	$subtotalSum = array (
			1 => 0,
			2 => 0,
			3 => 0,
			4 => 0,
			5 => 0,
			6 => 0,
			7 => 0,
			8 => 0,
			9 => 0,
			10 => 0,
			11 => 0,
			12 => 0 
	);
	for($i = 1; $i <= 15; $i ++) {
		
		if ($i == 1) {
			
			$html = '<tr><th colspan="2" >银行手续费</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotalSum [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 2) {
			
			$html .= '<tr><th colspan="2" >总部电话费</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotalSum [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 3) {
			
			$subtotal = array (
					1 => 0,
					2 => 0,
					3 => 0,
					4 => 0,
					5 => 0,
					6 => 0,
					7 => 0,
					8 => 0,
					9 => 0,
					10 => 0,
					11 => 0,
					12 => 0 
			);
			$html .= '<tr><th rowspan="8" width="90">缴税</th>';
			$html .= '<th width="90">增值税</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '增值税' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">税金附加</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '税金附加' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			//---------------
			$html .= '<th width="90">企业所得税</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '企业所得税' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			//-----------
			$html .= '<th width="90">工会费</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '工会费' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
					$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			//-----------
			$html .= '<th width="90">社保</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '社保' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
				$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			
			//-----------
			$html .= '<th width="90">个税</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '个税' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
				$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			
			//-----------
			$html .= '<th width="90">印花税</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '印花税' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
				$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			
			//-----------
			
			
			
			$html .= '<th width="90" class="colRed">小计</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colRed">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 5) {
			
			$html .= '<tr><th colspan="2" >保证金</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '' && $value ['month'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . abs ( $value ['payPrice'] ) . '</td>';
						$sum += abs ( $value ['payPrice'] );
						$subtotalSum [$month] += abs ( $value ['payPrice'] );
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		}
	}
	
	$html .= '<tr><th class="taR" colspan="2">合计</th>';
	$sum = 0;
	foreach ( $subtotalSum as $key => $value ) {
		$sum += $value;
		if (! empty ( $value ))
			$html .= '<td class="taR colGreen">￥' . $value . '</td>';
		else
			$html .= '<td class="taR">-</td>';
	}
	$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
	$html .= '</tr>';
	$html .= '<tr class="warning"><td class="taR" colspan="2"><b>全年合计</b></td>
	  						<td class="colGreed taR" colspan="14"><b style="font-size:24px">￥' . $sum . '</b></td></tr>';
	
	return array (
			'html' => $html,
			'sum' => $sum 
	);
}

/**
 * 获得汇总 日常收支年
 * @param array $statisticsIncome
 * @return string
 */
function _getPaymentsCollectByYearHtml($statisticsIncome = array()) {
	$html = '';
	$subtotalSum = array (
			1 => 0,
			2 => 0,
			3 => 0,
			4 => 0,
			5 => 0,
			6 => 0,
			7 => 0,
			8 => 0,
			9 => 0,
			10 => 0,
			11 => 0,
			12 => 0 
	);
	for($i = 1; $i <= 15; $i ++) {
		
		if ($i == 1) {
			$subtotal = array (
					1 => 0,
					2 => 0,
					3 => 0,
					4 => 0,
					5 => 0,
					6 => 0,
					7 => 0,
					8 => 0,
					9 => 0,
					10 => 0,
					11 => 0,
					12 => 0 
			);
			$html .= '<tr><th rowspan="4" width="90">利息收入</th>';
			$html .= '<th width="90">银行存款</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '银行存款' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">活期理财收益</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '活期理财收益' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">固定理财收益</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '固定理财收益' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">个人借款</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '个人借款' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90" class="colGreen">小计</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colGreen">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 2) {
			
			$subtotal = array (
					1 => 0,
					2 => 0,
					3 => 0,
					4 => 0,
					5 => 0,
					6 => 0,
					7 => 0,
					8 => 0,
					9 => 0,
					10 => 0,
					11 => 0,
					12 => 0 
			);
			$html .= '<tr><th rowspan="3" width="90">补贴收入</th>';
			$html .= '<th width="90">社保</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '社保' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">财政</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '财政' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90" class="colGreen">小计</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colGreen">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 3) {
			
			$html .= '<tr><th colspan="2" >纵横知道</th>';
			
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 4) {
			
			$subtotal = array (
					1 => 0,
					2 => 0,
					3 => 0,
					4 => 0,
					5 => 0,
					6 => 0,
					7 => 0,
					8 => 0,
					9 => 0,
					10 => 0,
					11 => 0,
					12 => 0 
			);
			$html .= '<tr><th rowspan="3" width="90">借款收回</th>';
			$html .= '<th width="90">备用金</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '备用金' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">私人借款</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '私人借款' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90" class="colGreen">小计</th>';
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colGreen">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 5) {
			
			$html .= '<tr><th colspan="2" >退汇</th>';
			
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 6) {
			
			$html .= '<tr><th colspan="2" >其他</th>';
			
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 8) {
			
			$html .= '<tr><th colspan="2" >保证金</th>';
			
			$sum = 0;
			for($month = 1; $month <= 12; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['month'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		}
	}
	
	$html .= '<tr><th class="taR" colspan="2">合计</th>';
	$sum = 0;
	foreach ( $subtotalSum as $key => $value ) {
		$sum += $value;
		if (! empty ( $value ))
			$html .= '<td class="taR colGreen">￥' . $value . '</td>';
		else
			$html .= '<td class="taR">-</td>';
	}
	$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
	$html .= '</tr>';
	$html .= '<tr class="warning"><td class="taR" colspan="2"><b>全年合计</b></td>
	  						<td class="colGreed taR" colspan="14"><b style="font-size:24px">￥' . $sum . '</b></td></tr>';
	
	return array (
			'html' => $html,
			'sum' => $sum 
	);
}

/**
 * 获得汇总 日常收支年
 * @param array $statisticsIncome
 * @return string
 */
function _getPaymentsCollectByDayHtml($statisticsIncome = array(), $day = 0) {
	$html = '<tr><th class="taC" colspan="2">日常收入</th>';
	
	for($d = 1; $d <= $day; $d ++) {
		$subtotalSum [$d] = 0;
		$html .= '<th class="taC" width="80">' . $d . '日</th>';
	}
	$html .= '<th class="taC" width="110">总计</th></tr>';
	
	for($i = 1; $i <= 15; $i ++) {
		
		if ($i == 1) {
			
			for($d = 1; $d <= $day; $d ++) {
				$subtotal [$d] = 0;
			}
			
			// $subtotal = array (
			// 1 => 0,
			// 2 => 0,
			// 3 => 0,
			// 4 => 0,
			// 5 => 0,
			// 6 => 0,
			// 7 => 0,
			// 8 => 0,
			// 9 => 0,
			// 10 => 0,
			// 11 => 0,
			// 12 => 0
			// );
			$html .= '<tr><th rowspan="4" width="90">利息收入</th>';
			$html .= '<th width="90">银行存款</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '银行存款' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">理财收益</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '理财收益' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">个人借款</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '个人借款' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90" class="colGreen">小计</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colGreen">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 2) {
			
			for($d = 1; $d <= $day; $d ++) {
				$subtotal [$d] = 0;
			}
			$html .= '<tr><th rowspan="3" width="90">补贴收入</th>';
			$html .= '<th width="90">社保</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '社保' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">财政</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '财政' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90" class="colGreen">小计</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colGreen">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 3) {
			
			$html .= '<tr><th colspan="2" >纵横知道</th>';
			
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 4) {
			
			for($d = 1; $d <= $day; $d ++) {
				$subtotal [$d] = 0;
			}
			$html .= '<tr><th rowspan="3" width="90">借款收回</th>';
			$html .= '<th width="90">备用金</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '备用金' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">私人借款</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '私人借款' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotal [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90" class="colGreen">小计</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colGreen">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 5) {
			
			$html .= '<tr><th colspan="2" >退汇</th>';
			
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 6) {
			
			$html .= '<tr><th colspan="2" >其他</th>';
			
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 8) {
			
			$html .= '<tr><th colspan="2" >保证金</th>';
			
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsIncome as $key => $value ) {
					if ($value ['accountMsg'] == '' && $value ['day'] == $month && $value ['accountType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['inComePrice'] . '</td>';
						$sum += $value ['inComePrice'];
						$subtotalSum [$month] += $value ['inComePrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
			$html .= '</tr>';
		}
	}
	
	$html .= '<tr><th class="taR" colspan="2">合计</th>';
	$sum = 0;
	foreach ( $subtotalSum as $key => $value ) {
		$sum += $value;
		if (! empty ( $value ))
			$html .= '<td class="taR colGreen">￥' . $value . '</td>';
		else
			$html .= '<td class="taR">-</td>';
	}
	$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
	$html .= '</tr>';
	$colspan = $day + 1;
	$html .= '<tr class="warning"><td class="taR" colspan="2"><b>全月合计</b></td>
	  						<td class="colGreed taR" colspan="' . $colspan . '"><b style="font-size:24px">￥' . $sum . '</b></td></tr>';
	
	return array (
			'html' => $html,
			'sum' => $sum 
	);
}
function _getPaymentsCollectPayByDayHtml($statisticsPay = array(), $day = 0) {
	$html = '<tr><th class="taC" colspan="2">日常支出</th>';
	
	for($d = 1; $d <= $day; $d ++) {
		$subtotalSum [$d] = 0;
		$html .= '<th class="taC" width="80">' . $d . '日</th>';
	}
	$html .= '<th class="taC" width="110">总计</th></tr>';
	
	for($i = 1; $i <= 15; $i ++) {
		
		if ($i == 1) {
			
			$html .= '<tr><th colspan="2" >银行手续费</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotalSum [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 2) {
			
			$html .= '<tr><th colspan="2" >总部电话费</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotalSum [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 3) {
			
			for($d = 1; $d <= $day; $d ++) {
				$subtotal [$d] = 0;
			}
			$html .= '<tr><th rowspan="8" width="90">缴税</th>';
			$html .= '<th width="90">增值税</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '增值税' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">税金附加</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '税金附加' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			$html .= '<th width="90">企业所得税</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '企业所得税' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR">￥' . $sum . '</td>';
			$html .= '</tr>';
			
			//------------------------------
			$html .= '<th width="90">工会费</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '工会费' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
				$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			//----------------------------------------
			$html .= '<th width="90">社保</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '社保' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
				$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			//----------------------------------------
			$html .= '<th width="90">个税</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '个税' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
				$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			//----------------------------------------
			$html .= '<th width="90">印花税</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '印花税' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . $value ['payPrice'] . '</td>';
						$sum += $value ['payPrice'];
						$subtotal [$month] += $value ['payPrice'];
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0){
				$html .= '<td class="taR">-</td>';
			}else{
				$html .= '<td class="taR">￥' . $sum . '</td>';
			}
			$html .= '</tr>';
			//----------------------------------------
			
			
			
			$html .= '<th width="90" class="colRed">小计</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$sum += $subtotal [$month];
				$subtotalSum [$month] += $subtotal [$month];
				
				if (! empty ( $subtotal [$month] ))
					$html .= '<td class="taR colRed">￥' . $subtotal [$month] . '</td>';
				else
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		} elseif ($i == 5) {
			
			$html .= '<tr><th colspan="2" >保证金</th>';
			$sum = 0;
			for($month = 1; $month <= $day; $month ++) {
				$falg = true;
				foreach ( $statisticsPay as $key => $value ) {
					if ($value ['expenditureMsg'] == '' && $value ['day'] == $month && $value ['expenditureType'] == $i) {
						$html .= '<td class="taR">￥' . abs ( $value ['payPrice'] ) . '</td>';
						$sum += abs ( $value ['payPrice'] );
						$subtotalSum [$month] += abs ( $value ['payPrice'] );
						$falg = false;
						break;
					}
				}
				if ($falg)
					$html .= '<td class="taR">-</td>';
			}
			if ($sum == 0)
				$html .= '<td class="taR">-</td>';
			else
				$html .= '<td class="taR colRed">￥' . $sum . '</td>';
			$html .= '</tr>';
		}
	}
	
	$html .= '<tr><th class="taR" colspan="2">合计</th>';
	$sum = 0;
	foreach ( $subtotalSum as $key => $value ) {
		$sum += $value;
		if (! empty ( $value ))
			$html .= '<td class="taR colGreen">￥' . $value . '</td>';
		else
			$html .= '<td class="taR">-</td>';
	}
	$html .= '<td class="taR colGreen">￥' . $sum . '</td>';
	$html .= '</tr>';
	$colspan = $day + 1;
	$html .= '<tr class="warning"><td class="taR" colspan="2"><b>全月合计</b></td>
	  						<td class="colGreed taR" colspan="' . $colspan . '"><b style="font-size:24px">￥' . $sum . '</b></td></tr>';
	
	return array (
			'html' => $html,
			'sum' => $sum 
	);
}
function getExecuteStaff($receiptDetail) {
	// 费用执行人相关数据
	$executeCopy = json_decode ( $receiptDetail ['executeCopy'], true );
	$executeStaff = json_decode ( $receiptDetail ['executeStaff'], true );
	foreach ( $executeCopy as $k => $v ) {
		$executeCopy [$k] ['date'] = '';
		$executeCopy [$k] ['opinion'] = '';
		$executeCopy [$k] ['status'] = '';
		if (! empty ( $executeStaff ))
			foreach ( $executeStaff as $m => $u ) {
				if ($v [0] == $m) {
					$executeCopy [$k] ['date'] = $u ['date'];
					$executeCopy [$k] ['opinion'] = $u ['opinion'];
					$executeCopy [$k] ['status'] = $u ['status'];
					break;
				}
			}
	}
	$receiptDetail ['executeCopy'] = $executeCopy;
	return $receiptDetail ['executeCopy'];
}
function num_to_rmb($num) {
	$c1 = "零壹贰叁肆伍陆柒捌玖";
	$c2 = "分角元拾佰仟万拾佰仟亿";
	// 精确到分后面就不要了，所以只留两个小数位
	$num = round ( $num, 2 );
	// 将数字转化为整数
	$num = $num * 100;
	if (strlen ( $num ) > 10) {
		return "金额太大，请检查";
	}
	$i = 0;
	$c = "";
	while ( 1 ) {
		if ($i == 0) {
			// 获取最后一位数字
			$n = substr ( $num, strlen ( $num ) - 1, 1 );
		} else {
			$n = $num % 10;
		}
		// 每次将最后一位数字转化为中文
		$p1 = substr ( $c1, 3 * $n, 3 );
		$p2 = substr ( $c2, 3 * $i, 3 );
		
		if ($n != '0' || ($n == '0' && ($p2 == '亿' || $p2 == '万' || $p2 == '元'))) {
			$c = $p1 . $p2 . $c;
		} else {
			$c = $p1 . $c;
		}
		$i = $i + 1;
		// 去掉数字最后一位了
		
		$num = $num / 10;
		// echo $num.'</br>';
		$num = ( int ) $num;
		// $num =floor($num);
		// $num = round ( $num, 0, PHP_ROUND_HALF_DOWN );
		// echo $num.'</br>';
		// 结束循环
		if ($num == 0) {
			break;
		}
	}
	// echo $c.'</br>';
	$j = 0;
	$slen = strlen ( $c );
	while ( $j < $slen ) {
		// utf8一个汉字相当3个字符
		$m = substr ( $c, $j, 6 );
		// 处理数字中很多0的情况,每次循环去掉一个汉字“零”
		if ($m == '零元' || $m == '零万' || $m == '零亿' || $m == '零零') {
			$left = substr ( $c, 0, $j );
			$right = substr ( $c, $j + 3 );
			$c = $left . $right;
			$j = $j - 3;
			$slen = $slen - 3;
		}
		$j = $j + 3;
	}
	// 这个是为了去掉类似23.0中最后一个“零”字
	if (substr ( $c, strlen ( $c ) - 3, 3 ) == '零') {
		$c = substr ( $c, 0, strlen ( $c ) - 3 );
	}
	// 将处理的汉字加上“整”
	if (empty ( $c )) {
		return "零元整";
	} else {
		return $c . "整";
	}
}

/**
 * 数据导出
 * @param array $title 标题行名称
 * @param array $data 导出数据
 * @param string $fileName 文件名
 * @param string $savePath 保存路径
 * @param $type 是否下载 false--保存 true--下载
 * @return string 返回文件全路径
 * @throws PHPExcel_Exception
 * @throws PHPExcel_Reader_Exception @exportExcel(array('姓名','年龄'), array(array('a',21),array('b',23)), '档案', './', true);
 */
function exportExcel($title = array(), $data = array(), $fileName = '', $savePath = './', $isDown = false) {
	Doo::loadClass ( 'PHPExcel1.7.9/PHPExcel' );
	$obj = new PHPExcel ();
	
	// 横向单元格标识
	$cellName = array (
			'A',
			'B',
			'C',
			'D',
			'E',
			'F',
			'G',
			'H',
			'I',
			'J',
			'K',
			'L',
			'M',
			'N',
			'O',
			'P',
			'Q',
			'R',
			'S',
			'T',
			'U',
			'V',
			'W',
			'X',
			'Y',
			'Z',
			'AA',
			'AB',
			'AC',
			'AD',
			'AE',
			'AF',
			'AG',
			'AH',
			'AI',
			'AJ',
			'AK',
			'AL',
			'AM',
			'AN',
			'AO',
			'AP',
			'AQ',
			'AR',
			'AS',
			'AT',
			'AU',
			'AV',
			'AW',
			'AX',
			'AY',
			'AZ' 
	);
	
	$obj->getActiveSheet ( 0 )->setTitle ( 'sheet名称' ); // 设置sheet名称
	$_row = 0; // 设置纵向单元格标识
	if ($title) {
		$_cnt = count ( $title );
		// $obj->getActiveSheet(0)->mergeCells('A'.$_row.':'.$cellName[$_cnt-1].$_row); //合并单元格
		// $obj->setActiveSheetIndex(0)->setCellValue('A'.$_row, '数据导出：'.date('Y-m-d H:i:s')); //设置合并后的单元格内容
		$_row ++;
		$i = 0;
		foreach ( $title as $v ) { // 设置列标题
			$obj->setActiveSheetIndex ( 0 )->setCellValue ( $cellName [$i] . $_row, $v );
			$i ++;
		}
		$_row ++;
	}
	;
	// html_entity_decode("&iuml;&raquo;&iquest;".$value['order_id']);
	
	// 填写数据
	if ($data) {
		$i = 0;
		foreach ( $data as $_v ) {
			$j = 0;
			foreach ( $_v as $_cell ) {
				$obj->getActiveSheet ( 0 )->setCellValueExplicit ( $cellName [$j] . ($i + $_row), $_cell, PHPExcel_Cell_DataType::TYPE_STRING );
				$j ++;
			}
			$i ++;
		}
	}
	
	// 文件名处理
	if (! $fileName) {
		$fileName = uniqid ( time (), true );
	}
	
	$objWrite = PHPExcel_IOFactory::createWriter ( $obj, 'Excel2007' );
	
	if ($isDown) { // 网页下载
		header ( 'pragma:public' );
		header ( "Content-Disposition:attachment;filename=$fileName.xls" );
		header ( 'Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		$objWrite->save ( 'php://output' );
		exit ();
	}
	
	$_fileName = iconv ( "utf-8", "gb2312", $fileName ); // 转码
	$_savePath = $savePath . $_fileName . '.xlsx';
	$objWrite->save ( $_savePath );
	
	return $savePath . $fileName . '.xlsx';
}

function cellColor($cells,$color,$obj){
	

	$obj->getActiveSheet()->getStyle($cells)->getFill()->applyFromArray(array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID,
			'startcolor' => array(
					'rgb' => $color
			)
	));
}

/**
 * 导出办事处全年汇中明细execl
 */
function excelRCData($summaryData,$aiAllList,$cglist,$year){
	Doo::loadClass ( 'PHPExcel1.7.9/PHPExcel' );
	$obj = new PHPExcel ();
	
	//总行数计算
	$onRow=4;
	$maxRow=(count($cglist)*14)+1+$onRow;
	
	//表头计算
	$cellCount=0;$sectionCount=array();$last=8;
	foreach ($aiAllList as $key=>$value){
		//栏目细则个数
		$cellCount+=count($value)+1;
		
		$sectionCount[$key]=array($last,count($value)+1+$last-1);
		$last=count($value)+1+$last;
	}
	$cellCount=$cellCount+7;
	//print_r($sectionCount);
	//设置宽高
	$colNum=1;$maxCol='ZZ';
	for ($col='A';$colNum<=$cellCount;$col++){
		$obj->getActiveSheet()->getColumnDimension($col)->setWidth(18);
		cellColor($col.'1', 'ddebf7',$obj);cellColor($col.'2', 'ddebf7',$obj);
		cellColor($col.'3', 'ddebf7',$obj);
		
		foreach ($sectionCount as $key=>$value){
			$falg=false;
			foreach ($value as $k=>$v){
				if($v==$colNum){
					$sectionCount[$key][$k]=$col;
					$falg=true;
					break;
				}
			}
			if($falg){
				break;
			}
		}
		
		if($colNum==$cellCount){
			$maxCol=$col;
		}
		$colNum++;
	}
	
	//print_r($sectionCount);DIE;
	
	$obj->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
	//设置字体居中
	$obj->getActiveSheet()->getStyle('A1:'.$maxCol.'3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	//设置边框
	$styleThinBlackBorderOutline = array('borders' => array(
					'allborders' => array( //设置全部边框
							'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
					),),);
	$obj->getActiveSheet()->getStyle( 'A1:'.$maxCol.$maxRow)->applyFromArray($styleThinBlackBorderOutline);
	
	//设置合并单元格
	$obj->getActiveSheet()->mergeCells('A1:A3');
	$obj->getActiveSheet()->mergeCells('B1:G2');
	$obj->getActiveSheet()->mergeCells('H1:'.$maxCol.'1');
	
	//表头内容
	$obj->getactivesheet()->setCellValue ( 'A1','费用项' );
	$obj->getactivesheet()->setCellValue ( 'B1','收入' );
	$obj->getactivesheet()->setCellValue ( 'B3','利润' );
	$obj->getactivesheet()->setCellValue ( 'C3','利润占比' );
	$obj->getactivesheet()->setCellValue ( 'D3','收入合计' );
	$obj->getactivesheet()->setCellValue ( 'E3','软件收入' );
	$obj->getactivesheet()->setCellValue ( 'F3','培训及其他收入' );
	$obj->getactivesheet()->setCellValue ( 'G3','费用合计' );
	//表头细则
	$obj->getactivesheet()->setCellValue ( 'H1','支出' );
	//print_r($sectionCount);die;
	foreach ($sectionCount as $key=>$value){
		$obj->getactivesheet()->setCellValue ( $value['0'].'2',$key);
		$obj->getActiveSheet()->getStyle($value['0'].'2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		$obj->getActiveSheet()->mergeCells($value['0'].'2:'.$value['1'].'2');
		$col=$value['0'];
		$obj-> getActiveSheet() -> getStyle($col.'3') -> getFont()->getColor()->setRGB('f70000');
		$obj->getactivesheet()->setCellValue ( $col.'3','合计');
		$col++;
		foreach ($aiAllList[$key] as $k=>$v){
			if($col>$value['1']){
				break;
			}
			$obj->getactivesheet()->setCellValue ( $col.'3',$v['name']);
			$col++;
		}
	}
	
	//数据填充
	//全年全选中办事处 金额合计
	$obj->getactivesheet()->setCellValue ( 'A'.$onRow,$year.'年合计' );
	$col='H';
	//合计行数计算
	$cgRow=array();$LS=0;
	foreach ($cglist as $cgk=>$value){
		$row=5+$LS;
		array_push($cgRow, $row);
		$LS+=14;
	}
	
	foreach ($aiAllList as $aiak=>$aiav){
		$sectionStart=$sectionCount[$aiak]['0'];
		$sectionStart++;
		
		$obj->getActiveSheet() -> getStyle($col.$onRow) -> getFont()->getColor()->setRGB('f70000');
		$obj->getactivesheet()->setCellValue( $col.$onRow, '=SUM('.$sectionStart.$onRow.':'.$sectionCount[$aiak]['1'].$onRow.')' );
		$col++;
		foreach ($aiav as $k=>$v){//项目细则
			$formula=array();
			foreach ($cgRow as $cgrk=>$cgrv){
				array_push($formula, $col.$cgrv);
			}
			$formula=implode(',', $formula);
			$obj->getactivesheet()->setCellValue( $col.$onRow, '=SUM('.$formula.')' );
			$col++;
		}
	}
	$onRow++;//行数增加
	
	foreach ($cglist as $cgk=>$value){
		
		//办事处年合计详情
		$obj->getactivesheet()->setCellValue ( 'A'.$onRow,$value['title'].'年合计' );
		$col='H';
		foreach ($aiAllList as $aiak=>$aiav){
			$sectionStart=$sectionCount[$aiak]['0'];
			$sectionStart++;
			$obj-> getActiveSheet() -> getStyle($col.$onRow) -> getFont()->getColor()->setRGB('f70000');
			$obj->getactivesheet()->setCellValue( $col.$onRow, '=SUM('.$sectionStart.$onRow.':'.$sectionCount[$aiak]['1'].$onRow.')' );
			$col++;
			foreach ($aiav as $k=>$v){//项目细则
				
				$sumRowStart=$onRow+2;
				$sumRowEnd=$sumRowStart+11;
				
				$obj->getactivesheet()->setCellValue( $col.$onRow, '=SUM('.$col.$sumRowStart.':'.$col.$sumRowEnd.')' );
				$col++;
			}
		}
		$onRow++;//行数增加
		
		//办事处年占比
		$obj->getactivesheet()->setCellValue ( 'A'.$onRow,$value['title'].'费用占比' );
		$col='H';
		foreach ($aiAllList as $aiak=>$aiav){
			$sectionStart=$sectionCount[$aiak]['0'];
			$sectionStart++;
			$sumRowStart=$onRow-1;
			$obj->getActiveSheet()->getStyle($col.$onRow)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);//设置百分比
			$obj-> getActiveSheet() -> getStyle($col.$onRow) -> getFont()->getColor()->setRGB('f70000');
			$obj->getactivesheet()->setCellValue( $col.$onRow, '='.$col.$sumRowStart.'/'.$col.'4' );
			$col++;
			foreach ($aiav as $k=>$v){//项目细则
				
				$obj->getActiveSheet()->getStyle($col.$onRow)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
				$obj->getactivesheet()->setCellValue( $col.$onRow, '='.$col.$sumRowStart.'/'.$col.'4' );
				$col++;
			}
		}
		
		$onRow++;//行数增加
		
		//办事处每月合计详情
		for($month=1;$month<=12;$month++){
			$obj->getactivesheet()->setCellValue ( 'A'.$onRow,$value['title'].$month.'月合计' );
			$col='H';
			if(isset($summaryData[$value['cid']])){
				foreach ($aiAllList as $aiak=>$aiav){//总项目
					$sectionStart=$sectionCount[$aiak]['0'];
					$sectionStart++;
					$obj-> getActiveSheet() -> getStyle($col.$onRow) -> getFont()->getColor()->setRGB('f70000');
					$obj->getactivesheet()->setCellValue( $col.$onRow, '=SUM('.$sectionStart.$onRow.':'.$sectionCount[$aiak]['1'].$onRow.')' );//公式
					$col++;
					foreach ($aiav as $k=>$v){//项目细则
						foreach ($summaryData[$value['cid']][$month][$aiak] as $dsk=>$dsv){//数据实体
							if($dsv['item']==$v['name']){
								$obj->getactivesheet()->setCellValue ( $col.$onRow,$dsv['price']);
								break;
							}
						}
						
						$col++;
					}
				}
			}
			
			$onRow++;
		}
		
	}
	

	
	
	$objWrite = PHPExcel_IOFactory::createWriter ( $obj, 'Excel2007' );
	header ( 'pragma:public' );
	header ( "Content-Disposition:attachment;filename=".$year."办事处费用明细.xlsx" );
	header ( 'Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
	$objWrite->save ( 'php://output' );
	exit ();
}


/*
 * 处理Excel导出
 *  @param $datas array 
 *  设置表格数据
 *   @param $titlename string 
 *   设置head @param $title string 设置表头
 */
function excelData($trainData, $receiptDetail, $summary, $aiList,$receiptLoanDetail,$invoiceList,$RItemList,$lecturerList,$invoiceTrainingDetail) {
	Doo::loadClass ( 'PHPExcel1.7.9/PHPExcel' );
	$obj = new PHPExcel ();
	
	
	//设置合并单元格
	$obj->getActiveSheet()->mergeCells('A1:D1');
	//设置居中--//字体加粗
	$obj->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$i=2;
	for(;$i<=20;$i++){
		$obj->getActiveSheet()->getStyle('A'.$i.':J'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		//$obj->getActiveSheet()->getStyle('A'.$i)->getFont()->setName('宋体')->setBold(true);
		//$obj->getActiveSheet()->getStyle('C'.$i)->getFont()->setName('宋体')->setBold(true);
	}
	
$styleThinBlackBorderOutline = array(
        'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
            ),

        ),
    );
    $obj->getActiveSheet()->getStyle( 'A1:D9')->applyFromArray($styleThinBlackBorderOutline);
    for ($i=1;$i<=9;$i++){
    	cellColor('A'.$i, 'ddebf7',$obj);
    }
    cellColor('C2', 'ddebf7',$obj);cellColor('C3', 'ddebf7',$obj);
    cellColor('C4', 'ddebf7',$obj);cellColor('C8', 'ddebf7',$obj);
    cellColor('C6', 'ddebf7',$obj);cellColor('C7', 'ddebf7',$obj);
    
	//设置单元格宽
	$obj->getActiveSheet()->getColumnDimension('A')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('B')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('C')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('D')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('E')->setWidth(25);
	$obj->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
	
	$obj->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体')->setBold(true);
	
	$obj->getactivesheet()->setCellValue ( 'A1','培训班信息' );
	
	$obj->getactivesheet()->setCellValue ( 'A2','培训班名称' );
	$obj->getactivesheet()->setCellValue ( 'B2',$trainData['trainName'] );
	$obj->getactivesheet()->setCellValue ( 'C2','所在办事处' );
	$obj->getactivesheet()->setCellValue ( 'D2',$trainData['categoryName'] );
	
	$obj->getactivesheet()->setCellValue ( 'A3','开班时间' );
	$obj->getactivesheet()->setCellValue ( 'B3',$trainData['trainStartDate'] );
	$obj->getactivesheet()->setCellValue ( 'C3','结束时间' );
	$obj->getactivesheet()->setCellValue ( 'D3',$trainData['trainEndDate'] );
	
	$obj->getactivesheet()->setCellValue ( 'A4','参加人数' );
	$obj->getactivesheet()->setCellValue ( 'B4',$trainData['participants'] );
	$obj->getactivesheet()->setCellValue ( 'C4','培训班地址' );
	autoCellsWidth($obj,'D',$trainData ['trainAddress']);
	$obj->getactivesheet()->setCellValue ( 'D4',$trainData['trainAddress'] );
	
	$obj->getactivesheet()->setCellValue ( 'A5','备注' );
	$obj->getActiveSheet()->mergeCells('B5:D5');
	$obj->getactivesheet()->setCellValue ( 'B5',$trainData['trainRemarks'] );
	
	$obj->getActiveSheet()->mergeCells('A6:D6');
	$obj->getActiveSheet()->getStyle('A6:D6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$obj->getactivesheet()->setCellValue ( 'A6','汇款信息' );
	
	$obj->getactivesheet()->setCellValue ( 'A7','汇款人户名' );
	$obj->getactivesheet()->setCellValue ( 'B7',$receiptDetail['remittanceName'] );
	$obj->getactivesheet()->setCellValue ( 'C7','汇款银行' );
	if(0==$receiptDetail['remittanceBankType']){
		$obj->getactivesheet()->setCellValue ( 'D7','广发银行' );
	}else{
		$obj->getactivesheet()->setCellValue ( 'D7',其他 );
	}
	
	$obj->getactivesheet()->setCellValue ( 'A8','汇款开户银行名称' );
	
	autoCellsWidth($obj,'B',$receiptDetail ['bankName']);
	
	$obj->getactivesheet()->setCellValue ( 'B8',$receiptDetail['bankName'] );
	$obj->getactivesheet()->setCellValue ( 'C8','汇款帐号' );
	$obj->getActiveSheet()->setCellValueExplicit('D8',$receiptDetail['bankNumber'],PHPExcel_Cell_DataType::TYPE_STRING);
	//$obj->getactivesheet()->setCellValue ( 'D7',$receiptDetail['bankNumber'].'' );
	
	if(0==$receiptDetail['remittanceBankType']){
		$obj->getactivesheet()->setCellValue ( 'A9','联行号' );
		$obj->getActiveSheet()->mergeCells('B9:D9');
		$obj->getactivesheet()->setCellValue ( 'B9',$receiptDetail['coupletNumber'] );
	}
	$obj->setActiveSheetIndex(0);
	$obj->getActiveSheet ()->setTitle ( '信息' );
	
	//------------------------------------------------------------------------------------------
	$obj->createSheet();
	$obj->setActiveSheetIndex(1);
	makeSummaryData($obj,$summary,$aiList);
	
	//------------------------------------------------------------------------------------------
	//绘制借款
	$obj->createSheet();
	$obj->setActiveSheetIndex(2);
	makeReceiptLoanData($obj,$receiptLoanDetail);
	
	//------------------------------------------------------------------------------------------
	//绘制收入
	$obj->createSheet();
	$obj->setActiveSheetIndex(3);
	makeInvoiceData($obj,$invoiceList,$invoiceTrainingDetail);
	
	//------------------------------------------------------------------------------------------
	//绘制支出
	$obj->createSheet();
	$obj->setActiveSheetIndex(4);
	makeItemData($obj,$RItemList);
	
	//------------------------------------------------------------------------------------------
	//绘制讲师
	$obj->createSheet();
	$obj->setActiveSheetIndex(5);
	makeLecturerData($obj,$lecturerList);
	
	$objWrite = PHPExcel_IOFactory::createWriter ( $obj, 'Excel2007' );
	header ( 'pragma:public' );
	header ( "Content-Disposition:attachment;filename=".$trainData['trainName'].".xlsx" );
	header ( 'Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
	$objWrite->save ( 'php://output' );
	exit ();
}


function makeLecturerData($obj,$lecturerList){

	//整体字体位置
	$invoiceCount=count($lecturerList)+2;

	for($i=1;$i<=$invoiceCount;$i++){
		$obj->getActiveSheet()->getStyle('A'.$i.':E'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		$obj->getActiveSheet()->getStyle('A'.$i.':E'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
		$obj->getActiveSheet()->getRowDimension($i)->setRowHeight(18);
	}
	$obj->getActiveSheet()->getColumnDimension('A')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('B')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('C')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('D')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('E')->setWidth(25);

	//边框
	$styleThinBlackBorderOutline = array(
			'borders' => array(
					'allborders' => array( //设置全部边框
							'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
					),
			),);
	$obj->getActiveSheet()->getStyle( 'A1:E'.$invoiceCount)->applyFromArray($styleThinBlackBorderOutline);

	cellColor('A1', 'ddebf7',$obj);cellColor('B1', 'ddebf7',$obj);
	cellColor('C1', 'ddebf7',$obj);cellColor('D1', 'ddebf7',$obj);cellColor('E1', 'ddebf7',$obj);

	$obj->getactivesheet()->setCellValue ( 'A1','类型' );
	$obj->getactivesheet()->setCellValue ( 'B1','讲师姓名' );
	$obj->getactivesheet()->setCellValue ( 'C1','授课日数' );
	$obj->getactivesheet()->setCellValue ( 'D1','日授课费' );
	$obj->getactivesheet()->setCellValue ( 'E1','合计应发授课费' );

	$i=2;$sumPrice=0;
	foreach ($lecturerList as $key=>$value){
		$lecturerName='';
		if($value['ltype']=='OUTSIDE'){
			$lecturerName.='外聘';
			if($value['taxation']==0){
				$lecturerName.='(不计税)';
			}else{
				$lecturerName.='(计税)';
			}
		}else{
			$lecturerName.='员工';
		}
		
		$obj->getactivesheet()->setCellValue ( 'A'.$i,$lecturerName );
		
		$obj->getactivesheet()->setCellValue ( 'B'.$i,$value['lecturerName'] );
		
		$obj->getactivesheet()->setCellValue ( 'C'.$i,$value['schoolDay'] );
		$obj->getactivesheet()->setCellValue ( 'D'.$i,$value['price'] );
		$obj->getactivesheet()->setCellValue ( 'E'.$i,$value['fees'] );
		$sumPrice+=$value['sum'];
		$i++;
	}
	$obj->getactivesheet()->setCellValue ( 'C'.$i,'合计' );
	$obj->getactivesheet()->setCellValue ( 'D'.$i,$sumPrice );
	$obj->getActiveSheet ()->setTitle ( '讲师' );
}

function makeItemData($obj,$itemList){

	//整体字体位置
	$invoiceCount=count($itemList)+2;

	for($i=1;$i<=$invoiceCount;$i++){
		$obj->getActiveSheet()->getStyle('A'.$i.':E'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		$obj->getActiveSheet()->getStyle('A'.$i.':E'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
		$obj->getActiveSheet()->getRowDimension($i)->setRowHeight(18);
	}
	$obj->getActiveSheet()->getColumnDimension('A')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('B')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('C')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('D')->setWidth(25);
	

	//边框
	$styleThinBlackBorderOutline = array(
			'borders' => array(
					'allborders' => array( //设置全部边框
							'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
					),
			),);
	$obj->getActiveSheet()->getStyle( 'A1:E'.$invoiceCount)->applyFromArray($styleThinBlackBorderOutline);

	cellColor('A1', 'ddebf7',$obj);cellColor('B1', 'ddebf7',$obj);
	cellColor('C1', 'ddebf7',$obj);cellColor('D1', 'ddebf7',$obj);
	cellColor('E1', 'ddebf7',$obj);

	$obj->getactivesheet()->setCellValue ( 'A1','日期' );
	$obj->getactivesheet()->setCellValue ( 'B1','类别' );
	$obj->getactivesheet()->setCellValue ( 'C1','金额' );
	$obj->getactivesheet()->setCellValue ( 'D1','经手人' );
	$obj->getactivesheet()->setCellValue ( 'E1','支出描述' );

	$i=2;$maxArray=array();$sumPrice=0;
	foreach ($itemList as $key=>$value){
		$obj->getactivesheet()->setCellValue ( 'A'.$i,$value['date'] );
		$obj->getactivesheet()->setCellValue ( 'B'.$i,$value['item'] );
		$obj->getactivesheet()->setCellValue ( 'C'.$i,$value['price'] );
		
		$obj->getactivesheet()->setCellValue ( 'D'.$i,$value['inputer'] );
		$obj->getactivesheet()->setCellValue ( 'E'.$i,$value['describe'] );
		array_push($maxArray, strlen($value['describe']));
		$sumPrice+=$value['price'];
		$i++;
	}
	
	$obj->getactivesheet()->setCellValue ( 'B'.$i,'合计' );
	$obj->getactivesheet()->setCellValue ( 'C'.$i,$sumPrice );
	$ma=0;
	if (!empty($maxArray)){
		$ma=max($maxArray);
	}
	$max=$ma-10;$fontlenght=25;
	
	if($max>25){
		$fontlenght=$max;
	}
	$obj->getActiveSheet()->getColumnDimension('E')->setWidth($fontlenght);
	
	$obj->getActiveSheet ()->setTitle ( '支出' );
}

function makeInvoiceData($obj,$invoiceList,$invoiceTrainingDetail){
	
	//整体字体位置
	$invoiceCount=count($invoiceList)+4;
	
	for($i=1;$i<=$invoiceCount;$i++){
		$obj->getActiveSheet()->getStyle('A'.$i.':I'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		$obj->getActiveSheet()->getStyle('A'.$i.':I'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
		$obj->getActiveSheet()->getRowDimension($i)->setRowHeight(18);
	}
	$obj->getActiveSheet()->getColumnDimension('A')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('B')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('C')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('D')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('E')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('F')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('G')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('H')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('I')->setWidth(25);
	
	//边框
	$styleThinBlackBorderOutline = array(
			'borders' => array(
					'allborders' => array( //设置全部边框
							'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
					),
			),);
	$obj->getActiveSheet()->getStyle( 'A1:I2')->applyFromArray($styleThinBlackBorderOutline);
	
	cellColor('A1', 'ddebf7',$obj);cellColor('B1', 'ddebf7',$obj);
	cellColor('C1', 'ddebf7',$obj);cellColor('D1', 'ddebf7',$obj);
	cellColor('E1', 'ddebf7',$obj);cellColor('F1', 'ddebf7',$obj);
	cellColor('H1', 'ddebf7',$obj);cellColor('G1', 'ddebf7',$obj);cellColor('I1', 'ddebf7',$obj);
	
	$obj->getactivesheet()->setCellValue ( 'A1','培训班名称' );
	$obj->getactivesheet()->setCellValue ( 'B1','办事处' );
	$obj->getactivesheet()->setCellValue ( 'C1','创建' );
	$obj->getactivesheet()->setCellValue ( 'D1','开班时间' );
	$obj->getactivesheet()->setCellValue ( 'E1','包含发票' );
	$obj->getactivesheet()->setCellValue ( 'F1','开票金额合计' );
	$obj->getactivesheet()->setCellValue ( 'G1','入账金额合计' );
	$obj->getactivesheet()->setCellValue ( 'H1','公司已收款' );
	$obj->getactivesheet()->setCellValue ( 'I1','办事处已收款' );
	
	if(!empty($invoiceTrainingDetail)){
		$obj->getactivesheet()->setCellValue ( 'A2',$invoiceTrainingDetail['trainName'] );
		$obj->getactivesheet()->setCellValue ( 'B2',$invoiceTrainingDetail['category'][0]['title'] );
		$obj->getactivesheet()->setCellValue ( 'C2',$invoiceTrainingDetail['staff']['username'] );
		$obj->getactivesheet()->setCellValue ( 'D2',$invoiceTrainingDetail['trainDate'] );
		$obj->getactivesheet()->setCellValue ( 'E2',$invoiceTrainingDetail['invoiceTotal'] );
		$obj->getactivesheet()->setCellValue ( 'F2',$invoiceTrainingDetail['invoiceTotalAmount'] );
		$obj->getactivesheet()->setCellValue ( 'G2',$invoiceTrainingDetail['invoiceArriveAmount'] );
		$obj->getactivesheet()->setCellValue ( 'H2',$invoiceTrainingDetail['invoiceCompanyAmount'] );
		$obj->getactivesheet()->setCellValue ( 'I2',$invoiceTrainingDetail['RIAmount'] );
	}
	
	
	//边框
	$styleThinBlackBorderOutline = array(
			'borders' => array(
					'allborders' => array( //设置全部边框
							'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
					),
			),);
	$obj->getActiveSheet()->getStyle( 'A4:H'.$invoiceCount)->applyFromArray($styleThinBlackBorderOutline);
	
	cellColor('A4', 'ddebf7',$obj);cellColor('B4', 'ddebf7',$obj);
	cellColor('C4', 'ddebf7',$obj);cellColor('D4', 'ddebf7',$obj);
	cellColor('E4', 'ddebf7',$obj);cellColor('F4', 'ddebf7',$obj);
	cellColor('H4', 'ddebf7',$obj);cellColor('G4', 'ddebf7',$obj);
	
	
	$obj->getactivesheet()->setCellValue ( 'A4','开票流水' );
	$obj->getactivesheet()->setCellValue ( 'B4','开票单位（抬头）' );
	$obj->getactivesheet()->setCellValue ( 'C4','发票号' );
	$obj->getactivesheet()->setCellValue ( 'D4','总额' );
	$obj->getactivesheet()->setCellValue ( 'E4','状态' );
	$obj->getactivesheet()->setCellValue ( 'F4','办事处收款' );
	$obj->getactivesheet()->setCellValue ( 'G4','收款流水号' );
	$obj->getactivesheet()->setCellValue ( 'H4','收款银行到账' );
	
	$i=5;$maxArray=array();
	foreach ($invoiceList as $key=>$value){
		$obj->getactivesheet()->setCellValue ( 'A'.$i,$value['invoiceSerial'] );
		
		$title='';
		if(!empty($value['invoiceType'])){
			$title=$value['invoiceCompany'];
		}else{
			$title=$value['invoiceTitle'];
		}
		$obj->getactivesheet()->setCellValue ( 'B'.$i,$title );
		
		$obj->getactivesheet()->setCellValue ( 'C'.$i,$value['invoiceNo'] );
		if(!empty($value['invoiceType'])){
			$obj->getactivesheet()->setCellValue ( 'D'.$i,'专'.$value['invoicePrice'] );
		}else{
			$obj->getactivesheet()->setCellValue ( 'D'.$i,$value['invoicePrice'] );
		}
		
		if($value['untreadStatus']==3){
			$obj->getactivesheet()->setCellValue ( 'E'.$i,'已入账' );
		}else{
			$obj->getactivesheet()->setCellValue ( 'E'.$i,'已出票' );
		}
		
		if($value['untreadStatus']==3){
			if($value['RIstatus']==1){
				$obj->getactivesheet()->setCellValue ( 'F'.$i,'已收款' );
			}
		}else{
			if($value['RIstatus']==1){
				$obj->getactivesheet()->setCellValue ( 'F'.$i,'已收款' );
			}
		}
		$receivablesSerial='';
		$receivablesDate='';$hang=0;
		if($value['untreadStatus']==3){
			foreach ($value['irDetail'] as $k=>$v){
				$receivablesSerial.=$v['receivablesSerial']."\r\n";
				$receivablesDate.=$v['receivablesDate']."\r\n";
				$hang+=8;
			}
			if($hang>8){
				$obj->getActiveSheet()->getRowDimension($i)->setRowHeight(18+$hang);
			}
			
		}
		$obj->getActiveSheet()->getStyle('H'.$i)->getAlignment()->setWrapText(true);
		$obj->getActiveSheet()->getStyle('G'.$i)->getAlignment()->setWrapText(true);
		
		$obj->getactivesheet()->setCellValue ( 'G'.$i,$receivablesSerial );
		$obj->getactivesheet()->setCellValue ( 'H'.$i,$receivablesDate );
		
		
		//按字体多少自动拉长单元格
		array_push($maxArray, strlen($title));
		
		$i++;
	}
	$ma=0;
	if (!empty($maxArray)){
		$ma=max($maxArray);
	}
	$max=$ma-10;$fontlenght=25;
	if($max>25){
		$fontlenght=$max;
	}
	$obj->getActiveSheet()->getColumnDimension('B')->setWidth($fontlenght);
	
	
	$obj->getActiveSheet ()->setTitle ( '收入' );
}

function makeReceiptLoanData($obj,$receiptLoanList){
	if (empty($receiptLoanList)){
		$obj->getActiveSheet ()->setTitle ( '借款' );
		return ;
	}
	
	$base=$lnBase=0;$lnKey=1;$lnBase=14;
	//定义行倍数
	$lnKey2=2;$lnKey3=3;$lnKey4=4;$lnKey5=5;
	$lnKey6=6;$lnKey7=7;$lnKey8=8;$lnKey9=9;
	$lnKey10=10;$lnKey11=11;$lnKey12=12;$lnKey13=13;
	$lnKey14=14;
	
	$obj->getActiveSheet()->getColumnDimension('A')->setWidth(35);
	$obj->getActiveSheet()->getColumnDimension('B')->setWidth(35);
	foreach ($receiptLoanList as $key=>$receiptLoanDetail){
		$lineNumber=1;
		$lineNumber=$lineNumber+$base;
		$maxBase=$base+16;
		//echo $maxBase.'<br/>';
		for (;$lineNumber<=$maxBase;$lineNumber++){
			$lineNumberDynamic = 'ln'.$lineNumber;
			$$lineNumberDynamic=$lineNumber;
			
			$obj->getActiveSheet()->getStyle('A'.$lineNumber.':B'.$lineNumber)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
			$obj->getActiveSheet()->getStyle('A'.$lineNumber.':B'.$lineNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
			$obj->getActiveSheet()->getRowDimension($lineNumber)->setRowHeight(18);
		}
		
		//正文
		$receiptLoanDetail ['loanItem'] = json_decode ( $receiptLoanDetail ['loanItem'], true );
		$receiptLoanDetail ['sumCN'] = num_to_rmb ( $receiptLoanDetail ['sum'] );
		$receiptLoanDetail ['loanSumCN'] = num_to_rmb ( $receiptLoanDetail ['loanSum'] );
		$receiptLoanDetail ['amountCN'] = num_to_rmb ( $receiptLoanDetail ['amount'] );
		//echo $receiptLoanDetail ['sumCN'].'<br/>';
		//边框
		$styleThinBlackBorderOutline = array(
				'borders' => array(
						'allborders' => array( //设置全部边框
								'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
						),
				),);
		$obj->getActiveSheet()->getStyle( 'A'.${'ln'.$lnKey}.':B'.${'ln'.$lnBase})->applyFromArray($styleThinBlackBorderOutline);
		
		
		//绘制颜色
		cellColor('A'.${'ln'.$lnKey}, 'ddebf7',$obj);cellColor('A'.${'ln'.$lnKey3}, 'ddebf7',$obj);
		cellColor('A'.${'ln'.$lnKey2}, 'ddebf7',$obj);cellColor('A'.${'ln'.$lnKey6}, 'ddebf7',$obj);
		cellColor('A'.${'ln'.$lnKey7}, 'ddebf7',$obj);cellColor('A'.${'ln'.$lnKey8}, 'ddebf7',$obj);
		cellColor('A'.${'ln'.$lnKey9}, 'ddebf7',$obj);cellColor('A'.${'ln'.$lnKey10}, 'ddebf7',$obj);
		cellColor('A'.${'ln'.$lnKey5}, 'ddebf7',$obj);cellColor('A'.${'ln'.$lnKey11}, 'ddebf7',$obj);
		
		//正文内容
		$obj->getActiveSheet()->mergeCells('A'.${'ln'.$lnKey}.':B'.${'ln'.$lnKey});
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey},'借款费用' );
		$obj->getActiveSheet()->getStyle('A'.${'ln'.$lnKey})->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$obj->getActiveSheet()->mergeCells('A'.${'ln'.$lnKey3}.':B'.${'ln'.$lnKey3});
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey3},'产生费用所在办事处' );
		$obj->getActiveSheet()->getStyle('A'.${'ln'.$lnKey3})->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$obj->getActiveSheet()->mergeCells('A'.${'ln'.$lnKey5}.':B'.${'ln'.$lnKey5});
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey5},'汇款信息' );
		$obj->getActiveSheet()->getStyle('A'.${'ln'.$lnKey5})->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$obj->getActiveSheet()->mergeCells('A'.${'ln'.$lnKey11}.':B'.${'ln'.$lnKey11});
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey11},'费用说明' );
		$obj->getActiveSheet()->getStyle('A'.${'ln'.$lnKey11})->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey2},'借款费用' );
		$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey2},$receiptLoanDetail ['loanItem'] ['借款费用'] [0] ['price'] );
		
		$obj->getActiveSheet()->mergeCells('A'.${'ln'.$lnKey4}.':B'.${'ln'.$lnKey4});
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey4},$receiptLoanDetail ['category'] [0] ['title'] );
		
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey6},'汇款人户名' );
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey7},'汇款银行' );
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey8},'汇款开户银行名称' );
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey9},'汇款帐号' );
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey10},'联行号' );
		
		$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey6},$receiptLoanDetail ['remittanceName'] );
		if ($receiptLoanDetail ['remittanceBankType'] == 0){
			$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey7},'广发银行' );
		}elseif ($receiptLoanDetail ['remittanceBankType'] == 1){
			$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey7},'其他' );
		}
		
		autoCellsWidth($obj,'B',$receiptLoanDetail ['bankName']);
		
		$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey8},$receiptLoanDetail ['bankName'] );
		$obj->getActiveSheet()->setCellValueExplicit('B'.${'ln'.$lnKey9},$receiptLoanDetail ['bankNumber'],PHPExcel_Cell_DataType::TYPE_STRING);
		//$obj->getactivesheet()->setCellValue ( 'B9',$receiptLoanDetail ['bankNumber'] );
		if ($receiptLoanDetail ['remittanceBankType'] == 1){
			$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey10},$receiptLoanDetail ['coupletNumber'] );
		}else{
			$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey10},'' );
		}
		
		$obj->getActiveSheet()->mergeCells('A'.${'ln'.$lnKey12}.':B'.${'ln'.$lnKey12});
		$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey12},$receiptLoanDetail ['explanation'] );
		
		$obj->getActiveSheet()->getStyle('A'.${'ln'.$lnKey13}.':B'.${'ln'.$lnKey13})->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$obj->getActiveSheet()->getStyle('A'.${'ln'.$lnKey14}.':B'.${'ln'.$lnKey14})->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey13},'（' . $receiptLoanDetail ['receiptOrder'] . '）本次费用合计' );
		$obj->getActiveSheet()->getStyle('B'.${'ln'.$lnKey13})->getAlignment()->setWrapText(true);
		$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey13},"￥" . $receiptLoanDetail ['sum'] . "\r\n" . $receiptLoanDetail ['sumCN']  );
		$obj->getActiveSheet()->getRowDimension(${'ln'.$lnKey13})->setRowHeight(36);
		
		$obj->getactivesheet()->setCellValue ( 'A'.${'ln'.$lnKey14},'汇款金额');
		$obj->getActiveSheet()->getStyle('B'.${'ln'.$lnKey14})->getAlignment()->setWrapText(true);
		$obj->getactivesheet()->setCellValue ( 'B'.${'ln'.$lnKey14},"￥" . $receiptLoanDetail ['amount'] . "\r\n" . $receiptLoanDetail ['amountCN']  );
		$obj->getActiveSheet()->getRowDimension(${'ln'.$lnKey14})->setRowHeight(36);
		
		//绘制计算
		$lnKey=$lnBase+2;$lnKey2=$lnBase+3;$lnKey3=$lnBase+4;$lnKey4=$lnBase+5;
		$lnKey5=$lnBase+6;$lnKey6=$lnBase+7;$lnKey8=$lnBase+9;$lnKey9=$lnBase+10;
		$lnKey10=$lnBase+11;$lnKey11=$lnBase+12;$lnKey12=$lnBase+13;$lnKey14=$lnBase+15;
		$lnKey7=$lnBase+8;$lnKey13=$lnBase+14;
		$lnBase=$lnKey+14;
		
		
		$base+=15;
	}
	//die;
	$obj->getActiveSheet ()->setTitle ( '借款' );
	
}



//绘制汇总
function makeSummaryData($obj,$summary,$aiList){
	//设置居中
	//$obj->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	for($i=1;$i<=35;$i++){
		$obj->getActiveSheet()->getStyle('A'.$i.':J'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		if ($i>=19){
			$obj->getActiveSheet()->getStyle('A'.$i.':B'.$i)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
		}
	}
	
	//设置单元格宽
	$obj->getActiveSheet()->getColumnDimension('A')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('B')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('C')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('D')->setWidth(25);
	$obj->getActiveSheet()->getColumnDimension('E')->setWidth(25);
	$obj->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
	
	$obj->getActiveSheet()->mergeCells('A1:A2');
	$obj->getActiveSheet()->mergeCells('B1:C1');
	$obj->getActiveSheet()->mergeCells('D1:E1');
	$obj->getActiveSheet()->mergeCells('C2:C3');
	$obj->getActiveSheet()->mergeCells('E2:E3');
	$obj->getActiveSheet()->mergeCells('A6:E6');
	$obj->getActiveSheet()->mergeCells('A10:E10');
	$obj->getActiveSheet()->mergeCells('A15:E15');
	$obj->getActiveSheet()->mergeCells('A16:E16');
	//支出详情
	$obj->getActiveSheet()->mergeCells('A18:C18');
	$obj->getActiveSheet()->mergeCells('A19:A35');
	
	cellColor('A1', 'ddebf7',$obj);cellColor('A2', 'ddebf7',$obj);
	cellColor('B1', 'ddebf7',$obj);cellColor('B2', 'ddebf7',$obj);
	cellColor('C2', 'ddebf7',$obj);cellColor('E2', 'ddebf7',$obj);
	cellColor('D1', 'ddebf7',$obj);cellColor('D2', 'ddebf7',$obj);
	cellColor('E3', 'ddebf7',$obj);cellColor('C3', 'ddebf7',$obj);
	for ($i=3;$i<=15;$i++){
		cellColor('A'.$i, 'ddebf7',$obj);
	}
	//支出详情
	cellColor('A18', 'ddebf7',$obj);cellColor('D18', 'ddebf7',$obj);
	cellColor('E18', 'ddebf7',$obj);cellColor('A19', 'ddebf7',$obj);
	
	for ($i=19;$i<=35;$i++){
		cellColor('B'.$i, 'ddebf7',$obj);
	}
	
	//边框
	$styleThinBlackBorderOutline = array(
			'borders' => array(
					'allborders' => array( //设置全部边框
							'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
					),
	
			),
	);
	$obj->getActiveSheet()->getStyle( 'A1:E16')->applyFromArray($styleThinBlackBorderOutline);
	//支出详情
	$obj->getActiveSheet()->getStyle( 'A18:E35')->applyFromArray($styleThinBlackBorderOutline);
	
	$obj->getactivesheet()->setCellValue ( 'A1','汇总项目' );
	$obj->getactivesheet()->setCellValue ( 'B1','收入按开票金额计算' );
	$obj->getactivesheet()->setCellValue ( 'D1','收入按入账金额计算' );
	$obj->getactivesheet()->setCellValue ( 'B2','金额' );
	$obj->getactivesheet()->setCellValue ( 'C2','占比' );
	$obj->getactivesheet()->setCellValue ( 'D2','金额' );
	$obj->getactivesheet()->setCellValue ( 'E2','占比' );
	
	$obj->getactivesheet()->setCellValue ( 'A3','收入' );
	$obj->getactivesheet()->setCellValue ( 'A4','支出' );
	$obj->getactivesheet()->setCellValue ( 'A5','利润' );
	$obj->getactivesheet()->setCellValue ( 'A6','收入-支出=利润' );
	
	$obj->getactivesheet()->setCellValue ( 'A7','公司利润' );
	$obj->getactivesheet()->setCellValue ( 'A8','办事处利润' );
	$obj->getactivesheet()->setCellValue ( 'A9','公司收款' );
	$obj->getactivesheet()->setCellValue ( 'A10','收入×40%=办事处利润   收入×60%=公司利润' );
	
	$obj->getactivesheet()->setCellValue ( 'A11','备用金（借款）' );
	$obj->getactivesheet()->setCellValue ( 'A12','办事处收款' );
	$obj->getactivesheet()->setCellValue ( 'A13','办事处实际支出' );
	$obj->getactivesheet()->setCellValue ( 'A14','付总部款' );
	$obj->getactivesheet()->setCellValue ( 'A15','备用金+办事处收款-办事处实际支出=付总部款' );
	
	//支出详情
	$obj->getactivesheet()->setCellValue ( 'A18','项目' );
	$obj->getactivesheet()->setCellValue ( 'D18','金额' );
	$obj->getactivesheet()->setCellValue ( 'E18','占入账金额比例' );
	$obj->getactivesheet()->setCellValue ( 'A19','支出明细' );
	
	$startKey=19;
	foreach ($aiList as $key=>$value){
		$obj->getActiveSheet()->getStyle('C'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$obj->getActiveSheet()->getStyle('E'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		if($value['name']=='讲课费'){
			$obj->getactivesheet()->setCellValue ( 'B'.$startKey,$value['name'] );
			$obj->getActiveSheet()->mergeCells('B'.$startKey.':B'.($startKey+2));
			$obj->getActiveSheet()->getStyle('C'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$obj->getActiveSheet()->getStyle('D'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,'外聘' );
			$obj->getactivesheet()->setCellValue ( 'D'.$startKey,$value['outsideTaxPrice'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['outsideTaxProportion'].'%' );
			$startKey+=1;
			$obj->getActiveSheet()->getStyle('D'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getActiveSheet()->getStyle('E'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,'外聘(不计税)' );
			$obj->getactivesheet()->setCellValue ( 'D'.$startKey,$value['outsideNoTaxPrice'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['outsideNoTaxProportion'].'%' );
			$startKey+=1;
			$obj->getActiveSheet()->getStyle('D'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getActiveSheet()->getStyle('E'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,'员工' );
			$obj->getactivesheet()->setCellValue ( 'D'.$startKey,$value['insidePrice'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['insideProportion'].'%' );
			
		}elseif($value['name']=='办公费'){
			$obj->getActiveSheet()->getStyle('C'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$obj->getActiveSheet()->getStyle('D'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getactivesheet()->setCellValue ( 'B'.$startKey,$value['name'] );
			$obj->getActiveSheet()->mergeCells('B'.$startKey.':B'.($startKey+1));
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,'办事处支出' );
			$obj->getactivesheet()->setCellValue ( 'D'.$startKey,$value['categoryPrice'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['categoryProportion'].'%' );
			$startKey=$startKey+1;
			$obj->getActiveSheet()->getStyle('D'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getActiveSheet()->getStyle('E'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getactivesheet()->setCellValue ( 'D'.$startKey,$value['companyPrice'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['companyProportion'].'%' );
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,'公司支出' );
			
		}elseif($value['name']=='邮寄费'){
			$obj->getActiveSheet()->getStyle('C'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$obj->getActiveSheet()->getStyle('D'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getactivesheet()->setCellValue ( 'B'.$startKey,$value['name'] );
			$obj->getActiveSheet()->mergeCells('B'.$startKey.':B'.($startKey+1));
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,'办事处支出' );
			$obj->getactivesheet()->setCellValue ( 'D'.$startKey,$value['categoryPrice'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['categoryProportion'].'%' );
			$startKey=$startKey+1;
			$obj->getActiveSheet()->getStyle('D'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getActiveSheet()->getStyle('E'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$obj->getactivesheet()->setCellValue ( 'D'.$startKey,$value['companyPrice'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['companyProportion'].'%' );
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,'公司支出' );
		}else{
			$obj->getActiveSheet()->mergeCells('C'.$startKey.':D'.$startKey);
			$obj->getactivesheet()->setCellValue ( 'B'.$startKey,$value['name'] );
			$obj->getactivesheet()->setCellValue ( 'C'.$startKey,$value['priceFormat'] );
			$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$value['proportion'].'%' );
		}
		$startKey++;
	}
	//小计
	$obj->getActiveSheet()->getStyle('C'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	$obj->getActiveSheet()->getStyle('E'.$startKey)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	$obj->getActiveSheet()->mergeCells('C'.$startKey.':D'.$startKey);
	$obj->getactivesheet()->setCellValue ( 'B'.$startKey,'小计' );
	$obj->getactivesheet()->setCellValue ( 'C'.$startKey,$summary[11]['total'] );
	$obj->getactivesheet()->setCellValue ( 'E'.$startKey,$summary[11]['totalProportion'].'%' );
	
	$startKey=3;
	//内容
	foreach ($summary as $key=>$value){
		$k=$key+$startKey;
		if($k==3){
			$obj->getactivesheet()->setCellValue ( 'B'.$k,$value['invoiceTotalAmount'] );
			$obj->getactivesheet()->setCellValue ( 'D'.$k,$value['invoiceArriveAmount'] );
		}elseif($k==15){
			$k+=1;
			$obj->getactivesheet()->setCellValue ( 'A'.$k,'学员：'.$value['participants'].'人   讲师：'.$value['lectureCount'].' 人' );
		}elseif($k==16){
			
		}elseif($k==6||$k==10){
			$k+=1;
			$startKey+=1;
			$obj->getactivesheet()->setCellValue ( 'B'.$k,$value[1] );
			$obj->getactivesheet()->setCellValue ( 'C'.$k,$value[2] );
			$obj->getactivesheet()->setCellValue ( 'D'.$k,$value[3] );
			$obj->getactivesheet()->setCellValue ( 'E'.$k,$value[4] );
		}else{
			$obj->getactivesheet()->setCellValue ( 'B'.$k,$value[1] );
			$obj->getactivesheet()->setCellValue ( 'C'.$k,$value[2] );
			$obj->getactivesheet()->setCellValue ( 'D'.$k,$value[3] );
			$obj->getactivesheet()->setCellValue ( 'E'.$k,$value[4] );
				
		}
	
	}
	
	$obj->getActiveSheet ()->setTitle ( '汇总表' );
}

function autoCellsWidth($obj,$cellsNub,$str){
	$max=strlen($str)-10;$fontlenght=35;
	if($max>35){
		$fontlenght=$max;
	}
	$obj->getActiveSheet()->getColumnDimension($cellsNub)->setWidth($fontlenght);
}

?>