<?
  /**********************
  * VERSIONE 3.0 READY
  **********************/
  include ("../common/public.php") ;
    
	// print_r($_GET + $_POST) ; // exit ;
  /***************************************************************************
  * where-list
  ***************************************************************************/    
  $selAge    = $agente ;
  $selCli    = $cliente ;
	$selFor    = $fornitore ;
	$selPro    = $prodotto ;
	
	$ordinaper = $colToSort ;

	$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($selAge) and ($selAge != 'tutti'))
    $where_age = " A.IdAgenti = $selAge" ;
	
  if ($profilo == 3)
    $where_age .= " and A.idagenti != 4 and A.idagenti != 5 and A.idagenti != 9 and A.idagenti != 10" ;
  else
    $where_age .= " and 1=1" ;

	/*************************
  * filtro Cliente
  **************************/  
  if (isset($selCli) and ($selCli != 'tutti'))
		$where_cli = " C.idclienti = '$selCli'" ;

	/*************************
  * filtro Fornitori
  **************************/  
  if (isset($selFor) and ($selFor != 'tutti'))
	{
		if ($selFor != $idsarzi)
		{
			$where_for_no_sarzi = "F.IdFornitori  = $selFor" ;
			$where_for_sarzi    = "F.IdFornitori  = -21" ;
		}
		else
		{
			$where_for_no_sarzi = "F.IdFornitori  = -21" ;
			$where_for_sarzi    = "F.IdFornitori  = $selFor" ;
		}
	}

	/*************************
  * filtro Prodotto
  **************************/  
  if (isset($selPro) and ($selPro != ''))
    $where_pro = " D.DescDettaglio like '%$selPro%'" ;
	
	/***************************************
	* controllo la colonna da ordinare
	***************************************/
	switch($ordinaper)
	{
		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 prodotti totali
			$order_by = "NumBottTot $direction, NomeProdotto" ;
			break ;

		case 5:			// numero prodotti venduti
			$order_by = "NumBottVend $direction, NomeProdotto" ;
			break ;
		
		case 6:			// numero prodotti in sconto merce
			$order_by = "NumBottSM $direction, NomeProdotto" ;
			break ;
		
		case 7:			// Percentuale Sconto Merce
			$order_by = "PercSM $direction, NomeProdotto" ;
			break ;
	}
		
  $qclipro = "
          select
							TT.IdAgente,
							TT.NomeAgente,
							TT.IdCliente,
							TT.NomeCliente,
							TT.IdFornitore,
							TT.NomeFornitore,
							TT.NomeProdotto,
							-- TT.IdProdotto,
							sum(TT.NumBottVend) + sum(TT.NumBottSM) as NumBottTot,
							sum(TT.NumBottVend) as NumBottVend,
 							sum(TT.NumBottSM) as NumBottSM,
							(sum(TT.NumBottSM) / (sum(TT.NumBottVend) + sum(TT.NumBottSM))) * 100  as PercSM
          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,
                  D.DescDettaglio                 as NomeProdotto,
									-- P.idprodotti                   as IdProdotto,
									(D.NumCartoni * D.ConfezioniDa) as NumBottVend,
									0                               as NumBottSM
							from
									agenti A
										inner join ordini O on O.idagente = A.idagenti and
                                           O.Anno = $anno
											inner join clienti C on C.idclienti = O.idcliente and
											                          $where_cli
											inner join fornitori F on F.idfornitori = O.idfornitore and
																									$where_for_no_sarzi
											inner join dettagli D on D.idordine = O.idordini and
                                               D.Anno = $anno          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 NumBottVend,
									0                               as NumBottSM
							from
									agenti A
										inner join ordini O on O.idagente = A.idagenti and
                                           O.Anno = $anno
											inner join clienti C on C.idclienti = O.idcliente and
											                          $where_cli
											inner join fornitori F on F.idfornitori = O.idfornitore and
																									$where_for_sarzi
											inner join dettagli D on D.idordine = O.idordini and
                                               D.Anno = $anno
												inner join sarzi 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,
                  D.DescDettaglio                 as NomeProdotto,
									-- P.idprodotti                    as IdProdotto,
									0                               as NumBottVend,
									(D.NumCartoni * D.ConfezioniDa) as NumBottSM
							from
									agenti A
										inner join ordini O on O.idagente = A.idagenti and
                                           O.Anno = $anno
											inner join clienti C on C.idclienti = O.idcliente and
											                             $where_cli
											inner join fornitori F on F.idfornitori = O.idfornitore and
																											 $where_for_no_sarzi
											inner join scontomerce D on D.idordine = O.idordini and
                                                  D.Anno = $anno          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,
									0                               as NumBottVend,
									(D.NumCartoni * D.ConfezioniDa) as NumBottSM
							from
									agenti A
										inner join ordini O on O.idagente = A.idagenti and
                                           O.Anno = $anno
											inner join clienti C on C.idclienti = O.idcliente and
											                             $where_cli
											inner join fornitori F on F.idfornitori = O.idfornitore and
																											 $where_for_sarzi
											inner join scontomerce D on D.idordine = O.idordini and
                                                  D.Anno = $anno
												inner join sarzi 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 (0, $qclipro) ;
  list($nrowsdati, $dati)  = $db->RecuperaDati($qclipro) ;
  
  $arrParGen = array() ;
	$arrParGen = RecuperaParametriGenerali($arrParGen, $tparametri) ;
	
	$nome         = $arrParGen['NomeAgenzia'] ;
	$sede         = "Sede operativa: " . $arrParGen['IndirizzoAgenzia'] ;
  $datisede     = "Telefono: " . $arrParGen['TelefonoAgenzia'] . 
                  " Fax: " . $arrParGen['FaxAgenzia'] .
                  " -- P.I. " . $arrParGen['PartitaIvaAgenzia'] ;
  $emailsede    = "Email: " . $arrParGen['MailAgenzia'] ;
  
  $titolo = "Lista Clienti / Bottiglie Acquistate in data " . Date("d/m/Y") ;
?>
<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" type="text/css" href="../common/mystyle.css" >
<body  topmargin="0" vlink="#336633" marginheight="0" bgcolor="#FFFFFF" leftmargin="0" link="#006600" marginwidth="0">

<table width="1000" border="0" cellpadding="2" cellspacing="1">
  <tr>
    <td width="40%"><IMG SRC="../images/Logo1.gif" WIDTH="200" HEIGHT="80" BORDER="0" ALT=""></td>
		<td width="60%" align="right"><font class="ordine8it"><? echo $sede ?><BR>
                                  <font class="ordine8it"><? echo $datisede ?><BR>
                                  <font class="ordine8it"><? echo $emailsede ?><BR></td>
  </tr>
  <tr>
    <td colspan="2"><hr color="black" size="1"></td> 
  </tr>
</table>
 
<table width="1000" border="0" cellpadding="0" cellspacing="0">
  <tr>
    <td align="center" colspan="2"><font class="ordine10"><? echo $titolo ?><BR><BR></td>
  </tr>
</table>

<table width="1000" border="0" cellpadding="2" cellspacing="1">
	<tr bgcolor="black">
    <td class="etidettagl8in" align="center">Agente</td>
    <td class="etidettagl8in" align="center">Cliente</td>
    <td class="etidettagl8in" align="center">Fornitore</td>
    <td class="etidettagl8in" align="center">Prodotto</td>
    <td class="etidettagl8in" align="center">Totale</td>
    <td class="etidettagl8in" align="center">Venduto</td>
    <td class="etidettagl8in" align="center">SM</td>
    <td class="etidettagl8in" align="center">%SM</td>
	</tr>
<?  
  for ($i = 0; $i < $nrowsdati ; $i++ )
  {
    $totale   += $dati['NumBottVend'][$i] + $dati['NumBottSM'][$i] ;
    $totaleV  += $dati['NumBottVend'][$i] ;
    $totaleSM += $dati['NumBottSM'][$i] ;
?>
  <tr>  
    <td class="sit_econ_st8"><? echo htmlspecialchars($dati['NomeAgente'][$i]) ?></td>
    <td class="sit_econ_st8"><? echo htmlspecialchars($dati['NomeCliente'][$i]) ?></td>
    <td class="sit_econ_st8"><? echo htmlspecialchars($dati['NomeFornitore'][$i]) ?></td>
    <td class="sit_econ_st8"><? echo htmlspecialchars($dati['NomeProdotto'][$i]) ?></td>
    <td class="sit_econ_st8" align="right"><? echo nfl($dati['NumBottTot'][$i]) ?></td>
    <td class="sit_econ_st8" align="right"><? echo nfl($dati['NumBottVend'][$i]) ?></td>
    <td class="sit_econ_st8" align="right"><? echo nfl($dati['NumBottSM'][$i]) ?></td>
    <td class="sit_econ_st8_lastcell" align="right"><? echo nfp($dati['PercSM'][$i]) ?></td>
  </tr>
<?
  }
  
?>
	<tr bgcolor="black">
    <td class="etidettagl8in_R" colspan="4">Totali:&nbsp;</td>
    <td class="etidettagl8in_R"><? echo nfl($totale) ?></td>
    <td class="etidettagl8in_R"><? echo nfl($totaleV) ?></td>
    <td class="etidettagl8in_R"><? echo nfl($totaleSM) ?></td>
    <td class="etidettagl8in_R"><? echo nfp(($totaleSM / $totale) * 100) ?></td>
	</tr>
</table>
</body>
</html>