IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FunCalVisitorPerAmount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FunCalVisitorPerAmount]
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
Create FUNCTION [dbo].[FunCalVisitorPerAmount](
	@VisitorAcc int,
	@Year char(2),
	@Mount char(2))
RETURNS money
AS
begin

declare @VisitorPer real, @VisitorBaseAmmount money,@VisitorIncreaseAmmount money, @VisitorIncreasePer real, @VisitorAutoDoc bit, @VisitorPerActiveStat bit
declare @InvoiceTotal money
declare @VisitAmount money

SELECT	@VisitorPer=isnull(VisitorPer,0),	
		@VisitorBaseAmmount=isnull(PersonTbl.VisitorBaseAmmount,0),
		@VisitorIncreaseAmmount=isnull(PersonTbl.VisitorIncreaseAmmount,0),
		@VisitorIncreasePer=isnull(PersonTbl.VisitorIncreasePer,0),
		@VisitorAutoDoc=PersonTbl.VisitorAutoDoc,
		@VisitorPerActiveStat=PersonTbl.VisitorPerActiveStat
FROM         PersonTbl 
WHERE     (FK_AccountSysCode = @VisitorAcc)

If @VisitorPerActiveStat=1
Begin
SELECT    @InvoiceTotal= SUM(VW_invoice_Cost_Amount.TotalAmount *(CASE InvoiceTbl.type WHEN 1 then 1 WHEN 3 THEN -1 ELSE 0 END)) 
FROM         VW_invoice_Cost_Amount INNER JOIN
                      InvoiceTbl ON VW_invoice_Cost_Amount.FK_InvoiceSysCode = InvoiceTbl.InvoiceSerialNo
WHERE     (SUBSTRING(InvoiceTbl.InvoiceDate, 0, 3) = @Year) AND (SUBSTRING(InvoiceTbl.InvoiceDate, 4, 2) = @Mount) 
			AND (InvoiceTbl.VisitorSyscode = @VisitorAcc) and InvoiceTbl.type in (1,3)

if @VisitorAutoDoc=1 
	Begin
		set @VisitAmount=@InvoiceTotal*@VisitorPer
	End
Else
	Begin
	if @InvoiceTotal<@VisitorBaseAmmount
		set @VisitAmount=0 --@InvoiceTotal*@VisitorPer
	Else
		Begin
		Set @VisitAmount=@VisitorBaseAmmount*@VisitorPer
		Set	@InvoiceTotal=@InvoiceTotal-@VisitorBaseAmmount
		Set @VisitorPer=@VisitorPer+@VisitorIncreasePer
		While @InvoiceTotal>@VisitorIncreaseAmmount
			Begin
			Set @VisitAmount=@VisitAmount+@VisitorIncreaseAmmount*@VisitorPer
			Set @VisitorPer=@VisitorPer+@VisitorIncreasePer
			Set	@InvoiceTotal=@InvoiceTotal-@VisitorIncreaseAmmount
			END
		Set @VisitAmount=@VisitAmount+@InvoiceTotal*@VisitorPer
		END
	END
END

return isnull(@VisitAmount,0)
end