asp-json

<%
'for Excel
sql= " SELECT a.[No_]"&_
 " ,Case When a.[External Tracking No_] = '' Then e.[External Tracking No_] Else a.[External Tracking No_] End as [Track No_]"&_
 " ,a.[Source ID]"&_
 " ,a.[Used on Bill of Lading No_]"&_
 " ,a.[Shipping Agent Code]"&_
 " ,a.[Calculation Weight (LBS)]"&_
 " ,a.[Shipping Charge]"&_
 " ,e.[Shipping Charge] as [Tot Charge (BOL)]"&_
 " ,a.[Calculation Value]"&_
 " ,convert(varchar(20),a.[Posting Date],111) _10"&_
 " ,a.[External Document No_] _11"&_
 " ,c.[Name] as [Agent Name 12]"&_
 " ,a.[Ship-to No_] _13"&_
 " ,Case When b.[No_ 2] =''Then [Old Customer No_ (4_0)] Else b.[No_ 2] End as [Old Customer No 14]"&_
 " ,b.[Name] as [Customer Name 15]"&_
 " ,Case When a.[Shipping Payment Type]=0 Then'Prepaid' When a.[Shipping Payment Type]=1 Then'Third Party' "&_
 " When a.[Shipping Payment Type]=2 Then'Freight Collect' Else Convert(VARCHAR(50), "&_
 " a.[Shipping Payment Type]) End as [Shipping Payment Type 16] ,a.[Ship-to Code] _17 "&_
 " ,a.[Shortcut Dimension 1 code] division_17"&_
 " ,a.[Location Code] _18"&_
 " ,Case When a.[Closed]= 0 Then 'False' Else 'True' End as [Closed 19]"&_
 " ,a.[Ship-to State] _20"&_
 " ,a.[Ship-to Zip Code] _21"&_
 " FROM   (select a.[No_],a.[Source ID],a.[External Tracking No_],"&_
 " a.[Used on Bill of Lading No_],"&_
 " a.[Shipping Agent Code], a.[Ship-to No_],"&_
 " a.[Calculation Weight (LBS)],a.[Shipping Charge],"&_
 " a.[Calculation Value],a.[Posting Date],"&_
 " a.[Shipping Payment Type],a.[Ship-to Code],"&_
 " a.[Closed], a.[Ship-to State],a.[Ship-to Zip Code],"&_
 " b.[External Document No_],"&_
 " b.[Shortcut Dimension 1 code], b.[Location Code]"&_
 " from [FOREMOST GROUPS,INC_$Posted Package] a left join"&_
 " (select y.[No_] as Post_Package_No,"&_
 " x.[No_], x.[External Document No_],"&_
 " x.[Shortcut Dimension 1 code],"&_
 " x.[Location Code]"&_
 "   from [Sales Header] x,"&_
 " [FOREMOST GROUPS,INC_$Posted Package] y"&_
 "   where y.[Source ID] = x.[No_]"&_
 " and y.[Posting Date]"&_
 " between '2013-1-1' and '2013-2-5'"&_
 " and y.[Ship-to No_] like '%'"&_
 "   Union"&_
 "   select y.[No_]as Post_Package_No,"&_
 " x.[No_],x.[External Document No_], x.[Shortcut Dimension 1 code],"&_
 " x.[Location Code]"&_
 "   from [Closed Sales Header] x,"&_
 " [FOREMOST GROUPS,INC_$Posted Package] y"&_
 "   where y.[Source ID] = x.[No_]"&_
 " and y.[Posting Date]"&_
 " between '2013-1-1' and '2013-2-5'"&_
 " and y.[Ship-to No_] like '%'"&_
 "   ) b on (a.[No_]= b.Post_Package_No)) a"&_
 " Left Join [Customer] b on a.[Ship-to No_] = b.[No_]"&_
 " Left Join [Shipping Agent] c"&_
 " on a.[Shipping Agent Code] = c.[Code]"&_
 " Left Join [FOREMOST GROUPS,INC_$Bill of Lading] e"&_
 " on a.[Used on Bill of Lading No_] = e.[No_]"&_
 " WHERE a.[Posting Date] between '2013-1-1' and '2013-2-5'"&_
 " and a.[Ship-to No_] like '%'"&_
 " ORDER By no_"&_
 " "



pcallback=Request.querystring("callback")
response.expires=-1
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "DSN=hkdvdb1;UID=userID;PWD=userPassword;Database=DataBaseName"
Set SQLStmt = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject ("ADODB.Recordset")
SQLStmt.CommandText = sql
SQLStmt.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
delimiter2=""
'response.setContentType("text/javascript");
'response.setCharacterEncoding("UTF-8");

'_r = callback + "([\n";
' charset=utf-8
Response.Clear()
Response.ContentType = "text/javascript"
response.write(pcallback)
response.write("([")
rcount=0
Do While Not RS.EOF
If rcount=14 Then Exit do
delimiter=""
response.write(delimiter2&"[")
ccount=0
for each col in rs.fields

if ccount<21 then
response.write(delimiter&"'"&col.value&"'")
delimiter=","
end if

ccount=ccount+1

if 1=0 then
roundup=2
if rr=60000 then
roundup=5
end if

if rr>=4 then
If IsNull(col.value) Then
aValue(rr)=FormatNumber(0,roundup)
else
aValue(rr)=FormatNumber(col.value,roundup)
end if
elseif  col.value="1753/01/01"  OR col.value="1754/01/01"  then
aValue(rr)="-"
else
aValue(rr)=col.value
end if
rr=rr+1
end if
next
delimiter2=","
response.write("]")
'response.write("\n")
rs.movenext
rcount=rcount+1
loop
response.write("])")
'Response.Write(json);
Response.End()

'?([
'['1396235501819','Government Logistics Department','REF','A'],
'['1396235623593','Government Logistics Department','2','D'],
'['1396235673056','Government Logistics Department','ASDFAASDFAFD','D']
']);

'?([
'['1396250882115','PYTN01403001','Government Logistics Department','D'],
'['1396251592777','RETN01403002','Government Logistics Department','D']
']);
'? {['some string 1', 'some data', 'whatever data']}
 '?([]);
 %>