{% comment %}
    Date:       2023-02-07
    Purpose:    Shortcode that will create metrics data for a New CRs / Leads per WEEK based on MONDAY to SUNDAY of the week regardless of the months/years, by Campus partition.
                This code will go backword in time based on the variable MONTHSBACK and update the metric.
    Created by: Trinity Fellowship Church, 5000 Hollywood Rd. Amarillo, TX 79118 
{% endcomment %}
{% if weeksback == 'No' or optypeid == 'No' %}
{% else %}
    {% if weeksback <= 0 %}{% assign weeksback = 1 %}{% endif %}
    {% assign thisweekssunday = 'Now' | SundayDate | Date:'MM/dd/yyyy' %}
    {% assign camps = 'Global' | Attribute:'ShortCodeMetricsCampuses','RawValue' | Split:',' %}
    {% assign campsct = camps | Size %}
    {% assign thekey1 = 'CRsOpWeeklyCT>' %}
    {% connectionopportunity where:'IsActive == "true" && Id == {{ optypeid }}' securityenabled:'false' %}
        {% assign colist = connectionopportunityItems %}
    {% endconnectionopportunity %}

    {% if campsct and campsct >= 0 %}

        {% for thiscampus in camps %}

            {% campus where:'Guid == "{{ thiscampus }}"' securityenabled:'false' %}
                {% assign lavacpid = campus.Id %}
                {% assign thekey = thekey1 | Append:lavacpid | Append:'>' %}
                {% for item in colist %}
                {% assign lavacopid = item.Id %}

                {% sql %}
                        DECLARE @i AS INT = 0;
                        DECLARE @thisweekssunday AS DATE = '{{ thisweekssunday | DATE:'MM/dd/yyyy' }}';
                        DECLARE @weeksback AS INT = {{ weeksback }}; 
                        DECLARE @lavakey AS NVARCHAR(240) = '{{ thekey }}';
                        DECLARE @cpid AS INT = {{ lavacpid }};
                        DECLARE @copid AS INT = {{ lavacopid }};

                        WHILE @i <= @weeksback
                            BEGIN
                            DECLARE @rowct AS INT = 0;
                            DECLARE @uwksunday AS DATE = DATEADD(wk,@i*-1,@thisweekssunday);
                            DECLARE @uwkmonday AS DATE = DATEADD(day,-6,@uwksunday);
                            DECLARE @thekey AS NVARCHAR(240) = CAST(@lavakey AS NVARCHAR) + CAST(FORMAT(@uwksunday,'MM/dd/yyyy') AS NVARCHAR);

                            DECLARE @queryliveinsert AS NVARCHAR(max) = 'INSERT INTO [_com_trinityfellowship_MetricsTemp]
                                        OUTPUT INSERTED.ID
                                        SELECT COUNT(cr.[Id]) AS [COUNT]
                                        ,cr.[CampusId] AS [CampusId]
                                        ,cr.[ConnectionOpportunityId] AS [Partiion001]
                                        ,0 AS [Partition002]
                                        ,0 AS [Partition003]
                                        ,'''
                                        + CONVERT(NVARCHAR(10),@uwksunday,101) + ''' AS [ThisWeeksSunday]
                                        ,''' + @thekey + ''' AS [TheKey]
                                        FROM [ConnectionRequest] cr
                                        WHERE cr.[ConnectionOpportunityId] = '
                                        + CAST(@copid AS NVARCHAR) + ' AND cr.[CampusId] = '
                                        + CAST(@cpid AS NVARCHAR) + ' AND cr.[CreatedDateTime] >= '''
                                        + CONVERT(NVARCHAR(10),@uwkmonday,101) + ''' AND cr.[CreatedDateTime] < '''
                                        + CONVERT(NVARCHAR(10),DATEADD(day,1,@uwksunday),101) + '''
                                        GROUP BY cr.[CampusId], cr.[ConnectionOpportunityId]'

                            DECLARE @querylivezero AS NVARCHAR(max) = 'INSERT INTO [_com_trinityfellowship_MetricsTemp]
                                        OUTPUT INSERTED.ID
                                        SELECT 0 AS [Count]
                                        ,' + CAST(@cpid AS NVARCHAR) + ' AS [CampusId]
                                        ,' + CAST(@copid AS NVARCHAR) + ' AS [Partition001]
                                        ,0 AS [Partition002]
                                        ,0 AS [Partition003]
                                        ,'''
                                        + CONVERT(NVARCHAR(10),@uwksunday,101) + ''' AS [ThisWeeksSunday]
                                        ,''' + @thekey + ''' AS [TheKey]'

                            DECLARE @querycount AS NVARCHAR(max) = 'SELECT 
                                        @C = COUNT(cr.[Id])
                                        FROM [ConnectionRequest] cr
                                       WHERE cr.[ConnectionOpportunityId] = '
                                        + CAST(@copid AS NVARCHAR) + ' AND cr.[CampusId] = '
                                        + CAST(@cpid AS NVARCHAR) + ' AND cr.[CreatedDateTime] >= '''
                                        + CONVERT(NVARCHAR(10),@uwkmonday,101) + ''' AND cr.[CreatedDateTime] < '''
                                        + CONVERT(NVARCHAR(10),DATEADD(day,1,@uwksunday),101) + '''
                                        GROUP BY cr.[CampusId], cr.[ConnectionOpportunityId]'

                            EXEC sp_executesql @querycount, N'@C INT OUTPUT', @C=@rowct OUTPUT

                            IF (@rowct > 0)
                                BEGIN
                                EXEC(@queryliveinsert);
                                END
                            ELSE
                                BEGIN
                                EXEC(@querylivezero);
                                END

                            SET @i = @i + 1
                            END;
                {% endsql %}
                {% endfor %}
            {% endcampus %}
        {% endfor %}
    {% endif %}

    {% sql %}

        DECLARE @lavakey AS NVARCHAR(240) = '{{ thekey1 }}' + '%';
        DECLARE @thisweekssunday AS DATE = '{{ thisweekssunday | DATE:'MM/dd/yyyy' }}';
        DECLARE @weeksback AS INT = {{ weeksback }};
        DECLARE @uwkmonday AS DATE = DATEADD(day,-6,DATEADD(wk,(@weeksback+1)*-1,@thisweekssunday));
        
        SELECT SUM([Count]) AS [Count]
        ,[CampusId]
        ,[ThisWeeksSunday]
        ,[Partition001]
        FROM [_com_trinityfellowship_MetricsTemp]
        Where [TheKey] LIKE '%' + @lavakey + '%'
        AND [ThisWeeksSunday] >= @uwkmonday AND [ThisWeeksSunday] <= @thisweekssunday
        Group By [CampusId],[ThisWeeksSunday],[Partition001]
        ORDER BY [ThisWeeksSunday],[CampusId],[Partition001];

    {% endsql %}

    {% for item in results %}
        {{ item.Count }}, {{ item.ThisWeeksSunday | Date:'MM/dd/yyyy' }}, {{ item.CampusId }},<br>
    {% endfor %}
    
    {% capture reply %}
        {[ metricscleanup thekey:'{{ thekey1 }}']}
    {% endcapture %}

{% endif %}