<?php
/* Copyright (C) 2016-2020  Laurent Destailleur		<eldy@users.sourceforge.net>
 * Copyright (C) 2016-2019  Alexandre Spangaro		<aspangaro@open-dsi.fr>
 * Copyright (C) 2019-2021  Frédéric France			<frederic.france@netlogic.fr>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */

/**
 * \file    htdocs/accountancy/index.php
 * \ingroup Accountancy (Double entries)
 * \brief   Home accounting module
 */

if (false === (@include '../../main.inc.php')) {  // From htdocs directory
	require '../../../main.inc.php'; // From "custom" directory
}
require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
require_once DOL_DOCUMENT_ROOT.'/core/class/html.formother.class.php';

// Load translation files required by the page
$langs->loadLangs(array("compta", "bills", "other", "accountancy", "loans", "banks", "admin", "dict"));

// Initialize technical object to manage hooks. Note that conf->hooks_modules contains array of hooks
$hookmanager->initHooks(array('accountancyindex'));

// Security check
if ($user->socid > 0) {
	accessforbidden();
}

if (empty($conf->accounting->enabled)) {
	accessforbidden();
}
if (empty($user->rights->accounting->mouvements->lire)) {
	accessforbidden();
}

if (empty($conf->comptabilite->enabled) && empty($conf->accounting->enabled) && empty($conf->asset->enabled) && empty($conf->intracommreport->enabled)) {
	accessforbidden();
}
if (empty($user->rights->compta->resultat->lire) && empty($user->rights->accounting->comptarapport->lire) && empty($user->rights->accounting->mouvements->lire) && empty($user->rights->asset->read) && empty($user->rights->intracommreport->read)) {
	accessforbidden();
}

// Load traductions files requiredby by page
$langs->load("companies");
$langs->load("other");
// Get parameters
if (empty(GETPOST('periodo')) ){
	$periodo =date('m Y');
}else{
	$periodo = GETPOST('periodo');
}



$chk_todas = GETPOST('chk_todas');

$arrayofjs = array( '/../custom/sydinitprocess/js/functions.js');

/*
 * View
 */

llxHeader('','Balanza de Comprobacion','','','','',$arrayofjs,'',0,0);

// $per = new Contabperiodos($db);
// $per->fetch_by_period($anio, $mes);

?>
<h1 align="center"><?=$conf->global->MAIN_INFO_SOCIETE_NOM?></h1>
<h1 align="center">Balanza de Comprobacion</h1>
<form id="chargeForm" method="POST">
<?php
$sel1 = "<select name='periodo' id='periodo' onchange='document.getElementById(".'"chargeForm"' .").submit();' >";
		$res = $db->query("Select rowid,label, date_start, date_end From ".MAIN_DB_PREFIX."accounting_fiscalyear where entity=".$conf->entity."");
		$sel1 .= '<option value="" >Seleccione</option>';
		while ($obj = $db->fetch_object($res)) {
			$sel1 .= "<option value='".$obj->label."'";
			if ($obj->label == $periodo ) {
				$sel1 .= " selected='selected' ";
			}
			$sel1 .= ">".$obj->label."</option>";
		}
		$sel1 .= "</select> ";

?>
<h3>Periodo contable: <?=$sel1;?></h3>
</form>
<form>
	<input type="hidden" name="a" value=<?=$anio;?> />
	<input type="hidden" name="m" value=<?=$mes;?> />
	<table class="noborder" width="100%">
		<tr class="liste_titre">
			<td colspan="2">&nbsp;</td>
			<td colspan="2" style="text-align: right">
				<input type="checkbox" name="chk_todas" <?=($chk_todas ? ' checked="checked" ' : '');?> onchange="this.form.submit()">Mostrar todas las cuentas
			</td> 
			<td style="text-align: right">
				<a href="balanza_comp_print.php?tipo=excel&a=<?=$anio;?>&m=<?=$mes;?>&t=<?=$chk_todas?>" target="popup">
					Descargar Excel
				</a>
			</td>
			<td style="text-align: right">
				<a href="balanza_comp_print.php?tipo=pdf&a=<?=$anio;?>&m=<?=$mes;?>&t=<?=$chk_todas?>" target="popup">
					Descargar PDF
				</a>
			</td>
		</tr>
		<tr class="liste_titre">
			<td style="width: 50%" colspan="2">Cuenta</td>
			<td style="width: 15%; text-align:right">Saldo<br>Inicial</td>
			<td style="width: 10%;text-align:right">Debe</td>
			<td style="width: 10%;text-align:right">Haber</td>
			<td style="width: 15%; text-align:right">Saldo<br>Actual</td>
		</tr>


<?php 
	$debe_total = 0;
	$haber_total = 0;
	$saldo_ini_tot = 0;

	$anio = substr($periodo,3,4);
	$mes = substr($periodo,0,2);

	$periodoant = date('Y') . $mes -1;
	$periodo = $anio.$mes;

	$sql="SELECT @a:=rowid as rowid, account_number as account_number, account_parent, label, rowid as id,
			(SELECT count(*) FROM ".MAIN_DB_PREFIX."accounting_account 
				WHERE entity=".$conf->entity." AND account_parent = @a ) as cant
			FROM ".MAIN_DB_PREFIX."accounting_account 
			WHERE entity=".$conf->entity." ORDER BY account_number";
	$rqs=$db->query($sql);

	

	while ($ctas2=$db->fetch_object($rqs)) {

		// $console = 'console.log(' . json_encode($ctas2) . ');';
		// $console = sprintf('<script>%s</script>', $console);
		// echo $console;

		//Saca los movimientos del mes de la cuenta
		$menor =false;
		if($ctas2->cant == 0){	
			$menor = true;
			$sql = "SELECT NVL(Sum(debit),0) as debe_total, NVL(Sum(credit),0) as haber_total ";
			$sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping b INNER JOIN  ".MAIN_DB_PREFIX."accounting_account a ON (b.numero_compte = a.account_number and b.entity =a.entity )";
			$sql .= " WHERE b.entity = ". $conf->entity . " AND year(doc_date) = ". substr($periodo,0,4) . " AND month(doc_date) = ". substr($periodo,4,2) ."";
			$sql .= " AND numero_compte = '".$ctas2->account_number ."' ";
		}else{
			$sql = "SELECT NVL(Sum(debit),0) as debe_total, NVL(Sum(credit),0) as haber_total ";
			$sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping b INNER JOIN  ".MAIN_DB_PREFIX."accounting_account a ON (b.numero_compte = a.account_number and b.entity =a.entity )";
			$sql .= " WHERE b.entity = ". $conf->entity . " AND year(doc_date) = ". substr($periodo,0,4) . " AND month(doc_date) = ". substr($periodo,4,2) ."";
			$sql .= " AND account_number IN (WITH RECURSIVE cte (rowid, account_number, account_parent) AS (";
			$sql .= " SELECT rowid, account_number, account_parent FROM  ".MAIN_DB_PREFIX."accounting_account WHERE account_parent = ".$ctas2->rowid." ";
			$sql .= " UNION ALL SELECT ac.rowid, ac.account_number,  ac.account_parent FROM ".MAIN_DB_PREFIX."accounting_account ac";
			$sql .= " INNER JOIN cte ON ac.account_parent = cte.rowid  ) SELECT account_number FROM cte)";
		}

		// if ($ctas2->rowid == 40 ){
		// 	var_dump($sql);
		// 	echo '<pre>';
		// 	var_dump($ctas2);
		// 	die;
		// }
		$rs = $db->query($sql);
		$result = $db->fetch_object($rs);
		$debe2 = $result->debe_total;
		$haber2 = $result->haber_total;

		
		// if($debe2!=0 || $haber2!=0){
		// 	$ctas2->cant=0;
		// }
		//Si es cuenta menor subcuentas = 0 entra a calcular el saldo
		//Si es padre va al else sacar el acumulado de movimeintos de las cuentas hijas y del periodo anterior
		if($ctas2->cant == 0){
			
			$debe = $result->debe_total;
			$haber = $result->haber_total;
			$debe_total += $result->debe_total;
			$haber_total += $result->haber_total;


			if ($menor){
				$column = "numero_compte";
				$sql = "SELECT  NVL(SUM(debit),0) - NVL(SUM(credit),0) as saldo ";
				$sql.= " FROM ";
				$sql.= " ".MAIN_DB_PREFIX."accounting_bookkeeping b inner join  ".MAIN_DB_PREFIX."accounting_account a on (b.numero_compte = a.account_number and b.entity =a.entity )";
				$sql.= " WHERE DATE_FORMAT(doc_date,'%Y%m') <= '".$periodoant."' AND b.entity = ".$conf->entity;
				$sql.= " AND numero_compte = '".$ctas2->account_number."' ";
			}else{
				$column = "account_parent";
				$sql = "SELECT  NVL(SUM(debit),0) - NVL(SUM(credit),0) as saldo ";
				$sql.= " FROM ";
				$sql.= " ".MAIN_DB_PREFIX."accounting_bookkeeping b inner join  ".MAIN_DB_PREFIX."accounting_account a on (b.numero_compte = a.account_number and b.entity =a.entity )";
				$sql.= " WHERE DATE_FORMAT(doc_date,'%Y%m') <= '".$periodoant."' AND b.entity = ".$conf->entity;
				$sql.= " AND account_number IN (WITH RECURSIVE cte (rowid, account_number, account_parent) AS (";
				$sql .= " SELECT rowid, account_number, account_parent FROM  ".MAIN_DB_PREFIX."accounting_account WHERE account_parent = ".$ctas2->rowid." ";
				$sql .= " UNION ALL SELECT ac.rowid, ac.account_number,  ac.account_parent FROM ".MAIN_DB_PREFIX."accounting_account ac";
				$sql .= " INNER JOIN cte ON ac.account_parent = cte.rowid  ) SELECT account_number FROM cte)";
			}

			$rs2 = $db->query($sql);
			$result2 = $db->fetch_object($rs2);

			$sdo_ini = $result2->saldo;
			$sdo_ini_tot += $sdo_ini;

			


			if ($sdo_ini != 0 || $debe != 0 || $haber != 0 || $chk_todas) {
				?>	
				<tr <?php print $bc[$var]; $var = !$var; ?>>
					<td colspan="2"><?php print $ctas2->account_number. " ".$ctas2->label;?></td>
					<?php 
					if($sdo_ini!=0){
						if($sdo_ini<0){
							$aux1=" color:red;";
						}else{
							$aux1="";
						}
					?>
						<td style="text-align: right;">
						<a href="/accountancy/bookkeeping/list.php?search_accountancy_code_start=<?=$ctas2->id;?>&search_accountancy_code_end=<?=$ctas2->id;?>&search_date_startday=01&search_date_startmonth=<?=($mes-1)?>&search_date_startyear=<?=$anio?>&search_date_endday=01&search_date_endmonth=<?=$mes?>&search_date_endyear=<?=$anio?>" style="<?=$aux1?>">
							<img src='<?="/../custom/sydinitprocess/images/lupa.png";?>' height='11px' width='11px'><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini, 2);?></a>
						</td>
					<?php 
					}else{
					?>
						<td style="text-align: right;"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini, 2);?></td>
					<?php 
					}
					?>
					<td style="text-align: right;">
						<a href="/accountancy/bookkeeping/list.php?search_accountancy_code_start=<?=$ctas2->id;?>&search_accountancy_code_end=<?=$ctas2->id;?>&search_date_startday=01&search_date_startmonth=<?=($mes)?>&search_date_startyear=<?=$anio?>&search_date_endday=01&search_date_endmonth=<?=($mes+1)?>&search_date_endyear=<?=$anio?>&search_debit=>0" >
							<img src='<?="/../custom/sydinitprocess/images/lupa.png";?>' height='11px' width='11px'> <?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($debe, 2); ?></a>
					</td>
					<td style="text-align: right;">
						<a href="/accountancy/bookkeeping/list.php?search_accountancy_code_start=<?=$ctas2->id;?>&search_accountancy_code_end=<?=$ctas2->id;?>&search_date_startday=01&search_date_startmonth=<?=($mes)?>&search_date_startyear=<?=$anio?>&search_date_endday=01&search_date_endmonth=<?=($mes+1)?>&search_date_endyear=<?=$anio?>&search_credit=>0" >
							<img src='<?="/../custom/sydinitprocess/images/lupa.png";?>' height='11px' width='11px'> <?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($haber, 2); ?></a>
					</td>
					<?php 
					$daldofin=$sdo_ini + $debe - $haber;
					if(($daldofin)<0){
						$aux2=" color:red;";
					}else{
						$aux2="";
					}
					?>
					<td style="text-align: right; <?=$aux2?>"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($daldofin, 2);?></td>
				</tr>
				<?php	 
			}
		}else{

			$sql = "SELECT NVL(Sum(debit),0) as debe_total, NVL(Sum(credit),0) as haber_total ";
			$sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping b INNER JOIN ".MAIN_DB_PREFIX."accounting_account a ON (b.numero_compte = a.account_number AND b.entity = a.entity )";
			$sql .= " WHERE  b.entity = ".$conf->entity." AND year(doc_date) = ". substr($periodo,0,4) . " AND month(doc_date) = ". substr($periodo,4,2) ." ";
			$sql .= " AND account_number IN (WITH RECURSIVE cte (rowid, account_number, account_parent) AS (";
			$sql .= " SELECT rowid, account_number, account_parent FROM  ".MAIN_DB_PREFIX."accounting_account WHERE account_parent = ".$ctas2->rowid." ";
			$sql .= " UNION ALL SELECT ac.rowid, ac.account_number,  ac.account_parent FROM ".MAIN_DB_PREFIX."accounting_account ac";
			$sql .= " INNER JOIN cte ON ac.account_parent = cte.rowid  ) SELECT account_number FROM cte)";

			$rs3 = $db->query($sql);
			$result3 = $db->fetch_object($rs3);

			$debe = $result3->debe_total;
			$haber = $result3->haber_total;
				
			$sql = "SELECT NVL(Sum(debit),0) - NVL(Sum(credit),0) as saldo ";
			$sql.= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping b INNER JOIN  ".MAIN_DB_PREFIX."accounting_account a ON (b.numero_compte = a.account_number AND b.entity = a.entity )";
			$sql.= " WHERE b.entity = ".$conf->entity." AND DATE_FORMAT(doc_date,'%Y%m') <= '".$periodoant."' ";
			$sql.= " AND account_number IN (WITH RECURSIVE cte (rowid, account_number, account_parent) AS (";
			$sql .= " SELECT rowid, account_number, account_parent FROM  ".MAIN_DB_PREFIX."accounting_account WHERE account_parent = ".$ctas2->rowid." ";
			$sql .= " UNION ALL SELECT ac.rowid, ac.account_number,  ac.account_parent FROM ".MAIN_DB_PREFIX."accounting_account ac";
			$sql .= " INNER JOIN cte ON ac.account_parent = cte.rowid  ) SELECT account_number FROM cte)";

			$rs4 = $db->query($sql);
			$result4 = $db->fetch_object($rs4);

			$sdo_ini = $result4->saldo;

	
/*
			$console = 'console.log(' . json_encode($result3) . ');';
			$console = sprintf('<script>%s</script>', $console);
			echo $console;

			$console = 'console.log(' . json_encode($result4) . ');';
			$console = sprintf('<script>%s</script>', $console);
			echo $console;

			$console = 'console.log(' . json_encode($sdo_ini) . ');';
			$console = sprintf('<script>%s "Haber:"</script>', $console);
			echo $console;
*/

			//var_dump($ctas->label." ".$sdo_ini);
			if ($sdo_ini != 0 || $debe != 0 || $haber != 0 || $chk_todas) {
				?>
				<tr <?php print $bc[$var]; $var = !$var; ?>>
					<td colspan="2"><strong><?php print $ctas2->account_number." ".$ctas2->label;?></strong></td>
					<?php 
					if($sdo_ini!=0){
						if($sdo_ini<0){
							$aux1=" color:red;";
						}else{
							$aux1="";
						}
					?>
						<td style="text-align: right;">
						<a style="font-weight: bold;" href="/accountancy/bookkeeping/list.php?search_accountancy_code_start=<?=$ctas2->id;?>&search_accountancy_code_end=<?=$ctas2->id;?>&search_date_startday=01&search_date_startmonth=<?=($mes-1)?>&search_date_startyear=<?=$anio?>&search_date_endday=01&search_date_endmonth=<?=$mes?>&search_date_endyear=<?=$anio?>" style="<?=$aux1?>">
						<img src='<?="/../custom/sydinitprocess/images/lupa.png";?>' height='11px' width='11px'><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini, 2);?></a></td>
					<?php 
					}else{
					?>
						<td style="text-align: right;"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini, 2);?></td>
					<?php 
					}
					?>
					<td style="text-align: right;">
						<a href="/accountancy/bookkeeping/list.php?search_accountancy_code_start=<?=$ctas2->id;?>&search_accountancy_code_end=<?=$ctas2->id;?>&search_date_startday=01&search_date_startmonth=<?=($mes)?>&search_date_startyear=<?=$anio?>&search_date_endday=01&search_date_endmonth=<?=($mes+1)?>&search_date_endyear=<?=$anio?>&search_debit=>0" >
							<img src='<?="/../custom/sydinitprocess/images/lupa.png";?>' height='11px' width='11px'> <?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($debe, 2); ?></a>
					</td>
					<td style="text-align: right;">
					<a href="/accountancy/bookkeeping/list.php?search_accountancy_code_start=<?=$ctas2->id;?>&search_accountancy_code_end=<?=$ctas2->id;?>&search_date_startday=01&search_date_startmonth=<?=($mes)?>&search_date_startyear=<?=$anio?>&search_date_endday=01&search_date_endmonth=<?=($mes+1)?>&search_date_endyear=<?=$anio?>&search_credit=>0" >
							<img src='<?="/../custom/sydinitprocess/images/lupa.png";?>' height='11px' width='11px'> <?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($haber, 2); ?></a>
					</td>
					<?php 
					$daldofin=$sdo_ini + $debe - $haber;
					if(($daldofin)<0){
						$aux2=" color:red;";
					}else{
						$aux2="";
					}
					?>
					<td style="text-align: right; <?=$aux2?>"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($daldofin, 2);?></td>
				</tr>
			<?php	 
			}	
		}

		$objetoJSON = [
			"Cuenta numero" => $ctas2->account_number,
			"Cuenta texto" => $ctas2->label,
			"Saldo Inical" => $sdo_ini,
			"Debe" => $debe,
			"Haber" => $haber,
			"Saldo Actual" => $sdo_ini + $debe - $haber
		];

		$console = 'console.log(' . json_encode($objetoJSON) . ');';
		$console = sprintf('<script>%s "Resultado:"</script>', $console);
		echo $console;
	}
	?>
	<tr <?php print $bc[$var]; $var = !$var; ?>>
		<td colspan="2" style="text-align: right;"><strong>Total:</strong></td>
		<td style="text-align: right;"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini_tot, 2);?></td>
		<td style="text-align: right;"><?=$langs->getCurrencySymbol($conf->currency).' '.number_format($debe_total, 2);?></td>
		<td style="text-align: right;"><?=$langs->getCurrencySymbol($conf->currency).' '.number_format($haber_total, 2);?></td>
		<td style="text-align: right;"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini_tot + $debe_total - $haber_total, 2);?></td>
	</tr>
</table>
</form>
<?php 

	// End of page
	llxFooter();
	$db->close();