Attendance Type Settings Overview

USE [ArenaDB]
GO

/****** Object:  View [dbo].[cust_CCIW_v_occurence_type]    Script Date: 9/22/2020 3:00:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- Meaning of core_occurrence_type_template.occurrence_freq_type and freq_qualifier
------------------------------------------------------------------------------------
-- Undefined = -1
-- Daily = 0        freq_qualifier: ""
-- Weekly = 1;      freq_qualifier: 0=Sun, 1=M, 2=Tu, 3=Wed, ... , 6=Sat (string)
-- Monthly = 2      freq_qualifier: 1=first day of month; 31=last day of month (string)
-- OneTime = 3      freq_qualifier: date (string)

CREATE VIEW [dbo].[cust_CCIW_v_occurence_type]
AS
WITH occurence_frequencies
AS (
    SELECT T1.occurrence_type_id
        ,STUFF((
                SELECT ', ' + T2.schedule_name + ' (' +
                    (CASE occurrence_freq_type
                        WHEN  0 THEN 'Daily'
                        WHEN  1 THEN 'Weekly'
                        WHEN  2 THEN 'Monthly'
                        WHEN  3 THEN 'OneTime' END)
                    + ')'
                FROM core_occurrence_type_template T2
                WHERE T2.occurrence_type_id = T1.occurrence_type_id
                FOR XML PATH('')
                ), 1, 1, '') AS Freqs
    FROM core_occurrence_type_template T1
    GROUP BY T1.occurrence_type_id
    HAVING occurrence_type_id IS NOT NULL
    ),

occurrence_labels
AS (
    SELECT T1.occurrence_type_id
        ,STUFF((
                SELECT ', ' + T2.report_name
                FROM cust_CCIW_v_occurrence_labels T2
                WHERE T2.occurrence_type_id = T1.occurrence_type_id
                FOR XML PATH('')
                ), 1, 1, '') AS labels
    FROM cust_CCIW_v_occurrence_labels T1
    GROUP BY T1.occurrence_type_id
    HAVING occurrence_type_id IS NOT NULL
    ),
available_kiosks
AS (
SELECT lt.occurrence_type_id
        ,STUFF((
                SELECT ', ' + s.notes
                FROM [orgn_location_occurrence_type] lt2
                JOIN comp_system_location sl ON lt2.location_id = sl.location_id
                JOIN comp_system s ON s.system_id = sl.system_id
                WHERE lt2.occurrence_type_id = lt.occurrence_type_id
                FOR XML PATH('')
                ), 1, 1, '') AS available_kiosks
    FROM [orgn_location_occurrence_type] lt
    JOIN comp_system_location sl ON lt.location_id = sl.location_id
    GROUP BY lt.occurrence_type_id
),

available_locations
AS (
    SELECT lt.occurrence_type_id
        ,STUFF((
                SELECT ', ' + l.location_name
                FROM orgn_location_occurrence_type lt2
                JOIN orgn_location l ON lt2.location_id = l.location_id
                WHERE lt.occurrence_type_id = lt2.occurrence_type_id
                FOR XML PATH('')
                ), 1, 1, '') AS available_locations
    FROM [orgn_location_occurrence_type] lt
    GROUP BY lt.occurrence_type_id
)

SELECT TOP 10000 t.occurrence_type_id -- used top here so that order by could be used
    ,t.type_name
    ,t.group_id
    ,g.group_name
    ,Active = t.active
    ,[Settings Review] = CASE
        WHEN membership_required = 1
            AND COALESCE(sync_with_profile, sync_with_cluster, sync_with_group, NULL) IS NULL
            THEN 'Membership_required but no syncing object specified. '
        ELSE ''
        END
    ,count_as_weekend_attendance = t.is_service
    ,count_as_volunteer_attendance = CASE -- This custom to the way CCIW indicates volunteer attendance
        WHEN profile_source_luid = 11592
            THEN 1
        ELSE 0
        END
    ,t.membership_required
    ,membership_required_in = p.profile_name
    ,v.profile_name AS leader_tag
    ,v.profile_id AS leader_tag_id
    ,profile_id = t.sync_with_profile
    ,location_specific_occurrences_enabled = t.location_specific_occurrences
    ,OL.labels
    ,[Uses_Override_Label] = CASE WHEN S.occurrence_type_id is not null  THEN 'Yes' ELSE 'No' END
    ,OCF.Freqs
     ,AL.available_locations
    ,AK.available_kiosks
    ,CASE
        WHEN a.t IS NULL
            THEN 0
        ELSE a.t
        END AS total_attendance

FROM core_occurrence_type t
JOIN core_occurrence_type_group g ON g.group_id = t.group_id
LEFT JOIN core_profile p ON p.profile_id = t.sync_with_profile
LEFT JOIN core_occurrence_type_leader l ON l.occurrence_type_id = t.occurrence_type_id
LEFT JOIN core_profile v ON v.profile_id = l.profile_id
LEFT JOIN occurrence_labels OL ON OL.occurrence_type_id = t.occurrence_type_id
LEFT JOIN occurence_frequencies OCF on OCF.occurrence_type_id = t.occurrence_type_id
LEFT JOIN (
    SELECT DISTINCT OL2.occurrence_type_id
    FROM cust_CCIW_v_occurrence_labels OL2
    WHERE OL2.shared_filter = 'overridden'
    ) S ON S.occurrence_type_id = t.occurrence_type_id
LEFT JOIN (
    SELECT occurrence_type
        ,SUM(attendance) AS t
    FROM core_v_occurrence
    GROUP BY occurrence_type
    ) AS a ON a.occurrence_type = t.occurrence_type_id
LEFT JOIN available_locations AL ON AL.occurrence_type_id = T.occurrence_type_id
LEFT JOIN available_kiosks AK ON AK.occurrence_type_id = T.occurrence_type_id
ORDER BY group_name, type_name
GO