<!DOCTYPE html>
<html lang="es-ES">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pantalla Reporte Gastos</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";

$rep=$_GET["rep"];
$tipo=$_GET["tipo"];
$agrupar=$_POST["agrupar"];
$con_gasto=$_POST["gasto"];
$con_egreso=$_POST["egreso"];
$documento=$_POST["documento"];
$item=$_POST["item"];
$familia=$_POST["familia"];
$subfamilia=$_POST["subfamilia"];
$grupo=$_POST["grupo"];
$subgrupo=$_POST["subgrupo"];
$otra_moneda=$_POST["otra_moneda"];
$usuario=$_POST["usuario"];
$fechaini=$_POST["fechaini"];
$fechafin=$_POST["fechafin"];
$det_orig=$_POST["det_orig"];
$ciudad=$_POST["ciudad"];
$cod_contexto=$_POST["contexto"];
$nombre=$_POST["nombre"];
$cuenta_gasto=$_POST["cuenta_gasto"];
$retencion_separada=$_POST["retencion_separada"];

$con_gasto_det=$_GET["gasto_det"];
$tipo_con_det=$_GET["tipo_con_det"];
$otra_moneda_det=$_GET["otra_moneda_det"];
$usuario_det=$_GET["usuario_det"];
$fechaini_det=$_GET["fechaini_det"];
$fechafin_det=$_GET["fechafin_det"];
$cod_contexto_det=$_GET["contexto_det"];
$nombre_det=$_GET["nombre_det"];
$cuenta_gasto_det=$_GET["cuenta_gasto_det"];
$retencion_separada_det=$_GET["retencion_separada_det"];
//$n_decimales = 6;

if ($familia == '') { $familia = 0; }
if ($subfamilia == '') { $subfamilia = 0; }
if ($grupo == '') { $grupo = 0; }
if ($subgrupo == '') { $subgrupo = 0; }

//controlo si elige cuenta contable de gasto, solo concepto de gasto sin el de egreso
if ($cuenta_gasto != 0) { $con_egreso = -1; }

$empresaoempleado = substr($nombre, -2);
$nombreemp = substr($nombre, 0, -3);

//verificar el tipo de usuario 3 es normal (no muestra CxP)
$resultuser = mysqli_query($link, "select pkyUser as cod_user, codType as cod_tipo 
from accUser
where fldLogin = '$usuario' and
sysEnabled = 1 and sysAuthorized = 1;");
		
while ($row = mysqli_fetch_array($resultuser)) {
$cod_usuario = $row['cod_user'];
$cod_tipo_usuario = $row['cod_tipo'];
}
mysqli_free_result($resultuser);

if($cod_tipo_usuario == 3) { 
	$valor_ver_cxp = ' c.sysVisible <> 1 and ';
	$valor_ver_cxp2 = ' a.sysVisible <> 1 and ';
} else {
	$valor_ver_cxp = ' ';
	$valor_ver_cxp2 = ' ';
}

//verificar el tipo de usuario 3 es normal (no muestra CxP) para el detalle
$resultuser = mysqli_query($link, "select pkyUser as cod_user, codType as cod_tipo 
from accUser
where fldLogin = '$usuario_det' and
sysEnabled = 1 and sysAuthorized = 1;");
		
while ($row = mysqli_fetch_array($resultuser)) {
$cod_usuario_det = $row['cod_user'];
$cod_tipo_usuario_det = $row['cod_tipo'];
}
mysqli_free_result($resultuser);

if($cod_tipo_usuario_det == 3) { 
	$valor_ver_cxp_det = ' c.sysVisible <> 1 and ';
	$valor_ver_cxp_det2 = ' a.sysVisible <> 1 and ';
} else {
	$valor_ver_cxp_det = ' ';
	$valor_ver_cxp_det2 = ' ';
}


if ($empresaoempleado == -1) { // if 1.1
	
	if($nombreemp == 0) {
		$nom_empemp = 'Empresas';
		$valor_empresa = ' ';
	} else { 
		$resultprov = mysqli_query($link, "select pkyCompany as codigo, fldName as empresa
								from agnCompany
								where
								pkyCompany = '$nombreemp' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultprov)) {
			$nom_empemp = $row['codigo'].' - '.$row['empresa'].' (Empresa)';
		}
		mysqli_free_result($resultprov);
		$valor_empresa = ' c.codSupplier = '. $nombreemp .' and ';
	}
	$valor_empleado = ' c.codEmployee is NULL and ';
	$con_egreso = -1;
}//fin if 1.1

if ($empresaoempleado == -2) { // if 1.2
	
	if($nombreemp == 0) {
		$nom_empemp = 'Empleados';
		$valor_empleado = ' ';
	} else { 
		$resultprov = mysqli_query($link, "select pkyPerson as codigo, case when fldMiddleName is NULL then concat(fldLastName,', ',fldFirstName) else concat(fldLastName,' ',fldMiddleName,', ',fldFirstName) end as empleado
								from agnPerson
								where
								pkyPerson = '$nombreemp' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultprov)) {
			$nom_empemp = $row['codigo'].' - '.$row['empleado'].' (Empleado)';
		}
		mysqli_free_result($resultprov);
		$valor_empleado = ' c.codEmployee = '. $nombreemp .' and ';
	}
	$valor_empresa = ' c.codSupplier is NULL and ';
	$con_egreso = -1;
}//fin if 1.2

if ($empresaoempleado == -3) { // if 1.3
	
	$nom_empemp = 'Todos';
	$valor_empresa = ' ';
	$valor_empleado = ' ';
}//fin if 1.3


// para que filtre solo uno de los conceptos elegidos, si se quita del index la opcion con -1
//if($con_gasto != 0 && $con_egreso == 0) { $con_egreso = -1; }
//if($con_egreso != 0 && $con_gasto == 0) { $con_gasto = -1; }

?>
<body>
<?php



//Inicio reporte gastos por item

if($rep == 5 && $tipo == 1) { // if rep

// se usa para tc a la fecha final, cuanto se debia acumulado a la fecha final en una moneda

//tipo de cambio a la fecha final
$resultom = mysqli_query($link, "SELECT T2.pkyExchangeRate as tc FROM 
(SELECT fldDate, MAX(fldTime) AS fldTime FROM actExchangeRate WHERE fldExclusive = '0' GROUP BY fldDate) as T1,
actExchangeRate T2 
WHERE 
T1.fldDate = (SELECT MAX(fldDate) FROM actExchangeRate WHERE fldDate <= '$fechafin' AND fldExclusive = '0') AND 
T2.fldDate = T1.fldDate AND 
T2.fldTime = T1.fldTime AND 
sysEnabled = 1 AND sysAuthorized = 1;");
while ($row = mysqli_fetch_array($resultom)) {
	$ultimo_tc = $row['tc'];
	}
	mysqli_free_result($resultom);

//tipo de cambio para la cabecera
$resulttc = mysqli_query($link, "select pkyExchangeRate as cod_tc, fldDate as fecha, fldTime as hora
from
actExchangeRate
where
pkyExchangeRate = '$ultimo_tc' and 
sysEnabled = 1 and sysAuthorized = 1;");
while ($row = mysqli_fetch_array($resulttc)) {
	$tc_fecha = $row['fecha'];
	$tc_hora = $row['hora'];
}
mysqli_free_result($resulttc);

$resulttcdet = mysqli_query($link, "select a.pkyExchangeRateDetail as cod_tc_det, a.codBaseCurrency as cod_mon_target, b.fldName as mon_target, b.txtDescription as nom_mon_target, a.codTargetCurrency as cod_mon_base, c.fldName as mon_base, c.txtDescription as nom_mon_base, a.fldRate as tasa
from
actExchangeRateDetail a, actCurrency b, actCurrency c
where
a.codExchangeRate = '$ultimo_tc' and 
a.codBaseCurrency = '$cod_moneda_principal' and 
a.codBaseCurrency = b.pkyCurrency and 
a.codTargetCurrency = c.pkyCurrency 
order by cod_mon_target;");
$ma_tc = array();
$m = 1;
while ($row = mysqli_fetch_array($resulttcdet)) {
	$ma_tc [$m][1] = $row['mon_base'];
	$ma_tc [$m][2] = $row['mon_target'];
	$ma_tc [$m][3] = $row['tasa'];
	$m++;
}
mysqli_free_result($resulttcdet);
$fin_m = $m - 1;

//nombre otra moneda
if($otra_moneda != 0) {//if otra moneda
$resultotra = mysqli_query($link, "SELECT fldName as otra_moneda
from
actCurrency
where
pkyCurrency = '$otra_moneda' and
sysEnabled = 1 and sysAuthorized = 1;");
		
	while ($row = mysqli_fetch_array($resultotra)) {
	$nom_otra_moneda = $row['otra_moneda'];
	}
	mysqli_free_result($resultotra);
} else {
	$nom_otra_moneda = 'Moneda original';
}//fin if otra moneda 


if ($con_gasto == 0) { //if concepto de gasto
	$nom_con_gasto = 'Todos';
	$valor_con_gasto = ' ';
} else { 
    if ($con_gasto == -1) { //if concepto de gasto 2
	$nom_con_gasto = 'Solo Concepto de Egreso (Mov. Merc.)';
  	$valor_con_gasto = ' a.codExpensesConcept = '.$con_gasto.' and ';
	} else {
	$valor_con_gasto = ' a.codExpensesConcept = '.$con_gasto.' and ';
	//nombre del concepto de gasto
	$resultgasto = mysqli_query($link, "select pkyExpensesConcept as codigo, fldName as gasto
	from payExpensesConcept 
	where
	pkyExpensesConcept = '$con_gasto' and 
	sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultgasto)) {
			$nom_con_gasto = $row['gasto'];
		}
		mysqli_free_result($resultgasto);
	} //fin if concepto de gasto 2
} //fin if concepto de gasto

if ($con_egreso == 0) { //if concepto de egreso
	$nom_con_egreso = 'Todos';
	$valor_con_egreso = ' ';
} else { 
    if ($con_egreso == -1) { //if concepto de egreso 2
	$nom_con_egreso = 'Solo Concepto de Gasto';
  	$valor_con_egreso = ' a.codTypeConcept = '.$con_egreso.' and ';
	} else {
	$valor_con_egreso = ' a.codTypeConcept = '.$con_egreso.' and ';
	//nombre del concepto de egreso
	$resultegreso = mysqli_query($link, "select pkyItemMovementTypeConcept as codigo, fldName as egreso
	from
	invItemMovementTypeConcept
	where
	pkyItemMovementTypeConcept = '$con_egreso' and 
	sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultegreso)) {
			$nom_con_egreso = $row['egreso'];
		}
		mysqli_free_result($resultegreso);
	} //fin if concepto de egreso 2
} //fin if concepto de egreso

if ($cuenta_gasto == 0) { // if cuenta_gasto
	$nom_cuenta_gasto = 'Todos';
	$valor_cuenta_gasto = ' ';
	//$valor_cuenta_gasto2 = ' ';
} else {
	$valor_cuenta_gasto = ' b.codAccount = ' .$cuenta_gasto. ' and ';
	//$valor_cuenta_gasto2 = ' e.codAccount = ' .$cuenta_gasto. ' and ';
	//nombre de la cuenta contable del almacen
	$result2 = mysqli_query($link, "select pkyAccount, fldName 
							   from actAccount 
							   where 
							pkyAccount = '$cuenta_gasto' and
							sysEnabled = 1 and sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($result2)) {
		$nom_cuenta_gasto = $row['fldName'];
	}
	mysqli_free_result($result2);
} //fin if cuenta_gasto

//contexto
if($cod_contexto == -1){ 
	$nom_contexto = 'Todos';
	$valor_contexto = ' ';
	$valor_contexto2 = ' ';
} else {
	$resultcontx = mysqli_query($link, "select pkyContext as cod_contexto, fldName as contexto, codCostCenter as cod_cc 
	from srvContext
	where 
	pkyContext = '$cod_contexto';");
		while ($row = mysqli_fetch_array($resultcontx)) {   
			$nom_contexto = $row['cod_contexto'].' - '.$row['contexto'];
		}
		mysqli_free_result($resultcontx);
	$valor_contexto = ' a.sysContext = '.$cod_contexto.' and ';	
	$valor_contexto2 = ' c.sysContext = '.$cod_contexto.' and ';	
}


// inicio agrupado por concepto
if($agrupar == 1) { //if 1
	
$documento = 0;
$item = 0;
$familia = 0;
$subfamilia = 0;
$grupo = 0;
$subgrupo = 0;
$ciudad = 0;
if($otra_moneda == 0) {
$otra_moneda = $cod_moneda_principal;
$nom_otra_moneda = $nom_moneda_principal;
}

//verificar si la retención se separa en impuestos 1=si, 0=no
if($retencion_separada == 1){
	$nom_retencion = 'Si';
	$valor_retencion = 't.monto_om * t.porcentaje_ret / 100';
} else {
	$nom_retencion = 'No';
	$valor_retencion = 0;
}

//este query calcula con el tc en el que se hizo la transacción, todos los CASE estan en funcion del detalle exento, impuesto 3777 (//compara si tiene cxp para el impuesto)
$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, sum(t.monto_om) as monto_om, t.tipo_con as tipo_con, t.cod_doc as cod_doc, sum(t.exento) AS exento, 
sum(case when t.cod_doc = 1 OR t.cod_doc = 2 OR t.cod_doc = 5 OR t.cod_doc = 7 then ((t.monto_om - t.exento) * $monto_iva / 100) 
ELSE case when t.cod_doc = 4 then (t.monto_om) 
	ELSE case when t.cod_doc = 3 then ($valor_retencion) 
		ELSE 0
		END
	END 
END) AS impuesto
from (
select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, 0 as tipo_con, a.codDocumentType as cod_doc, 
case when a.codDocumentType = 1 OR a.codDocumentType = 7 then (select fldExempt 
			from txsPurchasesLedger 
			where 
			pkyPurchasesLedger in (select fldInvoice from payAccountsPayableDetail WHERE pkyAccountsPayableDetail = a.pkyAccountsPayableDetail)) ELSE 0 end AS exento,
case when a.codDocumentType = 3 then (select sum(fldPercent)
			from txsRetentionDetail
			where
			codAccountsPayableDetail = a.pkyAccountsPayableDetail
			group by codAccountsPayableDetail) ELSE 0 end AS porcentaje_ret
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d
where
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
c.codExchangeRate = d.codExchangeRate and 
$valor_con_gasto 
$valor_empresa
$valor_empleado  
$valor_ver_cxp 
$valor_contexto2 
$valor_cuenta_gasto 
b.pkyExpensesConcept <> 20 and 
c.fldDate >= '$fechaini' and 
c.fldDate <= '$fechafin' and 
d.codBaseCurrency = '$otra_moneda' and  
d.codTargetCurrency = c.codCurrency and 
c.sysEnabled = 1 and c.sysAuthorized = 1

union all

select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, 0 as tipo_con, a.codDocumentType as cod_doc, 
case when a.codDocumentType = 1 OR a.codDocumentType = 7 then (select fldExempt 
			from txsPurchasesLedger 
			where 
			pkyPurchasesLedger in (select fldInvoice from payAccountsPayableDetail WHERE pkyAccountsPayableDetail = a.pkyAccountsPayableDetail)) ELSE 0 end AS exento,
case when a.codDocumentType = 3 then (select sum(fldPercent)
			from txsRetentionDetail
			where
			codAccountsPayableDetail = a.pkyAccountsPayableDetail
			group by codAccountsPayableDetail) ELSE 0 end AS porcentaje_ret
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c
where
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
$valor_con_gasto 
$valor_empresa
$valor_empleado  
$valor_ver_cxp 
$valor_contexto2 
$valor_cuenta_gasto 
b.pkyExpensesConcept <> 20 and 
c.fldDate >= '$fechaini' and 
c.fldDate <= '$fechafin' and 
c.codCurrency = '$otra_moneda' and
c.sysEnabled = 1 and c.sysAuthorized = 1

UNION ALL  

select b.pkyItemMovementTypeConcept as cod_gasto, b.fldName as concepto_gasto, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc , d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 1 as tipo_con, 0 as cod_doc, 0 as exento, 0 as porcentaje_ret 
from
invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d
where
a.codTypeConcept = b.pkyItemMovementTypeConcept and 
a.codType = 2 and 
a.pkyItemMovement = c.codItemMovement and 
b.fldItemControl = 1 and 
a.codExchangeRate = d.codExchangeRate and 
$valor_con_egreso 
$valor_contexto 
a.fldDate >= '$fechaini' and 
a.fldDate <= '$fechafin' and  
d.codBaseCurrency = '$otra_moneda' and 
d.codTargetCurrency = a.codCurrency and 
a.sysEnabled = 1 and a.sysAuthorized = 1

union all 

select b.pkyItemMovementTypeConcept as cod_gasto, b.fldName as concepto_gasto, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc , 1 as tc, c.fldTotaCost * 1 as monto_om, 1 as tipo_con, 0 as cod_doc, 0 as exento, 0 as porcentaje_ret 
from
invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c
where
a.codTypeConcept = b.pkyItemMovementTypeConcept and 
a.codType = 2 and 
a.pkyItemMovement = c.codItemMovement and 
b.fldItemControl = 1 and 
$valor_con_egreso 
$valor_contexto 
a.fldDate >= '$fechaini' and 
a.fldDate <= '$fechafin' and  
a.codCurrency = '$otra_moneda' and 
a.sysEnabled = 1 and a.sysAuthorized = 1) t
group by concepto_gasto, tipo_con;");

//este query calcula con el tipo de cambio calculado a la fecha final
/*
$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, sum(t.monto_om) as monto_om
from (
select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d
where
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
$valor_con_gasto 
d.codExchangeRate = '$ultimo_tc' and 
c.fldDate >= '$fechaini' and 
c.fldDate <= '$fechafin' and
d.codBaseCurrency = '$otra_moneda' and 
d.codTargetCurrency = c.codCurrency and 
c.sysEnabled = 1 and c.sysAuthorized = 1

union all

select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c
where
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
$valor_con_gasto 
c.fldDate >= '$fechaini' and 
c.fldDate <= '$fechafin' and 
c.codCurrency = '$otra_moneda' and
c.sysEnabled = 1 and c.sysAuthorized = 1) t
group by concepto_gasto;");
*/		
?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><?php echo $mostrar_logo_reportes; ?>
</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>
<p><br /><center><font color="#000099" size="+1"><b>Gastos</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="33%"><b>Agrupados por Concepto: </b>Si</TD>
        <TD width="33%"><b>Concepto de Gasto: </b><?php echo $nom_con_gasto; ?></TD>
        <TD width="33%"><b>Concepto de Egreso: </b><?php echo $nom_con_egreso; ?></TD>
        </TR>
        <TR>
        <TD><b>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?></TD>
        <TD><b>Empresa/Empleado: </b><?php echo $nom_empemp; ?></TD>
		<TD><b>Contexto: </b><?php echo $nom_contexto; ?></TD>
		</TR>
        <TR>
		<TD><b>Retención Separada en Impuesto: </b><?php echo $nom_retencion; ?></TD>
		<TD><b>Cuenta Contable del Concepto de Gasto: </b><?php echo $nom_cuenta_gasto; ?></TD>
        <TD><b>Valorado en: </b><?php echo $nom_otra_moneda; ?></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></tr>", 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%">
 	<thead>
    <TR> 
 	<TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Concepto de Gasto</b></TD>
	<TD align="center"><b>Monto</b></TD>
	<TD align="center"><b>Exento</b></TD>
	<TD align="center"><b>Impuestos</b></TD>
	<TD align="center"><b>Monto sin Imp.</b></TD>
    </TR>
    </thead>
    <tbody class="marcafila">

<?php
$color = 2;
$suma_gastos = 0;
$suma_exento = 0;
$suma_impuestos = 0;
$suma_gastos_sin_impuestos = 0;
	
while ($row = mysqli_fetch_array($result)) { //while
		
	$codigo = $row['cod_gasto'];
	$tipo_con = $row['tipo_con'];
	if($color % 2 == 0) { //if color
		printf("<tr BGCOLOR='#e2e7fa'><td align='center'>%s</td>", $row['cod_gasto']);//cod
	} else {
		printf("<tr><td align='center'>%s</td>", $row['cod_gasto']);//cod
	}//cierra if color
	printf("<td align='left'>%s</td>", $row['concepto_gasto']);//concepto gasto
	
	if ($det_orig == 0) { //if detalle moneda original
	printf("<td align='right'><a href='report.php?rep=5&tipo=2&gasto_det=$codigo&otra_moneda_det=$otra_moneda&fechaini_det=$fechaini&fechafin_det=$fechafin&tipo_con_det=$tipo_con&nombre_det=$nombre&contexto_det=$cod_contexto&cuenta_gasto_det=$cuenta_gasto&retencion_separada_det=$retencion_separada&usuario_det=$usuario' title='Detalle de Gastos'>%s</a></td>", number_format($row['monto_om'],$n_decimales));//monto
	} else {
	printf("<td align='right'><a href='report.php?rep=5&tipo=2&gasto_det=$codigo&otra_moneda_det=0&fechaini_det=$fechaini&fechafin_det=$fechafin&tipo_con_det=$tipo_con&nombre_det=$nombre&contexto_det=$cod_contexto&cuenta_gasto_det=$cuenta_gasto&retencion_separada_det=$retencion_separada&usuario_det=$usuario' title='Detalle de Gastos'>%s</a></td>", number_format($row['monto_om'],$n_decimales));//monto	
	} //fin if detalle moneda original
	printf("<td align='right'>%s</td>", number_format($row['exento'],$n_decimales));//exento
	printf("<td align='right'>%s</td>", number_format($row['impuesto'],$n_decimales));//impuestos
	printf("<td align='right'>%s</td></tr>", number_format($row['monto_om'] - $row['impuesto'],$n_decimales));//gasto sin impuestos

	$suma_gastos = $suma_gastos + $row['monto_om'];
	$suma_exento = $suma_exento + $row['exento'];
	$suma_impuestos = $suma_impuestos + $row['impuesto'];
	$suma_gastos_sin_impuestos = $suma_gastos_sin_impuestos + $row['monto_om'] - $row['impuesto'];
	$color++;
}//fin while
mysqli_free_result($result);
	
?>

<tr BGCOLOR='#ffcccc'><td colspan="2"><b>Total</b></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_gastos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_exento,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_impuestos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_gastos_sin_impuestos,$n_decimales)); ?></td>
</tr>
</tbody>
</table>
<?php

}//fin if 1


// inicio sin agrupacion
if($agrupar == 0) { //if 2


if ($documento == 0) { //if documento
	$nom_documento = 'Todos';
	$valor_documento = ' ';
} else { 
	if($documento == 999) { //if documento 2
	$nom_documento = 'Egresos';
	} else {
	//esto es para limitar solo a gasto
	$nom_con_egreso = 'Solo Concepto de Gasto';
  	$valor_con_egreso = ' a.codTypeConcept = '.$con_egreso.' and ';
	//nombre del documento
	$resultdoc = mysqli_query($link, "select pkyDocumentType as codigo, fldName as dcto
	from txsDocumentType 
	where 
	pkyDocumentType = '$documento' and 
	sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultdoc)) {
			$nom_documento = $row['dcto'];
		}
		mysqli_free_result($resultdoc);
	} //fin if documento 2
  	$valor_documento = ' a.codDocumentType = '.$documento.' and ';
} //fin if documento


if ($item == 0) { //if item
	$nom_item = 'Todos';
	$valor_item = ' ';
} else { 
  	$valor_item = ' a.codItem = '.$item.' and ';
	//nombre del item
	$resultitem = mysqli_query($link, "select pkyItem as codigo, fldName as item
	from invItem
	where
	pkyItem = '$item' and 
	sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultitem)) {
			$nom_item = $row['item'];
		}
		mysqli_free_result($resultitem);
} //fin if item

if ($ciudad == 0) { //if ciudad
    $nom_ciudad = 'Todos';
  } else { 
    //nombre de ciudad
	$resultciu = mysqli_query($link, "select pkyCity as codigo, fldName as ciudad
							from
							regCity 
							where
							pkyCity = '$ciudad';");
	while ($row = mysqli_fetch_array($resultciu)) {
	$nom_ciudad = $row['ciudad'];
	}
	mysqli_free_result($resultciu);
  } //fin if ciudad

if ($familia == 0) { // if familia
		$nom_familia = 'Todos';
		$valor_familia = ' ';
	} else {
		//nombre familia
		$resultfam = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$familia' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultfam)) {
			$nom_familia = $row['nombre'];
		}
		mysqli_free_result($resultfam);
		$valor_familia = ' f.codFamily = ' .$familia. ' and ';
	} //fin if familia

if ($subfamilia == 0) { // if subfamilia
		$nom_subfamilia = 'Todos';
		$valor_subfamilia = ' ';
	} else {
		//nombre sub familia
		$resultsubf = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$subfamilia' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsubf)) {
			$nom_subfamilia = $row['nombre'];
		}
		mysqli_free_result($resultsubf);
		$valor_subfamilia = ' f.codSubFamily = ' .$subfamilia. ' and ';
	} //fin if subfamilia
	
if ($grupo == 0) { // if grupo
		$nom_grupo = 'Todos';
		$valor_grupo = ' ';
	} else {
		//nombre grupo
		$resultgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$grupo' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultgru)) {
			$nom_grupo = $row['nombre'];
		}
		mysqli_free_result($resultgru);
		$valor_grupo = ' f.codGroup = ' .$grupo. ' and ';
	} //fin if grupo
	
if ($subgrupo == 0) { // if subgrupo
		$nom_subgrupo = 'Todos';
		$valor_subgrupo = ' ';
	} else {
		//nombre sub grupo
		$resultsubg = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$subgrupo' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsubg)) {
			$nom_subgrupo = $row['nombre'];
		}
		mysqli_free_result($resultsubg);
		$valor_subgrupo = ' f.codSubGroup = ' .$subgrupo. ' and ';
	} //fin if subgrupo


if ($item == 0 && $familia == 0 && $subfamilia == 0 && $grupo == 0 && $subgrupo == 0) {//if item 0
	if($otra_moneda != 0) { //if otra_moneda
	//query que calcula con el tipo de cambio en el que se hizo la transacción
	//si la ciudad es 0 es el query original se aumento el barrido de la tabla invItemMovement al final
	if ($ciudad == 0) { //if ciudad 1
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto_om as monto_om, t.cod_cxp as cod_cxp, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e 
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.concepto_gasto, t.cod_cxp;");
	} else { //else ciudad 1
	//este query si ciudad es != 0 suma las ciudades de los proveedores y los empleados, es el mismo de arriba solo que duplica para calcular por proveedor y empleado y unirlo (mayuscula UNION ALL es la union), para sumar tambien los movimientos de egreso al final se aumenta el barrido de invItemMovement UNION all solo una vez no se duplica no es necesario ya que no hay dos opciones como empresa y persona 
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto_om as monto_om, t.cod_cxp as cod_cxp, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	
		
	
	UNION ALL
	
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	
		UNION all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
		union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.concepto_gasto, t.cod_cxp;");
	} //fin if ciudad 1
	
	//query que calcula con el tipo de cambio de la fecha final
	/*
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, 
	t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto_om as monto_om, t.cod_cxp as cod_cxp
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	d.codExchangeRate = '$ultimo_tc' and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as  cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e
	where
	$valor_con_gasto 
	$valor_documento 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	d.codExchangeRate = '$ultimo_tc' and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e
	where
	$valor_con_gasto 
	$valor_documento 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1) t 
	order by t.concepto_gasto, t.cod_cxp;");
	*/
	
	//cuenta el numero de filas para sacar total por cada concepto (mismo query arriba, sin empresa_empleado)
	//con el tc transacción
	if ($ciudad == 0) { //if ciudad 2
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.concepto_gasto as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e 
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.concepto_gasto
	order by t.concepto_gasto, t.cod_cxp;");
	} else { //else ciudad 2
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.concepto_gasto as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	
	
	UNION ALL
	
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, txsDocumentType e, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	
	UNION all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
		union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.concepto_gasto
	order by t.concepto_gasto, t.cod_cxp;");
	} //fin if ciudad 2
	
	//con el tc fecha final
	/*
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.concepto_gasto as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	d.codExchangeRate = '$ultimo_tc' and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e
	where
	$valor_con_gasto 
	$valor_documento 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	d.codExchangeRate = '$ultimo_tc' and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e
	where
	$valor_con_gasto 
	$valor_documento 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1) t 
	group by t.concepto_gasto
	order by t.concepto_gasto, t.cod_cxp;");
	*/
	} else {
	
	//query que calcula con el tipo de cambio de la fecha final y saca el cod moneda de la cxp
	if ($ciudad == 0) { //if ciudad 3
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto,	t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto as monto_om, t.cod_cxp as cod_cxp, t.moneda as moneda, t.cod_moneda as cod_moneda, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");	
	
	
	} else { //else ciudad 3
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto,	t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto as monto_om, t.cod_cxp as cod_cxp, t.moneda as moneda, t.cod_moneda as cod_moneda, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, agnCompany h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	
	UNION ALL
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, agnPerson h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d,
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");
	} //fin if ciudad 3
	
	//con el tc fecha final
	if ($ciudad == 0) { //if ciudad 4
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.moneda as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.moneda 
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");
	} else { //else ciudad 4
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.moneda as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, agnCompany h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	
	UNION ALL
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, agnPerson h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d,
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.moneda 
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");
	} //fin if ciudad 4
	
	} //fin if otra_moneda

} else {

	if($otra_moneda != 0) { //if otra_moneda
	//query que calcula con el tipo de cambio en el que se hizo la transacción
	if ($ciudad == 0) { //if ciudad 5
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto_om as monto_om, t.cod_cxp as cod_cxp, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.concepto_gasto, t.cod_cxp;");
	} else { //else ciudad 5
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto_om as monto_om, t.cod_cxp as cod_cxp, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	UNION ALL 
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	UNION all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
		union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as emplesa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.concepto_gasto, t.cod_cxp;");
	} //fin if ciudad 5
	
	//query que calcula con el tipo de cambio de la fecha final
	/*
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, 
	t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto_om as monto_om, t.cod_cxp as cod_cxp
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	d.codExchangeRate = '$ultimo_tc' and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 ) t 
	order by t.concepto_gasto, t.cod_cxp;");
	*/
	//cuenta el numero de filas para sacar total por cada concepto (mismo query arriba)
	// con el tc transacción
	if ($ciudad == 0) { //if ciudad 6
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.concepto_gasto as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.concepto_gasto
	order by t.concepto_gasto, t.cod_cxp;");
	} else { //else ciudad 6
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.concepto_gasto as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 
	
	
	UNION ALL
	
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	c.codExchangeRate = d.codExchangeRate and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, c.codExchangeRate as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	UNION all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codExchangeRate = d.codExchangeRate and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = a.codCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1
	
		union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, a.codExchangeRate as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, 
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = '$otra_moneda' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.concepto_gasto
	order by t.concepto_gasto, t.cod_cxp;");
	} //fin if ciudad 6
	
	//con el tc fecha final
	/*
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.concepto_gasto as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	d.codExchangeRate = '$ultimo_tc' and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	d.codBaseCurrency = '$otra_moneda' and 
	d.codTargetCurrency = c.codCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
	f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
	where
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_gasto 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and 
	c.codCurrency = '$otra_moneda' and
	c.sysEnabled = 1 and c.sysAuthorized = 1 ) t 
	group by t.concepto_gasto
	order by t.concepto_gasto, t.cod_cxp;");
	*/
	} else {
	
	//query que calcula con el tipo de cambio de la fecha final
	if ($ciudad == 0) { //if ciudad 7
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto as monto_om, t.cod_cxp as cod_cxp, t.moneda as moneda, t.cod_moneda as cod_moneda, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa 
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g 
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and   
	a.codAccountsPayable = c.pkyAccountsPayable and   
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");
	
	//con el tc fecha final
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.moneda as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and   
	a.codAccountsPayable = c.pkyAccountsPayable and   
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d, 
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.moneda
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");
	} else { //else ciudad 7
	$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, t.cod_doc as cod_doc, t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto as monto_om, t.cod_cxp as cod_cxp, t.moneda as moneda, t.cod_moneda as cod_moneda, t.empresa_empleado as empresa_empleado, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h 
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and   
	a.codAccountsPayable = c.pkyAccountsPayable and   
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	UNION ALL
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, a.codDocumentType as cod_doc, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnPerson h 
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and 
	a.codExpensesConcept = b.pkyExpensesConcept and   
	a.codAccountsPayable = c.pkyAccountsPayable and   
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 0 as cod_doc, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d,
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");
	
	//con el tc fecha final
	$result_filas = mysqli_query($link, "select count(*) as no_filas, t.moneda as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnCompany h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codSupplier = h.pkyCompany and h.codCityLocation = '$ciudad' and
	a.codExpensesConcept = b.pkyExpensesConcept and   
	a.codAccountsPayable = c.pkyAccountsPayable and   
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	UNION ALL
	
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g, agnPerson h
	where
	$valor_con_gasto 
	$valor_empresa 
	$valor_empleado 
	$valor_ver_cxp 
	$valor_documento 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto2 
	$valor_cuenta_gasto 
	b.pkyExpensesConcept <> 20 and 
	c.codEmployee = h.pkyPerson and h.codCityLocation = '$ciudad' and
	a.codExpensesConcept = b.pkyExpensesConcept and   
	a.codAccountsPayable = c.pkyAccountsPayable and   
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini' and 
	c.fldDate <= '$fechafin' and  
	c.codCurrency = d.pkyCurrency and 
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo,  a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d,
	 invItem f, invItemCategory g, invItem h, invWarehouse i
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso 
	$valor_item 
	$valor_familia 
	$valor_subfamilia 
	$valor_grupo 
	$valor_subgrupo 
	$valor_contexto 
	a.codSourceWarehouse = i.pkyWarehouse and 
	i.codCity = '$ciudad' and
	a.fldDate >= '$fechaini' and 
	a.fldDate <= '$fechafin' and  
	a.codCurrency = d.pkyCurrency and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	group by t.moneda
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");
	} //fin if ciudad 7
	
	} //fin if otra_moneda
	
} //fin if item 0

//cuenta las filas para mostrar los resultados el campo con_gasto es el mismo en moneda = 0 y moneda != 0, pero en moneda = 0 es moneda en realidad
$mostrar_filas = array();
$i = 1;
while ($row = mysqli_fetch_array($result_filas)) {
	$mostrar_filas[$i][1] = $row['con_gasto'];
	$mostrar_filas[$i][2] = $row['no_filas'];
	//echo $mostrar_filas[$i][1] . " - " . $mostrar_filas[$i][2];
	$i++;
}
mysqli_free_result($result_filas);

$fin_ver = $i - 1;

$mostrar_resultado = array();
$i = 1;
while ($row = mysqli_fetch_array($result)) { //while
	$mostrar_resultado[$i][1] = $row['cod_gasto'];
	$mostrar_resultado[$i][2] = $row['concepto_gasto'];
	$mostrar_resultado[$i][3] = $row['documento'];
	$mostrar_resultado[$i][4] = $row['item'];
	$mostrar_resultado[$i][5] = $row['familia'];
	$mostrar_resultado[$i][6] = $row['subfamilia'];
	$mostrar_resultado[$i][7] = $row['grupo'];
	$mostrar_resultado[$i][8] = $row['subgrupo'];
	$mostrar_resultado[$i][9] = $row['monto_om'];
	$mostrar_resultado[$i][10] = $row['cod_cxp'];
	if($otra_moneda == 0) { //if otra_moneda 2
	$mostrar_resultado[$i][11] = $row['moneda'];//solo se usa cuando la moneda es 0
	$mostrar_resultado[$i][12] = $row['cod_moneda'];//solo se usa cuando la moneda es 0
	}// fin if otra_moneda 2
	$mostrar_resultado[$i][13] = $row['empresa_empleado'];
	$mostrar_resultado[$i][14] = $row['cod_doc'];
	$mostrar_resultado[$i][15] = $row['cod_det'];
	
	//para enlazar al que genero el asiento
	$papa_componente = $row['papa'];
	$papa_codigo = $row['cod_papa'];
	//$usuario = $usuario_det;
	include "../../generadopor.php";
	$mostrar_resultado[$i][16] = $papa_enlace;
	$i++;
} //fin while
mysqli_free_result($result);

$fin_res = $i - 1;

//verificar si la retención se separa en impuestos 1=si, 0=no
if($retencion_separada == 1){
	$nom_retencion = 'Si';
} else {
	$nom_retencion = 'No';
}

?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><?php echo $mostrar_logo_reportes; ?>
</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>
<p><br /><center><font color="#000099" size="+1"><b>Gastos</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>Agrupados por Concepto: </b>No</TD>
        <TD width="25%"><b>Concepto de Gasto: </b><?php echo $nom_con_gasto; ?></TD>
        <TD width="25%"><b>Tipo de Documento: </b><?php echo $nom_documento; ?></TD>
        <TD width="25%"><b>Item: </b><?php echo $nom_item; ?></TD>
        </TR>
        <TR>
        <TD><b>Familia: </b><?php echo $nom_familia; ?></TD>
        <TD><b>Sub Familia: </b><?php echo $nom_subfamilia; ?></TD>
        <TD><b>Grupo: </b><?php echo $nom_grupo; ?></TD>
        <TD><b>Sub Grupo: </b><?php echo $nom_subgrupo; ?></TD>
        </TR>
        <TR>
        <TD><b>Empresa/Empleado: </b><?php echo $nom_empemp; ?></TD>
        <TD><b>Concepto de Egreso (Mov. Merc.): </b><?php echo $nom_con_egreso; ?></TD>
        <TD><b>Ciudad (Prov./Emp./Alm. Orig.): </b><?php echo $nom_ciudad; ?></TD>
        <TD><b>Contexto: </b><?php echo $nom_contexto; ?></TD>
        </TR>
        <TR>
        <TD><b>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?></TD>
		<TD><b>Retención Separada en Impuesto: </b><?php echo $nom_retencion; ?></TD>
		<TD><b>Cuenta Contable del Concepto de Gasto: </b><?php echo $nom_cuenta_gasto; ?></TD>
        <TD><b>Valorado en: </b><?php echo $nom_otra_moneda; ?></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></tr>", 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%">
 	<thead>
    <TR> 
 	<TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Concepto de Gasto</b></TD>
    <TD align="center"><b>Empresa/Empleado</b></TD>
    <TD align="center"><b>Tipo de Doc.</b></TD>
    <TD align="center"><b>Item</b></TD>
    <TD align="center"><b>Familia</b></TD>
    <TD align="center"><b>Sub Familia</b></TD>
    <TD align="center"><b>Grupo</b></TD>
    <TD align="center"><b>Sub Grupo</b></TD>
    <TD align="center"><b>Gen. Por</b></TD>
    <?php if($otra_moneda == 0) { ?>
	<TD align="center"><b>Mon.</b></TD>
    <?php } ?>
    <TD align="center"><b>Monto</b></TD>
    <TD align="center"><b>Exento</b></TD>
    <TD align="center"><b>Impuestos</b></TD>
	<TD align="center"><b>Monto sin Imp.</b></TD>
    <TD align="center"><b>Pagos</b></TD>
    <TD align="center"><b>Saldo</b></TD>
    </TR>
    </thead>
    <tbody class="marcafila">

<?php

$color = 2;
$suma_gastos = 0;
$suma_pagos_total = 0;
$suma_saldo = 0;
$suma_exento = 0;
$suma_impuestos = 0;
$suma_gastos_sin_impuestos = 0;
$j = 1;
$i = 1;

while($i <= $fin_res) { //while principal
  $suma_gastos_fila = 0;
  $suma_pagos_fila = 0;
  $suma_saldo_fila = 0;
  $suma_exento_fila = 0;
  $suma_impuestos_fila = 0;
  $suma_gastos_sin_impuestos_fila = 0;
  for($k = 1; $k <= $mostrar_filas[$j][2]; $k++) { //for mostrar filas		
	$cod_mov = $mostrar_resultado[$i][1];
	$cod_cxp = $mostrar_resultado[$i][10];
	$cod_doc = $mostrar_resultado[$i][14];//cod del tipo de documento
	$cod_det = $mostrar_resultado[$i][15];//cod del detalle del mov. solo en payaccoutspayabledetail
	if($cod_cxp != 0) { //if cod_cxp
		
		if($color % 2 == 0) { //if color1
			printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModulePayments/VoucherAccountsPayable/index.php?pkyAccountsPayable=$cod_cxp&user=$usuario' title='Nota de Cuenta por Pagar'>%s</a></td>", $mostrar_resultado[$i][10]);//cod cxp
		} else {
			printf("<tr><td align='center'><a href='../../ModulePayments/VoucherAccountsPayable/index.php?pkyAccountsPayable=$cod_cxp&user=$usuario' title='Nota de Cuenta por Pagar'>%s</a></td>", $mostrar_resultado[$i][10]);//cod cxp
		}//cierra if color1
	
	} else { //else if cod_cxp
	
		if($color % 2 == 0) { //if color2
			printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModuleInventory/VoucherItemMovement/index.php?pkyItemMovement=$cod_mov&showBatch=0&printAccountEntry=0&user=$usuario' title='Movimiento de Mercader&iacute;a'>%s</a></td>", $mostrar_resultado[$i][1]);//cod mov
		} else {
			printf("<tr><td align='center'><a href='../../ModuleInventory/VoucherItemMovement/index.php?pkyItemMovement=$cod_mov&showBatch=0&printAccountEntry=0&user=$usuario' title='Movimiento de Mercader&iacute;a'>%s</a></td>", $mostrar_resultado[$i][1]);//cod mov
		}//cierra if color2
		
	} //fin if cod_cxp
	printf("<td align='left'>%s</td>", $mostrar_resultado[$i][2]);//concepto gasto
	printf("<td align='left'>%s</td>", $mostrar_resultado[$i][13]);//empresa / empleado
	printf("<td align='left'>%s</td>", $mostrar_resultado[$i][3]);//documento
	printf("<td align='left'>%s</td>", $mostrar_resultado[$i][4]);//item
	printf("<td align='left'>%s</td>", $mostrar_resultado[$i][5]);//familia
	
	$moneda_ = $mostrar_resultado[$i][11]; //moneda 
	$cod_moneda_ = $mostrar_resultado[$i][12]; //cod moneda
	$monto_om_ = $mostrar_resultado[$i][9]; //monto
	$subfamilia_ = $mostrar_resultado[$i][6]; //subfamilia
	$grupo_ = $mostrar_resultado[$i][7]; //grupo
	$subgrupo_ = $mostrar_resultado[$i][8]; //subgrupo
	
	if ($subfamilia_ <> NULL) { //if subfamilia
		$resultfam = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from invItemCategory
							where
							pkyItemCategory = '$subfamilia_' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultfam)) {
			$nom_subfamilia_ = $row['nombre'];
		}
		mysqli_free_result($resultfam);
		printf("<td align='left'>%s</td>", $nom_subfamilia_);//subfamilia
	} else {
		printf("<td align='left'>%s</td>", $mostrar_resultado[$i][6]);//subfamilia
	} //fin if subfamilia
	
	if ($grupo_ <> NULL) { //if grupo
		$resultgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from invItemCategory
							where
							pkyItemCategory = '$grupo_' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultgru)) {
			$nom_grupo_ = $row['nombre'];
		}
		mysqli_free_result($resultgru);
		printf("<td align='left'>%s</td>", $nom_grupo_);//grupo
	} else {
		printf("<td align='left'>%s</td>", $mostrar_resultado[$i][7]);//grupo
	} //fin if grupo
	
	if ($subgrupo_ <> NULL) { //if subgrupo
		$resultsgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from invItemCategory
							where
							pkyItemCategory = '$subgrupo_' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsgru)) {
			$nom_subgrupo_ = $row['nombre'];
		}
		mysqli_free_result($resultsgru);
		printf("<td align='left'>%s</td>", $nom_subgrupo_);//subgrupo
	} else {
		printf("<td align='left'>%s</td>", $mostrar_resultado[$i][8]);//subgrupo
	} //fin if subgrupo
	
	printf("<td align='center'>%s</td>", $mostrar_resultado[$i][16]);//gen por
	if($otra_moneda == 0) {
		printf("<td align='left'>%s</td>", $moneda_);//moneda
	}
	printf("<td align='right'>%s</td>", number_format($monto_om_,$n_decimales));//monto
	
	$suma_pagos = 0;
	
	if($otra_moneda != 0) {//if otra moneda 3
		//suma Pagos (son con el tc de la fecha en la que se hizo el pago)
		$result5 = mysqli_query($link, "select final.cod_cxp as cod_cxp, sum(final.total) as suma_pagos
			from(
			select otra.cod_cxp as cod_cxp, otra.suma_pagos as suma_pagos,
			 otra.cod_moneda_pago as cod_moneda_pago, otra.tc as tc, 
			 otra.cod_moneda_cxp as cod_moneda_cxp, 
			 otra.otra_moneda as otra_moneda, otra.tasa as tasa, 
			 otra.suma_pagos * otra.tasa as total
			from
			(select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
			t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
			t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
			r.fldRate as tasa
			from
			(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$otra_moneda' as otra_moneda
			from payPayment a, payPaymentDetail b, payAccountsPayable c
			where 
			$valor_ver_cxp 
			$valor_contexto 
			a.pkyPayment = b.codPayment and
			a.fldDate >= '$fechaini' and
			a.fldDate <= '$fechafin' and 
			b.codAccountsPayable is not null and
			b.codAccountsPayable = c.pkyAccountsPayable and 
			c.pkyAccountsPayable = '$cod_cxp' and 
			a.sysEnabled = 1 and a.sysAuthorized = 1 
			group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t,
			actExchangeRateDetail r
			where
			t.tc = r.codExchangeRate and
			t.cod_moneda_cxp = r.codTargetCurrency and
			t.otra_moneda = r.codBaseCurrency
			union all
			select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
			t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
			t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
			1 as tasa
			from
			(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$otra_moneda' as otra_moneda
			from payPayment a, payPaymentDetail b, payAccountsPayable c
			where 
			$valor_ver_cxp 
			$valor_contexto 
			a.pkyPayment = b.codPayment and
			a.fldDate >= '$fechaini' and
			a.fldDate <= '$fechafin' and 
			b.codAccountsPayable is not null and
			b.codAccountsPayable = c.pkyAccountsPayable and 
			c.pkyAccountsPayable = '$cod_cxp' and 
			a.sysEnabled = 1 and a.sysAuthorized = 1 
			group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t
			where
			t.cod_moneda_cxp = t.otra_moneda) otra) final
			group by final.cod_cxp
			order by final.cod_cxp;");	
			
		while ($row = mysqli_fetch_array($result5)) {
				$suma_pagos = $row['suma_pagos'];
			}
			mysqli_free_result($result5);
		
		//CxP 
		$result3 = mysqli_query($link, "select final.cod_cxp as cod_cxp, final.total as cxp
		from(
		select otra.cod_cxp as cod_cxp, otra.cxp as cxp, otra.cod_moneda as cod_moneda,
		otra.tc as tc, otra.otra_moneda as otra_moneda, otra.tasa as tasa, otra.cxp * otra.tasa as total
		from
		(select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
		t.tc as tc, t.otra_moneda as otra_moneda, r.fldRate as tasa
		from
		(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$otra_moneda' as otra_moneda
		from payAccountsPayable a
		where
		$valor_ver_cxp2
		$valor_contexto 
		a.pkyAccountsPayable = '$cod_cxp' and
		a.sysEnabled = 1 and a.sysAuthorized = 1) t,
		actExchangeRateDetail r
		where
		t.tc = r.codExchangeRate and
		t.cod_moneda = r.codTargetCurrency and
		t.otra_moneda = r.codBaseCurrency
		union all
		select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
		t.tc as tc, t.otra_moneda as otra_moneda, 1 as tasa
		from
		(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$otra_moneda' as otra_moneda
		from payAccountsPayable a
		where
		$valor_ver_cxp2
		$valor_contexto 
		a.pkyAccountsPayable = '$cod_cxp' and
		a.sysEnabled = 1 and a.sysAuthorized = 1) t
		where
		t.cod_moneda = t.otra_moneda) otra) final;");
			while ($row = mysqli_fetch_array($result3)) {
				$total_cxp = $row['cxp'];
			}
			mysqli_free_result($result3);
		
	} else {
		//son los mismos querys de arriba solo que la moneda es cod_moneda_
		//suma Pagos (son con el tc de la fecha en la que se hizo el pago, se usa como moneda cod_moneda_)
		$result5 = mysqli_query($link, "select final.cod_cxp as cod_cxp, sum(final.total) as suma_pagos
			from(
			select otra.cod_cxp as cod_cxp, otra.suma_pagos as suma_pagos,
			 otra.cod_moneda_pago as cod_moneda_pago, otra.tc as tc, 
			 otra.cod_moneda_cxp as cod_moneda_cxp, 
			 otra.otra_moneda as otra_moneda, otra.tasa as tasa, 
			 otra.suma_pagos * otra.tasa as total
			from
			(select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
			t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
			t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
			r.fldRate as tasa
			from
			(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$cod_moneda_' as otra_moneda
			from payPayment a, payPaymentDetail b, payAccountsPayable c
			where
			$valor_ver_cxp
			$valor_contexto 
			a.pkyPayment = b.codPayment and
			a.fldDate >= '$fechaini' and
			a.fldDate <= '$fechafin' and 
			b.codAccountsPayable is not null and
			b.codAccountsPayable = c.pkyAccountsPayable and 
			c.pkyAccountsPayable = '$cod_cxp' and 
			a.sysEnabled = 1 and a.sysAuthorized = 1 
			group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t,
			actExchangeRateDetail r
			where
			t.tc = r.codExchangeRate and
			t.cod_moneda_cxp = r.codTargetCurrency and
			t.otra_moneda = r.codBaseCurrency
			union all
			select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
			t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
			t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
			1 as tasa
			from
			(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$cod_moneda_' as otra_moneda
			from payPayment a, payPaymentDetail b, payAccountsPayable c
			where
			$valor_ver_cxp
			$valor_contexto 
			a.pkyPayment = b.codPayment and
			a.fldDate >= '$fechaini' and
			a.fldDate <= '$fechafin' and 
			b.codAccountsPayable is not null and
			b.codAccountsPayable = c.pkyAccountsPayable and 
			c.pkyAccountsPayable = '$cod_cxp' and 
			a.sysEnabled = 1 and a.sysAuthorized = 1 
			group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t
			where
			t.cod_moneda_cxp = t.otra_moneda) otra) final
			group by final.cod_cxp
			order by final.cod_cxp;");	
		
		while ($row = mysqli_fetch_array($result5)) {
				$suma_pagos = $row['suma_pagos'];
			}
			mysqli_free_result($result5);
		//CxP se usa como moneda cod_moneda_
		$result3 = mysqli_query($link, "select final.cod_cxp as cod_cxp, final.total as cxp
		from(
		select otra.cod_cxp as cod_cxp, otra.cxp as cxp, otra.cod_moneda as cod_moneda,
		otra.tc as tc, otra.otra_moneda as otra_moneda, otra.tasa as tasa, otra.cxp * otra.tasa as total
		from
		(select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
		t.tc as tc, t.otra_moneda as otra_moneda, r.fldRate as tasa
		from
		(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$cod_moneda_' as otra_moneda
		from payAccountsPayable a
		where
		$valor_ver_cxp2
		$valor_contexto 
		a.pkyAccountsPayable = '$cod_cxp' and
		a.sysEnabled = 1 and a.sysAuthorized = 1) t,
		actExchangeRateDetail r
		where
		t.tc = r.codExchangeRate and
		t.cod_moneda = r.codTargetCurrency and
		t.otra_moneda = r.codBaseCurrency
		union all
		select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
		t.tc as tc, t.otra_moneda as otra_moneda, 1 as tasa
		from
		(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$cod_moneda_' as otra_moneda
		from payAccountsPayable a
		where
		$valor_ver_cxp2
		$valor_contexto 
		a.pkyAccountsPayable = '$cod_cxp' and
		a.sysEnabled = 1 and a.sysAuthorized = 1) t
		where
		t.cod_moneda = t.otra_moneda) otra) final;");
			while ($row = mysqli_fetch_array($result3)) {
				$total_cxp = $row['cxp'];
			}
			mysqli_free_result($result3);
	}//fin if otra moneda 3	
	
	if($suma_pagos == NULL) { $suma_pagos = 0; }
	
	if($total_cxp != 0) {
		$coef = $suma_pagos / $total_cxp;
	} else {
		$coef = $suma_pagos;
	}
	
	if($cod_cxp != 0) {
		$saldo_ = $monto_om_ - ($monto_om_ * $coef);
	} else {
		$saldo_ = 0;
	}
		

	$porcentaje_ret = 0;
	$exento_ = 0;
	//compara si tiene cxp para el impuesto
	if($cod_cxp != 0) {
		//si es 1 factura, 2 posterior, 5 boleto y 7 factura grupo
		if($cod_doc == 1 || $cod_doc == 2 || $cod_doc == 5 || $cod_doc == 7) {
			//si 1 exento
			if($cod_doc == 1 || $cod_doc == 7) {
				$result77 = mysqli_query($link, "select fldExempt as exento 
				from txsPurchasesLedger 
				where 
				pkyPurchasesLedger in (select fldInvoice from payAccountsPayableDetail where pkyAccountsPayableDetail = '$cod_det');");
				while ($row = mysqli_fetch_array($result77)) {
					$exento_ = $row['exento'];
				}
				mysqli_free_result($result77);
			}
			$impuestos_ = ($monto_om_ - $exento_) * $monto_iva / 100;//$monto_iva viene de general
		} else {
			//si es 4 poliza
			if($cod_doc == 4) {
				$impuestos_ = $monto_om_;
			} else {
				//si 3 retencion
				if($cod_doc == 3) {
					$result7 = mysqli_query($link, "select sum(fldPercent) as porcentaje
						from txsRetentionDetail
						where
						codAccountsPayableDetail = '$cod_det'
						group by codAccountsPayableDetail;");
					while ($row = mysqli_fetch_array($result7)) {
						$porcentaje_ret = $row['porcentaje'];
					}
					mysqli_free_result($result7);
					$impuestos_ = $monto_om_ * $porcentaje_ret / 100;
				} else {
					$impuestos_ = 0;
				}

				//verificar si la retención se separa en impuestos 1=si, 0=no
				if($retencion_separada == 0){
					$impuestos_ = 0;
				}
			}
		}
	}
	$pagos_ = $monto_om_ - $saldo_;
	
	printf("<td align='right'>%s</td>", number_format($exento_, $n_decimales));//exento
	printf("<td align='right'>%s</td>", number_format($impuestos_, $n_decimales));//impuestos
	printf("<td align='right'>%s</td>", number_format($monto_om_ - $impuestos_, $n_decimales));//monto sin impuestos
	printf("<td align='right'>%s</td>", number_format($pagos_, $n_decimales));//pagado
	printf("<td align='right'>%s</td></tr>", number_format($saldo_, $n_decimales));//saldo
	
	$suma_saldo = $suma_saldo + $saldo_;
	$suma_pagos_total = $suma_pagos_total + $pagos_;
	$suma_gastos = $suma_gastos + $monto_om_;
	$suma_exento = $suma_exento + $exento_;
	$suma_impuestos = $suma_impuestos + $impuestos_;
	$suma_gastos_sin_impuestos = $suma_gastos_sin_impuestos + $monto_om_ - $impuestos_;
	$suma_saldo_fila = $suma_saldo_fila + $saldo_;
	$suma_pagos_fila = $suma_pagos_fila + $pagos_;
	$suma_gastos_fila = $suma_gastos_fila + $monto_om_;
	$suma_exento_fila = $suma_exento_fila + $exento_;
	$suma_impuestos_fila = $suma_impuestos_fila + $impuestos_;
	$suma_gastos_sin_impuestos_fila = $suma_gastos_sin_impuestos_fila + $monto_om_ - $impuestos_;
	$color++;
	$i++;
  }// fin for mostrar filas
  
  if($otra_moneda != 0) {//if otra moneda 4
  printf("<tr BGCOLOR='#ffcccc'><td colspan='10'><b>Total %s</b></td>", $mostrar_filas[$j][1]);//titulo total fila
  } else {
  printf("<tr BGCOLOR='#ffcccc'><td colspan='11'><b>Total %s</b></td>", $mostrar_filas[$j][1]);//titulo total fila 
  }//fin if otra moneda 4
  
  printf("<td align='right'><b>%s</b></td>", number_format($suma_gastos_fila,$n_decimales));//total gastos fila
  printf("<td align='right'><b>%s</b></td>", number_format($suma_exento_fila,$n_decimales));//total exento fila
  printf("<td align='right'><b>%s</b></td>", number_format($suma_impuestos_fila,$n_decimales));//total impu fila
  printf("<td align='right'><b>%s</b></td>", number_format($suma_gastos_sin_impuestos_fila,$n_decimales));//total gasto sin impu fila
  printf("<td align='right'><b>%s</b></td>", number_format($suma_pagos_fila,$n_decimales));//total pagos fila
  printf("<td align='right'><b>%s</b></td></tr>", number_format($suma_saldo_fila,$n_decimales));//total saldo fila
  
  $j++;
}//fin for principal

?>
<?php if($otra_moneda != 0) { ?>
<tr BGCOLOR='#ffcccc'><td colspan="10"><b>Total</b></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_gastos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_exento,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_impuestos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_gastos_sin_impuestos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_pagos_total,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_saldo,$n_decimales)); ?></td>
</tr>
<?php } ?>
</tbody>
</table>
<?php

} //fin if 2
} //fin if rep




// inicio muestra detalle

if($rep == 5 && $tipo == 2) { // if 3

$empresaoempleado_det = substr($nombre_det, -2);
$nombreemp_det = substr($nombre_det, 0, -3);

if ($empresaoempleado_det == -1) { // if 1.1
	
	if($nombreemp_det == 0) {
		$nom_empemp_det = 'Empresas';
		$valor_empresa_det = ' ';
	} else { 
		$resultprov = mysqli_query($link, "select pkyCompany as codigo, fldName as empresa
								from agnCompany
								where
								pkyCompany = '$nombreemp_det' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultprov)) {
			$nom_empemp_det = $row['codigo'].' - '.$row['empresa'].' (Empresa)';
		}
		mysqli_free_result($resultprov);
		$valor_empresa_det = ' c.codSupplier = '. $nombreemp_det .' and ';
	}
	$valor_empleado_det = ' c.codEmployee is NULL and ';
	$con_egreso_det = -1;
}//fin if 1.1

if ($empresaoempleado_det == -2) { // if 1.2
	
	if($nombreemp_det == 0) {
		$nom_empemp_det = 'Empleados';
		$valor_empleado_det = ' ';
	} else { 
		$resultprov = mysqli_query($link, "select pkyPerson as codigo, case when fldMiddleName is NULL then concat(fldLastName,', ',fldFirstName) else concat(fldLastName,' ',fldMiddleName,', ',fldFirstName) end as empleado
								from agnPerson
								where
								pkyPerson = '$nombreemp_det' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultprov)) {
			$nom_empemp_det = $row['codigo'].' - '.$row['empleado'].' (Empleado)';
		}
		mysqli_free_result($resultprov);
		$valor_empleado_det = ' c.codEmployee = '. $nombreemp_det .' and ';
	}
	$valor_empresa_det = ' c.codSupplier is NULL and ';
	$con_egreso_det = -1;
}//fin if 1.2

if ($empresaoempleado_det == -3) { // if 1.3
	
	$nom_empemp_det = 'Todos';
	$valor_empresa_det = ' ';
	$valor_empleado_det = ' ';
}//fin if 1.3

//tipo de cambio a la fecha final
$resultom = mysqli_query($link, "SELECT T2.pkyExchangeRate as tc FROM 
(SELECT fldDate, MAX(fldTime) AS fldTime FROM actExchangeRate WHERE fldExclusive = '0' GROUP BY fldDate) as T1,
actExchangeRate T2 
WHERE 
T1.fldDate = (SELECT MAX(fldDate) FROM actExchangeRate WHERE fldDate <= '$fechafin_det' AND fldExclusive = '0') AND 
T2.fldDate = T1.fldDate AND 
T2.fldTime = T1.fldTime AND 
sysEnabled = 1 AND sysAuthorized = 1;");
while ($row = mysqli_fetch_array($resultom)) {
	$ultimo_tc_det = $row['tc'];
	}
	mysqli_free_result($resultom);

//tipo de cambio para la cabecera
$resulttc = mysqli_query($link, "select pkyExchangeRate as cod_tc, fldDate as fecha, fldTime as hora
from
actExchangeRate
where
pkyExchangeRate = '$ultimo_tc_det' and 
sysEnabled = 1 and sysAuthorized = 1;");
while ($row = mysqli_fetch_array($resulttc)) {
	$tc_fecha = $row['fecha'];
	$tc_hora = $row['hora'];
}
mysqli_free_result($resulttc);

$resulttcdet = mysqli_query($link, "select a.pkyExchangeRateDetail as cod_tc_det, a.codBaseCurrency as cod_mon_target, b.fldName as mon_target, b.txtDescription as nom_mon_target, a.codTargetCurrency as cod_mon_base, c.fldName as mon_base, c.txtDescription as nom_mon_base, a.fldRate as tasa
from
actExchangeRateDetail a, actCurrency b, actCurrency c
where
a.codExchangeRate = '$ultimo_tc_det' and 
a.codBaseCurrency = '$cod_moneda_principal' and 
a.codBaseCurrency = b.pkyCurrency and 
a.codTargetCurrency = c.pkyCurrency 
order by cod_mon_target;");
$ma_tc = array();
$m = 1;
while ($row = mysqli_fetch_array($resulttcdet)) {
	$ma_tc [$m][1] = $row['mon_base'];
	$ma_tc [$m][2] = $row['mon_target'];
	$ma_tc [$m][3] = $row['tasa'];
	$m++;
}
mysqli_free_result($resulttcdet);
$fin_m = $m - 1;

//nombre otra moneda
if($otra_moneda_det != 0) {//if otra moneda
$resultotra = mysqli_query($link, "SELECT fldName as otra_moneda
from
actCurrency
where
pkyCurrency = '$otra_moneda_det' and
sysEnabled = 1 and sysAuthorized = 1;");
		
	while ($row = mysqli_fetch_array($resultotra)) {
	$nom_otra_moneda_det = $row['otra_moneda'];
	}
	mysqli_free_result($resultotra);
} else {
	$nom_otra_moneda_det = 'Moneda original';
}//fin if otra moneda 

$con_egreso_det = $con_gasto_det;

if($tipo_con_det == 0) { $con_egreso_det = -1; }
if($tipo_con_det == 1) { $con_gasto_det = -1; }


if ($con_gasto_det == 0) { //if concepto de gasto
	$nom_con_gasto_det = 'Todos';
	$valor_con_gasto_det = ' ';
} else { 
  	$valor_con_gasto_det = ' a.codExpensesConcept = '.$con_gasto_det.' and ';
	//nombre del concepto de gasto
	$resultgasto = mysqli_query($link, "select pkyExpensesConcept as codigo, fldName as gasto
	from payExpensesConcept 
	where
	pkyExpensesConcept = '$con_gasto_det' and 
	sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultgasto)) {
			$nom_con_gasto_det = $row['gasto'];
		}
		mysqli_free_result($resultgasto);
} //fin if concepto de gasto

if ($con_egreso_det == 0) { //if concepto de egreso
	$nom_con_egreso_det = 'Todos';
	$valor_con_egreso_det = ' ';
} else { 
  	$valor_con_egreso_det = ' a.codTypeConcept = '.$con_egreso_det.' and ';
	//nombre del concepto de egreso
	$resultegreso = mysqli_query($link, "select pkyItemMovementTypeConcept as codigo, fldName as egreso
	from
	invItemMovementTypeConcept
	where
	pkyItemMovementTypeConcept = '$con_egreso_det' and 
	sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultegreso)) {
			$nom_con_egreso_det = $row['egreso'];
		}
		mysqli_free_result($resultegreso);
} //fin if concepto de egreso

if ($cuenta_gasto_det == 0) { // if cuenta_gasto
	$nom_cuenta_gasto_det = 'Todos';
	$valor_cuenta_gasto_det = ' ';
} else {
	$valor_cuenta_gasto_det = ' b.codAccount = ' .$cuenta_gasto_det. ' and ';
	//nombre de la cuenta contable del almacen
	$result2 = mysqli_query($link, "select pkyAccount, fldName 
							   from actAccount 
							   where 
							pkyAccount = '$cuenta_gasto_det' and
							sysEnabled = 1 and sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($result2)) {
		$nom_cuenta_gasto_det = $row['fldName'];
	}
	mysqli_free_result($result2);
} //fin if cuenta_gasto

//contexto
if($cod_contexto_det == -1){ 
	$nom_contexto_det = 'Todos';
	$valor_contexto_det = ' ';
	$valor_contexto_det2 = ' ';
} else {
	$resultcontx = mysqli_query($link, "select pkyContext as cod_contexto, fldName as contexto, codCostCenter as cod_cc 
	from srvContext
	where 
	pkyContext = '$cod_contexto_det';");
		while ($row = mysqli_fetch_array($resultcontx)) {   
			$nom_contexto_det = $row['cod_contexto'].' - '.$row['contexto'];
		}
		mysqli_free_result($resultcontx);
	$valor_contexto_det = ' a.sysContext = '.$cod_contexto_det.' and ';	
	$valor_contexto_det2 = ' c.sysContext = '.$cod_contexto_det.' and ';	
}


if($otra_moneda_det != 0) {//if otra moneda 3

$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto, 
t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto_om as monto_om, t.cod_cxp as cod_cxp, t.empresa_empleado as empresa_empleado, t.cod_doc as cod_doc, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
from (
select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc_det' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.codDocumentType as cod_doc, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
txsDocumentType e, invItem f, invItemCategory g
where
$valor_con_gasto_det 
$valor_empleado_det 
$valor_empresa_det 
$valor_ver_cxp_det 
$valor_contexto_det2 
$valor_cuenta_gasto_det 
b.pkyExpensesConcept <> 20 and 
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
d.codExchangeRate = '$ultimo_tc_det' and  
a.codDocumentType = e.pkyDocumentType and 
a.codItem = f.pkyItem and 
f.codFamily = g.pkyItemCategory and 
c.fldDate >= '$fechaini_det' and 
c.fldDate <= '$fechafin_det' and  
d.codBaseCurrency = '$otra_moneda_det' and 
d.codTargetCurrency = c.codCurrency and 
c.sysEnabled = 1 and c.sysAuthorized = 1

union all

select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc_det' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.codDocumentType as cod_doc, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa 
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e, invItem f, invItemCategory g
where
$valor_con_gasto_det 
$valor_empleado_det 
$valor_empresa_det 
$valor_ver_cxp_det 
$valor_contexto_det2 
$valor_cuenta_gasto_det 
b.pkyExpensesConcept <> 20 and 
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
a.codDocumentType = e.pkyDocumentType and 
a.codItem = f.pkyItem and 
f.codFamily = g.pkyItemCategory and 
c.fldDate >= '$fechaini_det' and 
c.fldDate <= '$fechafin_det' and 
c.codCurrency = '$otra_moneda_det' and
c.sysEnabled = 1 and c.sysAuthorized = 1 


union all


select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc_det' as cod_tc, d.fldRate as tc, a.fldAmount * d.fldRate as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.codDocumentType as cod_doc, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa  
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actExchangeRateDetail d, 
txsDocumentType e
where
$valor_con_gasto_det 
$valor_empleado_det 
$valor_empresa_det 
$valor_ver_cxp_det 
$valor_contexto_det2 
$valor_cuenta_gasto_det 
b.pkyExpensesConcept <> 20 and 
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
d.codExchangeRate = '$ultimo_tc_det' and 
a.codDocumentType = e.pkyDocumentType and 
a.codItem is NULL and 
c.fldDate >= '$fechaini_det' and 
c.fldDate <= '$fechafin_det' and  
d.codBaseCurrency = '$otra_moneda_det' and 
d.codTargetCurrency = c.codCurrency and 
c.sysEnabled = 1 and c.sysAuthorized = 1

union all

select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, 
NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, a.fldAmount as monto, c.codCurrency as cod_moneda, '$ultimo_tc_det' as cod_tc, 1 as tc, a.fldAmount * 1 as monto_om, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.codDocumentType as cod_doc, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa  
from
payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, txsDocumentType e
where
$valor_con_gasto_det 
$valor_empleado_det 
$valor_empresa_det 
$valor_ver_cxp_det 
$valor_contexto_det2 
$valor_cuenta_gasto_det 
b.pkyExpensesConcept <> 20 and 
a.codExpensesConcept = b.pkyExpensesConcept and 
a.codAccountsPayable = c.pkyAccountsPayable and 
a.codDocumentType = e.pkyDocumentType and 
a.codItem is NULL and 
c.fldDate >= '$fechaini_det' and 
c.fldDate <= '$fechafin_det' and 
c.codCurrency = '$otra_moneda_det' and
c.sysEnabled = 1 and c.sysAuthorized = 1

UNION all

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, '$ultimo_tc_det' as cod_tc, d.fldRate as tc, c.fldTotaCost * d.fldRate as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_doc, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actExchangeRateDetail d, invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso_det 
	$valor_contexto_det 
	d.codExchangeRate = '$ultimo_tc_det' and 
	a.fldDate >= '$fechaini_det' and 
	a.fldDate <= '$fechafin_det' and  
	d.codBaseCurrency = '$otra_moneda_det' and 
	d.codTargetCurrency = a.codCurrency and  
	a.sysEnabled = 1 and a.sysAuthorized = 1

union all 

select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.fldTotaCost as monto, a.codCurrency as cod_moneda, '$ultimo_tc_det' as cod_tc, 1 as tc, c.fldTotaCost * 1 as monto_om, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_doc, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c,  
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso_det 
	$valor_contexto_det 
	a.fldDate >= '$fechaini_det' and 
	a.fldDate <= '$fechafin_det' and  
	a.codCurrency  = '$otra_moneda_det' and 
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
order by t.concepto_gasto;");

} else {

//los dos querys son necesarios para que calcule el total por moneda original
$result = mysqli_query($link, "select t.cod_gasto as cod_gasto, t.concepto_gasto as concepto_gasto,	t.documento as documento, t.item as item, t.familia as familia, t.subfamilia as subfamilia, t.grupo as grupo, t.subgrupo as subgrupo, t.monto as monto_om, t.cod_cxp as cod_cxp, t.moneda as moneda, t.cod_moneda as cod_moneda, t.empresa_empleado as empresa_empleado, t.cod_doc as cod_doc, t.cod_det as cod_det, t.papa as papa, t.cod_papa as cod_papa
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.codDocumentType as cod_doc, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa  
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto_det 
	$valor_empleado_det 
	$valor_empresa_det 
	$valor_ver_cxp_det 
	$valor_contexto_det2 
	$valor_cuenta_gasto_det 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini_det' and 
	c.fldDate <= '$fechafin_det' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp, case when c.codSupplier is not NULL then (select a1.fldName as emp from agnCompany a1 where a1.pkyCompany = c.codSupplier) else concat((select case when b1.fldMiddleName is NULL then concat(b1.fldLastName,', ',b1.fldFirstName) else concat(b1.fldLastName,' ',b1.fldMiddleName,', ',b1.fldFirstName) end as emp from agnPerson b1 where b1.pkyPerson = c.codEmployee),' (Empleado)') end as empresa_empleado, a.codDocumentType as cod_doc, a.pkyAccountsPayableDetail as cod_det, c.fldParentComponent as papa, c.cosComponentKey as cod_papa  
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e
	where
	$valor_con_gasto_det 
	$valor_empleado_det 
	$valor_empresa_det 
	$valor_ver_cxp_det 
	$valor_contexto_det2 
	$valor_cuenta_gasto_det 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini_det' and 
	c.fldDate <= '$fechafin_det' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	UNION all
	
	select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp, 'MOV. DE MERCADERIA' as empresa_empleado, 0 as cod_doc, 0 as cod_det, a.fldParentComponent as papa, a.cosComponentKey as cod_papa 
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d,  
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso_det 
	$valor_contexto_det 
	a.fldDate >= '$fechaini_det' and 
	a.fldDate <= '$fechafin_det' and  
	a.codCurrency = d.pkyCurrency and  
	a.sysEnabled = 1 and a.sysAuthorized = 1) t 
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");	

$result_filas = mysqli_query($link, "select count(*) as no_filas, t.moneda as con_gasto
	from (
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e, invItem f, invItemCategory g
	where
	$valor_con_gasto_det 
	$valor_empleado_det 
	$valor_empresa_det 
	$valor_ver_cxp_det 
	$valor_contexto_det2 
	$valor_cuenta_gasto_det 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem = f.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	c.fldDate >= '$fechaini_det' and 
	c.fldDate <= '$fechafin_det' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	union all
		
	select a.codExpensesConcept as cod_gasto, b.fldName as concepto_gasto, e.fldName as documento, NULL as item, NULL as familia, NULL as subfamilia, NULL as grupo, NULL as subgrupo, c.codCurrency as cod_moneda, d.fldName as moneda, a.fldAmount as monto, c.pkyAccountsPayable as cod_cxp
	from
	payAccountsPayableDetail a, payExpensesConcept b, payAccountsPayable c, actCurrency d, 
	txsDocumentType e
	where
	$valor_con_gasto_det 
	$valor_empleado_det 
	$valor_empresa_det 
	$valor_ver_cxp_det 
	$valor_contexto_det2 
	$valor_cuenta_gasto_det 
	b.pkyExpensesConcept <> 20 and 
	a.codExpensesConcept = b.pkyExpensesConcept and 
	a.codAccountsPayable = c.pkyAccountsPayable and 
	a.codDocumentType = e.pkyDocumentType and 
	a.codItem is NULL and 
	c.fldDate >= '$fechaini_det' and 
	c.fldDate <= '$fechafin_det' and  
	c.codCurrency = d.pkyCurrency and  
	c.sysEnabled = 1 and c.sysAuthorized = 1
	
	UNION all
	
	select a.pkyItemMovement as cod_gasto, concat(b.fldName, ' (', h.fldName, ') - ', round(c.fldQuantity,2)) as concepto_gasto, 'Egreso' as documento, f.fldName as item, g.fldName as familia, f.codSubFamily as subfamilia, f.codGroup as grupo, f.codSubGroup as subgrupo, a.codCurrency as cod_moneda, d.fldName as moneda, c.fldTotaCost as monto, 0 as cod_cxp
	from
	invItemMovement a, invItemMovementTypeConcept b, invItemMovementDetail c, actCurrency d,  
	 invItem f, invItemCategory g, invItem h
	where
	a.codTypeConcept = b.pkyItemMovementTypeConcept and 
	a.codType = 2 and 
	a.pkyItemMovement = c.codItemMovement and 
	a.codItem = f.pkyItem and 
	c.codItem = h.pkyItem and 
	f.codFamily = g.pkyItemCategory and 
	$valor_con_egreso_det 
	$valor_contexto_det 
	a.fldDate >= '$fechaini_det' and 
	a.fldDate <= '$fechafin_det' and  
	a.codCurrency = d.pkyCurrency and  
	a.sysEnabled = 1 and a.sysAuthorized = 1) t  
	group by t.moneda
	order by t.moneda, t.concepto_gasto, t.cod_cxp;");

$mostrar_filas = array();
$i = 1;
while ($row = mysqli_fetch_array($result_filas)) {
	$mostrar_filas[$i][1] = $row['con_gasto'];
	$mostrar_filas[$i][2] = $row['no_filas'];
	//echo $mostrar_filas[$i][1] . " - " . $mostrar_filas[$i][2];
	$i++;
}
mysqli_free_result($result_filas);
$fin_ver = $i - 1;
}//fin if otra moneda 3

$mostrar_resultado_det = array();
$i = 1;
while ($row = mysqli_fetch_array($result)) { //while
	$mostrar_resultado_det[$i][1] = $row['cod_gasto'];
	$mostrar_resultado_det[$i][2] = $row['concepto_gasto'];
	$mostrar_resultado_det[$i][3] = $row['documento'];
	$mostrar_resultado_det[$i][4] = $row['item'];
	$mostrar_resultado_det[$i][5] = $row['familia'];
	$mostrar_resultado_det[$i][6] = $row['subfamilia'];
	$mostrar_resultado_det[$i][7] = $row['grupo'];
	$mostrar_resultado_det[$i][8] = $row['subgrupo'];
	$mostrar_resultado_det[$i][9] = $row['monto_om'];
	$mostrar_resultado_det[$i][10] = $row['cod_cxp'];
	$mostrar_resultado_det[$i][13] = $row['empresa_empleado'];
	if($otra_moneda_det == 0) { //if otra_moneda 2
	$mostrar_resultado_det[$i][11] = $row['moneda'];//solo se usa cuando la moneda es 0
	$mostrar_resultado_det[$i][12] = $row['cod_moneda'];//solo se usa cuando la moneda es 0
	}// fin if otra_moneda 2
	$mostrar_resultado_det[$i][15] = $row['cod_doc'];//cod doc
	$mostrar_resultado_det[$i][16] = $row['cod_det'];//cod detalle
	//para enlazar al que genero el asiento
	$papa_componente = $row['papa'];
	$papa_codigo = $row['cod_papa'];
	$usuario = $usuario_det;
	include "../../generadopor.php";
	$mostrar_resultado_det[$i][14] = $papa_enlace;//generado por
	$i++;
} //fin while
mysqli_free_result($result);

$fin_res_det = $i - 1;

//verificar si la retención se separa en impuestos 1=si, 0=no
if($retencion_separada_det == 1){
	$nom_retencion_det = 'Si';
} else {
	$nom_retencion_det = 'No';
}

?>
<table border="0" align="left" width="100%" cellpadding="5">
<tr><td><?php echo $mostrar_logo_reportes; ?>
</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_det; ?></b>
</td></tr></table>
<p><br /><center><font color="#000099" size="+1"><b>Detalle Gastos</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> 
        <?php if($tipo_con_det == 0) { ?> 
        	<TD width="25%"><b>Concepto de Gasto: </b><?php echo $nom_con_gasto_det; ?></TD>
        <?php } else { ?>
        	<TD width="25%"><b>Concepto de Gasto (Egreso): </b><?php echo $nom_con_egreso_det; ?></TD>
        <?php } ?>
        <TD width="25%"><b>Empresa/Empleado: </b><?php echo $nom_empemp_det; ?></TD>
        <TD width="25%"><b>De Fecha: </b><?php echo $fechaini_det; ?> <b>a Fecha: </b><?php echo $fechafin_det; ?></TD>
        <TD width="25%"><b>Valorado en: </b><?php echo $nom_otra_moneda_det; ?></TD>
        </TR>   
        <TR>
		<?php if($tipo_con_det == 0) { ?> 
        	<TD colspan="2"><b>Cuenta Contable del Concepto de Gasto: </b><?php echo $nom_cuenta_gasto_det; ?></TD>
			<TD><b>Retención Separada en Impuesto: </b><?php echo $nom_retencion_det; ?></TD>
			<TD><b>Contexto: </b><?php echo $nom_contexto_det; ?></TD>
		<?php } else { ?>
			<TD colspan="4"><b>Contexto: </b><?php echo $nom_contexto_det; ?></TD>
        <?php } ?>
        </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_det; ?>&user=<?php echo $usuario; ?>" title="Tipo de Cambio"><?php echo $ultimo_tc_det; ?></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></tr>", 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%">
 	<thead>
    <TR> 
 	<TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Concepto de Gasto</b></TD>
    <TD align="center"><b>Empresa/Empleado</b></TD>
    <TD align="center"><b>Tipo de Doc.</b></TD>
    <TD align="center"><b>Item</b></TD>
    <TD align="center"><b>Familia</b></TD>
    <TD align="center"><b>Subfamilia</b></TD>
    <TD align="center"><b>Grupo</b></TD>
    <TD align="center"><b>Subgrupo</b></TD>
    <TD align="center"><b>Gen. Por</b></TD>
	<?php if($otra_moneda_det == 0) { ?>
	<TD align="center"><b>Mon.</b></TD>
    <?php } ?>
    <TD align="center"><b>Monto</b></TD>
    <TD align="center"><b>Exento</b></TD>
    <TD align="center"><b>Impuestos</b></TD>
	<TD align="center"><b>Monto sin Imp.</b></TD>
    <TD align="center"><b>Pagos</b></TD>
    <TD align="center"><b>Saldo</b></TD>
    </TR>
    </thead>
    <tbody class="marcafila">

<?php
$color = 2;
$suma_gastos = 0;
$suma_pagos_total = 0;
$suma_saldo = 0;
$suma_exento = 0;
$suma_impuestos = 0;
$suma_gastos_sin_impuestos = 0;
$j = 1;
$i = 1;

if($otra_moneda_det == 0) { //if filas (muestra filas por moneda para sumar solo en moneda 0)

	while($i <= $fin_res_det) { //while principal		
		$suma_gastos_fila = 0;
		$suma_pagos_fila = 0;
		$suma_saldo_fila = 0;
		$suma_exento_fila = 0;
		$suma_impuestos_fila = 0;
		$suma_gastos_sin_impuestos_fila = 0;

		for($k = 1; $k <= $mostrar_filas[$j][2]; $k++) { //for mostrar filas	
			$cod_cxp = $mostrar_resultado_det[$i][10];
			$cod_mov = $mostrar_resultado_det[$i][1];
			$cod_doc = $mostrar_resultado_det[$i][15];
			$cod_det = $mostrar_resultado_det[$i][16];
			if($cod_cxp != 0) { //if cod_cxp
				
				if($color % 2 == 0) { //if color
					printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModulePayments/VoucherAccountsPayable/index.php?pkyAccountsPayable=$cod_cxp&user=$usuario_det' title='Nota de Cuenta por Pagar'>%s</a></td>", $mostrar_resultado_det[$i][10]);//cod
				} else {
					printf("<tr><td align='center'><a href='../../ModulePayments/VoucherAccountsPayable/index.php?pkyAccountsPayable=$cod_cxp&user=$usuario_det' title='Nota de Cuenta por Pagar'>%s</a></td>", $mostrar_resultado_det[$i][10]);//cod
				}//cierra if color
			
			} else { //else if cod_cxp
			
				if($color % 2 == 0) { //if color2
					printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModuleInventory/VoucherItemMovement/index.php?pkyItemMovement=$cod_mov&showBatch=0&printAccountEntry=0&user=$usuario_det' title='Movimiento de Mercader&iacute;a'>%s</a></td>", $mostrar_resultado_det[$i][1]);//cod mov
				} else {
					printf("<tr><td align='center'><a href='../../ModuleInventory/VoucherItemMovement/index.php?pkyItemMovement=$cod_mov&showBatch=0&printAccountEntry=0&user=$usuario_det' title='Movimiento de Mercader&iacute;a'>%s</a></td>", $mostrar_resultado_det[$i][1]);//cod mov
				}//cierra if color2
				
			} //fin if cod_cxp
			
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][2]);//concepto gasto
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][13]);//empresa empleado
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][3]);//documento
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][4]);//item
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][5]);//familia
			
			$moneda_ = $mostrar_resultado_det[$i][11]; //moneda 
			$cod_moneda_ = $mostrar_resultado_det[$i][12]; //cod moneda
			$monto_om_ = $mostrar_resultado_det[$i][9]; //monto
			$subfamilia_ = $mostrar_resultado_det[$i][6]; //subfamilia
			$grupo_ = $mostrar_resultado_det[$i][7]; //grupo
			$subgrupo_ = $mostrar_resultado_det[$i][8]; //subgrupo
			
			if ($subfamilia_ <> NULL) { //if subfamilia
				$resultfam = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
									from invItemCategory
									where
									pkyItemCategory = '$subfamilia_' and 
									sysEnabled = 1 and sysAuthorized = 1;");
				while ($row = mysqli_fetch_array($resultfam)) {
					$nom_subfamilia_ = $row['nombre'];
				}
				mysqli_free_result($resultfam);
				printf("<td align='left'>%s</td>", $nom_subfamilia_);//subfamilia
			} else {
				printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][6]);//subfamilia
			} //fin if subfamilia
			
			if ($grupo_ <> NULL) { //if grupo
				$resultgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
									from invItemCategory
									where
									pkyItemCategory = '$grupo_' and 
									sysEnabled = 1 and sysAuthorized = 1;");
				while ($row = mysqli_fetch_array($resultgru)) {
					$nom_grupo_ = $row['nombre'];
				}
				mysqli_free_result($resultgru);
				printf("<td align='left'>%s</td>", $nom_grupo_);//grupo
			} else {
				printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][7]);//grupo
			} //fin if grupo
			
			if ($subgrupo_ <> NULL) { //if subgrupo
				$resultsgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
									from invItemCategory
									where
									pkyItemCategory = '$subgrupo_' and 
									sysEnabled = 1 and sysAuthorized = 1;");
				while ($row = mysqli_fetch_array($resultsgru)) {
					$nom_subgrupo_ = $row['nombre'];
				}
				mysqli_free_result($resultsgru);
				printf("<td align='left'>%s</td>", $nom_subgrupo_);//subgrupo
			} else {
				printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][8]);//subgrupo
			} //fin if subgrupo
			
			printf("<td align='center'>%s</td>", $mostrar_resultado_det[$i][14]);//gen por
			printf("<td align='left'>%s</td>", $moneda_);//moneda
			printf("<td align='right'>%s</td>", number_format($monto_om_,$n_decimales));//monto
			
			$suma_pagos = 0;
			
			//suma Pagos (son con el tc de la fecha en la que se hizo el pago, se usa como moneda cod_moneda_)
			$result5 = mysqli_query($link, "select final.cod_cxp as cod_cxp, sum(final.total) as suma_pagos
					from(
					select otra.cod_cxp as cod_cxp, otra.suma_pagos as suma_pagos,
					otra.cod_moneda_pago as cod_moneda_pago, otra.tc as tc, 
					otra.cod_moneda_cxp as cod_moneda_cxp, 
					otra.otra_moneda as otra_moneda, otra.tasa as tasa, 
					otra.suma_pagos * otra.tasa as total
					from
					(select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
					t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
					t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
					r.fldRate as tasa
					from
					(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$cod_moneda_' as otra_moneda
					from payPayment a, payPaymentDetail b, payAccountsPayable c
					where
					$valor_ver_cxp_det
					$valor_contexto_det 
					a.pkyPayment = b.codPayment and
					a.fldDate >= '$fechaini_det' and
					a.fldDate <= '$fechafin_det' and 
					b.codAccountsPayable is not null and
					b.codAccountsPayable = c.pkyAccountsPayable and 
					c.pkyAccountsPayable = '$cod_cxp' and 
					a.sysEnabled = 1 and a.sysAuthorized = 1 
					group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t,
					actExchangeRateDetail r
					where
					t.tc = r.codExchangeRate and
					t.cod_moneda_cxp = r.codTargetCurrency and
					t.otra_moneda = r.codBaseCurrency
					union all
					select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
					t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
					t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
					1 as tasa
					from
					(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$cod_moneda_' as otra_moneda
					from payPayment a, payPaymentDetail b, payAccountsPayable c
					where
					$valor_ver_cxp_det
					$valor_contexto_det 
					a.pkyPayment = b.codPayment and
					a.fldDate >= '$fechaini_det' and
					a.fldDate <= '$fechafin_det' and 
					b.codAccountsPayable is not null and
					b.codAccountsPayable = c.pkyAccountsPayable and 
					c.pkyAccountsPayable = '$cod_cxp' and 
					a.sysEnabled = 1 and a.sysAuthorized = 1 
					group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t
					where
					t.cod_moneda_cxp = t.otra_moneda) otra) final
					group by final.cod_cxp
					order by final.cod_cxp;");	
			
			while ($row = mysqli_fetch_array($result5)) {
				$suma_pagos = $row['suma_pagos'];
			}
			mysqli_free_result($result5);
			//CxP se usa como moneda cod_moneda_
			$result3 = mysqli_query($link, "select final.cod_cxp as cod_cxp, final.total as cxp
				from(
				select otra.cod_cxp as cod_cxp, otra.cxp as cxp, otra.cod_moneda as cod_moneda,
				otra.tc as tc, otra.otra_moneda as otra_moneda, otra.tasa as tasa, otra.cxp * otra.tasa as total
				from
				(select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
				t.tc as tc, t.otra_moneda as otra_moneda, r.fldRate as tasa
				from
				(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$cod_moneda_' as otra_moneda
				from payAccountsPayable a
				where
				$valor_ver_cxp_det2
				$valor_contexto_det 
				a.pkyAccountsPayable = '$cod_cxp' and
				a.sysEnabled = 1 and a.sysAuthorized = 1) t,
				actExchangeRateDetail r
				where
				t.tc = r.codExchangeRate and
				t.cod_moneda = r.codTargetCurrency and
				t.otra_moneda = r.codBaseCurrency
				union all
				select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
				t.tc as tc, t.otra_moneda as otra_moneda, 1 as tasa
				from
				(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$cod_moneda_' as otra_moneda
				from payAccountsPayable a
				where
				$valor_ver_cxp_det2
				$valor_contexto_det 
				a.pkyAccountsPayable = '$cod_cxp' and
				a.sysEnabled = 1 and a.sysAuthorized = 1) t
				where
				t.cod_moneda = t.otra_moneda) otra) final;");
			while ($row = mysqli_fetch_array($result3)) {
				$total_cxp = $row['cxp'];
			}
			mysqli_free_result($result3);
				
			if($suma_pagos == NULL) { $suma_pagos = 0; }
			
			if($total_cxp != 0) {
				$coef = $suma_pagos / $total_cxp;
			} else {
				$coef = $suma_pagos;
			}
			
			if($cod_cxp != 0) {
				$saldo_ = $monto_om_ - ($monto_om_ * $coef);
			} else {
				$saldo_ = 0;
			}

			$porcentaje_ret = 0;
			$exento_ = 0;
			//compara si tiene cxp para el impuesto
			if($cod_cxp != 0) {
				//si es 1 factura, 2 posterior, 5 boleto y 7 factura grupo
				if($cod_doc == 1 || $cod_doc == 2 || $cod_doc == 5 || $cod_doc == 7) {
					//si 1 exento
					if($cod_doc == 1 || $cod_doc == 7) {
						$result77 = mysqli_query($link, "select fldExempt as exento 
						from txsPurchasesLedger 
						where 
						pkyPurchasesLedger in (select fldInvoice from payAccountsPayableDetail where pkyAccountsPayableDetail = '$cod_det');");
						while ($row = mysqli_fetch_array($result77)) {
							$exento_ = $row['exento'];
						}
						mysqli_free_result($result77);
					}
					$impuestos_ = ($monto_om_ - $exento_) * $monto_iva / 100;//$monto_iva viene de general
				} else {
					//si es 4 poliza
					if($cod_doc == 4) {
						$impuestos_ = $monto_om_;
					} else {
						//si 3 retencion
						if($cod_doc == 3) {
							$result7 = mysqli_query($link, "select sum(fldPercent) as porcentaje
								from txsRetentionDetail
								where
								codAccountsPayableDetail = '$cod_det'
								group by codAccountsPayableDetail;");
							while ($row = mysqli_fetch_array($result7)) {
								$porcentaje_ret = $row['porcentaje'];
							}
							mysqli_free_result($result7);
							$impuestos_ = $monto_om_ * $porcentaje_ret / 100;
						} else {
							$impuestos_ = 0;
						}

						//verificar si la retención se separa en impuestos 1=si, 0=no
						if($retencion_separada_det == 0){
							$impuestos_ = 0;
						}
					}
				}
			}
			
			$pagos_ = $monto_om_ - $saldo_;
			
			printf("<td align='right'>%s</td>", number_format($exento_, $n_decimales));//exento
			printf("<td align='right'>%s</td>", number_format($impuestos_, $n_decimales));//impuestos
			printf("<td align='right'>%s</td>", number_format($monto_om_ - $impuestos_, $n_decimales));//monto sin impuestos
			printf("<td align='right'>%s</td>", number_format($pagos_, $n_decimales));//pagos
			printf("<td align='right'>%s</td></tr>", number_format($saldo_, $n_decimales));//saldo
			
			$suma_saldo_fila = $suma_saldo_fila + $saldo_;
			$suma_pagos_fila = $suma_pagos_fila + $pagos_;
			$suma_gastos_fila = $suma_gastos_fila + $monto_om_;
			$suma_exento_fila = $suma_exento_fila + $exento_;
			$suma_impuestos_fila = $suma_impuestos_fila + $impuestos_;
			$suma_gastos_sin_impuestos_fila = $suma_gastos_sin_impuestos_fila + $monto_om_ - $impuestos_;

			$suma_saldo = $suma_saldo + $saldo_;
			$suma_pagos_total = $suma_pagos_total + $pagos_;
			$suma_gastos = $suma_gastos + $monto_om_;
			$suma_exento = $suma_exento + $exento_;
			$suma_impuestos = $suma_impuestos + $impuestos_;
			$suma_gastos_sin_impuestos = $suma_gastos_sin_impuestos + $monto_om_ - $impuestos_;
			$color++;
			$i++;
		}// fin for mostrar filas
	
		printf("<tr BGCOLOR='#ffcccc'><td colspan='11'><b>Total %s (%s)</b></td>", $mostrar_filas[$j][1], $mostrar_filas[$j][2]);//titulo total fila 
		printf("<td align='right'><b>%s</b></td>", number_format($suma_gastos_fila,$n_decimales));//total gastos fila
		printf("<td align='right'><b>%s</b></td>", number_format($suma_exento_fila,$n_decimales));//total exento fila
		printf("<td align='right'><b>%s</b></td>", number_format($suma_impuestos_fila,$n_decimales));//total impuestos fila
		printf("<td align='right'><b>%s</b></td>", number_format($suma_gastos_sin_impuestos_fila,$n_decimales));//total gastos sin impuestos fila
		printf("<td align='right'><b>%s</b></td>", number_format($suma_pagos_fila,$n_decimales));//total pagos fila
		printf("<td align='right'><b>%s</b></td></tr>", number_format($suma_saldo_fila,$n_decimales));//total saldo fila
		$j++;
		
	}// fin while principal

} else { 

	for($i = 1; $i <= $fin_res_det; $i++) { //for principal		
		
		$cod_cxp = $mostrar_resultado_det[$i][10];
		$cod_mov = $mostrar_resultado_det[$i][1];
		$cod_doc = $mostrar_resultado_det[$i][15];
		$cod_det = $mostrar_resultado_det[$i][16];
		if($cod_cxp != 0) { //if cod_cxp
			
			if($color % 2 == 0) { //if color
				printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModulePayments/VoucherAccountsPayable/index.php?pkyAccountsPayable=$cod_cxp&user=$usuario_det' title='Nota de Cuenta por Pagar'>%s</a></td>", $mostrar_resultado_det[$i][10]);//cod
			} else {
				printf("<tr><td align='center'><a href='../../ModulePayments/VoucherAccountsPayable/index.php?pkyAccountsPayable=$cod_cxp&user=$usuario_det' title='Nota de Cuenta por Pagar'>%s</a></td>", $mostrar_resultado_det[$i][10]);//cod
			}//cierra if color
		
		} else { //else if cod_cxp
		
			if($color % 2 == 0) { //if color2
				printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModuleInventory/VoucherItemMovement/index.php?pkyItemMovement=$cod_mov&showBatch=0&printAccountEntry=0&user=$usuario_det' title='Movimiento de Mercader&iacute;a'>%s</a></td>", $mostrar_resultado_det[$i][1]);//cod mov
			} else {
				printf("<tr><td align='center'><a href='../../ModuleInventory/VoucherItemMovement/index.php?pkyItemMovement=$cod_mov&showBatch=0&printAccountEntry=0&user=$usuario_det' title='Movimiento de Mercader&iacute;a'>%s</a></td>", $mostrar_resultado_det[$i][1]);//cod mov
			}//cierra if color2
			
		} //fin if cod_cxp
		
		printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][2]);//concepto gasto
		printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][13]);//empresa empleado
		printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][3]);//documento
		printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][4]);//item
		printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][5]);//familia
		
		$moneda_ = $mostrar_resultado_det[$i][11]; //moneda 
		$cod_moneda_ = $mostrar_resultado_det[$i][12]; //cod moneda
		$monto_om_ = $mostrar_resultado_det[$i][9]; //monto
		$subfamilia_ = $mostrar_resultado_det[$i][6]; //subfamilia
		$grupo_ = $mostrar_resultado_det[$i][7]; //grupo
		$subgrupo_ = $mostrar_resultado_det[$i][8]; //subgrupo
		
		if ($subfamilia_ <> NULL) { //if subfamilia
			$resultfam = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
								from invItemCategory
								where
								pkyItemCategory = '$subfamilia_' and 
								sysEnabled = 1 and sysAuthorized = 1;");
			while ($row = mysqli_fetch_array($resultfam)) {
				$nom_subfamilia_ = $row['nombre'];
			}
			mysqli_free_result($resultfam);
			printf("<td align='left'>%s</td>", $nom_subfamilia_);//subfamilia
		} else {
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][6]);//subfamilia
		} //fin if subfamilia
		
		if ($grupo_ <> NULL) { //if grupo
			$resultgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
								from invItemCategory
								where
								pkyItemCategory = '$grupo_' and 
								sysEnabled = 1 and sysAuthorized = 1;");
			while ($row = mysqli_fetch_array($resultgru)) {
				$nom_grupo_ = $row['nombre'];
			}
			mysqli_free_result($resultgru);
			printf("<td align='left'>%s</td>", $nom_grupo_);//grupo
		} else {
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][7]);//grupo
		} //fin if grupo
		
		if ($subgrupo_ <> NULL) { //if subgrupo
			$resultsgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
								from invItemCategory
								where
								pkyItemCategory = '$subgrupo_' and 
								sysEnabled = 1 and sysAuthorized = 1;");
			while ($row = mysqli_fetch_array($resultsgru)) {
				$nom_subgrupo_ = $row['nombre'];
			}
			mysqli_free_result($resultsgru);
			printf("<td align='left'>%s</td>", $nom_subgrupo_);//subgrupo
		} else {
			printf("<td align='left'>%s</td>", $mostrar_resultado_det[$i][8]);//subgrupo
		} //fin if subgrupo
		
		printf("<td align='center'>%s</td>", $mostrar_resultado_det[$i][14]);//gen por
		printf("<td align='right'>%s</td>", number_format($monto_om_,$n_decimales));//monto
		
		$suma_pagos = 0;
		
		//suma Pagos (son con el tc de la fecha en la que se hizo el pago)
		$result5 = mysqli_query($link, "select final.cod_cxp as cod_cxp, sum(final.total) as suma_pagos
				from(
				select otra.cod_cxp as cod_cxp, otra.suma_pagos as suma_pagos,
				otra.cod_moneda_pago as cod_moneda_pago, otra.tc as tc, 
				otra.cod_moneda_cxp as cod_moneda_cxp, 
				otra.otra_moneda as otra_moneda, otra.tasa as tasa, 
				otra.suma_pagos * otra.tasa as total
				from
				(select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
				t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
				t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
				r.fldRate as tasa
				from
				(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$otra_moneda_det' as otra_moneda
				from payPayment a, payPaymentDetail b, payAccountsPayable c
				where
				$valor_ver_cxp_det
				$valor_contexto_det 
				a.pkyPayment = b.codPayment and
				a.fldDate >= '$fechaini_det' and
				a.fldDate <= '$fechafin_det' and 
				b.codAccountsPayable is not null and
				b.codAccountsPayable = c.pkyAccountsPayable and 
				c.pkyAccountsPayable = '$cod_cxp' and 
				a.sysEnabled = 1 and a.sysAuthorized = 1 
				group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t,
				actExchangeRateDetail r
				where
				t.tc = r.codExchangeRate and
				t.cod_moneda_cxp = r.codTargetCurrency and
				t.otra_moneda = r.codBaseCurrency
				union all
				select t.cod_cxp as cod_cxp, t.suma_pagos as suma_pagos, 
				t.cod_moneda_pago as cod_moneda_pago, t.tc as tc, 
				t.cod_moneda_cxp as cod_moneda_cxp, t.otra_moneda as otra_moneda, 
				1 as tasa
				from
				(select b.codAccountsPayable as cod_cxp, sum(b.fldTotal) as suma_pagos, a.codCurrency as cod_moneda_pago, a.codExchangeRate as tc, c.codCurrency as cod_moneda_cxp, '$otra_moneda_det' as otra_moneda
				from payPayment a, payPaymentDetail b, payAccountsPayable c
				where
				$valor_ver_cxp_det
				$valor_contexto_det 
				a.pkyPayment = b.codPayment and
				a.fldDate >= '$fechaini_det' and
				a.fldDate <= '$fechafin_det' and 
				b.codAccountsPayable is not null and
				b.codAccountsPayable = c.pkyAccountsPayable and 
				c.pkyAccountsPayable = '$cod_cxp' and 
				a.sysEnabled = 1 and a.sysAuthorized = 1 
				group by b.codAccountsPayable, a.codCurrency, c.codCurrency, a.codExchangeRate) t
				where
				t.cod_moneda_cxp = t.otra_moneda) otra) final
				group by final.cod_cxp
				order by final.cod_cxp;");	
				
		while ($row = mysqli_fetch_array($result5)) {
			$suma_pagos = $row['suma_pagos'];
		}
		mysqli_free_result($result5);
			
		//CxP 
		$result3 = mysqli_query($link, "select final.cod_cxp as cod_cxp, final.total as cxp
			from(
			select otra.cod_cxp as cod_cxp, otra.cxp as cxp, otra.cod_moneda as cod_moneda,
			otra.tc as tc, otra.otra_moneda as otra_moneda, otra.tasa as tasa, otra.cxp * otra.tasa as total
			from
			(select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
			t.tc as tc, t.otra_moneda as otra_moneda, r.fldRate as tasa
			from
			(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$otra_moneda_det' as otra_moneda
			from payAccountsPayable a
			where
			$valor_ver_cxp_det2
			$valor_contexto_det 
			a.pkyAccountsPayable = '$cod_cxp' and
			a.sysEnabled = 1 and a.sysAuthorized = 1) t,
			actExchangeRateDetail r
			where
			t.tc = r.codExchangeRate and
			t.cod_moneda = r.codTargetCurrency and
			t.otra_moneda = r.codBaseCurrency
			union all
			select t.cod_cxp as cod_cxp, t.cxp as cxp, t.cod_moneda as cod_moneda,
			t.tc as tc, t.otra_moneda as otra_moneda, 1 as tasa
			from
			(select a.pkyAccountsPayable as cod_cxp, a.fldTotal as cxp, a.codCurrency as cod_moneda, a.codExchangeRate as tc, '$otra_moneda_det' as otra_moneda
			from payAccountsPayable a
			where
			$valor_ver_cxp_det2
			$valor_contexto_det 
			a.pkyAccountsPayable = '$cod_cxp' and
			a.sysEnabled = 1 and a.sysAuthorized = 1) t
			where
			t.cod_moneda = t.otra_moneda) otra) final;");
		while ($row = mysqli_fetch_array($result3)) {
			$total_cxp = $row['cxp'];
		}
		mysqli_free_result($result3);
			
		if($suma_pagos == NULL) { $suma_pagos = 0; }
		
		if($total_cxp != 0) {
			$coef = $suma_pagos / $total_cxp;
		} else {
			$coef = $suma_pagos;
		}
		
		if($cod_cxp != 0) {
			$saldo_ = $monto_om_ - ($monto_om_ * $coef);
		} else {
			$saldo_ = 0;
		}

		$porcentaje_ret = 0;
		$exento_ = 0;
		//compara si tiene cxp para el impuesto
		if($cod_cxp != 0) {
			//si es 1 factura, 2 posterior, 5 boleto y 7 factura grupo
			if($cod_doc == 1 || $cod_doc == 2 || $cod_doc == 5 || $cod_doc == 7) {
				//si 1 exento
				if($cod_doc == 1 || $cod_doc == 7) {
					$result77 = mysqli_query($link, "select fldExempt as exento 
					from txsPurchasesLedger 
					where 
					pkyPurchasesLedger in (select fldInvoice from payAccountsPayableDetail where pkyAccountsPayableDetail = '$cod_det');");
					while ($row = mysqli_fetch_array($result77)) {
						$exento_ = $row['exento'];
					}
					mysqli_free_result($result77);
				}
				$impuestos_ = ($monto_om_ - $exento_) * $monto_iva / 100;//$monto_iva viene de general
			} else {
				//si es 4 poliza
				if($cod_doc == 4) {
					$impuestos_ = $monto_om_;
				} else {
					//si 3 retencion
					if($cod_doc == 3) {
						$result7 = mysqli_query($link, "select sum(fldPercent) as porcentaje
							from txsRetentionDetail
							where
							codAccountsPayableDetail = '$cod_det'
							group by codAccountsPayableDetail;");
						while ($row = mysqli_fetch_array($result7)) {
							$porcentaje_ret = $row['porcentaje'];
						}
						mysqli_free_result($result7);
						$impuestos_ = $monto_om_ * $porcentaje_ret / 100;
					} else {
						$impuestos_ = 0;
					}

					//verificar si la retención se separa en impuestos 1=si, 0=no
					if($retencion_separada_det == 0){
						$impuestos_ = 0;
					}
				}
			}
		}
		
		$pagos_ = $monto_om_ - $saldo_;
		
		printf("<td align='right'>%s</td>", number_format($exento_, $n_decimales));//exento
		printf("<td align='right'>%s</td>", number_format($impuestos_, $n_decimales));//impuestos
		printf("<td align='right'>%s</td>", number_format($monto_om_ - $impuestos_, $n_decimales));//monto sin impuestos
		printf("<td align='right'>%s</td>", number_format($pagos_, $n_decimales));//pagos
		printf("<td align='right'>%s</td></tr>", number_format($saldo_, $n_decimales));//saldo
		
		$suma_saldo = $suma_saldo + $saldo_;
		$suma_pagos_total = $suma_pagos_total + $pagos_;
		$suma_gastos = $suma_gastos + $monto_om_;
		$suma_exento = $suma_exento + $exento_;
		$suma_impuestos = $suma_impuestos + $impuestos_;
		$suma_gastos_sin_impuestos = $suma_gastos_sin_impuestos + $monto_om_ - $impuestos_;
		$color++;
		
	}// fin for principal

}//fin if filas (muestra filas por moneda para sumar solo en moneda 0)

?>
<?php if($otra_moneda_det != 0) { ?>
<tr BGCOLOR='#ffcccc'><td colspan="10"><b>Total</b></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_gastos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_exento,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_impuestos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_gastos_sin_impuestos,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_pagos_total,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_saldo,$n_decimales)); ?></td>
</tr>
<?php } ?>
</tbody>
</table>
<?php
} //fin if 3
?>
</body>
</html>
