<?php
$page_title="科目余额表—恩格会计软件";
$t1=microtime(true);//页面执行起始时间；
include("header.php");
//****************************************请求权限****************************************
require("locked.php");                                                                 //*
setlocked(10);//科目余额表                                                                //*
//****************************************************************************************
include("cnn.php");
?>

<?php 
if(($_SERVER['REQUEST_METHOD']=='POST')&&isset($_POST[qj0])&&isset($_POST[qj1])){
	//提交查询页面；
	echo '<form name="form1" method="post">
	<div align="center">
	<table>
	<caption><strong><font size="+2">科目余额表查找条件</font></strong></caption>
	<tr>
	<td>期间：</td>
	<td><input name="qj0" type="text" style="width:160px" value="'.$_POST[qj0].'" /></td>
	<td>—</td>
	<td><input name="qj1" type="text" style="width:160px" value="'.$_POST[qj1].'" ondblclick="autoQj()" /></td>
	</tr>
	<tr>
	<td>会计科目：</td>
	<td><input name="km0" type="text" style="width:160px" value="'.$_POST[km0].'" /></td>
	<td>—</td>
	<td><input name="km1" type="text" style="width:160px" value="'.$_POST[km1].'" ondblclick="autoKm()" /></td>
	</tr>';
	
	echo '<tr>
	      <td>包含未记账：</td>';
	if($_POST[jz]==1){
		echo '<td colspan="3">
			<select name="jz">
			<option value="1" selected="selected">是</option>
			<option value="0">否</option>
			</select>
			</td>';
	}else{
		echo '<td colspan="3">
			<select name="jz">
			<option value="0" selected="selected">否</option>
			<option value="1">是</option>
			</select>
			</td>';
	}
	echo '</tr>';
	
	echo '<tr>
	      <td>显示数量外币：</td>';
	if($_POST[slwb]==1){
		echo '<td colspan="3">
			<select name="slwb">
			<option value="1" selected="selected">是</option>
			<option value="0">否</option>
			</select>
			</td>';
	}else{
		echo '<td colspan="3">
			<select name="slwb">
			<option value="0" selected="selected">否</option>
			<option value="1">是</option>
			</select>
			</td>';
	}
	echo '</tr>';
	
	if($_POST[xm]==1){
		echo '<tr>
	        <td>包含项目明细：</td>
		    <td colspan="3">
			<select name="xm">
			<option value="1" selected="selected">是</option>
			<option value="0">否</option>
			</select>
			</td>
			</tr>';
		echo '<tr>
			<td>查找项目名称：</td>
			<td colspan="3">
			<input name="xms" type="text" style="width:160px" value="'.$_POST[xms].'" />
			</td>
			</tr>';
	}else{
		echo '<tr>
	        <td>包含项目明细：</td>
			<td colspan="3">
			<select name="xm">
			<option value="0" selected="selected">否</option>
			<option value="1">是</option>
			</select>
			</td>
			</tr>';
		echo '<tr>
			<td>查找项目名称：</td>
			<td colspan="3">
			<input name="xms" type="text" style="width:160px" value="'.$_POST[xms].'" />
			</td>
			</tr>';
	}
	echo '<tr>
	<td colspan="4" align="center"><input id="tj1" name="tj" type="button" onclick="tjcx();" value="提交" /></td>
	</tr>
	</table>
	</div>
	</form>';
	
	//提交数据成功，先构造查询凭证表的SQL语句，然后将查询到的相关数据写入table；
	$qj0=$mysqli->real_escape_string($_POST[qj0]);
	$qj1=$mysqli->real_escape_string($_POST[qj1]);
	$strsql='参数列表：'.$qj0.';'.$qj1.';'.$_POST[km0].';'.$_POST[km1].';'.$_POST[jz].';'.$_POST[slwb].';'.$_POST[xm].';'.$_POST[xms];
	
	echo '<hr width="95%">';
	echo '<div id="ts" align="center">'.$strsql.'</div>';//显示参数列表及处理时间；
	echo '<hr width="95%">';
	
	//调用生成科目余额表的存储过程，生成kmyrb临时表；
	//kmyrbs存储过程参数：起始期间，终止期间，起始科目，终止科目，是否记账，是否包含项目，是否统计期初数：（1表示统计期初数）；
	//是否统计期初数仅用于查询科目明细时使用，查询科目明细时，期初数由本界面期初数通过GET给值；
	$mysqli->query("call kmyrbs('".$qj0."','".$qj1."','".$_POST[km0]."','".$_POST[km1]."','".$_POST[jz]."','".$_POST[xm]."','1')");
	//如果包含项目明细，则用字段t来分组区分那些是科目数据，那些是项目数据；
	if($_POST[xm]==1 and $_POST[xms]!=''){//模糊查找项目名称；
		$strsql='select km,kma,dc,sum(slwb0) as slwb0,sum(dr0) as dr0,sum(cr0) as cr0,sum(ds1) as ds1,sum(dr1) as dr1,sum(cs1) as cs1,sum(cr1) as cr1 from kmyrb';
		$strsql=$strsql.' where kma like "%'.$_POST[xms].'%" group by km,kma,dc,t order by km,kma;';
	}else{
		$strsql='select km,kma,dc,sum(slwb0) as slwb0,sum(dr0) as dr0,sum(cr0) as cr0,sum(ds1) as ds1,sum(dr1) as dr1,sum(cs1) as cs1,sum(cr1) as cr1 from kmyrb';
		$strsql=$strsql.' group by km,kma,dc,t order by km,kma;';
	}
	$sql=$mysqli->query($strsql);
    $info=$sql->fetch_array(MYSQLI_BOTH);
    if($info){ //如果存在数据；
	    echo '<div align="center">
			  <table width="95%">
			  <tr><td>
			  <a href="V_1.php" target="_blank">新增凭证</a>|
			  <a href="javascript:CopyTable(\'tbl1\');">复制数据</a>|
			  <a href="javascript:void(MyPreview());">打印数据</a>
			  </td></tr>
			  </table>
			  </div>
			  <div id="div1" align="center">
			  <table id="tbl1" border="1" width="95%" cellspacing="0">
			  <caption><strong><font size="+2">科目余额表</font></strong></caption>';
		if($_POST[slwb]==1){
			//含数量外币列；
			echo '<thead><tr>
				  <td align="center">会计科目</td>
				  <td align="center">科目全称</td>
				  <td align="center">方向</td>
				  <td align="center">期初数量</td>
				  <td align="center">期初单价</td>
				  <td align="center">期初余额</td>
				  <td align="center">借方数量</td>
				  <td align="center">借方单价</td>
				  <td align="center">借方金额</td>
				  <td align="center">贷方数量</td>
				  <td align="center">贷方单价</td>
				  <td align="center">贷方金额</td>
				  <td align="center">期末数量</td>
				  <td align="center">期末单价</td>
				  <td align="center">期末余额</td>
				  </tr></thead>';
		}else{
			//不含数量外币列；
			echo '<thead><tr>
				  <td align="center">会计科目</td>
				  <td align="center">科目全称</td>
				  <td align="center">方向</td>
				  <td align="center">期初余额</td>
				  <td align="center">借方金额</td>
				  <td align="center">贷方金额</td>
				  <td align="center">期末余额</td>
				  </tr></thead>';
		}
		
		do{
			if($_POST[slwb]==1){//含数量外币列；
				if($info[dc]=="借方"){
					echo '<tr onmouseover="trMouseover(this)" onmouseout="trMouseout(this)">';
					if(substr_count($info[kma],"/")==0){
						echo '<td align="left"><a href="javascript:mxs('.$_POST[qj0].','.$_POST[qj1].','.$info[km].','.$info[slwb0].','.@round($info[dr0]-$info[cr0],2).','.$_POST[jz].');">'.$info[km].'</a></td>';
					}else{
						echo '<td align="left">'.$info[km].'</td>';	
					}
					echo '<td align="left">'.$info[kma].'</td>';
					echo '<td align="center">'.$info[dc].'</td>';
					echo '<td align="center">'.@round($info[slwb0],2).'</td>';
					echo '<td align="right">'.@number_format(($info[dr0]-$info[cr0])/$info[slwb0],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr0]-$info[cr0],2).'</td>';
					echo '<td align="center">'.@round($info[ds1],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr1]/$info[ds1],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr1],2).'</td>';
					echo '<td align="center">'.@round($info[cs1],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr1]/$info[cs1],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr1],2).'</td>';
					echo '<td align="center">'.@round($info[slwb0]+$info[ds1]-$info[cs1],2).'</td>';
					echo '<td align="right">'.@number_format(($info[dr0]-$info[cr0]+$info[dr1]-$info[cr1])/($info[slwb0]+$info[ds1]-$info[cs1]),2).'</td>';
					echo '<td align="right">'.@number_format($info[dr0]-$info[cr0]+$info[dr1]-$info[cr1],2).'</td>';
					echo '</tr>';
				}else{
					echo '<tr onmouseover="trMouseover(this)" onmouseout="trMouseout(this)">';
					if(substr_count($info[kma],"/")==0){
						echo '<td align="left"><a href="javascript:mxs('.$_POST[qj0].','.$_POST[qj1].','.$info[km].','.$info[slwb0].','.@round($info[cr0]-$info[dr0],2).','.$_POST[jz].');">'.$info[km].'</a></td>';
					}else{
						echo '<td align="left">'.$info[km].'</td>';
					}
					echo '<td align="left">'.$info[kma].'</td>';
					echo '<td align="center">'.$info[dc].'</td>';
					echo '<td align="center">'.@round($info[slwb0],2).'</td>';
					echo '<td align="right">'.@number_format(($info[cr0]-$info[dr0])/$info[slwb0],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr0]-$info[dr0],2).'</td>';
					echo '<td align="center">'.@round($info[ds1],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr1]/$info[ds1],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr1],2).'</td>';
					echo '<td align="center">'.@round($info[cs1],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr1]/$info[cs1],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr1],2).'</td>';
					echo '<td align="center">'.@round($info[slwb0]-$info[ds1]+$info[cs1],2).'</td>';
					echo '<td align="right">'.@number_format(($info[cr0]-$info[dr0]+$info[cr1]-$info[dr1])/($info[slwb0]-$info[ds1]+$info[cs1]),2).'</td>';
					echo '<td align="right">'.@number_format($info[cr0]-$info[dr0]+$info[cr1]-$info[dr1],2).'</td>';
					echo '</tr>';
				}
			}else{//不包含数量外币列；
				if($info[dc]=="借方"){
					echo '<tr onmouseover="trMouseover(this)" onmouseout="trMouseout(this)">';
					if(substr_count($info[kma],"/")==0){
						echo '<td align="left"><a href="javascript:mxs('.$_POST[qj0].','.$_POST[qj1].','.$info[km].',\'a\','.@round($info[dr0]-$info[cr0],2).','.$_POST[jz].');">'.$info[km].'</a></td>';
					}else{
						echo '<td align="left">'.$info[km].'</td>';	
					}
					echo '<td align="left">'.$info[kma].'</td>';
					echo '<td align="center">'.$info[dc].'</td>';
					echo '<td align="right">'.@number_format($info[dr0]-$info[cr0],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr1],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr1],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr0]-$info[cr0]+$info[dr1]-$info[cr1],2).'</td>';
					echo '</tr>';
				}else{
					echo '<tr onmouseover="trMouseover(this)" onmouseout="trMouseout(this)">';
					if(substr_count($info[kma],"/")==0){
						echo '<td align="left"><a href="javascript:mxs('.$_POST[qj0].','.$_POST[qj1].','.$info[km].',\'a\','.@round($info[cr0]-$info[dr0],2).','.$_POST[jz].');">'.$info[km].'</a></td>';
					}else{
						echo '<td align="left">'.$info[km].'</td>';
					}
					echo '<td align="left">'.$info[kma].'</td>';
					echo '<td align="center">'.$info[dc].'</td>';
					echo '<td align="right">'.@number_format($info[cr0]-$info[dr0],2).'</td>';
					echo '<td align="right">'.@number_format($info[dr1],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr1],2).'</td>';
					echo '<td align="right">'.@number_format($info[cr0]-$info[dr0]+$info[cr1]-$info[dr1],2).'</td>';
					echo '</tr>';
				}
			}
			
	    }while($info=$sql->fetch_array(MYSQLI_BOTH));
	    echo '</table></div>';
	}else{
		echo '<div align="center">未检索到相关数据；</div>';
	}
?>
<script language="javascript" type="text/javascript">
//限制频繁点击提交按钮，再次提交需要等待三秒；
document.getElementById("tj1").disabled=true;
setTimeout("getTj()",3000)
function getTj(){
	document.getElementById("tj1").disabled=false;
}
</script>
<?PHP
}else{//非提交查询页面；
    $strsql='select distinct qj from v1 order by qj desc';
	$sql=$mysqli->query($strsql);
    $info=$sql->fetch_array(MYSQLI_BOTH);
	if($info){
		echo '<form name="form1" method="post">
		<div align="center">
		<table>
		<caption><strong><font size="+2">科目余额表查找条件</font></strong></caption>
		<tr>
		<td>期间：</td>
		<td><input name="qj0" type="text" style="width:160px" value="'.$info[qj].'"/></td>
		<td>—</td>
		<td><input name="qj1" type="text" style="width:160px" ondblclick="autoQj()" value="'.$info[qj].'"/></td>
		</tr>
		<tr>
		<td>会计科目：</td>
		<td><input name="km0" type="text" style="width:160px" /></td>
		<td>—</td>
		<td><input name="km1" type="text" style="width:160px" ondblclick="autoKm()" /></td>
		</tr>
		<tr>
		<td>包含未记账：</td>
		<td colspan="3">
		<select name="jz">
		<option value="1" selected="selected">是</option>
		<option value="0">否</option>
		</select>
		</td>
		</tr>
		<tr>
		<td>显示数量外币：</td>
		<td colspan="3">
		<select name="slwb">
		<option value="0" selected="selected">否</option>
		<option value="1">是</option>
		</select>
		</td>
		</tr>
		<tr>
		<td>包含项目明细：</td>
		<td colspan="3">
		<select name="xm">
		<option value="0" selected="selected">否</option>
		<option value="1">是</option>
		</select>
		</td>
		</tr>
		<tr>
		<td>查找项目名称：</td>
		<td colspan="3">
		<input name="xms" type="text" style="width:160px" />
		</td>
		</tr>
		<tr>
		<td colspan="4" align="center"><input name="tj" type="button" onclick="tjcx();" value="提交" /></td>
		</tr>
		</table>
		</div>
		</form>';
	}else{
		echo '<form name="form1" method="post">
		<div align="center">
		<table>
		<caption><strong><font size="+2">操作提示</font></strong></caption>
		<tr>
		<td>凭证表中暂无任何数据！</td>
		</tr>
		</table>
		</div>
		</form>';
	}
}
$sql->free();
$mysqli->close();
?>

<script language="javascript" src="LodopFuncs.js"></script>

<script language="javascript" type="text/javascript">
<!--
//自动将起始期间填入终止期间；
function autoQj(){
	form1.qj1.value=form1.qj0.value;
}
//自动将起始会计科目填入终止期间；
function autoKm(){
	form1.km1.value=form1.km0.value;
}
//提交查询凭证条件；
function tjcx(){
	//查找凭证时必须保证期间条件完整；
	if(form1.qj0.value==""){
		alert("请输入起始期间！");
		form1.qj0.focus();
		return false;
	}
	
	if(isNaN(form1.qj0.value)){
		alert("起始期间必须为数字！");
		form1.qj0.focus();
		return false;
	}
	
	if(form1.qj1.value==""){
		alert("请输入终止期间！");
		form1.qj1.focus();
		return false;
	}
	
	if(isNaN(form1.qj1.value)){
		alert("终止期间必须为数字！");
		form1.qj1.focus();
		return false;
	}
	
	if(form1.qj0.value>form1.qj1.value){
		alert("终止期间小于起始期间！");
		form1.qj1.focus();
		return false;
	}

	form1.submit();
}

function CopyTable(tableid){
//在IE下，将数据复制到剪切板上；在非IE下，直接把数据导出CSV文件；
var tbl=document.getElementById(tableid);
try{
    var arr=new Array();
	for(var i=0,j=tbl.rows.length;i<j;i++){
		for(var m=0,n=tbl.rows[i].cells.length;m<n;m++){
			arr.push(tbl.rows[i].cells[m].innerText);
			if(m<n-1)arr.push("\t");
		}
		if(i<j-1)arr.push("\n");
	}
	var str=arr.join("");
	window.clipboardData.setData('text',str);
	alert('成功复制表格数据，请打开EXCEL粘贴数据！');
}catch(error){
	var str="";
	for(var i=0,j=tbl.rows.length;i<j;i++){
		for(var m=0,n=tbl.rows[i].cells.length;m<n;m++){
		    if(m==0){
				str=str+"\""+tbl.rows[i].cells[m].innerText+"\""
			}else{
			    str=str+",\""+tbl.rows[i].cells[m].innerText+"\""
			}
		}
		str=str+"\n";
	}
	
	var uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURI(str);
	
	var downloadLink = document.createElement("a");
	downloadLink.href = uri;
	downloadLink.download = "export.csv";
	
	document.body.appendChild(downloadLink);
	downloadLink.click();
	document.body.removeChild(downloadLink);
	
}finally{
	
}
}

function mxs(qj0,qj1,km,slwb,yr,jz){
	var aW=screen.availWidth;
	var aH=screen.availHeight;
	strkey='qj0='+qj0+'&qj1='+qj1+'&km='+km+'&slwb='+slwb+'&yr='+yr+'&jz='+jz;
	var urlstr='Vbook_kmyrbsub.php?'+strkey;
	window.open(urlstr,'','left=0,top=0,width='+aW+',height='+aH+',scrollbars=yes,resizable=yes');
}

var strLastColor;//记录鼠标经过表格行前表格原来的底色；

function trMouseover(tableTr){//鼠标经过表格行时的颜色；
    strLastColor=tableTr.style.background;
	tableTr.style.background="yellow";
}

function trMouseout(tableTr){//鼠标离开表格行时的颜色；
	tableTr.style.background=strLastColor;
}

var LODOP; //声明为全局变量

function MyPreview(){
	LODOP=getLodop();
	LODOP.PRINT_INIT("科目余额表打印");
	LODOP.SET_PRINT_PAGESIZE (2,0,0,"A4");
	LODOP.ADD_PRINT_TABLE("2%","2%","100%","100%",document.getElementById("div1").innerHTML);
	LODOP.PREVIEW();
}

//自动加载高亮显示；
HighLight();

//-->
</script>

<?php 
$t2=microtime(true);//页面执行终止时间；
//给HTML控件赋值；
echo '<script language="javascript" type="text/javascript">document.getElementById("ts").innerHTML+="，耗时 '.round($t2-$t1,4).' 秒。";</script>';
	
include("footer.php");
?>