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

// 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'){
	header("Content-type: application/ms-excel;charset=utf-8");
	header("Content-disposition: attachment; filename=perdidasyganancias".$anio.$mes.".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");



if(GETPOST('tipo')=='excel'){
$rep = '
<center><h3>'.$conf->global->MAIN_INFO_SOCIETE_NOM.' <br> - Perdidas y Ganancias -<br>
Periodo contable: '.$mes.' - '.$anio.'</h3></center>
	<table class="border" border="1" width="98%">
		';
}else{
	$rep = '
<center><h3>'.$conf->global->MAIN_INFO_SOCIETE_NOM.' <br> - Perdidas y Ganancias -<br>
Periodo contable: '.$mes.' - '.$anio.'</h3></center>
	<table class="border" border="1" width="98%" style="font-size:10px;border-collapse: collapse;">
		';
}

		$debe_total = 0;
		$haber_total = 0;
		$saldo_ini_tot = 0;
	
		$periodoant = $anio . $mes -1;
		$periodo = $anio.$mes;


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

			$rep .= '<tr class="liste_titre">';
			$rep .= '<td style="width: 80%" colspan="2"><strong>'.$groups->label .'<strong></td>';
			$rep .= '<td style="width: 20%;text-align:right">Saldo Periodo</td>';
			$rep .= '</tr>';

			$debe_total =0;
			$haber_total =0;
			$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) {
							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;
								}
								$rep .=  '<tr><td colspan="2">'. $ctas2->account_number. " ".$ctas2->label. '</td><td style="text-align: right; '.$aux2.'">' . $langs->getCurrencySymbol($conf->currency).' '.number_format($monto, 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 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) {

							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;
								}
					
						$rep .=  '<tr><td colspan="2"><strong>'. $ctas2->account_number. " ".$ctas2->label.'</strong></td><td style="text-align: right; '.$aux2.'">'.$langs->getCurrencySymbol($conf->currency).' '.number_format($monto, 2).'</td></tr>';
					}	
				}
					
			}
			if ($groups->sens == 1 ){
				$monto = ($debe_total - $haber_total);
			}else{
				   $monto = ($debe_total - $haber_total)*-1;
			}
			$totalgroup[$groups->code]=$monto ;
			$rep .=  '<tr>';
			$rep .=  '<td colspan="2" style="text-align: right;"><strong>Total '.$groups->label .':</strong></td>';
			$rep .=  '<td style="text-align: right;"><strong>'. $langs->getCurrencySymbol($conf->currency).' '.number_format(($monto), 2).'</strong></td>';
			$rep .=  '</tr>';
		}	
$rep .='</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("perdidasyganancias.pdf",array('Attachment'=>0));
}else{
	print $rep;
}
?>