GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'[sFee].[tTempReceipt]')
BEGIN
CREATE TABLE [sFee].[tTempReceipt](
	[Id] [bigint] NOT NULL,
	[SourceTypeId] [smallint] NULL,
	[SourceId] [bigint] NULL,
	[ReceiptNumber] [int] NOT NULL,
	[ReceiptDate] [date] NOT NULL,
	[ReceiptAmount] [decimal](12, 2) NULL,
	[PayTypeId] [tinyint] NOT NULL,
	[ChequeNo] [nvarchar](40) NULL,
	[BankName] [nvarchar](200) NULL,
	[ReceiptBookId] [int] NOT NULL,
	[Remark] [nvarchar](200) NULL,
	[IsObsolete] [bit] NOT NULL,
	[BatchId] [int] NULL,
	[OrgGroupBatchId] [int] NULL,
	[CreatedUserId] [bigint] NOT NULL,
	[CreatedDateTime] [datetime] NOT NULL,
	[TotalReceiptAmount] [decimal](12, 2) NULL,
	[DiscountAmount] [decimal](12, 2) NULL,
	[IsChequeCleared] [bit] NULL,
	[ChequeClearDate] [date] NULL,
	[ChequeDate] [date] NULL,
	[FeeInvoiceId] [bigint] NULL,
	[CollectionGroupId] [int] NULL,
	[AmountAfterDiscount] [decimal](12, 2) NULL,
	[CGSTPercentage] [decimal](5, 2) NULL,
	[SGSTPercentage] [decimal](5, 2) NULL,
	[CGSTAmount] [decimal](12, 2) NULL,
	[SGSTAmount] [decimal](12, 2) NULL,
	[DepositedLedgerId] [int] NULL,
	[PaymentTransactionId] [bigint] NULL,
	[ReceiptPrintGroupId] [nvarchar](100) NULL,
	[CancelStatusId] [smallint] NULL,
	[CancelStatusRemark] [nvarchar](500) NULL,
	[IsDiscountCoupon] [bit] NULL,
	[FirmReceiptBookId] [int] NULL)
END
GO
-------------------------------------------------------------------------
GO
INSERT INTO sFee.tTempReceipt
select * from sFee.tReceipt
GO

----------------------------
GO
INSERT INTO [sSecurity].[tUser]
           ([Id]
           ,[DisplayTitle]
           ,[LoginTitle]
           ,[LoginPassword]
           ,[LastLockOutDate]
           ,[NumberOfLogonAttempt]
           ,[IsObsolete]
           ,[CreatedUserId]
           ,[CreatedDateTime]
           ,[UpdatedUserId]
           ,[UpdatedDateTime]
           ,[SessionId]
           ,[OrgId]
           ,[SourceTypeId]
           ,[SourceId]
           ,[ValidUpto]
           ,[IsCommon]
           ,[TempPasswordCode]
           ,[PasswordExpiryDate]
           ,[OTPExpiryDate])
     VALUES
           (4
           ,'Online Pay'
           ,'OnlinePay'  
           ,'DM9pfqpY1pnuQQyk+mthAw=='
           ,'2000-01-01 00:00:00.000'
           ,0
           ,0
           ,1
           ,sCommon.fGetDate()
		   ,1
           ,sCommon.fGetDate()
           ,NULL
           ,1
           ,2003
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL
           ,NULL)
GO

------------------

GO
DECLARE @lOrgGroupId int
IF OBJECT_ID('tempdb..##tmpOrgGroupMaster') IS NOT NULL  DROP TABLE #tmpOrgGroupMaster
	CREATE TABLE #tmpOrgGroupMaster 
	(
	  Id int Identity(1,1)
	 ,OrgGroupId int
	 ,Code nvarchar(20)
	)
	
	INSERT INTO #tmpOrgGroupMaster
	select Id, Code from [sSetup].[tOrgGroupMaster] where Id <> 1
	
	Declare @lCount1 int
	SET @lCount1 = (select Count(Id) from #tmpOrgGroupMaster)

	WHILE (@lCount1 > 0)
	BEGIN 
		SET @lOrgGroupId = (select OrgGroupId from #tmpOrgGroupMaster where Id = @lCount1)
		
		IF OBJECT_ID('tempdb..#tmpOrgMaster') IS NOT NULL  DROP TABLE #tmpOrgMaster
	
		CREATE TABLE #tmpOrgMaster 
		(
		  Id int Identity(1,1)
		 ,OrgId int
		 ,Code nvarchar(20)
		)
	
		INSERT INTO #tmpOrgMaster
		select Id, Code from [sSetup].[tOrganizationMaster] where OrgGroupId = @lOrgGroupId AND Id <> 1
		DECLARE @lUserId1 bigint,@lCreatedUserId1 bigint
		SET @lUserId1 = (Select [sCommon].[fGetBigIntIdForOrg](4,@lOrgGroupId))
		SET @lCreatedUserId1 = (Select [sCommon].[fGetBigIntIdForOrg](3,@lOrgGroupId))
			
		INSERT INTO sSecurity.tUser 
		SELECT @lUserId1, DisplayTitle, LoginTitle, LoginPassword,LastLockOutDate,NumberOfLogonAttempt,IsObsolete,@lCreatedUserId1,sCommon.fGetDate()
		,@lCreatedUserId1,sCommon.fGetDate(),SessionId,@lOrgGroupId,SourceTypeId,SourceID,ValidUpto,IsCommon,TempPasswordCode,PasswordExpiryDate,
		OTPExpiryDate from sSecurity.tUser where Id = 4

		Declare @lCount int
		SET @lCount = (select Count(Id) from #tmpOrgMaster)

		WHILE (@lCount>0)
		BEGIN
			Declare @lOrgId int 
			SET @lOrgId = (select OrgId from #tmpOrgMaster where Id = @lCount)

			Update R SET R.CreatedUserId = @lUserId1
			from sFee.tReceipt R
			INNER JOIN sSecurity.tUser U ON U.Id = R.CreatedUserId 
			where (R.CreatedUserId = 3  OR U.SourceTypeId IN (2001,2003)) AND CAST(LEFT(R.Id, 4) AS INT) = @lOrgId

			SET @lCount = @lCount - 1
		END
		SET @lCount1 = @lCount1 - 1
		DROP TABLE #tmpOrgMaster
	END

	DROP TABLE #tmpOrgGroupMaster	

GO


-----------------

GO

ALTER PROCEDURE [sFee].[pReceiptAdd]
	
		   @rId bigint,
		   @rSourceTypeId smallint,
		   @rSourceId bigint,
		   @rReceiptNumber int,
		   @rReceiptDate date,
		   @rReceiptAmount decimal(12,2),
		   @rTotalReceiptAmount decimal(12,2),
		   @rDiscountAmount decimal(12,2),
		   @rPayTypeId tinyint,
		   @rChequeNo nvarchar(40),
		   @rBankName nvarchar(200),
		   @rReceiptBookId int,
		   @rIsObsolete bit,
		   @rCreatedUserId bigint,
		   @rOrgId int,
		   @rBatchId int,
		   @rOrgGroupBatchId int,
		   @rRemark nvarchar(200),
		   @rReceiptDetailXml [nvarchar](max),
		   @rChequeDate date,
		   @rCharge decimal(12,2)=0,
		   @rFeeInvoiceId bigint = NULL,
		   @rCollectionGroupId int = 0,
		   @rDepositedLedgerId int = 0,
		   @rPaymentTransactionId bigint = 0,
		   @rReceiptPrintGroupId nvarchar(100),
		   @rIsDiscountCoupon bit = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @loDoc INT
DECLARE @lIsCreated INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rReceiptDetailXml

BEGIN
	BEGIN TRY
	
	 SELECT @rOrgGroupBatchId = OrgGroupBatchId FROM sSetup.tBatchMaster where Id = @rBatchId

	 DECLARE @lConfigValue nvarchar(50)=''
	 SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 339 AND SourceTypeId = 2010 AND SourceId = LEFT(@rOrgGroupBatchId,4)

	 IF @lConfigValue = ''
		SET @lConfigValue = 'ReceiptBookWise'

	IF @rChequeDate = '1900-01-01'
		SET @rChequeDate = NULL
	 
	IF(@rReceiptNumber <> 0)
	BEGIN
		IF(@rId = 0)--ID Condition Added by dhruv for issue no.0013266	
		BEGIN
			IF(@lConfigValue='ReceiptBookWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
			ELSE IF(@lConfigValue='PayTypeWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
		END
		ELSE
		BEGIN
			IF(@lConfigValue='ReceiptBookWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId AND Id != @rId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
			ELSE IF(@lConfigValue='PayTypeWise')
			BEGIN
				IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId AND Id != @rId)
				BEGIN
					SELECT -102 AS Result
					RETURN	
				END
			END
		END 
	END
	ELSE
	BEGIN
		SET @rReceiptNumber = sFee.fReceiptNumberGet(@rReceiptBookId,@rOrgGroupBatchId,@rPayTypeId,@lConfigValue)
		Print @rReceiptNumber
	END	
EXECUTE [sFee].[pReceiptAddValidate] @rSourceId,'',@rReceiptDetailXml,@rIsCreated = @lIsCreated OUTPUT
 	IF(@lIsCreated = 1)
 		BEGIN
			SELECT -501 AS Result
			RETURN
		END
 
	 BEGIN TRANSACTION	
 IF(@rId = 0)	
BEGIN

	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tReceipt', 0, 1, 1,@outNextNumber = @lNextNumber output
	Print @lNextNumber
--IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
--			BEGIN
--				SELECT -102 AS Result
--				RETURN	
--			END

		--Added By Dhruv
			IF(@lConfigValue='ReceiptBookWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		ELSE IF(@lConfigValue='PayTypeWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		--Added By Dhruv

	Declare @lSourceTypeId int 
	SET @lSourceTypeId =(select ISNULL(SourceTypeId,0) from sSecurity.tUser where Id = @rCreatedUserId)

	DECLARE @lCreatedUserId bigint
	IF(@lSourceTypeId = 2001 OR @lSourceTypeId = 2003 OR @rCreatedUserId = 3)
		SET @lCreatedUserId=(Select [sCommon].[fGetBigIntIdForOrg](4,LEFT(@rOrgGroupBatchId,4)))
	ELSE
		SET @lCreatedUserId = @rCreatedUserId 

	INSERT INTO sFee.tReceipt
		(
		   Id,
		   SourceTypeId,
		   SourceId,
		   ReceiptNumber,
		   ReceiptDate,
		   ReceiptAmount,
		   TotalReceiptAmount,
		   DiscountAmount,
		   PayTypeId,
		   ChequeNo,
		   BankName,
		   ReceiptBookId,
		   Remark,
		   IsObsolete,
		   BatchId,
		   CreatedUserId,
		   CreatedDateTime,
		   OrgGroupBatchId,
		   ChequeDate,
		   FeeInvoiceId,
		   CollectionGroupId,
		   DepositedLedgerId,
		   PaymentTransactionId,
		   ReceiptPrintGroupId,
		   IsDiscountCoupon  ---Added By Dhruv for Issue . 0011150
        )
	VALUES
		(
			@lNextNumber,
			@rSourceTypeId,
			@rSourceId,
		    @rReceiptNumber,
		    @rReceiptDate,
		    @rReceiptAmount,
		    @rTotalReceiptAmount,
		    @rDiscountAmount,
		    @rPayTypeId,
		    @rChequeNo,
		    @rBankName,
		    @rReceiptBookId,
		    @rRemark,
		    @rIsObsolete,
		    @rBatchId,
		    @lCreatedUserId,
		    sCommon.fGetDate(),
		    @rOrgGroupBatchId,
		    @rChequeDate,
		    @rFeeInvoiceId,
		    @rCollectionGroupId,
		    @rDepositedLedgerId,
		    @rPaymentTransactionId,
			@rReceiptPrintGroupId,
			@rIsDiscountCoupon
		)
		
		INSERT INTO sFee.tReceiptDetail
		(
		   ReceiptId,
		   FeeSetupId,
		   Amount
        )
        SELECT  	
		  @lNextNumber,
		  FeeSetupId,
		  Amount
		
	FROM OPENXML (@loDoc, '/Table/ReceiptDetail', 1)
		WITH ( 
				FeeSetupId int,
				Amount decimal(12,2)
			  ) 
		
		
		SELECT @lNextNumber AS Result
		EXEC sp_xml_removedocument @loDoc
		
		IF (@rCharge > 0)
		BEGIN
		EXEC [sFee].[pReceiptChargeAdd] @rSourceTypeId,@rSourceId,@lNextNumber,@lCreatedUserId,@rOrgId,@rBatchId,@rCharge,@rReceiptBookId
	
		END
	
		
		DECLARE @lIsServiceTaxApplicable bit
	    DECLARE @lServiceTaxPercentage decimal(6,2)
	
	    SELECT @lIsServiceTaxApplicable = ISNULL(IsServiceTaxApplicable,0),@lServiceTaxPercentage = ISNULL(ServiceTaxPercentage,0) FROM sFee.tReceiptBookMaster where Id = @rReceiptBookId
		
		IF (@lIsServiceTaxApplicable > 0 AND @lServiceTaxPercentage > 0)
		BEGIN
		EXEC [sFee].[pReceiptServiceTaxAdd] @rSourceTypeId,@rSourceId,@lNextNumber,@lCreatedUserId,@rOrgId,@rBatchId,@lServiceTaxPercentage
		END

		EXEC [sFee].[pFeeProcessGeneratePaidFeeAdd] @rOrgId,@rBatchId,@rSourceId,@lNextNumber,@lCreatedUserId
		
 END 
	ELSE
	
	IF EXISTS(SELECT 1 FROM sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND Id = @rId)
	BEGIN
		UPDATE sFee.tReceipt SET
			ReceiptDate=@rReceiptDate,
			PayTypeId=@rPayTypeId,
			ChequeDate=@rChequeDate,
		    ChequeNo=@rChequeNo,
		    BankName=@rBankName,
		    Remark=@rRemark
		WHERE Id=@rId
		
	SELECT @rId AS RESULT
	END 
	ELSE
	IF(@rReceiptNumber <> 0)
	BEGIN
		IF(@lConfigValue='ReceiptBookWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END
		ELSE IF(@lConfigValue='PayTypeWise')
		BEGIN
			IF EXISTS(SELECT 1 from sFee.tReceipt where ReceiptNumber=@rReceiptNumber AND ReceiptBookId=@rReceiptBookId AND  PayTypeId=@rPayTypeId AND OrgGroupBatchId = @rOrgGroupBatchId)
			BEGIN
				SELECT -102 AS Result
				RETURN	
			END
		END

		BEGIN
		UPDATE sFee.tReceipt SET
			ReceiptNumber = @rReceiptNumber,
			ReceiptDate=@rReceiptDate,
			PayTypeId=@rPayTypeId,
			ChequeDate=@rChequeDate,
		    ChequeNo=@rChequeNo,
		    BankName=@rBankName,
		    Remark=@rRemark
		WHERE Id=@rId
		
	SELECT @rId AS RESULT
	END 
	END
	
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO