
ALTER PROCEDURE [sFee].[pFeeCustomSearchGet]
(
	@rOrgGroupBatchId int,
	@rOrgId smallint,
	@rBatchId int,
	@rOrderBy nvarchar(max) = 'ReceiptNumber',
	@rCode [nvarchar](20) = '',
	@rFirstName [nvarchar](50) = '',	
	@rLastName [nvarchar](50) = '',	
	@rDivisionId nvarchar(max) = '',
	@rReceiptBookId nvarchar(max) = '',
	@rFromReceiptDate Date = '0001-01-01',
	@rToReceiptDate Date = '9999-12-31',
	@rFromReceiptNo int,
	@rToReceiptNo int,
	@rCreatedUserId bigint,
	@rIsObsolete bit,
	@rDropDownStatusId int,
	@rDropDownClassId int,
	@rDropDownDivisionId int,
	@rDropDownReceiptBookId int,
	@rDepartmentId int=0,
	@rPageIndex int = null,
	@rAdmissionCategoryId int = 0
 )
AS
SET NOCOUNT ON
BEGIN
	create table #TEMPFee
	(
		tid int identity(1,1),t_AcedemicId bigint,t_ReceiptId bigint
	)
	
	DECLARE @Sql nvarchar(MAX)
	IF(@rFromReceiptDate IS NULL)
		SET @rFromReceiptDate='0001-01-01'
	IF(@rToReceiptDate IS NULL)
		SET @rToReceiptDate='9999-12-31'

		
	SET @rCode = sCommon.fSearchStringGet(@rCode)
	SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
	SET @rLastName = sCommon.fSearchStringGet(@rLastName)
	select @rPageIndex = case when isnull(@rPageIndex,0) = 0 then 1 else @rPageIndex end
	
	SELECT FCS.AcademicId,FCS.ClassId,FCS.DivisionId,FCS.RollNo,FCS.OrgGroupBatchId,
	FCS.OrgId,FCS.BatchId,FCS.OrgGroupBatch,FCS.Organization,FCS.Batch,FCS.StudentId,
	FCS.Code,FCS.Number,FCS.FirstName,FCS.LastName,FCS.Name,FCS.StatusId,FCS.ClassName,
	FCS.DivisionName,FCS.StatusName,FCS.ReceiptId,FCS.ReceiptNumber,FCS.ReceiptDate,
	FCS.ReceiptAmount,FCS.ChequeNo,FCS.BankName,FCS.Remark,FCS.IsObsolete,FCS.FeeReceiptBookId,
	FCS.ReceiptBookCode,FCS.ReceiptBookName,FCS.Address1,FCS.Address2,FCS.BankAccountNumber,
	FCS.PayTypeId,FCS.PayType,FCS.CreatedUserId,FCS.CreatedUserName, FCS.ParentContactNo
	into sFee.tTempFee1
	FROM sFee.vFeeCustomSearch FCS
	INNER JOIN sSetup.tClassMaster CM ON CM.Id = FCS.ClassId
	WHERE (FCS.OrgGroupBatchId = @rOrgGroupBatchId)
	AND	((FCS.OrgId = @rOrgId) OR (@rOrgId=0))
	AND ((FCS.BatchId = @rBatchId) OR (@rBatchId=0))
	AND ((FCS.Code Like @rCode + '%') OR (@rCode = ''))
	AND ((FCS.FirstName Like @rFirstName  + '%') OR (@rFirstName = ''))
	AND ((FCS.LastName Like @rLastName + '%')OR(@rLastName = ''))
	AND ((FCS.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo =0))
	AND ((FCS.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo=0))
	AND ((ISNULL(FCS.ReceiptDate,'0001-01-01') >= @rFromReceiptDate) OR (ISNULL(@rFromReceiptDate,'') = ''))
	AND ((ISNULL(FCS.ReceiptDate,'9999-12-31') <= @rToReceiptDate) OR (ISNULL(@rToReceiptDate,'') = ''))
	AND (FCS.DivisionId In( SELECT cast(item as int) FROM  sCommon.fnsplit(@rDivisionId,','))OR @rDivisionId='')
	AND (FCS.FeeReceiptBookId In( SELECT cast(item as int) FROM  sCommon.fnsplit(@rReceiptBookId,','))OR @rReceiptBookId='')
	AND ((FCS.CreatedUserId=@rCreatedUserId) OR (@rCreatedUserId=0))
	AND (FCS.IsObsolete=@rIsObsolete)
	AND ((FCS.StatusId = @rDropDownStatusId) OR (@rDropDownStatusId=0))
	AND ((FCS.ClassId = @rDropDownClassId) OR (@rDropDownClassId=0))
	AND ((FCS.DivisionId = @rDropDownDivisionId) OR (@rDropDownDivisionId=0))
	AND ((FCS.FeeReceiptBookId = @rDropDownReceiptBookId) OR (@rDropDownReceiptBookId=0))
	AND ((Cm.DepartmentId =@rDepartmentId ) OR (@rDepartmentId=0))
	AND ((FCS.AdmissionCategoryId = @rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
	
	SET @rOrderBy=Replace(@rOrderBy,'ID No','Code')
	SET @rOrderBy=Replace(@rOrderBy,'GR No','Code')
	
	SET @sql='SELECT Code AS Code,Name,ReceiptNumber,ReceiptDate,ReceiptAmount,
	PayType,ChequeNo,BankName,ReceiptBookName,Remark,OrgId,BatchId
	FROM sFee.tTempFee1 ORDER BY '+@rOrderBy
	
	exec(@Sql)
	
	SET @sql='INSERT INTO #TEMPFee SELECT AcademicId, ReceiptId
	FROM sFee.tTempFee1 ORDER BY '+@rOrderBy
	
	exec(@Sql)
	
	declare @lTotalRecords int,@lPageSize int = 10,@lPaging varchar(max)='',
			@lResult nvarchar(max)=''	--change varchar to nvarchar for mantis: 32993
	select @lTotalRecords = count(*) from #TEMPFee
	
	select @lPaging = @lPaging + dbo.fnc_SearchPagingFormat(@rPageIndex,@lTotalRecords,@lPageSize)
	
	select @lResult = @lResult + '<tr attrId="' + convert(varchar,isnull(t_AcedemicId,0)) + '"
	attrCode="' + isnull(Code,'') + '"
	attrReceiptNo="' + isnull(convert(varchar,ReceiptNumber),'') + '"
	attrBatchId="' + convert(varchar,@rBatchId) + '"
	attrOrgId="' + convert(varchar,@rOrgId) + '"
	attrOrgGroupBatchId="' + convert(varchar,@rOrgGroupBatchId) + '">
	<td><a style="cursor:pointer;" onclick="SecectThisRecord(this);">Select</a></td><td>' + isnull(Code,'') + '</td>
	<td>' + isnull(Name,'') + '</td><td>' + isnull(convert(varchar,ReceiptNumber),'') + '</td>
	<td>' + isnull(convert(varchar,ReceiptDate,103),'') + '</td>
	<td>' + isnull(convert(varchar,ReceiptAmount),'') + '</td><td>' + isnull(PayType,'') + '</td>
	<td>' + isnull(ChequeNo,'') + '</td><td>' + isnull(BankName,'') + '</td>
	<td>' + isnull(ReceiptBookName,'') + '</td><td>' + isnull(Remark,'') + '</td></tr>'
	from sFee.tTempFee1,#TEMPFee where t_AcedemicId = AcademicId AND t_ReceiptId = ReceiptId
	and tid between ((@lPageSize * (@rPageIndex - 1)) + 1) AND (@lPageSize * @rPageIndex)
	
	select @lPaging as UserPaging,@lResult as UserResult,@lTotalRecords as UserTotal
			
	DROP Table sFee.tTempFee1
	drop Table #TEMPFee
END
