<!DOCTYPE html>
<html lang="es-ES">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Pantalla Reporte Comisiones</title>
<link href="../../maincss.css" rel="stylesheet" type="text/css" />
<link href="../../printcss.css" rel="stylesheet" type="text/css" media="print" />

</head>
<?php
/*
Muestra las comisiones en caso de venta toma en cuenta los montos directamente de las ventas, en caso de las cobranzas toma en cuenta solo los montos cobrados de las ventas
*/
include "../../conn.php";
include "../../general.php";
include "../../token.php";

$rep=$_GET["rep"];
$tipo=$_GET["tipo"];
//$usuario=$_POST["usuario"];
$comisiones=$_POST["comisiones"];
$tipo_nota=$_POST["tiponota"];
$cliente=$_POST["cliente"];
$canal_venta=$_POST["canal_venta"];
$vendedor=$_POST["vendedor"];
$almacen=$_POST["almacen"];
$local=$_POST["local"];
$ciudad=$_POST["ciudad"];
$mostrar=$_POST["mostrar"];
$itemini=$_POST["itemini"];
$itemfin=$_POST["itemfin"];
$familia=$_POST["familia"];
$subfamilia=$_POST["subfamilia"];
$grupo=$_POST["grupo"];
$subgrupo=$_POST["subgrupo"];
$fechaini=$_POST["fechaini"];
$fechafin=$_POST["fechafin"];
$orden=$_POST["orden"];
$otra_moneda=$_POST["otra_moneda"];
$cod_contexto=$_POST["contexto"];
//$combinar=$_POST["combinar"];

//$usuario_det=$_GET["usuario_det"];
$comisiones_det=$_GET["comisiones_det"];
$tipo_nota_det=$_GET["tipo_nota_det"];
$cliente_det=$_GET["cliente_det"];
$canal_venta_det=$_GET["canal_venta_det"];
$vendedor_det=$_GET["vendedor_det"];
$almacen_det=$_GET["almacen_det"];
$local_det=$_GET["local_det"];
$ciudad_det=$_GET["ciudad_det"];
$mostrar_det=$_GET["mostrar_det"];
$itemini_det=$_GET["itemini_det"];
$itemfin_det=$_GET["itemfin_det"];
$familia_det=$_GET["familia_det"];
$subfamilia_det=$_GET["subfamilia_det"];
$grupo_det=$_GET["grupo_det"];
$subgrupo_det=$_GET["subgrupo_det"];
$fechaini_det=$_GET["fechaini_det"];
$fechafin_det=$_GET["fechafin_det"];
$orden_det=$_GET["orden_det"];
$otra_moneda_det=$_GET["otra_moneda_det"];
$cod_contexto_det=$_GET["contexto_det"];

//$n_decimales = 6;

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

?>
<body>

	<?php
	
//Inicio reporte comisiones


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

//nombre 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);


if($comisiones == 0) {
	$nom_comisiones = 'Ventas';
} else {
	$nom_comisiones = 'Cobranzas';
}

//$valor_agrupar = 'group by fin.cod_vendedor';
//$valor_ordenar = 'order by fin.vendedor';

//si mostrar es 0 no tomar en cuenta las familias y si es 1 no tomar los items
if($mostrar == 0) {
	$familia = 0;
	$subfamilia = 0;
	$grupo = 0;
	$subgrupo = 0;
} else {
	$itemini = 0;
	$itemfin = 0;
}

if ($tipo_nota == 0) { //if tipo nota
	$nom_tipo_nota = 'Todos';
	$valor_tipo_nota = ' ';
  } else { 
  	$valor_tipo_nota = ' a.codType = '.$tipo_nota.' and ';
	//nombre de tipo de nota
$resulttipo = mysqli_query($link, "select pkySalesNoteType as codigo, fldName as tipo
from 
slsSalesNoteType
where
pkySalesNoteType = '$tipo_nota' and 
sysEnabled = 1 and sysAuthorized = 1
order by tipo;");
	while ($row = mysqli_fetch_array($resulttipo)) {
	$nom_tipo_nota = $row['tipo'];
	}
	mysqli_free_result($resulttipo);
  } //fin if tipo nota

 if ($vendedor == 0) { //if vendedor
    $valor_vendedor = ' ';
	$nom_vendedor = 'Todos';
  } else { 
  	if($comisiones == 0) {
    $valor_vendedor = ' a.codSeller = '.$vendedor.' and ';
	} else {
	//en caso de que sea cobrador (comision por cobro)	
	$valor_vendedor = ' p.codCollector = '.$vendedor.' and ';	
	}
	//nombre de vendedor
	$resultven = mysqli_query($link, "select pkyPerson as codigo, case when fldMiddleName is NULL then concat(fldLastName,', ',fldFirstName) else concat(fldLastName,' ',fldMiddleName,', ',fldFirstName) end as vendedor
from agnPerson
where
fldIsEmployee = 1 and
fldCanSell = 1 and 
pkyPerson = '$vendedor' and
sysEnabled = 1 and sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($resultven)) {
	$nom_vendedor = $row['codigo'].' - '.$row['vendedor'];
	}
	mysqli_free_result($resultven);
  } //fin if vendedor

if ($cliente == 0) { //if cliente
    $valor_cliente = ' ';
	$nom_cliente = 'Todos';
  } else { 
    $valor_cliente = ' a.codClient = '.$cliente.' and ';
	//nombre de cliente
	$resultcli = mysqli_query($link, "select pkyCompany as codigo, fldName as cliente
from agnCompany
where
fldIsClient = 1 and
pkyCompany = '$cliente' and 
sysEnabled = 1 and sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($resultcli)) {
	$nom_cliente = $row['codigo'].' - '.$row['cliente'];
	}
	mysqli_free_result($resultcli);
  } //fin if cliente

if($canal_venta == 0) { // if canal
	$nom_canal_venta = 'Todos';
	$valor_canal_venta = ' ';
} else { 
	$resultcan = mysqli_query($link, "select pkyChannel as codigo, fldName as canal
						from agnChannel
						where
						pkyChannel = '$canal_venta';");
	while ($row = mysqli_fetch_array($resultcan)) {   
	$nom_canal_venta = $row['canal'];
	}
	mysqli_free_result($resultcan);
	$valor_canal_venta = ' c.codChannel = '.$canal_venta.' and ';
} // fin if canal

if ($almacen == 0) { //if almacen
    $valor_almacen = ' ';
	$nom_almacen = 'Todos';
  } else { 
    $valor_almacen = ' a.codWarehouse = '.$almacen.' and ';
	//nombre de almacen
	$resultalm = mysqli_query($link, "select pkyWarehouse as codigo, fldName as almacen
from invWarehouse
where
pkyWarehouse = '$almacen' and 
sysEnabled = 1 and sysAuthorized = 1
order by almacen;");
	while ($row = mysqli_fetch_array($resultalm)) {
	$nom_almacen = $row['almacen'];
	}
	mysqli_free_result($resultalm);
  } //fin if almacen
  
if ($local == 0) { //if local
    $valor_local = ' ';
	$nom_local = 'Todos';
  } else { 
    $valor_local = ' a.codSalesPlace = '.$local.' and ';
	//nombre de local
	$resultloc = mysqli_query($link, "select pkySalesPlace as codigo, fldName as local
from slsSalesPlace
where
pkySalesPlace = '$local' and 
sysEnabled = 1 and sysAuthorized = 1
order by local;");
	while ($row = mysqli_fetch_array($resultloc)) {
	$nom_local = $row['local'];
	}
	mysqli_free_result($resultloc);
  } //fin if local

if ($ciudad == 0) { //if ciudad
    $valor_ciudad = ' ';
	$nom_ciudad = 'Todos';
  } else { 
    $valor_ciudad = ' c.codCityLocation = '.$ciudad.' and ';
	//nombre de ciudad
	$resultciu = mysqli_query($link, "select pkyCity as cod_ciu, 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 ($itemini == 0) { // if 2.0
		$nom_item_ini = 'Todos';
		$valor_item_ini = ' ';
		$nom_item_fin = 'Todos';
		$valor_item_fin = ' ';
	} else {
		//nombre item
		$result2 = mysqli_query($link, "select pkyItem as cod_item, fldName as item, fldFactoryKey as cod_fabrica
								from
								invItem 
								where
								pkyItem = '$itemini' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result2)) {
			$nom_item_ini = $row['cod_item'].' - '.$row['cod_fabrica'].' - '.$row['item'];
		}
		mysqli_free_result($result2);
		$valor_item_ini = ' h.codItem >= ' .$itemini. ' and ';
	} //fin if 2.0
	
	if ($itemfin == 0) { // if 2.1
		$nom_item_fin = 'Todos';
		$valor_item_fin = ' ';
	} else {
		//nombre item
		$result2 = mysqli_query($link, "select pkyItem as cod_item, fldName as item, fldFactoryKey as cod_fabrica
								from
								invItem 
								where
								pkyItem = '$itemfin' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result2)) {
			$nom_item_fin = $row['cod_item'].' - '.$row['cod_fabrica'].' - '.$row['item'];
		}
		mysqli_free_result($result2);
		$valor_item_fin = ' h.codItem <= ' .$itemfin. ' and ';
	} //fin if 2.1
	

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 = ' i.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 = ' i.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 = ' i.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 = ' i.codSubGroup = ' .$subgrupo. ' and ';
	} //fin if subgrupo

//contexto
if($cod_contexto == -1){ 
	$nom_contexto = 'Todos';
	$valor_contexto = ' ';
} 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 ';	
}

if ($orden == 0) { // if orden 0
	$nom_orden = 'Fecha';
} // fin if orden 0

if ($orden == 1) { // if orden 1
	$nom_orden = 'Nro. de Documento';
} // fin if orden 1

if ($orden == 2) { // if orden 2
	$nom_orden = 'Co&oacute;digo';
} // fin if orden 2

//calculo de comisiones
if($comisiones == 0) { //if comisiones
//comision por ventas
$result = mysqli_query($link, "select fin.cod_item as cod_item, fin.item as item, fin.cod_cli as cod_cli, fin.cliente as cliente, fin.cod_vendedor as cod_vendedor, fin.vendedor as vendedor, 
fin.cod_alm as cod_alm, fin.almacen as almacen, fin.cod_local as cod_local, fin.local_venta as local_venta, fin.cod_ciudad as cod_ciudad, fin.ciudad as ciudad, 
fin.cod_familia as cod_familia, fin.familia as familia, 
fin.cantidad as cantidad, fin.precio_fin as precio, 
fin.desc_porc as desc_porc, sum(fin.precio_total_fin) as total_ventas, sum(fin.comision_ventas) as comision_ventas, 0 as total_cobros, 0 as comision_cobros
from (
select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, 
t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total,
t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, tc.fldRate as tasa, t.precio * tc.fldRate as precio_fin,
t.precio_total * tc.fldRate as precio_total_fin, 
t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, 
t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, 
t.cod_familia as cod_familia, t.familia as familia, t.comision * tc.fldRate as comision_ventas
from (
select h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, 
case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, 
h.fldQuantity as cantidad, h.fldPrice as precio, h.fldDiscount as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, 
a.codCurrency as cod_moneda_venta, a.codClient as cod_cli,
a.codSeller as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o
where 
$valor_item_ini 
$valor_item_fin  
$valor_vendedor 
$valor_cliente 
$valor_canal_venta 
$valor_almacen 
$valor_local 
$valor_ciudad 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo 
$valor_tipo_nota 
$valor_contexto 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and  
a.codSeller = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.sysEnabled = 1 and a.sysAuthorized = 1) t, actExchangeRateDetail tc
where
tc.codTargetCurrency = t.cod_moneda_venta and
tc.codBaseCurrency = '$otra_moneda' and
tc.codExchangeRate = t.cod_tc_venta

union all 

select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, 
t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total,
t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, 1 as tasa, t.precio * 1 as precio_fin, t.precio_total * 1 as precio_total_fin, 
t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, 
t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, 
t.cod_familia as cod_familia, t.familia as familia, t.comision * 1 as comision_ventas
from (
select h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, 
case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, 
h.fldQuantity as cantidad, h.fldPrice as precio, h.fldDiscount as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, 
a.codCurrency as cod_moneda_venta, a.codClient as cod_cli,
a.codSeller as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o
where 
$valor_item_ini 
$valor_item_fin  
$valor_vendedor 
$valor_cliente 
$valor_canal_venta 
$valor_almacen 
$valor_local 
$valor_ciudad 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo 
$valor_tipo_nota 
$valor_contexto 
a.fldDate >= '$fechaini' and
a.fldDate <= '$fechafin' and  
a.codSeller = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.sysEnabled = 1 and a.sysAuthorized = 1) t
where
t.cod_moneda_venta = '$otra_moneda') fin 
group by fin.cod_vendedor 
order by fin.vendedor;");

} else { //else comisiones

//comision por cobros (en el query esta como vendedor pero es cobrador, no se cambio por que habian muchos lugares que cambiar)
$result = mysqli_query($link, "select fin2.cod_item as cod_item, fin2.item as item, fin2.cod_cli as cod_cli, fin2.cliente as cliente, fin2.cod_vendedor as cod_vendedor, fin2.vendedor as vendedor, 
fin2.cod_alm as cod_alm, fin2.almacen as almacen, fin2.cod_local as cod_local, fin2.local_venta as local_venta, 
fin2.cod_ciudad as cod_ciudad, fin2.ciudad as ciudad, 
fin2.cod_familia as cod_familia, fin2.familia as familia, 
sum(fin2.cantidad) as cantidad, fin2.precio as precio, 
fin2.desc_porc as desc_porc, 0 as total_ventas, 0 as comision_ventas, sum(fin2.total_cobros) as total_cobros, sum(fin2.comision_cobros) as comision_cobros
from (
select fin.cod_item as cod_item, fin.item as item, fin.cod_cli as cod_cli, fin.cliente as cliente, fin.cod_vendedor as cod_vendedor, fin.vendedor as vendedor, 
fin.cod_alm as cod_alm, fin.almacen as almacen, fin.cod_local as cod_local, fin.local_venta as local_venta, 
fin.cod_ciudad as cod_ciudad, fin.ciudad as ciudad, 
fin.cod_familia as cod_familia, fin.familia as familia, 
fin.cantidad as cantidad, fin.precio_fin as precio, 
fin.desc_porc as desc_porc, fin.precio_total_fin as total_ventas, fin.comision_ventas as comision_ventas, (fin.porcentaje_cobros + fin.porcentaje_liquidacion) * fin.precio_total_fin / 100 as total_cobros, (fin.porcentaje_cobros + fin.porcentaje_liquidacion) * fin.comision_ventas / 100 as comision_cobros
from (
select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, 
t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total,
t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, tc.fldRate as tasa, t.precio * tc.fldRate as precio_fin,
t.precio_total * tc.fldRate as precio_total_fin, 
t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, 
t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, 
t.cod_familia as cod_familia, t.familia as familia, t.comision * tc.fldRate as comision_ventas, case when t.porcentaje_cobros is NULL then 0 else t.porcentaje_cobros end as porcentaje_cobros, case when t.porcentaje_liquidacion is NULL then 0 else t.porcentaje_liquidacion end as porcentaje_liquidacion
from (
select distinct h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, 
case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, 
h.fldQuantity as cantidad, h.fldPrice as precio, h.fldDiscount as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, 
a.codCurrency as cod_moneda_venta, a.codClient as cod_cli,
p.codCollector as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision, (select (sum(b1.fldTotal) * 100 / c1.fldTotal) as porcentaje_cobros
from colCollection a1, colCollectionDetail b1, colAccountsReceivable c1
where
a1.pkyCollection = b1.codCollection and 
b1.codAccountsReceivable = a.codAccountReceivable and 
c1.pkyAccountsReceivable = a.codAccountReceivable and 
a1.fldDate >= '$fechaini' and 
a1.fldDate <= '$fechafin' and 
a1.sysEnabled = 1 and a1.sysAuthorized = 1
group by b1.codAccountsReceivable) as porcentaje_cobros, 
(select (sum(b2.fldTotal) * 100 / c2.fldTotal) as porcentaje_liquidacion
from payPayment a2, payPaymentDetail b2, colAccountsReceivable c2
where
a2.pkyPayment = b2.codPayment and 
b2.codAccountsReceivable = a.codAccountReceivable and 
c2.pkyAccountsReceivable = a.codAccountReceivable and 
a2.fldDate >= '$fechaini' and 
a2.fldDate <= '$fechafin' and 
a2.sysEnabled = 1 and a2.sysAuthorized = 1
group by b2.codAccountsReceivable) as porcentaje_liquidacion
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o, colCollection p, colCollectionDetail q
where 
$valor_item_ini 
$valor_item_fin  
$valor_vendedor 
$valor_cliente 
$valor_canal_venta 
$valor_almacen 
$valor_local 
$valor_ciudad 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo 
$valor_tipo_nota 
$valor_contexto 
p.fldDate >= '$fechaini' and
p.fldDate <= '$fechafin' and 
p.pkyCollection = q.codCollection and 
q.codAccountsReceivable = a.codAccountReceivable and  
p.codCollector = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.sysEnabled = 1 and a.sysAuthorized = 1 and 
p.sysEnabled = 1 and p.sysAuthorized = 1) t, actExchangeRateDetail tc
where
tc.codTargetCurrency = t.cod_moneda_venta and
tc.codBaseCurrency = '$otra_moneda' and
tc.codExchangeRate = t.cod_tc_venta

union all 

select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, 
t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total,
t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, 1 as tasa, t.precio * 1 as precio_fin, t.precio_total * 1 as precio_total_fin, 
t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, 
t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, 
t.cod_familia as cod_familia, t.familia as familia, t.comision * 1 as comision_ventas, case when t.porcentaje_cobros is NULL then 0 else t.porcentaje_cobros end as porcentaje_cobros, case when t.porcentaje_liquidacion is NULL then 0 else t.porcentaje_liquidacion end as porcentaje_liquidacion
from (
select distinct h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, 
case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, 
h.fldQuantity as cantidad, h.fldPrice as precio, h.fldDiscount as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, 
a.codCurrency as cod_moneda_venta, a.codClient as cod_cli,
p.codCollector as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision, (select (sum(b1.fldTotal) * 100 / c1.fldTotal) as porcentaje_cobros
from colCollection a1, colCollectionDetail b1, colAccountsReceivable c1
where
a1.pkyCollection = b1.codCollection and 
b1.codAccountsReceivable = a.codAccountReceivable and 
c1.pkyAccountsReceivable = a.codAccountReceivable and 
a1.fldDate >= '$fechaini' and 
a1.fldDate <= '$fechafin' and 
a1.sysEnabled = 1 and a1.sysAuthorized = 1
group by b1.codAccountsReceivable) as porcentaje_cobros, 
(select (sum(b2.fldTotal) * 100 / c2.fldTotal) as porcentaje_liquidacion
from payPayment a2, payPaymentDetail b2, colAccountsReceivable c2
where
a2.pkyPayment = b2.codPayment and 
b2.codAccountsReceivable = a.codAccountReceivable and 
c2.pkyAccountsReceivable = a.codAccountReceivable and 
a2.fldDate >= '$fechaini' and 
a2.fldDate <= '$fechafin' and 
a2.sysEnabled = 1 and a2.sysAuthorized = 1
group by b2.codAccountsReceivable) as porcentaje_liquidacion
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o, colCollection p, colCollectionDetail q
where 
$valor_item_ini 
$valor_item_fin  
$valor_vendedor 
$valor_cliente 
$valor_canal_venta 
$valor_almacen 
$valor_local 
$valor_ciudad 
$valor_familia 
$valor_subfamilia 
$valor_grupo 
$valor_subgrupo 
$valor_tipo_nota 
$valor_contexto 
p.fldDate >= '$fechaini' and
p.fldDate <= '$fechafin' and 
p.pkyCollection = q.codCollection and 
q.codAccountsReceivable = a.codAccountReceivable and  
p.codCollector = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.sysEnabled = 1 and a.sysAuthorized = 1 and 
p.sysEnabled = 1 and p.sysAuthorized = 1) t
where
t.cod_moneda_venta = '$otra_moneda') fin ) fin2
group by fin2.cod_vendedor 
order by fin2.vendedor;");

} //fin if comisiones
?>
<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>Comisiones por Vendedor (<?php echo $nom_comisiones; ?>)</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>Vendedor / Cobrador: </b><?php echo $nom_vendedor; ?></TD>
        <TD width="25%"><b>Tipo: </b><?php echo $nom_tipo_nota; ?></TD>
        <TD width="25%"><b>Cliente: </b><?php echo $nom_cliente; ?></TD>
        <TD width="25%"><b>Canal (solo para empresas): </b><?php echo $nom_canal_venta; ?></TD>
        </TR>
        <TR>
        <TD><b>Local o Punto de venta: </b><?php echo $nom_local; ?></TD>
        <TD><b>Ciudad: </b><?php echo $nom_ciudad; ?></TD>
        <TD><b>Item inicial: </b><?php echo $nom_item_ini; ?></TD>
        <TD><b>Item final: </b><?php echo $nom_item_fin; ?></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>Almacen: </b><?php echo $nom_almacen; ?></TD>
        <TD><b>Ordenar Detalle por: </b><?php echo $nom_orden; ?></TD>
        <TD><b>De Fecha: </b><?php echo $fechaini; ?> <b>a Fecha: </b><?php echo $fechafin; ?></TD>
        <TD><b>Expresado en: </b><?php echo $nom_otra_moneda; ?></TD>
        </TR>
        <TR>
        <TD colspan="4"><b>Contexto: </b><?php echo $nom_contexto; ?></TD>
        </TR>
    </table>

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

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

<table border="1" align="center" cellpadding="2" width="100%">
 	<thead>
    <TR> 
 	<TD align="center"><b>Cod.</b></TD>
    <?php if($comisiones == 0) { ?>
    <TD align="center"><b>Vendedor</b></TD>
    <TD align="center"><b>Ventas del Periodo</b></TD>
    <TD align="center"><b>Comisiones por Ventas</b></TD>
    <?php } ?>
    <?php if($comisiones == 1) { ?>
    <TD align="center"><b>Cobrador</b></TD>
    <TD align="center"><b>Cobranzas del Periodo</b></TD>
    <TD align="center"><b>Comisiones por Cobranzas</b></TD>
    <?php } ?>
    </TR>
    </thead>
    <tbody class="marcafila">

    <?php
    	
	// cargar listado comisiones del periodo 
	$matriz_comi = array ();
	$i = 1;
	while ($row = mysqli_fetch_array($result)) {
		$matriz_comi[$i][1] = $row['cod_vendedor'];
		$matriz_comi[$i][2] = $row['vendedor'];
		$matriz_comi[$i][3] = $row['total_ventas'];
		$matriz_comi[$i][4] = $row['comision_ventas'];
		$matriz_comi[$i][5] = $row['total_cobros'];
		$matriz_comi[$i][6] = $row['comision_cobros'];
					
		$i++;
		}
		mysqli_free_result($result);
	
	$fin_comi = $i - 1;
		
	$color = 2;
	$suma_pt = 0;
	$suma_co = 0;
	$suma_pt2 = 0;
	$suma_co2 = 0;
	
	$i = 1;
	
	while($i <= $fin_comi) { //while
		
		if($color % 2 == 0) { //if color
		printf("<tr BGCOLOR='#e2e7fa'><td align='center'>%s</td>", $matriz_comi[$i][1]);//cod
		} else {
		printf("<tr><td align='center'>%s</td>", $matriz_comi[$i][1]);//cod
		}//cierra if color
		printf("<td>%s</td>", $matriz_comi[$i][2]);//vendedor/cobrador
		$cod_vendedor2 = $matriz_comi[$i][1];
		
		if($comisiones == 0) {
		printf("<td align='right'>%s</td>", number_format($matriz_comi[$i][3],$n_decimales));//ventas
		printf("<td align='right'><a href='report.php?rep=14&tipo=2&vendedor_det=$cod_vendedor2&cliente_det=$cliente&canal_venta_det=$canal_venta&almacen_det=$almacen&local_det=$local&ciudad_det=$ciudad&itemini_det=$itemini&itemfin_det=$itemfin&comisiones_det=$comisiones&mostrar_det=$mostrar&otra_moneda_det=$otra_moneda&fechaini_det=$fechaini&fechafin_det=$fechafin&tipo_nota_det=$tipo_nota&familia_det=$familia&subfamilia_det=$subfamilia&grupo_det=$grupo&subgrupo_det=$subgrupo&orden_det=$orden&contexto_det=$cod_contexto&usuario_det=$usuario&token=$token' title='Notas de Venta del Periodo'>%s</a></td></tr>", number_format($matriz_comi[$i][4],$n_decimales));//comision ventas
		}
		if($comisiones == 1) {
		printf("<td align='right'>%s</td>", number_format($matriz_comi[$i][5],$n_decimales));//cobros
		printf("<td align='right'><a href='report.php?rep=14&tipo=2&vendedor_det=$cod_vendedor2&cliente_det=$cliente&canal_venta_det=$canal_venta&almacen_det=$almacen&local_det=$local&ciudad_det=$ciudad&itemini_det=$itemini&itemfin_det=$itemfin&comisiones_det=$comisiones&mostrar_det=$mostrar&otra_moneda_det=$otra_moneda&fechaini_det=$fechaini&fechafin_det=$fechafin&tipo_nota_det=$tipo_nota&familia_det=$familia&subfamilia_det=$subfamilia&grupo_det=$grupo&subgrupo_det=$subgrupo&orden_det=$orden&contexto_det=$cod_contexto&usuario_det=$usuario&token=$token' title='Notas de Venta del Periodo'>%s</a></td></tr>", number_format($matriz_comi[$i][6],$n_decimales));//comision cobros
		}
						
		$suma_pt = $suma_pt + $matriz_comi[$i][3];
		$suma_co = $suma_co + $matriz_comi[$i][4];
		
		$suma_pt2 = $suma_pt2 + $matriz_comi[$i][5];
		$suma_co2 = $suma_co2 + $matriz_comi[$i][6];
		
	  	$color++;
		$i++;
	}//fin while
	
	?>
 
 <tr BGCOLOR='#ffcccc'><td colspan="2"><b>Totales</b></td>
 <?php if($comisiones == 0) { ?>
 <td align="right"><?php printf("<b>%s</b>", number_format($suma_pt,$n_decimales)); ?></td>
 <td align="right"><?php printf("<b>%s</b>", number_format($suma_co,$n_decimales)); ?></td>
 <?php } ?>
 <?php if($comisiones == 1) { ?>
 <td align="right"><?php printf("<b>%s</b>", number_format($suma_pt2,$n_decimales)); ?></td>
 <td align="right"><?php printf("<b>%s</b>", number_format($suma_co2,$n_decimales)); ?></td>
 <?php } ?>
 </tr>
 </tbody>
 </table>
         

<?php
	
} //fin if 2



// inicio muestra detalle

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

//nombre 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);

if($comisiones_det == 0) {
	$nom_comisiones_det = 'Ventas';
} else {
	$nom_comisiones_det = 'Cobranzas';
}

if($mostrar_det == 0) {
	$familia_det = 0;
	$subfamilia_det = 0;
	$grupo_det = 0;
	$subgrupo_det = 0;
} else {
	$itemini_det = 0;
	$itemfin_det = 0;
}

//tipo nota
if ($tipo_nota_det == 0) { //if tipo nota
	$nom_tipo_nota_det = 'Todos';
	$valor_tipo_nota_det = ' ';
  } else { 
  	$valor_tipo_nota_det = ' a.codType = '.$tipo_nota_det.' and ';
	//nombre de tipo de nota
$resulttipo = mysqli_query($link, "select pkyOrderNoteType as codigo, fldName as tipo
from 
slsOrderNoteType
where
pkyOrderNoteType = '$tipo_nota_det' and 
sysEnabled = 1 and sysAuthorized = 1
order by tipo;");
	while ($row = mysqli_fetch_array($resulttipo)) {
	$nom_tipo_nota_det = $row['tipo'];
	}
	mysqli_free_result($resulttipo);
  } //fin if tipo nota

if ($vendedor_det == 0) { //if vendedor
    $valor_vendedor_det = ' ';
	$nom_vendedor_det = 'Todos';
  } else { 
    if($comisiones_det == 0) {
    $valor_vendedor_det = ' a.codSeller = '.$vendedor_det.' and ';
	} else {
	//en caso de que sea cobrador (comision por cobro)	
	$valor_vendedor_det = ' p.codCollector = '.$vendedor_det.' and ';	
	}
    
	//nombre de vendedor
	$resultven = mysqli_query($link, "select pkyPerson as codigo, case when fldMiddleName is NULL then concat(fldLastName,', ',fldFirstName) else concat(fldLastName,' ',fldMiddleName,', ',fldFirstName) end as vendedor
from agnPerson
where
fldIsEmployee = 1 and
fldCanSell = 1 and 
pkyPerson = '$vendedor_det' and
sysEnabled = 1 and sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($resultven)) {
	$nom_vendedor_det = $row['codigo'].' - '.$row['vendedor'];
	}
	mysqli_free_result($resultven);
  } //fin if vendedor

if ($cliente_det == 0) { //if cliente
    $valor_cliente_det = ' ';
	$nom_cliente_det = 'Todos';
  } else { 
    $valor_cliente_det = ' a.codClient = '.$cliente_det.' and ';
	//nombre de cliente
	$resultcli = mysqli_query($link, "select pkyCompany as codigo, fldName as cliente
from agnCompany
where
fldIsClient = 1 and
pkyCompany = '$cliente_det' and 
sysEnabled = 1 and sysAuthorized = 1;");
	while ($row = mysqli_fetch_array($resultcli)) {
	$nom_cliente_det = $row['codigo'].' - '.$row['cliente'];
	}
	mysqli_free_result($resultcli);
  } //fin if cliente

if($canal_venta_det == 0) { // if canal
	$nom_canal_venta_det = 'Todos';
	$valor_canal_venta_det = ' ';
} else { 
	$resultcan = mysqli_query($link, "select pkyChannel as codigo, fldName as canal
						from agnChannel
						where
						pkyChannel = '$canal_venta_det';");
	while ($row = mysqli_fetch_array($resultcan)) {   
	$nom_canal_venta_det = $row['canal'];
	}
	mysqli_free_result($resultcan);
	$valor_canal_venta_det = ' c.codChannel = '.$canal_venta_det.' and ';
} // fin if canal

if ($almacen_det == 0) { //if almacen
    $valor_almacen_det = ' ';
	$nom_almacen_det = 'Todos';
  } else { 
    $valor_almacen_det = ' a.codWarehouse = '.$almacen_det.' and ';
	//nombre de almacen
	$resultalm = mysqli_query($link, "select pkyWarehouse as codigo, fldName as almacen
from invWarehouse
where
pkyWarehouse = '$almacen_det' and 
sysEnabled = 1 and sysAuthorized = 1
order by almacen;");
	while ($row = mysqli_fetch_array($resultalm)) {
	$nom_almacen_det = $row['almacen'];
	}
	mysqli_free_result($resultalm);
  } //fin if almacen
  
if ($local_det == 0) { //if local
    $valor_local_det = ' ';
	$nom_local_det = 'Todos';
  } else { 
    $valor_local_det = ' a.codSalesPlace = '.$local_det.' and ';
	//nombre de local
	$resultloc = mysqli_query($link, "select pkySalesPlace as codigo, fldName as local
from slsSalesPlace
where
pkySalesPlace = '$local_det' and 
sysEnabled = 1 and sysAuthorized = 1
order by local;");
	while ($row = mysqli_fetch_array($resultloc)) {
	$nom_local_det = $row['local'];
	}
	mysqli_free_result($resultloc);
  } //fin if local

if ($ciudad_det == 0) { //if ciudad
    $valor_ciudad_det = ' ';
	$nom_ciudad_det = 'Todos';
  } else { 
    $valor_ciudad_det = ' c.codCityLocation = '.$ciudad_det.' and ';
	//nombre de ciudad
	$resultciu = mysqli_query($link, "select pkyCity as cod_ciu, fldName as ciudad 
							from regCity
							where pkyCity = '$ciudad_det';");
	while ($row = mysqli_fetch_array($resultciu)) {
	$nom_ciudad_det = $row['ciudad'];
	}
	mysqli_free_result($resultciu);
  } //fin if ciudad


if ($itemini_det == 0) { // if 2.0
		$nom_item_ini_det = 'Todos';
		$valor_item_ini_det = ' ';
		$nom_item_fin_det = 'Todos';
		$valor_item_fin_det = ' ';
	} else {
		//nombre item
		$result2 = mysqli_query($link, "select pkyItem as cod_item, fldName as item, fldFactoryKey as cod_fabrica
								from
								invItem 
								where
								pkyItem = '$itemini_det' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result2)) {
			$nom_item_ini_det = $row['cod_item'].' - '.$row['cod_fabrica'].' - '.$row['item'];;
		}
		mysqli_free_result($result2);
		$valor_item_ini_det = ' h.codItem >= ' .$itemini_det. ' and ';
	} //fin if 2.0
	
	if ($itemfin_det == 0) { // if 2.1
		$nom_item_fin_det = 'Todos';
		$valor_item_fin_det = ' ';
	} else {
		//nombre item
		$result2 = mysqli_query($link, "select pkyItem as cod_item, fldName as item, fldFactoryKey as cod_fabrica
								from
								invItem 
								where
								pkyItem = '$itemfin_det' and
								sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($result2)) {
			$nom_item_fin_det = $row['cod_item'].' - '.$row['cod_fabrica'].' - '.$row['item'];;
		}
		mysqli_free_result($result2);
		$valor_item_fin_det = ' h.codItem <= ' .$itemfin_det. ' and ';
	} //fin if 2.1

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

if ($subfamilia_det == 0) { // if subfamilia
		$nom_subfamilia_det = 'Todos';
		$valor_subfamilia_det = ' ';
	} else {
		//nombre sub familia
		$resultsubf = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$subfamilia_det' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsubf)) {
			$nom_subfamilia_det = $row['nombre'];
		}
		mysqli_free_result($resultsubf);
		$valor_subfamilia_det = ' i.codSubFamily = ' .$subfamilia_det. ' and ';
	} //fin if subfamilia
	
if ($grupo_det == 0) { // if grupo
		$nom_grupo_det = 'Todos';
		$valor_grupo_det = ' ';
	} else {
		//nombre grupo
		$resultgru = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$grupo_det' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultgru)) {
			$nom_grupo_det = $row['nombre'];
		}
		mysqli_free_result($resultgru);
		$valor_grupo_det = ' i.codGroup = ' .$grupo_det. ' and ';
	} //fin if grupo
	
if ($subgrupo_det == 0) { // if subgrupo
		$nom_subgrupo_det = 'Todos';
		$valor_subgrupo_det = ' ';
	} else {
		//nombre sub grupo
		$resultsubg = mysqli_query($link, "select pkyItemCategory as cod, fldName as nombre
							from 
							invItemCategory
							where
							pkyItemCategory = '$subgrupo_det' and 
							sysEnabled = 1 and sysAuthorized = 1;");
		while ($row = mysqli_fetch_array($resultsubg)) {
			$nom_subgrupo_det = $row['nombre'];
		}
		mysqli_free_result($resultsubg);
		$valor_subgrupo_det = ' i.codSubGroup = ' .$subgrupo_det. ' and ';
	} //fin if subgrupo

//contexto
if($cod_contexto_det == -1){ 
	$nom_contexto_det = 'Todos';
	$valor_contexto_det = ' ';
} 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 ';	
}

if ($orden_det == 0) { // if orden 0
	$nom_orden_det = 'Fecha';
	$valor_orden_det = 'order by fecha, cod_nv';
} // fin if orden 0

if ($orden_det == 1) { // if orden 1
	$nom_orden_det = 'Nro. de Documento';
	$valor_orden_det = 'order by cast(nro_doc as unsigned), fecha, cod_nv';
} // fin if orden 1

if ($orden_det == 2) { // if orden 2
	$nom_orden_det = 'Co&oacute;digo';
	$valor_orden_det = 'order by cod_nv';
} // fin if orden 2

//mismo query general pero con adicion de campos en fin en fin y con agrupacion por nota de venta

if($comisiones_det == 0) {//if comisiones

//detalle comisiones ventas
$resultdet = mysqli_query($link, "select fin.cod_nv as cod_nv, fin.nro_doc as nro_doc, fin.fecha as fecha, fin.cod_cli as cod_cli, fin.cliente as cliente, fin.cod_vendedor as cod_vendedor, fin.vendedor as vendedor, fin.cod_alm as cod_alm, fin.almacen as almacen, fin.cod_local as cod_local, fin.local_venta as local_venta, fin.cod_ciudad as cod_ciudad, fin.ciudad as ciudad, fin.cod_familia as cod_familia, fin.familia as familia, sum(fin.cantidad) as cantidad, sum(fin.precio_fin) as precio, fin.desc_porc as desc_porc, sum(fin.precio_total_fin) as total_ventas, sum(fin.comision_ventas) as comision_ventas, 0 as total_cobros, 0 as comision_cobros, fin.desc_global as desc_global, fin.cod_factura as cod_factura, fin.nro_factura as nro_factura, 0 as total_porcentaje_cobros, 0 as saldo_pendiente, 0 as cxc 
from (
select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total, t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, tc.fldRate as tasa, t.precio * tc.fldRate as precio_fin, t.precio_total * tc.fldRate as precio_total_fin, t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, t.cod_familia as cod_familia, t.familia as familia, t.comision * tc.fldRate as comision_ventas, t.cod_nv as cod_nv, t.desc_global as desc_global, t.cod_factura as cod_factura, t.nro_factura as nro_factura
from (
select h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, 
case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, h.fldQuantity as cantidad, h.fldPrice as precio, a.fldTotalDiscountPercent as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, a.codCurrency as cod_moneda_venta, a.codClient as cod_cli,
a.codSeller as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision, a.pkySalesNote as cod_nv, a.fldGlobalDiscountPercent as desc_global, a.codInvoice as cod_factura, case when a.codInvoice is NULL then a.codInvoice else (select fldNumber from txsSalesLedger where pkySalesLedger = a.codInvoice) end as nro_factura 
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o
where 
$valor_item_ini_det 
$valor_item_fin_det  
$valor_vendedor_det 
$valor_cliente_det 
$valor_canal_venta_det 
$valor_almacen_det 
$valor_local_det 
$valor_ciudad_det 
$valor_familia_det 
$valor_subfamilia_det 
$valor_grupo_det 
$valor_subgrupo_det 
$valor_tipo_nota_det 
$valor_contexto_det 
a.fldDate >= '$fechaini_det' and
a.fldDate <= '$fechafin_det' and  
a.codSeller = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.sysEnabled = 1 and a.sysAuthorized = 1) t, actExchangeRateDetail tc
where
tc.codTargetCurrency = t.cod_moneda_venta and
tc.codBaseCurrency = '$otra_moneda_det' and
tc.codExchangeRate = t.cod_tc_venta

union all 

select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total, t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, 1 as tasa, t.precio * 1 as precio_fin, t.precio_total * 1 as precio_total_fin, t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, 
t.cod_familia as cod_familia, t.familia as familia, t.comision * 1 as comision_ventas, t.cod_nv as cod_nv, t.desc_global as desc_global, t.cod_factura as cod_factura, t.nro_factura as nro_factura
from (
select h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, 
case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, 
h.fldQuantity as cantidad, h.fldPrice as precio, a.fldTotalDiscountPercent as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, 
a.codCurrency as cod_moneda_venta, a.codClient as cod_cli,
a.codSeller as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision, a.pkySalesNote as cod_nv, a.fldGlobalDiscountPercent as desc_global, a.codInvoice as cod_factura, case when a.codInvoice is NULL then a.codInvoice else (select fldNumber from txsSalesLedger where pkySalesLedger = a.codInvoice) end as nro_factura 
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o
where 
$valor_item_ini_det 
$valor_item_fin_det 
$valor_vendedor_det 
$valor_cliente_det 
$valor_canal_venta_det 
$valor_almacen_det 
$valor_local_det 
$valor_ciudad_det 
$valor_familia_det 
$valor_subfamilia_det 
$valor_grupo_det 
$valor_subgrupo_det 
$valor_tipo_nota_det 
$valor_contexto_det 
a.fldDate >= '$fechaini_det' and
a.fldDate <= '$fechafin_det' and  
a.codSeller = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.sysEnabled = 1 and a.sysAuthorized = 1) t
where
t.cod_moneda_venta = '$otra_moneda_det') fin 
group by fin.cod_nv
$valor_orden_det;");

} else {

//detalle comisiones cobros	(se quito select distinct h.codItem... en dos lugares para que muestre cuando en la nv hay el mismo item con la misma cantidad y el precio) se volvio a poner
$resultdet = mysqli_query($link, "select fin2.cod_nv as cod_nv, fin2.nro_doc as nro_doc, fin2.fecha as fecha, fin2.cod_cli as cod_cli, fin2.cliente as cliente, fin2.cod_vendedor as cod_vendedor, fin2.vendedor as vendedor, fin2.cod_alm as cod_alm, fin2.almacen as almacen, fin2.cod_local as cod_local, fin2.local_venta as local_venta, fin2.cod_ciudad as cod_ciudad, fin2.ciudad as ciudad, fin2.cod_familia as cod_familia, fin2.familia as familia, sum(fin2.cantidad) as cantidad, fin2.precio as precio, fin2.desc_porc as desc_porc, sum(fin2.total_ventas) as total_ventas, 0 as comision_ventas, sum(fin2.total_cobros) as total_cobros, sum(fin2.comision_cobros) as comision_cobros, fin2.desc_global as desc_global, fin2.cod_factura as cod_factura, fin2.nro_factura as nro_factura, fin2.total_porcentaje_cobros as total_porcentaje_cobros, fin2.saldo_pendiente as saldo_pendiente, fin2.cod_cxc as cod_cxc 
from (
select fin.cod_item as cod_item, fin.item as item, fin.fecha as fecha, fin.cod_cli as cod_cli, fin.cliente as cliente, fin.cod_vendedor as cod_vendedor, fin.vendedor as vendedor, fin.cod_alm as cod_alm, fin.almacen as almacen, fin.cod_local as cod_local, fin.local_venta as local_venta, 
fin.cod_ciudad as cod_ciudad, fin.ciudad as ciudad, fin.cod_familia as cod_familia, fin.familia as familia, fin.cantidad as cantidad, fin.precio_fin as precio, fin.desc_porc as desc_porc, fin.precio_total_fin as total_ventas, fin.comision_ventas as comision_ventas, (fin.porcentaje_cobros + fin.porcentaje_liquidacion) * fin.precio_total_fin / 100 as total_cobros, (fin.porcentaje_cobros + fin.porcentaje_liquidacion) * fin.comision_ventas / 100 as comision_cobros, fin.cod_nv as cod_nv, fin.nro_doc as nro_doc, fin.desc_global as desc_global, fin.cod_factura as cod_factura, fin.nro_factura as nro_factura, (fin.porcentaje_cobros + fin.porcentaje_liquidacion) as total_porcentaje_cobros, fin.saldo_pendiente as saldo_pendiente, fin.cod_cxc as cod_cxc 
from (
select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total, t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, tc.fldRate as tasa, t.precio * tc.fldRate as precio_fin, t.precio_total * tc.fldRate as precio_total_fin, t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, t.cod_familia as cod_familia, t.familia as familia, t.comision * tc.fldRate as comision_ventas, case when t.porcentaje_cobros is NULL then 0 else t.porcentaje_cobros end as porcentaje_cobros, case when t.porcentaje_liquidacion is NULL then 0 else t.porcentaje_liquidacion end as porcentaje_liquidacion, t.cod_nv as cod_nv, t.desc_global as desc_global, t.cod_factura as cod_factura, t.nro_factura as nro_factura, t.saldo_pendiente * tc.fldRate as saldo_pendiente
from (
select distinct h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, 
h.fldQuantity as cantidad, h.fldPrice as precio, a.fldTotalDiscountPercent as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, a.codCurrency as cod_moneda_venta, a.codClient as cod_cli, p.codCollector as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision, (select (sum(b1.fldTotal) * 100 / c1.fldTotal) as porcentaje_cobros
from colCollection a1, colCollectionDetail b1, colAccountsReceivable c1
where
a1.pkyCollection = b1.codCollection and 
b1.codAccountsReceivable = a.codAccountReceivable and 
c1.pkyAccountsReceivable = a.codAccountReceivable and 
a1.fldDate >= '$fechaini_det' and 
a1.fldDate <= '$fechafin_det' and 
a1.sysEnabled = 1 and a1.sysAuthorized = 1
group by b1.codAccountsReceivable) as porcentaje_cobros, 
(select (sum(b2.fldTotal) * 100 / c2.fldTotal) as porcentaje_liquidacion
from payPayment a2, payPaymentDetail b2, colAccountsReceivable c2
where
a2.pkyPayment = b2.codPayment and 
b2.codAccountsReceivable = a.codAccountReceivable and 
c2.pkyAccountsReceivable = a.codAccountReceivable and 
a2.fldDate >= '$fechaini_det' and 
a2.fldDate <= '$fechafin_det' and 
a2.sysEnabled = 1 and a2.sysAuthorized = 1
group by b2.codAccountsReceivable) as porcentaje_liquidacion, 
a.pkySalesNote as cod_nv, a.fldGlobalDiscountPercent as desc_global, a.codInvoice as cod_factura, case when a.codInvoice is NULL then a.codInvoice else (select fldNumber from txsSalesLedger where pkySalesLedger = a.codInvoice) end as nro_factura, r.fldBalance as saldo_pendiente 
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o, colCollection p, colCollectionDetail q, colAccountsReceivable r 
where 
$valor_item_ini_det 
$valor_item_fin_det 
$valor_vendedor_det 
$valor_cliente_det 
$valor_canal_venta_det 
$valor_almacen_det 
$valor_local_det 
$valor_ciudad_det 
$valor_familia_det 
$valor_subfamilia_det 
$valor_grupo_det
$valor_subgrupo_det 
$valor_tipo_nota_det   
$valor_contexto_det 
p.fldDate >= '$fechaini_det' and
p.fldDate <= '$fechafin_det' and 
p.pkyCollection = q.codCollection and 
q.codAccountsReceivable = a.codAccountReceivable and  
p.codCollector = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.codAccountReceivable = r.pkyAccountsReceivable and 
a.sysEnabled = 1 and a.sysAuthorized = 1 and 
p.sysEnabled = 1 and p.sysAuthorized = 1) t, actExchangeRateDetail tc
where
tc.codTargetCurrency = t.cod_moneda_venta and
tc.codBaseCurrency = '$otra_moneda_det' and
tc.codExchangeRate = t.cod_tc_venta

union all 

select t.cod_item as cod_item, t.item as item, t.fecha as fecha, t.nro_doc as nro_doc, t.cliente as cliente, t.vendedor as vendedor, t.cod_alm as cod_alm, t.almacen as almacen, t.local_venta as local_venta, t.cod_local as cod_local, t.moneda_venta as moneda_venta, t.cod_tc_venta as cod_tc_venta, t.cantidad as cantidad, t.precio as precio, t.desc_porc as desc_porc, t.precio_total as precio_total, t.cod_cxc as cod_cxc, t.cod_moneda_venta as cod_moneda_venta, 1 as tasa, t.precio * 1 as precio_fin, t.precio_total * 1 as precio_total_fin, t.cod_cli as cod_cli, t.cod_vendedor as cod_vendedor, t.cod_ciudad as cod_ciudad, t.ciudad as ciudad, 
t.cod_familia as cod_familia, t.familia as familia, t.comision * 1 as comision_ventas, case when t.porcentaje_cobros is NULL then 0 else t.porcentaje_cobros end as porcentaje_cobros, case when t.porcentaje_liquidacion is NULL then 0 else t.porcentaje_liquidacion end as porcentaje_liquidacion, t.cod_nv as cod_nv, t.desc_global as desc_global, t.cod_factura as cod_factura, t.nro_factura as nro_factura, t.saldo_pendiente * 1 as saldo_pendiente 
from (
select distinct h.codItem as cod_item, i.fldName as item, a.fldDate as fecha, a.fldNumber as nro_doc, c.fldName as cliente, case when b.fldMiddleName is NULL then concat(b.fldLastName,', ',b.fldFirstName) else concat(b.fldLastName,' ',b.fldMiddleName,', ',b.fldFirstName) end as vendedor, a.codWarehouse as cod_alm, d.fldName as almacen, e.fldName as local_venta, a.codSalesPlace as cod_local, f.fldName as moneda_venta, a.codExchangeRate as cod_tc_venta, 
h.fldQuantity as cantidad, h.fldPrice as precio, a.fldTotalDiscountPercent as desc_porc, h.fldTotalNetPrice as precio_total, a.codAccountReceivable as cod_cxc, a.codCurrency as cod_moneda_venta, a.codClient as cod_cli, p.codCollector as cod_vendedor, c.codCityLocation as cod_ciudad, m.fldName as ciudad, i.codFamily as cod_familia, o.fldName as familia, h.fldCommission as comision, (select (sum(b1.fldTotal) * 100 / c1.fldTotal) as porcentaje_cobros
from colCollection a1, colCollectionDetail b1, colAccountsReceivable c1
where
a1.pkyCollection = b1.codCollection and 
b1.codAccountsReceivable = a.codAccountReceivable and 
c1.pkyAccountsReceivable = a.codAccountReceivable and 
a1.fldDate >= '$fechaini_det' and 
a1.fldDate <= '$fechafin_det' and 
a1.sysEnabled = 1 and a1.sysAuthorized = 1
group by b1.codAccountsReceivable) as porcentaje_cobros, 
(select (sum(b2.fldTotal) * 100 / c2.fldTotal) as porcentaje_liquidacion
from payPayment a2, payPaymentDetail b2, colAccountsReceivable c2
where
a2.pkyPayment = b2.codPayment and 
b2.codAccountsReceivable = a.codAccountReceivable and 
c2.pkyAccountsReceivable = a.codAccountReceivable and 
a2.fldDate >= '$fechaini_det' and 
a2.fldDate <= '$fechafin_det' and 
a2.sysEnabled = 1 and a2.sysAuthorized = 1
group by b2.codAccountsReceivable) as porcentaje_liquidacion, 
a.pkySalesNote as cod_nv, a.fldGlobalDiscountPercent as desc_global, a.codInvoice as cod_factura, case when a.codInvoice is NULL then a.codInvoice else (select fldNumber from txsSalesLedger where pkySalesLedger = a.codInvoice) end as nro_factura, r.fldBalance as saldo_pendiente 
from slsSalesNote a, agnPerson b, agnCompany c, invWarehouse d, slsSalesPlace e, actCurrency f,  
slsSalesNoteDetail h, invItem i, regCity m, invItemCategory o, colCollection p, colCollectionDetail q, colAccountsReceivable r 
where 
$valor_item_ini_det 
$valor_item_fin_det 
$valor_vendedor_det 
$valor_cliente_det 
$valor_canal_venta_det 
$valor_almacen_det 
$valor_local_det 
$valor_ciudad_det 
$valor_familia_det 
$valor_subfamilia_det 
$valor_grupo_det
$valor_subgrupo_det 
$valor_tipo_nota_det 
$valor_contexto_det 
p.fldDate >= '$fechaini_det' and
p.fldDate <= '$fechafin_det' and 
p.pkyCollection = q.codCollection and 
q.codAccountsReceivable = a.codAccountReceivable and  
p.codCollector = b.pkyPerson and
a.codClient = c.pkyCompany and 
a.codWarehouse = d.pkyWarehouse and 
a.codSalesPlace = e.pkySalesPlace and 
a.codCurrency = f.pkyCurrency and 
c.codCityLocation = m.pkyCity and 
h.codSalesNote = a.pkySalesNote and 
i.pkyItem = h.codItem and 
i.codFamily = o.pkyItemCategory and 
a.codAccountReceivable = r.pkyAccountsReceivable and 
a.sysEnabled = 1 and a.sysAuthorized = 1 and 
p.sysEnabled = 1 and p.sysAuthorized = 1) t
where
t.cod_moneda_venta = '$otra_moneda_det') fin ) fin2
group by fin2.cod_nv 
$valor_orden_det;");

}

$matriz_det = array();
$i = 1;

		while ($row = mysqli_fetch_array($resultdet)) {
		$matriz_det[$i][1] = $row['cod_nv'];
		$matriz_det[$i][2] = $row['nro_doc'];
		$matriz_det[$i][3] = $row['fecha'];
		$matriz_det[$i][4] = $row['cliente'];
		$matriz_det[$i][5] = $row['almacen'];
		$matriz_det[$i][6] = $row['local_venta'];
		$matriz_det[$i][7] = $row['ciudad'];
		$matriz_det[$i][8] = $row['cantidad'];
		$matriz_det[$i][9] = $row['precio'];
		$matriz_det[$i][10] = $row['desc_porc'];
		$matriz_det[$i][11] = $row['total_ventas'];
		$matriz_det[$i][12] = $row['comision_ventas'];
		$matriz_det[$i][13] = $row['total_cobros'];
		$matriz_det[$i][14] = $row['comision_cobros'];
		$matriz_det[$i][15] = $row['desc_global'];
		$matriz_det[$i][16] = $row['cod_factura'];
		$matriz_det[$i][17] = $row['nro_factura'];
		$matriz_det[$i][18] = $row['total_porcentaje_cobros'];
		$matriz_det[$i][19] = $row['saldo_pendiente'];
		$matriz_det[$i][20] = $row['cod_cxc'];
		
		$i++;
		}
		mysqli_free_result($resultdet);
		$fin_det = $i - 1;
?>
<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 Notas de Venta (<?php echo $nom_comisiones_det; ?>)</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>Vendedor / Cobrador: </b><?php echo $nom_vendedor_det; ?></TD>
        <TD width="25%"><b>Tipo: </b><?php echo $nom_tipo_nota_det; ?></TD>
        <TD width="25%"><b>Cliente: </b><?php echo $nom_cliente_det; ?></TD>
        <TD width="25%"><b>Canal (solo para empresas): </b><?php echo $nom_canal_venta_det; ?></TD>
        </TR>
        <TR>
        <TD><b>Local o Punto de venta: </b><?php echo $nom_local_det; ?></TD>
        <TD><b>Ciudad: </b><?php echo $nom_ciudad_det; ?></TD>
        <TD><b>Item inicial: </b><?php echo $nom_item_ini_det; ?></TD>
        <TD><b>Item final: </b><?php echo $nom_item_fin_det; ?></TD>
        </TR>
        <TR>
        <TD><b>Familia: </b><?php echo $nom_familia_det; ?></TD>
        <TD><b>Sub Familia: </b><?php echo $nom_subfamilia_det; ?></TD>
        <TD><b>Grupo: </b><?php echo $nom_grupo_det; ?></TD>
        <TD><b>Sub Grupo: </b><?php echo $nom_subgrupo_det; ?></TD>
        </TR>
        <TR>
        <TD><b>Almacen: </b><?php echo $nom_almacen_det; ?></TD>
        <TD><b>Ordenar Detalle por: </b><?php echo $nom_orden_det; ?></TD>
        <TD><b>De Fecha: </b><?php echo $fechaini_det; ?> <b>a Fecha: </b><?php echo $fechafin_det; ?></TD>
        <TD><b>Expresado en: </b><?php echo $nom_otra_moneda_det; ?></TD>
        </TR>
        <TR>
        <TD colspan="4"><b>Contexto: </b><?php echo $nom_contexto_det; ?></TD>
        </TR>  
    </table>

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

    <table border="1" align="center" cellpadding="2" width="100%">
    <tr>
    <TD><b>Tipo de Cambio:</b></TD>
    </tr><tr>
    <TD align="center"><a href="../../ModuleAccounting/VoucherExchangeRate/index.php?pkyExchangeRate=<?php echo $ultimo_tc; ?>&user=<?php echo $usuario; ?>&token=<?php echo $token; ?>" title="Tipo de Cambio"><?php echo $ultimo_tc; ?></a> (<?php echo $tc_fecha; ?> - <?php echo $tc_hora; ?>)<br/ >
        <table border="0" cellpadding="2" align="center">
            <?php
            for($m = 1; $m <= $fin_m; $m++) {
            printf("<tr><td align='left'>1 %s</td>", $ma_tc [$m][1]);//moneda base
            printf("<td align='center'>%s</td>", ' = ');//letra a
            printf("<td align='left'>%s %s</td>", number_format($ma_tc [$m][3],$n_tipocambio), $ma_tc [$m][2]);//moneda target y tasa
            //printf("<td align='right'>%s</td></tr>", number_format($ma_tc [$m][3],$n_tipocambio));//tasa
            }
            ?>
        </table>
    </TD>
    </tr>
	</table>
    
</td>
-->
</tr>
</table>
<br />
<table border="1" align="center" cellpadding="2" width="100%">
 	<thead>
    <TR> 
    <TD align="center"><b>Cod.</b></TD>
    <TD align="center"><b>Nro. Doc.</b></TD>
    <TD align="center" width="65"><b>Fecha</b></TD>
    <TD align="center"><b>Cliente</b></TD>
    <TD align="center"><b>Almac&eacute;n</b></TD>
    <TD align="center"><b>Punto o Local de venta</b></TD>
    <TD align="center"><b>Ciudad</b></TD>
    <TD align="center"><b>Factura</b></TD>
    <TD align="center"><b>Cant.</b></TD>
    <!--<TD align="center"><b>Precio</b></TD>-->
    <TD align="center"><b>Desc. Parc. %</b></TD>
    <TD align="center"><b>Desc. Glob. %</b></TD>
    <?php if($comisiones_det == 0) { ?>
    <TD align="center"><b>Total Ventas</b></TD>
    <TD align="center"><b>Comisi&oacute;n Ventas</b></TD>
    <?php } ?>
    <?php if($comisiones_det == 1) { ?>
    <TD align="center"><b>Total Ventas</b></TD>
    <TD align="center"><b>Cobros %</b></TD>
    <TD align="center"><b>Total Cobros</b></TD>
    <TD align="center"><b>Comisi&oacute;n Cobros</b></TD>
    <TD align="center"><b>Saldo Pendiente</b></TD>
    <?php } ?>
    </TR>
    </thead>
    <tbody class="marcafila">

    <?php
	$suma_cant = 0;
	//$suma_precio = 0;
	$suma_venta = 0;
	$suma_com_venta = 0;
	$suma_cobro = 0;
	$suma_com_cobro = 0;
	$suma_saldo = 0;
	$color = 2;
	for ($i = 1; $i <= $fin_det; $i++) { //for
		$codigo_nota_venta = $matriz_det[$i][1];
		$codigo_factura = $matriz_det[$i][16];
		$codigo_cxc = $matriz_det[$i][20];
		//$codigo_asiento = $matriz_det[$i][19];
		if($color % 2 == 0) { //if color
		printf("<tr BGCOLOR='#e2e7fa'><td align='center'><a href='../../ModuleSales/VoucherSalesNote/index.php?pkySalesNote=$codigo_nota_venta&user=$usuario_det&token=$token' title='Nota de Venta'>%s</a></td>", $matriz_det[$i][1]);//cod nota de venta
		} else {
		printf("<tr><td align='center'><a href='../../ModuleSales/VoucherSalesNote/index.php?pkySalesNote=$codigo_nota_venta&user=$usuario_det&token=$token' title='Nota de Venta'>%s</a></td>", $matriz_det[$i][1]);//cod nota de venta
		}//fin if color
		printf("<td align='right'>%s</td>", $matriz_det[$i][2]);//nro doc
		printf("<td align='right'>%s</td>", $matriz_det[$i][3]);//fecha
		printf("<td>%s</td>", $matriz_det[$i][4]);//cliente
		printf("<td>%s</td>", $matriz_det[$i][5]);//almacen
		printf("<td>%s</td>", $matriz_det[$i][6]);//punto o local de venta
		printf("<td>%s</td>", $matriz_det[$i][7]);//ciudad
		
		printf("<td align='right'><a href='../../ModuleTaxes/VoucherSalesLedger/index.php?pkySalesLedger=$codigo_factura&sheetSize=$formato_factura&head=$muestra_cabecera&user=$usuario_det&token=$token' title='Factura'>%s</a>-%s</td>", $matriz_det[$i][16], $matriz_det[$i][17]);//factura
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][8], $n_cantidad));//cantidad
		//printf("<td align='right'>%s</td>", number_format($matriz_det[$i][9], $n_decimales));//precio
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][10], $n_descuento));//descuento parcial
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][15], $n_descuento));//descuento global
		if($comisiones_det == 0) { //if com 0
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][11], $n_decimales));//total ventas
		printf("<td align='right'>%s</td></tr>", number_format($matriz_det[$i][12], $n_decimales));//comision ventas
		} //fin if com 0
		if($comisiones_det == 1) { //if com 1
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][11], $n_decimales));//total ventas
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][18], $n_descuento));//porcentaje cobros
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][13], $n_decimales));//total cobros
		printf("<td align='right'>%s</td>", number_format($matriz_det[$i][14], $n_decimales));//comision cobros
		//$saldo_pendiente = $matriz_det[$i][11] - $matriz_det[$i][13];
		printf("<td align='right'><a href='../../ModuleCollections/ReportAccountsReceivable/report.php?rep=6&tipo=2&cod_cxc=$codigo_cxc&empresa=1&otra_moneda_det=$otra_moneda_det&usuario_det=$usuario_det&token=$token' title='Kardex de Cuenta por Cobrar'>%s</a></td></tr>", number_format($matriz_det[$i][19], $n_decimales));//saldo pendiente
		} //fin if com1
		
		$suma_cant = $suma_cant + $matriz_det[$i][8];
		//$suma_precio = $suma_precio + $matriz_det[$i][9];
		$suma_venta = $suma_venta + $matriz_det[$i][11];
		$suma_com_venta = $suma_com_venta + $matriz_det[$i][12];
		$suma_cobro = $suma_cobro + $matriz_det[$i][13];
		$suma_com_cobro = $suma_com_cobro + $matriz_det[$i][14];
		$suma_saldo = $suma_saldo + $matriz_det[$i][19];
		
		$color++;
		}//fin for

?>
<tr BGCOLOR='#ffcccc'><td colspan="8"><b>Total</b></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_cant,$n_cantidad)); ?></td>
<!--<td align="right"><?php printf("<b>%s</b>", number_format($suma_precio,$n_decimales)); ?></td>-->
<td>&nbsp;</td>
<td>&nbsp;</td>
<?php if($comisiones_det == 0) { ?>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_venta,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_com_venta,$n_decimales)); ?></td>
<?php } ?>
<?php if($comisiones_det == 1) { ?>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_venta,$n_decimales)); ?></td>
<td>&nbsp;</td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_cobro,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_com_cobro,$n_decimales)); ?></td>
<td align="right"><?php printf("<b>%s</b>", number_format($suma_saldo,$n_decimales)); ?></td>
<?php } ?>
</tr>

<?php  
	
} //fin if 3
?>
</tbody>
</table>

</body>
</html>
