DELIMITER $$
CREATE DEFINER=`cpses_grj3j15can`@`localhost` FUNCTION `initcap`(`x` CHAR(30)) RETURNS char(30) CHARSET utf8
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`cpses_grj3j15can`@`localhost` PROCEDURE `insreservation`(IN `CHECKIN` DATE, IN `CHECKOUT` DATE, IN `ADULTS` INT, IN `CHILD` INT, IN `ROOMTYPE` INT, IN `ROOMNO` INT, IN `DONOTMOVE` INT, IN `SOURCE` INT, IN `statuses` INT, IN `paymentstatuses` INT, IN `FIRSTNAME` VARCHAR(255), IN `LASTNAME` VARCHAR(255), IN `ORG` VARCHAR(255), IN `ADDRESS` VARCHAR(500), IN `COUNTRY` INT, IN `STATE` INT, IN `ZIP` VARCHAR(255), IN `PHONE` VARCHAR(255), IN `FAX` VARCHAR(255), IN `EMAIL` VARCHAR(255), IN `CREDITDESC` VARCHAR(500), IN `CARDNO` VARCHAR(100), IN `CARDTYPE` VARCHAR(100), IN `MONYR` VARCHAR(100), IN `CCV` INT, IN `RATE_TYPE` VARCHAR(255), IN `COMMENTS` VARCHAR(1000), IN `INVOICEAMT` VARCHAR(100), IN `RESERVENO` VARCHAR(100), IN `TOTALDAYS` VARCHAR(100), OUT `BOOKSTATUS` VARCHAR(255), OUT `RESERVATION_NO` VARCHAR(255))
BEGIN
DECLARE V_COUNT INT(10) DEFAULT 0;
DECLARE V_guest_id INT(10);
DECLARE V_RES_id INT(10);
DECLARE V_INVOICE_ID INT(10);
DECLARE V_room_status INT(10);

	SELECT COUNT(1) INTO V_COUNT
	FROM `front_room` r
	WHERE r.status='1' AND r.ROOM_TYPE=ROOMTYPE AND r.id = ROOMNO AND NOT EXISTS
	(
	    SELECT 1 FROM `front_reservation` RES
	    WHERE RES.RES_ROOM_ID = r.ID AND RES.res_room_avail = 0
	    AND 
	    ((CHECKIN > RES.res_check_in AND CHECKIN < RES.res_check_OUT) OR
	     (CHECKOUT > RES.res_check_in AND CHECKOUT < RES.res_check_OUT) OR
	     (CHECKIN <= RES.res_check_in AND CHECKOUT >= RES.res_check_OUT)
	     )
	);
			
	
	IF V_COUNT > 0 THEN
	
		INSERT INTO `front_guest_user` (guest_first_name,
						guest_last_name,
						guest_org,
						guest_address,
						guest_state,
						guest_country,
						guest_zip,
						guest_phone,
						guest_fax,
						guest_email)
					VALUES (FIRSTNAME,
						LASTNAME,
						ORG,
						ADDRESS,
						STATE,
						COUNTRY,
						ZIP,
						PHONE,
						FAX,
						EMAIL
						);
					
		SET V_guest_id = LAST_INSERT_ID();
		
		IF V_guest_id IS NOT NULL THEN
			SELECT ROOM_STATUS INTO V_room_status FROM `front_room_status` WHERE id = statuses;
			
			INSERT INTO `front_reservation`(res_guest_id,
							res_check_in,
							res_check_out,
							res_room_id,
							res_room_type,
							res_adult,
							res_child,
							res_source,
							res_status,
							res_payment_status,
							res_comments,
							res_invoice_id,
							res_do_not_move,
							res_rate_type,
							res_reserve_no,
							res_dur_of_stay,
							res_room_avail
							)
						VALUES (V_guest_id,
							CHECKIN,
							CHECKOUT,
							ROOMNO,
							ROOMTYPE,
							ADULTS,
							CHILD,
							SOURCE,
							statuses,
							paymentstatuses,
							COMMENTS,
							0,
							DONOTMOVE,
							RATE_TYPE,
							RESERVENO,
							TOTALDAYS,
							V_room_status
							);
		
			SET V_RES_id = LAST_INSERT_ID();
			
			IF V_RES_id IS NOT NULL THEN
			
				INSERT INTO front_res_invoice (guest_id,
								res_id,
								invoice_amt
								)
							VALUES(V_guest_id,
								V_RES_id,
								INVOICEAMT
								);
				SET V_INVOICE_ID = LAST_INSERT_ID();
				
				IF V_INVOICE_ID IS NOT NULL THEN
				
					UPDATE `front_reservation` SET res_invoice_id = V_INVOICE_ID 
					WHERE res_id = V_RES_id AND res_guest_id = V_guest_id;
				
				END IF;
				
				INSERT INTO `front_guest_credit_info` (guest_id,
									res_id,
									`desc`,
									card_no,
									card_type,
									mon_yr,
									ccv
									)
								VALUES (V_guest_id,
									V_RES_id,
									CREDITDESC,
									CARDNO,
									CARDTYPE,
									MONYR,
									CCV
									);
				
			END IF;
							
		END IF;
		SET BOOKSTATUS = 'Booking successfully';
	ELSE
		SET BOOKSTATUS = 'Rooms not available';
	END IF;
	
	SET RESERVATION_NO = V_RES_id;
	
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`cpses_grj3j15can`@`localhost` PROCEDURE `updreservation`(IN `CHECKIN` DATE, IN `CHECKOUT` DATE, IN `ADULTS` INT, IN `CHILD` INT, IN `ROOMTYPE` INT, IN `ROOMNO` INT, IN `DONOTMOVE` INT, IN `SOURCE` INT, IN `statuses` INT, IN `paymentstatuses` INT, IN `FIRSTNAME` VARCHAR(255), IN `LASTNAME` VARCHAR(255), IN `ORG` VARCHAR(255), IN `ADDRESS` VARCHAR(500), IN `COUNTRY` INT, IN `STATE` INT, IN `ZIP` VARCHAR(255), IN `PHONE` VARCHAR(255), IN `FAX` VARCHAR(255), IN `EMAIL` VARCHAR(255), IN `CREDITDESC` VARCHAR(500), IN `CARDNO` VARCHAR(100), IN `CARDTYPE` VARCHAR(100), IN `MONYR` VARCHAR(100), IN `CCV` INT, IN `RATE_TYPE` VARCHAR(255), IN `COMMENTS` VARCHAR(1000), IN `INVOICEAMT` VARCHAR(100), IN `RESERVENO` VARCHAR(100), IN `TOTALDAYS` VARCHAR(100), IN `RESERVATION_NO` VARCHAR(255), OUT `BOOKSTATUS` VARCHAR(255))
BEGIN
DECLARE V_COUNT INT(10) DEFAULT 0;
DECLARE V_guest_id INT(10);
DECLARE V_room_status INT(10);

	SELECT COUNT(1) INTO V_COUNT
	FROM `front_room` r
	WHERE r.status='1' AND r.ROOM_TYPE=ROOMTYPE AND r.id = ROOMNO AND NOT EXISTS
	(
	    SELECT 1 FROM `front_reservation` RES
	    WHERE RES.RES_ROOM_ID = r.ID AND RES.RES_ID <> RESERVATION_NO AND RES.res_room_avail = 0
	    AND 
	    ((CHECKIN > RES.res_check_in AND CHECKIN < RES.res_check_OUT) OR
	     (CHECKOUT > RES.res_check_in AND CHECKOUT < RES.res_check_OUT) OR
	     (CHECKIN <= RES.res_check_in AND CHECKOUT >= RES.res_check_OUT)
	     )
	   
	);
	
	
	
	
	
	IF V_COUNT > 0 THEN
		
		SELECT ROOM_STATUS INTO V_room_status FROM `front_room_status` WHERE id = statuses;
		
		UPDATE `front_reservation` SET res_check_in = CHECKIN,
						res_check_out = CHECKOUT,
						res_room_id = ROOMNO,
						res_room_type = ROOMTYPE,
						res_adult = ADULTS,
						res_child = CHILD,
						res_source = SOURCE,
						res_status = statuses,
						res_payment_status = paymentstatuses,
						res_comments = COMMENTS,
						res_do_not_move = DONOTMOVE,
						res_rate_type = RATE_TYPE,
						res_reserve_no = RESERVENO,
						res_dur_of_stay = TOTALDAYS,
						res_room_avail = V_room_status
					WHERE res_id = RESERVATION_NO;
		
		SELECT res_guest_id  INTO v_guest_id FROM `front_reservation` WHERE res_id= RESERVATION_NO;
			
			UPDATE `front_guest_user` SET guest_first_name = FIRSTNAME,
						guest_last_name = LASTNAME,
						guest_org = ORG,
						guest_address = ADDRESS,
						guest_state = STATE,
						guest_country = COUNTRY,
						guest_zip = ZIP,
						guest_phone = PHONE,
						guest_fax = FAX,
						guest_email = EMAIL
					WHERE guest_id= v_guest_id;
			
			UPDATE `front_res_invoice` SET invoice_amt = INVOICEAMT 
			WHERE guest_id = v_guest_id AND res_id = RESERVATION_NO;
			
			
			UPDATE `front_guest_credit_info` SET `desc` = CREDITDESC,
								card_no = CARDNO,
								card_type = CARDTYPE,
								mon_yr = MONYR,
								ccv = CCV
							WHERE guest_id = v_guest_id AND res_id = RESERVATION_NO;
												
																
			
							
		
		SET BOOKSTATUS = 'Booking updated successfully';
	ELSE
		SET BOOKSTATUS = 'Rooms not available';
	END IF;
	
		
END$$
DELIMITER ;