<?php
session_start();

if( $_SESSION["user"] == "" || $_SESSION["passwd"] == "" ) {
   mysqli_close($conn);
   header("Location: index.php");
}

$admin = $_SESSION["admin"];

include_once('db_config.php');

$Year     = isset($_POST['year']) ? $_POST["year"]  : "";
$Month  = isset($_POST['month']) ? $_POST['month'] : "";
$Fecha = "";
$Total_Llamados = 0;
$Total_Minutos = 0;
$Importe_Total = 0;
$Total_Dia_Llamados = 0;
$Total_Dia_Minutos = 0;
$Importe_Dia_Total = 0;

$YS2015 = "";
$YS2016 = "";

if( empty($Year) ) {        
   if( $_SESSION["Year"] != "" ) $Year = $_SESSION["Year"];
   else {
      $Year = date("Y");
   }
}

if( empty($Month) ) {       
   if( $_SESSION["Month"] != "" ) $Month = $_SESSION["Month"];
   else {
     $Month = date("n");
   }
}
$_SESSION["Year"] = $Year;
$_SESSION["Month"] = $Month;

$End_Year = $Year;
if( ( $End_Month = intval( $Month ) + 1 ) > 12 ) {
   $End_Month = 1;
   ++$End_Year;
}


$Start = sprintf('%04d-%02d-01', $Year, $Month );
$End_Date = sprintf('%04d-%02d-01', $End_Year, $End_Month );
//$Start = $Year . "-" . $Month . "-01";
//$End   = $Year . "-" . $Month . "-31";

$sm = array("", "", "", "", "", "", "", "", "", "", "", "");
$sm[$Month-1] = "selected";

if( $Year == "2015")	$YS2015 = "selected";
if( $Year == "2016")	$YS2016 = "selected";
if( $Year == "2017")	$YS2017 = "selected";

if( $admin == true )
   $Movistar = 0.9225;
else $Movistar = 1.476; 
$Movistar = 1.476; 
$Personal = 1.476;
$Personal_New = 1.575;
$Claro =  0.738;
$Nextel = 1.107;
include 'header.php';	

function cnvSegundosHoras($tiempo_en_segundos) {
   $horas = floor($tiempo_en_segundos / 3600);
   $minutos = floor(($tiempo_en_segundos - ($horas * 3600)) / 60);
   $segundos = $tiempo_en_segundos - ($horas * 3600) - ($minutos * 60);
   if( $horas > 0 )
      return( sprintf("%d:%02d:%02d", $horas, $minutos, $segundos) ); 
   return( sprintf("%d:%02d", $minutos, $segundos) ); 
}
?>
<style>
.dropdown-content {
  overflow-x: hidden;
}
</style>
   <div id="content">
   <div class="container" >
      <div style="height: 15px;"></div>
      <div class="col l9 m12 s12">
         <div class="page-content">
            <div style="height:20px;"></div>
            <form id="asterisco" action="diario.php" method="post"> 
            <div class="row">
            <div class="input-field col m3">
    				<select name="month">
 <?php
  					echo "<option value='' disabled>Seleccione el Mes</option>";				
      					echo "<option value='1' " . $sm[0] . ">Enero</option>";
      					echo "<option value='2' " . $sm[1] . ">Febrero</option>";
      					echo "<option value='3' " . $sm[2] . ">Marzo</option>";
      					echo "<option value='4' " . $sm[3] . ">Abril</option>";
      					echo "<option value='5' " . $sm[4] . ">Mayo</option>";
      					echo "<option value='6' " . $sm[5] . ">Junio</option>";
      					echo "<option value='7' " . $sm[6] . ">Julio</option>";
      					echo "<option value='8' " . $sm[7] . ">Agosto</option>";
      					echo "<option value='9' " . $sm[8] . ">Septiembre</option>";
      					echo "<option value='10' " . $sm[9] . ">Octubre</option>";
      					echo "<option value='11' " . $sm[10] . ">Noviembre</option>";
      					echo "<option value='12' " . $sm[11] . ">Diciembre</option>";
?>      					
    					</select>
               <label>Mes</label>
  				</div>			
  				<div class="input-field col  m3">
    					<select  name="year">
    					<option value="" disabled selected>Seleccione el A&ntilde;o</option>
<?php

      					echo "<option value='2015' " . $YS2015 . ">2015</option>";
      					echo "<option value='2016' " . $YS2016 . ">2016</option>";
      					echo "<option value='2017' " . $YS2017 . ">2017</option>";
?>     					
     					</select>
               <label>A&ntilde;o</label>
  				</div>		
  			 	<button class="btn waves-effect waves-light btn col s1" style="margin-top:25px; margin-left: 30px;" type="submit" name="action" >Go!</button>
  				</row>	
  				</form>
  				 <div style="height:70px;"></div>
  			</div>	
  			<table class="highlight">
        				<thead>
          				<tr class="red lighten-3">
              				<th class="col s1" data-field="id">Fecha</th>
<?php
               if( $admin == true ) {
              					echo "<th class='col s2' data-field='name'>Dnis</th>"; 
              				}
?>              				
             				<th class="col s4" data-field="price">Aplicacion</th>
             				<th class="col s1 right-align" data-field="calls" class="col s1 right-align">Llamados</th>
             				<th class="col s2 right-align" data-field="minutes" class="col s2 right-align">Minutos</th>
             				<th class="col s2 right-align" data-field="promedio" class="col s2 right-align">Promedio</th>
              				<th class="col s2 right-align" data-field="price" class="col s2 right-align">Importe</th>
          				</tr>
        				</thead>
        				<tbody>

<script type="text/javascript" charset="utf-8">
   var Month = "";
   var Year = "";
   function btnClick() {

   }	
</script>
<?php
   $sql = "select  sql_no_cache dnis, date(fecha) as dia, dnis, aplicacion, count(id) as llamados, sum(duracion) as segundos, sum(duracion_facturada) as minutos, sum( duracion_facturada ) * Tarifa as importe from tickets where fecha >= '" . $Start . "' and fecha<'" . $End_Date . "' and dnis  >= '1151720084' and dnis <= '1151720087' and fecha >= '2016-02-01' group by dia desc, dnis";
   $result = $conn->query($sql);

   if ($result->num_rows > 0) {
    		// output data of each row
      $Fecha = "";
      $Total_Llamados = 0;
      $Total_Minutos = 0;
      $Importe_Total = 0;
      $Total_Dia_Llamados = 0;
      $Total_Dia_Minutos = 0;
      $Importe_Dia_Total = 0;
      $segundos = 0;

    		while($row = $result->fetch_assoc()) {

         $date = date_create($row["dia"]);
         $formatted = date_format($date, 'd-m-Y');
         
         // Tarfio segun operadoras
         $segundos = $row["segundos"];
         if( $segundos >= 10 && $segundos < 60 ) $segundos = 60;
         else if( $segundos < 10 ) $segundos = 0; 

         if( $row["llamados"] == 0 ) $Promedio = 0;  
         else $Promedio = $segundos / $row["llamados"];
         $Sec = cnvSegundosHoras($Promedio);

    		$Valor = 0;

         if( $date >= "2016-04-07 00:00:00" ) $valor_personal = $Personal_New;
         else $valor_personal = $Personal;

    		switch( $row["dnis"]) {
    			case '1151720061': $Valor = $Movistar; break;
    			case '1151720062': $Valor = $valor_personal; break; 
    			case '1151720063': $Valor = $Claro; break;
    			case '1151720064': $Valor = $Nextel; break;
    		}
    		$Importe = $segundos  /60 * $Valor;

    		if( $row["dia"]  != $Fecha ) {
         if( $Fecha != "" ) {
         		echo "<tr>";
         	 		echo "<td class='col s1' ></td>";
            If( $admin == true ) {
            				echo "<td class='col s2' ></td>";
            }            		
            			echo "<td class='col s4' ></td>";
           	 		echo "<td class='col s1  orange lighten-2 right-align'>" . number_format ( $Total_Dia_Llamados , 0, ',' ,  '.' ) . "</td>";
          			echo "<td class='col s2  orange lighten-2 right-align'>" . number_format (  $Total_Dia_Minutos , 0, ',' ,  '.' ) . "</td>";
         				echo "<td class='col s2  orange lighten-2 right-align'></td>";
         				echo "<td class='col s2  orange lighten-2 right-align'>" . number_format ( $Importe_Dia_Total , 2, ',' ,  '.' ) . "</td>";
        				echo "</tr>";
         }        					
 			$FechaDisplay = $Fecha = $row["dia"];
  			$FechaDisplay = $formatted;
         $Total_Dia_Llamados = 0;
         $Total_Dia_Minutos = 0;
         $Importe_Dia_Total = 0;
    		}    				
    		else $FechaDisplay = "";
    		echo "<tr>";
         		echo "<td class='col s1' >" . $FechaDisplay . "</td>";

         If( $admin == true ) {
               		echo "<td class='col s2' >" . $row["dnis"] ."</td>";
         }            		
//			else echo "<td class='col s2' style='visibility: hidden;'>" . $row["dnis"] ."</td>";
            		echo "<td class='col s4' >" . $row["aplicacion"] . "</td>";
            		echo "<td class='col s1 right-align'>" . number_format ( $row["llamados"] , 0, ',' ,  '.' ) . "</td>";
            		echo "<td class='col s2 right-align'>" . number_format ( $segundos / 60  , 0, ',' ,  '.' ) . "</td>";
            		echo "<td class='col s2 right-align'>" .  $Sec . " </td>";
            		echo "<td class='col s2 right-align'>" . number_format ( $Importe , 2, ',' ,  '.' ) . "</td>";
        			echo "</tr>";
        			$Total_Llamados += $row["llamados"];
        			$Total_Minutos += $segundos / 60 ;
        			$Importe_Total += $Importe;

        			$Total_Dia_Llamados += $row["llamados"];
        			$Total_Dia_Minutos += $segundos / 60 ;
        			$Importe_Dia_Total += $Importe;
    		}
   } 
   mysqli_close($conn);

   if( $Total_Llamados == 0 ) $Promedio = 0;
   	else $Promedio = $Total_Minutos / $Total_Llamados;

   	$Horas = intval($Promedio / 60);
   	$Promedio_Min = intval( $Promedio - $Horas );
   	$Promedio_Sec = ($Promedio - $Horas - $Promedio_Min ) * 60;
   	if( $Horas > 0 )
   		$Sec = sprintf("%d:%02d:%02d", $Horas, $Promedio_Min, $Promedio_Sec);
   	else $Sec = sprintf("%d:%02d",$Promedio_Min, $Promedio_Sec);
   	$Sec = sprintf("%d:%02d", $Promedio_Min, $Promedio_Sec);

   			echo "<tfoot>";
   	echo "<tr>";
   echo "<td class='col s1' ></td>";
   If( $admin == true ) {
      echo "<td class='col s2' ></td>";
   }            		
        	echo "<td class='col s4' ></td>";
        	echo "<td class='col s1  orange lighten-2 right-align'>" . number_format ( $Total_Dia_Llamados , 0, ',' ,  '.' ) . "</td>";
     		echo "<td class='col s2  orange lighten-2 right-align'>" . number_format (  $Total_Dia_Minutos , 0, ',' ,  '.' ) . "</td>";
      		echo "<td class='col s2  orange lighten-2 right-align'></td>";
      		echo "<td class='col s2  orange lighten-2 right-align'>" . number_format ( $Importe_Dia_Total , 2, ',' ,  '.' ) . "</td>";
   		echo "</tr>";

   		echo "<tr style='color:white; font-weight: 500;'>";
      			echo "<td class='col s1'></td>";
      If( $admin == true ) {
    					echo "<td class='col s2'></td>";
      }
      			echo "<td class='col s4'></td>";
      			echo "<td class='col s1 right-align red darken-4'>" . number_format ( $Total_Llamados, 0, ',' ,  '.' ) . "</td>";
      			echo "<td class='col s2 right-align red darken-4'>" . number_format ( $Total_Minutos, 0, ',' ,  '.' ) . "</td>";
      			echo "<td class='col s2 right-align red darken-4'>" . $Sec . "</td>";
      			echo "<td class='col s2 right-align red darken-4'>" . number_format ( $Importe_Total, 2, ',' ,  '.' ) . "</td>";
    			echo "</tr>";
    			echo "</tfoot>";
?>			
        	</tbody>
   		</table>
   	</div>
      <div style="height:100px;"></div>
   </div>
   </div>
   <script type="text/javascript" src="js/jquery.min.js"></script>
    	<script type="text/javascript" src="js/jquery.form.js"></script> 
   <script type="text/javascript" src="js/materialize.min.js"></script>
<script>
   $(document).ready(function() {

            	// bind 'myForm' and provide a simple callback function 
            	/*
            	$('#asterisco').ajaxForm(function() { 
                		alert("Thank you for your comment!"; 
            	}); */ 

      $('select').material_select();

      $('#month').on('change', function() {
  			Month = this.value;
  			console.log( this.value )
      });		
      $('#year').on('change', function() {
  			Year = this.value;
  			console.log( this.value )
      });		

      console.log( "Ready")
   });		
</script>
</body>
</html>
