USE [Polaris]
GO

/****** Object:  StoredProcedure [Polaris].[SILS_RPT_Notice_Forecast]    Script Date: 2023-11-24 8:36:20 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		Jason Tenter
-- Create date: 2020-10-20
-- Description:	Compiles a list of notices that will be sent over the next few days, using SA notice settings for accuracy.
-- =============================================

CREATE PROCEDURE [Polaris].[SILS_RPT_Notice_Forecast]

AS
BEGIN

	SET NOCOUNT ON;


CREATE TABLE #Attr
(
    AttrID int,
    DefaultValue nvarchar(255)
)

INSERT INTO #Attr
SELECT
    aa.AttrID,
    aa.DefaultValue
FROM Polaris.AdminAttributes aa (nolock)
WHERE aa.AttrID in 
(
    1612,   -- Enable overdue notices
    995,    -- 1st ovd days Interval
    2696,   -- 1st ovd addition TXT message
    996,    -- 2nd ovd days Interval
    2697,   -- 2nd ovd addition TXT message
    997,    -- 3rd ovd days Interval
    2698,   -- 3rd ovd addition TXT message
    1613,   -- Enable billing notices
    998,    -- Bill days Interval
    2149,   -- Bill Notification method
    2699,   -- Bill addition TXT message
    1614,   -- Enable request notices
    2701,   -- Request addition TXT message
    3027,   -- Enable 2nd request notices
    3030,   -- 2nd request days interval
    3029,   -- 2nd request addition TXT message
    2141,   -- Enable canceled request notices
    2702,   -- Cancelled request addition TXT message
    2703,   -- Reminders: addition TXT message
    1276,   -- Enable almost overdue reminders
    1270,   -- AOvd Number of days before an item is overdue
    1869,   -- AOvd Number of days between reminders
    1275,   -- Enable expiring patron reminders
    1269    -- Expiring patron days until expiration
)
GROUP BY
    aa.AttrID,
    aa.DefaultValue


CREATE TABLE #Org_Attr
(
    OrganizationID int,
    AttrID int
    --[Value] nvarchar(255)
)

INSERT INTO #Org_Attr
SELECT
    o.OrganizationID,
    a.AttrID
FROM Polaris.Organizations o (nolock),
#Attr a
WHERE o.OrganizationCodeID = 3


CREATE TABLE #Org_Values
(
    OrganizationID int,
    AttrID int,
    [Value] nvarchar(255)
)

INSERT INTO #Org_Values
SELECT
    oa.OrganizationID,
    oa.AttrID,
    CASE
        WHEN op3.VALUE IS NOT NULL THEN CASE
            WHEN op3.VALUE = 'Yes' THEN 1
            WHEN op3.VALUE = 'No' THEN 0
            ELSE op3.VALUE END
        WHEN op2.VALUE IS NOT NULL THEN CASE
            WHEN op2.VALUE = 'Yes' THEN 1
            WHEN op2.VALUE = 'No' THEN 0
            ELSE op2.VALUE END
        WHEN op1.VALUE IS NOT NULL THEN CASE
            WHEN op1.VALUE = 'Yes' THEN 1
            WHEN op1.VALUE = 'No' THEN 0
            ELSE op1.VALUE END
        ELSE a.DefaultValue
    END
FROM #Org_Attr oa
LEFT JOIN #Attr a
    on oa.AttrID = a.AttrID
INNER JOIN Polaris.organizations o3 (nolock)
    on oa.OrganizationID = o3.OrganizationID
INNER JOIN Polaris.organizations o2 (nolock)
    on o3.ParentOrganizationID = o2.OrganizationID
INNER JOIN Polaris.organizations o1 (nolock)
    on o2.ParentOrganizationID = o1.OrganizationID
LEFT JOIN Polaris.OrganizationsPPPP op3 (nolock)
    on o3.OrganizationID = op3.OrganizationID
        AND oa.AttrID = op3.AttrID
LEFT JOIN Polaris.OrganizationsPPPP op2 (nolock)
    on o2.OrganizationID = op2.OrganizationID
        AND oa.AttrID = op2.AttrID
LEFT JOIN Polaris.OrganizationsPPPP op1 (nolock)
    on o1.OrganizationID = op1.OrganizationID
        AND oa.AttrID = op1.AttrID
GROUP BY
    oa.OrganizationID,
    oa.AttrID,
    CASE
        WHEN op3.VALUE IS NOT NULL THEN CASE
            WHEN op3.VALUE = 'Yes' THEN 1
            WHEN op3.VALUE = 'No' THEN 0
            ELSE op3.VALUE END
        WHEN op2.VALUE IS NOT NULL THEN CASE
            WHEN op2.VALUE = 'Yes' THEN 1
            WHEN op2.VALUE = 'No' THEN 0
            ELSE op2.VALUE END
        WHEN op1.VALUE IS NOT NULL THEN CASE
            WHEN op1.VALUE = 'Yes' THEN 1
            WHEN op1.VALUE = 'No' THEN 0
            ELSE op1.VALUE END
        ELSE a.DefaultValue
    END

--SELECT TOP 1000 * FROM #Org_Values


CREATE TABLE #Org_Settings
(
    JKey int,
	OrganizationID int,
    --ParentOrganizationID int,
    --OrganizationCodeID int,
    OD1_On bit,
    OD1_Days int,
    OD1_Txt bit,
    OD2_On bit,
    OD2_Days int,
    OD2_Txt bit,
    OD3_On bit,
    OD3_Days int,
    OD3_Txt bit,
    Bill_On bit,
    Bill_Days int,
    Bill_DO int,
    Bill_Txt bit,
    Hold1_On bit,
    Hold1_Txt bit,
    Hold2_On bit,
    Hold2_days int,
    Hold2_Txt bit,
    Cancel_On bit,
    Cancel_Txt bit,
    Remind_Txt bit,
    AOD_On bit,
    AOD_Days int,
    AOD_Interval int,
    PatExp_On bit,
    PatExp_Days int
)

INSERT INTO #Org_Settings
SELECT 
    1,
	v.OrganizationID,
    OD1_On.Value,
    OD1_Days.Value,
    OD1_Txt.Value,
    CASE WHEN OD2_Days.Value > 0 AND OD1_On.Value = 1 THEN 1 ELSE 0 END,
    OD2_Days.Value,
    OD2_Txt.Value,
    CASE WHEN OD3_Days.Value > 0 AND OD1_On.Value = 1 THEN 1 ELSE 0 END,
    OD3_Days.Value,
    OD3_Txt.Value,
    Bill_On.Value,
    Bill_Days.Value,
    Bill_DO.Value,
    Bill_Txt.Value,
    Hold1_On.Value,
    Hold1_Txt.Value,
    Hold2_On.Value,
    Hold2_days.Value,
    Hold2_Txt.Value,
    Cancel_On.Value,
    Cancel_Txt.Value,
    Remind_Txt.Value,
    AOD_On.Value,
    AOD_Days.Value,
    AOD_Interval.Value,
    PatExp_On.Value,
    PatExp_Days.Value
FROM #Org_Values v
LEFT JOIN #Org_Values OD1_On
    on v.OrganizationID = OD1_On.OrganizationID and OD1_On.AttrID = 1612
LEFT JOIN #Org_Values OD1_Days
    on v.OrganizationID = OD1_Days.OrganizationID and OD1_Days.AttrID = 995
LEFT JOIN #Org_Values OD1_Txt
    on v.OrganizationID = OD1_Txt.OrganizationID and OD1_Txt.AttrID = 2696
LEFT JOIN #Org_Values OD2_Days
    on v.OrganizationID = OD2_Days.OrganizationID and OD2_Days.AttrID = 996
LEFT JOIN #Org_Values OD2_Txt
    on v.OrganizationID = OD2_Txt.OrganizationID and OD2_Txt.AttrID = 2697
LEFT JOIN #Org_Values OD3_Days
    on v.OrganizationID = OD3_Days.OrganizationID and OD3_Days.AttrID = 997
LEFT JOIN #Org_Values OD3_Txt
    on v.OrganizationID = OD3_Txt.OrganizationID and OD3_Txt.AttrID = 2698
LEFT JOIN #Org_Values Bill_On
    on v.OrganizationID = Bill_On.OrganizationID and Bill_On.AttrID = 1613
LEFT JOIN #Org_Values Bill_Days
    on v.OrganizationID = Bill_Days.OrganizationID and Bill_Days.AttrID = 998
LEFT JOIN #Org_Values Bill_DO
    on v.OrganizationID = Bill_DO.OrganizationID and Bill_DO.AttrID = 2149
LEFT JOIN #Org_Values Bill_Txt
    on v.OrganizationID = Bill_Txt.OrganizationID and Bill_Txt.AttrID = 2699
LEFT JOIN #Org_Values Hold1_On
    on v.OrganizationID = Hold1_On.OrganizationID and Hold1_On.AttrID = 1614
LEFT JOIN #Org_Values Hold1_Txt
    on v.OrganizationID = Hold1_Txt.OrganizationID and Hold1_Txt.AttrID = 2701
LEFT JOIN #Org_Values Hold2_On
    on v.OrganizationID = Hold2_On.OrganizationID and Hold2_On.AttrID = 3027
LEFT JOIN #Org_Values Hold2_days
    on v.OrganizationID = Hold2_days.OrganizationID and Hold2_days.AttrID = 3030
LEFT JOIN #Org_Values Hold2_Txt
    on v.OrganizationID = Hold2_Txt.OrganizationID and Hold2_Txt.AttrID = 3029
LEFT JOIN #Org_Values Cancel_On
    on v.OrganizationID = Cancel_On.OrganizationID and Cancel_On.AttrID = 2141
LEFT JOIN #Org_Values Cancel_Txt
    on v.OrganizationID = Cancel_Txt.OrganizationID and Cancel_Txt.AttrID = 2702
LEFT JOIN #Org_Values Remind_Txt
    on v.OrganizationID = Remind_Txt.OrganizationID and Remind_Txt.AttrID = 2703
LEFT JOIN #Org_Values AOD_On
    on v.OrganizationID = AOD_On.OrganizationID and AOD_On.AttrID = 1276
LEFT JOIN #Org_Values AOD_Days
    on v.OrganizationID = AOD_Days.OrganizationID and AOD_Days.AttrID = 1270
LEFT JOIN #Org_Values AOD_Interval
    on v.OrganizationID = AOD_Interval.OrganizationID and AOD_Interval.AttrID = 1869
LEFT JOIN #Org_Values PatExp_On
    on v.OrganizationID = PatExp_On.OrganizationID and PatExp_On.AttrID = 1275
LEFT JOIN #Org_Values PatExp_Days
    on v.OrganizationID = PatExp_Days.OrganizationID and PatExp_Days.AttrID = 1269
GROUP BY
    v.OrganizationID,
    OD1_On.Value,
    OD1_Days.Value,
    OD1_Txt.Value,
    CASE WHEN OD2_Days.Value > 0 AND OD1_On.Value = 1 THEN 1 ELSE 0 END,
    OD2_Days.Value,
    OD2_Txt.Value,
    CASE WHEN OD3_Days.Value > 0 AND OD1_On.Value = 1 THEN 1 ELSE 0 END,
    OD3_Days.Value,
    OD3_Txt.Value,
    Bill_On.Value,
    Bill_Days.Value,
    Bill_DO.Value,
    Bill_Txt.Value,
    Hold1_On.Value,
    Hold1_Txt.Value,
    Hold2_On.Value,
    Hold2_days.Value,
    Hold2_Txt.Value,
    Cancel_On.Value,
    Cancel_Txt.Value,
    Remind_Txt.Value,
    AOD_On.Value,
    AOD_Days.Value,
    AOD_Interval.Value,
    PatExp_On.Value,
    PatExp_Days.Value


/*
SELECT 
    o.name Branch,
    os.*
FROM #Org_Settings os
INNER JOIN Polaris.organizations o (nolock)
    on os.OrganizationID = o.OrganizationID
*/

-------------------                                         Circ Notices Data                                             ----------------------

CREATE TABLE #Dates (SpreadDates date)

;WITH sample AS (
  SELECT DATEADD(dd, -100, CONVERT(Date, getdate())) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM sample s
   WHERE DATEADD(dd, 1, dt) <= DATEADD(dd, -40, CONVERT(Date, getdate())))
INSERT INTO #Dates
SELECT * 
  FROM sample

;WITH sample AS (
  SELECT DATEADD(dd, -39, CONVERT(Date, getdate())) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
    FROM sample s
   WHERE DATEADD(dd, 1, dt) <= DATEADD(dd, 17, CONVERT(Date, getdate())))
INSERT INTO #Dates
SELECT * 
  FROM sample


CREATE TABLE #DueDates
(
    OrganizationID int,
    DueDate date,
    Count_ItemsDue int
)

INSERT INTO #DueDates
SELECT
    o.OrganizationID,
    d.SpreadDates,
    ISNULL(COUNT(DISTINCT ic.ItemRecordID),0) Count_ItemsDue
FROM Polaris.Organizations o (nolock)
CROSS JOIN #Dates d
LEFT JOIN Polaris.ItemCheckouts ic (nolock)
    on o.OrganizationID = ic.OrganizationID
        and CONVERT(Date, ic.DueDate) = d.SpreadDates
--and ic.ItemRecordID = 3185341
GROUP BY
    o.OrganizationID,
    d.SpreadDates
ORDER BY 
    o.OrganizationID,
    d.SpreadDates ASC


CREATE TABLE #DueDatesPats
(
    OrganizationID int,
    DueDate date,
    OD_Recv_Emails int,
    Bill_Recv_Emails int
)

INSERT INTO #DueDatesPats
SELECT
    ic.OrganizationID,
    CONVERT(Date, ic.DueDate) DueDate,
    ISNULL(COUNT(distinct pro.PatronID),0) OD_Recv_Emails,
    ISNULL(COUNT(distinct prb.PatronID),0) Bill_Recv_Emails
FROM Polaris.ItemCheckouts ic (nolock)
LEFT JOIN Polaris.PatronRegistration pro (nolock)
    on ic.PatronID = pro.PatronID 
        and pro.DeliveryOptionID = 2 and pro.ExcludeFromOverdues != 1
LEFT JOIN Polaris.PatronRegistration prb (nolock)
    on ic.PatronID = prb.PatronID 
        and prb.DeliveryOptionID = 2 and prb.ExcludeFromBills != 1
WHERE ic.DueDate BETWEEN DATEADD(dd, -100, CONVERT(Date, getdate())) and DATEADD(dd, 17, CONVERT(Date, getdate()))
GROUP BY
    ic.OrganizationID,
    CONVERT(Date, ic.DueDate)
ORDER BY 
    ic.OrganizationID,
    CONVERT(Date, ic.DueDate) ASC


CREATE TABLE #DueDatesSaskTel
(
    OrganizationID int,
    DueDate date,
    OD_Recv_SaskTel int,
    Bill_Recv_SaskTel int
)

INSERT INTO #DueDatesSaskTel
SELECT
    ic.OrganizationID,
    CONVERT(Date, ic.DueDate) DueDate,
    ISNULL(COUNT(distinct pro.PatronID),0) OD_Recv_SaskTel,
    ISNULL(COUNT(distinct prb.PatronID),0) Bill_Recv_SaskTel
FROM Polaris.ItemCheckouts ic (nolock)
LEFT JOIN Polaris.PatronRegistration pro (nolock)
    on ic.PatronID = pro.PatronID 
        and pro.DeliveryOptionID = 2 and pro.ExcludeFromOverdues != 1
        AND pro.EmailAddress like '%SaskTel%'
LEFT JOIN Polaris.PatronRegistration prb (nolock)
    on ic.PatronID = prb.PatronID 
        and prb.DeliveryOptionID = 2 and prb.ExcludeFromBills != 1
        AND prb.EmailAddress like '%SaskTel%'
WHERE ic.DueDate BETWEEN DATEADD(dd, -100, CONVERT(Date, getdate())) and DATEADD(dd, 17, CONVERT(Date, getdate()))
GROUP BY
    ic.OrganizationID,
    CONVERT(Date, ic.DueDate)
ORDER BY 
    ic.OrganizationID,
    CONVERT(Date, ic.DueDate) ASC


-- Almost Overdue due dates - these are sent by patron branch, so need to be calculated separately


CREATE TABLE #DueDatesPats_AOD
(
    OrganizationID int,
    DueDate date,
    AOD_Recv_Emails int
)

INSERT INTO #DueDatesPats_AOD
SELECT
    p.OrganizationID,
    CONVERT(Date, ic.DueDate) DueDate,
    ISNULL(COUNT(distinct pra.PatronID),0) AOD_Recv_Emails
FROM Polaris.ItemCheckouts ic (nolock)
INNER JOIN Polaris.Patrons p (nolock)
    on p.PatronID = ic.PatronID
LEFT JOIN Polaris.PatronRegistration pra (nolock)
    on p.PatronID = pra.PatronID 
        and pra.ExcludeFromAlmostOverdueAutoRenew != 1
        and pra.EmailAddress is not null
        and pra.EmailAddress like '%@%'
WHERE ic.DueDate BETWEEN DATEADD(dd, -100, CONVERT(Date, getdate())) and DATEADD(dd, 17, CONVERT(Date, getdate()))
GROUP BY
    p.OrganizationID,
    CONVERT(Date, ic.DueDate)
ORDER BY 
    p.OrganizationID,
    CONVERT(Date, ic.DueDate) ASC


CREATE TABLE #DueDatesSaskTel_AOD
(
    OrganizationID int,
    DueDate date,
    AOD_Recv_SaskTel int
)

INSERT INTO #DueDatesSaskTel_AOD
SELECT
    p.OrganizationID,
    CONVERT(Date, ic.DueDate) DueDate,
    ISNULL(COUNT(distinct pra.PatronID),0) AOD_Recv_SaskTel
FROM Polaris.ItemCheckouts ic (nolock)
INNER JOIN Polaris.Patrons p (nolock)
    on p.PatronID = ic.PatronID
LEFT JOIN Polaris.PatronRegistration pra (nolock)
    on p.PatronID = pra.PatronID 
        and pra.ExcludeFromAlmostOverdueAutoRenew != 1
        and pra.EmailAddress is not null
        AND pra.EmailAddress like '%SaskTel%'
WHERE ic.DueDate BETWEEN DATEADD(dd, -100, CONVERT(Date, getdate())) and DATEADD(dd, 17, CONVERT(Date, getdate()))
GROUP BY
    p.OrganizationID,
    CONVERT(Date, ic.DueDate)
ORDER BY 
    p.OrganizationID,
    CONVERT(Date, ic.DueDate) ASC


CREATE TABLE #DueDates2
(
    OrganizationID int,
    DueDate date,
    AOD_DueDate date,
    OD1_DueDate date,
    OD2_DueDate date,
    OD3_DueDate date,
    Bill_DueDate date,
    Count_ItemsDue int,
    AOD_Recv_Emails int,
    OD_Recv_Emails int,
    Bill_Recv_Emails int,
    AOD_Recv_SaskTel int,
    OD_Recv_SaskTel int,
    Bill_Recv_SaskTel int
)

INSERT INTO #DueDates2
SELECT
    dd.OrganizationID,
    dd.DueDate,
    CASE
        WHEN osa.AOD_On = 1 THEN DATEADD(dd, -osa.AOD_Days, dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.OD1_On = 1 THEN DATEADD(dd, (os.OD1_Days +1), dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.OD2_On = 1 THEN DATEADD(dd, (os.OD2_Days +1), dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.OD3_On = 1 THEN DATEADD(dd, (os.OD3_Days +1), dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.Bill_On = 1 AND os.Bill_DO in (0,2) THEN DATEADD(dd, (os.Bill_Days +1), dd.DueDate)
        ELSE NULL
    END,
    ISNULL(dd.Count_ItemsDue, 0),
    ISNULL(ddpa.AOD_Recv_Emails, 0),
    ISNULL(ddp.OD_Recv_Emails, 0),
    ISNULL(ddp.bill_Recv_Emails, 0),
    ISNULL(ddsa.AOD_Recv_SaskTel, 0),
    ISNULL(dds.OD_Recv_SaskTel, 0),
    ISNULL(dds.Bill_Recv_SaskTel, 0)
FROM #DueDates dd
INNER JOIN #Org_Settings os
    on dd.OrganizationID = os.OrganizationID
LEFT JOIN #DueDatesPats ddp
    on dd.OrganizationID = ddp.OrganizationID
        and dd.DueDate = ddp.DueDate
LEFT JOIN #DueDatesSaskTel dds
    on dd.OrganizationID = dds.OrganizationID
        and dd.DueDate = dds.DueDate
LEFT JOIN #DueDatesPats_AOD ddpa
    on dd.OrganizationID = ddpa.OrganizationID
        and dd.DueDate = ddpa.DueDate
LEFT JOIN #Org_Settings osa
    on ddpa.OrganizationID = osa.OrganizationID
LEFT JOIN #DueDatesSaskTel_AOD ddsa
    on dd.OrganizationID = ddsa.OrganizationID
        and dd.DueDate = ddsa.DueDate
GROUP BY
    dd.OrganizationID,
    dd.DueDate,
    CASE
        WHEN osa.AOD_On = 1 THEN DATEADD(dd, -osa.AOD_Days, dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.OD1_On = 1 THEN DATEADD(dd, (os.OD1_Days +1), dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.OD2_On = 1 THEN DATEADD(dd, (os.OD2_Days +1), dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.OD3_On = 1 THEN DATEADD(dd, (os.OD3_Days +1), dd.DueDate)
        ELSE NULL
    END,
    CASE
        WHEN os.Bill_On = 1 AND os.Bill_DO in (0,2) THEN DATEADD(dd, (os.Bill_Days +1), dd.DueDate)
        ELSE NULL
    END,
    ISNULL(dd.Count_ItemsDue, 0),
    ISNULL(ddpa.AOD_Recv_Emails, 0),
    ISNULL(ddp.OD_Recv_Emails, 0),
    ISNULL(ddp.bill_Recv_Emails, 0),
    ISNULL(ddsa.AOD_Recv_SaskTel, 0),
    ISNULL(dds.OD_Recv_SaskTel, 0),
    ISNULL(dds.Bill_Recv_SaskTel, 0)
ORDER BY dd.OrganizationID, dd.DueDate ASC

DECLARE @DD TABLE (DueDate date, DueDate_Named Varchar(30), Count_ItemsDue int)
DECLARE @AOD Table (AOD_DueDate date, ECount int, STCount int)
DECLARE @OD1 Table (OD1_DueDate date, ECount int, STCount int)
DECLARE @OD2 Table (OD2_DueDate date, ECount int, STCount int)
DECLARE @OD3 Table (OD3_DueDate date, ECount int, STCount int)
DECLARE @Bill Table (Bill_DueDate date, ECount int, STCount int)

INSERT INTO @DD
SELECT
    dd.DueDate,
    CONVERT(Varchar(15), dd.DueDate) + ' (' + LEFT(DATENAME(dw, dd.duedate), 3) + ')',
    SUM(dd.Count_ItemsDue)
FROM #DueDates2 dd
WHERE dd.DueDate BETWEEN CONVERT(Date, getdate()) and DATEADD(dd, 12, CONVERT(Date, getdate()))
GROUP BY 
	dd.DueDate

INSERT INTO @AOD
SELECT
    dd.AOD_DueDate,
    SUM(dd.AOD_Recv_Emails),
    SUM(dd.AOD_Recv_SaskTel)
FROM #DueDates2 dd
WHERE dd.AOD_DueDate BETWEEN CONVERT(Date, getdate()) and DATEADD(dd, 12, CONVERT(Date, getdate()))
GROUP BY 
	dd.AOD_DueDate

INSERT INTO @OD1
SELECT
    dd.OD1_DueDate,
    SUM(dd.OD_Recv_Emails),
    SUM(dd.OD_Recv_SaskTel)
FROM #DueDates2 dd
WHERE dd.OD1_DueDate BETWEEN CONVERT(Date, getdate()) and DATEADD(dd, 12, CONVERT(Date, getdate()))
GROUP BY 
	dd.OD1_DueDate


INSERT INTO @OD2
SELECT
    dd.OD2_DueDate,
    SUM(dd.OD_Recv_Emails),
    SUM(dd.OD_Recv_SaskTel)
FROM #DueDates2 dd
WHERE dd.OD2_DueDate BETWEEN CONVERT(Date, getdate()) and DATEADD(dd, 12, CONVERT(Date, getdate()))
GROUP BY 
	dd.OD2_DueDate


INSERT INTO @OD3
SELECT
    dd.OD3_DueDate,
    SUM(dd.OD_Recv_Emails),
    SUM(dd.OD_Recv_SaskTel)
FROM #DueDates2 dd
WHERE dd.OD3_DueDate BETWEEN CONVERT(Date, getdate()) and DATEADD(dd, 12, CONVERT(Date, getdate()))
GROUP BY 
	dd.OD3_DueDate

INSERT INTO @Bill
SELECT
    dd.Bill_DueDate,
    SUM(dd.Bill_Recv_Emails),
    SUM(dd.Bill_Recv_SaskTel)
FROM #DueDates2 dd
WHERE dd.Bill_DueDate BETWEEN CONVERT(Date, getdate()) and DATEADD(dd, 12, CONVERT(Date, getdate()))
GROUP BY 
	dd.Bill_DueDate

--SELECT * FROM @OD3 dd ORDER BY     dd.OD3_DueDate asc


-------------------                                         Other Notices Data                                             ----------------------


CREATE TABLE #PatExp
(
    OrganizationID int,
    ExpiryDate date,
    NoticeDate date,
    Count_PatExp int,
    Count_Patrons_Recv_Emails int,
    Count_Patrons_SaskTel int
)

INSERT INTO #PatExp
SELECT
    os.OrganizationID,
    CONVERT(date, pr.ExpirationDate),
    CASE
        WHEN os.PatExp_On = 1 THEN DATEADD(dd, -(os.PatExp_Days), CONVERT(date, pr.ExpirationDate))
        ELSE NULL
    END,
    ISNULL(COUNT(pr.PatronID), 0),
    ISNULL(COUNT(prd.PatronID), 0),
    ISNULL(COUNT(prst.PatronID), 0)
FROM #Org_Settings os
INNER JOIN Polaris.Patrons p (nolock)
    on p.OrganizationID = os.OrganizationID
INNER JOIN Polaris.PatronRegistration pr (nolock)
    on pr.PatronID = p.PatronID
LEFT JOIN Polaris.PatronRegistration prd (nolock)
    on pr.PatronID = prd.PatronID 
        and prd.EmailAddress is not null
        and prd.EmailAddress like '%@%'
        and prd.ExcludeFromPatronRecExpiration != 1
LEFT JOIN Polaris.PatronRegistration prst (nolock)
    on pr.PatronID = prst.PatronID 
        and prst.EmailAddress like '%SaskTel%'
        and prst.ExcludeFromPatronRecExpiration != 1
WHERE pr.ExpirationDate BETWEEN CONVERT(date, getdate()) AND DATEADD(dd, 50, CONVERT(Date, getdate()))
GROUP BY
    os.OrganizationID,
    CONVERT(date, pr.ExpirationDate),
    CASE
        WHEN os.PatExp_On = 1 THEN DATEADD(dd, -(os.PatExp_Days), CONVERT(date, pr.ExpirationDate))
        ELSE NULL
    END


CREATE TABLE #Queued
(
    NotificationTypeID int,
    QueueDate date,
    Notice_Count int,
    Count_EmailPat int,
    Count_Patrons_SaskTel int
)

INSERT INTO #Queued
select 
    nq.NotificationTypeID,
    CONVERT(DATE, GETDATE()),
    count(DISTINCT nq.NotificationQueueID),
    COUNT(DISTINCT nqe.PatronID),
    COUNT(Distinct prst.PatronID)
from results.polaris.NotificationQueue nq
left join polaris.polaris.NotificationTypes nt
   on nq.NotificationTypeID = nt.NotificationTypeID
left join polaris.polaris.DeliveryOptions do
   on nq.DeliveryOptionID = do.DeliveryOptionID
left join results.polaris.NotificationQueue nqe
   on nq.NotificationQueueID = nqe.NotificationQueueID
      and nqe.DeliveryOptionID = 2
LEFT JOIN Polaris.PatronRegistration prst (nolock)
    on nq.PatronID = prst.PatronID 
        and prst.DeliveryOptionID = 2 
        and prst.EmailAddress like '%SaskTel%'
--where nt.[Description] like '%hold%'
WHERE nq.NotificationTypeID in (2, 3) -- holds, cancellations
AND nq.CreationDate >= DATEADD(dd, -1, CONVERT(Date, GETDATE()))
GROUP BY
    nq.NotificationTypeID



CREATE TABLE #Hold2
(
    HoldNoticeDate date,
    HoldNotice2ndDate date,
    Count_2ndHolds int,
    Count_Patrons_Recv_Emails int,
    Count_Patrons_SaskTel int
)

INSERT INTO #Hold2
SELECT 
    CONVERT(Date, shr.HoldNotificationDate),
    CASE
        WHEN os.Hold2_On = 1 THEN DATEADD(dd, (os.Hold2_days+1), CONVERT(date, shr.HoldNotificationDate))
        ELSE NULL
    END,
    COUNT(Distinct shr.SysHoldRequestID),
    COUNT(Distinct pr.PatronID),
    COUNT(Distinct prst.PatronID)
FROM Polaris.SysHoldRequests shr (nolock)
INNER JOIN #Org_Settings os
    on shr.PickupBranchID = os.OrganizationID
LEFT JOIN Polaris.PatronRegistration pr (nolock)
    on shr.PatronID = pr.PatronID and pr.DeliveryOptionID = 2
    and pr.ExcludeFromHolds != 1
LEFT JOIN Polaris.PatronRegistration prst (nolock)
    on shr.PatronID = prst.PatronID 
        and prst.DeliveryOptionID = 2 
    and pr.ExcludeFromHolds != 1
        and prst.EmailAddress like '%SaskTel%'
WHERE shr.HoldNotification2ndDate is NULL
AND shr.HoldNotificationDate is NOT NULL
AND shr.HoldNotificationDate > DATEADD(dd, -10, CONVERT(DATE, getdate()))
AND shr.SysHoldStatusID = 6
GROUP BY
    CONVERT(Date, shr.HoldNotificationDate),
    CASE
        WHEN os.Hold2_On = 1 THEN DATEADD(dd, (os.Hold2_days+1), CONVERT(date, shr.HoldNotificationDate))
        ELSE NULL
    END


DECLARE @PExp TABLE (Exp_NoticeDate date, ECount int, STCount int)

INSERT INTO @PExp
SELECT
    pe.NoticeDate,
    SUM(pe.Count_Patrons_Recv_Emails),
    SUM(pe.Count_Patrons_SaskTel)
FROM #PatExp pe
WHERE pe.NoticeDate BETWEEN CONVERT(Date, getdate()) and DATEADD(dd, 12, CONVERT(Date, getdate()))
GROUP BY 
	pe.NoticeDate


-------------------                                         Summary                                            ----------------------



-- Days to show in the report the approx. period when notices send
-- Create median day list

DECLARE @Med_AOD Table (JKEY int, Med_Days int)
DECLARE @Med_OD1 Table (JKEY int, Med_Days int)
DECLARE @Med_OD2 Table (JKEY int, Med_Days int)
DECLARE @Med_OD3 Table (JKEY int, Med_Days int)
DECLARE @Med_Bill Table (JKEY int, Med_Days int)
DECLARE @Med_Holds2 Table (JKEY int, Med_Days int)
DECLARE @Med_PatExp Table (JKEY int, Med_Days int)


INSERT INTO @Med_AOD
SELECT
   JKEY,
   AVG(AOD_Days)
FROM
(
   SELECT
      JKEY,
      AOD_Days,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY AOD_Days ASC, OrganizationID ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY AOD_Days DESC, OrganizationID DESC) AS RowDesc
   FROM #Org_Settings os
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY JKEY
ORDER BY JKEY

INSERT INTO @Med_OD1
SELECT
   JKEY,
   AVG(OD1_Days)
FROM
(
   SELECT
      JKEY,
      OD1_Days,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY OD1_Days ASC, OrganizationID ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY OD1_Days DESC, OrganizationID DESC) AS RowDesc
   FROM #Org_Settings os
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY JKEY
ORDER BY JKEY

INSERT INTO @Med_OD2
SELECT
   JKEY,
   AVG(OD2_Days)
FROM
(
   SELECT
      JKEY,
      OD2_Days,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY OD2_Days ASC, OrganizationID ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY OD2_Days DESC, OrganizationID DESC) AS RowDesc
   FROM #Org_Settings os
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY JKEY
ORDER BY JKEY

INSERT INTO @Med_OD3
SELECT
   JKEY,
   AVG(OD3_Days)
FROM
(
   SELECT
      JKEY,
      OD3_Days,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY OD3_Days ASC, OrganizationID ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY OD3_Days DESC, OrganizationID DESC) AS RowDesc
   FROM #Org_Settings os
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY JKEY
ORDER BY JKEY

INSERT INTO @Med_Bill
SELECT
   JKEY,
   AVG(Bill_Days)
FROM
(
   SELECT
      JKEY,
      Bill_Days,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY Bill_Days ASC, OrganizationID ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY Bill_Days DESC, OrganizationID DESC) AS RowDesc
   FROM #Org_Settings os
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY JKEY
ORDER BY JKEY

INSERT INTO @Med_Holds2
SELECT
   JKEY,
   AVG(Hold2_days)
FROM
(
   SELECT
      JKEY,
      Hold2_days,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY Hold2_days ASC, OrganizationID ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY Hold2_days DESC, OrganizationID DESC) AS RowDesc
   FROM #Org_Settings os
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY JKEY
ORDER BY JKEY

INSERT INTO @Med_PatExp
SELECT
   JKEY,
   AVG(PatExp_Days)
FROM
(
   SELECT
      JKEY,
      PatExp_Days,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY PatExp_Days ASC, OrganizationID ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY JKEY
         ORDER BY PatExp_Days DESC, OrganizationID DESC) AS RowDesc
   FROM #Org_Settings os
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY JKEY
ORDER BY JKEY


CREATE TABLE #Summary
(
    DueDate_Named VARCHAR(30),
    Count_ItemsDue int,
    Est_Emails_Sending int,
    Est_SaskTel_Emails_Sending int,
    AlmostOverdue_Emails int,
    [1stOverdue_Emails] int,
    [2ndOverdue_Emails] int,
    [3rdOverdue_Emails] int,
    Bill_Emails int,
    [2ndHold_Emails] int,
    PatExp_Emails int,
    [Hold_Emails (Queued)] int,
    [HoldCancel_Emails (Queued)] int
)

-- Add median days to the results set
INSERT INTO #Summary
SELECT
    'Days',
    NULL,
    NULL,
    NULL,
    aod.Med_Days,
    od1.Med_Days,
    od2.Med_Days,
    od3.Med_Days,
    b.Med_Days,
    h.Med_Days,
    pe.Med_Days,
    NULL,
    NULL
FROM @Med_AOD aod
INNER JOIN @Med_OD1 od1
    on aod.JKEY = od1.JKEY
INNER JOIN @Med_OD2 od2
    on aod.JKEY = od2.JKEY
INNER JOIN @Med_OD3 od3
    on aod.JKEY = od3.JKEY
INNER JOIN @Med_Bill b
    on aod.JKEY = b.JKEY
INNER JOIN @Med_Holds2 h
    on aod.JKEY = h.JKEY
INNER JOIN @Med_PatExp pe
    on aod.JKEY = pe.JKEY


INSERT INTO #Summary
SELECT
    dd.DueDate_Named,
    dd.Count_ItemsDue,
    SUM(ISNULL(ao.ECount, 0) 
        + ISNULL(o.ECount, 0) 
        + ISNULL(o2.ECount, 0)
        + ISNULL(o3.ECount, 0) 
        + ISNULL(b.ECount, 0) 
        + ISNULL(h.Count_Patrons_Recv_Emails, 0) 
        + ISNULL(pe.ECount, 0) 
        + ISNULL(qh.Count_EmailPat, 0) 
        + ISNULL(qc.Count_EmailPat, 0)) Est_Emails_Sending,
    SUM(ISNULL(ao.STCount, 0) 
        + ISNULL(o.STCount, 0) 
        + ISNULL(o2.STCount, 0)
        + ISNULL(o3.STCount, 0) 
        + ISNULL(b.STCount, 0)
        + ISNULL(h.Count_Patrons_SaskTel, 0) 
        + ISNULL(pe.STCount, 0) 
        + ISNULL(qh.Count_Patrons_SaskTel, 0) 
        + ISNULL(qc.Count_Patrons_SaskTel, 0)) Est_SaskTel_Emails_Sending,
    ISNULL(ao.ECount, 0)  AlmostOverdue_Emails,
    ISNULL(o.ECount, 0)  "1stOverdue_Emails",
    ISNULL(o2.ECount, 0)  "2ndOverdue_Emails",
    ISNULL(o3.ECount, 0)  "3rdOverdue_Emails",
    ISNULL(B.ECount, 0)  "Bill_Emails",
    ISNULL(h.Count_Patrons_Recv_Emails, 0)  "2ndHold_Emails",
    ISNULL(pe.ECount, 0)  "PatExp_Emails",
    ISNULL(qh.Count_EmailPat, 0)  "Hold_Emails (Queued)",
    ISNULL(qc.Count_EmailPat, 0)  "HoldCancel_Emails (Queued)"
FROM @DD dd
LEFT JOIN @AOD ao
    on dd.DueDate = ao.AOD_DueDate
LEFT JOIN @OD1 o
    on dd.DueDate = o.OD1_DueDate
LEFT JOIN @OD2 o2
    on dd.DueDate = o2.OD2_DueDate
LEFT JOIN @OD3 o3
    on dd.DueDate = o3.OD3_DueDate
LEFT JOIN @Bill b
    on dd.DueDate = b.Bill_DueDate
LEFT JOIN @PExp pe
    on dd.DueDate = pe.Exp_NoticeDate
LEFT JOIN #Hold2 h
    on dd.DueDate = h.HoldNotice2ndDate
LEFT JOIN #Queued qh --queued hold notices
    on dd.DueDate = qh.QueueDate and qh.NotificationTypeID = 2
LEFT JOIN #Queued qc -- queued cancellation notices
    on dd.DueDate = qc.QueueDate and qc.NotificationTypeID = 3
GROUP BY 
    dd.DueDate_Named,
    dd.Count_ItemsDue,
    ISNULL(ao.ECount, 0),
    ISNULL(o.ECount, 0),
    ISNULL(o2.ECount, 0),
    ISNULL(o3.ECount, 0),
    ISNULL(B.ECount, 0),
    ISNULL(h.Count_Patrons_Recv_Emails, 0),
    ISNULL(pe.ECount, 0),
    ISNULL(qh.Count_EmailPat, 0),
    ISNULL(qc.Count_EmailPat, 0) 
ORDER BY 
    dd.DueDate_Named asc


SELECT * FROM #Summary ORDER BY DueDate_Named asc




END 

GO


