CREATE DEFINER=`admin`@`localhost` FUNCTION `calculate_interval_fractions_value`(
	interval_type VARCHAR(32), 
    start_date DATE,
    end_date DATE,
    input_value DECIMAL(12,2),
    inclusive BOOL
) RETURNS decimal(8,2)
    READS SQL DATA
    DETERMINISTIC
BEGIN

DECLARE dayspan INT DEFAULT 4;
DECLARE dayspanDecimal DECIMAL (12,6) DEFAULT 0;
DECLARE monthspan INT DEFAULT 0;
DECLARE interval_start DATE DEFAULT CURDATE();
DECLARE interval_end DATE DEFAULT CURDATE();
DECLARE temp_date DATE DEFAULT CURDATE();
DECLARE interval_days INT DEFAULT 2;
DECLARE interval_acc DECIMAL(12,6) DEFAULT 0;
-- DECLARE total_acc DECIMAL(10,2) DEFAULT 0;
DECLARE counter INT DEFAULT 0;

	IF input_value IS NOT NULL THEN
		IF interval_type = "monthly" THEN
			SET temp_date = start_date;
			SET monthspan = ABS( (MONTH(end_date) + (YEAR(end_date) * 12)) - (MONTH(start_date) + (YEAR(start_date) * 12)) );
			WHILE 
				(MONTH(temp_date) + (YEAR(temp_date) * 12)) <= (MONTH(end_date) + (YEAR(end_date) * 12))
			DO
				SET interval_days = DAY(LAST_DAY(temp_date));
				
				IF counter = 0 THEN
					IF monthspan = 0 THEN
						SET dayspan = ABS(DATEDIFF(start_date, end_date)) + inclusive;
					ELSE
						SET dayspan = ABS(interval_days - DAY(start_date)) + inclusive;
					END IF;
				ELSEIF counter = monthspan THEN
					SET dayspan = ABS(DATEDIFF(DATE_SUB(temp_date,INTERVAL DAYOFMONTH(temp_date) -1 DAY), end_date)) + inclusive;
				ELSE
					SET dayspan = interval_days;
				END IF;
				
				SET interval_acc = interval_acc  + ((input_value / interval_days) * dayspan);
				SET temp_date = temp_date + INTERVAL 1 MONTH;
				SET counter = counter + 1;
			END WHILE;
		ELSEIF interval_type = "weekly" THEN
			SET interval_days = 7;
			SET dayspanDecimal = (ABS(DATEDIFF(start_date, end_date)) + inclusive) / 7.0;
			SET interval_acc = (input_value / interval_days) * (dayspanDecimal * 7.0);
		ELSEIF interval_type  = "biweekly" THEN
			SET interval_days = 14;
			SET dayspanDecimal = (ABS(DATEDIFF(start_date, end_date)) + inclusive) / 14.0;
			SET interval_acc = (input_value / interval_days) * (dayspanDecimal * 14.0);
		ELSE
			SET interval_acc = input_value;
		END IF;
    ELSE
		SET interval_acc = 0.0;
    END IF;
    
RETURN interval_acc;
END