IF OBJECT_ID(N'KAAS_CP_ReportGeneration',N'P')IS NOT NULL
    DROP PROCEDURE [dbo].[KAAS_CP_ReportGeneration]
GO
CREATE PROCEDURE
       [dbo].[KAAS_CP_ReportGeneration]      
		(@FromDate VARCHAR(50),
		 @ToDate   VARCHAR(50),
		 @LoginId  BIGINT)       
      
AS        
/*******************************************************************************************************
* This procedure is used to Generate Case Associate code.				  							   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_CP_ReportGeneration]									           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2022-04-28	Vinodhkumar.M		Created for client portal report                                   *
* 2022-07-20	Vinodhkumar.M		Modified - added Client Name	                                   *
* 2024-07-11	Aakif				Modified - Included publish filters for CaseContacts               *
 *******************************************************************************************************/
BEGIN	

	DECLARE @FormatedToDate DATETIME = DATEADD(MINUTE,59,DATEADD(hh,23,CAST(@ToDate AS DATETIME)));
	
	SELECT DISTINCT
				Row_Number() 
				OVER ( ORDER BY  
				MAT.[Started] DESC)     					AS  [UniqueId],
				MAT.USER1									AS	[RateNo],
				MAT.ORIGINALDEBT							AS	[OriginalDebt],
				CASEASSOCIATE.NAME							AS	[Name],
				CASEASSOCIATE.ADDRESS						AS	[Address],
				CASEASSOCIATE.LEGALNAME						AS	[LegalName],
				CASEASSOCIATE.LEGALADDRESS					AS  [LegalAddress],
				MAT.COMMENT									AS  [Comment],
				MAT.[Started]						 	    AS	[Started],
				ISNULL(CL.CLNAME,'')					    AS  [ClientName]
		FROM  [DBO].KAAS_CP_TFGetMatterCodeForClient(@LoginId) LOGINACCESS
  INNER JOIN [DBO].[MATTERS] MAT 
		  ON LOGINACCESS.MatterCode = MAT.CODE
  INNER JOIN [DBO].[Client] CL
		  ON MAT.ClientCode = CL.CLCODE 
  INNER JOIN [DBO].[CASECONTACTS] CSC
		  ON MAT.CODE = (SELECT TOP 1 CSC.CASECODE )
  INNER JOIN [DBO].[CASEASSOCIATESNAMES] CASEASSOCIATE
		  ON  CASEASSOCIATE.CODE = CSC.NAMECODE
	   WHERE ISNULL([CSC].PUBLISH, '') = 'Y'
		 AND MAT.[Started] 
	 BETWEEN @FromDate 
		 AND @FormatedToDate

END   
GO