<?php
/* Busqueda de registros de la tabla VTA_Documento redefinidos por el usuario. */
class VTA_DocumentoFN extends BaseVTA_DocumentoFN
{
    public function select_one()
    {
        $a_Object = parent::select();
        if( count( $a_Object ) == 1 )
        {
            return $a_Object[0];
        }
    }

    public function buildConsult()
    {
        $s_SQLQuery = parent::buildConsult();
        if( !$this->getEstado() )
        {
            //$s_SQLQuery .= " AND Estado IN ( 'AN', 'CE' )  ";
        }
        return $s_SQLQuery;
    }
    
    /**
     * Recupero el Id + 1
     *
     * @return int
     */
    public static function recuperarId()
    {
        $o_utilGenerateId = new utilGenerateId();
        $o_utilGenerateId->setIdCompania( myUser::getUser()->getIdBusiness() );
        $o_utilGenerateId->setTipoCorrelativo( "VTA" );
        $o_utilGenerateId->setSerie( "CAB" );
        $s_Id = $o_utilGenerateId->generateStringId();
        return $s_Id;
    }

    public function selectTotales()
    {
        if( !$this->getEstado() )
        {
            $this->setEstado( VTA_DocumentoBN::ESTADO_CERRADO );
        }

        $s_SQLQuery = "SELECT 
                    SUM( IF(IdTipoDocumento='NC', ( IF(Estado='CE',MontoAfectoMN,0.00) * -1 ), IF(Estado='CE',MontoAfectoMN,0.00) ) ) MontoAfecto,
                    SUM( IF(IdTipoDocumento='NC', ( IF(Estado='CE',MontoNoAfectoMN,0.00) * -1 ), IF(Estado='CE',MontoNoAfectoMN,0.00) ) ) MontoNoAfecto,
                    SUM( IF(IdTipoDocumento='NC', ( IF(Estado='CE',MontoImpuestoMN,0.00) * -1 ), IF(Estado='CE',MontoImpuestoMN,0.00) ) ) MontoImpuestos,
                    SUM( IF(IdTipoDocumento='NC', ( IF(Estado='CE',MontoTotalMN,0.00) * -1 ), IF(Estado='CE',MontoTotalMN,0.00) ) ) MontoTotal
                  ";
        $s_SQLQuery .= " FROM VTA_Documento WHERE ";
        $s_SQLQuery .= $this->buildConsult();
        $s_SQLQuery .= ";";
        //pr( $s_SQLQuery );
        $o_ResultSet = Conexion::getConexion()->prepareStatement( $s_SQLQuery )->executeQuery();
        $o_ResultSet->next();
        $a_Data = $o_ResultSet->getRow();
        return $a_Data;
    }

    public static function recuperarFiltroSerie()
    {
        $s_Where = "";
        if( myUser::getUser()->getIdSucursal() )
        {
            $s_Where = " AND Sucursal='".myUser::getUser()->getIdSucursal()."' ";
        }
        $s_Sql = "SELECT DocumentoSerie
                FROM VTA_Documento
                WHERE IdTipoDocumento IN ( SELECT IdTipoDocumento FROM VTA_TipoDocumento WHERE EsFiscalFlag='S' ) AND Estado='CE' $s_Where
                GROUP BY DocumentoSerie;";
        $o_ResultSet = Conexion::getConexion()->prepareStatement( $s_Sql )->executeQuery();
        $a_Series = array();
        while( $o_ResultSet->next() ) 
        {
            $a_SQLRow = $o_ResultSet->getRow();
            $a_Series[$a_SQLRow['DocumentoSerie']] = $a_SQLRow['DocumentoSerie'];
        }
        return $a_Series;
    }

    /**
     * @param $s_NumeroDocumento
     * @return VTA_DocumentoBN
     * @throws SQLException
     */
    public static function selectByDocumentNumber( $s_NumeroDocumento )
    {
        $s_Sql = "SELECT *
                    FROM VTA_Documento
                    WHERE NumeroDocumento = '$s_NumeroDocumento'  "; //AND Estado IN ('CE','AN', 'PR')
        $o_ResultSet = Conexion::getConexion()->prepareStatement( $s_Sql )->executeQuery();
        $o_ResultSet->next();
        $a_Row = $o_ResultSet->getRow();
        $o_VTA_DocumentoFN = new VTA_DocumentoFN();
        $o_VTA_DocumentoBN = $o_VTA_DocumentoFN->insertObjectBN( $a_Row );
        return $o_VTA_DocumentoBN;
    }

    public static function selectBySerieNumber( $s_TypeSerieNumero )
    {
        $s_Sql = "SELECT *
                FROM VTA_Documento
                WHERE CONCAT( DocumentoCod, '', DocumentoSerie, '-', DocumentoNumero ) = '$s_TypeSerieNumero' AND Estado IN ('CE','AN') and IdAlmacen='".GSS_SucursalFN::recuperarIdAlmacen( myUser::getUser()->getIdSucursal() )."' and ClienteRUC<>'00000000'";
        $o_DBConnect = Conexion::getConexion();
        $o_Statement = $o_DBConnect->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $i_NumRows   = $o_ResultSet->getRecordCount();
        if( $i_NumRows <= 0 )
        {
            throw new Exception( "No se encontro el Documento." );
        }
        $o_ResultSet->next();
        $a_SQLRow = $o_ResultSet->getRow(); //devuele array

        $o_VTA_DocumentoFN = new VTA_DocumentoFN();
        $o_VTA_DocumentoBN = $o_VTA_DocumentoFN->insertObjectBN( $a_SQLRow );
        return $o_VTA_DocumentoBN;
    }

    public static function buscarNroDocumentoxNotaCredito( $s_NumeroDocumento )
    {
        $o_VTA_DocumentoFN = new VTA_DocumentoFN();
        $o_VTA_DocumentoFN->setNotaCreditoDocumento( $s_NumeroDocumento );
        $a_VTA_Documento = $o_VTA_DocumentoFN->select();
        if( $a_VTA_Documento )
        {
            $o_VTA_DocumentoBN = $a_VTA_Documento[0];
            return $o_VTA_DocumentoBN->getDocumentoTipoSerieNumero();
        }
        return "";
    }

    public static function findByDocumentoParentCod( $s_OrdenNum )
    {
        $s_Sql = "SELECT * FROM VTA_Documento where DocumentoParentCod='$s_OrdenNum';";
        $o_DBConnect = Conexion::getConexion();
        $o_Statement = $o_DBConnect->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $i_NumRows   = $o_ResultSet->getRecordCount();
        if( $i_NumRows > 0 )
        {
            $o_ResultSet->next();
            $a_SQLRow = $o_ResultSet->getRow(); //devuele array
            $o_VTA_DocumentoFN = new VTA_DocumentoFN();
            $o_VTA_DocumentoBN = $o_VTA_DocumentoFN->insertObjectBN( $a_SQLRow );
            return $o_VTA_DocumentoBN;
        }
        return null;
    }

    public static function getAlmacenAsignado( $IdTipoDocumento, $IdCompania, $NumeroDocumento )
    {
        $s_Sql = "SELECT IdAlmacen FROM VTA_Documento where IdTipoDocumento='$IdTipoDocumento' and IdCompania='$IdCompania' and NumeroDocumento='$NumeroDocumento';";
        $o_DBConnect = Conexion::getConexion();
        $o_Statement = $o_DBConnect->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $o_ResultSet->next();
        $a_Row = $o_ResultSet->getRow(); //devuele array
        
        //$o_SQLResult = mysql_query( $s_Sql, $o_DBConnect->getResource() );
        //$a_Row = mysql_fetch_array( $o_SQLResult );
        return $a_Row['IdAlmacen'];
    }

    //METODOS PARA REPORTES
    public static function getArrayTopVendedoresPorDosPeriodos( $s_Periodo1, $s_Periodo2 )
    {
        $s_Sql = "
                    SELECT
                      D.Vendedor,
                      ROUND( A.MontoTotal, 2 ) PeriodoUno,
                      ROUND( B.MontoTotal, 2 ) PeriodoDos,
                      ROUND( ( ( A.MontoTotal / B.MontoTotal ) * 100 ) - 100, 2 ) PorcVariacion
                    FROM VTA_Documento D
                      LEFT JOIN
                      (
                        SELECT Vendedor, SUM( MontoSubTotalMN ) MontoTotal
                        FROM VTA_Documento
                        where 1=1 and Periodo='$s_Periodo1' and Estado='CE'
                        group by Vendedor
                      ) A ON A.Vendedor=D.Vendedor
                      LEFT JOIN
                      (
                        SELECT Vendedor, SUM( MontoSubTotalMN ) MontoTotal
                        FROM VTA_Documento
                        where 1=1 and Periodo='$s_Periodo2' and Estado='CE'
                        group by Vendedor
                      ) B ON B.Vendedor=D.Vendedor
                      left join SEG_Usuario U ON D.Vendedor=U.IdUsuario
                    where 1=1
                        AND U.DeshabilitadoFlg='N'
                    group by D.Vendedor
                    ORDER BY 2 DESC;
        ";
        //pr( $s_Sql );
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $i_NumRows = $o_ResultSet->getRecordCount();
        $a_Data = array();
        if( $i_NumRows > 0 )
        {
            //while ( $a_SQLRow = mysql_fetch_array( $o_SQLResult, MYSQL_ASSOC ) )
            while( $o_ResultSet->next() ) 
            {
                $a_SQLRow = $o_ResultSet->getRow(); //devuele array
                $a_Data[$a_SQLRow['Vendedor']] = array
                ( 
                    'PeriodoUno'    => $a_SQLRow['PeriodoUno'], 
                    'PeriodoDos'    => $a_SQLRow['PeriodoDos'], 
                    'PorcVariacion' => $a_SQLRow['PorcVariacion'] 
                );
            }
        }
        return $a_Data;
    }

    public static function getArrayVentasPorFamilia( $s_Periodo )
    {
        $s_Sql = "
                    SELECT IdLinea, Descripcion, ROUND( SUM( MontoTotal ), 2 ) MontoTotal
                    FROM
                    (SELECT C.Estado, C.Periodo, I.IdLinea IdLinea, L.Descripcion Descripcion, D.MontoSubTotalMN MontoTotal -- IF(C.MonedaDocumento = 'EX', (C.MontoSubTotal * C.TipodeCambio) , C.MontoSubTotal) MontoTotal
                        FROM VTA_Documento C
                        LEFT JOIN VTA_DocumentoDetalle D ON C.IdTipoDocumento=D.IdTipoDocumento AND C.IdCompania=D.IdCompania AND  C.NumeroDocumento=D.NumeroDocumento
                        LEFT JOIN ALM_ItemMast I ON D.ItemCodigo=I.IdItem
                        LEFT JOIN MST_ClaseLinea L ON I.IdLinea=L.IdLinea
                        where 1=1
                        and C.Periodo='$s_Periodo'
                        and C.Estado='CE') XX
                        
                    GROUP BY IdLinea
                    ORDER BY 3 DESC;
                ";
        //pr( $s_Sql );
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $a_Data = array();
        while( $o_ResultSet->next() ) 
        {
            $a_SQLRow = $o_ResultSet->getRow(); //devuele array
            $a_Data[] = $a_SQLRow;
        }
        return $a_Data;
    }

    public static function getArrayVentasPorMes( $a_Parametros9 = array() )
    {
        $s_Where = "";
        if( isset( $a_Parametros9['PeriodoIni'] ) )
        {
            $s_Where .= " AND Periodo>='".$a_Parametros9['PeriodoIni']."' ";
        }
        if( isset( $a_Parametros9['PeriodoFin'] ) )
        {
            $s_Where .= " AND Periodo<='".$a_Parametros9['PeriodoFin']."' ";
        }
        $s_Sql = "
                    SELECT
                    C.Periodo, LEFT(C.Periodo, 4) Anio, RIGHT(C.Periodo, 2) Mes, SUM( ( IF( MonedaDocumento='EX', ( ROUND(MontoSubTotal*TipodeCambio,2) ), MontoSubTotal ) ) ) MontoTotal
                    FROM VTA_Documento C
                    WHERE 1=1
                    and C.Estado='CE'
                    $s_Where
                    GROUP BY C.Periodo
                    ORDER BY C.Periodo;

                ";
        //pr( $s_Sql );
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $a_Data = array();
        while( $o_ResultSet->next() ) 
        {
            $a_SQLRow = $o_ResultSet->getRow(); //devuele array
            $a_Data[] = $a_SQLRow;
        }
        return $a_Data;
    }

    public static function getArrayVentasPorDia( $a_Parametros10 = array() )
    {
        $s_Where = "";
        if( isset( $a_Parametros10['PeriodoIni'] ) )
        {
            $s_Where .= " AND FechaDocumento>='".$a_Parametros10['PeriodoIni']." 00:00:00' ";
        }
        if( isset( $a_Parametros10['PeriodoFin'] ) )
        {
            $s_Where .= " AND FechaDocumento<='".$a_Parametros10['PeriodoFin']." 23:59:00' ";
        }
        //pr( $s_Where );
        $s_Sql = "
        SELECT
        C.Periodo, LEFT(C.Periodo, 4) Anio, RIGHT(C.Periodo, 2) Mes,
        DATE_FORMAT(FechaDocumento,'%Y-%m-%d') FechaDoc,
        SUM( ( IF( MonedaDocumento='EX', ( ROUND(MontoSubTotal*TipodeCambio,2) ), MontoTotal ) ) ) MontoTotal
        FROM VTA_Documento C
        WHERE 1=1
        $s_Where
        and C.Estado='CE'
        GROUP BY C.Periodo, DATE_FORMAT(FechaDocumento,'%Y-%m-%d')
        ORDER BY C.Periodo, FechaDocumento;
        ";
        //pr( $s_Sql );
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $a_Data = array();
        while( $o_ResultSet->next() )
        {
            $a_SQLRow = $o_ResultSet->getRow(); //devuele array
            $a_Data[] = $a_SQLRow;
        }
        return $a_Data;
    }

    public static function getDataVentaMargen( $s_Periodo )
    {
        $o_VTA_DocumentoFN = new VTA_DocumentoFN();
        $o_VTA_DocumentoFN->setEstado( VTA_DocumentoBN::ESTADO_CERRADO );
        $o_VTA_DocumentoFN->setPeriodo( $s_Periodo );
        $a_VTA_Documento = $o_VTA_DocumentoFN->select();

        $f_SumaCostoAlmacenTotal = 0;
        $f_SumaTotalMargen = 0;
        $f_SumaTotal       = 0;

        foreach( $a_VTA_Documento as $o_VTA_DocumentoBN )
        {
            $f_SumaCostoAlmacen     = 0;
            $a_VTA_DocumentoDetalle = $o_VTA_DocumentoBN->getArrayObjectDetalles();
            $s_Moneda = $o_VTA_DocumentoBN->getMonedaDocumento();
            $f_TipoCambio = $o_VTA_DocumentoBN->getTipodeCambio();
            foreach ( $a_VTA_DocumentoDetalle as $o_VTA_DocumentoDetalleBN )
            {
                $f_CostoUnitVenta = $o_VTA_DocumentoDetalleBN->getCostoUnitarioMN(); //SE RECUPERA EL COSTO EN MONEDA NACIONAL

                if( $f_CostoUnitVenta > $o_VTA_DocumentoDetalleBN->getPrecioUnitarioMN() )
                {
                    $f_Resta = $o_VTA_DocumentoDetalleBN->getPrecioUnitarioMN() - $o_VTA_DocumentoDetalleBN->getCostoUnitarioMN();

                    $o_MST_ListaPrecioDetalleBN = MST_ListaPrecioDetalleFN::buscarPrecioGeneral( $o_VTA_DocumentoDetalleBN->getItemCodigo() );
                    $f_CostoUnitVenta = $o_MST_ListaPrecioDetalleBN->getPrecioCosto();
                    if( $f_CostoUnitVenta > $o_VTA_DocumentoDetalleBN->getPrecioUnitarioMN() )
                    {
                        $f_CostoUnitVenta = ( $o_VTA_DocumentoDetalleBN->getPrecioUnitarioMN() - 0.01 );
                    }
                }

                $f_SumaCostoAlmacen = $f_SumaCostoAlmacen + ( $f_CostoUnitVenta * $o_VTA_DocumentoDetalleBN->getCantidadPedida() );
            }

            $f_TotalUtil = 0;
            if( $f_SumaCostoAlmacen )
            {
                $f_SumaCostoAlmacenTotal = $f_SumaCostoAlmacenTotal + $f_SumaCostoAlmacen;
                if( $o_VTA_DocumentoBN->getMontoSubTotalMN() )
                {
                    $f_TotalUtil = utilMath::fnround( ( ( $o_VTA_DocumentoBN->getMontoSubTotalMN() - $f_SumaCostoAlmacen ) / $o_VTA_DocumentoBN->getMontoSubTotalMN() ) * 100, 2 );
                }

            }
            $f_Margen          = round( $o_VTA_DocumentoBN->getMontoSubTotalMN() - $f_SumaCostoAlmacen, 2 );
            $f_SumaTotalMargen = round( $f_SumaTotalMargen + $f_Margen, 2 );

            $f_SumaTotal = $f_SumaTotal + $o_VTA_DocumentoBN->getMontoSubTotalMN();
        }

        $a_Data = $o_VTA_DocumentoFN->selectTotales();
        $f_MargenPor = 0;
        if( $f_SumaTotalMargen )
        {
            if( floatval( $a_Data['MontoAfecto'] ) )
            {
                $f_MargenPor = round( $f_SumaTotalMargen / $a_Data['MontoAfecto'] * 100, 2 );
            }

        }

        return array( "VentaTotal" => $f_SumaTotal, "CostoTotal" => $f_SumaCostoAlmacenTotal, "MargenTotal" => $f_SumaTotalMargen, "MargenPor" => $f_MargenPor, "VetnaTotalIGV" => $a_Data['MontoTotal'] );
    }

    public static function getDataControlGastosCobranzas( $s_Periodo )
    {
        $o_VTA_DocumentoFN = new VTA_DocumentoFN();
        $o_VTA_DocumentoFN->setEstado( VTA_DocumentoBN::ESTADO_CERRADO );
        $o_VTA_DocumentoFN->setPeriodo( $s_Periodo );
        $a_VTA_Documento = $o_VTA_DocumentoFN->select();

        $f_SumaTotalSinIGV    = 0;
        $f_SumaTotalConIGV    = 0;
        $f_SumaTotalPagado    = 0;
        $f_SumaTotalPendiente = 0;

        $f_TotalDocSol   = 0;
        $f_TotalDocDol   = 0;
        $f_TotalPagoSol  = 0;
        $f_TotalPagoDol  = 0;
        $f_TotalSaldoSol = 0;
        $f_TotalSaldoDol = 0;
        foreach ( $a_VTA_Documento as $i_Key => $o_VTA_DocumentoBN )
        {
            $f_SumaTotalSinIGV    = $f_SumaTotalSinIGV + $o_VTA_DocumentoBN->getMontoSubTotalMN();
            $f_SumaTotalConIGV    = $f_SumaTotalConIGV + $o_VTA_DocumentoBN->getMontoTotalMN();
            $f_SumaTotalPagado    = $f_SumaTotalPagado + $o_VTA_DocumentoBN->getMontoPagadoMN();
            $f_SumaTotalPendiente = $f_SumaTotalPendiente + $o_VTA_DocumentoBN->getMontoSaldoMN();

            /*$f_TotalDocSol   = $f_TotalDocSol + ( is_numeric( $o_VTA_DocumentoBN->getMontoTotalSiEsSoles() ) ? $o_VTA_DocumentoBN->getMontoTotalSiEsSoles() : 0 );
            $f_TotalDocDol   = $f_TotalDocDol + ( is_numeric( $o_VTA_DocumentoBN->getMontoTotalSiEsDolares() ) ? $o_VTA_DocumentoBN->getMontoTotalSiEsDolares() : 0 );
            $f_TotalPagoSol  = $f_TotalPagoSol + ( is_numeric( $o_VTA_DocumentoBN->getMontoPagadoSiEsSoles() ) ? $o_VTA_DocumentoBN->getMontoPagadoSiEsSoles() : 0 );
            $f_TotalPagoDol  = $f_TotalPagoDol + ( is_numeric( $o_VTA_DocumentoBN->getMontoPagadoSiEsDolares() ) ? $o_VTA_DocumentoBN->getMontoPagadoSiEsDolares() : 0 );
            $f_TotalSaldoSol = $f_TotalSaldoSol + ( is_numeric( $o_VTA_DocumentoBN->getMontoSaldoSiEsSoles() ) ? $o_VTA_DocumentoBN->getMontoSaldoSiEsSoles() : 0 );
            $f_TotalSaldoDol = $f_TotalSaldoDol + ( is_numeric( $o_VTA_DocumentoBN->getMontoSaldoSiEsDolares() ) ? $o_VTA_DocumentoBN->getMontoSaldoSiEsDolares() : 0 );*/
        }

        /*if( $f_TotalDocSol != $f_TotalPagoSol )
        {
            $f_SumaTotalPagado = ( $f_TotalPagoSol / 1.18 );
        }
        else
        {
            $f_SumaTotalPagado = $f_SumaTotalSinIGV;
        }*/

        //$f_SumaTotalPendiente = $f_SumaTotalSinIGV - $f_SumaTotalPagado;

        return array
        (
            "SumaTotalSinIGV" => $f_SumaTotalSinIGV, "SumaTotalConIGV" => $f_SumaTotalConIGV, "SumaTotalPagado" => $f_SumaTotalPagado, "SumaTotalPendiente" => $f_SumaTotalPendiente
        );
    }


    public static function getVentasdeMes()
    {
        $MesActual = date( "m" );
        $AnioActual = date( "Y" );
        $s_IdCompania = myUser::getUser()->getIdBusiness();
        $s_Where = "AND A.IdCompania = ".$s_IdCompania;
        $s_Where .= " AND A.Periodo= '$AnioActual$MesActual' " ;
        $s_Where .= " AND p.IdCompania = ".$s_IdCompania;
        $s_Where .= " AND B.IdCompania = ".$s_IdCompania;

        //pr( $s_Where );
        /*
        $s_Sql = "
        SELECT
        ROUND(SUM( ( IF( A.MonedaDocumento='EX', ( A.MontoSubTotal*A.TipodeCambio ), A.MontoSubTotal ) ) ),2) VentaTotal,
        ROUND(SUM(C.PrecioCosto*B.CantidadPedida),2) CostoTotal,
        ROUND(SUM( ( IF( A.MonedaDocumento='EX', ( A.MontoSubTotal*A.TipodeCambio ), A.MontoSubTotal ) ) ) - SUM(C.PrecioCosto*B.CantidadPedida),2) MargenTotal
        FROM VTA_Documento A
        INNER JOIN VTA_DocumentoDetalle B
        ON A.NumeroDocumento = B.NumeroDocumento
        INNER JOIN ALM_ItemMast C
        ON B.ItemCOdigo = C.IdItem
        WHERE 1=1
        and A.Estado='CE'
        $s_Where
        ";
        */
        $s_Sql = "
        SELECT
            SUM( ( IF( MonedaDocumento='EX', ( MontoSubTotal*TipodeCambio ), MontoSubTotal ) ) ) VentaTotal,
            SUM(PrecioCostoTotal) as CostoTotal,
            SUM( ( IF( MonedaDocumento='EX', ( MontoSubTotal*TipodeCambio ), MontoSubTotal ) ) ) - SUM(PrecioCostoTotal) MargenTotal
        FROM
        (
            SELECT
                A.IdTipoDocumento, A.NumeroDocumento, A.Periodo,
                A.MonedaDocumento, ROUND(A.MontoSubTotal,2) MontoSubTotal, A.TipodeCambio, ROUND(SUM(p.PrecioCosto*B.CantidadPedida),2) PrecioCostoTotal,
                ROUND(SUM( ( IF( A.MonedaDocumento='EX', ( A.MontoSubTotal*A.TipodeCambio ), A.MontoSubTotal ) ) ),2) VentaTotalSinIGV
            FROM VTA_Documento A
                INNER JOIN VTA_DocumentoDetalle B ON A.NumeroDocumento = B.NumeroDocumento
                    INNER JOIN ALM_ItemMast C ON B.ItemCOdigo = C.IdItem
                        LEFT JOIN MST_ListaPrecioDetalle p ON C.IdItem=p.IdItem and p.IdTipoPrecio=1
            WHERE 1=1
                and A.Estado='CE'
                $s_Where
            GROUP BY A.NumeroDocumento
        ) XX
        GROUP BY Periodo;
        ";

        // pr( $s_Sql );
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $a_Data = array();
        while( $o_ResultSet->next() )
        {
            $a_Data = $o_ResultSet->getRow(); //devuele array
            // $a_Data[] = $a_SQLRow;
        }
        return $a_Data;

        //pr ($a_Data);
    }

    public static function getIdsDocumentosParaAnular()
    {
        $s_Sql = "
                    SELECT
                      IdTipoDocumento, IdCompania, NumeroDocumento,
                      FechaCreacion,
                      DATE_ADD( NOW(), INTERVAL -4 HOUR ),
                      VTA_Documento.*
                    FROM VTA_Documento
                    WHERE 1=1
                     AND Estado='PR'
                     AND FechaCreacion <= DATE_ADD( NOW(), INTERVAL -4 HOUR )
                    ORDER BY FechaCreacion DESC;
        ";
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $a_Data = array();
        while( $o_ResultSet->next() ) 
        {
            $a_SQLRow = $o_ResultSet->getRow(); //devuele array
            $a_Data[] = $a_SQLRow;
        }
        return $a_Data;
    }

    public static function getArrayDataReservaParaAnular()
    {
        $s_Sql = "
                    select *
                    from ALM_ReservaItemStock
                    WHERE 1=1
                     AND FechaCreacion <= DATE_ADD( NOW(), INTERVAL -7 DAY )
                    ORDER BY FechaCreacion DESC
                ";
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $a_Data = array();
        while( $o_ResultSet->next() ) 
        {
            $a_SQLRow = $o_ResultSet->getRow(); //devuele array
            $a_Data[] = $a_SQLRow;
        }
        return $a_Data;
    }

    public static function getArrayDataCorregirItemAlmacen()
    {
        $s_Sql = "
                    select *
                    from ALM_ItemAlmacenStock
                    where 1=1
                    and StockReservado>0;
                ";
        $o_Resource = Conexion::getConexion();
        $o_Statement = $o_Resource->prepareStatement( $s_Sql );
        $o_ResultSet = $o_Statement->executeQuery();
        $a_Data = array();
        while( $o_ResultSet->next() ) 
        {
            $a_SQLRow = $o_ResultSet->getRow(); //devuele array
            $a_Data[] = $a_SQLRow;
        }
        return $a_Data;
    }
}
