DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `bs`.`fondo_evolution_add`()
BEGIN
/**
 * Inserta en la tabla fondo_maniobra los saldos acumulados en los ultimos 365 dias
 */
	DECLARE datFEC DATE DEFAULT '2015-01-01';
	
	SELECT TIMESTAMPADD(DAY,1,MAX(fecha)) 
		INTO datFEC 
			FROM bs.fondo_maniobra;
	
	WHILE datFEC < util.VN_CURDATE() DO
		
		IF DAY(datFEC) MOD 28 = 0 THEN  -- esto solo sirve para no aburrirse mientras esperamos...
           SELECT datFEC;
		END IF;
        
		REPLACE bs.fondo_maniobra(Fecha, clientes_facturas, clientes_cobros,proveedores_facturas,proveedores_pagos, fondo)
			SELECT datFEC AS Fecha, Facturas, Cobros,Recibidas,Pagos, Facturas + Cobros + Recibidas + Pagos
				FROM (
					SELECT SUM(io.amount) AS Facturas
						FROM vn.invoiceOut io
							JOIN vn.client c ON io.clientFk = c.id 
						WHERE c.isRelevant
							AND io.companyFk <> 1381 
							AND io.issued BETWEEN '2011-01-01' AND datFEC
				) fac
					JOIN (
						SELECT - SUM(r.amountPaid) AS Cobros
							FROM vn.receipt r 
								JOIN vn.client c ON r.clientFk = c.id 
							WHERE c.isRelevant 
								AND r.companyFk <> 1381
								AND r.payed BETWEEN '2011-01-01' AND datFEC
					) cob
					JOIN (
						SELECT - SUM(id.amount) AS Recibidas
							FROM vn.invoiceIn ii 
								JOIN vn.invoiceInDueDay id ON ii.id = id.invoiceInFk 
							WHERE ii.companyFk <> 1381
								AND ii.issued BETWEEN '2015-01-01' AND datFEC
					) rec
					JOIN (
						SELECT SUM(p.amount) AS Pagos
							FROM vn.payment p 
							WHERE p.companyFk <>1381 
								AND p.received BETWEEN '2015-01-01' AND datFEC
					) pag;
                              
		UPDATE bs.fondo_maniobra
        	JOIN (
        		SELECT AVG(fondo) AS media
			        FROM bs.fondo_maniobra
			        	WHERE fecha <= datFEC
			) sub
		SET fondo_medio = media WHERE fecha = datFEC;
			                            
		SET datFEC = TIMESTAMPADD(DAY,1,datFEC);

	END WHILE;


END$$
DELIMITER ;