📄 复件 db2excel_out.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_color = trim(request("prod_color"))
prod_color = replace(prod_color,"'","’")
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)
%>
<%
sql = "select * from outward_elink "
sql = sql + " join outward_detail on outward_elink.elink_id = outward_detail.detail_elink_id "
sql = sql + " join guest on outward_detail.detail_guest_id = guest.guest_id "
sql = sql + " join outward on outward_elink.elink_outw_id = outward.outw_id "
sql = sql + " left outer join detail_info on outward_detail.detail_id = detail_info.info_detail_id "
sql = sql + " join product on outward_detail.detail_prod_id = product.prod_id "
sql = sql + " join dc on outward_elink.elink_dc_id = dc.dc_id "
sql = sql + " where outward_elink.elink_outw_date >= '"&start_date&"' and outward_elink.elink_outw_date <= '"&end_date&"' "
sql = sql + " and outward_elink.elink_companyid = "&newcompanyid&" "
if smallinput <> 0 then
sql = sql + " and outward_elink.elink_dc_id = "&smallinput&" "
else
if flag > 3 then
sql = sql + " and outward_elink.elink_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_color <> "" then
sql = sql + " and product.prod_color = '"&prod_color&"' "
end if
end if
'if odd <> "" then
' sql = sql + " and outward_elink.elink_odd = '"&odd&"' "
'end if
sql = sql + " order by outward_detail.detail_id asc "
'Response.Write sql
'Response.End
set rs = server.CreateObject("adodb.recordset")
rs.Open sql,conn
if rs.EOF and rs.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_20020418\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) & "SRA" & chr(9) & "DC编号" & 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) & "签收时间" & chr(9) & "签收人" & chr(9) & "身份证" & chr(9) & "预计到达时间" & chr(9) & "实际重量" & chr(9) & "记费重量" & chr(9)
'--将表的列名先写入EXCEL
myfile.writeline strLine
Do while Not rs.EOF
strLine=""
'for each x in rs.Fields
' strLine= strLine & x.value & chr(9)
'next
outw_sra = trim(rs("outw_sra"))
if isnumeric(outw_sra) = true then
outw_sra = outw_sra & "'"
end if
elink_odd = trim(rs("elink_odd"))
if isnumeric(elink_odd) = true then
elink_odd = elink_odd & "'"
end if
rec_idcard = trim(rs("rec_idcard"))
if isnumeric(rec_idcard) = true then
rec_idcard = rec_idcard & "'"
end if
transport_type = rs("transport_type")
if transport_type = 2 then
sql = "select * from air where air_elink_id = "&rs("elink_id")&""
set rs_air = conn.execute(sql)
if not rs_air.eof then
air_take_no = rs_air("air_take_no")
air_line_no = rs_air("air_line_no")
air_etd = rs_air("air_etd")
air_eta = rs_air("air_eta")
else
air_take_no = " "
air_line_no = " "
air_etd = " "
air_eta = " "
end if
rs_air.close
set rs_air = nothing
else
air_take_no = " "
air_line_no = " "
air_etd = " "
air_eta = " "
end if
strLine = strLine & rs("outw_date")& chr(9) & outw_sra & chr(9) & rs("dc_code")& chr(9) & rs("dc_name")& chr(9) & rs("guest_user")& chr(9) & rs("guest_address")& chr(9) & rs("guest_tel")& chr(9) & rs("guest_fax")& chr(9) & rs("guest_takecode")& chr(9)& rs("info_type")& chr(9)& rs("prod_no")& chr(9) & rs("prod_pinming")& chr(9) & rs("prod_type")& chr(9) & rs("prod_color")& chr(9) & rs("detail_no")& chr(9) & elink_odd & chr(9) & air_take_no & chr(9) & air_line_no & chr(9) & air_etd & chr(9) & air_eta & chr(9) & rs("rec_date")& chr(9) & rs("rec_man")& chr(9) & rec_idcard & chr(9) & rs("info_yuji_time") & chr(9) & rs("fact_weight") & chr(9) & rs("tally_weight")
'--将表的数据写入EXCEL
myfile.writeline strLine
rs.MoveNext
loop
'写入未生成易联单号的所有记录到EXCEL表中
sql = "select * from outward "
sql = sql + " right outer join outward_detail on outward.outw_id = outward_detail.detail_outw_id "
sql = sql + " join guest on outward_detail.detail_guest_id = guest.guest_id "
sql = sql + " join dc on outward.outw_dc_id = dc.dc_id "
'2002-9-17修改为左连接
'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 + " join product on outward_detail.detail_prod_id = product.prod_id "
sql = sql + " where outward.outw_date >= '"&start_date&"' and outward.outw_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_color <> "" then
sql = sql + " and product.prod_color = '"&prod_color&"' "
end if
end if
sql = sql + " and outward_detail.detail_elink_id = 0 "
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 not rs_2.EOF then
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")
prod_no = rs_2("prod_no")
prod_pinming = rs_2("prod_pinming")
prod_type = rs_2("prod_type")
prod_color = rs_2("prod_color")
detail_no = rs_2("detail_no")
elink_odd = " "
rec_idcard = " "
air_take_no = " "
air_line_no = " "
air_etd = " "
air_eta = " "
info_yuji_time = rs_2("info_yuji_time")
strLine = strLine & outw_date& 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) & guest_takecode& chr(9)& info_type& chr(9)& prod_no& chr(9) & prod_pinming& chr(9) & prod_type& chr(9) & prod_color& chr(9) & detail_no& chr(9) & elink_odd & chr(9) & air_take_no & chr(9) & air_line_no & chr(9) & air_etd & chr(9) & air_eta & chr(9) & " " & chr(9) & " " & chr(9) & rec_idcard & chr(9) & info_yuji_time & chr(9) & " " & chr(9) & " "
'--将表的数据写入EXCEL
myfile.writeline strLine
rs_2.MoveNext
loop
end if
rs_2.Close
set rs_2 = nothing
end if
set myfile = nothing
Set fs=Nothing
link="<A HREF=../"&filename2&">请用鼠标右键点击,然后选择<目标另存为></a>"
Response.write link
rs.Close
set rs = nothing
call closedatabase
%>
<input class=buttonface type="button" name="ok" value="返回" onclick="javascript:history.go(-1)">
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -