DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `vn`.`intrastat_estimateNet`(
	vSelf INT,
	vStems INT
)
	RETURNS double
	DETERMINISTIC
BEGIN
/**
* Calcula un valor neto estimado en función de 
* datos históricos de facturas intrastat.
*
* @param vSelf Id de intrastat
* @param vStems Número de unidades
* @return vNet
*/
	DECLARE vNet DOUBLE;

	SELECT ROUND(vStems / (SUM(average) / COUNT(average)), 2) INTO vNet
		FROM (
			SELECT *, stems / net average
				FROM invoiceInIntrastat
				WHERE intrastatFk = vSelf
					AND net
					AND stems > 0
				ORDER BY dated DESC
				LIMIT 20
		) sub;

	RETURN vNet/2;
END$$
DELIMITER ;

GRANT EXECUTE ON FUNCTION vn.intrastat_estimateNet TO administrative;