search with Fullname

USE [EduconnectSyncRTO]
GO
/****** Object:  StoredProcedure [dbo].[sp_getEnquiryList]    Script Date: 12/20/2021 3:10:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Kshetra Shrestha  --Update By: Yuvaraj Upadhyaya (dec 15 2021)
-- Create date: List Of Enquiry
-- Description: Get List of inquiries depending upon parameter
-- =============================================
ALTER PROCEDURE [dbo].[sp_getEnquiryList]
@PageNum INT =1,
@PageSize INT= 125241522,
@InstituteId INT =0 ,
@SearchText VARCHAR(100)='',
@STATUS INT=0,
@activeStatus INT = NULL,
@EnquiryAssignedTo NVARCHAR(MAX)='',
@Sorting VARCHAR(500)='e.EnquiryDate',
@SortOrder VARCHAR(500)='asc',
@FromDate VARCHAR(100)='',
@Todate VARCHAR(100)='',
@ReportType VARCHAR(100)=''
AS
BEGIN
 SET NOCOUNT ON;
DECLARE @SqlCount INT
DECLARE @SQLQUERYCOUNT nvarchar(MAX)
DECLARE @SQLQuery VARCHAR(MAX)
DECLARE @SQLTotalRow TABLE (TotalRow INT)

SET @SQLQUERYCOUNT ='SELECT
Count(*) as TotalRows
FROM Enquiry e
INNER JOIN InstituteCourse ic on e.InstituteCourseId= ic.InstituteCourseId
INNER JOIN Person p on e.PersonId = p.CredentialId
INNER JOIN Users u on e.PersonId= u.Id
LEFT OUTER JOIN Agent a on e.AgentId= a.AgentId
LEFT OUTER JOIN Users us ON e.AssignedTo =us.Id
INNER JOIN Institute i ON ic.InstituteId = i.InstituteId
LEFT OUTER JOIN CoursePackage cp ON e.CoursePackageId = cp.Id Where 1=1'
 IF(@FromDate!='' AND @Todate!='')
 SET @SQLQUERYCOUNT =@SQLQUERYCOUNT + ' AND (CAST(e.EnquiryDate AS DATE) >= '''+@FromDate+''' And CAST(e.EnquiryDate AS DATE) <='''+@Todate+''')'

IF(@activeStatus IS NOT NULL AND @activeStatus!=-1)
SET @SQLQUERYCOUNT =@SQLQUERYCOUNT+ ' AND e.IsActive='+CONVERT(VARCHAR, @activeStatus)+''
IF (@InstituteId > 0)
SET @SQLQUERYCOUNT =@SQLQUERYCOUNT+ ' AND i.InstituteId='+CONVERT(VARCHAR, @InstituteId)+''
 IF (@STATUS > 0)
SET @SQLQUERYCOUNT =@SQLQUERYCOUNT+ ' AND e.EnquiryStatus='+CONVERT(VARCHAR, @STATUS)+''

IF(@SearchText!='')
SET @SQLQUERYCOUNT =@SQLQUERYCOUNT+ ' AND ((u.FirstName' + '+'' ''+' + 'u.LastName LIKE ''%'+CONVERT(VARCHAR,@SearchText)+'%'') OR ic.SpecificCourseName like ''%'+CONVERT(VARCHAR,@SearchText)+'%'' OR a.AgencyLegalName like ''%'+CONVERT(VARCHAR,@SearchText)+'%'' OR i.InstituteTradingName like ''%'+
CONVERT(VARCHAR,@SearchText)+'%''
OR e.FromEmail like''%'+CONVERT(VARCHAR,@SearchText)+'%'')'
IF(@EnquiryAssignedTo!='')
SET @SQLQUERYCOUNT =@SQLQUERYCOUNT+ ' AND e.AssignedTo='''+ @EnquiryAssignedTo+''''
IF(@ReportType IS NOT NULL AND @ReportType!='' AND @ReportType='InstituteEnquiryList')
  SET @SQLQUERYCOUNT =@SQLQUERYCOUNT+ ' AND e.IsForwaredToProvider=1'
INSERT @SQLTotalRow
EXEC sp_executesql @SQLQUERYCOUNT , N'@SqlCount int out', @SqlCount OUT
SET @SqlCount=(SELECT * FROM @SQLTotalRow)
SET @SQLQuery ='SELECT
e.RequestId,
e.FromEmail,
p.PersonId,
CASE When (u.FirstName IS NULL) THEN '''++''' ELSE u.FirstName END +'''+' '+'''+ CASE WHEN (u.LastName IS NULL) THEN '''++''' ELSE u.LastName END AS UserName,
e.EnquiryDate,
e.EnquiryStatus,
e.IsForwaredToProvider,
ic.SpecificCourseName,
ic.InstituteId,
i.InstituteTradingName,
CASE WHEN (a.AgentId IS NOT NULL) THEN a.AgencyLegalName ELSE '''++''' END AS AgentName,
u.Type ,
e.CoursePackageId,
CASE WHEN (e.CoursePackageId IS NOT NULL) THEN cp.CoursePackageName ELSE '''++''' END AS CoursePackageName,
e.AssignedTo,
CASE WHEN (us.Id IS NULL) THEN '''++''' ELSE CASE WHEN (us.FirstName IS NULL) THEN '''++''' ELSE us.FirstName END + '''+' '+''' + CASE WHEN (us.LastName IS NULL) THEN '''++''' ELSE us.LastName END END AS AssignedToName,
e.IsActive,'+CONVERT(VARCHAR,@SqlCount)+' as TotalRecords
FROM Enquiry e
INNER JOIN InstituteCourse ic on e.InstituteCourseId= ic.InstituteCourseId
INNER JOIN Person p on e.PersonId = p.CredentialId
INNER JOIN Users u on e.PersonId= u.Id
LEFT OUTER JOIN Agent a on e.AgentId= a.AgentId
LEFT OUTER JOIN Users us ON e.AssignedTo =us.Id
INNER JOIN Institute i ON ic.InstituteId = i.InstituteId
LEFT OUTER JOIN CoursePackage cp ON e.CoursePackageId = cp.Id Where 1=1'
IF(@FromDate!='' AND @Todate!='')
 SET @SQLQuery =@SQLQuery+ ' AND (CAST(e.EnquiryDate AS DATE) >= '''+@FromDate+''' And CAST(e.EnquiryDate AS DATE) <='''+@Todate+''')'


IF(@activeStatus IS NOT NULL AND @activeStatus!=-1)
SET @SQLQuery =@SQLQuery+ ' AND e.IsActive='+ CONVERT(VARCHAR, @activeStatus)+''
IF (@InstituteId > 0)
SET @SQLQuery =@SQLQuery+ ' AND i.InstituteId='+CONVERT(VARCHAR, @InstituteId)+''
 IF (@STATUS > 0)
SET @SQLQuery =@SQLQuery+ ' AND e.EnquiryStatus='+CONVERT(VARCHAR, @STATUS)+''

IF(@SearchText!='')
SET @SQLQuery =@SQLQuery+ ' AND ((u.FirstName' + '+'' ''+' + 'u.LastName LIKE ''%'+CONVERT(VARCHAR,@SearchText)+'%'') OR ic.SpecificCourseName like ''%'+CONVERT(VARCHAR,@SearchText)+'%'' OR a.AgencyLegalName like ''%'+CONVERT(VARCHAR,@SearchText)+'%'' OR i.InstituteTradingName like ''%'+CONVERT(VARCHAR,@SearchText)+'%'' OR e.FromEmail like''%'+CONVERT(VARCHAR,@SearchText)+'%'')'

IF(@EnquiryAssignedTo!='')
SET @SQLQuery =@SQLQuery+ ' AND e.AssignedTo='''+ @EnquiryAssignedTo+''''
IF(@ReportType IS NOT NULL AND @ReportType!='' AND @ReportType='InstituteEnquiryList')
  SET @SQLQuery =@SQLQuery+ ' AND e.IsForwaredToProvider=1'


SET @SQLQuery =@SQLQuery +' Order By '+ CONVERT(VARCHAR,@Sorting) +' '+ @SortOrder +' OFFSET '+CONVERT(VARCHAR, (@PageNum-1)*@PageSize)+' ROWS  FETCH NEXT  '+CONVERT(VARCHAR,@PageSize)+'  ROWS ONLY OPTION (RECOMPILE)'
EXEC (@SQLQuery)
--select  (@SQLQuery)
END