📄 db2excel_out.asp

📁 1.多种查询方式
💻 ASP
字号:
<%@language=vbscript%>
<% 
response.buffer=true
Response.Expires = 0
Response.CacheControl = "Private"
%>
<!-- #include file=../data/username.inc-->
<!-- #include file=../data/connect.asp-->
<!--#include file=../data/myPrg.asp-->
<!-- #include file=../data/flag.inc-->
<% 
newcompanyid = request("companyid")
if newcompanyid = "" then
	Response.Write "<script language=javascript>"
	Response.Write "alert(""请先选择公司再做此步操作！"");"
	Response.write "history.go(-1);"
	Response.Write "</script>"
	Response.End
elseif not isnumeric(newcompanyid) then
	call closedatabase
	Response.Write "<script language=javascript>"
	Response.Write "alert(""companyid必须为整数！"");history.go(-1);"
	Response.Write "</script>"
	Response.End
else
	newcompanyid = cint(newcompanyid) 
end if
%>
<%'On Error Resume Next%>
<%const title="出库统计报表"%>
<html>
<head>
<title><%=title%></title>
<meta http-equiv="Content-Type"content="text/html; charset=gb_2312-80">
<link rel=stylesheet type=text/css href=../data/forum.css>
</head>
<%
wood_type = Request.Form("wood_type")
start_year = Request.Form("start_year")
start_month = Request.Form("start_month")
start_day = Request.Form("start_day")
end_year = Request.Form("end_year")
end_month = Request.Form("end_month")
end_day = Request.Form("end_day")
start_date = start_year&"-"&start_month&"-"&start_day
end_date = end_year&"-"&end_month&"-"&end_day
smallinput = request("dc_id")
if smallinput = "" then 
	smallinput = 0
else
	smallinput = replace(smallinput,"'","’")
end if 
biglocation = request("biglocation")
if biglocation = "" then 
	biglocation = 0
else
	biglocation = replace(biglocation,"'","’")
end if
smalllocation = request("smalllocation")
if smalllocation = "" then 
	smalllocation = 0
else
	smalllocation = replace(smalllocation,"'","’")
end if
oddsia = request("oddsia")
oddsia = replace(oddsia,"'","’")
prod_type2 = trim(request("prod_type"))
prod_type2 = replace(prod_type2,"'","’")
	prod_danwei = trim(request("prod_danwei"))
	prod_danwei = replace(prod_danwei,"'","’")
oddsra = request("oddsra")
if oddsra = 1 then
	sra = trim(request("sra"))
	sra = replace(sra,"'","’")
elseif oddsra = 2 then
	odd = trim(request("odd"))
	odd = replace(odd,"'","’")
else
	Response.End 
end if
%>
<body topmargin="0">
<%
Showtitle(title)
%>
  <%
		tt = 1

      
      
       sql = "select * from outward,outward_detail,guest,dc,product,prod_type "
	   sql = sql + " where outward.outw_id = outward_detail.detail_outw_id "
	   sql = sql + " and outward_detail.detail_guest_id = guest.guest_id "
	   sql = sql + " and outward.outw_dc_id = dc.dc_id "
	   
	   'sql = sql + " join detail_info on outward.outw_id = detail_info.info_outw_id "
	   'sql = sql + " left outer join detail_info on detail_info.info_detail_id = outward_detail.detail_id "
	   sql = sql + " and outward_detail.detail_prod_id = product.prod_id "
	   sql = sql + " and product.prod_flag = prod_type.id "
		sql = sql + " and outward.outw_fact_date >= #"&start_date&"# and outward.outw_fact_date <= #"&end_date&"# "
	   sql = sql + " and outward.outw_companyid = "&newcompanyid&" "
		if smallinput <> 0 then
			sql = sql + " and outward.outw_dc_id = "&smallinput&" "
		else
			if flag > 3 then
				sql = sql + " and outward.outw_dc_id in ("&dcservice&") "
			end if
		end if
		if cstr(wood_type) = "1" then
			sql = sql + " and product.prod_dir_flag = 0 "
		elseif cstr(wood_type) = "2" then
			sql = sql + " and product.prod_dir_flag = 1 "
		end if
		
		if clng(oddsia) = 1 then
			if smalllocation <> 0 then
				sql = sql + " and outward_detail.detail_prod_id = "&smalllocation&" "
			end if
	    elseif clng(oddsia) = 2 then
			if prod_type2 <> "" then
				sql = sql + " and product.prod_type = '"&prod_type2&"' "
			end if
			if prod_danwei <> "" then
				sql	= sql + " and product.prod_danwei = '"&prod_danwei&"' "
			end if
	    end if
	    
		sql = sql + " order by outward_detail.detail_id asc "
		'response.Write sql 
		'response.End 
		set rs_2 = server.CreateObject("adodb.recordset")
		rs_2.Open sql,conn,1,1
  if rs_2.EOF and rs_2.BOF then
	response.Write "无记录!"
	response.Write "<a href=""javascript:history.go(-1)""><font size=+2> 后 退 </font></a>"
	response.End 
  else
			Set fs = server.CreateObject("scripting.filesystemobject")
  '--假设你想让生成的EXCEL文件做如下的存放
  'filename = "D:\mir_20060618\companyadmin\"&Session("companymanager")&"_online.xls"
  filename =  server.MapPath(""&User&"_online_out.xls")
  filename2 = "operate\"&User&"_online_out.xls"
  '--如果原来的EXCEL文件存在的话删除它
  if fs.FileExists(filename) then
    fs.DeleteFile(filename)
  end if
  '--创建EXCEL文件
  set myfile = fs.CreateTextFile(filename,true)
  
  
    dim strLine,responsestr
    strLine=""
      'For each x in rs.fields
      '  strLine= strLine & x.name & chr(9)
      'Next
		 strline = strline & "出库单日期" & chr(9) & "出库日期" & chr(9) & "SRA" & chr(9) & "仓库编号" & chr(9) & "城市" & chr(9) & "领货人名" & chr(9) & "去向地址" & chr(9) & "联系电话" & chr(9) & "传真" & chr(9) & "证件号" & chr(9)  & "物品类别" & chr(9) & "物品编号" & chr(9) & "品名" & chr(9) & "物品型号" & chr(9) & "物品单位" & chr(9) & "数量" & chr(9) & "单价（元）" & chr(9) & "总价（元）" & chr(9) &  "签收时间" & chr(9)  & "出库人" & chr(9)   
      '--将表的列名先写入EXCEL
      myfile.writeline strLine
      Do while Not rs_2.EOF
      strLine=""
				
				'for each x in rs.Fields
				'  strLine= strLine & x.value & chr(9)
				'next
				outw_date = rs_2("outw_date")
				outw_sra = trim(rs_2("outw_sra"))
					if isnumeric(outw_sra) = true then
						outw_sra = outw_sra & "'"
					end if
				dc_code =  rs_2("dc_code")
				dc_name =  rs_2("dc_name")
				guest_user = rs_2("guest_user")
				guest_address = rs_2("guest_address")
				guest_tel =  rs_2("guest_tel")
				guest_fax = rs_2("guest_fax")
				guest_takecode = rs_2("guest_takecode")
				'info_type = rs_2("info_type")
				type_name = rs_2("type_name")
				prod_no =  rs_2("prod_no")
				prod_pinming = rs_2("prod_pinming")
				prod_type = rs_2("prod_type")
				prod_danwei = rs_2("prod_danwei")
				prod_danjia=rs_2("prod_danjia")
				
				detail_no = rs_2("detail_no")
				prod_total_money=detail_no*prod_danjia
				outwardfactdate=rs_2("detail_date")
				rec_idcard = rs_2("guest_idcard")
				detail_man=rs_2("detail_man")
				'info_yuji_time = rs_2("info_yuji_time")
				database_outw_id = rs_2("detail_outw_id")
				database_guest_id = rs_2("detail_guest_id")
				sql = "select * from detail_info where info_detail_id = (select min(detail_id) as detail_id from outward_detail where detail_outw_id = "&database_outw_id&" and detail_guest_id = "&database_guest_id&") "
				set rst = conn.execute(sql)
				if not rst.eof then
					info_type = rst("info_type")
					info_takeman_id = rst("info_takeman_id")
					info_yuji_time = rst("info_yuji_time")
				else
					info_type = " "
					info_takeman_id = " "
					info_yuji_time = " "
				end if
				rst.close
				set rst = nothing
            
      
      
				strLine = strLine & outw_date& chr(9) & outwardfactdate& chr(9) & outw_sra & chr(9) & dc_code& chr(9) & dc_name& chr(9) & guest_user& chr(9) & guest_address& chr(9) & guest_tel& chr(9) & guest_fax& chr(9) & rec_idcard & chr(9) & type_name &chr(9)& prod_no& chr(9) & prod_pinming& chr(9) & prod_type& chr(9) & prod_danwei& chr(9) & detail_no& chr(9) & prod_danjia& chr(9) & prod_total_money& chr(9) & " " & chr(9) & detail_man & chr(9) 
				'--将表的数据写入EXCEL
				myfile.writeline strLine
		
			rs_2.MoveNext
			loop 
		end if
        set myfile = nothing
Set fs=Nothing

link="<A HREF=../"&filename2&">请用鼠标右键点击,然后选择<目标另存为></a>"
Response.write link
rs_2.Close
set rs_2 = nothing
  call closedatabase
%>
<input class=buttonface type="button" name="ok" value="返回"  onclick="javascript:history.go(-1)">
</body>
</html>
💿 文件大小 1144 K
👤 上传用户 libin2722
📂 所属分类其他数据库
🏷️ 相关标签

#查询 #方式
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -