CREATE DEFINER=CURRENT_USER PROCEDURE `doctor_income_det_rpt`(`doctor_id_filter` INT, `start_date_filter` DATE, `end_date_filter` DATE)
BEGIN


SELECT 
   appointments.date AS Date,
    hrm_staffs.name AS Doctor,
    'Card' as `Account head`,
    concat("$",
    FORMAT((CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Amount`,
        patients.name Patient
FROM
    appointments
        JOIN
    hrm_staffs ON hrm_staffs.id = appointments.doctor_id
    join patients on patients.id = appointments.patient_id
    join payments on payments.reference_id = appointments.id and payments.reference = "appointment"
    where doctor_id = doctor_id_filter and appointments.date between  start_date_filter and end_date_filter
    
union

select 
   lab_requests.date AS Date,
    hrm_staffs.name AS Doctor,
    'Lab request' as `Account head`,
    concat("$",
    FORMAT((CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Amount`,
           patients.name Patient
        from lab_requests
       
JOIN  hrm_staffs ON hrm_staffs.id = lab_requests.doctor_id
join payments on payments.reference_id = lab_requests.id and payments.reference = "Lab request"
join patients on patients.id = lab_requests.patient
where lab_requests.doctor_id = doctor_id_filter and lab_requests.date between  start_date_filter and end_date_filter

union


select 
   image_requests.date AS Date,
    hrm_staffs.name AS Doctor,
    'Image request' as `Account head`,
    concat("$",
    FORMAT((CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Amount`,
     patients.name Patient
        from image_requests 
JOIN  hrm_staffs ON hrm_staffs.id = image_requests.doctor_id
join payments on payments.reference_id = image_requests.id and payments.reference = "Image request"
join patients on patients.id = image_requests.patient
where doctor_id = doctor_id_filter and image_requests.date between  start_date_filter and end_date_filter


union

select 
    operations.date AS Date,
    hrm_staffs.name AS Doctor,
    'Operation' as `Account head`,
    concat("$",
    FORMAT((CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Amount`,
        patients.name Patient
        from operations 
JOIN  hrm_staffs ON hrm_staffs.id = operations.dr
join payments on payments.reference_id = operations.id and payments.reference = "Operation"
join patients on patients.id = operations.patient
where dr = doctor_id_filter and operations.date between  start_date_filter and end_date_filter


union

select 
     services.date AS Date,
    hrm_staffs.name AS Doctor,
    'Service' as `Account head`,
    concat("$",
    FORMAT((CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Amount`,
          patients.name Patient
        from services 
JOIN  hrm_staffs ON hrm_staffs.id = services.doctor_id
join payments on payments.reference_id = services.id and payments.reference = "Service"
join patients on patients.id = services.patient
where doctor_id = doctor_id_filter and services.date between  start_date_filter and end_date_filter;
    
    
    

END
