Click here to Skip to main content
15,867,568 members
Home / Discussions / Database
   

Database

 
GeneralRe: Why connection is failing?! Pin
Mycroft Holmes16-Apr-14 12:57
professionalMycroft Holmes16-Apr-14 12:57 
GeneralRe: Why connection is failing?! Pin
Jassim Rahma16-Apr-14 12:59
Jassim Rahma16-Apr-14 12:59 
GeneralRe: Why connection is failing?! Pin
Bernhard Hiller16-Apr-14 20:54
Bernhard Hiller16-Apr-14 20:54 
GeneralRe: Why connection is failing?! Pin
Jassim Rahma16-Apr-14 22:38
Jassim Rahma16-Apr-14 22:38 
GeneralRe: Why connection is failing?! Pin
Eddy Vluggen17-Apr-14 0:26
professionalEddy Vluggen17-Apr-14 0:26 
GeneralRe: Why connection is failing?! Pin
Jassim Rahma17-Apr-14 0:42
Jassim Rahma17-Apr-14 0:42 
GeneralRe: Why connection is failing?! Pin
Eddy Vluggen17-Apr-14 10:46
professionalEddy Vluggen17-Apr-14 10:46 
Questionwhy looping is not starting from my start date? Pin
Jassim Rahma16-Apr-14 0:04
Jassim Rahma16-Apr-14 0:04 
Hi,

I am using this code to loop from start date to end date and add job orders into MySQL table. I only have one problem here. If My start date is 14 April 2014 it will insert records from 15 April 2014 so it's skipping my start date.

can anyone help please...

here is the stored procedure..

SQL
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



Technology News @ www.JassimRahma.com

AnswerRe: why looping is not starting from my start date? Pin
jimbowAX16-Apr-14 3:04
jimbowAX16-Apr-14 3:04 
QuestionRe: why looping is not starting from my start date? Pin
Richard MacCutchan16-Apr-14 6:01
mveRichard MacCutchan16-Apr-14 6:01 
AnswerRe: why looping is not starting from my start date? Pin
Jassim Rahma16-Apr-14 6:14
Jassim Rahma16-Apr-14 6:14 
SuggestionRe: why looping is not starting from my start date? Pin
Richard MacCutchan16-Apr-14 6:29
mveRichard MacCutchan16-Apr-14 6:29 
GeneralRe: why looping is not starting from my start date? Pin
jimbowAX22-Apr-14 7:32
jimbowAX22-Apr-14 7:32 
QuestionEmail Notification trhu SQL SERVER using SMTP Pin
Member 1075257815-Apr-14 21:09
Member 1075257815-Apr-14 21:09 
AnswerRe: Email Notification trhu SQL SERVER using SMTP Pin
jschell17-Apr-14 10:07
jschell17-Apr-14 10:07 
QuestionRecurring job orders timeing out after about 32 seconds Pin
Jassim Rahma14-Apr-14 23:54
Jassim Rahma14-Apr-14 23:54 
AnswerRe: Recurring job orders timeing out after about 32 seconds Pin
Richard Deeming15-Apr-14 2:30
mveRichard Deeming15-Apr-14 2:30 
GeneralRe: Recurring job orders timeing out after about 32 seconds Pin
Jassim Rahma15-Apr-14 2:47
Jassim Rahma15-Apr-14 2:47 
GeneralRe: Recurring job orders timeing out after about 32 seconds Pin
Richard Deeming15-Apr-14 2:54
mveRichard Deeming15-Apr-14 2:54 
GeneralRe: Recurring job orders timeing out after about 32 seconds Pin
Jassim Rahma15-Apr-14 2:55
Jassim Rahma15-Apr-14 2:55 
QuestionMySQL A connection attempt failed because the connected third party did not properly respond after a period of time Pin
Jassim Rahma12-Apr-14 1:57
Jassim Rahma12-Apr-14 1:57 
AnswerRe: MySQL A connection attempt failed because the connected third party did not properly respond after a period of time Pin
Jassim Rahma14-Apr-14 23:55
Jassim Rahma14-Apr-14 23:55 
Questionconvert Pin
Member 1074305611-Apr-14 10:33
Member 1074305611-Apr-14 10:33 
AnswerRe: convert Pin
Mycroft Holmes11-Apr-14 13:35
professionalMycroft Holmes11-Apr-14 13:35 
QuestionDatabase with direct TCP/IP access? Pin
Marco Bertschi10-Apr-14 23:17
protectorMarco Bertschi10-Apr-14 23:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.