📄 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)
%>
<%
tt = 1
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 prod_type on product.prod_flag = prod_type.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
'写入未生成易联单号的所有记录到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 + " join prod_type on product.prod_flag = prod_type.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
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)
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) & "记费重量" & 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")
prod_no = rs_2("prod_no")
prod_pinming = rs_2("prod_pinming")
type_name = rs_2("type_name")
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")
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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -