USE [Polaris]
GO

/****** Object:  StoredProcedure [Polaris].[SILS_RPT_PolarisCodes]    Script Date: 2023-11-23 2:27:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [Polaris].[SILS_RPT_PolarisCodes]


	@Library varchar(MAX),
	@PatCodes varchar(MAX),
    @Code int
    -- 1 = Branch Codes
    -- 2 = Collection Codes
    -- 3 = Fine Codes
    -- 4 = Item Statuses
    -- 5 = Loan Period Codes
    -- 6 = MARC TOMs
    -- 7 = Mat Types
    -- 8 = Mat Type Loan Periods
    -- 9 = Patron Codes
	
AS

BEGIN 

SET NOCOUNT ON



--create list of agencies for selection

 CREATE TABLE #Libraries (OrganizationID INT)    
  
 IF LEN(@Library) = 1 AND @Library= '0' -- ALL SELECTED  
 BEGIN  
  INSERT INTO #Libraries   
  SELECT OrganizationID FROM Polaris.Polaris.Organizations (NOLOCK) WHERE OrganizationCodeID = 2 
 END  
 ELSE  
 BEGIN  
  EXEC ('INSERT INTO #Libraries SELECT OrganizationID FROM Polaris.Polaris.Organizations (NOLOCK) WHERE OrganizationID in (' + @Library + ')')    
END

 CREATE TABLE #PatCodes (PatronCodeID INT)    
  
 IF LEN(@PatCodes) = 1 AND @PatCodes= '0' -- ALL SELECTED  
 BEGIN  
  INSERT INTO #PatCodes   
  SELECT PatronCodeID FROM Polaris.Polaris.PatronCodes (NOLOCK)
 END  
 ELSE  
 BEGIN  
  EXEC ('INSERT INTO #PatCodes SELECT PatronCodeID FROM Polaris.Polaris.PatronCodes (NOLOCK) WHERE PatronCodeID in (' + @PatCodes + ')')    
END

CREATE TABLE #All
(
-- Branch Codes
    BranchID int,
    BrCode varchar(15),
    BrName VARCHAR(Max),
    SuppressedPU varchar(5),
    SuppressedPAC varchar(5),
    HoldStop varchar(5),
    Closed varchar(15),
    SIPPort int,
    Email VARCHAR(Max),

-- Collections
    CollectionID int,
	Unused varchar(25),
    CName VARCHAR(Max),
    Abbreviation varchar(15),
	ItemCount int,

-- Fine Codes
    FineCodeID int,
    FineCode VARCHAR(Max),
    FinePatCode VARCHAR(Max),
    Amount money,
    MaxFine money,
    GraceUnits int,

-- Item Statuses
    ItemStatusID int,
    IstDescription VARCHAR(80),
    IstName VARCHAR(25),
    IstHolds bit,

-- Loan Period Codes
    LoanPeriodCodeID int,
    LoanPeriodCode varchar(80),
    LoanPatCode VARCHAR(Max),
    TimeUnit varchar(32),
    Units int,

-- MARC TOMs
    TOMid int,
    TOMDescription VARCHAR(Max),
    Precendence int,
    SearchCode varchar(30),

-- Mat Types
    MatTypeID int,
    MatType varchar(MAX),
    MatPatCode VARCHAR(Max),
    ReplCost Money,
    LoanLimit int,
    HoldLimit int,

-- Patron Codes
    PatronCodeID int,
    PatronCode varchar(80)
)


-- 1 = Branch Codes
IF @Code = 1

    BEGIN

        INSERT INTO #All (BranchID, BrCode, BrName, SuppressedPU, SuppressedPAC, HoldStop, Closed, SIPPort, Email)
        select distinct 
            o.OrganizationID,
            Abbreviation,
            Name,
            CASE 
                WHEN ((pu.ExcludedBranchID is null) or (pu.ExcludedBranchID is not null and Exclude = 0)) then ''
                ELSE 'Yes' 
            END,
            CASE 
                WHEN sup.SuppressedOrgID is null then ''
                ELSE 'Yes' 
            END ,
            CASE 
                WHEN halt.Value is null then ''
                ELSE 'Yes' 
            END,
            CASE 
                WHEN h.Value = 'MONCLOSED,TUECLOSED,WEDCLOSED,THUCLOSED,FRICLOSED,SATCLOSED,SUNCLOSED' then 'Closed'
                ELSE ''
            END,
            s.TCPIPPortNumber,
            email.Value
        from polaris.Organizations o (nolock)
		inner join #Libraries l
			on o.ParentOrganizationID = l.OrganizationID
        inner join polaris.OrganizationsPPPP email (nolock)
            on email.OrganizationID = o.OrganizationID and email.AttrID = 1
        left join Polaris.polaris.OrganizationsPPPP halt (nolock)
            on halt.OrganizationID = o.OrganizationID 
                and halt.AttrID = 3101 and halt.Value = 'Yes'
        left join polaris.HoldsPickupBranchesExclude pu (nolock)
            on pu.ExcludedBranchID = o.OrganizationID
        left join polaris.SA_PAC_SuppressedBranches sup (nolock)
            on sup.SuppressedOrgID = o.OrganizationID
        left join polaris.polaris.OrganizationsPPPP h (nolock)
            on h.OrganizationID = o.OrganizationID and h.AttrID = 96
        left join Polaris.polaris.SIPServicePorts s (nolock)
            on s.OrganizationID = o.OrganizationID
        GROUP BY
            o.OrganizationID,
            Abbreviation,
            Name,
            CASE 
                WHEN ((pu.ExcludedBranchID is null) or (pu.ExcludedBranchID is not null and Exclude = 0)) then ''
                ELSE 'Yes' 
            END,
            CASE 
                WHEN sup.SuppressedOrgID is null then ''
                ELSE 'Yes' 
            END ,
            CASE 
                WHEN halt.Value is null then ''
                ELSE 'Yes' 
            END,
            CASE 
                WHEN h.Value = 'MONCLOSED,TUECLOSED,WEDCLOSED,THUCLOSED,FRICLOSED,SATCLOSED,SUNCLOSED' then 'Closed'
                ELSE ''
            END,
            s.TCPIPPortNumber,
            email.Value

    END

-- 2 = Collection Codes
IF @Code = 2

    BEGIN

        INSERT INTO #All (CollectionID, Unused, CName, Abbreviation, ItemCount)
        SELECT
            c.CollectionID,
			CASE WHEN b.CollectionID IS NULL THEN 'Unused' ELSE null END Unused,
            c.name,
            c.Abbreviation,
			CASE
				WHEN b.CollectionID IS NULL THEN NULL
				ELSE ISNULL(ic.ItemCount,0)
			END
        FROM Polaris.Polaris.collections c (nolock)
		LEFT JOIN (
			SELECT
				oc.CollectionID
			FROM Polaris.OrganizationsCollections oc (nolock)
			INNER JOIN Polaris.Organizations o (nolock)
				on oc.OrganizationID = o.OrganizationID
			INNER JOIN #Libraries l
				on o.ParentOrganizationID = l.OrganizationID
				) b
					on c.CollectionID = b.CollectionID
		LEFT JOIN (
			SELECT
				cir.AssignedCollectionID,
				COUNT(DISTINCT cir.ItemRecordID) ItemCount
			FROM Polaris.CircItemRecords cir (nolock)
			INNER JOIN Polaris.Organizations o (nolock)
				on o.organizationID = cir.AssignedBranchID
			INNER JOIN #Libraries l
				on o.ParentOrganizationID = l.OrganizationID
			GROUP BY
				cir.AssignedCollectionID
				) ic
					on c.CollectionID = ic.AssignedCollectionID
		GROUP BY
            c.CollectionID,
			CASE WHEN b.CollectionID IS NULL THEN 'Unused' ELSE null END,
            c.name,
            c.Abbreviation,
			CASE
				WHEN b.CollectionID IS NULL THEN NULL
				ELSE ISNULL(ic.ItemCount,0)
			END
        ORDER BY c.Name

    END


-- 3 = Fine Codes
IF @Code = 3

    BEGIN

        INSERT INTO #All 
            (FineCodeID,
            FineCode,
            FinePatCode,
            Amount,
            MaxFine,
            GraceUnits)
        SELECT
            fc.FineCodeID,
            fc.[Description],
            pc.[Description],
            f.Amount,
            f.MaximumFine,
            f.GraceUnits
        FROM POlaris.FineCodes fc (nolock)
        INNER JOIN Polaris.Fines f (nolock)
            on fc.FineCodeID = f.FineCodeID
        INNER JOIN Polaris.PatronCodes pc (nolock)
            on f.PatronCodeID = pc.PatronCodeID
        INNER JOIN Polaris.Organizations o (nolock)
            on f.OrganizationID = o.OrganizationID
        INNER JOIN #Libraries l
            on o.ParentOrganizationID = l.OrganizationID
		INNER JOIN #PatCodes pp
			on pc.PatronCodeID = pp.PatronCodeID
		WHERE o.name NOT LIKE '%- PAC%'
		AND o.name NOT LIKE '%- Online%'
        GROUP BY
            fc.FineCodeID,
            fc.[Description],
            pc.[Description],
            f.Amount,
            f.MaximumFine,
            f.GraceUnits
        ORDER BY
            fc.FineCodeID,
            pc.[Description]

    END


-- 4 = Item Statuses
IF @Code = 4

    BEGIN

        INSERT INTO #All 
            (ItemStatusID,
            IstDescription,
            IstName,
            IstHolds)
        SELECT
            ist.ItemStatusID,
            ist.[Description],
            ist.name,
            CASE
                WHEN ist.ItemStatusID in (7,8,9,10,11,20,22) THEN 0
                ELSE 1
            END Holds
        FROM Polaris.ItemStatuses ist (nolock)
        GROUP BY 
            ist.ItemStatusID,
            ist.[Description],
            ist.name,
            CASE
                WHEN ist.ItemStatusID in (7,8,9,10,11,20,22) THEN 0
                ELSE 1
            END

    END


-- 5 = Loan Period Codes
IF @Code = 5

    BEGIN

        INSERT INTO #All 
            (LoanPeriodCodeID,
            LoanPeriodCode,
            LoanPatCode,
            TimeUnit,
            Units)

        SELECT
            lpc.LoanPeriodCodeID,
            lpc.[Description],
            pc.[Description],
            t.[Description],
            lp.Units
        FROM Polaris.LoanPeriodCodes lpc (nolock)
        INNER JOIN POlaris.LoanPeriods lp (nolock)
            on lpc.LoanPeriodCodeID = lp.LoanPeriodCodeID
        INNER JOIN Polaris.TimeUnits t (nolock)
            on lp.TimeUnit = t.TimeUnitID
        INNER JOIN Polaris.PatronCodes pc (nolock)
            on lp.PatronCodeID = pc.PatronCodeID
        INNER JOIN Polaris.Organizations o (nolock)
            on lp.OrganizationID = o.OrganizationID
        INNER JOIN #Libraries l
            on o.ParentOrganizationID = l.OrganizationID
		INNER JOIN #PatCodes pp
			on pc.PatronCodeID = pp.PatronCodeID
		WHERE o.name NOT LIKE '%- PAC%'
		AND o.name NOT LIKE '%- Online%'
        GROUP BY
            lpc.LoanPeriodCodeID,
            lpc.[Description],
            pc.[Description],
            t.[Description],
            lp.Units
        ORDER BY
            lpc.LoanPeriodCodeID,
            pc.[Description]

    END


-- 6 = MARC TOMs
IF @Code = 6

    BEGIN

        INSERT INTO #All 
            (TOMid,
            TOMDescription,
            Precendence,
            SearchCode)

        SELECT
            t.MARCTypeOfMaterialID,
            t.[Description],
            t.Precedence,
            t.SearchCode
        FROM Polaris.MARCTypeOfMaterial t (nolock)
        GROUP BY
            t.MARCTypeOfMaterialID,
            t.[Description],
            t.Precedence,
            t.SearchCode
        ORDER BY
            t.MARCTypeOfMaterialID

    END


-- 7 = Mat Types
IF @Code = 7

    BEGIN

        INSERT INTO #All (MatTypeID, MatType)

        SELECT
            mt.MaterialTypeID,
            mt.[Description]
        FROM Polaris.MaterialTypes mt (nolock)

    END


-- 8 = Mat Type Loan Periods
IF @Code = 8

    BEGIN

        INSERT INTO #All
            (MatTypeID,
            MatType,
            MatPatCode,
            ReplCost,
            LoanLimit,
            HoldLimit)

        SELECT
            mt.MaterialTypeID,
            mt.[Description],
            pc.[Description],
            rc.ReplacementCost,
            mll.MaxItems,
            mll.MaxRequestItems
        FROM Polaris.MaterialTypes mt (nolock)
        INNER JOIN Polaris.MaterialLoanLimits mll (nolock)
            on mt.MaterialTypeID = mll.MaterialTypeID
        INNER JOIN Polaris.PatronCodes pc (nolock)
            on mll.PatronCodeID = pc.PatronCodeID
        INNER JOIN Polaris.Organizations o (nolock)
            on mll.OrganizationID = o.OrganizationID
        INNER JOIN #Libraries l
            on o.ParentOrganizationID = l.OrganizationID
        LEFT JOIN Polaris.SA_LostItemReplacementCosts rc (nolock)
            on mt.MaterialTypeID = rc.MaterialTypeID
		INNER JOIN #PatCodes pp
			on pc.PatronCodeID = pp.PatronCodeID
		WHERE o.name NOT LIKE '%- PAC%'
		AND o.name NOT LIKE '%- Online%'
        GROUP BY
            mt.MaterialTypeID,
            mt.[Description],
            pc.[Description],
            rc.ReplacementCost,
            mll.MaxItems,
            mll.MaxRequestItems
        ORDER BY
            mt.[Description] ASC,
            pc.[Description] ASC

    END

-- 9 = Patron Codes
IF @Code = 9

    BEGIN

        INSERT INTO #All (PatronCodeID, PatronCode)
        SELECT
            pc.PatronCodeID,
            pc.[Description]
        FROM Polaris.Polaris.PatronCodes pc (nolock)

    END


SELECT *
FROM #All
/*
ORDER BY 
    CASE
        WHEN @Code = 1 THEN BrName
        WHEN @Code = 2 THEN CName
        WHEN @Code = 3 THEN FineCodeID
        WHEN @Code = 3 THEN FinePatCode
        WHEN @Code = 4 THEN IstDescription
        WHEN @Code = 5 THEN LoanPeriodCode
        WHEN @Code = 5 THEN LoanPatCode
        WHEN @Code = 6 THEN TOMDescription
        WHEN @Code = 7 THEN MatType
        WHEN @Code = 8 THEN MatType
        WHEN @Code = 8 THEN MatPatCode
        WHEN @Code = 9 THEN PatronCode
    END
*/



END








GO


