USE [JourneyDB]
GO
/****** Object:  StoredProcedure [dbo].[spReporting]    Script Date: 23/10/2023 06:11:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spReporting]    
(  
 @CustomerName NVARCHAR(100) ='',   
 @Email NVARCHAR(200)='',   
 @EnquiryRef NVARCHAR(50)='',   
 @TDRef1 NVARCHAR(50)='',   
 @TDRef2 NVARCHAR(50)='',   
 @PhoneNumber NVARCHAR(50)='',   
 @DepartingFromDate NVARCHAR(10)='',  
 @DepartingToDate NVARCHAR(10)='',  
 @EnquiryTypeId TINYINT=0,    
 @IsSpokenToClient BIT = null,   
 @LeadTypeId TINYINT=0,    
 @DepartureAirportId SMALLINT=0,   
 @FlightOptionsId TINYINT=0,   
 @HolidayTypeId TINYINT=0,  
 @CruiseDestinationId SMALLINT=0,  
 @VehicleId SMALLINT=0,  
 @PreferredHotelId SMALLINT=0,  
 @Duration TINYINT=0,   
 @NumberOfAdults TINYINT=0,   
 @MediaTypeId SMALLINT=0,   
 @FindUsId SMALLINT=13,   
 @FindUs NVARCHAR(50)='',  
 @EnquiryStatusId TINYINT=0,   
 @AllocateUserMasterId INT=0,   
 @StatusChangeFromDate NVARCHAR(10)='',  
 @StatusChangeToDate NVARCHAR(10)='',  
 @DestinationId SMALLINT=0,   
 @CaseFromDate NVARCHAR(10)='',  
 @CaseToDate NVARCHAR(10)='',  
 @AllocatedFromDate NVARCHAR(10)='',  
 @AllocatedToDate NVARCHAR(10)='',  
 @IsSplit BIT = 0,   
 @BrandId TINYINT=0,   
 @IsChangeDate BIT=0,  
 @limitfrom int = 0,  
 @limitto int = 15,  
 @moduleId int =0,  
 @ReturnFromDate NVARCHAR(10)='',  
 @ReturnToDate NVARCHAR(10)='',  
 @IsDeleted BIT = 0,  
 @IsArchived BIT = 0,  
 @NumberOfChilds TINYINT=0,   
 @NumberOfInfants TINYINT=0,  
 @DepartingDateFlag NVARCHAR(2)='E',  
 @BookedHolidayTypeId TINYINT=0 ,
 @Reference NVARCHAR(500)='',
 @BookedFromDate NVARCHAR(10)='',    
 @BookedToDate NVARCHAR(10)='' 
)  
AS    
BEGIN      
    DECLARE @EnquiryRef1 VARCHAR(50)  
    SET @EnquiryRef1= Case when  CHARINDEX('wb',@EnquiryRef) > 0  then @EnquiryRef when CHARINDEX('oh',@EnquiryRef) > 0 then @EnquiryRef WHEN CHARINDEX('ot',@EnquiryRef) > 0  then @EnquiryRef else '' end  
 SET @FindUs = CASE WHEN @FindUs = '0' THEN '' ELSE @FindUs END  
  
 DECLARE @Case_FromDate Datetime = CASE WHEN @CaseFromDate = '' then null else CONVERT(DATETIME,@CaseFromDate,103) end  
 DECLARE @Case_ToDate Datetime = CASE WHEN @CaseToDate = '' then null else  DATEADD(s,-1, DATEADD(d,1,CONVERT(DATETIME,@CaseToDate,103)))  end  
 DECLARE @Allocated_FromDate Datetime = CASE WHEN @AllocatedFromDate = '' then null else CONVERT(DATETIME,@AllocatedFromDate,103) end  
 DECLARE @Allocated_ToDate Datetime = CASE WHEN @AllocatedToDate = '' then null else DATEADD(s,-1, DATEADD(d,1,CONVERT(DATETIME,@AllocatedToDate,103)))  end  
 DECLARE @Departing_FromDate Datetime = CASE WHEN @DepartingFromDate = '' then null else CONVERT(DATETIME,@DepartingFromDate,103) end  
 DECLARE @Departing_ToDate Datetime = CASE WHEN @DepartingToDate = '' then null else DATEADD(s,-1, DATEADD(d,1,CONVERT(DATETIME,@DepartingToDate,103)))  end  
 DECLARE @Status_ChangeFromDate Datetime = CASE WHEN @StatusChangeFromDate = '' then null else CONVERT(DATETIME,@StatusChangeFromDate,103) end  
 DECLARE @Status_ChangeToDate Datetime = CASE WHEN @StatusChangeToDate = '' then null else  DATEADD(s,-1, DATEADD(d,1,CONVERT(DATETIME,@StatusChangeToDate,103)))  end  
 DECLARE @Return_FromDate Datetime = CASE WHEN @ReturnFromDate = '' then null else  CONVERT(DATETIME,@ReturnFromDate,103) end  
 DECLARE @Return_ToDate Datetime = CASE WHEN @ReturnToDate = '' then null else DATEADD(s,-1, DATEADD(d,1,CONVERT(DATETIME,@ReturnToDate,103))) end  
 DECLARE @Booked_FromDate DATETIME = CASE WHEN @BookedFromDate = '' THEN null ELSE CONVERT(DATETIME,@BookedFromDate,103) END      
 DECLARE @Booked_ToDate DATETIME = CASE WHEN @BookedToDate = '' THEN null ELSE CONVERT(DATETIME,(@BookedToDate + ' 23:59:59.59'),103) END
 IF(@IsChangeDate='1')    
 BEGIN     
  SELECT COUNT(EnquiryId) OVER () as Total_Rows, * FROM (  
   SELECT DISTINCT  E.EnquiryId, E.EnquiryDate, E.EnquiryAllocateDate,E.EnquiryRef, CE.Email, CP.PhoneNumber AS Telephone1,  
   CC.CustomerTitle, E.CustomerFirstName AS FirstName , E.CustomerLastName AS LastName,EB.BrandId,     
   E.DepartureDate , ISNULL(E.TDRef1,'') AS TDRef1, ISNULL(E.TDRef2,'') AS TDRef2,     
   ISNULL(DA.FlightAirportName,'--') AS DepartureAirportName,    
   (ISNULL(D.Duration,0)) AS Duration, H.HolidayTypeName AS HolidayType,     
   ISNULL(A.PropertyAccomTypeName,'--') AS AccommodationTypeName,   
   CASE WHEN (@FindUsId = 0 and @FindUs = '') THEN COALESCE(F.HowFindUsName,E.OtherMedia,'Other') ELSE   
   CASE WHEN @FindUs != '' then ISNULL(E.OtherMedia,'Other') Else COALESCE(F.HowFindUsName,E.OtherMedia,'Other') End  End AS Media,  
   E.NumberOfAdults, E.NumberOfChildren, E.NumberOfInfants ,     
   (SELECT (U.FirstName + ' ' + U.LastName) FROM Employee AS U WHERE E.AllocateEmployeeId = U.EmployeeId) AS AgentName,    
   ISNULL(E.LeadTypeId,'0') AS LeadType,   
   ISNULL(E.IsSpokenToClient,'0') AS IsSpokenToClient,     
   ESL.EnquiryStatusId AS EnquiryStatusId,   
   CASE WHEN E.EnquiryStatusId = 4 AND E.AllocateEmployeeId = 0 THEN 'QC In Progress' ELSE ES.StatusName END AS QuoteStatus, ISNULL(E.IsDuplicate,'0') AS IsDuplicate,    
   CASE WHEN E.EnquiryTypeId=1 THEN 'WEB' ELSE 'CALL' END AS EnqType,   
   CONVERT(VARCHAR(10), ESL.ModifyDate,105) + stuff( right( convert( VARCHAR(26),ESL.ModifyDate, 109 ), 15 ), 7, 9, ' ') AS ChangeDate,    
   (SELECT (U.FirstName + ' ' + U.LastName) FROM Employee AS U WHERE ESL.EmployeeId = U.EmployeeId) AS ChangedBy,  
   (SELECT (U.FirstName + ' ' + U.LastName) FROM Employee AS U WHERE ISNULL(EP.SplitWithAgentId,0) = U.EmployeeId and ISNULL(EP.IsSplit,0) = 1) AS SplitAgent,  
   ISNULL(EP.IsSplit,0) as IsSplit, E.RepeatType, ISNULL(EB.BrandId,1) AS Brand, ISNULL(E.IsDelete,0) AS IsDeleted,  
   ISNULL(EP.IsFSCheck,0) AS IsFSCheck, ISNULL(EP.IsTDCheck,0) AS IsTDCheck ,  
   EP.BookedOn AS  BookedDate ,
    STUFF((SELECT ', ' + D.DestinationName FROM EnquiryDestination AS ED WITH(NOLOCK) Inner Join Destination D WITH(NOLOCK) ON ED.DestinationId = D.DestinationId        
        WHERE ED.EnquiryId = E.EnquiryId      
      FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ')   AS DestinationNameList ,E.IsAccessibilityAssistance
   FROM  Enquiry AS E WITH(NOLOCK)  LEFT JOIN FlightAirport AS DA on E.DepartureAirportId=DA.FlightAirportId    
   LEFT JOIN PropertyAccomType AS A WITH(NOLOCK)  on E.AccommodationTypeId=A.PropertyAccomTypeId    
   LEFT JOIN MediaType as M WITH(NOLOCK)  on E.MediaTypeId= M.MediaTypeId    
   LEFT JOIN HowFindUs as F WITH(NOLOCK)  on E.HowFindUsId= F.HowFindUsId    
   LEFT JOIN EnquiryStatusLog as ESL WITH(NOLOCK)  on E.EnquiryId= ESL.EnquiryId   
   INNER JOIN HolidayType AS H WITH(NOLOCK)  on E.ActualHolidayTypeId = H.HolidayTypeId    
   INNER JOIN EnquiryStatus AS ES WITH(NOLOCK)  ON ESL.EnquiryStatusId = ES.EnquiryStatusId   
   LEFT JOIN Brand as EB WITH(NOLOCK)  on E.BrandId= EB.BrandId    
   LEFT JOIN CustomerContact as CC WITH(NOLOCK)  on E.CustomerContactId= CC.ContactId    
   LEFT JOIN ContactEmail AS CE WITH(NOLOCK)  On CE.ContactEmailId = E.ContactEmailId AND (CE.IsPrimary = 1)  
   LEFT JOIN ContactPhoneNumber AS CP WITH(NOLOCK)  On CP.ContactPhoneNumberId = E.MobileNumberId AND (CP.PhoneType = 2)AND (CP.IsPrimary = 1)      
   LEFT JOIN ContactPhoneNumber AS CPL WITH(NOLOCK)  On CPL.ContactPhoneNumberId = E.LandlineNumberId  
   Left Join  (select EnquiryId, Sum(Duration) as Duration  from EnquiryDestination WITH(NOLOCK)  group by EnquiryId) D on D.EnquiryId = E.EnquiryId  
   LEFT JOIN EnquiryProgress AS EP WITH(NOLOCK)  On EP.EnquiryId = E.EnquiryId  
   LEFT JOIN ContactNamesMigrationLog clog WITH(NOLOCK)  ON  cc.ContactId = E.CustomerContactId AND clog.EnquiryId = E.EnquiryId   
   LEFT JOIN  FSCase FC on FC.EnquiryId=E.EnquiryId   
   Left Join BookingEmailLog bel WITH(NOLOCK) ON E.EnquiryId =  bel.EnquiryId  
   WHERE (@CustomerName = '' OR LOWER(E.CustomerFirstName + ' ' + E.CustomerLastName) LIKE '%' + @CustomerName + '%'  
   OR clog.FirstName  + clog.LastName LIKE '%' + @CustomerName + '%' )  
   AND (@Email ='' OR CE.Email LIKE '%' + @Email+ '%' OR bel.Email LIKE  '%' + @Email+ '%')      
      AND (@moduleId = 0 OR (@moduleId=4 and e.brandId <>3) or (@moduleId=3 and( e.brandId =1 or e.BrandId=2 or Fc.EnquiryId is not null)))  
   AND ( @EnquiryRef1 <> '' OR (@EnquiryRef = ''  OR E.EnquiryRef = 'OH'+@EnquiryRef OR E.EnquiryRef = 'WB'+@EnquiryRef OR E.EnquiryRef = 'OT'+@EnquiryRef))  
      AND (@EnquiryRef1 = '' OR E.EnquiryRef =@EnquiryRef1)  
   AND (@TDRef1 = '' OR E.TDRef1 = @TDRef1) AND (@TDRef2 = '' OR E.TDRef2 = @TDRef2)      
   AND (@PhoneNumber = '' OR replace(CP.PhoneNumber,' ','') LIKE '%'+@PhoneNumber+'%' OR replace(CPL.PhoneNumber,' ','') LIKE '%'+@PhoneNumber+'%' )  
   AND (@EnquiryTypeId = 0 OR E.EnquiryTypeId = @EnquiryTypeId)  
   AND (@IsSpokenToClient IS NULL OR E.IsSpokenToClient = @IsSpokenToClient)   
   AND (@LeadTypeId = 0 OR E.LeadTypeId = @LeadTypeId)  
   AND (@DepartureAirportId = 0 OR E.DepartureAirportId = @DepartureAirportId)  
   AND (@FlightOptionsId = 0 OR E.FlightOptionId = @FlightOptionsId)     AND (@HolidayTypeId = 0 OR E.HolidayTypeId = @HolidayTypeId)  
   AND (@BookedHolidayTypeId = 0 OR E.ActualHolidayTypeId = @BookedHolidayTypeId)  
   AND (@CruiseDestinationId  = 0 OR E.CruiseDestination  = @CruiseDestinationId)  
   AND (@VehicleId = 0 OR E.VehicleType = @VehicleId)  
   AND (@PreferredHotelId = 0 OR E.IsPreferred = @PreferredHotelId)  
   AND (@Duration = 0 OR D.Duration = @Duration)  
   AND (@NumberOfAdults = 0 OR E.NumberOfAdults = @NumberOfAdults)  
   AND (@MediaTypeId = 0 OR E.MediaTypeId = @MediaTypeId)  
   AND IsNull(E.OtherMedia,'00') = case when (@FindUs !='' and  @FindUs !='0') then  @FindUs Else Isnull(E.OtherMedia,'00') End  
   AND IsNull(E.HowFindUsId,'00') = case when (@FindUs ='' Or  @FindUs ='0') and @FindUsId != 0 then  @FindUsId Else IsNull(E.HowFindUsId,'00') End 
   AND (@Reference='' OR E.OtherMedia=@Reference)
   AND (@EnquiryStatusId = 0 OR ESL.EnquiryStatusId = @EnquiryStatusId)  
   AND (@AllocateUserMasterId = 0 OR E.AllocateEmployeeId = @AllocateUserMasterId)  
   AND (@Status_ChangeFromDate is null OR ESL.ModifyDate >= @Status_ChangeFromDate)  
   AND (@Status_ChangeToDate is null OR ESL.ModifyDate <= @Status_ChangeToDate)     
   AND (@Case_FromDate is null OR E.EnquiryDate >= @Case_FromDate)  
   AND (@Case_ToDate is null OR E.EnquiryDate <= @Case_ToDate)  
   AND (@Allocated_FromDate is null OR E.EnquiryAllocateDate >= @Allocated_FromDate)  
   AND (@Allocated_ToDate is null OR E.EnquiryAllocateDate <= @Allocated_ToDate)  
   AND (@IsSplit =0 OR EP.IsSplit = @IsSplit)   
   AND (@BrandId = 0 OR EB.BrandId = @BrandId)    
   AND (@DestinationId = 0 OR E.EnquiryId in (select D.EnquiryId from EnquiryDestination D WITH(NOLOCK)  where D.DestinationId = @DestinationId and D.EnquiryId = E.EnquiryId))  
   AND (@Return_FromDate IS NULL OR Convert(date,E.ReturnDate,105) BETWEEN Convert(date,@Return_FromDate,105) AND Convert(date,@Return_ToDate,105))
   AND (@Booked_FromDate IS NULL OR Convert(date,EP.BookedOn,105) BETWEEN Convert(date,@Booked_FromDate,105) AND Convert(date,@Booked_ToDate,105)) 
   AND ((E.IsDelete  = 0 OR E.IsDelete = @IsDeleted ) AND (ISNULL(IsArchive,0)=0 OR ISNULL(IsArchive,0) = @IsArchived)) 
   AND (@NumberOfChilds = 0 OR E.NumberOfChildren = @NumberOfChilds)  
   AND (@NumberOfInfants = 0 OR E.NumberOfInfants = @NumberOfInfants)  
   AND ( @Departing_FromDate IS NULL OR  
     (  
      (@Departing_FromDate IS NOT NULL AND @DepartingDateFlag = 'E' AND Convert(date,E.DepartureDate,105) BETWEEN Convert(date,@Departing_FromDate,105) AND Convert(date,@Return_ToDate,105))  
      OR   
      (@Departing_FromDate IS NOT NULL AND @DepartingDateFlag = 'B' AND Convert(date,E.ActualDepartureDate,105) BETWEEN Convert(date,@Departing_FromDate,105) AND Convert(date,@Return_ToDate,105))  
     )  
    )  
    AND ISNULL(CC.IsContactDelete,0)=0  
  ) tbl  
  ORDER BY ChangeDate DESC 
  OPTION(RECOMPILE)
 END    
 ELSE    
 BEGIN   
  SELECT COUNT(EnquiryId) OVER () as Total_Rows, * from (  
   Select distinct E.EnquiryId, E.EnquiryDate, E.EnquiryAllocateDate,E.EnquiryRef, CE.Email, CP.PhoneNumber AS Telephone1,    
   CC.CustomerTitle, E.CustomerFirstName AS FirstName , E.CustomerLastName AS LastName, EB.BrandId,     
   E.DepartureDate , ISNULL(E.TDRef1,'') AS TDRef1,ISNULL(E.TDRef2,'') AS TDRef2,    
   ISNULL(DA.FlightAirportName,'--') AS DepartureAirportName,    
   (ISNULL(D.Duration,0)) AS Duration, H.HolidayTypeName AS HolidayType,     
   ISNULL(A.PropertyAccomTypeName,'--') as AccommodationTypeName,   
   Case When (@FindUsId = 0 and @FindUs = '') then COALESCE(F.HowFindUsName,E.OtherMedia,'Other') Else   
   Case When @FindUs != '' then ISNULL(E.OtherMedia,'Other') Else COALESCE(F.HowFindUsName,E.OtherMedia,'Other') End  End as Media,  
   E.NumberOfAdults, E.NumberOfChildren, E.NumberOfInfants ,     
   (SELECT (U.FirstName + ' ' + U.LastName) FROM Employee AS U WHERE E.AllocateEmployeeId = U.EmployeeId) AS AgentName,    
   ISNULL(E.LeadTypeId,'0') AS LeadType,   
   ISNULL(E.IsSpokenToClient,'0') AS IsSpokenToClient,     
   E.EnquiryStatusId AS EnquiryStatusId, CASE WHEN E.EnquiryStatusId = 4 AND E.AllocateEmployeeId = 0 THEN 'QC In Progress' ELSE ES.StatusName END AS QuoteStatus, ISNULL(E.IsDuplicate,'0') AS IsDuplicate,    
   CASE WHEN E.EnquiryTypeId=1 THEN 'WEB' ELSE 'CALL' END AS EnqType,  
   ' ' AS ChangeDate,     
   ' ' AS ChangedBy,  
   (SELECT (U.FirstName + ' ' + U.LastName) FROM Employee AS U WHERE ISNULL(EP.SplitWithAgentId,0) = U.EmployeeId and ISNULL(EP.IsSplit,0) = 1 ) AS SplitAgent,  
   ISNULL(EP.IsSplit,0)  as IsSplit ,E.RepeatType, ISNULL(EB.BrandId,1) AS Brand, ISNULL(E.IsDelete,0) AS IsDeleted,  
   ISNULL(EP.IsFSCheck,0) AS IsFSCheck, ISNULL(EP.IsTDCheck,0) AS IsTDCheck,
   EP.BookedOn AS  BookedDate ,
    STUFF((SELECT ', ' + D.DestinationName FROM EnquiryDestination AS ED WITH(NOLOCK) Inner Join Destination D WITH(NOLOCK) ON ED.DestinationId = D.DestinationId        
        WHERE ED.EnquiryId = E.EnquiryId   
   FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ')   AS DestinationNameList, E.IsAccessibilityAssistance
  
   FROM  Enquiry AS E WITH(NOLOCK) LEFT JOIN FlightAirport AS DA WITH(NOLOCK) on E.DepartureAirportId=DA.FlightAirportId and DA.IsDeparture = 1  
   LEFT JOIN PropertyAccomType AS A WITH(NOLOCK) on E.AccommodationTypeId=A.PropertyAccomTypeId   
   LEFT JOIN MediaType as M WITH(NOLOCK) on E.MediaTypeId= M.MediaTypeId    
   LEFT JOIN HowFindUs as F on E.HowFindUsId= F.HowFindUsId    
   INNER JOIN HolidayType AS H WITH(NOLOCK) on E.ActualHolidayTypeId = H.HolidayTypeId   
   INNER JOIN EnquiryStatus AS  ES WITH(NOLOCK)  ON E.EnquiryStatusId = ES.EnquiryStatusId   
   LEFT JOIN Brand as EB on E.BrandId= EB.BrandId    
   LEFT JOIN CustomerContact as CC WITH(NOLOCK) on E.CustomerContactId= CC.ContactId    
  
   LEFT JOIN ContactEmail AS CE WITH(NOLOCK) On CE.ContactEmailId = E.ContactEmailId AND (CE.IsPrimary = 1)  
   LEFT JOIN ContactPhoneNumber AS CP WITH(NOLOCK) On CP.ContactPhoneNumberId = E.MobileNumberId AND (CP.PhoneType = 2)AND (CP.IsPrimary = 1)    
   LEFT JOIN ContactPhoneNumber AS CPL WITH(NOLOCK)  On CPL.ContactPhoneNumberId = E.LandlineNumberId  
   Left Join  (select EnquiryId, Sum(Duration) as Duration  from EnquiryDestination WITH(NOLOCK) group by EnquiryId) D on D.EnquiryId = E.EnquiryId  
   LEFT JOIN EnquiryProgress AS EP WITH(NOLOCK) On EP.EnquiryId = E.EnquiryId  
   LEFT JOIN ContactNamesMigrationLog clog WITH(NOLOCK) ON cc.ContactId = E.CustomerContactId AND clog.EnquiryId = E.EnquiryId   
   LEFT JOIN  FSCase FC on FC.EnquiryId=E.EnquiryId   
   Left Join BookingEmailLog bel WITH(NOLOCK) ON E.EnquiryId =  bel.EnquiryId
   WHERE ((E.IsDelete  = 0 OR E.IsDelete = @IsDeleted ) and (ISNULL(IsArchive,0)=0 OR ISNULL(IsArchive,0) = @IsArchived))    
            AND (@CustomerName = '' OR LOWER(E.CustomerFirstName + ' ' + E.CustomerLastName) LIKE '%' + @CustomerName + '%'  
   OR clog.FirstName  + clog.LastName LIKE '%' + @CustomerName + '%' )  
   AND (@Email ='' OR CE.Email LIKE '%' + @Email+ '%' OR bel.Email LIKE  '%' + @Email+ '%')  
   AND (@moduleId = 0 OR (@moduleId=4 and e.brandId <>3) or (@moduleId=3 and( e.brandId =1 or e.BrandId=2 or Fc.EnquiryId is not null)))  
      AND ( @EnquiryRef1 <> '' OR (@EnquiryRef = ''  OR E.EnquiryRef = 'OH'+@EnquiryRef OR E.EnquiryRef = 'WB'+@EnquiryRef OR E.EnquiryRef = 'OT'+@EnquiryRef))  
      AND (@EnquiryRef1 = '' OR E.EnquiryRef =@EnquiryRef1)  
   AND (@TDRef1 = '' OR E.TDRef1 = @TDRef1) AND (@TDRef2 = '' OR E.TDRef2 = @TDRef2)  
   AND (@PhoneNumber = '' OR replace(CP.PhoneNumber,' ','') LIKE '%'+@PhoneNumber+'%' OR  replace(CPL.PhoneNumber,' ','') LIKE '%'+@PhoneNumber+'%' )     
   AND (@EnquiryTypeId = 0 OR E.EnquiryTypeId = @EnquiryTypeId)  
   AND (@IsSpokenToClient IS NULL OR E.IsSpokenToClient = @IsSpokenToClient)   
   AND (@LeadTypeId = 0 OR E.LeadTypeId = @LeadTypeId)  
   AND (@DepartureAirportId = 0 OR E.DepartureAirportId = @DepartureAirportId)  
   AND (@FlightOptionsId = 0 OR E.FlightOptionId = @FlightOptionsId)  
   AND (@HolidayTypeId = 0 OR E.HolidayTypeId = @HolidayTypeId)  -- ActualHolidayTypeId  
   AND (@BookedHolidayTypeId = 0 OR E.ActualHolidayTypeId = @BookedHolidayTypeId)  
   AND (@CruiseDestinationId  = 0 OR E.CruiseDestination  = @CruiseDestinationId)  
   AND (@VehicleId = 0 OR E.VehicleType = @VehicleId)  
   AND (@PreferredHotelId = 0 OR E.IsPreferred = @PreferredHotelId)  
   AND (@Duration = 0 OR D.Duration = @Duration)  
   AND (@NumberOfAdults = 0 OR E.NumberOfAdults = @NumberOfAdults)  
   AND (@MediaTypeId = 0 OR E.MediaTypeId = @MediaTypeId)  
   AND IsNull(E.OtherMedia,'00') = case when (@FindUs !='' and  @FindUs !='0') then  @FindUs Else Isnull(E.OtherMedia,'00') End  
   AND IsNull(E.HowFindUsId,'00') = case when (@FindUs ='' Or  @FindUs ='0') and @FindUsId != 0 then  @FindUsId Else IsNull(E.HowFindUsId,'00') End
   AND (@Reference='' OR E.OtherMedia=@Reference)
   AND (@EnquiryStatusId = 0 OR E.EnquiryStatusId = @EnquiryStatusId)  
   AND (@AllocateUserMasterId = 0 OR E.AllocateEmployeeId = @AllocateUserMasterId)  
   AND (@Case_FromDate is null OR E.EnquiryDate >= @Case_FromDate)  
   AND (@Case_ToDate is null OR E.EnquiryDate <= @Case_ToDate)  
   AND (@Allocated_FromDate is null OR E.EnquiryAllocateDate >= @Allocated_FromDate)  
   AND (@Allocated_ToDate is null OR E.EnquiryAllocateDate <= @Allocated_ToDate)  
   --AND (@IsSplit =0 OR EP.IsSplit = @IsSplit)   
   AND (@BrandId = 0 OR EB.BrandId = @BrandId)  
   AND (@DestinationId = 0 OR E.EnquiryId in (select D.EnquiryId from EnquiryDestination D WITH(NOLOCK) where D.DestinationId = @DestinationId and D.EnquiryId = E.EnquiryId))  
   AND (@Return_FromDate IS NULL OR Convert(date,E.ReturnDate,105) BETWEEN Convert(date,@Return_FromDate,105) AND Convert(date,@Return_ToDate,105))
   AND (@Booked_FromDate IS NULL OR Convert(date,EP.BookedOn,105) BETWEEN Convert(date,@Booked_FromDate,105) AND Convert(date,@Booked_ToDate,105))
   AND (@NumberOfChilds = 0 OR E.NumberOfChildren = @NumberOfChilds)  
   AND (@NumberOfInfants = 0 OR E.NumberOfInfants = @NumberOfInfants)  
   AND ( @Departing_FromDate IS NULL OR  
     (  
      (@Departing_FromDate IS NOT NULL AND @DepartingDateFlag = 'E' AND Convert(date,E.DepartureDate,105) BETWEEN Convert(date,@Departing_FromDate,105) AND Convert(date,@Departing_ToDate,105))  
      OR   
      (@Departing_FromDate IS NOT NULL AND @DepartingDateFlag = 'B' AND Convert(date,E.ActualDepartureDate,105) BETWEEN Convert(date,@Departing_FromDate,105) AND Convert(date,@Departing_ToDate,105))  
     )  
    )  
    AND ISNULL(CC.IsContactDelete,0)=0  
  ) tbl  
 
  ORDER BY EnquiryDate DESC  

  OFFSET @limitfrom ROWS FETCH NEXT (SELECT (@limitto)) ROWS ONLY;  
  
  END 
   
END