USE [Polaris]
GO

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

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [Polaris].[SILS_Rpt_Workstation_Inactivity] 
	@Agency varchar(MAX),
	@BranchList varchar(MAX),
    @LastLoginDate datetime

AS

BEGIN

SET NOCOUNT ON

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



-- Remove recently accessed workstations, then add 
create table #RecentLogons (WorkstationID int)

insert into #RecentLogons
select distinct th.WorkstationID
from PolarisTransactions.polaris.TransactionHeaders th (nolock)
where th.TransactionTypeID = 7200
and TranClientDate >= @LastLoginDate
GROUP BY
    th.WorkstationID


create table #Logons (WorkstationID int)

insert into #Logons
select distinct w.WorkstationID
from polaris.Workstations w (nolock)
inner join Polaris.Organizations o (nolock)
    on w.OrganizationID = o.OrganizationID
inner join #Branches b
    on b.OrganizationID = w.OrganizationID
WHERE w.WorkstationID not in 
    (select WorkstationID from #RecentLogons)
AND w.Enabled = 1
GROUP BY
    w.WorkstationID


create table #LastLogin (WorkstationID int, PolarisUserID int, OrganizationID int, TranClientDate datetime)

insert into #LastLogin
select 
    th.WorkstationID,
    th.PolarisUserID,
    th.OrganizationID,
    th.TranClientDate
from PolarisTransactions.polaris.TransactionHeaders th (nolock)
INNER JOIN #Logons l (nolock)
    on th.WorkstationID = l.WorkstationID
where th.TransactionTypeID = 7200
-- and th.TranClientDate <= @LastLoginDate
GROUP BY
    th.WorkstationID,
    th.PolarisUserID,
    th.OrganizationID,
    th.TranClientDate



select
    o.Name as Branch,
	w.WorkstationID,
    w.DisplayName Workstation_DisplayName,
    w.ComputerName,
    ll.LastLogOn as LastLogon_Date,
    pu.Name as LastLogon_User, 
    w.CreationDate
    --w.Enabled,
    --w.[Status],
    --w.StatusDate

from polaris.Workstations w (nolock)
INNER JOIN #Logons l (nolock)
    on w.WorkstationID = l.WorkstationID
inner join (
    SELECT
        WorkstationID,
        max(TranClientDate) LastLogOn
    FROM #LastLogin ll
    GROUP BY
        WorkstationID
        ) ll
            on w.WorkstationID = ll.WorkstationID
LEFT JOIN #LastLogin lp
    on w.WorkstationID = lp.WorkstationID
        and ll.LastLogOn = lp.TranClientDate

inner join Polaris.Organizations o (nolock)
    on w.OrganizationID = o.OrganizationID
inner join #Branches b
    on b.OrganizationID = w.OrganizationID

INNER JOIN Polaris.PolarisUsers pu (nolock)
    on lp.PolarisUserID = pu.PolarisUserID
--left join polaris.UsersPPPP pppp (nolock)  -- Added to remove suspended users (JT 2018-06-04)
--on pppp.PolarisUserID = pu.PolarisUserID AND pppp.AttrID = 659 -- Added (JT 2018-06-04)

--and (pppp.Value != 'Suspended' or pppp.Value is null) -- Added (JT 2018-06-04)
--AND NOT (pu.Enabled = 0 AND pppp.[Value] = 'Closed')

group by 
    o.Name,
	w.WorkstationID,
    w.DisplayName,
    w.ComputerName,
    pu.Name, 
    ll.LastLogOn,
    w.CreationDate
    --w.Enabled,
    --w.[Status],
    --w.StatusDate
ORDER BY
    o.Name,
    ll.LastLogOn desc,
    w.DisplayName,
    pu.Name






end


GO


