CREATE PROCEDURE `jm1_calculate`(IN recalculate_errors boolean, IN recalculate_all boolean)
BEGIN
	DECLARE finished INT DEFAULT 0;
	DECLARE v_id INT;
	DECLARE v_f_date DATE;
	DECLARE v_fc_days INT DEFAULT 0;
	DECLARE v_f_dom_flights INT;
	DECLARE v_f_intl_flights INT;
	DECLARE v_f_dom_pass_rpm BIGINT;
	DECLARE v_f_intl_pass_rpm BIGINT;
	DECLARE v_f_dom_pass INT;
	DECLARE v_f_intl_pass INT;
	DECLARE v_fc_dom_average_mpf FLOAT DEFAULT 0;
	DECLARE v_fc_intl_average_mpf FLOAT DEFAULT 0;
	DECLARE v_fc_vmt_pass_dom BIGINT DEFAULT 0;
	DECLARE v_fc_vmt_pass_intl BIGINT DEFAULT 0;
	DECLARE v_fc_mpg_calc_dom FLOAT DEFAULT 0;
	DECLARE v_f_mpg_intl_grossup FLOAT;
	DECLARE v_fc_mpg_calc_intl FLOAT DEFAULT 0;
	DECLARE v_fc_dem_dom FLOAT DEFAULT 0;
	DECLARE v_fc_dem_intl FLOAT DEFAULT 0;
	DECLARE v_fc_total_pass_dem FLOAT DEFAULT 0;
	DECLARE v_f_dom_rev_ton_miles INT;
	DECLARE v_f_intl_rev_ton_miles INT;
	DECLARE v_f_dom_rev_tons_enplaned FLOAT;
	DECLARE v_f_intl_rev_tons_enplaned FLOAT;
	DECLARE v_fc_dom_avg_mpf FLOAT DEFAULT 0;
	DECLARE v_fc_intl_avg_mpf FLOAT DEFAULT 0;
	DECLARE v_f_dom_cargo_flights INT;
	DECLARE v_f_intl_cargo_flights INT;
	DECLARE v_fc_vmt_cargo_dom BIGINT DEFAULT 0;
	DECLARE v_fc_vmt_cargo_intl BIGINT DEFAULT 0;
	DECLARE v_f_cargo_mpg_dom_grossup FLOAT DEFAULT 1;
	DECLARE v_fc_mpg_calc_cargo_dom FLOAT DEFAULT 0;
	DECLARE v_f_cargo_mpg_intl_grossup FLOAT DEFAULT 0.27272727272;
	DECLARE v_fc_mpg_calc_cargo_intl FLOAT DEFAULT 0;
	DECLARE v_fc_dem_dom2 FLOAT DEFAULT 0;
	DECLARE v_fc_dem_intl2 FLOAT DEFAULT 0;
	DECLARE v_fc_total_dem2 FLOAT DEFAULT 0;
	DECLARE v_fc_total_dem_pass_cargo FLOAT DEFAULT 0;
	DECLARE v_f_jet_demand FLOAT;
	DECLARE v_calculated BOOLEAN DEFAULT false;
	DECLARE v_has_error BOOLEAN DEFAULT false;

    DEClARE curRows
	CURSOR FOR
	select
	 id,
	 f_date,
	 f_dom_flights,
	 f_intl_flights,
	 f_dom_pass_rpm,
	 f_intl_pass_rpm,
	 f_dom_pass,
	 f_intl_pass,
	 f_mpg_intl_grossup,
	 f_dom_rev_ton_miles,
	 f_intl_rev_ton_miles,
	 f_dom_rev_tons_enplaned,
	 f_intl_rev_tons_enplaned,
	 f_dom_cargo_flights,
	 f_intl_cargo_flights,
	 f_cargo_mpg_dom_grossup,
	 f_cargo_mpg_intl_grossup,
	 f_jet_demand,
	 calculated,
     has_error
	from jet
    where recalculate_all or calculated = 0 or (has_error = 1 and recalculate_errors);
	declare continue handler for not found set finished=1;


	OPEN curRows;
	loopRows: LOOP
		FETCH curRows INTO
		v_id,
		v_f_date,
		v_f_dom_flights,
		v_f_intl_flights,
		v_f_dom_pass_rpm,
		v_f_intl_pass_rpm,
		v_f_dom_pass,
		v_f_intl_pass,
		v_f_mpg_intl_grossup,
		v_f_dom_rev_ton_miles,
		v_f_intl_rev_ton_miles,
		v_f_dom_rev_tons_enplaned,
		v_f_intl_rev_tons_enplaned,
		v_f_dom_cargo_flights,
		v_f_intl_cargo_flights,
		v_f_cargo_mpg_dom_grossup,
		v_f_cargo_mpg_intl_grossup,
		v_f_jet_demand,
		v_calculated,
		v_has_error;
		IF finished = 1 THEN LEAVE loopRows; END IF;

		-- calculation
        SET v_fc_days = DAYOFMONTH(LAST_DAY(v_f_date));
        SET v_fc_dom_average_mpf = v_f_dom_pass_rpm / NULLIF(v_f_dom_pass,0);
        SET v_fc_intl_average_mpf = v_f_intl_pass_rpm / NULLIF(v_f_intl_pass,0);
        SET v_fc_vmt_pass_dom = v_f_dom_flights * v_fc_dom_average_mpf;
        SET v_fc_vmt_pass_intl = v_f_intl_flights * v_fc_intl_average_mpf;
        SET v_fc_dom_avg_mpf = v_f_dom_rev_ton_miles / NULLIF(v_f_dom_rev_tons_enplaned,0);
        SET v_fc_intl_avg_mpf = v_f_intl_rev_ton_miles / NULLIF(v_f_intl_rev_tons_enplaned,0);
        SET v_fc_vmt_cargo_dom = v_fc_dom_avg_mpf * v_f_dom_cargo_flights;
        SET v_fc_vmt_cargo_intl = v_fc_intl_avg_mpf * v_f_intl_cargo_flights;
        SET v_fc_mpg_calc_dom = (v_fc_vmt_pass_dom + v_fc_vmt_pass_intl/NULLIF(v_f_mpg_intl_grossup,0) +
		 						v_fc_vmt_cargo_dom/NULLIF(v_f_cargo_mpg_dom_grossup,0) +
								v_fc_vmt_cargo_intl/NULLIF(v_f_cargo_mpg_dom_grossup*v_f_cargo_mpg_intl_grossup,0))
								/NULLIF(v_f_jet_demand*42*1000*v_fc_days,0);
        SET v_fc_mpg_calc_intl = v_fc_mpg_calc_dom * v_f_mpg_intl_grossup;
        SET v_fc_dem_dom = v_fc_vmt_pass_dom/NULLIF(v_fc_days*v_fc_mpg_calc_dom*42*1000,0);
        SET v_fc_dem_intl =  (v_fc_vmt_pass_intl/NULLIF(v_f_cargo_mpg_intl_grossup,0))/NULLIF(v_fc_days*v_fc_mpg_calc_dom*42*1000,0);
		SET v_fc_total_pass_dem = v_fc_dem_dom + v_fc_dem_intl;
        SET v_fc_mpg_calc_cargo_dom = v_fc_mpg_calc_dom * v_f_cargo_mpg_dom_grossup;
        SET v_fc_mpg_calc_cargo_intl = v_fc_mpg_calc_cargo_dom * v_f_cargo_mpg_intl_grossup;
        SET v_fc_dem_dom2 = v_fc_vmt_cargo_dom/NULLIF(v_fc_days*v_fc_mpg_calc_cargo_dom*42*1000,0);
		SET v_fc_dem_intl2 = v_fc_vmt_cargo_intl/NULLIF(v_fc_days*v_fc_mpg_calc_cargo_intl*42*1000,0);
		SET v_fc_total_dem2 = v_fc_dem_dom2 + v_fc_dem_intl2;
		SET v_fc_total_dem_pass_cargo = v_fc_total_pass_dem + v_fc_total_dem2;

		-- validation
		IF
        v_fc_days is null or
        v_fc_dom_average_mpf is null or
        v_fc_intl_average_mpf is null or
        v_fc_vmt_pass_dom is null or
        v_fc_vmt_pass_intl is null or
        v_fc_dom_avg_mpf is null or
        v_fc_intl_avg_mpf is null or
        v_fc_vmt_cargo_dom is null or
        v_fc_vmt_cargo_intl is null or
        v_fc_mpg_calc_dom is null or
        v_fc_mpg_calc_intl is null or
        v_fc_dem_dom is null or
        v_fc_dem_intl is null or
        v_fc_total_pass_dem is null or
        v_fc_mpg_calc_cargo_dom is null or
        v_fc_mpg_calc_cargo_intl is null or
        v_fc_dem_dom2 is null or
        v_fc_dem_intl2 is null or
        v_fc_total_dem2 is null or
        v_fc_total_dem_pass_cargo is null
        THEN
			UPDATE jet SET
            calculated = 1,
            has_error = 1
			WHERE id = v_id;
        ELSE
			UPDATE jet SET
			calculated = 1,
            has_error = 0
			WHERE id = v_id;
        END IF;

		-- update values anyway to see errors
		UPDATE jet SET
		fc_days = v_fc_days,
		fc_dom_average_mpf = v_fc_dom_average_mpf,
		fc_intl_average_mpf = v_fc_intl_average_mpf,
		fc_vmt_pass_dom = v_fc_vmt_pass_dom,
		fc_vmt_pass_intl = v_fc_vmt_pass_intl,
		fc_dom_avg_mpf = v_fc_dom_avg_mpf,
		fc_intl_avg_mpf = v_fc_intl_avg_mpf,
		fc_vmt_cargo_dom = v_fc_vmt_cargo_dom,
		fc_vmt_cargo_intl = v_fc_vmt_cargo_intl,
		fc_mpg_calc_dom = v_fc_mpg_calc_dom,
		fc_mpg_calc_intl = v_fc_mpg_calc_intl,
		fc_dem_dom = v_fc_dem_dom,
		fc_dem_intl = v_fc_dem_intl,
		fc_total_pass_dem = v_fc_total_pass_dem,
		fc_mpg_calc_cargo_dom = v_fc_mpg_calc_cargo_dom,
		fc_mpg_calc_cargo_intl = v_fc_mpg_calc_cargo_intl,
		fc_dem_dom2 = v_fc_dem_dom2,
		fc_dem_intl2 = v_fc_dem_intl2,
		fc_total_dem2 = v_fc_total_dem2,
		fc_total_dem_pass_cargo = v_fc_total_dem_pass_cargo
		WHERE id = v_id;

	END LOOP loopRows;
	CLOSE curRows;
END