<?php 
session_start();

require('../cfg/res.php');
require('../cfg/cfg.php');

include('php_gene1.php');

$xacceso=0;
$sql_acce='select * from usrdoc1 where (docu like "DC%" or docu like "CH%" ) and info="1" and user="'.$_SESSION['user_vcrx_id'].'" ';
$res_acce=mysql_query($sql_acce,$link_vcrx);
if (mysql_num_rows($res_acce)!=0){$xacceso=1;}
if (mb_stristr('sist.supe',$_SESSION['user_vcrx_tipo'])){$xacceso=1;}
if ($xacceso==0){echo '<font size="8" color="red"><b>';die('No tiene acceso a Saldos Bancarios ' );}	
	


$fini1= date('2000-01-01');
$sql_fini1='select * from empr0';
$res_fini1=mysql_query($sql_fini1,$link_vcrx);
$fini1=mysql_result($res_fini1,0,'empr_fbcos');
//echo '-'.$fini1.'-';

$fecha1 = date('Y-m-j');
$nuevafecha = strtotime ( '-120 month' , strtotime ( $fecha1 ) ) ;
$xfecha1 = date ( 'Y/m/j' , $nuevafecha );
$xfecha1= date('Y/m/'.'1');
$xfecha2= date('Y/m/d');


//	if (mb_stristr('gnya',$_SESSION['user_vcrx_empr']))
//	{$fini1=date('2010-11-01');}


	$enti=$_POST['enti'];
	if(isset($_POST['fecha_ini']) && !empty($_POST['fecha_ini']))
	{$fini=$_POST['fecha_ini'];}
	else
	{$fini=$fini1;}
	
	if(isset($_POST['fecha_fin']) && !empty($_POST['fecha_fin']))
	{$ffin=$_POST['fecha_fin'];}
	else
	{$ffin=date('Y-m-d');}

if(isset($_POST['fecha_ini']) && !empty($_POST['fecha_ini']) && isset($_POST['fecha_fin']) && !empty($_POST['fecha_fin']))
{
	$xfecha1=$_POST['fecha_ini'];
	$xfecha2=$_POST['fecha_fin'];
}



// if(isset($_POST['fcero']) && ($_POST['fcero']=='false'))
// {$xfcero=$_POST['fcero'];}
// else
// {$xfcero='true';}

// if(isset($_POST['fcord']) && ($_POST['fcord']=='false'))
// {$xfcord=$_POST['fcord'];}
// else
// {$xfcord='true';}

if(isset($_POST['fcom0']) && ($_POST['fcom0']=='false'))
{$xfcom0=$_POST['fcom0'];}
else
{$xfcom0='true';}

if(isset($_POST['fcom1']) && ($_POST['fcom1']=='false'))
{$xfcom1=$_POST['fcom1'];}
else
{$xfcom1='true';}

if(isset($_POST['fcom2']) && ($_POST['fcom2']=='false'))
{$xfcom2=$_POST['fcom2'];}
else
{$xfcom2='true';}



// if(isset($_POST['fdtr1']) && ($_POST['fdtr1']=='false'))
// {$xfdtr1=$_POST['fdtr1'];}
// else
// {$xfdtr1='true';}

// if(isset($_POST['f2col']) && ($_POST['f2col']=='true'))
// {$xf2col=$_POST['f2col'];}
// else
// {$xf2col='false';}



$sql_where='cant!=0 and fecha >= "'.$fini1.'" AND fecha <= "'.$xfecha2.'"';
//$sql_where='fecha <= "'.$xfecha2.'"';

$xfrepor='C';
if(isset($_POST['frepor']) && !empty($_POST['frepor']))
{
	$xfrepor=$_POST['frepor'];
}

if ($xfcom1=='true'){$xfrepor='C';}


// if($xfrepor=='B')
// {
// 	//$sql_where.=' and(ctan1="1" or ctan1="2" or ctan1="3") ';
// 	$sql_where.=' and(ctan1<="3" or ctan1>="8") ';
// 	if($xfcord=='false')
// 		{$sql_where.=' and(ctan1<="3") ';}
// }

// if($xfrepor=='R')
// {
// 	$sql_where.=' and fecha >= "'.$xfecha1.'" ';
// //	$sql_where.=' and(ctan1="4" or ctan1="5" or ctan1="6") ';
// 	$sql_where.=' and(ctan1>="4" and ctan1<="7") ';
// 	if($xfdtr1=='false')
// 		{$sql_where.=' and(docu not like "DTR%") ';}
// }

if($xfrepor=='S' or $xfrepor=='T')
{
	$sql_where.=' ';
}



$xfnivel='5';
// if(isset($_POST['fnivel']) && !empty($_POST['fnivel']))
// {$xfnivel=$_POST['fnivel'];}



if(isset($_POST['ctab']) && !empty($_POST['ctab']))
{
// 	$sql_where.=' and  (ctab like "'.$_POST['ctab'].'%" or ctab_descri like "%'.$_POST['ctab'].'%" ) ';
	$sql_where.=' and  (ctab like "'.$_POST['ctab'].'") ';
	$xfctab=$_POST['ctab'];
}

if(isset($_POST['fenti']) && !empty($_POST['fenti']))
{
	$sql_where.=' and  (enti like "'.$_POST['fenti'].'%" or enti_descri like "%'.$_POST['enti'].'%" ) ';
	$xfenti=$_POST['fenti'];
}

// if(isset($_POST['fitem']) && !empty($_POST['fitem']))
// {
// 	$sql_where.=' and  (item like "'.$_POST['fitem'].'%" or item_descri like "%'.$_POST['item'].'%" ) ';
// 	$xfenti=$_POST['fitem'];
// }


// if(isset($_POST['flsub']) && !empty($_POST['flsub']))
// {
// 	$sql_where.=' and  (item_lsub like "'.$_POST['flsub'].'%" or lsub_descri like "%'.$_POST['flsub'].'%" ) ';
// 	$xflsub=$_POST['flsub'];
// }


// if(isset($_POST['fzsub']) && !empty($_POST['fzsub']))
// {
// 	$sql_where.=' and  (item like "%'.$_POST['fzsub'].'%" ) ';
// 	$xfzsub=$_POST['fzsub'];
// }




if(isset($_POST['fctlg']) && !empty($_POST['fctlg']))
{
//	$sql_bcoss.=' and  (item_nctlg ='.$_POST['fctlg'].' )';
//	$xfctlg=$_POST['fctlg'];
}


// $sql_bcoss='';
// $sql_bcoss.='           select bcos12.*, 1 as nivel, ctan1 as ctab_codi, descri1 as ctab_desc, sum(tcant) as saldo, sum(tingreso) as tdeb, sum(tegreso) as tcre from bcos12 where '.$sql_where.' group by ctan1 ';
// if ($xfnivel>=2)
// {$sql_bcoss.=' union '.'select bcos12.*, 2 as nivel, ctan2 as ctab_codi, descri2 as ctab_desc, sum(tcant) as saldo, sum(tingreso) as tdeb, sum(tegreso) as tcre from bcos12 where '.$sql_where.' group by ctan2 '; }
// if ($xfnivel>=3)
// {$sql_bcoss.=' union '.'select bcos12.*, 3 as nivel, ctan3 as ctab_codi, descri3 as ctab_desc, sum(tcant) as saldo, sum(tingreso) as tdeb, sum(tegreso) as tcre from bcos12 where '.$sql_where.' group by ctan3 ';}
// if ($xfnivel>=4)
// {$sql_bcoss.=' union '.'select bcos12.*, 4 as nivel, ctan4 as ctab_codi, descri4 as ctab_desc, sum(tcant) as saldo, sum(tingreso) as tdeb, sum(tegreso) as tcre from bcos12 where '.$sql_where.' group by ctan4 ';}
// if ($xfnivel>=5)
// {$sql_bcoss.=' union '.'select bcos12.*, 5 as nivel, ctan5 as ctab_codi, descri5 as ctab_desc, sum(tcant) as saldo, sum(tingreso) as tdeb, sum(tegreso) as tcre from bcos12 where '.$sql_where.' group by ctan5 ';}

// echo $sql_where;

$sql_bcoss='select bcos12.*,
			sum(case when fecha<"'.$xfecha1.'" then (tingreso-tegreso) end) as saldoi, 
			sum(case when fecha>="'.$xfecha1.'" and fecha<="'.$xfecha2.'" then tingreso end) as tdeb, 
			sum(case when fecha>="'.$xfecha1.'" and fecha<="'.$xfecha2.'" then tegreso end) as tcre, 
			sum(tingreso-tegreso) as saldo
			from bcos12
			where '.$sql_where;

// $res_bcosst=mysql_query($sql_bcoss,$link_vcrx);
$sql_bcosst=$sql_bcoss;
if(mb_strstr('C',$xfrepor))
{$sql_bcoss.=' group by ctab';}

if(mb_strstr('F',$xfrepor))
{$sql_bcoss.=' group by ctab,fecha';}

// if(mb_strstr('T',$xfrepor))
// {
// $sql_bcoss='select bcos12.*,
// 			'.$xfnivel.' as nivel,
// 			ctan'.$xfnivel.' as ctab_codi,
// 			descri'.$xfnivel.' as ctab_desc,
// 			sum(case when fecha<"'.$xfecha1.'" then tcant end) as saldoi, 
// 			sum(case when fecha>="'.$xfecha1.'" and fecha<="'.$xfecha2.'" then tingreso end) as tdeb, 
// 			sum(case when fecha>="'.$xfecha1.'" and fecha<="'.$xfecha2.'" then tegreso end) as tcre, 
// 			sum(tcant) as saldo
// 			from bcos12
// 			where '.$sql_where;

// // $res_bcosst=mysql_query($sql_bcoss,$link_vcrx);
// $sql_bcosst=$sql_bcoss;
// $sql_bcoss.=' group by ctab';
	
// $xfniv=$xfnivel-1;	
// //echo $xfniv;
// while ($xfniv>0)
// {
// //echo $sql_bcoss;
// 	$sql_bcoss.=' union '.
// 			'select bcos12.*,
// 			'.$xfniv.' as nivel,
// 			ctan'.$xfniv.' as ctab_codi,
// 			descri'.$xfniv.' as ctab_desc,
// 			sum(case when fecha<"'.$xfecha1.'" then tcant end) as saldoi, 
// 			sum(case when fecha>="'.$xfecha1.'" and fecha<="'.$xfecha2.'" then tingreso end) as tdeb, 
// 			sum(case when fecha>="'.$xfecha1.'" and fecha<="'.$xfecha2.'" then tegreso end) as tcre, 
// 			sum(tcant) as saldo
// 			from bcos12
// 			where '.$sql_where;
// 	$sql_bcoss.=' group by ctan'.$xfniv;
// 	$xfniv--;
// }	
// }


// if(mb_strstr('BR',$xfrepor))
// {
// $sql_bcosst='select bcos12.*,1 as nivel, ctan1 as ctab_codi, descri1 as ctab_desc, sum(tcant) as saldo, sum(tingreso) as tdeb, sum(tegreso) as tcre from bcos12 where '.$sql_where.' group by ctan1';
// $res_bcosst=mysql_query($sql_bcosst,$link_vcrx);
// }


$xmeses=0;
$xwidth = 100;
if ($xfcom1=='true' || $xfcom2=='true')
{
		$xfecha1=date('Y-m-01',strtotime($xfecha1));
		$xfecha2=date('Y-m-t', strtotime($xfecha2));
		$xf2col='false';
		$sql_meses='select distinct fanio,fmes,fmesn from bcos11 where fecha>="'.$xfecha1.'" and fecha<="'.$xfecha2.'" order by fanio,fmes';
		$res_meses=mysql_query($sql_meses,$link_vcrx);
		$xmeses = mysql_num_rows($res_meses);
		$xmes1 = $xmeses -11;
		if($xmes1<=0){$xmes1=1;}
		$xcmes=0;
		$sql_pivot='';
		while ($r_mes0=mysql_fetch_assoc($res_meses))	
		{
			$xcmes++;
			$xfanio=$r_mes0['fanio'];
			$xfmes=$r_mes0['fmes'];

			if ($xcmes==$xmes1){$xfecha1c=date($xfanio.'-'.$xfmes.'-01');}
			if ($xcmes>=$xmes1)
			{
// 			$colmes = $xfanio.$xfmes;	
			$colmes = $xcmes-$xmes1+1;	
			if ($colmes<10){$colmes='0'.$colmes;}
// 			$sql_pivot.='sum(case when fanio='.$xfanio.' and fmes='.$xfmes.' then tcant end) as s'.$colmes.',';	
			$sql_pivot.='sum(case when concat(fanio,fmes)<='.$xfanio.$xfmes.' then tcant end) as s'.$colmes.',';	
			$sql_pivot.='sum(case when fanio='.$xfanio.' and fmes='.$xfmes.' then tingreso end) as d'.$colmes.',';	
			$sql_pivot.='sum(case when fanio='.$xfanio.' and fmes='.$xfmes.' then tegreso end) as c'.$colmes.',';	
			}
		}

	$sql_bcoss=str_replace($xfecha1,$xfecha1c,$sql_bcoss);
	$sql_bcoss=str_replace('bcos12.*,','bcos12.*,'.$sql_pivot,$sql_bcoss);

	$sql_bcosst=str_replace($xfecha1,$xfecha1c,$sql_bcosst);
	$sql_bcosst=str_replace('bcos12.*,','bcos12.*,'.$sql_pivot,$sql_bcosst);
	// 	echo $sql_bcoss;
	if($xmeses>6){$xwidth = 100 + ($xmeses-6)*10;}
	
}


// $sql_bcoss.=' order by ctab_codi,nivel,fanio,fmes ';
$sql_bcoss.=' order by ctab,fecha ';
$res_bcoss=mysql_query($sql_bcoss,$link_vcrx);
$res_bcosst=mysql_query($sql_bcosst,$link_vcrx);
// echo explain_sql( $sql_bcoss);
// echo str_repeat('-',date('s')*2).'';
// echo date('H:m:s');
?>


<table id="tabla_salbcos" width="<?php echo $xwidth?>%" border="0" cellspacing="0" cellpadding="2" style="border-collapse:collapse;font-size:12px; border:#000000 solid 1px;">

<tr style="border-bottom:#000000 solid 2px; ">

	<?php if($xfcom1=='true' || $xfcom2=='true') {  ?>
				<th></th>
				<th>CODIGO</th>
				<th>CUENTA</th>
		<?php if(mb_stristr('F',$xfrepor)){?>
<!-- 				<th>FECHA</th> -->
		<?php }?>
				<th>INICIAL</th>
	<?php	
// 		$sql_meses='select distinct fanio,fmes,fmesn from bcos12 where fecha<="'.$xfecha2.'" order by fanio,fmes';
		$res_meses=mysql_query($sql_meses,$link_vcrx);
		$xmeses = mysql_num_rows($res_meses);
		$xmes1 = $xmeses -11;
		$xcmes=0;
		if($xmes1<=0){$xmes1=1;}
		while ($r_mes0=mysql_fetch_assoc($res_meses))	
		{
			$xcmes++;
			if ($xcmes>=$xmes1){echo '<th>'.$r_mes0['fmesn'].' /'.substr($r_mes0['fanio'],2,4).'</th>';};
		}
	?>
			<th></th>

	<?php }else{	?>

	<?php if(mb_strstr('CF',$xfrepor)) {  ?>
				<th></th>
				<th>CODIGO</th>
				<th>CUENTA</th>
	<?php if(mb_strstr('F',$xfrepor)) {  ?>
				<th>FECHA</th>
  <?php }	?>
				<th>INICIAL</th>
				<th>DEBITOS</th>
				<th>CREDITOS</th>
				<th>SALDO</th>
				<th></th>

	<?php }	?>
	<?php } ?>
	
</tr>




<?php 
$filax=0;
$univel=0;
// explain_sql($sql_bcoss);
	
while($r_bcoss=mysql_fetch_assoc($res_bcoss))
{
	$duni=2;
if(1==1)
	{
	$xfont='';
	$xtabs='';
	?>
	<tr valign="top" class="filax" id="<?php echo $r_ped['idnum1']; ?>" align="center" style=" <?php if($filax>=4){echo 'border-top:dotted 1px #000;';$filax=1;}else{$filax++;} ?>" >
		<td>
				<a href="../lib/impr_bcoss0d.php?grupo=C&idnum=<?php echo $r_bcoss['ctab']; ?>&fecha_ini=<?php echo $fini; ?>&fecha_fin=<?php echo $ffin; ?>" target="_blank"><img src="http://vcrxol.com/_img/print2.png" width="20"  title="Imprimir  <?php echo $r_bcoss['ctab'];?>"  /></a>
		</td>

		<td align="center"><a href="#" onClick="bcoss1d('C','<?php echo $r_bcoss['ctab']; ?>',this);">
				<?php echo'<span title="'.$r_bcoss['ctab_desc'].'">'.$r_bcoss['ctab'].'</span>';?></a>
		</td>
		<td align="center"><?php echo'<span title="'.$r_bcoss['ctab_descri'].'">'.$r_bcoss['ctab_descri'].'</span>';?></td>

	<?php if(mb_strstr('F',$xfrepor)) {  ?>
		<td align="center"><?php echo'<span title="'.$r_bcoss['fecha'].'">'.$r_bcoss['fdian'].' '.$r_bcoss['fechadmy'].'</span>';?></td>
  <?php }	?>

		<?php if ($xfcom1=='true' || $xfcom2=='true'){ ?> 
		<?php if(mb_stristr('CF',$xfrepor)){ ?>
		<?php 	echo '<td align="right">'.number_format($r_bcoss['saldoi'],2).'</td>'; ?>
		<?php } ?>

					<?php	
						if ($xmeses>12){$xmeses=12;}
						for ($cmes=1;$cmes<=$xmeses;$cmes++)
						{
							$xcmes=$cmes;
							if ($xcmes<10){$xcmes='0'.$cmes;}
							if ($xfcom1=='true'){echo '<td align="right">'.number_format($r_bcoss['s'.$xcmes],2).'</td>';}
							if ($xfcom2=='true'){echo '<td align="right">'.number_format($r_bcoss['d'.$xcmes]-$r_bcoss['c'.$xcmes],2).'</td>';}
						}
					?>
		
		<?php }else{ ?> 
		<?php	if(mb_strstr('CF',$xfrepor)){	?>
				<td align="right">
					<?php
// 					if((mb_stristr('1.5.6.7.8',$r_bcoss['ctan1']) and $r_bcoss['saldoi']<0) 
// 					or (mb_stristr('2.3.4.9',$r_bcoss['ctan1']) and $r_bcoss['saldoi']>0) 
// 					)
					if($r_bcoss['saldoi']<0)	
					{echo '<font color="red">';} 
					?>
					<?php echo'<span >'.number_format($r_bcoss['saldoi'],2).'</span>';?>
				</td>
				<td align="right"><?php echo'<span >'.number_format($r_bcoss['tdeb'],2).'</span>';?></td>
				<td align="right"><?php echo'<span >'.number_format($r_bcoss['tcre'],2).'</span>';?></td>
				<td align="right">
					<?php
// 					if((mb_stristr('1.5.6.7.8',$r_bcoss['ctan1']) and $r_bcoss['saldo']<0) 
// 					or (mb_stristr('2.3.4.9',$r_bcoss['ctan1']) and $r_bcoss['saldo']>0) 
// 					)
					if($r_bcoss['saldo']<0)	
					{echo '<font color="red">';} 
					?>
					<?php echo'<span >'.number_format($r_bcoss['saldo'],2).'</span>';?>
				</td>
		<?php } ?>


 		<?php } ?> 

	</tr>
	<?php }?>
<?php }?>


		

  <?php
  if(mb_strstr('ST',$xfrepor))
  {
  ?>
  <tr align="center" style="background: #F7F7F7; border-top:solid 1px#000;border-bottom:solid 1px#000">

	<?php if($xfcom1=='true' || $xfcom2=='true') {  ?>
			<th></th>
			<th></th>
			<th>TOTALES</th>
	<?php	
		$res_bcosst=mysql_query($sql_bcosst,$link_vcrx);

						for ($cmes=1;$cmes<=$xmeses;$cmes++)
						{
							$xcmes=$cmes;
							if ($xcmes<10){$xcmes='0'.$cmes;}
							if ($xfcom1=='true'){echo '<td align="right">'.number_format($r_bcosst['s'.$xcmes],2).'</td>';}
							if ($xfcom2=='true'){echo '<td align="right">'.number_format($r_bcosst['d'.$xcmes]-$r_bcosst['c'.$xcmes],2).'</td>';}
// 							echo '<th align="right">'.$xfont.number_format(mysql_result($res_bcosst,0,'s'.$xcmes),2).'</th>';
						}
	?>

			<th></th>
		
	<?php }
		else{ ?>		
		
			<th></th>
			<th></th>
			<th>TOTALES</th>
			<th align="right"><?php echo number_format(mysql_result($res_bcosst,0,'saldoi'),2);?></th>
			<th align="right"><?php echo number_format(mysql_result($res_bcosst,0,'tdeb'),2);?></th>
			<th align="right"><?php echo number_format(mysql_result($res_bcosst,0,'tcre'),2);?></th>
			<th align="right"><?php echo number_format(mysql_result($res_bcosst,0,'saldo'),2);?></th>
	<?php } ?>		

	</tr>

	<?php }?>





</table>
</div>
<br>




