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

include_once('db_config.php');

$Title = "Liquidaci&oacute;n de IVA";
$Year     = isset($_POST['year']) ? $_POST["year"]  : "";
$Month  = isset($_POST['month']) ? $_POST['month'] : "";
$Fecha = "";
$YS2011 = "";
$YS2012 = "";
$YS2013 = "";
$YS2014 = "";
$YS2015 = "";
$YS2016 = "";
$YS2017 = "";
$YS2018 = "";
$YS2019 = "";

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;

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

if( $Year == "2011")  $YS2011 = "selected";
if( $Year == "2012")  $YS2012 = "selected";
if( $Year == "2013")  $YS2013 = "selected";
if( $Year == "2014")  $YS2014 = "selected";
if( $Year == "2015")  $YS2015 = "selected";
if( $Year == "2016")  $YS2016 = "selected";
if( $Year == "2017")  $YS2017 = "selected";
if( $Year == "2018")  $YS2018 = "selected";
if( $Year == "2019")  $YS2019 = "selected";
if( $Year == "2020")  $YS2020 = "selected";
$admin = false;
include 'contableHeader.php'; 
?>
  <link type="text/css" rel="stylesheet" href="styles/styles_100.css"  media="screen,projection"/> 

<style>


.input-field {
  margin-top: 0.6rem;
  margin-bottom: 0.6rem;
  font-size: 14px !important;
  color: black;
}
.red {
   color: black !important;
}
</style>

  <link href="http://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
  <div id="content">
  <div class="container" style="width:90%" >
    <div style="height: 15px;"></div>
    <div class="col l9 m12 s12">
      <div class="page-content">
        <div style="height:20px;"></div>
        <form id="iva" action="contableIva.php" method="post"> 
        <div class="row">
         <div class='input-field col s1 right-align margin-label'></div> 
         <div class="input-field col s3">
            <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  s2">
              <select  name="year">
              <option value="" disabled selected>Seleccione el A&ntilde;o</option>
<?php
                echo "<option value='2011' " . $YS2011 . ">2011</option>";
                echo "<option value='2012' " . $YS2012 . ">2012</option>";
                echo "<option value='2013' " . $YS2013 . ">2013</option>";
                echo "<option value='2014' " . $YS2014 . ">2014</option>";
                echo "<option value='2015' " . $YS2015 . ">2015</option>";
                echo "<option value='2016' " . $YS2016 . ">2016</option>";
                echo "<option value='2017' " . $YS2017 . ">2017</option>";
                echo "<option value='2018' " . $YS2018 . ">2018</option>";
                echo "<option value='2019' " . $YS2019 . ">2019</option>";
                echo "<option value='2020' " . $YS2020 . ">2020</option>";
?>              
              </select>
              <label>A&ntilde;o</label>
         </div>    
         <button class="btn waves-effect waves-light btn col s1" style="margin-top:5px; margin-left: 30px;" type="submit" name="action" >Go!</button>
         <button class="btn waves-effect waves-light btn col s1" style="margin-top:5px; margin-left: 5px; width:120px;" onclick="printIVA(this);" id="compras" >IVA Compras</button>
         <button class="btn waves-effect waves-light btn col s1" style="margin-top:5px; margin-left: 5px; width:120px;" onclick="printIVA(this);" id="ventas" >IVA Ventas</button>
        </row>  
        </form>
        <div style="height:70px;"></div>
      </div>  <!-- page-content --> 
<style>
th {
 font-weight: normal;
 color: black;
}
</style>

<?php

  $isData = false;

  // Ventas 
//  $sql = "select sql_no_cache comprobantes.id as idc, operacion, fecha, numero, empresas.id as code_empresa, empresas.nombre, iva_105, iva_21, iva_27, iva_MT, ( iva_105 + iva_21 +iva_27 + iva_MT ) as sin_iva, iva, importe, retenciones, retenciones_iva, op_exentas, ( retenciones_iva + op_exentas + otras_retenciones ) as otras, importe from comprobantes left join empresas on empresas.id = comprobantes.cliente where year(fecha)='"  . $Year . "' and month(fecha)='" . $Month . "' and comprobantes.Borrado=0 order by fecha";
//  $sql = "select sum(retenciones) as ib, sum(Retenciones_Ganancias) as gan, sum(op_exentas+otras_retenciones) as otras, sum(iva_105) as iva105,sum(iva_21) as iva21, sum(iva_27) as iva27,sum(iva_mt) as ivamt,sum(retenciones_iva) as ret_iva,sum(iva_105+iva_21+iva_27+iva_mt) as imponible,sum((iva_105*10.5/100)+(iva_21*21/100)+(iva_27*27/100)) as total_iva, sum(iva) as iva_total, sum(iva-retenciones_iva) as debito_iva from comprobantes where (operacion=1 or operacion=6) and year(fecha)='"  . $Year . "' and month(fecha)='" . $Month . "' and comprobantes.Borrado=0 order by fecha";
  $sql = "select sum(retenciones) as ib, sum(Retenciones_Ganancias) as gan, sum(op_exentas+otras_retenciones) as otras, sum(iva_105) as iva105,sum(iva_21) as iva21, sum(iva_27) as iva27,sum(iva_mt) as ivamt,sum(retenciones_iva) as ret_iva,sum(iva_105+iva_21+iva_27+iva_mt) as imponible,sum((iva_105*10.5/100)+(iva_21*21/100)+(iva_27*27/100)) as total_iva, sum(iva) as iva_total, sum(iva-retenciones_iva) as debito_iva from comprobantes where operacion=1 and year(fecha)='"  . $Year . "' and month(fecha)='" . $Month . "' and comprobantes.Borrado=0 order by fecha";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
      $IVA105 = $row["iva105"];
      $IVA21 = $row["iva21"];
      $IVA27 = $row["iva27"];
      $IVAMT = $row["ivamt"];
      $RET_IVA = $row["ret_iva"];
      $IMPONIBLE = $row["imponible"];
      $TOTAL_IVA = $row["total_iva"];
      $DEBITO_IVA = $row["debito_iva"];
      $RET_GAN = $row["gan"];
      $RETENCIONES = $row["ib"];
      $OTRAS = $row["otras"];
    }
   }


  // Notas de Credito
  $sql = "select sum(retenciones) as ib, sum(Retenciones_Ganancias) as gan, sum(op_exentas+otras_retenciones) as otras, sum(iva_105) as iva105,sum(iva_21) as iva21, sum(iva_27) as iva27,sum(iva_mt) as ivamt,sum(retenciones_iva) as ret_iva,sum(iva_105+iva_21+iva_27+iva_mt) as imponible,sum((iva_105*10.5/100)+(iva_21*21/100)+(iva_27*27/100)) as total_iva, sum(iva) as iva_total, sum(iva-retenciones_iva) as debito_iva from comprobantes where operacion=6 and year(fecha)='"  . $Year . "' and month(fecha)='" . $Month . "' and comprobantes.Borrado=0 order by fecha";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
      $IVA105 -= $row["iva105"];
      $IVA21 -= $row["iva21"];
      $IVA27 -= $row["iva27"];
      $IVAMT -= $row["ivamt"];
      $RET_IVA -= $row["ret_iva"];
      $IMPONIBLE -= $row["imponible"];
      $TOTAL_IVA -= $row["total_iva"];
      $DEBITO_IVA -= $row["debito_iva"];
      $RET_GAN -= $row["gan"];
      $RETENCIONES -= $row["ib"];
      $OTRAS -= $row["otras"];
    }
   }


  $sql = "select sum(retenciones) as ib, sum(Retenciones_Ganancias) as gan, sum(op_exentas+otras_retenciones) as otras, sum(iva_105) as iva105,sum(iva_21) as iva21, sum(iva_27) as iva27,sum(iva_mt) as ivamt,sum(retenciones_iva) as ret_iva,sum(iva_105+iva_21+iva_27+iva_mt) as imponible,sum((iva_105*10.5/100)+(iva_21*21/100)+(iva_27*27/100)) as total_iva, sum(iva) as iva_total, sum(iva+retenciones_iva) as debito_iva from comprobantes where (operacion=2 or operacion=7 or operacion=5 or operacion = 8 or operacion = 9) and year(fecha)='"  . $Year . "' and month(fecha)='" . $Month . "' and comprobantes.Borrado=0 order by fecha";
  $sql = "select sum(retenciones) as ib, sum(Retenciones_Ganancias) as gan, sum(op_exentas+otras_retenciones) as otras, sum(case when (operacion <> 8 and operacion <> 9 ) then iva_105 else 0 end ) as iva105,sum( case when (operacion <> 8 and operacion <> 9) then iva_21 else 0 end ) as iva21, sum(case when (operacion <> 8 and operacion <> 9) then iva_27 else 0 end) as iva27,sum(case when (operacion <> 8 and operacion <> 9) then iva_mt else 0 end ) as ivamt,sum(retenciones_iva) as ret_iva,sum(case when ( operacion <> 8 and operacion <> 9 ) then iva_105+iva_21+iva_27+iva_mt else 0 end) as imponible,sum(case when (operacion <> 8 and operacion <> 9) then (iva_105*10.5/100)+(iva_21*21/100)+(iva_27*27/100) else 0 end ) as total_iva, sum(iva) as iva_total, sum(iva+retenciones_iva) as debito_iva from comprobantes where (operacion=2 or operacion=7 or operacion=5 or operacion = 8 or operacion = 9) and year(fecha)='"  . $Year . "' and month(fecha)='" . $Month . "' and comprobantes.Borrado=0 order by fecha";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
      $CIVA105 = $row["iva105"];
      $CIVA21 = $row["iva21"];
      $CIVA27 = $row["iva27"];
      $CIVAMT = $row["ivamt"];
      $CRET_IVA = $row["ret_iva"];
      $CIMPONIBLE = $row["imponible"];
      $CTOTAL_IVA = $row["total_iva"];
      $CDEBITO_IVA = $row["debito_iva"];
      $CRETENCIONES = $row["ib"];
      $COTRAS = $row["otras"];
      $CRET_GAN = $row["gan"];
    }
   }
      echo "<div class='row data'>";
      echo "<div class='input-field col s2 right-align '>";
      echo "<b>IVA DEBITO</b>";
      echo "</div>";
      echo "<div class='input-field col s3 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "<b>IVA CREDITO</b>";
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "IVA 10.5%:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $IVA105, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "IVA 10.5%:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CIVA105, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align'>";
      echo "IVA 21%:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align '>";
      echo number_format ( $IVA21, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "IVA 21%:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CIVA21, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align'>";
      echo "IVA 27%:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align '>";
      echo number_format ( $IVA27, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "IVA 27%:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CIVA27, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align'>";
      echo "IVA Monotributo:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align '>";
      echo number_format ( $IVAMT, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "IVA Monotributo:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CIVAMT, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align'>";
      echo "Retenciones IVA:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align '>";
      echo number_format ( $RET_IVA, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "Retenciones IVA:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CRET_IVA, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align'>";
      echo "Importe Imponible:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align '>";
      echo number_format ( $IMPONIBLE, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "Importe Imponible:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CIMPONIBLE, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align'>";
      echo "IVA D&eacute;bito Total:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align '>";
      echo number_format ( $TOTAL_IVA, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "Cr&eacute;dito + Retenciones:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CDEBITO_IVA, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align'>";
      echo "D&eacute;bito IVA:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align '>";
      echo number_format ( $DEBITO_IVA, 2, ',' ,  '.' );
      echo "</div>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align '>";
      echo "Cr&eacute;dito IVA:";
      echo "</div>";
      echo "<div class='input-field col s2 right-align'>";
      echo number_format ( $CTOTAL_IVA, 2, ',' ,  '.' );
      echo "</div>";
      echo "</div>";

      echo "<span style='font-size: 12pt; line-height: 12pt; border-top:15px solid #ef9a9a;'>";

      echo "<div class='row data'>";
         echo "<div style='height:40px;'></div>";
         echo "<div class='input-field col s1 right-align margin-label'></div>";
         echo "<div class='input-field col s2 right-align red lighten-3' style='margin:0; border-top:15px solid #ef9a9a;'>";
            echo "<b>D&eacute;bito IVA:</b>";
         echo "</div>";
         echo "<div class='input-field col s2 right-align red lighten-3'  style='margin:0; border-top:15px solid #ef9a9a;'><b>";
            echo number_format( $DEBITO_IVA, 2, ',' ,  '.' );
         echo "</b></div>";

         echo "<div class='input-field col s3 right-align red lighten-3' style='margin:0; border-top:15px solid #ef9a9a;'>";
            echo "<b>Cr&eacute;dito IVA:</b>";
         echo "</div>";
         echo "<div class='input-field col s2 right-align red lighten-3'  style='margin:0; border-top:15px solid #ef9a9a;'><b>";
            echo number_format ( $CTOTAL_IVA, 2, ',' ,  '.' );
         echo "</b></div>";
      echo "</div>";

      echo "<div class='row data'>";
         echo "<div class='input-field col s1 right-align margin-label'></div>";
         echo "<div class='input-field col s2 right-align red lighten-3' style='margin:0; border-top:15px solid #ef9a9a;'>";
            echo "<b>Saldo IVA:</b>";
         echo "</div>";

         echo "<div class='input-field col s2 right-align red lighten-3'  style='margin:0; border-top:15px solid #ef9a9a;'><b>";
            echo number_format ( $DEBITO_IVA - $CDEBITO_IVA, 2, ',' ,  '.' );
         echo "</b></div>";
//         echo "</div>";

//      echo "<div class='row data'>";
//        echo "<div class='input-field col s1 right-align margin-label'></div>";
         echo "<div class='input-field col s3 right-align red lighten-3' style='margin:0; border-top:15px solid #ef9a9a;'>";
            echo "<b>Retenciones IVA:</b>";
         echo "</div>";
         echo "<div class='input-field col s2 right-align red lighten-3'  style='margin:0; border-top:15px solid #ef9a9a;'><b>";
            echo number_format ( $CRET_IVA, 2, ',' ,  '.' );
         echo "</b></div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s2 right-align red lighten-3' style='margin:0; border-top:15px solid #ef9a9a;'>";
      echo "<b>Retenciones IIBB:</b>";
      echo "</div>";
      echo "<div class='input-field col s2 right-align red lighten-3'  style='margin:0; border-top:15px solid #ef9a9a;'><b>";
      echo number_format ( $CRETENCIONES, 2, ',' ,  '.' );
      echo "</b></div>";
//      echo "</div>";

//      echo "<div class='row data'>";
//      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s3 right-align red lighten-3' style='margin:0; border-top:15px solid #ef9a9a;'>";
      echo "<b>Otras Retenciones:</b>";
      echo "</div>";
      echo "<div class='input-field col s2 right-align red lighten-3'  style='margin:0; border-top:15px solid #ef9a9a;'><b>";
      echo number_format ( $COTRAS, 2, ',' ,  '.' );
      echo "</b></div>";
      echo "</div>";

      echo "<div class='row data'>";
      echo "<div class='input-field col s1 right-align margin-label'></div>";
      echo "<div class='input-field col s5 right-align red lighten-3' style='margin:0; border-top:15px solid #ef9a9a;'>";
      echo "</div>";
      echo "<div class='input-field col s4 right-align red lighten-3'  style='margin:0; border-top:15px solid #ef9a9a;'><b>";
      echo "</b></div>";
      echo "</div>";
      echo "</span>";

      echo "</div>";
    mysqli_close($conn);
?>        
   </div>   <!-- column --> 
   <div style="height:100px;"></div>
   </div>            <!-- container --> 
   </div>            <!-- content -->

   <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="materialize/1.0.0/js/materialize.min.js"></script>
   <script src="js/numeral.min.js"></script>
   <script src="js/jquery.maskedinput.min.js" type="text/javascript"></script>
<script>

   $(document).ready(function() {
         $('.dropdown-trigger').dropdown({
            "hover": true,
            "constrainWidth": false,
            "coverTrigger": false
         });


      $('.collapsible').collapsible({
           accordion : false,
           onOpenStart: function(el) { captureEmpresa(el); } 
      });
      $('select').formSelect();



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

   // Post to the provided URL with the specified parameters.
   function post(path, parameters) {
      var form = $('<form></form>');

       form.attr("method", "post");
       form.attr("action", path);
       form.attr("target", "_blank");

       $.each(parameters, function(key, value) {
           var field = $('<input></input>');
           field.attr("type", "hidden");
           field.attr("name", key);
           field.attr("value", value);
           form.append(field);
       });

       // The form needs to be a part of the document in
       // order for us to be able to submit it.
       $(document.body).append(form);
       form.submit();
   };

   function printIVA(e) {
      var y = <?php echo(json_encode($Year)); ?>;
      var m = <?php echo(json_encode($Month)); ?>;
      var inicio = "01-" + m + "-" + y;

      if( m < 10 )
         inicio = "01-0" + m + "-" + y;

      console.log( inicio );
      if( e.id == "compras" )
         post("reportContable.php", { output: 'browser', mode: '', informe: 'iva_compras', desde: inicio });

      if( e.id == "ventas" )
        post("reportContable.php", { output: 'browser', mode: '', informe: 'iva_ventas', desde: inicio });

   }

</script>
</body>
</html>

