<?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 Situacion','','','','',$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 Situacion</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' ";
				$dateini =$obj->date_start;
				$datefin =$obj->date_end;
			}
			$sel1 .= ">".$obj->label."</option>";
		}
		$sel1 .= "</select> ";

		$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;

?>
<h3>Periodo contable: <?=$sel1;?></h3>
</form>
<form>
	<input type="hidden" name="a" value=<?=$anio;?> />
	<input type="hidden" name="m" value=<?=$mes;?> />
	<input type="hidden" name="token" value="<?newToken()?>">
	<table class="noborder" width="100%">
	<tr class="liste_titre">
			<td 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_sumas_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_sumas_print.php?tipo=pdf&a=<?=$anio;?>&m=<?=$mes;?>&t=<?=$chk_todas?>" target="popup">
					Descargar PDF
				</a>
			</td>
		</tr>
<?php 

	$sql="SELECT rowid, formula, label
	FROM ".MAIN_DB_PREFIX."c_accounting_category 
	WHERE category_type = 1 and code in ('10 ACTIVOS', '30 PASIVOS','40 CAPITAL')";
	$beginsql=$db->query($sql);

	while ($groups=$db->fetch_object($beginsql)) {
		$innergroup = explode ( '+',$groups->formula);
		$filtersql ="(";
		foreach ($innergroup as $item => $value){
			$filtersql .= "'".$value."',"; 
		}
		$filtersql = substr($filtersql,0,strlen($filtersql)-1).")";
		$sql="SELECT rowid, formula, label
		FROM ".MAIN_DB_PREFIX."c_accounting_category 
		WHERE category_type = 0 and code in " . $filtersql ;
		$subquery=$db->query($sql);
		$filtersql ="(";
		while ($categories=$db->fetch_object($subquery)) {
				$filtersql .= "".$categories->rowid.","; 
		}
		$filtersql = substr($filtersql,0,strlen($filtersql)-1).")";
		$sql="SELECT @a:=account_number as account_number, account_parent, label, rowid as id,
			(SELECT count(rowid) 
			FROM ".MAIN_DB_PREFIX."accounting_account 
			WHERE entity=".$conf->entity." AND account_parent LIKE CONCAT (@a,'%')) as cant
		FROM ".MAIN_DB_PREFIX."accounting_account 
		WHERE entity=".$conf->entity." and fk_accounting_category in ".$filtersql." ORDER BY account_number";
		
?>
		<tr class="liste_titre">
			<td style="width: 40%" colspan="2"><strong><?= $groups->label ?><strong></td>
			<td style="width: 10%;text-align:right">Saldo </td>
		</tr>
<?php

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

			//Saca los movimientos del mes de la cuenta
			$menor =false;
			if($ctas2->cant!=0){
				$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 )
				Where account_number like '".$ctas2->account_number."'";
				$sql .= " And year(doc_date) = ". substr($periodo,0,4) . " AND month(doc_date) = ". substr($periodo,4,2) ."";
				$sql.= " AND b.entity = ".$conf->entity;
				
			}else{
				$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 )
				Where account_number like '".$ctas2->account_number."'";
				$sql .= " And year(doc_date) = ". substr($periodo,0,4) . " AND month(doc_date) = ". substr($periodo,4,2) ."";
				$sql.= " AND b.entity = ".$conf->entity;
			}		

			$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";
				}else{
					$column = "account_parent";
				}
				$sql = "SELECT";
				$sql.= " 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."' ";
				$sql.= " AND ".$column." like '".$ctas2->account_number."%'";
				$sql.= " AND b.entity = ".$conf->entity;
				$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; print " ".$ctas2->label;?></td>
						<?php 
						if($sdo_ini!=0){
							if($sdo_ini<0){
								$aux1=" color:red;";
							}else{
								$aux1="";
							}
						}
						$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 like  Concat( a.account_number,'%') and b.entity =a.entity )";
				$sql.= " WHERE  account_parent like '".$ctas2->account_number."'";
				$sql .= " And year(doc_date) = ". substr($periodo,0,4) . " AND month(doc_date) = ". substr($periodo,4,2) ."";
				$sql.= " AND b.entity = ".$conf->entity;
				$rs3 = $db->query($sql);
				$result3 = $db->fetch_object($rs3);

				$debe = $result3->debe_total;
				$haber = $result3->haber_total;
					
				$sql = "SELECT";
				$sql.= "  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 like  Concat( a.account_number,'%') and b.entity =a.entity )";
				$sql.= " WHERE DATE_FORMAT(doc_date,'%Y%m') <= '".$periodoant."' ";
				$sql.= " AND account_parent like '".$ctas2->account_number."'";
				$sql.= " AND b.entity = ".$conf->entity;
				$rs4 = $db->query($sql);
				

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

				$sdo_ini = $result4->saldo;
				//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; print " ".$ctas2->label;?></strong></td>
						<?php 
						if($sdo_ini!=0){
							if($sdo_ini<0){
								$aux1=" color:red;";
							}else{
								$aux1="";
							}
						}
						$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	 
				}	
			}
				
		}
		?>
		<tr <?php print $bc[$var]; $var = !$var; ?>>
		<td colspan="2" style="text-align: right;"><strong>Total:</strong></td>
		<td style="text-align: right;"><strong><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini_tot + $debe_total - $haber_total, 2);?></strong></td>
	</tr>
	<?php

	}	
	?>

</table>
</form>
<?php 

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