USE [Polaris]
GO
/****** Object:  StoredProcedure [Polaris].[SILS_RPT_LibrarySettings]    Script Date: 2023-10-24 10:36:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [Polaris].[SILS_RPT_LibrarySettings]


	
AS

BEGIN 

SET NOCOUNT ON

-- Maintain the list of Patron Codes being included from our Lib settings report
-- Which should basically be Adult, Child and YA, since the other cards can diverge in diff ways
DECLARE @PatCode_Incl TABLE (PatronCodeID int)
INSERT INTO @PatCode_Incl
SELECT PatronCodeID
FROM Polaris.PatronCodes pc (nolock) 
WHERE PatronCodeID in (1, 2, 3)
-- (4, 5, 8, 33, 34, 17, 18, 19, 35, 36, 37, 38)
-- prove excluded Outreach, ILL, Print-Disabled, Partial Payment, and Banned cards
GROUP BY PatronCodeID

/*
CREATE TABLE #Summary (
    LibID int,
    Loan_7Day varchar(15),
    Loan_7Day_BrCnt int,
    Loan_10Day varchar(15),
    Loan_10Day_BrCnt int,
    Loan_14Day varchar(15),
    Loan_14Day_BrCnt int,
    Loan_21Day varchar(15),
    Loan_21Day_BrCnt int,
    Adult_ItemsOut int,
    Adult_ItemsOut_Exc int,
    Adult_HoldsLmt int,
    Adult_HoldsLmt_Exc int,
    Adult_MinFine money,
    Adult_MinFine_Exc int,
    Adult_MaxFine money,
    Adult_MaxFine_Exc int,
    Holds_PU int,
    Holds_PU_Exc int,
    StoppedRTF int,
    StoppedRTF_BrCnt int,
    PrimeRTF_Days int,
    PrimeRTF_Days_BrCnt int
)
*/


CREATE TABLE #LoanPeriods (
    LibID int,
    Loan_7Day varchar(15),
    Loan_7Day_BrCnt int,
    Loan_7Day_LineCnt int,
    Loan_10Day varchar(15),
    Loan_10Day_BrCnt int,
    Loan_10Day_LineCnt int,
    Loan_14Day varchar(15),
    Loan_14Day_BrCnt int,
    Loan_14Day_LineCnt int,
    Loan_21Day varchar(15),
    Loan_21Day_BrCnt int,
    Loan_21Day_LineCnt int
)


CREATE TABLE #LoanLimits (
    LibID int,
    ItemsOut int,
    ItemsOut_BrCnt int,
    ItemsOut_LineCnt int,
    HoldsLmt int,
    HoldsLmt_BrCnt int,
    HoldsLmt_LineCnt int,
    MinFine money,
    MinFine_BrCnt int,
    MinFine_LineCnt int,
    MaxFine money,
    MaxFine_BrCnt int,
    MaxFine_LineCnt int
)


CREATE TABLE #HoldSettings (
    LibID int,
    Holds_PU int,
    Holds_PU_BrExc int,
    StoppedRTF varchar(6),
    StoppedRTF_BrExc int,
    PrimeRTF_Days int,
    PrimeRTF_Days_BrExc int
);


WITH Branches AS (
    SELECT
        ln.OrganizationID LibID,
        o.OrganizationID
    FROM Polaris.SILS_Library_Names ln (nolock)
    INNER JOIN Polaris.Organizations o (nolock)
        on ln.OrganizationID = o.ParentOrganizationID
    LEFT JOIN POlaris.SA_PAC_SuppressedBranches sup (nolock)
        on sup.SuppressedOrgID = o.OrganizationID
    WHERE o.name NOT LIKE '%PAC'
    AND o.name NOT LIKE '%Online'
    AND ((sup.SuppressedOrgID is null AND o.OrganizationID NOT IN (6,7,296,298))
        OR o.OrganizationID IN (6,7,296,298))
    GROUP BY
        ln.OrganizationID,
        o.OrganizationID
),

LP_7Day As (
    SELECT
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description "LoanPeriod",
        COUNT(DISTINCT lp.OrganizationID) "LP_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.LoanPeriods lp (nolock)
        on b.OrganizationID = lp.OrganizationID
            and lp.LoanPeriodCodeID = 2
            and lp.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    LEFT JOIN Polaris.TimeUnits tu (nolock)
        on lp.TimeUnit = tu.TimeUnitID
    GROUP BY
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description
),

LP_10Day As (
    SELECT
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description "LoanPeriod",
        COUNT(DISTINCT lp.OrganizationID) "LP_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.LoanPeriods lp (nolock)
        on b.OrganizationID = lp.OrganizationID
            and lp.LoanPeriodCodeID = 3
            and lp.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    LEFT JOIN Polaris.TimeUnits tu (nolock)
        on lp.TimeUnit = tu.TimeUnitID
    GROUP BY
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description
),

LP_14Day As (
    SELECT
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description "LoanPeriod",
        COUNT(DISTINCT lp.OrganizationID) "LP_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.LoanPeriods lp (nolock)
        on b.OrganizationID = lp.OrganizationID
            and lp.LoanPeriodCodeID = 4
            and lp.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    LEFT JOIN Polaris.TimeUnits tu (nolock)
        on lp.TimeUnit = tu.TimeUnitID
    GROUP BY
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description
),

LP_21Day As (
    SELECT
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description "LoanPeriod",
        COUNT(DISTINCT lp.OrganizationID) "LP_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.LoanPeriods lp (nolock)
        on b.OrganizationID = lp.OrganizationID
            and lp.LoanPeriodCodeID = 5
            and lp.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    LEFT JOIN Polaris.TimeUnits tu (nolock)
        on lp.TimeUnit = tu.TimeUnitID
    GROUP BY
        b.LibID,
        CONVERT(Varchar(3), lp.Units) + ' ' + tu.Description
),

LineCounts AS (
    SELECT 
    ln.OrganizationID,
    COUNT(DISTINCT lp1.LoanPeriod) Loan_7Day_LineCnt,
    COUNT(DISTINCT lp2.LoanPeriod) Loan_10Day_LineCnt,
    COUNT(DISTINCT lp3.LoanPeriod) Loan_14Day_LineCnt,
    COUNT(DISTINCT lp4.LoanPeriod) Loan_21Day_LineCnt

FROM Polaris.SILS_Library_Names ln (nolock)
LEFT JOIN LP_7Day lp1
    ON ln.OrganizationID = lp1.LibID
LEFT JOIN LP_10Day lp2
    ON ln.OrganizationID = lp2.LibID
LEFT JOIN LP_14Day lp3
    ON ln.OrganizationID = lp3.LibID
LEFT JOIN LP_21Day lp4
    ON ln.OrganizationID = lp4.LibID

GROUP BY
    ln.OrganizationID
)

INSERT INTO #LoanPeriods
/*    (LibID, Loan_7Day, Loan_7Day_BrCnt, Loan_10Day, Loan_10Day_BrCnt,
    Loan_14Day, Loan_14Day_BrCnt, Loan_21Day, Loan_21Day_BrCnt) */
SELECT 
    ln.OrganizationID,
    lp1.LoanPeriod,
    lp1.LP_BrCount,
    lc.Loan_7Day_LineCnt,
    lp2.LoanPeriod,
    lp2.LP_BrCount,
    lc.Loan_10Day_LineCnt,
    lp3.LoanPeriod,
    lp3.LP_BrCount,
    lc.Loan_14Day_LineCnt,
    lp4.LoanPeriod,
    lp4.LP_BrCount,
    lc.Loan_21Day_LineCnt

FROM Polaris.SILS_Library_Names ln (nolock)
LEFT JOIN LP_7Day lp1
    ON ln.OrganizationID = lp1.LibID
LEFT JOIN LP_10Day lp2
    ON ln.OrganizationID = lp2.LibID
LEFT JOIN LP_14Day lp3
    ON ln.OrganizationID = lp3.LibID
LEFT JOIN LP_21Day lp4
    ON ln.OrganizationID = lp4.LibID
LEFT JOIN LineCounts lc
    on ln.OrganizationID = lc.OrganizationID

GROUP BY
    ln.OrganizationID,
    lp1.LoanPeriod,
    lp1.LP_BrCount,
    lc.Loan_7Day_LineCnt,
    lp2.LoanPeriod,
    lp2.LP_BrCount,
    lc.Loan_10Day_LineCnt,
    lp3.LoanPeriod,
    lp3.LP_BrCount,
    lc.Loan_14Day_LineCnt,
    lp4.LoanPeriod,
    lp4.LP_BrCount,
    lc.Loan_21Day_LineCnt;


WITH Branches AS (
    SELECT
        ln.OrganizationID LibID,
        o.OrganizationID
    FROM Polaris.SILS_Library_Names ln (nolock)
    INNER JOIN Polaris.Organizations o (nolock)
        on ln.OrganizationID = o.ParentOrganizationID
    LEFT JOIN POlaris.SA_PAC_SuppressedBranches sup (nolock)
        on sup.SuppressedOrgID = o.OrganizationID
    WHERE o.name NOT LIKE '%PAC'
    AND o.name NOT LIKE '%Online'
    AND ((sup.SuppressedOrgID is null AND o.OrganizationID NOT IN (6,7,296,298))
        OR o.OrganizationID IN (6,7,296,298))
    GROUP BY
        ln.OrganizationID,
        o.OrganizationID
),

LL_Items As (
    SELECT
        b.LibID,
        ll.TotalItems,
        COUNT(DISTINCT ll.OrganizationID) "LL_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.PatronLoanLimits ll (nolock)
        on b.OrganizationID = ll.OrganizationID
            and ll.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    GROUP BY
        b.LibID,
        ll.TotalItems
),

LL_Holds As (
    SELECT
        b.LibID,
        ll.TotalHolds,
        COUNT(DISTINCT ll.OrganizationID) "LL_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.PatronLoanLimits ll (nolock)
        on b.OrganizationID = ll.OrganizationID
            and ll.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    GROUP BY
        b.LibID,
        ll.TotalHolds
),

LL_MinFine As (
    SELECT
        b.LibID,
        ll.MinFine,
        COUNT(DISTINCT ll.OrganizationID) "LL_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.PatronLoanLimits ll (nolock)
        on b.OrganizationID = ll.OrganizationID
            and ll.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    GROUP BY
        b.LibID,
        ll.MinFine
),

LL_MaxFine As (
    SELECT
        b.LibID,
        ll.MaxFine,
        COUNT(DISTINCT ll.OrganizationID) "LL_BrCount"
    FROM Branches b
    LEFT JOIN Polaris.PatronLoanLimits ll (nolock)
        on b.OrganizationID = ll.OrganizationID
            and ll.PatronCodeID IN (SELECT * FROM @PatCode_Incl)
    GROUP BY
        b.LibID,
        ll.MaxFine
),

LineCount AS (
    SELECT
        ln.OrganizationID,
        COUNT(DISTINCT li.TotalItems) TotalItems_LineCnt,
        COUNT(DISTINCT lh.TotalHolds) TotalHolds_LineCnt,
        COUNT(DISTINCT min.MinFine) MinFine_LineCnt,
        COUNT(DISTINCT max.MaxFine) MaxFine_LineCnt
    FROM Polaris.SILS_Library_Names ln (nolock)
    LEFT JOIN LL_Items li
        on ln.OrganizationID = li.LibID
    LEFT JOIN LL_Holds lh
        on ln.OrganizationID = lh.LibID
    LEFT JOIN LL_MinFine min
        on ln.OrganizationID = min.LibID
    LEFT JOIN LL_MaxFine max
        on ln.OrganizationID = max.LibID
    GROUP BY
        ln.OrganizationID
)

INSERT INTO #LoanLimits
SELECT
    ln.OrganizationID,
    li.TotalItems,
    li.LL_BrCount,
    lc.TotalItems_LineCnt,
    lh.TotalHolds,
    lh.LL_BrCount,
    lc.TotalHolds_LineCnt,
    min.MinFine,
    min.LL_BrCount,
    lc.MinFine_LineCnt,
    max.MaxFine,
    max.LL_BrCount,
    lc.MaxFine_LineCnt
FROM Polaris.SILS_Library_Names ln (nolock)
LEFT JOIN LL_Items li
    on ln.OrganizationID = li.LibID
LEFT JOIN LL_Holds lh
    on ln.OrganizationID = lh.LibID
LEFT JOIN LL_MinFine min
    on ln.OrganizationID = min.LibID
LEFT JOIN LL_MaxFine max
    on ln.OrganizationID = max.LibID
LEFT JOIN LineCount lc
    on ln.OrganizationID = lc.OrganizationID
GROUP BY
    ln.OrganizationID,
    li.TotalItems,
    li.LL_BrCount,
    lc.TotalItems_LineCnt,
    lh.TotalHolds,
    lh.LL_BrCount,
    lc.TotalHolds_LineCnt,
    min.MinFine,
    min.LL_BrCount,
    lc.MinFine_LineCnt,
    max.MaxFine,
    max.LL_BrCount,
    lc.MaxFine_LineCnt


INSERT INTO #HoldSettings
SELECT
    ln.OrganizationID,
    oPP.Value Holds_PU,
    COUNT(DISTINCT oPb.OrganizationID) Holds_PU_Exc,
    ISNULL(oPR.Value, oPSR.Value) StoppedRTF,
    COUNT(DISTINCT oPRb.OrganizationID) StoppedRTF_BrCnt,
    ISNULL(oPDRb.Value, oPDSR.Value) PrimeRTF_Days,
    COUNT(DISTINCT oPDRb.OrganizationID) PrimeRTF_Days_BrCnt
FROM Polaris.SILS_Library_Names ln (nolock)
INNER JOIN Polaris.Organizations o (nolock)
    on ln.OrganizationID = o.ParentOrganizationID
LEFT JOIN Polaris.Organizations os (nolock)
    on ln.OrganizationID = os.OrganizationID

LEFT JOIN POlaris.SA_PAC_SuppressedBranches sup (nolock)
    on sup.SuppressedOrgID = o.OrganizationID
LEFT JOIN polaris.OrganizationsPPPP oPP (nolock)
    on ln.OrganizationID = oPP.OrganizationID
        and opp.AttrID = 793 -- Days until hold pickup
LEFT JOIN polaris.OrganizationsPPPP oPb (nolock)
    on o.OrganizationID = oPb.OrganizationID
        and oPb.AttrID = 793
        and oPb.Value != oPP.Value

LEFT JOIN polaris.OrganizationsPPPP oPR (nolock)
    on ln.OrganizationID = oPR.OrganizationID
        and oPR.AttrID = 3101 -- stopped RTF
LEFT JOIN polaris.OrganizationsPPPP oPRb (nolock)
    on o.OrganizationID = oPRb.OrganizationID
        and oPRb.AttrID = 3101
        and oPRb.Value != oPR.Value
        and oPRb.OrganizationID NOT IN (6,7,296,298)
LEFT JOIN polaris.OrganizationsPPPP oPSR (nolock)
    on os.ParentOrganizationID = oPSR.OrganizationID
        and oPSR.AttrID = 3101 -- System stopped RTF

LEFT JOIN polaris.OrganizationsPPPP oPDR (nolock)
    on ln.OrganizationID = oPDR.OrganizationID
        and oPDR.AttrID = 792 -- Primary RTF days
LEFT JOIN polaris.OrganizationsPPPP oPDSR (nolock)
    on os.ParentOrganizationID = oPDSR.OrganizationID
        and oPDSR.AttrID = 792
LEFT JOIN polaris.OrganizationsPPPP oPDRb (nolock)
    on o.OrganizationID = oPDRb.OrganizationID
        and oPDRb.AttrID = 792 -- System Primary RTF days
        and (oPDRb.Value != oPDR.Value
            OR oPDRb.Value != oPDSR.Value)
WHERE o.name NOT LIKE '%PAC'
AND o.name NOT LIKE '%Online'
AND ((sup.SuppressedOrgID is null AND o.OrganizationID NOT IN (6,7,296,298))
    OR o.OrganizationID IN (6,7,296,298))
GROUP BY
    ln.OrganizationID,
    oPP.Value,
    ISNULL(oPR.Value, oPSR.Value),
    ISNULL(oPDRb.Value, oPDSR.Value);


WITH LineCounts AS (
    SELECT 
        LibID,
        COUNT(DISTINCT Holds_PU) Holds_PU_LineCnt,
        COUNT(DISTINCT StoppedRTF) StoppedRTF_LineCnt,
        COUNT(DISTINCT PrimeRTF_Days) PrimeRTF_Days_LineCnt
    FROM #HoldSettings hs
    GROUP BY
        LibID
)


SELECT 
    ln.Name Library,
    lp.Loan_7Day,
    Loan_7Day_BrCnt,
    Loan_7Day_LineCnt,
    Loan_10Day,
    Loan_10Day_BrCnt,
    Loan_10Day_LineCnt,
    Loan_14Day,
    Loan_14Day_BrCnt,
    Loan_14Day_LineCnt,
    Loan_21Day,
    Loan_21Day_BrCnt,
    Loan_21Day_LineCnt,
    ItemsOut,
    ItemsOut_BrCnt,
    ItemsOut_LineCnt,
    HoldsLmt,
    HoldsLmt_BrCnt,
    HoldsLmt_LineCnt,
    MinFine,
    MinFine_BrCnt,
    MinFine_LineCnt,
    MaxFine,
    MaxFine_BrCnt,
    MaxFine_LineCnt,
    Holds_PU,
    Holds_PU_BrExc,
    Holds_PU_LineCnt,
    StoppedRTF,
    StoppedRTF_BrExc,
    StoppedRTF_LineCnt,
    PrimeRTF_Days,
    PrimeRTF_Days_BrExc,
    PrimeRTF_Days_LineCnt 

FROM Polaris.SILS_Library_Names ln (nolock)
LEFT JOIN #LoanPeriods lp
    on ln.OrganizationID = lp.LibID
LEFT JOIN #LoanLimits ll
    on ln.OrganizationID = ll.LibID
LEFT JOIN #HoldSettings hs
    on ln.OrganizationID = hs.LibID
LEFT JOIN LineCounts lc
    on ln.OrganizationID = lc.LibID
GROUP BY
    ln.Name,
    lp.Loan_7Day,
    Loan_7Day_BrCnt,
    Loan_7Day_LineCnt,
    Loan_10Day,
    Loan_10Day_BrCnt,
    Loan_10Day_LineCnt,
    Loan_14Day,
    Loan_14Day_BrCnt,
    Loan_14Day_LineCnt,
    Loan_21Day,
    Loan_21Day_BrCnt,
    Loan_21Day_LineCnt,
    ItemsOut,
    ItemsOut_BrCnt,
    ItemsOut_LineCnt,
    HoldsLmt,
    HoldsLmt_BrCnt,
    HoldsLmt_LineCnt,
    MinFine,
    MinFine_BrCnt,
    MinFine_LineCnt,
    MaxFine,
    MaxFine_BrCnt,
    MaxFine_LineCnt,
    Holds_PU,
    Holds_PU_BrExc,
    Holds_PU_LineCnt,
    StoppedRTF,
    StoppedRTF_BrExc,
    StoppedRTF_LineCnt,
    PrimeRTF_Days,
    PrimeRTF_Days_BrExc,
    PrimeRTF_Days_LineCnt 
ORDER BY
    ln.Name







END








