accrpt.asp

进销存软件源代码

<%
dim gDateFrom1,gDateFrom2,gDateTo1, gDateTo2
dim gTableName
dim gIncludeChild
gTableName="bdVoucher"
gIncludeChild=0

Function NullToZero(v)
	if IsNull(v) then
		NullToZero=0
	else
		NullToZero=v
	end if
End Function

'初始化
Function RptInit(DateFrom1,DateFrom2,DateTo1, DateTo2)
	gDateFrom1=DateFrom1
	gDateFrom2=DateFrom2
	gDateTo1=DateTo1
	gDateTo2=DateTo2
End Function

'取得科目余额方向
Function RptSubjectBalanceDirect(Code)
'返回：0/1 debit/credit
	dim szSQl
	dim rsCheck
	
	RptSubjectBalanceDirect = 0
	szSQL="SELECT Count(*) as rc FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [Code]='" & SQLInputParam(Code) & "'"
	rsCheck=Conn.Execute(szSQL)
	if rsCheck("rc")>0 then
		szSQL="SELECT [int1] FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [Code]='" & SQLInputParam(Code) & "'"
		rsCheck=Conn.Execute(szSQL)
		RptSubjectBalanceDirect = rsCheck("int1")
	end if
	set rsCheck=nothing
End Function

'取得科目余额
Function RptSubjectBalance(Code,Time)
	dim szSQl, szCode, iCodeLen, DebitOrCredit, FirstBalance
	dim rsCheck,gDateTo
	if Time=0 then
		if gDateTo1="FirstNum" then
			RptSubjectBalance=NullToZero(RptGetFristYear(Code))
			exit function
		else
		if len(gDateTo1)=0 then	err.Raise 10000,"AccountRpt","取科目余额无结束时间！"
			gDateto=gDateTo1
		end if
	
	else
		if len(gDateTo2)=0 then	err.Raise 10000,"AccountRpt","取科目余额无结束时间！"
			gDateTo=gDateTo2
	end if
	szCode=Code
	DebitOrCredit=RptSubjectBalanceDirect(szCode)
	iCodeLen = Len(szCode)
	if isdate(gDateTo) then
	'计算期初余额
	szSQL="SELECT SUM(POWER(-1,[biSubject].[int1])*[vl].[Quantity]) AS [Amount] FROM [bdFirstBalance] AS [vt],[bdFirstBalanceList] AS [vl],[biSubject] WHERE [vt].[BillType]=17 AND [vt].[ID]=[vl].[ID] AND [biSubject].[ID]=[vl].[ProductID] AND [vt].[AccountID]=" & AccountID & " AND SUBSTRING([biSubject].[Code],1,"&iCodeLen&")='" & szCode & "'"
	rsCheck=Conn.Execute(szSQL)
	FirstBalance=NullToZero(rsCheck("Amount"))
	end if
	'End	
	szSQL="SELECT SUM([vl].[Currency1]-[vl].[Currency2]) AS [Amount] FROM ["&gTableName&"] AS [vt],["&gTableName&"List] AS [vl],[biSubject] WHERE [vt].[AuditID]<>0 AND [vt].[Isdeleted]=0 AND [vt].[BillType]=8 AND [vt].[ID]=[vl].[ID] AND [biSubject].[ID]=[vl].[ProductID] AND [vt].[AccountID]=" & AccountID & " AND [vt].[BillDate]<='" & GetDateString(gDateTo) &"' AND SUBSTRING([biSubject].[Code],1,"&iCodeLen&")='" & szCode & "'"
	rsCheck=Conn.Execute(szSQL)
	if DebitOrCredit=0 then
		RptSubjectBalance=NullToZero(rsCheck("Amount"))+FirstBalance
	else
		RptSubjectBalance=0-NullToZero(rsCheck("Amount"))-FirstBalance
	end if
End Function

'取得科目明细帐户的借方或贷方余额的合计
Function RptSubjectDetailBalance(Code, DebitOrCredit)	'0/1 debit/credit
	dim szSQl, szCode, iCodeLen
	dim rsCheck

	szCode=Code
	iCodeLen = Len(szCode)

	'Set3
	'Set2
	'Set1
	szSQL="SELECT DISTINCT [vt].[AccountID] AS [AccountID], [biSubject].[Code] AS [Code], SUM([vl].[Currency1]-[vl].[Currency2]) AS [Amount] FROM ["&gTableName&"] AS [vt],["&gTableName&"List] AS [vl],[biSubject] WHERE [vt].[BillType]=8 AND [vt].[ID]=[vl].[ID] AND [biSubject].[ID]=[vl].[ProductID] AND [vt].[AccountID]=" & AccountID & " AND [vt].[BillDate]<='" & GetDateString(gDateTo) &"' AND SUBSTRING([biSubject].[Code],1,"&iCodeLen&")='" & szCode & "' GROUP BY [vt].[AccountID], [biSubject].[Code]"
	szSQL=szSQL&"UNION SELECT DISTINCT [vt].[AccountID] AS [AccountID], [biSubject].[Code] AS [Code], SUM(POWER(-1,[vl].[int2])*[vl].[Quantity]) AS [Amount] FROM [bdFirstBalance] AS [vt],[bdFirstBalanceList] AS [vl],[biSubject] WHERE [vt].[BillType]=17 AND [vt].[ID]=[vl].[ID] AND [biSubject].[ID]=[vl].[ProductID] AND [vt].[AccountID]=" & AccountID & " AND SUBSTRING([biSubject].[Code],1,"&iCodeLen&")='" & szCode & "' GROUP BY [vt].[AccountID], [biSubject].[Code]"
	'Set1 End
	szSQL="SELECT DISTINCT [Code], SUM([Amount]) AS [Amount] FROM (" & szSQL & ") AS [nv] GROUP BY [Code]"
	'Set2 End
	szSQL="SELECT DISTINCT SUM([Amount]) AS [Amount] FROM (" & szSQL & ") AS [nnv] "
	if DebitOrCredit=0 then
		szSQL=szSQL & "WHERE [Amount]>0"
	else
		szSQL=szSQL & "WHERE [Amount]<0"
	end if
	'Set3 End
	rsCheck=Conn.Execute(szSQL)
	if DebitOrCredit=0 then
		RptSubjectDetailBalance=NullToZero(rsCheck("Amount"))
	else
		RptSubjectDetailBalance=0-NullToZero(rsCheck("Amount"))
	end if
End Function

'取得科目发生额
Function RptSubjectHappen(Code, DebitOrCredit, Time)	'0/1 debit/credit
	dim szSQl, szCode, iCodeLen, szSumName, gDateFrom, gDateTo
	dim rsCheck
	if Time=0 then
		if len(gDateFrom1)=0 then	err.Raise 10000,"AccountRpt","取科目发生额无开始时间！"
		if len(gDateTo1)=0 then	err.Raise 10000,"AccountRpt","取科目发生额无结束时间！"
		gDateFrom=gDateFrom1
		gDateTo=gDateTo1
	else
		if len(gDateFrom2)=0 then	err.Raise 10000,"AccountRpt","取科目发生额无开始时间！"
		if len(gDateTo2)=0 then	err.Raise 10000,"AccountRpt","取科目发生额无结束时间！"
		gDateFrom=gDateFrom2
		gDateTo=gDateTo2
	end if
	
	szCode=Code
	iCodeLen = Len(szCode)
	if DebitOrCredit=0 then
		szSumName="Currency1"
	else
		szSumName="Currency2"
	end if
	szSQL="SELECT SUM([vl].["&szSumName&"]) AS [Amount] FROM ["&gTableName&"] AS [vt],["&gTableName&"List] AS [vl],[biSubject] WHERE [vt].[AuditID]<>0 AND [vt].[Isdeleted]=0 AND [vt].[BillType]=8 AND [vt].[ID]=[vl].[ID] AND [biSubject].[ID]=[vl].[ProductID] AND [vt].[AccountID]=" & AccountID & " AND [vt].[BillDate]<='" & GetDateString(gDateTo) &"' AND SUBSTRING([biSubject].[Code],1,"&iCodeLen&")='" & szCode & "'"
	if gDateFrom<>"First" then szSQL = szSQL & " AND [vt].[BillDate]>'" & GetDateString(gDateFrom) &"'"
	rsCheck=Conn.Execute(szSQL)
	RptSubjectHappen=NullToZero(rsCheck("Amount"))
End Function

function RptGetFristYear(Code)
	
	dim szSQL,szSelect,szCheck
	dim rsCheck

	szSQL=" FROM [bdFirstBalance] AS [vt], [bdFirstBalanceList] AS [vl], [biSubject] WHERE [vt].[BillType]=17 AND [vt].[ID]=[vl].[ID] AND [vt].[AccountID]=" & AccountID &" AND [biSubject].[ID]=[vl].[ProductID] AND [biSubject].[Code]='"& CStr(Code) &"' AND [biSubject].[InfoType]=10"
	szSelect="SELECT [vl].[Currency3] As [Currency3]"
	szCheck=Conn.Execute("SELECT COUNT(*) AS rc " & szSQL)
	if szCheck("rc")<>0 then
		rsCheck=Conn.Execute(szSelect & szSQL)
		RptGetFristYear=NullToZero(rsCheck("Currency3"))
	else
		RptGetFristYear=0
	end if
	set szCheck=nothing
	set rsCheck=nothing
end function
%>