set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO




ALTER FUNCTION [dbo].[FunDocumentDetail_Archive_FromDate] 
	(
	@ArchiveName nvarchar(100),
	@FromDate Char(8)=''
	)
RETURNS 
	 @table_variable TABLE (
		DetailCode int,
		AmmountIn Money, 
	    AmmountOut Money,
	    UserRowNo int, 
	    DocDate char(8),
	    Comment nvarchar(300),
		ArchiveName nvarchar(100),
        SysDate datetime,
		SndFk_AccountSyscode int,
		DocCode int,
		AccountCode int
       	)
AS
	BEGIN

--- find از قبل
if not (@FromDate is null or @FromDate='' or @FromDate='00/00/00')
	INSERT INTO @table_variable
SELECT     - 1 AS DetailCode, SUM(AmmountIN) AS AmmountIN, SUM(AmmountOUT) AS AmmountOut, NULL AS UserRowNo, NULL AS DocDate, 
                      N'....' + MIN(DocDate) + N' - ' + MAX(DocDate) + N'...' AS Comment, NULL AS ArchiveName, NULL AS SysDate, NULL AS SndFk_AccountSyscode,null as DocCode,null as Fk_AccountSyscode
FROM         VWDocumentDetail_DocHeader
WHERE     (DocDate < @FromDate)
GROUP BY DetailCode * 0
else set @FromDate=''


--- find other Archive
if not (@ArchiveName is null)
begin
			INSERT INTO @table_variable
SELECT     - 2 AS DetailCode, SUM(AmmountIN) AS AmmountIN, SUM(AmmountOUT) AS AmmountOut, NULL AS UserRowNo, NULL AS DocDate, 
                      ArchiveName + N' : ' + MIN(DocDate) + N' - ' + MAX(DocDate) AS Comment, ArchiveName, NULL AS SysDate, NULL AS SndFk_AccountSyscode, NULL 
                      AS DocCode, NULL AS Fk_AccountSyscode
FROM         VWDocumentDetail_DocHeader
WHERE     (DocDate >= @FromDate)
GROUP BY ArchiveName
HAVING      (ArchiveName <> @ArchiveName)
ORDER BY ArchiveName
		--- Main list with archive
			INSERT INTO @table_variable
SELECT     DetailCode, AmmountIN, AmmountOUT, UserRowNo, DocDate, Comment, ArchiveName, SysDate, SndFk_AccountSyscode, Fk_docSysCode, 
                      Fk_AccountSyscode
FROM         VWDocumentDetail_DocHeader
WHERE     (DocDate >= @FromDate) AND (ArchiveName = @ArchiveName)
end
else 
			INSERT INTO @table_variable
SELECT     DetailCode, AmmountIN, AmmountOUT, UserRowNo, DocDate, Comment, ArchiveName, SysDate, SndFk_AccountSyscode, Fk_docSysCode, 
                      Fk_AccountSyscode
FROM         VWDocumentDetail_DocHeader
WHERE     (DocDate >= @FromDate)

return
	END