function.asp

这是去年开发的中移鼎讯手机进销存系统 大家

		end if
	end if
	if CountyID<>"" then
		csql = csql + " CountyID="&Cstr(CountyID)&" and "
	end if
	if UserPower<>"" then
	 csql = csql + " InChk_UserPower="&UserPower&" and "
	end if
	if Pro_ID<>"" then
		csql = csql + "  Pro_ID="&Cstr(Pro_ID)&" and "
	end if
	if UserID<>"" then
		csql = csql + "  InChk_UserID="&Cstr(UserID)&" and"
	end if
	csql = csql + " IsChk=1;"
	'Response.write csql
	Call sql_open(crs,csql,Conn,1,1)
	b = crs("b")
	if b="" or isnull(b) then 
		b=0
	end if
	call rs_end(crs)
	InPro_Num = clng(b)
end function
'进货金额[市、县、分销点]
function InPro_M(UserPower,UserID,CityID,CountyID,Pro_ID,IsNew,fromTime,toTime)
	Dim b
	b=0
	csql ="Select SUM(Pro_Money) from T_SubOutOrder where CityID="&Cstr(CityID)&" and "	
	if IsNew = 1 then
		if fromTime="" or toTime="" then
			csql = csql + " DATEPART(dd, Intime)=DATEPART(dd, GETDATE()) and "
		else
			csql = csql + " convert(varchar(10),InTime,120) between  '"&fromTime&"' and '"&toTime&"' and "
		end if
	end if
	if CountyID<>"" then
		csql = csql + " CountyID="&Cstr(CountyID)&" and "
	end if
	if UserPower<>"" then
	 csql = csql + " InChk_UserPower="&UserPower&" and "
	end if
	if Pro_ID<>"" then
		csql = csql + "  Pro_ID="&Cstr(Pro_ID)&" and "
	end if
	if UserID<>"" then
		csql = csql + "  InChk_UserID="&Cstr(UserID)&" and"
	end if
	csql = csql + " IsChk=1;"
	'Response.write csql
	Call sql_open(crs,csql,Conn,1,1)
	b = crs("b")
	if b="" or isnull(b) then 
		b=0
	end if
	call rs_end(crs)
	InPro_M = clng(b)
end function
'///////////////////////////////////////////////
''发货数[省、市、县]
function OutPro_Num(UserPower,UserID,CityID,CountyID,Pro_ID,IsNew,fromTime,toTime)
	Dim b
	b=0

	csql = "Select SUM(Pro_Num) as b from T_SubOutOrder where CityID="&Cstr(CityID)&" "
	if IsNew = 1 then
		if fromTime="" or toTime="" then
			csql = csql + " and DATEPART(dd, Intime)=DATEPART(dd, GETDATE()) "
		else
			csql = csql + " and convert(varchar(10),InTime,120) between  '"&fromTime&"' and '"&toTime&"' "
		end if
	end if
	if CountyID<>"" then
		csql = csql + " and CountyID="&Cstr(CountyID)&" "
	end if
	if UserPower<>"" then
		csql = csql + " and UserPower="&UserPower&" "
	end if
	if Pro_ID<>"" then
		csql = csql + " and Pro_ID="&Cstr(Pro_ID)&" "
	end if
	if UserID<>"" then
		csql = csql + " and UserID="&Cstr(UserID)&" "
	end if	
	'response.write csql&"<br>"
	Call sql_open(crs,csql,Conn,1,1)
	b = crs("b")
	if b="" or isnull(b) then 
		b=0
	end if
	call rs_end(crs)
	OutPro_Num = clng(b)	
end function

''////////////发货金额////////////[省、市、县]
function OutPro_M(UserPower,UserID,CityID,CountyID,Pro_ID,IsNew,fromTime,toTime)
	Dim b
	b=0
	csql = "Select SUM(Pro_Money) from T_SubOutOrder where CityID="&Cstr(CityID)&" "
	if IsNew = 1 then
		if fromTime="" or toTime="" then
			csql = csql + " and DATEPART(dd, Intime)=DATEPART(dd, GETDATE()) "
		else
			csql = csql + " and convert(varchar(10),InTime,120) between  '"&fromTime&"' and '"&toTime&"' "
		end if
	end if
	if CountyID<>"" then
		csql = csql + " and CountyID="&Cstr(CountyID)&" "
	end if
	if UserPower<>"" then
		csql = csql + " and UserPower="&UserPower&" "
	end if
	if Pro_ID<>"" then
		csql = csql + " and Pro_ID="&Cstr(Pro_ID)&" "
	end if
	if UserID<>"" then
		csql = csql + " and UserID="&Cstr(UserID)&" "
	end if	
'	response.write csql&"<br>"
	Call sql_open(crs,csql,Conn,1,1)
	b = crs("b")
	if b="" or isnull(b) then 
		b=0
	end if
	call rs_end(crs)
	OutPro_M = clng(b)	
end function
'///////////////
'功能:
'1   查回款数
'2   回款金额
'3   开票金额
'4   未开票金额
'5   用户自己的售货情况
'UserPower 用户组
'UserID 用户ID(如果用查自己的记录)
'CityID 市
'CountyID 县
'Pro_ID 产品ID号
'IsNew 是否为新增 1为
'fromTime 统计开始时间
'toTime 
'Sel 查看回款数或回款金额
'IsChk   是否确认的
function Fund_Num(UserPower,UserID,CityID,CountyID,Pro_ID,IsNew,fromTime,toTime,IsChk,Sel)
	Dim m1
	csql = "Select Fund_Num,BackFundMoney from T_SubSaleOrder where CityID="&Cstr(CityID)&" "
	if IsNew = 1 then
		if fromTime="" or toTime="" then
			csql = csql + " and DATEPART(dd, Intime)=DATEPART(dd, GETDATE()) "
		else
			csql = csql + " and convert(varchar(10),InTime,120) between  '"&fromTime&"' and '"&toTime&"' "
		end if
	end if
	
	if CountyID<>"" then
		csql = csql + " and CountyID="&Cstr(CountyID)&" "
	end if
	if UserPower<>"" then
		csql = csql + " and UserPower="&UserPower&" "
	end if
	if Pro_ID<>"" then
		csql = csql + " and Pro_ID="&Cstr(Pro_ID)&" "
	end if
	if UserID<>"" then
		csql = csql + " and UserID="&Cstr(UserID)&" "
	end if		
	if IsChk=1 then
		csql = csql + " and IsChk=1 "
	end if
	'response.write csql&"<br>"
	Call sql_open(crs,csql,Conn,1,1)
	if crs.eof and crs.bof then
		m1 = 0
	else
		Do while not crs.eof
			if Sel=1 then
				m1 = m1+clng(crs("Fund_Num"))
			elseif Sel=2 then
				m1 = m1+clng(crs("BackFundMoney"))
			end if
		crs.movenext
		loop
	end if
	call rs_end(crs)
	Fund_Num = clng(m1)		
end function
'sel=1统计数量
'sel=2统计金额
function OutmyPro(UserPower,UserID,Pro_ID,IsNew,fromTime,toTime,sel)
	Dim m1
	m1=0
	csql = "Select Pro_Num,Pro_Money from T_SubOutOrder "
	if IsNew = 1 then
		if fromTime="" or toTime="" then
			csql = csql + " where DATEPART(dd, Intime)=DATEPART(dd, GETDATE()) "
		else
			csql = csql + " where convert(varchar(10),InTime,120) between  '"&fromTime&"' and '"&toTime&"' "
		end if
		csql = csql + " and UserPower="&UserPower&" and Pro_ID="&Cstr(Pro_ID)&" and UserID="&Cstr(UserID)&" "
	else
		csql = csql + " where UserPower="&UserPower&" and Pro_ID="&Cstr(Pro_ID)&" and UserID="&Cstr(UserID)&" "
	end if
	'response.write csql&"<br>"
	Call sql_open(crs,csql,Conn,1,1)
	if crs.eof and crs.bof then
		m1 = 0
	else
		Do while not crs.eof
			if Sel=1 then
				m1 = m1+clng(crs("Pro_Num"))
			elseif Sel=2 then
				m1 = m1+clng(crs("Pro_Money"))
			end if

		crs.movenext
		loop
	end if
	call rs_end(crs)
	OutmyPro = clng(m1)	
end function

function Fundmy_Num(UserPower,UserID,Pro_ID,IsNew,fromTime,toTime,IsChk,Sel)
	Dim m1
	csql = "Select Fund_Num,BackFundMoney from T_SubSaleOrder "
	if IsNew = 1 then
		if fromTime="" or toTime="" then
			csql = csql + " where DATEPART(dd, Intime)=DATEPART(dd, GETDATE()) "
		else
			csql = csql + " where convert(varchar(10),InTime,120) between  '"&fromTime&"' and '"&toTime&"' "
		end if
		csql = csql + " and UserPower="&UserPower&" and Pro_ID="&Cstr(Pro_ID)&" and UserID="&Cstr(UserID)&" "
	else
		csql = csql + " where UserPower="&UserPower&" and Pro_ID="&Cstr(Pro_ID)&" and UserID="&Cstr(UserID)&" "
	end if
	if IsChk=1 then
		csql = csql + " and IsChk=1 "
	end if
	'response.write csql&"<br>"
	Call sql_open(crs,csql,Conn,1,1)
	if crs.eof and crs.bof then
		m1 = 0
	else
		Do while not crs.eof
			if Sel=1 then
				m1 = m1+clng(crs("Fund_Num"))
			elseif Sel=2 then
				m1 = m1+clng(crs("BackFundMoney"))
			end if
		crs.movenext
		loop
	end if
	call rs_end(crs)
	Fundmy_Num = clng(m1)		
end function


Class ExcelGen
	Private objSpreadsheet
	Private iColOffset
	Private iRowOffset
	Sub Class_Initialize()
		Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")
		iRowOffset = 2
		iColOffset = 2
	End Sub
	
	Sub Class_Terminate()
		Set objSpreadsheet = Nothing 'Clean up
	End Sub
	
	Public Property Let ColumnOffset(iColOff)
		If iColOff > 0 then
			iColOffset = iColOff
		Else
			iColOffset = 2
		End If
	End Property
	
	Public Property Let RowOffset(iRowOff)
		If iRowOff > 0 then
			iRowOffset = iRowOff
		Else
			iRowOffset = 2
		End If
	End Property 
'--控制Excel表头
	Sub GenerateWorksheet(objRS)
		'Populates the Excel worksheet based on a Recordset's contents
		'Start by displaying the titles
		If objRS.EOF then Exit Sub
		Dim objField, iCol, iRow
		iCol = iColOffset
		iRow = iRowOffset
		For Each objField in objRS.Fields
			objSpreadsheet.Cells(iRow, iCol).Value = objField.Name
			objSpreadsheet.Columns(iCol).AutoFitColumns 
'			'设置Excel表里的字体
			objSpreadsheet.Cells(iRow, iCol).Font.Bold = True 
			objSpreadsheet.Cells(iRow, iCol).Font.Italic = False
			objSpreadsheet.Cells(iRow, iCol).Font.Size = 10
			objSpreadsheet.Cells(iRow, iCol).Halignment = 2 '居中
			iCol = iCol + 1
		Next 'objField
		'----Display all of the data 
		Do While Not objRS.EOF
			iRow = iRow + 1
			iCol = iColOffset
			For Each objField in objRS.Fields
				If IsNull(objField.Value) then
					objSpreadsheet.Cells(iRow, iCol).Value = ""
				Else
					objSpreadsheet.Cells(iRow, iCol).Value = objField.Value
					objSpreadsheet.Columns(iCol).AutoFitColumns 
					objSpreadsheet.Cells(iRow, iCol).Font.Bold = False 
					objSpreadsheet.Cells(iRow, iCol).Font.Italic = False
					objSpreadsheet.Cells(iRow, iCol).Font.Size = 10 
				End If
				iCol = iCol + 1
			Next 'objField
		objRS.MoveNext 
		Loop 
	End Sub
	Function SaveWorksheet(strFileName)
		'Save the worksheet to a specified filename
		On Error Resume Next
		Call objSpreadsheet.ActiveSheet.Export(strFileName, 0)
		SaveWorksheet = (Err.Number = 0)
	End Function
End Class
''///////////////////////////////////////////////////////////
''再改进货函数(市)
'统计某市、县的进货情况，包括进货数目和金额
'sel=1 查进货产品数量 sel=2 查金额
'UserPower用户等级
'UserID 用户ID号
'CityID 市ID，CountyID县ID
'Pro_ID 产品ID
'必须自己确认进货才算
''///////////////////////////////////////////////////////////
function S_InSubOrder(sel,UserID,Pro_ID)
	Dim b,krs,ksql
	b = 0
	if Sel=1 then
		ksql = "Select SUM(Pro_Num) as b "
	else	
		ksql = "Select SUM(Pro_Money) as b "
	end if
	ksql = ksql + "from T_SubOutOrder where InChk_UserID="&cstr(UserID)&" and Pro_ID="&Cstr(Pro_ID)&" and IsChk=1;"
	'Response.write ksql
	Call sql_open(krs,ksql,conn,1,1)
	b = krs("b")
	if b="" or isnull(b) then 
		b=0
	end if
	call rs_end(krs)
	S_InSubOrder = clng(b)
end function
''发货数
''只要有发货就算。
function S_OutSubOrder(sel,UserID,Pro_ID)
	Dim b,krs,ksql
	b = 0
	if Sel=1 then
		ksql = "Select SUM(Pro_Num) as b "
	else	
		ksql = "Select SUM(Pro_Money) as b "
	end if
	ksql = ksql + "from T_SubOutOrder where UserID="&cstr(UserID)&" and Pro_ID="&Cstr(Pro_ID)&";"
	'Response.write ksql
	Call sql_open(krs,ksql,conn,1,1)
	b = krs("b")
	if b="" or isnull(b) then 
		b=0
	end if
	call rs_end(krs)
	S_OutSubOrder = clng(b)
end function
''回款数目(销售数)
'sel 数量/金额 
'UserPower 
'CityID 市ID,CountyID,县ID
'Pro_ID 产品ID
function S_FundSubOrder(sel,UserPower,CityID,CountyID,UserID,Pro_ID)
	Dim b,krs,ksql
	b = 0
	if Sel=1 then
		ksql = "Select SUM(Fund_Num) as b "
	else	
		ksql = "Select SUM(BackFundMoney) as b "
	end if
	ksql = ksql + " from T_SubSaleOrder where IsChk=1 and"
	if Pro_ID<>"" then
		ksql = ksql + " Pro_ID="&Cstr(Pro_ID)&" and "
	end if
	if UserPower=9 then		'分销点用户
		ksql = ksql + " UserID="&Cstr(UserID)&" "
	elseif UserPower=99 then	'县级用户
		ksql = ksql + " CountyID="&cstr(CountyID)&" "
	elseif UserPower=999 then	'市级用户
		ksql = ksql + " CityID="&cstr(CityID)&" and  CountyID=0"
	end if

'	Response.write ksql
	Call sql_open(krs,ksql,conn,1,1)
	b = krs("b")
	if b="" or isnull(b) then 
		b=0
	end if
	call rs_end(krs)
	S_FundSubOrder = clng(b)
end function
%>