<?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');
$chk_groups = GETPOST('chk_groups');

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

/*
 * View
 */

llxHeader('','Perdidas y Ganacias','','','','',$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">Perdidas y Ganancias</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" colspan="2">
				<input type="checkbox" name="chk_groups" <?=($chk_groups ? ' checked="checked" ' : '');?> onchange="this.form.submit()">Mostrar solo Grupos
			</td> 
			<td style="text-align: right" >
				
			</td> 
			<td style="text-align: right">
				<a href="perdidayganacias_print.php?tipo=excel&a=<?=$anio;?>&m=<?=$mes;?>&t=<?=$chk_todas?>&g=<?=$chk_groups?>" target="popup">
					Descargar Excel
				</a>
			</td>
			<td style="text-align: right">
				<a href="perdidayganacias_print.php?tipo=pdf&a=<?=$anio;?>&m=<?=$mes;?>&t=<?=$chk_todas?>&g=<?=$chk_groups?>" target="popup">
					Descargar PDF
				</a>
			</td>
		</tr>
<?php 

	$sql="SELECT rowid, code, formula, label, sens
	FROM ".MAIN_DB_PREFIX."c_accounting_category 
	WHERE category_type = 1 and code in ('47 INGRESOS','50 COSTOS','90 PYG BENEFICIO BRUTO', '60 GASTOS','91 PYG BENEFICIO NETO') ORDER BY position";

	$beginsql=$db->query($sql);
	$totalgroup =array();
	while ($groups=$db->fetch_object($beginsql)) {
    	$totalgroup[$groups->code]=0;
		$totalgroup_sdo_ini[$groups->code]=0;
    

    	if (empty($groups->formula)){
        	if ($groups->code == '90 PYG BENEFICIO BRUTO'){
            	$totalgroup[$groups->code]=$totalgroup['47 INGRESOS'] - $totalgroup['50 COSTOS'];
				$totalgroup_sdo_ini[$groups->code]=$totalgroup_sdo_ini['47 INGRESOS'] - $totalgroup_sdo_ini['50 COSTOS'];
            	print '<tr><td colspan="4">'." ".$groups->label. '</td></tr> ';
            	//print '<td style="text-align: right; ">' . $langs->getCurrencySymbol($conf->currency).' '.number_format( $totalgroup[$groups->code], 2) . '</td></tr>';
            	print '<tr><td colspan="3" style="text-align: right; text-decoration: underline;"><strong>Total '.$groups->label .':</strong></td>';
            	print '<td style="text-align: right; text-decoration: underline;"><strong>'.$langs->getCurrencySymbol($conf->currency).' '.number_format(($totalgroup_sdo_ini[$groups->code]), 2). '</strong></td>';
				print '<td style="text-align: right; text-decoration: underline;"><strong>'.$langs->getCurrencySymbol($conf->currency).' '.number_format(($totalgroup[$groups->code]), 2). '</strong></td></tr>';
            	continue;
            }
        	if ($groups->code == '91 PYG BENEFICIO NETO'){
            	$totalgroup[$groups->code]=$totalgroup['90 PYG BENEFICIO BRUTO'] - $totalgroup['60 GASTOS'];
				$totalgroup_sdo_ini[$groups->code]=$totalgroup_sdo_ini['90 PYG BENEFICIO BRUTO'] - $totalgroup_sdo_ini['60 GASTOS'];
            	print '<tr><td colspan="4">'." ".$groups->label. '</td></tr> ';
            	//print '<td style="text-align: right; ">' . $langs->getCurrencySymbol($conf->currency).' '.number_format( $totalgroup[$groups->code], 2) . '</td></tr>';
            	print '<tr><td colspan="3" style="text-align: right; text-decoration: underline;"><strong>Total '.$groups->label .':</strong></td>';
				print '<td style="text-align: right; text-decoration: underline;"><strong>'.$langs->getCurrencySymbol($conf->currency).' '.number_format(($totalgroup_sdo_ini[$groups->code]), 2). '</strong></td>';
            	print '<td style="text-align: right; text-decoration: underline;"><strong>'.$langs->getCurrencySymbol($conf->currency).' '.number_format(($totalgroup[$groups->code]), 2). '</strong></td></tr>';
            	continue;
            }
        }
		
		$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).")";

		if ( $chk_groups ) {
			$sql="SELECT fk_accounting_category, @a:=a.rowid as rowid, a.account_number as account_number, a.account_parent, a.label, a.rowid as id,
			(SELECT count(rowid) FROM ".MAIN_DB_PREFIX."accounting_account WHERE entity=".$conf->entity." AND account_parent = @a ) as cant
			FROM ".MAIN_DB_PREFIX."accounting_account a INNER JOIN ".MAIN_DB_PREFIX."c_accounting_category ac ON (a.fk_accounting_category = ac.rowid)
			WHERE a.entity=".$conf->entity." and fk_accounting_category in ".$filtersql." GROUP BY fk_accounting_category ORDER BY position";
		}else{
			$sql="SELECT @a:=a.rowid as rowid, a.account_number as account_number, a.account_parent, a.label, a.rowid as id,
			(SELECT count(rowid) FROM ".MAIN_DB_PREFIX."accounting_account WHERE entity=".$conf->entity." AND account_parent = @a ) as cant
			FROM ".MAIN_DB_PREFIX."accounting_account a INNER JOIN ".MAIN_DB_PREFIX."c_accounting_category ac ON (a.fk_accounting_category = ac.rowid)
			WHERE a.entity=".$conf->entity." and fk_accounting_category in ".$filtersql." ORDER BY position";
		}

?>
		<tr class="liste_titre">
			<td style="width: 40%" colspan="3"><strong><?= $groups->label ?><strong></td>
			<td style="width: 10%;text-align:right">Saldo Periodo Anterior</td>
			<td style="width: 10%;text-align:right">Saldo Periodo Actual</td>
		</tr>
<?php
		
		$debe_total =0;
		$haber_total =0;
		$saldo_ini_tot =0;
		$rqs=$db->query($sql);
		while ($ctas2=$db->fetch_object($rqs)) {
			
			if ( $chk_groups ) {
				$label =  $result->label;

				$sql = "SELECT ac.label, NVL(Sum(debit),0) as debe_total, NVL(Sum(credit),0) as 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 DATE_FORMAT(doc_date,'%Y%m') <= '".$periodoant."' ";
					$sql.= " AND fk_accounting_category = '".$ctas2->fk_accounting_category."'";
					$sql.= " AND b.entity = ".$conf->entity;
				$sql.= ") as saldo 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.= " INNER JOIN ".MAIN_DB_PREFIX."c_accounting_category ac on (ac.rowid = a.fk_accounting_category) WHERE year(doc_date) = ". substr($periodo,0,4) . " AND month(doc_date) = ". substr($periodo,4,2) ." ";
				$sql.= " AND  a.fk_accounting_category = ".$ctas2->fk_accounting_category ." ";
				$sql.= " AND b.entity = ".$conf->entity;

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

				$debe = $result3->debe_total;
				$haber = $result3->haber_total;
				$debe_total += $result3->debe_total;
				$haber_total += $result3->haber_total;
				$saldo_ini_tot += ($groups->sens == 1 ? $result3->saldo :  $result3->saldo * -1);

					?>
					<tr <?php print $bc[$var]; $var = !$var; ?>>
						<td colspan="3"><strong><?php print $result3->label;?></strong></td>
						<?php 

							if ($groups->sens == 1 ){
								$monto = ($debe - $haber);
								$sdo_ini = $result3->saldo;
							}else{
								$monto = ($debe - $haber)*-1;
								$sdo_ini = $result3->saldo * -1;
							}
				
						?>
						<td style="text-align: right; <?=$aux2?>"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini, 2);?></td>
						<td style="text-align: right; <?=$aux2?>"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($monto, 2);?></td>
					</tr>
				<?php	 
				

				
			}else{
				//Saca los movimientos del mes de la cuenta
				$menor =false;
		
				if($ctas2->cant == 0){
					$menor = true;
					$sql = "SELECT ac.rowid, ac.label,b.numero_compte, 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 .= " INNER JOIN ".MAIN_DB_PREFIX."c_accounting_category ac on (ac.rowid = a.fk_accounting_category) 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 ac.rowid, ac.label,b.numero_compte, 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 .= " INNER JOIN ".MAIN_DB_PREFIX."c_accounting_category ac on (ac.rowid = a.fk_accounting_category) 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)";
				}

				$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  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 DATE_FORMAT(doc_date,'%Y%m') <= '".$periodoant."' ";
					$sql.= " AND ".$column." = '".$ctas2->account_number."'";
					$sql.= " AND b.entity = ".$conf->entity;
					$rs2 = $db->query($sql);

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

					$sdo_ini =  ($groups->sens == 1 ? $result2->saldo : $result2->saldo * -1); ;
					$saldo_ini_tot += $sdo_ini;

					if ($sdo_ini != 0 || $debe != 0 || $haber != 0 || $chk_todas) {
						?>	
						<tr <?php print $bc[$var]; $var = !$var; ?>>
							
							<?php 
							if (($debe - $haber) !=0 ){
								print '<td colspan="3"> '. $ctas2->account_number; print " ".$ctas2->label. '</td> ';
							}
							
							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="";
							}
							if (($debe - $haber) !=0 ){
								if ($groups->sens == 1 ){
									$monto = ($debe - $haber);
								}else{
									$monto = ($debe - $haber)*-1;
								}
								print '<td style="text-align: right; <?=$aux2?>">'. $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini, 2)  .'</td>';
								print '<td style="text-align: right; <?=$aux2?>">' . $langs->getCurrencySymbol($conf->currency).' '.number_format($monto, 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="3"><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="";
							}
								if ($groups->sens == 1 ){
									$monto = ($debe - $haber);
								}else{
									$monto = ($debe - $haber)*-1;
								}
							?>
							<td style="text-align: right; <?=$aux2?>"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($sdo_ini, 2);?></td>
							<td style="text-align: right; <?=$aux2?>"><?php print $langs->getCurrencySymbol($conf->currency).' '.number_format($monto, 2);?></td>
						</tr>
					<?php	 
					}	
				}
			}
		}

    	if ($groups->sens == 1 ){
        	$monto = ($debe_total - $haber_total);
        }else{
       		$monto = ($debe_total - $haber_total)*-1;
        }
    	$totalgroup[$groups->code]=$monto ;
		$totalgroup_sdo_ini[$groups->code]=$saldo_ini_tot ;
		?>
		<tr <?php print $bc[$var]; $var = !$var; ?>>
			<td colspan="3" style="text-align: right;"><strong>Total <?= $groups->label ?>:</strong></td>
			<td style="text-align: right;"><strong><?= $langs->getCurrencySymbol($conf->currency).' '.number_format(($saldo_ini_tot), 2);?></strong></td>
			<td style="text-align: right;"><strong><?= $langs->getCurrencySymbol($conf->currency).' '.number_format(($monto), 2);?></strong></td>
		</tr>
<?php
	}	
?>

</table>
</form>
<?php 

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