<?
  include ("../common/public.php") ;		
	
	$where_cli   = "1=1" ;
	$where_age   = "1=1" ;
	$where_for_no_sarzi = "F.IdFornitori != $idsarzi" ;
	$where_for_sarzi    = "F.IdFornitori  = $idsarzi" ;
	$where_pro   = "1=1" ;

	/*************************
  * filtro Agente
  **************************/  
  if (isset($agente) and ($agente != 'tutti'))
    $where_age = " A.IdAgenti = $agente" ;
	
	/*************************
  * filtro Cliente
  **************************/  
  if (isset($cliente) and ($cliente != 'tutti'))
		$where_cli = " C.idclienti = '$cliente'" ;

	/*************************
  * filtro Fornitori
  **************************/  
  if (isset($fornitore) and ($fornitore != 'tutti'))
	{
		if ($fornitore != $idsarzi)
		{
			$where_for_no_sarzi = "F.IdFornitori  = $fornitore" ;
			$where_for_sarzi    = "F.IdFornitori  = -21" ;
		}
		else
		{
			$where_for_no_sarzi = "F.IdFornitori  = -21" ;
			$where_for_sarzi    = "F.IdFornitori  = $fornitore" ;
		}
	}

	/*************************
  * filtro Prodotto
  **************************/  
  if (isset($prodotto) and ($prodotto != ''))
    $where_pro = " P.nome like '%$prodotto%'" ;

	/***************************************
	* controllo la colonna da ordinare
	***************************************/
	switch($colToSort)
	{
		case 0:			// agente
			$order_by = "NomeAgente $direction, NomeProdotto" ;
			break ;
		
		case 1:			// cliente
			$order_by = "NomeCliente $direction, NomeProdotto" ;
			break ;

		case 2:			// fornitore
			$order_by = "NomeFornitore $direction, NomeProdotto" ;
			break ;

		case 3:			// prodotto
			$order_by = "NomeProdotto $direction, NomeCliente" ;
			break ;

		case 4:			// numero bottiglie
			$order_by = "NumBott $direction, NomeProdotto" ;
			break ;
	}

	$qclipro = "
          select
							TT.IdAgente,
							TT.NomeAgente,
							TT.IdCliente,
							TT.NomeCliente,
							TT.IdFornitore,
							TT.NomeFornitore,
							TT.NomeProdotto,
							TT.IdProdotto,
							sum(TT.NumBott) as NumBott
          from
						(	select
									A.idagenti                      as IdAgente,
									A.nome                          as NomeAgente,
									C.idclienti                     as IdCliente,
									C.nome                          as NomeCliente,
									F.idfornitori                   as IdFornitore,
									F.nome                          as NomeFornitore,
									CONCAT(P.nome, ' ', P.Annata)   as NomeProdotto,
									P.idprodotti                    as IdProdotto,
									(D.NumCartoni * D.ConfezioniDa) as NumBott
							from
									$tagenti A
										inner join $tordini O on O.idagente = A.idagenti
											inner join $tclienti C on C.idclienti = O.idcliente and
											                          $where_cli
											inner join $tfornitori F on F.idfornitori = O.idfornitore and
																									$where_for_no_sarzi
											inner join $tdettagli D on D.idordine = O.idordini
												inner join $tprodotti P on P.idprodotti = D.idprodotto and
												                             $where_pro
							where
									$where_age
						union all
							select
									A.idagenti                      as IdAgente,
									A.nome                          as NomeAgente,
									C.idclienti                     as IdCliente,
									C.nome                          as NomeCliente,
									F.idfornitori                   as IdFornitore,
									F.nome                          as NomeFornitore,
									P.nome                          as NomeProdotto,
									P.idprodotti                    as IdProdotto,
									(D.NumCartoni * D.ConfezioniDa) as NumBott
							from
									$tagenti A
										inner join $tordini O on O.idagente = A.idagenti
											inner join $tclienti C on C.idclienti = O.idcliente and
											                          $where_cli
											inner join $tfornitori F on F.idfornitori = O.idfornitore and
																									$where_for_sarzi
											inner join $tdettagli D on D.idordine = O.idordini
												inner join $tsarzi P on P.idprodotti = SUBSTRING(D.idprodotto,3) and
												                        $where_pro
							where
									$where_age
						union all
							select
									A.idagenti                      as IdAgente,
									A.nome                          as NomeAgente,
									C.idclienti                     as IdCliente,
									C.nome                          as NomeCliente,
									F.idfornitori                   as IdFornitore,
									F.nome                          as NomeFornitore,
									CONCAT(P.nome, ' ', P.Annata)   as NomeProdotto,
									P.idprodotti                    as IdProdotto,
									(D.NumCartoni * D.ConfezioniDa) as NumBott
							from
									$tagenti A
										inner join $tordini O on O.idagente = A.idagenti
											inner join $tclienti C on C.idclienti = O.idcliente and
											                             $where_cli
											inner join $tfornitori F on F.idfornitori = O.idfornitore and
																											 $where_for_no_sarzi
											inner join $tscontomerce D on D.idordine = O.idordini
												inner join $tprodotti P on P.idprodotti = D.idprodotto and
												                             $where_pro
							where
									$where_age
						union all
							select
									A.idagenti                      as IdAgente,
									A.nome                          as NomeAgente,
									C.idclienti                     as IdCliente,
									C.nome                          as NomeCliente,
									F.idfornitori                   as IdFornitore,
									F.nome                          as NomeFornitore,
									P.nome                          as NomeProdotto,
									P.idprodotti                    as IdProdotto,
									(D.NumCartoni * D.ConfezioniDa) as NumBott
							from
									$tagenti A
										inner join $tordini O on O.idagente = A.idagenti
											inner join $tclienti C on C.idclienti = O.idcliente and
											                             $where_cli
											inner join $tfornitori F on F.idfornitori = O.idfornitore and
																											 $where_for_sarzi
											inner join $tscontomerce D on D.idordine = O.idordini
												inner join $tsarzi P on P.idprodotti = SUBSTRING(D.idprodotto,3) and
												                             $where_pro
							where
									$where_age		
						) as TT
					group by
							TT.IdAgente,
							TT.NomeAgente,
							TT.IdCliente,
							TT.NomeCliente,
							TT.IdFornitore,
							TT.NomeFornitore,
							TT.NomeProdotto,
							TT.IdProdotto
					order by
							$order_by
						 " ;

	debug (1, $qclipro) ;
	list($nrowsdati, $dati)  = $db->RecuperaDati($qclipro) ;
	// print_r($dati) ; exit ;

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
 <HEAD>
	<title><? echo TITLE ?></title>
	<meta name="ROBOTS" content="INDEX,NOFOLLOW">
	<meta name="keywords" content="">
	<meta name="description" content="DaliWine - Ordini Online">
	<meta http-equiv="PICS-Label" content=''>
	<meta http-equiv="expires" content="Fri, 5 Apr 1996 23:59:59 GMT">
 </HEAD>
<link rel="stylesheet" href="../common/mystyle.css" type="text/css">
<style>
.destra {
	text-align:right ;
}
.sinistra {
	text-align:left ;
}
</style>
<body  topmargin="0" vlink="#336633" marginheight="0" bgcolor="#FFFFFF" leftmargin="0" link="#006600" marginwidth="0">
<table width="740" border="0" cellpadding="0" cellspacing="0">
  <tr>
    <td width="100%"><IMG SRC="../images/Logo1.gif" WIDTH="200" HEIGHT="80" BORDER=0 ALT=""></td>
  </tr>
  <tr>
    <td><hr color="black" size="1"></td> 
  </tr>
  <tr>
    <td align="center" width="100%"><font class="ordine10"><? echo $titolo ?></font></td>
  </tr>
	<tr>
    <td align="center">
      <table border="0" width="100%" cellspacing="2" cellpadding="0">
        <tr bgcolor="black">
          <td class="etidettaglin" width="14%" align="center">Agente</td>
          <td class="etidettaglin" width="27%" align="center">Cliente</td>
          <td class="etidettaglin" width="35%" align="center">Fornitore</td>
          <td class="etidettaglin" width="19%" align="center">Prodotto</td>
          <td class="etidettaglin" align="center">Bott</td>
				</tr>
<?
	$totBott = 0 ;
	for ($i=0; $i<$nrowsdati; $i++)
	{
?>
        <tr>  
          <td class="datiordini8 sinistra"><? echo $dati['NomeAgente'][$i] ?></td>
          <td class="datiordini8 sinistra"><? echo $dati['NomeCliente'][$i] ?></td>
          <td class="datiordini8 sinistra"><? echo $dati['NomeFornitore'][$i] ?></td>
          <td class="datiordini8 sinistra"><? echo $dati['NomeProdotto'][$i] ?></td>
          <td class="datiordini8 destra"><? echo $dati['NumBott'][$i] ?></td>
				</tr>
<?
	$totBott += $dati['NumBott'][$i] ;
	}
?>
        <tr bgcolor="black">
          <td class="etidettaglin destra" colspan="5">Totale Bottiglie:&nbsp;<? echo $totBott ?></td>
				</tr>
		</table></td>
	</tr>
</table>

</BODY>
</HTML>