USE [TMS]
GO
/****** Object:  StoredProcedure [dbo].[SearchTicketsrpt]    Script Date: 12/4/2017 1:01:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:		Abhishek Mahajan
-- Alter date: 07/10/2008
-- Description:	Search tickets on the basis of filter criteria.
-- =============================================
ALTER Procedure [dbo].[SearchTicketsrpt]
(
	@CustomerId varchar(50),
	@ProjectId varchar(8000),
    
   
	@AssignedTo varchar(8000),
	@AssignedTeamId varchar(8000),
    @CurrentStatus varchar(8000),
	@RespondedBy varchar(8000),
	@ApplicationId varchar(8000),
	@PriorityId varchar(8000),
	@DetailedCategoryId varchar(8000),
	@SubCategoryId varchar(8000),
	@TicketNo varchar(50),
	@CallTypeCode varchar(8000),
	@CallSourceCode varchar(8000),
	@ResolvedBy varchar(100),
	
	@Submitter varchar(100),
	@ProblemSummary varchar(8000),
	@AssignedDateGreater datetime,
	@AssignedDateLesser datetime,
	@ResolvedDateGreater datetime,
	@ResolvedDateLesser datetime,
	@IsResponseOverDue varchar(100),
	@IsResolutionOverDue varchar(100),
	@Country varchar(100)
	
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @vSQL varchar(8000)

select @vSQL = 'SELECT   Distinct dbo.Organisation.ORG_NAME as Customer,TicketDetail.TICKET_NO AS TicketNo, '+
            'dbo.Application.APP_NAME as Application,dbo.CallType.CALL_NAME as CallType,dbo.SLAPriorityDetails.SLA_PRIORITY_CODE as Priority,dbo.Team.TEAM_NAME as Team,TicketStatus.TICKET_STATUS_NAME AS Status,TicketDetail.TICKET_PROBLEM_SUMMARY AS ProblemSummary,TicketDetail.TICKET_RESPONSE_MADE_BY AS RespondedBy,   '+
'Project.PROJECT_NAME, '+
'dbo.TicketDetail.TICKET_RESOLUTION_MADE_BY,cast(ltrim(rtrim(replace(replace(dbo.TicketDetail.TICKET_RESOLUTION_Date, char(13), ''''), char(10), '' ''))) as varchar(max) ) AS TicketResolutionDate,cast(ltrim(rtrim(replace(replace(dbo.TicketDetail.TICKET_RESOLUTION_DEADLINE, char(13), ''''), char(10), '' ''))) as varchar(max) ) AS TicketResolutionDeadline, 
 '+
'cast(ltrim(rtrim(replace(replace(dbo.TicketDetail.Call_Loggin_Time, char(13), ''''), char(10), '' ''))) as varchar(max) ) AS LoginTime, '+

'dbo.Impact.IMPACT_VALUE,dbo.TicketDetail.Complexity


FROM	TicketDetail INNER JOIN '+
            'Project ON TicketDetail.FK_PROJECT_ID = Project.PK_PROJECT_ID INNER JOIN '+
'dbo.BusinessUnit on dbo.BusinessUnit.PK_BUSINESS_UNIT_ID=dbo.Project.FK_BUSINESS_UNIT_ID inner JOIN '+
'dbo.Discipline on dbo.Discipline.DISCIPLINE_ID=dbo.Project.FK_DISCIPLINE_ID inner Join '+
'TicketStatus ON TicketDetail.FK_TICKET_STATUS_ID = TicketStatus.PK_TICKET_STATUS_ID LEFT OUTER JOIN '+
'ApplicationDetailedCategory ON TicketDetail.FK_APP_DETAILED_CATEGORY_ID = ApplicationDetailedCategory.PK_APP_DETAILED_CATEGORY_ID LEFT OUTER JOIN '+
'Organisation ON Project.FK_CUSTOMER_ORG_ID = Organisation.PK_ORG_ID INNER JOIN '+
'dbo.SubSector on dbo.SubSector.PK_SUB_SECTOR_ID=dbo.Organisation.FK_SUB_SECTOR_ID inner join '+
'dbo.Sector on dbo.Sector.PK_SECTOR_ID=dbo.SubSector.FK_SECTOR_ID inner join '+
'CallSource ON TicketDetail.FK_CALL_SOURCE_ID = CallSource.PK_CALL_SOURCE_ID INNER JOIN '+
'CallType ON TicketDetail.FK_CALL_TYPE_ID = CallType.PK_CALL_TYPE_ID Left outer Join '+


'dbo.Team on  dbo.Team.PK_TEAM_ID=dbo.TicketDetail.FK_ASSIGNED_TO_TEAM_ID Left outer Join '+
'dbo.Severity on dbo.Severity.PK_SEVERITY_ID=dbo.TicketDetail.FK_SEVERITY_ID Left outer Join '+

'dbo.SLAPriorityDetails on dbo.SLAPriorityDetails.PK_SLA_PRIORITY_ID=dbo.TicketDetail.FK_SLA_PRIORITY_ID Left outer Join '+
'dbo.SLA on dbo.SLA.PK_SLA_ID=dbo.SLAPriorityDetails.FK_SLA_ID Left Outer join '+
'dbo.Application on dbo.Application.PK_APP_ID=dbo.TicketDetail.FK_APP_ID Left outer Join '+
'dbo.ApplicationSubcategory on dbo.ApplicationSubcategory.PK_APP_SUB_CATEGORY_ID=dbo.TicketDetail.FK_APP_SUB_CATEGORY_ID Left outer Join '+
'dbo.TeamMember on dbo.TeamMember.EMP_ID=dbo.TicketDetail.FK_Assign_To_Member_ID Left outer Join '+
'dbo.Employee on dbo.Employee.EMP_ID=dbo.TeamMember.EMP_ID left outer join '+
'dbo.Person on dbo.Person.PK_PERSON_ID=dbo.Employee.FK_PERSON_ID left outer join '+
'dbo.ProjectCustomerStatus on dbo.ProjectCustomerStatus.PK_PROJECT_CUSTOMER_STATUS_ID=dbo.TicketDetail.FK_PROJECT_CUSTOMER_STATUS_ID left outer join '+
'dbo.Impact on dbo.Impact.PK_IMPACT_ID=dbo.TicketDetail.Fk_Impact_Id left outer join '+
'dbo.SolutionTicket on dbo.SolutionTicket.FK_TICKET_ID=dbo.TicketDetail.PK_TICKET_ID left outer join '+
'dbo.Solution on dbo.Solution.PK_SOL_ID=dbo.SolutionTicket.FK_SOLUTION_ID left outer join '+
'dbo.FixCode on dbo.FixCode.PK_FIX_CODE_ID=dbo.TicketDetail.Fix_Code left outer join '+
'dbo.TicketCustomFieldDetail on dbo.TicketDetail.PK_TICKET_ID = dbo.TicketCustomFieldDetail.PK_TICKET_ID left outer join '  +
'dbo.CustomFieldDetails on dbo.TicketCustomFieldDetail.PK_FIELD_ID = dbo.CustomFieldDetails.PK_FIELD_ID  '+

	'WHERE '+''

		

            if(@CustomerId <> '00000000-0000-0000-0000-000000000000' AND @CustomerId IS not NULL)
			BEGIN
				select @vSQL = @vSQL + 'dbo.Project.FK_CUSTOMER_ORG_ID = ''' + convert(varchar(36), @CustomerId) + '''' 
            END

	        if(@ResolvedDateGreater is not NULL)
			BEGIN
			select @vSQL = @vSQL + ' AND (TicketDetail.Ticket_Closed_Date is NULL OR TicketDetail.Ticket_Closed_Date >= ''' + convert(varchar, @ResolvedDateGreater) + ''')'
			END
			if(@ResolvedDateLesser is not NULL)
			BEGIN
			select @vSQL = @vSQL + ' AND (TicketDetail.Ticket_Closed_Date is NULL OR TicketDetail.Ticket_Closed_Date <= ''' + convert(varchar, @ResolvedDateLesser) + ''')' 
			END
			           
			if(@AssignedDateGreater is not NULL)
			BEGIN
			select @vSQL = @vSQL + ' AND (TicketDetail.Call_Loggin_Time is NULL OR TicketDetail.Call_Loggin_Time >= ''' + convert(varchar, @AssignedDateGreater) + ''')'
			END
			if(@AssignedDateLesser is not NULL)
			BEGIN
			select @vSQL = @vSQL + ' AND (TicketDetail.Call_Loggin_Time is NULL OR TicketDetail.Call_Loggin_Time <= ''' + convert(varchar, @AssignedDateLesser) + ''')'
			END 

           

	        if(@ProblemSummary <> '' AND @ProblemSummary IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND TicketDetail.TICKET_PROBLEM_SUMMARY like ''%'+ convert(varchar(36), @ProblemSummary) + '%'''
            END

			 

		    if(@Submitter <> '' AND @Submitter IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND TicketDetail.SUBMITTER like ''%'+ convert(varchar(36), @Submitter) + '%'''
            END

			if(@ResolvedBy <> '' AND @ResolvedBy IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND TicketDetail.TICKET_RESOLUTION_MADE_BY like ''%'+ convert(varchar(36), @ResolvedBy) + '%'''
            END	

            if(@RespondedBy <> '' AND @RespondedBy IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND TicketDetail.TICKET_RESPONSE_MADE_BY like ''%'+ convert(varchar(36), @RespondedBy) + '%'''
            END				
				
           
			
			if(@AssignedTo <> '00000000-0000-0000-0000-000000000000' AND @AssignedTo IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.FK_Assign_To_Member_ID IN (''' + @AssignedTo + ''')'
			END

            

		    if(@TicketNo IS not NULL AND @TicketNo <> 0)
			BEGIN
			select @vSQL = @vSQL + ' AND TicketDetail.TICKET_NO = ''' + convert(varchar(36), @TicketNo) + ''''
			END 

			if(@ProjectId <> '00000000-0000-0000-0000-000000000000' AND @ProjectId IS not NULL)
			BEGIN
			select @vSQL = @vSQL + ' AND dbo.TicketDetail.FK_PROJECT_ID IN (''' + @ProjectId + ''')'
			END

			if(@PriorityId <> '00000000-0000-0000-0000-000000000000' AND @PriorityId IS not NULL)
			BEGIN
			select @vSQL = @vSQL + ' AND dbo.SLAPriorityDetails.PK_SLA_PRIORITY_ID IN (''' + @PriorityId + ''')'
			END

			if(@AssignedTeamId <> '00000000-0000-0000-0000-000000000000' AND @AssignedTeamId IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.FK_ASSIGNED_TO_TEAM_ID IN (''' + @AssignedTeamId + ''')'	
     		END

			if(@CurrentStatus <> '00000000-0000-0000-0000-000000000000' AND @CurrentStatus IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.FK_TICKET_STATUS_ID IN (''' + @CurrentStatus + ''')'
			END

			if(@ApplicationId <> '00000000-0000-0000-0000-000000000000' AND @ApplicationId IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.FK_APP_ID IN (''' + @ApplicationId + ''')'
			END

			if(@DetailedCategoryId <> '00000000-0000-0000-0000-000000000000' AND @DetailedCategoryId IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND ApplicationDetailedCategory.PK_APP_DETAILED_CATEGORY_ID IN (''' + @DetailedCategoryId + ''')'
			END

			if(@SubCategoryId <> '00000000-0000-0000-0000-000000000000' AND @SubCategoryId IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND TicketDetail.FK_APP_SUB_CATEGORY_ID IN (''' + @SubCategoryId + ''')'
			END

			if(@CallTypeCode <> '00000000-0000-0000-0000-000000000000' And @CallTypeCode IS not NULL)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.FK_CALL_TYPE_ID IN (''' + @CallTypeCode + ''')'
			END

			if(@CallSourceCode <> '00000000-0000-0000-0000-000000000000' AND @CallSourceCode is not null)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.FK_CALL_SOURCE_ID IN (''' + @CallSourceCode + ''')'
			END
            if(@IsResponseOverDue is not null)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.IS_RESPONSE_OVERDUE = (''' + @IsResponseOverDue + ''')'
			END
			if(@IsResolutionOverDue is not null)
			BEGIN
				select @vSQL = @vSQL + ' AND dbo.TicketDetail.IS_RESOLUTION_OVERDUE = (''' + @IsResolutionOverDue + ''')'
			END

			if(@Country is not null and @Country <> '')
			BEGIN
				--select @vSQL = @vSQL + ' AND dbo.TicketCustomFieldDetail.PK_TICKET_ID IN (SELECT PK_TICKET_ID from TicketCustomFieldDetail Where Value = ''' + @Country + ''' AND PK_FIELD_ID In (SELECT PK_FIELD_ID FROM CUSTOMFIELDDETAILS WHERE FIELD_NAME = ''Country''))  '
				select @vSQL = @vSQL + ' AND Upper(dbo.TicketCustomFieldDetail.Value) = Upper(''' + @Country + ''') '
			END
 
			
			select @vSQL = @vSQL +' Order By  dbo.TicketDetail.TICKET_NO Desc'


	--print @vSQL

	Execute (@vSQL)	
	--exec sp_executesql @statement = @vSQL


END