You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
73 lines
2.2 KiB
73 lines
2.2 KiB
DROP VIEW IF EXISTS noshowinfo_per_subscriber_view;
|
|
|
|
CREATE VIEW noshowinfo_per_subscriber_view AS
|
|
(
|
|
SELECT
|
|
usr.user_id,
|
|
usr.salutation,
|
|
usr.last_name,
|
|
usr.first_name,
|
|
usr.email_address,
|
|
(
|
|
SELECT count(evta.attendance_id)
|
|
FROM
|
|
`event_registration` AS `evtreg`,
|
|
`event_attendance` AS `evta`,
|
|
`event` AS `evt`,
|
|
`event_schedule` AS `evts`
|
|
WHERE
|
|
evts.event_schedule_id = evtreg.event_schedule_id
|
|
AND evta.registration_id = evtreg.registration_id
|
|
AND evtreg.subscriber = `usr`.user_id
|
|
AND evts.no_show_stat = 1
|
|
AND evt.event_id = evts.event_id
|
|
AND evta.attendance_type = 0
|
|
AND evtreg.status = 1
|
|
AND evta.is_attended = 0
|
|
AND evt.event_category IN ('REGULAR_EVENT')
|
|
) AS `noshow_complete`,
|
|
(
|
|
SELECT count(evta.attendance_id)
|
|
FROM
|
|
`event_registration` AS `evtreg`,
|
|
`event_attendance` AS `evta`,
|
|
`event` AS `evt`,
|
|
`event_schedule` AS `evts`
|
|
WHERE evts.event_schedule_id = evtreg.event_schedule_id
|
|
AND evta.registration_id = evtreg.registration_id
|
|
AND evtreg.subscriber = `usr`.user_id
|
|
AND evts.no_show_stat = 1
|
|
AND evt.event_id = evts.event_id
|
|
AND evta.attendance_type = 0
|
|
AND evtreg.status = 1
|
|
AND evta.is_attended = 2
|
|
AND evt.event_category IN ('REGULAR_EVENT')
|
|
) AS `noshow_partial`,
|
|
(
|
|
SELECT count(evta.attendance_id)
|
|
FROM
|
|
`event_registration` AS `evtreg`,
|
|
`event_attendance` AS `evta`,
|
|
`event` AS `evt`,
|
|
`event_schedule` AS `evts`
|
|
INNER JOIN gm_evsched_setting ges USING (event_schedule_id)
|
|
WHERE
|
|
evts.event_schedule_id = evtreg.event_schedule_id
|
|
AND evta.registration_id = evtreg.registration_id
|
|
AND evtreg.subscriber = `usr`.user_id
|
|
AND evts.no_show_stat = 1
|
|
AND evt.event_id = evts.event_id
|
|
AND evta.attendance_type = 0
|
|
AND evtreg.status = 1
|
|
AND evta.is_attended = 0
|
|
AND evt.event_category IN ('REGULAR_EVENT')
|
|
AND evts.start_date_time > DATE_SUB(NOW(),
|
|
INTERVAL (SELECT nshow_period FROM gm_subsettings_view) DAY)
|
|
AND ges.mod_stat = 1
|
|
) AS `noshow_moderation`
|
|
|
|
FROM
|
|
`user` AS `usr`
|
|
WHERE
|
|
usr.STATUS = 1
|
|
ORDER BY `usr`.`user_id` ASC)
|