<% Dim objQuery Set objQuery = GetDBConn() %> <% Dim iYear iYear = Trim(Request("iYear")) Dim iMonth, sMonth, cMonth iMonth = Trim(Request("iMonth")) sMonth = Trim(Request("sMonth")) Dim totDay totDay = Trim(Request("totDay")) IF Len(iMonth) > 1 Then cMonth = iMonth Else cMonth = "0" & iMonth End If Dim agent_name, agent_id agent_name = Trim(Request("agent_name")) agent_id = Trim(Request("agent_id")) Dim remarks remarks = Trim(Request("remarks")) Dim arrRemarks arrRemarks = Split(remarks, ",") '//response.write remarks '----------------------------------------------------------------------------------------------- 'NJ to JFK Dim dQuery1 dQuery1 = " IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS " &_ " WHERE TABLE_NAME = 'TOTAL_SUMMARY_FOR_MONTH_NJJFK_"&Left(agent_id,3)&"') " &_ " DROP VIEW TOTAL_SUMMARY_FOR_MONTH_NJJFK_"&Left(agent_id,3)&" " objQuery.execute(dQuery1) Dim tranQuery1 tranQuery1 = " CREATE VIEW TOTAL_SUMMARY_FOR_MONTH_NJJFK_"&Left(agent_id,3)&" " &_ " AS " &_ " SELECT sum(adult_cnt)+sum(child_cnt) as ttl, d_air_1, depart_date " &_ " FROM S_Reservation " &_ " WHERE user_id = '"&agent_id&"' and d_flight = 'OZ221' and convert(datetime, depart_date, 101) >= convert(datetime, '"&cMonth&"/01/"&iYear&" 00:00:00', 101) and " &_ " convert(datetime, depart_date, 101) <= convert(datetime, '"&cMonth&"/"&totDay&"/"&iYear&" 23:59:59', 101) " &_ " GROUP BY d_air_1, depart_date " objQuery.execute(tranQuery1) 'JFK to NJ Dim dQuery2 dQuery2 = " IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS " &_ " WHERE TABLE_NAME = 'TOTAL_SUMMARY_FOR_MONTH_JFKNJ_"&Left(agent_id,3)&"') " &_ " DROP VIEW TOTAL_SUMMARY_FOR_MONTH_JFKNJ_"&Left(agent_id,3)&" " objQuery.execute(dQuery2) Dim tranQuery2 tranQuery2 = " CREATE VIEW TOTAL_SUMMARY_FOR_MONTH_JFKNJ_"&Left(agent_id,3)&" " &_ " AS " &_ " SELECT sum(adult_cnt)+sum(child_cnt) as ttl, r_air_2, return_date " &_ " FROM S_Reservation " &_ " WHERE user_id = '"&agent_id&"' and r_flight = 'OZ222' and convert(datetime, return_date, 101) >= convert(datetime, '"&cMonth&"/01/"&iYear&" 00:00:00', 101) and " &_ " convert(datetime, return_date, 101) <= convert(datetime, '"&cMonth&"/"&totDay&"/"&iYear&" 23:59:59', 101) " &_ " GROUP BY r_air_2, return_date " objQuery.execute(tranQuery2) Function funcGet221NJJFK(byRef objQuery, byVal d_air_1) Dim strQuery strQuery = " SELECT ttl, d_air_1, depart_date " &_ " FROM TOTAL_SUMMARY_FOR_MONTH_NJJFK_"&Left(agent_id,3)&" " &_ " WHERE d_air_1 = '" & d_air_1 & "' ORDER BY depart_date asc" 'Response.write strQuery 'response.end funcGet221NJJFK = arrGetDBResult(objQuery, strQuery) End Function Function funcGet222JFKNJ(byRef objQuery, byVal r_air_2) Dim strQuery strQuery = " SELECT ttl, r_air_2, return_date " &_ " FROM TOTAL_SUMMARY_FOR_MONTH_JFKNJ_"&Left(agent_id,3)&" " &_ " WHERE r_air_2 = '" & r_air_2 & "' ORDER BY return_date asc" 'Response.write strQuery 'response.end funcGet222JFKNJ = arrGetDBResult(objQuery, strQuery) End Function '----------------------------------------------------------------------------------------------- Dim arrNJList1, arrNJList2, arrNJList3, arrNJList4 arrNJList1 = funcGet221NJJFK(objQuery, "CLOSTER (Golf Range)") arrNJList2 = funcGet221NJJFK(objQuery, "Tenafly (Train Station)") arrNJList3 = funcGet221NJJFK(objQuery, "HANNAM CHAIN (RT 46)") 'arrNJList4 = funcGet221NJJFK(objQuery, "Fort Lee (Woori Bank)") Dim arrJFKList1, arrJFKList2, arrJFKList3, arrJFKList4 arrJFKList1 = funcGet222JFKNJ(objQuery, "CLOSTER (Golf Range)") arrJFKList2 = funcGet222JFKNJ(objQuery, "Tenafly (Train Station)") arrJFKList3 = funcGet222JFKNJ(objQuery, "HANNAM CHAIN (RT 46)") 'arrJFKList4 = funcGet222JFKNJ(objQuery, "Fort Lee (Woori Bank)") %>

[<%=agent_name%>]  Reservation Summary of Shuttle Service for <%=sMonth%> <%=iYear%>

As Of <%=Date()%>

<% Dim ix, ixVal, sDate Dim a1, a2, a3, a4, b1, b2, b3, b4 Dim aRow1, aRow2, aTtl a1 = 0 a2 = 0 a3 = 0 a4 = 0 b1 = 0 b2 = 0 b3 = 0 b4 = 0 aRow1 = 0 aRow2 = 0 aTtl = 0 Dim Tot1, Tot2 Tot1 = 0 Tot2 = 0 Dim njNum For ix=1 To totDay IF Len(ix) > 1 Then ixVal = ix Else ixVal = "0" & ix End If sDate = cMonth &"/"& ixVal &"/"& iYear %> <% Tot1 = Tot1 + aRow1 Tot2 = Tot2 + aRow2 aRow1 = 0 aRow2 = 0 aTtl = 0 Next %>

Date/Route

CLOSTER

TENAFLY

HANNAM

TOTAL

REMARKS

221

222

221

222

221

222

221

222

TTL

<%=ix%>

<%=sDate%>

<%IF IsArray(arrNJList1) Then For njNum = 0 To UBound(arrNJList1, 2) IF sDate = Trim(arrNJList1(2, njNum)) Then a1 = a1 + arrNJList1(0, njNum) %> <%=Trim(arrNJList1(0, njNum))%> <% aRow1 = aRow1 + arrNJList1(0, njNum) End IF Next End IF%>

<%IF IsArray(arrJFKList1) Then For njNum = 0 To UBound(arrJFKList1, 2) IF sDate = Trim(arrJFKList1(2, njNum)) Then b1 = b1 + arrJFKList1(0, njNum) %> <%=Trim(arrJFKList1(0, njNum))%> <% aRow2 = aRow2 + arrJFKList1(0, njNum) End IF Next End IF%>

<%IF IsArray(arrNJList2) Then For njNum = 0 To UBound(arrNJList2, 2) IF sDate = Trim(arrNJList2(2, njNum)) Then a2 = a2 + arrNJList2(0, njNum) %> <%=Trim(arrNJList2(0, njNum))%> <% aRow1 = aRow1 + arrNJList2(0, njNum) End IF Next End IF%>

<%IF IsArray(arrJFKList2) Then For njNum = 0 To UBound(arrJFKList2, 2) IF sDate = Trim(arrJFKList2(2, njNum)) Then b2 = b2 + arrJFKList2(0, njNum) %> <%=Trim(arrJFKList2(0, njNum))%> <% aRow2 = aRow2 + arrJFKList2(0, njNum) End IF Next End IF%>

<%IF IsArray(arrNJList3) Then For njNum = 0 To UBound(arrNJList3, 2) IF sDate = Trim(arrNJList3(2, njNum)) Then a3 = a3 + arrNJList3(0, njNum) %> <%=Trim(arrNJList3(0, njNum))%> <% aRow1 = aRow1 + arrNJList3(0, njNum) End IF Next End IF%>

<%IF IsArray(arrJFKList3) Then For njNum = 0 To UBound(arrJFKList3, 2) IF sDate = Trim(arrJFKList3(2, njNum)) Then b3 = b3 + arrJFKList3(0, njNum) %> <%=Trim(arrJFKList3(0, njNum))%> <% aRow2 = aRow2 + arrJFKList3(0, njNum) End IF Next End IF%>

<%=aRow1%>

<%=aRow2%>

<%=aRow1+aRow2%>

<%=arrRemarks(ix-1)%>

Grand Total

<%=a1%>

<%=b1%>

<%=a2%>

<%=b2%>

<%=a3%>

<%=b3%>

<%=Tot1%>

<%=Tot2%>

<%=Tot1+Tot2%>

<% %> <% Set objQuery = Nothing %>