<?php
/* Busqueda de registros de la tabla ALM_TransParaRepInv redefinidos por el usuario. */
class ALM_TransParaRepInvFN extends BaseALM_TransParaRepInvFN
{

    public static function ResumenEdadInventarioTotales( $s_Dias1 = null,  $s_Dias2 = null )
    {
        $s_Where = "";

        if( $s_Dias1 )
        {
            $s_Where .= " AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) >= $s_Dias1";
        }

        if( $s_Dias2 )
        {
            $s_Where .= " AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) <= $s_Dias2";
        }

        $s_Sql = "
SELECT
    SUM( COSTO_TOTAL ) COSTO_TOTAL, SUM( CantSaldo ) CANT_SALDO
FROM
(
    SELECT
        T2.IdItem,
        ( SELECT DescripcionCompleta from ALM_ItemMast where IdItem=T2.IdItem ) DescripcionCompleta,
        DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) DiasCalculadoOnline,
        T2.DiasCalculado,
        ( SELECT PrecioCosto from MST_ListaPrecioDetalle where IdItem=T2.IdItem AND IdTipoPrecio=1 LIMIT 1 ) COSTO_UNI,
        -- ( SELECT SUM(StockFisico) StockFisico FROM ALM_ItemAlmacenStock WHERE IdItem=T2.IdItem ) STOCK_FISICO,
        SUM(T2.CantSaldo) CantSaldo,
        ROUND( ( SELECT PrecioCosto from MST_ListaPrecioDetalle where IdItem=T2.IdItem AND IdTipoPrecio=1 LIMIT 1 ) * SUM(T2.CantSaldo), 2) COSTO_TOTAL
    FROM
    (
        SELECT
            MAX(IdTrx) IdTrx, MAX(FechaDocumento) FechaDocumento, ReferenciaTipoDocumentoOriginal, ReferenciaNumeroDocumentoOriginal
        FROM ALM_TransParaRepInv
        WHERE
            ALM_TransParaRepInv.IdCompania = 1
            AND ALM_TransParaRepInv.IdSucursal = 1
            --  AND ALM_TransParaRepInv.IdItem = '01773'
            AND 1 = 1
        GROUP BY ReferenciaTipoDocumentoOriginal, ReferenciaNumeroDocumentoOriginal, IdItem
        ORDER BY FechaDocumento DESC
    ) T1
        LEFT JOIN ALM_TransParaRepInv T2 ON T1.IdTrx=T2.IdTrx
    WHERE 1=1
        AND  T2.CantSaldo > 0
        -- AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) >= 0 AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) <= 15
        $s_Where
    GROUP BY T2.IdItem
    ORDER BY ( SELECT DescripcionCompleta from ALM_ItemMast where IdItem=T2.IdItem )
) X
        ";
        $o_ResultSet = Conexion::getConexion()->prepareStatement( $s_Sql )->executeQuery();
        $o_ResultSet->next();
        $a_SQLRow = $o_ResultSet->getRow();
        return $a_SQLRow;
    }

    public static function EdadCostoInventarioPorPeriodos( $s_Dias1,  $s_Dias2 )
    {
     
        $s_Where = "WHERE 1=1";
        
        if( isset( $s_Dias1 ) )
        {
            $s_Where .= " AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) >=". $s_Dias1;
        }
        
        if( isset( $s_Dias2 ) )
        {
            $s_Where .= " AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) <= ".$s_Dias2;
        }
        
        $s_Sql = "
        SELECT COALESCE(SUM( CostoTotal ), 0 ) PrecioCosto
        
        FROM
        (
            
            SELECT
            DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) DiasCalculadoOnline,
            T2.IdItem,
            T2.StockFisico,
            (SELECT PrecioCosto FROM ALM_ItemMast WHERE IdItem=T2.IdItem) CostoUnitario,
            ((SELECT PrecioCosto FROM ALM_ItemMast WHERE IdItem=T2.IdItem) * T2.StockFisico) CostoTotal
            FROM
            (
                SELECT
                MAX(IdTrx) IdTrx, MAX(FechaDocumento) FechaDocumento, ReferenciaTipoDocumentoOriginal, ReferenciaNumeroDocumentoOriginal, IdItem
                FROM ALM_TransParaRepInv
                WHERE
                ALM_TransParaRepInv.IdCompania = 1 AND ALM_TransParaRepInv.IdSucursal = 1
                -- AND ALM_TransParaRepInv.IdItem = '01049'
                AND 1 = 1
                GROUP BY ReferenciaTipoDocumentoOriginal, ReferenciaNumeroDocumentoOriginal, IdItem
                ORDER BY FechaDocumento DESC
                ) T1
            LEFT JOIN ALM_TransParaRepInv T2 ON T1.IdTrx=T2.IdTrx
            $s_Where AND  CantSaldo > 0
            ORDER BY FechaDocumentoOriginal
            
            ) FINAL
        ";
        //pr( $s_Sql );
        $o_ResultSet = Conexion::getConexion()->prepareStatement( $s_Sql )->executeQuery();
        $o_ResultSet->next();
        $s_PrecioCosto = $o_ResultSet->getfloat( 'PrecioCosto' );
        return $s_PrecioCosto;
      
    }
    
    public function buscarDocumentosRelacionados( $a_Param = array() )
    {
        $s_Where  = $this->buildConsult();
        $s_Where2 = " CantSaldo > 0 ";

        if( isset( $a_Param['Inicio'] ) )
        {
            $s_Where2 .= " AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) >= ".$a_Param['Inicio']." ";
        }

        if( isset( $a_Param['Fin'] ) )
        {
            $s_Where2 .= " AND DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) <= ".$a_Param['Fin']." ";
        }

        $s_Sql = "
                SELECT
                  -- DATEDIFF(LEFT(T2.FechaDocumento,10), LEFT(T2.FechaDocumentoOriginal,10)) DiasCalculadoOnline,
                  DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d' ), LEFT(T2.FechaDocumentoOriginal,10)) DiasCalculadoOnline,
                  T2.*
                FROM
                (
                    SELECT
                      MAX(IdTrx) IdTrx, MAX(FechaDocumento) FechaDocumento, ReferenciaTipoDocumentoOriginal, ReferenciaNumeroDocumentoOriginal
                    FROM ALM_TransParaRepInv
                    WHERE
                      $s_Where
                     GROUP BY ReferenciaTipoDocumentoOriginal, ReferenciaNumeroDocumentoOriginal
                    ORDER BY FechaDocumento DESC
                ) T1
                    LEFT JOIN ALM_TransParaRepInv T2 ON T1.IdTrx=T2.IdTrx
                WHERE 1=1 AND $s_Where2
                ORDER BY FechaDocumentoOriginal
                ;
        ";
        //pr( $s_Sql );
        $o_ResultSet = Conexion::getConexion()->prepareStatement( $s_Sql )->executeQuery();
        $a_ALM_TransParaRepInv = array();
        while( $o_ResultSet->next() ) 
        {
            $a_SQLRow = $o_ResultSet->getRow();
            $o_ALM_TransParaRepInvBN = $this->insertObjectBN( $a_SQLRow );
            $a_ALM_TransParaRepInv[] = $o_ALM_TransParaRepInvBN;
        }
        return $a_ALM_TransParaRepInv;
    }
}
?>