Database

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_recurring bit, IN param_max_recurring_date date, 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
	UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;

	INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), 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_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
	SET param_record_identity = LAST_INSERT_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;

	IF (param_is_recurring = TRUE) THEN
	BEGIN
		REPEAT
			SET param_job_order_date = DATE_ADD(param_job_order_date, INTERVAL 7 DAY);

			-- insert recurring job orders;
			INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), 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_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
			SET param_record_identity = LAST_INSERT_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.");
		UNTIL param_job_order_date >= param_max_recurring_date
		END REPEAT;
	END;
	END IF;
END

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_recurring bit, IN param_max_recurring_date date, 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
	UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;

	INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), 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_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
	SET param_record_identity = LAST_INSERT_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;

	IF (param_is_recurring = TRUE) THEN
	BEGIN
		REPEAT
			SET param_job_order_date = DATE_ADD(param_job_order_date, INTERVAL 7 DAY);

			-- insert recurring job orders;
			INSERT INTO job_orders (history_status, status_user, status_date, customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_recurring, max_recurring_date, is_contract, contract_id, total_hours, created_date, created_user) VALUES ('New', param_created_user, NOW(), 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_recurring, param_max_recurring_date, param_is_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
			SET param_record_identity = LAST_INSERT_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.");
		UNTIL param_job_order_date >= param_max_recurring_date
		END REPEAT;
	END;
	END IF;
END