<?php
  include("conn.php");
  //Recupero parametros
  $vDataPeriodos     = $_POST['xDataPeriodos'];
  $vBases            = $_POST['xBases'];
  $vMostar           = $_POST['xMostar'];
  $vCero             = $_POST['xCero'];
  $vOrden            = $_POST['xOrden'];
  
  //Armo el filtro de periodos;
  $sqlPeriodos = "";
  if ($vDataPeriodos != '') {
    $xDataPeriodos = json_decode($vDataPeriodos);
    $sqlPeriodos = " AND (";
    $sqlFiltoFecha = " AND (";
    foreach ($xDataPeriodos as $value) {
      if ($sqlPeriodos <> " AND (") {
        $sqlPeriodos .= " OR ";
        $sqlFiltoFecha .= " OR ";
      }
      $auxDesde = $value->desde;
      $auxHasta = $value->hasta;
      $sqlPeriodos .= "(Z.FECHA BETWEEN '" . $value->desde . "' AND '" . $value->hasta . "')";
      $sqlFiltoFecha .= " X.FECHA < '" . $value->desde . "' ";
    }
    $sqlPeriodos .= ")";
    $sqlFiltoFecha .= ")";
  }
  //Armo el filtro de BASES, obtengo el nombre de la base y lleno la variable para el los filtros
  $sqlBases = "SELECT DISTINCT E.BASE, E.DESCRI, E.NOMBRE_BASE 
               FROM (SELECT XB.BASE, XB.DESCRI, XB.NOMBRE_BASE 
                     FROM BASES xB 
                          INNER JOIN USUARIOSBASES XUB 
                              INNER JOIN USUARIOS XU 
                              ON XUB.ID_USUARIO = XU.ID_USUARIO 
                              AND XU.USUARIO = 'PIXO_WEB' 
                          ON XB.BASE = XUB.BASE) E 
                    INNER JOIN USUARIOSBASES UB 
                        INNER JOIN USUARIOS U 
                        ON UB.ID_USUARIO = U.ID_USUARIO 
                    ON E.BASE = UB.BASE 
               WHERE 1=1 AND E.BASE = ".$vBases."
               ORDER BY E.DESCRI";

  $RsEmpresas = $DBSegu->Execute($sqlBases);
  if (!$RsEmpresas) {
    $jsondata['success'] = false;
    $jsondata['message'] = 'Ha ocurrido un error durante la consulta. Ref. #V01.02';
    header('Content-type: application/json; charset=utf-8');
    echo json_encode($jsondata);
    die();  
  }

  $sqlBase = $RsEmpresas->fields["NOMBRE_BASE"].".dbo.";

  //Armo el filtro por saldo cero
  $sqlSaldoCero = "";
  if ($vCero == 1) {
    $sqlSaldoCero = " and (isnull(AUXI_SALDO.IMPORTE,0) + isnull(AUXI_MOV.DEBE,0) - isnull(AUXI_MOV.HABER,0)) <> 0 "; 
  }
  
  //Armo orden 
  if ($vOrden == 'codigo') { 
    $sqlOrden = 'CC.CLI_PRV';
  }
  else {
    $sqlOrden = 'CC.RAZON_SOCIAL';
  }

  //veo que mostrar
  switch ($vMostar) {
    case 0:
      $sqlMostrar = '';
    break;
    case 1:
      $sqlMostrar = ' and not CC.TIPO_CLI is null '; 
    break;
    case 2:
      $sqlMostrar = ' and not CC.TIPO_PRV is null ';
    break;
    default:
      $sqlMostrar = '';
    break;
  }
  
  $resu = '';

  $sqlConsulta = "select CC.CLI_PRV, CC.RAZON_SOCIAL, isnull(AUXI_SALDO.IMPORTE,cast(0 as money)) as ANTERIOR,
                         isnull(AUXI_MOV.DEBE,cast(0 as money)) as DEBE, 
                         isnull(AUXI_MOV.HABER,cast(0 as money)) as HABER,
                         isnull(AUXI_SALDO.IMPORTE,cast(0 as money)) + isnull(AUXI_MOV.DEBE,cast(0 as money)) - isnull(AUXI_MOV.HABER,cast(0 as money)) AS FINAL
                  from ".$sqlBase."ccCLIPRV CC
                       left outer join (select X.CLI_PRV, sum(X.IMPORTE * ".$_SESSION['BaseMadre']."ValorCredito(TMOV.CREDITO)) as IMPORTE
                                        from ".$sqlBase."ccCTACTE X
                                             inner join ".$sqlBase."ccTIPOMOV TMOV
                                             on TMOV.TIPO_MOV = X.TIPO_MOV
                                        where 1=1 ".$sqlFiltoFecha."
                                        Group by X.CLI_PRV) AUXI_SALDO
                       on AUXI_SALDO.CLI_PRV = CC.CLI_PRV
                       left outer join (select Z.CLI_PRV,
                                              sum(case when isnull(ZMOV.CREDITO,0) = 0 then Z.IMPORTE else cast(0 as money) end) as DEBE,
                                              sum(case when isnull(ZMOV.CREDITO,0) = 1 then Z.IMPORTE else cast(0 as money) end) as HABER
                                       from ".$sqlBase."ccCTACTE Z
                                            inner join ".$sqlBase."ccTIPOMOV ZMOV
                                            on ZMOV.TIPO_MOV = Z.TIPO_MOV
                                       where 1=1 ".$sqlPeriodos."
                                       group by Z.CLI_PRV) AUXI_MOV
                      on AUXI_MOV.CLI_PRV = CC.CLI_PRV
                  where CC.SUB_CTA = '000' ".$sqlMostrar."
                    and (isnull(AUXI_SALDO.IMPORTE,cast(0 as money)) <> 0 or 
                         isnull(AUXI_MOV.DEBE,cast(0 as money)) <> 0 or 
                         isnull(AUXI_MOV.HABER,cast(0 as money)) <> 0)
                  ".$sqlSaldoCero." 
                  order by " . $sqlOrden;
  $RsConsulta = $DBSegu->Execute($sqlConsulta);
  if (!$RsConsulta) {
    $jsondata['success'] = false;
    $jsondata['message'] = 'Ha ocurrido un error durante la consulta. Ref. #V01.03'.$sqlConsulta;
    header('Content-type: application/json; charset=utf-8');
    echo json_encode($jsondata);
    die();  
  }

  $resu = ' <input type="hidden" id="auxDesde" value="'.$auxDesde.'">
            <input type="hidden" id="auxHasta" value="'.$auxHasta.'">
            <div class="row">
              <table id="tablaResultado" class="display table table-striped table-bordered sticky-header" style="width:100%">
                <thead style="background-color: #db5916; color: aliceblue;">
                  <tr>
                    <th class="thalign tituloshead">CUENTA</th>
                    <th class="thalign tituloshead">RAZÓN SOCIAL</th>
                    <th class="thalign tituloshead">INICIAL</th>
                    <th class="thalign tituloshead">DEBE</th>
                    <th class="thalign tituloshead">HABER</th>
                    <th class="thalign tituloshead">SALDO FINAL</th>
                    <th class="thalign tituloshead"></th>
                  </tr>
                </thead>
                <tbody>';

  while (!$RsConsulta->EOF) {
    $auxAnterior = $RsConsulta->fields['ANTERIOR'];
    $auxDebe = $RsConsulta->fields['DEBE'];
    $auxHaber = $RsConsulta->fields['HABER'];
    $auxFinal = $RsConsulta->fields['FINAL'];
    $nuevosaldo = (float)$auxAnterior - (float)$auxDebe + (float)$auxHaber;
    $resu .= '      <tr>
                      <td class="numerales">' . $RsConsulta->fields['CLI_PRV'] . '</td>
                      <td class="numerales" id="razon'.$RsConsulta->fields['CLI_PRV'].'">' . utf8_encode($RsConsulta->fields['RAZON_SOCIAL']) . '</td>
                      <td class="numerales" id="inicial'.$RsConsulta->fields['CLI_PRV'].'" data-valor="'.$auxAnterior.'">$' . number_format($auxAnterior, 2, ',', '.') . '</td>
                      <td class="numerales">$' . number_format($auxDebe, 2, ',', '.') . '</td>
                      <td class="numerales">$' . number_format($auxHaber, 2, ',', '.') . '</td>
                      <td class="numerales">$' . number_format($auxFinal, 2, ',', '.') . '</td>
                      <td class="numerales">
                        <div class="btn-group">
                          <button type="button" class="btn btn-xxs dropdown-toggle" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                            <span class="glyphicon glyphicon-menu-hamburger" aria-hidden="true"></span> <!--<span class="caret"></span>-->
                          </button>
                          <ul class="dropdown-menu dropdown-menu-right">
                            <li><a href="#" title="Ver Resumen de Cuenta" onclick="verResumen(\''.$RsConsulta->fields['CLI_PRV'].'\', '.$vBases.')">Ver Resumen de Cuenta</a></li>
                            <li><a href="#" title="Ver Composición de Deuda" onclick="verDeuda(\''.$RsConsulta->fields['CLI_PRV'].'\', '.$vBases.')">Ver Composición de Deuda</a></li>
                          </ul>
                        </div>                      
                      </td>
                    </tr>';  

    $RsConsulta->MoveNext();
  }
  //pinto el total del último subtotal
  $resu .= '      </tbody>';

  $resu .= '    <tfoot>
                </tfoot>
              </div>
            </table>';


  $resu .= '</div>';

  $jsondata = array();
  $jsondata['success'] = true;
  $jsondata['message'] = $resu;
  
  header('Content-type: application/json; charset=utf-8');
  echo json_encode($jsondata);
  die();  
?>