CREATE DEFINER=CURRENT_USER PROCEDURE `doctor_income_rpt`()
BEGIN


select ROW_NUMBER() OVER (ORDER BY sum(Appointment) + sum(Lab) + sum(Image) + sum(Operation) + sum(Service) desc) AS Sqn,
concat("<a class='report_tags' target='_blank' href='report_center/show/16/?doctor_id=",dr_id,"'>",Doctor,"</a>") Doctor, sum(Appointment) as Appointment, sum(Lab) as Lab, sum(Image) as Image, sum(Operation) as Operation, sum(Service) as Service, 
sum(Appointment) + sum(Lab) + sum(Image) + sum(Operation) + sum(Service)
Total from(
SELECT 
   hrm_staffs.id AS dr_id,
    hrm_staffs.name AS Doctor,
    concat(
    FORMAT(SUM(CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Appointment`,
        0 Lab,
        0 Image,
        0 Operation,
        0 Service
FROM
    appointments
        JOIN
    hrm_staffs ON hrm_staffs.id = appointments.doctor_id
    join payments on payments.reference_id = appointments.id and payments.reference = "appointment"
GROUP BY hrm_staffs.name, hrm_staffs.id

union

select 
 hrm_staffs.id AS dr_id,
  hrm_staffs.name AS Doctor,
    0 `Appointment`,
    concat(
    FORMAT(SUM(CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Lab`,
         0 Image,
         0 Operation,
        0 Service
        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"
GROUP BY hrm_staffs.name, hrm_staffs.id

union


select 
   hrm_staffs.id AS dr_id,
  hrm_staffs.name AS Doctor,
   0 `Appointment`,
        0 Lab,
    concat(
    FORMAT(SUM(CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Image`,
        0 Operation,
        0 Service
        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"
GROUP BY hrm_staffs.name, hrm_staffs.id


union

select 
   hrm_staffs.id AS dr_id,
  hrm_staffs.name AS Doctor,
   0 `Appointment`,
        0 Lab,
        0 Image,
    concat(
    FORMAT(SUM(CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Operation`,
        0 Service
        from operations 
JOIN  hrm_staffs ON hrm_staffs.id = operations.dr
join payments on payments.reference_id = operations.id and payments.reference = "Operation"
GROUP BY hrm_staffs.name,hrm_staffs.id


union

select 
   hrm_staffs.id AS dr_id,
  hrm_staffs.name AS Doctor,
   0 `Appointment`,
        0 Lab,
        0 Image,
		0 Operation,
    concat(
    FORMAT(SUM(CASE
            WHEN payments.currency = 1 THEN payments.payment
            ELSE payments.payment / payments.exchange_rate
        END),
        2)) AS `Service`
        from services 
JOIN  hrm_staffs ON hrm_staffs.id = services.doctor_id
join payments on payments.reference_id = services.id and payments.reference = "Service"
GROUP BY hrm_staffs.name, hrm_staffs.id)z
group by Doctor,dr_id
order by sum(Appointment) + sum(Lab) + sum(Image) + sum(Operation) + sum(Service) desc
;
END
