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_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