USE [Polaris]
GO

/****** Object:  StoredProcedure [Polaris].[SILS_Rpt_FloatingCollectionLimits]    Script Date: 2023-11-23 2:41:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [Polaris].[SILS_Rpt_FloatingCollectionLimits]         

	@Agency varchar(MAX), -- generate a selectable list of agencies in the dropdown.
	@BranchList varchar(MAX), -- generate a selectable list of agencies in the dropdown.
	@CollectionList varchar(MAX) -- generate a selectable list of collections in the dropdown.

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #Agencies (OrganizationID INT)      

 BEGIN  
  EXEC ('INSERT INTO #Agencies SELECT OrganizationID FROM Polaris.Polaris.Organizations (NOLOCK) WHERE OrganizationID in (' + @Agency + ')')    
 END 

CREATE TABLE #Branches (OrganizationID INT)    

 IF LEN(@BranchList) = 1 AND @BranchList= '0' -- ALL SELECTED   
 BEGIN  
  INSERT INTO #Branches   
  SELECT OrganizationID FROM Polaris.Polaris.Organizations (NOLOCK) WHERE OrganizationCodeID = 3 AND ParentOrganizationID = @Agency
 END  
 ELSE  
 BEGIN  
  EXEC ('INSERT INTO #Branches SELECT OrganizationID FROM Polaris.Polaris.Organizations (NOLOCK) WHERE OrganizationID in (' + @BranchList + ') AND ParentOrganizationID = ' + @Agency )    
 END 
	

	CREATE TABLE #NullColl (CollectionID INT, Name Varchar(Max))
		INSERT INTO #NullColl SELECT CollectionID, Name FROM Polaris.Polaris.Collections (NOLOCK)

	CREATE TABLE #Collections (CollectionID INT, Name Varchar(Max))

	IF LEN(@CollectionList) = 1 AND @CollectionList= '0' -- ALL SELECTED   
	 BEGIN  
	  INSERT INTO #Collections   
	  SELECT CollectionID, Name FROM #NullColl --Polaris.Polaris.Collections (NOLOCK)
	 END  
	 ELSE  
	 BEGIN  
	  EXEC ('INSERT INTO #Collections SELECT CollectionID, Name FROM #NullColl WHERE CollectionID in (' + @CollectionList + ')' )
	 END


CREATE TABLE #AvailItems (OrgID int, CollectionID int, AvailItems int)

INSERT INTO #AvailItems
select distinct 
	fcl.ItemCheckInBranchID,
	fcl.CollectionID,
    COUNT(DISTINCT cir.ItemRecordID) AvailItems
from polaris.SA_FloatingMatrixCollectionLimits fcl (nolock)
INNER JOIN #Collections c
	on fcl.CollectionID = c.CollectionID
inner join #Branches b
on b.OrganizationID = fcl.ItemCheckInBranchID
INNER JOIN Polaris.CircItemRecords cir (nolock)
    on b.OrganizationID = cir.AssignedBranchID
        and cir.AssignedCollectionID = fcl.CollectionID
        and cir.ItemStatusID = 1
GROUP BY
	fcl.ItemCheckInBranchID,
	fcl.CollectionID

CREATE TABLE #TransitItems (OrgID int, CollectionID int, TransitItems int)

INSERT INTO #TransitItems
select distinct 
	fcl.ItemCheckInBranchID,
	fcl.CollectionID,
    COUNT(DISTINCT cir.ItemRecordID) AvailItems
from polaris.SA_FloatingMatrixCollectionLimits fcl (nolock)
inner join #Branches b
on b.OrganizationID = fcl.ItemCheckInBranchID
INNER JOIN Polaris.CircItemRecords cir (nolock)
    on b.OrganizationID = cir.InTransitRecvdBranchID
        and cir.AssignedCollectionID = fcl.CollectionID
        and cir.ItemStatusID = 6
GROUP BY
	fcl.ItemCheckInBranchID,
	fcl.CollectionID


select distinct 
	c.Name as Collection,
	o.Name as ItemCheckinBranch,
	fcl.Limit,
	fcl.ItemCount,
    ISNULL(ai.AvailItems, 0) AvailItems,
    ISNULL(ti.TransitItems, 0) TransitItems,
	CONVERT(decimal(9,2),itemcount) / CONVERT(decimal(9,2), limit) AS FullLimit

from polaris.SA_FloatingMatrixCollectionLimits fcl (nolock)
INNER JOIN #Collections c0
	on fcl.CollectionID = c0.CollectionID
inner join polaris.SA_FloatingMatrix fm (nolock)
on fm.ItemCheckInBranchID = fcl.ItemCheckInBranchID 

inner join polaris.OrganizationsFloatingCollections ofc (nolock)
on ofc.HomeBranchID = fm.ItemHomeBranchID and ofc.CollectionID = fcl.CollectionID

inner join polaris.Collections c (nolock)
on c.CollectionID = fcl.CollectionID

inner join polaris.organizations o
on fcl.ItemCheckInBranchID = o.OrganizationID

inner join #Branches b
on b.OrganizationID = fcl.ItemCheckInBranchID

LEFT JOIN #AvailItems ai
    on fcl.ItemCheckInBranchID = ai.OrgID
        and fcl.CollectionID = ai.CollectionID

LEFT JOIN #TransitItems ti
    on fcl.ItemCheckInBranchID = ti.OrgID
        and fcl.CollectionID = ti.CollectionID

where CanFloat = 1
--and fcl.ItemCount > 0
and fcl.Limit > 0
and ofc.HomeBranchID is not null
GROUP BY
	o.Name,
	c.Name,
	fcl.Limit,
	fcl.ItemCount,
    ai.AvailItems,
    ti.TransitItems,
	CONVERT(decimal(9,2),itemcount) / CONVERT(decimal(9,2), limit)
ORDER BY
	c.Name,
	o.Name

END

GO


