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

declare  @VisitorGoodGroupCode int, @VisitorGoodActiveStat bit
declare @VisitAmount money

SELECT     
	@VisitorGoodGroupCode=VisitorGoodGroupCode,
	@VisitorGoodActiveStat=VisitorGoodActiveStat
FROM         PersonTbl
Where FK_AccountSysCode=@VisitorAcc

If @VisitorGoodActiveStat=1
	Begin
	 SELECT   @VisitAmount=sum( invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate * invoiceDetailTbl.Fee * isnull(StockTbl.VisitorPer,0) * (CASE InvoiceTbl.type WHEN 1 then 1 WHEN 3 THEN -1 ELSE 0 END))
	FROM         InvoiceTbl INNER JOIN
						  invoiceDetailTbl ON InvoiceTbl.InvoiceSerialNo = invoiceDetailTbl.FK_InvoiceSysCode INNER JOIN
						  StockTbl ON invoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode
	WHERE     (InvoiceTbl.VisitorSyscode = @VisitorAcc and InvoiceTbl.type in (1,3)) AND
		(SUBSTRING(InvoiceTbl.InvoiceDate, 0, 3) = @Year) AND (SUBSTRING(InvoiceTbl.InvoiceDate, 4, 2) = @Mount) 
	End

If @VisitorGoodActiveStat=2
	Begin
	SELECT    @VisitAmount=sum(invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate * invoiceDetailTbl.Fee * isnull(StockTbl.VisitorPer,0) * (CASE InvoiceTbl.type WHEN 1 then 1 WHEN 3 THEN -1 ELSE 0 END))
	FROM         InvoiceTbl INNER JOIN
						  invoiceDetailTbl ON InvoiceTbl.InvoiceSerialNo = invoiceDetailTbl.FK_InvoiceSysCode INNER JOIN
						  StockTbl ON invoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode INNER JOIN
						  stockGroup2Tbl ON StockTbl.GroupID2 = stockGroup2Tbl.GroupId
	WHERE     (InvoiceTbl.VisitorSyscode = @VisitorAcc and InvoiceTbl.type in (1,3)) AND (StockTbl.GroupID2 = @VisitorGoodGroupCode) AND
		(SUBSTRING(InvoiceTbl.InvoiceDate, 0, 3) = @Year) AND (SUBSTRING(InvoiceTbl.InvoiceDate, 4, 2) = @Mount)  
	End

If @VisitorGoodActiveStat=3
	Begin
	SELECT     @VisitAmount=sum(invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate * invoiceDetailTbl.Fee * isnull(stockGroup2Tbl.VisitPercent,0) * (CASE InvoiceTbl.type WHEN 1 then 1 WHEN 3 THEN -1 ELSE 0 END))
	FROM         InvoiceTbl INNER JOIN
						  invoiceDetailTbl ON InvoiceTbl.InvoiceSerialNo = invoiceDetailTbl.FK_InvoiceSysCode INNER JOIN
						  StockTbl ON invoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode INNER JOIN
						  stockGroup2Tbl ON StockTbl.GroupID2 = stockGroup2Tbl.GroupId
	WHERE     (InvoiceTbl.VisitorSyscode = @VisitorAcc and InvoiceTbl.type in (1,3)) AND
		(SUBSTRING(InvoiceTbl.InvoiceDate, 0, 3) = @Year) AND (SUBSTRING(InvoiceTbl.InvoiceDate, 4, 2) = @Mount)
	End
If @VisitorGoodActiveStat=4
	Begin
	SELECT    @VisitAmount=sum(invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate * invoiceDetailTbl.Fee * isnull(stockGroup2Tbl.VisitPercent,0) * (CASE InvoiceTbl.type WHEN 1 then 1 WHEN 3 THEN -1 ELSE 0 END))
	FROM         InvoiceTbl INNER JOIN
						  invoiceDetailTbl ON InvoiceTbl.InvoiceSerialNo = invoiceDetailTbl.FK_InvoiceSysCode INNER JOIN
						  StockTbl ON invoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode INNER JOIN
						  stockGroup2Tbl ON StockTbl.GroupID2 = stockGroup2Tbl.GroupId
	WHERE     (InvoiceTbl.VisitorSyscode = @VisitorAcc and InvoiceTbl.type in (1,3)) AND (StockTbl.GroupID2 = @VisitorGoodGroupCode) AND
		(SUBSTRING(InvoiceTbl.InvoiceDate, 0, 3) = @Year) AND (SUBSTRING(InvoiceTbl.InvoiceDate, 4, 2) = @Mount)  
	End
 
return isnull(@VisitAmount,0)
end