<!DOCTYPE html>
<html lang="es-ES">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pantalla Exportaci&oacute;n Libro de Compras y Ventas</title>
<link href="../../maincss.css" rel="stylesheet" type="text/css" />
<link href="../../printcss.css" rel="stylesheet" type="text/css" media="print" />

</head>
<?php
include "../../conn.php";
include "../../general.php";
include "../../token.php";

$rep=$_GET["rep"];
$tipo=$_GET["tipo"];

//$usuario=$_POST["usuario"];
$ano_det=$_POST["ano_det"];
$mes_det=$_POST["mes_det"];

$tipo_rep=$_POST["tipo_rep"];

$n_decimales = 2;


?>
<body>
<?php

//Inicio Exportacion Libro de Compras y Ventas

if($rep == 9 && $tipo == 1) { // if 2

if($mi_cod_pais == 1) { //if bolivia

  	if ($mes_det == 1) { $nom_mes = 'Enero'; }
	if ($mes_det == 2) { $nom_mes = 'Febrero'; }
	if ($mes_det == 3) { $nom_mes = 'Marzo'; }
	if ($mes_det == 4) { $nom_mes = 'Abril'; }
	if ($mes_det == 5) { $nom_mes = 'Mayo'; }
	if ($mes_det == 6) { $nom_mes = 'Junio'; }
	if ($mes_det == 7) { $nom_mes = 'Julio'; }
	if ($mes_det == 8) { $nom_mes = 'Agosto'; }
	if ($mes_det == 9) { $nom_mes = 'Septiembre'; }
	if ($mes_det == 10) { $nom_mes = 'Octubre'; }
	if ($mes_det == 11) { $nom_mes = 'Noviembre'; }
	if ($mes_det == 12) { $nom_mes = 'Diciembre'; }
	
	$fechaini = $ano_det.'-'.$mes_det.'-'.'01';
	$mes_d = mktime( 0, 0, 0, $mes_det, 1, $ano_det ); 
	$dias_mes = intval(date("t",$mes_d));
	//echo $dias_mes;
	$fechafin = $ano_det.'-'.$mes_det.'-'.$dias_mes;

		//listado de facturas compras
		$result = mysqli_query($link, "select t.codigo as codigo, t.nro_fac as nro_fac,
t.nro_auto as nro_auto, t.fecha as fecha, 
t.cod_control as cod_control, t.cod_prov as cod_prov, 
t.proveedor as proveedor, t.nit as nit, t.total_bruto as total_bruto, 
t.ice as ice, t.excento as excento, t.total_neto as total_neto,
t.credito as credito, t.descuento as descuento, t.cod_tipo_compra as cod_tipo_compra
from
(select a.pkyPurchasesLedger as codigo, a.fldNumber as nro_fac,
a.fldAuthorization as nro_auto, a.fldDate as fecha, 
a.fldControlCode as cod_control, a.codSupplier as cod_prov, 
a.fldName as proveedor, a.fldNIT as nit, a.fldGrossTotal as total_bruto,
a.fldICE as ice, a.fldExempt as excento, a.fldNetTotal as total_neto,
a.fldTaxCredit as credito, a.fldDiscount as descuento, a.codType as cod_tipo_compra
from
txsPurchasesLedger a
where
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and
a.sysEnabled = 1 AND a.sysAuthorized = 1

UNION ALL

select a.pkyImportDocument as codigo, a.fldNumber as nro_fac, 
3 as nro_auto, a.fldPaymentDate as fecha, 
0 as cod_control, a.codSupplier as cod_prov, 
a.fldName as proveedor, a.fldNIT as nit, a.fldTotal as total_bruto, 
0 as ice, 0 as excento, a.fldTotal as total_neto, 
a.fldTotalVAT as credito, 0 as descuento, a.codType as cod_tipo_compra 
from 
pchImportDocument a 
where 
a.fldPaymentDate >= '$fechaini' and
a.fldPaymentDate <= '$fechafin' and
a.sysEnabled = 1 AND a.sysAuthorized = 1


UNION ALL 

select a.pkyTicket as codigo, a.fldNumber as nro_fac, 
1 as nro_auto, a.fldDate as fecha, 
0 as cod_control, a.codSupplier as cod_prov, 
a.fldName as proveedor, a.fldNIT as nit, a.fldGrossTotal as total_bruto, 
0 as ice, a.fldExempt as excento, a.fldNetTotal as total_neto, 
a.fldTaxCredit as credito, 0 as descuento, a.codType as cod_tipo_compra
from 
txsTicket a 
where 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and
a.sysEnabled = 1 AND a.sysAuthorized = 1) t 
order by t.fecha, t.proveedor, t.nro_auto;");
		

$matriz_compras = array();
$i = 1;
while ($row = mysqli_fetch_array($result)) {
	$matriz_compras [$i][1] = $row['codigo'];
	$matriz_compras [$i][2] = $row['nro_fac'];
	$matriz_compras [$i][3] = $row['nro_auto'];
	$matriz_compras [$i][4] = $row['fecha'];
	$matriz_compras [$i][5] = $row['cod_control'];
	if($row['cod_control'] == NULL){
		$matriz_compras [$i][5] = 0;
	} else {
		$matriz_compras [$i][5] = $row['cod_control'];
	}
	$matriz_compras [$i][6] = $row['cod_pro'];
	$matriz_compras [$i][7] = $row['proveedor'];
	$matriz_compras [$i][8] = $row['nit'];
	$matriz_compras [$i][9] = round($row['total_bruto'],$n_decimales);
	$matriz_compras [$i][10] = round($row['ice'],$n_decimales);
	$matriz_compras [$i][11] = round($row['excento'],$n_decimales);
	$matriz_compras [$i][12] = round($row['total_neto'],$n_decimales);
	$matriz_compras [$i][13] = round($row['credito'],$n_decimales);
	$matriz_compras [$i][14] = round($row['descuento'],$n_decimales);
	if($row['descuento'] <= 0) { 
		$matriz_compras [$i][14] = 0;
	}
	$matriz_compras [$i][15] = $row['cod_tipo_compra'];
	$i++;
}
$fin_c = $i - 1;
mysqli_free_result($result);


//listado de facturas ventas
$result1 = mysqli_query($link, "select a.pkySalesLedger as codigo, a.fldNumber as nro_fac,
a.fldAuthorization as nro_auto, a.fldDate as fecha, 
a.fldControlCode as cod_control, a.codClient as cod_cli, 
a.fldName as cliente, a.fldNIT as nit, a.fldGrossTotal as total_bruto,
a.fldICE as ice, a.fldExempt as excento, a.fldNetTotal as total_neto,
a.fldTaxDebit as debito, a.fldDiscount as descuento, a.codStatus as cod_estado
from
txsSalesLedger a
where
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' 
order by fecha, nro_auto, nro_fac;");
		

$matriz_ventas = array();
$i = 1;
while ($row = mysqli_fetch_array($result1)) {
	$matriz_ventas [$i][1] = $row['codigo'];
	$matriz_ventas [$i][2] = $row['nro_fac'];
	$matriz_ventas [$i][3] = $row['nro_auto'];
	$matriz_ventas [$i][4] = $row['fecha'];
	if($row['cod_control'] == NULL){
		$matriz_ventas [$i][5] = 0;
	} else {
		$matriz_ventas [$i][5] = $row['cod_control'];
	}
	$matriz_ventas [$i][6] = $row['cod_cli'];
	$matriz_ventas [$i][7] = $row['cliente'];
	$matriz_ventas [$i][8] = $row['nit'];
	$matriz_ventas [$i][9] = round($row['total_bruto'],$n_decimales);
	$matriz_ventas [$i][10] = round($row['ice'],$n_decimales);
	$matriz_ventas [$i][11] = round($row['excento'],$n_decimales);
	$matriz_ventas [$i][12] = round($row['total_neto'],$n_decimales);
	$matriz_ventas [$i][13] = round($row['debito'],$n_decimales);
	$matriz_ventas [$i][14] = $row['cod_estado'];
	$matriz_ventas [$i][15] = round($row['descuento'],$n_decimales);
	if($row['descuento'] <= 0) { 
		$matriz_ventas [$i][15] = 0;
	}
	if($row['cod_estado'] == 1){ $matriz_ventas [$i][16] = 'V'; }
	if($row['cod_estado'] == 2){ $matriz_ventas [$i][16] = 'A'; }
	if($row['cod_estado'] == 3){ $matriz_ventas [$i][16] = 'E'; }
	if($row['cod_estado'] == 4){ $matriz_ventas [$i][16] = 'N'; }
	if($row['cod_estado'] == 5){ $matriz_ventas [$i][16] = 'C'; }
	//para las facturas anuladas, extraviadas y no usadas
	if($row['cod_estado'] == 2 || $row['cod_estado'] == 3 || $row['cod_estado'] == 4) { //if estado
		$matriz_ventas [$i][5] = 0;
		if($row['cod_estado'] == 2) { $matriz_ventas [$i][7] = 'ANULADA'; }
		if($row['cod_estado'] == 3) { $matriz_ventas [$i][7] = 'EXTRAVIADA'; }
		if($row['cod_estado'] == 4) { $matriz_ventas [$i][7] = 'NO UTILIZADA'; }
		$matriz_ventas [$i][8] = 0;
		$matriz_ventas [$i][9] = 0;
		$matriz_ventas [$i][10] = 0;
		$matriz_ventas [$i][11] = 0;
		$matriz_ventas [$i][12] = 0;
		$matriz_ventas [$i][13] = 0;
		$matriz_ventas [$i][15] = 0;
		if($row['cod_estado'] == 2){ $matriz_ventas [$i][16] = 'A'; }
		if($row['cod_estado'] == 3){ $matriz_ventas [$i][16] = 'E'; }
		if($row['cod_estado'] == 4){ $matriz_ventas [$i][16] = 'N'; }
	} //fin if estado
	$i++;
}
$fin_v = $i - 1;
mysqli_free_result($result1);

//listado de facturas no usadas
$result22 = mysqli_query($link, "select fldEmissionDate as fecha, fldAuthorization as autorizacion, fldNextNumber as nro_ini, fldEndNumber as nro_fin 
from txsAuthorizationControl
where 
fldEmissionDate >= '$fechaini' and
fldEmissionDate <= '$fechafin' and 
fldClosed = 1 and 
sysEnabled = 1 and sysAuthorized = 1;");

$numero_no_usadas = mysqli_num_rows($result22);

while ($row = mysqli_fetch_array($result22)) {
	$fecha_no_usadas = $row['fecha'];
	$auto_no_usadas = $row['autorizacion'];
	$nro_ini_no_usadas = $row['nro_ini'];
	$nro_fin_no_usadas = $row['nro_fin'];
}
mysqli_free_result($result22);

if($numero_no_usadas == 1) {//if no usadas
	$p = $fin_v + 1;
	for($n = $nro_ini_no_usadas; $n <= $nro_fin_no_usadas; $n++) {//for n
		$matriz_ventas [$p][1] = 0; //codigo
		$matriz_ventas [$p][2] = $n; //nro fac
		$matriz_ventas [$p][3] = $auto_no_usadas; //autorizacion
		$matriz_ventas [$p][4] = $fecha_no_usadas; //fecha
		$matriz_ventas [$p][5] = 0; //cod control
		$matriz_ventas [$p][6] = 0; //cod cliente
		$matriz_ventas [$p][7] = 'NO UTILIZADA'; //cliente
		$matriz_ventas [$p][8] = 0; //NIT
		$matriz_ventas [$p][9] = 0; //total bruto
		$matriz_ventas [$p][10] = 0; //ice
		$matriz_ventas [$p][11] = 0; //exento
		$matriz_ventas [$p][12] = 0; //total neto
		$matriz_ventas [$p][13] = 0; //debito
		$matriz_ventas [$p][14] = 4; //cod estado
		$matriz_ventas [$p][15] = 0; //descuento
		$matriz_ventas [$p][16] = 'N'; //estado
		$p++;	
	}//fin for n
	$fin_v = $p - 1;
}//fin if no usadas

//if($resultado == 0) { //if resultado
?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><b>Empresa: <?php echo $nombre_empresa; ?><br/>NIT: <?php echo $mi_nit; ?></b>
</td><td align="right"><b>Fecha: <?php echo date("Y-m-d"); ?><br/>Hora: <?php echo date("H:i:s"); ?><br/>Usuario: <?php echo $usuario; ?></b>
</td></tr></table><br/>
<p><center><font color="#000099" size="+1"><b>Exportaci&oacute;n Libro de Compras y Ventas</b></font></center></p>
<table border="0" cellpadding="2" align="center" width="100%">
<tr>
<td>	
    <table border="1" align="center" cellpadding="2" width="100%">
        <TR> 
        <TD width="25%"><b>A&ntilde;o: </b><?php echo $ano_det; ?></TD>
        <TD width="25%"><b>Mes: </b><?php echo $nom_mes; ?></TD>
        <TD width="25%"><b>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?></TD>
        <TD width="25%"><b>Expresado en: </b><?php echo $nom_moneda_principal; ?></TD>
        </TR>
    </table>

</td>
<!--
<td valign="top" width="150">

    <table border="1" align="center" cellpadding="2" width="100%">
    <tr>
    <TD><b>Tipo de Cambio:</b></TD>
    </tr><tr>
    <TD align="center"><a href="../../ModuleAccounting/VoucherExchangeRate/index.php?pkyExchangeRate=<?php echo $ultimo_tc; ?>&user=<?php echo $usuario; ?>" title="Tipo de Cambio"><?php echo $ultimo_tc; ?></a> (<?php echo $tc_fecha; ?> - <?php echo $tc_hora; ?>)<br/ >
        <table border="0" cellpadding="2" align="center">
            <?php
            for($m = 1; $m <= $fin_m; $m++) {
            printf("<tr><td align='left'>1 %s</td>", $ma_tc [$m][1]);//moneda base
            printf("<td align='center'>%s</td>", ' = ');//letra a
            printf("<td align='left'>%s %s</td>", number_format($ma_tc [$m][3],$n_tipocambio), $ma_tc [$m][2]);//moneda target y tasa
            //printf("<td align='right'>%s</td></tr>", number_format($ma_tc [$m][3],$n_tipocambio));//tasa
            }
            ?>
        </table>
    </TD>
    </tr>
	</table>
    
</td>
-->
</tr>
</table>
<br /> 
<table border="1" align="center" cellpadding="2" width="100%">
 		<TR> 
 		<TD align="center"><b>Nro.</b></TD>
        <TD align="center"><b>Detalle</b></TD>
    	<TD align="center"><b>Nombre Archivo</b></TD>
        <TD align="center"><b>Compras</b></TD>
        <TD align="center"><b>Ventas</b></TD>
        <TD align="center"><b>Cr&eacute;dito Fiscal</b></TD>
        <TD align="center"><b>D&eacute;bito Fiscal</b></TD>
        <TD align="center"><b>Guardar</b></TD>
        </TR>

  <?php
  	//COMPRAS
  	$nombre_archivo = 'compras_'.$mes_det.$ano_det.'_'.$mi_nit;
	$total_compras = 0;
	$f = fopen("exp/$nombre_archivo.txt","w");
	$sep = "|";
	$fin_de_linea = "\r\n";

	$j = 1;
	while ($j <= $fin_c) { //while
		$fecha_emision = $matriz_compras [$j][4];
		$ano1 = substr($fecha_emision,0,4);
		$mes1 = substr($fecha_emision,5,2);
		$dia1 = substr($fecha_emision,8,2);
		if($matriz_compras [$j][3] == 3) {
			$nro_fac = 0;
			$nro_dui = $matriz_compras [$j][2];
		} else {
			$nro_fac = $matriz_compras [$j][2];
			$nro_dui = 0;
		}
		//$valor_poliza = 0;
		$fecha_emision = $dia1 . '/' . $mes1 . '/' . $ano1;
		if($matriz_compras [$j][3] == 3) { //if poliza
			$iva013 = $monto_iva / 100;
			//$valor_poliza = $matriz_compras [$j][2];
			//$matriz_compras [$j][2] = 0;
			$matriz_compras [$j][9] = $matriz_compras [$j][13] / $iva013;
			$matriz_compras [$j][12] = $matriz_compras [$j][9];
		} // fin if poliza
		$texto = $matriz_compras [$j][7];//proveedor
		$texto = strtr(strtoupper($texto), "àáâãäåæçèéêëìíîïðñòóôõöøùüú", "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÜÚ");
		$texto = substr($texto,0,150);
		$imp_total_compra = $matriz_compras [$j][9] + $matriz_compras [$j][14];
		$imp_no_cred_fis = $matriz_compras [$j][10] + $matriz_compras [$j][11];
		$subtotal = $imp_total_compra - $imp_no_cred_fis;
		$imp_cred_fis = $subtotal - $matriz_compras [$j][14];
		$linea = 1 . $sep . //especificacion
				$j . $sep . //numero correlativo
				$fecha_emision . $sep . //fecha fac o dui
				$matriz_compras [$j][8] . $sep . //nit proveedor
				$texto . $sep . //razon social proveedor
				$nro_fac . $sep . //numero factura
				$nro_dui. $sep . //numero dui
				$matriz_compras [$j][3] . $sep . //autorizacion
				number_format($imp_total_compra,2, '.', '') . $sep . //imp total compra
				number_format($imp_no_cred_fis,2, '.', '') . $sep . //imp no sujeto a credito fiscal
				number_format($subtotal,2, '.', '') . $sep . //subtotal
				number_format($matriz_compras [$j][14],2, '.', '') . $sep . //descuentos
				number_format($imp_cred_fis,2, '.', '') . $sep . //imp base para credito fiscal
				number_format($matriz_compras [$j][13],2, '.', '') . $sep . //credito fiscal
				$matriz_compras [$j][5] . $sep . //cod control
				$matriz_compras [$j][15] . $fin_de_linea; //tipo compra
		//$total_compras = $total_compras + $matriz_compras [$j][12];
		$total_compras = $total_compras + $imp_cred_fis;
		$total_credito_fiscal = $total_credito_fiscal + $matriz_compras [$j][13];
		fwrite($f,$linea);
		$j++;
	}//fin while
	fclose($f); 
	$fichero = "exp/".$nombre_archivo.".txt";
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'>1</td>");//numero
	
	if(!file_exists($fichero)) {
		printf("<td align='left'>El archivo no se gener&oacute; !, revise los par&aacute;metros</td>");//detalle
	} else {
		printf("<td align='left'>Archivo generado exitosamente !</td>");//detalle
	}
	printf("<td align='left'>%s</td>", substr($fichero,4));//nombre
	printf("<td align='right'>%s</td>", number_format($total_compras,$n_decimales));//compras
	printf("<td align='right'>&nbsp;</td>");//ventas
	printf("<td align='right'>%s</td>", number_format($total_credito_fiscal,$n_decimales));//credito fiscal
	printf("<td align='right'>&nbsp;</td>");//debito fiscal
	printf("<td align='center'><a href='descarga.php?fichero=$fichero' title='Guardar Archivo'><img src='../../Images/filesave.png' alt='Guardar Archivo' /></a></td></tr>");//guardar
	//echo "<a href='descarga.php?fichero=$fichero'> Guardar Archivo </a> ";
	
	//VENTAS
	$nombre_archivo = 'ventas_'.$mes_det.$ano_det.'_'.$mi_nit;
	$total_ventas = 0;
	$f = fopen("exp/$nombre_archivo.txt","w");
	$sep = "|";
	$fin_de_linea = "\r\n";

	$j = 1;
	while ($j <= $fin_v) { //while
		$fecha_emision = $matriz_ventas [$j][4];
		$ano1 = substr($fecha_emision,0,4);
		$mes1 = substr($fecha_emision,5,2);
		$dia1 = substr($fecha_emision,8,2);
		$fecha_emision = $dia1 . '/' . $mes1 . '/' . $ano1;
		$texto = $matriz_ventas [$j][7];//cliente
		$texto = strtr(strtoupper($texto), "àáâãäåæçèéêëìíîïðñòóôõöøùüú", "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÜÚ");
		$texto = substr($texto,0,150);
		$imp_total_venta = $matriz_ventas [$j][9] + $matriz_ventas [$j][15];
		$subtotal = $imp_total_venta - $matriz_ventas [$j][10] - $matriz_ventas [$j][11] - 0;
		$imp_base_debito_fis = $imp_total_venta - $matriz_ventas [$j][15];
		$linea = 3 . $sep . //especificacion
				$j . $sep . //numero correlativo
				$fecha_emision . $sep . //fecha fac
				$matriz_ventas [$j][2] . $sep . //numero factura
				$matriz_ventas [$j][3] . $sep . //autorizacion
				$matriz_ventas [$j][16] . $sep . //estado
				$matriz_ventas [$j][8] . $sep . //nit cliente
				$texto . $sep . //razon social cliente
				number_format($imp_total_venta,2, '.', '') . $sep . //imp total venta
				number_format($matriz_ventas [$j][10],2, '.', '') . $sep . //imp ice
				number_format($matriz_ventas [$j][11],2, '.', '') . $sep . //imp exento
				number_format(0,2, '.', '') . $sep . //tasa cero
				number_format($subtotal,2, '.', '') . $sep . //subtotal
				number_format($matriz_ventas [$j][15],2, '.', '') . $sep . //descuentos
				number_format($imp_base_debito_fis,2, '.', '') . $sep . //imp base para debito fiscal
				number_format($matriz_ventas [$j][13],2, '.', '') . $sep . //debito fiscal
				$matriz_ventas [$j][5] . $fin_de_linea; //cod control
		if($matriz_ventas [$j][14] == 1 || $matriz_ventas [$j][14] == 5){
		$total_ventas = $total_ventas + $imp_base_debito_fis;
		$total_debito_fiscal = $total_debito_fiscal + $matriz_ventas [$j][13];
		}
		fwrite($f,$linea);
		$j++;
	}//fin while
	fclose($f); 
	$fichero = "exp/".$nombre_archivo.".txt";
	printf("<tr><td align='center'>2</td>");//numero
	
	if(!file_exists($fichero)) {
		printf("<td align='left'>El archivo no se gener&oacute; !, revise los par&aacute;metros</td>");//detalle
	} else {
		printf("<td align='left'>Archivo generado exitosamente !</td>");//detalle
	}
	printf("<td align='left'>%s</td>", substr($fichero,4));//nombre
	printf("<td align='right'>&nbsp;</td>");//compras
	printf("<td align='right'>%s</td>", number_format($total_ventas,$n_decimales));//ventas
	printf("<td align='right'>&nbsp;</td>");//credito fiscal
	printf("<td align='right'>%s</td>", number_format($total_debito_fiscal,$n_decimales));//debito fiscal
	printf("<td align='center'><a href='descarga.php?fichero=$fichero' title='Guardar Archivo'><img src='../../Images/filesave.png' alt='Guardar Archivo' /></a></td></tr>");//guardar
  
//TOTAL
	$nombre_archivo = 'TOTALES_'.$mes_det.$ano_det;
	
	$f = fopen("exp/$nombre_archivo.txt","w");
	$sep = "|";
	$fin_de_linea = "\r\n";

	$linea = 'TOTAL COMPRA' . $sep . number_format($total_compras,2, '.', '') . $fin_de_linea . 'TOTAL VENTA' . $sep . number_format($total_ventas,2, '.', ''); 
	fwrite($f,$linea);
		
	fclose($f); 
	$fichero = "exp/".$nombre_archivo.".txt";
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'>3</td>");//numero
	
	if(!file_exists($fichero)) {
		printf("<td align='left'>El archivo no se gener&oacute; !, revise los par&aacute;metros</td>");//detalle
	} else {
		printf("<td align='left'>Archivo generado exitosamente !</td>");//detalle
	}
	printf("<td align='left'>%s</td>", substr($fichero,4));//nombre
	printf("<td align='right'>%s</td>", number_format($total_compras,$n_decimales));//compras
	printf("<td align='right'>%s</td>", number_format($total_ventas,$n_decimales));//ventas
	printf("<td align='right'>&nbsp;</td>");//credito fiscal
	printf("<td align='right'>&nbsp;</td>");//debito fiscal
	printf("<td align='center'><a href='descarga.php?fichero=$fichero' title='Guardar Archivo'><img src='../../Images/filesave.png' alt='Guardar Archivo' /></a></td></tr>");//guardar
  
} //fin if bolivia


if($mi_cod_pais == 176) { //if paraguay
	
	//$n_decimales176 = 0;
	
  	if ($mes_det == 1) { $nom_mes = 'Enero'; }
	if ($mes_det == 2) { $nom_mes = 'Febrero'; }
	if ($mes_det == 3) { $nom_mes = 'Marzo'; }
	if ($mes_det == 4) { $nom_mes = 'Abril'; }
	if ($mes_det == 5) { $nom_mes = 'Mayo'; }
	if ($mes_det == 6) { $nom_mes = 'Junio'; }
	if ($mes_det == 7) { $nom_mes = 'Julio'; }
	if ($mes_det == 8) { $nom_mes = 'Agosto'; }
	if ($mes_det == 9) { $nom_mes = 'Septiembre'; }
	if ($mes_det == 10) { $nom_mes = 'Octubre'; }
	if ($mes_det == 11) { $nom_mes = 'Noviembre'; }
	if ($mes_det == 12) { $nom_mes = 'Diciembre'; }
	
	$fechaini = $ano_det.'-'.$mes_det.'-'.'01';
	$mes_d = mktime( 0, 0, 0, $mes_det, 1, $ano_det ); 
	$dias_mes = intval(date("t",$mes_d));
	//echo $dias_mes;
	$fechafin = $ano_det.'-'.$mes_det.'-'.$dias_mes;
	
	//listado de facturas compras
	$result = mysqli_query($link, "select fin.codigo as codigo, fin.nro_fac as nro_fac, fin.nro_auto as nro_auto, fin.fecha as fecha, fin.cod_control as cod_control, fin.cod_prov as cod_prov, fin.proveedor as proveedor, fin.ruc as ruc, fin.total_bruto as total_bruto, fin.ice as ice, fin.excento as excento, fin.total10 as total10, fin.credito10 as credito10, fin.total5 as total5, fin.credito5 as credito5, fin.fecha_emision as fecha_emision, fin.tabla_genera as tabla_genera, fin.cod_genera as cod_genera, fin.glosa as glosa, fin.cod_moneda as cod_moneda, fin.cod_tc as cod_tc, fin.cod_tipo_doc as cod_tipo_doc
from
(select a.pkyPurchasesLedger as codigo, a.fldNumber as nro_fac, a.fldAuthorization as nro_auto, a.fldDate as fecha, a.fldControlCode as cod_control, a.codSupplier as cod_prov, a.fldName as proveedor, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, a.fldICE as ice, a.fldExempt as excento, a.fldNetTotal as total10, a.fldTaxCredit as credito10, a.fldNetTotalAlternative as total5, a.fldTaxCreditAlternative as credito5, a.fldEmissionDate as fecha_emision,  a.fldParentComponent as tabla_genera, a.cosComponentKey as cod_genera, a.txtDescription as glosa, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as cod_tipo_doc
from
txsPurchasesLedger a
where 
a.codCurrency = '$cod_moneda_principal' and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and
a.sysEnabled = 1 AND a.sysAuthorized = 1

union all

select t.codigo as codigo, t.nro_fac as nro_fac, t.nro_auto as nro_auto, t.fecha as fecha, t.cod_control as cod_control, t.cod_prov as cod_prov, 
t.proveedor as proveedor, t.ruc as ruc, t.total_bruto * tc.fldRate as total_bruto, t.ice * tc.fldRate as ice, t.excento * tc.fldRate as excento, t.total10 * tc.fldRate as total10, t.credito10 * tc.fldRate as credito10, t.total5 * tc.fldRate as total5, t.credito5 * tc.fldRate as credito5, t.fecha_emision as fecha_emision, t.tabla_genera as tabla_genera, t.cod_genera as cod_genera, t.glosa as glosa, t.cod_moneda as cod_moneda, t.cod_tc as cod_tc, t.cod_tipo_doc as cod_tipo_doc
from
(select a.pkyPurchasesLedger as codigo, a.fldNumber as nro_fac, a.fldAuthorization as nro_auto, a.fldDate as fecha, a.fldControlCode as cod_control, a.codSupplier as cod_prov, a.fldName as proveedor, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, a.fldICE as ice, a.fldExempt as excento, a.fldNetTotal as total10, a.fldTaxCredit as credito10, a.fldNetTotalAlternative as total5, a.fldTaxCreditAlternative as credito5, a.fldEmissionDate as fecha_emision,  a.fldParentComponent as tabla_genera, a.cosComponentKey as cod_genera, a.txtDescription as glosa, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as cod_tipo_doc
from
txsPurchasesLedger a
where 
a.codCurrency <> '$cod_moneda_principal' and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and
a.sysEnabled = 1 AND a.sysAuthorized = 1) t, actExchangeRateDetail tc
where
tc.codTargetCurrency = t.cod_moneda and
tc.codBaseCurrency = '$cod_moneda_principal' and
tc.codExchangeRate = t.cod_tc


UNION ALL 


select a.pkyDiscountsDevolutions as codigo, a.fldNumber as nro_fac, a.fldAuthorization as nro_auto, a.fldDate as fecha, a.fldControlCode as cod_control, a.codCompany as cod_prov, a.fldName as proveedor, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, 0 as ice, a.fldExempt as excento, a.fldNetTotal as total10, a.fldTaxCredit as credito10, a.fldNetTotalAlternative as total5, a.fldTaxCreditAlternative as credito5, a.fldEmissionDate as fecha_emision,  a.fldParentComponent as tabla_genera, a.cosComponentKey as cod_genera, a.txtDescription as glosa, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 3 as cod_tipo_doc
from
txsDiscountsDevolutions a
where 
a.codCurrency = '$cod_moneda_principal' and 
a.fldIsReceived = 1 and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and
a.sysEnabled = 1 AND a.sysAuthorized = 1

union all

select t.codigo as codigo, t.nro_fac as nro_fac, t.nro_auto as nro_auto, t.fecha as fecha, t.cod_control as cod_control, t.cod_prov as cod_prov, 
t.proveedor as proveedor, t.ruc as ruc, t.total_bruto * tc.fldRate as total_bruto, t.ice * tc.fldRate as ice, t.excento * tc.fldRate as excento, t.total10 * tc.fldRate as total10, t.credito10 * tc.fldRate as credito10, t.total5 * tc.fldRate as total5, t.credito5 * tc.fldRate as credito5, t.fecha_emision as fecha_emision, t.tabla_genera as tabla_genera, t.cod_genera as cod_genera, t.glosa as glosa, t.cod_moneda as cod_moneda, t.cod_tc as cod_tc, t.cod_tipo_doc as cod_tipo_doc
from
(select a.pkyDiscountsDevolutions as codigo, a.fldNumber as nro_fac, a.fldAuthorization as nro_auto, a.fldDate as fecha, a.fldControlCode as cod_control, a.codCompany as cod_prov, a.fldName as proveedor, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, 0 as ice, a.fldExempt as excento, a.fldNetTotal as total10, a.fldTaxCredit as credito10, a.fldNetTotalAlternative as total5, a.fldTaxCreditAlternative as credito5, a.fldEmissionDate as fecha_emision,  a.fldParentComponent as tabla_genera, a.cosComponentKey as cod_genera, a.txtDescription as glosa, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 3 as cod_tipo_doc
from
txsDiscountsDevolutions a
where 
a.codCurrency <> '$cod_moneda_principal' and 
a.fldIsReceived = 1 and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and
a.sysEnabled = 1 AND a.sysAuthorized = 1) t, actExchangeRateDetail tc
where
tc.codTargetCurrency = t.cod_moneda and
tc.codBaseCurrency = '$cod_moneda_principal' and
tc.codExchangeRate = t.cod_tc) fin 
order by fin.fecha, fin.proveedor, fin.nro_auto;");
		

$matriz_compras = array();
$monto_enc = 0;
$i = 1;
while ($row = mysqli_fetch_array($result)) {
	$matriz_compras [$i][1] = $row['codigo'];
	$matriz_compras [$i][2] = $row['nro_fac'];
	$matriz_compras [$i][3] = $row['nro_auto'];
	$matriz_compras [$i][4] = $row['fecha'];
	$matriz_compras [$i][5] = $row['cod_control'];
	if($row['cod_control'] == NULL){
		$matriz_compras [$i][5] = 0;
	} else {
		$matriz_compras [$i][5] = $row['cod_control'];
	}
	$matriz_compras [$i][6] = $row['cod_pro'];
	$matriz_compras [$i][7] = $row['proveedor'];
	$matriz_compras [$i][8] = $row['ruc'];
	$matriz_compras [$i][9] = round($row['total_bruto'],$n_decimales176);
	$matriz_compras [$i][10] = round($row['ice'],$n_decimales176);
	$matriz_compras [$i][11] = round($row['excento'],$n_decimales176);
	$matriz_compras [$i][12] = round($row['total10'],$n_decimales176) - round($row['credito10'],$n_decimales176);
	$matriz_compras [$i][13] = round($row['credito10'],$n_decimales176);
	$matriz_compras [$i][14] = $row['glosa'];
	$matriz_compras [$i][15] = $row['tabla_genera'];
	$matriz_compras [$i][16] = $row['cod_genera'];
	$matriz_compras [$i][17] = round($row['descuento'],$n_decimales176);
	$matriz_compras [$i][18] = $row['cod_tipo_doc'];
	$matriz_compras [$i][19] = NULL; //enlace al papa
	$matriz_compras [$i][20] = round($row['total5'],$n_decimales176) - round($row['credito5'],$n_decimales176);
	$matriz_compras [$i][21] = round($row['credito5'],$n_decimales176);
		
	$monto_enc = $monto_enc + $matriz_compras [$i][11] + $matriz_compras [$i][12] + $matriz_compras [$i][20]; //no sumo iva5 ni iva 10, ya se incluye 
	$i++;
}
$fin_c = $i - 1;
mysqli_free_result($result);

//representante legal
$resultrep = mysqli_query($link, "select a.codPerson as cod_persona, case when b.fldMiddleName is NULL then concat(b.fldLastName,' ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,' ',b.fldFirstName) end as persona, b.fldAlternativeKey as ruc 
from 
txsTaxesConfig a, agnPerson b
where 
a.codPerson = b.pkyPerson and 
b.sysEnabled = 1 and b.sysAuthorized = 1;");
while ($row = mysqli_fetch_array($resultrep)) {
	$nombre_representante = $row['persona'];
	$ruc_representante = $row['ruc'];
}
mysqli_free_result($resultrep);	

//encabezado compras
	$tipo_reg_enc = 1;
	$periodo_enc = $ano_det.$mes_det;
	//$tipo_rep = 1;
	$cod_obligacion = 911;
	$cod_formulario = 211;
	$divide_ruc = explode("-", $mi_nit);
	$ruc_ag_inf = $divide_ruc[0]; // ruc
	$dv_ag_inf = $divide_ruc[1]; // digito
	$nom_ag_inf = strtr(strtoupper($nombre_empresa), "àáâãäåæçèéêëìíîïðñòóôõöøùüú", "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÜÚ");
	$nom_ag_inf = substr($nom_ag_inf,0,80);
	$divide_ruc_rep = explode("-", $ruc_representante);
	$ruc_rep_legal = $divide_ruc_rep[0]; // ruc rep
	$dv_rep_legal = $divide_ruc_rep[1]; // digito rep
	$nom_rep_legal = strtr(strtoupper($nombre_representante), "àáâãäåæçèéêëìíîïðñòóôõöøùüú", "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÜÚ");
	$nom_rep_legal = substr($nom_rep_legal,0,80);
	$cant_reg_enc = $fin_c;
	//$monto_enc = 0;
	$exportador_enc = 'NO';
	$version_enc = 2;

//listado de facturas ventas
$result1 = mysqli_query($link, "select fin2.codigo as codigo, fin2.nro_fac as nro_fac, fin2.nro_auto as nro_auto, fin2.fecha as fecha, fin2.cod_cli as cod_cli, fin2.cliente as cliente, fin2.ruc as ruc, fin2.total_bruto as total_bruto, fin2.total_neto as total_neto, fin2.descuento as descuento, fin2.cod_estado as cod_estado, fin2.glosa as glosa, fin2.comp_gen as comp_gen, fin2.cod_nv as cod_nv, fin2.cod_moneda as cod_moneda, fin2.cod_tc as cod_tc, fin2.cod_tipo as cod_tipo, fin2.monto_10 as monto_10, fin2.iva_10 as iva_10, fin2.monto_5 as monto_5, fin2.iva_5 as iva_5, fin2.exento as exento, fin2.cod_tipo_doc as cod_tipo_doc
from 
(select fin.codigo as codigo, fin.nro_fac as nro_fac, fin.nro_auto as nro_auto, fin.fecha as fecha, fin.cod_cli as cod_cli, fin.cliente as cliente, fin.ruc as ruc, fin.total_bruto as total_bruto, fin.total_neto as total_neto, fin.descuento as descuento, fin.cod_estado as cod_estado, fin.glosa as glosa, fin.comp_gen as comp_gen, fin.cod_nv as cod_nv, fin.cod_moneda as cod_moneda, fin.cod_tc as cod_tc, fin.cod_tipo as cod_tipo, sum(fin.monto_10) as monto_10, sum(fin.iva_10) as iva_10, sum(fin.monto_5) as monto_5, sum(fin.iva_5) as iva_5, sum(fin.exento) as exento, fin.cod_tipo_doc as cod_tipo_doc
from
(select t.codigo as codigo, t.nro_fac as nro_fac, t.nro_auto as nro_auto, t.fecha as fecha, t.cod_cli as cod_cli, t.cliente as cliente, t.ruc as ruc, t.total_bruto as total_bruto, t.total_neto as total_neto, t.descuento as descuento, t.cod_estado as cod_estado, t.glosa as glosa, t.comp_gen as comp_gen, t.cod_nv as cod_nv, t.cod_moneda as cod_moneda, t.cod_tc as cod_tc, t.cod_tipo as cod_tipo, t.monto_10 as monto_10,  t.iva_10 as iva_10, t.monto_5 as monto_5, t.iva_5 as iva_5, t.exento as exento, t.cod_tipo_doc as cod_tipo_doc
from
(select a.pkySalesLedger as codigo, a.fldNumber as nro_fac, 
a.fldAuthorization as nro_auto, a.fldDate as fecha, a.codClient as cod_cli, a.fldName as cliente, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, a.fldNetTotal as total_neto, a.fldDiscount as descuento, a.codStatus as cod_estado, a.txtDescription as glosa, a.fldParentComponent as comp_gen, a.cosComponentKey as cod_nv, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, c.codType as cod_tipo,  
case when d.fldTax = 10 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_10, 
case when d.fldTax = 10 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_10,
case when d.fldTax = 5 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_5, 
case when d.fldTax = 5 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_5,
case when d.fldExempt <> 0 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*(d.fldExempt/100) else 0 end as exento, 1 as cod_tipo_doc 
from
txsSalesLedger a, txsAuthorizationControl b, slsSalesNote c, slsSalesNoteDetail d 
where 
a.codAuthorizationControl = b.pkyAuthorizationControl and 
a.cosComponentKey = c.pkySalesNote and 
c.pkySalesNote = d.codSalesNote and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin') t 
where 
t.cod_moneda = '$cod_moneda_principal' 

union all 

select t.codigo as codigo, t.nro_fac as nro_fac, t.nro_auto as nro_auto, t.fecha as fecha, t.cod_cli as cod_cli, t.cliente as cliente, t.ruc as ruc, t.total_bruto * tc.fldRate as total_bruto, t.total_neto * tc.fldRate as total_neto, t.descuento * tc.fldRate as descuento, t.cod_estado as cod_estado, t.glosa as glosa, t.comp_gen as comp_gen, t.cod_nv as cod_nv, t.cod_moneda as cod_moneda, t.cod_tc as cod_tc, t.cod_tipo as cod_tipo, t.monto_10 * tc.fldRate as monto_10, t.iva_10 * tc.fldRate as iva_10, t.monto_5 * tc.fldRate as monto_5, t.iva_5 * tc.fldRate as iva_5, t.exento * tc.fldRate as exento, t.cod_tipo_doc as cod_tipo_doc
from
(select a.pkySalesLedger as codigo, a.fldNumber as nro_fac, 
a.fldAuthorization as nro_auto, a.fldDate as fecha, a.codClient as cod_cli, a.fldName as cliente, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, a.fldNetTotal as total_neto, a.fldDiscount as descuento, a.codStatus as cod_estado, a.txtDescription as glosa, a.fldParentComponent as comp_gen, a.cosComponentKey as cod_nv, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, c.codType as cod_tipo,  
case when d.fldTax = 10 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_10, 
case when d.fldTax = 10 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_10,
case when d.fldTax = 5 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_5, 
case when d.fldTax = 5 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_5,
case when d.fldExempt <> 0 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*(d.fldExempt/100) else 0 end as exento, 1 as cod_tipo_doc 
from
txsSalesLedger a, txsAuthorizationControl b, slsSalesNote c, slsSalesNoteDetail d 
where 
a.codAuthorizationControl = b.pkyAuthorizationControl and 
a.cosComponentKey = c.pkySalesNote and 
c.pkySalesNote = d.codSalesNote and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin') t, actExchangeRateDetail tc 
where 
tc.codTargetCurrency = t.cod_moneda and
tc.codBaseCurrency = '$cod_moneda_principal' and
tc.codExchangeRate = t.cod_tc) fin 
group by fin.codigo


UNION ALL


select fin.codigo as codigo, fin.nro_fac as nro_fac, fin.nro_auto as nro_auto, fin.fecha as fecha, fin.cod_cli as cod_cli, fin.cliente as cliente, fin.ruc as ruc, fin.total_bruto as total_bruto, fin.total_neto as total_neto, fin.descuento as descuento, fin.cod_estado as cod_estado, fin.glosa as glosa, fin.comp_gen as comp_gen, fin.cod_nv as cod_nv, fin.cod_moneda as cod_moneda, fin.cod_tc as cod_tc, fin.cod_tipo as cod_tipo, sum(fin.monto_10) as monto_10, sum(fin.iva_10) as iva_10, sum(fin.monto_5) as monto_5, sum(fin.iva_5) as iva_5, sum(fin.exento) as exento, fin.cod_tipo_doc as cod_tipo_doc
from
(select t.codigo as codigo, t.nro_fac as nro_fac, t.nro_auto as nro_auto, t.fecha as fecha, t.cod_cli as cod_cli, t.cliente as cliente, t.ruc as ruc, t.total_bruto as total_bruto, t.total_neto as total_neto, t.descuento as descuento, t.cod_estado as cod_estado, t.glosa as glosa, t.comp_gen as comp_gen, t.cod_nv as cod_nv, t.cod_moneda as cod_moneda, t.cod_tc as cod_tc, t.cod_tipo as cod_tipo, t.monto_10 as monto_10,  t.iva_10 as iva_10, t.monto_5 as monto_5, t.iva_5 as iva_5, t.exento as exento, t.cod_tipo_doc as cod_tipo_doc
from
(select a.pkyDiscountsDevolutions as codigo, a.fldNumber as nro_fac, 
a.fldAuthorization as nro_auto, a.fldDate as fecha, a.codCompany as cod_cli, a.fldName as cliente, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, a.fldNetTotal as total_neto, 0 as descuento, 0 as cod_estado, a.txtDescription as glosa, a.fldParentComponent as comp_gen, a.cosComponentKey as cod_nv, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as cod_tipo,  
case when d.fldTax = 10 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_10, 
case when d.fldTax = 10 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_10,
case when d.fldTax = 5 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_5, 
case when d.fldTax = 5 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_5,
case when d.fldExempt <> 0 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*(d.fldExempt/100) else 0 end as exento, 2 as cod_tipo_doc
from
txsDiscountsDevolutions a, txsAuthorizationControl b, txsDiscountsDevolutionsDetail d
where 
a.fldAuthorization = b.fldAuthorization and 
a.pkyDiscountsDevolutions = d.codDiscountsDevolutions and 
a.fldIsReceived = 0 and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin') t 
where 
t.cod_moneda = '$cod_moneda_principal' 

union all 

select t.codigo as codigo, t.nro_fac as nro_fac, t.nro_auto as nro_auto, t.fecha as fecha, t.cod_cli as cod_cli, t.cliente as cliente, t.ruc as ruc, t.total_bruto * tc.fldRate as total_bruto, t.total_neto * tc.fldRate as total_neto, t.descuento * tc.fldRate as descuento, t.cod_estado as cod_estado, t.glosa as glosa, t.comp_gen as comp_gen, t.cod_nv as cod_nv, t.cod_moneda as cod_moneda, t.cod_tc as cod_tc, t.cod_tipo as cod_tipo, t.monto_10 * tc.fldRate as monto_10, t.iva_10 * tc.fldRate as iva_10, t.monto_5 * tc.fldRate as monto_5, t.iva_5 * tc.fldRate as iva_5, t.exento * tc.fldRate as exento, t.cod_tipo_doc as cod_tipo_doc
from
(select a.pkyDiscountsDevolutions as codigo, a.fldNumber as nro_fac, 
a.fldAuthorization as nro_auto, a.fldDate as fecha, a.codCompany as cod_cli, a.fldName as cliente, a.fldNIT as ruc, a.fldGrossTotal as total_bruto, a.fldNetTotal as total_neto, 0 as descuento, 0 as cod_estado, a.txtDescription as glosa, a.fldParentComponent as comp_gen, a.cosComponentKey as cod_nv, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as cod_tipo,  
case when d.fldTax = 10 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_10, 
case when d.fldTax = 10 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_10,
case when d.fldTax = 5 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100) else 0 end as monto_5, 
case when d.fldTax = 5 then ((d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*((100-d.fldExempt)/100))*(d.fldTax/100) else 0 end as iva_5,
case when d.fldExempt <> 0 then (d.fldTotalNetPrice/(((d.fldTax/100)*((100-d.fldExempt)/100))+1))*(d.fldExempt/100) else 0 end as exento, 2 as cod_tipo_doc
from
txsDiscountsDevolutions a, txsAuthorizationControl b, txsDiscountsDevolutionsDetail d
where 
a.fldAuthorization = b.fldAuthorization and 
a.pkyDiscountsDevolutions = d.codDiscountsDevolutions and 
a.fldIsReceived = 0 and 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin') t, actExchangeRateDetail tc 
where 
tc.codTargetCurrency = t.cod_moneda and
tc.codBaseCurrency = '$cod_moneda_principal' and
tc.codExchangeRate = t.cod_tc) fin 
group by fin.codigo) fin2
order by fin2.fecha, fin2.nro_fac;");
		

$matriz_ventas = array();
$monto_enc2 = 0;
$i = 1;
while ($row = mysqli_fetch_array($result1)) {
	$matriz_ventas [$i][1] = $row['codigo'];
	$matriz_ventas [$i][2] = $row['nro_fac'];
	$matriz_ventas [$i][3] = $row['nro_auto'];
	$matriz_ventas [$i][4] = $row['fecha'];
	$matriz_ventas [$i][5] = $row['cod_cli'];
	$matriz_ventas [$i][6] = $row['cliente'];
	$matriz_ventas [$i][7] = $row['ruc'];
	$matriz_ventas [$i][8] = $row['total_bruto'];
	$matriz_ventas [$i][9] = $row['total_neto'];
	$matriz_ventas [$i][10] = $row['descuento'];
	$matriz_ventas [$i][11] = $row['cod_estado'];
	$matriz_ventas [$i][12] = $row['glosa'];
	$matriz_ventas [$i][13] = $row['comp_gen'];
	$matriz_ventas [$i][14] = $row['cod_nv'];
	$matriz_ventas [$i][15] = $row['cod_moneda'];
	$matriz_ventas [$i][16] = $row['cod_tc'];
	$matriz_ventas [$i][17] = $row['cod_tipo'];
	$matriz_ventas [$i][18] = round($row['monto_10'],$n_decimales176);
	$matriz_ventas [$i][19] = round($row['monto_5'],$n_decimales176);
	$matriz_ventas [$i][20] = round($row['exento'],$n_decimales176);
	$matriz_ventas [$i][21] = 0; //retenciones
	$matriz_ventas [$i][22] = 1; //CO CR
	$matriz_ventas [$i][23] = round($row['iva_10'],$n_decimales176); //iva 10
	$matriz_ventas [$i][24] = round($row['iva_5'],$n_decimales176); //iva 5
	//if($row['cod_tipo'] == 1){ $matriz_ventas [$i][22] = 1; }
	if($row['cod_tipo'] == 5){ $matriz_ventas [$i][22] = 2; }
	$matriz_ventas [$i][25] = $row['cod_tipo_doc'];
	
	if($matriz_ventas [$i][11] == 2 || $matriz_ventas [$i][11] == 3 || $matriz_ventas [$i][11] == 4) {//if si es A
	$matriz_ventas [$i][6] = "FACTURA ANULADA";
	$matriz_ventas [$i][18] = 0;
	$matriz_ventas [$i][23] = 0;
	$matriz_ventas [$i][19] = 0;
	$matriz_ventas [$i][24] = 0;	
	$matriz_ventas [$i][20] = 0;
	$matriz_ventas [$i][21] = 0;
	}//fin if si es A
	
	//$matriz_ventas [$i][23] = round($matriz_ventas [$i][18] / 11,$n_decimales176);
	//$matriz_ventas [$i][18] = round($matriz_ventas [$i][18] / 1.1,$n_decimales176);
	//$matriz_ventas [$i][24] = round($matriz_ventas [$i][19] / 1.05 * 0.05,$n_decimales176);	
	//$matriz_ventas [$i][19] = round($matriz_ventas [$i][19] / 1.05,$n_decimales176);
		//$exento = round($matriz_ventas [$i][20],$n_decimales176);
	//$total_final = $importe_10 + $iva_10 + $importe_5 + $iva_5 + $exento;
	//$retencion = round($matriz_ventas [$i][21],$n_decimales176);
	
	$monto_enc2 = $monto_enc2 + $matriz_ventas [$i][18] + $matriz_ventas [$i][19] + $matriz_ventas [$i][20] + $matriz_ventas [$i][23] + $matriz_ventas [$i][24];
	
	$i++;
}
$fin_v = $i - 1;
mysqli_free_result($result1);

//encabezado ventas
	$tipo_reg_enc2 = 1;
	$periodo_enc2 = $ano_det.$mes_det;
	$tipo_rep2 = $tipo_rep;
	$cod_obligacion2 = 921;
	$cod_formulario2 = 221;
	$divide_ruc2 = explode("-", $mi_nit);
	$ruc_ag_inf2 = $divide_ruc2[0]; // ruc
	$dv_ag_inf2 = $divide_ruc2[1]; // digito
	$nom_ag_inf2 = $nom_ag_inf;
	$ruc_rep_legal2 = $ruc_rep_legal;
	$dv_rep_legal2 = $dv_rep_legal;
	$nom_rep_legal2 = $nom_rep_legal;
	$cant_reg_enc2 = $fin_v;
	//$monto_enc2 = 0;
	$version_enc2 = 2;


//if($resultado == 0) { //if resultado
?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><b>Empresa: <?php echo $nombre_empresa; ?><br/>NIT: <?php echo $mi_nit; ?></b>
</td><td align="right"><b>Fecha: <?php echo date("Y-m-d"); ?><br/>Hora: <?php echo date("H:i:s"); ?><br/>Usuario: <?php echo $usuario; ?></b>
</td></tr></table><br/>
<p><center><font color="#000099" size="+1"><b>Exportaci&oacute;n Libro de Compras y Ventas</b></font></center></p>
<table border="0" cellpadding="2" align="center" width="100%">
<tr>
<td>	
    <table border="1" align="center" cellpadding="2" width="100%">
        <TR> 
        <TD width="25%"><b>A&ntilde;o: </b><?php echo $ano_det; ?></TD>
        <TD width="25%"><b>Mes: </b><?php echo $nom_mes; ?></TD>
        <TD width="25%"><b>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?></TD>
        <TD width="25%"><b>Expresado en: </b><?php echo $nom_moneda_principal; ?></TD>
        </TR>
    </table>

</td>
<!--
<td valign="top" width="150">

    <table border="1" align="center" cellpadding="2" width="100%">
    <tr>
    <TD><b>Tipo de Cambio:</b></TD>
    </tr><tr>
    <TD align="center"><a href="../../ModuleAccounting/VoucherExchangeRate/index.php?pkyExchangeRate=<?php echo $ultimo_tc; ?>&user=<?php echo $usuario; ?>&token=<?php echo $token; ?>" title="Tipo de Cambio"><?php echo $ultimo_tc; ?></a> (<?php echo $tc_fecha; ?> - <?php echo $tc_hora; ?>)<br/ >
        <table border="0" cellpadding="2" align="center">
            <?php
            for($m = 1; $m <= $fin_m; $m++) {
            printf("<tr><td align='left'>1 %s</td>", $ma_tc [$m][1]);//moneda base
            printf("<td align='center'>%s</td>", ' = ');//letra a
            printf("<td align='left'>%s %s</td>", number_format($ma_tc [$m][3],$n_tipocambio), $ma_tc [$m][2]);//moneda target y tasa
            //printf("<td align='right'>%s</td></tr>", number_format($ma_tc [$m][3],$n_tipocambio));//tasa
            }
            ?>
        </table>
    </TD>
    </tr>
	</table>
    
</td>
-->
</tr>
</table>
<br /> 
<table border="1" align="center" cellpadding="2" width="100%">
 		<TR> 
 		<TD align="center"><b>Nro.</b></TD>
        <TD align="center"><b>Detalle</b></TD>
    	<TD align="center"><b>Nombre Archivo</b></TD>
        <TD align="center"><b>Compras</b></TD>
        <TD align="center"><b>Ventas</b></TD>
        <!--
        <TD align="center"><b>Cr&eacute;dito Fiscal</b></TD>
        <TD align="center"><b>D&eacute;bito Fiscal</b></TD>
        -->
        <TD align="center"><b>Guardar</b></TD>
        </TR>

  <?php
  	//COMPRAS
  	$nombre_archivo = 'compras_'.$mes_det.$ano_det;
	$total_compras = 0;
	$f = fopen("exp/$nombre_archivo.txt","w");
	$sep = "\t";
	$fin_de_linea = "\r\n";

	$j = 0; //empieza de 0 para la cabecera
	while ($j <= $fin_c) { //while
		
		if($j == 0) { //if encabezado
		$linea = $tipo_reg_enc . $sep . //tipo de registro
				$periodo_enc . $sep . //periodo
				$tipo_rep . $sep . //tipo de reporte
				$cod_obligacion . $sep . //cod obligacion
				$cod_formulario . $sep . //cod formulario
				$ruc_ag_inf . $sep . //ruc agente informacion
				$dv_ag_inf. $sep . //dv agente informacion
				$nom_ag_inf . $sep . //nombre agente informacion
				$ruc_rep_legal . $sep . //ruc rep legal
				$dv_rep_legal . $sep . //dv rep legal
				$nom_rep_legal . $sep . //nombre rep legal
				$cant_reg_enc . $sep . //cantidad registros
				$monto_enc . $sep . //monto total
				$exportador_enc . $sep . //exportador
				$version_enc . $fin_de_linea; //version
		} else { //else if ancabezado
		$divide_ruc = explode("-", $matriz_compras [$j][8]);
		$ruc_prov = $divide_ruc[0]; // ruc
		$dv_prov = $divide_ruc[1]; // digito
		$nom_prov = $matriz_compras [$j][7];
		$nom_prov = strtr(strtoupper($nom_prov), "àáâãäåæçèéêëìíîïðñòóôõöøùüú", "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÜÚ");
		$nom_prov = substr($nom_prov,0,80);
		$tipo_documento = $matriz_compras [$j][18]; //puede ser 1,2,3,4,5,7,8,11,13
		$nro_documento = $matriz_compras [$j][2];
			if($tipo_documento == 1 || $tipo_documento == 2 || $tipo_documento == 3 || $tipo_documento == 5) { 
				$nro_timbrado = $matriz_compras [$j][3]; 
			} else {
				$nro_timbrado = 0;	
			}
			if($tipo_documento == 11) { 
				$ruc_prov = 22222201; // ruc
				$dv_prov = 0; // digito (revisar para 22222201)
				$nro_documento = 0;
			}
		$fecha_emision = $matriz_compras [$j][4];
		$ano1 = substr($fecha_emision,0,4);
		$mes1 = substr($fecha_emision,5,2);
		$dia1 = substr($fecha_emision,8,2);
		$fecha_emision = $dia1 . '/' . $mes1 . '/' . $ano1;
		$tipo_operacion = 0; //0,6,7,8,9,10,11
		$condicion_compra = 1; //1=contado, 2=credito
			if($condicion_compra == 1) {
				$cant_cuotas = 0; // 0 numero de cuotas si es al contado
			} else {
				$cant_cuotas = 2; // numero de cuotas si es a credito
			}
		$monto_10 = $matriz_compras [$j][12];
		$monto_5 = $matriz_compras [$j][20];
		
		$linea = 2 . $sep . //tipo de registro
				$ruc_prov . $sep . //ruc prov
				$dv_prov . $sep . //dv prov
				$nom_prov . $sep . //nombre prov
				$nro_timbrado . $sep . //nro timbrado
				$tipo_documento . $sep . //tipo documento
				$nro_documento . $sep . //nro documento (factura)
				$fecha_emision . $sep . //fecha documento (factura)
				$monto_10 . $sep . //monto 10%
				$matriz_compras [$j][13] . $sep . //iva 10%
				$matriz_compras [$j][20] . $sep . //monto 5%
				$monto_5 . $sep . //iva 5%
				$matriz_compras [$j][11] . $sep . //exento
				$tipo_operacion . $sep . //tipo operacion
				$condicion_compra . $sep . //condicion compra
				$cant_cuotas . $fin_de_linea; //cant cuotas	
		} //fin if encabezado
		
		fwrite($f,$linea);
		$j++;
	}//fin while
	fclose($f); 
	$fichero = "exp/".$nombre_archivo.".txt";
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'>1</td>");//numero
	
	if(!file_exists($fichero)) {
		printf("<td align='left'>El archivo no se gener&oacute; !, revise los par&aacute;metros</td>");//detalle
	} else {
		printf("<td align='left'>Archivo generado exitosamente !</td>");//detalle
	}
	printf("<td align='left'>%s</td>", substr($fichero,4));//nombre
	printf("<td align='right'>%s</td>", number_format($monto_enc,$n_decimales176));//compras
	printf("<td align='right'>&nbsp;</td>");//ventas
	//printf("<td align='right'>%s</td>", number_format($total_credito_fiscal,$n_decimales));//credito fiscal
	//printf("<td align='right'>&nbsp;</td>");//debito fiscal
	printf("<td align='center'><a href='descarga.php?fichero=$fichero' title='Guardar Archivo'><img src='../../Images/filesave.png' alt='Guardar Archivo' /></a></td></tr>");//guardar
	//echo "<a href='descarga.php?fichero=$fichero'> Guardar Archivo </a> ";
	
	//VENTAS
	$nombre_archivo = 'ventas_'.$mes_det.$ano_det;
	$total_ventas = 0;
	$f = fopen("exp/$nombre_archivo.txt","w");
	$sep = "\t";
	$fin_de_linea = "\r\n";

	$j = 0; //empieza de 0 para la cabecera
	while ($j <= $fin_v) { //while
		if($j == 0) { //if encabezado
		$linea = $tipo_reg_enc2 . $sep . //tipo de registro
				$periodo_enc2 . $sep . //periodo
				$tipo_rep2 . $sep . //tipo de reporte
				$cod_obligacion2 . $sep . //cod obligacion
				$cod_formulario2 . $sep . //cod formulario
				$ruc_ag_inf2 . $sep . //ruc agente informacion
				$dv_ag_inf2 . $sep . //dv agente informacion
				$nom_ag_inf2 . $sep . //nombre agente informacion
				$ruc_rep_legal2 . $sep . //ruc rep legal
				$dv_rep_legal2 . $sep . //dv rep legal
				$nom_rep_legal2 . $sep . //nombre rep legal
				$cant_reg_enc2 . $sep . //cantidad registros
				$monto_enc2 . $sep . //monto total
				$version_enc2 . $fin_de_linea; //version
		} else { //else if ancabezado
		$divide_ruc = explode("-", $matriz_ventas [$j][7]);
		$ruc_cli = $divide_ruc[0]; // ruc
		$dv_cli = $divide_ruc[1]; // digito
		$tipo_documento = $matriz_ventas [$j][25]; //puede ser 1,2,3,6,7,12,13
		if($ruc_cli == 66666601 && $dv_cli == 6) { 
			$tipo_documento = 12; 
		}
		$nom_cli = $matriz_ventas [$j][6];
		$nom_cli = strtr(strtoupper($nom_cli), "àáâãäåæçèéêëìíîïðñòóôõöøùüú", "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÜÚ");
		$nom_cli = substr($nom_cli,0,80);
		$nro_documento = $matriz_ventas [$j][2];
		if($ruc_cli == 44444401 && $dv_cli == 7) { 
			$tipo_documento = 0; 
			$nro_documento = 0;
			$nro_timbrado = 0;
		}
		//$tipo_documento = 1; //puede ser 1,2,3,6,7,12,13
		if($tipo_documento == 1 || $tipo_documento == 2 || $tipo_documento == 3 || $tipo_documento == 6 || $tipo_documento == 12) { 
			$nro_timbrado = $matriz_ventas [$j][3]; 
		} else {
			$nro_timbrado = 0;	
		}
		$fecha_emision = $matriz_ventas [$j][4];
		$ano1 = substr($fecha_emision,0,4);
		$mes1 = substr($fecha_emision,5,2);
		$dia1 = substr($fecha_emision,8,2);
		$fecha_emision = $dia1 . '/' . $mes1 . '/' . $ano1;
		$total_ingreso = $matriz_ventas [$j][18]+$matriz_ventas [$j][23]+$matriz_ventas [$j][19]+$matriz_ventas [$j][24]+$matriz_ventas [$j][20];
		$condicion_venta = $matriz_ventas [$j][22]; //1=contado, 2=credito
		if($condicion_venta == 1) {
			$cant_cuotas = 0; // 0 numero de cuotas si es al contado
		} else {
			$cant_cuotas = 2; // numero de cuotas si es a credito
		}
		
		$linea = 2 . $sep . //tipo de registro
				$ruc_cli . $sep . //ruc prov
				$dv_cli . $sep . //dv prov
				$nom_cli . $sep . //nombre prov
				$tipo_documento . $sep . //tipo documento
				$nro_documento. $sep . //nro documento (factura)
				$fecha_emision . $sep . //fecha documento (factura)
				$matriz_ventas [$j][18] . $sep . //monto 10%
				$matriz_ventas [$j][23] . $sep . //iva 10%
				$matriz_ventas [$j][19] . $sep . //monto 5%
				$matriz_ventas [$j][24] . $sep . //iva 5%
				$matriz_ventas [$j][20] . $sep . //exento
				$total_ingreso . $sep . //total ventas
				$condicion_venta . $sep . //condicion venta
				$cant_cuotas . $sep . //cant cuotas	
				$nro_timbrado . $fin_de_linea; //nro timbrado
		} //fin if encabezado
		
		fwrite($f,$linea);
		$j++;
	}//fin while
	fclose($f); 
	$fichero = "exp/".$nombre_archivo.".txt";
	printf("<tr><td align='center'>2</td>");//numero
	
	if(!file_exists($fichero)) {
		printf("<td align='left'>El archivo no se gener&oacute; !, revise los par&aacute;metros</td>");//detalle
	} else {
		printf("<td align='left'>Archivo generado exitosamente !</td>");//detalle
	}
	printf("<td align='left'>%s</td>", substr($fichero,4));//nombre
	printf("<td align='right'>&nbsp;</td>");//compras
	printf("<td align='right'>%s</td>", number_format($monto_enc2,$n_decimales176));//ventas
	//printf("<td align='right'>&nbsp;</td>");//credito fiscal
	//printf("<td align='right'>%s</td>", number_format($total_debito_fiscal,$n_decimales));//debito fiscal
	printf("<td align='center'><a href='descarga.php?fichero=$fichero' title='Guardar Archivo'><img src='../../Images/filesave.png' alt='Guardar Archivo' /></a></td></tr>");//guardar
  
//TOTAL
	$nombre_archivo = 'TOTALES_'.$mes_det.$ano_det;
	
	$f = fopen("exp/$nombre_archivo.txt","w");
	$sep = "\t";
	$fin_de_linea = "\r\n";

	$linea = 'TOTAL COMPRA' . $sep . number_format($monto_enc,2, '.', '') . $fin_de_linea . 'TOTAL VENTA' . $sep . number_format($monto_enc2,2, '.', ''); 
	fwrite($f,$linea);
		
	fclose($f); 
	$fichero = "exp/".$nombre_archivo.".txt";
	printf("<tr BGCOLOR='#e2e7fa'><td align='center'>3</td>");//numero
	
	if(!file_exists($fichero)) {
		printf("<td align='left'>El archivo no se gener&oacute; !, revise los par&aacute;metros</td>");//detalle
	} else {
		printf("<td align='left'>Archivo generado exitosamente !</td>");//detalle
	}
	printf("<td align='left'>%s</td>", substr($fichero,4));//nombre
	printf("<td align='right'>%s</td>", number_format($monto_enc,$n_decimales176));//compras
	printf("<td align='right'>%s</td>", number_format($monto_enc2,$n_decimales176));//ventas
	//printf("<td align='right'>&nbsp;</td>");//credito fiscal
	//printf("<td align='right'>&nbsp;</td>");//debito fiscal
	printf("<td align='center'><a href='descarga.php?fichero=$fichero' title='Guardar Archivo'><img src='../../Images/filesave.png' alt='Guardar Archivo' /></a></td></tr>");//guardar
  
} //fin if paraguay

} //fin if 2



?>

</body>
</html>
