IN `PS_ScheduleID` VARCHAR(8), IN `PS_Name` VARCHAR(85), 
IN `PT_ID` VARCHAR(2), IN `PS_StatusID` VARCHAR(2),
 IN `PS_StartDate` DATE, IN `PS_EndDate` DATE, 
 IN `PS_StartTime` TIME, IN `PS_EndTime` TIME, 
 IN `PS_RoomSingleCost` DECIMAL(10,2), 
 IN `PS_RoomSharedCost` DECIMAL(10,2), IN `PS_RoomSharedAllocated` INT(10), 
 IN `PS_RoomSingleAllocated` INT(10), IN `PS_UpdatedBy` VARCHAR(20), 
 IN `PS_Address` VARCHAR(80), IN  `PS_CityId` char(6), 
 IN `PS_StateID` CHAR(2), IN `PS_zipcode` CHAR(5), Out `status` int(4)

BEGIN
   set status = 0000;
 
 UPDATE programschedule
    SET
        programschedule_name = PS_Name,
        programtype_id = PT_ID,
        programschedulestatus_id = PS_StatusID,
        start_date = PS_StartDate,
        end_date = PS_EndDate,
        start_time = PS_StartTime,
        end_time = PS_EndTime,
        room_single_cost = PS_RoomSingleCost,
        room_shared_cost = PS_RoomSharedCost,
        rooms_shared_allocated = PS_RoomSharedAllocated,
        rooms_single_allocated = PS_RoomSingleAllocated,
        updated_date = NOW(),
        updated_by = PS_UpdatedBy,
        address = PS_Address,
        city_id =PS_CityId,
        state_id = PS_StateID,
        zipcode = PS_zipcode
    WHERE programschedule_id = PS_ScheduleID;

    -- Return success code
    set status =  9999;
   
    
END

IN `PS_ID` CHAR(8), 
IN `PS_NewStatusID` VARCHAR(2), IN `PS_UpdatedBy` VARCHAR(20), Out `status` int(4)

BEGIN
  set status = 0000;
    UPDATE programschedule
    SET programschedulestatus_id = PS_NewStatusID,
        updated_date = NOW(),
        updated_by = PS_UpdatedBy
    WHERE programschedule_id = PS_ID;

     set status =  9999;
    
END

IN P_FirstName VARCHAR(40), IN P_LastName VARCHAR(40),
 IN P_MiddleName VARCHAR(40), IN P_DOB DATE, IN P_Gender CHAR(1), IN P_SSNLastFourDigits CHAR(4),
 IN P_PhoneNumber VARCHAR(10), IN P_Email VARCHAR(40), IN P_Address VARCHAR(80), IN P_CityId VARCHAR(6),
 IN P_StateId CHAR(2), IN P_Zipcode CHAR(5), IN P_Medications TEXT, IN P_SpecialDiet TEXT, IN P_Allergies TEXT,
 IN P_MedicalNeeds TEXT, IN P_MedicalNeedsDetails TEXT, IN P_Pregnancy TINYINT(1), IN P_Smoker TINYINT(1),
 IN P_PriorTreatmentDrugAlcohol TINYINT(1), IN P_DlNumber VARCHAR(20), IN P_DlIssuedState CHAR(2),
 IN P_DlSuspended CHAR(1), IN P_DlPoints INT(2), IN P_EmergencyContactName VARCHAR(80),
 IN P_EmergencyContactPhoneNumber VARCHAR(10), IN P_EmergencyContactRelationship VARCHAR(50),
 IN P_CreatedBy VARCHAR(20), IN P_UpdatedBy VARCHAR(20), IN C_CourtId VARCHAR(3), IN C_CourtOther VARCHAR(40),
 IN C_CaseNumber VARCHAR(30), IN C_BloodAlcoholContent DECIMAL(4,3), IN R_ProgramScheduleId CHAR(8),
 IN R_RegistrationStatusId CHAR(1), IN R_RoomSingleBooked INT(10), IN R_RoomSharedBooked INT(10),
 IN Pay_PaymentOptionId CHAR(1), IN Pay_PaymentTypeId CHAR(1), IN Pay_PaymentStatusId CHAR(2),
 IN Pay_PaymentAmount DECIMAL(10,2), IN Pay_BillingName VARCHAR(40), IN Pay_BillingAddress VARCHAR(80),
 IN Pay_BillingZipcode CHAR(5), IN Pay_BillingPhoneNumber VARCHAR(10), IN Pay_TransactionId VARCHAR(100),
 IN Pay_MODate DATETIME, IN Pay_MONumber VARCHAR(15), IN Pay_CardNumber CHAR(4),
 IN ReceiptNumber VARCHAR(10), IN EncryptedReceiptNumber VARCHAR(50),
 out status int(4), out currentDateTime datetime

BEGIN
DECLARE New_ParticipantId INT; 
DECLARE RoomAvailable INT;
DECLARE last_receipt_id VARCHAR(30);


 
DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
	ROLLBACK;
	set status = 9997;
END;

	SELECT parameter_value INTO last_receipt_id
		FROM setting WHERE parameter_name = 'ReceiptNumber_LastUsed';

  set status = 9999;
  set currentDateTime = now();

 
  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_SSNLastFourDigits
AND p.phonenumber = P_PhoneNumber
AND p.email = P_Email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C', 'N')) THEN
		-- ROLLBACK;
        set status = 9998;
		LEAVE main_block;
	-- else
     END IF;
    
     START TRANSACTION;
	IF R_RoomSharedBooked = 1 then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule Where  programschedule_id = R_ProgramScheduleId ;
	ELSEIF R_RoomSingleBooked = 1 then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule Where  programschedule_id = R_ProgramScheduleId ;
	END IF;
    
      
    -- Update the room booking in program schedule table
    IF RoomAvailable > 0 AND Pay_PaymentStatusId = 'SU' then
		Update programschedule set rooms_shared_booked = rooms_shared_booked + R_RoomSharedBooked,
		rooms_single_booked = rooms_single_booked + R_RoomSingleBooked 
		Where  programschedule_id = R_ProgramScheduleId;
        
	END IF;

    -- Insert into participant
    INSERT INTO participant (
        firstname, lastname, middlename, dob, gender, ssn_lastfourdigits,
        phonenumber, email, address, city_id, state_id, zipcode,
        medications, special_diet, allergies, medical_needs, medical_needs_details,
        pregnancy, smoker, prior_treatment_drugalcohol,
        dl_number, dl_issued_state, dl_suspended, dl_points,
        created_date, created_by, updated_date, updated_by,
        emergencycontact_name, emergencycontact_phoneNumber, emergencycontact_relationship
    ) VALUES (
        P_FirstName, P_LastName, P_MiddleName, P_DOB, P_Gender, P_SSNLastFourDigits,
        P_PhoneNumber, P_Email, P_Address, P_CityId, P_StateId, P_Zipcode,
        P_Medications, P_SpecialDiet, P_Allergies, P_MedicalNeeds, P_MedicalNeedsDetails,
        P_Pregnancy, P_Smoker, P_PriorTreatmentDrugAlcohol,
        P_DlNumber, P_DlIssuedState, P_DlSuspended, P_DlPoints,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy,
        P_EmergencyContactName, P_EmergencyContactPhoneNumber, P_EmergencyContactRelationship
    );
    -- Get new participant_id
    SET New_ParticipantId = LAST_INSERT_ID();
    -- Insert into participantcourtcase
    INSERT INTO participantcourtcase (
        participant_id, court_id, court_other, case_number, blood_alcohol_content,
        created_date, created_by, updated_date, updated_by
    ) VALUES (
        New_ParticipantId, C_CourtId, C_CourtOther, C_CaseNumber, C_BloodAlcoholContent,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy
    );
    -- Insert into programregistrationparticipant
    IF RoomAvailable > 0 then
		INSERT INTO programregistrationparticipant (
			programschedule_id, participant_id, registrationstatus_id, 
			room_single_requested, room_shared_requested,
			created_date, created_by, updated_date, updated_by
		) VALUES (
			R_ProgramScheduleId, New_ParticipantId, R_RegistrationStatusId, 
			R_RoomSingleBooked, R_RoomSharedBooked,
			NOW(), P_CreatedBy, NOW(), P_UpdatedBy
		);
	ELSEIF RoomAvailable <= 0 then
		INSERT INTO programregistrationparticipant (
			programschedule_id, participant_id, registrationstatus_id, 
			room_single_requested, room_shared_requested,
			created_date, created_by, updated_date, updated_by
		) VALUES (
			R_ProgramScheduleId, New_ParticipantId, "N", 
			R_RoomSingleBooked, R_RoomSharedBooked,
			NOW(), P_CreatedBy, NOW(), P_UpdatedBy
		);
	END IF;
    -- Insert into programregistrationpayment
    INSERT INTO programregistrationpayment (
        programschedule_id, participant_id, payment_date, paymenttype_id, paymentstatus_id,
        paymentoption_id, payment_amount, billing_name,  billing_address,
        billing_zipcode, billing_phonenumber, transaction_id,
       mo_date, mo_number, creditcard_lastfourdigits, receipt_number,
        created_date, created_by, updated_date, updated_by
    ) VALUES (
        R_ProgramScheduleId, New_ParticipantId, NOW(), Pay_PaymentTypeId, Pay_PaymentStatusId,
        Pay_PaymentOptionId, Pay_PaymentAmount, Pay_BillingName,  Pay_BillingAddress,
        Pay_BillingZipcode, Pay_BillingPhoneNumber, Pay_TransactionId,
        Pay_MODate, Pay_MONumber,Pay_CardNumber,ReceiptNumber,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy
    );
    
    
     UPDATE setting
	SET parameter_value = EncryptedReceiptNumber,
		created_by = IF(last_receipt_id IS NULL OR last_receipt_id = '', P_CreatedBy, created_by),
		created_date = IF(last_receipt_id IS NULL OR last_receipt_id = '', NOW(), created_date),
		updated_by = P_UpdatedBy,
		updated_date = NOW()
	WHERE parameter_name = 'ReceiptNumber_LastUsed';
    
       COMMIT;
       IF RoomAvailable <= 0 then
       		set status = 9994;
		END IF;
	-- END IF;
     END main_block; 
END

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

IN P_FirstName VARCHAR(40), IN P_LastName VARCHAR(40),
 IN P_MiddleName VARCHAR(40), IN P_DOB DATE, IN P_Gender CHAR(1), IN P_SSNLastFourDigits CHAR(4),
 IN P_PhoneNumber VARCHAR(10), IN P_Email VARCHAR(40), IN P_Address VARCHAR(80), IN P_CityId VARCHAR(6),
 IN P_StateId CHAR(2), IN P_Zipcode CHAR(5), IN P_Medications TEXT, IN P_SpecialDiet TEXT, IN P_Allergies TEXT,
 IN P_MedicalNeeds TEXT, IN P_Pregnancy TINYINT(1), IN P_Smoker TINYINT(1),
 IN P_PriorTreatmentDrugAlcohol TINYINT(1), IN P_DlNumber VARCHAR(20), IN P_DlIssuedState CHAR(2),
 IN P_DlSuspended CHAR(1), IN P_DlPoints INT(2), IN P_EmergencyContactName VARCHAR(80),
 IN P_EmergencyContactPhoneNumber VARCHAR(10), IN P_EmergencyContactRelationship VARCHAR(50),
 IN P_CreatedBy VARCHAR(20), IN P_UpdatedBy VARCHAR(20), IN C_CourtId VARCHAR(3), IN C_CourtOther VARCHAR(40),
 IN C_CaseNumber VARCHAR(30), IN C_BloodAlcoholContent DECIMAL(4,3), IN R_ProgramScheduleId CHAR(8),
 IN R_RegistrationStatusId CHAR(1), IN R_RoomSingleBooked INT(10), IN R_RoomSharedBooked INT(10),
 IN Pay_PaymentOptionId CHAR(1), IN Pay_PaymentTypeId CHAR(1), IN Pay_PaymentStatusId CHAR(2),
 IN Pay_PaymentAmount DECIMAL(10,2), IN Pay_BillingName VARCHAR(40), IN Pay_BillingAddress VARCHAR(80),
 IN Pay_BillingZipcode CHAR(5), IN Pay_BillingPhoneNumber VARCHAR(10), IN Pay_TransactionId VARCHAR(100),
 IN Pay_MODate DATETIME, IN Pay_MONumber VARCHAR(15), IN Pay_CardNumber CHAR(4),
 IN ReceiptNumber VARCHAR(10), IN EncryptedReceiptNumber VARCHAR(50),
 IN P_CourtOrder TINYINT(1),
 out status int(4), out currentDateTime datetime

BEGIN
DECLARE New_ParticipantId INT; 
DECLARE RoomAvailable INT;
DECLARE last_receipt_id VARCHAR(30);


 
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN
      ROLLBACK;
       set status = 9997;
	 END;

	SELECT parameter_value INTO last_receipt_id
		FROM setting WHERE parameter_name = 'ReceiptNumber_LastUsed';

  set status = 9999;
  set currentDateTime = now();

 
  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_SSNLastFourDigits
AND p.phonenumber = P_PhoneNumber
AND p.email = P_Email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C', 'N')) THEN
		-- ROLLBACK;
        set status = 9998;
		LEAVE main_block;
	-- else
     END IF;
    
     START TRANSACTION;
	IF R_RoomSharedBooked = 1 then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule Where  programschedule_id = R_ProgramScheduleId ;
	ELSEIF R_RoomSingleBooked = 1 then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule Where  programschedule_id = R_ProgramScheduleId ;
	END IF;
    
      
    -- Update the room booking in program schedule table
    IF RoomAvailable > 0 AND Pay_PaymentStatusId = 'SU' then
		Update programschedule set rooms_shared_booked = rooms_shared_booked + R_RoomSharedBooked,
		rooms_single_booked = rooms_single_booked + R_RoomSingleBooked 
		Where  programschedule_id = R_ProgramScheduleId;
        
	END IF;

    -- Insert into participant
    INSERT INTO participant (
        firstname, lastname, middlename, dob, gender, ssn_lastfourdigits,
        phonenumber, email, address, city_id, state_id, zipcode,
        medications, special_diet, allergies, medical_needs,
        pregnancy, smoker, prior_treatment_drugalcohol,
        dl_number, dl_issued_state, dl_suspended, dl_points,
        created_date, created_by, updated_date, updated_by,
        emergencycontact_name, emergencycontact_phoneNumber,
        emergencycontact_relationship, blood_alcohol_content, court_order
    ) VALUES (
        P_FirstName, P_LastName, P_MiddleName, P_DOB, P_Gender, P_SSNLastFourDigits,
        P_PhoneNumber, P_Email, P_Address, P_CityId, P_StateId, P_Zipcode,
        P_Medications, P_SpecialDiet, P_Allergies, P_MedicalNeeds,
        P_Pregnancy, P_Smoker, P_PriorTreatmentDrugAlcohol,
        P_DlNumber, P_DlIssuedState, P_DlSuspended, P_DlPoints,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy,
        P_EmergencyContactName, P_EmergencyContactPhoneNumber,
        P_EmergencyContactRelationship, C_BloodAlcoholContent, P_CourtOrder
    );
    -- Get new participant_id
    SET New_ParticipantId = LAST_INSERT_ID();
    -- Insert into participantcourtcase
    IF P_CourtOrder=1 THEN
    INSERT INTO participantcourtcase (
        participant_id, court_id, court_other, case_number,
        created_date, created_by, updated_date, updated_by
    ) VALUES (
        New_ParticipantId, C_CourtId, C_CourtOther, C_CaseNumber,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy
    );
    END IF;
    -- Insert into programregistrationparticipant
    IF RoomAvailable > 0 then
		INSERT INTO programregistrationparticipant (
			programschedule_id, participant_id, registrationstatus_id, 
			room_single_requested, room_shared_requested,
			created_date, created_by, updated_date, updated_by
		) VALUES (
			R_ProgramScheduleId, New_ParticipantId, R_RegistrationStatusId, 
			R_RoomSingleBooked, R_RoomSharedBooked,
			NOW(), P_CreatedBy, NOW(), P_UpdatedBy
		);
	ELSEIF RoomAvailable <= 0 then
		INSERT INTO programregistrationparticipant (
			programschedule_id, participant_id, registrationstatus_id, 
			room_single_requested, room_shared_requested,
			created_date, created_by, updated_date, updated_by
		) VALUES (
			R_ProgramScheduleId, New_ParticipantId, "N", 
			R_RoomSingleBooked, R_RoomSharedBooked,
			NOW(), P_CreatedBy, NOW(), P_UpdatedBy
		);
	END IF;
    -- Insert into programregistrationpayment
    INSERT INTO programregistrationpayment (
        programschedule_id, participant_id, payment_date, paymenttype_id, paymentstatus_id,
        paymentoption_id, payment_amount, billing_name,  billing_address,
        billing_zipcode, billing_phonenumber, transaction_id,
       mo_date, mo_number, creditcard_lastfourdigits, receipt_number,
        created_date, created_by, updated_date, updated_by
    ) VALUES (
        R_ProgramScheduleId, New_ParticipantId, NOW(), Pay_PaymentTypeId, Pay_PaymentStatusId,
        Pay_PaymentOptionId, Pay_PaymentAmount, Pay_BillingName,  Pay_BillingAddress,
        Pay_BillingZipcode, Pay_BillingPhoneNumber, Pay_TransactionId,
        Pay_MODate, Pay_MONumber,Pay_CardNumber,ReceiptNumber,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy
    );
    
    
     UPDATE setting
	SET parameter_value = EncryptedReceiptNumber,
		created_by = IF(last_receipt_id IS NULL OR last_receipt_id = '', P_CreatedBy, created_by),
		created_date = IF(last_receipt_id IS NULL OR last_receipt_id = '', NOW(), created_date),
		updated_by = P_UpdatedBy,
		updated_date = NOW()
	WHERE parameter_name = 'ReceiptNumber_LastUsed';
    
       COMMIT;
       IF RoomAvailable <= 0 then
       		set status = 9994;
		END IF;
	-- END IF;
     END main_block; 
END

IN `ProgramId` CHAR(2), IN `ScheduleId` CHAR(8), IN `StartDate` DATE, IN `EndDate` DATE, IN `ProgramScheduleStatusId` VARCHAR(3)

BEGIN

	Select DISTINCT programtype_id, programtype_name as program, programschedule_id, programschedule_name as schedule,
    DATE_FORMAT(start_date, '%m/%d/%Y') as startDate, DATE_FORMAT(end_date, '%m/%d/%Y') as endDate,
    programschedulestatus_id as programScheduleStatusId,
    DATE_FORMAT(start_time, '%l:%i %p') AS startTime,  -- 12-hour format with AM/PM
  DATE_FORMAT(end_time, '%l:%i %p') AS endTime,
  CONCAT(address, ', ', city_name, ', ', state_id, ' ', zipcode) AS address,
  programschedulestatus_desc AS statusDesc
    from vScheduleParticipantDetails 
    Where (programtype_id = ProgramId or ProgramId = '') 
    and (programschedule_id = ScheduleId or ScheduleId = '')
	AND (((StartDate != '' AND EndDate != '')
	AND (start_date <= EndDate AND end_date >= StartDate))
    OR StartDate = '' OR EndDate = '')
    and (programschedulestatus_id = ProgramScheduleStatusId or ProgramScheduleStatusId = '')
    order by start_date desc;


END

IN `P_ZipCode` CHAR(5), out status int(4)

BEGIN
	set status = 0000;
    
    IF EXISTS (SELECT 1 FROM zipcode
    WHERE zipcode = P_ZipCode) THEN
        set status =  9999; 
    ELSE
        set status = 9995; 
    END IF;
END

IN `PS_UserId` VARCHAR(20), IN `PS_Password` VARCHAR(40)

BEGIN
UPDATE user
  SET password = PS_Password
  WHERE user_id = PS_UserId;
END

IN `ProgramId` CHAR(2), IN `ScheduleStatusId` CHAR(2), IN `ScheduleId` CHAR(8), IN `StartDate` DATE, IN `EndDate` DATE,IN `OrderingCourtId` VARCHAR(3)

BEGIN
    -- Total Programs
    SELECT COUNT(DISTINCT programtype_id) AS total_programs
    FROM vScheduleParticipantDetails
     WHERE (programtype_id = ProgramId OR ProgramId = '')
	 and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
     AND (programschedule_id = ScheduleId OR ScheduleId = '')
	 AND (
          ((StartDate != '' AND EndDate != '')
             AND (start_date <= EndDate AND end_date >= StartDate))
          OR StartDate = '' OR EndDate = ''
         )
	 AND (court_id = OrderingCourtId or OrderingCourtId = '');
      
	-- Total Schedules - Completed
    SELECT COUNT(DISTINCT programschedule_id) AS total_schedules_completed
    FROM vScheduleParticipantDetails
    WHERE programschedulestatus_id = 'CO'
      AND (programtype_id = ProgramId OR ProgramId = '')
      and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
      AND (programschedule_id = ScheduleId OR ScheduleId = '')
      AND (
            ((StartDate != '' AND EndDate != '')
              AND (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
           )
	  AND (court_id = OrderingCourtId or OrderingCourtId = '');
      
	-- Total Schedules - Open
    SELECT COUNT(DISTINCT programschedule_id) AS total_schedules_open
    FROM vScheduleParticipantDetails
    WHERE programschedulestatus_id = 'OP'
     AND (programtype_id = ProgramId OR ProgramId = '')
     AND (programschedule_id = ScheduleId OR ScheduleId = '')
     and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
     AND (
           ((StartDate != '' AND EndDate != '')
             AND (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
          )
	 AND (court_id = OrderingCourtId or OrderingCourtId = '');
      
	-- Total Participants
    SELECT COUNT(DISTINCT participant_id) AS total_participants
    FROM vScheduleParticipantDetails
    WHERE  (programtype_id = ProgramId OR ProgramId = '')
	 and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
      AND (programschedule_id = ScheduleId OR ScheduleId = '')
      AND (
	      ((StartDate != '' AND EndDate != '')
             AND (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
          )
	  AND (court_id = OrderingCourtId or OrderingCourtId = '')
      AND firstname IS NOT NULL
      AND lastname IS NOT NULL
      AND phonenumber IS NOT NULL;
      
		-- Schedule Open after start date
        SELECT COUNT(DISTINCT programschedule_id) AS schedules_due
		FROM vScheduleParticipantDetails
		WHERE programschedulestatus_id = 'OP' 
        and (start_date <= now());
      
	-- Participants by Program 
		SELECT 
		  pt.programtype_name AS program_name,
		  COUNT(DISTINCT v.participant_id) AS participant_count
		FROM programtype pt
		LEFT JOIN vScheduleParticipantDetails v
		  ON v.programtype_id = pt.programtype_id
		  AND (v.programtype_id = ProgramId OR ProgramId = '')
		  AND (
				((StartDate != '' AND EndDate != '')
				  AND (v.start_date <= EndDate AND v.end_date >= StartDate))
				OR StartDate = '' OR EndDate = ''
			  )
		  AND (court_id = OrderingCourtId or OrderingCourtId = '')
		  AND v.firstname IS NOT NULL
		  AND v.lastname IS NOT NULL
		  AND v.phonenumber IS NOT NULL
		GROUP BY pt.programtype_name
        order by pt.programtype_name;
      
	-- Participants by Schedule Status
		SELECT
		  s.programschedulestatus_desc AS schedule_status,
		  COUNT(DISTINCT v.participant_id) AS participant_count
		FROM programschedulestatus s
		LEFT JOIN vScheduleParticipantDetails v
		  ON v.programschedulestatus_id = s.programschedulestatus_id
		  and (v.programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
		  AND (v.programschedule_id = ScheduleId OR ScheduleId = '')
		  AND (
				((StartDate != '' AND EndDate != '')
				 AND (v.start_date <= EndDate AND v.end_date >= StartDate))
			   OR StartDate = '' OR EndDate = ''
			  )
		  AND (court_id = OrderingCourtId or OrderingCourtId = '')
		GROUP BY s.programschedulestatus_desc
		ORDER BY s.programschedulestatus_desc;
    
	-- Schedules by Program
		SELECT 
		  pt.programtype_name AS program_name,
		  COUNT(DISTINCT v.programschedule_id) AS schedule_count
		FROM programtype pt
		LEFT JOIN vScheduleParticipantDetails v
		  ON v.programtype_id = pt.programtype_id
		  AND (v.programtype_id = ProgramId OR ProgramId = '')
		  and (v.programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
		  AND (v.programschedule_id = ScheduleId OR ScheduleId = '')
		  AND (
				((StartDate != '' AND EndDate != '')
				  AND (v.start_date <= EndDate AND v.end_date >= StartDate))
				OR StartDate = '' OR EndDate = ''
			  )
		GROUP BY pt.programtype_name
        order by pt.programtype_name;
END

    IN TransactionTypeId char(1),
    IN paymentTypeId CHAR(1),
    IN paymentStartDate DATE,
    IN paymentEndDate DATE,
    IN ProgramId CHAR(2),
    IN programScheduleStatusId VARCHAR(2)

BEGIN

    -- 1. Confirmed Registrations with Pending Payments
    SELECT 
        COUNT(DISTINCT participant_id) AS ConfirmedPendingPayments
    FROM (
        SELECT 
            participant_id,
            programschedule_id,
            CASE 
                WHEN room_single_requested = 1 THEN room_single_cost
                WHEN room_shared_requested = 1 THEN room_shared_cost
                ELSE 0
            END AS room_cost,
            amount_paid
        FROM vpaymentdetails
        WHERE 
            registrationstatus_id = 'C'
			AND paymentstatus_id = 'SU'
          
    ) AS filtered
    WHERE amount_paid < room_cost;

    -- 2. New Registrations with successful Payments   
    SELECT 
        COUNT(DISTINCT participant_id) AS NewRegistrationWithPayments
    FROM vpaymentdetails
    WHERE registrationstatus_id = 'N'
		AND paymentstatus_id = 'SU'
		AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId);
	

    -- 3. Total MO Payments – Count & Amount
    SELECT 
        COUNT(participant_id) AS MOPaymentCount,
        IFNULL(SUM(payment_amount), 0) AS MOPaymentAmount
    FROM vpaymentdetails
    WHERE 
	     ((paymentTypeId = '' AND paymenttype_id = 'M') or (paymentTypeId = 'M' AND paymenttype_id = paymentTypeId) )
		AND ((TransactionTypeId = 0 OR TransactionTypeId = '') AND paymentoption_id IN ('D', 'F', 'O'))       
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
        AND ((paymentStartDate = '' AND paymentEndDate = '') OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));

    -- 4. Total Cash Payments – Count & Amount
    SELECT 
		COUNT(participant_id) AS CashPaymentCount,
        IFNULL(SUM(payment_amount), 0) AS CashPaymentAmount
    FROM vpaymentdetails
    WHERE
		 ((paymentTypeId = '' AND paymenttype_id = 'C') or (paymentTypeId = 'C' AND paymenttype_id = paymentTypeId) )
		-- AND ( TransactionTypeId = '' OR refund  = TransactionTypeId )
        AND ((TransactionTypeId = 0 OR TransactionTypeId = '') AND paymentoption_id IN ('D', 'F', 'O'))       
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
       AND ((paymentStartDate = '' AND paymentEndDate = '') OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));

    -- 5. Total Credit Card Payments – Count & Amount
    SELECT 
		COUNT(participant_id) AS CardPaymentCount,
        IFNULL(SUM(payment_amount), 0) AS CardPaymentAmount
    FROM vpaymentdetails
    WHERE 
	 ((paymentTypeId = '' AND paymenttype_id = 'D') or (paymentTypeId = 'D' AND paymenttype_id = paymentTypeId) )
       AND ((TransactionTypeId = 0 OR TransactionTypeId = '') AND paymentoption_id IN ('D', 'F', 'O'))       
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
        AND ((paymentStartDate = '' AND paymentEndDate = '') OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));
        
        -- 6. Total check refund
    SELECT 
		COUNT(participant_id) AS CheckRefundCount,
        IFNULL(SUM(payment_amount), 0) AS CheckRefundAmount
    FROM vpaymentdetails
    WHERE 
	    ((paymentTypeId = '' AND paymenttype_id = 'K') or (paymentTypeId = 'K' AND paymenttype_id = paymentTypeId) )
       	AND (TransactionTypeId IN (1, '') AND paymentoption_id = 'R')
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
     AND ((paymentStartDate = '' AND paymentEndDate = '') OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));
     
     -- 7. Total cash refund
    SELECT 
		COUNT(participant_id) AS CashRefundCount,
        IFNULL(SUM(payment_amount), 0) AS CashRefundAmount
    FROM vpaymentdetails
    WHERE 
	    ((paymentTypeId = '' AND paymenttype_id = 'C') or (paymentTypeId = 'C' AND paymenttype_id = paymentTypeId) )
  	    AND (TransactionTypeId IN (1, '') AND paymentoption_id = 'R')
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
     AND ((paymentStartDate = '' AND paymentEndDate = '') OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));
     
      -- 8. Total card refund
    SELECT 
		COUNT(participant_id) AS CardRefundCount,
        IFNULL(SUM(payment_amount), 0) AS CardRefundAmount
    FROM vpaymentdetails
    WHERE 
	    ((paymentTypeId = '' AND paymenttype_id = 'D') or (paymentTypeId = 'D' AND paymenttype_id = paymentTypeId) )
	    AND (TransactionTypeId IN (1, '') AND paymentoption_id = 'R')
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
     AND ((paymentStartDate = '' AND paymentEndDate = '') OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate)); 
    END

IN `emailName` VARCHAR(50)

BEGIN
	SELECT * FROM emailnotification where emailnotification_name = emailName;
END

BEGIN
    UPDATE programschedule
    SET programschedulestatus_id = 'CO', updated_date = NOW(), updated_by = 'System'  
    WHERE CURDATE() >= end_date
      AND (programschedulestatus_id = 'OP' OR programschedulestatus_id = 'CL');
END

BEGIN
 SELECT parameter_value 
    FROM setting WHERE parameter_name = 'ReceiptNumber_LastUsed';
END

IN `ProgramId` CHAR(2), IN `ScheduleId` CHAR(8), IN `StartDate` DATE, IN `EndDate` DATE,
 IN `ProgramScheduleStatusId` VARCHAR(3), IN `OrderingCourtId` VARCHAR(3), IN `RegistrationStatus` CHAR(1)

BEGIN

	Select DISTINCT programtype_id, programtype_name as program, programschedule_id, programschedule_name as schedule,
    DATE_FORMAT(start_date, '%m/%d/%Y') as startDate, DATE_FORMAT(end_date, '%m/%d/%Y') as endDate,
    programschedulestatus_id as programScheduleStatusId,
    DATE_FORMAT(start_time, '%l:%i %p') AS startTime,  -- 12-hour format with AM/PM
  DATE_FORMAT(end_time, '%l:%i %p') AS endTime,
  CONCAT(address, ', ', city_name, ', ', state_id, ' ', zipcode) AS address,
  programschedulestatus_desc AS statusDesc,
  rooms_shared_allocated, rooms_single_allocated,
  programschedulestatus_id,
  COUNT(
  DISTINCT CASE
    WHEN registrationstatus_id  <> 'L'
    THEN participant_id
  END
) AS totalParticipants, 
	CASE 
    WHEN programschedulestatus_id = 'OP'
         AND start_date <= NOW()
    THEN 1 ELSE 0
END AS is_schedules_due,
  -- Fully Paid 
        SUM(
            CASE 
                WHEN  registrationstatus_id  <> 'L'
                and amount_paid >= total_amount_due 
                THEN 1 ELSE 0 
            END
        ) AS fullyPaid,
 
        -- Partially Paid 
        SUM(
            CASE 
                WHEN registrationstatus_id  <> 'L'
                and ((amount_paid > 0 and amount_paid < total_amount_due))
                THEN 1 ELSE 0 
            END
        ) AS partiallyPaid,
		SUM(
            CASE 
                WHEN amount_paid >= total_amount_due 
                THEN 1 ELSE 0 
            END
        ) AS fullyPaidCountWithAllStatus,
 
        -- Partially Paid 
        SUM(
            CASE 
                WHEN (amount_paid > 0 and amount_paid < total_amount_due)
                THEN 1 ELSE 0 
            END
        ) AS partiallyPaidCountWithAllStatus,
		sharedRoomAvailable,
        singleRoomAvailable,
        created_date,
        updated_date
        
    from vScheduleParticipantDetails 
    Where (programtype_id = ProgramId or ProgramId = '') 
    and (programschedule_id = ScheduleId or ScheduleId = '')
	AND (((StartDate != '' AND EndDate != '')
	AND (start_date <= EndDate AND end_date >= StartDate))
    OR StartDate = '' OR EndDate = '')
    and (programschedulestatus_id = ProgramScheduleStatusId or ProgramScheduleStatusId = '')
	and (court_id = OrderingCourtId or OrderingCourtId = '')
	and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '')
    GROUP BY
    programschedule_id
    
        
    order by start_date desc;


END

IN `StateId` VARCHAR(50)

BEGIN
    SELECT state_id, state_name 
    FROM state 
    WHERE state.state_id = StateId;
END

IN `CityId` VARCHAR(10)

BEGIN
    SELECT zipcode, city_id
    FROM zipcode 
    WHERE zipcode.city_id = CityId;
END

IN in_parameter_name VARCHAR(30)

BEGIN

  SELECT parameter_value FROM Setting WHERE parameter_name = in_parameter_name;
END

	IN `TransactionTypeId` char(1),
    IN `paymentTypeId` CHAR(1),
    IN `paymentStartDate` DATE,
    IN `paymentEndDate` DATE,
    IN `ProgramId` CHAR(2),
    IN `programScheduleStatusId` VARCHAR(2)

BEGIN
    SELECT
        participant_id,
        CONCAT(firstname, ' ', lastname) AS participantName,
        programtype_name AS program,
        programtype_id,
        programschedulestatus_id,
        programschedule_name AS schedule,
		DATE_FORMAT(payment_date, '%m/%d/%Y %T') AS payment_date,
        -- DATE_FORMAT(payment_date, '%m/%d/%Y %I:%i %p') AS payment_date,
        paymenttype_id,
        paymenttype_desc,
        paymentoption_id,
        paymentoption_desc,
        paymentstatus_id,
        paymentstatus_Desc,
        payment_amount,
        billing_name,
        billing_address,
        billing_zipcode,
        billing_phonenumber,
        transaction_id,
       IFNULL(creditcard_lastfourdigits, '') AS creditcard_lastfourdigits,
        mo_number, IF(mo_date = '0000-00-00', '', DATE_FORMAT(mo_date, '%m/%d/%Y')) AS mo_date,
		-- CAST(refund AS UNSIGNED) AS refund,   
        programschedule_id,
        registrationstatus_desc,
		receipt_number,
        location,
		start_date,
        end_date,
        start_time,
        end_time,
        check_number
    FROM vpaymentdetails
    WHERE 
     (ProgramId = '' OR programtype_id = ProgramId) AND
	 (programschedulestatus_id = programScheduleStatusId OR programScheduleStatusId = '')
	-- AND  ( TransactionTypeId = '' OR  refund  = TransactionTypeId )
   AND (TransactionTypeId = '' OR
   (TransactionTypeId = '0' AND paymentoption_id IN ('D', 'F', 'O')) OR
   (TransactionTypeId = '1' AND paymentoption_id = 'R'))
	AND (paymenttype_id = paymentTypeId OR paymentTypeId = '') 
    AND ((paymentStartDate = '' AND paymentEndDate = '') OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate))
    ORDER BY payment_date DESC;
    END

IN `PS_UserId` VARCHAR(50)

BEGIN
  SELECT password, user_active, firstname, lastname
  FROM user
  WHERE user_id = PS_UserId
  LIMIT 1;
END

Begin
SELECT 
        *
    FROM (
        SELECT 
			distinct(participant_id),
			participantName,
            programschedule_id,
            CASE 
                WHEN room_single_requested = 1 THEN room_single_cost
                WHEN room_shared_requested = 1 THEN room_shared_cost
                ELSE 0
            END AS paymentAmount,
            amount_paid,
            programschedule_name as scheduleDesc,
            CASE 
            WHEN (CASE 
                      WHEN room_single_requested = 1 THEN room_single_cost
                      WHEN room_shared_requested = 1 THEN room_shared_cost
                      ELSE 0
                  END) - amount_paid > 0
            THEN (CASE 
                      WHEN room_single_requested = 1 THEN room_single_cost
                      WHEN room_shared_requested = 1 THEN room_shared_cost
                      ELSE 0
                  END) - amount_paid
            ELSE 0
        END AS paymentDue,  startDate
        FROM vpaymentdetails
        WHERE 
            registrationstatus_id = 'C'
			AND paymentstatus_id = 'SU'
			AND startDate BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30' DAY
    ) AS filtered
    WHERE amount_paid < paymentAmount
    order by startDate  asc, participantName asc
    ;
End

IN P_FirstName VARCHAR(40), IN P_LastName VARCHAR(40),
 IN P_MiddleName VARCHAR(40), IN P_DOB DATE, IN P_Gender CHAR(1), IN P_SSNLastFourDigits CHAR(4),
 IN P_PhoneNumber VARCHAR(10), IN P_Email VARCHAR(40), IN P_Address VARCHAR(80), IN P_CityId VARCHAR(6),
 IN P_StateId CHAR(2), IN P_Zipcode CHAR(5), IN P_Medications TEXT, IN P_SpecialDiet TEXT, IN P_Allergies TEXT,
 IN P_MedicalNeeds TEXT, IN P_Pregnancy TINYINT(1), IN P_Smoker TINYINT(1),
 IN P_PriorTreatmentDrugAlcohol TINYINT(1), IN P_DlNumber VARCHAR(20), IN P_DlIssuedState CHAR(2),
 IN P_DlSuspended CHAR(1), IN P_DlPoints INT(2), IN P_EmergencyContactName VARCHAR(80),
 IN P_EmergencyContactPhoneNumber VARCHAR(10), IN P_EmergencyContactRelationship VARCHAR(50),
 IN P_CreatedBy VARCHAR(20), IN P_UpdatedBy VARCHAR(20), IN C_CourtId VARCHAR(3), IN C_CourtOther VARCHAR(40),
 IN C_CaseNumber VARCHAR(30), IN C_BloodAlcoholContent DECIMAL(4,3), IN R_ProgramScheduleId CHAR(8),
 IN R_RegistrationStatusId CHAR(1), IN R_RoomSingleBooked INT(10), IN R_RoomSharedBooked INT(10),
 IN Pay_PaymentOptionId CHAR(1), IN Pay_PaymentTypeId CHAR(1), IN Pay_PaymentStatusId CHAR(2),
 IN Pay_PaymentAmount DECIMAL(10,2), IN Pay_BillingName VARCHAR(40), IN Pay_BillingAddress VARCHAR(80),
 IN Pay_BillingZipcode CHAR(5), IN Pay_BillingPhoneNumber VARCHAR(10), IN Pay_TransactionId VARCHAR(100),
 IN Pay_MODate DATETIME, IN Pay_MONumber VARCHAR(15), IN Pay_CardNumber CHAR(4),
 IN ReceiptNumber VARCHAR(10), IN EncryptedReceiptNumber VARCHAR(50),
 IN P_CourtOrder TINYINT(1),
 out status int(4), out currentDateTime datetime

BEGIN
DECLARE New_ParticipantId INT; 
DECLARE RoomAvailable INT;
DECLARE last_receipt_id VARCHAR(30);


 
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN
      ROLLBACK;
       set status = 9997;
	 END;

	SELECT parameter_value INTO last_receipt_id
		FROM setting WHERE parameter_name = 'ReceiptNumber_LastUsed';

  set status = 9999;
  set currentDateTime = now();

 
  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_SSNLastFourDigits
AND p.phonenumber = P_PhoneNumber
AND p.email = P_Email
AND prp.programschedule_id = R_ProgramScheduleId
AND prp.registrationstatus_id IN ('C', 'N')) THEN
		-- ROLLBACK;
        set status = 9998;
		LEAVE main_block;
	-- else
     END IF;
    
     START TRANSACTION;
	IF R_RoomSharedBooked = 1 then
		Select (rooms_shared_allocated - rooms_shared_booked) into RoomAvailable from programschedule Where  programschedule_id = R_ProgramScheduleId ;
	ELSEIF R_RoomSingleBooked = 1 then
		Select (rooms_single_allocated - rooms_single_booked) into RoomAvailable from programschedule Where  programschedule_id = R_ProgramScheduleId ;
	END IF;
    
      
    -- Update the room booking in program schedule table
    IF RoomAvailable > 0 AND Pay_PaymentStatusId = 'SU' then
		Update programschedule set rooms_shared_booked = rooms_shared_booked + R_RoomSharedBooked,
		rooms_single_booked = rooms_single_booked + R_RoomSingleBooked 
		Where  programschedule_id = R_ProgramScheduleId;
        
	END IF;

    -- Insert into participant
    INSERT INTO participant (
        firstname, lastname, middlename, dob, gender, ssn_lastfourdigits,
        phonenumber, email, address, city_id, state_id, zipcode,
        medications, special_diet, allergies, medical_needs,
        pregnancy, smoker, prior_treatment_drugalcohol,
        dl_number, dl_issued_state, dl_suspended, dl_points,
        created_date, created_by, updated_date, updated_by,
        emergencycontact_name, emergencycontact_phoneNumber,
        emergencycontact_relationship, blood_alcohol_content, court_order
    ) VALUES (
        P_FirstName, P_LastName, P_MiddleName, P_DOB, P_Gender, P_SSNLastFourDigits,
        P_PhoneNumber, P_Email, P_Address, P_CityId, P_StateId, P_Zipcode,
        P_Medications, P_SpecialDiet, P_Allergies, P_MedicalNeeds,
        P_Pregnancy, P_Smoker, P_PriorTreatmentDrugAlcohol,
        P_DlNumber, P_DlIssuedState, P_DlSuspended, P_DlPoints,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy,
        P_EmergencyContactName, P_EmergencyContactPhoneNumber,
        P_EmergencyContactRelationship, C_BloodAlcoholContent, P_CourtOrder
    );
    -- Get new participant_id
    SET New_ParticipantId = LAST_INSERT_ID();
    -- Insert into participantcourtcase
    -- IF P_CourtOrder=1 THEN
    INSERT INTO participantcourtcase (
        participant_id, court_id, court_other, case_number,
        created_date, created_by, updated_date, updated_by
    ) VALUES (
        New_ParticipantId, C_CourtId, C_CourtOther, C_CaseNumber,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy
    );
   --  END IF;
    -- Insert into programregistrationparticipant
    IF RoomAvailable > 0 then
		INSERT INTO programregistrationparticipant (
			programschedule_id, participant_id, registrationstatus_id, 
			room_single_requested, room_shared_requested,
			created_date, created_by, updated_date, updated_by
		) VALUES (
			R_ProgramScheduleId, New_ParticipantId, R_RegistrationStatusId, 
			R_RoomSingleBooked, R_RoomSharedBooked,
			NOW(), P_CreatedBy, NOW(), P_UpdatedBy
		);
	ELSEIF RoomAvailable <= 0 then
		INSERT INTO programregistrationparticipant (
			programschedule_id, participant_id, registrationstatus_id, 
			room_single_requested, room_shared_requested,
			created_date, created_by, updated_date, updated_by
		) VALUES (
			R_ProgramScheduleId, New_ParticipantId, "N", 
			R_RoomSingleBooked, R_RoomSharedBooked,
			NOW(), P_CreatedBy, NOW(), P_UpdatedBy
		);
	END IF;
    -- Insert into programregistrationpayment
    INSERT INTO programregistrationpayment (
        programschedule_id, participant_id, payment_date, paymenttype_id, paymentstatus_id,
        paymentoption_id, payment_amount, billing_name,  billing_address,
        billing_zipcode, billing_phonenumber, transaction_id,
       mo_date, mo_number, creditcard_lastfourdigits, receipt_number,
        created_date, created_by, updated_date, updated_by
    ) VALUES (
        R_ProgramScheduleId, New_ParticipantId, NOW(), Pay_PaymentTypeId, Pay_PaymentStatusId,
        Pay_PaymentOptionId, Pay_PaymentAmount, Pay_BillingName,  Pay_BillingAddress,
        Pay_BillingZipcode, Pay_BillingPhoneNumber, Pay_TransactionId,
        Pay_MODate, Pay_MONumber,Pay_CardNumber,ReceiptNumber,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy
    );
    
    
     UPDATE setting
	SET parameter_value = EncryptedReceiptNumber,
		created_by = IF(last_receipt_id IS NULL OR last_receipt_id = '', P_CreatedBy, created_by),
		created_date = IF(last_receipt_id IS NULL OR last_receipt_id = '', NOW(), created_date),
		updated_by = P_UpdatedBy,
		updated_date = NOW()
	WHERE parameter_name = 'ReceiptNumber_LastUsed';
    
       COMMIT;
       IF RoomAvailable <= 0 then
       		set status = 9994;
		END IF;
	-- END IF;
     END main_block; 
END

IN `P_CityName` VARCHAR(30), IN `P_StateId` CHAR(2), IN `P_ZipCode` CHAR(5), OUT `status` INT(4), OUT `CityId` VARCHAR(6)

BEGIN
  DECLARE Current_City_Id VARCHAR(6) DEFAULT NULL;
  DECLARE Lower_CityName VARCHAR(30);
  SET Lower_CityName = LOWER(P_CityName);
  SET status = 9999;
  SET CityId = '';

  -- Check if city exists at all
  IF NOT EXISTS (SELECT 1 FROM city WHERE LOWER(city_name) = Lower(P_CityName)) THEN
    SET status = 9997;

  ELSE
    -- Get City ID with matching state
    SELECT city_id INTO Current_City_Id
    FROM city
    WHERE LOWER(city_name) = Lower(P_CityName) AND state_id = P_StateId;

    SET CityId = Current_City_Id;

    IF Current_City_Id IS NULL THEN
      SET status = 9996;

    ELSEIF NOT EXISTS (
      SELECT 1 FROM zipcode
      WHERE city_id = Current_City_Id AND zipcode = P_ZipCode
    ) THEN
      SET status = 9995;

    END IF;
  END IF;
END

IN `P_FirstName` VARCHAR(40), IN `P_LastName` VARCHAR(40), IN `P_DOB` DATE, IN `P_SSNLastFourDigits` CHAR(4), IN `P_PhoneNumber` VARCHAR(10), IN `P_Email` VARCHAR(40), IN `R_ProgramScheduleId` CHAR(8), OUT status int(4)

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_SSNLastFourDigits
	AND p.phonenumber = P_PhoneNumber
	AND p.email = P_Email
	AND prp.programschedule_id = R_ProgramScheduleId
	AND prp.registrationstatus_id IN ('C', 'N')) THEN
        SELECT 9998 AS status;
	END IF;
END

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

IN `ProgramId` CHAR(2), IN `ScheduleStatusId` CHAR(2), IN `ScheduleId` CHAR(8), IN `StartDate` DATE, 
IN `EndDate` DATE, IN `OrderingCourtId` VARCHAR(3), IN `RegistrationStatus` CHAR(1)

BEGIN
    -- Total Programs
    SELECT COUNT(DISTINCT programtype_id) AS total_programs
    FROM vScheduleParticipantDetails
     WHERE (programtype_id = ProgramId OR ProgramId = '')
	 and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
     AND (programschedule_id = ScheduleId OR ScheduleId = '')
	 AND (
          ((StartDate != '' AND EndDate != '')
             AND (start_date <= EndDate AND end_date >= StartDate))
          OR StartDate = '' OR EndDate = ''
         )
	 AND (court_id = OrderingCourtId or OrderingCourtId = '')
     and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '');
      
	-- Total Schedules - Completed
    SELECT COUNT(DISTINCT programschedule_id) AS total_schedules_completed
    FROM vScheduleParticipantDetails
    WHERE programschedulestatus_id = 'CO'
      AND (programtype_id = ProgramId OR ProgramId = '')
      and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
      AND (programschedule_id = ScheduleId OR ScheduleId = '')
      AND (
            ((StartDate != '' AND EndDate != '')
              AND (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
           )
	  AND (court_id = OrderingCourtId or OrderingCourtId = '')
      and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '');
      
	-- Total Schedules - Open
    SELECT COUNT(DISTINCT programschedule_id) AS total_schedules_open
    FROM vScheduleParticipantDetails
    WHERE programschedulestatus_id = 'OP'
     AND (programtype_id = ProgramId OR ProgramId = '')
     AND (programschedule_id = ScheduleId OR ScheduleId = '')
     and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
     AND (
           ((StartDate != '' AND EndDate != '')
             AND (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
          )
	 AND (court_id = OrderingCourtId or OrderingCourtId = '')
     and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '');
      
	-- Total Participants
  --  SELECT COUNT(DISTINCT participant_id) AS total_participants
  --  FROM vScheduleParticipantDetails
  --  WHERE  (programtype_id = ProgramId OR ProgramId = '')
	-- and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
   --   AND (programschedule_id = ScheduleId OR ScheduleId = '')
    --  AND (
	 --     ((StartDate != '' AND EndDate != '')
       --      AND (start_date <= EndDate AND end_date >= StartDate))
       --     OR StartDate = '' OR EndDate = ''
      --    )
	--  AND (court_id = OrderingCourtId or OrderingCourtId = '')
    --  AND firstname IS NOT NULL
    --  AND lastname IS NOT NULL
    --  AND phonenumber IS NOT NULL;
    
    -- fully and partially paid participant
    SELECT 
    -- Fully Paid 
        SUM(
            CASE 
                WHEN amount_paid >= total_amount_due 
                THEN 1 ELSE 0 
            END
        ) AS fullyPaidParticipants,
        -- Partially Paid 
    SUM(
            CASE 
                WHEN (amount_paid > 0 and (amount_paid < total_amount_due))
                THEN 1 ELSE 0 
            END
        ) AS partiallyPaidParticipants
    FROM vScheduleParticipantDetails
    WHERE  (programtype_id = ProgramId OR ProgramId = '')
	 and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
      AND (programschedule_id = ScheduleId OR ScheduleId = '')
      AND (
	      ((StartDate != '' AND EndDate != '')
             AND (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
          )
	  AND (court_id = OrderingCourtId or OrderingCourtId = '')
      and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '')
      AND firstname IS NOT NULL
      AND lastname IS NOT NULL
      AND phonenumber IS NOT NULL;
      
		-- Schedule Open after start date
        SELECT COUNT(DISTINCT programschedule_id) AS schedules_due
		FROM vScheduleParticipantDetails
		WHERE programschedulestatus_id = 'OP' 
        and (start_date <= now())
        and  (programtype_id = ProgramId OR ProgramId = '')
		 and (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
		  AND (programschedule_id = ScheduleId OR ScheduleId = '')
		  AND (
			  ((StartDate != '' AND EndDate != '')
				 AND (start_date <= EndDate AND end_date >= StartDate))
				OR StartDate = '' OR EndDate = ''
			  )
		  AND (court_id = OrderingCourtId or OrderingCourtId = '')
		  AND firstname IS NOT NULL
		  AND lastname IS NOT NULL
		  AND phonenumber IS NOT NULL;
      
	-- Participants by Program 
		SELECT 
		  pt.programtype_name AS program_name,
		  COUNT(DISTINCT v.participant_id) AS participant_count
		FROM programtype pt
		LEFT JOIN vScheduleParticipantDetails v
		  ON v.programtype_id = pt.programtype_id
		  AND (v.programtype_id = ProgramId OR ProgramId = '')
		  and (v.programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
		  AND (v.programschedule_id = ScheduleId OR ScheduleId = '')
		  AND (
				((StartDate != '' AND EndDate != '')
				  AND (v.start_date <= EndDate AND v.end_date >= StartDate))
				OR StartDate = '' OR EndDate = ''
			  )
		  AND (court_id = OrderingCourtId or OrderingCourtId = '')
          and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '')
		  AND v.firstname IS NOT NULL
		  AND v.lastname IS NOT NULL
		  AND v.phonenumber IS NOT NULL
		GROUP BY pt.programtype_name
        order by pt.programtype_name;
      
	-- Participants by Schedule Status
		SELECT
		  s.programschedulestatus_desc AS schedule_status,
		  COUNT(DISTINCT v.participant_id) AS participant_count
		FROM programschedulestatus s
		LEFT JOIN vScheduleParticipantDetails v
		  ON v.programschedulestatus_id = s.programschedulestatus_id
          AND (v.programtype_id = ProgramId OR ProgramId = '')
		  and (v.programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
		  AND (v.programschedule_id = ScheduleId OR ScheduleId = '')
		  AND (
				((StartDate != '' AND EndDate != '')
				 AND (v.start_date <= EndDate AND v.end_date >= StartDate))
			   OR StartDate = '' OR EndDate = ''
			  )
		  AND (court_id = OrderingCourtId or OrderingCourtId = '')
          and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '')
		GROUP BY s.programschedulestatus_desc
		ORDER BY s.programschedulestatus_desc;
    
	-- Schedules by Program
		SELECT 
		  pt.programtype_name AS program_name,
		  COUNT(DISTINCT v.programschedule_id) AS schedule_count
		FROM programtype pt
		LEFT JOIN vScheduleParticipantDetails v
		  ON v.programtype_id = pt.programtype_id
		  AND (v.programtype_id = ProgramId OR ProgramId = '')
		  and (v.programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
		  AND (v.programschedule_id = ScheduleId OR ScheduleId = '')
		  AND (
				((StartDate != '' AND EndDate != '')
				  AND (v.start_date <= EndDate AND v.end_date >= StartDate))
				OR StartDate = '' OR EndDate = ''
			  )
		  AND (court_id = OrderingCourtId or OrderingCourtId = '')
          and (registrationstatus_id = RegistrationStatus or RegistrationStatus = '')
		GROUP BY pt.programtype_name
        order by pt.programtype_name;
END