Drop files here

SQL upload ( 0 ) x -

Page-related settings Click on the bar to scroll to top of page
Press Ctrl+Enter to execute query Press Enter to execute query
ascending
descending
Order:
Debug SQL
Count
Execution order
Time taken
Order by:
Group queries
Ungroup queries
Collapse Expand Show trace Hide trace Count : Time taken :
Bookmarks
Refresh
Add
No bookmarks
Add bookmark
Options
Set default





Collapse Expand Requery Edit Explain Profiling Bookmark Query failed Database : Queried time :
Customize browse mode.
Browse mode
Documentation Use only icons, only text or both. Restore default value
Documentation Use only icons, only text or both. Restore default value
Documentation Whether a user should be displayed a "show all (rows)" button. Restore default value
Documentation Number of rows displayed when browsing a result set. If the result set contains more rows, "Previous" and "Next" links will be shown. Restore default value
Documentation SMART - i.e. descending order for columns of type TIME, DATE, DATETIME and TIMESTAMP, ascending order otherwise. Restore default value
Documentation Highlight row pointed by the mouse cursor. Restore default value
Documentation Highlight selected rows. Restore default value
Documentation Restore default value
Documentation Restore default value
Documentation Repeat the headers every X cells, 0 deactivates this feature. Restore default value
Documentation Maximum number of characters shown in any non-numeric column on browse view. Restore default value
Documentation These are Edit, Copy and Delete links. Restore default value
Documentation Whether to show row links even in the absence of a unique key. Restore default value
Documentation Default sort order for tables with a primary key. Restore default value
Documentation When browsing tables, the sorting of each table is remembered. Restore default value
Documentation For display Options Restore default value 
SELECT * FROM `proc` ORDER BY `param_list` DESC
[ Edit inline ] [ Edit ] [ Explain SQL ] [ Create PHP code ] [ Refresh ]
|
|
|
|
Filter rows:
Sort by key:
Full texts db name type specific_name language sql_data_access is_deterministic security_type param_list Descending Ascending 1 returns body definer created modified sql_mode comment character_set_client collation_connection db_collation body_utf8 aggregate
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_old' AND `proc`.`name` = 'sUpdateParticipantDetails' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_old sUpdateParticipantDetails PROCEDURE sUpdateParticipantDetails SQL CONTAINS_SQL NO DEFINER 
`P_participant_id` INT(10), `P_firstname` VARCHAR(40), `P_lastname` VARCHAR(40), `P_middlename` VARCHAR(40), `P_dob` DATE, `P_gender` CHAR(1), `P_ssn_lastfourdigits` CHAR(4), `P_phonenumber` VARCHAR(10), `P_email` VARCHAR(40), `P_address` VARCHAR(80), `P_city_id` VARCHAR(30), `P_state_id` CHAR(2), `P_zipcode` CHAR(5), `P_medications` TEXT, `P_special_diet` TEXT, `P_allergies` TEXT, `P_medical_needs` TEXT, `P_pregnancy` TINYINT(1), `P_smoker` TINYINT(1), `P_prior_treatment_drugalcohol` TINYINT(1), `P_dl_number` VARCHAR(20), `P_dl_issued_state` CHAR(2), `P_dl_suspended` CHAR(1), `P_dl_points` INT(2), `P_updated_by` VARCHAR(20), `P_emergenycontact_name` VARCHAR(80), `P_emergencycontact_phoneNumber` VARCHAR(10), `P_emergencycontact_relationship` VARCHAR(50), `C_CourtId` VARCHAR(3), `C_CourtOther` VARCHAR(40), `C_CaseNumber` VARCHAR(30), `C_BloodAlcoholContent` DECIMAL(4,3), `R_ProgramScheduleId` CHAR(8), `cityId` CHAR(6), IN `P_CourtOrder` TINYINT(1), OUT `status` INT(4)

BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content = C_BloodAlcoholContent
where 
participant_id =P_participant_id;


-- update participantcourtcase
 IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
 ELSE 
	IF EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
    DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
    END IF;
 END IF;

 
END main_block;
END
empower_user@% 2025-12-03 05:48:46 2025-12-03 05:48:46 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content = C_BloodAlcoholContent
where 
participant_id =P_participant_id;


-- update participantcourtcase
 IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
 ELSE 
	IF EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
    DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
    END IF;
 END IF;

 
END main_block;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev' AND `proc`.`name` = 'sUpdateParticipantDetails' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev sUpdateParticipantDetails PROCEDURE sUpdateParticipantDetails SQL CONTAINS_SQL NO DEFINER 
`P_participant_id` INT(10), `P_firstname` VARCHAR(40), `P_lastname` VARCHAR(40), `P_middlename` VARCHAR(40), `P_dob` DATE, `P_gender` CHAR(1), `P_ssn_lastfourdigits` CHAR(4), `P_phonenumber` VARCHAR(10), `P_email` VARCHAR(40), `P_address` VARCHAR(80), `P_city_id` VARCHAR(30), `P_state_id` CHAR(2), `P_zipcode` CHAR(5), `P_medications` TEXT, `P_special_diet` TEXT, `P_allergies` TEXT, `P_medical_needs` TEXT, `P_pregnancy` TINYINT(1), `P_smoker` TINYINT(1), `P_prior_treatment_drugalcohol` TINYINT(1), `P_dl_number` VARCHAR(20), `P_dl_issued_state` CHAR(2), `P_dl_suspended` CHAR(1), `P_dl_points` INT(2), `P_updated_by` VARCHAR(20), `P_emergenycontact_name` VARCHAR(80), `P_emergencycontact_phoneNumber` VARCHAR(10), `P_emergencycontact_relationship` VARCHAR(50), `C_CourtId` VARCHAR(3), `C_CourtOther` VARCHAR(40), `C_CaseNumber` VARCHAR(30), `C_BloodAlcoholContent` DECIMAL(4,3), `R_ProgramScheduleId` CHAR(8), `cityId` CHAR(6), IN `P_CourtOrder` TINYINT(1), IN `P_Notes` text, OUT `status` INT(4)

BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent,
notes = P_Notes

where 
participant_id =P_participant_id;


-- update participantcourtcase
 -- IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
  -- ELSE 
	-- IF EXISTS (
      --  SELECT 1
      --  FROM participantcourtcase
      --  WHERE participant_id = P_participant_id
 --   ) THEN
 --   DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
  --  END IF;
-- END IF;

END main_block;
END
empower_user@% 2026-02-10 08:04:06 2026-02-10 08:04:06 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent,
notes = P_Notes

where 
participant_id =P_participant_id;


-- update participantcourtcase
 -- IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
  -- ELSE 
	-- IF EXISTS (
      --  SELECT 1
      --  FROM participantcourtcase
      --  WHERE participant_id = P_participant_id
 --   ) THEN
 --   DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
  --  END IF;
-- END IF;

END main_block;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_feb10_2026' AND `proc`.`name` = 'sUpdateParticipantDetails' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_feb10_2026 sUpdateParticipantDetails PROCEDURE sUpdateParticipantDetails SQL CONTAINS_SQL NO DEFINER 
`P_participant_id` INT(10), `P_firstname` VARCHAR(40), `P_lastname` VARCHAR(40), `P_middlename` VARCHAR(40), `P_dob` DATE, `P_gender` CHAR(1), `P_ssn_lastfourdigits` CHAR(4), `P_phonenumber` VARCHAR(10), `P_email` VARCHAR(40), `P_address` VARCHAR(80), `P_city_id` VARCHAR(30), `P_state_id` CHAR(2), `P_zipcode` CHAR(5), `P_medications` TEXT, `P_special_diet` TEXT, `P_allergies` TEXT, `P_medical_needs` TEXT, `P_pregnancy` TINYINT(1), `P_smoker` TINYINT(1), `P_prior_treatment_drugalcohol` TINYINT(1), `P_dl_number` VARCHAR(20), `P_dl_issued_state` CHAR(2), `P_dl_suspended` CHAR(1), `P_dl_points` INT(2), `P_updated_by` VARCHAR(20), `P_emergenycontact_name` VARCHAR(80), `P_emergencycontact_phoneNumber` VARCHAR(10), `P_emergencycontact_relationship` VARCHAR(50), `C_CourtId` VARCHAR(3), `C_CourtOther` VARCHAR(40), `C_CaseNumber` VARCHAR(30), `C_BloodAlcoholContent` DECIMAL(4,3), `R_ProgramScheduleId` CHAR(8), `cityId` CHAR(6), IN `P_CourtOrder` TINYINT(1), IN `P_Notes` text, OUT `status` INT(4)

BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent,
notes = P_Notes

where 
participant_id =P_participant_id;


-- update participantcourtcase
 -- IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
  -- ELSE 
	-- IF EXISTS (
      --  SELECT 1
      --  FROM participantcourtcase
      --  WHERE participant_id = P_participant_id
 --   ) THEN
 --   DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
  --  END IF;
-- END IF;

END main_block;
END
empower_user@% 2026-02-10 07:39:06 2026-02-10 07:39:06 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent,
notes = P_Notes

where 
participant_id =P_participant_id;


-- update participantcourtcase
 -- IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
  -- ELSE 
	-- IF EXISTS (
      --  SELECT 1
      --  FROM participantcourtcase
      --  WHERE participant_id = P_participant_id
 --   ) THEN
 --   DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
  --  END IF;
-- END IF;

END main_block;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_20260210' AND `proc`.`name` = 'sUpdateParticipantDetails' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_20260210 sUpdateParticipantDetails PROCEDURE sUpdateParticipantDetails SQL CONTAINS_SQL NO DEFINER 
`P_participant_id` INT(10), `P_firstname` VARCHAR(40), `P_lastname` VARCHAR(40), `P_middlename` VARCHAR(40), `P_dob` DATE, `P_gender` CHAR(1), `P_ssn_lastfourdigits` CHAR(4), `P_phonenumber` VARCHAR(10), `P_email` VARCHAR(40), `P_address` VARCHAR(80), `P_city_id` VARCHAR(30), `P_state_id` CHAR(2), `P_zipcode` CHAR(5), `P_medications` TEXT, `P_special_diet` TEXT, `P_allergies` TEXT, `P_medical_needs` TEXT, `P_pregnancy` TINYINT(1), `P_smoker` TINYINT(1), `P_prior_treatment_drugalcohol` TINYINT(1), `P_dl_number` VARCHAR(20), `P_dl_issued_state` CHAR(2), `P_dl_suspended` CHAR(1), `P_dl_points` INT(2), `P_updated_by` VARCHAR(20), `P_emergenycontact_name` VARCHAR(80), `P_emergencycontact_phoneNumber` VARCHAR(10), `P_emergencycontact_relationship` VARCHAR(50), `C_CourtId` VARCHAR(3), `C_CourtOther` VARCHAR(40), `C_CaseNumber` VARCHAR(30), `C_BloodAlcoholContent` DECIMAL(4,3), `R_ProgramScheduleId` CHAR(8), `cityId` CHAR(6), IN `P_CourtOrder` TINYINT(1), IN `P_Notes` text, OUT `status` INT(4)

BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent,
notes = P_Notes

where 
participant_id =P_participant_id;


-- update participantcourtcase
 -- IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
  -- ELSE 
	-- IF EXISTS (
      --  SELECT 1
      --  FROM participantcourtcase
      --  WHERE participant_id = P_participant_id
 --   ) THEN
 --   DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
  --  END IF;
-- END IF;

END main_block;
END
empower_user@% 2026-02-10 08:24:05 2026-02-10 08:24:05 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent,
notes = P_Notes

where 
participant_id =P_participant_id;


-- update participantcourtcase
 -- IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
  -- ELSE 
	-- IF EXISTS (
      --  SELECT 1
      --  FROM participantcourtcase
      --  WHERE participant_id = P_participant_id
 --   ) THEN
 --   DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
  --  END IF;
-- END IF;

END main_block;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_migration' AND `proc`.`name` = 'sUpdateParticipantDetails' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_migration sUpdateParticipantDetails PROCEDURE sUpdateParticipantDetails SQL CONTAINS_SQL NO DEFINER 
`P_participant_id` INT(10), `P_firstname` VARCHAR(40), `P_lastname` VARCHAR(40), `P_middlename` VARCHAR(40), `P_dob` DATE, `P_gender` CHAR(1), `P_ssn_lastfourdigits` CHAR(4), `P_phonenumber` VARCHAR(10), `P_email` VARCHAR(40), `P_address` VARCHAR(80), `P_city_id` VARCHAR(30), `P_state_id` CHAR(2), `P_zipcode` CHAR(5), `P_medications` TEXT, `P_special_diet` TEXT, `P_allergies` TEXT, `P_medical_needs` TEXT, `P_pregnancy` TINYINT(1), `P_smoker` TINYINT(1), `P_prior_treatment_drugalcohol` TINYINT(1), `P_dl_number` VARCHAR(20), `P_dl_issued_state` CHAR(2), `P_dl_suspended` CHAR(1), `P_dl_points` INT(2), `P_updated_by` VARCHAR(20), `P_emergenycontact_name` VARCHAR(80), `P_emergencycontact_phoneNumber` VARCHAR(10), `P_emergencycontact_relationship` VARCHAR(50), `C_CourtId` VARCHAR(3), `C_CourtOther` VARCHAR(40), `C_CaseNumber` VARCHAR(30), `C_BloodAlcoholContent` DECIMAL(4,3), `R_ProgramScheduleId` CHAR(8), `cityId` CHAR(6),
IN `P_CourtOrder` TINYINT(1), OUT `status` INT(4)

BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent

where 
participant_id =P_participant_id;


-- update participantcourtcase
 IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
 ELSE 
	IF EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
    DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
    END IF;
 END IF;

END main_block;
END
empower_user@% 2025-11-12 06:29:21 2025-11-12 06:29:21 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship,
court_order=P_CourtOrder,
blood_alcohol_content=C_BloodAlcoholContent

where 
participant_id =P_participant_id;


-- update participantcourtcase
 IF P_CourtOrder = 1 THEN
    IF NOT EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
        INSERT INTO participantcourtcase (
            participant_id,
            court_id,
            court_other,
            case_number,
            created_date,
            created_by,
            updated_date,
            updated_by
        ) VALUES (
            P_participant_id,
            C_CourtId,
            C_CourtOther,
            C_CaseNumber,
            NOW(),
            P_updated_by,
            NOW(),
            P_updated_by
        );
    ELSE
        UPDATE participantcourtcase
        SET
            court_id = C_CourtId,
            court_other = C_CourtOther,
            case_number = C_CaseNumber,
            updated_date = NOW(),
            updated_by = P_updated_by
        WHERE participant_id = P_participant_id;
    END IF;
 ELSE 
	IF EXISTS (
        SELECT 1
        FROM participantcourtcase
        WHERE participant_id = P_participant_id
    ) THEN
    DELETE FROM participantcourtcase WHERE participant_id = P_participant_id;
    END IF;
 END IF;

END main_block;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'empower_dev' AND `proc`.`name` = 'sUpdateParticipantDetails' AND `proc`.`type` = 'PROCEDURE'
 		empower_dev sUpdateParticipantDetails PROCEDURE sUpdateParticipantDetails SQL CONTAINS_SQL NO DEFINER 
`P_participant_id` INT(10), `P_firstname` VARCHAR(40), `P_lastname` VARCHAR(40), `P_middlename` VARCHAR(40), `P_dob` DATE, `P_gender` CHAR(1), `P_ssn_lastfourdigits` CHAR(4), `P_phonenumber` VARCHAR(10), `P_email` VARCHAR(40), `P_address` VARCHAR(80), `P_city_id` VARCHAR(30), `P_state_id` CHAR(2), `P_zipcode` CHAR(5), `P_medications` TEXT, `P_special_diet` TEXT, `P_allergies` TEXT, `P_medical_needs` TEXT, `P_medical_needs_details` TEXT, `P_pregnancy` TINYINT(1), `P_smoker` TINYINT(1), `P_prior_treatment_drugalcohol` TINYINT(1), `P_dl_number` VARCHAR(20), `P_dl_issued_state` CHAR(2), `P_dl_suspended` CHAR(1), `P_dl_points` INT(2), `P_updated_by` VARCHAR(20), `P_emergenycontact_name` VARCHAR(80), `P_emergencycontact_phoneNumber` VARCHAR(10), 
`P_emergencycontact_relationship` VARCHAR(50),  `C_CourtId` VARCHAR(3), `C_CourtOther` VARCHAR(40), `C_CaseNumber` VARCHAR(30), 
`C_BloodAlcoholContent` DECIMAL(4,3), `R_ProgramScheduleId` CHAR(8), `cityId` CHAR(6), out status int(4)

BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
medical_needs_details=P_medical_needs_details,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship

where 
participant_id =P_participant_id;


-- update participantcourtcase
update participantcourtcase
set
court_id=C_CourtId,
court_other=C_CourtOther,
case_number=C_CaseNumber,
blood_alcohol_content=C_BloodAlcoholContent,
updated_date=now(),
updated_by=P_updated_by
where 
participant_id =P_participant_id;

END main_block;
END
empower_user@% 2025-09-17 07:31:35 2025-09-17 07:31:35 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
set status = 9999;
main_block: BEGIN

IF EXISTS (
SELECT 1
FROM participant p
JOIN programregistrationparticipant prp ON p.participant_id = prp.participant_id
JOIN programschedule ps ON prp.programschedule_id = ps.programschedule_id
WHERE LOWER(REPLACE(TRIM(p.firstname), ' ', '')) = LOWER(REPLACE(TRIM(P_firstname), ' ', ''))
AND LOWER(REPLACE(TRIM(p.lastname), ' ', '')) = LOWER(REPLACE(TRIM(P_lastname), ' ', ''))
AND p.dob = P_DOB
AND p.ssn_lastfourdigits = P_ssn_lastfourdigits
AND p.phonenumber = P_phonenumber
AND p.email = P_email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C','N')
AND (p.participant_id != P_participant_id)
) then
ROLLBACK;
	set status = 9998;
	LEAVE main_block;
END IF;

update participant
set
firstname=P_firstname,
lastname=P_lastname,
middlename=P_middlename,
dob=P_dob,
gender=P_gender,
ssn_lastfourdigits=P_ssn_lastfourdigits,
phonenumber=P_phonenumber,
email =P_email,
address=P_address,
city_id=cityId,
state_id=P_state_id,
zipcode=P_zipcode,
medications=P_medications,
special_diet=P_special_diet,
allergies=P_allergies,
medical_needs=P_medical_needs,
medical_needs_details=P_medical_needs_details,
pregnancy=P_pregnancy,
smoker=P_smoker,
prior_treatment_drugalcohol=P_prior_treatment_drugalcohol,
dl_number=P_dl_number,
dl_issued_state=P_dl_issued_state,
dl_suspended=P_dl_suspended,
dl_points=P_dl_points,
updated_date=now(),
updated_by=P_updated_by,
emergencycontact_name=P_emergenycontact_name,
emergencycontact_phoneNumber=P_emergencycontact_phoneNumber,
emergencycontact_relationship=P_emergencycontact_relationship

where 
participant_id =P_participant_id;


-- update participantcourtcase
update participantcourtcase
set
court_id=C_CourtId,
court_other=C_CourtOther,
case_number=C_CaseNumber,
blood_alcohol_content=C_BloodAlcoholContent,
updated_date=now(),
updated_by=P_updated_by
where 
participant_id =P_participant_id;

END main_block;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'empower_dev' AND `proc`.`name` = 'sUpdateSettingValue' AND `proc`.`type` = 'PROCEDURE'
 		empower_dev sUpdateSettingValue PROCEDURE sUpdateSettingValue SQL CONTAINS_SQL NO DEFINER 
IN in_parameter_name VARCHAR(30)
, IN in_parameter_value VARCHAR(200),IN in_UpdatedBy VARCHAR(20), Out `status` int(4) 

BEGIN

 DECLARE parameterValue VARCHAR(50);
 SELECT parameter_value INTO parameterValue
    FROM setting WHERE parameter_name = in_parameter_name;
    set status = 0000;
    
	UPDATE setting
SET parameter_value = in_parameter_value,
    created_by = IF(parameterValue IS NULL OR parameterValue = '', in_UpdatedBy, created_by),
    created_date = IF(parameterValue IS NULL OR parameterValue = '', NOW(), created_date),
    updated_by = in_UpdatedBy,
    updated_date = NOW()
WHERE parameter_name = in_parameter_name;
 set status =  9999;
END
empower_user@% 2025-08-05 00:01:57 2025-08-05 00:01:57 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN

 DECLARE parameterValue VARCHAR(50);
 SELECT parameter_value INTO parameterValue
    FROM setting WHERE parameter_name = in_parameter_name;
    set status = 0000;
    
	UPDATE setting
SET parameter_value = in_parameter_value,
    created_by = IF(parameterValue IS NULL OR parameterValue = '', in_UpdatedBy, created_by),
    created_date = IF(parameterValue IS NULL OR parameterValue = '', NOW(), created_date),
    updated_by = in_UpdatedBy,
    updated_date = NOW()
WHERE parameter_name = in_parameter_name;
 set status =  9999;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'empower_dev' AND `proc`.`name` = 'sGetSettingValue' AND `proc`.`type` = 'PROCEDURE'
 		empower_dev sGetSettingValue PROCEDURE sGetSettingValue SQL CONTAINS_SQL NO DEFINER 
IN in_parameter_name VARCHAR(30)

BEGIN

  SELECT parameter_value FROM Setting WHERE parameter_name = in_parameter_name;
END
empower_user@% 2025-07-28 03:09:21 2025-07-28 03:09:21 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN

  SELECT parameter_value FROM Setting WHERE parameter_name = in_parameter_name;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'empower_dev' AND `proc`.`name` = 'sConfirmParticipant' AND `proc`.`type` = 'PROCEDURE'
 		empower_dev sConfirmParticipant PROCEDURE sConfirmParticipant SQL CONTAINS_SQL NO DEFINER 
IN `roomType` VARCHAR(6), IN `ProgramScheduleId` CHAR(8), IN `ParticipantId` INT(10)

BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C' where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
empower_user@% 2025-07-22 04:28:28 2025-07-22 04:28:28 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C' where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_migration' AND `proc`.`name` = 'sConfirmParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_migration sConfirmParticipant PROCEDURE sConfirmParticipant SQL CONTAINS_SQL NO DEFINER 
IN `roomType` VARCHAR(6), IN `ProgramScheduleId` CHAR(8), IN `ParticipantId` INT(10)

BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C' where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
empower_user@% 2025-10-30 05:30:11 2025-10-30 05:30:11 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C' where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_old' AND `proc`.`name` = 'sConfirmParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_old sConfirmParticipant PROCEDURE sConfirmParticipant SQL CONTAINS_SQL NO DEFINER 
IN `roomType` VARCHAR(6), IN `ProgramScheduleId` CHAR(8), IN `ParticipantId` INT(10)

BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C' where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
empower_user@% 2025-12-03 05:48:45 2025-12-03 05:48:45 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C' where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev' AND `proc`.`name` = 'sConfirmParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev sConfirmParticipant PROCEDURE sConfirmParticipant SQL CONTAINS_SQL NO DEFINER 
IN `roomType` VARCHAR(6), IN `ProgramScheduleId` CHAR(8), IN `ParticipantId` INT(10)

BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C',
		room_single_requested = CASE 
			WHEN roomType = 'single' THEN 1 
			ELSE 0 
		END,
		room_shared_requested = CASE 
			WHEN roomType = 'single' THEN 0 
			ELSE 1 
		END
        where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
empower_user@% 2026-02-10 08:02:08 2026-02-10 08:02:08 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C',
		room_single_requested = CASE 
			WHEN roomType = 'single' THEN 1 
			ELSE 0 
		END,
		room_shared_requested = CASE 
			WHEN roomType = 'single' THEN 0 
			ELSE 1 
		END
        where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_feb10_2026' AND `proc`.`name` = 'sConfirmParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_feb10_2026 sConfirmParticipant PROCEDURE sConfirmParticipant SQL CONTAINS_SQL NO DEFINER 
IN `roomType` VARCHAR(6), IN `ProgramScheduleId` CHAR(8), IN `ParticipantId` INT(10)

BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C',
		room_single_requested = CASE 
			WHEN roomType = 'single' THEN 1 
			ELSE 0 
		END,
		room_shared_requested = CASE 
			WHEN roomType = 'single' THEN 0 
			ELSE 1 
		END
        where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
empower_user@% 2026-02-10 07:39:06 2026-02-10 07:39:06 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C',
		room_single_requested = CASE 
			WHEN roomType = 'single' THEN 1 
			ELSE 0 
		END,
		room_shared_requested = CASE 
			WHEN roomType = 'single' THEN 0 
			ELSE 1 
		END
        where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_20260210' AND `proc`.`name` = 'sConfirmParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_20260210 sConfirmParticipant PROCEDURE sConfirmParticipant SQL CONTAINS_SQL NO DEFINER 
IN `roomType` VARCHAR(6), IN `ProgramScheduleId` CHAR(8), IN `ParticipantId` INT(10)

BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C',
		room_single_requested = CASE 
			WHEN roomType = 'single' THEN 1 
			ELSE 0 
		END,
		room_shared_requested = CASE 
			WHEN roomType = 'single' THEN 0 
			ELSE 1 
		END
        where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
empower_user@% 2026-02-10 08:24:05 2026-02-10 08:24:05 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    DECLARE RoomAvailable INT;
    
    IF roomType = 'shared' then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	ELSEIF roomType = 'single' then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule 
        Where  programschedule_id = ProgramScheduleId;
	END IF;
    
    IF RoomAvailable = 0 THEN
        SELECT 9994 AS status; 
    ELSE
		IF roomType = 'shared' then
			Update programschedule set rooms_shared_booked = rooms_shared_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		ELSEIF roomType = 'single' then
			Update programschedule set rooms_single_booked = rooms_single_booked + 1
			Where  programschedule_id = ProgramScheduleId;
		END IF;
        
        -- confirm registration
		UPDATE programregistrationparticipant set registrationstatus_id = 'C',
		room_single_requested = CASE 
			WHEN roomType = 'single' THEN 1 
			ELSE 0 
		END,
		room_shared_requested = CASE 
			WHEN roomType = 'single' THEN 0 
			ELSE 1 
		END
        where participant_id = ParticipantId
		AND programschedule_id = ProgramScheduleId;
	SELECT 9999 AS status; 
    END IF;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'empower_dev' AND `proc`.`name` = 'sUpdateScheduleForParticipant' AND `proc`.`type` = 'PROCEDURE'
 		empower_dev sUpdateScheduleForParticipant PROCEDURE sUpdateScheduleForParticipant SQL CONTAINS_SQL NO DEFINER 
IN `prp_programschedule_id` CHAR(8), IN `prp_participant_id` INT(10), IN `prp_updated_by` VARCHAR(20), IN `prp_old_programschedule_id` CHAR(8), IN `prp_roomType` VARCHAR(10), IN `prp_registrationstatus_id` CHAR(1)

BEGIN
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;
     
      -- Return success code
    SELECT 9999 AS status;
        
END
empower_user@% 2025-07-25 23:49:00 2025-07-25 23:49:00 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;
     
      -- Return success code
    SELECT 9999 AS status;
        
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_migration' AND `proc`.`name` = 'sUpdateScheduleForParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_migration sUpdateScheduleForParticipant PROCEDURE sUpdateScheduleForParticipant SQL CONTAINS_SQL NO DEFINER 
IN `prp_programschedule_id` CHAR(8), IN `prp_participant_id` INT(10), IN `prp_updated_by` VARCHAR(20), IN `prp_old_programschedule_id` CHAR(8), IN `prp_roomType` VARCHAR(10), IN `prp_registrationstatus_id` CHAR(1)

BEGIN
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;
     
      -- Return success code
    SELECT 9999 AS status;
        
END
empower_user@% 2025-10-30 05:30:11 2025-10-30 05:30:11 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;
     
      -- Return success code
    SELECT 9999 AS status;
        
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_old' AND `proc`.`name` = 'sUpdateScheduleForParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_old sUpdateScheduleForParticipant PROCEDURE sUpdateScheduleForParticipant SQL CONTAINS_SQL NO DEFINER 
IN `prp_programschedule_id` CHAR(8), IN `prp_participant_id` INT(10), IN `prp_updated_by` VARCHAR(20), IN `prp_old_programschedule_id` CHAR(8), IN `prp_roomType` VARCHAR(10), IN `prp_registrationstatus_id` CHAR(1)

BEGIN
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;
     
      -- Return success code
    SELECT 9999 AS status;
        
END
empower_user@% 2025-12-03 05:48:46 2025-12-03 05:48:46 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;
     
      -- Return success code
    SELECT 9999 AS status;
        
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev' AND `proc`.`name` = 'sUpdateScheduleForParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev sUpdateScheduleForParticipant PROCEDURE sUpdateScheduleForParticipant SQL CONTAINS_SQL NO DEFINER 
IN `prp_programschedule_id` CHAR(8),
 IN `prp_participant_id` INT(10), IN `prp_updated_by` VARCHAR(20), IN `prp_old_programschedule_id` CHAR(8), 
 IN `prp_roomType` VARCHAR(10), IN `prp_registrationstatus_id` CHAR(1) ,IN roomtypechange TINYINT(1),  
    IN is_single_room TINYINT(1),       
    IN is_shared_room TINYINT(1),
    IN reschedulingfee_amount decimal(10,2),
	IN ps_changelog_created_date datetime,
    IN `P_Notes` text
 

BEGIN

update participant set notes = P_Notes where participant_id=prp_participant_id;

 IF roomtypechange = 1 THEN

	UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        room_single_requested = is_single_room,
        room_shared_requested = is_shared_room,
        updated_date = NOW(),
        updated_by = prp_updated_by
        
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
     IF prp_registrationstatus_id = 'C' THEN
     UPDATE programschedule
	 SET 
		rooms_shared_booked = 
			CASE 
				WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
				ELSE rooms_shared_booked
			END,
		rooms_single_booked = 
			CASE 
				WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
				ELSE rooms_single_booked
			END
	 WHERE programschedule_id = prp_old_programschedule_id;
     
     Update programschedule set rooms_shared_booked = rooms_shared_booked + is_shared_room,
	 rooms_single_booked = rooms_single_booked + is_single_room 
	 Where  programschedule_id = prp_programschedule_id;
	 END IF;
     
-- room type not changed start

  ELSE
 
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;

 END IF;     
 
 INSERT INTO `programschedulechangelog`
(`participant_id`,
`from_programschedule_id`,
`to_programschedule_id`,
`reschedulingfee_amount`,
`created_date`,
`created_by`)
VALUES
(prp_participant_id,
prp_old_programschedule_id,
prp_programschedule_id,
reschedulingfee_amount,
ps_changelog_created_date,
prp_updated_by);


      -- Return success code
    SELECT 9999 AS status;
        
END
empower_user@% 2026-02-10 08:04:24 2026-02-10 08:04:24 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN

update participant set notes = P_Notes where participant_id=prp_participant_id;

 IF roomtypechange = 1 THEN

	UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        room_single_requested = is_single_room,
        room_shared_requested = is_shared_room,
        updated_date = NOW(),
        updated_by = prp_updated_by
        
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
     IF prp_registrationstatus_id = 'C' THEN
     UPDATE programschedule
	 SET 
		rooms_shared_booked = 
			CASE 
				WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
				ELSE rooms_shared_booked
			END,
		rooms_single_booked = 
			CASE 
				WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
				ELSE rooms_single_booked
			END
	 WHERE programschedule_id = prp_old_programschedule_id;
     
     Update programschedule set rooms_shared_booked = rooms_shared_booked + is_shared_room,
	 rooms_single_booked = rooms_single_booked + is_single_room 
	 Where  programschedule_id = prp_programschedule_id;
	 END IF;
     
-- room type not changed start

  ELSE
 
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;

 END IF;     
 
 INSERT INTO `programschedulechangelog`
(`participant_id`,
`from_programschedule_id`,
`to_programschedule_id`,
`reschedulingfee_amount`,
`created_date`,
`created_by`)
VALUES
(prp_participant_id,
prp_old_programschedule_id,
prp_programschedule_id,
reschedulingfee_amount,
ps_changelog_created_date,
prp_updated_by);


      -- Return success code
    SELECT 9999 AS status;
        
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_feb10_2026' AND `proc`.`name` = 'sUpdateScheduleForParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_feb10_2026 sUpdateScheduleForParticipant PROCEDURE sUpdateScheduleForParticipant SQL CONTAINS_SQL NO DEFINER 
IN `prp_programschedule_id` CHAR(8),
 IN `prp_participant_id` INT(10), IN `prp_updated_by` VARCHAR(20), IN `prp_old_programschedule_id` CHAR(8), 
 IN `prp_roomType` VARCHAR(10), IN `prp_registrationstatus_id` CHAR(1) ,IN roomtypechange TINYINT(1),  
    IN is_single_room TINYINT(1),       
    IN is_shared_room TINYINT(1),
    IN reschedulingfee_amount decimal(10,2),
	IN ps_changelog_created_date datetime,
    IN `P_Notes` text
 

BEGIN

update participant set notes = P_Notes where participant_id=prp_participant_id;

 IF roomtypechange = 1 THEN

	UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        room_single_requested = is_single_room,
        room_shared_requested = is_shared_room,
        updated_date = NOW(),
        updated_by = prp_updated_by
        
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
     IF prp_registrationstatus_id = 'C' THEN
     UPDATE programschedule
	 SET 
		rooms_shared_booked = 
			CASE 
				WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
				ELSE rooms_shared_booked
			END,
		rooms_single_booked = 
			CASE 
				WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
				ELSE rooms_single_booked
			END
	 WHERE programschedule_id = prp_old_programschedule_id;
     
     Update programschedule set rooms_shared_booked = rooms_shared_booked + is_shared_room,
	 rooms_single_booked = rooms_single_booked + is_single_room 
	 Where  programschedule_id = prp_programschedule_id;
	 END IF;
     
-- room type not changed start

  ELSE
 
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;

 END IF;     
 
 INSERT INTO `programschedulechangelog`
(`participant_id`,
`from_programschedule_id`,
`to_programschedule_id`,
`reschedulingfee_amount`,
`created_date`,
`created_by`)
VALUES
(prp_participant_id,
prp_old_programschedule_id,
prp_programschedule_id,
reschedulingfee_amount,
ps_changelog_created_date,
prp_updated_by);


      -- Return success code
    SELECT 9999 AS status;
        
END
empower_user@% 2026-02-10 07:39:06 2026-02-10 07:39:06 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN

update participant set notes = P_Notes where participant_id=prp_participant_id;

 IF roomtypechange = 1 THEN

	UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        room_single_requested = is_single_room,
        room_shared_requested = is_shared_room,
        updated_date = NOW(),
        updated_by = prp_updated_by
        
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
     IF prp_registrationstatus_id = 'C' THEN
     UPDATE programschedule
	 SET 
		rooms_shared_booked = 
			CASE 
				WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
				ELSE rooms_shared_booked
			END,
		rooms_single_booked = 
			CASE 
				WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
				ELSE rooms_single_booked
			END
	 WHERE programschedule_id = prp_old_programschedule_id;
     
     Update programschedule set rooms_shared_booked = rooms_shared_booked + is_shared_room,
	 rooms_single_booked = rooms_single_booked + is_single_room 
	 Where  programschedule_id = prp_programschedule_id;
	 END IF;
     
-- room type not changed start

  ELSE
 
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;

 END IF;     
 
 INSERT INTO `programschedulechangelog`
(`participant_id`,
`from_programschedule_id`,
`to_programschedule_id`,
`reschedulingfee_amount`,
`created_date`,
`created_by`)
VALUES
(prp_participant_id,
prp_old_programschedule_id,
prp_programschedule_id,
reschedulingfee_amount,
ps_changelog_created_date,
prp_updated_by);


      -- Return success code
    SELECT 9999 AS status;
        
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_20260210' AND `proc`.`name` = 'sUpdateScheduleForParticipant' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_20260210 sUpdateScheduleForParticipant PROCEDURE sUpdateScheduleForParticipant SQL CONTAINS_SQL NO DEFINER 
IN `prp_programschedule_id` CHAR(8),
 IN `prp_participant_id` INT(10), IN `prp_updated_by` VARCHAR(20), IN `prp_old_programschedule_id` CHAR(8), 
 IN `prp_roomType` VARCHAR(10), IN `prp_registrationstatus_id` CHAR(1) ,IN roomtypechange TINYINT(1),  
    IN is_single_room TINYINT(1),       
    IN is_shared_room TINYINT(1),
    IN reschedulingfee_amount decimal(10,2),
	IN ps_changelog_created_date datetime,
    IN `P_Notes` text
 

BEGIN

update participant set notes = P_Notes where participant_id=prp_participant_id;

 IF roomtypechange = 1 THEN

	UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        room_single_requested = is_single_room,
        room_shared_requested = is_shared_room,
        updated_date = NOW(),
        updated_by = prp_updated_by
        
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
     IF prp_registrationstatus_id = 'C' THEN
     UPDATE programschedule
	 SET 
		rooms_shared_booked = 
			CASE 
				WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
				ELSE rooms_shared_booked
			END,
		rooms_single_booked = 
			CASE 
				WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
				ELSE rooms_single_booked
			END
	 WHERE programschedule_id = prp_old_programschedule_id;
     
     Update programschedule set rooms_shared_booked = rooms_shared_booked + is_shared_room,
	 rooms_single_booked = rooms_single_booked + is_single_room 
	 Where  programschedule_id = prp_programschedule_id;
	 END IF;
     
-- room type not changed start

  ELSE
 
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;

 END IF;     
 
 INSERT INTO `programschedulechangelog`
(`participant_id`,
`from_programschedule_id`,
`to_programschedule_id`,
`reschedulingfee_amount`,
`created_date`,
`created_by`)
VALUES
(prp_participant_id,
prp_old_programschedule_id,
prp_programschedule_id,
reschedulingfee_amount,
ps_changelog_created_date,
prp_updated_by);


      -- Return success code
    SELECT 9999 AS status;
        
END
empower_user@% 2026-02-10 08:24:05 2026-02-10 08:24:05 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN

update participant set notes = P_Notes where participant_id=prp_participant_id;

 IF roomtypechange = 1 THEN

	UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        room_single_requested = is_single_room,
        room_shared_requested = is_shared_room,
        updated_date = NOW(),
        updated_by = prp_updated_by
        
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
     IF prp_registrationstatus_id = 'C' THEN
     UPDATE programschedule
	 SET 
		rooms_shared_booked = 
			CASE 
				WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
				ELSE rooms_shared_booked
			END,
		rooms_single_booked = 
			CASE 
				WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
				ELSE rooms_single_booked
			END
	 WHERE programschedule_id = prp_old_programschedule_id;
     
     Update programschedule set rooms_shared_booked = rooms_shared_booked + is_shared_room,
	 rooms_single_booked = rooms_single_booked + is_single_room 
	 Where  programschedule_id = prp_programschedule_id;
	 END IF;
     
-- room type not changed start

  ELSE
 
    UPDATE programregistrationparticipant
    SET
        programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date = NOW(),
        updated_by = prp_updated_by
        -- , registrationstatus_id='C'
    WHERE
        programschedule_id = prp_old_programschedule_id
	AND participant_id = prp_participant_id ;
     
        
    UPDATE programregistrationpayment
     SET
         programschedule_id=prp_programschedule_id,
        participant_id=prp_participant_id,
        updated_date=now(),
        updated_by = prp_updated_by
        
          WHERE
          programschedule_id = prp_old_programschedule_id
     AND  participant_id = prp_participant_id ;
     
	IF prp_registrationstatus_id = 'C' THEN
    UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' AND prp_registrationstatus_id='C' THEN rooms_shared_booked - 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' AND prp_registrationstatus_id='C' THEN rooms_single_booked - 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_old_programschedule_id;

 UPDATE programschedule
SET 
    rooms_shared_booked = 
        CASE 
            WHEN prp_roomType = 'shared' THEN rooms_shared_booked + 1
            ELSE rooms_shared_booked
        END,
    rooms_single_booked = 
        CASE 
            WHEN prp_roomType = 'single' THEN rooms_single_booked + 1
            ELSE rooms_single_booked
        END
WHERE programschedule_id = prp_programschedule_id;
END IF;

 END IF;     
 
 INSERT INTO `programschedulechangelog`
(`participant_id`,
`from_programschedule_id`,
`to_programschedule_id`,
`reschedulingfee_amount`,
`created_date`,
`created_by`)
VALUES
(prp_participant_id,
prp_old_programschedule_id,
prp_programschedule_id,
reschedulingfee_amount,
ps_changelog_created_date,
prp_updated_by);


      -- Return success code
    SELECT 9999 AS status;
        
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'empower_dev' AND `proc`.`name` = 'sGetProgramSchedule' AND `proc`.`type` = 'PROCEDURE'
 		empower_dev sGetProgramSchedule PROCEDURE sGetProgramSchedule SQL CONTAINS_SQL NO DEFINER 
IN `programTypeId` VARCHAR(10), IN `programScheduleStatusId` VARCHAR(3)

BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date asc;
END
empower_user@% 2025-10-28 06:45:14 2025-10-28 06:45:14 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date asc;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev' AND `proc`.`name` = 'sGetProgramSchedule' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev sGetProgramSchedule PROCEDURE sGetProgramSchedule SQL CONTAINS_SQL NO DEFINER 
IN `programTypeId` VARCHAR(10), IN `programScheduleStatusId` VARCHAR(3)

BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date desc;
END
empower_user@% 2026-02-10 08:03:13 2026-02-10 08:03:13 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date desc;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev' AND `proc`.`name` = 'sGetSchedulesByProgramStatusAndDateRange' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev sGetSchedulesByProgramStatusAndDateRange PROCEDURE sGetSchedulesByProgramStatusAndDateRange SQL CONTAINS_SQL NO DEFINER 
IN `programTypeId` VARCHAR(10), IN `programScheduleStatusId` VARCHAR(3)

BEGIN
  
	-- DECLARE startDate DATE;
   -- DECLARE endDate DATE;
    
   -- Start date: 12 months before today
   -- SET startDate = DATE_SUB(CURDATE(), INTERVAL 12 MONTH);

    -- End date: 6 months after today
   -- SET endDate = DATE_ADD(CURDATE(), INTERVAL 6 MONTH);
    
    SELECT ps.*, c.city_name,
    s.programschedulestatus_desc,
    DATE_FORMAT(ps.start_date, '%m/%d/%Y') as startDateDisplay
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    LEFT JOIN programschedulestatus s ON ps.programschedulestatus_id = s.programschedulestatus_id
    WHERE 
    (
            programTypeId = 'all'
            OR ps.programtype_id = programTypeId
        )
    -- ps.programtype_id = programTypeId
        AND (
            programScheduleStatusId = 'all'
            OR ps.programschedulestatus_id = programScheduleStatusId
        )
      --  AND ps.start_date >= startDate
      --  AND ps.end_date <= endDate
    ORDER BY ps.start_date DESC;
END
empower_user@% 2026-02-10 08:03:37 2026-02-10 08:03:37 NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTIO... utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
  
	-- DECLARE startDate DATE;
   -- DECLARE endDate DATE;
    
   -- Start date: 12 months before today
   -- SET startDate = DATE_SUB(CURDATE(), INTERVAL 12 MONTH);

    -- End date: 6 months after today
   -- SET endDate = DATE_ADD(CURDATE(), INTERVAL 6 MONTH);
    
    SELECT ps.*, c.city_name,
    s.programschedulestatus_desc,
    DATE_FORMAT(ps.start_date, '%m/%d/%Y') as startDateDisplay
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    LEFT JOIN programschedulestatus s ON ps.programschedulestatus_id = s.programschedulestatus_id
    WHERE 
    (
            programTypeId = 'all'
            OR ps.programtype_id = programTypeId
        )
    -- ps.programtype_id = programTypeId
        AND (
            programScheduleStatusId = 'all'
            OR ps.programschedulestatus_id = programScheduleStatusId
        )
      --  AND ps.start_date >= startDate
      --  AND ps.end_date <= endDate
    ORDER BY ps.start_date DESC;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_migration' AND `proc`.`name` = 'sGetProgramSchedule' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_migration sGetProgramSchedule PROCEDURE sGetProgramSchedule SQL CONTAINS_SQL NO DEFINER 
IN `programTypeId` VARCHAR(10), IN `programScheduleStatusId` VARCHAR(3)

BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date desc;
END
empower_user@% 2025-10-30 05:30:11 2025-10-30 05:30:11 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_unicode_ci utf8mb4_general_ci 
BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date desc;
END
NONE
Edit Edit Copy Copy Delete Delete
DELETE FROM proc WHERE `proc`.`db` = 'db_empower_dev_old' AND `proc`.`name` = 'sGetProgramSchedule' AND `proc`.`type` = 'PROCEDURE'
 		db_empower_dev_old sGetProgramSchedule PROCEDURE sGetProgramSchedule SQL CONTAINS_SQL NO DEFINER 
IN `programTypeId` VARCHAR(10), IN `programScheduleStatusId` VARCHAR(3)

BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date asc;
END
empower_user@% 2025-12-03 05:48:46 2025-12-03 05:48:46 NO_AUTO_VALUE_ON_ZERO utf8mb4 utf8mb4_general_ci utf8mb4_general_ci 
BEGIN
    SELECT ps.*, c.city_name
    FROM programschedule ps
    LEFT JOIN city c ON ps.city_id = c.city_id
    WHERE ps.programtype_id = programTypeId 
       AND (
        programScheduleStatusId = 'all'
        OR ps.programschedulestatus_id = programScheduleStatusId
      )
      
      order by ps.start_date asc;
END
NONE
With selected: With selected:
|
|
|
|
Filter rows:
Sort by key:
Query results operations Copy to clipboard Copy to clipboard Export Export Display chart Display chart Create view Create view
Bookmark this SQL query Bookmark this SQL query