DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `bs`.`m3Add`()
BEGIN

	DECLARE datSTART DATE;
    DECLARE datEND DATE;
   
    SELECT TIMESTAMPADD(WEEK, -1,MAX(fecha)) INTO datSTART
		FROM bs.m3;
        
	SET datEND = TIMESTAMPADD(DAY,-1,util.VN_CURDATE());
        
	DELETE FROM bs.m3 
    WHERE fecha >= datSTART;
   
	INSERT INTO bs.m3 (fecha, provinceFk, warehouseFk, m3, year, month, week, day, dayName, euros)
		SELECT v.fecha, a.provinceFk, t.warehouseFk, sum(s.quantity * ic.cm3delivery) / 1000000 AS m3,
				tm.year, tm.month, tm.week, tm.day, dayname(v.fecha), sum(importe)
		FROM vn.ticket t
			JOIN vn.sale s ON s.ticketFk = t.id
            JOIN vn.item i ON i.id = s.itemFk
            JOIN vn.itemType it ON it.id = i.typeFk
			JOIN bs.ventas v ON v.Id_Movimiento = s.id -- Filtra solo por ventas "buenas"
            JOIN vn.time tm ON tm.dated = v.fecha
			JOIN vn.itemCost ic ON ic.itemFk = s.itemFk AND ic.warehouseFk = t.warehouseFk
			JOIN vn.address a ON a.id = t.addressFk
		WHERE v.fecha BETWEEN datSTART AND datEND
		AND s.quantity > 0 -- evita abonos
		AND t.companyFk = 442 -- Verdnatura
		GROUP BY t.warehouseFk, v.fecha, a.provinceFk;
END$$
DELIMITER ;