IN `ProgramScheduleId` CHAR(8), IN `ParticipantId` 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` CHAR(15), IN `Pay_CardNumber` CHAR(4), IN `CreatedBy` VARCHAR(20), OUT `status` INT(4)

BEGIN
		set status = 0000;   
        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,   
        created_date, created_by, updated_date, updated_by
    ) VALUES (
        ProgramScheduleId, 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,      
        NOW(), CreatedBy, NOW(), CreatedBy
    );
        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), OUT `status` INT(4)

BEGIN
DECLARE New_ParticipantId INT; 
DECLARE RoomAvailable INT;

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

  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_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,
        emergenycontact_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,
        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,
        NOW(), P_CreatedBy, NOW(), P_UpdatedBy
    );
       COMMIT;
       IF RoomAvailable <= 0 then
       		set status = 9994;
		END IF;
	-- END IF;
     END main_block; 
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 `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 (
            ((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 (
            ((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 (programschedulestatus_id = ScheduleStatusId or ScheduleStatusId = '')
      AND (programschedule_id = ScheduleId OR ScheduleId = '')
      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;
      
	-- 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 (
				((v.start_date <= EndDate AND v.end_date >= StartDate))
				OR StartDate = '' OR EndDate = ''
			  )
		  AND (v.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;
      
	-- 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 (
				((v.start_date <= EndDate AND v.end_date >= StartDate))
			   OR StartDate = '' OR EndDate = ''
			  )
		  AND (v.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 (
				((v.start_date <= EndDate AND v.end_date >= StartDate))
				OR StartDate = '' OR EndDate = ''
			  )
		  AND (v.court_id = OrderingCourtId OR OrderingCourtId = '')
		GROUP BY pt.programtype_name;
END

IN `IsAdmin` INT(2)

BEGIN
    IF IsAdmin = 1 THEN
        SELECT paymentoption_id, paymentoption_desc FROM paymentoption;
    ELSE
        SELECT paymentoption_id, paymentoption_desc FROM paymentoption 
        WHERE paymentOption_id IN ('D', 'F');
    END IF;
END

IN `isAdmin` INT

BEGIN
    IF isAdmin = 1 THEN
        SELECT paymenttype_id, paymenttype_desc FROM paymenttype 
        order by paymenttype_desc;
    ELSE
        SELECT paymenttype_id, paymenttype_desc
        FROM paymenttype 
        WHERE paymenttype_id IN ('D')
        order by paymenttype_desc;
    END IF;
END

IN `PS_Name` VARCHAR(40), 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_Max_Participants` INT(10), IN `PS_RoomSingleCost` DECIMAL(10,2), IN `PS_RoomSharedCost` DECIMAL(10,2), IN `PS_RoomSharedAllocated` INT(10), IN `PS_RoomSharedBooked` INT(10), IN `PS_RoomSingleAllocated` INT(10), IN `PS_RoomSingleBooked` INT(10), IN `PS_CreatedBy` VARCHAR(20), 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

   

	DECLARE max_id_number INT DEFAULT 0;
    DECLARE last_program_id VARCHAR(10);

    DECLARE new_id CHAR(8);
	 main_block: BEGIN
     set status = 9998;
 
    

	-- Checking already exists

     IF EXISTS (SELECT 1

    FROM programschedule

    WHERE programType_id = PT_ID 

    AND LOWER(REPLACE(TRIM(address), ' ', '')) = LOWER(REPLACE(TRIM(PS_Address), ' ', ''))    

    AND start_date = PS_StartDate
    AND city_id = PS_CityId
    AND state_id = PS_StateId
    AND zipcode = PS_zipcode) THEN 
		ROLLBACK;

        set status =  9998; 

		LEAVE main_block;
        END IF;

        -- Generate next programschedule_id

       
        SELECT programschedule_id INTO last_program_id
        FROM programschedule
        ORDER BY programschedule_id DESC
        LIMIT 1;
        
       IF last_program_id IS NULL OR last_program_id = '' THEN
           -- SET new_id = 'PS000001';
            SET new_id = CONCAT('PS', LPAD(1, 6, '0'));
        ELSE
            SET max_id_number = CAST(SUBSTRING(last_program_id, 3) AS UNSIGNED);
            SET new_id = CONCAT('PS', LPAD(max_id_number + 1, 6, '0'));
        END IF;
        
        

        INSERT INTO programschedule(

        programschedule_id,

        programschedule_name,

        programtype_id,

        programschedulestatus_id,

        start_date,

        end_date,

        start_time,

        end_time,     

        participants_maximum,

        room_single_cost,

        room_shared_cost,

        rooms_shared_allocated,

        rooms_shared_booked,

        rooms_single_allocated,

        rooms_single_booked,

        created_date,

        created_by,

        updated_date,

        updated_by,
        address,
        city_id,
        state_id,
        zipcode

        )values(

        new_id,

        PS_Name,

        PT_ID,

		PS_StatusId,

		PS_StartDate,

		PS_EndDate,

		PS_StartTime ,

		PS_EndTime ,		

		PS_Max_Participants,

		PS_RoomSingleCost,

		PS_RoomSharedCost,

		PS_RoomSharedAllocated,

		PS_RoomSharedBooked,

		PS_RoomSingleAllocated,

		PS_RoomSingleBooked,

		NOW(),

		PS_CreatedBy,

		NOW(),

		PS_UpdatedBy,
        PS_Address ,
		PS_CityId,
		PS_StateId ,
		PS_zipcode 

        );

        set status =  9999;

 END main_block;  

END

IN `PS_UserId` VARCHAR(50)

BEGIN
  SELECT password, user_active, firstname, lastname
  FROM user
  WHERE user_id = PS_UserId
  LIMIT 1;
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 `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_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 `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 `p_programschedule_id` VARCHAR(20), IN `p_programtype_id` CHAR(2), IN `p_roomType` VARCHAR(10)

BEGIN
    -- DECLARE v_startDate DATE;

    -- Get start date of the given program schedule
	-- SELECT start_date 
    -- INTO v_startDate 
    -- FROM programschedule 
    -- WHERE programschedule_id = p_programschedule_id;

    -- Main SELECT query
    SELECT  
        ps.programschedule_id, 
        ps.programschedule_name,
        ps.programtype_id, 
        DATE_FORMAT(ps.start_date, '%m/%d/%Y') AS startDate, 
        DATE_FORMAT(ps.end_date, '%m/%d/%Y') AS endDate,
        ps.programschedulestatus_id AS programScheduleStatusId,
        DATE_FORMAT(ps.start_time, '%l:%i %p') AS startTime, 
        DATE_FORMAT(ps.end_time, '%l:%i %p') AS endTime,
        CONCAT(ps.address, ', ', c.city_name, ', ', s.state_name, ', ', ps.zipcode) AS address,
        ps.room_single_cost, 
        ps.room_shared_cost
    FROM programschedule ps
    JOIN city c ON c.city_id = ps.city_id
     JOIN state s ON s.state_id = ps.state_id
    WHERE ps.programschedule_id != p_programschedule_id
      AND ps.programtype_id = p_programtype_id
      AND ps.programschedulestatus_id = 'OP'
      AND ps.start_date > NOW()
      AND (
        (p_roomType = 'shared' AND ps.rooms_shared_allocated - ps.rooms_shared_booked != 0)
        OR
        (p_roomType = 'single' AND ps.rooms_single_allocated - ps.rooms_single_booked != 0)
      )
      
       ORDER BY ps.start_date DESC;
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 `PS_UserId` VARCHAR(50)

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

IN `paymentStatusId` CHAR(2), 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 (paymentStatusId = '' or paymentstatus_id = paymentStatusId )
            AND (paymentTypeId = '' or paymenttype_id = paymentTypeId )       
            AND (ProgramId = '' OR programtype_id = ProgramId)
            AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
            -- AND (paymentStartDate IS NULL OR DATE(payment_date) >= paymentStartDate)
            -- AND (paymentEndDate IS NULL OR DATE(payment_date) <= paymentEndDate)
            AND (paymentStartDate = '' OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate))
    ) AS filtered
    WHERE amount_paid < room_cost;

    -- 2. New Registrations with Processed Payments   
    SELECT 
        COUNT(DISTINCT participant_id) AS NewRegistrationWithPayments
    FROM vpaymentdetails
    WHERE registrationstatus_id = 'N'
		AND (paymentStatusId = '' or paymentstatus_id = paymentStatusId )
		AND (paymentTypeId = '' or paymenttype_id = paymentTypeId )
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
        AND (paymentStartDate = '' OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));

    -- 3. Total MO Payments – Count & Amount
    SELECT 
        COUNT(*) 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 paymentstatus_id = 'SU'       
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
         AND (paymentStartDate = '' OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));

    -- 4. Total Cash Payments – Count & Amount
    SELECT 
        COUNT(*) 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 paymentstatus_id = 'SU'
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
        AND (paymentStartDate = '' OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));

    -- 5. Total Credit Card Payments – Count & Amount
    SELECT 
        COUNT(*) 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 paymentstatus_id = 'SU'
        AND (ProgramId = '' OR programtype_id = ProgramId)
        AND (programScheduleStatusId = '' OR programschedulestatus_id = programScheduleStatusId)
        AND (paymentStartDate = '' OR (DATE(payment_date) >= paymentStartDate AND DATE(payment_date) <= paymentEndDate));
    END

IN `programId` CHAR(2), IN `StartDate` DATE, IN `EndDate` DATE

BEGIN

    --  Number of Programs with No Open Schedules
    
 SELECT count( DISTINCT programtype_id)  programswith_noopenschedule 
    FROM vscheduleparticipantdetails 
    WHERE (programtype_id = programId OR programId = '')
      AND programschedulestatus_id not in ('OP')
      AND (
            (
             (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
      );
  
     -- Number of Confirmed Registrations
    SELECT COUNT(DISTINCT participant_id) AS confirmed_registrations
    FROM vscheduleparticipantdetails
    WHERE (programtype_id = programId OR programId = '')
      
      AND registrationstatus_id = 'C'
      AND (
            (
             (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
      );
      
      -- AND (room_single_requested = 1 OR room_shared_requested = 1)
      
      --  Number of Open Schedules with No Registrations
    SELECT COUNT(DISTINCT programschedule_id) AS open_schedules_no_registrations
    FROM vscheduleparticipantdetails
    WHERE (programtype_id = programId OR programId = '')
      AND programschedulestatus_id = 'OP'
      AND (participant_id = '' OR participant_id is NULL)
      AND (
            (
             (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
      );

    
--  Number of Closed Schedules with New Registrations
    SELECT COUNT(DISTINCT programschedule_id) AS closed_schedules_with_new_registrations
    FROM vscheduleparticipantdetails
    WHERE (programtype_id = ProgramId OR ProgramId = '')
      AND programschedulestatus_id = 'CL'
      AND registrationstatus_id = 'N'
     -- AND (participant_id = '' OR participant_id is NULL)
      AND (
            (
             (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
      );

    --  Number of Completed Schedules with New Registrations
    SELECT COUNT(DISTINCT programschedule_id) AS completed_schedules_with_new_registrations
    FROM vscheduleparticipantdetails
    WHERE (programtype_id = ProgramId OR ProgramId = '')
      AND programschedulestatus_id = 'CO'
      AND registrationstatus_id = 'N'
   --   AND (participant_id = '' OR participant_id is NULL)
      AND (
            (
             (start_date <= EndDate AND end_date >= StartDate))
            OR StartDate = '' OR EndDate = ''
      );


	
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 `emailName` VARCHAR(50)

BEGIN
	SELECT * FROM emailnotification where emailnotification_name = emailName;
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

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

IN `in_parameter_name` VARCHAR(30)

BEGIN

  SELECT parameter_value FROM Setting WHERE parameter_name = in_parameter_name;
END