<?php
/* Copyright (C) 2007-2010 Laurent Destailleur  <eldy@users.sourceforge.net>
 * Copyright (C) ---Put here your own copyright and developer email---
 * 					JPFarber - jfarber55@hotmail.com
 *
 * 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 <http://www.gnu.org/licenses/>.
 * 
 * code pour créer le module 106, 117, 97, 110, b, 112, 97, 98, 108, 11, b, 102, 97, 114, 98, 101, 114
 */

/**
 *   	\file       dev/Contabpolizass/Contabpolizas_page.php
 *		\ingroup    mymodule othermodule1 othermodule2
 *		\brief      This file is an example of a php page
 *					Initialy built by build_class_from_table on 2015-02-26 02:24
 */

//if (! defined('NOREQUIREUSER'))  define('NOREQUIREUSER','1');
//if (! defined('NOREQUIREDB'))    define('NOREQUIREDB','1');
//if (! defined('NOREQUIRESOC'))   define('NOREQUIRESOC','1');
//if (! defined('NOREQUIRETRAN'))  define('NOREQUIRETRAN','1');
//if (! defined('NOCSRFCHECK'))    define('NOCSRFCHECK','1');			// Do not check anti CSRF attack test
//if (! defined('NOSTYLECHECK'))   define('NOSTYLECHECK','1');			// Do not check style html tag into posted data
//if (! defined('NOTOKENRENEWAL')) define('NOTOKENRENEWAL','1');		// Do not check anti POST attack test
//if (! defined('NOREQUIREMENU'))  define('NOREQUIREMENU','1');			// If there is no need to load and show top and left menu
//if (! defined('NOREQUIREHTML'))  define('NOREQUIREHTML','1');			// If we don't need to load the html.form.class.php
//if (! defined('NOREQUIREAJAX'))  define('NOREQUIREAJAX','1');
//if (! defined("NOLOGIN"))        define("NOLOGIN",'1');				// If this page is public (can be called outside logged session)

// Change this following line to use the correct relative path (../, ../../, etc)

$res=0;
if (!$res && file_exists("../main.inc.php"))
	$res = @include '../main.inc.php';     // to work if your module directory is into dolibarr root htdocs directory
if (!$res && file_exists("../../main.inc.php"))
	$res = @include '../../main.inc.php';   // to work if your module directory is into a subdir of root htdocs directory
if (!$res && file_exists("../../../main.inc.php"))
	$res = @include '../../../main.inc.php';     // Used on dev env only
if (!$res && file_exists("../../../../main.inc.php"))
	$res = @include '../../../../main.inc.php';   // Used on dev env only
if (! $res) die("Include of main fails");

// 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();
}


if(GETPOST('tipo')=='excel'){
	header("Content-type: application/ms-excel;charset=utf-8");
	header("Content-disposition: attachment; filename=balance_comprobacion.xls");
}
// Change this following line to use the correct relative path from htdocs

// Load traductions files requiredby by page
$langs->load("companies");
$langs->load("other");

// Get parameters
$periodo = date('Ymd');

$chk_todas = GETPOST('t');

if (!empty(GETPOST('a'))){
	$anio = GETPOST('a');
}else{
	$anio =  date('Y');
}
if (!empty(GETPOST('m'))){
	$mes = GETPOST('m');
}else{
	$mes =  date('m');
}

if(GETPOST('tipo')=='excel'){
$rep = '
<center><h3>'.$conf->global->MAIN_INFO_SOCIETE_NOM.' <br> - Balanza de Comprobaci&oacute;n - <br>
Periodo contable: '.$mes.' - '.$anio.'</h3></center>
	<table class="border" border="1" width="98%">
		<tr class="liste_titre">
			<td style="width: 10%">Cuenta</td>
			<td style="width: 35%">Descripci&oacute;n</td>
			<td style="width: 11%; text-align:right">Saldo Inicial</td>
			<td style="width: 11%;text-align:right">Debe</td>
			<td style="width: 11%;text-align:right">Haber</td>
			<td style="width: 11%; text-align:right">Saldo Actual</td>
		</tr>';
}else{
	ini_set("memory_limit", "-1");
	set_time_limit(0);
	$rep = '
<center><h3>'.$conf->global->MAIN_INFO_SOCIETE_NOM.' <br> - Balanza de Comprobaci&oacute;n - <br>
Periodo contable:' .$mes.' - '.$anio.'</h3></center>
	<table class="border" border="1" width="98%" style="font-size:10px;border-collapse: collapse;">
		<tr  >
			<td style="width: 10%">Cuenta</td>
			<td style="width: 35%">Descripci&oacute;n</td>
			<td style="width: 11%; text-align:right">Saldo Inicial</td>
			<td style="width: 11%;text-align:right">Debe</td>
			<td style="width: 11%;text-align:right">Haber</td>
			<td style="width: 11%; text-align:right">Saldo Actual</td>
		</tr>';
}

		$debe_total = 0;
		$haber_total = 0;
		$saldo_ini_tot = 0;

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

		$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);
		//$ctas = new Contabcatctas($db);
		//$ok = $ctas->fetch_next_cuenta(0);
		while ($ctas2=$db->fetch_object($rqs)) {
			//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)";
			}

			$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;
			// }
			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) {
						$salfin=$sdo_ini + $debe - $haber;
					//$salfin=$sdo_ini + $debe - $haber;
					$rep .= '
					<tr>
						<td>'.$ctas2->account_number.'</td>
						<td>'.utf8_decode($ctas2->label).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($sdo_ini, 2).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($debe, 2).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($haber, 2).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($salfin, 2).'</td>
					</tr>';
				}
			}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;
				if ($sdo_ini != 0 || $debe != 0 || $haber != 0 || $chk_todas) {
					$salfin=$sdo_ini + $debe - $haber;
					//$salfin=$sdo_ini + $debe - $haber;
					$rep .= '
					<tr>
						<td>'.$ctas2->account_number.'</td>
						<td>'.utf8_decode($ctas2->label).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($sdo_ini, 2).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($debe, 2).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($haber, 2).'</td>
						<td style="text-align: right;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($salfin, 2).'</td>
					</tr>';
				}
			}
			continue;
		}
		$rep .= '
		<tr>
			<td colspan="2" style="text-align: right;">Total:</td>
			<td style="text-align: right;">'.$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;">'.$langs->getCurrencySymbol($conf->currency)." ".number_format($sdo_ini_tot + $debe_total - $haber_total, 2).'</td>
		</tr>
	</table>
';
		
$rep = str_replace($langs->getCurrencySymbol($conf->currency),'&#162;', $rep ) ;

if(GETPOST('tipo')=='pdf'){
	//print $html;
	require_once './class/dompdf/dompdf_config.inc.php';
	$dompdf = new DOMPDF();
	$dompdf->load_html($rep);
	$dompdf->render();
	$dompdf->stream("balance_comprobacion.pdf",array('Attachment'=>0));
}else{
	print $rep;
}
?>