copy of accrpt.asp

进销存软件源代码

<!-- #include virtual="/eB3KTransaction.asp" --><!-- #include file="../Common.asp" --><!-- #include file="../stdlib.asp" --><%
dim gInit
dim gDateFrom, gDateTo
dim gTableName
dim gIncludeChild
gTableName="bdVoucher"
gIncludeChild=0
gInit=0

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

'初始化
Function RptInit(DateFrom, DateTo)
	gDateFrom=DateFrom
	gDateTo=DateTo
	gInit=1
End Function

'取得科目余额方向
Function RptSubjectBalanceDirect(SubjectID)
'返回：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 [ID]=" & SubjectID
	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 [ID]=" & SubjectID
		rsCheck=Conn.Execute(szSQL)
		RptSubjectBalanceDirect = rsCheck("int1")
	end if
	set rsCheck=nothing
End Function

'取得科目余额
Function RptSubjectBalance(SubjectID)
	dim szSQl, szCode, iCodeLen, DebitOrCredit, FirstBalance
	dim rsCheck

	szSQL="SELECT Count(*) as rc FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [ID]=" & SubjectID
	rsCheck=Conn.Execute(szSQL)
	if rsCheck("rc")>0 then
		szSQL="SELECT [int1], [Code] FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [ID]=" & SubjectID
		rsCheck=Conn.Execute(szSQL)
		szCode = rsCheck("Code")
		DebitOrCredit = rsCheck("int1")
		iCodeLen = Len(szCode)
	end if
	set rsCheck=nothing
	
	'计算期初余额
	szSQL="SELECT 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 & "'"
	rsCheck=Conn.Execute(szSQL)
	FirstBalance=NullToZero(rsCheck("Amount"))
	'End
	
	szSQL="SELECT 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 & "'"
	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(SubjectID, DebitOrCredit)	'0/1 debit/credit
	dim szSQl, szCode, iCodeLen
	dim rsCheck

	szSQL="SELECT Count(*) as rc FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [ID]=" & SubjectID
	rsCheck=Conn.Execute(szSQL)
	if rsCheck("rc")>0 then
		szSQL="SELECT [Code] FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [ID]=" & SubjectID
		rsCheck=Conn.Execute(szSQL)
		szCode = rsCheck("Code")
		iCodeLen = Len(szCode)
	end if
	set rsCheck=nothing
	
	'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(SubjectID, DebitOrCredit)	'0/1 debit/credit
	dim szSQl, szCode, iCodeLen, szSumName
	dim rsCheck

	szSQL="SELECT Count(*) as rc FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [ID]=" & SubjectID
	rsCheck=Conn.Execute(szSQL)
	if rsCheck("rc")>0 then
		szSQL="SELECT [Code] FROM [biSubject] WHERE [InfoType]=10 AND (AccountID=(SELECT RootID FROM [biCorperation] WHERE ID=" & AccountID & ") OR AccountID=" & AccountID & ") AND [ID]=" & SubjectID
		rsCheck=Conn.Execute(szSQL)
		szCode = rsCheck("Code")
		iCodeLen = Len(szCode)
	end if
	set rsCheck=nothing
	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].[BillType]=8 AND [vt].[ID]=[vl].[ID] AND [biSubject].[ID]=[vl].[ProductID] AND [vt].[AccountID]=" & AccountID & " AND [vt].[BillDate]>='" & GetDateString(gDateFrom) &"' AND [vt].[BillDate]<='" & GetDateString(gDateTo) &"' AND SUBSTRING([biSubject].[Code],1,"&iCodeLen&")='" & szCode & "'"
	rsCheck=Conn.Execute(szSQL)
	RptSubjectHappen=NullToZero(rsCheck("Amount"))
End Function
%>