MySQL Create every Sun for date = xx

[jrahma]

Hi,

I am using below MySQL stored procedure to add a new job order into MySql table.

I would like to do the following modification in the code:

I want to add an additional parameter called param_max_auto_weekday

then I want to check the WeekDay of the param_job_order_date

and repeat the code until reaching param_max_auto_weekday

so if the WeekDay(param_job_order_date) = Sunday

then It should INSERT INTO job_orders for every Sunday in the week until param_max_auto_weekday

Here is the code... with Thanks:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
	INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_contract, contract_id, total_hours, created_date, created_user) VALUES (param_customer_id, param_cleaner_id, param_job_order_date, param_job_order_date_to, param_start_time, param_end_time, param_job_order_note, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
	SET param_record_identity = LAST_INSERT_ID();

UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;

IF (param_is_contract = TRUE) THEN
BEGIN
	UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_record_identity;
	UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
END;
END IF;

-- add event log;
CALL sp_add_event_log("JOBORDER", param_record_identity, param_created_user, "Job order was created.");
CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
END