USE [PTWNBK_H]
GO

/****** Object:  StoredProcedure [dbo].[getDividendQuarterly]    Script Date: 21/07/2018 11:57:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[getDividendQuarterly] @acparentid varchar(40),
@ShareFaceValue numeric(15,2),@IgnoreDiv4FractionShare int,@wherecondi varchar(max),
@fdt datetime,@tdt datetime,@minsharebal numeric(18,2),
@divrate numeric(18,2),@divcalctype int,@roundto int,@inclenddt int,@yearstartdt datetime
AS
BEGIN
		
/*				
declare  @acparentid varchar(40),@wherecondi varchar(max),							
@fdt datetime,@tdt datetime,@minsharebal numeric(18,2),							
@divrate numeric(18,2),@divcalctype int,@roundto int,@inclenddt int,@yearstartdt datetime							
--?	@return_value = [dbo].[getDividend]						
set		@acparentid = '0101'					
set		@wherecondi =' 1=1  '					
set		@fdt = '2015-04-01'					
set		@tdt = '2016-03-31'					
set		@minsharebal = 0					
set		@divrate = 11					
set		@divcalctype = 3					
set		@roundto = 0					
set		@inclenddt = 1					
set		@yearstartdt = '2015-04-01'		
set     @wherecondi	=' Acsub.Acmastid=''A_282347'' '		
							
							
*/				
	---- for ignore div4fraction of share balance 
	-- ( Note :  need to pass the parameter )
	----declare @IgnoreDiv4FractionShare int,@ShareFaceValue numeric(15,2)
	--set @IgnoreDiv4FractionShare=1
	--set @ShareFaceValue=1000
	------------------------------


	SET NOCOUNT ON;						
							
	declare @basefdt datetime						
	set @basefdt = @fdt						
							
	CREATE TABLE  #TempTable (RowID int IDENTITY(1, 1), acmastid varchar(40),						
    account varchar(40),name varchar(50),							
	pfix varchar(15),vcno int,sfix varchar(15),						
    OpProduct numeric(18,2), BookBeginDT datetime,divRate numeric(18,2),amount numeric(18,2),bal numeric(18,2))							
							
    							
    declare @cmdstring varchar(max)							
    set @cmdstring = 'INSERT INTO #TempTable ' +							
	'SELECT AcSub.AcMastId,AcSub.Account,AcSub.Name,'+						
	'AcSub.PFIX,AcSub.VCNO,AcSub.SFIX,'+						
	'Acop.OpProduct,LocSec.BookBeginDT,'+ STR(@divrate,15,3) + ' as divrate,' +						
	'0 as amount,' +						
	'ISNULL(dbo.SubAcClBal(AcSub.AcmastId,AcSub.AcParentId,''' + LEFT(CONVERT(VARCHAR, @tdt, 120), 10) + ''',''' + left(convert(varchar, @yearstartdt,120),10)  +''',''#ALL'',''#ALL''),0) as bal ' +						
	'FROM AcSub '+						
	'LEFT OUTER JOIN Acop ON AcSub.Acmastid = Acop.AcmastId AND Acop.R = 0 INNER JOIN '+						
	'LocSec ON AcSub.LocId = LocSec.LocSecId '+						
	'WHERE ' + @wherecondi + ' AND ' +						
	'ISNULL(dbo.SubAcClBal(AcSub.AcmastId,AcSub.AcParentId,''' + LEFT(CONVERT(VARCHAR, @tdt, 120), 10) + ''',''' + left(convert(varchar, @yearstartdt,120),10)  +''',''#ALL'',''#ALL''),0) >= ' + LTRIM(RTRIM(STR(@minsharebal,15,2))) + 						
	' ORDER BY AcSub.PFIX,AcSub.VCNO,AcSub.SFIX'						
--	'WHERE ' + @wherecondi + ' AND ' +						
							
	exec(@cmdstring)						
							
	DECLARE @NumberRecords int						
    set @NumberRecords = @@ROWCOUNT							
    if @NumberRecords >= 1							
	BEGIN 						
		declare @RowCount int,@divamount numeric(18,2),@shareclbal numeric(18,2),					
		@acmastid varchar(40),@opproduct numeric(18,2),@sharerunningbal numeric(18,2),					
		@NumberTrRecords int,@tempfdt datetime,@temptdt datetime,@daydiff int,@RowCountTr int,					
		@bookbegdt datetime,@mindivamount numeric(18,2)					
							
        SELECT @mindivamount = ISNULL(NumValue,0) FROM ConfigMast WHERE ConfigMastID='#CM318'							
							
		set @RowCount = 1					
		WHILE @RowCount <= @NumberRecords					
		BEGIN					
			set @fdt = @basefdt				
			set @divamount = 0				
			set @shareclbal =0				
			set @opproduct =0				
			set @bookbegdt =null				
			SELECT @acmastid = acmastid, @shareclbal = bal, @opproduct = OpProduct,@bookbegdt = BookBeginDT				
			FROM #TempTable WHERE RowID = @RowCount				
			if @fdt < @bookbegdt				
			begin				
				set @fdt = @bookbegdt			
			end				
							
			if @basefdt > @bookbegdt 				
			begin				
				set @opproduct =0			
			end				
							
			if @divcalctype = 1				
			begin				
				set @sharerunningbal = ISNULL(dbo.SubAcOpBal(@acmastid,@acparentid,@fdt,@yearstartdt,'#ALL','#ALL'),0)			
							
				CREATE TABLE  #TempTrTable (RowID int IDENTITY(1, 1), dt datetime,dr numeric(15,2),			
				cr numeric(15,2))			
				INSERT INTO #TempTrTable 			
				SELECT Billmast.Dt,isnull(Voucher.Dr,0) as dr,isnull(Voucher.Cr,0) as cr			
				FROM Billmast INNER JOIN Voucher 			
				ON Billmast.BillmastId = Voucher.BillMastId  			
				WHERE Billmast.R = 0 AND Billmast.T = 0 AND Voucher.R = 0 AND Voucher.T = 0 AND 			
				Voucher.AcmastId = @acparentid AND Voucher.AcHeadId =@acparentid 			
				AND Voucher.AcSubId =  @acmastid 			
				AND Voucher.Dt >= @fdt AND Voucher.Dt <= @tdt  			
				ORDER BY Billmast.Dt,Billmast.TNo			
				set @NumberTrRecords = @@ROWCOUNT 			
							
				if @opproduct > 0			
				begin			
					set @divamount = (@opproduct * @divrate) / 36500		
				end			
							
				set @tempfdt  = @fdt			
							
				if @NumberTrRecords >= 1			
				begin			
					declare @dr numeric(18,2),@cr numeric (18,2)		
					set @RowCountTr = 1		
					WHILE @RowCountTr <= @NumberTrRecords		
					BEGIN		
						set @dr = 0	
						set @cr =0	
						select @temptdt = dt,@dr = dr,@cr = cr from #TempTrTable WHERE RowID = @RowCountTr	
						set @daydiff =  DATEDIFF(day,@tempfdt,@temptdt)	
						if @sharerunningbal >= @minsharebal	
						begin	
							set @divamount = @divamount + (@sharerunningbal * @divrate * @daydiff) / 36500
						end	
						set @sharerunningbal = (@sharerunningbal + @cr) - @dr	
						set @tempfdt = @temptdt	
						set @RowCountTr = @RowCountTr + 1	
					END -- transaction loop end		
				end			
				DROP TABLE #TempTrTable			
							
				set @temptdt = @tdt			
				if @inclenddt = 1			
				begin			
					set @temptdt = DATEADD(day,1,@temptdt)		
				end			
				set @daydiff =  DATEDIFF(day,@tempfdt,@temptdt)			
				if @sharerunningbal >= @minsharebal			
				begin			
					set @divamount = @divamount + (@sharerunningbal * @divrate * @daydiff) / 36500		
				end			
							
							
			end				
			else if @divcalctype = 2				
			begin				
							
				CREATE TABLE  #TempTrTable16 (RowID int IDENTITY(1, 1),yr int,mn int,Bal16Cr numeric(18,2),			
				Bal16Dr numeric(18,2),BalMonthEndCr numeric(18,2),BalMonthEndDr numeric(18,2))			
							
				INSERT INTO #TempTrTable16 			
				SELECT year(Voucher.dt) as Yr,month(Voucher.dt) Mn,			
				SUM(CASE WHEN day(Voucher.Dt) < 16 THEN Voucher.Cr ELSE 0 END) As Bal16Cr,			
				SUM(CASE WHEN day(Voucher.Dt) < 16 THEN Voucher.Dr ELSE 0 END) As Bal16Dr, 			
				SUM(Voucher.Cr) As BalMonthEndCr, SUM(Voucher.Dr) As BalMonthEndDr 			
				FROM Billmast INNER JOIN Voucher ON billmast.BillmastId = Voucher.BillmastId			
				WHERE Billmast.R = 0 AND bILLMAST.t =0 AND Voucher.R = 0 AND Voucher.T = 0 AND 			
				Voucher.AcmastId = @acparentid AND Voucher.AcHeadId =@acparentid 			
				AND Voucher.AcSubId =  @acmastid AND Voucher.Dt >= @fdt AND Voucher.Dt <= @tdt  			
				GROUP BY Year(Voucher.Dt),Month(Voucher.Dt)			
                ORDER BY Year(Voucher.Dt),Month(Voucher.Dt)							
							
				SET @sharerunningbal = ISNULL(dbo.SubAcOpBal(@acmastid,@acparentid,@fdt,@yearstartdt,'#ALL','#ALL'),0)			
							
				declare @totproduct numeric(18,2)			
				set @totproduct = @opproduct			
				set @tempfdt = @fdt			
				set @temptdt = @tdt			
				declare @month int , @year int,@16dr numeric(18,2),@16cr numeric(18,2),			
				@enddr numeric(18,2),@endcr numeric(18,2),@running16bal numeric(18,2),@runningendbal numeric(18,2)			
				set @running16bal = @sharerunningbal			
				set @runningendbal = @sharerunningbal			
							
				WHILE @tempfdt <= @temptdt			
				BEGIN			
					SET @month = MONTH(@tempfdt)		
					SET @year = YEAR(@tempfdt)		
					set @16dr =0		
					set @16cr =0		
					set @enddr =0		
					set @endcr =0		
					select @16dr = Bal16Dr,@16cr = Bal16Cr,@enddr = BalMonthEndDr,@endcr = BalMonthEndCr		
					FROM #TempTrTable16 WHERE YR = @year AND MN = @month		
					set @running16bal = (@running16bal + ISNULL(@16cr,0)) - ISNULL(@16dr,0)		
					set @runningendbal = (@runningendbal + isnull(@endcr,0)) - isnull(@enddr,0)		
							
					if @running16bal < @runningendbal		
					begin		
						if @running16bal >= @minsharebal	
						begin	
							set @totproduct = @totproduct + @running16bal
						end	
					end		
					else		
					begin		
						if @runningendbal >= @minsharebal	
						begin 	
							set @totproduct = @totproduct + @runningendbal
						end	
					end		
					set @running16bal = @runningendbal		
					set @tempfdt = DATEADD(month,1,@tempfdt)		
				END			
				set @divamount = (@totproduct * @divrate) / 1200;			
							
				drop table #TempTrTable16			
			end 				
			else if @divcalctype = 3 --quarter basis				
			begin				
							
				CREATE TABLE  #TempTrTableCalQtr (RowID int IDENTITY(1, 1),yr int,qtr int,Dr numeric(18,2),Cr numeric(18,2))			
							
				INSERT INTO #TempTrTableCalQtr 			
				SELECT year(Voucher.dt) as Yr,ceiling(month(Voucher.dt)/3.0) Qtr,			
			    SUM(Voucher.dr) As dr, SUM(Voucher.cr) As Cr 				
				FROM Billmast INNER JOIN Voucher ON billmast.BillmastId = Voucher.BillmastId			
				WHERE Billmast.R = 0 AND bILLMAST.t =0 AND Voucher.R = 0 AND Voucher.T = 0 AND 			
				Voucher.AcmastId = @acparentid AND Voucher.AcHeadId =@acparentid 			
				AND Voucher.AcSubId =  @acmastid AND Voucher.Dt >= @fdt AND Voucher.Dt <= @tdt  			
				GROUP BY Year(Voucher.Dt),ceiling(month(Voucher.dt)/3.0)			
                 ORDER BY Year(Voucher.Dt),ceiling(month(Voucher.dt)/3.0)							
							
				--SELECT * FROM 	#TempTrTableCalQtr		
				SET @sharerunningbal = ISNULL(dbo.SubAcOpBal(@acmastid,@acparentid,@fdt,@yearstartdt,'#ALL','#ALL'),0)			
				--PRINT 'OP'
				--PRINT 		@sharerunningbal	
				set @totproduct = @opproduct			
				set @tempfdt = @fdt			
				set @temptdt = @tdt			
				declare @qtr int			
				declare @qtrDr numeric(18,2),@qtrCr numeric(18,2)			
				declare @curdivproduct numeric(18,2)			
				set @runningendbal = @sharerunningbal			
							
				WHILE @tempfdt <= @temptdt	BEGIN	
					
					SET @qtr = ceiling(month(@tempfdt)/3.0)		
					set  @year = YEAR(@tempfdt)		
					set  @qtrDr =0		
					set  @qtrCr =0		
					set @curdivproduct =0 	
				--	print '-----------------  '
	             --   print @qtr                                                      						

					--select * from 	#TempTrTableCalQtr WHERE YR = @year AND Qtr = @qtr	
					--select @qtrDr = QtrDr,@qtrCr = QtrCr		
					--FROM #TempTrTableCalQtr WHERE YR = @year AND Qtr = @qtr		
					select @qtrDr = Dr,@qtrCr = Cr		
					FROM #TempTrTableCalQtr WHERE YR = @year AND Qtr = @qtr		
					
                     -- last op or last running bal > this quarter end bal							
                   --  print @runningendbal
                   --  print (@runningendbal + isnull(@qtrCr,0)) - isnull(@qtrDr,0)
                     
                    if @runningendbal<=(@runningendbal + isnull(@qtrCr,0)) - isnull(@qtrDr,0) begin							
                       set @curdivproduct=@runningendbal end							
                    else begin							
                       set @curdivproduct=(@runningendbal + isnull(@qtrCr,0)) - isnull(@qtrDr,0)							
                    end		
                    
                    					
   					if @curdivproduct >= @minsharebal  begin 
						if @IgnoreDiv4FractionShare=1 begin
						    set @curdivproduct=convert(int,@curdivproduct/@ShareFaceValue)*@ShareFaceValue
						end
						set @totproduct = @totproduct +  @curdivproduct	
 					 --   print @curdivproduct
 					 --   print '--------------------'
 					end		
 					
                    set @runningendbal = (@runningendbal + isnull(@qtrCr,0)) - isnull(@qtrDr,0)							
							
					set @tempfdt = DATEADD(month,3,@tempfdt)		
				END			
							
				set @divamount = (@totproduct * @divrate)*3 / 1200;			
							
			--	print ' tot product '
              --print @totproduct
			--	print @divamount			
				drop table #TempTrTableCalQtr			
							
							
			end				
			else				
			begin				
				set @divamount = (@shareclbal * @divrate) /100			
			end				
							
			set @divamount = round(@divamount,@roundto)				
			if @divamount < @mindivamount				
			begin				
				set @divamount =0			
			end				
							
			update #TempTable set amount = @divamount where acmastid = @acmastid				
			set @RowCount = @RowCount + 1				
							
		END  -- while loop end					
							
	END 						
	select * from #TempTable 						
	drop table #TempTable 						
/*
drop table #TempTable 	
drop table #TempTrTableCalQtr	

*/
END
GO