SELECT COALESCE(u2.email, 'N/A') AS 'To:'
,COALESCE(u1.email, 'N/A') AS 'Reply To:'
 
,CONCAT (
( CASE WHEN GROUP_CONCAT(DISTINCT (
CASE WHEN ifsFeedbackTable.IFS_FEEDBACK LIKE '%?%'
THEN 'Response Required' ELSE '' END
) SEPARATOR '-') LIKE '%Response%'
THEN CONCAT ('Response Required | ', u2.display_name, ' | ','Appointments ' )
ELSE ( CASE 
WHEN MAX(ifsFeedbackTable.IFS_FEEDBACK) IS NOT NULL
THEN CONCAT ('Review Required | ', u2.display_name, ' | Appointments ' )
ELSE CONCAT ( 'Summary | ', u2.display_name, ' | ' )
END ) END ) 
,DATE_FORMAT(appointments.dateofappt, '%m/%d/%y')
) AS 'Subject'
 ##################################
#below is old
/*
,(CASE 
 WHEN u2.id IN ( 3969, 3893, 4011, 3887, 4047 ) THEN 'bryanb@reliablegaragedoor.com, joelw@reliablegaragedoor.com' 
 WHEN u2.id IN ( 3732, 4008, 3966, 3909, 3769 ) THEN 'bryanb@reliablegaragedoor.com, jamesw@reliablegaragedoor.com'  
 WHEN u2.id IN ( 3968,  3845 ) THEN 'bryanb@reliablegaragedoor.com, ians@reliablegaragedoor.com,  geraldr@reliablegaragedoor.com'  
  WHEN u2.id IN ( 3868 ) THEN 'bryanb@reliablegaragedoor.com, geraldr@reliablegaragedoor.com' 
 WHEN u2.id IN ( 3768, 3763, 3958 ) THEN 'bryanb@reliablegaragedoor.com' 
ELSE '' END) AS 'CC'
*/
 #above is old
 ##################################
 #below is new for Derek Stesniak
 
 
 ,(CASE 
WHEN u2.id IN ( 3732, 3845 ) THEN 'bryanb@reliablegaragedoor.com, dereks@reliablegaragedoor.com'  
WHEN u2.id IN ( 3909, 3958, 3868 ) THEN 'bryanb@reliablegaragedoor.com' 
WHEN u2.id IN ( 3893, 3887, 3763, 3768 ) THEN 'bryanb@reliablegaragedoor.com, joelw@reliablegaragedoor.com' 
WHEN u2.id IN ( 4047, 3968, 3966 ) THEN 'bryanb@reliablegaragedoor.com, ians@reliablegaragedoor.com '  
ELSE '' END) AS 'CC'
 
 #try below once new version tested and working
 
# CONCAT( u1.email, ', bryanb@reliablegaragedoor.com' ) AS 'CC'
 
  #try above once new version tested and working
 
 #above is new for Derek Stesniak
 ##################################

,'scotts@reliablegaragedoor.com' AS 'BCC'
,CONCAT (
'<SPAN STYLE="font-size:14pt;color:blue">'
,u2.display_name
,' | '
,DAYNAME(appointments.dateofappt)
,', '
,DATE_FORMAT(appointments.dateofappt, '%m/%d/%Y')
,' | '
,CONCAT (
COALESCE(TotalRated.cnt,'0')
,' of '
,COALESCE(TotalAppointments.cnt, '0')
)
,' Appointments Reviewed'

,'</SPAN>'
,'<SPAN STYLE="font-size:12pt;color:green">'
,'<br/><br/><u><strong>Green Appt Types:</u></strong><br/><br/></SPAN>'
,COALESCE(GROUP_CONCAT(DISTINCT greenApptDetailTable.GREEN_APPT_TYPES SEPARATOR '<br/>'), 'No Green Appt Data')
,'<SPAN STYLE="font-size:12pt;color:orange">'
,'<br/><br/><u><strong>Orange Appt Types:</u></strong><br/><br/></SPAN>'
,COALESCE(GROUP_CONCAT(DISTINCT orangeApptDetailTable.ORANGE_APPT_TYPES SEPARATOR '<br/>'), 'No Orange Appt Data')
,'<SPAN STYLE="font-size:12pt;color:red">'
,'<br/><br/><u><strong>Red Appt Types:</u></strong><br/><br/></SPAN>'
,COALESCE(GROUP_CONCAT(DISTINCT redApptDetailTable.RED_APPT_TYPES SEPARATOR '<br/>'), 'No Red Appt Data')
,'<SPAN STYLE="font-size:12pt;color:blue">'
,'<br/><br/><u><strong>Supervisor Feedback:<br/><br/></u></strong>'
,'</SPAN>'
,(
CASE 
WHEN MAX(ifsFeedbackTable.IFS_FEEDBACK) IS NOT NULL
THEN GROUP_CONCAT(DISTINCT ifsFeedbackTable.IFS_FEEDBACK SEPARATOR '')
ELSE 'No IFS Feedback for these Appointments'
END
)
    
    
,' <hr size="3" noshade><div STYLE="text-align:center;"><br/><br/>'
  

,'Do you have any questions about the accuracy of the reported numbers? If so, '
 ,'please contact <a href="mailto:scotts@reliablegaragedoor.com?subject=Question about Sales Reporting Numbers">scotts@reliablegaragedoor.com</a>. '
,'Your feedback is how we get better. <br/><br/>Thank You!<br/><br/><br/></div>'
    
    
    
    
    
) AS 'Email Body'
FROM appointmentifsreviews
LEFT JOIN appointments ON appointments.id = appointmentifsreviews.appt_id
LEFT JOIN users u1 ON u1.id = appointmentifsreviews.add_id
LEFT JOIN users u2 ON u2.id = appointments.fieldworker
LEFT JOIN appointmentifsratings ON appointmentifsratings.id = appointmentifsreviews.appt_rating_id
LEFT OUTER join appointmenttypes q1 on appointments.appointmenttype_id = q1.id
LEFT OUTER join appointmentgroups q2 on q2.id = q1.appointmentgroup_id
LEFT OUTER JOIN appointmentgrouptypes q3 ON q3.id = q1.appointmentgrouptype_id
LEFT JOIN customers ON customers.id = appointments.customer_id
LEFT JOIN orders ON appointments.id = orders.appointment_id
LEFT OUTER JOIN orderinfos  ON orders.id = orderinfos.order_id 
LEFT JOIN jobsites ON jobsites.id = orderinfos.jobsite_id
LEFT JOIN appointmentstatuses ON appointmentstatuses.appointment_id = appointments.id
LEFT JOIN jobstatuses ON jobstatuses.id = appointmentstatuses.jobstatus_id
LEFT JOIN (
SELECT
COUNT(DISTINCT(appointmentifsreviews.appt_id)) AS cnt,users.id,DATE(appointments.dateofappt)AS dte
FROM appointmentifsreviews
LEFT OUTER JOIN appointments ON appointments.id = appointmentifsreviews.appt_id
LEFT OUTER JOIN users On users.id = appointments.fieldworker
WHERE
appointments.is_cancel = 0
AND appointmentifsreviews.appt_rating_id IS NOT NULL 

#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 
    
    
GROUP BY users.id,DATE(appointments.dateofappt) 
    ORDER BY appointments.id ASC 
) AS TotalRated ON TotalRated.dte=DATE(appointments.dateofappt) AND TotalRated.id=u2.id
LEFT JOIN (
SELECT
COUNT(DISTINCT(appointments.id)) AS cnt,DATE(appointments.dateofappt) AS dte,users.id 
FROM appointments 
LEFT OUTER JOIN users On users.id = appointments.fieldworker
WHERE
appointments.is_cancel = 0 
    
#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 
    
    
  
GROUP BY DATE(appointments.dateofappt),users.id
ORDER BY appointments.id ASC 
) AS TotalAppointments ON TotalAppointments.dte=DATE(appointments.dateofappt) AND TotalAppointments.id=u2.id
LEFT JOIN (
SELECT appointmentifsreviews.appt_id AS APPT_ID
,CONCAT (
COALESCE(appointmentifsratings.name, 'N/A')
,' | '
,'<a href="https://app.reliablegaragedoor.com/appointments.html?appointment_id='
,appointments.id
,'">'
,CONCAT (
customers.first_name
,' '
,customers.last_name
)
,' - '
,COALESCE(jobsites.city, customers.city)
,' </a>'
,' | '
,COALESCE(CONCAT (
'$'
,FORMAT(Table1.BALANCE, 2)
), CONCAT (
'$'
,FORMAT(0, 2)
))
,' - '
,COALESCE(TIMESTAMPDIFF(MINUTE, MAX(IF (
jobstatuses.name = 'Arrived On Site'
,appointmentstatuses.jobstatus_date
,''
) )
,MAX(IF (
jobstatuses.name = 'Complete'
,appointmentstatuses.jobstatus_date
,''
) ) )
,'N/A' )
,' minutes' ) AS GREEN_APPT_TYPES
,COALESCE(TIMESTAMPDIFF(MINUTE, MAX(IF (
jobstatuses.name = 'Arrived On Site'
,appointmentstatuses.jobstatus_date
,''
) )
,MAX(IF (
jobstatuses.name = 'Complete'
,appointmentstatuses.jobstatus_date
,''
) ) )
,'N/A' ) AS TimeSpentonSite
,COALESCE(TIMESTAMPDIFF(MINUTE, MAX(IF (
jobstatuses.name = 'Arrived On Site'
,appointmentstatuses.jobstatus_date
,''
) )
,MAX(IF (
jobstatuses.name = 'Finishing'
,appointmentstatuses.jobstatus_date
,''
) ) )
,'N/A' ) AS RevenuePerHour
,CASE 
WHEN MAX(orders.ordertype_id) NOT IN (
5,6
)
THEN appointments.id
ELSE NULL
END AS Green_Orders_noAttached
,CASE 
WHEN MAX(orders.ordertype_id) IN (
5 ,6
)
THEN appointments.id
ELSE NULL
END AS Green_Orders_Attached
,COALESCE(FORMAT(Table1.BALANCE, 2), FORMAT(0, 2)) AS GreenRevenue 
FROM appointmentifsreviews 
LEFT JOIN appointments ON appointments.id = appointmentifsreviews.appt_id 
LEFT JOIN users u1 ON u1.id = appointmentifsreviews.add_id 
LEFT JOIN users u2 ON u2.id = appointments.fieldworker 
LEFT JOIN appointmentifsratings ON appointmentifsratings.id = appointmentifsreviews.appt_rating_id 
LEFT OUTER join appointmenttypes q1 on appointments.appointmenttype_id = q1.id
LEFT OUTER join appointmentgroups q2 on q2.id = q1.appointmentgroup_id
LEFT OUTER JOIN appointmentgrouptypes q3 ON q3.id = q1.appointmentgrouptype_id
LEFT JOIN customers ON customers.id = appointments.customer_id 
LEFT JOIN orders ON appointments.id = orders.appointment_id 
LEFT JOIN orderinfos  ON orders.id = orderinfos.order_id 
LEFT JOIN jobsites ON jobsites.id = orderinfos.jobsite_id 
LEFT JOIN appointmentstatuses ON appointmentstatuses.appointment_id = appointments.id 
LEFT JOIN jobstatuses ON jobstatuses.id = appointmentstatuses.jobstatus_id 
LEFT JOIN (
SELECT appointments.id AS APPT_ID
,COALESCE(INVTable.balance, SOTable.balance, ESTTable.balance, 0) AS BALANCE
FROM appointments
LEFT JOIN orders ON appointments.id = orders.appointment_id
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 5
) AS INVTable ON appointments.id = INVTable.APPT_ID
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 6
) AS SOTable ON appointments.id = SOTable.APPT_ID
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 4
) AS ESTTable ON appointments.id = ESTTable.APPT_ID
) AS Table1 ON appointments.id = Table1.appt_id 
WHERE appointmentifsreviews.email_sent = 0
AND q2.appointmentgrouptype_id = 2 
GROUP BY appointments.id 
ORDER BY appointmentifsreviews.created_date DESC 
) AS greenApptDetailTable ON greenApptDetailTable.APPT_ID = appointments.id LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,SUM(items.extended_price) AS rvn
FROM orders
INNER JOIN appointments ON appointments.id = orders.appointment_id
INNER JOIN items ON orders.id = items.order_id
LEFT JOIN categories ON categories.id = items.product_category_id
WHERE items.status = 0
AND items.declined = 0
AND categories.itemcategories_id = 8

    
#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 
    
    
GROUP BY orders.appointment_id
    ORDER BY orders.appointment_id ASC 
) AS Rust_Revenue ON Rust_Revenue.APPT_ID = appointments.id LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,SUM(items.extended_price) AS rvn
FROM orders
INNER JOIN appointments ON appointments.id = orders.appointment_id
INNER JOIN items ON orders.id = items.order_id
LEFT JOIN categories ON categories.id = items.product_category_id
WHERE items.status = 0
AND items.declined = 0
AND categories.itemcategories_id = 2

    
#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 
    
    
GROUP BY orders.appointment_id 
    ORDER BY orders.appointment_id ASC 
) AS Opener_Revenue ON Opener_Revenue.APPT_ID = appointments.id LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,SUM(items.extended_price) AS rvn
FROM orders
INNER JOIN appointments ON appointments.id = orders.appointment_id
INNER JOIN items ON orders.id = items.order_id
LEFT JOIN categories ON categories.id = items.product_category_id
WHERE items.status = 0
AND items.declined = 0
AND categories.itemcategories_id = 3

#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 
    
    
GROUP BY orders.appointment_id 
    ORDER BY orders.appointment_id ASC 
) AS Spring_Revenue ON Spring_Revenue.APPT_ID = appointments.id LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,SUM(items.extended_price) AS rvn
FROM orders
INNER JOIN appointments ON appointments.id = orders.appointment_id
INNER JOIN items ON orders.id = items.order_id
LEFT JOIN categories ON categories.id = items.product_category_id
WHERE items.status = 0
AND items.declined = 0
AND categories.itemcategories_id = 1
    
#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 
    
    
GROUP BY orders.appointment_id 
ORDER BY orders.appointment_id ASC 
) AS Door_Revenue ON Door_Revenue.APPT_ID = appointments.id LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,SUM(items.extended_price) AS rvn
FROM orders
INNER JOIN appointments ON appointments.id = orders.appointment_id
INNER JOIN items ON orders.id = items.order_id
LEFT JOIN categories ON categories.id = items.product_category_id
WHERE items.status = 0
AND items.declined = 0
AND categories.itemcategories_id = 4
 

#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 
    
GROUP BY orders.appointment_id 
ORDER BY orders.appointment_id ASC 
) AS Misc_Revenue ON Misc_Revenue.APPT_ID = appointments.id LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,SUM(orders.discount) AS Appt_Discount
FROM orders
INNER JOIN appointments ON appointments.id = orders.appointment_id
WHERE ordertype_id IN (
5,6
)
    
    
#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 

    
    
    
GROUP BY orders.appointment_id 
ORDER BY orders.appointment_id ASC 
) AS APDiscount ON APDiscount.APPT_ID = appointments.id 

LEFT JOIN (
SELECT appointmentifsreviews.appt_id AS APPT_ID
,CONCAT (
COALESCE(appointmentifsratings.name, 'N/A')
,' | '
,'<a href="https://app.reliablegaragedoor.com/appointments.html?appointment_id='
,appointments.id
,'">'
,CONCAT (
customers.first_name
,' '
,customers.last_name
)
,' - '
,COALESCE(jobsites.city, customers.city)
,' </a>'
,' | '
,COALESCE(CONCAT (
'$'
,FORMAT(Table1.BALANCE, 2)
), CONCAT (
'$'
,FORMAT(0, 2)
))
,' - '
,COALESCE(TIMESTAMPDIFF(MINUTE, MAX(IF (
jobstatuses.name = 'Arrived On Site'
,appointmentstatuses.jobstatus_date
,''
) )
,MAX(IF (
jobstatuses.name = 'Complete'
,appointmentstatuses.jobstatus_date
,''
) ) )
,'N/A' )
,' minutes' ) AS ORANGE_APPT_TYPES 
FROM appointmentifsreviews 
    LEFT JOIN appointments ON appointments.id = appointmentifsreviews.appt_id 
    LEFT JOIN users u1 ON u1.id = appointmentifsreviews.add_id 
    LEFT JOIN users u2 ON u2.id = appointments.fieldworker 
    LEFT JOIN appointmentifsratings ON appointmentifsratings.id = appointmentifsreviews.appt_rating_id 
    LEFT  join appointmenttypes q1 on appointments.appointmenttype_id = q1.id
    LEFT  join appointmentgroups q2 on q2.id = q1.appointmentgroup_id
    LEFT  JOIN appointmentgrouptypes q3 ON q3.id = q1.appointmentgrouptype_id
    LEFT JOIN customers ON customers.id = appointments.customer_id 
    LEFT JOIN orders ON customers.id = orders.customer_id 
    LEFT JOIN orderinfos  ON orders.id = orderinfos.order_id 
    LEFT JOIN jobsites ON jobsites.id = orderinfos.jobsite_id 
    LEFT JOIN appointmentstatuses ON appointmentstatuses.appointment_id = appointments.id 
    LEFT JOIN jobstatuses ON jobstatuses.id = appointmentstatuses.jobstatus_id 
    LEFT JOIN (
SELECT appointments.id AS APPT_ID
,COALESCE(INVTable.balance, SOTable.balance, ESTTable.balance, 0) AS BALANCE
FROM appointments
LEFT JOIN orders ON appointments.id = orders.appointment_id
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 5
) AS INVTable ON appointments.id = INVTable.APPT_ID
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 6
) AS SOTable ON appointments.id = SOTable.APPT_ID
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 4
) AS ESTTable ON appointments.id = ESTTable.APPT_ID
) AS Table1 ON appointments.id = Table1.appt_id 
    WHERE appointmentifsreviews.email_sent = 0
AND q2.appointmentgrouptype_id = 3 
    GROUP BY appointments.id 
    ORDER BY appointmentifsreviews.created_date DESC 
) AS orangeApptDetailTable ON orangeApptDetailTable.APPT_ID = appointments.id LEFT JOIN (
SELECT appointmentifsreviews.appt_id AS APPT_ID
,CONCAT (
COALESCE(appointmentifsratings.name, 'N/A')
,' | '
,'<a href="https://app.reliablegaragedoor.com/appointments.html?appointment_id='
,appointments.id
,'">'
,CONCAT (
customers.first_name
,' '
,customers.last_name
)
,' - '
,COALESCE(jobsites.city, customers.city)
,' </a>'
,' | '
,COALESCE(CONCAT (
'$'
,FORMAT(Table1.BALANCE, 2)
), CONCAT (
'$'
,FORMAT(0, 2)
))
,' - '
,COALESCE(TIMESTAMPDIFF(MINUTE, MAX(IF (
jobstatuses.name = 'Arrived On Site'
,appointmentstatuses.jobstatus_date
,''
) )
,MAX(IF (
jobstatuses.name = 'Complete'
,appointmentstatuses.jobstatus_date
,''
) ) )
,'N/A' )
,' minutes' ) AS RED_APPT_TYPES 
FROM appointmentifsreviews 
    LEFT JOIN appointments ON appointments.id = appointmentifsreviews.appt_id 
    LEFT JOIN users u1 ON u1.id = appointmentifsreviews.add_id 
    LEFT JOIN users u2 ON u2.id = appointments.fieldworker 
    LEFT JOIN appointmentifsratings ON appointmentifsratings.id = appointmentifsreviews.appt_rating_id 
    LEFT JOIN appointmenttypes q1 on appointments.appointmenttype_id = q1.id
    LEFT JOIN appointmentgroups q2 on q2.id = q1.appointmentgroup_id
    LEFT JOIN appointmentgrouptypes q3 ON q3.id = q1.appointmentgrouptype_id
    LEFT JOIN customers ON customers.id = appointments.customer_id 
    LEFT JOIN orders ON customers.id = orders.customer_id 
    LEFT JOIN orderinfos  ON orders.id = orderinfos.order_id 
    LEFT JOIN jobsites ON jobsites.id = orderinfos.jobsite_id 
    LEFT JOIN appointmentstatuses ON appointmentstatuses.appointment_id = appointments.id 
    LEFT JOIN jobstatuses ON jobstatuses.id = appointmentstatuses.jobstatus_id 
    LEFT JOIN (
SELECT appointments.id AS APPT_ID
,COALESCE(INVTable.balance, SOTable.balance, ESTTable.balance, 0) AS BALANCE
FROM appointments
LEFT JOIN orders ON appointments.id = orders.appointment_id
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 5
) AS INVTable ON appointments.id = INVTable.APPT_ID
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 6
) AS SOTable ON appointments.id = SOTable.APPT_ID
LEFT JOIN (
SELECT orders.appointment_id AS APPT_ID
,COALESCE(orders.balance_due, CONCAT (
'$'
,FORMAT(0, 2)
)) AS BALANCE
FROM orders
WHERE orders.ordertype_id = 4
) AS ESTTable ON appointments.id = ESTTable.APPT_ID
) AS Table1 ON appointments.id = Table1.appt_id WHERE appointmentifsreviews.email_sent = 0
AND q2.appointmentgrouptype_id = 4 
GROUP BY appointments.id 
ORDER BY appointmentifsreviews.created_date DESC 
) AS redApptDetailTable ON redApptDetailTable.APPT_ID = appointments.id 
LEFT JOIN (
SELECT appointmentifsreviews.appt_id AS APPT_ID
,(
CONCAT (
'<u>'
,CONCAT (
customers.first_name
,' '
,customers.last_name
)
,'</u> - '
,COALESCE(jobsites.city, customers.city)
,':<BLOCKQUOTE>"'
,COALESCE(appointmentifsreviews.note COLLATE utf8_general_ci, 'No IFS Feedback for these Appointments')
,'"</BLOCKQUOTE>'
)
) AS IFS_FEEDBACK
FROM appointmentifsreviews
LEFT JOIN appointments ON appointments.id = appointmentifsreviews.appt_id
LEFT JOIN users u1 ON u1.id = appointmentifsreviews.add_id
LEFT JOIN users u2 ON u2.id = appointments.fieldworker
LEFT JOIN appointmentifsratings ON appointmentifsratings.id = appointmentifsreviews.appt_rating_id
LEFT join appointmenttypes q1 on appointments.appointmenttype_id = q1.id
LEFT join appointmentgroups q2 on q2.id = q1.appointmentgroup_id
LEFT JOIN appointmentgrouptypes q3 ON q3.id = q1.appointmentgrouptype_id
LEFT JOIN customers ON customers.id = appointments.customer_id
LEFT JOIN orders ON customers.id = orders.customer_id
LEFT JOIN orderinfos  ON orders.id = orderinfos.order_id 
LEFT JOIN jobsites ON jobsites.id = orderinfos.jobsite_id 
LEFT JOIN appointmentstatuses ON appointmentstatuses.appointment_id = appointments.id
LEFT JOIN jobstatuses ON jobstatuses.id = appointmentstatuses.jobstatus_id
WHERE appointmentifsreviews.email_sent = 0
AND appointmentifsreviews.note IS NOT NULL
GROUP BY appointmentifsreviews.id
ORDER BY appointmentifsreviews.id ASC
) AS ifsFeedbackTable ON ifsFeedbackTable.APPT_ID = appointments.id 

/*main where clause below*/
WHERE 
appointmentifsreviews.email_sent = 0
AND appointmentifsreviews.status <> 0 


#change below for procedure
AND DATE(appointments.dateofappt) 
#BETWEEN '2020-12-14' AND CURDATE()  
BETWEEN '2020-12-14' AND '2021-04-25' 


GROUP BY u2.id, DATE(appointments.dateofappt) 
ORDER BY DATE(appointments.dateofappt) ASC
                                                                                                            
			